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
Description: AUD: Project Subledger: Summary
Application: Projects
Source: AUD: Project Subledger Summary (XML)
Short Name: PAXMGSLS_XML
DB package: PA_PAXMGSLS_XMLP_PKG
Run
PA Project Subledger Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |