[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Added procedures update_plan, |
| update_dist_plan,delete_plan, |
| check_plan_code |
| brajendr 08-Jan-2003 Bug # 2710314 |
| Added a Function validate_student_efc |
| for checking the validity of EFC |
| |
| brajendr 31-Dec-2002 Bug # 2721995 |
| Added an extra condition in first if |
| condition. ( fund_code IS NULL ) |
| |
*======================================================================*/
PROCEDURE update_plan(
p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
p_method_code IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 1-NOV-2003
--
--Purpose:Update a distribution plan's distribution percentages
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
-- Get all terms associated with the plan ID
CURSOR c_terms(
cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
) IS
SELECT rowid row_id,terms.*
FROM igf_aw_dp_terms terms
WHERE adplans_id = cp_adplans_id
AND ld_perct_num IS NOT NULL;
igf_aw_dp_terms_pkg.update_row(
x_rowid => terms_rec.row_id,
x_adterms_id => terms_rec.adterms_id,
x_adplans_id => terms_rec.adplans_id,
x_ld_cal_type => terms_rec.ld_cal_type,
x_ld_sequence_number => terms_rec.ld_sequence_number,
x_ld_perct_num => NULL,
x_mode => 'R'
);
fnd_message.set_token('NAME','IGF_AW_GEN_005.UPDATE_PLAN');
END update_plan;
PROCEDURE update_dist_plan(
p_award_id igf_aw_award.award_id%TYPE
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 1-NOV-2003
--
--Purpose:To update an award's distribution plan with NULL when disbursements are changed manually
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
--bvisvana 11-Jul-2005 TBH impact for notification status code,notification status date and publish in ss flag
-------------------------------------------------------------------
-- Get award details
CURSOR c_award(
cp_award_id igf_aw_award.award_id%TYPE
) IS
SELECT *
FROM igf_aw_award
WHERE award_id = cp_award_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 => l_award.paid_amt,
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 => NULL,
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
);
fnd_message.set_token('NAME','IGF_AW_GEN_005.UPDATE_DIST_PLAN');
END update_dist_plan;
SELECT adplans_id
FROM igf_aw_target_grp
WHERE adplans_id = cp_adplans_id;
SELECT adplans_id
FROM igf_aw_awd_frml_det
WHERE adplans_id = cp_adplans_id;
SELECT adplans_id
FROM igf_aw_award
WHERE adplans_id = cp_adplans_id;
PROCEDURE delete_plan(
p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
p_adterms_id igf_aw_dp_terms.adterms_id%TYPE
) AS
------------------------------------------------------------------
--Created by : veramach, Oracle India
--Date created: 1-NOV-2003
--
--Purpose: To delete terms and teaching periods attahced to a distribution plan
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
-- Get all terms attached to a distribution plan
CURSOR c_terms(
cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
cp_adterms_id igf_aw_dp_terms.adterms_id%TYPE
) IS
SELECT rowid row_id,terms.adterms_id adterms_id
FROM igf_aw_dp_terms terms
WHERE adplans_id = cp_adplans_id
AND adterms_id = NVL(cp_adterms_id,adterms_id);
SELECT rowid row_id
FROM igf_aw_dp_teach_prds
WHERE adterms_id = cp_adterms_id;
igf_aw_dp_teach_prds_pkg.delete_row(x_rowid => l_teaching_periods_rec.row_id);
igf_aw_dp_terms_pkg.delete_row(x_rowid => l_terms_rec.row_id);
fnd_message.set_token('NAME','IGF_AW_GEN_005.DELETE_PLAN');
END delete_plan;
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT 1
FROM igs_pe_fund_excl
WHERE fund_code = cp_fund_code
AND person_id = cp_person_id
AND encumbrance_type = cp_encb_type
AND s_encmb_effect_type = cp_effect_type
AND NVL(cp_date,TRUNC(sysdate)) BETWEEN TRUNC(pfe_start_dt) AND NVL(TRUNC(expiry_dt),TRUNC(sysdate))
);
SELECT eff.encumbrance_type encb_type, eff.s_encmb_effect_type effect_type
FROM igs_fi_encmb_type typ,
igs_pe_pers_encumb enc,
igs_pe_persenc_effct eff
WHERE typ.s_encumbrance_cat = 'ACADEMIC'
AND typ.encumbrance_type = enc.encumbrance_type
AND NVL(cp_date, TRUNC(sysdate)) BETWEEN TRUNC(eff.pee_start_dt) AND NVL(TRUNC(eff.expiry_dt), TRUNC(sysdate))
AND enc.person_id = cp_person_id
AND enc.encumbrance_type = eff.encumbrance_type
AND eff.person_id = enc.person_id
AND eff.s_encmb_effect_type IN ('EX_AWD', 'EX_SP_AWD', 'EX_DISB', 'EX_SP_DISB');
|| rasahoo 27-Nov-2003 FA 128 Isir Update
|| Changed the Cursor c_chk_valid_efc as part of paid efc impact
*/
l_efc NUMBER;