View: IGS_AD_INTERVIEW_LETTERS_V

View Text

SELECT HZ.PARTY_ID
, HZ.PARTY_NAME
, HZ.PARTY_NUMBER
, HZ.ADDRESS1
, HZ.ADDRESS2
, HZ.ADDRESS3
, HZ.ADDRESS4
, HZ.CATEGORY_CODE
, HZ.CITY
, HZ.COUNTRY
, HZ.COUNTY
, HZ.EMAIL_ADDRESS
, HZ.KNOWN_AS
, HZ.PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME
, HZ.PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT
, HZ.PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE
, HZ.POSTAL_CODE
, HZ.PROVINCE
, HZ.SALUTATION
, HZ.STATE
, HZ.STATUS
, ACAIV.PREFERENCE_NUMBER
, PS.COURSE_CD
, PS.TITLE
, PS.RESPONSIBLE_ORG_UNIT_CD
, OU.PARTY_NAME DESCRIPTION
, PS.COURSE_TYPE
, ACAIV.ADM_OUTCOME_STATUS
, ACAIV.EXPECTED_COMPLETION_YR
, ACAIV.EXPECTED_COMPLETION_PERD
, AAV.ACAD_ALTERNATE_CODE
, AAV.ACAD_CAL_TYPE
, AAV.ACAD_CI_SEQUENCE_NUMBER
, AAV.ADM_CAL_TYPE
, AAV.ADM_CI_SEQUENCE_NUMBER
, AAV.APPL_DT
, AAV.COMMENCEMENT_PERIOD
, AAV.ADMISSION_PROCESS_CAT
, OUS.S_ADM_OUTCOME_STATUS
, ACAIV.ADM_CNDTNL_OFFER_STATUS
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR')
, PERSID.GROUP_ID
, ACAIV.ADMISSION_APPL_NUMBER
, ACAIV.NOMINATED_COURSE_CD
, ACAIV.SEQUENCE_NUMBER
, AA.APPLICATION_ID
, PVLVLS.PANEL_LEVEL_CODE
, INTPNL.PANEL_CODE
, INTPNL.PANEL_DESC
, PNMEM.ROLE_TYPE_CODE
, PNMEM.ROLE_TYPE_DESC
, PNMEM.MEMBER_PERSON_ID
, PNMEM.MEMBER_PERSON_NUMBER
, PNMEM.MEMBER_FULL_NAME
, NVL(PNMEM.INTERVIEW_DATE
, INTPNL.INTERVIEW_DATE)
, TO_CHAR(NVL(PNMEM.INTERVIEW_TIME
, INTPNL.INTERVIEW_TIME)
, 'HH-MI') INTERVIEW_TIME
, NVL(PNMEM.LOCATION_CD
, INTPNL.LOCATION_CD)
, NVL(PNMEM.LOCATION_DESC
, INTPNL.LOCATION_DESC)
, NVL(PNMEM.ROOM_ID
, INTPNL.ROOM_ID)
, NVL(PNMEM.ROOM_CD
, INTPNL.ROOM_CD)
, NVL(PNMEM.ROOM_DESC
, INTPNL.ROOM_DESC)
, PNMEM.MEMBER_DECISION_CODE
, PNMEM.MEMBER_DECISION_CODE_DESC
, PNMEM.MEMBER_DECISION_TYPE
, PNMEM.MEMBER_DECISION_DATE
, INTPNL.FINAL_DECISION_CODE
, INTPNL.FINAL_DECISION_CODE_DESC
, INTPNL.FINAL_DECISION_TYPE
, INTPNL.FINAL_DECISION_DATE
, ACAIV.ATTENDANCE_TYPE
, ACAIV.ATTENDANCE_MODE
FROM HZ_PARTIES HZ
, IGS_PE_HZ_PARTIES PE
, IGS_AD_APPL_ALL AA
, IGS_AD_APPL_STAT AST
, IGS_AD_PS_APPL_INST_ALL ACAIV
, IGS_AD_APPL_ADMAPPL_V AAV
, IGS_PS_VER_ALL PS
, HZ_PARTIES OU
, IGS_PE_HZ_PARTIES PE1
, IGS_AD_OU_STAT OUS
, IGS_PE_PRSID_GRP_MEM_ALL PERSID
, IGS_AD_SCRN_INT_PANEL_DTLS_V INTPNL
, IGS_AD_PNMEMBR_DTLS_V PNMEM
, IGS_AD_INTVW_PNLS PVLVLS
, IGS_AD_CODE_CLASSES CC1
, IGS_AD_CODE_CLASSES CC2
WHERE HZ.PARTY_ID = PE.PARTY_ID
AND PERSID.PERSON_ID(+) =PE.PARTY_ID
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'
AND AA.PERSON_ID = PE.PARTY_ID
AND AA.ADM_APPL_STATUS = AST.ADM_APPL_STATUS
AND (AST.S_ADM_APPL_STATUS = 'RECEIVED' OR AST.S_ADM_APPL_STATUS = 'COMPLETED')
AND ACAIV.ADM_OUTCOME_STATUS = OUS.ADM_OUTCOME_STATUS
AND ACAIV.PERSON_ID = AA.PERSON_ID
AND ACAIV.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND AAV.PERSON_ID = ACAIV.PERSON_ID
AND AAV.ADMISSION_APPL_NUMBER = ACAIV.ADMISSION_APPL_NUMBER
AND ACAIV.COURSE_CD = PS.COURSE_CD
AND ACAIV.CRV_VERSION_NUMBER = PS.VERSION_NUMBER
AND PS.RESPONSIBLE_ORG_UNIT_CD = PE1.OSS_ORG_UNIT_CD
AND PE1.PARTY_ID = OU.PARTY_ID
AND NOT EXISTS (SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP APCS
WHERE APCS.ADMISSION_CAT = AAV.ADMISSION_CAT
AND APCS.S_ADMISSION_PROCESS_TYPE = AAV.S_ADMISSION_PROCESS_TYPE
AND APCS.S_ADMISSION_STEP_TYPE = 'AUTO-OFFER'
AND APCS.STEP_GROUP_TYPE = 'OUTCOME'
AND APCS.STEP_GROUP_TYPE <> 'TRACK' )
AND INTPNL.PERSON_ID = ACAIV.PERSON_ID
AND INTPNL.ADMISSION_APPL_NUMBER =ACAIV.ADMISSION_APPL_NUMBER
AND INTPNL.NOMINATED_COURSE_CD =ACAIV.NOMINATED_COURSE_CD
AND INTPNL.SEQUENCE_NUMBER =ACAIV.SEQUENCE_NUMBER
AND INTPNL.PANEL_TYPE_CODE ='INTERVIEW'
AND INTPNL.PANEL_DTLS_ID = PNMEM.PANEL_DTLS_ID
AND INTPNL.PANEL_CODE = PVLVLS.PANEL_CODE
AND OUS.S_ADM_OUTCOME_STATUS = 'PENDING'
AND CC1.NAME = PNMEM.MEMBER_DECISION_CODE
AND CC1.CLASS = 'INTERVIEW'
AND CC1.CLASS_TYPE_CODE = 'ADM_CODE_CLASSES'
AND CC1.SYSTEM_STATUS ='PENDING'
AND CC2.NAME = INTPNL.FINAL_DECISION_CODE
AND CC2.CLASS = 'FINAL_INTERVIEW'
AND CC2.CLASS_TYPE_CODE = 'ADM_CODE_CLASSES'
AND CC2.SYSTEM_STATUS ='PENDING'

Columns