[Home] [Help]
74: Change History :
75: Who When What
76: ***************************************************************/
77:
78: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
79:
80: BEGIN
81: l_he_code_map_val.association_code := 'OSS_HESA_LOC_CAMP_ASSOC';
82: l_he_code_map_val.map2 := p_location_cd;
645: Change History :
646: Who When What
647: ***************************************************************/
648:
649: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
650: l_oss_fe_stdnt_mrker igs_he_code_map_val.map1%TYPE;
651:
652: BEGIN
653:
646: Who When What
647: ***************************************************************/
648:
649: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
650: l_oss_fe_stdnt_mrker igs_he_code_map_val.map1%TYPE;
651:
652: BEGIN
653:
654: IF p_spa_fe_stdnt_mrker IS NULL
718: jtmathew 24-Mar-05 Modified procedure to check HESA code mapping first before
719: calling c_gov_fsrc for bug #4218148
720: ***************************************************************/
721:
722: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
723: l_oss_fund_src igs_he_code_map_val.map1%TYPE;
724: l_hesa_fund_src igs_he_ex_rn_dat_fd.value%TYPE;
725: l_govt_fund_src igs_he_ex_rn_dat_fd.value%TYPE;
726:
719: calling c_gov_fsrc for bug #4218148
720: ***************************************************************/
721:
722: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
723: l_oss_fund_src igs_he_code_map_val.map1%TYPE;
724: l_hesa_fund_src igs_he_ex_rn_dat_fd.value%TYPE;
725: l_govt_fund_src igs_he_ex_rn_dat_fd.value%TYPE;
726:
727: CURSOR c_fsrc IS
867: AND NVL(hecodeval.closed_ind,'N') = 'N' ;
868:
869: l_residency_status igs_pe_res_dtls_v.residency_status%TYPE;
870: l_oss_fund_cd VARCHAR2(30);
871: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
872: l_res_class_cd igs_pe_res_dtls_v.residency_class%TYPE;
873: l_dummy VARCHAR2(1);
874:
875: BEGIN
1032: Change History :
1033: Who When What
1034: ***************************************************************/
1035:
1036: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1037:
1038: BEGIN
1039: l_he_code_map_val.association_code := 'UC_OSS_HE_GEN_ASSOC';
1040: l_he_code_map_val.map2 := p_gender;
1072: Change History :
1073: Who When What
1074: ***************************************************************/
1075:
1076: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1077:
1078: BEGIN
1079: l_he_code_map_val.association_code := 'UC_OSS_HE_DOM_ASSOC';
1080: l_he_code_map_val.map2 := Nvl(p_spa_domicile, p_ad_domicile) ;
1118: -- smaddali modified this cursor to get replace system date with HESA submission end date, bug#3235753
1119: CURSOR c_ntnl (p_hesa_code VARCHAR2) IS
1120: SELECT b.map3
1121: FROM igs_pe_citizenship_v a,
1122: igs_he_code_map_val b
1123: WHERE a.party_id = p_person_id
1124: AND Nvl(End_Date, p_enrl_start_dt) >= p_enrl_start_dt
1125: AND b.map2 = a.country_code
1126: AND b.map3 = Nvl(p_hesa_code, b.map3)
1180: and remove its reference as part of HEFD311 - July 2004
1181: Changes enhancement bug, 2956444
1182: ***************************************************************/
1183:
1184: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1185:
1186: BEGIN
1187:
1188: IF p_oss_eth IS NOT NULL THEN
1221: Change History :
1222: Who When What
1223: ***************************************************************/
1224:
1225: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1226: BEGIN
1227:
1228: l_he_code_map_val.association_code := 'OSS_HESA_DIS_ALLOW_ASSOC';
1229: l_he_code_map_val.map2 := p_oss_dis_allow;
1277: AND ( Start_Date IS NULL OR Start_Date <= p_enrl_end_dt )
1278: AND ( End_Date IS NULL OR End_Date >= p_enrl_start_dt )
1279: ORDER BY Start_Date DESC;
1280:
1281: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1282: l_disability igs_pe_pers_disablty_v.disability_type%TYPE;
1283: l_hesa_disability igs_he_ex_rn_dat_fd.value%TYPE;
1284:
1285: BEGIN
1347: Change History :
1348: Who When What
1349: ***************************************************************/
1350:
1351: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1352: BEGIN
1353:
1354: l_he_code_map_val.association_code := 'OSS_HESA_SUP_BAND_ASSOC';
1355: l_he_code_map_val.map2 := p_oss_supp_band;
1449: Change History :
1450: Who When What
1451: ***************************************************************/
1452:
1453: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1454: BEGIN
1455:
1456: -- IF p_fe_stdnt_mrker IS NOT NULL
1457: -- Amended for Bug 2353094
1558: Change History :
1559: Who When What
1560: ***************************************************************/
1561:
1562: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1563:
1564: BEGIN
1565: l_he_code_map_val.association_code := 'OSS_HESA_SPEC_STUD_ASSOC';
1566: l_he_code_map_val.map2 := Nvl(p_spa_special_student, p_ad_special_student) ;
1706: Change History :
1707: Who When What
1708: ***************************************************************/
1709:
1710: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1711:
1712: BEGIN
1713: l_he_code_map_val.association_code := 'OSS_HESA_LOCSDY_ASSOC';
1714: l_he_code_map_val.map2 := Nvl(Nvl(p_susa_study_location,
1784: AND a.active = 'A'
1785: ORDER BY c.Start_Date DESC;
1786:
1787:
1788: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1789: l_oss_term_time_acc igs_he_en_susa.term_time_accom%TYPE;
1790: l_address_usage igs_he_code_map_val.map1%TYPE;
1791:
1792: BEGIN
1786:
1787:
1788: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
1789: l_oss_term_time_acc igs_he_en_susa.term_time_accom%TYPE;
1790: l_address_usage igs_he_code_map_val.map1%TYPE;
1791:
1792: BEGIN
1793:
1794: IF p_susa_term_time_acc IS NOT NULL
2135:
2136:
2137:
2138:
2139: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2140: l_graduand_count NUMBER := 0;
2141:
2142: BEGIN
2143:
2212: FROM igs_he_st_spa
2213: WHERE person_id = p_person_id
2214: AND course_cd = cp_course_cd ;
2215:
2216: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2217: l_to_course_cd igs_he_st_spa.course_cd%TYPE := NULL ;
2218: l_from_sin igs_he_st_spa.student_inst_number%TYPE := NULL ;
2219: l_to_sin igs_he_st_spa.student_inst_number%TYPE := NULL ;
2220:
2308: Change History :
2309: Who When What
2310: ***************************************************************/
2311:
2312: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2313:
2314: BEGIN
2315: IF p_fe_stdnt_mrker IN ('1','3','4')
2316: THEN
2373: a.award_grade ,
2374: c.map1,
2375: a.grading_schema_cd
2376: FROM igs_en_spa_awd_aim a ,
2377: igs_he_code_map_val c
2378: WHERE a.person_id = p_person_id
2379: AND a.course_cd = p_course_cd
2380: AND a.conferral_date BETWEEN p_enrl_start_dt and p_enrl_end_dt
2381: AND a.complete_ind ='Y'
2383: AND c.association_code = 'OSS_HESA_AWD_ASSOC'
2384: ORDER BY c.map1 ASC;
2385:
2386:
2387: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2388: l_awd1 igs_en_spa_awd_aim.award_cd%TYPE;
2389: l_awd2 igs_en_spa_awd_aim.award_cd%TYPE;
2390: l_grade1 igs_en_spa_awd_aim.award_grade%TYPE;
2391: l_grade2 igs_en_spa_awd_aim.award_grade%TYPE;
2390: l_grade1 igs_en_spa_awd_aim.award_grade%TYPE;
2391: l_grade2 igs_en_spa_awd_aim.award_grade%TYPE;
2392: l_grading_schema1 igs_en_spa_awd_aim.grading_schema_cd%TYPE;
2393: l_grading_schema2 igs_en_spa_awd_aim.grading_schema_cd%TYPE;
2394: l_classification1 igs_he_code_map_val.map1%TYPE;
2395: l_classification2 igs_he_code_map_val.map1%TYPE;
2396:
2397: BEGIN
2398: -- smaddali removed code checking for graduand awards,
2391: l_grade2 igs_en_spa_awd_aim.award_grade%TYPE;
2392: l_grading_schema1 igs_en_spa_awd_aim.grading_schema_cd%TYPE;
2393: l_grading_schema2 igs_en_spa_awd_aim.grading_schema_cd%TYPE;
2394: l_classification1 igs_he_code_map_val.map1%TYPE;
2395: l_classification2 igs_he_code_map_val.map1%TYPE;
2396:
2397: BEGIN
2398: -- smaddali removed code checking for graduand awards,
2399: -- for HECR210 program completion validation build, bug#2874542
2496: ***************************************************************/
2497:
2498: CURSOR c_get_feq IS
2499: SELECT map1
2500: FROM igs_ps_award , igs_he_code_map_val
2501: WHERE course_cd = p_course_cd
2502: AND version_number = p_version_number
2503: AND closed_ind = 'N'
2504: AND map2 = award_cd
2504: AND map2 = award_cd
2505: AND association_code = 'OSS_HESA_FEQAIM_ASSOC'
2506: ORDER BY default_ind DESC , map1 ASC;
2507:
2508: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2509:
2510: BEGIN
2511:
2512: IF p_fe_stdnt_mrker IN ('1','3','4')
2561: Who When What
2562: Bayadav 25-OCT-02 Included new procedure as a part of HEFD101
2563: ***************************************************************/
2564:
2565: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2566: BEGIN
2567:
2568: IF p_qual_aim_subj1 IS NOT NULL
2569: AND p_qual_aim_subj2 IS NULL
2640: AND a.major_field_ind = 'N'
2641: AND a.field_of_study = b.field_of_study
2642: ORDER BY percentage DESC ;
2643:
2644: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2645: l_percentage1 igs_ps_field_study.percentage%TYPE;
2646: l_percentage2 igs_ps_field_study.percentage%TYPE;
2647: l_percentage3 igs_ps_field_study.percentage%TYPE;
2648:
2880: -- smaddali Modified for bug#3360646, to check for dates overlapping with submission period
2881: -- and to exclude awards which are conferred before the submission start_date
2882: CURSOR c_spawd IS
2883: SELECT map1
2884: FROM igs_en_spa_awd_aim , igs_he_code_map_val
2885: WHERE person_id = p_person_id
2886: AND course_cd = p_course_cd
2887: AND start_dt <= NVL(p_enrl_end_dt,start_dt)
2888: AND ( end_dt IS NULL OR end_dt >= NVL(p_enrl_start_dt,end_dt) )
2896: --smaddali modified this cursor for bug 2392702
2897: -- smaddali Modified for bug#3360646, to remove default_ind=Y check and add default_ind in order by clause
2898: CURSOR c_prgawd IS
2899: SELECT map1
2900: FROM igs_ps_award , igs_he_code_map_val
2901: WHERE course_cd = p_course_cd
2902: AND version_number = p_version_number
2903: AND closed_ind = 'N'
2904: AND map2 = award_cd
2904: AND map2 = award_cd
2905: AND association_code = 'OSS_HESA_AWD_ASSOC'
2906: ORDER BY default_ind DESC, map1 ASC ;
2907:
2908: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
2909: l_oss_gen_qaim igs_ps_award.award_cd%TYPE;
2910:
2911: BEGIN
2912:
2999: AND ihpou.institution_cd = ihpinst.oss_org_unit_cd
3000: GROUP BY ihpinst.oi_govt_institution_cd
3001: ORDER BY SUM(a.percentage) DESC;
3002:
3003: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3004: BEGIN
3005:
3006: OPEN c_awdbdy (p_awd1);
3007: FETCH c_awdbdy INTO p_awd_body1;
3126: Change History :
3127: Who When What
3128: ***************************************************************/
3129:
3130: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3131:
3132: BEGIN
3133: l_he_code_map_val.association_code := 'OSS_HESA_TTCID_ASSOC';
3134: l_he_code_map_val.map2 := Nvl(p_spa_ttcid, p_prg_ttcid) ;
3166: Change History :
3167: Who When What
3168: ***************************************************************/
3169:
3170: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3171:
3172: BEGIN
3173: l_he_code_map_val.association_code := 'OSS_HESA_ITTPHSC_ASSOC';
3174: l_he_code_map_val.map2 := Nvl(p_spa_itt_phsc, p_prg_itt_phsc) ;
3206: Change History :
3207: Who When What
3208: ***************************************************************/
3209:
3210: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3211:
3212: BEGIN
3213: l_he_code_map_val.association_code := 'OSS_HESA_BITTM_ASSOC';
3214: l_he_code_map_val.map2 := Nvl(p_spa_itt_mrker, p_prg_itt_mrker) ;
3246: Change History :
3247: Who When What
3248: ***************************************************************/
3249:
3250: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3251:
3252: BEGIN
3253: l_he_code_map_val.association_code := 'OSS_HESA_TQSEC_ASSOC';
3254: l_he_code_map_val.map2 := p_oss_teach_qual_sect;
3286: Change History :
3287: Who When What
3288: ***************************************************************/
3289:
3290: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3291:
3292: BEGIN
3293: l_he_code_map_val.association_code := 'OSS_HESA_TQSUB123_ASSOC';
3294: l_he_code_map_val.map2 := p_oss_teach_qual_sbj;
3357: AND cainstall.start_dt <= p_enrl_end_dt
3358: ORDER BY cainstall.start_dt DESC;
3359:
3360: l_residency_status igs_pe_res_dtls_v.residency_status%TYPE;
3361: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3362: l_oss_fee_elig igs_he_code_map_val.map1%TYPE;
3363: l_res_class_cd igs_pe_res_dtls_v.residency_class%TYPE;
3364:
3365: BEGIN
3358: ORDER BY cainstall.start_dt DESC;
3359:
3360: l_residency_status igs_pe_res_dtls_v.residency_status%TYPE;
3361: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3362: l_oss_fee_elig igs_he_code_map_val.map1%TYPE;
3363: l_res_class_cd igs_pe_res_dtls_v.residency_class%TYPE;
3364:
3365: BEGIN
3366:
3484: Who When What
3485: jtmathew 25-Jan-2006 Modifications for bug 4416467
3486: ***************************************************************/
3487:
3488: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3489:
3490: BEGIN
3491:
3492: IF p_hesa_fee_elig = '2' THEN
3563: AND (chg.course_cd IS NULL OR chg.course_cd = p_course_cd)
3564: AND chg.fee_type = ft.fee_type
3565: AND (chg.effective_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt);
3566:
3567: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3568: l_tuition_fees NUMBER;
3569:
3570: l_hesa_feprmk igs_he_code_map_val.map1%TYPE := NULL;
3571:
3566:
3567: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3568: l_tuition_fees NUMBER;
3569:
3570: l_hesa_feprmk igs_he_code_map_val.map1%TYPE := NULL;
3571:
3572: BEGIN
3573:
3574: OPEN c_fees;
3651: FROM igf_aw_awd_disb a,
3652: igf_aw_award b,
3653: igf_aw_fund_mast fmast,
3654: igf_ap_fa_base_rec c,
3655: igs_he_code_map_val d,
3656: igs_ca_inst ca
3657: WHERE a.award_id = b.award_id
3658: AND b.fund_id = fmast.fund_id
3659: AND b.base_id = c.base_id
3747: AND ft.s_fee_type = 'TUTNFEE';
3748:
3749: l_value igs_he_ex_rn_dat_fd.value%TYPE;
3750: l_slc_lea_cd igs_pe_alt_pers_id.api_person_id%TYPE;
3751: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
3752: l_invoice_amt igs_fi_inv_int.invoice_amount%TYPE;
3753: l_tot_invoice_amt igs_fi_inv_int.invoice_amount%TYPE;
3754: l_message_name fnd_new_messages.message_name%TYPE;
3755: l_fee_cal_type igs_ca_inst.cal_type%TYPE;
3917: Change History :
3918: Who When What
3919: ***************************************************************/
3920:
3921: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3922:
3923: BEGIN
3924: l_he_code_map_val.association_code := 'OSS_HESA_RELIG_ASSOC';
3925: l_he_code_map_val.map2 := p_oss_religion;
3958: Change History :
3959: Who When What
3960: ***************************************************************/
3961:
3962: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
3963:
3964: BEGIN
3965: IF p_fe_stdnt_mrker IN ('1','3','4')
3966: AND p_oss_sldd_disc_prv IS NOT NULL
4000: Change History :
4001: Who When What
4002: ***************************************************************/
4003:
4004: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4005:
4006: BEGIN
4007: l_he_code_map_val.association_code := 'OSS_HESA_NONPAY_ASSOC';
4008: l_he_code_map_val.map2 := p_oss_non_payment_rsn;
4084: AND NVL(ihpinst.oi_local_institution_ind,'N') = 'N'
4085: ORDER BY b.percentage DESC;
4086:
4087: l_un_inst c_un_inst%ROWTYPE := NULL ;
4088: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4089: l_oss_inst VARCHAR2(30);
4090:
4091: BEGIN
4092:
4216: AND a.uoo_id = cps.uoo_id(+)
4217: AND a.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
4218: AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt ;
4219:
4220: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4221: l_other_inst c_other_inst_cp%ROWTYPE ;
4222: l_total_cp c_total_cp%ROWTYPE ;
4223:
4224: BEGIN
4283: fmak 19-Jan-05 Modified association code reference from OSS_HESA_CRDSTCM_ASSOC
4284: to OSS_HESA_CRDTSCM_ASSOC for bug 3842077
4285: ***************************************************************/
4286:
4287: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4288:
4289: BEGIN
4290: -- fmak modified for bug 3842077
4291: l_he_code_map_val.association_code := 'OSS_HESA_CRDTSCM_ASSOC';
4326: Who When What
4327: smaddali Added extra parameter p_susa_credit_level for bug 2415879
4328: ***************************************************************/
4329:
4330: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4331:
4332: BEGIN
4333: l_he_code_map_val.association_code := 'OSS_HESA_LEVLCRD_ASSOC';
4334: l_he_code_map_val.map2 := NVL(p_susa_credit_level,p_poous_credit_level);
4411: AND a.ci_start_dt BETWEEN p_enrl_start_dt AND p_enrl_end_dt
4412: GROUP BY b.unit_level
4413: ORDER BY credit_points DESC;
4414:
4415: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4416: l_lvl1 igs_ps_unit_ver.unit_level%TYPE;
4417: l_lvl2 igs_ps_unit_ver.unit_level%TYPE;
4418: l_lvl3 igs_ps_unit_ver.unit_level%TYPE;
4419: l_lvl4 igs_ps_unit_ver.unit_level%TYPE;
4543:
4544: l_sql_stmt VARCHAR2(2000);
4545: l_index NUMBER;
4546: l_total_prop NUMBER := 0;
4547: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
4548: l_poous_cc igs_he_poous_ou_cc%ROWTYPE;
4549: l_max_recs NUMBER;
4550:
4551: TYPE cur_cc IS REF CURSOR;
5178: Change History :
5179: Who When What
5180: ***************************************************************/
5181:
5182: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5183:
5184: BEGIN
5185: l_he_code_map_val.association_code := 'OSS_HESA_TYPEYR_ASSOC';
5186: l_he_code_map_val.map2 := p_oss_pyr_type;
5218: Change History :
5219: Who When What
5220: ***************************************************************/
5221:
5222: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5223:
5224: BEGIN
5225: l_he_code_map_val.association_code := 'OSS_HESA_FUNDLEV_ASSOC';
5226: l_he_code_map_val.map2 := Nvl(p_poous_lvl_appl_fund,
5276: jtmathew 01-FEB-06 Modified procedure to return 1 if student completed requirements
5277: of program before or during the HESA reporting period
5278: ***************************************************************/
5279:
5280: l_he_code_map_val IGS_HE_CODE_MAP_VAL%ROWTYPE := NULL;
5281: l_progression_out_type IGS_PR_STDNT_PR_OU.progression_outcome_type%TYPE;
5282: l_prev_start_dt IGS_CA_INST.start_dt%TYPE;
5283: l_prev_applied_dt IGS_PR_STDNT_PR_OU.applied_dt%TYPE;
5284: l_prev_decision_dt IGS_PR_STDNT_PR_OU.decision_dt%TYPE;
5517: Change History :
5518: Who When What
5519: ***************************************************************/
5520:
5521: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5522:
5523: BEGIN
5524: l_he_code_map_val.association_code := 'OSS_HESA_DESTIN_ASSOC';
5525: l_he_code_map_val.map2 := p_oss_destination;
5558: Change History :
5559: Who When What
5560: ***************************************************************/
5561:
5562: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5563:
5564: BEGIN
5565:
5566: IF p_oss_itt_outcome IS NOT NULL
5598: Change History :
5599: Who When What
5600: ***************************************************************/
5601:
5602: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5603:
5604: BEGIN
5605: l_he_code_map_val.association_code := 'OSS_HESA_UFI_ASSOC';
5606: l_he_code_map_val.map2 := p_oss_ufi_place;
5640: Change History :
5641: Who When What
5642: ***************************************************************/
5643:
5644: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5645:
5646: BEGIN
5647: l_he_code_map_val.association_code := 'OSS_HESA_FRAN_ASSOC';
5648: l_he_code_map_val.map2 := Nvl(Nvl(p_susa_franch_activity,
5683: Change History :
5684: Who When What
5685: ***************************************************************/
5686:
5687: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5688:
5689: BEGIN
5690: l_he_code_map_val.association_code := 'UC_OSS_HE_SOC_ASSOC';
5691: l_he_code_map_val.map2 := Nvl(p_spa_social_class_ind,
5724: Change History :
5725: Who When What
5726: ***************************************************************/
5727:
5728: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5729:
5730: BEGIN
5731: l_he_code_map_val.association_code := 'UC_OSS_HE_OCC_ASSOC';
5732: l_he_code_map_val.map2 := p_spa_occupation_code;
5817: l_year DATE;
5818: l_creation_date DATE;
5819: l_org_alternate_id igs_or_org_alt_ids_v.org_alternate_id%TYPE;
5820: l_govt_institution_cd igs_pe_hz_parties.oi_govt_institution_cd%TYPE;
5821: l_he_code_map_val igs_he_code_map_val%ROWTYPE;
5822:
5823: BEGIN
5824:
5825: --Get the institute last attended from Academic History
5918: WHERE reference_cd_type = 'REGBODY'
5919: AND course_cd = p_course_cd
5920: AND version_number = p_version_number;
5921:
5922: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5923:
5924: BEGIN
5925: OPEN c_regbdy;
5926: FETCH c_regbdy INTO l_he_code_map_val.map2;
5962: Change History :
5963: Who When What
5964: ***************************************************************/
5965:
5966: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
5967:
5968: BEGIN
5969: l_he_code_map_val.association_code := 'OSS_HESA_NHS_FUND_ASSOC';
5970: l_he_code_map_val.map2 := Nvl(p_spa_nhs_fund_src,
6003: Change History :
6004: Who When What
6005: ***************************************************************/
6006:
6007: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6008:
6009: BEGIN
6010: l_he_code_map_val.association_code := 'OSS_HESA_NHS_EMPLOY_ASSOC';
6011: l_he_code_map_val.map2 := p_spa_nhs_employer;
6203: c_outdt_rec c_outdt%ROWTYPE ;
6204:
6205:
6206: l_index NUMBER;
6207: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6208: l_grading_schema_cd igs_as_suaai_v.grading_schema_cd%TYPE;
6209: l_gs_version_number igs_as_suaai_v.gs_version_number%TYPE;
6210: l_grade igs_as_suaai_v.grade%TYPE;
6211: l_s_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
6342: ***************************************************************/
6343:
6344:
6345:
6346: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6347: l_hesa_mode igs_he_code_map_val.map1%TYPE;
6348:
6349: BEGIN
6350: l_he_code_map_val.association_code := 'OSS_HESA_MODE_ASSOC';
6343:
6344:
6345:
6346: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6347: l_hesa_mode igs_he_code_map_val.map1%TYPE;
6348:
6349: BEGIN
6350: l_he_code_map_val.association_code := 'OSS_HESA_MODE_ASSOC';
6351: l_he_code_map_val.map2 := Nvl(p_susa_study_mode,
6531: Who When What
6532: smaddali Modified this procedure to check if l_annual_credit is a number before calculating fte , bug 2668966
6533: ***************************************************************/
6534:
6535: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6536: l_annual_credit NUMBER;
6537: l_credit igs_he_code_map_val.map1%TYPE ;
6538:
6539: NOT_NUMBER_EXCEP EXCEPTION ;
6533: ***************************************************************/
6534:
6535: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6536: l_annual_credit NUMBER;
6537: l_credit igs_he_code_map_val.map1%TYPE ;
6538:
6539: NOT_NUMBER_EXCEP EXCEPTION ;
6540:
6541: BEGIN
6712: AND b.code_type = 'OSS_QUAL_1ST_DEGREE'
6713: AND NVL(b.closed_ind,'N') = 'N' )
6714: ORDER BY a.year DESC;
6715:
6716: l_pgce_class igs_he_code_map_val.map1%TYPE;
6717: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6718: l_grading_schema igs_uc_qual_dets.grading_schema_cd%TYPE;
6719:
6720: BEGIN
6713: AND NVL(b.closed_ind,'N') = 'N' )
6714: ORDER BY a.year DESC;
6715:
6716: l_pgce_class igs_he_code_map_val.map1%TYPE;
6717: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
6718: l_grading_schema igs_uc_qual_dets.grading_schema_cd%TYPE;
6719:
6720: BEGIN
6721:
7302: Change History :
7303: Who When What
7304: ***************************************************************/
7305:
7306: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7307:
7308: BEGIN
7309: l_he_code_map_val.association_code := 'OSS_HESA_MARSTAT_ASSOC';
7310: l_he_code_map_val.map2 := p_oss_marital_status;
7340: Change History :
7341: Who When What
7342: ***************************************************************/
7343:
7344: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7345:
7346: BEGIN
7347: l_he_code_map_val.association_code := 'OSS_HESA_DEPEND_ASSOC';
7348: l_he_code_map_val.map2 := p_oss_dependants;
7380: Change History :
7381: Who When What
7382: ***************************************************************/
7383:
7384: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7385:
7386: BEGIN
7387: l_he_code_map_val.association_code := 'OSS_HESA_ELIGENFD_ASSOC';
7388: l_he_code_map_val.map2 := NVL(p_susa_enh_fund_elig,p_spa_enh_fund_elig);
7421: Change History :
7422: Who When What
7423: ***************************************************************/
7424:
7425: l_map1 igs_he_code_map_val.map1%TYPE;
7426:
7427: CURSOR c_hesa_dis_types IS
7428: SELECT map1
7429: FROM igs_he_code_map_val a, igs_pe_pers_disablty b
7425: l_map1 igs_he_code_map_val.map1%TYPE;
7426:
7427: CURSOR c_hesa_dis_types IS
7428: SELECT map1
7429: FROM igs_he_code_map_val a, igs_pe_pers_disablty b
7430: WHERE a.map2 = b.disability_type
7431: AND a.association_code = 'OSS_HESA_LEARNDIF_ASSOC'
7432: AND NOT NVL(b.start_date, p_enrl_end_dt) > p_enrl_end_dt
7433: AND NOT NVL(b.end_date, p_enrl_start_dt) < p_enrl_start_dt
7480: Change History :
7481: Who When What
7482: ***************************************************************/
7483:
7484: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7485:
7486: BEGIN
7487: l_he_code_map_val.association_code := 'OSS_HESA_GOVINIT_ASSOC';
7488: l_he_code_map_val.map2 := NVL(p_spa_gov_initiatives_cd,p_prog_gov_initiatives_cd);
7597: Change History :
7598: Who When What
7599: ***************************************************************/
7600:
7601: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7602:
7603: BEGIN
7604: l_he_code_map_val.association_code := 'OSS_HESA_ELIDISUP_ASSOC';
7605: l_he_code_map_val.map2 := NVL(p_spa_disadv_uplift_elig_cd,p_prog_disadv_uplift_elig_cd);
7636: Change History :
7637: Who When What
7638: ***************************************************************/
7639:
7640: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7641:
7642: BEGIN
7643: l_he_code_map_val.association_code := 'OSS_HESA_FROUTARR_ASSOC';
7644: l_he_code_map_val.map2 := NVL(p_spa_franch_out_arr_cd,p_prog_franch_out_arr_cd );
7674: Change History :
7675: Who When What
7676: ***************************************************************/
7677:
7678: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7679:
7680: BEGIN
7681: l_he_code_map_val.association_code := 'OSS_HESA_EMPROLE_ASSOC';
7682: l_he_code_map_val.map2 := p_spa_employer_role_cd;
7713: Change History :
7714: Who When What
7715: ***************************************************************/
7716:
7717: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7718:
7719: BEGIN
7720: l_he_code_map_val.association_code := 'OSS_HESA_FRANPART_ASSOC';
7721: l_he_code_map_val.map2 := p_spa_franch_partner_cd;
7758: FROM igs_pe_languages_v
7759: WHERE party_id = p_person_id
7760: AND language_name = 'WS';
7761:
7762: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7763:
7764: BEGIN
7765: l_he_code_map_val.association_code := 'OSS_HESA_WELSSP_ASSOC';
7766:
7812: FROM igs_pe_race
7813: WHERE person_id = p_person_id;
7814:
7815:
7816: l_he_code_map_val igs_he_code_map_val%ROWTYPE := NULL;
7817:
7818: BEGIN
7819: l_he_code_map_val.association_code := 'OSS_HESA_NATIOND_ASSOC';
7820:
7870:
7871: CURSOR c_welsh_bacc_qual IS
7872: SELECT 'X'
7873: FROM igs_uc_qual_dets iuqd,
7874: igs_he_code_map_val ihcmv
7875: WHERE iuqd.person_id = p_person_id
7876: AND ihcmv.association_code = 'UCAS_OSS_AWD_ASSOC'
7877: AND ihcmv.map1 = 'WB'
7878: AND ihcmv.map2 = iuqd.exam_level;
7879:
7880: CURSOR c_welsh_bacc_pass_grd IS
7881: SELECT 'X'
7882: FROM igs_uc_qual_dets iuqd,
7883: igs_he_code_map_val ihcmv,
7884: igs_as_grd_sch_grade iagsg
7885: WHERE iuqd.person_id = p_person_id
7886: AND iuqd.exam_level = ihcmv.map2
7887: AND iuqd.grading_schema_cd = iagsg.grading_schema_cd