[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
SELECT rowid,a.*
FROM igs_ad_acadint_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE igs_ad_acadint_int a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_acad_interest b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.field_of_study = a.field_of_study );
UPDATE igs_ad_acadint_int a
SET status = '3',
error_code = 'E420',
error_text = l_error_text1
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NOT EXISTS ( SELECT 'X'
FROM igs_ps_fld_of_study_all b
WHERE b.field_of_study = a.field_of_study
AND b.closed_ind = 'N' );
UPDATE igs_ad_acadint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = c_acadint_rec.rowid;
igs_ad_acad_interest_pkg.insert_row
(
x_rowid => l_rowid,
x_acad_interest_id => l_acad_int_id,
x_person_id => c_acadint_rec.person_id ,
x_admission_appl_number => c_acadint_rec.admission_appl_number,
x_field_of_study => c_acadint_rec.field_of_study ,
x_mode => 'R'
);
UPDATE igs_ad_acadint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = c_acadint_rec.rowid;
UPDATE igs_ad_acadint_int
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = c_acadint_rec.rowid;
UPDATE igs_ad_acadint_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = c_acadint_rec.rowid;
UPDATE igs_ad_acadint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = c_acadint_rec.rowid;
UPDATE igs_ad_acadint_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = c_acadint_rec.rowid;
SELECT rowid,a.*
FROM igs_ad_appint_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE igs_ad_appint_int a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_app_intent b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.intent_type_id = a.intent_type_id);
UPDATE igs_ad_appint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_code)
WHERE rowid = l_appint_rec.rowid;
UPDATE igs_ad_appint_int
SET status = '3',
error_code = 'E423',
error_text = l_error_text1
WHERE rowid = l_appint_rec.rowid;
igs_ad_app_intent_pkg.insert_row (
x_rowid => l_rowid,
x_app_intent_id => l_app_int_id,
x_person_id => l_appint_rec.person_id,
x_admission_appl_number => l_appint_rec.admission_appl_number,
x_intent_type_id => l_appint_rec.intent_type_id,
x_attribute_category => l_appint_rec.attribute_category,
x_attribute1 => l_appint_rec.attribute1,
x_attribute2 => l_appint_rec.attribute2,
x_attribute3 => l_appint_rec.attribute3,
x_attribute4 => l_appint_rec.attribute4,
x_attribute5 => l_appint_rec.attribute5,
x_attribute6 => l_appint_rec.attribute6,
x_attribute7 => l_appint_rec.attribute7,
x_attribute8 => l_appint_rec.attribute8,
x_attribute9 => l_appint_rec.attribute9,
x_attribute10 => l_appint_rec.attribute10,
x_attribute11 => l_appint_rec.attribute11,
x_attribute12 => l_appint_rec.attribute12,
x_attribute13 => l_appint_rec.attribute13,
x_attribute14 => l_appint_rec.attribute14,
x_attribute15 => l_appint_rec.attribute15,
x_attribute16 => l_appint_rec.attribute16,
x_attribute17 => l_appint_rec.attribute17,
x_attribute18 => l_appint_rec.attribute18,
x_attribute19 => l_appint_rec.attribute19,
x_attribute20 => l_appint_rec.attribute20,
x_mode => 'R'
);
UPDATE igs_ad_appint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = l_appint_rec.rowid;
UPDATE igs_ad_appint_int
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = l_appint_rec.rowid;
UPDATE igs_ad_appint_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = l_appint_rec.rowid;
UPDATE igs_ad_appint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_appint_rec.rowid;
UPDATE igs_ad_appint_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_appint_rec.rowid;
SELECT rowid,a.*
FROM igs_ad_othinst_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE igs_ad_othinst_int a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_other_inst b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.institution_code = a.institution_cd );
UPDATE igs_ad_othinst_int a
SET status = '3',
error_code = 'E425',
error_text = l_error_text1
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NOT EXISTS (SELECT 'X'
FROM IGS_OR_INST_ORG_BASE_V b
WHERE a.institution_cd = b.party_number -- 5386694 (was wrongly compared with "ou_institution_cd" from the bug 4947103)
AND b.inst_org_ind = 'I'
AND institution_status IN
(SELECT institution_status
FROM igs_or_inst_stat
WHERE s_institution_status = 'ACTIVE')
);
UPDATE igs_ad_othinst_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_oth_inst_rec.rowid;
igs_ad_other_inst_pkg.insert_row
(
x_rowid => l_rowid,
x_other_inst_id => l_oth_inst_id,
x_person_id => l_oth_inst_rec.person_id,
x_admission_appl_number => l_oth_inst_rec.admission_appl_number,
x_institution_code => l_oth_inst_rec.institution_cd,
x_mode => 'R'
);
UPDATE igs_ad_othinst_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = l_oth_inst_rec.rowid;
UPDATE igs_ad_othinst_int
SET status = cst_s_val_4,
error_code =cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = l_oth_inst_rec.rowid;
UPDATE igs_ad_othinst_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = l_oth_inst_rec.rowid;
UPDATE igs_ad_othinst_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_oth_inst_rec.rowid;
UPDATE igs_ad_othinst_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_oth_inst_rec.rowid;
SELECT rowid,a.*
FROM igs_ad_splint_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE IGS_AD_SPLINT_INT a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_spl_interests b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.special_interest_type_id = a.special_interest_type_id);
UPDATE igs_ad_splint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_spl_intrst_rec.rowid;
igs_ad_spl_interests_pkg.insert_row (
x_rowid => l_rowid,
x_spl_interest_id => l_spl_int_id ,
x_person_id => l_spl_intrst_rec.person_id ,
x_admission_appl_number => l_spl_intrst_rec.admission_appl_number ,
x_special_interest_type_id => l_spl_intrst_rec.special_interest_type_id ,
x_mode => 'R'
);
UPDATE IGS_AD_SPLINT_INT
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = l_spl_intrst_rec.rowid;
UPDATE IGS_AD_SPLINT_INT
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = l_spl_intrst_rec.rowid;
UPDATE IGS_AD_SPLINT_INT
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = l_spl_intrst_rec.rowid;
UPDATE igs_ad_splint_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_spl_intrst_rec.rowid;
UPDATE IGS_AD_SPLINT_INT
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_spl_intrst_rec.rowid;
SELECT rowid,a.*
FROM igs_ad_spltal_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE IGS_AD_SPLTAL_INT a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_spl_talents b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.special_talent_type_id = a.special_talent_type_id);
UPDATE igs_ad_spltal_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_spl_tal_rec.rowid;
igs_ad_spl_talents_pkg.insert_row(
x_rowid => l_rowid,
x_spl_talent_id => l_spl_tal_id,
x_person_id => l_spl_tal_rec.person_id,
x_admission_appl_number => l_spl_tal_rec.admission_appl_number ,
x_special_talent_type_id => l_spl_tal_rec.special_talent_type_id,
x_mode => 'R' );
UPDATE IGS_AD_SPLTAL_INT
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = l_spl_tal_rec.rowid;
UPDATE IGS_AD_SPLTAL_INT
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = l_SPL_TAL_REC.rowid;
UPDATE igs_ad_spltal_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = l_spl_tal_rec.rowid;
UPDATE igs_ad_spltal_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E701,
error_text = NVL(l_error_text,l_error_text1)
WHERE rowid = l_spl_tal_rec.rowid;
UPDATE IGS_AD_SPLTAL_INT
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_spl_tal_rec.rowid;
SELECT rowid,a.*
FROM igs_ad_perstmt_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2';
UPDATE igs_ad_perstmt_int a
SET status = '3',
error_code = 'E678',
error_text = l_error_text1
WHERE
interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS (SELECT 1 FROM igs_ad_appl_perstat b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.persl_stat_type = a.persl_stat_type
AND TRUNC(b.date_received) = TRUNC(a.date_received));
igs_ad_appl_perstat_pkg.insert_row(
x_rowid => l_rowid,
x_appl_perstat_id => l_appl_perstat_id,
x_person_id => l_appl_pers_rec.person_id,
x_admission_appl_number => l_appl_pers_rec.admission_appl_number,
x_persl_stat_type => l_appl_pers_rec.persl_stat_type,
x_date_received => TRUNC(l_appl_pers_rec.date_received),
x_mode => 'R');
UPDATE igs_ad_perstmt_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = l_appl_pers_rec.rowid;
UPDATE igs_ad_perstmt_int
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = l_appl_pers_rec.rowid;
UPDATE igs_ad_perstmt_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = l_appl_pers_rec.rowid;
UPDATE igs_ad_perstmt_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_appl_pers_rec.rowid;
SELECT cst_insert dmlmode,rowid,a.*
FROM igs_ad_fee_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND (
( NVL(match_ind,'15') = '15'
AND NOT EXISTS (SELECT 1 FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date))
)
OR (p_rule = 'R'
AND match_ind IN ('16','25')
)
)
UNION ALL
SELECT cst_update dmlmode, rowid, a.*
FROM igs_ad_fee_int a
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND (
(p_rule = 'I')
OR (p_rule = 'R' AND match_ind = '21')
)
AND EXISTS ( SELECT 1 FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date)
);
SELECT rowid, appreq.*
FROM igs_ad_app_req appreq
WHERE person_id = l_fee_int_rec.person_id
AND admission_appl_number = l_fee_int_rec.admission_appl_number
AND applicant_fee_type = l_fee_int_rec.applicant_fee_type_id
AND applicant_fee_status = l_fee_int_rec.applicant_fee_status_id
AND TRUNC(fee_date) = TRUNC(l_fee_int_rec.fee_date);
UPDATE igs_ad_fee_int
SET status = '3',
error_code = 'E700',
error_text = l_error_text1
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND NVL(match_ind,'15') <> '15';
UPDATE igs_ad_fee_int
SET status = '1',
error_code = NULL,
error_text = NULL
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND match_ind IN ('17','18','19','22','23','24','27');
UPDATE igs_ad_fee_int a
SET status = '3'
,error_code = 'E681',
error_text = l_error_text1
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND 1 < (SELECT COUNT (*) FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
UPDATE igs_ad_fee_int a
SET status = '1',
error_code = NULL,
error_text = NULL,
match_ind = '19'
WHERE interface_run_id = p_interface_run_id
AND status = '2'
AND EXISTS ( SELECT 1 FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
IF l_fee_int_rec.dmlmode = cst_insert THEN
l_rowid := NULL;
igs_ad_app_req_pkg.insert_row(
x_rowid => l_rowid,
x_app_req_id => l_app_req_id,
x_person_id => l_fee_int_rec.person_id,
x_admission_appl_number => l_fee_int_rec.admission_appl_number,
x_applicant_fee_type => l_fee_int_rec.applicant_fee_type_id,
x_applicant_fee_status => l_fee_int_rec.applicant_fee_status_id,
x_fee_date => TRUNC(l_fee_int_rec.fee_date),
x_fee_payment_method => NULL,
x_fee_amount => l_fee_int_rec.fee_amount,
x_reference_num => l_fee_int_rec.reference_num,
x_mode => 'R',
x_credit_card_code => NULL,
x_credit_card_holder_name => NULL,
x_credit_card_number => NULL,
x_credit_card_expiration_date => NULL,
x_rev_gl_ccid => NULL,
x_cash_gl_ccid => NULL,
x_rev_account_cd => NULL,
x_cash_account_cd => NULL,
x_gl_date => NULL,
x_gl_posted_date => NULL,
x_posting_control_id => NULL,
x_credit_card_tangible_cd => NULL,
x_credit_card_payee_cd => NULL,
x_credit_card_status_code => NULL
);
ELSIF l_fee_int_rec.dmlmode = cst_update THEN
OPEN c_dup_recd(l_fee_int_rec);
igs_ad_app_req_pkg.update_row(
x_rowid => l_dup_recd.rowid,
x_app_req_id => l_dup_recd.app_req_id,
x_person_id => l_fee_int_rec.PERSON_ID,
x_admission_appl_numbeR => l_fee_int_rec.admission_appl_number,
x_applicant_fee_type => NVL(l_fee_int_rec.applicant_fee_type_id, l_dup_recd.applicant_fee_type),
x_applicant_fee_status => NVL(l_fee_int_rec.applicant_fee_status_id, l_dup_recd.applicant_fee_status),
x_fee_date => TRUNC(NVL(l_fee_int_rec.fee_date, l_dup_recd.fee_date)),
x_fee_payment_method => l_dup_recd.fee_payment_method,
x_fee_amount => NVL(l_fee_int_rec.fee_amount, l_dup_recd.fee_amount),
x_reference_num => NVL(l_fee_int_rec.reference_num, l_dup_recd.reference_num),
x_mode => 'R',
x_credit_card_code => l_dup_recd.credit_card_code,
x_credit_card_holder_name => l_dup_recd.credit_card_holder_name,
x_credit_card_number => l_dup_recd.credit_card_number,
x_credit_card_expiration_date => l_dup_recd.credit_card_expiration_date,
x_rev_gl_ccid => l_dup_recd.rev_gl_ccid,
x_cash_gl_ccid => l_dup_recd.cash_gl_ccid,
x_rev_account_cd => l_dup_recd.rev_account_cd,
x_cash_account_cd => l_dup_recd.cash_account_cd,
x_gl_date => l_dup_recd.gl_date,
x_gl_posted_date => l_dup_recd.gl_posted_date,
x_posting_control_id => l_dup_recd.posting_control_id,
x_credit_card_tangible_cd => l_dup_recd.credit_card_tangible_cd,
x_credit_card_payee_cd => l_dup_recd.credit_card_payee_cd,
x_credit_card_status_code => l_dup_recd.credit_card_status_code
);
UPDATE igs_ad_fee_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL,
match_ind = DECODE (l_fee_int_rec.dmlmode, cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
WHERE rowid = l_fee_int_rec.rowid;
IF l_fee_int_rec.dmlmode = cst_update THEN
l_error_code := 'E014';
ELSIF l_fee_int_rec.dmlmode = cst_insert THEN
l_error_code := 'E322';
UPDATE igs_ad_fee_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = l_fee_int_rec.rowid;
UPDATE igs_ad_fee_int a
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL,
match_ind = cst_mi_val_23
WHERE interface_run_id = p_interface_run_id
AND p_rule = 'R'
AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
AND EXISTS (
SELECT rowid FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date)
AND NVL(b.reference_num, '-1') = NVL( NVL(a.reference_num, b.reference_num ) , -1)
AND b.fee_amount = a.fee_amount);
UPDATE igs_ad_fee_int a
SET
status = cst_s_val_3
, match_ind = cst_mi_val_20
, dup_app_req_id = ( SELECT APP_REQ_ID FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date))
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
AND EXISTS ( SELECT rowid FROM igs_ad_app_req b
WHERE b.person_id = a.person_id
AND b.admission_appl_number = a.admission_appl_number
AND b.applicant_fee_type = a.applicant_fee_type_id
AND b.applicant_fee_status = a.applicant_fee_status_id
AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
UPDATE igs_ad_fee_int
SET
status = cst_s_val_3
, error_code = cst_ec_val_E700,
error_text = l_error_text1
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND match_ind IS NOT NULL;