Oracle Bloggers: Little-Known Factors That Could Affect the Performance of AutoInvoice Process

By Oracle Blog's

AutoInvoice is one of those processes whose performance is really impacted by the way you setup the application. A small change in the System Options screen, can be the difference between this process completing in a few seconds or not completing at all.
Arguable, three of the most important settings you need to review and make sure they are correctly set, are the following:

• System option: Log File Message Level
• System option: Max Memory (in bytes)
• Indexes on Line Transaction Flexfield columns

System option: Log File Message Level

Log File Message Level should never be greater than 0 (zero) in a production environment; unless Oracle Support team requests it to troubleshoot a functional issue. In that case, the problem must be reproduced with the minimum amount of data needed.
If you use too much data, you might not be able to reproduce the functional problem because of the performance this option might cause.

System option: Max Memory (in bytes)

This setting will determine the number of rows that AutoInvoice will be able to process in one fetch. The general recommendation is to set it to a value between 3 and 10 MB; however, it highly depends on the capacity of your server. As a minimum, please make sure it is set to 3 MB.
The screen shot below shows the setting of these two system options

Indexes on Line Transaction Flexfield (LTF) Columns

As you might already know, if you are implementing AutoInvoice, you must define the Line Transaction Flexfield and this setup can be different for each source you are using.
Based on these LTFs, you must create custom indexes on the following tables on the AR schema:

• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_INTERFACE_LINES_ALL
• RA_INTERFACE_DISTRIBUTIONS_ALL
• RA_INTERFACE_SALESCREDITS_ALL

The indexes must include the context and attribute columns fort the specific Line Transaction Flexfield. If your flexfield definition uses attributes 2, 5, and 13, the following indexes should be created:

CREATE UNIQUE INDEX AR.XX_RA_CUSTOMER_TRX_U1 ON AR.RA_CUSTOMER_TRX_ALL
(INTERFACE_HEADER_CONTEXT,
 INTERFACE_HEADER_ATTRIBUTE2,
 INTERFACE_HEADER_ATTRIBUTE5,
 INTERFACE_HEADER_ATTRIBUTE13);

CREATE UNIQUE INDEX AR.XX_RA_CUSTOMER_TRX_LINES_U1 ON AR.RA_CUSTOMER_TRX_LINES_ALL
(INTERFACE_LINE_CONTEXT,
 INTERFACE_LINE_ATTRIBUTE2,
 INTERFACE_LINE_ATTRIBUTE5,
 INTERFACE_LINE_ATTRIBUTE13);

CREATE UNIQUE INDEX AR.XX_RA_INTERFACE_LINES_U1 ON AR.RA_INTERFACE_LINES_ALL
(INTERFACE_LINE_CONTEXT,
 INTERFACE_LINE_ATTRIBUTE2,
 INTERFACE_LINE_ATTRIBUTE5,
 INTERFACE_LINE_ATTRIBUTE13);

CREATE UNIQUE INDEX AR.XX_RA_INTERFACE_DIST_U1 ON AR.RA_INTERFACE_DISTRIBUTIONS_ALL
(INTERFACE_LINE_CONTEXT,
 INTERFACE_LINE_ATTRIBUTE2,
 INTERFACE_LINE_ATTRIBUTE5,
 INTERFACE_LINE_ATTRIBUTE13);

CREATE UNIQUE INDEX AR.XX_RA_INT_SALESCRDS_U1 ON AR.RA_INTERFACE_SALESCREDITS_ALL
(INTERFACE_LINE_CONTEXT,
 INTERFACE_LINE_ATTRIBUTE2,
 INTERFACE_LINE_ATTRIBUTE5,
 INTERFACE_LINE_ATTRIBUTE13);

Please note that if you cannot create the indexes above as UNIQUE, due to duplicate keys, you can create them as normal. You can also change the name of the indexes as per your standards.
There might not be statistics on what percentage of all the performance problems with AutoInvoice are solved by setting the above options and indexes correctly, but it could easily be 80%.