VIEW: APPS.IGS_DA_XML_ACADEMICPROGRAM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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