FA Capital Spending - draft

SELECT
	cbd.deprn_method 		ADD_METHOD, 
	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')  	ADD_COMP,
	round(sum(dh.units_assigned *  bk.cost / ah.units),
		:precision)	ADD_COST,
	round(sum(dh.units_assigned * 
	 NVL(mc.cost,nvl(bk_before.cost,0))/ah.units),
		:precision)	ADDB_COST, 
	FA_FASCAPSP_XMLP_PKG.meth_per_add_beforeformula(:METH_ADD_COST, :METH_ADDB_COST) METH_PER_ADD_BEFORE, 
	FA_FASCAPSP_XMLP_PKG.meth_per_bud_beforeformula(:METH_BUD_COST, :METH_BUDB_COST) METH_PER_BUD_BEFORE, 
	FA_FASCAPSP_XMLP_PKG.meth_per_add_budformula(:METH_BUD_COST, :METH_ADD_COST) METH_PER_ADD_BUD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_add_comp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_ADD_COMP, 
	FA_FASCAPSP_XMLP_PKG.per_add_beforeformula(round(sum(dh.units_assigned *  bk.cost / ah.units),   :precision), round(sum(dh.units_assigned *    NVL(mc.cost,nvl(bk_before.cost,0))/ah.units),   :precision)) PER_ADD_BEFORE, 
	FA_FASCAPSP_XMLP_PKG.per_bud_beforeformula(:BUD_COST_SUM, :BUDB_COST_SUM) PER_BUD_BEFORE, 
	FA_FASCAPSP_XMLP_PKG.per_add_budformula(:BUD_COST_SUM, round(sum(dh.units_assigned *  bk.cost / ah.units),   :precision)) PER_ADD_BUD
FROM
	fa_books bk,
	fa_mc_books mc,
	fa_books_bas bk_before,
	fa_asset_history ah,
	fa_distribution_history dh,
	fa_book_controls tax,
	fa_fiscal_year fy, 
	fa_transaction_headers add_th,
	fa_category_book_defaults cbd,
	gl_code_combinations dhcc
WHERE
tax.book_type_code = :P_TAX_BOOK            AND
add_th.book_type_code = tax.book_type_code AND
add_th.transaction_type_code  = 'ADDITION'     AND
ah.asset_id =  add_th.asset_id AND
add_th.date_effective between ah.date_effective AND
nvl(ah.date_ineffective,sysdate) AND
ah.category_id = cbd.category_id    AND
cbd.book_type_code= :P_TAX_BOOK    AND
bk.asset_id = add_th.asset_id AND
bk.book_type_code = tax.book_type_code AND
bk.transaction_header_id_in = add_th.transaction_header_id  AND
bk.date_placed_in_service between fy.start_date and fy.end_date
AND fy.fiscal_year  = :FISCAL_YEAR
AND  fy.fiscal_year_name = tax.fiscal_year_name  AND
dh.asset_id = add_th.asset_id AND
dh.book_type_code= tax.distribution_source_book AND
dh.date_ineffective is null AND
dh.code_combination_id = dhcc.code_combination_id  AND
bk_before.book_type_code(+) = bk.book_type_code  AND
bk_before.asset_id(+)             = bk.asset_id    AND
bk_before.transaction_header_id_in(+) = bk.transaction_header_id_in
AND bk_before.date_placed_in_service(+) <= :P_DPIS
and mc.transaction_header_id_in (+) = DECODE(:mrc_sob_type_code,'R',bk_before.transaction_header_id_in,null)
and mc.set_of_books_id(+) = :set_of_books_id
group by
	cbd.deprn_method, 
	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')
--Added during DT Fix
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_add_comp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
--End of DT Fix
order by
/*	cbd.deprn_method,
	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')*/
 1, 2, 3, 4