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_MTRL_TYPE_PKG

Source


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