Archiving Transactions

This chapter provides an overview of the data types available for archiving and an overview of archive objects definitions, PSQuery objects, and template objects and discusses how to:

Archive data.
Generate archive candidate reports.
Restore archived data.
Clear Receivables transaction tables.
Use a SQL script to clear Receivables transaction tables.

Understanding Data Types Available for Archiving

This section discusses:

Data types.
Pending item data.
Payment data.
Posted item data.
Direct debit data.
Action list data.
Statement information, dunning letter information, and overdue charge information.
Posted customer data.
Conversation data.
Customer definition data.
Message log data.
Suggested order of data removal.

Data Types

Most Receivables tables contain data keyed either by business unit or tableSet. The tables keyed by business unit accumulate large volumes of data over time. Eventually, you may not need to access some of the data routinely. Receivables enables you to archive the inactive data on the transaction tables keyed by business unit to the history tables. If you need to access the data later, you can restore it.

The following table lists the tables keyed by business unit. Receivables delivers archive objects to remove only the first six types of data listed in the table: pending item, payment, posted item, statement, action list, and direct debit data using the PeopleSoft Enterprise Data Archive Manager.

Note. To remove the other data types, you must directly remove the records from the database. You can remove the records either by using a SQL script that you write or by creating your own archive objects in PeopleTools and then using the Data Archive Manager.

Type of Data	Description	Tables
Pending item data	Group control, pending item, pending tax information, and pending accounting entries processed by the Receivable Update Application Engine process (ARUPDATE).	PS_GROUP_CONTROL PS_PENDING_ITEM PS_PENDING_VAT PS_PENDING_DST PS_PENDING_TAX PS_PENDING_TAX_DTL PS_PENDING_IT_MRL
Payment data	Deposit and payment information provided during online entry or during the Payment Interface Application Engine process (AR_PAYLOAD).	PS_DEPOSIT_CONTROL PS_PAYMENT PS_PAYMENT_ITEM PS_PAYMENT_ID_CUST PS_PAYMENT_ID_ITEM PS_PAY_MISC_DST PS_PAY_ITEM_MRL PS_EE_DJR_ACCTG_LN
Posted item data	Item information, such as item activity, item tax information, and item accounting entries maintained by the Receivable Update and Aging Application Engine processes (AR_AGING).	PS_ITEM PS_ITEM_ACTIVITY PS_ITEM_ACT_VAT PS_ITEM_DST PS_ITEM_ACTTAX PS_ITEM_ACTTAX_DTL PS_ITEM_AUDIT PS_AR_IPAC_HDR PS_AR_IPAC_SGL PS_AR_IPAC_DETAIL PS_AR_CC_TRAN_HDR PS_AR_CC_TRAN_TBL PS_EE_ITM_ACCTG_LN
Statement data	Customer and item summary data maintained by the Statement Application Engine process (AR_STMTS).	PS_STMT_CUST PS_STMT_CUST_DTL PS_STMT_CUST_BFWD PS_AR_STMT_CUST
Action list data	Actions added to actions lists by the Condition Monitor process (AR_CNDMON) or by entering them on the Item Action page.	PS_ACTION_LST
Direct debit data	Direct debit control and direct debit item data created when you create direct items, run the Receivable Update process, and run the Create Direct Debit process (AR_DIRDEBIT).	PS_DD_CONTROL PS_DD_ITEM PS_DD_ITEM_MRL
Posted customer data	Summary information at a customer level maintained by the Receivable Update and Aging processes.	PS_CUST_AGING PS_CUST_DATA PS_CUST_HISTORY PS_SUBCUST_AGING PS_SUBCUST_DATA PS_SUBCUST_HISTORY
Dunning data	Customer and item summary data maintained by the Dunning Application Engine process (AR_DUNNING).	PS_DUN_CUST PS_DUN_DTL
Overdue charge data	Customer and item summary data maintained by the Overdue Charge Application Engine process (AR_OVRDUE).	PS_OC_CUST PS_OC_CUST_DTL
Conversation data	Summary and detailed information about conversations with customers.	PS_CUST_CONVER PS_CUST_CONV_HDR PS_CUST_CONVER_DTL PS_CUST_CONVER_ATT
Customer Definition data	Required for processing customers.	PS_CUSTOMER PS_CUST_REGN_TYPE PS_CUST_VAT_REG PS_CUST_ADDR_SEQ PS_CUST_ADDRESS PS_CUST_CNTCT_SEQ PS_CUST_CONTACT PS_CUST_CNTCT_TYPE PS_CUST_CNTCT_DOC PS_CUST_CNTCT_CARD PS_CUST_CNTCT_PHN PS_CUST_CREDIT PS_CUST_OPTION PS_CUST_SHIPTO_OPT PS_CUST_SOLDTO_OPT PS_CUST_BILLTO PS_CUST_SHIPTO PS_CUST_CRSPD PS_CUST_CONVER PS_CONVR_DTL PS_KEYWORD_TBL_AR PS_FOLLOWUP_TBL
Message log data	Log messages generated by background processing.	PS_MESSAGE_LOG PS_MESSAGE_LOGPARM

Pending Item Data

The Receivable Update process is the only way to create posted information, such as customer balance and history information, as well as the corresponding item, item activity, item activity taxes, and item accounting entry information.

The tables for pending item data can be populated in many ways:

Through interface programs that convert information from an existing system or bring in information from billing systems.
Through the online Group Entry function, which provides pending item information for the tables.
Through worksheets (payment, maintenance, transfer, direct debit, and draft).
Through Automatic Maintenance Application Engine (AR_AUTOMNT) and Payment Predictor Application Engine (ARPREDCT) processing.
Through overdue charges, item splits, and the unpost function.

Once a group posts, the system updates customer-level posted data and item-level posted data. The system records the data from the pending item data tables in a relational form into the posted item data tables. So the data is no longer needed for its original purpose.

The system does not delete the pending item data. Instead, it marks the data as posted and stores it in its original form. The system continues to use the data in the pending item tables for control and inquiry and for unposting groups. From a control and inquiry perspective, you may want to keep all pending data, even though it has been posted, until all groups from a certain day or time period have been posted.

The most important reason for retaining pending item data is that the system uses it for unposting a group. When you set a group or a payment to unpost, the system utilizes the pending data to create a mirror-image group, one that is opposite in sign from the original. We recommend that you retain pending item data until you are sure that you no longer need to unpost a group or payment.

If you delete pending item data for posted payment groups, you should also delete its associated deposit and payment information.

Payment Data

The payment data tables store payment information that you entered directly online or through the payment interface. Payments are processed when you:

Apply them through a payment worksheet or automatically with Payment Predictor.
Journal them directly to the general ledger.

The end result of the payment application process (whether it occurs online or during background processing) is the transformation of each payment into a group and its components. The system restates the payment data in pending item data format for submission to the Receivable Update process. The system stores the pending item data in the pending item tables.

After you post the group and its components and distribute it to your general ledger, the posted data tables contain all the information necessary to reference payment information for customer questions. For example, you can review the payment application on the Item Activity From A Payment page. The item activity table stores the payment ID.

When all payments in a deposit have been completely posted, you may want to retain the original entered format of the payment data so you can use the inquiry pages to view totals and a summary of the status of deposits and payments received.

You must keep deposit and payment information, in addition to the related group and pending items to unpost a payment. If you archive a deposit and its payments or the associated pending item data, you cannot unpost any of the payments within the deposit.

For directly journalled payments, the accounting entries in the PS_PAYMENT_MISC_DST table must remain in Receivables until the Journal Generator Application Engine process (FS_JGEN) processes them.

A suggested approach for archiving payment data is to select deposits that have been completely posted and processed by the Journal Generator process and whose accounting date matches the archive criteria.

When all relevant conditions for a given deposit have been met, remove the data in all eight payment tables.

Posted Item Data

If the records in the Item family meet certain criteria, you can remove or archive closed item information from these posted item data tables:

PS_ITEM
- Has a closed status and has been closed for a designated amount of time.
- Is not referenced by an active conversation entry that is less than 90 days old.
- Has no associated customer statement detail records.
- Has no associated customer dunning letter detail records.
- Has no associated customer overdue charge detail records.
- Has no associated payment worksheet activity (PS_PAYMENT_ITEM records).
- Has no associated maintenance worksheet activity (PS_WS_ITEM records).
PS_ITEM_ACTIVITY
- Are no longer needed for Unit Activity page inquiry or reporting.
  
  The Unit Activity page and reports show the beginning and ending balances of a business unit and the related financial events. The page and reports are based on the information contained in ITEM_ACTIVITY records.
- No longer have corresponding pending item data.
PS_ITEM_ACT_VAT records that have been posted to the VAT ledger and are no longer needed for audit purposes.
PS_ITEM_DST records that have been distributed to your general ledger.
PS_ITEM_ACTTAX and PS_ITEM_ACTTAX_DTL records that contain tax information that are no longer needed for audit purposes.
PS_ITEM_AUDIT records that contain changes to posted items from item maintenance and are no longer needed.
PS_AR_IPAC_HDR, PS_AR_IPAC_SGL, PS_AR_IPAC_DETAIL records that contain information for the U.S. Department of Treasury for intra-governmental transactions that you no longer need to interface to the U. S. Department of Treasury.
PS_AR_CC_TRAN_HDR and PS_AR_CC_TRAN_TBL records that contain the source transactions for control budgets and are no longer needed.
AR_EE_ITEM_ACCTG_LN records that contain entry event information that has been distributed to your general ledger.

When all relevant conditions have been met for a given item family, remove the data from all thirteen tables at the same time.

You must remove the related CUST_CONVER_DTL records when you remove items and payments. This table ties conversations to items and payments.

Direct Debit Data

The direct debit data tables store information from direct debit groups that you create by running the Create Direct Debits process (AR_DIRDEBIT) and update using a direct debit worksheet. You should keep direct debit data until the direct debit has been paid by the bank. If you archive a direct debit, you can no longer unpost the direct debit group or any of the items paid by the direct debit.

The system archives direct debit data when:

The cash posting date or cancel posting data for the direct debit group is equal to or less than the archive date.
The status of the direct debit group is either Completed or Rejected.

The system does not change the status to Completed or Rejected until your run the Receivable Update process.

Action List Data

Each time you run the Condition Monitor process or add an action for an item on the Item Action page, the system adds an action to the Action List table (ACTION_LST). You can view a list of all actions for a customer, including completed actions on the Action History page. So, you need to determine how frequently you want to archive actions.

The system only archives actions if the actions have:

A date that the action was added that is equal to or less than the archive date.
A status of Cancelled, Completed, System Cancelled, or System Completed.

Statement Information, Dunning Letter Information, and Overdue Charge Information

Each time that you run statements, generate dunning letters, or generate overdue charges, the system updates the relevant information tables.

Because the most recent statement or letter or the second most recent statement letter is probably the one that will most likely be discussed with a customer, you may decide not to keep older statements or letters. When you decide to archive statements or dunning letters, compare the statement date or dunning date to the target archive date.

Note. Receivables only provides archive objects to archive statement tables.

Posted Customer Data

After you post pending items to a customer and age them, the posted customer data tables contain summary data. If you do not use subcustomer qualifiers, none of the three subcustomer-oriented tables contain information.

When no more open items exist in a business unit and customer combination, and after you run the Aging process, the customer aging and subcustomer aging tables do not contain any rows.

The PS_CUST_DATA and PS_SUBCUST_DATA tables contain balance and event information. Unless you know that you will never post to the customer or subcustomer again, do not remove or archive data from these tables.

If you archive these tables, delete rows for a customer only if the customer has no item information and has been inactive for a significant period of time. Do not delete these rows if another business unit has data that you need to maintain for the customer.

The PS_CUST_HISTORY and PS_SUBCUST_HISTORY tables contain one row for each history element for each fiscal year and accounting period. Unless you need history at the business unit level for the period, you can delete or archive history information that you no longer need. The system does not store business unit summary information. Instead, it derives that information by combining customer-level information.

You should compare the fiscal year and accounting period of the data that you selected to delete to your target fiscal year and accounting period. Because the system needs some of the history elements for other elements to work correctly, we recommend keeping all history elements for a given fiscal year and accounting period.

Conversation Data

Conversation information can accumulate rapidly, depending on how you record multiple conversations. You can open a single conversation record and use the Comments field to record multiple conversations by date and time on the Conversations page. You can also use the DateTime field to enter a separate row for each conversation.

If you maintain a single conversation record using the Comments field, the volume remains lower, and you may not need to delete the row until the customer becomes inactive.

If you use more than one row for each conversation, you can remove conversation rows if the conversation:

Has a date older than your target archive date.
No longer requires review.
Has a status of closed.
Does not reference any open items directly by an item reference or indirectly through a purchase order, bill of lading, document, or payment reference.
Does not reference unposted payment information.

The keys for the PS_CUST_CONVER_HDR table are SETID, CUST_ID, CONVER_DTTM_INIT, and CONVER_DT.

You must remove the PS_ CUST_CONVER_DTL records when you remove items and payments. This table ties conversations to items and payments.

Customer Definition Data

The customer definition tables are keyed by setID.

Determining whether a customer is active requires a cross-business perspective. In other words, information contained in these tables may be eligible for removal or archiving if a customer is no longer active in any of the business units that use the tableSet customer definitions. To meet these criteria, a customer cannot have any of the following information:

Customer balance information (PS_CUST_DATA) in any business unit.
Customer aging information (PS_CUST_AGING) in any business unit.
Customer history information (PS_CUST_HISTORY) in any business unit.
Customer conversations (PS_CUST_CONVER) in this tableSet.
Customer conversation references (PS_CUST_CONVER_DTL) in any business unit.
Customer relationships.

For example, the customer cannot be a corporate, remit from, or correspondence customer for another customer.
A reference in a corporate customer tree.
Posted item information in any business unit.

(This information should not exist if the preceding posted customer information does not exist.)
Statement information in any business unit.

(This information should not exist if the preceding posted customer information does not exist.)
Dunning information in any business unit.

(This information should not exist if the preceding posted customer information does not exist.)
Overdue charge information in any business unit.

(This information should not exist if the preceding posted customer information does not exist.)
Pending item data in any business unit (pending item data family).
Saved payment worksheets in any business unit (PS_PAYMENT_ITEM).
Saved maintenance worksheets in any business unit (PS_WS_ITEM).

Note. You must remove data from all the tables at the same time. You can remove Dun and Bradstreet information independently of the other customer tables if it becomes outdated. You may need a separate step to remove effective-dated rows from the PS_CUST_DB table based on a target removal or archive date.

Message Log Data

Whenever a background process runs, such as Receivable Update or Aging, the system creates messages and stores them in the message log tables. The system does not need these messages for subsequent processing, such as unposting a group or a payment. These tables can quickly become quite large, especially if you run a background process in debug mode.

If you decide to archive these tables, compare the date and time stamp in the message log to the archive date.

Suggested Order of Data Removal

When archiving data, remove data in the following order (taking into consideration your removal or archive date requirements):

Pending item data for completely posted groups.
Payment data for completely posted deposits.
Direct debit data for completed or rejected direct debits that are posted.
Inactive conversation data.
Inactive statement data.
Inactive dunning data.
Inactive overdue charge data.
Closed action data.
Closed posted item data.
Inactive posted customer data.
Inactive customer definition data.

Understanding Archive Objects Definitions, Query Definitions, and Template Definitions

Receivables delivers archive object definitions, query definitions, and template definitions to archive:

Pending item data
Payment data
Posted item data
Action list data
Direct debit data
Statement data

You can modify them if needed using the Data Archive Manager. You can also use the Data Archive Manager to create your own archive object definitions, query definitions, and template definitions to archive other types of data.

Archive Object Definitions

An archive object definition identifies the tables that contain the data to be archived and the history tables that will be updated for each table. Receivables delivers these archive objects:

Data Type	Archive Object
Pending item data	AR_ARCH_PENDING_ITEM
Payment data	AR_ARCH_PAYMENTS
Posted item data	AR_ARCH_ITEMS
Direct debit data	AR_ARCH_DIRECT_DEBITS
Action list data	AR_ARCH_ACTIONS
Statement data	AR_ARCH_STATEMENTS

Archive Query Definitions

Each archive query definition defines the selection criteria to archive data from transaction tables. Receivables delivers these archive queries:

Data Type	Archive Query
Pending item data	AR_ARCH_PENDING_ITEM
Payment data	AR_ARCH_PAYMENTS
Posted item data	AR_ARCH_ITEM
Direct debit data	AR_ARCH_DIRECT_DEBIT
Action list data	AR_ARCH_ACTION_LST
Statement data	AR_ARCH_STATEMENTS

Review these queries to determine whether you want to add additional selection criteria for the transactions to be archived.

Archive Template Definitions

When you archive data, you select an archive template definition. Each archive template definition includes one or more archive object definitions and the archive query definitions used to select the data. Receivables delivers these archive templates:

Data Type	Archive Template
Pending item data	ARPEND
Payment data	ARPYMT
Posted item data	ARITEM
Direct debit data	ARDIRDEB
Action list data	ARACTLST
Statement data	ARSTMT

Archiving Data

This section provides an overview of the archive process flow, an overview of history tables, and lists the pages used to archive data.

Understanding the Archive Process Flow

Use the Data Archive Manager to archive transactions. You use the same run control page for each step in the process except for auditing the archive selection and generating archive candidate reports.

Perform these tasks to archive data:

Archive transaction data to the history tables by selecting the archive template and query for the data type that you want to archive on the Archive Data to History page.

Also, enter the archive date (or statement ID for the statement archive process ) used to select the transactions by clicking Reset Query Bind Variables on the Define Query Bind Variables page.

This table shows what the system compares the archive date to for the different data types:

Data Type	Archive Date
Pending item data	Accounting date for the pending item group.
Payment data	Accounting date for the deposit.
Posted item data	Accounting date for the item.
Direct debit data	Cash posting date or cancellation posting date for the direct debit.
Action list data	Date that the action was added.
Statement data	Statement ID for the statement.

Note. You can run the process multiple times to create various “what-if” scenarios based on the archive date.

(Optional) Review the number of rows that were selected for archiving for each table on the Audit Archiving page.

This page lists the number of rows that were selected for archiving for each archive ID (template), archive batch number, and table combination.

Note. This page displays data only if you selected the Audit Row Count check box on the Archive Data to History page.
(Optional) Run the archive candidate reports delivered with Receivables to see a list of the transactions that were selected in the transaction tables and copied to the history tables.
Delete the data from the transaction tables for a specific archive ID (template) and batch number on the Archive Data to History page.
(Optional) Rollback the transaction data from the history tables for a specific archive ID (template) and batch number on the Archive Data to History page if you deleted the transaction data in error.
Remove the transaction data from the history tables for a specific archive ID (template) and batch number on the Archive Data to History page.

Understanding History Tables

Receivables updates these history tables when you run the selection process:

Data to Archive	History Tables
Pending item data	PS_AR_GROUP_CNTL_H PS_AR_PEND_ITM_H PS_AR_PEND_DST_H PS_AR_PEND_VAT_H PS_AR_PEND_TAX_H PS_AR_PEND_TX_DT_H PS_AR_PEND_IT_MR_H
Payment data	PS_AR_DEP_CONTRL_H PS_AR_PAYMENT_H PS_AR_PAY_ITEM_H PS_AR_PAY_ID_CUS_H PS_AR_PAY_ID_ITM_H PS_AR_PAY_IT_MRL_H PS_AR_PAY_MIS_DS_H PS_AR_EE_DJR_ACT_H
Posted item data	PS_AR_ITEM_H PS_AR_ITEM_DST_H PS_AR_ITM_ACTVTY_H PS_AR_ITM_ACTVAT_H PS_AR_ITM_ACTTAX_H PS_AR_ITMACTXDT_H PS_AR_ITEM_AUDIT_H PS_AR_IPAC_HDR_H PS_AR_IPAC_SGL_H PS_AR_IPAC_DET_H PS_AR_CCTRN_HDR_H PS_AR_CCTRN_TBL_H PS_AR_EE_ITACLN_H
Direct debit data	PS_AR_DD_CONTROL_H PS_AR_DD_ITEM_H PS_AR_DD_ITM_MRL_H
Action list data	PS_AR_ACTION_LST_H
Statement data	PS_AR_STMT_CUST_H PS_AR_STMT_CUSDT_H PS_AR_STMT_CUSBD_H PS_AR_STMT_CUS_H

The data in these tables is keyed by the archive ID (template) and batch number for each archive run. If you want to see all transactions that are selected to archive you can run a query on the history tables or you can run one of the Receivables archive candidate reports.

You may not want to delete data from the history tables each time that you select and delete data from the transaction tables. This way the data will be available for a period of time to enable you to restore the data into the transaction tables. We recommend that you create a schedule for deleting data from the history tables. Before you delete data from the history tables, you should use a database utility to copy the contents to a flat file.

Important! If you decide to run the archive selection process again because you didn't select the correct data, you must first use the Data Archive Manager option to remove the data from the history tables.

Pages Used to Archive Data

Page Name	Object Name	Navigation	Usage
Archive Data to History	PSARCHRUNCNTL	PeopleTools, Data Archive Manager, Archive Data to History	Select an archive template and query and select the action you want to take in the archiving process.
Define Query Bind Variables	PSARCHRUNQRYBND	Click Define Binds on the Archive Data to History page.	Enter the date used to select the transactions to archive by clicking the Reset Query Bind Variables button.
Audit Archiving	PSARCHIVEAUDIT	PeopleTools, Data Archive Manager, Audit Archiving	View the number of rows selected to be archived for each table for a specific archive run.

Generating Archive Candidate Reports

This section provides an overview of archive candidate reports, discusses common elements, and lists the pages used to generate archive candidate reports.

Understanding Archive Candidate Reports

Receivables provides these reports that lists the transaction data selected by the Archive Data Manager for deletion:

Data Type	Report
Pending item data	AR Pending Item Archive Candidates (AR70001)
Payment data	AR Payment Archive Candidates (AR70002)
Posted item data	AR Item Archive Candidates (AR70003)
Direct debit data	AR Direct Debit Archive Candidates report (AR70006)
Action list data	AR Action List Archive Candidates report (AR70005)
Statement data	AR Statement Archive Candidate (AR70004)

Use these reports to determine whether you have selected the data that you want to delete from the transaction tables.

Common Elements Used in This Section

Archive Batch Number	Enter the batch number for the archive run whose data you want to review. The system assigns a batch number each time you run the Data Archive Manager process (SPARCHIVE) to select rows for archiving. You can obtain the batch numbers by viewing the details from the run control page or on the Audit Archiving page.
Archive ID	Displays the archive ID, which is the archive template for the data type whose report you are running.

Pages Used for Archive Candidate Reports

Page Name	Object Name	Navigation	Usage
Pending Archive Candidates	RUN_AR70001	Accounts Receivable, Receivables Update, Archive Receivables, Pending Candidate Report, Pending Archive Candidates	Run the AR Pending Item Archive Candidates report. Use the report to view pending item groups that you selected to archive.
Item Archive Candidates	RUN_AR70003	Accounts Receivable, Receivables Update, Archive Receivables, Item Candidate Report, Item Archive Candidates	Run the AR Item Archive Candidates report. Use the report to view closed items that you selected to archive.
Payment Archive Candidates	RUN_AR70002	Accounts Receivable, Receivables Update, Archive Receivables, Payment Candidate Report, Payment Archive Candidates	Run the AR Payment Archive Candidates report. Use the report to view payments that you selected to archive.
Statement Archive Candidates	RUN_AR70004	Accounts Receivable, Receivables Update, Archive Receivables, Statement Candidate Report, Statement Archive Candidates	Run the AR Statement Archive Candidates report. Use the report to view customer statements that you selected to archive.
Run AR70005	RUN_AR70005	Accounts Receivable, Receivables Update, Archive Receivables, Action List Candidate Report, Run AR70005	Run the AR Action List Archive Candidates report. Use the report to view actions that you selected to archive.
Run AR70006	RUN_AR70006	Accounts Receivable, Receivables Update, Archive Receivables, Direct Debit Candidate Report, Run AR70006	Run the AR Direct Debit Archive Candidates report. Use the report to view direct debits that you selected to archive.

Restoring Archived Data

This section provides an overview of data restoration and lists the page used to restore data.

Understanding Data Restoration

The Selection option on the Archive Data to History page places a copy of the data in the transaction tables in the history tables and the Delete option removes the transaction data from the transaction tables. If you delete the data from transaction tables in error, you can restore the data from the history tables by using the Rollback option on the Archive Data to History page. The process restores all data for a specific archive ID (template) and process run. You cannot restore selected data. For example, you cannot specify that you want to restore data for a specific customer or group control.

Important! After you delete data from the history tables by using the Remove from History option on the Archive data to History page, you cannot restore it to the transaction tables unless you first manually restore it to the history tables from a flat file.

Page Used to Restore Archived Data

Page Name	Object Name	Navigation	Usage
Archive Data to History	PSARCHRUNCNTL	PeopleTools, Data Archive Manager, Archive Data to History	Select an archive ID (template) and the batch number for the process run of the data that you want to restore.

Clearing Receivables Transaction Tables

During your implementation process, you may have entered test data that you want to delete without going through a formal archiving process.

To remove test data without going through a formal archiving process, remove records from the database through a program that you have written. If you have tested archiving, you will also need to remove data from the history tables.

Using Receivables Tables

This section lists the tables keyed by business unit from which you need to delete rows to clear receivables transactions and history from the system:

Type of Data	Tables
Pending item data	PS_GROUP_CONTROL PS_PENDING_DST PS_PENDING_ITEM PS_PENDING_VAT PS_PENDING_TAX PS_PENDING_TAX_DTL PS_PENDING_IT_MRL
Payment data	PS_DEPOSIT_CONTROL PS_PAYMENT PS_PAYMENT_ID_CUST PS_PAYMENT_ID_ITEM PS_PAYMENT_ITEM PS_PAY_MISC_DST PS_ITEM_MRL PS_EE_DJR_ACCTG_LN
Maintenance worksheet data	PS_WS_CONTROL PS_WS_ID_CUST PS_WS_ID_ITEM PS_WS_ITEM
Transfer worksheet data	PS_TRN_CONTROL PS_TRN_ID_CUST PS_TRN_ID_ITEM PS_TRN_ITEM
Draft worksheet data	PS_DRAFT_CONTROL PS_DRAFT_ITEM PS_DR_REMIT_CNTL PS_DR_REMIT_CUST PS_DR_REMIT_DATE PS_DR_REMIT_DTL PS_DR_REMIT_ITEM
Posted item data	PS_ITEM PS_ITEM_ACT_VAT PS_ITEM_ACTIVITY PS_ITEM_DST PS_ITEM_ACTTAX PS_ITEM_ACTTAX_DTL PS_ITEM_AUDIT PS_AR_IPAC_HDR PS_AR_IPAC_SGL PS_AR_IPAC_DETAIL PS_AR_CC_TRAN_HDR PS_AR_CC_TRAN_TBL PS_EE_ITM_ACCTG_LN
Direct debit worksheet data	PS_DD_CONTROL PS_DD_ITEM PS_DD_ITEM_MRL
Action list data	PS_ACTION_LST
Posted customer data	PS_CUST_AGING PS_CUST_DATA PS_CUST_HISTORY PS_SUBCUST_AGING PS_SUBCUST_DATA PS_SUBCUST_HISTORY
Statement data	PS_STMT_CUST PS_STMT_CUST_DTL PS_STMT_CUST_BFWD PS_AR_STMT_CUST
Dunning data	PS_DUN_CUST PS_DUN_CUST_DTL
Overdue charge data	PS_FC_CUST PS_FC_CUST_DTL
Conversation data	PS_CUST_CONVER PS_CUST_CONVER_DTL PS_CUST_CONVER_HDR PS_CUST_CONVER_ATT
Revaluation	PS_RVL_ACCTG_LN_AR
Archived closed items	PS_AR_ITEM_H PS_AR_ITEM_DST_H PS_AR_ITM_ACTVTY_H PS_AR_ITM_ACTVAT_H PS_AR_ITM_ACTTAX_H PS_AR_ITMACTXDT_H PS_AR_ITEM_AUDIT_H PS_AR_IPAC_HDR_H PS_AR_IPAC_SGL_H PS_AR_IPAC_DET_H PS_AR_CCTRN_HDR_H PS_AR_CCTRN_TBL_H PS_AR_EE_ITACLN_H
Archived pending items	PS_AR_GROUP_CNTL_H PS_AR_PEND_ITM_H PS_AR_PEND_DST_H PS_AR_PEND_VAT_H PS_AR_PEND_TAX_H PS_AR_PEND_TX_DT_H PS_AR_PEND_IT_MR_H
Archived payments	PS_AR_DEP_CONTRL_H PS_AR_PAYMENT_H PS_AR_PAY_ITEM_H PS_AR_PAY_ID_CUS_H PS_AR_PAY_ID_ITM_H PS_AR_PAY_IT_MRL_H PS_AR_PAY_MIS_DS_H PS_AR_EE_DJR_ACT_H
Archived direct debits	PS_AR_DD_CONTROL_H PS_AR_DD_ITEM_H PS_AR_DD_ITM_MRL_H
Archived actions	PS_AR_ACTION_LST_H
Archived statements	PS_AR_STMT_CUST_H PS_AR_STMT_CUSTDT_H PS_AR_STMT_CUSBD_H PS_AR_STMT_CUS_H

Using SQL to Clear Receivables Transaction Tables

Use the following SQL script to clear Receivables transaction tables in test databases that are populated when you enter and post transactions, run aging, run statements, and generate overdue charges. This script also clears history tables.

SET ECHO ON
TRUNCATE TABLE PS_ITEM;
TRUNCATE TABLE PS_ITEM_ACT_VAT;
TRUNCATE TABLE PS_ITEM_ACTTAX;
TRUNCATE TABLE PS_ITEM_ACTTAX_DTL;
TRUNCATE TABLE PS_ITEM_ACTIVITY;
TRUNCATE TABLE PS_ITEM_DST;
TRUNCATE TABLE PS_ITEM_AUDIT
TRUNCATE TABLE PS_AR_IPAC_HDR
TRUNCATE TABLE PS_AR_IPAC_SGL
TRUNCATE TABLE PS_AR_IPAC_DETAIL
TRUNCATE TABLE PS_AR_CC_TRAN_HDR
TRUNCATE TABLE PS_AR_CC_TRAN_TBL
TRUNCATE TABLE PS_EE_ITM_ACCTG_LN
TRUNCATE TABLE PS_GROUP_CONTROL;
TRUNCATE TABLE PS_PENDING_DST;
TRUNCATE TABLE PS_PENDING_ITEM;
TRUNCATE TABLE PS_PENDING_VAT;
TRUNCATE TABLE PS_PENDING_TAX;
TRUNCATE TABLE PS_PENDING_TAX_DTL;
TRUNCATE TABLE PS_PENDING_IT_MRL;
TRUNCATE TABLE PS_DEPOSIT_CONTROL;
TRUNCATE TABLE PS_PAYMENT;
TRUNCATE TABLE PS_PAYMENT_ID_CUST;
TRUNCATE TABLE PS_PAYMENT_ID_ITEM;
TRUNCATE TABLE PS_PAYMENT_ITEM;
TRUNCATE TABLE PS_PAY_MISC_DST;
TRUNCATE TABLE PS_PAY_ITEM_MRL;
TRUNCATE TABLE PS_EE_DJR_ACCTG_LN;
TRUNCATE TABLE PS_CUST_DATA;
TRUNCATE TABLE PS_CUST_HISTORY_TBL;
TRUNCATE TABLE PS_SUBCUST_DATA;
TRUNCATE TABLE PS_SUBCUST_HISTORY;
TRUNCATE TABLE PS_VAT_BALANCES;
TRUNCATE TABLE PS_RVL_ACCTG_LN_AR;
TRUNCATE TABLE PS_TRN_CONTROL;
TRUNCATE TABLE PS_TRN_ID_CUST;
TRUNCATE TABLE PS_TRN_ID_ITEM;
TRUNCATE TABLE PS_TRN_ITEM;
TRUNCATE TABLE PS_DRAFT_CONTROL;
TRUNCATE TABLE PS_DRAFT_ITEM;
TRUNCATE TABLE PS_REMIT_CNTL;
TRUNCATE TABLE PS_REMIT_CUST;
TRUNCATE TABLE PS_REMIT_DATE;
TRUNCATE TABLE PS_REMIT_DTL;
TRUNCATE TABLE PS_REMIT_ITEM;
TRUNCATE TABLE PS_DD_CONTROL;
TRUNCATE TABLE PS_DD_ITEM;
TRUNCATE TABLE PS_DD_ITEM_MRL;
TRUNCATE TABLE PS_ACTION_LST;
TRUNCATE TABLE PS_CUST_AGING;
TRUNCATE TABLE PS_SUBCUST_AGING;
TRUNCATE TABLE PS_STMT_CUST;
TRUNCATE TABLE PS_STMT_CUST_DTL;
TRUNCATE TABLE PS_STMT_CUST_BFWD;
TRUNCATE TABLE PS_AR_STMT_CUST;
TRUNCATE TABLE PS_DUN_CUST;
TRUNCATE TABLE PS_DUN_CUST_DTL;
TRUNCATE TABLE PS_FC_CUST;
TRUNCATE TABLE PS_FC_CUST_DTL;
TRUNCATE TABLE PS_WS_CONTROL;
TRUNCATE TABLE PS_WS_ID_CUST;
TRUNCATE TABLE PS_WS_ID_ITEM;
TRUNCATE TABLE PS_WS_ITEM;
TRUNCATE TABLE PS_CUST_CONVER;
TRUNCATE TABLE PS_CUST_CONVER_HDR;
TRUNCATE TABLE PS_CUST_CONVER_DTL;
TRUNCATE TABLE PS_CUST_CONVER_ATT;
TRUNCATE TABLE PS_AR_ITEM_H;
TRUNCATE TABLE PS_AR_ITEM_DST_H;
TRUNCATE TABLE PS_AR_AR_ITM_ACTVTY_H;
TRUNCATE TABLE PS_AR_ITM_ACTVAT_H;
TRUNCATE TABLE PS_AR_ITM_ACTTAX_H;
TRUNCATE TABLE PS_AR_ITM_ACTXDT_H;
TRUNCATE TABLE PS_AR_ITEM_AUDIT_H;
TRUNCATE TABLE PS_AR_IPAC_HDR_H;
TRUNCATE TABLE PS_AR_IPAC_SGL_H;
TRUNCATE TABLE PS_AR_IPAC_DET_H;
TRUNCATE TABLE PS_AR_CCTRN_HDR_H;
TRUNCATE TABLE PS_AR_CCTRN_TBL_H;
TRUNCATE TABLE PS_AR_EE_ITACLN_H;
TRUNCATE TABLE PS_AR_GROUP_CNTL_H;
TRUNCATE TABLE PS_AR_PEND_ITM_H;
TRUNCATE TABLE PS_AR_PEND_DST_H;
TRUNCATE TABLE PS_AR_PEND_VAT_H;
TRUNCATE TABLE PS_AR_PEND_TAX_H;
TRUNCATE TABLE PS_AR_PEND_TX_DT_H;
TRUNCATE TABLE PS_AR_IT_MR_H;
TRUNCATE TABLE PS_AR_DEP_CONTRL_H;
TRUNCATE TABLE PS_AR_PAYMENT_H;
TRUNCATE TABLE PS_AR_PAY_ITEM_H;
TRUNCATE TABLE PS_AR_PAY_ID_CUS_H;
TRUNCATE TABLE PS_AR_PAY_ID_ITM_H;
TRUNCATE TABLE PS_AR_PAY_IT_MRL_H;
TRUNCATE TABLE PS_AR_PAY_MIS_DS_H;
TRUNCATE TABLE PS_AR_EE_DJR_ACT_H;
TRUNCATE TABLE PS_AR_STMT_CUST_H;
TRUNCATE TABLE PS_AR_STMT_CUSDT_H;
TRUNCATE TABLE PS_AR_STMT_CUSBD_H;
TRUNCATE TABLE PS_AR_STMT_CUS_H;
TRUNCATE TABLE PS_AR_DD_CONTROL_H;
TRUNCATE TABLE PS_AR_DD_ITEM_H;
TRUNCATE TABLE PS_AR_DD_ITM_MRL_H;
TRUNCATE TABLE PS_AR_ACTION_LST_H;