View: IGS_PR_STDNT_PR_OU_V

View Text

SELECT SPO.ROW_ID
, SPO.PERSON_ID
, PE.PARTY_NUMBER
, SPO.COURSE_CD
, SCA.VERSION_NUMBER
, SPO.COURSE_CD || '/' || SCA.VERSION_NUMBER
, SPO.SEQUENCE_NUMBER
, SPO.PRG_CAL_TYPE
, SPO.PRG_CI_SEQUENCE_NUMBER
, SPO.RULE_CHECK_DT
, SPO.PROGRESSION_RULE_CAT
, SPO.PRA_SEQUENCE_NUMBER
, SPO.PRO_SEQUENCE_NUMBER
, SPO.PRO_PRA_SEQUENCE_NUMBER
, SPO.PROGRESSION_OUTCOME_TYPE
, DECODE(SPO.DURATION
, NULL
, NULL
, SPO.DURATION || '/' || SPO.DURATION_TYPE)
, SPO.DECISION_STATUS
, NVL(PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD)
, PE.PARTY_NAME
, PRO.RANK
, OU.POSITIVE_OUTCOME_IND
, CRV.COURSE_TYPE
, SUSA.UNIT_SET_CD
, SUSA.US_VERSION_NUMBER
, SPO.CREATED_BY
, SPO.CREATION_DATE
, SPO.LAST_UPDATED_BY
, SPO.LAST_UPDATE_DATE
, SPO.LAST_UPDATE_LOGIN
FROM HZ_PARTIES PE
, IGS_PR_RU_APPL PRA
, IGS_PR_RU_OU PRO
, IGS_EN_STDNT_PS_ATT_SV SCA
, IGS_PR_STDNT_PR_OU SPO
, IGS_PS_VER CRV
, IGS_PR_OU_TYPE OU
, IGS_AS_SU_SETATMPT SUSA
WHERE SPO.PERSON_ID = SCA.PERSON_ID
AND SPO.COURSE_CD = SCA.COURSE_CD
AND SPO.PERSON_ID = PE.PARTY_ID
AND SCA.COURSE_CD = CRV.COURSE_CD
AND SCA.VERSION_NUMBER = CRV.VERSION_NUMBER
AND SPO.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT (+)
AND SPO.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER (+)
AND SPO.PROGRESSION_RULE_CAT = PRO.PROGRESSION_RULE_CAT (+)
AND NVL(SPO.PRO_PRA_SEQUENCE_NUMBER
, SPO.PRA_SEQUENCE_NUMBER) = PRO.PRA_SEQUENCE_NUMBER (+)
AND SPO.PRO_SEQUENCE_NUMBER = PRO.SEQUENCE_NUMBER (+)
AND SPO.PROGRESSION_OUTCOME_TYPE = OU.PROGRESSION_OUTCOME_TYPE
AND SUSA.PERSON_ID(+) = SCA.PERSON_ID
AND SUSA.COURSE_CD(+) = SCA.COURSE_CD
AND SUSA.STUDENT_CONFIRMED_IND(+) = 'Y'
AND (NVL(SUSA.END_DT
, SYSDATE) >=SYSDATE OR NVL(SUSA.RQRMNTS_COMPLETE_IND
, 'N') = 'N') UNION SELECT SPO.ROW_ID
, SPO.PERSON_ID
, PE.PARTY_NUMBER
, SPO.COURSE_CD
, SCA.VERSION_NUMBER
, SPO.COURSE_CD || '/' || SCA.VERSION_NUMBER
, SPO.SEQUENCE_NUMBER
, SPO.PRG_CAL_TYPE
, SPO.PRG_CI_SEQUENCE_NUMBER
, SPO.RULE_CHECK_DT
, SPO.PROGRESSION_RULE_CAT
, SPO.PRA_SEQUENCE_NUMBER
, SPO.PRO_SEQUENCE_NUMBER
, SPO.PRO_PRA_SEQUENCE_NUMBER
, SPO.PROGRESSION_OUTCOME_TYPE
, DECODE(SPO.DURATION
, NULL
, NULL
, SPO.DURATION || '/' ||SPO.DURATION_TYPE)
, SPO.DECISION_STATUS
, NVL(PRA.PROGRESSION_RULE_CD
, PRA.REFERENCE_CD)
, PE.PARTY_NAME
, PRO.RANK
, OU.POSITIVE_OUTCOME_IND
, CRV.COURSE_TYPE
, NULL
, TO_NUMBER(NULL)
, SPO.CREATED_BY
, SPO.CREATION_DATE
, SPO.LAST_UPDATED_BY
, SPO.LAST_UPDATE_DATE
, SPO.LAST_UPDATE_LOGIN
FROM HZ_PARTIES PE
, IGS_PR_RU_APPL PRA
, IGS_PR_RU_OU PRO
, IGS_EN_STDNT_PS_ATT_SV SCA
, IGS_PR_STDNT_PR_OU SPO
, IGS_PS_VER CRV
, IGS_PR_OU_TYPE OU
WHERE SPO.PERSON_ID = SCA.PERSON_ID
AND SPO.COURSE_CD = SCA.COURSE_CD
AND SPO.PERSON_ID = PE.PARTY_ID
AND SCA.COURSE_CD = CRV.COURSE_CD
AND SCA.VERSION_NUMBER = CRV.VERSION_NUMBER
AND SPO.PROGRESSION_RULE_CAT = PRA.PROGRESSION_RULE_CAT (+)
AND SPO.PRA_SEQUENCE_NUMBER = PRA.SEQUENCE_NUMBER (+)
AND SPO.PROGRESSION_RULE_CAT = PRO.PROGRESSION_RULE_CAT (+)
AND NVL(SPO.PRO_PRA_SEQUENCE_NUMBER
, SPO.PRA_SEQUENCE_NUMBER) =PRO.PRA_SEQUENCE_NUMBER (+)
AND SPO.PRO_SEQUENCE_NUMBER = PRO.SEQUENCE_NUMBER (+)
AND SPO.PROGRESSION_OUTCOME_TYPE = OU.PROGRESSION_OUTCOME_TYPE

Columns