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