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_GRP_TYPE_PKG

Source


1 package body IGS_PS_GRP_TYPE_PKG AS
2   /* $Header: IGSPI18B.pls 115.3 2002/11/29 02:03:59 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PS_GRP_TYPE%RowType;
6   new_references IGS_PS_GRP_TYPE%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_course_group_type IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_s_course_group_type 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_GRP_TYPE
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       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37       IGS_GE_MSG_STACK.ADD;
38       App_Exception.Raise_Exception;
39       Close cur_old_ref_values;
40       Return;
41     END IF;
42     Close cur_old_ref_values;
43 
44     -- Populate New Values.
45     new_references.course_group_type := x_course_group_type;
46     new_references.description := x_description;
47     new_references.s_course_group_type := x_s_course_group_type;
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   -- Trigger description :-
63   -- "OSS_TST".trg_cgt_br_iu
64   -- BEFORE INSERT OR UPDATE
65   -- ON IGS_PS_GRP_TYPE
66   -- FOR EACH ROW
67 
68   PROCEDURE BeforeRowInsertUpdate1(
69     p_inserting IN BOOLEAN DEFAULT FALSE,
70     p_updating IN BOOLEAN DEFAULT FALSE,
71     p_deleting IN BOOLEAN DEFAULT FALSE
72     ) AS
73 	v_message_name	varchar2(30);
74   BEGIN
75 	-- Validate system IGS_PS_COURSE group type.
76 	IF p_inserting OR
77 		(p_updating AND
78 		((old_references.s_course_group_type <> new_references.s_course_group_type) OR
79 		 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N'))) THEN
80 		IF IGS_PS_VAL_CGT.crsp_val_cgt_sys_cgt (
81 				new_references.s_course_group_type,
82 				v_message_name) = FALSE THEN
83 			Fnd_Message.Set_Name('IGS',v_message_name);
84 			 IGS_GE_MSG_STACK.ADD;
85 			App_Exception.Raise_Exception;
86 		END IF;
87 	END IF;
88 
89   END BeforeRowInsertUpdate1;
90 
91  PROCEDURE Check_Constraints (
92  Column_Name	IN VARCHAR2	DEFAULT NULL,
93  Column_Value 	IN VARCHAR2	DEFAULT NULL
94  )
95  AS
96  BEGIN
97 
98 	IF column_name is null then
99 	    NULL;
100 	ELSIF upper(Column_name) = 'CLOSED_IND' then
101 	    new_references.closed_ind := column_value;
102 	ELSIF upper(Column_name) = 'COURSE_GROUP_TYPE' then
103 	    new_references.course_group_type := column_value;
104 	ELSIF upper(Column_name) = 'S_COURSE_GROUP_TYPE' then
105 	    new_references.s_course_group_type := column_value;
106 	END IF;
107 
108     IF upper(column_name) = 'COURSE_GROUP_TYPE' OR
109     column_name is null Then
110 	   IF ( new_references.course_group_type <> UPPER(new_references.course_group_type) ) Then
111       	 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
112       	 IGS_GE_MSG_STACK.ADD;
113              App_Exception.Raise_Exception;
114           END IF;
115       END IF;
116 
117     IF upper(column_name) = 'S_COURSE_GROUP_TYPE' OR
118     column_name is null Then
119 	   IF ( new_references.s_course_group_type <> UPPER(new_references.s_course_group_type) ) Then
120       	 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
121       	 IGS_GE_MSG_STACK.ADD;
122              App_Exception.Raise_Exception;
123           END IF;
124       END IF;
125 
126     IF upper(column_name) = 'CLOSED_IND' OR
127     column_name is null Then
128 	   IF ( new_references.closed_ind NOT IN ( 'Y' , 'N' ) ) Then
129       	 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
130       	 IGS_GE_MSG_STACK.ADD;
131              App_Exception.Raise_Exception;
132           END IF;
133       END IF;
134 
135   END Check_Constraints;
136 
137   PROCEDURE Check_Parent_Existance AS
138   BEGIN
139 
140     IF (((old_references.s_course_group_type = new_references.s_course_group_type)) OR
141         ((new_references.s_course_group_type IS NULL))) THEN
142       NULL;
143     ELSE
144 	 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
145 	  'COURSE_GROUP_TYPE',
146         new_references.s_course_group_type
147         ) THEN
148 	        Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
149 	        IGS_GE_MSG_STACK.ADD;
150 	        App_Exception.Raise_Exception;
151 	END IF;
152     END IF;
153 NULL;
154   END Check_Parent_Existance;
155 
156   PROCEDURE Check_Child_Existance AS
157   BEGIN
158 
159     IGS_PS_GRP_PKG.GET_FK_IGS_PS_GRP_TYPE (
160       old_references.course_group_type
161       );
162 
163   END Check_Child_Existance;
164 
165   FUNCTION Get_PK_For_Validation (
166     x_course_group_type IN VARCHAR2
167     )
168   RETURN BOOLEAN AS
169 
170     CURSOR cur_rowid IS
171       SELECT   rowid
172       FROM     IGS_PS_GRP_TYPE
173       WHERE    course_group_type = x_course_group_type
174       FOR UPDATE NOWAIT;
175 
176     lv_rowid cur_rowid%RowType;
177 
178   BEGIN
179 
180     Open cur_rowid;
181     Fetch cur_rowid INTO lv_rowid;
182     IF (cur_rowid%FOUND) THEN
183       Close cur_rowid;
184       Return (TRUE);
185     ELSE
186 	Close cur_rowid;
187       Return (FALSE);
188     END IF;
189 
190   END Get_PK_For_Validation;
191 
192   PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
193     x_s_course_group_type IN VARCHAR2
194     ) AS
195 
196     CURSOR cur_rowid IS
197       SELECT   rowid
198       FROM     IGS_PS_GRP_TYPE
199       WHERE    s_course_group_type = x_s_course_group_type ;
200 
201     lv_rowid cur_rowid%RowType;
202 
203   BEGIN
204 
205     Open cur_rowid;
206     Fetch cur_rowid INTO lv_rowid;
207     IF (cur_rowid%FOUND) THEN
208       Fnd_Message.Set_Name ('IGS', 'IGS_PS_CGT_LOOKUPS_FK');
209       IGS_GE_MSG_STACK.ADD;
210       App_Exception.Raise_Exception;
211       Close cur_rowid;
212       Return;
213     END IF;
214     Close cur_rowid;
215 
216   END GET_FK_IGS_LOOKUPS_VIEW;
217 
218   PROCEDURE Before_DML (
219     p_action IN VARCHAR2,
220     x_rowid IN VARCHAR2 DEFAULT NULL,
221     x_course_group_type IN VARCHAR2 DEFAULT NULL,
222     x_description IN VARCHAR2 DEFAULT NULL,
223     x_s_course_group_type IN VARCHAR2 DEFAULT NULL,
224     x_closed_ind IN VARCHAR2 DEFAULT NULL,
225     x_creation_date IN DATE DEFAULT NULL,
226     x_created_by IN NUMBER DEFAULT NULL,
227     x_last_update_date IN DATE DEFAULT NULL,
228     x_last_updated_by IN NUMBER DEFAULT NULL,
229     x_last_update_login IN NUMBER DEFAULT NULL
230   ) AS
231   BEGIN
232 
233     Set_Column_Values (
234       p_action,
235       x_rowid,
236       x_course_group_type,
237       x_description,
238       x_s_course_group_type,
239       x_closed_ind,
240       x_creation_date,
241       x_created_by,
242       x_last_update_date,
243       x_last_updated_by,
244       x_last_update_login
245     );
246 
247     IF (p_action = 'INSERT') THEN
248       -- Call all the procedures related to Before Insert.
249       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
250 	IF Get_PK_For_Validation (
251       new_references.course_group_type ) THEN
252 	   Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
253 	   IGS_GE_MSG_STACK.ADD;
254          App_Exception.Raise_Exception;
255 	END IF;
256       Check_Constraints;
257       Check_Parent_Existance;
258     ELSIF (p_action = 'UPDATE') THEN
259       -- Call all the procedures related to Before Update.
260       BeforeRowInsertUpdate1 ( p_updating => TRUE );
261       Check_Constraints;
262       Check_Parent_Existance;
263     ELSIF (p_action = 'DELETE') THEN
264       -- Call all the procedures related to Before Delete.
265       Check_Child_Existance;
266     ELSIF (p_action = 'VALIDATE_INSERT') THEN
267 	IF  Get_PK_For_Validation (
268       new_references.course_group_type ) THEN
269 	    Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
270 	    IGS_GE_MSG_STACK.ADD;
271 	    App_Exception.Raise_Exception;
272 	END IF;
273 	Check_Constraints;
274     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
275 	Check_Constraints;
276     ELSIF (p_action = 'VALIDATE_DELETE') THEN
277       Check_Child_Existance;
278     END IF;
279 
280   END Before_DML;
281 
282   PROCEDURE After_DML (
283     p_action IN VARCHAR2,
284     x_rowid IN VARCHAR2
285   ) AS
286   BEGIN
287 
288     l_rowid := x_rowid;
289 
290 
291 
292   END After_DML;
293 
294 procedure INSERT_ROW (
295   X_ROWID in out NOCOPY VARCHAR2,
296   X_COURSE_GROUP_TYPE in VARCHAR2,
297   X_DESCRIPTION in VARCHAR2,
298   X_S_COURSE_GROUP_TYPE in VARCHAR2,
299   X_CLOSED_IND in VARCHAR2,
300   X_MODE in VARCHAR2 default 'R'
301   ) AS
302     cursor C is select ROWID from IGS_PS_GRP_TYPE
303       where COURSE_GROUP_TYPE = X_COURSE_GROUP_TYPE;
304     X_LAST_UPDATE_DATE DATE;
305     X_LAST_UPDATED_BY NUMBER;
306     X_LAST_UPDATE_LOGIN NUMBER;
307 begin
308   X_LAST_UPDATE_DATE := SYSDATE;
309   if(X_MODE = 'I') then
310     X_LAST_UPDATED_BY := 1;
311     X_LAST_UPDATE_LOGIN := 0;
312   elsif (X_MODE = 'R') then
313     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
314     if X_LAST_UPDATED_BY is NULL then
315       X_LAST_UPDATED_BY := -1;
316     end if;
317     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
318     if X_LAST_UPDATE_LOGIN is NULL then
319       X_LAST_UPDATE_LOGIN := -1;
320     end if;
321   else
322     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
323     IGS_GE_MSG_STACK.ADD;
324     app_exception.raise_exception;
325   end if;
326 Before_DML (
327     p_action => 'INSERT',
328     x_rowid => X_ROWID,
329     x_course_group_type => X_COURSE_GROUP_TYPE,
330     x_description => X_DESCRIPTION ,
331     x_s_course_group_type => X_S_COURSE_GROUP_TYPE ,
332     x_closed_ind => NVL(X_CLOSED_IND,'N') ,
333     x_creation_date => X_LAST_UPDATE_DATE  ,
334     x_created_by => X_LAST_UPDATED_BY ,
335     x_last_update_date => X_LAST_UPDATE_DATE  ,
336     x_last_updated_by => X_LAST_UPDATED_BY ,
337     x_last_update_login => X_LAST_UPDATE_LOGIN
338  );
339 
340   insert into IGS_PS_GRP_TYPE (
341     COURSE_GROUP_TYPE,
342     DESCRIPTION,
343     S_COURSE_GROUP_TYPE,
344     CLOSED_IND,
345     CREATION_DATE,
346     CREATED_BY,
347     LAST_UPDATE_DATE,
348     LAST_UPDATED_BY,
349     LAST_UPDATE_LOGIN
350   ) values (
351     NEW_REFERENCES.COURSE_GROUP_TYPE,
352     NEW_REFERENCES.DESCRIPTION,
353     NEW_REFERENCES.S_COURSE_GROUP_TYPE,
354     NEW_REFERENCES.CLOSED_IND,
355     X_LAST_UPDATE_DATE,
356     X_LAST_UPDATED_BY,
357     X_LAST_UPDATE_DATE,
358     X_LAST_UPDATED_BY,
359     X_LAST_UPDATE_LOGIN
360   );
361 
362   open c;
363   fetch c into X_ROWID;
364   if (c%notfound) then
365     close c;
366     raise no_data_found;
367   end if;
368   close c;
369 After_DML (
370 	p_action => 'INSERT',
371 	x_rowid => X_ROWID
372 );
373 end INSERT_ROW;
374 
375 procedure LOCK_ROW (
376   X_ROWID IN VARCHAR2,
377   X_COURSE_GROUP_TYPE in VARCHAR2,
378   X_DESCRIPTION in VARCHAR2,
379   X_S_COURSE_GROUP_TYPE in VARCHAR2,
380   X_CLOSED_IND in VARCHAR2
381 ) AS
382   cursor c1 is select
383       DESCRIPTION,
384       S_COURSE_GROUP_TYPE,
385       CLOSED_IND
386     from IGS_PS_GRP_TYPE
387     where ROWID = X_ROWID
388     for update nowait;
389   tlinfo c1%rowtype;
390 
391 begin
392   open c1;
393   fetch c1 into tlinfo;
394   if (c1%notfound) then
395     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
396     IGS_GE_MSG_STACK.ADD;
397     app_exception.raise_exception;
398     close c1;
399     return;
400   end if;
401   close c1;
402 
403   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
404       AND (tlinfo.S_COURSE_GROUP_TYPE = X_S_COURSE_GROUP_TYPE)
405       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
406   ) then
407     null;
408   else
409     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
410     IGS_GE_MSG_STACK.ADD;
411     app_exception.raise_exception;
412   end if;
413   return;
414 end LOCK_ROW;
415 
416 procedure UPDATE_ROW (
417   X_ROWID IN VARCHAR2,
418   X_COURSE_GROUP_TYPE in VARCHAR2,
419   X_DESCRIPTION in VARCHAR2,
420   X_S_COURSE_GROUP_TYPE in VARCHAR2,
421   X_CLOSED_IND in VARCHAR2,
422   X_MODE in VARCHAR2 default 'R'
423   ) AS
424     X_LAST_UPDATE_DATE DATE;
425     X_LAST_UPDATED_BY NUMBER;
426     X_LAST_UPDATE_LOGIN NUMBER;
427 begin
428   X_LAST_UPDATE_DATE := SYSDATE;
429   if(X_MODE = 'I') then
430     X_LAST_UPDATED_BY := 1;
431     X_LAST_UPDATE_LOGIN := 0;
432   elsif (X_MODE = 'R') then
433     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
434     if X_LAST_UPDATED_BY is NULL then
435       X_LAST_UPDATED_BY := -1;
436     end if;
437     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
438     if X_LAST_UPDATE_LOGIN is NULL then
439       X_LAST_UPDATE_LOGIN := -1;
440     end if;
441   else
442     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
443     IGS_GE_MSG_STACK.ADD;
444     app_exception.raise_exception;
445   end if;
446 Before_DML (
447     p_action => 'UPDATE',
448     x_rowid => X_ROWID,
449     x_course_group_type => X_COURSE_GROUP_TYPE,
450     x_description => X_DESCRIPTION ,
451     x_s_course_group_type => X_S_COURSE_GROUP_TYPE ,
452     x_closed_ind => X_CLOSED_IND ,
453     x_creation_date => X_LAST_UPDATE_DATE  ,
454     x_created_by => X_LAST_UPDATED_BY ,
455     x_last_update_date => X_LAST_UPDATE_DATE  ,
456     x_last_updated_by => X_LAST_UPDATED_BY ,
457     x_last_update_login => X_LAST_UPDATE_LOGIN
458  );
459 
460   update IGS_PS_GRP_TYPE set
461     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
462     S_COURSE_GROUP_TYPE = NEW_REFERENCES.S_COURSE_GROUP_TYPE,
463     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
464     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
465     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
466     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
467   where ROWID = X_ROWID
468   ;
469   if (sql%notfound) then
470     raise no_data_found;
471   end if;
472 After_DML (
473 	p_action => 'UPDATE',
474 	x_rowid => X_ROWID
475 );
476 end UPDATE_ROW;
477 
478 procedure ADD_ROW (
479   X_ROWID in out NOCOPY VARCHAR2,
480   X_COURSE_GROUP_TYPE in VARCHAR2,
481   X_DESCRIPTION in VARCHAR2,
482   X_S_COURSE_GROUP_TYPE in VARCHAR2,
483   X_CLOSED_IND in VARCHAR2,
484   X_MODE in VARCHAR2 default 'R'
485   ) AS
486   cursor c1 is select rowid from IGS_PS_GRP_TYPE
487      where COURSE_GROUP_TYPE = X_COURSE_GROUP_TYPE
488   ;
489 begin
490   open c1;
491   fetch c1 into X_ROWID;
492   if (c1%notfound) then
493     close c1;
494     INSERT_ROW (
495      X_ROWID,
496      X_COURSE_GROUP_TYPE,
497      X_DESCRIPTION,
498      X_S_COURSE_GROUP_TYPE,
499      X_CLOSED_IND,
500      X_MODE);
501     return;
502   end if;
503   close c1;
504   UPDATE_ROW (
505    X_ROWID,
506    X_COURSE_GROUP_TYPE,
507    X_DESCRIPTION,
508    X_S_COURSE_GROUP_TYPE,
509    X_CLOSED_IND,
510    X_MODE);
511 end ADD_ROW;
512 
513 procedure DELETE_ROW (
514   X_ROWID in VARCHAR2
515 ) AS
516 begin
517 Before_DML (
518 	p_action => 'DELETE',
519 	x_rowid => X_ROWID
520 );
521   delete from IGS_PS_GRP_TYPE
522   where ROWID = X_ROWID;
523   if (sql%notfound) then
524     raise no_data_found;
525   end if;
526 After_DML (
527 	p_action => 'DELETE',
528 	x_rowid => X_ROWID
529 );
530 end DELETE_ROW;
531 
532 end IGS_PS_GRP_TYPE_PKG;