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_PS_UNIT_LEVEL_PKG

Source


1 package body IGS_PS_UNIT_LEVEL_PKG as
2 /* $Header: IGSPI81B.pls 115.9 2003/10/16 08:30:32 nalkumar ship $ */
3 
4 
5   l_rowid VARCHAR2(25);
6   old_references IGS_PS_UNIT_LEVEL_ALL%RowType;
7   new_references IGS_PS_UNIT_LEVEL_ALL%RowType;
8 
9   PROCEDURE Set_Column_Values (
10     p_action IN VARCHAR2,
11     x_rowid IN VARCHAR2 DEFAULT NULL,
12     x_unit_level IN VARCHAR2 DEFAULT NULL,
13     x_description IN VARCHAR2 DEFAULT NULL,
14     x_wam_weighting IN NUMBER DEFAULT NULL,
15     x_closed_ind IN VARCHAR2 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     x_org_id IN NUMBER DEFAULT NULL
22   ) AS
23 
24     CURSOR cur_old_ref_values IS
25       SELECT   *
26       FROM     IGS_PS_UNIT_LEVEL_ALL
27       WHERE    rowid = x_rowid;
28 
29   BEGIN
30 
31     l_rowid := x_rowid;
32 
33     -- Code for setting the Old and New Reference Values.
34     -- Populate Old Values.
35     Open cur_old_ref_values;
36     Fetch cur_old_ref_values INTO old_references;
37     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
38       Close cur_old_ref_values;
39       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40       IGS_GE_MSG_STACK.ADD;
41       App_Exception.Raise_Exception;
42       Return;
43     END IF;
44     Close cur_old_ref_values;
45 
46     -- Populate New Values.
47     new_references.unit_level := x_unit_level;
48     new_references.description := x_description;
49     new_references.wam_weighting := x_wam_weighting;
50     new_references.closed_ind := x_closed_ind;
51     IF (p_action = 'UPDATE') THEN
52       new_references.creation_date := old_references.creation_date;
53       new_references.created_by := old_references.created_by;
54     ELSE
55       new_references.creation_date := x_creation_date;
56       new_references.created_by := x_created_by;
57     END IF;
58     new_references.last_update_date := x_last_update_date;
59     new_references.last_updated_by := x_last_updated_by;
60     new_references.last_update_login := x_last_update_login;
61     new_references.org_id := x_org_id;
62 
63   END Set_Column_Values;
64 
65 PROCEDURE Check_Constraints(
66 				Column_Name 	IN	VARCHAR2	DEFAULT NULL,
67 				Column_Value 	IN	VARCHAR2	DEFAULT NULL)
68 AS
69 BEGIN
70 
71 	IF Column_Name IS NULL Then
72 		NULL;
73 	ELSIF Upper(Column_Name)='CLOSED_IND' Then
74 		New_References.closed_ind := Column_Value;
75 	ELSIF Upper(Column_Name)='UNIT_LEVEL' Then
76 		New_References.unit_level := Column_Value;
77 	END IF;
78 
79 	IF Upper(Column_Name)='WAM_WEIGHTING' OR Column_Name IS NULL Then
80 		IF New_References.wam_weighting < 0 AND  New_References.wam_weighting > 9.999 Then
81 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
82       IGS_GE_MSG_STACK.ADD;
83 			        App_Exception.Raise_Exception;
84 		END IF;
85 	END IF;
86 
87 	IF Upper(Column_Name)='CLOSED_IND' OR Column_Name IS NULL Then
88 		IF new_references.closed_ind NOT IN ( 'Y' , 'N' ) Then
89 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
90       IGS_GE_MSG_STACK.ADD;
91 			        App_Exception.Raise_Exception;
92 		END IF;
93 	END IF;
94 
95 	IF Upper(Column_Name)='UNIT_LEVEL' OR Column_Name IS NULL Then
96 		IF New_References.Unit_Level <> UPPER(New_References.Unit_Level) Then
97 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
98       IGS_GE_MSG_STACK.ADD;
99 			        App_Exception.Raise_Exception;
100 		END IF;
101 	END IF;
102 
103 END Check_Constraints;
104 
105 
106   FUNCTION Get_PK_For_Validation (
107     x_unit_level IN VARCHAR2
108     ) RETURN BOOLEAN AS
109 
110     CURSOR cur_rowid IS
111       SELECT   rowid
112       FROM     IGS_PS_UNIT_LEVEL_ALL
113       WHERE    unit_level = x_unit_level;
114 
115     lv_rowid cur_rowid%RowType;
116 
117   BEGIN
118 
119     Open cur_rowid;
120     Fetch cur_rowid INTO lv_rowid;
121  IF (cur_rowid%FOUND) THEN
122 	Close cur_rowid;
123       Return(TRUE);
124     ELSE
125 	Close cur_rowid;
126       Return(FALSE);
127     END IF;
128 
129   END Get_PK_For_Validation;
130 
131   PROCEDURE Before_DML (
132     p_action IN VARCHAR2,
133     x_rowid IN VARCHAR2 DEFAULT NULL,
134     x_unit_level IN VARCHAR2 DEFAULT NULL,
135     x_description IN VARCHAR2 DEFAULT NULL,
136     x_wam_weighting IN NUMBER DEFAULT NULL,
137     x_closed_ind IN VARCHAR2 DEFAULT NULL,
138     x_creation_date IN DATE DEFAULT NULL,
139     x_created_by IN NUMBER DEFAULT NULL,
140     x_last_update_date IN DATE DEFAULT NULL,
141     x_last_updated_by IN NUMBER DEFAULT NULL,
142     x_last_update_login IN NUMBER DEFAULT NULL,
143     x_org_id IN NUMBER DEFAULT NULL
144   ) AS
145   BEGIN
146 
147     Set_Column_Values (
148       p_action,
149       x_rowid,
150       x_unit_level,
151       x_description,
152       x_wam_weighting,
153       x_closed_ind,
154       x_creation_date,
155       x_created_by,
156       x_last_update_date,
157       x_last_updated_by,
158       x_last_update_login,
159       x_org_id
160     );
161 
162     IF (p_action = 'INSERT') THEN
163       -- Call all the procedures related to Before Insert.
164 	  IF Get_PK_For_Validation (New_References.unit_level) THEN
165 		Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
166                 IGS_GE_MSG_STACK.ADD;
167 		App_Exception.Raise_Exception;
168 	   END IF;
169 	   Check_Constraints;
170     ELSIF (p_action = 'UPDATE') THEN
171       -- Call all the procedures related to Before Update.
172 	   Check_Constraints;
173     ELSIF (p_action = 'VALIDATE_INSERT') THEN
174 	   IF Get_PK_For_Validation (New_References.unit_level) THEN
175              Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
176              IGS_GE_MSG_STACK.ADD;
177 	     App_Exception.Raise_Exception;
178 	   END IF;
179 	   Check_Constraints;
180    ELSIF (p_action = 'VALIDATE_UPDATE') THEN
181 	   Check_Constraints;
182    END IF;
183 
184   END Before_DML;
185 
186   PROCEDURE After_DML (
187     p_action IN VARCHAR2,
188     x_rowid IN VARCHAR2
189   ) AS
190   BEGIN
191 
192     l_rowid := x_rowid;
193 
194 
195   END After_DML;
196 
197 
198 procedure INSERT_ROW (
199   X_ROWID in out NOCOPY VARCHAR2,
200   X_UNIT_LEVEL in VARCHAR2,
201   X_DESCRIPTION in VARCHAR2,
202   X_WAM_WEIGHTING in NUMBER,
203   X_CLOSED_IND in VARCHAR2,
204   X_MODE in VARCHAR2 default 'R',
205   X_ORG_ID in NUMBER
206   ) AS
207     cursor C is select ROWID from IGS_PS_UNIT_LEVEL_ALL
208       where UNIT_LEVEL = X_UNIT_LEVEL;
209     X_LAST_UPDATE_DATE DATE;
210     X_LAST_UPDATED_BY NUMBER;
211     X_LAST_UPDATE_LOGIN NUMBER;
212 begin
213   X_LAST_UPDATE_DATE := SYSDATE;
214   if(X_MODE = 'I') then
215     X_LAST_UPDATED_BY := 1;
216     X_LAST_UPDATE_LOGIN := 0;
217   elsif (X_MODE = 'R') then
218     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
219     if X_LAST_UPDATED_BY is NULL then
220       X_LAST_UPDATED_BY := -1;
221     end if;
222     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
223     if X_LAST_UPDATE_LOGIN is NULL then
224       X_LAST_UPDATE_LOGIN := -1;
225     end if;
226   else
227     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
228       IGS_GE_MSG_STACK.ADD;
229     app_exception.raise_exception;
230   end if;
231 
232   Before_DML(
233   p_action => 'INSERT',
234   x_rowid => X_ROWID,
235   x_unit_level => X_UNIT_LEVEL,
236   x_description => X_DESCRIPTION,
237   x_wam_weighting => X_WAM_WEIGHTING,
238   x_closed_ind => NVL(X_CLOSED_IND,'N'),
239   x_creation_date => X_LAST_UPDATE_DATE,
240   x_created_by => X_LAST_UPDATED_BY,
241   x_last_update_date => X_LAST_UPDATE_DATE,
242   x_last_updated_by => X_LAST_UPDATED_BY,
243   x_last_update_login => X_LAST_UPDATE_LOGIN,
244   x_org_id => igs_ge_gen_003.get_org_id
245   );
246 
247   insert into IGS_PS_UNIT_LEVEL_ALL (
248     UNIT_LEVEL,
249     DESCRIPTION,
250     WAM_WEIGHTING,
251     CLOSED_IND,
252     CREATION_DATE,
253     CREATED_BY,
254     LAST_UPDATE_DATE,
255     LAST_UPDATED_BY,
256     LAST_UPDATE_LOGIN,
257     ORG_ID
258   ) values (
259     NEW_REFERENCES.UNIT_LEVEL,
260     NEW_REFERENCES.DESCRIPTION,
261     NEW_REFERENCES.WAM_WEIGHTING,
262     NEW_REFERENCES.CLOSED_IND,
263     X_LAST_UPDATE_DATE,
264     X_LAST_UPDATED_BY,
265     X_LAST_UPDATE_DATE,
266     X_LAST_UPDATED_BY,
267     X_LAST_UPDATE_LOGIN,
268     NEW_REFERENCES.ORG_ID
269   );
270 
271   open c;
272   fetch c into X_ROWID;
273   if (c%notfound) then
274     close c;
275     raise no_data_found;
276   end if;
277   close c;
278   After_DML (
279      p_action => 'INSERT',
280      x_rowid => X_ROWID
281     );
282 
283 end INSERT_ROW;
284 
285 procedure LOCK_ROW (
286   X_ROWID IN VARCHAR2,
287   X_UNIT_LEVEL in VARCHAR2,
288   X_DESCRIPTION in VARCHAR2,
289   X_WAM_WEIGHTING in NUMBER,
290   X_CLOSED_IND in VARCHAR2
291 
292 ) AS
293   cursor c1 is select
294       DESCRIPTION,
295       WAM_WEIGHTING,
296       CLOSED_IND
297 
298     from IGS_PS_UNIT_LEVEL_ALL
299     where ROWID = X_ROWID
300     for update nowait;
301   tlinfo c1%rowtype;
302 
303 begin
304   open c1;
305   fetch c1 into tlinfo;
306   if (c1%notfound) then
307     close c1;
308     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
309       IGS_GE_MSG_STACK.ADD;
310     app_exception.raise_exception;
311     return;
312   end if;
313   close c1;
314 
315   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
316       AND ((tlinfo.WAM_WEIGHTING = X_WAM_WEIGHTING)
317            OR ((tlinfo.WAM_WEIGHTING is null)
318                AND (X_WAM_WEIGHTING is null)))
319       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
320 
321   ) then
322     null;
323   else
324     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
325       IGS_GE_MSG_STACK.ADD;
326     app_exception.raise_exception;
327   end if;
328   return;
329 end LOCK_ROW;
330 
331 procedure UPDATE_ROW (
332   X_ROWID IN VARCHAR2,
333   X_UNIT_LEVEL in VARCHAR2,
334   X_DESCRIPTION in VARCHAR2,
335   X_WAM_WEIGHTING in NUMBER,
336   X_CLOSED_IND in VARCHAR2,
337   X_MODE in VARCHAR2 default 'R'
338 
339   ) AS
340     X_LAST_UPDATE_DATE DATE;
341     X_LAST_UPDATED_BY NUMBER;
342     X_LAST_UPDATE_LOGIN NUMBER;
343 begin
344   X_LAST_UPDATE_DATE := SYSDATE;
345   if(X_MODE = 'I') then
346     X_LAST_UPDATED_BY := 1;
347     X_LAST_UPDATE_LOGIN := 0;
348   elsif (X_MODE = 'R') then
349     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
350     if X_LAST_UPDATED_BY is NULL then
351       X_LAST_UPDATED_BY := -1;
352     end if;
353     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
354     if X_LAST_UPDATE_LOGIN is NULL then
355       X_LAST_UPDATE_LOGIN := -1;
356     end if;
357   else
358     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
359       IGS_GE_MSG_STACK.ADD;
360     app_exception.raise_exception;
361   end if;
362 
363   Before_DML(
364   p_action => 'UPDATE',
365   x_rowid => X_ROWID,
366   x_unit_level => X_UNIT_LEVEL,
367   x_description => X_DESCRIPTION,
368   x_wam_weighting => X_WAM_WEIGHTING,
369   x_closed_ind => X_CLOSED_IND,
370   x_creation_date => X_LAST_UPDATE_DATE,
371   x_created_by => X_LAST_UPDATED_BY,
372   x_last_update_date => X_LAST_UPDATE_DATE,
373   x_last_updated_by => X_LAST_UPDATED_BY,
374   x_last_update_login => X_LAST_UPDATE_LOGIN
375 
376   );
377   update IGS_PS_UNIT_LEVEL_ALL set
378     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
379     WAM_WEIGHTING = NEW_REFERENCES.WAM_WEIGHTING,
380     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
381     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
382     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
383     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
384 
385     where ROWID = X_ROWID
386   ;
387   if (sql%notfound) then
388     raise no_data_found;
389   end if;  After_DML (
390      p_action => 'UPDATE',
391      x_rowid => X_ROWID
392     );
393 end UPDATE_ROW;
394 
395 procedure ADD_ROW (
396   X_ROWID in out NOCOPY VARCHAR2,
397   X_UNIT_LEVEL in VARCHAR2,
398   X_DESCRIPTION in VARCHAR2,
399   X_WAM_WEIGHTING in NUMBER,
400   X_CLOSED_IND in VARCHAR2,
401   X_MODE in VARCHAR2 default 'R',
402   X_ORG_ID in NUMBER
403     ) AS
404   cursor c1 is select rowid from IGS_PS_UNIT_LEVEL_ALL
405      where UNIT_LEVEL = X_UNIT_LEVEL
406   ;
407 begin
408   open c1;
409   fetch c1 into X_ROWID;
410   if (c1%notfound) then
411     close c1;
412     INSERT_ROW (
413      X_ROWID,
414      X_UNIT_LEVEL,
415      X_DESCRIPTION,
416      X_WAM_WEIGHTING,
417      X_CLOSED_IND,
418      X_MODE,
419      X_ORG_ID);
420     return;
421   end if;
422   close c1;
423   UPDATE_ROW (
424    X_ROWID,
425    X_UNIT_LEVEL,
426    X_DESCRIPTION,
427    X_WAM_WEIGHTING,
428    X_CLOSED_IND,
429    X_MODE
430    );
431 end ADD_ROW;
432 
433 
434 end IGS_PS_UNIT_LEVEL_PKG;