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 |
