1 PACKAGE BODY igs_ps_uso_facility_pkg AS
2 /* $Header: IGSPI2OB.pls 120.1 2005/06/29 03:27:25 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_uso_facility%ROWTYPE;
6 new_references igs_ps_uso_facility%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_uso_facility_id IN NUMBER DEFAULT NULL,
12 x_unit_section_occurrence_id IN NUMBER DEFAULT NULL,
13 x_facility_code IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 25-MAY-2001
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_PS_USO_FACILITY
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
44 CLOSE cur_old_ref_values;
45 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
46 igs_ge_msg_stack.add;
47 app_exception.raise_exception;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.uso_facility_id := x_uso_facility_id;
54 new_references.unit_section_occurrence_id := x_unit_section_occurrence_id;
55 new_references.facility_code := x_facility_code;
56
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END set_column_values;
70
71
72 PROCEDURE check_uniqueness AS
73 /*
74 || Created By : [email protected]
75 || Created On : 25-MAY-2001
76 || Purpose : Handles the Unique Constraint logic defined for the columns.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 */
82 BEGIN
83
84 IF ( get_uk_for_validation (
85 new_references.unit_section_occurrence_id,
86 new_references.facility_code
87 )
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 : [email protected]
100 || Created On : 25-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.unit_section_occurrence_id = new_references.unit_section_occurrence_id)) OR
110 ((new_references.unit_section_occurrence_id IS NULL))) THEN
111 NULL;
112 ELSIF NOT igs_ps_usec_occurs_pkg.get_pk_for_validation (
113 new_references.unit_section_occurrence_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.facility_code = new_references.facility_code)) OR
121 ((new_references.facility_code IS NULL))) THEN
122 NULL;
123 ELSIF NOT igs_ps_media_equip_pkg.get_pk_for_validation (
124 new_references.facility_code
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 END check_parent_existance;
132
133
134 FUNCTION get_pk_for_validation (
135 x_uso_facility_id IN NUMBER
136 ) RETURN BOOLEAN AS
137 /*
138 || Created By : [email protected]
139 || Created On : 25-MAY-2001
140 || Purpose : Validates the Primary Key of the table.
141 || Known limitations, enhancements or remarks :
142 || Change History :
143 || Who When What
144 || (reverse chronological order - newest change first)
145 */
146 CURSOR cur_rowid IS
147 SELECT rowid
148 FROM igs_ps_uso_facility
149 WHERE uso_facility_id = x_uso_facility_id
150 FOR UPDATE NOWAIT;
151
152 lv_rowid cur_rowid%RowType;
153
154 BEGIN
155
156 OPEN cur_rowid;
157 FETCH cur_rowid INTO lv_rowid;
158 IF (cur_rowid%FOUND) THEN
159 CLOSE cur_rowid;
160 RETURN(TRUE);
161 ELSE
162 CLOSE cur_rowid;
163 RETURN(FALSE);
164 END IF;
165
166 END get_pk_for_validation;
167
168
169 FUNCTION get_uk_for_validation (
170 x_unit_section_occurrence_id IN NUMBER,
171 x_facility_code IN VARCHAR2
172 ) RETURN BOOLEAN AS
173 /*
174 || Created By : [email protected]
175 || Created On : 25-MAY-2001
176 || Purpose : Validates the Unique Keys of the table.
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || (reverse chronological order - newest change first)
181 */
182 CURSOR cur_rowid IS
183 SELECT rowid
184 FROM igs_ps_uso_facility
185 WHERE unit_section_occurrence_id = x_unit_section_occurrence_id
186 AND facility_code = x_facility_code
187 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
188
189 lv_rowid cur_rowid%RowType;
190
191 BEGIN
192
193 OPEN cur_rowid;
194 FETCH cur_rowid INTO lv_rowid;
195 IF (cur_rowid%FOUND) THEN
196 CLOSE cur_rowid;
197 RETURN (true);
198 ELSE
199 CLOSE cur_rowid;
200 RETURN(FALSE);
201 END IF;
202
203 END get_uk_for_validation ;
204
205
206 PROCEDURE get_fk_igs_ps_usec_occurs (
207 x_unit_section_occurrence_id IN NUMBER
208 ) AS
209 /*
210 || Created By : [email protected]
211 || Created On : 25-MAY-2001
212 || Purpose : Validates the Foreign Keys for the table.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_ps_uso_facility
221 WHERE ((unit_section_occurrence_id = x_unit_section_occurrence_id));
222
223 lv_rowid cur_rowid%RowType;
224
225 BEGIN
226
227 OPEN cur_rowid;
228 FETCH cur_rowid INTO lv_rowid;
229 IF (cur_rowid%FOUND) THEN
230 CLOSE cur_rowid;
231 fnd_message.set_name ('IGS', 'IGS_PS_USO_USOF_FK1');
232 igs_ge_msg_stack.add;
233 app_exception.raise_exception;
234 RETURN;
235 END IF;
236 CLOSE cur_rowid;
237
238 END get_fk_igs_ps_usec_occurs;
239
240
241 PROCEDURE get_fk_igs_ps_media_equip (
242 x_media_code IN VARCHAR2
243 ) AS
244 /*
245 || Created By : [email protected]
246 || Created On : 25-MAY-2001
247 || Purpose : Validates the Foreign Keys for the table.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM igs_ps_uso_facility
256 WHERE ((facility_code = x_media_code));
257
258 lv_rowid cur_rowid%RowType;
259
260 BEGIN
261
262 OPEN cur_rowid;
263 FETCH cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 CLOSE cur_rowid;
266 fnd_message.set_name ('IGS', 'IGS_PS_ME_USOF_FK2');
267 igs_ge_msg_stack.add;
268 app_exception.raise_exception;
269 RETURN;
270 END IF;
271 CLOSE cur_rowid;
272
273 END get_fk_igs_ps_media_equip;
274
275
276 PROCEDURE before_dml (
277 p_action IN VARCHAR2,
278 x_rowid IN VARCHAR2 DEFAULT NULL,
279 x_uso_facility_id IN NUMBER DEFAULT NULL,
280 x_unit_section_occurrence_id IN NUMBER DEFAULT NULL,
281 x_facility_code IN VARCHAR2 DEFAULT NULL,
282 x_creation_date IN DATE DEFAULT NULL,
283 x_created_by IN NUMBER DEFAULT NULL,
284 x_last_update_date IN DATE DEFAULT NULL,
285 x_last_updated_by IN NUMBER DEFAULT NULL,
286 x_last_update_login IN NUMBER DEFAULT NULL
287 ) AS
288 /*
289 || Created By : [email protected]
290 || Created On : 25-MAY-2001
291 || Purpose : Initialises the columns, Checks Constraints, Calls the
292 || Trigger Handlers for the table, before any DML operation.
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 */
298 BEGIN
299
300 set_column_values (
301 p_action,
302 x_rowid,
303 x_uso_facility_id,
304 x_unit_section_occurrence_id,
305 x_facility_code,
306 x_creation_date,
307 x_created_by,
308 x_last_update_date,
309 x_last_updated_by,
310 x_last_update_login
311 );
312
313 IF (p_action = 'INSERT') THEN
314 -- Call all the procedures related to Before Insert.
315 IF ( get_pk_for_validation(
316 new_references.uso_facility_id
317 )
318 ) THEN
319 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
320 igs_ge_msg_stack.add;
321 app_exception.raise_exception;
322 END IF;
323 check_uniqueness;
324 check_parent_existance;
325 ELSIF (p_action = 'UPDATE') THEN
326 -- Call all the procedures related to Before Update.
327 check_uniqueness;
328 check_parent_existance;
329 ELSIF (p_action = 'VALIDATE_INSERT') THEN
330 -- Call all the procedures related to Before Insert.
331 IF ( get_pk_for_validation (
332 new_references.uso_facility_id
333 )
334 ) THEN
335 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
336 igs_ge_msg_stack.add;
337 app_exception.raise_exception;
338 END IF;
339 check_uniqueness;
340 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
341 check_uniqueness;
342 END IF;
343
344 l_rowid:=NULL;
345
346 END before_dml;
347
348 PROCEDURE After_DML (
349 p_action IN VARCHAR2,
350 x_rowid IN VARCHAR2
351 ) IS
352 /*************************************************************
353 Created By : sarakshi
354 Date Created By : 12-May-2005
355 Purpose : To update the scheduling status to USER_UPDATE when record is created or deleted for the occurrence
356 Know limitations, enhancements or remarks
357 Change History
358 Who When What
359 (reverse chronological order - newest change first)
360 ***************************************************************/
361 CURSOR c_occurs(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
362 SELECT uso.unit_section_occurrence_id
363 FROM igs_ps_usec_occurs_all uso
364 WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
365 AND uso.no_set_day_ind ='N'
366 AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
367
368 BEGIN
369
370 l_rowid := x_rowid;
371
372 IF (p_action = 'INSERT') THEN
373 --Update the schedule status of the occurrence to USER_UPDATE if inserting a record
374 FOR l_occurs_rec IN c_occurs(new_references.unit_section_occurrence_id) LOOP
375 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
376 END LOOP;
377
378 ELSIF (p_action = 'UPDATE') THEN
379 --Update the schedule status of the occurrence to USER_UPDATE if updating a record
380 FOR l_occurs_rec IN c_occurs(new_references.unit_section_occurrence_id) LOOP
381 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
382 END LOOP;
383
384
385 ELSIF (p_action = 'DELETE') THEN
386 --Update the schedule status of the occurrence to USER_UPDATE if updating a record
387 FOR l_occurs_rec IN c_occurs(old_references.unit_section_occurrence_id) LOOP
388 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
389 END LOOP;
390
391 END IF;
392
393 l_rowid:=NULL;
394 END After_DML;
395
396
397 PROCEDURE insert_row (
398 x_rowid IN OUT NOCOPY VARCHAR2,
399 x_uso_facility_id IN OUT NOCOPY NUMBER,
400 x_unit_section_occurrence_id IN NUMBER,
401 x_facility_code IN VARCHAR2,
402 x_mode IN VARCHAR2 DEFAULT 'R'
403 ) AS
404 /*
405 || Created By : [email protected]
406 || Created On : 25-MAY-2001
407 || Purpose : Handles the INSERT DML logic for the table.
408 || Known limitations, enhancements or remarks :
409 || Change History :
410 || Who When What
411 || (reverse chronological order - newest change first)
412 */
413 CURSOR c IS
414 SELECT rowid
415 FROM igs_ps_uso_facility
416 WHERE uso_facility_id = x_uso_facility_id;
417
418 x_last_update_date DATE;
419 x_last_updated_by NUMBER;
420 x_last_update_login NUMBER;
421
422 BEGIN
423
424 x_last_update_date := SYSDATE;
425 IF (x_mode = 'I') THEN
426 x_last_updated_by := 1;
427 x_last_update_login := 0;
428 ELSIF (x_mode = 'R') THEN
429 x_last_updated_by := fnd_global.user_id;
430 IF (x_last_updated_by IS NULL) THEN
431 x_last_updated_by := -1;
432 END IF;
433 x_last_update_login := fnd_global.login_id;
434 IF (x_last_update_login IS NULL) THEN
435 x_last_update_login := -1;
436 END IF;
437 ELSE
438 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
439 igs_ge_msg_stack.add;
440 app_exception.raise_exception;
441 END IF;
442
443 SELECT igs_ps_uso_facility_s.NEXTVAL
444 INTO x_uso_facility_id
445 FROM dual;
446
447 before_dml(
448 p_action => 'INSERT',
449 x_rowid => x_rowid,
450 x_uso_facility_id => x_uso_facility_id,
451 x_unit_section_occurrence_id => x_unit_section_occurrence_id,
452 x_facility_code => x_facility_code,
453 x_creation_date => x_last_update_date,
454 x_created_by => x_last_updated_by,
455 x_last_update_date => x_last_update_date,
456 x_last_updated_by => x_last_updated_by,
457 x_last_update_login => x_last_update_login
458 );
459
460 INSERT INTO igs_ps_uso_facility (
461 uso_facility_id,
462 unit_section_occurrence_id,
463 facility_code,
464 creation_date,
465 created_by,
466 last_update_date,
467 last_updated_by,
468 last_update_login
469 ) VALUES (
470 new_references.uso_facility_id,
471 new_references.unit_section_occurrence_id,
472 new_references.facility_code,
473 x_last_update_date,
474 x_last_updated_by,
475 x_last_update_date,
476 x_last_updated_by,
477 x_last_update_login
478 );
479
480 OPEN c;
481 FETCH c INTO x_rowid;
482 IF (c%NOTFOUND) THEN
483 CLOSE c;
484 RAISE NO_DATA_FOUND;
485 END IF;
486 CLOSE c;
487
488 After_DML (
489 p_action => 'INSERT' ,
490 x_rowid => X_ROWID );
491
492 END insert_row;
493
494
495 PROCEDURE lock_row (
496 x_rowid IN VARCHAR2,
497 x_uso_facility_id IN NUMBER,
498 x_unit_section_occurrence_id IN NUMBER,
499 x_facility_code IN VARCHAR2
500 ) AS
501 /*
502 || Created By : [email protected]
503 || Created On : 25-MAY-2001
504 || Purpose : Handles the LOCK mechanism for the table.
505 || Known limitations, enhancements or remarks :
506 || Change History :
507 || Who When What
508 || (reverse chronological order - newest change first)
509 */
510 CURSOR c1 IS
511 SELECT
512 unit_section_occurrence_id,
513 facility_code
514 FROM igs_ps_uso_facility
515 WHERE rowid = x_rowid
516 FOR UPDATE NOWAIT;
517
518 tlinfo c1%ROWTYPE;
519
520 BEGIN
521
522 OPEN c1;
523 FETCH c1 INTO tlinfo;
524 IF (c1%notfound) THEN
525 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
526 igs_ge_msg_stack.add;
527 CLOSE c1;
528 app_exception.raise_exception;
529 RETURN;
530 END IF;
531 CLOSE c1;
532
533 IF (
534 (tlinfo.unit_section_occurrence_id = x_unit_section_occurrence_id)
535 AND (tlinfo.facility_code = x_facility_code)
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_uso_facility_id IN NUMBER,
552 x_unit_section_occurrence_id IN NUMBER,
553 x_facility_code IN VARCHAR2,
554 x_mode IN VARCHAR2 DEFAULT 'R'
555 ) AS
556 /*
557 || Created By : [email protected]
558 || Created On : 25-MAY-2001
559 || Purpose : Handles the UPDATE DML logic for the table.
560 || Known limitations, enhancements or remarks :
561 || Change History :
562 || Who When What
563 || (reverse chronological order - newest change first)
564 */
565 x_last_update_date DATE ;
566 x_last_updated_by NUMBER;
567 x_last_update_login NUMBER;
568
569 BEGIN
570
571 x_last_update_date := SYSDATE;
572 IF (X_MODE = 'I') THEN
573 x_last_updated_by := 1;
574 x_last_update_login := 0;
575 ELSIF (x_mode = 'R') THEN
576 x_last_updated_by := fnd_global.user_id;
577 IF x_last_updated_by IS NULL THEN
578 x_last_updated_by := -1;
579 END IF;
580 x_last_update_login := fnd_global.login_id;
581 IF (x_last_update_login IS NULL) THEN
582 x_last_update_login := -1;
583 END IF;
584 ELSE
585 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
586 igs_ge_msg_stack.add;
587 app_exception.raise_exception;
588 END IF;
589
590 before_dml(
591 p_action => 'UPDATE',
592 x_rowid => x_rowid,
593 x_uso_facility_id => x_uso_facility_id,
594 x_unit_section_occurrence_id => x_unit_section_occurrence_id,
595 x_facility_code => x_facility_code,
596 x_creation_date => x_last_update_date,
597 x_created_by => x_last_updated_by,
598 x_last_update_date => x_last_update_date,
599 x_last_updated_by => x_last_updated_by,
600 x_last_update_login => x_last_update_login
601 );
602
603 UPDATE igs_ps_uso_facility
604 SET
605 unit_section_occurrence_id = new_references.unit_section_occurrence_id,
606 facility_code = new_references.facility_code,
607 last_update_date = x_last_update_date,
608 last_updated_by = x_last_updated_by,
609 last_update_login = x_last_update_login
610 WHERE rowid = x_rowid;
611
612 IF (SQL%NOTFOUND) THEN
613 RAISE NO_DATA_FOUND;
614 END IF;
615
616 After_DML (
617 p_action => 'UPDATE' ,
618 x_rowid => X_ROWID );
619
620 END update_row;
621
622
623 PROCEDURE add_row (
624 x_rowid IN OUT NOCOPY VARCHAR2,
625 x_uso_facility_id IN OUT NOCOPY NUMBER,
626 x_unit_section_occurrence_id IN NUMBER,
627 x_facility_code IN VARCHAR2,
628 x_mode IN VARCHAR2 DEFAULT 'R'
629 ) AS
630 /*
631 || Created By : [email protected]
632 || Created On : 25-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_uso_facility
642 WHERE uso_facility_id = x_uso_facility_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_uso_facility_id,
654 x_unit_section_occurrence_id,
655 x_facility_code,
656 x_mode
657 );
658 RETURN;
659 END IF;
660 CLOSE c1;
661
662 update_row (
663 x_rowid,
664 x_uso_facility_id,
665 x_unit_section_occurrence_id,
666 x_facility_code,
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 : [email protected]
678 || Created On : 25-MAY-2001
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_ps_uso_facility
693 WHERE rowid = x_rowid;
694
695 IF (SQL%NOTFOUND) THEN
696 RAISE NO_DATA_FOUND;
697 END IF;
698
699 After_DML (
700 p_action => 'DELETE' ,
701 x_rowid => X_ROWID );
702
703 END delete_row;
704
705
706 END igs_ps_uso_facility_pkg;
[Home] [Help]