APPS.JL_JLMXFISR_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 13

    SELECT
      COMPANY_NAME,
      CATEGORY_FLEX_STRUCTURE
    INTO C_ORGANIZATION_NAME,CAT_FLEX_STRUCT
    FROM
      FA_SYSTEM_CONTROLS;

Line: 21

        SELECT
          FCURR.CURRENCY_CODE,
          FCURR.PRECISION,
          FCURR.MINIMUM_ACCOUNTABLE_UNIT,
          FCURR.DESCRIPTION
        INTO BASE_CURR,PREC,MIN_AU,DESCR
        FROM
          FA_BOOK_CONTROLS_MRC_V BKCTRL,
          FND_CURRENCIES_VL FCURR,
          GL_SETS_OF_BOOKS GSBKS
        WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
          AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;

Line: 43

        SELECT
          FCURR.CURRENCY_CODE,
          FCURR.PRECISION,
          FCURR.MINIMUM_ACCOUNTABLE_UNIT,
          FCURR.DESCRIPTION
        INTO BASE_CURR,PREC,MIN_AU,DESCR
        FROM
          FA_BOOK_CONTROLS BKCTRL,
          FND_CURRENCIES_VL FCURR,
          GL_SETS_OF_BOOKS GSBKS
        WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
          AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;

Line: 88

    SELECT
      FPI.PRICE_INDEX_ID
    INTO X
    FROM
      FA_PRICE_INDEXES FPI,
      FA_CATEGORY_BOOK_DEFAULTS FCBD
    WHERE FCBD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
      AND FCBD.CATEGORY_ID = ASSET_CAT_ID
      AND ACQDATE >= FCBD.START_DPIS
      AND ACQDATE <= NVL(FCBD.END_DPIS
       ,ACQDATE)
      AND FCBD.PRICE_INDEX_NAME = FPI.PRICE_INDEX_NAME;

Line: 106

      SELECT
        DESCRIPTION
      INTO CATEGORY_DESC
      FROM
        FA_CATEGORIES
      WHERE CATEGORY_ID = ASSET_CAT_ID;

Line: 134

    SELECT
      PRICE_INDEX_VALUE
    INTO X
    FROM
      FA_PRICE_INDEX_VALUES
    WHERE PRICE_INDEX_ID = C_PRICE_INDEX
      AND ACQDATE BETWEEN FROM_DATE
      AND TO_DATE;

Line: 146

      SELECT
        PRICE_INDEX_NAME
      INTO INDEX_NAME
      FROM
        FA_PRICE_INDEXES
      WHERE PRICE_INDEX_ID = C_PRICE_INDEX;

Line: 226

    INSERT INTO JL_MX_FA_ISR_IMPAC
      (PROCESS_ID
      ,ASSET_ID
      ,TRANSACTION_HEADER_ID_IN
      ,TRANSACTION_HEADER_ID_OUT
      ,DESCRIPTION
      ,ASSET_CATEGORY_ID
      ,ASSET_NUMBER
      ,DATE_PLACED_IN_SERVICE
      ,PRORATE_DATE
      ,RETIREMENT_ID
      ,INPC
      ,ORIGINAL_COST
      ,ACCUM_DEPRN_PREVIOUS_FY
      ,ADJUSTED_DEPRN_CURRENT_FY
      ,PERIOD_COUNTER_FULLY_RESERVED
      ,LAST_UPDATE_DATE
      ,LAST_UPDATED_BY
      ,CREATION_DATE
      ,CREATED_BY
      ,LAST_UPDATE_LOGIN)
    VALUES   (P_CONC_REQUEST_ID
      ,ASSET_ID
      ,TRANSACTION_HEADER_ID_IN
      ,TRANSACTION_HEADER_ID_OUT
      ,DESCRIPTION
      ,ASSET_CAT_ID
      ,ASSET_NUMBER
      ,ACQDATE
      ,PRORATE_DATE
      ,RETIREMENT_ID
      ,C_INDEX_VALUE
      ,ORIGINAL_COST
      ,C_ACCUM_DEPRN_PREV_YR
      ,ACCUM_DEPRN
      ,PERIOD_COUNTER_FULLY_RESERVED
      ,SYSDATE
      ,-1
      ,SYSDATE
      ,C_USER_ID
      ,C_USER_ID);

Line: 321

          SELECT
            TO_CHAR(A.DATE_RETIRED
                   ,'MM')
          INTO MON_RETIRED
          FROM
            FA_RETIREMENTS_MRC_V A
          WHERE A.RETIREMENT_ID = RETIREMENT_ID_1;

Line: 334

          SELECT
            TO_CHAR(A.DATE_RETIRED
                   ,'MM')
          INTO MON_RETIRED
          FROM
            FA_RETIREMENTS A
          WHERE A.RETIREMENT_ID = RETIREMENT_ID_1;

Line: 372

      SELECT
        PRICE_INDEX_VALUE
      INTO INPC_HALF
      FROM
        FA_PRICE_INDEX_VALUES
      WHERE PRICE_INDEX_ID = C_PRICE_INDEX
        AND HALF_PERIOD_DATE BETWEEN FROM_DATE
        AND TO_DATE;

Line: 384

        SELECT
          PRICE_INDEX_NAME
        INTO INDEX_NAME
        FROM
          FA_PRICE_INDEXES
        WHERE PRICE_INDEX_ID = C_PRICE_INDEX;

Line: 430

        SELECT
          MIN(PERIOD_COUNTER),
          MAX(PERIOD_COUNTER)
        INTO MIN_PER_COUNTER,MAX_PER_COUNTER
        FROM
          FA_DEPRN_PERIODS_MRC_V
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FISCAL_YEAR = P_CURR_FY;

Line: 448

        SELECT
          MAX(PERIOD_COUNTER)
        INTO LAST_PER_COUNTER
        FROM
          FA_DEPRN_PERIODS_MRC_V
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FISCAL_YEAR = P_CURR_FY - 1;

Line: 464

        SELECT
          A.START_DATE,
          A.END_DATE
        INTO FROM_DATE,TO_DATE
        FROM
          FA_BOOK_CONTROLS_MRC_V B,
          FA_FISCAL_YEAR A
        WHERE A.FISCAL_YEAR = P_CURR_FY
          AND B.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND B.FISCAL_YEAR_NAME = A.FISCAL_YEAR_NAME;

Line: 485

        SELECT
          MIN(PERIOD_COUNTER),
          MAX(PERIOD_COUNTER)
        INTO MIN_PER_COUNTER,MAX_PER_COUNTER
        FROM
          FA_DEPRN_PERIODS
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FISCAL_YEAR = P_CURR_FY;

Line: 503

        SELECT
          MAX(PERIOD_COUNTER)
        INTO LAST_PER_COUNTER
        FROM
          FA_DEPRN_PERIODS
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FISCAL_YEAR = P_CURR_FY - 1;

Line: 519

        SELECT
          A.START_DATE,
          A.END_DATE
        INTO FROM_DATE,TO_DATE
        FROM
          FA_BOOK_CONTROLS B,
          FA_FISCAL_YEAR A
        WHERE A.FISCAL_YEAR = P_CURR_FY
          AND B.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND B.FISCAL_YEAR_NAME = A.FISCAL_YEAR_NAME;

Line: 587

      SELECT
        FDS.ADJUSTED_COST,
        FDS.PERIOD_COUNTER
      FROM
        FA_DEPRN_SUMMARY FDS
      WHERE FDS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
        AND FDS.ASSET_ID = P_ASSET_ID
        AND FDS.PERIOD_COUNTER between P_PERIOD_START
        AND P_PERIOD_COUNTER;

Line: 597

      SELECT
        FDS.ADJUSTED_COST,
        FDS.PERIOD_COUNTER
      FROM
        FA_DEPRN_SUMMARY_MRC_V FDS
      WHERE FDS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
        AND FDS.ASSET_ID = P_ASSET_ID
        AND FDS.PERIOD_COUNTER between P_PERIOD_START
        AND P_PERIOD_COUNTER;

Line: 610

        SELECT
          PERIOD_COUNTER,
          DEPRN_RESERVE,
          ADJUSTED_COST
        INTO L_LAST_DEPRN_PERIOD_PREV_FY,L_ACC_DEPRN_PREV_FY,L_ADJUSTED_COST_PREV_FY
        FROM
          FA_DEPRN_SUMMARY_MRC_V
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND ASSET_ID = P_ASSET_ID
          AND PERIOD_COUNTER = (
          SELECT
            MAX(FDS2.PERIOD_COUNTER)
          FROM
            FA_DEPRN_SUMMARY_MRC_V FDS2
          WHERE FDS2.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND FDS2.ASSET_ID = P_ASSET_ID
            AND FDS2.PERIOD_COUNTER <= P_PERIOD_START - 1 );

Line: 638

        SELECT
          COUNT(RET.RETIREMENT_ID)
        INTO L_HAS_RETIREMENT
        FROM
          FA_RETIREMENTS_MRC_V RET,
          FA_DEPRN_PERIODS_MRC_V FDP
        WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND RET.STATUS <> 'DELETED'
          AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
          AND RET.ASSET_ID = P_ASSET_ID
          AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
          AND FDP.CALENDAR_PERIOD_CLOSE_DATE
          AND FDP.FISCAL_YEAR = P_CURR_FY;

Line: 657

        SELECT
          COUNT(FDD.DEPRN_ADJUSTMENT_AMOUNT),
          SUM(FDD.DEPRN_ADJUSTMENT_AMOUNT)
        INTO L_HAS_RESERVE_ADJUSTMENT,L_TOTAL_DEPRN_ADJUSTMENT
        FROM
          FA_DEPRN_DETAIL_MRC_V FDD
        WHERE FDD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FDD.ASSET_ID = P_ASSET_ID
          AND FDD.PERIOD_COUNTER between P_PERIOD_START
          AND P_PERIOD_END
          AND FDD.DEPRN_ADJUSTMENT_AMOUNT <> 0;

Line: 670

        SELECT
          BKS.COST
        INTO L_COST_BEGIN_YEAR
        FROM
          FA_BOOKS_MRC_V BKS,
          FA_DEPRN_PERIODS_MRC_V DPP
        WHERE DPP.PERIOD_COUNTER = P_PERIOD_START
          AND BKS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND DPP.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
          AND BKS.ASSET_ID = P_ASSET_ID
          AND DPP.PERIOD_OPEN_DATE between BKS.DATE_EFFECTIVE
          AND NVL(BKS.DATE_INEFFECTIVE
           ,DPP.PERIOD_OPEN_DATE);

Line: 692

          SELECT
            DEPRN_RESERVE / L_COST_BEGIN_YEAR * P_COST_RETIRED
          INTO P_ACC_DEPRN_PREV_FY
          FROM
            FA_DEPRN_SUMMARY_MRC_V
          WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND ASSET_ID = P_ASSET_ID
            AND PERIOD_COUNTER = L_LAST_DEPRN_PERIOD_PREV_FY;

Line: 716

              SELECT
                RET.COST_RETIRED
              INTO L_COST_RETIRED
              FROM
                FA_RETIREMENTS_MRC_V RET,
                FA_DEPRN_PERIODS_MRC_V FDP
              WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
                AND RET.STATUS <> 'DELETED'
                AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
                AND RET.ASSET_ID = P_ASSET_ID
                AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
                AND FDP.CALENDAR_PERIOD_CLOSE_DATE
                AND FDP.PERIOD_COUNTER = C_RET_REC.PERIOD_COUNTER;

Line: 754

        SELECT
          PERIOD_COUNTER,
          DEPRN_RESERVE,
          ADJUSTED_COST
        INTO L_LAST_DEPRN_PERIOD_PREV_FY,L_ACC_DEPRN_PREV_FY,L_ADJUSTED_COST_PREV_FY
        FROM
          FA_DEPRN_SUMMARY
        WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND ASSET_ID = P_ASSET_ID
          AND PERIOD_COUNTER = (
          SELECT
            MAX(FDS2.PERIOD_COUNTER)
          FROM
            FA_DEPRN_SUMMARY FDS2
          WHERE FDS2.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND FDS2.ASSET_ID = P_ASSET_ID
            AND FDS2.PERIOD_COUNTER <= P_PERIOD_START - 1 );

Line: 782

        SELECT
          COUNT(RET.RETIREMENT_ID)
        INTO L_HAS_RETIREMENT
        FROM
          FA_RETIREMENTS RET,
          FA_DEPRN_PERIODS FDP
        WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND RET.STATUS <> 'DELETED'
          AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
          AND RET.ASSET_ID = P_ASSET_ID
          AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
          AND FDP.CALENDAR_PERIOD_CLOSE_DATE
          AND FDP.FISCAL_YEAR = P_CURR_FY;

Line: 801

        SELECT
          BKS.COST
        INTO L_COST_BEGIN_YEAR
        FROM
          FA_BOOKS BKS,
          FA_DEPRN_PERIODS DPP
        WHERE DPP.PERIOD_COUNTER = P_PERIOD_START
          AND BKS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND DPP.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
          AND BKS.ASSET_ID = P_ASSET_ID
          AND DPP.PERIOD_OPEN_DATE between BKS.DATE_EFFECTIVE
          AND NVL(BKS.DATE_INEFFECTIVE
           ,DPP.PERIOD_OPEN_DATE);

Line: 822

        SELECT
          COUNT(FDD.DEPRN_ADJUSTMENT_AMOUNT),
          SUM(FDD.DEPRN_ADJUSTMENT_AMOUNT)
        INTO L_HAS_RESERVE_ADJUSTMENT,L_TOTAL_DEPRN_ADJUSTMENT
        FROM
          FA_DEPRN_DETAIL FDD
        WHERE FDD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
          AND FDD.ASSET_ID = P_ASSET_ID
          AND FDD.PERIOD_COUNTER between P_PERIOD_START
          AND P_PERIOD_END
          AND FDD.DEPRN_ADJUSTMENT_AMOUNT <> 0;

Line: 836

          SELECT
            DEPRN_RESERVE / L_COST_BEGIN_YEAR * P_COST_RETIRED
          INTO P_ACC_DEPRN_PREV_FY
          FROM
            FA_DEPRN_SUMMARY
          WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND ASSET_ID = P_ASSET_ID
            AND PERIOD_COUNTER = L_LAST_DEPRN_PERIOD_PREV_FY;

Line: 860

              SELECT
                RET.COST_RETIRED
              INTO L_COST_RETIRED
              FROM
                FA_RETIREMENTS RET,
                FA_DEPRN_PERIODS FDP
              WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
                AND RET.STATUS <> 'DELETED'
                AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
                AND RET.ASSET_ID = P_ASSET_ID
                AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
                AND FDP.CALENDAR_PERIOD_CLOSE_DATE
                AND FDP.PERIOD_COUNTER = C_RET_REC.PERIOD_COUNTER;

Line: 913

          SELECT
            SUM(FAD.ADJUSTMENT_AMOUNT)
          INTO P_ACC_DEPRN_LIFE_TD
          FROM
            FA_ADJUSTMENTS_MRC_V FAD
          WHERE FAD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND FAD.ASSET_ID = P_ASSET_ID
            AND FAD.SOURCE_TYPE_CODE = 'RETIREMENT'
            AND FAD.ADJUSTMENT_TYPE = 'RESERVE'
            AND FAD.TRANSACTION_HEADER_ID = P_TRANSACTION_HEADER_ID;

Line: 931

          SELECT
            DEPRN_RESERVE
          INTO P_ACC_DEPRN_LIFE_TD
          FROM
            FA_DEPRN_SUMMARY_MRC_V
          WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND ASSET_ID = P_ASSET_ID
            AND PERIOD_COUNTER = P_PERIOD_COUNTER;

Line: 949

          SELECT
            SUM(FAD.ADJUSTMENT_AMOUNT)
          INTO P_ACC_DEPRN_LIFE_TD
          FROM
            FA_ADJUSTMENTS FAD
          WHERE FAD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND FAD.ASSET_ID = P_ASSET_ID
            AND FAD.SOURCE_TYPE_CODE = 'RETIREMENT'
            AND FAD.ADJUSTMENT_TYPE = 'RESERVE'
            AND FAD.TRANSACTION_HEADER_ID = P_TRANSACTION_HEADER_ID;

Line: 967

          SELECT
            DEPRN_RESERVE
          INTO P_ACC_DEPRN_LIFE_TD
          FROM
            FA_DEPRN_SUMMARY
          WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
            AND ASSET_ID = P_ASSET_ID
            AND PERIOD_COUNTER = P_PERIOD_COUNTER;

Line: 990

        SELECT
          DATE_RETIRED
        INTO L_FA_RETIREMENT
        FROM
          FA_RETIREMENTS_MRC_V
        WHERE RETIREMENT_ID = RETIREMENT_ID_1;

Line: 997

        SELECT
          DATE_RETIRED
        INTO L_FA_RETIREMENT
        FROM
          FA_RETIREMENTS
        WHERE RETIREMENT_ID = RETIREMENT_ID_1;

Line: 1013

    SELECT
      MEANING
    INTO T_MEANING
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO'
      AND LOOKUP_CODE = P_INCLUDE_DPIS;

Line: 1029

    SELECT
      MEANING
    INTO T_MEANING
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO'
      AND LOOKUP_CODE = P_INCLUDE_RET;

Line: 1045

    SELECT
      MEANING
    INTO T_MEANING
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO'
      AND LOOKUP_CODE = P_INCLUDE_ZERO_NBV_ASSETS;

Line: 1062

        SELECT
          MRC_SOB_TYPE_CODE,
          CURRENCY_CODE
        INTO P_MRCSOBTYPE,LP_CURRENCY_CODE
        FROM
          GL_SETS_OF_BOOKS
        WHERE SET_OF_BOOKS_ID = P_CA_SET_OF_BOOKS_ID;

Line: 1310

    MODE="SELECT"
    DISPLAY="ALL"');*/