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_SPRVSR_TYPE_PKG

Source


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