My Oracle Support Banner

Poor Performance Of AUD: Project Subledger Summary Report After Patch 24741496:R12.PJC.C (Doc ID 2475834.1)

Last updated on FEBRUARY 14, 2019

Applies to:

Oracle Project Costing - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5 version, Accounting

fter applying the patch 24741496:R12.PJC.C - The 'AUD: Project Subledger Summary report' it is taking a long time to complete.

File version after the patch applied. (Resolving the numbers in the report but taking days to complete running the report)
PAXMGSLS.rdf 120.5.12020000.10

File version after the patch is reverted . Took just 10 mins to run.
PAXMGSLS.rdf 120.5.12020000.3

The following statement is the cause of the poor performance:

SELECT /*+ no_expand push_pred(trans) */ trans . code_combination_id code_combination_id , gl_account_description , prj . segment1 project_number , prj . NAME project_description , trans . manufacturing_related , trans . system_linkage , SUM ( DECODE ( NVL ( trans . burdened_cost , 0 ) , 0 , trans . raw_cost , trans . burdened_cost ) ) total_cost , SUM ( trans . raw_cost ) raw_cost , SUM ( NVL ( NVL ( trans . burdened_cost , 0 ) , trans . raw_cost ) - trans . raw_cost ) burden_cost FROM pa_projects_all prj , pa_tasks task , ( SELECT (  CC.SEGMENT1||'
'||CC.SEGMENT2||'
'||CC.SEGMENT3||'
'||CC.SEGMENT4||'
'||CC.SEGMENT5||'
'||CC.SEGMENT6||'
'||CC.SEGMENT7||'
'||CC.SEGMENT8 ) gl_account_description , SYS . project_manufacturing_flag manufacturing_related , SYS . meaning system_linkage , cc . description , cdl . dr_code_combination_id code_combination_id , ei . system_linkage_function system_linkage_function , ei . task_id task_id , ei . expenditure_type expenditure_type , cdl . acct_raw_cost raw_cost , cdl . acct_burdened_cost burdened_cost , ei . expenditure_item_id expen_item_id , cdl . line_num FROM pa_expenditure_items_all ei , pa_cost_distribution_lines_all cdl , pa_transaction_sources ts , pa_implementations imp , pa_expenditure_types et , gl_code_combinations cc , pa_system_linkages SYS WHERE  CC.SEGMENT1 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT2 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT3 = '154559' AND CC.SEGMENT4 = '16882' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ' AND CC.SEGMENT6 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT7 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' and cdl . expenditure_item_id = ei . expenditure_item_id AND cdl . line_type = 'R' AND cdl . transfer_status_code || '' IN ( 'A' , 'V' ) AND cdl . acct_event_id IS NULL AND ( cdl . org_id = imp . org_id OR imp . org_id IS NULL ) AND imp . set_of_books_id = : p_ca_set_of_books_id AND ( cdl . gl_date ) BETWEEN ( : cf_from_date ) AND ( : cf_to_date ) AND NVL ( ei . historical_flag , 'Y' ) = DECODE ( NVL ( ts . predefined_flag , 'Y' ) , 'N' , NVL ( ei . historical_flag , 'Y' ) , 'Y' ) AND ei . transaction_source = ts . transaction_source (+) AND ei . expenditure_type = et . expenditure_type AND cc . code_combination_id = cdl . dr_code_combination_id AND ei . system_linkage_function = SYS . FUNCTION UNION SELECT /*+ merge no_expand  */ (  CC.SEGMENT1||'
'||CC.SEGMENT2||'
'||CC.SEGMENT3||'
'||CC.SEGMENT4||'
'||CC.SEGMENT5||'
'||CC.SEGMENT6||'
'||CC.SEGMENT7||'
'||CC.SEGMENT8 ) gl_account_description , SYS . project_manufacturing_flag manufacturing_related , SYS . meaning system_linkage , cc . description , ael . code_combination_id code_combination_id , ei . system_linkage_function system_linkage_function , ei . task_id task_id , ei . expenditure_type expenditure_type , cdl . acct_raw_cost raw_cost , cdl . acct_burdened_cost burdened_cost , ei . expenditure_item_id expen_item_id , cdl . line_num FROM pa_expenditure_items_all ei , pa_cost_distribution_lines_all cdl , pa_implementations imp , pa_transaction_sources ts , xla_distribution_links xdl , xla_ae_headers aeh , xla_ae_lines ael , xla_acct_class_assgns xaca , xla_assignment_defns_b xad , xla_post_acct_progs_b xpap , gl_ledgers gl , pa_expenditure_types et , gl_code_combinations cc , pa_system_linkages SYS WHERE  CC.SEGMENT1 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT2 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT3 = '154559' AND CC.SEGMENT4 = '16882' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ' AND CC.SEGMENT6 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT7 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' and cdl . expenditure_item_id = ei . expenditure_item_id AND ( ( NVL ( ei . historical_flag , 'Y' ) = 'N' ) OR ( cdl . acct_event_id IS NOT NULL ) ) AND ts . transaction_source (+) = ei . transaction_source AND cdl . line_type = 'R' AND cdl . transfer_status_code || '' IN ( 'A' , 'V' ) AND ( cdl . org_id = imp . org_id OR imp . org_id IS NULL ) AND ( cdl . gl_date ) BETWEEN ( : cf_from_date ) AND ( : cf_to_date ) AND imp . set_of_books_id = : p_ca_set_of_books_id AND xdl . application_id = DECODE ( cdl . transfer_status_code , 'V' , DECODE ( ts . acct_source_code , 'AP_INV' , 200 , 'AP_PAY' , 200 , 'AP_APP' , 200 , 'INV' , 707 , 'WIP' , 707 , 'RCV' , 707 , 0 ) , 275 ) AND xdl . source_distribution_id_num_1 = DECODE ( cdl . transfer_status_code , 'V' , DECODE ( ts . acct_source_code , 'AP_INV' , DECODE ( ei . document_payment_id , NULL , ei . document_distribution_id , cdl . system_reference5 ) , 'AP_PAY' , cdl . system_reference5 , 'AP_APP' , cdl . system_reference5 , 'INV' , cdl . system_reference5 , 'WIP' , cdl . system_reference5 , 'RCV' , cdl . system_reference5 ) , ei . expenditure_item_id ) AND NVL ( xdl . source_distribution_id_num_2 , - 99 ) = DECODE ( cdl . transfer_status_code , 'V' , NVL ( xdl . source_distribution_id_num_2 , - 99 ) , cdl . line_num ) AND xdl . source_distribution_type = DECODE ( cdl . transfer_status_code , 'V' , DECODE ( ts . acct_source_code , 'AP_PAY' , 'AP_PMT_DIST' , 'AP_INV' , DECODE ( ei . document_payment_id , NULL , 'AP_INV_DIST' , 'AP_PMT_DIST' ) , 'AP_APP' , 'AP_PREPAY' , 'RCV' , 'RCV_RECEIVING_SUB_LEDGER' , 'INV' , 'MTL_TRANSACTION_ACCOUNTS' , 'WIP' , 'WIP_TRANSACTION_ACCOUNTS' ) , cdl . line_type ) AND xdl . ae_header_id = aeh . ae_header_id AND xdl . ae_line_num = ael . ae_line_num AND xdl . ae_header_id = ael . ae_header_id AND aeh . application_id = ael . application_id AND ael . application_id = xdl . application_id AND aeh . balance_type_code = 'A' AND aeh . accounting_entry_status_code = 'F' AND aeh . ledger_id = imp . set_of_books_id AND ael . accounting_class_code = xaca . accounting_class_code AND xaca . program_code = xad . program_code AND xaca . program_owner_code = xad . program_owner_code AND xad . program_code = xpap . program_code AND xpap . program_owner_code = 'S' AND xaca . assignment_code = xad . assignment_code AND xaca . assignment_owner_code = xad . assignment_owner_code AND ( xad . ledger_id IS NULL OR xad . ledger_id = imp . set_of_books_id ) AND xad . enabled_flag = 'Y' AND xpap . program_code = 'PA_POSTACCOUNTING_DEBIT' AND xpap . application_id = 275 AND gl . ledger_id = imp . set_of_books_id AND ei . expenditure_type = et . expenditure_type AND cc . code_combination_id = ael . code_combination_id AND ei . system_linkage_function = SYS . FUNCTION AND DECODE ( xaca . accounting_class_code , 'DISCOUNT' , DECODE ( gl . sla_ledger_cash_basis_flag , 'Y' , 2 , 1 ) , 1 ) = 1 ) trans   WHERE prj.project_id = task.project_id AND trans.task_id = task.task_id AND prj.segment1 BETWEEN nvl ( : p_from_project , prj.segment1 ) AND nvl ( : p_to_project , prj.segment1 ) GROUP BY trans.code_combination_id , trans.gl_account_description , trans.description , prj.segment1 , prj.NAME , trans.manufacturing_related , trans.system_linkage ORDER BY 2 ASC,1 ASC,4 ASC,3 ASC,5 ASC

 

 

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


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