[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_APP_CLEARING_PKG
Source
1 PACKAGE BODY igs_uc_app_clearing_pkg AS
2 /* $Header: IGSXI04B.pls 115.7 2003/06/11 10:28:02 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_app_clearing%ROWTYPE;
6 new_references igs_uc_app_clearing%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_clearing_app_id IN NUMBER ,
12 x_app_id IN NUMBER ,
13 x_enquiry_no IN NUMBER ,
14 x_app_no IN NUMBER ,
15 x_date_cef_sent IN DATE ,
16 x_cef_no IN NUMBER ,
17 x_central_clearing IN VARCHAR2,
18 x_institution IN VARCHAR2,
19 x_course IN VARCHAR2,
20 x_campus IN VARCHAR2,
21 x_entry_month IN NUMBER ,
22 x_entry_year IN NUMBER ,
23 x_entry_point IN VARCHAR2,
24 x_result IN VARCHAR2,
25 x_cef_received IN VARCHAR2,
26 x_clearing_app_source IN VARCHAR2,
27 x_imported IN VARCHAR2,
28 x_creation_date IN DATE ,
29 x_created_by IN NUMBER ,
30 x_last_update_date IN DATE ,
31 x_last_updated_by IN NUMBER ,
32 x_last_update_login IN NUMBER
33 ) AS
34 /*
35 || Created By : rgopalan
36 || Created On : 01-OCT-2001
37 || Purpose : Initialises the Old and New references for the columns of the table.
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
42 || (reverse chronological order - newest change first)
43 */
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM IGS_UC_APP_CLEARING
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 OPEN cur_old_ref_values;
57 FETCH cur_old_ref_values INTO old_references;
58 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
59 CLOSE cur_old_ref_values;
60 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
61 igs_ge_msg_stack.add;
62 app_exception.raise_exception;
63 RETURN;
64 END IF;
65 CLOSE cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.clearing_app_id := x_clearing_app_id;
69 new_references.app_id := x_app_id;
70 new_references.enquiry_no := x_enquiry_no;
71 new_references.app_no := x_app_no;
72 new_references.date_cef_sent := x_date_cef_sent;
73 new_references.cef_no := x_cef_no;
74 new_references.central_clearing := x_central_clearing;
75 new_references.institution := x_institution;
76 new_references.course := x_course;
77 new_references.campus := x_campus;
78 new_references.entry_month := x_entry_month;
79 new_references.entry_year := x_entry_year;
80 new_references.entry_point := x_entry_point;
81 new_references.result := x_result;
82 new_references.cef_received := x_cef_received;
83 new_references.clearing_app_source := x_clearing_app_source;
84 new_references.imported := x_imported;
85
86 IF (p_action = 'UPDATE') THEN
87 new_references.creation_date := old_references.creation_date;
88 new_references.created_by := old_references.created_by;
89 ELSE
90 new_references.creation_date := x_creation_date;
91 new_references.created_by := x_created_by;
92 END IF;
93
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END set_column_values;
99
100
101 PROCEDURE check_parent_existance AS
102 /*
103 || Created By : rgopalan
104 || Created On : 01-OCT-2001
105 || Purpose : Checks for the existance of Parent records.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113 IF (((old_references.app_id = new_references.app_id)) OR
114 ((new_references.app_id IS NULL))) THEN
115 NULL;
116 ELSIF NOT igs_uc_applicants_pkg.get_pk_for_validation (
117 new_references.app_id
118 ) THEN
119 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123
124 END check_parent_existance;
125
126
127 PROCEDURE check_child_existance IS
128 /*
129 || Created By : rgopalan
130 || Created On : 01-OCT-2001
131 || Purpose : Checks for the existance of Child records.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 */
137 BEGIN
138
139 igs_uc_app_clr_rnd_pkg.get_fk_igs_uc_app_clearing (
140 old_references.clearing_app_id
141 );
142
143 END check_child_existance;
144
145
146 FUNCTION get_pk_for_validation (
147 x_clearing_app_id IN NUMBER
148 ) RETURN BOOLEAN AS
149 /*
150 || Created By : rgopalan
151 || Created On : 01-OCT-2001
152 || Purpose : Validates the Primary Key of the table.
153 || Known limitations, enhancements or remarks :
154 || Change History :
155 || Who When What
156 || (reverse chronological order - newest change first)
157 */
158 CURSOR cur_rowid IS
159 SELECT rowid
160 FROM igs_uc_app_clearing
161 WHERE clearing_app_id = x_clearing_app_id ;
162
163 lv_rowid cur_rowid%RowType;
164
165 BEGIN
166
167 OPEN cur_rowid;
168 FETCH cur_rowid INTO lv_rowid;
169 IF (cur_rowid%FOUND) THEN
170 CLOSE cur_rowid;
171 RETURN(TRUE);
172 ELSE
173 CLOSE cur_rowid;
174 RETURN(FALSE);
175 END IF;
176
177 END get_pk_for_validation;
178
179
180 PROCEDURE get_fk_igs_uc_applicants (
181 x_app_id IN NUMBER
182 ) AS
183 /*
184 || Created By : rgopalan
185 || Created On : 01-OCT-2001
186 || Purpose : Validates the Foreign Keys for the table.
187 || Known limitations, enhancements or remarks :
188 || Change History :
189 || Who When What
190 || (reverse chronological order - newest change first)
191 || Nishikant 17Jun2002 Bug#2415346. UCAPCL_UCAP_FKIGS_UC_APPLICANTS
192 || message was replaced with IGS_UC_UCAPCL_UCAP_FK.
193 */
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM igs_uc_app_clearing
197 WHERE ((app_id = x_app_id));
198
199 lv_rowid cur_rowid%RowType;
200
201 BEGIN
202
203 OPEN cur_rowid;
204 FETCH cur_rowid INTO lv_rowid;
205 IF (cur_rowid%FOUND) THEN
206 CLOSE cur_rowid;
207 fnd_message.set_name ('IGS', 'IGS_UC_UCAPCL_UCAP_FK');
208 igs_ge_msg_stack.add;
209 app_exception.raise_exception;
210 RETURN;
211 END IF;
212 CLOSE cur_rowid;
213
214 END get_fk_igs_uc_applicants;
215
216
217 PROCEDURE before_dml (
218 p_action IN VARCHAR2,
219 x_rowid IN VARCHAR2,
220 x_clearing_app_id IN NUMBER ,
221 x_app_id IN NUMBER ,
222 x_enquiry_no IN NUMBER ,
223 x_app_no IN NUMBER ,
224 x_date_cef_sent IN DATE ,
225 x_cef_no IN NUMBER ,
226 x_central_clearing IN VARCHAR2,
227 x_institution IN VARCHAR2,
228 x_course IN VARCHAR2,
229 x_campus IN VARCHAR2,
230 x_entry_month IN NUMBER ,
231 x_entry_year IN NUMBER ,
232 x_entry_point IN VARCHAR2,
233 x_result IN VARCHAR2,
234 x_cef_received IN VARCHAR2,
235 x_clearing_app_source IN VARCHAR2,
236 x_imported IN VARCHAR2,
237 x_creation_date IN DATE ,
238 x_created_by IN NUMBER ,
239 x_last_update_date IN DATE ,
240 x_last_updated_by IN NUMBER ,
241 x_last_update_login IN NUMBER
242 ) AS
243 /*
244 || Created By : rgopalan
245 || Created On : 01-OCT-2001
246 || Purpose : Initialises the columns, Checks Constraints, Calls the
247 || Trigger Handlers for the table, before any DML operation.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
252 || (reverse chronological order - newest change first)
253 */
254 BEGIN
255
256 set_column_values (
257 p_action,
258 x_rowid,
259 x_clearing_app_id,
260 x_app_id,
261 x_enquiry_no,
262 x_app_no,
263 x_date_cef_sent,
264 x_cef_no,
265 x_central_clearing,
266 x_institution,
267 x_course,
268 x_campus,
269 x_entry_month,
270 x_entry_year,
271 x_entry_point,
272 x_result,
273 x_cef_received,
274 x_clearing_app_source,
275 x_imported,
276 x_creation_date,
277 x_created_by,
278 x_last_update_date,
279 x_last_updated_by,
280 x_last_update_login
281 );
282
283 IF (p_action = 'INSERT') THEN
284 -- Call all the procedures related to Before Insert.
285 IF ( get_pk_for_validation(
286 new_references.clearing_app_id
287 )
288 ) THEN
289 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 END IF;
293 check_parent_existance;
294 ELSIF (p_action = 'UPDATE') THEN
295 -- Call all the procedures related to Before Update.
296 check_parent_existance;
297 ELSIF (p_action = 'DELETE') THEN
298 -- Call all the procedures related to Before Delete.
299 check_child_existance;
300 ELSIF (p_action = 'VALIDATE_INSERT') THEN
301 -- Call all the procedures related to Before Insert.
302 IF ( get_pk_for_validation (
303 new_references.clearing_app_id
304 )
305 ) THEN
306 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310 ELSIF (p_action = 'VALIDATE_DELETE') THEN
311 check_child_existance;
312 END IF;
313
314 END before_dml;
315
316
317 PROCEDURE insert_row (
318 x_rowid IN OUT NOCOPY VARCHAR2,
319 x_clearing_app_id IN OUT NOCOPY NUMBER,
320 x_app_id IN NUMBER,
321 x_enquiry_no IN NUMBER,
322 x_app_no IN NUMBER,
323 x_date_cef_sent IN DATE,
324 x_cef_no IN NUMBER,
325 x_central_clearing IN VARCHAR2,
326 x_institution IN VARCHAR2,
327 x_course IN VARCHAR2,
328 x_campus IN VARCHAR2,
329 x_entry_month IN NUMBER,
330 x_entry_year IN NUMBER,
331 x_entry_point IN VARCHAR2,
332 x_result IN VARCHAR2,
333 x_cef_received IN VARCHAR2,
334 x_clearing_app_source IN VARCHAR2,
335 x_imported IN VARCHAR2,
336 x_mode IN VARCHAR2
337 ) AS
338 /*
339 || Created By : rgopalan
340 || Created On : 01-OCT-2001
341 || Purpose : Handles the INSERT DML logic for the table.
342 || Known limitations, enhancements or remarks :
343 || Change History :
344 || Who When What
345 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
346 || (reverse chronological order - newest change first)
347 */
348 CURSOR c IS
349 SELECT rowid
350 FROM igs_uc_app_clearing
351 WHERE clearing_app_id = x_clearing_app_id;
352
353 x_last_update_date DATE;
354 x_last_updated_by NUMBER;
355 x_last_update_login NUMBER;
356
357 BEGIN
358
359 x_last_update_date := SYSDATE;
360 IF (x_mode = 'I') THEN
361 x_last_updated_by := 1;
362 x_last_update_login := 0;
363 ELSIF (x_mode = 'R') THEN
364 x_last_updated_by := fnd_global.user_id;
365 IF (x_last_updated_by IS NULL) THEN
366 x_last_updated_by := -1;
367 END IF;
368 x_last_update_login := fnd_global.login_id;
369 IF (x_last_update_login IS NULL) THEN
370 x_last_update_login := -1;
371 END IF;
372 ELSE
373 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377
378 SELECT igs_uc_app_clearing_s.NEXTVAL
379 INTO x_clearing_app_id
380 FROM dual;
381
382 before_dml(
383 p_action => 'INSERT',
384 x_rowid => x_rowid,
385 x_clearing_app_id => x_clearing_app_id,
386 x_app_id => x_app_id,
387 x_enquiry_no => x_enquiry_no,
388 x_app_no => x_app_no,
389 x_date_cef_sent => x_date_cef_sent,
390 x_cef_no => x_cef_no,
391 x_central_clearing => x_central_clearing,
392 x_institution => x_institution,
393 x_course => x_course,
394 x_campus => x_campus,
395 x_entry_month => x_entry_month,
396 x_entry_year => x_entry_year,
397 x_entry_point => x_entry_point,
398 x_result => x_result,
399 x_cef_received => x_cef_received,
400 x_clearing_app_source => x_clearing_app_source,
401 x_imported => x_imported,
402 x_creation_date => x_last_update_date,
403 x_created_by => x_last_updated_by,
404 x_last_update_date => x_last_update_date,
405 x_last_updated_by => x_last_updated_by,
406 x_last_update_login => x_last_update_login
407 );
408
409 INSERT INTO igs_uc_app_clearing (
410 clearing_app_id,
411 app_id,
412 enquiry_no,
413 app_no,
414 date_cef_sent,
415 cef_no,
416 central_clearing,
417 institution,
418 course,
419 campus,
420 entry_month,
421 entry_year,
422 entry_point,
423 result,
424 cef_received,
425 clearing_app_source,
426 imported,
427 creation_date,
428 created_by,
429 last_update_date,
430 last_updated_by,
431 last_update_login
432 ) VALUES (
433 new_references.clearing_app_id,
434 new_references.app_id,
435 new_references.enquiry_no,
436 new_references.app_no,
437 new_references.date_cef_sent,
438 new_references.cef_no,
439 new_references.central_clearing,
440 new_references.institution,
441 new_references.course,
442 new_references.campus,
443 new_references.entry_month,
444 new_references.entry_year,
445 new_references.entry_point,
446 new_references.result,
447 new_references.cef_received,
448 new_references.clearing_app_source,
449 new_references.imported,
450 x_last_update_date,
451 x_last_updated_by,
452 x_last_update_date,
453 x_last_updated_by,
454 x_last_update_login
455 );
456
457 OPEN c;
458 FETCH c INTO x_rowid;
459 IF (c%NOTFOUND) THEN
460 CLOSE c;
461 RAISE NO_DATA_FOUND;
462 END IF;
463 CLOSE c;
464
465 END insert_row;
466
467
468 PROCEDURE lock_row (
469 x_rowid IN VARCHAR2,
470 x_clearing_app_id IN NUMBER,
471 x_app_id IN NUMBER,
472 x_enquiry_no IN NUMBER,
473 x_app_no IN NUMBER,
474 x_date_cef_sent IN DATE,
475 x_cef_no IN NUMBER,
476 x_central_clearing IN VARCHAR2,
477 x_institution IN VARCHAR2,
478 x_course IN VARCHAR2,
479 x_campus IN VARCHAR2,
480 x_entry_month IN NUMBER,
481 x_entry_year IN NUMBER,
482 x_entry_point IN VARCHAR2,
483 x_result IN VARCHAR2,
484 x_cef_received IN VARCHAR2,
485 x_clearing_app_source IN VARCHAR2,
486 x_imported IN VARCHAR2
487 ) AS
488 /*
489 || Created By : rgopalan
490 || Created On : 01-OCT-2001
491 || Purpose : Handles the LOCK mechanism for the table.
492 || Known limitations, enhancements or remarks :
493 || Change History :
494 || Who When What
495 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
496 || (reverse chronological order - newest change first)
497 */
498 CURSOR c1 IS
499 SELECT
500 app_id,
501 enquiry_no,
502 app_no,
503 date_cef_sent,
504 cef_no,
505 central_clearing,
506 institution,
507 course,
508 campus,
509 entry_month,
510 entry_year,
511 entry_point,
512 result,
513 cef_received,
514 clearing_app_source,
515 imported
516 FROM igs_uc_app_clearing
517 WHERE rowid = x_rowid
518 FOR UPDATE NOWAIT;
519
520 tlinfo c1%ROWTYPE;
521
522 BEGIN
523
524 OPEN c1;
525 FETCH c1 INTO tlinfo;
526 IF (c1%notfound) THEN
527 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
528 igs_ge_msg_stack.add;
529 CLOSE c1;
530 app_exception.raise_exception;
531 RETURN;
532 END IF;
533 CLOSE c1;
534
535 IF (
536 (tlinfo.app_id = x_app_id)
537 AND ((tlinfo.enquiry_no = x_enquiry_no) OR ((tlinfo.enquiry_no IS NULL) AND (X_enquiry_no IS NULL)))
538 AND ((tlinfo.app_no = x_app_no) OR ((tlinfo.app_no IS NULL) AND (X_app_no IS NULL)))
539 AND ((tlinfo.date_cef_sent = x_date_cef_sent) OR ((tlinfo.date_cef_sent IS NULL) AND (X_date_cef_sent IS NULL)))
540 AND (tlinfo.cef_no = x_cef_no)
541 AND (tlinfo.central_clearing = x_central_clearing)
542 AND ((tlinfo.institution = x_institution) OR ((tlinfo.institution IS NULL) AND (X_institution IS NULL)))
543 AND ((tlinfo.course = x_course) OR ((tlinfo.course IS NULL) AND (X_course IS NULL)))
544 AND ((tlinfo.campus = x_campus) OR ((tlinfo.campus IS NULL) AND (X_campus IS NULL)))
545 AND ((tlinfo.entry_month = x_entry_month) OR ((tlinfo.entry_month IS NULL) AND (X_entry_month IS NULL)))
546 AND ((tlinfo.entry_year = x_entry_year) OR ((tlinfo.entry_year IS NULL) AND (X_entry_year IS NULL)))
547 AND ((tlinfo.entry_point = x_entry_point) OR ((tlinfo.entry_point IS NULL) AND (X_entry_point IS NULL)))
548 AND ((tlinfo.result = x_result) OR ((tlinfo.result IS NULL) AND (X_result IS NULL)))
549 AND (tlinfo.cef_received = x_cef_received)
550 AND (tlinfo.clearing_app_source = x_clearing_app_source)
551 AND (tlinfo.imported = x_imported)
552 ) THEN
553 NULL;
554 ELSE
555 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
556 igs_ge_msg_stack.add;
557 app_exception.raise_exception;
558 END IF;
559
560 RETURN;
561
562 END lock_row;
563
564
565 PROCEDURE update_row (
566 x_rowid IN VARCHAR2,
567 x_clearing_app_id IN NUMBER,
568 x_app_id IN NUMBER,
569 x_enquiry_no IN NUMBER,
570 x_app_no IN NUMBER,
571 x_date_cef_sent IN DATE,
572 x_cef_no IN NUMBER,
573 x_central_clearing IN VARCHAR2,
574 x_institution IN VARCHAR2,
575 x_course IN VARCHAR2,
576 x_campus IN VARCHAR2,
577 x_entry_month IN NUMBER,
578 x_entry_year IN NUMBER,
579 x_entry_point IN VARCHAR2,
580 x_result IN VARCHAR2,
581 x_cef_received IN VARCHAR2,
582 x_clearing_app_source IN VARCHAR2,
583 x_imported IN VARCHAR2,
584 x_mode IN VARCHAR2
585 ) AS
586 /*
587 || Created By : rgopalan
588 || Created On : 01-OCT-2001
589 || Purpose : Handles the UPDATE DML logic for the table.
590 || Known limitations, enhancements or remarks :
591 || Change History :
592 || Who When What
593 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
594 || (reverse chronological order - newest change first)
595 */
596 x_last_update_date DATE ;
597 x_last_updated_by NUMBER;
598 x_last_update_login NUMBER;
599
600 BEGIN
601
602 x_last_update_date := SYSDATE;
603 IF (X_MODE = 'I') THEN
604 x_last_updated_by := 1;
605 x_last_update_login := 0;
606 ELSIF (x_mode = 'R') THEN
607 x_last_updated_by := fnd_global.user_id;
608 IF x_last_updated_by IS NULL THEN
609 x_last_updated_by := -1;
610 END IF;
611 x_last_update_login := fnd_global.login_id;
612 IF (x_last_update_login IS NULL) THEN
613 x_last_update_login := -1;
614 END IF;
615 ELSE
616 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
617 igs_ge_msg_stack.add;
618 app_exception.raise_exception;
619 END IF;
620
621 before_dml(
622 p_action => 'UPDATE',
623 x_rowid => x_rowid,
624 x_clearing_app_id => x_clearing_app_id,
625 x_app_id => x_app_id,
626 x_enquiry_no => x_enquiry_no,
627 x_app_no => x_app_no,
628 x_date_cef_sent => x_date_cef_sent,
629 x_cef_no => x_cef_no,
630 x_central_clearing => x_central_clearing,
631 x_institution => x_institution,
632 x_course => x_course,
633 x_campus => x_campus,
634 x_entry_month => x_entry_month,
635 x_entry_year => x_entry_year,
636 x_entry_point => x_entry_point,
637 x_result => x_result,
638 x_cef_received => x_cef_received,
639 x_clearing_app_source => x_clearing_app_source,
640 x_imported => x_imported,
641 x_creation_date => x_last_update_date,
642 x_created_by => x_last_updated_by,
643 x_last_update_date => x_last_update_date,
644 x_last_updated_by => x_last_updated_by,
645 x_last_update_login => x_last_update_login
646 );
647
648 UPDATE igs_uc_app_clearing
649 SET
650 app_id = new_references.app_id,
651 enquiry_no = new_references.enquiry_no,
652 app_no = new_references.app_no,
653 date_cef_sent = new_references.date_cef_sent,
654 cef_no = new_references.cef_no,
655 central_clearing = new_references.central_clearing,
656 institution = new_references.institution,
657 course = new_references.course,
658 campus = new_references.campus,
659 entry_month = new_references.entry_month,
660 entry_year = new_references.entry_year,
661 entry_point = new_references.entry_point,
662 result = new_references.result,
663 cef_received = new_references.cef_received,
664 clearing_app_source = new_references.clearing_app_source,
665 imported = new_references.imported,
666 last_update_date = x_last_update_date,
667 last_updated_by = x_last_updated_by,
668 last_update_login = x_last_update_login
669 WHERE rowid = x_rowid;
670
671 IF (SQL%NOTFOUND) THEN
672 RAISE NO_DATA_FOUND;
673 END IF;
674
675 END update_row;
676
677
678 PROCEDURE add_row (
679 x_rowid IN OUT NOCOPY VARCHAR2,
680 x_clearing_app_id IN OUT NOCOPY NUMBER,
681 x_app_id IN NUMBER,
682 x_enquiry_no IN NUMBER,
683 x_app_no IN NUMBER,
684 x_date_cef_sent IN DATE,
685 x_cef_no IN NUMBER,
686 x_central_clearing IN VARCHAR2,
687 x_institution IN VARCHAR2,
688 x_course IN VARCHAR2,
689 x_campus IN VARCHAR2,
690 x_entry_month IN NUMBER,
691 x_entry_year IN NUMBER,
692 x_entry_point IN VARCHAR2,
693 x_result IN VARCHAR2,
694 x_cef_received IN VARCHAR2,
695 x_clearing_app_source IN VARCHAR2,
696 x_imported IN VARCHAR2,
697 x_mode IN VARCHAR2
698 ) AS
699 /*
700 || Created By : rgopalan
701 || Created On : 01-OCT-2001
702 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
703 || Known limitations, enhancements or remarks :
704 || Change History :
705 || Who When What
706 || smaddali 10-jun-03 obsoleting datetimestamp field for ucfd203 - bug#2669208
707 || (reverse chronological order - newest change first)
708 */
709 CURSOR c1 IS
710 SELECT rowid
711 FROM igs_uc_app_clearing
712 WHERE clearing_app_id = x_clearing_app_id;
713
714 BEGIN
715
716 OPEN c1;
717 FETCH c1 INTO x_rowid;
718 IF (c1%NOTFOUND) THEN
719 CLOSE c1;
720
721 insert_row (
722 x_rowid,
723 x_clearing_app_id,
724 x_app_id,
725 x_enquiry_no,
726 x_app_no,
727 x_date_cef_sent,
728 x_cef_no,
729 x_central_clearing,
730 x_institution,
731 x_course,
732 x_campus,
733 x_entry_month,
734 x_entry_year,
735 x_entry_point,
736 x_result,
737 x_cef_received,
738 x_clearing_app_source,
739 x_imported,
740 x_mode
741 );
742 RETURN;
743 END IF;
744 CLOSE c1;
745
746 update_row (
747 x_rowid,
748 x_clearing_app_id,
749 x_app_id,
750 x_enquiry_no,
751 x_app_no,
752 x_date_cef_sent,
753 x_cef_no,
754 x_central_clearing,
755 x_institution,
756 x_course,
757 x_campus,
758 x_entry_month,
759 x_entry_year,
760 x_entry_point,
761 x_result,
762 x_cef_received,
763 x_clearing_app_source,
764 x_imported,
765 x_mode
766 );
767
768 END add_row;
769
770
771 PROCEDURE delete_row (
772 x_rowid IN VARCHAR2
773 ) AS
774 /*
775 || Created By : rgopalan
776 || Created On : 01-OCT-2001
777 || Purpose : Handles the DELETE DML logic for the table.
778 || Known limitations, enhancements or remarks :
779 || Change History :
780 || Who When What
781 || (reverse chronological order - newest change first)
782 */
783 BEGIN
784
785 before_dml (
786 p_action => 'DELETE',
787 x_rowid => x_rowid
788 );
789
790 DELETE FROM igs_uc_app_clearing
791 WHERE rowid = x_rowid;
792
793 IF (SQL%NOTFOUND) THEN
794 RAISE NO_DATA_FOUND;
795 END IF;
796
797 END delete_row;
798
799
800 END igs_uc_app_clearing_pkg;