PA Project Subledger Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: AUD: Project Subledger: Summary
Application: Projects
Source: AUD: Project Subledger Summary (XML)
Short Name: PAXMGSLS_XML
DB package: PA_PAXMGSLS_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Operating Unit
 
LOV
From GL Account
 
Char
To GL Account
 
Char
From Project
 
Char
To Project
 
Char
From GL Date
 
Date
To GL Date
 
Date