[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ihp.oi_govt_institution_cd govt_institution_cd
FROM igs_pe_hz_parties ihp,
igs_or_inst_stat st
WHERE ihp.oi_institution_status = st.institution_status AND
st.s_institution_status= 'ACTIVE' AND
ihp.oi_local_institution_ind = 'Y' AND
ihp.inst_org_ind = 'I' AND
ihp.oi_govt_institution_cd IS NOT NULL;
sjlaport 31-Jan-05 Modified cursor c_api for HE358 to ignore logically deleted records
***************************************************************/
-- smaddali modified this cursor to get records which are effective in the HESA submission period, bug#3235753
CURSOR c_api IS
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type = p_id_type
AND Start_Dt <= p_enrl_end_dt
AND ( End_Dt IS NULL OR End_Dt >= p_enrl_start_dt )
AND (End_Dt IS NULL OR Start_Dt <> End_Dt)
ORDER BY Start_Dt DESC;
to ignore logically deleted records
***************************************************************/
CURSOR c_comdt IS
SELECT to_char(MIN(commencement_dt),'YY')
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id;
SELECT igs_he_stdnt_id_s.nextval
FROM dual;
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type IN ('UCASID','NMASID','SWASID','GTTRID')
AND Start_Dt <= p_enrl_end_dt
AND ( End_Dt IS NULL OR End_Dt >= p_enrl_start_dt )
AND (End_Dt IS NULL OR Start_Dt <> End_Dt);
SELECT api_person_id, start_dt, end_dt
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type = 'HUSID'
AND (end_dt IS NULL OR start_dt <> end_dt)
ORDER BY start_dt DESC;
SELECT start_dt
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type = 'HUSID'
AND start_dt > p_enrl_end_dt
AND (end_dt IS NULL OR start_Dt <> end_Dt)
ORDER BY start_dt ASC;
SELECT api_person_id, start_dt, end_dt
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type = 'HUSID'
AND start_dt <= p_enrl_end_dt
AND (end_dt IS NULL OR end_dt >= p_enrl_start_dt)
AND (end_dt IS NULL OR start_dt <> end_dt)
ORDER BY start_dt DESC;
CURSOR c_deleted_husid(cp_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
cp_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE,
cp_start_dt igs_pe_alt_pers_id.start_dt%TYPE) IS
SELECT 'X'
FROM igs_pe_alt_pers_id
WHERE pe_person_id = cp_person_id
AND api_person_id = cp_api_person_id
AND start_dt = TRUNC(cp_start_dt)
AND person_id_type = 'HUSID'
AND start_dt = end_dt;
c_deleted_husid_rec c_deleted_husid%ROWTYPE ;
OPEN c_deleted_husid(p_person_id,l_stdnt_id,l_start_dt);
FETCH c_deleted_husid INTO c_deleted_husid_rec;
IF c_deleted_husid%FOUND THEN
CLOSE c_deleted_husid;
UPDATE igs_pe_alt_pers_id
SET end_dt = l_end_dt,
last_updated_by = Fnd_Global.user_id,
last_update_date = Sysdate,
last_update_login = Fnd_Global.login_id
WHERE pe_person_id = p_person_id
AND api_person_id = l_stdnt_id
AND person_id_type = 'HUSID'
AND start_dt = l_start_dt;
CLOSE c_deleted_husid;
INSERT INTO igs_pe_alt_pers_id
(pe_person_id,
api_person_id,
person_id_type,
start_dt,
end_dt,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_person_id,
l_stdnt_id,
'HUSID',
l_start_dt ,
l_end_dt ,
Fnd_Global.user_id,
Sysdate,
Fnd_Global.user_id,
Sysdate,
Fnd_Global.login_id);
OPEN c_deleted_husid(p_person_id,l_stdnt_id,c_husid_rec.end_dt + 1);
FETCH c_deleted_husid INTO c_deleted_husid_rec;
IF c_deleted_husid%FOUND THEN
CLOSE c_deleted_husid;
UPDATE igs_pe_alt_pers_id
SET end_dt = NULL,
last_updated_by = Fnd_Global.user_id,
last_update_date = Sysdate,
last_update_login = Fnd_Global.login_id
WHERE pe_person_id = p_person_id
AND api_person_id = l_stdnt_id
AND person_id_type = 'HUSID'
AND start_dt = c_husid_rec.end_dt + 1;
CLOSE c_deleted_husid;
INSERT INTO igs_pe_alt_pers_id
(pe_person_id,
api_person_id,
person_id_type,
start_dt,
end_dt,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_person_id,
l_stdnt_id,
'HUSID',
c_husid_rec.end_dt + 1 ,
NULL ,
Fnd_Global.user_id,
Sysdate,
Fnd_Global.user_id,
Sysdate,
Fnd_Global.login_id);
IF c_deleted_husid%ISOPEN
THEN
CLOSE c_deleted_husid;
SELECT b.govt_funding_source,
a.funding_source
FROM igs_fi_fnd_src_rstn a,
igs_fi_fund_src b
WHERE a.course_cd = p_course_cd
AND a.version_number = p_version_number
AND a.dflt_ind = 'Y'
AND a.funding_source = b.funding_source;
SELECT govt_funding_source,
funding_source
FROM igs_fi_fund_src
WHERE funding_source = cp_fund_src ;
anwest 09-Dec-04 Modified for HE356 - updated to accommodate
Term/Load calendar associated with residency
jtmathew 23-May-06 Modified c_res_sts for bug 5210481
***************************************************************/
-- smaddali modified this cursor for bug 2730388 to get only open code values
-- smaddali modified this cursor for bug 2367167 to get records which are effective in the HESA submission period, bug#3235753
-- anwest modified this cursor for HE356 to get open and closed values and not restricted to OSS_RESIDENCY_OS
-- jtmathew modified this cursor for bug 5210481 to remove igs_pe_res_dtls_v view
CURSOR c_res_sts (cp_res_class_cd VARCHAR2) IS
SELECT peresdtls.residency_status_cd residency_status
FROM igs_pe_res_dtls_all peresdtls,
igs_lookup_values cc1,
igs_lookup_values cc2,
igs_ca_inst_all cainstall
WHERE peresdtls.person_id = p_person_id
AND peresdtls.residency_class_cd = cc1.lookup_code
AND cc1.lookup_type = 'PE_RES_CLASS'
AND peresdtls.residency_status_cd = cc2.lookup_code
AND cc2.lookup_type = 'PE_RES_STATUS'
AND peresdtls.residency_class_cd = cp_res_class_cd
AND peresdtls.cal_type = cainstall.cal_type
AND peresdtls.sequence_number = cainstall.sequence_number
AND cainstall.start_dt <= p_enrl_end_dt
ORDER BY cainstall.start_dt DESC;
SELECT 'X'
FROM igs_he_code_values hecodeval
WHERE hecodeval.code_type = 'OSS_RESIDENCY_OS'
AND hecodeval.value = cp_res_stat_cd
AND NVL(hecodeval.closed_ind,'N') = 'N' ;
SELECT surname
FROM igs_pe_person_alias_v
WHERE person_id = p_person_id
AND alias_type = 'SNAME16'
AND ( Start_Dt IS NULL OR Start_Dt <= p_enrl_end_dt )
AND ( End_Dt IS NULL OR End_Dt >= p_enrl_start_dt )
ORDER BY Start_Dt DESC;
SELECT b.map3
FROM igs_pe_citizenship_v a,
igs_he_code_map_val b
WHERE a.party_id = p_person_id
AND Nvl(End_Date, p_enrl_start_dt) >= p_enrl_start_dt
AND b.map2 = a.country_code
AND b.map3 = Nvl(p_hesa_code, b.map3)
AND b.association_code = 'UC_OSS_HE_NAT_ASSOC'
ORDER BY b.map3 DESC;
SELECT disability_type
FROM igs_pe_pers_disablty_v
WHERE person_id = p_person_id
AND ( Start_Date IS NULL OR Start_Date <= p_enrl_end_dt )
AND ( End_Date IS NULL OR End_Date >= p_enrl_start_dt )
ORDER BY Start_Date DESC;
SELECT MAX(TO_NUMBER(TO_CHAR(end_date,'YYYY')))
FROM igs_ad_acad_history_v
WHERE person_id = p_person_id
AND end_date < p_com_dt;
SELECT sequence_no
FROM igs_ps_us_prenr_cfg
WHERE unit_set_cd = p_unit_set_cd;
smaddali Added c_acad_cal and c_selection_dt cursors and modified cursor c_yos for HEFD209 build , bug#2717755
smaddali 29-Oct-03 Modified procedure get_year_of_student to add 1 new parameter for bug#3224246
jbaber 20-Sep-04 Changes for HEFD350 - Stat changes for 2004/05
Added new parameter p_yop_year_of_student. Use YoP year of student value if it exists.
jbaber 04-Nov-04 Modified c_yos to discount future dated transfers as per HE354 - Program Transfer
jbaber 15-Apr-05 Modified c_yos to include records where future_dated_trans_flag = N or S as per bug #4179106
jchakrab 03-Feb-06 Modified c_yos to exclude academic calendar instances if its start date > the HESA reporting period end date.
***************************************************************/
-- Get the academic calendar type and student instance number of the current program attempt.
CURSOR c_acad_cal IS
SELECT a.cal_type,b.student_inst_number
FROM igs_en_stdnt_ps_att_all a , igs_he_st_spa_all b
WHERE a.person_id = p_person_id AND
a.course_cd = p_course_cd AND
a.person_id = b.person_id AND
a.course_cd = b.course_cd ;
SELECT COUNT (DISTINCT cir.sup_ci_sequence_number || cir.sup_cal_type)
FROM igs_he_st_spa_all spa ,
igs_en_stdnt_ps_att sca,
igs_en_su_attempt sua,
igs_ca_inst_rel cir,
igs_ca_inst_all ca
WHERE SPA.person_id = p_person_id and
SPA.student_inst_number = p_student_inst_number AND
SCA.person_id = SPA.person_id AND
SCA.course_cd = SPA.course_cd AND
SCA.Cal_type = p_cal_type AND
NVL(sca.future_dated_trans_flag,'N') IN ('N','S') AND
SUA.person_id = SCA.person_id AND
SUA.course_cd = SCA.course_cd AND
SUA.unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DISCONTIN') AND
CIR.sub_cal_type = SUA.cal_type AND
CIR.sub_ci_sequence_number = SUA.ci_sequence_number AND
CIR.sup_cal_type = SCA.cal_type AND
SUA.ci_start_dt <= p_enrl_end_dt AND
CIR.sup_cal_type = CA.cal_type AND
CIR.sup_ci_sequence_number = CA.sequence_number AND
CA.start_dt <= p_enrl_end_dt;
gmaheswa 14-Nov-03 Bug 3227107 , address changes. Modified c_address cursor to select active records.
smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
jtmathew 23-may-06 Modified c_addrus for bug 5210481
***************************************************************/
-- smaddali modified this cursor for bug 2730388 to get only open code values
-- smaddali modified this cursor for bug#2950834 to get only active partysiteuses
-- smaddali modified this cursor to get records which are effective in the HESA submission period, bug#3235753
-- jtmathew modified this cursor for bug 5210481 to remove igs_pe_addr_v view
CURSOR c_addrus IS
SELECT MAX( site_use_type)
FROM igs_pe_partysiteuse_v a,
hz_party_sites b,
igs_pe_hz_pty_sites c,
igs_he_code_values d
WHERE a.party_site_id = b.party_site_id
AND b.party_site_id = c.party_site_id(+)
AND (b.status = 'A'
AND ( c.Start_Date IS NULL OR c.Start_Date <= p_enrl_end_dt )
AND ( c.End_Date IS NULL OR c.End_Date >= p_enrl_start_dt )
)
AND b.party_id = p_person_id
AND d.code_type = 'TERM_TIME_ADDR'
AND d.value = a.site_use_type
AND NVL(d.closed_ind,'N') = 'N'
AND a.active = 'A'
ORDER BY c.Start_Date DESC;
SELECT min(award_start_date)
FROM igs_he_submsn_awd
WHERE submission_name = p_submission_name;
SELECT max(award_end_date)
FROM igs_he_submsn_awd
WHERE submission_name = p_submission_name;
SELECT 'X'
FROM igs_he_submsn_awd
WHERE submission_name = p_submission_name
AND type = cp_type;
SELECT type, key1, award_start_date, award_end_date
FROM igs_he_submsn_awd
WHERE submission_name = p_submission_name
ORDER BY type;
SELECT COUNT(a.person_id)
FROM IGS_EN_SPA_AWD_AIM a
WHERE a.person_id = p_person_id
AND a.course_cd = p_course_cd
AND a.conferral_date BETWEEN p_enrl_start_dt and p_enrl_end_dt
AND a.complete_ind = 'Y';
SELECT course_cd
FROM igs_ps_stdnt_trn
WHERE person_id = p_person_id
AND transfer_course_cd = p_course_cd
ORDER BY transfer_dt DESC ;
SELECT student_inst_number
FROM igs_he_st_spa
WHERE person_id = p_person_id
AND course_cd = cp_course_cd ;
SELECT a.award_cd,
a.award_grade ,
c.map1,
a.grading_schema_cd
FROM igs_en_spa_awd_aim a ,
igs_he_code_map_val c
WHERE a.person_id = p_person_id
AND a.course_cd = p_course_cd
AND a.conferral_date BETWEEN p_enrl_start_dt and p_enrl_end_dt
AND a.complete_ind ='Y'
AND a.award_cd = c.map2
AND c.association_code = 'OSS_HESA_AWD_ASSOC'
ORDER BY c.map1 ASC;
smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_get_feq to select open program awards only.
smaddali 29-jan-04 Bug#3360646 modified cursor c_get_feq to remove condition default_ind=Y
***************************************************************/
CURSOR c_get_feq IS
SELECT map1
FROM igs_ps_award , igs_he_code_map_val
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND closed_ind = 'N'
AND map2 = award_cd
AND association_code = 'OSS_HESA_FEQAIM_ASSOC'
ORDER BY default_ind DESC , map1 ASC;
SELECT b.govt_field_of_study,
a.percentage
FROM igs_ps_field_study a,
igs_ps_fld_of_study b
WHERE a.course_cd = p_course_cd
AND a.version_number = p_version_number
AND a.major_field_ind = 'Y'
AND a.field_of_study = b.field_of_study;
SELECT b.govt_field_of_study,
a.percentage
FROM igs_ps_field_study a,
igs_ps_fld_of_study b
WHERE a.course_cd = p_course_cd
AND a.version_number = p_version_number
AND a.major_field_ind = 'N'
AND a.field_of_study = b.field_of_study
ORDER BY percentage DESC ;
smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_prgawd to select open program awards only.
smaddali 21-jan-04 Modified cursor c_prgawd and c_spawd for bug#3360646
***************************************************************/
--smaddali modified this cursor for bug 2392702
-- smaddali Modified for bug#3360646, to check for dates overlapping with submission period
-- and to exclude awards which are conferred before the submission start_date
CURSOR c_spawd IS
SELECT map1
FROM igs_en_spa_awd_aim , igs_he_code_map_val
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND start_dt <= NVL(p_enrl_end_dt,start_dt)
AND ( end_dt IS NULL OR end_dt >= NVL(p_enrl_start_dt,end_dt) )
AND ( (complete_ind = 'Y' AND conferral_date >= NVL(p_awd_conf_start_dt,conferral_date) ) OR
complete_ind = 'N'
)
AND map2 = award_cd
AND association_code = 'OSS_HESA_AWD_ASSOC'
ORDER BY map1 ASC ;
SELECT map1
FROM igs_ps_award , igs_he_code_map_val
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND closed_ind = 'N'
AND map2 = award_cd
AND association_code = 'OSS_HESA_AWD_ASSOC'
ORDER BY default_ind DESC, map1 ASC ;
SELECT ihpinst.oi_govt_institution_cd govt_institution_cd
FROM igs_ps_awd_own a,
igs_pe_hz_parties ihpou,
igs_pe_hz_parties ihpinst
WHERE a.course_cd = p_course_cd
AND a.version_number = p_version_number
AND a.award_cd = p_award_cd
AND a.org_unit_cd = ihpou.oss_org_unit_cd
AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
GROUP BY ihpinst.oi_govt_institution_cd
ORDER BY SUM(a.percentage) DESC;
anwest 09-Dec-03 Modified for HE356 - updated to accommodate
Term/Load calendar associated with residency
jtmathew 23-May-06 Modified c_res_sts for bug 5210481
***************************************************************/
-- smaddali modified this cursor for bug 2367167 to get records which are effective in the HESA submission period, bug#3235753
-- anwest modified this cursor for HE356 to accommodate Term/Load Calendar for Residency Status
-- jtmathew modified this cursor for bug 5210481 to remove igs_pe_res_dtls_v view
CURSOR c_res_sts (cp_res_class_cd VARCHAR2) IS
SELECT peresdtls.residency_status_cd residency_status
FROM igs_pe_res_dtls_all peresdtls,
igs_lookup_values cc1,
igs_lookup_values cc2,
igs_ca_inst_all cainstall
WHERE peresdtls.person_id = p_person_id
AND peresdtls.residency_class_cd = cc1.lookup_code
AND cc1.lookup_type = 'PE_RES_CLASS'
AND peresdtls.residency_status_cd = cc2.lookup_code
AND cc2.lookup_type = 'PE_RES_STATUS'
AND peresdtls.residency_class_cd = cp_res_class_cd
AND peresdtls.cal_type = cainstall.cal_type
AND peresdtls.sequence_number = cainstall.sequence_number
AND cainstall.start_dt <= p_enrl_end_dt
ORDER BY cainstall.start_dt DESC;
SELECT SUM(chg.transaction_amount) tuition_fees
FROM igs_fi_fee_type ft, igs_fi_fee_as chg
WHERE ft.s_fee_type = 'TUTNFEE'
AND chg.person_id = p_person_id
AND (chg.course_cd IS NULL OR chg.course_cd = p_course_cd)
AND chg.fee_type = ft.fee_type
AND (chg.effective_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt);
sjlaport 31-Jan-05 Modified cursor cur_slc_lea_cd for HE358 to ignore logically deleted records
jchakrab 02-Feb-06 Modified logic such that a value 98 is derived when tution fee amount=0
***************************************************************/
--Query to get all Sponsor records for student within the given HESA reporting period.
CURSOR cur_spnsr IS
SELECT SUM (a.disb_net_amt) sponsor_amount,
d.map1 sponsor
FROM igf_aw_awd_disb a,
igf_aw_award b,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec c,
igs_he_code_map_val d,
igs_ca_inst ca
WHERE a.award_id = b.award_id
AND b.fund_id = fmast.fund_id
AND b.base_id = c.base_id
AND a.ld_cal_type = ca.cal_type
AND a.ld_sequence_number = ca.sequence_number
AND d.association_code = 'OSS_SPONSOR_MSTUFEE_ASSOC'
AND d.map2 = fmast.fund_code
AND c.person_id = p_person_id
AND a.fee_class = 'TUITION'
AND a.trans_type IN ('A' , 'P')
AND b.award_status IN ('ACCEPTED','OFFERED')
AND ca.start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
GROUP BY d.map1
ORDER BY sponsor_amount DESC, sponsor ASC;
SELECT SUM (a.disb_net_amt) sponsor_amount,
fmast.fund_code
FROM igf_aw_awd_disb a,
igf_aw_award b,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec c,
igs_he_code_values d,
igs_ca_inst ca
WHERE a.award_id = b.award_id
AND b.fund_id = fmast.fund_id
AND b.base_id = c.base_id
AND a.ld_cal_type = ca.cal_type
AND a.ld_sequence_number = ca.sequence_number
AND d.code_type = 'OSS_SLC_SPONSOR'
AND d.value = fmast.fund_code
AND NVL(d.closed_ind, 'N') = 'N'
AND c.person_id = p_person_id
AND a.fee_class = 'TUITION'
AND a.trans_type IN ('A' , 'P')
AND ca.start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
GROUP BY fmast.fund_code;
SELECT SUBSTR (api.api_person_id,1,4)
FROM igs_pe_alt_pers_id_v api,
igs_pe_person_id_typ pit
WHERE api.person_id_type = pit.Person_id_type
AND pit.s_person_id_type = 'SLC'
AND api.pe_person_id = p_person_id
AND NVL (api.start_dt, p_enrl_end_dt ) <= p_enrl_end_dt
AND NVL (api.end_dt, p_enrl_start_dt ) >= p_enrl_start_dt
AND (api.end_dt IS NULL OR api.start_dt <> api.end_dt)
ORDER BY api.start_dt DESC;
SELECT DISTINCT a.ld_cal_type, a.ld_sequence_number
FROM igf_aw_awd_disb a,
igf_aw_award b,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec c,
igs_he_code_values d,
igs_ca_inst ca
WHERE a.award_id = b.award_id
AND b.fund_id = fmast.fund_id
AND b.base_id = c.base_id
AND a.ld_cal_type = ca.cal_type
AND a.ld_sequence_number = ca.sequence_number
AND d.code_type = 'OSS_SLC_SPONSOR'
AND d.value = fmast.fund_code
AND NVL(d.closed_ind, 'N') = 'N'
AND c.person_id = p_person_id
AND a.fee_class = 'TUITION'
AND a.trans_type IN ('A' , 'P')
AND ca.start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt;
SELECT NVL(SUM(inv.invoice_amount),0) invoice_amount
FROM igs_fi_inv_int inv,
igs_fi_fee_type ft
WHERE inv.fee_type = ft.fee_type
AND person_id = p_person_id
AND inv.fee_cal_type = cp_fee_cal_type
AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND ft.s_fee_type = 'TUTNFEE';
SELECT ihp.oi_govt_institution_cd govt_institution_cd
FROM igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = p_inst_cd;
SELECT ihpinst.oi_govt_institution_cd govt_institution_cd
FROM igs_en_su_attempt a,
igs_ps_tch_resp b,
igs_pe_hz_parties ihpou,
igs_pe_hz_parties ihpinst,
igs_he_st_unt_vs_all hunt
WHERE a.course_cd = p_course_cd
AND a.person_id = p_person_id
AND b.unit_cd = a.unit_cd
AND b.version_number = a.version_number
AND a.unit_cd = hunt.unit_cd (+)
AND a.version_number = hunt.version_number (+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
AND b.org_unit_cd = ihpou.oss_org_unit_cd
AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
AND NVL(ihpinst.oi_local_institution_ind,'N') = 'N'
ORDER BY b.percentage DESC;
SELECT SUM( NVL(override_enrolled_cp,NVL(cps.enrolled_credit_points,e.enrolled_credit_points)) * b.percentage / 100 ) other_inst_cp
FROM igs_en_su_attempt a,
igs_ps_tch_resp b,
igs_pe_hz_parties ihpou,
igs_pe_hz_parties ihpinst,
igs_ps_unit_ver e,
igs_ps_usec_cps cps,
igs_he_st_unt_vs_all hunt
WHERE a.course_cd = p_course_cd
AND a.person_id = p_person_id
AND b.unit_cd = a.unit_cd
AND b.version_number = a.version_number
AND b.org_unit_cd = ihpou.oss_org_unit_cd
AND a.unit_cd = hunt.unit_cd (+)
AND a.version_number = hunt.version_number (+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
AND NVL(ihpinst.oi_local_institution_ind,'N') = 'N'
AND e.unit_cd = a.unit_cd
AND e.version_number = a.version_number
AND a.uoo_id = cps.uoo_id(+)
AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt ;
SELECT SUM( NVL(override_enrolled_cp,NVL(cps.enrolled_credit_points,e.enrolled_credit_points))) Total_credit_points
FROM igs_en_su_attempt a,
igs_ps_unit_ver e,
igs_ps_usec_cps cps,
igs_he_st_unt_vs_all hunt
WHERE a.course_cd = p_course_cd
AND a.person_id = p_person_id
AND a.unit_cd = hunt.unit_cd (+)
AND a.version_number = hunt.version_number (+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND e.unit_cd = a.unit_cd
AND e.version_number = a.version_number
AND a.uoo_id = cps.uoo_id(+)
AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt ;
SELECT SUM( NVL(override_enrolled_cp,NVL(cps.enrolled_credit_points,b.enrolled_credit_points)) ) credit_points, b.unit_level
FROM igs_en_su_attempt a ,
igs_ps_unit_ver b,
igs_ps_usec_cps cps,
igs_he_st_unt_vs_all hunt
WHERE a.course_cd = p_course_cd
AND a.person_id = p_person_id
AND a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND a.unit_cd = hunt.unit_cd (+)
AND a.version_number = hunt.version_number (+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND a.uoo_id = cps.uoo_id(+)
AND a.unit_attempt_status = 'COMPLETED'
AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
GROUP BY b.unit_level
ORDER BY credit_points DESC;
SELECT govt_field_of_study
FROM IGS_PS_FLD_OF_STUDY PFS
WHERE field_of_study = p_subject;
SELECT 'X'
FROM IGS_HE_EN_SUSA_CC
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND unit_set_cd = cp_unit_set_cd
AND sequence_number = cp_sequence_number;
SELECT 'X'
FROM IGS_HE_ST_SPA_CC
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd;
SELECT 'X'
FROM IGS_HE_POOUS_OU_CC pocc,
IGS_HE_POOUS_OU_ALL poou
WHERE poou.hesa_poous_ou_id = pocc.hesa_poous_ou_id
AND poou.course_cd = cp_course_cd
AND poou.crv_version_number = cp_crv_version_number
AND poou.unit_set_cd = cp_unit_set_cd
AND poou.us_version_number = cp_us_version_number
AND poou.cal_type = cp_cal_type
AND poou.attendance_mode = cp_attendance_mode
AND poou.attendance_type = cp_attendance_type
AND poou.location_cd = cp_location_cd;
SELECT 'X'
FROM
igs_he_ou_cc org,
igs_he_poous_ou_all poo
WHERE
org.org_unit_cd = poo.organization_unit
AND poo.course_cd = p_course_cd
AND poo.crv_version_number = p_version_number
AND poo.unit_set_cd = p_unit_set_cd
AND poo.us_version_number = p_us_version_number
AND poo.cal_type = p_cal_type
AND poo.attendance_mode = p_attendance_mode
AND poo.attendance_type = p_attendance_type
AND poo.location_cd = p_location_cd;
SELECT 'X'
FROM
IGS_HE_PROG_OU_CC pcc,
IGS_PS_OWN pown
WHERE
pcc.course_cd = p_course_cd
AND pcc.version_number = p_version_number
AND pcc.course_cd = pown.course_cd
AND pcc.version_number = pown.version_number
AND pcc.org_unit_cd = pown.org_unit_cd;
SELECT 'X'
FROM
igs_he_ou_cc org,
igs_ps_own ps
WHERE
org.org_unit_cd = ps.org_unit_cd
AND ps.course_cd = p_course_cd
AND ps.version_number = p_version_number;
SELECT 'X'
FROM igs_he_unt_ou_cc a,
igs_en_su_attempt_all b,
igs_ps_tch_resp c,
igs_he_st_unt_vs_all hunt
WHERE b.course_cd = p_course_cd
AND b.person_id = p_person_id
AND a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND b.unit_cd = hunt.unit_cd (+)
AND b.version_number = hunt.version_number (+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND b.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND c.unit_cd = a.unit_cd
AND c.version_number = a.version_number
AND c.org_unit_cd = a.org_unit_cd
AND b.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt;
SELECT 'X'
FROM
igs_he_ou_cc org,
igs_ps_tch_resp ps,
igs_en_su_attempt_all su
WHERE
org.org_unit_cd = ps.org_unit_cd
AND ps.unit_cd = su.unit_cd
AND ps.version_number = su.version_number
AND su.person_id = p_person_id
AND su.course_cd = p_course_cd
AND su.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND su.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt;
SELECT 'X'
FROM igs_he_unt_ou_cc ucc,
igs_ps_tch_resp ptr
WHERE ucc.unit_cd = p_unit_cd
AND ucc.version_number = p_uv_version_number
AND ucc.unit_cd = ptr.unit_cd
AND ucc.version_number = ptr.version_number
AND ucc.org_unit_cd = ptr.org_unit_cd;
SELECT 'X'
FROM igs_he_ou_cc org,
igs_ps_tch_resp ptr
WHERE ptr.unit_cd = p_unit_cd
AND ptr.version_number = p_uv_version_number
AND ptr.org_unit_cd = org.org_unit_cd;
SELECT cost_centre, subject, proportion
FROM igs_he_en_susa_cc
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_set_cd = p_unit_set_cd
AND sequence_number = p_sequence_number
ORDER BY proportion DESC;
SELECT cost_centre, subject, proportion
FROM igs_he_st_spa_cc
WHERE person_id = p_person_id
AND course_cd = p_course_cd
ORDER BY proportion DESC;
SELECT cost_centre, subject, SUM (pocc.proportion*NVL(poou.proportion,0)/100) proportion
FROM igs_he_poous_ou_cc pocc,
igs_he_poous_ou_all poou
WHERE poou.hesa_poous_ou_id = pocc.hesa_poous_ou_id
AND poou.course_cd = p_course_cd
AND poou.crv_version_number = p_version_number
AND poou.unit_set_cd = p_unit_set_cd
AND poou.us_version_number = p_us_version_number
AND poou.cal_type = p_cal_type
AND poou.attendance_mode = p_attendance_mode
AND poou.attendance_type = p_attendance_type
AND poou.location_cd = p_location_cd
GROUP BY cost_centre, subject
ORDER BY proportion DESC;
SELECT org.cost_centre,
org.subject,
SUM((NVL(poo.proportion,0) * org.proportion / 100)) proportion
FROM igs_he_ou_cc org,
igs_he_poous_ou_all poo
WHERE org.org_unit_cd = poo.organization_unit
AND poo.course_cd = p_course_cd
AND poo.crv_version_number = p_version_number
AND poo.unit_set_cd = p_unit_set_cd
AND poo.us_version_number = p_us_version_number
AND poo.cal_type = p_cal_type
AND poo.attendance_mode = p_attendance_mode
AND poo.attendance_type = p_attendance_type
AND poo.location_cd = p_location_cd
GROUP BY org.cost_centre, org.subject
ORDER BY proportion DESC;
SELECT cost_centre, subject, SUM(proportion*percentage/100) proportion
FROM igs_he_prog_ou_cc pcc,
igs_ps_own pown
WHERE pcc.course_cd = p_course_cd
AND pcc.version_number = p_version_number
AND pcc.course_cd = pown.course_cd
AND pcc.version_number = pown.version_number
AND pcc.org_unit_cd = pown.org_unit_cd
GROUP BY cost_centre, subject
ORDER BY proportion DESC;
SELECT org.cost_centre,
org.subject,
SUM(ps.percentage * org.proportion / 100) proportion
FROM igs_he_ou_cc org,
igs_ps_own ps
WHERE org.org_unit_cd = ps.org_unit_cd
AND ps.course_cd = p_course_cd
AND ps.version_number = p_version_number
GROUP BY org.cost_centre, org.subject
ORDER BY proportion DESC;
SELECT a.cost_centre,
a.subject,
SUM(proportion*percentage/100) proportion
FROM igs_he_unt_ou_cc a,
igs_en_su_attempt_all b,
igs_ps_tch_resp c,
igs_he_st_unt_vs_all hunt
WHERE b.course_cd = p_course_cd
AND b.person_id = p_person_id
AND a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND c.unit_cd = a.unit_cd
AND c.version_number = a.version_number
AND c.org_unit_cd = a.org_unit_cd
AND b.unit_cd = hunt.unit_cd(+)
AND b.version_number = hunt.version_number(+)
AND NVL(hunt.exclude_flag, 'N') = 'N'
AND b.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND b.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
GROUP BY a.cost_centre, a.subject
ORDER BY proportion DESC;
SELECT org.cost_centre,
org.subject,
SUM(ps.percentage * org.proportion / 100) proportion
FROM igs_he_ou_cc org,
igs_ps_tch_resp ps,
igs_en_su_attempt_all su
WHERE org.org_unit_cd = ps.org_unit_cd
AND ps.unit_cd = su.unit_cd
AND ps.version_number = su.version_number
AND su.person_id = p_person_id
AND su.course_cd = p_course_cd
AND su.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND su.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
GROUP BY org.cost_centre, org.subject
ORDER BY proportion DESC;
SELECT cost_centre,
subject,
SUM(proportion*percentage/100) proportion
FROM igs_he_unt_ou_cc ucc,
igs_ps_tch_resp ptr
WHERE ucc.unit_cd = p_unit_cd
AND ucc.version_number = p_uv_version_number
AND ucc.unit_cd = ptr.unit_cd
AND ucc.version_number = ptr.version_number
AND ucc.org_unit_cd = ptr.org_unit_cd
GROUP BY cost_centre, subject
ORDER BY proportion DESC;
SELECT cost_centre,
subject,
SUM(proportion*percentage/100) proportion
FROM igs_he_ou_cc org,
igs_ps_tch_resp ptr
WHERE ptr.unit_cd = p_unit_cd
AND ptr.version_number = p_uv_version_number
AND ptr.org_unit_cd = org.org_unit_cd
GROUP BY cost_centre, subject
ORDER BY proportion DESC;
rnirwani 13-09-04 Changed the cursor, c_susp to exclude logically deleted intermission
records as per Bug# 3885804
jbaber 30-11-04 Consider mutiple intermission records for bug# 4037237
Now calls isDormant function
***************************************************************/
CURSOR c_susp IS
SELECT start_dt,
end_dt
FROM igs_en_stdnt_ps_intm spi
WHERE spi.person_id = p_person_id
AND spi.course_cd = p_course_cd
AND spi.start_dt <= p_enrl_end_dt
AND spi.end_dt >= p_enrl_end_dt
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND (spi.approved = 'Y' OR
EXISTS( SELECT 1 FROM igs_en_intm_types
WHERE intermission_type = spi.intermission_type AND
appr_reqd_ind = 'N' ));
SELECT commencement_dt,
discontinued_dt,
course_rqrmnts_complete_dt
FROM igs_en_stdnt_ps_att_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_version_number;
SELECT progression_outcome_type,ca.start_dt , spo.decision_dt, spo.applied_dt
FROM IGS_PR_STDNT_PR_OU spo,
IGS_CA_INST ca
WHERE spo.person_id = cp_person_id AND
spo.course_cd = cp_course_cd AND
-- Select Approved Progression Outcomes
spo.decision_status = 'APPROVED' AND
-- Select Progression Outcome records that overlap the HESA reporting period
spo.prg_cal_type = ca. cal_type AND
spo.prg_ci_sequence_number = ca.sequence_number AND
ca.start_dt <= cp_enrl_end_dt AND
ca.end_dt >= cp_enrl_start_dt
-- If multiple outcome types records exist overlapping the HESA reporting period then use the progression
-- record with latest progression calendar.
-- If there are multiple approved outcome types for the same Period that have been applied then
-- use the one with the latest Applied date and
-- If multiple approved outcome types exist for the same Period that have been applied and
-- have the same applied Applied date use the one with the latest Decision Date
ORDER BY ca.start_dt DESC, spo.applied_dt DESC, spo.decision_dt DESC;
SELECT institution_code,
NVL(end_date, TO_DATE('01-01-1500', 'DD-MM-YYYY')) end_date_al,
creation_date
FROM igs_ad_acad_history_v
WHERE person_id = p_person_id
AND (end_date IS NULL OR end_date < p_com_date)
ORDER BY end_date_al DESC, creation_date DESC;
SELECT org_alternate_id
FROM igs_or_org_alt_ids oai,igs_or_org_alt_idtyp oait
WHERE oai.org_structure_id = p_inst_cd
AND oai.org_structure_type = 'INSTITUTE'
AND oai.org_alternate_id_type = oait.org_alternate_id_type
AND oait.system_id_type = 'HESA_INST'
AND ( oai.start_date <= p_enrl_end_dt
AND
(oai.end_date IS NULL OR oai.end_date >=p_enrl_start_dt)
)
ORDER BY oai.start_date DESC;
SELECT ihp.oi_govt_institution_cd govt_institution_cd
FROM igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = p_inst_cd;
SELECT reference_cd
FROM igs_ps_ref_cd
WHERE reference_cd_type = 'REGBODY'
AND course_cd = p_course_cd
AND version_number = p_version_number;
SELECT SUM(number_of_qual),
SUM(tariff_score)
FROM igs_he_st_spa_ut a,
igs_he_ut_lvl_award b,
igs_he_ut_calc_type c
WHERE a.person_id = p_person_id
AND a.course_cd = p_course_cd
AND b.Tariff_calc_type_cd = c.Tariff_calc_type_cd
AND a.qualification_level = b.award_cd
AND b.tariff_level_cd = p_hesa_qual
AND b.closed_ind = 'N'
AND c.External_calc_ind = 'Y'
And c.closed_ind = 'N';
SELECT DISTINCT a.unit_cd,
a.version_number,
d.prop_of_teaching_in_welsh,
NVL(cps.enrolled_credit_points,b.enrolled_credit_points) enrolled_credit_point
FROM igs_en_su_attempt_all a,
igs_ps_unit_ver_all b,
igs_he_st_spa_all c,
igs_he_st_unt_vs_all d,
igs_ps_usec_cps cps
WHERE a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND c.person_id = a.person_id
AND c.course_cd = a.course_cd
AND c.student_inst_number = p_student_inst_number
AND b.unit_cd = d.unit_cd
AND b.version_number = d.version_number
AND a.person_id = p_person_id
AND a.uoo_id = cps.uoo_id(+)
AND NVL(d.exclude_flag, 'N') = 'N'
AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
UNION
SELECT DISTINCT a.unit_cd,
a.version_number,
d.prop_of_teaching_in_welsh,
NVL(cps.enrolled_credit_points,b.enrolled_credit_points) enrolled_credit_point
FROM igs_en_su_attempt_all a,
igs_ps_unit_ver_all b,
igs_he_st_spa_all c,
igs_he_st_unt_vs_all d,
igs_as_su_stmptout e,
igs_ps_usec_cps cps
WHERE a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND c.person_id = a.person_id
AND c.course_cd = a.course_cd
AND c.student_inst_number = p_student_inst_number
AND b.unit_cd = d.unit_cd
AND b.version_number = d.version_number
AND a.person_id = p_person_id
AND e.person_id = a.person_id
AND e.course_cd = a.course_cd
AND e.uoo_id = a.uoo_id
AND a.uoo_id = cps.uoo_id(+)
AND e.outcome_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
AND e.finalised_outcome_ind = 'Y'
AND NVL(d.exclude_flag, 'N') = 'N'
AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
ORDER BY enrolled_credit_point DESC;
SELECT sua.course_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.ci_end_dt,
sua.unit_attempt_status,
sua.no_assessment_ind,
suo.outcome_dt,
sua.uoo_id
FROM igs_as_su_stmptout suo,
igs_en_su_attempt_all sua,
igs_he_st_spa_all spa
WHERE suo.person_id = sua.person_id
AND suo.course_cd = sua.course_cd
AND suo.uoo_id = sua.uoo_id
AND sua.person_id = spa.person_id
AND sua.course_cd = spa.course_cd
AND spa.student_inst_number = p_student_inst_number
AND suo.outcome_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
AND suo.unit_cd = p_unit_cd
AND suo.person_id = p_person_id
AND suo.finalised_outcome_ind = 'Y'
AND sua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
ORDER BY suo.outcome_dt DESC ;
rnirwani 13-09-04 Changed the cursor, c_drm to exclude logically deleted intermission
records as per Bug# 3885804
jbaber 30-11-04 Consider mutiple intermission records for bug# 4037237
Now calls isDormant function
jbaber 26-01-06 Check if student is dormant even if source is susa or poous
***************************************************************/
l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
SELECT commencement_dt,
discontinued_dt,
course_rqrmnts_complete_dt
FROM igs_en_stdnt_ps_att_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_version_number;
SELECT start_dt,
end_dt
FROM igs_en_stdnt_ps_intm spi
WHERE spi.person_id = p_person_id
AND spi.course_cd = p_course_cd
AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
AND spi.start_dt<= cp_end_dt
AND NVL(spi.end_dt,cp_end_dt) >= cp_start_dt
AND (spi.approved = 'Y' OR
EXISTS( SELECT 1 FROM igs_en_intm_types
WHERE intermission_type = spi.intermission_type AND
appr_reqd_ind = 'N' ))
ORDER BY start_dt;
SELECT SUM(percentage)
FROM igs_ps_tch_resp a,
igs_pe_hz_parties ihpou,
igs_pe_hz_parties ihpinst
WHERE a.unit_cd = p_unit_cd
AND a.version_number = p_version_number
AND a.org_unit_cd = ihpou.oss_org_unit_cd
AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
AND NVL(ihpinst.oi_local_institution_ind,'N') = 'N' ;
SELECT SUM(percentage) percentage,
ihpinst.oi_govt_institution_cd govt_institution_cd
FROM igs_ps_tch_resp a,
igs_pe_hz_parties ihpou,
igs_pe_hz_parties ihpinst
WHERE a.unit_cd = p_unit_cd
AND a.version_number = p_version_number
AND a.org_unit_cd = ihpou.oss_org_unit_cd
AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
AND NVL(ihpinst.oi_local_institution_ind,'N') = 'N'
GROUP BY ihpinst.oi_govt_institution_cd
ORDER BY percentage DESC;
SELECT a.approved_result, a.grading_schema_cd
FROM igs_uc_qual_dets a
WHERE a.person_id = p_person_id
AND EXISTS (SELECT 'X'
FROM igs_he_code_values b
WHERE b.value = a.exam_level
AND b.code_type = 'OSS_QUAL_1ST_DEGREE'
AND NVL(b.closed_ind,'N') = 'N' )
ORDER BY a.year DESC;
SELECT MIN(sca.commencement_dt) commencement_dt
FROM igs_en_stdnt_ps_att_all sca,
igs_he_st_spa_all hspa
WHERE hspa.person_id = p_person_id
AND hspa.student_inst_number = p_student_inst_number
AND sca.person_id = hspa.person_id
AND sca.course_cd = hspa.course_cd;
SELECT map1
FROM igs_he_code_map_val a, igs_pe_pers_disablty b
WHERE a.map2 = b.disability_type
AND a.association_code = 'OSS_HESA_LEARNDIF_ASSOC'
AND NOT NVL(b.start_date, p_enrl_end_dt) > p_enrl_end_dt
AND NOT NVL(b.end_date, p_enrl_start_dt) < p_enrl_start_dt
AND b.person_id = p_person_id
GROUP BY map1;
SELECT count(*)
FROM
igs_en_su_attempt_all su, igs_as_su_stmptout_all suo1,
igs_he_st_spa spa1, igs_he_st_spa spa2, igs_as_grd_sch_grade gsg
WHERE su.person_id = suo1.person_id
AND su.course_cd = suo1.course_cd
AND su.unit_cd = suo1.unit_cd
AND su.unit_attempt_status = 'COMPLETED'
AND su.person_id = spa2.person_id
AND su.course_cd = spa2.course_cd
AND suo1.outcome_dt <= p_enrl_end_dt
AND spa1.person_id = spa2.person_id
AND spa1.student_inst_number = spa2.student_inst_number
AND spa1.person_id = p_person_id
AND spa1.course_cd = p_course_cd
AND gsg.s_result_type = 'PASS'
AND gsg.grading_schema_cd = suo1.grading_schema_cd
AND gsg.grade = suo1.grade
AND gsg.version_number = suo1.version_number
AND suo1.outcome_dt IN
(SELECT MAX(suo2.outcome_dt)
FROM igs_as_su_stmptout_all suo2
WHERE suo2.person_id = suo1.person_id
AND suo2.course_cd = suo1.course_cd
AND suo2.finalised_outcome_ind = 'Y'
AND suo2.unit_cd = suo1.unit_cd);
SELECT speaks_level
FROM igs_pe_languages_v
WHERE party_id = p_person_id
AND language_name = 'WS';
SELECT race_cd
FROM igs_pe_race
WHERE person_id = p_person_id;
SELECT 'X'
FROM igs_uc_qual_dets iuqd,
igs_he_code_map_val ihcmv
WHERE iuqd.person_id = p_person_id
AND ihcmv.association_code = 'UCAS_OSS_AWD_ASSOC'
AND ihcmv.map1 = 'WB'
AND ihcmv.map2 = iuqd.exam_level;
SELECT 'X'
FROM igs_uc_qual_dets iuqd,
igs_he_code_map_val ihcmv,
igs_as_grd_sch_grade iagsg
WHERE iuqd.person_id = p_person_id
AND iuqd.exam_level = ihcmv.map2
AND iuqd.grading_schema_cd = iagsg.grading_schema_cd
AND iuqd.version_number = iagsg.version_number
AND iuqd.approved_result = iagsg.grade
AND iagsg.s_result_type = 'PASS'
AND ihcmv.association_code = 'UCAS_OSS_AWD_ASSOC'
AND ihcmv.map1 = 'WB';