VIEW: APPS.IGS_AD_INTERVIEW_LETTERS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'