FA Mass Revaluation Review - draft

SELECT th.mass_reference_id Reval_Id,
       th.transaction_header_id  TH_ID, 
       th.asset_id Asset_Id,
       lpad(ad.asset_number,  15, '0')  Asset_ord,
       ad.asset_number Asset_Number,
       ad.asset_category_id Category_Id,
      -- null Category,
       &CAT_FLEX_ALL_SEG Category, 
	   bk.cost Old_Cost,
       bknew.cost New_Cost,
       bk.life_in_months Old_Life,
       bknew.life_in_months New_Life,
       bk.reval_ceiling Reval_Ceiling,
       ds.deprn_reserve +
       sum(decode(adj.transaction_header_id,th.transaction_header_id,0,
                     decode(adj.adjustment_type,
                    'RESERVE',(decode(adj.debit_credit_flag,'CR',1,-1) * adj.adjustment_amount),
                    'EXPENSE',(decode(adj.debit_credit_flag,'DR',1,-1) * adj.adjustment_amount),
                    0))) old_deprn_resv,
       ds.deprn_reserve +
         sum(decode(adj.adjustment_type,
                    'RESERVE',(decode(adj.debit_credit_flag,'CR',1,-1) * adj.adjustment_amount),
                    'EXPENSE',(decode(adj.debit_credit_flag,'DR',1,-1) * adj.adjustment_amount),
                    0)) new_deprn_resv,
       ds.reval_reserve  +
         sum(decode(adj.transaction_header_id,th.transaction_header_id,0,
                    decode(adj.adjustment_type,
                   'REVAL RESERVE',(decode(adj.debit_credit_flag,'CR',1,-1) * adj.adjustment_amount),                    0))) old_reval_resv,
       ds.reval_reserve +
          sum(decode(adj.adjustment_type,
                 'REVAL RESERVE',(decode(adj.debit_credit_flag,'CR',1,-1) * adj.adjustment_amount),
                 0)) new_reval_resv, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, CAT.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY, 
	&frrf FRRF, 
	FA_FASRVRVW_XMLP_PKG.mfrrformula(:MFRR) MFRR, 
	FA_FASRVRVW_XMLP_PKG.reval_pctformula(:REVAL_PCT) REVAL_PCT, 
	&lef LEF, 
	&lec LEC, 
	&d_found D_FOUND, 
	FA_FASRVRVW_XMLP_PKG.d_old_lifeformula(bk.life_in_months) D_OLD_LIFE, 
	FA_FASRVRVW_XMLP_PKG.d_new_lifeformula(bknew.life_in_months) D_NEW_LIFE, 
	FA_FASRVRVW_XMLP_PKG.new_dep_rsvformula() NEW_DEP_RSV, 
	&NEW_REVAL_RSV NEW_REVAL_RSV, 
	FA_FASRVRVW_XMLP_PKG.adj_dep_rsvformula(th.transaction_header_id, th.asset_id, :NEW_DEP_RSV) ADJ_DEP_RSV, 
    FA_FASRVRVW_XMLP_PKG.adj_reval_rsvformula(th.transaction_header_id, th.asset_id, :NEW_REVAL_RSV) ADJ_REVAL_RSV, 
    &ass_frrf ASS_FRRF, 
	&ass_lef ASS_LEF, 
	FA_FASRVRVW_XMLP_PKG.ass_reval_pctformula(:ASS_REVAL_PCT) ASS_REVAL_PCT, 
	&ass_lec ASS_LEC, 
	FA_FASRVRVW_XMLP_PKG.ass_mfrrformula(:ASS_MFRR) ASS_MFRR, 
	&ASS_FOUND ASS_FOUND,
	FA_FASRVRVW_XMLP_PKG.OLD_DEP_RSV_p OLD_DEP_RSV,
	FA_FASRVRVW_XMLP_PKG.OLD_REVAL_RSV_p OLD_REVAL_RSV
FROM fa_transaction_headers th,
     fa_additions ad,
     fa_books bk,
     fa_books bknew,
     fa_categories cat,
     fa_deprn_summary ds,
     fa_deprn_periods dp,
     fa_adjustments adj
 WHERE (th.mass_transaction_id = :P_MASS_REVAL_ID1 or
             (th.mass_transaction_id is null and
              th.mass_reference_id = :P_MASS_REVAL_ID1))
and   th.transaction_type_code = 'REVALUATION'
and   th.book_type_code = :BOOK
and   bk.transaction_header_id_out = th.transaction_header_id
and   bknew.transaction_header_id_in = th.transaction_header_id
and   ad.asset_id = th.asset_id
and   th.date_effective between dp.period_open_date and nvl(dp.period_close_date,sysdate)
and   dp.book_type_code = th.book_type_code
and   ds.asset_id = th.asset_id
and   ds.book_type_code = th.book_type_code
and   ( ds.period_counter = dp.period_counter - 1 or
           ds.period_counter = (select max(ds1.period_counter) from fa_deprn_summary ds1 where
                                            ds1.asset_id = th.asset_id and ds1.book_type_code =  :BOOK))
and   adj.asset_id = th.asset_id
and   adj.book_type_code = th.book_type_code
and   adj.period_counter_created = dp.period_counter
and   ad.asset_category_id = cat.category_id
group by
       ad.asset_id,
       ad.asset_number,
       ad.asset_category_id,
       &CAT_FLEX_ALL_SEG,
       bk.life_in_months,
       bknew.life_in_months,
       bk.cost,
       bknew.cost,
       bknew.reval_ceiling,
       ds.deprn_reserve,
       ds.reval_reserve,
       th.mass_reference_id,
       th.transaction_header_id,
       th.asset_id,bk.reval_ceiling ,
-- added by Atul
	  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, CAT.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , 
	&frrf , 
	FA_FASRVRVW_XMLP_PKG.mfrrformula(:MFRR) , 
	FA_FASRVRVW_XMLP_PKG.reval_pctformula(:REVAL_PCT) , 
	&lef , 
	&lec , 
	&d_found , 
	FA_FASRVRVW_XMLP_PKG.d_old_lifeformula(bk.life_in_months) , 
	FA_FASRVRVW_XMLP_PKG.d_new_lifeformula(bknew.life_in_months) , 
	FA_FASRVRVW_XMLP_PKG.new_dep_rsvformula() , 
	&NEW_REVAL_RSV , 
	FA_FASRVRVW_XMLP_PKG.adj_dep_rsvformula(th.transaction_header_id, th.asset_id, :NEW_DEP_RSV) , 
	FA_FASRVRVW_XMLP_PKG.adj_reval_rsvformula(th.transaction_header_id, th.asset_id, :NEW_REVAL_RSV) , 
    &ass_frrf , 
    &ass_lef , 
	FA_FASRVRVW_XMLP_PKG.ass_reval_pctformula(:ASS_REVAL_PCT) , 
	&ass_lec , 
	FA_FASRVRVW_XMLP_PKG.ass_mfrrformula(:ASS_MFRR) , 
	&ASS_FOUND ,
	FA_FASRVRVW_XMLP_PKG.OLD_DEP_RSV_p ,
	FA_FASRVRVW_XMLP_PKG.OLD_REVAL_RSV_p
order by D_CATEGORY, old_deprn_resv, new_deprn_resv, old_reval_resv, new_reval_resv, Asset_ord, Reval_Id, Asset_Number, Asset_Id, Old_Life, New_Life, Old_Cost, New_Cost, Reval_Ceiling, TH_ID