The following tables store information during the Archive and Purge process. Each of these tables (except the AR_ARCHIVE_CONTROL and AR_ARCHIVE_CONTROL_DETAIL tables) must be empty for the Archive-Preview or Archive and Purge programs to run.

AR_ARCHIVE_PURGE_LOG

This table is used to store messages during the processing cycle. You can review this table to identify at what point an error occurred. This table contains the following columns:

• MESSAGE - Message text.

• TIME - Time that it was inserted into the log table.

AR_PURGE_TRX_AUX

This table is used during the selection process to store the selected transaction identification numbers:

• TRX_ID - The CUSTOMER_TRX_ID

• RELATED_ID - Related CUSTOMER_TRX_ID

• TYPE - Related transaction type

• STATUS - Indicates purgeable status with a Yes or No

  • Index:

  • AR_PURGE_TRX_AUX_N1 on the TRX_ID column.

AR_PURGE_REC_AUX

This table is used during the selection process to store the selected receipt identification numbers:

• REC_ID - The CASH_RECEIPT_ID.

• REC_TRX_ID - Related transaction's CUSTOMER_TRX_ID

• STATUS - Indicates purgeable status with a Yes or No

  • Index:

  • AR_PURGE_REC_AUX_N1 on the REC_ID column.

AR_PURGE_OE_EXCEPTIONS

This table is used during the selection process to store identification numbers of transactions that do not meet the Oracle Order Management purge requirements:

• TRX_ID - The CUSTOMER_TRX_ID

  • Index:

  • AR_PURGE_OE_EXCEPTION_N1 on the TRX_ID column.

AR_ARCHIVE_PURGE_INTERIM

This table is populated by the validation process and stores the IDs of qualifying transactions. The Purge program uses these IDs to identify transactions to purge but does not re-validate the IDs.

• TRX_ID - The CUSTOMER_TRX_ID

• RELATED_ID - Related CUSTOMER_TRX_ID or CASH_RECEIPT_ID

  • Indices:

  • AR_ARCHIVE_PURGE_INTERIM_N1 on the TRX_ID column.

  • AR_ARCHIVE_PURGE_INTERIM_N2 on the RELATED_ID column.

AR_ARCHIVE_CONTROL

This table stores historical data for Archive and Purge runs. Each Archive and Purge module inserts a record into this table. For example, if you run Archive-Preview, there will be a record for the control module, a record for the selection and validation, a record for the archive and so on. All records associated with a particular run have the same archive_id and the records are distinguished by request_id. As each step begins it inserts a record and updates the status column with R for running. When the step completes, the program updates the status column with C for complete and inserts a new record with a status R, for the next step of the process.

• CREATION_DATE - Date of creation

• CREATED_BY - Standard who column

• TRANSACTION_MODE - Parameter

• TRANSACTION_TYPE - Parameter

• TRANSACTION_PERIOD - Parameter

• OPEN_RECEIVABLES - Parameter

• POSTABLE - Parameter

• ARCHIVE_LEVEL - Parameter

• NUMBER_OF_PROCESSES - Parameter

• COMMIT_SIZE - Parameter

• STATUS - Status

• REQUEST_ID - Concurrent request id.

• COMMENTS - User enterable comments

• ARCHIVE_ID - Unique Identifier for the Archive/Purge run

AR_ARCHIVE_CONTROL_DETAIL

This table stores historical, statistical data for Archive/Purge runs. It stores the transaction type, record count and amount, grouped by transaction type. It will contain one record for each GL period archived during the Archive process. This information is used for the Archive Summary report.

• ARCHIVE_ID - Unique Identifier for the Archive/Purge run

• PERIOD_NUMBER - Sequence of GL Period associated with this group of transactions

• PERIOD_NAME - GL Period associated with this group of transactions

• INVOICES_CNT - Number of Invoices processed

• CREDIT_MEMOS_CNT - Number of Credit Memos processed

• DEBIT_MEMOS_CNT - Number of Debit Memos processed

• CHARGEBACKS_CNT - Number of Chargebacks processed

• DEPOSITS_CNT - Number of Deposits processed

• ADJUSTMENTS_CNT - Number of Adjustments processed

• CASH_RECEIPTS_CNT - Number of Receipts processed

• INVOICES_NO_REC_CNT - Number of Invoices not open to receivables processed

• CREDIT_MEMOS_NO_REC_CNT - Number of Credit Memos not open to receivables processed

• DEBIT_MEMOS_NO_REC_CNT - Number of Debit Memos not open to receivables processed

• CHARGEBACKS_NO_REC_CNT - Number of Chargebacks not open to receivables processed

• DEPOSITS_NO_REC_CNT - Number of Deposits not open to receivables processed

• GUARANTEES_CNT - Number of Guarantees processed

• MISC_RECEIPTS_CNT - Number of Miscellaneous Receipts processed

• INVOICES_TOTAL - Total amount of Invoices

• CREDIT_MEMOS_TOTAL - Total amount of Credit Memos

• DEBIT_MEMOS_TOTAL - Total amount of Debit Memos

• CHARGEBACKS_TOTAL - Total amount of Chargebacks

• DEPOSITS_TOTAL - Total amount of Deposits

• ADJUSTMENTS_TOTAL - Total amount of Adjustments

• CASH_RECEIPTS_TOTAL - Total amount of Receipts

• DISCOUNTS_TOTAL - Total amount of Discounts

• EXCHANGE_GAIN_LOSS_TOTAL - Total amount of exchange rate gain and loss

• INVOICES_NO_REC_TOTAL - Total amount of Invoices not open to receivables

• CREDIT_MEMOS_NO_REC_TOTAL - Total amount of Credit Memos not open to receivables

• DEBIT_MEMOS_NO_REC_TOTAL - Total amount of Debit Memos not open to receivables

• CHARGEBACKS_NO_REC_TOTAL - Total amount of Chargebacks not open to receivables

• DEPOSITS_NO_REC_TOTAL - Total amount of Deposits not open to receivables

• GUARANTEES_TOTAL - Total amount of Guarantees

• MISC_RECEIPTS_TOTAL - Total amount of Miscellaneous Receipts

AR_ARCHIVE_HEADER

The Headers table stores the main transaction information. Main transactions may be Invoices, Receipts, Credit or Debit Memos, Adjustments, Guarantees, Deposits, Chargebacks, and On-Account Credits. This data will be archived for all 'Archive-Levels'. This information is used for the Archive Detail report.

AR_ARCHIVE_DETAIL

This table contains information related to transaction lines, as well as distribution information. This table will contain records relating to Credit Memo and Receipt Applications for a 'Header level' archive. However, most of this information is archived for 'Header and Line' and 'Header, Line and Distribution' archives. In addition, the following information will only be archived for a 'Header, Line and Distribution' level archive:

• Selected, distribution related columns from the lines records.

• One additional record for each account distribution in RA_CUST_TRX_LINE_GL_DIST and AR_MISC_CASH_DISTRIBUTIONS. The columns archived in these records are listed separately at the end of this table.

If you select HEADERS, LINES and DISTRIBUTIONS additional records will be archived. These records will contain the following information plus reference data to relate them to the appropriate line record in this table.