1 PACKAGE BODY igs_ad_room_pkg AS
2 /* $Header: IGSAIB5B.pls 115.15 2003/10/30 13:17:31 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_room_all%RowType;
5 new_references igs_ad_room_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_room_id IN NUMBER DEFAULT NULL,
12 x_building_id IN NUMBER DEFAULT NULL,
13 x_room_cd IN VARCHAR2 DEFAULT NULL,
14 x_description IN VARCHAR2 DEFAULT NULL,
15 x_primary_use_cd IN VARCHAR2 DEFAULT NULL,
16 x_capacity IN NUMBER DEFAULT NULL,
17 x_closed_ind IN VARCHAR2 DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) AS
24
25 /*************************************************************
26 Created By :hsahni
27 Date Created By :10-MAY-2000
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32
33 (reverse chronological order - newest change first)
34 ***************************************************************/
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_AD_ROOM_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 Close cur_old_ref_values;
51 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.org_id := x_org_id;
60 new_references.room_id := x_room_id;
61 new_references.building_id := x_building_id;
62 new_references.room_cd := x_room_cd;
63 new_references.description := x_description;
64 new_references.primary_use_cd := x_primary_use_cd;
65 new_references.capacity := x_capacity;
66 new_references.closed_ind := x_closed_ind;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77
78 END Set_Column_Values;
79
80 PROCEDURE Check_Constraints (
81 Column_Name IN VARCHAR2 DEFAULT NULL,
82 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
83 /*************************************************************
84 Created By :hsahni
85 Date Created By :10-MAY-2000
86 Purpose :
87 Know limitations, enhancements or remarks
88 Change History
89 Who When What
90
91 (reverse chronological order - newest change first)
92 ***************************************************************/
93
94 BEGIN
95
96 IF column_name IS NULL THEN
97 NULL;
98 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
99 new_references.closed_ind := column_value;
100 NULL;
101 END IF;
102
103
104
105 -- The following code checks for check constraints on the Columns.
106 IF Upper(Column_Name) = 'CLOSED_IND' OR
107 Column_Name IS NULL THEN
108 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
109 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
110 IGS_GE_MSG_STACK.ADD;
111 App_Exception.Raise_Exception;
112 END IF;
113 END IF;
114
115
116 END Check_Constraints;
117
118 PROCEDURE Check_Uniqueness AS
119 /*************************************************************
120 Created By :hsahni
121 Date Created By :10-MAY-2000
122 Purpose :
123 Know limitations, enhancements or remarks
124 Change History
125 Who When What
126
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 begin
131 IF Get_Uk_For_Validation (
132 new_references.building_id
133 ,new_references.room_cd
134 ) THEN
135 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
136 IGS_GE_MSG_STACK.ADD;
137 app_exception.raise_exception;
138 END IF;
139 END Check_Uniqueness ;
140 PROCEDURE Check_Parent_Existance AS
141 /*************************************************************
142 Created By :hsahni
143 Date Created By :10-MAY-2000
144 Purpose :
145 Know limitations, enhancements or remarks
146 Change History
147 Who When What
148
149 (reverse chronological order - newest change first)
150 ***************************************************************/
151
152 BEGIN
153
154 IF (((old_references.building_id = new_references.building_id)) OR
155 ((new_references.building_id IS NULL))) THEN
156 NULL;
157 ELSIF NOT Igs_Ad_Building_Pkg.Get_PK_For_Validation (
158 new_references.building_id ,
159 'N') THEN
160 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END IF;
164 if not IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ( 'PRIMARY_USE',
165 new_references.primary_use_cd ) 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
171
172 END Check_Parent_Existance;
173
174 PROCEDURE Check_Child_Existance IS
175 /*************************************************************
176 Created By :hsahni
177 Date Created By :10-MAY-2000
178 Purpose :
179 Know limitations, enhancements or remarks
180 Change History
181 Who When What
182
183 (reverse chronological order - newest change first)
184 ***************************************************************/
185
186 BEGIN
187
188 Igs_Ps_Unit_Location_Pkg.Get_FK_Igs_Ad_Room (
189 old_references.room_id
190 );
191
192 Igs_Ps_Usec_Occurs_Pkg.Get_FK_Igs_Ad_Room (
193 old_references.room_id
194 );
195
196 igs_ps_us_unsched_cl_pkg.Get_FK_Igs_Ad_Room (
197 old_references.room_id
198 );
199
200 igs_ps_usec_occurs_pkg.Get_FK_Igs_Ad_Room (
201 old_references.room_id
202 );
203 igs_ad_panel_dtls_pkg.get_fk_igs_ad_room(
204 old_references.room_id
205 );
206
207
208 END Check_Child_Existance;
209
210 FUNCTION Get_PK_For_Validation (
211 x_room_id IN NUMBER,
212 x_closed_ind IN VARCHAR2
213 ) RETURN BOOLEAN AS
214
215 /*************************************************************
216 Created By :hsahni
217 Date Created By :10-MAY-2000
218 Purpose :
219 Know limitations, enhancements or remarks
220 Change History
221 Who When What
222
223 (reverse chronological order - newest change first)
224 ***************************************************************/
225
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM igs_ad_room_all
229 WHERE room_id = x_room_id AND
230 closed_ind = NVL(x_closed_ind,closed_ind)
231 FOR UPDATE NOWAIT;
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 Return(TRUE);
242 ELSE
243 Close cur_rowid;
244 Return(FALSE);
245 END IF;
246 END Get_PK_For_Validation;
247
248 FUNCTION Get_UK_For_Validation (
249 x_building_id IN NUMBER,
250 x_room_cd IN VARCHAR2,
251 x_closed_ind IN VARCHAR2
252 ) RETURN BOOLEAN AS
253
254 /*************************************************************
255 Created By :hsahni
256 Date Created By :10-MAY-2000
257 Purpose :
258 Know limitations, enhancements or remarks
259 Change History
260 Who When What
261
262 (reverse chronological order - newest change first)
263 ***************************************************************/
264
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM igs_ad_room_all
268 WHERE building_id = x_building_id AND
269 room_cd = x_room_cd AND
270 ((l_rowid is null) or (rowid <> l_rowid)) AND
271 closed_ind = NVL(x_closed_ind,closed_ind)
272
273 ;
274 lv_rowid cur_rowid%RowType;
275
276 BEGIN
277
278 Open cur_rowid;
279 Fetch cur_rowid INTO lv_rowid;
280 IF (cur_rowid%FOUND) THEN
281 Close cur_rowid;
282 return (true);
283 ELSE
284 close cur_rowid;
285 return(false);
286 END IF;
287 END Get_UK_For_Validation ;
288 PROCEDURE GET_FK_Igs_Ad_Building (
289 x_building_id IN NUMBER
290 ) AS
291
292 /*************************************************************
293 Created By :hsahni
294 Date Created By :10-MAY-2000
295 Purpose :
296 Know limitations, enhancements or remarks
297 Change History
298 Who When What
299
300 (reverse chronological order - newest change first)
301 ***************************************************************/
302
303 CURSOR cur_rowid IS
304 SELECT rowid
305 FROM igs_ad_room_all
306 WHERE building_id = x_building_id ;
307
308 lv_rowid cur_rowid%RowType;
309
310 BEGIN
311
312 Open cur_rowid;
313 Fetch cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 Close cur_rowid;
316 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ROOM_BLDG_FK');
317 IGS_GE_MSG_STACK.ADD;
318 App_Exception.Raise_Exception;
319 Return;
320 END IF;
321 Close cur_rowid;
322
323 END Get_FK_Igs_Ad_Building;
324
325 PROCEDURE Before_DML (
326 p_action IN VARCHAR2,
327 x_rowid IN VARCHAR2 DEFAULT NULL,
328 x_org_id IN NUMBER DEFAULT NULL,
329 x_room_id IN NUMBER DEFAULT NULL,
330 x_building_id IN NUMBER DEFAULT NULL,
331 x_room_cd IN VARCHAR2 DEFAULT NULL,
332 x_description IN VARCHAR2 DEFAULT NULL,
333
334 x_primary_use_cd IN VARCHAR2 DEFAULT NULL,
335 x_capacity IN NUMBER DEFAULT NULL,
336 x_closed_ind IN VARCHAR2 DEFAULT NULL,
337 x_creation_date IN DATE DEFAULT NULL,
338 x_created_by IN NUMBER DEFAULT NULL,
339 x_last_update_date IN DATE DEFAULT NULL,
340 x_last_updated_by IN NUMBER DEFAULT NULL,
341 x_last_update_login IN NUMBER DEFAULT NULL
342 ) AS
343 /*************************************************************
344 Created By :hsahni
345 Date Created By :10-MAY-2000
346 Purpose :
347 Know limitations, enhancements or remarks
348 Change History
349 Who When What
350
351 (reverse chronological order - newest change first)
352 ***************************************************************/
353
354 BEGIN
355
356 Set_Column_Values (
357 p_action,
358 x_rowid,
359 x_org_id,
360 x_room_id,
361 x_building_id,
362 x_room_cd,
363 x_description,
364 x_primary_use_cd,
365 x_capacity,
366 x_closed_ind,
367 x_creation_date,
368 x_created_by,
369 x_last_update_date,
370 x_last_updated_by,
371 x_last_update_login
372 );
373
374 IF (p_action = 'INSERT') THEN
375 -- Call all the procedures related to Before Insert.
376 Null;
377 IF Get_Pk_For_Validation(
378 new_references.room_id) THEN
379 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 END IF;
383 Check_Uniqueness;
384 Check_Constraints;
385 Check_Parent_Existance;
386 ELSIF (p_action = 'UPDATE') THEN
387 -- Call all the procedures related to Before Update.
388 Null;
389 Check_Uniqueness;
390 Check_Constraints;
391 Check_Parent_Existance;
392 ELSIF (p_action = 'DELETE') THEN
393 -- Call all the procedures related to Before Delete.
394 Null;
395 Check_Child_Existance;
396 ELSIF (p_action = 'VALIDATE_INSERT') THEN
397 -- Call all the procedures related to Before Insert.
398 IF Get_PK_For_Validation (
399 new_references.room_id) THEN
400 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
401 IGS_GE_MSG_STACK.ADD;
402 App_Exception.Raise_Exception;
403 END IF;
404 Check_Uniqueness;
405 Check_Constraints;
406 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
407 Check_Uniqueness;
408 Check_Constraints;
409 ELSIF (p_action = 'VALIDATE_DELETE') THEN
410 Check_Child_Existance;
411 END IF;
412 l_rowid:=NULL;
413 END Before_DML;
414
415 PROCEDURE After_DML (
416 p_action IN VARCHAR2,
417 x_rowid IN VARCHAR2
418 ) IS
419 /*************************************************************
420 Created By :hsahni
421 Date Created By :10-MAY-2000
422 Purpose :
423 Know limitations, enhancements or remarks
424 Change History
425 Who When What
426
427 (reverse chronological order - newest change first)
428 ***************************************************************/
429
430 BEGIN
431
432 l_rowid := x_rowid;
433
434
435 IF (p_action = 'INSERT') THEN
436 -- Call all the procedures related to After Insert.
437 Null;
438 ELSIF (p_action = 'UPDATE') THEN
439 -- Call all the procedures related to After Update.
440 Null;
441 ELSIF (p_action = 'DELETE') THEN
442 -- Call all the procedures related to After Delete.
443 Null;
444 END IF;
445
446 l_rowid:=NULL;
447
448 END After_DML;
449
450 procedure INSERT_ROW (
451 X_ROWID in out NOCOPY VARCHAR2,
452 x_ORG_ID IN NUMBER,
453 x_ROOM_ID IN OUT NOCOPY NUMBER,
454 x_BUILDING_ID IN NUMBER,
455 x_ROOM_CD IN VARCHAR2,
456 x_DESCRIPTION IN VARCHAR2,
457 x_PRIMARY_USE_CD IN VARCHAR2,
458 x_CAPACITY IN NUMBER,
459 x_CLOSED_IND IN VARCHAR2,
460 X_MODE in VARCHAR2 default 'R'
461 ) AS
465 Purpose :
462 /*************************************************************
463 Created By :hsahni
464 Date Created By :10-MAY-2000
466 Know limitations, enhancements or remarks
467 Change History
468 Who When What
469
470 (reverse chronological order - newest change first)
471 ***************************************************************/
472
473 cursor C is select ROWID from IGS_AD_ROOM_ALL
474 where ROOM_ID= X_ROOM_ID
475 ;
476 X_LAST_UPDATE_DATE DATE ;
477 X_LAST_UPDATED_BY NUMBER ;
478 X_LAST_UPDATE_LOGIN NUMBER ;
479 begin
480 X_LAST_UPDATE_DATE := SYSDATE;
481 if(X_MODE = 'I') then
482 X_LAST_UPDATED_BY := 1;
483 X_LAST_UPDATE_LOGIN := 0;
484 elsif (X_MODE = 'R') then
485 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
486 if X_LAST_UPDATED_BY is NULL then
487 X_LAST_UPDATED_BY := -1;
488 end if;
489 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
490 if X_LAST_UPDATE_LOGIN is NULL then
491 X_LAST_UPDATE_LOGIN := -1;
492 end if;
493 else
494 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
495 IGS_GE_MSG_STACK.ADD;
496 app_exception.raise_exception;
497 end if;
498
499 x_ROOM_ID := -1;
500 Before_DML(
501 p_action=>'INSERT',
502 x_rowid=>X_ROWID,
503 x_org_id => igs_ge_gen_003.get_org_id,
504 x_room_id=>X_ROOM_ID,
505 x_building_id=>X_BUILDING_ID,
506 x_room_cd=>X_ROOM_CD,
507 x_description=>X_DESCRIPTION,
508 x_primary_use_cd=>X_PRIMARY_USE_CD,
509 x_capacity=>X_CAPACITY,
510 x_closed_ind=>X_CLOSED_IND,
511 x_creation_date=>X_LAST_UPDATE_DATE,
512 x_created_by=>X_LAST_UPDATED_BY,
513 x_last_update_date=>X_LAST_UPDATE_DATE,
514 x_last_updated_by=>X_LAST_UPDATED_BY,
515 x_last_update_login=>X_LAST_UPDATE_LOGIN);
516 insert into IGS_AD_ROOM_ALL (
517 ORG_ID,
518 ROOM_ID
519 ,BUILDING_ID
520 ,ROOM_CD
521 ,DESCRIPTION
522 ,PRIMARY_USE_CD
523 ,CAPACITY
524 ,CLOSED_IND
525 ,CREATION_DATE
526 ,CREATED_BY
527 ,LAST_UPDATE_DATE
528 ,LAST_UPDATED_BY
529 ,LAST_UPDATE_LOGIN
530 ) values (
531 NEW_REFERENCES.ORG_ID,
532 IGS_AD_ROOM_S.NEXTVAL
533 ,NEW_REFERENCES.BUILDING_ID
534 ,NEW_REFERENCES.ROOM_CD
535 ,NEW_REFERENCES.DESCRIPTION
536 ,NEW_REFERENCES.PRIMARY_USE_CD
537 ,NEW_REFERENCES.CAPACITY
538 ,NEW_REFERENCES.CLOSED_IND
539 ,X_LAST_UPDATE_DATE
540 ,X_LAST_UPDATED_BY
541
542 ,X_LAST_UPDATE_DATE
543 ,X_LAST_UPDATED_BY
544 ,X_LAST_UPDATE_LOGIN
545 )RETURNING ROOM_ID INTO x_ROOM_ID;
546 open c;
547 fetch c into X_ROWID;
548 if (c%notfound) then
549 close c;
550 raise no_data_found;
551 end if;
552 close c;
553 After_DML (
554 p_action => 'INSERT' ,
555 x_rowid => X_ROWID );
556 end INSERT_ROW;
557 procedure LOCK_ROW (
558 X_ROWID in VARCHAR2,
559 x_ROOM_ID IN NUMBER,
560 x_BUILDING_ID IN NUMBER,
561 x_ROOM_CD IN VARCHAR2,
562 x_DESCRIPTION IN VARCHAR2,
563 x_PRIMARY_USE_CD IN VARCHAR2,
564 x_CAPACITY IN NUMBER,
565 x_CLOSED_IND IN VARCHAR2 ) AS
566 /*************************************************************
567 Created By :hsahni
568 Date Created By :10-MAY-2000
569 Purpose :
570 Know limitations, enhancements or remarks
571 Change History
572 Who When What
573
574 (reverse chronological order - newest change first)
575 ***************************************************************/
576
577 cursor c1 is select
578 BUILDING_ID
579 , ROOM_CD
580 , DESCRIPTION
581 , PRIMARY_USE_CD
582 , CAPACITY
583 , CLOSED_IND
584 from IGS_AD_ROOM_ALL
585 where ROWID = X_ROWID
586 for update nowait;
587 tlinfo c1%rowtype;
588 begin
589 open c1;
590 fetch c1 into tlinfo;
591 if (c1%notfound) then
592 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
593 IGS_GE_MSG_STACK.ADD;
594 close c1;
595 app_exception.raise_exception;
596 return;
597 end if;
598 close c1;
599 if ( ( tlinfo.BUILDING_ID = X_BUILDING_ID)
600 AND (tlinfo.ROOM_CD = X_ROOM_CD)
601 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
602 AND (tlinfo.PRIMARY_USE_CD = X_PRIMARY_USE_CD)
603 AND (tlinfo.CAPACITY = X_CAPACITY)
604 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
605 ) then
606 null;
607 else
608 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
609 IGS_GE_MSG_STACK.ADD;
610 app_exception.raise_exception;
611 end if;
612 return;
613 end LOCK_ROW;
614 Procedure UPDATE_ROW (
615 X_ROWID in VARCHAR2,
616 x_ROOM_ID IN NUMBER,
620 x_PRIMARY_USE_CD IN VARCHAR2,
617 x_BUILDING_ID IN NUMBER,
618 x_ROOM_CD IN VARCHAR2,
619 x_DESCRIPTION IN VARCHAR2,
621 x_CAPACITY IN NUMBER,
622 x_CLOSED_IND IN VARCHAR2,
623 X_MODE in VARCHAR2 default 'R'
624 ) AS
625 /*************************************************************
626 Created By :hsahni
627 Date Created By :10-MAY-2000
628 Purpose :
629 Know limitations, enhancements or remarks
630 Change History
631 Who When What
632
633 (reverse chronological order - newest change first)
634 ***************************************************************/
635
636 X_LAST_UPDATE_DATE DATE ;
637 X_LAST_UPDATED_BY NUMBER ;
638 X_LAST_UPDATE_LOGIN NUMBER ;
639 begin
640 X_LAST_UPDATE_DATE := SYSDATE;
641
642 if(X_MODE = 'I') then
643 X_LAST_UPDATED_BY := 1;
644 X_LAST_UPDATE_LOGIN := 0;
645 elsif (X_MODE = 'R') then
646 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
647 if X_LAST_UPDATED_BY is NULL then
648 X_LAST_UPDATED_BY := -1;
649 end if;
650 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
651 if X_LAST_UPDATE_LOGIN is NULL then
652 X_LAST_UPDATE_LOGIN := -1;
653 end if;
654 else
655 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
656 IGS_GE_MSG_STACK.ADD;
657 app_exception.raise_exception;
658 end if;
659 Before_DML(
660 p_action=>'UPDATE',
661 x_rowid=>X_ROWID,
662 x_room_id=>X_ROOM_ID,
663 x_building_id=>X_BUILDING_ID,
664 x_room_cd=>X_ROOM_CD,
665 x_description=>X_DESCRIPTION,
666 x_primary_use_cd=>X_PRIMARY_USE_CD,
667 x_capacity=>X_CAPACITY,
668 x_closed_ind=>X_CLOSED_IND,
669 x_creation_date=>X_LAST_UPDATE_DATE,
670 x_created_by=>X_LAST_UPDATED_BY,
671 x_last_update_date=>X_LAST_UPDATE_DATE,
672 x_last_updated_by=>X_LAST_UPDATED_BY,
673 x_last_update_login=>X_LAST_UPDATE_LOGIN);
674 update IGS_AD_ROOM_ALL set
675 BUILDING_ID = NEW_REFERENCES.BUILDING_ID,
676 ROOM_CD = NEW_REFERENCES.ROOM_CD,
677 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
678 PRIMARY_USE_CD = NEW_REFERENCES.PRIMARY_USE_CD,
679 CAPACITY = NEW_REFERENCES.CAPACITY,
680 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
681 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
682 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
683 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
684 where ROWID = X_ROWID;
685 if (sql%notfound) then
686 raise no_data_found;
687 end if;
688
689 After_DML (
690 p_action => 'UPDATE' ,
691 x_rowid => X_ROWID
692 );
693 end UPDATE_ROW;
694 procedure ADD_ROW (
695 X_ROWID in out NOCOPY VARCHAR2,
696 X_ORG_ID in NUMBER,
697 x_ROOM_ID IN OUT NOCOPY NUMBER,
698 x_BUILDING_ID IN NUMBER,
699 x_ROOM_CD IN VARCHAR2,
700 x_DESCRIPTION IN VARCHAR2,
701 x_PRIMARY_USE_CD IN VARCHAR2,
702 x_CAPACITY IN NUMBER,
703 x_CLOSED_IND IN VARCHAR2,
704 X_MODE in VARCHAR2 default 'R'
705 ) AS
706 /*************************************************************
707 Created By :hsahni
708 Date Created By :10-MAY-2000
709 Purpose :
710 Know limitations, enhancements or remarks
711 Change History
712 Who When What
713
714 (reverse chronological order - newest change first)
715 ***************************************************************/
716
717 cursor c1 is select ROWID from IGS_AD_ROOM_ALL
718 where ROOM_ID= X_ROOM_ID
719 ;
720 begin
721 open c1;
722 fetch c1 into X_ROWID;
723 if (c1%notfound) then
724 close c1;
725 INSERT_ROW (
726 X_ROWID,
727 X_ORG_ID,
728 X_ROOM_ID,
729 X_BUILDING_ID,
730 X_ROOM_CD,
731 X_DESCRIPTION,
732 X_PRIMARY_USE_CD,
733 X_CAPACITY,
734 X_CLOSED_IND,
735 X_MODE );
736 return;
737 end if;
738 close c1;
739 UPDATE_ROW (
740 X_ROWID,
741 X_ROOM_ID,
742 X_BUILDING_ID,
743
744 X_ROOM_CD,
745 X_DESCRIPTION,
746 X_PRIMARY_USE_CD,
747 X_CAPACITY,
748 X_CLOSED_IND,
749 X_MODE );
750 end ADD_ROW;
751 procedure DELETE_ROW (
752 X_ROWID in VARCHAR2
753 ) AS
754 /*************************************************************
755 Created By :hsahni
756 Date Created By :10-MAY-2000
757 Purpose :
758 Know limitations, enhancements or remarks
759 Change History
760 Who When What
761
762 (reverse chronological order - newest change first)
763 ***************************************************************/
764
765 begin
766 Before_DML (
767 p_action => 'DELETE',
768 x_rowid => X_ROWID
769 );
770 delete from IGS_AD_ROOM_ALL
771 where ROWID = X_ROWID;
772 if (sql%notfound) then
773 raise no_data_found;
774 end if;
775 After_DML (
776 p_action => 'DELETE',
777 x_rowid => X_ROWID
778 );
779 end DELETE_ROW;
780 END igs_ad_room_pkg;
[Home] [Help]