PAXEXCPD, Transaction Exception Details by PA Period, Performance Issue (Doc ID 2664476.1)

Last updated on APRIL 15, 2024

Applies to:

Symptoms

There is performance issue for "Transaction Exception Details by PA Period" concurrent program.

SELECT
imp.org_id cst_org_id,
'Costing Exceptions' Cst_Exceptions,
cst.period_name Period_Cst,
cst.exception_reason Exception_Reason_Cst,
cst.CORRECTIVE_ACTION Correction_Action_Cst,
DECODE(cst.vendor_number, NULL, cst.person_name,
cst.vendor_number) Vendor_Employee,
cst.project_number Project_Cst,
cst.task_number Task_Cst,
cst.expenditure_type Expenditure_Type_Cst,
cst.expenditure_item_date EI_Date,
cst.quantity quantity_cst,
substr(cst.expenditure_group,1,20) Group_Name, /* Bug 2544265
,UTF8 change,added substr */
cst.amount Amount_Cst,
cst.denom_currency_code denom_curr_code_cst,
cst.denom_amount denom_amount_cst,
lk.meaning unit_cst,
cst.expenditure_item_id EI_Id_Cst --Bug 17894604 - Grants Award Number ER
.....
.....

WHERE cst.expenditure_type = et.expenditure_type AND NVL (cst.unit_of_measure , et.unit_of_measure ) = lk.lookup_code
AND lk.lookup_type = 'UNIT' AND cst.gl_date BETWEEN : start_date AND : end_date
AND ( ( nvl ( : exception_type , 'COST_EXCP' ) = 'COST_EXCP' AND :p_exception_reason is null )
OR ( cst.exception_reason in ( select pl.exception_reason from pa_exception_reasons pl
where pl.exception_category = 'COST_EXCP' and pl.exception_code = : p_exception_reason )
AND : exception_type = 'COST_EXCP' AND : p_exception_reason is not null ) )
AND : calling_mode = 'GL' AND : pa_new_gl_date = 'Y' AND nvl ( cst.same_pa_gl_period , 'N' ) in ( 'N' , 'Y' )
AND ( ( : across_ous = 'Y' and Exists ( Select 1 From HR_ORG_UNITS_NO_JOIN hr
Where hr.ORGANIZATION_ID = cst.org_id ) and set_of_books_id =
( select set_of_books_id from pa_implementations where org_id = nvl ( org_id , - 99 ) ) )
OR ( : across_ous = 'N' and nvl ( cst.org_id , - 99 ) = : org_id1 ) )
ORDER BY 2 ASC, 1 ASC, 3 ASC, 4 ASC, 5 ASC, 14 ASC ,cst_org_id ,period_cst ,exception_reason_cst ,denom_curr_code_cst

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.