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

Oracle apps EBS sql to get Fixed Asset FA details with Net Book Value NBV and Depreciation
Query to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
How to find net book value in Oracle

  SELECT DISTINCT
         fab.asset_id,
         fab.asset_number,
         FAT.DESCRIPTION,
         fab.current_units,
         SUM (fdh.units_assigned)                         units_assigned_count,
         COUNT (fdh.distribution_id)                      distribution_count,
         ROUND ( (fb.life_in_months / 12), 1)             lifeyear,
         TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY')
            date_placed_in_service,
         fb.deprn_method_code,
         fb.cost,
         fb.original_cost,
         (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3)
            CATEGORY_COMBINATION,
         NVL (
            (SELECT SUM (deprn_reserve)
               FROM fa_deprn_detail FDD, fa_distribution_history fdhi
              WHERE     fdd.asset_id = fab.asset_id
                    AND PERIOD_COUNTER =
                           (SELECT MAX (PERIOD_COUNTER)
                              FROM fa_deprn_detail fddi
                             WHERE     ASSET_ID = FDD.ASSET_ID
                                   AND fddi.distribution_id =
                                          fdd.distribution_id
                                   AND fddi.distribution_id =
                                          fdhi.distribution_id)
                    AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID),
            0)
            depreciation,
         (  fb.cost
          - NVL (
               (SELECT SUM (deprn_reserve)
                  FROM fa_deprn_detail FDD, fa_distribution_history fdhi
                 WHERE     fdd.asset_id = fab.asset_id
                       AND PERIOD_COUNTER =
                              (SELECT MAX (PERIOD_COUNTER)
                                 FROM fa_deprn_detail fddi
                                WHERE     ASSET_ID = FDD.ASSET_ID
                                      AND fddi.distribution_id =
                                             fdd.distribution_id
                                      AND fddi.distribution_id =
                                             fdhi.distribution_id)
                       AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID),
               0))
            nbv,
         FACB.ASSET_COST_ACCT                             ORIGINALCOSTGL,
         facb.deprn_reserve_acct                          accumulateddeprngl,
         fb.book_type_code,
         TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY')       CREATION_DATE,
         TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY') purchased_date,
         fab.creation_date,
         fcb.category_id,
         (SELECT DISTINCT
                 LISTAGG (fidv.INVOICE_NUMBER, ';')
                    WITHIN GROUP (ORDER BY fidv.asset_id)
                    OVER (PARTITION BY fidv.asset_id)
            FROM FA_INVOICE_DETAILS_V fidv
           WHERE fidv.asset_id = fab.asset_id)
            INVOICE_NUMBER,
         (SELECT DISTINCT
                    pol.category_id
                 || ';'
                 || mc.concatenated_segments
                 || ';'
                 || poh.segment1
                    po_number
            FROM po_headers_all     poh,
                 po_lines_all       pol,
                 po_distributions_all pda,
                 gl_code_combinations gcc,
                 mtl_categories_kfv mc,
                 FA_INVOICE_DETAILS_V fidv
           WHERE     poh.po_header_id = pol.po_header_id
                 AND pol.po_line_id = pda.po_line_id
                 AND pol.po_header_id = pda.po_header_id
                 AND gcc.code_combination_id = pda.code_combination_id
                 AND pol.category_id = mc.category_id
                 AND fidv.asset_id = fab.asset_id
                 AND fidv.po_number = poh.segment1
                 AND ROWNUM = 1)
            po_details
    FROM fa_additions_b         fab,
         fa_additions_tl        fat,
         fa_books               fb,
         fa_categories_b        fcb,
         fa_distribution_history fdh,
         fa_locations           fl,
         fa_category_books      facb,
         fa_asset_keywords      fak,
         fa_add_warranties      fad,
         fa_warranties          fw,
         gl_code_combinations_kfv gcc
   WHERE     1 = 1
         AND fab.asset_id = fat.asset_id
         AND fab.asset_category_id = fcb.category_id
         AND fab.asset_id = fb.asset_id
         AND fab.asset_id = fdh.asset_id
         AND fdh.location_id = fl.location_id
         AND fak.code_combination_id(+) = fab.asset_key_ccid
         AND fad.asset_id(+) = fab.asset_id
         AND fb.date_ineffective IS NULL
         AND facb.category_id = fcb.category_id
         AND fb.book_type_code = 'Your ASSET BOOK'
         AND facb.book_type_code = fb.book_type_code
         AND fat.LANGUAGE = 'US'
         AND FW.WARRANTY_ID(+) = FAD.WARRANTY_ID
         AND NOT EXISTS
                (SELECT 1
                   FROM FA_RETIREMENTS FR
                  WHERE RETIREMENT_ID = FDH.RETIREMENT_ID)
         AND ( (fdh.retirement_id IS NOT NULL) OR FDH.DATE_INEFFECTIVE IS NULL)
         AND NOT EXISTS
                (SELECT 1
                   FROM (SELECT TRANSACTION_TYPE_CODE
                           FROM (  SELECT TRANSACTION_TYPE_CODE
                                     FROM FA_TRANSACTION_HISTORY_TRX_V
                                    WHERE ASSET_ID = FAB.ASSET_ID
                                 ORDER BY TRANSACTION_HEADER_ID DESC)
                          WHERE ROWNUM = 1)
                  WHERE TRANSACTION_TYPE_CODE = 'FULL RETIREMENT')
         AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
GROUP BY fab.asset_id,
         fab.asset_number,
         FAT.DESCRIPTION,
         fab.current_units,
         ROUND ( (fb.life_in_months / 12), 1),
         TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY'),
         fb.deprn_method_code,
         fb.original_cost,
         (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3),
         FACB.ASSET_COST_ACCT,
         facb.deprn_reserve_acct,
         fb.book_type_code,
         TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY'),
         TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY'),
         fab.creation_date,
         fb.cost,
         fcb.category_id

Related posts:

• PTD and YTD balances query in Oracle R12
• Query to find all Expense report entry delegations
• Query to find vacation rules in Oracle R12
• View accounting query in oracle apps R12
• Query to get AR invoice SLA and GL details
• Accounting entries not transferred to the General Ledger
• Query to find all APIs of Oracle Apps modules
• R12 AR cash receipt SLA and GL details query
• Oracle apps AR Applications and SLA details
• Oracle EBS query to get AP Invoice SLA Details
• Oracle EBS query to get AR Invoice SLA Details
• Oracle AR Remit to address query
• Oracle Apps iExpense credit card details query
• Query to get customer tax registration details in R12
• Purchase Requisition Vertex debug xml query in Oracle EBS
• Purchase Order Vertex debug xml query in Oracle EBS
• AP Invoice Vertex debug xml query in Oracle EBS
• AR Invoice Vertex debug xml query in Oracle EBS
• Query to find AP invoice workflow item key in oracle apps
• Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
• Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
• Query to find Sales Orders that are shipped but not invoiced
• Query to get Customer Bank Account Details
• Query to get description of GL Code Combination
• Query to find Purchase Order and Invoice Details
• Query to find AP Invoices matched with receipts
• Query to find Supplier Bank Details
• Query to find Credit Memos on an AP invoice
• Query to find the AP invoices that are applied to multiple Purchase Orders
• AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
• Query to find shipset and fulfilment set

Upload your own post and refer it anywhere anytime: