View: IGS_DA_XML_SUBPROGRAM_V

Product:	IGS - Student System
Description:	XML element SubProgramType (child of AcademicProgram). Returns the Major(s), Minor(s), and Track(s) of the Academic Program.
Implementation/DBA Data:	APPS.IGS_DA_XML_SUBPROGRAM_V

View Text

SELECT DS.WIF_MAJOR_UNIT_SET_CAT
, DRW.MAJOR_UNIT_SET_CD1
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MAJOR_UNIT_SET_CD1)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MAJOR1'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DS.PROGRAM_DEFINITION_IND = 'N'
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MAJOR_UNIT_SET_CD1 IS NOT NULL UNION ALL SELECT DS.WIF_MAJOR_UNIT_SET_CAT
, DRW.MAJOR_UNIT_SET_CD2
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MAJOR_UNIT_SET_CD2)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MAJOR2'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DS.PROGRAM_DEFINITION_IND = 'N'
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MAJOR_UNIT_SET_CD2 IS NOT NULL UNION ALL SELECT DS.WIF_MAJOR_UNIT_SET_CAT
, DRW.MAJOR_UNIT_SET_CD3
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MAJOR_UNIT_SET_CD3)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MAJOR3'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DS.PROGRAM_DEFINITION_IND = 'N'
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MAJOR_UNIT_SET_CD3 IS NOT NULL UNION ALL SELECT DS.WIF_MINOR_UNIT_SET_CAT
, DRW.MINOR_UNIT_SET_CD1
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MINOR_UNIT_SET_CD1)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MINOR1'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MINOR_UNIT_SET_CD1 IS NOT NULL UNION ALL SELECT DS.WIF_MINOR_UNIT_SET_CAT
, DRW.MINOR_UNIT_SET_CD2
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MINOR_UNIT_SET_CD2)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MINOR2'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MINOR_UNIT_SET_CD2 IS NOT NULL UNION ALL SELECT DS.WIF_MINOR_UNIT_SET_CAT
, DRW.MINOR_UNIT_SET_CD3
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.MINOR_UNIT_SET_CD3)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'MINOR3'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.MINOR_UNIT_SET_CD3 IS NOT NULL UNION ALL SELECT DS.WIF_TRACK_UNIT_SET_CAT
, DRW.TRACK_UNIT_SET_CD1
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.TRACK_UNIT_SET_CD1)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'TRACK1'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.TRACK_UNIT_SET_CD1 IS NOT NULL UNION ALL SELECT DS.WIF_TRACK_UNIT_SET_CAT
, DRW.TRACK_UNIT_SET_CD2
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.TRACK_UNIT_SET_CD2)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'TRACK2'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.TRACK_UNIT_SET_CD2 IS NOT NULL UNION ALL SELECT DS.WIF_TRACK_UNIT_SET_CAT
, DRW.TRACK_UNIT_SET_CD3
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(DRW.TRACK_UNIT_SET_CD3)
, CI.ALTERNATE_CODE
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'TRACK3'
FROM IGS_DA_REQ_WIF DRW
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
WHERE DRS.BATCH_ID = DRW.BATCH_ID
AND DRS.WIF_ID = DRW.WIF_ID
AND DS.S_CONTROL_NUM = 1
AND DRW.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND DRW.CATALOG_CI_SEQ_NUM = CI.SEQUENCE_NUMBER
AND DRW.TRACK_UNIT_SET_CD3 IS NOT NULL UNION ALL SELECT US.UNIT_SET_CAT
, US.UNIT_SET_CD
, IGS_GE_GEN_004.GET_UNIT_SET_TITLE(SUSA.UNIT_SET_CD)
, NVL(CI.ALTERNATE_CODE
, 'N/A')
, ''
, ''
, DRS.BATCH_ID
, DRS.PERSON_ID
, DRS.IGS_DA_REQ_STDNTS_ID
, 'UNITSET'
FROM IGS_EN_UNIT_SET US
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_SETUP DS
, IGS_CA_INST_ALL CI
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_AS_SU_SETATMPT SUSA
, IGS_PE_STAT_DETAILS PSD
WHERE DRS.PERSON_ID = SPA.PERSON_ID
AND DRS.PROGRAM_CODE = SPA.COURSE_CD
AND DS.S_CONTROL_NUM = 1
AND (DS.PROGRAM_DEFINITION_IND = 'N' OR US.UNIT_SET_CAT != DS.WIF_MAJOR_UNIT_SET_CAT)
AND SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND SUSA.STUDENT_CONFIRMED_IND = 'Y'
AND (SUSA.END_DT IS NULL OR (SUSA.END_DT IS NOT NULL
AND SUSA.RQRMNTS_COMPLETE_IND = 'Y'))
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND SUSA.US_VERSION_NUMBER = US.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 DRS.WIF_PROGRAM_CODE IS NULL

Columns