[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER := g_created_by;
g_last_update_login NUMBER := fnd_global.login_id;
update the UCAS Interface Tables with Sent To OSS Flag
6. Launching Export UCAS Applicant to OSS Error Report, if any errors found
Known limitations,enhancements,remarks:
CHANGE HISTORY:
WHO WHEN WHAT
AYEDUBAT 15-JUL-03 Changed the cursor,cur_ninumber_alt_type to remove the condition,
ni_number_alt_pers_type IS NULL as part of Multiple Cycles Enh Bug#2669208
AYEDUBAT 16-JUL-2003 Modified to correct the the title value of UCAS with the pre-adjucent_name in OSS
Added ORDER BY Clause while processing the Applications for Bug#2669208
DSRIDHAR 25-SEP-2003 Bug No. 2980137. Added a local variable to obtain the return code from
pop_res_dtls_int.
RGANGARA 10-APR-2004 bug# 3553352. Added validation to check Whether Decision Maker ID has been set for each
of the Systems which have atleast one Applicant record for processing.
ANWEST 30-SEP-2004 Bug# 3642740 Added 2 new cursors and 2 FOR
LOOPS to review and process all 'I' records
ANWEST 25-NOV-2004 Modified for UCFD040 - Bug# 4015492 Added 2 new cursors,
2 new data types, 6 new local variables, 1 new procedure,
1 more mandatory check and code logic associated with
person residency term
ANWEST 21-JUL-2005 Bug# 4465994 Corrected app_no parameter
ANWEST 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
JCHAKRAB 20-Feb-2006 Modified for bugs 3691220, 3691210, 3691176 - replaced existing cursors
cur_exp_applicant_dtls, cur_app_address_dtls, cur_app_name_dtls with REF CURSORS
to improve performance
***************************************************************** */
-- Fetch the Source Type ID for UCAS Person from Person Source Types Table
CURSOR cur_pe_src_types IS
SELECT source_type_id
FROM IGS_PE_SRC_TYPES_ALL
WHERE source_type = 'UCAS PER'
AND NVL(closed_ind,'N') = 'N';
SELECT name,ni_number_alt_pers_type
FROM IGS_UC_DEFAULTS
WHERE system_code = 'N';
SELECT DISTINCT system_code
FROM igs_uc_applicants ucap
WHERE ucap.app_no = NVL(cp_app_no, ucap.app_no)
AND NVL(ucap.sent_to_oss,'N') = 'N';
SELECT name, decision_make_id
FROM igs_uc_defaults
WHERE system_code = cp_system_code;
SELECT person_id, person_number,title,
last_name surname, first_name given_names, gender sex,
birth_date, pre_name_adjunct
FROM IGS_PE_PERSON_BASE_V
WHERE person_id = cp_person_id;
SELECT ucap.app_no, ucap.oss_person_id, domicile_apr, ucap.country_birth
FROM IGS_UC_APPLICANTS ucap
WHERE ucap.app_no = cp_app_no;
SELECT interface_id
FROM IGS_AD_INTERFACE_ALL
WHERE batch_id = cp_batch_id
AND person_id = cp_person_id;
SELECT ucap.rowid,ucap.*
FROM IGS_UC_APPLICANTS ucap
WHERE ucap.app_no = cp_app_no;
SELECT ucad.rowid,ucad.*
FROM IGS_UC_APP_ADDRESES ucad
WHERE ucad.app_no = cp_app_no;
SELECT ucn.rowid,ucn.*
FROM IGS_UC_APP_NAMES ucn
WHERE ucn.app_no = cp_app_no;
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT ucap.rowid,ucap.*
FROM IGS_UC_APPLICANTS ucap
WHERE ucap.ad_batch_id = cp_batch_id;
SELECT ucad.rowid,ucad.*
FROM IGS_UC_APP_ADDRESES ucad
WHERE ucad.ad_batch_id = cp_batch_id;
SELECT 'X'
FROM IGS_AD_INTERFACE_ALL
WHERE batch_id = cp_batch_id
AND interface_id = cp_interface_id;
SELECT ucapi.rowid, ucapi.*
FROM IGS_UC_APPLICANTS ucapi
WHERE ucapi.sent_to_oss = 'I';
SELECT ucadi.rowid, ucadi.*
FROM IGS_UC_APP_ADDRESES ucadi
WHERE ucadi.sent_to_oss_flag = 'I';
/* Cursors used to retrieve the Term Calender for insertion in the Residency
Interface Table. */
-- anwest UCFD040 Bug# 4015492 New cursor to store maximum current cycle
CURSOR cur_get_current_cycle IS
SELECT max(current_cycle)
FROM IGS_UC_DEFAULTS;
SELECT DISTINCT ucsyscal.system_code,
ucsyscal.entry_year,
cainstall.cal_type,
cainstall.sequence_number,
cainstall.start_dt
FROM IGS_CA_INST_ALL cainstall,
IGS_CA_INST_REL cainstrel,
IGS_CA_TYPE catype,
IGS_CA_STAT castat,
IGS_UC_SYS_CALNDRS ucsyscal
WHERE castat.s_cal_status = 'ACTIVE' and
catype.s_cal_cat = 'LOAD' and
cainstall.cal_status = castat.s_cal_status and
cainstall.cal_type = catype.cal_type and
cainstall.cal_type = cainstrel.sub_cal_type and
cainstrel.sub_ci_sequence_number = cainstall.sequence_number and
cainstrel.sup_cal_type = ucsyscal.aca_cal_type and
cainstrel.sup_ci_sequence_number = ucsyscal.aca_cal_seq_no and
ucsyscal.entry_year <= cp_entry_year + 1 and
ucsyscal.entry_year >= cp_entry_year - 1
ORDER BY ucsyscal.system_code, cainstall.start_dt;
SELECT system_code, entry_year
FROM IGS_UC_APP_CHOICES
WHERE app_no = cp_app_no
ORDER BY entry_year;
SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
COUNTRY_BIRTH
FROM IGS_UC_APPLICANTS UCAP
WHERE UCAP.SENT_TO_OSS = 'N'
ORDER BY UCAP.APP_NO;
SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
COUNTRY_BIRTH
FROM IGS_UC_APPLICANTS UCAP
WHERE UCAP.APP_NO = p_app_no AND
UCAP.SENT_TO_OSS = 'N'
ORDER BY UCAP.APP_NO;
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 (
IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
fnd_message.get_string('IGS','IGS_UC_IMP_FROM_UCAS_BATCH_ID'),
g_created_by,
SYSDATE,
g_last_updated_by,
SYSDATE,
g_last_update_login,
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) )
RETURNING batch_id INTO l_ad_batch_id;
INSERT INTO igs_ad_interface_all (
interface_id,
batch_id,
source_type_id,
person_id,
match_ind,
surname,
given_names,
sex,
birth_dt,
pre_name_adjunct,
status,
record_status,
pref_alternate_id,
birth_country,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
IGS_AD_INTERFACE_S.NEXTVAL,
l_ad_batch_id,
l_src_type_id,
cur_uc_person_dtls_rec.person_id,
'15',
cur_uc_person_dtls_rec.surname,
cur_uc_person_dtls_rec.given_names,
cur_uc_person_dtls_rec.sex,
cur_uc_person_dtls_rec.birth_date,
cur_uc_person_dtls_rec.pre_name_adjunct,
'2',
'2',
NULL,
l_oss_country_code,
g_created_by,
SYSDATE,
g_last_updated_by,
SYSDATE,
g_last_update_login )
RETURNING interface_id INTO l_ad_interface_id ;
igs_uc_applicants_pkg.update_row (
x_rowid => cur_ucas_app_rec.rowid
,x_app_id => cur_ucas_app_rec.app_id
,x_app_no => cur_ucas_app_rec.app_no
,x_check_digit => cur_ucas_app_rec.check_digit
,x_personal_id => cur_ucas_app_rec.personal_id
,x_enquiry_no => cur_ucas_app_rec.enquiry_no
,x_oss_person_id => cur_ucas_app_rec.oss_person_id
,x_application_source => cur_ucas_app_rec.application_source
,x_name_change_date => cur_ucas_app_rec.name_change_date
,x_student_support => cur_ucas_app_rec.student_support
,x_address_area => cur_ucas_app_rec.address_area
,x_application_date => cur_ucas_app_rec.application_date
,x_application_sent_date => cur_ucas_app_rec.application_sent_date
,x_application_sent_run => cur_ucas_app_rec.application_sent_run
,x_lea_code => cur_ucas_app_rec.lea_code
,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
,x_fee_text => cur_ucas_app_rec.fee_text
,x_domicile_apr => cur_ucas_app_rec.domicile_apr
,x_code_changed_date => cur_ucas_app_rec.code_changed_date
,x_school => cur_ucas_app_rec.school
,x_withdrawn => cur_ucas_app_rec.withdrawn
,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
,x_route_b => cur_ucas_app_rec.route_b
,x_exam_change_date => cur_ucas_app_rec.exam_change_date
,x_a_levels => cur_ucas_app_rec.a_levels
,x_as_levels => cur_ucas_app_rec.as_levels
,x_highers => cur_ucas_app_rec.highers
,x_csys => cur_ucas_app_rec.csys
,x_winter => cur_ucas_app_rec.winter
,x_previous => cur_ucas_app_rec.previous
,x_gnvq => cur_ucas_app_rec.gnvq
,x_btec => cur_ucas_app_rec.btec
,x_ilc => cur_ucas_app_rec.ilc
,x_ailc => cur_ucas_app_rec.ailc
,x_ib => cur_ucas_app_rec.ib
,x_manual => cur_ucas_app_rec.manual
,x_reg_num => cur_ucas_app_rec.reg_num
,x_oeq => cur_ucas_app_rec.oeq
,x_eas => cur_ucas_app_rec.eas
,x_roa => cur_ucas_app_rec.roa
,x_status => cur_ucas_app_rec.status
,x_firm_now => cur_ucas_app_rec.firm_now
,x_firm_reply => cur_ucas_app_rec.firm_reply
,x_insurance_reply => cur_ucas_app_rec.insurance_reply
,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
,x_residential_category => cur_ucas_app_rec.residential_category
,x_personal_statement => cur_ucas_app_rec.personal_statement
,x_match_prev => cur_ucas_app_rec.match_prev
,x_match_prev_date => cur_ucas_app_rec.match_prev_date
,x_match_winter => cur_ucas_app_rec.match_winter
,x_match_summer => cur_ucas_app_rec.match_summer
,x_gnvq_date => cur_ucas_app_rec.gnvq_date
,x_ib_date => cur_ucas_app_rec.ib_date
,x_ilc_date => cur_ucas_app_rec.ilc_date
,x_ailc_date => cur_ucas_app_rec.ailc_date
,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
,x_prev_surname => cur_ucas_app_rec.prev_surname
,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
,x_sent_to_oss => cur_ucas_app_rec.sent_to_oss
,x_batch_identifier => cur_ucas_app_rec.batch_identifier
,x_mode => 'R'
,x_gce => cur_ucas_app_rec.gce
,x_vce => cur_ucas_app_rec.vce
,x_sqa => cur_ucas_app_rec.sqa
,x_previousas => cur_ucas_app_rec.previousas
,x_keyskills => cur_ucas_app_rec.keyskills
,x_vocational => cur_ucas_app_rec.vocational
,x_scn => cur_ucas_app_rec.scn
,x_prevoeq => cur_ucas_app_rec.prevoeq
,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
,x_extra_status => cur_ucas_app_rec.extra_status
,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
,x_cef_no => cur_ucas_app_rec.cef_no
,x_system_code => cur_ucas_app_rec.system_code
,x_gcse_eng => cur_ucas_app_rec.gcse_eng
,x_gcse_math => cur_ucas_app_rec.gcse_math
,x_degree_subject => cur_ucas_app_rec.degree_subject
,x_degree_status => cur_ucas_app_rec.degree_status
,x_degree_class => cur_ucas_app_rec.degree_class
,x_gcse_sci => cur_ucas_app_rec.gcse_sci
,x_welshspeaker => cur_ucas_app_rec.welshspeaker
,x_ni_number => cur_ucas_app_rec.ni_number
,x_earliest_start => cur_ucas_app_rec.earliest_start
,x_near_inst => cur_ucas_app_rec.near_inst
,x_pref_reg => cur_ucas_app_rec.pref_reg
,x_qual_eng => cur_ucas_app_rec.qual_eng
,x_qual_math => cur_ucas_app_rec.qual_math
,x_qual_sci => cur_ucas_app_rec.qual_sci
,x_main_qual => cur_ucas_app_rec.main_qual
,x_qual_5 => cur_ucas_app_rec.qual_5
,x_future_serv => cur_ucas_app_rec.future_serv
,x_future_set => cur_ucas_app_rec.future_set
,x_present_serv => cur_ucas_app_rec.present_serv
,x_present_set => cur_ucas_app_rec.present_set
,x_curr_employment => cur_ucas_app_rec.curr_employment
,x_edu_qualification => cur_ucas_app_rec.edu_qualification
,x_ad_batch_id => l_ad_batch_id
,x_ad_interface_id => l_ad_interface_id
,x_nationality => cur_ucas_app_rec.nationality
,x_dual_nationality => cur_ucas_app_rec.dual_nationality
,x_special_needs => cur_ucas_app_rec.special_needs
,x_country_birth => cur_ucas_app_rec.country_birth );
SELECT *
FROM IGS_UC_APP_ADDRESES
WHERE SENT_TO_OSS_FLAG = 'N'
ORDER BY APP_NO;
SELECT *
FROM IGS_UC_APP_ADDRESES
WHERE APP_NO = p_app_no AND
SENT_TO_OSS_FLAG = 'N'
ORDER BY APP_NO;
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 (
IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
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)
)
RETURNING batch_id INTO l_ad_batch_id;
INSERT INTO igs_ad_interface_all (
interface_id,
batch_id,
source_type_id,
person_id,
match_ind,
surname,
given_names,
sex,
birth_dt,
pre_name_adjunct,
status,
record_status,
pref_alternate_id,
birth_country,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
IGS_AD_INTERFACE_S.NEXTVAL,
l_ad_batch_id,
l_src_type_id,
cur_uc_app_dtls_rec.oss_person_id,
'15',
cur_uc_person_dtls_rec.surname,
cur_uc_person_dtls_rec.given_names,
cur_uc_person_dtls_rec.sex,
cur_uc_person_dtls_rec.birth_date,
cur_uc_person_dtls_rec.pre_name_adjunct,
'2',
'2',
NULL,
l_oss_country_code,
g_created_by,
SYSDATE,
g_last_updated_by,
SYSDATE,
g_last_update_login )
RETURNING interface_id INTO l_ad_interface_id ;
/* Update the IGS_UC_APP_ADDRESES Table with AD_BATCH_ID => Admissions Batch ID and
AD_INTERFACE_ID => Admissions Interface ID */
cur_app_address_rec := NULL;
igs_uc_app_addreses_pkg.update_row (
x_rowid => cur_app_address_rec.rowid,
x_app_no => cur_app_address_rec.app_no,
x_address_area => cur_app_address_rec.address_area,
x_address1 => cur_app_address_rec.address1,
x_address2 => cur_app_address_rec.address2,
x_address3 => cur_app_address_rec.address3,
x_address4 => cur_app_address_rec.address4,
x_post_code => cur_app_address_rec.post_code,
x_mail_sort => cur_app_address_rec.mail_sort,
x_telephone => cur_app_address_rec.telephone,
x_fax => cur_app_address_rec.fax,
x_email => cur_app_address_rec.email,
x_home_address1 => cur_app_address_rec.home_address1,
x_home_address2 => cur_app_address_rec.home_address2,
x_home_address3 => cur_app_address_rec.home_address3,
x_home_address4 => cur_app_address_rec.home_address4,
x_home_postcode => cur_app_address_rec.home_postcode,
x_home_phone => cur_app_address_rec.home_phone,
x_home_fax => cur_app_address_rec.home_fax,
x_home_email => cur_app_address_rec.home_email,
x_sent_to_oss_flag => cur_app_address_rec.sent_to_oss_flag,
x_mobile => cur_app_address_rec.mobile,
x_country_code => cur_app_address_rec.country_code,
x_home_country_code => cur_app_address_rec.home_country_code,
x_ad_batch_id => l_ad_batch_id,
x_ad_interface_id => l_ad_interface_id,
x_mode => 'R' );
SELECT APP_NO,TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, SEX
FROM IGS_UC_APP_NAMES
WHERE SENT_TO_OSS_FLAG = 'N'
ORDER BY APP_NO;
SELECT APP_NO,TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, SEX
FROM IGS_UC_APP_NAMES
WHERE APP_NO = p_app_no AND
SENT_TO_OSS_FLAG = 'N'
ORDER BY APP_NO;
/* Update the IGS_UC_APP_NAMES Table with sent_to_oss_flag to 'Y' */
cur_app_name_rec := NULL;
igs_uc_app_names_pkg.update_row(
x_rowid => cur_app_name_rec.rowid
,x_app_no => cur_app_name_rec.app_no
,x_check_digit => cur_app_name_rec.check_digit
,x_name_change_date => cur_app_name_rec.name_change_date
,x_title => cur_app_name_rec.title
,x_fore_names => cur_app_name_rec.fore_names
,x_surname => cur_app_name_rec.surname
,x_birth_date => cur_app_name_rec.birth_date
,x_sex => cur_app_name_rec.sex
,x_sent_to_oss_flag => 'Y'
,x_mode => 'R' );
/******* Process the Interface Records and update the UCAS Interface Tables ******/
IF l_adm_imp_status = TRUE THEN
-- Processing the records in the IGS_UC_APPLICANTS Table
FOR cur_proc_applicant_rec IN cur_proc_applicants(l_ad_batch_id) LOOP
-- Check whether the record exist in the IGS_AD_INTERFACE Table for the
-- Admission Batch ID and Interface ID
OPEN cur_ad_interface_exist( cur_proc_applicant_rec.ad_batch_id, cur_proc_applicant_rec.ad_interface_id);
igs_uc_applicants_pkg.update_row (
x_rowid => cur_ucas_app_rec.rowid
,x_app_id => cur_ucas_app_rec.app_id
,x_app_no => cur_ucas_app_rec.app_no
,x_check_digit => cur_ucas_app_rec.check_digit
,x_personal_id => cur_ucas_app_rec.personal_id
,x_enquiry_no => cur_ucas_app_rec.enquiry_no
,x_oss_person_id => cur_ucas_app_rec.oss_person_id
,x_application_source => cur_ucas_app_rec.application_source
,x_name_change_date => cur_ucas_app_rec.name_change_date
,x_student_support => cur_ucas_app_rec.student_support
,x_address_area => cur_ucas_app_rec.address_area
,x_application_date => cur_ucas_app_rec.application_date
,x_application_sent_date => cur_ucas_app_rec.application_sent_date
,x_application_sent_run => cur_ucas_app_rec.application_sent_run
,x_lea_code => cur_ucas_app_rec.lea_code
,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
,x_fee_text => cur_ucas_app_rec.fee_text
,x_domicile_apr => cur_ucas_app_rec.domicile_apr
,x_code_changed_date => cur_ucas_app_rec.code_changed_date
,x_school => cur_ucas_app_rec.school
,x_withdrawn => cur_ucas_app_rec.withdrawn
,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
,x_route_b => cur_ucas_app_rec.route_b
,x_exam_change_date => cur_ucas_app_rec.exam_change_date
,x_a_levels => cur_ucas_app_rec.a_levels
,x_as_levels => cur_ucas_app_rec.as_levels
,x_highers => cur_ucas_app_rec.highers
,x_csys => cur_ucas_app_rec.csys
,x_winter => cur_ucas_app_rec.winter
,x_previous => cur_ucas_app_rec.previous
,x_gnvq => cur_ucas_app_rec.gnvq
,x_btec => cur_ucas_app_rec.btec
,x_ilc => cur_ucas_app_rec.ilc
,x_ailc => cur_ucas_app_rec.ailc
,x_ib => cur_ucas_app_rec.ib
,x_manual => cur_ucas_app_rec.manual
,x_reg_num => cur_ucas_app_rec.reg_num
,x_oeq => cur_ucas_app_rec.oeq
,x_eas => cur_ucas_app_rec.eas
,x_roa => cur_ucas_app_rec.roa
,x_status => cur_ucas_app_rec.status
,x_firm_now => cur_ucas_app_rec.firm_now
,x_firm_reply => cur_ucas_app_rec.firm_reply
,x_insurance_reply => cur_ucas_app_rec.insurance_reply
,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
,x_residential_category => cur_ucas_app_rec.residential_category
,x_personal_statement => cur_ucas_app_rec.personal_statement
,x_match_prev => cur_ucas_app_rec.match_prev
,x_match_prev_date => cur_ucas_app_rec.match_prev_date
,x_match_winter => cur_ucas_app_rec.match_winter
,x_match_summer => cur_ucas_app_rec.match_summer
,x_gnvq_date => cur_ucas_app_rec.gnvq_date
,x_ib_date => cur_ucas_app_rec.ib_date
,x_ilc_date => cur_ucas_app_rec.ilc_date
,x_ailc_date => cur_ucas_app_rec.ailc_date
,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
,x_prev_surname => cur_ucas_app_rec.prev_surname
,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
,x_sent_to_oss => l_sent_to_oss_flag -- updated column
,x_batch_identifier => cur_ucas_app_rec.batch_identifier
,x_mode => 'R'
,x_gce => cur_ucas_app_rec.gce
,x_vce => cur_ucas_app_rec.vce
,x_sqa => cur_ucas_app_rec.sqa
,x_previousas => cur_ucas_app_rec.previousas
,x_keyskills => cur_ucas_app_rec.keyskills
,x_vocational => cur_ucas_app_rec.vocational
,x_scn => cur_ucas_app_rec.scn
,x_prevoeq => cur_ucas_app_rec.prevoeq
,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
,x_extra_status => cur_ucas_app_rec.extra_status
,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
,x_cef_no => cur_ucas_app_rec.cef_no
,x_system_code => cur_ucas_app_rec.system_code
,x_gcse_eng => cur_ucas_app_rec.gcse_eng
,x_gcse_math => cur_ucas_app_rec.gcse_math
,x_degree_subject => cur_ucas_app_rec.degree_subject
,x_degree_status => cur_ucas_app_rec.degree_status
,x_degree_class => cur_ucas_app_rec.degree_class
,x_gcse_sci => cur_ucas_app_rec.gcse_sci
,x_welshspeaker => cur_ucas_app_rec.welshspeaker
,x_ni_number => cur_ucas_app_rec.ni_number
,x_earliest_start => cur_ucas_app_rec.earliest_start
,x_near_inst => cur_ucas_app_rec.near_inst
,x_pref_reg => cur_ucas_app_rec.pref_reg
,x_qual_eng => cur_ucas_app_rec.qual_eng
,x_qual_math => cur_ucas_app_rec.qual_math
,x_qual_sci => cur_ucas_app_rec.qual_sci
,x_main_qual => cur_ucas_app_rec.main_qual
,x_qual_5 => cur_ucas_app_rec.qual_5
,x_future_serv => cur_ucas_app_rec.future_serv
,x_future_set => cur_ucas_app_rec.future_set
,x_present_serv => cur_ucas_app_rec.present_serv
,x_present_set => cur_ucas_app_rec.present_set
,x_curr_employment => cur_ucas_app_rec.curr_employment
,x_edu_qualification => cur_ucas_app_rec.edu_qualification
,x_ad_batch_id => cur_ucas_app_rec.ad_batch_id
,x_ad_interface_id => cur_ucas_app_rec.ad_interface_id
,x_nationality => cur_ucas_app_rec.nationality
,x_dual_nationality => cur_ucas_app_rec.dual_nationality
,x_special_needs => cur_ucas_app_rec.special_needs
,x_country_birth => cur_ucas_app_rec.country_birth );
igs_uc_app_addreses_pkg.update_row (
x_rowid => cur_app_address_rec.rowid,
x_app_no => cur_app_address_rec.app_no,
x_address_area => cur_app_address_rec.address_area,
x_address1 => cur_app_address_rec.address1,
x_address2 => cur_app_address_rec.address2,
x_address3 => cur_app_address_rec.address3,
x_address4 => cur_app_address_rec.address4,
x_post_code => cur_app_address_rec.post_code,
x_mail_sort => cur_app_address_rec.mail_sort,
x_telephone => cur_app_address_rec.telephone,
x_fax => cur_app_address_rec.fax,
x_email => cur_app_address_rec.email,
x_home_address1 => cur_app_address_rec.home_address1,
x_home_address2 => cur_app_address_rec.home_address2,
x_home_address3 => cur_app_address_rec.home_address3,
x_home_address4 => cur_app_address_rec.home_address4,
x_home_postcode => cur_app_address_rec.home_postcode,
x_home_phone => cur_app_address_rec.home_phone,
x_home_fax => cur_app_address_rec.home_fax,
x_home_email => cur_app_address_rec.home_email,
x_sent_to_oss_flag => l_sent_to_oss_flag,
x_mobile => cur_app_address_rec.mobile,
x_country_code => cur_app_address_rec.country_code,
x_home_country_code => cur_app_address_rec.home_country_code,
x_ad_batch_id => cur_app_address_rec.ad_batch_id,
x_ad_interface_id => cur_app_address_rec.ad_interface_id,
x_mode => 'R' );
igs_uc_applicants_pkg.update_row (
x_rowid => cur_ucas_app_rec.rowid
,x_app_id => cur_ucas_app_rec.app_id
,x_app_no => cur_ucas_app_rec.app_no
,x_check_digit => cur_ucas_app_rec.check_digit
,x_personal_id => cur_ucas_app_rec.personal_id
,x_enquiry_no => cur_ucas_app_rec.enquiry_no
,x_oss_person_id => cur_ucas_app_rec.oss_person_id
,x_application_source => cur_ucas_app_rec.application_source
,x_name_change_date => cur_ucas_app_rec.name_change_date
,x_student_support => cur_ucas_app_rec.student_support
,x_address_area => cur_ucas_app_rec.address_area
,x_application_date => cur_ucas_app_rec.application_date
,x_application_sent_date => cur_ucas_app_rec.application_sent_date
,x_application_sent_run => cur_ucas_app_rec.application_sent_run
,x_lea_code => cur_ucas_app_rec.lea_code
,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
,x_fee_text => cur_ucas_app_rec.fee_text
,x_domicile_apr => cur_ucas_app_rec.domicile_apr
,x_code_changed_date => cur_ucas_app_rec.code_changed_date
,x_school => cur_ucas_app_rec.school
,x_withdrawn => cur_ucas_app_rec.withdrawn
,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
,x_route_b => cur_ucas_app_rec.route_b
,x_exam_change_date => cur_ucas_app_rec.exam_change_date
,x_a_levels => cur_ucas_app_rec.a_levels
,x_as_levels => cur_ucas_app_rec.as_levels
,x_highers => cur_ucas_app_rec.highers
,x_csys => cur_ucas_app_rec.csys
,x_winter => cur_ucas_app_rec.winter
,x_previous => cur_ucas_app_rec.previous
,x_gnvq => cur_ucas_app_rec.gnvq
,x_btec => cur_ucas_app_rec.btec
,x_ilc => cur_ucas_app_rec.ilc
,x_ailc => cur_ucas_app_rec.ailc
,x_ib => cur_ucas_app_rec.ib
,x_manual => cur_ucas_app_rec.manual
,x_reg_num => cur_ucas_app_rec.reg_num
,x_oeq => cur_ucas_app_rec.oeq
,x_eas => cur_ucas_app_rec.eas
,x_roa => cur_ucas_app_rec.roa
,x_status => cur_ucas_app_rec.status
,x_firm_now => cur_ucas_app_rec.firm_now
,x_firm_reply => cur_ucas_app_rec.firm_reply
,x_insurance_reply => cur_ucas_app_rec.insurance_reply
,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
,x_residential_category => cur_ucas_app_rec.residential_category
,x_personal_statement => cur_ucas_app_rec.personal_statement
,x_match_prev => cur_ucas_app_rec.match_prev
,x_match_prev_date => cur_ucas_app_rec.match_prev_date
,x_match_winter => cur_ucas_app_rec.match_winter
,x_match_summer => cur_ucas_app_rec.match_summer
,x_gnvq_date => cur_ucas_app_rec.gnvq_date
,x_ib_date => cur_ucas_app_rec.ib_date
,x_ilc_date => cur_ucas_app_rec.ilc_date
,x_ailc_date => cur_ucas_app_rec.ailc_date
,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
,x_prev_surname => cur_ucas_app_rec.prev_surname
,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
,x_sent_to_oss => 'Y'
,x_batch_identifier => cur_ucas_app_rec.batch_identifier
,x_mode => 'R'
,x_gce => cur_ucas_app_rec.gce
,x_vce => cur_ucas_app_rec.vce
,x_sqa => cur_ucas_app_rec.sqa
,x_previousas => cur_ucas_app_rec.previousas
,x_keyskills => cur_ucas_app_rec.keyskills
,x_vocational => cur_ucas_app_rec.vocational
,x_scn => cur_ucas_app_rec.scn
,x_prevoeq => cur_ucas_app_rec.prevoeq
,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
,x_extra_status => cur_ucas_app_rec.extra_status
,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
,x_cef_no => cur_ucas_app_rec.cef_no
,x_system_code => cur_ucas_app_rec.system_code
,x_gcse_eng => cur_ucas_app_rec.gcse_eng
,x_gcse_math => cur_ucas_app_rec.gcse_math
,x_degree_subject => cur_ucas_app_rec.degree_subject
,x_degree_status => cur_ucas_app_rec.degree_status
,x_degree_class => cur_ucas_app_rec.degree_class
,x_gcse_sci => cur_ucas_app_rec.gcse_sci
,x_welshspeaker => cur_ucas_app_rec.welshspeaker
,x_ni_number => cur_ucas_app_rec.ni_number
,x_earliest_start => cur_ucas_app_rec.earliest_start
,x_near_inst => cur_ucas_app_rec.near_inst
,x_pref_reg => cur_ucas_app_rec.pref_reg
,x_qual_eng => cur_ucas_app_rec.qual_eng
,x_qual_math => cur_ucas_app_rec.qual_math
,x_qual_sci => cur_ucas_app_rec.qual_sci
,x_main_qual => cur_ucas_app_rec.main_qual
,x_qual_5 => cur_ucas_app_rec.qual_5
,x_future_serv => cur_ucas_app_rec.future_serv
,x_future_set => cur_ucas_app_rec.future_set
,x_present_serv => cur_ucas_app_rec.present_serv
,x_present_set => cur_ucas_app_rec.present_set
,x_curr_employment => cur_ucas_app_rec.curr_employment
,x_edu_qualification => cur_ucas_app_rec.edu_qualification
,x_ad_batch_id => cur_ucas_app_rec.ad_batch_id
,x_ad_interface_id => cur_ucas_app_rec.ad_interface_id
,x_nationality => cur_ucas_app_rec.nationality
,x_dual_nationality => cur_ucas_app_rec.dual_nationality
,x_special_needs => cur_ucas_app_rec.special_needs
,x_country_birth => cur_ucas_app_rec.country_birth );
igs_uc_app_addreses_pkg.update_row (
x_rowid => cur_app_address_rec.rowid,
x_app_no => cur_app_address_rec.app_no,
x_address_area => cur_app_address_rec.address_area,
x_address1 => cur_app_address_rec.address1,
x_address2 => cur_app_address_rec.address2,
x_address3 => cur_app_address_rec.address3,
x_address4 => cur_app_address_rec.address4,
x_post_code => cur_app_address_rec.post_code,
x_mail_sort => cur_app_address_rec.mail_sort,
x_telephone => cur_app_address_rec.telephone,
x_fax => cur_app_address_rec.fax,
x_email => cur_app_address_rec.email,
x_home_address1 => cur_app_address_rec.home_address1,
x_home_address2 => cur_app_address_rec.home_address2,
x_home_address3 => cur_app_address_rec.home_address3,
x_home_address4 => cur_app_address_rec.home_address4,
x_home_postcode => cur_app_address_rec.home_postcode,
x_home_phone => cur_app_address_rec.home_phone,
x_home_fax => cur_app_address_rec.home_fax,
x_home_email => cur_app_address_rec.home_email,
x_sent_to_oss_flag => 'Y',
x_mobile => cur_app_address_rec.mobile,
x_country_code => cur_app_address_rec.country_code,
x_home_country_code => cur_app_address_rec.home_country_code,
x_ad_batch_id => cur_app_address_rec.ad_batch_id,
x_ad_interface_id => cur_app_address_rec.ad_interface_id,
x_mode => 'R' );
SELECT api_person_id, start_dt, end_dt
FROM IGS_PE_ALT_PERS_ID
WHERE pe_person_id = cp_person_id
AND person_id_type = cp_pers_id_type
ORDER BY START_DT DESC;
SELECT 'X'
FROM IGS_PE_PERSON_ID_TYP
WHERE person_id_type = cp_person_id_type
AND unique_ind = 'Y'
AND closed_ind = 'N';
SELECT 'X'
FROM IGS_PE_ALT_PERS_ID
WHERE api_person_id = cp_api_person_id
AND person_id_type = cp_person_id_type;
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(
IGS_AD_API_INT_S.NEXTVAL
,p_interface_id
,p_ninumber_alt_type
,p_ni_number
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
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 (
IGS_AD_API_INT_S.NEXTVAL
,p_interface_id
,'UCASREGNO'
,p_scn
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
dsridhar 25-SEP-2003 Bug No. 2980137. While inserting into IGS_PE_RES_DTLS_INT,
the EVALUATOR field is populated with PERSON_NUMBER instead
of DECISION_MAKE_ID. Added a cursor to get the PERSON_NUMBER
from DECISION_MAKE_ID. Added a new variable p_retcode to this
procedure to return the error code in case of an error.
rgangara 10-APR-2004 Bug 3553352. Removed cursor cur_uc_defaults and associated
validation as this has been moved to top of the process as
mandatory check instead of doing it at AppNo level.
anwest 25-NOV-2004 UCFD040 - Bug# 4015492 Added 2 new parameters to the signature
and updated the INSERT statement to include these and remove
START_DT
***************************************************************** */
-- Fetch the HESA Mapping value
CURSOR cur_hesa_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT pv.person_number
FROM igs_pe_person_base_v pv, igs_uc_defaults ucd
WHERE pv.person_id = ucd.decision_make_id
AND ucd.system_code = p_system_code;
INSERT INTO IGS_PE_RES_DTLS_INT (
INTERFACE_RES_ID,
INTERFACE_ID,
RESIDENCY_STATUS_CD,
RESIDENCY_CLASS_CD,
EVALUATION_DATE,
EVALUATOR,
STATUS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CAL_TYPE, -- anwest UCFD040 Bug# 4015492 Added new parameter
SEQUENCE_NUMBER) -- anwest UCFD040 Bug# 4015492 Added new parameter
VALUES (
igs_pe_res_dtls_int_s.NEXTVAL
,p_interface_id
,l_residency_status_cd
,fnd_profile.value('IGS_FI_RES_CLASS_ID')
,TRUNC(SYSDATE)
,l_person_number -- Bug No. 2980137. Replaced decision_make_id with l_person_number
,'2' -- Pending Status
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login
,p_cal_type -- anwest UCFD040 Bug# 4015492 Added new parameter
,p_sequence_number); -- anwest UCFD040 Bug# 4015492 Added new parameter
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT 'X'
FROM igs_ad_acad_history_v a
WHERE a.person_id = cp_person_id
AND a.institution_code = cp_inst_cd ;
INSERT INTO igs_ad_acadhis_int_all ( interface_acadhis_id,
interface_id,
institution_code,
current_inst,
end_date,
status,
transcript_required,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES ( igs_ad_acadhis_int_s.NEXTVAL,
p_interface_id,
l_oss_inst,
'N',
NULL,
'2',
'N',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE) );
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT 'X'
FROM IGS_PE_CITIZENSHIP_V
WHERE party_id = cp_person_id
AND country_code = cp_country_code;
INSERT INTO igs_pe_citizen_int (
interface_citizenship_id
,interface_id
,country_code
,status
,date_recognized
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_PE_CITIZEN_INT_S.NEXTVAL
,p_interface_id
,l_oss_val
,'2'
,p_application_date
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_pe_citizen_int (
interface_citizenship_id
,interface_id
,country_code
,status
,date_recognized
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_PE_CITIZEN_INT_S.NEXTVAL
,p_interface_id
,l_oss_val
,'2'
,p_application_date
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = cp_assoc
AND map1 = cp_map1;
SELECT start_date, end_date
FROM IGS_PE_PERS_DISABLTY
WHERE person_id = cp_person_id
AND disability_type = cp_disability_type
ORDER BY start_date DESC;
SELECT disability_type,start_date
FROM IGS_PE_PERS_DISABLTY
WHERE person_id = cp_person_id
AND TRUNC(start_date) <= TRUNC(SYSDATE)
AND end_date IS NULL;
INSERT INTO igs_ad_disablty_int_all (
interface_disablty_id
,interface_id
,disability_type
,start_date
,end_date
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_DISABLTY_INT_S.NEXTVAL
,p_interface_id
,cur_other_disablty_dtls_rec.disability_type
,cur_other_disablty_dtls_rec.start_date
,TRUNC(SYSDATE)
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_ad_disablty_int_all (
interface_disablty_id
,interface_id
,disability_type
,start_date
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_DISABLTY_INT_S.NEXTVAL
,p_interface_id
,l_oss_val
,l_dis_start_dt
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'EMAIL_FORMAT'
AND enabled_flag ='Y'
AND NVL(START_DATE_ACTIVE,SYSDATE) <=SYSDATE
AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
AND LANGUAGE = USERENV('LANG') AND view_application_id = 222 AND security_group_id(+) = 0;
INSERT INTO igs_ad_contacts_int_all (
interface_contacts_id
,interface_id
,phone_number
,status
,contact_point_type
,primary_flag
,phone_line_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_CONTACTS_INT_S.NEXTVAL
,p_interface_id
,p_telephone
,'2'
,'PHONE'
,'Y'
,'GEN'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_ad_contacts_int_all (
interface_contacts_id
,interface_id
,email_address
,email_format
,status
,contact_point_type
,primary_flag
,phone_line_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_CONTACTS_INT_S.NEXTVAL
,p_interface_id
,p_email
,l_email_format
,'2'
,'EMAIL'
,'N'
,'GEN'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_ad_contacts_int_all (
interface_contacts_id
,interface_id
,phone_number
,status
,contact_point_type
,primary_flag
,phone_line_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_CONTACTS_INT_S.NEXTVAL
,p_interface_id
,p_home_phone
,'2'
,'PHONE'
,'N'
,'GEN'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_ad_contacts_int_all (
interface_contacts_id
,interface_id
,phone_number
,status
,contact_point_type
,primary_flag
,phone_line_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_CONTACTS_INT_S.NEXTVAL
,p_interface_id
,p_mobile
,'2'
,'PHONE'
,'N'
,'MOBILE'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
SELECT territory_code
FROM FND_TERRITORIES_VL
WHERE territory_short_name = p_short_name ;
SELECT map2
FROM IGS_HE_CODE_MAP_VAL
WHERE association_code = p_assoc
AND map1 = p_map1;
INSERT INTO igs_ad_addr_int_all (
interface_addr_id
,interface_id
,addr_line_1
,addr_line_2
,addr_line_3
,addr_line_4
,postcode
,country
,delivery_point_code
,correspondence_flag
,start_date
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_ADDR_INT_S.NEXTVAL
,p_interface_id
,p_app_address_dtls_rec.address1
,p_app_address_dtls_rec.address2
,p_app_address_dtls_rec.address3
,p_app_address_dtls_rec.address4
,p_app_address_dtls_rec.post_code
,l_country_code
,p_app_address_dtls_rec.mail_sort
,'Y'
,SYSDATE
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login )
RETURNING interface_addr_id INTO l_interface_addr_id ;
INSERT INTO igs_ad_addrusage_int_all (
interface_addrusage_id
,interface_addr_id
,site_use_code
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
igs_ad_addrusage_int_s.NEXTVAL
,l_interface_addr_id
,l_usage
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
INSERT INTO igs_ad_addr_int_all(
interface_addr_id
,interface_id
,addr_line_1
,addr_line_2
,addr_line_3
,addr_line_4
,postcode
,country
,delivery_point_code
,correspondence_flag
,start_date
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
IGS_AD_ADDR_INT_S.NEXTVAL
,p_interface_id
,l_home_address1
,p_app_address_dtls_rec.home_address2
,p_app_address_dtls_rec.home_address3
,p_app_address_dtls_rec.home_address4
,p_app_address_dtls_rec.home_postcode
,l_country_code
,p_app_address_dtls_rec.mail_sort
,'N'
,SYSDATE
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login )
RETURNING interface_addr_id INTO l_interface_addr_id ;
INSERT INTO igs_ad_addrusage_int_all (
interface_addrusage_id
,interface_addr_id
,site_use_code
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES (
igs_ad_addrusage_int_s.NEXTVAL
,l_interface_addr_id
,NVL(p_addr_usage_home, 'HOME')
,'2'
,g_created_by
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_last_update_login );
SELECT 'X'
FROM IGS_AD_SOURCE_CAT
WHERE source_type_id = cp_source_type_id AND
category_name = cp_category AND
include_ind = 'Y';
SELECT match_set_id
FROM IGS_PE_MATCH_SETS
WHERE source_type_id = cp_source_type_id;
SELECT interface_id
FROM IGS_AD_INTERFACE_ALL
WHERE batch_id = cp_batch_id;
/* The admission import process should be launched only if admission interface records are inserted
in instance of the current run and the Source Type ID and Match Set ID are not null */
IF NVL(p_ad_batch_id,0) <> 0 AND cur_ad_interface%FOUND AND
NVL(p_source_type_id,0) <> 0 AND NVL(l_match_set_id,0) <> 0 THEN
p_status := TRUE;