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_SPL_TYPE_PKG

Source


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