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

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Form 4797 - Gain From Disposition of 1250 Property Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS445_XML
DB package: FA_FAS445_XMLP_PKG 
SELECT	
	--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,
       	th.book_type_code					BOOK,
	ad.asset_number						ASSET,
	ad.asset_id						ASSET_ID,
	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)
								SALE,
	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.nbv_retired * (dh.units_assigned/ah.units),
	ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION)
								NBVR,
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)							GLA,
ROUND(sum(Decode(Sign(Months_Between(ret.date_retired,
       bk.date_placed_in_service) - 12), -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.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount)
	* (dh.units_assigned/ah.units),
	((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount)
	* (abs(dh.transaction_units)/ret.units)))),:PRECISION)
      								XCESS,
ROUND(sum(Decode(Sign(ret.gain_loss_amount),
	1, 	Decode (Sign(Months_Between(ret.date_retired,
	 bk.date_placed_in_service)-12), 
	   -1, 	         decode(ret.units, NULL,
		 (ret.gain_loss_amount - Least(ret.gain_loss_amount, 
	  	 (ret.cost_retired-ret.nbv_retired)))
		* (dh.units_assigned/ah.units),
	            (ret.gain_loss_amount -
		 Least(ret.gain_loss_amount, 	 	          (ret.cost_retired-ret.nbv_retired))) 		        * (abs(dh.transaction_units)/ret.units)),
	decode(ret.units, NULL, 		(ret.gain_loss_amount - 
		Least(ret.gain_loss_amount,
		   ( (ret.cost_retired-ret.nbv_retired) -
                 		       decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
		                              '0',ret.cost_retired-ret.nbv_retired, 
			         ret.stl_deprn_amount)     ) + 
( (ret.cost_retired - ret.nbv_retired) -
 ((ret.cost_retired - ret.nbv_retired) -
decode(ret.stl_deprn_amount, NULL,ret.cost_retired - ret.nbv_retired,
			'0', ret.cost_retired - ret.nbv_retired, 
			ret.stl_deprn_amount))) *0.2
		   ))
		* (dh.units_assigned/ah.units),
	         (ret.gain_loss_amount - 
	          Least(ret.gain_loss_amount,
	         ((ret.cost_retired-ret.nbv_retired) - 
	         decode(ret.stl_deprn_amount,NULL,ret.cost_retired - ret.nbv_retired,
			'0', ret.cost_retired - ret.nbv_retired,
			ret.stl_deprn_amount) )))
	* (abs(dh.transaction_units)/ret.units))),
	0.00)),:PRECISION)							CGAIN,
ROUND(sum(Decode(Sign(gain_loss_amount),
	1,Decode (Sign(Months_Between(ret.date_retired,
		     		 bk.date_placed_in_service)-12), -1,
		decode(ret.units, NULL, 			Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired))
			    			* (dh.units_assigned/ah.units),
			Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired))
					                  * (abs(dh.transaction_units)/ret.units)),
	decode(ret.units, NULL, 		(Least(ret.gain_loss_amount,
		       (   ((ret.cost_retired-ret.nbv_retired) -
            			 decode(ret.stl_deprn_amount,NULL, ret.cost_retired-ret.nbv_retired,
			  			 '0', ret.cost_retired-ret.nbv_retired,
						  ret.stl_deprn_amount) )+
			(   (ret.cost_retired - ret.nbv_retired) - 
			(  ret.cost_retired - ret.nbv_retired -
			 decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
			  				 '0', ret.cost_retired-ret.nbv_retired,
			 				 ret.stl_deprn_amount) )   )   *.20) )  )
						* (dh.units_assigned/ah.units),
		(Least(ret.gain_loss_amount,
		           ((ret.cost_retired-ret.nbv_retired) -
         			  decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
			 '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ))+((
          			 decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
			  '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )*.20))
			* (abs(dh.transaction_units)/ret.units))),
		0.00)),:PRECISION)					 	ORD_INC,
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)							CLOSS,
	Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) GAIN_LOSS
--Added the below functions during DT Fix
	,FA_FAS445_XMLP_PKG.d_excess_1969formula(th.book_type_code, ad.asset_id, :BOOK_CLASS,
		ROUND(sum(Decode(Sign(Months_Between(ret.date_retired,
		   bk.date_placed_in_service) - 12), -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.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount)
		* (dh.units_assigned/ah.units),
		((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount)
		* (abs(dh.transaction_units)/ret.units)))),:PRECISION),
		ROUND(sum(decode(ret.units, NULL,
		ret.cost_retired * (dh.units_assigned/ah.units),
		ret.cost_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION),
		ROUND(sum(decode(ret.units, NULL,
		ret.nbv_retired * (dh.units_assigned/ah.units),
		ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION)
	) D_EXCESS_1969
	,FA_FAS445_XMLP_PKG.d_excess_1962formula(th.book_type_code, ad.asset_id, :BOOK_CLASS,
		ROUND(sum(decode(ret.units, NULL,
		ret.cost_retired * (dh.units_assigned/ah.units),
		ret.cost_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION),
		ROUND(sum(decode(ret.units, NULL,
		ret.nbv_retired * (dh.units_assigned/ah.units),
		ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION),
		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),
		ROUND(sum(Decode(Sign(ret.gain_loss_amount),
		1, 	Decode (Sign(Months_Between(ret.date_retired,
		 bk.date_placed_in_service)-12), 
		   -1, 	         decode(ret.units, NULL,
			 (ret.gain_loss_amount - Least(ret.gain_loss_amount, 
			 (ret.cost_retired-ret.nbv_retired)))
			* (dh.units_assigned/ah.units),
					(ret.gain_loss_amount -
			 Least(ret.gain_loss_amount, 	 	          (ret.cost_retired-ret.nbv_retired))) 		        * (abs(dh.transaction_units)/ret.units)),
		decode(ret.units, NULL, 		(ret.gain_loss_amount - 
			Least(ret.gain_loss_amount,
			   ( (ret.cost_retired-ret.nbv_retired) -
								   decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
										  '0',ret.cost_retired-ret.nbv_retired, 
						 ret.stl_deprn_amount)     ) + 
	( (ret.cost_retired - ret.nbv_retired) -
	 ((ret.cost_retired - ret.nbv_retired) -
	decode(ret.stl_deprn_amount, NULL,ret.cost_retired - ret.nbv_retired,
				'0', ret.cost_retired - ret.nbv_retired, 
				ret.stl_deprn_amount))) *0.2
			   ))
			* (dh.units_assigned/ah.units),
				 (ret.gain_loss_amount - 
				  Least(ret.gain_loss_amount,
				 ((ret.cost_retired-ret.nbv_retired) - 
				 decode(ret.stl_deprn_amount,NULL,ret.cost_retired - ret.nbv_retired,
				'0', ret.cost_retired - ret.nbv_retired,
				ret.stl_deprn_amount) )))
		* (abs(dh.transaction_units)/ret.units))),
		0.00)),:PRECISION),
		ROUND(sum(Decode(Sign(gain_loss_amount),
		1,Decode (Sign(Months_Between(ret.date_retired,
						 bk.date_placed_in_service)-12), -1,
			decode(ret.units, NULL, 			Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired))
								* (dh.units_assigned/ah.units),
				Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired))
										  * (abs(dh.transaction_units)/ret.units)),
		decode(ret.units, NULL, 		(Least(ret.gain_loss_amount,
				   (   ((ret.cost_retired-ret.nbv_retired) -
							 decode(ret.stl_deprn_amount,NULL, ret.cost_retired-ret.nbv_retired,
							 '0', ret.cost_retired-ret.nbv_retired,
							  ret.stl_deprn_amount) )+
				(   (ret.cost_retired - ret.nbv_retired) - 
				(  ret.cost_retired - ret.nbv_retired -
				 decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
								 '0', ret.cost_retired-ret.nbv_retired,
								 ret.stl_deprn_amount) )   )   *.20) )  )
							* (dh.units_assigned/ah.units),
			(Least(ret.gain_loss_amount,
					   ((ret.cost_retired-ret.nbv_retired) -
						  decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
				 '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ))+((
						 decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired,
				  '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )*.20))
				* (abs(dh.transaction_units)/ret.units))),
			0.00)),:PRECISION)
	) D_EXCESS_1962
	,FA_FAS445_XMLP_PKG.P_SECTION_1231_GAIN_1962_p P_SECTION_1231_GAIN_1962
	,FA_FAS445_XMLP_PKG.P_ORDINARY_INCOME_1962_p P_ORDINARY_INCOME_1962
	,FA_FAS445_XMLP_PKG.P_EXCESS_1969_HIDE_p P_EXCESS_1969_HIDE
	,FA_FAS445_XMLP_PKG.P_EXCESS_1969_p P_EXCESS_1969
--End of DT Fix
FROM	
	fa_additions				ad,
	fa_books				bk,
	fa_deprn_periods			dp,
	fa_retirements				ret,
	fa_system_controls			sc,
	fa_transaction_headers			th,
	fa_asset_history			ah,
	fa_distribution_history			dh,
	fa_category_book_defaults		cbd,
	fa_book_controls			bc,
	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	= UPPER(:P_BOOK)       			AND
	th.transaction_type_code in
				('FULL RETIREMENT', 'PARTIAL RETIREMENT')
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.status = 'PROCESSED'
AND	bk.transaction_header_id_out	= th.transaction_header_id	AND
	bk.book_type_code		= UPPER(: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 = '1250'
AND
	dh.asset_id = th.asset_id				AND
dh.asset_id = th.asset_id				AND
	dh.book_type_code = bc.book_type_code 			AND
	dh.book_type_code = bc.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),
	:Company_Name,
	:PERIOD1_PC ,
	:PERIOD2_PC,
	--&ACCT_FLEX_BAL_SEG,
	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'),
	th.book_type_code,
	ad.asset_number,
	ad.asset_id
ORDER BY 
/*        nvl(&ACCT_FLEX_BAL_SEG,'None'),
	ad.asset_number*/
2, 1, 13, 4, 3, 5, 6, 7, 8, 9, 10, 11, 12
Parameter Name SQL text Validation
To Period 
 
 LOV Oracle
From Period 
 
 LOV Oracle
Book 
 
 LOV Oracle
Download
Related Blitz Reports
FA Form 4797 - Ordinary Gains and Losses - draft
FA Form 4797 - Sales or Exchanges of Property - draft
FA Asset Summary Report (Germany)
FA Retired Assets Without Property Classes - draft
FA Form 4797 - Gain From Disposition of 1245 Property - draft
FA Capital Spending - draft
FA Reinstated Assets - draft
FA Mass Additions Invoice Split - draft
FA Fully Reserved Assets - draft