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