PeopleSoft Enterprise Inventory 9.1 PeopleBook

Purging and Archiving Inventory Data

This chapter provides an overview of the purge and archive processes and discusses how to:

Purge inventory data.
Generate flat file formats for purging routines.
Archive shipping and pegging history.

Understanding the Purge and Archive Processes

PeopleSoft Inventory maintains transaction and static data for both reporting and transactional purposes. For organizations with large transaction volumes, retaining historical data in the PeopleSoft system tables can require additional hardware for data storage, cause reduced processing speed, and affect efficiency in reporting information. To avoid these potential problems, you can schedule the Inventory Data Purge process (IN_PURGE) and the Data Archive Manager to remove or archive historical or obsolete data from system tables based on criteria that you define.

Purging Inventory Data

You can schedule logical purging process runs of the various purging routines by setting up multiple requests for different purge routines under one run control ID. To purge obsolete or historical data from the PeopleSoft Inventory system tables, schedule a run for the appropriate purge routine using the pages in the Inventory Purge Process component.

Warning! The purging function is very powerful. Improper use of the Inventory Data Purge process can result in the unintentional loss of data. Use the purging process only after you have been properly trained to use it. Become completely familiar with the purging process and the use the available flat-file option to conserve the historical data. Before purging, you should always perform a backup.

This section discusses how to:

Specify purge process request parameters.
Specify parameters for purge routines.
Purge cost and accounting history.
Purge counting events.
Purge inactive items.
Purge physical inventory history.
Purge replenishment history.
Purge storage areas and storage locations.

Pages Used to Purge Inventory Data

Page Name	Definition Name	Navigation	Usage
Purge Inventory Data	RUN_IN_PURGE	Inventory, Purge Inventory Data	Specify parameters for the Inventory Data Purge process.
Purging Parameters	RUN_IN_PURGE_PARM	Inventory, Purge Inventory Data, Purging Parameters	Select parameters for a specific purge routine, limiting the rows of data that the Inventory Data Purge process removes.

Specifying Purge Process Request Parameters

Access the Purge Inventory Data page (Inventory, Purge Inventory Data).

Flat File

Select to copy the data selected for purging to a comma-separated flat file. With this option, you do not purge data from the PeopleSoft Inventory system. You can review the data in the flat file using third-party tools before purging it from the system tables.

Flat File/Purge

Select to copy data selected for purging to a comma-separated flat file and then remove the data from the system tables. When you save the page, a warning message indicates that you are about to delete data from the system.

Purge

Select to remove the selected data from the system tables without writing the data to a flat file. When you save the page, a warning message indicates that you are about to delete data from the system.

File Directory

Enter the file directory path where the flat file is to be stored. If you selected purge, this field is not available.

File Name

Enter a file name prefix for the flat file. If you selected purge, this field is not available. The system generates the rest of the file name as follows:

[prefix]_[record name]_YYYYMMDD_[sequence number], where:

prefix is the value that you enter for file name.

record name is the object name of the record that you selected to purge.

YYYYMMDD is the year, month, and day that the flat file was created.

sequence number is a system-generated unique number.

A .csv (comma-separated format) extension is appended to the file name. For example, if you enter PURGE as the file name and purge the BU_ITEMS_INV record on June 16, 2000, the associated flat file is named PURGE_BU_ITEMS_INV_2000616_1.CSV.

Character Set

Select ANSI or Unicode to determine the character set of the data in the flat file.

Specifying Parameters for Purge Routines

Access the Purging Parameters page (Inventory, Purge Inventory Data, Purging Parameters).

Purging Selection

Specify a purge routine. Values are:

Costing and Accounting History

Counting Events

Inactive Items

Physical Inv History (physical inventory history)

Replenishment History

Storage Areas/Locations

Note. Each purging selection uses a set of specific parameters. Purging parameters that do not apply to the purging selection are unavailable for entry. The following sections provide more detail about the purging parameters for each purging selection.

Purging Cost and Accounting History

Select the Costing and Accounting History purge routine to remove obsolete stock movement transactions in the TRANSACTION_INV table in PeopleSoft Inventory and the associated costing records and accounting lines in PeopleSoft Cost Management. To maintain referential integrity across applications, the routines group related records using the putaway transaction key. All records that match the key are analyzed for purge eligibility. No records in the group are purged until all records in the group are eligible for purging.

Enter the following parameters:

Book Name	The name of the cost book to purge.
Cutoff Date	The cutoff transaction date. All records in the group matching a given putaway key must have transaction dates before the specified cutoff date before you can purge any of the records.
Include Serial/Lot Trace Trans	Select this check box to purge lot or serial tracking items from the TRANSACTION_INV record. Once this data has been purged, the lot and serial genealogy inquiries cannot display this information.

Rules Used to Determine Eligibility for Purging

The following rules identify which TRANSACTION_INV records and associated costing records and accounting lines matching a given putaway key are eligible for purging:

The TRANSACTION_INV record must have a zero on-hand quantity.
The TRANSACTION_INV record must be costed.
The accounting lines for all relevant costs and variances associated with TRANSACTION_INV records must exist.

This includes accounting lines representing depletions against original receipt quantities.
The costs associated with the shipment and depletion of a consigned item in the TRANSACTION_INV table must be vouchered.
The TRANSACTION_INV records representing stock transfers to business units can be purged only if the transfer has been received or canceled and if accounting lines for all relevant costs and variances exist for both the source and destination business units.
The TRANSACTION_INV records representing a shipment on behalf of another business unit can be purged only if all relevant ship-on-behalf accounting lines exist.
The TRANSACTION_INV record and all associated costing transactions and accounting lines must have a transaction date before the cutoff date.

For example, if a putaway transaction has a date before the cutoff date but the depletion transaction representing the shipment of the putaway quantity occurs after the cutoff date, the putaway transaction and associated costing and accounting lines are not eligible for purging.
All accounting lines for a TRANSACTION_INV record must be posted to a PeopleSoft General Ledger journal.

For putaway transactions, this includes accounting lines representing depletions against the original receipt quantity.

Tables Accessed and Updated

These tables are accessed and update by the Costing and Accounting History purge process:

TRANSACTION_INV
CM_ACCTG_LINE
CM_TRANSACTION
CM_RECEIPTS
CM_RECEIPT_COST
CM_VARIANCES
CM_VARIANC_COST
CM_DEPLETION
CM_DEPLETE
CM_DEPLETE_COST
CM_TRANVAR
CM_TRANVAR_COST
CM_COST_ADJ
CM_INTRANSIT
CM_INTRAN_BOOK
CM_SHIPONBEHALF
CM_SHIPONB_BOOK
CM_PERPAVG_COST
CM_PERDAVG_COST
CM_ACTUAL_COST
CM_ACTUAL_BOOK
CM_MATCHED_COST

Purging Counting Events

Select the Counting Events purge routine to remove finished and canceled counting events for cycle and physical inventory (wall-to-wall) counts.

Enter the following parameters:

Cancelled Counting Events Only	Purge only canceled counting events.
Date From and Date To	The date range for the creation event IDs that you want to purge. Date from is the date that the counting event was created.
All Counting IDs	Include all eligible counting event IDs in the purge process.
From Count ID and To Count ID	The range of counting events that you want to purge. To specify a single counting event ID, enter the same count ID for the To Count ID field.

Rules Used to Determine Eligibility for Purging

The following rules identify which counting events are eligible for purging:

All counting events must have a status of Finished or Canceled.
If the purge is limited by date, the counting events must have a creation date (count start date) that is equal to the specified date or that falls within the specified date range.
If the purge is limited by counting event ID, the counting events must have a counting event ID that is equal to the specified ID or that falls within the specified range of IDs.

Tables Accessed and Updated

These tables are accessed and update by the Purging Counting Events process:

COUNT_INV
COUNT_HDR_INV

See Also

Counting Inventory Stock

Purging Inactive Items

Select the Inactive Items purge routine to remove item records with an Inactive status from both the business unit and setID levels.

Enter the following parameters:

SetID

Inactive items are removed from the setID level only after they are purged from all business units associated with the setID.

Item List Option

Specify whether inactive items should be removed from a list of:

All items.
A specific category of items.
A specific family of items.
A specific group of items.
A range of items.
A single inactive item.

Rules Used to Determine Eligibility for Purging

Use the following rules to identify which items are eligible for purging:

The item must have a status of Inactive at the business-unit level.
To be purged from the setID, an item must have a status of Inactive at the setID level and must be purged from all business units associated with the setID.
The item ID must not be referenced in the Order Management Product Definition table (PROD_DEFN).

If you need to purge an item that has a product ID associated with it, remove the reference to the PeopleSoft Inventory item ID in the Order Management Product Definition table. You can delete the item reference on the Product Definition - Definition page or for product kits, on the Product Kit Summary page in PeopleSoft Order Management.

Tables Accessed and Updated

These tables are accessed and update by the Purging Inactive Items process:

AUTO_SERIAL_NUM
BRAND_NAMES_INV
BU_ITEMS_CONFIG
BU_ITEMS_INV
BU_ITEMS_WTVOL
BU_ITEM_SUB
BU_ITEM_UTIL_CD
CE_FCST_PUR
CE_ITEMCOST
CE_ITEMCOST_DT
CE_ITEMEXPD
CE_ITEMEXPH
CP_CONFIG
DEFAULT_LOC_INV
DF_SETUP_IT_INV
FXD_BIN_LOC_INV
INV_ITEMS
INV_ITEMS_LANG
INV_ITEM_PHOTO
INV_ITEM_SPEX
INV_ITEM_UOM
INV_TRN_CST_TAO
INV_TRN_DMD_TAO
INV_TRN_SUM_TAO
ITEM_ALIAS_TBL
ITEM_MFG
ITEM_SPEX_PUR
ITM_CAT_VNDR_WS
ITM_SHIPTO
ITM_SHIPTO_VNDR
ITM_VENDOR
ITM_VENDOR_LOC
ITM_VENDOR_MFG
ITM_VNDR_LOC_MC
ITM_VNDR_UOM
ITM_VNDR_UOM_PR
ITM_VND_RTV_FEE
LOT_CONTROL_INV
MARKUP_ITEM_INV
MASTER_ITEM_TBL
MASTER_ITM_LANG
PICK_ZONE_INV
PRICE_ADJ_DTL
PRICE_ADJ_RULE
PRICE_ADJ_SET
PTWY_IT_RULE_IN
PTWY_ZONE_INV
PURCH_ITEM_ATTR
PURCH_ITEM_BU
PURCH_ITEM_LANG
STD_PRICE_INV
STOCK_PERIOD_IN
SUBSTITUTE_ITM
TRANS_UNIT_IT
UOM_TYPE_INV
CM_ITEM_METHOD
IN_VMI_ITEMS

See Also

Inactivating Items

Purging Physical Inventory History

Select the Physical Inv History purge routine to remove zero-quantity stock records from PeopleSoft Inventory's PHYSICAL_INV table for counting inventory. Stock records in the PHYSICAL_INV table track the quantity of an item stored in a specific storage location within a business unit. These records are keyed by item ID, storage location, lot ID, serial ID, staged date, container ID, and unit of measure.

Enter the following parameter:

Item List Option

Specify whether stock records for items should be removed from a list of all items or a range of items, or whether a single inactive item should be removed. You can also specify the records to be removed by item category, item family, or item group.

Rules Used to Determine Eligibility for Purging

The following rules identify which item stock records are eligible for purging:

The stock record must have an item quantity of zero (PHYSICAL_INV qty = 0, not negative).
The stock record cannot exist in an open counting event.
The stock record cannot have hard allocations against it in the DEMAND_PHYS_INV table for orders that have not been depleted or canceled.
The stock record cannot have hard allocations against it in the DEMAND_PHYS_INV table for open or in-transit interunit transfers.
The stock record cannot have hard allocations against it in the DEMAND_PHYS_INV table for sales orders, intercompany transfers, or non-inventory item demand lines that have not been picked up by PeopleSoft Billing.
The stock record cannot have hard allocations against it in the DEMAND_PHYS_INV table for intrastat orders, for which intrastat documentation has not been generated.

Tables Accessed and Updated

The PHYSICAL_INV table is accessed and updated by the Purging Physical Inventory History process.

See Also

Monitoring Inventory Quantity Balances

Purging Replenishment History

Select the Replenishment History purge routine to remove obsolete replenishment requests from the PeopleSoft Inventory tables. This routine removes replenishment request records within a specified date range for a business unit.

Enter the following parameters:

Date From and Date To	The date range for replenishment requests that you want to purge. This is the date that the replenishment request was created.
Adhoc Requisitions Only	Select to purge ad hoc replenishment requests only.

Rules Used to Determine Eligibility for Purging

The following rules identify which replenishment requisitions are eligible for purging:

The replenishment request must have been processed by PeopleSoft Purchasing or canceled.
The replenishment request must have been created on a date that falls within the specified date range.

Tables Accessed and Updated

This table is accessed and update by the Purging Replenishment History process: REPLEN_RQST_INV.

See Also

Replenishing Inventory

Purging Storage Areas and Storage Locations

Select the Storage Areas/Locations purge routine to remove records for closed storage areas or closed storage locations from a business unit. You can limit the purge to a specific location or group of locations by specifying a storage area and up to four levels of storage location detail.

Enter the following parameter:

Storage Area

Designate the storage area and up to four levels of storage location detail. All storage locations that meet the criteria that you specify are checked for purge eligibility. For example, if you enter only a storage area, all storage locations defined for that area are checked for purge eligibility.

Rules Used to Determine Eligibility for Purging

The following rules identify which items are eligible for purging:

The storage areas and storage locations must be closed.
All storage locations within a storage area must be purged before the storage area itself can be purged.

Tables Accessed and Updated

These tables are accessed and update by the Purging Storage Areas and Storage Locations process:

STOR_AREA_INV
STOR_LOC_INV

Generating Flat File Formats for Purging Routines

For each purging routine that you schedule, you have the option of writing the data to a flat file, deleting data from the database, or both. The data selected for purging is based on the parameters that you enter and the internal system edits. The internal system edits ensure that referential integrity is maintained, protecting the data integrity of system tables.

Use the flat-file option to confirm which data records are to be purged before running the process in purge mode. In purge mode, you can also write the purged data to a comma-separated flat file. Use this option if you need to retain purged data for a specified period of years for audit purposes. Before running purge routines, use the PeopleSoft Application Designer to verify that the file layout conforms to any ChartField configuration completed in PeopleSoft.

Note. You must use a third-party tool to analyze and maintain data written to a flat file. PeopleSoft does not provide a flat-file editor. Also, PeopleSoft offers no mechanism for restoring purged data to the system tables from a flat file.

To generate the flat-file formats for the purging routines, print the record definitions for the tables updated by each routine by using PeopleSoft Application Designer.

Note. The flat files generated by the purging routines match the record definitions of the updated tables as the tables are shipped. Save a copy of the original record definitions to use as the flat-file definitions before making modifications to the records. If changes have been made to the original tables, be sure that the delivered file layout object for the table has been properly updated.

See Also

Enterprise PeopleTools PeopleBook: PeopleSoft Application Designer

Archiving Shipping and Pegging History

Use the Data Archive Manager in PeopleTools to move shipped demand lines and peg chains from the transaction tables to the history tables.

Archiving Shipping History

Use the Data Archive Manager in PeopleTools to remove shipped and canceled demand lines from a business unit with ship dates that fall within a specified date range.

Rules Used to Determine Eligibility for Archiving Shipping History

The following rules identify which demand lines are eligible for archiving:

The demand line must be depleted by the Deplete On Hand Qty (Depletion) process (IN_FUL_DPL) or must have been previously canceled.
The demand line for sales orders, intercompany transfers, and non-inventory item demand lines must be picked up by PeopleSoft Billing.
If the demand line requires intrastat reporting, these reports must be generated.
The demand line must have a ship date that falls within the specified date range.
All demand lines of an order must be eligible for archiving before any of the demand lines for that order can be archived.
All order lines must be archived before order header records (ISSUE_HDR_INV and MSR_HDR_INV) can be archived.
All peg chains must be archived before their related pegged demand lines can be archived.

Tables Accessed and Updated for Shipping History

The Data Archive Manager archives and then purges the following tables:

IN_DEMAND
IN_DEMAND_ADDR
IN_DEMAND_BI
IN_DEMAND_CMNT
IN_DEMAND_TRPC
IN_DEMAND_HASH
SHIP_HDR_INV
SHIP_SERIAL_INV
SHIP_CMNT_INV
IN_DELIVERY_ORD
DEMAND_PHYS_INV
ISSUE_HDR_INV
MSR_HDR_INV
LOT_ALLOC_INV
EST_SHIP_INV
EST_SHIP_IN_TMP
SHIP_CNTR_INV
SHIP_CNTRLS_INV

The Data Archive Manager purges the historical data in the IN_SHIP_DOC_TRK table without archiving it. Data from this file cannot be recovered once it is purged.

Archiving Pegging History

Use the Data Archive Manager in PeopleTools to move peg chains in the IN_PEGGING table to its history table, IN_PEG_HIST. This routine removes completed and canceled peg chains within a specified date range.

Table Accessed and Updated for Pegging History

The IN_PEGGING table is accessed and updated by the Data Archive Manager.

Pages Used to Archive Shipping and Pegging History

Page Name	Definition Name	Navigation	Usage
Archive Data To History	PSARCHRUNCNTL	PeopleTools, Data Archive Manager, Archive Data To History	Use this page to submit batch Application Engine jobs to move shipping data and peg chains between the transactional tables and the history tables.
Define Archive Query Binds	PSARCHRUNQRYBND	Select the Define Binds link on the Archive Data To History page.	Enter the bind variables for shipping and pegging history.

Using the Data Archive Manager

Access the Archive Data To History page (PeopleTools, Data Archive Manager, Archive Data To History).

PeopleSoft Data Archive Manager provides an integrated and consistent framework for archiving data from PeopleSoft applications. Using the Archive Data To History page you can define a job to move shipping data and peg chains from transactional tables to history tables.

Archive Template	Select the archive template, INDEMAND, to move shipping data from transactional tables to history tables. Select the archive template, INPEG to move peg chain data to the history table.
Process Type	Select the type of action to be performed, the options are: Delete: Select to delete data from transaction tables. Data rows will be deleted from the transaction tables only if they've already been archived in the history tables. Remove from History: Select to delete data from the history tables. Rollback: Select to copy data from history tables back to transaction tables. Selection: Select to copy data from transaction tables to history tables.
Selective Query	Specify the archive query defined within the archive template to use at runtime. If there are bind variables, you will be prompted to enter the bind variables when you click the Define Binds link. For the INDEMAND archive template, there is one archive query, IN_ARCH_DMD_BU_DT_QUERY (archive demand lines by business unit and date). For the INPEG archive template, there is one archive query, IN_ARCH_PEG_QUERY (archive pegging). Once you enter the archive query, the Define Binds link is displayed.
Define Binds	Select this link to access the Define Archive Query Binds page where you can enter the bind variables for shipping and pegging history.
Batch Number	For archiving processes that are based on data in the history tables (such as delete data from transactional tables, copy data from history tables to transactional tables, and delete data from history tables), you will be prompted to enter an Archive Batch Number.
Commit at End	Data Archive Manager processes data using set-based processing, but doesn't issue any commits to the database server until the entire process has completed. For example, if your Archive Template is defined with Pre- and Post- Application Engine programs, the Data Archive Manager first executes the Pre-Application Engine program, then it processed all of the tables in the Archive Template, next it executes the Post-Application Engine program. Upon successful execution of all these steps, a commit is issued to the database. When you select this option, the set-based processing option is automatically selected as well.
Set-Based Processing	Data is processed by passing a single SQL statement per record to be archived to the database server. A commit is issued to the database server after successful completion of each SQL statement.
Row-Based Processing	Data Archive Manager processes data one row at a time using PeopleCode fetches. This method of archiving is more memory intensive and takes longer than set-based processing. However, for archiving processes that contain significant amounts of data, row-based processing could be used to reduce adverse affects on the database server. Row-based processing is appropriate when you're archiving large amounts of data from transactional tables and wish to issue commits more frequently. If you select this option, you must enter a commit frequency.
Commit Frequency	Specify the number of rows to process before issuing a commit to the database.
Audit Row Count	Select to audit the number of rows in the record that meet the criteria. This number is displayed in the Number of Rows field on the Audit Archiving page

See Also

Enterprise PeopleTools PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager"

Flat File	Select to copy the data selected for purging to a comma-separated flat file. With this option, you do not purge data from the PeopleSoft Inventory system. You can review the data in the flat file using third-party tools before purging it from the system tables.
Flat File/Purge	Select to copy data selected for purging to a comma-separated flat file and then remove the data from the system tables. When you save the page, a warning message indicates that you are about to delete data from the system.
Purge	Select to remove the selected data from the system tables without writing the data to a flat file. When you save the page, a warning message indicates that you are about to delete data from the system.
File Directory	Enter the file directory path where the flat file is to be stored. If you selected purge, this field is not available.
File Name	Enter a file name prefix for the flat file. If you selected purge, this field is not available. The system generates the rest of the file name as follows: [prefix]_[record name]_YYYYMMDD_[sequence number], where: prefix is the value that you enter for file name. record name is the object name of the record that you selected to purge. YYYYMMDD is the year, month, and day that the flat file was created. sequence number is a system-generated unique number. A .csv (comma-separated format) extension is appended to the file name. For example, if you enter PURGE as the file name and purge the BU_ITEMS_INV record on June 16, 2000, the associated flat file is named PURGE_BU_ITEMS_INV_2000616_1.CSV.
Character Set	Select ANSI or Unicode to determine the character set of the data in the flat file.