1 PACKAGE BODY igs_uc_proc_com_inst_data AS
2 /* $Header: IGSUC66B.pls 120.5 2006/09/15 01:38:11 jbaber noship $ */
3
4 g_success_rec_cnt NUMBER;
5 g_error_rec_cnt NUMBER;
6 g_error_code igs_uc_ccontrl_ints.error_code%TYPE;
7 g_crnt_institute igs_uc_defaults.current_inst_code%TYPE;
8
9
10 PROCEDURE common_data_setup (errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER) IS
12 /******************************************************************
13 Created By : rgangara
14 Date Created By : 12-JUNE-2003
15 Purpose : For general derivations which are required while
16 processing reference data views.
17 views
18 Known limitations,enhancements,remarks:
19 Change History
20 Who When What
21 rgangara 31-DEC-03 Added Generate Party Number validation as part of Bug# 3327176
22 ******************************************************************/
23
24 -- Get the current institution code set in UCAS Setup for FTUG as all systems have the same.
25 CURSOR crnt_inst_cur IS
26 SELECT current_inst_code
27 FROM igs_uc_defaults
28 WHERE system_code = 'U';
29
30 BEGIN
31
32 OPEN crnt_inst_cur;
33 FETCH crnt_inst_cur INTO g_crnt_institute;
34 CLOSE crnt_inst_cur;
35
36 IF g_crnt_institute IS NULL THEN
37 fnd_message.set_name('IGS','IGS_UC_CURR_INST_NOT_SET');
38 errbuf := fnd_message.get;
39 fnd_file.put_line(fnd_file.log, errbuf);
40 retcode := 2;
41 RETURN;
42 END IF;
43
44 -- validate that the Generate party Num profile is set to Y else log error message
45 -- Added this validation as part of Bug# 3327176
46 IF fnd_profile.value('HZ_GENERATE_PARTY_NUMBER') <> 'Y' THEN
47 fnd_message.set_name('IGS','IGS_UC_GEN_PRTY_PROF_NOT_SET');
48 errbuf := fnd_message.get;
49 fnd_file.put_line(fnd_file.log, errbuf);
50 retcode := 2;
51 RETURN;
52 END IF;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 retcode := 2;
57 fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
58 fnd_message.set_token('NAME','IGS_UC_PROC_COM_INST_DATA.COMMON_DATA_SETUP '||' - '||SQLERRM);
59 errbuf := fnd_message.get;
60 fnd_file.put_line(fnd_file.LOG, errbuf);
61 app_exception.raise_exception;
62 END common_data_setup;
63
64
65
66
67 PROCEDURE process_uvinstitution IS
68 /******************************************************************
69 Created By : rgangara
70 Date Created By : 12-JUNE-2003
71 Purpose : For processing UVINSTITITUTION view
72 Known limitations,enhancements,remarks:
73 Change History
74 Who When What
75 rbezawad 27-Apr-04 Added code to insert a record into igs_uc_inst_control
76 when there is no existing record for bug 3595582.
77 jbaber 03-Aug-05 Import ALL fields instead of just some for bug 4532072
78 jchakrab 08-Aug-2005 Modified for UC315 - removed validation for insttype
79 and updater, as these columns are no longer used
80 ***************************************************************** */
81
82 l_rowcnt NUMBER ;
83 l_rowid VARCHAR2(26) ;
84 g_error_code igs_uc_ccontrl_ints.error_code%TYPE;
85
86 CURSOR int_uinst_cur IS
87 SELECT uinst.rowid,
88 uinst.*
89 FROM igs_uc_uinst_ints uinst
90 WHERE record_status = 'N';
91
92 CURSOR chk_inst_ctl IS
93 SELECT count(*)
94 FROM igs_uc_inst_control;
95
96 CURSOR old_inst_cur IS
97 SELECT inst.rowid row_id,
98 inst.*
99 FROM igs_uc_inst_control inst;
100
101 old_inst_ctl_rec old_inst_cur%ROWTYPE;
102
103 BEGIN
104
105 -- initialize variables
106 g_success_rec_cnt := 0;
107 g_error_rec_cnt := 0;
108 l_rowcnt := 0;
109
110 -- log record processing message
111 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
112 fnd_message.set_token('VIEW', 'UVINSTITUTION ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
113 fnd_file.put_line(fnd_file.log, fnd_message.get);
114
115 -- record level initialization
116 g_error_code := NULL;
117
118
119 -- check the count of records in the main table.
120 OPEN chk_inst_ctl;
121 FETCH chk_inst_ctl INTO l_rowcnt;
122 CLOSE chk_inst_ctl;
123
124
125 -- check that the Institution data is setup. If not available then insert a record.
126 IF(l_rowcnt = 0) THEN
127 BEGIN
128 igs_uc_inst_control_pkg.insert_row -- IGSXI20B.pls
129 (
130 x_rowid => l_rowid
131 ,x_updater => '-1'
132 ,x_inst_type => 'C'
133 ,x_inst_short_name => NULL
134 ,x_inst_name => NULL
135 ,x_inst_full_name => NULL
136 ,x_switchboard_tel_no => NULL
137 ,x_decision_cards => NULL
138 ,x_record_cards => NULL
139 ,x_labels => NULL
140 ,x_weekly_mov_list_seq => NULL
141 ,x_weekly_mov_paging => NULL
142 ,x_form_seq => NULL
143 ,x_ebl_required => NULL
144 ,x_ebl_media_1or2 => NULL
145 ,x_ebl_media_3 => NULL
146 ,x_ebl_1or2_merged => NULL
147 ,x_ebl_1or2_board_group => NULL
148 ,x_ebl_3_board_group => NULL
149 ,x_ebl_nc_app => NULL
150 ,x_ebl_major_key1 => NULL
151 ,x_ebl_major_key2 => NULL
152 ,x_ebl_major_key3 => NULL
153 ,x_ebl_minor_key1 => NULL
154 ,x_ebl_minor_key2 => NULL
155 ,x_ebl_minor_key3 => NULL
156 ,x_ebl_final_key => NULL
157 ,x_odl1 => NULL
158 ,x_odl1a => NULL
159 ,x_odl2 => NULL
160 ,x_odl3 => NULL
161 ,x_odl_summer => NULL
162 ,x_odl_route_b => NULL
163 ,x_monthly_seq => NULL
164 ,x_monthly_paper => NULL
165 ,x_monthly_page => NULL
166 ,x_monthly_type => NULL
167 ,x_june_list_seq => NULL
168 ,x_june_labels => NULL
169 ,x_june_num_labels => NULL
170 ,x_course_analysis => NULL
171 ,x_campus_used => NULL
172 ,x_d3_doc_required => NULL
173 ,x_clearing_accept_copy_form => NULL
174 ,x_online_message => NULL
175 ,x_ethnic_list_seq => NULL
176 ,x_mode => 'R'
177 ,x_starx => NULL
178 );
179 l_rowcnt := 1;
180 EXCEPTION
181 WHEN OTHERS THEN
182 g_error_code := '9999';
183 fnd_file.put_line(fnd_file.log, SQLERRM);
184 END;
185 END IF;
186
187 -- check that the Institution data is setup correctly. Only 1 record must exist. Please check the setup.
188 IF(l_rowcnt > 1) THEN
189 g_error_code := '1039';
190 END IF;
191
192
193 IF(l_rowcnt = 1) THEN
194 OPEN old_inst_cur;
195 FETCH old_inst_cur INTO old_inst_ctl_rec;
196 CLOSE old_inst_cur;
197
198 FOR new_uinst_rec IN int_uinst_cur LOOP
199
200 BEGIN
201
202 IF g_error_code IS NULL THEN
203
204 BEGIN
205 igs_uc_inst_control_pkg.update_row -- IGSXI20B.pls
206 (
207 x_rowid => old_inst_ctl_rec.row_id
208 ,x_updater => new_uinst_rec.updater
209 ,x_inst_type => new_uinst_rec.insttype
210 ,x_inst_short_name => new_uinst_rec.instshortname
211 ,x_inst_name => new_uinst_rec.instname
212 ,x_inst_full_name => new_uinst_rec.instfullname
213 ,x_switchboard_tel_no => new_uinst_rec.switchboardtelno
214 ,x_decision_cards => new_uinst_rec.decisioncards
215 ,x_record_cards => new_uinst_rec.recordcards
216 ,x_labels => new_uinst_rec.labels
217 ,x_weekly_mov_list_seq => new_uinst_rec.weeklymovlistseq
218 ,x_weekly_mov_paging => new_uinst_rec.weeklymovpaging
219 ,x_form_seq => new_uinst_rec.formseq
220 ,x_ebl_required => new_uinst_rec.eblrequired
221 ,x_ebl_media_1or2 => new_uinst_rec.eblmedia1or2
222 ,x_ebl_media_3 => new_uinst_rec.eblmedia3
223 ,x_ebl_1or2_merged => new_uinst_rec.ebl1or2merged
224 ,x_ebl_1or2_board_group => new_uinst_rec.ebl1or2boardgroup
225 ,x_ebl_3_board_group => new_uinst_rec.ebl3boardgroup
226 ,x_ebl_nc_app => new_uinst_rec.eblncapp
227 ,x_ebl_major_key1 => new_uinst_rec.eblmajorkey1
228 ,x_ebl_major_key2 => new_uinst_rec.eblmajorkey2
229 ,x_ebl_major_key3 => new_uinst_rec.eblmajorkey3
230 ,x_ebl_minor_key1 => new_uinst_rec.eblminorkey1
231 ,x_ebl_minor_key2 => new_uinst_rec.eblminorkey2
232 ,x_ebl_minor_key3 => new_uinst_rec.eblminorkey3
233 ,x_ebl_final_key => new_uinst_rec.eblfinalkey
234 ,x_odl1 => new_uinst_rec.odl1
235 ,x_odl1a => new_uinst_rec.odl1a
236 ,x_odl2 => new_uinst_rec.odl2
237 ,x_odl3 => new_uinst_rec.odl3
238 ,x_odl_summer => new_uinst_rec.odlsummer
239 ,x_odl_route_b => new_uinst_rec.odlrouteb
240 ,x_monthly_seq => new_uinst_rec.monthlyseq
241 ,x_monthly_paper => new_uinst_rec.monthlypaper
242 ,x_monthly_page => new_uinst_rec.monthlypage
243 ,x_monthly_type => new_uinst_rec.monthlytype
244 ,x_june_list_seq => new_uinst_rec.junelistseq
245 ,x_june_labels => new_uinst_rec.junelabels
246 ,x_june_num_labels => new_uinst_rec.junenumlabels
247 ,x_course_analysis => new_uinst_rec.courseanalysis
248 ,x_campus_used => new_uinst_rec.campusused
249 ,x_d3_doc_required => new_uinst_rec.d3docsrequired
250 ,x_clearing_accept_copy_form => new_uinst_rec.clearingacceptcopyform
251 ,x_online_message => new_uinst_rec.onlinemessage
252 ,x_ethnic_list_seq => new_uinst_rec.ethniclistseq
253 ,x_mode => 'R'
254 ,x_starx => new_uinst_rec.starx
255 ); EXCEPTION
256 WHEN OTHERS THEN
257 g_error_code := '9998';
258 fnd_file.put_line(fnd_file.log, SQLERRM);
259 END;
260
261 END IF; -- error code check
262
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 -- catch any unhandled/unexpected errors while processing a record.
267 -- This would enable processing to continue with subsequent records.
268
269 -- Close any Open cursors
270 IF chk_inst_ctl%ISOPEN THEN
271 CLOSE chk_inst_ctl;
272 END IF;
273
274 IF old_inst_cur%ISOPEN THEN
275 CLOSE old_inst_cur;
276 END IF;
277
278 g_error_code := '1055';
279 fnd_file.put_line(fnd_file.log, SQLERRM);
280 END;
281
282 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
283 -- while processing the record.
284 IF g_error_code IS NOT NULL THEN
285
286 UPDATE igs_uc_uinst_ints
287 SET error_code = g_error_code
288 WHERE rowid = new_uinst_rec.rowid;
289
290 -- log error message/meaning.
291 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
292 -- update error count
293 g_error_rec_cnt := g_error_rec_cnt + 1;
294
295 ELSE
296
297 UPDATE igs_uc_uinst_ints
298 SET record_status = 'D',
299 error_code = NULL
300 WHERE rowid = new_uinst_rec.rowid;
301
302 g_success_rec_cnt := g_success_rec_cnt + 1;
303
304 END IF;
305
306 END LOOP;
307
308 END IF; -- rowcount = 1
309
310
311 COMMIT;
312 -- log process complete
313 igs_uc_proc_ucas_data.log_proc_complete('UVINSTITUTION', g_success_rec_cnt, g_error_rec_cnt);
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 -- Process should continue with processing of other view data
318 ROLLBACK;
319 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
320 fnd_message.set_token('VIEW', 'UVINSTITUTION'||' - '||SQLERRM);
321 fnd_file.put_line(fnd_file.log, fnd_message.get);
322 END process_uvinstitution;
323
324
325 /* ============================================================================================================= */
326 -- INSTITUTION VIEWS
327 /* ============================================================================================================= */
328
329
330 PROCEDURE process_uvofferabbrev IS
331 /******************************************************************
332 Created By : rgangara
333 Date Created By : 12-JUNE-2003
334 Purpose : For processing Updateable Offer Abbreviations data from UCAS
335 Known limitations,enhancements,remarks:
336 Change History
337 Who When What
338 jchakrab 08-Aug-2005 Modified for UC315
339 - Added update-facility for offer abbrevs as UvOfferAbbrev view
340 is no longer updateable via odbc-link
341 - Only AbbrevText can be updateable via net-update system
342 - Removed validation for letterformat, as this column is no longer used
343 ***************************************************************** */
344
345 -- Get new interface records
346 CURSOR int_uvoffabrv_cur IS
347 SELECT rowid
348 ,abbrevid
349 ,updater
350 ,abbrevtext
351 ,letterformat
352 ,summarychar
353 ,abbrevuse
354 FROM igs_uc_uofabrv_ints
355 WHERE record_status = 'N';
356
357 -- check whether corresponding record already exists.
358 CURSOR old_uvoffabrv_cur (p_abbrev igs_uc_ref_off_abrv.abbrev_code%TYPE) IS
359 SELECT rowid,
360 abbrev_code,
361 uv_timestamp,
362 uv_updater,
363 abbrev_text,
364 letter_format,
365 summary_char,
366 uncond,
367 withdrawal,
368 release,
369 imported,
370 sent_to_ucas,
371 deleted,
372 tariff
373 FROM igs_uc_ref_off_abrv
374 WHERE abbrev_code = p_abbrev;
375
376 old_uvoffabrv_rec old_uvoffabrv_cur%ROWTYPE;
377 l_rec_status igs_uc_uofabrv_ints.record_status%TYPE;
378 l_uncond igs_uc_ref_off_abrv.uncond%TYPE;
379 l_withdrawal igs_uc_ref_off_abrv.withdrawal%TYPE;
380 l_char_abbrev igs_uc_ref_off_abrv.abbrev_code%TYPE;
381
382 BEGIN
383 -- initialize variables
384 g_success_rec_cnt := 0;
385 g_error_rec_cnt := 0;
386 l_rec_status := NULL;
387
388 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
389 fnd_message.set_token('VIEW', 'UVOFFERABBREV ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
390 fnd_file.put_line(fnd_file.log, fnd_message.get);
391
392 -- Get all the reocords from interface table with status = 'N'
393 FOR new_uvoffabrv_rec IN int_uvoffabrv_cur LOOP
394
395 BEGIN
396
397 -- record level initialization
398 g_error_code := NULL;
399 l_rec_status := 'N';
400 old_uvoffabrv_rec := NULL;
401
402 -- log message for the record being processed
403 fnd_message.set_name('IGS','IGS_UC_PROC_INTERFACE_REC');
404 fnd_message.set_token('KEY', 'Abbreviation Code ');
405 fnd_message.set_token('VALUE', TO_CHAR(new_uvoffabrv_rec.abbrevid));
406 fnd_file.put_line(fnd_file.log, fnd_message.get);
407
408
409 -- validate mandatory fields have values.
410 -- jchakrab removed validation check for letterformat as its no longer used
411 IF new_uvoffabrv_rec.abbrevid IS NULL THEN
412 g_error_code := '1037';
413 END IF;
414
415 -- Since UVOfferabbreviations data for abbrevid is NUMERIC only and the data in main table
416 -- is VARCHAR2 format. Hence conversion needed and that too for 2 digits only.
417 l_char_abbrev := LTRIM(TO_CHAR(new_uvoffabrv_rec.abbrevid,'09'),' ');
418
419 -- derive UNCOND based on the value for abbrev usage
420 IF new_uvoffabrv_rec.abbrevuse = 'U' THEN
421 l_uncond := 'Y';
422 ELSE
423 l_uncond := 'N';
424 END IF;
425
426 -- derive WITHDRAWAL based on the value for abbrev usage
427 IF new_uvoffabrv_rec.abbrevuse = 'W' THEN
428 l_withdrawal := 'Y';
429 ELSE
430 l_withdrawal := 'N';
431 END IF;
432
433
434 IF g_error_code IS NULL THEN
435
436 -- check whether corresponding rec already exists
437 OPEN old_uvoffabrv_cur(l_char_abbrev);
438 FETCH old_uvoffabrv_cur INTO old_uvoffabrv_rec;
439 CLOSE old_uvoffabrv_cur;
440
441
442 -- If not found then insert
443 IF old_uvoffabrv_rec.rowid IS NULL THEN
444 BEGIN
445 igs_uc_ref_off_abrv_pkg.insert_row --IGSXI30B.pls
446 (
447 x_rowid => old_uvoffabrv_rec.rowid -- i.e. NULL.
448 ,x_abbrev_code => l_char_abbrev
449 ,x_uv_updater => new_uvoffabrv_rec.updater
450 ,x_abbrev_text => new_uvoffabrv_rec.abbrevtext
451 ,x_letter_format => new_uvoffabrv_rec.letterformat
452 ,x_summary_char => NVL(new_uvoffabrv_rec.summarychar, 'N')
453 ,x_uncond => l_uncond
454 ,x_withdrawal => l_withdrawal
455 ,x_release => 'N'
456 ,x_imported => 'N'
457 ,x_sent_to_ucas => 'Y'
458 ,x_deleted => 'N'
459 ,x_tariff => NULL
460 ,x_mode => 'R'
461 );
462
463 l_rec_status := 'D';
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 g_error_code := '9999';
468 fnd_file.put_line(fnd_file.log, SQLERRM);
469 END;
470
471 ELSE -- update
472
473 BEGIN
474 igs_uc_ref_off_abrv_pkg.update_row --IGSXI30B.pls
475 (
476 x_rowid => old_uvoffabrv_rec.rowid
477 ,x_abbrev_code => old_uvoffabrv_rec.abbrev_code
478 ,x_uv_updater => old_uvoffabrv_rec.uv_updater
479 ,x_abbrev_text => new_uvoffabrv_rec.abbrevtext -- only abbrev_text is updateable
480 ,x_letter_format => old_uvoffabrv_rec.letter_format
481 ,x_summary_char => old_uvoffabrv_rec.summary_char
482 ,x_uncond => old_uvoffabrv_rec.uncond
483 ,x_withdrawal => old_uvoffabrv_rec.withdrawal
484 ,x_release => old_uvoffabrv_rec.release
485 ,x_imported => old_uvoffabrv_rec.imported
486 ,x_sent_to_ucas => old_uvoffabrv_rec.sent_to_ucas
487 ,x_deleted => old_uvoffabrv_rec.deleted
488 ,x_tariff => old_uvoffabrv_rec.tariff
489 ,x_mode => 'R'
490 );
491
492 l_rec_status := 'D';
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 g_error_code := '9999';
497 fnd_file.put_line(fnd_file.log, SQLERRM);
498 END;
499
500 END IF;
501
502 END IF; -- error not null
503
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 -- catch any unhandled/unexpected errors while processing a record.
508 -- This would enable processing to continue with subsequent records.
509
510 -- Close any Open cursors
511 IF old_uvoffabrv_cur%ISOPEN THEN
512 CLOSE old_uvoffabrv_cur;
513 END IF;
514
515 g_error_code := '1055';
516 fnd_file.put_line(fnd_file.log, SQLERRM);
517 END;
518
519 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
520 -- while processing the record.
521 IF g_error_code IS NOT NULL THEN
522
523 UPDATE igs_uc_uofabrv_ints
524 SET error_code = g_error_code
525 WHERE rowid = new_uvoffabrv_rec.rowid;
526
527 -- log error message/meaning.
528 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
529
530 -- update error count
531 g_error_rec_cnt := g_error_rec_cnt + 1;
532
533 ELSE
534 UPDATE igs_uc_uofabrv_ints
535 SET record_status = l_rec_status,
536 error_code = NULL
537 WHERE rowid = new_uvoffabrv_rec.rowid;
538
539 g_success_rec_cnt := g_success_rec_cnt + 1;
540 END IF;
541
542 END LOOP;
543
544 COMMIT;
545 -- log processing complete for this view
546 igs_uc_proc_ucas_data.log_proc_complete('UVOFFERABBREV', g_success_rec_cnt, g_error_rec_cnt);
547
548 EXCEPTION
549 -- Process should continue with processing of other view data
550 WHEN OTHERS THEN
551 ROLLBACK;
552 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
553 fnd_message.set_token('VIEW', 'UVOFFERABBREV'||' - '||SQLERRM);
554 fnd_file.put_line(fnd_file.log, fnd_message.get);
555
556 END process_uvofferabbrev;
557
558
559
560 PROCEDURE process_cvinstitution IS
561 /******************************************************************
562 Created By : rgangara
563 Date Created By : 12-JUNE-2003
564 Purpose : For processing Institution details from UCAS
565 Known limitations,enhancements,remarks:
566 Change History
567 Who When What
568 ******************************************************************/
569 l_rowid VARCHAR2(26) ;
570
571 -- Get new interface records
572 CURSOR int_cvinst_cur IS
573 SELECT cvinst.rowid,
574 cvinst.*
575 FROM igs_uc_cinst_ints cvinst
576 WHERE record_status = 'N';
577
578 -- check whether corresponding record already exists.
579 CURSOR old_inst_cur (p_inst igs_uc_cinst_ints.inst%TYPE) IS
580 SELECT cominst.rowid,
581 cominst.*
582 FROM igs_uc_com_inst cominst
583 WHERE cominst.inst = p_inst ;
584
585 old_inst_rec old_inst_cur%ROWTYPE;
586
587 BEGIN
588 -- initialize variables
589 g_success_rec_cnt := 0;
590 g_error_rec_cnt := 0;
591
592 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
593 fnd_message.set_token('VIEW', 'CVINSTITUTION ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
594 fnd_file.put_line(fnd_file.log, fnd_message.get);
595
596 -- Get all the reocords from interface table with status = 'N'
597 FOR new_cvinst_rec IN int_cvinst_cur LOOP
598
599 BEGIN
600 -- record level initialization
601 l_rowid := NULL;
602 g_error_code := NULL;
603 old_inst_rec := NULL;
604
605 -- log record level processing message
606 fnd_message.set_name('IGS','IGS_UC_INST_PROC');
607 fnd_message.set_token('INST', new_cvinst_rec.inst);
608 fnd_file.put_line(fnd_file.log, fnd_message.get);
609
610
611 -- validate mandatory fields have values.
612 IF new_cvinst_rec.inst IS NULL THEN
613 g_error_code := '1037';
614 END IF;
615
616
617 IF g_error_code IS NULL THEN
618
619 -- check whether corresponding rec already exists
620 OPEN old_inst_cur(new_cvinst_rec.inst);
621 FETCH old_inst_cur INTO old_inst_rec;
622 CLOSE old_inst_cur;
623
624
625 -- If not found then insert
626 IF old_inst_rec.rowid IS NULL THEN
627
628 BEGIN
629 -- insert a new record in the main table
630 igs_uc_com_inst_pkg.insert_row -- IGSXI09B.pls
631 (
632 x_rowid => old_inst_rec.rowid
633 ,x_inst => new_cvinst_rec.inst
634 ,x_inst_code => new_cvinst_rec.instcode
635 ,x_inst_name => new_cvinst_rec.instname
636 ,x_ucas => 'Y' -- For FTUG System
637 ,x_gttr => NVL(new_cvinst_rec.gttr, 'N')
638 ,x_swas => NVL(new_cvinst_rec.swas, 'N')
639 ,x_nmas => NVL(new_cvinst_rec.nmas, 'N')
640 ,x_imported => 'Y'
641 ,x_mode => 'R'
642 );
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 g_error_code := '9999';
647 fnd_file.put_line(fnd_file.log, SQLERRM);
648 END;
649
650 ELSE -- update
651
652 BEGIN
653 -- update a new record in the main table
654 igs_uc_com_inst_pkg.update_row -- IGSXI09B.pls
655 (
656 x_rowid => old_inst_rec.rowid
657 ,x_inst => old_inst_rec.inst
658 ,x_inst_code => new_cvinst_rec.instcode
659 ,x_inst_name => new_cvinst_rec.instname
660 ,x_ucas => 'Y' -- For FTUG System
661 ,x_gttr => NVL(old_inst_rec.gttr, 'N')
662 ,x_swas => NVL(old_inst_rec.swas, 'N')
663 ,x_nmas => NVL(old_inst_rec.nmas, 'N')
664 ,x_imported => 'Y'
665 ,x_mode => 'R'
666 );
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 g_error_code := '9998';
671 fnd_file.put_line(fnd_file.log, SQLERRM);
672 END;
673
674 END IF; -- insert/update
675
676 END IF; -- error not null
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 -- catch any unhandled/unexpected errors while processing a record.
681 -- This would enable processing to continue with subsequent records.
682
683 -- Close any Open cursors
684 IF old_inst_cur%ISOPEN THEN
685 CLOSE old_inst_cur;
686 END IF;
687
688 g_error_code := '1055';
689 fnd_file.put_line(fnd_file.log, SQLERRM);
690 END;
691
692
693 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
694 -- while processing the record.
695 IF g_error_code IS NOT NULL THEN
696
697 UPDATE igs_uc_cinst_ints
698 SET error_code = g_error_code
699 WHERE rowid = new_cvinst_rec.rowid;
700
701 -- log error message/meaning.
702 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
703
704 -- update error count
705 g_error_rec_cnt := g_error_rec_cnt + 1;
706
707 ELSE
708
709 UPDATE igs_uc_cinst_ints
710 SET record_status = 'D',
711 error_code = NULL
712 WHERE rowid = new_cvinst_rec.rowid;
713
714 g_success_rec_cnt := g_success_rec_cnt + 1;
715 END IF;
716
717 END LOOP;
718
719 COMMIT;
720 -- log processing complete for this view
721 igs_uc_proc_ucas_data.log_proc_complete('CVINSTITUTION', g_success_rec_cnt, g_error_rec_cnt);
722
723 EXCEPTION
724 -- Process should continue with processing of other view data
725 WHEN OTHERS THEN
726 ROLLBACK;
727 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
728 fnd_message.set_token('VIEW', 'CVINSTITUTION'||' - '||SQLERRM);
729 fnd_file.put_line(fnd_file.log, fnd_message.get);
730 END process_cvinstitution ;
731
732
733
734 PROCEDURE process_cveblsubject IS
735 /******************************************************************
736 Created By : rgangara
737 Date Created By : 12-JUNE-2003
738 Purpose : For processing Exam board subject details from UCAS
739 Known limitations,enhancements,remarks:
740 Change History
741 Who When What
742 dsridhar 06-JUL-2003 Bug No:3083850. Validation added to check the existance
743 of parent records in igs_uc_ref_awrdbdy.
744 ******************************************************************/
745 l_rowid VARCHAR2(26) ;
746
747 -- Get new interface records
748 CURSOR int_cveblsubj_cur IS
749 SELECT cves.rowid,
750 cves.*
751 FROM igs_uc_ceblsbj_ints cves
752 WHERE cves.record_status = 'N';
753
754 -- check whether corresponding record already exists.
755 CURSOR old_eblsubj_cur (p_sub_id igs_uc_ceblsbj_ints.subjectid%TYPE) IS
756 SELECT ces.rowid,
757 ces.*
758 FROM igs_uc_com_ebl_subj ces
759 WHERE ces.subject_id = p_sub_id ;
760
761 -- check for existance of a record in igs_uc_ref_awrdbdy
762 CURSOR chk_awd_body (p_year igs_uc_ref_awrdbdy.year%TYPE,
763 p_sitting igs_uc_ref_awrdbdy.sitting%TYPE,
764 p_awd_body igs_uc_ref_awrdbdy.awarding_body%TYPE) IS
765 SELECT 'X'
766 FROM igs_uc_ref_awrdbdy
767 WHERE year = p_year AND
768 sitting = p_sitting AND
769 awarding_body = p_awd_body;
770
771 old_eblsubj_rec old_eblsubj_cur%ROWTYPE;
772 l_awd_body_flag VARCHAR2(1);
773
774 BEGIN
775 -- initialize variables
776 g_success_rec_cnt := 0;
777 g_error_rec_cnt := 0;
778
779 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
780 fnd_message.set_token('VIEW', 'CVEBLSUBJECT ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
781 fnd_file.put_line(fnd_file.log, fnd_message.get);
782
783 -- Get all the reocords from interface table with status = 'N'
784 FOR new_eblsubj_rec IN int_cveblsubj_cur LOOP
785
786 BEGIN
787 -- record level initialization
788 l_rowid := NULL;
789 g_error_code := NULL;
790 old_eblsubj_rec := NULL;
791 l_awd_body_flag := NULL;
792
793 -- log record level processing message
794 fnd_message.set_name('IGS','IGS_UC_SUBJ_PROC');
795 fnd_message.set_token('SUBJ', TO_CHAR(new_eblsubj_rec.subjectid));
796 fnd_file.put_line(fnd_file.log, fnd_message.get);
797
798
799 -- validate mandatory fields have values.
800 IF new_eblsubj_rec.subjectid IS NULL OR
801 new_eblsubj_rec.sitting IS NULL OR
802 new_eblsubj_rec.awardingbody IS NULL OR
803 new_eblsubj_rec.examlevel IS NULL OR
804 new_eblsubj_rec.year IS NULL THEN
805
806 g_error_code := '1037';
807 END IF;
808
809 -- validate for a record in igs_uc_ref_awrdbdy
810 IF g_error_code IS NULL THEN
811
812 OPEN chk_awd_body (new_eblsubj_rec.year, new_eblsubj_rec.sitting, new_eblsubj_rec.awardingbody);
813 FETCH chk_awd_body INTO l_awd_body_flag;
814
815 IF chk_awd_body%NOTFOUND THEN
816 g_error_code := '1060';
817 END IF;
818
819 CLOSE chk_awd_body;
820 END IF;
821
822 IF g_error_code IS NULL THEN
823
824 -- check whether corresponding rec already exists
825 OPEN old_eblsubj_cur(new_eblsubj_rec.subjectid);
826 FETCH old_eblsubj_cur INTO old_eblsubj_rec;
827 CLOSE old_eblsubj_cur;
828
829
830 -- If not found then insert
831 IF old_eblsubj_rec.rowid IS NULL THEN
832
833 BEGIN
834 -- insert a new record in the main table
835 igs_uc_com_ebl_subj_pkg.insert_row -- IGSXI08B.pls
836 (
837 x_rowid => old_eblsubj_rec.rowid
838 ,x_subject_id => new_eblsubj_rec.subjectid
839 ,x_year => new_eblsubj_rec.year
840 ,x_sitting => new_eblsubj_rec.sitting
841 ,x_awarding_body => new_eblsubj_rec.awardingbody
842 ,x_external_ref => new_eblsubj_rec.externalref
843 ,x_exam_level => new_eblsubj_rec.examlevel
844 ,x_title => new_eblsubj_rec.title
845 ,x_subject_code => NVL(new_eblsubj_rec.subjcode ,'ZZZZZZ')
846 ,x_imported => 'Y'
847 ,x_mode => 'R'
848 );
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 g_error_code := '9999';
853 fnd_file.put_line(fnd_file.log, SQLERRM);
854 END;
855
856 ELSE -- update
857
858 BEGIN
859 -- update a new record in the main table
860 igs_uc_com_ebl_subj_pkg.update_row -- IGSXI08B.pls
861 (
862 x_rowid => old_eblsubj_rec.rowid
863 ,x_subject_id => old_eblsubj_rec.subject_id
864 ,x_year => new_eblsubj_rec.year
865 ,x_sitting => new_eblsubj_rec.sitting
866 ,x_awarding_body => new_eblsubj_rec.awardingbody
867 ,x_external_ref => new_eblsubj_rec.externalref
868 ,x_exam_level => new_eblsubj_rec.examlevel
869 ,x_title => new_eblsubj_rec.title
870 ,x_subject_code => NVL(new_eblsubj_rec.subjcode ,'ZZZZZZ')
871 ,x_imported => 'Y'
872 ,x_mode => 'R'
873 );
874
875 EXCEPTION
876 WHEN OTHERS THEN
877 g_error_code := '9998';
878 fnd_file.put_line(fnd_file.log, SQLERRM);
879 END;
880
881 END IF; -- insert/update
882
883 END IF; -- error not null
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 -- catch any unhandled/unexpected errors while processing a record.
888 -- This would enable processing to continue with subsequent records.
889
890 -- Close any Open cursors
891 IF old_eblsubj_cur%ISOPEN THEN
892 CLOSE old_eblsubj_cur;
893 END IF;
894
895 g_error_code := '1055';
896 fnd_file.put_line(fnd_file.log, SQLERRM);
897 END;
898
899 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
900 -- while processing the record.
901 IF g_error_code IS NOT NULL THEN
902
903 UPDATE igs_uc_ceblsbj_ints
904 SET error_code = g_error_code
905 WHERE rowid = new_eblsubj_rec.rowid;
906
907 -- log error message/meaning.
908 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
909
910 -- update error count
911 g_error_rec_cnt := g_error_rec_cnt + 1;
912
913 ELSE
914
915 UPDATE igs_uc_ceblsbj_ints
916 SET record_status = 'D',
917 error_code = NULL
918 WHERE rowid = new_eblsubj_rec.rowid;
919
920 g_success_rec_cnt := g_success_rec_cnt + 1;
921 END IF;
922
923 END LOOP;
924
925 COMMIT;
926 -- log processing complete for this view
927 igs_uc_proc_ucas_data.log_proc_complete('CVEBLSUBJECT', g_success_rec_cnt, g_error_rec_cnt);
928
929 EXCEPTION
930 -- Process should continue with processing of other view data
931 WHEN OTHERS THEN
932 ROLLBACK;
933 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
934 fnd_message.set_token('VIEW', 'CVEBLSUBJECT'||' - '||SQLERRM);
935 fnd_file.put_line(fnd_file.log, fnd_message.get);
936 END process_cveblsubject ;
937
938
939
940 PROCEDURE process_cvschool IS
941 /******************************************************************
942 Created By : rgangara
943 Date Created By : 12-JUNE-2003
944 Purpose : For processing School details from UCAS
945 Known limitations,enhancements,remarks:
946 Change History
947 Who When What
948 ******************************************************************/
949 l_schsite_rowid VARCHAR2(26) ;
950
951 -- Get new interface records
952 CURSOR int_cvschool_cur IS
953 SELECT cvs.rowid,
954 cvs.*
955 FROM igs_uc_cvsch_ints cvs
956 WHERE cvs.record_status = 'N';
957
958 -- check whether corresponding record already exists.
959 CURSOR old_school_cur (p_school_id igs_uc_cvsch_ints.school%TYPE) IS
960 SELECT csh.rowid,
961 csh.*
962 FROM igs_uc_com_sch csh
963 WHERE csh.school = p_school_id ;
964
965
966 -- check for school type in Lookups
967 CURSOR chk_sch_type (p_sch_type igs_uc_cvsch_ints.schooltype%TYPE) IS
968 SELECT 'X'
969 FROM igs_uc_ref_codes
970 WHERE code_type = 'ST'
971 AND code = p_sch_type;
972
973 -- school site details
974 CURSOR old_schsite_cur (p_sch_id igs_uc_com_schsites.school%TYPE, p_site_cd igs_uc_com_schsites.sitecode%TYPE ) IS
975 SELECT a.ROWID
976 FROM igs_uc_com_schsites a
977 WHERE school = p_sch_id
978 AND sitecode = p_site_cd;
979
980
981 old_school_rec old_school_cur%ROWTYPE;
982 l_sch_type_flag VARCHAR2(1);
983
984 BEGIN
985 -- initialize variables
986 g_success_rec_cnt := 0;
987 g_error_rec_cnt := 0;
988
989 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
990 fnd_message.set_token('VIEW', 'CVSCHOOL ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
991 fnd_file.put_line(fnd_file.log, fnd_message.get);
992
993
994 -- Get all the reocords from interface table with status = 'N'
995 FOR new_school_rec IN int_cvschool_cur LOOP
996
997 BEGIN
998
999 -- record level initialization
1000 g_error_code := NULL;
1001 old_school_rec := NULL;
1002
1003
1004 -- log record level processing message
1005 fnd_message.set_name('IGS','IGS_UC_SCH_PROC');
1006 fnd_message.set_token('SCH', TO_CHAR(new_school_rec.school));
1007 fnd_file.put_line(fnd_file.log, fnd_message.get);
1008
1009
1010 -- validate mandatory fields have values.
1011 IF new_school_rec.school IS NULL OR new_school_rec.estabgrp IS NULL THEN
1012 g_error_code := '1037';
1013 END IF;
1014
1015 -- validate school type value
1016 IF g_error_code IS NULL THEN
1017
1018 OPEN chk_sch_type (NVL(new_school_rec.schooltype, 'A'));
1019 FETCH chk_sch_type INTO l_sch_type_flag;
1020
1021 IF chk_sch_type%NOTFOUND THEN
1022 g_error_code := '1003';
1023 END IF;
1024
1025 CLOSE chk_sch_type;
1026 END IF;
1027
1028
1029 -- main processing begins
1030 IF g_error_code IS NULL THEN
1031
1032 -- check whether corresponding rec already exists
1033 OPEN old_school_cur(new_school_rec.school);
1034 FETCH old_school_cur INTO old_school_rec;
1035 CLOSE old_school_cur;
1036
1037
1038 -- If not found then insert
1039 IF old_school_rec.rowid IS NULL THEN
1040
1041 BEGIN
1042 -- insert a new record in the main table
1043 igs_uc_com_sch_pkg.insert_row -- IGSXI10B.pls
1044 (
1045 x_rowid => old_school_rec.rowid
1046 ,x_school => new_school_rec.school
1047 ,x_school_name => new_school_rec.schoolname
1048 ,x_name_change_date => NULL
1049 ,x_former_name => new_school_rec.formername
1050 ,x_ncn => new_school_rec.ncn
1051 ,x_edexcel_ncn => new_school_rec.edexcelncn
1052 ,x_dfee_code => new_school_rec.dfeecode
1053 ,x_country => new_school_rec.country
1054 ,x_lea => new_school_rec.lea
1055 ,x_ucas_status => new_school_rec.ucasstatus
1056 ,x_estab_group => new_school_rec.estabgrp
1057 ,x_school_type => NVL(new_school_rec.schooltype,'A')
1058 ,x_stats_date => NVL(new_school_rec.statsdate, TRUNC(SYSDATE))
1059 ,x_number_on_roll => NVL(new_school_rec.noroll ,0)
1060 ,x_number_in_5_form => NVL(new_school_rec.no5th ,0)
1061 ,x_number_in_6_form => NVL(new_school_rec.no6th ,0)
1062 ,x_number_to_he => NVL(new_school_rec.nohe ,0)
1063 ,x_imported => 'Y'
1064 ,x_mode => 'R'
1065 );
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 g_error_code := '9999';
1070 fnd_file.put_line(fnd_file.log, SQLERRM);
1071 END;
1072
1073 ELSE -- update
1074
1075 BEGIN
1076 -- update a new record in the main table
1077 igs_uc_com_sch_pkg.update_row -- IGSXI10B.pls
1078 (
1079 x_rowid => old_school_rec.rowid
1080 ,x_school => old_school_rec.school
1081 ,x_school_name => new_school_rec.schoolname
1082 ,x_name_change_date => NULL
1083 ,x_former_name => new_school_rec.formername
1084 ,x_ncn => new_school_rec.ncn
1085 ,x_edexcel_ncn => new_school_rec.edexcelncn
1086 ,x_dfee_code => new_school_rec.dfeecode
1087 ,x_country => new_school_rec.country
1088 ,x_lea => new_school_rec.lea
1089 ,x_ucas_status => new_school_rec.ucasstatus
1090 ,x_estab_group => new_school_rec.estabgrp
1091 ,x_school_type => NVL(new_school_rec.schooltype,'A')
1092 ,x_stats_date => NVL(new_school_rec.statsdate,TRUNC(SYSDATE))
1093 ,x_number_on_roll => NVL(new_school_rec.noroll ,0)
1094 ,x_number_in_5_form => NVL(new_school_rec.no5th ,0)
1095 ,x_number_in_6_form => NVL(new_school_rec.no6th ,0)
1096 ,x_number_to_he => NVL(new_school_rec.nohe ,0)
1097 ,x_imported => 'Y'
1098 ,x_mode => 'R'
1099 );
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 g_error_code := '9998';
1104 fnd_file.put_line(fnd_file.log, SQLERRM);
1105 END;
1106
1107 END IF; -- insert/update
1108
1109
1110 --- SCHOOL SITES processing begins here
1111 IF g_error_code IS NULL THEN
1112 -- Process School sites data now
1113 l_schsite_rowid := NULL;
1114
1115 OPEN old_schsite_cur(new_school_rec.school, NVL(new_school_rec.sitecode,'A'));
1116 FETCH old_schsite_cur INTO l_schsite_rowid;
1117 CLOSE old_schsite_cur;
1118
1119 fnd_message.set_name('IGS','IGS_UC_SCH_SITE_PROC');
1120 fnd_message.set_token('SCH', TO_CHAR(new_school_rec.school));
1121 fnd_message.set_token('SITE', NVL(new_school_rec.sitecode,'A'));
1122 fnd_file.put_line(fnd_file.log, fnd_message.get);
1123
1124 IF l_schsite_rowid IS NULL THEN
1125 BEGIN
1126 igs_uc_com_schsites_pkg.insert_row -- IGSXI11B.pls
1127 (
1128 x_rowid => l_schsite_rowid
1129 ,x_school => new_school_rec.school
1130 ,x_sitecode => NVL(new_school_rec.sitecode ,'A')
1131 ,x_address1 => new_school_rec.address1
1132 ,x_address2 => new_school_rec.address2
1133 ,x_address3 => new_school_rec.address3
1134 ,x_address4 => new_school_rec.address4
1135 ,x_postcode => new_school_rec.postcode
1136 ,x_mailsort => new_school_rec.mailsort
1137 ,x_town_key => new_school_rec.townkey
1138 ,x_county_key => new_school_rec.countykey
1139 ,x_country_code => new_school_rec.countrycode
1140 ,x_imported => 'Y'
1141 ,x_mode => 'R'
1142 );
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 g_error_code := '9999';
1146 fnd_file.put_line(fnd_file.log, SQLERRM);
1147 END;
1148
1149 ELSE
1150 -- update
1151 BEGIN
1152 igs_uc_com_schsites_pkg.update_row -- IGSXI11B.pls
1153 (
1154 x_rowid => l_schsite_rowid
1155 ,x_school => old_school_rec.school
1156 ,x_sitecode => NVL(new_school_rec.sitecode, 'A')
1157 ,x_address1 => new_school_rec.address1
1158 ,x_address2 => new_school_rec.address2
1159 ,x_address3 => new_school_rec.address3
1160 ,x_address4 => new_school_rec.address4
1161 ,x_postcode => new_school_rec.postcode
1162 ,x_mailsort => new_school_rec.mailsort
1163 ,x_town_key => new_school_rec.townkey
1164 ,x_county_key => new_school_rec.countykey
1165 ,x_country_code => new_school_rec.countrycode
1166 ,x_imported => 'Y'
1167 ,x_mode => 'R'
1168 );
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 g_error_code := '9998';
1172 fnd_file.put_line(fnd_file.log, SQLERRM);
1173 END;
1174
1175 END IF; -- insert/update school sites
1176
1177 END IF; -- for school sites processing
1178
1179
1180 END IF; -- error not null
1181
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 -- catch any unhandled/unexpected errors while processing a record.
1185 -- This would enable processing to continue with subsequent records.
1186
1187 -- Close any Open cursors
1188 IF old_school_cur%ISOPEN THEN
1189 CLOSE old_school_cur;
1190 END IF;
1191
1192
1193 IF chk_sch_type%ISOPEN THEN
1194 CLOSE chk_sch_type;
1195 END IF;
1196
1197
1198 IF old_schsite_cur%ISOPEN THEN
1199 CLOSE old_schsite_cur;
1200 END IF;
1201
1202 g_error_code := '1055';
1203 fnd_file.put_line(fnd_file.log, SQLERRM);
1204 END;
1205
1206 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1207 -- while processing the record.
1208 IF g_error_code IS NOT NULL THEN
1209
1210 UPDATE igs_uc_cvsch_ints
1211 SET error_code = g_error_code
1212 WHERE rowid = new_school_rec.rowid;
1213
1214 -- log error message/meaning.
1215 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1216
1217 -- update error count
1218 g_error_rec_cnt := g_error_rec_cnt + 1;
1219
1220 ELSE
1221
1222 UPDATE igs_uc_cvsch_ints
1223 SET record_status = 'D',
1224 error_code = NULL
1225 WHERE rowid = new_school_rec.rowid;
1226
1227 g_success_rec_cnt := g_success_rec_cnt + 1;
1228 END IF;
1229
1230 END LOOP;
1231
1232 COMMIT;
1233 -- log processing complete for this view
1234 igs_uc_proc_ucas_data.log_proc_complete('CVSCHOOL', g_success_rec_cnt, g_error_rec_cnt);
1235
1236 EXCEPTION
1237 -- Process should continue with processing of other view data
1238 WHEN OTHERS THEN
1239 ROLLBACK;
1240 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1241 fnd_message.set_token('VIEW', 'CVSCHOOL'||' - '||SQLERRM);
1242 fnd_file.put_line(fnd_file.log, fnd_message.get);
1243 END process_cvschool ;
1244
1245
1246 PROCEDURE process_cvschoolcontact IS
1247 /******************************************************************
1248 Created By : rgangara
1249 Date Created By : 12-JUNE-2003
1250 Purpose : For processing School contact details info. from UCAS
1251 Known limitations,enhancements,remarks:
1252 Change History
1253 Who When What
1254 ******************************************************************/
1255
1256 -- Get new interface records
1257 CURSOR int_cvschcnt_cur IS
1258 SELECT csci.rowid,
1259 csci.*
1260 FROM igs_uc_cschcnt_ints csci
1261 WHERE csci.record_status = 'N';
1262
1263 -- check whether corresponding record already exists.
1264 CURSOR old_schsite_cnt_cur (p_school igs_uc_cschcnt_ints.school%TYPE,
1265 p_site_code igs_uc_cschcnt_ints.sitecode%TYPE,
1266 p_contact_cd igs_uc_cschcnt_ints.contactcode%TYPE) IS
1267 SELECT csscn.rowid,
1268 csscn.*
1269 FROM igs_uc_com_scsicnts csscn
1270 WHERE csscn.school = p_school
1271 AND csscn.sitecode = p_site_code
1272 AND csscn.contact_code = p_contact_cd;
1273
1274 -- validate school
1275 CURSOR chk_school (p_school igs_uc_cschcnt_ints.school%TYPE) IS
1276 SELECT 'X'
1277 FROM igs_uc_com_sch
1278 WHERE school = p_school;
1279
1280 -- validate school site details
1281 CURSOR chk_schsite (p_school igs_uc_cschcnt_ints.school%TYPE, p_site_code igs_uc_cschcnt_ints.sitecode%TYPE) IS
1282 SELECT 'X'
1283 FROM igs_uc_com_schsites
1284 WHERE school = p_school
1285 AND sitecode = p_site_code;
1286
1287
1288 old_schsite_cnt_rec old_schsite_cnt_cur%ROWTYPE;
1289 l_check_flag VARCHAR2(1);
1290
1291 BEGIN
1292 -- initialize variables
1293 g_success_rec_cnt := 0;
1294 g_error_rec_cnt := 0;
1295 l_check_flag := NULL;
1296
1297 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1298 fnd_message.set_token('VIEW', 'CVSCHOOLCONTACT ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1299 fnd_file.put_line(fnd_file.log, fnd_message.get);
1300
1301 -- Get all the reocords from interface table with status = 'N'
1302 FOR new_schcntct_rec IN int_cvschcnt_cur LOOP
1303
1304 BEGIN
1305 -- record level initialization
1306 g_error_code := NULL;
1307 old_schsite_cnt_rec := NULL;
1308
1309
1310 -- log record level processing message
1311 fnd_message.set_name('IGS','IGS_UC_SCH_SITE_CNTCT_PROC');
1312 fnd_message.set_token('SCH', TO_CHAR(new_schcntct_rec.school));
1313 fnd_message.set_token('SITE', new_schcntct_rec.sitecode);
1314 fnd_message.set_token('CONTACT', TO_CHAR(new_schcntct_rec.contactcode));
1315 fnd_file.put_line(fnd_file.log, fnd_message.get);
1316
1317
1318 -- assigning default to sitecode and assigning to same record variable for further processing.
1319 new_schcntct_rec.sitecode := NVL(new_schcntct_rec.sitecode, 'A') ;
1320
1321 -- validate mandatory fields have values.
1322 IF new_schcntct_rec.school IS NULL OR new_schcntct_rec.contactcode IS NULL THEN
1323 g_error_code := '1037';
1324 END IF;
1325
1326 -- validate school value
1327 IF g_error_code IS NULL THEN
1328
1329 l_check_flag := NULL;
1330 OPEN chk_school (new_schcntct_rec.school);
1331 FETCH chk_school INTO l_check_flag;
1332 CLOSE chk_school;
1333
1334 IF l_check_flag IS NULL THEN
1335 g_error_code := '1004';
1336 END IF;
1337
1338 END IF;
1339
1340 -- validate school value
1341 IF g_error_code IS NULL THEN
1342
1343 l_check_flag := NULL; -- initialize again as it is being re-used.
1344 OPEN chk_schsite (new_schcntct_rec.school, new_schcntct_rec.sitecode);
1345 FETCH chk_schsite INTO l_check_flag;
1346
1347 IF chk_schsite%NOTFOUND THEN
1348 g_error_code := '1005';
1349 END IF;
1350
1351 CLOSE chk_schsite;
1352 END IF;
1353
1354
1355 -- main processing begins
1356 IF g_error_code IS NULL THEN
1357
1358 -- check whether corresponding rec already exists
1359 OPEN old_schsite_cnt_cur(new_schcntct_rec.school, new_schcntct_rec.sitecode, new_schcntct_rec.contactcode);
1360 FETCH old_schsite_cnt_cur INTO old_schsite_cnt_rec;
1361 CLOSE old_schsite_cnt_cur;
1362
1363 -- If not found then insert
1364 IF old_schsite_cnt_rec.rowid IS NULL THEN
1365
1366 BEGIN
1367 -- insert a new record in the main table
1368 igs_uc_com_scsicnts_pkg.insert_row --IGSXI12B.pls
1369 (
1370 x_rowid => old_schsite_cnt_rec.rowid
1371 ,x_school => new_schcntct_rec.school
1372 ,x_sitecode => new_schcntct_rec.sitecode
1373 ,x_contact_code => new_schcntct_rec.contactcode
1374 ,x_contact_post => new_schcntct_rec.contactpost
1375 ,x_contact_name => new_schcntct_rec.contactname
1376 ,x_telephone => new_schcntct_rec.telephone
1377 ,x_fax => new_schcntct_rec.fax
1378 ,x_email => new_schcntct_rec.email
1379 ,x_principal => NVL(new_schcntct_rec.principal,'N')
1380 ,x_lists => NVL(new_schcntct_rec.lists,'N')
1381 ,x_orders => NVL(new_schcntct_rec.orders,'N')
1382 ,x_forms => NVL(new_schcntct_rec.forms,'N')
1383 ,x_referee => NVL(new_schcntct_rec.referee,'N')
1384 ,x_careers => NVL(new_schcntct_rec.careers,'N')
1385 ,x_eas_contact => NVL(new_schcntct_rec.eascontact,'N')
1386 ,x_imported => 'Y'
1387 ,x_mode => 'R'
1388 );
1389
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 g_error_code := '9999';
1393 fnd_file.put_line(fnd_file.log, SQLERRM);
1394 END;
1395
1396 ELSE -- update
1397
1398 BEGIN
1399 -- update a new record in the main table
1400 igs_uc_com_scsicnts_pkg.update_row --IGSXI12B.pls
1401 (
1402 x_rowid => old_schsite_cnt_rec.rowid
1403 ,x_school => new_schcntct_rec.school
1404 ,x_sitecode => new_schcntct_rec.sitecode
1405 ,x_contact_code => new_schcntct_rec.contactcode
1406 ,x_contact_post => new_schcntct_rec.contactpost
1407 ,x_contact_name => new_schcntct_rec.contactname
1408 ,x_telephone => new_schcntct_rec.telephone
1409 ,x_fax => new_schcntct_rec.fax
1410 ,x_email => new_schcntct_rec.email
1411 ,x_principal => NVL(new_schcntct_rec.principal,'N')
1412 ,x_lists => NVL(new_schcntct_rec.lists,'N')
1413 ,x_orders => NVL(new_schcntct_rec.orders,'N')
1414 ,x_forms => NVL(new_schcntct_rec.forms,'N')
1415 ,x_referee => NVL(new_schcntct_rec.referee,'N')
1416 ,x_careers => NVL(new_schcntct_rec.careers,'N')
1417 ,x_eas_contact => NVL(new_schcntct_rec.eascontact,'N')
1418 ,x_imported => 'Y'
1419 ,x_mode => 'R'
1420 );
1421
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 g_error_code := '9998';
1425 fnd_file.put_line(fnd_file.log, SQLERRM);
1426 END;
1427
1428 END IF; -- insert/update
1429
1430 END IF; -- main processing
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 -- catch any unhandled/unexpected errors while processing a record.
1435 -- This would enable processing to continue with subsequent records.
1436
1437 -- Close any Open cursors
1438 IF old_schsite_cnt_cur%ISOPEN THEN
1439 CLOSE old_schsite_cnt_cur;
1440 END IF;
1441
1442
1443 IF chk_school%ISOPEN THEN
1444 CLOSE chk_school;
1445 END IF;
1446
1447
1448 IF chk_schsite%ISOPEN THEN
1449 CLOSE chk_schsite;
1450 END IF;
1451
1452 g_error_code := '1055';
1453 fnd_file.put_line(fnd_file.log, SQLERRM);
1454 END;
1455
1456 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1457 -- while processing the record.
1458 IF g_error_code IS NOT NULL THEN
1459
1460 UPDATE igs_uc_cschcnt_ints
1461 SET error_code = g_error_code
1462 WHERE rowid = new_schcntct_rec.rowid;
1463
1464 -- log error message/meaning.
1465 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1466
1467 -- update error count
1468 g_error_rec_cnt := g_error_rec_cnt + 1;
1469
1470 ELSE
1471
1472 UPDATE igs_uc_cschcnt_ints
1473 SET record_status = 'D',
1474 error_code = NULL
1475 WHERE rowid = new_schcntct_rec.rowid;
1476
1477 g_success_rec_cnt := g_success_rec_cnt + 1;
1478 END IF;
1479
1480 END LOOP;
1481
1482 COMMIT;
1483 -- log processing complete for this view
1484 igs_uc_proc_ucas_data.log_proc_complete('CVSCHOOLCONTACT', g_success_rec_cnt, g_error_rec_cnt);
1485
1486 EXCEPTION
1487 -- Process should continue with processing of other view data
1488 WHEN OTHERS THEN
1489 ROLLBACK;
1490 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1491 fnd_message.set_token('VIEW', 'CVSCHOOLCONTACT'||' - '||SQLERRM);
1492 fnd_file.put_line(fnd_file.log, fnd_message.get);
1493 END process_cvschoolcontact ;
1494
1495
1496
1497 PROCEDURE process_cvcourse IS
1498 /******************************************************************
1499 Created By : rgangara
1500 Date Created By : 12-JUNE-2003
1501 Purpose : For processing Course details info. from UCAS
1502 Known limitations,enhancements,remarks:
1503 Change History
1504 Who When What
1505 jbaber 11-Jul-06 Current and deferred validity default to R
1506 for UCAS 2007 Support
1507 ******************************************************************/
1508
1509 -- Get new interface records
1510 CURSOR int_cvcrse_cur IS
1511 SELECT csci.rowid,
1512 csci.*
1513 FROM igs_uc_ccrse_ints csci
1514 WHERE csci.record_status = 'N';
1515
1516 -- check whether corresponding record already exists.
1517 -- Currently since only FTUG is supported over Hercules and no course data for other systems come from UCAS,
1518 -- the system code has been hardcoded to U for 'FTUG'.
1519 CURSOR old_crse_cur (p_course igs_uc_ccrse_ints.course%TYPE, p_campus igs_uc_ccrse_ints.campus%TYPE,
1520 p_inst igs_uc_ccrse_ints.inst%TYPE, p_system_code igs_uc_ccrse_ints.system_code%TYPE) IS
1521 SELECT crdet.rowid,
1522 crdet.*
1523 FROM igs_uc_crse_dets crdet
1524 WHERE crdet.ucas_program_code = p_course
1525 AND crdet.institute = p_inst
1526 AND crdet.ucas_campus = p_campus
1527 AND crdet.system_code = p_system_code;
1528
1529 -- validate inst
1530 CURSOR chk_institute (p_inst igs_uc_ccrse_ints.inst%TYPE) IS
1531 SELECT 'X'
1532 FROM igs_uc_com_inst
1533 WHERE inst = p_inst;
1534
1535 -- To get the Course records that require updation to set CLEARING_OPTIONS column value to 'Y'
1536 -- Those records which are present in uvcoursevacoptions)
1537 CURSOR crse_vacops_cur IS
1538 SELECT a.ROWID row_id,
1539 a.*
1540 FROM igs_uc_crse_dets a, igs_uc_ucrsvop_ints b
1541 WHERE a.ucas_program_code = b.course
1542 AND a.ucas_campus = b.campus
1543 AND a.institute = g_crnt_institute
1544 AND a.system_code = 'U'
1545 AND b.record_status = 'N';
1546
1547 old_crse_rec old_crse_cur%ROWTYPE;
1548 l_check_flag VARCHAR2(1);
1549
1550 BEGIN
1551 -- initialize variables
1552 g_success_rec_cnt := 0;
1553 g_error_rec_cnt := 0;
1554
1555 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1556 fnd_message.set_token('VIEW', 'CVCOURSE ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1557 fnd_file.put_line(fnd_file.log, fnd_message.get);
1558
1559 -- Get all the reocords from interface table with status = 'N'
1560 FOR new_cvcrse_rec IN int_cvcrse_cur LOOP
1561
1562 BEGIN
1563 -- record level initialization
1564 g_error_code := NULL;
1565 old_crse_rec := NULL;
1566
1567
1568 -- log record level processing message
1569 fnd_message.set_name('IGS','IGS_UC_INST_CRS_CAMP_PROC');
1570 fnd_message.set_token('INST', new_cvcrse_rec.inst);
1571 fnd_message.set_token('COURSE', new_cvcrse_rec.course);
1572 fnd_message.set_token('CAMPUS', new_cvcrse_rec.campus);
1573 fnd_file.put_line(fnd_file.log, fnd_message.get);
1574
1575
1576 -- validate mandatory fields have values.
1577 IF new_cvcrse_rec.course IS NULL OR new_cvcrse_rec.campus IS NULL OR new_cvcrse_rec.inst IS NULL THEN
1578 g_error_code := '1037';
1579 END IF;
1580
1581 -- validate institute value
1582 IF g_error_code IS NULL THEN
1583
1584 l_check_flag := NULL;
1585 OPEN chk_institute (new_cvcrse_rec.inst);
1586 FETCH chk_institute INTO l_check_flag;
1587
1588 IF chk_institute%NOTFOUND THEN
1589 g_error_code := '1006';
1590 END IF;
1591
1592 CLOSE chk_institute;
1593 END IF;
1594
1595
1596 -- main processing begins
1597 IF g_error_code IS NULL THEN
1598
1599 -- check whether corresponding rec already exists
1600 OPEN old_crse_cur(new_cvcrse_rec.course, new_cvcrse_rec.campus, new_cvcrse_rec.inst, new_cvcrse_rec.system_code);
1601 FETCH old_crse_cur INTO old_crse_rec;
1602 CLOSE old_crse_cur;
1603
1604 -- If not found then insert
1605 IF old_crse_rec.rowid IS NULL THEN
1606
1607 BEGIN
1608 -- insert a new record in the main table
1609 igs_uc_crse_dets_pkg.insert_row -- IGSXI14B.pls
1610 (
1611 x_rowid => old_crse_rec.rowid
1612 ,x_ucas_program_code => new_cvcrse_rec.course
1613 ,x_oss_program_code => NULL
1614 ,x_oss_program_version => NULL
1615 ,x_institute => new_cvcrse_rec.inst
1616 ,x_uvcourse_updater => '5'
1617 ,x_uvcrsevac_updater => '5'
1618 ,x_short_title => new_cvcrse_rec.shortname
1619 ,x_long_title => new_cvcrse_rec.longname
1620 ,x_ucas_campus => new_cvcrse_rec.campus
1621 ,x_oss_location => NULL
1622 ,x_faculty => new_cvcrse_rec.faculty
1623 ,x_total_no_of_seats => NULL
1624 ,x_min_entry_points => NULL
1625 ,x_max_entry_points => NULL
1626 ,x_current_validity => 'R'
1627 ,x_deferred_validity => 'R'
1628 ,x_term_1_start => NULL
1629 ,x_term_1_end => NULL
1630 ,x_term_2_start => NULL
1631 ,x_term_2_end => NULL
1632 ,x_term_3_start => NULL
1633 ,x_term_3_end => NULL
1634 ,x_term_4_start => NULL
1635 ,x_term_4_end => NULL
1636 ,x_cl_updated => NULL
1637 ,x_cl_date => NULL
1638 ,x_vacancy_status => NULL
1639 ,x_no_of_vacancy => NULL
1640 ,x_score => NULL
1641 ,x_rb_full => NULL
1642 ,x_scot_vac => NULL
1643 ,x_sent_to_ucas => 'Y'
1644 ,x_mode => 'R'
1645 ,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
1646 ,x_oss_attendance_type => NULL
1647 ,x_oss_attendance_mode => NULL
1648 ,x_joint_admission_ind => new_cvcrse_rec.jointadmission
1649 ,x_open_extra_ind => new_cvcrse_rec.openextra
1650 ,x_system_code => new_cvcrse_rec.system_code
1651 ,x_clearing_options => 'N'
1652 ,x_imported => 'Y'
1653 );
1654
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 g_error_code := '9999';
1658 fnd_file.put_line(fnd_file.log, SQLERRM);
1659 END;
1660
1661 ELSE -- update
1662
1663 BEGIN
1664 -- update a new record in the main table
1665 igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
1666 (
1667 x_rowid => old_crse_rec.rowid
1668 ,x_ucas_program_code => old_crse_rec.ucas_program_code
1669 ,x_oss_program_code => old_crse_rec.oss_program_code
1670 ,x_oss_program_version => old_crse_rec.oss_program_version
1671 ,x_institute => old_crse_rec.institute
1672 ,x_uvcourse_updater => old_crse_rec.uvcourse_updater
1673 ,x_uvcrsevac_updater => old_crse_rec.uvcrsevac_updater
1674 ,x_short_title => new_cvcrse_rec.shortname
1675 ,x_long_title => new_cvcrse_rec.longname
1676 ,x_ucas_campus => old_crse_rec.ucas_campus
1677 ,x_oss_location => old_crse_rec.oss_location
1678 ,x_faculty => new_cvcrse_rec.faculty
1679 ,x_total_no_of_seats => old_crse_rec.total_no_of_seats
1680 ,x_min_entry_points => old_crse_rec.min_entry_points
1681 ,x_max_entry_points => old_crse_rec.max_entry_points
1682 ,x_current_validity => old_crse_rec.current_validity
1683 ,x_deferred_validity => old_crse_rec.deferred_validity
1684 ,x_term_1_start => old_crse_rec.term_1_start
1685 ,x_term_1_end => old_crse_rec.term_1_end
1686 ,x_term_2_start => old_crse_rec.term_2_start
1687 ,x_term_2_end => old_crse_rec.term_2_end
1688 ,x_term_3_start => old_crse_rec.term_3_start
1689 ,x_term_3_end => old_crse_rec.term_3_end
1690 ,x_term_4_start => old_crse_rec.term_4_start
1691 ,x_term_4_end => old_crse_rec.term_4_end
1692 ,x_cl_updated => old_crse_rec.cl_updated
1693 ,x_cl_date => old_crse_rec.cl_date
1694 ,x_vacancy_status => old_crse_rec.vacancy_status
1695 ,x_no_of_vacancy => old_crse_rec.no_of_vacancy
1696 ,x_score => old_crse_rec.score
1697 ,x_rb_full => old_crse_rec.rb_full
1698 ,x_scot_vac => old_crse_rec.scot_vac
1699 ,x_sent_to_ucas => old_crse_rec.sent_to_ucas
1700 ,x_mode => 'R'
1701 ,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
1702 ,x_oss_attendance_type => old_crse_rec.oss_attendance_type
1703 ,x_oss_attendance_mode => old_crse_rec.oss_attendance_mode
1704 ,x_joint_admission_ind => new_cvcrse_rec.jointadmission
1705 ,x_open_extra_ind => new_cvcrse_rec.openextra
1706 ,x_system_code => old_crse_rec.system_code
1707 ,x_clearing_options => old_crse_rec.clearing_options
1708 ,x_imported => 'Y'
1709 );
1710
1711 EXCEPTION
1712 WHEN OTHERS THEN
1713 g_error_code := '9998';
1714 fnd_file.put_line(fnd_file.log, SQLERRM);
1715 END;
1716
1717 END IF; -- insert/update
1718
1719 END IF; -- main processing
1720
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 -- catch any unhandled/unexpected errors while processing a record.
1724 -- This would enable processing to continue with subsequent records.
1725
1726 -- Close any Open cursors
1727 IF old_crse_cur%ISOPEN THEN
1728 CLOSE old_crse_cur;
1729 END IF;
1730
1731
1732 IF chk_institute%ISOPEN THEN
1733 CLOSE chk_institute;
1734 END IF;
1735
1736
1737 IF crse_vacops_cur%ISOPEN THEN
1738 CLOSE crse_vacops_cur;
1739 END IF;
1740
1741 g_error_code := '1055';
1742 fnd_file.put_line(fnd_file.log, SQLERRM);
1743 END;
1744
1745
1746 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
1747 -- while processing the record.
1748 IF g_error_code IS NOT NULL THEN
1749
1750 UPDATE igs_uc_ccrse_ints
1751 SET error_code = g_error_code
1752 WHERE rowid = new_cvcrse_rec.rowid;
1753
1754 -- log error message/meaning.
1755 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
1756
1757 -- update error count
1758 g_error_rec_cnt := g_error_rec_cnt + 1;
1759
1760 ELSE
1761
1762 UPDATE igs_uc_ccrse_ints
1763 SET record_status = 'D',
1764 error_code = NULL
1765 WHERE rowid = new_cvcrse_rec.rowid;
1766
1767 g_success_rec_cnt := g_success_rec_cnt + 1;
1768 END IF;
1769
1770 END LOOP;
1771
1772
1773 -- for updating of Course vacancy information
1774 -- Update Course records to set CLEARING_OPTIONS column value to 'Y' if the corresponding
1775 -- course vacancies options are available in uvcoursevacoptions table i.e. igs_uc_ucrsvop_ints
1776 FOR crse_vacops_rec IN crse_vacops_cur
1777 LOOP
1778
1779 BEGIN
1780 igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
1781 (
1782 x_rowid => crse_vacops_rec.row_id
1783 ,x_ucas_program_code => crse_vacops_rec.ucas_program_code
1784 ,x_oss_program_code => crse_vacops_rec.oss_program_code
1785 ,x_oss_program_version => crse_vacops_rec.oss_program_version
1786 ,x_institute => crse_vacops_rec.institute
1787 ,x_uvcourse_updater => crse_vacops_rec.uvcourse_updater
1788 ,x_uvcrsevac_updater => crse_vacops_rec.uvcrsevac_updater
1789 ,x_short_title => crse_vacops_rec.short_title
1790 ,x_long_title => crse_vacops_rec.long_title
1791 ,x_ucas_campus => crse_vacops_rec.ucas_campus
1792 ,x_oss_location => crse_vacops_rec.oss_location
1793 ,x_faculty => crse_vacops_rec.faculty
1794 ,x_total_no_of_seats => crse_vacops_rec.total_no_of_seats
1795 ,x_min_entry_points => crse_vacops_rec.min_entry_points
1796 ,x_max_entry_points => crse_vacops_rec.max_entry_points
1797 ,x_current_validity => crse_vacops_rec.current_validity
1798 ,x_deferred_validity => crse_vacops_rec.deferred_validity
1799 ,x_term_1_start => crse_vacops_rec.term_1_start
1800 ,x_term_1_end => crse_vacops_rec.term_1_end
1801 ,x_term_2_start => crse_vacops_rec.term_2_start
1802 ,x_term_2_end => crse_vacops_rec.term_2_end
1803 ,x_term_3_start => crse_vacops_rec.term_3_start
1804 ,x_term_3_end => crse_vacops_rec.term_3_end
1805 ,x_term_4_start => crse_vacops_rec.term_4_start
1806 ,x_term_4_end => crse_vacops_rec.term_4_end
1807 ,x_cl_updated => crse_vacops_rec.cl_updated
1808 ,x_cl_date => crse_vacops_rec.cl_date
1809 ,x_vacancy_status => crse_vacops_rec.vacancy_status
1810 ,x_no_of_vacancy => crse_vacops_rec.no_of_vacancy
1811 ,x_score => crse_vacops_rec.score
1812 ,x_rb_full => crse_vacops_rec.rb_full
1813 ,x_scot_vac => crse_vacops_rec.scot_vac
1814 ,x_sent_to_ucas => crse_vacops_rec.sent_to_ucas
1815 ,x_mode => 'R'
1816 ,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
1817 ,x_oss_attendance_type => crse_vacops_rec.oss_attendance_type
1818 ,x_oss_attendance_mode => crse_vacops_rec.oss_attendance_mode
1819 ,x_joint_admission_ind => crse_vacops_rec.joint_admission_ind
1820 ,x_open_extra_ind => crse_vacops_rec.open_extra_ind
1821 ,x_system_code => crse_vacops_rec.system_code
1822 ,x_clearing_options => 'Y'
1823 ,x_imported => crse_vacops_rec.imported
1824 );
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 -- catch any unhandled/unexpected errors while processing a record.
1829 -- This would enable processing to continue with subsequent records.
1830 g_error_code := '1055';
1831 fnd_file.put_line(fnd_file.log, SQLERRM);
1832 END;
1833
1834 END LOOP;
1835 -- end of processing for course vacancy options flag updation.
1836
1837
1838 COMMIT;
1839 -- log processing complete for this view
1840 igs_uc_proc_ucas_data.log_proc_complete('CVCOURSE', g_success_rec_cnt, g_error_rec_cnt);
1841
1842 EXCEPTION
1843 -- Process should continue with processing of other view data
1844 WHEN OTHERS THEN
1845 ROLLBACK;
1846 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
1847 fnd_message.set_token('VIEW', 'CVCOURSE'||' - '||SQLERRM);
1848 fnd_file.put_line(fnd_file.log, fnd_message.get);
1849 END process_cvcourse ;
1850
1851
1852
1853 PROCEDURE process_uvcourse IS
1854 /******************************************************************
1855 Created By : rgangara
1856 Date Created By : 12-JUNE-2003
1857 Purpose : For processing Updateable Course details info. from UCAS
1858 Known limitations,enhancements,remarks:
1859 Change History
1860 Who When What
1861 ******************************************************************/
1862
1863 -- Get new interface records
1864 CURSOR int_uvcrse_cur IS
1865 SELECT usci.rowid,
1866 usci.*
1867 FROM igs_uc_ucrse_ints usci
1868 WHERE usci.record_status = 'N';
1869
1870 -- check whether corresponding record already exists.
1871 -- Currently since only FTUG is supported over Hercules and no course data for other systems come from UCAS,
1872 -- the system code has been hardcoded to U for 'FTUG'.
1873 CURSOR old_ucrse_cur (p_course igs_uc_ccrse_ints.course%TYPE, p_campus igs_uc_ccrse_ints.campus%TYPE,
1874 p_inst igs_uc_ccrse_ints.inst%TYPE, p_system igs_uc_crse_dets.system_code%TYPE) IS
1875 SELECT ucrdet.rowid,
1876 ucrdet.*
1877 FROM igs_uc_crse_dets ucrdet
1878 WHERE ucrdet.ucas_program_code = p_course
1879 AND ucrdet.institute = p_inst
1880 AND ucrdet.ucas_campus = p_campus
1881 AND ucrdet.system_code = p_system;
1882
1883 old_ucrse_rec old_ucrse_cur%ROWTYPE;
1884 l_check_flag VARCHAR2(1);
1885
1886 BEGIN
1887 -- initialize variables
1888 g_success_rec_cnt := 0;
1889 g_error_rec_cnt := 0;
1890
1891 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
1892 fnd_message.set_token('VIEW', 'UVCOURSE ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1893 fnd_file.put_line(fnd_file.log, fnd_message.get);
1894
1895 -- Get all the reocords from interface table with status = 'N'
1896 FOR new_uvcrse_rec IN int_uvcrse_cur LOOP
1897
1898 BEGIN
1899 -- record level initialization
1900 g_error_code := NULL;
1901 old_ucrse_rec := NULL;
1902
1903
1904 -- log record level processing - Course and campus
1905 fnd_message.set_name('IGS','IGS_UC_CRS_CAMP_PROC');
1906 fnd_message.set_token('COURSE', new_uvcrse_rec.course);
1907 fnd_message.set_token('CAMPUS', new_uvcrse_rec.campus);
1908 fnd_file.put_line(fnd_file.log, fnd_message.get);
1909
1910 -- validate mandatory fields have values.
1911 IF new_uvcrse_rec.course IS NULL OR new_uvcrse_rec.campus IS NULL THEN
1912 g_error_code := '1037';
1913 END IF;
1914
1915
1916 -- main processing begins
1917 IF g_error_code IS NULL THEN
1918
1919 -- check whether corresponding rec already exists
1920 OPEN old_ucrse_cur(new_uvcrse_rec.course, new_uvcrse_rec.campus, g_crnt_institute, 'U');
1921 FETCH old_ucrse_cur INTO old_ucrse_rec;
1922 CLOSE old_ucrse_cur;
1923
1924 -- If not found then insert
1925 IF old_ucrse_rec.rowid IS NULL THEN
1926 -- report error if corresponding record does not already exist.
1927 g_error_code := '1033';
1928
1929 ELSE -- update
1930
1931 BEGIN
1932 -- update a new record in the main table
1933 igs_uc_crse_dets_pkg.update_row -- IGSXI14B.pls
1934 (
1935 x_rowid => old_ucrse_rec.rowid
1936 ,x_ucas_program_code => old_ucrse_rec.ucas_program_code
1937 ,x_oss_program_code => old_ucrse_rec.oss_program_code
1938 ,x_oss_program_version => old_ucrse_rec.oss_program_version
1939 ,x_institute => old_ucrse_rec.institute
1940 ,x_uvcourse_updater => NVL(new_uvcrse_rec.updater,'5')
1941 ,x_uvcrsevac_updater => old_ucrse_rec.uvcrsevac_updater
1942 ,x_short_title => new_uvcrse_rec.shorttitle
1943 ,x_long_title => new_uvcrse_rec.longtitle
1944 ,x_ucas_campus => old_ucrse_rec.ucas_campus
1945 ,x_oss_location => old_ucrse_rec.oss_location
1946 ,x_faculty => new_uvcrse_rec.faculty
1947 ,x_total_no_of_seats => old_ucrse_rec.total_no_of_seats
1948 ,x_min_entry_points => old_ucrse_rec.min_entry_points
1949 ,x_max_entry_points => old_ucrse_rec.max_entry_points
1950 ,x_current_validity => old_ucrse_rec.current_validity
1951 ,x_deferred_validity => old_ucrse_rec.deferred_validity
1952 ,x_term_1_start => new_uvcrse_rec.term1start
1953 ,x_term_1_end => new_uvcrse_rec.term1end
1954 ,x_term_2_start => new_uvcrse_rec.term2start
1955 ,x_term_2_end => new_uvcrse_rec.term2end
1956 ,x_term_3_start => new_uvcrse_rec.term3start
1957 ,x_term_3_end => new_uvcrse_rec.term3end
1958 ,x_term_4_start => new_uvcrse_rec.term4start
1959 ,x_term_4_end => new_uvcrse_rec.term4end
1960 ,x_cl_updated => old_ucrse_rec.cl_updated
1961 ,x_cl_date => old_ucrse_rec.cl_date
1962 ,x_vacancy_status => old_ucrse_rec.vacancy_status
1963 ,x_no_of_vacancy => old_ucrse_rec.no_of_vacancy
1964 ,x_score => old_ucrse_rec.score
1965 ,x_rb_full => old_ucrse_rec.rb_full
1966 ,x_scot_vac => old_ucrse_rec.scot_vac
1967 ,x_sent_to_ucas => old_ucrse_rec.sent_to_ucas
1968 ,x_mode => 'R'
1969 ,x_ucas_system_id => NULL -- passed as NULL as System_code is being used for identifying the System
1970 ,x_oss_attendance_type => old_ucrse_rec.oss_attendance_type
1971 ,x_oss_attendance_mode => old_ucrse_rec.oss_attendance_mode
1972 ,x_joint_admission_ind => new_uvcrse_rec.jointadmission
1973 ,x_open_extra_ind => new_uvcrse_rec.openextra
1974 ,x_system_code => old_ucrse_rec.system_code
1975 ,x_clearing_options => old_ucrse_rec.clearing_options
1976 ,x_imported => 'Y'
1977 );
1978
1979
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 g_error_code := '9998';
1983 fnd_file.put_line(fnd_file.log, SQLERRM);
1984 END;
1985
1986 END IF; -- insert/update
1987
1988 END IF; -- main processing
1989
1990
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 -- catch any unhandled/unexpected errors while processing a record.
1994 -- This would enable processing to continue with subsequent records.
1995
1996 -- Close any Open cursors
1997 IF old_ucrse_cur%ISOPEN THEN
1998 CLOSE old_ucrse_cur;
1999 END IF;
2000
2001 g_error_code := '1055';
2002 fnd_file.put_line(fnd_file.log, SQLERRM);
2003 END;
2004
2005 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
2006 -- while processing the record.
2007 IF g_error_code IS NOT NULL THEN
2008
2009 UPDATE igs_uc_ucrse_ints
2010 SET error_code = g_error_code
2011 WHERE rowid = new_uvcrse_rec.rowid;
2012
2013 -- log error message/meaning.
2014 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
2015
2016 -- update error count
2017 g_error_rec_cnt := g_error_rec_cnt + 1;
2018
2019 ELSE
2020
2021 UPDATE igs_uc_ucrse_ints
2022 SET record_status = 'D',
2023 error_code = NULL
2024 WHERE rowid = new_uvcrse_rec.rowid;
2025
2026 g_success_rec_cnt := g_success_rec_cnt + 1;
2027 END IF;
2028
2029 END LOOP;
2030
2031
2032 COMMIT;
2033 -- log processing complete for this view
2034 igs_uc_proc_ucas_data.log_proc_complete('UVCOURSE', g_success_rec_cnt, g_error_rec_cnt);
2035
2036 EXCEPTION
2037 -- Process should continue with processing of other view data
2038 WHEN OTHERS THEN
2039 ROLLBACK;
2040 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
2041 fnd_message.set_token('VIEW', 'UVCOURSE'||' - '||SQLERRM);
2042 fnd_file.put_line(fnd_file.log, fnd_message.get);
2043 END process_uvcourse ;
2044
2045
2046 PROCEDURE process_uvcoursekeyword IS
2047 /******************************************************************
2048 Created By : rgangara
2049 Date Created By : 12-JUNE-2003
2050 Purpose : For processing Updateable Course Vacancy option details info. from UCAS
2051 Known limitations,enhancements,remarks:
2052 Change History
2053 Who When What
2054 rgangara 16-ARP-04 Modified keyword processing to delete existing keyword records for the
2055 combination (Course,campus, optioncode) and insert afresh the entire
2056 set. If any records fails in the set, none of the records should get
2057 processed. All keyword records with error would as usual get populated
2058 with Error Code. For records which are successful but could not be processed
2059 as the set has some invalid records would be populated with 2002 error code.
2060 This is done as part of bug# 3496874.
2061 jbaber 15-SEP-05 Removed keyno from cursor for bug 4589994
2062 ******************************************************************/
2063
2064 -- Get distinct Course options for new interface records
2065 CURSOR int_crseops_cur IS
2066 SELECT DISTINCT int.course,
2067 int.campus,
2068 int.optioncode
2069 FROM igs_uc_ucrskwd_ints int
2070 WHERE int.record_status = 'N';
2071
2072 -- Cursor to return rows for a course,campus,optioncode combination
2073 -- which are to be deleted before inserting fresh records.
2074 CURSOR old_crskwd_del_cur (p_course igs_uc_ucrskwd_ints.course%TYPE,
2075 p_campus igs_uc_ucrskwd_ints.campus%TYPE,
2076 p_opt_code igs_uc_ucrskwd_ints.optioncode%TYPE,
2077 p_system igs_uc_crse_keywrds.system_code%TYPE ) IS
2078 SELECT ucrvop.rowid, ucrvop.keyword
2079 FROM igs_uc_crse_keywrds ucrvop
2080 WHERE ucrvop.ucas_program_code = p_course
2081 AND ucrvop.institute = g_crnt_institute
2082 AND ucrvop.ucas_campus = p_campus
2083 AND ucrvop.option_code = p_opt_code
2084 AND ucrvop.system_code = p_system;
2085
2086
2087 -- Get new interface records for the course, campus, optioncode
2088 CURSOR int_ucrsekwd_cur (cp_course igs_uc_ucrskwd_ints.course%TYPE,
2089 cp_campus igs_uc_ucrskwd_ints.campus%TYPE,
2090 cp_optioncd igs_uc_ucrskwd_ints.optioncode%TYPE) IS
2091 SELECT ucvi.rowid,
2092 ucvi.*
2093 FROM igs_uc_ucrskwd_ints ucvi
2094 WHERE ucvi.record_status = 'N'
2095 AND ucvi.course = cp_course
2096 AND ucvi.campus = cp_campus
2097 AND ucvi.optioncode = cp_optioncd;
2098
2099 -- check whether corresponding record already exists.
2100 -- Currently since only FTUG is supported over Hercules and no course data for other systems come from UCAS,
2101 -- the system code has been hardcoded to U for 'FTUG'.
2102 CURSOR old_crskwd_cur (p_course igs_uc_ucrskwd_ints.course%TYPE,
2103 p_campus igs_uc_ucrskwd_ints.campus%TYPE,
2104 p_opt_code igs_uc_ucrskwd_ints.optioncode%TYPE,
2105 p_keyword igs_uc_ucrskwd_ints.keyword%TYPE,
2106 p_system igs_uc_crse_keywrds.system_code%TYPE ) IS
2107 SELECT ucrvop.rowid,
2108 ucrvop.*
2109 FROM igs_uc_crse_keywrds ucrvop
2110 WHERE ucrvop.ucas_program_code = p_course
2111 AND ucrvop.institute = g_crnt_institute
2112 AND ucrvop.ucas_campus = p_campus
2113 AND ucrvop.option_code = p_opt_code
2114 AND ucrvop.keyword = p_keyword
2115 AND ucrvop.system_code = p_system;
2116
2117 -- validate the the UCAS program details are valid i.e exist in Course details table.
2118 CURSOR validate_crse_cur (p_course igs_uc_ucrsvac_ints.course%TYPE, p_campus igs_uc_ucrsvac_ints.campus%TYPE,
2119 p_system igs_uc_crse_dets.system_code%TYPE) IS
2120 SELECT ucrdet.rowid
2121 FROM igs_uc_crse_dets ucrdet
2122 WHERE ucrdet.ucas_program_code = p_course
2123 AND ucrdet.institute = g_crnt_institute
2124 AND ucrdet.ucas_campus = p_campus
2125 AND ucrdet.system_code = p_system;
2126
2127
2128 -- validate keyword value exists in IGS_UC_REF_KEYWORDS
2129 CURSOR validate_keyword_cur (p_keyword igs_uc_ucrskwd_ints.keyword%TYPE) IS
2130 SELECT rowid
2131 FROM igs_uc_ref_keywords
2132 WHERE keyword = p_keyword;
2133
2134
2135 old_crsekwd_rec old_crskwd_cur%ROWTYPE;
2136 l_rowid VARCHAR2(26);
2137 l_crse_keyword_id igs_uc_crse_keywrds.crse_keyword_id%TYPE;
2138 l_set_level_success VARCHAR2(1);
2139
2140 BEGIN
2141 -- initialize variables
2142 g_success_rec_cnt := 0;
2143 g_error_rec_cnt := 0;
2144 l_set_level_success := 'Y';
2145
2146 fnd_message.set_name('IGS','IGS_UC_PROC_VIEW_DATA');
2147 fnd_message.set_token('VIEW', 'UVCOURSEKEYWORD ON '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2148 fnd_file.put_line(fnd_file.log, fnd_message.get);
2149
2150 -- Setting Error code to NULL for 'N' status records.
2151 -- This is done to support the logic for processing as a combined set whereby the entire set is
2152 -- marked as error if atleast one rec in the set is invalid.
2153 UPDATE igs_uc_ucrskwd_ints
2154 SET error_code = NULL
2155 WHERE record_status = 'N';
2156
2157
2158 FOR int_crseops_rec IN int_crseops_cur LOOP
2159
2160 l_set_level_success := 'Y'; -- initializing to Yes for each set
2161 -- log record level processing message
2162 fnd_message.set_name('IGS','IGS_UC_CRS_CAMP_OPT_PROC');
2163 fnd_message.set_token('COURSE', int_crseops_rec.course);
2164 fnd_message.set_token('CAMPUS', int_crseops_rec.campus);
2165 fnd_message.set_token('OPTION', int_crseops_rec.optioncode);
2166 fnd_file.put_line(fnd_file.log, fnd_message.get);
2167
2168 -- Check whether any correspoinding records exist in UCAS table.
2169 -- IF exists delete old keyword records for the combination.
2170 FOR old_crskwd_del_rec IN old_crskwd_del_cur(int_crseops_rec.course,
2171 int_crseops_rec.campus,
2172 int_crseops_rec.optioncode,
2173 'U')
2174 LOOP
2175 igs_uc_crse_keywrds_pkg.delete_row (old_crskwd_del_rec.rowid);
2176 END LOOP;
2177
2178 -- Get all the reocords for the combination from interface table with status = 'N'
2179 FOR new_ucrsekwd_rec IN int_ucrsekwd_cur(int_crseops_rec.course,
2180 int_crseops_rec.campus,
2181 int_crseops_rec.optioncode)
2182 LOOP
2183
2184 BEGIN
2185 -- record level initialization
2186 g_error_code := NULL;
2187 old_crsekwd_rec := NULL;
2188 l_crse_keyword_id := NULL;
2189
2190 -- log record level processing message
2191 fnd_message.set_name('IGS','IGS_UC_CRSE_KEYWORD_PROC');
2192 fnd_message.set_token('KEYWORD', new_ucrsekwd_rec.keyword);
2193 fnd_file.put_line(fnd_file.log, ' ' || fnd_message.get);
2194
2195 -- validate mandatory fields have values.
2196 IF new_ucrsekwd_rec.course IS NULL OR new_ucrsekwd_rec.campus IS NULL OR new_ucrsekwd_rec.optioncode IS NULL THEN
2197 g_error_code := '1037';
2198 END IF;
2199
2200
2201 -- course validation
2202 IF g_error_code IS NULL THEN
2203
2204 l_rowid := NULL;
2205 OPEN validate_crse_cur (new_ucrsekwd_rec.course, new_ucrsekwd_rec.campus, 'U');
2206 FETCH validate_crse_cur INTO l_rowid;
2207 CLOSE validate_crse_cur;
2208
2209 IF l_rowid IS NULL THEN
2210 g_error_code := '1035';
2211 END IF;
2212
2213 END IF;
2214
2215
2216 -- Keyword validation
2217 IF g_error_code IS NULL THEN
2218
2219 l_rowid := NULL;
2220 OPEN validate_keyword_cur (new_ucrsekwd_rec.keyword);
2221 FETCH validate_keyword_cur INTO l_rowid;
2222 CLOSE validate_keyword_cur;
2223
2224 IF l_rowid IS NULL THEN
2225 g_error_code := '1036';
2226 END IF;
2227
2228 END IF;
2229
2230
2231 -- main processing begins
2232 IF g_error_code IS NULL THEN
2233 -- check whether corresponding rec already exists
2234 OPEN old_crskwd_cur(new_ucrsekwd_rec.course, new_ucrsekwd_rec.campus, new_ucrsekwd_rec.optioncode, new_ucrsekwd_rec.keyword, 'U');
2235 FETCH old_crskwd_cur INTO old_crsekwd_rec;
2236 CLOSE old_crskwd_cur;
2237
2238 -- If not found then insert
2239 IF old_crsekwd_rec.rowid IS NULL THEN
2240
2241 BEGIN
2242 -- insert a new record - call the TBH
2243 igs_uc_crse_keywrds_pkg.insert_row -- IGSXI15B.pls
2244 (
2245 x_rowid => old_crsekwd_rec.rowid
2246 ,x_ucas_program_code => new_ucrsekwd_rec.course
2247 ,x_institute => g_crnt_institute
2248 ,x_ucas_campus => new_ucrsekwd_rec.campus
2249 ,x_option_code => new_ucrsekwd_rec.optioncode
2250 ,x_preference => new_ucrsekwd_rec.keyno
2251 ,x_keyword => new_ucrsekwd_rec.keyword
2252 ,x_updater => NVL(new_ucrsekwd_rec.updater,'5')
2253 ,x_active => NVL(new_ucrsekwd_rec.active,'Y')
2254 ,x_deleted => 'N'
2255 ,x_sent_to_ucas => 'Y'
2256 ,x_mode => 'R'
2257 ,x_system_code => 'U'
2258 ,x_crse_keyword_id => l_crse_keyword_id
2259 );
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 g_error_code := '9999';
2264 fnd_file.put_line(fnd_file.log, SQLERRM);
2265 END;
2266
2267
2268 ELSE -- update
2269
2270 BEGIN
2271 -- update a new record in the main table
2272 igs_uc_crse_keywrds_pkg.update_row -- IGSXI15B.pls
2273 (
2274 x_rowid => old_crsekwd_rec.rowid
2275 ,x_ucas_program_code => old_crsekwd_rec.ucas_program_code
2276 ,x_institute => old_crsekwd_rec.institute
2277 ,x_ucas_campus => old_crsekwd_rec.ucas_campus
2278 ,x_option_code => old_crsekwd_rec.option_code
2279 ,x_preference => old_crsekwd_rec.preference
2280 ,x_keyword => new_ucrsekwd_rec.keyword
2281 ,x_updater => NVL(new_ucrsekwd_rec.updater,'5')
2282 ,x_active => NVL(new_ucrsekwd_rec.active,'Y')
2283 ,x_deleted => old_crsekwd_rec.deleted
2284 ,x_sent_to_ucas => 'Y'
2285 ,x_mode => 'R'
2286 ,x_system_code => old_crsekwd_rec.system_code
2287 ,x_crse_keyword_id => old_crsekwd_rec.crse_keyword_id
2288 );
2289
2290 EXCEPTION
2291 WHEN OTHERS THEN
2292 g_error_code := '9998';
2293 fnd_file.put_line(fnd_file.log, SQLERRM);
2294 END;
2295
2296 END IF; -- insert/update
2297
2298 END IF; -- main processing
2299
2300
2301 EXCEPTION
2302 WHEN OTHERS THEN
2303 -- catch any unhandled/unexpected errors while processing a record.
2304 -- This would enable processing to continue with subsequent records.
2305
2306 -- Close any Open cursors
2307 IF old_crskwd_cur%ISOPEN THEN
2308 CLOSE old_crskwd_cur;
2309 END IF;
2310
2311
2312 IF validate_crse_cur%ISOPEN THEN
2313 CLOSE validate_crse_cur;
2314 END IF;
2315
2316 IF validate_keyword_cur%ISOPEN THEN
2317 CLOSE validate_keyword_cur;
2318 END IF;
2319
2320 g_error_code := '1055';
2321 fnd_file.put_line(fnd_file.log, SQLERRM);
2322 END;
2323
2324 -- update the interface table rec - record_status if successfully processed or Error Code if any error encountered
2325 -- while processing the record.
2326 IF g_error_code IS NOT NULL THEN
2327
2328 -- set the flag to No if atleast one rec fails in a set.
2329 l_set_level_success := 'N';
2330
2331 UPDATE igs_uc_ucrskwd_ints
2332 SET error_code = g_error_code
2333 WHERE rowid = new_ucrsekwd_rec.rowid;
2334
2335 -- log error message/meaning.
2336 igs_uc_proc_ucas_data.log_error_msg(g_error_code);
2337
2338 -- update error count
2339 g_error_rec_cnt := g_error_rec_cnt + 1;
2340
2341 ELSE
2342 -- No updating of record status to D as they will be done at the SET level below
2343 g_success_rec_cnt := g_success_rec_cnt + 1;
2344
2345 END IF;
2346
2347 END LOOP;
2348
2349 -- The following logic is to set Status and error codes in INT table for the
2350 -- combination based on whether all records were successfully processed or not.
2351 -- If atleast one record failed in the set the entire set is marked as Error.
2352 -- Errored records will have specific error codes. For records that are valid
2353 -- in the failed set will have error as 2002. Added as part of bug# 3496874
2354 IF l_set_level_success = 'N' THEN
2355 -- Delete all the successfully created records of the set as the entire set is not successful.
2356 FOR old_crskwd_del_rec IN old_crskwd_del_cur(int_crseops_rec.course,
2357 int_crseops_rec.campus,
2358 int_crseops_rec.optioncode,
2359 'U')
2360 LOOP
2361 igs_uc_crse_keywrds_pkg.delete_row (old_crskwd_del_rec.rowid);
2362 END LOOP;
2363
2364 -- update valid INTS records for this course and campus combination to 2002
2365 -- for records which were successful but cant be processed as a set.
2366 -- The error code is NULL condition is used to select only success records
2367 -- for the current set in which they will be NULL. Earlier records if any would
2368 -- have got deleted or Error Code reset to NULL in the beginning of the keyword processing.
2369 UPDATE igs_uc_ucrskwd_ints SET error_code = '2002'
2370 WHERE record_status = 'N'
2371 AND course = int_crseops_rec.course
2372 AND campus = int_crseops_rec.campus
2373 AND optioncode = int_crseops_rec.optioncode
2374 AND error_code IS NULL ;
2375
2376 -- reset the success count by that many as they were earlier taken to be successful
2377 g_success_rec_cnt := g_success_rec_cnt - SQL%ROWCOUNT;
2378 g_error_rec_cnt := g_error_rec_cnt + SQL%ROWCOUNT;
2379 ELSE
2380
2381 -- Indicates all records in the set are successful.
2382 -- update all INTS records for this set status 'D'.
2383 UPDATE igs_uc_ucrskwd_ints SET record_status = 'D' , error_code = NULL
2384 WHERE record_status = 'N'
2385 AND course = int_crseops_rec.course
2386 AND campus = int_crseops_rec.campus
2387 AND optioncode = int_crseops_rec.optioncode;
2388 END IF ;
2389
2390 END LOOP; -- main loop for the combination
2391
2392 COMMIT;
2393 -- log processing complete for this view
2394 igs_uc_proc_ucas_data.log_proc_complete('UVCOURSEKEYWORD', g_success_rec_cnt, g_error_rec_cnt);
2395
2396 EXCEPTION
2397 -- Process should continue with processing of other view data
2398 WHEN OTHERS THEN
2399 ROLLBACK;
2400 fnd_message.set_name('IGS','IGS_UC_ERROR_PROC_DATA');
2401 fnd_message.set_token('VIEW', 'UVCOURSEKEYWORD'||' - '||SQLERRM);
2402 fnd_file.put_line(fnd_file.log, fnd_message.get);
2403 END process_uvcoursekeyword ;
2404
2405 END igs_uc_proc_com_inst_data;
[Home] [Help]