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;

Thanks Yogesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s