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