VIEW: APPS.IGS_PR_RULE_OUT_V

Source

View Text - Preformatted

SELECT pro.progression_rule_cat, pra.sequence_number, 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, pra.s_relation_type, pro.pra_sequence_number, pro.POSITIVE_OUTCOME_IND, pro.CREATED_BY, pro.CREATION_DATE, pro.LAST_UPDATED_BY, pro.LAST_UPDATE_DATE, pro.LAST_UPDATE_LOGIN, pro.logical_delete_dt FROM IGS_PR_RU_APPL pra, IGS_PR_RU_OU_V pro WHERE ( pro.progression_rule_cat = pra.progression_rule_cat AND pro.pra_sequence_number = pra.sequence_number ) OR EXISTS ( SELECT 1 FROM igs_pr_ru_appl pra1 WHERE pra.progression_rule_cd IS NOT NULL AND pra.s_relation_type IN ('CRV', 'OU', 'CTY', 'SCA', 'PRO', 'SPO') AND NOT EXISTS ( SELECT 'x' FROM igs_pr_ru_ou pro1 WHERE pro1.progression_rule_cat = pra.progression_rule_cat AND pro1.pra_sequence_number = pra.sequence_number) AND pra1.s_relation_type = 'PRR' AND pra1.progression_rule_cat = pra.progression_rule_cat AND pra1.progression_rule_cd = pra.progression_rule_cd AND pro.progression_rule_cat = pra1.progression_rule_cat AND pro.pra_sequence_number = pra1.sequence_number UNION ALL SELECT 1 FROM igs_pr_ru_appl pra1 WHERE pra.s_relation_type IN ('PRR', 'CRV', 'OU', 'CTY', 'SCA', 'PRO', 'SPO') AND NOT EXISTS ( SELECT 'x' FROM igs_pr_ru_ou pro1 WHERE pro1.progression_rule_cat = pra.progression_rule_cat AND pro1.pra_sequence_number = pra.sequence_number) AND NOT EXISTS ( SELECT 'x' FROM igs_pr_ru_appl pra2, igs_pr_ru_ou pro2 WHERE pra2.s_relation_type = 'PRR' AND pra2.progression_rule_cat = pra.progression_rule_cat AND pra2.progression_rule_cd = pra.progression_rule_cd AND pro2.progression_rule_cat = pra2.progression_rule_cat AND pro2.pra_sequence_number = pra2.sequence_number) AND pra1.s_relation_type = 'PRGC' AND pra1.progression_rule_cat = pra.progression_rule_cat AND pro.progression_rule_cat = pra1.progression_rule_cat AND pro.pra_sequence_number = pra1.sequence_number)

View Text - HTML Formatted

SELECT PRO.PROGRESSION_RULE_CAT
, PRA.SEQUENCE_NUMBER
, 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
, PRA.S_RELATION_TYPE
, PRO.PRA_SEQUENCE_NUMBER
, PRO.POSITIVE_OUTCOME_IND
, PRO.CREATED_BY
, PRO.CREATION_DATE
, PRO.LAST_UPDATED_BY
, PRO.LAST_UPDATE_DATE
, PRO.LAST_UPDATE_LOGIN
, PRO.LOGICAL_DELETE_DT
FROM IGS_PR_RU_APPL PRA
, IGS_PR_RU_OU_V PRO
WHERE ( PRO.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRO.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER ) OR EXISTS ( SELECT 1
FROM IGS_PR_RU_APPL PRA1
WHERE PRA.PROGRESSION_RULE_CD IS NOT NULL
AND PRA.S_RELATION_TYPE IN ('CRV'
, 'OU'
, 'CTY'
, 'SCA'
, 'PRO'
, 'SPO')
AND NOT EXISTS ( SELECT 'X'
FROM IGS_PR_RU_OU PRO1
WHERE PRO1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRO1.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER)
AND PRA1.S_RELATION_TYPE = 'PRR'
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRA1.PROGRESSION_RULE_CD = PRA.PROGRESSION_RULE_CD
AND PRO.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRO.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER UNION ALL SELECT 1
FROM IGS_PR_RU_APPL PRA1
WHERE PRA.S_RELATION_TYPE IN ('PRR'
, 'CRV'
, 'OU'
, 'CTY'
, 'SCA'
, 'PRO'
, 'SPO')
AND NOT EXISTS ( SELECT 'X'
FROM IGS_PR_RU_OU PRO1
WHERE PRO1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRO1.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER)
AND NOT EXISTS ( SELECT 'X'
FROM IGS_PR_RU_APPL PRA2
, IGS_PR_RU_OU PRO2
WHERE PRA2.S_RELATION_TYPE = 'PRR'
AND PRA2.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRA2.PROGRESSION_RULE_CD = PRA.PROGRESSION_RULE_CD
AND PRO2.PROGRESSION_RULE_CAT = PRA2.PROGRESSION_RULE_CAT
AND PRO2.PRA_SEQUENCE_NUMBER = PRA2.SEQUENCE_NUMBER)
AND PRA1.S_RELATION_TYPE = 'PRGC'
AND PRA1.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT
AND PRO.PROGRESSION_RULE_CAT = PRA1.PROGRESSION_RULE_CAT
AND PRO.PRA_SEQUENCE_NUMBER = PRA1.SEQUENCE_NUMBER)