Skip to main content

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;

Labels:

Comments

Post a Comment

Popular posts from this blog

Create Accounting for a Payment generates errors 95333 and 95359

Create Accounting for a Payment generates errors 95333 and 95359 Error: 95333: A conversion rate does not exist to convert USD to AUD for the conversion type Corporate and conversion date 20-MAR-09 for line -25. Please use the Daily Rates form in General Ledger to enter a conversion rate for these currencies, conversion date and conversion type. 95359: There is no accounted amount for the subledger journal entry line. Please inform your system administrator or support representative that: The source assigned to the accounting attribute Accounted Amount has no value for extract line number 88547. Please make sure the source assigned to the accounting attribute Accounted Amount has a valid value, or assign a different source to this accounting attribute. Solution: 1. Specify a conversion rate for the currencies and conversion date mentioned in the error message 95333 Navigation under the General Ledger responsibility: Setup > Currencies > Currency Rates Manager > Daily Rates ...
15 comments
Read more

Public API’s for FA Transactions

Public API’s for FA Transactions So far Oracle FA is have all the good things except the lack on reporting.Oracle FA is now offer lot of public API's that can be used to interfacing with third party or Oracle application other modules. Here are some of transaction's API's:   Additions API if you have requirement to add assets directly via PL/SQL then use  FA_ADDITION_PUB.DO_ADDITION. If you have selected the Allow CIP Assets check box on the Book Controls window of a tax book when adding CIP assets using the Additions API, the this API automatically adds those CIP assets to that tax book at the same time that they are added to the corporate book. Adjustments API you can make cost adjustments to your assetsdirectly via PL/SQL using  FA_ADJUSTMENT_PUB.DO_ADJUSTMENT  for any  process adjustment. Detail can be found in appendix H) You can use this API if you have a custom interface that makes it difficult to use with the existing Oracle Assets interfaces for adjusti...
Post a Comment
Read more

AP Table Relation Oracle Apps

AP Table Relation Oracle Apps ORACLE PAYABLE TABLE RELATION Source Table Dependent Table Condition AP_INVOICE_LINES_ALL AIL ZX_LINES_SUMMARY ZLS AIL.invoice_id = ZLS.trx_id and  ZLS.application_id  = 200 and  ZLS.entity_code  = 'AP_INVOICES' and  ZLS.event_class_code  in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and  AIL.summary_tax_line_id = ZLS.summary_tax_line_id AP_INVOICE_LINES_ALL AIL ZX_LINES ZL AIL.invoice_id = ZL.trx_id and  ZL.application_id  = 200 and  ZL.entity_code  = 'AP_INVOICES' and  ZL.event_class_code  in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and  AIL.line_number = ZL.trx_line_number AP_INVOICE_DISTRIBUTIONS_ALL AID ZX_REC_NREC_DIST ZD AID.invoice_id = ZD.trx_id and  ZD.application_id  = 200 and  ZD.entity_code  = 'AP_INVOICES' and  ZD.event_class_code  in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and...
Post a Comment
Read more