Introduction

This document describes the setups and processing steps to improve performance for Autoinvoice, Create Accounting, Auto lockbox and advanced collections.

Auto Invoice:

1. Certain executables should be incompatible with Auto Invoice so as not to interfere with performance as well as data integrity. The following executables are seeded as incompatible with Auto Invoice, confirm the following:Incompatibilities with RAXMTR:Responsibility: System Administrator
   Navigation: Concurrent Program > Define
   Query Up Short Name = RAXMTRProgram: Auto invoice Master Program
   Short Name: RAXMTR
   Application: Receivables

Query up Short Name = RAXTRX
Program: Auto invoice Import Program
Short Name: RAXTRX
Application: Receivables

Click on Incompatibilities button, and setup the below concurrent programs

Program: Autoinvoice Master Program
Short Name: RAXMTR

Application                         Name                                     Scope   Type
———————————-  —————————————  ——- ——
Receivables                         Archive Restart                          Set     Global
Receivables                         Purge                                    Set     Global
Receivables                         Archive and Purge                        Set     Global
Receivables                         Archive Preview                          Set     Global
Receivables                         Refresh AR Transactions Summary Table    Set     Global
Projects                            PRC: Tieback Invoices from Receivables   Program Domain
Inventory                           Create Intercompany AR Invoices          Program Global
Financials Common Modules           Reverse Netting Batch                    Set     Domain
Financials Common Modules           Settle Netting Batch                     Set     Domain
Financials Common Modules           Submit Netting Batch                     Set     Domain
Financials Common Modules           Create Netting Batch                     Set     Domain
Process Manufacturing Financials    AR Update                                Set     Global
Asia/Pacific Localizations          India – Local Concurrent for Processing  Set     Domain
Order Lines to AR

Program: Autoinvoice Import Program
Short Name: RAXTRX

Application                         Name                                     Scope   Type
———————————-  —————————————  ——- ——
Receivables                         Archive Restart                          Set     Global
Receivables                         Purge                                    Set     Global
Receivables                         Archive and Purge                        Set     Global
Receivables                         Archive Preview                          Set     Global
Receivables                         Refresh AR Transactions Summary Table    Set     Global
Projects                            PRC: Tieback Invoices from Receivables   Program Domain
Projects                            PRC: Interface Invoices to Receivables   Program Domain
Projects                            PRC: Interface Intercompany Invoices to  Program Domain
Receivables
Process Manufacturing Financials    AR Update                                Set     Global
Order Management                    Initialize Credit Summaries Table        Program Global
Collections                         IEX: Populate UWQ Summary Table          Program Global
Asia/Pacific Localizations          India – AR Tax and Freight Defaultation  Set     Domain
Grants Accounting                   GMS: Interface Invoices to Receivables   Program Domain

2.Incorrectly set database initialization parameters can have a detrimental effect on Auto Invoice performance and performance of the entire EBS application.

The Value in the below initial parameters can be wrongly set.

Run the script bde_chk_cbo.sql from metalink which will compare the initialization parameters with the required settings.

3. Accurate Cost Based Optimizer (CBO) statistics are essential for optimal performance. Note 163208.1, bde_last_analyzed.sql – Verifies CBO Statistics, can be run to verify the accuracy of statistics. Also, you can manually generate statistics on the following tables.

sql> execute fnd_stats.gather_table_stats(‘AR’,’RA_CUSTOMER_TRX_ALL’);
sql> execute fnd_stats.gather_table_stats(‘AR’,’RA_CUSTOMER_TRX_LINES_ALL’);
sql> execute fnd_stats.gather_table_stats(‘AR’,’RA_INTERFACE_LINES_ALL’);
sql> execute fnd_stats.gather_table_stats(‘AR’,’RA_INTERFACE_DISTRIBUTIONS_ALL’);
sql> execute fnd_stats.gather_table_stats(‘AR’,’RA_INTERFACE_SALESCREDITS_ALL’);

1. Auto Invoice interface tables may occasionally need to be truncated in order to reset the high water mark on these tables.

Use the following commands to truncate the interface tables.

sql> connect ar/ar
sql> truncate table RA_INTERFACE_LINES_ALL;
sql> truncate table RA_INTERFACE_DISTRIBUTIONS_ALL;
sql> truncate table RA_INTERFACE_SALESCREDITS_ALL;
sql> truncate table RA_INTERFACE_ERRORS_ALL;

 

2. The purpose of the Line Transaction Flexfield is to uniquely identify transaction lines. Therefore, the indexes should be created as UNIQUE indexes. However, Auto Invoice does not validate or require a unique LTF. If you receive errors stating that the indexes cannot be created because of duplicate keys, then you should create non-unique indexes by removing the UNIQUE constraint from the CREATE INDEX statement. Unique indexes will provide better performance results.

 

3. Consider using NOLOGGING for these indexes if ARCHIVING is enabled on your database. NOLOGGING should reduce the amount of time to create the index and will save space in the redo log files. Indexes created using NOLOGGING are not archived. Therefore, you should perform a backup after you create the indexes. Your DBA should review and advise whether NOLOGGING is appropriate for your environment.

Performance issues are usually caused by multiple problems. The root cause can be product related, issues can also be due to hardware constraints, network problems, set up issues or other factors. Properly completing the setup steps shown in this document can help rule out improper or incomplete setup as a cause for any performance problem you may encounter.

Manage Receivables System Options

The settings in System Options that impact Auto Invoice are under the Trans and Customers tab.

1. Set Purge Interface Table = Y

The interface tables are not meant to store data beyond processing transactions in Auto Invoice. This setting only purges data that has been processed.  Data resulting in error can still be corrected and re-processed.

1. Set Max Memory (in bytes) = Suggested 3MB (3145728 bytes)

This is the maximum amount of memory used for Auto Invoice validation.

1. Log File Message Level = 0

Setting the log file message level to 0 provides the least detailed debug messages but still includes errors and warning messages.  After you have completed the setup of Auto Invoice, you should need only minimal messages in the log file.

1. Accounting Flex field

The Oracle Receivables Auto Invoice Setup Diagnostic Test will show your Accounting Flex field.  You should use a segment that has already been indexed in the GL_CODE_COMBINATIONS table when your Accounting Flex field was created.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment.

The indexes on GL_CODE_COMBINATIONS are also included in the Auto Invoice Setup Diagnostic Test.  Typically, the Account Segment will contain the most distinct values of the flex field and should therefore be used as the tuning segment.  Alternatively, you can choose a segment in your Accounting Flex field which contains the most distinct values. You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM GL_CODE_COMBINATIONS;

1. System Items

The Oracle Receivables Auto Invoice Setup Diagnostic Test shows indexes on MTL_SYSTEM_ITEMS_B.  You should use a segment that has already been indexed in the MTL_SYSTEM_ITEMS_B table.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment.

Typically, the Item Segment will contain the most distinct values of the flex field and should therefore be used as the tuning segment.  Alternatively, you can choose a segment in your System Items Flex field which contains the most distinct values. You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM MTL_SYSTEM_ITEMS_B;

1. Territory

The Oracle Receivables Auto Invoice Setup Diagnostic Test shows indexes on RA_TERRITORIES.  You should use a segment that has already been indexed in the RA_TERRITORIES table.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment.

Typically, the Country Segment will contain the most distinct values of the flex field and should therefore be used as the tuning segment.  Alternatively, you can choose a segment which contains the most distinct values.  You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM RA_TERRITORIES;
Responsibility:  Receivables Manager
Navigation:  Setup > System > System Options > Trans and Customers Tab

Manage System Profile Options

1. AR:  Auto Invoice Gather Statistics

The recommended setting is Y (Yes) at the Site level.  Setting this profile will gather statistics on the interface and appropriate application tables before running Auto Invoice.

Responsibility: System Administrator
Navigation: Profile > System
Query: AR: Auto Invoice Gather Statistics

2.AR: Maximum Lines Per Auto Invoice Worker
This profile option should be set if you are running Auto Invoice Master with multiple workers and your transactions typically have a large number of lines. Setting this profile option will help Auto Invoice determine the maximum number of lines to assign to an Auto Invoice worker.

For example, if you typically process invoices with approximately 10,000 lines per invoice, then set the value = 10,000. If you are unsure, or if the number of lines per transaction are low or varied, you should not enter a value for this profile option.

Responsibility: System Administrator
Navigation: Profile > System
Query: AR: Maximum lines per Auto Invoice worker

For additional information, please review Note 1151373.1, How to set “AR: Maximum Lines per Auto Invoice Worker” profile option.

1. MO: Operating Unit

This profile option should be set up correctly at the responsibility level. It should point to the Default Operating Unit associated to the Responsibility you are using to run Auto Invoice.

Responsibility: System Administrator
Navigation: Profile > System
Query: MO: Operating Based on Your Line Transaction Flex field (LTF)

This example assumes that you are using a context that has 3 segments of the flex field enabled.  You will need to modify the queries below to include ALL enabled segments of your LTF.

You should create the indexes using SQLPlus as follows:

SQL> CONNECT AR/AR

SQL> CREATE UNIQUE INDEX XX_RA_CUSTOMER_TRX_U1 ON RA_CUSTOMER_TRX_ALL
(INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_CUSTOMER_TRX_LINES_U1 ON RA_CUSTOMER_TRX_LINES_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_INTERFACE_LINES_U1 ON RA_INTERFACE_LINES_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);
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, or if you have set up the application to derive these values, then you do not need to create these indexes.

SQL> CREATE UNIQUE INDEX XX_RA_INTERFACE_DIST_U1 ON RA_INTERFACE_DISTRIBUTIONS_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_INT_SALESCRDS_U1 ON RA_INTERFACE_SALESCREDITS_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

 

Ensure all other required indexes are setup as per Oracle Receivables User’s Guide

Create Accounting:

1. DB level setup

The following parameters were set to True at the DB level.

• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
• OPTIMIZER_USE_SQL_PLAN_BASELINES

Please set the below parameters in init.ora to False:

• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
• OPTIMIZER_USE_SQL_PLAN_BASELINES=false

Once done, Run the Create accounting program.

2. Navigation for No.Of.Processors and unit size

Set up > Financials > General Ledger > Accounting Setup Manager > Accounting Setups

Query the ledger > Click the Update Accounting Options.

Click Update on the Subledger Accounting Options > Update system options for required application.

 

Considering three operating units Canada, Germany and Japan

 

Ledger	SLA	No. of Processors	Processor Unit Size	Total No. of records processed
Canada	Standard Accrual	12	1000	12K
Germany	Standard Accrual	6	1000	6K
Japan	Standard Accrual	6	1000	6K
USA	Standard Accrual	12	5000	12K

 

 

3. select count(*) from XLA_DIAG_SOURCES.Based on this value, it might be necessary to run the concurrent request
   “Purge Transaction Objects Diagnostics”, if the customer does not need
   diagnostic data of previous Create Accounting runs.The program “Purge Transaction Object Diagnostics” is done per Subledger Application.

 

 

 

Advanced Collections

1. IEX: Scoring Engine Harness concurrent process and opening the Scoring Engine forms, you will need to make sure you run the following concurrent processes to clear the iex_score_histories and iex_del_buffers tables.IEX: Purge Score History Table
   IEX: Clear Delinquency Buffers TableThese tables need to be kept to a minimum in order to keep performance of the IEX: Scoring Engine Harness from running to long.
2. Application profile settings that optimize the collector work queue performance

Set the following profile option

IEX: Enable Work Queue Statuses (Yes or No) to No.  This will hide the sub-nodes (Active, Complete and Pending) most users don’t utilize.
IEU: Desktop: UI: Automatic Refresh Type (Select Work or Get Work) set to Get Work
IEU: Desktop: UI: Refresh Style (Automatic, Login, Manual and None) set to Login. This refreshes only when the collector logs into the system.
IEX: Batch size (default 1000). We have found 100000 is a good balance between space consumption and performance.  The higher you set this value, more temporary table space and memory will be required.
IEX:  Show Last Payment Due on Field (default Yes).  If set to No, it hides the last payment due on date field on the collections form and does not call the query therefore improving performance.

3.   Confirm that the Collections Debug is off.  Make sure the profile IEX: Debug Level is set to 20. Anything lower means that debug is on.
4. IEX Strategy Management Program runs for a long time. If user is not using the automated switch strategy functionality run the concurrent program with the parameter, Ignore strategy switch set to Yes.  The program will perform faster because it will not check each strategy for the need to switch.

Auto Lockbox

1. For issues in the Validation or Post Quick Cash steps you can run a concurrent process sql trace.

1. To Enable Level 12 Trace:
   Responsibility: System Administrator
   Navigation: Profile > System
   • Find the profile option ‘Initialization SQL Statement – Custom’ for User who is submitting the process to trace.
   • User: User submitting the process
   • Profile: Initialization SQL Statement – Custom
   • Click on User column – Edit Field and enter
     begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=’||””||’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’||””); end;
   • Save.
2. Run the process to reproduce the issue.
   Note: Avoid any extra steps as we want to trace just the process that is failing.
3. Disable Trace by resetting the above profiles.
4. To pull the trace:

Run the following query, this gives the directory where the trace file is stored.
SELECT name, value
FROM v$parameterÇ
WHERE name = ‘user_dump_dest’;

Go to the directory returned by USER_DUMP_DEST

1. Run each file through the TKProf program, to provide an interpreted file output.

1. Archive/Purge programs can be executed to purge the processed data.
   Please ensure the following:

1. Submit Lockbox Validation Processing and ensure it runs without errors before running the Archive/Purge programs.
2. Before data is purged from AR_PAYMENTS_INTERFACE, run Post Quick Cash before the Archive/Purge programs. Before data is purged from
   • AR_INTERIM_CASH_RECEIPTS
   • AR_INTERIM_CASH_RECEIPT_LINES