R12 Fixed Asset Register Query

 Fixed Asset Register Query :

/* Formatted on 05-Nov-18 11:04:01 AM (QP5 v5.256.13226.35538) */
SELECT FB.BOOK_TYPE_CODE,
       FB.ASSET_ID,
       ASSET_NUMBER,
       FAB.DESCRIPTION,
          FC.SEGMENT1
       || ','
       || FC.SEGMENT2
       || ','
       || FC.SEGMENT3
       || ','
       || FC.SEGMENT4
          CATEGORY,
       FC.SEGMENT1 MAJ_CAT,
       FC.SEGMENT2 MIN_CAT1,
       FC.SEGMENT3 MIN_CAT2,
       FC.SEGMENT4 MIN_CAT3,
       FB.DATE_PLACED_IN_SERVICE,
       FB.PRORATE_CONVENTION_CODE,
       FB.PRORATE_DATE,
       DEPRN_METHOD_CODE,
       LIFE_IN_MONTHS,
       (FB.BASIC_RATE * 100) || '%' BASIC_RATE,
       (FB.ADJUSTED_RATE * 100) || '%' ADJUSTED_RATE,
       TO_CHAR (DATE_RETIRED, 'MON-YY') PERIOD_RETIRED,
       ASSET_TYPE,
       COST,
       ORIGINAL_COST,
       SALVAGE_VALUE,
       RECOVERABLE_COST,
       NVL (
          (SELECT TOTAL_DEPRN_AMOUNT
             FROM FA_FINANCIAL_INQUIRY_DEPRN_V
            WHERE     ASSET_ID = FB.ASSET_ID
                  AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER)
                                          FROM FA_FINANCIAL_INQUIRY_DEPRN_V
                                         WHERE ASSET_ID = FB.ASSET_ID)),
          0)
          LAST_DEP,
       NVL (
          (SELECT PERIOD_ENTERED
             FROM FA_FINANCIAL_INQUIRY_DEPRN_V
            WHERE     ASSET_ID = FB.ASSET_ID
                  AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER)
                                          FROM FA_FINANCIAL_INQUIRY_DEPRN_V
                                         WHERE ASSET_ID = FB.ASSET_ID)),
          'No Period')
          LAST_DEP_PERIOD,
       NVL (
          (SELECT SUM (TOTAL_DEPRN_AMOUNT)
             FROM FA_FINANCIAL_INQUIRY_DEPRN_V, GL_PERIODS GP
            WHERE     ASSET_ID = FB.ASSET_ID
                  AND GP.END_DATE BETWEEN    '01-JAN-'
                                          || TO_CHAR (SYSDATE, 'YYYY')
                                      AND SYSDATE
                  AND PERIOD_ENTERED = GP.PERIOD_NAME),
          0)
          YTD_DEPC,
       NVL (
          (SELECT SUM (TOTAL_DEPRN_AMOUNT)
             FROM FA_FINANCIAL_INQUIRY_DEPRN_V, GL_PERIODS GP
            WHERE     ASSET_ID = FB.ASSET_ID
                  AND GP.END_DATE <= SYSDATE
                  AND PERIOD_ENTERED = GP.PERIOD_NAME),
          0)
          REV_DEPC,
         COST
       - ( (SELECT SUM (TOTAL_DEPRN_AMOUNT)
              FROM FA_FINANCIAL_INQUIRY_DEPRN_V, GL_PERIODS GP
             WHERE     ASSET_ID = FB.ASSET_ID
                   AND GP.END_DATE <= SYSDATE
                   AND PERIOD_ENTERED = GP.PERIOD_NAME))
          NET_BOOK_VALUE,
          FL.SEGMENT1
       || '.'
       || FL.SEGMENT2
       || '.'
       || FL.SEGMENT3
       || '.'
       || FL.SEGMENT3
       || '.'
       || FL.SEGMENT4
       || '.'
       || FL.SEGMENT5
       || '.'
       || FL.SEGMENT6
       || '.'
       || FL.SEGMENT7
          LOCATION_CODE_COMBINATION,
       FL.SEGMENT1,
       FL.SEGMENT2,
       FL.SEGMENT3,
       FL.SEGMENT4,
       FL.SEGMENT5,
       FL.SEGMENT6,
       FL.SEGMENT7,
       FDH.UNITS_ASSIGNED UNITS,
       GCC.CONCATENATED_SEGMENTS DEP_CODE_COMPINATION,
       GCC.SEGMENT1 CO_SEG,
       GET_DESC (101,
                 1,
                 GCC.SEGMENT1,
                 NULL)
          CO_SEG_DESC,
       GCC.SEGMENT2 BU_SEG,
       GET_DESC (101,
                 2,
                 GCC.SEGMENT2,
                 NULL)
          BU_SEG_DESC,
       GCC.SEGMENT3 CC_SEG,
       GET_DESC (101,
                 3,
                 GCC.SEGMENT3,
                 NULL)
          CC_SEG_DESC,
       GCC.SEGMENT4 LOC_SEG,
       GET_DESC (101,
                 4,
                 GCC.SEGMENT4,
                 NULL)
          LOC_SEG_DESC,
       GCC.SEGMENT5 ACC_SEG,
       GET_DESC (101,
                 5,
                 GCC.SEGMENT5,
                 NULL)
          ACC_SEG_DESC,
       GCC.SEGMENT6 S_ACC_SEG,
       GET_DESC (101,
                 6,
                 GCC.SEGMENT6,
                 GCC.SEGMENT5)
          S_ACC_SEG_DESC,
       GCC.SEGMENT7 PRO_SEG,
       GET_DESC (101,
                 7,
                 GCC.SEGMENT7,
                 NULL)
          PRO_SEG_DESC,
       GCC.SEGMENT8 PL_SEG,
       GET_DESC (101,
                 8,
                 GCC.SEGMENT8,
                 NULL)
          PL_SEG_DESC,
          GET_DESC (101,
                    1,
                    GCC.SEGMENT1,
                    NULL)
       || '.'
       || GET_DESC (101,
                    2,
                    GCC.SEGMENT2,
                    NULL)
       || '.'
       || GET_DESC (101,
                    3,
                    GCC.SEGMENT3,
                    NULL)
       || '.'
       || GET_DESC (101,
                    4,
                    GCC.SEGMENT4,
                    NULL)
       || '.'
       || GET_DESC (101,
                    5,
                    GCC.SEGMENT5,
                    NULL)
       || '.'
       || GET_DESC (101,
                    6,
                    GCC.SEGMENT6,
                    GCC.SEGMENT5)
       || '.'
       || GET_DESC (101,
                    7,
                    GCC.SEGMENT7,
                    NULL)
       || '.'
       || GET_DESC (101,
                    8,
                    GCC.SEGMENT8,
                    NULL)
          DEP_CODE_COMPINATION_DESC
  FROM FA_ADDITIONS_v FAB
       JOIN FA_BOOKS_V FB ON (FAB.ASSET_ID = FB.ASSET_ID)
       JOIN FA_DISTRIBUTION_HISTORY FDH ON (FB.ASSET_ID = FDH.ASSET_ID)
       LEFT JOIN FA_LOCATIONS FL ON (FDH.LOCATION_ID = FL.LOCATION_ID)
       LEFT JOIN FA_RETIREMENTS FR ON (FDH.RETIREMENT_ID = FR.RETIREMENT_ID)
       LEFT JOIN FA_ASSET_KEYWORDS FK
          ON (FK.CODE_COMBINATION_ID = FAB.ASSET_KEY_CCID)
       JOIN GL_CODE_COMBINATIONS_KFV GCC
          ON (GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID)
       JOIN FA_CATEGORIES_B FC ON (FAB.ASSET_CATEGORY_ID = FC.CATEGORY_ID)
 WHERE FDH.DATE_INEFFECTIVE IS NULL AND FB.BOOK_TYPE_CODE = :BOOK_TYPE_CODE;