Understanding Data Types Available for Archiving

This topic 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.

For more information, see the product documentation for PeopleTools: Data Management, especially topics in the “Using PeopleSoft Data Archive Manager” section.

Most PeopleSoft 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. PeopleSoft 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. PeopleSoft 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 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 Receivables 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

The Receivables 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.

After 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. 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 uses the pending data to create a mirror-image group, one that is opposite in sign from the original. Oracle suggests 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.

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 Receivables 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 journaled payments, the accounting entries in the PS_PAYMENT_MISC_DST table must remain in PeopleSoft 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.

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.

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 you run the Receivables Update process.

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. 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.

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: PeopleSoft Receivables only provides archive objects to archive statement tables.

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 will 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, Oracle suggests keeping all history elements for a given fiscal year and accounting period.

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.

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 & 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.

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.

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

Note: Run the payment archive process first. Pending groups with a group type of P for payments are not archived unless the associated payment has already been archived.

  1. Payment data for completely posted deposits.

  2. Pending item data for completely posted groups.

  3. Direct debit data for completed or rejected direct debits that are posted.

  4. Inactive conversation data.

  5. Inactive statement data.

  6. Inactive dunning data.

  7. Inactive overdue charge data.

  8. Closed action data.

  9. Closed posted item data.

  10. Inactive posted customer data.

  11. Inactive customer definition data.