View: IGS_AD_PS_APPL_INST_APLINST_V

View Text

SELECT ACAI.ROW_ID ROW_ID
, ACAI.PERSON_ID
, P.PARTY_NUMBER PERSON_NUMBER
, ACAI.ADMISSION_APPL_NUMBER
, ACAI.NOMINATED_COURSE_CD
, ACAI.SEQUENCE_NUMBER
, AA.ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER
, NVL(ACAI.ADM_CAL_TYPE
, AA.ADM_CAL_TYPE)
, NVL(ACAI.ADM_CI_SEQUENCE_NUMBER
, AA.ADM_CI_SEQUENCE_NUMBER)
, ACAI.COURSE_CD
, ACAI.CRV_VERSION_NUMBER
, ACAI.LOCATION_CD
, ACAI.ATTENDANCE_MODE
, ACAI.ATTENDANCE_TYPE
, ACAI.UNIT_SET_CD
, DECODE(ACAI.UNIT_SET_CD
, NULL
, NULL
, (SELECT ESU.TITLE
FROM IGS_EN_UNIT_SET ESU
WHERE ACAI.UNIT_SET_CD = ESU.UNIT_SET_CD
AND ACAI.US_VERSION_NUMBER = ESU.VERSION_NUMBER )) UNIT_SET_TITLE
, ACAI.US_VERSION_NUMBER
, ACAI.PREFERENCE_NUMBER
, ACAI.ADM_DOC_STATUS
, ACAI.ADM_ENTRY_QUAL_STATUS
, ACAI.LATE_ADM_FEE_STATUS
, ACAI.ADM_OUTCOME_STATUS
, ACAI.ADM_OTCM_STATUS_AUTH_PERSON_ID
, P1.PARTY_NUMBER ADM_OTCM_STATUS_AUTH_PER_NUM
, ACAI.ADM_OUTCOME_STATUS_AUTH_DT
, ACAI.ADM_OUTCOME_STATUS_REASON
, ACAI.OFFER_DT
, ACAI.OFFER_RESPONSE_DT
, ACAI.PRPSD_COMMENCEMENT_DT
, ACAI.ADM_CNDTNL_OFFER_STATUS
, ACAI.CNDTNL_OFFER_CNDTN
, ACAI.CNDTNL_OFFER_SATISFIED_DT
, ACAI.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAI.ADM_OFFER_RESP_STATUS
, ACAI.ACTUAL_RESPONSE_DT
, ACAI.APPLICANT_ACPTNCE_CNDTN
, ACAI.ADM_OFFER_DFRMNT_STATUS
, ACAI.DEFERRED_ADM_CAL_TYPE
, ACAI.DEFERRED_ADM_CI_SEQUENCE_NUM
, DECODE(ACAI.DEFERRED_ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI.ALTERNATE_CODE
FROM IGS_CA_INST CI
WHERE CI.CAL_TYPE = ACAI.DEFERRED_ADM_CAL_TYPE
AND CI.SEQUENCE_NUMBER = ACAI.DEFERRED_ADM_CI_SEQUENCE_NUM ))
, DECODE(ACAI.DEF_ACAD_CAL_TYPE
, NULL
, NULL
, (SELECT CI2.ALTERNATE_CODE
FROM IGS_CA_INST CI2
WHERE CI2.CAL_TYPE = ACAI.DEF_ACAD_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = ACAI.DEF_ACAD_CI_SEQUENCE_NUM ))
, ACAI.DEFERRED_TRACKING_ID
, ACAI.ASS_RANK
, ACAI.SECONDARY_ASS_RANK
, ACAI.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ACAI.ASS_TRACKING_ID
, ACAI.FEE_CAT
, ACAI.HECS_PAYMENT_OPTION
, ACAI.EXPECTED_COMPLETION_YR
, ACAI.EXPECTED_COMPLETION_PERD
, ACAI.CORRESPONDENCE_CAT
, ACAI.ENROLMENT_CAT
, ACAI.FUNDING_SOURCE
, ACA.BASIS_FOR_ADMISSION_TYPE
, ACA.REQ_FOR_RECONSIDERATION_IND
, ACA.REQ_FOR_ADV_STANDING_IND
, ACA.ADMISSION_CD
, ACA.COURSE_RANK_SET
, ACA.COURSE_RANK_SCHEDULE
, ACA.TRANSFER_COURSE_CD
, RTRIM (CRV.TITLE)
, CRV.COURSE_STATUS
, ACAI.CREATED_BY
, ACAI.CREATION_DATE
, ACAI.LAST_UPDATED_BY
, ACAI.LAST_UPDATE_DATE
, ACAI.LAST_UPDATE_LOGIN
, ACAI.REQUEST_ID
, ACAI.PROGRAM_APPLICATION_ID
, ACAI.PROGRAM_ID
, ACAI.PROGRAM_UPDATE_DATE
, ACAI.PREDICTED_GPA
, ACAI.ACADEMIC_INDEX
, ACAI.APP_FILE_LOCATION
, ACAI.APP_SOURCE_ID
, DECODE (ACAI.APP_SOURCE_ID
, NULL
, NULL
, (SELECT CC1.NAME
FROM IGS_AD_CODE_CLASSES CC1
WHERE ACAI.APP_SOURCE_ID = CC1.CODE_ID
AND CC1.CLASS = 'SYS_APPL_SOURCE' ))
, DECODE (ACAI.APP_SOURCE_ID
, NULL
, NULL
, (SELECT CC1.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC1
WHERE ACAI.APP_SOURCE_ID = CC1.CODE_ID
AND CC1.CLASS = 'SYS_APPL_SOURCE' ))
, NULL
, NULL
, NULL
, NULL
, ACAI.WAITLIST_RANK
, ACAI.ATTENT_OTHER_INST_CD
, DECODE(ACAI.ATTENT_OTHER_INST_CD
, NULL
, NULL
, (SELECT I.NAME
FROM IGS_OR_INSTITUTION I
WHERE ACAI.ATTENT_OTHER_INST_CD = I.INSTITUTION_CD ))
, ACAI.EDU_GOAL_PRIOR_ENROLL_ID
, DECODE (ACAI.EDU_GOAL_PRIOR_ENROLL_ID
, NULL
, NULL
, (SELECT CC3.NAME
FROM IGS_AD_CODE_CLASSES CC3
WHERE ACAI.EDU_GOAL_PRIOR_ENROLL_ID = CC3.CODE_ID
AND CC3.CLASS = 'EDU_GOALS' ))
, DECODE (ACAI.EDU_GOAL_PRIOR_ENROLL_ID
, NULL
, NULL
, (SELECT CC3.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC3
WHERE ACAI.EDU_GOAL_PRIOR_ENROLL_ID = CC3.CODE_ID
AND CC3.CLASS = 'EDU_GOALS' ))
, ACAI.DECISION_DATE
, ACAI.DECISION_MAKE_ID
, IGS_GE_GEN_001.ADM_GET_NAME (ACAI.DECISION_MAKE_ID) DECISION_MAKER_NAME
, ACAI.DECISION_REASON_ID
, DECODE (ACAI.DECISION_REASON_ID
, NULL
, NULL
, (SELECT CC5.NAME
FROM IGS_AD_CODE_CLASSES CC5
WHERE ACAI.DECISION_REASON_ID = CC5.CODE_ID
AND CC5.CLASS = 'DECISION_REASON' ))
, DECODE (ACAI.DECISION_REASON_ID
, NULL
, NULL
, (SELECT CC5.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC5
WHERE ACAI.DECISION_REASON_ID = CC5.CODE_ID
AND CC5.CLASS = 'DECISION_REASON' ))
, ACAI.PENDING_REASON_ID
, DECODE (ACAI.PENDING_REASON_ID
, NULL
, NULL
, (SELECT CC4.NAME
FROM IGS_AD_CODE_CLASSES CC4
WHERE ACAI.PENDING_REASON_ID = CC4.CODE_ID
AND CC4.CLASS = 'PENDING_REASON' ))
, DECODE (ACAI.PENDING_REASON_ID
, NULL
, NULL
, (SELECT CC4.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC4
WHERE ACAI.PENDING_REASON_ID = CC4.CODE_ID
AND CC4.CLASS = 'PENDING_REASON' ))
, ACAI.DECISION_NOTES
, ACAI.DEFICIENCY_IN_PREP
, ACAI.SPL_CONSIDER_COMMENTS
, ACAI.APPLY_FOR_FINAID
, ACAI.FINAID_APPLY_DATE
, ACAI.ATTRIBUTE_CATEGORY
, ACAI.ATTRIBUTE1
, ACAI.ATTRIBUTE2
, ACAI.ATTRIBUTE3
, ACAI.ATTRIBUTE4
, ACAI.ATTRIBUTE5
, ACAI.ATTRIBUTE6
, ACAI.ATTRIBUTE7
, ACAI.ATTRIBUTE8
, ACAI.ATTRIBUTE9
, ACAI.ATTRIBUTE10
, ACAI.ATTRIBUTE11
, ACAI.ATTRIBUTE12
, ACAI.ATTRIBUTE13
, ACAI.ATTRIBUTE14
, ACAI.ATTRIBUTE15
, ACAI.ATTRIBUTE16
, ACAI.ATTRIBUTE17
, ACAI.ATTRIBUTE18
, ACAI.ATTRIBUTE19
, ACAI.ATTRIBUTE20
, ACAI.SS_APPLICATION_ID
, ACAI.AUTHORIZED_DT
, ACAI.AUTHORIZING_PERS_ID
, DECODE ( ACAI.AUTHORIZING_PERS_ID
, NULL
, NULL
, (SELECT P2.PARTY_NUMBER
FROM HZ_PARTIES P2
WHERE ACAI.AUTHORIZING_PERS_ID = P2.PARTY_ID )) AUTHORIZING_PERS_NUM
, ACAI.ENTRY_STATUS
, DECODE(ACAI.ENTRY_STATUS
, NULL
, NULL
, (SELECT CC6.NAME
FROM IGS_AD_CODE_CLASSES CC6
WHERE ACAI.ENTRY_STATUS = CC6.CODE_ID
AND CC6.CLASS = 'STATUS' ))
, DECODE (ACAI.ENTRY_STATUS
, NULL
, NULL
, (SELECT CC6.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC6
WHERE ACAI.ENTRY_STATUS = CC6.CODE_ID
AND CC6.CLASS = 'STATUS' ))
, ACAI.ENTRY_LEVEL
, DECODE(ACAI.ENTRY_LEVEL
, NULL
, NULL
, (SELECT CC7.NAME
FROM IGS_AD_CODE_CLASSES CC7
WHERE ACAI.ENTRY_LEVEL = CC7.CODE_ID
AND CC7.CLASS = 'LEVEL' ))
, DECODE (ACAI.ENTRY_LEVEL
, NULL
, NULL
, (SELECT CC7.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC7
WHERE ACAI.ENTRY_LEVEL = CC7.CODE_ID
AND CC7.CLASS = 'LEVEL' ))
, ACAI.SCH_APL_TO_ID
, DECODE(ACAI.SCH_APL_TO_ID
, NULL
, NULL
, (SELECT ASAT.SCHOOL_APPLYING_TO
FROM IGS_AD_SCHL_APLY_TO ASAT
WHERE ACAI.SCH_APL_TO_ID = ASAT.SCH_APL_TO_ID ))
, DECODE(ACAI.SCH_APL_TO_ID
, NULL
, NULL
, (SELECT ASAT.DESCRIPTION
FROM IGS_AD_SCHL_APLY_TO ASAT
WHERE ACAI.SCH_APL_TO_ID = ASAT.SCH_APL_TO_ID ))
, ACAI.IDX_CALC_DATE
, ACAI.WAITLIST_STATUS
, DECODE ( NVL (WAITLIST_STATUS
, '*')
, '*'
, ' '
, (SELECT MEANING
FROM IGS_LOOKUP_VALUES LK
WHERE LK.LOOKUP_TYPE = 'ADM_WAITLIST_STATUS'
AND LK.LOOKUP_CODE = ACAI.WAITLIST_STATUS ) )
, AA.ADM_FEE_STATUS
, NVL ( P1.KNOWN_AS
, SUBSTR ( P1.PERSON_FIRST_NAME
, 1
, DECODE ( INSTR (P1.PERSON_FIRST_NAME
, ' ')
, 0
, LENGTH (P1.PERSON_FIRST_NAME)
, ( INSTR (P1.PERSON_FIRST_NAME
, ' ') - 1 ) ) ) ) || ' ' || P1.PERSON_LAST_NAME ADM_OTCM_STATUS_AUTH_PER_NAME
, DECODE(ACAI.DECISION_MAKE_ID
, NULL
, NULL
, (SELECT P3.PARTY_NUMBER
FROM HZ_PARTIES P3
WHERE ACAI.DECISION_MAKE_ID = P3.PARTY_ID )) DECISION_MAKER_NUMBER
, ACAI.ATTRIBUTE21
, ACAI.ATTRIBUTE22
, ACAI.ATTRIBUTE23
, ACAI.ATTRIBUTE24
, ACAI.ATTRIBUTE25
, ACAI.ATTRIBUTE26
, ACAI.ATTRIBUTE27
, ACAI.ATTRIBUTE28
, ACAI.ATTRIBUTE29
, ACAI.ATTRIBUTE30
, ACAI.ATTRIBUTE31
, ACAI.ATTRIBUTE32
, ACAI.ATTRIBUTE33
, ACAI.ATTRIBUTE34
, ACAI.ATTRIBUTE35
, ACAI.ATTRIBUTE36
, ACAI.ATTRIBUTE37
, ACAI.ATTRIBUTE38
, ACAI.ATTRIBUTE39
, ACAI.ATTRIBUTE40
, DECODE (ACA.ADMISSION_CD
, NULL
, NULL
, (SELECT ACO.DESCRIPTION
FROM IGS_AD_CD ACO
WHERE ACA.ADMISSION_CD = ACO.ADMISSION_CD )) ADMISSION_CD_DESC
, DECODE(ACA.BASIS_FOR_ADMISSION_TYPE
, NULL
, NULL
, (SELECT ABFA.DESCRIPTION
FROM IGS_AD_BASIS_FOR_AD ABFA
WHERE ABFA.BASIS_FOR_ADMISSION_TYPE = ACA.BASIS_FOR_ADMISSION_TYPE )) BASIS_FOR_ADM_TYPE_DESC
, DECODE(ACAI.FUTURE_ACAD_CAL_TYPE
, NULL
, NULL
, (SELECT CI_FUT_ACAD.ALTERNATE_CODE
FROM IGS_CA_INST CI_FUT_ACAD
WHERE CI_FUT_ACAD.CAL_TYPE = ACAI.FUTURE_ACAD_CAL_TYPE
AND CI_FUT_ACAD.SEQUENCE_NUMBER = ACAI.FUTURE_ACAD_CI_SEQUENCE_NUMBER )) FUTURE_ACAD_ALTERNATE_CODE
, ACAI.FUTURE_ACAD_CAL_TYPE
, ACAI.FUTURE_ACAD_CI_SEQUENCE_NUMBER
, DECODE(ACAI.FUTURE_ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI_FUT_ADM.ALTERNATE_CODE
FROM IGS_CA_INST CI_FUT_ADM
WHERE CI_FUT_ADM.CAL_TYPE = ACAI.FUTURE_ADM_CAL_TYPE
AND CI_FUT_ADM.SEQUENCE_NUMBER = ACAI.FUTURE_ADM_CI_SEQUENCE_NUMBER )) FUTURE_ADM_ALTERNATE_CODE
, ACAI.FUTURE_ADM_CAL_TYPE
, ACAI.FUTURE_ADM_CI_SEQUENCE_NUMBER
, ACAI.PREVIOUS_TERM_ADM_APPL_NUMBER
, ACAI.PREVIOUS_TERM_SEQUENCE_NUMBER
, ACAI.FUTURE_TERM_ADM_APPL_NUMBER
, ACAI.FUTURE_TERM_SEQUENCE_NUMBER
, DECODE(ACAI.ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI_ADM_CAL.ALTERNATE_CODE
FROM IGS_CA_INST CI_ADM_CAL
WHERE CI_ADM_CAL.CAL_TYPE = ACAI.ADM_CAL_TYPE
AND CI_ADM_CAL.SEQUENCE_NUMBER = ACAI.ADM_CI_SEQUENCE_NUMBER )) ADM_ALTERNATE_CODE
, DEF_ACAD_CAL_TYPE
, DEF_ACAD_CI_SEQUENCE_NUM
, DEF_PREV_TERM_ADM_APPL_NUM
, DEF_PREV_APPL_SEQUENCE_NUM
, DEF_TERM_ADM_APPL_NUM
, DEF_APPL_SEQUENCE_NUM
, AA.APPLICATION_ID APPLICATION_ID
, AA.ALT_APPL_ID ALT_APPL_ID
, ACAI.APPL_INST_STATUS
, ACAI.AIS_REASON
, ACAI.DECLINE_OFR_REASON
FROM IGS_AD_PS_APPL_INST_SV ACAI
, IGS_AD_APPL AA
, IGS_AD_PS_APPL ACA
, IGS_PS_VER CRV
, HZ_PARTIES P
, HZ_PARTIES P1
WHERE AA.PERSON_ID = ACAI.PERSON_ID
AND ACAI.PERSON_ID = P.PARTY_ID
AND AA.ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER
AND ACA.PERSON_ID = ACAI.PERSON_ID
AND ACA.ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER
AND ACA.NOMINATED_COURSE_CD = ACAI.NOMINATED_COURSE_CD
AND CRV.COURSE_CD = ACAI.COURSE_CD
AND CRV.VERSION_NUMBER = ACAI.CRV_VERSION_NUMBER
AND ACAI.ADM_OTCM_STATUS_AUTH_PERSON_ID = P1.PARTY_ID(+)

Columns