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_RE_THS_EXAM_TYPE_PKG

Source


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