[Home] [Help]
358: sca.discontinuation_reason_cd
359: FROM IGS_PR_STDNT_PR_OU spo,
360: IGS_PR_OU_TYPE pot,
361: IGS_EN_STDNT_PS_ATT sca,
362: IGS_PS_VER crv
363: WHERE (
364: p_spo_person_id IS NULL
365: AND
366: -- Decision Status is approved and applied_dt is null/'0001/01/01
2898: --
2899: -- Append Program Type filter to the Where clause only when it is passed
2900: --
2901: IF (p_course_type IS NOT NULL) THEN
2902: from_clause := from_clause || ', igs_ps_ver_all crv';
2903: END IF;
2904: --
2905: stmt_str := 'SELECT sca.person_id, sca.course_cd, sca.version_number ';
2906: stmt_str := stmt_str || from_clause;
3084: DECLARE
3085: cst_prg_check CONSTANT VARCHAR2(10) := 'PRG_CHECK';
3086: cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
3087: cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
3088: v_course_type IGS_PS_VER.course_type%TYPE;
3089: v_start_dt DATE;
3090: v_cutoff_dt DATE;
3091: v_dummy VARCHAR2(1);
3092: CURSOR c_st IS
3156:
3157: v_st_lck_rec c_st_lck%ROWTYPE;
3158:
3159: CURSOR c_crv (
3160: cp_course_cd IGS_PS_VER.course_cd%TYPE,
3161: cp_version_number IGS_PS_VER.version_number%TYPE) IS
3162: SELECT crv.course_type
3163: FROM IGS_PS_VER crv
3164: WHERE crv.course_cd = cp_course_cd AND
3157: v_st_lck_rec c_st_lck%ROWTYPE;
3158:
3159: CURSOR c_crv (
3160: cp_course_cd IGS_PS_VER.course_cd%TYPE,
3161: cp_version_number IGS_PS_VER.version_number%TYPE) IS
3162: SELECT crv.course_type
3163: FROM IGS_PS_VER crv
3164: WHERE crv.course_cd = cp_course_cd AND
3165: crv.version_number = cp_version_number;
3159: CURSOR c_crv (
3160: cp_course_cd IGS_PS_VER.course_cd%TYPE,
3161: cp_version_number IGS_PS_VER.version_number%TYPE) IS
3162: SELECT crv.course_type
3163: FROM IGS_PS_VER crv
3164: WHERE crv.course_cd = cp_course_cd AND
3165: crv.version_number = cp_version_number;
3166: CURSOR c_str (
3167: cp_person_id IGS_PE_STD_TODO_REF.person_id%TYPE,
3543: v_rule_check_dt DATE DEFAULT SYSDATE;
3544: v_rolled_back BOOLEAN DEFAULT FALSE;
3545: v_sprc_insert_count INTEGER DEFAULT 0;
3546: v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
3547: v_course_type IGS_PS_VER.course_type%TYPE;
3548: v_message_text IGS_RU_ITEM.value%TYPE;
3549: v_start_dt DATE;
3550: v_result_status VARCHAR2(10);
3551: v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
3579: SELECT sca.course_attempt_status,
3580: sca.version_number,
3581: crv.course_type
3582: FROM IGS_EN_STDNT_PS_ATT sca,
3583: IGS_PS_VER crv
3584: WHERE sca.person_id = p_person_id AND
3585: sca.course_cd = p_course_cd AND
3586: crv.course_cd = sca.course_cd AND
3587: crv.version_number = sca.version_number;
3708:
3709: CURSOR c_crv IS
3710: SELECT crv.responsible_org_unit_cd,
3711: crv.responsible_ou_start_dt
3712: FROM IGS_PS_VER crv
3713: WHERE crv.course_cd = p_course_cd AND
3714: crv.version_number = p_sca_version_number;
3715: CURSOR c_poc IS
3716: SELECT poc.course_cd
5420:
5421:
5422: PROCEDURE prgpl_sca_process_rules (
5423: p_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE,
5424: p_course_type IGS_PS_VER.course_type%TYPE)
5425: IS
5426: BEGIN -- prgpl_sca_process_rules
5427: DECLARE
5428: cst_sca CONSTANT VARCHAR2(10) := 'SCA';
5562: pra1.sequence_number
5563: AND prctv1.prg_cal_type = prctv.prg_cal_type)
5564: OR EXISTS (
5565: SELECT 'x'
5566: FROM igs_ps_ver crv1
5567: WHERE crv1.course_cd = cp_course_cd
5568: AND crv1.version_number = cp_version_number
5569: AND ( ( crv1.responsible_org_unit_cd =
5570: pra.ou_org_unit_cd
5592: prctv.start_effective_period, prctv.num_of_applications,
5593: pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd,
5594: pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt,
5595: pra.course_type
5596: FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv, igs_ps_ver crv
5597: WHERE pra.s_relation_type = cst_cty
5598: AND pra.progression_rule_cat = cp_prg_rule_cat
5599: AND pra.logical_delete_dt IS NULL
5600: AND crv.course_type = pra.course_type