PA Project Subledger: Detail by Project

Description
Categories: BI Publisher, Financials
Application: Projects
Source: AUD: Project Subledger: Detail by Project (XML)
Short Name: PAXMGSLD_XML
DB package: PA_PAXMGSLD_XMLP_PKG
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,
        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		
	   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 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
		AND cdl.org_id = imp.org_id
	  UNION
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          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    ( 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
P_SORT_TYPE
 
P_COA_ID
 
LOV Oracle
Set of Books Id
 
Number
Ledger Currency
 
LOV Oracle
To Task Number
 
From Task Number
 
Project Number
 
LOV Oracle
To GL Date
 
Date
From GL Date
 
Date
To GL Account
 
From GL Account