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_CATEGORY_PKG

Source


1 package body IGS_PS_UNIT_CATEGORY_PKG as
2 /* $Header: IGSPI75B.pls 115.6 2002/11/29 02:37:17 nsidana ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_PS_UNIT_CATEGORY_ALL%RowType;
5   new_references IGS_PS_UNIT_CATEGORY_ALL%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_unit_cd IN VARCHAR2 DEFAULT NULL,
11     x_version_number IN NUMBER DEFAULT NULL,
12     x_unit_cat 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     x_org_id IN NUMBER DEFAULT NULL
19   ) AS
20 
21     CURSOR cur_old_ref_values IS
22       SELECT   *
23       FROM     IGS_PS_UNIT_CATEGORY_ALL
24       WHERE    rowid = x_rowid;
25 
26   BEGIN
27 
28     l_rowid := x_rowid;
29 
30     -- Code for setting the Old and New Reference Values.
31     -- Populate Old Values.
32     Open cur_old_ref_values;
33     Fetch cur_old_ref_values INTO old_references;
34     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
35       Close cur_old_ref_values;
36       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37       IGS_GE_MSG_STACK.ADD;
38       App_Exception.Raise_Exception;
39       Return;
40     END IF;
41     Close cur_old_ref_values;
42 
43     -- Populate New Values.
44     new_references.unit_cd := x_unit_cd;
45     new_references.version_number := x_version_number;
46     new_references.unit_cat := x_unit_cat;
47     IF (p_action = 'UPDATE') THEN
48       new_references.creation_date := old_references.creation_date;
49       new_references.created_by := old_references.created_by;
50     ELSE
51       new_references.creation_date := x_creation_date;
52       new_references.created_by := x_created_by;
53     END IF;
54     new_references.last_update_date := x_last_update_date;
55     new_references.last_updated_by := x_last_updated_by;
56     new_references.last_update_login := x_last_update_login;
57     new_references.org_id := x_org_id;
58 
59   END Set_Column_Values;
60 
61   PROCEDURE BeforeRowInsertUpdateDelete1(
62     p_inserting IN BOOLEAN DEFAULT FALSE,
63     p_updating IN BOOLEAN DEFAULT FALSE,
64     p_deleting IN BOOLEAN DEFAULT FALSE
65     ) AS
66 	v_unit_cd		IGS_PS_UNIT_CATEGORY_ALL.unit_cd%TYPE;
67 	v_version_number	IGS_PS_UNIT_CATEGORY_ALL.version_number%TYPE;
68 	v_message_name	VARCHAR2(30);
69   BEGIN
70 	-- Set variables.
71 	IF p_deleting THEN
72 		v_unit_cd := old_references.unit_cd;
73 		v_version_number := old_references.version_number;
74 	ELSE -- p_inserting or p_updating
75 		v_unit_cd := new_references.unit_cd;
76 		v_version_number := new_references.version_number;
77 	END IF;
78 	-- Validate the insert/update/delete.
79 	IF  IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
80 			v_unit_cd,
81 			v_version_number,
82 			v_message_name) = FALSE THEN
83 				Fnd_Message.Set_Name('IGS', v_message_name);
84 				IGS_GE_MSG_STACK.ADD;
85 				App_Exception.Raise_Exception;
86 	END IF;
87 	-- Validate IGS_PS_UNIT cat.  IGS_PS_UNIT cat is not updateable.
88 	IF p_inserting THEN
89 		IF IGS_PS_VAL_UC.crsp_val_uc_unit_cat (
90 				new_references.unit_cat,
91 				v_message_name) = FALSE THEN
92 					Fnd_Message.Set_Name('IGS', v_message_name);
93 					IGS_GE_MSG_STACK.ADD;
94 					App_Exception.Raise_Exception;
95 		END IF;
96 	END IF;
97 
98 
99   END BeforeRowInsertUpdateDelete1;
100 
101 PROCEDURE Check_Constraints(
102 				Column_Name 	IN	VARCHAR2	DEFAULT NULL,
103 				Column_Value 	IN	VARCHAR2	DEFAULT NULL)
104 AS
105 BEGIN
106 
107 	IF Column_Name IS NULL Then
108 		NULL;
109 	ELSIF Upper(Column_Name)='UNIT_CAT' Then
110 		New_References.Unit_Cat := Column_Value;
111 	ELSIF Upper(Column_Name)='UNIT_CD' Then
112 		New_References.Unit_Cd := Column_Value;
113 	END IF;
114 
115 	IF Upper(Column_Name)='UNIT_CAT' OR Column_Name IS NULL Then
116 		IF New_References.Unit_Cat <> UPPER(New_References.Unit_Cat) Then
117 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
118 			        IGS_GE_MSG_STACK.ADD;
119 			        App_Exception.Raise_Exception;
120 		END IF;
121 	END IF;
122 
123 	IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
124 		IF New_References.Unit_Cd <> UPPER(New_References.Unit_Cd) Then
125 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
126 			        IGS_GE_MSG_STACK.ADD;
127 			        App_Exception.Raise_Exception;
128 		END IF;
129 	END IF;
130 
131 END Check_Constraints;
132 
133   PROCEDURE Check_Parent_Existance AS
134   BEGIN
135 
136     IF (((old_references.unit_cat = new_references.unit_cat)) OR
137         ((new_references.unit_cat IS  NULL))) THEN
138       NULL;
139     ELSE
140       IF NOT IGS_PS_UNIT_CAT_PKG.Get_PK_For_Validation (
141         new_references.unit_cat) THEN
142 				  Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
143 				  IGS_GE_MSG_STACK.ADD;
144 			        App_Exception.Raise_Exception;
145 	END IF;
146     END IF;
147 
148     IF (((old_references.unit_cd = new_references.unit_cd) AND
149          (old_references.version_number = new_references.version_number)) OR
150         ((new_references.unit_cd IS NULL) OR
151          (new_references.version_number IS NULL))) THEN
152       NULL;
153     ELSE
154       IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
155         new_references.unit_cd,
156         new_references.version_number
157         ) THEN
158 				  Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
159 				  IGS_GE_MSG_STACK.ADD;
160 			        App_Exception.Raise_Exception;
161 	END IF;
162 
163     END IF;
164 
165   END Check_Parent_Existance;
166 
167   FUNCTION Get_PK_For_Validation (
168     x_unit_cd IN VARCHAR2,
169     x_version_number IN NUMBER,
170     x_unit_cat IN VARCHAR2
171     ) RETURN BOOLEAN AS
172 
173     CURSOR cur_rowid IS
174       SELECT   rowid
175       FROM     IGS_PS_UNIT_CATEGORY_ALL
176       WHERE    unit_cd = x_unit_cd
177       AND      version_number = x_version_number
178       AND      unit_cat = x_unit_cat
179       FOR UPDATE NOWAIT;
180 
181     lv_rowid cur_rowid%RowType;
182 
183   BEGIN
184 
185     Open cur_rowid;
186     Fetch cur_rowid INTO lv_rowid;
187     IF (cur_rowid%FOUND) THEN
188 	Close cur_rowid;
189       Return(TRUE);
190     ELSE
191 	Close cur_rowid;
192       Return(FALSE);
193     END IF;
194 
195   END Get_PK_For_Validation;
196 
197   PROCEDURE GET_FK_IGS_PS_UNIT_CAT (
198     x_unit_cat IN VARCHAR2
199     ) AS
200 
201     CURSOR cur_rowid IS
202       SELECT   rowid
203       FROM     IGS_PS_UNIT_CATEGORY_ALL
204       WHERE    unit_cat = x_unit_cat ;
205 
206     lv_rowid cur_rowid%RowType;
207 
208   BEGIN
209 
210     Open cur_rowid;
211     Fetch cur_rowid INTO lv_rowid;
212     IF (cur_rowid%FOUND) THEN
213       Close cur_rowid;
214       Fnd_Message.Set_Name ('IGS', 'IGS_PS_UC_UCA_FK');
215       IGS_GE_MSG_STACK.ADD;
216       App_Exception.Raise_Exception;
217       Return;
218     END IF;
219     Close cur_rowid;
220 
221   END GET_FK_IGS_PS_UNIT_CAT;
222 
223   PROCEDURE GET_FK_IGS_PS_UNIT_VER (
224     x_unit_cd IN VARCHAR2,
225     x_version_number IN NUMBER
226     ) AS
227 
228     CURSOR cur_rowid IS
229       SELECT   rowid
230       FROM     IGS_PS_UNIT_CATEGORY_ALL
231       WHERE    unit_cd = x_unit_cd
232       AND      version_number = x_version_number ;
233 
234     lv_rowid cur_rowid%RowType;
235 
236   BEGIN
237 
238     Open cur_rowid;
239     Fetch cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241       Close cur_rowid;
242       Fnd_Message.Set_Name ('IGS', 'IGS_PS_UC_UV_FK');
243       IGS_GE_MSG_STACK.ADD;
244       App_Exception.Raise_Exception;
245       Return;
246     END IF;
247     Close cur_rowid;
248 
249   END GET_FK_IGS_PS_UNIT_VER;
250 
251   PROCEDURE Before_DML (
252     p_action IN VARCHAR2,
253     x_rowid IN VARCHAR2 DEFAULT NULL,
254     x_unit_cd IN VARCHAR2 DEFAULT NULL,
255     x_version_number IN NUMBER DEFAULT NULL,
256     x_unit_cat IN VARCHAR2 DEFAULT NULL,
257     x_creation_date IN DATE DEFAULT NULL,
258     x_created_by IN NUMBER DEFAULT NULL,
259     x_last_update_date IN DATE DEFAULT NULL,
260     x_last_updated_by IN NUMBER DEFAULT NULL,
261     x_last_update_login IN NUMBER DEFAULT NULL,
262     x_org_id IN NUMBER DEFAULT NULL
263   ) AS
264   BEGIN
265 
266     Set_Column_Values (
267       p_action,
268       x_rowid,
269       x_unit_cd,
270       x_version_number,
271       x_unit_cat,
272       x_creation_date,
273       x_created_by,
274       x_last_update_date,
275       x_last_updated_by,
276       x_last_update_login ,
277       x_org_id
278     );
279 
280     IF (p_action = 'INSERT') THEN
281       -- Call all the procedures related to Before Insert.
282       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
283         IF Get_PK_For_Validation (New_References.unit_cd,
284 					    New_References.version_number ,
285 					    New_References.unit_cat) THEN
286 			Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
287 			IGS_GE_MSG_STACK.ADD;
288 		      App_Exception.Raise_Exception;
289 	   END IF;
290 	   Check_Constraints;
291         Check_Parent_Existance;
292     ELSIF (p_action = 'UPDATE') THEN
293       -- Call all the procedures related to Before Update.
294       BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
295       Check_Constraints;
296       Check_Parent_Existance;
297     ELSIF (p_action = 'DELETE') THEN
298       -- Call all the procedures related to Before Delete.
299       BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
300 	Check_Constraints;
301     ELSIF (p_action = 'VALIDATE_INSERT') THEN
302 	    IF Get_PK_For_Validation (New_References.unit_cd,
303 					    New_References.version_number ,
304 					    New_References.unit_cat) THEN
305 			Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
306 			IGS_GE_MSG_STACK.ADD;
307 		      App_Exception.Raise_Exception;
308 	   END IF;
309 	   Check_Constraints;
310    ELSIF (p_action = 'VALIDATE_UPDATE') THEN
311 	   Check_Constraints;
312 
313     END IF;
314 
315   END Before_DML;
316 
317   PROCEDURE After_DML (
318     p_action IN VARCHAR2,
319     x_rowid IN VARCHAR2
320   ) AS
321   BEGIN
322 
323     l_rowid := x_rowid;
324 
325 
326   END After_DML;
327 
328 procedure INSERT_ROW (
329   X_ROWID in out NOCOPY VARCHAR2,
330   X_UNIT_CD in VARCHAR2,
331   X_VERSION_NUMBER in NUMBER,
332   X_UNIT_CAT in VARCHAR2,
333   X_MODE in VARCHAR2 default 'R',
334   x_ORG_ID in NUMBER
335   ) AS
336     cursor C is select ROWID from IGS_PS_UNIT_CATEGORY_ALL
337       where UNIT_CD = X_UNIT_CD
338       and VERSION_NUMBER = X_VERSION_NUMBER
339       and UNIT_CAT = X_UNIT_CAT;
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   Before_DML(
363   p_action => 'INSERT',
364   x_rowid => X_ROWID,
365   x_unit_cd => X_UNIT_CD,
366   x_version_number => X_VERSION_NUMBER,
367   x_unit_cat => X_UNIT_CAT,
368   x_creation_date => X_LAST_UPDATE_DATE,
369   x_created_by => X_LAST_UPDATED_BY,
370   x_last_update_date => X_LAST_UPDATE_DATE,
371   x_last_updated_by => X_LAST_UPDATED_BY,
372   x_last_update_login => X_LAST_UPDATE_LOGIN,
373   x_org_id => igs_ge_gen_003.get_org_id
374   );
375 
376   insert into IGS_PS_UNIT_CATEGORY_ALL (
377     UNIT_CD,
378     VERSION_NUMBER,
379     UNIT_CAT,
380     CREATION_DATE,
381     CREATED_BY,
382     LAST_UPDATE_DATE,
383     LAST_UPDATED_BY,
384     LAST_UPDATE_LOGIN,
385     ORG_ID
386   ) values (
387     NEW_REFERENCES.UNIT_CD,
388     NEW_REFERENCES.VERSION_NUMBER,
389     NEW_REFERENCES.UNIT_CAT,
390     X_LAST_UPDATE_DATE,
391     X_LAST_UPDATED_BY,
392     X_LAST_UPDATE_DATE,
393     X_LAST_UPDATED_BY,
394     X_LAST_UPDATE_LOGIN,
395     NEW_REFERENCES.ORG_ID
396   );
397 
398   open c;
399   fetch c into X_ROWID;
400   if (c%notfound) then
401     close c;
402     raise no_data_found;
403   end if;
404   close c;
405   After_DML (
406      p_action => 'INSERT',
407      x_rowid => X_ROWID
408     );
409 
410 end INSERT_ROW;
411 
412 procedure LOCK_ROW (
413   X_ROWID IN VARCHAR2,
414   X_UNIT_CD in VARCHAR2,
415   X_VERSION_NUMBER in NUMBER,
416   X_UNIT_CAT in VARCHAR2
417 
418 ) AS
419   cursor c1 is select ROWID
420     from IGS_PS_UNIT_CATEGORY_ALL
421     where ROWID = X_ROWID
422     for update nowait;
423   tlinfo c1%rowtype;
424 
425 begin
426   open c1;
427   fetch c1 into tlinfo;
428   if (c1%notfound) then
429     close c1;
430     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
431     IGS_GE_MSG_STACK.ADD;
432     app_exception.raise_exception;
433     return;
434   end if;
435   close c1;
436 
437   return;
438 end LOCK_ROW;
439 
440 procedure DELETE_ROW (
441   X_ROWID in VARCHAR2
442 ) AS
443 begin
444    Before_DML (
445      p_action => 'DELETE',
446      x_rowid => X_ROWID
447     );
448   delete from IGS_PS_UNIT_CATEGORY_ALL
449   where ROWID = X_ROWID;
450   if (sql%notfound) then
451     raise no_data_found;
452   end if;
453   After_DML (
454      p_action => 'DELETE',
455      x_rowid => X_ROWID
456     );
457 
458 end DELETE_ROW;
459 
460 end IGS_PS_UNIT_CATEGORY_PKG;