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_STAT_PKG

Source


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