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_PNL_TYPE_PKG

Source


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