View: IGS_AD_MKADM_DES_INT_V

View Text

SELECT MDI.ROWID ROW_ID
, MDI.INTERFACE_MKDES_ID INTERFACE_MKDES_ID
, MDI.INTERFACE_RUN_ID INTERFACE_RUN_ID
, MDI.BATCH_ID BATCH_ID
, ABDD.DESCRIPTION DESCRIPTION
, AA.ACAD_CAL_TYPE ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER ACAD_CI_SEQUENCE_NUMBER
, AA.ADM_CAL_TYPE ADM_CAL_TYPE
, AA.ADM_CI_SEQUENCE_NUMBER ADM_CI_SEQUENCE_NUMBER
, CI2.ALTERNATE_CODE ADM_ALTERNATE_CODE
, CI1.ALTERNATE_CODE ACAD_ALTERNATE_CODE
, AA.ADMISSION_CAT ADMISSION_CAT
, AA.S_ADMISSION_PROCESS_TYPE S_ADMISSION_PROCESS_TYPE
, MDI.PERSON_ID PERSON_ID
, MDI.SEQUENCE_NUMBER SEQUENCE_NUMBER
, PP.PERSON_NUMBER PERSON_NUMBER
, PP.FULL_NAME FULL_NAME
, PIT.API_PERSON_ID API_PERSON_ID
, MDI.ADMISSION_APPL_NUMBER ADMISSION_APPL_NUMBER
, MDI.NOMINATED_COURSE_CD NOMINATED_COURSE_CD
, CRV.TITLE COURSE_TITLE
, MDI.ADM_OUTCOME_STATUS ADM_OUTCOME_STATUS
, MDI.ERROR_TEXT ERROR_TEXT
, MDI.STATUS STATUS
, MDI.ERROR_CODE ERROR_CODE
, MDI.DECISION_MAKE_ID DECISION_MAKE_ID
, PPV.PERSON_NUMBER DECISION_MAKER_NUMBER
, PPV.FULL_NAME DECISION_MAKER_NAME
, MDI.DECISION_DATE DECISION_DATE
, MDI.DECISION_REASON_ID DECISION_REASON_ID
, CC1.NAME DECISION_REASON
, CC1.DESCRIPTION DECISION_REASON_DESC
, MDI.PENDING_REASON_ID PENDING_REASON_ID
, CC2.NAME PENDING_REASON
, CC2.DESCRIPTION PENDING_REASON_DESC
, MDI.OFFER_DT OFFER_DT
, MDI.OFFER_RESPONSE_DT OFFER_RESPONSE_DT
, MDI.CREATION_DATE CREATION_DATE
, MDI.CREATED_BY CREATED_BY
, MDI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MDI.LAST_UPDATED_BY LAST_UPDATED_BY
, MDI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MDI.REQUEST_ID REQUEST_ID
, MDI.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, MDI.PROGRAM_ID PROGRAM_ID
, MDI.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, APAIAV.CRV_VERSION_NUMBER CRV_VERSION_NUMBER
, APAIAV.LOCATION_CD LOCATION_CD
, APAIAV.ATTENDANCE_MODE ATTENDANCE_MODE
, APAIAV.ATTENDANCE_TYPE ATTENDANCE_TYPE
, APAIAV.UNIT_SET_CD UNIT_SET_CD
, APAIAV.US_VERSION_NUMBER US_VERSION_NUMBER
, MDI.RECONSIDER_FLAG RECONSIDER_FLAG
, MDI. PRPSD_COMMENCEMENT_DATE
FROM IGS_AD_ADMDE_INT MDI
, IGS_AD_BATC_DEF_DET ABDD
, IGS_PE_PERSON_BASE_V PP
, IGS_PE_PERSON_BASE_V PPV
, IGS_AD_PS_APPL_INST APAIAV
, IGS_PS_VER CRV
, IGS_AD_APPL AA
, IGS_CA_INST CI1
, IGS_CA_INST CI2
, IGS_AD_CODE_CLASSES CC1
, IGS_AD_CODE_CLASSES CC2
, IGS_PE_PERSON_ID_TYPE_V PIT
WHERE /* JOINING INTERFACE TABLE
AND DEFAULT DECISION DETAILS TABLE */ MDI.BATCH_ID = ABDD.BATCH_ID
AND /* JOINING INTERFACE TABLE
AND PERSON TABLE */ MDI.PERSON_ID = PP.PERSON_ID
AND /* JOINING INTERFACE TABLE
AND APPLICATION INSTANCE TABLE */ MDI.PERSON_ID = AA.PERSON_ID
AND MDI.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND /* JOINING APPLICATION INSTANCE VIEW
AND APPLICATION INTERFACE VIEW */ APAIAV.PERSON_ID = MDI.PERSON_ID
AND APAIAV.NOMINATED_COURSE_CD = MDI.NOMINATED_COURSE_CD
AND APAIAV.ADMISSION_APPL_NUMBER = MDI.ADMISSION_APPL_NUMBER
AND APAIAV.SEQUENCE_NUMBER = MDI.SEQUENCE_NUMBER
AND CRV.COURSE_CD = APAIAV.COURSE_CD
AND CRV.VERSION_NUMBER = APAIAV.CRV_VERSION_NUMBER
AND CI1.CAL_TYPE = AA.ACAD_CAL_TYPE
AND CI1.SEQUENCE_NUMBER = AA.ACAD_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = AA.ADM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = AA.ADM_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = NVL(APAIAV.ADM_CAL_TYPE
, AA.ADM_CAL_TYPE)
AND CI2.SEQUENCE_NUMBER = NVL(APAIAV.ADM_CI_SEQUENCE_NUMBER
, AA.ADM_CI_SEQUENCE_NUMBER )
AND /* JOINING APPLICATION INTERFACE VIEW
AND PERSON VIEW */ PPV.PERSON_ID (+) = MDI.DECISION_MAKE_ID
AND /* JOINING INTERFACE TABLE
AND CODE CLASSES TABLE FOR DECISION REASON*/ MDI.DECISION_REASON_ID = CC1.CODE_ID (+)
AND /* JOINING INTERFACE TABLE
AND CODE CLASSES TABLE FOR PENDING REASON*/ MDI.PENDING_REASON_ID = CC2.CODE_ID (+)
AND /* JOINING INTERFACE TABLE
AND LOOKUPS VIEW */ PIT.PE_PERSON_ID (+) = PP.PERSON_ID
AND /* IF ALL THE APC/CALENDER COLUMNS ARE NULL IN BATCH TABLE THEN SELECT ALL THE RECORDS
FROM THE DECISION INTERFACE TABLE ELSE SELECT ALL THE RECORDS MATCHING TO APC/CALENDER OF BATCH RECORD.*/ ( (ABDD.ACAD_CAL_TYPE IS NULL
AND ABDD.ACAD_CI_SEQUENCE_NUMBER IS NULL
AND ABDD.ADM_CAL_TYPE IS NULL
AND ABDD.ADM_CI_SEQUENCE_NUMBER IS NULL
AND ABDD.ADMISSION_CAT IS NULL
AND ABDD.S_ADMISSION_PROCESS_TYPE IS NULL) OR (ABDD.ACAD_CAL_TYPE = AA.ACAD_CAL_TYPE
AND ABDD.ACAD_CI_SEQUENCE_NUMBER = AA.ACAD_CI_SEQUENCE_NUMBER
AND ABDD.ADM_CAL_TYPE = NVL(APAIAV.ADM_CAL_TYPE
, AA.ADM_CAL_TYPE)
AND ABDD.ADM_CI_SEQUENCE_NUMBER = NVL(APAIAV.ADM_CI_SEQUENCE_NUMBER
, AA.ADM_CI_SEQUENCE_NUMBER )
AND ABDD.ADMISSION_CAT = AA.ADMISSION_CAT
AND ABDD.S_ADMISSION_PROCESS_TYPE = AA.S_ADMISSION_PROCESS_TYPE) )

Columns