FA Asset Retirements - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Retirements Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS440_XML
DB package: FA_FAS440_XMLP_PKG
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
Parameter Name SQL text Validation
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Set of Books Currency
 
LOV Oracle
Book
 
LOV Oracle
Ask a question