Auto Invoice Performance Improvement

Setup Considerations for Performance:
===============================
System Options Setup
-Log File Message Level-Message Level 0 shows the following in the log file:

• Product Name & Version
• AutoInvoice Start Time, End Time, & Logical Steps
• AutoInvoice Concurrent Request Arguments
• Error and Warning Messages

-Message Level 1 shows time stamped function labels plus all of the above.
-Message Level 2 shows above entries plus:

• Sizes of Allocated Arrays ,Dynamic SQL Statements , Number of Rows Updated, Inserted and Deleted

-Message Level 3 shows above plus Method IV SQL Array Values

Scenarios: You have number of transactions in the AR Interface table which are ready to get invoiced for the source order Management, you have run the auto invoice program and the process is taking long time to complete e.g more then 6 hrs for 3000 lines . What do you do and how do you improve the performance of the Auto invoice program.

To increase performance during the validation portion of AutoInvoice:

NOTE: You may want to back up this table before truncating if you need to review prior errors. Errors will be re-submitted into this table from any unprocessed invoice lines

1. Ensure that all indexes for the transaction flexfield are set up i.e indexes should be created on Transaction Flexfield columns.


2. Unique, concatenated indexes should be created on the following tables and columns:

• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_INTERFACE_LINES_ALL
• RA_INTERFACE_DISTRIBUTIONS_ALL
• RA_INTERFACE_SALESCREDITS_ALL
• OE_ORDER_LINES_ALL

1.Truncate the table RA_INTERFACE_ERRORS_ALL.

sql> connect AR/AR

sql> truncate table RA_INTERFACE_ERRORS_ALL;

2. If your Line Transaction Flexfield is ORDER ENTRY. You need to create indexes for ALL columns used in the Line Transaction Flexfield.

First, drop existing custom indexes if you have any:

sql> drop index XXX_RA_CUSTOMER_TRX_LINES_A1;

sql> drop index XXX_RA_INTERFACE_LINES_A2;

Second, create the following indexes:

NOTE:You can use CREATE INDEX instead of CREATE UNIQUEINDEX if necessary.

SQL> CONNECT AR/AR

SQL> CREATE UNIQUE INDEX XX_RA_CUST_TRX_U1 ON RA_CUSTOMER_TRX_ALL (INTERFACE_HEADER_CONTEXT, INTERFACE_HEADER_ATTRIBUTE1, INTERFACE_HEADER_ATTRIBUTE2, INTERFACE_HEADER_ATTRIBUTE3, INTERFACE_HEADER_ATTRIBUTE4, INTERFACE_HEADER_ATTRIBUTE5, INTERFACE_HEADER_ATTRIBUTE6, INTERFACE_HEADER_ATTRIBUTE7, INTERFACE_HEADER_ATTRIBUTE8, INTERFACE_HEADER_ATTRIBUTE9, INTERFACE_HEADER_ATTRIBUTE10, INTERFACE_HEADER_ATTRIBUTE11, INTERFACE_HEADER_ATTRIBUTE12, INTERFACE_HEADER_ATTRIBUTE13, INTERFACE_HEADER_ATTRIBUTE14, INTERFACE_HEADER_ATTRIBUTE15);

SQL> CREATE UNIQUE INDEX XXX_RA_CUST_TRX_LINES_U1 ON RA_CUSTOMER_TRX_LINES_ALL (INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8, INTERFACE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11, INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14, INTERFACE_LINE_ATTRIBUTE15);

SQL> CREATE UNIQUE INDEX XXX_RA_INT_LINES_U1 ON RA_INTERFACE_LINES_ALL (INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8, INTERFACE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11, INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14, INTERFACE_LINE_ATTRIBUTE15);

SQL> CREATE UNIQUE INDEX XXX_RA_INT_DIST_U1 ON RA_INTERFACE_DISTRIBUTIONS_ALL (INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8, INTERFACE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11, INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14, INTERFACE_LINE_ATTRIBUTE15);

SQL> CREATE UNIQUE INDEX XXX_RA_INT_SALESCREDITS_U1 ON RA_INTERFACE_SALESCREDITS_ALL (INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8, INTERFACE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11, INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14, INTERFACE_LINE_ATTRIBUTE15);

Note: You only need to create the following indexes, if these tables are being populated with interface data. If you are not using sales credits or accounting distributions, then you do not need to create these indexes

SQL > create index ONT.OE_ORDER_LINES_A1 on ONT.OE_ORDER_LINES_ALL (to_char(line_id)) tablespace APPS_TS_TX_IDX;

3. Set the Log File Message Level = 1 Navigate to Setup > System > System Options and set this value = 1.

4. Set the Profile option AR:Maximum lines per AutoInvoice worker : 1000 (At Site level). Please set this to a value that approximates an even distribution of invoices lines per worker. For example if each invoice has approximately 800 to 1000+ lines and you process 5 invoices per Autoinvoice run (approximately 5000 invoice lines) you could set the value of this profile to 1000 which should distribute 1 invoice (800 to 1000+ lines) per worker, if you run with 5 workers. If you are unsure of the distribution, do NOT set this profile.

Note:

1. AutoInvoice inserts data into the tables, but never changes the data that is fed into the tables.
2. Review the RA_INTERFACE_ERRORS table for details on what is failing during import. Use this information to quickly search for known issues.
3. Review the Invalid Objects and Disabled Trigger Section to confirm correct setup.
4. Run Gather Schema Statistics from the SYSADMIN responsibility

5. If you do NOT see a performance gain after making these changes, Please raise an SR with Oracle Support and upload the log files and execution report from a run of Autoinvoice and the Auto invoice diagnostics script.

6. SQL Query to check the time taken to complete by Autoinvoice import prgram.

SELECT request_id, TO_CHAR(request_date,'DD-MON-YYYY') "Date", RPAD(user_concurrent_program_name,60) "Program Name", RPAD(argument_text,20) "Arg", RPAD(TO_CHAR(a.ACTUAL_START_DATE, 'hh24:mi:ss'),10) "Start", RPAD(TO_CHAR(a.ACTUAL_COMPLETION_DATE, 'hh24:mi:ss'),10) "End", RPAD(TO_CHAR( (TRUNC(SYSDATE) + ( a.ACTUAL_COMPLETION_DATE - a.ACTUAL_START_DATE)), 'hh24:mi:ss'),10) "Actual", c.user_name, status_code "S"FROM applsys.fnd_concurrent_requests a, applsys.fnd_concurrent_programs_tl b, apps.fnd_user cWHERE b.user_concurrent_program_name like 'Autoinvoice Import Program' AND a.concurrent_program_id = b.concurrent_program_id AND a.request_date >= 'DD-MON-YYYY' AND a.requested_by = c.user_idORDER BY request_id desc;

Note : 197213.1-Autoinvoice Performance Is Slow. Note:169935.1 - Troubleshooting Oracle ERP Apps Performance Issues.

Source: My OracleSupport