FA Form 4797 - Ordinary Gains and Losses - draft

SELECT 	
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')		        comp_code,
	CB.ASSET_COST_ACCT		account,
	ad.property_1245_1250_code			class,
	ad.asset_number || ' - ' || ad.description	asset_num,
	bk.date_placed_in_service			dpis,
	ret.date_retired				date_sold,
	ROUND(sum(decode(ret.units, NULL,
		nvl(ret.proceeds_of_sale,0.00)*(dh.units_assigned/ah.units),
		nvl(ret.proceeds_of_sale,0.00)*
		(abs(dh.transaction_units)/ret.units))),:PRECISION)
							proceeds,
	ROUND(sum(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))),:PRECISION)
							reserve,
	ROUND(sum(decode(ret.units, NULL,
		(ret.cost_retired + nvl(ret.cost_of_removal, 0))
		*(dh.units_assigned/ah.units),
		(ret.cost_retired + nvl(ret.cost_of_removal, 0))
		*(abs(dh.transaction_units)/ret.units))),:PRECISION)
							basis,
	Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) gain_loss,
	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
FROM	
	fa_retirements				ret,
	fa_additions				ad,
	fa_asset_history			ah,
	fa_system_controls			sc,
	fa_category_books			cb,
	fa_transaction_headers			th,
	fa_books				bk,
	fa_distribution_history			dh,
	gl_code_combinations			dhcc,
	fa_category_book_defaults		cbd
WHERE 	
	th.date_effective		 >= :PERIOD1_POD  AND
	th.date_effective		 <= :PERIOD2_PCD  AND
	th.book_type_code		 =  :P_BOOK  AND
	th.transaction_type_code || ''	in
		('FULL RETIREMENT',
		'PARTIAL RETIREMENT')				        AND
	th.transaction_key		= 'R'
AND
	ah.asset_id		= th.asset_id				AND
	ah.date_effective	<= th.date_effective			AND
	nvl(ah.date_ineffective,sysdate+1) > th.date_effective		
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.asset_id		= th.asset_id		                AND
	ret.status = 'PROCESSED'
AND
	bk.transaction_header_id_out	= th.transaction_header_id	AND
	bk.book_type_code  		= :P_BOOK
AND
	cbd.category_id         	     = ah.category_id		AND
	cbd.book_type_code             	     = th.book_type_code	AND
	(bk.date_placed_in_service between cbd.start_dpis and 
				nvl(cbd.end_dpis,sysdate))		AND
	(months_between(ret.date_retired, bk.date_placed_in_service) <=
				nvl(cbd.capital_gain_threshold,12))	
AND
	ad.asset_id 			= th.asset_id			AND
	ad.property_1245_1250_code	in ('1245', '1250')
AND	
	cb.category_id			= ad.asset_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
	sign(ret.gain_loss_amount),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	ad.property_1245_1250_code,
	CB.ASSET_COST_ACCT,
	ad.asset_number,
	ad.description,
	bk.date_placed_in_service,
	ret.date_retired
ORDER BY 
/*	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	Decode (Sign(ret.gain_loss_amount), -1, 'LOSS', 'GAIN'),
	ad.property_1245_1250_code,
	CB.ASSET_COST_ACCT,
	ad.asset_number*/
1, 10, 3, 2, 4, 5, 6, 7, 8, 9, 10