FA Form 4797 - Sales or Exchanges of Property - draft

SELECT 	
	nvl(&ACCT_FLEX_BAL_SEG, 'None')		        comp_code,
	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,
	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),
	nvl(&ACCT_FLEX_BAL_SEG, 'None'),
	ad.property_1245_1250_code,
        cb.asset_cost_acct,
	ad.asset_number,
	ad.description,
	bk.date_placed_in_service,
	ret.date_retired,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')
/*ORDER BY 
	nvl(&ACCT_FLEX_BAL_SEG, 'None'),
	ad.property_1245_1250_code,
	cb.asset_cost_acct,
	lpad(ad.asset_number,15,0)*/
	ORDER BY comp_code,GAIN_loss,class,account,ASSET_NUM,DPIS,DATE_SOLD,PROCEEDS,
	RESERVE,BASIS,GAIN