1 PACKAGE BODY igf_aw_coa_group_pkg AS
2 /* $Header: IGFWI05B.pls 115.17 2002/11/28 14:37:26 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_coa_group_all%ROWTYPE;
6 new_references igf_aw_coa_group_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_coa_code IN VARCHAR2,
12 x_ci_cal_type IN VARCHAR2,
13 x_ci_sequence_number IN NUMBER,
14 x_rule_order IN NUMBER,
15 x_s_rule_call_cd IN VARCHAR2,
16 x_rul_sequence_number IN NUMBER,
17 x_pell_coa IN NUMBER,
18 x_pell_alt_exp IN NUMBER,
19 x_creation_date IN DATE,
20 x_created_by IN NUMBER,
21 x_last_update_date IN DATE,
22 x_last_updated_by IN NUMBER,
23 x_last_update_login IN NUMBER,
24 x_coa_grp_desc IN VARCHAR2
25 ) AS
26 --
27 -- Created By : adhawan
28 -- Created On : 30-NOV-2000
29 -- Purpose : Initialises the Old and New references for the columns of the table.
30 -- Known limitations, enhancements or remarks :
31 -- Change History :
32 -- Who When What
33 -- (reverse chronological order - newest change first)
34 --
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGF_AW_COA_GROUP_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.coa_code := x_coa_code;
60 new_references.ci_cal_type := x_ci_cal_type;
61 new_references.ci_sequence_number := x_ci_sequence_number;
62 new_references.rule_order := x_rule_order;
63 new_references.s_rule_call_cd := x_s_rule_call_cd;
64 new_references.rul_sequence_number := x_rul_sequence_number;
65 new_references.pell_coa := x_pell_coa;
66 new_references.pell_alt_exp := x_pell_alt_exp;
67 new_references.coa_grp_desc := x_coa_grp_desc;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81
82 END set_column_values;
83
84
85
86 PROCEDURE check_parent_existance AS
87 --
88 -- Created By : adhawan
89 -- Created On : 30-NOV-2000
90 -- Purpose : Initialises the Old and New references for the columns of the table.
91 -- Known limitations, enhancements or remarks :
92 -- Change History :
93 -- Who When What
94 -- (reverse chronological order - newest change first)
95 --
96 BEGIN
97
98 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
99 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
100 ((new_references.ci_cal_type IS NULL) OR
101 (new_references.ci_sequence_number IS NULL))) THEN
102 NULL;
103 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
104 new_references.ci_cal_type,
105 new_references.ci_sequence_number
106 ) THEN
107 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
108 igs_ge_msg_stack.add;
109 app_exception.raise_exception;
110 END IF;
111
112 END check_parent_existance;
113
114
115 PROCEDURE check_child_existance IS
116 --
117 -- Created By : adhawan
118 -- Created On : 30-NOV-2000
119 -- Purpose : Initialises the Old and New references for the columns of the table.
120 -- Known limitations, enhancements or remarks :
121 -- Change History :
122 -- Who When What
123 -- (reverse chronological order - newest change first)
124 --
125
126 BEGIN
127
128 igf_aw_coa_grp_item_pkg.get_fk_igf_aw_coa_group (
129 old_references.coa_code,
130 old_references.ci_cal_type,
131 old_references.ci_sequence_number
132 );
133
134 igf_aw_coa_ld_pkg.get_fk_igf_aw_coa_group (
135 old_references.coa_code,
136 old_references.ci_cal_type,
137 old_references.ci_sequence_number
138 );
139 END check_child_existance;
140
141
142 FUNCTION get_pk_for_validation (
143 x_coa_code IN VARCHAR2,
144 x_ci_cal_type IN VARCHAR2,
145 x_ci_sequence_number IN NUMBER
146 ) RETURN BOOLEAN AS
147 --
148 -- Created By : adhawan
149 -- Created On : 30-NOV-2000
150 -- Purpose : Initialises the Old and New references for the columns of the table.
151 -- Known limitations, enhancements or remarks :
152 -- Change History :
153 -- Who When What
154 -- (reverse chronological order - newest change first)
155 --
156
157 CURSOR cur_rowid IS
158 SELECT rowid
159 FROM igf_aw_coa_group_all
160 WHERE upper(coa_code) = upper(x_coa_code)
161 AND ci_cal_type = x_ci_cal_type
162 AND ci_sequence_number = x_ci_sequence_number
163 FOR UPDATE NOWAIT;
164
165 lv_rowid cur_rowid%RowType;
166
167 BEGIN
168
169 OPEN cur_rowid;
170 FETCH cur_rowid INTO lv_rowid;
171 IF (cur_rowid%FOUND) THEN
172 CLOSE cur_rowid;
173 RETURN(TRUE);
174 ELSE
175 CLOSE cur_rowid;
176 RETURN(FALSE);
177 END IF;
178
179 END get_pk_for_validation;
180
181
182
183 PROCEDURE get_fk_igs_ca_inst (
184 x_cal_type IN VARCHAR2,
185 x_sequence_number IN NUMBER
186 ) AS
187 --
188 -- Created By : adhawan
189 -- Created On : 30-NOV-2000
190 -- Purpose : Initialises the Old and New references for the columns of the table.
191 -- Known limitations, enhancements or remarks :
192 -- Change History :
193 -- Who When What
194 -- (reverse chronological order - newest change first)
195 --
196 CURSOR cur_rowid IS
197 SELECT rowid
198 FROM igf_aw_coa_group_all
199 WHERE ((ci_cal_type = x_cal_type) AND
200 (ci_sequence_number = x_sequence_number));
201
202 lv_rowid cur_rowid%RowType;
203
204 BEGIN
205
206 OPEN cur_rowid;
207 FETCH cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 CLOSE cur_rowid;
210 fnd_message.set_name ('IGF', 'IGF_AW_COAG_CI_FK');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 RETURN;
214 END IF;
215 CLOSE cur_rowid;
216
217 END get_fk_igs_ca_inst;
218
219
220 PROCEDURE before_dml (
221 p_action IN VARCHAR2,
222 x_rowid IN VARCHAR2,
223 x_coa_code IN VARCHAR2,
224 x_ci_cal_type IN VARCHAR2,
225 x_ci_sequence_number IN NUMBER,
226 x_rule_order IN NUMBER,
227 x_s_rule_call_cd IN VARCHAR2,
228 x_rul_sequence_number IN NUMBER,
229 x_pell_coa IN NUMBER,
230 x_pell_alt_exp IN NUMBER,
231 x_creation_date IN DATE,
232 x_created_by IN NUMBER,
233 x_last_update_date IN DATE,
234 x_last_updated_by IN NUMBER,
235 x_last_update_login IN NUMBER,
236 x_coa_grp_desc IN VARCHAR2
237 ) AS
238
239 --
240 -- Created By : adhawan
241 -- Created On : 30-NOV-2000
242 -- Purpose : Initialises the Old and New references for the columns of the table.
243 -- Known limitations, enhancements or remarks :
244 -- Change History :
245 -- Who When What
246 -- (reverse chronological order - newest change first)
247 --
248
249 BEGIN
250
251 set_column_values (
252 p_action,
253 x_rowid,
254 x_coa_code,
255 x_ci_cal_type,
256 x_ci_sequence_number,
257 x_rule_order,
258 x_s_rule_call_cd,
259 x_rul_sequence_number,
260 x_pell_coa,
261 x_pell_alt_exp,
262 x_creation_date,
263 x_created_by,
264 x_last_update_date,
265 x_last_updated_by,
266 x_last_update_login,
267 x_coa_grp_desc
268 );
269
270 IF (p_action = 'INSERT') THEN
271 -- Call all the procedures related to Before Insert.
272 IF ( get_pk_for_validation(
273 new_references.coa_code,
274 new_references.ci_cal_type,
275 new_references.ci_sequence_number
276 )
277 ) THEN
278 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 END IF;
282 check_parent_existance;
283 ELSIF (p_action = 'UPDATE') THEN
284 -- Call all the procedures related to Before Update.
285 check_parent_existance;
286 ELSIF (p_action = 'DELETE') THEN
287 -- Call all the procedures related to Before Delete.
288 check_child_existance;
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.coa_code,
293 new_references.ci_cal_type,
294 new_references.ci_sequence_number
295 )
296 ) THEN
297 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 END IF;
301 ELSIF (p_action = 'VALIDATE_DELETE') THEN
302 check_child_existance;
303 END IF;
304
305 END before_dml;
306
307
308 PROCEDURE insert_row (
309 x_rowid IN OUT NOCOPY VARCHAR2,
310 x_coa_code IN VARCHAR2,
311 x_ci_cal_type IN VARCHAR2,
312 x_ci_sequence_number IN NUMBER,
313 x_rule_order IN NUMBER,
314 x_s_rule_call_cd IN VARCHAR2,
315 x_rul_sequence_number IN NUMBER,
316 x_pell_coa IN NUMBER,
317 x_pell_alt_exp IN NUMBER,
318 x_coa_grp_desc IN VARCHAR2,
319 x_mode IN VARCHAR2
320 ) AS
321
322 --
323 -- Created By : adhawan
324 -- Created On : 30-NOV-2000
325 -- Purpose : Initialises the Old and New references for the columns of the table.
326 -- Known limitations, enhancements or remarks :
327 -- Change History :
328 -- Who When What
329 -- (reverse chronological order - newest change first)
330 --
331
332 CURSOR c IS
333 SELECT rowid
334 FROM igf_aw_coa_group_all
335 WHERE coa_code = x_coa_code
336 AND ci_cal_type = x_ci_cal_type
337 AND ci_sequence_number = x_ci_sequence_number;
338
339 x_last_update_date DATE;
340 x_last_updated_by NUMBER;
341 x_last_update_login NUMBER;
345
342 l_org_id igf_aw_coa_group_all.org_id%TYPE;
343
344 BEGIN
346 l_org_id := igf_aw_gen.get_org_id;
347
348 x_last_update_date := SYSDATE;
349 IF (x_mode = 'I') THEN
350 x_last_updated_by := 1;
351 x_last_update_login := 0;
352 ELSIF (x_mode = 'R') THEN
353 x_last_updated_by := fnd_global.user_id;
354 IF (x_last_updated_by IS NULL) THEN
355 x_last_updated_by := -1;
356 END IF;
357 x_last_update_login := fnd_global.login_id;
358 IF (x_last_update_login IS NULL) THEN
359 x_last_update_login := -1;
360 END IF;
361 ELSE
362 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
363 igs_ge_msg_stack.add;
364 app_exception.raise_exception;
365 END IF;
366
367 before_dml(
368 p_action => 'INSERT',
369 x_rowid => x_rowid,
370 x_coa_code => x_coa_code,
371 x_ci_cal_type => x_ci_cal_type,
372 x_ci_sequence_number => x_ci_sequence_number,
373 x_rule_order => x_rule_order,
374 x_s_rule_call_cd => x_s_rule_call_cd,
375 x_rul_sequence_number => x_rul_sequence_number,
376 x_pell_coa => x_pell_coa,
377 x_pell_alt_exp => x_pell_alt_exp,
378 x_creation_date => x_last_update_date,
379 x_created_by => x_last_updated_by,
380 x_last_update_date => x_last_update_date,
381 x_last_updated_by => x_last_updated_by,
382 x_last_update_login => x_last_update_login,
383 x_coa_grp_desc => x_coa_grp_desc
384 );
385
386 INSERT INTO igf_aw_coa_group(
387 coa_code,
388 ci_cal_type,
389 ci_sequence_number,
390 creation_date,
391 created_by,
392 last_update_date,
393 last_updated_by,
394 last_update_login,
395 org_id,
396 coa_grp_desc
397 ) VALUES (
398 new_references.coa_code,
399 new_references.ci_cal_type,
400 new_references.ci_sequence_number,
401 x_last_update_date,
402 x_last_updated_by,
403 x_last_update_date,
404 x_last_updated_by,
405 x_last_update_login,
406 l_org_id,
407 new_references.coa_grp_desc
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_coa_code IN VARCHAR2,
424 x_ci_cal_type IN VARCHAR2,
425 x_ci_sequence_number IN NUMBER,
426 x_rule_order IN NUMBER,
427 x_s_rule_call_cd IN VARCHAR2,
428 x_rul_sequence_number IN NUMBER,
429 x_pell_coa IN NUMBER,
430 x_pell_alt_exp IN NUMBER,
431 x_coa_grp_desc IN VARCHAR2
432 ) AS
433
434 --
435 -- Created By : adhawan
436 -- Created On : 30-NOV-2000
437 -- Purpose : Initialises the Old and New references for the columns of the table.
438 -- Known limitations, enhancements or remarks :
439 -- Change History :
440 -- Who When What
441 -- (reverse chronological order - newest change first)
442 --
443
444 CURSOR c1 IS
445 SELECT
446 coa_grp_desc
447 FROM igf_aw_coa_group_all
448 WHERE rowid = x_rowid
449 FOR UPDATE NOWAIT;
450
451 tlinfo c1%ROWTYPE;
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.coa_grp_desc = x_coa_grp_desc) OR ((tlinfo.coa_grp_desc IS NULL) AND (x_coa_grp_desc IS NULL)))
467 ) THEN
468 NULL;
469 ELSE
470 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
471 igs_ge_msg_stack.add;
472 app_exception.raise_exception;
473 END IF;
474
475 RETURN;
476
477 END lock_row;
478
479
480 PROCEDURE update_row (
481 x_rowid IN VARCHAR2,
482 x_coa_code IN VARCHAR2,
483 x_ci_cal_type IN VARCHAR2,
484 x_ci_sequence_number IN NUMBER,
485 x_rule_order IN NUMBER,
486 x_s_rule_call_cd IN VARCHAR2,
487 x_rul_sequence_number IN NUMBER,
491 x_mode IN VARCHAR2
488 x_pell_coa IN NUMBER,
489 x_pell_alt_exp IN NUMBER,
490 x_coa_grp_desc IN VARCHAR2,
492 ) AS
493
494 --
495 -- Created By : adhawan
496 -- Created On : 30-NOV-2000
497 -- Purpose : Initialises the Old and New references for the columns of the table.
498 -- Known limitations, enhancements or remarks :
499 -- Change History :
500 -- Who When What
501 -- (reverse chronological order - newest change first)
502 --
503
504 x_last_update_date DATE ;
505 x_last_updated_by NUMBER;
506 x_last_update_login NUMBER;
507
508 BEGIN
509
510 x_last_update_date := SYSDATE;
511 IF (X_MODE = 'I') THEN
512 x_last_updated_by := 1;
513 x_last_update_login := 0;
514 ELSIF (x_mode = 'R') THEN
515 x_last_updated_by := fnd_global.user_id;
516 IF x_last_updated_by IS NULL THEN
517 x_last_updated_by := -1;
518 END IF;
519 x_last_update_login := fnd_global.login_id;
520 IF (x_last_update_login IS NULL) THEN
521 x_last_update_login := -1;
522 END IF;
523 ELSE
524 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
525 igs_ge_msg_stack.add;
526 app_exception.raise_exception;
527 END IF;
528
529 before_dml(
530 p_action => 'UPDATE',
531 x_rowid => x_rowid,
532 x_coa_code => x_coa_code,
533 x_ci_cal_type => x_ci_cal_type,
534 x_ci_sequence_number => x_ci_sequence_number,
535 x_rule_order => x_rule_order,
536 x_s_rule_call_cd => x_s_rule_call_cd,
537 x_rul_sequence_number => x_rul_sequence_number,
538 x_pell_coa => x_pell_coa,
539 x_pell_alt_exp => x_pell_alt_exp,
540 x_creation_date => x_last_update_date,
541 x_created_by => x_last_updated_by,
542 x_last_update_date => x_last_update_date,
543 x_last_updated_by => x_last_updated_by,
544 x_last_update_login => x_last_update_login,
545 x_coa_grp_desc => x_coa_grp_desc
546 );
547
548 UPDATE igf_aw_coa_group_all
549 SET
550 coa_grp_desc = new_references.coa_grp_desc,
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_coa_code IN VARCHAR2,
566 x_ci_cal_type IN VARCHAR2,
567 x_ci_sequence_number IN NUMBER,
568 x_rule_order IN NUMBER,
569 x_s_rule_call_cd IN VARCHAR2,
570 x_rul_sequence_number IN NUMBER,
571 x_pell_coa IN NUMBER,
572 x_pell_alt_exp IN NUMBER,
573 x_coa_grp_desc IN VARCHAR2,
574 x_mode IN VARCHAR2
575 ) AS
576
577 --
578 -- Created By : adhawan
579 -- Created On : 30-NOV-2000
580 -- Purpose : Initialises the Old and New references for the columns of the table.
581 -- Known limitations, enhancements or remarks :
582 -- Change History :
583 -- Who When What
584 -- (reverse chronological order - newest change first)
585 --
586
587 CURSOR c1 IS
588 SELECT rowid
589 FROM igf_aw_coa_group_all
590 WHERE coa_code = x_coa_code
591 AND ci_cal_type = x_ci_cal_type
592 AND ci_sequence_number = x_ci_sequence_number;
593
594 BEGIN
595
599 CLOSE c1;
596 OPEN c1;
597 FETCH c1 INTO x_rowid;
598 IF (c1%NOTFOUND) THEN
600
601 insert_row (
602 x_rowid,
603 x_coa_code,
604 x_ci_cal_type,
605 x_ci_sequence_number,
606 x_rule_order,
607 x_s_rule_call_cd,
608 x_rul_sequence_number,
609 x_pell_coa,
610 x_pell_alt_exp,
611 x_coa_grp_desc,
612 x_mode
613 );
614 RETURN;
615 END IF;
616 CLOSE c1;
617
618 update_row (
619 x_rowid,
620 x_coa_code,
621 x_ci_cal_type,
622 x_ci_sequence_number,
623 x_rule_order,
624 x_s_rule_call_cd,
625 x_rul_sequence_number,
626 x_pell_coa,
627 x_pell_alt_exp,
628 x_coa_grp_desc,
629 x_mode
630 );
631
632 END add_row;
633
634
635 PROCEDURE delete_row (
636 x_rowid IN VARCHAR2
637 ) AS
638
639 --
640 -- Created By : adhawan
641 -- Created On : 30-NOV-2000
642 -- Purpose : Initialises the Old and New references for the columns of the table.
643 -- Known limitations, enhancements or remarks :
644 -- Change History :
645 -- Who When What
646 -- (reverse chronological order - newest change first)
647 --
648
649 BEGIN
650
651 before_dml (
652 p_action => 'DELETE',
653 x_rowid => x_rowid
654 );
655
656 DELETE FROM igf_aw_coa_group_all
657 WHERE rowid = x_rowid;
658
659 IF (SQL%NOTFOUND) THEN
660 RAISE NO_DATA_FOUND;
661 END IF;
662
663 END delete_row;
664
665
666 END igf_aw_coa_group_pkg;
[Home] [Help]