VIEW: APPS.IGS_PR_SCA_PRG_RULE_APPL_V

Source

View Text - Preformatted

SELECT sca.person_id, sca.course_cd, prctcv.prg_cal_type, prctcv.prg_ci_sequence_number, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( ci.cal_type, ci.sequence_number) || '/' || ci.alternate_code,1,20), pra.progression_rule_cat, pra.sequence_number, pra.rul_sequence_number, pra.s_relation_type, DECODE(pra.s_relation_type, 'OU', 'ORG UNIT', 'CTY', 'CRS TYPE', 'CRV', 'COURSE', 'SCA', 'STUDENT'), pra.progression_rule_cd, pra.reference_cd, SUBSTR(pra.progression_rule_cd || pra.reference_cd, 1, 10), pra.attendance_type, prctcv.start_effective_period, prctcv.num_of_applications, pra.created_by, pra.creation_date, pra.last_updated_by, pra.last_update_date, pra.last_update_login FROM IGS_CA_STAT cs, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv, IGS_CA_INST ci, IGS_PR_RU_APPL pra, IGS_PS_VER crv, IGS_EN_STDNT_PS_ATT sca WHERE pra.s_relation_type = 'CTY' AND pra.course_type = crv.course_type AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra.progression_rule_cat, pra.sequence_number, ci.cal_type, ci.sequence_number, prctcv.start_effective_period, prctcv.num_of_applications, pra.s_relation_type, pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd, pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt, pra.course_type) = 'Y' AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'CRV' AND pra1.logical_delete_dt IS NULL AND pra1.crv_course_cd = sca.course_cd AND pra1.crv_version_number = sca.version_number AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'OU' AND pra1.logical_delete_dt IS NULL AND IGS_PR_GEN_001.PRGP_GET_CRV_CMT( sca.course_cd, sca.version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt) ='Y' AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'SCA' AND pra1.logical_delete_dt IS NULL AND pra1.sca_person_id = sca.person_id AND pra1.sca_course_cd = sca.course_cd AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND sca.course_attempt_status NOT IN ('UNCONFIRM','COMPLETED') AND crv.course_cd = sca.course_cd AND crv.version_number = sca.version_number AND pra.logical_delete_dt IS NULL AND prctcv.progression_rule_cat = pra.progression_rule_cat AND prctcv.pra_sequence_number = pra.sequence_number AND prctcv.prg_cal_type = ci.cal_type AND prctcv.prg_ci_sequence_number = ci.sequence_number UNION ALL SELECT sca.person_id, sca.course_cd, prctcv.prg_cal_type, prctcv.prg_ci_sequence_number, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( ci.cal_type, ci.sequence_number) || '/' || ci.alternate_code,1,20), pra.progression_rule_cat, pra.sequence_number, pra.rul_sequence_number, pra.s_relation_type, DECODE(pra.s_relation_type, 'OU', 'ORG UNIT', 'CTY', 'CRS TYPE', 'CRV', 'COURSE', 'SCA', 'STUDENT'), pra.progression_rule_cd, pra.reference_cd, SUBSTR(pra.progression_rule_cd || pra.reference_cd, 1, 10), pra.attendance_type, prctcv.start_effective_period, prctcv.num_of_applications, pra.created_by, pra.creation_date, pra.last_updated_by, pra.last_update_date, pra.last_update_login FROM IGS_CA_STAT cs, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv, IGS_CA_INST ci, IGS_PR_RU_APPL pra, IGS_PS_VER crv, IGS_EN_STDNT_PS_ATT sca WHERE pra.s_relation_type = 'OU' AND IGS_PR_GEN_001.PRGP_GET_CRV_CMT( sca.course_cd, sca.version_number, pra.ou_org_unit_cd, pra.ou_start_dt) = 'Y' AND ( EXISTS ( SELECT 'x' FROM IGS_PS_OWN cow WHERE cow.course_cd = crv.course_cd AND cow.version_number = crv.version_number AND cow.percentage = 100 ) OR NOT EXISTS ( SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_RU_CA_TYPE_V prctv1 WHERE pra1.s_relation_type = 'OU' AND pra1.logical_delete_dt IS NULL AND pra1.progression_rule_cat = pra.progression_rule_cat AND pra1.sequence_number <> pra.sequence_number AND (pra1.ou_org_unit_cd <> pra.ou_org_unit_cd OR pra1.ou_start_dt <> pra.ou_start_dt) AND prctv1.progression_rule_cat = pra1.progression_rule_cat AND prctv1.pra_sequence_number = pra1.sequence_number AND prctv1.prg_cal_type = prctcv.prg_cal_type ) OR EXISTS ( SELECT 'x' FROM IGS_PS_VER crv1 WHERE crv1.course_cd = crv.course_cd AND crv1.version_number = crv.version_number AND ((crv.responsible_org_unit_cd = pra.ou_org_unit_cd AND crv.responsible_ou_start_dt = pra.ou_start_dt) OR IGS_OR_GEN_001.ORGP_GET_WITHIN_OU( pra.ou_org_unit_cd, pra.ou_start_dt, crv.responsible_org_unit_cd, crv.responsible_ou_start_dt, 'N') = 'Y') ) ) AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra.progression_rule_cat, pra.sequence_number, ci.cal_type, ci.sequence_number, prctcv.start_effective_period, prctcv.num_of_applications, pra.s_relation_type, pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd, pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt, pra.course_type) = 'Y' AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'CRV' AND pra1.logical_delete_dt IS NULL AND pra1.crv_course_cd = sca.course_cd AND pra1.crv_version_number = sca.version_number AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'SCA' AND pra1.logical_delete_dt IS NULL AND pra1.sca_person_id = sca.person_id AND pra1.sca_course_cd = sca.course_cd AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND sca.course_attempt_status NOT IN ('UNCONFIRM','COMPLETED') AND crv.course_cd = sca.course_cd AND crv.version_number = sca.version_number AND pra.logical_delete_dt IS NULL AND prctcv.progression_rule_cat = pra.progression_rule_cat AND prctcv.pra_sequence_number = pra.sequence_number AND prctcv.prg_cal_type = ci.cal_type AND prctcv.prg_ci_sequence_number = ci.sequence_number UNION ALL SELECT sca.person_id, sca.course_cd, prctcv.prg_cal_type, prctcv.prg_ci_sequence_number, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( ci.cal_type, ci.sequence_number) || '/' || ci.alternate_code,1,20), pra.progression_rule_cat, pra.sequence_number, pra.rul_sequence_number, pra.s_relation_type, DECODE(pra.s_relation_type, 'OU', 'ORG UNIT', 'CTY', 'CRS TYPE', 'CRV', 'COURSE', 'SCA', 'STUDENT'), pra.progression_rule_cd, pra.reference_cd, SUBSTR(pra.progression_rule_cd || pra.reference_cd, 1, 10), pra.attendance_type, prctcv.start_effective_period, prctcv.num_of_applications, pra.created_by, pra.creation_date, pra.last_updated_by, pra.last_update_date, pra.last_update_login FROM IGS_CA_STAT cs, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv, IGS_CA_INST ci, IGS_PR_RU_APPL pra, IGS_PS_VER crv, IGS_EN_STDNT_PS_ATT sca WHERE pra.s_relation_type = 'CRV' AND pra.crv_course_cd = sca.course_cd AND pra.crv_version_number = sca.version_number AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra.progression_rule_cat, pra.sequence_number, ci.cal_type, ci.sequence_number, prctcv.start_effective_period, prctcv.num_of_applications, pra.s_relation_type, pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd, pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt, pra.course_type) = 'Y' AND NOT EXISTS (SELECT 'x' FROM IGS_PR_RU_APPL pra1, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv1 WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra1.progression_rule_cat, pra1.sequence_number, prctcv1.prg_cal_type, prctcv1.prg_ci_sequence_number, prctcv1.start_effective_period, prctcv1.num_of_applications, pra1.s_relation_type, pra1.sca_person_id, pra1.sca_course_cd, pra1.crv_course_cd, pra1.crv_version_number, pra1.ou_org_unit_cd, pra1.ou_start_dt, pra1.course_type) = 'Y' AND pra1.s_relation_type = 'SCA' AND pra1.logical_delete_dt IS NULL AND pra1.sca_person_id = sca.person_id AND pra1.sca_course_cd = sca.course_cd AND pra1.progression_rule_cat = pra.progression_rule_cat AND prctcv1.progression_rule_cat = pra1.progression_rule_cat AND prctcv1.pra_sequence_number = pra1.sequence_number AND prctcv1.prg_cal_type = prctcv.prg_cal_type AND prctcv1.prg_ci_sequence_number = prctcv.prg_ci_sequence_number ) AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND sca.course_attempt_status NOT IN ('UNCONFIRM','COMPLETED') AND crv.course_cd = sca.course_cd AND crv.version_number = sca.version_number AND pra.logical_delete_dt IS NULL AND prctcv.progression_rule_cat = pra.progression_rule_cat AND prctcv.pra_sequence_number = pra.sequence_number AND prctcv.prg_cal_type = ci.cal_type AND prctcv.prg_ci_sequence_number = ci.sequence_number UNION ALL SELECT sca.person_id, sca.course_cd, prctcv.prg_cal_type, prctcv.prg_ci_sequence_number, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( ci.cal_type, ci.sequence_number) || '/' || ci.alternate_code,1,20), pra.progression_rule_cat, pra.sequence_number, pra.rul_sequence_number, pra.s_relation_type, DECODE(pra.s_relation_type, 'OU', 'ORG UNIT', 'CTY', 'CRS TYPE', 'CRV', 'COURSE', 'SCA', 'STUDENT'), pra.progression_rule_cd, pra.reference_cd, SUBSTR(pra.progression_rule_cd || pra.reference_cd, 1, 10), pra.attendance_type, prctcv.start_effective_period, prctcv.num_of_applications, pra.created_by, pra.creation_date, pra.last_updated_by, pra.last_update_date, pra.last_update_login FROM IGS_CA_STAT cs, IGS_PR_PRG_RU_CA_TYPE_CI_V prctcv, IGS_CA_INST ci, IGS_PR_RU_APPL pra, IGS_PS_VER crv, IGS_EN_STDNT_PS_ATT sca WHERE pra.s_relation_type = 'SCA' AND pra.sca_person_id = sca.person_id AND pra.sca_course_cd = sca.course_cd AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( sca.person_id, sca.course_cd, sca.version_number, crv.course_type, pra.progression_rule_cat, pra.sequence_number, ci.cal_type, ci.sequence_number, prctcv.start_effective_period, prctcv.num_of_applications, pra.s_relation_type, pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd, pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt, pra.course_type) = 'Y' AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND sca.course_attempt_status NOT IN ('UNCONFIRM','COMPLETED') AND crv.course_cd = sca.course_cd AND crv.version_number = sca.version_number AND pra.logical_delete_dt IS NULL AND prctcv.progression_rule_cat = pra.progression_rule_cat AND prctcv.pra_sequence_number = pra.sequence_number AND prctcv.prg_cal_type = ci.cal_type AND prctcv.prg_ci_sequence_number = ci.sequence_number

View Text - HTML Formatted

SELECT SCA.PERSON_ID
, SCA.COURSE_CD
, PRCTCV.PRG_CAL_TYPE
, PRCTCV.PRG_CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) || '/' || CI.ALTERNATE_CODE
, 1
, 20)
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, PRA.RUL_SEQUENCE_NUMBER
, PRA.S_RELATION_TYPE
, DECODE(PRA.S_RELATION_TYPE
, 'OU'
, 'ORG UNIT'
, 'CTY'
, 'CRS TYPE'
, 'CRV'
, 'COURSE'
, 'SCA'
, 'STUDENT')
, PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD
, SUBSTR(PRA.PROGRESSION_RULE_CD || PRA.REFERENCE_CD
, 1
, 10)
, PRA.ATTENDANCE_TYPE
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.CREATED_BY
, PRA.CREATION_DATE
, PRA.LAST_UPDATED_BY
, PRA.LAST_UPDATE_DATE
, PRA.LAST_UPDATE_LOGIN
FROM IGS_CA_STAT CS
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV
, IGS_CA_INST CI
, IGS_PR_RU_APPL PRA
, IGS_PS_VER CRV
, IGS_EN_STDNT_PS_ATT SCA
WHERE PRA.S_RELATION_TYPE = 'CTY'
AND PRA.COURSE_TYPE = CRV.COURSE_TYPE
AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.S_RELATION_TYPE
, PRA.SCA_PERSON_ID
, PRA.SCA_COURSE_CD
, PRA.CRV_COURSE_CD
, PRA.CRV_VERSION_NUMBER
, PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT
, PRA.COURSE_TYPE) = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'CRV'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.CRV_COURSE_CD = SCA.COURSE_CD
AND PRA1.CRV_VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'OU'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND IGS_PR_GEN_001.PRGP_GET_CRV_CMT( SCA.COURSE_CD
, SCA.VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT) ='Y'
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'SCA'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.SCA_PERSON_ID = SCA.PERSON_ID
AND PRA1.SCA_COURSE_CD = SCA.COURSE_CD
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND SCA.COURSE_ATTEMPT_STATUS NOT IN ('UNCONFIRM'
, 'COMPLETED')
AND CRV.COURSE_CD = SCA.COURSE_CD
AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA.LOGICAL_DELETE_DT IS NULL
AND PRCTCV.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER
AND PRCTCV.PRG_CAL_TYPE = CI.CAL_TYPE
AND PRCTCV.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER UNION ALL SELECT SCA.PERSON_ID
, SCA.COURSE_CD
, PRCTCV.PRG_CAL_TYPE
, PRCTCV.PRG_CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) || '/' || CI.ALTERNATE_CODE
, 1
, 20)
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, PRA.RUL_SEQUENCE_NUMBER
, PRA.S_RELATION_TYPE
, DECODE(PRA.S_RELATION_TYPE
, 'OU'
, 'ORG UNIT'
, 'CTY'
, 'CRS TYPE'
, 'CRV'
, 'COURSE'
, 'SCA'
, 'STUDENT')
, PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD
, SUBSTR(PRA.PROGRESSION_RULE_CD || PRA.REFERENCE_CD
, 1
, 10)
, PRA.ATTENDANCE_TYPE
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.CREATED_BY
, PRA.CREATION_DATE
, PRA.LAST_UPDATED_BY
, PRA.LAST_UPDATE_DATE
, PRA.LAST_UPDATE_LOGIN
FROM IGS_CA_STAT CS
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV
, IGS_CA_INST CI
, IGS_PR_RU_APPL PRA
, IGS_PS_VER CRV
, IGS_EN_STDNT_PS_ATT SCA
WHERE PRA.S_RELATION_TYPE = 'OU'
AND IGS_PR_GEN_001.PRGP_GET_CRV_CMT( SCA.COURSE_CD
, SCA.VERSION_NUMBER
, PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT) = 'Y'
AND ( EXISTS ( SELECT 'X'
FROM IGS_PS_OWN COW
WHERE COW.COURSE_CD = CRV.COURSE_CD
AND COW.VERSION_NUMBER = CRV.VERSION_NUMBER
AND COW.PERCENTAGE = 100 ) OR NOT EXISTS ( SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_RU_CA_TYPE_V PRCTV1
WHERE PRA1.S_RELATION_TYPE = 'OU'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRA1.SEQUENCE_NUMBER <> PRA.SEQUENCE_NUMBER
AND (PRA1.OU_ORG_UNIT_CD <> PRA.OU_ORG_UNIT_CD OR PRA1.OU_START_DT <> PRA.OU_START_DT)
AND PRCTV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE ) OR EXISTS ( SELECT 'X'
FROM IGS_PS_VER CRV1
WHERE CRV1.COURSE_CD = CRV.COURSE_CD
AND CRV1.VERSION_NUMBER = CRV.VERSION_NUMBER
AND ((CRV.RESPONSIBLE_ORG_UNIT_CD = PRA.OU_ORG_UNIT_CD
AND CRV.RESPONSIBLE_OU_START_DT = PRA.OU_START_DT) OR IGS_OR_GEN_001.ORGP_GET_WITHIN_OU( PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT
, CRV.RESPONSIBLE_ORG_UNIT_CD
, CRV.RESPONSIBLE_OU_START_DT
, 'N') = 'Y') ) )
AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.S_RELATION_TYPE
, PRA.SCA_PERSON_ID
, PRA.SCA_COURSE_CD
, PRA.CRV_COURSE_CD
, PRA.CRV_VERSION_NUMBER
, PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT
, PRA.COURSE_TYPE) = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'CRV'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.CRV_COURSE_CD = SCA.COURSE_CD
AND PRA1.CRV_VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL(SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'SCA'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.SCA_PERSON_ID = SCA.PERSON_ID
AND PRA1.SCA_COURSE_CD = SCA.COURSE_CD
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND SCA.COURSE_ATTEMPT_STATUS NOT IN ('UNCONFIRM'
, 'COMPLETED')
AND CRV.COURSE_CD = SCA.COURSE_CD
AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA.LOGICAL_DELETE_DT IS NULL
AND PRCTCV.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER
AND PRCTCV.PRG_CAL_TYPE = CI.CAL_TYPE
AND PRCTCV.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER UNION ALL SELECT SCA.PERSON_ID
, SCA.COURSE_CD
, PRCTCV.PRG_CAL_TYPE
, PRCTCV.PRG_CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) || '/' || CI.ALTERNATE_CODE
, 1
, 20)
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, PRA.RUL_SEQUENCE_NUMBER
, PRA.S_RELATION_TYPE
, DECODE(PRA.S_RELATION_TYPE
, 'OU'
, 'ORG UNIT'
, 'CTY'
, 'CRS TYPE'
, 'CRV'
, 'COURSE'
, 'SCA'
, 'STUDENT')
, PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD
, SUBSTR(PRA.PROGRESSION_RULE_CD || PRA.REFERENCE_CD
, 1
, 10)
, PRA.ATTENDANCE_TYPE
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.CREATED_BY
, PRA.CREATION_DATE
, PRA.LAST_UPDATED_BY
, PRA.LAST_UPDATE_DATE
, PRA.LAST_UPDATE_LOGIN
FROM IGS_CA_STAT CS
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV
, IGS_CA_INST CI
, IGS_PR_RU_APPL PRA
, IGS_PS_VER CRV
, IGS_EN_STDNT_PS_ATT SCA
WHERE PRA.S_RELATION_TYPE = 'CRV'
AND PRA.CRV_COURSE_CD = SCA.COURSE_CD
AND PRA.CRV_VERSION_NUMBER = SCA.VERSION_NUMBER
AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.S_RELATION_TYPE
, PRA.SCA_PERSON_ID
, PRA.SCA_COURSE_CD
, PRA.CRV_COURSE_CD
, PRA.CRV_VERSION_NUMBER
, PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT
, PRA.COURSE_TYPE) = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM IGS_PR_RU_APPL PRA1
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV1
WHERE IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA1.PROGRESSION_RULE_CAT
, PRA1.SEQUENCE_NUMBER
, PRCTCV1.PRG_CAL_TYPE
, PRCTCV1.PRG_CI_SEQUENCE_NUMBER
, PRCTCV1.START_EFFECTIVE_PERIOD
, PRCTCV1.NUM_OF_APPLICATIONS
, PRA1.S_RELATION_TYPE
, PRA1.SCA_PERSON_ID
, PRA1.SCA_COURSE_CD
, PRA1.CRV_COURSE_CD
, PRA1.CRV_VERSION_NUMBER
, PRA1.OU_ORG_UNIT_CD
, PRA1.OU_START_DT
, PRA1.COURSE_TYPE) = 'Y'
AND PRA1.S_RELATION_TYPE = 'SCA'
AND PRA1.LOGICAL_DELETE_DT IS NULL
AND PRA1.SCA_PERSON_ID = SCA.PERSON_ID
AND PRA1.SCA_COURSE_CD = SCA.COURSE_CD
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV1.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRCTCV1.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER
AND PRCTCV1.PRG_CAL_TYPE = PRCTCV.PRG_CAL_TYPE
AND PRCTCV1.PRG_CI_SEQUENCE_NUMBER = PRCTCV.PRG_CI_SEQUENCE_NUMBER )
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND SCA.COURSE_ATTEMPT_STATUS NOT IN ('UNCONFIRM'
, 'COMPLETED')
AND CRV.COURSE_CD = SCA.COURSE_CD
AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA.LOGICAL_DELETE_DT IS NULL
AND PRCTCV.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER
AND PRCTCV.PRG_CAL_TYPE = CI.CAL_TYPE
AND PRCTCV.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER UNION ALL SELECT SCA.PERSON_ID
, SCA.COURSE_CD
, PRCTCV.PRG_CAL_TYPE
, PRCTCV.PRG_CI_SEQUENCE_NUMBER
, SUBSTR(IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD( CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) || '/' || CI.ALTERNATE_CODE
, 1
, 20)
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, PRA.RUL_SEQUENCE_NUMBER
, PRA.S_RELATION_TYPE
, DECODE(PRA.S_RELATION_TYPE
, 'OU'
, 'ORG UNIT'
, 'CTY'
, 'CRS TYPE'
, 'CRV'
, 'COURSE'
, 'SCA'
, 'STUDENT')
, PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD
, SUBSTR(PRA.PROGRESSION_RULE_CD || PRA.REFERENCE_CD
, 1
, 10)
, PRA.ATTENDANCE_TYPE
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.CREATED_BY
, PRA.CREATION_DATE
, PRA.LAST_UPDATED_BY
, PRA.LAST_UPDATE_DATE
, PRA.LAST_UPDATE_LOGIN
FROM IGS_CA_STAT CS
, IGS_PR_PRG_RU_CA_TYPE_CI_V PRCTCV
, IGS_CA_INST CI
, IGS_PR_RU_APPL PRA
, IGS_PS_VER CRV
, IGS_EN_STDNT_PS_ATT SCA
WHERE PRA.S_RELATION_TYPE = 'SCA'
AND PRA.SCA_PERSON_ID = SCA.PERSON_ID
AND PRA.SCA_COURSE_CD = SCA.COURSE_CD
AND IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL( SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, CRV.COURSE_TYPE
, PRA.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, PRCTCV.START_EFFECTIVE_PERIOD
, PRCTCV.NUM_OF_APPLICATIONS
, PRA.S_RELATION_TYPE
, PRA.SCA_PERSON_ID
, PRA.SCA_COURSE_CD
, PRA.CRV_COURSE_CD
, PRA.CRV_VERSION_NUMBER
, PRA.OU_ORG_UNIT_CD
, PRA.OU_START_DT
, PRA.COURSE_TYPE) = 'Y'
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND SCA.COURSE_ATTEMPT_STATUS NOT IN ('UNCONFIRM'
, 'COMPLETED')
AND CRV.COURSE_CD = SCA.COURSE_CD
AND CRV.VERSION_NUMBER = SCA.VERSION_NUMBER
AND PRA.LOGICAL_DELETE_DT IS NULL
AND PRCTCV.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRCTCV.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER
AND PRCTCV.PRG_CAL_TYPE = CI.CAL_TYPE
AND PRCTCV.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER