Oracle Receivable AutoInvoice Purge Program

Purpose

Oracle Receivables (AR): AutoInvoice Information Center > Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12 > Note 1127413.1

The AutoInvoice feature was developed to provide a means of creating large volumes of transactions without manual input. Since AutoInvoice processes this bulk data through an open interface table, it is not necessary to retain the data in the interface table once the transactions have been generated in Receivables. AutoInvoice Purge provides a mechanism to clean up the data once it has been processed.

This note contains an overview of the AutoInvoice Purge process. It provides setup, execution and troubleshooting information.

If you are encountering other issues involving AutoInvoice, please review Note 1075757.1, Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12

Note:
There is another feature in Receivables called Archive and Purge.
This allows a user to archive old data from core Receivables tables (e.g. Transactions, Receipts, Adjustments) and then purges the data. Archive and Purge does not impact the interface tables used by AutoInvoice. These two features are separate and distinct. 

This note does not discuss Archive and Purge.

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

1. Overview of AutoInvoice Purge

Data to be transferred into Receivables via AutoInvoice can be provided by integrated systems (e.g. Oracle Order Management) , via insertion of data into tables via feeder or legacy systems or via customized scripts that populate the interface tables.

The main interface tables are:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE SALESCREDITS_ALL

During processing of AutoInvoice, the data provided in the interface tables are transferred into core Receivables tables such as:
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_LINE_SALESREPS_ALL

Once the data is validated, and the transactions are created, data in the interface tables can be removed. For optimized performance, it is recommended that interface data be purged regularly to eliminate unnecessary overhead.

When invoked the AutoInvoice Purge process will remove from the interface tables, rows that have been successfully processed.

2. Running AutoInvoice Purge

There are 2 ways to invoke the AutoInvoice Purge process. Pros and Cons are listed for each option. Your business requirements as well as circumstances encountered using AutoInvoice, will help you determine what option will work best for you.

a. Automatically

By checking Purge Interface Tables checkbox in the System Options form, you are allowing AutoInvoice to clean up after itself once it has completed processing.

Responsibility: Receivables Manager
Navigation: Setup > System > System Options

Note: If  you are troubleshooting or debugging an AutoInvoice process wherein the imported data has an issue or is not what you expected, you should turn off the Automatic Purge of the Interface tables, so that you can review the Interface data and determine if the issue is due to how the data was passed in.

Pros of running AutoInvoice Purge automatically:

·         Interface data volume does not build up

·         Processed interface data does not take up disk space

·         Performance is not hampered by the presence of records that are already processed

Cons of running AutoInvoice Purge Automatically:

·         If you encounter an issue with the imported transactions, you cannot verify if the issue is due to the data provided in the interface table, because the record is no longer there

·         Adds overhead to the entire AutoInvoice process because deleting interface data is added to the tasks that need to be completed by the concurrent request (i.e. import + purge).

b. Manually

Submitting via the Submission Request form, gives you the flexibility of choosing when to purge processed data from the Interface tables.

Responsibility: Receivables Manager
Navigation: Interfaces > AutoInvoice
                    or View > Requests > Submit a New Request

1.      In Submit a New Request form, pick Single Request

2.      From the list of values pick AutoInvoice Purge Program

Pros of running AutoInvoice Purge manually:

·         You are in control of when the interface data is cleared out of the system

·         You can review interface data in case you encounter issues with the transactions created by the AutoInvoice process

·         By running purge separately, you can process the import of data faster and deal with the clean up later

Cons of running AutoInvoice Purge manually:

·         Interface data may build up and cause performance issues

·         The process is invoked manually so it may be 'forgotten'

·         May cause performance issues when the interface table contains a large volume of processed records

·          

3. Underlying Code and tables Impacted by AutoInvoice Purge

The AutoInvoice Purge process is defined in c-code raadel.opc.

The logic deletes rows from the interface tables, by cross-referencing that that parent row in RA_INTERFACE_LINES_ALL has INTERFACE_STATUS = 'P'.

Data is deleted from interface tables in the following order:
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_ERRORS_ALL

Note: Unless RA_INTERFACE_LINES_ALL.INTERFACE_STATUS = P, AutoInvoice Purge will not delete the row and its supporting records in the other RA_INTERFACE* tables.

4. Troubleshooting AutoInvoice Purge

   a. Orphan interface data

There are cases wherein you may have records in children interface tables: RA_INTERFACE_DISTRIBUTIONS_ALL, RA_INTERFACE_SALESCREDITS_ALL or RA_INTERFACE_ERRORS_ALL wherein the INTERFACE_LINE_ID value no longer exists in parent interface table: RA_INTERFACE_LINES_ALL. This situation is typically referred to as orphan rows.

The following code illustrates the condition used by the code to delete records from a child table:

DELETE FROM RA_INTERFACE_DISTRIBUTIONS D
WHERE EXISTS
(SELECT 'X'
 FROM RA_INTERFACE_LINES L
 WHERE L.INTERFACE_LINE_ID = D.INTERFACE_LINE_ID
 AND L.INTERFACE_STATUS = 'P');

For an orphan row, the condition L.INTERFACE_LINE_ID = D.INTERFACE_LINE_ID will never be true, hence such rows will never get deleted by the AutoInvoice Purge process. The presence of orphan rows typically will not cause problems in the AutoInvoice process. However, if you have large volumes of orphans, they may impact the performance. If you need assistance in eliminating orphan rows, please contact Oracle Support.

b. Unable to Query records in Interface Lines

You have not checked the Purge Interface Tables in the System Options form. Thus, interface data remains in the table even after they have been successfully processed. You want to view the interface lines so you try to run a query using the Interface Lines form.

Responsibility: Receivables Manager
Navigation: Control > AutoInvoice > Interface Lines

The form does not return data as you expect.

This is expected functionality. The Interface Lines form was created to view records that have issues preventing them from being successfully processed. The form has a hard-coded condition that checks for:

interface_status is null and link_to_line_id is null

For records that have been processed, interface_status = P, which then prevents the form from showing the data. If you want to see data for a successfully processed row, you need to use the Transactions form.

5. Patching

The following lists the latest versions of AutoInvoice Purge code available as of Last Update Date of this note. To ensure you are not encountering an issue which has already been reported and fixed, check that you are on the latest version of the code.

Filename	Release 11.5	Release 12.x
raadel.opc	AR.N: 115.3.15102.2 Patch 4229870 AR.O: 115.4.15104.3 Patch 7582004	12.0 and 12.1: 120.3 baseline

6. Logging A Service Request (SR)

In the event that this troubleshooting guide does not allow you to resolve your issue regarding AutoInvoice Purge, please log a Service Request with Support. When logging your SR, be sure to provide the following information to expedite resolution of your issue: 

• AutoInvoice Purge Concurrent Process log file (see Note 1079666.1).
• For performance issues, provide the trace files generated by AutoInvoice Purge (see Note 1079666.1).
• If you have data issues, include output from the following Oracle Diagnostics:
• Oracle Receivables AutoInvoice Interface Data Collection Test - this will Support what your interface data looks like.
• Oracle Receivables Transaction Data Setup Test - this will show Support what the created transaction looks like.

For information on Oracle Diagnostics, please review the Diagnostic catalogs for your release as noted below:

• Release 11.5: see Note 179661.1, E-Business Suite Diagnostics 11i Test Catalog
• Release 12.x: see Note 421245.1, E-Business Suite Diagnostics References for R12

Once you have identified your release, locate the note for the specific diagnostic you would like to run.  For example, in 12.0.6, Note 732203.1, R12.0.6+ : Oracle Receivables AutoInvoice Interface Data Collection Test.

Feedback

We hope you find this information useful. Our desire is to provide the right information when you need it. Please let us know how we are doing. We welcome your feedback.

References

NOTE:1067402.1 - AutoInvoice Setup for Release 12: A Case Study Using a Simple Script
NOTE:1075757.1 - Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12
NOTE:179661.1 - E-Business Suite Diagnostics 11i Test Catalog
NOTE:421245.1 - E-Business Suite Diagnostics References for R12