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