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_TYPE_GRP_PKG

Source


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