Remaining Life Of an Asset in R12

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

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