VIEW: APPS.IGS_DA_XML_SUBPROGRAM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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