[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
smvk 24-Feb-2003 Bug # 2758823. DL 03-04 Updates.
***************************************************************/
INV_HDR_OR_TLR EXCEPTION;
SELECT RTRIM(LTRIM(SUBSTR(record_data, 15, 8))) message_class,
RTRIM(LTRIM(SUBSTR(record_data, 23, 2))) batch_type,
RTRIM(LTRIM(SUBSTR(record_data, 23, 23))) batch_id,
to_date(SUBSTR(record_data, 46, 16),'YYYYMMDDHH24MISS') bth_creation_date,
RTRIM(LTRIM(SUBSTR(record_data, 60, 2))) batch_rej_code
FROM igf_sl_load_file_t
WHERE lort_id = 1
AND record_data LIKE 'DL HEADER%'
AND file_type = cp_c_file_type;
SELECT lort_id last_lort_id,
RTRIM(SUBSTR(record_data,15,7)) number_rec,
RTRIM(SUBSTR(record_data,22,5)) accept_rec,
RTRIM(SUBSTR(record_data,27,5)) reject_rec,
RTRIM(SUBSTR(record_data,32,5)) pending_rec
FROM igf_sl_load_file_t
WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t)
AND record_data LIKE 'DL TRAILER%'
AND file_type = cp_c_file_type;
SELECT record_data FROM igf_sl_load_file_t ,igf_db_ytd_smr ln
WHERE file_type ='DL_YTDS' AND
SUBSTR(record_data,1,1)='Y' AND
SUBSTR(record_data,2,23) =ln.batch_id;
SELECT record_data FROM
igf_sl_load_file_t
WHERE lort_id BETWEEN 2 AND (cp_c_last_lort_id-1)
AND file_type ='DL_YTDS'
AND SUBSTR(record_data,1,1)='Y'
AND SUBSTR(record_data,25,6)=p_school_code
ORDER BY lort_id ;
insert into the igf_db_ytd_smr_all table */
IF (l_n_last_lort_id-2) <> l_n_rec_num THEN
RAISE INV_TRL_NUM;
igf_db_ytd_smr_pkg.insert_row(
x_mode => 'R',
x_rowid => l_rowid,
x_ytds_id =>l_ytds_id,
x_dl_version =>l_dl_ytds_rec.dl_version ,
x_record_type =>l_dl_ytds_rec.record_type ,
x_batch_id =>l_dl_ytds_rec.batch_id ,
x_school_code =>l_dl_ytds_rec.school_code ,
x_stat_end_dt =>l_dl_ytds_rec.stat_end_dt ,
x_process_dt =>l_dl_ytds_rec.process_dt ,
x_disb_smr_type =>l_dl_ytds_rec.disb_smr_type ,
x_bkd_gross =>l_dl_ytds_rec.bkd_gross ,
x_bkd_fee =>l_dl_ytds_rec.bkd_fee ,
x_bkd_int_rebate =>l_dl_ytds_rec.bkd_int_rebate ,
x_bkd_net =>l_dl_ytds_rec.bkd_net ,
x_unbkd_gross =>l_dl_ytds_rec.unbkd_gross ,
x_unbkd_fee =>l_dl_ytds_rec.unbkd_fee ,
x_unbkd_int_rebate =>l_dl_ytds_rec.unbkd_int_rebate ,
x_unbkd_net =>l_dl_ytds_rec.unbkd_net ,
x_region_code =>l_dl_ytds_rec.region_code ,
x_state_code =>l_dl_ytds_rec.state_code ,
x_rec_count =>l_dl_ytds_rec.rec_count
);
SELECT record_data FROM igf_sl_load_file_t ,igf_db_ytd_dtl ln
WHERE file_type ='DL_YTDD' AND
SUBSTR(record_data,1,1)='D' AND
SUBSTR(record_data,2,23) =ln.batch_id ;
SELECT record_data FROM
igf_sl_load_file_t , igf_sl_loans_v sl
WHERE lort_id BETWEEN 2 AND (cp_c_last_lort_id-1)
AND file_type ='DL_YTDD'
AND SUBSTR(record_data,1,1)='D'
AND SUBSTR(record_data,25,6)=p_school_code -- To select only those records who have a school code passed as parameter
AND SUBSTR(record_data,51,21)=sl.loan_number--who have a valid loan number in the system(igf_sl_loans_v)
ORDER BY lort_id ;
insert into the igf_db_ytd_dtl table */
IF (l_n_last_lort_id-2) <> l_n_rec_num THEN
RAISE INV_TRL_NUM;
igf_db_ytd_dtl_pkg.insert_row(
x_mode => 'R',
x_rowid => l_rowid,
x_ytdd_id => l_ytdd_id,
x_dl_version => l_dl_ytds_rec_det.dl_version ,
x_record_type => l_dl_ytds_rec_det.record_type ,
x_batch_id => l_dl_ytds_rec_det.batch_id ,
x_school_code => l_dl_ytds_rec_det.school_code ,
x_stat_end_dt => l_dl_ytds_rec_det.stat_end_dt ,
x_process_dt => l_dl_ytds_rec_det.process_dt ,
x_loan_number => l_dl_ytds_rec_det.loan_number ,
x_loan_bkd_dt => l_dl_ytds_rec_det.loan_bkd_dt ,
x_disb_bkd_dt => l_dl_ytds_rec_det.disb_bkd_dt ,
x_disb_gross => l_dl_ytds_rec_det.disb_gross ,
x_disb_fee => l_dl_ytds_rec_det.disb_fee ,
x_disb_int_rebate => l_dl_ytds_rec_det.disb_int_rebate ,
x_disb_net => l_dl_ytds_rec_det.disb_net ,
x_disb_net_adj => l_dl_ytds_rec_det.disb_net_adj ,
x_disb_num => l_dl_ytds_rec_det.disb_num ,
x_disb_seq_num => l_dl_ytds_rec_det.disb_seq_num ,
x_trans_type => l_dl_ytds_rec_det.trans_type ,
x_trans_dt => l_dl_ytds_rec_det.trans_dt ,
x_total_gross => l_dl_ytds_rec_det.total_gross ,
x_total_fee => l_dl_ytds_rec_det.total_fee ,
x_total_int_rebate => l_dl_ytds_rec_det.total_int_rebate ,
x_total_net => l_dl_ytds_rec_det.total_net ,
x_region_code => l_dl_ytds_rec_det.region_code ,
x_state_code => l_dl_ytds_rec_det.state_code ,
x_rec_count => l_dl_ytds_rec_det.rec_count
);
SELECT dl_version , ci_alternate_code FROM
igf_sl_dl_setup_v
WHERE
ci_cal_type =l_ci_cal_type AND
ci_sequence_number =l_ci_sequence_number ;
SELECT dl_version , ci_alternate_code FROM
igf_sl_dl_setup_v
WHERE
ci_cal_type =l_ci_cal_type AND
ci_sequence_number =l_ci_sequence_number ;