FA Asset Retirements Report- Not Supported: Reserved For Future Use

SELECT 	
        --&ACCT_FLEX_BAL_SEG		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,
	falu.meaning				asset_type,
	decode (ah.asset_type,
		'CIP', cb.cip_cost_acct,
		cb.asset_cost_acct)			account,
	--&ACCT_FLEX_COST_SEG		cost_center,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1,
	ad.asset_number,
	ret.date_retired,
	ad.asset_number || ' - ' || ad.description		asset_num_desc,
	th.transaction_type_code,
	th.asset_id,
	books.date_placed_in_service,
	sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
	    decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
	    aj.adjustment_amount)		cost,
	sum(decode(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
	    decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
	    aj.adjustment_amount)		nbv,
	sum(decode(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
	    decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
	    aj.adjustment_amount)		proceeds,            
	sum(decode(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
	    decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
	    aj.adjustment_amount)		removal,	
	sum(decode(aj.adjustment_type,'REVAL RSV RET',1,0)*
	    decode(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
	     aj.adjustment_amount)		reval_rsv_ret,
	th.transaction_header_id,
	decode (th.transaction_type_code,
		'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
		to_char(null))			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_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, */
	FA_FAS440_XMLP_PKG.gain_lossformula(sum ( decode ( aj.adjustment_type , 'NBV RETIRED' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'PROCEEDS CLR' , 1 , 'PROCEEDS' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , 1 , 'CR' , - 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REMOVALCOST' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REVAL RSV RET' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount )) Gain_Loss
FROM
	fa_transaction_headers		th,
	fa_additions			ad,
	&lp_fa_books				books,
	&lp_fa_retirements			ret,
	&lp_fa_adjustments			aj,
	fa_distribution_history		dh,
	gl_code_combinations		dhcc,
	fa_asset_history			ah,
	fa_category_books			cb,
	fa_lookups			falu
WHERE 	
	th.date_effective		 >= :PERIOD1_POD  AND
	th.date_effective		 <= :PERIOD2_PCD  AND
	th.book_type_code		 =  :P_BOOK  AND
	th.transaction_key		= 'R'
AND
	ret.book_type_code		= :P_BOOK  AND	
	ret.asset_id		= books.asset_id		AND
	decode (th.transaction_type_code,
		'REINSTATEMENT', ret.transaction_header_id_out,
		ret.transaction_header_id_in)	= th.transaction_header_id
AND
	ad.asset_id		= th.asset_id
AND
	aj.asset_id		= ret.asset_id		AND
	aj.book_type_code	= :P_BOOK  
     and aj.adjustment_type not in (select  'PROCEEDS' from &lp_fa_adjustments aj1
				where aj1.book_type_code = aj.book_type_code
				and aj1.asset_id = aj.asset_id
				and aj1.transaction_header_id = aj.transaction_header_id
				and aj1.adjustment_type = 'PROCEEDS CLR')  
AND	aj.transaction_header_id	= th.transaction_header_id
AND
	ah.asset_id		= ad.asset_id		AND
	ah.date_effective		<= th.date_effective		AND
	nvl(ah.date_ineffective, th.date_effective+1)
				> th.date_effective
AND
	falu.lookup_code		= ah.asset_type		AND
	falu.lookup_type		= 'ASSET TYPE'
AND
	books.transaction_header_id_out
				= th.transaction_header_id	AND
	books.book_type_code	= :P_BOOK  AND
	books.asset_id		= ad.asset_id
AND	
	cb.category_id		= ah.category_id		AND
	cb.book_type_code		= :P_BOOK  AND
	dh.distribution_id	= aj.distribution_id
AND th.asset_id = dh.asset_id   
AND
	dhcc.code_combination_id	= dh.code_combination_id
GROUP BY
	falu.meaning,
	/*&ACCT_FLEX_BAL_SEG,
	&ACCT_FLEX_COST_SEG,*/
	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'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
	th.transaction_type_code,
	th.asset_id,
	cb.asset_cost_acct,
	cb.cip_cost_acct,
	ad.asset_number,
 	ad.description,
	books.date_placed_in_service,
	ret.date_retired,
	th.transaction_header_id,
	ah.asset_type,
	ret.gain_loss_amount
--ORDER BY 1,2,3,4,5,6
ORDER BY 1,2,3,4,8, 9, 5, 7, 10, 6, 11, 12, 13, 14, 15, 16, 17