My Oracle Support Banner

R12 AP: Account Analysis Report (XLAAARPT) Performance Issues (Doc ID 834775.1)

Last updated on JANUARY 16, 2018

Applies to:

Oracle Payables - Version 12.0.6 and later
Information in this document applies to any platform.
This problem can occur on any platform.
***Checked for relevance on 25-Dec-2015***


Symptoms

When running the Account Analysis Report from the Payables responsibility,
it is running for a very long time even with narrow parameters.
The process does not complete.

The following SQL statement is taking most of the time:

 

SELECT
TABLE1.GL_DATE GL_DATE
,TABLE1.CREATED_BY CREATED_BY
,TABLE1.CREATION_DATE CREATION_DATE
,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE
,TABLE1.GL_TRANSFER_DATE GL_TRANSFER_DATE
,TABLE1.REFERENCE_DATE REFERENCE_DATE

[....]
[....]

,gl_je_batches gjb
,xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date
AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND
:P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND
decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) =
glbgt.ledger_currency --added bug 6686541 [WebIV]
AND NVL(gjh.je_from_sla_flag,'N') = 'U'
AND fnm.application_id = 101
AND fnm.language_code = USERENV('LANG')
AND fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222',
'PPOS0243', 'PPOS0222_G','PPOSO275')
AND gjl.description= fnm.message_text
AND NVL(gjh.budget_version_id,-19999) =
NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999) =
NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND not exists (select 'x' from gl_import_references gir where
gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num)
AND (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) <> 0)

)TABLE1
ORDER BY
TABLE1.LEDGER_NAME
,TABLE1.LEDGER_CURRENCY
,TABLE1.CODE_COMBINATION_ID
,TABLE1.PERIOD_YEAR
,TABLE1.PERIOD_NUMBER
,TABLE1.BALANCE_TYPE_CODE
,TABLE1.BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE
,TABLE1.JE_SOURCE_NAME
,TABLE1.HEADER_ID

 

 

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.