FA Recoverable Cost - draft

SELECT	NVL(&ACCT_FLEX_BAL_SEG, 'NONE')	 			COMP_CODE,
	AD.ASSET_NUMBER						ASSET_NUMBER,
	ROUND(SUM(NVL(MC.COST,BK_CORP.COST) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS) ,:PRECISION)	CORP_COST,
	ROUND(SUM(NVL(MC.SALVAGE_VALUE,BK_CORP.SALVAGE_VALUE) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)	CORP_SALV_VALUE,	
ROUND(SUM(NVL(MC.ADJUSTED_RECOVERABLE_COST,NVL(BK_CORP.adjusted_recoverable_cost,0)) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)	CORP_RECOV_COST,
	DECODE(SIGN(ROUND(SUM(NVL(MC.COST,BK_CORP.COST) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS)
	   	- SUM(BK_CORP.SALVAGE_VALUE * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)
		- 
          ROUND(SUM(NVL(MC.ADJUSTED_RECOVERABLE_COST,NVL(BK_CORP.adjusted_recoverable_cost,0)) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)),
		'0',NULL,'*') 					CORP_CHECK,
	ROUND(SUM(BK_TAX.COST * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)	TAX_COST,
	ROUND(SUM(BK_TAX.SALVAGE_VALUE * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS)	,:PRECISION)TAX_SALV_VALUE,
	ROUND(SUM(NVL(BK_TAX.ITC_BASIS,0) * 
		NVL(ITC.BASIS_REDUCTION_RATE,0) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)	BASIS_RED_AMNT,
	ROUND(SUM(NVL(BK_TAX.adjusted_recoverable_cost,0) *
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)	TAX_RECOV_COST,
	DECODE(SIGN(ROUND(SUM(BK_TAX.COST * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS) 
		- SUM(BK_TAX.SALVAGE_VALUE * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS / AH.UNITS))
		- (SUM(NVL(BK_TAX.ITC_BASIS,0) *
		NVL(ITC.BASIS_REDUCTION_RATE,0)	* 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS)),:PRECISION)
	 	- ROUND(SUM(NVL(BK_TAX.adjusted_recoverable_cost,0) * 
		NVL(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION)),
		'0',NULL,'*') 					TAX_CHECK, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1
FROM
	FA_ADDITIONS 				AD,
	FA_MC_BOOKS				MC,
	FA_BOOKS_BAS 				BK_CORP,
	FA_ASSET_HISTORY			AH,
	FA_BOOKS 				BK_TAX,
	FA_ITC_RATES				ITC,
	FA_DISTRIBUTION_HISTORY			DH,
	GL_CODE_COMBINATIONS			DHCC
WHERE
	AD.ASSET_ID			= AH.ASSET_ID			AND
	:CORP_PERIOD_PCD BETWEEN AH.DATE_EFFECTIVE AND
    		NVL(AH.DATE_INEFFECTIVE, :CORP_PERIOD_PCD)		AND
	AD.ASSET_ID       		= BK_TAX.ASSET_ID 
AND
   	BK_CORP.ASSET_ID(+)		= AD.ASSET_ID    		                  AND
                  BK_CORP.BOOK_TYPE_CODE(+)	= :DISTRIBUTION_SOURCE_BOOK  AND 
	MC.TRANSACTION_HEADER_ID_IN(+) = 
                                     DECODE(:mrc_sob_type_code,'R',BK_CORP.TRANSACTION_HEADER_ID_IN,NULL)
AND 	MC.SET_OF_BOOKS_ID(+)  = :set_of_books_id
AND  	BK_TAX.BOOK_TYPE_CODE		= :P_BOOK  AND
 	BK_TAX.DEPRECIATE_FLAG          = 'YES'                                                                       AND   	
	ITC.ITC_AMOUNT_ID(+) 		= BK_TAX.ITC_AMOUNT_ID
AND     
	:PERIOD1_PCD BETWEEN BK_TAX.DATE_EFFECTIVE AND
		NVL(BK_TAX.DATE_INEFFECTIVE, :PERIOD1_PCD)		AND
	:CORP_PERIOD_PCD BETWEEN BK_CORP.DATE_EFFECTIVE AND
		NVL(BK_CORP.DATE_INEFFECTIVE, :CORP_PERIOD_PCD)
AND
	DH.ASSET_ID(+) 			= AD.ASSET_ID   		AND
   	DH.BOOK_TYPE_CODE(+)		= :DISTRIBUTION_SOURCE_BOOK  AND
	:CORP_PERIOD_PCD BETWEEN DH.DATE_EFFECTIVE AND
		NVL(DH.DATE_INEFFECTIVE, :CORP_PERIOD_PCD)		
AND
	DH.CODE_COMBINATION_ID		= DHCC.CODE_COMBINATION_ID(+)
GROUP BY  
	AD.ASSET_NUMBER,
	NVL(&ACCT_FLEX_BAL_SEG, 'NONE')
--Added during DT Fix
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
--End of DT Fix
ORDER BY 1,2