[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update IGS_UC_TRANSACTIONS table directly as |
| part of UCFD02_Small_Systems Enh. Bug# 2643048 |
| Also added validation for ensuring that files |
are processed in seqeuence only. |
| rgangara 27-Nov-02 Removed TRIM for CAMPUS fields in *C and *G |
| Added *G, *T in Checkdigit validation. |
| rgangara 28-Nov-02 Fixed review comments. |
| Birthdate in (*N, *K) is sent as DDMMYY format |
| from UCAS. |
| rbezawad 02-Dec-02 Removed the TO_NUMBER() conversion while |
| importing data into IGS_UC_MV_IVSTARK.SPECIALNEEDS |
| column. This is done w.r.t. Bug 2620166 as |
| there is change in Hercules data model. |
| ayedubat 12-Dec-02 Changed the transfer_to_stara procedure for |
| bug:2702489 |
| rbezawad 17-Dec-02 Modified the transfer_ack_to_trans procedure to |
| remove the code which is loggig message |
| IGS_UC_TRAN_PROC_APPCH for 2nd time. Bug 2711183. |
| smaddali 29-jan-03 Modified procedure transfer_to_ivstarpqr ,for |
| UCCR005 build ,bug # 2749404. |
| rbezawad 25-Feb-03 Modified procedure transfer_to_starpqr() w.r.t. Bug |
| 2810932 for processing Previous results of an |
| applicant upto maximum 21 sets. |
| rbezawad 06-Mar-03 Corrected the code to properly display the count of |
| successful records w.r.t Bug 2810665. |
| pmarada 11-Jun-03 Added ucas_cycle to uc_transaction table, as per |
| UCFD203-Multiple cycles build, bug 2669208 |
| smaddali 30-Jun-03 Modified for Bug#2669208 , UCFD203 -multiple cycles |
| dsridhar 25-Jul-03 Bug No: 3022067, part of change request for UCAS |
| Application Calendar Mapping. Removed references to |
| calendar fields in igs_uc_cyc_defaults_pkg. |
| ayedubat 30-Jul-03 Changed the procedure,transfer_to_starw to replace |
| the column names substchoice1, substchoice2, |
| substchoice3, substchoice4, substchoice5, |
| substchoice6 and substchoice7 with choice1lost, |
| choice2lost, choice3lost, choice4lost, choice5lost, |
| choice6lost, choice7lost of igs_uc_istarw_ints for |
| bug, 2669208. |
| smaddali 07-Aug-03 Modified procedure logic for updating *N INTS record |
| in *K and *N transaction processing procedures for |
| bug 3085770 |
| smaddali 26-Aug-03 Modified procedure transfer_to_starpqr ,population of|
| field Grade , as part of bug#3114629 |
| smaddali 04-Sep-03 Modified procedure transfer_to_starpqr , bug#3122898 |
| rbezawad 13-Oct-03 Modified for ucfd209- Substitution Support build |
| bug#2669228. |
| jchakrab 27-Jul-04 Modified for UCFD308-UCAS 2005 Regulatory Changes |
| jbaber 12-Jul-05 Modified for UC315 - UCAS Support 2006 |
| jbaber 23-Aug-05 Modified for UC307 - HERCULES Small Systems Support |
| anwest 18-Jan-06 Bug# 4950285 R12 Disable OSS Mandate |
| anwest 29-May-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES |
| jbaber 12-Jul-06 Modified for UC325 - UCAS Support 2007 |
*==============================================================================*/
-- Declare all Global variables and global constants
g_record_cnt NUMBER;
SELECT MAX(configured_cycle) configured_cycle, MAX(current_cycle) current_cycle
FROM igs_uc_defaults ;
SELECT entry_year
FROM igs_uc_ucas_control
WHERE system_code = 'U'
AND ucas_cycle = g_c_cycles.configured_cycle;
SELECT file_type
FROM igs_uc_load_mv_t
WHERE marvin_id = p_marvin_id - 1;
SELECT a.rowid , a.* , b.name
FROM igs_uc_cyc_defaults a , igs_uc_defaults b
WHERE a.system_code = b.system_code
AND a.system_code = cp_syscode
AND a.ucas_cycle = g_c_cycles.configured_cycle ;
igs_uc_cyc_defaults_pkg.update_row (
x_rowid => cyc_defaults_rec.rowid,
x_system_code => cyc_defaults_rec.system_code,
x_ucas_cycle => cyc_defaults_rec.ucas_cycle,
x_ucas_interface => cyc_defaults_rec.ucas_interface,
x_marvin_seq => p_curr_seq_num,
x_clearing_flag => cyc_defaults_rec.clearing_flag,
x_extra_flag => cyc_defaults_rec.extra_flag,
x_cvname_flag => cyc_defaults_rec.cvname_flag,
x_mode => 'R'
);
SELECT marvin_id, record_data
FROM igs_uc_load_mv_t
WHERE trans_type = 'AE' ;
SELECT file_type
FROM igs_uc_load_mv_t
WHERE marvin_id = cp_marvin_id - 1;
SELECT a.rowid , a.* , b.name
FROM igs_uc_cyc_defaults a , igs_uc_defaults b
WHERE a.system_code = b.system_code
AND a.system_code = cp_syscode
AND a.ucas_cycle = g_c_cycles.configured_cycle ;
|| Purpose : Inserts the given *A transaction record into igs_uc_mv_ivstara table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| ayedubat 12-DEC-2002 Passed the SYSDATE for TIMESTAMP column for bug fix:2702489
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstara with igs_uc_istara_ints
|| (reverse chronological order - newest change first)
*/
ln_appno igs_uc_istara_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istara_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_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
(
ln_appno, -- APPNO,
TRIM(SUBSTR(p_record_data,17,1)), -- ADDRESSAREA,
TRIM(SUBSTR(p_record_data,18,27)), -- ADDRESS1,
TRIM(SUBSTR(p_record_data,45,27)), -- ADDRESS2,
TRIM(SUBSTR(p_record_data,72,27)), -- ADDRESS3,
TRIM(SUBSTR(p_record_data,99,27)), -- ADDRESS4,
TRIM(SUBSTR(p_record_data,126,8)), -- POSTCODE,
TRIM(SUBSTR(p_record_data,134,5)), -- MAILSORT,
TRIM(SUBSTR(p_record_data,139,20)), -- TELEPHONE,
NULL, -- FAX
NULL, -- EMAIL,
NULL, -- HOMEADDRESS1,
NULL, -- HOMEADDRESS2,
NULL, -- HOMEADDRESS3,
NULL, -- HOMEADDRESS4,
NULL, -- HOMEPOSTCODE,
NULL, -- HOMEPHONE,
NULL, -- HOMEFAX
NULL, -- HOMEEMAIL
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE,
);
|| Purpose : Inserts the given *C transaction record into igs_uc_mv_ivstarc table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
||
|| rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstarc.EXTRAROUND column value from 58-60 column positions.
|| Modified w.r.t. UCFD06 Build 2574566.
|| rgangara 11-Nov-02 Added logic to insert into IVSTARC extension table to hold
|| additional *C data for small systems support. Bug 2643048.
|| Changed the Inst field positions from 18,4 to 18,3 after discussing with Martin
|| as the table has it as 3 chars and was erroring out.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarc with igs_uc_istarc_ints which
|| includes columns of igs_uc_ss_ivstarc table also
|| rbezawad 13-Oct-03 Modified for ucfd209- Substitution Support build , bug#2669228
*/
l_char_choice VARCHAR2(1) := TRIM(SUBSTR(p_record_data,17,1));
SELECT w.ROWID
FROM igs_uc_istarw_ints w
WHERE w.appno = cp_appno
AND w.record_status = 'N';
l_sql_stmt := 'UPDATE igs_uc_istarw_ints SET choice'||l_num_choice||'lost = ''Y'' WHERE ROWID = :1';
INSERT INTO igs_uc_istarw_ints(
appno,
miscoded,
cancelled,
canceldate,
remark,
jointadmission,
choice1lost,
choice2lost,
choice3lost,
choice4lost,
choice5lost,
choice6lost,
choice7lost,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
'N', -- MISCODED,
'N', -- CANCELLED,
NULL, -- CANCELDATE,
NULL, -- REMARK,
'N', -- JOINTADMISSION
DECODE(l_num_choice,1,'Y','N'), -- CHOICE1LOST
DECODE(l_num_choice,2,'Y','N'), -- CHOICE2LOST
DECODE(l_num_choice,3,'Y','N'), -- CHOICE3LOST
DECODE(l_num_choice,4,'Y','N'), -- CHOICE4LOST
DECODE(l_num_choice,5,'Y','N'), -- CHOICE5LOST
DECODE(l_num_choice,6,'Y','N'), -- CHOICE6LOST
DECODE(l_num_choice,7,'Y','N'), -- CHOICE7LOST
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
UPDATE igs_uc_istarc_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno
AND choiceno = l_num_choice AND ucas_cycle= g_c_cycles.configured_cycle;
INSERT INTO igs_uc_istarc_ints(
appno,
choiceno,
ucas_cycle,
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
)
VALUES
(
ln_appno, -- APPNO,
l_num_choice, -- CHOICENO,
g_c_cycles.configured_cycle, -- UCAS_CYCLE,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- LASTCHANGE,
TRIM(SUBSTR(p_record_data,18,3)), -- INST,
TRIM(SUBSTR(p_record_data,22,6)), -- COURSE,
DECODE(RTRIM(SUBSTR(p_record_data,28,1)),NULL,
'*',SUBSTR(p_record_data,28,1)) , -- CAMPUS,
TRIM(SUBSTR(p_record_data,29,1)), -- FACULTY,
TRIM(SUBSTR(p_record_data,54,1)), -- HOME,
TRIM(SUBSTR(p_record_data,30,1)), -- DECISION,
NULL, -- DECISIONDATE,
NULL, -- DECISIONNUMBER,
TRIM(SUBSTR(p_record_data,31,1)), -- REPLY,
TRIM(SUBSTR(p_record_data,32,6)), -- SUMMARYCONDITIONS,
TO_NUMBER(TRIM(SUBSTR(p_record_data,40,2))), -- ENTRYMONTH,
TO_NUMBER(TRIM(SUBSTR(p_record_data,38,2))), -- ENTRYYEAR,
TRIM(SUBSTR(p_record_data,55,1)), -- ENTRYPOINT,
DECODE(TRIM(SUBSTR(p_record_data,42,1)),'C','Y','N'),-- CHOICECANCELLED,
TRIM(SUBSTR(p_record_data,43,1)), -- ACTION,
TRIM(SUBSTR(p_record_data,44,1)), -- SUBSTITUTION,
NULL, -- DATESUBSTITUTED,
NULL, -- PREVIOUSINST,
TRIM(SUBSTR(p_record_data,46,6)), -- PREVIOUSCOURSE,
NULL, -- PREVIOUSCAMPUS,
TRIM(SUBSTR(p_record_data,45,1)), -- UCASAMENDMENT,
TO_NUMBER(TRIM(SUBSTR(p_record_data,56,1))), -- ROUTEBPREF,
NULL, -- ROUTEBROUND,
NULL, -- DETAIL
TO_NUMBER(TRIM(SUBSTR(p_record_data,58,3))), -- EXTRAROUND,
DECODE(TRIM(SUBSTR(p_record_data,57,1)),'R','Y','N'),-- RESIDENTIAL,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *G (GTTR Referral Details) transaction data into igs_uc_mv_ivstarG table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarg with igs_uc_istarg_ints
|| (reverse chronological order - newest change first)
||
*/
l_char_choice VARCHAR2(1) := TRIM(SUBSTR(p_record_data,17,1));
UPDATE igs_uc_istarg_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno
AND roundno = l_num_choice ;
INSERT INTO igs_uc_istarg_ints(
appno ,
roundno ,
ucas_cycle ,
lastchange ,
inst ,
course ,
campus ,
parttime ,
decision ,
reply ,
entryyear ,
entrymonth ,
action ,
interview ,
lateapplication ,
modular ,
confirmed ,
gcseeng ,
gcsemath ,
degreesubject ,
degreestatus ,
degreeclass ,
gcsesci ,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
l_num_choice, -- ROUNDNO
g_c_cycles.configured_cycle, -- UCAS_CYCLE,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)),'DDMMRR'), -- LASTCHANGE
TRIM(SUBSTR(p_record_data,18,3)), -- INST Though as per Manual 4 chars, take it as 3 since our table has 3
TRIM(SUBSTR(p_record_data,22,6)), -- COURSE
DECODE(RTRIM(SUBSTR(p_record_data,28,1)),NULL,
'*',SUBSTR(p_record_data,28,1) ) , -- CAMPUS
DECODE(TRIM(SUBSTR(p_record_data,30,1)),'P','Y','N'),-- PARTTIME
TRIM(SUBSTR(p_record_data,31,1)), -- DECISION
TRIM(SUBSTR(p_record_data,32,1)), -- REPLY
TO_NUMBER(TRIM(SUBSTR(p_record_data,33,2))), -- ENTRYYEAR
TO_NUMBER(TRIM(SUBSTR(p_record_data,53,2))), -- ENTRYMONTH
TRIM(SUBSTR(p_record_data,35,1)), -- ACTION
TO_DATE(TRIM(SUBSTR(p_record_data,44,6)), 'DDMMRR'), -- INTERVIEW
DECODE(TRIM(SUBSTR(p_record_data,51,1)),'L','Y','N'),-- LATEAPPLICATION
DECODE(TRIM(SUBSTR(p_record_data,52,1)),'M','Y','N'),-- MODULAR
TRIM(SUBSTR(p_record_data,36,1)), -- CONFIRMED
TRIM(SUBSTR(p_record_data,37,1)), -- GCSE_ENG
TRIM(SUBSTR(p_record_data,38,1)), -- GCSE_MATH
TRIM(SUBSTR(p_record_data,40,2)), -- DEGREE_SUBJECT
TRIM(SUBSTR(p_record_data,39,1)), -- DEGREE_STATUS
TRIM(SUBSTR(p_record_data,42,2)), -- DEGREE_CLASS
TRIM(SUBSTR(p_record_data,50,1)), -- GCSE_SCI
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *H transaction record into igs_uc_mv_ivstarh table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rgangara 11-Nov-02 Added logic to insert into IVSTARH extension table to hold
|| additional *H data for small systems support. Bug 2643048
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarh with igs_uc_istarh_ints which
|| includes columns of igs_uc_ss_ivstarh table also
*/
ln_appno igs_uc_istarh_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istarh_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno ;
INSERT INTO igs_uc_istarh_ints(
appno,
ethnic,
socialclass,
pocceduchangedate,
pocc,
pocctext,
lasteducation,
educationleavedate,
lea,
socialeconomic,
dependants,
married,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
TO_NUMBER(TRIM(SUBSTR(p_record_data,17,2))), -- ETHNIC,
TRIM(SUBSTR(p_record_data,19,1)), -- SOCIALCLASS,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- POCCEDUCHANGEDATE,
TRIM(SUBSTR(p_record_data,39,4)), -- POCC, -- 21-Nov-02 changed from 39,3 to 39,4 as it was wrong earlier.
NULL, -- POCCTEXT,
TO_NUMBER(TRIM(SUBSTR(p_record_data,23,7))), -- LASTEDUCATION,
TO_NUMBER(TRIM(SUBSTR(p_record_data,30,2))), -- EDUCATIONLEAVEDATE,
NULL, -- LEA,
TO_NUMBER(TRIM(SUBSTR(p_record_data,35,1))), -- SOCIALECONOMIC,
TO_NUMBER(TRIM(SUBSTR(p_record_data,36,2))), -- DEPENDANTS,
TRIM(SUBSTR(p_record_data,38,1)), -- MARRIED,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *K transactionrecord into igs_uc_mv_ivstark table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
||
|| rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstark.CHOICESALLTRANSPARENT column value from 120 column position and
|| EXTRASTATUS, EXTRAPASSPORTNO columns with NULL values. Modified w.r.t. UCFD06 Build 2574566.
|| rgangara 11-Nov-02 Added logic to insert into IVSTARK extension table to hold
|| additional *K data for small systems support. Bug 2643048
|| rbezawad 02-Dec-2002 Removed the TO_NUMBER() conversion while importing data into IGS_UC_MV_IVSTARK.SPECIALNEEDS column.
|| This is done w.r.t. Bug 2620166 as there is change in Hercules data model.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstark with igs_uc_istark_ints which
|| includes columns of igs_uc_ss_ivstark table also
|| smaddali 7-aug-03 Modified procedure logic for updating *N INTS record for bug 3085770
*/
l_scn igs_uc_istark_ints.scn%TYPE := NULL;
SELECT a.rowid ,a.namechangedate , a.title , a.forenames, a.surname
FROM igs_uc_istarn_ints a
WHERE record_status = 'N'
AND appno = cp_appno ;
SELECT name_change_date , title , fore_names , surname
FROM igs_uc_app_names
WHERE app_no = cp_appno ;
SELECT record_data
FROM igs_uc_load_mv_t
WHERE trans_type = '*N'
AND TO_NUMBER(TRIM(SUBSTR(record_data,1,8))) = cp_appno;
UPDATE igs_uc_istark_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_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
(
ln_appno, -- APPNO,
TO_DATE(TRIM(SUBSTR(p_record_data,24,6)), 'DDMMRR'), -- APPLICATIONDATE,
NULL, -- SENTDATE,
TO_NUMBER(TRIM(SUBSTR(p_record_data,30,3))), -- RUNSENT,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- CODEDCHANGEDATE,
TO_NUMBER(TRIM(SUBSTR(p_record_data,39,5))), -- SCHOOL,
TRIM(SUBSTR(p_record_data,45,1)), -- RESCAT,
NULL, -- FEELEVEL,
TO_NUMBER(TRIM(SUBSTR(p_record_data,56,2))), -- FEEPAYER,
NULL, -- FEETEXT,
TO_NUMBER(TRIM(SUBSTR(p_record_data,47,3))), -- APR,
NULL, -- LEA
TO_NUMBER(TRIM(SUBSTR(p_record_data,50,3))), -- COUNTRYBIRTH,
TO_NUMBER(TRIM(SUBSTR(p_record_data,53,3))), -- NATIONALITY,
NULL, -- DUALNATIONALITY,
TRIM(SUBSTR(p_record_data,17,1)), -- WITHDRAWN,
TO_DATE(TRIM(SUBSTR(p_record_data,18,6)), 'DDMMRR'), -- WITHDRAWNDATE,
DECODE(TRIM(SUBSTR(p_record_data,80,1)),'B','Y','N'), -- ROUTEB,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- EXAMCHANGEDATE,
NULL, -- ALEVELS,
NULL, -- ASLEVELS,
NULL, -- HIGHERS,
NULL, -- CSYS,
TO_NUMBER(TRIM(SUBSTR(p_record_data,58,1))), -- GCE,
TO_NUMBER(TRIM(SUBSTR(p_record_data,59,1))), -- VCE,
TRIM(SUBSTR(p_record_data,61,1)), -- SQA,
TO_NUMBER(TRIM(SUBSTR(p_record_data,65,1))), -- WINTER,
TO_NUMBER(TRIM(SUBSTR(p_record_data,66,1))), -- PREVIOUSA,
TO_NUMBER(TRIM(SUBSTR(p_record_data,119,1))), -- PREVIOUSAS,
NULL, -- KEYSKILLS,
NULL, -- VOCATIONAL,
NULL, -- GNVQ
DECODE(TRIM(SUBSTR(p_record_data,62,1)),'B','Y','N'), -- BTEC,
DECODE(TRIM(SUBSTR(p_record_data,64,1)),'I','Y','N'), -- ILC,
NULL, -- AICE,
DECODE(TRIM(SUBSTR(p_record_data,63,1)),'I','Y',NULL), -- IB,
NULL, -- MANUAL,
l_regno, -- REGNO,
l_scn, -- SCN,
TRIM(SUBSTR(p_record_data,79,1)), -- OEQ,
NULL, -- PREVOEQ,
NVL(TRIM(SUBSTR(p_record_data,105,1)),'P'), -- EAS,
TRIM(SUBSTR(p_record_data,68,1)), -- ROA,
TRIM(SUBSTR(p_record_data,46,1)), -- SPECIALNEEDS,
NULL, -- CRIMINALCONV,
NULL, -- UKENTRYDATE,
NULL, -- STATUS,
NULL, -- FIRMNOW,
NULL, -- FIRMREPLY,
NULL, -- INSURANCEREPLY,
NULL, -- CONFHISTFIRMREPLY,
NULL, -- CONFHISTINSURANCEREPLY,
DECODE(TRIM(SUBSTR(p_record_data,120,1)),'Y','Y','N'), -- CHOICESALLTRANSPARENT,
NULL, -- EXTRASTATUS,
NULL, -- EXTRAPASSPORTNO,
TRIM(SUBSTR(p_record_data,81,1)), -- WELSHSPEAKER
TRIM(SUBSTR(p_record_data,82,9)), -- NINUMBER
TRIM(SUBSTR(p_record_data,91,4)), -- EARLIESTSTART
TRIM(SUBSTR(p_record_data,95,4)), -- NEARINST
TO_NUMBER(TRIM(SUBSTR(p_record_data,99,1))), -- PREFREG
TRIM(SUBSTR(p_record_data,100,1)), -- QUALENG
TRIM(SUBSTR(p_record_data,101,1)), -- QUALMATH
TRIM(SUBSTR(p_record_data,102,1)), -- QUALSCI
TRIM(SUBSTR(p_record_data,103,1)), -- MAINQUAL
TRIM(SUBSTR(p_record_data,104,1)), -- QUAL5
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
UPDATE igs_uc_istarn_ints SET
namechangedate = NVL(l_namechangedate,namechangedate),
title = NVL(l_title,title),
forenames = NVL(l_forenames,forenames),
surname = NVL(l_surname,surname) ,
birthdate = TO_DATE(TRIM(SUBSTR(p_record_data,33,6)), 'DDMMRR') ,
sex = TRIM(SUBSTR(p_record_data,44,1)),
ad_batch_id = NULL ,
ad_interface_id = NULL ,
ad_api_id = NULL ,
error_code = NULL
WHERE rowid = c_starn_int_rec.rowid ;
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
(
ln_appno, -- APPNO,
TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))), -- CHECKDIGIT,
l_namechangedate, -- NAMECHANGEDATE,
l_title, -- TITLE,
l_forenames, -- FORENAMES,
l_surname, -- SURNAME,
TO_DATE(TRIM(SUBSTR(p_record_data,33,6)), 'DDMMRR'), -- BIRTHDATE,
TRIM(SUBSTR(p_record_data,44,1)), -- SEX,
NULL, -- AD_BATCH_ID
NULL, -- AD_INTERFACE_ID
NULL, -- AD_API_ID
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *N transaction record into igs_uc_mv_ivstarn table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rgangara 28 Nov 02 For Y2K problem found during testing.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarn with igs_uc_istarn_ints
|| smaddali 7-aug-03 Modified procedure logic for updating *N INTS record for bug 3085770
*/
/* rgangara 28-Nov-02
Modified by rgangara to overcome Y2K issue for birthdate. The date of birth data that comes in from UCAS
is in DDMMYY format. As such when this is converted and populated into the table, it is saved as DDMMYYYY.
This was causing a problem. For ex a date of birth of say 01-Jan-72 would be 010172 in the flat file coming from
UCAS since it is in DDMMYY format. However, when this is stored in a table and queried, it would 01-Jan-2072
which would through up errors. Hence since it is assumed that the Applicants in UCAS would have birthdates in
19's and not beyond 2000, the code here has been modified to do a proper conversion by changing format mask as 'DDMMRR' instead of 'DDMMYY'.
*/
ln_appno igs_uc_istarn_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
SELECT record_data
FROM igs_uc_load_mv_t
WHERE trans_type = '*K'
AND TO_NUMBER(TRIM(SUBSTR(record_data,1,8))) = cp_appno;
SELECT a.rowid , a.sex, a.birthdate
FROM igs_uc_istarn_ints a
WHERE record_status = 'N'
AND appno = cp_appno ;
SELECT birth_date , sex
FROM igs_uc_app_names
WHERE app_no = cp_appno ;
UPDATE igs_uc_istarn_ints SET
checkdigit = TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))) ,
namechangedate = TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR') ,
title = UPPER(TRIM(SUBSTR(p_record_data,17,4))) ,
forenames = TRIM(SUBSTR(p_record_data,42,24)) ,
surname = TRIM(SUBSTR(p_record_data,24,18)) ,
birthdate = NVL(l_birthdate,birthdate),
sex = NVL(l_sex,sex) ,
ad_batch_id = NULL ,
ad_interface_id = NULL ,
ad_api_id = NULL ,
error_code = NULL
WHERE rowid = c_starn_int_rec.rowid ;
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
(
ln_appno, -- APPNO,
TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))), -- CHECKDIGIT,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- NAMECHANGEDATE,
UPPER(TRIM(SUBSTR(p_record_data,17,4))), -- TITLE,
TRIM(SUBSTR(p_record_data,42,24)), -- FORENAMES,
TRIM(SUBSTR(p_record_data,24,18)), -- SURNAME,
l_birthdate, -- BIRTHDATE,
l_sex, -- SEX,
NULL, -- AD_BATCH_ID
NULL, -- AD_INTERFACE_ID
NULL, -- AD_API_ID
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *P *R transactions record into igs_uc_mv_ivstarpqr and igs_uc_mv_ivqual tables
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rbezawad 25-Sep-2002 Removed code which is populating IGS_UC_MV_IVQUAL.GNVQDATE as the column is obsoleted.
|| smaddali 29-jan-03 Enhanced processing for *P and *R transactions to populate subject_id and ebl_result as part of build UCCR005 , bug #2749404
|| rbezawad 25-Feb-03 Modified procedure transfer_to_starpqr for processing Previous results of an applicant upto maximum 21 sets w.r.t. Bug 2810932.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarpqr with igs_uc_istrpqr_ints
|| smaddali 26-aug-03 Modified procedure for *P transaction , to populate grade1 and 2 fields instead of Grade field, for bug#3114629
|| smaddali 4-sep-03 Obsoleting existing 'N' records for bug#3122898
*/
ln_appno igs_uc_istrpqr_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istrpqr_ints SET record_status = 'D'
WHERE record_status = 'L' AND appno = ln_appno
AND marvin_type = SUBSTR(p_trans_type,2,1) ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'O' , error_code = NULL
WHERE record_status = 'N' AND appno = ln_appno
AND marvin_type = SUBSTR(p_trans_type,2,1) ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'N'
WHERE record_status = 'L' AND appno = ln_appno
AND marvin_type = 'P' ;
UPDATE igs_uc_istrpqr_ints SET record_status = 'N'
WHERE record_status = 'L' AND appno = ln_appno
AND marvin_type = 'R';
UPDATE igs_uc_istrpqr_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno
AND yearofexam = l_year_date AND sitting = l_sitting
AND examboard = l_exam_board_code AND eblsubject = l_ebl_code ;
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
(
ln_appno, -- APPNO,
NULL, -- SUBJECTID,
NULL, -- EBLRESULT,
NULL, -- EBLAMENDED,
NULL, -- CLAIMEDRESULT,
l_year_date, -- YEAROFEXAM
l_sitting, -- SITTING
l_exam_board_code, -- EXAMBOARD
l_ebl_code, -- EBLSUBJECT
NULL, -- GRADE
l_grade1, -- GRADE1
l_grade2, -- GRADE2
l_lending_board, -- LENDINGBOARD
l_matchind, -- MATCHIND
SUBSTR(p_trans_type,2,1), -- MARVIN_TYPE
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *T (General Social Care Council Data) transaction data into igs_uc_mv_ivstarT table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstart with igs_uc_istart_ints
|| (reverse chronological order - newest change first)
||
*/
ln_appno igs_uc_istart_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istart_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno ;
INSERT INTO igs_uc_istart_ints(
appno ,
lastchange ,
futureserv ,
futureset ,
presentserv ,
presentset ,
curremp ,
eduqual ,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)),'DDMMRR'), -- LASTCHANGE
TRIM(SUBSTR(p_record_data,17,1)), -- FUTURESERV
TRIM(SUBSTR(p_record_data,18,1)), -- FUTUTRESET
TRIM(SUBSTR(p_record_data,19,1)), -- PRESENTSERV
TRIM(SUBSTR(p_record_data,20,1)), -- PRSENTSET
TRIM(SUBSTR(p_record_data,21,1)), -- CURREMP
TRIM(SUBSTR(p_record_data,22,2)), -- EDUQUAL
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *W transaction record into igs_uc_mv_ivstarw table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
||
|| rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstarw.JOINTADMISSION column value based on value in 17 column position.
|| Modified w.r.t. UCFD06 Build 2574566.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarw with igs_uc_istarw_ints
*/
ln_appno igs_uc_istarw_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istarw_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno ;
INSERT INTO igs_uc_istarw_ints(
appno,
miscoded,
cancelled,
canceldate,
remark,
jointadmission,
choice1lost,
choice2lost,
choice3lost,
choice4lost,
choice5lost,
choice6lost,
choice7lost,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
DECODE(TRIM(SUBSTR(p_record_data,17,1)),'M','Y','N'), -- MISCODED,
DECODE(TRIM(SUBSTR(p_record_data,17,1)),'C','Y','N'), -- CANCELLED,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- CANCELDATE,
NULL, -- REMARK,
DECODE(TRIM(SUBSTR(p_record_data,17,1)),'J','Y','N'), -- JOINTADMISSION
'N', -- CHOICE1LOST
'N', -- CHOICE2LOST
'N', -- CHOICE3LOST
'N', -- CHOICE4LOST
'N', -- CHOICE5LOST
'N', -- CHOICE6LOST
'N', -- CHOICE7LOST
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *X transaction record into igs_uc_mv_ivstarx table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rbezawad 25-Sep-2002 Modified population of POCC field to get 4 characters.
||
|| rgangara 11-Nov-02 Added logic to insert into IVSTARX extension table to hold
|| additional *X data for small systems support. Bug 2643048
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarx with igs_uc_istarx_ints which
|| includes columns of igs_uc_ss_ivstarx table also
*/
ln_appno igs_uc_istarx_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
UPDATE igs_uc_istarx_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno ;
INSERT INTO igs_uc_istarx_ints(
appno,
ethnic,
pocceduchangedate,
socialclass,
pocc,
pocctext,
socioeconomic,
occbackground,
religion,
dependants,
married,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
TO_NUMBER(TRIM(SUBSTR(p_record_data,17,2))), -- ETHNIC,
TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- POCCEDUCHANGEDATE,
TRIM(SUBSTR(p_record_data,19,1)), -- SOCIALCLASS,
TRIM(SUBSTR(p_record_data,28,4)), -- POCC,
NULL, -- POCCTEXT,
TRIM(SUBSTR(p_record_data,27,1)), -- SOCIOECONOMIC,
NULL, -- OCCBACKGROUND,
TO_NUMBER(TRIM(SUBSTR(p_record_data,23,1))), -- RELIGION
TO_NUMBER(TRIM(SUBSTR(p_record_data,24,2))), -- DEPENDANTS
TRIM(SUBSTR(p_record_data,26,1)), -- MARRIED
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given *Z transaction record into igs_uc_mv_ivstarz1 and igs_uc_mv_ivstarz2 tables
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_ivstarz1 with igs_uc_istarz1_ints and
|| igs_uc_mv_ivstarz2 with igs_uc_istarz2_ints
|| (reverse chronological order - newest change first)
*/
ln_position NUMBER;
UPDATE igs_uc_istarz1_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno ;
INSERT INTO igs_uc_istarz1_ints(
appno,
datecefsent,
cefno,
centralclearing,
inst,
course,
campus,
faculty,
entryyear,
entrymonth,
entrypoint,
result,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
NULL, -- DATECEFSENT,
NULL, -- CEFNO,
'N', -- CENTRALCLEARING,
TRIM(SUBSTR(p_record_data,20,3)), -- INST,
TRIM(SUBSTR(p_record_data,24,6)), -- COURSE,
TRIM(SUBSTR(p_record_data,30,1)), -- CAMPUS,
TRIM(SUBSTR(p_record_data,31,1)), -- FACULTY,
TO_NUMBER(TRIM(SUBSTR(p_record_data,204,2))), -- ENTRYYEAR,
TO_NUMBER(TRIM(SUBSTR(p_record_data,206,2))), -- ENTRYMONTH,
TO_NUMBER(TRIM(SUBSTR(p_record_data,203,1))), -- ENTRYPOINT,
l_result, -- RESULT,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
UPDATE igs_uc_istarz2_ints SET record_status = 'O'
WHERE record_status = 'N' AND appno = ln_appno
AND inst = TRIM(SUBSTR(p_record_data,ln_position,3))
AND course = TRIM(SUBSTR(p_record_data,ln_position+4,6))
AND campus = TRIM(SUBSTR(p_record_data,ln_position+10,1));
INSERT INTO igs_uc_istarz2_ints(
appno,
roundno,
inst,
course,
campus,
faculty,
roundtype,
result,
record_status,
error_code
)
VALUES
(
ln_appno, -- APPNO,
NULL, -- ROUNDNO,
TRIM(SUBSTR(p_record_data,ln_position,3)), -- INST,
TRIM(SUBSTR(p_record_data,ln_position+4,6)), -- COURSE,
TRIM(SUBSTR(p_record_data,ln_position+10,1)), -- CAMPUS,
TRIM(SUBSTR(p_record_data,ln_position+11,1)), -- FACULTY,
'F', -- ROUNDTYPE,
TRIM(SUBSTR(p_record_data,ln_position+12,1)), -- RESULT,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given QA transaction record into igs_uc_mv_uvofr_abv table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_uvofr_abv with igs_uc_uofabrv_ints
|| (reverse chronological order - newest change first)
*/
BEGIN
-- Obsolete matching records in interface table with status N
UPDATE igs_uc_uofabrv_ints SET record_status = 'O'
WHERE record_status = 'N' AND abbrevid = TO_NUMBER(TRIM(SUBSTR(p_record_data,1,2)));
INSERT INTO igs_uc_uofabrv_ints(
abbrevid,
updater,
abbrevtext,
letterformat,
summarychar,
abbrevuse,
record_status,
error_code
)
VALUES
(
TO_NUMBER(TRIM(SUBSTR(p_record_data,1,2))), -- ABBREVID,
NULL, -- UPDATER,
TRIM(SUBSTR(p_record_data,5,57)), -- ABBREVTEXT,
TRIM(SUBSTR(p_record_data,3,1)), -- LETTERFORMAT,
TRIM(SUBSTR(p_record_data,4,1)), -- SUMMARYCHAR,
TRIM(SUBSTR(p_record_data,4,1)), -- ABBREVUSE,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given QC transaction record into igs_uc_mv_uvcrs_vac and igs_uc_mv_uvcrs_vop tables
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| replaced igs_uc_mv_uvcrs_vac with igs_uc_ucrsvac_ints and
|| igs_uc_mv_uvcrs_vop with igs_uc_ucrsvop_ints
|| (reverse chronological order - newest change first)
*/
ln_position NUMBER;
UPDATE igs_uc_ucrsvac_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = lv_course AND campus = lv_campus ;
INSERT INTO igs_uc_ucrsvac_ints(
course,
campus,
updater,
clupdated,
cldate,
vacstatus,
novac,
score,
rbfull,
scotvac,
record_status,
error_code
)
VALUES
(
lv_course, -- COURSE,
lv_campus, -- CAMPUS,
NULL, -- UPDATER,
NULL, -- CLUPDATED,
NULL, -- CLDATE,
lv_vac_status, -- VACSTATUS,
TRIM(SUBSTR(p_record_data,9,2)), -- NOVAC,
TO_NUMBER(TRIM(SUBSTR(p_record_data,11,2))), -- SCORE,
NULL, -- RBFULL,
NULL, -- SCOTVAC,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
UPDATE igs_uc_ucrsvop_ints SET record_status = 'O'
WHERE record_status = 'N' AND course = lv_course
AND campus = lv_campus AND optioncode = TRIM(SUBSTR(p_record_data,ln_position,2)) ;
INSERT INTO igs_uc_ucrsvop_ints(
course,
campus,
optioncode,
updater,
clupdated,
cldate,
vacstatus,
record_status,
error_code
)
VALUES
(
lv_course, -- COURSE,
lv_campus, -- CAMPUS,
TRIM(SUBSTR(p_record_data,ln_position,2)), -- OPTIONCODE,
NULL, -- UPDATER,
NULL, -- CLUPDATED,
NULL, -- CLDATE,
lv_vac_status, -- VACSTATUS,
'N', -- RECORD_STATUS,
NULL -- ERROR_CODE
);
|| Purpose : Inserts the given Acknowledgment transactions into igs_uc_mv_tranin table
|| Known limitations, enhancements or remarks :
|| RGANGARA 11-Nov-02. It is said that all the transaction types for all systems falling
|| under Ack/Echo transactions have the same format and update processing
|| logic and hence using the same procedure for all such transactions.
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| rgangara 11-Nov-02 Modified the procedure to work for different types of ECHO/
|| Acknowledgment transactions.
|| NOTE: Clarified that the processing remains same for all the
|| different types of Transactions falling under this category
|| Also to update IGS_UC_TRANSACTIONS table directly instead of
|| Marvin tranin table.
|| rbezawad 17-Dec-02 Modified the procedure to remove the code which is loggig
|| IGS_UC_TRAN_PROC_APPCH for 2nd time w.r.t. Bug 2711183.
|| smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
|| to add ucas_cycle check in the cursor cur_trans
*/
-- Get transactions for the application, Choice number , ucas_cycle and Transaction Type having NULL error_code.
-- NULL error code implies that the no response/echo/Ack transaction has been received against it.
-- smaddali modified this cursor for ucfd203 - multiple cycles build to add ucas_cycle check in where clause
CURSOR cur_trans(
cp_appno igs_uc_transactions.app_no%TYPE,
cp_choiceno igs_uc_transactions.choice_no%TYPE
) IS
SELECT rowid ,
uc_tran_id ,
transaction_id ,
datetimestamp ,
updater ,
error_code ,
transaction_type ,
app_no ,
choice_no ,
decision ,
program_code ,
campus ,
entry_month ,
entry_year ,
entry_point ,
soc ,
comments_in_offer ,
return1 ,
return2 ,
hold_flag ,
sent_to_ucas ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
test_cond_cat ,
test_cond_name ,
inst_reference ,
auto_generated_flag,
system_code ,
ucas_cycle ,
modular ,
part_time
FROM igs_uc_transactions
WHERE app_no = cp_appno
AND choice_no = cp_choiceno
AND transaction_type = p_trans_type
AND error_code IS NULL
AND ucas_cycle = g_c_cycles.configured_cycle
ORDER BY uc_tran_id;
igs_uc_transactions_pkg.update_row(
x_rowid => trans_rec.rowid ,
x_uc_tran_id => trans_rec.uc_tran_id ,
x_transaction_id => trans_rec.transaction_id ,
x_datetimestamp => SYSDATE , -- update
x_updater => trans_rec.updater ,
x_error_code => p_error_code , -- update
x_transaction_type => trans_rec.transaction_type ,
x_app_no => trans_rec.app_no ,
x_choice_no => trans_rec.choice_no ,
x_decision => trans_rec.decision ,
x_program_code => trans_rec.program_code ,
x_campus => trans_rec.campus ,
x_entry_month => trans_rec.entry_month ,
x_entry_year => trans_rec.entry_year ,
x_entry_point => trans_rec.entry_point ,
x_soc => trans_rec.soc ,
x_comments_in_offer => trans_rec.comments_in_offer ,
x_return1 => trans_rec.return1 ,
x_return2 => trans_rec.return2 ,
x_hold_flag => trans_rec.hold_flag ,
x_sent_to_ucas => trans_rec.sent_to_ucas ,
x_test_cond_cat => trans_rec.test_cond_cat ,
x_test_cond_name => trans_rec.test_cond_name ,
x_mode => 'R' ,
x_inst_reference => trans_rec.inst_reference ,
x_auto_generated_flag => trans_rec.auto_generated_flag,
x_system_code => trans_rec.system_code ,
x_ucas_cycle => trans_rec.ucas_cycle ,
x_modular => trans_rec.modular ,
x_part_time => trans_rec.part_time
);
SELECT MOD(COUNT(*),2)
FROM igs_uc_load_mv_t
WHERE trans_type = '*S';
SELECT DISTINCT file_type
FROM igs_uc_load_mv_t
WHERE file_type IS NOT NULL ;
SELECT ucas_interface
FROM igs_uc_cyc_defaults
WHERE system_code = cp_sys_code
AND ucas_cycle = g_c_cycles.configured_cycle ;
SELECT *
FROM igs_uc_load_mv_t
WHERE marvin_id = cp_marvin_id;
UPDATE igs_uc_load_mv_t SET record_status = 'N' WHERE marvin_id = p_marvin_id;
SELECT marvin_id
FROM igs_uc_load_mv_t
WHERE contd_flag = '1'
AND record_status = 'N'
ORDER BY marvin_id
FOR UPDATE OF marvin_id NOWAIT;
UPDATE IGS_UC_LOAD_MV_T SET record_data = lv_record_data, record_status = 'R' WHERE CURRENT OF cur_ucas;
SELECT marvin_id, trans_type, error_code, record_data
FROM igs_uc_load_mv_t
WHERE record_status = 'R'
ORDER BY marvin_id ;