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