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_AD_GOV_BAS_FR_TY_PKG

Source


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