Accumulated depreciation or YTD depreciation projection as of specific period end.

Oracle by default does not maintain future depreciation information (Accumulated or YTD) as depreciation could change as per cost adjustment, addition or asset retirement.

Often there will be requirement to write a function or package that returns YTD depreciation or Accumulated depreciation as of year-end or specific period(In this example it is Dec 2017) for example Accumulated depreciation as of Dec 2017, YTD depreciation as of Dec 2017 and remaining depreciation period for asset after Dec 2017.

Over here I had requirement to get Asset depreciation as of Dec 2017 for Workday Financial Conversion.

Following is the function that returns depreciation as of certain period.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122

CREATE OR REPLACE FUNCTION APPS.xxcb_get_asset_accum_deprn( v_asset_num NUMBER, v_deprn_type varchar2, l_future_period number) RETURN VARCHAR2 AS -- l_rem_deprn_period number; l_asset_id number; l_current_deprn_period number; l_no_of_deprn_period number; -- to be calculated l_total_depreciation number; l_monthly_depreciation number; l_ytd_depreciation number; l_accum_depreciation number; l_total_sum_depreciation number; l_asset_cost number; -- BEGIN -- --Get asset id select asset_id into l_asset_id from fa_additions_b where asset_number = v_asset_num; begin --1) Get remaining period of the asset --2) Get Current Period of Asset Depeciation --3) Get asset depreciation of current period or as of Jun-06 --4) Get accumulated depreciation as of current period --5) Get YTD Depreciation of Asset --6) Get Asset Cost select DECODE (books.period_counter_fully_retired,NULL, GREATEST( NVL (books.life_in_months, 0)- round(MONTHS_BETWEEN (nvl(fdp.PERIOD_CLOSE_DATE,fdp.PERIOD_OPEN_DATE),books.prorate_date)),0),0) rem_deprn_period, substr(fdp.period_name,1,2) Current_Period_Of_Asset, decode(gl_code.segment1, 10,dep_sum.SYSTEM_DEPRN_AMOUNT,(select dep_mc_sum.SYSTEM_DEPRN_AMOUNT from fa_mc_deprn_summary dep_mc_sum where dep_mc_sum.asset_id = fab.asset_id and dep_mc_sum.set_of_books_id = 2025 and dep_mc_sum.period_counter = dep_sum.period_counter)) deprn_current_period, decode(gl_code.segment1, 10,dep_sum.deprn_reserve,(select dep_mc_sum.deprn_reserve from fa_mc_deprn_summary dep_mc_sum where dep_mc_sum.asset_id = fab.asset_id and dep_mc_sum.set_of_books_id = 2025 and dep_mc_sum.period_counter = dep_sum.period_counter)) accum_deprn_current_period, nvl(decode(gl_code.segment1, 10,(select dep_sum_ytd.ytd_deprn from fa_deprn_summary dep_sum_ytd where dep_sum_ytd.asset_id = fab.asset_id and dep_sum_ytd.deprn_run_date = (select max(deprn_run_date) from fa_deprn_summary a,fa_deprn_periods b where a.asset_id = fab.asset_id and a.period_counter = b.period_counter and b.fiscal_year = '2017')),(select dep_mc_sum.ytd_deprn from fa_mc_deprn_summary dep_mc_sum where dep_mc_sum.asset_id = fab.asset_id and dep_mc_sum.set_of_books_id = 2025 and dep_mc_sum.deprn_run_date = (select max(deprn_run_date) from fa_mc_deprn_summary a,fa_deprn_periods b where a.asset_id = fab.asset_id and a.set_of_books_id = 2025 and a.period_counter = b.period_counter and b.fiscal_year = '2017'))),0) Year_To_Date_Depreciation, decode( gl_code.segment1,10,books.cost,(select mc_book.cost from fa_mc_books mc_book where mc_book.asset_id = fab.asset_id and mc_book.date_ineffective is null and mc_book.transaction_header_id_out is null and set_of_books_id = 2025)) Acquisition_Cost into l_rem_deprn_period, l_current_deprn_period, l_monthly_depreciation, l_accum_depreciation, l_ytd_depreciation, l_asset_cost from fa_additions_b fab, fa_additions_tl fat, fa_distribution_history fdh, gl_code_combinations_kfv gl_code, fa_books books, fa_categories_b fcb, fa_category_book_defaults fcbd, fa_deprn_summary dep_sum, fa_deprn_periods fdp, fa_locations loc where fab.asset_id = fdh.asset_id and fdh.code_combination_id = gl_code.code_combination_id and fab.asset_id = fat.asset_id and fab.asset_id = books.asset_id and books.date_ineffective is null and books.transaction_header_id_out is null and books.book_type_code = 'US_CB CORP' and fab.asset_category_id = fcb.category_id and fcbd.category_id = fcb.category_id and fcbd.book_type_code = 'US_CB CORP' and fcbd.end_dpis is null and books.asset_id = dep_sum.asset_id and dep_sum.period_counter=fdp.period_counter --and fdp.period_name = '06-2017' and dep_sum.deprn_run_date = (select max(deprn_run_date) from fa_deprn_summary where asset_id = fab.asset_id and book_type_code = 'US_CB CORP') and fdp.book_type_code = 'US_CB CORP' and fdh.location_id = loc.location_id and fdh.date_ineffective is null and fab.asset_type = 'CAPITALIZED' and fab.asset_id=l_asset_id; -- end; -- No of period depreciation to be calculated l_no_of_deprn_period:= l_future_period -l_current_deprn_period; -- Calculate depreciation amount from current period to end of Dec-2017 if (l_rem_deprn_period < l_no_of_deprn_period) then -- l_total_depreciation := l_monthly_depreciation * l_rem_deprn_period; -- else -- l_total_depreciation := l_monthly_depreciation * l_no_of_deprn_period; -- end if; -- if v_deprn_type = 'ACCUM' then -- Calculate total accumulated depreciation until Dec-2017 l_total_sum_depreciation := l_accum_depreciation + l_total_depreciation; -- if (l_total_sum_depreciation > l_asset_cost) then -- l_total_sum_depreciation := l_asset_cost; -- end if; -- elsif v_deprn_type = 'YTD' then -- Calculate total YTD depreciation until Dec-2017 l_total_sum_depreciation := l_ytd_depreciation + l_total_depreciation; -- end if; -- RETURN l_total_sum_depreciation; -- END; /

Three parameters that this function accepts are

• Asset Number: Asset Number of the asset for which we need to calculate YTD and accumulated depreciation.
• Depreciation Type: Single function is being used to get YTD as well as accumulated depreciation. Two possible values for this parameter are “YTD” and “ACCUM”
• Future Period: We need to pass future period number. For example 12 for Dec

Following is the code that returns remaining period post 12-2017.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

-- Query to get remaining period as of 31-Dec-2017 select greatest(DECODE (books.period_counter_fully_retired,NULL, GREATEST( NVL (books.life_in_months, 0)- round(MONTHS_BETWEEN (nvl(fdp.PERIOD_CLOSE_DATE,fdp.PERIOD_OPEN_DATE),books.prorate_date)),0),0)-(12-(substr(fdp.period_name,1,2))),0) result from fa_additions_b fab, fa_additions_tl fat, fa_distribution_history fdh, gl_code_combinations_kfv gl_code, fa_books books, fa_categories_b fcb, fa_category_book_defaults fcbd, fa_deprn_summary dep_sum, fa_deprn_periods fdp, fa_locations loc where fab.asset_id = fdh.asset_id and fdh.code_combination_id = gl_code.code_combination_id and fab.asset_id = fat.asset_id and fab.asset_id = books.asset_id and books.date_ineffective is null and books.transaction_header_id_out is null and books.book_type_code = 'US_CB CORP' and fab.asset_category_id = fcb.category_id and fcbd.category_id = fcb.category_id and fcbd.book_type_code = 'US_CB CORP' and fcbd.end_dpis is null and books.asset_id = dep_sum.asset_id and dep_sum.period_counter=fdp.period_counter --and fdp.period_name = '06-2017' and dep_sum.deprn_run_date = (select max(deprn_run_date) from fa_deprn_summary where asset_id = fab.asset_id and book_type_code = 'US_CB CORP') and fdp.book_type_code = 'US_CB CORP' and fdh.location_id = loc.location_id and fdh.date_ineffective is null and fab.asset_type = 'CAPITALIZED' and fab.asset_id=2518;

Following are sample script for testing this function

1 2 3 4

select xxcb_get_asset_accum_deprn(1492,'ACCUM',12) ACCUM_deprn_as_of_Dec_2017 from dual; --1492, 1729 select xxcb_get_asset_accum_deprn(1492,'YTD',12) YTD_deprn_as_of_Dec_2017 from dual;

What are the tables involved in the Transfer to GL and GL posting?

Subledger Tables 
XLA_AE_HEADERS 
XLA_AE_LINES 
XLA_DISTRIBUTION_LINKS 

a) Transfer Journal Entries to GL (XLAGLTRN) process takes the subledger journals and inserts records into the Interface Tables 

Interface Tables 
GL_INTERFACE / XLA_GLT_<groupid> 

b) Journal Import (GLLEZL) then reads from the interface table and creates records in the GL Tables 

GL Tables 
GL_JE_BATCHES 
GL_JE_HEADERS 
GL_JE_LINES 
GL_IMPORT_REFERENCES 

c) GL Posting process then posts to the GL_BALANCES table. 

Ref: Doc ID 876190.1

Prorate Convention in Fixed Assets

Prorate Conventions in Fixed Assets

Before we discuss depreciation it is worth to discuss prorate conventions as there are wide variety of prorate conventions and each of those prorate conventions has implication on depreciation calculation.

Definition:

The prorate convention determines how much depreciation to take in the first and last year of an asset’s life based on when you place the asset in service.

Since assets can be acquired at any time in a given period, we must have a convention for treating each instance. Should we count a whole period’s depreciation if the asset is added at the end of the month? Should we take a half period’s depreciation if an asset is added past a certain point in the period? The date placed in service is very important in every case.

Thus, together with the date placed in service, the prorate convention helps to determine the prorate date. It is this prorates date that actually determines the proper amount of depreciation to take in the first and last years of asset life.

TIP: Your convention must include every date in your fiscal year; otherwise, Oracle Assets cannot calculate depreciation properly.

Prorate Conventions:

You can define prorate conventions with prorate dates that have a variety of effects. You can also use the same convention as your prorate and retirement conventions. Three of the more common prorate conventions are:

• ACTUAL MONTHS CONVENTION, OR CURRENT MONTH: Takes one month of depreciation for the month you acquire the asset. Does not take depreciation in the month you retire the asset. For a retirement convention, does not take depreciation in the month you retire the asset.

• FOLLOWING MONTH CONVENTION: Does not take any depreciation in the month you acquire the asset. Takes one month of depreciation in the last month of asset life. For a retirement convention, take one month of depreciation for the month you retire the asset.

• MID-MONTH CONVENTION: Takes half a month of depreciation in the month you acquire the asset. Takes half a month of depreciation in the last month of asset life. For a retirement convention, take half a month of depreciation in the month you retire the asset. An asset is considered retired on the midpoint of the retirement month.

Additionally, Oracle Assets provides a mid-quarterly prorate convention:

• MID-QUARTER CONVENTION: Takes half a quarter of depreciation in the quarter you acquire the asset. Takes half a quarter of depreciation in the last quarter of asset life. For a retirement convention, take half a quarter of asset life. For a retirement, convention, take half a quarter of depreciation in the quarter you retire the asset. An asset is considered retired on the midpoint of the retirement quarter.

Examples of different Half-Year prorate conventions:

• HALF-YEAR CONVENTION: Takes half a year of depreciation in the year you acquire the asset and in the last year of life. For a retirement convention, take half a year of depreciation in the year you retire the asset. The asset is considered retired on the midpoint of the year retired.

• STANDARD MODIFIED HALF-YEAR CONVENTION: Takes a full year of depreciation in the year you acquire the asset if you acquire it in the first half of the year. Does not take any depreciation if you acquire the asset in the second half of the year. For a retirement convention, take a full year of depreciation in the year you retire the asset if you retire it in the second half of the year. Does not take any depreciation if you retire the asset in the first half of the year.

• ALTERNATE MODIFIED HALF-YEAR CONVENTION: For a retirement convention, take one fourth of a year of depreciation in the year you retire the asset.

• ACRS HALF-YEAR CONVENTION: Takes a full year of depreciation in the year you acquire the asset. Does not take any depreciation in the last year of life. For a retirement convention, does not take any depreciation for the year you retire the asset.

Prorate Conventions: Examples

Month-to-Month

Month-to-Month is the most common prorate convention and calendar setup. The depreciation calendar and prorate calendar are exactly the same: a standard 12 month calendar with each period corresponding to a different month.

1) Set up your depreciation calendar first.

Simply navigate to the Asset Calendars form and enter periods for all relevant years.

2) Set up your prorate calendar next.

A Month-to-Month prorate calendar has exactly the same 12 periods, so you can enter the same calendar name in both fields in the Book Controls form.

3) Finally, choose your prorate convention.

Let’s choose a Current Month Prorate Convention. Simply navigate to the Default Depreciation Rules zone of the Asset Categories form. You can also specify the prorate convention in the Books window during the detail additions process.

Thus, an asset with a date placed in service anytime at all in January will have a full month’s depreciation for that period. In the period retired, or the last period of the asset’s life, the asset will not have any depreciation taken.

TIP: Select the “Depreciate When Date Placed In Service” check box if you want to start taking depreciation in the accounting period that corresponds to the date placed in service, instead of the period that corresponds to the prorate date. This option determines over how many periods to spread the annual depreciation amount. For straight-line depreciating assets, Oracle Assets always starts taking depreciation in the accounting period that corresponds to the prorate date.

Depreciate when placed in service flag

Designates whether to start taking depreciation in the accounting period that corresponds to the date placed in service or to start taking depreciation in the accounting period that corresponds to the prorate date.

Navigation: Setup → Asset System →Prorate Conventions

For more information on prorate conventions please refer support reference document mentioned below, you will find examples for Mid-Month, Daily and Common Tax Setup etc.

Reference: Doc ID 115323.1

Share this: