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_CAT_PKG

Source


1 package body IGS_PS_CAT_PKG AS
2   /* $Header: IGSPI08B.pls 115.3 2002/11/29 01:54:22 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PS_CAT%RowType;
6   new_references IGS_PS_CAT%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_course_cat 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_CAT
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_cat := x_course_cat;
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  )
64  AS
65  BEGIN
66 
67 	IF column_name is null then
68 	    NULL;
69 	ELSIF upper(Column_name) = 'CLOSED_IND' then
70 	    new_references.closed_ind := column_value;
71 	ELSIF upper(Column_name) = 'COURSE_CAT' then
72 	    new_references.course_cat := column_value;
73      END IF;
74 
75     IF upper(column_name) = 'CLOSED_IND' OR
76     column_name is null Then
77   	IF ( new_references.closed_ind NOT IN ( 'Y' , 'N' ) ) Then
78 	       Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
79 	       IGS_GE_MSG_STACK.ADD;
80              App_Exception.Raise_Exception;
81           END IF;
82       END IF;
83 
84     IF upper(column_name) = 'COURSE_CAT' OR
85     column_name is null Then
86 	   IF ( new_references.course_cat <> UPPER(new_references.course_cat) ) 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 
93   END Check_Constraints;
94 
95   PROCEDURE Check_Child_Existance AS
96   BEGIN
97 
98     IGS_PS_CATEGORISE_PKG.GET_FK_IGS_PS_CAT (
99       old_references.course_cat
100       );
101 
102   END Check_Child_Existance;
103 
104   FUNCTION Get_PK_For_Validation (
105     x_course_cat IN VARCHAR2
106     )
107   RETURN BOOLEAN AS
108 
109     CURSOR cur_rowid IS
110       SELECT   rowid
111       FROM     IGS_PS_CAT
112       WHERE    course_cat = x_course_cat
113       FOR UPDATE NOWAIT;
114 
115     lv_rowid cur_rowid%RowType;
116 
117   BEGIN
118 
119     Open cur_rowid;
120     Fetch cur_rowid INTO lv_rowid;
121     IF (cur_rowid%FOUND) THEN
122       Close cur_rowid;
123       Return (TRUE);
124     ELSE
125 	Close cur_rowid;
126       Return (FALSE);
127     END IF;
128 
129   END Get_PK_For_Validation;
130 
131   PROCEDURE Before_DML (
132     p_action IN VARCHAR2,
133     x_rowid IN VARCHAR2 DEFAULT NULL,
134     x_course_cat IN VARCHAR2 DEFAULT NULL,
135     x_description IN VARCHAR2 DEFAULT NULL,
136     x_closed_ind IN VARCHAR2 DEFAULT NULL,
137     x_creation_date IN DATE DEFAULT NULL,
138     x_created_by IN NUMBER DEFAULT NULL,
139     x_last_update_date IN DATE DEFAULT NULL,
140     x_last_updated_by IN NUMBER DEFAULT NULL,
141     x_last_update_login IN NUMBER DEFAULT NULL
142   ) AS
143   BEGIN
144 
145     Set_Column_Values (
146       p_action,
147       x_rowid,
148       x_course_cat,
149       x_description,
150       x_closed_ind,
151       x_creation_date,
152       x_created_by,
153       x_last_update_date,
154       x_last_updated_by,
155       x_last_update_login
156     );
157 
158     IF (p_action = 'INSERT') THEN
159       -- Call all the procedures related to Before Insert.
160 	IF Get_PK_For_Validation (
161 	   new_references.course_cat ) THEN
162 	   Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
163 	   IGS_GE_MSG_STACK.ADD;
164          App_Exception.Raise_Exception;
165 	END IF;
166       Check_Constraints;
167     ELSIF (p_action = 'UPDATE') THEN
168       -- Call all the procedures related to Before Update.
169       Null;
170       Check_Constraints;
171     ELSIF (p_action = 'DELETE') THEN
172       -- Call all the procedures related to Before Delete.
173       Null;
174       Check_Child_Existance;
175     ELSIF (p_action = 'VALIDATE_INSERT') THEN
176 	IF  Get_PK_For_Validation (
177 	new_references.course_cat ) 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 procedure INSERT_ROW (
203   X_ROWID in out NOCOPY VARCHAR2,
204   X_COURSE_CAT in VARCHAR2,
205   X_DESCRIPTION in VARCHAR2,
206   X_CLOSED_IND in VARCHAR2,
207   X_MODE in VARCHAR2 default 'R'
208   ) AS
209     cursor C is select ROWID from IGS_PS_CAT
210       where COURSE_CAT = X_COURSE_CAT;
211     X_LAST_UPDATE_DATE DATE;
212     X_LAST_UPDATED_BY NUMBER;
213     X_LAST_UPDATE_LOGIN NUMBER;
214 begin
215   X_LAST_UPDATE_DATE := SYSDATE;
216   if(X_MODE = 'I') then
217     X_LAST_UPDATED_BY := 1;
218     X_LAST_UPDATE_LOGIN := 0;
219   elsif (X_MODE = 'R') then
220     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
221     if X_LAST_UPDATED_BY is NULL then
222       X_LAST_UPDATED_BY := -1;
223     end if;
224     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
225     if X_LAST_UPDATE_LOGIN is NULL then
226       X_LAST_UPDATE_LOGIN := -1;
227     end if;
228   else
229     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
230     IGS_GE_MSG_STACK.ADD;
231     app_exception.raise_exception;
232   end if;
233 
234 Before_DML (
235     p_action => 'INSERT',
236     x_rowid => X_ROWID,
237     x_course_cat => X_COURSE_CAT,
238     x_description => X_DESCRIPTION ,
239     x_closed_ind => NVL(X_CLOSED_IND,'N'),
240     x_creation_date => X_LAST_UPDATE_DATE  ,
241     x_created_by => X_LAST_UPDATED_BY ,
242     x_last_update_date => X_LAST_UPDATE_DATE  ,
243     x_last_updated_by => X_LAST_UPDATED_BY ,
244     x_last_update_login => X_LAST_UPDATE_LOGIN
245  );
246 
247   insert into IGS_PS_CAT (
248     COURSE_CAT,
249     DESCRIPTION,
250     CLOSED_IND,
251     CREATION_DATE,
252     CREATED_BY,
253     LAST_UPDATE_DATE,
254     LAST_UPDATED_BY,
255     LAST_UPDATE_LOGIN
256   ) values (
257     NEW_REFERENCES.COURSE_CAT,
258     NEW_REFERENCES.DESCRIPTION,
259     NEW_REFERENCES.CLOSED_IND,
260     X_LAST_UPDATE_DATE,
261     X_LAST_UPDATED_BY,
262     X_LAST_UPDATE_DATE,
263     X_LAST_UPDATED_BY,
264     X_LAST_UPDATE_LOGIN
265   );
266 
267   open c;
268   fetch c into X_ROWID;
269   if (c%notfound) then
270     close c;
271     raise no_data_found;
272   end if;
273   close c;
274 
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_COURSE_CAT 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_CAT
292     where ROWID = X_ROWID
293     for update nowait;
294   tlinfo c1%rowtype;
295 
296 begin
297   open c1;
298   fetch c1 into tlinfo;
299   if (c1%notfound) then
300     close c1;
301     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
302     IGS_GE_MSG_STACK.ADD;
303     app_exception.raise_exception;
304     return;
305   end if;
306   close c1;
307 
308   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
309       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
310   ) then
311     null;
312   else
313     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
314     IGS_GE_MSG_STACK.ADD;
315     app_exception.raise_exception;
316   end if;
317   return;
318 end LOCK_ROW;
319 
320 procedure UPDATE_ROW (
321   X_ROWID IN VARCHAR2,
322   X_COURSE_CAT in VARCHAR2,
323   X_DESCRIPTION in VARCHAR2,
324   X_CLOSED_IND in VARCHAR2,
325   X_MODE in VARCHAR2 default 'R'
326   ) AS
327     X_LAST_UPDATE_DATE DATE;
328     X_LAST_UPDATED_BY NUMBER;
329     X_LAST_UPDATE_LOGIN NUMBER;
330 begin
331   X_LAST_UPDATE_DATE := SYSDATE;
332   if(X_MODE = 'I') then
333     X_LAST_UPDATED_BY := 1;
334     X_LAST_UPDATE_LOGIN := 0;
335   elsif (X_MODE = 'R') then
336     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
337     if X_LAST_UPDATED_BY is NULL then
338       X_LAST_UPDATED_BY := -1;
339     end if;
340     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
341     if X_LAST_UPDATE_LOGIN is NULL then
342       X_LAST_UPDATE_LOGIN := -1;
343     end if;
344   else
345     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
346     IGS_GE_MSG_STACK.ADD;
347     app_exception.raise_exception;
348   end if;
349 
350 Before_DML (
351     p_action => 'UPDATE',
352     x_rowid => X_ROWID,
353     x_course_cat => X_COURSE_CAT,
354     x_description => X_DESCRIPTION ,
355     x_closed_ind => X_CLOSED_IND,
356     x_creation_date => X_LAST_UPDATE_DATE  ,
357     x_created_by => X_LAST_UPDATED_BY ,
358     x_last_update_date => X_LAST_UPDATE_DATE  ,
359     x_last_updated_by => X_LAST_UPDATED_BY ,
360     x_last_update_login => X_LAST_UPDATE_LOGIN
361  );
362 
363   update IGS_PS_CAT set
364     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
365     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
366     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
367     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
368     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
369   where ROWID = X_ROWID
370   ;
371   if (sql%notfound) then
372     raise no_data_found;
373   end if;
374 
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_CAT 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_CAT
390      where COURSE_CAT = X_COURSE_CAT
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_CAT,
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_CAT,
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 
419 Before_DML (
420 	p_action => 'DELETE',
421 	x_rowid => X_ROWID
422 );
423 
424   delete from IGS_PS_CAT
425   where ROWID = X_ROWID;
426   if (sql%notfound) then
427     raise no_data_found;
428   end if;
429 
430 After_DML (
431 	p_action => 'DELETE',
432 	x_rowid => X_ROWID
433 );
434 
435 end DELETE_ROW;
436 
437 end IGS_PS_CAT_PKG;