1 PACKAGE BODY igs_fi_hier_accounts_pkg AS
2 /* $Header: IGSSIA8B.pls 115.9 2003/03/19 08:36:23 smadathi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_hier_accounts%ROWTYPE;
6 new_references igs_fi_hier_accounts%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_acct_hier_id IN NUMBER DEFAULT NULL,
12 x_name IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_default_flag IN VARCHAR2 DEFAULT NULL,
15 x_zero_fill_flag IN VARCHAR2 DEFAULT NULL,
16 x_closed_ind 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 : kkillams
25 || Created On : 19-JUL-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_HIER_ACCOUNTS
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.acct_hier_id := x_acct_hier_id;
57 new_references.name := x_name;
58 new_references.description := x_description;
59 new_references.default_flag := x_default_flag;
60 new_references.zero_fill_flag := x_zero_fill_flag;
61 new_references.closed_ind := x_closed_ind;
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 FUNCTION check_used_in_ftci (
78 x_acct_hier_id IN NUMBER
79 ) RETURN BOOLEAN AS
80 /*
81 || Created By : jbegum
82 || Created On : 8-March-2002
83 || Purpose : Validates whether the account hierarchy has been asssigned to an FTCI .
84 || Done as part of bug fix of bug#2148665
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90 CURSOR cur_acct_hier IS
91 SELECT 'x'
92 FROM igs_fi_f_typ_ca_inst
93 WHERE acct_hier_id = x_acct_hier_id;
94
95 l_acct_hier cur_acct_hier%ROWTYPE;
96
97 BEGIN
98
99 OPEN cur_acct_hier;
100 FETCH cur_acct_hier INTO l_acct_hier;
101 IF (cur_acct_hier%FOUND) THEN
102 CLOSE cur_acct_hier;
103 RETURN(TRUE);
104 ELSE
105 CLOSE cur_acct_hier;
106 RETURN(FALSE);
107 END IF;
108
109 END check_used_in_ftci;
110
111 PROCEDURE check_child_existance IS
112 /*
113 || Created By : kkillams
114 || Created On : 19-JUL-2001
115 || Purpose : Checks for the existance of Child records.
116 || Known limitations, enhancements or remarks :
117 || Change History :
118 || Who When What
119 || (reverse chronological order - newest change first)
120 */
121 BEGIN
122
123 igs_fi_hier_acct_tbl_pkg.get_fk_igs_fi_hier_accounts (
124 old_references.acct_hier_id
125 );
126
127 END check_child_existance;
128
129 PROCEDURE check_uniqueness AS
130 /*
131 || Created By : smadathi
132 || Created On : 06-Mar-2003
133 || Purpose : Handles the Unique Constraint logic defined for the columns.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 BEGIN
140
141 IF ( get_uk_for_validation (new_references.name)) THEN
142
143 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
144 igs_ge_msg_stack.add;
145 app_exception.raise_exception;
146
147 END IF;
148
149 END check_uniqueness;
150
151
152 FUNCTION get_pk_for_validation (
153 x_acct_hier_id IN NUMBER
154 ) RETURN BOOLEAN AS
155 /*
156 || Created By : kkillams
157 || Created On : 19-JUL-2001
158 || Purpose : Validates the Primary Key of the table.
159 || Known limitations, enhancements or remarks :
160 || Change History :
161 || Who When What
162 || (reverse chronological order - newest change first)
163 */
164 CURSOR cur_rowid IS
165 SELECT rowid
166 FROM igs_fi_hier_accounts
167 WHERE acct_hier_id = x_acct_hier_id
168 FOR UPDATE NOWAIT;
169
170 lv_rowid cur_rowid%RowType;
171
172 BEGIN
173
174 OPEN cur_rowid;
175 FETCH cur_rowid INTO lv_rowid;
176 IF (cur_rowid%FOUND) THEN
177 CLOSE cur_rowid;
178 RETURN(TRUE);
179 ELSE
180 CLOSE cur_rowid;
181 RETURN(FALSE);
182 END IF;
183
184 END get_pk_for_validation;
185
186
187 FUNCTION get_uk_for_validation ( x_name IN VARCHAR2
188 ) RETURN BOOLEAN AS
189 /*
190 || Created By : SMADATHI
191 || Created On : 06-MAR-2003
192 || Purpose : Validates the Unique Keys of the table.
193 || Known limitations, enhancements or remarks :
194 || Change History :
195 || Who When What
196 || (reverse chronological order - newest change first)
197 */
198 CURSOR c_rowid IS
199 SELECT rowid
200 FROM igs_fi_hier_accounts
201 WHERE name = x_name
202 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
203
204 l_v_rowid c_rowid%ROWTYPE;
205
206 BEGIN
207
208 OPEN c_rowid;
209 FETCH c_rowid INTO l_v_rowid;
210 IF (c_rowid%FOUND) THEN
211 CLOSE c_rowid;
212 RETURN (TRUE);
213 ELSE
214 CLOSE c_rowid;
215 RETURN(FALSE);
216 END IF;
217
218 END get_uk_for_validation ;
219
220
221 PROCEDURE before_dml (
222 p_action IN VARCHAR2,
223 x_rowid IN VARCHAR2 DEFAULT NULL,
224 x_acct_hier_id IN NUMBER DEFAULT NULL,
225 x_name IN VARCHAR2 DEFAULT NULL,
226 x_description IN VARCHAR2 DEFAULT NULL,
227 x_default_flag IN VARCHAR2 DEFAULT NULL,
228 x_zero_fill_flag IN VARCHAR2 DEFAULT NULL,
229 x_closed_ind IN VARCHAR2 DEFAULT NULL,
230 x_creation_date IN DATE DEFAULT NULL,
231 x_created_by IN NUMBER DEFAULT NULL,
232 x_last_update_date IN DATE DEFAULT NULL,
233 x_last_updated_by IN NUMBER DEFAULT NULL,
234 x_last_update_login IN NUMBER DEFAULT NULL
235 ) AS
236 /*
237 || Created By : kkillams
238 || Created On : 19-JUL-2001
239 || Purpose : Initialises the columns, Checks Constraints, Calls the
240 || Trigger Handlers for the table, before any DML operation.
241 || Known limitations, enhancements or remarks :
242 || Change History :
243 || Who When What
244 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null
245 || jbegum 8-March-02 As part of bug fix of bug#2148665
246 || added call to function check_used_in_ftci
247 || (reverse chronological order - newest change first)
248 */
249 BEGIN
250
251 set_column_values (
252 p_action,
253 x_rowid,
254 x_acct_hier_id,
255 x_name,
256 x_description,
257 x_default_flag,
258 x_zero_fill_flag,
259 x_closed_ind,
260 x_creation_date,
261 x_created_by,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_login
265 );
266
267 IF (p_action = 'INSERT') THEN
268 -- Call all the procedures related to Before Insert.
269 IF ( get_pk_for_validation(
270 new_references.acct_hier_id
271 )
272 ) THEN
273 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
274 igs_ge_msg_stack.add;
275 app_exception.raise_exception;
276 END IF;
277 check_uniqueness;
278 ELSIF (p_action = 'DELETE') THEN
279 -- Call all the procedures related to Before Delete.
280 check_child_existance;
281 IF ( check_used_in_ftci (
282 old_references.acct_hier_id
283 )
284 ) THEN
285 fnd_message.set_name('IGS','IGS_FI_ACCT_HIER_NO_DEL');
286 igs_ge_msg_stack.add;
287 app_exception.raise_exception;
288 END IF;
289 ELSIF (p_action = 'VALIDATE_INSERT') THEN
290 -- Call all the procedures related to Before Insert.
291 IF ( get_pk_for_validation (
292 new_references.acct_hier_id
293 )
294 ) THEN
295 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
296 igs_ge_msg_stack.add;
297 app_exception.raise_exception;
298 END IF;
299 check_uniqueness;
300 ELSIF (p_action = 'VALIDATE_DELETE') THEN
301 check_child_existance;
302 IF ( check_used_in_ftci (
303 old_references.acct_hier_id
304 )
305 ) THEN
306 fnd_message.set_name('IGS','IGS_FI_ACCT_HIER_NO_DEL');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310 END IF;
311 l_rowid := NULL;
312 END before_dml;
313
314
315 PROCEDURE insert_row (
316 x_rowid IN OUT NOCOPY VARCHAR2,
317 x_acct_hier_id IN OUT NOCOPY NUMBER,
318 x_name IN VARCHAR2,
319 x_description IN VARCHAR2,
320 x_default_flag IN VARCHAR2,
321 x_zero_fill_flag IN VARCHAR2,
322 x_closed_ind IN VARCHAR2,
323 x_mode IN VARCHAR2 DEFAULT 'R'
324 ) AS
325 /*
326 || Created By : kkillams
327 || Created On : 19-JUL-2001
328 || Purpose : Handles the INSERT DML logic for the table.
329 || Known limitations, enhancements or remarks :
330 || Change History :
331 || Who When What
332 || (reverse chronological order - newest change first)
333 */
334 CURSOR c IS
335 SELECT rowid
336 FROM igs_fi_hier_accounts
337 WHERE acct_hier_id = x_acct_hier_id;
338
339 x_last_update_date DATE;
340 x_last_updated_by NUMBER;
341 x_last_update_login NUMBER;
342
343 BEGIN
344
345 x_last_update_date := SYSDATE;
346 IF (x_mode = 'I') THEN
347 x_last_updated_by := 1;
348 x_last_update_login := 0;
349 ELSIF (x_mode = 'R') THEN
350 x_last_updated_by := fnd_global.user_id;
351 IF (x_last_updated_by IS NULL) THEN
352 x_last_updated_by := -1;
353 END IF;
354 x_last_update_login := fnd_global.login_id;
355 IF (x_last_update_login IS NULL) THEN
356 x_last_update_login := -1;
357 END IF;
358 ELSE
359 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
360 igs_ge_msg_stack.add;
361 app_exception.raise_exception;
362 END IF;
363
364 SELECT igs_fi_hier_accounts_s.NEXTVAL
365 INTO x_acct_hier_id
366 FROM dual;
367
368 before_dml(
369 p_action => 'INSERT',
370 x_rowid => x_rowid,
371 x_acct_hier_id => x_acct_hier_id,
372 x_name => x_name,
373 x_description => x_description,
374 x_default_flag => x_default_flag,
375 x_zero_fill_flag => x_zero_fill_flag,
376 x_closed_ind => x_closed_ind,
377 x_creation_date => x_last_update_date,
378 x_created_by => x_last_updated_by,
379 x_last_update_date => x_last_update_date,
380 x_last_updated_by => x_last_updated_by,
381 x_last_update_login => x_last_update_login
382 );
383
384 INSERT INTO igs_fi_hier_accounts (
385 acct_hier_id,
386 name,
387 description,
388 default_flag,
389 zero_fill_flag,
390 closed_ind,
391 creation_date,
392 created_by,
393 last_update_date,
394 last_updated_by,
395 last_update_login
396 ) VALUES (
397 new_references.acct_hier_id,
398 new_references.name,
399 new_references.description,
400 new_references.default_flag,
401 new_references.zero_fill_flag,
402 new_references.closed_ind,
403 x_last_update_date,
404 x_last_updated_by,
405 x_last_update_date,
406 x_last_updated_by,
407 x_last_update_login
408 );
409
410 OPEN c;
411 FETCH c INTO x_rowid;
412 IF (c%NOTFOUND) THEN
413 CLOSE c;
414 RAISE NO_DATA_FOUND;
415 END IF;
416 CLOSE c;
417
418 END insert_row;
419
420
421 PROCEDURE lock_row (
422 x_rowid IN VARCHAR2,
423 x_acct_hier_id IN NUMBER,
427 x_zero_fill_flag IN VARCHAR2,
424 x_name IN VARCHAR2,
425 x_description IN VARCHAR2,
426 x_default_flag IN VARCHAR2,
428 x_closed_ind IN VARCHAR2
429 ) AS
430 /*
431 || Created By : kkillams
432 || Created On : 19-JUL-2001
433 || Purpose : Handles the LOCK mechanism for the table.
434 || Known limitations, enhancements or remarks :
435 || Change History :
436 || Who When What
437 || (reverse chronological order - newest change first)
438 */
439 CURSOR c1 IS
440 SELECT
441 name,
442 description,
443 default_flag,
444 zero_fill_flag,
445 closed_ind
446 FROM igs_fi_hier_accounts
447 WHERE rowid = x_rowid
448 FOR UPDATE NOWAIT;
449
450 tlinfo c1%ROWTYPE;
451
452 BEGIN
453
454 OPEN c1;
455 FETCH c1 INTO tlinfo;
456 IF (c1%notfound) THEN
457 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
458 igs_ge_msg_stack.add;
459 CLOSE c1;
460 app_exception.raise_exception;
461 RETURN;
462 END IF;
463 CLOSE c1;
464
465 IF (
466 (tlinfo.name = x_name)
467 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
468 AND (tlinfo.default_flag = x_default_flag)
469 AND (tlinfo.zero_fill_flag = x_zero_fill_flag)
470 AND (tlinfo.closed_ind = x_closed_ind)
471 ) THEN
472 NULL;
473 ELSE
474 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
475 igs_ge_msg_stack.add;
476 app_exception.raise_exception;
477 END IF;
478
479 RETURN;
480
481 END lock_row;
482
483
484 PROCEDURE update_row (
485 x_rowid IN VARCHAR2,
486 x_acct_hier_id IN NUMBER,
487 x_name IN VARCHAR2,
488 x_description IN VARCHAR2,
489 x_default_flag IN VARCHAR2,
490 x_zero_fill_flag IN VARCHAR2,
491 x_closed_ind IN VARCHAR2,
492 x_mode IN VARCHAR2 DEFAULT 'R'
493 ) AS
494 /*
495 || Created By : kkillams
496 || Created On : 19-JUL-2001
497 || Purpose : Handles the UPDATE DML logic for the table.
498 || Known limitations, enhancements or remarks :
499 || Change History :
500 || Who When What
501 || (reverse chronological order - newest change first)
502 */
503 x_last_update_date DATE ;
504 x_last_updated_by NUMBER;
505 x_last_update_login NUMBER;
506
507 BEGIN
508
509 x_last_update_date := SYSDATE;
510 IF (X_MODE = 'I') THEN
511 x_last_updated_by := 1;
512 x_last_update_login := 0;
513 ELSIF (x_mode = 'R') THEN
514 x_last_updated_by := fnd_global.user_id;
515 IF x_last_updated_by IS NULL THEN
516 x_last_updated_by := -1;
517 END IF;
518 x_last_update_login := fnd_global.login_id;
519 IF (x_last_update_login IS NULL) THEN
520 x_last_update_login := -1;
521 END IF;
522 ELSE
523 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
524 igs_ge_msg_stack.add;
525 app_exception.raise_exception;
526 END IF;
527
528 before_dml(
529 p_action => 'UPDATE',
530 x_rowid => x_rowid,
531 x_acct_hier_id => x_acct_hier_id,
532 x_name => x_name,
533 x_description => x_description,
534 x_default_flag => x_default_flag,
535 x_zero_fill_flag => x_zero_fill_flag,
536 x_closed_ind => x_closed_ind,
537 x_creation_date => x_last_update_date,
538 x_created_by => x_last_updated_by,
539 x_last_update_date => x_last_update_date,
540 x_last_updated_by => x_last_updated_by,
541 x_last_update_login => x_last_update_login
542 );
543
544 UPDATE igs_fi_hier_accounts
545 SET
546 name = new_references.name,
547 description = new_references.description,
548 default_flag = new_references.default_flag,
549 zero_fill_flag = new_references.zero_fill_flag,
550 closed_ind = new_references.closed_ind,
551 last_update_date = x_last_update_date,
552 last_updated_by = x_last_updated_by,
553 last_update_login = x_last_update_login
554 WHERE rowid = x_rowid;
555
556 IF (SQL%NOTFOUND) THEN
557 RAISE NO_DATA_FOUND;
558 END IF;
559
560 END update_row;
561
562
563 PROCEDURE add_row (
564 x_rowid IN OUT NOCOPY VARCHAR2,
565 x_acct_hier_id IN OUT NOCOPY NUMBER,
566 x_name IN VARCHAR2,
567 x_description IN VARCHAR2,
568 x_default_flag IN VARCHAR2,
569 x_zero_fill_flag IN VARCHAR2,
570 x_closed_ind IN VARCHAR2,
571 x_mode IN VARCHAR2 DEFAULT 'R'
572 ) AS
573 /*
574 || Created By : kkillams
575 || Created On : 19-JUL-2001
576 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
577 || Known limitations, enhancements or remarks :
578 || Change History :
579 || Who When What
580 || (reverse chronological order - newest change first)
581 */
582 CURSOR c1 IS
583 SELECT rowid
584 FROM igs_fi_hier_accounts
585 WHERE acct_hier_id = x_acct_hier_id;
586
587 BEGIN
588
589 OPEN c1;
590 FETCH c1 INTO x_rowid;
591 IF (c1%NOTFOUND) THEN
592 CLOSE c1;
593
594 insert_row (
595 x_rowid,
596 x_acct_hier_id,
597 x_name,
598 x_description,
599 x_default_flag,
600 x_zero_fill_flag,
601 x_closed_ind,
602 x_mode
603 );
604 RETURN;
605 END IF;
606 CLOSE c1;
607
608 update_row (
609 x_rowid,
610 x_acct_hier_id,
611 x_name,
612 x_description,
613 x_default_flag,
614 x_zero_fill_flag,
615 x_closed_ind,
616 x_mode
617 );
618
619 END add_row;
620
621
622 PROCEDURE delete_row (
623 x_rowid IN VARCHAR2
624 ) AS
625 /*
626 || Created By : kkillams
627 || Created On : 19-JUL-2001
628 || Purpose : Handles the DELETE DML logic for the table.
629 || Known limitations, enhancements or remarks :
630 || Change History :
631 || Who When What
632 || (reverse chronological order - newest change first)
633 */
634 BEGIN
635
636 before_dml (
637 p_action => 'DELETE',
638 x_rowid => x_rowid
639 );
640
641 DELETE FROM igs_fi_hier_accounts
642 WHERE rowid = x_rowid;
643
644 IF (SQL%NOTFOUND) THEN
645 RAISE NO_DATA_FOUND;
646 END IF;
647
648 END delete_row;
649
650
651 END igs_fi_hier_accounts_pkg;
[Home] [Help]