[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.party_id person_id, p.person_last_name surname,
p.person_first_name given_names,
pp.gender sex,
pp.date_of_birth birth_dt
FROM hz_parties p , hz_person_profiles pp
WHERE p.party_number = cp_person_number AND
pp.party_id(+)=p.party_id AND
SYSDATE BETWEEN NVL(pp.effective_start_date,SYSDATE) AND NVL(pp.effective_end_date,SYSDATE);
SELECT description
FROM igs_lookups_view
WHERE lookup_type = 'IGS_HE_IMP_ERR' AND
lookup_code = p_error_code AND
closed_ind = 'N' ;
igs_he_ucas_imp_err_pkg.insert_row ( X_ROWID => l_rowid ,
X_ERROR_INTERFACE_ID => l_error_interface_id ,
X_INTERFACE_HESA_ID => p_interface_id ,
X_BATCH_ID => l_batch_id ,
X_ERROR_CODE => p_error_code ,
X_ERROR_TEXT => l_error_text ,
X_MODE => 'R' ) ;
FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
/******************************************************************
Created By : sjlaport
Date Created By : 07-February-2005
Purpose : Determines if a logically deleted Alternate Id
record exists with the value and start date.
Known limitations,enhancements,remarks:
Change History
Who When What
***************************************************************** */
CURSOR c_pe_alt_pers_del IS
SELECT *
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type= p_alt_person_type
AND api_person_id = p_alt_pers_id
AND TRUNC(start_dt) = TRUNC(p_start_dt);
fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.DELETED_ALT_ID_EXISTS'||' - '||SQLERRM);
END deleted_alt_id_exists;
for HE358 to ignore logically deleted records. Included
call to new function to check for logically deleted records.
***************************************************************** */
l_interface_api_id NUMBER ;
l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
l_insert_record BOOLEAN;
SELECT *
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type= cp_person_id_type
AND (end_dt IS NULL OR start_dt <> end_dt)
ORDER BY start_dt DESC;
l_insert_record := FALSE;
IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
INSERT INTO igs_ad_api_int(status,
org_id,
person_id_type,
alternate_id,
start_dt,
end_dt,
interface_api_id,
interface_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (p_status,
p_orgid,
p_alt_person_type,
p_alt_pers_id,
TRUNC(SYSDATE),
NULL,
igs_ad_api_int_s.NEXTVAL,
p_interface_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
INSERT INTO igs_ad_api_int(status,
org_id,
person_id_type,
alternate_id,
start_dt,
end_dt,
interface_api_id,
interface_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (p_status,
p_orgid,
p_alt_person_type,
c_pe_alt_pers_rec.api_person_id,
NVL(c_pe_alt_pers_rec.start_dt , Trunc(SYSDATE - 1) ),
Trunc(SYSDATE - 1),
igs_ad_api_int_s.NEXTVAL,
p_interface_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
l_insert_record := TRUE;
IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
l_insert_record := TRUE;
IF l_insert_record THEN
-- Create a new record based on the interface record
-- starting from SYSDATE
INSERT INTO igs_ad_api_int(status,
org_id,
person_id_type,
alternate_id,
start_dt,
end_dt,
interface_api_id,
interface_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (p_status,
p_orgid,
p_alt_person_type,
p_alt_pers_id,
TRUNC(SYSDATE),
NULL,
igs_ad_api_int_s.NEXTVAL,
p_interface_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, l_api_start_dt) THEN
log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
l_insert_record := TRUE;
IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
l_insert_record := TRUE;
IF l_insert_record THEN
-- Create record from specified start date
INSERT INTO igs_ad_api_int(status,
org_id,
person_id_type,
alternate_id,
start_dt,
end_dt,
interface_api_id,
interface_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (p_status,
p_orgid,
p_alt_person_type,
p_alt_pers_id,
l_api_start_dt,
NULL,
igs_ad_api_int_s.NEXTVAL,
p_interface_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
END IF; -- Insert record
smaddali 9-jan-03 modified cursor c_interface to trim fields being selected to remove spaces , bug 2740653
ayedubat 09-OCT-03 Removed the cursor,c_pe_stat and put an additional validation
to populate the admission statistics interface table only
if the ethnic origin is provided in the interface table for Bug# 3175020
ayedubat 14-OCT-03 Changed the Logic for populating the altenate person id interface table
if the student has an Alternate Person ID record with the same TYPE and ID
and that is the latest record for Bug# 2762866
sjlaport 07-Dec-04 Added call to method import_alternate_person_id and removed cursors that were
were no longer used
***************************************************************** */
l_status VARCHAR2(2);
l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
SELECT trim(a.interface_hesa_id) interface_hesa_id , trim(a.batch_id) batch_id,
upper(trim(a.husid)) husid , trim(a.person_number) person_number,
upper(trim(a.ucasnum)) ucasnum, upper(trim(a.scotvec)) scotvec, trim(a.surname) surname,
trim(a.given_names) given_names , (a.birth_dt) birth_dt,
trim(a.country_code) country_code, trim(a.disability_type) disability_type
FROM igs_he_ucas_imp_int a
WHERE a.batch_id = l_batch_id AND
interface_hesa_id = p_hesa_id ;
SELECT *
FROM hz_citizenship
WHERE party_id = p_person_id AND
country_code = l_oss_nation
ORDER BY date_recognized DESC ;
SELECT *
FROM hz_citizenship
WHERE party_id = p_person_id AND
country_code <> l_oss_nation
ORDER BY country_code DESC ;
SELECT dis.rowid,dis.*
FROM igs_pe_pers_disablty dis
WHERE person_id = p_person_id AND
disability_type = l_oss_disability AND
end_date IS NULL
ORDER BY start_date DESC ;
SELECT dis.*
FROM igs_pe_pers_disablty dis
WHERE person_id = p_person_id
AND disability_type <> l_oss_disability
AND start_date IS NOT NULL
ORDER BY dis.disability_type DESC ;
SELECT dis.*
FROM igs_pe_pers_disablty dis
WHERE person_id = p_person_id AND
disability_type = l_oss_disability AND
Trunc(start_date) = Trunc(SYSDATE)
ORDER BY start_date DESC ;
INSERT INTO igs_ad_interface(person_number,
interface_id,
batch_id,
org_id,
source_type_id,
person_id,
surname,
given_names,
sex,
birth_dt,
status,
record_status,
match_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES(c_interface_rec.person_number,
igs_ad_interface_s.NEXTVAL,
p_batch_id,
p_orgid,
p_source_type_id,
c_pe_det_rec.person_id,
NVL(c_interface_rec.surname,c_pe_det_rec.surname),
NVL(c_interface_rec.given_names,c_pe_det_rec.given_names),
NVL(l_oss_gender,c_pe_det_rec.sex),
NVL(c_interface_rec.birth_dt,c_pe_det_rec.birth_dt),
l_status,
l_record_status,
'15',
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_request_id,
l_program_application_id,
l_program_id,
SYSDATE ) RETURNING interface_id INTO l_interface_id;
INSERT INTO igs_ad_stat_int(interface_stat_id,
interface_id,
status,
org_id,
ethnic_origin,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_ad_stat_int_s.NEXTVAL,
l_interface_id,
l_status,
p_orgid,
l_oss_ethnicity,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_stat_id INTO l_interface_stat_id;
INSERT INTO igs_ad_disablty_int(interface_disablty_id,
interface_id,
status,
disability_type,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_ad_disablty_int_s.NEXTVAL ,
l_interface_id,
l_status,
c_other_dis_rec.disability_type,
NVL(c_other_dis_rec.start_date,TRUNC(SYSDATE)),
TRUNC(SYSDATE),
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
INSERT INTO igs_ad_disablty_int(interface_disablty_id,
interface_id,
status,
disability_type,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_ad_disablty_int_s.NEXTVAL ,
l_interface_id,
l_status,
l_oss_disability,
TRUNC(SYSDATE),
NULL,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
INSERT INTO igs_ad_disablty_int(interface_disablty_id,
interface_id,
status,
disability_type,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_ad_disablty_int_s.NEXTVAL ,
l_interface_id,
l_status,
c_pe_dis_sysdate_rec.disability_type,
NVL(c_pe_dis_sysdate_rec.start_date, Trunc(SYSDATE)),
NULL,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
IGS_PE_PERS_DISABLTY_PKG.UPDATE_ROW (
X_ROWID => c_pe_dis_rec.rowid,
X_IGS_PE_PERS_DISABLTY_ID => c_pe_dis_rec.igs_pe_pers_disablty_id,
x_PERSON_ID => c_pe_dis_rec.person_id,
x_DISABILITY_TYPE => c_pe_dis_rec.disability_type,
x_CONTACT_IND => c_pe_dis_rec.contact_ind ,
x_SPECIAL_ALLOW_CD => c_pe_dis_rec.special_allow_cd,
x_SUPPORT_LEVEL_CD => c_pe_dis_rec.support_level_cd ,
x_DOCUMENTED => c_pe_dis_rec.documented ,
x_SPECIAL_SERVICE_ID => c_pe_dis_rec.special_service_id ,
x_ATTRIBUTE_CATEGORY => c_pe_dis_rec.attribute_category ,
x_ATTRIBUTE1 => c_pe_dis_rec.attribute1,
x_ATTRIBUTE2 => c_pe_dis_rec.attribute2,
x_ATTRIBUTE3 => c_pe_dis_rec.attribute3,
x_ATTRIBUTE4 => c_pe_dis_rec.attribute4,
x_ATTRIBUTE5 => c_pe_dis_rec.attribute5,
x_ATTRIBUTE6 => c_pe_dis_rec.attribute6,
x_ATTRIBUTE7 => c_pe_dis_rec.attribute7,
x_ATTRIBUTE8 => c_pe_dis_rec.attribute8,
x_ATTRIBUTE9 => c_pe_dis_rec.attribute9,
x_ATTRIBUTE10 => c_pe_dis_rec.attribute10,
x_ATTRIBUTE11 => c_pe_dis_rec.attribute11,
x_ATTRIBUTE12 => c_pe_dis_rec.attribute12,
x_ATTRIBUTE13 => c_pe_dis_rec.attribute13,
x_ATTRIBUTE14 => c_pe_dis_rec.attribute14,
x_ATTRIBUTE15 => c_pe_dis_rec.attribute15,
x_ATTRIBUTE16 => c_pe_dis_rec.attribute16,
x_ATTRIBUTE17 => c_pe_dis_rec.attribute17,
x_ATTRIBUTE18 => c_pe_dis_rec.attribute18,
x_ATTRIBUTE19 => c_pe_dis_rec.attribute19,
x_ATTRIBUTE20 => c_pe_dis_rec.attribute20,
X_ELIG_EARLY_REG_IND => c_pe_dis_rec.elig_early_reg_ind,
X_START_DATE => TRUNC(SYSDATE),
X_END_DATE => NULL,
X_INFO_SOURCE => c_pe_dis_rec.info_source,
X_INTERVIEWER_ID => c_pe_dis_rec.interviewer_id,
X_INTERVIEWER_DATE => c_pe_dis_rec.interviewer_date,
X_MODE => 'R'
);
INSERT INTO igs_ad_disablty_int(interface_disablty_id,
interface_id,
status,
disability_type,
start_date,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_ad_disablty_int_s.NEXTVAL ,
l_interface_id,
l_status,
c_pe_dis_sysdate_rec.disability_type,
c_pe_dis_sysdate_rec.start_date,
NULL,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
interface_id,
status,
country_code,
date_recognized,
date_disowned,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_pe_citizen_int_s.NEXTVAL,
l_interface_id,
l_status,
c_other_nat_rec.country_code,
NVL(c_other_nat_rec.date_recognized,TRUNC(SYSDATE)) ,
NULL,
TRUNC(SYSDATE),
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
interface_id,
status,
country_code,
date_recognized,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_pe_citizen_int_s.NEXTVAL,
l_interface_id,
l_status,
l_oss_nation,
Trunc(SYSDATE) ,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
interface_id,
status,
country_code,
date_recognized,
date_disowned,
end_date,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (igs_pe_citizen_int_s.NEXTVAL,
l_interface_id,
l_status,
c_pe_nat_rec.country_code,
NVL(c_pe_nat_rec.date_recognized,TRUNC(SYSDATE)) ,
NULL,
NULL,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
SELECT match_set_id
FROM igs_pe_match_sets
WHERE source_type_id = p_source_type_id;
rbezawad 26-Feb-03 Modified w.r.t. Bug 2777247. Added code to insert record into IGS_AD_IMP_BATCH_DET table.
rbezawad 16-Sep-03 Modified the process w.r.t. UCFD210 Build, Bug 2893542 to populate the Previous education details into
OSS Academic History and obsolete the functionality related to IGS_UC_ATTEND_HIST.
sjlaport 24-Feb-05 Corrected reference to HESA disability association OSS_HESA_DISABILITY_ASSOC
sjlaport 31-Jan-05 Modified cursor c_alt_persid for HE358 to ignore logically deleted records.
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
***************************************************************** */
IGS_UC_HE_NOT_ENABLED_EXCEP EXCEPTION;
SELECT err.rowid
FROM igs_he_ucas_imp_err err
WHERE err.batch_id = p_batch_id AND
err.interface_hesa_id = NVL(cp_interface_id, err.interface_hesa_id);
SELECT 'X'
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_persid_type ;
SELECT 'X'
FROM igs_or_org_alt_idtyp
WHERE system_id_type = 'HESA_INST'
AND NVL (close_ind, 'N') = 'N' ;
SELECT 'X'
FROM igs_ad_source_cat_v
WHERE source_type_id = cp_source_type_id
AND category_name = 'PERSON_ACADEMIC_HISTORY'
AND include_ind = 'Y';
SELECT TRIM(a.interface_hesa_id) interface_hesa_id , TRIM(a.batch_id) batch_id,
UPPER(TRIM(a.husid)) husid , TRIM(a.person_number) person_number, TRIM(a.course_cd) course_cd,
TRIM(a.unit_set_cd) unit_set_cd, UPPER(TRIM(a.ucasnum)) ucasnum, UPPER(TRIM(a.scotvec)) scotvec,
TRIM(a.surname) surname, TRIM(a.given_names) given_names , (a.birth_dt) birth_dt,
TRIM(a.sex) sex, TRIM(a.domicile_cd) domicile_cd, TRIM(a.country_code) country_code,
TRIM(a.ethnic_origin) ethnic_origin, TRIM(a.disability_type) disability_type,
a.prev_inst_left_date prev_inst_left_date, TRIM(a.occcode) occcode, TRIM(a.highest_qual_on_entry) highest_qual_on_entry,
TRIM(a.subject_qualaim1) subject_qualaim1, TRIM(a.subject_qualaim2) subject_qualaim2 ,
TRIM(a.subject_qualaim3) subject_qualaim3 ,TRIM(a.qualaim_proportion) qualaim_proportion,
TRIM(a.fee_eligibility) fee_eligibility, TRIM(a.postcode) postcode, TRIM(a.social_class_ind) social_class_ind,
TRIM(a.occupation_code) occupation_code, TRIM(a.inst_code) inst_code, TRIM(a.gceasn) gceasn,
TRIM(a.gceasts) gceasts, TRIM(a.vceasn) vceasn, TRIM(a.vceasts) vceasts, TRIM(a.gcean) gcean,
TRIM(a.gceats) gceats, TRIM(a.vcean) vcean, TRIM(a.vceats) vceats, TRIM(a.ksqn) ksqn,
TRIM(a.ksqts) ksqts, TRIM(a.uksan) uksan, TRIM(a.uksats) uksats, TRIM(a.sahn) sahn,
TRIM(a.sahts) sahts, TRIM(a.shn) shn, TRIM(a.shts) shts, TRIM(a.si2n) si2n, TRIM(a.si2ts) si2ts,
TRIM(a.ssgcn) ssgcn, TRIM(a.ssgcts) ssgcts, TRIM(a.scsn) scsn, TRIM(a.scsts) scsts,
TRIM(a.aean) aean, TRIM(a.aeats) aeats, TRIM(a.total_ucas_tariff) total_ucas_tariff
FROM igs_he_ucas_imp_int a
WHERE a.batch_id = p_batch_id ;
SELECT party_number person_number
FROM igs_pe_alt_pers_id , hz_parties
WHERE person_id_type = cp_persid_type AND
api_person_id = cp_person_id AND
NVL(start_dt,SYSDATE) <= SYSDATE AND
end_dt IS NULL AND
party_id = pe_person_id
AND (end_dt IS NULL OR start_dt <> end_dt);
SELECT person_id ,course_cd , version_number
FROM igs_he_st_spa_all
WHERE person_id = cp_person_id AND
course_cd = NVL(cp_course_cd,course_cd) ;
SELECT 'X'
FROM igs_he_en_susa
WHERE person_id = cp_person_id AND
course_cd = NVL(cp_course_cd,course_cd) AND
unit_set_cd = NVL(cp_unit_set_cd,unit_set_cd) ;
SELECT map2
FROM igs_he_code_map_val
WHERE association_code = cp_assoc_code AND
map3 = cp_value ;
SELECT map2
FROM igs_he_code_map_val
WHERE association_code = cp_assoc_code AND
map1 = cp_value ;
SELECT grade
FROM igs_as_grd_sch_grade gsg
WHERE
EXISTS( SELECT 'X' FROM igs_he_code_values
WHERE code_type = 'HESA_HIGH_QUAL_ON_ENT' AND
value = gsg.grading_schema_cd AND
NVL(closed_ind,'N' ) = 'N' )
AND gsg.grade= cp_high_qual
AND ROWNUM < 2;
SELECT ORG_STRUCTURE_ID
FROM IGS_OR_ORG_ALT_IDS OAI,IGS_OR_ORG_ALT_IDTYP_V OAIT
WHERE OAI.ORG_alternate_ID = CP_INST_CODE
AND OAI.ORG_STRUCTURE_TYPE = 'INSTITUTE'
AND TRUNC (SYSDATE) BETWEEN TRUNC (OAI.START_DATE) AND NVL (TRUNC (OAI.END_DATE), TRUNC (SYSDATE)+1)
AND OAI.ORG_ALTERNATE_ID_TYPE = OAIT.ORG_ALTERNATE_ID_TYPE
AND OAIT.SYSTEM_ID_TYPE = 'HESA_INST';
SELECT a.field_of_study
FROM igs_ps_fld_of_study a
WHERE a.govt_field_of_study = cp_subject AND
a.closed_ind = 'N'
ORDER BY a.field_of_study ;
SELECT igs_ad_interface_batch_id_s.NEXTVAL
FROM dual;
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type = 'UCAS PER'
AND NVL(closed_ind,'N') = 'N';
SELECT spa.rowid , spa.*
FROM igs_he_st_spa_all spa
WHERE spa.person_id = cp_person_id AND
spa.course_cd = NVL(cp_course_cd,course_cd) ;
SELECT susa.rowid , susa.*
FROM igs_he_en_susa susa
WHERE susa.person_id = cp_person_id AND
susa.course_cd = NVL(cp_course_cd,course_cd) AND
susa.unit_set_cd = NVL(cp_unit_set_cd,unit_set_cd) ;
SELECT rowid
FROM igs_he_st_spa_ut_all
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd ;
SELECT a.*
FROM igs_ad_acad_history_v a
WHERE a.person_id = cp_person_id
AND a.institution_code = cp_inst_cd ;
SELECT COUNT(*)
FROM igs_ad_acad_history_v a
WHERE a.person_id = cp_person_id
AND a.institution_code = cp_inst_cd ;
SELECT person_number, last_name surname, first_name given_names, gender sex, birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT a.interface_id
FROM igs_ad_interface_all a
WHERE a.batch_id = cp_batch_id
AND a.person_id= cp_person_id
AND a.status = '2'
AND a.record_status='2';
SELECT a.person_number, a.interface_id
FROM igs_ad_interface a
WHERE a.batch_id = cp_batch_id AND
a.person_number= cp_person_number AND
( a.status IN ('2','3') OR a.record_status='3' ) ;
SELECT a.interface_acadhis_id
FROM igs_ad_acadhis_int_all a
WHERE a.interface_id = cp_interface_id
AND a.status = '3';
SELECT trim(a.interface_hesa_id) interface_hesa_id , trim(a.person_number) person_number
FROM igs_he_ucas_imp_int a
WHERE a.batch_id = p_batch_id ;
SELECT utct.tariff_calc_type_cd
FROM IGS_HE_UT_CALC_TYPE utct
WHERE utct.external_calc_ind = 'Y'
AND utct.closed_ind = 'N' ;
SELECT utla.award_cd
FROM IGS_HE_UT_LVL_AWARD utla
WHERE utla.tariff_calc_type_cd = cp_calc_type
AND utla.tariff_level_cd = cp_tariff_level_cd
AND utla.default_award_ind = 'Y'
AND utla.closed_ind = 'N' ;
igs_he_ucas_imp_err_pkg.delete_row( X_ROWID => c_del_err_rec.rowid ) ;
INSERT INTO igs_ad_imp_batch_det ( batch_id,
batch_desc,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id)
VALUES ( l_imp_batch_id,
fnd_message.get_string('IGS','IGS_HE_IMP_HESA_DET_BATCH_ID'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
);
UPDATE igs_he_ucas_imp_int SET person_number = l_person_number
WHERE batch_id =p_batch_id AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
Igs_Ad_Acad_History_Pkg.update_row (
x_rowid => l_acad_hist_rec.row_id,
x_attribute14 => l_acad_hist_rec.attribute14,
x_attribute15 => l_acad_hist_rec.attribute15,
x_attribute16 => l_acad_hist_rec.attribute16,
x_attribute17 => l_acad_hist_rec.attribute17,
x_attribute18 => l_acad_hist_rec.attribute18,
x_attribute19 => l_acad_hist_rec.attribute19,
x_attribute20 => l_acad_hist_rec.attribute20,
x_attribute13 => l_acad_hist_rec.attribute13,
x_attribute11 => l_acad_hist_rec.attribute11,
x_attribute12 => l_acad_hist_rec.attribute12,
x_education_id => l_acad_hist_rec.Education_Id,
x_person_id => l_acad_hist_rec.person_id,
x_current_inst => l_acad_hist_rec.current_inst,
x_degree_attempted => l_acad_hist_rec.degree_attempted, --modified academic History LOV Build
x_program_code => l_acad_hist_rec.Program_Code,
x_degree_earned => l_acad_hist_rec.degree_earned,
x_comments => l_acad_hist_rec.Comments,
x_start_date => l_acad_hist_rec.Start_Date,
x_end_date => NVL(c_interface_rec.prev_inst_left_date,l_acad_hist_rec.End_Date),
x_planned_completion_date => l_acad_hist_rec.planned_completion_date,
x_recalc_total_cp_attempted => l_acad_hist_rec.recalc_total_cp_attempted,
x_recalc_total_cp_earned => l_acad_hist_rec.recalc_total_cp_earned,
x_recalc_total_unit_gp => l_acad_hist_rec.recalc_total_unit_gp,
x_recalc_tot_gpa_units_attemp=> l_acad_hist_rec.recalc_total_gpa_units_attemp,
x_recalc_inst_gpa => l_acad_hist_rec.recalc_inst_gpa,
x_recalc_grading_scale_id => l_acad_hist_rec.recalc_grading_scale_id,
x_selfrep_total_cp_attempted => l_acad_hist_rec.selfrep_total_cp_attempted,
x_selfrep_total_cp_earned => l_acad_hist_rec.selfrep_total_cp_earned,
x_selfrep_total_unit_gp => l_acad_hist_rec.selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => l_acad_hist_rec.selfrep_total_gpa_units_attemp,
x_selfrep_inst_gpa => l_acad_hist_rec.selfrep_inst_gpa,
x_selfrep_grading_scale_id => l_acad_hist_rec.selfrep_grading_scale_id,
x_selfrep_weighted_gpa => l_acad_hist_rec.selfrep_weighted_gpa,
x_selfrep_rank_in_class => l_acad_hist_rec.selfrep_rank_in_class,
x_selfrep_weighed_rank => l_acad_hist_rec.selfrep_weighed_rank,
x_type_of_school => l_acad_hist_rec.type_of_school,
x_institution_code => l_acad_hist_rec.institution_code,
x_attribute_category => l_acad_hist_rec.attribute_category,
x_attribute1 => l_acad_hist_rec.attribute1,
x_attribute2 => l_acad_hist_rec.attribute2,
x_attribute3 => l_acad_hist_rec.attribute3,
x_attribute4 => l_acad_hist_rec.attribute4,
x_attribute5 => l_acad_hist_rec.attribute5,
x_attribute6 => l_acad_hist_rec.attribute6,
x_attribute7 => l_acad_hist_rec.attribute7,
x_attribute8 => l_acad_hist_rec.attribute8,
x_attribute9 => l_acad_hist_rec.attribute9,
x_attribute10 => l_acad_hist_rec.attribute10,
x_selfrep_class_size => l_acad_hist_rec.selfrep_class_size,
x_transcript_required => l_acad_hist_rec.transcript_required,
x_object_version_number => l_acad_hist_rec.object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R');
INSERT INTO igs_ad_acadhis_int_all ( interface_acadhis_id,
interface_id,
institution_code,
current_inst,
end_date,
status,
transcript_required,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES ( igs_ad_acadhis_int_s.NEXTVAL,
l_interface_id,
l_oss_inst,
'N',
c_interface_rec.prev_inst_left_date,
'2',
'N',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE) )
RETURNING interface_acadhis_id INTO l_interface_acadhis_id;
END IF ; -- end of updating or inserting attendance history records
igs_he_st_spa_all_pkg.update_row (
X_ROWID => c_upd_spa_rec.rowid ,
X_HESA_ST_SPA_ID => c_upd_spa_rec.hesa_st_spa_id,
X_ORG_ID => c_upd_spa_rec.org_id,
X_PERSON_ID => c_upd_spa_rec.person_id,
X_COURSE_CD => c_upd_spa_rec.course_cd,
X_VERSION_NUMBER => c_upd_spa_rec.version_number,
X_FE_STUDENT_MARKER => c_upd_spa_rec.fe_student_marker,
X_DOMICILE_CD => NVL(l_oss_domicile, c_upd_spa_rec.domicile_cd),
X_INST_LAST_ATTENDED => c_upd_spa_rec.inst_last_attended,
X_YEAR_LEFT_LAST_INST => c_upd_spa_rec.year_left_last_inst,
X_HIGHEST_QUAL_ON_ENTRY => NVL(c_interface_rec.highest_qual_on_entry,c_upd_spa_rec.highest_qual_on_entry),
X_DATE_QUAL_ON_ENTRY_CALC => c_upd_spa_rec.date_qual_on_entry_calc,
X_A_LEVEL_POINT_SCORE => c_upd_spa_rec.a_level_point_score,
X_HIGHERS_POINTS_SCORES => c_upd_spa_rec.highers_points_scores,
X_OCCUPATION_CODE => NVL(l_oss_occupation,c_upd_spa_rec.occupation_code),
X_COMMENCEMENT_DT => c_upd_spa_rec.commencement_dt,
X_SPECIAL_STUDENT => c_upd_spa_rec.special_student,
X_STUDENT_QUAL_AIM => c_upd_spa_rec.student_qual_aim,
X_STUDENT_FE_QUAL_AIM => c_upd_spa_rec.student_fe_qual_aim,
X_TEACHER_TRAIN_PROG_ID => c_upd_spa_rec.teacher_train_prog_id,
X_ITT_PHASE => c_upd_spa_rec.itt_phase,
X_BILINGUAL_ITT_MARKER => c_upd_spa_rec.bilingual_itt_marker,
X_TEACHING_QUAL_GAIN_SECTOR => c_upd_spa_rec.teaching_qual_gain_sector,
X_TEACHING_QUAL_GAIN_SUBJ1 => c_upd_spa_rec.teaching_qual_gain_subj1,
X_TEACHING_QUAL_GAIN_SUBJ2 => c_upd_spa_rec.teaching_qual_gain_subj2 ,
X_TEACHING_QUAL_GAIN_SUBJ3 => c_upd_spa_rec.teaching_qual_gain_subj3,
X_STUDENT_INST_NUMBER => c_upd_spa_rec.student_inst_number,
X_DESTINATION => c_upd_spa_rec.destination,
X_ITT_PROG_OUTCOME => c_upd_spa_rec.itt_prog_outcome,
X_HESA_RETURN_NAME => c_upd_spa_rec.hesa_return_name,
X_HESA_RETURN_ID => c_upd_spa_rec.hesa_return_id,
X_HESA_SUBMISSION_NAME => c_upd_spa_rec.hesa_submission_name,
X_ASSOCIATE_UCAS_NUMBER => c_upd_spa_rec.associate_ucas_number,
X_ASSOCIATE_SCOTT_CAND => c_upd_spa_rec.associate_scott_cand,
X_ASSOCIATE_TEACH_REF_NUM => c_upd_spa_rec.associate_teach_ref_num,
X_ASSOCIATE_NHS_REG_NUM => c_upd_spa_rec.associate_nhs_reg_num,
X_NHS_FUNDING_SOURCE => c_upd_spa_rec.nhs_funding_source,
X_UFI_PLACE => c_upd_spa_rec.ufi_place,
X_POSTCODE => NVL(c_interface_rec.postcode,c_upd_spa_rec.postcode),
X_SOCIAL_CLASS_IND => NVL(l_oss_social_class,c_upd_spa_rec.social_class_ind),
X_OCCCODE => NVL(c_interface_rec.occcode,c_upd_spa_rec.occcode),
X_TOTAL_UCAS_TARIFF => NVL(c_interface_rec.total_ucas_tariff,c_upd_spa_rec.total_ucas_tariff),
X_NHS_EMPLOYER => c_upd_spa_rec.nhs_employer,
X_RETURN_TYPE => c_upd_spa_rec.return_type,
X_QUAL_AIM_SUBJ1 => NVL(l_oss_subj1,c_upd_spa_rec.qual_aim_subj1),
X_QUAL_AIM_SUBJ2 => NVL(l_oss_subj2,c_upd_spa_rec.qual_aim_subj2),
X_QUAL_AIM_SUBJ3 => NVL(l_oss_subj3,c_upd_spa_rec.qual_aim_subj3),
X_QUAL_AIM_PROPORTION => NVL(l_oss_proportion,c_upd_spa_rec.qual_aim_proportion) ,
X_MODE => 'R',
X_DEPENDANTS_CD => c_upd_spa_rec.dependants_cd ,
X_IMPLIED_FUND_RATE => c_upd_spa_rec.implied_fund_rate ,
X_GOV_INITIATIVES_CD => c_upd_spa_rec.gov_initiatives_cd ,
X_UNITS_FOR_QUAL => c_upd_spa_rec.units_for_qual ,
X_DISADV_UPLIFT_ELIG_CD => c_upd_spa_rec.disadv_uplift_elig_cd ,
X_FRANCH_PARTNER_CD => c_upd_spa_rec.franch_partner_cd ,
X_UNITS_COMPLETED => c_upd_spa_rec.units_completed ,
X_FRANCH_OUT_ARR_CD => c_upd_spa_rec.franch_out_arr_cd ,
X_EMPLOYER_ROLE_CD => c_upd_spa_rec.employer_role_cd ,
X_DISADV_UPLIFT_FACTOR => c_upd_spa_rec.disadv_uplift_factor ,
X_ENH_FUND_ELIG_CD => c_upd_spa_rec.enh_fund_elig_cd,
X_EXCLUDE_FLAG => c_upd_spa_rec.exclude_flag
) ;
igs_he_st_spa_ut_all_pkg.delete_row( X_ROWID => c_del_tariff_rec.rowid );
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_gceasn ,
X_NUMBER_OF_QUAL => c_interface_rec.gceasn ,
X_TARIFF_SCORE => c_interface_rec.gceasts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_vceasn ,
X_NUMBER_OF_QUAL => c_interface_rec.vceasn ,
X_TARIFF_SCORE => c_interface_rec.vceasts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_gcean ,
X_NUMBER_OF_QUAL => c_interface_rec.gcean ,
X_TARIFF_SCORE => c_interface_rec.gceats ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_vcean ,
X_NUMBER_OF_QUAL => c_interface_rec.vcean ,
X_TARIFF_SCORE => c_interface_rec.vceats ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_ksqn ,
X_NUMBER_OF_QUAL => c_interface_rec.ksqn ,
X_TARIFF_SCORE => c_interface_rec.ksqts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_uksan,
X_NUMBER_OF_QUAL => c_interface_rec.uksan ,
X_TARIFF_SCORE => c_interface_rec.uksats ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_sahn ,
X_NUMBER_OF_QUAL => c_interface_rec.sahn ,
X_TARIFF_SCORE => c_interface_rec.sahts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_shn,
X_NUMBER_OF_QUAL => c_interface_rec.shn ,
X_TARIFF_SCORE => c_interface_rec.shts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_ssgcn,
X_NUMBER_OF_QUAL => c_interface_rec.ssgcn ,
X_TARIFF_SCORE => c_interface_rec.ssgcts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_si2n ,
X_NUMBER_OF_QUAL => c_interface_rec.si2n ,
X_TARIFF_SCORE => c_interface_rec.si2ts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_scsn ,
X_NUMBER_OF_QUAL => c_interface_rec.scsn ,
X_TARIFF_SCORE => c_interface_rec.scsts ,
X_MODE => 'R'
) ;
igs_he_st_spa_ut_all_pkg.insert_row (
X_ROWID => l_rowid ,
X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
X_ORG_ID => l_org_id ,
X_PERSON_ID => c_spa_rec.person_id ,
X_COURSE_CD => c_spa_rec.course_cd,
X_VERSION_NUMBER => c_spa_rec.version_number ,
X_QUALIFICATION_LEVEL => l_oss_aean ,
X_NUMBER_OF_QUAL => c_interface_rec.aean,
X_TARIFF_SCORE => c_interface_rec.aeats,
X_MODE => 'R'
) ;
END IF ; -- end of inserting tariff records
igs_he_en_susa_pkg.update_row(
X_ROWID => c_upd_susa_rec.rowid ,
X_HESA_EN_SUSA_ID => c_upd_susa_rec.hesa_en_susa_id,
X_PERSON_ID => c_upd_susa_rec.person_id ,
X_COURSE_CD => c_upd_susa_rec.course_cd ,
X_UNIT_SET_CD => c_upd_susa_rec.unit_set_cd ,
X_US_VERSION_NUMBER => c_upd_susa_rec.us_version_number ,
X_SEQUENCE_NUMBER => c_upd_susa_rec.sequence_number ,
X_NEW_HE_ENTRANT_CD => c_upd_susa_rec.new_he_entrant_cd ,
X_TERM_TIME_ACCOM => c_upd_susa_rec.term_time_accom ,
X_DISABILITY_ALLOW => c_upd_susa_rec.disability_allow ,
X_ADDITIONAL_SUP_BAND => c_upd_susa_rec.additional_sup_band ,
X_SLDD_DISCRETE_PROV => c_upd_susa_rec.sldd_discrete_prov ,
X_STUDY_MODE => c_upd_susa_rec.study_mode ,
X_STUDY_LOCATION => c_upd_susa_rec.study_location ,
X_FTE_PERC_OVERRIDE => c_upd_susa_rec.fte_perc_override ,
X_FRANCHISING_ACTIVITY => c_upd_susa_rec.franchising_activity ,
X_COMPLETION_STATUS => c_upd_susa_rec.completion_status ,
X_GOOD_STAND_MARKER => c_upd_susa_rec.good_stand_marker ,
X_COMPLETE_PYR_STUDY_CD => c_upd_susa_rec.complete_pyr_study_cd ,
X_CREDIT_VALUE_YOP1 => c_upd_susa_rec.credit_value_yop1 ,
X_CREDIT_VALUE_YOP2 => c_upd_susa_rec.credit_value_yop2 ,
X_CREDIT_VALUE_YOP3 => c_upd_susa_rec.credit_value_yop3 ,
X_CREDIT_VALUE_YOP4 => c_upd_susa_rec.credit_value_yop4 ,
X_CREDIT_LEVEL_ACHIEVED1 => c_upd_susa_rec.credit_level_achieved1 ,
X_CREDIT_LEVEL_ACHIEVED2 => c_upd_susa_rec.credit_level_achieved2 ,
X_CREDIT_LEVEL_ACHIEVED3 => c_upd_susa_rec.credit_level_achieved3 ,
X_CREDIT_LEVEL_ACHIEVED4 => c_upd_susa_rec.credit_level_achieved4 ,
X_CREDIT_PT_ACHIEVED1 => c_upd_susa_rec.credit_pt_achieved1 ,
X_CREDIT_PT_ACHIEVED2 => c_upd_susa_rec.credit_pt_achieved2 ,
X_CREDIT_PT_ACHIEVED3 => c_upd_susa_rec.credit_pt_achieved3 ,
X_CREDIT_PT_ACHIEVED4 => c_upd_susa_rec.credit_pt_achieved4 ,
X_CREDIT_LEVEL1 => c_upd_susa_rec.credit_level1 ,
X_CREDIT_LEVEL2 => c_upd_susa_rec.credit_level2 ,
X_CREDIT_LEVEL3 => c_upd_susa_rec.credit_level3 ,
X_CREDIT_LEVEL4 => c_upd_susa_rec.credit_level4 ,
X_ADDITIONAL_SUP_COST => c_upd_susa_rec.additional_sup_cost,
X_ENH_FUND_ELIG_CD => c_upd_susa_rec.enh_fund_elig_cd,
X_DISADV_UPLIFT_FACTOR => c_upd_susa_rec.disadv_uplift_factor,
X_YEAR_STU => c_upd_susa_rec.year_stu,
X_GRAD_SCH_GRADE => c_upd_susa_rec.grad_sch_grade ,
X_MARK => c_upd_susa_rec.mark ,
X_TEACHING_INST1 => c_upd_susa_rec.teaching_inst1 ,
X_TEACHING_INST2 => c_upd_susa_rec.teaching_inst2 ,
X_PRO_NOT_TAUGHT => c_upd_susa_rec.pro_not_taught ,
X_FUNDABILITY_CODE => c_upd_susa_rec.fundability_code ,
X_FEE_ELIGIBILITY => l_oss_fee_elig ,
X_FEE_BAND => c_upd_susa_rec.fee_band ,
X_NON_PAYMENT_REASON => c_upd_susa_rec.non_payment_reason ,
X_STUDENT_FEE => c_upd_susa_rec.student_fee ,
X_FTE_INTENSITY => c_upd_susa_rec.fte_intensity ,
X_CALCULATED_FTE => c_upd_susa_rec.calculated_fte ,
X_FTE_CALC_TYPE => c_upd_susa_rec.fte_calc_type ,
X_TYPE_OF_YEAR => c_upd_susa_rec.type_of_year ,
X_MODE => 'R'
) ;
END IF ; -- susa to be updated
DELETE FROM igs_he_ucas_imp_int WHERE batch_id = p_batch_id
AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
DELETE FROM igs_he_batch_int WHERE batch_id = p_batch_id ;