[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FAC_WL_PKG
Source
1 PACKAGE BODY igs_ps_fac_wl_pkg AS
2 /* $Header: IGSPI3JB.pls 120.0 2005/06/01 20:11:29 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_fac_wl%ROWTYPE;
6 new_references igs_ps_fac_wl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fac_wl_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_calendar_cat IN VARCHAR2 DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_attribute_category IN VARCHAR2 DEFAULT NULL,
17 x_attribute1 IN VARCHAR2 DEFAULT NULL,
18 x_attribute2 IN VARCHAR2 DEFAULT NULL,
19 x_attribute3 IN VARCHAR2 DEFAULT NULL,
20 x_attribute4 IN VARCHAR2 DEFAULT NULL,
21 x_attribute5 IN VARCHAR2 DEFAULT NULL,
22 x_attribute6 IN VARCHAR2 DEFAULT NULL,
23 x_attribute7 IN VARCHAR2 DEFAULT NULL,
24 x_attribute8 IN VARCHAR2 DEFAULT NULL,
25 x_attribute9 IN VARCHAR2 DEFAULT NULL,
26 x_attribute10 IN VARCHAR2 DEFAULT NULL,
27 x_attribute11 IN VARCHAR2 DEFAULT NULL,
28 x_attribute12 IN VARCHAR2 DEFAULT NULL,
29 x_attribute13 IN VARCHAR2 DEFAULT NULL,
30 x_attribute14 IN VARCHAR2 DEFAULT NULL,
31 x_attribute15 IN VARCHAR2 DEFAULT NULL,
32 x_attribute16 IN VARCHAR2 DEFAULT NULL,
33 x_attribute17 IN VARCHAR2 DEFAULT NULL,
34 x_attribute18 IN VARCHAR2 DEFAULT NULL,
35 x_attribute19 IN VARCHAR2 DEFAULT NULL,
36 x_attribute20 IN VARCHAR2 DEFAULT NULL,
37 x_creation_date IN DATE DEFAULT NULL,
38 x_created_by IN NUMBER DEFAULT NULL,
39 x_last_update_date IN DATE DEFAULT NULL,
40 x_last_updated_by IN NUMBER DEFAULT NULL,
41 x_last_update_login IN NUMBER DEFAULT NULL
42 ) AS
43 /*
44 || Created By : kkillams
45 || Created On : 17-JAN-2002
46 || Purpose : Initialises the Old and New references for the columns of the table.
47 || Known limitations, enhancements or remarks :
48 || Change History :
49 || Who When What
50 || (reverse chronological order - newest change first)
51 */
52
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM igs_ps_fac_wl
56 WHERE rowid = x_rowid;
57
58 BEGIN
59
60 l_rowid := x_rowid;
61
62 -- Code for setting the Old and New Reference Values.
63 -- Populate Old Values.
64 OPEN cur_old_ref_values;
65 FETCH cur_old_ref_values INTO old_references;
66 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
67 CLOSE cur_old_ref_values;
68 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
69 igs_ge_msg_stack.add;
70 app_exception.raise_exception;
71 RETURN;
72 END IF;
73 CLOSE cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.fac_wl_id := x_fac_wl_id;
77 new_references.person_id := x_person_id;
78 new_references.calendar_cat := x_calendar_cat;
79 new_references.cal_type := x_cal_type;
80 new_references.ci_sequence_number := x_ci_sequence_number;
81 new_references.attribute_category := x_attribute_category;
82 new_references.attribute1 := x_attribute1;
83 new_references.attribute2 := x_attribute2;
84 new_references.attribute3 := x_attribute3;
85 new_references.attribute4 := x_attribute4;
86 new_references.attribute5 := x_attribute5;
87 new_references.attribute6 := x_attribute6;
88 new_references.attribute7 := x_attribute7;
89 new_references.attribute8 := x_attribute8;
90 new_references.attribute9 := x_attribute9;
91 new_references.attribute10 := x_attribute10;
92 new_references.attribute11 := x_attribute11;
93 new_references.attribute12 := x_attribute12;
94 new_references.attribute13 := x_attribute13;
95 new_references.attribute14 := x_attribute14;
96 new_references.attribute15 := x_attribute15;
97 new_references.attribute16 := x_attribute16;
98 new_references.attribute17 := x_attribute17;
99 new_references.attribute18 := x_attribute18;
100 new_references.attribute19 := x_attribute19;
101 new_references.attribute20 := x_attribute20;
102
103 IF (p_action = 'UPDATE') THEN
104 new_references.creation_date := old_references.creation_date;
105 new_references.created_by := old_references.created_by;
106 ELSE
107 new_references.creation_date := x_creation_date;
108 new_references.created_by := x_created_by;
109 END IF;
110
111 new_references.last_update_date := x_last_update_date;
112 new_references.last_updated_by := x_last_updated_by;
113 new_references.last_update_login := x_last_update_login;
114
115 END set_column_values;
116
117
118 PROCEDURE check_uniqueness AS
119 /*
120 || Created By : kkillams
121 || Created On : 17-JAN-2002
122 || Purpose : Handles the Unique Constraint logic defined for the columns.
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 */
128 BEGIN
129
130 IF ( get_uk_for_validation (
131 new_references.person_id,
132 new_references.cal_type,
133 new_references.ci_sequence_number
134 )
135 ) THEN
136 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
137 igs_ge_msg_stack.add;
138 app_exception.raise_exception;
139 END IF;
140
141 END check_uniqueness;
142
143
144 PROCEDURE check_parent_existance AS
145 /*
146 || Created By : kkillams
147 || Created On : 17-JAN-2002
148 || Purpose : Checks for the existance of Parent records.
149 || Known limitations, enhancements or remarks :
150 || Change History :
151 || Who When What
152 || (reverse chronological order - newest change first)
153 */
154 CURSOR cur_hz IS SELECT 1 FROM HZ_PARTIES
155 WHERE PARTY_ID = new_references.person_id;
156 rec_hz cur_hz%rowtype;
157 BEGIN
158
159 IF (((old_references.person_id = new_references.person_id)) OR
160 ((new_references.person_id IS NULL))) THEN
161 NULL;
162 ELSE
163 OPEN cur_hz;
164 FETCH cur_hz INTO rec_hz;
165 IF cur_hz%NOTFOUND THEN
166 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
167 igs_ge_msg_stack.add;
168 app_exception.raise_exception;
169 END IF;
170 CLOSE cur_hz;
171 END IF;
172
173 IF (((old_references.cal_type = new_references.cal_type) AND
174 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
175 ((new_references.cal_type IS NULL) OR
176 (new_references.ci_sequence_number IS NULL))) THEN
177 NULL;
178 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
179 new_references.cal_type,
180 new_references.ci_sequence_number
181 ) THEN
182 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
183 igs_ge_msg_stack.add;
184 app_exception.raise_exception;
185 END IF;
186
187 END check_parent_existance;
188
189
190 PROCEDURE check_child_existance IS
191 /*
192 || Created By : kkillams
193 || Created On : 17-JAN-2002
194 || Purpose : Checks for the existance of Child records.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 BEGIN
201
202 igs_ps_fac_asg_task_pkg.get_fk_igs_ps_fac_wl (
203 old_references.fac_wl_id
204 );
205
206 igs_ps_fac_ovr_wl_pkg.get_fk_igs_ps_fac_wl (
207 old_references.fac_wl_id
208 );
209
210 END check_child_existance;
211
212
213 FUNCTION get_pk_for_validation (
214 x_fac_wl_id IN NUMBER
215 ) RETURN BOOLEAN AS
216 /*
217 || Created By : kkillams
218 || Created On : 17-JAN-2002
219 || Purpose : Validates the Primary Key of the table.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 CURSOR cur_rowid IS
226 SELECT rowid
227 FROM igs_ps_fac_wl
228 WHERE fac_wl_id = x_fac_wl_id
229 FOR UPDATE NOWAIT;
230
231 lv_rowid cur_rowid%RowType;
232
233 BEGIN
234
235 OPEN cur_rowid;
236 FETCH cur_rowid INTO lv_rowid;
237 IF (cur_rowid%FOUND) THEN
238 CLOSE cur_rowid;
239 RETURN(TRUE);
240 ELSE
241 CLOSE cur_rowid;
242 RETURN(FALSE);
243 END IF;
244
245 END get_pk_for_validation;
246
247
248 FUNCTION get_uk_for_validation (
249 x_person_id IN NUMBER,
250 x_cal_type IN VARCHAR2,
251 x_ci_sequence_number IN NUMBER
252 ) RETURN BOOLEAN AS
253 /*
254 || Created By : kkillams
255 || Created On : 17-JAN-2002
256 || Purpose : Validates the Unique Keys of the table.
257 || Known limitations, enhancements or remarks :
258 || Change History :
259 || Who When What
260 || (reverse chronological order - newest change first)
261 */
262 CURSOR cur_rowid IS
263 SELECT rowid
264 FROM igs_ps_fac_wl
265 WHERE person_id = x_person_id
266 AND cal_type = x_cal_type
267 AND ci_sequence_number = x_ci_sequence_number
268 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 OPEN cur_rowid;
275 FETCH cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 CLOSE cur_rowid;
278 RETURN (true);
279 ELSE
280 CLOSE cur_rowid;
281 RETURN(FALSE);
282 END IF;
283
284 END get_uk_for_validation ;
285
286 PROCEDURE get_fk_igs_ca_inst (
287 x_cal_type IN VARCHAR2,
288 x_sequence_number IN NUMBER
289 ) AS
290 /*
291 || Created By : kkillams
292 || Created On : 17-JAN-2002
293 || Purpose : Validates the Foreign Keys for the table.
294 || Known limitations, enhancements or remarks :
295 || Change History :
296 || Who When What
297 || (reverse chronological order - newest change first)
298 */
299 CURSOR cur_rowid IS
300 SELECT rowid
301 FROM igs_ps_fac_wl
302 WHERE ((cal_type = x_cal_type) AND
303 (ci_sequence_number = x_sequence_number));
304
305 lv_rowid cur_rowid%RowType;
306
307 BEGIN
308
309 OPEN cur_rowid;
310 FETCH cur_rowid INTO lv_rowid;
311 IF (cur_rowid%FOUND) THEN
312 CLOSE cur_rowid;
313 fnd_message.set_name ('IGS', 'IGS_PS_FWL_CI_FK');
314 igs_ge_msg_stack.add;
315 app_exception.raise_exception;
316 RETURN;
317 END IF;
318 CLOSE cur_rowid;
319
320 END get_fk_igs_ca_inst;
321
322
323 PROCEDURE before_dml (
324 p_action IN VARCHAR2,
325 x_rowid IN VARCHAR2 DEFAULT NULL,
326 x_fac_wl_id IN NUMBER DEFAULT NULL,
327 x_person_id IN NUMBER DEFAULT NULL,
328 x_calendar_cat IN VARCHAR2 DEFAULT NULL,
329 x_cal_type IN VARCHAR2 DEFAULT NULL,
330 x_ci_sequence_number IN NUMBER DEFAULT NULL,
331 x_attribute_category IN VARCHAR2 DEFAULT NULL,
332 x_attribute1 IN VARCHAR2 DEFAULT NULL,
333 x_attribute2 IN VARCHAR2 DEFAULT NULL,
334 x_attribute3 IN VARCHAR2 DEFAULT NULL,
335 x_attribute4 IN VARCHAR2 DEFAULT NULL,
336 x_attribute5 IN VARCHAR2 DEFAULT NULL,
337 x_attribute6 IN VARCHAR2 DEFAULT NULL,
338 x_attribute7 IN VARCHAR2 DEFAULT NULL,
339 x_attribute8 IN VARCHAR2 DEFAULT NULL,
340 x_attribute9 IN VARCHAR2 DEFAULT NULL,
341 x_attribute10 IN VARCHAR2 DEFAULT NULL,
342 x_attribute11 IN VARCHAR2 DEFAULT NULL,
343 x_attribute12 IN VARCHAR2 DEFAULT NULL,
344 x_attribute13 IN VARCHAR2 DEFAULT NULL,
345 x_attribute14 IN VARCHAR2 DEFAULT NULL,
346 x_attribute15 IN VARCHAR2 DEFAULT NULL,
347 x_attribute16 IN VARCHAR2 DEFAULT NULL,
348 x_attribute17 IN VARCHAR2 DEFAULT NULL,
349 x_attribute18 IN VARCHAR2 DEFAULT NULL,
350 x_attribute19 IN VARCHAR2 DEFAULT NULL,
351 x_attribute20 IN VARCHAR2 DEFAULT NULL,
352 x_creation_date IN DATE DEFAULT NULL,
353 x_created_by IN NUMBER DEFAULT NULL,
354 x_last_update_date IN DATE DEFAULT NULL,
355 x_last_updated_by IN NUMBER DEFAULT NULL,
356 x_last_update_login IN NUMBER DEFAULT NULL
357 ) AS
358 /*
359 || Created By : kkillams
360 || Created On : 17-JAN-2002
361 || Purpose : Initialises the columns, Checks Constraints, Calls the
362 || Trigger Handlers for the table, before any DML operation.
363 || Known limitations, enhancements or remarks :
364 || Change History :
365 || Who When What
366 || (reverse chronological order - newest change first)
367 */
368 BEGIN
369
370 set_column_values (
371 p_action,
372 x_rowid,
373 x_fac_wl_id,
374 x_person_id,
375 x_calendar_cat,
376 x_cal_type,
377 x_ci_sequence_number,
378 x_attribute_category,
379 x_attribute1,
380 x_attribute2,
381 x_attribute3,
382 x_attribute4,
383 x_attribute5,
384 x_attribute6,
385 x_attribute7,
386 x_attribute8,
387 x_attribute9,
388 x_attribute10,
389 x_attribute11,
390 x_attribute12,
391 x_attribute13,
392 x_attribute14,
393 x_attribute15,
394 x_attribute16,
395 x_attribute17,
396 x_attribute18,
397 x_attribute19,
398 x_attribute20,
399 x_creation_date,
400 x_created_by,
401 x_last_update_date,
402 x_last_updated_by,
403 x_last_update_login
404 );
405
406 IF (p_action = 'INSERT') THEN
407 -- Call all the procedures related to Before Insert.
408 IF ( get_pk_for_validation(
409 new_references.fac_wl_id
410 )
411 ) THEN
412 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
413 igs_ge_msg_stack.add;
414 app_exception.raise_exception;
415 END IF;
416 check_uniqueness;
417 check_parent_existance;
418 ELSIF (p_action = 'UPDATE') THEN
419 -- Call all the procedures related to Before Update.
420 check_uniqueness;
421 check_parent_existance;
422 ELSIF (p_action = 'DELETE') THEN
423 -- Call all the procedures related to Before Delete.
424 check_child_existance;
425 ELSIF (p_action = 'VALIDATE_INSERT') THEN
426 -- Call all the procedures related to Before Insert.
427 IF ( get_pk_for_validation (
428 new_references.fac_wl_id
429 )
430 ) THEN
431 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434 END IF;
435 check_uniqueness;
436 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
437 check_uniqueness;
438 ELSIF (p_action = 'VALIDATE_DELETE') THEN
439 check_child_existance;
440 END IF;
441
442 END before_dml;
443
444
445 PROCEDURE insert_row (
446 x_rowid IN OUT NOCOPY VARCHAR2,
447 x_fac_wl_id IN OUT NOCOPY NUMBER,
448 x_person_id IN NUMBER,
449 x_calendar_cat IN VARCHAR2,
450 x_cal_type IN VARCHAR2,
451 x_ci_sequence_number IN NUMBER,
452 x_attribute_category IN VARCHAR2,
453 x_attribute1 IN VARCHAR2,
454 x_attribute2 IN VARCHAR2,
455 x_attribute3 IN VARCHAR2,
456 x_attribute4 IN VARCHAR2,
457 x_attribute5 IN VARCHAR2,
458 x_attribute6 IN VARCHAR2,
459 x_attribute7 IN VARCHAR2,
460 x_attribute8 IN VARCHAR2,
461 x_attribute9 IN VARCHAR2,
462 x_attribute10 IN VARCHAR2,
463 x_attribute11 IN VARCHAR2,
464 x_attribute12 IN VARCHAR2,
465 x_attribute13 IN VARCHAR2,
466 x_attribute14 IN VARCHAR2,
467 x_attribute15 IN VARCHAR2,
468 x_attribute16 IN VARCHAR2,
469 x_attribute17 IN VARCHAR2,
470 x_attribute18 IN VARCHAR2,
471 x_attribute19 IN VARCHAR2,
472 x_attribute20 IN VARCHAR2,
473 x_mode IN VARCHAR2 DEFAULT 'R'
474 ) AS
475 /*
476 || Created By : kkillams
477 || Created On : 17-JAN-2002
478 || Purpose : Handles the INSERT DML logic for the table.
479 || Known limitations, enhancements or remarks :
480 || Change History :
481 || Who When What
482 || (reverse chronological order - newest change first)
483 */
484 CURSOR c IS
485 SELECT rowid
486 FROM igs_ps_fac_wl
487 WHERE fac_wl_id = x_fac_wl_id;
488
489 x_last_update_date DATE;
490 x_last_updated_by NUMBER;
491 x_last_update_login NUMBER;
492
493 BEGIN
494
495 x_last_update_date := SYSDATE;
496 IF (x_mode = 'I') THEN
497 x_last_updated_by := 1;
498 x_last_update_login := 0;
499 ELSIF (x_mode = 'R') THEN
500 x_last_updated_by := fnd_global.user_id;
501 IF (x_last_updated_by IS NULL) THEN
502 x_last_updated_by := -1;
503 END IF;
504 x_last_update_login := fnd_global.login_id;
505 IF (x_last_update_login IS NULL) THEN
506 x_last_update_login := -1;
507 END IF;
508 ELSE
509 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
510 igs_ge_msg_stack.add;
511 app_exception.raise_exception;
512 END IF;
513
514 SELECT igs_ps_fac_wl_s.NEXTVAL
515 INTO x_fac_wl_id
516 FROM dual;
517
518 before_dml(
519 p_action => 'INSERT',
520 x_rowid => x_rowid,
521 x_fac_wl_id => x_fac_wl_id,
522 x_person_id => x_person_id,
523 x_calendar_cat => x_calendar_cat,
524 x_cal_type => x_cal_type,
525 x_ci_sequence_number => x_ci_sequence_number,
526 x_attribute_category => x_attribute_category,
527 x_attribute1 => x_attribute1,
528 x_attribute2 => x_attribute2,
529 x_attribute3 => x_attribute3,
530 x_attribute4 => x_attribute4,
531 x_attribute5 => x_attribute5,
532 x_attribute6 => x_attribute6,
533 x_attribute7 => x_attribute7,
534 x_attribute8 => x_attribute8,
535 x_attribute9 => x_attribute9,
536 x_attribute10 => x_attribute10,
537 x_attribute11 => x_attribute11,
538 x_attribute12 => x_attribute12,
539 x_attribute13 => x_attribute13,
540 x_attribute14 => x_attribute14,
541 x_attribute15 => x_attribute15,
542 x_attribute16 => x_attribute16,
543 x_attribute17 => x_attribute17,
544 x_attribute18 => x_attribute18,
545 x_attribute19 => x_attribute19,
546 x_attribute20 => x_attribute20,
547 x_creation_date => x_last_update_date,
548 x_created_by => x_last_updated_by,
549 x_last_update_date => x_last_update_date,
550 x_last_updated_by => x_last_updated_by,
551 x_last_update_login => x_last_update_login
552 );
553
554 INSERT INTO igs_ps_fac_wl (
555 fac_wl_id,
556 person_id,
557 calendar_cat,
558 cal_type,
559 ci_sequence_number,
560 attribute_category,
561 attribute1,
562 attribute2,
563 attribute3,
564 attribute4,
565 attribute5,
566 attribute6,
567 attribute7,
568 attribute8,
569 attribute9,
570 attribute10,
571 attribute11,
572 attribute12,
573 attribute13,
574 attribute14,
575 attribute15,
576 attribute16,
577 attribute17,
578 attribute18,
579 attribute19,
580 attribute20,
581 creation_date,
582 created_by,
583 last_update_date,
584 last_updated_by,
585 last_update_login
586 ) VALUES (
587 new_references.fac_wl_id,
588 new_references.person_id,
589 new_references.calendar_cat,
590 new_references.cal_type,
591 new_references.ci_sequence_number,
592 new_references.attribute_category,
593 new_references.attribute1,
594 new_references.attribute2,
595 new_references.attribute3,
596 new_references.attribute4,
597 new_references.attribute5,
598 new_references.attribute6,
599 new_references.attribute7,
600 new_references.attribute8,
601 new_references.attribute9,
602 new_references.attribute10,
603 new_references.attribute11,
604 new_references.attribute12,
605 new_references.attribute13,
606 new_references.attribute14,
607 new_references.attribute15,
608 new_references.attribute16,
609 new_references.attribute17,
610 new_references.attribute18,
611 new_references.attribute19,
612 new_references.attribute20,
613 x_last_update_date,
614 x_last_updated_by,
615 x_last_update_date,
616 x_last_updated_by,
617 x_last_update_login
618 );
619
620 OPEN c;
621 FETCH c INTO x_rowid;
622 IF (c%NOTFOUND) THEN
623 CLOSE c;
624 RAISE NO_DATA_FOUND;
625 END IF;
626 CLOSE c;
627
628 END insert_row;
629
630
631 PROCEDURE lock_row (
632 x_rowid IN VARCHAR2,
633 x_fac_wl_id IN NUMBER,
634 x_person_id IN NUMBER,
635 x_calendar_cat IN VARCHAR2,
636 x_cal_type IN VARCHAR2,
637 x_ci_sequence_number IN NUMBER,
638 x_attribute_category IN VARCHAR2,
639 x_attribute1 IN VARCHAR2,
640 x_attribute2 IN VARCHAR2,
641 x_attribute3 IN VARCHAR2,
642 x_attribute4 IN VARCHAR2,
643 x_attribute5 IN VARCHAR2,
644 x_attribute6 IN VARCHAR2,
645 x_attribute7 IN VARCHAR2,
646 x_attribute8 IN VARCHAR2,
647 x_attribute9 IN VARCHAR2,
648 x_attribute10 IN VARCHAR2,
649 x_attribute11 IN VARCHAR2,
650 x_attribute12 IN VARCHAR2,
651 x_attribute13 IN VARCHAR2,
652 x_attribute14 IN VARCHAR2,
653 x_attribute15 IN VARCHAR2,
654 x_attribute16 IN VARCHAR2,
655 x_attribute17 IN VARCHAR2,
656 x_attribute18 IN VARCHAR2,
657 x_attribute19 IN VARCHAR2,
658 x_attribute20 IN VARCHAR2
659 ) AS
660 /*
661 || Created By : kkillams
662 || Created On : 17-JAN-2002
663 || Purpose : Handles the LOCK mechanism for the table.
664 || Known limitations, enhancements or remarks :
665 || Change History :
666 || Who When What
667 || sarakshi 08-Apr-2002 column std_exp_wl is made null,as a part of bug:2277132
668 || (reverse chronological order - newest change first)
669 */
670 CURSOR c1 IS
671 SELECT
672 person_id,
673 calendar_cat,
674 cal_type,
675 ci_sequence_number,
676 attribute_category,
677 attribute1,
678 attribute2,
679 attribute3,
680 attribute4,
681 attribute5,
682 attribute6,
683 attribute7,
684 attribute8,
685 attribute9,
686 attribute10,
687 attribute11,
688 attribute12,
689 attribute13,
690 attribute14,
691 attribute15,
692 attribute16,
693 attribute17,
694 attribute18,
695 attribute19,
696 attribute20
697 FROM igs_ps_fac_wl
698 WHERE rowid = x_rowid
699 FOR UPDATE NOWAIT;
700
701 tlinfo c1%ROWTYPE;
702
703 BEGIN
704
705 OPEN c1;
706 FETCH c1 INTO tlinfo;
707 IF (c1%notfound) THEN
708 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
709 igs_ge_msg_stack.add;
710 CLOSE c1;
711 app_exception.raise_exception;
712 RETURN;
713 END IF;
714 CLOSE c1;
715
716 IF (
717 (tlinfo.person_id = x_person_id)
718 AND (tlinfo.calendar_cat = x_calendar_cat)
719 AND (tlinfo.cal_type = x_cal_type)
720 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
721 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
722 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
723 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
724 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
725 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
726 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
727 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
728 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
729 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
730 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
731 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
732 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
733 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
734 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
735 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
736 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
737 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
738 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
739 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
740 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
741 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
742 ) THEN
743 NULL;
744 ELSE
745 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
746 igs_ge_msg_stack.add;
747 app_exception.raise_exception;
748 END IF;
749
750 RETURN;
751
752 END lock_row;
753
754
755 PROCEDURE update_row (
756 x_rowid IN VARCHAR2,
757 x_fac_wl_id IN NUMBER,
758 x_person_id IN NUMBER,
759 x_calendar_cat IN VARCHAR2,
760 x_cal_type IN VARCHAR2,
761 x_ci_sequence_number IN NUMBER,
762 x_attribute_category IN VARCHAR2,
763 x_attribute1 IN VARCHAR2,
764 x_attribute2 IN VARCHAR2,
765 x_attribute3 IN VARCHAR2,
766 x_attribute4 IN VARCHAR2,
767 x_attribute5 IN VARCHAR2,
768 x_attribute6 IN VARCHAR2,
769 x_attribute7 IN VARCHAR2,
770 x_attribute8 IN VARCHAR2,
771 x_attribute9 IN VARCHAR2,
772 x_attribute10 IN VARCHAR2,
773 x_attribute11 IN VARCHAR2,
774 x_attribute12 IN VARCHAR2,
775 x_attribute13 IN VARCHAR2,
776 x_attribute14 IN VARCHAR2,
777 x_attribute15 IN VARCHAR2,
778 x_attribute16 IN VARCHAR2,
779 x_attribute17 IN VARCHAR2,
780 x_attribute18 IN VARCHAR2,
781 x_attribute19 IN VARCHAR2,
782 x_attribute20 IN VARCHAR2,
783 x_mode IN VARCHAR2 DEFAULT 'R'
784 ) AS
785 /*
786 || Created By : kkillams
787 || Created On : 17-JAN-2002
788 || Purpose : Handles the UPDATE DML logic for the table.
789 || Known limitations, enhancements or remarks :
790 || Change History :
791 || Who When What
792 || (reverse chronological order - newest change first)
793 */
794 x_last_update_date DATE ;
795 x_last_updated_by NUMBER;
796 x_last_update_login NUMBER;
797
798 BEGIN
799
800 x_last_update_date := SYSDATE;
801 IF (X_MODE = 'I') THEN
802 x_last_updated_by := 1;
803 x_last_update_login := 0;
804 ELSIF (x_mode = 'R') THEN
805 x_last_updated_by := fnd_global.user_id;
806 IF x_last_updated_by IS NULL THEN
807 x_last_updated_by := -1;
808 END IF;
809 x_last_update_login := fnd_global.login_id;
810 IF (x_last_update_login IS NULL) THEN
811 x_last_update_login := -1;
812 END IF;
813 ELSE
814 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
815 igs_ge_msg_stack.add;
816 app_exception.raise_exception;
817 END IF;
818
819 before_dml(
820 p_action => 'UPDATE',
821 x_rowid => x_rowid,
822 x_fac_wl_id => x_fac_wl_id,
823 x_person_id => x_person_id,
824 x_calendar_cat => x_calendar_cat,
825 x_cal_type => x_cal_type,
826 x_ci_sequence_number => x_ci_sequence_number,
827 x_attribute_category => x_attribute_category,
828 x_attribute1 => x_attribute1,
829 x_attribute2 => x_attribute2,
830 x_attribute3 => x_attribute3,
831 x_attribute4 => x_attribute4,
832 x_attribute5 => x_attribute5,
833 x_attribute6 => x_attribute6,
834 x_attribute7 => x_attribute7,
835 x_attribute8 => x_attribute8,
836 x_attribute9 => x_attribute9,
837 x_attribute10 => x_attribute10,
838 x_attribute11 => x_attribute11,
839 x_attribute12 => x_attribute12,
840 x_attribute13 => x_attribute13,
841 x_attribute14 => x_attribute14,
842 x_attribute15 => x_attribute15,
843 x_attribute16 => x_attribute16,
844 x_attribute17 => x_attribute17,
845 x_attribute18 => x_attribute18,
846 x_attribute19 => x_attribute19,
847 x_attribute20 => x_attribute20,
848 x_creation_date => x_last_update_date,
849 x_created_by => x_last_updated_by,
850 x_last_update_date => x_last_update_date,
851 x_last_updated_by => x_last_updated_by,
852 x_last_update_login => x_last_update_login
853 );
854
855 UPDATE igs_ps_fac_wl
856 SET
857 person_id = new_references.person_id,
858 calendar_cat = new_references.calendar_cat,
859 cal_type = new_references.cal_type,
860 ci_sequence_number = new_references.ci_sequence_number,
861 attribute_category = new_references.attribute_category,
862 attribute1 = new_references.attribute1,
863 attribute2 = new_references.attribute2,
864 attribute3 = new_references.attribute3,
865 attribute4 = new_references.attribute4,
866 attribute5 = new_references.attribute5,
867 attribute6 = new_references.attribute6,
868 attribute7 = new_references.attribute7,
869 attribute8 = new_references.attribute8,
870 attribute9 = new_references.attribute9,
871 attribute10 = new_references.attribute10,
872 attribute11 = new_references.attribute11,
873 attribute12 = new_references.attribute12,
874 attribute13 = new_references.attribute13,
875 attribute14 = new_references.attribute14,
876 attribute15 = new_references.attribute15,
877 attribute16 = new_references.attribute16,
878 attribute17 = new_references.attribute17,
879 attribute18 = new_references.attribute18,
880 attribute19 = new_references.attribute19,
881 attribute20 = new_references.attribute20,
882 last_update_date = x_last_update_date,
883 last_updated_by = x_last_updated_by,
884 last_update_login = x_last_update_login
885 WHERE rowid = x_rowid;
886
887 IF (SQL%NOTFOUND) THEN
888 RAISE NO_DATA_FOUND;
889 END IF;
890
891 END update_row;
892
893
894 PROCEDURE add_row (
895 x_rowid IN OUT NOCOPY VARCHAR2,
896 x_fac_wl_id IN OUT NOCOPY NUMBER,
897 x_person_id IN NUMBER,
898 x_calendar_cat IN VARCHAR2,
899 x_cal_type IN VARCHAR2,
900 x_ci_sequence_number IN NUMBER,
901 x_attribute_category IN VARCHAR2,
902 x_attribute1 IN VARCHAR2,
903 x_attribute2 IN VARCHAR2,
904 x_attribute3 IN VARCHAR2,
905 x_attribute4 IN VARCHAR2,
906 x_attribute5 IN VARCHAR2,
907 x_attribute6 IN VARCHAR2,
908 x_attribute7 IN VARCHAR2,
909 x_attribute8 IN VARCHAR2,
910 x_attribute9 IN VARCHAR2,
911 x_attribute10 IN VARCHAR2,
912 x_attribute11 IN VARCHAR2,
913 x_attribute12 IN VARCHAR2,
914 x_attribute13 IN VARCHAR2,
915 x_attribute14 IN VARCHAR2,
916 x_attribute15 IN VARCHAR2,
917 x_attribute16 IN VARCHAR2,
918 x_attribute17 IN VARCHAR2,
919 x_attribute18 IN VARCHAR2,
920 x_attribute19 IN VARCHAR2,
921 x_attribute20 IN VARCHAR2,
922 x_mode IN VARCHAR2 DEFAULT 'R'
923 ) AS
924 /*
925 || Created By : kkillams
926 || Created On : 17-JAN-2002
927 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
928 || Known limitations, enhancements or remarks :
929 || Change History :
930 || Who When What
931 || (reverse chronological order - newest change first)
932 */
933 CURSOR c1 IS
934 SELECT rowid
935 FROM igs_ps_fac_wl
936 WHERE fac_wl_id = x_fac_wl_id;
937
938 BEGIN
939
940 OPEN c1;
941 FETCH c1 INTO x_rowid;
942 IF (c1%NOTFOUND) THEN
943 CLOSE c1;
944
945 insert_row (
946 x_rowid,
947 x_fac_wl_id,
948 x_person_id,
949 x_calendar_cat,
950 x_cal_type,
951 x_ci_sequence_number,
952 x_attribute_category,
953 x_attribute1,
954 x_attribute2,
955 x_attribute3,
956 x_attribute4,
957 x_attribute5,
958 x_attribute6,
959 x_attribute7,
960 x_attribute8,
961 x_attribute9,
962 x_attribute10,
963 x_attribute11,
964 x_attribute12,
965 x_attribute13,
966 x_attribute14,
967 x_attribute15,
968 x_attribute16,
969 x_attribute17,
970 x_attribute18,
971 x_attribute19,
972 x_attribute20,
973 x_mode
974 );
975 RETURN;
976 END IF;
977 CLOSE c1;
978
979 update_row (
980 x_rowid,
981 x_fac_wl_id,
982 x_person_id,
983 x_calendar_cat,
984 x_cal_type,
985 x_ci_sequence_number,
986 x_attribute_category,
987 x_attribute1,
988 x_attribute2,
989 x_attribute3,
990 x_attribute4,
991 x_attribute5,
992 x_attribute6,
993 x_attribute7,
994 x_attribute8,
995 x_attribute9,
996 x_attribute10,
997 x_attribute11,
998 x_attribute12,
999 x_attribute13,
1000 x_attribute14,
1001 x_attribute15,
1002 x_attribute16,
1003 x_attribute17,
1004 x_attribute18,
1005 x_attribute19,
1006 x_attribute20,
1007 x_mode
1008 );
1009
1010 END add_row;
1011
1012
1013 PROCEDURE delete_row (
1014 x_rowid IN VARCHAR2
1015 ) AS
1016 /*
1017 || Created By : kkillams
1018 || Created On : 17-JAN-2002
1019 || Purpose : Handles the DELETE DML logic for the table.
1020 || Known limitations, enhancements or remarks :
1021 || Change History :
1022 || Who When What
1023 || (reverse chronological order - newest change first)
1024 */
1025 BEGIN
1026
1027 before_dml (
1028 p_action => 'DELETE',
1029 x_rowid => x_rowid
1030 );
1031
1032 DELETE FROM igs_ps_fac_wl
1033 WHERE rowid = x_rowid;
1034
1035 IF (SQL%NOTFOUND) THEN
1036 RAISE NO_DATA_FOUND;
1037 END IF;
1038
1039 END delete_row;
1040
1041
1042 END igs_ps_fac_wl_pkg;