1 PACKAGE BODY igs_fi_balances_pkg AS
2 /* $Header: IGSSI99B.pls 115.12 2003/02/14 07:32:31 pathipat ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_balances%ROWTYPE;
6 new_references igs_fi_balances%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_balance_id IN NUMBER ,
12 x_party_id IN NUMBER ,
13 x_standard_balance IN NUMBER ,
14 x_fee_balance IN NUMBER ,
15 x_holds_balance IN NUMBER ,
16 x_balance_date IN DATE ,
17 x_fee_balance_rule_id IN NUMBER ,
18 x_holds_balance_rule_id 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 ) AS
25 /*
26 || Created By : BDEVARAK
27 || Created On : 26-APR-2001
28 || Purpose : Initialises the Old and New references for the columns of the table.
29 || Known limitations, enhancements or remarks :
30 || Change History :
31 || Who When What
32 || (reverse chronological order - newest change first)
33 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
34 || installment_balance_id and installment_balance_rule_id for
35 || Enh Bug # 2562745
36 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
37 || agairola 30-May-2002 For bug 2364505, obsoleted column
38 standard_balance_rule_id
39 */
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_FI_BALANCES
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 OPEN cur_old_ref_values;
53 FETCH cur_old_ref_values INTO old_references;
54 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55 CLOSE cur_old_ref_values;
56 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57 igs_ge_msg_stack.add;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61 CLOSE cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.balance_id := x_balance_id;
65 new_references.party_id := x_party_id;
66 new_references.standard_balance := x_standard_balance;
67 new_references.fee_balance := x_fee_balance;
68 new_references.holds_balance := x_holds_balance;
69 new_references.balance_date := x_balance_date;
70 new_references.fee_balance_rule_id := x_fee_balance_rule_id;
71 new_references.holds_balance_rule_id := x_holds_balance_rule_id;
72
73 IF (p_action = 'UPDATE') THEN
74 new_references.creation_date := old_references.creation_date;
75 new_references.created_by := old_references.created_by;
76 ELSE
77 new_references.creation_date := x_creation_date;
78 new_references.created_by := x_created_by;
79 END IF;
80
81 new_references.last_update_date := x_last_update_date;
82 new_references.last_updated_by := x_last_updated_by;
83 new_references.last_update_login := x_last_update_login;
84
85 END set_column_values;
86
87 PROCEDURE check_parent_existance AS
88 /*
89 || Created By : BDEVARAK
90 || Created On : 26-APR-2001
91 || Purpose : Checks for the existance of Parent records.
92 || Known limitations, enhancements or remarks :
93 || Change History :
94 || Who When What
95 || (reverse chronological order - newest change first)
96 || pathipat 14-Feb-2003 Enh 2747325 - Removed FOR UPDATE NOWAIT clause
97 || in cursor cur_rowid
98 || pathipat 30-SEP-2002 Obsoleted columns other_balance_rule_id and
99 || installment_balance_rule_id for Enh Bug # 2562745
100 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
101 || agairola 30-May-2002 For bug 2364505, obsoleted column
102 standard_balance_rule_id
103 */
104
105 CURSOR cur_rowid IS
106 SELECT rowid
107 FROM hz_parties
108 WHERE party_id = new_references.party_id;
109
110 lv_rowid cur_rowid%RowType;
111
112 BEGIN
113
114 IF (((old_references.party_id = new_references.party_id)) OR
115 ((new_references.party_id IS NULL))) THEN
116 NULL;
117 ELSE
118 OPEN cur_rowid;
119 FETCH cur_rowid INTO lv_rowid;
120 IF (cur_rowid%FOUND) THEN
121 CLOSE cur_rowid;
122 ELSE
123 CLOSE cur_rowid;
124 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
125 igs_ge_msg_stack.add;
126 app_exception.raise_exception;
127 END IF;
128 END IF;
129
130 IF (((old_references.fee_balance_rule_id = new_references.fee_balance_rule_id)) OR
131 ((new_references.fee_balance_rule_id IS NULL))) THEN
132 NULL;
133 ELSIF NOT igs_fi_balance_rules_pkg.get_pk_for_validation (
134 new_references.fee_balance_rule_id
135 ) THEN
136 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
137 igs_ge_msg_stack.add;
138 app_exception.raise_exception;
139 END IF;
140
141 IF (((old_references.holds_balance_rule_id = new_references.holds_balance_rule_id)) OR
142 ((new_references.holds_balance_rule_id IS NULL))) THEN
143 NULL;
144 ELSIF NOT igs_fi_balance_rules_pkg.get_pk_for_validation (
145 new_references.holds_balance_rule_id
146 ) THEN
147 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148 igs_ge_msg_stack.add;
149 app_exception.raise_exception;
150 END IF;
151
152 /* IGS_FI_BALANCE_RULES_PKG.get_pk_for_validation calls for columns other_balance_rule_id and
153 installment_balance_rule_id removed, part of Enh Bug 2562745 */
154
155 END check_parent_existance;
156
157 PROCEDURE check_child_existance AS
158 /*
159 || Created By : PATHIPAT
160 || Created On : 21-OCT-2002
161 || Purpose : Validates the Primary Key of the table.
162 || Known limitations, enhancements or remarks :
163 || Change History :
164 || Who When What
165 || (reverse chronological order - newest change first)
166 */
167 BEGIN
168
169 igs_fi_balances_hst_pkg.get_fk_igs_fi_balances(
170 old_references.balance_id
171 );
172 END check_child_existance;
173
174
175 FUNCTION get_pk_for_validation (
176 x_balance_id IN NUMBER
177 ) RETURN BOOLEAN AS
178 /*
179 || Created By : BDEVARAK
180 || Created On : 26-APR-2001
181 || Purpose : Validates the Primary Key of the table.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || (reverse chronological order - newest change first)
186 */
187 CURSOR cur_rowid IS
188 SELECT rowid
189 FROM igs_fi_balances
190 WHERE balance_id = x_balance_id
191 FOR UPDATE NOWAIT;
192
193 lv_rowid cur_rowid%RowType;
194
195 BEGIN
196
197 OPEN cur_rowid;
198 FETCH cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 CLOSE cur_rowid;
201 RETURN(TRUE);
202 ELSE
203 CLOSE cur_rowid;
204 RETURN(FALSE);
205 END IF;
206
207 END get_pk_for_validation;
208
209 FUNCTION get_uk_for_validation (
210 x_party_id IN NUMBER,
211 x_balance_date IN DATE)
212 RETURN BOOLEAN AS
213 /*
214 || Created By : SCHODAVA
215 || Created On : 08-OCT-2001
216 || Purpose : Validates the Unique Key for the table.
217 || Known limitations, enhancements or remarks : Added as a part of SFCR010 (Enh # 2030448)
218 || Change History :
219 || Who When What
220 || smvk 17-Sep-2002 Removed the subaccount_id in parameter and its usage in the function
221 || as a part of Bug # 2564643
222 || (reverse chronological order - newest change first)
223 */
224 CURSOR cur_rowid IS
225 SELECT rowid
226 FROM IGS_FI_BALANCES
227 WHERE party_id = new_references.party_id
228 AND TRUNC(balance_date) = TRUNC(new_references.balance_date)
229 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
230 FOR UPDATE NOWAIT;
231
232 lv_rowid cur_rowid%RowType;
233
234 BEGIN
235 OPEN cur_rowid;
236 FETCH cur_rowid INTO lv_rowid;
237 IF (cur_rowid%FOUND) THEN
238 CLOSE cur_rowid;
239 RETURN(TRUE);
240 ELSE
241 CLOSE cur_rowid;
242 RETURN (FALSE);
243 END IF;
244
245 END get_uk_for_validation;
246
247 PROCEDURE check_uniqueness AS
248 /*
249 || Created By : SCHODAVA
250 || Created On : 08-OCT-2001
251 || Purpose : Validates the Unique Key for the table.
252 || Known limitations, enhancements or remarks : Added as a part of SFCR010 (Enh # 2030448)
253 || Change History :
254 || Who When What
255 || smvk 17-Sep-2002 Removed the subaccount_id from the get_uk_for_validation function call
256 || as a part of Bug # 2564643
257 || (reverse chronological order - newest change first)
258 */
259 BEGIN
260
261 IF get_uk_for_validation (
262 new_references.party_id,
263 new_references.balance_date)
264 THEN
265 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
266 IGS_GE_MSG_STACK.ADD;
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 END IF;
269
270 END check_uniqueness ;
271
272 /* Removed the procedure get_fk_igs_fi_subaccts_all as a part of Bug # 2564643 */
273
274 PROCEDURE afterrowupdate AS
275 /*
276 || Created By : PATHIPAT
277 || Created On : 30-SEP-2002
278 || Purpose : Maintaining history for the balances table
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 l_v_balance_type igs_fi_balances_hst.balance_type%TYPE;
285 l_v_balance_amount igs_fi_balances_hst.balance_amount%TYPE;
286 l_v_balance_rule_id igs_fi_balances_hst.balance_rule_id%TYPE;
287 l_record_changed BOOLEAN := FALSE;
288 l_balance_hist_id igs_fi_balances_hst.balance_hist_id%TYPE := NULL;
289
290 BEGIN
291
292 l_rowid := NULL;
293
294 -- If the value has been updated, then insert into history table.
295 IF (new_references.fee_balance <> old_references.fee_balance) OR
296 ((new_references.fee_balance IS NOT NULL) AND (old_references.fee_balance IS NULL)) OR
297 ((new_references.fee_balance IS NULL) AND (old_references.fee_balance IS NOT NULL)) OR
298 (new_references.fee_balance_rule_id <> old_references.fee_balance_rule_id) OR
299 ((new_references.fee_balance_rule_id IS NOT NULL) AND (old_references.fee_balance_rule_id IS NULL)) OR
300 ((new_references.fee_balance_rule_id IS NULL) AND (old_references.fee_balance_rule_id IS NOT NULL)) THEN
301
302 l_record_changed := TRUE;
303 l_v_balance_type := 'FEE';
304 l_v_balance_amount := old_references.fee_balance;
305 l_v_balance_rule_id := old_references.fee_balance_rule_id;
306
307 ELSIF (new_references.holds_balance <> old_references.holds_balance) OR
308 ((new_references.holds_balance IS NOT NULL) AND (old_references.holds_balance IS NULL)) OR
309 ((new_references.holds_balance IS NULL) AND (old_references.holds_balance IS NOT NULL)) OR
310 (new_references.holds_balance_rule_id <> old_references.holds_balance_rule_id) OR
311 ((new_references.holds_balance_rule_id IS NOT NULL) AND (old_references.holds_balance_rule_id IS NULL)) OR
312 ((new_references.holds_balance_rule_id IS NULL) AND (old_references.holds_balance_rule_id IS NOT NULL)) THEN
313
314 l_record_changed := TRUE;
315 l_v_balance_type := 'HOLDS';
316 l_v_balance_amount := old_references.holds_balance;
317 l_v_balance_rule_id := old_references.holds_balance_rule_id;
318
319 END IF;
320
321 IF l_record_changed THEN
322
323 IGS_FI_BALANCES_HST_PKG.INSERT_ROW ( x_rowid => l_rowid,
324 x_balance_hist_id => l_balance_hist_id,
325 x_balance_id => old_references.balance_id,
326 x_balance_type => l_v_balance_type,
327 x_balance_amount => l_v_balance_amount,
328 x_balance_rule_id => l_v_balance_rule_id,
329 x_mode => 'R'
330 );
331 END IF;
332
333 END afterrowupdate;
334
335
336 PROCEDURE before_dml (
337 p_action IN VARCHAR2,
338 x_rowid IN VARCHAR2 ,
339 x_balance_id IN NUMBER ,
340 x_party_id IN NUMBER ,
341 x_standard_balance IN NUMBER ,
342 x_fee_balance IN NUMBER ,
343 x_holds_balance IN NUMBER ,
344 x_balance_date IN DATE ,
345 x_fee_balance_rule_id IN NUMBER ,
346 x_holds_balance_rule_id IN NUMBER ,
347 x_creation_date IN DATE ,
348 x_created_by IN NUMBER ,
349 x_last_update_date IN DATE ,
350 x_last_updated_by IN NUMBER ,
351 x_last_update_login IN NUMBER
352 ) AS
353 /*
354 || Created By : BDEVARAK
355 || Created On : 26-APR-2001
356 || Purpose : Initialises the columns, Checks Constraints, Calls the
357 || Trigger Handlers for the table, before any DML operation.
358 || Known limitations, enhancements or remarks :
359 || Change History :
360 || Who When What
361 || (reverse chronological order - newest change first)
362 || pathipat 21-OCT-2002 Bug:2562745 - Added check_child_existance() calls before
363 || delete operation.
364 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
365 || installment_balance_id and installment_balance_rule_id for
366 || Enh Bug # 2562745
367 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
368 || agairola 30-May-2002 For bug 2364505, obsoleted column
369 || standard_balance_rule_id
370 || SCHODAVA 8-OCT-2001 Enh # 2030448 (SFCR010)
371 */
372 BEGIN
373
374 set_column_values (
375 p_action,
376 x_rowid,
377 x_balance_id,
378 x_party_id,
379 x_standard_balance,
380 x_fee_balance,
381 x_holds_balance,
382 x_balance_date,
383 x_fee_balance_rule_id,
384 x_holds_balance_rule_id,
385 x_creation_date,
386 x_created_by,
387 x_last_update_date,
388 x_last_updated_by,
389 x_last_update_login
390 );
391 -- Calls to check_uniqueness are added by schodava as a part of Enh # 2030448 (SFCR010)
392 IF (p_action = 'INSERT') THEN
393 -- Call all the procedures related to Before Insert.
394 IF ( get_pk_for_validation(
395 new_references.balance_id
396 )
397 ) THEN
398 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
399 igs_ge_msg_stack.add;
400 app_exception.raise_exception;
401 END IF;
402 check_parent_existance;
403 check_uniqueness;
404 ELSIF (p_action = 'UPDATE') THEN
405 -- Call all the procedures related to Before Update.
406 check_parent_existance;
407 check_uniqueness;
408 ELSIF (p_action = 'DELETE') THEN
409 -- Call all the procedures related to Before Delete.
410 check_child_existance;
411 ELSIF (p_action = 'VALIDATE_INSERT') THEN
412 -- Call all the procedures related to Before Insert.
413 IF ( get_pk_for_validation (
414 new_references.balance_id
415 )
416 ) THEN
417 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
418 igs_ge_msg_stack.add;
419 app_exception.raise_exception;
420 END IF;
421 check_uniqueness;
422 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
423 check_uniqueness;
424 ELSIF (p_action = 'VALIDATE_DELETE') THEN
425 -- Call all the procedures related to Before Delete.
426 check_child_existance;
427 END IF;
428
429 END before_dml;
430
431 PROCEDURE after_dml(
432 p_action IN VARCHAR2 ,
433 x_rowid IN VARCHAR2
434 ) AS
435 /*
436 || Created By : PATHIPAT
437 || Created On : 30-SEP-2002
438 || Purpose : Handles the AFTER DML logic for the table.
439 || Known limitations, enhancements or remarks :
440 || Change History :
441 || Who When What
442 || (reverse chronological order - newest change first)
443 ||
444 */
445 l_rowid VARCHAR2(25);
446 BEGIN
447
448 IF (p_action = 'UPDATE') THEN
449 afterrowupdate;
450 END IF;
451
452 END after_dml;
453
454 PROCEDURE insert_row (
455 x_rowid IN OUT NOCOPY VARCHAR2,
456 x_balance_id IN OUT NOCOPY NUMBER,
457 x_party_id IN NUMBER,
458 x_standard_balance IN NUMBER,
459 x_fee_balance IN NUMBER,
460 x_holds_balance IN NUMBER,
461 x_balance_date IN DATE,
462 x_fee_balance_rule_id IN NUMBER,
463 x_holds_balance_rule_id IN NUMBER,
464 x_mode IN VARCHAR2
465 ) AS
466 /*
467 || Created By : BDEVARAK
468 || Created On : 26-APR-2001
469 || Purpose : Handles the INSERT DML logic for the table.
470 || Known limitations, enhancements or remarks :
471 || Change History :
472 || Who When What
473 || (reverse chronological order - newest change first)
474 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
475 || installment_balance_id and installment_balance_rule_id for
476 || Enh Bug # 2562745
477 || smvk 17-Sep-2002 Obsoleted column subaccount_id as a part of Bug # 2564643
478 || agairola 30-May-2002 For bug 2364505, obsoleted column
479 standard_balance_rule_id
480 */
481 CURSOR c IS
482 SELECT rowid
483 FROM igs_fi_balances
484 WHERE balance_id = x_balance_id;
485
486 x_last_update_date DATE;
487 x_last_updated_by NUMBER;
488 x_last_update_login NUMBER;
489 x_request_id NUMBER;
490 x_program_id NUMBER;
491 x_program_application_id NUMBER;
492 x_program_update_date DATE;
493
494 BEGIN
495
496 x_last_update_date := SYSDATE;
497 IF (x_mode = 'I') THEN
498 x_last_updated_by := 1;
499 x_last_update_login := 0;
500 ELSIF (x_mode = 'R') THEN
501 x_last_updated_by := fnd_global.user_id;
502 IF (x_last_updated_by IS NULL) THEN
503 x_last_updated_by := -1;
504 END IF;
505 x_last_update_login := fnd_global.login_id;
506 IF (x_last_update_login IS NULL) THEN
507 x_last_update_login := -1;
508 END IF;
509 x_request_id := fnd_global.conc_request_id;
510 x_program_id := fnd_global.conc_program_id;
511 x_program_application_id := fnd_global.prog_appl_id;
512
513 IF (x_request_id = -1) THEN
514 x_request_id := NULL;
515 x_program_id := NULL;
516 x_program_application_id := NULL;
517 x_program_update_date := NULL;
518 ELSE
519 x_program_update_date := SYSDATE;
520 END IF;
521 ELSE
522 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
523 igs_ge_msg_stack.add;
524 app_exception.raise_exception;
525 END IF;
526
527 SELECT igs_fi_balances_s.NEXTVAL
528 INTO x_balance_id
529 FROM dual;
530
531 before_dml(
532 p_action => 'INSERT',
533 x_rowid => x_rowid,
534 x_balance_id => x_balance_id,
535 x_party_id => x_party_id,
536 x_standard_balance => x_standard_balance,
537 x_fee_balance => x_fee_balance,
538 x_holds_balance => x_holds_balance,
539 x_balance_date => x_balance_date,
540 x_fee_balance_rule_id => x_fee_balance_rule_id,
541 x_holds_balance_rule_id => x_holds_balance_rule_id,
542 x_creation_date => x_last_update_date,
543 x_created_by => x_last_updated_by,
544 x_last_update_date => x_last_update_date,
545 x_last_updated_by => x_last_updated_by,
546 x_last_update_login => x_last_update_login
547 );
548
549 INSERT INTO igs_fi_balances (
550 balance_id,
551 party_id,
552 standard_balance,
553 fee_balance,
554 holds_balance,
555 balance_date,
556 fee_balance_rule_id,
557 holds_balance_rule_id,
558 creation_date,
559 created_by,
560 last_update_date,
561 last_updated_by,
562 last_update_login,
563 request_id,
564 program_id,
565 program_application_id,
566 program_update_date
567 ) VALUES (
568 new_references.balance_id,
569 new_references.party_id,
570 new_references.standard_balance,
571 new_references.fee_balance,
572 new_references.holds_balance,
573 new_references.balance_date,
574 new_references.fee_balance_rule_id,
575 new_references.holds_balance_rule_id,
576 x_last_update_date,
577 x_last_updated_by,
578 x_last_update_date,
579 x_last_updated_by,
580 x_last_update_login ,
581 x_request_id,
582 x_program_id,
583 x_program_application_id,
584 x_program_update_date
585 );
586
587 OPEN c;
588 FETCH c INTO x_rowid;
589 IF (c%NOTFOUND) THEN
590 CLOSE c;
591 RAISE NO_DATA_FOUND;
592 END IF;
593 CLOSE c;
594
595 END insert_row;
596
597
598 PROCEDURE lock_row (
599 x_rowid IN VARCHAR2,
600 x_balance_id IN NUMBER,
601 x_party_id IN NUMBER,
602 x_standard_balance IN NUMBER,
603 x_fee_balance IN NUMBER,
604 x_holds_balance IN NUMBER,
605 x_balance_date IN DATE,
606 x_fee_balance_rule_id IN NUMBER,
607 x_holds_balance_rule_id IN NUMBER
608 ) AS
609 /*
610 || Created By : BDEVARAK
611 || Created On : 26-APR-2001
612 || Purpose : Handles the LOCK mechanism for the table.
613 || Known limitations, enhancements or remarks :
614 || Change History :
615 || Who When What
616 || (reverse chronological order - newest change first)
617 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
618 || installment_balance_id and installment_balance_rule_id for
619 || Enh Bug # 2562745
620 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
621 || agairola 30-May-2002 For bug 2364505, obsoleted column
622 standard_balance_rule_id
623 */
624 CURSOR c1 IS
625 SELECT
626 party_id,
627 standard_balance,
628 fee_balance,
629 holds_balance,
630 balance_date,
631 fee_balance_rule_id,
632 holds_balance_rule_id
633 FROM igs_fi_balances
634 WHERE rowid = x_rowid
635 FOR UPDATE NOWAIT;
636
637 tlinfo c1%ROWTYPE;
638
639 BEGIN
640
641 OPEN c1;
642 FETCH c1 INTO tlinfo;
643 IF (c1%notfound) THEN
644 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
645 igs_ge_msg_stack.add;
646 CLOSE c1;
647 app_exception.raise_exception;
648 RETURN;
649 END IF;
650 CLOSE c1;
651
652 IF (
653 (tlinfo.party_id = x_party_id)
654 AND ((tlinfo.standard_balance = x_standard_balance) OR ((tlinfo.standard_balance IS NULL) AND (X_standard_balance IS NULL)))
655 AND ((tlinfo.fee_balance = x_fee_balance) OR ((tlinfo.fee_balance IS NULL) AND (X_fee_balance IS NULL)))
656 AND ((tlinfo.holds_balance = x_holds_balance) OR ((tlinfo.holds_balance IS NULL) AND (X_holds_balance IS NULL)))
657 AND (tlinfo.balance_date = x_balance_date)
658 AND ((tlinfo.fee_balance_rule_id = x_fee_balance_rule_id) OR ((tlinfo.fee_balance_rule_id IS NULL) AND (X_fee_balance_rule_id IS NULL)))
659 AND ((tlinfo.holds_balance_rule_id = x_holds_balance_rule_id) OR ((tlinfo.holds_balance_rule_id IS NULL) AND (X_holds_balance_rule_id IS NULL)))
660 ) THEN
661 NULL;
662 ELSE
663 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
664 igs_ge_msg_stack.add;
665 app_exception.raise_exception;
666 END IF;
667
668 RETURN;
669
670 END lock_row;
671
672
673 PROCEDURE update_row (
674 x_rowid IN VARCHAR2,
675 x_balance_id IN NUMBER,
676 x_party_id IN NUMBER,
677 x_standard_balance IN NUMBER,
678 x_fee_balance IN NUMBER,
679 x_holds_balance IN NUMBER,
680 x_balance_date IN DATE,
681 x_fee_balance_rule_id IN NUMBER,
682 x_holds_balance_rule_id IN NUMBER,
683 x_mode IN VARCHAR2
684 ) AS
685 /*
686 || Created By : BDEVARAK
687 || Created On : 26-APR-2001
688 || Purpose : Handles the UPDATE DML logic for the table.
689 || Known limitations, enhancements or remarks :
690 || Change History :
691 || Who When What
692 || (reverse chronological order - newest change first)
693 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
694 || installment_balance_id and installment_balance_rule_id for
695 || Enh Bug # 2562745
696 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
697 || agairola 30-May-2002 For bug 2364505, obsoleted column
698 standard_balance_rule_id
699 */
700 x_last_update_date DATE ;
701 x_last_updated_by NUMBER;
702 x_last_update_login NUMBER;
703 x_request_id NUMBER;
704 x_program_id NUMBER;
705 x_program_application_id NUMBER;
706 x_program_update_date DATE;
707
708 BEGIN
709
710 x_last_update_date := SYSDATE;
711 IF (X_MODE = 'I') THEN
712 x_last_updated_by := 1;
713 x_last_update_login := 0;
714 ELSIF (x_mode = 'R') THEN
715 x_last_updated_by := fnd_global.user_id;
716 IF x_last_updated_by IS NULL THEN
717 x_last_updated_by := -1;
718 END IF;
719 x_last_update_login := fnd_global.login_id;
720 IF (x_last_update_login IS NULL) THEN
721 x_last_update_login := -1;
722 END IF;
723 ELSE
724 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
725 igs_ge_msg_stack.add;
726 app_exception.raise_exception;
727 END IF;
728
729 before_dml(
730 p_action => 'UPDATE',
731 x_rowid => x_rowid,
732 x_balance_id => x_balance_id,
733 x_party_id => x_party_id,
734 x_standard_balance => x_standard_balance,
735 x_fee_balance => x_fee_balance,
736 x_holds_balance => x_holds_balance,
737 x_balance_date => x_balance_date,
738 x_fee_balance_rule_id => x_fee_balance_rule_id,
739 x_holds_balance_rule_id => x_holds_balance_rule_id,
740 x_creation_date => x_last_update_date,
741 x_created_by => x_last_updated_by,
742 x_last_update_date => x_last_update_date,
743 x_last_updated_by => x_last_updated_by,
744 x_last_update_login => x_last_update_login
745 );
746
747 IF (x_mode = 'R') THEN
748 x_request_id := fnd_global.conc_request_id;
749 x_program_id := fnd_global.conc_program_id;
750 x_program_application_id := fnd_global.prog_appl_id;
751 IF (x_request_id = -1) THEN
752 x_request_id := old_references.request_id;
753 x_program_id := old_references.program_id;
754 x_program_application_id := old_references.program_application_id;
755 x_program_update_date := old_references.program_update_date;
756 ELSE
757 x_program_update_date := SYSDATE;
758 END IF;
759 END IF;
760
761 UPDATE igs_fi_balances
762 SET
763 party_id = new_references.party_id,
764 standard_balance = new_references.standard_balance,
765 fee_balance = new_references.fee_balance,
766 holds_balance = new_references.holds_balance,
767 balance_date = new_references.balance_date,
768 fee_balance_rule_id = new_references.fee_balance_rule_id,
769 holds_balance_rule_id = new_references.holds_balance_rule_id,
770 last_update_date = x_last_update_date,
771 last_updated_by = x_last_updated_by,
772 last_update_login = x_last_update_login ,
773 request_id = x_request_id,
774 program_id = x_program_id,
775 program_application_id = x_program_application_id,
776 program_update_date = x_program_update_date
777 WHERE rowid = x_rowid;
778
779 IF (SQL%NOTFOUND) THEN
780 RAISE NO_DATA_FOUND;
781 END IF;
782
783 -- Added as part of Enh Bug: 2562745
784 -- When the balances table is updated, the initial version of the record is saved in
785 -- the history table.
786
787 after_dml(
788 p_action => 'UPDATE',
789 x_rowid => x_rowid );
790
791 END update_row;
792
793
794 PROCEDURE add_row (
795 x_rowid IN OUT NOCOPY VARCHAR2,
796 x_balance_id IN OUT NOCOPY NUMBER,
797 x_party_id IN NUMBER,
798 x_standard_balance IN NUMBER,
799 x_fee_balance IN NUMBER,
800 x_holds_balance IN NUMBER,
801 x_balance_date IN DATE,
802 x_fee_balance_rule_id IN NUMBER,
803 x_holds_balance_rule_id IN NUMBER,
804 x_mode IN VARCHAR2
805 ) AS
806 /*
807 || Created By : BDEVARAK
808 || Created On : 26-APR-2001
809 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
810 || Known limitations, enhancements or remarks :
811 || Change History :
812 || Who When What
813 || (reverse chronological order - newest change first)
814 || pathipat 30-SEP-2002 Obsoleted columns other_balance_id, other_balance_rule_id,
815 || installment_balance_id and installment_balance_rule_id for
816 || Enh Bug # 2562745
817 || smvk 17-Sep-2002 Obsoleted column subaccount_id, as part of Bug # 2564643
818 || agairola 30-May-2002 For bug 2364505, obsoleted column
819 standard_balance_rule_id
820 */
821 CURSOR c1 IS
822 SELECT rowid
823 FROM igs_fi_balances
824 WHERE balance_id = x_balance_id;
825
826 BEGIN
827
828 OPEN c1;
829 FETCH c1 INTO x_rowid;
830 IF (c1%NOTFOUND) THEN
831 CLOSE c1;
832
833 insert_row (
834 x_rowid,
835 x_balance_id,
836 x_party_id,
837 x_standard_balance,
838 x_fee_balance,
839 x_holds_balance,
840 x_balance_date,
841 x_fee_balance_rule_id,
842 x_holds_balance_rule_id,
843 x_mode
844 );
845 RETURN;
846 END IF;
847 CLOSE c1;
848
849 update_row (
850 x_rowid,
851 x_balance_id,
852 x_party_id,
853 x_standard_balance,
854 x_fee_balance,
855 x_holds_balance,
856 x_balance_date,
857 x_fee_balance_rule_id,
858 x_holds_balance_rule_id,
859 x_mode
860 );
861
862 END add_row;
863
864
865 PROCEDURE delete_row (
866 x_rowid IN VARCHAR2
867 ) AS
868 /*
869 || Created By : BDEVARAK
870 || Created On : 26-APR-2001
871 || Purpose : Handles the DELETE DML logic for the table.
872 || Known limitations, enhancements or remarks :
873 || Change History :
874 || Who When What
875 || (reverse chronological order - newest change first)
876 */
877 BEGIN
878
879 before_dml (
880 p_action => 'DELETE',
881 x_rowid => x_rowid
882 );
883
884 DELETE FROM igs_fi_balances
885 WHERE rowid = x_rowid;
886
887 IF (SQL%NOTFOUND) THEN
888 RAISE NO_DATA_FOUND;
889 END IF;
890
891 END delete_row;
892
893
894 END igs_fi_balances_pkg;
[Home] [Help]