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