FA Mass Retirements - draft

		 SELECT 	&ACCT_FLEX_BAL_SEG		comp_code,
	falu.meaning				asset_type,
	decode (ah.asset_type,
		'CIP', cb.cip_cost_acct,
		cb.asset_cost_acct)			account,
	&ACCT_FLEX_COST_SEG		cost_center,
	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,
	decode (th.transaction_type_code, 'REINSTATEMENT', -1, 1) *
	sum(decode(ret.units, Null,
 		ret.cost_retired * (dh.units_assigned /ah.units),
 		ret.cost_retired * abs(dh.transaction_units) / ret.units))
						cost,
	decode (th.transaction_type_code, 'REINSTATEMENT', -1, 1) *
	sum(decode(ret.units, Null, 
		ret.nbv_retired * (dh.units_assigned /ah.units),
		ret.nbv_retired * abs(dh.transaction_units) / ret.units))
						nbv,
	decode (th.transaction_type_code, 'REINSTATEMENT', -1, 1) *
	sum(decode(ret.units, Null, 
		ret.proceeds_of_sale * (dh.units_assigned /ah.units),
		ret.proceeds_of_sale *
			abs(dh.transaction_units) / ret.units))
						proceeds,
	decode (th.transaction_type_code, 'REINSTATEMENT', -1, 1) *
	sum(decode(ret.units, Null, 
		ret.cost_of_removal * (dh.units_assigned /ah.units),
		ret.cost_of_removal *
			abs(dh.transaction_units) / ret.units))
						removal,
        0  reval_rsv_ret,
	th.transaction_header_id,
	decode (th.transaction_type_code,
		'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
		to_char(null))			code,
                  parent_asset.asset_number   parent_asset_number,
				 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
FROM	fa_lookups		falu,
	fa_retirements		ret,
	fa_asset_history		ah,
	fa_additions		ad,
                  fa_additions                                 parent_asset,
	fa_books			books,
	fa_category_books		cb,
	fa_transaction_headers	th,
	fa_distribution_history	dh,
	gl_code_combinations	dhcc,
        fa_mass_retirements             fmr
WHERE	fmr.mass_retirement_id = :P_MASS_RETIREMENT_ID1
AND           th.mass_transaction_id = fmr.mass_retirement_id
AND	th.book_type_code		 =  fmr.book_type_code		
AND	th.transaction_type_code || ''	in
		('FULL RETIREMENT',
		'PARTIAL RETIREMENT',
		'REINSTATEMENT')				
AND	th.transaction_key		= 'R'
AND          ad.parent_asset_id                      = parent_asset.asset_id(+)
AND	ret.book_type_code		= th.book_type_code		
AND	ret.asset_id		= th.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	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	= th.book_type_code		
AND	books.asset_id		= th.asset_id
AND	cb.category_id		= ah.category_id		
AND	cb.book_type_code		= fmr.book_type_code
AND	dh.asset_id = th.asset_id				
AND	(dh.retirement_id = ret.retirement_id		OR
		(th.date_effective between
			dh.date_effective and nvl(dh.date_ineffective,sysdate)
		 and ret.units is null))
AND	dhcc.code_combination_id	= dh.code_combination_id
GROUP BY
	falu.meaning,
	&ACCT_FLEX_BAL_SEG,
	&ACCT_FLEX_COST_SEG,
	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,
                  parent_asset.asset_number,
				  --added
				  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')
ORDER BY 1,2,3,4,5,6