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_AS_SPCL_CONS_CAT_PKG

Source


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