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