eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_OR_UNIT_LOC_PKG

Source


1 package body IGS_OR_UNIT_LOC_PKG AS
2  /* $Header: IGSOI13B.pls 115.7 2003/10/30 13:29:48 rghosh ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_OR_UNIT_LOC%RowType;
5   new_references IGS_OR_UNIT_LOC%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
11     x_start_dt IN DATE DEFAULT NULL,
12     x_location_cd IN VARCHAR2 DEFAULT NULL,
13     x_creation_date IN DATE DEFAULT NULL,
14     x_created_by IN NUMBER DEFAULT NULL,
15     x_last_update_date IN DATE DEFAULT NULL,
16     x_last_updated_by IN NUMBER DEFAULT NULL,
17     x_last_update_login IN NUMBER DEFAULT NULL
18   ) AS
19     CURSOR cur_old_ref_values IS
20       SELECT   *
21       FROM     IGS_OR_UNIT_LOC
22       WHERE    rowid = x_rowid;
23   BEGIN
24     l_rowid := x_rowid;
25     -- Code for setting the Old and New Reference Values.
26     -- Populate Old Values.
27     Open cur_old_ref_values;
28     Fetch cur_old_ref_values INTO old_references;
29     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
30       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
31       IGS_GE_MSG_STACK.ADD;
32       App_Exception.Raise_Exception;
33       Close cur_old_ref_values;
34       Return;
35     END IF;
36     Close cur_old_ref_values;
37     -- Populate New Values.
38     new_references.org_unit_cd := x_org_unit_cd;
39     new_references.start_dt := x_start_dt;
40     new_references.location_cd := x_location_cd;
41     IF (p_action = 'UPDATE') THEN
42       new_references.creation_date := old_references.creation_date;
43       new_references.created_by := old_references.created_by;
44     ELSE
45       new_references.creation_date := x_creation_date;
46       new_references.created_by := x_created_by;
47     END IF;
48     new_references.last_update_date := x_last_update_date;
49     new_references.last_updated_by := x_last_updated_by;
50     new_references.last_update_login := x_last_update_login;
51   END Set_Column_Values;
52   -- Trigger description :-
53   -- "OSS_TST".trg_oul_br_iu
54   -- BEFORE INSERT OR UPDATE
55   -- ON IGS_OR_UNIT_LOC
56   -- FOR EACH ROW
57 
58   PROCEDURE BeforeRowInsertUpdate1(
59     p_inserting IN BOOLEAN DEFAULT FALSE,
60     p_updating IN BOOLEAN DEFAULT FALSE,
61     p_deleting IN BOOLEAN DEFAULT FALSE
62     ) AS
63 	v_message_name   VARCHAR2(30);
64   BEGIN
65 	IF p_inserting THEN
66 	-- As part of the bug# 1956374 changed to the below call from IGS_OR_VAL_OUL.crsp_val_loc_cd
67 		IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
68 				new_references.location_cd,
69   						v_message_name) = FALSE THEN
70 			Fnd_Message.Set_Name('IGS',v_message_name);
71 			IGS_GE_MSG_STACK.ADD;
72 			App_Exception.Raise_Exception ;
73 		END IF;
74 	END IF;
75   END BeforeRowInsertUpdate1;
76 
77   PROCEDURE Check_Parent_Existance AS
78   BEGIN
79     IF (((old_references.location_cd = new_references.location_cd)) OR
80         ((new_references.location_cd IS NULL))) THEN
81       NULL;
82     ELSE
83       IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
84         new_references.location_cd ,
85         'N' )THEN
86         Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
87         IGS_GE_MSG_STACK.ADD;
88         App_Exception.Raise_Exception;
89 
90 	  END IF;
91     END IF;
92     IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
93          (old_references.start_dt = new_references.start_dt)) OR
94         ((new_references.org_unit_cd IS NULL) OR
95          (new_references.start_dt IS NULL))) THEN
96       NULL;
97     ELSE
98       IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
99         new_references.org_unit_cd,
100         new_references.start_dt
101         )THEN
102       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
103       IGS_GE_MSG_STACK.ADD;
104       App_Exception.Raise_Exception;
105 
106 	  END IF;
107     END IF;
108   END Check_Parent_Existance;
109 
110   FUNCTION Get_PK_For_Validation (
111     x_org_unit_cd IN VARCHAR2,
112     x_start_dt IN DATE,
113     x_location_cd IN VARCHAR2
114     ) RETURN BOOLEAN AS
115     CURSOR cur_rowid IS
116       SELECT   rowid
117       FROM     IGS_OR_UNIT_LOC
118       WHERE    org_unit_cd = x_org_unit_cd
119       AND      start_dt = x_start_dt
120       AND      location_cd = x_location_cd
121       FOR UPDATE NOWAIT;
122     lv_rowid cur_rowid%RowType;
123   BEGIN
124     Open cur_rowid;
125     Fetch cur_rowid INTO lv_rowid;
126     IF (cur_rowid%FOUND) THEN
127         Close cur_rowid;
128  		RETURN(TRUE);
129 	ELSE
130         Close cur_rowid;
131 	    RETURN(FALSE);
132 	END IF;
133   END Get_PK_For_Validation;
134 
135   PROCEDURE GET_FK_IGS_AD_LOCATION (
136     x_location_cd IN VARCHAR2
137     ) AS
138     CURSOR cur_rowid IS
139       SELECT   rowid
140       FROM     IGS_OR_UNIT_LOC
141       WHERE    location_cd = x_location_cd ;
142     lv_rowid cur_rowid%RowType;
143   BEGIN
144     Open cur_rowid;
145     Fetch cur_rowid INTO lv_rowid;
146     IF (cur_rowid%FOUND) THEN
147       Close cur_rowid;
148       Fnd_Message.Set_Name ('IGS', 'IGS_OR_OUL_LOC_FK');
149       IGS_GE_MSG_STACK.ADD;
150       App_Exception.Raise_Exception;
151       Return;
152     END IF;
153     Close cur_rowid;
154   END GET_FK_IGS_AD_LOCATION;
155 
156   PROCEDURE GET_FK_IGS_OR_UNIT (
157     x_org_unit_cd IN VARCHAR2,
158     x_start_dt IN DATE
159     ) AS
160     CURSOR cur_rowid IS
161       SELECT   rowid
162       FROM     IGS_OR_UNIT_LOC
163       WHERE    org_unit_cd = x_org_unit_cd
164       AND      start_dt = x_start_dt ;
165     lv_rowid cur_rowid%RowType;
166   BEGIN
167     Open cur_rowid;
168     Fetch cur_rowid INTO lv_rowid;
169     IF (cur_rowid%FOUND) THEN
170       Close cur_rowid;
171       Fnd_Message.Set_Name ('IGS', 'IGS_OR_OUL_OU_FK');
172       IGS_GE_MSG_STACK.ADD;
173       App_Exception.Raise_Exception;
174       Return;
175     END IF;
176     Close cur_rowid;
177   END GET_FK_IGS_OR_UNIT;
178 
179   PROCEDURE Before_DML (
180     p_action IN VARCHAR2,
181     x_rowid IN VARCHAR2 DEFAULT NULL,
182     x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
183     x_start_dt IN DATE DEFAULT NULL,
184     x_location_cd IN VARCHAR2 DEFAULT NULL,
185     x_creation_date IN DATE DEFAULT NULL,
186     x_created_by IN NUMBER DEFAULT NULL,
187     x_last_update_date IN DATE DEFAULT NULL,
188     x_last_updated_by IN NUMBER DEFAULT NULL,
189     x_last_update_login IN NUMBER DEFAULT NULL
190   ) AS
191   BEGIN
192     Set_Column_Values (
193       p_action,
194       x_rowid,
195       x_org_unit_cd,
196       x_start_dt,
197       x_location_cd,
198       x_creation_date,
199       x_created_by,
200       x_last_update_date,
201       x_last_updated_by,
202       x_last_update_login
203     );
204     IF (p_action = 'INSERT') THEN
205       -- Call all the procedures related to Before Insert.
206       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
207 
208    IF  Get_PK_For_Validation (
209     new_references.org_unit_cd ,
210     new_references.start_dt ,
211     new_references.location_cd
212     	)THEN
213         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
214         IGS_GE_MSG_STACK.ADD;
215         App_Exception.Raise_Exception;
216 
217    END IF;
218       Check_Parent_Existance;
219 	  Check_Constraints ;
220     ELSIF (p_action = 'UPDATE') THEN
221       -- Call all the procedures related to Before Update.
222       BeforeRowInsertUpdate1 ( p_updating => TRUE );
223       Check_Constraints ;
224       Check_Parent_Existance;
225     ELSIF (p_action = 'DELETE') THEN
226       -- Call all the procedures related to Before Delete.
227       Null;
228 	ELSIF (p_action = 'VALIDATE_INSERT') THEN
229 
230    IF  Get_PK_For_Validation (
231     new_references.org_unit_cd ,
232     new_references.start_dt ,
233     new_references.location_cd
234     	)THEN
235         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
236         IGS_GE_MSG_STACK.ADD;
237         App_Exception.Raise_Exception;
238 
239    END IF;
240 	  Check_Constraints ;
241 
242 	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
243 
244      Check_Constraints ;
245 
246 	ELSIF (p_action = 'VALIDATE_DELETE') THEN
247 
248      NULL;
249 
250     END IF;
251   END Before_DML;
252 
253   PROCEDURE After_DML (
254     p_action IN VARCHAR2,
255     x_rowid IN VARCHAR2
256   ) AS
257   BEGIN
258     l_rowid := x_rowid;
259   END After_DML;
260 
261 procedure INSERT_ROW (
262   X_ROWID in out NOCOPY VARCHAR2,
263   X_ORG_UNIT_CD in VARCHAR2,
264   X_START_DT in DATE,
265   X_LOCATION_CD in VARCHAR2,
266   X_MODE in VARCHAR2 default 'R'
267   ) AS
268     cursor C is select ROWID from IGS_OR_UNIT_LOC
269       where ORG_UNIT_CD = X_ORG_UNIT_CD
270       and START_DT = X_START_DT
271       and LOCATION_CD = X_LOCATION_CD;
272     X_LAST_UPDATE_DATE DATE;
273     X_LAST_UPDATED_BY NUMBER;
274     X_LAST_UPDATE_LOGIN NUMBER;
275 begin
276   X_LAST_UPDATE_DATE := SYSDATE;
277   if(X_MODE = 'I') then
278     X_LAST_UPDATED_BY := 1;
279     X_LAST_UPDATE_LOGIN := 0;
280   elsif (X_MODE = 'R') then
281     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
282     if X_LAST_UPDATED_BY is NULL then
283       X_LAST_UPDATED_BY := -1;
284     end if;
285     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
286     if X_LAST_UPDATE_LOGIN is NULL then
287       X_LAST_UPDATE_LOGIN := -1;
288     end if;
289   else
290     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
291     IGS_GE_MSG_STACK.ADD;
292     app_exception.raise_exception;
293   end if;
294    Before_DML(
295     p_action=>'INSERT',
296     x_rowid=>X_ROWID,
297     x_location_cd=>X_LOCATION_CD,
298     x_org_unit_cd=>X_ORG_UNIT_CD,
299     x_start_dt=>X_START_DT,
300     x_creation_date=>X_LAST_UPDATE_DATE,
301     x_created_by=>X_LAST_UPDATED_BY,
302     x_last_update_date=>X_LAST_UPDATE_DATE,
303     x_last_updated_by=>X_LAST_UPDATED_BY,
304     x_last_update_login=>X_LAST_UPDATE_LOGIN
305     );
306   insert into IGS_OR_UNIT_LOC (
307     ORG_UNIT_CD,
308     START_DT,
309     LOCATION_CD,
310     CREATION_DATE,
311     CREATED_BY,
312     LAST_UPDATE_DATE,
313     LAST_UPDATED_BY,
314     LAST_UPDATE_LOGIN
315   ) values (
316     NEW_REFERENCES.ORG_UNIT_CD,
317     NEW_REFERENCES.START_DT,
318     NEW_REFERENCES.LOCATION_CD,
319     X_LAST_UPDATE_DATE,
320     X_LAST_UPDATED_BY,
321     X_LAST_UPDATE_DATE,
322     X_LAST_UPDATED_BY,
323     X_LAST_UPDATE_LOGIN
324   );
325   open c;
326   fetch c into X_ROWID;
327   if (c%notfound) then
328     close c;
329     raise no_data_found;
330   end if;
331   close c;
332   After_DML(
333     p_action=>'INSERT',
334     x_rowid=>X_ROWID
335     );
336 end INSERT_ROW;
337 
338 procedure LOCK_ROW (
339   X_ROWID in VARCHAR2,
340   X_ORG_UNIT_CD in VARCHAR2,
341   X_START_DT in DATE,
342   X_LOCATION_CD in VARCHAR2
343 ) AS
344   cursor c1 is select rowid
345     from IGS_OR_UNIT_LOC
346     where ROWID = X_ROWID
347     for update nowait ;
348   tlinfo c1%rowtype;
349 begin
350   open c1;
351   fetch c1 into tlinfo;
352   if (c1%notfound) then
353     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
354     app_exception.raise_exception;
355     close c1;
356     return;
357   end if;
358   close c1;
359   return;
360 end LOCK_ROW;
361 
362 procedure DELETE_ROW (
363     X_ROWID in VARCHAR2
364     ) AS
365 begin
366   Before_DML(
367    p_action=>'DELETE',
368    x_rowid=>X_ROWID
369    );
370   delete from IGS_OR_UNIT_LOC
371   where ROWID = X_ROWID ;
372   if (sql%notfound) then
373     raise no_data_found;
374   end if;
375   After_DML(
376     p_action=>'DELETE',
377     x_rowid=>X_ROWID
378     );
379 end DELETE_ROW;
380 
381 procedure Check_Constraints (
382   Column_Name in VARCHAR2 DEFAULT NULL ,
383   Column_Value in VARCHAR2 DEFAULT NULL
384   ) AS
385   /*----------------------------------------------------------------------------
386   ||  Created By : pkpatel
387   ||  Created On :
388   ||  Purpose :
389   ||  Known limitations, enhancements or remarks :
390   ||  Change History :
391   ||  Who             When            What
392   ||  (reverse chronological order - newest change first)
393   ||  pkpatel    29-JUL-2002   Bug No: 2461744
394   ||                           Removed the upper check constraint on org_unit_cd
395   ----------------------------------------------------------------------------*/
396 
397  begin
398 
399 IF Column_Name is null THEN
400   NULL;
401 ELSIF upper(Column_name) = 'LOCATION_CD' THEN
402   new_references.LOCATION_CD:= COLUMN_VALUE ;
403 END IF ;
404 
405 IF upper(Column_name) = 'LOCATION_CD' OR COLUMN_NAME IS NULL THEN
406   IF new_references.LOCATION_CD <> upper(new_references.LOCATION_CD) then
407     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
408     IGS_GE_MSG_STACK.ADD;
409     App_Exception.Raise_Exception ;
410   END IF;
411 
412 END IF ;
413 
414 end Check_Constraints ;
415 
416 end IGS_OR_UNIT_LOC_PKG;