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_CA_ARTS_TC_CA_CD_PKG

Source


1 package body IGS_CA_ARTS_TC_CA_CD_PKG AS
2 /* $Header: IGSCI01B.pls 115.3 2002/11/28 22:59:23 nsidana ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_CA_ARTS_TC_CA_CD%RowType;
5   new_references IGS_CA_ARTS_TC_CA_CD%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_arts_teaching_cal_type_cd IN VARCHAR2 DEFAULT NULL,
11     x_description IN VARCHAR2 DEFAULT NULL,
12     x_closed_ind IN VARCHAR2 DEFAULT NULL,
13     x_creation_date IN DATE DEFAULT NULL,
14     x_created_by IN NUMBER DEFAULT NULL,
15     x_last_update_date IN DATE DEFAULT NULL,
16     x_last_updated_by IN NUMBER DEFAULT NULL,
17     x_last_update_login IN NUMBER DEFAULT NULL
18   ) AS
19 
20     CURSOR cur_old_ref_values IS
21       SELECT   *
22       FROM     IGS_CA_ARTS_TC_CA_CD
23       WHERE    rowid = x_rowid;
24 
25   BEGIN
26 
27     l_rowid := x_rowid;
28 
29     -- Code for setting the Old and New Reference Values.
30     -- Populate Old Values.
31     Open cur_old_ref_values;
32     Fetch cur_old_ref_values INTO old_references;
33     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
34       Close cur_old_ref_values;
35       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36       IGS_GE_MSG_STACK.ADD;
37       App_Exception.Raise_Exception;
38       Return;
39     END IF;
40     Close cur_old_ref_values;
41 
42     -- Populate New Values.
43     new_references.arts_teaching_cal_type_cd := x_arts_teaching_cal_type_cd;
44     new_references.description := x_description;
45     new_references.closed_ind := x_closed_ind;
46     IF (p_action = 'UPDATE') THEN
47       new_references.creation_date := old_references.creation_date;
48       new_references.created_by := old_references.created_by;
49     ELSE
50       new_references.creation_date := x_creation_date;
51       new_references.created_by := x_created_by;
52     END IF;
53     new_references.last_update_date := x_last_update_date;
54     new_references.last_updated_by := x_last_updated_by;
55     new_references.last_update_login := x_last_update_login;
56 
57   END Set_Column_Values;
58 
59 
60   PROCEDURE Check_Child_Existance AS
61   BEGIN
62 
63     IGS_CA_TYPE_PKG.GET_FK_IGS_CA_ARTS_TC_CA_CD (
64       old_references.arts_teaching_cal_type_cd
65       );
66 
67   END Check_Child_Existance;
68 
69   FUNCTION Get_PK_For_Validation (
70     x_arts_teaching_cal_type_cd IN VARCHAR2
71     ) RETURN BOOLEAN AS
72 
73     CURSOR cur_rowid IS
74       SELECT   rowid
75       FROM     IGS_CA_ARTS_TC_CA_CD
76       WHERE    arts_teaching_cal_type_cd = x_arts_teaching_cal_type_cd
77       FOR UPDATE NOWAIT;
78 
79     lv_rowid cur_rowid%RowType;
80 
81   BEGIN
82 
83 Open cur_rowid;
84 Fetch cur_rowid INTO lv_rowid;
85 	IF (cur_rowid%FOUND) THEN
86 	      Close cur_rowid;
87 	      Return (TRUE);
88 	ELSE
89 	      Close cur_rowid;
90 	      Return (FALSE);
91 	END IF;
92 
93   END Get_PK_For_Validation;
94 
95 PROCEDURE Check_Constraints (
96 	Column_Name	    IN  VARCHAR2		DEFAULT NULL,
97 	Column_Value    IN  VARCHAR2		DEFAULT NULL
98 				) AS
99 		BEGIN
100 			IF  column_name is null then
101                         NULL;
102                   ELSIF UPPER(column_name) = 'CLOSED_IND' Then
103 				new_references.closed_ind := column_value;
104 			Elsif UPPER(column_name) = 'ARTS_TEACHING_CAL_TYPE_CD' Then
105 				new_references.arts_teaching_cal_type_cd := column_value;
106 			end if;
107 			If upper(column_name) = 'CLOSED_IND' or column_name is null Then
108 				if new_references.closed_ind not in ('Y', 'N') then
109        				Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
110        				IGS_GE_MSG_STACK.ADD;
111        				App_Exception.Raise_Exception;
112 				end if;
113 			end if;
114 			if upper(column_name) = 'ARTS_TEACHING_CAL_TYPE_CD'  or column_name is null Then
115 				if new_references.arts_teaching_cal_type_cd   <> upper(new_references.arts_teaching_cal_type_cd ) then
116        				Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
117        				IGS_GE_MSG_STACK.ADD;
118        				App_Exception.Raise_Exception;
119 				end if;
120 			end if;
121 
122 END Check_Constraints;
123 
124 
125 PROCEDURE Before_DML (
126     p_action IN VARCHAR2,
127     x_rowid IN  VARCHAR2 DEFAULT NULL,
128     x_arts_teaching_cal_type_cd IN VARCHAR2 DEFAULT NULL,
129     x_description IN VARCHAR2 DEFAULT NULL,
130     x_closed_ind IN VARCHAR2 DEFAULT NULL,
131     x_creation_date IN DATE  DEFAULT NULL,
132     x_created_by IN NUMBER DEFAULT NULL,
133     x_last_update_date IN DATE DEFAULT NULL,
134     x_last_updated_by IN NUMBER DEFAULT NULL,
135     x_last_update_login IN NUMBER DEFAULT NULL
136   ) AS
137   BEGIN
138 
139     Set_Column_Values (
140       p_action,
141       x_rowid,
142       x_arts_teaching_cal_type_cd,
143       x_description,
144       x_closed_ind,
145       x_creation_date,
146       x_created_by,
147       x_last_update_date,
148       x_last_updated_by,
149       x_last_update_login
150     );
151 
152     IF (p_action = 'INSERT') THEN
153       -- Call all the procedures related to Before Insert.
154        IF  Get_PK_For_Validation ( new_references.arts_teaching_cal_type_cd ) THEN
155   		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
156   		IGS_GE_MSG_STACK.ADD;
157 	      App_Exception.Raise_Exception;
158 	 END IF;
159 	Check_Constraints;
160       ELSIF (p_action = 'VALIDATE_INSERT') THEN
161       IF  Get_PK_For_Validation ( new_references.arts_teaching_cal_type_cd ) 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       Check_Constraints;
170       ELSIF (p_action = 'VALIDATE_UPDATE') THEN
171       Check_Constraints;
172       ELSIF (p_action = 'DELETE') THEN
173       -- Call all the procedures related to Before Delete.
174       Check_Child_Existance;
175       ELSIF (p_action = 'VALIDATE_DELETE') THEN
176       Check_Child_Existance;
177    END IF;
178 
179   END Before_DML;
180 
181 
182 procedure INSERT_ROW (
183   X_ROWID in out NOCOPY VARCHAR2,
184   X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
185   X_DESCRIPTION in VARCHAR2,
186   X_CLOSED_IND in VARCHAR2,
187   X_MODE in VARCHAR2 default 'R'
188   ) AS
189     cursor C is select ROWID from IGS_CA_ARTS_TC_CA_CD
190       where ARTS_TEACHING_CAL_TYPE_CD = X_ARTS_TEACHING_CAL_TYPE_CD;
191     X_LAST_UPDATE_DATE DATE;
192     X_LAST_UPDATED_BY NUMBER;
193     X_LAST_UPDATE_LOGIN NUMBER;
194 begin
195   X_LAST_UPDATE_DATE := SYSDATE;
196   if(X_MODE = 'I') then
197     X_LAST_UPDATED_BY := 1;
198     X_LAST_UPDATE_LOGIN := 0;
199   elsif (X_MODE = 'R') then
200     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
201     if X_LAST_UPDATED_BY is NULL then
202       X_LAST_UPDATED_BY := -1;
203     end if;
204     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
205     if X_LAST_UPDATE_LOGIN is NULL then
206       X_LAST_UPDATE_LOGIN := -1;
207     end if;
208   else
209     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
210     IGS_GE_MSG_STACK.ADD;
211     app_exception.raise_exception;
212   end if;
213   Before_DML (
214     p_action =>'INSERT',
215     x_rowid =>X_ROWID,
216     x_arts_teaching_cal_type_cd =>X_ARTS_TEACHING_CAL_TYPE_CD,
217     x_description =>X_DESCRIPTION,
218     x_closed_ind =>NVL(X_CLOSED_IND,'N'),
219     x_creation_date =>X_LAST_UPDATE_DATE,
220     x_created_by =>X_LAST_UPDATED_BY,
221     x_last_update_date =>X_LAST_UPDATE_DATE,
222     x_last_updated_by =>X_LAST_UPDATED_BY,
223     x_last_update_login =>X_LAST_UPDATE_LOGIN
224   );
225   insert into IGS_CA_ARTS_TC_CA_CD (
226     ARTS_TEACHING_CAL_TYPE_CD,
227     DESCRIPTION,
228     CLOSED_IND,
229     CREATION_DATE,
230     CREATED_BY,
231     LAST_UPDATE_DATE,
232     LAST_UPDATED_BY,
233     LAST_UPDATE_LOGIN
234   ) values (
235     NEW_REFERENCES.ARTS_TEACHING_CAL_TYPE_CD,
236     NEW_REFERENCES.DESCRIPTION,
237     NEW_REFERENCES.CLOSED_IND,
238     X_LAST_UPDATE_DATE,
239     X_LAST_UPDATED_BY,
240     X_LAST_UPDATE_DATE,
241     X_LAST_UPDATED_BY,
242     X_LAST_UPDATE_LOGIN
243   );
244 
245   open c;
246   fetch c into X_ROWID;
247   if (c%notfound) then
248     close c;
249     raise no_data_found;
250   end if;
251   close c;
252 
253 end INSERT_ROW;
254 
255 procedure LOCK_ROW (
256   X_ROWID in VARCHAR2,
257   X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
258   X_DESCRIPTION in VARCHAR2,
259   X_CLOSED_IND in VARCHAR2
260 ) AS
261   cursor c1 is select
262       DESCRIPTION,
263       CLOSED_IND
264     from IGS_CA_ARTS_TC_CA_CD
265     where ROWID = X_ROWID
266     for update nowait;
267   tlinfo c1%rowtype;
268 
269 begin
270   open c1;
271   fetch c1 into tlinfo;
272   if (c1%notfound) then
273     close c1;
274     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
275     IGS_GE_MSG_STACK.ADD;
276     app_exception.raise_exception;
277     return;
278   end if;
279   close c1;
280 
281   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
282       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
283   ) then
284     null;
285   else
286     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
287     IGS_GE_MSG_STACK.ADD;
288     app_exception.raise_exception;
289   end if;
290   return;
291 end LOCK_ROW;
292 
293 procedure UPDATE_ROW (
294   X_ROWID in  VARCHAR2,
295   X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
296   X_DESCRIPTION in VARCHAR2,
297   X_CLOSED_IND in VARCHAR2,
298   X_MODE in VARCHAR2 default 'R'
299   ) AS
300     X_LAST_UPDATE_DATE DATE;
301     X_LAST_UPDATED_BY NUMBER;
302     X_LAST_UPDATE_LOGIN NUMBER;
303 begin
304   X_LAST_UPDATE_DATE := SYSDATE;
305   if(X_MODE = 'I') then
306     X_LAST_UPDATED_BY := 1;
307     X_LAST_UPDATE_LOGIN := 0;
308   elsif (X_MODE = 'R') then
309     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
310     if X_LAST_UPDATED_BY is NULL then
311       X_LAST_UPDATED_BY := -1;
312     end if;
313     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
314     if X_LAST_UPDATE_LOGIN is NULL then
315       X_LAST_UPDATE_LOGIN := -1;
316     end if;
317   else
318     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
319     IGS_GE_MSG_STACK.ADD;
320     app_exception.raise_exception;
321   end if;
322 
323 Before_DML (
324     p_action =>'UPDATE',
325     x_rowid =>X_ROWID,
326     x_arts_teaching_cal_type_cd =>X_ARTS_TEACHING_CAL_TYPE_CD,
327     x_description =>X_DESCRIPTION,
328     x_closed_ind =>X_CLOSED_IND,
329     x_creation_date =>X_LAST_UPDATE_DATE,
330     x_created_by =>X_LAST_UPDATED_BY,
331     x_last_update_date =>X_LAST_UPDATE_DATE,
332     x_last_updated_by =>X_LAST_UPDATED_BY,
333     x_last_update_login =>X_LAST_UPDATE_LOGIN
334   );
335 
336   update IGS_CA_ARTS_TC_CA_CD set
337     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
338     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
339     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
340     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
341     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
342   where ROWID = X_ROWID
343   ;
344   if (sql%notfound) then
345     raise no_data_found;
346   end if;
347 end UPDATE_ROW;
348 
349 procedure ADD_ROW (
350   X_ROWID in out NOCOPY VARCHAR2,
351   X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
352   X_DESCRIPTION in VARCHAR2,
353   X_CLOSED_IND in VARCHAR2,
354   X_MODE in VARCHAR2 default 'R'
355   ) AS
356   cursor c1 is select rowid from IGS_CA_ARTS_TC_CA_CD
357      where ARTS_TEACHING_CAL_TYPE_CD = X_ARTS_TEACHING_CAL_TYPE_CD
358   ;
359   begin
360   open c1;
361   fetch c1 into X_ROWID;
362   if (c1%notfound) then
363     close c1;
364     INSERT_ROW (
365      X_ROWID,
366      X_ARTS_TEACHING_CAL_TYPE_CD,
367      X_DESCRIPTION,
368      X_CLOSED_IND,
369      X_MODE);
370     return;
371   end if;
372   close c1;
373   UPDATE_ROW (
374    X_ROWID,
375    X_ARTS_TEACHING_CAL_TYPE_CD,
376    X_DESCRIPTION,
377    X_CLOSED_IND,
378    X_MODE);
379 end ADD_ROW;
380 
381 procedure DELETE_ROW(X_ROWID in  VARCHAR2) AS
382 begin
383 Before_DML (
384     p_action =>'DELETE',
385     x_rowid =>X_ROWID
386   );
387   delete from IGS_CA_ARTS_TC_CA_CD
388   where ROWID = X_ROWID;
389   if (sql%notfound) then
390     raise no_data_found;
391   end if;
392 end DELETE_ROW;
393 
394 end IGS_CA_ARTS_TC_CA_CD_PKG;