PAXAUGLT AUD: Cost Audit Report Performance Issues
(Doc ID 2759079.1)
Last updated on MARCH 04, 2024
Applies to:
Oracle Project Costing - Version 12.2.9 and laterInformation in this document applies to any platform.
Symptoms
Performance issues with AUD: Cost Audit Report.
Navigate: Other -> Requests -> Run
Select and run AUD: Cost Audit Report
Report is taking over six hours to complete successfully.
Other times the report completes in error - 'snapshot too old'.
Problem SQL:
SELECT *
FROM
( SELECT /*+ push_pred(cst) use_nl(cc,cst) */ cst.code_combination_id ,
project_number , task_number , SUBSTR ( expenditure_type , 1 , 22 )
expenditure_type , expenditure_item_date , SUBSTR ( emp_or_org_name , 1 ,
32 ) emp_or_org_name , period_name , transferred_date , gl_dr_amount ,
gl_cr_amount , cr_code_combination_id FROM PA_GL_COST_XFER_AUDIT_V cst
WHERE : cp_gl_start is NULL AND TRUNC ( transferred_date ) BETWEEN nvl (
TRUNC ( : cf_from_date ) , TRUNC ( transferred_date ) ) AND nvl ( TRUNC ( :
cf_to_date ) , TRUNC ( transferred_date ) ) AND EXISTS ( SELECT 1 FROM
gl_code_combinations CC WHERE CC.SEGMENT1 BETWEEN '0000000000' AND
'ZZZZZZZZZZ' AND CC.SEGMENT2 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT3
BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT4 BETWEEN '0000000000'
AND 'ZZZZZZZZZZ' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ' AND
CC.SEGMENT6 = '61003600' AND CC.SEGMENT7 BETWEEN '0000000000' AND
'ZZZZZZZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND
CC.SEGMENT9 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT10 BETWEEN
'0000000000' AND 'ZZZZZZZZZZ' and CC.code_combination_id =
cst.code_combination_id ) UNION ALL SELECT /*+ push_pred(cst) use_nl(cc,cst)
*/ cst.code_combination_id , project_number , task_number , SUBSTR (
expenditure_type , 1 , 22 ) expenditure_type , expenditure_item_date ,
SUBSTR ( emp_or_org_name , 1 , 32 ) emp_or_org_name , period_name ,
transferred_date , gl_dr_amount , gl_cr_amount , cr_code_combination_id
FROM PA_GL_COST_XFER_AUDIT_V cst WHERE : cp_gl_start is NOT NULL AND
gl_date BETWEEN : cp_gl_start AND : cp_gl_end AND TRUNC ( transferred_date )
BETWEEN nvl ( TRUNC ( : cf_from_date ) , TRUNC ( transferred_date ) ) AND
nvl ( TRUNC ( : cf_to_date ) , TRUNC ( transferred_date ) ) AND EXISTS (
SELECT 1 FROM gl_code_combinations CC WHERE CC.SEGMENT1 BETWEEN
'0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT2 BETWEEN '0000' AND 'ZZZZ' AND
CC.SEGMENT3 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT4 BETWEEN
'0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ'
AND CC.SEGMENT6 = '61003600' AND CC.SEGMENT7 BETWEEN '0000000000' AND
'ZZZZZZZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND
CC.SEGMENT9 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT10 BETWEEN
'0000000000' AND 'ZZZZZZZZZZ' and CC.code_combination_id =
cst.code_combination_id ) ) ORDER BY 1 ASC , code_combination_id ,
project_number , task_number , expenditure_type , expenditure_item_date
Changes
Upgraded form 12.2.6 to 12.2.9.
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 |
Changes |
Cause |
Solution |
References |