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_GE_EXCEPTIONS_PKG

Source


1 package body IGS_GE_EXCEPTIONS_PKG as
2 /* $Header: IGSMI12B.pls 115.3 2002/11/29 01:12:07 nsidana ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_GE_EXCEPTIONS%RowType;
5   new_references IGS_GE_EXCEPTIONS%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_EXCPT_ID IN NUMBER DEFAULT NULL,
11     x_row_id IN ROWID DEFAULT NULL,
12     x_owner IN VARCHAR2 DEFAULT NULL,
13     x_table_name IN VARCHAR2 DEFAULT NULL,
14     x_constraint 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_GE_EXCEPTIONS
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       Close cur_old_ref_values;
37       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38       IGS_GE_MSG_STACK.ADD;
39       App_Exception.Raise_Exception;
40       Return;
41     END IF;
42     Close cur_old_ref_values;
43 
44     -- Populate New Values.
45     new_references.EXCPT_ID := x_EXCPT_ID;
46     new_references.row_id := x_row_id;
47     new_references.owner := x_owner;
48     new_references.table_name := x_table_name;
49     new_references.constraint := x_constraint;
50     IF (p_action = 'UPDATE') THEN
51       new_references.creation_date := old_references.creation_date;
52       new_references.created_by := old_references.created_by;
53     ELSE
54       new_references.creation_date := x_creation_date;
55       new_references.created_by := x_created_by;
56     END IF;
57     new_references.last_update_date := x_last_update_date;
58     new_references.last_updated_by := x_last_updated_by;
59     new_references.last_update_login := x_last_update_login;
60 
61   END Set_Column_Values;
62 
63  PROCEDURE Check_Constraints(
64   Column_Name IN VARCHAR2 DEFAULT NULL,
65   Column_Value IN VARCHAR2 DEFAULT NULL
66  ) as
67   BEGIN
68    IF column_name is null then
69 	NULL;
70    ELSIF upper(Column_name) = 'CONSTRAINT' then
71 	new_references.constraint := column_value ;
72    ELSIF upper(Column_name) = 'OWNER' then
73 	new_references.owner := column_value ;
74    ELSIF upper(Column_name) = 'TABLE_NAME' then
75 	new_references.table_name := column_value ;
76 
77    END IF;
78    IF upper(Column_name) = 'CONSTRAINT' OR column_name is null then
79 	IF new_references.constraint<> UPPER(new_references.constraint) then
80    	    Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
81    	    IGS_GE_MSG_STACK.ADD;
82 	    App_Exception.Raise_Exception;
83 	END IF;
84    END IF;
85    IF upper(Column_name) = 'OWNER' OR column_name is null then
86 	IF new_references.owner <> UPPER(new_references.owner ) then
87    	    Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
88    	    IGS_GE_MSG_STACK.ADD;
89 	    App_Exception.Raise_Exception;
90 	END IF;
91    END IF;
92    IF upper(Column_name) = 'TABLE_NAME'  OR column_name is null then
93 	IF new_references.table_name  <> UPPER(new_references.table_name ) then
94    	    Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
95    	    IGS_GE_MSG_STACK.ADD;
96 	    App_Exception.Raise_Exception;
97 	END IF;
98    END IF;
99 
100   END Check_Constraints;
101 
102 
103   FUNCTION GET_PK_FOR_VALIDATION (
104 	x_excpt_id IN VARCHAR2
105   ) RETURN BOOLEAN as
106 
107     CURSOR cur_rowid IS
108       SELECT   rowid
109       FROM     IGS_GE_EXCEPTIONS
110       WHERE    excpt_id = x_excpt_id
111       FOR UPDATE NOWAIT;
112 
113     lv_rowid cur_rowid%RowType;
114 
115   BEGIN
116 
117     Open cur_rowid;
118     Fetch cur_rowid INTO lv_rowid;
119 	IF (cur_rowid%FOUND) THEN
120 	  Close cur_rowid;
121 	  Return(TRUE);
122 	ELSE
123 	  Close cur_rowid;
124 	  Return(FALSE);
125 	END IF;
126 END ;
127 
128 
129   PROCEDURE Before_DML (
130     p_action IN VARCHAR2,
131     x_rowid IN VARCHAR2 DEFAULT NULL,
132     x_EXCPT_ID in number DEFAULT NULL,
133     x_row_id IN ROWID DEFAULT NULL,
134     x_owner IN VARCHAR2 DEFAULT NULL,
135     x_table_name IN VARCHAR2 DEFAULT NULL,
136     x_constraint 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_EXCPT_ID,
149       x_row_id,
150       x_owner,
151       x_table_name,
152       x_constraint,
153       x_creation_date,
154       x_created_by,
155       x_last_update_date,
156       x_last_updated_by,
157       x_last_update_login
158     );
159 
160     IF (p_action = 'INSERT') THEN
161 	IF Get_PK_For_Validation(
162 		new_references.excpt_id
163 	)THEN
164 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
165 		IGS_GE_MSG_STACK.ADD;
166 		App_Exception.Raise_Exception;
167 	END IF	;
168 	Check_Constraints;
169     ELSIF (p_action = 'UPDATE') THEN
170       -- Call all the procedures related to Before Update.
171 	Check_Constraints;
172     ELSIF (p_action = 'DELETE') THEN
173       -- Call all the procedures related to Before Delete.
174       Null;
175     ELSIF (p_action = 'VALIDATE_INSERT') THEN
176 	IF NOT Get_PK_For_Validation(
177 		new_references.excpt_id
178 	)THEN
179 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
180 		IGS_GE_MSG_STACK.ADD;
181 		App_Exception.Raise_Exception;
182 	END IF	;
183 	Check_Constraints;
184     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
185       -- Call all the procedures related to Before Update.
186 	Check_Constraints;
187     ELSIF (p_action = 'VALIDATE_DELETE') THEN
188       -- Call all the procedures related to Before Delete.
189       Null;
190     END IF;
191 
192   END Before_DML;
193 
194   PROCEDURE After_DML (
195     p_action IN VARCHAR2,
196     x_rowid IN VARCHAR2
197   ) as
198   BEGIN
199 
200     l_rowid := x_rowid;
201 
202     IF (p_action = 'INSERT') THEN
203       -- Call all the procedures related to After Insert.
204       Null;
205     ELSIF (p_action = 'UPDATE') THEN
206       -- Call all the procedures related to After Update.
207       Null;
208     ELSIF (p_action = 'DELETE') THEN
209       -- Call all the procedures related to After Delete.
210       Null;
211     END IF;
212 
213   END After_DML;
214 
215 procedure INSERT_ROW (
216   X_ROWID in out NOCOPY VARCHAR2,
217   X_EXCPT_ID in out NOCOPY NUMBER,
218   X_ROW_ID in ROWID,
219   X_OWNER in VARCHAR2,
220   X_TABLE_NAME in VARCHAR2,
221   X_CONSTRAINT in VARCHAR2,
222   X_MODE in VARCHAR2 default 'R'
223   ) as
224     cursor C is select ROWID from IGS_GE_EXCEPTIONS
225       where ROW_ID = X_ROW_ID;
226     X_LAST_UPDATE_DATE DATE;
227     X_LAST_UPDATED_BY NUMBER;
228     X_LAST_UPDATE_LOGIN NUMBER;
229 begin
230   SELECT IGS_GE_EXCEPTIONS_EXCPT_ID_S.NEXTVAL INTO X_EXCPT_ID
231   FROM DUAL;
232 
233   X_LAST_UPDATE_DATE := SYSDATE;
234   if(X_MODE = 'I') then
235     X_LAST_UPDATED_BY := 1;
236     X_LAST_UPDATE_LOGIN := 0;
237   elsif (X_MODE = 'R') then
238     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
239     if X_LAST_UPDATED_BY is NULL then
240       X_LAST_UPDATED_BY := -1;
241     end if;
242     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
243     if X_LAST_UPDATE_LOGIN is NULL then
244       X_LAST_UPDATE_LOGIN := -1;
245     end if;
246   else
247     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
248     IGS_GE_MSG_STACK.ADD;
249     app_exception.raise_exception;
250   end if;
251   Before_DML (
252     p_action => 'INSERT',
253     x_rowid => X_ROWID,
254     x_EXCPT_ID => X_EXCPT_ID,
255     x_row_id => X_ROW_ID,
256     x_owner => X_OWNER,
257     x_table_name => X_TABLE_NAME,
258     x_constraint => X_CONSTRAINT,
259     x_created_by => X_LAST_UPDATED_BY,
260     x_creation_date => X_LAST_UPDATE_DATE,
261     x_last_updated_by => X_LAST_UPDATED_BY,
262     x_last_update_date => X_LAST_UPDATE_DATE,
263     x_last_update_login => X_LAST_UPDATE_LOGIN
264 );
265 
266   insert into IGS_GE_EXCEPTIONS (
267     EXCPT_ID,
268     ROW_ID,
269     OWNER,
270     TABLE_NAME,
271     CONSTRAINT,
272     CREATION_DATE,
273     CREATED_BY,
274     LAST_UPDATE_DATE,
275     LAST_UPDATED_BY,
276     LAST_UPDATE_LOGIN
277   ) values (
278     NEW_REFERENCES.EXCPT_ID,
279     NEW_REFERENCES.ROW_ID,
280     NEW_REFERENCES.OWNER,
281     NEW_REFERENCES.TABLE_NAME,
282     NEW_REFERENCES.CONSTRAINT,
283     X_LAST_UPDATE_DATE,
284     X_LAST_UPDATED_BY,
285     X_LAST_UPDATE_DATE,
286     X_LAST_UPDATED_BY,
287     X_LAST_UPDATE_LOGIN
288   );
289 
290   open c;
291   fetch c into X_ROWID;
292   if (c%notfound) then
293     close c;
294     raise no_data_found;
295   end if;
296   close c;
297   After_DML (
298     p_action => 'INSERT',
299     x_rowid => X_ROWID
300    );
301 
302 end INSERT_ROW;
303 
304 procedure LOCK_ROW (
305   X_ROWID in VARCHAR2,
306   X_EXCPT_ID in NUMBER,
307   X_ROW_ID in ROWID,
308   X_OWNER in VARCHAR2,
309   X_TABLE_NAME in VARCHAR2,
310   X_CONSTRAINT in VARCHAR2
311 ) as
312   cursor c1 is select
313       ROW_ID,
314       OWNER,
315       TABLE_NAME,
316       CONSTRAINT
317     from IGS_GE_EXCEPTIONS
318     where ROWID = X_ROWID
319     for update  nowait;
320   tlinfo c1%rowtype;
321 
322 begin
323   open c1;
324   fetch c1 into tlinfo;
325   if (c1%notfound) then
326     close c1;
327     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
328     IGS_GE_MSG_STACK.ADD;
329     app_exception.raise_exception;
330     return;
331   end if;
332   close c1;
333 
334   if ( (tlinfo.OWNER = X_OWNER)
335       AND (tlinfo.TABLE_NAME = X_TABLE_NAME)
336       AND (tlinfo.CONSTRAINT = X_CONSTRAINT)
337   ) then
338     null;
339   else
340     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
341     IGS_GE_MSG_STACK.ADD;
342     app_exception.raise_exception;
343   end if;
344   return;
345 end LOCK_ROW;
346 
347 procedure UPDATE_ROW (
348   X_ROWID in VARCHAR2,
349   X_EXCPT_ID in NUMBER,
350   X_ROW_ID in ROWID,
351   X_OWNER in VARCHAR2,
352   X_TABLE_NAME in VARCHAR2,
353   X_CONSTRAINT in VARCHAR2,
354   X_MODE in VARCHAR2 default 'R'
355   ) as
356     X_LAST_UPDATE_DATE DATE;
357     X_LAST_UPDATED_BY NUMBER;
358     X_LAST_UPDATE_LOGIN NUMBER;
359 begin
360   X_LAST_UPDATE_DATE := SYSDATE;
361   if(X_MODE = 'I') then
362     X_LAST_UPDATED_BY := 1;
363     X_LAST_UPDATE_LOGIN := 0;
364   elsif (X_MODE = 'R') then
365     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
366     if X_LAST_UPDATED_BY is NULL then
367       X_LAST_UPDATED_BY := -1;
368     end if;
369     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
370     if X_LAST_UPDATE_LOGIN is NULL then
371       X_LAST_UPDATE_LOGIN := -1;
372     end if;
373   else
374     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
375     IGS_GE_MSG_STACK.ADD;
376     app_exception.raise_exception;
377   end if;
378   Before_DML (
379     p_action => 'UPDATE',
380     x_rowid => X_ROWID,
381     x_EXCPT_ID => X_EXCPT_ID,
382     x_row_id => X_ROW_ID,
383     x_owner => X_OWNER,
384     x_table_name => X_TABLE_NAME,
385     x_constraint => X_CONSTRAINT,
386     x_created_by => X_LAST_UPDATED_BY,
387     x_creation_date => X_LAST_UPDATE_DATE,
388     x_last_updated_by => X_LAST_UPDATED_BY,
389     x_last_update_date => X_LAST_UPDATE_DATE,
390     x_last_update_login => X_LAST_UPDATE_LOGIN
391 );
392 
393   update IGS_GE_EXCEPTIONS set
394     ROW_ID = NEW_REFERENCES.ROW_ID,
395     OWNER =  NEW_REFERENCES.OWNER,
396     TABLE_NAME =   NEW_REFERENCES.TABLE_NAME,
397     CONSTRAINT =   NEW_REFERENCES.CONSTRAINT,
398     LAST_UPDATE_DATE =  X_LAST_UPDATE_DATE,
399     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
400     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
401     where ROWID = X_ROWID
402   ;
406   After_DML (
403   if (sql%notfound) then
404     raise no_data_found;
405   end if;
407     p_action => 'UPDATE',
408     x_rowid => X_ROWID
409   );
410 end UPDATE_ROW;
411 
412 procedure ADD_ROW (
413   X_ROWID in out NOCOPY VARCHAR2,
414   X_EXCPT_ID in out NOCOPY NUMBER,
415   X_ROW_ID in ROWID,
416   X_OWNER in VARCHAR2,
417   X_TABLE_NAME in VARCHAR2,
418   X_CONSTRAINT in VARCHAR2,
419   X_MODE in VARCHAR2 default 'R'
420   ) as
421   cursor c1 is select rowid from IGS_GE_EXCEPTIONS
422      where ROW_ID = X_ROW_ID
423   ;
424 
425 begin
426   open c1;
427   fetch c1 into X_ROWID;
428   if (c1%notfound) then
429     close c1;
430     INSERT_ROW (
431      X_ROWID,
432      X_EXCPT_ID,
433      X_ROW_ID,
434      X_OWNER,
435      X_TABLE_NAME,
436      X_CONSTRAINT,
437      X_MODE);
438     return;
439   end if;
440   close c1;
441   UPDATE_ROW (
442    X_ROWID,
443    X_EXCPT_ID,
444    X_ROW_ID,
445    X_OWNER,
446    X_TABLE_NAME,
447    X_CONSTRAINT,
448    X_MODE);
449 end ADD_ROW;
450 
451 procedure DELETE_ROW (
452   X_ROWID in VARCHAR2
453 ) as
454 begin
455   Before_DML (
456     p_action => 'DELETE',
457     x_rowid => X_ROWID
458   );
459   delete from IGS_GE_EXCEPTIONS
460   where ROWID = X_ROWID;
461   if (sql%notfound) then
462     raise no_data_found;
463   end if;
464   After_DML (
465     p_action => 'DELETE',
466     x_rowid => X_ROWID
467   );
468 
469 end DELETE_ROW;
470 
471 end IGS_GE_EXCEPTIONS_PKG;