[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
In the call to igf_db_dl_disb_resp_pkg.insert_row passed NULL to x_sch_code_status,x_loan_num_status,
x_disb_num_status,x_trans_date_status,x_trans_date_status.
mesriniv 19-MAR-2002 Added Version 2002-2003 check
(reverse chronological order - newest change first)
***************************************************************/
l_temp VARCHAR2(30);
SELECT RTRIM(SUBSTR(record_data, 23, 23)) batch_id,
RTRIM(SUBSTR(record_data, 15, 8)) message_class,
RTRIM(SUBSTR(record_data, 46, 16)) bth_creation_date,
RTRIM(SUBSTR(record_data, 60, 2)) batch_rej_code,
RTRIM(SUBSTR(record_data, 23, 2)) batch_type
FROM igf_sl_load_file_t
WHERE lort_id = 1
AND record_data LIKE 'DL HEADER%'
AND file_type = 'DL_DISB';
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 = 'DL_DISB';
igf_sl_dl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_dbth_id => l_dbth_id,
x_batch_id => l_rec_batch_id,
x_message_class => l_rec_message_class,
x_bth_creation_date => TO_DATE(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
x_batch_rej_code => l_rec_batch_rej_code,
x_end_date => NULL,
x_batch_type => l_rec_batch_type,
x_send_resp => 'R',
x_status => 'Y'
);
SELECT record_data
FROM igf_sl_load_file_t
WHERE lort_id between 2 AND (l_last_lort_id-1)
AND file_type = 'DL_DISB';
igf_db_dl_disb_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_ddrp_id => x_ddrp_id,
x_dbth_id => l_dbth_id,
x_loan_number => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,1,21))),
x_disb_num => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,22,2)))),
x_disb_activity => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,24,1))),
x_transaction_date => TO_DATE(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,25,8))),'YYYYMMDD'),
x_disb_seq_num => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,33,2)))),
x_disb_gross_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,35,5)))),
x_fee_1 => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,40,5)))),
x_disb_net_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,45,5)))),
x_int_rebate_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,50,5)))),
x_user_ident => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,56,8))),
x_disb_batch_id => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,64,23))),
x_school_id => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,87,6))),
x_sch_code_status => NULL,
x_loan_num_status => NULL,
x_disb_num_status => NULL,
x_disb_activity_status => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,96,10))),
x_trans_date_status => NULL,
x_disb_seq_num_status => NULL,
x_loc_disb_gross_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,108,5)))),
x_loc_fee_1 => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,113,5)))),
x_loc_disb_net_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,118,5)))),
x_servicer_refund_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,123,6)))),
x_loc_int_rebate_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,130,5)))),
x_loc_net_booked_loan => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,139,5))),
x_ack_date => TO_DATE(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,144,8))),'YYYYMMDD'),
x_affirm_flag => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,152,1))),
x_status => 'N'
);
SELECT *
FROM igf_db_dl_disb_resp
WHERE dbth_id = l_dbth_id
AND status = 'N';
SELECT adcd.*
FROM igf_aw_db_chg_dtls adcd,
igf_sl_loans sl
WHERE
sl.loan_number = l_loan_number AND
adcd.award_id = sl.award_id AND
adcd.disb_num = l_disb_num AND
adcd.disb_seq_num = l_disb_seq_num;
SELECT ci_cal_type, ci_sequence_number
FROM igf_sl_dl_setup
WHERE dl_version = cp_dl_version;
SELECT adcd.*,adcd.ROWID
FROM igf_aw_db_chg_dtls adcd
WHERE adcd.award_id = lcur_awdisb.award_id
AND adcd.disb_num = lcur_db_resp.disb_num
AND adcd.disb_seq_num = lcur_db_resp.disb_seq_num
FOR UPDATE OF adcd.award_id NOWAIT;
igf_aw_db_chg_dtls_pkg.update_row (
x_rowid => tbh_rec.ROWID,
x_award_id => tbh_rec.award_id,
x_disb_num => tbh_rec.disb_num,
x_disb_seq_num => tbh_rec.disb_seq_num,
x_disb_accepted_amt => tbh_rec.disb_accepted_amt,
x_orig_fee_amt => tbh_rec.orig_fee_amt,
x_disb_net_amt => tbh_rec.disb_net_amt,
x_disb_date => tbh_rec.disb_date,
x_disb_activity => tbh_rec.disb_activity,
x_disb_status => l_disb_status,
x_disb_status_date => l_disb_status_date,
x_disb_rel_flag => tbh_rec.disb_rel_flag,
x_first_disb_flag => tbh_rec.first_disb_flag,
x_interest_rebate_amt => tbh_rec.interest_rebate_amt,
x_disb_conf_flag => tbh_rec.disb_conf_flag,
x_pymnt_prd_start_date => tbh_rec.pymnt_prd_start_date,
x_note_message => tbh_rec.note_message,
x_batch_id_txt => tbh_rec.batch_id_txt,
x_ack_date => l_disb_ack_date,
x_booking_id_txt => tbh_rec.booking_id_txt,
x_booking_date => tbh_rec.booking_date,
x_mode => 'R');
SELECT adcd.*, adcd.ROWID
FROM igf_aw_db_chg_dtls adcd
WHERE adcd.award_id = lcur_awdisb.award_id
AND adcd.disb_num = lcur_db_resp.disb_num
AND adcd.disb_seq_num = lcur_db_resp.disb_seq_num
FOR UPDATE OF adcd.award_id NOWAIT;
igf_aw_db_chg_dtls_pkg.update_row (
x_rowid => tbh_recss.ROWID,
x_award_id => tbh_recss.award_id,
x_disb_num => tbh_recss.disb_num,
x_disb_seq_num => tbh_recss.disb_seq_num,
x_disb_accepted_amt => tbh_recss.disb_accepted_amt,
x_orig_fee_amt => tbh_recss.orig_fee_amt,
x_disb_net_amt => tbh_recss.disb_net_amt,
x_disb_date => tbh_recss.disb_date,
x_disb_activity => tbh_recss.disb_activity,
x_disb_status => 'B',
x_disb_status_date => TRUNC(SYSDATE),
x_disb_rel_flag => tbh_recss.disb_rel_flag,
x_first_disb_flag => tbh_recss.first_disb_flag,
x_interest_rebate_amt => tbh_recss.interest_rebate_amt,
x_disb_conf_flag => tbh_recss.disb_conf_flag,
x_pymnt_prd_start_date => tbh_recss.pymnt_prd_start_date,
x_note_message => tbh_recss.note_message,
x_batch_id_txt => tbh_recss.batch_id_txt,
x_ack_date => l_disb_ack_date,
x_booking_id_txt => l_batch_id,
x_booking_date => lcur_db_resp.transaction_date,
x_mode => 'R');
igf_db_dl_disb_resp_pkg.update_row (
x_mode => 'R',
x_rowid => lcur_db_resp.row_id,
x_ddrp_id => lcur_db_resp.ddrp_id,
x_dbth_id => lcur_db_resp.dbth_id,
x_loan_number => lcur_db_resp.loan_number,
x_disb_num => lcur_db_resp.disb_num,
x_disb_activity => lcur_db_resp.disb_activity,
x_transaction_date => lcur_db_resp.transaction_date,
x_disb_seq_num => lcur_db_resp.disb_seq_num,
x_disb_gross_amt => lcur_db_resp.disb_gross_amt,
x_fee_1 => lcur_db_resp.fee_1,
x_disb_net_amt => lcur_db_resp.disb_net_amt,
x_int_rebate_amt => lcur_db_resp.int_rebate_amt,
x_user_ident => lcur_db_resp.user_ident,
x_disb_batch_id => lcur_db_resp.disb_batch_id,
x_school_id => lcur_db_resp.school_id,
x_sch_code_status => lcur_db_resp.sch_code_status,
x_loan_num_status => lcur_db_resp.loan_num_status,
x_disb_num_status => lcur_db_resp.disb_num_status,
x_disb_activity_status => lcur_db_resp.disb_activity_status,
x_trans_date_status => lcur_db_resp.trans_date_status,
x_disb_seq_num_status => lcur_db_resp.disb_seq_num_status,
x_loc_disb_gross_amt => lcur_db_resp.loc_disb_gross_amt,
x_loc_fee_1 => lcur_db_resp.loc_fee_1,
x_loc_disb_net_amt => lcur_db_resp.loc_disb_net_amt,
x_servicer_refund_amt => lcur_db_resp.servicer_refund_amt,
x_loc_int_rebate_amt => lcur_db_resp.loc_int_rebate_amt,
x_loc_net_booked_loan => lcur_db_resp.loc_net_booked_loan,
x_ack_date => lcur_db_resp.ack_date,
x_affirm_flag => lcur_db_resp.affirm_flag,
x_status =>'Y' );