PA Project Subledger: Detail by Project

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