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