Remaining Life Of an Asset in R12

 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';