Skip Headers

Oracle Receivables User Guide
Release 12.1
Part Number E13522-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

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

the picture is described in the document text

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:

  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:

  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:

    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:

    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:

    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.

  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:

    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:

  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:

    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.

    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:

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:

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

the picture is described in the document text

Notes on the above diagram:

Archive different levels of cash receipt data

the picture is described in the document text

Notes on the above diagram:

Archive different levels of adjustment data

the picture is described in the document text

Notes on the above diagram:

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.

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:

AR_PURGE_TRX_AUX

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

AR_PURGE_REC_AUX

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

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:

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.

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.

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.

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:

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

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 Applications 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:

At the end of the report, these exception items are totalled 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 totalled 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