[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(current_cycle) current_cycle , MAX(configured_cycle) configured_cycle
FROM igs_uc_defaults ;
SELECT entry_year
FROM igs_uc_ucas_control
WHERE system_code = cp_system_code
AND ucas_cycle = g_cyc_info_rec.configured_cycle;
SELECT ucas_interface
FROM IGS_UC_CYC_DEFAULTS
WHERE system_code = cp_system_code
AND ucas_cycle = g_cyc_info_rec.configured_cycle;
SELECT aprdate, disdate, errordate, ethnicdate, examdate, feedate,
keyworddate, oeqdate, offerabbrevdate, offersubjdate, poccdate,
rescatdate, awardbodydate, statusdate, ucasgroupdate, estabgroup,
schooltype, tariffdate, jointadmissions,
sysdate AS COUNTRY, sysdate AS NATIONALITY
FROM igs_uc_u_cvrefamendments_2003 a;
SELECT aprdate, disdate, errordate, ethnicdate, examdate, feedate,
keyworddate, oeqdate, offerabbrevdate, offersubjdate, poccdate,
rescatdate, awardbodydate, statusdate, ucasgroupdate, estabgroup,
schooltype, tariffdate, jointadmissions, country, nationality
FROM igs_uc_u_cvrefamendments_2007 ;
SELECT *
FROM igs_uc_g_cvgrefamendments_2006;
SELECT hrc.timestamp
FROM igs_uc_hrc_timstmps hrc
WHERE view_name = p_view
AND ucas_cycle = g_cyc_info_rec.configured_cycle ;
Purpose : inserts/updates hercules timestamp record for passed view
Known limitations,enhancements,remarks:
Change History
Who When What
***************************************************************** */
l_rowid VARCHAR2(50) ;
SELECT DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,timestamp
,RTRIM(faculty) faculty
,RTRIM(shortname) shortname
,RTRIM(longname) longname
,RTRIM(validcurrent) validcurrent
,RTRIM(validdefer) validdefer
,RTRIM(jointadmission) jointadmission
,RTRIM(openextra) openextra
FROM igs_uc_u_cvcourse_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_cvcourse_2003 ;
UPDATE igs_uc_ccrse_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_cvcourse_rec.course
AND campus = c_cvcourse_rec.campus AND inst = c_cvcourse_rec.inst ;
INSERT INTO igs_uc_ccrse_ints ( inst,
course,
campus,
faculty,
shortname,
longname,
validcurrent,
validdefer,
jointadmission,
openextra,
system_code,
record_status,
error_code )
VALUES ( c_cvcourse_rec.inst,
c_cvcourse_rec.course,
c_cvcourse_rec.campus,
c_cvcourse_rec.faculty,
c_cvcourse_rec.shortname,
c_cvcourse_rec.longname,
c_cvcourse_rec.validcurrent,
c_cvcourse_rec.validdefer,
c_cvcourse_rec.jointadmission,
c_cvcourse_rec.openextra,
'U',
'N',
NULL) ;
SELECT DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,timestamp
,RTRIM(SUBSTR(longname,1,20)) shortname
,RTRIM(longname) longname
FROM igs_uc_g_cvgcourse_2007
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_g_cvgcourse_2007 ;
UPDATE igs_uc_ccrse_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_cvgcourse_rec.course
AND campus = c_cvgcourse_rec.campus AND inst = c_cvgcourse_rec.inst ;
INSERT INTO igs_uc_ccrse_ints ( inst,
course,
campus,
shortname,
longname,
system_code,
record_status,
error_code )
VALUES ( c_cvgcourse_rec.inst,
c_cvgcourse_rec.course,
c_cvgcourse_rec.campus,
c_cvgcourse_rec.shortname,
c_cvgcourse_rec.longname,
'G',
'N',
NULL) ;
SELECT DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,timestamp
,RTRIM(shortname) shortname
,RTRIM(longname) longname
FROM igs_uc_n_cvncourse_2007
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_n_cvncourse_2007 ;
UPDATE igs_uc_ccrse_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_cvncourse_rec.course
AND campus = c_cvncourse_rec.campus AND inst = c_cvncourse_rec.inst ;
INSERT INTO igs_uc_ccrse_ints ( inst,
course,
campus,
shortname,
longname,
system_code,
record_status,
error_code )
VALUES ( c_cvncourse_rec.inst,
c_cvncourse_rec.course,
c_cvncourse_rec.campus,
c_cvncourse_rec.shortname,
c_cvncourse_rec.longname,
'N',
'N',
NULL) ;
jchakrab 24-Mar-06 Removed ROWID from columns selected in c_cvebl - 5114377
***************************************************************** */
-- Get all the records from hercules view whose timestamp is > passed timestamp
-- or get all the records in hercules view if the timestamp passed is null
CURSOR c_cvebl( p_timestamp igs_uc_hrc_timstmps.timestamp%TYPE ) IS
SELECT
subjectid
,timestamp
,year
,NVL(DECODE(RTRIM(sitting),NULL, RPAD('*',LENGTH(sitting),'*'), RTRIM(sitting)),'*') sitting
,NVL(DECODE(RTRIM(awardingbody),NULL, RPAD('*',LENGTH(awardingbody),'*'), RTRIM(awardingbody)),'*') awardingbody
,RTRIM(externalref) externalref
,DECODE(RTRIM(examlevel),NULL, RPAD('*',LENGTH(examlevel),'*'), RTRIM(examlevel)) examlevel
,RTRIM(title) title
,RTRIM(subjcode) subjcode
FROM igs_uc_u_cveblsubject_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_cveblsubject_2003 ;
UPDATE igs_uc_ceblsbj_ints SET record_status = 'O'
WHERE record_status = 'N' AND subjectid = c_cvebl_rec.subjectid ;
INSERT INTO igs_uc_ceblsbj_ints ( subjectid,
year,
sitting,
awardingbody,
externalref,
examlevel,
title,
subjcode,
record_status,
error_code )
VALUES ( c_cvebl_rec.subjectid,
c_cvebl_rec.year,
c_cvebl_rec.sitting,
c_cvebl_rec.awardingbody,
c_cvebl_rec.externalref,
c_cvebl_rec.examlevel,
c_cvebl_rec.title,
c_cvebl_rec.subjcode,
'N',
NULL) ;
SELECT DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,timestamp
,RTRIM(instcode) instcode
,RTRIM(instname) instname
,RTRIM(ucas) ucas
,RTRIM(gttr) gttr
,'N' swas
,RTRIM(nmas) nmas
FROM igs_uc_u_cvinstitution_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_cvinstitution_2003 ;
UPDATE igs_uc_cinst_ints SET record_status = 'O'
WHERE record_status = 'N' AND inst = c_cvinst_rec.inst ;
INSERT INTO igs_uc_cinst_ints ( inst,
instcode,
instname,
ucas,
gttr,
swas,
nmas,
record_status,
error_code )
VALUES ( c_cvinst_rec.inst,
c_cvinst_rec.instcode,
c_cvinst_rec.instname,
c_cvinst_rec.ucas,
c_cvinst_rec.gttr,
c_cvinst_rec.swas,
c_cvinst_rec.nmas,
'N',
NULL) ;
SELECT DECODE(RTRIM(childinst),NULL, RPAD('*',LENGTH(childinst),'*'), RTRIM(childinst)) childinst
,NVL(DECODE(RTRIM(parentinst1),NULL, RPAD('*',LENGTH(parentinst1),'*'), RTRIM(parentinst1)),'*') parentinst1
,RTRIM(parentinst2) parentinst2
,RTRIM(parentinst3) parentinst3
,RTRIM(parentinst4) parentinst4
,RTRIM(parentinst5) parentinst5
FROM igs_uc_u_cvjntadmissions_2003 ;
UPDATE igs_uc_cjntadm_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_cjntadm_ints ( childinst,
parentinst1,
parentinst2,
parentinst3,
parentinst4,
parentinst5,
record_status,
error_code )
VALUES ( c_cvjntadm_rec.childinst,
c_cvjntadm_rec.parentinst1,
c_cvjntadm_rec.parentinst2,
c_cvjntadm_rec.parentinst3,
c_cvjntadm_rec.parentinst4,
c_cvjntadm_rec.parentinst5,
'N',
NULL) ;
SELECT dom
,RTRIM(domtext) domtext
,RTRIM(leaflag) leaflag
FROM igs_uc_u_cvrefapr_2003 ;
UPDATE igs_uc_crapr_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crapr_ints ( dom,
domtext,
leaflag,
record_status,
error_code )
VALUES ( c_cvapr_rec.dom,
c_cvapr_rec.domtext,
c_cvapr_rec.leaflag,
'N',
NULL) ;
SELECT year
,DECODE(RTRIM(sitting),NULL, RPAD('*',LENGTH(sitting),'*'), RTRIM(sitting)) sitting
,DECODE(RTRIM(awardingbody),NULL, RPAD('*',LENGTH(awardingbody),'*'), RTRIM(awardingbody)) awardingbody
,RTRIM(bodyname) bodyname
,RTRIM(bodyabbrev) bodyabbrev
FROM igs_uc_u_cvrefawardbody_2003 ;
UPDATE igs_uc_crawdbd_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crawdbd_ints ( year,
sitting,
awardingbody,
bodyname,
bodyabbrev,
record_status,
error_code)
VALUES ( c_cvawb_rec.year,
c_cvawb_rec.sitting,
c_cvawb_rec.awardingbody,
c_cvawb_rec.bodyname,
c_cvawb_rec.bodyabbrev,
'N',
NULL) ;
SELECT dis
,RTRIM(distext) distext
FROM igs_uc_u_cvrefdis_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'DC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'DC',
c_cvdis_rec.dis,
c_cvdis_rec.distext,
'N',
NULL) ;
SELECT errorcode
,RTRIM(errordescription) errordescription
FROM igs_uc_u_cvreferror_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'EC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'EC',
c_cverr_rec.errorcode,
c_cverr_rec.errordescription,
'N',
NULL) ;
SELECT DECODE(RTRIM(estabgrp),NULL, RPAD('*',LENGTH(estabgrp),'*'), RTRIM(estabgrp)) estabgrp
,RTRIM(estabtext) estabtext
FROM igs_uc_u_cvrefestgroup_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'EG' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'EG',
c_cvest_rec.estabgrp,
c_cvest_rec.estabtext,
'N',
NULL) ;
SELECT ethnic
,RTRIM(ethnictext) ethnictext
FROM igs_uc_u_cvrefethnic_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'ET' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'ET',
c_cveth_rec.ethnic,
c_cveth_rec.ethnictext,
'N',
NULL) ;
SELECT DECODE(RTRIM(examlevel),NULL, RPAD('*',LENGTH(examlevel),'*'), RTRIM(examlevel)) examlevel
,RTRIM(examtext) examtext
FROM igs_uc_u_cvrefexam_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'EL' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'EL',
c_cvexam_rec.examlevel,
c_cvexam_rec.examtext,
'N',
NULL) ;
SELECT feepayer
,RTRIM(feetext) feetext
FROM igs_uc_u_cvreffee_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'FC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'FC',
c_cvfee_rec.feepayer,
c_cvfee_rec.feetext,
'N',
NULL) ;
SELECT DECODE(RTRIM(keyword),NULL, RPAD('*',LENGTH(keyword),'*'), RTRIM(keyword)) keyword
FROM igs_uc_u_cvrefkeyword_2003 ;
UPDATE igs_uc_crkywd_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crkywd_ints ( keyword,
record_status,
error_code )
VALUES ( c_cvkyw_rec.keyword,
'N',
NULL) ;
SELECT DECODE(RTRIM(oeq),NULL, RPAD('*',LENGTH(oeq),'*'), RTRIM(oeq)) oeq
,RTRIM(oeqtext) oeqtext
FROM igs_uc_u_cvrefoeq_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'OC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'OC',
c_cvoeq_rec.oeq,
c_cvoeq_rec.oeqtext,
'N',
NULL) ;
SELECT DECODE(RTRIM(abbrevcode),NULL, RPAD('*',LENGTH(abbrevcode),'*'), RTRIM(abbrevcode)) abbrevcode
,RTRIM(abbrevtext) abbrevtext
,DECODE(RTRIM(letterformat),NULL, RPAD('*',LENGTH(letterformat),'*'), RTRIM(letterformat)) letterformat
,RTRIM(summarychar) summarychar
,RTRIM(uncond) uncond
,RTRIM(withdrawal) withdrawal
,RTRIM(release) release
,RTRIM(tariff) tariff
FROM igs_uc_u_cvrefofferabbrev_2003 ;
UPDATE igs_uc_croffab_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_croffab_ints ( abbrevcode,
abbrevtext,
letterformat,
summarychar,
uncond,
withdrawal,
release,
tariff,
record_status,
error_code )
VALUES ( c_cvoffabr_rec.abbrevcode,
c_cvoffabr_rec.abbrevtext,
c_cvoffabr_rec.letterformat,
c_cvoffabr_rec.summarychar,
c_cvoffabr_rec.uncond,
c_cvoffabr_rec.withdrawal,
c_cvoffabr_rec.release,
c_cvoffabr_rec.tariff,
'N',
NULL) ;
SELECT DECODE(RTRIM(subjcode),NULL, RPAD('*',LENGTH(subjcode),'*'), RTRIM(subjcode)) subjcode
,RTRIM(subjtext) subjtext
FROM igs_uc_u_cvrefoffersubj_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'SB' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'SB',
c_cvoffsbj_rec.subjcode,
c_cvoffsbj_rec.subjtext,
'N',
NULL) ;
SELECT pocc
,socialclass
,RTRIM(occupationtext) occupationtext
,RTRIM(alternativetext) alternativetext
,alternateclass1
,alternateclass2
,socioeconomic
FROM igs_uc_u_cvrefpocc_2003 ;
UPDATE igs_uc_crefpoc_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crefpoc_ints ( pocc,
socialclass,
occupationtext,
alternativetext,
alternateclass1,
alternateclass2,
socioeconomic,
record_status,
error_code )
VALUES ( c_cvpocc_rec.pocc,
c_cvpocc_rec.socialclass,
c_cvpocc_rec.occupationtext,
c_cvpocc_rec.alternativetext,
c_cvpocc_rec.alternateclass1,
c_cvpocc_rec.alternateclass2,
c_cvpocc_rec.socioeconomic,
'N',
NULL) ;
SELECT DECODE(RTRIM(rescat),NULL, RPAD('*',LENGTH(rescat),'*'), RTRIM(rescat)) rescat
,RTRIM(rescattext) rescattext
FROM igs_uc_u_cvrefrescat_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'RC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'RC',
c_cvrescat_rec.rescat,
c_cvrescat_rec.rescattext,
'N',
NULL) ;
SELECT DECODE(RTRIM(schooltype),NULL, RPAD('*',LENGTH(schooltype),'*'), RTRIM(schooltype)) schooltype
,RTRIM(schooltext) schooltext
FROM igs_uc_u_cvrefschooltype_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'ST' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'ST',
c_cvschtyp_rec.schooltype,
c_cvschtyp_rec.schooltext,
'N',
NULL) ;
SELECT status
,RTRIM(statustext) statustext
FROM igs_uc_u_cvrefstatus_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'SC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'SC',
c_cvstatus_rec.status,
c_cvstatus_rec.statustext,
'N',
NULL) ;
SELECT DECODE(RTRIM(examlevel),NULL, RPAD('*',LENGTH(examlevel),'*'), RTRIM(examlevel)) examlevel
,DECODE(RTRIM(examgrade),NULL, RPAD('*',LENGTH(examgrade),'*'), RTRIM(examgrade)) examgrade
,DECODE(RTRIM(tariffscore),NULL, RPAD('*',LENGTH(tariffscore),'*'), RTRIM(tariffscore)) tariffscore
FROM igs_uc_u_cvreftariff_2003 ;
UPDATE igs_uc_ctariff_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_ctariff_ints ( examlevel,
examgrade,
tariffscore,
record_status,
error_code )
VALUES ( c_cvtariff_rec.examlevel,
c_cvtariff_rec.examgrade,
c_cvtariff_rec.tariffscore,
'N',
NULL) ;
SELECT groupno
,RTRIM(groupname) groupname
FROM igs_uc_u_cvrefucasgroup_2003 ;
l_new_max_timestamp igs_uc_u_cvrefamendments_2003.ucasgroupdate%TYPE ;
l_new_max_timestamp := g_refamend_timestamp.ucasgroupdate ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'GN' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'GN',
c_cvugroup_rec.groupno,
c_cvugroup_rec.groupname,
'N',
NULL) ;
SELECT school
,DECODE(RTRIM(sitecode),NULL, RPAD('*',LENGTH(sitecode),'*'), RTRIM(sitecode)) sitecode
,timestamp
,RTRIM(schoolname) schoolname
,namechangedate
,RTRIM(formername) formername
,RTRIM(SUBSTR(ncn,1,5)) ncn
,RTRIM(edexcelncn) edexcelncn
,RTRIM(dfeecode) dfeecode
,country
,RTRIM(lea) lea
,RTRIM(ucasstatus) ucasstatus
,NVL(DECODE(RTRIM(estabgrp),NULL, RPAD('*',LENGTH(estabgrp),'*'), RTRIM(estabgrp)),'*') estabgrp
,RTRIM(schooltype) schooltype
,statsdate
,noroll
,no5th
,no6th
,nohe
,RTRIM(address1) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(townkey) townkey
,RTRIM(countykey) countykey
,RTRIM(countrycode) countrycode
FROM igs_uc_u_cvschool_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_cvschool_2003 ;
UPDATE igs_uc_cvsch_ints SET record_status = 'O'
WHERE record_status = 'N' AND school = c_cvschool_rec.school ;
INSERT INTO igs_uc_cvsch_ints ( school,
sitecode,
schoolname,
namechangedate,
formername,
ncn,
edexcelncn,
dfeecode,
country,
lea,
ucasstatus,
estabgrp,
schooltype,
statsdate,
noroll,
no5th,
no6th,
nohe,
address1,
address2,
address3,
address4,
postcode,
mailsort,
townkey,
countykey,
countrycode,
record_status,
error_code
)
VALUES ( c_cvschool_rec.school,
NVL(c_cvschool_rec.sitecode,'A'),
c_cvschool_rec.schoolname,
c_cvschool_rec.namechangedate,
c_cvschool_rec.formername,
c_cvschool_rec.ncn,
c_cvschool_rec.edexcelncn,
c_cvschool_rec.dfeecode,
c_cvschool_rec.country,
c_cvschool_rec.lea,
c_cvschool_rec.ucasstatus,
c_cvschool_rec.estabgrp,
c_cvschool_rec.schooltype,
c_cvschool_rec.statsdate,
c_cvschool_rec.noroll,
c_cvschool_rec.no5th,
c_cvschool_rec.no6th,
c_cvschool_rec.nohe,
c_cvschool_rec.address1,
c_cvschool_rec.address2,
c_cvschool_rec.address3,
c_cvschool_rec.address4,
c_cvschool_rec.postcode,
c_cvschool_rec.mailsort,
c_cvschool_rec.townkey,
c_cvschool_rec.countykey,
c_cvschool_rec.countrycode,
'N',
NULL) ;
SELECT school
,DECODE(RTRIM(sitecode),NULL, RPAD('*',LENGTH(sitecode),'*'), RTRIM(sitecode)) sitecode
,contactcode
,timestamp
,RTRIM(contactpost) contactpost
,RTRIM(contactname) contactname
,RTRIM(telephone) telephone
,RTRIM(fax) fax
,RTRIM(email) email
,RTRIM(principal) principal
,RTRIM(lists) lists
,RTRIM(orders) orders
,RTRIM(forms) forms
,RTRIM(referee) referee
,RTRIM(careers) careers
,RTRIM(eascontact) eascontact
FROM igs_uc_u_cvschoolcontact_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_cvschoolcontact_2003 ;
UPDATE igs_uc_cschcnt_ints SET record_status = 'O'
WHERE record_status = 'N' AND school = c_cvschcnt_rec.school
AND sitecode = NVL(c_cvschcnt_rec.sitecode,'A') AND contactcode = c_cvschcnt_rec.contactcode ;
INSERT INTO igs_uc_cschcnt_ints ( school,
sitecode,
contactcode,
contactpost,
contactname,
telephone,
fax,
email,
principal,
lists,
orders,
forms,
referee,
careers,
eascontact,
record_status,
error_code
)
VALUES ( c_cvschcnt_rec.school,
NVL(c_cvschcnt_rec.sitecode,'A'),
c_cvschcnt_rec.contactcode,
c_cvschcnt_rec.contactpost,
c_cvschcnt_rec.contactname,
c_cvschcnt_rec.telephone,
c_cvschcnt_rec.fax,
c_cvschcnt_rec.email,
c_cvschcnt_rec.principal,
c_cvschcnt_rec.lists,
c_cvschcnt_rec.orders,
c_cvschcnt_rec.forms,
c_cvschcnt_rec.referee,
c_cvschcnt_rec.careers,
c_cvschcnt_rec.eascontact,
'N',
NULL) ;
SELECT DECODE(RTRIM(contactcode),NULL, RPAD('*',LENGTH(contactcode),'*'), RTRIM(contactcode)) contactcode
,timestamp
,RTRIM(updater) updater
,DECODE(RTRIM(name),NULL, RPAD('*',LENGTH(name),'*'), RTRIM(name)) name
,DECODE(RTRIM(post),NULL, RPAD('*',LENGTH(post),'*'), RTRIM(post)) post
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(telephone) telephone
,RTRIM(email) email
,RTRIM(fax) fax
FROM igs_uc_u_uvcontact_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcontact_2003 ;
UPDATE igs_uc_ucntact_ints SET record_status = 'O'
WHERE record_status = 'N' AND contactcode = c_uvcnt_rec.contactcode ;
INSERT INTO igs_uc_ucntact_ints ( contactcode,
updater,
name,
post,
address1,
address2,
address3,
address4,
telephone,
email,
fax,
record_status,
error_code
)
VALUES ( c_uvcnt_rec.contactcode,
c_uvcnt_rec.updater,
c_uvcnt_rec.name,
c_uvcnt_rec.post,
c_uvcnt_rec.address1,
c_uvcnt_rec.address2,
c_uvcnt_rec.address3,
c_uvcnt_rec.address4,
c_uvcnt_rec.telephone,
c_uvcnt_rec.email,
c_uvcnt_rec.fax,
'N',
NULL) ;
SELECT DECODE(RTRIM(contactcode),NULL, RPAD('*',LENGTH(contactcode),'*'), RTRIM(contactcode)) contactcode
,ucasgroup
,RTRIM(updater) updater
,timestamp
FROM igs_uc_u_uvcontgrp_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcontgrp_2003 ;
UPDATE igs_uc_ucntgrp_ints SET record_status = 'O'
WHERE record_status = 'N' AND contactcode = c_uvcntgr_rec.contactcode
AND ucasgroup = c_uvcntgr_rec.ucasgroup ;
INSERT INTO igs_uc_ucntgrp_ints ( contactcode,
ucasgroup,
updater,
record_status,
error_code
)
VALUES ( c_uvcntgr_rec.contactcode,
c_uvcntgr_rec.ucasgroup,
c_uvcntgr_rec.updater,
'N',
NULL) ;
SELECT DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,RTRIM(faculty) faculty
,RTRIM(shorttitle) shorttitle
,RTRIM(longtitle) longtitle
,RTRIM(validcurr) validcurr
,RTRIM(validdefer) validdefer
,term1start
,term1end
,term2start
,term2end
,term3start
,term3end
,term4start
,term4end
,RTRIM(jointadmission) jointadmission
,RTRIM(openextra) openextra
FROM igs_uc_u_uvcourse_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcourse_2003 ;
UPDATE igs_uc_ucrse_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_uvcrse_rec.course
AND campus = c_uvcrse_rec.campus ;
INSERT INTO igs_uc_ucrse_ints ( course,
campus,
updater,
faculty,
shorttitle,
longtitle,
validcurr,
validdefer,
term1start,
term1end,
term2start,
term2end,
term3start,
term3end,
term4start,
term4end,
jointadmission,
openextra,
record_status,
error_code
)
VALUES ( c_uvcrse_rec.course,
c_uvcrse_rec.campus,
c_uvcrse_rec.updater,
c_uvcrse_rec.faculty,
c_uvcrse_rec.shorttitle,
c_uvcrse_rec.longtitle,
c_uvcrse_rec.validcurr,
c_uvcrse_rec.validdefer,
c_uvcrse_rec.term1start,
c_uvcrse_rec.term1end,
c_uvcrse_rec.term2start,
c_uvcrse_rec.term2end,
c_uvcrse_rec.term3start,
c_uvcrse_rec.term3end,
c_uvcrse_rec.term4start,
c_uvcrse_rec.term4end,
c_uvcrse_rec.jointadmission,
c_uvcrse_rec.openextra,
'N',
NULL) ;
SELECT DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)) campus
,DECODE(RTRIM(optioncode),NULL, RPAD('*',LENGTH(optioncode),'*'), RTRIM(optioncode)) optioncode
,NULL keyno
,DECODE(RTRIM(keyword),NULL, RPAD('*',LENGTH(keyword),'*'), RTRIM(keyword)) keyword
,timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,RTRIM(active) active
FROM igs_uc_u_uvcoursekeyword_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcoursekeyword_2003 ;
UPDATE igs_uc_ucrskwd_ints
SET record_status = 'O'
WHERE record_status = 'N'
AND (course, campus, optioncode) IN (
SELECT DISTINCT DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course,
DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)) campus,
DECODE(RTRIM(optioncode),NULL, RPAD('*',LENGTH(optioncode),'*'), RTRIM(optioncode)) optioncode
FROM igs_uc_u_uvcoursekeyword_2003
WHERE (timestamp > p_old_timestamp OR p_old_timestamp IS NULL));
INSERT INTO igs_uc_ucrskwd_ints ( course,
campus,
optioncode,
keyno,
keyword,
updater,
active,
record_status,
error_code
)
VALUES ( c_uvcrsekyw_rec.course,
c_uvcrsekyw_rec.campus,
c_uvcrsekyw_rec.optioncode,
c_uvcrsekyw_rec.keyno,
c_uvcrsekyw_rec.keyword,
c_uvcrsekyw_rec.updater,
c_uvcrsekyw_rec.active,
'N',
NULL) ;
SELECT DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)) campus
,timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,RTRIM(clupdated) clupdated
,cldate
,RTRIM(vacstatus) vacstatus
,RTRIM(novac) novac
,score
,RTRIM(rbfull) rbfull
,RTRIM(scotvac) scotvac
FROM igs_uc_u_uvcoursevacs_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcoursevacs_2003 ;
UPDATE igs_uc_ucrsvac_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_uvcrsevac_rec.course
AND campus = c_uvcrsevac_rec.campus ;
INSERT INTO igs_uc_ucrsvac_ints ( course,
campus,
updater,
clupdated,
cldate,
vacstatus,
novac,
score,
rbfull,
scotvac,
record_status,
error_code
)
VALUES ( c_uvcrsevac_rec.course,
c_uvcrsevac_rec.campus,
c_uvcrsevac_rec.updater,
c_uvcrsevac_rec.clupdated,
c_uvcrsevac_rec.cldate,
c_uvcrsevac_rec.vacstatus,
c_uvcrsevac_rec.novac,
c_uvcrsevac_rec.score,
c_uvcrsevac_rec.rbfull,
c_uvcrsevac_rec.scotvac,
'N',
NULL) ;
SELECT DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)) campus
,DECODE(RTRIM(optioncode),NULL, RPAD('*',LENGTH(optioncode),'*'), RTRIM(optioncode)) optioncode
,timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,RTRIM(clupdated) clupdated
,cldate
,RTRIM(vacstatus) vacstatus
FROM igs_uc_u_uvcoursevacops_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvcoursevacops_2003 ;
UPDATE igs_uc_ucrsvop_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = c_uvcrsevacop_rec.course
AND campus = c_uvcrsevacop_rec.campus AND optioncode = c_uvcrsevacop_rec.optioncode ;
INSERT INTO igs_uc_ucrsvop_ints ( course,
campus,
optioncode,
updater,
clupdated,
cldate,
vacstatus,
record_status,
error_code
)
VALUES ( c_uvcrsevacop_rec.course,
c_uvcrsevacop_rec.campus,
c_uvcrsevacop_rec.optioncode,
c_uvcrsevacop_rec.updater,
c_uvcrsevacop_rec.clupdated,
c_uvcrsevacop_rec.cldate,
c_uvcrsevacop_rec.vacstatus,
'N',
NULL) ;
SELECT timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,DECODE(RTRIM(insttype),NULL, RPAD('*',LENGTH(insttype),'*'), RTRIM(insttype)) insttype
,RTRIM(instshortname) instshortname
,RTRIM(instname) instname
,RTRIM(instfullname) instfullname
,RTRIM(switchboardtelno) switchboardtelno
,RTRIM(decisioncards) decisioncards
,RTRIM(recordcards) recordcards
,RTRIM(labels) labels
,RTRIM(weeklymovlistseq) weeklymovlistseq
,RTRIM(weeklymovpaging) weeklymovpaging
,RTRIM(formseq) formseq
,RTRIM(eblrequired) eblrequired
,RTRIM(eblmedia1or2) eblmedia1or2
,RTRIM(eblmedia3) eblmedia3
,RTRIM(ebl1or2merged) ebl1or2merged
,RTRIM(ebl1or2boardgroup) ebl1or2boardgroup
,RTRIM(ebl3boardgroup) ebl3boardgroup
,RTRIM(eblncapp) eblncapp
,RTRIM(eblmajorkey1) eblmajorkey1
,RTRIM(eblmajorkey2) eblmajorkey2
,RTRIM(eblmajorkey3) eblmajorkey3
,RTRIM(eblminorkey1) eblminorkey1
,RTRIM(eblminorkey2) eblminorkey2
,RTRIM(eblminorkey3) eblminorkey3
,RTRIM(eblfinalkey) eblfinalkey
,RTRIM(odl1) odl1
,RTRIM(odl1a) odl1a
,RTRIM(odl2) odl2
,RTRIM(odl3) odl3
,RTRIM(odlsummer) odlsummer
,RTRIM(odlrouteb) odlrouteb
,RTRIM(monthlyseq) monthlyseq
,RTRIM(monthlypaper) monthlypaper
,RTRIM(monthlypage) monthlypage
,RTRIM(monthlytype) monthlytype
,RTRIM(junelistseq) junelistseq
,RTRIM(junelabels) junelabels
,RTRIM(junenumlabels) junenumlabels
,RTRIM(courseanalysis) courseanalysis
,RTRIM(campusused) campusused
,RTRIM(d3docsrequired) d3docsrequired
,RTRIM(clearingacceptcopyform) clearingacceptcopyform
,RTRIM(onlinemessage) onlinemessage
,RTRIM(ethniclistseq) ethniclistseq
FROM igs_uc_u_uvinstitution_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvinstitution_2003 ;
UPDATE igs_uc_uinst_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_uinst_ints ( updater,
insttype,
instshortname,
instname,
instfullname,
switchboardtelno,
decisioncards,
recordcards,
labels,
weeklymovlistseq,
weeklymovpaging,
formseq,
eblrequired,
eblmedia1or2,
eblmedia3,
ebl1or2merged,
ebl1or2boardgroup,
ebl3boardgroup,
eblncapp,
eblmajorkey1,
eblmajorkey2,
eblmajorkey3,
eblminorkey1,
eblminorkey2,
eblminorkey3,
eblfinalkey,
odl1,
odl1a,
odl2,
odl3,
odlsummer,
odlrouteb,
monthlyseq,
monthlypaper,
monthlypage,
monthlytype,
junelistseq,
junelabels,
junenumlabels,
courseanalysis,
campusused,
d3docsrequired,
clearingacceptcopyform,
onlinemessage,
ethniclistseq,
starx,
record_status,
error_code
)
VALUES ( c_uinst_rec.updater,
c_uinst_rec.insttype,
c_uinst_rec.instshortname,
c_uinst_rec.instname,
c_uinst_rec.instfullname,
c_uinst_rec.switchboardtelno,
c_uinst_rec.decisioncards,
c_uinst_rec.recordcards,
c_uinst_rec.labels,
c_uinst_rec.weeklymovlistseq,
c_uinst_rec.weeklymovpaging,
c_uinst_rec.formseq,
c_uinst_rec.eblrequired,
c_uinst_rec.eblmedia1or2,
c_uinst_rec.eblmedia3,
c_uinst_rec.ebl1or2merged,
c_uinst_rec.ebl1or2boardgroup,
c_uinst_rec.ebl3boardgroup,
c_uinst_rec.eblncapp,
c_uinst_rec.eblmajorkey1,
c_uinst_rec.eblmajorkey2,
c_uinst_rec.eblmajorkey3,
c_uinst_rec.eblminorkey1,
c_uinst_rec.eblminorkey2,
c_uinst_rec.eblminorkey3,
c_uinst_rec.eblfinalkey,
c_uinst_rec.odl1,
c_uinst_rec.odl1a,
c_uinst_rec.odl2,
c_uinst_rec.odl3,
c_uinst_rec.odlsummer,
c_uinst_rec.odlrouteb,
c_uinst_rec.monthlyseq,
c_uinst_rec.monthlypaper,
c_uinst_rec.monthlypage,
c_uinst_rec.monthlytype,
c_uinst_rec.junelistseq,
c_uinst_rec.junelabels,
c_uinst_rec.junenumlabels,
c_uinst_rec.courseanalysis,
c_uinst_rec.campusused,
c_uinst_rec.d3docsrequired,
c_uinst_rec.clearingacceptcopyform,
c_uinst_rec.onlinemessage,
c_uinst_rec.ethniclistseq,
'N',
'N',
NULL) ;
SELECT abbrevid
,timestamp
,RTRIM(updater) updater
,RTRIM(abbrevtext) abbrevtext
,DECODE(RTRIM(letterformat),NULL, RPAD('*',LENGTH(letterformat),'*'), RTRIM(letterformat)) letterformat
,RTRIM(summarychar) summarychar
,abbrevuse
FROM igs_uc_u_uvofferabbrev_2003
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvofferabbrev_2003 ;
UPDATE igs_uc_uofabrv_ints SET record_status = 'O'
WHERE record_status = 'N' AND abbrevid = c_uvoffabrv_rec.abbrevid ;
INSERT INTO igs_uc_uofabrv_ints ( abbrevid,
updater,
abbrevtext,
letterformat,
summarychar,
abbrevuse,
record_status,
error_code
)
VALUES ( c_uvoffabrv_rec.abbrevid,
c_uvoffabrv_rec.updater,
c_uvoffabrv_rec.abbrevtext,
c_uvoffabrv_rec.letterformat,
c_uvoffabrv_rec.summarychar,
c_uvoffabrv_rec.abbrevuse,
'N',
NULL) ;
SELECT pocc
,socialclass
,RTRIM(occupationtext) occupationtext
,RTRIM(alternativetext) alternativetext
,alternateclass1
,alternateclass2
FROM igs_uc_u_cvrefpre2000pocc_2003 ;
UPDATE igs_uc_crprepo_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crprepo_ints( pocc,
socialclass,
occupationtext,
alternativetext,
alternateclass1,
alternateclass2,
record_status,
error_code
)
VALUES ( c_cvprepocc_rec.pocc,
c_cvprepocc_rec.socialclass,
c_cvprepocc_rec.occupationtext,
c_cvprepocc_rec.alternativetext,
c_cvprepocc_rec.alternateclass1,
c_cvprepocc_rec.alternateclass2,
'N',
NULL) ;
SELECT socialclass
,RTRIM(socialclasstext) socialclasstext
FROM igs_uc_u_cvrefsocialclass_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type = 'PC';
INSERT INTO igs_uc_crfcode_ints( code_type,
code,
code_text,
record_status,
error_code
)
VALUES ( 'PC',
c_cvsocclas_rec.socialclass,
c_cvsocclas_rec.socialclasstext,
'N',
NULL) ;
SELECT socioecon
,RTRIM(socioecontext) socioecontext
FROM igs_uc_u_cvrefsocioecon_2003 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type = 'PE';
INSERT INTO igs_uc_crfcode_ints( code_type,
code,
code_text,
record_status,
error_code
)
VALUES ( 'PE',
c_cvsocioecon_rec.socioecon,
c_cvsocioecon_rec.socioecontext,
'N',
NULL) ;
SELECT DECODE(RTRIM(subjcode),NULL, RPAD('*',LENGTH(subjcode),'*'), RTRIM(subjcode)) subjcode
,NVL(DECODE(RTRIM(subjtext),NULL, RPAD('*',LENGTH(subjtext),'*'), RTRIM(subjtext)),'*') subjtext
,RTRIM(subjabbrev) subjabbrev
,RTRIM(ebl_subj) ebl_subj
FROM igs_uc_u_cvrefsubj_2003 ;
UPDATE igs_uc_crsubj_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_crsubj_ints( subjcode,
subjtext,
subjabbrev,
ebl_subj,
record_status,
error_code
)
VALUES ( c_cvsubj_rec.subjcode,
c_cvsubj_rec.subjtext,
c_cvsubj_rec.subjabbrev,
c_cvsubj_rec.ebl_subj,
'N',
NULL) ;
SELECT DECODE(RTRIM(countrycode),NULL, RPAD('*',LENGTH(countrycode),'*'), RTRIM(countrycode)) countrycode
,RTRIM(description) description
,RTRIM(type) type
FROM igs_uc_u_cvrefcountry_2007 ;
UPDATE igs_uc_country_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_country_ints ( countrycode,
description,
type,
record_status,
error_code )
VALUES ( c_cvrefcountry_rec.countrycode,
c_cvrefcountry_rec.description,
c_cvrefcountry_rec.type,
'N',
NULL) ;
SELECT DECODE(RTRIM(nationality),NULL, RPAD('*',LENGTH(nationality),'*'), RTRIM(nationality)) nationality
,RTRIM(description) description
FROM igs_uc_u_cvrefnationality_2007 ;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'NC' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'NC',
c_cvrefnationality_rec.nationality,
c_cvrefnationality_rec.description,
'N',
NULL) ;
SELECT degreesubject
,RTRIM(description) description
FROM igs_uc_g_cvgrefdegreesubj_2006;
UPDATE igs_uc_crfcode_ints SET record_status = 'O' WHERE record_status = 'N' AND code_type= 'DS' ;
INSERT INTO igs_uc_crfcode_ints ( code_type,
code,
code_text,
record_status,
error_code )
VALUES ( 'DS',
c_cvgrefdegsubj_rec.degreesubject,
c_cvgrefdegsubj_rec.description,
'N',
NULL );
SELECT appno
,choiceno
,timestamp
,DECODE(RTRIM(offercourse),NULL, RPAD('*',LENGTH(offercourse),'*'), RTRIM(offercourse)) offercourse
,DECODE(RTRIM(offercampus),NULL, RPAD('*',LENGTH(offercampus),'*'), RTRIM(offercampus)) offercampus
,offercourselength
,RTRIM(offerentrymonth) offerentrymonth
,RTRIM(offerentryyear) offerentryyear
,RTRIM(offerentrypoint) offerentrypoint
,RTRIM(offertext) offertext
FROM igs_uc_ivoffer_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivoffer_2003_v ;
UPDATE igs_uc_ioffer_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND choiceno = c_ivoffer_rec.choiceno ;
INSERT INTO igs_uc_ioffer_ints ( appno,
choiceno,
offercourse,
offercampus,
offercourselength,
offerentrymonth,
offerentryyear,
offerentrypoint,
offertext,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivoffer_rec.choiceno,
c_ivoffer_rec.offercourse,
c_ivoffer_rec.offercampus,
c_ivoffer_rec.offercourselength,
c_ivoffer_rec.offerentrymonth,
c_ivoffer_rec.offerentryyear,
c_ivoffer_rec.offerentrypoint,
c_ivoffer_rec.offertext,
'N',
NULL,
g_cyc_info_rec.configured_cycle ) ;
SELECT appno
,roundno
,timestamp
,RTRIM(offertext) offertext
FROM igs_uc_ivgoffer_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgoffer_2006_v ;
UPDATE igs_uc_ioffer_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivgoffer_rec.appno
AND choiceno = c_ivgoffer_rec.roundno ;
INSERT INTO igs_uc_ioffer_ints ( appno,
choiceno,
offertext,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivgoffer_rec.roundno,
c_ivgoffer_rec.offertext,
'N',
NULL,
g_cyc_info_rec.configured_cycle ) ;
SELECT appno
,choiceno
,timestamp
,RTRIM(offertext) offertext
FROM igs_uc_ivnoffer_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnoffer_2006_v ;
UPDATE igs_uc_ioffer_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivnoffer_rec.appno
AND choiceno = c_ivnoffer_rec.choiceno ;
INSERT INTO igs_uc_ioffer_ints ( appno,
choiceno,
offertext,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivnoffer_rec.choiceno,
c_ivnoffer_rec.offertext,
'N',
NULL,
g_cyc_info_rec.configured_cycle ) ;
SELECT appno
,timestamp
,RTRIM(matchprevious) matchprevious
,matchpreviousdate
,RTRIM(matchwinter) matchwinter
,RTRIM(matchsummer) matchsummer
,gnvqdate -- not used in hercules
,ibdate
,ilcdate
,aicedate
,gcesqadate
FROM igs_uc_ivqualification_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivqualification_2003_v ;
UPDATE igs_uc_iqual_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_iqual_ints ( appno,
matchprevious,
matchpreviousdate,
matchwinter,
matchsummer,
gnvqdate,
ibdate,
ilcdate,
aicedate,
gcesqadate,
record_status,
error_code
)
VALUES ( l_appno,
c_ivqual_rec.matchprevious,
c_ivqual_rec.matchpreviousdate,
c_ivqual_rec.matchwinter,
c_ivqual_rec.matchsummer,
c_ivqual_rec.gnvqdate,
c_ivqual_rec.ibdate,
c_ivqual_rec.ilcdate,
c_ivqual_rec.aicedate,
c_ivqual_rec.gcesqadate,
'N',
NULL ) ;
SELECT appno
,timestamp
,addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,NULL fax -- not used in hercules
,RTRIM(email) email
,DECODE(RTRIM(homeaddress1),NULL, RPAD('*',LENGTH(homeaddress1),'*'), RTRIM(homeaddress1)) homeaddress1
,RTRIM(homeaddress2) homeaddress2
,RTRIM(homeaddress3) homeaddress3
,RTRIM(homeaddress4) homeaddress4
,RTRIM(homepostcode) homepostcode
,RTRIM(homephone) homephone
,NULL homefax -- not used in hercules
,NULL homeemail -- not used in hercules
FROM igs_uc_ivstara_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstara_2003_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivstara_rec.appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
fax,
email,
homeaddress1,
homeaddress2,
homeaddress3,
homeaddress4,
homepostcode,
homephone,
homefax,
homeemail,
record_status,
error_code
)
VALUES ( c_ivstara_rec.appno,
c_ivstara_rec.addressarea,
c_ivstara_rec.address1,
c_ivstara_rec.address2,
c_ivstara_rec.address3,
c_ivstara_rec.address4,
c_ivstara_rec.postcode,
c_ivstara_rec.mailsort,
c_ivstara_rec.telephone,
c_ivstara_rec.fax,
c_ivstara_rec.email,
c_ivstara_rec.homeaddress1,
c_ivstara_rec.homeaddress2,
c_ivstara_rec.homeaddress3,
c_ivstara_rec.homeaddress4,
c_ivstara_rec.homepostcode,
c_ivstara_rec.homephone,
c_ivstara_rec.homefax,
c_ivstara_rec.homeemail,
'N',
NULL ) ;
SELECT appno
,timestamp
,addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,DECODE(RTRIM(homeaddress1),NULL, RPAD('*',LENGTH(homeaddress1),'*'), RTRIM(homeaddress1)) homeaddress1
,RTRIM(homeaddress2) homeaddress2
,RTRIM(homeaddress3) homeaddress3
,RTRIM(homeaddress4) homeaddress4
,RTRIM(homepostcode) homepostcode
FROM igs_uc_ivstara_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstara_2006_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homeaddress1,
homeaddress2,
homeaddress3,
homeaddress4,
homepostcode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstara_rec.addressarea,
c_ivstara_rec.address1,
c_ivstara_rec.address2,
c_ivstara_rec.address3,
c_ivstara_rec.address4,
c_ivstara_rec.postcode,
c_ivstara_rec.mailsort,
c_ivstara_rec.telephone,
c_ivstara_rec.email,
c_ivstara_rec.homeaddress1,
c_ivstara_rec.homeaddress2,
c_ivstara_rec.homeaddress3,
c_ivstara_rec.homeaddress4,
c_ivstara_rec.homepostcode,
'N',
NULL ) ;
SELECT appno
,timestamp
,addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,DECODE(RTRIM(homeaddress1),NULL, RPAD('*',LENGTH(homeaddress1),'*'), RTRIM(homeaddress1)) homeaddress1
,RTRIM(homeaddress2) homeaddress2
,RTRIM(homeaddress3) homeaddress3
,RTRIM(homeaddress4) homeaddress4
,RTRIM(homepostcode) homepostcode
,RTRIM(mobile) mobile
,RTRIM(countrycode) countrycode
,RTRIM(homecountrycode) homecountrycode
FROM igs_uc_ivstara_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstara_2007_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homeaddress1,
homeaddress2,
homeaddress3,
homeaddress4,
homepostcode,
mobile,
countrycode,
homecountrycode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstara_rec.addressarea,
c_ivstara_rec.address1,
c_ivstara_rec.address2,
c_ivstara_rec.address3,
c_ivstara_rec.address4,
c_ivstara_rec.postcode,
c_ivstara_rec.mailsort,
c_ivstara_rec.telephone,
c_ivstara_rec.email,
c_ivstara_rec.homeaddress1,
c_ivstara_rec.homeaddress2,
c_ivstara_rec.homeaddress3,
c_ivstara_rec.homeaddress4,
c_ivstara_rec.homepostcode,
c_ivstara_rec.mobile,
c_ivstara_rec.countrycode,
c_ivstara_rec.homecountrycode,
'N',
NULL ) ;
SELECT appno
,timestamp
,RTRIM(addressarea) addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,RTRIM(homepostcode) homepostcode
FROM igs_uc_ivgstara_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstara_2006_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homepostcode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstara_rec.addressarea,
c_ivgstara_rec.address1,
c_ivgstara_rec.address2,
c_ivgstara_rec.address3,
c_ivgstara_rec.address4,
c_ivgstara_rec.postcode,
c_ivgstara_rec.mailsort,
c_ivgstara_rec.telephone,
c_ivgstara_rec.email,
c_ivgstara_rec.homepostcode,
'N',
NULL ) ;
SELECT appno
,timestamp
,addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,DECODE(RTRIM(home_address1),NULL, RPAD('*',LENGTH(home_address1),'*'), RTRIM(home_address1)) homeaddress1
,RTRIM(home_address2) homeaddress2
,RTRIM(home_address3) homeaddress3
,RTRIM(home_address4) homeaddress4
,RTRIM(homepostcode) homepostcode
,RTRIM(mobile) mobile
,RTRIM(countrycode) countrycode
,RTRIM(homecountrycode) homecountrycode
FROM igs_uc_ivgstara_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstara_2007_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homeaddress1,
homeaddress2,
homeaddress3,
homeaddress4,
homepostcode,
mobile,
countrycode,
homecountrycode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstara_rec.addressarea,
c_ivgstara_rec.address1,
c_ivgstara_rec.address2,
c_ivgstara_rec.address3,
c_ivgstara_rec.address4,
c_ivgstara_rec.postcode,
c_ivgstara_rec.mailsort,
c_ivgstara_rec.telephone,
c_ivgstara_rec.email,
c_ivgstara_rec.homeaddress1,
c_ivgstara_rec.homeaddress2,
c_ivgstara_rec.homeaddress3,
c_ivgstara_rec.homeaddress4,
c_ivgstara_rec.homepostcode,
c_ivgstara_rec.mobile,
c_ivgstara_rec.countrycode,
c_ivgstara_rec.homecountrycode,
'N',
NULL ) ;
SELECT appno
,timestamp
,RTRIM(addressarea) addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,RTRIM(homepostcode) homepostcode
FROM igs_uc_ivnstara_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstara_2006_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homepostcode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstara_rec.addressarea,
c_ivnstara_rec.address1,
c_ivnstara_rec.address2,
c_ivnstara_rec.address3,
c_ivnstara_rec.address4,
c_ivnstara_rec.postcode,
c_ivnstara_rec.mailsort,
c_ivnstara_rec.telephone,
c_ivnstara_rec.email,
c_ivnstara_rec.homepostcode,
'N',
NULL ) ;
SELECT appno
,timestamp
,addressarea
,DECODE(RTRIM(address1),NULL, RPAD('*',LENGTH(address1),'*'), RTRIM(address1)) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(postcode) postcode
,mailsort
,RTRIM(telephone) telephone
,RTRIM(email) email
,DECODE(RTRIM(homeaddress1),NULL, RPAD('*',LENGTH(homeaddress1),'*'), RTRIM(homeaddress1)) homeaddress1
,RTRIM(homeaddress2) homeaddress2
,RTRIM(homeaddress3) homeaddress3
,RTRIM(homeaddress4) homeaddress4
,RTRIM(homepostcode) homepostcode
,RTRIM(mobile) mobile
,RTRIM(countrycode) countrycode
,RTRIM(homecountrycode) homecountrycode
FROM igs_uc_ivnstara_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstara_2007_v ;
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istara_ints ( appno,
addressarea,
address1,
address2,
address3,
address4,
postcode,
mailsort,
telephone,
email,
homeaddress1,
homeaddress2,
homeaddress3,
homeaddress4,
homepostcode,
mobile,
countrycode,
homecountrycode,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstara_rec.addressarea,
c_ivnstara_rec.address1,
c_ivnstara_rec.address2,
c_ivnstara_rec.address3,
c_ivnstara_rec.address4,
c_ivnstara_rec.postcode,
c_ivnstara_rec.mailsort,
c_ivnstara_rec.telephone,
c_ivnstara_rec.email,
c_ivnstara_rec.homeaddress1,
c_ivnstara_rec.homeaddress2,
c_ivnstara_rec.homeaddress3,
c_ivnstara_rec.homeaddress4,
c_ivnstara_rec.homepostcode,
c_ivnstara_rec.mobile,
c_ivnstara_rec.countrycode,
c_ivnstara_rec.homecountrycode,
'N',
NULL ) ;
SELECT appno
,NVL(choiceno,9) choiceno -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,timestamp
,NVL(lastchange,SYSDATE) lastchange
,NVL(DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)),'*') inst
,NVL(DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)),'*') course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,RTRIM(faculty) faculty
,RTRIM(home) home
,RTRIM(decision) decision
,decisiondate
,decisionnumber
,RTRIM(reply) reply
,RTRIM(summaryconditions) summaryconditions
,entrymonth
,SUBSTR(LPAD(entryyear,4,0),3,2) entryyear
,entrypoint
,RTRIM(choicecancelled) choicecancelled
,RTRIM(action) action
,RTRIM(substitution) substitution
,datesubstituted
,DECODE(RTRIM(previousinst),NULL, RPAD('*',LENGTH(previousinst),'*'), RTRIM(previousinst)) previousinst
,DECODE(RTRIM(previouscourse),NULL, RPAD('*',LENGTH(previouscourse),'*'), RTRIM(previouscourse)) previouscourse
,DECODE(RTRIM(previouscampus),NULL, RPAD('*',LENGTH(previouscampus),'*'), RTRIM(previouscampus)) previouscampus
,RTRIM(ucasamendment) ucasamendment
,routebpref
,routebround
,RTRIM(SUBSTR(detail,1,20)) detail
,extraround
FROM igs_uc_ivstarc_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarc_2003_v ;
UPDATE igs_uc_istarc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND choiceno = c_ivstarc_rec.choiceno AND ucas_cycle = g_cyc_info_rec.configured_cycle ;
INSERT INTO igs_uc_istarc_ints ( appno,
choiceno,
lastchange,
inst,
course,
campus,
faculty,
home,
decision,
decisiondate,
decisionnumber,
reply,
summaryconditions,
entrymonth,
entryyear,
entrypoint,
choicecancelled,
action,
substitution,
datesubstituted,
previousinst,
previouscourse,
previouscampus,
ucasamendment,
routebpref,
routebround,
detail,
extraround,
residential,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivstarc_rec.choiceno,
c_ivstarc_rec.lastchange,
c_ivstarc_rec.inst,
c_ivstarc_rec.course,
c_ivstarc_rec.campus,
c_ivstarc_rec.faculty,
c_ivstarc_rec.home,
c_ivstarc_rec.decision,
c_ivstarc_rec.decisiondate,
c_ivstarc_rec.decisionnumber,
c_ivstarc_rec.reply,
c_ivstarc_rec.summaryconditions,
c_ivstarc_rec.entrymonth,
c_ivstarc_rec.entryyear,
c_ivstarc_rec.entrypoint,
c_ivstarc_rec.choicecancelled,
c_ivstarc_rec.action,
c_ivstarc_rec.substitution,
c_ivstarc_rec.datesubstituted,
c_ivstarc_rec.previousinst,
c_ivstarc_rec.previouscourse,
c_ivstarc_rec.previouscampus,
c_ivstarc_rec.ucasamendment,
c_ivstarc_rec.routebpref,
c_ivstarc_rec.routebround,
c_ivstarc_rec.detail,
c_ivstarc_rec.extraround,
NULL,
'N',
NULL ,
g_cyc_info_rec.configured_cycle
) ;
SELECT appno
,NVL(choiceno,9) choiceno -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,timestamp
,NVL(lastchange,SYSDATE) lastchange
,NVL(DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)),'*') inst
,NVL(DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)),'*') course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,RTRIM(residential) residential
,RTRIM(decision) decision
,decisiondate
,decisionnumber
,RTRIM(reply) reply
,entrymonth
,SUBSTR(LPAD(entryyear,4,0),3,2) entryyear
,RTRIM(choicecancelled) choicecancelled
,RTRIM(action) action
,RTRIM(substitution) substitution
,datesubstituted
,DECODE(RTRIM(previousinst),NULL, RPAD('*',LENGTH(previousinst),'*'), RTRIM(previousinst)) previousinst
,DECODE(RTRIM(previouscourse),NULL, RPAD('*',LENGTH(previouscourse),'*'), RTRIM(previouscourse)) previouscourse
,DECODE(RTRIM(previouscampus),NULL, RPAD('*',LENGTH(previouscampus),'*'), RTRIM(previouscampus)) previouscampus
FROM igs_uc_ivnstarc_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarc_2006_v ;
UPDATE igs_uc_istarc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND choiceno = c_ivnstarc_rec.choiceno AND ucas_cycle = g_cyc_info_rec.configured_cycle ;
INSERT INTO igs_uc_istarc_ints ( appno,
choiceno,
lastchange,
inst,
course,
campus,
residential,
decision,
decisiondate,
decisionnumber,
reply,
entrymonth,
entryyear,
choicecancelled,
action,
substitution,
datesubstituted,
previousinst,
previouscourse,
previouscampus,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivnstarc_rec.choiceno,
c_ivnstarc_rec.lastchange,
c_ivnstarc_rec.inst,
c_ivnstarc_rec.course,
c_ivnstarc_rec.campus,
c_ivnstarc_rec.residential,
c_ivnstarc_rec.decision,
c_ivnstarc_rec.decisiondate,
c_ivnstarc_rec.decisionnumber,
c_ivnstarc_rec.reply,
c_ivnstarc_rec.entrymonth,
c_ivnstarc_rec.entryyear,
c_ivnstarc_rec.choicecancelled,
c_ivnstarc_rec.action,
c_ivnstarc_rec.substitution,
c_ivnstarc_rec.datesubstituted,
c_ivnstarc_rec.previousinst,
c_ivnstarc_rec.previouscourse,
c_ivnstarc_rec.previouscampus,
'N',
NULL ,
g_cyc_info_rec.configured_cycle
) ;
SELECT appno
,NVL(roundno,99) roundno -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,timestamp
,NVL(lastchange,SYSDATE) lastchange
,NVL(DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)),'*') inst
,NVL(DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)),'*') course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus
,RTRIM(modular) modular
,RTRIM(parttime) parttime
,RTRIM(decision) decision
,RTRIM(reply) reply
,entrymonth
,SUBSTR(LPAD(entryyear,4,0),3,2) entryyear
,RTRIM(action) action
,RTRIM(english) english
,RTRIM(maths) maths
,RTRIM(science) science
,RTRIM(degreestatus) degreestatus
,RTRIM(degreesubject) degreesubject
,RTRIM(degreeclass) degreeclass
,interviewdate
,RTRIM(lateapplication) lateapplication
FROM igs_uc_ivgstarg_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarg_2006_v ;
UPDATE igs_uc_istarg_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND roundno = c_ivgstarg_rec.roundno AND ucas_cycle = g_cyc_info_rec.configured_cycle ;
INSERT INTO igs_uc_istarg_ints ( appno,
roundno,
lastchange,
inst,
course,
campus,
modular,
parttime,
decision,
reply,
entrymonth,
entryyear,
action,
gcseeng,
gcsemath,
gcsesci,
degreestatus,
degreesubject,
degreeclass,
interview,
lateapplication,
record_status,
error_code,
ucas_cycle
)
VALUES ( l_appno,
c_ivgstarg_rec.roundno,
c_ivgstarg_rec.lastchange,
c_ivgstarg_rec.inst,
c_ivgstarg_rec.course,
c_ivgstarg_rec.campus,
c_ivgstarg_rec.modular,
c_ivgstarg_rec.parttime,
c_ivgstarg_rec.decision,
c_ivgstarg_rec.reply,
c_ivgstarg_rec.entrymonth,
c_ivgstarg_rec.entryyear,
c_ivgstarg_rec.action,
c_ivgstarg_rec.english,
c_ivgstarg_rec.maths,
c_ivgstarg_rec.science,
c_ivgstarg_rec.degreestatus,
c_ivgstarg_rec.degreesubject,
c_ivgstarg_rec.degreeclass,
c_ivgstarg_rec.interviewdate,
c_ivgstarg_rec.lateapplication,
'N',
NULL ,
g_cyc_info_rec.configured_cycle
) ;
SELECT appno
,timestamp
,ethnic
,RTRIM(socialclass) socialclass
,pocceduchangedate
,RTRIM(pocc) pocc
,RTRIM(pocctext) pocctext
,NULL lasteducation
,NULL educationleavedate
,NULL lea -- not used in hercules
,socialeconomic
FROM igs_uc_ivstarh_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarh_2003_v ;
UPDATE igs_uc_istarh_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarh_ints ( appno,
ethnic,
socialclass,
pocceduchangedate,
pocc,
pocctext,
lasteducation,
educationleavedate,
lea,
socialeconomic,
dependants,
married,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarh_rec.ethnic,
c_ivstarh_rec.socialclass,
c_ivstarh_rec.pocceduchangedate,
c_ivstarh_rec.pocc,
c_ivstarh_rec.pocctext,
c_ivstarh_rec.lasteducation,
c_ivstarh_rec.educationleavedate,
c_ivstarh_rec.lea,
c_ivstarh_rec.socialeconomic,
NULL ,
NULL ,
'N',
NULL
) ;
SELECT appno
,timestamp
,RTRIM(ethnic) ethnic
FROM igs_uc_ivgstarh_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarh_2006_v ;
UPDATE igs_uc_istarh_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarh_ints ( appno,
ethnic,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstarh_rec.ethnic,
'N',
NULL
) ;
SELECT appno
,timestamp
,RTRIM(ethnic) ethnic
,numberdependants
,RTRIM(maritalstatus) maritalstatus
FROM igs_uc_ivnstarh_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarh_2006_v ;
UPDATE igs_uc_istarh_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarh_ints ( appno,
ethnic,
dependants,
married,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstarh_rec.ethnic,
c_ivnstarh_rec.numberdependants,
c_ivnstarh_rec.maritalstatus,
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,sentdate
,runsent
,codedchangedate
,school
,RTRIM(rescat) rescat
,feelevel
,feepayer
,RTRIM(feetext) feetext
,apr
,NULL lea -- not used in hercules
,countrybirth
,nationality
,dualnationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,RTRIM(routeb) routeb
,examchangedate
,NULL alevels -- not used in hercules
,NULL aslevels -- not used in hercules
,NULL highers -- not used in hercules
,NULL csys -- not used in hercules
,winter
,previousa
,RTRIM(btec) btec
,RTRIM(ilc) ilc
,RTRIM(aice) aice
,RTRIM(ib) ib
,RTRIM(manual) manual
,RTRIM(regno) regno
,RTRIM(oeq) oeq
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
,RTRIM(roa) roa
,specialneeds
,RTRIM(status) status
,firmnow
,firmreply
,insurancereply
,confhistfirmreply
,confhistinsurancereply
,gce
,vce
,RTRIM(sqa) sqa
,previousas
,RTRIM(keyskills) keyskills
,RTRIM(vocational) vocational
,NULL gnvq -- not used in hercules
,RTRIM(scn) scn
,RTRIM(prevoeq) prevoeq
,ukentrydate
,RTRIM(criminalconv) criminalconv
,RTRIM(choicesalltransparent) choicesalltransparent
,extrastatus
,RTRIM(extrapassportno) extrapassportno
FROM igs_uc_ivstark_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstark_2003_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
sentdate,
runsent,
codedchangedate,
school,
rescat,
feelevel,
feepayer,
feetext,
apr,
lea,
countrybirth,
nationality,
dualnationality,
withdrawn,
withdrawndate,
routeb,
examchangedate,
alevels,
aslevels,
highers,
csys,
gce,
vce,
sqa,
winter,
previousa,
previousas,
keyskills,
vocational,
gnvq,
btec,
ilc,
aice,
ib,
manual,
regno,
scn,
oeq,
prevoeq,
eas,
roa,
specialneeds,
criminalconv,
ukentrydate,
status,
firmnow,
firmreply,
insurancereply,
confhistfirmreply,
confhistinsurancereply,
choicesalltransparent,
extrastatus,
extrapassportno,
welshspeaker,
ninumber,
earlieststart,
nearinst,
prefreg,
qualeng,
qualmath,
qualsci,
mainqual,
qual5,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstark_rec.applicationdate,
c_ivstark_rec.sentdate,
c_ivstark_rec.runsent,
c_ivstark_rec.codedchangedate,
c_ivstark_rec.school,
c_ivstark_rec.rescat,
c_ivstark_rec.feelevel,
c_ivstark_rec.feepayer,
c_ivstark_rec.feetext,
c_ivstark_rec.apr,
c_ivstark_rec.lea,
c_ivstark_rec.countrybirth,
c_ivstark_rec.nationality,
c_ivstark_rec.dualnationality,
c_ivstark_rec.withdrawn,
c_ivstark_rec.withdrawndate,
c_ivstark_rec.routeb,
c_ivstark_rec.examchangedate,
c_ivstark_rec.alevels,
c_ivstark_rec.aslevels,
c_ivstark_rec.highers,
c_ivstark_rec.csys,
c_ivstark_rec.gce,
c_ivstark_rec.vce,
c_ivstark_rec.sqa,
c_ivstark_rec.winter,
c_ivstark_rec.previousa,
c_ivstark_rec.previousas,
c_ivstark_rec.keyskills,
c_ivstark_rec.vocational,
c_ivstark_rec.gnvq,
c_ivstark_rec.btec,
c_ivstark_rec.ilc,
c_ivstark_rec.aice,
c_ivstark_rec.ib,
c_ivstark_rec.manual,
c_ivstark_rec.regno,
c_ivstark_rec.scn,
c_ivstark_rec.oeq,
c_ivstark_rec.prevoeq,
NVL(c_ivstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
c_ivstark_rec.roa,
c_ivstark_rec.specialneeds,
c_ivstark_rec.criminalconv,
c_ivstark_rec.ukentrydate,
c_ivstark_rec.status,
c_ivstark_rec.firmnow,
c_ivstark_rec.firmreply,
c_ivstark_rec.insurancereply,
c_ivstark_rec.confhistfirmreply,
c_ivstark_rec.confhistinsurancereply,
c_ivstark_rec.choicesalltransparent,
c_ivstark_rec.extrastatus,
c_ivstark_rec.extrapassportno,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,sentdate
,runsent
,codedchangedate
,school
,RTRIM(rescat) rescat
,feelevel
,feepayer
,RTRIM(feetext) feetext
,apr
,NULL lea -- not used in hercules
,countrybirth
,nationality
,dualnationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,RTRIM(routeb) routeb
,examchangedate
,NULL alevels -- not used in hercules
,NULL aslevels -- not used in hercules
,NULL highers -- not used in hercules
,NULL csys -- not used in hercules
,winter
,previousa
,RTRIM(btec) btec
,RTRIM(ilc) ilc
,RTRIM(aice) aice
,RTRIM(ib) ib
,RTRIM(manual) manual
,RTRIM(regno) regno
,RTRIM(oeq) oeq
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
,RTRIM(roa) roa
,disability
,RTRIM(status) status
,firmnow
,firmreply
,insurancereply
,confhistfirmreply
,confhistinsurancereply
,gce
,vce
,RTRIM(sqa) sqa
,previousas
,RTRIM(keyskills) keyskills
,RTRIM(vocational) vocational
,NULL gnvq -- not used in hercules
,RTRIM(scn) scn
,RTRIM(prevoeq) prevoeq
,ukentrydate
,RTRIM(criminalconv) criminalconv
,RTRIM(choicesalltransparent) choicesalltransparent
,extrastatus
,RTRIM(extrapassportno) extrapassportno
FROM igs_uc_ivstark_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstark_2007_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
sentdate,
runsent,
codedchangedate,
school,
rescat,
feelevel,
feepayer,
feetext,
apr,
lea,
countrybirth,
nationality,
dualnationality,
withdrawn,
withdrawndate,
routeb,
examchangedate,
alevels,
aslevels,
highers,
csys,
gce,
vce,
sqa,
winter,
previousa,
previousas,
keyskills,
vocational,
gnvq,
btec,
ilc,
aice,
ib,
manual,
regno,
scn,
oeq,
prevoeq,
eas,
roa,
specialneeds,
criminalconv,
ukentrydate,
status,
firmnow,
firmreply,
insurancereply,
confhistfirmreply,
confhistinsurancereply,
choicesalltransparent,
extrastatus,
extrapassportno,
welshspeaker,
ninumber,
earlieststart,
nearinst,
prefreg,
qualeng,
qualmath,
qualsci,
mainqual,
qual5,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstark_rec.applicationdate,
c_ivstark_rec.sentdate,
c_ivstark_rec.runsent,
c_ivstark_rec.codedchangedate,
c_ivstark_rec.school,
c_ivstark_rec.rescat,
c_ivstark_rec.feelevel,
c_ivstark_rec.feepayer,
c_ivstark_rec.feetext,
c_ivstark_rec.apr,
c_ivstark_rec.lea,
c_ivstark_rec.countrybirth,
c_ivstark_rec.nationality,
c_ivstark_rec.dualnationality,
c_ivstark_rec.withdrawn,
c_ivstark_rec.withdrawndate,
c_ivstark_rec.routeb,
c_ivstark_rec.examchangedate,
c_ivstark_rec.alevels,
c_ivstark_rec.aslevels,
c_ivstark_rec.highers,
c_ivstark_rec.csys,
c_ivstark_rec.gce,
c_ivstark_rec.vce,
c_ivstark_rec.sqa,
c_ivstark_rec.winter,
c_ivstark_rec.previousa,
c_ivstark_rec.previousas,
c_ivstark_rec.keyskills,
c_ivstark_rec.vocational,
c_ivstark_rec.gnvq,
c_ivstark_rec.btec,
c_ivstark_rec.ilc,
c_ivstark_rec.aice,
c_ivstark_rec.ib,
c_ivstark_rec.manual,
c_ivstark_rec.regno,
c_ivstark_rec.scn,
c_ivstark_rec.oeq,
c_ivstark_rec.prevoeq,
NVL(c_ivstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
c_ivstark_rec.roa,
c_ivstark_rec.disability,
c_ivstark_rec.criminalconv,
c_ivstark_rec.ukentrydate,
c_ivstark_rec.status,
c_ivstark_rec.firmnow,
c_ivstark_rec.firmreply,
c_ivstark_rec.insurancereply,
c_ivstark_rec.confhistfirmreply,
c_ivstark_rec.confhistinsurancereply,
c_ivstark_rec.choicesalltransparent,
c_ivstark_rec.extrastatus,
c_ivstark_rec.extrapassportno,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,codedchangedate
,RTRIM(rescat) rescat
,feepayer
,apr
,countrybirth
,nationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
,specialneeds
,ukentrydate
,RTRIM(status) status
,firmnow
,extrastatus
FROM igs_uc_ivgstark_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstark_2006_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
rescat,
feepayer,
apr,
countrybirth,
nationality,
withdrawn,
withdrawndate,
eas,
specialneeds,
ukentrydate,
status,
firmnow,
extrastatus,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstark_rec.applicationdate,
c_ivgstark_rec.rescat,
c_ivgstark_rec.feepayer,
c_ivgstark_rec.apr,
c_ivgstark_rec.countrybirth,
c_ivgstark_rec.nationality,
c_ivgstark_rec.withdrawn,
c_ivgstark_rec.withdrawndate,
NVL(c_ivgstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
c_ivgstark_rec.specialneeds,
c_ivgstark_rec.ukentrydate,
c_ivgstark_rec.status,
c_ivgstark_rec.firmnow,
c_ivgstark_rec.extrastatus,
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,codedchangedate
,RTRIM(rescat) rescat
,feepayer
,apr
,countrybirth
,nationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
,disability
,ukentrydate
,RTRIM(status) status
,firmnow
,extrastatus
FROM igs_uc_ivgstark_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstark_2007_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
rescat,
feepayer,
apr,
countrybirth,
nationality,
withdrawn,
withdrawndate,
eas,
specialneeds,
ukentrydate,
status,
firmnow,
extrastatus,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstark_rec.applicationdate,
c_ivgstark_rec.rescat,
c_ivgstark_rec.feepayer,
c_ivgstark_rec.apr,
c_ivgstark_rec.countrybirth,
c_ivgstark_rec.nationality,
c_ivgstark_rec.withdrawn,
c_ivgstark_rec.withdrawndate,
NVL(c_ivgstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
c_ivgstark_rec.disability,
c_ivgstark_rec.ukentrydate,
c_ivgstark_rec.status,
c_ivgstark_rec.firmnow,
c_ivgstark_rec.extrastatus,
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,sentdate
,runsent
,codedchangedate
,RTRIM(rescat) rescat
,feelevel
,apr
,countrybirth
,nationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,qualenglish
,qualmaths
,qualscience
,qual5point
,qualmain
,nationalinsurance
,startdate
,nearestinst
,prefregion
,specialneeds
,RTRIM(status) status
,firmnow
,ukentrydate
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
FROM igs_uc_ivnstark_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstark_2006_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
sentdate,
runsent,
codedchangedate,
rescat,
feelevel,
apr,
countrybirth,
nationality,
withdrawn,
withdrawndate,
qualeng,
qualmath,
qualsci,
qual5,
mainqual,
ninumber,
earlieststart,
nearinst,
prefreg,
specialneeds,
status,
firmnow,
ukentrydate,
eas,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstark_rec.applicationdate,
c_ivnstark_rec.sentdate,
c_ivnstark_rec.runsent,
c_ivnstark_rec.codedchangedate,
c_ivnstark_rec.rescat,
c_ivnstark_rec.feelevel,
c_ivnstark_rec.apr,
c_ivnstark_rec.countrybirth,
c_ivnstark_rec.nationality,
c_ivnstark_rec.withdrawn,
c_ivnstark_rec.withdrawndate,
c_ivnstark_rec.qualenglish,
c_ivnstark_rec.qualmaths,
c_ivnstark_rec.qualscience,
c_ivnstark_rec.qual5point,
c_ivnstark_rec.qualmain,
c_ivnstark_rec.nationalinsurance,
TO_CHAR(l_startdate,'mmyy'),
c_ivnstark_rec.nearestinst,
c_ivnstark_rec.prefregion,
c_ivnstark_rec.specialneeds,
c_ivnstark_rec.status,
c_ivnstark_rec.firmnow,
c_ivnstark_rec.ukentrydate,
NVL(c_ivnstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
'N',
NULL
) ;
SELECT appno
,timestamp
,applicationdate
,sentdate
,runsent
,codedchangedate
,RTRIM(rescat) rescat
,feelevel
,apr
,countrybirth
,nationality
,RTRIM(withdrawn) withdrawn
,withdrawndate
,qualenglish
,qualmaths
,qualscience
,qual5point
,qualmain
,nationalinsurance
,startdate
,nearestinst
,prefregion
,disability
,RTRIM(status) status
,firmnow
,ukentrydate
,DECODE(RTRIM(eas),'Y','E',RTRIM(eas) ) eas
FROM igs_uc_ivnstark_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstark_2007_v ;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istark_ints ( appno,
applicationdate,
sentdate,
runsent,
codedchangedate,
rescat,
feelevel,
apr,
countrybirth,
nationality,
withdrawn,
withdrawndate,
qualeng,
qualmath,
qualsci,
qual5,
mainqual,
ninumber,
earlieststart,
nearinst,
prefreg,
specialneeds,
status,
firmnow,
ukentrydate,
eas,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstark_rec.applicationdate,
c_ivnstark_rec.sentdate,
c_ivnstark_rec.runsent,
c_ivnstark_rec.codedchangedate,
c_ivnstark_rec.rescat,
c_ivnstark_rec.feelevel,
c_ivnstark_rec.apr,
c_ivnstark_rec.countrybirth,
c_ivnstark_rec.nationality,
c_ivnstark_rec.withdrawn,
c_ivnstark_rec.withdrawndate,
c_ivnstark_rec.qualenglish,
c_ivnstark_rec.qualmaths,
c_ivnstark_rec.qualscience,
c_ivnstark_rec.qual5point,
c_ivnstark_rec.qualmain,
c_ivnstark_rec.nationalinsurance,
TO_CHAR(l_startdate,'mmyy'),
c_ivnstark_rec.nearestinst,
c_ivnstark_rec.prefregion,
c_ivnstark_rec.disability,
c_ivnstark_rec.status,
c_ivnstark_rec.firmnow,
c_ivnstark_rec.ukentrydate,
NVL(c_ivnstark_rec.eas,'P'), -- converting EAS field ,for bug#3087852
'N',
NULL
) ;
SELECT appno
,timestamp
,checkdigit
,namechangedate
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivstarn_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarn_2003_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
namechangedate,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivstarn_rec.namechangedate,
c_ivstarn_rec.title,
c_ivstarn_rec.forenames,
c_ivstarn_rec.surname,
c_ivstarn_rec.birthdate,
c_ivstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,RTRIM(personalid) personalid
,timestamp
,checkdigit
,namechangedate
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivstarn_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarn_2007_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
personalid,
namechangedate,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivstarn_rec.personalid,
c_ivstarn_rec.namechangedate,
c_ivstarn_rec.title,
c_ivstarn_rec.forenames,
c_ivstarn_rec.surname,
c_ivstarn_rec.birthdate,
c_ivstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,timestamp
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivgstarn_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarn_2006_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivgstarn_rec.title,
c_ivgstarn_rec.forenames,
c_ivgstarn_rec.surname,
c_ivgstarn_rec.birthdate,
c_ivgstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,personalid
,timestamp
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivgstarn_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarn_2007_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
personalid,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivgstarn_rec.personalid,
c_ivgstarn_rec.title,
c_ivgstarn_rec.forenames,
c_ivgstarn_rec.surname,
c_ivgstarn_rec.birthdate,
c_ivgstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,timestamp
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivnstarn_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarn_2006_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivnstarn_rec.title,
c_ivnstarn_rec.forenames,
c_ivnstarn_rec.surname,
c_ivnstarn_rec.birthdate,
c_ivnstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,personalid
,timestamp
,UPPER(RTRIM(title)) title
,RTRIM(forenames) forenames
,DECODE(RTRIM(surname),NULL, RPAD('*',LENGTH(surname),'*'), RTRIM(surname)) surname
,birthdate
,sex
FROM igs_uc_ivnstarn_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarn_2007_v ;
UPDATE igs_uc_istarn_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarn_ints ( appno,
checkdigit,
personalid,
title,
forenames,
surname,
birthdate,
sex,
ad_batch_id,
ad_interface_id,
ad_api_id,
record_status,
error_code
)
VALUES ( l_appno,
l_checkdigit,
c_ivnstarn_rec.personalid,
c_ivnstarn_rec.title,
c_ivnstarn_rec.forenames,
c_ivnstarn_rec.surname,
c_ivnstarn_rec.birthdate,
c_ivnstarn_rec.sex,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
FROM igs_uc_ivqualification_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT appno
,timestamp
,subjectid
,RTRIM(eblresult) eblresult
,RTRIM(eblamended) eblamended
,RTRIM(claimedresult) claimedresult
FROM igs_uc_ivstarpqr_2003_v
WHERE appno = cp_appno ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'O' , error_code = NULL
WHERE record_status = 'N' AND appno = l_appno_qual ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'D' , error_code = NULL
WHERE record_status = 'L' AND appno = l_appno_qual ;
INSERT INTO igs_uc_istrpqr_ints ( appno,
subjectid,
eblresult,
eblamended,
claimedresult,
yearofexam,
sitting,
examboard,
eblsubject,
grade,
grade1,
grade2,
lendingboard,
matchind,
marvin_type,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarpqr_rec.subjectid,
c_ivstarpqr_rec.eblresult,
c_ivstarpqr_rec.eblamended,
c_ivstarpqr_rec.claimedresult,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
FROM igs_uc_ivqualification_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT appno
,timestamp
,subjectid
,RTRIM(eblresult) eblresult
FROM igs_uc_ivstarpqr_2003_v
WHERE appno = cp_appno ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'O' , error_code = NULL
WHERE record_status = 'N' AND appno = l_appno_qual ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'D' , error_code = NULL
WHERE record_status = 'L' AND appno = l_appno_qual ;
INSERT INTO igs_uc_istrpqr_ints ( appno,
subjectid,
eblresult,
eblamended,
claimedresult,
yearofexam,
sitting,
examboard,
eblsubject,
grade,
grade1,
grade2,
lendingboard,
matchind,
marvin_type,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarpqr_rec.subjectid,
c_ivstarpqr_rec.eblresult,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
, RTRIM(remark) remark
, RTRIM(jointadmission) jointadmission
FROM igs_uc_ivstarw_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarw_2003_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivstarw_rec.appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
jointadmission,
choice1lost,
choice2lost,
choice3lost,
choice4lost,
choice5lost,
choice6lost,
choice7lost,
canceldate,
remark,
record_status,
error_code
)
VALUES ( c_ivstarw_rec.appno,
c_ivstarw_rec.miscoded,
c_ivstarw_rec.cancelled,
c_ivstarw_rec.jointadmission,
'N',
'N',
'N',
'N',
'N',
'N',
'N',
c_ivstarw_rec.canceldate,
c_ivstarw_rec.remark,
'N',
NULL
) ;
SELECT appno
,timestamp
,ethnic
,pocceduchangedate
,socialclass
,RTRIM(pocc) pocc
,RTRIM(pocctext) pocctext
,RTRIM(socioeconomic) socioeconomic
,RTRIM(occbackground) occbackground
FROM igs_uc_ivstarx_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarx_2003_v ;
UPDATE igs_uc_istarx_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarx_ints ( appno,
ethnic,
pocceduchangedate,
socialclass,
pocc,
pocctext,
socioeconomic,
occbackground,
religion,
dependants,
married,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarx_rec.ethnic,
c_ivstarx_rec.pocceduchangedate,
c_ivstarx_rec.socialclass,
c_ivstarx_rec.pocc,
c_ivstarx_rec.pocctext,
c_ivstarx_rec.socioeconomic,
c_ivstarx_rec.occbackground,
NULL,
NULL,
NULL,
'N',
NULL
) ;
SELECT appno
,timestamp
,ethnic
FROM igs_uc_ivgstarx_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarx_2006_v ;
UPDATE igs_uc_istarx_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarx_ints ( appno,
ethnic,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstarx_rec.ethnic,
'N',
NULL
) ;
SELECT appno
,timestamp
,ethnic
,numberdependants
,RTRIM(maritalstatus) maritalstatus
FROM igs_uc_ivnstarx_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarx_2006_v ;
UPDATE igs_uc_istarx_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarx_ints ( appno,
ethnic,
dependants,
married,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstarx_rec.ethnic,
c_ivnstarx_rec.numberdependants,
c_ivnstarx_rec.maritalstatus,
'N',
NULL
) ;
SELECT appno
,timestamp
,datecefsent
,cefno
,RTRIM(centralclearing) centralclearing
,DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,faculty
,SUBSTR(LPAD(entryyear,4,0),3,2) entryyear
,entrymonth
,RTRIM(entrypoint) entrypoint
,RTRIM(result) result
FROM igs_uc_ivstarz1_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarz1_2003_v ;
UPDATE igs_uc_istarz1_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarz1_ints ( appno,
datecefsent,
cefno,
centralclearing,
inst,
course,
campus,
faculty,
entryyear,
entrymonth,
entrypoint,
result,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarz1_rec.datecefsent,
c_ivstarz1_rec.cefno,
c_ivstarz1_rec.centralclearing,
c_ivstarz1_rec.inst,
c_ivstarz1_rec.course,
c_ivstarz1_rec.campus,
c_ivstarz1_rec.faculty,
c_ivstarz1_rec.entryyear,
c_ivstarz1_rec.entrymonth,
c_ivstarz1_rec.entrypoint,
c_ivstarz1_rec.result,
'N',
NULL
) ;
SELECT appno
,timestamp
,cefno
,DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
,DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
,NVL(DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)),'*') campus -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
,SUBSTR(LPAD(entryyear,4,0),3,2) entryyear
,entrymonth
,RTRIM(result) result
FROM igs_uc_ivnstarz1_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarz1_2006_v ;
UPDATE igs_uc_istarz1_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarz1_ints ( appno,
cefno,
inst,
course,
campus,
entryyear,
entrymonth,
result,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstarz1_rec.cefno,
c_ivnstarz1_rec.inst,
c_ivnstarz1_rec.course,
c_ivnstarz1_rec.campus,
c_ivnstarz1_rec.entryyear,
c_ivnstarz1_rec.entrymonth,
c_ivnstarz1_rec.result,
'N',
NULL
) ;
SELECT appno
, roundno
, timestamp
, DECODE(RTRIM(inst),NULL, RPAD('*',LENGTH(inst),'*'), RTRIM(inst)) inst
, DECODE(RTRIM(course),NULL, RPAD('*',LENGTH(course),'*'), RTRIM(course)) course
, DECODE(RTRIM(campus),NULL, RPAD('*',LENGTH(campus),'*'), RTRIM(campus)) campus
, RTRIM(faculty) faculty
, RTRIM(roundtype) roundtype
, RTRIM(result) result
FROM igs_uc_ivstarz2_2003_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarz2_2003_v ;
UPDATE igs_uc_istarz2_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivstarz2_rec.appno
AND inst = c_ivstarz2_rec.inst AND course = c_ivstarz2_rec.course
AND campus = c_ivstarz2_rec.campus;
INSERT INTO igs_uc_istarz2_ints ( appno,
roundno,
inst,
course,
campus,
faculty,
roundtype,
result,
record_status,
error_code
)
VALUES ( c_ivstarz2_rec.appno,
c_ivstarz2_rec.roundno,
c_ivstarz2_rec.inst,
c_ivstarz2_rec.course,
c_ivstarz2_rec.campus,
c_ivstarz2_rec.faculty,
c_ivstarz2_rec.roundtype,
c_ivstarz2_rec.result,
'N',
NULL
) ;
SELECT a.appno
FROM igs_uc_ivstatement_2003_v a
WHERE NOT EXISTS ( SELECT b.app_no
FROM IGS_UC_APPLICANTS b
WHERE b.app_no = a.appno
AND b.personal_statement IS NOT NULL );
SELECT appno,statement
FROM igs_uc_ivstatement_2003_v
WHERE appno = p_appno ;
UPDATE igs_uc_istmnt_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_stmt_appno_rec.appno;
INSERT INTO igs_uc_istmnt_ints ( appno,
statement,
record_status,
error_code
)
VALUES ( c_stmt_rec.appno,
c_stmt_rec.statement,
'N',
NULL
) ;
SELECT appno,statement
FROM igs_uc_ivstatement_2004_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstatement_2004_v ;
UPDATE igs_uc_istmnt_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istmnt_ints ( appno,
statement,
record_status,
error_code
)
VALUES ( l_appno,
c_stmt_rec.statement,
'N',
NULL
) ;
SELECT appno,statement
FROM igs_uc_ivgstatement_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstatement_2006_v ;
UPDATE igs_uc_istmnt_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istmnt_ints ( appno,
statement,
record_status,
error_code
)
VALUES ( l_appno,
c_stmt_rec.statement,
'N',
NULL
) ;
SELECT appno,statement
FROM igs_uc_ivnstatement_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstatement_2006_v ;
UPDATE igs_uc_istmnt_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno;
INSERT INTO igs_uc_istmnt_ints ( appno,
statement,
record_status,
error_code
)
VALUES ( l_appno,
c_stmt_rec.statement,
'N',
NULL
) ;
SELECT timestamp
,DECODE(RTRIM(updater),NULL, RPAD('*',LENGTH(updater),'*'), RTRIM(updater)) updater
,DECODE(RTRIM(insttype),NULL, RPAD('*',LENGTH(insttype),'*'), RTRIM(insttype)) insttype
,RTRIM(instshortname) instshortname
,RTRIM(instname) instname
,RTRIM(instfullname) instfullname
,RTRIM(switchboardtelno) switchboardtelno
,RTRIM(decisioncards) decisioncards
,RTRIM(recordcards) recordcards
,RTRIM(labels) labels
,RTRIM(weeklymovlistseq) weeklymovlistseq
,RTRIM(weeklymovpaging) weeklymovpaging
,RTRIM(formseq) formseq
,RTRIM(eblrequired) eblrequired
,RTRIM(eblmedia1or2) eblmedia1or2
,RTRIM(eblmedia3) eblmedia3
,RTRIM(ebl1or2merged) ebl1or2merged
,RTRIM(ebl1or2boardgroup) ebl1or2boardgroup
,RTRIM(ebl3boardgroup) ebl3boardgroup
,RTRIM(eblncapp) eblncapp
,RTRIM(eblmajorkey1) eblmajorkey1
,RTRIM(eblmajorkey2) eblmajorkey2
,RTRIM(eblmajorkey3) eblmajorkey3
,RTRIM(eblminorkey1) eblminorkey1
,RTRIM(eblminorkey2) eblminorkey2
,RTRIM(eblminorkey3) eblminorkey3
,RTRIM(eblfinalkey) eblfinalkey
,RTRIM(odl1) odl1
,RTRIM(odl1a) odl1a
,RTRIM(odl2) odl2
,RTRIM(odl3) odl3
,RTRIM(odlsummer) odlsummer
,RTRIM(odlrouteb) odlrouteb
,RTRIM(monthlyseq) monthlyseq
,RTRIM(monthlypaper) monthlypaper
,RTRIM(monthlypage) monthlypage
,RTRIM(monthlytype) monthlytype
,RTRIM(junelistseq) junelistseq
,RTRIM(junelabels) junelabels
,RTRIM(junenumlabels) junenumlabels
,RTRIM(courseanalysis) courseanalysis
,RTRIM(campusused) campusused
,RTRIM(d3docsrequired) d3docsrequired
,RTRIM(clearingacceptcopyform) clearingacceptcopyform
,RTRIM(onlinemessage) onlinemessage
,RTRIM(ethniclistseq) ethniclistseq
,NVL(RTRIM(starx),'N') starx
FROM igs_uc_u_uvinstitution_2004
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_u_uvinstitution_2004 ;
UPDATE igs_uc_uinst_ints SET record_status = 'O' WHERE record_status = 'N' ;
INSERT INTO igs_uc_uinst_ints ( updater,
insttype,
instshortname,
instname,
instfullname,
switchboardtelno,
decisioncards,
recordcards,
labels,
weeklymovlistseq,
weeklymovpaging,
formseq,
eblrequired,
eblmedia1or2,
eblmedia3,
ebl1or2merged,
ebl1or2boardgroup,
ebl3boardgroup,
eblncapp,
eblmajorkey1,
eblmajorkey2,
eblmajorkey3,
eblminorkey1,
eblminorkey2,
eblminorkey3,
eblfinalkey,
odl1,
odl1a,
odl2,
odl3,
odlsummer,
odlrouteb,
monthlyseq,
monthlypaper,
monthlypage,
monthlytype,
junelistseq,
junelabels,
junenumlabels,
courseanalysis,
campusused,
d3docsrequired,
clearingacceptcopyform,
onlinemessage,
ethniclistseq,
starx,
record_status,
error_code
)
VALUES ( c_uinst_rec.updater,
c_uinst_rec.insttype,
c_uinst_rec.instshortname,
c_uinst_rec.instname,
c_uinst_rec.instfullname,
c_uinst_rec.switchboardtelno,
c_uinst_rec.decisioncards,
c_uinst_rec.recordcards,
c_uinst_rec.labels,
c_uinst_rec.weeklymovlistseq,
c_uinst_rec.weeklymovpaging,
c_uinst_rec.formseq,
c_uinst_rec.eblrequired,
c_uinst_rec.eblmedia1or2,
c_uinst_rec.eblmedia3,
c_uinst_rec.ebl1or2merged,
c_uinst_rec.ebl1or2boardgroup,
c_uinst_rec.ebl3boardgroup,
c_uinst_rec.eblncapp,
c_uinst_rec.eblmajorkey1,
c_uinst_rec.eblmajorkey2,
c_uinst_rec.eblmajorkey3,
c_uinst_rec.eblminorkey1,
c_uinst_rec.eblminorkey2,
c_uinst_rec.eblminorkey3,
c_uinst_rec.eblfinalkey,
c_uinst_rec.odl1,
c_uinst_rec.odl1a,
c_uinst_rec.odl2,
c_uinst_rec.odl3,
c_uinst_rec.odlsummer,
c_uinst_rec.odlrouteb,
c_uinst_rec.monthlyseq,
c_uinst_rec.monthlypaper,
c_uinst_rec.monthlypage,
c_uinst_rec.monthlytype,
c_uinst_rec.junelistseq,
c_uinst_rec.junelabels,
c_uinst_rec.junenumlabels,
c_uinst_rec.courseanalysis,
c_uinst_rec.campusused,
c_uinst_rec.d3docsrequired,
c_uinst_rec.clearingacceptcopyform,
c_uinst_rec.onlinemessage,
c_uinst_rec.ethniclistseq,
c_uinst_rec.starx,
'N',
NULL) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
, RTRIM(remark) remark
, RTRIM(jointadmission) jointadmission
, NVL(choice1lost,'N') choice1lost
, NVL( choice2lost,'N') choice2lost
, NVL( choice3lost,'N') choice3lost
, NVL( choice4lost,'N') choice4lost
, NVL( choice5lost,'N') choice5lost
, NVL( choice6lost,'N') choice6lost
, NVL( choice7lost,'N') choice7lost
FROM igs_uc_ivstarw_2004_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivstarw_2004_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
jointadmission,
choice1lost,
choice2lost,
choice3lost,
choice4lost,
choice5lost,
choice6lost,
choice7lost,
canceldate,
remark,
record_status,
error_code
)
VALUES ( l_appno,
c_ivstarw_rec.miscoded,
c_ivstarw_rec.cancelled,
c_ivstarw_rec.jointadmission,
c_ivstarw_rec.choice1lost,
c_ivstarw_rec.choice2lost,
c_ivstarw_rec.choice3lost,
c_ivstarw_rec.choice4lost,
c_ivstarw_rec.choice5lost,
c_ivstarw_rec.choice6lost,
c_ivstarw_rec.choice7lost,
c_ivstarw_rec.canceldate,
c_ivstarw_rec.remark,
'N',
NULL
) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
FROM igs_uc_ivgstarw_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarw_2006_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
canceldate,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstarw_rec.miscoded,
c_ivgstarw_rec.cancelled,
c_ivgstarw_rec.canceldate,
'N',
NULL
) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
, remark
FROM igs_uc_ivgstarw_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgstarw_2007_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
canceldate,
remark,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgstarw_rec.miscoded,
c_ivgstarw_rec.cancelled,
c_ivgstarw_rec.canceldate,
c_ivgstarw_rec.remark,
'N',
NULL
) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
FROM igs_uc_ivnstarw_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarw_2006_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
canceldate,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstarw_rec.miscoded,
c_ivnstarw_rec.cancelled,
c_ivnstarw_rec.canceldate,
'N',
NULL
) ;
SELECT appno
, timestamp
, RTRIM(miscoded) miscoded
, RTRIM(cancelled) cancelled
, canceldate
, remark
FROM igs_uc_ivnstarw_2007_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnstarw_2007_v ;
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno ;
INSERT INTO igs_uc_istarw_ints ( appno,
miscoded,
cancelled,
canceldate,
remark,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnstarw_rec.miscoded,
c_ivnstarw_rec.cancelled,
c_ivnstarw_rec.canceldate,
c_ivnstarw_rec.remark,
'N',
NULL
) ;
SELECT appno
,timestamp
,qualid
,RTRIM(qualtype) qualtype
,RTRIM(awardbody) awardbody
,RTRIM(title) title
,RTRIM(grade) grade
,qualdate
FROM igs_uc_ivformquals_2004_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivformquals_2004_v ;
UPDATE igs_uc_ifrmqul_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND qualid = c_ivformqual_rec.qualid ;
INSERT INTO igs_uc_ifrmqul_ints ( appno,
qualid,
qualtype,
awardbody,
title,
grade,
qualdate,
record_status,
error_code
)
VALUES ( l_appno,
c_ivformqual_rec.qualid,
c_ivformqual_rec.qualtype,
c_ivformqual_rec.awardbody,
c_ivformqual_rec.title,
c_ivformqual_rec.grade,
c_ivformqual_rec.qualdate,
'N',
NULL
) ;
SELECT appno
,timestamp
,qualid
,RTRIM(SUBSTR(qualtype,1,30)) qualtype
,RTRIM(awardbody) awardbody
,RTRIM(title) title
,RTRIM(grade) grade
,qualdateyear
,qualdatemonth
FROM igs_uc_ivformquals_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivformquals_2006_v ;
UPDATE igs_uc_ifrmqul_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND qualid = c_ivformqual_rec.qualid ;
INSERT INTO igs_uc_ifrmqul_ints ( appno,
qualid,
qualtype,
awardbody,
title,
grade,
qualdate,
record_status,
error_code
)
VALUES ( l_appno,
c_ivformqual_rec.qualid,
c_ivformqual_rec.qualtype,
c_ivformqual_rec.awardbody,
c_ivformqual_rec.title,
c_ivformqual_rec.grade,
l_qualdate,
'N',
NULL
) ;
SELECT appno
,timestamp
,DECODE(RTRIM(refereename),NULL, RPAD('*',LENGTH(refereename),'*'), RTRIM(refereename)) refereename
,RTRIM(refereepost) refereepost
,RTRIM(estabname) estabname
,RTRIM(address1) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(telephone) telephone
,RTRIM(fax) fax
,RTRIM(email) email
,statement statement
FROM igs_uc_ivreference_2004_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivreference_2004_v ;
UPDATE igs_uc_irefrnc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = c_ivreference_rec.appno
AND refereename = c_ivreference_rec.refereename ;
INSERT INTO igs_uc_irefrnc_ints ( appno,
refereename,
refereepost,
estabname,
address1,
address2,
address3,
address4,
telephone,
fax,
email,
statement,
record_status,
error_code
)
VALUES ( c_ivreference_rec.appno,
c_ivreference_rec.refereename,
c_ivreference_rec.refereepost,
c_ivreference_rec.estabname,
c_ivreference_rec.address1,
c_ivreference_rec.address2,
c_ivreference_rec.address3,
c_ivreference_rec.address4,
c_ivreference_rec.telephone,
c_ivreference_rec.fax,
c_ivreference_rec.email,
c_ivreference_rec.statement,
'N',
NULL
) ;
SELECT appno
,timestamp
,DECODE(RTRIM(refereename),NULL, RPAD('*',LENGTH(refereename),'*'), RTRIM(refereename)) refereename
,RTRIM(refereepost) refereepost
,RTRIM(estabname) estabname
,RTRIM(address1) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(telephone) telephone
,RTRIM(fax) fax
,RTRIM(email) email
,statement statement
,RTRIM(predictedgrades) predictedgrades
FROM igs_uc_ivreference_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivreference_2006_v ;
UPDATE igs_uc_irefrnc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND refereename = c_ivreference_rec.refereename ;
INSERT INTO igs_uc_irefrnc_ints ( appno,
refereename,
refereepost,
estabname,
address1,
address2,
address3,
address4,
telephone,
fax,
email,
statement,
predictedgrades,
record_status,
error_code
)
VALUES ( l_appno,
c_ivreference_rec.refereename,
c_ivreference_rec.refereepost,
c_ivreference_rec.estabname,
c_ivreference_rec.address1,
c_ivreference_rec.address2,
c_ivreference_rec.address3,
c_ivreference_rec.address4,
c_ivreference_rec.telephone,
c_ivreference_rec.fax,
c_ivreference_rec.email,
c_ivreference_rec.statement,
c_ivreference_rec.predictedgrades,
'N',
NULL
) ;
SELECT appno
,timestamp
,DECODE(RTRIM(refereename),NULL, RPAD('*',LENGTH(refereename),'*'), RTRIM(refereename)) refereename
,RTRIM(refereepost) refereepost
,RTRIM(estabname) estabname
,RTRIM(address1) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(telephone) telephone
,RTRIM(fax) fax
,RTRIM(email) email
,statement statement
FROM igs_uc_ivgreference_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivgreference_2006_v ;
UPDATE igs_uc_irefrnc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND refereename = c_ivgreference_rec.refereename ;
INSERT INTO igs_uc_irefrnc_ints ( appno,
refereename,
refereepost,
estabname,
address1,
address2,
address3,
address4,
telephone,
fax,
email,
statement,
record_status,
error_code
)
VALUES ( l_appno,
c_ivgreference_rec.refereename,
c_ivgreference_rec.refereepost,
c_ivgreference_rec.estabname,
c_ivgreference_rec.address1,
c_ivgreference_rec.address2,
c_ivgreference_rec.address3,
c_ivgreference_rec.address4,
c_ivgreference_rec.telephone,
c_ivgreference_rec.fax,
c_ivgreference_rec.email,
c_ivgreference_rec.statement,
'N',
NULL
) ;
SELECT appno
,timestamp
,DECODE(RTRIM(refereename),NULL, RPAD('*',LENGTH(refereename),'*'), RTRIM(refereename)) refereename
,RTRIM(refereepost) refereepost
,RTRIM(estabname) estabname
,RTRIM(address1) address1
,RTRIM(address2) address2
,RTRIM(address3) address3
,RTRIM(address4) address4
,RTRIM(telephone) telephone
,RTRIM(fax) fax
,RTRIM(email) email
,statement statement
FROM igs_uc_ivnreference_2006_v
WHERE ( timestamp > p_timestamp OR p_timestamp IS NULL ) ;
SELECT MAX(timestamp)
FROM igs_uc_ivnreference_2006_v ;
UPDATE igs_uc_irefrnc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = l_appno
AND refereename = c_ivnreference_rec.refereename ;
INSERT INTO igs_uc_irefrnc_ints ( appno,
refereename,
refereepost,
estabname,
address1,
address2,
address3,
address4,
telephone,
fax,
email,
statement,
record_status,
error_code
)
VALUES ( l_appno,
c_ivnreference_rec.refereename,
c_ivnreference_rec.refereepost,
c_ivnreference_rec.estabname,
c_ivnreference_rec.address1,
c_ivnreference_rec.address2,
c_ivnreference_rec.address3,
c_ivnreference_rec.address4,
c_ivnreference_rec.telephone,
c_ivnreference_rec.fax,
c_ivnreference_rec.email,
c_ivnreference_rec.statement,
'N',
NULL
) ;
load_ivstara_2006 ; -- updated for 2006