PA Project Subledger: Detail by Project

Description
Categories: BI Publisher
Imported from BI Publisher
Description: AUD: Project Subledger: Detail by Project: Shows the transactions in Oracle Projects for a GL account projectwise
Application: Projects
Source: AUD: Project Subledger: Detail by Project (XML)
Short Name: PAXMGSLD_XML
DB package: PA_PAXMGSLD_XMLP_PKG
Run PA Project Subledger: Detail by Project and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT /*+ no_expand */ 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,
        pa_implementations          imp,
	(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		,
		cdl.line_num	
	   From
		pa_expenditure_items_all        ei,
	        pa_cost_distribution_lines_all  cdl,
	 	pa_transaction_Sources          ts,
		pa_implementations          imp
	  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 cdl.gl_date between :cp_from_date and :cp_to_date 
	    AND cdl.acct_event_id is null
		AND cdl.org_id = imp.org_id
	  UNION
Select /*+ no_expand  leading(imp xpap gl cdl ei ts xdl aeh ael)  use_nl(imp xpap gl cdl ei xdl aeh ael)  index(cdl PA_COST_DISTRIBUTION_LINES_N18) */  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		,
		cdl.line_num
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          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 cdl.gl_date between :cp_from_date and :cp_to_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 = xdl.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    ( prj.org_id =imp.org_id or imp.org_id is null )
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
Project Number
 
LOV Oracle
From Task Number
 
Char
To Task Number
 
Char
Blitz Report™