APPS.FTP_IRC_ADI_MIGRATE SQL Statements

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

Line: 22

 Pgmr  : Karen added: update interfece table status with 'F' when migration failed
 **************************************************************************/
PROCEDURE migrateparameters (
 errbuf   OUT NOCOPY VARCHAR2,
 retcode  OUT NOCOPY VARCHAR2,
 p_int_rate_code IN NUMBER
)
IS

TYPE t_eff_date_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.EFFECTIVE_DATE%TYPE;

Line: 53

select EFFECTIVE_DATE,MEAN_REVERSION_SPEED,
LONG_RUN_RATE,VOLATILITY_MERTON,VOLATILITY_VASICEK
bulk collect into l_eff_date_tbl,l_mean_rev_tbl,l_long_rr_tbl,
l_vol_merton_tbl,l_vol_vasicek_tbl
from FTP_IRC_ADI_PARAM_T where
INTEREST_RATE_CODE = p_int_rate_code;

Line: 63

    UPDATE FTP_IRC_TS_PARAM_HIST SET
    MEAN_REVERSION_SPEED = l_mean_rev_tbl(i),
    LONG_RUN_RATE = l_long_rr_tbl(i),
    VOLATILITY_MERTON = l_vol_merton_tbl(i),
    VOLATILITY_VASICEK = l_vol_vasicek_tbl(i)
    WHERE EFFECTIVE_DATE = l_eff_date_tbl(i) AND
    INTEREST_RATE_CODE = p_int_rate_code;

Line: 73

        INSERT INTO FTP_IRC_TS_PARAM_HIST(EFFECTIVE_DATE,INTEREST_RATE_CODE,
        MEAN_REVERSION_SPEED,LONG_RUN_RATE,VOLATILITY_MERTON,VOLATILITY_VASICEK,
        RATE_DATA_SOURCE_CODE,IS_VALID_FLG,LAST_MODIFIED_DATE,CREATION_DATE,
        CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE)
        VALUES(l_eff_date_tbl(i),p_int_rate_code,l_mean_rev_tbl(i),l_long_rr_tbl(i),
        l_vol_merton_tbl(i),l_vol_vasicek_tbl(i),1,1,sysdate,sysdate,1,1,sysdate);

Line: 81

       update FTP_IRC_ADI_PARAM_T set STATUS ='INSERT'
       where EFFECTIVE_DATE = l_eff_date_tbl(i)
       AND INTEREST_RATE_CODE = p_int_rate_code;

Line: 87

       update FTP_IRC_ADI_PARAM_T set STATUS ='UPDATE'
       where EFFECTIVE_DATE = l_eff_date_tbl(i)
       AND INTEREST_RATE_CODE = p_int_rate_code;

Line: 96

            update FTP_IRC_ADI_PARAM_T set STATUS ='FTP_PARAM_MIGRATE_ERR' where EFFECTIVE_DATE = l_eff_date_tbl(i)
            AND INTEREST_RATE_CODE = p_int_rate_code;

Line: 113

  p_msg_text => 'Successfully inserted/updated rows: '||l_mean_rev_tbl.COUNT
 );

Line: 122

deleteparameters (
 errbuf   => errbuf,
 retcode  => retcode,
 p_int_rate_code => p_int_rate_code
);

Line: 142

 Desc  : Deletes Parameter Rates type of Interest Rate Codes.
 Pgmr  : Raghuram K Nanda
 Date  : 8-Mar-2005
 **************************************************************************/
PROCEDURE deleteparameters (
 errbuf   OUT NOCOPY VARCHAR2,
 retcode  OUT NOCOPY VARCHAR2,
 p_int_rate_code IN NUMBER
)
IS
l_block  CONSTANT  VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.deleteparameters';

Line: 161

DELETE FROM FTP_IRC_ADI_PARAM_T WHERE
INTEREST_RATE_CODE = p_int_rate_code and STATUS IN ('INSERT','UPDATE');

Line: 167

  p_msg_text => 'Successfully deleted rows: '||SQL%ROWCOUNT
 );

Line: 184

END deleteparameters;

Line: 213

l_select_stmt varchar2(1000);

Line: 239

select INTEREST_RATE_TERM_MULT,INTEREST_RATE_TERM
bulk collect into l_int_mults_tbl,l_int_terms_tbl
from FTP_IRC_RATE_TERMS
where INTEREST_RATE_CODE = p_int_rate_code
--Bobby20050719 - UI sorts terms points based on actual length of the term - - Bug 4494361
order by INTEREST_RATE_TERM * decode(INTEREST_RATE_TERM_MULT,'D',1, 'M',30.5, 'Y',365);

Line: 249

select EFFECTIVE_DATE,INT_RATE
bulk collect into l_eff_date_tbl,l_int_rates_tbl
from (
 select INTEREST_RATE_CODE,EFFECTIVE_DATE,decode( COLUMN_VALUE,
       1,INTEREST_RATE1, 2,INTEREST_RATE2,
       3,INTEREST_RATE3, 4,INTEREST_RATE4,
       5,INTEREST_RATE5, 6,INTEREST_RATE6, 7,INTEREST_RATE7,
       8,INTEREST_RATE8, 9,INTEREST_RATE9, 10,INTEREST_RATE10,
       11,INTEREST_RATE11, 12,INTEREST_RATE12,
       13,INTEREST_RATE13, 14,INTEREST_RATE14,
       15,INTEREST_RATE15, 16,INTEREST_RATE16, 17,INTEREST_RATE17,
       18,INTEREST_RATE18, 19,INTEREST_RATE19, 20,INTEREST_RATE20,
       21,INTEREST_RATE21, 22,INTEREST_RATE22,
       23,INTEREST_RATE23, 24,INTEREST_RATE24,
       25,INTEREST_RATE25, 26,INTEREST_RATE26, 27,INTEREST_RATE27,
       28,INTEREST_RATE28, 29,INTEREST_RATE29, 30,INTEREST_RATE30,
       31,INTEREST_RATE31, 32,INTEREST_RATE32,
       33,INTEREST_RATE33, 34,INTEREST_RATE34,
       35,INTEREST_RATE35, 36,INTEREST_RATE36, 37,INTEREST_RATE37,
       38,INTEREST_RATE38, 39,INTEREST_RATE39, 40,INTEREST_RATE40,
       41,INTEREST_RATE41, 42,INTEREST_RATE42,
       43,INTEREST_RATE43, 44,INTEREST_RATE44,
       45,INTEREST_RATE45, 46,INTEREST_RATE46, 47,INTEREST_RATE47,
       48,INTEREST_RATE48, 49,INTEREST_RATE49, 50,INTEREST_RATE50,
       51,INTEREST_RATE51, 52,INTEREST_RATE52,
       53,INTEREST_RATE53, 54,INTEREST_RATE54,
       55,INTEREST_RATE55, 56,INTEREST_RATE56, 57,INTEREST_RATE57,
       58,INTEREST_RATE58, 59,INTEREST_RATE59, 60,INTEREST_RATE60,
       61,INTEREST_RATE61, 62,INTEREST_RATE62,
       63,INTEREST_RATE63, 64,INTEREST_RATE64,
       65,INTEREST_RATE65, 66,INTEREST_RATE66, 67,INTEREST_RATE67,
       68,INTEREST_RATE68, 69,INTEREST_RATE69, 70,INTEREST_RATE70,
       71,INTEREST_RATE71, 72,INTEREST_RATE72,
       73,INTEREST_RATE73, 74,INTEREST_RATE74,
       75,INTEREST_RATE75, 76,INTEREST_RATE76, 77,INTEREST_RATE77,
       78,INTEREST_RATE78, 79,INTEREST_RATE79, 80,INTEREST_RATE80,
       81,INTEREST_RATE81, 82,INTEREST_RATE82,
       83,INTEREST_RATE83, 84,INTEREST_RATE84,
       85,INTEREST_RATE85, 86,INTEREST_RATE86, 87,INTEREST_RATE87,
       88,INTEREST_RATE88, 89,INTEREST_RATE89, 90,INTEREST_RATE90,
       91,INTEREST_RATE91, 92,INTEREST_RATE92,
       93,INTEREST_RATE93, 94,INTEREST_RATE94,
       95,INTEREST_RATE95, 96,INTEREST_RATE96, 97,INTEREST_RATE97,
       98,INTEREST_RATE98, 99,INTEREST_RATE99, 100,INTEREST_RATE100
      ) int_rate
 from FTP_IRC_ADI_RATE_T, TABLE(FTP_MULTI_TABLE_F())
 --Bobby20050719 - Interest rate should be in the same order as it interface table - Bug 4494361
 where INTEREST_RATE_CODE = p_int_rate_code order by EFFECTIVE_DATE,COLUMN_VALUE )
 where INT_RATE is not null;

Line: 315

    UPDATE FTP_IRC_RATE_HIST SET
    INTEREST_RATE = l_int_rates_tbl(i)
    where INTEREST_RATE_TERM = l_int_term and
    INTEREST_RATE_TERM_MULT = l_int_mult and
    EFFECTIVE_DATE = l_eff_date_tbl(i) and
    INTEREST_RATE_CODE = p_int_rate_code;

Line: 324

        insert into FTP_IRC_RATE_HIST(EFFECTIVE_DATE,INTEREST_RATE_CODE,
        INTEREST_RATE_TERM,INTEREST_RATE_TERM_MULT,INTEREST_RATE,
        RATE_DATA_SOURCE_CODE,LAST_MODIFIED_DATE,CREATION_DATE,CREATED_BY,
        LAST_UPDATED_BY,LAST_UPDATE_DATE )
        values(l_eff_date_tbl(i),p_int_rate_code,l_int_term,
        l_int_mult,l_int_rates_tbl(i),1,sysdate,sysdate,1,1,sysdate );

Line: 332

       update FTP_IRC_ADI_RATE_T set STATUS ='INSERT'
       where EFFECTIVE_DATE = l_eff_date_tbl(i)
       AND INTEREST_RATE_CODE = p_int_rate_code;

Line: 338

       update FTP_IRC_ADI_RATE_T set STATUS ='UPDATE'
       where EFFECTIVE_DATE = l_eff_date_tbl(i)
       AND INTEREST_RATE_CODE = p_int_rate_code;

Line: 359

  p_msg_text => 'Successfully inserted/updated rows: '||l_int_terms_tbl.COUNT
 );

Line: 367

deletehistrates (
 errbuf   => errbuf,
 retcode  => retcode,
 p_int_rate_code => p_int_rate_code
);

Line: 388

 Desc  : Deletes Historical Rates type of Interest Rate Code.
 Pgmr  : Raghuram K Nanda
 Date  : 8-Mar-2005
 **************************************************************************/
PROCEDURE deletehistrates (
 errbuf   OUT NOCOPY VARCHAR2,
 retcode  OUT NOCOPY VARCHAR2,
 p_int_rate_code IN NUMBER
)
IS
l_block  CONSTANT  VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.deletehistrates';

Line: 407

DELETE FROM FTP_IRC_ADI_RATE_T WHERE INTEREST_RATE_CODE = p_int_rate_code and
STATUS IN ('INSERT','UPDATE');

Line: 413

  p_msg_text => 'Successfully deleted rows: '||SQL%ROWCOUNT
 );

Line: 431

END deletehistrates;