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