eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.IGS_AD_PS_APPL_INST_APLINST_V

Source

View Text - Preformatted


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(+)

View Text - HTML Formatted

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(+)