View: IGS_PR_SCA_PRG_RULE_APPL_V

View Text

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

Columns