X

Review EBS product's new releases & features, troubleshooting tips & tricks, best practices, upcoming webcasts

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

Bernardo Campesino
Principal Technical Support Engineer

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%.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.