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