R12.1.3 Performance in APOPITRN module: Open Items Revaluation Report (XML) - apopitrb.pls

(Doc ID 2358512.1)

Last updated on FEBRUARY 07, 2018

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Performance problem with Open Items Revaluation Report (XML)

 

Applied the following actions which did not resolve the issue:

1. Apply Patch:24525558:R12.AP.B to deliver apopitrb.pls 120.1.12010000.19

2. Download test file newdebug_apopitrb.pls and compile
a. Create a backup of $AP_TOP/patch/115/sql/apopitrb.pls
b. Download newdebug_apopitrb.pls from the RFC.
c. Create a backup of $AP_TOP/patch/115/sql/apopitrb.pls
d. Rename newdebug_apopitrb.pls to apopitrb.pls and copy to
$AP_TOP/patch/115/sql
e. Compile the package using SQLPlus
"Alter package AP_OPEN_ITEMS_REVAL_PKG compile"
"Alter package AP_OPEN_ITEMS_REVAL_PKG compile body"
f. Confirm successful compilation.

3. Create index and gather stats

a. create index xla.XLA_AE_LINES_IX1 on
xla.xla_ae_lines(APPLICATION_ID,AE_HEADER_ID,accounting_class_code)
local;
b. sqlplus apps/pwd
exec fnd_stats.gather_table_stats ('XLA','XLA_AE_LINES',
percent=>99,degree=>NULL,cascade=>TRUE);

4. Gather stats with below code

begin
for i in ( select owner,table_name from dba_tables where table_name
in('AP_CHECKS_ALL',
'AP_INVOICES_ALL',
'AP_INVOICE_DISTRIBUTIONS_ALL',
'AP_INVOICE_PAYMENTS_ALL',
'AP_SUPPLIERS',
'AP_SUPPLIER_SITES_ALL',
'AP_SYSTEM_PARAMETERS_ALL',
'CST_COGS_EVENTS',
'CST_XLA_INV_EVENT_MAP',
'FND_CONCURRENT_REQUESTS',
'FND_LOOKUP_VALUES',
'GL_CODE_COMBINATIONS',
'HR_ALL_ORGANIZATION_UNITS',
'HR_ALL_ORGANIZATION_UNITS_TL',
'HR_ORGANIZATION_INFORMATION',
'HZ_PARTIES',
'MO_GLOB_ORG_ACCESS_TMP',
'MTL_MATERIAL_TRANSACTIONS',
'MTL_PARAMETERS',
'MTL_TRANSACTION_ACCOUNTS',
'PJM_ORG_PARAMETERS',
'RCV_ACCOUNTING_EVENTS',
'RCV_TRANSACTIONS',
'WIP_COST_TXN_INTERFACE',
'WIP_TRANSACTIONS',
'WIP_TRANSACTION_ACCOUNTS',
'XLA_AE_HEADERS',
'XLA_AE_LINES',
'XLA_DISTRIBUTION_LINKS',
'XLA_TRANSACTION_ENTITIES'))
loop
FND_STATS.GATHER_TABLE_STATS(i.owner,i.table_name,DBMS_STATS.AUTO_SAMPLE_SIZE)

;
end loop;
end;
/

5. Update OPP timeout options

a. Log in as System Administrator > Profile > System
At the Site level:
Set profile option "Concurrent:OPP Response Timeout" to 10800 seconds
Set profile option "Concurrent:OPP Processing Timeout" to 8 hours (28800
seconds)
Save

6. Update BI Publisher/XDO settings
A. Make sure that the scalability option is set:

As System Administrator: Navigate to Concurrent->Program->Define
Query up the report: APOPITRN: Open Items Revaluation Report (XML)
Add a parameter named 'ScalableFlag'. Token needs to be 'ScalableFlag' (this
is a case sensitive value).
Value Set: yes_no
Default Type - Constant
Default Value: Y
Select checkbox 'Enable'
UNCHECK the check box 'Displayed' if it is currently checked.

B. Configure the XML Publisher Administrator Configuration settings:

As XML Publisher Administrator navigate to Administration->Configuration.
Under Temporary Directory pick a temporary file location on your concurrent
processing node. This should be at least 5GB or 20x larger than largest XML
data file you generate
To improve performance, make sure the tmp directory used by BI Publisher is
located in a local hard disk, not a remote or networked hard drive.
This is because tmp file I/O performance is crucial to generating the report
quickly. Please consult your DBA on this issue.

Under FO Processing, set:
Use XML Publisher's XSLT processor set to True
Enable scalable feature of XSLT processor set to True
Enable XSLT runtime optimization set to True

C. Configure the Output Post Processor's JVM for maximum memory:

Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from
FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

D. Use PDF output format instead of RTF output:

Go to System Administrator Responsibility
Profile > System
Query for the option 'FND: Default Template Output Type' and select "PDF" as
LOV (at Site and User level).
Save.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms