VIEW: APPS.IGS_PR_ACAD_TRANS_CREDIT_V

Source

View Text - Preformatted

SELECT spa.person_id, pe.party_number person_number, spa.course_cd program_cd, ahv.institution_code, ahv.institution_name, ahv.institution_type, hz.city institution_city, hz.state institution_state, hz.postal_code institution_postal_code, ahv.start_date, ahv.end_date, ahv.program_type_attempted, ahv.program_type_earned, ahv.program_code, ahv.title, ahv.education_id, SUM(adv.credit_points) credit_points FROM igs_en_stdnt_ps_att_all spa, hz_parties pe, hz_parties hz, igs_ad_acad_history_v ahv, ( SELECT asu.person_id, ath.education_id, asu.as_course_cd, asu.achievable_credit_points credit_points FROM igs_av_stnd_unit_all asu, igs_ad_term_unitdtls atud, igs_ad_term_details atd, igs_ad_transcript ath WHERE asu.prev_unit_cd IS NOT NULL AND asu.s_adv_stnd_granting_status = 'GRANTED' AND asu.s_adv_stnd_recognition_type = 'CREDIT' AND atud.unit_details_id = asu.unit_details_id AND atd.term_details_id = atud.term_details_id AND ath.transcript_id = atd.transcript_id UNION ALL SELECT asul.person_id, ath.education_id, asul.as_course_cd, asul.credit_points FROM igs_av_stnd_unit_lvl_all asul, igs_ad_term_unitdtls atud, igs_ad_term_details atd, igs_ad_transcript ath WHERE asul.prev_unit_cd IS NOT NULL AND asul.s_adv_stnd_granting_status = 'GRANTED' AND atud.unit_details_id = asul.unit_details_id AND atd.term_details_id = atud.term_details_id AND ath.transcript_id = atd.transcript_id ) adv, IGS_PE_HZ_PARTIES IPZ WHERE ahv.person_id = spa.person_id AND pe.party_id = spa.person_id AND adv.person_id = spa.person_id AND adv.education_id = ahv.education_id AND ipz.oss_org_unit_cd(+) = ahv.institution_code AND ipz.party_id = hz.party_id(+) AND adv.as_course_cd = spa.course_cd GROUP BY spa.person_id, pe.party_number, spa.course_cd, ahv.institution_code, ahv.institution_name, ahv.institution_type, hz.city, hz.state, hz.postal_code, ahv.start_date, ahv.end_date, ahv.program_type_attempted, ahv.program_type_earned, ahv.program_code, ahv.title, ahv.education_id

View Text - HTML Formatted

SELECT SPA.PERSON_ID
, PE.PARTY_NUMBER PERSON_NUMBER
, SPA.COURSE_CD PROGRAM_CD
, AHV.INSTITUTION_CODE
, AHV.INSTITUTION_NAME
, AHV.INSTITUTION_TYPE
, HZ.CITY INSTITUTION_CITY
, HZ.STATE INSTITUTION_STATE
, HZ.POSTAL_CODE INSTITUTION_POSTAL_CODE
, AHV.START_DATE
, AHV.END_DATE
, AHV.PROGRAM_TYPE_ATTEMPTED
, AHV.PROGRAM_TYPE_EARNED
, AHV.PROGRAM_CODE
, AHV.TITLE
, AHV.EDUCATION_ID
, SUM(ADV.CREDIT_POINTS) CREDIT_POINTS
FROM IGS_EN_STDNT_PS_ATT_ALL SPA
, HZ_PARTIES PE
, HZ_PARTIES HZ
, IGS_AD_ACAD_HISTORY_V AHV
, ( SELECT ASU.PERSON_ID
, ATH.EDUCATION_ID
, ASU.AS_COURSE_CD
, ASU.ACHIEVABLE_CREDIT_POINTS CREDIT_POINTS
FROM IGS_AV_STND_UNIT_ALL ASU
, IGS_AD_TERM_UNITDTLS ATUD
, IGS_AD_TERM_DETAILS ATD
, IGS_AD_TRANSCRIPT ATH
WHERE ASU.PREV_UNIT_CD IS NOT NULL
AND ASU.S_ADV_STND_GRANTING_STATUS = 'GRANTED'
AND ASU.S_ADV_STND_RECOGNITION_TYPE = 'CREDIT'
AND ATUD.UNIT_DETAILS_ID = ASU.UNIT_DETAILS_ID
AND ATD.TERM_DETAILS_ID = ATUD.TERM_DETAILS_ID
AND ATH.TRANSCRIPT_ID = ATD.TRANSCRIPT_ID UNION ALL SELECT ASUL.PERSON_ID
, ATH.EDUCATION_ID
, ASUL.AS_COURSE_CD
, ASUL.CREDIT_POINTS
FROM IGS_AV_STND_UNIT_LVL_ALL ASUL
, IGS_AD_TERM_UNITDTLS ATUD
, IGS_AD_TERM_DETAILS ATD
, IGS_AD_TRANSCRIPT ATH
WHERE ASUL.PREV_UNIT_CD IS NOT NULL
AND ASUL.S_ADV_STND_GRANTING_STATUS = 'GRANTED'
AND ATUD.UNIT_DETAILS_ID = ASUL.UNIT_DETAILS_ID
AND ATD.TERM_DETAILS_ID = ATUD.TERM_DETAILS_ID
AND ATH.TRANSCRIPT_ID = ATD.TRANSCRIPT_ID ) ADV
, IGS_PE_HZ_PARTIES IPZ
WHERE AHV.PERSON_ID = SPA.PERSON_ID
AND PE.PARTY_ID = SPA.PERSON_ID
AND ADV.PERSON_ID = SPA.PERSON_ID
AND ADV.EDUCATION_ID = AHV.EDUCATION_ID
AND IPZ.OSS_ORG_UNIT_CD(+) = AHV.INSTITUTION_CODE
AND IPZ.PARTY_ID = HZ.PARTY_ID(+)
AND ADV.AS_COURSE_CD = SPA.COURSE_CD GROUP BY SPA.PERSON_ID
, PE.PARTY_NUMBER
, SPA.COURSE_CD
, AHV.INSTITUTION_CODE
, AHV.INSTITUTION_NAME
, AHV.INSTITUTION_TYPE
, HZ.CITY
, HZ.STATE
, HZ.POSTAL_CODE
, AHV.START_DATE
, AHV.END_DATE
, AHV.PROGRAM_TYPE_ATTEMPTED
, AHV.PROGRAM_TYPE_EARNED
, AHV.PROGRAM_CODE
, AHV.TITLE
, AHV.EDUCATION_ID