VIEW: APPS.IGSFV_AD_ADM_PS_APPL_INST

Source

View Text - Preformatted

SELECT apd.PERSON_ID, p1.PARTY_NUMBER, pp1.PERSON_NAME, apd.ADMISSION_APPL_NUMBER, apd.NOMINATED_COURSE_CD, psv.TITLE, apd.SEQUENCE_NUMBER, apd.PREDICTED_GPA, apd.ACADEMIC_INDEX, ci.ALTERNATE_CODE, apd.ADM_CAL_TYPE, apd.ADM_CI_SEQUENCE_NUMBER, apd.APP_FILE_LOCATION, apd.APP_SOURCE_ID, cc1.NAME, cc1.DESCRIPTION, apd.COURSE_CD, psv.TITLE, apd.CRV_VERSION_NUMBER, apd.WAITLIST_RANK, apd.LOCATION_CD, lc.DESCRIPTION, apd.ATTENT_OTHER_INST_CD, p4.PARTY_NAME, apd.ATTENDANCE_MODE, eam.DESCRIPTION, apd.EDU_GOAL_PRIOR_ENROLL_ID, cc6.NAME, cc6.DESCRIPTION, apd.ATTENDANCE_TYPE, eat.DESCRIPTION, apd.DECISION_MAKE_ID, p2.PARTY_NUMBER, pp2.person_name, apd.UNIT_SET_CD, esu.TITLE, apd.US_VERSION_NUMBER, apd.DECISION_DATE, '_DF:IGS:IGS_AD_APPL_INST_FLEX:apd', apd.DECISION_REASON_ID, cc2.NAME, cc2.DESCRIPTION, apd.DECISION_NOTES , apd.PENDING_REASON_ID, cc3.NAME, cc3.DESCRIPTION, apd.PREFERENCE_NUMBER, apd.ADM_DOC_STATUS, dst.DESCRIPTION, apd.ADM_ENTRY_QUAL_STATUS, eqst.DESCRIPTION, apd.DEFICIENCY_IN_PREP, apd.LATE_ADM_FEE_STATUS, fst.DESCRIPTION, apd.SPL_CONSIDER_COMMENTS, '_LA:apd.APPLY_FOR_FINAID:IGS_LOOKUP_VALUES:YES_NO:MEANING', apd.FINAID_APPLY_DATE, apd.ADM_OUTCOME_STATUS, ost.DESCRIPTION, apd.ADM_OTCM_STATUS_AUTH_PERSON_ID, igs_ge_gen_001.adm_get_name (apd.ADM_OTCM_STATUS_AUTH_PERSON_ID), apd.ADM_OUTCOME_STATUS_AUTH_DT, apd.ADM_OUTCOME_STATUS_REASON, apd.OFFER_DT, apd.OFFER_RESPONSE_DT, apd.PRPSD_COMMENCEMENT_DT, apd.ADM_CNDTNL_OFFER_STATUS, cdst.DESCRIPTION, apd.CNDTNL_OFFER_CNDTN, apd.CNDTNL_OFFER_SATISFIED_DT, '_LA:apd.CNDTNL_OFFER_MUST_BE_STSFD_IND:IGS_LOOKUP_VALUES:YES_NO:MEANING', apd.ADM_OFFER_RESP_STATUS, orst.DESCRIPTION, apd.ACTUAL_RESPONSE_DT, apd.ADM_OFFER_DFRMNT_STATUS, dfst.DESCRIPTION, ci1.ALTERNATE_CODE, apd.DEFERRED_ADM_CAL_TYPE, apd.DEFERRED_ADM_CI_SEQUENCE_NUM, apd.DEFERRED_TRACKING_ID, apd.ASS_RANK, apd.SECONDARY_ASS_RANK, apd.INTRNTNL_ACCEPTANCE_ADVICE_NUM, apd.ASS_TRACKING_ID, apd.FEE_CAT, fct.DESCRIPTION, apd.HECS_PAYMENT_OPTION, hpop.DESCRIPTION, apd.EXPECTED_COMPLETION_YR, apd.EXPECTED_COMPLETION_PERD, apd.CORRESPONDENCE_CAT, cct.DESCRIPTION, apd.ENROLMENT_CAT, ect.DESCRIPTION, apd.FUNDING_SOURCE, ffs.DESCRIPTION, apd.APPLICANT_ACPTNCE_CNDTN, apd.SS_APPLICATION_ID, apd.SS_PWD, apd.ENTRY_STATUS, cc4.NAME, cc4.DESCRIPTION, apd.ENTRY_LEVEL, cc5.NAME, cc5.DESCRIPTION, apd.SCH_APL_TO_ID, scht.SCHOOL_APPLYING_TO, scht.DESCRIPTION, apd.AUTHORIZED_DT, apd.AUTHORIZING_PERS_ID, p3.PARTY_NUMBER, pp3.PERSON_NAME, apd.IDX_CALC_DATE, '_LA:apd.WAITLIST_STATUS:IGS_LOOKUP_VALUES:ADM_WAITLIST_STATUS:MEANING', ci2.ALTERNATE_CODE, apd.FUTURE_ACAD_CAL_TYPE, apd.FUTURE_ACAD_CI_SEQUENCE_NUMBER, ci3.ALTERNATE_CODE, apd.FUTURE_ADM_CAL_TYPE, apd.FUTURE_ADM_CI_SEQUENCE_NUMBER, apd.PREVIOUS_TERM_ADM_APPL_NUMBER, apd.PREVIOUS_TERM_SEQUENCE_NUMBER, apd.FUTURE_TERM_ADM_APPL_NUMBER, apd.FUTURE_TERM_SEQUENCE_NUMBER, ci4.ALTERNATE_CODE, apd.DEF_ACAD_CAL_TYPE, apd.DEF_ACAD_CI_SEQUENCE_NUM, apd.DEF_PREV_TERM_ADM_APPL_NUM, apd.DEF_PREV_APPL_SEQUENCE_NUM, apd.DEF_TERM_ADM_APPL_NUM, apd.DEF_APPL_SEQUENCE_NUM, apd.CREATED_BY, apd.CREATION_DATE, apd.LAST_UPDATED_BY, apd.LAST_UPDATE_DATE, apd.APPL_INST_STATUS, apd.AIS_REASON AIS_REASON_CODE, '_LA:apd.AIS_REASON:IGS_LOOKUP_VALUES:IGS_AD_WITHDRAW_RSN:MEANING' "_LA:AIS_REASON", apd.DECLINE_OFR_REASON DECLINE_OFR_REASON_CODE, '_LA:apd.DECLINE_OFR_REASON:IGS_LOOKUP_VALUES:IGS_AD_DECL_OFR_REAS:MEANING' "_LA:DECLINE_OFR_REASON" FROM igs_ad_ps_appl_inst_all apd, HZ_PARTIES P1, HZ_PERSON_PROFILES PP1, igs_ad_code_classes cc1, igs_ad_code_classes cc2, igs_ad_code_classes cc3, igs_ad_code_classes cc4, igs_ad_code_classes cc5, igs_ad_code_classes cc6, IGS_PS_VER_ALL psv, igs_ad_location_all lc, igs_en_atd_mode_all eam, igs_en_atd_type_all eat, igs_en_unit_set_all esu, igs_ad_doc_stat dst, igs_ad_fee_stat fst, igs_ad_ou_stat ost, igs_ad_cndnl_ofrstat cdst, igs_ad_ofr_resp_stat orst, igs_ad_ent_qf_stat eqst, igs_ad_ofrdfrmt_stat dfst, igs_co_cat cct, igs_en_enrolment_cat ect, igs_fi_fund_src_all ffs, igs_fi_hecs_pay_optn hpop, igs_fi_fee_cat_all fct, HZ_PARTIES p4, igs_pe_hz_parties php, igs_ad_schl_aply_to scht, igs_ca_inst_all ci, igs_ca_inst_all ci1, igs_ca_inst_all ci2, igs_ca_inst_all ci3, igs_ca_inst_all ci4, HZ_PARTIES P2, HZ_PERSON_PROFILES PP2, HZ_PARTIES P3, HZ_PERSON_PROFILES PP3 WHERE apd.NOMINATED_COURSE_CD = psv.COURSE_CD AND apd.CRV_VERSION_NUMBER = psv.VERSION_NUMBER AND apd.PERSON_ID = p1.PARTY_ID AND P1.PARTY_ID = PP1.PARTY_ID AND PP1.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED' AND SYSDATE BETWEEN PP1.EFFECTIVE_START_DATE AND NVL(PP1.EFFECTIVE_END_DATE,SYSDATE) AND apd.APP_SOURCE_ID = cc1.CODE_ID(+) AND apd.DECISION_REASON_ID = cc2.code_id(+) AND apd.PENDING_REASON_ID = cc3.code_id(+) AND apd.LOCATION_CD = lc.LOCATION_CD(+) AND apd.ATTENDANCE_MODE = eam.ATTENDANCE_MODE(+) AND apd.ATTENDANCE_TYPE = eat.ATTENDANCE_TYPE(+) AND apd.UNIT_SET_CD = esu.UNIT_SET_CD(+) AND apd.US_VERSION_NUMBER = esu.VERSION_NUMBER(+) AND apd.ADM_DOC_STATUS = dst.ADM_DOC_STATUS AND apd.LATE_ADM_FEE_STATUS = fst.ADM_FEE_STATUS AND apd.ADM_OUTCOME_STATUS = ost.ADM_OUTCOME_STATUS AND apd.ADM_ENTRY_QUAL_STATUS = eqst.ADM_ENTRY_QUAL_STATUS AND apd.ADM_CNDTNL_OFFER_STATUS = cdst.ADM_CNDTNL_OFFER_STATUS AND apd.ADM_OFFER_RESP_STATUS = orst.ADM_OFFER_RESP_STATUS AND apd.ADM_OFFER_DFRMNT_STATUS = dfst.ADM_OFFER_DFRMNT_STATUS AND apd.CORRESPONDENCE_CAT = cct.CORRESPONDENCE_CAT(+) AND apd.ENROLMENT_CAT = ect.ENROLMENT_CAT(+) AND apd.FUNDING_SOURCE = ffs.FUNDING_SOURCE(+) AND apd.HECS_PAYMENT_OPTION = hpop.HECS_PAYMENT_OPTION(+) AND apd.FEE_CAT = fct.FEE_CAT(+) AND apd.ATTENT_OTHER_INST_CD = php.oss_org_unit_cd (+) AND php.party_id = p4.party_id (+) AND apd.ENTRY_STATUS = cc4.code_id(+) AND apd.ENTRY_LEVEL = cc5.code_id(+) AND apd.EDU_GOAL_PRIOR_ENROLL_ID = cc6.code_id(+) AND apd.SCH_APL_TO_ID = scht.SCH_APL_TO_ID(+) AND ci1.CAL_TYPE(+) = apd.DEFERRED_ADM_CAL_TYPE AND ci1.SEQUENCE_NUMBER(+) = apd.DEFERRED_ADM_CI_SEQUENCE_NUM AND ci2.CAL_TYPE(+) = apd.FUTURE_ACAD_CAL_TYPE AND ci2.SEQUENCE_NUMBER(+) = apd.FUTURE_ACAD_CI_SEQUENCE_NUMBER AND ci3.CAL_TYPE(+) = apd.FUTURE_ADM_CAL_TYPE AND ci3.SEQUENCE_NUMBER(+) = apd.FUTURE_ADM_CI_SEQUENCE_NUMBER AND ci.CAL_TYPE (+) = apd.ADM_CAL_TYPE AND ci.SEQUENCE_NUMBER(+) = apd.ADM_CI_SEQUENCE_NUMBER AND ci4.CAL_TYPE(+) = APD.DEF_ACAD_CAL_TYPE AND ci4.SEQUENCE_NUMBER (+) = APD.DEF_ACAD_CI_SEQUENCE_NUM AND apd.AUTHORIZING_PERS_ID = p3.party_id(+) AND P3.PARTY_ID = PP3.PARTY_ID (+) AND PP3.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED' AND SYSDATE BETWEEN PP3.EFFECTIVE_START_DATE(+) AND NVL(PP3.EFFECTIVE_END_DATE,SYSDATE) And apd.DECISION_MAKE_ID = p2.party_id(+) AND P2.PARTY_ID = PP2.PARTY_ID AND PP2.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED' AND SYSDATE BETWEEN PP2.EFFECTIVE_START_DATE AND NVL(PP2.EFFECTIVE_END_DATE,SYSDATE) With read only

View Text - HTML Formatted

SELECT APD.PERSON_ID
, P1.PARTY_NUMBER
, PP1.PERSON_NAME
, APD.ADMISSION_APPL_NUMBER
, APD.NOMINATED_COURSE_CD
, PSV.TITLE
, APD.SEQUENCE_NUMBER
, APD.PREDICTED_GPA
, APD.ACADEMIC_INDEX
, CI.ALTERNATE_CODE
, APD.ADM_CAL_TYPE
, APD.ADM_CI_SEQUENCE_NUMBER
, APD.APP_FILE_LOCATION
, APD.APP_SOURCE_ID
, CC1.NAME
, CC1.DESCRIPTION
, APD.COURSE_CD
, PSV.TITLE
, APD.CRV_VERSION_NUMBER
, APD.WAITLIST_RANK
, APD.LOCATION_CD
, LC.DESCRIPTION
, APD.ATTENT_OTHER_INST_CD
, P4.PARTY_NAME
, APD.ATTENDANCE_MODE
, EAM.DESCRIPTION
, APD.EDU_GOAL_PRIOR_ENROLL_ID
, CC6.NAME
, CC6.DESCRIPTION
, APD.ATTENDANCE_TYPE
, EAT.DESCRIPTION
, APD.DECISION_MAKE_ID
, P2.PARTY_NUMBER
, PP2.PERSON_NAME
, APD.UNIT_SET_CD
, ESU.TITLE
, APD.US_VERSION_NUMBER
, APD.DECISION_DATE
, '_DF:IGS:IGS_AD_APPL_INST_FLEX:APD'
, APD.DECISION_REASON_ID
, CC2.NAME
, CC2.DESCRIPTION
, APD.DECISION_NOTES
, APD.PENDING_REASON_ID
, CC3.NAME
, CC3.DESCRIPTION
, APD.PREFERENCE_NUMBER
, APD.ADM_DOC_STATUS
, DST.DESCRIPTION
, APD.ADM_ENTRY_QUAL_STATUS
, EQST.DESCRIPTION
, APD.DEFICIENCY_IN_PREP
, APD.LATE_ADM_FEE_STATUS
, FST.DESCRIPTION
, APD.SPL_CONSIDER_COMMENTS
, '_LA:APD.APPLY_FOR_FINAID:IGS_LOOKUP_VALUES:YES_NO:MEANING'
, APD.FINAID_APPLY_DATE
, APD.ADM_OUTCOME_STATUS
, OST.DESCRIPTION
, APD.ADM_OTCM_STATUS_AUTH_PERSON_ID
, IGS_GE_GEN_001.ADM_GET_NAME (APD.ADM_OTCM_STATUS_AUTH_PERSON_ID)
, APD.ADM_OUTCOME_STATUS_AUTH_DT
, APD.ADM_OUTCOME_STATUS_REASON
, APD.OFFER_DT
, APD.OFFER_RESPONSE_DT
, APD.PRPSD_COMMENCEMENT_DT
, APD.ADM_CNDTNL_OFFER_STATUS
, CDST.DESCRIPTION
, APD.CNDTNL_OFFER_CNDTN
, APD.CNDTNL_OFFER_SATISFIED_DT
, '_LA:APD.CNDTNL_OFFER_MUST_BE_STSFD_IND:IGS_LOOKUP_VALUES:YES_NO:MEANING'
, APD.ADM_OFFER_RESP_STATUS
, ORST.DESCRIPTION
, APD.ACTUAL_RESPONSE_DT
, APD.ADM_OFFER_DFRMNT_STATUS
, DFST.DESCRIPTION
, CI1.ALTERNATE_CODE
, APD.DEFERRED_ADM_CAL_TYPE
, APD.DEFERRED_ADM_CI_SEQUENCE_NUM
, APD.DEFERRED_TRACKING_ID
, APD.ASS_RANK
, APD.SECONDARY_ASS_RANK
, APD.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, APD.ASS_TRACKING_ID
, APD.FEE_CAT
, FCT.DESCRIPTION
, APD.HECS_PAYMENT_OPTION
, HPOP.DESCRIPTION
, APD.EXPECTED_COMPLETION_YR
, APD.EXPECTED_COMPLETION_PERD
, APD.CORRESPONDENCE_CAT
, CCT.DESCRIPTION
, APD.ENROLMENT_CAT
, ECT.DESCRIPTION
, APD.FUNDING_SOURCE
, FFS.DESCRIPTION
, APD.APPLICANT_ACPTNCE_CNDTN
, APD.SS_APPLICATION_ID
, APD.SS_PWD
, APD.ENTRY_STATUS
, CC4.NAME
, CC4.DESCRIPTION
, APD.ENTRY_LEVEL
, CC5.NAME
, CC5.DESCRIPTION
, APD.SCH_APL_TO_ID
, SCHT.SCHOOL_APPLYING_TO
, SCHT.DESCRIPTION
, APD.AUTHORIZED_DT
, APD.AUTHORIZING_PERS_ID
, P3.PARTY_NUMBER
, PP3.PERSON_NAME
, APD.IDX_CALC_DATE
, '_LA:APD.WAITLIST_STATUS:IGS_LOOKUP_VALUES:ADM_WAITLIST_STATUS:MEANING'
, CI2.ALTERNATE_CODE
, APD.FUTURE_ACAD_CAL_TYPE
, APD.FUTURE_ACAD_CI_SEQUENCE_NUMBER
, CI3.ALTERNATE_CODE
, APD.FUTURE_ADM_CAL_TYPE
, APD.FUTURE_ADM_CI_SEQUENCE_NUMBER
, APD.PREVIOUS_TERM_ADM_APPL_NUMBER
, APD.PREVIOUS_TERM_SEQUENCE_NUMBER
, APD.FUTURE_TERM_ADM_APPL_NUMBER
, APD.FUTURE_TERM_SEQUENCE_NUMBER
, CI4.ALTERNATE_CODE
, APD.DEF_ACAD_CAL_TYPE
, APD.DEF_ACAD_CI_SEQUENCE_NUM
, APD.DEF_PREV_TERM_ADM_APPL_NUM
, APD.DEF_PREV_APPL_SEQUENCE_NUM
, APD.DEF_TERM_ADM_APPL_NUM
, APD.DEF_APPL_SEQUENCE_NUM
, APD.CREATED_BY
, APD.CREATION_DATE
, APD.LAST_UPDATED_BY
, APD.LAST_UPDATE_DATE
, APD.APPL_INST_STATUS
, APD.AIS_REASON AIS_REASON_CODE
, '_LA:APD.AIS_REASON:IGS_LOOKUP_VALUES:IGS_AD_WITHDRAW_RSN:MEANING' "_LA:AIS_REASON"
, APD.DECLINE_OFR_REASON DECLINE_OFR_REASON_CODE
, '_LA:APD.DECLINE_OFR_REASON:IGS_LOOKUP_VALUES:IGS_AD_DECL_OFR_REAS:MEANING' "_LA:DECLINE_OFR_REASON"
FROM IGS_AD_PS_APPL_INST_ALL APD
, HZ_PARTIES P1
, HZ_PERSON_PROFILES PP1
, IGS_AD_CODE_CLASSES CC1
, IGS_AD_CODE_CLASSES CC2
, IGS_AD_CODE_CLASSES CC3
, IGS_AD_CODE_CLASSES CC4
, IGS_AD_CODE_CLASSES CC5
, IGS_AD_CODE_CLASSES CC6
, IGS_PS_VER_ALL PSV
, IGS_AD_LOCATION_ALL LC
, IGS_EN_ATD_MODE_ALL EAM
, IGS_EN_ATD_TYPE_ALL EAT
, IGS_EN_UNIT_SET_ALL ESU
, IGS_AD_DOC_STAT DST
, IGS_AD_FEE_STAT FST
, IGS_AD_OU_STAT OST
, IGS_AD_CNDNL_OFRSTAT CDST
, IGS_AD_OFR_RESP_STAT ORST
, IGS_AD_ENT_QF_STAT EQST
, IGS_AD_OFRDFRMT_STAT DFST
, IGS_CO_CAT CCT
, IGS_EN_ENROLMENT_CAT ECT
, IGS_FI_FUND_SRC_ALL FFS
, IGS_FI_HECS_PAY_OPTN HPOP
, IGS_FI_FEE_CAT_ALL FCT
, HZ_PARTIES P4
, IGS_PE_HZ_PARTIES PHP
, IGS_AD_SCHL_APLY_TO SCHT
, IGS_CA_INST_ALL CI
, IGS_CA_INST_ALL CI1
, IGS_CA_INST_ALL CI2
, IGS_CA_INST_ALL CI3
, IGS_CA_INST_ALL CI4
, HZ_PARTIES P2
, HZ_PERSON_PROFILES PP2
, HZ_PARTIES P3
, HZ_PERSON_PROFILES PP3
WHERE APD.NOMINATED_COURSE_CD = PSV.COURSE_CD
AND APD.CRV_VERSION_NUMBER = PSV.VERSION_NUMBER
AND APD.PERSON_ID = P1.PARTY_ID
AND P1.PARTY_ID = PP1.PARTY_ID
AND PP1.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED'
AND SYSDATE BETWEEN PP1.EFFECTIVE_START_DATE
AND NVL(PP1.EFFECTIVE_END_DATE
, SYSDATE)
AND APD.APP_SOURCE_ID = CC1.CODE_ID(+)
AND APD.DECISION_REASON_ID = CC2.CODE_ID(+)
AND APD.PENDING_REASON_ID = CC3.CODE_ID(+)
AND APD.LOCATION_CD = LC.LOCATION_CD(+)
AND APD.ATTENDANCE_MODE = EAM.ATTENDANCE_MODE(+)
AND APD.ATTENDANCE_TYPE = EAT.ATTENDANCE_TYPE(+)
AND APD.UNIT_SET_CD = ESU.UNIT_SET_CD(+)
AND APD.US_VERSION_NUMBER = ESU.VERSION_NUMBER(+)
AND APD.ADM_DOC_STATUS = DST.ADM_DOC_STATUS
AND APD.LATE_ADM_FEE_STATUS = FST.ADM_FEE_STATUS
AND APD.ADM_OUTCOME_STATUS = OST.ADM_OUTCOME_STATUS
AND APD.ADM_ENTRY_QUAL_STATUS = EQST.ADM_ENTRY_QUAL_STATUS
AND APD.ADM_CNDTNL_OFFER_STATUS = CDST.ADM_CNDTNL_OFFER_STATUS
AND APD.ADM_OFFER_RESP_STATUS = ORST.ADM_OFFER_RESP_STATUS
AND APD.ADM_OFFER_DFRMNT_STATUS = DFST.ADM_OFFER_DFRMNT_STATUS
AND APD.CORRESPONDENCE_CAT = CCT.CORRESPONDENCE_CAT(+)
AND APD.ENROLMENT_CAT = ECT.ENROLMENT_CAT(+)
AND APD.FUNDING_SOURCE = FFS.FUNDING_SOURCE(+)
AND APD.HECS_PAYMENT_OPTION = HPOP.HECS_PAYMENT_OPTION(+)
AND APD.FEE_CAT = FCT.FEE_CAT(+)
AND APD.ATTENT_OTHER_INST_CD = PHP.OSS_ORG_UNIT_CD (+)
AND PHP.PARTY_ID = P4.PARTY_ID (+)
AND APD.ENTRY_STATUS = CC4.CODE_ID(+)
AND APD.ENTRY_LEVEL = CC5.CODE_ID(+)
AND APD.EDU_GOAL_PRIOR_ENROLL_ID = CC6.CODE_ID(+)
AND APD.SCH_APL_TO_ID = SCHT.SCH_APL_TO_ID(+)
AND CI1.CAL_TYPE(+) = APD.DEFERRED_ADM_CAL_TYPE
AND CI1.SEQUENCE_NUMBER(+) = APD.DEFERRED_ADM_CI_SEQUENCE_NUM
AND CI2.CAL_TYPE(+) = APD.FUTURE_ACAD_CAL_TYPE
AND CI2.SEQUENCE_NUMBER(+) = APD.FUTURE_ACAD_CI_SEQUENCE_NUMBER
AND CI3.CAL_TYPE(+) = APD.FUTURE_ADM_CAL_TYPE
AND CI3.SEQUENCE_NUMBER(+) = APD.FUTURE_ADM_CI_SEQUENCE_NUMBER
AND CI.CAL_TYPE (+) = APD.ADM_CAL_TYPE
AND CI.SEQUENCE_NUMBER(+) = APD.ADM_CI_SEQUENCE_NUMBER
AND CI4.CAL_TYPE(+) = APD.DEF_ACAD_CAL_TYPE
AND CI4.SEQUENCE_NUMBER (+) = APD.DEF_ACAD_CI_SEQUENCE_NUM
AND APD.AUTHORIZING_PERS_ID = P3.PARTY_ID(+)
AND P3.PARTY_ID = PP3.PARTY_ID (+)
AND PP3.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED'
AND SYSDATE BETWEEN PP3.EFFECTIVE_START_DATE(+)
AND NVL(PP3.EFFECTIVE_END_DATE
, SYSDATE)
AND APD.DECISION_MAKE_ID = P2.PARTY_ID(+)
AND P2.PARTY_ID = PP2.PARTY_ID
AND PP2.CONTENT_SOURCE_TYPE (+) = 'USER_ENTERED'
AND SYSDATE BETWEEN PP2.EFFECTIVE_START_DATE
AND NVL(PP2.EFFECTIVE_END_DATE
, SYSDATE) WITH READ ONLY