Archive and Purge

Using Archive and Purge

Important: You should not use the Receivables Archive and Purge program if you are using cash basis accounting.

Databases with high volumes of transactions rapidly increase in size and memory requirements. This can have a detrimental impact on performance for both online and background processing. Receivables stores large quantities of historical data to maintain audit trails, but this data need not be available online. The Archive and Purge feature lets you periodically save and delete transactions that you no longer need online to reclaim space in your database and improve system performance.

Depending on your business needs, you can archive records at one of three levels of detail: 'header-level', 'header and line-level', and 'header, line, and distribution-level'. Transactions are purged from the database based on the parameters you specify. The purge process will remove eligible transactions and all activities relating to these transactions such as adjustments, credits, reversals, calls, sales credits, and receipts.

For example, in the following diagram Invoice A has been paid by Receipt 1, which also partially paid Invoice B. Receipt 2 is used to pay the remainder of Invoice B. In addition, Invoice B is applied to a commitment with Invoice C. Invoice C is paid by Receipt 3 and a Credit Memo. All of these transactions are considered to be members of a single chain of related transactions. The Archive and Purge program rejects the entire chain if any member does not meet the purge criteria.

Chain of Related Transactions

It is important to read this essay in its entirety before running the Archive and Purge programs. Archive and Purge deletes transaction information from your database and this essay outlines the steps you must take to ensure that all critical information and reports are available for future use.

Related Topics

Preparing to Run Archive and Purge

Archive and Purge Cycle

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Preparing to Run Archive and Purge

Before running the standard Archive and Purge program, perform the following steps to prepare your system. These steps ensure that no important data is deleted from Receivables when running Archive and Purge.

Not all of these steps are required before running the Call New Archive and Purge Process. If a step is optional or not required for this program, this is indicated in the step description.

1. Clear archive tables (standard Archive and Purge only)

   The Archive/Purge programs verify that the archive tables are clear before running. If the tables are not clear, you will receive an error and processing will stop. Check the following tables to ensure that they are empty:

   • AR_ARCHIVE_HEADER

   • AR_ARCHIVE_DETAIL

2. Ensure no other users are on the system (standard Archive and Purge only)

   The Archive/Purge programs can only be run when other users are not accessing the system. The programs will verify that no other concurrent processes can run while it is processing. However, you must ensure that no other concurrent programs run between the time you start the purge preparation steps and Archive/Purge begins.

3. Run the Oracle Sales Compensation interface

   If you use Oracle Sales Compensation, you must run the Oracle Sales Compensation open interface to copy information from the following Receivables tables before purging:

   • RA_CUSTOMER_TRX

   • RA_CUSTOMER_TRX_LINES

   • RA_CUSTOMER_TRX_LINES_SALESREPS

4. Run Intrastat

   Ensure that your movement statistics records have been reported to the authorities for the periods you are purging.

   With the formation of the European Union (EU), the border restrictions between member states were lifted. This rendered the method of gathering trade statistics on how goods were moved, and the type of goods being moved, obsolete. The EU replaced the old method with 'Intrastat' which requires companies within the EU to gather movement statistics concerning the trade between EU member states.

   In Oracle Applications, movement statistics are tied to the shipment information and passed through AutoInvoice to Receivables. The Intrastat report used to satisfy the EU requirement derives data from invoice information in Receivables. Therefore, you should not delete any invoice information which has associated movement statistics until you report the movement information to the authorities. This is usually done on a monthly basis, but could be on any negotiated period.

5. Verify AutoInvoice tables are empty (optional, but recommended)

   To ensure that you do not purge transactions which could be affected by records in the AutoInvoice tables, verify that the following AutoInvoice interface tables are empty:

   • RA_INTERFACE_LINES

   • RA_INTERFACE_SALESCREDITS

   • RA_INTERFACE_DISTRIBUTIONS

   If these tables are populated, you must run AutoInvoice and ensure it clears these tables before running the Archive/Purge programs.

6. Verify Lockbox tables are empty (optional, but recommended)

   To ensure that you do not purge transactions that could be affected by records in the Lockbox tables, verify that the Lockbox Interim table is empty:

   • AR_PAYMENTS_INTERFACE

   If this table is populated, you must run Submit Lockbox Validation Processing and ensure it runs without errors before running the Archive/Purge programs.

7. Verify QuickCash tables are empty (optional, but recommended)

   To ensure that you do not purge transactions that could be affected by records in the QuickCash tables, verify that the following QuickCash tables are empty:

   • AR_INTERIM_CASH_RECEIPTS

   • AR_INTERIM_CASH_RECEIPT_LINES

   If these tables are populated, you must run Post QuickCash before the Archive/Purge programs.

8. Run Tax Reports

   Certain tax reports derive values which are not stored in the database. These reports cannot derive accurate data for periods in which transactions have been purged. You should therefore run these reports for the periods you are purging and store the output for future use, as the data in these reports may be needed in a tax audit.

   • If your tax type is US Sales Tax, run the following:

     • Adjustments Register

     • Miscellaneous Receipts Register

     • Sales Journal by General Ledger Account

     • U.S. Sales Tax Report

   • If your tax type is VAT, run the following:

     • Adjustments Register

     • Customers with 0 VAT and No VAT Registration Number

     • Miscellaneous Receipts Register

     • Sales Journal by General Ledger Account

     • Tax Reconciliation Report

     • VAT Exception Report

9. Back up the Database

   Before you purge any records from Receivables, you must back up your database for safety. You should also confirm the integrity of your backup.

Related Topics

Running Archive and Purge

Archive and Purge Cycle

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Archive Tables

Archive and Purge Cycle

The cycle for the standard Archive and Purge program is divided into four separate processes: Selection and Validation, Archive, Purge, and optionally Copying to a file. The Selection and Validation and Archive processes form the Archive-Preview program. This program selects eligible transaction using criteria you specified, validates the data to identify the transaction chains, then stores this information in the archive tables. The Purge program uses the information in the archive tables to delete eligible transactions from the database tables. Alternatively, you can run the Selection and Validation, Archive, and Purge processes together using the Archive and Purge program. The final process is to transfer the archive data to a separate storage medium. Using the Archive to File program enables you to write the archive information to a flat file. Alternatively, you can export the AR_ARCHIVE_HEADER and AR_ARCHIVE_DETAIL tables and import them into your own archive tables.

Once you have completed all of the preparation steps, you can run the following programs from the Requests window: Archive-Preview, Purge, Archive and Purge, and Archive to File. Each of these programs can be run as a separate process, however the Purge and Archive to File programs cannot be run until the Archive tables are populated by either the Archive-Preview or the Archive and Purge programs. Additionally, you can run the Archive-Restart program and Archive Reports from the Requests window.

The Call New Archive and Purge Process includes the all of the processes as the standard Archive and Purge program, but it does not generate a preview report of items selected for purging or the Archive Detail/Summary reports. This process selects an item based on the criteria you entered and ensures that it meets the requirements for purging. It then purges the transaction and moves on to the next transaction available for archive and purge. Information about transactions that could not be purged and items that are purged is written to a log file. This file name is the same as the concurrent request ID.

Variable	Description
Archive-Preview	The Archive-Preview program selects and validates transactions that meet the purge parameters and copies the transaction information into the archive tables. A report is automatically generated after the archive tables are populated. The level of detail of this report is determined by the parameter you select when you start the Archive-Preview program.
Purge	The purge process purges eligible transaction data. To run this program you must first run the Archive-Preview program as this identifies eligible transactions and stores the IDs in AR_ARCHIVE_PURGE_INTERIM. Warning: You should only run the Purge program if no users have been on the system since you started the Archive-Preview, as this process does not revalidate the IDs stored in AR_ARCHIVE_PURGE_INTERIM.
Archive and Purge	The Archive and Purge program populates the archive tables and purges transaction information in one step. This can also be run after Archive-Preview if you cannot be sure that no users have been on the system since you started the Archive-Preview.
Archive to File	This is an optional program which can be used to copy the archive tables to a flat file if this is the desired method of storage.
Archive-Restart	This program is used for error handling when the Archive-Preview or Archive and Purge fails. It can be used to save the system from having to revalidate all purge candidates, if Archive/Purge has completed the selection and validation phase, then fails during the archive phase. Archive-Restart clears the Archive Header and Detail tables and submits the archive report. When submitting the Archive-Restart program you must provide the following parameters: Archive Level, Summary Report Only, Number of Workers, Commit Size, and Archive ID.
Archive Summary Report	Submit this report manually from the Requests window if the report fails when submitted by the Archive and Purge or the Archive-Preview program. You can also submit this report to review summary information for previous Archive/Purge runs. The Archive Summary Report includes the amount and count of transactions selected for purge based on the AR_ARCHIVE_CONTROL table. When submitting the Archive Summary Report program, you must provide the Archive ID.
Archive Detail Report	Submit this report manually from the Requests window if the report fails when submitted by the Archive and Purge or the Archive-Preview program. The Archive Detail Report includes a breakdown of the above summary information by customer. This report is based on the AR_ARCHIVE_HEADER table. When submitting the Archive Detail Report program, you must provide the Archive ID.
Call New Archive and Purge Process	Submit this program manually from the Requests window. Users do not have to log off the system to run this program. This option does not purge deposits, guarantees, miscellaneous receipts or any items linked to these transactions. This option does not create the Archive Purge Detail or Summary reports; instead, the program writes information about the purge process to a log file.

A typical Archive/Purge process might include the following steps.

1. Change user responsibility.

   The Archive/Purge programs are only available to users with the AR Archive Purge User responsibility.

2. Run Archive-Preview (standard Archive and Purge program only)

   In the Run Archive and Purge window, select the Archive-Preview program. When running the Archive-Preview program you must provide values for the following parameters:

   • GL Date Type (Required, Default)

   • Archive Period (Required)

   • Open Receivables Only (Required, Default)

   • Postable Items Only (Required, Default)

   • Customer Name (Optional)

   • Archive Level (Required)

   • Summary Report Only (Required, Default)

   • Number of Workers (Required, Default)

   • Commit Size (Required, Default)

   For a detailed description of parameters see: Archive and Purge Parameters.

3. Review Archive Report (standard Archive and Purge program only)

   Use the Archive Report(s) generated during the Archive-Preview program to review transaction counts and amounts. The Grand Total of the report should equal zero.

   This report is based on the transactions selected for purge and stored in the AR_ARCHIVE_PURGE_INTERIM table.

4. Purge Database Tables

   Return to the Run Archive and Purge window to start the purge program by entering Purge in the Name field. The Purge removes transaction information from the database based on the data in table AR_ARCHIVE_PURGE_INTERIM. The Purge program provides the following parameters:

   • Number of Workers (Required, Default)

   • Archive ID (Required)

     For a detailed description of parameters see: Archive and Purge Parameters.

     The purge program does not generate a report as it would use the same archive table information as the archive report, so the two reports would be identical.

     Important: If you wish to ensure consistency between the Archive-Preview and the Purge, no users should be on the system in the interim.

     You can run the Archive and Purge instead of the Purge if you cannot be sure that no users have been on the system since you started the Archive-Preview. You must clear the archive tables before running this program. The parameters for this program combine the parameters of the Archive-Preview and Purge programs.

5. Move Archive Data to Storage

   From the Run Archive and Purge window, select the Archive to File program to move your archive data to a file in the standard output directory (AR_TOP/out) with the file name <user id.request id>.

   Warning: Ensure that you move your archive output from the AR_TOP/out directory to an appropriate storage area. Otherwise, it will be deleted when your system administrator clears the output directories.

   Important: Archived data is for reference purposes only. After you move the data to your storage area, you cannot bring it back into Oracle Receivables for additional processing.

6. Clear Archive Tables

   Once archive data has been stored the archive tables must be cleared before the next purge run. To clear the archive tables use the TRUNCATE command in SQL with the following tables:

   • AR_ARCHIVE_HEADER

   • AR_ARCHIVE_DETAIL

   Important: The following information in this step is true for the standard Archive and Purge program, but not the Call New Archive and Purge Process.

   The following tables will be cleared automatically the next time you run the Archive/Purge programs. However, you may wish to TRUNCATE these tables now. The TRUNCATE command is a more efficient way of clearing these tables and will save time during the next Archive/Purge process.

   • AR_PURGE_TRX_AUX

   • AR_PURGE_REC_AUX

   • AR_ARCHIVE_PURGE_LOG

   • AR_ARCHIVE_PURGE_INTERIM

   • AR_PURGE_OE_EXCEPTIONS

   The truncate command removes all of the rows from the tables.

   Warning: You cannot rollback a TRUNCATE statement.

7. Reorganize the Database

   After you purge your database, you should contact your Database Administrator (DBA) so that he can export and import the tables and indexes from which you purged data. By recreating these objects, you can reduce the memory each object occupies in your tablespace and increase the performance of your system.

Related Topics

Archive and Purge Parameters

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Purge Criteria

Transactions: Transactions and all activities relating to the transactions such as adjustments, credits, reversals, calls, sales credits, and receipts must meet the following criteria:

• All transactions must be posted to GL. Receivables considers a transaction to be posted if every record relating to the transaction has a GL Posted date (this does not apply to transactions not eligible for posting if the Postable Only parameter is set to No).

• Standard Archive and Purge program: Transactions applied to commitments are not eligible for purge until the commitment is closed. A commitment is considered closed when the commitment balance (or if it is a deposit the deposit balance) is zero.

  Call New Archive and Purge: Transactions applied to commitments are not eligible for purge.

• If the GL Date Type parameter is:

  • Invoice GL date - all invoice GL dates must be prior to the end date of the period specified.

  • Receipts GL date - all receipt GL dates must be prior to the end date of the period specified.

  • All GL dates - the GL dates of all selected transactions must be prior to the end date of the period specified.

  Note: The GL Date Type parameter does not apply if you choose to include transactions not eligible for posting. In this case the transaction date will be used for date checking. This parameter applies only to the standard Archive and Purge program.

• All transactions must be closed (for example, the payment schedules have no amount due). This does not apply if you choose to include transactions not open to receivables. These transactions do not have a payment schedule and therefore are not checked.

• If the transaction is a receipt, it must be related to transactions eligible for purge, unless it is a reversed unapplied receipt in which case it may not be related to any transaction.

• If the transaction is a receipt, it must be fully applied or unapplied and reversed. For example, the status of the latest AR_CASH_RECEIPT_HISTORY record must be 'Cleared', 'Risk_Eliminated', or 'Reversed', or for Debit Memo reversals the reversal date must be not null.

• All transactions must meet the purge parameters you specify.

• Miscellaneous receipts will not be Purged unless you run Archive/Purge for all customers, because they are not related to specific customers.

The following are general rules transactions must meet to be considered closed:

Variable	Description
Invoice	Invoice balance is reduced to zero by application of one or more of the following: Cash Receipts, Credit Memos, Approved Adjustments, or Deposits.
Debit Memo	Debit Memo balance is reduced to zero by application of one or more of the following: Cash Receipts, Credit Memos, or Approved Adjustments.
Credit Memo	Credit Memo balance is fully applied to one or more of the following: Invoices, Debit Memos, Chargebacks, or Cash Receipts.
Chargeback	Chargeback is fully applied to either a Cash Receipt, Credit Memo, or an Approved Adjustment.
Deposit	Deposit balance and commitment balance is fully applied to one or more invoices.
Guarantee	Commitment balance is fully covered by one or more invoices. Important: The Call New Archive and Purge program does not purge deposits, guarantees, miscellaneous receipts or any items linked to these transactions.
Cash Receipt	Receipt balance is fully applied to one or more of the following: Invoice, Debit Memo, Credit Memo, Chargeback, Deposit. If the receipt was not applied but has been reversed, it is also eligible for purge.
Adjustment	Approved and Applied to an Invoice, Debit Memo, or Credit Memo.

Batches

A batch is not considered to be part of a transaction chain, therefore transactions that are part of a batch may be purged even if all transactions in the batch are not purgeable. The batch will be eligible for purge when all of the transactions associated to it are purged. Prior to a batch being purged you can review a batch with some of the transactions deleted. In this case the batch the Partially Purged check box will be checked and the Control Totals fields in the batch will appear to be out of balance. This is because the Actual Count and Amount fields in the Control Totals section do not include purged transaction data.

Transactions Related to Projects

Transactions related to Oracle Projects are not purged by default. However, you can override this default by adding your own criteria of what project-related transactions are to be purged. For example, you may wish to purge project-related transactions originating from a project that has since been closed and that will not be reopened for additional activity.

Note: No transactions in Oracle Projects are purged.

You specify your own criteria of what invoices to purge by adding your logic to the Receivables Invoice Purge client extension provided by Oracle Project Accounting. You first determine the logic that you want to include in the client extension. You then add and test your logic in the PL/SQL function client_purgeable in the package pa_ar_trx_purge. This function exists in the file PAXARPGB.pls located in the Oracle Project Accounting install/sql/ directory. Oracle Project Accounting provides the parameter of customer_trx_id to the client_purgeable function.

Transaction Related to Orders

Transactions will not be purged if they are referenced by open return lines in Oracle Order Management. In addition, commitments that are referenced by open order lines within Oracle Order Management are not purgeable. To do this, the Archive/Purge process uses the view SO_OPEN_ORDER_INVOICE_REF_V and the table AR_PURGE_OE_EXCEPTIONS which hold transaction IDs of open orders. The purge program uses these as criteria for eliminating transactions from the purge process. For more information, see: Archive Tables.

Transactions Under Collectibility Analysis

Transactions will not be purged if Event-Based Revenue Management is enabled and collectibility analysis is still in progress. Receivables determines the status of collectibility analysis using the LINE_COLLECTIBILITY_FLAG on the AR_DEFERRED_LINES_ALL table.

Receipts Reconciled in Cash Management

Receipts that were reconciled in Cash Management cannot be purged in Receivables until the related bank statement records in Cash Management are purged.

Client Extension

Receivables provides a client extension to enable you to integrate with third party applications or choose to exclude or include transactions from purge selection based on criteria that you define.

You specify your criteria by customizing the PL/SQL function trx_purgeable in the package arp_trx_purge. This function exists in the file ARPUPRGB.pls located in the Receivables install/sql/ directory. Receivables provides the parameter customer_trx_id to the trx_purgeable function which by default returns a true value. You need to add your logic to return a value of false for the customer_trx_id of the transactions you do not want to purge.

Related Topics

Tables Purged

Archive and Purge Cycle

Archive Level

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Tables Purged

The Archive and Purge programs delete transaction data from the following tables:

• AR_ACTION_NOTIFICATIONS

• AR_ADJUSTMENTS

• AR_BATCHES

• AR_CALL_ACTIONS

• AR_CASH_RECEIPTS

• AR_CASH_RECEIPT_HISTORY

• AR_CORRESPONDENCE_PAY_SCHED

• AR_CUSTOMER_CALL_TOPICS

• AR_DEFERRED_LINES_ALL

• AR_LINE_CONTS_ALL

• AR_MISC_CASH_DISTRIBUTIONS

• AR_NOTES

• AR_PAYMENT_SCHEDULES

• AR_RATE_ADJUSTMENTS

• AR_RECEIVABLE_APPLICATIONS

• AR_TRANSMISSIONS

• RA_BATCHES

• RA_CUSTOMER_TRX

• RA_CUSTOMER_TRX_LINES

• RA_CUST_TRX_LINE_GL_DIST

• RA_CUST_TRX_LINE_SALESREPS

• AR_CORRESPONDENCES

• AR_DISTRIBUTIONS

Related Topics

Archive Level

Archive and Purge Cycle

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Archive Level

The Archive and Purge program provides three levels of detail for archiving transaction information. You can archive just header level data for your transactions; header and line level data; or header, line, and distribution data.

Archived transactions are stored in the AR_ARCHIVE_HEADER and AR_ARCHIVE_DETAIL tables. The header table stores records of three types: Transactions (Invoices, Credit or Debit Memos, Guarantees, Deposits, Chargebacks, and On-Account Credits), Receipts and Adjustments. Records stored in the detail table relate to these header records.

The following diagrams illustrate the relationships between the records in these two tables.

Note: Regardless of the level of detail you choose to archive, the purge portion of this program will remove all records for the selected transaction and all related transactions.

Archive different levels of transaction data

Notes on the above diagram:

• Header records relate to detail records using transaction_class and transaction_id. Detail records are credit memo application(s), transaction line(s) and a distribution of type 'REC'.

• Line records in AR_ARCHIVE_DETAIL relate to distributions in the same table using transaction_class, transaction_id and transaction_line_id.

• For credit memos, 'related' columns in AR_ARCHIVE_HEADER indicate the credited transaction, or for invoices, they indicate the commitment applied (if applicable).

• For transaction lines, 'related' columns in AR_ARCHIVE_DETAIL indicate the credited line and the commitment line applied to the transaction (if applicable).

• For credit memo applications, 'related' columns in AR_ARCHIVE_DETAIL indicate the transaction credited

Archive different levels of cash receipt data

Notes on the above diagram:

• Header records relate to detail records using transaction_class and transaction_id. Detail records are cash receipt application(s), a cash receipt history record, and miscellaneous cash distributions.

• Miscellaneous cash distributions are stored only for a distribution level archive.

• No additional records are stored for a line level archive.

• For receipt applications, 'related' columns in AR_ARCHIVE_DETAIL indicate the transaction applied to the receipt.

Archive different levels of adjustment data

Notes on the above diagram:

• Header records relate to detail records using transaction_class and transaction_id.

• 'Related' columns in AR_ARCHIVE_DETAIL indicate the adjusted transaction in AR_ARCHIVE_HEADER.

• No additional columns records are stored for a distribution level archive.

Depending on the archive level you choose, different types and numbers of records will be stored. Also, for a distribution level archive, additional columns in line level records are populated.

Use the following table to determine which records are created for each archive level:

Level	Storage Table	Number of Records Archived
Headers	AR_ARCHIVE_HEADER	1 record for each transaction, receipt, and adjustment
Headers	AR_ARCHIVE_DETAIL	1 record for each credit memo and receipt application
Headers	AR_ARCHIVE_DETAIL	1 record for the latest AR_CASH_RECEIPT_HISTORY record
Headers and Lines	AR_ARCHIVE_DETAIL	1 record for each transaction line
Headers and Lines	AR_ARCHIVE_DETAIL (see Header Level)	1 record for each adjustment plus the Header level records
Header, Lines and Distributions	AR_ARCHIVE_DETAIL	1 record for each transaction distribution
Header, Lines and Distributions	AR_ARCHIVE_DETAIL	1 record for each miscellaneous cash distribution
Header, Lines and Distributions	AR_ARCHIVE_DETAIL (see Header and Line Level)	Additional accounting related columns archived on above line records plus the Header and Line level records

For a detailed list of all the columns archived for each level, see the Archive Tables.

Related Topics

Archive and Purge Cycle

Preparing to Run Archive and Purge

Purge Criteria

Tables Purged

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Data not Archived

The following table outlines transaction information that is purged but not archived as part of the Archive/Purge process. If you need to retain this information you must copy the required information before running Purge.

Information Not Archived	Source Tables
Sales information	RA_CUST_TRX_LINE_ SALESREPS
Call and all related information	AR_ACTION_NOTIFICATIONS AR_NOTES AR_CALL_ACTIONS AR_CUSTOMER_CALL_TOPICS
Invoice and Correspondence information concerning dunning letters	AR_CORRESPONDENCE_PAY_SCHED AR_CORRESPONDENCES
Detail Payment Schedule information	AR_PAYMENT_SCHEDULES
Currency exchange adjustments	AR_RATE_ADJUSTMENTS
Unaccrued adjustments	AR_ADJUSTMENTS (where status = 'U')
Cash Basis accounting information	AR_CASH_BASIS_ DISTRIBUTIONS

Important: The Archive/Purge programs should not be used if your Accounting Method is Cash Basis (as defined in the System Options window).

Related Topics

Monitoring Your Archive and Purge

Preparing to Run Archive and Purge

Archive and Purge Cycle

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Archive Tables

Monitoring Your Archive and Purge

When you submit any of the Archive/Purge programs, Receivables tracks the status of your process by inserting messages into a log table, AR_ARCHIVE_PURGE_LOG. These messages can be either Status or Error messages.

Status messages are inserted into the log table as different events in the Archive/Purge process take place, such as starting or completing a module. The modules that make up the Archive/Purge process are submitted by a 'control module' which produces many of the generic status messages.

Error messages are inserted into the log table when a module fails. You can then use the error messages to help you restart the correct programs and avoid repeating processes which completed successfully.

Monitoring Your Archive/Purge

You can monitor the progress of your Archive/Purge in two ways. Using the View Concurrent Requests window, or by accessing the AR_ARCHIVE_PURGE_LOG and AR_ARCHIVE_CONTROL tables using SQL*Plus.

During processing of any Archive/Purge run, multiple concurrent requests will be submitted. For example, if you submit the Archive-Preview, there will be a parent concurrent request for the control module, named Archive-Preview. This request will submit requests for the selection and validation process, for the archive, for the report, and so on. These child requests are submitted sequentially, so you can monitor the progress of your Archive/Purge by ensuring each child request completes successfully. When all child requests have completed successfully, all the messages in AR_ARCHIVE_PURGE_LOG are written to the report output file of the parent request.

If you want to monitor the progress of each request more closely, you can access the AR_ARCHIVE_PURGE_LOG table directly using SQL*Plus. Use the following commands to write the contents of the log table to a file titled log.lst in the directory where you logged on to SQL*Plus:

spool log
COLUMN MESSAGE format A50
select MESSAGE, TIME
from AR_ARCHIVE_PURGE_LOG;
spool off

You can then review this file to check your progress.

You will also need to access the log table directly if one of your concurrent requests fail. You can access AR_ARCHIVE_PURGE_LOG to see what the last message in the table is. This will be the final error message inserted before the program failed. You can match this error against the list of error messages below to determine your next course of action.

If there is a problem with your concurrent manager you can view the status of each concurrent request by accessing the AR_ARCHIVE_CONTROL table. Use the following commands to create a file containing status information for the current Archive/Purge run titled control.lst, in the directory where you logged on to SQL*Plus:

spool control
select request_id, status
from AR_ARCHIVE_CONTROL
WHERE archive_id = '<current archive_id in the format RRMMDDHHMISS>';
spool off

Refer to the table descriptions later in this essay for more information on how these tables are populated.

Status and Error Messages

In the tables below messages are grouped by module. Each table contains the message as it appears in the message log and a description of the message.

This table shows control module status messages:

Message	Message Description
AR_ARCHIVE_CONT_START	Starting the <program_name>. This message is used each time the control module starts a new program.
AR_ARCHIVE_SUB_START	Calling <program_name> process. This message appears as the control module calls each child program.
AR_ARCHIVE_REQ_SUB	Submitted concurrent request <request_id>. This message appears as the control module submits a concurrent request for each child program.
AR_ARCHIVE_REQ_TERM	Request: <request_id> Completed/Errored. This message returns the status of the above request.
AR_ARCHIVE_ERROR	<function_name> <error_message> <error_code>. This message appears if the above returns an error. It will be the last message in the file if there is an error and will return the 'technical' error code. For example: PL*SQL error. Contact your system administrator or support if you receive this message.
AR_ARCHIVE_SUB_COMP	<program_name> process complete. This message is the last message for each child process called.
AR_ARCHIVE_CONT_COMP	Completed the <program_name>. This message appears at the very end, when everything completes.

This table shows selection module status messages:

Message	Message Description
AR_ARCHIVE_START_SEL	Starting Data Selection...
AR_ARCHIVE_PUR_INT	Purging interim and auxiliary tables
AR_ARCHIVE_RETR_TRX	Retrieving transactions
AR_ARCHIVE_TRX_LOAD	Loaded <count> Transactions into AR_PURGE_TRX_AUX
AR_ARCHIVE_RETR_REC	Retrieving receipts
AR_ARCHIVE_REC_LOAD	Loaded <count> Receipts into AR_PURGE_REC_AUX
AR_ARCHIVE_COUNT	Loaded <count> rows into auxiliary tables. This message prints every 10,000 lines.
AR_ARCHIVE_COMP_SEL	Data selection complete
AR_ARCHIVE_CONTEXT	Oracle Projects context is <PA_transaction_flexfield_context>. This message appears if PA is installed.
AR_ARCHIVE_START_CYC	Validating data
AR_ARCHIVE_COMP_CYC	Validation complete
AR_ARCHIVE_INS_INT	Inserting into AR_ARCHIVE_PURGE_INTERIM

This table shows archive module status messages:

Message	Message Description
AR_ARCHIVE_ARC_START	Archiving...
AR_ARCHIVE_ARC_TRX	Archiving transaction ID range: <id_low> to <id_high>
AR_ARCHIVE_ARC_TRX_C	Finished archiving transaction ID range: <id_low> to <id_high>
AR_ARCHIVE_ARC_COMP	Archive Complete
AR_ARCHIVE_REP_START	Running archive report
AR_ARCHIVE_REP_COMP	Archive report complete

This table shows purge module status messages:

Message	Message Description
AR_ARCHIVE_PUR_START	Purging...
AR_ARCHIVE_PUR	Purging Transaction/Receipt/Batch_ID range: <id_low> to <id_high>
AR_ARCHIVE_PUR_C	Finished purging Transaction/Receipt/Batch_ID range: <id_low> to <id_high>
AR_ARCHIVE_PUR_COMP	Purge Complete

Error Messages: When you submit the Archive/Purge programs, records in the following tables are deleted as indicated.

• AR_PURGE_TRX_AUX

  • Records in this table are deleted before Archive-Preview and Archive and Purge.

• AR_PURGE_REC_AUX

  • Records in this table are deleted before Archive-Preview and Archive and Purge.

• AR_ARCHIVE_PURGE_INTERIM

  • Records in this table are deleted before Archive-Preview and Archive and Purge.

• AR_PURGE_OE_EXCEPTIONS

  • Records in this table are deleted before Archive-Preview and Archive and Purge.

• AR_ARCHIVE_PURGE_LOG

  • Records in this table are deleted before Archive-Preview and Archive and Purge.

• AR_ARCHIVE_HEADER

  • Records in this table are deleted before Archive-Restart.

• AR_ARCHIVE_DETAIL

  • Records in this table are deleted before Archive-Restart.

• AR_ARCHIVE_CONTROL_DETAIL

  • Records with the current archive_id are deleted before Archive-Restart.

This table shows generic error messages that are used for more than one error situation where noted.

Message	Message Description
AR_ARCHIVE_TABLE_POP	Archive/Purge terminated. Archive tables are populated. Please save then delete the contents of AR_ARCHIVE_HEADER and AR_ARCHIVE_DETAIL, then resubmit Archive/Purge. This message appears at the very start if the archive tables are not empty.
AR_ARCHIVE_NO_DATE	No date retrieved. Exiting program. This message appears if you cannot get the last day of the period from the period parameter entered.
AR_ARCHIVE_FAIL_A	Your selection and validation process failed. Please submit Archive-Preview. This message appears when you submit Archive-Preview and it fails during the selection and validation process.
AR_ARCHIVE_FAIL_A	Your archive process failed. Please submit Archive-Restart. This message appears when you submit Archive-Preview and it fails during the archive module.
AR_ARCHIVE_FAIL_A	Your archive summary/detail report process failed. Please submit Archive Summary/Detail Report. This message appears when you submit Archive-Preview and it fails during the report module.
AR_ARCHIVE_FAIL_A	Your selection and validation process failed. Please submit Archive and Purge. This message appears when you submit Archive and Purge and it fails during the selection and validation module.
AR_ARCHIVE_FAIL_B	Your archive process failed. Please submit Archive-Restart, then Purge. This message appears when you submit Archive and Purge and it fails during the archive module.
AR_ARCHIVE_FAIL_C	Your archive was successful, but your purge process failed. Please resubmit Purge. This message appears when you submit Archive and Purge and it fails during the Purge process.
AR_ARCHIVE_FAIL_D	Your Archive was successful, but your report failed. Please submit your Archive Report then Purge. This message appears when you submit Archive and Purge and it fails during the report module.
AR_ARCHIVE_FAIL_A	Your Purge process failed. Please submit Purge. This message appears when you submit Purge and it fails.
AR_ARCHIVE_FAIL_A	Your Archive to File process failed. Please submit Archive to File. This message appears when you submit Archive to File and it fails.
AR_ARCHIVE_FAIL_A	Your Archive Restart process failed. Please submit Archive Restart. This message appears when you submit Archive Restart and it fails during the archive.
AR_ARCHIVE_FAIL_C	Your archive was successful, but your Summary/Detail Report process failed. Please resubmit Summary/Detail Report. This message appears when you submit Archive Restart and it fails during the report.

Related Topics

Archive Tables

Preparing to Run Archive and Purge

Archive and Purge Cycle

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Archive Tables

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.

Note: There may be one or more GL Periods associated with each Archive/Purge run.

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

Note: Records stored in this table are of three types; Transactions (TRX), Receipts (CR) and Adjustments (ADJ). If one of these types is not referenced, it means the column is null for records of that type.

AR_ARCHIVE_HEADER	Source Database Columns	Source Columns	Derived From Database Tables	Derived From Columns
ARCHIVE_ID
TRANSACTION_CLASS	RA_CUST_TRX_TYPES	TYPE	RA_CUSTOMER_TRX	CUST_TRX_TYPE_ID
	AR_CASH_RECEIPTS	TYPE
	Constant Value	ADJ
TRANSACTION_TYPE	RA_CUST_TRX_TYPES	NAME (TRX)	RA_CUSTOMER_ TRX	CUST_TRX_TYPE_ID
TRANSACTION_ID	RA_CUSTOMER_TRX	CUSTOMER_TRX_ID
	AR_CASH_RECEIPTS	CASH_RECEIPT_ID
	AR_ADJUSTMENTS	ADJUSTMENT_ID
RELATED_TRANSACTION_CLASS	RA_CUST_TRX_TYPES	TYPE (Invoice being credited) (TRX)	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		TYPE (Commitment related to an invoice) (TRX)	RA_CUSTOMER_TRX	INITIAL_CUSTOMER_ TRX_ID CUST_TRX_TYPE_ID
		TYPE (Invoice being adjusted) (ADJ)	AR_ADJUSTMENTS RA_CUSTOMER_TRX	CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
RELATED_TRANSACTION_TYPE	RA_CUST_TRX_TYPES	NAME (Invoice being credited) (TRX)	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		NAME (Commitment related to an invoice) (TRX)	RA_CUSTOMER_TRX	INITIAL_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		NAME (Invoice being adjusted) (ADJ)	AR_ADJUSTMENTS RA_CUSTOMER_TRX	CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
RELATED_TRANSACTION_ID	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID (TRX)
		INITIAL_CUSTOMER_TRX_ID (TRX)
	AR_ADJUSTMENTS	CUSTOMER_TRX_ID (ADJ)
TRANSACTION_NUMBER	RA_CUSTOMER_TRX	TRX_NUMBER
	AR_CASH_RECEIPTS	RECEIPT_NUMBER
	AR_ADJUSTMENTS	ADJUSTMENT_NUMBER
TRANSACTION_DATE	RA_CUSTOMER_TRX	TRX_DATE
	AR_CASH_RECEIPTS	RECEIPT_DATE
	AR_ADJUSTMENTS	APPLY_DATE
BATCH_NAME	RA_BATCHES	NAME (TRX)	RA_CUSTOMER_TRX	BATCH_ID
	AR_BATCHES	NAME (CR)	AR_CASH_RECEIPT_HISTORY	BATCH_ID (Receipt batch from first posted record)
BATCH_SOURCE_NAME	RA_BATCHES_SOURCES	NAME (TRX)	RA_CUSTOMER_TRX	BATCH_SOURCE_ID
	AR_BATCHES_SOURCES	NAME (CR)	AR_CASH_RECEIPT_HISTORY AR_BATCHES	BATCH_ID (Receipt batch from first posted record) BATCH_SOURCE_ID
SET_OF_BOOKS_NAME	GL_SET_OF_BOOKS	NAME	RA_CUSTOMER_TRX	SET_OF_BOOKS_ID
	GL_SET_OF_BOOKS	NAME	AR_CASH_RECEIPTS	SET_OF_BOOKS_ID
	GL_SET_OF_BOOKS	NAME	AR_ADJUSTMENTS	SET_OF_BOOKS_ID
AMOUNT	RA_CUST_TRX_LINE_GL_DIST	AMOUNT (from 'REC' record)
	AR_CASH_RECEIPTS	AMOUNT
	AR_ADJUSTMENTS	AMOUNT
TYPE	AR_CASH_RECEIPTS	TYPE (CR)
	AR_ADJUSTMENTS	TYPE (ADJ)
ADJUSTMENT_TYPE	AR_ADJUSTMENTS	ADJUSTMENT_TYPE (ADJ)
POST_TO_GL	RA_CUST_TRX_TYPES	POST_TO_GL (TRX)	RA_CUSTOMER_TRX	CUST_TRX_TYPE_ID
ACCOUNTING_AFFECT_FLAG	RA_CUST_TRX_TYPES	ACCOUNTING_AFFECT_FLAG (TRX)	RA_CUSTOMER_TRX	CUST_TRX_TYPE_ID
REASON_CODE_MEANING	AR_LOOKUPS	MEANING (Type:'INVOICING_REASON') (TRX)	RA_CUSTOMER_TRX	REASON_CODE
		MEANING (Type:'ADJUST_REASON') (ADJ)	AR_ADJUSTMENTS	REASON_CODE
CASH_RECEIPT_STATUS	AR_CASH_RECEIPTS	STATUS (CR)
CASH_RECEIPT_HISTORY_STATUS	AR_CASH_RECEIPT_HISTORY	STATUS (where current_record_flag = Y) (CR)
BILL_TO_CUSTOMER_NUMBER	HZ_PARTIES	PARTY_NUMBER	RA_CUSTOMER_TRX	BILL_TO_CUSTOMER_ID
		(CR)	AR_CASH_RECEIPTS	PAY_FROM_CUSTOMER
BILL_TO_CUSTOMER_NAME	HZ_PARTIES	PARTY_NAME (TRX)	RA_CUSTOMER_TRX	BILL_TO_CUSTOMER_ID
		(CR)	AR_CASH_RECEIPTS	PAY_FROM_CUSTOMER
BILL_TO_CUSTOMER_LOCATION	HZ_CUST_ACCT_SITE	LOCATION	RA_CUSTOMER_TRX HZ_CUST_SITE_USES	BILL_TO_SITE_USE_ID SITE_USE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES	CUSTOMER_SITE_USE_ID SITE_USE_ID
BILL_TO_CUSTOMER_ ADDRESS1,2,3,&4	HZ_LOCATIONS	ADDRESS1,2,3,&4 (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_LOCATION	BILL_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES HZ_LOCATION	CUSTOMER_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
BILL_TO_CUSTOMER_CITY	HZ_LOCATIONS	CITY (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_LOCATION	BILL_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES HZ_LOCATION	CUSTOMER_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
BILL_TO_CUSTOMER_STATE	HZ_LOCATIONS	STATE (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_LOCATION	BILL_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	CUSTOMER_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
BILL_TO_CUSTOMER_COUNTRY	HZ_LOCATIONS	COUNTRY (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	BILL_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	CUSTOMER_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
BILL_TO_CUSTOMER_ POSTAL_CODE	HZ_LOCATIONS	POSTAL_CODE (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	BILL_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
		(CR)	AR_CASH_RECEIPTS HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	CUSTOMER_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
SHIP_TO_CUSTOMER_NUMBER	HZ_PARTIES	PARTY_NUMBER	RA_CUSTOMER_TRX	SHIP_TO_CUSTOMER_ID
SHIP_TO_CUSTOMER_NAME	HZ_PARTIES	PARTY_NAME (TRX)	RA_CUSTOMER_TRX	SHIP_TO_CUSTOMER_ID
SHIP_TO_CUSTOMER_LOCATION	HZ_CUST_SITE_USES	LOCATION (TRX)	RA_CUSTOMER_TRX	SHIP_TO_SITE_USE_ID
SHIP_TO_CUSTOMER_ADDRESS1,2,3,&4	HZ_LOCATIONS	ADDRESS1,2,3,&4 (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	SHIP_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
SHIP_TO_CUSTOMER_CITY	HZ_LOCATIONS	CITY (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	SHIP_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
SHIP_TO_CUSTOMER_STATE	HZ_LOCATIONS	STATE (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	SHIP_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
SHIP_TO_CUSTOMER_COUNTRY	HZ_LOCATIONS	COUNTRY (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	SHIP_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
SHIP_TO_CUSTOMER_POSTAL_CODE	HZ_LOCATIONS	POSTAL_CODE (TRX)	RA_CUSTOMER_TRX HZ_CUST_SITE_USES HZ_CUST_ACCT_SITE	SHIP_TO_SITE_USE_ID SITE_USE_ID CUSTOMER_SITE_ID
REMIT_TO_ADDRESS1,2,3,4	HZ_LOCATIONS	ADDRESS1,2,3,&4 (TRX)	RA_CUSTOMER_TRX HZ_CUST_ACCT_SITE	REMIT_TO_ADDRESS_ID CUSTOMER_SITE_ID
REMIT_TO_CITY	HZ_LOCATIONS	CITY (TRX)	RA_CUSTOMER_TRX HZ_CUST_ACCT_SITE	REMIT_TO_ADDRESS_ID CUSTOMER_SITE_ID
REMIT_TO_STATE	HZ_LOCATIONS	STATE (TRX)	RA_CUSTOMER_TRX HZ_CUST_ACCT_SITE	REMIT_TO_ADDRESS_ID CUSTOMER_SITE_ID
REMIT_TO_COUNTRY	HZ_LOCATIONS	COUNTRY (TRX)	RA_CUSTOMER_TRX HZ_CUST_ACCT_SITE	REMIT_TO_ADDRESS_ID CUSTOMER_SITE_ID
REMIT_TO_POSTAL_CODE	HZ_LOCATIONS	POSTAL_CODE (TRX)	RA_CUSTOMER_TRX HZ_CUST_ACCT_SITE	REMIT_TO_ADDRESS_ID CUSTOMER_SITE_ID
SALESREP_NAME	RA_SALESREPS	NAME (TRX)	RA_CUSTOMER_TRX	PRIMARY_SALESREP_ID
TERM_NAME	RA_TERMS	NAME (TRX)	RA_CUSTOMER_TRX	TERM_ID
TERM_DUE_DATE	RA_CUSTOMER_TRX	TERM_DUE_DATE (holds final due date for payment schedule) (TRX)
PRINTING_LAST_PRINTED	RA_CUSTOMER_TRX	PRINTING_LAST_PRINTED (TRX)
PRINTING_OPTION	RA_CUSTOMER_TRX	PRINTING_OPTION (TRX)
PURCHASE_ORDER	RA_CUSTOMER_TRX	PURCHASE_ORDER (TRX)
COMMENTS	RA_CUSTOMER_TRX	COMMENTS
	AR_CASH_RECEIPTS	COMMENTS
	AR_ADJUSTMENTS	COMMENTS
EXCHANGE_RATE_TYPE	RA_CUSTOMER_TRX	EXCHANGE_RATE_TYPE (TRX)
	AR_CASH_RECEIPTS	EXCHANGE_RATE_TYPE (CR)
EXCHANGE_RATE_DATE	RA_CUSTOMER_TRX	EXCHANGE_RATE_DATE (TRX)
	AR_CASH_RECEIPTS	EXCHANGE_RATE_DATE (CR)
EXCHANGE_RATE	RA_CUSTOMER_TRX	EXCHANGE_RATE (TRX)
	AR_CASH_RECEIPTS	EXCHANGE_RATE (CR)
CURRENCY_CODE	RA_CUSTOMER_TRX	INVOICE_CURRENCY_CODE (TRX)
	AR_CASH_RECEIPTS	CURRENCY_CODE (CR)
GL_DATE	RA_CUST_TRX_LINE_GL_DIST	GL_DATE (from 'REC' record) or TRX_DATE (if post to GL = N)
	AR_CASH_RECEIPT_HISTORY	GL_DATE (where current_record_flag = Y)
	AR_ADJUSTMENTS	GL_DATE
REVERSAL_DATE	AR_CASH_RECEIPTS	REVERSAL_DATE (CR)
REVERSAL_CATEGORY	AR_CASH_RECEIPTS	REVERSAL_CATEGORY (CR)
REVERSAL_REASON_CODE_MEANING	AR_LOOKUPS	MEANING (TYPE:CKAJST_REASON) (CR)	AR_CASH_RECEIPTS	REVERSAL_REASON_CODE
REVERSAL_COMMENTS	AR_CASH_RECEIPTS	REVERSAL_COMMENTS (CR)
ATTRIBUTE_CATEGORY	RA_CUSTOMER_TRX	ATTRIBUTE_CATEGORY
	AR_CASH_RECEIPTS	ATTRIBUTE_CATEGORY
	AR_ADJUSTMENTS	ATTRIBUTE_CATEGORY
ATTRIBUTE1-15	RA_CUSTOMER_TRX	ATTRIBUTE1-15
	AR_CASH_RECEIPTS	ATTRIBUTE1-15
	AR_ADJUSTMENTS	ATTRIBUTE1-15
RECEIPT_METHOD_NAME	AR_RECEIPT_METHODS	NAME (CR)	AR_CASH_RECEIPTS	RECEIPT_METHOD_ID
WAYBILL_NUMBER	RA_CUSTOMER_TRX	WAYBILL_NUMBER (TRX)
DOCUMENT_SEQUENCE_NAME	FND_DOCUMENT_SEQUENCES	NAME	RA_CUSTOMER_TRX	DOC_SEQUENCE_ID
			AR_CASH_RECEIPTS	DOC_SEQUENCE_ID
			AR_ADJUSTMENTS	DOC_SEQUENCE_ID
DOCUMENT_SEQUENCE_VALUE	RA_CUSTOMER_TRX	DOC_SEQUENCE_VALUE
	AR_CASH_RECEIPTS	DOC_SEQUENCE_VALUE
	AR_ADJUSTMENTS	DOC_SEQUENCE_VALUE
START_DATE_COMMITMENT	RA_CUSTOMER_TRX	START_DATE_COMMITMENT (TRX)
END_DATE_COMMITMENT	RA_CUSTOMER_TRX	END_DATE_COMMITMENT (TRX)
INVOICING_RULE_NAME	RA_RULES	NAME (TRX)	RA_CUSTOMER_TRX	INVOICING_RULE_ID
CUSTOMER_BANK_ACCOUNT_NAME	AP_BANK_ACCOUNTS	BANK_ACCOUNT_NAME (TRX)	RA_CUSTOMER_TRX	CUSTOMER_BANK_ACCOUNT_ID
		(CR)	AR_CASH_RECEIPTS	CUSTOMER_BANK_ACCOUNT_ID
DEPOSIT_DATE	AR_CASH_RECEIPTS	DEPOSIT_DATE (CR)
FACTOR_DISCOUNT_AMOUNT	AR_CASH_RECEIPTS	FACTOR_DISCOUNT_AMOUNT (CR)
INTERFACE_HEADER_CONTEXT	RA_CUSTOMER_TRX	INTERFACE_HEADER_CONTEXT (TRX)
INTERFACE_HEADER_ATTRIBUTE1- 15	RA_CUSTOMER_TRX	INTERFACE_HEADER_ATTRIBUTE1-15 (TRX)
BANK_DEPOSIT_NUMBER	AR_BATCHES	BANK_DEPOSIT_NUMBER (CR)	AR_CASH_RECEIPT_HISTORY	BATCH_ID (for remit batch)
REFERENCE_TYPE	AR_CASH_RECEIPTS	REFERENCE_TYPE (CR)
REFERENCE_ID	AR_CASH_RECEIPTS	REFERENCE_ID (CR)
CUSTOMER_RECEIPT_REFERENCE	AR_CASH_RECEIPTS	CUSTOMER_RECEIPT_REFERENCE (CR)
BANK_ACCOUNT_NAME	AP_BANK_ACCOUNTS	BANK_ACCOUNT_NAME (CR)	AR_CASH_RECEIPTS	REMITTANCE_BANK_ACCOUNT_ID
ACCTD_AMOUNT	RA_CUST_TRX_LINE_GL_DIST	ACCTD_AMOUNT (from 'REC' record)
	AR_RECEIVABLES_APPLICATIONS	sum (ACCTD_AMOUNT_APPLIED_FROM)
	AR_MISC_CASH_DISTRIBUTIONS	sum (ACCTD_AMOUNT)
	AR_ADJUSTMENTS	ACCTD_AMOUNT
EXCHANGE_GAIN_LOSS	AR_RECEIVABLES_APPLICATIONS	sum (ACCTD_AMOUNT_APPLIED_FROM) - sum (ACCTD_AMOUNT_APPLIED_TO) (CR)
EARNED_DISCOUNT_TAKEN	AR_RECEIVABLES_APPLICATIONS	sum (EARNED_DISCOUNT_TAKEN) (CR)
UNEARNED_DISCOUNT_TAKEN	AR_RECEIVABLES_APPLICATIONS	sum (UNEARNED_DISCOUNT_TAKEN) (CR)
ACCT_EARNED_DISCOUNT_TAKEN	AR_RECEIVABLES_APPLICATIONS	sum (ACCT_EARNED_DISCOUNT_TAKEN) (CR)
ACCT_UNEARNED_DISCOUNT_TAKEN	AR_RECEIVABLES_APPLICATIONS	sum (ACCT_UNEARNED_DISCOUNT_TAKEN) (CR)

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.

AR_ARCHIVE_DETAIL	Source Database Columns	Source Columns	Derived From Database Tables	Derived From Columns
ARCHIVE_ID
TRANSACTION_CLASS	RA_CUST_TRX_TYPES	TYPE	RA_CUSTOMER_TRX	CUST_TRX_TYPE_ID
	AR_CASH_RECEIPTS	TYPE
	Constant Value	ADJ
TRANSACTION_TYPE	RA_CUST_TRX_TYPES	NAME	RA_CUSTOMER_TRX	CUST_TRX_TYPE_ID
TRANSACTION_ID	RA_CUSTOMER_TRX	CUSTOMER_TRX_ID
	AR_CASH_RECEIPTS	CASH_RECEIPT_ID
	AR_ADJUSTMENTS	ADJUSTMENT_ID
TRANSACTION_LINE_ID	RA_CUSTOMER_TRX_LINES	CUSTOMER_TRX_LINE_ID
	RA_CUST_TRX_LINE_GL_DIST	CUSTOMER_TRX_LINE_ID
RELATED_TRANSACTION_CLASS	RA_CUST_TRX_TYPES	TYPE (Invoice being credited)	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		TYPE (Commitment relating to an invoice)	RA_CUSTOMER_TRX	INITIAL_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		TYPE (Invoice being adjusted)	AR_ADJUSTMENTS RA_CUSTOMER_TRX	CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		TYPE (credit memo and receipt applications)	AR_RECEIVABLES_APPLICATIONS RA_CUSTOMER_TRX	APPLIED_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
RELATED_TRANSACTION_TYPE	RA_CUST_TRX_TYPES	NAME	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		NAME	RA_CUSTOMER_TRX	INITIAL_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		NAME	AR_ADJUSTMENTS RA_CUSTOMER_TRX	CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
		NAME	AR_RECEIVABLES_APPLICATIONS RA_CUSTOMER_TRX	APPLIED_CUSTOMER_TRX_ID CUST_TRX_TYPE_ID
RELATED_TRANSACTION_ID	RA_CUSTOMER_TRX	PREVIOUS_CUSTOMER_TRX_ID
		INITIAL_CUSTOMER_TRX_ID
	AR_ADJUSTMENTS	CUSTOMER_TRX_ID
	AR_RECEIVABLES_APPLICATIONS	APPLIED_CUSTOMER_TRX_ID
RELATED_TRANSACTION_LINE_ID	RA_CUSTOMER_TRX_LINES	PREVIOUS_CUSTOMER_TRX_LINE_ID (Line being credited)
		INITIAL_CUSTOMER_TRX_LINE_ID (Commitment relating to an invoice)
LINE_NUMBER	RA_CUSTOMER_TRX_LINES	LINE_NUMBER (used only for TRX and line level credit memo applications)
DISTRIBUTION_TYPE	RA_CUSTOMER_TRX_LINES	VALUE: LINE
	AR_CASH_RECEIPT_HISTORY	VALUE: CRH
	AR_ADJUSTMENTS	VALUE: ADJ
	AR_RECEIVABLES_APPLICATIONS	VALUE: REC_APP
	AR_RECEIVABLES_APPLICATIONS	VALUE: CM_APP
	AR_MISC_CASH_DISTRIBUTIONS	VALUE: MCD
	RA_CUST_TRX_LINE_GL_DIST	ACCOUNT_CLASS
APPLICATION_TYPE	AR_RECEIVABLES_APPLICATIONS	APPLICATION_TYPE
REASON_CODE_MEANING	AR_LOOKUPS	MEANING (TYPE: INVOICING_REASON)	RA_CUSTOMER_TRX_LINES	REASON_CODE
LINE_DESCRIPTION	RA_CUSTOMER_TRX_LINES	DESCRIPTION
ITEM_NAME	MTL_SYSTEM_ITEMS	concatenated SEGMENT1..20	RA_CUSTOMER_TRX_LINES	INVENTORY_ITEM_ID
QUANTITY	RA_CUSTOMER_TRX_LINES	QUANTITY_CREDITED
	RA_CUSTOMER_TRX_LINES	QUANTITY_INVOICED
UNIT_SELLING_PRICE	RA_CUSTOMER_TRX_LINES	UNIT_SELLING_PRICE
LINE_TYPE	RA_CUSTOMER_TRX_LINES	LINE_TYPE
ATTRIBUTE_CATEGORY	RA_CUSTOMER_TRX_LINES	ATTRIBUTE_CATEGORY
	RA_CUST_TRX_LINE_GL_DIST	ATTRIBUTE_CATEGORY
	AR_CASH_RECEIPT_HISTORY	ATTRIBUTE_CATEGORY
	AR_MISC_CASH_DISTRIBUTIONS	ATTRIBUTE_CATEGORY
	AR_ADJUSTMENTS	ATTRIBUTE_CATEGORY
	AR_RECEIVABLE_APPLICATIONS	ATTRIBUTE_CATEGORY
ATTRIBUTE1-15	RA_CUSTOMER_TRX_LINES	ATTRIBUTE1-15
	RA_CUST_TRX_LINE_GL_DIST	ATTRIBUTE1-15
	AR_CASH_RECEIPT_HISTORY	ATTRIBUTE1-15
	AR_MISC_CASH_DISTRIBUTIONS	ATTRIBUTE1-15
	AR_ADJUSTMENTS	ATTRIBUTE_1- 15
	AR_RECEIVABLE_APPLICATIONS	ATTRIBUTE_1 -15
UOM_CODE	RA_CUSTOMER_TRX_LINES	UOM_CODE
USSGL_TRANSACTION_CODE	RA_CUST_TRX_LINE_GL_DIST	USSGL_TRANSACTION_CODE
	AR_CASH_RECEIPTS	USSGL_TRANSACTION_CODE
	AR_MISC_CASH_DISTRIBUTIONS	USSGL_TRANSACTION_CODE
	AR_ADJUSTMENTS	USSGL_TRANSACTION_CODE
	AR_RECEIVABLE_APPLICATIONS	USSGL_TRANSACTION_CODE
TAX_RATE	RA_CUSTOMER_TRX_LINES	TAX_RATE
	AR_VAT_TAX	TAX_RATE	AR_CASH_RECEIPTS	VAT_TAX_ID
TAX_CODE	AR_VAT_TAX	TAX_CODE	RA_ CUSTOMER_TRX_LINES	VAT_TAX_ID
			AR_CASH_RECEIPTS	VAT_TAX_ID
TAX_PRECEDENCE	RA_CUSTOMER_TRX_LINES	TAX_PRECEDENCE
LAST_PERIOD_TO_CREDIT	RA_CUSTOMER_TRX_LINES	LAST_PERIOD_TO_CREDIT
COMMENTS	AR_RECEIVABLE_APPLICATIONS	COMMENTS
	AR_MISC_CASH_DISTRIBUTIONS	COMMENTS
LINE_ADJUSTED	AR_ADJUSTMENTS	LINE_ADJUSTED
FREIGHT_ADJUSTED	AR_ADJUSTMENTS	FREIGHT_ADJUSTED
TAX_ADJUSTED	AR_ADJUSTMENTS	TAX_ADJUSTED
RECEIVABLES_CHARGES_ADJUSTED	AR_ADJUSTMENTS	RECEIVABLES_CHARGES_ ADJUSTED
LINE_APPLIED	AR_RECEIVABLE_APPLICATIONS	LINE_APPLIED
FREIGHT_APPLIED	AR_RECEIVABLE_APPLICATIONS	FREIGHT_ APPLIED
TAX_APPLIED	AR_RECEIVABLE_APPLICATIONS	TAX_APPLIED
RECEIVABLES_CHARGES_APPLIED	AR_RECEIVABLE_APPLICATIONS	RECEIVABLES_CHARGES_APPLIED
EARNED_DISCOUNT_TAKEN	AR_RECEIVABLE_APPLICATIONS	EARNED_DISCOUNT_TAKEN
UNEARNED_DISCOUNT_TAKEN	AR_RECEIVABLE_APPLICATIONS	UNEARNED_DISCOUNT_TAKEN
ACCTD_AMOUNT_APPLIED_FROM	AR_RECEIVABLE_APPLICATIONS	ACCTD_AMOUNT_APPLIED_FROM
ACCTD_AMOUNT_APPLIED_TO	AR_RECEIVABLE_APPLICATIONS	ACCTD_AMOUNT_APPLIED_TO
ACCTD_EARNED_DISC_TAKEN	AR_RECEIVABLE_APPLICATIONS	ACCT_EARNED_DISCOUNT_TAKEN
ACCTD_UNEARNED_DISC_TAKEN	AR_RECEIVABLE_APPLICATIONS	ACCT_UNEARNED_DISCOUNT_TAKEN
FACTOR_DISCOUNT_AMOUNT	AR_CASH_RECEIPT_HISTORY	FACTOR_DISCOUNT_AMOUNT
ACCTD_FACTOR_DISCOUNT_AMOUNT	AR_CASH_RECEIPT_HISTORY	ACCTD_ FACTOR_DISCOUNT_AMOUNT
INTERFACE_LINE_CONTEXT	RA_CUSTOMER_TRX_LINES	INTERFACE_LINE_CONTEXT
INTERFACE_LINE_ATTRIBUTE1-15	RA_CUSTOMER_TRX_LINES	INTERFACE_ LINE_ATTRIBUTE1-15
EXCHANGE_RATE_TYPE	AR_CASH_RECEIPT_HISTORY	EXCHANGE_RATE_TYPE
EXCHANGE_RATE_DATE	AR_CASH_RECEIPT_HISTORY	EXCHANGE_RATE_DATE
EXCHANGE_RATE	AR_CASH_RECEIPT_HISTORY	EXCHANGE_RATE
DUE_DATE	AR_PAYMENT_SCHEDULES	DUE_DATE (allows you to derive transaction due date(s) from credit and receipt applications)	AR_RECEIVABLES_APPLICATIONS	PAYMENT_SCHEDULE_ID
APPLY_DATE	AR_RECEIVABLE_APPLICATIONS	APPLY_DATE
	AR_MISC_CASH_DISTRIBUTIONS	APPLY_DATE
MOVEMENT_ID	RA_CUSTOMER_TRX_LINES	MOVEMENT_ID
TAX_VENDOR_RETURN_CODE	RA_CUSTOMER_TRX_LINES	TAX_VENDOR_RETURN_CODE
TAX_AUTHORITY_TAX_RATES	AR_SALES_TAX	LOCATION_RATE1-10	RA_CUSTOMER_TRX_LINES	SALES_TAX_ID
TAX_EXEMPTION_FLAG	RA_CUSTOMER_TRX_LINES	TAX_EXEMPTION_FLAG
TAX_EXEMPTION_ID	RA_CUSTOMER_TRX_LINES	TAX_EXEMPTION_ID
TAX_EXEMPTION_TYPE	RA_TAX_EXEMPTIONS	EXEMPTION_TYPE	RA_CUSTOMER_TRX_LINES	TAX_EXEMPTION_ID
TAX_EXEMPTION_REASON	LINE_TYPE = TAX AR_LOOKUPS LINE_TYPE = LINE AR_LOOKUPS	MEANING (TYPE:TAX_REASON) MEANING (TYPE:TAX_REASON)	RA_TAX_EXEMPTIONS and RA_CUSTOMER_TRX_LINES RA_CUSTOMER_TRX_LINES	TAX_EXEMPT_REASON_CODE TAX_EXEMPTION_ID TAX_EXEMPT_REASON_CODE
TAX_EXEMPTION_NUMBER	LINE_TYPE = TAX RA_TAX_EXEMPTIONS LINE_TYPE = LINE RA_CUSTOMER_TRX_LINES	CUSTOMER_EXEMPTION_NUMBER TAX_EXEMPT_NUMBER	RA_CUSTOMER_TRX_LINES	TAX_EXEMPTION_ID
ITEM_EXCEPTION_RATE	RA_ITEM_EXCEPTION_RATES	LOCATION1- 10_RATE	RA_CUSTOMER_TRX_LINES	ITEM_EXCEPTION_RATE_ID
ITEM_EXCEPTION_REASON	AR_LOOKUPS	MEANING (TYPE:TAX_EXCEPTION_REASON)	RA_ITEM_EXCEPTION_RATES and RA_CUSTOMER_TRX_LINES	REASON_CODE ITEM_EXCEPTION_RATE_ID
AMOUNT	RA_CUSTOMER_TRX_LINES	EXTENDED_AMOUNT
	RA_CUST_TRX_LINE_GL_DIST	AMOUNT
	AR_ADJUSTMENTS	AMOUNT
	AR_CASH_RECEIPT_HISTORY	AMOUNT
	AR_MISC_CASH_DISTRIBUTIONS	AMOUNT
	AR_RECEIVABLES_APPLICATIONS	AMOUNT_APPLIED
ACCTD_AMOUNT	RA_CUST_TRX_LINE_GL_DIST	ACCTD_AMOUNT
	AR_ADJUSTMENTS	ACCTD_AMOUNT
	AR_CASH_RECEIPT_HISTORY	ACCTD_AMOUNT
	AR_MISC_CASH_DISTRIBUTIONS	ACCTD_AMOUNT
GL_DATE	RA_CUST_TRX_LINE_GL_DIST	GL_DATE or TRX_DATE (if post to GL = N)
	AR_MISC_CASH_DISTRIBUTIONS	GL_DATE
	AR_ADJUSTMENTS	GL_DATE
	AR_RECEIVABLE_APPLICATIONS	GL_DATE
	AR_CASH_RECEIPT_HISTORY	GL_DATE
GL_POSTED_DATE	RA_CUST_TRX_LINE_GL_DIST	GL_POSTED_DATE
	AR_MISC_CASH_DISTRIBUTIONS	GL_POSTED_DATE
	AR_ADJUSTMENTS	GL_POSTED_DATE
	AR_RECEIVABLE_APPLICATIONS	GL_POSTED_DATE
	AR_CASH_RECEIPT_HISTORY	GL_POSTED_DATE
ACCOUNTING_RULE_NAME	RA_RULES	NAME	RA_CUSTOMER_TRX_LINES	ACCOUNTING_RULE_ID
RULE_DURATION	RA_CUSTOMER_TRX_LINES	ACCOUNTING_RULE_DURATION
RULE_START_DATE	RA_CUSTOMER_TRX_LINES	RULE_START_DATE

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.

AR_ARCHIVE_DETAIL	Source Database Columns	Source Columns	Derived From Database Tables	Derived From Columns
ACCOUNT_COMBINATION1	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	RA_CUST_TRX_LINE_GL_DIST	CODE_COMBINATION_ID
	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_ADJUSTMENTS	CODE_COMBINATION_ID
	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_CASH_RECEIPT_HISTORY	ACCOUNT_CODE_COMBINATION_ID
	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_MISC_CASH_DISTRIBUTIONS	CODE_COMBINATION_ID
	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_RECEIVABLE_APPLICATIONS	CODE_COMBINATION_ID
ACCOUNT_COMBINATION2	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_CASH_RECEIPT_HISTORY	BANK_CHARGE_ACCOUNT_ID
ACCOUNT_COMBINATION3	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_RECEIVABLE_APPLICATIONS	EARNED_DISCOUNT_CCID
ACCOUNT_COMBINATION4	GL_CODE_COMBINATIONS	SEGMENT1-SEGMENT30	AR_RECEIVABLE_APPLICATIONS	UNEARNED_DISCOUNT_CCID

Related Topics

Preparing to Run Archive and Purge

Archive and Purge Cycle

Purge Criteria

Tables Purged

Archive Level

Data Not Archived

Monitoring Your Archive Purge

Running Archive and Purge

The Archive and Purge feature lets you periodically save and delete transactions that you no longer need online to reclaim space in your database and improve system performance. There are eight different programs available from this window. Depending on which program you run, a report might be generated to show you all the transactions that have been purged. If you are running in Preview mode, the report shows all purge candidates.

Prerequisites

• Prepare Receivables to run archive and purge

To submit Receivables Archive and Purge programs:

1. Navigate to the Submit Requests window.

2. Enter the Archive and Purge program Name to submit, or select a one from the list of values.

3. Choose OK.

4. Enter parameters for submitting this program. See: Archive and Purge Parameters.

   Note: When you run either the Archive and Purge Summary or Detail report, you must enter the Archive ID to use to generate your report. The report uses the format RRMMDDHHMISS for the Archive ID (two digit numerical designations for year, month, day, hour, minute, and seconds). This Archive ID is assigned when the archive program is submitted.

5. Choose OK.

6. To print the results of this submission, enter Print Options. Enter the number of Copies to print, a printing Style, and the Printer to use.

7. To save the output to a file, check the Save Output check box.

8. To run this program more than once, enter Run Options. You can enter a Resubmit interval, a date and time To Start the resubmission, and an ending date on which to cease repeating.

9. Choose Submit. Receivables displays a concurrent Request ID for this submission. You can use the Concurrent Requests Summary window to view the status of your Archive and Purge programs.

Related Topics

Monitoring Your Archive Purge

Status and Error Messages

Archive Detail/Summary Reports

Using Archive and Purge

Monitoring Requests, Oracle E-Business Suite User's Guide

Archive and Purge Parameters

Following are the parameters for the standard Archive and Purge program. No users can be on the system when running this program.

To run archive and purge while users are working on the system, run the Call New Archive and Purge Process.

Standard Archive and Purge Process

GL Date Type: Choose a validation type to determine which GL date is used to select transactions. There are three validation types you can use to limit the transactions selected for purge:

Variable	Description
Invoice GL Date	The Invoice GL Date type checks only the GL dates for the selected invoices. The GL date of all selected invoices must be on or before the end date of the period specified in the Purge Period parameter. General Ledger dates for related transactions are not checked.
Receipt GL Date	The Receipt GL Date type checks only the GL dates for the selected receipts. The GL date of all selected receipts must be on or prior to the end date of the period specified in the Purge Period parameter. General Ledger dates for related transactions are not checked.
All GL Dates	This date type is the most restrictive and requires that a transaction and all its related transactions have GL dates on or prior to the end date of the period specified in the Purge Period parameter. Receivables uses All GL Dates as the default value.

Archive Period: To determine which data is purged you must specify the period to be purged. Only closed periods are eligible for selection. All transactions that meet the purge criteria in this period are selected for purge. In addition, transactions in previous periods that meet the purge criteria and were not purged by earlier purges will also be selected.

Open Receivables Only: Transactions not open to receivables will never be paid and therefore, never closed. Enter Yes to indicate that you want to include only transactions with Open Receivables set to Yes. The default value for this parameter is No, allowing transactions to be selected regardless of the setting of the Open Receivables flag.

Postable Items Only: Enter Yes to indicate that you want to include only transactions with Post to GL set to Yes. The default value for this parameter is No, allowing transactions to be selected regardless of the setting of the Post to GL flag.

Customer Name: Enter a customer name if you wish to only purge transactions for a specific customer. If no value is entered for this parameter all customers will be included.

Archive Level: When you start the Archive/Purge programs you must select the level of detail you want to archive. Refer to the section on Archive Level for more information on which records are created for each archive level.

Summary Report Only: Enter Yes if you want to limit the Archive Report to summary information. The summary report includes the amount and count of transactions selected for purge. If you enter No, you will receive a summary report and a detail report which breaks down the summary information by customer. The default value for this parameter is Yes.

Number of Workers: This parameter is used during the Archive and Purge processes only. It is not used for selection and validation. Enter the number of parallel workers you want to use to run the Archive/Purge process. Parallel processing lets you split the program into several processes and run each process simultaneously thus decreasing the total run time of the program. The default value for this parameter is one.

Commit Size: This parameter is used during the selection and validation and archive process only. Enter the number of transactions you want to be processed before a save. The default value for this parameter is 1000.

Archive ID: Select the archive ID of the archive to be either used for generating a report or purged from the database. The program uses the format RRMMDDHHMISS for the Archive ID (two digit numerical designation for the year, month, day, hour, minute, and seconds). This value is based on the time the archive program is submitted.

Call New Archive and Purge Process

Following are the parameters for the Call New Archive and Purge Process. Users do not have to log off the system to run this program. This option does not create the Archive Purge Detail or Summary reports; instead, it writes information about the purge to a log file.

Important: This option does not purge deposits, guarantees, miscellaneous receipts or any items linked to these transactions.

Cut Off Date: The date to use when selecting transactions for archive purge. The program selects each transaction according to the GL date or transaction date. Transactions that do not post to the general ledger (post to GL flag is set to No) do not have a GL date. The program selects a transaction for purging if the GL or transaction date is earlier than the date you enter here.

Archive Level: The level of detail you want to archive. For more information, see: Archive Level.

Number of Workers: This parameter is used during the Archive and Purge processes only. It is not used for selection and validation. Enter the number of parallel workers you want to use to run the Archive/Purge process. Parallel processing lets you split the program into several processes and run each process simultaneously thus decreasing the total run time of the program. The default value for this parameter is one.

Related Topics

Purge Criteria

Archive and Purge Cycle

Tables Purged

Archive Level

Data Not Archived

Monitoring Your Archive Purge

Archive Tables

Running Archive and Purge

Archive Summary/Detail Reports

Receivables creates these reports automatically when you run the Archive and Purge, Archive-Preview, or Archive Restart program. Use these reports to review summary information for your Archive and Purge submission.

The Archive-Summary Report includes the amount and count of transactions selected for purge based on the AR_ARCHIVE_CONTROL_DETAIL table. The Archive Detail Report includes the amount and count of transactions selected for purge, as well as a breakdown of the summary information by customer. This report is based on the AR_ARCHIVE_HEADER and the AR_ARCHIVE_DETAIL tables. The Archive Detail report is generated automatically if you set the 'Summary Report Only' parameter to No.

If you run Archive-Preview, the report lists purge candidates. If you run either of the other two programs, the report provides details of the actual transactions purged.

You can submit this report for previous archive runs to review summary information for what was previously purged. To help you identify the correct archive run, the archive Id parameter is displayed in a date format, which indicates the exact date and time the program was run.

Note: Miscellaneous Receipts will not be Purged unless you run Archive and Purge for all customers because Miscellaneous Receipts are not related to specific customers. Therefore, if you run Archive and Purge for a specific customer, Miscellaneous Receipts will not be displayed in this report

Report Heading - Summary Report

Purge Period: The period from which the transactions have been archived and purged. The Archive Summary report may include transactions from past periods that were not eligible for purge when the archive and purge programs were run for that period and thus your report may include several periods. Each period will display on a separate page. The report is ordered by period.

Row Headings - Summary Report

Grand Total: The total amounts of debits and credits for the entire purge run, excluding Guarantees, Miscellaneous Receipts and transactions not open to receivables. This total should equal zero.

Total: The total amount of debits and credits for the period. The first total value should net to zero across all periods in the purge run. The second total for a period represents a total for Guarantees, Miscellaneous Receipts and transactions not open to receivables.

Report Heading - Detail Report

Customer: Archive/Purge may select transactions from past periods that were not eligible for purge when the archive and purge programs were run for that period, so your report may include customer transactions from several periods. The report is ordered by customer. Each customer will display on a separate page.

Row Headings - Detail Report

Total For Customer: The total amounts of debits and credits for the customer. Archive/Purge will not purge transactions unless the entire chain of transactions are closed and are being purged also. Consequently, the Customer Total may equal zero. This total would not equal zero for any of the following reasons:

• The customer's transactions were fully or partially paid or credited by another customer's receipt or credit memo which is also being purged.

• If Guarantees are listed, they will be included in the Customer Total. Guarantees have no related payment and will therefore not net to zero.

• If any of the customer's transactions are not open to receivables, they too have no related payment and so will not net to zero.

• If there was any exchange rate gain/loss or discounts taken during receipt application.

At the end of the report, these exception items are totaled separately so you can reconcile your Grand Total against individual Customer Totals.

Total Discounts: The total discounts taken across all customers. These items are included in the Grand Total but not in Customer Totals.

Total Gain/Loss: The total exchange rate gain/loss across all customers. These items are included in the Grand Total but not in Customer Totals.

Total Open Rec = N: The total items not open to Receivables across all customers. These items are not included in the Grand Total but are included in Customer Totals.

Total Guarantees: The total Guarantees across all customers. Guarantees are not included in the Grand Total but are included in Customer Totals.

Total Misc. Transactions: Miscellaneous receipts are not related to Customers and are therefore totaled separately at the end of the report. Miscellaneous receipts have no related invoice and so will not net to zero. Consequently, they are not included in the Grand Total of the report.

Miscellaneous Receipts will not be Purged unless you run Archive/Purge for all customers, because they are not related to specific customers. If you run Archive/Purge for a specific customer, the Total for Miscellaneous Transactions will not display.

Grand Total: The total amount of debits and credits for the entire purge run, excluding Guarantees, Miscellaneous Receipts, and transactions not open to receivables. The total across all your customers less the totals for Guarantees, transactions not open to receivables, discounts and exchange rate gain/loss should equal the Grand Total. This total should equal zero.

Related Topics

Running Archive and Purge

Using Archive and Purge

Running Standard Reports and Listings

Common Report Parameters