Whitepaper: Basic pre-requisites / setups to decrease the risk of Performance issues during AutoInvoice Import

Problem Statement:

While importing transactions into Oracle Applications, the AutoInvoice program takes longer time to complete the process. This performance issue can be attributed to basic AR setup issues, product related issues, hardware and network problems.

Resolution

Oracle Application Setup: Receivables System Options

-          Setting purge interface table = Y

-          Set Max Memory (in bytes) = Suggested less than 3MB (3145728 bytes)

-          Log File Message Level = 0

-          Tuning Segments

                   o   Accounting Flex

                   o   System Items

                   o   Territory

Oracle Application Setup: Manage System Profile Options

-          AR:  AutoInvoice Gather Statistics

-          AR: Maximum Lines Per AutoInvoice Worker

Database Options

Create UNIQUE or Non-UNIQUE index on attribute columns in database tables, setup as part of Receivables Descriptive Flexfields. The tables that are involved are RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_INTERFACE_LINES_ALL. If line transaction flexfields are not setup then creating index on these tables will become an overhead, these table columns need to be indexed only if the columns contain data.

The tables RA_INTERFACE_DISTRIBUTIONS_ALL and RA_INTERFACE_SALESCREDITS_ALL needs to be indexed only if you are NOT using sales credits or accounting distributions (or if you have set up the application to derive these values)

Concurrent Manager

-          Create separate independent concurrent manager for AR Invoice Processing which eliminates the effect of other programs run using standard manager

-          Set the AR AutoInvoice Master program to run in parallel. When executing the AR AutoInvoice Master program, enter the parameter to run the number of instances in parallel (1-15). The number can be decided on considering system load, data volume.

-          Execute AutoInvoice Master program and not the AutoInvoice import program as a standalone, the former program coordinates the activities being done such as gathering statistics, marking records for processing (setting request IDs on blocks of records, parallel processing).  The master program also spawns and manages child requests for importing and purging of data.

Other Issues

-          It is also possible that there is data corruption in your interface or core AR tables which would affect the process and the import would never complete. This requires data correction.

-          Incorrectly set database initialization parameters can have a detrimental effect on AutoInvoice performance and performance of the entire EBS application. These include release specific database initialization parameters.

-          Latest application patches not being applied on the instance.

-          High volume of processed / unprocessed data in interface tables, hence there is a need to truncate AutoInvoice interface tables on regular intervals.