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_PS_GOVT_ATD_TYPE_PKG

Source


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