[Home] [Help]
149:
150: END deleted_alt_id_exists;
151:
152:
153: PROCEDURE import_alternate_person_id(p_interface_hesa_id igs_he_ucas_imp_int.interface_hesa_id%TYPE,
154: p_interface_id NUMBER,
155: p_status VARCHAR,
156: p_orgid NUMBER,
157: p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
468: p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
469: p_batch_id NUMBER, -- admission import batch id
470: p_orgid NUMBER ,
471: p_person_id igs_pe_person.person_id%TYPE ,
472: p_hesa_id igs_he_ucas_imp_int.interface_hesa_id%TYPE) AS
473: /******************************************************************
474: Created By : smaddali
475: Date Created By : 29-oct-2002
476: Purpose : To populate import person details interface tables
514: upper(trim(a.husid)) husid , trim(a.person_number) person_number,
515: upper(trim(a.ucasnum)) ucasnum, upper(trim(a.scotvec)) scotvec, trim(a.surname) surname,
516: trim(a.given_names) given_names , (a.birth_dt) birth_dt,
517: trim(a.country_code) country_code, trim(a.disability_type) disability_type
518: FROM igs_he_ucas_imp_int a
519: WHERE a.batch_id = l_batch_id AND
520: interface_hesa_id = p_hesa_id ;
521: c_interface_rec c_interface%ROWTYPE ;
522:
1109: l_hesa_st_spau_id igs_he_st_spa_ut.hesa_st_spau_id%TYPE;
1110:
1111:
1112: -- Get all the error records for the passed batch id and interface id
1113: CURSOR c_del_err(cp_interface_id igs_he_ucas_imp_int.interface_hesa_id%TYPE) IS
1114: SELECT err.rowid
1115: FROM igs_he_ucas_imp_err err
1116: WHERE err.batch_id = p_batch_id AND
1117: err.interface_hesa_id = NVL(cp_interface_id, err.interface_hesa_id);
1159: TRIM(a.ksqts) ksqts, TRIM(a.uksan) uksan, TRIM(a.uksats) uksats, TRIM(a.sahn) sahn,
1160: TRIM(a.sahts) sahts, TRIM(a.shn) shn, TRIM(a.shts) shts, TRIM(a.si2n) si2n, TRIM(a.si2ts) si2ts,
1161: TRIM(a.ssgcn) ssgcn, TRIM(a.ssgcts) ssgcts, TRIM(a.scsn) scsn, TRIM(a.scsts) scsts,
1162: TRIM(a.aean) aean, TRIM(a.aeats) aeats, TRIM(a.total_ucas_tariff) total_ucas_tariff
1163: FROM igs_he_ucas_imp_int a
1164: WHERE a.batch_id = p_batch_id ;
1165: c_interface_rec c_interface%ROWTYPE ;
1166:
1167:
1349: -- Get the hesa import interface record for the passed person number
1350: -- smaddali modified this cursor to add trim to remove spaces as part of bug fix 2740653
1351: CURSOR c_imp_int IS
1352: SELECT trim(a.interface_hesa_id) interface_hesa_id , trim(a.person_number) person_number
1353: FROM igs_he_ucas_imp_int a
1354: WHERE a.batch_id = p_batch_id ;
1355: c_imp_int_rec c_imp_int%ROWTYPE ;
1356:
1357: -- To get the External Calculation Type used for UCAS Tariff Calculation
1636: -- If oss person record is found but the person identifier in the interface record is null
1637: -- then update the interface record with the identified person number
1638: IF c_interface_rec.person_number IS NULL THEN
1639: c_interface_rec.person_number := l_person_number ;
1640: UPDATE igs_he_ucas_imp_int SET person_number = l_person_number
1641: WHERE batch_id =p_batch_id AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
1642: END IF ;
1643: END IF ;
1644: CLOSE c_pe_det ;
2848: -- If no errors have been logged for this interface record then delete this interface record
2849: OPEN c_del_err(c_interface_rec.interface_hesa_id) ;
2850: FETCH c_del_err INTO c_del_err_rec ;
2851: IF c_del_err%NOTFOUND THEN
2852: DELETE FROM igs_he_ucas_imp_int WHERE batch_id = p_batch_id
2853: AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
2854: END IF ;
2855: CLOSE c_del_err ;
2856: END LOOP; -- deleting successful interface records