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_STAGE_TYPE_PKG

Source


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