[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG in function IGS_PR_UPD_SCA_STATUS
|| ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference
|| in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
||
|| NALKUMAR 19-NOV-2002 Bug NO: 2658550 ||
|| Modified this object as per the FA110 PR Enh. ||
||==============================================================================||
|| pkpatel 07-OCT-2002 Bug No: 2600842 ||
|| Added the parameter auth_resp_id in the call to THB igs_pe_pers_encumb_pkg
| nmankodi 11-Apr-2005 fnd_user.customer_id column has been changed to
| fnd_user.person_party_id as an ebizsuite wide TCA mandate.
||==============================================================================||
*/
FUNCTION IGS_PR_GET_SCSC_COMP(
p_person_id IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_version_number IN NUMBER ,
p_cst_sequence_number IN NUMBER )
RETURN VARCHAR2 IS
gv_other_detail VARCHAR2(255);
SELECT 'X'
FROM student_crs_stage_cmpltn scsc
WHERE scsc.person_id = p_person_id AND
scsc.course_cd = p_course_cd AND
scsc.version_number = p_version_number AND
scsc.cst_sequence_number = p_cst_sequence_number; */
SELECT crv.course_type,
cow.org_unit_cd,
cow.ou_start_dt
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv,
IGS_PS_OWN cow
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
(sca.version_number = p_version_number OR
p_version_number IS NULL) AND
(sca.location_cd = p_location_cd OR
p_location_cd IS NULL) AND
(sca.attendance_mode = p_attendance_mode OR
p_attendance_mode IS NULL) AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number AND
crv.course_cd = cow.course_cd AND
crv.version_number = cow.version_number AND
(crv.course_type = p_course_type OR
p_course_type IS NULL);
SELECT 'X'
FROM IGS_OR_UNIT_REL our
WHERE our.parent_org_unit_cd = p_org_unit_cd AND
our.parent_start_dt = p_ou_start_dt AND
our.child_org_unit_cd = cp_cow_org_unit_cd AND
our.child_start_dt = cp_cow_ou_start_dt AND
our.logical_delete_dt IS NULL AND
EXISTS (
SELECT 'X'
FROM IGS_OR_REL_PS_TYPE ourct
WHERE our.parent_org_unit_cd = ourct.parent_org_unit_cd AND
our.parent_start_dt = ourct.parent_start_dt AND
our.child_org_unit_cd = ourct.child_org_unit_cd AND
our.child_start_dt = ourct.child_start_dt AND
our.create_dt = ourct.our_create_dt AND
ourct.course_type = cp_course_type);
SELECT spo.prg_cal_type,
spo.prg_ci_sequence_number,
spo.duration,
spo.duration_type,
ci.start_dt,
sca.version_number
FROM IGS_PR_STDNT_PR_OU spo,
IGS_CA_INST ci,
IGS_EN_STDNT_PS_ATT sca
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number = p_sequence_number AND
ci.cal_type = spo.prg_cal_type AND
ci.sequence_number = spo.prg_ci_sequence_number AND
sca.person_id = spo.person_id AND
sca.course_cd = spo.course_cd;
SELECT ci.cal_type,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE ct.cal_type = ci.cal_type AND
ct.s_cal_cat = cst_progress AND
cs.cal_status = ci.cal_status AND
cs.s_cal_status = cst_active AND
ci.start_dt > cp_prg_start_dt AND
( EXISTS (SELECT 'x'
FROM IGS_PR_S_PRG_CAL spc1,
IGS_PR_S_PRG_CAL spc2
WHERE spc1.s_control_num = 1 AND
spc2.s_control_num = 1 AND
spc1.prg_cal_type = cp_spo_prg_cal_type AND
spc2.prg_cal_type = ci.cal_type AND
spc1.stream_num = spc2.stream_num) OR
EXISTS (SELECT 'x'
FROM IGS_PR_S_OU_PRG_CAL sopc1,
IGS_PR_S_OU_PRG_CAL sopc2
WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT( p_course_cd,
cp_sca_version_number,
sopc1.org_unit_cd,
sopc1.ou_start_dt) = 'Y' AND
sopc1.prg_cal_type = cp_spo_prg_cal_type AND
sopc2.org_unit_cd = sopc1.org_unit_cd AND
sopc2.ou_start_dt = sopc1.ou_start_dt AND
sopc2.prg_cal_type = ci.cal_type AND
sopc1.stream_num = sopc2.stream_num) OR
EXISTS (SELECT 'x'
FROM IGS_PR_S_CRV_PRG_CAL scpc1,
IGS_PR_S_CRV_PRG_CAL scpc2
WHERE scpc1.course_cd = p_course_cd AND
scpc1.version_number = cp_sca_version_number AND
scpc1.prg_cal_type = cp_spo_prg_cal_type AND
scpc2.course_cd = scpc1.course_cd AND
scpc2.version_number = scpc1.version_number AND
scpc2.prg_cal_type = ci.cal_type AND
scpc1.stream_num = scpc2.stream_num)) AND
(cp_spo_duration_type = cst_normal OR
(IGS_PR_GEN_001.PRGP_get_drtn_efctv (
ci.cal_type,
ci.sequence_number,
p_person_id,
p_course_cd) = 'Y') AND
EXISTS (
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST_REL cir
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_coursE_cd AND
sua.unit_attempt_status IN ('ENROLLED','COMPLETED','DISCONTIN') AND
cir.sup_cal_type = ci.cal_type AND
cir.sup_ci_sequence_number = ci.sequence_number AND
cir.sub_cal_type = sua.cal_type AND
cir.sub_ci_sequence_number = sua.ci_sequence_number))
ORDER BY ci.start_dt;
SELECT 'X'
FROM IGS_PR_STDNT_PR_OU
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
prg_cal_type = p_prg_cal_type AND
prg_ci_sequence_number = p_prg_ci_sequence_number AND
rule_check_dt = p_rule_check_dt AND
progression_rule_cat = p_progression_rule_cat AND
pra_sequence_number = p_pra_sequence_number AND
decision_status IN ('APPROVED', 'PENDING');
SELECT 'X'
FROM IGS_PR_SDT_PR_RU_CK sprc
WHERE sprc.person_id = p_person_id AND
sprc.course_cd = p_course_cd AND
sprc.prg_cal_type = p_prg_cal_type AND
sprc.prg_ci_sequence_number = p_prg_ci_sequence_number AND
sprc.rule_check_dt = p_rule_check_dt AND
sprc.progression_rule_cat = p_progression_rule_cat AND
sprc.pra_sequence_number = p_pra_sequence_number AND
sprc.rule_check_dt
= ( SELECT MAX(sprc2.rule_check_dt)
FROM IGS_PR_SDT_PR_RU_CK sprc2
WHERE sprc2.person_id = sprc.person_id AND
sprc2.course_cd = sprc.course_cd AND
sprc2.prg_cal_type = sprc.prg_cal_type AND
sprc2.prg_ci_sequence_number = sprc.prg_ci_sequence_number AND
sprc2.progression_rule_cat = sprc.progression_rule_cat AND
sprc2.pra_sequence_number = sprc.pra_sequence_number);
SELECT IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
dai.dt_alias,
dai.sequence_number,
p_prg_cal_type,
p_prg_sequence_number)
FROM IGS_CA_DA_INST dai
WHERE dai.cal_type = p_prg_cal_type AND
dai.ci_sequence_number = p_prg_sequence_number AND
dai.dt_alias = p_dt_alias
ORDER BY 1 DESC;
gv_err_inserting VARCHAR2(255);
SELECT poc.course_cd
FROM IGS_PR_OU_PS poc
WHERE poc.progression_rule_cat = p_progression_rule_cat AND
poc.pra_sequence_number = p_pra_sequence_number AND
poc.pro_sequence_number = p_pro_sequence_number;
SELECT pous.unit_set_cd,
pous.us_version_number
FROM IGS_PR_OU_UNIT_SET pous
WHERE pous.progression_rule_cat = p_progression_rule_cat AND
pous.pra_sequence_number = p_pra_sequence_number AND
pous.pro_sequence_number = p_pro_sequence_number;
SELECT poa.award_cd
FROM IGS_PR_OU_AWD poa
WHERE poa.progression_rule_cat = p_progression_rule_cat AND
poa.pra_sequence_number = p_pra_sequence_number AND
poa.pro_sequence_number = p_pro_sequence_number;
SELECT popu.unit_cd,
popu.s_unit_type
FROM IGS_PR_OU_UNIT popu
WHERE popu.progression_rule_cat = p_progression_rule_cat AND
popu.pra_sequence_number = p_pra_sequence_number AND
popu.pro_sequence_number = p_pro_sequence_number;
SELECT popf.fund_Code
FROM IGS_PR_OU_FND popf
WHERE popf.progression_rule_cat = p_progression_rule_cat AND
popf.pra_sequence_number = p_pra_sequence_number AND
popf.pro_sequence_number = p_pro_sequence_number;
IGS_PR_OU_PS_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
X_COURSE_CD =>v_poc_rec.course_cd,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
IGS_PR_OU_UNIT_SET_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
X_UNIT_SET_CD =>v_pous_rec.unit_set_cd,
X_US_VERSION_NUMBER =>v_pous_rec.us_version_number,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
IGS_PR_OU_AWD_PKG.INSERT_ROW (
X_ROWID => lv_rowid,
X_PROGRESSION_RULE_CAT => p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER => p_new_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER => p_new_pro_sequence_number,
X_AWARD_CD => v_poa_rec.award_cd,
X_MODE => 'R'
);
IGS_PR_OU_UNIT_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
X_UNIT_CD =>v_popu_rec.unit_cd,
X_S_UNIT_TYPE =>v_popu_rec.s_unit_type,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
IGS_PR_OU_FND_PKG.INSERT_ROW (
X_ROWID => lv_rowid,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
X_FUND_CODE => v_popf_rec.fund_code,
X_MODE =>'R'
);
SELECT pra.s_relation_type,
pra.progression_rule_cd,
pra.rul_sequence_number,
pra.attendance_type,
pra.ou_org_unit_cd,
pra.ou_start_dt,
pra.course_type,
pra.crv_course_cd,
pra.crv_version_number,
pra.sca_person_id,
pra.sca_course_cd,
pra.pro_progression_rule_cat,
pra.pro_pra_sequence_number,
pra.pro_sequence_number,
pra.spo_person_id,
pra.spo_course_cd,
pra.spo_sequence_number,
pra.message,
pra.reference_cd
FROM IGS_PR_RU_APPL pra
WHERE pra.progression_rule_cat = p_progression_rule_cat AND
pra.sequence_number = p_sequence_number AND
pra.logical_delete_dt IS NULL;
SELECT prgc.s_rule_call_cd
FROM IGS_PR_RU_CAT prgc
WHERE prgc.progression_rule_cat = p_progression_rule_cat;
SELECT IGS_PR_PRA_SEQUENCE_NO_S.NEXTVAL
FROM dual;
SELECT IGS_PR_PRO_SEQUENCE_NO_S.NEXTVAL
FROM dual;
SELECT prrc.prg_cal_type,
prrc.start_sequence_number,
prrc.end_sequence_number,
prrc.start_effective_period,
prrc.num_of_applications
FROM IGS_PR_RU_CA_TYPE prrc
WHERE prrc.progression_rule_cat = p_progression_rule_cat AND
prrc.pra_sequence_number = p_sequence_number;
SELECT pro.sequence_number,
pro.number_of_failures,
pro.progression_outcome_type,
pro.apply_automatically_ind,
pro.prg_rule_repeat_fail_type,
pro.override_show_cause_ind,
pro.override_appeal_ind,
pro.duration,
pro.duration_type,
pro.rank,
pro.encmb_course_group_cd,
pro.restricted_enrolment_cp,
pro.restricted_attendance_type,
pro.comments
FROM IGS_PR_RU_OU pro
WHERE pro.progression_rule_cat = p_progression_rule_cat AND
pro.pra_sequence_number = p_sequence_number AND
-- anilk, bug#2784198
pro.logical_delete_dt IS NULL;
SELECT pra.progression_rule_cat,
pra.sequence_number
FROM IGS_PR_RU_APPL pra
WHERE pra.pro_progression_rule_cat = cp_pro_progression_rule_cat AND
pra.pro_pra_sequence_number = cp_pro_pra_sequence_number AND
pra.pro_sequence_number = cp_pro_sequence_number;
IGS_PR_RU_APPL_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_SEQUENCE_NUMBER =>gv_new_pra_sequence_number,
X_S_RELATION_TYPE =>v_pra_s_relation_type,
X_PROGRESSION_RULE_CD =>v_pra_progression_rule_cd,
X_REFERENCE_CD =>v_pra_reference_cd,
X_RUL_SEQUENCE_NUMBER =>v_pra_rul_sequence_number,
X_ATTENDANCE_TYPE =>v_pra_attendance_type,
X_OU_ORG_UNIT_CD =>v_pra_ou_org_unit_cd,
X_OU_START_DT =>v_pra_ou_start_dt,
X_COURSE_TYPE =>v_pra_course_type,
X_CRV_COURSE_CD =>v_pra_crv_course_cd,
X_CRV_VERSION_NUMBER =>v_pra_crv_version_number,
X_SCA_PERSON_ID =>v_pra_sca_person_id,
X_SCA_COURSE_CD =>v_pra_sca_course_cd,
X_PRO_PROGRESSION_RULE_CAT =>v_pra_pro_progression_rule_cat,
X_PRO_PRA_SEQUENCE_NUMBER =>v_pra_pro_pra_sequence_number,
X_PRO_SEQUENCE_NUMBER =>v_pra_pro_sequence_number,
X_SPO_PERSON_ID =>v_pra_spo_person_id,
X_SPO_COURSE_CD =>v_pra_spo_course_cd,
X_SPO_SEQUENCE_NUMBER =>v_pra_spo_sequence_number,
X_LOGICAL_DELETE_DT =>NULL,
X_MESSAGE =>v_pra_message,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
IGS_PR_RU_CA_TYPE_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER=> gv_new_pra_sequence_number,
X_PRG_CAL_TYPE =>v_prrc_rec.prg_cal_type,
X_START_SEQUENCE_NUMBER =>v_prrc_rec.start_sequence_number,
X_END_SEQUENCE_NUMBER =>v_prrc_rec.end_sequence_number,
X_START_EFFECTIVE_PERIOD =>v_prrc_rec.start_effective_period,
X_NUM_OF_APPLICATIONS =>v_prrc_rec.num_of_applications,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.INSERT_RU_CA');
IGS_PR_RU_OU_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PROGRESSION_RULE_CAT=> p_progression_rule_cat,
X_PRA_SEQUENCE_NUMBER=> gv_new_pra_sequence_number,
X_SEQUENCE_NUMBER =>v_new_pro_sequence_number,
X_NUMBER_OF_FAILURES =>v_pro_rec.number_of_failures,
X_PROGRESSION_OUTCOME_TYPE =>v_pro_rec.progression_outcome_type,
X_APPLY_AUTOMATICALLY_IND =>v_pro_rec.apply_automatically_ind,
X_PRG_RULE_REPEAT_FAIL_TYPE =>v_pro_rec.prg_rule_repeat_fail_type,
X_OVERRIDE_SHOW_CAUSE_IND =>v_pro_rec.override_show_cause_ind,
X_OVERRIDE_APPEAL_IND =>v_pro_rec.override_appeal_ind,
X_DURATION =>v_pro_rec.duration,
X_DURATION_TYPE =>v_pro_rec.duration_type,
X_RANK => v_pro_rec.rank,
X_ENCMB_COURSE_GROUP_CD =>v_pro_rec.encmb_course_group_cd,
X_RESTRICTED_ENROLMENT_CP =>v_pro_rec.restricted_enrolment_cp,
X_RESTRICTED_ATTENDANCE_TYPE =>v_pro_rec.restricted_attendance_type,
X_COMMENTS =>v_pro_rec.comments,
X_MODE =>'R',
X_ORG_ID => l_org_id
);
gv_err_inserting := 'Creation OF NEW records failed FOR the '
|| 'following TABLES :'
|| gv_err_ins_rule
|| gv_err_ins_pra
|| gv_err_ins_prrc
|| gv_err_ins_pro
|| gv_err_ins_poc
|| gv_err_ins_pous
|| gv_err_ins_popf
|| gv_err_ins_popu;
SELECT pot.encumbrance_type
FROM IGS_PR_STDNT_PR_OU spo,
igs_pr_ou_type pot
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number = p_spo_sequence_number AND
pot.progression_outcome_type = spo.progression_outcome_type;
SELECT spo.sequence_number,
spo.progression_outcome_type,
spo.expiry_dt,
pot.encumbrance_type
FROM IGS_PR_STDNT_PR_OU spo,
igs_pr_ou_type pot
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number <> p_spo_sequence_number AND
spo.decision_status = cst_approved AND
pot.progression_outcome_type = spo.progression_outcome_type AND
pot.encumbrance_type IS NOT NULL AND
IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
p_person_id,
p_course_cd,
spo.sequence_number,
spo.expiry_dt) <> cst_expired;
SELECT 'X'
FROM IGS_PE_PERSENC_EFFCT pee,
IGS_PE_PERS_ENCUMB pen
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd IS NULL AND
(pen.expiry_dt IS NULL OR
pen.expiry_dt > TRUNC(SYSDATE)) AND
pee.person_id = pen.person_id AND
pee.encumbrance_type = pen.encumbrance_type AND
pee.pen_start_dt = pen.start_dt AND
(pee.expiry_dt IS NULL OR
pee.expiry_dt > TRUNC(SYSDATE)) AND
pee.s_encmb_effect_type IN
(
SELECT s_encmb_effect_type
FROM igs_fi_enc_dflt_eft etde
WHERE etde.encumbrance_type = cp_encumbrance_type);
SELECT spo.*, spo.ROWID
FROM IGS_PR_STDNT_PR_OU spo
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number = cp_spo_sequence_number
FOR UPDATE NOWAIT;
SELECT edte.s_encmb_effect_type
FROM igs_fi_enc_dflt_eft edte
WHERE edte.encumbrance_type = cp_encumbrance_type AND
edte.s_encmb_effect_type IN (
cst_sus_course,
cst_exc_course,
cst_exc_crs_gp,
cst_exc_crs_us,
cst_exc_crs_u,
cst_rqrd_crs_u,
cst_rstr_at_ty,
cst_rstr_ge_cp,
cst_rstr_le_cp);
SELECT pee.s_encmb_effect_type
FROM IGS_PE_PERS_ENCUMB pen,
IGS_PE_PERSENC_EFFCT pee
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd = p_course_cd AND
pen.spo_sequence_number = cp_spo_sequence_number AND
pen.person_id = pee.person_id AND
pen.encumbrance_type = pee.encumbrance_type AND
pen.start_dt = pee.pen_start_dt AND
pee.s_encmb_effect_type IN (
cst_sus_course,
cst_exc_course,
cst_exc_crs_gp,
cst_exc_crs_us,
cst_exc_crs_u,
cst_rqrd_crs_u,
cst_rstr_at_ty,
cst_rstr_ge_cp,
cst_rstr_le_cp) AND
(
( pee.expiry_dt IS NOT NULL AND
pee.expiry_dt > TRUNC(SYSDATE)) OR
( pee.expiry_dt IS NULL AND
IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
p_person_id,
p_course_cd,
cp_spo_sequence_number,
cp_spo_expiry_dt) <> cst_expired)
);
IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
X_ROWID => v_spo_expire_rec.ROWID,
X_PERSON_ID => v_spo_expire_rec.PERSON_ID,
X_COURSE_CD => v_spo_expire_rec.COURSE_CD,
X_SEQUENCE_NUMBER => v_spo_expire_rec.SEQUENCE_NUMBER,
X_PRG_CAL_TYPE => v_spo_expire_rec.PRG_CAL_TYPE,
X_PRG_CI_SEQUENCE_NUMBER => v_spo_expire_rec.PRG_CI_SEQUENCE_NUMBER,
X_RULE_CHECK_DT => v_spo_expire_rec.RULE_CHECK_DT,
X_PROGRESSION_RULE_CAT => v_spo_expire_rec.PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER => v_spo_expire_rec.PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_spo_expire_rec.PRO_SEQUENCE_NUMBER,
X_PROGRESSION_OUTCOME_TYPE => v_spo_expire_rec.PROGRESSION_OUTCOME_TYPE,
X_DURATION => v_spo_expire_rec.DURATION,
X_DURATION_TYPE => v_spo_expire_rec.DURATION_TYPE,
X_DECISION_STATUS => v_spo_expire_rec.DECISION_STATUS,
X_DECISION_DT => v_spo_expire_rec.DECISION_DT,
X_DECISION_ORG_UNIT_CD => v_spo_expire_rec.DECISION_ORG_UNIT_CD,
X_DECISION_OU_START_DT => v_spo_expire_rec.DECISION_OU_START_DT,
X_APPLIED_DT => v_spo_expire_rec.APPLIED_DT,
X_SHOW_CAUSE_EXPIRY_DT => v_spo_expire_rec.SHOW_CAUSE_EXPIRY_DT,
X_SHOW_CAUSE_DT => v_spo_expire_rec.SHOW_CAUSE_DT,
X_SHOW_CAUSE_OUTCOME_DT => v_spo_expire_rec.SHOW_CAUSE_OUTCOME_DT,
X_SHOW_CAUSE_OUTCOME_TYPE => v_spo_expire_rec.SHOW_CAUSE_OUTCOME_TYPE,
X_APPEAL_EXPIRY_DT => v_spo_expire_rec.APPEAL_EXPIRY_DT,
X_APPEAL_DT => v_spo_expire_rec.APPEAL_DT,
X_APPEAL_OUTCOME_DT => v_spo_expire_rec.APPEAL_OUTCOME_DT,
X_APPEAL_OUTCOME_TYPE => v_spo_expire_rec.APPEAL_OUTCOME_TYPE,
X_ENCMB_COURSE_GROUP_CD => v_spo_expire_rec.ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP => v_spo_expire_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_spo_expire_rec.RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS => v_spo_expire_rec.COMMENTS,
X_SHOW_CAUSE_COMMENTS => v_spo_expire_rec.SHOW_CAUSE_COMMENTS,
X_APPEAL_COMMENTS => v_spo_expire_rec.APPEAL_COMMENTS,
X_EXPIRY_DT => v_spo_expire_rec.EXPIRY_DT,
X_PRO_PRA_SEQUENCE_NUMBER => v_spo_expire_rec.PRO_PRA_SEQUENCE_NUMBER,
X_MODE => 'R'
);
SELECT sca.*, sca.ROWID
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd
FOR UPDATE NOWAIT;
SELECT sca.version_number,
sca.progression_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT PARTY_NUMBER FROM HZ_PARTIES
WHERE PARTY_ID = cp_party_id;
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_rec.ROWID,
X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_upd_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => v_sca_upd_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_upd_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_upd_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => v_sca_upd_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => v_sca_upd_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => v_sca_upd_rec.VERSION_NUMBER,
X_CAL_TYPE => v_sca_upd_rec.CAL_TYPE,
X_LOCATION_CD => v_sca_upd_rec.LOCATION_CD,
X_ATTENDANCE_MODE => v_sca_upd_rec.ATTENDANCE_MODE,
X_ATTENDANCE_TYPE => v_sca_upd_rec.ATTENDANCE_TYPE,
X_COO_ID => v_sca_upd_rec.COO_ID,
X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => v_sca_upd_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_progression_status, --updating this column
X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_upd_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => v_sca_upd_rec.DISCONTINUED_DT,
X_DISCONTINUATION_REASON_CD => v_sca_upd_rec.DISCONTINUATION_REASON_CD,
X_LAPSED_DT => v_sca_upd_rec.LAPSED_DT,
X_FUNDING_SOURCE => v_sca_upd_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => v_sca_upd_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => v_sca_upd_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.IGS_PR_CLASS_STD_ID,
X_PRIMARY_PROGRAM_TYPE => v_sca_upd_rec.PRIMARY_PROGRAM_TYPE,
X_PRIMARY_PROG_TYPE_SOURCE => v_sca_upd_rec.PRIMARY_PROG_TYPE_SOURCE,
X_CATALOG_CAL_TYPE => v_sca_upd_rec.CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM => v_sca_upd_rec.CATALOG_SEQ_NUM,
X_KEY_PROGRAM => v_sca_upd_rec.KEY_PROGRAM,
X_MANUAL_OVR_CMPL_DT_IND => v_sca_upd_rec.MANUAL_OVR_CMPL_DT_IND ,
X_OVERRIDE_CMPL_DT => v_sca_upd_rec.OVERRIDE_CMPL_DT ,
X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => v_sca_upd_rec.ATTRIBUTE1 ,
X_ATTRIBUTE2 => v_sca_upd_rec.ATTRIBUTE2 ,
X_ATTRIBUTE3 => v_sca_upd_rec.ATTRIBUTE3 ,
X_ATTRIBUTE4 => v_sca_upd_rec.ATTRIBUTE4 ,
X_ATTRIBUTE5 => v_sca_upd_rec.ATTRIBUTE5 ,
X_ATTRIBUTE6 => v_sca_upd_rec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => v_sca_upd_rec.ATTRIBUTE7 ,
X_ATTRIBUTE8 => v_sca_upd_rec.ATTRIBUTE8 ,
X_ATTRIBUTE9 => v_sca_upd_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => v_sca_upd_rec.ATTRIBUTE10 ,
X_ATTRIBUTE11 => v_sca_upd_rec.ATTRIBUTE11 ,
X_ATTRIBUTE12 => v_sca_upd_rec.ATTRIBUTE12 ,
X_ATTRIBUTE13 => v_sca_upd_rec.ATTRIBUTE13 ,
X_ATTRIBUTE14 => v_sca_upd_rec.ATTRIBUTE14 ,
X_ATTRIBUTE15 => v_sca_upd_rec.ATTRIBUTE15 ,
X_ATTRIBUTE16 => v_sca_upd_rec.ATTRIBUTE16 ,
X_ATTRIBUTE17 => v_sca_upd_rec.ATTRIBUTE17 ,
X_ATTRIBUTE18 => v_sca_upd_rec.ATTRIBUTE18 ,
X_ATTRIBUTE19 => v_sca_upd_rec.ATTRIBUTE19 ,
X_ATTRIBUTE20 => v_sca_upd_rec.ATTRIBUTE20 ,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.future_dated_trans_flag
);
|| ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
|| kdande 17-Dec-2002 Bug# 2543601. Changed the query for c_pe
|| cursor to use hz_parties instead of igs_pe_person
|| nalkumar 19-NOV-2002 Bug NO: 2658550. Modified this function as per the FA110 PR Enh.
|| pkpatel 7-OCT-2002 Bug No: 2600842
|| Added the parameter x_auth_resp_id in the call to igs_pe_pers_encumb_pkg
|| (reverse chronological order - newest change first)
*/
gv_other_detail VARCHAR2(255);
SELECT spo.decision_status,
spo.encmb_course_group_cd,
spo.restricted_enrolment_cp,
spo.restricted_attendance_type,
spo.expiry_dt,
pot.encumbrance_type,
att.closed_ind
FROM IGS_PR_STDNT_PR_OU spo,
igs_pr_ou_type pot,
igs_en_atd_type att
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number = p_spo_sequence_number AND
pot.progression_outcome_type = spo.progression_outcome_type AND
att.attendance_type (+)= spo.restricted_attendance_type;
SELECT p.party_id person_id
FROM hz_parties p,
fnd_user u
WHERE u.user_id = FND_GLOBAL.USER_ID
AND u.person_party_id = p.party_id
AND SUBSTR (igs_en_gen_003.get_staff_ind (p.party_id), 1, 1) = 'Y';
SELECT pen.expiry_dt
FROM IGS_PE_PERS_ENCUMB pen
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd = p_course_cd AND
pen.spo_sequence_number = p_spo_sequence_number;
SELECT etde.s_encmb_effect_type
FROM igs_fi_enc_dflt_eft etde
WHERE etde.encumbrance_type = cp_encumbrance_type;
SELECT pee.*,
pee.ROWID
FROM IGS_PE_PERS_ENCUMB pen,
IGS_PE_PERSENC_EFFCT pee,
igs_fi_enc_dflt_eft etde
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd = p_course_cd AND
pen.spo_sequence_number = p_spo_sequence_number AND
pen.person_id = pee.person_id AND
pen.encumbrance_type = pee.encumbrance_type AND
pen.start_dt = pee.pen_start_dt AND
pee.encumbrance_type = etde.encumbrance_type AND
pee.s_encmb_effect_type = etde.s_encmb_effect_type AND
(pee.expiry_dt IS NULL OR
pee.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT IGS_PR_PEE_SEQUEN_S.NEXTVAL
FROM DUAL;
SELECT pce.*, pce.ROWID
FROM igs_pe_course_excl pce
WHERE pce.person_id = p_person_id AND
pce.encumbrance_type = cp_encumbrance_type AND
pce.pen_start_dt = cp_pen_start_dt AND
pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
pce.pee_start_dt = cp_pee_start_dt AND
pce.pee_sequence_number = cp_pee_sequence_number AND
(pce.expiry_dt IS NULL OR
pce.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pce.*, pce.ROWID
FROM igs_pe_course_excl pce
WHERE pce.person_id = p_person_id AND
pce.encumbrance_type = cp_encumbrance_type AND
pce.pen_start_dt = cp_pen_start_dt AND
pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
pce.pee_start_dt = cp_pee_start_dt AND
pce.pee_sequence_number = cp_pee_sequence_number AND
pce.course_cd = cp_course_cd AND
pce.pce_start_dt = cp_pce_start_dt
FOR UPDATE NOWAIT;
SELECT spc.course_cd
FROM igs_pr_stdnt_pr_ps spc
WHERE spc.person_id = p_person_id AND
spc.spo_course_cd = p_course_cd AND
spc.spo_sequence_number = p_spo_sequence_number;
SELECT pcge.*, pcge.ROWID
FROM igs_pe_crs_grp_excl pcge
WHERE pcge.person_id = p_person_id AND
pcge.encumbrance_type = cp_encumbrance_type AND
pcge.pen_start_dt = cp_pen_start_dt AND
pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
pcge.pee_start_dt = cp_pee_start_dt AND
pcge.pee_sequence_number = cp_pee_sequence_number AND
(pcge.expiry_dt IS NULL OR
pcge.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pcge.*, pcge.ROWID
FROM igs_pe_crs_grp_excl pcge
WHERE pcge.person_id = p_person_id AND
pcge.encumbrance_type = cp_encumbrance_type AND
pcge.pen_start_dt = cp_pen_start_dt AND
pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
pcge.pee_start_dt = cp_pee_start_dt AND
pcge.pee_sequence_number = cp_pee_sequence_number AND
pcge.course_group_cd = cp_course_group_cd AND
pcge.pcge_start_dt = cp_pcge_start_dt
FOR UPDATE NOWAIT;
SELECT puse.*, puse.ROWID
FROM igs_pe_unt_set_excl puse
WHERE puse.person_id = p_person_id AND
puse.encumbrance_type = cp_encumbrance_type AND
puse.pen_start_dt = cp_pen_start_dt AND
puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
puse.pee_start_dt = cp_pee_start_dt AND
puse.pee_sequence_number = cp_pee_sequence_number AND
(puse.expiry_dt IS NULL OR
puse.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT puse.*, puse.ROWID
FROM igs_pe_unt_set_excl puse
WHERE puse.person_id = p_person_id AND
puse.encumbrance_type = cp_encumbrance_type AND
puse.pen_start_dt = cp_pen_start_dt AND
puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
puse.pee_start_dt = cp_pee_start_dt AND
puse.pee_sequence_number = cp_pee_sequence_number AND
puse.unit_set_cd = cp_unit_set_cd AND
puse.us_version_number = cp_us_version_number AND
puse.puse_start_dt = cp_puse_start_dt
FOR UPDATE NOWAIT;
SELECT spus.unit_set_cd,
spus.version_number
FROM igs_pr_sdt_pr_unt_st spus
WHERE spus.person_id = p_person_id AND
spus.course_cd = p_course_cd AND
spus.spo_sequence_number = p_spo_sequence_number;
SELECT pue.*, pue.ROWID
FROM igs_pe_pers_unt_excl pue
WHERE pue.person_id = p_person_id AND
pue.encumbrance_type = cp_encumbrance_type AND
pue.pen_start_dt = cp_pen_start_dt AND
pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
pue.pee_start_dt = cp_pee_start_dt AND
pue.pee_sequence_number = cp_pee_sequence_number AND
(pue.expiry_dt IS NULL OR
pue.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pue.*, pue.ROWID
FROM igs_pe_pers_unt_excl pue
WHERE pue.person_id = p_person_id AND
pue.encumbrance_type = cp_encumbrance_type AND
pue.pen_start_dt = cp_pen_start_dt AND
pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
pue.pee_start_dt = cp_pee_start_dt AND
pue.pee_sequence_number = cp_pee_sequence_number AND
pue.unit_cd = cp_unit_cd AND
pue.pue_start_dt = cp_pue_start_dt
FOR UPDATE NOWAIT;
SELECT pur.*, pur.ROWID
FROM igs_pe_unt_requirmnt pur
WHERE pur.person_id = p_person_id AND
pur.encumbrance_type = cp_encumbrance_type AND
pur.pen_start_dt = cp_pen_start_dt AND
pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
pur.pee_start_dt = cp_pee_start_dt AND
pur.pee_sequence_number = cp_pee_sequence_number AND
(pur.expiry_dt IS NULL OR
pur.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pur.*, pur.ROWID
FROM igs_pe_unt_requirmnt pur
WHERE pur.person_id = p_person_id AND
pur.encumbrance_type = cp_encumbrance_type AND
pur.pen_start_dt = cp_pen_start_dt AND
pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
pur.pee_start_dt = cp_pee_start_dt AND
pur.pee_sequence_number = cp_pee_sequence_number AND
pur.unit_cd = cp_unit_cd AND
pur.pur_start_dt = cp_pur_start_dt
FOR UPDATE NOWAIT;
SELECT spu.unit_cd
FROM igs_pr_stdnt_pr_unit spu
WHERE spu.person_id = p_person_id AND
spu.course_cd = p_course_cd AND
spu.spo_sequence_number = p_spo_sequence_number AND
spu.s_unit_type = cp_s_unit_type;
SELECT apply_to_course_ind
FROM igs_en_encmb_efcttyp seet
WHERE seet.s_encmb_effect_type = cp_s_encmb_effect_type;
SELECT pfe.*, pfe.rowid
FROM igs_pe_fund_excl pfe
WHERE pfe.person_id = p_person_id AND
pfe.encumbrance_type = cp_encumbrance_type AND
pfe.pen_start_dt = cp_pen_start_dt AND
pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
pfe.pee_start_dt = cp_pee_start_dt AND
pfe.pee_sequence_number = cp_pee_sequence_number AND
(pfe.expiry_dt IS NULL OR
pfe.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pfe.*, pfe.ROWID
FROM igs_pe_fund_excl pfe
WHERE pfe.person_id = p_person_id AND
pfe.encumbrance_type = cp_encumbrance_type AND
pfe.pen_start_dt = cp_pen_start_dt AND
pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
pfe.pee_start_dt = cp_pee_start_dt AND
pfe.pee_sequence_number = cp_pee_sequence_number AND
pfe.fund_code = cp_fund_code AND
pfe.pfe_start_dt = cp_pfe_start_dt
FOR UPDATE NOWAIT;
SELECT spf.fund_code
FROM igs_pr_stdnt_pr_fnd spf
WHERE spf.person_id = p_person_id AND
spf.course_cd = p_course_cd AND
spf.spo_sequence_number = p_spo_sequence_number;
|| ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
|| pkpatel 7-OCT-2002 Bug No: 2600842
|| Added the parameter x_auth_resp_id in the call to igs_pe_pers_encumb_pkg
|| (reverse chronological order - newest change first)
*/
gvl_other_detail VARCHAR2(255);
SELECT pen.*,
pen.ROWID
FROM IGS_PE_PERS_ENCUMB pen
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd = p_course_cd AND
pen.spo_sequence_number = p_spo_sequence_number AND
((cp_expiry_dt IS NULL OR
cp_expiry_dt > gcst_sysdate) OR
(pen.expiry_dt IS NULL OR
pen.expiry_dt > gcst_sysdate))
FOR UPDATE NOWAIT;
SELECT pee.*,
pee.ROWID
FROM IGS_PE_PERSENC_EFFCT pee
WHERE pee.person_id = p_person_id AND
pee.encumbrance_type = cp_encumbrance_type AND
pee.pen_start_dt = cp_pen_start_dt AND
((cp_expiry_dt IS NULL OR
cp_expiry_dt > gcst_sysdate) OR
(pee.expiry_dt IS NULL OR
pee.expiry_dt > gcst_sysdate))
FOR UPDATE NOWAIT;
SELECT pce.*,pce.ROWID
FROM igs_pe_course_excl pce
WHERE pce.person_id = p_person_id AND
pce.encumbrance_type = cp_encumbrance_type AND
pce.pen_start_dt = cp_pen_start_dt AND
pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
pce.pee_start_dt = cp_pee_start_dt AND
pce.pee_sequence_number = cp_pee_sequence_number AND
(pce.expiry_dt IS NULL OR
pce.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pcge.*, pcge.ROWID
FROM igs_pe_crs_grp_excl pcge
WHERE pcge.person_id = p_person_id AND
pcge.encumbrance_type = cp_encumbrance_type AND
pcge.pen_start_dt = cp_pen_start_dt AND
pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
pcge.pee_start_dt = cp_pee_start_dt AND
pcge.pee_sequence_number = cp_pee_sequence_number AND
(pcge.expiry_dt IS NULL OR
pcge.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT puse.*,puse.ROWID
FROM igs_pe_unt_set_excl puse
WHERE puse.person_id = p_person_id AND
puse.encumbrance_type = cp_encumbrance_type AND
puse.pen_start_dt = cp_pen_start_dt AND
puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
puse.pee_start_dt = cp_pee_start_dt AND
puse.pee_sequence_number = cp_pee_sequence_number AND
(puse.expiry_dt IS NULL OR
puse.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pue.*, pue.ROWID
FROM igs_pe_pers_unt_excl pue
WHERE pue.person_id = p_person_id AND
pue.encumbrance_type = cp_encumbrance_type AND
pue.pen_start_dt = cp_pen_start_dt AND
pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
pue.pee_start_dt = cp_pee_start_dt AND
pue.pee_sequence_number = cp_pee_sequence_number AND
(pue.expiry_dt IS NULL OR
pue.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pur.*, pur.ROWID
FROM igs_pe_unt_requirmnt pur
WHERE pur.person_id = p_person_id AND
pur.encumbrance_type = cp_encumbrance_type AND
pur.pen_start_dt = cp_pen_start_dt AND
pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
pur.pee_start_dt = cp_pee_start_dt AND
pur.pee_sequence_number = cp_pee_sequence_number AND
(pur.expiry_dt IS NULL OR
pur.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pfe.*, pfe.ROWID
FROM igs_pe_fund_excl pfe
WHERE pfe.person_id = p_person_id AND
pfe.encumbrance_type = cp_encumbrance_type AND
pfe.pen_start_dt = cp_pen_start_dt AND
pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
pfe.pee_start_dt = cp_pee_start_dt AND
pfe.pee_sequence_number = cp_pee_sequence_number AND
(pfe.expiry_dt IS NULL OR
pfe.expiry_dt > gcst_sysdate)
FOR UPDATE NOWAIT;
igs_pe_course_excl_pkg.UPDATE_ROW(
X_ROWID => v_pce_rec.ROWID,
X_PERSON_ID => v_pce_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pce_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pce_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_CD => v_pce_rec.COURSE_CD,
X_PCE_START_DT => v_pce_rec.PCE_START_DT,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R'
);
igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
X_ROWID => v_pcge_rec.ROWID,
X_PERSON_ID => v_pcge_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R'
);
igs_pe_unt_set_excl_pkg.UPDATE_ROW(
X_ROWID => v_puse_rec.ROWID,
X_PERSON_ID => v_puse_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_puse_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_puse_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
X_EXPIRY_DT => p_expiry_dt,
X_MODE => 'R'
);
igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
X_ROWID => v_pue_rec.ROWID,
X_PERSON_ID => v_pue_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pue_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pue_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pue_rec.UNIT_CD,
X_PUE_START_DT => v_pue_rec.PUE_START_DT,
X_EXPIRY_DT => P_EXPIRY_DT,
X_MODE => 'R'
);
igs_pe_unt_requirmnt_pkg.update_row(
X_ROWID => v_pur_rec.ROWID,
X_PERSON_ID => v_pur_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pur_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pur_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pur_rec.UNIT_CD,
X_PUR_START_DT => v_pur_rec.PUR_START_DT,
X_EXPIRY_DT => P_EXPIRY_DT,
X_MODE => 'R'
);
igs_pe_fund_excl_pkg.update_row(
X_ROWID => v_pur_rec.rowid ,
X_FUND_EXCL_ID => v_pur_rec.fund_excl_id ,
X_PERSON_ID => v_pur_rec.person_id ,
X_ENCUMBRANCE_TYPE => v_pur_rec.encumbrance_type ,
X_PEN_START_DT => v_pur_rec.pen_start_dt ,
X_S_ENCMB_EFFECT_TYPE => v_pur_rec.s_encmb_effect_type,
X_PEE_START_DT => v_pur_rec.pee_start_dt ,
X_PEE_SEQUENCE_NUMBER => v_pur_rec.pee_sequence_number,
X_FUND_CODE => v_pur_rec.fund_code ,
X_PFE_START_DT => v_pur_rec.pfe_start_dt ,
X_EXPIRY_DT => p_expiry_dt ,
X_MODE => 'R');
igs_pe_persenc_effct_pkg.update_row(
X_ROWID => v_pee_rec.ROWID,
X_PERSON_ID => v_pee_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pee_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pee_rec.PEE_START_DT,
X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
X_EXPIRY_DT => P_EXPIRY_DT,
X_COURSE_CD => v_pee_rec.COURSE_CD,
X_RESTRICTED_ENROLMENT_CP => v_pee_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
X_MODE => 'R'
);
IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW(
X_ROWID => v_pen_rec.ROWID,
X_PERSON_ID => v_pen_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pen_rec.ENCUMBRANCE_TYPE,
X_START_DT => v_pen_rec.START_DT,
X_EXPIRY_DT => p_expiry_dt,
X_AUTHORISING_PERSON_ID => v_pen_rec.AUTHORISING_PERSON_ID,
X_COMMENTS => v_pen_rec.COMMENTS,
X_SPO_COURSE_CD => v_pen_rec.SPO_COURSE_CD,
X_SPO_SEQUENCE_NUMBER => v_pen_rec.SPO_SEQUENCE_NUMBER,
X_CAL_TYPE => v_pen_rec.CAL_TYPE,
X_SEQUENCE_NUMBER => v_pen_rec.SEQUENCE_NUMBER,
x_auth_resp_id => v_pen_rec.auth_resp_id,
x_external_reference => v_pen_rec.external_reference,
X_MODE => 'R'
);
SELECT pee.*,
pee.ROWID
FROM IGS_PE_PERS_ENCUMB pen,
IGS_PE_PERSENC_EFFCT pee,
igs_fi_enc_dflt_eft etde
WHERE pen.person_id = p_person_id AND
pen.spo_course_cd = p_course_cd AND
pen.spo_sequence_number = p_spo_sequence_number AND
pen.person_id = pee.person_id AND
pen.encumbrance_type = pee.encumbrance_type AND
pen.start_dt = pee.pen_start_dt AND
pee.encumbrance_type = etde.encumbrance_type AND
pee.s_encmb_effect_type <> etde.s_encmb_effect_type AND
(pee.expiry_dt IS NULL OR
pee.expiry_dt >= gcst_sysdate)
FOR UPDATE NOWAIT;
SELECT pce.*, pce.ROWID
FROM igs_pe_course_excl pce
WHERE pce.person_id = p_person_id AND
pce.encumbrance_type = cp_encumbrance_type AND
pce.pen_start_dt = cp_pen_start_dt AND
pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
pce.pee_start_dt = cp_pee_start_dt AND
pce.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
SELECT pcge.*, pcge.ROWID
FROM igs_pe_crs_grp_excl pcge
WHERE pcge.person_id = p_person_id AND
pcge.encumbrance_type = cp_encumbrance_type AND
pcge.pen_start_dt = cp_pen_start_dt AND
pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
pcge.pee_start_dt = cp_pee_start_dt AND
pcge.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
SELECT puse.*, puse.ROWID
FROM igs_pe_unt_set_excl puse
WHERE puse.person_id = p_person_id AND
puse.encumbrance_type = cp_encumbrance_type AND
puse.pen_start_dt = cp_pen_start_dt AND
puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
puse.pee_start_dt = cp_pee_start_dt AND
puse.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
SELECT pue.*, pue.ROWID
FROM igs_pe_pers_unt_excl pue
WHERE pue.person_id = p_person_id AND
pue.encumbrance_type = cp_encumbrance_type AND
pue.pen_start_dt = cp_pen_start_dt AND
pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
pue.pee_start_dt = cp_pee_start_dt AND
pue.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
SELECT pur.*, pur.ROWID
FROM igs_pe_unt_requirmnt pur
WHERE pur.person_id = p_person_id AND
pur.encumbrance_type = cp_encumbrance_type AND
pur.pen_start_dt = cp_pen_start_dt AND
pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
pur.pee_start_dt = cp_pee_start_dt AND
pur.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
SELECT pfe.*, pfe.ROWID
FROM IGS_PE_FUND_EXCL pfe
WHERE pfe.person_id = p_person_id AND
pfe.encumbrance_type = cp_encumbrance_type AND
pfe.pen_start_dt = cp_pen_start_dt AND
pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
pfe.pee_start_dt = cp_pee_start_dt AND
pfe.pee_sequence_number = cp_pee_sequence_number
FOR UPDATE NOWAIT;
igs_pe_course_excl_pkg.UPDATE_ROW(
X_ROWID => v_pce_rec.ROWID,
X_PERSON_ID => v_pce_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pce_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pce_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_CD => v_pce_rec.COURSE_CD,
X_PCE_START_DT => v_pce_rec.PCE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime, --gjha1
X_MODE => 'R'
);
igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
X_ROWID => v_pcge_rec.ROWID,
X_PERSON_ID => v_pcge_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime , --gjha1
X_MODE => 'R'
);
igs_pe_unt_set_excl_pkg.UPDATE_ROW(
X_ROWID => v_puse_rec.ROWID,
X_PERSON_ID => v_puse_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_puse_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_puse_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime, --gjha1
X_MODE => 'R'
);
igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
X_ROWID => v_pue_rec.ROWID,
X_PERSON_ID => v_pue_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pue_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pue_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pue_rec.UNIT_CD,
X_PUE_START_DT => v_pue_rec.PUE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime, --gjha1
X_MODE => 'R'
);
igs_pe_fund_excl_pkg.update_row(
X_ROWID => v_pfe_rec.rowid ,
X_FUND_EXCL_ID => v_pfe_rec.fund_excl_id ,
X_PERSON_ID => v_pfe_rec.person_id ,
X_ENCUMBRANCE_TYPE => v_pfe_rec.encumbrance_type ,
X_PEN_START_DT => v_pfe_rec.pen_start_dt ,
X_S_ENCMB_EFFECT_TYPE => v_pfe_rec.s_encmb_effect_type,
X_PEE_START_DT => v_pfe_rec.pee_start_dt ,
X_PEE_SEQUENCE_NUMBER => v_pfe_rec.pee_sequence_number,
X_FUND_CODE => v_pfe_rec.fund_code ,
X_PFE_START_DT => v_pfe_rec.pfe_start_dt ,
X_EXPIRY_DT => gcst_sysdatetime ,
X_MODE => 'R');
UPDATE igs_pe_unt_requirmnt
SET expiry_dt = gcst_sysdate
WHERE CURRENT OF c_pur;
igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
X_ROWID => v_pur_rec.ROWID,
X_PERSON_ID => v_pur_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pur_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pur_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pur_rec.UNIT_CD,
X_PUR_START_DT => v_pur_rec.PUR_START_DT,
X_EXPIRY_DT => gcst_sysdatetime,--gjha1
X_MODE => 'R'
);
IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
X_ROWID => v_pee_rec.ROWID,
X_PERSON_ID => v_pee_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pee_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pee_rec.PEE_START_DT,
X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
X_EXPIRY_DT => gcst_sysdatetime, --updated
X_COURSE_CD => v_pee_rec.COURSE_CD,
X_RESTRICTED_ENROLMENT_CP => v_pee_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
X_MODE => 'R'
);
SAVEPOINT sp_before_update;
ROLLBACK TO sp_before_update;
ROLLBACK TO sp_before_update;
ROLLBACK TO sp_before_update;
IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
X_ROWID => v_pee_rec.ROWID,
X_PERSON_ID => v_pee_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pee_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pee_rec.PEE_START_DT,
X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
X_EXPIRY_DT => v_expiry_dt, --updated
X_COURSE_CD => v_pee_rec.COURSE_CD,
X_RESTRICTED_ENROLMENT_CP => v_spo_rec.restricted_enrolment_cp, --updated
X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
X_MODE => 'R'
);
IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
X_ROWID => v_pee_rec.ROWID,
X_PERSON_ID => v_pee_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pee_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pee_rec.PEE_START_DT,
X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
X_EXPIRY_DT => v_expiry_dt, --updated
X_COURSE_CD => v_pee_rec.COURSE_CD,
X_RESTRICTED_ENROLMENT_CP => v_pee_rec.restricted_enrolment_cp,
X_RESTRICTED_ATTENDANCE_TYPE => v_spo_rec.restricted_attendance_type, --updated
X_MODE => 'R'
);
igs_pe_course_excl_pkg.UPDATE_ROW(
X_ROWID => v_pce_rec.ROWID,
X_PERSON_ID => v_pce_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pce_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pce_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_CD => v_pce_rec.COURSE_CD,
X_PCE_START_DT => v_pce_rec.PCE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime,
X_MODE => 'R'
);
igs_pe_course_excl_pkg.UPDATE_ROW(
X_ROWID => v_pce_dup_rec.ROWID,
X_PERSON_ID => v_pce_dup_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pce_dup_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pce_dup_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pce_dup_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pce_dup_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pce_dup_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_CD => v_pce_dup_rec.COURSE_CD,
X_PCE_START_DT => v_pce_dup_rec.PCE_START_DT,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R'
);
/*INSERT INTO igs_pe_course_excl (
person_id,
encumbrance_type,
pen_start_dt,
s_encmb_effect_type,
pee_start_dt,
pee_sequence_number,
course_cd,
pce_start_dt,
expiry_dt)
VALUES (
p_person_id,
v_pee_rec.encumbrance_type,
v_pee_rec.pen_start_dt,
v_pee_rec.s_encmb_effect_type,
v_pee_rec.pee_start_dt,
v_pee_rec.sequence_number,
v_spc_table(v_index1),
gcst_sysdate,
v_expiry_dt); */
igs_pe_course_excl_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_pee_rec.encumbrance_type,
X_PEN_START_DT =>v_pee_rec.pen_start_dt,
X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
X_PEE_START_DT =>v_pee_rec.pee_start_dt,
X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
X_COURSE_CD =>v_spc_table(v_index1),
X_PCE_START_DT =>gcst_sysdatetime, ---- GJHA Changed it from gcst_sysdate
X_EXPIRY_DT =>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
X_ROWID => v_pcge_rec.ROWID,
X_PERSON_ID => v_pcge_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime,
X_MODE => 'R'
);
igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
X_ROWID => v_pcge_dup_rec.ROWID,
X_PERSON_ID => v_pcge_dup_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pcge_dup_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pcge_dup_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pcge_dup_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pcge_dup_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pcge_dup_rec.PEE_SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_pcge_dup_rec.COURSE_GROUP_CD,
X_PCGE_START_DT => v_pcge_dup_rec.PCGE_START_DT,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R'
);
igs_pe_crs_grp_excl_pkg.INSERT_ROW(
X_ROWID => lv_rowid,
X_PERSON_ID => P_PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pee_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pee_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
X_COURSE_GROUP_CD => v_spo_rec.encmb_course_group_cd,
X_PCGE_START_DT => gcst_sysdatetime, --gjha Changed it from gcst_sysdate
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R'
);
igs_pe_unt_set_excl_pkg.UPDATE_ROW(
X_ROWID => v_puse_rec.ROWID,
X_PERSON_ID => v_puse_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_puse_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_puse_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
X_EXPIRY_DT => gcst_sysdate,
X_MODE => 'R'
);
igs_pe_unt_set_excl_pkg.UPDATE_ROW(
X_ROWID => v_puse_dup_rec.ROWID,
X_PERSON_ID => v_puse_dup_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_puse_dup_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_puse_dup_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_puse_dup_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_puse_dup_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_puse_dup_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_SET_CD => v_puse_dup_rec.UNIT_SET_CD,
X_US_VERSION_NUMBER => v_puse_dup_rec.US_VERSION_NUMBER,
X_PUSE_START_DT => v_puse_dup_rec.PUSE_START_DT,
X_EXPIRY_DT => v_EXPIRY_DT,
X_MODE => 'R'
);
igs_pe_unt_set_excl_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE=> v_pee_rec.encumbrance_type,
X_PEN_START_DT=> v_pee_rec.pen_start_dt,
X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
X_PEE_START_DT =>v_pee_rec.pee_start_dt,
X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
X_UNIT_SET_CD =>v_spus_table(v_index1).unit_set_cd,
X_US_VERSION_NUMBER =>v_spus_table(v_index1).version_number,
X_PUSE_START_DT =>gcst_sysdatetime, -- GJHA Changed it from gcst_sysdate
X_EXPIRY_DT =>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
X_ROWID => v_pue_rec.ROWID,
X_PERSON_ID => v_pue_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pue_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pue_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pue_rec.UNIT_CD,
X_PUE_START_DT => v_pue_rec.PUE_START_DT,
X_EXPIRY_DT => gcst_sysdatetime,
X_MODE => 'R'
);
igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
X_ROWID => v_pue_dup_rec.ROWID,
X_PERSON_ID => v_pue_dup_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pue_dup_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pue_dup_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pue_dup_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pue_dup_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pue_dup_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pue_dup_rec.UNIT_CD,
X_PUE_START_DT => v_pue_dup_rec.PUE_START_DT,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R'
);
igs_pe_pers_unt_excl_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_pee_rec.encumbrance_type,
X_PEN_START_DT =>v_pee_rec.pen_start_dt,
X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
X_PEE_START_DT =>v_pee_rec.pee_start_dt,
X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
X_UNIT_CD =>v_spu_table(v_index1),
X_PUE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
X_EXPIRY_DT=>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
X_ROWID => v_pur_rec.ROWID,
X_PERSON_ID => v_pur_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pur_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pur_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pur_rec.UNIT_CD,
X_PUR_START_DT => v_pur_rec.PUR_START_DT,
X_EXPIRY_DT => gcst_sysdatetime,
X_MODE => 'R'
);
igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
X_ROWID => v_pur_dup_rec.ROWID,
X_PERSON_ID => v_pur_dup_rec.PERSON_ID,
X_ENCUMBRANCE_TYPE => v_pur_dup_rec.ENCUMBRANCE_TYPE,
X_PEN_START_DT => v_pur_dup_rec.PEN_START_DT,
X_S_ENCMB_EFFECT_TYPE => v_pur_dup_rec.S_ENCMB_EFFECT_TYPE,
X_PEE_START_DT => v_pur_dup_rec.PEE_START_DT,
X_PEE_SEQUENCE_NUMBER => v_pur_dup_rec.PEE_SEQUENCE_NUMBER,
X_UNIT_CD => v_pur_dup_rec.UNIT_CD,
X_PUR_START_DT => v_pur_dup_rec.PUR_START_DT,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R'
);
igs_pe_unt_requirmnt_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE=> v_pee_rec.encumbrance_type,
X_PEN_START_DT=> v_pee_rec.pen_start_dt,
X_S_ENCMB_EFFECT_TYPE=> v_pee_rec.s_encmb_effect_type,
X_PEE_START_DT =>v_pee_rec.pee_start_dt,
X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
X_UNIT_CD =>v_spu_table(v_index1),
X_PUR_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
X_EXPIRY_DT=>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_fund_excl_pkg.update_row(
X_ROWID => v_pfe_rec.rowid ,
X_FUND_EXCL_ID => v_pfe_rec.fund_excl_id ,
X_PERSON_ID => v_pfe_rec.person_id ,
X_ENCUMBRANCE_TYPE => v_pfe_rec.encumbrance_type ,
X_PEN_START_DT => v_pfe_rec.pen_start_dt ,
X_S_ENCMB_EFFECT_TYPE => v_pfe_rec.s_encmb_effect_type,
X_PEE_START_DT => v_pfe_rec.pee_start_dt ,
X_PEE_SEQUENCE_NUMBER => v_pfe_rec.pee_sequence_number,
X_FUND_CODE => v_pfe_rec.fund_code ,
X_PFE_START_DT => v_pfe_rec.pfe_start_dt ,
X_EXPIRY_DT => gcst_sysdatetime ,
X_MODE => 'R');
igs_pe_fund_excl_pkg.update_row(
X_ROWID => v_pfe_dup_rec.rowid ,
X_FUND_EXCL_ID => v_pfe_dup_rec.fund_excl_id ,
X_PERSON_ID => v_pfe_dup_rec.person_id ,
X_ENCUMBRANCE_TYPE => v_pfe_dup_rec.encumbrance_type ,
X_PEN_START_DT => v_pfe_dup_rec.pen_start_dt ,
X_S_ENCMB_EFFECT_TYPE => v_pfe_dup_rec.s_encmb_effect_type,
X_PEE_START_DT => v_pfe_dup_rec.pee_start_dt ,
X_PEE_SEQUENCE_NUMBER => v_pfe_dup_rec.pee_sequence_number,
X_FUND_CODE => v_pfe_dup_rec.fund_code ,
X_PFE_START_DT => v_pfe_dup_rec.pfe_start_dt ,
X_EXPIRY_DT => v_expiry_dt ,
X_MODE => 'R');
igs_pe_fund_excl_pkg.insert_row (
X_ROWID => l_rowid,
X_FUND_EXCL_ID => l_fund_excl_id,
X_PERSON_ID => p_person_id,
X_ENCUMBRANCE_TYPE => v_pee_rec.encumbrance_type,
X_PEN_START_DT => v_pee_rec.pee_start_dt,
X_S_ENCMB_EFFECT_TYPE => v_pee_rec.s_encmb_effect_type,
X_PEE_START_DT => v_pee_rec.pee_start_dt,
X_PEE_SEQUENCE_NUMBER => v_pee_rec.sequence_number,
X_FUND_CODE => v_spf_table(v_index1),
X_PFE_START_DT => gcst_sysdatetime,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R' );
ROLLBACK TO sp_before_update;
IGS_PE_PERS_ENCUMB_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
X_START_DT=> gcst_sysdatetime, --gjha Chaged from gcst_sysdatetime -- Modified by Prajeesh to sysdatetime
X_EXPIRY_DT =>v_expiry_dt,
X_AUTHORISING_PERSON_ID=> v_authorising_person_id,
X_COMMENTS =>NULL,
X_SPO_COURSE_CD => p_course_cd,
X_SPO_SEQUENCE_NUMBER => lv_spo_sequence_number,
x_auth_resp_id => NULL,
x_external_reference => NULL, -- ssawhney, should always be NULL when created from internal system
X_MODE =>'R'
);
SELECT DECODE( v_etde_table(v_index1).s_encmb_effect_type,
cst_rstr_ge_cp, v_spo_rec.restricted_enrolment_cp,
cst_rstr_le_cp, v_spo_rec.restricted_enrolment_cp,
NULL) FROM DUAL;
SELECT DECODE( v_etde_table(v_index1).s_encmb_effect_type,
cst_rstr_at_ty, v_spo_rec.restricted_attendance_type,
NULL) FROM DUAL;
IGS_PE_PERSENC_EFFCT_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdatetime --Modified by Prajeesh to sysdatetime
X_S_ENCMB_EFFECT_TYPE=> v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdate
X_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_EXPIRY_DT=> v_expiry_dt,
X_COURSE_CD =>v_apply_course_cd,
X_RESTRICTED_ENROLMENT_CP =>v_decode_val1,
X_RESTRICTED_ATTENDANCE_TYPE =>v_decode_val2,
X_MODE =>'R'
);
igs_pe_course_excl_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from datetime --Modified by Prajeesh to sysdatetime
X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT =>gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_COURSE_CD =>v_spc_rec.course_cd,
X_PCE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
X_EXPIRY_DT =>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_crs_grp_excl_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from sysdatetime --Modified by Prajeesh to sysdatetime
X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT =>gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_COURSE_GROUP_CD =>v_spo_rec.encmb_course_group_cd,
X_PCGE_START_DT=> gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
X_EXPIRY_DT =>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_unt_set_excl_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from sysdatetime --modified by Prajeesh
X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT =>gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_UNIT_SET_CD =>v_spus_rec.unit_set_cd,
X_US_VERSION_NUMBER =>v_spus_rec.version_number,
X_PUSE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
X_EXPIRY_DT =>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_pers_unt_excl_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from sysdatetime --Modified by Prajeesh
X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT =>gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_UNIT_CD =>v_spu_rec.unit_cd,
X_PUE_START_DT => gcst_sysdatetime, -- gjha Changed it from v_spu_table(v_index1),
X_EXPIRY_DT=>v_expiry_dt, --Gjha Changed it from gcst_sysdate,
X_MODE =>'R'
);
igs_pe_unt_requirmnt_pkg.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID =>p_person_id,
X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdatetime --Modified by Prajeesh
X_S_ENCMB_EFFECT_TYPE=> v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT =>gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
X_UNIT_CD =>v_spu_rec.unit_cd,
X_PUR_START_DT =>gcst_sysdatetime,
X_EXPIRY_DT=>v_expiry_dt,
X_MODE =>'R'
);
igs_pe_fund_excl_pkg.insert_row (
X_ROWID => l_rowid,
X_FUND_EXCL_ID => l_fund_excl_id,
X_PERSON_ID => p_person_id,
X_ENCUMBRANCE_TYPE => v_spo_rec.encumbrance_type,
X_PEN_START_DT => gcst_sysdatetime,
X_S_ENCMB_EFFECT_TYPE => v_etde_table(v_index1).s_encmb_effect_type,
X_PEE_START_DT => gcst_sysdatetime,
X_PEE_SEQUENCE_NUMBER => v_pee_sequence_number,
X_FUND_CODE => v_spf_rec.fund_code,
X_PFE_START_DT => gcst_sysdatetime,
X_EXPIRY_DT => v_expiry_dt,
X_MODE => 'R' );
ROLLBACK TO sp_before_update;