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_EN_UNIT_SET_CAT_PKG

Source


1 package body IGS_EN_UNIT_SET_CAT_PKG AS
2 /* $Header: IGSEI02B.pls 115.5 2003/03/25 08:42:08 nalkumar ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_EN_UNIT_SET_CAT%RowType;
6   new_references IGS_EN_UNIT_SET_CAT%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_unit_set_cat IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_s_unit_set_cat IN VARCHAR2 DEFAULT NULL,
14     x_rank 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   )AS
22 
23     CURSOR cur_old_ref_values IS
24       SELECT   *
25       FROM     IGS_EN_UNIT_SET_CAT
26       WHERE    rowid = x_rowid;
27 
28   BEGIN
29 
30     l_rowid := x_rowid;
31 
32     -- Code for setting the Old and New Reference Values.
33     -- Populate Old Values.
34     Open cur_old_ref_values;
35     Fetch cur_old_ref_values INTO old_references;
36     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39       Close cur_old_ref_values;
40       App_Exception.Raise_Exception;
41       Return;
42     END IF;
43     Close cur_old_ref_values;
44 
45     -- Populate New Values.
46     new_references.unit_set_cat := x_unit_set_cat;
47     new_references.description := x_description;
48     new_references.s_unit_set_cat := x_s_unit_set_cat;
49     new_references.rank := x_rank;
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 
62   END Set_Column_Values;
63 
64 
65  PROCEDURE Check_Constraints (
66  	Column_Name	IN	VARCHAR2	DEFAULT NULL,
67  	Column_Value 	IN	VARCHAR2	DEFAULT NULL
68  ) as
69 
70   BEGIN
71 
72   -- The following code checks for check constraints on the Columns.
73 
74     IF column_name is NULL THEN
75         NULL;
76     ELSIF  UPPER(column_name) = 'UNIT_SET_CAT' THEN
77         new_references.unit_set_cat := column_value;
78     ELSIF  UPPER(column_name) = 'S_UNIT_SET_CAT' THEN
79         new_references.s_unit_set_cat := column_value;
80     ELSIF  UPPER(column_name) = 'CLOSED_IND' THEN
81         new_references.closed_ind := column_value;
82     ELSIF  UPPER(column_name) = 'RANK' THEN
83         new_references.rank := IGS_GE_NUMBER.TO_NUM(column_value);
84 
85     END IF;
86 
87     IF ((UPPER (column_name) = 'UNIT_SET_CAT') OR (column_name IS NULL)) THEN
88       IF (new_references.unit_set_cat <> UPPER (new_references.unit_set_cat)) 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) = 'S_UNIT_SET_CAT') OR (column_name IS NULL)) THEN
96       IF (new_references.s_unit_set_cat IS NULL ) THEN
97         Fnd_Message.Set_Name ('IGS', 'IGS_GE_MANDATORY_FLD');
98         IGS_GE_MSG_STACK.ADD;
99         App_Exception.Raise_Exception;
100       END IF;
101     END IF;
102 
103     IF ((UPPER (column_name) = 'CLOSED_IND') OR (column_name IS NULL)) THEN
104       IF new_references.closed_ind  NOT IN ( 'Y' , 'N' ) THEN
105         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
106 IGS_GE_MSG_STACK.ADD;
107         App_Exception.Raise_Exception;
108       END IF;
109     END IF;
110 
111 
112     IF ((UPPER (column_name) = 'RANK') OR (column_name IS NULL)) THEN
113       IF new_references.rank < 0  OR
114          new_references.rank > 999  THEN
115         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
116 IGS_GE_MSG_STACK.ADD;
117         App_Exception.Raise_Exception;
118       END IF;
119     END IF;
120 
121 
122   END Check_Constraints;
123 
124   PROCEDURE Check_Child_Existance AS
125   BEGIN
126 
127     IGS_EN_UNIT_SET_PKG.GET_FK_IGS_EN_UNIT_SET_CAT (
128       old_references.unit_set_cat
129       );
130     igs_da_setup_pkg.get_fk_igs_en_unit_set_cat(old_references.unit_set_cat);
131 
132   END Check_Child_Existance;
133 
134   FUNCTION Get_PK_For_Validation (
135     x_unit_set_cat IN VARCHAR2
136     )  RETURN BOOLEAN AS
137 
138     CURSOR cur_rowid IS
139       SELECT   rowid
140       FROM     IGS_EN_UNIT_SET_CAT
141       WHERE    unit_set_cat = x_unit_set_cat
142       FOR UPDATE NOWAIT;
143 
144     lv_rowid cur_rowid%RowType;
145 
146   BEGIN
147 
148     Open cur_rowid;
149     Fetch cur_rowid INTO lv_rowid;
150 
151     IF (cur_rowid%FOUND) THEN
152        Close cur_rowid;
153        Return(TRUE);
154     ELSE
155        Close cur_rowid;
156        Return(FALSE);
157     END IF;
158 
159 
160   END Get_PK_For_Validation;
161 
162   PROCEDURE Before_DML (
163     p_action IN VARCHAR2,
164     x_rowid IN VARCHAR2 DEFAULT NULL,
165     x_unit_set_cat IN VARCHAR2 DEFAULT NULL,
166     x_description IN VARCHAR2 DEFAULT NULL,
167     x_s_unit_set_cat IN VARCHAR2 DEFAULT NULL,
168     x_rank IN NUMBER DEFAULT NULL,
169     x_closed_ind IN VARCHAR2 DEFAULT NULL,
170      x_creation_date IN DATE DEFAULT NULL,
171     x_created_by IN NUMBER DEFAULT NULL,
172     x_last_update_date IN DATE DEFAULT NULL,
173     x_last_updated_by IN NUMBER DEFAULT NULL,
174     x_last_update_login IN NUMBER DEFAULT NULL
175   ) AS
176   BEGIN
177 
178     Set_Column_Values (
179       p_action,
180       x_rowid,
181       x_unit_set_cat,
182       x_description,
183       x_s_unit_set_cat,
184       x_rank,
185       x_closed_ind,
186       x_creation_date,
187       x_created_by,
188       x_last_update_date,
189       x_last_updated_by,
190       x_last_update_login
191     );
192 
193     IF (p_action = 'INSERT') THEN
194       -- Call all the procedures related to Before Insert.
195 
196 	IF Get_PK_For_Validation(
197 		 new_references.unit_set_cat
198 	                            ) THEN
199 
200  		Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
201 IGS_GE_MSG_STACK.ADD;
202  		App_Exception.Raise_Exception;
203 
204 	END IF;
205 
206 	Check_Constraints;
207 
208     ELSIF (p_action = 'UPDATE') THEN
209       -- Call all the procedures related to Before Update.
210 	Check_Constraints;
211     ELSIF (p_action = 'DELETE') THEN
212       -- Call all the procedures related to Before Delete.
213       Check_Child_Existance;
214 
215     ELSIF (p_action = 'VALIDATE_INSERT') THEN
216       		IF  Get_PK_For_Validation (
217 		          new_references.unit_set_cat
218 				 ) THEN
219 		          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
220 IGS_GE_MSG_STACK.ADD;
221 		          App_Exception.Raise_Exception;
222      	        END IF;
223       		Check_Constraints;
224     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
225       		  Check_Constraints;
226     ELSIF (p_action = 'VALIDATE_DELETE') THEN
227 
228                  Check_Child_Existance;
229     END IF;
230 
231   END Before_DML;
232 
233   PROCEDURE After_DML (
234     p_action IN VARCHAR2,
235     x_rowid IN VARCHAR2
236   ) AS
237   BEGIN
238 
239     l_rowid := x_rowid;
240 
241     IF (p_action = 'INSERT') THEN
242       -- Call all the procedures related to After Insert.
243       Null;
244     ELSIF (p_action = 'UPDATE') THEN
245       -- Call all the procedures related to After Update.
246       Null;
247     ELSIF (p_action = 'DELETE') THEN
248       -- Call all the procedures related to After Delete.
249       Null;
250     END IF;
251 
252   END After_DML;
253 
254 
255 
256 procedure INSERT_ROW (
257   X_ROWID in out NOCOPY VARCHAR2,
258   X_UNIT_SET_CAT in VARCHAR2,
259   X_DESCRIPTION in VARCHAR2,
260   X_S_UNIT_SET_CAT in VARCHAR2,
261   X_RANK in NUMBER,
262   X_CLOSED_IND in VARCHAR2,
263   X_MODE in VARCHAR2 default 'R'
264   ) AS
265     cursor C is select ROWID from IGS_EN_UNIT_SET_CAT
266       where UNIT_SET_CAT = X_UNIT_SET_CAT;
267     X_LAST_UPDATE_DATE DATE;
268     X_LAST_UPDATED_BY NUMBER;
269     X_LAST_UPDATE_LOGIN NUMBER;
270 begin
271   X_LAST_UPDATE_DATE := SYSDATE;
272   if(X_MODE = 'I') then
273     X_LAST_UPDATED_BY := 1;
274     X_LAST_UPDATE_LOGIN := 0;
275   elsif (X_MODE = 'R') then
276     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
277     if X_LAST_UPDATED_BY is NULL then
278       X_LAST_UPDATED_BY := -1;
279     end if;
280     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
281     if X_LAST_UPDATE_LOGIN is NULL then
282       X_LAST_UPDATE_LOGIN := -1;
283     end if;
284   else
285     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
286 IGS_GE_MSG_STACK.ADD;
287     app_exception.raise_exception;
288   end if;
289 
290   Before_DML(
291       p_action => 'INSERT' ,
292       x_rowid => x_rowid ,
293       x_unit_set_cat => x_unit_set_cat ,
294       x_description => x_description ,
295       x_s_unit_set_cat => x_s_unit_set_cat ,
296       x_rank => NVL(x_rank,1) ,
297       x_closed_ind => NVL(x_closed_ind,'N') ,
298       x_creation_date => x_last_update_date ,
299       x_created_by => x_last_updated_by  ,
300       x_last_update_date => x_last_update_date ,
301       x_last_updated_by => x_last_updated_by ,
302       x_last_update_login => x_last_update_login
303     );
304 
305   insert into IGS_EN_UNIT_SET_CAT (
306     UNIT_SET_CAT,
307     DESCRIPTION,
308     S_UNIT_SET_CAT,
309     RANK,
310     CLOSED_IND,
311     CREATION_DATE,
312     CREATED_BY,
313     LAST_UPDATE_DATE,
314     LAST_UPDATED_BY,
315     LAST_UPDATE_LOGIN
316   ) values (
317     NEW_REFERENCES.UNIT_SET_CAT,
318     NEW_REFERENCES.DESCRIPTION,
319     NEW_REFERENCES.S_UNIT_SET_CAT,
320     NEW_REFERENCES.RANK,
321     NEW_REFERENCES.CLOSED_IND,
322     X_LAST_UPDATE_DATE,
323     X_LAST_UPDATED_BY,
324     X_LAST_UPDATE_DATE,
325     X_LAST_UPDATED_BY,
326     X_LAST_UPDATE_LOGIN
327   );
328 
329   open c;
330   fetch c into X_ROWID;
331   if (c%notfound) then
332     close c;
333     raise no_data_found;
334   end if;
335   close c;
336 
337   After_DML(
338     p_action => 'INSERT',
339     x_rowid => X_ROWID
340   );
341 
342 end INSERT_ROW;
343 
344 procedure LOCK_ROW (
345   X_ROWID IN VARCHAR2,
346   X_UNIT_SET_CAT in VARCHAR2,
347   X_DESCRIPTION in VARCHAR2,
348   X_S_UNIT_SET_CAT in VARCHAR2,
349   X_RANK in NUMBER,
350   X_CLOSED_IND in VARCHAR2
351 ) AS
352   cursor c1 is select
353       DESCRIPTION,
354       S_UNIT_SET_CAT,
355       RANK,
356       CLOSED_IND
357     from IGS_EN_UNIT_SET_CAT
358     where ROWID = X_ROWID
359     for update nowait;
360   tlinfo c1%rowtype;
361 
362 begin
363   open c1;
364   fetch c1 into tlinfo;
365   if (c1%notfound) then
366     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
367 IGS_GE_MSG_STACK.ADD;
368     app_exception.raise_exception;
369     close c1;
370     return;
371   end if;
372   close c1;
373 
374   if (
375       (tlinfo.DESCRIPTION = X_DESCRIPTION)
376       AND ((tlinfo.S_UNIT_SET_CAT = X_S_UNIT_SET_CAT)
377            OR ((tlinfo.S_UNIT_SET_CAT IS NULL)
378                AND (X_S_UNIT_SET_CAT IS NULL)))
379       AND (tlinfo.RANK = X_RANK)
380       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
381   ) then
382     null;
383   else
384     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
385 IGS_GE_MSG_STACK.ADD;
386     app_exception.raise_exception;
387   end if;
388   return;
389 end LOCK_ROW;
390 
391 procedure UPDATE_ROW (
392   X_ROWID IN VARCHAR2,
393   X_UNIT_SET_CAT in VARCHAR2,
394   X_DESCRIPTION in VARCHAR2,
395   X_S_UNIT_SET_CAT in VARCHAR2,
396   X_RANK in NUMBER,
397   X_CLOSED_IND in VARCHAR2,
398   X_MODE in VARCHAR2 default 'R'
399   ) AS
400     X_LAST_UPDATE_DATE DATE;
401     X_LAST_UPDATED_BY NUMBER;
402     X_LAST_UPDATE_LOGIN NUMBER;
403 begin
404   X_LAST_UPDATE_DATE := SYSDATE;
405   if(X_MODE = 'I') then
406     X_LAST_UPDATED_BY := 1;
407     X_LAST_UPDATE_LOGIN := 0;
408   elsif (X_MODE = 'R') then
409     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
410     if X_LAST_UPDATED_BY is NULL then
411       X_LAST_UPDATED_BY := -1;
412     end if;
413     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
414     if X_LAST_UPDATE_LOGIN is NULL then
415       X_LAST_UPDATE_LOGIN := -1;
416     end if;
417   else
418     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
419 IGS_GE_MSG_STACK.ADD;
420     app_exception.raise_exception;
421   end if;
422 
423   Before_DML(
424       p_action => 'UPDATE' ,
425       x_rowid => x_rowid ,
426       x_unit_set_cat => x_unit_set_cat ,
427       x_description => x_description ,
428       x_s_unit_set_cat => x_s_unit_set_cat ,
429       x_rank => x_rank ,
430       x_closed_ind => x_closed_ind ,
431       x_creation_date => x_last_update_date,
432       x_created_by => x_last_updated_by ,
433       x_last_update_date => x_last_update_date ,
434       x_last_updated_by => x_last_updated_by ,
435       x_last_update_login => x_last_update_login
436     );
437 
438   update IGS_EN_UNIT_SET_CAT set
439     S_UNIT_SET_CAT = NEW_REFERENCES.S_UNIT_SET_CAT,
440     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
441     RANK = NEW_REFERENCES.RANK,
442     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
443     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
444     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
445     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
446   where ROWID = X_ROWID
447   ;
448   if (sql%notfound) then
449     raise no_data_found;
450   end if;
451 
452   After_DML(
453     p_action => 'UPDATE',
454     x_rowid => X_ROWID
455   );
456 
457 end UPDATE_ROW;
458 
459 procedure ADD_ROW (
460   X_ROWID in out NOCOPY VARCHAR2,
461   X_UNIT_SET_CAT in VARCHAR2,
462   X_DESCRIPTION in VARCHAR2,
463   X_S_UNIT_SET_CAT in VARCHAR2,
464   X_RANK in NUMBER,
465   X_CLOSED_IND in VARCHAR2,
466   X_MODE in VARCHAR2 default 'R'
467   ) AS
468   cursor c1 is select rowid from IGS_EN_UNIT_SET_CAT
469      where UNIT_SET_CAT = X_UNIT_SET_CAT
470   ;
471 begin
472   open c1;
473   fetch c1 into X_ROWID;
474   if (c1%notfound) then
475     close c1;
476     INSERT_ROW (
477      X_ROWID,
478      X_UNIT_SET_CAT,
479      X_DESCRIPTION,
480      X_S_UNIT_SET_CAT,
481      X_RANK,
482      X_CLOSED_IND,
483      X_MODE);
484     return;
485   end if;
486   close c1;
487   UPDATE_ROW (
488    X_ROWID,
489    X_UNIT_SET_CAT,
490    X_DESCRIPTION,
491    X_S_UNIT_SET_CAT,
492    X_RANK,
493    X_CLOSED_IND,
494    X_MODE);
495 end ADD_ROW;
496 
497 procedure DELETE_ROW (
498   X_ROWID IN VARCHAR2
499 ) AS
500 begin
501 
502   Before_DML(
503     p_action => 'DELETE',
504     x_rowid => X_ROWID
505   );
506 
507   delete from IGS_EN_UNIT_SET_CAT
508   where ROWID = X_ROWID;
509   if (sql%notfound) then
510     raise no_data_found;
511   end if;
512 
513   After_DML(
514     p_action => 'DELETE',
515     x_rowid => X_ROWID
516   );
517 
518 
519 end DELETE_ROW;
520 
521 end IGS_EN_UNIT_SET_CAT_PKG;