[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(coa.amount)
FROM igf_aw_coa_itm_terms coa,
igf_aw_awd_prd_term trms,
igf_ap_fa_base_rec_all fa,
igf_aw_coa_items item
WHERE fa.base_id = coa.base_id
AND fa.ci_cal_type = trms.ci_cal_type
AND fa.ci_sequence_number = trms.ci_sequence_number
AND coa.ld_cal_type = trms.ld_cal_type
AND coa.ld_sequence_number = trms.ld_sequence_number
AND fa.base_id = cp_base_id
AND trms.award_prd_cd = cp_awd_prd_code
AND item.base_id = coa.base_id
AND item.item_code = coa.item_code
AND (
(cp_use_direct_costs = 'Y' AND NVL(item.fixed_cost, 'N') = 'Y')
OR (cp_use_direct_costs = 'N' AND NVL(item.fixed_cost, 'N') IN ('Y','N')
));
SELECT SUM(coa.amount)
FROM igf_aw_coa_itm_terms coa,
igf_aw_coa_items item
WHERE coa.base_id = cp_base_id
AND item.base_id = coa.base_id
AND item.item_code = coa.item_code
AND (
(cp_use_direct_costs = 'Y' AND NVL(item.fixed_cost, 'N') = 'Y')
OR (cp_use_direct_costs = 'N' AND NVL(item.fixed_cost, 'N') IN('Y','N')
));
SELECT SUM(NVL(disb.disb_gross_amt, 0)) award_amount
FROM igf_aw_award_all awd,
igf_aw_awd_disb_all disb,
igf_aw_awd_prd_term aprd,
igf_ap_fa_base_rec_all fa
WHERE disb.award_id = awd.award_id
AND awd.base_id = cp_base_id
AND awd.base_id = fa.base_id
AND awd.award_status IN('OFFERED', 'ACCEPTED')
AND disb.trans_type <> 'C'
AND disb.ld_cal_type = aprd.ld_cal_type
AND disb.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ci_cal_type = fa.ci_cal_type
AND aprd.ci_sequence_number = fa.ci_sequence_number
AND awd.award_id = NVL(cp_award_id,awd.award_id);
SELECT SUM(NVL(disb.disb_gross_amt, 0)) award_amount
FROM igf_aw_award_all awd,
igf_aw_awd_disb_all disb
WHERE disb.award_id = awd.award_id
AND awd.base_id = cp_base_id
AND awd.award_status IN ('OFFERED', 'ACCEPTED')
AND disb.trans_type <> 'C'
AND awd.award_id = NVL(cp_award_id,awd.award_id);
SELECT NVL(lock_flag,'N') lock_flag
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
SELECT NVL(lock_flag,'N') lock_flag
FROM igf_aw_coa_items
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
SELECT NVL(lock_coa_flag,'N') lock_coa_flag
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
PROCEDURE updateLock(
p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_mode VARCHAR2,
p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
);
updateLock(p_base_id,'Y',p_item_code,p_ld_cal_type,p_ld_sequence_number);
updateLock(p_base_id,'N',p_item_code,p_ld_cal_type,p_ld_sequence_number);
PROCEDURE updateLock(
p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_mode VARCHAR2,
p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 07-OCT-2004
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR c_item_term(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_item_code igf_aw_item.item_code%TYPE,
cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
) IS
SELECT terms.rowid row_id,
terms.*
FROM igf_aw_coa_itm_terms terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
SELECT items.rowid row_id,
items.*
FROM igf_aw_coa_items items
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
SELECT fa.rowid row_id,
fa.*
FROM igf_ap_fa_base_rec_all fa
WHERE base_id = cp_base_id;
SELECT terms.ld_cal_type,
terms.ld_sequence_number
FROM igf_aw_coa_itm_terms terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
SELECT items.item_code
FROM igf_aw_coa_items items
WHERE base_id = cp_base_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_base_id:'||p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_mode:'||p_mode);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_item_code:'||p_item_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_ld_cal_type:'||p_ld_cal_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_ld_sequence_number:'||p_ld_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 1');
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => l_item_term.base_id,
x_item_code => l_item_term.item_code,
x_amount => l_item_term.amount,
x_ld_cal_type => l_item_term.ld_cal_type,
x_ld_sequence_number => l_item_term.ld_sequence_number,
x_mode => 'R',
x_lock_flag => 'Y'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 2');
igf_aw_coa_items_pkg.update_row(
x_rowid => l_item.row_id,
x_base_id => l_item.base_id,
x_item_code => l_item.item_code,
x_amount => l_item.amount,
x_pell_coa_amount => l_item.pell_coa_amount,
x_alt_pell_amount => l_item.alt_pell_amount,
x_fixed_cost => l_item.fixed_cost,
x_legacy_record_flag => l_item.legacy_record_flag,
x_mode => 'R',
x_lock_flag => 'Y'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 3');
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => l_base.row_id,
x_base_id => l_base.base_id,
x_ci_cal_type => l_base.ci_cal_type,
x_person_id => l_base.person_id,
x_ci_sequence_number => l_base.ci_sequence_number,
x_org_id => l_base.org_id,
x_coa_pending => l_base.coa_pending,
x_verification_process_run => l_base.verification_process_run,
x_inst_verif_status_date => l_base.inst_verif_status_date,
x_manual_verif_flag => l_base.manual_verif_flag,
x_fed_verif_status => l_base.fed_verif_status,
x_fed_verif_status_date => l_base.fed_verif_status_date,
x_inst_verif_status => l_base.inst_verif_status,
x_nslds_eligible => l_base.nslds_eligible,
x_ede_correction_batch_id => l_base.ede_correction_batch_id,
x_fa_process_status_date => l_base.fa_process_status_date,
x_isir_corr_status => l_base.isir_corr_status,
x_isir_corr_status_date => l_base.isir_corr_status_date,
x_isir_status => l_base.isir_status,
x_isir_status_date => l_base.isir_status_date,
x_coa_code_f => l_base.coa_code_f,
x_coa_code_i => l_base.coa_code_i,
x_coa_f => l_base.coa_f,
x_coa_i => l_base.coa_i,
x_disbursement_hold => l_base.disbursement_hold,
x_fa_process_status => l_base.fa_process_status,
x_notification_status => l_base.notification_status,
x_notification_status_date => l_base.notification_status_date,
x_packaging_hold => l_base.packaging_hold,
x_packaging_status => l_base.packaging_status,
x_packaging_status_date => l_base.packaging_status_date,
x_total_package_accepted => l_base.total_package_accepted,
x_total_package_offered => l_base.total_package_offered,
x_admstruct_id => l_base.admstruct_id,
x_admsegment_1 => l_base.admsegment_1,
x_admsegment_2 => l_base.admsegment_2,
x_admsegment_3 => l_base.admsegment_3,
x_admsegment_4 => l_base.admsegment_4,
x_admsegment_5 => l_base.admsegment_5,
x_admsegment_6 => l_base.admsegment_6,
x_admsegment_7 => l_base.admsegment_7,
x_admsegment_8 => l_base.admsegment_8,
x_admsegment_9 => l_base.admsegment_9,
x_admsegment_10 => l_base.admsegment_10,
x_admsegment_11 => l_base.admsegment_11,
x_admsegment_12 => l_base.admsegment_12,
x_admsegment_13 => l_base.admsegment_13,
x_admsegment_14 => l_base.admsegment_14,
x_admsegment_15 => l_base.admsegment_15,
x_admsegment_16 => l_base.admsegment_16,
x_admsegment_17 => l_base.admsegment_17,
x_admsegment_18 => l_base.admsegment_18,
x_admsegment_19 => l_base.admsegment_19,
x_admsegment_20 => l_base.admsegment_20,
x_packstruct_id => l_base.packstruct_id,
x_packsegment_1 => l_base.packsegment_1,
x_packsegment_2 => l_base.packsegment_2,
x_packsegment_3 => l_base.packsegment_3,
x_packsegment_4 => l_base.packsegment_4,
x_packsegment_5 => l_base.packsegment_5,
x_packsegment_6 => l_base.packsegment_6,
x_packsegment_7 => l_base.packsegment_7,
x_packsegment_8 => l_base.packsegment_8,
x_packsegment_9 => l_base.packsegment_9,
x_packsegment_10 => l_base.packsegment_10,
x_packsegment_11 => l_base.packsegment_11,
x_packsegment_12 => l_base.packsegment_12,
x_packsegment_13 => l_base.packsegment_13,
x_packsegment_14 => l_base.packsegment_14,
x_packsegment_15 => l_base.packsegment_15,
x_packsegment_16 => l_base.packsegment_16,
x_packsegment_17 => l_base.packsegment_17,
x_packsegment_18 => l_base.packsegment_18,
x_packsegment_19 => l_base.packsegment_19,
x_packsegment_20 => l_base.packsegment_20,
x_miscstruct_id => l_base.miscstruct_id,
x_miscsegment_1 => l_base.miscsegment_1,
x_miscsegment_2 => l_base.miscsegment_2,
x_miscsegment_3 => l_base.miscsegment_3,
x_miscsegment_4 => l_base.miscsegment_4,
x_miscsegment_5 => l_base.miscsegment_5,
x_miscsegment_6 => l_base.miscsegment_6,
x_miscsegment_7 => l_base.miscsegment_7,
x_miscsegment_8 => l_base.miscsegment_8,
x_miscsegment_9 => l_base.miscsegment_9,
x_miscsegment_10 => l_base.miscsegment_10,
x_miscsegment_11 => l_base.miscsegment_11,
x_miscsegment_12 => l_base.miscsegment_12,
x_miscsegment_13 => l_base.miscsegment_13,
x_miscsegment_14 => l_base.miscsegment_14,
x_miscsegment_15 => l_base.miscsegment_15,
x_miscsegment_16 => l_base.miscsegment_16,
x_miscsegment_17 => l_base.miscsegment_17,
x_miscsegment_18 => l_base.miscsegment_18,
x_miscsegment_19 => l_base.miscsegment_19,
x_miscsegment_20 => l_base.miscsegment_20,
x_prof_judgement_flg => l_base.prof_judgement_flg,
x_nslds_data_override_flg => l_base.nslds_data_override_flg,
x_target_group => l_base.target_group,
x_coa_fixed => l_base.coa_fixed,
x_coa_pell => l_base.coa_pell,
x_mode => 'R',
x_profile_status => l_base.profile_status,
x_profile_status_date => l_base.profile_status_date,
x_profile_fc => l_base.profile_fc,
x_tolerance_amount => l_base.tolerance_amount,
x_manual_disb_hold => l_base.manual_disb_hold,
x_pell_alt_expense => l_base.pell_alt_expense,
x_assoc_org_num => l_base.assoc_org_num,
x_award_fmly_contribution_type => l_base.award_fmly_contribution_type,
x_isir_locked_by => l_base.isir_locked_by,
x_adnl_unsub_loan_elig_flag => l_base.adnl_unsub_loan_elig_flag,
x_lock_coa_flag => 'Y',
x_lock_awd_flag => l_base.lock_awd_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 4');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 5');
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_item_term.row_id,
x_base_id => l_item_term.base_id,
x_item_code => l_item_term.item_code,
x_amount => l_item_term.amount,
x_ld_cal_type => l_item_term.ld_cal_type,
x_ld_sequence_number => l_item_term.ld_sequence_number,
x_mode => 'R',
x_lock_flag => 'N'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 6');
igf_aw_coa_items_pkg.update_row(
x_rowid => l_item.row_id,
x_base_id => l_item.base_id,
x_item_code => l_item.item_code,
x_amount => l_item.amount,
x_pell_coa_amount => l_item.pell_coa_amount,
x_alt_pell_amount => l_item.alt_pell_amount,
x_fixed_cost => l_item.fixed_cost,
x_legacy_record_flag => l_item.legacy_record_flag,
x_mode => 'R',
x_lock_flag => 'N'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 7');
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => l_base.row_id,
x_base_id => l_base.base_id,
x_ci_cal_type => l_base.ci_cal_type,
x_person_id => l_base.person_id,
x_ci_sequence_number => l_base.ci_sequence_number,
x_org_id => l_base.org_id,
x_coa_pending => l_base.coa_pending,
x_verification_process_run => l_base.verification_process_run,
x_inst_verif_status_date => l_base.inst_verif_status_date,
x_manual_verif_flag => l_base.manual_verif_flag,
x_fed_verif_status => l_base.fed_verif_status,
x_fed_verif_status_date => l_base.fed_verif_status_date,
x_inst_verif_status => l_base.inst_verif_status,
x_nslds_eligible => l_base.nslds_eligible,
x_ede_correction_batch_id => l_base.ede_correction_batch_id,
x_fa_process_status_date => l_base.fa_process_status_date,
x_isir_corr_status => l_base.isir_corr_status,
x_isir_corr_status_date => l_base.isir_corr_status_date,
x_isir_status => l_base.isir_status,
x_isir_status_date => l_base.isir_status_date,
x_coa_code_f => l_base.coa_code_f,
x_coa_code_i => l_base.coa_code_i,
x_coa_f => l_base.coa_f,
x_coa_i => l_base.coa_i,
x_disbursement_hold => l_base.disbursement_hold,
x_fa_process_status => l_base.fa_process_status,
x_notification_status => l_base.notification_status,
x_notification_status_date => l_base.notification_status_date,
x_packaging_hold => l_base.packaging_hold,
x_packaging_status => l_base.packaging_status,
x_packaging_status_date => l_base.packaging_status_date,
x_total_package_accepted => l_base.total_package_accepted,
x_total_package_offered => l_base.total_package_offered,
x_admstruct_id => l_base.admstruct_id,
x_admsegment_1 => l_base.admsegment_1,
x_admsegment_2 => l_base.admsegment_2,
x_admsegment_3 => l_base.admsegment_3,
x_admsegment_4 => l_base.admsegment_4,
x_admsegment_5 => l_base.admsegment_5,
x_admsegment_6 => l_base.admsegment_6,
x_admsegment_7 => l_base.admsegment_7,
x_admsegment_8 => l_base.admsegment_8,
x_admsegment_9 => l_base.admsegment_9,
x_admsegment_10 => l_base.admsegment_10,
x_admsegment_11 => l_base.admsegment_11,
x_admsegment_12 => l_base.admsegment_12,
x_admsegment_13 => l_base.admsegment_13,
x_admsegment_14 => l_base.admsegment_14,
x_admsegment_15 => l_base.admsegment_15,
x_admsegment_16 => l_base.admsegment_16,
x_admsegment_17 => l_base.admsegment_17,
x_admsegment_18 => l_base.admsegment_18,
x_admsegment_19 => l_base.admsegment_19,
x_admsegment_20 => l_base.admsegment_20,
x_packstruct_id => l_base.packstruct_id,
x_packsegment_1 => l_base.packsegment_1,
x_packsegment_2 => l_base.packsegment_2,
x_packsegment_3 => l_base.packsegment_3,
x_packsegment_4 => l_base.packsegment_4,
x_packsegment_5 => l_base.packsegment_5,
x_packsegment_6 => l_base.packsegment_6,
x_packsegment_7 => l_base.packsegment_7,
x_packsegment_8 => l_base.packsegment_8,
x_packsegment_9 => l_base.packsegment_9,
x_packsegment_10 => l_base.packsegment_10,
x_packsegment_11 => l_base.packsegment_11,
x_packsegment_12 => l_base.packsegment_12,
x_packsegment_13 => l_base.packsegment_13,
x_packsegment_14 => l_base.packsegment_14,
x_packsegment_15 => l_base.packsegment_15,
x_packsegment_16 => l_base.packsegment_16,
x_packsegment_17 => l_base.packsegment_17,
x_packsegment_18 => l_base.packsegment_18,
x_packsegment_19 => l_base.packsegment_19,
x_packsegment_20 => l_base.packsegment_20,
x_miscstruct_id => l_base.miscstruct_id,
x_miscsegment_1 => l_base.miscsegment_1,
x_miscsegment_2 => l_base.miscsegment_2,
x_miscsegment_3 => l_base.miscsegment_3,
x_miscsegment_4 => l_base.miscsegment_4,
x_miscsegment_5 => l_base.miscsegment_5,
x_miscsegment_6 => l_base.miscsegment_6,
x_miscsegment_7 => l_base.miscsegment_7,
x_miscsegment_8 => l_base.miscsegment_8,
x_miscsegment_9 => l_base.miscsegment_9,
x_miscsegment_10 => l_base.miscsegment_10,
x_miscsegment_11 => l_base.miscsegment_11,
x_miscsegment_12 => l_base.miscsegment_12,
x_miscsegment_13 => l_base.miscsegment_13,
x_miscsegment_14 => l_base.miscsegment_14,
x_miscsegment_15 => l_base.miscsegment_15,
x_miscsegment_16 => l_base.miscsegment_16,
x_miscsegment_17 => l_base.miscsegment_17,
x_miscsegment_18 => l_base.miscsegment_18,
x_miscsegment_19 => l_base.miscsegment_19,
x_miscsegment_20 => l_base.miscsegment_20,
x_prof_judgement_flg => l_base.prof_judgement_flg,
x_nslds_data_override_flg => l_base.nslds_data_override_flg,
x_target_group => l_base.target_group,
x_coa_fixed => l_base.coa_fixed,
x_coa_pell => l_base.coa_pell,
x_mode => 'R',
x_profile_status => l_base.profile_status,
x_profile_status_date => l_base.profile_status_date,
x_profile_fc => l_base.profile_fc,
x_tolerance_amount => l_base.tolerance_amount,
x_manual_disb_hold => l_base.manual_disb_hold,
x_pell_alt_expense => l_base.pell_alt_expense,
x_assoc_org_num => l_base.assoc_org_num,
x_award_fmly_contribution_type => l_base.award_fmly_contribution_type,
x_isir_locked_by => l_base.isir_locked_by,
x_adnl_unsub_loan_elig_flag => l_base.adnl_unsub_loan_elig_flag,
x_lock_coa_flag => 'N',
x_lock_awd_flag => l_base.lock_awd_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 8');
END updateLock;
SELECT coa.ld_cal_type,
coa.ld_sequence_number
FROM igf_aw_awd_prd_term ap,
igf_ap_fa_base_rec_all fa,
igf_aw_coa_itm_terms coa
WHERE fa.base_id = cp_base_id
AND fa.ci_cal_type = ap.ci_cal_type
AND fa.ci_sequence_number = ap.ci_sequence_number
AND coa.base_id = cp_base_id
AND coa.ld_cal_type = ap.ld_cal_type
AND coa.ld_sequence_number = ap.ld_sequence_number
AND ap.award_prd_cd = cp_awd_prd_code
GROUP BY coa.ld_cal_type,coa.ld_sequence_number;
SELECT coa.ld_cal_type,
coa.ld_sequence_number
FROM igf_aw_coa_itm_terms coa
WHERE coa.base_id = cp_base_id
GROUP BY coa.ld_cal_type,coa.ld_sequence_number;
SELECT num_days_divisor, roundoff_fact
FROM igf_ap_efc_v efc,
igf_ap_fa_base_rec_all fabase
WHERE efc.ci_cal_type = fabase.ci_cal_type
AND efc.ci_sequence_number = fabase.ci_sequence_number
AND fabase.base_id = cp_base_id;
SELECT person_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
SELECT org_unit_cd,
program_type,
program_location_cd,
program_cd,
class_standing,
residency_status_code,
housing_status_code,
attendance_type,
attendance_mode,
months_enrolled_num,
credit_points_num
FROM igf_ap_fa_ant_data
WHERE base_id = cp_base_id
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
SELECT ps.responsible_org_unit_cd,1 record_order
FROM igs_en_spa_terms key,
igs_ps_ver ps
WHERE key.key_program_flag = 'Y'
AND person_id = cp_person_id
AND ps.course_cd = key.program_cd
AND ps.version_number = key.program_version
AND key.term_cal_type = cp_ld_cal_type
AND key.term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT ps.responsible_org_unit_cd,
2 record_order
FROM igs_en_stdnt_ps_att att,
igs_ps_ver ps
WHERE att.person_id = cp_person_id
AND att.key_program = 'Y'
AND att.course_cd = ps.course_cd
AND att.version_number = ps.version_number
ORDER BY record_order;
SELECT pt.course_type,1 record_order
FROM igs_en_spa_terms sp,
igs_ps_ver pv,
igs_ps_type_v pt
WHERE sp.key_program_flag = 'Y'
AND sp.program_cd = pv.course_cd
AND sp.program_version = pv.version_number
AND pv.course_type = pt.course_type
AND sp.person_id = cp_person_id
AND sp.term_cal_type = cp_ld_cal_type
AND sp.term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT pt.course_type,2 record_order
FROM igs_en_stdnt_ps_att sp,
igs_ps_ver pv,
igs_ps_type_v pt
WHERE sp.key_program = 'Y'
AND sp.course_cd = pv.course_cd
AND sp.version_number = pv.version_number
AND pv.course_type = pt.course_type
AND sp.person_id = cp_person_id
ORDER BY record_order;
SELECT location_cd,
1 record_order
FROM igs_en_spa_terms
WHERE key_program_flag = 'Y'
AND person_id = cp_person_id
AND term_cal_type = cp_ld_cal_type
AND term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT location_cd,
2 record_order
FROM igs_en_stdnt_ps_att
WHERE key_program = 'Y'
AND person_id = cp_person_id
ORDER BY record_order;
SELECT program_cd,
program_version,
1 record_order
FROM igs_en_spa_terms
WHERE key_program_flag = 'Y'
AND person_id = cp_person_id
AND term_cal_type = cp_ld_cal_type
AND term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT course_cd program_cd,
version_number program_version,
2 record_order
FROM igs_en_stdnt_ps_att
WHERE key_program = 'Y'
AND person_id = cp_person_id
ORDER BY record_order;
SELECT attendance_type,
1 record_order
FROM igs_en_spa_terms
WHERE key_program_flag = 'Y'
AND person_id = cp_person_id
AND term_cal_type = cp_ld_cal_type
AND term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT attendance_type,
2 record_order
FROM igs_en_stdnt_ps_att
WHERE key_program = 'Y'
AND person_id = cp_person_id
ORDER BY record_order;
SELECT attendance_mode,
1 record_order
FROM igs_en_spa_terms
WHERE key_program_flag = 'Y'
AND person_id = cp_person_id
AND term_cal_type = cp_ld_cal_type
AND term_sequence_number = cp_ld_sequence_number
UNION ALL
SELECT attendance_mode,
2 record_order
FROM igs_en_stdnt_ps_att
WHERE key_program = 'Y'
AND person_id = cp_person_id
ORDER BY record_order;
SELECT teach_period_resid_stat_cd
FROM igs_pe_teach_periods_all
WHERE person_id = cp_person_id
AND cal_type = cp_ld_cal_type
AND sequence_number = cp_ld_sequence_number;
SELECT num_days_divisor, roundoff_fact
FROM igf_ap_efc_v efc,
igf_ap_fa_base_rec_all fabase
WHERE efc.ci_cal_type = fabase.ci_cal_type
AND efc.ci_sequence_number = fabase.ci_sequence_number
AND fabase.base_id = cp_base_id;
SELECT batch.ci_cal_type,
batch.ci_sequence_number
FROM igf_ap_batch_aw_map_all batch,
igf_ap_fa_base_rec_all fa,
igf_aw_coa_items coa
WHERE batch.award_year_status_code = 'O'
AND fa.ci_cal_type = batch.ci_cal_type
AND fa.ci_sequence_number = batch.ci_sequence_number
AND fa.base_id = coa.base_id
AND fa.person_id = cp_person_id
GROUP BY batch.ci_cal_type,
batch.ci_sequence_number;
SELECT person_id
FROM igs_en_spa_terms
WHERE key_program_flag = 'Y'
AND program_cd = cp_program_code
AND program_version = cp_version_number;
SELECT awd.rowid row_id,
awd.*
FROM igf_aw_award_all awd
WHERE awd.base_id = c_base_id
AND (awd.awd_proc_status_code = 'AWARDED' OR awd.awd_proc_status_code IS NULL)
AND NOT EXISTS
(SELECT disb.ld_cal_type,
disb.ld_sequence_number
FROM igf_aw_awd_disb_all disb
WHERE disb.award_id = awd.award_id
MINUS
SELECT apt.ld_cal_type,
apt.ld_sequence_number
FROM igf_ap_fa_base_rec_all fab,
igf_aw_awd_prd_term apt
WHERE fab.base_id = c_base_id AND
apt.ci_cal_type = fab.ci_cal_type AND
apt.ci_sequence_number = fab.ci_sequence_number AND
apt.award_prd_cd = c_award_prd_code
);
SELECT awd.rowid row_id,
awd.*
FROM igf_aw_award_all awd
WHERE awd.base_id = c_base_id
AND (awd.awd_proc_status_code = 'AWARDED' OR awd.awd_proc_status_code IS NULL);
igf_aw_award_pkg.update_row(
x_mode => 'R',
x_rowid => l_awards.row_id ,
x_award_id => l_awards.award_id ,
x_fund_id => l_awards.fund_id ,
x_base_id => l_awards.base_id ,
x_offered_amt => l_awards.offered_amt ,
x_accepted_amt => l_awards.accepted_amt ,
x_paid_amt => l_awards.paid_amt ,
x_packaging_type => l_awards.packaging_type ,
x_batch_id => l_awards.batch_id ,
x_manual_update => l_awards.manual_update ,
x_rules_override => l_awards.rules_override ,
x_award_date => l_awards.award_date ,
x_award_status => l_awards.award_status ,
x_attribute_category => l_awards.attribute_category ,
x_attribute1 => l_awards.attribute1 ,
x_attribute2 => l_awards.attribute2 ,
x_attribute3 => l_awards.attribute3 ,
x_attribute4 => l_awards.attribute4 ,
x_attribute5 => l_awards.attribute5 ,
x_attribute6 => l_awards.attribute6 ,
x_attribute7 => l_awards.attribute7 ,
x_attribute8 => l_awards.attribute8 ,
x_attribute9 => l_awards.attribute9 ,
x_attribute10 => l_awards.attribute10 ,
x_attribute11 => l_awards.attribute11 ,
x_attribute12 => l_awards.attribute12 ,
x_attribute13 => l_awards.attribute13 ,
x_attribute14 => l_awards.attribute14 ,
x_attribute15 => l_awards.attribute15 ,
x_attribute16 => l_awards.attribute16 ,
x_attribute17 => l_awards.attribute17 ,
x_attribute18 => l_awards.attribute18 ,
x_attribute19 => l_awards.attribute19 ,
x_attribute20 => l_awards.attribute20 ,
x_rvsn_id => l_awards.rvsn_id ,
x_alt_pell_schedule => l_awards.alt_pell_schedule ,
x_award_number_txt => l_awards.award_number_txt ,
x_legacy_record_flag => l_awards.legacy_record_flag ,
x_adplans_id => l_awards.adplans_id ,
x_lock_award_flag => l_awards.lock_award_flag ,
x_app_trans_num_txt => l_awards.app_trans_num_txt ,
x_awd_proc_status_code => lv_status ,
x_notification_status_code => l_awards.notification_status_code,
x_notification_status_date => l_awards.notification_status_date,
x_publish_in_ss_flag => l_awards.publish_in_ss_flag
);
igf_aw_award_pkg.update_row(
x_mode => 'R',
x_rowid => l_award_prd.row_id ,
x_award_id => l_award_prd.award_id ,
x_fund_id => l_award_prd.fund_id ,
x_base_id => l_award_prd.base_id ,
x_offered_amt => l_award_prd.offered_amt ,
x_accepted_amt => l_award_prd.accepted_amt ,
x_paid_amt => l_award_prd.paid_amt ,
x_packaging_type => l_award_prd.packaging_type ,
x_batch_id => l_award_prd.batch_id ,
x_manual_update => l_award_prd.manual_update ,
x_rules_override => l_award_prd.rules_override ,
x_award_date => l_award_prd.award_date ,
x_award_status => l_award_prd.award_status ,
x_attribute_category => l_award_prd.attribute_category ,
x_attribute1 => l_award_prd.attribute1 ,
x_attribute2 => l_award_prd.attribute2 ,
x_attribute3 => l_award_prd.attribute3 ,
x_attribute4 => l_award_prd.attribute4 ,
x_attribute5 => l_award_prd.attribute5 ,
x_attribute6 => l_award_prd.attribute6 ,
x_attribute7 => l_award_prd.attribute7 ,
x_attribute8 => l_award_prd.attribute8 ,
x_attribute9 => l_award_prd.attribute9 ,
x_attribute10 => l_award_prd.attribute10 ,
x_attribute11 => l_award_prd.attribute11 ,
x_attribute12 => l_award_prd.attribute12 ,
x_attribute13 => l_award_prd.attribute13 ,
x_attribute14 => l_award_prd.attribute14 ,
x_attribute15 => l_award_prd.attribute15 ,
x_attribute16 => l_award_prd.attribute16 ,
x_attribute17 => l_award_prd.attribute17 ,
x_attribute18 => l_award_prd.attribute18 ,
x_attribute19 => l_award_prd.attribute19 ,
x_attribute20 => l_award_prd.attribute20 ,
x_rvsn_id => l_award_prd.rvsn_id ,
x_alt_pell_schedule => l_award_prd.alt_pell_schedule ,
x_award_number_txt => l_award_prd.award_number_txt ,
x_legacy_record_flag => l_award_prd.legacy_record_flag ,
x_adplans_id => l_award_prd.adplans_id ,
x_lock_award_flag => l_award_prd.lock_award_flag ,
x_app_trans_num_txt => l_award_prd.app_trans_num_txt ,
x_awd_proc_status_code => lv_status ,
x_notification_status_code => l_award_prd.notification_status_code,
x_notification_status_date => l_award_prd.notification_status_date,
x_publish_in_ss_flag => l_award_prd.publish_in_ss_flag
);
SELECT MIN(cal.start_dt) start_date,
MAX(cal.end_dt) end_date
FROM igf_aw_awd_prd_term aprd,
igs_ca_inst cal
WHERE aprd.ci_cal_type = cp_ci_cal_type
AND aprd.ci_sequence_number = cp_ci_sequence_number
AND aprd.award_prd_cd = cp_award_prd_code
AND aprd.ld_cal_type = cal.cal_type
AND aprd.ld_sequence_number = cal.sequence_number;
SELECT 'x'
FROM igs_ps_ver
WHERE course_cd = cp_program_code
AND ( responsible_org_unit_cd = cp_org_unit
OR EXISTS (SELECT 'x'
FROM igs_ps_own
WHERE course_cd = cp_program_code
AND org_unit_cd = cp_org_unit
)
);
SELECT 'x'
FROM igs_ps_ver
WHERE course_cd = cp_program_code
AND course_type = cp_program_type;
SELECT 'x'
FROM igs_ps_ofr_opt_all offering
WHERE offering.course_cd = cp_program_code
AND offering.location_cd = cp_location_code;
SELECT 'x'
FROM igs_ps_ofr_opt_all offering,
igs_ps_ofr_pat offering_pattern
WHERE offering.course_cd = cp_program_code
AND offering.location_cd = cp_location_code
AND offering.attendance_mode = cp_attend_mode
AND offering.attendance_type = cp_attend_type
AND offering.delete_flag = 'N'
AND offering.coo_id = offering_pattern.coo_id
AND offering_pattern.offered_ind = 'Y';