PLSQL Query for Assets Revaluation - Oracle EBS R12

https://fixedasstets.blogspot.com/2018/07/plsql-query-for-assets-revaluation.html

PLSQL Query for Assets Revaluation

SELECT
/* th.mass_transaction_id,
 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.TAG_NUMBER,
ad.description,
       bk.DATE_PLACED_IN_SERVICE  ,
to_char(th.TRANSACTION_DATE_ENTERED,'DD-MON-YYYY') Revaluation_Date,
       ak.segment1 AssetKey,
       --ad.DESCRIPTION ,
       --ad.TAG_NUMBER,
       ad.asset_category_id Category_Id,
       cat.SEGMENT1||'-'||cat.SEGMENT2||'-'||cat.SEGMENT3 Category,
       fl.segment1||'-'||fl.segment2||'-'||fl.segment3 Location,
     --  &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
FROM fa_transaction_headers th,
     fa_additions ad,
     fa_asset_keywords ak,
     fa_books bk,
     fa_books bknew,
     fa_locations fl,
     fa_categories_b cat,
     fa_deprn_summary ds,
     fa_deprn_periods dp,
     fa_distribution_history dh,
     fa_adjustments adj
 WHERE  1  =  1

/* and  (th.mass_transaction_id = &P_MASS_REVAL_ID or
             (th.mass_transaction_id is null and
              th.mass_reference_id = &P_MASS_REVAL_ID))*/
and   th.transaction_type_code = 'REVALUATION'
and   th.book_type_code =  :P_BOOK_TYPE
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 ad.ASSET_KEY_CCID = ak.CODE_COMBINATION_ID
and dh.asset_id =   ad.asset_id
and  dh.date_ineffective is null
and fl.location_id=dh.location_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 dh.BOOK_TYPE_CODE = :P_BOOK_TYPE
and   ds.period_counter =
        (select max(ds2.period_counter)
           from fa_deprn_summary ds2
          where ds2.asset_id = th.asset_id
            and ds2.book_type_code = :P_BOOK_TYPE
            and ds2.period_counter <= dp.period_counter - 1)
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
and cat.segment1 like  nvl(:p_major_cat,cat.segment1)
and cat.segment2 like  nvl(:p_minor_cat,cat.segment2)
and ak.segment1 like nvl(:p_asset_key,ak.segment1)
group by
      th.mass_transaction_id,
       ad.asset_id,
       ad.asset_number,
         bk.DATE_PLACED_IN_SERVICE  ,
ad.description,
         ak.segment1 ,
         cat.SEGMENT1,cat.SEGMENT2,cat.SEGMENT3,
          fl.segment1, fl.segment2, fl.segment3,
       --ad.DESCRIPTION ,
       ad.asset_category_id,
     --  &CAT_FLEX_ALL_SEG,
       bk.life_in_months,
       bknew.life_in_months,
       bk.cost,
       bknew.cost,
       bknew.reval_ceiling,
th.TRANSACTION_DATE_ENTERED,
       ds.deprn_reserve,
       ds.reval_reserve,
       th.mass_reference_id,
       th.transaction_header_id,
ad.TAG_NUMBER,
       th.asset_id,bk.reval_ceiling
order by 1