FA Asset Reclassification Reconciliation - draft

select
        decode(in_out.asset_type,
                  'CIP', :MEANING_CIP,
                  'CAPITALIZED', :MEANING_CAP,
                  'EXPENSED', :MEANING_EXP,
                  '') D_ASSET_TYPE,
	--&ACCT_FLEX_BAL_SEG COMPANY_CODE,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('company_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMPANY_CODE_DSP1,
	decode (th.date_effective,
			in_out.date_effective,
         decode(in_out.asset_type, 'CIP',to_cb.cip_cost_acct,
					 to_cb.asset_cost_acct),
         decode(in_out.asset_type, 'CIP', from_cb.cip_cost_acct,
					  from_cb.asset_cost_acct))
        ASSET_ACCT,
        --&ACCT_FLEX_COST_SEG COST_CTR,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CTR_DSP1,
	decode(in_out.asset_type, 'CIP', '',	
	       decode (th.date_effective,
			in_out.date_effective, to_cb.deprn_reserve_acct,
					from_cb.deprn_reserve_acct))
                                                                reserve_acct,
	decode (th.date_effective, in_out.date_effective, 'IN', 'OUT')
	IN_OUT_CODE,
	ad.asset_number || ' - ' || ad.description ASSET_NUMBER,
	decode (th.date_effective, in_out.date_effective, 1, -1) *
		sum (cost_adj.adjustment_amount)   COST_ADJ,
	decode (th.date_effective, in_out.date_effective, 1, -1) *
                sum(nvl(res_adj.adjustment_amount,0.00))  RES_ADJ,
        --&CAT_FLEX_ALL_SEG_FR FROM_CATEGORY,
        --&CAT_FLEX_ALL_SEG_TO TO_CATEGORY
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_from_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_FROM_CATEGORY1,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_to_category', 'OFA', 'CAT#', 101, NULL, tcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_TO_CATEGORY1
from	fa_additions			ad,
        gl_code_combinations            dhcc,
	fa_categories			fcat,
	fa_categories			tcat,
	fa_category_books		from_cb,
	fa_category_books		to_cb,
	fa_asset_history		from_ah,
 	fa_asset_history		to_ah,
	fa_asset_history		in_out,
	fa_distribution_history		dh,
	fa_adjustments			cost_adj,
	fa_adjustments			res_adj,
	fa_transaction_headers		th
where
	th.book_type_code 		=  :P_BOOK  and
	th.transaction_type_code 	=  'RECLASS' and
	th.date_effective	between
           :PERIOD1_POD  and
           :PERIOD2_PCD
and
	ad.asset_id 			=  th.asset_id
and
	dh.book_type_code 		=  :DISTRIBUTION_SOURCE_BOOK  and
	dh.asset_id 			=  th.asset_id 
and
	dhcc.code_combination_id	=  dh.code_combination_id
and
	cost_adj.transaction_header_id	=  th.transaction_header_id	and
	cost_adj.book_type_code		=  :P_BOOK  and
	cost_adj.source_type_code	=  'RECLASS'			and
	cost_adj.adjustment_type	in ('COST', 'CIP COST')			and
	cost_adj.debit_credit_flag	=  'CR'				and
	cost_adj.distribution_id	=  dh.distribution_id 
and
res_adj.transaction_header_id (+) = cost_adj.transaction_header_id  and
	res_adj.book_type_code(+)	=  :P_BOOK  and
	res_adj.source_type_code(+)	=  'RECLASS'			and
	res_adj.adjustment_type(+)	=  'RESERVE'			and
	res_adj.debit_credit_flag(+)	=  'DR'				and
	res_adj.distribution_id(+)	=  cost_adj.distribution_id 
and
	to_ah.asset_id			=  th.asset_id			and
	to_ah.date_effective		=  th.date_effective
and
	in_out.asset_id			=  th.asset_id			and
	th.date_effective		in
			(in_out.date_effective, in_out.date_ineffective)
and
	from_ah.asset_id		=  th.asset_id			and
	from_ah.date_ineffective	=  th.date_effective
and
	tcat.category_id 	 	=  to_ah.category_id
and
	fcat.category_id 		=  from_ah.category_id 
and
	to_cb.category_id 		=  to_ah.category_id 		and
	to_cb.book_type_code 		=  :P_BOOK  
and
	from_cb.category_id 		=  from_ah.category_id 		and
	from_cb.book_type_code 	=  :P_BOOK  
group by
        decode(in_out.asset_type,
                  'CIP', :MEANING_CIP,
                  'CAPITALIZED', :MEANING_CAP,
                  'EXPENSED', :MEANING_EXP,
                  ''),
/*	&ACCT_FLEX_BAL_SEG,
        &ACCT_FLEX_COST_SEG,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('company_code_dsp', '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('cost_ctr_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
	decode (th.date_effective,
			in_out.date_effective,
         decode(in_out.asset_type, 'CIP',to_cb.cip_cost_acct,
					 to_cb.asset_cost_acct),
         decode(in_out.asset_type, 'CIP', from_cb.cip_cost_acct,
					  from_cb.asset_cost_acct)),
	decode(in_out.asset_type, 'CIP', '', 
	       decode (th.date_effective,
			in_out.date_effective, to_cb.deprn_reserve_acct,
					from_cb.deprn_reserve_acct)),
	th.date_effective,
	in_out.date_effective,
	in_out.date_ineffective,
        in_out.units,
	ad.asset_number,
	ad.description,
        /*&CAT_FLEX_ALL_SEG_FR,
        &CAT_FLEX_ALL_SEG_TO,*/
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_from_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_to_category', 'OFA', 'CAT#', 101, NULL, tcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
        th.transaction_header_id,
        ad.asset_id
order by
/*	2,
        1,
	lpad (
	      decode (th.date_effective,
			in_out.date_effective,
         decode(in_out.asset_type, 'CIP',to_cb.cip_cost_acct,
					 to_cb.asset_cost_acct),
         decode(in_out.asset_type, 'CIP', from_cb.cip_cost_acct,
					  from_cb.asset_cost_acct)),
              25,0),
	4,
	decode (th.date_effective, in_out.date_effective, 1, 2),
	asset_number*/
2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11