JH Asset details

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