1 package body IGS_RU_LOV_PKG as
2 /* $Header: IGSUI08B.pls 115.8 2002/11/29 04:26:52 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_LOV%RowType;
6 new_references IGS_RU_LOV%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_sequence_number IN NUMBER ,
12 x_description IN VARCHAR2 ,
13 x_help_text IN VARCHAR2 ,
14 x_selectable IN VARCHAR2 ,
15 x_creation_date IN DATE ,
16 x_created_by IN NUMBER ,
17 x_last_update_date IN DATE ,
18 x_last_updated_by IN NUMBER ,
19 x_last_update_login IN NUMBER
20 ) IS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_RU_LOV
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_LOV : P_ACTION INSERT VALIDATE_INSERT : IGSUI08B.PLS');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Close cur_old_ref_values;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.sequence_number := x_sequence_number;
47 new_references.description := x_description;
48 new_references.help_text := x_help_text;
49 new_references.selectable := x_selectable;
50 IF (p_action = 'UPDATE') THEN
51 new_references.creation_date := old_references.creation_date;
52 new_references.created_by := old_references.created_by;
53 ELSE
54 new_references.creation_date := x_creation_date;
55 new_references.created_by := x_created_by;
56 END IF;
57 new_references.last_update_date := x_last_update_date;
58 new_references.last_updated_by := x_last_updated_by;
59 new_references.last_update_login := x_last_update_login;
60
61 END Set_Column_Values;
62
63 PROCEDURE Check_Constraints (
64 Column_Name IN VARCHAR2 ,
65 Column_Value IN VARCHAR2
66 )
67 IS
68 BEGIN
69 IF Column_Name is null THEN
70 NULL;
71 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
72 new_references.SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
73
74 ELSIF upper(Column_name) = 'SELECTABLE' THEN
75 new_references.SELECTABLE:= COLUMN_VALUE ;
76
77 END IF ;
78
79 IF upper(Column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
80 IF new_references.SEQUENCE_NUMBER < 1 or new_references.SEQUENCE_NUMBER > 999999 then
81 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception ;
84 END IF;
85
86 END IF ;
87
88 IF upper(Column_name) = 'SELECTABLE' OR COLUMN_NAME IS NULL THEN
89 IF new_references.SELECTABLE not in ('Y','N') 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
95 END IF ;
96 END Check_Constraints;
97
98 FUNCTION Get_PK_For_Validation (
99 x_description IN VARCHAR2,
100 x_sequence_number IN NUMBER
101 )RETURN BOOLEAN
102 IS
103 CURSOR cur_rowid IS
104 SELECT rowid
105 FROM IGS_RU_LOV
106 WHERE description = x_description
107 AND sequence_number = x_sequence_number
108 FOR UPDATE NOWAIT;
109
110 lv_rowid cur_rowid%RowType;
111
112 BEGIN
113
114 Open cur_rowid;
115 Fetch cur_rowid INTO lv_rowid;
116 IF (cur_rowid%FOUND) THEN
117 Close cur_rowid;
118 Return (TRUE);
119 ELSE
120 Close cur_rowid;
121 Return (FALSE);
122 END IF;
123 END Get_PK_For_Validation;
124
125 PROCEDURE Before_DML (
126 p_action IN VARCHAR2,
127 x_rowid IN VARCHAR2 ,
128 x_sequence_number IN NUMBER ,
129 x_description IN VARCHAR2 ,
130 x_help_text IN VARCHAR2 ,
131 x_selectable IN VARCHAR2 ,
132 x_creation_date IN DATE ,
133 x_created_by IN NUMBER ,
134 x_last_update_date IN DATE ,
135 x_last_updated_by IN NUMBER ,
136 x_last_update_login IN NUMBER
137 ) IS
138 BEGIN
139 Set_Column_Values (
140 p_action,
141 x_rowid,
142 x_sequence_number,
143 x_description,
144 x_help_text,
145 x_selectable,
146 x_creation_date,
147 x_created_by,
148 x_last_update_date,
149 x_last_updated_by,
150 x_last_update_login
151 );
152
153 IF (p_action = 'INSERT') THEN
154 -- Call all the procedures related to Before Insert.
155 /** Removed Call to Get_PK_for_Validation .
156 Reason : After insert - in actual code - it is having NULL in dup_val_on_index. i.e. if
157 duplicate record found then dont raise any error and continue with next case
158 Date : 25-jan-2000
159 **/
160 Check_Constraints;
161 ELSIF (p_action = 'UPDATE') THEN
162 -- Call all the procedures related to Before Update.
163 Check_Constraints; -- if procedure present
164 ELSIF (p_action = 'VALIDATE_INSERT') THEN
165 /** Removed Call to Get_PK_for_Validation .
166 Reason : After insert - in actual code - it is having NULL in dup_val_on_index. i.e. if
167 duplicate record found then dont raise any error and continue with next case
168 Date : 25-jan-2000
169 **/
170 Check_Constraints;
171 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
172 Check_Constraints;
173 END IF;
174 END Before_DML;
175
176 PROCEDURE After_DML (
177 p_action IN VARCHAR2,
178 x_rowid IN VARCHAR2
179 ) IS
180 BEGIN
181
182 l_rowid := x_rowid;
183
184 END After_DML;
185
186 procedure INSERT_ROW (
187 X_ROWID in out NOCOPY VARCHAR2,
188 X_DESCRIPTION in VARCHAR2,
189 X_SEQUENCE_NUMBER in NUMBER,
190 X_HELP_TEXT in VARCHAR2,
191 X_SELECTABLE in VARCHAR2,
192 X_MODE in VARCHAR2
193 ) is
194 cursor C is select ROWID from IGS_RU_LOV
195 where DESCRIPTION = X_DESCRIPTION
196 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
197 X_LAST_UPDATE_DATE DATE;
198 X_LAST_UPDATED_BY NUMBER;
199 X_LAST_UPDATE_LOGIN NUMBER;
200 begin
201 X_LAST_UPDATE_DATE := SYSDATE;
202 if(X_MODE = 'I') then
203 X_LAST_UPDATED_BY := 1;
204 X_LAST_UPDATE_LOGIN := 0;
205 elsif (X_MODE = 'R') then
206 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
207 if X_LAST_UPDATED_BY is NULL then
208 X_LAST_UPDATED_BY := -1;
209 end if;
210 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
211 if X_LAST_UPDATE_LOGIN is NULL then
212 X_LAST_UPDATE_LOGIN := -1;
213 end if;
214 else
215 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
216 IGS_GE_MSG_STACK.ADD;
217 app_exception.raise_exception;
218 end if;
219
220
221 Before_DML(
222 p_action=>'INSERT',
223 x_rowid=>X_ROWID,
224 x_description=>X_DESCRIPTION,
225 x_help_text=>X_HELP_TEXT,
226 x_selectable=>X_SELECTABLE,
227 x_sequence_number=>X_SEQUENCE_NUMBER,
228 x_creation_date=>X_LAST_UPDATE_DATE,
229 x_created_by=>X_LAST_UPDATED_BY,
230 x_last_update_date=>X_LAST_UPDATE_DATE,
231 x_last_updated_by=>X_LAST_UPDATED_BY,
232 x_last_update_login=>X_LAST_UPDATE_LOGIN
233 );
234
235 insert into IGS_RU_LOV (
236 SEQUENCE_NUMBER,
237 DESCRIPTION,
238 HELP_TEXT,
239 SELECTABLE,
240 CREATION_DATE,
241 CREATED_BY,
242 LAST_UPDATE_DATE,
243 LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN
245 ) values (
246 NEW_REFERENCES.SEQUENCE_NUMBER,
247 NEW_REFERENCES.DESCRIPTION,
248 NEW_REFERENCES.HELP_TEXT,
249 NEW_REFERENCES.SELECTABLE,
250 X_LAST_UPDATE_DATE,
251 X_LAST_UPDATED_BY,
252 X_LAST_UPDATE_DATE,
253 X_LAST_UPDATED_BY,
254 X_LAST_UPDATE_LOGIN
255 );
256
257 open c;
258 fetch c into X_ROWID;
259 if (c%notfound) then
260 close c;
261 raise no_data_found;
262 end if;
263 close c;
264
265 After_DML (
266 p_action => 'INSERT',
267 x_rowid => X_ROWID);
268
269 end INSERT_ROW;
270
271 procedure LOCK_ROW (
272 X_ROWID in VARCHAR2,
273 X_DESCRIPTION in VARCHAR2,
274 X_SEQUENCE_NUMBER in NUMBER,
275 X_HELP_TEXT in VARCHAR2,
276 X_SELECTABLE in VARCHAR2
277 ) is
278 cursor c1 is select
279 HELP_TEXT,
280 SELECTABLE
281 from IGS_RU_LOV
282 where ROWID = X_ROWID for update nowait;
283 tlinfo c1%rowtype;
284
285 begin
286 open c1;
287 fetch c1 into tlinfo;
288 if (c1%notfound) then
289 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
290 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_LOV : P_ACTION LOCK_ROW : IGSUI08B.PLS');
291 IGS_GE_MSG_STACK.ADD;
292 app_exception.raise_exception;
293 close c1;
294 return;
295 end if;
296 close c1;
297
298 if ( ((tlinfo.HELP_TEXT = X_HELP_TEXT)
299 OR ((tlinfo.HELP_TEXT is null)
300 AND (X_HELP_TEXT is null)))
301 AND ((tlinfo.SELECTABLE = X_SELECTABLE)
302 OR ((tlinfo.SELECTABLE is null)
303 AND (X_SELECTABLE is null)))
304 ) then
305 null;
306 else
307 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
308 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_LOV : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI08B.PLS');
309 IGS_GE_MSG_STACK.ADD;
310 app_exception.raise_exception;
311 end if;
312 return;
313 end LOCK_ROW;
314
315 procedure UPDATE_ROW (
316 X_ROWID in VARCHAR2,
317 X_DESCRIPTION in VARCHAR2,
318 X_SEQUENCE_NUMBER in NUMBER,
319 X_HELP_TEXT in VARCHAR2,
320 X_SELECTABLE in VARCHAR2,
321 X_MODE in VARCHAR2
322 ) is
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(
347 p_action=>'UPDATE',
348 x_rowid=>X_ROWID,
349 x_description=>X_DESCRIPTION,
350 x_help_text=>X_HELP_TEXT,
351 x_selectable=>X_SELECTABLE,
352 x_sequence_number=>X_SEQUENCE_NUMBER,
353 x_creation_date=>X_LAST_UPDATE_DATE,
354 x_created_by=>X_LAST_UPDATED_BY,
355 x_last_update_date=>X_LAST_UPDATE_DATE,
356 x_last_updated_by=>X_LAST_UPDATED_BY,
357 x_last_update_login=>X_LAST_UPDATE_LOGIN
358 );
359
360 update IGS_RU_LOV set
361 HELP_TEXT = NEW_REFERENCES.HELP_TEXT,
362 SELECTABLE = NEW_REFERENCES.SELECTABLE,
363 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
364 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
366 where ROWID = X_ROWID
367 ;
368 if (sql%notfound) then
369 raise no_data_found;
370 end if;
371
372 After_DML (
373 p_action => 'UPDATE',
374 x_rowid => X_ROWID);
375
376 end UPDATE_ROW;
377
378 procedure ADD_ROW (
379 X_ROWID in out NOCOPY VARCHAR2,
380 X_DESCRIPTION in VARCHAR2,
381 X_SEQUENCE_NUMBER in NUMBER,
382 X_HELP_TEXT in VARCHAR2,
383 X_SELECTABLE in VARCHAR2,
384 X_MODE in VARCHAR2
385 ) is
386 cursor c1 is select rowid from IGS_RU_LOV
387 where DESCRIPTION = X_DESCRIPTION
388 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
389 ;
390
391 begin
392 open c1;
393 fetch c1 into X_ROWID;
394 if (c1%notfound) then
395 close c1;
396 INSERT_ROW (
397 X_ROWID,
398 X_DESCRIPTION,
399 X_SEQUENCE_NUMBER,
400 X_HELP_TEXT,
401 X_SELECTABLE,
402 X_MODE);
403 return;
404 end if;
405 close c1;
406 UPDATE_ROW (
407 X_ROWID,
408 X_DESCRIPTION,
409 X_SEQUENCE_NUMBER,
410 X_HELP_TEXT,
411 X_SELECTABLE,
412 X_MODE);
413 end ADD_ROW;
414
415 procedure DELETE_ROW (
416 X_ROWID in VARCHAR2
417 ) is
418 begin
419
420 Before_DML (
421 p_action => 'DELETE',
422 x_rowid => X_ROWID);
423
424 delete from IGS_RU_LOV
425 where ROWID = X_ROWID;
426 if (sql%notfound) then
427 raise no_data_found;
428 end if;
429
430 After_DML (
431 p_action => 'DELETE',
432 x_rowid => X_ROWID);
433
434 end DELETE_ROW;
435
436 end IGS_RU_LOV_PKG;
[Home] [Help]