PA Project Subledger Summary

➜ Back to Library
Description
Categories: BI Publisher, Financials
Application: Projects
Source: AUD: Project Subledger Summary (XML)
Short Name: PAXMGSLS_XML
DB package: PA_PAXMGSLS_XMLP_PKG 
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
Parameter Name SQL text Validation
COA_ID 
 
 LOV Oracle
P_SOB_ID 
 
 Number
Set of Books Currency 
 
 LOV Oracle
To GL Date 
 
 Date
From GL Date 
 
 Date
To Project 
 
From Project 
 
To GL Account 
 
From GL Account 
 
Download
 
Related Blitz Reports
PA Project Subledger: Detail by Expenditure Type
PA Project Subledger: Detail by Project
PA Sweep Transaction Accounting Events
JA India - Open Transactions Report for Prior-GST (XML Publisher)
PO Endeca Template
PA Supplier Costs Interface Audit
PA Supplier Cost Audit
AR Open Balances Revaluation
AP Prepayment Balance Report - Functional Currency (CZech)