FA Reinstated Assets - draft

SELECT 	FALU.MEANING					ASSET_TYPE,
	--nvl(&ACCT_FLEX_BAL_SEG,'None')			comp_code,
	nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),'None') comp_code_dsp1, 
	--NVL(&ACCT_FLEX_COST_SEG,'None')			cost_center,
	NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),'None') cost_center_dsp1,
	DECODE(AH.ASSET_TYPE,
               'CIP',CB.CIP_COST_ACCT,
               CB.ASSET_COST_ACCT)                 	ACCOUNT,
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION	NO,
	books.date_placed_in_service			DPIS,
	RET.DATE_RETIRED		DR,
	DP.PERIOD_NAME					PREIN,
	ROUND(sum(decode(ret.units, NUll, ret.cost_retired
	* (dh.units_assigned /ah.units),
	ret.cost_retired * (abs(dh.transaction_units) / ret.units))),:PRECISION)
							cost,
	ROUND(sum(decode(ret.units, NUll, ret.cost_retired
	* (dh.units_assigned /ah.units),
	ret.cost_retired * (abs(dh.transaction_units) / ret.units))) - 
	sum(decode(ret.units, NUll, ret.nbv_retired
	* (dh.units_assigned /ah.units),
	ret.nbv_retired * (abs(dh.transaction_units) / ret.units))),:PRECISION)
							reserve,
	ROUND(sum(decode(ret.units, NUll, ret.gain_loss_amount
	* (dh.units_assigned /ah.units),
	ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units))),:PRECISION)
							gl,
	ROUND(sum(decode(ret.units, NUll, nvl(ret.itc_recaptured,0.00)
	* (dh.units_assigned /ah.units),
	nvl(ret.itc_recaptured,0.00)
	* (abs(dh.transaction_units) / ret.units))),:PRECISION)
							itc
	/*,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code_dsp, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center_dsp*/
FROM
	FA_LOOKUPS				FALU,
	FA_DISTRIBUTION_HISTORY			DH,
	GL_CODE_COMBINATIONS			dhCC,
	FA_DEPRN_PERIODS			DP,
	FA_ASSET_HISTORY			AH,
	FA_ADDITIONS				AD,
	FA_CATEGORY_BOOKS			CB,
	FA_BOOKS				BOOKS2,
	FA_BOOKS				BOOKS,
	FA_RETIREMENTS				RET,
	FA_TRANSACTION_HEADERS			TH
WHERE
	DP.BOOK_TYPE_CODE	= :P_Book
AND
	TH.DATE_EFFECTIVE	 BETWEEN
		DP.PERIOD_OPEN_DATE				AND
		NVL (DP.PERIOD_CLOSE_DATE, SYSDATE)		AND
	TH.BOOK_TYPE_CODE 	  = :P_Book  AND
	TH.TRANSACTION_TYPE_CODE  = 'REINSTATEMENT'		AND
	TH.DATE_EFFECTIVE	 BETWEEN
		:Period1_POD  and :Period2_PCD 
AND
	BOOKS.TRANSACTION_HEADER_ID_OUT	= TH.TRANSACTION_HEADER_ID	AND
	BOOKS.BOOK_TYPE_CODE		= :P_Book
AND
	BOOKS2.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE		AND
	BOOKS2.ASSET_ID = BOOKS.ASSET_ID				AND
	BOOKS2.DATE_INEFFECTIVE IS NULL				AND
	BOOKS2.DATE_EFFECTIVE BETWEEN :PERIOD1_POD  AND :PERIOD2_PCD  AND
	BOOKS2.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND
	RET.TRANSACTION_HEADER_ID_OUT 	= TH.TRANSACTION_HEADER_ID	AND
	RET.BOOK_TYPE_CODE		= :P_Book  AND
	AD.ASSET_ID 		= TH.ASSET_ID
AND
	AH.ASSET_ID		= TH.ASSET_ID			AND
	AH.DATE_EFFECTIVE  	<= TH.DATE_EFFECTIVE		AND
	(AH.DATE_INEFFECTIVE IS NULL				OR
	AH.DATE_INEFFECTIVE 	> TH.DATE_EFFECTIVE)
AND
	FALU.LOOKUP_CODE	= AH.ASSET_TYPE			AND
	FALU.LOOKUP_TYPE	= 'ASSET TYPE'
AND
	CB.CATEGORY_ID		= AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE	= :P_Book
AND
	DH.ASSET_ID		= TH.ASSET_ID			AND
	DH.BOOK_TYPE_CODE	= :Distribution_Source_Book  AND
	( DH.RETIREMENT_ID	= RET.RETIREMENT_ID			OR
	  ( RET.DATE_EFFECTIVE	>= DH.DATE_EFFECTIVE			AND
	    RET.DATE_EFFECTIVE	<= NVL (DH.DATE_INEFFECTIVE,SYSDATE)	AND
	    RET.UNITS		IS NULL))
AND
	dhCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
GROUP BY
--	nvl(&ACCT_FLEX_BAL_SEG,'None'),
	nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),'None'),
	FALU.MEANING,
	DECODE(AH.ASSET_TYPE,
               'CIP',CB.CIP_COST_ACCT,
               CB.ASSET_COST_ACCT),
--	NVL(&ACCT_FLEX_COST_SEG,'None'),
	NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),'None'),
	AD.ASSET_NUMBER,
	AD.DESCRIPTION,
	books.date_placed_in_service,
	RET.DATE_RETIRED,
	DP.PERIOD_NAME
ORDER BY 
/*	nvl(&ACCT_FLEX_BAL_SEG,'None'),
	falu.meaning,
	DECODE(AH.ASSET_TYPE,
               'CIP',CB.CIP_COST_ACCT,
               CB.ASSET_COST_ACCT),
 	NVL(&ACCT_FLEX_COST_SEG,'None'),
	ASSET_NUMBER*/
2, 1, 4, 3, 5, 6, 7, 8, 9, 10, 11, 12