[Home] [Help]
37: g_prog_type_rec_flag BOOLEAN; -- used as a flag to check whether to search igs_he_submsn_awd table
38: l_awd_conf_start_dt DATE;
39: l_awd_conf_end_dt DATE;
40: -- PL/SQL table to hold award conferral dates for a submission
41: g_awd_table igs_he_extract_fields_pkg.awd_table;
42:
43: -- Index Table to hold the field definitions.
44: TYPE fldnum IS TABLE OF igs_he_usr_rt_cl_fld.field_number%TYPE
45: INDEX BY binary_integer;
64:
65: g_msg_ext_fld_val_null VARCHAR2(2000);
66:
67: -- Structure to hold cost centres
68: g_cc_rec igs_he_extract_fields_pkg.cc_rec;
69: g_total_ccs NUMBER;
70:
71: -- Structure to hold Modules
72: g_mod_rec igs_he_extract_fields_pkg.mod_rec;
68: g_cc_rec igs_he_extract_fields_pkg.cc_rec;
69: g_total_ccs NUMBER;
70:
71: -- Structure to hold Modules
72: g_mod_rec igs_he_extract_fields_pkg.mod_rec;
73: g_total_mod NUMBER;
74:
75: /*----------------------------------------------------------------------
76: This procedures writes onto the log file
146: CLOSE c_field_of_study ;
147: END IF ;
148:
149: Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
150: Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_FIELDS_PKG.get_pgce_subj');
151: IGS_GE_MSG_STACK.ADD;
152: App_Exception.Raise_Exception;
153:
154: END get_pgce_subj ;
704: Who When What
705: Bayadav 25-MAR-2002 Changed the code for field 26 as a part of HECR002.
706: Bayadav 26-Mar-2002 Changed the code for field 74 as a part of HECR001.
707: Bayadav 26-Mar-2002 Changed the code for field 101,104,107,113,116,119,122,125,128,131,137,140,143 and 146 as a part of HECR004 .
708: Bayadav 19-JUL-2002 Changed the call for IGS_HE_EXTRACT_FIELDS_PKG.get_year_of_student proc for fields 27 and 30 as per bug 2449010
709: smaddali 23-jul-02 modified field 76 to call new procedure for bug 2452592
710: Bayadav 24-OCt-02 modified the logic for field 148,4,33,43,44,45,46,49,50,153,6,14,17,27,29,
711: 34,36,42,83,84,85,54,55,56,57,58,59,60,61,62,63,78,155,156,161 as a part of HEFD101(2636897)
712:
769:
770: ELSIF p_field_number = 2
771: THEN
772: -- Hesa Institution Id
773: igs_he_extract_fields_pkg.get_hesa_inst_id
774: (p_hesa_inst_id => p_value);
775:
776: ELSIF p_field_number = 3
777: THEN
775:
776: ELSIF p_field_number = 3
777: THEN
778: -- Campus Id
779: igs_he_extract_fields_pkg.get_campus_id
780: (p_location_cd => g_en_stdnt_ps_att.location_cd,
781: p_campus_id => p_value);
782:
783: ELSIF p_field_number = 4
791: l_inst_id := 0;
792: END IF;
793:
794: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
795: igs_he_extract_fields_pkg.get_stdnt_id
796: (p_person_id => p_person_id,
797: p_inst_id => l_inst_id,
798: p_stdnt_id => p_value,
799: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
804: -- Scottish Candidate Number
805: IF g_he_st_spa.associate_scott_cand = 'Y'
806: THEN
807: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
808: igs_he_extract_fields_pkg.get_alt_pers_id
809: (p_person_id => p_person_id,
810: p_id_type => 'UCASREGNO',
811: p_api_id => p_value,
812: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
818: -- FE Student Marker
819: -- First get the Funding Source
820: -- smaddali modified this call to pass funding_source field for hefd208 - bug#2717751
821: -- jtmathew modified this call to pass funding source from spa level - bug#3962575
822: igs_he_extract_fields_pkg.get_funding_src
823: (p_course_cd => p_course_cd,
824: p_version_number => p_crv_version_number,
825: p_spa_fund_src => g_en_stdnt_ps_att.funding_source,
826: p_poous_fund_src => g_he_poous.funding_source,
829:
830: -- Next get the Fundability Code
831: IF g_field_defn.oss_value.EXISTS(64) THEN
832: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
833: igs_he_extract_fields_pkg.get_fundability_cd
834: (p_person_id => p_person_id,
835: p_susa_fund_cd => g_he_en_susa.fundability_code,
836: p_spa_funding_source => g_en_stdnt_ps_att.funding_source,
837: p_poous_fund_cd => g_he_poous.fundability_cd,
847: -- Now get the FE Student Marker
848: --Included new param validation_country as a part of HEFD101
849:
850: IF g_he_submsn_header.validation_country IN ('ENGLAND','WALES') AND g_field_defn.oss_value.EXISTS(64) AND g_field_defn.oss_value.EXISTS(65) THEN
851: igs_he_extract_fields_pkg.get_fe_stdnt_mrker
852: (p_spa_fe_stdnt_mrker => g_he_st_spa.fe_student_marker,
853: p_fe_program_marker => g_he_st_prog.fe_program_marker,
854: p_funding_src => g_field_defn.oss_value(64),
855: p_fundability_cd => g_field_defn.oss_value(65),
901: THEN
902: -- Family Name on 16th Birthday
903: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
904: l_fmly_name := NULL;
905: igs_he_extract_fields_pkg.get_fmly_name_on_16_bday
906: (p_person_id => p_person_id,
907: p_fmly_name => l_fmly_name,
908: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
909: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
920:
921: ELSIF p_field_number = 11
922: THEN
923: -- Gender
924: igs_he_extract_fields_pkg.get_gender
925: (p_gender => g_pe_person.sex,
926: p_hesa_gender => p_value);
927:
928: ELSIF p_field_number = 12
927:
928: ELSIF p_field_number = 12
929: THEN
930: -- Domicile
931: igs_he_extract_fields_pkg.get_domicile
932: (p_ad_domicile => g_he_ad_dtl.domicile_cd,
933: p_spa_domicile => g_he_st_spa.domicile_cd,
934: p_hesa_domicile => p_value);
935:
936: ELSIF p_field_number = 13
937: THEN
938: -- Nationality
939: -- smaddali 11-dec-03 Modified for bug#3235753 , added 1 new parameter
940: igs_he_extract_fields_pkg.get_nationality
941: (p_person_id => p_person_id,
942: p_nationality => p_value,
943: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date);
944:
946: -- as part of HEFD311 - July 2004 Changes enhancement bug, 2956444
947: ELSIF p_field_number = 14
948: THEN
949: -- Ethnicity
950: igs_he_extract_fields_pkg.get_ethnicity (
951: p_person_id => p_person_id,
952: p_oss_eth => g_pe_stat_v.ethnic_origin_id,
953: p_hesa_eth => p_value);
954:
954:
955: ELSIF p_field_number = 15
956: THEN
957: -- Disability Allowance
958: igs_he_extract_fields_pkg.get_disablity_allow
959: (p_oss_dis_allow => g_he_en_susa.disability_allow,
960: p_hesa_dis_allow => p_value);
961:
962: ELSIF p_field_number = 16
962: ELSIF p_field_number = 16
963: THEN
964: -- Diability
965: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
966: igs_he_extract_fields_pkg.get_disablity
967: (p_person_id => p_person_id,
968: p_disability => p_value,
969: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
970: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
975: -- Additional Support Band
976: IF ((g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3))
977: AND g_he_submsn_header.validation_country = 'ENGLAND') THEN
978:
979: igs_he_extract_fields_pkg.get_addnl_supp_band
980: (p_oss_supp_band => g_he_en_susa.additional_sup_band,
981: p_hesa_supp_band => p_value);
982: ELSE
983: p_value := NULL;
993: THEN
994:
995: --Get the field 41 value - Qualification Aim
996: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
997: igs_he_extract_fields_pkg.get_gen_qual_aim
998: (p_person_id => p_person_id,
999: p_course_cd => p_course_cd,
1000: p_version_number => p_crv_version_number,
1001: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
1004: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
1005: p_awd_conf_start_dt => l_awd_conf_start_dt);
1006:
1007: -- Get the field 70 value - Mode of Study
1008: igs_he_extract_fields_pkg.get_mode_of_study
1009: (p_person_id => p_person_id,
1010: p_course_cd => p_course_cd,
1011: p_version_number => p_crv_version_number,
1012: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1021:
1022: -- Get the field 148 value - UCAS NUM
1023: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
1024: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1025: igs_he_extract_fields_pkg.get_ucasnum
1026: (p_person_id => p_person_id,
1027: p_ucasnum => g_field_defn.hesa_value(148),
1028: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1029: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
1033: END IF;
1034:
1035: IF g_field_defn.hesa_value.EXISTS(148) AND g_field_defn.hesa_value.EXISTS(41) THEN
1036: -- Calculate the field 19 value - Year left last institution
1037: igs_he_extract_fields_pkg.get_yr_left_last_inst
1038: (p_person_id => p_person_id,
1039: p_com_dt => g_en_stdnt_ps_att.commencement_dt,
1040: p_hesa_gen_qaim => g_field_defn.hesa_value(41),
1041: p_ucasnum => g_field_defn.hesa_value(148),
1072:
1073: -- Get the field 148 value - UCAS NUM
1074: IF NOT g_field_defn.hesa_value.EXISTS(148) THEN
1075: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
1076: igs_he_extract_fields_pkg.get_ucasnum
1077: (p_person_id => p_person_id,
1078: p_ucasnum => g_field_defn.hesa_value(148),
1079: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1080: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
1095:
1096: ELSIF p_field_number = 26
1097: THEN
1098: -- Commencement Date
1099: igs_he_extract_fields_pkg.get_commencement_dt( p_hesa_commdate => g_he_st_spa.commencement_dt,
1100: p_enstdnt_commdate => g_en_stdnt_ps_att.commencement_dt,
1101: p_person_id => p_person_id ,
1102: p_course_cd => p_course_cd ,
1103: p_version_number => p_crv_version_number,
1110: -- smaddali removed the call to field 72 and added call to field30 for bug 2452551
1111: -- First get field 30, Year of student
1112:
1113: -- jbaber added p_susa_year_of_student for HEFD350
1114: igs_he_extract_fields_pkg.get_year_of_student
1115: (p_person_id => p_person_id ,
1116: p_course_cd => p_course_cd ,
1117: p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
1118: p_sequence_number => g_as_su_setatmpt.sequence_number,
1123: --get field 41 value also first as it is required for this calculation
1124: -- Qualification Aim
1125: IF NOT g_field_defn.hesa_value.EXISTS(41) THEN
1126: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
1127: igs_he_extract_fields_pkg.get_gen_qual_aim
1128: (p_person_id => p_person_id,
1129: p_course_cd => p_course_cd,
1130: p_version_number => p_crv_version_number,
1131: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
1150: (g_field_defn.hesa_value(41) IN (61,62,97,98))
1151: )
1152: )
1153: THEN
1154: igs_he_extract_fields_pkg.get_new_ent_to_he
1155: (p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
1156: p_susa_new_ent_to_he => g_he_en_susa.new_he_entrant_cd,
1157: p_yop => g_field_defn.hesa_value(30),
1158: p_high_qual_on_ent => g_field_defn.hesa_value(21),
1174: END IF;
1175:
1176: ELSIF p_field_number = 28 THEN
1177: -- Special students
1178: igs_he_extract_fields_pkg.get_special_student
1179: (p_ad_special_student => g_he_ad_dtl.special_student_cd,
1180: p_spa_special_student => g_he_st_spa.special_student,
1181: p_oss_special_student => g_field_defn.oss_value(28),
1182: p_hesa_special_student => p_value);
1182: p_hesa_special_student => p_value);
1183:
1184: ELSIF p_field_number = 29 THEN
1185: --get the quail1 field 37 and 38 value required in calculating field 29 value
1186: igs_he_extract_fields_pkg.get_qual_obtained
1187: (p_person_id => p_person_id,
1188: p_course_cd => p_course_cd,
1189: p_enrl_start_dt => l_awd_conf_start_dt,
1190: p_enrl_end_dt => l_awd_conf_end_dt,
1195: p_classification => g_field_defn.hesa_value(39));
1196:
1197: --Calcualting field 53 fierst reuqured fro field 161 calcualtion
1198: -- Teacher Training Course Identifier
1199: igs_he_extract_fields_pkg.get_teach_train_crs_id
1200: (p_prg_ttcid => g_he_st_prog.teacher_train_prog_id,
1201: p_spa_ttcid => g_he_st_spa.teacher_train_prog_id,
1202: p_hesa_ttcid => g_field_defn.hesa_value(53));
1203:
1205: --Calculating field 161 first required for field 29
1206: -- Outcome of ITT Program
1207:
1208: IF g_he_submsn_header.validation_country IN ('ENGLAND','WALES') THEN
1209: igs_he_extract_fields_pkg.get_itt_outcome
1210: (p_oss_itt_outcome => g_he_st_spa.itt_prog_outcome,
1211: p_teach_train_prg => g_field_defn.hesa_value(53),
1212: p_hesa_itt_outcome => g_field_defn.hesa_value(161));
1213: END IF;
1220: -- Get the value
1221: IF g_he_st_spa.associate_teach_ref_num = 'Y'
1222: THEN
1223: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1224: igs_he_extract_fields_pkg.get_alt_pers_id
1225: (p_person_id => p_person_id,
1226: p_id_type => 'TEACH REF',
1227: p_api_id => p_value,
1228: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1255:
1256: ELSE
1257:
1258: -- jbaber added p_susa_year_of_student for HEFD350
1259: igs_he_extract_fields_pkg.get_year_of_student
1260: (p_person_id => p_person_id,
1261: p_course_cd => p_course_cd,
1262: p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
1263: p_sequence_number => g_as_su_setatmpt.sequence_number,
1276: ELSIF p_field_number = 31
1277: THEN
1278: -- Term Time Accomodation
1279: -- Calculate field 71, location of study first
1280: igs_he_extract_fields_pkg.get_study_location
1281: (p_susa_study_location => g_he_en_susa.study_location,
1282: p_poous_study_location => g_he_poous.location_of_study,
1283: p_prg_study_location => g_he_st_prog.location_of_study,
1284: p_oss_study_location => g_field_defn.oss_value(71),
1285: p_hesa_study_location => g_field_defn.hesa_value(71));
1286:
1287: -- Next calcualte TTA
1288: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1289: igs_he_extract_fields_pkg.get_term_time_acc
1290: (p_person_id => p_person_id,
1291: p_susa_term_time_acc => g_he_en_susa.term_time_accom,
1292: p_study_location => g_field_defn.oss_value(71),
1293: p_hesa_term_time_acc => p_value,
1301: ELSIF p_field_number = 33
1302: THEN
1303:
1304: -- Reason for leaving institution
1305: igs_he_extract_fields_pkg.get_rsn_inst_left
1306: (p_person_id => p_person_id,
1307: p_course_cd => p_course_cd ,
1308: p_crs_req_comp_ind => g_en_stdnt_ps_att.course_rqrmnt_complete_ind,
1309: p_crs_req_comp_dt => g_en_stdnt_ps_att.course_rqrmnts_complete_dt,
1322: --smaddali added new parameter p_course_cd to this call for bug 2396174
1323: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
1324: THEN
1325: -- Calculate Completion Status
1326: igs_he_extract_fields_pkg.get_completion_status
1327: (p_person_id => p_person_id,
1328: p_course_cd => p_course_cd ,
1329: p_susa_comp_status => g_he_en_susa.completion_status,
1330: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
1355:
1356: -- Good Standing Marker
1357: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
1358: THEN
1359: igs_he_extract_fields_pkg.get_good_stand_mrkr
1360: (p_susa_good_st_mk => g_he_en_susa.good_stand_marker,
1361: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
1362: p_crs_req_comp_ind => g_en_stdnt_ps_att.course_rqrmnt_complete_ind,
1363: p_discont_date => g_en_stdnt_ps_att.discontinued_dt,
1373: IF g_field_defn.hesa_value.EXISTS(37)
1374: THEN
1375: p_value := g_field_defn.hesa_value(37);
1376: ELSE
1377: igs_he_extract_fields_pkg.get_qual_obtained
1378: (p_person_id => p_person_id,
1379: p_course_cd => p_course_cd,
1380: p_enrl_start_dt => l_awd_conf_start_dt,
1381: p_enrl_end_dt => l_awd_conf_end_dt,
1393: IF g_field_defn.hesa_value.EXISTS(38)
1394: THEN
1395: p_value := g_field_defn.hesa_value(38);
1396: ELSE
1397: igs_he_extract_fields_pkg.get_qual_obtained
1398: (p_person_id => p_person_id,
1399: p_course_cd => p_course_cd,
1400: p_enrl_start_dt => l_awd_conf_start_dt,
1401: p_enrl_end_dt => l_awd_conf_end_dt,
1414: IF g_field_defn.hesa_value.EXISTS(39)
1415: THEN
1416: p_value := g_field_defn.hesa_value(39);
1417: ELSE
1418: igs_he_extract_fields_pkg.get_qual_obtained
1419: (p_person_id => p_person_id,
1420: p_course_cd => p_course_cd,
1421: p_enrl_start_dt => l_awd_conf_start_dt,
1422: p_enrl_end_dt => l_awd_conf_end_dt,
1444: ELSE
1445:
1446: -- Qualification Aim
1447: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
1448: igs_he_extract_fields_pkg.get_gen_qual_aim
1449: (p_person_id => p_person_id,
1450: p_course_cd => p_course_cd,
1451: p_version_number => p_crv_version_number,
1452: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
1465:
1466: -- FE General Qualification Aim
1467: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
1468: THEN
1469: igs_he_extract_fields_pkg.get_fe_qual_aim
1470: (p_spa_fe_qual_aim => g_he_st_spa.student_fe_qual_aim,
1471: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
1472: p_course_cd => p_course_cd,
1473: p_version_number => p_crv_version_number,
1486: FETCH c_subj INTO p_value;
1487: CLOSE c_subj;
1488: ELSE
1489: -- Qualification Aim, Subject 1
1490: igs_he_extract_fields_pkg.get_qual_aim_sbj
1491: (p_course_cd => p_course_cd,
1492: p_version_number => p_crv_version_number,
1493: p_subject1 => p_value,
1494: p_subject2 => g_field_defn.hesa_value(44),
1521: -- Calculated earlier..
1522: p_value := g_field_defn.hesa_value(44);
1523: ELSE
1524: -- Not calculated earlier, calculate now
1525: igs_he_extract_fields_pkg.get_qual_aim_sbj
1526: (p_course_cd => p_course_cd,
1527: p_version_number => p_crv_version_number,
1528: p_subject1 => l_dummy1,
1529: p_subject2 => p_value,
1556: -- Calculated earlier..
1557: p_value := g_field_defn.hesa_value(45);
1558: ELSE
1559: -- Not calculated earlier, calculate now
1560: igs_he_extract_fields_pkg.get_qual_aim_sbj
1561: (p_course_cd => p_course_cd,
1562: p_version_number => p_crv_version_number,
1563: p_subject1 => l_dummy1,
1564: p_subject2 => l_dummy2,
1575: g_he_st_spa.qual_aim_subj2 IS NOT NULL OR
1576: g_he_st_spa.qual_aim_subj3 IS NOT NULL
1577: THEN
1578:
1579: igs_he_extract_fields_pkg.get_qual_aim_sbj1(
1580: p_qual_aim_subj1 => g_he_st_spa.qual_aim_subj1,
1581: p_qual_aim_subj2 => g_he_st_spa.qual_aim_subj2,
1582: p_qual_aim_subj3 => g_he_st_spa.qual_aim_subj3,
1583: p_oss_qualaim_sbj => g_he_st_spa.qual_aim_proportion,
1590: -- Calculated earlier..
1591: p_value := g_field_defn.hesa_value(46);
1592: ELSE
1593: -- Not calculated earlier, calculate now
1594: igs_he_extract_fields_pkg.get_qual_aim_sbj
1595: (p_course_cd => p_course_cd,
1596: p_version_number => p_crv_version_number,
1597: p_subject1 => l_dummy1,
1598: p_subject2 => l_dummy2,
1606: -- Awarding Body 1
1607: IF g_field_defn.oss_value.EXISTS(37)
1608: AND g_field_defn.oss_value.EXISTS(38)
1609: THEN
1610: igs_he_extract_fields_pkg.get_awd_body_12
1611: (p_course_cd => p_course_cd,
1612: p_version_number => p_crv_version_number,
1613: p_awd1 => g_field_defn.oss_value(37),
1614: p_awd2 => g_field_defn.oss_value(38),
1627: ELSIF g_field_defn.oss_value.EXISTS(37)
1628: AND g_field_defn.oss_value.EXISTS(38)
1629: THEN
1630: -- Not calculated therefore calculate.
1631: igs_he_extract_fields_pkg.get_awd_body_12
1632: (p_course_cd => p_course_cd,
1633: p_version_number => p_crv_version_number,
1634: p_awd1 => g_field_defn.oss_value(37),
1635: p_awd2 => g_field_defn.oss_value(38),
1640:
1641: ELSIF p_field_number = 49
1642: THEN
1643: -- Length of Program
1644: igs_he_extract_fields_pkg.get_new_prog_length
1645: (p_spa_attendance_type => g_en_stdnt_ps_att.attendance_type,
1646: p_program_length => g_ps_ofr_opt.program_length,
1647: p_program_length_measurement => g_ps_ofr_opt.program_length_measurement,
1648: p_length => p_value,
1661: -- Calculated Earlier ..
1662: p_value := g_field_defn.hesa_value(50);
1663: ELSE
1664: -- Not calculated earlier ..
1665: igs_he_extract_fields_pkg.get_new_prog_length
1666: (p_spa_attendance_type => g_en_stdnt_ps_att.attendance_type,
1667: p_program_length => g_ps_ofr_opt.program_length,
1668: p_program_length_measurement => g_ps_ofr_opt.program_length_measurement,
1669: p_length => g_field_defn.hesa_value(49),
1693: p_value := g_field_defn.hesa_value(53);
1694: ELSE
1695: -- Not calculated earlier ..
1696: -- Teacher Training Course Identifier
1697: igs_he_extract_fields_pkg.get_teach_train_crs_id
1698: (p_prg_ttcid => g_he_st_prog.teacher_train_prog_id,
1699: p_spa_ttcid => g_he_st_spa.teacher_train_prog_id,
1700: p_hesa_ttcid => p_value);
1701: END IF;
1704: THEN
1705:
1706: IF (g_he_submsn_header.validation_country in ('ENGLAND','WALES') AND ( g_field_defn.hesa_value.EXISTS(53) and g_field_defn.hesa_value(53) IN(1,2,6,7) )) THEN
1707: -- ITT Phase / Scope
1708: igs_he_extract_fields_pkg.get_itt_phsc
1709: (p_prg_itt_phsc => g_he_st_prog.itt_phase,
1710: p_spa_itt_phsc => g_he_st_spa.itt_phase,
1711: p_hesa_itt_phsc => p_value);
1712:
1718: THEN
1719:
1720: IF (g_he_submsn_header.validation_country in ('SCOTLAND','WALES','NORTHERN IRELAND') AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) IN(1,2)) ) THEN
1721: -- Bilingual ITT Marker
1722: igs_he_extract_fields_pkg.get_itt_mrker
1723: (p_prg_itt_mrker => g_he_st_prog.bilingual_itt_marker,
1724: p_spa_itt_mrker => g_he_st_spa.bilingual_itt_marker,
1725: p_hesa_itt_mrker => p_value);
1726:
1733: THEN
1734:
1735: IF ( g_he_submsn_header.validation_country IN ('SCOTLAND','NORTHERN IRELAND') AND ( g_field_defn.hesa_value.EXISTS(53) and g_field_defn.hesa_value(53) IN(1,2) ) ) THEN
1736: -- Teaching Qualification Sought Sector
1737: igs_he_extract_fields_pkg.get_teach_qual_sect
1738: (p_oss_teach_qual_sect => g_he_st_prog.teaching_qual_sought_sector,
1739: p_hesa_teach_qual_sect => p_value);
1740:
1741: ELSE
1745: ELSIF p_field_number = 57
1746: THEN
1747: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) AND g_field_defn.hesa_value(56) =2 ) ) THEN
1748: -- Teaching Qualification Sought Subject 1
1749: igs_he_extract_fields_pkg.get_teach_qual_sbj
1750: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj1,
1751: p_hesa_teach_qual_sbj => p_value);
1752:
1753:
1759: ELSIF p_field_number = 58
1760: THEN
1761: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) AND g_field_defn.hesa_value(56) =2 )) THEN
1762: -- Teaching Qualification Sought Subject 2
1763: igs_he_extract_fields_pkg.get_teach_qual_sbj
1764: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj2,
1765: p_hesa_teach_qual_sbj => p_value);
1766: ELSE
1767: g_default_pro:= 'N';
1770: ELSIF p_field_number = 59
1771: THEN
1772: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) AND g_field_defn.hesa_value(56) =2 )) THEN
1773: -- Teaching Qualification Sought Subject 3
1774: igs_he_extract_fields_pkg.get_teach_qual_sbj
1775: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj3,
1776: p_hesa_teach_qual_sbj => p_value);
1777: ELSE
1778: g_default_pro:= 'N';
1782: THEN
1783:
1784: IF ( g_he_submsn_header.validation_country IN ('SCOTLAND' , 'NORTHERN IRELAND' ) AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) in (1,2)) ) THEN
1785: -- Teaching Qualification Gained Sector
1786: igs_he_extract_fields_pkg.get_teach_qual_sect
1787: (p_oss_teach_qual_sect => g_he_st_spa.teaching_qual_gain_sector,
1788: p_hesa_teach_qual_sect => p_value);
1789:
1790: IF p_value IS NULL
1801: THEN
1802:
1803: IF (g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) AND g_field_defn.hesa_value(60) = 2 ) ) THEN
1804: -- Teaching Qualification Gained Subject 1
1805: igs_he_extract_fields_pkg.get_teach_qual_sbj
1806: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj1,
1807: p_hesa_teach_qual_sbj => p_value);
1808:
1809: IF p_value IS NULL
1819: ELSIF p_field_number = 62
1820: THEN
1821: IF (g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) AND g_field_defn.hesa_value(60) = 2 ) ) THEN
1822: -- Teaching Qualification Gained Subject 2
1823: igs_he_extract_fields_pkg.get_teach_qual_sbj
1824: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj2,
1825: p_hesa_teach_qual_sbj => p_value);
1826:
1827: IF p_value IS NULL
1839: THEN
1840:
1841: IF (g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) AND g_field_defn.hesa_value(60) = 2 ) ) THEN
1842: -- Teaching Qualification Gained Subject 3
1843: igs_he_extract_fields_pkg.get_teach_qual_sbj
1844: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj3,
1845: p_hesa_teach_qual_sbj => p_value);
1846:
1847: IF p_value IS NULL
1867: ELSE
1868: -- Not calculated earlier.
1869: -- smaddali modified this call to pass funding_source field for hefd208 - bug#2717751
1870: -- jtmathew modified this call to pass funding_source at spa level - bug#3962575
1871: igs_he_extract_fields_pkg.get_funding_src
1872: (p_course_cd => p_course_cd,
1873: p_version_number => p_crv_version_number,
1874: p_spa_fund_src => g_en_stdnt_ps_att.funding_source,
1875: p_poous_fund_src => g_he_poous.funding_source,
1889: IF g_field_defn.oss_value.EXISTS(64)
1890: THEN
1891: -- Next get the Fundability Code
1892: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1893: igs_he_extract_fields_pkg.get_fundability_cd
1894: (p_person_id => p_person_id,
1895: p_susa_fund_cd => g_he_en_susa.fundability_code,
1896: p_spa_funding_source => g_en_stdnt_ps_att.funding_source,
1897: p_poous_fund_cd => g_he_poous.fundability_cd,
1914: IF g_field_defn.oss_value.EXISTS(6)
1915: AND g_field_defn.oss_value.EXISTS(28)
1916: THEN
1917: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1918: igs_he_extract_fields_pkg.get_fee_elig
1919: (p_person_id => p_person_id,
1920: p_susa_fee_elig => g_he_en_susa.fee_eligibility,
1921: p_fe_stdnt_mrker => g_field_defn.oss_value(6),
1922: p_study_mode => NVL(g_he_en_susa.study_mode,NVL(g_he_poous.mode_of_study,g_en_stdnt_ps_att.attendance_type)) ,
1925: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1926: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
1927: ELSE
1928: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
1929: igs_he_extract_fields_pkg.get_fee_elig
1930: (p_person_id => p_person_id,
1931: p_susa_fee_elig => g_he_en_susa.fee_eligibility,
1932: p_fe_stdnt_mrker => NULL,
1933: p_study_mode => NVL(g_he_en_susa.study_mode,NVL(g_he_poous.mode_of_study,g_en_stdnt_ps_att.attendance_type)),
1940: ELSIF p_field_number = 67
1941: THEN
1942: -- Fee Band
1943: IF g_field_defn.hesa_value.EXISTS(66) THEN
1944: igs_he_extract_fields_pkg.get_fee_band
1945: (p_hesa_fee_elig => g_field_defn.hesa_value(66),
1946: p_susa_fee_band => g_he_en_susa.fee_band,
1947: p_poous_fee_band => g_he_poous.fee_band,
1948: p_prg_fee_band => g_he_st_prog.fee_band,
1948: p_prg_fee_band => g_he_st_prog.fee_band,
1949: p_hesa_fee_band => p_value);
1950: ELSE
1951:
1952: igs_he_extract_fields_pkg.get_fee_band
1953: (p_hesa_fee_elig => NULL,
1954: p_susa_fee_band => g_he_en_susa.fee_band,
1955: p_poous_fee_band => g_he_poous.fee_band,
1956: p_prg_fee_band => g_he_st_prog.fee_band,
1970: -- smaddali 14-oct-03 added 2 new parameters to the procedure get_amt_tuition_fees, for bug#3179544
1971: IF g_field_defn.hesa_value.EXISTS(6)
1972: AND g_field_defn.hesa_value.EXISTS(28)
1973: THEN
1974: igs_he_extract_fields_pkg.get_amt_tuition_fees
1975: (p_person_id => p_person_id,
1976: p_course_cd => p_course_cd,
1977: p_cal_type => g_en_stdnt_ps_att.cal_type,
1978: p_fe_prg_mrker => g_he_st_prog.fe_program_marker,
1984: );
1985:
1986: -- Calculate Mode of Study
1987: IF NOT g_field_defn.hesa_value.EXISTS(70) THEN
1988: igs_he_extract_fields_pkg.get_mode_of_study
1989: (p_person_id => p_person_id,
1990: p_course_cd => p_course_cd,
1991: p_version_number => p_crv_version_number,
1992: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
1997: p_mode_of_study => g_field_defn.hesa_value(70));
1998: END IF ;
1999:
2000: -- Now calculate the major source of tuition fees
2001: igs_he_extract_fields_pkg.get_maj_src_tu_fee
2002: (p_person_id => p_person_id,
2003: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
2004: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
2005: p_special_stdnt => g_field_defn.hesa_value(28),
2022: THEN
2023: -- Calculated earlier, for field 68
2024: p_value := g_field_defn.hesa_value(70);
2025: ELSE
2026: igs_he_extract_fields_pkg.get_mode_of_study
2027: (p_person_id => p_person_id,
2028: p_course_cd => p_course_cd,
2029: p_version_number => p_crv_version_number,
2030: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
2044: -- Calculated earlier, for field 31
2045: p_value := g_field_defn.hesa_value(71);
2046: ELSE
2047: -- Not calculated earlier, hence derive
2048: igs_he_extract_fields_pkg.get_study_location
2049: (p_susa_study_location => g_he_en_susa.study_location,
2050: p_poous_study_location => g_he_poous.location_of_study,
2051: p_prg_study_location => g_he_st_prog.location_of_study,
2052: p_oss_study_location => g_field_defn.oss_value(71),
2060: THEN
2061: p_value := g_field_defn.hesa_value(72);
2062: ELSE
2063: -- Not calculated earlier, hence derive
2064: igs_he_extract_fields_pkg.get_year_of_prog
2065: (p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
2066: p_year_of_prog => p_value);
2067:
2068: END IF;
2124: -- PGCE - Classification of Undergraduate Degree
2125: --smaddali added the dependency on field41 for bug 2436924
2126: IF g_field_defn.hesa_value.EXISTS(41) AND g_field_defn.hesa_value(41) IN ('12','13')
2127: THEN
2128: igs_he_extract_fields_pkg.get_pgce_class
2129: (p_person_id => p_person_id,
2130: p_pgce_class => p_value);
2131: ELSE
2132: p_value := NULL ;
2136: THEN
2137:
2138: IF (g_he_submsn_header.validation_country = 'NORTHERN IRELAND' AND ( g_field_defn.hesa_value.EXISTS(12) AND g_field_defn.hesa_value(12) = '8826')) THEN
2139: -- Religion
2140: igs_he_extract_fields_pkg.get_religion
2141: (p_oss_religion => g_pe_stat_v.religion,
2142: p_hesa_religion => p_value);
2143:
2144: ELSE
2160: THEN
2161: -- SLDD Discrete Provision
2162: IF g_field_defn.hesa_value.EXISTS(6)
2163: THEN
2164: igs_he_extract_fields_pkg.get_sldd_disc_prv
2165: (p_oss_sldd_disc_prv => g_he_en_susa.sldd_discrete_prov,
2166: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
2167: p_hesa_sldd_disc_prv => p_value);
2168: END IF;
2187: ELSE
2188:
2189: -- Not calculated earlier. Derive now.
2190: -- smaddali 14-oct-03 added 2 new parameters to the procedure get_amt_tuition_fees, for bug#3179544
2191: igs_he_extract_fields_pkg.get_amt_tuition_fees
2192: (p_person_id => p_person_id,
2193: p_course_cd => p_course_cd,
2194: p_cal_type => g_en_stdnt_ps_att.cal_type,
2195: p_fe_prg_mrker => g_he_st_prog.fe_program_marker,
2209: g_default_pro:= 'N';
2210:
2211: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4) THEN
2212: -- Non Payment Reason
2213: igs_he_extract_fields_pkg.get_non_payment_rsn
2214: (p_oss_non_payment_rsn => g_he_en_susa.non_payment_reason,
2215: p_hesa_non_payment_rsn => p_value,
2216: p_fe_stdnt_mrker => g_field_defn.hesa_value(6));
2217:
2231:
2232: ELSIF p_field_number = 86
2233: THEN
2234: -- Other Insitutions Providing Teaching 1
2235: igs_he_extract_fields_pkg.get_oth_teach_inst
2236: (p_person_id => p_person_id,
2237: p_course_cd => p_course_cd,
2238: p_program_calc => g_he_st_prog.program_calc,
2239: p_susa_inst1 => g_he_en_susa.teaching_inst1,
2255: -- Calculated earlier in field 86
2256: p_value := g_field_defn.hesa_value(87);
2257: ELSE
2258: -- Not calculated earlier
2259: igs_he_extract_fields_pkg.get_oth_teach_inst
2260: (p_person_id => p_person_id,
2261: p_course_cd => p_course_cd,
2262: p_program_calc => g_he_st_prog.program_calc,
2263: p_susa_inst1 => g_he_en_susa.teaching_inst1,
2287: ELSIF p_field_number = 90
2288: THEN
2289: --smaddali added 2 new parameters p_enrl_start_dt and p_enrl_end_dt for bug 2437081
2290: -- Proportion Not taught by this institution
2291: igs_he_extract_fields_pkg.get_prop_not_taught
2292: (p_person_id => p_person_id,
2293: p_course_cd => p_course_cd,
2294: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
2295: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date ,
2305:
2306: ELSIF p_field_number = 91
2307: THEN
2308: -- Credit Transfer Scheme
2309: igs_he_extract_fields_pkg.get_credit_trans_sch
2310: (p_oss_credit_trans_sch => g_he_st_prog.credit_transfer_scheme,
2311: p_hesa_credit_trans_sch => p_value);
2312:
2313: ELSIF p_field_number = 92
2334: ELSIF p_field_number = 94
2335: THEN
2336: -- smaddali removed the validation to check for g_he_st_prog.program_calc for bug 2419875
2337: -- Level of Credit, 1
2338: igs_he_extract_fields_pkg.get_credit_level
2339: (p_susa_credit_level => g_he_en_susa.credit_level1,
2340: p_poous_credit_level => g_he_poous.level_credit1,
2341: p_hesa_credit_level => p_value);
2342:
2343: ELSIF p_field_number = 95
2344: THEN
2345: -- smaddali removed the validation to check for g_he_st_prog.program_calc for bug 2419875
2346: -- Level of Credit, 2
2347: igs_he_extract_fields_pkg.get_credit_level
2348: (p_susa_credit_level => g_he_en_susa.credit_level2 ,
2349: p_poous_credit_level => g_he_poous.level_credit2,
2350: p_hesa_credit_level => p_value);
2351:
2353: THEN
2354:
2355: -- Number of Credit Points Obtained 1
2356: -- jbaber added crd_pt3-4, lvl_crd_pt3-4 for HEFD350
2357: igs_he_extract_fields_pkg.get_credit_obtained
2358: (p_person_id => p_person_id,
2359: p_course_cd => p_course_cd,
2360: p_prog_calc => g_he_st_prog.program_calc,
2361: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
2387: IF g_field_defn.hesa_value.EXISTS(97)
2388: THEN
2389: p_value := g_field_defn.hesa_value(97);
2390: ELSE
2391: igs_he_extract_fields_pkg.get_credit_obtained
2392: (p_person_id => p_person_id,
2393: p_course_cd => p_course_cd,
2394: p_prog_calc => g_he_st_prog.program_calc,
2395: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
2423: IF g_field_defn.hesa_value.EXISTS(98)
2424: THEN
2425: p_value := g_field_defn.hesa_value(98);
2426: ELSE
2427: igs_he_extract_fields_pkg.get_credit_obtained
2428: (p_person_id => p_person_id,
2429: p_course_cd => p_course_cd,
2430: p_prog_calc => g_he_st_prog.program_calc,
2431: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
2455: IF g_field_defn.hesa_value.EXISTS(99)
2456: THEN
2457: p_value := g_field_defn.hesa_value(99);
2458: ELSE
2459: igs_he_extract_fields_pkg.get_credit_obtained
2460: (p_person_id => p_person_id,
2461: p_course_cd => p_course_cd,
2462: p_prog_calc => g_he_st_prog.program_calc,
2463: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
2486: --smaddali added the initialisation of variable g_cc_rec for bug 2417370
2487: --jbaber added p_validation_country for HEFD350
2488: g_cc_rec := NULL ;
2489: g_total_ccs := 0;
2490: igs_he_extract_fields_pkg.get_cost_centres
2491: (p_person_id => p_person_id,
2492: p_course_cd => p_course_cd,
2493: p_version_number => p_crv_version_number,
2494: p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
2933: ELSE
2934:
2935: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
2936: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
2937: igs_he_extract_fields_pkg.get_ucasnum
2938: (p_person_id => p_person_id,
2939: p_ucasnum => p_value,
2940: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
2941: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
2961:
2962: ELSIF p_field_number = 152
2963: THEN
2964: -- Suspension of Active studies
2965: igs_he_extract_fields_pkg.get_studies_susp
2966: (p_person_id => p_person_id,
2967: p_course_cd => p_course_cd,
2968: p_version_number => p_crv_version_number,
2969: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
2973: ELSIF p_field_number = 153
2974: THEN
2975: -- Type of Program year
2976:
2977: igs_he_extract_fields_pkg.get_pyr_type
2978: (p_oss_pyr_type => NVL(g_he_en_susa.type_of_year,g_he_poous.type_of_year),
2979: p_hesa_pyr_type => p_value);
2980:
2981: ELSIF p_field_number = 154
2980:
2981: ELSIF p_field_number = 154
2982: THEN
2983: -- Level applicable for funding
2984: igs_he_extract_fields_pkg.get_lvl_appl_to_fund
2985: (p_poous_lvl_appl_fund => g_he_poous.level_applicable_to_funding,
2986: p_prg_lvl_appl_fund => g_he_st_prog.level_applicable_to_funding,
2987: p_hesa_lvl_appl_fund => p_value);
2988:
3026: l_typeyr := NULL;
3027: END IF;
3028:
3029: -- Completion of Year of Program of study
3030: igs_he_extract_fields_pkg.get_comp_pyr_study (
3031: p_susa_comp_pyr_study => g_he_en_susa.complete_pyr_study_cd,
3032: p_fundlev => l_fundlev,
3033: p_spcstu => l_spcstu,
3034: p_notact => l_notact,
3051: THEN
3052:
3053: IF (g_he_submsn_header.validation_country = 'WALES' AND ( g_field_defn.hesa_value.EXISTS(6) and g_field_defn.hesa_value(6) IN (1,3,4))) THEN
3054: -- Get the value of Destination
3055: igs_he_extract_fields_pkg.get_destination
3056: (p_oss_destination => g_he_st_spa.destination,
3057: p_hesa_destination => p_value);
3058: ELSE
3059: -- The default value should not be calculated for any other condition
3095: -- Calculated earlier, for field 29
3096: p_value := g_field_defn.hesa_value(161);
3097:
3098: ELSE
3099: igs_he_extract_fields_pkg.get_itt_outcome
3100: (p_oss_itt_outcome => g_he_st_spa.itt_prog_outcome,
3101: p_teach_train_prg => g_field_defn.hesa_value(53),
3102: p_hesa_itt_outcome => p_value);
3103:
3119:
3120: ELSIF p_field_number = 164
3121: THEN
3122: -- UFI Place
3123: igs_he_extract_fields_pkg.get_ufi_place
3124: (p_oss_ufi_place => NVL(g_he_st_spa.ufi_place,g_he_poous.ufi_place),
3125: p_hesa_ufi_place => p_value);
3126:
3127: ELSIF p_field_number = 165
3126:
3127: ELSIF p_field_number = 165
3128: THEN
3129: --Franchising Activity
3130: igs_he_extract_fields_pkg.get_franchising_activity
3131: (p_susa_franch_activity => g_he_en_susa.franchising_activity,
3132: p_poous_franch_activity => g_he_poous.franchising_activity,
3133: p_prog_franch_activity => g_he_st_prog.franchising_activity,
3134: p_hesa_franch_activity => p_value);
3135:
3136: ELSIF p_field_number = 166
3137: THEN
3138: -- Institutions own campus identifier
3139: igs_he_extract_fields_pkg.get_campus_id
3140: (p_location_cd => g_en_stdnt_ps_att.location_cd,
3141: p_campus_id => p_value);
3142:
3143: ELSIF p_field_number = 167
3145: -- sjlaporte use date comparison rather than text bug 3933715
3146: -- Social Class Indicator
3147: IF g_en_stdnt_ps_att.commencement_dt > TO_DATE('31/07/2002', 'DD/MM/YYYY')
3148: THEN
3149: igs_he_extract_fields_pkg.get_social_class_ind
3150: (p_spa_social_class_ind => g_he_st_spa.social_class_ind,
3151: p_adm_social_class_ind => g_he_ad_dtl.social_class_cd,
3152: p_hesa_social_class_ind => p_value);
3153: END IF;
3157: -- sjlaporte use date comparison rather than text bug 3933715
3158: -- Occupation Code
3159: IF g_en_stdnt_ps_att.commencement_dt > TO_DATE('31/07/2002', 'DD/MM/YYYY')
3160: THEN
3161: igs_he_extract_fields_pkg.get_occupation_code
3162: (p_spa_occupation_code => g_he_st_spa.occupation_code,
3163: p_hesa_occupation_code => p_value);
3164:
3165: END IF;
3168: THEN
3169:
3170: -- Insitute last attended
3171: -- smaddali modified this field to remove the dependency on field 148UCASNUM for bug 2663717
3172: igs_he_extract_fields_pkg.get_inst_last_attended
3173: (p_person_id => p_person_id,
3174: p_com_date => g_en_stdnt_ps_att.commencement_dt,
3175: p_inst_last_att => p_value,
3176: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
3179:
3180: ELSIF p_field_number = 170
3181: THEN
3182: -- Regulatory Body
3183: igs_he_extract_fields_pkg.get_regulatory_body
3184: (p_course_cd => p_course_cd,
3185: p_version_number => p_crv_version_number,
3186: p_hesa_regulatory_body => p_value);
3187:
3195: -- and adding validation to derive this field only if associate_nhs_reg_num=Y
3196: IF g_he_st_spa.associate_nhs_reg_num = 'Y' THEN
3197:
3198: -- Get alternate person id with type 'DH REG REF' which overlaps HESA reporting period
3199: igs_he_extract_fields_pkg.get_alt_pers_id
3200: (p_person_id => p_person_id,
3201: p_id_type => 'DH REG REF',
3202: p_api_id => p_value,
3203: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
3217:
3218: ELSIF p_field_number = 172
3219: THEN
3220: -- Source of NHS funding
3221: igs_he_extract_fields_pkg.get_nhs_fund_src
3222: (p_spa_nhs_fund_src => g_he_st_spa.nhs_funding_source,
3223: p_prg_nhs_fund_src => g_he_st_prog.nhs_funding_source,
3224: p_hesa_nhs_fund_src => p_value);
3225:
3225:
3226: ELSIF p_field_number = 173
3227: THEN
3228: -- NHS Employer
3229: igs_he_extract_fields_pkg.get_nhs_employer
3230: (p_spa_nhs_employer => g_he_st_spa.nhs_employer,
3231: p_hesa_nhs_employer => p_value);
3232:
3233: ELSIF p_field_number = 174
3235:
3236: g_default_pro := 'N';
3237:
3238: -- Number of GCE AS Levels
3239: igs_he_extract_fields_pkg.get_qual_dets
3240: (p_person_id => p_person_id,
3241: p_course_cd => p_course_cd,
3242: p_hesa_qual => 'GCSEAS',
3243: p_no_of_qual => p_value,
3243: p_no_of_qual => p_value,
3244: p_tariff_score => g_field_defn.hesa_value(175));
3245:
3246: IF p_value IS NOT NULL THEN
3247: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3248: p_person_number => g_pe_person.person_number,
3249: p_course_cd => p_course_cd,
3250: p_hesa_qual => 'GCEASN',
3251: p_no_of_qual => p_value);
3253: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3254: IF g_field_defn.hesa_value.EXISTS(41) AND
3255: g_field_defn.hesa_value.EXISTS(26) AND
3256: g_field_defn.hesa_value.EXISTS(148) AND
3257: igs_he_extract_fields_pkg.is_ucas_ftug
3258: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3259: p_hesa_commdate => g_field_defn.hesa_value(26),
3260: p_ucasnum => g_field_defn.hesa_value(148),
3261: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3274: IF g_field_defn.hesa_value.EXISTS(175) AND g_field_defn.hesa_value(175) IS NOT NULL
3275: THEN
3276: p_value := g_field_defn.hesa_value(175);
3277:
3278: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3279: p_person_number => g_pe_person.person_number,
3280: p_course_cd => p_course_cd,
3281: p_hesa_qual => 'GCEASTS',
3282: p_tariff_score => p_value);
3286: IF p_value IS NULL AND
3287: g_field_defn.hesa_value.EXISTS(41) AND
3288: g_field_defn.hesa_value.EXISTS(26) AND
3289: g_field_defn.hesa_value.EXISTS(148) AND
3290: igs_he_extract_fields_pkg.is_ucas_ftug
3291: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3292: p_hesa_commdate => g_field_defn.hesa_value(26),
3293: p_ucasnum => g_field_defn.hesa_value(148),
3294: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3301:
3302: g_default_pro := 'N';
3303:
3304: -- Number of VCE AS Levels
3305: igs_he_extract_fields_pkg.get_qual_dets
3306: (p_person_id => p_person_id,
3307: p_course_cd => p_course_cd,
3308: p_hesa_qual => 'VCSEAS',
3309: p_no_of_qual => p_value,
3309: p_no_of_qual => p_value,
3310: p_tariff_score => g_field_defn.hesa_value(177));
3311:
3312: IF p_value IS NOT NULL THEN
3313: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3314: p_person_number => g_pe_person.person_number,
3315: p_course_cd => p_course_cd,
3316: p_hesa_qual => 'VCEASN',
3317: p_no_of_qual => p_value);
3319:
3320: IF g_field_defn.hesa_value.EXISTS(41) AND
3321: g_field_defn.hesa_value.EXISTS(26) AND
3322: g_field_defn.hesa_value.EXISTS(148) AND
3323: igs_he_extract_fields_pkg.is_ucas_ftug
3324: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3325: p_hesa_commdate => g_field_defn.hesa_value(26),
3326: p_ucasnum => g_field_defn.hesa_value(148),
3327: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3339: IF g_field_defn.hesa_value.EXISTS(177) AND g_field_defn.hesa_value(177) IS NOT NULL
3340: THEN
3341: p_value := g_field_defn.hesa_value(177);
3342:
3343: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3344: p_person_number => g_pe_person.person_number,
3345: p_course_cd => p_course_cd,
3346: p_hesa_qual => 'VCEASTS',
3347: p_tariff_score => p_value);
3351: IF p_value IS NULL AND
3352: g_field_defn.hesa_value.EXISTS(41) AND
3353: g_field_defn.hesa_value.EXISTS(26) AND
3354: g_field_defn.hesa_value.EXISTS(148) AND
3355: igs_he_extract_fields_pkg.is_ucas_ftug
3356: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3357: p_hesa_commdate => g_field_defn.hesa_value(26),
3358: p_ucasnum => g_field_defn.hesa_value(148),
3359: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3366:
3367: g_default_pro := 'N';
3368:
3369: -- Number of GCE A Levels
3370: igs_he_extract_fields_pkg.get_qual_dets
3371: (p_person_id => p_person_id,
3372: p_course_cd => p_course_cd,
3373: p_hesa_qual => 'GCSEA',
3374: p_no_of_qual => p_value,
3374: p_no_of_qual => p_value,
3375: p_tariff_score => g_field_defn.hesa_value(179));
3376:
3377: IF p_value IS NOT NULL THEN
3378: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3379: p_person_number => g_pe_person.person_number,
3380: p_course_cd => p_course_cd,
3381: p_hesa_qual => 'GCEAN',
3382: p_no_of_qual => p_value);
3384:
3385: IF g_field_defn.hesa_value.EXISTS(41) AND
3386: g_field_defn.hesa_value.EXISTS(26) AND
3387: g_field_defn.hesa_value.EXISTS(148) AND
3388: igs_he_extract_fields_pkg.is_ucas_ftug
3389: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3390: p_hesa_commdate => g_field_defn.hesa_value(26),
3391: p_ucasnum => g_field_defn.hesa_value(148),
3392: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3403: IF g_field_defn.hesa_value.EXISTS(179) AND g_field_defn.hesa_value(179) IS NOT NULL
3404: THEN
3405: p_value := g_field_defn.hesa_value(179);
3406:
3407: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3408: p_person_number => g_pe_person.person_number,
3409: p_course_cd => p_course_cd,
3410: p_hesa_qual => 'GCEATS',
3411: p_tariff_score => p_value);
3416: IF p_value IS NULL AND
3417: g_field_defn.hesa_value.EXISTS(41) AND
3418: g_field_defn.hesa_value.EXISTS(26) AND
3419: g_field_defn.hesa_value.EXISTS(148) AND
3420: igs_he_extract_fields_pkg.is_ucas_ftug
3421: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3422: p_hesa_commdate => g_field_defn.hesa_value(26),
3423: p_ucasnum => g_field_defn.hesa_value(148),
3424: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3432:
3433: g_default_pro := 'N';
3434:
3435: -- Number of VCE A Levels
3436: igs_he_extract_fields_pkg.get_qual_dets
3437: (p_person_id => p_person_id,
3438: p_course_cd => p_course_cd,
3439: p_hesa_qual => 'VCSEA',
3440: p_no_of_qual => p_value,
3440: p_no_of_qual => p_value,
3441: p_tariff_score => g_field_defn.hesa_value(181));
3442:
3443: IF p_value IS NOT NULL THEN
3444: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3445: p_person_number => g_pe_person.person_number,
3446: p_course_cd => p_course_cd,
3447: p_hesa_qual => 'VCEAN',
3448: p_no_of_qual => p_value);
3450: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3451: IF g_field_defn.hesa_value.EXISTS(41) AND
3452: g_field_defn.hesa_value.EXISTS(26) AND
3453: g_field_defn.hesa_value.EXISTS(148) AND
3454: igs_he_extract_fields_pkg.is_ucas_ftug
3455: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3456: p_hesa_commdate => g_field_defn.hesa_value(26),
3457: p_ucasnum => g_field_defn.hesa_value(148),
3458: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3472: IF g_field_defn.hesa_value.EXISTS(181) AND g_field_defn.hesa_value(181) IS NOT NULL
3473: THEN
3474: p_value := g_field_defn.hesa_value(181);
3475:
3476: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3477: p_person_number => g_pe_person.person_number,
3478: p_course_cd => p_course_cd,
3479: p_hesa_qual => 'VCEATS',
3480: p_tariff_score => p_value);
3485: IF p_value IS NULL AND
3486: g_field_defn.hesa_value.EXISTS(41) AND
3487: g_field_defn.hesa_value.EXISTS(26) AND
3488: g_field_defn.hesa_value.EXISTS(148) AND
3489: igs_he_extract_fields_pkg.is_ucas_ftug
3490: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3491: p_hesa_commdate => g_field_defn.hesa_value(26),
3492: p_ucasnum => g_field_defn.hesa_value(148),
3493: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3500:
3501: g_default_pro := 'N';
3502:
3503: -- Number of Key Skill Qualifications
3504: igs_he_extract_fields_pkg.get_qual_dets
3505: (p_person_id => p_person_id,
3506: p_course_cd => p_course_cd,
3507: p_hesa_qual => 'KEYSKL',
3508: p_no_of_qual => p_value,
3508: p_no_of_qual => p_value,
3509: p_tariff_score => g_field_defn.hesa_value(183));
3510:
3511: IF p_value IS NOT NULL THEN
3512: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3513: p_person_number => g_pe_person.person_number,
3514: p_course_cd => p_course_cd,
3515: p_hesa_qual => 'KSQN',
3516: p_no_of_qual => p_value);
3518: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3519: IF g_field_defn.hesa_value.EXISTS(41) AND
3520: g_field_defn.hesa_value.EXISTS(26) AND
3521: g_field_defn.hesa_value.EXISTS(148) AND
3522: igs_he_extract_fields_pkg.is_ucas_ftug
3523: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3524: p_hesa_commdate => g_field_defn.hesa_value(26),
3525: p_ucasnum => g_field_defn.hesa_value(148),
3526: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3539: IF g_field_defn.hesa_value.EXISTS(183) AND g_field_defn.hesa_value(183) IS NOT NULL
3540: THEN
3541: p_value := g_field_defn.hesa_value(183);
3542:
3543: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3544: p_person_number => g_pe_person.person_number,
3545: p_course_cd => p_course_cd,
3546: p_hesa_qual => 'KSQTS',
3547: p_tariff_score => p_value);
3552: IF p_value IS NULL AND
3553: g_field_defn.hesa_value.EXISTS(41) AND
3554: g_field_defn.hesa_value.EXISTS(26) AND
3555: g_field_defn.hesa_value.EXISTS(148) AND
3556: igs_he_extract_fields_pkg.is_ucas_ftug
3557: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3558: p_hesa_commdate => g_field_defn.hesa_value(26),
3559: p_ucasnum => g_field_defn.hesa_value(148),
3560: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3568:
3569: g_default_pro := 'N';
3570:
3571: -- Number of 1 unit key skill awards
3572: igs_he_extract_fields_pkg.get_qual_dets
3573: (p_person_id => p_person_id,
3574: p_course_cd => p_course_cd,
3575: p_hesa_qual => '1UNKEYSKL',
3576: p_no_of_qual => p_value,
3576: p_no_of_qual => p_value,
3577: p_tariff_score => g_field_defn.hesa_value(185));
3578:
3579: IF p_value IS NOT NULL THEN
3580: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3581: p_person_number => g_pe_person.person_number,
3582: p_course_cd => p_course_cd,
3583: p_hesa_qual => 'UKSAN',
3584: p_no_of_qual => p_value);
3586: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3587: IF g_field_defn.hesa_value.EXISTS(41) AND
3588: g_field_defn.hesa_value.EXISTS(26) AND
3589: g_field_defn.hesa_value.EXISTS(148) AND
3590: igs_he_extract_fields_pkg.is_ucas_ftug
3591: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3592: p_hesa_commdate => g_field_defn.hesa_value(26),
3593: p_ucasnum => g_field_defn.hesa_value(148),
3594: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3607: IF g_field_defn.hesa_value.EXISTS(185) AND g_field_defn.hesa_value(185) IS NOT NULL
3608: THEN
3609: p_value := g_field_defn.hesa_value(185);
3610:
3611: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3612: p_person_number => g_pe_person.person_number,
3613: p_course_cd => p_course_cd,
3614: p_hesa_qual => 'UKSATS',
3615: p_tariff_score => p_value);
3620: IF p_value IS NULL AND
3621: g_field_defn.hesa_value.EXISTS(41) AND
3622: g_field_defn.hesa_value.EXISTS(26) AND
3623: g_field_defn.hesa_value.EXISTS(148) AND
3624: igs_he_extract_fields_pkg.is_ucas_ftug
3625: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3626: p_hesa_commdate => g_field_defn.hesa_value(26),
3627: p_ucasnum => g_field_defn.hesa_value(148),
3628: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3635:
3636: g_default_pro := 'N';
3637:
3638: -- Number of Scottish Advanced Higher Levels
3639: igs_he_extract_fields_pkg.get_qual_dets
3640: (p_person_id => p_person_id,
3641: p_course_cd => p_course_cd,
3642: p_hesa_qual => 'SCOTADH',
3643: p_no_of_qual => p_value,
3643: p_no_of_qual => p_value,
3644: p_tariff_score => g_field_defn.hesa_value(187));
3645:
3646: IF p_value IS NOT NULL THEN
3647: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3648: p_person_number => g_pe_person.person_number,
3649: p_course_cd => p_course_cd,
3650: p_hesa_qual => 'SAHN',
3651: p_no_of_qual => p_value);
3653: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3654: IF g_field_defn.hesa_value.EXISTS(41) AND
3655: g_field_defn.hesa_value.EXISTS(26) AND
3656: g_field_defn.hesa_value.EXISTS(148) AND
3657: igs_he_extract_fields_pkg.is_ucas_ftug
3658: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3659: p_hesa_commdate => g_field_defn.hesa_value(26),
3660: p_ucasnum => g_field_defn.hesa_value(148),
3661: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3674: IF g_field_defn.hesa_value.EXISTS(187) AND g_field_defn.hesa_value(187) IS NOT NULL
3675: THEN
3676: p_value := g_field_defn.hesa_value(187);
3677:
3678: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3679: p_person_number => g_pe_person.person_number,
3680: p_course_cd => p_course_cd,
3681: p_hesa_qual => 'SAHTS',
3682: p_tariff_score => p_value);
3686: IF p_value IS NULL AND
3687: g_field_defn.hesa_value.EXISTS(41) AND
3688: g_field_defn.hesa_value.EXISTS(26) AND
3689: g_field_defn.hesa_value.EXISTS(148) AND
3690: igs_he_extract_fields_pkg.is_ucas_ftug
3691: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3692: p_hesa_commdate => g_field_defn.hesa_value(26),
3693: p_ucasnum => g_field_defn.hesa_value(148),
3694: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3701:
3702: g_default_pro := 'N';
3703:
3704: -- Number of Scottish Higher Levels
3705: igs_he_extract_fields_pkg.get_qual_dets
3706: (p_person_id => p_person_id,
3707: p_course_cd => p_course_cd,
3708: p_hesa_qual => 'SCOTH',
3709: p_no_of_qual => p_value,
3709: p_no_of_qual => p_value,
3710: p_tariff_score => g_field_defn.hesa_value(189));
3711:
3712: IF p_value IS NOT NULL THEN
3713: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3714: p_person_number => g_pe_person.person_number,
3715: p_course_cd => p_course_cd,
3716: p_hesa_qual => 'SHN',
3717: p_no_of_qual => p_value);
3719: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3720: IF g_field_defn.hesa_value.EXISTS(41) AND
3721: g_field_defn.hesa_value.EXISTS(26) AND
3722: g_field_defn.hesa_value.EXISTS(148) AND
3723: igs_he_extract_fields_pkg.is_ucas_ftug
3724: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3725: p_hesa_commdate => g_field_defn.hesa_value(26),
3726: p_ucasnum => g_field_defn.hesa_value(148),
3727: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3739: -- Scottish Higher level Tariff Score
3740: IF g_field_defn.hesa_value.EXISTS(189) AND g_field_defn.hesa_value(189) IS NOT NULL
3741: THEN
3742: p_value := g_field_defn.hesa_value(189);
3743: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3744: p_person_number => g_pe_person.person_number,
3745: p_course_cd => p_course_cd,
3746: p_hesa_qual => 'SHTS',
3747: p_tariff_score => p_value);
3752: IF p_value IS NULL AND
3753: g_field_defn.hesa_value.EXISTS(41) AND
3754: g_field_defn.hesa_value.EXISTS(26) AND
3755: g_field_defn.hesa_value.EXISTS(148) AND
3756: igs_he_extract_fields_pkg.is_ucas_ftug
3757: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3758: p_hesa_commdate => g_field_defn.hesa_value(26),
3759: p_ucasnum => g_field_defn.hesa_value(148),
3760: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3767:
3768: g_default_pro := 'N';
3769:
3770: -- Number of Scottish Intermediate Levels
3771: igs_he_extract_fields_pkg.get_qual_dets
3772: (p_person_id => p_person_id,
3773: p_course_cd => p_course_cd,
3774: p_hesa_qual => 'SCOTI2',
3775: p_no_of_qual => p_value,
3775: p_no_of_qual => p_value,
3776: p_tariff_score => g_field_defn.hesa_value(191));
3777:
3778: IF p_value IS NOT NULL THEN
3779: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3780: p_person_number => g_pe_person.person_number,
3781: p_course_cd => p_course_cd,
3782: p_hesa_qual => 'SI2N',
3783: p_no_of_qual => p_value);
3785: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3786: IF g_field_defn.hesa_value.EXISTS(41) AND
3787: g_field_defn.hesa_value.EXISTS(26) AND
3788: g_field_defn.hesa_value.EXISTS(148) AND
3789: igs_he_extract_fields_pkg.is_ucas_ftug
3790: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3791: p_hesa_commdate => g_field_defn.hesa_value(26),
3792: p_ucasnum => g_field_defn.hesa_value(148),
3793: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3805: -- Scottish Intermediate level Tariff Score
3806: IF g_field_defn.hesa_value.EXISTS(191) AND g_field_defn.hesa_value(191) IS NOT NULL
3807: THEN
3808: p_value := g_field_defn.hesa_value(191);
3809: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3810: p_person_number => g_pe_person.person_number,
3811: p_course_cd => p_course_cd,
3812: p_hesa_qual => 'SI2TS',
3813: p_tariff_score => p_value);
3818: IF p_value IS NULL AND
3819: g_field_defn.hesa_value.EXISTS(41) AND
3820: g_field_defn.hesa_value.EXISTS(26) AND
3821: g_field_defn.hesa_value.EXISTS(148) AND
3822: igs_he_extract_fields_pkg.is_ucas_ftug
3823: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3824: p_hesa_commdate => g_field_defn.hesa_value(26),
3825: p_ucasnum => g_field_defn.hesa_value(148),
3826: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3833:
3834: g_default_pro := 'N';
3835:
3836: -- Number of Scottish Standard Grade Credit Levels
3837: igs_he_extract_fields_pkg.get_qual_dets
3838: (p_person_id => p_person_id,
3839: p_course_cd => p_course_cd,
3840: p_hesa_qual => 'SCOTST',
3841: p_no_of_qual => p_value,
3841: p_no_of_qual => p_value,
3842: p_tariff_score => g_field_defn.hesa_value(193));
3843:
3844: IF p_value IS NOT NULL THEN
3845: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3846: p_person_number => g_pe_person.person_number,
3847: p_course_cd => p_course_cd,
3848: p_hesa_qual => 'SSGCN',
3849: p_no_of_qual => p_value);
3851: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3852: IF g_field_defn.hesa_value.EXISTS(41) AND
3853: g_field_defn.hesa_value.EXISTS(26) AND
3854: g_field_defn.hesa_value.EXISTS(148) AND
3855: igs_he_extract_fields_pkg.is_ucas_ftug
3856: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3857: p_hesa_commdate => g_field_defn.hesa_value(26),
3858: p_ucasnum => g_field_defn.hesa_value(148),
3859: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3872: IF g_field_defn.hesa_value.EXISTS(193) AND g_field_defn.hesa_value(193) IS NOT NULL
3873: THEN
3874: p_value := g_field_defn.hesa_value(193);
3875:
3876: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3877: p_person_number => g_pe_person.person_number,
3878: p_course_cd => p_course_cd,
3879: p_hesa_qual => 'SSGCTS',
3880: p_tariff_score => p_value);
3885: IF p_value IS NULL AND
3886: g_field_defn.hesa_value.EXISTS(41) AND
3887: g_field_defn.hesa_value.EXISTS(26) AND
3888: g_field_defn.hesa_value.EXISTS(148) AND
3889: igs_he_extract_fields_pkg.is_ucas_ftug
3890: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3891: p_hesa_commdate => g_field_defn.hesa_value(26),
3892: p_ucasnum => g_field_defn.hesa_value(148),
3893: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3900:
3901: g_default_pro := 'N';
3902:
3903: -- Number of Scottish Core Skills Levels
3904: igs_he_extract_fields_pkg.get_qual_dets
3905: (p_person_id => p_person_id,
3906: p_course_cd => p_course_cd,
3907: p_hesa_qual => 'SCOTCO',
3908: p_no_of_qual => p_value,
3908: p_no_of_qual => p_value,
3909: p_tariff_score => g_field_defn.hesa_value(195));
3910:
3911: IF p_value IS NOT NULL THEN
3912: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3913: p_person_number => g_pe_person.person_number,
3914: p_course_cd => p_course_cd,
3915: p_hesa_qual => 'SCSN',
3916: p_no_of_qual => p_value);
3918: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3919: IF g_field_defn.hesa_value.EXISTS(41) AND
3920: g_field_defn.hesa_value.EXISTS(26) AND
3921: g_field_defn.hesa_value.EXISTS(148) AND
3922: igs_he_extract_fields_pkg.is_ucas_ftug
3923: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3924: p_hesa_commdate => g_field_defn.hesa_value(26),
3925: p_ucasnum => g_field_defn.hesa_value(148),
3926: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3938: -- Scottish Core Skills level Tariff Score
3939: IF g_field_defn.hesa_value.EXISTS(195) AND g_field_defn.hesa_value(195) IS NOT NULL
3940: THEN
3941: p_value := g_field_defn.hesa_value(195);
3942: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
3943: p_person_number => g_pe_person.person_number,
3944: p_course_cd => p_course_cd,
3945: p_hesa_qual => 'SCSTS',
3946: p_tariff_score => p_value);
3951: IF p_value IS NULL AND
3952: g_field_defn.hesa_value.EXISTS(41) AND
3953: g_field_defn.hesa_value.EXISTS(26) AND
3954: g_field_defn.hesa_value.EXISTS(148) AND
3955: igs_he_extract_fields_pkg.is_ucas_ftug
3956: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3957: p_hesa_commdate => g_field_defn.hesa_value(26),
3958: p_ucasnum => g_field_defn.hesa_value(148),
3959: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
3966:
3967: g_default_pro := 'N';
3968:
3969: -- Number of Advanced Extension
3970: igs_he_extract_fields_pkg.get_qual_dets
3971: (p_person_id => p_person_id,
3972: p_course_cd => p_course_cd,
3973: p_hesa_qual => 'ADVEXT',
3974: p_no_of_qual => p_value,
3974: p_no_of_qual => p_value,
3975: p_tariff_score => g_field_defn.hesa_value(197));
3976:
3977: IF p_value IS NOT NULL THEN
3978: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
3979: p_person_number => g_pe_person.person_number,
3980: p_course_cd => p_course_cd,
3981: p_hesa_qual => 'AEAN',
3982: p_no_of_qual => p_value);
3984: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
3985: IF g_field_defn.hesa_value.EXISTS(41) AND
3986: g_field_defn.hesa_value.EXISTS(26) AND
3987: g_field_defn.hesa_value.EXISTS(148) AND
3988: igs_he_extract_fields_pkg.is_ucas_ftug
3989: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
3990: p_hesa_commdate => g_field_defn.hesa_value(26),
3991: p_ucasnum => g_field_defn.hesa_value(148),
3992: p_min_commdate => TO_DATE('31/07/2003', 'DD/MM/YYYY'))
4004: -- Advanced Extension Tariff Score
4005: IF g_field_defn.hesa_value.EXISTS(197) AND g_field_defn.hesa_value(197) IS NOT NULL
4006: THEN
4007: p_value := g_field_defn.hesa_value(197);
4008: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
4009: p_person_number => g_pe_person.person_number,
4010: p_course_cd => p_course_cd,
4011: p_hesa_qual => 'AENTS',
4012: p_tariff_score => p_value);
4017: IF p_value IS NULL AND
4018: g_field_defn.hesa_value.EXISTS(41) AND
4019: g_field_defn.hesa_value.EXISTS(26) AND
4020: g_field_defn.hesa_value.EXISTS(148) AND
4021: igs_he_extract_fields_pkg.is_ucas_ftug
4022: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
4023: p_hesa_commdate => g_field_defn.hesa_value(26),
4024: p_ucasnum => g_field_defn.hesa_value(148),
4025: p_min_commdate => TO_DATE('31/07/2003', 'DD/MM/YYYY'))
4036: p_value := g_he_st_spa.total_ucas_tariff;
4037:
4038: IF p_value IS NOT NULL
4039: THEN
4040: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
4041: p_person_number => g_pe_person.person_number,
4042: p_course_cd => p_course_cd,
4043: p_hesa_qual => 'TOTALTS',
4044: p_tariff_score => p_value);
4045: ELSE
4046: IF g_field_defn.hesa_value.EXISTS(41) AND
4047: g_field_defn.hesa_value.EXISTS(26) AND
4048: g_field_defn.hesa_value.EXISTS(148) AND
4049: igs_he_extract_fields_pkg.is_ucas_ftug
4050: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
4051: p_hesa_commdate => g_field_defn.hesa_value(26),
4052: p_ucasnum => g_field_defn.hesa_value(148),
4053: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
4058:
4059: ELSIF p_field_number = 199
4060: THEN
4061: -- Number of CACHE qualifications
4062: igs_he_extract_fields_pkg.get_qual_dets
4063: (p_person_id => p_person_id,
4064: p_course_cd => p_course_cd,
4065: p_hesa_qual => 'CACHE',
4066: p_no_of_qual => p_value,
4076:
4077: ELSIF p_field_number = 201
4078: THEN
4079: -- Number of BTEC
4080: igs_he_extract_fields_pkg.get_qual_dets
4081: (p_person_id => p_person_id,
4082: p_course_cd => p_course_cd,
4083: p_hesa_qual => 'BTEC',
4084: p_no_of_qual => p_value,
4094:
4095: ELSIF p_field_number = 203
4096: THEN
4097: -- International Baccalaureate Tariff Score
4098: igs_he_extract_fields_pkg.get_qual_dets
4099: (p_person_id => p_person_id,
4100: p_course_cd => p_course_cd,
4101: p_hesa_qual => 'INBA',
4102: p_no_of_qual => l_dummy,
4104:
4105: ELSIF p_field_number = 204
4106: THEN
4107: -- Irish Leaving certificate tariff score
4108: igs_he_extract_fields_pkg.get_qual_dets
4109: (p_person_id => p_person_id,
4110: p_course_cd => p_course_cd,
4111: p_hesa_qual => 'ILC',
4112: p_no_of_qual => l_dummy,
4114:
4115: ELSIF p_field_number = 205
4116: THEN
4117: -- Music, Drama and Performing Arts
4118: igs_he_extract_fields_pkg.get_qual_dets
4119: (p_person_id => p_person_id,
4120: p_course_cd => p_course_cd,
4121: p_hesa_qual => 'MUDRPA',
4122: p_no_of_qual => l_dummy,
4140:
4141: ELSIF p_field_number = 208
4142: THEN
4143: -- Level of Credit, 3
4144: igs_he_extract_fields_pkg.get_credit_level
4145: (p_susa_credit_level => g_he_en_susa.credit_level3,
4146: p_poous_credit_level => g_he_poous.level_credit3,
4147: p_hesa_credit_level => p_value);
4148:
4148:
4149: ELSIF p_field_number = 209
4150: THEN
4151: -- Level of Credit, 4
4152: igs_he_extract_fields_pkg.get_credit_level
4153: (p_susa_credit_level => g_he_en_susa.credit_level4 ,
4154: p_poous_credit_level => g_he_poous.level_credit4,
4155: p_hesa_credit_level => p_value);
4156:
4160: IF g_field_defn.hesa_value.EXISTS(210)
4161: THEN
4162: p_value := g_field_defn.hesa_value(210);
4163: ELSE
4164: igs_he_extract_fields_pkg.get_credit_obtained
4165: (p_person_id => p_person_id,
4166: p_course_cd => p_course_cd,
4167: p_prog_calc => g_he_st_prog.program_calc,
4168: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
4193: IF g_field_defn.hesa_value.EXISTS(211)
4194: THEN
4195: p_value := g_field_defn.hesa_value(211);
4196: ELSE
4197: igs_he_extract_fields_pkg.get_credit_obtained
4198: (p_person_id => p_person_id,
4199: p_course_cd => p_course_cd,
4200: p_prog_calc => g_he_st_prog.program_calc,
4201: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
4226: IF g_field_defn.hesa_value.EXISTS(212)
4227: THEN
4228: p_value := g_field_defn.hesa_value(212);
4229: ELSE
4230: igs_he_extract_fields_pkg.get_credit_obtained
4231: (p_person_id => p_person_id,
4232: p_course_cd => p_course_cd,
4233: p_prog_calc => g_he_st_prog.program_calc,
4234: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
4257: IF g_field_defn.hesa_value.EXISTS(213)
4258: THEN
4259: p_value := g_field_defn.hesa_value(213);
4260: ELSE
4261: igs_he_extract_fields_pkg.get_credit_obtained
4262: (p_person_id => p_person_id,
4263: p_course_cd => p_course_cd,
4264: p_prog_calc => g_he_st_prog.program_calc,
4265: p_susa_crd_pt1 => g_he_en_susa.credit_pt_achieved1,
4284:
4285: ELSIF p_field_number = 214
4286: THEN
4287: -- Marital Status
4288: igs_he_extract_fields_pkg.get_marital_status
4289: (p_oss_marital_status => g_pe_stat_v.marital_status,
4290: p_hesa_marital_status => p_value);
4291:
4292: ELSIF p_field_number = 215
4291:
4292: ELSIF p_field_number = 215
4293: THEN
4294: -- Dependants
4295: igs_he_extract_fields_pkg.get_dependants
4296: (p_oss_dependants => g_he_st_spa.dependants_cd,
4297: p_hesa_dependants => p_value);
4298:
4299: ELSIF p_field_number = 216
4306:
4307: -- Student must be FE student
4308: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4309:
4310: igs_he_extract_fields_pkg.get_enh_fund_elig
4311: (p_susa_enh_fund_elig => g_he_en_susa.enh_fund_elig_cd,
4312: p_spa_enh_fund_elig => g_he_st_spa.enh_fund_elig_cd,
4313: p_hesa_enh_fund_elig => p_value);
4314:
4348:
4349: -- Student must be FE student
4350: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4351:
4352: igs_he_extract_fields_pkg.get_learn_dif
4353: (p_person_id => p_person_id,
4354: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4355: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
4356: p_hesa_disability_type => p_value);
4395:
4396: -- Student must be FE student
4397: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4398:
4399: igs_he_extract_fields_pkg.get_gov_init
4400: (p_spa_gov_initiatives_cd => g_he_st_spa.gov_initiatives_cd,
4401: p_prog_gov_initiatives_cd => g_he_st_prog.gov_initiatives_cd,
4402: p_hesa_gov_initiatives_cd => p_value);
4403:
4416:
4417: -- Student must be FE student
4418: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4419:
4420: igs_he_extract_fields_pkg.get_units_completed
4421: (p_person_id => p_person_id,
4422: p_course_cd => p_course_cd,
4423: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
4424: p_spa_units_completed => g_he_st_spa.units_completed,
4465:
4466: -- Student must be FE student
4467: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4468:
4469: igs_he_extract_fields_pkg.get_disadv_uplift_elig
4470: (p_spa_disadv_uplift_elig_cd => g_he_st_spa.disadv_uplift_elig_cd,
4471: p_prog_disadv_uplift_elig_cd => g_he_st_prog.disadv_uplift_elig_cd,
4472: p_hesa_disadv_uplift_elig_cd => p_value);
4473:
4508:
4509: -- Student must be FE student
4510: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4511:
4512: igs_he_extract_fields_pkg.get_franch_out_arr
4513: (p_spa_franch_out_arr_cd => g_he_st_spa.franch_out_arr_cd,
4514: p_prog_franch_out_arr_cd => g_he_st_prog.franch_out_arr_cd,
4515: p_hesa_franch_out_arr_cd => p_value);
4516:
4529:
4530: -- Student must be FE student
4531: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
4532:
4533: igs_he_extract_fields_pkg.get_employer_role
4534: (p_spa_employer_role_cd => g_he_st_spa.employer_role_cd,
4535: p_hesa_employer_role_cd => p_value);
4536:
4537: --If the value is calculated as NULL above then do the default processing for this field
4546:
4547: IF g_field_defn.hesa_value.EXISTS(12) AND
4548: g_field_defn.hesa_value(12) IN ('6826') THEN
4549:
4550: igs_he_extract_fields_pkg.get_welsh_bacc_qual(p_person_id => p_person_id,
4551: p_welsh_bacc => p_value);
4552:
4553: IF p_value = '3' THEN
4554:
4632: Change History :
4633: Who When What
4634: bayadav 25-Mar-02 Changed the code for field 26 as a part of HECR002.
4635: bayadav 26-Mar-02 Changed the code for field 74 as a part of HECR001.
4636: bayadav 19-Jul-02 Changed the call for IGS_HE_EXTRACT_FIELDS_PKG.get_year_of_student proc for fields 27 and 30 as per bug 2449010
4637: smaddali 23-Jul-02 Modified field 76 to call new procedure for bug 2452592
4638: bayadav 24-Oct-02 Modified the logic for field 148,4,33,43,44,45,46,49,50,153,6,14,17,27,29,
4639: 34,36,42,83,84,85,54,55,56,57,58,59,60,61,62,63,78,140,141,146 as a part of HEFD101(2636897)
4640: bayadav 02-Dec-02 Included 'WALES' also in the counrty list for field 140 as a part fo bug 2685091.Also made the default processing TRUE for other countries
4693:
4694: ELSIF p_field_number = 2
4695: THEN
4696: -- Hesa Institution Id
4697: igs_he_extract_fields_pkg.get_hesa_inst_id
4698: (p_hesa_inst_id => p_value);
4699:
4700: ELSIF p_field_number = 3
4701: THEN
4699:
4700: ELSIF p_field_number = 3
4701: THEN
4702: -- Campus Id
4703: igs_he_extract_fields_pkg.get_campus_id
4704: (p_location_cd => g_en_stdnt_ps_att.location_cd,
4705: p_campus_id => p_value);
4706:
4707: ELSIF p_field_number = 4
4715: l_inst_id := 0;
4716: END IF;
4717:
4718: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4719: igs_he_extract_fields_pkg.get_stdnt_id
4720: (p_person_id => p_person_id,
4721: p_inst_id => l_inst_id,
4722: p_stdnt_id => p_value,
4723: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4728: -- Scottish Candidate Number
4729: IF g_he_st_spa.associate_scott_cand = 'Y'
4730: THEN
4731: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4732: igs_he_extract_fields_pkg.get_alt_pers_id
4733: (p_person_id => p_person_id,
4734: p_id_type => 'UCASREGNO',
4735: p_api_id => p_value,
4736: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4742: -- FE Student Marker
4743: -- First get the Funding Source
4744: -- smaddali modified this call to pass funding_source field for hefd208 - bug#2717751
4745: -- jtmathew modified this call to pass funding_source field at spa level - bug#3962575
4746: igs_he_extract_fields_pkg.get_funding_src
4747: (p_course_cd => p_course_cd,
4748: p_version_number => p_crv_version_number,
4749: p_spa_fund_src => g_en_stdnt_ps_att.funding_source,
4750: p_poous_fund_src => g_he_poous.funding_source,
4753:
4754: -- Next get the Fundability Code
4755: IF g_field_defn.oss_value.EXISTS(64) THEN
4756: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4757: igs_he_extract_fields_pkg.get_fundability_cd
4758: (p_person_id => p_person_id,
4759: p_susa_fund_cd => g_he_en_susa.fundability_code,
4760: p_spa_funding_source => g_en_stdnt_ps_att.funding_source,
4761: p_poous_fund_cd => g_he_poous.fundability_cd,
4768: END IF;
4769:
4770: -- Now get the FE Student Marker
4771: IF g_he_submsn_header.validation_country IN ('ENGLAND','WALES') AND g_field_defn.oss_value.EXISTS(64) AND g_field_defn.oss_value.EXISTS(65) THEN
4772: igs_he_extract_fields_pkg.get_fe_stdnt_mrker
4773: (p_spa_fe_stdnt_mrker => g_he_st_spa.fe_student_marker,
4774: p_fe_program_marker => g_he_st_prog.fe_program_marker,
4775: p_funding_src => g_field_defn.oss_value(64),
4776: p_fundability_cd => g_field_defn.oss_value(65),
4824: THEN
4825: -- Family Name on 16th Birthday
4826: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4827: l_fmly_name := NULL;
4828: igs_he_extract_fields_pkg.get_fmly_name_on_16_bday
4829: (p_person_id => p_person_id,
4830: p_fmly_name => l_fmly_name,
4831: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4832: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
4844:
4845: ELSIF p_field_number = 11
4846: THEN
4847: -- Gender
4848: igs_he_extract_fields_pkg.get_gender
4849: (p_gender => g_pe_person.sex,
4850: p_hesa_gender => p_value);
4851:
4852: ELSIF p_field_number = 12
4851:
4852: ELSIF p_field_number = 12
4853: THEN
4854: -- Domicile
4855: igs_he_extract_fields_pkg.get_domicile
4856: (p_ad_domicile => g_he_ad_dtl.domicile_cd,
4857: p_spa_domicile => g_he_st_spa.domicile_cd,
4858: p_hesa_domicile => p_value);
4859:
4860: ELSIF p_field_number = 13
4861: THEN
4862: -- Nationality
4863: -- smaddali 11-dec-03 Modified for bug#3235753 , added 1 new parameter
4864: igs_he_extract_fields_pkg.get_nationality
4865: (p_person_id => p_person_id,
4866: p_nationality => p_value,
4867: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date);
4868:
4870: -- as part of HEFD311 - July 2004 Changes enhancement bug, 2956444
4871: ELSIF p_field_number = 14
4872: THEN
4873: -- Ethnicity
4874: igs_he_extract_fields_pkg.get_ethnicity (
4875: p_person_id => p_person_id,
4876: p_oss_eth => g_pe_stat_v.ethnic_origin_id,
4877: p_hesa_eth => p_value);
4878:
4878:
4879: ELSIF p_field_number = 15
4880: THEN
4881: -- Disability Allowance
4882: igs_he_extract_fields_pkg.get_disablity_allow
4883: (p_oss_dis_allow => g_he_en_susa.disability_allow,
4884: p_hesa_dis_allow => p_value);
4885:
4886: ELSIF p_field_number = 16
4886: ELSIF p_field_number = 16
4887: THEN
4888: -- Diability
4889: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4890: igs_he_extract_fields_pkg.get_disablity
4891: (p_person_id => p_person_id,
4892: p_disability => p_value,
4893: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4894: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
4898:
4899:
4900: IF (( g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3)) AND g_he_submsn_header.validation_country = 'ENGLAND') THEN
4901: -- Additional Support Band
4902: igs_he_extract_fields_pkg.get_addnl_supp_band
4903: (p_oss_supp_band => g_he_en_susa.additional_sup_band,
4904: p_hesa_supp_band => p_value);
4905:
4906: ELSE
4918: THEN
4919:
4920: --Get the field 41 value - Qualification Aim
4921: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
4922: igs_he_extract_fields_pkg.get_gen_qual_aim
4923: (p_person_id => p_person_id,
4924: p_course_cd => p_course_cd,
4925: p_version_number => p_crv_version_number,
4926: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
4929: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
4930: p_awd_conf_start_dt => l_awd_conf_start_dt);
4931:
4932: -- Get the field 70 value - Mode of Study
4933: igs_he_extract_fields_pkg.get_mode_of_study
4934: (p_person_id => p_person_id,
4935: p_course_cd => p_course_cd,
4936: p_version_number => p_crv_version_number,
4937: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4946:
4947: -- Get the field 133 value - UCAS NUM
4948: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
4949: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
4950: igs_he_extract_fields_pkg.get_ucasnum
4951: (p_person_id => p_person_id,
4952: p_ucasnum => g_field_defn.hesa_value(133),
4953: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
4954: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
4958: END IF;
4959:
4960: IF g_field_defn.hesa_value.EXISTS(41) AND g_field_defn.hesa_value.EXISTS(133) THEN
4961: -- Calculate the field 19 value - Year left last institution
4962: igs_he_extract_fields_pkg.get_yr_left_last_inst
4963: (p_person_id => p_person_id,
4964: p_com_dt => g_en_stdnt_ps_att.commencement_dt,
4965: p_hesa_gen_qaim => g_field_defn.hesa_value(41),
4966: p_ucasnum => g_field_defn.hesa_value(133),
4997:
4998: -- Get the field 133 value - UCAS NUM
4999: IF NOT g_field_defn.hesa_value.EXISTS(133) THEN
5000: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
5001: igs_he_extract_fields_pkg.get_ucasnum
5002: (p_person_id => p_person_id,
5003: p_ucasnum => g_field_defn.hesa_value(133),
5004: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5005: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
5021: ELSIF p_field_number = 26
5022: THEN
5023:
5024: -- Commencement Date
5025: igs_he_extract_fields_pkg.get_commencement_dt( p_hesa_commdate => g_he_st_spa.commencement_dt,
5026: p_enstdnt_commdate => g_en_stdnt_ps_att.commencement_dt,
5027: p_person_id => p_person_id ,
5028: p_course_cd => p_course_cd ,
5029: p_version_number => p_crv_version_number,
5037: -- smaddali removed the call to derive field 72 value and added the call to derive field 30
5038: -- because the dependency should be on field 30 not on 72 , bug 2452551
5039: -- First get field 30, Year of student
5040: -- jbaber added p_susa_year_of_student for HEFD350
5041: igs_he_extract_fields_pkg.get_year_of_student
5042: (p_person_id => p_person_id ,
5043: p_course_cd => p_course_cd ,
5044: p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
5045: p_sequence_number => g_as_su_setatmpt.sequence_number,
5050: --get field 41 value also first as it is required for this calculation
5051: -- Qualification Aim
5052: IF NOT g_field_defn.hesa_value.EXISTS(41) THEN
5053: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
5054: igs_he_extract_fields_pkg.get_gen_qual_aim
5055: (p_person_id => p_person_id,
5056: p_course_cd => p_course_cd,
5057: p_version_number => p_crv_version_number,
5058: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
5073: (g_field_defn.hesa_value(41) >= 02 and g_field_defn.hesa_value(41) <= 52)
5074: or
5075: (g_field_defn.hesa_value(41) IN (61,62,97,98))
5076: ) ) THEN
5077: igs_he_extract_fields_pkg.get_new_ent_to_he
5078: (p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
5079: p_susa_new_ent_to_he => g_he_en_susa.new_he_entrant_cd,
5080: p_yop => g_field_defn.hesa_value(30),
5081: p_high_qual_on_ent => g_field_defn.hesa_value(21),
5099:
5100: ELSIF p_field_number = 28
5101: THEN
5102: -- Special students
5103: igs_he_extract_fields_pkg.get_special_student
5104: (p_ad_special_student => g_he_ad_dtl.special_student_cd,
5105: p_spa_special_student => g_he_st_spa.special_student,
5106: p_oss_special_student => g_field_defn.oss_value(28),
5107: p_hesa_special_student => p_value);
5109: ELSIF p_field_number = 29
5110: THEN
5111:
5112: --get the quail1 field 37 and 38 value required in calculating field 29 value
5113: igs_he_extract_fields_pkg.get_qual_obtained
5114: (p_person_id => p_person_id,
5115: p_course_cd => p_course_cd,
5116: p_enrl_start_dt => l_awd_conf_start_dt,
5117: p_enrl_end_dt => l_awd_conf_end_dt,
5122: p_classification => g_field_defn.hesa_value(39));
5123:
5124:
5125: --Calculating field 53 first requred for field 146 calcualtion
5126: igs_he_extract_fields_pkg.get_teach_train_crs_id
5127: (p_prg_ttcid => g_he_st_prog.teacher_train_prog_id,
5128: p_spa_ttcid => g_he_st_spa.teacher_train_prog_id,
5129: p_hesa_ttcid => g_field_defn.hesa_value(53));
5130:
5131:
5132: --Calculating field 146 first required for field 29
5133: -- Outcome of ITT Program
5134: IF g_he_submsn_header.validation_country IN ('ENGLAND','WALES') THEN
5135: igs_he_extract_fields_pkg.get_itt_outcome
5136: (p_oss_itt_outcome => g_he_st_spa.itt_prog_outcome,
5137: p_teach_train_prg => g_field_defn.hesa_value(53),
5138: p_hesa_itt_outcome => g_field_defn.hesa_value(146));
5139: END IF;
5146: -- Get Teacher Reference Num
5147: IF g_he_st_spa.associate_teach_ref_num = 'Y'
5148: THEN
5149: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
5150: igs_he_extract_fields_pkg.get_alt_pers_id
5151: (p_person_id => p_person_id,
5152: p_id_type => 'TEACH REF',
5153: p_api_id => p_value,
5154: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5177: p_value := g_field_defn.hesa_value(30);
5178: ELSE
5179:
5180: -- jbaber added p_susa_year_of_student for HEFD350
5181: igs_he_extract_fields_pkg.get_year_of_student
5182: (p_person_id => p_person_id,
5183: p_course_cd => p_course_cd,
5184: p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
5185: p_sequence_number => g_as_su_setatmpt.sequence_number,
5195: ELSIF p_field_number = 31
5196: THEN
5197: -- Term Time Accomodation
5198: -- Calculate field 71, location of study first
5199: igs_he_extract_fields_pkg.get_study_location
5200: (p_susa_study_location => g_he_en_susa.study_location,
5201: p_poous_study_location => g_he_poous.location_of_study,
5202: p_prg_study_location => g_he_st_prog.location_of_study,
5203: p_oss_study_location => g_field_defn.oss_value(71),
5204: p_hesa_study_location => g_field_defn.hesa_value(71));
5205:
5206: -- Next calcualte TTA
5207: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
5208: igs_he_extract_fields_pkg.get_term_time_acc
5209: (p_person_id => p_person_id,
5210: p_susa_term_time_acc => g_he_en_susa.term_time_accom,
5211: p_study_location => g_field_defn.oss_value(71),
5212: p_hesa_term_time_acc => p_value,
5220:
5221: ELSIF p_field_number = 33
5222: THEN
5223: -- Reason for leaving institution
5224: igs_he_extract_fields_pkg.get_rsn_inst_left
5225: (p_person_id => p_person_id,
5226: p_course_cd => p_course_cd ,
5227: p_crs_req_comp_ind => g_en_stdnt_ps_att.course_rqrmnt_complete_ind,
5228: p_crs_req_comp_dt => g_en_stdnt_ps_att.course_rqrmnts_complete_dt,
5241: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
5242: THEN
5243: -- Calculate Completion Status
5244: --smaddali added new parameter p_course_cd to this call for bug 2396174
5245: igs_he_extract_fields_pkg.get_completion_status
5246: (p_person_id => p_person_id,
5247: p_course_cd => p_course_cd ,
5248: p_susa_comp_status => g_he_en_susa.completion_status,
5249: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
5271: g_default_pro:= 'N';
5272: -- Good Standing Marker
5273: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
5274: THEN
5275: igs_he_extract_fields_pkg.get_good_stand_mrkr
5276: (p_susa_good_st_mk => g_he_en_susa.good_stand_marker,
5277: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
5278: p_crs_req_comp_ind => g_en_stdnt_ps_att.course_rqrmnt_complete_ind,
5279: p_discont_date => g_en_stdnt_ps_att.discontinued_dt,
5287: -- Get fields 37, 38 and 39 together.
5288: IF g_field_defn.hesa_value.EXISTS(37) THEN
5289: p_value := g_field_defn.hesa_value(37) ;
5290: ELSE
5291: igs_he_extract_fields_pkg.get_qual_obtained
5292: (p_person_id => p_person_id,
5293: p_course_cd => p_course_cd,
5294: p_enrl_start_dt => l_awd_conf_start_dt,
5295: p_enrl_end_dt => l_awd_conf_end_dt,
5307: IF g_field_defn.hesa_value.EXISTS(38)
5308: THEN
5309: p_value := g_field_defn.hesa_value(38);
5310: ELSE
5311: igs_he_extract_fields_pkg.get_qual_obtained
5312: (p_person_id => p_person_id,
5313: p_course_cd => p_course_cd,
5314: p_enrl_start_dt => l_awd_conf_start_dt,
5315: p_enrl_end_dt => l_awd_conf_end_dt,
5328: IF g_field_defn.hesa_value.EXISTS(39)
5329: THEN
5330: p_value := g_field_defn.hesa_value(39);
5331: ELSE
5332: igs_he_extract_fields_pkg.get_qual_obtained
5333: (p_person_id => p_person_id,
5334: p_course_cd => p_course_cd,
5335: p_enrl_start_dt => l_awd_conf_start_dt,
5336: p_enrl_end_dt => l_awd_conf_end_dt,
5357:
5358: ELSE
5359: -- Qualification Aim
5360: -- smaddali 21-jan-04 added 2 new parameters for bug#3360646
5361: igs_he_extract_fields_pkg.get_gen_qual_aim
5362: (p_person_id => p_person_id,
5363: p_course_cd => p_course_cd,
5364: p_version_number => p_crv_version_number,
5365: p_spa_gen_qaim => g_he_st_spa.student_qual_aim,
5375: g_default_pro:= 'N';
5376: -- FE General Qualification Aim
5377: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)
5378: THEN
5379: igs_he_extract_fields_pkg.get_fe_qual_aim
5380: (p_spa_fe_qual_aim => g_he_st_spa.student_fe_qual_aim,
5381: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
5382: p_course_cd => p_course_cd,
5383: p_version_number => p_crv_version_number,
5395: CLOSE c_subj;
5396:
5397: ELSE
5398: -- Qualification Aim, Subject 1
5399: igs_he_extract_fields_pkg.get_qual_aim_sbj
5400: (p_course_cd => p_course_cd,
5401: p_version_number => p_crv_version_number,
5402: p_subject1 => p_value,
5403: p_subject2 => g_field_defn.hesa_value(44),
5428: -- Calculated earlier..
5429: p_value := g_field_defn.hesa_value(44);
5430: ELSE
5431: -- Not calculated earlier, calculate now
5432: igs_he_extract_fields_pkg.get_qual_aim_sbj
5433: (p_course_cd => p_course_cd,
5434: p_version_number => p_crv_version_number,
5435: p_subject1 => l_dummy1,
5436: p_subject2 => p_value,
5461: -- Calculated earlier..
5462: p_value := g_field_defn.hesa_value(45);
5463: ELSE
5464: -- Not calculated earlier, calculate now
5465: igs_he_extract_fields_pkg.get_qual_aim_sbj
5466: (p_course_cd => p_course_cd,
5467: p_version_number => p_crv_version_number,
5468: p_subject1 => l_dummy1,
5469: p_subject2 => l_dummy2,
5481: g_he_st_spa.qual_aim_subj2 IS NOT NULL OR
5482: g_he_st_spa.qual_aim_subj3 IS NOT NULL
5483: THEN
5484:
5485: igs_he_extract_fields_pkg.get_qual_aim_sbj1(
5486: p_qual_aim_subj1 => g_he_st_spa.qual_aim_subj1,
5487: p_qual_aim_subj2 => g_he_st_spa.qual_aim_subj2,
5488: p_qual_aim_subj3 => g_he_st_spa.qual_aim_subj3,
5489: p_oss_qualaim_sbj => g_he_st_spa.qual_aim_proportion,
5497: -- Calculated earlier..
5498: p_value := g_field_defn.hesa_value(46);
5499: ELSE
5500: -- Not calculated earlier, calculate now
5501: igs_he_extract_fields_pkg.get_qual_aim_sbj
5502: (p_course_cd => p_course_cd,
5503: p_version_number => p_crv_version_number,
5504: p_subject1 => l_dummy1,
5505: p_subject2 => l_dummy2,
5514: -- Awarding Body 1
5515: IF g_field_defn.oss_value.EXISTS(37)
5516: AND g_field_defn.oss_value.EXISTS(38)
5517: THEN
5518: igs_he_extract_fields_pkg.get_awd_body_12
5519: (p_course_cd => p_course_cd,
5520: p_version_number => p_crv_version_number,
5521: p_awd1 => g_field_defn.oss_value(37),
5522: p_awd2 => g_field_defn.oss_value(38),
5534: ELSIF g_field_defn.oss_value.EXISTS(37)
5535: AND g_field_defn.oss_value.EXISTS(38)
5536: THEN
5537: -- Not calculated therefore calculate.
5538: igs_he_extract_fields_pkg.get_awd_body_12
5539: (p_course_cd => p_course_cd,
5540: p_version_number => p_crv_version_number,
5541: p_awd1 => g_field_defn.oss_value(37),
5542: p_awd2 => g_field_defn.oss_value(38),
5547: ELSIF p_field_number = 49
5548: THEN
5549: -- Length of Program
5550: -- Length of Program
5551: igs_he_extract_fields_pkg.get_new_prog_length
5552: (p_spa_attendance_type => g_en_stdnt_ps_att.attendance_type,
5553: p_program_length => g_ps_ofr_opt.program_length,
5554: p_program_length_measurement => g_ps_ofr_opt.program_length_measurement,
5555: p_length => p_value,
5567: -- Calculated Earlier ..
5568: p_value := g_field_defn.hesa_value(50);
5569: ELSE
5570: -- Not calculated earlier ..
5571: igs_he_extract_fields_pkg.get_new_prog_length
5572: (p_spa_attendance_type => g_en_stdnt_ps_att.attendance_type,
5573: p_program_length => g_ps_ofr_opt.program_length,
5574: p_program_length_measurement => g_ps_ofr_opt.program_length_measurement,
5575: p_length => g_field_defn.hesa_value(49),
5599: p_value := g_field_defn.hesa_value(53);
5600: ELSE
5601:
5602: -- Teacher Training Course Identifier
5603: igs_he_extract_fields_pkg.get_teach_train_crs_id
5604: (p_prg_ttcid => g_he_st_prog.teacher_train_prog_id,
5605: p_spa_ttcid => g_he_st_spa.teacher_train_prog_id,
5606: p_hesa_ttcid => p_value);
5607: END IF;
5609: ELSIF p_field_number = 54
5610: THEN
5611: IF (g_he_submsn_header.validation_country in ('ENGLAND','WALES') AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) IN(1,2,6,7) )) THEN
5612: -- ITT Phase / Scope
5613: igs_he_extract_fields_pkg.get_itt_phsc
5614: (p_prg_itt_phsc => g_he_st_prog.itt_phase,
5615: p_spa_itt_phsc => g_he_st_spa.itt_phase,
5616: p_hesa_itt_phsc => p_value);
5617: ELSE
5621: ELSIF p_field_number = 55
5622: THEN
5623: IF (g_he_submsn_header.validation_country in ('SCOTLAND','WALES','NORTHERN IRELAND') AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) IN(1,2)) ) THEN
5624: -- Bilingual ITT Marker
5625: igs_he_extract_fields_pkg.get_itt_mrker
5626: (p_prg_itt_mrker => g_he_st_prog.bilingual_itt_marker,
5627: p_spa_itt_mrker => g_he_st_spa.bilingual_itt_marker,
5628: p_hesa_itt_mrker => p_value);
5629:
5635: THEN
5636:
5637: IF ( g_he_submsn_header.validation_country IN ('SCOTLAND' ,'NORTHERN IRELAND') AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) IN(1, 2))) THEN
5638: -- Teaching Qualification Sought Sector
5639: igs_he_extract_fields_pkg.get_teach_qual_sect
5640: (p_oss_teach_qual_sect => g_he_st_prog.teaching_qual_sought_sector,
5641: p_hesa_teach_qual_sect => p_value);
5642:
5643: ELSE
5647: ELSIF p_field_number = 57
5648: THEN
5649: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) and g_field_defn.hesa_value(56) = 2 ) ) THEN
5650: -- Teaching Qualification Sought Subject 1
5651: igs_he_extract_fields_pkg.get_teach_qual_sbj
5652: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj1,
5653: p_hesa_teach_qual_sbj => p_value);
5654: ELSE
5655: g_default_pro:= 'N';
5659: ELSIF p_field_number = 58
5660: THEN
5661: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) AND g_field_defn.hesa_value(56) = 2 )) THEN
5662: -- Teaching Qualification Sought Subject 2
5663: igs_he_extract_fields_pkg.get_teach_qual_sbj
5664: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj2,
5665: p_hesa_teach_qual_sbj => p_value);
5666: ELSE
5667: g_default_pro:= 'N';
5670: ELSIF p_field_number = 59
5671: THEN
5672: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(56) and g_field_defn.hesa_value(56) = 2 ) ) THEN
5673: -- Teaching Qualification Sought Subject 3
5674: igs_he_extract_fields_pkg.get_teach_qual_sbj
5675: (p_oss_teach_qual_sbj => g_he_st_prog.teaching_qual_sought_subj3,
5676: p_hesa_teach_qual_sbj => p_value);
5677: ELSE
5678: g_default_pro:= 'N';
5682: THEN
5683:
5684: IF ( g_he_submsn_header.validation_country IN ('SCOTLAND' , 'NORTHERN IRELAND' ) AND ( g_field_defn.hesa_value.EXISTS(53) AND g_field_defn.hesa_value(53) in (1,2) ) ) THEN
5685: -- Teaching Qualification Gained Sector
5686: igs_he_extract_fields_pkg.get_teach_qual_sect
5687: (p_oss_teach_qual_sect => g_he_st_spa.teaching_qual_gain_sector,
5688: p_hesa_teach_qual_sect => p_value);
5689:
5690: IF p_value IS NULL
5702: THEN
5703:
5704: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) AND g_field_defn.hesa_value(60) = 2 )) THEN
5705: -- Teaching Qualification Gained Subject 1
5706: igs_he_extract_fields_pkg.get_teach_qual_sbj
5707: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj1,
5708: p_hesa_teach_qual_sbj => p_value);
5709:
5710: IF p_value IS NULL
5723: THEN
5724:
5725: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) and g_field_defn.hesa_value(60) = 2 ) ) THEN
5726: -- Teaching Qualification Gained Subject 2
5727: igs_he_extract_fields_pkg.get_teach_qual_sbj
5728: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj2,
5729: p_hesa_teach_qual_sbj => p_value);
5730:
5731: IF p_value IS NULL
5741: ELSIF p_field_number = 63
5742: THEN
5743: IF ( g_he_submsn_header.validation_country = 'SCOTLAND' AND ( g_field_defn.hesa_value.EXISTS(60) AND g_field_defn.hesa_value(60) = 2 )) THEN
5744: -- Teaching Qualification Gained Subject 3
5745: igs_he_extract_fields_pkg.get_teach_qual_sbj
5746: (p_oss_teach_qual_sbj => g_he_st_spa.teaching_qual_gain_subj3,
5747: p_hesa_teach_qual_sbj => p_value);
5748:
5749: IF p_value IS NULL
5768: ELSE
5769: -- Not calculated earlier.
5770: -- smaddali modified this call to pass funding_source field for hefd208 - bug#2717751
5771: -- jtmathew modified this call to pass funding_source field at spa level - bug#3962575
5772: igs_he_extract_fields_pkg.get_funding_src
5773: (p_course_cd => p_course_cd,
5774: p_version_number => p_crv_version_number,
5775: p_spa_fund_src => g_en_stdnt_ps_att.funding_source,
5776: p_poous_fund_src => g_he_poous.funding_source,
5790: IF g_field_defn.oss_value.EXISTS(64)
5791: THEN
5792: -- Next get the Fundability Code
5793: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
5794: igs_he_extract_fields_pkg.get_fundability_cd
5795: (p_person_id => p_person_id,
5796: p_susa_fund_cd => g_he_en_susa.fundability_code,
5797: p_spa_funding_source => g_en_stdnt_ps_att.funding_source,
5798: p_poous_fund_cd => g_he_poous.fundability_cd,
5815: IF g_field_defn.oss_value.EXISTS(6)
5816: AND g_field_defn.oss_value.EXISTS(28)
5817: THEN
5818: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
5819: igs_he_extract_fields_pkg.get_fee_elig
5820: (p_person_id => p_person_id,
5821: p_susa_fee_elig => g_he_en_susa.fee_eligibility,
5822: p_fe_stdnt_mrker => g_field_defn.oss_value(6),
5823: p_study_mode => NVL(g_he_en_susa.study_mode,NVL(g_he_poous.mode_of_study,g_en_stdnt_ps_att.attendance_type)),
5826: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5827: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
5828: ELSE
5829: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
5830: igs_he_extract_fields_pkg.get_fee_elig
5831: (p_person_id => p_person_id,
5832: p_susa_fee_elig => g_he_en_susa.fee_eligibility,
5833: p_fe_stdnt_mrker => NULL,
5834: p_study_mode => NVL(g_he_en_susa.study_mode,NVL(g_he_poous.mode_of_study,g_en_stdnt_ps_att.attendance_type)),
5841: ELSIF p_field_number = 67
5842: THEN
5843: -- Fee Band
5844: IF g_field_defn.hesa_value.EXISTS(66) THEN
5845: igs_he_extract_fields_pkg.get_fee_band
5846: (p_hesa_fee_elig => g_field_defn.hesa_value(66),
5847: p_susa_fee_band => g_he_en_susa.fee_band,
5848: p_poous_fee_band => g_he_poous.fee_band,
5849: p_prg_fee_band => g_he_st_prog.fee_band,
5849: p_prg_fee_band => g_he_st_prog.fee_band,
5850: p_hesa_fee_band => p_value);
5851: ELSE
5852:
5853: igs_he_extract_fields_pkg.get_fee_band
5854: (p_hesa_fee_elig => NULL,
5855: p_susa_fee_band => g_he_en_susa.fee_band,
5856: p_poous_fee_band => g_he_poous.fee_band,
5857: p_prg_fee_band => g_he_st_prog.fee_band,
5871: -- smaddali 14-oct-03 added 2 new parameters to the procedure get_amt_tuition_fees, for bug#3179544
5872: IF g_field_defn.hesa_value.EXISTS(6)
5873: AND g_field_defn.hesa_value.EXISTS(28)
5874: THEN
5875: igs_he_extract_fields_pkg.get_amt_tuition_fees
5876: (p_person_id => p_person_id,
5877: p_course_cd => p_course_cd,
5878: p_cal_type => g_en_stdnt_ps_att.cal_type,
5879: p_fe_prg_mrker => g_he_st_prog.fe_program_marker,
5884: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
5885:
5886: -- Calculate Mode of Study
5887: IF NOT g_field_defn.hesa_value.EXISTS(70) THEN
5888: igs_he_extract_fields_pkg.get_mode_of_study
5889: (p_person_id => p_person_id,
5890: p_course_cd => p_course_cd,
5891: p_version_number => p_crv_version_number,
5892: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5897: p_mode_of_study => g_field_defn.hesa_value(70));
5898: END IF ;
5899:
5900: -- Now calculate the major source of tuition fees
5901: igs_he_extract_fields_pkg.get_maj_src_tu_fee
5902: (p_person_id => p_person_id,
5903: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5904: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
5905: p_special_stdnt => g_field_defn.hesa_value(28),
5921: THEN
5922: -- Calculated earlier, for field 68
5923: p_value := g_field_defn.hesa_value(70);
5924: ELSE
5925: igs_he_extract_fields_pkg.get_mode_of_study
5926: (p_person_id => p_person_id,
5927: p_course_cd => p_course_cd,
5928: p_version_number => p_crv_version_number,
5929: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
5943: -- Calculated earlier, for field 31
5944: p_value := g_field_defn.hesa_value(71);
5945: ELSE
5946: -- Not calculated earlier, hence derive
5947: igs_he_extract_fields_pkg.get_study_location
5948: (p_susa_study_location => g_he_en_susa.study_location,
5949: p_poous_study_location => g_he_poous.location_of_study,
5950: p_prg_study_location => g_he_st_prog.location_of_study,
5951: p_oss_study_location => g_field_defn.oss_value(71),
5959: THEN
5960: p_value := g_field_defn.hesa_value(72);
5961: ELSE
5962: -- Not calculated earlier, hence derive
5963: igs_he_extract_fields_pkg.get_year_of_prog
5964: (p_unit_set_cd => g_as_su_setatmpt.unit_set_cd,
5965: p_year_of_prog => p_value);
5966:
5967: END IF;
6020: -- PGCE - Classification of Undergraduate Degree
6021: --smaddali added the whole code instead of NULL statement for bug 2436924
6022: IF g_field_defn.hesa_value.EXISTS(41) AND g_field_defn.hesa_value(41) IN ('12','13')
6023: THEN
6024: igs_he_extract_fields_pkg.get_pgce_class
6025: (p_person_id => p_person_id,
6026: p_pgce_class => p_value);
6027: ELSE
6028: p_value := NULL ;
6031: ELSIF p_field_number = 78
6032: THEN
6033: IF (g_he_submsn_header.validation_country = 'NORTHERN IRELAND' AND ( g_field_defn.hesa_value.EXISTS(12) and g_field_defn.hesa_value(12) = '8826'))THEN
6034: -- Religion
6035: igs_he_extract_fields_pkg.get_religion
6036: (p_oss_religion => g_pe_stat_v.religion,
6037: p_hesa_religion => p_value);
6038:
6039: ELSE
6054: THEN
6055: -- SLDD Discrete Provision
6056: IF g_field_defn.hesa_value.EXISTS(6)
6057: THEN
6058: igs_he_extract_fields_pkg.get_sldd_disc_prv
6059: (p_oss_sldd_disc_prv => g_he_en_susa.sldd_discrete_prov,
6060: p_fe_stdnt_mrker => g_field_defn.hesa_value(6),
6061: p_hesa_sldd_disc_prv => p_value);
6062: END IF;
6081: ELSE
6082:
6083: -- Not calculated earlier. Derive now.
6084: -- smaddali 14-oct-03 added 2 new parameters to the procedure get_amt_tuition_fees, for bug#3179544
6085: igs_he_extract_fields_pkg.get_amt_tuition_fees
6086: (p_person_id => p_person_id,
6087: p_course_cd => p_course_cd,
6088: p_cal_type => g_en_stdnt_ps_att.cal_type,
6089: p_fe_prg_mrker => g_he_st_prog.fe_program_marker,
6102: g_default_pro:= 'N';
6103:
6104: IF g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4) THEN
6105: -- Non Payment Reason
6106: igs_he_extract_fields_pkg.get_non_payment_rsn
6107: (p_oss_non_payment_rsn => g_he_en_susa.non_payment_reason,
6108: p_hesa_non_payment_rsn => p_value,
6109: p_fe_stdnt_mrker => g_field_defn.hesa_value(6));
6110:
6119: g_total_mod := 0;
6120:
6121: IF g_he_st_prog.program_calc = 'N'
6122: THEN
6123: igs_he_extract_fields_pkg.get_module_dets
6124: (p_person_id => p_person_id,
6125: p_course_cd => p_course_cd,
6126: p_version_number => p_crv_version_number,
6127: p_student_inst_number => g_he_st_spa.student_inst_number,
6606:
6607: IF g_he_st_spa.associate_ucas_number = 'Y' THEN
6608:
6609: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
6610: igs_he_extract_fields_pkg.get_ucasnum
6611: (p_person_id => p_person_id,
6612: p_ucasnum => p_value,
6613: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
6614: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
6634:
6635: ELSIF p_field_number = 137
6636: THEN
6637: -- Suspension of Active studies
6638: igs_he_extract_fields_pkg.get_studies_susp
6639: (p_person_id => p_person_id,
6640: p_course_cd => p_course_cd,
6641: p_version_number => p_crv_version_number,
6642: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
6645:
6646: ELSIF p_field_number = 138
6647: THEN
6648: -- Type of Program year
6649: igs_he_extract_fields_pkg.get_pyr_type
6650: (p_oss_pyr_type => NVL(g_he_en_susa.type_of_year,g_he_poous.type_of_year),
6651: p_hesa_pyr_type => p_value);
6652:
6653: ELSIF p_field_number = 139
6652:
6653: ELSIF p_field_number = 139
6654: THEN
6655: -- Level applicable for funding
6656: igs_he_extract_fields_pkg.get_lvl_appl_to_fund
6657: (p_poous_lvl_appl_fund => g_he_poous.level_applicable_to_funding,
6658: p_prg_lvl_appl_fund => g_he_st_prog.level_applicable_to_funding,
6659: p_hesa_lvl_appl_fund => p_value);
6660:
6699: l_typeyr := NULL;
6700: END IF;
6701:
6702: -- Completion of Year of Program of study
6703: igs_he_extract_fields_pkg.get_comp_pyr_study (
6704: p_susa_comp_pyr_study => g_he_en_susa.complete_pyr_study_cd,
6705: p_fundlev => l_fundlev,
6706: p_spcstu => l_spcstu,
6707: p_notact => l_notact,
6726:
6727: IF (g_he_submsn_header.validation_country = 'WALES' AND ( g_field_defn.hesa_value.EXISTS(6) and g_field_defn.hesa_value(6) IN (1,3,4))) THEN
6728: -- Get the value
6729: -- Destination
6730: igs_he_extract_fields_pkg.get_destination
6731: (p_oss_destination => g_he_st_spa.destination,
6732: p_hesa_destination => p_value);
6733: ELSE
6734: -- The default value should not be calculated for any other condition
6769: -- Calculated earlier, for field 29
6770: p_value := g_field_defn.hesa_value(146);
6771:
6772: ELSE
6773: igs_he_extract_fields_pkg.get_itt_outcome
6774: (p_oss_itt_outcome => g_he_st_spa.itt_prog_outcome,
6775: p_teach_train_prg => g_field_defn.hesa_value(53),
6776: p_hesa_itt_outcome => p_value);
6777:
6793:
6794: ELSIF p_field_number = 149
6795: THEN
6796: -- UFI Place
6797: igs_he_extract_fields_pkg.get_ufi_place
6798: (p_oss_ufi_place => NVL(g_he_st_spa.ufi_place,g_he_poous.ufi_place),
6799: p_hesa_ufi_place => p_value);
6800:
6801: ELSIF p_field_number = 150
6800:
6801: ELSIF p_field_number = 150
6802: THEN
6803: --Franchising Activity
6804: igs_he_extract_fields_pkg.get_franchising_activity
6805: (p_susa_franch_activity => g_he_en_susa.franchising_activity,
6806: p_poous_franch_activity => g_he_poous.franchising_activity,
6807: p_prog_franch_activity => g_he_st_prog.franchising_activity,
6808: p_hesa_franch_activity => p_value);
6809:
6810: ELSIF p_field_number = 151
6811: THEN
6812: -- Institutions own campus identifier
6813: igs_he_extract_fields_pkg.get_campus_id
6814: (p_location_cd => g_en_stdnt_ps_att.location_cd,
6815: p_campus_id => p_value);
6816:
6817: ELSIF p_field_number = 152
6820: -- sjlaporte use date comparison rather than text bug 3933715
6821: -- Social Class Indicator
6822: IF g_en_stdnt_ps_att.commencement_dt > TO_DATE('31/07/2002', 'DD/MM/YYYY')
6823: THEN
6824: igs_he_extract_fields_pkg.get_social_class_ind
6825: (p_spa_social_class_ind => g_he_st_spa.social_class_ind,
6826: p_adm_social_class_ind => g_he_ad_dtl.social_class_cd,
6827: p_hesa_social_class_ind => p_value);
6828: END IF;
6833: -- sjlaporte use date comparison rather than text bug 3933715
6834: -- Occupation Code
6835: IF g_en_stdnt_ps_att.commencement_dt > TO_DATE('31/07/2002', 'DD/MM/YYYY')
6836: THEN
6837: igs_he_extract_fields_pkg.get_occupation_code
6838: (p_spa_occupation_code => g_he_st_spa.occupation_code,
6839: p_hesa_occupation_code => p_value);
6840:
6841: END IF;
6843: ELSIF p_field_number = 154
6844: THEN
6845: -- Insitute last attended
6846: -- smaddali modified this field to remove the dependency on field 148UCASNUM for bug 2663717
6847: igs_he_extract_fields_pkg.get_inst_last_attended
6848: (p_person_id => p_person_id,
6849: p_com_date => g_en_stdnt_ps_att.commencement_dt,
6850: p_inst_last_att => p_value,
6851: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
6854:
6855: ELSIF p_field_number = 155
6856: THEN
6857: -- Regulatory Body
6858: igs_he_extract_fields_pkg.get_regulatory_body
6859: (p_course_cd => p_course_cd,
6860: p_version_number => p_crv_version_number,
6861: p_hesa_regulatory_body => p_value);
6862:
6868: -- Regulatory Body Registration Number
6869: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
6870: -- and adding validation to derive this field only if associate_nhs_reg_num=Y
6871: IF g_he_st_spa.associate_nhs_reg_num = 'Y' THEN
6872: igs_he_extract_fields_pkg.get_alt_pers_id
6873: (p_person_id => p_person_id,
6874: p_id_type => 'DH REG REF',
6875: p_api_id => p_value,
6876: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
6889:
6890: ELSIF p_field_number = 157
6891: THEN
6892: -- Source of NHS funding
6893: igs_he_extract_fields_pkg.get_nhs_fund_src
6894: (p_spa_nhs_fund_src => g_he_st_spa.nhs_funding_source,
6895: p_prg_nhs_fund_src => g_he_st_prog.nhs_funding_source,
6896: p_hesa_nhs_fund_src => p_value);
6897:
6897:
6898: ELSIF p_field_number = 158
6899: THEN
6900: -- NHS Employer
6901: igs_he_extract_fields_pkg.get_nhs_employer
6902: (p_spa_nhs_employer => g_he_st_spa.nhs_employer,
6903: p_hesa_nhs_employer => p_value);
6904:
6905: ELSIF p_field_number = 159
6907:
6908: g_default_pro := 'N';
6909:
6910: -- Number of GCE AS Levels
6911: igs_he_extract_fields_pkg.get_qual_dets
6912: (p_person_id => p_person_id,
6913: p_course_cd => p_course_cd,
6914: p_hesa_qual => 'GCSEAS',
6915: p_no_of_qual => p_value,
6915: p_no_of_qual => p_value,
6916: p_tariff_score => g_field_defn.hesa_value(160));
6917:
6918: IF p_value IS NOT NULL THEN
6919: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
6920: p_person_number => g_pe_person.person_number,
6921: p_course_cd => p_course_cd,
6922: p_hesa_qual => 'GCEASN',
6923: p_no_of_qual => p_value);
6925: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
6926: IF g_field_defn.hesa_value.EXISTS(41) AND
6927: g_field_defn.hesa_value.EXISTS(26) AND
6928: g_field_defn.hesa_value.EXISTS(133) AND
6929: igs_he_extract_fields_pkg.is_ucas_ftug
6930: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
6931: p_hesa_commdate => g_field_defn.hesa_value(26),
6932: p_ucasnum => g_field_defn.hesa_value(133),
6933: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
6945: -- GCE AS level Tariff Score
6946: IF g_field_defn.hesa_value.EXISTS(160) AND g_field_defn.hesa_value(160) IS NOT NULL
6947: THEN
6948: p_value := g_field_defn.hesa_value(160);
6949: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
6950: p_person_number => g_pe_person.person_number,
6951: p_course_cd => p_course_cd,
6952: p_hesa_qual => 'GCEASTS',
6953: p_tariff_score => p_value);
6958: IF p_value IS NULL AND
6959: g_field_defn.hesa_value.EXISTS(41) AND
6960: g_field_defn.hesa_value.EXISTS(26) AND
6961: g_field_defn.hesa_value.EXISTS(133) AND
6962: igs_he_extract_fields_pkg.is_ucas_ftug
6963: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
6964: p_hesa_commdate => g_field_defn.hesa_value(26),
6965: p_ucasnum => g_field_defn.hesa_value(133),
6966: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
6973:
6974: g_default_pro := 'N';
6975:
6976: -- Number of VCE AS Levels
6977: igs_he_extract_fields_pkg.get_qual_dets
6978: (p_person_id => p_person_id,
6979: p_course_cd => p_course_cd,
6980: p_hesa_qual => 'VCSEAS',
6981: p_no_of_qual => p_value,
6981: p_no_of_qual => p_value,
6982: p_tariff_score => g_field_defn.hesa_value(162));
6983:
6984: IF p_value IS NOT NULL THEN
6985: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
6986: p_person_number => g_pe_person.person_number,
6987: p_course_cd => p_course_cd,
6988: p_hesa_qual => 'VCEASN',
6989: p_no_of_qual => p_value);
6991: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
6992: IF g_field_defn.hesa_value.EXISTS(41) AND
6993: g_field_defn.hesa_value.EXISTS(26) AND
6994: g_field_defn.hesa_value.EXISTS(133) AND
6995: igs_he_extract_fields_pkg.is_ucas_ftug
6996: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
6997: p_hesa_commdate => g_field_defn.hesa_value(26),
6998: p_ucasnum => g_field_defn.hesa_value(133),
6999: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7011: -- VCE AS level Tariff Score
7012: IF g_field_defn.hesa_value.EXISTS(162) AND g_field_defn.hesa_value(162) IS NOT NULL
7013: THEN
7014: p_value := g_field_defn.hesa_value(162);
7015: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7016: p_person_number => g_pe_person.person_number,
7017: p_course_cd => p_course_cd,
7018: p_hesa_qual => 'VCEASTS',
7019: p_tariff_score => p_value);
7024: IF p_value IS NULL AND
7025: g_field_defn.hesa_value.EXISTS(41) AND
7026: g_field_defn.hesa_value.EXISTS(26) AND
7027: g_field_defn.hesa_value.EXISTS(133) AND
7028: igs_he_extract_fields_pkg.is_ucas_ftug
7029: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7030: p_hesa_commdate => g_field_defn.hesa_value(26),
7031: p_ucasnum => g_field_defn.hesa_value(133),
7032: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7039:
7040: g_default_pro := 'N';
7041:
7042: -- Number of GCE A Levels
7043: igs_he_extract_fields_pkg.get_qual_dets
7044: (p_person_id => p_person_id,
7045: p_course_cd => p_course_cd,
7046: p_hesa_qual => 'GCSEA',
7047: p_no_of_qual => p_value,
7047: p_no_of_qual => p_value,
7048: p_tariff_score => g_field_defn.hesa_value(164));
7049:
7050: IF p_value IS NOT NULL THEN
7051: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7052: p_person_number => g_pe_person.person_number,
7053: p_course_cd => p_course_cd,
7054: p_hesa_qual => 'GCEAN',
7055: p_no_of_qual => p_value);
7057: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7058: IF g_field_defn.hesa_value.EXISTS(41) AND
7059: g_field_defn.hesa_value.EXISTS(26) AND
7060: g_field_defn.hesa_value.EXISTS(133) AND
7061: igs_he_extract_fields_pkg.is_ucas_ftug
7062: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7063: p_hesa_commdate => g_field_defn.hesa_value(26),
7064: p_ucasnum => g_field_defn.hesa_value(133),
7065: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7078: IF g_field_defn.hesa_value.EXISTS(164) AND g_field_defn.hesa_value(164) IS NOT NULL
7079: THEN
7080: p_value := g_field_defn.hesa_value(164);
7081:
7082: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7083: p_person_number => g_pe_person.person_number,
7084: p_course_cd => p_course_cd,
7085: p_hesa_qual => 'GCEATS',
7086: p_tariff_score => p_value);
7091: IF p_value IS NULL AND
7092: g_field_defn.hesa_value.EXISTS(41) AND
7093: g_field_defn.hesa_value.EXISTS(26) AND
7094: g_field_defn.hesa_value.EXISTS(133) AND
7095: igs_he_extract_fields_pkg.is_ucas_ftug
7096: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7097: p_hesa_commdate => g_field_defn.hesa_value(26),
7098: p_ucasnum => g_field_defn.hesa_value(133),
7099: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7106:
7107: g_default_pro := 'N';
7108:
7109: -- Number of VCE A Levels
7110: igs_he_extract_fields_pkg.get_qual_dets
7111: (p_person_id => p_person_id,
7112: p_course_cd => p_course_cd,
7113: p_hesa_qual => 'VCSEA',
7114: p_no_of_qual => p_value,
7114: p_no_of_qual => p_value,
7115: p_tariff_score => g_field_defn.hesa_value(166));
7116:
7117: IF p_value IS NOT NULL THEN
7118: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7119: p_person_number => g_pe_person.person_number,
7120: p_course_cd => p_course_cd,
7121: p_hesa_qual => 'VCEAN',
7122: p_no_of_qual => p_value);
7124: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7125: IF g_field_defn.hesa_value.EXISTS(41) AND
7126: g_field_defn.hesa_value.EXISTS(26) AND
7127: g_field_defn.hesa_value.EXISTS(133) AND
7128: igs_he_extract_fields_pkg.is_ucas_ftug
7129: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7130: p_hesa_commdate => g_field_defn.hesa_value(26),
7131: p_ucasnum => g_field_defn.hesa_value(133),
7132: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7145: IF g_field_defn.hesa_value.EXISTS(166) AND g_field_defn.hesa_value(166) IS NOT NULL
7146: THEN
7147: p_value := g_field_defn.hesa_value(166);
7148:
7149: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7150: p_person_number => g_pe_person.person_number,
7151: p_course_cd => p_course_cd,
7152: p_hesa_qual => 'VCEATS',
7153: p_tariff_score => p_value);
7158: IF p_value IS NULL AND
7159: g_field_defn.hesa_value.EXISTS(41) AND
7160: g_field_defn.hesa_value.EXISTS(26) AND
7161: g_field_defn.hesa_value.EXISTS(133) AND
7162: igs_he_extract_fields_pkg.is_ucas_ftug
7163: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7164: p_hesa_commdate => g_field_defn.hesa_value(26),
7165: p_ucasnum => g_field_defn.hesa_value(133),
7166: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7173:
7174: g_default_pro := 'N';
7175:
7176: -- Number of Key Skill Qualifications
7177: igs_he_extract_fields_pkg.get_qual_dets
7178: (p_person_id => p_person_id,
7179: p_course_cd => p_course_cd,
7180: p_hesa_qual => 'KEYSKL',
7181: p_no_of_qual => p_value,
7181: p_no_of_qual => p_value,
7182: p_tariff_score => g_field_defn.hesa_value(168));
7183:
7184: IF p_value IS NOT NULL THEN
7185: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7186: p_person_number => g_pe_person.person_number,
7187: p_course_cd => p_course_cd,
7188: p_hesa_qual => 'KSQN',
7189: p_no_of_qual => p_value);
7191: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7192: IF g_field_defn.hesa_value.EXISTS(41) AND
7193: g_field_defn.hesa_value.EXISTS(26) AND
7194: g_field_defn.hesa_value.EXISTS(133) AND
7195: igs_he_extract_fields_pkg.is_ucas_ftug
7196: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7197: p_hesa_commdate => g_field_defn.hesa_value(26),
7198: p_ucasnum => g_field_defn.hesa_value(133),
7199: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7212: IF g_field_defn.hesa_value.EXISTS(168) AND g_field_defn.hesa_value(168) IS NOT NULL
7213: THEN
7214: p_value := g_field_defn.hesa_value(168);
7215:
7216: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7217: p_person_number => g_pe_person.person_number,
7218: p_course_cd => p_course_cd,
7219: p_hesa_qual => 'KSQTS',
7220: p_tariff_score => p_value);
7225: IF p_value IS NULL AND
7226: g_field_defn.hesa_value.EXISTS(41) AND
7227: g_field_defn.hesa_value.EXISTS(26) AND
7228: g_field_defn.hesa_value.EXISTS(133) AND
7229: igs_he_extract_fields_pkg.is_ucas_ftug
7230: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7231: p_hesa_commdate => g_field_defn.hesa_value(26),
7232: p_ucasnum => g_field_defn.hesa_value(133),
7233: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7240:
7241: g_default_pro := 'N';
7242:
7243: -- Number of 1 unit key skill awards
7244: igs_he_extract_fields_pkg.get_qual_dets
7245: (p_person_id => p_person_id,
7246: p_course_cd => p_course_cd,
7247: p_hesa_qual => '1UNKEYSKL',
7248: p_no_of_qual => p_value,
7248: p_no_of_qual => p_value,
7249: p_tariff_score => g_field_defn.hesa_value(170));
7250:
7251: IF p_value IS NOT NULL THEN
7252: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7253: p_person_number => g_pe_person.person_number,
7254: p_course_cd => p_course_cd,
7255: p_hesa_qual => 'UKSAN',
7256: p_no_of_qual => p_value);
7258: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7259: IF g_field_defn.hesa_value.EXISTS(41) AND
7260: g_field_defn.hesa_value.EXISTS(26) AND
7261: g_field_defn.hesa_value.EXISTS(133) AND
7262: igs_he_extract_fields_pkg.is_ucas_ftug
7263: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7264: p_hesa_commdate => g_field_defn.hesa_value(26),
7265: p_ucasnum => g_field_defn.hesa_value(133),
7266: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7278: IF g_field_defn.hesa_value.EXISTS(170) AND g_field_defn.hesa_value(170) IS NOT NULL
7279: THEN
7280: p_value := g_field_defn.hesa_value(170);
7281:
7282: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7283: p_person_number => g_pe_person.person_number,
7284: p_course_cd => p_course_cd,
7285: p_hesa_qual => 'UKSATS',
7286: p_tariff_score => p_value);
7291: IF p_value IS NULL AND
7292: g_field_defn.hesa_value.EXISTS(41) AND
7293: g_field_defn.hesa_value.EXISTS(26) AND
7294: g_field_defn.hesa_value.EXISTS(133) AND
7295: igs_he_extract_fields_pkg.is_ucas_ftug
7296: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7297: p_hesa_commdate => g_field_defn.hesa_value(26),
7298: p_ucasnum => g_field_defn.hesa_value(133),
7299: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7306:
7307: g_default_pro := 'N';
7308:
7309: -- Number of Scottish Advanced Higher Levels
7310: igs_he_extract_fields_pkg.get_qual_dets
7311: (p_person_id => p_person_id,
7312: p_course_cd => p_course_cd,
7313: p_hesa_qual => 'SCOTADH',
7314: p_no_of_qual => p_value,
7314: p_no_of_qual => p_value,
7315: p_tariff_score => g_field_defn.hesa_value(172));
7316:
7317: IF p_value IS NOT NULL THEN
7318: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7319: p_person_number => g_pe_person.person_number,
7320: p_course_cd => p_course_cd,
7321: p_hesa_qual => 'SAHN',
7322: p_no_of_qual => p_value);
7324: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7325: IF g_field_defn.hesa_value.EXISTS(41) AND
7326: g_field_defn.hesa_value.EXISTS(26) AND
7327: g_field_defn.hesa_value.EXISTS(133) AND
7328: igs_he_extract_fields_pkg.is_ucas_ftug
7329: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7330: p_hesa_commdate => g_field_defn.hesa_value(26),
7331: p_ucasnum => g_field_defn.hesa_value(133),
7332: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7346: IF g_field_defn.hesa_value.EXISTS(172) AND g_field_defn.hesa_value(172) IS NOT NULL
7347: THEN
7348: p_value := g_field_defn.hesa_value(172);
7349:
7350: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7351: p_person_number => g_pe_person.person_number,
7352: p_course_cd => p_course_cd,
7353: p_hesa_qual => 'SAHTS',
7354: p_tariff_score => p_value);
7359: IF p_value IS NULL AND
7360: g_field_defn.hesa_value.EXISTS(41) AND
7361: g_field_defn.hesa_value.EXISTS(26) AND
7362: g_field_defn.hesa_value.EXISTS(133) AND
7363: igs_he_extract_fields_pkg.is_ucas_ftug
7364: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7365: p_hesa_commdate => g_field_defn.hesa_value(26),
7366: p_ucasnum => g_field_defn.hesa_value(133),
7367: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7374:
7375: g_default_pro := 'N';
7376:
7377: -- Number of Scottish Higher Levels
7378: igs_he_extract_fields_pkg.get_qual_dets
7379: (p_person_id => p_person_id,
7380: p_course_cd => p_course_cd,
7381: p_hesa_qual => 'SCOTH',
7382: p_no_of_qual => p_value,
7382: p_no_of_qual => p_value,
7383: p_tariff_score => g_field_defn.hesa_value(174));
7384:
7385: IF p_value IS NOT NULL THEN
7386: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7387: p_person_number => g_pe_person.person_number,
7388: p_course_cd => p_course_cd,
7389: p_hesa_qual => 'SHN',
7390: p_no_of_qual => p_value);
7392: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7393: IF g_field_defn.hesa_value.EXISTS(41) AND
7394: g_field_defn.hesa_value.EXISTS(26) AND
7395: g_field_defn.hesa_value.EXISTS(133) AND
7396: igs_he_extract_fields_pkg.is_ucas_ftug
7397: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7398: p_hesa_commdate => g_field_defn.hesa_value(26),
7399: p_ucasnum => g_field_defn.hesa_value(133),
7400: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7413: IF g_field_defn.hesa_value.EXISTS(174) AND g_field_defn.hesa_value(174) IS NOT NULL
7414: THEN
7415: p_value := g_field_defn.hesa_value(174);
7416:
7417: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7418: p_person_number => g_pe_person.person_number,
7419: p_course_cd => p_course_cd,
7420: p_hesa_qual => 'SHTS',
7421: p_tariff_score => p_value);
7426: IF p_value IS NULL AND
7427: g_field_defn.hesa_value.EXISTS(41) AND
7428: g_field_defn.hesa_value.EXISTS(26) AND
7429: g_field_defn.hesa_value.EXISTS(133) AND
7430: igs_he_extract_fields_pkg.is_ucas_ftug
7431: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7432: p_hesa_commdate => g_field_defn.hesa_value(26),
7433: p_ucasnum => g_field_defn.hesa_value(133),
7434: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7441:
7442: g_default_pro := 'N';
7443:
7444: -- Number of Scottish Intermediate Levels
7445: igs_he_extract_fields_pkg.get_qual_dets
7446: (p_person_id => p_person_id,
7447: p_course_cd => p_course_cd,
7448: p_hesa_qual => 'SCOTI2',
7449: p_no_of_qual => p_value,
7449: p_no_of_qual => p_value,
7450: p_tariff_score => g_field_defn.hesa_value(176));
7451:
7452: IF p_value IS NOT NULL THEN
7453: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7454: p_person_number => g_pe_person.person_number,
7455: p_course_cd => p_course_cd,
7456: p_hesa_qual => 'SI2N',
7457: p_no_of_qual => p_value);
7459: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7460: IF g_field_defn.hesa_value.EXISTS(41) AND
7461: g_field_defn.hesa_value.EXISTS(26) AND
7462: g_field_defn.hesa_value.EXISTS(133) AND
7463: igs_he_extract_fields_pkg.is_ucas_ftug
7464: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7465: p_hesa_commdate => g_field_defn.hesa_value(26),
7466: p_ucasnum => g_field_defn.hesa_value(133),
7467: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7480: IF g_field_defn.hesa_value.EXISTS(176) AND g_field_defn.hesa_value(176) IS NOT NULL
7481: THEN
7482: p_value := g_field_defn.hesa_value(176);
7483:
7484: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7485: p_person_number => g_pe_person.person_number,
7486: p_course_cd => p_course_cd,
7487: p_hesa_qual => 'SI2TS',
7488: p_tariff_score => p_value);
7493: IF p_value IS NULL AND
7494: g_field_defn.hesa_value.EXISTS(41) AND
7495: g_field_defn.hesa_value.EXISTS(26) AND
7496: g_field_defn.hesa_value.EXISTS(133) AND
7497: igs_he_extract_fields_pkg.is_ucas_ftug
7498: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7499: p_hesa_commdate => g_field_defn.hesa_value(26),
7500: p_ucasnum => g_field_defn.hesa_value(133),
7501: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7508:
7509: g_default_pro := 'N';
7510:
7511: -- Number of Scottish Standard Grade Credit Levels
7512: igs_he_extract_fields_pkg.get_qual_dets
7513: (p_person_id => p_person_id,
7514: p_course_cd => p_course_cd,
7515: p_hesa_qual => 'SCOTST',
7516: p_no_of_qual => p_value,
7516: p_no_of_qual => p_value,
7517: p_tariff_score => g_field_defn.hesa_value(178));
7518:
7519: IF p_value IS NOT NULL THEN
7520: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7521: p_person_number => g_pe_person.person_number,
7522: p_course_cd => p_course_cd,
7523: p_hesa_qual => 'SSGCN',
7524: p_no_of_qual => p_value);
7526: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7527: IF g_field_defn.hesa_value.EXISTS(41) AND
7528: g_field_defn.hesa_value.EXISTS(26) AND
7529: g_field_defn.hesa_value.EXISTS(133) AND
7530: igs_he_extract_fields_pkg.is_ucas_ftug
7531: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7532: p_hesa_commdate => g_field_defn.hesa_value(26),
7533: p_ucasnum => g_field_defn.hesa_value(133),
7534: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7548: IF g_field_defn.hesa_value.EXISTS(178) AND g_field_defn.hesa_value(178) IS NOT NULL
7549: THEN
7550: p_value := g_field_defn.hesa_value(178);
7551:
7552: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7553: p_person_number => g_pe_person.person_number,
7554: p_course_cd => p_course_cd,
7555: p_hesa_qual => 'SSGCTS',
7556: p_tariff_score => p_value);
7561: IF p_value IS NULL AND
7562: g_field_defn.hesa_value.EXISTS(41) AND
7563: g_field_defn.hesa_value.EXISTS(26) AND
7564: g_field_defn.hesa_value.EXISTS(133) AND
7565: igs_he_extract_fields_pkg.is_ucas_ftug
7566: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7567: p_hesa_commdate => g_field_defn.hesa_value(26),
7568: p_ucasnum => g_field_defn.hesa_value(133),
7569: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7576:
7577: g_default_pro := 'N';
7578:
7579: -- Number of Scottish Core Skills Levels
7580: igs_he_extract_fields_pkg.get_qual_dets
7581: (p_person_id => p_person_id,
7582: p_course_cd => p_course_cd,
7583: p_hesa_qual => 'SCOTCO',
7584: p_no_of_qual => p_value,
7584: p_no_of_qual => p_value,
7585: p_tariff_score => g_field_defn.hesa_value(180));
7586:
7587: IF p_value IS NOT NULL THEN
7588: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7589: p_person_number => g_pe_person.person_number,
7590: p_course_cd => p_course_cd,
7591: p_hesa_qual => 'SCSN',
7592: p_no_of_qual => p_value);
7594: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7595: IF g_field_defn.hesa_value.EXISTS(41) AND
7596: g_field_defn.hesa_value.EXISTS(26) AND
7597: g_field_defn.hesa_value.EXISTS(133) AND
7598: igs_he_extract_fields_pkg.is_ucas_ftug
7599: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7600: p_hesa_commdate => g_field_defn.hesa_value(26),
7601: p_ucasnum => g_field_defn.hesa_value(133),
7602: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7615: IF g_field_defn.hesa_value.EXISTS(180) AND g_field_defn.hesa_value(180) IS NOT NULL
7616: THEN
7617: p_value := g_field_defn.hesa_value(180);
7618:
7619: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7620: p_person_number => g_pe_person.person_number,
7621: p_course_cd => p_course_cd,
7622: p_hesa_qual => 'SCSTS',
7623: p_tariff_score => p_value);
7628: IF p_value IS NULL AND
7629: g_field_defn.hesa_value.EXISTS(41) AND
7630: g_field_defn.hesa_value.EXISTS(26) AND
7631: g_field_defn.hesa_value.EXISTS(133) AND
7632: igs_he_extract_fields_pkg.is_ucas_ftug
7633: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7634: p_hesa_commdate => g_field_defn.hesa_value(26),
7635: p_ucasnum => g_field_defn.hesa_value(133),
7636: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7643:
7644: g_default_pro := 'N';
7645:
7646: -- Number of Advanced Extension
7647: igs_he_extract_fields_pkg.get_qual_dets
7648: (p_person_id => p_person_id,
7649: p_course_cd => p_course_cd,
7650: p_hesa_qual => 'ADVEXT',
7651: p_no_of_qual => p_value,
7651: p_no_of_qual => p_value,
7652: p_tariff_score => g_field_defn.hesa_value(182));
7653:
7654: IF p_value IS NOT NULL THEN
7655: igs_he_extract_fields_pkg.limit_no_of_qual(p_field_number => p_field_number,
7656: p_person_number => g_pe_person.person_number,
7657: p_course_cd => p_course_cd,
7658: p_hesa_qual => 'AEAN',
7659: p_no_of_qual => p_value);
7661: -- If is a UCAS (FTUG, NMAS or SWAS) student with no qualification details use default value
7662: IF g_field_defn.hesa_value.EXISTS(41) AND
7663: g_field_defn.hesa_value.EXISTS(26) AND
7664: g_field_defn.hesa_value.EXISTS(133) AND
7665: igs_he_extract_fields_pkg.is_ucas_ftug
7666: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7667: p_hesa_commdate => g_field_defn.hesa_value(26),
7668: p_ucasnum => g_field_defn.hesa_value(133),
7669: p_min_commdate => TO_DATE('31/07/2003', 'DD/MM/YYYY'))
7682: IF g_field_defn.hesa_value.EXISTS(182) AND g_field_defn.hesa_value(182) IS NOT NULL
7683: THEN
7684: p_value := g_field_defn.hesa_value(182);
7685:
7686: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7687: p_person_number => g_pe_person.person_number,
7688: p_course_cd => p_course_cd,
7689: p_hesa_qual => 'AENTS',
7690: p_tariff_score => p_value);
7695: IF p_value IS NULL AND
7696: g_field_defn.hesa_value.EXISTS(41) AND
7697: g_field_defn.hesa_value.EXISTS(26) AND
7698: g_field_defn.hesa_value.EXISTS(133) AND
7699: igs_he_extract_fields_pkg.is_ucas_ftug
7700: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7701: p_hesa_commdate => g_field_defn.hesa_value(26),
7702: p_ucasnum => g_field_defn.hesa_value(133),
7703: p_min_commdate => TO_DATE('31/07/2003', 'DD/MM/YYYY'))
7715:
7716: -- If is a UCAS (FTUG, NMAS or SWAS) student with no tariff information use default value
7717: IF p_value IS NOT NULL
7718: THEN
7719: igs_he_extract_fields_pkg.limit_tariff_score(p_field_number => p_field_number,
7720: p_person_number => g_pe_person.person_number,
7721: p_course_cd => p_course_cd,
7722: p_hesa_qual => 'TOTALTS',
7723: p_tariff_score => p_value);
7724: ELSE
7725: IF g_field_defn.hesa_value.EXISTS(41) AND
7726: g_field_defn.hesa_value.EXISTS(26) AND
7727: g_field_defn.hesa_value.EXISTS(133) AND
7728: igs_he_extract_fields_pkg.is_ucas_ftug
7729: (p_hesa_qual_aim => g_field_defn.hesa_value(41),
7730: p_hesa_commdate => g_field_defn.hesa_value(26),
7731: p_ucasnum => g_field_defn.hesa_value(133),
7732: p_min_commdate => TO_DATE('31/07/2002', 'DD/MM/YYYY'))
7737:
7738: ELSIF p_field_number = 184
7739: THEN
7740: -- Number of CACHE qualifications
7741: igs_he_extract_fields_pkg.get_qual_dets
7742: (p_person_id => p_person_id,
7743: p_course_cd => p_course_cd,
7744: p_hesa_qual => 'CACHE',
7745: p_no_of_qual => p_value,
7755:
7756: ELSIF p_field_number = 186
7757: THEN
7758: -- Number of BTEC
7759: igs_he_extract_fields_pkg.get_qual_dets
7760: (p_person_id => p_person_id,
7761: p_course_cd => p_course_cd,
7762: p_hesa_qual => 'BTEC',
7763: p_no_of_qual => p_value,
7773:
7774: ELSIF p_field_number = 188
7775: THEN
7776: -- International Baccalaureate Tariff Score
7777: igs_he_extract_fields_pkg.get_qual_dets
7778: (p_person_id => p_person_id,
7779: p_course_cd => p_course_cd,
7780: p_hesa_qual => 'INBA',
7781: p_no_of_qual => l_dummy,
7783:
7784: ELSIF p_field_number = 189
7785: THEN
7786: -- Irish Leaving certificate tariff score
7787: igs_he_extract_fields_pkg.get_qual_dets
7788: (p_person_id => p_person_id,
7789: p_course_cd => p_course_cd,
7790: p_hesa_qual => 'ILC',
7791: p_no_of_qual => l_dummy,
7793:
7794: ELSIF p_field_number = 190
7795: THEN
7796: -- Music, Drama and Performing Arts
7797: igs_he_extract_fields_pkg.get_qual_dets
7798: (p_person_id => p_person_id,
7799: p_course_cd => p_course_cd,
7800: p_hesa_qual => 'MUDRPA',
7801: p_no_of_qual => l_dummy,
7812:
7813: ELSIF p_field_number = 192
7814: THEN
7815: -- Marital Status
7816: igs_he_extract_fields_pkg.get_marital_status
7817: (p_oss_marital_status => g_pe_stat_v.marital_status,
7818: p_hesa_marital_status => p_value);
7819:
7820: ELSIF p_field_number = 193
7819:
7820: ELSIF p_field_number = 193
7821: THEN
7822: -- Dependants
7823: igs_he_extract_fields_pkg.get_dependants
7824: (p_oss_dependants => g_he_st_spa.dependants_cd,
7825: p_hesa_dependants => p_value);
7826:
7827: ELSIF p_field_number = 194
7834:
7835: -- Student must be FE student
7836: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
7837:
7838: igs_he_extract_fields_pkg.get_enh_fund_elig
7839: (p_susa_enh_fund_elig => g_he_en_susa.enh_fund_elig_cd,
7840: p_spa_enh_fund_elig => g_he_st_spa.enh_fund_elig_cd,
7841: p_hesa_enh_fund_elig => p_value);
7842:
7876:
7877: -- Student must be FE student
7878: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
7879:
7880: igs_he_extract_fields_pkg.get_learn_dif
7881: (p_person_id => p_person_id,
7882: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
7883: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
7884: p_hesa_disability_type => p_value);
7924:
7925: -- Student must be FE student
7926: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
7927:
7928: igs_he_extract_fields_pkg.get_gov_init
7929: (p_spa_gov_initiatives_cd => g_he_st_spa.gov_initiatives_cd,
7930: p_prog_gov_initiatives_cd => g_he_st_prog.gov_initiatives_cd,
7931: p_hesa_gov_initiatives_cd => p_value);
7932:
7945:
7946: -- Student must be FE student
7947: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
7948:
7949: igs_he_extract_fields_pkg.get_units_completed
7950: (p_person_id => p_person_id,
7951: p_course_cd => p_course_cd,
7952: p_enrl_end_dt => g_he_submsn_header.enrolment_end_date,
7953: p_spa_units_completed => g_he_st_spa.units_completed,
7994:
7995: -- Student must be FE student
7996: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
7997:
7998: igs_he_extract_fields_pkg.get_disadv_uplift_elig
7999: (p_spa_disadv_uplift_elig_cd => g_he_st_spa.disadv_uplift_elig_cd,
8000: p_prog_disadv_uplift_elig_cd => g_he_st_prog.disadv_uplift_elig_cd,
8001: p_hesa_disadv_uplift_elig_cd => p_value);
8002:
8037:
8038: -- Student must be FE student
8039: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
8040:
8041: igs_he_extract_fields_pkg.get_franch_out_arr
8042: (p_spa_franch_out_arr_cd => g_he_st_spa.franch_out_arr_cd,
8043: p_prog_franch_out_arr_cd => g_he_st_prog.franch_out_arr_cd,
8044: p_hesa_franch_out_arr_cd => p_value);
8045:
8058:
8059: -- Student must be FE student
8060: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
8061:
8062: igs_he_extract_fields_pkg.get_employer_role
8063: (p_spa_employer_role_cd => g_he_st_spa.employer_role_cd,
8064: p_hesa_employer_role_cd => p_value);
8065:
8066: --If the value is calculated as NULL above then do the default processing for this field
8078:
8079: -- Student must be FE student
8080: IF (g_field_defn.hesa_value.EXISTS(6) AND g_field_defn.hesa_value(6) IN (1,3,4)) THEN
8081:
8082: igs_he_extract_fields_pkg.get_franchise_partner
8083: (p_spa_franch_partner_cd => g_he_st_spa.franch_partner_cd,
8084: p_hesa_franch_partner_cd => p_value);
8085:
8086: --If the value is calculated as NULL above then do the default processing for this field
8098:
8099: -- Student must be FE student
8100: IF (g_field_defn.hesa_value.EXISTS(12) AND g_field_defn.hesa_value(12) IN (6826)) THEN
8101:
8102: igs_he_extract_fields_pkg.get_welsh_speaker_ind
8103: (p_person_id => p_person_id,
8104: p_hesa_welsh_speaker_ind => p_value);
8105:
8106: --If the value is calculated as NULL above then do the default processing for this field
8118:
8119: -- Student must be FE student
8120: IF (g_field_defn.hesa_value.EXISTS(12) AND g_field_defn.hesa_value(12) IN (6826)) THEN
8121:
8122: igs_he_extract_fields_pkg.get_national_id
8123: (p_person_id => p_person_id,
8124: p_hesa_national_id1 => p_value,
8125: p_hesa_national_id2 => g_field_defn.hesa_value(208) );
8126:
8145:
8146: -- Student must be FE student
8147: IF (g_field_defn.hesa_value.EXISTS(12) AND g_field_defn.hesa_value(12) IN (6826)) THEN
8148:
8149: igs_he_extract_fields_pkg.get_national_id
8150: (p_person_id => p_person_id,
8151: p_hesa_national_id1 => g_field_defn.hesa_value(207),
8152: p_hesa_national_id2 => p_value);
8153:
8164:
8165: IF g_field_defn.hesa_value.EXISTS(12) AND
8166: g_field_defn.hesa_value(12) IN ('6826') THEN
8167:
8168: igs_he_extract_fields_pkg.get_welsh_bacc_qual(p_person_id => p_person_id,
8169: p_welsh_bacc => p_value);
8170:
8171: IF p_value = '3' THEN
8172:
8258:
8259: ELSIF p_field_number = 2
8260: THEN
8261: -- Hesa Institution Id
8262: igs_he_extract_fields_pkg.get_hesa_inst_id
8263: (p_hesa_inst_id => p_value);
8264:
8265: ELSIF p_field_number = 3
8266: THEN
8267:
8268: -- Included the below logic as a part of HECR001(Bug 2278825)
8269: --Check if location is recroded at unit level .If yes then get the cooressponding HESA mapped campus id
8270: IF g_he_st_unt_vs.location_cd IS NOT NULL THEN
8271: igs_he_extract_fields_pkg.get_campus_id
8272: (p_location_cd => g_he_st_unt_vs.location_cd,
8273: p_campus_id => p_value);
8274: -- smaddali removed the code getting campusid from igs_en_stdnt_ps_att.location_cd for bug#3216400
8275: -- because student is not related to module return
8293: IF g_he_st_unt_vs.proportion_of_fte IS NOT NULL
8294: THEN
8295: p_value := Ltrim(To_Char(g_he_st_unt_vs.proportion_of_fte,'000.0') );
8296: ELSE
8297: igs_he_extract_fields_pkg.get_mod_prop_fte
8298: (p_enrolled_credit_points => g_ps_unit_ver_v.enrolled_credit_points,
8299: p_unit_level => g_ps_unit_ver_v.unit_level,
8300: p_prop_of_fte => l_fte_prop);
8301: --smaddali adding format mask '000.0' for bug 2437081
8306:
8307: ELSIF p_field_number = 7
8308: THEN
8309: -- Proportion not taught by this institution
8310: igs_he_extract_fields_pkg.get_mod_prop_not_taught
8311: (p_unit_cd => p_unit_cd,
8312: p_version_number => p_uv_version_number,
8313: p_prop_not_taught => l_prop_not_taught);
8314: --smaddali adding format mask '000.0' for bug 2437081
8316:
8317: ELSIF p_field_number = 8
8318: THEN
8319: -- Credit Transfer Scheme
8320: igs_he_extract_fields_pkg.get_credit_trans_sch
8321: (p_oss_credit_trans_sch => g_he_st_unt_vs.credit_transfer_scheme,
8322: p_hesa_credit_trans_sch => p_value);
8323:
8324: ELSIF p_field_number = 9
8333: IF g_field_defn.hesa_value.EXISTS(9)
8334: THEN
8335: IF g_field_defn.hesa_value(9) <> '999'
8336: THEN
8337: igs_he_extract_fields_pkg.get_credit_level
8338: (p_susa_credit_level => NULL ,
8339: p_poous_credit_level => g_ps_unit_ver_v.unit_level,
8340: p_hesa_credit_level => p_value);
8341: END IF;
8352: --smaddali added the initialisation of variable g_cc_rec for bug 2417370
8353: --jbaber added p_validation_country for HEFD350
8354: g_cc_rec := NULL ;
8355: g_total_ccs := 0;
8356: igs_he_extract_fields_pkg.get_cost_centres
8357: (p_person_id => NULL,
8358: p_course_cd => NULL,
8359: p_version_number => NULL,
8360: p_unit_set_cd => NULL,
8433:
8434: ELSIF p_field_number = 19
8435: THEN
8436: -- Other Institution Providing teaching 1
8437: igs_he_extract_fields_pkg.get_mod_oth_teach_inst
8438: (p_unit_cd => p_unit_cd,
8439: p_version_number => p_uv_version_number,
8440: p_oth_teach_inst => p_value);
8441:
8624:
8625: ELSIF p_field_number = 2
8626: THEN
8627: -- Hesa Institution Id
8628: igs_he_extract_fields_pkg.get_hesa_inst_id
8629: (p_hesa_inst_id => p_value);
8630:
8631: ELSIF p_field_number = 3
8632: THEN
8639: l_inst_id := 0;
8640: END IF;
8641:
8642: -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
8643: igs_he_extract_fields_pkg.get_stdnt_id
8644: (p_person_id => p_person_id,
8645: p_inst_id => l_inst_id,
8646: p_stdnt_id => p_value,
8647: p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
10072: CLOSE c_term_con ;
10073: END IF ; -- if student is leaving / continuing
10074:
10075: -- Get Award Conferral Dates
10076: igs_he_extract_fields_pkg.get_awd_conferral_dates
10077: (g_awd_table,
10078: g_he_ext_run_dtls.submission_name,
10079: g_prog_rec_flag,
10080: g_prog_type_rec_flag,
10671: IF Substr(g_he_submsn_return.record_id,4,2) = '11'
10672: OR Substr(g_he_submsn_return.record_id,4,2) = '12'
10673: THEN
10674:
10675: igs_he_extract_fields_pkg.get_awd_dtls (g_he_ext_run_dtls.submission_name,
10676: g_awd_table,
10677: g_prog_rec_flag,
10678: g_prog_type_rec_flag);
10679: END IF;