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_STAT_PKG

Source


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