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
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.
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
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.
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
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.
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.
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.
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.
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
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
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.
Change user responsibility.
The Archive/Purge programs are only available to users with the AR Archive Purge User responsibility.
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.
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.
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.
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.
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.
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
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. |
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 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.
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 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 that were reconciled in Cash Management cannot be purged in Receivables until the related bank statement records in Cash Management are purged.
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
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
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
Preparing to Run Archive and Purge
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
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.
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.
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
Preparing to Run Archive and Purge
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.
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.
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.
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.
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.
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.
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
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
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) |
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
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
Navigate to the Submit Requests window.
Enter the Archive and Purge program Name to submit, or select a one from the list of values.
Choose OK.
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.
Choose OK.
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.
To save the output to a file, check the Save Output check box.
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.
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
Archive Detail/Summary Reports
Monitoring Requests, Oracle E-Business Suite User's Guide
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.
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.
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
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
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.
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.
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.
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