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

Oracle Fixed Assets Revaluation - Oracle EBS R12

https://fixedasstets.blogspot.com/2018/07/oracle-fixed-assets-revaluation-oracle.html


Please Copy Paste the Below URL in your Browser

https://www.youtube.com/watch?v=BAAnI_Md03c

Reval Amortization journal line is missing from the Depreciation journal when Depreciation and Create Accounting are run after upgrade to R12.1.3 - Oracle EBS R12

https://fixedasstets.blogspot.com/2018/07/error-0-subledger-journal-entry-does.html

When attempting to run Create Accounting -  Assets, the following error occurs:

Error 0 - Subledger Journal Entry Does Not Balance In The Entered Currency Please Verify Entered Amount 

Actual Revaluation Entry is:

	ACCUMULATED DEP		135,320.60
REVALUATION		7,162.21
	DEPRECIATION EXP		7,162.21
DEPRECIATION EXP		135,320.60

Cause:

Reval Amortization journal line is missing from the Depreciation journal when Depreciation and Create Accounting are run after upgrade to R12.1.3
This causes the unbalanced journal as there is no contra-entry to the reval reserve posting generated.
The reval amortization side of the entry is not generated because there is a condition in the Journal Line Definition which looks for fa_book_controls.sorp_enabled_flag to be either Y or N (and not a null value).
In the customer case, this flag is null and hence no reval amortization journal line is generated.

In this case, for an asset book that was set up and has been running for a long time on R11i, when the book was defined, there was no such field as fa_book_controls.sorp_enabled_flag.
Then the 'SORP' solution (a UK Localization functionality) was released by Oracle Development and introduced fa_book_controls.sorp_enabled_flag.
For existing customers who do not use 'SORP' (ie. those outside of the UK), this flag remains null.   On 11i this is not a problem, but on R12, the condition mentioned in the previous update relies on it being populated.

This was determined by review of the example problem asset traces uploaded to the SR which shows that the customer has fa_book_controls.sorp_enabled_flag = null.

Solution

1.  Navigate to the Book Controls form and query the asset book.
2.  Move to the 'Accounting Rules' tab.
3.  Find that the UK Local Authority Accounting option is unchecked.
4.  Check this checkbox, then uncheck it again and save.  The form is thus saved with a blank value.
5.  Re-run Create Accounting - Assets which should now complete with no unbalanced journals.