[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT current_inst_code
FROM igs_uc_defaults
WHERE system_code = 'U';
rbezawad 27-Apr-04 Added code to insert a record into igs_uc_inst_control
when there is no existing record for bug 3595582.
jbaber 03-Aug-05 Import ALL fields instead of just some for bug 4532072
jchakrab 08-Aug-2005 Modified for UC315 - removed validation for insttype
and updater, as these columns are no longer used
***************************************************************** */
l_rowcnt NUMBER ;
SELECT uinst.rowid,
uinst.*
FROM igs_uc_uinst_ints uinst
WHERE record_status = 'N';
SELECT count(*)
FROM igs_uc_inst_control;
SELECT inst.rowid row_id,
inst.*
FROM igs_uc_inst_control inst;
igs_uc_inst_control_pkg.insert_row -- IGSXI20B.pls
(
x_rowid => l_rowid
,x_updater => '-1'
,x_inst_type => 'C'
,x_inst_short_name => NULL
,x_inst_name => NULL
,x_inst_full_name => NULL
,x_switchboard_tel_no => NULL
,x_decision_cards => NULL
,x_record_cards => NULL
,x_labels => NULL
,x_weekly_mov_list_seq => NULL
,x_weekly_mov_paging => NULL
,x_form_seq => NULL
,x_ebl_required => NULL
,x_ebl_media_1or2 => NULL
,x_ebl_media_3 => NULL
,x_ebl_1or2_merged => NULL
,x_ebl_1or2_board_group => NULL
,x_ebl_3_board_group => NULL
,x_ebl_nc_app => NULL
,x_ebl_major_key1 => NULL
,x_ebl_major_key2 => NULL
,x_ebl_major_key3 => NULL
,x_ebl_minor_key1 => NULL
,x_ebl_minor_key2 => NULL
,x_ebl_minor_key3 => NULL
,x_ebl_final_key => NULL
,x_odl1 => NULL
,x_odl1a => NULL
,x_odl2 => NULL
,x_odl3 => NULL
,x_odl_summer => NULL
,x_odl_route_b => NULL
,x_monthly_seq => NULL
,x_monthly_paper => NULL
,x_monthly_page => NULL
,x_monthly_type => NULL
,x_june_list_seq => NULL
,x_june_labels => NULL
,x_june_num_labels => NULL
,x_course_analysis => NULL
,x_campus_used => NULL
,x_d3_doc_required => NULL
,x_clearing_accept_copy_form => NULL
,x_online_message => NULL
,x_ethnic_list_seq => NULL
,x_mode => 'R'
,x_starx => NULL
);
igs_uc_inst_control_pkg.update_row -- IGSXI20B.pls
(
x_rowid => old_inst_ctl_rec.row_id
,x_updater => new_uinst_rec.updater
,x_inst_type => new_uinst_rec.insttype
,x_inst_short_name => new_uinst_rec.instshortname
,x_inst_name => new_uinst_rec.instname
,x_inst_full_name => new_uinst_rec.instfullname
,x_switchboard_tel_no => new_uinst_rec.switchboardtelno
,x_decision_cards => new_uinst_rec.decisioncards
,x_record_cards => new_uinst_rec.recordcards
,x_labels => new_uinst_rec.labels
,x_weekly_mov_list_seq => new_uinst_rec.weeklymovlistseq
,x_weekly_mov_paging => new_uinst_rec.weeklymovpaging
,x_form_seq => new_uinst_rec.formseq
,x_ebl_required => new_uinst_rec.eblrequired
,x_ebl_media_1or2 => new_uinst_rec.eblmedia1or2
,x_ebl_media_3 => new_uinst_rec.eblmedia3
,x_ebl_1or2_merged => new_uinst_rec.ebl1or2merged
,x_ebl_1or2_board_group => new_uinst_rec.ebl1or2boardgroup
,x_ebl_3_board_group => new_uinst_rec.ebl3boardgroup
,x_ebl_nc_app => new_uinst_rec.eblncapp
,x_ebl_major_key1 => new_uinst_rec.eblmajorkey1
,x_ebl_major_key2 => new_uinst_rec.eblmajorkey2
,x_ebl_major_key3 => new_uinst_rec.eblmajorkey3
,x_ebl_minor_key1 => new_uinst_rec.eblminorkey1
,x_ebl_minor_key2 => new_uinst_rec.eblminorkey2
,x_ebl_minor_key3 => new_uinst_rec.eblminorkey3
,x_ebl_final_key => new_uinst_rec.eblfinalkey
,x_odl1 => new_uinst_rec.odl1
,x_odl1a => new_uinst_rec.odl1a
,x_odl2 => new_uinst_rec.odl2
,x_odl3 => new_uinst_rec.odl3
,x_odl_summer => new_uinst_rec.odlsummer
,x_odl_route_b => new_uinst_rec.odlrouteb
,x_monthly_seq => new_uinst_rec.monthlyseq
,x_monthly_paper => new_uinst_rec.monthlypaper
,x_monthly_page => new_uinst_rec.monthlypage
,x_monthly_type => new_uinst_rec.monthlytype
,x_june_list_seq => new_uinst_rec.junelistseq
,x_june_labels => new_uinst_rec.junelabels
,x_june_num_labels => new_uinst_rec.junenumlabels
,x_course_analysis => new_uinst_rec.courseanalysis
,x_campus_used => new_uinst_rec.campusused
,x_d3_doc_required => new_uinst_rec.d3docsrequired
,x_clearing_accept_copy_form => new_uinst_rec.clearingacceptcopyform
,x_online_message => new_uinst_rec.onlinemessage
,x_ethnic_list_seq => new_uinst_rec.ethniclistseq
,x_mode => 'R'
,x_starx => new_uinst_rec.starx
); EXCEPTION
UPDATE igs_uc_uinst_ints
SET error_code = g_error_code
WHERE rowid = new_uinst_rec.rowid;
UPDATE igs_uc_uinst_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_uinst_rec.rowid;
Purpose : For processing Updateable Offer Abbreviations data from UCAS
Known limitations,enhancements,remarks:
Change History
Who When What
jchakrab 08-Aug-2005 Modified for UC315
- Added update-facility for offer abbrevs as UvOfferAbbrev view
is no longer updateable via odbc-link
- Only AbbrevText can be updateable via net-update system
- Removed validation for letterformat, as this column is no longer used
***************************************************************** */
-- Get new interface records
CURSOR int_uvoffabrv_cur IS
SELECT rowid
,abbrevid
,updater
,abbrevtext
,letterformat
,summarychar
,abbrevuse
FROM igs_uc_uofabrv_ints
WHERE record_status = 'N';
SELECT rowid,
abbrev_code,
uv_timestamp,
uv_updater,
abbrev_text,
letter_format,
summary_char,
uncond,
withdrawal,
release,
imported,
sent_to_ucas,
deleted,
tariff
FROM igs_uc_ref_off_abrv
WHERE abbrev_code = p_abbrev;
igs_uc_ref_off_abrv_pkg.insert_row --IGSXI30B.pls
(
x_rowid => old_uvoffabrv_rec.rowid -- i.e. NULL.
,x_abbrev_code => l_char_abbrev
,x_uv_updater => new_uvoffabrv_rec.updater
,x_abbrev_text => new_uvoffabrv_rec.abbrevtext
,x_letter_format => new_uvoffabrv_rec.letterformat
,x_summary_char => NVL(new_uvoffabrv_rec.summarychar, 'N')
,x_uncond => l_uncond
,x_withdrawal => l_withdrawal
,x_release => 'N'
,x_imported => 'N'
,x_sent_to_ucas => 'Y'
,x_deleted => 'N'
,x_tariff => NULL
,x_mode => 'R'
);
ELSE -- update
BEGIN
igs_uc_ref_off_abrv_pkg.update_row --IGSXI30B.pls
(
x_rowid => old_uvoffabrv_rec.rowid
,x_abbrev_code => old_uvoffabrv_rec.abbrev_code
,x_uv_updater => old_uvoffabrv_rec.uv_updater
,x_abbrev_text => new_uvoffabrv_rec.abbrevtext -- only abbrev_text is updateable
,x_letter_format => old_uvoffabrv_rec.letter_format
,x_summary_char => old_uvoffabrv_rec.summary_char
,x_uncond => old_uvoffabrv_rec.uncond
,x_withdrawal => old_uvoffabrv_rec.withdrawal
,x_release => old_uvoffabrv_rec.release
,x_imported => old_uvoffabrv_rec.imported
,x_sent_to_ucas => old_uvoffabrv_rec.sent_to_ucas
,x_deleted => old_uvoffabrv_rec.deleted
,x_tariff => old_uvoffabrv_rec.tariff
,x_mode => 'R'
);
UPDATE igs_uc_uofabrv_ints
SET error_code = g_error_code
WHERE rowid = new_uvoffabrv_rec.rowid;
UPDATE igs_uc_uofabrv_ints
SET record_status = l_rec_status,
error_code = NULL
WHERE rowid = new_uvoffabrv_rec.rowid;
SELECT cvinst.rowid,
cvinst.*
FROM igs_uc_cinst_ints cvinst
WHERE record_status = 'N';
SELECT cominst.rowid,
cominst.*
FROM igs_uc_com_inst cominst
WHERE cominst.inst = p_inst ;
igs_uc_com_inst_pkg.insert_row -- IGSXI09B.pls
(
x_rowid => old_inst_rec.rowid
,x_inst => new_cvinst_rec.inst
,x_inst_code => new_cvinst_rec.instcode
,x_inst_name => new_cvinst_rec.instname
,x_ucas => 'Y' -- For FTUG System
,x_gttr => NVL(new_cvinst_rec.gttr, 'N')
,x_swas => NVL(new_cvinst_rec.swas, 'N')
,x_nmas => NVL(new_cvinst_rec.nmas, 'N')
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_com_inst_pkg.update_row -- IGSXI09B.pls
(
x_rowid => old_inst_rec.rowid
,x_inst => old_inst_rec.inst
,x_inst_code => new_cvinst_rec.instcode
,x_inst_name => new_cvinst_rec.instname
,x_ucas => 'Y' -- For FTUG System
,x_gttr => NVL(old_inst_rec.gttr, 'N')
,x_swas => NVL(old_inst_rec.swas, 'N')
,x_nmas => NVL(old_inst_rec.nmas, 'N')
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert/update
UPDATE igs_uc_cinst_ints
SET error_code = g_error_code
WHERE rowid = new_cvinst_rec.rowid;
UPDATE igs_uc_cinst_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_cvinst_rec.rowid;
SELECT cves.rowid,
cves.*
FROM igs_uc_ceblsbj_ints cves
WHERE cves.record_status = 'N';
SELECT ces.rowid,
ces.*
FROM igs_uc_com_ebl_subj ces
WHERE ces.subject_id = p_sub_id ;
SELECT 'X'
FROM igs_uc_ref_awrdbdy
WHERE year = p_year AND
sitting = p_sitting AND
awarding_body = p_awd_body;
igs_uc_com_ebl_subj_pkg.insert_row -- IGSXI08B.pls
(
x_rowid => old_eblsubj_rec.rowid
,x_subject_id => new_eblsubj_rec.subjectid
,x_year => new_eblsubj_rec.year
,x_sitting => new_eblsubj_rec.sitting
,x_awarding_body => new_eblsubj_rec.awardingbody
,x_external_ref => new_eblsubj_rec.externalref
,x_exam_level => new_eblsubj_rec.examlevel
,x_title => new_eblsubj_rec.title
,x_subject_code => NVL(new_eblsubj_rec.subjcode ,'ZZZZZZ')
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_com_ebl_subj_pkg.update_row -- IGSXI08B.pls
(
x_rowid => old_eblsubj_rec.rowid
,x_subject_id => old_eblsubj_rec.subject_id
,x_year => new_eblsubj_rec.year
,x_sitting => new_eblsubj_rec.sitting
,x_awarding_body => new_eblsubj_rec.awardingbody
,x_external_ref => new_eblsubj_rec.externalref
,x_exam_level => new_eblsubj_rec.examlevel
,x_title => new_eblsubj_rec.title
,x_subject_code => NVL(new_eblsubj_rec.subjcode ,'ZZZZZZ')
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert/update
UPDATE igs_uc_ceblsbj_ints
SET error_code = g_error_code
WHERE rowid = new_eblsubj_rec.rowid;
UPDATE igs_uc_ceblsbj_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_eblsubj_rec.rowid;
SELECT cvs.rowid,
cvs.*
FROM igs_uc_cvsch_ints cvs
WHERE cvs.record_status = 'N';
SELECT csh.rowid,
csh.*
FROM igs_uc_com_sch csh
WHERE csh.school = p_school_id ;
SELECT 'X'
FROM igs_uc_ref_codes
WHERE code_type = 'ST'
AND code = p_sch_type;
SELECT a.ROWID
FROM igs_uc_com_schsites a
WHERE school = p_sch_id
AND sitecode = p_site_cd;
igs_uc_com_sch_pkg.insert_row -- IGSXI10B.pls
(
x_rowid => old_school_rec.rowid
,x_school => new_school_rec.school
,x_school_name => new_school_rec.schoolname
,x_name_change_date => NULL
,x_former_name => new_school_rec.formername
,x_ncn => new_school_rec.ncn
,x_edexcel_ncn => new_school_rec.edexcelncn
,x_dfee_code => new_school_rec.dfeecode
,x_country => new_school_rec.country
,x_lea => new_school_rec.lea
,x_ucas_status => new_school_rec.ucasstatus
,x_estab_group => new_school_rec.estabgrp
,x_school_type => NVL(new_school_rec.schooltype,'A')
,x_stats_date => NVL(new_school_rec.statsdate, TRUNC(SYSDATE))
,x_number_on_roll => NVL(new_school_rec.noroll ,0)
,x_number_in_5_form => NVL(new_school_rec.no5th ,0)
,x_number_in_6_form => NVL(new_school_rec.no6th ,0)
,x_number_to_he => NVL(new_school_rec.nohe ,0)
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_com_sch_pkg.update_row -- IGSXI10B.pls
(
x_rowid => old_school_rec.rowid
,x_school => old_school_rec.school
,x_school_name => new_school_rec.schoolname
,x_name_change_date => NULL
,x_former_name => new_school_rec.formername
,x_ncn => new_school_rec.ncn
,x_edexcel_ncn => new_school_rec.edexcelncn
,x_dfee_code => new_school_rec.dfeecode
,x_country => new_school_rec.country
,x_lea => new_school_rec.lea
,x_ucas_status => new_school_rec.ucasstatus
,x_estab_group => new_school_rec.estabgrp
,x_school_type => NVL(new_school_rec.schooltype,'A')
,x_stats_date => NVL(new_school_rec.statsdate,TRUNC(SYSDATE))
,x_number_on_roll => NVL(new_school_rec.noroll ,0)
,x_number_in_5_form => NVL(new_school_rec.no5th ,0)
,x_number_in_6_form => NVL(new_school_rec.no6th ,0)
,x_number_to_he => NVL(new_school_rec.nohe ,0)
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert/update
igs_uc_com_schsites_pkg.insert_row -- IGSXI11B.pls
(
x_rowid => l_schsite_rowid
,x_school => new_school_rec.school
,x_sitecode => NVL(new_school_rec.sitecode ,'A')
,x_address1 => new_school_rec.address1
,x_address2 => new_school_rec.address2
,x_address3 => new_school_rec.address3
,x_address4 => new_school_rec.address4
,x_postcode => new_school_rec.postcode
,x_mailsort => new_school_rec.mailsort
,x_town_key => new_school_rec.townkey
,x_county_key => new_school_rec.countykey
,x_country_code => new_school_rec.countrycode
,x_imported => 'Y'
,x_mode => 'R'
);
igs_uc_com_schsites_pkg.update_row -- IGSXI11B.pls
(
x_rowid => l_schsite_rowid
,x_school => old_school_rec.school
,x_sitecode => NVL(new_school_rec.sitecode, 'A')
,x_address1 => new_school_rec.address1
,x_address2 => new_school_rec.address2
,x_address3 => new_school_rec.address3
,x_address4 => new_school_rec.address4
,x_postcode => new_school_rec.postcode
,x_mailsort => new_school_rec.mailsort
,x_town_key => new_school_rec.townkey
,x_county_key => new_school_rec.countykey
,x_country_code => new_school_rec.countrycode
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert/update school sites
UPDATE igs_uc_cvsch_ints
SET error_code = g_error_code
WHERE rowid = new_school_rec.rowid;
UPDATE igs_uc_cvsch_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_school_rec.rowid;
SELECT csci.rowid,
csci.*
FROM igs_uc_cschcnt_ints csci
WHERE csci.record_status = 'N';
SELECT csscn.rowid,
csscn.*
FROM igs_uc_com_scsicnts csscn
WHERE csscn.school = p_school
AND csscn.sitecode = p_site_code
AND csscn.contact_code = p_contact_cd;
SELECT 'X'
FROM igs_uc_com_sch
WHERE school = p_school;
SELECT 'X'
FROM igs_uc_com_schsites
WHERE school = p_school
AND sitecode = p_site_code;
igs_uc_com_scsicnts_pkg.insert_row --IGSXI12B.pls
(
x_rowid => old_schsite_cnt_rec.rowid
,x_school => new_schcntct_rec.school
,x_sitecode => new_schcntct_rec.sitecode
,x_contact_code => new_schcntct_rec.contactcode
,x_contact_post => new_schcntct_rec.contactpost
,x_contact_name => new_schcntct_rec.contactname
,x_telephone => new_schcntct_rec.telephone
,x_fax => new_schcntct_rec.fax
,x_email => new_schcntct_rec.email
,x_principal => NVL(new_schcntct_rec.principal,'N')
,x_lists => NVL(new_schcntct_rec.lists,'N')
,x_orders => NVL(new_schcntct_rec.orders,'N')
,x_forms => NVL(new_schcntct_rec.forms,'N')
,x_referee => NVL(new_schcntct_rec.referee,'N')
,x_careers => NVL(new_schcntct_rec.careers,'N')
,x_eas_contact => NVL(new_schcntct_rec.eascontact,'N')
,x_imported => 'Y'
,x_mode => 'R'
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_com_scsicnts_pkg.update_row --IGSXI12B.pls
(
x_rowid => old_schsite_cnt_rec.rowid
,x_school => new_schcntct_rec.school
,x_sitecode => new_schcntct_rec.sitecode
,x_contact_code => new_schcntct_rec.contactcode
,x_contact_post => new_schcntct_rec.contactpost
,x_contact_name => new_schcntct_rec.contactname
,x_telephone => new_schcntct_rec.telephone
,x_fax => new_schcntct_rec.fax
,x_email => new_schcntct_rec.email
,x_principal => NVL(new_schcntct_rec.principal,'N')
,x_lists => NVL(new_schcntct_rec.lists,'N')
,x_orders => NVL(new_schcntct_rec.orders,'N')
,x_forms => NVL(new_schcntct_rec.forms,'N')
,x_referee => NVL(new_schcntct_rec.referee,'N')
,x_careers => NVL(new_schcntct_rec.careers,'N')
,x_eas_contact => NVL(new_schcntct_rec.eascontact,'N')
,x_imported => 'Y'
,x_mode => 'R'
);
END IF; -- insert/update
UPDATE igs_uc_cschcnt_ints
SET error_code = g_error_code
WHERE rowid = new_schcntct_rec.rowid;
UPDATE igs_uc_cschcnt_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_schcntct_rec.rowid;
SELECT csci.rowid,
csci.*
FROM igs_uc_ccrse_ints csci
WHERE csci.record_status = 'N';
SELECT crdet.rowid,
crdet.*
FROM igs_uc_crse_dets crdet
WHERE crdet.ucas_program_code = p_course
AND crdet.institute = p_inst
AND crdet.ucas_campus = p_campus
AND crdet.system_code = p_system_code;
SELECT 'X'
FROM igs_uc_com_inst
WHERE inst = p_inst;
SELECT a.ROWID row_id,
a.*
FROM igs_uc_crse_dets a, igs_uc_ucrsvop_ints b
WHERE a.ucas_program_code = b.course
AND a.ucas_campus = b.campus
AND a.institute = g_crnt_institute
AND a.system_code = 'U'
AND b.record_status = 'N';
igs_uc_crse_dets_pkg.insert_row -- IGSXI14B.pls
(
x_rowid => old_crse_rec.rowid
,x_ucas_program_code => new_cvcrse_rec.course
,x_oss_program_code => NULL
,x_oss_program_version => NULL
,x_institute => new_cvcrse_rec.inst
,x_uvcourse_updater => '5'
,x_uvcrsevac_updater => '5'
,x_short_title => new_cvcrse_rec.shortname
,x_long_title => new_cvcrse_rec.longname
,x_ucas_campus => new_cvcrse_rec.campus
,x_oss_location => NULL
,x_faculty => new_cvcrse_rec.faculty
,x_total_no_of_seats => NULL
,x_min_entry_points => NULL
,x_max_entry_points => NULL
,x_current_validity => 'R'
,x_deferred_validity => 'R'
,x_term_1_start => NULL
,x_term_1_end => NULL
,x_term_2_start => NULL
,x_term_2_end => NULL
,x_term_3_start => NULL
,x_term_3_end => NULL
,x_term_4_start => NULL
,x_term_4_end => NULL
,x_cl_updated => NULL
,x_cl_date => NULL
,x_vacancy_status => NULL
,x_no_of_vacancy => NULL
,x_score => NULL
,x_rb_full => NULL
,x_scot_vac => NULL
,x_sent_to_ucas => 'Y'
,x_mode => 'R'
,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
,x_oss_attendance_type => NULL
,x_oss_attendance_mode => NULL
,x_joint_admission_ind => new_cvcrse_rec.jointadmission
,x_open_extra_ind => new_cvcrse_rec.openextra
,x_system_code => new_cvcrse_rec.system_code
,x_clearing_options => 'N'
,x_imported => 'Y'
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
(
x_rowid => old_crse_rec.rowid
,x_ucas_program_code => old_crse_rec.ucas_program_code
,x_oss_program_code => old_crse_rec.oss_program_code
,x_oss_program_version => old_crse_rec.oss_program_version
,x_institute => old_crse_rec.institute
,x_uvcourse_updater => old_crse_rec.uvcourse_updater
,x_uvcrsevac_updater => old_crse_rec.uvcrsevac_updater
,x_short_title => new_cvcrse_rec.shortname
,x_long_title => new_cvcrse_rec.longname
,x_ucas_campus => old_crse_rec.ucas_campus
,x_oss_location => old_crse_rec.oss_location
,x_faculty => new_cvcrse_rec.faculty
,x_total_no_of_seats => old_crse_rec.total_no_of_seats
,x_min_entry_points => old_crse_rec.min_entry_points
,x_max_entry_points => old_crse_rec.max_entry_points
,x_current_validity => old_crse_rec.current_validity
,x_deferred_validity => old_crse_rec.deferred_validity
,x_term_1_start => old_crse_rec.term_1_start
,x_term_1_end => old_crse_rec.term_1_end
,x_term_2_start => old_crse_rec.term_2_start
,x_term_2_end => old_crse_rec.term_2_end
,x_term_3_start => old_crse_rec.term_3_start
,x_term_3_end => old_crse_rec.term_3_end
,x_term_4_start => old_crse_rec.term_4_start
,x_term_4_end => old_crse_rec.term_4_end
,x_cl_updated => old_crse_rec.cl_updated
,x_cl_date => old_crse_rec.cl_date
,x_vacancy_status => old_crse_rec.vacancy_status
,x_no_of_vacancy => old_crse_rec.no_of_vacancy
,x_score => old_crse_rec.score
,x_rb_full => old_crse_rec.rb_full
,x_scot_vac => old_crse_rec.scot_vac
,x_sent_to_ucas => old_crse_rec.sent_to_ucas
,x_mode => 'R'
,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
,x_oss_attendance_type => old_crse_rec.oss_attendance_type
,x_oss_attendance_mode => old_crse_rec.oss_attendance_mode
,x_joint_admission_ind => new_cvcrse_rec.jointadmission
,x_open_extra_ind => new_cvcrse_rec.openextra
,x_system_code => old_crse_rec.system_code
,x_clearing_options => old_crse_rec.clearing_options
,x_imported => 'Y'
);
END IF; -- insert/update
UPDATE igs_uc_ccrse_ints
SET error_code = g_error_code
WHERE rowid = new_cvcrse_rec.rowid;
UPDATE igs_uc_ccrse_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_cvcrse_rec.rowid;
igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
(
x_rowid => crse_vacops_rec.row_id
,x_ucas_program_code => crse_vacops_rec.ucas_program_code
,x_oss_program_code => crse_vacops_rec.oss_program_code
,x_oss_program_version => crse_vacops_rec.oss_program_version
,x_institute => crse_vacops_rec.institute
,x_uvcourse_updater => crse_vacops_rec.uvcourse_updater
,x_uvcrsevac_updater => crse_vacops_rec.uvcrsevac_updater
,x_short_title => crse_vacops_rec.short_title
,x_long_title => crse_vacops_rec.long_title
,x_ucas_campus => crse_vacops_rec.ucas_campus
,x_oss_location => crse_vacops_rec.oss_location
,x_faculty => crse_vacops_rec.faculty
,x_total_no_of_seats => crse_vacops_rec.total_no_of_seats
,x_min_entry_points => crse_vacops_rec.min_entry_points
,x_max_entry_points => crse_vacops_rec.max_entry_points
,x_current_validity => crse_vacops_rec.current_validity
,x_deferred_validity => crse_vacops_rec.deferred_validity
,x_term_1_start => crse_vacops_rec.term_1_start
,x_term_1_end => crse_vacops_rec.term_1_end
,x_term_2_start => crse_vacops_rec.term_2_start
,x_term_2_end => crse_vacops_rec.term_2_end
,x_term_3_start => crse_vacops_rec.term_3_start
,x_term_3_end => crse_vacops_rec.term_3_end
,x_term_4_start => crse_vacops_rec.term_4_start
,x_term_4_end => crse_vacops_rec.term_4_end
,x_cl_updated => crse_vacops_rec.cl_updated
,x_cl_date => crse_vacops_rec.cl_date
,x_vacancy_status => crse_vacops_rec.vacancy_status
,x_no_of_vacancy => crse_vacops_rec.no_of_vacancy
,x_score => crse_vacops_rec.score
,x_rb_full => crse_vacops_rec.rb_full
,x_scot_vac => crse_vacops_rec.scot_vac
,x_sent_to_ucas => crse_vacops_rec.sent_to_ucas
,x_mode => 'R'
,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
,x_oss_attendance_type => crse_vacops_rec.oss_attendance_type
,x_oss_attendance_mode => crse_vacops_rec.oss_attendance_mode
,x_joint_admission_ind => crse_vacops_rec.joint_admission_ind
,x_open_extra_ind => crse_vacops_rec.open_extra_ind
,x_system_code => crse_vacops_rec.system_code
,x_clearing_options => 'Y'
,x_imported => crse_vacops_rec.imported
);
Purpose : For processing Updateable Course details info. from UCAS
Known limitations,enhancements,remarks:
Change History
Who When What
******************************************************************/
-- Get new interface records
CURSOR int_uvcrse_cur IS
SELECT usci.rowid,
usci.*
FROM igs_uc_ucrse_ints usci
WHERE usci.record_status = 'N';
SELECT ucrdet.rowid,
ucrdet.*
FROM igs_uc_crse_dets ucrdet
WHERE ucrdet.ucas_program_code = p_course
AND ucrdet.institute = p_inst
AND ucrdet.ucas_campus = p_campus
AND ucrdet.system_code = p_system;
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
(
x_rowid => old_ucrse_rec.rowid
,x_ucas_program_code => old_ucrse_rec.ucas_program_code
,x_oss_program_code => old_ucrse_rec.oss_program_code
,x_oss_program_version => old_ucrse_rec.oss_program_version
,x_institute => old_ucrse_rec.institute
,x_uvcourse_updater => NVL(new_uvcrse_rec.updater,'5')
,x_uvcrsevac_updater => old_ucrse_rec.uvcrsevac_updater
,x_short_title => new_uvcrse_rec.shorttitle
,x_long_title => new_uvcrse_rec.longtitle
,x_ucas_campus => old_ucrse_rec.ucas_campus
,x_oss_location => old_ucrse_rec.oss_location
,x_faculty => new_uvcrse_rec.faculty
,x_total_no_of_seats => old_ucrse_rec.total_no_of_seats
,x_min_entry_points => old_ucrse_rec.min_entry_points
,x_max_entry_points => old_ucrse_rec.max_entry_points
,x_current_validity => old_ucrse_rec.current_validity
,x_deferred_validity => old_ucrse_rec.deferred_validity
,x_term_1_start => new_uvcrse_rec.term1start
,x_term_1_end => new_uvcrse_rec.term1end
,x_term_2_start => new_uvcrse_rec.term2start
,x_term_2_end => new_uvcrse_rec.term2end
,x_term_3_start => new_uvcrse_rec.term3start
,x_term_3_end => new_uvcrse_rec.term3end
,x_term_4_start => new_uvcrse_rec.term4start
,x_term_4_end => new_uvcrse_rec.term4end
,x_cl_updated => old_ucrse_rec.cl_updated
,x_cl_date => old_ucrse_rec.cl_date
,x_vacancy_status => old_ucrse_rec.vacancy_status
,x_no_of_vacancy => old_ucrse_rec.no_of_vacancy
,x_score => old_ucrse_rec.score
,x_rb_full => old_ucrse_rec.rb_full
,x_scot_vac => old_ucrse_rec.scot_vac
,x_sent_to_ucas => old_ucrse_rec.sent_to_ucas
,x_mode => 'R'
,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
,x_oss_attendance_type => old_ucrse_rec.oss_attendance_type
,x_oss_attendance_mode => old_ucrse_rec.oss_attendance_mode
,x_joint_admission_ind => new_uvcrse_rec.jointadmission
,x_open_extra_ind => new_uvcrse_rec.openextra
,x_system_code => old_ucrse_rec.system_code
,x_clearing_options => old_ucrse_rec.clearing_options
,x_imported => 'Y'
);
END IF; -- insert/update
UPDATE igs_uc_ucrse_ints
SET error_code = g_error_code
WHERE rowid = new_uvcrse_rec.rowid;
UPDATE igs_uc_ucrse_ints
SET record_status = 'D',
error_code = NULL
WHERE rowid = new_uvcrse_rec.rowid;
Purpose : For processing Updateable Course Vacancy option details info. from UCAS
Known limitations,enhancements,remarks:
Change History
Who When What
rgangara 16-ARP-04 Modified keyword processing to delete existing keyword records for the
combination (Course,campus, optioncode) and insert afresh the entire
set. If any records fails in the set, none of the records should get
processed. All keyword records with error would as usual get populated
with Error Code. For records which are successful but could not be processed
as the set has some invalid records would be populated with 2002 error code.
This is done as part of bug# 3496874.
jbaber 15-SEP-05 Removed keyno from cursor for bug 4589994
******************************************************************/
-- Get distinct Course options for new interface records
CURSOR int_crseops_cur IS
SELECT DISTINCT int.course,
int.campus,
int.optioncode
FROM igs_uc_ucrskwd_ints int
WHERE int.record_status = 'N';
SELECT ucrvop.rowid, ucrvop.keyword
FROM igs_uc_crse_keywrds ucrvop
WHERE ucrvop.ucas_program_code = p_course
AND ucrvop.institute = g_crnt_institute
AND ucrvop.ucas_campus = p_campus
AND ucrvop.option_code = p_opt_code
AND ucrvop.system_code = p_system;
SELECT ucvi.rowid,
ucvi.*
FROM igs_uc_ucrskwd_ints ucvi
WHERE ucvi.record_status = 'N'
AND ucvi.course = cp_course
AND ucvi.campus = cp_campus
AND ucvi.optioncode = cp_optioncd;
SELECT ucrvop.rowid,
ucrvop.*
FROM igs_uc_crse_keywrds ucrvop
WHERE ucrvop.ucas_program_code = p_course
AND ucrvop.institute = g_crnt_institute
AND ucrvop.ucas_campus = p_campus
AND ucrvop.option_code = p_opt_code
AND ucrvop.keyword = p_keyword
AND ucrvop.system_code = p_system;
SELECT ucrdet.rowid
FROM igs_uc_crse_dets ucrdet
WHERE ucrdet.ucas_program_code = p_course
AND ucrdet.institute = g_crnt_institute
AND ucrdet.ucas_campus = p_campus
AND ucrdet.system_code = p_system;
SELECT rowid
FROM igs_uc_ref_keywords
WHERE keyword = p_keyword;
UPDATE igs_uc_ucrskwd_ints
SET error_code = NULL
WHERE record_status = 'N';
igs_uc_crse_keywrds_pkg.delete_row (old_crskwd_del_rec.rowid);
igs_uc_crse_keywrds_pkg.insert_row -- IGSXI15B.pls
(
x_rowid => old_crsekwd_rec.rowid
,x_ucas_program_code => new_ucrsekwd_rec.course
,x_institute => g_crnt_institute
,x_ucas_campus => new_ucrsekwd_rec.campus
,x_option_code => new_ucrsekwd_rec.optioncode
,x_preference => new_ucrsekwd_rec.keyno
,x_keyword => new_ucrsekwd_rec.keyword
,x_updater => NVL(new_ucrsekwd_rec.updater,'5')
,x_active => NVL(new_ucrsekwd_rec.active,'Y')
,x_deleted => 'N'
,x_sent_to_ucas => 'Y'
,x_mode => 'R'
,x_system_code => 'U'
,x_crse_keyword_id => l_crse_keyword_id
);
ELSE -- update
BEGIN
-- update a new record in the main table
igs_uc_crse_keywrds_pkg.update_row -- IGSXI15B.pls
(
x_rowid => old_crsekwd_rec.rowid
,x_ucas_program_code => old_crsekwd_rec.ucas_program_code
,x_institute => old_crsekwd_rec.institute
,x_ucas_campus => old_crsekwd_rec.ucas_campus
,x_option_code => old_crsekwd_rec.option_code
,x_preference => old_crsekwd_rec.preference
,x_keyword => new_ucrsekwd_rec.keyword
,x_updater => NVL(new_ucrsekwd_rec.updater,'5')
,x_active => NVL(new_ucrsekwd_rec.active,'Y')
,x_deleted => old_crsekwd_rec.deleted
,x_sent_to_ucas => 'Y'
,x_mode => 'R'
,x_system_code => old_crsekwd_rec.system_code
,x_crse_keyword_id => old_crsekwd_rec.crse_keyword_id
);
END IF; -- insert/update
UPDATE igs_uc_ucrskwd_ints
SET error_code = g_error_code
WHERE rowid = new_ucrsekwd_rec.rowid;
igs_uc_crse_keywrds_pkg.delete_row (old_crskwd_del_rec.rowid);
UPDATE igs_uc_ucrskwd_ints SET error_code = '2002'
WHERE record_status = 'N'
AND course = int_crseops_rec.course
AND campus = int_crseops_rec.campus
AND optioncode = int_crseops_rec.optioncode
AND error_code IS NULL ;
UPDATE igs_uc_ucrskwd_ints SET record_status = 'D' , error_code = NULL
WHERE record_status = 'N'
AND course = int_crseops_rec.course
AND campus = int_crseops_rec.campus
AND optioncode = int_crseops_rec.optioncode;