Asset categories with accounts sql

 https://fixedasstets.blogspot.com/2024/11/asset-categories-with-accounts-sql.html

select a.segment1 , a.segment2, b.book_type_code,

(select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.asset_cost_account_ccid) Asset_Cost_Account,

  

  (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.asset_clearing_account_ccid) Asset_clearing_Account,

  

    

  (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.deprn_expense_account_ccid) Depreciation_expense,

  (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.reserve_account_ccid) Accumulated_depreciation,

  

  (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.bonus_expense_account_ccid) Bonus_expense,

  

    (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.bonus_reserve_acct_ccid) bonus_reserve,

  

  

    

    (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.wip_cost_account_ccid) cip_cost,

  

    

    (select  c.segment1||'.'||c.segment2||'.'||c.segment3||'.'||c.segment4||'.'||c.segment5||'.'||c.segment6||'.'||c.segment7

  from gl_code_combinations c where c.code_combination_id = b.wip_clearing_account_ccid) cip_clearing,

  

  d.depreciate_flag, d.deprn_method  , d.life_in_months , d.prorate_convention_code , d.retirement_prorate_convention, d.percent_salvage_value, d.capital_gain_threshold, d.recognize_gain_loss , d.terminal_gain_loss

from  FA_CATEGORIES_VL a, FA_CATEGORY_BOOKS b , FA_CATEGORY_BOOK_DEFAULTS d

where a.category_id = b.category_id

and b.category_id = d.category_id

and b.book_type_code= d.book_type_code

--and b.book_type_code  = ''

--and a.segment1 = 'FURNITURE & FIXTURE'

Amortize Adjustment in Oracle FA EBS R12

https://fixedasstets.blogspot.com/2021/08/amortize-adjustment-in-oracle-fa-ebs-r12.html

When you perform a financial adjustment on an asset such as a cost adjustment, a depreciation method change, or a date placed in service change, the effect that the adjustment will have depends on whether the adjustment is expensed or amortized.

If the transaction is an Expensed Adjustment, the effect of the adjustment will be from the Date Placed In Service (DPIS) of the asset.  Oracle Assets recalculates depreciation using the new information starting from the DPIS and derives the expected depreciation. The expected depreciation reserve will be compared with the existing depreciation reserve of the asset and any excess or deficit amount will be accounted in the period in which the adjustment is done.

If you want the adjustment to be effective from a specific period and not from the DPIS, the transaction needs to be an Amortized Adjustment.  For amortized adjustments, the effect of the adjustment will be from the period in which the amortization start date falls and the accumulated depreciation prior to the amortization start date will not be re-validated.  When an amortized adjustment is performed, Oracle Assets spreads the adjustment amount over the remaining life or remaining capacity of the asset. 

Note that once an asset has an amortized adjustment, it is no longer possible to perform an expensed adjustment on that asset.

How do you perform an Expensed or an Amortized adjustment?

While performing the transaction, whether it is expensed or amortized depends on these selections:

Here is an example of how expensed and amortized adjustments work:

Let's take an asset that has these parameters:
Cost = 12000
DPIS = 01-JAN-2016
Depreciation method and life = STL for 12 months
In the Aug-16 period, the life is changed to 15 months.

If the adjustment is expensed:

The depreciation, with an asset life of 12 months, would be 12000 / 12 = 1000 per month.
The depreciation, with an asset life of 15 months, would be 12000 / 15 = 800 per month.
Until the Aug-16 period, the asset was depreciating based on a life of 12 months.  The existing depreciation reserve of the asset is 1000 * 7 months = 7000.
Based on the new life, the accumulated depreciation should be 800 * 7 months = 5600.
The depreciation catch-up in Aug-16 is 7000 - 5600 = (1400).
From the Aug-16 period onwards, the monthly depreciation for the asset will 800.

Now, if that adjustment is amortized:

The depreciation, with an asset life of 12 months, would be 12000 / 12 = 1000 per month.
The depreciation reserve amount charged up to Aug-16 is 1000 * 7 months = 7000.
So, the net book value of the asset as of Aug-16 is 12000 - 7000 = 5000.
The remaining life of the asset as of the Aug-16 period is:  Total life – life already completed = 15 months – 7 months = 8 months.
From the Aug-16 period onwards, the monthly depreciation for the asset will be Net Book Value / Remaining Life = 5000 / 8 months = 625.

Asset cost	12000
DPIS	12-Jan-16
Life in months	12
In Aug-16 (Life in months)	15
Method	STL
If Depreciation is expensed
Period	Depreciation Amount	Adjustment
16-Jan	1000
16-Feb	1000
16-Mar	1000
16-Apr	1000
16-May	1000
16-Jun	1000
16-Jul	1000
16-Aug	800	-1400
If Depreciation is Amortized
Period	Depreciation Amount	Adjustment
16-Jan	1000
16-Feb	1000
16-Mar	1000
16-Apr	1000
16-May	1000
16-Jun	1000
16-Jul	1000
16-Aug	625
Total Asset Cost	12000
Total Depreciation	7000
NBV	5000
Remaining Life (15-7)	8
New Depreciation from Aug-16	625

----------------------------------------------------------------------------------------------------------------------------

Another example of expensed and amortized adjustments:

Asset cost	13500
DPIS	17-Sep-18
Life in months	12
In NOV-18 Cost changed to	15000
Method	STL
If Depreciation is expensed
Period	Depreciation Amount	Adjustment
18-Nov	1250	125
18-Oct	1125
If Depreciation in amortized
Period	Depreciation Amount	Adjustment
18-Nov	1261.36
18-Oct	1125
Asset Cost in OCT-18	13500
Accumulated Depreciation in OCT-18	1125
NBV in OCT-18 (Asset Cost - Accumulated Dep)	12375
New Cost in NOV-18	15000
Adjustment in Cost (New Cost in NOV-18 15000 - Cost in OCT-18 13500)	1500
New NBV in NOV-18 (NBV of OCT-18 12375 + Adjustment in Cost 1500)	13875
Life Remaining in Months (12-1)	11
New Depreciation from NOV-18	1261.363636

FA Asset Retirement Entries - Oracle EBS R12

https://fixedasstets.blogspot.com/2019/03/fa-asset-retirement-entries-oracle-ebs.html

Asset Retirement
Proceeds of Sale Clearing Account	DR	Account Code Combination is derived from Book Controls
Accumulated Depreciation	DR	Account Code Combination is derived from Asset Category Setup
Gain/Loss on Retirement	DR/CR	Account Code Combination is derived from Book Controls
Asset Cost Retired	CR	Account Code Combination is derived from Asset Category Setup
Cost of Removal Clearing Account	CR	Account Code Combination is derived from Book Controls

Create AR Miscellaneous Receipt
Bank A/c	DR
Proceeds of Sale Clearing Account	CR
Create AP Invoice
Cost of Removal Clearing Account	DR
AP Liability A/c	CR
Payment of AP Invoice
AP Liability A/c	DR
Bank	CR

Asset Cost Retired + Cost of Removal – Accumulated Depreciation - Proceeds of Sale = Gain/Loss

If we want to Book Gain and Loss on Proceeds of Sale, Cost of removal and NBV Retired, then the Accounting Entries will be as below:

Asset Retirement
Proceeds of Sale Clearing Account	DR	Account Code Combination is derived from Book Controls
Accumulated Depreciation	DR	Account Code Combination is derived from Asset Category Setup
Cost of Removal	DR	Account Code Combination is derived from Book Controls
Net Book Value Retired	DR	Account Code Combination is derived from Book Controls	Balancing Figure
Asset Cost	CR	Account Code Combination is derived from Asset Category Setup
Proceeds of Sale	CR	Account Code Combination is derived from Book Controls
Cost of Removal Clearing Account	CR	Account Code Combination is derived from Book Controls

--------------------------------------------------------------------------------------------------------------------------

Partial Retirement

Cost	8000
NBV	7833.33
Accumulated Dep	166.67
Life	4 Years
DIS	31-Jul-06
Prorate Date	16-Jul-06
Remaining Life	3 Years & 11 Months
Asset Retirement	Amount
Current Cost	8000
Cost Retired	5000	Calculate Accumulated Depreciation on this Figure (5000/4)/(12) = 104.1667
Proceeds of Sale	3500
Cost of Removal	0
Gain/Loss	1395.83
Asset Cost Retired + Cost of Removal – Accumulated Depreciation - Proceeds of Sale = Gain/Loss
Accounting Entries	Debit	Credit
Asset Cost Retired		5000
Accumulated Depreciation	104.1667
Proceeds of sale	3500
Cost of Removal		0
Gain/Loss	1395.83		Loss on Retirement
Accounting Entries	DR	CR	Account Selection
Proceeds of Sale Clearing	3500		Clearing Account from Book Controls
Accumulated Depreciation	104.1667		Account Code Combination is derived from Asset Category Setup
Net Book Value Retired Loss	7895.8333		Loss Account from Book Controls
Cost of Removal	0		Loss Account from Book Controls
Asset Cost Retired		8000	Account Code Combination is derived from Asset Category Setup
Proceeds of Sale Gain		3500	Loss Account from Book Controls
Cost of Removal Clearing		0	Clearing Account from Book Controls

---------------------------------------------------------------------------------

Full Retirement

Cost	35000
NBV	32666
Accumulated Dep	2333.32
Life	5 Years
DIS	23-Jan-18
Prorate Date	16-Jan-18
Remaining Life	4 Years & 8 Months
Asset Retirement	Amount
Current Cost	35000
Cost Retired	35000
Proceeds of Sale	5000
Cost of Removal	450
Gain/Loss	28116.68
Accounting Entries	Debit	Credit
Asset Cost Retired		35000
Proceeds of Sale Clearing Account	5000
Accumulated Depreciation	2333.32
Cost of Removal Clearing Account		450
Gain/Loss	28116.68		Loss on Retirement
Accounting Entries	DR	CR	Account Selection
Proceeds of Sale Clearing Account	5000		Clearing Account from Book Controls
Accumulated Depreciation	2333.32		Account Code Combination is derived from Asset Category Setup
Cost of Removal Gain	450		Loss Account from Book Controls
Net Book Value Retired Loss	32666.68		Loss Account from Book Controls
Asset Cost Retired		35000	Account Code Combination is derived from Asset Category Setup
Proceeds of Sale Gain		5000	Loss Account from Book Controls
Cost of Removal Clearing Account		450	Clearing Account from Book Controls