A) Remaining Life Of an Asset Based on Sysdate
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 |
SELECT DECODE( books.period_counter_fully_retired, NULL, trunc(greatest( nvl( books.life_in_months, 0 ) - round(months_between( SYSDATE, books.prorate_date ) ), 0 ) / 12), 0 ) remaining_life_in_years, DECODE( books.period_counter_fully_retired, NULL, greatest( nvl( books.life_in_months, 0 ) - round(months_between( SYSDATE, books.prorate_date ) ), 0 ), 0 ) remaining_life_in_months, asset_id FROM fa_books books WHERE asset_id = 2728-- Pass your assest AND books.date_ineffective IS NULL AND books.transaction_header_id_out IS NULL; |
B) Remaining Life Of an Asset Based on last depreciation run or as displyed on Asset Workbench screen
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 |
SELECT DECODE( books.period_counter_fully_retired, NULL, trunc(greatest( nvl( books.life_in_months, 0 ) - trunc(months_between( nvl(fdp.PERIOD_CLOSE_DATE,fdp.PERIOD_OPEN_DATE), books.prorate_date ) ), 0 ) / 12), 0 ) remaining_life_in_years, DECODE( books.period_counter_fully_retired, NULL, greatest( nvl( books.life_in_months, 0 ) - trunc(months_between( nvl(fdp.PERIOD_CLOSE_DATE,fdp.PERIOD_OPEN_DATE), books.prorate_date ) ), 0 ), 0 ) remaining_life_in_months, books.asset_id FROM fa_books books, fa_deprn_summary dep_sum, fa_deprn_periods fdp WHERE books.asset_id = 2728-- Pass your assest AND books.date_ineffective IS NULL AND books.transaction_header_id_out IS NULL AND books.asset_id = dep_sum.asset_id AND dep_sum.period_counter=fdp.period_counter AND fdp.period_name = '05-2017'; |