View: IGS_DA_XML_ACADEMICPROGRAM_V

Product:	IGS - Student System
Description:	XML element AcademicProgramSub view indexed by Degree Program view Provides the provides the DA specified program information "SPA" -- Students Selected Degree Program "WIF" -- What-if Program "SPECIAL" -- Special Program
Implementation/DBA Data:	APPS.IGS_DA_XML_ACADEMICPROGRAM_V

View Text

SELECT PV.COURSE_TYPE
, DECODE(DS.PROGRAM_DEFINITION_IND
, 'N'
, DRS.PROGRAM_CODE
, 'Y'
, DRS.PROGRAM_MAJOR_CODE)
, PV.TITLE
, ''
, NVL(CI.ALTERNATE_CODE
, 'N/A')
, IGS_PR_GET_CLASS_STD.GET_CLASS_STANDING(SPA.PERSON_ID
, SPA.COURSE_CD
, 'N'
, NULL
, CI2.CAL_TYPE
, CI2.SEQUENCE_NUMBER)
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, CI2.CAL_TYPE
, CI2.SEQUENCE_NUMBER
, 'SPA1'
FROM IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_CA_INST_ALL CI
, IGS_CA_INST_ALL CI2
, IGS_PS_VER_ALL PV
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_PE_STAT_DETAILS PSD
WHERE SPA.PERSON_ID = DRS.PERSON_ID
AND SPA.COURSE_CD = DRS.PROGRAM_CODE
AND PV.COURSE_CD = SPA.COURSE_CD
AND PV.VERSION_NUMBER = SPA.VERSION_NUMBER
AND SPA.PERSON_ID = PSD.PERSON_ID(+)
AND PSD.CATALOG_CAL_TYPE = CI.CAL_TYPE(+)
AND PSD.CATALOG_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER(+)
AND DS.S_CONTROL_NUM = 1
AND (CI2.CAL_TYPE
, CI2.SEQUENCE_NUMBER) IN (SELECT TTL.LOAD_CAL_TYPE
, TTL.LOAD_CI_SEQUENCE_NUMBER
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_CA_TEACH_TO_LOAD_V TTL
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND TTL.LOAD_END_DT = (SELECT MAX(TTL2.LOAD_END_DT)
FROM IGS_EN_SU_ATTEMPT SUA2
, IGS_CA_TEACH_TO_LOAD_V TTL2
WHERE SUA2.PERSON_ID = SPA.PERSON_ID
AND SUA2.CAL_TYPE = TTL2.TEACH_CAL_TYPE
AND SUA2.CI_SEQUENCE_NUMBER = TTL2.TEACH_CI_SEQUENCE_NUMBER)) UNION ALL SELECT PV.COURSE_TYPE
, DECODE(DS.PROGRAM_DEFINITION_IND
, 'N'
, DRS.PROGRAM_CODE
, 'Y'
, DRS.PROGRAM_MAJOR_CODE)
, PV.TITLE
, ''
, NVL(CI.ALTERNATE_CODE
, 'N/A')
, IGS_PR_GET_CLASS_STD.GET_CLASS_STANDING(SPA.PERSON_ID
, SPA.COURSE_CD
, 'N'
, NULL
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER)
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, ''
, TO_NUMBER(NULL)
, 'SPA2'
FROM IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_CA_INST_ALL CI
, IGS_PS_VER_ALL PV
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_PE_STAT_DETAILS PSD
WHERE SPA.PERSON_ID = DRS.PERSON_ID
AND SPA.COURSE_CD = DRS.PROGRAM_CODE
AND PV.COURSE_CD = SPA.COURSE_CD
AND PV.VERSION_NUMBER = SPA.VERSION_NUMBER
AND SPA.PERSON_ID = PSD.PERSON_ID(+)
AND PSD.CATALOG_CAL_TYPE = CI.CAL_TYPE(+)
AND PSD.CATALOG_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER(+)
AND DS.S_CONTROL_NUM = 1
AND NOT EXISTS(SELECT 'X'
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_CA_TEACH_TO_LOAD_V TTL
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER) UNION ALL SELECT PV.COURSE_TYPE
, DECODE ( DS.PROGRAM_DEFINITION_IND
, 'N'
, DRS.WIF_PROGRAM_CODE
, 'Y'
, DRS.PROGRAM_MAJOR_CODE)
, PV.TITLE
, ''
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, ''
, TO_NUMBER (NULL)
, 'WIF'
FROM IGS_PS_VER_ALL PV
, IGS_CA_INST_ALL CI
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_REQ_WIF DRW
, IGS_DA_SETUP DS
WHERE PV.COURSE_CD = DRS.WIF_PROGRAM_CODE
AND PV.VERSION_NUMBER = (SELECT PV2.VERSION_NUMBER
FROM IGS_PS_VER PV2
, IGS_PS_STAT PS
WHERE PV2.COURSE_CD = DRS.WIF_PROGRAM_CODE
AND PS.COURSE_STATUS = PV2.COURSE_STATUS
AND PS.S_COURSE_STATUS = 'ACTIVE'
AND ROWNUM = 1)
AND DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DS.S_CONTROL_NUM = 1 UNION ALL SELECT 'SPECIAL'
, DECODE ( DS.PROGRAM_DEFINITION_IND
, 'N'
, DRS.SPECIAL_PROGRAM_CODE
, 'Y'
, DRS.PROGRAM_MAJOR_CODE )
, FLV.DESCRIPTION
, ''
, DR.SPECIAL_PROGRAM_CATALOG
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, ''
, TO_NUMBER (NULL)
, 'SPECIAL'
FROM IGS_DA_RQST DR
, IGS_DA_REQ_STDNTS DRS
, FND_LOOKUP_VALUES FLV
, IGS_DA_SETUP DS
WHERE FLV.LOOKUP_TYPE = 'IGS_DA_SPECIAL_PROGRAM'
AND FLV.LOOKUP_CODE = DRS.SPECIAL_PROGRAM_CODE
AND DRS.BATCH_ID = DR.BATCH_ID
AND DS.S_CONTROL_NUM = 1

Columns