Oracle FA Period Closing Procedure

https://fixedasstets.blogspot.com/2018/12/oracle-fa-period-closing-procedure.html

1. Create all transactions (mass additions, retirements etc) before running the Depreciation Program. Check for the Mass Additions with the Status of “NEW”.

2. Before running the depreciation, Project the depreciation by running the Projections. Select the projection calendar, number of periods, Starting period, the corporate book and click on the ‘Run’ button. Total Depreciation for the period will be shown as the output in the concurrent request output.

3. Run the depreciation program without closing the period.

4. Module: Fixed Assets.

5. Navigation: Depreciation > Depreciation. Select the corporate book and the period. Do Not Check the Check Box‘ Close Period ’

6. Verify The Journal Entry Reserve Report for the calculation of Depreciation and whether depreciation is calculated for all the assets. After checking the results go to next step.

7. Now run the Depreciation program with the check box ‘Close Period’ Checked.

8. Transfer information from fixed assets to General Ledger.

(Module: Fixed Assets. Navigation: Submit RequestCreate Journal Entries in Fixed Asset. Choose the Corporate Book and period for parameters as shown below.

9. This process creates the Journal Entries Automatically in the

General Ledger. Journal import from general Ledger need not be run both for Primary as well as Reporting Set of Books. 

10. Verify the Unposted Entries in the journal Entry Screen. 

11. Post the journal Entries.

Opening / Closing the Period in Fixed Assets:
1. If the Depreciation is run with the Check Box ‘Close Period’ Checked, the period will be closed and the next period will be opened automatically.

Note: In Fixed Assets, once a period is closed, it cannot be reopened.

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.

Depreciation Calculation Formula's for STL Method - Oracle EBS R12

https://fixedasstets.blogspot.com/2018/06/depreciation-calculation-formulas.html

How you can calculate Depreciation in STL Method

Book Name	TEST
Fiscal Year	Asset Fiscal Year
Depreciation Calendar	Depr. Cal.
Depreciation Calendar Periods	12
Prorate Calendar	Depr. Cal.
Number of Prorate Period in Fiscal year	12
Depreciation Allocation	Evenly

Suppose Asset:

Date Placed in Service	22-OCT-15
Prorate Date	01-OCT-15
Deprciation Method	STL
Depreciable Basis	COST
Depreciation Start Date	22-OCT-15
Cost	524413.9
Salvage Value	0
Recoverable Cost (Cost -Salvage Value)	524413.9
Period JUL-18 (1 of 12) Period day range: 01-JUL-2018 - 31-JUL-2018 Fiscal Year: 01-JUL-2018 - 30-JUN-2019

Element	Fomula	Value
Life		36
Annual Depreciation Rate	12 / Life 12/36	0.33
Current adjusted Cost		524413.9
Rate Adjustment factor		1
dpr_formula_factor		1
Current Fiscal Year Fraction	((Current Fiscal year end date + 1 - Current Fiscal year Starrt date)) /(Current Fiscal year end date + 1 - Current Fiscal year Starrt date))) ,(30-JUN-19 + 1 - 01-JUL-18) /(30-JUN-19 + 1 - 01-JUL-18))	1
Annualized Depreciation Amount For Fiscal Year	(Current Adjusted Cost/Rate Adjustment factor)*Annual Depreciation rate*NVL(formula factor,1) 524413.9/1*.3333333333333333333333333333333333333333*1	174804.63
Actual Depreciation for current Fiscal year	Annualized Depreciation Amount For Fiscal Year * (Current Fiscal Year Fraction/Adjustment first year fraction); 174804.63 * (1/1);	174804.63
Current Period Fraction	1.0 / Number of Depreciation Period in Fiscal year 1.0 /12	0.08333333
Current Period Depreciation Amount	Annualized Depreciation Amount For Fiscal Year*Current Period Fraction 174804.63*.0833333333333333333333333333333333333333	14567.05
Year to Date depreciaiton	NVL(Previous YTD ,0) + Current Period depreciation 0 + 14567.05	14567.05
Depreciation Reserve	NVL(Depreciation reserve for previous fiscal year,0)+ NVL(Year to Date depreciation,0) 480712.73+ 14567.05	495279.78

Period AUG-18 (2 of 12)

Period day range:	01-AUG-2018 - 31-AUG-2018
Fiscal Year:	01-JUL-2018 - 30-JUN-2019

Element	Fomula	Value
Life		36
Annual Depreciation Rate	12 / Life 12/36	0.33
Current adjusted Cost		524413.9
Rate Adjustment factor		1
dpr_formula_factor		1
Current Fiscal Year Fraction	((Current Fiscal year end date + 1 - Current Fiscal year Starrt date)) /(Current Fiscal year end date + 1 - Current Fiscal year Starrt date)))  ,(30-JUN-19 + 1 - 01-JUL-18) /(30-JUN-19 + 1 - 01-JUL-18))	1
Annualized Depreciation Amount For Fiscal Year	(Current Adjusted Cost/Rate Adjustment factor)*Annual Depreciation rate*NVL(formula factor,1) 524413.9/1*.3333333333333333333333333333333333333333*1	174804.63
Actual Depreciation for current Fiscal year	Annualized Depreciation Amount For Fiscal Year * (Current Fiscal Year Fraction/Adjustment first year fraction); 174804.63 * (1/1);	174804.63
Current Period Fraction	1.0 / Number of Depreciation Period in Fiscal year 1.0 /12	0.08333333
Current Period Depreciation Amount	Annualized Depreciation Amount For Fiscal Year*Current Period Fraction 174804.63*.0833333333333333333333333333333333333333	14567.05
Year to Date depreciaiton	NVL(Previous YTD ,0) + Current Period depreciation 14567.05 + 14567.05	29134.1
Depreciation Reserve	NVL(Depreciation reserve for previous fiscal year,0)+ NVL(Year to Date depreciation,0) 480712.73+ 29134.1	509846.83