[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT batch_desc
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT batch_year,
award_year_status_code,
sys_award_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT A.batch_num batch_id,
A.agrint_id agrint_id,
A.ci_alternate_code ci_alternate_code,
A.person_number person_number,
A.fund_code fund_code,
A.offered_amt offrd_amt,
A.accepted_amt accpt_amt,
A.paid_amt paid_amt,
A.import_status_type import_status,
A.import_record_type import_record_type,
A.ROWID ROW_ID
FROM igf_aw_li_agr_ints A
WHERE A.batch_num = cp_batch_id
AND A.ci_alternate_code = cp_alternate_code
AND A.import_status_type IN ('U','R')
ORDER BY A.person_number;
SELECT fund_id
FROM igf_aw_fund_mast FM
WHERE FM.ci_cal_type = cp_cal_type
AND FM.ci_sequence_number = cp_seq_number
AND FM.fund_code = cp_fund_code ;
SELECT award_id,
AW.ROWID ROW_ID
FROM igf_aw_award AW
WHERE AW.base_id = cp_base_id
AND AW.fund_id = cp_fund_id;
|| veramach 1-NOV-2003 #3160568 Added adplans_id in the calls to igf_aw_award_pkg.insert_row and igf_aw_award_pkg.update_row
|| (reverse chronological order - newest change first)
*/
BEGIN
igf_aw_gen.set_org_id(NULL);
IF NVL(l_interface.import_record_type,'X') = 'U' THEN -- AS THE AWARD DOES NOT EXIST SO IT CAN NOT BE UPDATED
fnd_message.set_name('IGF','IGF_SE_FUND_CODE');
igf_aw_award_pkg.insert_row(
x_mode => 'R',
x_award_id => lv_award_id,
x_fund_id => l_fund_id.fund_id,
x_base_id => lv_base_id,
x_rowid => lv_row_id,
x_offered_amt => l_interface.offrd_amt,
x_accepted_amt => l_interface.accpt_amt,
x_paid_amt => l_interface.paid_amt,
x_batch_id => l_interface.batch_id,
x_packaging_type => NULL,
x_manual_update => NULL,
x_rules_override => NULL,
x_award_date => NULL,
x_award_status => 'ACCEPTED',
x_rvsn_id => NULL,
x_alt_pell_schedule => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_award_number_txt => NULL,
x_legacy_record_flag => 'Y',
x_adplans_id => NULL,
x_lock_award_flag => 'N',
x_app_trans_num_txt => NULL,
x_awd_proc_status_code => NULL,
x_notification_status_code => NULL,
x_notification_status_date => NULL,
x_publish_in_ss_flag => 'N'
);
l_debug_str := l_debug_str || ' Record Inserted';
igf_aw_award_pkg.update_row(
x_mode => 'R',
x_award_id => l_award_id.award_id,
x_fund_id => l_fund_id.fund_id,
x_base_id => lv_base_id,
x_rowid => l_award_id.ROW_ID,
x_offered_amt => l_interface.offrd_amt,
x_accepted_amt => l_interface.accpt_amt,
x_paid_amt => l_interface.paid_amt,
x_batch_id => l_interface.batch_id,
x_packaging_type => NULL,
x_manual_update => NULL,
x_rules_override => NULL,
x_award_date => NULL,
x_award_status => 'ACCEPTED',
x_rvsn_id => NULL,
x_alt_pell_schedule => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_award_number_txt => NULL,
x_legacy_record_flag => 'Y',
x_adplans_id => NULL,
x_lock_award_flag => 'N',
x_app_trans_num_txt => NULL,
x_awd_proc_status_code => NULL,
x_notification_status_code => NULL,
x_notification_status_date => NULL,
x_publish_in_ss_flag => 'N'
);
l_debug_str := l_debug_str || ' Record Updated';
UPDATE igf_aw_li_agr_ints
SET import_status_type = 'E'
WHERE ROWID = l_interface.ROW_ID;
DELETE FROM igf_aw_li_agr_ints
WHERE ROWID = l_interface.ROW_ID;
l_debug_str := l_debug_str || ' Record Deleted from interface table';
UPDATE igf_aw_li_agr_ints
SET import_status_type = 'I'
WHERE ROWID = l_interface.ROW_ID;