FA Form 4797 - Gain From Disposition of 1245 Property - draft

SELECT 	
	--nvl(&ACCT_FLEX_BAL_SEG,'None')			COMP_CODE,
    --    &ACCT_FLEX_ACCT_SEG				ACCOUNT,
--Added during DT Fix
	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_DSP1,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('account_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ACCOUNT_DSP1,
--End of DT Fix
	ad.asset_number  || ' - ' || AD.DESCRIPTION		ASSET_NUMBER,
	ad.asset_id 			ASSET_ID,
	ROUND(sum(decode(ret.units, NUll, ret.proceeds_of_sale
	* (dh.units_assigned /ah.units),
	ret.proceeds_of_sale * (abs(dh.transaction_units) / ret.units))),:PRECISION)
							PROCEEDS,
	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_RETIRED,
	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)
							GAIN,
	ROUND(sum(decode(sign(ret.gain_loss_amount), 1,
	 decode(sign(ret.cost_retired - ret.nbv_retired-ret.gain_loss_amount),
	 -1,
	 (decode(ret.units, NUll, 
	 (ret.gain_loss_amount - ret.cost_retired + ret.nbv_retired)
	 * (dh.units_assigned /ah.units),
	 (ret.gain_loss_amount - ret.cost_retired + ret.nbv_retired)
	 * (abs(dh.transaction_units) / ret.units))), (0.00)), (0.00))),:PRECISION)	CAP_GAIN,
	ROUND(sum(decode(sign(ret.gain_loss_amount), 1,  
	 decode(sign(ret.cost_retired - ret.nbv_retired-ret.gain_loss_amount),
	 -1,
	 (decode(ret.units, NUll, (ret.cost_retired-ret.nbv_retired)
	 * (dh.units_assigned /ah.units),
	 (ret.cost_retired - ret.nbv_retired)
	 * (abs(dh.transaction_units) / ret.units))), (decode(ret.units, NUll, ret.gain_loss_amount
	 * (dh.units_assigned /ah.units),
	 ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units)))),0.00)),:PRECISION)   ORD_INCOME,
	ROUND(sum(decode(sign(ret.gain_loss_amount), -1,
	(decode(ret.units, NUll, ret.gain_loss_amount
	* (dh.units_assigned /ah.units),
	ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units))),
	0.00)),:PRECISION)						CAP_LOSS,
	Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) GAIN_LOSS
FROM	
	fa_deprn_periods			dp,
      	fa_retirements				ret,
   	fa_lookups			lu,
	fa_additions				ad,
	fa_asset_history			ah,
       	fa_category_books			cb,
	fa_transaction_headers			th,
	fa_books				books,
	fa_distribution_history			dh,
	fa_category_book_defaults		cbd,
	gl_code_combinations			dhcc
WHERE 	
      	dp.book_type_code	     = UPPER(:P_BOOK)                   AND
	dp.period_counter           >= :PERIOD1_PC     			AND
	dp.period_counter 	    <= nvl(:PERIOD2_PC,
					dp.period_counter)
AND
	th.date_effective	    >= dp.period_open_date		AND
	th.date_effective	    <= NVL(dp.period_close_date,sysdate)AND
	th.book_type_code            = dp.book_type_code		AND
	(th.transaction_type_code    = 'FULL RETIREMENT'		OR
	th.transaction_type_code     = 'PARTIAL RETIREMENT')
AND
	ret.transaction_header_id_in = th.transaction_header_id		AND
	((UPPER(ret.retirement_type_code) like '%SALE%')		OR 
	((ret.retirement_type_code is NULL) AND (ret.proceeds_of_sale > 0))) 
AND
	ret.status = 'PROCESSED'
AND
	books.transaction_header_id_out = th.transaction_header_id	AND
	books.book_type_code  		= th.book_type_code		
AND
	cbd.category_id         	     = ah.category_id		AND
	cbd.book_type_code             	     = th.book_type_code	AND
	(books.date_placed_in_service between cbd.start_dpis and 
				nvl(cbd.end_dpis,sysdate))		AND
	(months_between(ret.date_retired, books.date_placed_in_service) > 
				nvl(cbd.capital_gain_threshold,12))	
AND
        cb.category_id         	     = ah.category_id			AND
	cb.book_type_code            = th.book_type_code
AND
	lu.lookup_type(+) = 'GAINLOSS'				AND
	lu.lookup_code(+) = Decode (Sign(ret.gain_loss_amount), -1, 'LOSS', 'GAIN') 
AND
	ad.asset_id 		     = th.asset_id			AND
	ad.property_1245_1250_code   = '1245'           
 AND		
	ah.asset_id		     = ad.asset_id			AND
	ah.date_effective	     <= th.date_effective		AND
	(ah.date_ineffective is null					OR
	ah.date_ineffective 	     > th.date_effective)		
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
	Sign(ret.gain_loss_amount),
   	lu.meaning,
	:PERIOD1_PC ,
	nvl(:PERIOD2_PC, '&3'),
--Start DT Fix
	--nvl(&ACCT_FLEX_BAL_SEG,'None'),
	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'),
--End of DT Fix
	th.book_type_code,
--Start DT Fix
        --&ACCT_FLEX_ACCT_SEG, 
		fnd_flex_xml_publisher_apis.process_kff_combination_1('account_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
--End of DT Fix
        ad.asset_number,
        ad.description,
        ad.asset_id
ORDER BY 
/*
	nvl(&ACCT_FLEX_BAL_SEG,'None'),
	&ACCT_FLEX_ACCT_SEG, 
                 ad.asset_number
*/
 1, 12, 2, 4, 3, 5, 6, 7, 8, 9, 10, 11