[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number;
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type='IGF_GE_PARAMETERS'
AND lookup_code IN ('AWARD_YEAR','FUND_CODE','PERSON_NUMBER','PARAMETER_PASS');
SELECT party_number
FROM hz_parties hz,
igf_ap_fa_base_rec_all fa
WHERE fa.person_id = hz.party_id
AND fa.base_id = c_base_id;
SELECT fund_code
FROM igf_aw_fund_mast
WHERE fund_id = c_fund_id;
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN ('FUND_CODE','PERSON_NUMBER','AWARD_ID');
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type='IGF_GE_PARAMETERS'
AND lookup_code IN ('BATCH_ID','AUTH_ID','VALIDATION_LVL','PARAMETER_PASS');
SELECT threshold_perct, threshold_value
FROM igf_aw_fund_mast
WHERE fund_id = cv_fund_id;
SELECT pit.api_person_id ssn,
fmast.ci_cal_type,
fmast.ci_sequence_number,
awd.base_id,
awd.award_id
FROM igf_aw_award awd,
igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec farec,
igs_pe_alt_pers_id_v pit,
igs_pe_person_id_typ pit_2
WHERE awd.fund_id = fmast.fund_id
AND awd.base_id = cv_base_id
AND awd.base_id = farec.base_id
AND fcat.fund_code = fmast.fund_code
AND fcat.fed_fund_code = 'FWS'
AND awd.fund_id = cv_fund_id
AND awd.award_id = cv_award_id
AND farec.person_id = pit.pe_person_id (+)
AND pit.person_id_type = pit_2.person_id_type
AND pit_2.s_person_id_type = 'SSN'
AND SYSDATE BETWEEN pit.start_dt AND NVL(pit.end_dt, SYSDATE);
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type = 'IGF_MATCH_CRITERIA'
AND lookup_code = 'SSN';
SELECT person_first_name, person_last_name, address1, address2, address3, address4, city,
state, province, county, country
FROM hz_parties
WHERE party_id = cv_person_id;
SELECT gender sex, birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cv_person_id;
SELECT a.visa_type,a.visa_category,a.visa_number,a.visa_expiry_date, b.visit_start_date entry_date
FROM igs_pe_visa a,igs_pe_visit_histry b
WHERE a.person_id = cv_person_id
AND NVL(a.visa_expiry_date,SYSDATE) >= SYSDATE
AND a.visa_id = b.visa_id
ORDER BY a.visa_expiry_date DESC;
SELECT NVL(marital_status,'NA') marital_status
FROM HZ_PERSON_PROFILES
WHERE party_id = cv_person_id
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE);
SELECT NVL(awd.accepted_amt,0) accepted_amt
FROM igf_aw_award awd
WHERE awd.award_id = cp_award_id;
SELECT igf_se_auth_s1.NEXTVAL
FROM dual;
SELECT auth_id
FROM igf_se_auth
WHERE award_id = cv_award_id
AND auth_id IS NOT NULL
AND flag = 'A';
SELECT rowid, sai.*
FROM igf_se_auth sai
WHERE sai.auth_id = cv_auth_id
AND sai.flag ='A'
FOR UPDATE NOWAIT;
PROCEDURE insert_auth IS
--------------------------------------------------------
--Created by : ssawhney on 2nd Jan
--Purpose : local procedure to insert into igf_se_auth table
--Change History :
--Who When What
----------------------------------------------------------------
l_rowid VARCHAR2(30);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_flag =>' || 'A');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_person_id =>' || l_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_first_name =>' || hzp_rec.person_first_name);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_last_name =>' || hzp_rec.person_last_name);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address1 =>' || NVL(hzp_rec.address1,'NA'));
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address2 =>' || hzp_rec.address2);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address3 =>' || hzp_rec.address3);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_address4 =>' || hzp_rec.address4);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_city =>' || hzp_rec.city);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_state =>' || hzp_rec.state);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_province =>' || hzp_rec.province);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_county =>' || hzp_rec.county);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_country =>' || hzp_rec.country);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_sex =>' || pe_rec.sex);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_birth_dt =>' || NVL(pe_rec.birth_dt,SYSDATE));
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_ssn_no =>' || awd_rec.ssn);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_marital_status =>' || NVL(stat_rec.marital_status,'NA'));
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_type =>' || l_visa_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_category =>' || l_visa_category);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_number =>' || l_visa_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_visa_expiry_dt =>' || l_visa_expiry_dt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_entry_date =>' || l_entry_date);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_fund_id =>' || l_fund_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_threshold_perct =>' || fund_mast_rec.threshold_perct);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_threshold_value =>' || fund_mast_rec.threshold_value);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_accepted_amnt =>' || accept_amnt_rec.accepted_amt);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_aw_cal_type =>' || awd_rec.ci_cal_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_aw_sequence_number =>' || awd_rec.ci_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth','x_award_id =>' || awd_rec.award_id);
igf_se_auth_pkg.insert_row(
x_rowid => l_rowid,
x_sequence_no => l_sequence_no,
x_auth_id => l_auth_id,
x_flag => 'A', -- this is the active record now.
x_person_id => l_person_id,
x_first_name => hzp_rec.person_first_name,
x_last_name => hzp_rec.person_last_name,
x_address1 => NVL(hzp_rec.address1,'NA'),
x_address2 => hzp_rec.address2,
x_address3 => hzp_rec.address3,
x_address4 => hzp_rec.address4,
x_city => hzp_rec.city,
x_state => hzp_rec.state,
x_province => hzp_rec.province,
x_county => hzp_rec.county,
x_country => hzp_rec.country,
x_sex => pe_rec.sex,
x_birth_dt => NVL(pe_rec.birth_dt,SYSDATE),
x_ssn_no => awd_rec.ssn,
x_marital_status => NVL(stat_rec.marital_status,'NA'),
x_visa_type => l_visa_type,
x_visa_category => l_visa_category,
x_visa_number => l_visa_number,
x_visa_expiry_dt => l_visa_expiry_dt,
x_entry_date => l_entry_date,
x_fund_id => l_fund_id,
x_threshold_perct => fund_mast_rec.threshold_perct,
x_threshold_value => fund_mast_rec.threshold_value,
x_accepted_amnt => accept_amnt_rec.accepted_amt,
x_aw_cal_type => awd_rec.ci_cal_type,
x_aw_sequence_number => awd_rec.ci_sequence_number,
x_award_id => awd_rec.award_id,
x_authorization_date => SYSDATE,
x_notification_date => NULL
);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_se_gen_001.send_work_auth.insert_auth',' Unhandled Exception ->' || SQLERRM);
FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth:igf_se_auth.insert') ;
END insert_auth;
insert_auth ;
igf_se_auth_pkg.update_row (
old_auth_rec.rowid,
old_auth_rec.sequence_no,
old_auth_rec.auth_id,
'I', -- this record is inactive now
old_auth_rec.person_id,
old_auth_rec.first_name,
old_auth_rec.last_name,
old_auth_rec.address1,
old_auth_rec.address2,
old_auth_rec.address3,
old_auth_rec.address4,
old_auth_rec.city,
old_auth_rec.state,
old_auth_rec.province,
old_auth_rec.county,
old_auth_rec.country,
old_auth_rec.sex,
old_auth_rec.birth_dt,
old_auth_rec.ssn_no,
old_auth_rec.marital_status,
old_auth_rec.visa_type,
old_auth_rec.visa_category,
old_auth_rec.visa_number,
old_auth_rec.visa_expiry_dt,
old_auth_rec.entry_date,
old_auth_rec.fund_id,
old_auth_rec.threshold_perct,
old_auth_rec.threshold_value,
old_auth_rec.accepted_amnt,
old_auth_rec.aw_cal_type,
old_auth_rec.aw_sequence_number,
'R',
old_auth_rec.award_id,
old_auth_rec.authorization_date,
old_auth_rec.notification_date
);
insert_auth;
FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.send_work_auth:igf_se_auth.update');
SELECT awd.award_id,
awd.fund_id fund_id,
fa.person_id person_id,
awd.base_id,
hz.party_number person_number,
fmast.fund_code
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat,
igf_ap_fa_base_rec_all fa,
hz_parties hz
WHERE fcat.fed_fund_code = 'FWS'
AND awd.award_status = 'ACCEPTED'
AND awd.fund_id = NVL(cp_fund_id, awd.fund_id)
AND awd.base_id = NVL(cp_base_id, awd.base_id)
AND fa.ci_cal_type = cp_awd_cal_type
AND fa.ci_sequence_number = cp_awd_seq_no
AND fmast.fund_id = awd.fund_id
AND fmast.fund_code = fcat.fund_code
AND awd.base_id = fa.base_id
AND fa.person_id = hz.party_id;
SELECT 'x'
FROM igf_se_auth
WHERE award_id = cp_award_id
AND flag = 'A';
SELECT awd.accepted_amt accepted_amt,
auth.accepted_amnt accepted_amnt
FROM igf_aw_award_all awd,
igf_se_auth auth
WHERE awd.award_id = auth.award_id
AND awd.award_id = cp_award_id
AND auth.flag = 'A';
SELECT rowid, sei.*
FROM IGF_SE_PAYMENT_INT sei
WHERE sei.batch_id = cv_batch_id
AND sei.auth_id = NVL(cv_auth_id,sei.auth_id)
AND sei.status IN ('NEW', 'UPLOAD')
ORDER BY auth_id, person_id
FOR UPDATE NOWAIT;
SELECT COUNT(*) count
FROM igf_se_auth c,igf_aw_award a, igf_ap_fa_base_rec b
WHERE c.auth_id = cv_auth_id
AND c.award_id = a.award_id
AND a.base_id = b.base_id
AND b.person_id = cv_person_id
AND flag = 'A';
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_STUD_EMP_ERROR'
AND lookup_code = c_error_cd;
SELECT pint.status, pint.error_code, hz.party_number, pint.auth_id
FROM igf_se_payment_int pint, hz_parties hz
WHERE pint.status IN ('DONE','ERROR')
AND pint.person_id = hz.party_id
AND pint.batch_id = p_batch_id;
PROCEDURE update_record(payment_rec IN c_payment%ROWTYPE,
p_error_cd IN igf_se_payment_int.error_code%TYPE) IS
--------------------------------------------------------
--Created by : ssawhney on 2nd Jan
--Purpose : local procedure to update IGF_SE_PAYMENT_INT based on p_error_cd
--Change History :
--Who When What
----------------------------------------------------------------
l_error_cd igf_se_payment_int.error_code%TYPE;
igf_se_payment_int_pkg.update_row(
x_rowid => payment_rec.rowid,
x_transaction_id => payment_rec.transaction_id,
x_batch_id => payment_rec.batch_id,
x_payroll_id => payment_rec.payroll_id,
x_payroll_date => payment_rec.payroll_date,
x_auth_id => payment_rec.auth_id,
x_person_id => payment_rec.person_id,
x_fund_id => payment_rec.fund_id,
x_paid_amount => payment_rec.paid_amount,
x_org_unit_cd => payment_rec.org_unit_cd,
x_status => l_status,
x_error_code => l_error_cd
);
FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.payroll_upload:igf_se_payment_int.update');
END update_record;
igf_se_payment_pkg.insert_row(
x_rowid => l_rowid,
x_transaction_id => l_transaction_id,
x_payroll_id => payment_rec.payroll_id,
x_payroll_date => payment_rec.payroll_date,
x_auth_id => payment_rec.auth_id,
x_person_id => payment_rec.person_id,
x_fund_id => payment_rec.fund_id,
x_paid_amount => payment_rec.paid_amount,
x_org_unit_cd => payment_rec.org_unit_cd,
x_source => l_source
);
update_record(payment_rec,l_error_cd);
update_record(payment_rec,l_error_cd);
DELETE FROM igf_se_payment_int
WHERE status = 'DONE'
AND batch_id = l_batch_id;
FND_MESSAGE.SET_TOKEN('NAME','igf_se_gen_001.payroll_upload: delete record');
SELECT rowid row_id,auth.*
FROM igf_se_auth auth
WHERE auth_id = cv_auth_id
AND flag='A';
SELECT award_year_status_code
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT NVL(accepted_amt,NVL(offered_amt,0)) accepted_amt,
base_id
FROM igf_aw_award_all awd,
igf_se_auth se
WHERE se.award_id = awd.award_id
AND se.auth_id = cv_auth_id
AND se.flag = 'A';
SELECT awd.*
FROM igf_aw_award awd,
igf_se_auth auth
WHERE auth.auth_id = cp_auth_id
AND auth.award_id = awd.award_id;
SELECT SUM( NVL(paid_amount, 0))
FROM igf_se_payment
WHERE auth_id = cv_auth_id;
SELECT threshold_perct, threshold_value
FROM igf_aw_fund_mast
WHERE fund_id = cv_fund_id;
igf_aw_award_pkg.update_row(
x_rowid => l_award.row_id,
x_award_id => l_award.award_id,
x_fund_id => l_award.fund_id,
x_base_id => l_award.base_id,
x_offered_amt => l_award.offered_amt,
x_accepted_amt => l_award.accepted_amt,
x_paid_amt => ln_total_paid_amount,
x_packaging_type => l_award.packaging_type,
x_batch_id => l_award.batch_id,
x_manual_update => l_award.manual_update,
x_rules_override => l_award.rules_override,
x_award_date => l_award.award_date,
x_award_status => l_award.award_status,
x_attribute_category => l_award.attribute_category,
x_attribute1 => l_award.attribute1,
x_attribute2 => l_award.attribute2,
x_attribute3 => l_award.attribute3,
x_attribute4 => l_award.attribute4,
x_attribute5 => l_award.attribute5,
x_attribute6 => l_award.attribute6,
x_attribute7 => l_award.attribute7,
x_attribute8 => l_award.attribute8,
x_attribute9 => l_award.attribute9,
x_attribute10 => l_award.attribute10,
x_attribute11 => l_award.attribute11,
x_attribute12 => l_award.attribute12,
x_attribute13 => l_award.attribute13,
x_attribute14 => l_award.attribute14,
x_attribute15 => l_award.attribute15,
x_attribute16 => l_award.attribute16,
x_attribute17 => l_award.attribute17,
x_attribute18 => l_award.attribute18,
x_attribute19 => l_award.attribute19,
x_attribute20 => l_award.attribute20,
x_rvsn_id => l_award.rvsn_id,
x_alt_pell_schedule => l_award.alt_pell_schedule,
x_mode => 'R',
x_award_number_txt => l_award.award_number_txt,
x_legacy_record_flag => l_award.legacy_record_flag,
x_adplans_id => l_award.adplans_id,
x_lock_award_flag => l_award.lock_award_flag,
x_app_trans_num_txt => l_award.app_trans_num_txt,
x_awd_proc_status_code => l_award.awd_proc_status_code,
x_notification_status_code => l_award.notification_status_code,
x_notification_status_date => l_award.notification_status_date,
x_publish_in_ss_flag => l_award.publish_in_ss_flag
);
igf_se_auth_pkg.update_row(
x_rowid => auth_rec.row_id,
x_sequence_no => auth_rec.sequence_no,
x_auth_id => auth_rec.auth_id,
x_flag => auth_rec.flag,
x_person_id => auth_rec.person_id,
x_first_name => auth_rec.first_name,
x_last_name => auth_rec.last_name,
x_address1 => auth_rec.address1,
x_address2 => auth_rec.address2,
x_address3 => auth_rec.address3,
x_address4 => auth_rec.address4,
x_city => auth_rec.city,
x_state => auth_rec.state,
x_province => auth_rec.province,
x_county => auth_rec.county,
x_country => auth_rec.country,
x_sex => auth_rec.sex,
x_birth_dt => auth_rec.birth_dt,
x_ssn_no => auth_rec.ssn_no,
x_marital_status => auth_rec.marital_status,
x_visa_type => auth_rec.visa_type,
x_visa_category => auth_rec.visa_category,
x_visa_number => auth_rec.visa_number,
x_visa_expiry_dt => auth_rec.visa_expiry_dt,
x_entry_date => auth_rec.entry_date,
x_fund_id => auth_rec.fund_id,
x_threshold_perct => auth_rec.threshold_perct,
x_threshold_value => auth_rec.threshold_value,
x_accepted_amnt => auth_rec.accepted_amnt,
x_aw_cal_type => auth_rec.aw_cal_type,
x_aw_sequence_number => auth_rec.aw_sequence_number,
x_mode => 'R',
x_award_id => auth_rec.award_id,
x_authorization_date => auth_rec.authorization_date,
x_notification_date => l_notification_date
);
SELECT fa.person_number, fa.full_name
FROM igs_pe_person_base_v fa
WHERE fa.person_id = p_person_id ;
SELECT fund_code,threshold_perct,threshold_value
FROM igf_aw_fund_mast
WHERE fund_id = p_fund_id;
SELECT paid_amt
FROM igf_se_work_awd_prg_v
WHERE award_id=p_award_id;
SELECT igs_pe_res_chg_s.nextval INTO l_seq_val from DUAL;