[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_PROC_REFERENCE_DATA
Source
1 PACKAGE BODY igs_uc_proc_reference_data AS
2 /* $Header: IGSUC67B.pls 120.2 2006/08/21 06:15:58 jbaber noship $ */
3
4 g_success_rec_cnt NUMBER;
5 g_error_rec_cnt NUMBER;
6 g_error_code igs_uc_crfcode_ints.error_code%TYPE;
7
8
9 PROCEDURE process_cvrefcodes IS
10 /******************************************************************
11 Created By : rgangara
12 Date Created By : 12-JUNE-2003
13 Purpose : For processing CVCONTROL data
14 Known limitations,enhancements,remarks:
15 Change History
16 Who When What
17 ******************************************************************/
18
19 CURSOR int_crfcode_cur IS
20 SELECT rowid,
21 code_type,
22 code,
23 code_text
24 FROM igs_uc_crfcode_ints
25 WHERE record_status = 'N';
26
27 CURSOR chk_code_type_cur (p_code igs_uc_crfcode_ints.code%TYPE) IS
28 SELECT 'X'
29 FROM igs_lookup_values
30 WHERE lookup_type = 'IGS_UC_CODE_TYPES'
31 AND lookup_code = p_code
32 AND enabled_flag = 'Y'
33 AND NVL(closed_ind, 'N') = 'N';
34
35 CURSOR old_rfcode_cur(p_code igs_uc_ref_codes.code%TYPE, p_type igs_uc_ref_codes.code_type%TYPE) IS
36 SELECT rfc.rowid
37 FROM igs_uc_ref_codes rfc
38 WHERE code_type = p_type
39 AND code = p_code ;
40
41 old_rfcode_rec old_rfcode_cur%ROWTYPE ;
42 l_valid_type VARCHAR2(1);
43 l_rowid VARCHAR2(26) := NULL;
44
45 BEGIN
46
47 -- initialize variables
48 g_success_rec_cnt := 0;
49 g_error_rec_cnt := 0;
50
51 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
52 fnd_message.set_token('VIEW', 'REFERENCE CODES ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
53 fnd_file.put_line(fnd_file.log, fnd_message.get);
54
55 -- Get all the reocords from interface table with status = 'N'
56 FOR new_crfcode_rec IN int_crfcode_cur
57 LOOP
58
59 BEGIN
60 -- initialize record level variables.
61 l_rowid := NULL;
62 g_error_code := NULL;
63 l_valid_type := NULL;
64
65 -- log record level processing message
66 fnd_message.set_name('IGS','IGS_UC_PROC_REFCODE_REC');
67 fnd_message.set_token('CODE', new_crfcode_rec.code);
68 fnd_message.set_token('TYPE', new_crfcode_rec.code_type);
69 fnd_file.put_line(fnd_file.log, fnd_message.get);
70
71
72 -- validate mandatory fields have values.
73 IF new_crfcode_rec.code_type IS NULL OR new_crfcode_rec.code IS NULL THEN
74 g_error_code := '1037';
75 END IF;
76
77 IF g_error_code IS NULL THEN
78 -- validate Code type value.
79 OPEN chk_code_type_cur (new_crfcode_rec.code_type);
80 FETCH chk_code_type_cur INTO l_valid_type;
81 CLOSE chk_code_type_cur;
82
83 IF l_valid_type IS NULL THEN
84 -- invalid code type hence not found and is NULL
85 g_error_code := '1040';
86 END IF;
87 END IF;
88
89 IF g_error_code IS NULL THEN -- i.e. Code type is valid
90
91 l_rowid := NULL;
92
93 -- Check whther the error code already exists or not
94 -- If exists , update the records otherwise insert a new record
95 OPEN old_rfcode_cur(new_crfcode_rec.code, new_crfcode_rec.code_type);
96 FETCH old_rfcode_cur INTO l_rowid;
97 CLOSE old_rfcode_cur;
98
99 IF l_rowid IS NULL THEN
100 BEGIN
101 --Insert a new record
102 igs_uc_ref_codes_pkg.insert_row --IGSXI26B.pls
103 (
104 x_rowid => l_rowid
105 ,x_code_type => new_crfcode_rec.code_type
106 ,x_code => new_crfcode_rec.code
107 ,x_code_text => new_crfcode_rec.code_text
108 ,x_imported => 'Y'
109 ,x_mode => 'R'
110 );
111 EXCEPTION
112 WHEN OTHERS THEN
113 g_error_code := '9998';
114 fnd_file.put_line(fnd_file.log, SQLERRM);
115
116 END;
117
118 ELSE /* Update the record */
119 BEGIN
120 igs_uc_ref_codes_pkg.update_row --IGSXI26B.pls
121 (
122 x_rowid => l_rowid
123 ,x_code_type => new_crfcode_rec.code_type
124 ,x_code => new_crfcode_rec.code
125 ,x_code_text => new_crfcode_rec.code_text
126 ,x_imported => 'Y'
127 ,x_mode => 'R'
128 );
129 EXCEPTION
130 WHEN OTHERS THEN
131 g_error_code := '9998';
132 fnd_file.put_line(fnd_file.log, SQLERRM);
133
134 -- log error message
135 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
136 END;
137 END IF; -- insert / update
138 END IF; -- Code type validation
139
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 -- catch any unhandled/unexpected errors while processing a record.
144 -- This would enable processing to continue with subsequent records.
145
146 -- Close any Open cursors
147 IF chk_code_type_cur%ISOPEN THEN
148 CLOSE chk_code_type_cur;
149 END IF;
150
151 IF old_rfcode_cur%ISOPEN THEN
152 CLOSE old_rfcode_cur;
153 END IF;
154
155 IF chk_code_type_cur%ISOPEN THEN
156 CLOSE chk_code_type_cur;
157 END IF;
158
159 g_error_code := '1055';
160 fnd_file.put_line(fnd_file.log, SQLERRM);
161 END;
162
163 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
164 -- while processing the record.
165 IF g_error_code IS NOT NULL THEN
166 UPDATE igs_uc_crfcode_ints
167 SET error_code = g_error_code
168 WHERE rowid = new_crfcode_rec.rowid;
169
170 -- log error message/meaning.
171 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
172 -- update error count
173 g_error_rec_cnt := g_error_rec_cnt + 1;
174
175 ELSE
176 UPDATE igs_uc_crfcode_ints
177 SET record_status = 'D',
178 error_code = NULL
179 WHERE rowid = new_crfcode_rec.rowid;
180
181 g_success_rec_cnt := g_success_rec_cnt + 1; -- count successfully processed records
182 END IF;
183
184 END LOOP;
185
186 COMMIT;
187 -- log processing complete for this view
188 igs_uc_proc_ucas_data.log_proc_complete('REFERENCE CODES', g_success_rec_cnt, g_error_rec_cnt);
189
190 EXCEPTION
191 -- Process should continue with processing of other view data
192 WHEN OTHERS THEN
193 ROLLBACK;
194 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
195 fnd_message.set_token('VIEW', 'REF CODES'||' - '||SQLERRM);
196 fnd_file.put_line(fnd_file.log, fnd_message.get);
197 END process_cvrefcodes;
198
199
200
201 PROCEDURE process_cvrefawardbody IS
202 /******************************************************************
203 Created By : rgangara
204 Date Created By : 12-JUNE-2003
205 Purpose : For processing Ref Award body data
206 Known limitations,enhancements,remarks:
207 Change History
208 Who When What
209 ***************************************************************** */
210 l_rowid VARCHAR2(26) := NULL;
211
212 -- Get new interface records
213 CURSOR int_awdbdy_cur IS
214 SELECT rowid
215 ,year
216 ,sitting
217 ,awardingbody
218 ,bodyname
219 ,bodyabbrev
220 FROM igs_uc_crawdbd_ints
221 WHERE record_status = 'N';
222
223 -- check whether corresponding record already exists.
224 CURSOR old_awdbdy_cur (p_year igs_uc_ref_awrdbdy.year%TYPE,
225 p_sitting igs_uc_ref_awrdbdy.sitting%TYPE,
226 p_awd_body igs_uc_ref_awrdbdy.awarding_body%TYPE) IS
227 SELECT awd.rowid
228 FROM igs_uc_ref_awrdbdy awd
229 WHERE awd.year = p_year
230 AND awd.sitting = p_sitting
231 AND awd.awarding_body = p_awd_body;
232
233 BEGIN
234 -- initialize variables
235 g_success_rec_cnt := 0;
236 g_error_rec_cnt := 0;
237
238 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
239 fnd_message.set_token('VIEW', 'CVREFAWARDBODY ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
240 fnd_file.put_line(fnd_file.log, fnd_message.get);
241
242 -- Get all the reocords from interface table with status = 'N'
243 FOR new_awdbdy_rec IN int_awdbdy_cur LOOP
244
245 BEGIN
246 -- record level initialization
247 l_rowid := NULL;
248 g_error_code := NULL;
249
250 -- log record level processing message
251 fnd_message.set_name('IGS','IGS_UC_PROC_REFAWD_REC');
252 fnd_message.set_token('YEAR', new_awdbdy_rec.year);
253 fnd_message.set_token('SITTING', new_awdbdy_rec.sitting);
254 fnd_message.set_token('AWDBDY', new_awdbdy_rec.awardingbody);
255 fnd_file.put_line(fnd_file.log, fnd_message.get);
256
257
258 -- validate mandatory fields have values.
259 IF new_awdbdy_rec.year IS NULL OR new_awdbdy_rec.sitting IS NULL OR new_awdbdy_rec.awardingbody IS NULL THEN
260 g_error_code := '1037';
261 END IF;
262
263 IF g_error_code IS NULL THEN
264
265 -- check whether corresponding rec already exists
266 OPEN old_awdbdy_cur(new_awdbdy_rec.year, new_awdbdy_rec.sitting, new_awdbdy_rec.awardingbody);
267 FETCH old_awdbdy_cur INTO l_rowid;
268 CLOSE old_awdbdy_cur;
269
270
271 -- If not found then insert
272 IF l_rowid IS NULL THEN
273 BEGIN
274 igs_uc_ref_awrdbdy_pkg.insert_row --IGSXI25B.pls
275 (
276 x_rowid => l_rowid
277 ,x_year => new_awdbdy_rec.year
278 ,x_sitting => new_awdbdy_rec.sitting
279 ,x_awarding_body => new_awdbdy_rec.awardingbody
280 ,x_body_name => new_awdbdy_rec.bodyname
281 ,x_body_abbrev => new_awdbdy_rec.bodyabbrev
282 ,x_imported => 'Y'
283 ,x_mode => 'R'
284 );
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 g_error_code := '9999';
289 fnd_file.put_line(fnd_file.log, SQLERRM);
290
291 END;
292
293 ELSE -- update
294 BEGIN
295 igs_uc_ref_awrdbdy_pkg.update_row --IGSXI25B.pls
296 (
297 x_rowid => l_rowid
298 ,x_year => new_awdbdy_rec.year
299 ,x_sitting => new_awdbdy_rec.sitting
300 ,x_awarding_body => new_awdbdy_rec.awardingbody
301 ,x_body_name => new_awdbdy_rec.bodyname
302 ,x_body_abbrev => new_awdbdy_rec.bodyabbrev
303 ,x_imported => 'Y'
304 ,x_mode => 'R'
305 );
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 g_error_code := '9998';
310 fnd_file.put_line(fnd_file.log, SQLERRM);
311
312 END;
313 END IF; -- insert/update
314
315 END IF; -- error code check
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 -- catch any unhandled/unexpected errors while processing a record.
320 -- This would enable processing to continue with subsequent records.
321
322 -- Close any Open cursors
323 IF old_awdbdy_cur%ISOPEN THEN
324 CLOSE old_awdbdy_cur;
325 END IF;
326
327 g_error_code := '1055';
328 fnd_file.put_line(fnd_file.log, SQLERRM);
329 END;
330
331 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
332 -- while processing the record.
333 IF g_error_code IS NOT NULL THEN
334 UPDATE igs_uc_crawdbd_ints
335 SET error_code = g_error_code
336 WHERE rowid = new_awdbdy_rec.rowid;
337
338 -- log error message/meaning.
339 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
340 -- update error count
341 g_error_rec_cnt := g_error_rec_cnt + 1;
342
343 ELSE
344 UPDATE igs_uc_crawdbd_ints
345 SET record_status = 'D',
346 error_code = NULL
347 WHERE rowid = new_awdbdy_rec.rowid;
348
349 g_success_rec_cnt := g_success_rec_cnt + 1;
350
351 END IF;
352
353 END LOOP;
354
355 COMMIT;
356 -- log processing complete for this view
357 igs_uc_proc_ucas_data.log_proc_complete('CVREFAWARDBODY', g_success_rec_cnt, g_error_rec_cnt);
358
359 EXCEPTION
360 -- Process should continue with processing of other view data
361 WHEN OTHERS THEN
362 ROLLBACK;
363 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
364 fnd_message.set_token('VIEW', 'CVREFAWARDBODY'||' - '||SQLERRM);
365 fnd_file.put_line(fnd_file.log, fnd_message.get);
366 END process_cvrefawardbody;
367
368
369
370 PROCEDURE process_cvrefapr IS
371 /******************************************************************
372 Created By : rgangara
373 Date Created By : 12-JUNE-2003
374 Purpose : For processing Results data from UCAS
375 Known limitations,enhancements,remarks:
376 Change History
377 Who When What
378 ***************************************************************** */
379 l_rowid VARCHAR2(26) := NULL;
380
381 -- Get new interface records
382 CURSOR int_refapr_cur IS
383 SELECT rowid
384 ,dom
385 ,domtext
386 ,leaflag
387 FROM igs_uc_crapr_ints
388 WHERE record_status = 'N';
389
390 -- check whether corresponding record already exists.
391 CURSOR old_refapr_cur (p_dom igs_uc_crapr_ints.dom%TYPE) IS
392 SELECT rapr.rowid
393 FROM igs_uc_ref_apr rapr
394 WHERE rapr.dom = p_dom ;
395
396 BEGIN
397 -- initialize variables
398 g_success_rec_cnt := 0;
399 g_error_rec_cnt := 0;
400
404
401 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
402 fnd_message.set_token('VIEW', 'CVREFAPR ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
403 fnd_file.put_line(fnd_file.log, fnd_message.get);
405 -- Get all the reocords from interface table with status = 'N'
406 FOR new_refapr_rec IN int_refapr_cur LOOP
407
408 BEGIN
409
410 -- record level initialization
411 l_rowid := NULL;
412 g_error_code := NULL;
413
414 -- log record level processing message
415 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
416 fnd_message.set_token('KEY', 'DOM');
417 fnd_message.set_token('VALUE', new_refapr_rec.dom);
418 fnd_file.put_line(fnd_file.log, fnd_message.get);
419
420
421 -- validate mandatory fields have values.
422 IF new_refapr_rec.dom IS NULL THEN
423 g_error_code := '1037';
424 END IF;
425
426 IF g_error_code IS NULL THEN
427
428 -- check whether corresponding rec already exists
429 OPEN old_refapr_cur(new_refapr_rec.dom);
430 FETCH old_refapr_cur INTO l_rowid;
431 CLOSE old_refapr_cur;
432
433
434 -- If not found then insert
435 IF l_rowid IS NULL THEN
436 BEGIN
437 igs_uc_ref_apr_pkg.insert_row --IGSXI24B.pls
438 (
439 x_rowid => l_rowid
440 ,x_dom => new_refapr_rec.dom
441 ,x_dom_text => new_refapr_rec.domtext
442 ,x_lea_flag => NVL(new_refapr_rec.leaflag,'Y')
443 ,x_imported => 'Y'
444 ,x_mode => 'R'
445 );
446
447 EXCEPTION
448 WHEN OTHERS THEN
449 g_error_code := '9999';
450 fnd_file.put_line(fnd_file.log, SQLERRM);
451 END;
452
453 ELSE -- update
454 BEGIN
455 igs_uc_ref_apr_pkg.update_row --IGSXI24B.pls
456 (
457 x_rowid => l_rowid
458 ,x_dom => new_refapr_rec.dom
459 ,x_dom_text => new_refapr_rec.domtext
460 ,x_lea_flag => NVL(new_refapr_rec.leaflag,'Y')
461 ,x_imported => 'Y'
462 ,x_mode => 'R'
463 );
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 g_error_code := '9998';
468 fnd_file.put_line(fnd_file.log, SQLERRM);
469 END;
470 END IF;
471
472 END IF; -- error not null
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 -- catch any unhandled/unexpected errors while processing a record.
477 -- This would enable processing to continue with subsequent records.
478
479 -- Close any Open cursors
480 IF old_refapr_cur%ISOPEN THEN
481 CLOSE old_refapr_cur;
482 END IF;
483
484 g_error_code := '1055';
485 fnd_file.put_line(fnd_file.log, SQLERRM);
486 END;
487
488 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
489 -- while processing the record.
490 IF g_error_code IS NOT NULL THEN
491 UPDATE igs_uc_crapr_ints
492 SET error_code = g_error_code
493 WHERE rowid = new_refapr_rec.rowid;
494
495 -- log error message/meaning.
496 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
497
498 -- update error count
499 g_error_rec_cnt := g_error_rec_cnt + 1;
500
501 ELSE
502 UPDATE igs_uc_crapr_ints
503 SET record_status = 'D',
504 error_code = NULL
505 WHERE rowid = new_refapr_rec.rowid;
506
507 g_success_rec_cnt := g_success_rec_cnt + 1;
508 END IF;
509
510 END LOOP;
511
512 COMMIT;
513 -- log processing complete for this view
514 igs_uc_proc_ucas_data.log_proc_complete('CVREFAPR', g_success_rec_cnt, g_error_rec_cnt);
515
516 EXCEPTION
517 -- Process should continue with processing of other view data
518 WHEN OTHERS THEN
519 ROLLBACK;
520 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
521 fnd_message.set_token('VIEW', 'CVREFAPR'||' - '||SQLERRM);
522 fnd_file.put_line(fnd_file.log, fnd_message.get);
523 END process_cvrefapr;
524
525
526 PROCEDURE process_cvrefkeyword IS
527 /******************************************************************
528 Created By : rgangara
529 Date Created By : 12-JUNE-2003
530 Purpose : For processing Reference Keywords data from UCAS
531 Known limitations,enhancements,remarks:
532 Change History
533 Who When What
534 ***************************************************************** */
535 l_rowid VARCHAR2(26) := NULL;
536
540 keyword
537 -- Get new interface records
538 CURSOR int_refkwd_cur IS
539 SELECT rowid,
541 FROM igs_uc_crkywd_ints
542 WHERE record_status = 'N';
543
544 -- check whether corresponding record already exists.
545 CURSOR old_refkwd_cur (p_keyword igs_uc_crkywd_ints.keyword%TYPE) IS
546 SELECT rowid
547 FROM igs_uc_ref_keywords
548 WHERE keyword = p_keyword ;
549
550 BEGIN
551 -- initialize variables
552 g_success_rec_cnt := 0;
553 g_error_rec_cnt := 0;
554
555 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
556 fnd_message.set_token('VIEW', 'CVREFKEYWORD ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
557 fnd_file.put_line(fnd_file.log, fnd_message.get);
558
559 -- Get all the reocords from interface table with status = 'N'
560 FOR new_refkwd_rec IN int_refkwd_cur LOOP
561
562 BEGIN
563 -- record level initialization
564 l_rowid := NULL;
565 g_error_code := NULL;
566
567 -- log record level processing message
568 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
569 fnd_message.set_token('KEY', 'KEYWORD');
570 fnd_message.set_token('VALUE', new_refkwd_rec.keyword);
571 fnd_file.put_line(fnd_file.log, fnd_message.get);
572
573
574 -- validate mandatory fields have values.
575 IF new_refkwd_rec.keyword IS NULL THEN
576 g_error_code := '1037';
577 END IF;
578
579 IF g_error_code IS NULL THEN
580
581 -- check whether corresponding rec already exists
582 OPEN old_refkwd_cur(new_refkwd_rec.keyword);
583 FETCH old_refkwd_cur INTO l_rowid;
584 CLOSE old_refkwd_cur;
585
586
587 -- If not found then insert
588 IF l_rowid IS NULL THEN
589 BEGIN
590 igs_uc_ref_keywords_pkg.insert_row --IGSXI29B.pls
591 (
592 x_rowid => l_rowid
593 ,x_keyword => new_refkwd_rec.keyword
594 ,x_imported => 'Y'
595 ,x_mode => 'R'
596 );
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 g_error_code := '9999';
601 fnd_file.put_line(fnd_file.log, SQLERRM);
602 END;
603
604 ELSE -- update
605 BEGIN
606 igs_uc_ref_keywords_pkg.update_row --IGSXI29B.pls
607 (
608 x_rowid => l_rowid
609 ,x_keyword => new_refkwd_rec.keyword
610 ,x_imported => 'Y'
611 ,x_mode => 'R'
612 );
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 g_error_code := '9998';
617 fnd_file.put_line(fnd_file.log, SQLERRM);
618 END;
619 END IF;
620
621 END IF; -- error not null
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 -- catch any unhandled/unexpected errors while processing a record.
626 -- This would enable processing to continue with subsequent records.
627
628 -- Close any Open cursors
629 IF old_refkwd_cur%ISOPEN THEN
630 CLOSE old_refkwd_cur;
631 END IF;
632
633 g_error_code := '1055';
634 fnd_file.put_line(fnd_file.log, SQLERRM);
635 END;
636
637 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
638 -- while processing the record.
639 IF g_error_code IS NOT NULL THEN
640 UPDATE igs_uc_crkywd_ints
641 SET error_code = g_error_code
642 WHERE rowid = new_refkwd_rec.rowid;
643
644 -- log error message/meaning.
645 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
646 -- update error count
647 g_error_rec_cnt := g_error_rec_cnt + 1;
651 SET record_status = 'D',
648
649 ELSE
650 UPDATE igs_uc_crkywd_ints
652 error_code = NULL
653 WHERE rowid = new_refkwd_rec.rowid;
654
655 g_success_rec_cnt := g_success_rec_cnt + 1;
656 END IF;
657
658 END LOOP;
659
660 COMMIT;
661 -- log processing complete for this view
662 igs_uc_proc_ucas_data.log_proc_complete('CVREFKEYWORD', g_success_rec_cnt, g_error_rec_cnt);
663
664 EXCEPTION
665 -- Process should continue with processing of other view data
666 WHEN OTHERS THEN
667 ROLLBACK;
668 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
669 fnd_message.set_token('VIEW', 'CVREFKEYWORD'||' - '||SQLERRM);
670 fnd_file.put_line(fnd_file.log, fnd_message.get);
671 END process_cvrefkeyword;
672
673
674 PROCEDURE process_cvrefpocc IS
675 /******************************************************************
676 Created By : rgangara
677 Date Created By : 12-JUNE-2003
678 Purpose : For processing REFPOCC data from UCAS
679 Known limitations,enhancements,remarks:
680 Change History
681 Who When What
682 jbaber 15-Sep-05 Removed NULL check on socialclass and socioeconomic
683 for bug 4589994
684 ***************************************************************** */
685 l_rowid VARCHAR2(26) := NULL;
686
687 -- Get new interface records
688 CURSOR int_refpocc_cur IS
689 SELECT rowid
690 ,pocc
691 ,socialclass
692 ,occupationtext
693 ,alternativetext
694 ,alternateclass1
695 ,alternateclass2
696 ,socioeconomic
697 FROM igs_uc_crefpoc_ints
698 WHERE record_status = 'N';
699
700 -- check whether corresponding record already exists.
701 CURSOR old_refpocc_cur (p_pocc igs_uc_crefpoc_ints.pocc%TYPE) IS
702 SELECT rowid
703 FROM igs_uc_ref_pocc
704 WHERE pocc = p_pocc ;
705
706 BEGIN
707 -- initialize variables
708 g_success_rec_cnt := 0;
709 g_error_rec_cnt := 0;
710
711 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
712 fnd_message.set_token('VIEW', 'CVREFPOCC ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
713 fnd_file.put_line(fnd_file.log, fnd_message.get);
714
715 -- Get all the reocords from interface table with status = 'N'
716 FOR new_refpocc_rec IN int_refpocc_cur LOOP
717
718 BEGIN
719
720 -- record level initialization
721 l_rowid := NULL;
722 g_error_code := NULL;
723
724 -- log record level processing message
725 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
726 fnd_message.set_token('KEY', 'POCC');
727 fnd_message.set_token('VALUE', new_refpocc_rec.pocc);
728 fnd_file.put_line(fnd_file.log, fnd_message.get);
729
730
731 -- validate mandatory fields have values.
732 IF new_refpocc_rec.pocc IS NULL THEN
733 g_error_code := '1037';
734 END IF;
735
736
737 IF g_error_code IS NULL THEN
738
739 -- check whether corresponding rec already exists
740 OPEN old_refpocc_cur(new_refpocc_rec.pocc);
741 FETCH old_refpocc_cur INTO l_rowid;
742 CLOSE old_refpocc_cur;
743
744
745 -- If not found then insert
746 IF l_rowid IS NULL THEN
747
748 BEGIN
749 igs_uc_ref_pocc_pkg.insert_row --IGSXI31B.pls
750 (
751 x_rowid => l_rowid
752 ,x_pocc => new_refpocc_rec.pocc
753 ,x_social_class => new_refpocc_rec.socialclass
754 ,x_occupation_text => new_refpocc_rec.occupationtext
755 ,x_alternative_text => new_refpocc_rec.alternativetext
756 ,x_alternative_class1 => new_refpocc_rec.alternateclass1
757 ,x_alternative_class2 => new_refpocc_rec.alternateclass2
758 ,x_imported => 'Y'
759 ,x_socio_economic => new_refpocc_rec.socioeconomic
760 ,x_mode => 'R'
761 );
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 g_error_code := '9999';
766 fnd_file.put_line(fnd_file.log, SQLERRM);
767 END;
768
769 ELSE -- update
770 BEGIN
771 igs_uc_ref_pocc_pkg.update_row --IGSXI31B.pls
772 (
773 x_rowid => l_rowid
774 ,x_pocc => new_refpocc_rec.pocc
775 ,x_social_class => new_refpocc_rec.socialclass
776 ,x_occupation_text => new_refpocc_rec.occupationtext
777 ,x_alternative_text => new_refpocc_rec.alternativetext
778 ,x_alternative_class1 => new_refpocc_rec.alternateclass1
779 ,x_alternative_class2 => new_refpocc_rec.alternateclass2
783 );
780 ,x_imported => 'Y'
781 ,x_socio_economic => new_refpocc_rec.socioeconomic
782 ,x_mode => 'R'
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 g_error_code := '9998';
788 fnd_file.put_line(fnd_file.log, SQLERRM);
789 END;
790 END IF;
791
792 END IF; -- error not null
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 -- catch any unhandled/unexpected errors while processing a record.
797 -- This would enable processing to continue with subsequent records.
798
799 -- Close any Open cursors
800 IF old_refpocc_cur%ISOPEN THEN
801 CLOSE old_refpocc_cur;
802 END IF;
803
804 g_error_code := '1055';
805 fnd_file.put_line(fnd_file.log, SQLERRM);
806 END;
807
808 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
809 -- while processing the record.
810 IF g_error_code IS NOT NULL THEN
811 UPDATE igs_uc_crefpoc_ints
812 SET error_code = g_error_code
813 WHERE rowid = new_refpocc_rec.rowid;
814
815 -- log error message/meaning.
816 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
817 -- update error count
818 g_error_rec_cnt := g_error_rec_cnt + 1;
819
820 ELSE
821 UPDATE igs_uc_crefpoc_ints
822 SET record_status = 'D',
823 error_code = NULL
824 WHERE rowid = new_refpocc_rec.rowid;
825
826 g_success_rec_cnt := g_success_rec_cnt + 1;
827 END IF;
828
829 END LOOP;
830
831 COMMIT;
832 -- log processing complete for this view
833 igs_uc_proc_ucas_data.log_proc_complete('CVREFPOCC', g_success_rec_cnt, g_error_rec_cnt);
834
835 EXCEPTION
836 WHEN OTHERS THEN
837 ROLLBACK;
838 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
839 fnd_message.set_token('VIEW', 'CVREFPOCC'||' - '||SQLERRM);
840 fnd_file.put_line(fnd_file.log, fnd_message.get);
841 END process_cvrefpocc;
842
843
844
845
846 PROCEDURE process_cvrefofferabbrev IS
847 /******************************************************************
848 Created By : rgangara
849 Date Created By : 12-JUNE-2003
850 Purpose : For processing REF Offer Abbreviations data from UCAS
851 Known limitations,enhancements,remarks:
852 Change History
853 Who When What
854 ***************************************************************** */
855 l_rowid VARCHAR2(26) := NULL;
856
857 -- Get new interface records
858 CURSOR int_refoffab_cur IS
859 SELECT offab.rowid,
860 offab.*
861 FROM igs_uc_croffab_ints offab
862 WHERE record_status = 'N';
863
864 -- check whether corresponding record already exists.
865 CURSOR old_refoffab_cur (p_abbrev igs_uc_croffab_ints.abbrevcode%TYPE) IS
866 SELECT roab.rowid,
867 roab.*
868 FROM igs_uc_ref_off_abrv roab
869 WHERE abbrev_code = p_abbrev ;
870
871 old_refoffab_rec old_refoffab_cur%ROWTYPE;
872
873 BEGIN
874
875 -- Populate seed abbreviation codes if not present in main table.
876 -- check for 'TO'.
877 OPEN old_refoffab_cur ('TO');
878 FETCH old_refoffab_cur INTO old_refoffab_rec;
879
880 IF old_refoffab_cur%NOTFOUND THEN
881 l_rowid := NULL;
882 -- create a new record with Abbreviation = 'TO' if not found
883 igs_uc_ref_off_abrv_pkg.insert_row --IGSXI30B.pls
884 (
888 ,x_abbrev_text => 'Tariff Offer'
885 x_rowid => l_rowid
886 ,x_abbrev_code => 'TO'
887 ,x_uv_updater => NULL
889 ,x_letter_format => 'B'
890 ,x_summary_char => NULL
891 ,x_uncond => 'N'
892 ,x_withdrawal => 'N'
893 ,x_release => 'N'
894 ,x_imported => 'N'
895 ,x_sent_to_ucas => 'Y'
896 ,x_deleted => 'N'
897 ,x_tariff => 'Y'
898 ,x_mode => 'R'
899 );
900 END IF;
901 CLOSE old_refoffab_cur;
902
903 old_refoffab_rec := NULL; -- initialize it back to NULL.
904
905 -- Populate seed abbreviation codes if not present in main table.
906 -- check for 'TE'
907 OPEN old_refoffab_cur ('TE');
908 FETCH old_refoffab_cur INTO old_refoffab_rec;
909
910 IF old_refoffab_cur%NOTFOUND THEN
911 l_rowid := NULL;
912 -- create a new record with Abbreviation = 'TE' if not found
913 igs_uc_ref_off_abrv_pkg.insert_row --IGSXI30B.pls
914 (
915 x_rowid => l_rowid
916 ,x_abbrev_code => 'TE'
917 ,x_uv_updater => NULL
918 ,x_abbrev_text => 'End Tariff Offer'
919 ,x_letter_format => 'B'
920 ,x_summary_char => NULL
921 ,x_uncond => 'N'
922 ,x_withdrawal => 'N'
923 ,x_release => 'N'
924 ,x_imported => 'N'
925 ,x_sent_to_ucas => 'Y'
926 ,x_deleted => 'N'
927 ,x_tariff => 'Y'
928 ,x_mode => 'R'
929 );
930 END IF;
931 CLOSE old_refoffab_cur;
932 old_refoffab_rec := NULL;
933
934 -- Interface table processing begins from here.
935 -- initialize variables
936 g_success_rec_cnt := 0;
937 g_error_rec_cnt := 0;
938
939 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
940 fnd_message.set_token('VIEW', 'CVREFOFFERABBREV ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
941 fnd_file.put_line(fnd_file.log, fnd_message.get);
942
943 -- Get all the reocords from interface table with status = 'N'
944 FOR new_refoffab_rec IN int_refoffab_cur LOOP
945
946 BEGIN
947
948 -- record level initialization
949 l_rowid := NULL;
950 g_error_code := NULL;
951 old_refoffab_rec := NULL;
952
953 -- log record level processing message
954 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
955 fnd_message.set_token('KEY', 'Abbreviation Code');
956 fnd_message.set_token('VALUE', new_refoffab_rec.abbrevcode);
957 fnd_file.put_line(fnd_file.log, fnd_message.get);
958
959
960 -- validate mandatory fields have values.
961 IF new_refoffab_rec.abbrevcode IS NULL OR new_refoffab_rec.letterformat IS NULL THEN
962 g_error_code := '1037';
963 END IF;
964
965 IF g_error_code IS NULL THEN
966
967 -- check whether corresponding rec already exists
968 OPEN old_refoffab_cur(new_refoffab_rec.abbrevcode);
969 FETCH old_refoffab_cur INTO old_refoffab_rec;
970 CLOSE old_refoffab_cur;
971
972
973 -- If not found then insert
974 IF old_refoffab_rec.rowid IS NULL THEN
975 BEGIN
976 igs_uc_ref_off_abrv_pkg.insert_row --IGSXI30B.pls
977 (
978 x_rowid => l_rowid
979 ,x_abbrev_code => new_refoffab_rec.abbrevcode
980 ,x_uv_updater => ''
981 ,x_abbrev_text => new_refoffab_rec.abbrevtext
982 ,x_letter_format => new_refoffab_rec.letterformat
983 ,x_summary_char => NVL(new_refoffab_rec.summarychar, 'N')
984 ,x_uncond => NVL(new_refoffab_rec.uncond, 'N')
985 ,x_withdrawal => NVL(new_refoffab_rec.withdrawal, 'N')
986 ,x_release => NVL(new_refoffab_rec.release, 'N')
987 ,x_imported => 'Y'
988 ,x_sent_to_ucas => 'Y'
989 ,x_deleted => 'N'
990 ,x_tariff => new_refoffab_rec.tariff
991 ,x_mode => 'R'
992 );
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 g_error_code := '9999';
997 fnd_file.put_line(fnd_file.log, SQLERRM);
998 END;
999
1000 ELSE -- update
1001 BEGIN
1002 igs_uc_ref_off_abrv_pkg.update_row --IGSXI30B.pls
1003 (
1004 x_rowid => old_refoffab_rec.rowid
1005 ,x_abbrev_code => old_refoffab_rec.abbrev_code
1006 ,x_uv_updater => old_refoffab_rec.uv_updater
1007 ,x_abbrev_text => new_refoffab_rec.abbrevtext
1008 ,x_letter_format => new_refoffab_rec.letterformat
1009 ,x_summary_char => NVL(new_refoffab_rec.summarychar, 'N')
1010 ,x_uncond => NVL(new_refoffab_rec.uncond, 'N')
1014 ,x_sent_to_ucas => 'Y'
1011 ,x_withdrawal => NVL(new_refoffab_rec.withdrawal, 'N')
1012 ,x_release => NVL(new_refoffab_rec.release, 'N')
1013 ,x_imported => 'Y'
1015 ,x_deleted => 'N'
1016 ,x_tariff => new_refoffab_rec.tariff
1017 ,x_mode => 'R'
1018 );
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 g_error_code := '9998';
1023 fnd_file.put_line(fnd_file.log, SQLERRM);
1024 END;
1025 END IF;
1026
1027 END IF; -- error not null
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 -- catch any unhandled/unexpected errors while processing a record.
1032 -- This would enable processing to continue with subsequent records.
1033
1034 -- Close any Open cursors
1035 IF old_refoffab_cur%ISOPEN THEN
1036 CLOSE old_refoffab_cur;
1037 END IF;
1038
1039 g_error_code := '1055';
1040 fnd_file.put_line(fnd_file.log, SQLERRM);
1041 END;
1042
1043 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1044 -- while processing the record.
1045 IF g_error_code IS NOT NULL THEN
1046 UPDATE igs_uc_croffab_ints
1047 SET error_code = g_error_code
1048 WHERE rowid = new_refoffab_rec.rowid;
1049
1050 -- log error message/meaning.
1051 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1052 -- update error count
1053 g_error_rec_cnt := g_error_rec_cnt + 1;
1054
1055
1056 ELSE
1057 UPDATE igs_uc_croffab_ints
1058 SET record_status = 'D',
1059 error_code = NULL
1060 WHERE rowid = new_refoffab_rec.rowid;
1061
1062 g_success_rec_cnt := g_success_rec_cnt + 1;
1063 END IF;
1064
1065 END LOOP;
1066
1067 COMMIT;
1068 -- log processing complete for this view
1069 igs_uc_proc_ucas_data.log_proc_complete('CVREFOFFERABBREV', g_success_rec_cnt, g_error_rec_cnt);
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 ROLLBACK;
1074 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1075 fnd_message.set_token('VIEW', 'CVREFOFFERABBREV'||' - '||SQLERRM);
1076 fnd_file.put_line(fnd_file.log, fnd_message.get);
1077 END process_cvrefofferabbrev;
1078
1079
1080
1081
1082 PROCEDURE process_cvrefsubj IS
1083 /******************************************************************
1084 Created By : rgangara
1085 Date Created By : 12-JUNE-2003
1086 Purpose : For processing Subjects data from UCAS
1087 Known limitations,enhancements,remarks:
1088 Change History
1089 Who When What
1090 ***************************************************************** */
1091 l_rowid VARCHAR2(26) := NULL;
1092
1093 -- Get new interface records
1094 CURSOR int_refsubj_cur IS
1095 SELECT rowid,
1096 subjcode,
1097 subjtext,
1098 subjabbrev,
1099 ebl_subj
1100 FROM igs_uc_crsubj_ints
1101 WHERE record_status = 'N';
1102
1103 -- check whether corresponding record already exists.
1104 CURSOR old_refsubj_cur (p_subject igs_uc_crsubj_ints.subjcode%TYPE) IS
1105 SELECT rowid
1106 FROM igs_uc_ref_subj
1107 WHERE SUBJ_CODE = p_subject ;
1108
1109 BEGIN
1110 -- initialize variables
1111 g_success_rec_cnt := 0;
1112 g_error_rec_cnt := 0;
1113
1114 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1115 fnd_message.set_token('VIEW', 'CVREFSUBJ ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1116 fnd_file.put_line(fnd_file.log, fnd_message.get);
1117
1118 -- Get all the reocords from interface table with status = 'N'
1119 FOR new_refsubj_rec IN int_refsubj_cur LOOP
1120
1121 BEGIN
1122
1123 -- record level initialization
1124 l_rowid := NULL;
1125 g_error_code := NULL;
1126
1127 -- log record level processing message
1128 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
1129 fnd_message.set_token('KEY', 'SUBJECT ');
1130 fnd_message.set_token('VALUE', new_refsubj_rec.subjcode);
1131 fnd_file.put_line(fnd_file.log, fnd_message.get);
1132
1133
1134 -- validate mandatory fields have values.
1135 IF new_refsubj_rec.subjcode IS NULL OR new_refsubj_rec.SUBJTEXT IS NULL THEN
1136 g_error_code := '1037';
1137 END IF;
1138
1139 IF g_error_code IS NULL THEN
1140
1141 -- check whether corresponding rec already exists
1142 OPEN old_refsubj_cur(new_refsubj_rec.subjcode);
1143 FETCH old_refsubj_cur INTO l_rowid;
1144 CLOSE old_refsubj_cur;
1145
1146
1147 -- If not found then insert
1148 IF l_rowid IS NULL THEN
1152 x_rowid => l_rowid,
1149 BEGIN
1150 igs_uc_ref_subj_pkg.Insert_row
1151 (
1153 x_subj_code => new_refsubj_rec.subjcode,
1154 x_subj_text => new_refsubj_rec.subjtext ,
1155 x_subj_abbrev => new_refsubj_rec.subjabbrev,
1156 x_ebl_subj => new_refsubj_rec.ebl_subj,
1157 x_imported => 'Y',
1158 x_mode => 'R'
1159 );
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 g_error_code := '9999';
1164 fnd_file.put_line(fnd_file.log, SQLERRM);
1165 END;
1166
1167 ELSE -- update
1168 BEGIN
1169 igs_uc_ref_subj_pkg.update_row
1170 (
1171 x_rowid => l_rowid,
1172 x_subj_code => new_refsubj_rec.subjcode,
1173 x_subj_text => new_refsubj_rec.subjtext ,
1174 x_subj_abbrev => new_refsubj_rec.subjabbrev,
1175 x_ebl_subj => new_refsubj_rec.ebl_subj,
1176 x_imported => 'Y',
1177 x_mode => 'R'
1178 );
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 g_error_code := '9998';
1183 fnd_file.put_line(fnd_file.log, SQLERRM);
1184 END;
1185 END IF;
1186
1187 END IF; -- error not null
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 -- catch any unhandled/unexpected errors while processing a record.
1192 -- This would enable processing to continue with subsequent records.
1193
1194 -- Close any Open cursors
1195 IF old_refsubj_cur%ISOPEN THEN
1196 CLOSE old_refsubj_cur;
1197 END IF;
1198
1199 g_error_code := '1055';
1200 fnd_file.put_line(fnd_file.log, SQLERRM);
1201 END;
1202
1203 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1204 -- while processing the record.
1205 IF g_error_code IS NOT NULL THEN
1206 UPDATE igs_uc_crsubj_ints
1207 SET error_code = g_error_code
1208 WHERE rowid = new_refsubj_rec.rowid;
1209
1210 -- log error message/meaning.
1211 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1212 -- update error count
1213 g_error_rec_cnt := g_error_rec_cnt + 1;
1214
1215 ELSE
1216 UPDATE igs_uc_crsubj_ints
1217 SET record_status = 'D',
1218 error_code = NULL
1219 WHERE rowid = new_refsubj_rec.rowid;
1220
1221 g_success_rec_cnt := g_success_rec_cnt + 1;
1222 END IF;
1223
1224 END LOOP;
1225
1226 COMMIT;
1227 -- log processing complete for this view
1228 igs_uc_proc_ucas_data.log_proc_complete('CVREFSUBJ', g_success_rec_cnt, g_error_rec_cnt);
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 ROLLBACK;
1233 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1234 fnd_message.set_token('VIEW', 'CVREFSUBJ'||' - '||SQLERRM);
1235 fnd_file.put_line(fnd_file.log, fnd_message.get);
1236 END process_cvrefsubj;
1237
1238
1239
1240
1241 PROCEDURE process_cvreftariff IS
1242 /******************************************************************
1243 Created By : rgangara
1244 Date Created By : 12-JUNE-2003
1245 Purpose : For processing Tariff data from UCAS
1246 Known limitations,enhancements,remarks:
1247 Change History
1248 Who When What
1249 ***************************************************************** */
1250
1251 -- Get new interface records
1252 CURSOR int_reftariff_cur IS
1253 SELECT rowid,
1254 examlevel,
1255 examgrade,
1256 tariffscore
1257 FROM igs_uc_ctariff_ints
1258 WHERE record_status = 'N';
1259
1260 -- check whether corresponding record already exists.
1261 CURSOR old_reftariff_cur (p_examlevel igs_uc_ctariff_ints.examlevel%TYPE,
1262 p_examgrade igs_uc_ctariff_ints.examgrade%TYPE) IS
1263 SELECT rtrf.rowid,
1264 rtrf.*
1265 FROM igs_uc_ref_tariff rtrf
1266 WHERE rtrf.exam_level = p_examlevel
1267 AND rtrf.exam_grade = p_examgrade;
1268
1269 old_tariff_rec old_reftariff_cur%ROWTYPE;
1270
1271 BEGIN
1272 -- initialize variables
1273 g_success_rec_cnt := 0;
1274 g_error_rec_cnt := 0;
1275
1276 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1277 fnd_message.set_token('VIEW', 'CVREFTARIFF ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1278 fnd_file.put_line(fnd_file.log, fnd_message.get);
1279
1280 -- Get all the reocords from interface table with status = 'N'
1281 FOR new_reftariff_rec IN int_reftariff_cur LOOP
1282
1283 BEGIN
1284 -- record level initialization
1288 -- log record level processing message
1285 g_error_code := NULL;
1286 old_tariff_rec := NULL;
1287
1289 fnd_message.set_name('IGS','IGS_UC_PROC_REFTARIFF_REC');
1290 fnd_message.set_token('EXAMLVL' , new_reftariff_rec.examlevel);
1291 fnd_message.set_token('EXAMGRADE', new_reftariff_rec.examgrade);
1292 fnd_file.put_line(fnd_file.log, fnd_message.get);
1293
1294 -- validate mandatory fields have values.
1295 IF new_reftariff_rec.examlevel IS NULL OR new_reftariff_rec.examgrade IS NULL OR new_reftariff_rec.tariffscore IS NULL THEN
1296 g_error_code := '1037';
1297 END IF;
1298
1299
1300 IF g_error_code IS NULL THEN
1301
1302 -- check whether corresponding rec already exists
1303 OPEN old_reftariff_cur(new_reftariff_rec.examlevel, new_reftariff_rec.examgrade);
1304 FETCH old_reftariff_cur INTO old_tariff_rec;
1305 CLOSE old_reftariff_cur;
1306
1307
1308 -- If not found then insert
1309 IF old_tariff_rec.rowid IS NULL THEN
1310 BEGIN
1311 igs_uc_ref_tariff_pkg.insert_row --
1312 (
1313 x_rowid => old_tariff_rec.rowid
1314 ,x_exam_level => new_reftariff_rec.examlevel
1315 ,x_exam_grade => new_reftariff_rec.examgrade
1316 ,x_tariff_score => new_reftariff_rec.tariffscore
1317 ,x_imported =>'Y'
1318 ,x_mode =>'R'
1319 );
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 g_error_code := '9999';
1324 fnd_file.put_line(fnd_file.log, SQLERRM);
1325 END;
1326
1327 ELSE -- update
1328 BEGIN
1329 igs_uc_ref_tariff_pkg.update_row --
1330 (
1331 x_rowid => old_tariff_rec.rowid
1332 ,x_exam_level => old_tariff_rec.exam_level
1333 ,x_exam_grade => old_tariff_rec.exam_grade
1334 ,x_tariff_score => new_reftariff_rec.tariffscore
1335 ,x_imported =>'Y'
1336 ,x_mode =>'R'
1337 );
1338
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 g_error_code := '9998';
1342 fnd_file.put_line(fnd_file.log, SQLERRM);
1343 END;
1344 END IF;
1345
1346 END IF; -- error not null
1347
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 -- catch any unhandled/unexpected errors while processing a record.
1351 -- This would enable processing to continue with subsequent records.
1352
1353 -- Close any Open cursors
1354 IF old_reftariff_cur%ISOPEN THEN
1355 CLOSE old_reftariff_cur;
1356 END IF;
1357
1358 g_error_code := '1055';
1359 fnd_file.put_line(fnd_file.log, SQLERRM);
1360 END;
1361
1362 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1363 -- while processing the record.
1364 IF g_error_code IS NOT NULL THEN
1365 UPDATE igs_uc_ctariff_ints
1366 SET error_code = g_error_code
1367 WHERE rowid = new_reftariff_rec.rowid;
1368
1369 -- log error message/meaning.
1370 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1371
1372 -- update error count
1373 g_error_rec_cnt := g_error_rec_cnt + 1;
1374
1375 ELSE
1376 UPDATE igs_uc_ctariff_ints
1377 SET record_status = 'D',
1378 error_code = NULL
1379 WHERE rowid = new_reftariff_rec.rowid;
1380
1381 g_success_rec_cnt := g_success_rec_cnt + 1;
1382 END IF;
1383
1384 END LOOP;
1385
1386 COMMIT;
1387 -- log processing complete for this view
1388 igs_uc_proc_ucas_data.log_proc_complete('CVREFTARIFF', g_success_rec_cnt, g_error_rec_cnt);
1389
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 ROLLBACK;
1393 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1394 fnd_message.set_token('VIEW', 'CVREFTARIFF'||' - '||SQLERRM);
1395 fnd_file.put_line(fnd_file.log, fnd_message.get);
1396
1397 END process_cvreftariff;
1398
1399
1400 PROCEDURE process_cvjointadmissions IS
1401 /******************************************************************
1402 Created By : rgangara
1403 Date Created By : 12-JUNE-2003
1404 Purpose : For processing Joint Admissions data from UCAS
1405 Known limitations,enhancements,remarks:
1406 Change History
1407 Who When What
1408 ***************************************************************** */
1409 l_rowid VARCHAR2(26) := NULL;
1410
1411 -- Get new interface records
1412 CURSOR int_cjntadm_cur IS
1413 SELECT rowid
1414 ,childinst
1415 ,parentinst1
1416 ,parentinst2
1420 FROM igs_uc_cjntadm_ints
1417 ,parentinst3
1418 ,parentinst4
1419 ,parentinst5
1421 WHERE record_status = 'N';
1422
1423 -- check whether corresponding record already exists.
1424 CURSOR old_cjntadm_cur (p_childinst igs_uc_cjntadm_ints.childinst%TYPE) IS
1425 SELECT rowid
1426 FROM igs_uc_jnt_adm_inst
1427 WHERE child_inst = p_childinst ;
1428
1429 CURSOR chk_inst_cur (p_inst igs_uc_com_inst.inst%TYPE) IS
1430 SELECT 'X'
1431 FROM igs_uc_com_inst
1432 WHERE inst = p_inst;
1433
1434 chk_inst_rec chk_inst_cur%ROWTYPE;
1435 l_inst_exists VARCHAR2(1);
1436
1437 BEGIN
1438 -- initialize variables
1439 g_success_rec_cnt := 0;
1440 g_error_rec_cnt := 0;
1441
1442 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1443 fnd_message.set_token('VIEW', 'CVJOINTADMISSIONS ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1444 fnd_file.put_line(fnd_file.log, fnd_message.get);
1445
1446 -- Get all the reocords from interface table with status = 'N'
1447 FOR new_cjntadm_rec IN int_cjntadm_cur LOOP
1448
1449 BEGIN
1450 -- record level initialization
1451 l_rowid := NULL;
1452 g_error_code := NULL;
1453
1454 -- log record level processing message
1455 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
1456 fnd_message.set_token('KEY' , 'CHILDINST ');
1457 fnd_message.set_token('VALUE', new_cjntadm_rec.childinst);
1458 fnd_file.put_line(fnd_file.log, fnd_message.get);
1459
1460
1461 -- validate mandatory fields have values.
1462 IF new_cjntadm_rec.childinst IS NULL OR new_cjntadm_rec.parentinst1 IS NULL THEN
1463 g_error_code := '1037';
1464 END IF;
1465
1466 -- validate that the child institution field has valid institution code value.
1467 IF g_error_code IS NULL THEN
1468 OPEN chk_inst_cur (new_cjntadm_rec.childinst);
1469 FETCH chk_inst_cur INTO chk_inst_rec;
1470
1471 IF chk_inst_cur%NOTFOUND THEN
1472 g_error_code := '1027';
1473 END IF;
1474
1475 CLOSE chk_inst_cur;
1476 END IF;
1477
1478 -- validate that the parent institution 1 field has valid institution code value.
1479 IF g_error_code IS NULL THEN
1480
1481 OPEN chk_inst_cur (new_cjntadm_rec.parentinst1);
1482 FETCH chk_inst_cur INTO chk_inst_rec;
1483
1484 IF chk_inst_cur%NOTFOUND THEN
1485 g_error_code := '1028';
1486 END IF;
1487
1488 CLOSE chk_inst_cur;
1489 END IF;
1490
1491 -- validate that the parent institution 2 field has valid institution code value.
1492 IF g_error_code IS NULL AND new_cjntadm_rec.parentinst2 IS NOT NULL THEN
1493
1494 OPEN chk_inst_cur (new_cjntadm_rec.parentinst2);
1495 FETCH chk_inst_cur INTO chk_inst_rec;
1496
1497 IF chk_inst_cur%NOTFOUND THEN
1498 g_error_code := '1029';
1499 END IF;
1500
1501 CLOSE chk_inst_cur;
1502 END IF;
1503
1504 -- validate that the parent institution 3 field has valid institution code value.
1505 IF g_error_code IS NULL AND new_cjntadm_rec.parentinst3 IS NOT NULL THEN
1506
1507 OPEN chk_inst_cur (new_cjntadm_rec.parentinst3);
1508 FETCH chk_inst_cur INTO chk_inst_rec;
1509
1510 IF chk_inst_cur%NOTFOUND THEN
1511 g_error_code := '1030';
1512 END IF;
1513
1514 CLOSE chk_inst_cur;
1515 END IF;
1516
1517 -- validate that the parent institution 4 field has valid institution code value.
1518 IF g_error_code IS NULL AND new_cjntadm_rec.parentinst4 IS NOT NULL THEN
1519
1520 OPEN chk_inst_cur (new_cjntadm_rec.parentinst4);
1521 FETCH chk_inst_cur INTO chk_inst_rec;
1522
1523 IF chk_inst_cur%NOTFOUND THEN
1524 g_error_code := '1031';
1525 END IF;
1526
1527 CLOSE chk_inst_cur;
1531 IF g_error_code IS NULL AND new_cjntadm_rec.parentinst5 IS NOT NULL THEN
1528 END IF;
1529
1530 -- validate that the parent institution 5 field has valid institution code value.
1532
1533 OPEN chk_inst_cur (new_cjntadm_rec.parentinst5);
1534 FETCH chk_inst_cur INTO chk_inst_rec;
1535
1536 IF chk_inst_cur%NOTFOUND THEN
1537 g_error_code := '1032';
1538 END IF;
1539
1540 CLOSE chk_inst_cur;
1541 END IF;
1542
1543
1544 IF g_error_code IS NULL THEN
1545
1546 -- check whether corresponding rec already exists
1547 OPEN old_cjntadm_cur(new_cjntadm_rec.childinst);
1548 FETCH old_cjntadm_cur INTO l_rowid;
1549 CLOSE old_cjntadm_cur;
1550
1551
1552 -- If not found then insert
1553 IF l_rowid IS NULL THEN
1554 BEGIN
1555 igs_uc_jnt_adm_inst_pkg.insert_row --
1556 (
1557 x_rowid => l_rowid
1558 ,x_child_inst => new_cjntadm_rec.childinst
1559 ,x_parent_inst1 => new_cjntadm_rec.parentinst1
1560 ,x_parent_inst2 => new_cjntadm_rec.parentinst2
1561 ,x_parent_inst3 => new_cjntadm_rec.parentinst3
1562 ,x_parent_inst4 => new_cjntadm_rec.parentinst4
1563 ,x_parent_inst5 => new_cjntadm_rec.parentinst5
1564 ,x_mode =>'R'
1565 );
1566
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 g_error_code := '9999';
1570 fnd_file.put_line(fnd_file.log, SQLERRM);
1571 END;
1572
1573 ELSE -- update
1574 BEGIN
1575 igs_uc_jnt_adm_inst_pkg.update_row
1576 (
1577 x_rowid => l_rowid
1578 ,x_child_inst => new_cjntadm_rec.childinst
1579 ,x_parent_inst1 => new_cjntadm_rec.parentinst1
1580 ,x_parent_inst2 => new_cjntadm_rec.parentinst2
1581 ,x_parent_inst3 => new_cjntadm_rec.parentinst3
1582 ,x_parent_inst4 => new_cjntadm_rec.parentinst4
1583 ,x_parent_inst5 => new_cjntadm_rec.parentinst5
1584 ,x_mode =>'R'
1585 );
1586
1587 EXCEPTION
1588 WHEN OTHERS THEN
1589 g_error_code := '9998';
1590 fnd_file.put_line(fnd_file.log, SQLERRM);
1591 END;
1592 END IF;
1593
1594 END IF; -- error not null
1595
1596
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 -- catch any unhandled/unexpected errors while processing a record.
1600 -- This would enable processing to continue with subsequent records.
1601
1602 -- Close any Open cursors
1603 IF old_cjntadm_cur%ISOPEN THEN
1604 CLOSE old_cjntadm_cur;
1605 END IF;
1606
1607 IF chk_inst_cur%ISOPEN THEN
1608 CLOSE chk_inst_cur;
1609 END IF;
1610
1611 g_error_code := '1055';
1612 fnd_file.put_line(fnd_file.log, SQLERRM);
1613 END;
1614
1615 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1616 -- while processing the record.
1617 IF g_error_code IS NOT NULL THEN
1618 UPDATE igs_uc_cjntadm_ints
1619 SET error_code = g_error_code
1620 WHERE rowid = new_cjntadm_rec.rowid;
1621
1622 -- log error message/meaning.
1623 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1624 -- update error count
1625 g_error_rec_cnt := g_error_rec_cnt + 1;
1626
1627 ELSE
1628 UPDATE igs_uc_cjntadm_ints
1629 SET record_status = 'D',
1630 error_code = NULL
1631 WHERE rowid = new_cjntadm_rec.rowid;
1632
1633 g_success_rec_cnt := g_success_rec_cnt + 1;
1634 END IF;
1635
1636 END LOOP;
1637
1638 COMMIT;
1639 -- log processing complete for this view
1640 igs_uc_proc_ucas_data.log_proc_complete('CVJOINTADMISSIONS', g_success_rec_cnt, g_error_rec_cnt);
1641
1642 EXCEPTION
1643 WHEN OTHERS THEN
1644 ROLLBACK;
1645 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1646 fnd_message.set_token('VIEW', 'CVJOINTADMISSIONS'||' - '||SQLERRM);
1647 fnd_file.put_line(fnd_file.log, fnd_message.get);
1648 END process_cvjointadmissions;
1649
1650
1651 PROCEDURE process_cvrefcountry IS
1652 /******************************************************************
1653 Created By : jbaber
1654 Date Created By : 14-July-2006
1655 Purpose : For processing Ref Country data
1656 Known limitations,enhancements,remarks:
1657 Change History
1658 Who When What
1659 ***************************************************************** */
1660 l_rowid VARCHAR2(26) := NULL;
1661
1662 -- Get new interface records
1663 CURSOR int_country_cur IS
1664 SELECT rowid
1665 ,countrycode
1666 ,description
1667 ,type
1668 FROM igs_uc_country_ints
1669 WHERE record_status = 'N';
1670
1671 -- check whether corresponding record already exists.
1672 CURSOR old_country_cur (p_country_code igs_uc_ref_country.country_code%TYPE) IS
1673 SELECT rowid
1674 FROM igs_uc_ref_country
1675 WHERE country_code = p_country_code;
1676
1677 BEGIN
1678 -- initialize variables
1679 g_success_rec_cnt := 0;
1680 g_error_rec_cnt := 0;
1681
1682 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1683 fnd_message.set_token('VIEW', 'CVREFCOUNTRY ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1684 fnd_file.put_line(fnd_file.log, fnd_message.get);
1685
1686 -- Get all the reocords from interface table with status = 'N'
1687 FOR new_country_rec IN int_country_cur LOOP
1688
1689 BEGIN
1690 -- record level initialization
1691 l_rowid := NULL;
1692 g_error_code := NULL;
1693
1694 -- log record level processing message
1695 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
1696 fnd_message.set_token('KEY' , 'COUNTRY CODE');
1697 fnd_message.set_token('VALUE', new_country_rec.countrycode);
1698 fnd_file.put_line(fnd_file.log, fnd_message.get);
1699
1700
1701 -- validate mandatory fields have values.
1702 IF new_country_rec.countrycode IS NULL THEN
1703 g_error_code := '1037';
1704 END IF;
1705
1706 IF g_error_code IS NULL THEN
1707
1708 -- check whether corresponding rec already exists
1709 OPEN old_country_cur(new_country_rec.countrycode);
1710 FETCH old_country_cur INTO l_rowid;
1711 CLOSE old_country_cur;
1712
1713
1714 -- If not found then insert
1715 IF l_rowid IS NULL THEN
1716 BEGIN
1717 igs_uc_ref_country_pkg.insert_row --
1718 (
1719 x_rowid => l_rowid
1720 ,x_country_code => new_country_rec.countrycode
1721 ,x_description => new_country_rec.description
1722 ,x_type => new_country_rec.type
1723 ,x_imported => 'Y'
1724 ,x_mode => 'R'
1725 );
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 g_error_code := '9999';
1730 fnd_file.put_line(fnd_file.log, SQLERRM);
1731 END;
1732
1733 ELSE -- update
1734 BEGIN
1735 igs_uc_ref_country_pkg.update_row
1736 (
1737 x_rowid => l_rowid
1738 ,x_country_code => new_country_rec.countrycode
1739 ,x_description => new_country_rec.description
1740 ,x_type => new_country_rec.type
1741 ,x_imported => 'Y'
1742 ,x_mode => 'R'
1743 );
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 g_error_code := '9998';
1748 fnd_file.put_line(fnd_file.log, SQLERRM);
1749 END;
1750 END IF;
1751
1752 END IF; -- error not null
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 -- catch any unhandled/unexpected errors while processing a record.
1757 -- This would enable processing to continue with subsequent records.
1758
1759 -- Close any Open cursors
1760 IF old_country_cur%ISOPEN THEN
1761 CLOSE old_country_cur;
1762 END IF;
1763
1764 g_error_code := '1055';
1765 fnd_file.put_line(fnd_file.log, SQLERRM);
1766 END;
1767
1768 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1769 -- while processing the record.
1770 IF g_error_code IS NOT NULL THEN
1771 UPDATE igs_uc_country_ints
1772 SET error_code = g_error_code
1773 WHERE rowid = new_country_rec.rowid;
1774
1775 -- log error message/meaning.
1776 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1777 -- update error count
1778 g_error_rec_cnt := g_error_rec_cnt + 1;
1779
1780 ELSE
1781 UPDATE igs_uc_country_ints
1782 SET record_status = 'D',
1783 error_code = NULL
1784 WHERE rowid = new_country_rec.rowid;
1785
1786 g_success_rec_cnt := g_success_rec_cnt + 1;
1787 END IF;
1788
1789
1790 END LOOP;
1791
1792 COMMIT;
1793 -- log processing complete for this view
1794 igs_uc_proc_ucas_data.log_proc_complete('CVREFCOUNTRY', g_success_rec_cnt, g_error_rec_cnt);
1795
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 ROLLBACK;
1799 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1800 fnd_message.set_token('VIEW', 'CVREFPRECOUNTRY'||' - '||SQLERRM);
1801 fnd_file.put_line(fnd_file.log, fnd_message.get);
1802 END process_cvrefcountry;
1803
1804
1805
1806
1807 END igs_uc_proc_reference_data;