Reorganizing and Purging the MAC-PAC Database

Reorganizing the database causes the system to review the files to determine which records should be deleted. The records are then purged from the file. Files should be reorganized periodically to ensure that your system runs efficiently.

For example, when you select a part or workcenter for deletion through the online maintenance conversations, MAC-PAC does not actually remove the record from the file. Instead, it marks the record as inactive. You can reactivate the record until the file is reorganized. When the file is reorganized, the record is physically purged from the file.

In addition to the records you have marked for deletion, file reorganization looks for other records that need to be deleted. For example, all records past the purge cutoff date on history files are deleted during a reorganization. Because this information is removed permanently from the file, it is important to back up data and run audit trails before reorganizing the file.

You must determine how often each file needs to be reorganized/purged. This decision will be affected by the amount of disk space you have, the CPU time currently required to run the system, and how much historical data needs to be available for viewing. Most installations decide to reorganize and purge their files once a month.

How to Reorganize and Purge the MAC-PAC Database

The following steps should be performed to reorganize or purge the database.

If You Are Using the Recovery Management Module

Run Audit Trails	Step 1.	Run all audit trail reports in the software. Audit trails occur throughout the system and are selected from the application menus. (The Reference File audit trail is selected through the Processing Menu.) Note that running an audit trail deletes it from the system unless it is necessary for Inventory Accounting.
Check for Failed Transactions	Step 2.	Select the Off-line option on the Transactions Recovery Management Menu. If no off- line transactions have failed, the Recovery Management Menu is redisplayed with the Option field blank. If any off-line transactions have failed, the Failed Off-line Transactions List screen is displayed. See the Recovery Management section of this manual for a description of this screen and for the necessary recovery procedures.
Change Journal Receiver	Step 3.	Select the Change Journal Receiver option on the Recovery Management Menu. The system automatically generates a new receiver.
Display Journal Attributes	Step 4.	Select the Display Journal Attributes option on the Recovery Management Menu. The attributes for the journal receivers are displayed. The system-assigned name of any receiver that is to be backed up should be noted.
Back Up Journal	Step 5.	The journal receiver should be backed up whenever the journal receiver is changed. To back up a journal receiver, select the Back Up Receiver option on the Recovery Management Menu. The Journal Receiver Backup screen is displayed. A sample of this screen is found in the Recovery Management section of this manual. Enter the name of each journal receiver that is to be backed up and press the ENTER key. The Recovery Management Menu is redisplayed.
Delete Journal Receiver	Step 6.	Select the Display Journal Attributes option on the Recovery Management Menu. Press F15 to display the receivers directory. Then type 4 (delete) next to the receiver to be deleted and press ENTER.
Back Up the Database	Step 7.	Select the Back Up Masters option on the Recovery Management Menu.
Reorganize the Manufacturing and Distribution Files	Step 8a.	Select the File Support option from the System Processing Menu. Then select which files need to be reorganized. Refer to the How to Purge Manufacturing and Distribution Files section for more information. Or submit the File Reorganization Master program, CF011E. Refer to the File Reorganization Master section for instructions on how to run this program.
Clear Financial Files	Step 8b.	Use the AR, AP, and GL menus to select the files to be cleared/purged. Refer to the How to Purge Financial Files section for more details.
Change Journal Receiver	Step 9.	Select the Change Journal Receiver option on the Recovery Management Menu. The system automatically generates a new receiver.
Delete Receiver from Step 10.	Step 10.	The number of the receiver to be deleted is 2 less than the number of the current receiver. Select the Display Journal Attributes option on the Recovery Management Menu. Press F15 to display the receivers directory. Then type 4 (delete) next to the receiver to be deleted and press ENTER.
Continue with Normal Processing	Step 11.

If You are Not Using the Recovery Management Module

Run Audit Trails	Step 1.	Run all audit trail reports in the software. Audit trails occur throughout the system and are selected from the application menus. (The Reference File audit trail is selected through the Processing Menu.) Note that running an audit trail deletes it from the system unless it is necessary for Inventory Accounting.
Back Up the Database	Step 2.	Select the Back Up Masters option on the Recovery Management Menu.
Reorganize the Manufacturing and Distribution Files	Step 3a.	Select the File Support option from the System Processing Menu. Then select which files need to be reorganized. Refer to the How to Purge Manufacturing and Distribution Files section for more information. Or submit the File Reorganization Master program, CF011E. Refer to the File Reorganization Master section for instructions on how to run this program.
Clear Financial Files	Step 3b.	Use the AR, AP, and GL menus to select the files to be cleared/purged. Refer to the How to Purge Financial Files section for more details.
Continue with Normal Processing	Step 4.

How to Purge the Manufacturing and Distribution Module’s Files

Manufacturing and distribution files are reorganized using the File Support option on the System Processing Menu. You select which file is to be reorganized. Or, the files can be reorganized by the File Reorganization Master program, CF011E. Refer to the File Reorganization Master section for instructions on how to run this program.

MAC-PAC supports three types of reorganization:

· File Reorganized Directly. When you select the file to be reorganized, any records flagged for deletion are removed from the file. The RGZPFM (Reorganize Physical File Members) command is submitted directly for the selected file.

· File Reorganized through Purge Program. All records in a file that are beyond the horizon date (or meet other qualification parameters) are deleted. An RPG program is called to mark records for deletion. You may specify if you want the purge program to remove the logical file members of all affected physical files before reorganizing the physical files. After the files are reorganized, the logical file members will be readded. To take advantage of the remove logical file members feature, you must set the data area, RMVLOGICAL, to '1'. If RMVLOGICAL is a value of anything other than '1', the RGZFM will be performed with the logical files intact.

· Related Files Reorganized. When one of these files is selected for reorganization, the system reviews the file and marks records for deletion if they are beyond the horizon date (or meet other qualification parameters). The system then determines which related files need to be updated. All files are then purged at the same time. An RPG program is called to mark records for deletion and determine related files.

Depending on which type of reorganization is appropriate for the selected file, the RGZPFM command is executed for the file or an RPG program is run to perform the reorganization. The method used for each file is specified in the Control Language section of the Technical Support Manual. A list of RPG programs used in the reorganization process is also included in that section.

File Reorganization Master

The File Reorganization Master program, CF011E, will automatically submit the reorganization jobs for any files that you select. To select a file to be reorganized by the File Reorganization Master program, perform the following steps:

a) Enter Reference File Maintenance for category E38.

b) Enter the file name of the file you want to reorganize automatically.

c) On the change screen, set the Auto Reorg flag to 'Y'. Enter a value in the Horizon Days field and a calendar code in the Calendar Code field. The horizon days and calendar code will be used to determine the horizon date for the reorganization program. Note that the horizon date will be calculated by subtracting the horizon days on Reference File category E38 from today's date on the shop calendar specified on Reference File category E38.

d) Enter the System Processing Menu and choose option 11 - Submit Auto Reorganize Processing. This will call the File Reorganization Master CL program, CF011CLP. This CL program will call the File Reorganization Master program, CF011E.

e) The File Reorganization Master program will read through all records on Reference File category E38. If the Auto Reorg flag is 'Y', it will retrieve the corresponding record on Reference File category L34 and submit the reorganization.

How to Purge the Financial Module’s Files

Most financial files are purged through options on the financial application menus, rather than through the File Support Menu. The following programs are used to purge financial files. For more information about these programs, refer to the corresponding program description manuals.

Accounts Receivable

Both of the AR programs are accessed through the Data Base Reports Selection option on the Accounts Receivable Menu. The cutoff date for purging is entered on the selection screen.

· Data Base Purge (AR770P). Reorganizes the following files:

- Customer Master Files (ARCUSTP, ARADDRP, and AR640M). These files contain customer information and all addresses for the customer. The information is deleted for all customers that were marked for deletion using the Customer Master Maintenance conversation.

- Open Item Files (AROPENP, ARDISTP, ARCMTSP, and ARLOTP). These files contain the open item detail, header, distribution, comment, and lot master information. Note that an open item will be deleted only if the document amount is zero and the last maintenance date (OILMDT) is before or on the purge date. The purge date is retrieved from Reference File category 355. If an open item is deleted, the related open item header, distribution, comment, and lot master records are deleted.

- Open Payment Files (ARAPPLP and ARPMNTP). Note that a payment is only deleted if the unapplied payment amount is zero and the last maintenance date (PHLHDT) is before of on the purge date. The purge date is retrieved from Reference File category 335. If a payment is deleted, its related payment header and application records are deleted. If Draft Management is installed, any draft or bank remittance associated with the payment must also be eligible for deletion.

- Draft Remittance Files (DMDRFP and DMBKRM). Payment must have been remitted prior to the purge date or have a status of canceled for a bank remittance to be purged. For a draft to be deleted, it must have a status of: accepted, rejected, claimed back, or unpaid. It must also have a due date + credit date combination of less than the purge date.

· Subsystem Account Purge (AR775P). Reorganizes the Accounting Transaction File (AR510AP). This option purges all records that have been processed and sent to the General Ledger.

Accounts Payable

All Accounts Payable purge programs are accessed through the Database Purge/Clear Select option on the Accounts Payable Menu. When you select this option, you can specify which of the programs should be run. The purge programs are run interactively.

· Open Item/Payment Purge (AP010E). Reorganizes the following files:

Open Item Header	Standing Payable Header
Open Item Detail	Standing Payable Detail
Comment	Standing Payable Comment
Payment Header	Tentative Open Item Header
Application	Tentative Open Item Detail
	Tentative Open Item Comment

Note : Open items are deleted only if it is fully paid and matched to payments that are not outstanding. If a payment applies to more than one invoice, all invoices must be fully paid or closed. If an open item or void payment record is closed, it will be purged if it falls within the purge cutoff date.

Note: Payments (and their associated invoices) are also purged only if they have been reconciled. Therefore, before running AP010E, you should perform the following steps:

a. Clear any payments through the Clear Payment Entry Conversation. To clear a payment enter a status of 'C' (cleared) and a cleared date.

b. Reconcile the payments using the Bank Reports Selection Conversation. You must choose the option for a final/post bank reconciliation. This batch job submits the bank reconciliation reports and updates the payment status to 'R'. Before final posting, you should perform a trial post and resolve any issues.

Once the payments have been reconciled, AP010E will purge all voided payments that have been reconciled, all payments (and their associated invoices) that have been reconciled and have a payment date before the cutoff date, and all closed invoices where the accounting date is less than the cutoff date. The cutoff date is specified on Reference File category 207. If an open item was partially paid by one check that has been reconciled and partially paid by another check that has not been reconciled, neither the invoice nor the reconciled check will be purged.

· Accounting Transaction File Purge (AP500E). Purges the Accounting Transaction File (AP100AP). A transaction is purged only if it has already been distributed to the General Ledger and its accounting date is less than the cutoff date. The cutoff date is specified on Reference File category 207.

· CL Program to Clear and Reorganize Audit Trails (AP355C). This program clears all AP audit trail files, reorganizes the Currency Master File (CF240M), and clears the Unused Internal Document Number (APUIDP) and Unused Internal Payment Number (APUIPP) files. The following audit trails are cleared:

· Open item Header (APINHBP)

· Open Item Detail (APIVDBP)

· Payment Header (APPYHBP)

· Payment Applications (APPYABP)

· Bank Master (CFBKMBP)

· Bank Account Master (CFBAMBP)

· Bank Account Payment Type (CFBPTBP)

· Currency Master (CF240AP)

· Vendor Master (AP60TAP)

General Ledger

Files are purged using two options on the General Ledger Menu:

· History Maintenance. Allows the following files to be selected for purging.

Screen Option	Files Purged	Data to Be Entered	Program Called to Perform Purge
Delete Posted Journals	GLCMTRP1 GLCMTRP2 GLJHDRP	year, company, location	GL950P
Remove Master File History	Single Currency: GLHISTP1 GLHISTP2 Multi-Currency: GLHISTP3	year, company, location	GL950P
Delete Master Files	Single Currency: GLFILEP3 GLFILEP4 Multi-Currency: GLMLTP1	year, company, location	GL950P
Delete Account/ Center	GLFILEP1 GLFILEP3 GLCMTRP1 GLCMTRP2 GLFILEP4 GLMLTP1	company, location	CF560E

· Clear Files. This option is used to clear the General Ledger audit trails. You select which audit trails are to be cleared, after which GL986C is called to purge the files. The following options can be selected for purging:

Screen Option	Files Purged
Account/Center Master File Audit Trail	CF220BP1 CF220BP2 CF220BP3
Budget Master File Audit Trail	GL800AP1 GL800AP2
Reference File Audit Trail	CF900AP
Currency Rate Master Audit Trail	CF249BP

Sequence for File Purging

Figure 1 indicates the sequence we recommend for purging your files.

Note: You should reorganize the three Planning Action Report files before submitting the Requirements Planning Generation job:

· RP100AP1 - Planning Action Report Header

· RP100AP2 - Planning Action Report Supply Detail

· RP100AP3 - Planning Action Report Demand Detail

Sequence		Menu Option	Module	Area Affected
Preparation
A		Various	All	Delete/deactivate unwanted entities
B		Period End Update (IAMENU)	IA	Close IA month end
C		Various	All	Run all audit trails
D		Recovery Management (CFSPMENU)	RM	Change receiver, backup database
Purge Closed Orders
A		IC100M	IC	Manufacturing Orders + others
B		PO210M1	PO	Receipts
C		PO180M1	PO/IC	Purchase Orders + others
D		TC100M1	TC	Transfer Orders
E		OP120AP1	OP	Shipping/Billing Workfile
F		OP100M1	OP/IC	Sales Orders + others
G		OP100M5	OP/IC	Blanket Order Schedule
H		OP140M	OP/IC	Contract Price
I		OP170M1	OP/IC	Quote Orders
J		OP690AP	OP	Bill of Lading Transaction
K		GA200M	SYNCHRO	Offset Releases
L		GA120M3	SYNCHRO	Endorsements + comments
M		GA100M1	SYNCHRO	Delivery Point
N		GA105M	SYNCHRO	Exception Dates
O		GA120M1	SYNCHRO	Blanket Orders + others
P		GA120M2	SYNCHRO	Part/Delivery Point
Q		GA210M1	SYNCHRO	Delivery Request + others
R		PO160M1	PO	Requisitions
S		TC110M1	TC	Transfer Requisitions
T		GA230M1	SYNCHRO	Picker Header + others
U		TC510AP	TC	Audit Trails
V		POAUTR	PO	Audit Trails
Purge Inventory Balances, Report Requests & Transaction History
A		IC140M	DE	Warehouse Balance
B		IC150M	IC	Lot Control
C		EC170M	EC	Configuration Assignment
D		IC020AL	IC	Transaction History
E		SF020AL	SF	Transaction History
F		IC310AP	IC	Report Requests
G		SF310AP	SF	Report Requests
H		IA300AP1	IA	Cost Generation Requests
I		IA320AP	IA	Report Requests
J		CP310AP	CP	Report Requests
K		SA310AP	SA	Report Requests
Purge Demand and Production
A		IC170M	DE	Warehouse Description
B		MS110M	MS	Planned Routings
C		ME100M	ME	Workcenters
D		MS100M	MS	Key Facilities
E		ME140M	ME	Departments
F		DE140M	DE	Stock Locations
G		MS120M	MS	Production Plan
H		MS150M	MS	Projected Demand
I		MS160M	MS	Tentative Master Schedule
J		JT100AP	JT	Production Schedule Workfile
K		JT100M	JT	Flow Authorizations
L		JT180M	JT	Production Center
M		DE130M	DE	Containers
N		DE170M	DE	Revision Levels
O		CB540AP	CB	CONBON Card Count
P		BCTRN	BC	EDC Transactions
Purge Static Data (Less Frequent)
A		ME120M	ME	Routings
B		DE120M	DE	Product Structures
C		DE100M	DE	Part Master, etc.
D		DE160M	DE	Engineering Change
E		DE210M	DE	Part Comments
F		OP120M	OP	Sales History
G		OP210M	OP	Customer/Item
H		see AR menu	OP/AR	Customer
I		see OP menu	OP	Sales Stats Post
J		PO140M1	PO/AP	Item/Vendor
K		APMASTP	PO/AP	Vendor Master
L		BD100M	BD	Document Master
M		BD120M	BD	Document Structure
N		EC100M	EC	Standard Rule
O		EC110M1	EC	Screen Rule
P		EC120M1	EC	Matrix Rule
Q		EC140M	EC	Configuration Code Master File
R		EC160M	EC	Template Master File
S		CF160M	OP/AR	Carrier Master File
T		JC100M	JC	Job Master
	Purge Financials				See AP & GL & AR menus
	Purge Recovery Management
	Asynchronous Workfile Purge

Suggested Sequence for File Purging