PA Project Subledger: Detail by Expenditure Type

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: AUD: Project Subledger: Detail by Expenditure Type: Shows the transactions in Oracle Projects for a GL account Expenditure Typewise across projects
Application: Projects
Source: AUD: Project Subledger: Detail by Expenditure Type (XML)
Short Name: PAXMGSLE_XML
DB package: PA_PAXMGSLD_XMLP_PKG
Run PA Project Subledger: Detail by Expenditure Type and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  null		   C_FLEXDATA3,   
        cdl_ei.dr_code_combination_id code_combination_id, 
        cc.description		   Account_description,
        prj.segment1		   project_number,
        prj.name		   project_name,
        task.task_number			,
        task.task_name				,
        fnd.meaning				,
        sys.meaning		   system_linkage_function,
        et.expenditure_type			,
        task.task_name || fnd.meaning || sys.meaning task_mfg_sys,
        SUM(cdl_ei.acct_raw_cost) raw_cost		,
        SUM(nvl(nvl(cdl_ei.acct_burdened_cost,0), cdl_ei.acct_raw_cost) -
                cdl_ei.acct_raw_cost) burden_cost	,
        SUM(decode(nvl(cdl_ei.acct_burdened_cost,0), 0, cdl_ei.acct_raw_cost,
                cdl_ei.acct_burdened_cost)) total_cost, 
	PA_PAXMGSLD_XMLP_PKG.cf_account_idformula(cdl_ei.dr_code_combination_id) CF_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
FROM    pa_expenditure_types            et,
        pa_projects_all                 prj,
        pa_tasks                        task,
        gl_code_combinations            cc,
	(Select cdl.dr_code_combination_id AS dr_code_combination_id	,
		ei.expenditure_type		,
		ei.expenditure_item_id		,
		ei.task_id			,
		ei.system_linkage_function	,
		cdl.acct_raw_cost		,
		cdl.acct_burdened_cost		
	   From
		pa_expenditure_items_all        ei,
	        pa_cost_distribution_lines_all  cdl,
		pa_transaction_Sources          ts
	  Where cdl.expenditure_item_id         =  ei.expenditure_item_id
	    AND cdl.line_type                   =  'R'
	    AND ts.transaction_source (+)      =  ei.transaction_source
	    AND nvl(ei.historical_flag,'Y')     =  decode(nvl(ts.predefined_flag,'Y'),'N',nvl(ei.historical_flag,'Y'),'Y')
	    AND cdl.transfer_status_code || '' in  ('A','V')
	    AND trunc(cdl.gl_date) BETWEEN 
		        NVL(:cp_from_date,  trunc(cdl.gl_date)) AND 
		        NVL(:cp_to_date,      trunc(cdl.gl_date))
	    AND cdl.acct_event_id is null
	  UNION ALL
Select ael.code_combination_id AS dr_code_combination_id,
		ei.expenditure_type		,
		ei.expenditure_item_id		,
		ei.task_id			,
		ei.system_linkage_function	,
		cdl.acct_raw_cost		,
		cdl.acct_burdened_cost
From
		pa_expenditure_items_all        ei,
	        pa_cost_distribution_lines_all  cdl,
		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,
		pa_implementations_all          imp,
		gl_ledgers			gl
	  Where cdl.expenditure_item_id         =  ei.expenditure_item_id
	    AND cdl.line_type                   =  'R'
	    AND cdl.transfer_status_code || '' in  ('A','V')
	    AND trunc(cdl.gl_date) BETWEEN 
		        NVL(:cp_from_date,  trunc(cdl.gl_date)) AND 
		        NVL(:cp_to_date,      trunc(cdl.gl_date))
	    AND ((nvl(ei.historical_flag,'Y') = 'N') or (cdl.acct_event_id is not null))
	    AND cdl.org_id = imp.org_id
	    AND ts.transaction_source (+)      =  ei.transaction_source
	    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 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
	    AND xad.program_owner_code = xpap.program_owner_code
            AND xpap.program_code = 'PA_POSTACCOUNTING_DEBIT'
            AND xpap.application_id = 275
	 ) cdl_ei,
        pa_system_linkages sys,
        fnd_lookups fnd
WHERE   cdl_ei.expenditure_type             =  et.expenditure_type
AND     cc.code_combination_id          =  cdl_ei.dr_code_combination_id
AND     cdl_ei.task_id                      =  task.task_id
AND     task.project_id                 =  prj.project_id
AND     cdl_ei.system_linkage_function      =  sys.function
AND     prj.project_id = nvl(:p_project_id,prj.project_id) 
AND     task_number BETWEEN 
        NVL(:p_from_task, task.task_number) AND
        NVL(:p_to_task, task.task_number)
AND     sys.project_manufacturing_flag = lookup_code
AND     lookup_type = 'YES_NO'
AND     &c_where
AND     :p_sort_type = 'P'
GROUP  BY  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'),
           cdl_ei.dr_code_combination_id, 
           cc.description,
           prj.segment1,
           prj.name,
           task.task_number,
           task.task_name,
           fnd.meaning,
           sys.meaning,
           et.expenditure_type,
           task.task_name || fnd.meaning || sys.meaning ORDER BY 2 ASC,1 ASC,3 ASC,4 ASC,5 ASC,8 ASC,11 ASC
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
From GL Account 
 
 Char
To GL Account 
 
 Char
From GL Date 
 
 Date
To GL Date 
 
 Date
Expenditure Type 
 
 LOV Oracle
From Project 
 
 Char
To Project 
 
 Char
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis (Distributions) PA Project Subledger Summary PA Project Subledger: Detail by Project GL Account Analysis (Distributions) 11g GL SLA Drillback Diagnostic GL Account Analysis 11g ECC Projects, Projects Costing: Transactions, SQL1 XLA Distribution Links Summary
Blitz Report™

Blitz Report™ provides multiple benefits: