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_MR_TP_PKG

Source


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