[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT current_inst_code
FROM igs_uc_defaults
WHERE current_inst_code IS NOT NULL;
SELECT MAX(configured_cycle) configured_cycle
FROM igs_uc_defaults ;
SELECT app_no,
oss_person_id
FROM igs_uc_applicants
WHERE app_no = p_appno;
PROCEDURE proc_update_ivstarn_status IS
/******************************************************************
Created By : rgangara
Date Created By : 12-JUN-03
Purpose : LOCAL PROCEDURE called from process_IVSTARN Procedure.
This process picks up all the records from the IVSTARN
interface table where status = 'I' and checks whether
corresponding record exists in IGS_AD_INTERFACE table.
This could be due to some data error or Adm import process
exitting due to some reason.
If corresponding record found then retain the status to I
and popuate Error code ELSE update the status to 'D'.
Known limitations,enhancements,remarks:
Change History
Who When What
rgangara 31-DEC-03 Enhanced logging of Person Creation Details Bug# 3327176.
***************************************************************** */
-- get all the records which have been populated successfully into UCAS tables
-- but have encountered errors while creating person using Adm Import process
CURSOR cur_ivstarn IS
SELECT stn.rowid,
stn.appno,
stn.ad_batch_id,
stn.ad_interface_id,
stn.ad_api_id ,
stn.record_status ,
stn.error_code
FROM igs_uc_istarn_ints stn
WHERE stn.record_status = 'I';
SELECT 'X'
FROM igs_ad_interface_all
WHERE interface_id = p_interface_id;
UPDATE igs_uc_istarn_ints
SET record_status = 'D'
WHERE rowid = ivstarn_rec.rowid;
UPDATE igs_uc_istarn_ints
SET error_code = '3001'
WHERE rowid = ivstarn_rec.rowid;
fnd_message.set_token('NAME','IGS_UC_PROC_APPLICATION_DATA.PROC_UPDATE_IVSTARN_STATUS'||' - '||SQLERRM);
END proc_update_ivstarn_status;
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE oss_person_id IS NULL;
SELECT pe_person_id
FROM igs_pe_alt_pers_id
WHERE api_person_id = p_ucas_appno
AND person_id_type = p_ucas_system;
fnd_Message.Set_name('IGS','IGS_UC_PRSN_UPDATE_FOR_APPNO');
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => ucas_app_person_rec.rowid
,x_app_id => ucas_app_person_rec.app_id
,x_app_no => ucas_app_person_rec.app_no
,x_check_digit => ucas_app_person_rec.check_digit
,x_personal_id => ucas_app_person_rec.personal_id
,x_enquiry_no => ucas_app_person_rec.enquiry_no
,x_oss_person_id => l_oss_person_id
,x_application_source => ucas_app_person_rec.application_source
,x_name_change_date => ucas_app_person_rec.name_change_date
,x_student_support => ucas_app_person_rec.student_support
,x_address_area => ucas_app_person_rec.address_area
,x_application_date => ucas_app_person_rec.application_date
,x_application_sent_date => ucas_app_person_rec.application_sent_date
,x_application_sent_run => ucas_app_person_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => ucas_app_person_rec.fee_payer_code
,x_fee_text => ucas_app_person_rec.fee_text
,x_domicile_apr => ucas_app_person_rec.domicile_apr
,x_code_changed_date => ucas_app_person_rec.code_changed_date
,x_school => ucas_app_person_rec.school
,x_withdrawn => ucas_app_person_rec.withdrawn
,x_withdrawn_date => ucas_app_person_rec.withdrawn_date
,x_rel_to_clear_reason => ucas_app_person_rec.rel_to_clear_reason
,x_route_b => ucas_app_person_rec.route_b
,x_exam_change_date => ucas_app_person_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => ucas_app_person_rec.winter
,x_previous => ucas_app_person_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => ucas_app_person_rec.btec
,x_ilc => ucas_app_person_rec.ilc
,x_ailc => ucas_app_person_rec.ailc
,x_ib => ucas_app_person_rec.ib
,x_manual => ucas_app_person_rec.manual
,x_reg_num => ucas_app_person_rec.reg_num
,x_oeq => ucas_app_person_rec.oeq
,x_eas => ucas_app_person_rec.eas
,x_roa => ucas_app_person_rec.roa
,x_status => ucas_app_person_rec.status
,x_firm_now => ucas_app_person_rec.firm_now
,x_firm_reply => ucas_app_person_rec.firm_reply
,x_insurance_reply => ucas_app_person_rec.insurance_reply
,x_conf_hist_firm_reply => ucas_app_person_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => ucas_app_person_rec.conf_hist_ins_reply
,x_residential_category => ucas_app_person_rec.residential_category
,x_personal_statement => ucas_app_person_rec.personal_statement
,x_match_prev => ucas_app_person_rec.match_prev
,x_match_prev_date => ucas_app_person_rec.match_prev_date
,x_match_winter => ucas_app_person_rec.match_winter
,x_match_summer => ucas_app_person_rec.match_summer
,x_gnvq_date => ucas_app_person_rec.gnvq_date
,x_ib_date => ucas_app_person_rec.ib_date
,x_ilc_date => ucas_app_person_rec.ilc_date
,x_ailc_date => ucas_app_person_rec.ailc_date
,x_gcseqa_date => ucas_app_person_rec.gcseqa_date
,x_uk_entry_date => ucas_app_person_rec.uk_entry_date
,x_prev_surname => ucas_app_person_rec.prev_surname
,x_criminal_convictions => ucas_app_person_rec.criminal_convictions
,x_sent_to_hesa => ucas_app_person_rec.sent_to_hesa
,x_sent_to_oss => ucas_app_person_rec.sent_to_oss
,x_batch_identifier => ucas_app_person_rec.batch_identifier
,x_mode => 'R'
,x_gce => ucas_app_person_rec.gce
,x_vce => ucas_app_person_rec.vce
,x_sqa => ucas_app_person_rec.sqa
,x_previousas => ucas_app_person_rec.previousas
,x_keyskills => ucas_app_person_rec.keyskills
,x_vocational => ucas_app_person_rec.vocational
,x_scn => ucas_app_person_rec.scn
,x_PrevOEQ => ucas_app_person_rec.PrevOEQ
,x_choices_transparent_ind => ucas_app_person_rec.choices_transparent_ind
,x_extra_status => ucas_app_person_rec.extra_status
,x_extra_passport_no => ucas_app_person_rec.extra_passport_no
,x_request_app_dets_ind => ucas_app_person_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => ucas_app_person_rec.request_copy_app_frm_ind
,x_cef_no => ucas_app_person_rec.cef_no
,x_system_code => ucas_app_person_rec.system_code
,x_gcse_eng => ucas_app_person_rec.gcse_eng
,x_gcse_math => ucas_app_person_rec.gcse_math
,x_degree_subject => ucas_app_person_rec.degree_subject
,x_degree_status => ucas_app_person_rec.degree_status
,x_degree_class => ucas_app_person_rec.degree_class
,x_gcse_sci => ucas_app_person_rec.gcse_sci
,x_welshspeaker => ucas_app_person_rec.welshspeaker
,x_ni_number => ucas_app_person_rec.ni_number
,x_earliest_start => ucas_app_person_rec.earliest_start
,x_near_inst => ucas_app_person_rec.near_inst
,x_pref_reg => ucas_app_person_rec.pref_reg
,x_qual_eng => ucas_app_person_rec.qual_eng
,x_qual_math => ucas_app_person_rec.qual_math
,x_qual_sci => ucas_app_person_rec.qual_sci
,x_main_qual => ucas_app_person_rec.main_qual
,x_qual_5 => ucas_app_person_rec.qual_5
,x_future_serv => ucas_app_person_rec.future_serv
,x_future_set => ucas_app_person_rec.future_set
,x_present_serv => ucas_app_person_rec.present_serv
,x_present_set => ucas_app_person_rec.present_set
,x_curr_employment => ucas_app_person_rec.curr_employment
,x_edu_qualification => ucas_app_person_rec.edu_qualification
,x_ad_batch_id => ucas_app_person_rec.ad_batch_id
,x_ad_interface_id => ucas_app_person_rec.ad_interface_id
,x_nationality => ucas_app_person_rec.nationality
,x_dual_nationality => ucas_app_person_rec.dual_nationality
,x_special_needs => ucas_app_person_rec.special_needs
,x_country_birth => ucas_app_person_rec.country_birth
);
SELECT match_set_id
FROM igs_pe_match_sets
WHERE source_type_id = p_source_type_id;
SELECT ivstn.rowid,
ivstn.*
FROM igs_uc_istarn_ints ivstn
WHERE ivstn.record_status = 'N';
SELECT uapn.rowid,
uapn.*
FROM igs_uc_app_names uapn
WHERE uapn.app_no = p_appno;
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE ucap.app_no = p_appno;
SELECT system_code
FROM igs_uc_ucas_control
WHERE ucas_cycle = (2000 + TO_NUMBER(SUBSTR(LPAD(TO_CHAR(p_appno),8,'0'),0,2)))
AND p_appno BETWEEN appno_first AND appno_maximum;
SELECT map2
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map1 = p_map1;
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_Type = 'CONTACT_TITLE'
AND lookup_code = p_adjunct
AND enabled_flag = 'Y'
AND LANGUAGE = USERENV('LANG') AND view_application_id = 222 AND security_group_id(+) = 0;
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type = 'UCAS PER';
SELECT igs_ad_interface_s.NEXTVAL
FROM DUAL;
SELECT igs_ad_interface_batch_id_s.NEXTVAL
FROM DUAL;
SELECT igs_ad_api_int_s.NEXTVAL
FROM DUAL;
l_new_rec_flag VARCHAR2(1); -- flag to check whether a new record (i.e. insert) or old record(i.e.update)
l_recs_inserted NUMBER := 0;
l_last_updated_by NUMBER ;
l_last_update_login NUMBER ;
l_last_update_date DATE ;
l_last_updated_by := l_created_by;
l_last_update_login := fnd_global.login_id;
l_last_update_date := l_creation_date;
igs_uc_applicants_pkg.insert_row -- IGSXI01B.pls
(
x_rowid => appl_rec.rowid
,x_app_id => appl_rec.app_id -- can be used since this rec variable would be null
,x_app_no => new_ivstarn_rec.appno
,x_check_digit => new_ivstarn_rec.checkdigit
,x_personal_id => new_ivstarn_rec.personalid
,x_enquiry_no => appl_rec.enquiry_no -- IN OUT parameter. hence rec variable is used.
,x_oss_person_id => NULL
,x_application_source => 'U' -- hard coded for UCAS
,x_name_change_date => NULL
,x_student_support => NULL
,x_address_area => NULL
,x_application_date => NULL
,x_application_sent_date => NULL
,x_application_sent_run => NULL
,x_lea_code => NULL
,x_fee_payer_code => NULL
,x_fee_text => NULL
,x_domicile_apr => NULL
,x_code_changed_date => NULL
,x_school => NULL
,x_withdrawn => NULL
,x_withdrawn_date => NULL
,x_rel_to_clear_reason => NULL
,x_route_b => 'N' -- default initialization
,x_exam_change_date => NULL
,x_a_levels => NULL
,x_as_levels => NULL
,x_highers => NULL
,x_csys => NULL
,x_winter => NULL
,x_previous => NULL
,x_gnvq => NULL
,x_btec => NULL
,x_ilc => NULL
,x_ailc => NULL
,x_ib => NULL
,x_manual => NULL
,x_reg_num => NULL
,x_oeq => NULL
,x_eas => NULL
,x_roa => NULL
,x_status => NULL
,x_firm_now => NULL
,x_firm_reply => NULL
,x_insurance_reply => NULL
,x_conf_hist_firm_reply => NULL
,x_conf_hist_ins_reply => NULL
,x_residential_category => NULL
,x_personal_statement => NULL
,x_match_prev => NULL
,x_match_prev_date => NULL
,x_match_winter => NULL
,x_match_summer => NULL
,x_gnvq_date => NULL
,x_ib_date => NULL
,x_ilc_date => NULL
,x_ailc_date => NULL
,x_gcseqa_date => NULL
,x_uk_entry_date => NULL
,x_prev_surname => NULL
,x_criminal_convictions => NULL
,x_sent_to_hesa => 'N'
,x_sent_to_oss => 'N'
,x_batch_identifier => NULL
,x_mode => 'R'
,x_GCE => NULL
,x_VCE => NULL
,x_SQA => NULL
,x_PREVIOUSAS => NULL
,x_KEYSKILLS => NULL
,x_VOCATIONAL => NULL
,x_SCN => NULL
,x_PrevOEQ => NULL
,x_choices_transparent_ind => NULL
,x_extra_status => NULL
,x_extra_passport_no => NULL
,x_request_app_dets_ind => NULL
,x_request_copy_app_frm_ind => NULL
,x_cef_no => NULL
,x_system_code => l_system_code
,x_gcse_eng => NULL
,x_gcse_math => NULL
,x_degree_subject => NULL
,x_degree_status => NULL
,x_degree_class => NULL
,x_gcse_sci => NULL
,x_welshspeaker => NULL
,x_ni_number => NULL
,x_earliest_start => NULL
,x_near_inst => NULL
,x_pref_reg => NULL
,x_qual_eng => NULL
,x_qual_math => NULL
,x_qual_sci => NULL
,x_main_qual => NULL
,x_qual_5 => NULL
,x_future_serv => NULL
,x_future_set => NULL
,x_present_serv => NULL
,x_present_set => NULL
,x_curr_employment => NULL
,x_edu_qualification => NULL
);
END IF; -- uc appliants insert check
END IF; -- error code check for inserting into UC applicants
igs_uc_app_names_pkg.insert_row
(
x_rowid => old_starn_rec.rowid, -- while insert this rec variable would be null.
x_app_no => new_ivstarn_rec.appno,
x_check_digit => new_ivstarn_rec.checkdigit,
x_name_change_date => new_ivstarn_rec.namechangedate,
x_title => new_ivstarn_rec.title,
x_fore_names => new_ivstarn_rec.forenames,
x_surname => new_ivstarn_rec.surname,
x_birth_date => new_ivstarn_rec.birthdate,
x_sex => new_ivstarn_rec.sex,
x_sent_to_oss_flag => 'N',
x_mode => 'R'
);
l_new_rec_flag := 'Y'; -- flag identifying a new record i.e. insert into UC Applicants.
ELSE -- Corr. rec exists in UCAS Names table hence going for update.
BEGIN
-- call the update row to update existing record in UC APP NAMES table.
igs_uc_app_names_pkg.update_row
(
x_rowid => old_starn_rec.rowid, -- while insert this rec variable would be null.
x_app_no => old_starn_rec.app_no,
x_check_digit => old_starn_rec.check_digit,
x_name_change_date => new_ivstarn_rec.namechangedate,
x_title => new_ivstarn_rec.title,
x_fore_names => new_ivstarn_rec.forenames,
x_surname => new_ivstarn_rec.surname,
x_birth_date => new_ivstarn_rec.birthdate,
x_sex => new_ivstarn_rec.sex,
x_sent_to_oss_flag => 'N',
x_mode => 'R'
);
END IF; -- insert/update APP NAMES table.
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_adm_batch_id,
fnd_message.get_string('IGS','IGS_UC_IMP_FROM_UCAS_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)
);
INSERT INTO igs_ad_interface_all
(
interface_id,
batch_id,
source_type_id,
surname,
given_names,
sex,
birth_dt,
pre_name_adjunct,
status,
record_status,
pref_alternate_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_interface_id,
l_adm_batch_id,
l_source_type_id,
new_ivstarn_rec.surname,
NVL(new_ivstarn_rec.forenames,'*'), -- given name
l_oss_sex_val, -- sex
new_ivstarn_rec.birthdate,
new_ivstarn_rec.title,
'2', -- status
'2', -- record status
NULL,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
g_error_code := '1057'; -- insert error
INSERT INTO igs_ad_api_int
(
interface_api_id
,interface_id
,person_id_type
,alternate_id
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
l_alt_pers_seq_id
,l_interface_id
,l_alt_pers_id_type -- Alternate Person ID Type - Based on system
,new_ivstarn_rec.appno -- Appno as Alternate Person ID
,'2'
,l_created_by
,l_creation_date
,l_last_updated_by
,l_last_update_date
,l_last_update_login
);
l_recs_inserted := l_recs_inserted + 1;
UPDATE igs_uc_istarn_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarn_rec.rowid;
UPDATE igs_uc_istarn_ints
SET record_status = 'I' , -- 'I' signifies intermediate stage of processing.
error_code = NULL ,
ad_batch_id = l_adm_batch_id,
ad_interface_id = l_interface_id,
ad_api_id = l_alt_pers_seq_id
WHERE rowid = new_ivstarn_rec.rowid;
fnd_message.set_token('RECCNT', TO_CHAR(l_recs_inserted));
IF l_invoke_adm_import_Proc_flag = 'Y' AND l_recs_inserted > 0 THEN
-- invoke procedure that calls Adm import process to process the person details populated.
proc_invoke_adm_imp_process (l_adm_batch_id, l_source_type_id);
proc_update_ivstarn_status;
SELECT ivsta.rowid,
ivsta.*
FROM igs_uc_istara_ints ivsta
WHERE record_status = 'N';
SELECT appaddr.rowid,
appaddr.*
FROM igs_uc_app_addreses appaddr
WHERE appaddr.app_no = p_appno;
SELECT app.rowid, app.*
FROM igs_uc_applicants app
WHERE app.app_no = cp_appno;
SELECT 'X'
FROM igs_uc_ref_country
WHERE country_code = p_code;
igs_uc_app_addreses_pkg.insert_row --
(
x_rowid => old_stara_rec.rowid
,x_app_no => new_ivstara_rec.appno
,x_address_area => new_ivstara_rec.addressarea
,x_address1 => new_ivstara_rec.address1
,x_address2 => new_ivstara_rec.address2
,x_address3 => new_ivstara_rec.address3
,x_address4 => new_ivstara_rec.address4
,x_post_code => new_ivstara_rec.postcode
,x_mail_sort => new_ivstara_rec.mailsort
,x_telephone => new_ivstara_rec.telephone
,x_fax => new_ivstara_rec.fax
,x_email => new_ivstara_rec.email
,x_home_address1 => new_ivstara_rec.homeaddress1
,x_home_address2 => new_ivstara_rec.homeaddress2
,x_home_address3 => new_ivstara_rec.homeaddress3
,x_home_address4 => new_ivstara_rec.homeaddress4
,x_home_postcode => new_ivstara_rec.homepostcode
,x_home_phone => new_ivstara_rec.homephone
,x_home_fax => new_ivstara_rec.homefax
,x_home_email => new_ivstara_rec.homeemail
,x_sent_to_oss_flag => 'N'
,x_mobile => new_ivstara_rec.mobile
,x_country_code => new_ivstara_rec.countrycode
,x_home_country_code => new_ivstara_rec.homecountrycode
,x_ad_batch_id => NULL
,x_ad_interface_id => NULL
,x_mode => 'R'
);
ELSE /* Update the record */
BEGIN
-- call the TBH to update the record
igs_uc_app_addreses_pkg.update_row --
(
x_rowid => old_stara_rec.rowid
,x_app_no => old_stara_rec.app_no
,x_address_area => new_ivstara_rec.addressarea
,x_address1 => new_ivstara_rec.address1
,x_address2 => new_ivstara_rec.address2
,x_address3 => new_ivstara_rec.address3
,x_address4 => new_ivstara_rec.address4
,x_post_code => new_ivstara_rec.postcode
,x_mail_sort => new_ivstara_rec.mailsort
,x_telephone => new_ivstara_rec.telephone
,x_fax => new_ivstara_rec.fax
,x_email => new_ivstara_rec.email
,x_home_address1 => new_ivstara_rec.homeaddress1
,x_home_address2 => new_ivstara_rec.homeaddress2
,x_home_address3 => new_ivstara_rec.homeaddress3
,x_home_address4 => new_ivstara_rec.homeaddress4
,x_home_postcode => new_ivstara_rec.homepostcode
,x_home_phone => new_ivstara_rec.homephone
,x_home_fax => new_ivstara_rec.homefax
,x_home_email => new_ivstara_rec.homeemail
,x_sent_to_oss_flag => 'N'
,x_mobile => new_ivstara_rec.mobile
,x_country_code => new_ivstara_rec.countrycode
,x_home_country_code => new_ivstara_rec.homecountrycode
,x_ad_batch_id => old_stara_rec.ad_batch_id
,x_ad_interface_id => old_stara_rec.ad_interface_id
,x_mode => 'R'
);
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => app_rec.rowid
,x_app_id => app_rec.app_id
,x_app_no => app_rec.app_no
,x_check_digit => app_rec.check_digit
,x_personal_id => app_rec.personal_id
,x_enquiry_no => app_rec.enquiry_no
,x_oss_person_id => app_rec.oss_person_id
,x_application_source => app_rec.application_source
,x_name_change_date => app_rec.name_change_date
,x_student_support => app_rec.student_support
,x_address_area => app_rec.address_area
,x_application_date => app_rec.application_date
,x_application_sent_date => app_rec.application_sent_date
,x_application_sent_run => app_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => app_rec.fee_payer_code
,x_fee_text => app_rec.fee_text
,x_domicile_apr => app_rec.domicile_apr
,x_code_changed_date => app_rec.code_changed_date
,x_school => app_rec.school
,x_withdrawn => app_rec.withdrawn
,x_withdrawn_date => app_rec.withdrawn_date
,x_rel_to_clear_reason => app_rec.rel_to_clear_reason
,x_route_b => app_rec.route_b
,x_exam_change_date => app_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => app_rec.winter
,x_previous => app_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => app_rec.btec
,x_ilc => app_rec.ilc
,x_ailc => app_rec.ailc
,x_ib => app_rec.ib
,x_manual => app_rec.manual
,x_reg_num => app_rec.reg_num
,x_oeq => app_rec.oeq
,x_eas => app_rec.eas
,x_roa => app_rec.roa
,x_status => app_rec.status
,x_firm_now => app_rec.firm_now
,x_firm_reply => app_rec.firm_reply
,x_insurance_reply => app_rec.insurance_reply
,x_conf_hist_firm_reply => app_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => app_rec.conf_hist_ins_reply
,x_residential_category => app_rec.residential_category
,x_personal_statement => app_rec.personal_statement
,x_match_prev => app_rec.match_prev
,x_match_prev_date => app_rec.match_prev_date
,x_match_winter => app_rec.match_winter
,x_match_summer => app_rec.match_summer
,x_gnvq_date => app_rec.gnvq_date
,x_ib_date => app_rec.ib_date
,x_ilc_date => app_rec.ilc_date
,x_ailc_date => app_rec.ailc_date
,x_gcseqa_date => app_rec.gcseqa_date
,x_uk_entry_date => app_rec.uk_entry_date
,x_prev_surname => app_rec.prev_surname
,x_criminal_convictions => app_rec.criminal_convictions
,x_sent_to_hesa => app_rec.sent_to_hesa
,x_sent_to_oss => 'N'
,x_batch_identifier => app_rec.batch_identifier
,x_mode => 'R'
,x_GCE => app_rec.GCE
,x_VCE => app_rec.VCE
,x_SQA => app_rec.SQA
,x_PREVIOUSAS => app_rec.previousas
,x_KEYSKILLS => app_rec.keyskills
,x_VOCATIONAL => app_rec.vocational
,x_SCN => app_rec.SCN
,x_PrevOEQ => app_rec.PrevOEQ
,x_choices_transparent_ind => app_rec.choices_transparent_ind
,x_extra_status => app_rec.extra_status
,x_extra_passport_no => app_rec.extra_passport_no
,x_request_app_dets_ind => app_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => app_rec.request_copy_app_frm_ind
,x_cef_no => app_rec.cef_no
,x_system_code => app_rec.system_code
,x_gcse_eng => app_rec.gcse_eng
,x_gcse_math => app_rec.gcse_math
,x_degree_subject => app_rec.degree_subject
,x_degree_status => app_rec.degree_status
,x_degree_class => app_rec.degree_class
,x_gcse_sci => app_rec.gcse_sci
,x_welshspeaker => app_rec.welshspeaker
,x_ni_number => app_rec.ni_number
,x_earliest_start => app_rec.earliest_start
,x_near_inst => app_rec.near_inst
,x_pref_reg => app_rec.pref_reg
,x_qual_eng => app_rec.qual_eng
,x_qual_math => app_rec.qual_math
,x_qual_sci => app_rec.qual_sci
,x_main_qual => app_rec.main_qual
,x_qual_5 => app_rec.qual_5
,x_future_serv => app_rec.future_serv
,x_future_set => app_rec.future_set
,x_present_serv => app_rec.present_serv
,x_present_set => app_rec.present_set
,x_curr_employment => app_rec.curr_employment
,x_edu_qualification => app_rec.edu_qualification
,x_ad_batch_id => app_rec.ad_batch_id
,x_ad_interface_id => app_rec.ad_interface_id
,x_nationality => app_rec.nationality
,x_dual_nationality => app_rec.dual_nationality
,x_special_needs => app_rec.special_needs
,x_country_birth => app_rec.country_birth
);
END IF; -- insert / update
UPDATE igs_uc_istara_ints
SET error_code = g_error_code
WHERE rowid = new_ivstara_rec.rowid;
UPDATE igs_uc_istara_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstara_rec.rowid;
smaddali 8-aug-03 Modified igs_uc_applicants.update call to update ni_number, criminal_conv,ukentry_date fields,bug#3088436
rgangara 05-FEB-04 Added update to App Stats table for Sent_to_HESA flag as the Domicile data comes in *K transaction but
is exported to OSS along with App Stats data. Without this, the Modified Domicile data cannot be exported
since the process picks only when the said flag is 'N'. Bug# 3405245
jchakrab 23-AUG-04 Modified for Bug#3838781 - Update the system code for existing SWAS applicants in igs_uc_applicants
when applications are re-sent by UCAS, as SWAS applicants need to be identified as FTUG applicants for
ucas_cycle > 2004.
jbaber 15-Sep-05 Modified for bug 4589994 - do not update routeb with NULL value
jchakrab 06-Sep-06 Modified for bug 5481963 - update app-choice records when IVSTARK withdrawn value is W or C
******************************************************************/
CURSOR new_ivstark_cur IS
SELECT ivstk.rowid,
ivstk.*
FROM igs_uc_istark_ints ivstk
WHERE record_status = 'N';
SELECT appl.rowid,
appl.*
FROM igs_uc_applicants appl
WHERE appl.app_no = p_appno;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = p_type
AND code = p_code;
SELECT 'X'
FROM igs_uc_com_sch
WHERE school = p_school;
SELECT 'X'
FROM igs_uc_ref_apr
WHERE dom = p_id;
SELECT 'X'
FROM igs_uc_ref_country
WHERE country_code = p_code;
SELECT stat.rowid,
stat.*
FROM igs_uc_app_stats stat
WHERE app_no = cp_appno;
SELECT uacc.rowid,
uacc.*
FROM igs_uc_app_choices uacc
WHERE uacc.app_no = p_appno
AND uacc.ucas_cycle = p_ucas_cycle
AND uacc.institute_code = p_inst_code;
ELSE /* Update the record */
-- jchakrab added to identify SWAS applicants in cycle > 2004
-- as FTUG applicants - Bug#3838781 - 23-Aug-2004
l_system_code := old_stark_rec.system_code;
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => old_stark_rec.rowid
,x_app_id => old_stark_rec.app_id
,x_app_no => old_stark_rec.app_no
,x_check_digit => old_stark_rec.check_digit
,x_personal_id => old_stark_rec.personal_id
,x_enquiry_no => old_stark_rec.enquiry_no
,x_oss_person_id => old_stark_rec.oss_person_id
,x_application_source => old_stark_rec.application_source
,x_name_change_date => old_stark_rec.name_change_date
,x_student_support => old_stark_rec.student_support
,x_address_area => old_stark_rec.address_area
,x_application_date => new_ivstark_rec.applicationdate
,x_application_sent_date => new_ivstark_rec.sentdate
,x_application_sent_run => new_ivstark_rec.runsent
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => new_ivstark_rec.feepayer
,x_fee_text => new_ivstark_rec.feetext
,x_domicile_apr => new_ivstark_rec.apr
,x_code_changed_date => new_ivstark_rec.codedchangedate
,x_school => new_ivstark_rec.school
,x_withdrawn => new_ivstark_rec.withdrawn
,x_withdrawn_date => new_ivstark_rec.withdrawndate
,x_rel_to_clear_reason => old_stark_rec.rel_to_clear_reason
,x_route_b => NVL(new_ivstark_rec.routeb, old_stark_rec.route_b)
,x_exam_change_date => new_ivstark_rec.examchangedate
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => new_ivstark_rec.winter
,x_previous => new_ivstark_rec.previousa
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => new_ivstark_rec.btec
,x_ilc => new_ivstark_rec.ilc
,x_ailc => new_ivstark_rec.aice
,x_ib => new_ivstark_rec.ib
,x_manual => new_ivstark_rec.manual
,x_reg_num => new_ivstark_rec.regno
,x_oeq => new_ivstark_rec.oeq
,x_eas => new_ivstark_rec.eas
,x_roa => new_ivstark_rec.roa
,x_status => new_ivstark_rec.status
,x_firm_now => new_ivstark_rec.firmnow
,x_firm_reply => new_ivstark_rec.firmreply
,x_insurance_reply => new_ivstark_rec.insurancereply
,x_conf_hist_firm_reply => new_ivstark_rec.confhistfirmreply
,x_conf_hist_ins_reply => new_ivstark_rec.confhistinsurancereply
,x_residential_category => new_ivstark_rec.rescat
,x_personal_statement => old_stark_rec.personal_statement
,x_match_prev => old_stark_rec.match_prev
,x_match_prev_date => old_stark_rec.match_prev_date
,x_match_winter => old_stark_rec.match_winter
,x_match_summer => old_stark_rec.match_summer
,x_gnvq_date => old_stark_rec.gnvq_date
,x_ib_date => old_stark_rec.ib_date
,x_ilc_date => old_stark_rec.ilc_date
,x_ailc_date => old_stark_rec.ailc_date
,x_gcseqa_date => old_stark_rec.gcseqa_date
,x_uk_entry_date => new_ivstark_rec.ukentrydate
,x_prev_surname => old_stark_rec.prev_surname
,x_criminal_convictions => new_ivstark_rec.criminalconv
,x_sent_to_hesa => 'N'
,x_sent_to_oss => 'N'
,x_batch_identifier => old_stark_rec.batch_identifier
,x_mode => 'R'
,x_GCE => new_ivstark_rec.GCE
,x_VCE => new_ivstark_rec.VCE
,x_SQA => new_ivstark_rec.SQA
,x_PREVIOUSAS => new_ivstark_rec.previousas
,x_KEYSKILLS => new_ivstark_rec.keyskills
,x_VOCATIONAL => new_ivstark_rec.vocational
,x_SCN => new_ivstark_rec.SCN
,x_PrevOEQ => new_ivstark_rec.PrevOEQ
,x_choices_transparent_ind => new_ivstark_rec.choicesalltransparent
,x_extra_status => new_ivstark_rec.extrastatus
,x_extra_passport_no => new_ivstark_rec.extrapassportno
,x_request_app_dets_ind => old_stark_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => old_stark_rec.request_copy_app_frm_ind
,x_cef_no => old_stark_rec.cef_no
,x_system_code => l_system_code -- update the system code - Bug#3838781
,x_gcse_eng => old_stark_rec.gcse_eng
,x_gcse_math => old_stark_rec.gcse_math
,x_degree_subject => old_stark_rec.degree_subject
,x_degree_status => old_stark_rec.degree_status
,x_degree_class => old_stark_rec.degree_class
,x_gcse_sci => old_stark_rec.gcse_sci
,x_welshspeaker => new_ivstark_rec.welshspeaker
,x_ni_number => new_ivstark_rec.ninumber
,x_earliest_start => new_ivstark_rec.earlieststart
,x_near_inst => new_ivstark_rec.nearinst
,x_pref_reg => new_ivstark_rec.prefreg
,x_qual_eng => new_ivstark_rec.qualeng
,x_qual_math => new_ivstark_rec.qualmath
,x_qual_sci => new_ivstark_rec.qualsci
,x_main_qual => new_ivstark_rec.mainqual
,x_qual_5 => new_ivstark_rec.qual5
,x_future_serv => old_stark_rec.future_serv
,x_future_set => old_stark_rec.future_set
,x_present_serv => old_stark_rec.present_serv
,x_present_set => old_stark_rec.present_set
,x_curr_employment => old_stark_rec.curr_employment
,x_edu_qualification => old_stark_rec.edu_qualification
,x_ad_batch_id => old_stark_rec.ad_batch_id
,x_ad_interface_id => old_stark_rec.ad_interface_id
,x_nationality => new_ivstark_rec.nationality
,x_dual_nationality => new_ivstark_rec.dualnationality
,x_special_needs => new_ivstark_rec.specialneeds
,x_country_birth => new_ivstark_rec.countrybirth
);
igs_uc_app_stats_pkg.update_row(
x_rowid => Cur_app_stats_rec.rowid
,x_app_stat_id => Cur_app_stats_rec.app_stat_id
,x_app_id => Cur_app_stats_rec.app_id
,x_app_no => Cur_app_stats_rec.app_no
,x_starh_ethnic => Cur_app_stats_rec.starh_ethnic
,x_starh_social_class => Cur_app_stats_rec.starh_social_class
,x_starh_pocc_edu_chg_dt => Cur_app_stats_rec.starh_pocc_edu_chg_dt
,x_starh_pocc => Cur_app_stats_rec.starh_pocc
,x_starh_pocc_text => Cur_app_stats_rec.starh_pocc_text
,x_starh_last_edu_inst => Cur_app_stats_rec.starh_last_edu_inst
,x_starh_edu_leave_date => Cur_app_stats_rec.starh_edu_leave_date
,x_starh_lea => Cur_app_stats_rec.starh_lea
,x_starx_ethnic => Cur_app_stats_rec.starx_ethnic
,x_starx_pocc_edu_chg => Cur_app_stats_rec.starx_pocc_edu_chg
,x_starx_pocc => Cur_app_stats_rec.starx_pocc
,x_starx_pocc_text => Cur_app_stats_rec.starx_pocc_text
,x_sent_to_hesa => 'N' -- set the flag to 'N' for this update.
,x_mode => 'R'
,x_starh_socio_economic => Cur_app_stats_rec.starh_socio_economic
,x_starx_socio_economic => Cur_app_stats_rec.starx_socio_economic
,x_starx_occ_background => Cur_app_stats_rec.starx_occ_background
,x_ivstarh_dependants => Cur_app_stats_rec.ivstarh_dependants
,x_ivstarh_married => Cur_app_stats_rec.ivstarh_married
,x_ivstarx_religion => Cur_app_stats_rec.ivstarx_religion
,x_ivstarx_dependants => Cur_app_stats_rec.ivstarx_dependants
,x_ivstarx_married => Cur_app_stats_rec.ivstarx_married
);
igs_uc_app_choices_pkg.update_row(
x_rowid => cur_uc_app_choices_rec.rowid
,x_app_choice_id => cur_uc_app_choices_rec.app_choice_id
,x_app_id => cur_uc_app_choices_rec.app_id
,x_app_no => cur_uc_app_choices_rec.app_no
,x_choice_no => cur_uc_app_choices_rec.choice_no
,x_last_change => cur_uc_app_choices_rec.last_change
,x_institute_code => cur_uc_app_choices_rec.institute_code
,x_ucas_program_code => cur_uc_app_choices_rec.ucas_program_code
,x_oss_program_code => cur_uc_app_choices_rec.oss_program_code
,x_oss_program_version => cur_uc_app_choices_rec.oss_program_version
,x_oss_attendance_type => cur_uc_app_choices_rec.oss_attendance_type
,x_oss_attendance_mode => cur_uc_app_choices_rec.oss_attendance_mode
,x_campus => cur_uc_app_choices_rec.campus
,x_oss_location => cur_uc_app_choices_rec.oss_location
,x_faculty => cur_uc_app_choices_rec.faculty
,x_entry_year => cur_uc_app_choices_rec.entry_year
,x_entry_month => cur_uc_app_choices_rec.entry_month
,x_point_of_entry => cur_uc_app_choices_rec.point_of_entry
,x_home => cur_uc_app_choices_rec.home
,x_deferred => cur_uc_app_choices_rec.deferred
,x_route_b_pref_round => cur_uc_app_choices_rec.route_b_pref_round
,x_route_b_actual_round => cur_uc_app_choices_rec.route_b_actual_round
,x_condition_category => cur_uc_app_choices_rec.condition_category
,x_condition_code => cur_uc_app_choices_rec.condition_code
,x_decision => 'W'
,x_decision_date => cur_uc_app_choices_rec.decision_date
,x_decision_number => cur_uc_app_choices_rec.decision_number
,x_reply => NULL
,x_summary_of_cond => cur_uc_app_choices_rec.summary_of_cond
,x_choice_cancelled => cur_uc_app_choices_rec.choice_cancelled
,x_action => cur_uc_app_choices_rec.action
,x_substitution => cur_uc_app_choices_rec.substitution
,x_date_substituted => cur_uc_app_choices_rec.date_substituted
,x_prev_institution => cur_uc_app_choices_rec.prev_institution
,x_prev_course => cur_uc_app_choices_rec.prev_course
,x_prev_campus => cur_uc_app_choices_rec.prev_campus
,x_ucas_amendment => cur_uc_app_choices_rec.ucas_amendment
,x_withdrawal_reason => cur_uc_app_choices_rec.withdrawal_reason
,x_offer_course => cur_uc_app_choices_rec.offer_course
,x_offer_campus => cur_uc_app_choices_rec.offer_campus
,x_offer_crse_length => cur_uc_app_choices_rec.offer_crse_length
,x_offer_entry_month => cur_uc_app_choices_rec.offer_entry_month
,x_offer_entry_year => cur_uc_app_choices_rec.offer_entry_year
,x_offer_entry_point => cur_uc_app_choices_rec.offer_entry_point
,x_offer_text => cur_uc_app_choices_rec.offer_text
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => cur_uc_app_choices_rec.request_id
,x_batch_id => cur_uc_app_choices_rec.batch_id
,x_extra_round_nbr => cur_uc_app_choices_rec.extra_round_nbr
,x_system_code => cur_uc_app_choices_rec.system_code
,x_part_time => cur_uc_app_choices_rec.part_time
,x_interview => cur_uc_app_choices_rec.interview
,x_late_application => cur_uc_app_choices_rec.late_application
,x_modular => cur_uc_app_choices_rec.modular
,x_residential => cur_uc_app_choices_rec.residential
,x_ucas_cycle => cur_uc_app_choices_rec.ucas_cycle
);
END IF; -- insert / update
UPDATE igs_uc_istark_ints
SET error_code = g_error_code
WHERE rowid = new_ivstark_rec.rowid;
UPDATE igs_uc_istark_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstark_rec.rowid;
arvsrini 05-MAR-04 Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
If it exists, the record is updated using choice number as IGS_UC_ISTARC_INTS.CHOICENO. Also if there exists records
in IGS_UC_TRANSACTIONS with choice_no = 99 then those records are also updated. Bug#3239860
jchakrab 23-AUG-04 Modified for Bug# 3837871 - Update system_code of existing app_choice records in
IGS_UC_APP_CHOICES with current cycle's system_code in IGS_UC_APLICANTS
jbaber 15-Sep-05 Entryyear defaults if NULL for all systems
anwest 29-May-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
******************************************************************/
CURSOR new_ivstarc_cur IS
SELECT ivstk.rowid,
ivstk.*
FROM igs_uc_istarc_ints ivstk
WHERE record_status = 'N';
SELECT appl.rowid,
appl.*
FROM igs_uc_app_choices appl
WHERE appl.app_no = p_appno
AND appl.choice_no = p_choiceno
AND appl.ucas_cycle = p_cycle;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
SELECT 'X'
FROM igs_uc_com_inst
WHERE inst = p_inst;
SELECT entry_year
FROM igs_uc_ucas_control
WHERE system_code = p_system
AND ucas_cycle = p_cycle;
SELECT oss_program_code,
oss_program_version,
oss_location,
oss_attendance_mode,
oss_attendance_type
FROM igs_uc_crse_dets
WHERE System_Code = p_system
AND ucas_program_code = p_course
AND ucas_campus = p_campus
AND Institute = p_inst;
SELECT current_inst_code
FROM igs_uc_defaults
WHERE system_code = p_sys_code;
SELECT trans.rowid,
trans.*
FROM igs_uc_transactions trans
WHERE trans.app_no = p_app_no
AND trans.choice_no = 9;
igs_uc_app_choices_pkg.insert_row -- IGSXI02B.pls
(
x_rowid => old_starc_rec.rowid
,x_app_choice_id => l_app_choice_id
,x_app_id => get_appl_dets_rec.app_id
,x_app_no => new_ivstarc_rec.appno
,x_choice_no => new_ivstarc_rec.choiceno
,x_last_change => new_ivstarc_rec.lastchange
,x_institute_code => new_ivstarc_rec.inst
,x_ucas_program_code => new_ivstarc_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarc_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => new_ivstarc_rec.faculty
,x_entry_year => l_entry_year
,x_entry_month => l_entrymonth
,x_point_of_entry => new_ivstarc_rec.entrypoint
,x_home => NVL(new_ivstarc_rec.home,'N')
,x_deferred => l_deferred
,x_route_b_pref_round => new_ivstarc_rec.routebpref
,x_route_b_actual_round => new_ivstarc_rec.routebround
,x_condition_category => NULL
,x_condition_code => NULL
,x_decision => new_ivstarc_rec.decision
,x_decision_date => new_ivstarc_rec.decisiondate
,x_decision_number => new_ivstarc_rec.decisionnumber
,x_reply => new_ivstarc_rec.reply
,x_summary_of_cond => new_ivstarc_rec.summaryconditions
,x_choice_cancelled => new_ivstarc_rec.choicecancelled
,x_action => new_ivstarc_rec.action
,x_substitution => new_ivstarc_rec.substitution
,x_date_substituted => new_ivstarc_rec.datesubstituted
,x_prev_institution => new_ivstarc_rec.previousinst
,x_prev_course => new_ivstarc_rec.previouscourse
,x_prev_campus => new_ivstarc_rec.previouscampus
,x_ucas_amendment => new_ivstarc_rec.ucasamendment
,x_withdrawal_reason => NULL
,x_offer_course => NULL
,x_offer_campus => NULL
,x_offer_crse_length => NULL
,x_offer_entry_month => NULL
,x_offer_entry_year => NULL
,x_offer_entry_point => NULL
,x_offer_text => NULL
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => new_ivstarc_rec.extraround
,x_system_code => get_appl_dets_rec.system_code
,x_part_time => NULL
,x_interview => NULL
,x_late_application => NULL
,x_modular => NULL
,x_residential => new_ivstarc_rec.residential
,x_ucas_cycle => new_ivstarc_rec.ucas_cycle
);
igs_uc_transactions_pkg.update_row
(
x_rowid => uc_transaction_rec.rowid,
x_uc_tran_id => uc_transaction_rec.uc_tran_id,
x_transaction_id => uc_transaction_rec.transaction_id,
x_datetimestamp => uc_transaction_rec.datetimestamp,
x_updater => uc_transaction_rec.updater,
x_error_code => uc_transaction_rec.error_code,
x_transaction_type => uc_transaction_rec.transaction_type,
x_app_no => uc_transaction_rec.app_no,
x_choice_no => new_ivstarc_rec.choiceno,
x_decision => uc_transaction_rec.decision,
x_program_code => uc_transaction_rec.program_code,
x_campus => uc_transaction_rec.campus,
x_entry_month => uc_transaction_rec.entry_month,
x_entry_year => uc_transaction_rec.entry_year,
x_entry_point => uc_transaction_rec.entry_point,
x_soc => uc_transaction_rec.soc,
x_comments_in_offer => uc_transaction_rec.comments_in_offer,
x_return1 => uc_transaction_rec.return1,
x_return2 => uc_transaction_rec.return2,
x_hold_flag => uc_transaction_rec.hold_flag,
x_sent_to_ucas => uc_transaction_rec.sent_to_ucas,
x_test_cond_cat => uc_transaction_rec.test_cond_cat,
x_test_cond_name => uc_transaction_rec.test_cond_name,
x_mode => 'R',
x_inst_reference => uc_transaction_rec.inst_reference,
x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
x_system_code => uc_transaction_rec.system_code,
x_ucas_cycle => uc_transaction_rec.ucas_cycle,
x_modular => uc_transaction_rec.modular,
x_part_time => uc_transaction_rec.part_time
);
igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
(
x_rowid => old_starc_9_rec.rowid
,x_app_choice_id => old_starc_9_rec.app_choice_id
,x_app_id => old_starc_9_rec.app_id
,x_app_no => old_starc_9_rec.app_no
,x_choice_no => new_ivstarc_rec.choiceno
,x_last_change => new_ivstarc_rec.lastchange
,x_institute_code => new_ivstarc_rec.inst
,x_ucas_program_code => new_ivstarc_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarc_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => new_ivstarc_rec.faculty
,x_entry_year => l_entry_year
,x_entry_month => l_entrymonth
,x_point_of_entry => new_ivstarc_rec.entrypoint
,x_home => NVL(new_ivstarc_rec.home,'N')
,x_deferred => l_deferred
,x_route_b_pref_round => new_ivstarc_rec.routebpref
,x_route_b_actual_round => new_ivstarc_rec.routebround
,x_condition_category => old_starc_9_rec.condition_category
,x_condition_code => old_starc_9_rec.condition_code
,x_decision => new_ivstarc_rec.decision
,x_decision_date => new_ivstarc_rec.decisiondate
,x_decision_number => new_ivstarc_rec.decisionnumber
,x_reply => new_ivstarc_rec.reply
,x_summary_of_cond => new_ivstarc_rec.summaryconditions
,x_choice_cancelled => new_ivstarc_rec.choicecancelled
,x_action => new_ivstarc_rec.action
,x_substitution => new_ivstarc_rec.substitution
,x_date_substituted => new_ivstarc_rec.datesubstituted
,x_prev_institution => new_ivstarc_rec.previousinst
,x_prev_course => new_ivstarc_rec.previouscourse
,x_prev_campus => new_ivstarc_rec.previouscampus
,x_ucas_amendment => new_ivstarc_rec.ucasamendment
,x_withdrawal_reason => old_starc_9_rec.withdrawal_reason
,x_offer_course => old_starc_9_rec.offer_course
,x_offer_campus => old_starc_9_rec.offer_campus
,x_offer_crse_length => old_starc_9_rec.offer_crse_length
,x_offer_entry_month => old_starc_9_rec.offer_entry_month
,x_offer_entry_year => old_starc_9_rec.offer_entry_year
,x_offer_entry_point => old_starc_9_rec.offer_entry_point
,x_offer_text => old_starc_9_rec.offer_text
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => new_ivstarc_rec.extraround
,x_system_code => get_appl_dets_rec.system_code -- update with current system_code in igs_uc_applicants -Bug#3838781
,x_part_time => old_starc_9_rec.part_time
,x_interview => old_starc_9_rec.interview
,x_late_application => old_starc_9_rec.late_application
,x_modular => old_starc_9_rec.modular
,x_residential => new_ivstarc_rec.residential
,x_ucas_cycle => new_ivstarc_rec.ucas_cycle
);
END IF; -- error code for Update row
/* Update the record */
-- For an Application Choice if the UCAS Course details are modified at UCAS end,
-- then the OSS program details for such an application needs to be derived again
-- based on the new/updated UCAS Course. Otherwise, if the UCAS Course details
-- remain the same, then the existing OSS Program details for this record are retained.
-- Checking whether the UCAS Program details have been modified at UCAS End.
IF new_ivstarc_rec.course <> old_starc_rec.ucas_program_code OR
new_ivstarc_rec.campus <> old_starc_rec.campus OR
new_ivstarc_rec.inst <> old_starc_rec.institute_code THEN
-- Deriving the OSS Program details for the new/updated UCAS Course.
OPEN get_oss_prog_cur (new_ivstarc_rec.course, new_ivstarc_rec.campus,
new_ivstarc_rec.inst, old_starc_rec.system_code);
igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
(
x_rowid => old_starc_rec.rowid
,x_app_choice_id => old_starc_rec.app_choice_id
,x_app_id => old_starc_rec.app_id
,x_app_no => old_starc_rec.app_no
,x_choice_no => old_starc_rec.choice_no
,x_last_change => new_ivstarc_rec.lastchange
,x_institute_code => new_ivstarc_rec.inst
,x_ucas_program_code => new_ivstarc_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarc_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => new_ivstarc_rec.faculty
,x_entry_year => l_entry_year
,x_entry_month => l_entrymonth
,x_point_of_entry => new_ivstarc_rec.entrypoint
,x_home => NVL(new_ivstarc_rec.home,'N')
,x_deferred => l_deferred
,x_route_b_pref_round => new_ivstarc_rec.routebpref
,x_route_b_actual_round => new_ivstarc_rec.routebround
,x_condition_category => old_starc_rec.condition_category
,x_condition_code => old_starc_rec.condition_code
,x_decision => new_ivstarc_rec.decision
,x_decision_date => new_ivstarc_rec.decisiondate
,x_decision_number => new_ivstarc_rec.decisionnumber
,x_reply => new_ivstarc_rec.reply
,x_summary_of_cond => new_ivstarc_rec.summaryconditions
,x_choice_cancelled => new_ivstarc_rec.choicecancelled
,x_action => new_ivstarc_rec.action
,x_substitution => new_ivstarc_rec.substitution
,x_date_substituted => new_ivstarc_rec.datesubstituted
,x_prev_institution => new_ivstarc_rec.previousinst
,x_prev_course => new_ivstarc_rec.previouscourse
,x_prev_campus => new_ivstarc_rec.previouscampus
,x_ucas_amendment => new_ivstarc_rec.ucasamendment
,x_withdrawal_reason => old_starc_rec.withdrawal_reason
,x_offer_course => old_starc_rec.offer_course
,x_offer_campus => old_starc_rec.offer_campus
,x_offer_crse_length => old_starc_rec.offer_crse_length
,x_offer_entry_month => old_starc_rec.offer_entry_month
,x_offer_entry_year => old_starc_rec.offer_entry_year
,x_offer_entry_point => old_starc_rec.offer_entry_point
,x_offer_text => old_starc_rec.offer_text
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => new_ivstarc_rec.extraround
,x_system_code => get_appl_dets_rec.system_code -- update with current system_code in igs_uc_applicants -Bug#3838781
,x_part_time => old_starc_rec.part_time
,x_interview => old_starc_rec.interview
,x_late_application => old_starc_rec.late_application
,x_modular => old_starc_rec.modular
,x_residential => new_ivstarc_rec.residential
,x_ucas_cycle => new_ivstarc_rec.ucas_cycle
);
END IF; -- error code for Update row
END IF; -- insert / update (starc rowid check)
UPDATE igs_uc_istarc_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarc_rec.rowid;
UPDATE igs_uc_istarc_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarc_rec.rowid;
arvsrini 08-MAR-04 Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
If it exists, the record is updated using choice number as IGS_UC_ISTARC_INTS.ROUNDNO. Also if there exists records
in IGS_UC_TRANSACTIONS with choice_no = 99 then those records are also updated.Bug#3239860
******************************************************************/
CURSOR new_ivstarg_cur IS
SELECT ivstk.rowid,
ivstk.*
FROM igs_uc_istarg_ints ivstk
WHERE record_status = 'N';
SELECT appl.rowid,
appl.*
FROM igs_uc_app_choices appl
WHERE appl.app_no = p_appno
AND appl.choice_no = p_choiceno
AND appl.ucas_cycle = p_cycle;
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE app_no = p_appno;
SELECT 'X'
FROM igs_uc_com_inst
WHERE inst = p_inst;
SELECT entry_year
FROM igs_uc_ucas_control
WHERE system_code = p_system
AND ucas_cycle = p_cycle;
SELECT oss_program_code,
oss_program_version,
oss_location,
oss_attendance_mode,
oss_attendance_type
FROM igs_uc_crse_dets
WHERE System_Code = p_system
AND ucas_program_code = p_course
AND ucas_campus = p_campus
AND Institute = p_inst;
SELECT current_inst_code
FROM igs_uc_defaults
WHERE system_code = p_sys_code;
SELECT trans.rowid,
trans.*
FROM igs_uc_transactions trans
WHERE trans.app_no = p_app_no
AND trans.choice_no = 99;
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => get_appl_dets_rec.rowid
,x_app_id => get_appl_dets_rec.app_id
,x_app_no => get_appl_dets_rec.app_no
,x_check_digit => get_appl_dets_rec.check_digit
,x_personal_id => get_appl_dets_rec.personal_id
,x_enquiry_no => get_appl_dets_rec.enquiry_no
,x_oss_person_id => get_appl_dets_rec.oss_person_id
,x_application_source => get_appl_dets_rec.application_source
,x_name_change_date => get_appl_dets_rec.name_change_date
,x_student_support => get_appl_dets_rec.student_support
,x_address_area => get_appl_dets_rec.address_area
,x_application_date => get_appl_dets_rec.application_date
,x_application_sent_date => get_appl_dets_rec.application_sent_date
,x_application_sent_run => get_appl_dets_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => get_appl_dets_rec.fee_payer_code
,x_fee_text => get_appl_dets_rec.fee_text
,x_domicile_apr => get_appl_dets_rec.domicile_apr
,x_code_changed_date => get_appl_dets_rec.code_changed_date
,x_school => get_appl_dets_rec.school
,x_withdrawn => get_appl_dets_rec.withdrawn
,x_withdrawn_date => get_appl_dets_rec.withdrawn_date
,x_rel_to_clear_reason => get_appl_dets_rec.rel_to_clear_reason
,x_route_b => get_appl_dets_rec.route_b
,x_exam_change_date => get_appl_dets_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => get_appl_dets_rec.winter
,x_previous => get_appl_dets_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => get_appl_dets_rec.btec
,x_ilc => get_appl_dets_rec.ilc
,x_ailc => get_appl_dets_rec.ailc
,x_ib => get_appl_dets_rec.ib
,x_manual => get_appl_dets_rec.manual
,x_reg_num => get_appl_dets_rec.reg_num
,x_oeq => get_appl_dets_rec.oeq
,x_eas => get_appl_dets_rec.eas
,x_roa => get_appl_dets_rec.roa
,x_status => get_appl_dets_rec.status
,x_firm_now => get_appl_dets_rec.firm_now
,x_firm_reply => get_appl_dets_rec.firm_reply
,x_insurance_reply => get_appl_dets_rec.insurance_reply
,x_conf_hist_firm_reply => get_appl_dets_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => get_appl_dets_rec.conf_hist_ins_reply
,x_residential_category => get_appl_dets_rec.residential_category
,x_personal_statement => get_appl_dets_rec.personal_statement
,x_match_prev => get_appl_dets_rec.match_prev
,x_match_prev_date => get_appl_dets_rec.match_prev_date
,x_match_winter => get_appl_dets_rec.match_winter
,x_match_summer => get_appl_dets_rec.match_summer
,x_gnvq_date => get_appl_dets_rec.gnvq_date
,x_ib_date => get_appl_dets_rec.ib_date
,x_ilc_date => get_appl_dets_rec.ilc_date
,x_ailc_date => get_appl_dets_rec.ailc_date
,x_gcseqa_date => get_appl_dets_rec.gcseqa_date
,x_uk_entry_date => get_appl_dets_rec.uk_entry_date
,x_prev_surname => get_appl_dets_rec.prev_surname
,x_criminal_convictions => get_appl_dets_rec.criminal_convictions
,x_sent_to_hesa => 'N'
,x_sent_to_oss => 'N'
,x_batch_identifier => get_appl_dets_rec.batch_identifier
,x_mode => 'R'
,x_GCE => get_appl_dets_rec.GCE
,x_VCE => get_appl_dets_rec.VCE
,x_SQA => get_appl_dets_rec.SQA
,x_PREVIOUSAS => get_appl_dets_rec.previousas
,x_KEYSKILLS => get_appl_dets_rec.keyskills
,x_VOCATIONAL => get_appl_dets_rec.vocational
,x_SCN => get_appl_dets_rec.SCN
,x_PrevOEQ => get_appl_dets_rec.PrevOEQ
,x_choices_transparent_ind => get_appl_dets_rec.choices_transparent_ind
,x_extra_status => get_appl_dets_rec.extra_status
,x_extra_passport_no => get_appl_dets_rec.extra_passport_no
,x_request_app_dets_ind => get_appl_dets_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => get_appl_dets_rec.request_copy_app_frm_ind
,x_cef_no => get_appl_dets_rec.cef_no
,x_system_code => get_appl_dets_rec.system_code
,x_gcse_eng => new_ivstarg_rec.gcseeng
,x_gcse_math => new_ivstarg_rec.gcsemath
,x_degree_subject => new_ivstarg_rec.degreesubject
,x_degree_status => new_ivstarg_rec.degreestatus
,x_degree_class => new_ivstarg_rec.degreeclass
,x_gcse_sci => new_ivstarg_rec.gcsesci
,x_welshspeaker => get_appl_dets_rec.welshspeaker
,x_ni_number => get_appl_dets_rec.ni_number
,x_earliest_start => get_appl_dets_rec.earliest_start
,x_near_inst => get_appl_dets_rec.near_inst
,x_pref_reg => get_appl_dets_rec.pref_reg
,x_qual_eng => get_appl_dets_rec.qual_eng
,x_qual_math => get_appl_dets_rec.qual_math
,x_qual_sci => get_appl_dets_rec.qual_sci
,x_main_qual => get_appl_dets_rec.main_qual
,x_qual_5 => get_appl_dets_rec.qual_5
,x_future_serv => get_appl_dets_rec.future_serv
,x_future_set => get_appl_dets_rec.future_set
,x_present_serv => get_appl_dets_rec.present_serv
,x_present_set => get_appl_dets_rec.present_set
,x_curr_employment => get_appl_dets_rec.curr_employment
,x_edu_qualification => get_appl_dets_rec.edu_qualification
,x_ad_batch_id => get_appl_dets_rec.ad_batch_id
,x_ad_interface_id => get_appl_dets_rec.ad_interface_id
,x_nationality => get_appl_dets_rec.nationality
,x_dual_nationality => get_appl_dets_rec.dual_nationality
,x_special_needs => get_appl_dets_rec.special_needs
,x_country_birth => get_appl_dets_rec.country_birth
);
igs_uc_app_choices_pkg.insert_row -- IGSXI02B.pls
(
x_rowid => old_starg_rec.rowid
,x_app_choice_id => l_app_choice_id
,x_app_id => get_appl_dets_rec.app_id
,x_app_no => new_ivstarg_rec.appno
,x_choice_no => new_ivstarg_rec.roundno
,x_last_change => new_ivstarg_rec.lastchange
,x_institute_code => new_ivstarg_rec.inst
,x_ucas_program_code => new_ivstarg_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarg_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => NULL
,x_entry_year => new_ivstarg_rec.entryyear
,x_entry_month => NVL(new_ivstarg_rec.entrymonth,0)
,x_point_of_entry => NULL
,x_home => 'N'
,x_deferred => l_deferred
,x_route_b_pref_round => NULL
,x_route_b_actual_round => NULL
,x_condition_category => NULL
,x_condition_code => NULL
,x_decision => new_ivstarg_rec.decision
,x_decision_date => NULL
,x_decision_number => NULL
,x_reply => new_ivstarg_rec.reply
,x_summary_of_cond => NULL
,x_choice_cancelled => NULL
,x_action => new_ivstarg_rec.action
,x_substitution => NULL
,x_date_substituted => NULL
,x_prev_institution => NULL
,x_prev_course => NULL
,x_prev_campus => NULL
,x_ucas_amendment => NULL
,x_withdrawal_reason => NULL
,x_offer_course => NULL
,x_offer_campus => NULL
,x_offer_crse_length => NULL
,x_offer_entry_month => NULL
,x_offer_entry_year => NULL
,x_offer_entry_point => NULL
,x_offer_text => NULL
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => NULL
,x_system_code => get_appl_dets_rec.system_code
,x_part_time => new_ivstarg_rec.parttime
,x_interview => new_ivstarg_rec.interview
,x_late_application => new_ivstarg_rec.lateapplication
,x_modular => new_ivstarg_rec.modular
,x_residential => NULL
,x_ucas_cycle => new_ivstarg_rec.ucas_cycle
);
/* Update the record */
------------------------------------------------
-- For an Application Choice if the UCAS Course details are modified at UCAS end,
-- then the OSS program details for such an application needs to be derived again
-- based on the new/updated UCAS Course. Otherwise, if the UCAS Course details
-- remain the same, then the existing OSS Program details for this record are retained.
------------------------------------------------
-- Checking whether the UCAS Program details have been modified at UCAS End.
IF new_ivstarg_rec.course <> old_starg_99_rec.ucas_program_code OR
new_ivstarg_rec.campus <> old_starg_99_rec.campus OR
new_ivstarg_rec.inst <> old_starg_99_rec.institute_code THEN
-- Derive the OSS Program details for the new/updated UCAS Course.
OPEN get_oss_prog_cur (new_ivstarg_rec.course, new_ivstarg_rec.campus,
new_ivstarg_rec.inst, old_starg_99_rec.system_code);
igs_uc_transactions_pkg.update_row
(
x_rowid => uc_transaction_rec.rowid,
x_uc_tran_id => uc_transaction_rec.uc_tran_id,
x_transaction_id => uc_transaction_rec.transaction_id,
x_datetimestamp => uc_transaction_rec.datetimestamp,
x_updater => uc_transaction_rec.updater,
x_error_code => uc_transaction_rec.error_code,
x_transaction_type => uc_transaction_rec.transaction_type,
x_app_no => uc_transaction_rec.app_no,
x_choice_no => new_ivstarg_rec.roundno,
x_decision => uc_transaction_rec.decision,
x_program_code => uc_transaction_rec.program_code,
x_campus => uc_transaction_rec.campus,
x_entry_month => uc_transaction_rec.entry_month,
x_entry_year => uc_transaction_rec.entry_year,
x_entry_point => uc_transaction_rec.entry_point,
x_soc => uc_transaction_rec.soc,
x_comments_in_offer => uc_transaction_rec.comments_in_offer,
x_return1 => uc_transaction_rec.return1,
x_return2 => uc_transaction_rec.return2,
x_hold_flag => uc_transaction_rec.hold_flag,
x_sent_to_ucas => uc_transaction_rec.sent_to_ucas,
x_test_cond_cat => uc_transaction_rec.test_cond_cat,
x_test_cond_name => uc_transaction_rec.test_cond_name,
x_mode => 'R',
x_inst_reference => uc_transaction_rec.inst_reference,
x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
x_system_code => uc_transaction_rec.system_code,
x_ucas_cycle => uc_transaction_rec.ucas_cycle,
x_modular => uc_transaction_rec.modular,
x_part_time => uc_transaction_rec.part_time
);
igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
(
x_rowid => old_starg_99_rec.rowid
,x_app_choice_id => old_starg_99_rec.app_choice_id
,x_app_id => old_starg_99_rec.app_id
,x_app_no => old_starg_99_rec.app_no
,x_choice_no => new_ivstarg_rec.roundno
,x_last_change => new_ivstarg_rec.lastchange
,x_institute_code => new_ivstarg_rec.inst
,x_ucas_program_code => new_ivstarg_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarg_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => old_starg_99_rec.faculty
,x_entry_year => NVL(new_ivstarg_rec.entryyear,0)
,x_entry_month => NVL(new_ivstarg_rec.entrymonth,0)
,x_point_of_entry => old_starg_99_rec.point_of_entry
,x_home => old_starg_99_rec.home
,x_deferred => l_deferred
,x_route_b_pref_round => old_starg_99_rec.route_b_pref_round
,x_route_b_actual_round => old_starg_99_rec.route_b_actual_round
,x_condition_category => old_starg_99_rec.condition_category
,x_condition_code => old_starg_99_rec.condition_code
,x_decision => new_ivstarg_rec.decision
,x_decision_date => old_starg_99_rec.decision_date
,x_decision_number => old_starg_99_rec.decision_number
,x_reply => new_ivstarg_rec.reply
,x_summary_of_cond => old_starg_99_rec.summary_of_cond
,x_choice_cancelled => old_starg_99_rec.choice_cancelled
,x_action => new_ivstarg_rec.action
,x_substitution => old_starg_99_rec.substitution
,x_date_substituted => old_starg_99_rec.date_substituted
,x_prev_institution => old_starg_99_rec.prev_institution
,x_prev_course => old_starg_99_rec.prev_course
,x_prev_campus => old_starg_99_rec.prev_campus
,x_ucas_amendment => old_starg_99_rec.ucas_amendment
,x_withdrawal_reason => old_starg_99_rec.withdrawal_reason
,x_offer_course => old_starg_99_rec.offer_course
,x_offer_campus => old_starg_99_rec.offer_campus
,x_offer_crse_length => old_starg_99_rec.offer_crse_length
,x_offer_entry_month => old_starg_99_rec.offer_entry_month
,x_offer_entry_year => old_starg_99_rec.offer_entry_year
,x_offer_entry_point => old_starg_99_rec.offer_entry_point
,x_offer_text => old_starg_99_rec.offer_text
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => old_starg_99_rec.extra_round_nbr
,x_system_code => old_starg_99_rec.system_code
,x_part_time => new_ivstarg_rec.parttime
,x_interview => new_ivstarg_rec.interview
,x_late_application => new_ivstarg_rec.lateapplication
,x_modular => new_ivstarg_rec.modular
,x_residential => old_starg_99_rec.residential
,x_ucas_cycle => old_starg_99_rec.ucas_cycle
);
/* Update the record */
------------------------------------------------
-- For an Application Choice if the UCAS Course details are modified at UCAS end,
-- then the OSS program details for such an application needs to be derived again
-- based on the new/updated UCAS Course. Otherwise, if the UCAS Course details
-- remain the same, then the existing OSS Program details for this record are retained.
------------------------------------------------
-- Checking whether the UCAS Program details have been modified at UCAS End.
IF new_ivstarg_rec.course <> old_starg_rec.ucas_program_code OR
new_ivstarg_rec.campus <> old_starg_rec.campus OR
new_ivstarg_rec.inst <> old_starg_rec.institute_code THEN
-- Derive the OSS Program details for the new/updated UCAS Course.
OPEN get_oss_prog_cur (new_ivstarg_rec.course, new_ivstarg_rec.campus,
new_ivstarg_rec.inst, old_starg_rec.system_code);
igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
(
x_rowid => old_starg_rec.rowid
,x_app_choice_id => old_starg_rec.app_choice_id
,x_app_id => old_starg_rec.app_id
,x_app_no => old_starg_rec.app_no
,x_choice_no => old_starg_rec.choice_no
,x_last_change => new_ivstarg_rec.lastchange
,x_institute_code => new_ivstarg_rec.inst
,x_ucas_program_code => new_ivstarg_rec.course
,x_oss_program_code => oss_prog_rec.oss_program_code
,x_oss_program_version => oss_prog_rec.oss_program_version
,x_oss_attendance_type => oss_prog_rec.oss_attendance_type
,x_oss_attendance_mode => oss_prog_rec.oss_attendance_mode
,x_campus => new_ivstarg_rec.campus
,x_oss_location => oss_prog_rec.oss_location
,x_faculty => old_starg_rec.faculty
,x_entry_year => NVL(new_ivstarg_rec.entryyear,0)
,x_entry_month => NVL(new_ivstarg_rec.entrymonth,0)
,x_point_of_entry => old_starg_rec.point_of_entry
,x_home => old_starg_rec.home
,x_deferred => l_deferred
,x_route_b_pref_round => old_starg_rec.route_b_pref_round
,x_route_b_actual_round => old_starg_rec.route_b_actual_round
,x_condition_category => old_starg_rec.condition_category
,x_condition_code => old_starg_rec.condition_code
,x_decision => new_ivstarg_rec.decision
,x_decision_date => old_starg_rec.decision_date
,x_decision_number => old_starg_rec.decision_number
,x_reply => new_ivstarg_rec.reply
,x_summary_of_cond => old_starg_rec.summary_of_cond
,x_choice_cancelled => old_starg_rec.choice_cancelled
,x_action => new_ivstarg_rec.action
,x_substitution => old_starg_rec.substitution
,x_date_substituted => old_starg_rec.date_substituted
,x_prev_institution => old_starg_rec.prev_institution
,x_prev_course => old_starg_rec.prev_course
,x_prev_campus => old_starg_rec.prev_campus
,x_ucas_amendment => old_starg_rec.ucas_amendment
,x_withdrawal_reason => old_starg_rec.withdrawal_reason
,x_offer_course => old_starg_rec.offer_course
,x_offer_campus => old_starg_rec.offer_campus
,x_offer_crse_length => old_starg_rec.offer_crse_length
,x_offer_entry_month => old_starg_rec.offer_entry_month
,x_offer_entry_year => old_starg_rec.offer_entry_year
,x_offer_entry_point => old_starg_rec.offer_entry_point
,x_offer_text => old_starg_rec.offer_text
,x_mode => 'R'
,x_export_to_oss_status => 'NEW'
,x_error_code => NULL
,x_request_id => NULL
,x_batch_id => NULL
,x_extra_round_nbr => old_starg_rec.extra_round_nbr
,x_system_code => old_starg_rec.system_code
,x_part_time => new_ivstarg_rec.parttime
,x_interview => new_ivstarg_rec.interview
,x_late_application => new_ivstarg_rec.lateapplication
,x_modular => new_ivstarg_rec.modular
,x_residential => old_starg_rec.residential
,x_ucas_cycle => old_starg_rec.ucas_cycle
);
END IF; -- insert / update (starg rowid check)
UPDATE igs_uc_istarg_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarg_rec.rowid;
UPDATE igs_uc_istarg_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarg_rec.rowid;
SELECT ivstt.rowid,
ivstt.*
FROM igs_uc_istart_ints ivstt
WHERE record_status = 'N';
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE ucap.app_no = p_appno;
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => old_start_rec.rowid
,x_app_id => old_start_rec.app_id
,x_app_no => old_start_rec.app_no
,x_check_digit => old_start_rec.check_digit
,x_personal_id => old_start_rec.personal_id
,x_enquiry_no => old_start_rec.enquiry_no
,x_oss_person_id => old_start_rec.oss_person_id
,x_application_source => old_start_rec.application_source
,x_name_change_date => old_start_rec.name_change_date
,x_student_support => old_start_rec.student_support
,x_address_area => old_start_rec.address_area
,x_application_date => old_start_rec.application_date
,x_application_sent_date => old_start_rec.application_sent_date
,x_application_sent_run => old_start_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => old_start_rec.fee_payer_code
,x_fee_text => old_start_rec.fee_text
,x_domicile_apr => old_start_rec.domicile_apr
,x_code_changed_date => old_start_rec.code_changed_date
,x_school => old_start_rec.school
,x_withdrawn => old_start_rec.withdrawn
,x_withdrawn_date => old_start_rec.withdrawn_date
,x_rel_to_clear_reason => old_start_rec.rel_to_clear_reason
,x_route_b => old_start_rec.route_b
,x_exam_change_date => old_start_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => old_start_rec.winter
,x_previous => old_start_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => old_start_rec.btec
,x_ilc => old_start_rec.ilc
,x_ailc => old_start_rec.ailc
,x_ib => old_start_rec.ib
,x_manual => old_start_rec.manual
,x_reg_num => old_start_rec.reg_num
,x_oeq => old_start_rec.oeq
,x_eas => old_start_rec.eas
,x_roa => old_start_rec.roa
,x_status => old_start_rec.status
,x_firm_now => old_start_rec.firm_now
,x_firm_reply => old_start_rec.firm_reply
,x_insurance_reply => old_start_rec.insurance_reply
,x_conf_hist_firm_reply => old_start_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => old_start_rec.conf_hist_ins_reply
,x_residential_category => old_start_rec.residential_category
,x_personal_statement => old_start_rec.personal_statement
,x_match_prev => old_start_rec.match_prev
,x_match_prev_date => old_start_rec.match_prev_date
,x_match_winter => old_start_rec.match_winter
,x_match_summer => old_start_rec.match_summer
,x_gnvq_date => old_start_rec.gnvq_date
,x_ib_date => old_start_rec.ib_date
,x_ilc_date => old_start_rec.ilc_date
,x_ailc_date => old_start_rec.ailc_date
,x_gcseqa_date => old_start_rec.gcseqa_date
,x_uk_entry_date => old_start_rec.uk_entry_date
,x_prev_surname => old_start_rec.prev_surname
,x_criminal_convictions => old_start_rec.criminal_convictions
,x_sent_to_hesa => 'N'
,x_sent_to_oss => 'N'
,x_batch_identifier => old_start_rec.batch_identifier
,x_mode => 'R'
,x_gce => old_start_rec.gce
,x_vce => old_start_rec.vce
,x_sqa => old_start_rec.sqa
,x_previousas => old_start_rec.previousas
,x_keyskills => old_start_rec.keyskills
,x_vocational => old_start_rec.vocational
,x_scn => old_start_rec.scn
,x_PrevOEQ => old_start_rec.PrevOEQ
,x_choices_transparent_ind => old_start_rec.choices_transparent_ind
,x_extra_status => old_start_rec.extra_status
,x_extra_passport_no => old_start_rec.extra_passport_no
,x_request_app_dets_ind => old_start_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => old_start_rec.request_copy_app_frm_ind
,x_cef_no => old_start_rec.cef_no
,x_system_code => old_start_rec.system_code
,x_gcse_eng => old_start_rec.gcse_eng
,x_gcse_math => old_start_rec.gcse_math
,x_degree_subject => old_start_rec.degree_subject
,x_degree_status => old_start_rec.degree_status
,x_degree_class => old_start_rec.degree_class
,x_gcse_sci => old_start_rec.gcse_sci
,x_welshspeaker => old_start_rec.welshspeaker
,x_ni_number => old_start_rec.ni_number
,x_earliest_start => old_start_rec.earliest_start
,x_near_inst => old_start_rec.near_inst
,x_pref_reg => old_start_rec.pref_reg
,x_qual_eng => old_start_rec.qual_eng
,x_qual_math => old_start_rec.qual_math
,x_qual_sci => old_start_rec.qual_sci
,x_main_qual => old_start_rec.main_qual
,x_qual_5 => old_start_rec.qual_5
,x_future_serv => new_ivstart_rec.futureserv
,x_future_set => new_ivstart_rec.futureset
,x_present_serv => new_ivstart_rec.presentserv
,x_present_set => new_ivstart_rec.presentset
,x_curr_employment => new_ivstart_rec.curremp
,x_edu_qualification => new_ivstart_rec.eduqual
,x_ad_batch_id => old_start_rec.ad_batch_id
,x_ad_interface_id => old_start_rec.ad_interface_id
,x_nationality => old_start_rec.nationality
,x_dual_nationality => old_start_rec.dual_nationality
,x_special_needs => old_start_rec.special_needs
,x_country_birth => old_start_rec.country_birth
);
UPDATE igs_uc_istart_ints
SET error_code = g_error_code
WHERE rowid = new_ivstart_rec.rowid;
UPDATE igs_uc_istart_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstart_rec.rowid;
SELECT ivqual.rowid,
ivqual.*
FROM igs_uc_iqual_ints ivqual
WHERE ivqual.record_status = 'N';
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE ucap.app_no = p_appno;
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => old_qual_rec.rowid
,x_app_id => old_qual_rec.app_id
,x_app_no => old_qual_rec.app_no
,x_check_digit => old_qual_rec.check_digit
,x_personal_id => old_qual_rec.personal_id
,x_enquiry_no => old_qual_rec.enquiry_no
,x_oss_person_id => old_qual_rec.oss_person_id
,x_application_source => old_qual_rec.application_source
,x_name_change_date => old_qual_rec.name_change_date
,x_student_support => old_qual_rec.student_support
,x_address_area => old_qual_rec.address_area
,x_application_date => old_qual_rec.application_date
,x_application_sent_date => old_qual_rec.application_sent_date
,x_application_sent_run => old_qual_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => old_qual_rec.fee_payer_code
,x_fee_text => old_qual_rec.fee_text
,x_domicile_apr => old_qual_rec.domicile_apr
,x_code_changed_date => old_qual_rec.code_changed_date
,x_school => old_qual_rec.school
,x_withdrawn => old_qual_rec.withdrawn
,x_withdrawn_date => old_qual_rec.withdrawn_date
,x_rel_to_clear_reason => old_qual_rec.rel_to_clear_reason
,x_route_b => old_qual_rec.route_b
,x_exam_change_date => old_qual_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => old_qual_rec.winter
,x_previous => old_qual_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => old_qual_rec.btec
,x_ilc => old_qual_rec.ilc
,x_ailc => old_qual_rec.ailc
,x_ib => old_qual_rec.ib
,x_manual => old_qual_rec.manual
,x_reg_num => old_qual_rec.reg_num
,x_oeq => old_qual_rec.oeq
,x_eas => old_qual_rec.eas
,x_roa => old_qual_rec.roa
,x_status => old_qual_rec.status
,x_firm_now => old_qual_rec.firm_now
,x_firm_reply => old_qual_rec.firm_reply
,x_insurance_reply => old_qual_rec.insurance_reply
,x_conf_hist_firm_reply => old_qual_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => old_qual_rec.conf_hist_ins_reply
,x_residential_category => old_qual_rec.residential_category
,x_personal_statement => old_qual_rec.personal_statement
,x_match_prev => new_ivqual_rec.matchprevious
,x_match_prev_date => new_ivqual_rec.matchpreviousdate
,x_match_winter => new_ivqual_rec.matchwinter
,x_match_summer => new_ivqual_rec.matchsummer
,x_gnvq_date => new_ivqual_rec.gnvqdate
,x_ib_date => new_ivqual_rec.ibdate
,x_ilc_date => new_ivqual_rec.ilcdate
,x_ailc_date => new_ivqual_rec.aicedate
,x_gcseqa_date => new_ivqual_rec.gcesqadate
,x_uk_entry_date => old_qual_rec.uk_entry_date
,x_prev_surname => old_qual_rec.prev_surname
,x_criminal_convictions => old_qual_rec.criminal_convictions
,x_sent_to_hesa => 'N'
,x_sent_to_oss => 'N'
,x_batch_identifier => old_qual_rec.batch_identifier
,x_mode => 'R'
,x_gce => old_qual_rec.gce
,x_vce => old_qual_rec.vce
,x_sqa => old_qual_rec.sqa
,x_previousas => old_qual_rec.previousas
,x_keyskills => old_qual_rec.keyskills
,x_vocational => old_qual_rec.vocational
,x_scn => old_qual_rec.scn
,x_PrevOEQ => old_qual_rec.PrevOEQ
,x_choices_transparent_ind => old_qual_rec.choices_transparent_ind
,x_extra_status => old_qual_rec.extra_status
,x_extra_passport_no => old_qual_rec.extra_passport_no
,x_request_app_dets_ind => old_qual_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => old_qual_rec.request_copy_app_frm_ind
,x_cef_no => old_qual_rec.cef_no
,x_system_code => old_qual_rec.system_code
,x_gcse_eng => old_qual_rec.gcse_eng
,x_gcse_math => old_qual_rec.gcse_math
,x_degree_subject => old_qual_rec.degree_subject
,x_degree_status => old_qual_rec.degree_status
,x_degree_class => old_qual_rec.degree_class
,x_gcse_sci => old_qual_rec.gcse_sci
,x_welshspeaker => old_qual_rec.welshspeaker
,x_ni_number => old_qual_rec.ni_number
,x_earliest_start => old_qual_rec.earliest_start
,x_near_inst => old_qual_rec.near_inst
,x_pref_reg => old_qual_rec.pref_reg
,x_qual_eng => old_qual_rec.qual_eng
,x_qual_math => old_qual_rec.qual_math
,x_qual_sci => old_qual_rec.qual_sci
,x_main_qual => old_qual_rec.main_qual
,x_qual_5 => old_qual_rec.qual_5
,x_future_serv => old_qual_rec.future_serv
,x_future_set => old_qual_rec.future_set
,x_present_serv => old_qual_rec.present_serv
,x_present_set => old_qual_rec.present_set
,x_curr_employment => old_qual_rec.curr_employment
,x_edu_qualification => old_qual_rec.edu_qualification
,x_ad_batch_id => old_qual_rec.ad_batch_id
,x_ad_interface_id => old_qual_rec.ad_interface_id
,x_nationality => old_qual_rec.nationality
,x_dual_nationality => old_qual_rec.dual_nationality
,x_special_needs => old_qual_rec.special_needs
,x_country_birth => old_qual_rec.country_birth
);
UPDATE igs_uc_iqual_ints
SET error_code = g_error_code
WHERE rowid = new_ivqual_rec.rowid;
UPDATE igs_uc_iqual_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivqual_rec.rowid;
has to be updated only when the existing
personal statement is NULL.
Known limitations,enhancements,remarks:
Change History
Who When What
******************************************************************/
CURSOR new_ivstmnt_cur IS
SELECT ivstmt.rowid,
ivstmt.*
FROM igs_uc_istmnt_ints ivstmt
WHERE ivstmt.record_status = 'N';
SELECT ucap.rowid,
ucap.*
FROM igs_uc_applicants ucap
WHERE ucap.app_no = p_appno;
igs_uc_applicants_pkg.update_row -- IGSXI01B.pls
(
x_rowid => old_stmt_rec.rowid
,x_app_id => old_stmt_rec.app_id
,x_app_no => old_stmt_rec.app_no
,x_check_digit => old_stmt_rec.check_digit
,x_personal_id => old_stmt_rec.personal_id
,x_enquiry_no => old_stmt_rec.enquiry_no
,x_oss_person_id => old_stmt_rec.oss_person_id
,x_application_source => old_stmt_rec.application_source
,x_name_change_date => old_stmt_rec.name_change_date
,x_student_support => old_stmt_rec.student_support
,x_address_area => old_stmt_rec.address_area
,x_application_date => old_stmt_rec.application_date
,x_application_sent_date => old_stmt_rec.application_sent_date
,x_application_sent_run => old_stmt_rec.application_sent_run
,x_lea_code => NULL -- obsoleted by UCAS
,x_fee_payer_code => old_stmt_rec.fee_payer_code
,x_fee_text => old_stmt_rec.fee_text
,x_domicile_apr => old_stmt_rec.domicile_apr
,x_code_changed_date => old_stmt_rec.code_changed_date
,x_school => old_stmt_rec.school
,x_withdrawn => old_stmt_rec.withdrawn
,x_withdrawn_date => old_stmt_rec.withdrawn_date
,x_rel_to_clear_reason => old_stmt_rec.rel_to_clear_reason
,x_route_b => old_stmt_rec.route_b
,x_exam_change_date => old_stmt_rec.exam_change_date
,x_a_levels => NULL -- obsoleted by UCAS
,x_as_levels => NULL -- obsoleted by UCAS
,x_highers => NULL -- obsoleted by UCAS
,x_csys => NULL -- obsoleted by UCAS
,x_winter => old_stmt_rec.winter
,x_previous => old_stmt_rec.previous
,x_gnvq => NULL -- obsoleted by UCAS
,x_btec => old_stmt_rec.btec
,x_ilc => old_stmt_rec.ilc
,x_ailc => old_stmt_rec.ailc
,x_ib => old_stmt_rec.ib
,x_manual => old_stmt_rec.manual
,x_reg_num => old_stmt_rec.reg_num
,x_oeq => old_stmt_rec.oeq
,x_eas => old_stmt_rec.eas
,x_roa => old_stmt_rec.roa
,x_status => old_stmt_rec.status
,x_firm_now => old_stmt_rec.firm_now
,x_firm_reply => old_stmt_rec.firm_reply
,x_insurance_reply => old_stmt_rec.insurance_reply
,x_conf_hist_firm_reply => old_stmt_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => old_stmt_rec.conf_hist_ins_reply
,x_residential_category => old_stmt_rec.residential_category
,x_personal_statement => new_ivstmt_rec.statement
,x_match_prev => old_stmt_rec.match_prev
,x_match_prev_date => old_stmt_rec.match_prev_date
,x_match_winter => old_stmt_rec.match_winter
,x_match_summer => old_stmt_rec.match_summer
,x_gnvq_date => old_stmt_rec.gnvq_date
,x_ib_date => old_stmt_rec.ib_date
,x_ilc_date => old_stmt_rec.ilc_date
,x_ailc_date => old_stmt_rec.ailc_date
,x_gcseqa_date => old_stmt_rec.gcseqa_date
,x_uk_entry_date => old_stmt_rec.uk_entry_date
,x_prev_surname => old_stmt_rec.prev_surname
,x_criminal_convictions => old_stmt_rec.criminal_convictions
,x_sent_to_hesa => old_stmt_rec.sent_to_hesa
,x_sent_to_oss => old_stmt_rec.sent_to_oss
,x_batch_identifier => old_stmt_rec.batch_identifier
,x_mode => 'R'
,x_gce => old_stmt_rec.gce
,x_vce => old_stmt_rec.vce
,x_sqa => old_stmt_rec.sqa
,x_previousas => old_stmt_rec.previousas
,x_keyskills => old_stmt_rec.keyskills
,x_vocational => old_stmt_rec.vocational
,x_scn => old_stmt_rec.scn
,x_PrevOEQ => old_stmt_rec.PrevOEQ
,x_choices_transparent_ind => old_stmt_rec.choices_transparent_ind
,x_extra_status => old_stmt_rec.extra_status
,x_extra_passport_no => old_stmt_rec.extra_passport_no
,x_request_app_dets_ind => old_stmt_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => old_stmt_rec.request_copy_app_frm_ind
,x_cef_no => old_stmt_rec.cef_no
,x_system_code => old_stmt_rec.system_code
,x_gcse_eng => old_stmt_rec.gcse_eng
,x_gcse_math => old_stmt_rec.gcse_math
,x_degree_subject => old_stmt_rec.degree_subject
,x_degree_status => old_stmt_rec.degree_status
,x_degree_class => old_stmt_rec.degree_class
,x_gcse_sci => old_stmt_rec.gcse_sci
,x_welshspeaker => old_stmt_rec.welshspeaker
,x_ni_number => old_stmt_rec.ni_number
,x_earliest_start => old_stmt_rec.earliest_start
,x_near_inst => old_stmt_rec.near_inst
,x_pref_reg => old_stmt_rec.pref_reg
,x_qual_eng => old_stmt_rec.qual_eng
,x_qual_math => old_stmt_rec.qual_math
,x_qual_sci => old_stmt_rec.qual_sci
,x_main_qual => old_stmt_rec.main_qual
,x_qual_5 => old_stmt_rec.qual_5
,x_future_serv => old_stmt_rec.future_serv
,x_future_set => old_stmt_rec.future_set
,x_present_serv => old_stmt_rec.present_serv
,x_present_set => old_stmt_rec.present_set
,x_curr_employment => old_stmt_rec.curr_employment
,x_edu_qualification => old_stmt_rec.edu_qualification
,x_ad_batch_id => old_stmt_rec.ad_batch_id
,x_ad_interface_id => old_stmt_rec.ad_interface_id
,x_nationality => old_stmt_rec.nationality
,x_dual_nationality => old_stmt_rec.dual_nationality
,x_special_needs => old_stmt_rec.special_needs
,x_country_birth => old_stmt_rec.country_birth
);
END IF; -- only update and no insert.
UPDATE igs_uc_istmnt_ints
SET error_code = g_error_code
WHERE rowid = new_ivstmt_rec.rowid;
UPDATE igs_uc_istmnt_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstmt_rec.rowid;
SELECT ivoff.rowid,
ivoff.*
FROM igs_uc_ioffer_ints ivoff
WHERE record_status = 'N';
SELECT appl.rowid,
appl.*
FROM igs_uc_app_choices appl
WHERE appl.app_no = p_appno
AND appl.choice_no = p_choiceno
AND appl.ucas_cycle = p_cycle;
ELSE /* Update the record */
BEGIN
-- call the TBH to update the record
igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
(
x_rowid => old_offer_rec.rowid
,x_app_choice_id => old_offer_rec.app_choice_id
,x_app_id => old_offer_rec.app_id
,x_app_no => old_offer_rec.app_no
,x_choice_no => old_offer_rec.choice_no
,x_last_change => old_offer_rec.last_change
,x_institute_code => old_offer_rec.institute_code
,x_ucas_program_code => old_offer_rec.ucas_program_code
,x_oss_program_code => old_offer_rec.oss_program_code
,x_oss_program_version => old_offer_rec.oss_program_version
,x_oss_attendance_type => old_offer_rec.oss_attendance_type
,x_oss_attendance_mode => old_offer_rec.oss_attendance_mode
,x_campus => old_offer_rec.campus
,x_oss_location => old_offer_rec.oss_location
,x_faculty => old_offer_rec.faculty
,x_entry_year => old_offer_rec.entry_year
,x_entry_month => old_offer_rec.entry_month
,x_point_of_entry => old_offer_rec.point_of_entry
,x_home => old_offer_rec.home
,x_deferred => old_offer_rec.deferred
,x_route_b_pref_round => old_offer_rec.route_b_pref_round
,x_route_b_actual_round => old_offer_rec.route_b_actual_round
,x_condition_category => old_offer_rec.condition_category
,x_condition_code => old_offer_rec.condition_code
,x_decision => old_offer_rec.decision
,x_decision_date => old_offer_rec.decision_date
,x_decision_number => old_offer_rec.decision_number
,x_reply => old_offer_rec.reply
,x_summary_of_cond => old_offer_rec.summary_of_cond
,x_choice_cancelled => old_offer_rec.choice_cancelled
,x_action => old_offer_rec.action
,x_substitution => old_offer_rec.substitution
,x_date_substituted => old_offer_rec.date_substituted
,x_prev_institution => old_offer_rec.prev_institution
,x_prev_course => old_offer_rec.prev_course
,x_prev_campus => old_offer_rec.prev_campus
,x_ucas_amendment => old_offer_rec.ucas_amendment
,x_withdrawal_reason => old_offer_rec.withdrawal_reason
,x_offer_course => new_ivoffer_rec.offercourse
,x_offer_campus => new_ivoffer_rec.offercampus
,x_offer_crse_length => new_ivoffer_rec.offercourselength
,x_offer_entry_month => new_ivoffer_rec. offerentrymonth
,x_offer_entry_year => new_ivoffer_rec.offerentryyear
,x_offer_entry_point => new_ivoffer_rec.offerentrypoint
,x_offer_text => new_ivoffer_rec.offertext
,x_mode => 'R'
,x_export_to_oss_status => old_offer_rec.export_to_oss_status
,x_error_code => old_offer_rec.error_code
,x_request_id => old_offer_rec.request_id
,x_batch_id => old_offer_rec.batch_id
,x_extra_round_nbr => old_offer_rec.extra_round_nbr
,x_system_code => old_offer_rec.system_code
,x_part_time => old_offer_rec.part_time
,x_interview => old_offer_rec.interview
,x_late_application => old_offer_rec.late_application
,x_modular => old_offer_rec.modular
,x_residential => old_offer_rec.residential
,x_ucas_cycle => old_offer_rec.ucas_cycle
);
END IF; -- insert / update
UPDATE igs_uc_ioffer_ints
SET error_code = g_error_code
WHERE rowid = new_ivoffer_rec.rowid;
UPDATE igs_uc_ioffer_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivoffer_rec.rowid;
SELECT ivstx.rowid,
ivstx.*
FROM igs_uc_istarx_ints ivstx
WHERE ivstx.record_status = 'N';
SELECT uast.rowid,
uast.*
FROM igs_uc_app_stats uast
WHERE uast.app_no = p_appno;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = 'ET'
and code = p_ethnic;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = 'PC'
AND code = p_socialclass;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
igs_uc_app_stats_pkg.insert_row -- IGSXI07B.pls
(
x_rowid => old_starx_rec.rowid -- would be NULL since no existing rec found
,x_app_stat_id => old_starx_rec.app_stat_id -- since this will be NULL since no existing rec found.
,x_app_id => appl_det_rec.app_id
,x_app_no => new_ivstarx_rec.appno
,x_starh_ethnic => NULL
,x_starh_social_class => NULL
,x_starh_pocc_edu_chg_dt => NULL
,x_starh_pocc => NULL
,x_starh_pocc_text => NULL
,x_starh_last_edu_inst => NULL
,x_starh_edu_leave_date => NULL
,x_starh_lea => NULL
,x_starx_ethnic => new_ivstarx_rec.ethnic
,x_starx_pocc_edu_chg => new_ivstarx_rec.pocceduchangedate
,x_starx_pocc => new_ivstarx_rec.pocc
,x_starx_pocc_text => new_ivstarx_rec.pocctext
,x_sent_to_hesa => 'N'
,x_starx_socio_economic => new_ivstarx_rec.socioeconomic
,x_starx_occ_background => new_ivstarx_rec.occbackground
,x_starh_socio_economic => NULL
,x_mode => 'R'
,x_ivstarh_dependants => NULL
,x_ivstarh_married => NULL
,x_ivstarx_religion => new_ivstarx_rec.religion
,x_ivstarx_married => new_ivstarx_rec.married
,x_ivstarx_dependants => new_ivstarx_rec.dependants
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_stats_pkg.update_row -- IGSXI07B.pls
(
x_rowid => old_starx_rec.rowid
,x_app_stat_id => old_starx_rec.app_stat_id
,x_app_id => old_starx_rec.app_id
,x_app_no => old_starx_rec.app_no
,x_starh_ethnic => old_starx_rec.starh_ethnic
,x_starh_social_class => old_starx_rec.starh_social_class
,x_starh_pocc_edu_chg_dt => old_starx_rec.starh_pocc_edu_chg_dt
,x_starh_pocc => old_starx_rec.starh_pocc
,x_starh_pocc_text => old_starx_rec.starh_pocc_text
,x_starh_last_edu_inst => old_starx_rec.starh_last_edu_inst
,x_starh_edu_leave_date => old_starx_rec.starh_edu_leave_date
,x_starh_lea => old_starx_rec.starh_lea
,x_starx_ethnic => new_ivstarx_rec.ethnic
,x_starx_pocc_edu_chg => new_ivstarx_rec.pocceduchangedate
,x_starx_pocc => new_ivstarx_rec.pocc
,x_starx_pocc_text => new_ivstarx_rec.pocctext
,x_sent_to_hesa => 'N'
,x_starx_socio_economic => new_ivstarx_rec.socioeconomic
,x_starx_occ_background => new_ivstarx_rec.occbackground
,x_starh_socio_economic => NULL
,x_mode => 'R'
,x_ivstarh_dependants => old_starx_rec.ivstarh_dependants
,x_ivstarh_married => old_starx_rec.ivstarh_married
,x_ivstarx_religion => new_ivstarx_rec.religion
,x_ivstarx_married => new_ivstarx_rec.married
,x_ivstarx_dependants => new_ivstarx_rec.dependants
);
END IF; -- insert / update
UPDATE igs_uc_istarx_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarx_rec.rowid;
UPDATE igs_uc_istarx_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarx_rec.rowid;
SELECT ivsth.rowid,
ivsth.*
FROM igs_uc_istarh_ints ivsth
WHERE ivsth.record_status = 'N';
SELECT uast.rowid,
uast.*
FROM igs_uc_app_stats uast
WHERE uast.app_no = p_appno;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = 'ET'
and code = p_ethnic;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = 'PC'
AND code = p_socialclass;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
igs_uc_app_stats_pkg.insert_row -- IGSXI07B.pls
(
x_rowid => old_starh_rec.rowid
,x_app_stat_id => old_starh_rec.app_stat_id -- can be used as this value will be NULL during insert as no rec exists.
,x_app_id => appl_det_rec.app_id
,x_app_no => new_ivstarh_rec.appno
,x_starh_ethnic => new_ivstarh_rec.ethnic
,x_starh_social_class => new_ivstarh_rec.socialclass
,x_starh_pocc_edu_chg_dt => new_ivstarh_rec.pocceduchangedate
,x_starh_pocc => new_ivstarh_rec.pocc
,x_starh_pocc_text => new_ivstarh_rec.pocctext
,x_starh_last_edu_inst => new_ivstarh_rec.lasteducation
,x_starh_edu_leave_date => new_ivstarh_rec.educationleavedate
,x_starh_lea => new_ivstarh_rec.lea
,x_starx_ethnic => NULL
,x_starx_pocc_edu_chg => NULL
,x_starx_pocc => NULL
,x_starx_pocc_text => NULL
,x_sent_to_hesa => 'N'
,x_starx_socio_economic => NULL
,x_starx_occ_background => NULL
,x_starh_socio_economic => l_socialeconomic
,x_mode => 'R'
,x_ivstarh_dependants => new_ivstarh_rec.dependants
,x_ivstarh_married => new_ivstarh_rec.married
,x_ivstarx_religion => NULL
,x_ivstarx_married => NULL
,x_ivstarx_dependants => NULL
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_stats_pkg.update_row -- IGSXI07B.pls
(
x_rowid => old_starh_rec.rowid
,x_app_stat_id => old_starh_rec.app_stat_id
,x_app_id => old_starh_rec.app_id
,x_app_no => old_starh_rec.app_no
,x_starh_ethnic => new_ivstarh_rec.ethnic
,x_starh_social_class => new_ivstarh_rec.socialclass
,x_starh_pocc_edu_chg_dt => new_ivstarh_rec.pocceduchangedate
,x_starh_pocc => new_ivstarh_rec.pocc
,x_starh_pocc_text => new_ivstarh_rec.pocctext
,x_starh_last_edu_inst => new_ivstarh_rec.lasteducation
,x_starh_edu_leave_date => new_ivstarh_rec.educationleavedate
,x_starh_lea => new_ivstarh_rec.lea
,x_starx_ethnic => old_starh_rec.starx_ethnic
,x_starx_pocc_edu_chg => old_starh_rec.starx_pocc_edu_chg
,x_starx_pocc => old_starh_rec.starx_pocc
,x_starx_pocc_text => old_starh_rec.starx_pocc_text
,x_sent_to_hesa => 'N'
,x_starx_socio_economic => old_starh_rec.starx_socio_economic
,x_starx_occ_background => old_starh_rec.starx_occ_background
,x_starh_socio_economic => l_socialeconomic
,x_mode => 'R'
,x_ivstarh_dependants => new_ivstarh_rec.dependants
,x_ivstarh_married => new_ivstarh_rec.married
,x_ivstarx_religion => old_starh_rec.ivstarx_religion
,x_ivstarx_married => old_starh_rec.ivstarx_married
,x_ivstarx_dependants => old_starh_rec.ivstarx_dependants
);
END IF; -- insert / update
UPDATE igs_uc_istarh_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarh_rec.rowid;
UPDATE igs_uc_istarh_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarh_rec.rowid;
arvsrini 04-MAR-04 Added code to use IGS_UC_ISTARZ1_INTS record to update Choice Number 9 record in IGS_UC_APP_CHOICES when the
IGS_UC_ISTARZ1_INTS.INST = Current Institution Code defined in UCAS Setup.
modified wrt UCCR008 build. Bug#3239860
anwest 29-MAY-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
anwest 02-AUG-06 Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
******************************************************************/
CURSOR new_ivstarz1_cur IS
SELECT ivstz1.rowid,
ivstz1.*
FROM igs_uc_istarz1_ints ivstz1
WHERE ivstz1.record_status = 'N';
SELECT uacl.rowid,
uacl.*
FROM igs_uc_app_clearing uacl
WHERE uacl.app_no = p_appno;
SELECT ucas, gttr, nmas, swas
FROM igs_uc_com_inst
WHERE inst = p_inst;
SELECT 'X'
FROM igs_uc_crse_dets
WHERE ucas_program_code = p_course
AND institute = p_inst
AND ucas_campus = p_campus
AND system_code = p_system;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
SELECT current_inst_code
FROM igs_uc_defaults
WHERE system_code = p_sys_code;
SELECT uacc.rowid,
uacc.*
FROM igs_uc_app_choices uacc
WHERE uacc.app_no = p_appno
AND uacc.choice_no = 9;
SELECT oss_program_code,
oss_program_version,
oss_location,
oss_attendance_mode,
oss_attendance_type
FROM igs_uc_crse_dets
WHERE System_Code = p_system
AND ucas_program_code = p_course
AND ucas_campus = p_campus
AND Institute = p_inst;
igs_uc_app_clearing_pkg.insert_row -- IGSXI04B.pls
(
x_rowid => old_starz1_rec.rowid -- it would be NULL since rec not found.
,x_clearing_app_id => old_starz1_rec.clearing_app_id -- since it would be NULL as rec not found
,x_app_id => appl_det_rec.app_id
,x_enquiry_no => NULL
,x_app_no => new_ivstarz1_rec.appno
,x_date_cef_sent => new_ivstarz1_rec.datecefsent
,x_cef_no => NVL(new_ivstarz1_rec.cefno ,999999)
,x_central_clearing => NVL(new_ivstarz1_rec.centralclearing ,'N')
,x_institution => new_ivstarz1_rec.inst
,x_course => new_ivstarz1_rec.course
,x_campus => l_campus_clr -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,x_entry_month => new_ivstarz1_rec.entrymonth
,x_entry_year => new_ivstarz1_rec.entryyear
,x_entry_point => new_ivstarz1_rec.entrypoint
,x_result => new_ivstarz1_rec.result
,x_cef_received => 'N'
,x_clearing_app_source => 'O'
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_clearing_pkg.update_row -- IGSXI04B.pls
(
x_rowid => old_starz1_rec.rowid
,x_clearing_app_id => old_starz1_rec.clearing_app_id
,x_app_id => old_starz1_rec.app_id
,x_enquiry_no => old_starz1_rec.enquiry_no
,x_app_no => old_starz1_rec.app_no
,x_date_cef_sent => new_ivstarz1_rec.datecefsent
,x_cef_no => NVL(new_ivstarz1_rec.cefno ,999999)
,x_central_clearing => NVL(new_ivstarz1_rec.centralclearing ,'N')
,x_institution => new_ivstarz1_rec.inst
,x_course => new_ivstarz1_rec.course
,x_campus => l_campus_clr -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,x_entry_month => new_ivstarz1_rec.entrymonth
,x_entry_year => new_ivstarz1_rec.entryyear
,x_entry_point => new_ivstarz1_rec.entrypoint
,x_result => new_ivstarz1_rec.result
,x_cef_received => old_starz1_rec.cef_received
,x_clearing_app_source => old_starz1_rec.clearing_app_source
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert / update
igs_uc_app_choices_pkg.update_row
(
x_rowid => uc_app_choices_rec.rowid,
x_app_choice_id => uc_app_choices_rec.app_choice_id,
x_app_id => uc_app_choices_rec.app_id,
x_app_no => uc_app_choices_rec.app_no,
x_choice_no => uc_app_choices_rec.choice_no,
x_last_change => uc_app_choices_rec.last_change,
x_institute_code => uc_app_choices_rec.institute_code,
x_ucas_program_code => NVL(new_ivstarz1_rec.course, uc_app_choices_rec.ucas_program_code),
x_oss_program_code => l_oss_program_code ,
x_oss_program_version => l_oss_program_version,
x_oss_attendance_type => l_oss_attendance_type,
x_oss_attendance_mode => l_oss_attendance_mode,
x_campus => l_campus_chc, -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
x_oss_location => l_oss_location,
x_faculty => NVL(new_ivstarz1_rec.faculty, uc_app_choices_rec.faculty),
x_entry_year => NVL(new_ivstarz1_rec.entryyear, uc_app_choices_rec.entry_year),
x_entry_month => NVL(new_ivstarz1_rec.entrymonth, uc_app_choices_rec.entry_month),
x_point_of_entry => NVL(new_ivstarz1_rec.entrypoint, uc_app_choices_rec.point_of_entry),
x_home => uc_app_choices_rec.home,
x_deferred => uc_app_choices_rec.deferred,
x_route_b_pref_round => uc_app_choices_rec.route_b_pref_round,
x_route_b_actual_round => uc_app_choices_rec.route_b_actual_round,
x_condition_category => uc_app_choices_rec.condition_category,
x_condition_code => uc_app_choices_rec.condition_code,
x_decision => l_decision,
x_decision_date => NVL(uc_app_choices_rec.decision_date,SYSDATE),
x_decision_number => uc_app_choices_rec.decision_number,
x_reply => l_reply,
x_summary_of_cond => uc_app_choices_rec.summary_of_cond,
x_choice_cancelled => uc_app_choices_rec.choice_cancelled,
x_action => uc_app_choices_rec.action,
x_substitution => uc_app_choices_rec.substitution ,
x_date_substituted => uc_app_choices_rec.date_substituted,
x_prev_institution => uc_app_choices_rec.prev_institution,
x_prev_course => uc_app_choices_rec.prev_course,
x_prev_campus => uc_app_choices_rec.prev_campus,
x_ucas_amendment => uc_app_choices_rec.ucas_amendment,
x_withdrawal_reason => uc_app_choices_rec.withdrawal_reason,
x_offer_course => uc_app_choices_rec.offer_course,
x_offer_campus => uc_app_choices_rec.offer_campus,
x_offer_crse_length => uc_app_choices_rec.offer_crse_length,
x_offer_entry_month => uc_app_choices_rec.offer_entry_month,
x_offer_entry_year => uc_app_choices_rec.offer_entry_year,
x_offer_entry_point => uc_app_choices_rec.offer_entry_point,
x_offer_text => uc_app_choices_rec.offer_text,
x_mode => 'R',
x_export_to_oss_status => 'NEW',
x_error_code => NULL,
x_request_id => uc_app_choices_rec.request_id,
x_batch_id => uc_app_choices_rec.batch_id,
x_extra_round_nbr => uc_app_choices_rec.extra_round_nbr,
x_system_code => uc_app_choices_rec.system_code,
x_part_time => uc_app_choices_rec.part_time,
x_interview => uc_app_choices_rec.interview,
x_late_application => uc_app_choices_rec.late_application,
x_modular => uc_app_choices_rec.modular,
x_residential => uc_app_choices_rec.residential,
x_ucas_cycle => uc_app_choices_rec.ucas_cycle
);
UPDATE igs_uc_istarz1_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarz1_rec.rowid;
UPDATE igs_uc_istarz1_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarz1_rec.rowid;
SELECT ivstz2.rowid,
ivstz2.*
FROM igs_uc_istarz2_ints ivstz2
WHERE ivstz2.record_status = 'N';
SELECT uaclr.rowid,
uaclr.*
FROM igs_uc_app_clr_rnd uaclr
WHERE uaclr.app_no = p_appno
AND uaclr.ucas_program_code = p_course
AND uaclr.ucas_campus = p_campus
AND uaclr.institution = p_inst
AND uaclr.system_code = p_system;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
SELECT clearing_app_id
FROM igs_uc_app_clearing
WHERE app_no = p_appno;
SELECT ucas, gttr, nmas, swas
FROM igs_uc_com_inst
WHERE inst = p_inst;
SELECT oss_program_code,
oss_program_version,
oss_attendance_type,
oss_attendance_mode,
oss_location
FROM igs_uc_crse_dets
WHERE ucas_program_code = p_course
AND institute = p_inst
AND ucas_campus = p_campus
AND system_code = p_system;
l_clearing_id igs_uc_app_clearing.clearing_app_id%TYPE; -- for holding clearing ID needed while insert
igs_uc_app_clr_rnd_pkg.insert_row -- IGSXI05B.pls
(
x_rowid => old_starz2_rec.rowid
,x_app_clear_round_id => old_starz2_rec.app_clear_round_id -- since it would be NULL if no rec found.
,x_clearing_app_id => l_clearing_id
,x_app_no => new_ivstarz2_rec.appno
,x_enquiry_no => NULL
,x_round_no => NVL(new_ivstarz2_rec.roundno ,1)
,x_institution => new_ivstarz2_rec.inst
,x_ucas_program_code => new_ivstarz2_rec.course
,x_ucas_campus => new_ivstarz2_rec.campus
,x_oss_program_code => validate_Course_rec.oss_program_code
,x_oss_program_version => validate_Course_rec.oss_program_version
,x_oss_location => validate_Course_rec.oss_location
,x_faculty => new_ivstarz2_rec.faculty
,x_accommodation_reqd => 'N'
,x_round_type => new_ivstarz2_rec.roundtype
,x_result => new_ivstarz2_rec.result
,x_mode => 'R'
,x_oss_attendance_type => validate_Course_rec.oss_attendance_mode
,x_oss_attendance_mode => validate_Course_rec.oss_attendance_type
,x_system_code => appl_det_rec.system_code
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_clr_rnd_pkg.update_row -- IGSXI05B.pls
(
x_rowid => old_starz2_rec.rowid
,x_app_clear_round_id => old_starz2_rec.app_clear_round_id
,x_clearing_app_id => old_starz2_rec.clearing_app_id
,x_app_no => old_starz2_rec.app_no
,x_enquiry_no => old_starz2_rec.enquiry_no
,x_round_no => old_starz2_rec.round_no
,x_institution => old_starz2_rec.institution
,x_ucas_program_code => old_starz2_rec.ucas_program_code
,x_ucas_campus => old_starz2_rec.ucas_campus
,x_oss_program_code => old_starz2_rec.oss_program_code
,x_oss_program_version => old_starz2_rec.oss_program_version
,x_oss_location => old_starz2_rec.oss_location
,x_faculty => new_ivstarz2_rec.faculty
,x_accommodation_reqd => old_starz2_rec.accommodation_reqd
,x_round_type => new_ivstarz2_rec.roundtype
,x_result => new_ivstarz2_rec.result
,x_mode => 'R'
,x_oss_attendance_type => old_starz2_rec.oss_attendance_type
,x_oss_attendance_mode => old_starz2_rec.oss_attendance_mode
,x_system_code => old_starz2_rec.system_code
);
END IF; -- insert / update
UPDATE igs_uc_istarz2_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarz2_rec.rowid;
UPDATE igs_uc_istarz2_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarz2_rec.rowid;
SELECT ivstz2.rowid,
ivstz2.*
FROM igs_uc_istarw_ints ivstz2
WHERE ivstz2.record_status = 'N';
SELECT uwap.rowid,
uwap.*
FROM igs_uc_wrong_app uwap
WHERE uwap.app_no = p_appno;
SELECT LKUP.MEANING
FROM IGS_LOOKUP_VALUES LKUP
WHERE LKUP.LOOKUP_TYPE = 'IGS_UC_APP_WITHDRAWN'
AND LKUP.LOOKUP_CODE = 'R';
igs_uc_wrong_app_pkg.insert_row -- IGSXI34B.pls
(
x_rowid => old_starw_rec.rowid -- since it would be NULL if old rec not found
,x_wrong_app_id => old_starw_rec.wrong_app_id -- since it would be NULL if old rec not found
,x_app_no => new_ivstarw_rec.appno
,x_miscoded => NVL(new_ivstarw_rec.miscoded, 'N')
,x_cancelled => NVL(new_ivstarw_rec.cancelled, 'N')
,x_cancel_date => new_ivstarw_rec.canceldate
,x_remark => new_ivstarw_rec.remark
,x_expunge => l_expunge_flag
,x_batch_id => NULL
,x_expunged => 'N'
,x_mode => 'R'
,x_joint_admission_ind => NVL(new_ivstarw_rec.jointadmission, 'N')
,x_choice1_lost => NVL(new_ivstarw_rec.choice1lost, 'N')
,x_choice2_lost => NVL(new_ivstarw_rec.choice2lost, 'N')
,x_choice3_lost => NVL(new_ivstarw_rec.choice3lost, 'N')
,x_choice4_lost => NVL(new_ivstarw_rec.choice4lost, 'N')
,x_choice5_lost => NVL(new_ivstarw_rec.choice5lost, 'N')
,x_choice6_lost => NVL(new_ivstarw_rec.choice6lost, 'N')
,x_choice7_lost => NVL(new_ivstarw_rec.choice7lost, 'N')
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_wrong_app_pkg.update_row -- IGSXI34B.pls
(
x_rowid => old_starw_rec.rowid
,x_wrong_app_id => old_starw_rec.wrong_app_id
,x_app_no => old_starw_rec.app_no
,x_miscoded => NVL(new_ivstarw_rec.miscoded, 'N')
,x_cancelled => NVL(new_ivstarw_rec.cancelled, 'N')
,x_cancel_date => new_ivstarw_rec.canceldate
,x_remark => new_ivstarw_rec.remark
,x_expunge => l_expunge_flag
,x_batch_id => old_starw_rec.batch_id
,x_expunged => 'N'
,x_mode => 'R'
,x_joint_admission_ind => NVL(new_ivstarw_rec.jointadmission, 'N')
,x_choice1_lost => NVL(new_ivstarw_rec.choice1lost, 'N')
,x_choice2_lost => NVL(new_ivstarw_rec.choice2lost, 'N')
,x_choice3_lost => NVL(new_ivstarw_rec.choice3lost, 'N')
,x_choice4_lost => NVL(new_ivstarw_rec.choice4lost, 'N')
,x_choice5_lost => NVL(new_ivstarw_rec.choice5lost, 'N')
,x_choice6_lost => NVL(new_ivstarw_rec.choice6lost, 'N')
,x_choice7_lost => NVL(new_ivstarw_rec.choice7lost, 'N')
);
END IF; -- insert / update
UPDATE igs_uc_istarw_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarw_rec.rowid;
UPDATE igs_uc_istarw_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivstarw_rec.rowid;
SELECT irefr.rowid,
irefr.*
FROM igs_uc_irefrnc_ints irefr
WHERE irefr.record_status = 'N';
SELECT uapref.rowid,
uapref.*
FROM igs_uc_app_referees uapref
WHERE uapref.app_no = p_appno
AND uapref.referee_name = p_referee;
SELECT uapref.statement
FROM igs_uc_app_referees uapref
WHERE rowid = cp_rowid
FOR UPDATE NOWAIT;
igs_uc_app_referees_pkg.insert_row -- IGSXI49B.pls
(
x_rowid => old_refer_rec.rowid
,x_app_no => new_ireference_rec.appno
,x_referee_name => new_ireference_rec.refereename
,x_referee_post => new_ireference_rec.refereepost
,x_estab_name => new_ireference_rec.estabname
,x_address1 => new_ireference_rec.address1
,x_address2 => new_ireference_rec.address2
,x_address3 => new_ireference_rec.address3
,x_address4 => new_ireference_rec.address4
,x_telephone => new_ireference_rec.telephone
,x_fax => new_ireference_rec.fax
,x_email => new_ireference_rec.email
,x_statement => EMPTY_CLOB()
,x_predicted_grades => new_ireference_rec.predictedgrades
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_referees_pkg.update_row -- IGSXI49B.pls
(
x_rowid => old_refer_rec.rowid
,x_app_no => old_refer_rec.app_no
,x_referee_name => old_refer_rec.referee_name
,x_referee_post => new_ireference_rec.refereepost
,x_estab_name => new_ireference_rec.estabname
,x_address1 => new_ireference_rec.address1
,x_address2 => new_ireference_rec.address2
,x_address3 => new_ireference_rec.address3
,x_address4 => new_ireference_rec.address4
,x_telephone => new_ireference_rec.telephone
,x_fax => new_ireference_rec.fax
,x_email => new_ireference_rec.email
,x_statement => EMPTY_CLOB()
,x_predicted_grades => new_ireference_rec.predictedgrades
,x_mode => 'R'
);
END IF; -- insert / update
UPDATE igs_uc_irefrnc_ints
SET error_code = g_error_code
WHERE rowid = new_ireference_rec.rowid;
UPDATE igs_uc_irefrnc_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ireference_rec.rowid;
SELECT ivfq.rowid,
ivfq.*
FROM igs_uc_ifrmqul_ints ivfq
WHERE ivfq.record_status = 'N';
SELECT ufq.rowid,
ufq.*
FROM igs_uc_form_quals ufq
WHERE ufq.app_no = p_appno
AND ufq.qual_type = p_qualtype
AND ufq.title = p_title;
igs_uc_form_quals_pkg.insert_row -- IGSXI51B.pls
(
x_rowid => old_frmqual_rec.rowid
,x_app_no => new_ivfrmqual_rec.appno
,x_qual_id => new_ivfrmqual_rec.qualid
,x_qual_type => new_ivfrmqual_rec.qualtype
,x_award_body => new_ivfrmqual_rec.awardbody
,x_title => new_ivfrmqual_rec.title
,x_grade => new_ivfrmqual_rec.grade
,x_qual_date => new_ivfrmqual_rec.qualdate
,x_mode => 'R'
);
ELSE -- update
BEGIN
igs_uc_form_quals_pkg.update_row -- IGSXI51B.pls
(
x_rowid => old_frmqual_rec.rowid
,x_app_no => old_frmqual_rec.app_no
,x_qual_id => new_ivfrmqual_rec.qualid
,x_qual_type => old_frmqual_rec.qual_type
,x_award_body => new_ivfrmqual_rec.awardbody
,x_title => old_frmqual_rec.title
,x_grade => new_ivfrmqual_rec.grade
,x_qual_date => new_ivfrmqual_rec.qualdate
,x_mode => 'R'
);
END IF; -- insert / update
UPDATE igs_uc_ifrmqul_ints
SET error_code = g_error_code
WHERE rowid = new_ivfrmqual_rec.rowid;
UPDATE igs_uc_ifrmqul_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_ivfrmqual_rec.rowid;
SELECT ivpqr.rowid,
ivpqr.*
FROM igs_uc_istrpqr_ints ivpqr
WHERE ivpqr.record_status = 'N'
AND ivpqr.appno = cp_appno ;
SELECT uapr.rowid,
uapr.*
FROM igs_uc_app_results uapr
WHERE uapr.app_no = p_appno
AND uapr.subject_id = p_sub_id;
SELECT app_id,
system_code
FROM igs_uc_applicants
WHERE app_no = p_appno;
SELECT DISTINCT appno
FROM igs_uc_istrpqr_ints
WHERE record_status = 'N';
SELECT rowid
FROM igs_uc_app_results
WHERE app_no = cp_appno;
SELECT rowid
,subject_id
,year
,sitting
,awarding_body
,external_ref
,exam_level
,title
,subject_code
,imported
FROM igs_uc_com_ebl_subj
WHERE subject_id = p_subject;
SELECT exam_level ,
awarding_body,
conv_ebl_format
FROM igs_uc_map_ebl_qual
WHERE exam_board_code = cp_exam_board_code
AND ebl_format = cp_ebl_code
AND closed_ind = 'N' ;
SELECT ebl.subject_id
FROM Igs_uc_com_ebl_subj ebl , igs_uc_ref_subj ref
WHERE ebl.subject_code = ref.subj_code
AND ebl.year = cp_year
AND ebl.sitting = cp_sitting
AND ebl.awarding_body = cp_awarding_body
AND ebl.exam_level = cp_exam_level
AND ref.ebl_subj = cp_ebl_subject
ORDER BY ebl.external_ref DESC ;
UPDATE igs_uc_istrpqr_ints
SET error_code = NULL
WHERE record_status = 'N' AND appno = new_appl_rec.appno ;
igs_uc_app_results_pkg.delete_row(get_appno_rec.rowid);
igs_uc_app_results_pkg.insert_row (
x_rowid => old_starpqr_rec.rowid
,x_app_result_id => old_starpqr_rec.app_result_id -- since it would also be NULL when record does not exist.
,x_app_id => appl_det_rec.app_id
,x_app_no => new_ivstarpqr_rec.appno
,x_enquiry_no => NULL
,x_exam_level => subject_rec.exam_level
,x_year => subject_rec.year
,x_sitting => subject_rec.sitting
,x_award_body => subject_rec.awarding_body
,x_subject_id => new_ivstarpqr_rec.subjectid
,x_predicted_result => NULL
,x_result_in_offer => NULL
,x_ebl_result => new_ivstarpqr_rec.eblresult
,x_ebl_amended_result => new_ivstarpqr_rec.eblamended
,x_claimed_result => new_ivstarpqr_rec.claimedresult
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- call the TBH to update the record
igs_uc_app_results_pkg.update_row (
x_rowid => old_starpqr_rec.rowid
,x_app_result_id => old_starpqr_rec.app_result_id
,x_app_id => old_starpqr_rec.app_id
,x_app_no => old_starpqr_rec.app_no
,x_enquiry_no => old_starpqr_rec.enquiry_no
,x_exam_level => old_starpqr_rec.exam_level
,x_year => old_starpqr_rec.year
,x_sitting => old_starpqr_rec.sitting
,x_award_body => old_starpqr_rec.award_body
,x_subject_id => old_starpqr_rec.subject_id
,x_predicted_result => old_starpqr_rec.predicted_result
,x_result_in_offer => old_starpqr_rec.result_in_offer
,x_ebl_result => new_ivstarpqr_rec.eblresult
,x_ebl_amended_result => new_ivstarpqr_rec.eblamended
,x_claimed_result => new_ivstarpqr_rec.claimedresult
,x_imported => old_starpqr_rec.imported
,x_mode => 'R'
);
END IF; -- insert / update
UPDATE igs_uc_istrpqr_ints
SET error_code = g_error_code
WHERE rowid = new_ivstarpqr_rec.rowid ;
igs_uc_app_results_pkg.delete_row(get_appno_rec.rowid);
UPDATE igs_uc_istrpqr_ints SET error_code = '2001'
WHERE record_status = 'N' AND appno = new_appl_rec.appno AND error_code IS NULL ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'L' , error_code = NULL
WHERE record_status = 'N' AND appno = new_appl_rec.appno ;