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;