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_THESIS_RESULT_PKG

Source


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