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_TOA_CLS_CD_PKG

Source


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