R12 GL All Acounting All JEs 'All Journal Entries' Query

 R12 GL All Acounting All JEs 'All Journal Entries' Query :

SELECT GB.NAME LEDGER,
          GB.LEDGER_ID,
          GJH.JE_HEADER_ID,
          GJH.JE_SOURCE,
          GJCT.USER_JE_CATEGORY_NAME JE_CATEGORY,
          GJB.NAME BATCH_NAME,
          GJH.NAME JOURNAL_NAME,
          GJH.PERIOD_NAME,
          GJH.STATUS,
          GJH.CURRENCY_CODE,
          GJH.CURRENCY_CONVERSION_RATE,
          GJH.DOC_SEQUENCE_VALUE JOURNAL_NUMBER,
          GJH.CREATION_DATE,
          GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
          GJH.DESCRIPTION JOURNAL_DESCRIPTION,
          GJL.JE_LINE_NUM,
          GJL.DESCRIPTION,
          GCC."CODE_COMBINATION_ID",
          GCC."COMPANY",
          GCC."COMPANY_DESC",
          GCC."BUSINESS_UNIT",
          GCC."BUSINESS_UNIT_DESC",
          GCC."COST_CENTER",
          GCC."COST_CENTER_DESC",
          GCC."LOCATION",
          GCC."LOCATION_DESC",
          GCC."ACCOUNT",
          GCC."ACCOUNT_DESC",
          GCC."SUB_ACCOUNT",
          GCC."SUB_ACCOUNT_DESC",
          GCC."PROJECT",
          GCC."PROJECT_DESC",
          GCC."PRODUCT",
          GCC."PRODUCT_DESC",
          GCC."CODE_COMBINATION",
          NVL (GJL.ENTERED_DR, 0) ENTERED_DEBIT,
          NVL (GJL.ENTERED_CR, 0) ENTERED_CREDIT,
          NVL (GJL.ACCOUNTED_DR, 0) DEBIT,
          NVL (GJL.ACCOUNTED_CR, 0) CREDIT,
          NVL (GJL.ACCOUNTED_DR, 0) - NVL (GJL.ACCOUNTED_CR, 0) BALANCE,
          GP.PERIOD_YEAR,
          GP.PERIOD_NUM
     FROM GL_JE_BATCHES GJB
          JOIN GL_JE_HEADERS GJH ON (GJB.JE_BATCH_ID = GJH.JE_BATCH_ID)
          JOIN GL_JE_CATEGORIES_TL GJCT
             ON (GJCT.JE_CATEGORY_NAME = GJH.JE_CATEGORY)
          JOIN GL_JE_LINES GJL ON (GJL.JE_HEADER_ID = GJH.JE_HEADER_ID)
          JOIN XX_GL_CODE_COMBINATION_DESC GCC
             ON (GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID)
          JOIN XX_PERIODS GP ON (GJH.PERIOD_NAME = GP.PERIOD_NAME)
          JOIN GL_LEDGERS GB ON (GB.LEDGER_ID = GJH.LEDGER_ID)
    WHERE 1 = 1 AND GJH.ACTUAL_FLAG = 'A' AND GJH.LEDGER_ID = :LEDGER_ID;

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;