PA Project Subledger Summary

SELECT trans.code_combination_id,                
               prj.segment1     Project_Number,
               prj.name        Project_Description,
               sys.project_manufacturing_flag        Manufacturing_Related,
               sys.meaning  System_Linkage ,
               SUM(trans.Raw_Cost) Raw_Cost,
               SUM(nvl(nvl(trans.burdened_cost,0), trans.raw_cost) - trans.raw_cost) Burden_Cost,
               SUM(decode(nvl(trans.burdened_cost,0), 0, trans.raw_cost,trans.burdened_cost)) Total_Cost,
	PA_PAXMGSLS_XMLP_PKG.c_account_idformula(trans.code_combination_id) C_Account_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_account_description', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_ACCOUNT_DESCRIPTION,
	PA_PAXMGSLS_XMLP_PKG.cf_acct_currency_codeformula() CF_ACCT_CURRENCY_CODE,
	--PA_PAXMGSLS_XMLP_PKG.ca_raw_costformula(:CF_ACCT_CURRENCY_CODE, :A_raw_cost) CA_raw_cost, 
	--PA_PAXMGSLS_XMLP_PKG.ca_burden_costformula(:CF_ACCT_CURRENCY_CODE, :A_burden_cost) CA_burden_cost, 
	--PA_PAXMGSLS_XMLP_PKG.ca_total_costformula(:CF_ACCT_CURRENCY_CODE, :A_total_cost) CA_total_cost, 
	--PA_PAXMGSLS_XMLP_PKG.cp_raw_costformula(:CF_ACCT_CURRENCY_CODE, :P_raw_cost) CP_raw_cost, 
	--PA_PAXMGSLS_XMLP_PKG.cp_burden_costformula(:CF_ACCT_CURRENCY_CODE, :P_burden_cost) CP_burden_cost, 
	--PA_PAXMGSLS_XMLP_PKG.cp_total_costformula(:CF_ACCT_CURRENCY_CODE, :P_total_cost) CP_total_cost, 
	PA_PAXMGSLS_XMLP_PKG.mfg_relatedformula(sys.project_manufacturing_flag) Mfg_related, 
	--PA_PAXMGSLS_XMLP_PKG.cm_raw_costformula(:CF_ACCT_CURRENCY_CODE, :M_raw_cost) CM_raw_cost, 
	--PA_PAXMGSLS_XMLP_PKG.cm_burden_costformula(:CF_ACCT_CURRENCY_CODE, :M_burden_cost) CM_burden_cost, 
	--PA_PAXMGSLS_XMLP_PKG.cm_total_costformula(:CF_ACCT_CURRENCY_CODE, :M_total_cost) CM_total_cost, 
	PA_PAXMGSLS_XMLP_PKG.c_sys_linkageformula(sys.meaning) C_sys_linkage 
	--PA_PAXMGSLS_XMLP_PKG.c_raw_costformula(:CF_ACCT_CURRENCY_CODE, SUM ( Raw_Cost )) C_raw_cost, 
	--PA_PAXMGSLS_XMLP_PKG.c_burden_costformula(:CF_ACCT_CURRENCY_CODE, SUM ( Burden_Cost )) C_burden_cost, 
	--PA_PAXMGSLS_XMLP_PKG.c_total_costformula(:CF_ACCT_CURRENCY_CODE, SUM ( Total_Cost )) C_total_cost
FROM 
  pa_expenditure_types            et,
  pa_projects_all                 prj,
  pa_tasks                        task,
  gl_code_combinations            cc,
  pa_system_linkages              sys,
  (
        SELECT    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_all          imp
        WHERE   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   trunc(cdl.gl_date) BETWEEN nvl(:cf_from_date, trunc(cdl.gl_date)) AND nvl(:cf_to_date, trunc(cdl.gl_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(+)
        UNION  
        SELECT    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_all          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
        WHERE   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   trunc(cdl.gl_date) BETWEEN nvl(:cf_from_date, trunc(cdl.gl_date)) AND nvl(:cf_to_date, trunc(cdl.gl_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   decode ( xaca.accounting_class_code ,'DISCOUNT', decode (gl.sla_ledger_cash_basis_flag , 'Y',  2 , 1) , 1 ) = 1
        ) trans
WHERE  &c_where
AND   trans.expenditure_type             =  et.expenditure_type
AND     task.project_id                  =  prj.project_id
AND    trans.task_id                     =  task.task_id
AND       cc.code_combination_id = trans.code_combination_id
 AND   trans.system_linkage_function      =  sys.function
  AND   prj.segment1 BETWEEN nvl(:p_from_project, prj.segment1) AND nvl(:p_to_project, prj.segment1)
GROUP BY trans.code_combination_id, 
                    cc.description,
                     prj.segment1,
                     prj.name,
                     sys.project_manufacturing_flag,
                     sys.meaning,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_account_description', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION')
					 ORDER BY 2 ASC,1 ASC,4 ASC,3 ASC,5 ASC