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_GV_FLD_OF_SDY_PKG

Source


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