1 PACKAGE BODY IGS_UC_MV_DATA_UPLD AS
2 /* $Header: IGSUC31B.pls 120.5 2006/08/21 03:52:35 jbaber ship $ */
3
4 /*===============================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===============================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGS_UC_MV_DATA_UPLD |
11 | |
12 | NOTES |
13 | This is a Concurrent requeset set which segregates the data into |
14 | dummy hercules table after checking and validating the data. |
15 | This also rearranges the data in the record data spans more than |
16 | one record. |
17 | |
18 | HISTORY |
19 | Who When What |
20 | rbezawad 25-Sep-02 Modified w.r.t. UCFD06 Build, Bug No: 2574566. |
21 | rgangara 11-Nov-02 Added *G, *T transaction processing and modified |
22 | Acknowledgement/echo transaction processing to |
23 | update IGS_UC_TRANSACTIONS table directly as |
24 | part of UCFD02_Small_Systems Enh. Bug# 2643048 |
25 | Also added validation for ensuring that files |
26 are processed in seqeuence only. |
27 | rgangara 27-Nov-02 Removed TRIM for CAMPUS fields in *C and *G |
28 | Added *G, *T in Checkdigit validation. |
29 | rgangara 28-Nov-02 Fixed review comments. |
30 | Birthdate in (*N, *K) is sent as DDMMYY format |
31 | from UCAS. |
32 | rbezawad 02-Dec-02 Removed the TO_NUMBER() conversion while |
33 | importing data into IGS_UC_MV_IVSTARK.SPECIALNEEDS |
34 | column. This is done w.r.t. Bug 2620166 as |
35 | there is change in Hercules data model. |
36 | ayedubat 12-Dec-02 Changed the transfer_to_stara procedure for |
37 | bug:2702489 |
38 | rbezawad 17-Dec-02 Modified the transfer_ack_to_trans procedure to |
39 | remove the code which is loggig message |
40 | IGS_UC_TRAN_PROC_APPCH for 2nd time. Bug 2711183. |
41 | smaddali 29-jan-03 Modified procedure transfer_to_ivstarpqr ,for |
42 | UCCR005 build ,bug # 2749404. |
43 | rbezawad 25-Feb-03 Modified procedure transfer_to_starpqr() w.r.t. Bug |
44 | 2810932 for processing Previous results of an |
45 | applicant upto maximum 21 sets. |
46 | rbezawad 06-Mar-03 Corrected the code to properly display the count of |
47 | successful records w.r.t Bug 2810665. |
48 | pmarada 11-Jun-03 Added ucas_cycle to uc_transaction table, as per |
49 | UCFD203-Multiple cycles build, bug 2669208 |
50 | smaddali 30-Jun-03 Modified for Bug#2669208 , UCFD203 -multiple cycles |
51 | dsridhar 25-Jul-03 Bug No: 3022067, part of change request for UCAS |
52 | Application Calendar Mapping. Removed references to |
53 | calendar fields in igs_uc_cyc_defaults_pkg. |
54 | ayedubat 30-Jul-03 Changed the procedure,transfer_to_starw to replace |
55 | the column names substchoice1, substchoice2, |
56 | substchoice3, substchoice4, substchoice5, |
57 | substchoice6 and substchoice7 with choice1lost, |
58 | choice2lost, choice3lost, choice4lost, choice5lost, |
59 | choice6lost, choice7lost of igs_uc_istarw_ints for |
60 | bug, 2669208. |
61 | smaddali 07-Aug-03 Modified procedure logic for updating *N INTS record |
62 | in *K and *N transaction processing procedures for |
63 | bug 3085770 |
64 | smaddali 26-Aug-03 Modified procedure transfer_to_starpqr ,population of|
65 | field Grade , as part of bug#3114629 |
66 | smaddali 04-Sep-03 Modified procedure transfer_to_starpqr , bug#3122898 |
67 | rbezawad 13-Oct-03 Modified for ucfd209- Substitution Support build |
68 | bug#2669228. |
69 | jchakrab 27-Jul-04 Modified for UCFD308-UCAS 2005 Regulatory Changes |
70 | jbaber 12-Jul-05 Modified for UC315 - UCAS Support 2006 |
71 | jbaber 23-Aug-05 Modified for UC307 - HERCULES Small Systems Support |
72 | anwest 18-Jan-06 Bug# 4950285 R12 Disable OSS Mandate |
73 | anwest 29-May-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES |
74 | jbaber 12-Jul-06 Modified for UC325 - UCAS Support 2007 |
75 *==============================================================================*/
76
77 -- Declare all Global variables and global constants
78 g_record_cnt NUMBER;
79 g_success_cnt NUMBER;
80
81 -- smaddali added these cursors for bug#2669208 , ucfd203 build
82 -- Get the current and configured cycles from defaults table
83 CURSOR c_cycles IS
84 SELECT MAX(configured_cycle) configured_cycle, MAX(current_cycle) current_cycle
85 FROM igs_uc_defaults ;
86 g_c_cycles c_cycles%ROWTYPE ;
87
88 -- get the cycle to which hercules is configured
89 CURSOR c_ucas_cycle IS
90 SELECT entry_year
91 FROM igs_uc_ucas_control
92 WHERE system_code = 'U'
93 AND ucas_cycle = g_c_cycles.configured_cycle;
94 c_ucas_cycle_rec c_ucas_cycle%ROWTYPE ;
95
96 FUNCTION is_numeric(
97 p_value VARCHAR2
98 ) RETURN BOOLEAN AS
99
100 /*
101 || Created By : brajendr
102 || Created On :
103 || Purpose : function which return TRUE if the passed value
104 || can be convertable into NUMBER else return FALSE.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 return_value BOOLEAN := FALSE;
111 ln_number NUMBER;
112
113 BEGIN
114
115 -- Check whether the passed value can be convertable to number of not.
116 -- If the value can not be convertable to number catch the exception and return FALSE.
117 BEGIN
118 ln_number := TO_NUMBER(p_value);
119 return_value := TRUE;
120
121 EXCEPTION
122 WHEN VALUE_ERROR THEN
123 return_value := FALSE;
124 END;
125
126 RETURN return_value;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
131 fnd_message.set_token('NAME','IS_NUMERIC - '||SQLERRM);
132 igs_ge_msg_stack.add;
133 app_exception.raise_exception;
134
135 END is_numeric;
136
137
138 FUNCTION get_numeric_choice(
139 p_char VARCHAR2
140 ) RETURN NUMBER IS
141 /*
142 || Created By : rgangara
143 || Created On : 11-Nov-02
144 || Purpose : Function which return a Numeric Choice Number if the incoming
145 || Choice Number is an Alphabet
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 */
151 l_num NUMBER(2);
152 l_ascii NUMBER(2);
153 l_a_ascii NUMBER(2) := ASCII('A');
154 BEGIN
155
156 IF ASCII(p_char) BETWEEN 49 and 57 THEN
157 l_num := TO_NUMBER(p_char);
158
159 ELSIF (UPPER(p_char) BETWEEN 'A' AND 'Z' ) THEN
160
161 l_ascii := ASCII(UPPER(p_char));
162 l_num := 10 + (l_ascii - l_a_ascii);
163 ELSE
164 l_num := 0;
165 fnd_message.set_name('IGS', 'IGS_UC_INVALID_CHOICE');
166 fnd_message.set_token('CHOICE',p_char);
167 fnd_file.put_line( fnd_file.log, fnd_message.get());
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171
172 RETURN (l_num);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
177 fnd_message.set_token('NAME','GET_NUMERIC_CHOICE - '||SQLERRM);
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END get_numeric_choice;
181
182
183 PROCEDURE validate_file_seq_num(
184 p_marvin_id igs_uc_load_mv_t.marvin_id%TYPE,
185 p_curr_seq_num igs_uc_load_mv_t.record_data%TYPE
186 ) is
187 /*
188 || Created By : rgangara
189 || Created On : 22-Nov-02
190 || Purpose : To validate that the incoming flat files are being processed in sequence otherwise display suitable error message and stop processing.
191 || Known limitations, enhancements or remarks :
192 || Change History :
193 || Who When What
194 || smaddali 30-jun-03 modified for ucfd203 - multiple cycles build ,bug#2669208
195 || to get the marvin seq no from igs_uc_cyc_defaults, since
196 || igs_uc_adm_systems is obsolete
197 || dsridhar 25-JUL-2003 Bug No: 3022067, part of change request for UCAS Application Calendar Mapping.
198 || Removed references to calendar fields in igs_uc_cyc_defaults_pkg.
199 || (reverse chronological order - newest change first)
200 */
201
202 -- To identify the system to which the file sequence number transaction has come.
203 -- 'AE' transactions are the 1st record after header which contains the system to which the section belongs.
204 -- The earlier record to the 'AE' transaction contains header info which can be used for system identification.
205 CURSOR cur_ae_trans IS
206 SELECT file_type
207 FROM igs_uc_load_mv_t
208 WHERE marvin_id = p_marvin_id - 1;
209
210 -- Cursor to get the existing sequence number for the coresponding system
211 -- smaddali modified cursor for bug #2669208 , ucfd203 build, igs_uc_adm_systems is now merged with igs_uc_defaults
212 CURSOR c_cyc_defaults(cp_syscode igs_uc_cyc_defaults.system_code%TYPE) IS
213 SELECT a.rowid , a.* , b.name
214 FROM igs_uc_cyc_defaults a , igs_uc_defaults b
215 WHERE a.system_code = b.system_code
216 AND a.system_code = cp_syscode
217 AND a.ucas_cycle = g_c_cycles.configured_cycle ;
218
219 ae_trans_rec cur_ae_trans%ROWTYPE;
220 cyc_defaults_rec c_cyc_defaults%ROWTYPE;
221
222 BEGIN
223 OPEN cur_ae_trans;
224 FETCH cur_ae_trans INTO ae_trans_rec;
225 CLOSE cur_ae_trans;
226
227 IF ae_trans_rec.file_type IS NOT NULL THEN
228 -- get the earlier marvin seqence number
229 OPEN c_cyc_defaults (ae_trans_rec.file_type);
230 FETCH c_cyc_defaults INTO cyc_defaults_rec;
231 CLOSE c_cyc_defaults;
232
233 -- log a message displaying the system and the section number being processed.
234 fnd_message.set_name('IGS', 'IGS_UC_MV_PROC_SEQ');
235 fnd_message.set_token('SYSTEM', cyc_defaults_rec.name);
236 fnd_message.set_token('SEQ', p_curr_seq_num);
237 fnd_file.put_line(fnd_file.log, fnd_message.get());
238
239 --Update the Marvin file seq number in the setup table.
240 igs_uc_cyc_defaults_pkg.update_row (
241 x_rowid => cyc_defaults_rec.rowid,
242 x_system_code => cyc_defaults_rec.system_code,
243 x_ucas_cycle => cyc_defaults_rec.ucas_cycle,
244 x_ucas_interface => cyc_defaults_rec.ucas_interface,
245 x_marvin_seq => p_curr_seq_num,
246 x_clearing_flag => cyc_defaults_rec.clearing_flag,
247 x_extra_flag => cyc_defaults_rec.extra_flag,
248 x_cvname_flag => cyc_defaults_rec.cvname_flag,
249 x_mode => 'R'
250 );
251
252 END IF;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
257 fnd_message.set_token('NAME','VALIDATE_FILE_SEQ_NUM - '||SQLERRM);
258 igs_ge_msg_stack.add;
259 app_exception.raise_exception;
260 END validate_file_seq_num;
261
262
263 FUNCTION sequence_validity RETURN BOOLEAN IS
264 /*
265 || Created By : smaddali
266 || Created On : 9-jul-03
267 || Purpose : To validate that the incoming flat files have the correct marvin sequence number
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || (reverse chronological order - newest change first)
272 */
273
274 -- Get the details of AE transactions
275 CURSOR cur_seq IS
276 SELECT marvin_id, record_data
277 FROM igs_uc_load_mv_t
278 WHERE trans_type = 'AE' ;
279
280 -- To identify the system to which the file sequence number transaction has come.
281 -- 'AE' transactions are the 1st record after header which contains the system to which the section belongs.
282 -- The earlier record to the 'AE' transaction contains header info which can be used for system identification.
283 CURSOR cur_ae_trans (cp_marvin_id igs_uc_load_mv_t.marvin_id%TYPE ) IS
284 SELECT file_type
285 FROM igs_uc_load_mv_t
286 WHERE marvin_id = cp_marvin_id - 1;
287
288 -- Cursor to get the existing sequence number for the coresponding system
289 -- smaddali modified cursor for bug #2669208 , ucfd203 build, igs_uc_adm_systems is now merged with igs_uc_defaults
290 CURSOR c_cyc_defaults(cp_syscode igs_uc_cyc_defaults.system_code%TYPE) IS
291 SELECT a.rowid , a.* , b.name
292 FROM igs_uc_cyc_defaults a , igs_uc_defaults b
293 WHERE a.system_code = b.system_code
294 AND a.system_code = cp_syscode
295 AND a.ucas_cycle = g_c_cycles.configured_cycle ;
296
297 ae_trans_rec cur_ae_trans%ROWTYPE;
298 cyc_defaults_rec c_cyc_defaults%ROWTYPE;
299 l_appno VARCHAR2(8);
300 l_valid BOOLEAN ;
301 l_curr_seq_num NUMBER ;
302
303 BEGIN
304 -- initialise variable
305 l_valid := TRUE ;
306
307 -- loop thru all the AE transactions
308 FOR cur_seq_rec IN cur_seq LOOP
309
310 l_curr_seq_num := SUBSTR(cur_seq_rec.record_data,4) ;
311 ae_trans_rec := NULL ;
312 OPEN cur_ae_trans( cur_seq_rec.marvin_id );
313 FETCH cur_ae_trans INTO ae_trans_rec;
314 CLOSE cur_ae_trans;
315
316 IF ae_trans_rec.file_type IS NOT NULL THEN
317 -- check whether the system exists and get the earlier marvin seqence number
318 cyc_defaults_rec := NULL ;
319 OPEN c_cyc_defaults (ae_trans_rec.file_type);
320 FETCH c_cyc_defaults INTO cyc_defaults_rec;
321 IF c_cyc_defaults%NOTFOUND THEN
322 CLOSE c_cyc_defaults;
323 fnd_message.set_name('IGS', 'IGS_UC_MV_SYSTEM_NOT_CONFIG');
324 fnd_message.set_token('SYSTEM', ae_trans_rec.file_type );
325 fnd_file.put_line( fnd_file.log, fnd_message.get());
326 l_valid := FALSE ;
327 ELSE
328 CLOSE c_cyc_defaults;
329 END IF;
330
331 -- check whether the current file/section being processed is immediate next to earlier processed seq.
332 IF NVL(cyc_defaults_rec.marvin_seq,0) <> (l_curr_seq_num - 1) THEN
333 fnd_message.set_name('IGS', 'IGS_UC_MV_FILE_NOT_SEQ');
334 fnd_message.set_token('SYSTEM', cyc_defaults_rec.name);
335 fnd_message.set_token('OLDSEQ', NVL(cyc_defaults_rec.marvin_seq,0));
336 fnd_message.set_token('CURSEQ', l_curr_seq_num);
337 fnd_file.put_line(fnd_file.log, fnd_message.get());
338 l_valid := FALSE ;
339 END IF;
340
341 END IF;
342 END LOOP ;
343
344 RETURN l_valid ;
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
349 fnd_message.set_token('NAME','SEQUENCE_VALIDITY - '||SQLERRM);
350 igs_ge_msg_stack.add;
351 app_exception.raise_exception;
352 END sequence_validity;
353
354
355
356 FUNCTION get_check_digit(
357 p_appno VARCHAR2
358 ) RETURN NUMBER AS
359
360 /*
361 || Created By : brajendr
362 || Created On :
363 || Purpose :
364 || Known limitations, enhancements or remarks :
365 || Change History :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 || anwest 29-May-2006 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
369 */
370 ln_chk_digit NUMBER := 0;
371 lv_weight VARCHAR2(8) := '13791379';
372 lv_appno VARCHAR2(8);
373
374 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
375 -- This code has been added to accommodate the UCAS
376 -- error in calculating the check digit incorrectly
377 -- for this application range by using the incorrect
378 -- weighting
379 lv_weight_alt VARCHAR2(8) := '03790379';
380 lv_appno_alt_min NUMBER := 6000999;
381 lv_appno_alt_max NUMBER := 6009996;
382
383
384 BEGIN
385
386 -- Calculte the check digit.
387 lv_appno := LPAD(p_appno,8,0);
388 FOR i IN 1..8 LOOP
389
390 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
391 -- This code has been added to accommodate the UCAS
392 -- error in calculating the check digit incorrectly
393 -- for this application range by using the incorrect
394 -- weighting
395 IF TO_NUMBER(lv_appno) >= lv_appno_alt_min AND TO_NUMBER(lv_appno) <= lv_appno_alt_max THEN
396 ln_chk_digit := ln_chk_digit + TO_NUMBER(SUBSTR(lv_appno,i,1)) * TO_NUMBER(SUBSTR(lv_weight_alt,i,1));
397 ELSE
398 ln_chk_digit := ln_chk_digit + TO_NUMBER(SUBSTR(lv_appno,i,1)) * TO_NUMBER(SUBSTR(lv_weight,i,1));
399 END IF;
400
401 END LOOP;
402
403 ln_chk_digit := 10-MOD(ln_chk_digit,10);
404
405 RETURN MOD(ln_chk_digit,10);
406
407 EXCEPTION
408 WHEN VALUE_ERROR THEN
409 RETURN -1;
410
411 END get_check_digit;
412
413
414 PROCEDURE transfer_to_stara(
415 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
416 p_record_data igs_uc_load_mv_t.record_data%TYPE
417 ) AS
418 /*
419 || Created By : brajendr
420 || Created On :
421 || Purpose : Inserts the given *A transaction record into igs_uc_mv_ivstara table
422 || Known limitations, enhancements or remarks :
423 || Change History :
424 || Who When What
425 || ayedubat 12-DEC-2002 Passed the SYSDATE for TIMESTAMP column for bug fix:2702489
426 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
427 || replaced igs_uc_mv_ivstara with igs_uc_istara_ints
428 || (reverse chronological order - newest change first)
429 */
430
431 ln_appno igs_uc_istara_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
432
433 BEGIN
434
435 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
436 fnd_message.set_token('TTYPE','*A ');
437 fnd_message.set_token('APPNO', ln_appno);
438 fnd_file.put_line( fnd_file.log, fnd_message.get());
439
440 -- Obsolete matching records in interface table with status N
441 UPDATE igs_uc_istara_ints SET record_status = 'O'
442 WHERE record_status = 'N' AND appno = ln_appno ;
443
444 INSERT INTO igs_uc_istara_ints(
445 appno,
446 addressarea,
447 address1,
448 address2,
449 address3,
450 address4,
451 postcode,
452 mailsort,
453 telephone,
454 fax,
455 email,
456 homeaddress1,
457 homeaddress2,
458 homeaddress3,
459 homeaddress4,
460 homepostcode,
461 homephone,
462 homefax,
463 homeemail,
464 record_status,
465 error_code
466 )
467 VALUES
468 (
469 ln_appno, -- APPNO,
470 TRIM(SUBSTR(p_record_data,17,1)), -- ADDRESSAREA,
471 TRIM(SUBSTR(p_record_data,18,27)), -- ADDRESS1,
472 TRIM(SUBSTR(p_record_data,45,27)), -- ADDRESS2,
473 TRIM(SUBSTR(p_record_data,72,27)), -- ADDRESS3,
474 TRIM(SUBSTR(p_record_data,99,27)), -- ADDRESS4,
475 TRIM(SUBSTR(p_record_data,126,8)), -- POSTCODE,
476 TRIM(SUBSTR(p_record_data,134,5)), -- MAILSORT,
477 TRIM(SUBSTR(p_record_data,139,20)), -- TELEPHONE,
478 NULL, -- FAX
479 NULL, -- EMAIL,
480 NULL, -- HOMEADDRESS1,
481 NULL, -- HOMEADDRESS2,
482 NULL, -- HOMEADDRESS3,
483 NULL, -- HOMEADDRESS4,
484 NULL, -- HOMEPOSTCODE,
485 NULL, -- HOMEPHONE,
486 NULL, -- HOMEFAX
487 NULL, -- HOMEEMAIL
488 'N', -- RECORD_STATUS,
489 NULL -- ERROR_CODE,
490 );
491
492 -- Increase the success record count.
493 g_success_cnt := g_success_cnt +1;
494
495 EXCEPTION
496 WHEN VALUE_ERROR THEN
497 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
498 fnd_message.set_token('TTYPE',p_trans_type);
499 fnd_file.put_line( fnd_file.log, fnd_message.get());
500 igs_ge_msg_stack.add;
501 app_exception.raise_exception;
502
503 WHEN OTHERS THEN
504 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
505 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARA - '||SQLERRM);
506 igs_ge_msg_stack.add;
507 app_exception.raise_exception;
508
509 END transfer_to_stara;
510
511
512 PROCEDURE transfer_to_starc(
513 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
514 p_record_data igs_uc_load_mv_t.record_data%TYPE
515 ) AS
516 /*
517 || Created By : brajendr
518 || Created On :
519 || Purpose : Inserts the given *C transaction record into igs_uc_mv_ivstarc table
520 || Known limitations, enhancements or remarks :
521 || Change History :
522 || Who When What
523 || (reverse chronological order - newest change first)
524 ||
525 || rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstarc.EXTRAROUND column value from 58-60 column positions.
526 || Modified w.r.t. UCFD06 Build 2574566.
527 || rgangara 11-Nov-02 Added logic to insert into IVSTARC extension table to hold
528 || additional *C data for small systems support. Bug 2643048.
529 || Changed the Inst field positions from 18,4 to 18,3 after discussing with Martin
530 || as the table has it as 3 chars and was erroring out.
531 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
532 || replaced igs_uc_mv_ivstarc with igs_uc_istarc_ints which
533 || includes columns of igs_uc_ss_ivstarc table also
534 || rbezawad 13-Oct-03 Modified for ucfd209- Substitution Support build , bug#2669228
535 */
536
537 l_char_choice VARCHAR2(1) := TRIM(SUBSTR(p_record_data,17,1));
538 l_num_choice NUMBER(2);
539 ln_appno igs_uc_istarc_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
540 l_reason_for_transmission VARCHAR2(1) := TRIM(SUBSTR(p_record_data,10,1));
541 l_rowid VARCHAR2(25);
542 l_sql_stmt VARCHAR2(500);
543
544 -- Check any record with passed AppNo exists in IGS_UC_ISTARW_INTS with record_status = 'N'
545 CURSOR cur_wrong_app (cp_appno igs_uc_istarw_ints.appno%TYPE) IS
546 SELECT w.ROWID
547 FROM igs_uc_istarw_ints w
548 WHERE w.appno = cp_appno
549 AND w.record_status = 'N';
550
551 BEGIN
552
553 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APPCH');
554 fnd_message.set_token('TTYPE','*C ');
555 fnd_message.set_token('APPNO', ln_appno);
556 fnd_message.set_token('CHOICENO', l_char_choice);
557 fnd_file.put_line( fnd_file.log, fnd_message.get());
558
559 --Added as part of UCFD02 build for GTTR system.
560 --If the incoming CHOICE No. is Alphabetic then it has to be converted to appropriate Number and used.
561 -- i.e. if the incoming Choice No = 'A' then Choice No = 10, If it is 'F' then Choice = 15 etc.
562 IF NOT ASCII(l_char_choice) BETWEEN 49 AND 57 THEN
563 l_num_choice := get_numeric_choice(l_char_choice);
564 ELSE
565 l_num_choice := TO_NUMBER(l_char_choice);
566 END IF;
567
568 IF l_reason_for_transmission = 'R' THEN
569 --When Reason For Transmission i.e Character position 10 = 'R'
570
571 l_rowid := NULL;
572 OPEN cur_wrong_app (ln_appno);
573 FETCH cur_wrong_app INTO l_rowid;
574 CLOSE cur_wrong_app;
575
576 --Check any record with this AppNo exists in IGS_UC_ISTARW_INTS with record_status = 'N'
577 IF l_rowid IS NOT NULL THEN
578 IF l_num_choice BETWEEN 1 AND 7 THEN
579 --The update should be such that other choicelost fields should retain their existing values
580 -- and only the current choice related choicelost field value should get updated in the IGS_UC_ISTARW_INTS table.
581 l_sql_stmt := 'UPDATE igs_uc_istarw_ints SET choice'||l_num_choice||'lost = ''Y'' WHERE ROWID = :1';
582 EXECUTE IMMEDIATE l_sql_stmt USING l_rowid;
583 END IF;
584
585 ELSE
586 --Insert a new record into IGS_UC_ISTARW_INTS
587 INSERT INTO igs_uc_istarw_ints(
588 appno,
589 miscoded,
590 cancelled,
591 canceldate,
592 remark,
593 jointadmission,
594 choice1lost,
595 choice2lost,
596 choice3lost,
597 choice4lost,
598 choice5lost,
599 choice6lost,
600 choice7lost,
601 record_status,
602 error_code
603 )
604 VALUES
605 (
606 ln_appno, -- APPNO,
607 'N', -- MISCODED,
608 'N', -- CANCELLED,
609 NULL, -- CANCELDATE,
610 NULL, -- REMARK,
611 'N', -- JOINTADMISSION
612 DECODE(l_num_choice,1,'Y','N'), -- CHOICE1LOST
613 DECODE(l_num_choice,2,'Y','N'), -- CHOICE2LOST
614 DECODE(l_num_choice,3,'Y','N'), -- CHOICE3LOST
615 DECODE(l_num_choice,4,'Y','N'), -- CHOICE4LOST
616 DECODE(l_num_choice,5,'Y','N'), -- CHOICE5LOST
617 DECODE(l_num_choice,6,'Y','N'), -- CHOICE6LOST
618 DECODE(l_num_choice,7,'Y','N'), -- CHOICE7LOST
619 'N', -- RECORD_STATUS,
620 NULL -- ERROR_CODE
621 );
622 END IF;
623
624 ELSE
625 --When Reason For Transmission i.e Character position 10 <> 'R'
626 -- Obsolete matching records in interface table with status N
627 UPDATE igs_uc_istarc_ints SET record_status = 'O'
628 WHERE record_status = 'N' AND appno = ln_appno
629 AND choiceno = l_num_choice AND ucas_cycle= g_c_cycles.configured_cycle;
630
631 INSERT INTO igs_uc_istarc_ints(
632 appno,
633 choiceno,
634 ucas_cycle,
635 lastchange,
636 inst,
637 course,
638 campus,
639 faculty,
640 home,
641 decision,
642 decisiondate,
643 decisionnumber,
644 reply,
645 summaryconditions,
646 entrymonth,
647 entryyear,
648 entrypoint,
649 choicecancelled,
650 action,
651 substitution,
652 datesubstituted,
653 previousinst,
654 previouscourse,
655 previouscampus,
656 ucasamendment,
657 routebpref,
658 routebround,
659 detail,
660 extraround,
661 residential,
662 record_status,
663 error_code
664 )
665 VALUES
666 (
667 ln_appno, -- APPNO,
668 l_num_choice, -- CHOICENO,
669 g_c_cycles.configured_cycle, -- UCAS_CYCLE,
670 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- LASTCHANGE,
671 TRIM(SUBSTR(p_record_data,18,3)), -- INST,
672 TRIM(SUBSTR(p_record_data,22,6)), -- COURSE,
673 DECODE(RTRIM(SUBSTR(p_record_data,28,1)),NULL,
674 '*',SUBSTR(p_record_data,28,1)) , -- CAMPUS,
675 TRIM(SUBSTR(p_record_data,29,1)), -- FACULTY,
676 TRIM(SUBSTR(p_record_data,54,1)), -- HOME,
677 TRIM(SUBSTR(p_record_data,30,1)), -- DECISION,
678 NULL, -- DECISIONDATE,
679 NULL, -- DECISIONNUMBER,
680 TRIM(SUBSTR(p_record_data,31,1)), -- REPLY,
681 TRIM(SUBSTR(p_record_data,32,6)), -- SUMMARYCONDITIONS,
682 TO_NUMBER(TRIM(SUBSTR(p_record_data,40,2))), -- ENTRYMONTH,
683 TO_NUMBER(TRIM(SUBSTR(p_record_data,38,2))), -- ENTRYYEAR,
684 TRIM(SUBSTR(p_record_data,55,1)), -- ENTRYPOINT,
685 DECODE(TRIM(SUBSTR(p_record_data,42,1)),'C','Y','N'),-- CHOICECANCELLED,
686 TRIM(SUBSTR(p_record_data,43,1)), -- ACTION,
687 TRIM(SUBSTR(p_record_data,44,1)), -- SUBSTITUTION,
688 NULL, -- DATESUBSTITUTED,
689 NULL, -- PREVIOUSINST,
690 TRIM(SUBSTR(p_record_data,46,6)), -- PREVIOUSCOURSE,
691 NULL, -- PREVIOUSCAMPUS,
692 TRIM(SUBSTR(p_record_data,45,1)), -- UCASAMENDMENT,
693 TO_NUMBER(TRIM(SUBSTR(p_record_data,56,1))), -- ROUTEBPREF,
694 NULL, -- ROUTEBROUND,
695 NULL, -- DETAIL
696 TO_NUMBER(TRIM(SUBSTR(p_record_data,58,3))), -- EXTRAROUND,
697 DECODE(TRIM(SUBSTR(p_record_data,57,1)),'R','Y','N'),-- RESIDENTIAL,
698 'N', -- RECORD_STATUS,
699 NULL -- ERROR_CODE
700 );
701
702 END IF;
703
704 -- Increase the success record count.
705 g_success_cnt := g_success_cnt +1;
706
707 EXCEPTION
708 WHEN VALUE_ERROR THEN
709 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
710 fnd_message.set_token('TTYPE',p_trans_type);
711 fnd_file.put_line( fnd_file.log, fnd_message.get());
712 igs_ge_msg_stack.add;
713 app_exception.raise_exception;
714
715 WHEN OTHERS THEN
716 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
717 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARC - '||SQLERRM);
718 igs_ge_msg_stack.add;
719 app_exception.raise_exception;
720
721 END transfer_to_starc;
722
723
724 PROCEDURE transfer_to_starg(
725 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
726 p_record_data igs_uc_load_mv_t.record_data%TYPE
727 ) AS
728 /*
729 || Created By : rgangara
730 || Created On : 11-Nov-02
731 || Purpose : Inserts the given *G (GTTR Referral Details) transaction data into igs_uc_mv_ivstarG table
732 || Known limitations, enhancements or remarks :
733 || Change History :
734 || Who When What
735 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
736 || replaced igs_uc_mv_ivstarg with igs_uc_istarg_ints
737 || (reverse chronological order - newest change first)
738 ||
739 */
740
741 l_char_choice VARCHAR2(1) := TRIM(SUBSTR(p_record_data,17,1));
742 l_num_choice NUMBER(2);
743 ln_appno igs_uc_istarg_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
744
745
746 BEGIN
747
748 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APPCH');
749 fnd_message.set_token('TTYPE','*G ');
750 fnd_message.set_token('APPNO', ln_appno);
751 fnd_message.set_token('CHOICENO', l_char_choice);
752 fnd_file.put_line( fnd_file.log, fnd_message.get());
753
754 --Added as part of UCFD02 build for GTTR system.
755 --If the incoming CHOICE No. is Alphabetic then it has to be converted to appropriate Number and used.
756 -- i.e. if the incoming Choice No = 'A' then Choice No = 10, If it is 'F' then Choice = 15 etc.
757 IF NOT ASCII(l_char_choice) BETWEEN 49 and 57 THEN
758 l_num_choice := get_numeric_choice(l_char_choice);
759 ELSE
760 l_num_choice := TO_NUMBER(l_char_choice);
761 END IF;
762
763 -- Obsolete matching records in interface table with status N
764 UPDATE igs_uc_istarg_ints SET record_status = 'O'
765 WHERE record_status = 'N' AND appno = ln_appno
766 AND roundno = l_num_choice ;
767
768
769 INSERT INTO igs_uc_istarg_ints(
770 appno ,
771 roundno ,
772 ucas_cycle ,
773 lastchange ,
774 inst ,
775 course ,
776 campus ,
777 parttime ,
778 decision ,
779 reply ,
780 entryyear ,
781 entrymonth ,
782 action ,
783 interview ,
784 lateapplication ,
785 modular ,
786 confirmed ,
787 gcseeng ,
788 gcsemath ,
789 degreesubject ,
790 degreestatus ,
791 degreeclass ,
792 gcsesci ,
793 record_status,
794 error_code
795 )
796 VALUES
797 (
798 ln_appno, -- APPNO,
799 l_num_choice, -- ROUNDNO
800 g_c_cycles.configured_cycle, -- UCAS_CYCLE,
801 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)),'DDMMRR'), -- LASTCHANGE
802 TRIM(SUBSTR(p_record_data,18,3)), -- INST Though as per Manual 4 chars, take it as 3 since our table has 3
803 TRIM(SUBSTR(p_record_data,22,6)), -- COURSE
804 DECODE(RTRIM(SUBSTR(p_record_data,28,1)),NULL,
805 '*',SUBSTR(p_record_data,28,1) ) , -- CAMPUS
806 DECODE(TRIM(SUBSTR(p_record_data,30,1)),'P','Y','N'),-- PARTTIME
807 TRIM(SUBSTR(p_record_data,31,1)), -- DECISION
808 TRIM(SUBSTR(p_record_data,32,1)), -- REPLY
809 TO_NUMBER(TRIM(SUBSTR(p_record_data,33,2))), -- ENTRYYEAR
810 TO_NUMBER(TRIM(SUBSTR(p_record_data,53,2))), -- ENTRYMONTH
811 TRIM(SUBSTR(p_record_data,35,1)), -- ACTION
812 TO_DATE(TRIM(SUBSTR(p_record_data,44,6)), 'DDMMRR'), -- INTERVIEW
813 DECODE(TRIM(SUBSTR(p_record_data,51,1)),'L','Y','N'),-- LATEAPPLICATION
814 DECODE(TRIM(SUBSTR(p_record_data,52,1)),'M','Y','N'),-- MODULAR
815 TRIM(SUBSTR(p_record_data,36,1)), -- CONFIRMED
816 TRIM(SUBSTR(p_record_data,37,1)), -- GCSE_ENG
817 TRIM(SUBSTR(p_record_data,38,1)), -- GCSE_MATH
818 TRIM(SUBSTR(p_record_data,40,2)), -- DEGREE_SUBJECT
819 TRIM(SUBSTR(p_record_data,39,1)), -- DEGREE_STATUS
820 TRIM(SUBSTR(p_record_data,42,2)), -- DEGREE_CLASS
821 TRIM(SUBSTR(p_record_data,50,1)), -- GCSE_SCI
822 'N', -- RECORD_STATUS,
823 NULL -- ERROR_CODE
824 );
825
826 -- Increase the success record count.
827 g_success_cnt := g_success_cnt +1;
828
829 EXCEPTION
830 WHEN VALUE_ERROR THEN
831 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
832 fnd_message.set_token('TTYPE',p_trans_type);
833 fnd_file.put_line( fnd_file.log, fnd_message.get());
834 igs_ge_msg_stack.add;
835 app_exception.raise_exception;
836
837 WHEN OTHERS THEN
838 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
839 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARG - '||SQLERRM);
840 igs_ge_msg_stack.add;
841 app_exception.raise_exception;
842
843 END transfer_to_starg;
844
845
846 PROCEDURE transfer_to_starh(
847 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
848 p_record_data igs_uc_load_mv_t.record_data%TYPE
849 ) AS
850 /*
851 || Created By : brajendr
852 || Created On :
853 || Purpose : Inserts the given *H transaction record into igs_uc_mv_ivstarh table
854 || Known limitations, enhancements or remarks :
855 || Change History :
856 || Who When What
857 || (reverse chronological order - newest change first)
858 || rgangara 11-Nov-02 Added logic to insert into IVSTARH extension table to hold
859 || additional *H data for small systems support. Bug 2643048
860 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
861 || replaced igs_uc_mv_ivstarh with igs_uc_istarh_ints which
862 || includes columns of igs_uc_ss_ivstarh table also
863 */
864
865
866 ln_appno igs_uc_istarh_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
867
868
869 BEGIN
870
871 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
872 fnd_message.set_token('TTYPE','*H ');
873 fnd_message.set_token('APPNO', ln_appno);
874 fnd_file.put_line( fnd_file.log, fnd_message.get());
875
876 -- Obsolete matching records in interface table with status N
877 UPDATE igs_uc_istarh_ints SET record_status = 'O'
878 WHERE record_status = 'N' AND appno = ln_appno ;
879
880 INSERT INTO igs_uc_istarh_ints(
881 appno,
882 ethnic,
883 socialclass,
884 pocceduchangedate,
885 pocc,
886 pocctext,
887 lasteducation,
888 educationleavedate,
889 lea,
890 socialeconomic,
891 dependants,
892 married,
893 record_status,
894 error_code
895 )
896 VALUES
897 (
898 ln_appno, -- APPNO,
899 TO_NUMBER(TRIM(SUBSTR(p_record_data,17,2))), -- ETHNIC,
900 TRIM(SUBSTR(p_record_data,19,1)), -- SOCIALCLASS,
901 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- POCCEDUCHANGEDATE,
902 TRIM(SUBSTR(p_record_data,39,4)), -- POCC, -- 21-Nov-02 changed from 39,3 to 39,4 as it was wrong earlier.
903 NULL, -- POCCTEXT,
904 TO_NUMBER(TRIM(SUBSTR(p_record_data,23,7))), -- LASTEDUCATION,
905 TO_NUMBER(TRIM(SUBSTR(p_record_data,30,2))), -- EDUCATIONLEAVEDATE,
906 NULL, -- LEA,
907 TO_NUMBER(TRIM(SUBSTR(p_record_data,35,1))), -- SOCIALECONOMIC,
908 TO_NUMBER(TRIM(SUBSTR(p_record_data,36,2))), -- DEPENDANTS,
909 TRIM(SUBSTR(p_record_data,38,1)), -- MARRIED,
910 'N', -- RECORD_STATUS,
911 NULL -- ERROR_CODE
912 );
913
914 -- Increase the success record count.
915 g_success_cnt := g_success_cnt +1;
916
917 EXCEPTION
918 WHEN VALUE_ERROR THEN
919 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
920 fnd_message.set_token('TTYPE',p_trans_type);
921 fnd_file.put_line( fnd_file.log, fnd_message.get());
922 igs_ge_msg_stack.add;
923 app_exception.raise_exception;
924
925 WHEN OTHERS THEN
926 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
927 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARH - '||SQLERRM);
928 igs_ge_msg_stack.add;
929 app_exception.raise_exception;
930
931 END transfer_to_starh;
932
933
934 PROCEDURE transfer_to_stark(
935 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
936 p_record_data igs_uc_load_mv_t.record_data%TYPE
937 ) AS
938 /*
939 || Created By : brajendr
940 || Created On :
941 || Purpose : Inserts the given *K transactionrecord into igs_uc_mv_ivstark table
942 || Known limitations, enhancements or remarks :
943 || Change History :
944 || Who When What
945 || (reverse chronological order - newest change first)
946 ||
947 || rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstark.CHOICESALLTRANSPARENT column value from 120 column position and
948 || EXTRASTATUS, EXTRAPASSPORTNO columns with NULL values. Modified w.r.t. UCFD06 Build 2574566.
949 || rgangara 11-Nov-02 Added logic to insert into IVSTARK extension table to hold
950 || additional *K data for small systems support. Bug 2643048
951 || rbezawad 02-Dec-2002 Removed the TO_NUMBER() conversion while importing data into IGS_UC_MV_IVSTARK.SPECIALNEEDS column.
952 || This is done w.r.t. Bug 2620166 as there is change in Hercules data model.
953 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
954 || replaced igs_uc_mv_ivstark with igs_uc_istark_ints which
955 || includes columns of igs_uc_ss_ivstark table also
956 || smaddali 7-aug-03 Modified procedure logic for updating *N INTS record for bug 3085770
957 */
958
959
960 l_scn igs_uc_istark_ints.scn%TYPE := NULL;
961 l_regno igs_uc_istark_ints.regno%TYPE := NULL;
962 ln_appno igs_uc_istark_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
963
964 -- Get matching record from starN int table for the passed hercules record
965 CURSOR c_starn_int( cp_appno igs_uc_istarn_ints.appno%TYPE ) IS
966 SELECT a.rowid ,a.namechangedate , a.title , a.forenames, a.surname
967 FROM igs_uc_istarn_ints a
968 WHERE record_status = 'N'
969 AND appno = cp_appno ;
970
971 c_starn_int_rec c_starn_int%ROWTYPE ;
972
973 -- get the name details for this applicant
974 CURSOR c_app_name (cp_appno igs_uc_app_names.app_no%TYPE ) IS
975 SELECT name_change_date , title , fore_names , surname
976 FROM igs_uc_app_names
977 WHERE app_no = cp_appno ;
978 c_app_name_rec c_app_name%ROWTYPE ;
979
980 -- Get the name details from the Starn transaction for this applicant
981 CURSOR get_n_data( cp_appno NUMBER ) IS
982 SELECT record_data
983 FROM igs_uc_load_mv_t
984 WHERE trans_type = '*N'
985 AND TO_NUMBER(TRIM(SUBSTR(record_data,1,8))) = cp_appno;
986
987 l_n_data igs_uc_load_mv_t.record_data%TYPE ;
988
989 l_namechangedate igs_uc_app_names.name_change_date%TYPE ;
990 l_title igs_uc_app_names.title%TYPE ;
991 l_forenames igs_uc_app_names.fore_names%TYPE ;
992 l_surname igs_uc_app_names.surname%TYPE ;
993
994 BEGIN
995
996 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
997 fnd_message.set_token('TTYPE','*K ');
998 fnd_message.set_token('APPNO', ln_appno);
999 fnd_file.put_line( fnd_file.log, fnd_message.get());
1000
1001 -- If the value stored in the 69th position is Numeric then the data goes into
1002 -- Regno column else data goes into scn column
1003 IF is_numeric(TRIM(SUBSTR(p_record_data,69,1))) THEN
1004 l_regno := TRIM(SUBSTR(p_record_data,69,10));
1005 ELSE
1006 l_scn := TRIM(SUBSTR(p_record_data,69,9));
1007 END IF;
1008
1009 -- Obsolete matching records in starK interface table with status N
1010 UPDATE igs_uc_istark_ints SET record_status = 'O'
1011 WHERE record_status = 'N' AND appno = ln_appno ;
1012
1013 INSERT INTO igs_uc_istark_ints(
1014 appno,
1015 applicationdate,
1016 sentdate,
1017 runsent,
1018 codedchangedate,
1019 school,
1020 rescat,
1021 feelevel,
1022 feepayer,
1023 feetext,
1024 apr,
1025 lea,
1026 countrybirth,
1027 nationality,
1028 dualnationality,
1029 withdrawn,
1030 withdrawndate,
1031 routeb,
1032 examchangedate,
1033 alevels,
1034 aslevels,
1035 highers,
1036 csys,
1037 gce,
1038 vce,
1039 sqa,
1040 winter,
1041 previousa,
1042 previousas,
1043 keyskills,
1044 vocational,
1045 gnvq,
1046 btec,
1047 ilc,
1048 aice,
1049 ib,
1050 manual,
1051 regno,
1052 scn,
1053 oeq,
1054 prevoeq,
1055 eas,
1056 roa,
1057 specialneeds,
1058 criminalconv,
1059 ukentrydate,
1060 status,
1061 firmnow,
1062 firmreply,
1063 insurancereply,
1064 confhistfirmreply,
1065 confhistinsurancereply,
1066 choicesalltransparent,
1067 extrastatus,
1068 extrapassportno,
1069 welshspeaker ,
1070 ninumber ,
1071 earlieststart,
1072 nearinst ,
1073 prefreg ,
1074 qualeng ,
1075 qualmath ,
1076 qualsci ,
1077 mainqual ,
1078 qual5 ,
1079 record_status,
1080 error_code
1081 )
1082 VALUES
1083 (
1084 ln_appno, -- APPNO,
1085 TO_DATE(TRIM(SUBSTR(p_record_data,24,6)), 'DDMMRR'), -- APPLICATIONDATE,
1086 NULL, -- SENTDATE,
1087 TO_NUMBER(TRIM(SUBSTR(p_record_data,30,3))), -- RUNSENT,
1088 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- CODEDCHANGEDATE,
1089 TO_NUMBER(TRIM(SUBSTR(p_record_data,39,5))), -- SCHOOL,
1090 TRIM(SUBSTR(p_record_data,45,1)), -- RESCAT,
1091 NULL, -- FEELEVEL,
1092 TO_NUMBER(TRIM(SUBSTR(p_record_data,56,2))), -- FEEPAYER,
1093 NULL, -- FEETEXT,
1094 TO_NUMBER(TRIM(SUBSTR(p_record_data,47,3))), -- APR,
1095 NULL, -- LEA
1096 TO_NUMBER(TRIM(SUBSTR(p_record_data,50,3))), -- COUNTRYBIRTH,
1097 TO_NUMBER(TRIM(SUBSTR(p_record_data,53,3))), -- NATIONALITY,
1098 NULL, -- DUALNATIONALITY,
1099 TRIM(SUBSTR(p_record_data,17,1)), -- WITHDRAWN,
1100 TO_DATE(TRIM(SUBSTR(p_record_data,18,6)), 'DDMMRR'), -- WITHDRAWNDATE,
1101 DECODE(TRIM(SUBSTR(p_record_data,80,1)),'B','Y','N'), -- ROUTEB,
1102 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- EXAMCHANGEDATE,
1103 NULL, -- ALEVELS,
1104 NULL, -- ASLEVELS,
1105 NULL, -- HIGHERS,
1106 NULL, -- CSYS,
1107 TO_NUMBER(TRIM(SUBSTR(p_record_data,58,1))), -- GCE,
1108 TO_NUMBER(TRIM(SUBSTR(p_record_data,59,1))), -- VCE,
1109 TRIM(SUBSTR(p_record_data,61,1)), -- SQA,
1110 TO_NUMBER(TRIM(SUBSTR(p_record_data,65,1))), -- WINTER,
1111 TO_NUMBER(TRIM(SUBSTR(p_record_data,66,1))), -- PREVIOUSA,
1112 TO_NUMBER(TRIM(SUBSTR(p_record_data,119,1))), -- PREVIOUSAS,
1113 NULL, -- KEYSKILLS,
1114 NULL, -- VOCATIONAL,
1115 NULL, -- GNVQ
1116 DECODE(TRIM(SUBSTR(p_record_data,62,1)),'B','Y','N'), -- BTEC,
1117 DECODE(TRIM(SUBSTR(p_record_data,64,1)),'I','Y','N'), -- ILC,
1118 NULL, -- AICE,
1119 DECODE(TRIM(SUBSTR(p_record_data,63,1)),'I','Y',NULL), -- IB,
1120 NULL, -- MANUAL,
1121 l_regno, -- REGNO,
1122 l_scn, -- SCN,
1123 TRIM(SUBSTR(p_record_data,79,1)), -- OEQ,
1124 NULL, -- PREVOEQ,
1125 NVL(TRIM(SUBSTR(p_record_data,105,1)),'P'), -- EAS,
1126 TRIM(SUBSTR(p_record_data,68,1)), -- ROA,
1127 TRIM(SUBSTR(p_record_data,46,1)), -- SPECIALNEEDS,
1128 NULL, -- CRIMINALCONV,
1129 NULL, -- UKENTRYDATE,
1130 NULL, -- STATUS,
1131 NULL, -- FIRMNOW,
1132 NULL, -- FIRMREPLY,
1133 NULL, -- INSURANCEREPLY,
1134 NULL, -- CONFHISTFIRMREPLY,
1135 NULL, -- CONFHISTINSURANCEREPLY,
1136 DECODE(TRIM(SUBSTR(p_record_data,120,1)),'Y','Y','N'), -- CHOICESALLTRANSPARENT,
1137 NULL, -- EXTRASTATUS,
1138 NULL, -- EXTRAPASSPORTNO,
1139 TRIM(SUBSTR(p_record_data,81,1)), -- WELSHSPEAKER
1140 TRIM(SUBSTR(p_record_data,82,9)), -- NINUMBER
1141 TRIM(SUBSTR(p_record_data,91,4)), -- EARLIESTSTART
1142 TRIM(SUBSTR(p_record_data,95,4)), -- NEARINST
1143 TO_NUMBER(TRIM(SUBSTR(p_record_data,99,1))), -- PREFREG
1144 TRIM(SUBSTR(p_record_data,100,1)), -- QUALENG
1145 TRIM(SUBSTR(p_record_data,101,1)), -- QUALMATH
1146 TRIM(SUBSTR(p_record_data,102,1)), -- QUALSCI
1147 TRIM(SUBSTR(p_record_data,103,1)), -- MAINQUAL
1148 TRIM(SUBSTR(p_record_data,104,1)), -- QUAL5
1149 'N', -- RECORD_STATUS,
1150 NULL -- ERROR_CODE
1151 );
1152
1153 IF TO_DATE(TRIM(SUBSTR(p_record_data,33,6)), 'DDMMRR') IS NOT NULL OR
1154 TRIM(SUBSTR(p_record_data,44,1)) IS NOT NULL THEN
1155
1156
1157 l_n_data := NULL ;
1158 l_namechangedate := NULL;
1159 l_title := NULL ;
1160 l_forenames := NULL ;
1161 l_surname := NULL ;
1162 -- Check if *N tran exists in this flat file
1163 l_n_data := NULL ;
1164 OPEN get_n_data(ln_appno);
1165 FETCH get_n_data INTO l_n_data ;
1166 IF get_n_data%NOTFOUND THEN
1167 -- if starN tran doesn't exist in this file then , check if *N INTS record
1168 -- was created by earlier flat file , if so retain Name fields in *N INTS table
1169 c_starn_int_rec := NULL ;
1170 OPEN c_starn_int( ln_appno ) ;
1171 FETCH c_starn_int INTO c_starn_int_rec;
1172 CLOSE c_starn_int;
1173
1174 -- Else if *N INTS record is not present or if name fields are NULL then Get values
1175 -- from igs_uc_app_names
1176 c_app_name_rec := NULL;
1177 OPEN c_app_name( ln_appno ) ;
1178 FETCH c_app_name INTO c_app_name_rec ;
1179 CLOSE c_app_name ;
1180
1181 -- If existing *N INTS record has NULL values for dob and sex then , overwrite with appnamesvalues
1182 l_namechangedate := NVL(c_starn_int_rec.namechangedate,c_app_name_rec.name_change_date) ;
1183 l_title := NVL(c_starn_int_rec.title, c_app_name_rec.title) ;
1184 l_forenames := NVL(c_starn_int_rec.forenames, c_app_name_rec.fore_names) ;
1185 l_surname := NVL(c_starn_int_rec.surname, c_app_name_rec.surname) ;
1186
1187 ELSE
1188 -- if *N transaction exists for this appno then get name details from there
1189 l_namechangedate := TO_DATE(TRIM(SUBSTR(l_n_data,11,6)), 'DDMMRR') ;
1190 l_title := UPPER(TRIM(SUBSTR(l_n_data,17,4))) ;
1191 l_forenames := TRIM(SUBSTR(l_n_data,42,24));
1192 l_surname := TRIM(SUBSTR(l_n_data,24,18)) ;
1193
1194 END IF ;
1195 CLOSE get_n_data;
1196
1197
1198 -- check if a *N record already exists for this record , if so update it
1199 -- else create a new record with values derived as above
1200 c_starn_int_rec := NULL ;
1201 OPEN c_starn_int( ln_appno ) ;
1202 FETCH c_starn_int INTO c_starn_int_rec ;
1203 IF c_starn_int%FOUND THEN
1204 UPDATE igs_uc_istarn_ints SET
1205 namechangedate = NVL(l_namechangedate,namechangedate),
1206 title = NVL(l_title,title),
1207 forenames = NVL(l_forenames,forenames),
1208 surname = NVL(l_surname,surname) ,
1209 birthdate = TO_DATE(TRIM(SUBSTR(p_record_data,33,6)), 'DDMMRR') ,
1210 sex = TRIM(SUBSTR(p_record_data,44,1)),
1211 ad_batch_id = NULL ,
1212 ad_interface_id = NULL ,
1213 ad_api_id = NULL ,
1214 error_code = NULL
1215 WHERE rowid = c_starn_int_rec.rowid ;
1216 ELSE
1217 INSERT INTO igs_uc_istarn_ints (
1218 appno,
1219 checkdigit,
1220 namechangedate,
1221 title,
1222 forenames,
1223 surname,
1224 birthdate,
1225 sex,
1226 ad_batch_id ,
1227 ad_interface_id ,
1228 ad_api_id ,
1229 record_status,
1230 error_code
1231 )
1232 VALUES
1233 (
1234 ln_appno, -- APPNO,
1235 TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))), -- CHECKDIGIT,
1236 l_namechangedate, -- NAMECHANGEDATE,
1237 l_title, -- TITLE,
1238 l_forenames, -- FORENAMES,
1239 l_surname, -- SURNAME,
1240 TO_DATE(TRIM(SUBSTR(p_record_data,33,6)), 'DDMMRR'), -- BIRTHDATE,
1241 TRIM(SUBSTR(p_record_data,44,1)), -- SEX,
1242 NULL, -- AD_BATCH_ID
1243 NULL, -- AD_INTERFACE_ID
1244 NULL, -- AD_API_ID
1245 'N', -- RECORD_STATUS,
1246 NULL -- ERROR_CODE
1247 );
1248
1249 END IF ;
1250 CLOSE c_starn_int ;
1251
1252 END IF ; -- process birthdate and sex fields
1253
1254 -- Increase the success record count.
1255 g_success_cnt := g_success_cnt +1;
1256
1257 EXCEPTION
1258 WHEN VALUE_ERROR THEN
1259 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1260 fnd_message.set_token('TTYPE',p_trans_type);
1261 fnd_file.put_line( fnd_file.log, fnd_message.get());
1262 igs_ge_msg_stack.add;
1263 app_exception.raise_exception;
1264
1265 WHEN OTHERS THEN
1266 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1267 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARK - '||SQLERRM);
1268 igs_ge_msg_stack.add;
1269 app_exception.raise_exception;
1270
1271 END transfer_to_stark;
1272
1273
1274 PROCEDURE transfer_to_starn(
1275 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1276 p_record_data igs_uc_load_mv_t.record_data%TYPE
1277 ) AS
1278 /*
1279 || Created By : brajendr
1280 || Created On :
1281 || Purpose : Inserts the given *N transaction record into igs_uc_mv_ivstarn table
1282 || Known limitations, enhancements or remarks :
1283 || Change History :
1284 || Who When What
1285 || (reverse chronological order - newest change first)
1286 || rgangara 28 Nov 02 For Y2K problem found during testing.
1287 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1288 || replaced igs_uc_mv_ivstarn with igs_uc_istarn_ints
1289 || smaddali 7-aug-03 Modified procedure logic for updating *N INTS record for bug 3085770
1290 */
1291
1292 /* rgangara 28-Nov-02
1293 Modified by rgangara to overcome Y2K issue for birthdate. The date of birth data that comes in from UCAS
1294 is in DDMMYY format. As such when this is converted and populated into the table, it is saved as DDMMYYYY.
1295 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
1296 UCAS since it is in DDMMYY format. However, when this is stored in a table and queried, it would 01-Jan-2072
1297 which would through up errors. Hence since it is assumed that the Applicants in UCAS would have birthdates in
1298 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'.
1299 */
1300
1301 ln_appno igs_uc_istarn_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1302
1303 -- Get the details of birthdate and sex from the Stark transaction
1304 CURSOR get_k_data( cp_appno NUMBER ) IS
1305 SELECT record_data
1306 FROM igs_uc_load_mv_t
1307 WHERE trans_type = '*K'
1308 AND TO_NUMBER(TRIM(SUBSTR(record_data,1,8))) = cp_appno;
1309
1310 l_k_data igs_uc_load_mv_t.record_data%TYPE ;
1311
1312 -- Get matching record from starN int table for the passed hercules record
1313 CURSOR c_starn_int( cp_appno igs_uc_istarn_ints.appno%TYPE ) IS
1314 SELECT a.rowid , a.sex, a.birthdate
1315 FROM igs_uc_istarn_ints a
1316 WHERE record_status = 'N'
1317 AND appno = cp_appno ;
1318
1319 c_starn_int_rec c_starn_int%ROWTYPE ;
1320
1321 l_birthdate igs_uc_istarn_ints.birthdate%TYPE ;
1322 l_sex igs_uc_istarn_ints.sex%TYPE ;
1323
1324 -- get the name details for this applicant
1325 CURSOR c_app_name (cp_appno igs_uc_app_names.app_no%TYPE ) IS
1326 SELECT birth_date , sex
1327 FROM igs_uc_app_names
1328 WHERE app_no = cp_appno ;
1329 c_app_name_rec c_app_name%ROWTYPE ;
1330
1331 BEGIN
1332
1333 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1334 fnd_message.set_token('TTYPE','*N');
1335 fnd_message.set_token('APPNO', ln_appno);
1336 fnd_file.put_line( fnd_file.log, fnd_message.get());
1337
1338 -- initialising variables
1339 l_k_data := NULL ;
1340 l_birthdate := NULL;
1341 l_sex := NULL ;
1342 -- Check if *K transation exists in this flat file
1343 l_k_data := NULL ;
1344 OPEN get_k_data(ln_appno);
1345 FETCH get_k_data INTO l_k_data ;
1346 IF get_k_data%NOTFOUND THEN
1347
1348 -- if stark tran doesn't exist in this tran then , check if *N INTS record
1349 -- was created by earlier flat file , if so retain dob and sex in *N INTS table
1350 c_starn_int_rec := NULL;
1351 OPEN c_starn_int( ln_appno ) ;
1352 FETCH c_starn_int INTO c_starn_int_rec;
1353 CLOSE c_starn_int;
1354
1355 -- Else if *N INTS record is not present or if dob and sex are NULL then Get values from igs_uc_app_names
1356 c_app_name_rec := NULL ;
1357 OPEN c_app_name( ln_appno ) ;
1358 FETCH c_app_name INTO c_app_name_rec ;
1359 CLOSE c_app_name ;
1360
1361 -- If existing *N INTS record has NULL values for dob and sex then , overwrite with appnamesvalues
1362 l_birthdate := NVL(c_starn_int_rec.birthdate, c_app_name_rec.birth_date);
1363 l_sex := NVL(c_starn_int_rec.sex, c_app_name_rec.sex) ;
1364
1365 ELSE
1366 -- if *K transaction exists for this appno then get dob and sex details from there
1367 l_birthdate := TO_DATE(TRIM(SUBSTR(l_k_data,33,6)), 'DDMMRR') ;
1368 l_sex := TRIM(SUBSTR(l_k_data,44,1)) ;
1369 END IF ;
1370 CLOSE get_k_data;
1371
1372 -- check if a *N record already exists for this appno with record_status 'N', if so update it
1373 -- else create a new record with values derived as above and the current transaction
1374 c_starn_int_rec := NULL;
1375 OPEN c_starn_int( ln_appno ) ;
1376 FETCH c_starn_int INTO c_starn_int_rec;
1377 IF c_starn_int%FOUND THEN
1378 UPDATE igs_uc_istarn_ints SET
1379 checkdigit = TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))) ,
1380 namechangedate = TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR') ,
1381 title = UPPER(TRIM(SUBSTR(p_record_data,17,4))) ,
1382 forenames = TRIM(SUBSTR(p_record_data,42,24)) ,
1383 surname = TRIM(SUBSTR(p_record_data,24,18)) ,
1384 birthdate = NVL(l_birthdate,birthdate),
1385 sex = NVL(l_sex,sex) ,
1386 ad_batch_id = NULL ,
1387 ad_interface_id = NULL ,
1388 ad_api_id = NULL ,
1389 error_code = NULL
1390 WHERE rowid = c_starn_int_rec.rowid ;
1391 ELSE
1392 INSERT INTO igs_uc_istarn_ints (
1393 appno,
1394 checkdigit,
1395 namechangedate,
1396 title,
1397 forenames,
1398 surname,
1399 birthdate,
1400 sex,
1401 ad_batch_id ,
1402 ad_interface_id ,
1403 ad_api_id ,
1404 record_status,
1405 error_code
1406 )
1407 VALUES
1408 (
1409 ln_appno, -- APPNO,
1410 TO_NUMBER(TRIM(SUBSTR(p_record_data,9,1))), -- CHECKDIGIT,
1411 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- NAMECHANGEDATE,
1412 UPPER(TRIM(SUBSTR(p_record_data,17,4))), -- TITLE,
1413 TRIM(SUBSTR(p_record_data,42,24)), -- FORENAMES,
1414 TRIM(SUBSTR(p_record_data,24,18)), -- SURNAME,
1415 l_birthdate, -- BIRTHDATE,
1416 l_sex, -- SEX,
1417 NULL, -- AD_BATCH_ID
1418 NULL, -- AD_INTERFACE_ID
1419 NULL, -- AD_API_ID
1420 'N', -- RECORD_STATUS,
1421 NULL -- ERROR_CODE
1422 );
1423
1424 END IF ;
1425 CLOSE c_starn_int ;
1426
1427
1428 -- Increase the success record count.
1429 g_success_cnt := g_success_cnt +1;
1430
1431 EXCEPTION
1432 WHEN VALUE_ERROR THEN
1433 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1434 fnd_message.set_token('TTYPE',p_trans_type);
1435 fnd_file.put_line( fnd_file.log, fnd_message.get());
1436 igs_ge_msg_stack.add;
1437 app_exception.raise_exception;
1438
1439 WHEN OTHERS THEN
1440 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1441 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARN - '||SQLERRM);
1442 igs_ge_msg_stack.add;
1443 app_exception.raise_exception;
1444
1445 END transfer_to_starn;
1446
1447
1448 PROCEDURE transfer_to_starpqr(
1449 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1450 p_record_data igs_uc_load_mv_t.record_data%TYPE
1451 ) AS
1452 /*
1453 || Created By : brajendr
1454 || Created On :
1455 || Purpose : Inserts the given *P *R transactions record into igs_uc_mv_ivstarpqr and igs_uc_mv_ivqual tables
1456 || Known limitations, enhancements or remarks :
1457 || Change History :
1458 || Who When What
1459 || (reverse chronological order - newest change first)
1460 || rbezawad 25-Sep-2002 Removed code which is populating IGS_UC_MV_IVQUAL.GNVQDATE as the column is obsoleted.
1461 || 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
1462 || 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.
1463 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1464 || replaced igs_uc_mv_ivstarpqr with igs_uc_istrpqr_ints
1465 || smaddali 26-aug-03 Modified procedure for *P transaction , to populate grade1 and 2 fields instead of Grade field, for bug#3114629
1466 || smaddali 4-sep-03 Obsoleting existing 'N' records for bug#3122898
1467 */
1468
1469 ln_appno igs_uc_istrpqr_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1470
1471 -- smaddali added the declarations for bug# 2749404
1472 l_sitting igs_uc_istrpqr_ints.sitting%TYPE;
1473 l_grade1 igs_uc_istrpqr_ints.grade1%TYPE;
1474 l_grade2 igs_uc_istrpqr_ints.grade2%TYPE;
1475 l_ebl_code igs_uc_istrpqr_ints.eblsubject%TYPE ;
1476 l_matchind igs_uc_istrpqr_ints.matchind%TYPE := TRIM(SUBSTR(p_record_data,17,1)) ;
1477 l_exam_board_code igs_uc_istrpqr_ints.examboard%TYPE;
1478 l_year_date igs_uc_istrpqr_ints.yearofexam%TYPE ;
1479 l_lending_board igs_uc_istrpqr_ints.lendingboard%TYPE ;
1480 l_pr_start_pos NUMBER;
1481 -- end of change by smaddali for bug#2749404
1482
1483
1484 BEGIN
1485
1486 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1487 fnd_message.set_token('TTYPE','*PQR');
1488 fnd_message.set_token('APPNO', ln_appno);
1489 fnd_file.put_line( fnd_file.log, fnd_message.get());
1490
1491 -- update all records in interface table for the current applicant ,marvin type with status L
1492 -- to status D,i.e processed for this applicant
1493 UPDATE igs_uc_istrpqr_ints SET record_status = 'D'
1494 WHERE record_status = 'L' AND appno = ln_appno
1495 AND marvin_type = SUBSTR(p_trans_type,2,1) ;
1496
1497 -- update all records in interface table for the current applicant, marvin type with status N
1498 -- to status O,i.e obsolete for this applicant
1499 UPDATE igs_uc_istrpqr_ints SET record_status = 'O' , error_code = NULL
1500 WHERE record_status = 'N' AND appno = ln_appno
1501 AND marvin_type = SUBSTR(p_trans_type,2,1) ;
1502
1503 -- reinstate records with status L of the other marvin type for this applicant
1504 IF p_trans_type = '*R' THEN
1505 -- set matching records in interface table with status L to N
1506 UPDATE igs_uc_istrpqr_ints SET record_status = 'N'
1507 WHERE record_status = 'L' AND appno = ln_appno
1508 AND marvin_type = 'P' ;
1509 ELSIF p_trans_type = '*P' THEN
1510 -- set matching records in interface table with status L to N
1511 UPDATE igs_uc_istrpqr_ints SET record_status = 'N'
1512 WHERE record_status = 'L' AND appno = ln_appno
1513 AND marvin_type = 'R';
1514 END IF ;
1515
1516 --Assign the Previous Result details starting location.
1517 l_pr_start_pos := 18;
1518
1519 --To loop through the maximum 21 sets of previous results i.e., from 18 to 227 positions
1520 -- and insert previous results data into igs_uc_istrpqr_ints table.
1521 WHILE TRIM(SUBSTR(p_record_data,l_pr_start_pos,2)) IS NOT NULL AND l_pr_start_pos <= 218 LOOP
1522 -- extract field values
1523 l_year_date := LPAD(TRIM(SUBSTR(p_record_data,l_pr_start_pos,2)),2,'0') ;
1524 l_sitting := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+2),1)) ; --Extracts String from Position 20
1525 l_exam_board_code := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+3),1)) ; --Extracts String from Position 21
1526 l_ebl_code := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+4),3)) ; --Extracts String from Positions 22,23,24
1527 -- smaddali moved the population of these fields grade1,2 to be common for both *P and *R transactions, bug#3114629
1528 l_grade1 := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+7),1)) ; --Extracts String from Position 25
1529 l_grade2 := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+8),1)) ; --Extracts String from Position 26
1530
1531 IF p_trans_type = '*P' THEN
1532 l_matchind := NULL ;
1533 l_lending_board := NULL ;
1534 ELSIF p_trans_type = '*R' THEN
1535 l_lending_board := TRIM(SUBSTR(p_record_data,(l_pr_start_pos+9),1)) ;
1536 END IF ; -- end of *p/*r transaction
1537
1538 -- Obsolete matching records in interface table with status N
1539 UPDATE igs_uc_istrpqr_ints SET record_status = 'O'
1540 WHERE record_status = 'N' AND appno = ln_appno
1541 AND yearofexam = l_year_date AND sitting = l_sitting
1542 AND examboard = l_exam_board_code AND eblsubject = l_ebl_code ;
1543
1544 INSERT INTO igs_uc_istrpqr_ints(
1545 appno,
1546 subjectid,
1547 eblresult,
1548 eblamended,
1549 claimedresult,
1550 yearofexam,
1551 sitting,
1552 examboard ,
1553 eblsubject,
1554 grade,
1555 grade1,
1556 grade2,
1557 lendingboard,
1558 matchind ,
1559 marvin_type ,
1560 record_status,
1561 error_code
1562 )
1563 VALUES
1564 (
1565 ln_appno, -- APPNO,
1566 NULL, -- SUBJECTID,
1567 NULL, -- EBLRESULT,
1568 NULL, -- EBLAMENDED,
1569 NULL, -- CLAIMEDRESULT,
1570 l_year_date, -- YEAROFEXAM
1571 l_sitting, -- SITTING
1572 l_exam_board_code, -- EXAMBOARD
1573 l_ebl_code, -- EBLSUBJECT
1574 NULL, -- GRADE
1575 l_grade1, -- GRADE1
1576 l_grade2, -- GRADE2
1577 l_lending_board, -- LENDINGBOARD
1578 l_matchind, -- MATCHIND
1579 SUBSTR(p_trans_type,2,1), -- MARVIN_TYPE
1580 'N', -- RECORD_STATUS,
1581 NULL -- ERROR_CODE
1582 );
1583
1584 -- Increase the success record count.
1585 g_success_cnt := g_success_cnt +1;
1586
1587 l_pr_start_pos := l_pr_start_pos + 10;
1588
1589 END LOOP; --Previous Results Loop
1590
1591
1592 EXCEPTION
1593 WHEN VALUE_ERROR THEN
1594 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1595 fnd_message.set_token('TTYPE',p_trans_type);
1596 fnd_file.put_line( fnd_file.log, fnd_message.get());
1597 igs_ge_msg_stack.add;
1598 app_exception.raise_exception;
1599
1600 WHEN OTHERS THEN
1601 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1602 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARPQR - '||SQLERRM);
1603 igs_ge_msg_stack.add;
1604 app_exception.raise_exception;
1605
1606 END transfer_to_starpqr;
1607
1608
1609
1610 PROCEDURE transfer_to_start(
1611 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1612 p_record_data igs_uc_load_mv_t.record_data%TYPE
1613 ) AS
1614 /*
1615 || Created By : rgangara
1616 || Created On : 12-Nov-02
1617 || Purpose : Inserts the given *T (General Social Care Council Data) transaction data into igs_uc_mv_ivstarT table
1618 || Known limitations, enhancements or remarks :
1619 || Change History :
1620 || Who When What
1621 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1622 || replaced igs_uc_mv_ivstart with igs_uc_istart_ints
1623 || (reverse chronological order - newest change first)
1624 ||
1625 */
1626
1627 ln_appno igs_uc_istart_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1628
1629 BEGIN
1630
1631 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1632 fnd_message.set_token('TTYPE','*T');
1633 fnd_message.set_token('APPNO', ln_appno);
1634 fnd_file.put_line( fnd_file.log, fnd_message.get());
1635
1636 -- Obsolete matching records in interface table with status N
1637 UPDATE igs_uc_istart_ints SET record_status = 'O'
1638 WHERE record_status = 'N' AND appno = ln_appno ;
1639
1640 INSERT INTO igs_uc_istart_ints(
1641 appno ,
1642 lastchange ,
1643 futureserv ,
1644 futureset ,
1645 presentserv ,
1646 presentset ,
1647 curremp ,
1648 eduqual ,
1649 record_status,
1650 error_code
1651 )
1652 VALUES
1653 (
1654 ln_appno, -- APPNO,
1655 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)),'DDMMRR'), -- LASTCHANGE
1656 TRIM(SUBSTR(p_record_data,17,1)), -- FUTURESERV
1657 TRIM(SUBSTR(p_record_data,18,1)), -- FUTUTRESET
1658 TRIM(SUBSTR(p_record_data,19,1)), -- PRESENTSERV
1659 TRIM(SUBSTR(p_record_data,20,1)), -- PRSENTSET
1660 TRIM(SUBSTR(p_record_data,21,1)), -- CURREMP
1661 TRIM(SUBSTR(p_record_data,22,2)), -- EDUQUAL
1662 'N', -- RECORD_STATUS,
1663 NULL -- ERROR_CODE
1664 );
1665
1666 -- Increase the success record count.
1667 g_success_cnt := g_success_cnt +1;
1668
1669 EXCEPTION
1670 WHEN VALUE_ERROR THEN
1671 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1672 fnd_message.set_token('TTYPE',p_trans_type);
1673 fnd_file.put_line( fnd_file.log, fnd_message.get());
1674 igs_ge_msg_stack.add;
1675 app_exception.raise_exception;
1676
1677 WHEN OTHERS THEN
1678 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1679 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_START - '||SQLERRM);
1680 igs_ge_msg_stack.add;
1681 app_exception.raise_exception;
1682
1683 END transfer_to_start;
1684
1685
1686
1687
1688 PROCEDURE transfer_to_starw(
1689 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1690 p_record_data igs_uc_load_mv_t.record_data%TYPE
1691 ) AS
1692 /*
1693 || Created By : brajendr
1694 || Created On :
1695 || Purpose : Inserts the given *W transaction record into igs_uc_mv_ivstarw table
1696 || Known limitations, enhancements or remarks :
1697 || Change History :
1698 || Who When What
1699 || (reverse chronological order - newest change first)
1700 ||
1701 || rbezawad 24-Sep-2002 Added code to populate igs_uc_mv_ivstarw.JOINTADMISSION column value based on value in 17 column position.
1702 || Modified w.r.t. UCFD06 Build 2574566.
1703 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1704 || replaced igs_uc_mv_ivstarw with igs_uc_istarw_ints
1705 */
1706
1707 ln_appno igs_uc_istarw_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1708
1709 BEGIN
1710
1711 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1712 fnd_message.set_token('TTYPE','*W');
1713 fnd_message.set_token('APPNO', ln_appno);
1714 fnd_file.put_line( fnd_file.log, fnd_message.get());
1715
1716 -- Obsolete matching records in interface table with status N
1717 UPDATE igs_uc_istarw_ints SET record_status = 'O'
1718 WHERE record_status = 'N' AND appno = ln_appno ;
1719
1720 INSERT INTO igs_uc_istarw_ints(
1721 appno,
1722 miscoded,
1723 cancelled,
1724 canceldate,
1725 remark,
1726 jointadmission,
1727 choice1lost,
1728 choice2lost,
1729 choice3lost,
1730 choice4lost,
1731 choice5lost,
1732 choice6lost,
1733 choice7lost,
1734 record_status,
1735 error_code
1736 )
1737 VALUES
1738 (
1739 ln_appno, -- APPNO,
1740 DECODE(TRIM(SUBSTR(p_record_data,17,1)),'M','Y','N'), -- MISCODED,
1741 DECODE(TRIM(SUBSTR(p_record_data,17,1)),'C','Y','N'), -- CANCELLED,
1742 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- CANCELDATE,
1743 NULL, -- REMARK,
1744 DECODE(TRIM(SUBSTR(p_record_data,17,1)),'J','Y','N'), -- JOINTADMISSION
1745 'N', -- CHOICE1LOST
1746 'N', -- CHOICE2LOST
1747 'N', -- CHOICE3LOST
1748 'N', -- CHOICE4LOST
1749 'N', -- CHOICE5LOST
1750 'N', -- CHOICE6LOST
1751 'N', -- CHOICE7LOST
1752 'N', -- RECORD_STATUS,
1753 NULL -- ERROR_CODE
1754 );
1755
1756 -- Increase the success record count.
1757 g_success_cnt := g_success_cnt +1;
1758
1759 EXCEPTION
1760 WHEN VALUE_ERROR THEN
1761 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1762 fnd_message.set_token('TTYPE',p_trans_type);
1763 fnd_file.put_line( fnd_file.log, fnd_message.get());
1764 igs_ge_msg_stack.add;
1765 app_exception.raise_exception;
1766
1767 WHEN OTHERS THEN
1768 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1769 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARW - '||SQLERRM);
1770 igs_ge_msg_stack.add;
1771 app_exception.raise_exception;
1772
1773 END transfer_to_starw;
1774
1775
1776 PROCEDURE transfer_to_starx(
1777 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1778 p_record_data igs_uc_load_mv_t.record_data%TYPE
1779 ) AS
1780 /*
1781 || Created By : brajendr
1782 || Created On :
1783 || Purpose : Inserts the given *X transaction record into igs_uc_mv_ivstarx table
1784 || Known limitations, enhancements or remarks :
1785 || Change History :
1786 || Who When What
1787 || (reverse chronological order - newest change first)
1788 || rbezawad 25-Sep-2002 Modified population of POCC field to get 4 characters.
1789 ||
1790 || rgangara 11-Nov-02 Added logic to insert into IVSTARX extension table to hold
1791 || additional *X data for small systems support. Bug 2643048
1792 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1793 || replaced igs_uc_mv_ivstarx with igs_uc_istarx_ints which
1794 || includes columns of igs_uc_ss_ivstarx table also
1795 */
1796
1797 ln_appno igs_uc_istarx_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1798
1799 BEGIN
1800
1801 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1802 fnd_message.set_token('TTYPE','*X');
1803 fnd_message.set_token('APPNO', ln_appno);
1804 fnd_file.put_line( fnd_file.log, fnd_message.get());
1805
1806 -- Obsolete matching records in interface table with status N
1807 UPDATE igs_uc_istarx_ints SET record_status = 'O'
1808 WHERE record_status = 'N' AND appno = ln_appno ;
1809
1810
1811 INSERT INTO igs_uc_istarx_ints(
1812 appno,
1813 ethnic,
1814 pocceduchangedate,
1815 socialclass,
1816 pocc,
1817 pocctext,
1818 socioeconomic,
1819 occbackground,
1820 religion,
1821 dependants,
1822 married,
1823 record_status,
1824 error_code
1825 )
1826 VALUES
1827 (
1828 ln_appno, -- APPNO,
1829 TO_NUMBER(TRIM(SUBSTR(p_record_data,17,2))), -- ETHNIC,
1830 TO_DATE(TRIM(SUBSTR(p_record_data,11,6)), 'DDMMRR'), -- POCCEDUCHANGEDATE,
1831 TRIM(SUBSTR(p_record_data,19,1)), -- SOCIALCLASS,
1832 TRIM(SUBSTR(p_record_data,28,4)), -- POCC,
1833 NULL, -- POCCTEXT,
1834 TRIM(SUBSTR(p_record_data,27,1)), -- SOCIOECONOMIC,
1835 NULL, -- OCCBACKGROUND,
1836 TO_NUMBER(TRIM(SUBSTR(p_record_data,23,1))), -- RELIGION
1837 TO_NUMBER(TRIM(SUBSTR(p_record_data,24,2))), -- DEPENDANTS
1838 TRIM(SUBSTR(p_record_data,26,1)), -- MARRIED
1839 'N', -- RECORD_STATUS,
1840 NULL -- ERROR_CODE
1841 );
1842
1843 -- Increase the success record count.
1844 g_success_cnt := g_success_cnt +1;
1845
1846 EXCEPTION
1847 WHEN VALUE_ERROR THEN
1848 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1849 fnd_message.set_token('TTYPE',p_trans_type);
1850 fnd_file.put_line( fnd_file.log, fnd_message.get());
1851 igs_ge_msg_stack.add;
1852 app_exception.raise_exception;
1853
1854 WHEN OTHERS THEN
1855 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1856 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARX - '||SQLERRM);
1857 igs_ge_msg_stack.add;
1858 app_exception.raise_exception;
1859
1860 END transfer_to_starx;
1861
1862
1863 PROCEDURE transfer_to_starz(
1864 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
1865 p_record_data igs_uc_load_mv_t.record_data%TYPE
1866 ) AS
1867 /*
1868 || Created By : brajendr
1869 || Created On :
1870 || Purpose : Inserts the given *Z transaction record into igs_uc_mv_ivstarz1 and igs_uc_mv_ivstarz2 tables
1871 || Known limitations, enhancements or remarks :
1872 || Change History :
1873 || Who When What
1874 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
1875 || replaced igs_uc_mv_ivstarz1 with igs_uc_istarz1_ints and
1876 || igs_uc_mv_ivstarz2 with igs_uc_istarz2_ints
1877 || (reverse chronological order - newest change first)
1878 */
1879
1880 ln_position NUMBER;
1881 l_result igs_uc_istarz1_ints.result%TYPE := NULL;
1882 ln_appno igs_uc_istarz1_ints.appno%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
1883
1884 BEGIN
1885
1886 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APP');
1887 fnd_message.set_token('TTYPE','*Z');
1888 fnd_message.set_token('APPNO', ln_appno);
1889 fnd_file.put_line( fnd_file.log, fnd_message.get());
1890
1891 -- Result column contains 'A' if the Institution and course columns has the values
1892 IF (TRIM(SUBSTR(p_record_data,20,4)) IS NOT NULL) OR
1893 (TRIM(SUBSTR(p_record_data,24,6)) IS NOT NULL) THEN
1894 l_result := 'A';
1895 END IF;
1896
1897 -- Obsolete matching records in interface table with status N
1898 UPDATE igs_uc_istarz1_ints SET record_status = 'O'
1899 WHERE record_status = 'N' AND appno = ln_appno ;
1900
1901 -- Each transactio of Z will have 8 detials of Z transactions also.
1902 -- These detail z transaction shall be stored in igs_uc_mv_ivstarz2 table
1903 INSERT INTO igs_uc_istarz1_ints(
1904 appno,
1905 datecefsent,
1906 cefno,
1907 centralclearing,
1908 inst,
1909 course,
1910 campus,
1911 faculty,
1912 entryyear,
1913 entrymonth,
1914 entrypoint,
1915 result,
1916 record_status,
1917 error_code
1918 )
1919 VALUES
1920 (
1921 ln_appno, -- APPNO,
1922 NULL, -- DATECEFSENT,
1923 NULL, -- CEFNO,
1924 'N', -- CENTRALCLEARING,
1925 TRIM(SUBSTR(p_record_data,20,3)), -- INST,
1926 TRIM(SUBSTR(p_record_data,24,6)), -- COURSE,
1927 TRIM(SUBSTR(p_record_data,30,1)), -- CAMPUS,
1928 TRIM(SUBSTR(p_record_data,31,1)), -- FACULTY,
1929 TO_NUMBER(TRIM(SUBSTR(p_record_data,204,2))), -- ENTRYYEAR,
1930 TO_NUMBER(TRIM(SUBSTR(p_record_data,206,2))), -- ENTRYMONTH,
1931 TO_NUMBER(TRIM(SUBSTR(p_record_data,203,1))), -- ENTRYPOINT,
1932 l_result, -- RESULT,
1933 'N', -- RECORD_STATUS,
1934 NULL -- ERROR_CODE
1935 );
1936
1937 ln_position := 86;
1938
1939 FOR i in 1..8 LOOP
1940 IF TRIM(SUBSTR(p_record_data,ln_position,4)) IS NOT NULL THEN
1941
1942 -- Obsolete matching records in interface table with status N
1943 UPDATE igs_uc_istarz2_ints SET record_status = 'O'
1944 WHERE record_status = 'N' AND appno = ln_appno
1945 AND inst = TRIM(SUBSTR(p_record_data,ln_position,3))
1946 AND course = TRIM(SUBSTR(p_record_data,ln_position+4,6))
1947 AND campus = TRIM(SUBSTR(p_record_data,ln_position+10,1));
1948
1949 INSERT INTO igs_uc_istarz2_ints(
1950 appno,
1951 roundno,
1952 inst,
1953 course,
1954 campus,
1955 faculty,
1956 roundtype,
1957 result,
1958 record_status,
1959 error_code
1960 )
1961 VALUES
1962 (
1963 ln_appno, -- APPNO,
1964 NULL, -- ROUNDNO,
1965 TRIM(SUBSTR(p_record_data,ln_position,3)), -- INST,
1966 TRIM(SUBSTR(p_record_data,ln_position+4,6)), -- COURSE,
1967 TRIM(SUBSTR(p_record_data,ln_position+10,1)), -- CAMPUS,
1968 TRIM(SUBSTR(p_record_data,ln_position+11,1)), -- FACULTY,
1969 'F', -- ROUNDTYPE,
1970 TRIM(SUBSTR(p_record_data,ln_position+12,1)), -- RESULT,
1971 'N', -- RECORD_STATUS,
1972 NULL -- ERROR_CODE
1973 );
1974 END IF;
1975 ln_position := ln_position + 13;
1976
1977 END LOOP;
1978
1979 -- Increase the success record count.
1980 g_success_cnt := g_success_cnt +1;
1981
1982 EXCEPTION
1983 WHEN VALUE_ERROR THEN
1984 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
1985 fnd_message.set_token('TTYPE',p_trans_type);
1986 fnd_file.put_line( fnd_file.log, fnd_message.get());
1987 igs_ge_msg_stack.add;
1988 app_exception.raise_exception;
1989
1990 WHEN OTHERS THEN
1991 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1992 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_STARZ - '||SQLERRM);
1993 igs_ge_msg_stack.add;
1994 app_exception.raise_exception;
1995
1996 END transfer_to_starz;
1997
1998
1999 PROCEDURE transfer_to_qa(
2000 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
2001 p_record_data igs_uc_load_mv_t.record_data%TYPE
2002 ) AS
2003 /*
2004 || Created By : brajendr
2005 || Created On :
2006 || Purpose : Inserts the given QA transaction record into igs_uc_mv_uvofr_abv table
2007 || Known limitations, enhancements or remarks :
2008 || Change History :
2009 || Who When What
2010 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2011 || replaced igs_uc_mv_uvofr_abv with igs_uc_uofabrv_ints
2012 || (reverse chronological order - newest change first)
2013 */
2014
2015 BEGIN
2016
2017 -- Obsolete matching records in interface table with status N
2018 UPDATE igs_uc_uofabrv_ints SET record_status = 'O'
2019 WHERE record_status = 'N' AND abbrevid = TO_NUMBER(TRIM(SUBSTR(p_record_data,1,2)));
2020
2021 -- copy the marvin record into the ucas interface tables
2022 INSERT INTO igs_uc_uofabrv_ints(
2023 abbrevid,
2024 updater,
2025 abbrevtext,
2026 letterformat,
2027 summarychar,
2028 abbrevuse,
2029 record_status,
2030 error_code
2031 )
2032 VALUES
2033 (
2034 TO_NUMBER(TRIM(SUBSTR(p_record_data,1,2))), -- ABBREVID,
2035 NULL, -- UPDATER,
2036 TRIM(SUBSTR(p_record_data,5,57)), -- ABBREVTEXT,
2037 TRIM(SUBSTR(p_record_data,3,1)), -- LETTERFORMAT,
2038 TRIM(SUBSTR(p_record_data,4,1)), -- SUMMARYCHAR,
2039 TRIM(SUBSTR(p_record_data,4,1)), -- ABBREVUSE,
2040 'N', -- RECORD_STATUS,
2041 NULL -- ERROR_CODE
2042 );
2043
2044 -- Increase the success record count.
2045 g_success_cnt := g_success_cnt +1;
2046
2047 EXCEPTION
2048 WHEN VALUE_ERROR THEN
2049 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
2050 fnd_message.set_token('TTYPE',p_trans_type);
2051 fnd_file.put_line( fnd_file.log, fnd_message.get());
2052 igs_ge_msg_stack.add;
2053 app_exception.raise_exception;
2054
2055 WHEN OTHERS THEN
2056 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2057 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_QA - '||SQLERRM);
2058 igs_ge_msg_stack.add;
2059 app_exception.raise_exception;
2060
2061 END transfer_to_qa;
2062
2063
2064 PROCEDURE transfer_to_qc(
2065 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
2066 p_record_data igs_uc_load_mv_t.record_data%TYPE
2067 ) AS
2068 /*
2069 || Created By : brajendr
2070 || Created On :
2071 || Purpose : Inserts the given QC transaction record into igs_uc_mv_uvcrs_vac and igs_uc_mv_uvcrs_vop tables
2072 || Known limitations, enhancements or remarks :
2073 || Change History :
2074 || Who When What
2075 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2076 || replaced igs_uc_mv_uvcrs_vac with igs_uc_ucrsvac_ints and
2077 || igs_uc_mv_uvcrs_vop with igs_uc_ucrsvop_ints
2078 || (reverse chronological order - newest change first)
2079 */
2080
2081 ln_position NUMBER;
2082 lv_course igs_uc_ucrsvac_ints.course%TYPE;
2083 lv_campus igs_uc_ucrsvac_ints.campus%TYPE;
2084 lv_vac_status igs_uc_ucrsvac_ints.vacstatus%TYPE;
2085
2086 BEGIN
2087
2088 -- Extract the data into the temporary variables
2089 lv_course := TRIM(SUBSTR(p_record_data,1,6));
2090 lv_campus := TRIM(SUBSTR(p_record_data,7,1));
2091 lv_vac_status := TRIM(SUBSTR(p_record_data,8,1));
2092
2093 -- Obsolete matching records in interface table with status N
2094 UPDATE igs_uc_ucrsvac_ints SET record_status = 'O'
2095 WHERE record_status = 'N' AND course = lv_course AND campus = lv_campus ;
2096
2097
2098 -- Each QC transaction indicates the Vacancy detals and 100 vancancy options details.
2099 -- All the vacancy option details are stored in igs_uc_mv_uvcrs_vop table
2100 INSERT INTO igs_uc_ucrsvac_ints(
2101 course,
2102 campus,
2103 updater,
2104 clupdated,
2105 cldate,
2106 vacstatus,
2107 novac,
2108 score,
2109 rbfull,
2110 scotvac,
2111 record_status,
2112 error_code
2113 )
2114 VALUES
2115 (
2116 lv_course, -- COURSE,
2117 lv_campus, -- CAMPUS,
2118 NULL, -- UPDATER,
2119 NULL, -- CLUPDATED,
2120 NULL, -- CLDATE,
2121 lv_vac_status, -- VACSTATUS,
2122 TRIM(SUBSTR(p_record_data,9,2)), -- NOVAC,
2123 TO_NUMBER(TRIM(SUBSTR(p_record_data,11,2))), -- SCORE,
2124 NULL, -- RBFULL,
2125 NULL, -- SCOTVAC,
2126 'N', -- RECORD_STATUS,
2127 NULL -- ERROR_CODE
2128 );
2129
2130 ln_position := 65;
2131
2132 FOR i In 1..100 LOOP
2133 IF TRIM(SUBSTR(p_record_data,ln_position,2)) IS NOT NULL THEN
2134 -- Obsolete matching records in interface table with status N
2135 UPDATE igs_uc_ucrsvop_ints SET record_status = 'O'
2136 WHERE record_status = 'N' AND course = lv_course
2137 AND campus = lv_campus AND optioncode = TRIM(SUBSTR(p_record_data,ln_position,2)) ;
2138
2139
2140 INSERT INTO igs_uc_ucrsvop_ints(
2141 course,
2142 campus,
2143 optioncode,
2144 updater,
2145 clupdated,
2146 cldate,
2147 vacstatus,
2148 record_status,
2149 error_code
2150 )
2151 VALUES
2152 (
2153 lv_course, -- COURSE,
2154 lv_campus, -- CAMPUS,
2155 TRIM(SUBSTR(p_record_data,ln_position,2)), -- OPTIONCODE,
2156 NULL, -- UPDATER,
2157 NULL, -- CLUPDATED,
2158 NULL, -- CLDATE,
2159 lv_vac_status, -- VACSTATUS,
2160 'N', -- RECORD_STATUS,
2161 NULL -- ERROR_CODE
2162 );
2163 END IF;
2164 ln_position := ln_position + 2;
2165 END LOOP;
2166
2167 -- Increase the success record count.
2168 g_success_cnt := g_success_cnt +1;
2169
2170 EXCEPTION
2171 WHEN VALUE_ERROR THEN
2172 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
2173 fnd_message.set_token('TTYPE',p_trans_type);
2174 fnd_file.put_line( fnd_file.log, fnd_message.get());
2175 igs_ge_msg_stack.add;
2176 app_exception.raise_exception;
2177
2178 WHEN OTHERS THEN
2179 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2180 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_TO_QC - '||SQLERRM);
2181 igs_ge_msg_stack.add;
2182 app_exception.raise_exception;
2183
2184 END transfer_to_qc;
2185
2186
2187 PROCEDURE transfer_ack_to_trans(
2188 p_trans_type igs_uc_load_mv_t.trans_type%TYPE,
2189 p_error_code igs_uc_load_mv_t.error_code%TYPE,
2190 p_record_data igs_uc_load_mv_t.record_data%TYPE
2191 ) AS
2192 /*
2193 || Created By : brajendr
2194 || Created On :
2195 || Purpose : Inserts the given Acknowledgment transactions into igs_uc_mv_tranin table
2196 || Known limitations, enhancements or remarks :
2197 || RGANGARA 11-Nov-02. It is said that all the transaction types for all systems falling
2198 || under Ack/Echo transactions have the same format and update processing
2199 || logic and hence using the same procedure for all such transactions.
2200 || Change History :
2201 || Who When What
2202 || (reverse chronological order - newest change first)
2203 || rgangara 11-Nov-02 Modified the procedure to work for different types of ECHO/
2204 || Acknowledgment transactions.
2205 || NOTE: Clarified that the processing remains same for all the
2206 || different types of Transactions falling under this category
2207 || Also to update IGS_UC_TRANSACTIONS table directly instead of
2208 || Marvin tranin table.
2209 || rbezawad 17-Dec-02 Modified the procedure to remove the code which is loggig
2210 || IGS_UC_TRAN_PROC_APPCH for 2nd time w.r.t. Bug 2711183.
2211 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2212 || to add ucas_cycle check in the cursor cur_trans
2213 */
2214
2215 -- Get transactions for the application, Choice number , ucas_cycle and Transaction Type having NULL error_code.
2216 -- NULL error code implies that the no response/echo/Ack transaction has been received against it.
2217 -- smaddali modified this cursor for ucfd203 - multiple cycles build to add ucas_cycle check in where clause
2218 CURSOR cur_trans(
2219 cp_appno igs_uc_transactions.app_no%TYPE,
2220 cp_choiceno igs_uc_transactions.choice_no%TYPE
2221 ) IS
2222 SELECT rowid ,
2223 uc_tran_id ,
2224 transaction_id ,
2225 datetimestamp ,
2226 updater ,
2227 error_code ,
2228 transaction_type ,
2229 app_no ,
2230 choice_no ,
2231 decision ,
2232 program_code ,
2233 campus ,
2234 entry_month ,
2235 entry_year ,
2236 entry_point ,
2237 soc ,
2238 comments_in_offer ,
2239 return1 ,
2240 return2 ,
2241 hold_flag ,
2242 sent_to_ucas ,
2243 created_by ,
2244 creation_date ,
2245 last_updated_by ,
2246 last_update_date ,
2247 last_update_login ,
2248 test_cond_cat ,
2249 test_cond_name ,
2250 inst_reference ,
2251 auto_generated_flag,
2252 system_code ,
2253 ucas_cycle ,
2254 modular ,
2255 part_time
2256 FROM igs_uc_transactions
2257 WHERE app_no = cp_appno
2258 AND choice_no = cp_choiceno
2259 AND transaction_type = p_trans_type
2260 AND error_code IS NULL
2261 AND ucas_cycle = g_c_cycles.configured_cycle
2262 ORDER BY uc_tran_id;
2263
2264 trans_rec cur_trans%ROWTYPE;
2265 ln_choiceno igs_uc_transactions.choice_no%TYPE;
2266 l_char_choice VARCHAR2(1) := TRIM(SUBSTR(p_record_data,10,1));
2267 ln_appno igs_uc_transactions.app_no%TYPE := TO_NUMBER(TRIM(SUBSTR(p_record_data,1,8)));
2268 BEGIN
2269
2270 fnd_message.set_name('IGS', 'IGS_UC_TRAN_PROC_APPCH');
2271 fnd_message.set_token('TTYPE',p_trans_type);
2272 fnd_message.set_token('APPNO', ln_appno);
2273 fnd_message.set_token('CHOICENO', l_char_choice);
2274 fnd_file.put_line( fnd_file.log, fnd_message.get());
2275
2276 --Added as part of UCFD02 build for GTTR system.
2277 --If the incoming CHOICE No. is Alphabetic then it has to be converted to appropriate Number and used.
2278 -- i.e. if the incoming Choice No = 'A' then Choice No = 10, If it is 'F' then Choice = 15 etc.
2279 IF NOT ASCII(l_char_choice) BETWEEN 49 and 57 THEN
2280 ln_choiceno := get_numeric_choice(l_char_choice);
2281 ELSE
2282 ln_choiceno := TO_NUMBER(l_char_choice);
2283 END IF;
2284
2285 OPEN cur_trans( ln_appno, ln_choiceno);
2286 FETCH cur_trans INTO trans_rec;
2287
2288 -- If there is only one instance of the corresponding transaction record then, update the record with the error code
2289 -- If no record was foung then log a message that "No Transaction record was found - Error codition since a transaction
2290 -- coming as Echo/Ack a transaction record should be existing in our tranaction table.
2291 -- If more than one record then update the oldest transaction record.
2292 IF cur_trans%NOTFOUND THEN
2293 fnd_message.set_name('IGS', 'IGS_UC_MV_NO_TRANIN');
2294 fnd_message.set_token('TTYPE',p_trans_type);
2295 fnd_message.set_token('APPNO',ln_appno);
2296 fnd_message.set_token('CHCNO',ln_choiceno);
2297 fnd_file.put_line( fnd_file.log, fnd_message.get());
2298 igs_ge_msg_stack.add;
2299 app_exception.raise_exception;
2300
2301 ELSE
2302 -- update the corresponding transaction record with Timestamp and Errorcode
2303 igs_uc_transactions_pkg.update_row(
2304 x_rowid => trans_rec.rowid ,
2305 x_uc_tran_id => trans_rec.uc_tran_id ,
2306 x_transaction_id => trans_rec.transaction_id ,
2307 x_datetimestamp => SYSDATE , -- update
2308 x_updater => trans_rec.updater ,
2309 x_error_code => p_error_code , -- update
2310 x_transaction_type => trans_rec.transaction_type ,
2311 x_app_no => trans_rec.app_no ,
2312 x_choice_no => trans_rec.choice_no ,
2313 x_decision => trans_rec.decision ,
2314 x_program_code => trans_rec.program_code ,
2315 x_campus => trans_rec.campus ,
2316 x_entry_month => trans_rec.entry_month ,
2317 x_entry_year => trans_rec.entry_year ,
2318 x_entry_point => trans_rec.entry_point ,
2319 x_soc => trans_rec.soc ,
2320 x_comments_in_offer => trans_rec.comments_in_offer ,
2321 x_return1 => trans_rec.return1 ,
2322 x_return2 => trans_rec.return2 ,
2323 x_hold_flag => trans_rec.hold_flag ,
2324 x_sent_to_ucas => trans_rec.sent_to_ucas ,
2325 x_test_cond_cat => trans_rec.test_cond_cat ,
2326 x_test_cond_name => trans_rec.test_cond_name ,
2327 x_mode => 'R' ,
2328 x_inst_reference => trans_rec.inst_reference ,
2329 x_auto_generated_flag => trans_rec.auto_generated_flag,
2330 x_system_code => trans_rec.system_code ,
2331 x_ucas_cycle => trans_rec.ucas_cycle ,
2332 x_modular => trans_rec.modular ,
2333 x_part_time => trans_rec.part_time
2334 );
2335
2336 -- Increase the success record count.
2337 g_success_cnt := g_success_cnt +1;
2338 END IF;
2339
2340 CLOSE cur_trans;
2341
2342 EXCEPTION
2343 WHEN VALUE_ERROR THEN
2344 fnd_message.set_name('IGS', 'IGS_UC_NON_NUMERIC_DATA');
2345 fnd_message.set_token('TTYPE',p_trans_type);
2346 fnd_file.put_line( fnd_file.log, fnd_message.get());
2347 igs_ge_msg_stack.add;
2348 app_exception.raise_exception;
2349
2350 WHEN OTHERS THEN
2351 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2352 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_ACK_TO_TRANS - '||SQLERRM);
2353 igs_ge_msg_stack.add;
2354 app_exception.raise_exception;
2355
2356 END transfer_ack_to_trans;
2357
2358
2359
2360 FUNCTION check_validity RETURN BOOLEAN IS
2361 /*
2362 || Created By : brajendr
2363 || Created On :
2364 || Purpose : Function returns TRUE if the
2365 || a. Data file contains valid supported file formats specified in the igs_uc_cyc_defaults
2366 || b. Profile interface value is set to Marvin
2367 || c. Data file contains valid data sections with number of Headers and Trailers are equal
2368 || Else this function returns FALSE.
2369 || Known limitations, enhancements or remarks :
2370 || Change History :
2371 || Who When What
2372 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2373 || replaced profile for interface type by igs_uc_cyc_defaults
2374 || (reverse chronological order - newest change first)
2375 */
2376
2377 -- Check for the count of the number of the Headers and trailers
2378 CURSOR hdr_count IS
2379 SELECT MOD(COUNT(*),2)
2380 FROM igs_uc_load_mv_t
2381 WHERE trans_type = '*S';
2382
2383 -- Check whether the file type present in the data file and the file
2384 -- types mentioned in the system are correct.
2385 CURSOR chk_file_type IS
2386 SELECT DISTINCT file_type
2387 FROM igs_uc_load_mv_t
2388 WHERE file_type IS NOT NULL ;
2389 chk_file_type_rec chk_file_type%ROWTYPE ;
2390
2391 ln_count NUMBER := 0 ;
2392
2393 -- smaddali added cursor for bug#2669208 , ucfd203 build
2394 CURSOR c_interface(cp_sys_code igs_uc_cyc_defaults.system_code%TYPE) IS
2395 SELECT ucas_interface
2396 FROM igs_uc_cyc_defaults
2397 WHERE system_code = cp_sys_code
2398 AND ucas_cycle = g_c_cycles.configured_cycle ;
2399 c_interface_rec c_interface%ROWTYPE ;
2400
2401 l_valid BOOLEAN ;
2402
2403 BEGIN
2404
2405 l_valid := TRUE ;
2406 -- Get the Header and Trailer counts from the Marvin data file.
2407 OPEN hdr_count;
2408 FETCH hdr_count INTO ln_count;
2409 CLOSE hdr_count;
2410
2411 -- If the count is 0 then number of headers is equal to number of trailers
2412 -- so return TRUE else FALSE
2413 IF ln_count = 0 THEN
2414
2415 -- Check whether the Data format type is supported.
2416 FOR chk_file_type_rec IN chk_file_type LOOP
2417 -- check if setup is configured for this system
2418 OPEN c_interface(chk_file_type_rec.file_type) ;
2419 FETCH c_interface INTO c_interface_rec ;
2420 IF c_interface%NOTFOUND THEN
2421 CLOSE c_interface ;
2422 l_valid := FALSE ;
2423 fnd_message.set_name('IGS', 'IGS_UC_MV_SYSTEM_NOT_CONFIG');
2424 fnd_message.set_token('SYSTEM', chk_file_type_rec.file_type ) ;
2425 fnd_file.put_line( fnd_file.log, fnd_message.get());
2426 ELSE
2427 CLOSE c_interface ;
2428 -- check for Marvin or Hercules Interface.
2429 -- Load Marvin data only if the profile value is Marvin.
2430 IF c_interface_rec.ucas_interface <> 'M' THEN
2431 l_valid := FALSE ;
2432 fnd_message.set_name('IGS', 'IGS_UC_SYS_NOT_MARV');
2433 fnd_message.set_token('SYSTEM_CODE' ,chk_file_type_rec.file_type) ;
2434 fnd_file.put_line( fnd_file.log, fnd_message.get());
2435 END IF ;
2436 END IF;
2437 END LOOP ;
2438
2439 ELSE
2440 l_valid := FALSE ;
2441 fnd_message.set_name('IGS', 'IGS_UC_MV_DATA_SEC_WRNG');
2442 fnd_file.put_line( fnd_file.log, fnd_message.get());
2443 END IF;
2444
2445 RETURN l_valid ;
2446
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2450 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.CHECK_VALIDITY - '||SQLERRM);
2451 igs_ge_msg_stack.add;
2452 app_exception.raise_exception;
2453
2454 END check_validity;
2455
2456
2457 FUNCTION merge_data(
2458 p_marvin_id IGS_UC_LOAD_MV_T.marvin_id%TYPE
2459 ) RETURN VARCHAR2 AS
2460
2461 /*
2462 || Created By : brajendr
2463 || Created On :
2464 || Purpose : This is a recursive function which calls itself and appends
2465 || remaining record data if the logical data in the data file
2466 || spans more than one physical record.
2467 || Known limitations, enhancements or remarks :
2468 || Change History :
2469 || Who When What
2470 || (reverse chronological order - newest change first)
2471 */
2472
2473 -- Get the marvin data for the given marvin_id.
2474 CURSOR cur_ucas (cp_marvin_id igs_uc_load_mv_t.marvin_id%TYPE) IS
2475 SELECT *
2476 FROM igs_uc_load_mv_t
2477 WHERE marvin_id = cp_marvin_id;
2478
2479 ucas_rec igs_uc_load_mv_t%ROWTYPE;
2480
2481 BEGIN
2482
2483 -- Get the marvin data for processing.
2484 OPEN cur_ucas(p_marvin_id);
2485 FETCH cur_ucas INTO ucas_rec;
2486 CLOSE cur_ucas;
2487
2488 -- If the continuation flag is 9, means it is already the last record, then return the same record
2489 -- else merge the data till it finds the last record.
2490 -- RPAD data for 80 characters so that position of the data is retained if data is merged.
2491 IF ucas_rec.contd_flag = '9' THEN
2492 UPDATE igs_uc_load_mv_t SET record_status = 'N' WHERE marvin_id = p_marvin_id;
2493 RETURN (RPAD(NVL(ucas_rec.record_data,' '),67));
2494 ELSE
2495 RETURN (RPAD(NVL(ucas_rec.record_data,' '),67) || merge_data(ucas_rec.marvin_id + 1));
2496 END IF;
2497
2498 EXCEPTION
2499 WHEN app_exception.record_lock_exception THEN
2500 RAISE;
2501
2502 WHEN OTHERS THEN
2503 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2504 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.MERGE_DATA - '||SQLERRM);
2505 igs_ge_msg_stack.add;
2506 app_exception.raise_exception;
2507
2508 END merge_data;
2509
2510
2511 PROCEDURE rearrange_data IS
2512 /*
2513 || Created By : brajendr
2514 || Created On :
2515 || Purpose : This procedure checks whether the logical record data spans
2516 || accross more than one physical record in the data file. If
2517 || data is spanned then this funciton calls merge function to
2518 || merge the spanned data.
2519 || Known limitations, enhancements or remarks :
2520 || Change History :
2521 || Who When What
2522 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2523 || (reverse chronological order - newest change first)
2524 */
2525
2526 -- Get the details of
2527 CURSOR cur_ucas IS
2528 SELECT marvin_id
2529 FROM igs_uc_load_mv_t
2530 WHERE contd_flag = '1'
2531 AND record_status = 'N'
2532 ORDER BY marvin_id
2533 FOR UPDATE OF marvin_id NOWAIT;
2534
2535 lv_record_data IGS_UC_LOAD_MV_T.record_data%TYPE;
2536
2537 BEGIN
2538
2539 -- Loop thru all the records in whcih logical record spanned accross more than one record.
2540 FOR rec_cur_ucas IN cur_ucas LOOP
2541 lv_record_data := merge_data(rec_cur_ucas.MARVIN_ID);
2542 UPDATE IGS_UC_LOAD_MV_T SET record_data = lv_record_data, record_status = 'R' WHERE CURRENT OF cur_ucas;
2543 END LOOP;
2544
2545 EXCEPTION
2546 WHEN app_exception.record_lock_exception THEN
2547 RAISE;
2548
2549 WHEN OTHERS THEN
2550 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2551 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.REARRANGE_DATA - '||SQLERRM);
2552 igs_ge_msg_stack.add;
2553 app_exception.raise_exception;
2554
2555 END rearrange_data;
2556
2557
2558 PROCEDURE transfer_data(
2559 errbuf OUT NOCOPY VARCHAR2,
2560 retcode OUT NOCOPY NUMBER
2561 ) IS
2562 /*
2563 || Created By : brajendr
2564 || Created On :
2565 || Purpose : Procedure whcih will check for transaction type and calls the
2566 || corresponding procedure for transfer of data into the different tables
2567 || Known limitations, enhancements or remarks :
2568 || Change History :
2569 || Who When What
2570 || (reverse chronological order - newest change first)
2571 || rgangara 11-Nov-02 Added call to process *G, *T transaction types
2572 || and modified Acknowledgement transaction call to
2573 || process different type of ECHO transactions.
2574 || as part of UCFD02_Small_SYstems. Bug# 2643048
2575 || rbezawad 06-Mar-03 Removed the code which is increasing the "success record count" after processing "AE" transaction w.r.t Bug 2810665.
2576 || smaddali 30-Jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2577 || forking the process depending on the configured cycle
2578 || jchakrab 27-Jul-04 Modified for UCFD308-UCAS 2005 Regulatory Changes
2579 || jbaber 12-Jul-05 Modified for UC315 - UCAS Support 2006
2580 */
2581
2582
2583 -- Get the details of
2584 CURSOR cur_ucas IS
2585 SELECT marvin_id, trans_type, error_code, record_data
2586 FROM igs_uc_load_mv_t
2587 WHERE record_status = 'R'
2588 ORDER BY marvin_id ;
2589
2590 l_valid_rec BOOLEAN := TRUE;
2591 l_curr_rec_data igs_uc_load_mv_t.record_data%TYPE;
2592 l_trans_type igs_uc_load_mv_t.trans_type%TYPE;
2593
2594 BEGIN
2595
2596 -- Initialize the flag to TRUE before every run
2597 l_valid_rec := TRUE;
2598
2599 -- Loop thru all the records and transfer data into corresponding
2600 -- interface tables based on the Transaction Type
2601 FOR rec_cur_ucas IN cur_ucas LOOP
2602
2603 -- copying the cursor values into temporary variable for logging purposes, in case exceptions are raised.
2604 l_curr_rec_data := rec_cur_ucas.record_data;
2605 l_trans_type := rec_cur_ucas.trans_type;
2606
2607 -- Incarease the global count.
2608 g_record_cnt := g_record_cnt + 1;
2609
2610
2611 -- Check whether the Applicaiton number is correct or not for all the Applications tables.
2612 -- If the Application number is Invalid then the job should log a message and continue with
2613 -- available transaction
2614 IF rec_cur_ucas.trans_type IN ( '*A','*C','*G','*H','*K','*N','*P','*R','*T','*W','*X','*Z','LA', 'LC','LD','LE','LK','PE','RA','RD','RE','RK','RQ','RR','RW','RX','XA','XD') THEN
2615 IF TO_CHAR(get_check_digit(SUBSTR(rec_cur_ucas.record_data,1,8))) <> SUBSTR(rec_cur_ucas.record_data,9,1) THEN
2616 l_valid_rec := FALSE;
2617 fnd_message.set_name('IGS', 'IGS_UC_INVLD_APPNO');
2618 fnd_message.set_token('APPNO',SUBSTR(rec_cur_ucas.record_data,1,8));
2619 fnd_message.set_token('TTYPE',rec_cur_ucas.trans_type);
2620 fnd_file.put_line(fnd_file.log,fnd_message.get );
2621 END IF;
2622
2623 ELSE
2624 -- if trans type = 'AE' then it carries the file seq num which needs to be validated that the flat files are being processed in sequence.
2625 IF rec_cur_ucas.trans_type = 'AE' AND SUBSTR(rec_cur_ucas.record_data,1,3) = 'SEQ' THEN
2626 validate_file_seq_num(rec_cur_ucas.marvin_id, SUBSTR(rec_cur_ucas.record_data,4));
2627 END IF;
2628 END IF;
2629
2630 -- Transfer all the transactions from the temporary table to corresponding
2631 -- interface tables based on the transaction type.
2632 IF l_valid_rec THEN
2633 -- If system is configured for 2003 cycle then call the procedures for 2003 marvin structure
2634 IF g_c_cycles.configured_cycle = '2003' THEN
2635
2636 IF rec_cur_ucas.trans_type = '*N' THEN
2637 transfer_to_starn( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2638 ELSIF rec_cur_ucas.trans_type = '*A' THEN
2639 transfer_to_stara( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2640 ELSIF rec_cur_ucas.trans_type = '*C' THEN
2641 transfer_to_starc( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2642 ELSIF rec_cur_ucas.trans_type = '*G' THEN
2643 transfer_to_starg( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2644 ELSIF rec_cur_ucas.trans_type = '*H' THEN
2645 transfer_to_starh( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2646 ELSIF rec_cur_ucas.trans_type = '*K' THEN
2647 transfer_to_stark( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2648 ELSIF rec_cur_ucas.trans_type IN ('*P', '*R') THEN
2649 transfer_to_starpqr( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2650 ELSIF rec_cur_ucas.trans_type = '*T' THEN
2651 transfer_to_start( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2652 ELSIF rec_cur_ucas.trans_type = '*W' THEN
2653 transfer_to_starw( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2654 ELSIF rec_cur_ucas.trans_type = '*X' THEN
2655 transfer_to_starx( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2656 ELSIF rec_cur_ucas.trans_type = '*Z' THEN
2657 transfer_to_starz( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2658 ELSIF rec_cur_ucas.trans_type = 'QA' THEN
2659 transfer_to_qa( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2660 ELSIF rec_cur_ucas.trans_type = 'QC' THEN
2661 transfer_to_qc( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2662 ELSIF rec_cur_ucas.trans_type IN ('LA', 'LC','LD','LE','LK','PE','RA','RD','RE','RK','RQ','RR','RW','RX','XA','XD') THEN
2663 transfer_ack_to_trans( rec_cur_ucas.trans_type, rec_cur_ucas.error_code, rec_cur_ucas.record_data);
2664 END IF;
2665 ELSIF g_c_cycles.configured_cycle = '2004' OR g_c_cycles.configured_cycle = '2005' OR g_c_cycles.configured_cycle = '2006' OR g_c_cycles.configured_cycle = '2007' THEN
2666 -- Only *W has changed from 2003 to add new columns,
2667 -- However this change is only in the Hercules interface and not in marvin interface
2668 -- Hence the calls for 2003 and 2004 are exactly same
2669 -- No data model changes for 2005, therefore use same calls for 2005 as in 2004
2670 IF rec_cur_ucas.trans_type = '*N' THEN
2671 transfer_to_starn( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2672 ELSIF rec_cur_ucas.trans_type = '*A' THEN
2673 transfer_to_stara( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2674 ELSIF rec_cur_ucas.trans_type = '*C' THEN
2675 transfer_to_starc( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2676 ELSIF rec_cur_ucas.trans_type = '*G' THEN
2677 transfer_to_starg( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2678 ELSIF rec_cur_ucas.trans_type = '*H' THEN
2679 transfer_to_starh( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2680 ELSIF rec_cur_ucas.trans_type = '*K' THEN
2681 transfer_to_stark( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2682 ELSIF rec_cur_ucas.trans_type IN ('*P', '*R') THEN
2683 transfer_to_starpqr( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2684 ELSIF rec_cur_ucas.trans_type = '*T' THEN
2685 transfer_to_start( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2686 ELSIF rec_cur_ucas.trans_type = '*W' THEN
2687 transfer_to_starw( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2688 ELSIF rec_cur_ucas.trans_type = '*X' THEN
2689 transfer_to_starx( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2690 ELSIF rec_cur_ucas.trans_type = '*Z' THEN
2691 transfer_to_starz( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2692 ELSIF rec_cur_ucas.trans_type = 'QA' THEN
2693 transfer_to_qa( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2694 ELSIF rec_cur_ucas.trans_type = 'QC' THEN
2695 transfer_to_qc( rec_cur_ucas.trans_type, rec_cur_ucas.record_data);
2696 ELSIF rec_cur_ucas.trans_type IN ('LA', 'LC','LD','LE','LK','PE','RA','RD','RE','RK','RQ','RR','RW','RX','XA','XD') THEN
2697 transfer_ack_to_trans( rec_cur_ucas.trans_type, rec_cur_ucas.error_code, rec_cur_ucas.record_data);
2698 END IF;
2699
2700 ELSIF g_c_cycles.configured_cycle = '2008' THEN
2701 NULL ; -- future use
2702 END IF ;
2703
2704 END IF; -- record is valid
2705
2706 END LOOP;
2707
2708 IF NOT l_valid_rec THEN
2709 retcode := 2 ;
2710 RETURN ;
2711 END IF ;
2712
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 fnd_file.put_line( fnd_file.log, 'TRANSACTION: ' ||l_trans_type || ' DATA : ' || l_curr_rec_data);
2716 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2717 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.TRANSFER_DATA - '||SQLERRM);
2718 igs_ge_msg_stack.add;
2719 app_exception.raise_exception;
2720
2721 END transfer_data;
2722
2723
2724 PROCEDURE process_marvin_data(
2725 errbuf OUT NOCOPY VARCHAR2,
2726 retcode OUT NOCOPY NUMBER
2727 ) IS
2728 /*
2729 || Created By : brajendr
2730 || Created On : 05-Apr-2002
2731 || Purpose : This is main Procedure which will transfer the Marvin flat file data
2732 || into the corresponding Dummy Hercules tables. This procedures calls
2733 || other prodecures / functions in the given order
2734 || a. check_validity
2735 || b. rearrange_data
2736 || a. transfer_data
2737 || Known limitations, enhancements or remarks :
2738 || Change History :
2739 || Who When What
2740 || anwest 18-JAN-06 Bug# 4950285 R12 Disable OSS Mandate
2741 || smaddali 30-jun-03 Modified for ucfd203- multiple cycles build , bug#2669208
2742 || added validations on cycle info
2743 || (reverse chronological order - newest change first)
2744 */
2745
2746
2747 BEGIN
2748
2749 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
2750 IGS_GE_GEN_003.SET_ORG_ID;
2751
2752 fnd_file.put_line( fnd_file.log, ' ');
2753 retcode := 0;
2754 -- Set the global values, which are used to set the WHO Columns.
2755 g_record_cnt := 0;
2756 g_success_cnt := 0;
2757
2758 -- Get the current and configured cycles , if not setup then show error and exit
2759 g_c_cycles := NULL ;
2760 OPEN c_cycles ;
2761 FETCH c_cycles INTO g_c_cycles ;
2762 CLOSE c_cycles ;
2763 IF g_c_cycles.configured_cycle IS NULL OR g_c_cycles.current_cycle IS NULL THEN
2764 fnd_message.set_name('IGS','IGS_UC_CYCLE_NOT_FOUND');
2765 errbuf := fnd_message.get;
2766 fnd_file.put_line(fnd_file.log, errbuf);
2767 retcode := 2 ;
2768 RETURN ;
2769 END IF ;
2770
2771 -- get the configured cycle of hercules system
2772 c_ucas_cycle_rec := NULL ;
2773 OPEN c_ucas_cycle ;
2774 FETCH c_ucas_cycle INTO c_ucas_cycle_rec ;
2775 CLOSE c_ucas_cycle ;
2776 -- If hercules and our oss system are not configured to the same cycle then report error and exit
2777 IF NVL(c_ucas_cycle_rec.entry_year,0) <> LTRIM(SUBSTR(g_c_cycles.configured_cycle,3,2) ) THEN
2778 fnd_message.set_name('IGS','IGS_UC_CYCLES_NOT_SYNC');
2779 fnd_message.set_token('UCAS_CYCLE',LTRIM(SUBSTR(g_c_cycles.configured_cycle,3,2) ) );
2780 fnd_message.set_token('HERC_CYCLE',NVL(c_ucas_cycle_rec.entry_year,0 ) );
2781 fnd_message.set_token('SYSTEM_CODE','UCAS');
2782 errbuf := fnd_message.get ;
2783 fnd_file.put_line(fnd_file.log,errbuf );
2784 retcode := 2 ;
2785 RETURN ;
2786 END IF ;
2787
2788 -- show the configured cycle information , because the other messages donot show its value
2789 fnd_message.set_name('IGS','IGS_UC_CYC_INFO');
2790 fnd_message.set_token('CONF_CYCLE',g_c_cycles.configured_cycle );
2791 fnd_file.put_line(fnd_file.log, fnd_message.get );
2792 fnd_file.put_line( fnd_file.log, ' ');
2793
2794 -- Check for the Validity of the file.
2795 -- 1.Number of headers in the data file should equal to number of trailers
2796 -- 2.cycle defaults should be setup for each of the systems present in the flat file
2797 -- 3.ucas interface should be setup to Marvin for each of the systems present in the flat file
2798 IF NOT check_validity THEN
2799 retcode := 2;
2800 RETURN;
2801 END IF;
2802
2803 -- validate if marvin seq for all the systems in the flat file is in sequence
2804 IF NOT sequence_validity THEN
2805 retcode := 2;
2806 RETURN;
2807 END IF ;
2808
2809 -- Re-arrange data
2810 -- If one logical data spans accross more than one physical records, then
2811 -- Group all related records to one record for further processing.
2812 rearrange_data;
2813
2814 -- Move data to actual interface tables.
2815 transfer_data(errbuf, retcode) ;
2816 -- If any validations failed while transfering the transactions then rollback and complete in error
2817 IF retcode = 2 THEN
2818 ROLLBACK ;
2819 RETURN ;
2820 END IF ;
2821
2822 -- Print Number of records successfully transferred
2823 fnd_file.put_line( fnd_file.log, ' ');
2824 fnd_message.set_name('IGS', 'IGS_UC_MV_LOAD_SUCCESS');
2825 fnd_message.set_token('CNT', g_success_cnt);
2826 fnd_file.put_line( fnd_file.log, fnd_message.get());
2827
2828 -- commit the data;
2829 COMMIT;
2830 fnd_file.put_line( fnd_file.log, ' ');
2831
2832 EXCEPTION
2833 WHEN app_exception.record_lock_exception THEN
2834 ROLLBACK;
2835 retcode := 2;
2836 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
2837 igs_ge_msg_stack.conc_exception_hndl;
2838
2839 WHEN OTHERS THEN
2840 ROLLBACK;
2841 retcode := 2;
2842 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2843 fnd_message.set_token('NAME','IGS_UC_MV_DATA_UPLD.PROCESS_MARVIN_DATA - '||SQLERRM);
2844 errbuf := fnd_message.get;
2845 igs_ge_msg_stack.conc_exception_hndl;
2846
2847 END process_marvin_data;
2848
2849 END igs_uc_mv_data_upld;
[Home] [Help]