1 PACKAGE BODY IGS_GE_CFG_TAB_PKG AS
2 /* $Header: IGSNIA1B.pls 120.1 2005/09/08 15:46:17 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ge_cfg_tab%ROWTYPE;
5 new_references igs_ge_cfg_tab%ROWTYPE;
6 PROCEDURE set_column_values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2,
9 x_responsibility_id IN NUMBER,
10 x_form_code IN VARCHAR2,
11 x_tab_code IN VARCHAR2,
12 x_config_opt IN VARCHAR2,
13 x_creation_date IN DATE,
14 x_created_by IN NUMBER,
15 x_last_update_date IN DATE,
16 x_last_updated_by IN NUMBER,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*
20 || Created By : [email protected]
21 || Created On : 07-OCT-2002
22 || Purpose : Initialises the Old and New references for the columns of the table.
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 */
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM igs_ge_cfg_tab
31 WHERE rowid = x_rowid;
32 BEGIN
33 l_rowid := x_rowid;
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 OPEN cur_old_ref_values;
37 FETCH cur_old_ref_values INTO old_references;
38 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
39 CLOSE cur_old_ref_values;
40 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
41 igs_ge_msg_stack.add;
42 app_exception.raise_exception;
43 RETURN;
44 END IF;
45 CLOSE cur_old_ref_values;
46 -- Populate New Values.
47 new_references.responsibility_id := x_responsibility_id;
48 new_references.form_code := x_form_code;
49 new_references.tab_code := x_tab_code;
50 new_references.config_opt := x_config_opt;
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61 END set_column_values;
62 PROCEDURE check_parent_existance AS
63 /*
64 || Created By : [email protected]
65 || Created On : 07-OCT-2002
66 || Purpose : Checks for the existance of Parent records.
67 || Known limitations, enhancements or remarks :
68 || Change History :
69 || Who When What
70 || (reverse chronological order - newest change first)
71 */
72 BEGIN
73 IF (((old_references.responsibility_id = new_references.responsibility_id) AND
74 (old_references.form_code = new_references.form_code)) OR
75 ((new_references.responsibility_id IS NULL) OR
76 (new_references.form_code IS NULL))) THEN
77 NULL;
78 ELSIF NOT igs_ge_cfg_form_pkg.get_pk_for_validation (
79 new_references.responsibility_id,
80 new_references.form_code
81 ) THEN
82 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
83 igs_ge_msg_stack.add;
84 app_exception.raise_exception;
85 END IF;
86 END check_parent_existance;
87 FUNCTION get_pk_for_validation (
88 x_responsibility_id IN NUMBER,
89 x_form_code IN VARCHAR2,
90 x_tab_code IN VARCHAR2
91 ) RETURN BOOLEAN AS
92 /*
93 || Created By : [email protected]
94 || Created On : 07-OCT-2002
95 || Purpose : Validates the Primary Key of the table.
96 || Known limitations, enhancements or remarks :
97 || Change History :
98 || Who When What
99 || (reverse chronological order - newest change first)
100 */
101 CURSOR cur_rowid IS
102 SELECT rowid
103 FROM igs_ge_cfg_tab
104 WHERE responsibility_id = x_responsibility_id
105 AND form_code = x_form_code
106 AND tab_code = x_tab_code
107 FOR UPDATE NOWAIT;
108 lv_rowid cur_rowid%RowType;
109 BEGIN
110 OPEN cur_rowid;
111 FETCH cur_rowid INTO lv_rowid;
112 IF (cur_rowid%FOUND) THEN
113 CLOSE cur_rowid;
114 RETURN(TRUE);
115 ELSE
116 CLOSE cur_rowid;
117 RETURN(FALSE);
118 END IF;
119 END get_pk_for_validation;
120 PROCEDURE get_fk_igs_ge_cfg_form (
121 x_responsibility_id IN NUMBER,
122 x_form_code IN VARCHAR2
123 ) AS
124 /*
125 || Created By : [email protected]
126 || Created On : 07-OCT-2002
127 || Purpose : Validates the Foreign Keys for the table.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || asbala 19-JUN-2003 Changed the name of error message to IGS_PE_CFM_CTB_FK
132 || (reverse chronological order - newest change first)
133 */
134 CURSOR cur_rowid IS
135 SELECT rowid
136 FROM igs_ge_cfg_tab
137 WHERE ((form_code = x_form_code) AND
138 (responsibility_id = x_responsibility_id));
139 lv_rowid cur_rowid%RowType;
140 BEGIN
141 OPEN cur_rowid;
142 FETCH cur_rowid INTO lv_rowid;
143 IF (cur_rowid%FOUND) THEN
144 CLOSE cur_rowid;
145 fnd_message.set_name ('IGS', 'IGS_PE_CFM_CTB_FK');
146 igs_ge_msg_stack.add;
147 app_exception.raise_exception;
148 RETURN;
149 END IF;
150 CLOSE cur_rowid;
151 END get_fk_igs_ge_cfg_form;
152 PROCEDURE before_dml (
153 p_action IN VARCHAR2,
154 x_rowid IN VARCHAR2,
155 x_responsibility_id IN NUMBER,
156 x_form_code IN VARCHAR2,
157 x_tab_code IN VARCHAR2,
158 x_config_opt IN VARCHAR2,
159 x_creation_date IN DATE,
160 x_created_by IN NUMBER,
161 x_last_update_date IN DATE,
162 x_last_updated_by IN NUMBER,
163 x_last_update_login IN NUMBER
164 ) AS
165 /*
166 || Created By : [email protected]
167 || Created On : 07-OCT-2002
168 || Purpose : Initialises the columns, Checks Constraints, Calls the
169 || Trigger Handlers for the table, before any DML operation.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 BEGIN
176 set_column_values (
177 p_action,
178 x_rowid,
179 x_responsibility_id,
180 x_form_code,
181 x_tab_code,
182 x_config_opt,
183 x_creation_date,
184 x_created_by,
185 x_last_update_date,
186 x_last_updated_by,
187 x_last_update_login
188 );
189 IF (p_action = 'INSERT') THEN
190 -- Call all the procedures related to Before Insert.
191 IF ( get_pk_for_validation(
192 new_references.responsibility_id,
193 new_references.form_code,
194 new_references.tab_code
195 )
196 ) THEN
197 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
198 igs_ge_msg_stack.add;
199 app_exception.raise_exception;
200 END IF;
201 check_parent_existance;
202 ELSIF (p_action = 'UPDATE') THEN
203 -- Call all the procedures related to Before Update.
204 check_parent_existance;
205 ELSIF (p_action = 'VALIDATE_INSERT') THEN
206 -- Call all the procedures related to Before Insert.
207 IF ( get_pk_for_validation (
208 new_references.responsibility_id,
209 new_references.form_code,
210 new_references.tab_code
211 )
212 ) THEN
213 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
214 igs_ge_msg_stack.add;
215 app_exception.raise_exception;
216 END IF;
217 END IF;
218 END before_dml;
219 PROCEDURE insert_row (
220 x_rowid IN OUT NOCOPY VARCHAR2,
221 x_responsibility_id IN NUMBER,
222 x_form_code IN VARCHAR2,
223 x_tab_code IN VARCHAR2,
224 x_config_opt IN VARCHAR2,
225 x_mode IN VARCHAR2
226 ) AS
227 /*
228 || Created By : [email protected]
229 || Created On : 07-OCT-2002
230 || Purpose : Handles the INSERT DML logic for the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || (reverse chronological order - newest change first)
235 */
236 x_last_update_date DATE;
237 x_last_updated_by NUMBER;
238 x_last_update_login NUMBER;
239 BEGIN
240 x_last_update_date := SYSDATE;
241 IF (x_mode = 'I') THEN
242 x_last_updated_by := 1;
243 x_last_update_login := 0;
244 ELSIF (x_mode = 'R') THEN
245 x_last_updated_by := fnd_global.user_id;
246 IF (x_last_updated_by IS NULL) THEN
247 x_last_updated_by := -1;
248 END IF;
249 x_last_update_login := fnd_global.login_id;
250 IF (x_last_update_login IS NULL) THEN
251 x_last_update_login := -1;
252 END IF;
253 ELSE
254 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
255 igs_ge_msg_stack.add;
256 app_exception.raise_exception;
257 END IF;
258 before_dml(
259 p_action => 'INSERT',
260 x_rowid => x_rowid,
261 x_responsibility_id => x_responsibility_id,
262 x_form_code => x_form_code,
263 x_tab_code => x_tab_code,
264 x_config_opt => x_config_opt,
265 x_creation_date => x_last_update_date,
266 x_created_by => x_last_updated_by,
267 x_last_update_date => x_last_update_date,
268 x_last_updated_by => x_last_updated_by,
269 x_last_update_login => x_last_update_login
270 );
271 INSERT INTO igs_ge_cfg_tab (
272 responsibility_id,
273 form_code,
274 tab_code,
275 config_opt,
276 creation_date,
277 created_by,
278 last_update_date,
279 last_updated_by,
280 last_update_login
281 ) VALUES (
282 new_references.responsibility_id,
283 new_references.form_code,
284 new_references.tab_code,
285 new_references.config_opt,
286 x_last_update_date,
287 x_last_updated_by,
288 x_last_update_date,
289 x_last_updated_by,
290 x_last_update_login
291 ) RETURNING ROWID INTO x_rowid;
292 END insert_row;
293 PROCEDURE lock_row (
294 x_rowid IN VARCHAR2,
295 x_responsibility_id IN NUMBER,
296 x_form_code IN VARCHAR2,
297 x_tab_code IN VARCHAR2,
298 x_config_opt IN VARCHAR2
299 ) AS
300 /*
301 || Created By : [email protected]
302 || Created On : 07-OCT-2002
303 || Purpose : Handles the LOCK mechanism for the table.
304 || Known limitations, enhancements or remarks :
305 || Change History :
306 || Who When What
307 || (reverse chronological order - newest change first)
308 */
309 CURSOR c1 IS
310 SELECT
311 config_opt
312 FROM igs_ge_cfg_tab
313 WHERE rowid = x_rowid
314 FOR UPDATE NOWAIT;
315 tlinfo c1%ROWTYPE;
316 BEGIN
317 OPEN c1;
318 FETCH c1 INTO tlinfo;
319 IF (c1%notfound) THEN
320 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
321 igs_ge_msg_stack.add;
322 CLOSE c1;
323 app_exception.raise_exception;
324 RETURN;
325 END IF;
326 CLOSE c1;
327 IF (
328 ((tlinfo.config_opt = x_config_opt) OR ((tlinfo.config_opt IS NULL) AND (X_config_opt IS NULL)))
329 ) THEN
330 NULL;
331 ELSE
332 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
333 igs_ge_msg_stack.add;
334 app_exception.raise_exception;
335 END IF;
336 RETURN;
337 END lock_row;
338 PROCEDURE update_row (
339 x_rowid IN VARCHAR2,
340 x_responsibility_id IN NUMBER,
341 x_form_code IN VARCHAR2,
342 x_tab_code IN VARCHAR2,
343 x_config_opt IN VARCHAR2,
344 x_mode IN VARCHAR2
345 ) AS
346 /*
347 || Created By : [email protected]
348 || Created On : 07-OCT-2002
349 || Purpose : Handles the UPDATE DML logic for the table.
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || (reverse chronological order - newest change first)
354 */
355 x_last_update_date DATE ;
356 x_last_updated_by NUMBER;
357 x_last_update_login NUMBER;
358 BEGIN
359 x_last_update_date := SYSDATE;
360 IF (X_MODE = 'I') THEN
361 x_last_updated_by := 1;
362 x_last_update_login := 0;
363 ELSIF (x_mode = 'R') THEN
364 x_last_updated_by := fnd_global.user_id;
365 IF x_last_updated_by IS NULL THEN
366 x_last_updated_by := -1;
367 END IF;
368 x_last_update_login := fnd_global.login_id;
369 IF (x_last_update_login IS NULL) THEN
370 x_last_update_login := -1;
371 END IF;
372 ELSE
373 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377 before_dml(
378 p_action => 'UPDATE',
379 x_rowid => x_rowid,
380 x_responsibility_id => x_responsibility_id,
381 x_form_code => x_form_code,
382 x_tab_code => x_tab_code,
383 x_config_opt => x_config_opt,
384 x_creation_date => x_last_update_date,
385 x_created_by => x_last_updated_by,
386 x_last_update_date => x_last_update_date,
387 x_last_updated_by => x_last_updated_by,
388 x_last_update_login => x_last_update_login
389 );
390 UPDATE igs_ge_cfg_tab
391 SET
392 config_opt = new_references.config_opt,
393 last_update_date = x_last_update_date,
394 last_updated_by = x_last_updated_by,
395 last_update_login = x_last_update_login
396 WHERE rowid = x_rowid;
397 IF (SQL%NOTFOUND) THEN
398 RAISE NO_DATA_FOUND;
399 END IF;
400 END update_row;
401 PROCEDURE add_row (
402 x_rowid IN OUT NOCOPY VARCHAR2,
403 x_responsibility_id IN NUMBER,
404 x_form_code IN VARCHAR2,
405 x_tab_code IN VARCHAR2,
406 x_config_opt IN VARCHAR2,
407 x_mode IN VARCHAR2
408 ) AS
409 /*
410 || Created By : [email protected]
411 || Created On : 07-OCT-2002
412 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
413 || Known limitations, enhancements or remarks :
414 || Change History :
415 || Who When What
416 || (reverse chronological order - newest change first)
417 */
418 CURSOR c1 IS
419 SELECT rowid
420 FROM igs_ge_cfg_tab
421 WHERE responsibility_id = x_responsibility_id
422 AND form_code = x_form_code
423 AND tab_code = x_tab_code;
424 BEGIN
425 OPEN c1;
426 FETCH c1 INTO x_rowid;
427 IF (c1%NOTFOUND) THEN
428 CLOSE c1;
429 insert_row (
430 x_rowid,
431 x_responsibility_id,
432 x_form_code,
433 x_tab_code,
434 x_config_opt,
435 x_mode
436 );
437 RETURN;
438 END IF;
439 CLOSE c1;
440 update_row (
441 x_rowid,
442 x_responsibility_id,
443 x_form_code,
444 x_tab_code,
445 x_config_opt,
446 x_mode
447 );
448 END add_row;
449 PROCEDURE delete_row (
450 x_rowid IN VARCHAR2
451 ) AS
452 /*
453 || Created By : [email protected]
454 || Created On : 07-OCT-2002
455 || Purpose : Handles the DELETE DML logic for the table.
456 || Known limitations, enhancements or remarks :
457 || Change History :
458 || Who When What
459 || (reverse chronological order - newest change first)
460 */
461 BEGIN
462 before_dml (
463 p_action => 'DELETE',
464 x_rowid => x_rowid
465 );
466 DELETE FROM igs_ge_cfg_tab
467 WHERE rowid = x_rowid;
468 IF (SQL%NOTFOUND) THEN
469 RAISE NO_DATA_FOUND;
470 END IF;
471 END delete_row;
472 END igs_ge_cfg_tab_pkg;
[Home] [Help]