1 PACKAGE BODY igs_fi_a_hierarchies_pkg AS
2 /* $Header: IGSSI89B.pls 120.1 2005/09/22 03:37:17 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_a_hierarchies%ROWTYPE;
6 new_references igs_fi_a_hierarchies%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_appl_hierarchy_id IN NUMBER DEFAULT NULL,
12 x_hierarchy_name IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_effective_start_date IN DATE DEFAULT NULL,
15 x_effective_end_date IN DATE DEFAULT NULL,
16 x_description IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23 /*
24 || Created By : BDEVARAK
25 || Created On : 26-APR-2001
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_FI_A_HIERARCHIES
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.appl_hierarchy_id := x_appl_hierarchy_id;
57 new_references.hierarchy_name := x_hierarchy_name;
58 new_references.version_number := x_version_number;
59 new_references.effective_start_date := x_effective_start_date;
60 new_references.effective_end_date := x_effective_end_date;
61 new_references.description := x_description;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE check_uniqueness AS
79 /*
80 || Created By : BDEVARAK
81 || Created On : 26-APR-2001
82 || Purpose : Handles the Unique Constraint logic defined for the columns.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 BEGIN
89
90 IF ( get_uk_for_validation (
91 new_references.hierarchy_name,
92 new_references.version_number
93 )
94 ) THEN
95 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
96 igs_ge_msg_stack.add;
97 app_exception.raise_exception;
98 END IF;
99
100 END check_uniqueness;
101
102 PROCEDURE check_child_existance IS
103 /*
104 || Created By : BDEVARAK
105 || Created On : 26-APR-2001
106 || Purpose : Checks for the existance of Child records.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 BEGIN
113
114 igs_fi_applications_pkg.get_fk_igs_fi_a_hierarchies (
115 old_references.appl_hierarchy_id
116 );
117
118 igs_fi_app_rules_pkg.get_fk_igs_fi_a_hierarchies (
119 old_references.appl_hierarchy_id
120 );
121
122 END check_child_existance;
123
124
125 FUNCTION get_pk_for_validation (
126 x_appl_hierarchy_id IN NUMBER
127 ) RETURN BOOLEAN AS
128 /*
129 || Created By : BDEVARAK
130 || Created On : 26-APR-2001
131 || Purpose : Validates the Primary Key of the table.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 || vvutukur 30-Dec-2002 Bug#2725955.Removed FOR UPDATE from cursor cur_rowid.
137 */
138 CURSOR cur_rowid IS
139 SELECT rowid
140 FROM igs_fi_a_hierarchies
141 WHERE appl_hierarchy_id = x_appl_hierarchy_id;
142
143 lv_rowid cur_rowid%RowType;
144
145 BEGIN
146
147 OPEN cur_rowid;
148 FETCH cur_rowid INTO lv_rowid;
149 IF (cur_rowid%FOUND) THEN
150 CLOSE cur_rowid;
151 RETURN(TRUE);
152 ELSE
153 CLOSE cur_rowid;
154 RETURN(FALSE);
155 END IF;
156
157 END get_pk_for_validation;
158
159
160 FUNCTION get_uk_for_validation (
161 x_hierarchy_name IN VARCHAR2,
162 x_version_number IN NUMBER
163 ) RETURN BOOLEAN AS
164 /*
165 || Created By : BDEVARAK
166 || Created On : 26-APR-2001
167 || Purpose : Validates the Unique Keys of the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_fi_a_hierarchies
176 WHERE hierarchy_name = x_hierarchy_name
177 AND version_number = x_version_number
178 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
179
180 lv_rowid cur_rowid%RowType;
181
182 BEGIN
183
184 OPEN cur_rowid;
185 FETCH cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 CLOSE cur_rowid;
188 RETURN (true);
189 ELSE
190 CLOSE cur_rowid;
191 RETURN(FALSE);
192 END IF;
193
194 END get_uk_for_validation ;
195
196
197 PROCEDURE before_dml (
198 p_action IN VARCHAR2,
199 x_rowid IN VARCHAR2 DEFAULT NULL,
200 x_appl_hierarchy_id IN NUMBER DEFAULT NULL,
201 x_hierarchy_name IN VARCHAR2 DEFAULT NULL,
202 x_version_number IN NUMBER DEFAULT NULL,
203 x_effective_start_date IN DATE DEFAULT NULL,
204 x_effective_end_date IN DATE DEFAULT NULL,
205 x_description IN VARCHAR2 DEFAULT NULL,
206 x_creation_date IN DATE DEFAULT NULL,
207 x_created_by IN NUMBER DEFAULT NULL,
208 x_last_update_date IN DATE DEFAULT NULL,
209 x_last_updated_by IN NUMBER DEFAULT NULL,
210 x_last_update_login IN NUMBER DEFAULT NULL
211 ) AS
212 /*
213 || Created By : BDEVARAK
214 || Created On : 26-APR-2001
215 || Purpose : Initialises the columns, Checks Constraints, Calls the
216 || Trigger Handlers for the table, before any DML operation.
217 || Known limitations, enhancements or remarks :
218 || Change History :
219 || Who When What
220 || (reverse chronological order - newest change first)
221 */
222 BEGIN
223
224 set_column_values (
225 p_action,
226 x_rowid,
227 x_appl_hierarchy_id,
228 x_hierarchy_name,
229 x_version_number,
230 x_effective_start_date,
231 x_effective_end_date,
232 x_description,
233 x_creation_date,
234 x_created_by,
235 x_last_update_date,
236 x_last_updated_by,
237 x_last_update_login
238 );
239
240 IF (p_action = 'INSERT') THEN
241 -- Call all the procedures related to Before Insert.
242 IF ( get_pk_for_validation(
243 new_references.appl_hierarchy_id
244 )
245 ) THEN
246 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250 check_uniqueness;
251 ELSIF (p_action = 'UPDATE') THEN
252 -- Call all the procedures related to Before Update.
253 check_uniqueness;
254 ELSIF (p_action = 'DELETE') THEN
255 -- Call all the procedures related to Before Delete.
256 check_child_existance;
257 ELSIF (p_action = 'VALIDATE_INSERT') THEN
258 -- Call all the procedures related to Before Insert.
259 IF ( get_pk_for_validation (
260 new_references.appl_hierarchy_id
261 )
262 ) THEN
263 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
264 igs_ge_msg_stack.add;
265 app_exception.raise_exception;
266 END IF;
267 check_uniqueness;
268 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
269 check_uniqueness;
270 ELSIF (p_action = 'VALIDATE_DELETE') THEN
271 check_child_existance;
272 END IF;
273
274 END before_dml;
275
276
277 PROCEDURE insert_row (
278 x_rowid IN OUT NOCOPY VARCHAR2,
279 x_appl_hierarchy_id IN OUT NOCOPY NUMBER,
280 x_hierarchy_name IN VARCHAR2,
281 x_version_number IN NUMBER,
282 x_effective_start_date IN DATE,
283 x_effective_end_date IN DATE,
284 x_description IN VARCHAR2,
285 x_mode IN VARCHAR2 DEFAULT 'R'
286 ) AS
287 /*
288 || Created By : BDEVARAK
289 || Created On : 26-APR-2001
290 || Purpose : Handles the INSERT DML logic for the table.
291 || Known limitations, enhancements or remarks :
292 || Change History :
293 || Who When What
294 || (reverse chronological order - newest change first)
295 */
296 CURSOR c IS
297 SELECT rowid
298 FROM igs_fi_a_hierarchies
299 WHERE appl_hierarchy_id = x_appl_hierarchy_id;
300
301 x_last_update_date DATE;
302 x_last_updated_by NUMBER;
303 x_last_update_login NUMBER;
304
305 BEGIN
306
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
326 SELECT igs_fi_a_hierarchies_s.NEXTVAL
327 INTO x_appl_hierarchy_id
328 FROM dual;
329
330 before_dml(
331 p_action => 'INSERT',
332 x_rowid => x_rowid,
333 x_appl_hierarchy_id => x_appl_hierarchy_id,
334 x_hierarchy_name => x_hierarchy_name,
335 x_version_number => x_version_number,
336 x_effective_start_date => x_effective_start_date,
337 x_effective_end_date => x_effective_end_date,
338 x_description => x_description,
339 x_creation_date => x_last_update_date,
340 x_created_by => x_last_updated_by,
341 x_last_update_date => x_last_update_date,
342 x_last_updated_by => x_last_updated_by,
343 x_last_update_login => x_last_update_login
344 );
345
346 INSERT INTO igs_fi_a_hierarchies (
347 appl_hierarchy_id,
348 hierarchy_name,
349 version_number,
350 effective_start_date,
351 effective_end_date,
352 description,
353 creation_date,
354 created_by,
355 last_update_date,
356 last_updated_by,
357 last_update_login
358 ) VALUES (
359 new_references.appl_hierarchy_id,
360 new_references.hierarchy_name,
361 new_references.version_number,
362 new_references.effective_start_date,
363 new_references.effective_end_date,
364 new_references.description,
365 x_last_update_date,
366 x_last_updated_by,
367 x_last_update_date,
368 x_last_updated_by,
369 x_last_update_login
370 );
371
372 OPEN c;
373 FETCH c INTO x_rowid;
374 IF (c%NOTFOUND) THEN
375 CLOSE c;
376 RAISE NO_DATA_FOUND;
377 END IF;
378 CLOSE c;
379
380 END insert_row;
381
382
383 PROCEDURE lock_row (
384 x_rowid IN VARCHAR2,
385 x_appl_hierarchy_id IN NUMBER,
386 x_hierarchy_name IN VARCHAR2,
387 x_version_number IN NUMBER,
388 x_effective_start_date IN DATE,
389 x_effective_end_date IN DATE,
390 x_description IN VARCHAR2
391 ) AS
392 /*
393 || Created By : BDEVARAK
394 || Created On : 26-APR-2001
395 || Purpose : Handles the LOCK mechanism for the table.
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || (reverse chronological order - newest change first)
400 */
401 CURSOR c1 IS
402 SELECT
403 hierarchy_name,
404 version_number,
405 effective_start_date,
406 effective_end_date,
407 description
408 FROM igs_fi_a_hierarchies
409 WHERE rowid = x_rowid
410 FOR UPDATE NOWAIT;
411
412 tlinfo c1%ROWTYPE;
413
414 BEGIN
415
416 OPEN c1;
417 FETCH c1 INTO tlinfo;
418 IF (c1%notfound) THEN
419 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
420 igs_ge_msg_stack.add;
421 CLOSE c1;
422 app_exception.raise_exception;
423 RETURN;
424 END IF;
425 CLOSE c1;
426
427 IF (
428 (tlinfo.hierarchy_name = x_hierarchy_name)
429 AND (tlinfo.version_number = x_version_number)
430 AND (tlinfo.effective_start_date = x_effective_start_date)
431 AND ((tlinfo.effective_end_date = x_effective_end_date) OR ((tlinfo.effective_end_date IS NULL) AND (X_effective_end_date IS NULL)))
432 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
433 ) THEN
434 NULL;
435 ELSE
436 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
437 igs_ge_msg_stack.add;
438 app_exception.raise_exception;
439 END IF;
440
441 RETURN;
442
443 END lock_row;
444
445
446 PROCEDURE update_row (
447 x_rowid IN VARCHAR2,
448 x_appl_hierarchy_id IN NUMBER,
449 x_hierarchy_name IN VARCHAR2,
450 x_version_number IN NUMBER,
451 x_effective_start_date IN DATE,
452 x_effective_end_date IN DATE,
453 x_description IN VARCHAR2,
454 x_mode IN VARCHAR2 DEFAULT 'R'
455 ) AS
456 /*
457 || Created By : BDEVARAK
458 || Created On : 26-APR-2001
459 || Purpose : Handles the UPDATE DML logic for the table.
460 || Known limitations, enhancements or remarks :
461 || Change History :
462 || Who When What
463 || (reverse chronological order - newest change first)
464 */
465 x_last_update_date DATE ;
466 x_last_updated_by NUMBER;
467 x_last_update_login NUMBER;
468
469 BEGIN
470
471 x_last_update_date := SYSDATE;
472 IF (X_MODE = 'I') THEN
473 x_last_updated_by := 1;
474 x_last_update_login := 0;
475 ELSIF (x_mode = 'R') THEN
476 x_last_updated_by := fnd_global.user_id;
477 IF x_last_updated_by IS NULL THEN
478 x_last_updated_by := -1;
479 END IF;
480 x_last_update_login := fnd_global.login_id;
481 IF (x_last_update_login IS NULL) THEN
482 x_last_update_login := -1;
483 END IF;
484 ELSE
485 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
486 igs_ge_msg_stack.add;
487 app_exception.raise_exception;
488 END IF;
489
490 before_dml(
491 p_action => 'UPDATE',
492 x_rowid => x_rowid,
493 x_appl_hierarchy_id => x_appl_hierarchy_id,
494 x_hierarchy_name => x_hierarchy_name,
495 x_version_number => x_version_number,
496 x_effective_start_date => x_effective_start_date,
497 x_effective_end_date => x_effective_end_date,
498 x_description => x_description,
499 x_creation_date => x_last_update_date,
500 x_created_by => x_last_updated_by,
501 x_last_update_date => x_last_update_date,
502 x_last_updated_by => x_last_updated_by,
503 x_last_update_login => x_last_update_login
504 );
505
506 UPDATE igs_fi_a_hierarchies
507 SET
508 hierarchy_name = new_references.hierarchy_name,
509 version_number = new_references.version_number,
510 effective_start_date = new_references.effective_start_date,
511 effective_end_date = new_references.effective_end_date,
512 description = new_references.description,
513 last_update_date = x_last_update_date,
514 last_updated_by = x_last_updated_by,
515 last_update_login = x_last_update_login
516 WHERE rowid = x_rowid;
517
518 IF (SQL%NOTFOUND) THEN
519 RAISE NO_DATA_FOUND;
520 END IF;
521
522 END update_row;
523
524
525 PROCEDURE add_row (
526 x_rowid IN OUT NOCOPY VARCHAR2,
527 x_appl_hierarchy_id IN OUT NOCOPY NUMBER,
528 x_hierarchy_name IN VARCHAR2,
529 x_version_number IN NUMBER,
530 x_effective_start_date IN DATE,
531 x_effective_end_date IN DATE,
532 x_description IN VARCHAR2,
533 x_mode IN VARCHAR2 DEFAULT 'R'
534 ) AS
535 /*
536 || Created By : BDEVARAK
537 || Created On : 26-APR-2001
538 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
539 || Known limitations, enhancements or remarks :
540 || Change History :
541 || Who When What
542 || (reverse chronological order - newest change first)
543 */
544 CURSOR c1 IS
545 SELECT rowid
546 FROM igs_fi_a_hierarchies
547 WHERE appl_hierarchy_id = x_appl_hierarchy_id;
548
549 BEGIN
550
551 OPEN c1;
552 FETCH c1 INTO x_rowid;
553 IF (c1%NOTFOUND) THEN
554 CLOSE c1;
555
556 insert_row (
557 x_rowid,
558 x_appl_hierarchy_id,
559 x_hierarchy_name,
560 x_version_number,
561 x_effective_start_date,
562 x_effective_end_date,
563 x_description,
564 x_mode
565 );
566 RETURN;
567 END IF;
568 CLOSE c1;
569
570 update_row (
571 x_rowid,
572 x_appl_hierarchy_id,
573 x_hierarchy_name,
574 x_version_number,
575 x_effective_start_date,
576 x_effective_end_date,
577 x_description,
578 x_mode
579 );
580
581 END add_row;
582
583
584 PROCEDURE delete_row (
585 x_rowid IN VARCHAR2
586 ) AS
587 /*
588 || Created By : BDEVARAK
589 || Created On : 26-APR-2001
590 || Purpose : Handles the DELETE DML logic for the table.
591 || Known limitations, enhancements or remarks :
592 || Change History :
593 || Who When What
594 || (reverse chronological order - newest change first)
595 */
596 BEGIN
597
598 before_dml (
599 p_action => 'DELETE',
600 x_rowid => x_rowid
601 );
602
603 DELETE FROM igs_fi_a_hierarchies
604 WHERE rowid = x_rowid;
605
606 IF (SQL%NOTFOUND) THEN
607 RAISE NO_DATA_FOUND;
608 END IF;
609
610 END delete_row;
611
612
613 END igs_fi_a_hierarchies_pkg;
[Home] [Help]