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_EXM_SPRVSRTYP_PKG

Source


1 package body IGS_AS_EXM_SPRVSRTYP_PKG as
2 /* $Header: IGSDI41B.pls 115.7 2003/05/19 04:43:54 ijeddy ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AS_EXM_SPRVSRTYP%RowType;
6   new_references IGS_AS_EXM_SPRVSRTYP%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_in_charge_ind 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_EXM_SPRVSRTYP
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.exam_supervisor_type := x_exam_supervisor_type;
46     new_references.description := x_description;
47     new_references.in_charge_ind := x_in_charge_ind;
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_EXM_INS_SPVSR_PKG.GET_FK_IGS_AS_EXM_SPRVSRTYP (
67       old_references.exam_supervisor_type
68       );
69 
70     IGS_AS_EXM_SES_VN_SP_PKG.GET_FK_IGS_AS_EXM_SPRVSRTYP (
71       old_references.exam_supervisor_type
72       );
73 
74     IGS_AS_EXM_SUPRVISOR_PKG.GET_FK_IGS_AS_EXM_SPRVSRTYP (
75       old_references.exam_supervisor_type
76       );
77 
78   END Check_Child_Existance;
79 
80   FUNCTION   Get_PK_For_Validation (
81     x_exam_supervisor_type IN VARCHAR2
82     ) RETURN BOOLEAN AS
83     CURSOR cur_rowid IS
84       SELECT   rowid
85       FROM     IGS_AS_EXM_SPRVSRTYP
86       WHERE    exam_supervisor_type = x_exam_supervisor_type
87       FOR UPDATE NOWAIT;
88 
89     lv_rowid cur_rowid%RowType;
90 
91   BEGIN
92 
93     Open cur_rowid;
94     Fetch cur_rowid INTO lv_rowid;
95 IF (cur_rowid%FOUND) THEN
96  Close cur_rowid;
97  Return (TRUE);
98 ELSE
99     Close cur_rowid;
100     Return (FALSE);
101 END IF;
102     Close cur_rowid;
103 
104   END Get_PK_For_Validation;
105 
106 PROCEDURE Check_Constraints (
107 Column_Name	IN	VARCHAR2	DEFAULT NULL,
108 Column_Value 	IN	VARCHAR2	DEFAULT NULL
109 	) as
110 BEGIN
111       IF  column_name is null then
112          NULL;
113       ELSIF upper(Column_name) = 'CLOSED_IND' then
114          new_references.closed_ind:= column_value;
115       ELSIF upper(Column_name) = 'IN_CHARGE_IND' then
116          new_references.in_charge_ind:= column_value;
117       ELSIF upper(Column_name) = 'EXAM_SUPERVISOR_TYPE' then
118          new_references.exam_supervisor_type:= column_value;
119       END IF;
120      IF upper(column_name) = 'CLOSED_IND' OR
121         column_name is null Then
122         IF new_references.closed_ind <> UPPER(new_references.closed_ind) OR new_references.closed_ind NOT IN ( 'Y' , 'N' )Then
123           Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
124           Igs_Ge_Msg_Stack.Add;
125           App_Exception.Raise_Exception;
126         END IF;
127      END IF;
128 
129      IF upper(column_name) = 'IN_CHARGE_IND' OR
130         column_name is null Then
131         IF new_references.in_charge_ind <> UPPER(new_references.in_charge_ind) OR new_references.in_charge_ind NOT IN ( 'Y' , 'N' ) Then
132           Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
133           Igs_Ge_Msg_Stack.Add;
134           App_Exception.Raise_Exception;
135         END IF;
136      END IF;
137      IF upper(column_name) = 'EXAM_SUPERVISOR_TYPE' OR
138         column_name is null Then
139         IF new_references.exam_supervisor_type <> UPPER(new_references.exam_supervisor_type) Then
140           Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
141           Igs_Ge_Msg_Stack.Add;
142           App_Exception.Raise_Exception;
143         END IF;
144      END IF;
145 
146 END Check_Constraints;
147 
148 
149   PROCEDURE Before_DML (
150     p_action IN VARCHAR2,
151     x_rowid IN  VARCHAR2 DEFAULT NULL,
152     x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
153     x_description IN VARCHAR2 DEFAULT NULL,
154     x_in_charge_ind IN VARCHAR2 DEFAULT NULL,
155     x_closed_ind IN VARCHAR2 DEFAULT NULL,
156     x_creation_date IN DATE DEFAULT NULL,
157     x_created_by IN NUMBER DEFAULT NULL,
158     x_last_update_date IN DATE DEFAULT NULL,
159     x_last_updated_by IN NUMBER DEFAULT NULL,
160     x_last_update_login IN NUMBER DEFAULT NULL
161   ) as
162   BEGIN
163 
164     Set_Column_Values (
165       p_action,
166       x_rowid,
167       x_exam_supervisor_type,
168       x_description,
169       x_in_charge_ind,
170       x_closed_ind,
171       x_creation_date,
172       x_created_by,
173       x_last_update_date,
174       x_last_updated_by,
175       x_last_update_login
176     );
177 
178     IF (p_action = 'INSERT') THEN
179       -- Call all the procedures related to Before Insert.
180       Null;
181 IF  Get_PK_For_Validation (
182              new_references.exam_supervisor_type
183 			             ) THEN
184 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
185 Igs_Ge_Msg_Stack.Add;
186 App_Exception.Raise_Exception;
187 END IF;
188 
189       Check_Constraints;
190     ELSIF (p_action = 'UPDATE') THEN
191       -- Call all the procedures related to Before Update.
192       Null;
193       Check_Constraints;
194     ELSIF (p_action = 'DELETE') THEN
195       -- Call all the procedures related to Before Delete.
196       Null;
197       Check_Child_Existance;
198     ELSIF (p_action = 'VALIDATE_INSERT') THEN
199 IF  Get_PK_For_Validation (
200              new_references.exam_supervisor_type
201 			             ) THEN
202 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
203 Igs_Ge_Msg_Stack.Add;
204 App_Exception.Raise_Exception;
205 END IF;
206 	        Check_Constraints;
207     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
208 	        Check_Constraints;
209     ELSIF (p_action = 'VALIDATE_DELETE') THEN
210               Check_Child_Existance;
211 
212     END IF;
213 
214 /*
215 The (L_ROWID := null) was added by ijeddy on the 12-apr-2003 as
216 part of the bug fix for bug no 2868726, (Uniqueness Check at Item Level)
217 */
218 L_ROWID := null;
219 
220  END Before_DML;
221 
222 
223 procedure INSERT_ROW (
224   X_ROWID in out NOCOPY VARCHAR2,
225   X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
226   X_DESCRIPTION in VARCHAR2,
227   X_IN_CHARGE_IND in VARCHAR2,
228   X_CLOSED_IND in VARCHAR2,
229   X_MODE in VARCHAR2 default 'R'
230   ) as
231     cursor C is select ROWID from IGS_AS_EXM_SPRVSRTYP
232       where EXAM_SUPERVISOR_TYPE = X_EXAM_SUPERVISOR_TYPE;
233     X_LAST_UPDATE_DATE DATE;
234     X_LAST_UPDATED_BY NUMBER;
235     X_LAST_UPDATE_LOGIN NUMBER;
236 begin
237   X_LAST_UPDATE_DATE := SYSDATE;
238   if(X_MODE = 'I') then
239     X_LAST_UPDATED_BY := 1;
240     X_LAST_UPDATE_LOGIN := 0;
241   elsif (X_MODE = 'R') then
242     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
243     if X_LAST_UPDATED_BY is NULL then
244       X_LAST_UPDATED_BY := -1;
245     end if;
246     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
247     if X_LAST_UPDATE_LOGIN is NULL then
248       X_LAST_UPDATE_LOGIN := -1;
249     end if;
250   else
251     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
252     Igs_Ge_Msg_Stack.Add;
253     app_exception.raise_exception;
254   end if;
255   Before_DML(
256   p_action=>'INSERT',
257   x_rowid=>X_ROWID,
258   x_closed_ind=>NVL(X_CLOSED_IND,'N'),
259   x_description=>X_DESCRIPTION,
260   x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
261   x_in_charge_ind=> NVL(X_IN_CHARGE_IND,'N'),
262   x_creation_date=>X_LAST_UPDATE_DATE,
263   x_created_by=>X_LAST_UPDATED_BY,
264   x_last_update_date=>X_LAST_UPDATE_DATE,
265   x_last_updated_by=>X_LAST_UPDATED_BY,
266   x_last_update_login=>X_LAST_UPDATE_LOGIN
267   );
268   insert into IGS_AS_EXM_SPRVSRTYP (
269     EXAM_SUPERVISOR_TYPE,
270     DESCRIPTION,
271     IN_CHARGE_IND,
272     CLOSED_IND,
273     CREATION_DATE,
274     CREATED_BY,
275     LAST_UPDATE_DATE,
276     LAST_UPDATED_BY,
277     LAST_UPDATE_LOGIN
278   ) values (
279     NEW_REFERENCES.EXAM_SUPERVISOR_TYPE,
280     NEW_REFERENCES.DESCRIPTION,
281     NEW_REFERENCES.IN_CHARGE_IND,
282     NEW_REFERENCES.CLOSED_IND,
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 end INSERT_ROW;
298 
299 procedure LOCK_ROW (
300   X_ROWID in  VARCHAR2,
301   X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
302   X_DESCRIPTION in VARCHAR2,
303   X_IN_CHARGE_IND in VARCHAR2,
304   X_CLOSED_IND in VARCHAR2
305 ) as
306   cursor c1 is select
307       DESCRIPTION,
308       IN_CHARGE_IND,
309       CLOSED_IND
310     from IGS_AS_EXM_SPRVSRTYP
311     where ROWID = X_ROWID  for update  nowait;
312   tlinfo c1%rowtype;
313 
314 begin
315   open c1;
316   fetch c1 into tlinfo;
317   if (c1%notfound) then
318     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
319     Igs_Ge_Msg_Stack.Add;
320     close c1;
321     app_exception.raise_exception;
322     return;
323   end if;
324   close c1;
325 
326   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
327       AND (tlinfo.IN_CHARGE_IND = X_IN_CHARGE_IND)
328       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
329   ) then
330     null;
331   else
332     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
333     Igs_Ge_Msg_Stack.Add;
334     app_exception.raise_exception;
335   end if;
336   return;
337 end LOCK_ROW;
338 
339 procedure UPDATE_ROW (
340   X_ROWID in  VARCHAR2,
341   X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
342   X_DESCRIPTION in VARCHAR2,
343   X_IN_CHARGE_IND in VARCHAR2,
344   X_CLOSED_IND in VARCHAR2,
345   X_MODE in VARCHAR2 default 'R'
346   ) as
347     X_LAST_UPDATE_DATE DATE;
348     X_LAST_UPDATED_BY NUMBER;
349     X_LAST_UPDATE_LOGIN NUMBER;
350 begin
351   X_LAST_UPDATE_DATE := SYSDATE;
352   if(X_MODE = 'I') then
353     X_LAST_UPDATED_BY := 1;
354     X_LAST_UPDATE_LOGIN := 0;
355   elsif (X_MODE = 'R') then
356     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
357     if X_LAST_UPDATED_BY is NULL then
358       X_LAST_UPDATED_BY := -1;
359     end if;
360     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
361     if X_LAST_UPDATE_LOGIN is NULL then
362       X_LAST_UPDATE_LOGIN := -1;
363     end if;
364   else
365     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
366     Igs_Ge_Msg_Stack.Add;
367     app_exception.raise_exception;
368   end if;
369    Before_DML(
370   p_action=>'UPDATE',
371   x_rowid=>X_ROWID,
372   x_closed_ind=>X_CLOSED_IND,
373   x_description=>X_DESCRIPTION,
374   x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
375   x_in_charge_ind=>X_IN_CHARGE_IND,
376   x_creation_date=>X_LAST_UPDATE_DATE,
377   x_created_by=>X_LAST_UPDATED_BY,
378   x_last_update_date=>X_LAST_UPDATE_DATE,
379   x_last_updated_by=>X_LAST_UPDATED_BY,
380   x_last_update_login=>X_LAST_UPDATE_LOGIN
381   );
382 
383   update IGS_AS_EXM_SPRVSRTYP set
384     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
385     IN_CHARGE_IND = NEW_REFERENCES.IN_CHARGE_IND,
386     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
387     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
388     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
389     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
390   where ROWID = X_ROWID;
391   if (sql%notfound) then
392     raise no_data_found;
393   end if;
394 end UPDATE_ROW;
395 
396 procedure ADD_ROW (
397   X_ROWID in out NOCOPY VARCHAR2,
398   X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
399   X_DESCRIPTION in VARCHAR2,
400   X_IN_CHARGE_IND in VARCHAR2,
401   X_CLOSED_IND in VARCHAR2,
402   X_MODE in VARCHAR2 default 'R'
403   ) as
404   cursor c1 is select rowid from IGS_AS_EXM_SPRVSRTYP
405      where EXAM_SUPERVISOR_TYPE = X_EXAM_SUPERVISOR_TYPE
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_EXAM_SUPERVISOR_TYPE,
415      X_DESCRIPTION,
416      X_IN_CHARGE_IND,
417      X_CLOSED_IND,
418      X_MODE);
419     return;
420   end if;
421   close c1;
422   UPDATE_ROW (
423    X_ROWID,
424    X_EXAM_SUPERVISOR_TYPE,
425    X_DESCRIPTION,
426    X_IN_CHARGE_IND,
427    X_CLOSED_IND,
428    X_MODE);
429 end ADD_ROW;
430 
431 procedure DELETE_ROW (
432   X_ROWID in VARCHAR2) as
433 begin
434  Before_DML(
435   p_action => 'DELETE',
436   x_rowid => X_ROWID
437   );
438 
439   delete from IGS_AS_EXM_SPRVSRTYP
440  where ROWID = X_ROWID;
441   if (sql%notfound) then
442     raise no_data_found;
443   end if;
444 
445 end DELETE_ROW;
446 
447 end IGS_AS_EXM_SPRVSRTYP_PKG;