[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
While Selecting Person Group member, start date and end date are checked.
Additional issue: Local variables l_c_message_name, l_c_outcome have to be re-initialized
before start of each Enrollment Credit
vchappid 09-Jan-2003 Bug# 2730010, when a student has program attempt as Enrolled and no action is being
taken on the Deposit then different message is logged in the log file.
vchappid 06-Jan-2003 Bug# 2729948, message name changed from IGS_FI_PRS_OR_PRSIDGRP to IGS_FI_PRS_PRSIDGRP_NULL
whenever user don't provide either person id or person id group as parameter to the request
Additionally, Person Group Id parameter is being validated twice.
Removed duplicate Person Group ID validation.
******************************************************************/
g_c_yes CONSTANT VARCHAR2(1) := 'Y';
SELECT 'X'
FROM igs_ca_inst_rel r1
WHERE r1.sub_cal_type = cp_c_load_cal_type AND
r1.sub_ci_sequence_number = cp_n_load_ci_seq_num AND
r1.sup_cal_type = cp_c_acad_cal_type AND
r1.sup_ci_sequence_number = cp_n_acad_ci_seq_num AND
EXISTS (SELECT 'X'
FROM igs_ca_inst_rel r2
WHERE r2.sup_cal_type = r1.sup_cal_type AND
r2.sup_ci_sequence_number = r1.sup_ci_sequence_number AND
r2.sub_cal_type = cp_c_adm_cal_type AND
r2.sub_ci_sequence_number = cp_n_adm_ci_seq_num);
SELECT spa.course_cd course_cd,
spa.version_number version_number,
ps.course_type course_type,
spa.course_attempt_status course_attempt_status
FROM igs_en_stdnt_ps_att spa,
igs_ps_ver ps
WHERE spa.course_cd = ps.course_cd AND
spa.version_number = ps.version_number AND
spa.person_id = cp_n_person_id AND
(
(cp_c_sca_status= g_c_sca_enrolled AND spa.course_attempt_status = 'ENROLLED')
OR
(
cp_c_sca_status = g_c_sca_other
AND
spa.course_attempt_status IN ('COMPLETED','INTERMIT','INACTIVE','UNCONFIRM','ENROLLED')
)
)
AND
(
(spa.course_attempt_status ='ENROLLED' AND
EXISTS
(
SELECT 'X'
FROM igs_en_su_attempt sua,
igs_ca_load_to_teach_v lci
WHERE sua.person_id = spa.person_id AND
sua.course_cd = spa.course_cd AND
sua.cal_type = lci.teach_cal_type AND
sua.ci_sequence_number = lci.teach_ci_sequence_number AND
(lci.load_cal_type = cp_c_load_cal_type AND
lci.load_ci_sequence_number = cp_n_load_cal_seq_num) AND
(
(cp_c_sca_status= g_c_sca_enrolled AND sua.unit_attempt_status = 'ENROLLED')
OR
(cp_c_sca_status = g_c_sca_other
AND
sua.unit_attempt_status IN ('UNCONFIRM','WAITLISTED','COMPLETED')
)
)
)
OR
(spa.course_attempt_status IN ('COMPLETED','INTERMIT','INACTIVE','UNCONFIRM') AND cp_c_sca_status = g_c_sca_other)
)
);
SELECT person_number, full_name
FROM igs_fi_parties_v
WHERE person_id = cp_n_person_id;
SELECT credit_type_name,payment_credit_type_name
FROM igs_fi_cr_types_v
WHERE credit_type_id = cp_credit_type_id;
SELECT credit_type_name
FROM igs_fi_cr_types
WHERE credit_type_id = cp_credit_type_id AND
credit_class = p_c_credit_class;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_n_person_id;
SELECT group_cd
FROM igs_pe_persid_group
WHERE group_id= p_n_person_id_grp AND
TRUNC(create_dt)<= TRUNC(SYSDATE) AND
closed_ind = 'N';
SELECT t.cal_type, t.start_dt, t.end_dt
FROM igs_ca_inst t, igs_ca_type ty
WHERE t.cal_type = ty.cal_type
AND ty.s_cal_cat='LOAD'
AND t.cal_type = cp_c_cal_type
AND t.sequence_number = cp_n_seq_num;
SELECT crd.*
FROM igs_fi_credits_all crd,
igs_fi_cr_types crt
WHERE crd.party_id = cp_n_person_id AND
crd.credit_type_id = crt.credit_type_id AND
crt.credit_class = g_c_othdeposit AND
(
(crt.credit_type_id = cp_c_cr_type_id AND cp_c_cr_type_id IS NOT NULL)
OR
cp_c_cr_type_id IS NULL
) AND
(
(crd.fee_cal_type = cp_c_fee_cal_type
AND
crd.fee_ci_sequence_number = cp_n_fee_seq_num
)
OR
(
(crd.fee_cal_type IS NULL AND cp_c_fee_cal_type IS NULL)
AND
(crd.fee_ci_sequence_number IS NULL AND cp_n_fee_seq_num IS NULL)
)
) AND
crd.status = 'CLEARED';
Modified cursor c_enr_dpsts to select credit type id also.
vchappid 09-Jan-2003 Bug# 2730010, when a student has program attempt as Enrolled and no action is being
taken on the Deposit then different message is logged in the log file.
Additional issue: Local variables l_c_message_name, l_c_outcome have to be re-initialized
before start of each Enrollment Credit
******************************************************************/
-- Cursor for fetching all Deposit Transactions of Enrolment Deposit Credit Class
-- with 'Cleared' status and matching credit type if provided
CURSOR c_enr_dpsts(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
cp_n_cr_type_id igs_fi_cr_types.credit_type_id%TYPE)
IS
SELECT crd.credit_id,crd.credit_type_id,
TO_NUMBER(crd.source_transaction_ref) source_transaction_ref,
crd.credit_number,
crd.amount
FROM igs_fi_credits crd,
igs_fi_cr_types crt
WHERE crd.party_id = cp_n_person_id AND
crd.credit_type_id = crt.credit_type_id AND
(
(crt.credit_type_id = cp_n_cr_type_id AND cp_n_cr_type_id IS NOT NULL)
OR
cp_n_cr_type_id IS NULL
) AND
crt.credit_class = g_c_enrdeposit AND
crd.status = 'CLEARED'
ORDER BY crd.credit_number;
SELECT admission_appl_number, application_type
FROM igs_ad_appl
WHERE person_id = cp_n_person_id AND
(application_id IS NOT NULL AND application_id = cp_n_appl_id);
SELECT NVL(enroll_deposit_level, g_c_appl_dep_lvl) enroll_deposit_level
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = cp_c_application_type AND
closed_ind <> 'Y';
SELECT inst.course_cd course_cd,
inst.crv_version_number version_number,
ps.course_type course_type
FROM igs_ad_ps_appl_inst inst,
igs_ad_appl aa,
igs_ps_ver ps,
igs_ad_ofrdfrmt_stat df,
igs_ad_ofr_resp_stat off
WHERE inst.person_id = cp_n_person_id AND
aa.person_id = inst.person_id AND
aa.admission_appl_number = inst.admission_appl_number AND
(
(inst.admission_appl_number = cp_c_adm_application_number AND cp_c_criteria = 'APPL')
OR
(inst.admission_appl_number <> cp_c_adm_application_number AND cp_c_criteria = 'ALL')
) AND
(inst.course_cd = ps.course_cd AND inst.crv_version_number = ps.version_number) AND
inst.adm_offer_resp_status = off.adm_offer_resp_status AND
inst.adm_offer_dfrmnt_status = df.adm_offer_dfrmnt_status AND
(
(off.s_adm_offer_resp_status = 'DEFERRAL' AND df.s_adm_offer_dfrmnt_status = 'CONFIRM' AND
check_acad_load_adm_rel(cp_load_cal_type,
cp_load_ci_seq_number,
inst.def_acad_cal_type,
inst.def_acad_ci_sequence_num,
inst.deferred_adm_cal_type,
inst.deferred_adm_ci_sequence_num) = 'Y'
)
OR
(off.s_adm_offer_resp_status = 'ACCEPTED'
AND
check_acad_load_adm_rel(cp_load_cal_type,
cp_load_ci_seq_number,
aa.acad_cal_type,
aa.acad_ci_sequence_number,
inst.adm_cal_type,
inst.adm_ci_sequence_number) = 'Y'
)
);
SELECT program_code,
version_number,
NULL course_type
FROM igs_ad_deplvl_prg
WHERE admission_application_type = cp_c_adm_appl_type AND
closed_ind='N';
SELECT NULL program_code,
NULL version_number,
program_type
FROM igs_ad_deplvl_prgty
WHERE admission_application_type = cp_c_adm_appl_type AND
closed_ind = 'N';
vchappid 09-Jan-2003 Bug# 2729935, While Selecting Person Group member, start date and end date is checked.
******************************************************************/
CURSOR c_person(cp_n_person_id igs_pe_person.person_id%TYPE,
cp_n_person_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE)
IS
SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE group_id = cp_n_person_grp_id AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date,SYSDATE)) AND TRUNC(NVL(end_date,SYSDATE)) AND
cp_n_person_id IS NULL AND
cp_n_person_grp_id IS NOT NULL
UNION
SELECT cp_n_person_id
FROM DUAL
WHERE cp_n_person_id IS NOT NULL AND
cp_n_person_grp_id IS NULL;