Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation

1SELECT DISTINCT
2         fab.asset_id,
3         fab.asset_number,
4         FAT.DESCRIPTION,
5         fab.current_units,
6         SUM (fdh.units_assigned)                         units_assigned_count,
7         COUNT (fdh.distribution_id)                      distribution_count,
8         ROUND ( (fb.life_in_months / 12), 1)             lifeyear,
9         TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY')
10            date_placed_in_service,
11         fb.deprn_method_code,
12         fb.cost,
13         fb.original_cost,
14         (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3)
15            CATEGORY_COMBINATION,
16         NVL (
17            (SELECT SUM (deprn_reserve)
18               FROM fa_deprn_detail FDD, fa_distribution_history fdhi
19              WHERE     fdd.asset_id = fab.asset_id
20                    AND PERIOD_COUNTER =
21                           (SELECT MAX (PERIOD_COUNTER)
22                              FROM fa_deprn_detail fddi
23                             WHERE     ASSET_ID = FDD.ASSET_ID
24                                   AND fddi.distribution_id =
25                                          fdd.distribution_id
26                                   AND fddi.distribution_id =
27                                          fdhi.distribution_id)
28                    AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID),
29            0)
30            depreciation,
31         (  fb.cost
32          - NVL (
33               (SELECT SUM (deprn_reserve)
34                  FROM fa_deprn_detail FDD, fa_distribution_history fdhi
35                 WHERE     fdd.asset_id = fab.asset_id
36                       AND PERIOD_COUNTER =
37                              (SELECT MAX (PERIOD_COUNTER)
38                                 FROM fa_deprn_detail fddi
39                                WHERE     ASSET_ID = FDD.ASSET_ID
40                                      AND fddi.distribution_id =
41                                             fdd.distribution_id
42                                      AND fddi.distribution_id =
43                                             fdhi.distribution_id)
44                       AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID),
45               0))
46            nbv,
47         FACB.ASSET_COST_ACCT                             ORIGINALCOSTGL,
48         facb.deprn_reserve_acct                          accumulateddeprngl,
49         fb.book_type_code,
50         TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY')       CREATION_DATE,
51         TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY') purchased_date,
52         fab.creation_date,
53         fcb.category_id,
54         (SELECT DISTINCT
55                 LISTAGG (fidv.INVOICE_NUMBER, ';')
56                    WITHIN GROUP (ORDER BY fidv.asset_id)
57                    OVER (PARTITION BY fidv.asset_id)
58            FROM FA_INVOICE_DETAILS_V fidv
59           WHERE fidv.asset_id = fab.asset_id)
60            INVOICE_NUMBER,
61         (SELECT DISTINCT
62                    pol.category_id
63                 || ';'
64                 || mc.concatenated_segments
65                 || ';'
66                 || poh.segment1
67                    po_number
68            FROM po_headers_all     poh,
69                 po_lines_all       pol,
70                 po_distributions_all pda,
71                 gl_code_combinations gcc,
72                 mtl_categories_kfv mc,
73                 FA_INVOICE_DETAILS_V fidv
74           WHERE     poh.po_header_id = pol.po_header_id
75                 AND pol.po_line_id = pda.po_line_id
76                 AND pol.po_header_id = pda.po_header_id
77                 AND gcc.code_combination_id = pda.code_combination_id
78                 AND pol.category_id = mc.category_id
79                 AND fidv.asset_id = fab.asset_id
80                 AND fidv.po_number = poh.segment1
81                 AND ROWNUM = 1)
82            po_details
83    FROM fa_additions_b         fab,
84         fa_additions_tl        fat,
85         fa_books               fb,
86         fa_categories_b        fcb,
87         fa_distribution_history fdh,
88         fa_locations           fl,
89         fa_category_books      facb,
90         fa_asset_keywords      fak,
91         fa_add_warranties      fad,
92         fa_warranties          fw,
93         gl_code_combinations_kfv gcc
94   WHERE     1 = 1
95         AND fab.asset_id = fat.asset_id
96         AND fab.asset_category_id = fcb.category_id
97         AND fab.asset_id = fb.asset_id
98         AND fab.asset_id = fdh.asset_id
99         AND fdh.location_id = fl.location_id
100         AND fak.code_combination_id(+) = fab.asset_key_ccid
101         AND fad.asset_id(+) = fab.asset_id
102         AND fb.date_ineffective IS NULL
103         AND facb.category_id = fcb.category_id
104         AND fb.book_type_code = 'Your ASSET BOOK'
105         AND facb.book_type_code = fb.book_type_code
106         AND fat.LANGUAGE = 'US'
107         AND FW.WARRANTY_ID(+) = FAD.WARRANTY_ID
108         AND NOT EXISTS
109                (SELECT 1
110                   FROM FA_RETIREMENTS FR
111                  WHERE RETIREMENT_ID = FDH.RETIREMENT_ID)
112         AND ( (fdh.retirement_id IS NOT NULL) OR FDH.DATE_INEFFECTIVE IS NULL)
113         AND NOT EXISTS
114                (SELECT 1
115                   FROM (SELECT TRANSACTION_TYPE_CODE
116                           FROM (  SELECT TRANSACTION_TYPE_CODE
117                                     FROM FA_TRANSACTION_HISTORY_TRX_V
118                                    WHERE ASSET_ID = FAB.ASSET_ID
119                                 ORDER BY TRANSACTION_HEADER_ID DESC)
120                          WHERE ROWNUM = 1)
121                  WHERE TRANSACTION_TYPE_CODE = 'FULL RETIREMENT')
122         AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
123GROUP BY fab.asset_id,
124         fab.asset_number,
125         FAT.DESCRIPTION,
126         fab.current_units,
127         ROUND ( (fb.life_in_months / 12), 1),
128         TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY'),
129         fb.deprn_method_code,
130         fb.original_cost,
131         (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3),
132         FACB.ASSET_COST_ACCT,
133         facb.deprn_reserve_acct,
134         fb.book_type_code,
135         TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY'),
136         TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY'),
137         fab.creation_date,
138         fb.cost,
139         fcb.category_id