APPS.FII_CURR_CONV_MAINTAIN_PKG SQL Statements

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

Line: 99

     select start_date
     into g_current_start_date
     from FII_TIME_MONTH
     where end_date >= g_global_start_date
     and sysdate between start_date and end_date;

Line: 131

         SELECT
            PERIOD_SET_NAME
          , PERIOD_ID
          , PERIOD_START_DATE
          , PERIOD_END_DATE
          , FROM_CURRENCY
          , TO_CURRENCY
          , CONVERSION_TYPE
          , CONVERSION_RATE
          , CURRENCY_TYPE
          , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
         FROM FII_CURR_CONV_RATES
         WHERE 1=0;

Line: 167

         SELECT
            PERIOD_SET_NAME
          , PERIOD_ID
          , PERIOD_START_DATE
          , PERIOD_END_DATE
          , FROM_CURRENCY
          , TO_CURRENCY
          , CONVERSION_TYPE
          , CONVERSION_RATE
          , CURRENCY_TYPE
          , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
         FROM FII_CURR_CONV_RATES_GT
         MINUS
         SELECT
            PERIOD_SET_NAME
          , PERIOD_ID
          , PERIOD_START_DATE
          , PERIOD_END_DATE
          , FROM_CURRENCY
          , TO_CURRENCY
          , CONVERSION_TYPE
          , CONVERSION_RATE
          , CURRENCY_TYPE
          , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
         FROM FII_CURR_CONV_RATES
         WHERE CONVERSION_RATE < 0;

Line: 197

         SELECT
            PERIOD_SET_NAME
          , PERIOD_ID
          , PERIOD_START_DATE
          , PERIOD_END_DATE
          , FROM_CURRENCY
          , TO_CURRENCY
          , CONVERSION_TYPE
          , CONVERSION_RATE
          , CURRENCY_TYPE
          , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
         FROM FII_CURR_CONV_RATES
         WHERE CONVERSION_RATE < 0;

Line: 297

    g_phase := 'Insert into FII_CURR_CONV_RATES';

Line: 299

    insert into FII_CURR_CONV_RATES (
        PERIOD_SET_NAME
      , PERIOD_ID
      , PERIOD_START_DATE
      , PERIOD_END_DATE
      , FROM_CURRENCY
      , TO_CURRENCY
      , CONVERSION_TYPE
      , CONVERSION_RATE
      , CURRENCY_TYPE
      , CREATION_DATE
      , CREATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_LOGIN
      )
    select
        'Gregorian'
      , prd.MONTH_ID
      , prd.START_DATE
      , prd.END_DATE
      , cfr.CURRENCY
      , cto.currency_code
      , cto.rate_type
      , decode( cfr.CURRENCY, 'NA_EDW', 1,
          GL_CURRENCY_API.get_rate_sql(
            nvl(cfr.CURRENCY, '')
          , cto.currency_code
          , least(prd.START_DATE, g_current_start_date)
          , cto.rate_type
          )
        )
      , decode(cto.currency_code, g_prim_currency, 'P', 'S')
      , SYSDATE
      , FII_USER_ID
      , SYSDATE
      , FII_USER_ID
      , FII_LOGIN_ID
    from FII_TIME_MONTH prd
       , HRI_CONVERT_FROM_CURRENCIES_V cfr
       , ( select g_prim_currency currency_code, g_prim_rate_type rate_type
           from dual
           union all
           select g_sec_currency  currency_code, g_sec_rate_type  rate_type
           from dual
           where g_sec_currency is not null
           and  g_sec_rate_type is not null
         ) cto
    where prd.end_date >= g_global_start_date;

Line: 350

        FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT ||
                           ' rows into FII_CURR_CONV_RATES');

Line: 356

    insert into FII_CURR_CONV_RATES_GT (
        PERIOD_SET_NAME
      , PERIOD_ID
      , PERIOD_START_DATE
      , PERIOD_END_DATE
      , FROM_CURRENCY
      , TO_CURRENCY
      , CONVERSION_TYPE
      , CONVERSION_RATE
      , CURRENCY_TYPE
      )
    SELECT
        PERIOD_SET_NAME
      , PERIOD_ID
      , PERIOD_START_DATE
      , PERIOD_END_DATE
      , FROM_CURRENCY
      , TO_CURRENCY
      , CONVERSION_TYPE
      , CONVERSION_RATE
      , CURRENCY_TYPE
    FROM FII_CURR_CONV_RATES
    WHERE CONVERSION_RATE < 0;

Line: 381

        FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT ||
                           ' rows into FII_CURR_CONV_RATES_GT');

Line: 385

    g_phase := 'Update FII_CURR_CONV_RATES';

Line: 387

    update FII_CURR_CONV_RATES
    set CONVERSION_RATE =
        GL_CURRENCY_API.get_closest_rate_sql(
            nvl(FROM_CURRENCY, '')
          , TO_CURRENCY
          , least(PERIOD_START_DATE, g_current_start_date)
          , CONVERSION_TYPE
          , least(PERIOD_START_DATE, g_current_start_date) - g_global_start_date
        )
    where CONVERSION_RATE < 0
    and least(PERIOD_START_DATE, g_current_start_date) > g_global_start_date;

Line: 400

        FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT ||
                           ' rows in FII_CURR_CONV_RATES');