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_TYPE_PKG

Source


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