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_RE_SEO_CLASS_CD_PKG

Source


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