[Home] [Help]
50: -- smvk 17-Sep-2002 Removed the usage of subaccout_id in the entire package, As a part of Bug # 2564643.
51: --smadathi 03-Jul-2002 Bug 2443082. Modified update_balances procedure. Added new private function
52: -- retro_update_balance.
53: --agairola 11-Jun-2002 Bug No:2373963 Modified the code for the calculate_balance and calculate_balance_1
54: --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
55: --vvutukur 09-may-2002 Bugs#2329042,2309047,Modifications done in calculate_balance,calculate_balance_1,
56: -- check_exclusion_rules.
57: --smadathi 10-APR-2002 Bug 2289191. Function calculate_balance_1,procedure calculate_balance
58: -- procedure check_exclusion_rules Modified.
106: p_balance_type IN igs_lookup_values.lookup_code%TYPE,
107: p_balance_date IN igs_fi_balances.balance_date%TYPE,
108: p_action IN VARCHAR2,
109: p_balance_rule_id IN igs_fi_balance_rules.balance_rule_id%TYPE,
110: p_balance_amount OUT NOCOPY igs_fi_balances.standard_balance%TYPE,
111: p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
112: ) AS
113: ------------------------------------------------------------------
114: --Created by : Sanil Madathil, Oracle IDC
149: -- 2. The Balances were getting created/updated only if there were any charges
150: -- or credit records found. Incase of no charges or credits, the balances were
151: -- not getting updated/created. Modified the code to update or create the balance
152: -- even if no charge or credits were found
153: --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
154: --vvutukur 09-may-2002 Bugs#2329042,2309047. Modified c_fi_inv_int_all,c_fi_credits_all cursors
155: -- and removed cursor c_igs_fi_bal_rules. Put a call to check_exclusion_rules
156: -- and removed redundant code for assigning cursor fetched values into local variables.
157: --smadathi 10-APR-2002 Bug 2289191. References to enabled_flag column removed from cursor
221: WHERE party_id = p_person_id
222: AND TRUNC(balance_date) = TRUNC(p_balance_date);
223:
224: l_cur_rec_exists cur_rec_exists%ROWTYPE;
225: l_invoice_amount igs_fi_balances.standard_balance%TYPE := 0.0;
226: l_credit_amount igs_fi_credits_all.amount%TYPE := 0.0;
227: l_balance igs_fi_balances.standard_balance%TYPE := 0.0;
228: l_rowid igs_fi_inv_int_v.row_id%TYPE;
229: l_balance_id igs_fi_balances.balance_id%TYPE;
223:
224: l_cur_rec_exists cur_rec_exists%ROWTYPE;
225: l_invoice_amount igs_fi_balances.standard_balance%TYPE := 0.0;
226: l_credit_amount igs_fi_credits_all.amount%TYPE := 0.0;
227: l_balance igs_fi_balances.standard_balance%TYPE := 0.0;
228: l_rowid igs_fi_inv_int_v.row_id%TYPE;
229: l_balance_id igs_fi_balances.balance_id%TYPE;
230: l_bal_standard igs_fi_balances.standard_balance%TYPE := 0.0;
231: l_bal_fee igs_fi_balances.standard_balance%TYPE := 0.0;
226: l_credit_amount igs_fi_credits_all.amount%TYPE := 0.0;
227: l_balance igs_fi_balances.standard_balance%TYPE := 0.0;
228: l_rowid igs_fi_inv_int_v.row_id%TYPE;
229: l_balance_id igs_fi_balances.balance_id%TYPE;
230: l_bal_standard igs_fi_balances.standard_balance%TYPE := 0.0;
231: l_bal_fee igs_fi_balances.standard_balance%TYPE := 0.0;
232: l_bal_hold igs_fi_balances.standard_balance%TYPE := 0.0;
233: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
234: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
227: l_balance igs_fi_balances.standard_balance%TYPE := 0.0;
228: l_rowid igs_fi_inv_int_v.row_id%TYPE;
229: l_balance_id igs_fi_balances.balance_id%TYPE;
230: l_bal_standard igs_fi_balances.standard_balance%TYPE := 0.0;
231: l_bal_fee igs_fi_balances.standard_balance%TYPE := 0.0;
232: l_bal_hold igs_fi_balances.standard_balance%TYPE := 0.0;
233: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
234: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
235: l_message fnd_new_messages.message_name%TYPE := NULL;
228: l_rowid igs_fi_inv_int_v.row_id%TYPE;
229: l_balance_id igs_fi_balances.balance_id%TYPE;
230: l_bal_standard igs_fi_balances.standard_balance%TYPE := 0.0;
231: l_bal_fee igs_fi_balances.standard_balance%TYPE := 0.0;
232: l_bal_hold igs_fi_balances.standard_balance%TYPE := 0.0;
233: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
234: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
235: l_message fnd_new_messages.message_name%TYPE := NULL;
236: l_return_status BOOLEAN := FALSE;
293: --get the balance amount by subtracting total unapplied amount from the total invoice due.
294: l_balance := NVL(l_invoice_amount,0.0) - NVL(l_credit_amount,0.0);
295:
296: --assign the amount to the corresponding balance.
297: IF p_balance_type = 'STANDARD' THEN
298: l_bal_standard := l_balance ;
299: l_bal_fee := NULL;
300: l_bal_hold := NULL;
301: l_bal_rule_fee := NULL;
294: l_balance := NVL(l_invoice_amount,0.0) - NVL(l_credit_amount,0.0);
295:
296: --assign the amount to the corresponding balance.
297: IF p_balance_type = 'STANDARD' THEN
298: l_bal_standard := l_balance ;
299: l_bal_fee := NULL;
300: l_bal_hold := NULL;
301: l_bal_rule_fee := NULL;
302: l_bal_rule_hold:= NULL;
300: l_bal_hold := NULL;
301: l_bal_rule_fee := NULL;
302: l_bal_rule_hold:= NULL;
303: ELSIF p_balance_type = 'FEE' THEN
304: l_bal_standard := NULL;
305: l_bal_fee := l_balance;
306: l_bal_hold := NULL;
307: l_bal_rule_fee := p_balance_rule_id;
308: l_bal_rule_hold:= NULL;
306: l_bal_hold := NULL;
307: l_bal_rule_fee := p_balance_rule_id;
308: l_bal_rule_hold:= NULL;
309: ELSIF p_balance_type = 'HOLDS' THEN
310: l_bal_standard := NULL;
311: l_bal_fee := NULL;
312: l_bal_hold := l_balance;
313: l_bal_rule_fee := NULL;
314: l_bal_rule_hold := p_balance_rule_id;
320: OPEN cur_rec_exists ;
321: FETCH cur_rec_exists INTO l_cur_rec_exists;
322: IF cur_rec_exists%FOUND THEN
323: CLOSE cur_rec_exists;
324: IF p_balance_type = 'STANDARD' THEN
325: l_cur_rec_exists.standard_balance := l_balance;
326: ELSIF p_balance_type = 'FEE' THEN
327: l_cur_rec_exists.fee_balance := l_balance;
328: l_cur_rec_exists.fee_balance_rule_id := p_balance_rule_id;
321: FETCH cur_rec_exists INTO l_cur_rec_exists;
322: IF cur_rec_exists%FOUND THEN
323: CLOSE cur_rec_exists;
324: IF p_balance_type = 'STANDARD' THEN
325: l_cur_rec_exists.standard_balance := l_balance;
326: ELSIF p_balance_type = 'FEE' THEN
327: l_cur_rec_exists.fee_balance := l_balance;
328: l_cur_rec_exists.fee_balance_rule_id := p_balance_rule_id;
329: ELSIF p_balance_type = 'HOLDS' THEN
339: igs_fi_balances_pkg.update_row(
340: X_ROWID => l_cur_rec_exists.rowid,
341: X_BALANCE_ID => l_cur_rec_exists.balance_id,
342: X_PARTY_ID => l_cur_rec_exists.party_id,
343: X_STANDARD_BALANCE => l_cur_rec_exists.standard_balance,
344: X_FEE_BALANCE => l_cur_rec_exists.fee_balance,
345: X_HOLDS_BALANCE => l_cur_rec_exists.holds_balance,
346: X_BALANCE_DATE => l_cur_rec_exists.balance_date,
347: X_FEE_BALANCE_RULE_ID => l_cur_rec_exists.fee_balance_rule_id,
353: CLOSE cur_rec_exists;
354: --Insert a row in igs_fi_balances table.
355: -- Removed the parameter subaccount_id, as a part of Bug # 2564643
356:
357: l_bal_standard := igs_fi_gen_gl.get_formatted_amount(l_bal_standard);
358: l_bal_fee := igs_fi_gen_gl.get_formatted_amount(l_bal_fee);
359: l_bal_hold := igs_fi_gen_gl.get_formatted_amount(l_bal_hold);
360:
361: igs_fi_balances_pkg.insert_row
361: igs_fi_balances_pkg.insert_row
362: ( X_ROWID => l_rowid,
363: X_BALANCE_ID => l_balance_id,
364: X_PARTY_ID => p_person_id,
365: X_STANDARD_BALANCE => l_bal_standard,
366: X_FEE_BALANCE => l_bal_fee,
367: X_HOLDS_BALANCE => l_bal_hold,
368: X_BALANCE_DATE => p_balance_date,
369: X_FEE_BALANCE_RULE_ID => l_bal_rule_fee,
455: --smvk 17-Sep-2002 Removed the input parameter p_subaccount_id and
456: -- its usage from this function as a part of Bug # 2564643.
457: --smadathi 03-Jul-2002 Bug 2443082. Modified insert_or_update_balance function. Incorporated invokation of
458: -- retro_update_balance function for retroactive updation of balances
459: --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
460: --Nishikant 18DEC2001 A new parameter p_source_id added to the procedure and
461: -- three parameters p_source_date, p_fee_type, p_credit_type_id removed
462: -- from the procedure. The code written to check the credit or charge
463: -- transaction is excluded or not based upon exclusion rules is
474: l_action_active CONSTANT VARCHAR2(10):= 'ACTIVE';
475: l_action_max CONSTANT VARCHAR2(10):= 'MAX';
476: l_hold_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'HOLDS';
477: l_fee_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'FEE';
478: l_std_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'STANDARD';
479:
480: l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
481: l_last_conversion_date DATE := NULL;
482: l_version_number igs_fi_balance_rules.version_number%TYPE := NULL;
531: -- OTHER as these have been obsolete.Modified tbh calls(igs_fi_balances_pkg)accordingly.
532: --smvk 17-Sep-2002 Removed the references of the parameter p_subaccount_id from this function, as a part of Bug # 2564643
533: --smadathi 03-Jul-2002 Bug 2443082. Modified cursor c_std_balance, c_fee_balance, c_holds_balance,c_inst_balance,
534: -- c_other_balance select statements.
535: --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
536: --Nishikant 18DEC2001 Code added to check for the parameter p_balance_type
537: -- is valid or not. Enh bug#2124001
538: -------------------------------------------------------------------
539:
538: -------------------------------------------------------------------
539:
540: /* Cursors to select the previously existing balance and rule id n table irresspective of the balance type..will select only for a particular balance type - see where clause */
541:
542: -- Cursor selects all the standard balances for person , subaccount combination
543: -- which are created before the balance date parameter in the descending order of balance date
544:
545: CURSOR c_std_balance IS
546: SELECT standard_balance
542: -- Cursor selects all the standard balances for person , subaccount combination
543: -- which are created before the balance date parameter in the descending order of balance date
544:
545: CURSOR c_std_balance IS
546: SELECT standard_balance
547: FROM igs_fi_balances
548: WHERE party_id = p_party_id
549: /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
550: AND standard_balance IS NOT NULL
546: SELECT standard_balance
547: FROM igs_fi_balances
548: WHERE party_id = p_party_id
549: /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
550: AND standard_balance IS NOT NULL
551: AND TO_CHAR(balance_date,'YYYYMMDD') <= TO_CHAR(p_balance_date,'YYYYMMDD')
552: ORDER by balance_date desc;
553:
554:
585: rec_upd_balance c_upd_balance%ROWTYPE;
586:
587: l_rowid rowid;
588: l_balance_id igs_fi_balances.balance_id%TYPE := NULL;
589: l_bal_standard igs_fi_balances.standard_balance%TYPE := NULL;
590: l_bal_fee igs_fi_balances.standard_balance%TYPE := NULL;
591: l_bal_hold igs_fi_balances.standard_balance%TYPE := NULL;
592: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
593: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
586:
587: l_rowid rowid;
588: l_balance_id igs_fi_balances.balance_id%TYPE := NULL;
589: l_bal_standard igs_fi_balances.standard_balance%TYPE := NULL;
590: l_bal_fee igs_fi_balances.standard_balance%TYPE := NULL;
591: l_bal_hold igs_fi_balances.standard_balance%TYPE := NULL;
592: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
593: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
594:
587: l_rowid rowid;
588: l_balance_id igs_fi_balances.balance_id%TYPE := NULL;
589: l_bal_standard igs_fi_balances.standard_balance%TYPE := NULL;
590: l_bal_fee igs_fi_balances.standard_balance%TYPE := NULL;
591: l_bal_hold igs_fi_balances.standard_balance%TYPE := NULL;
592: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
593: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
594:
595: l_n_table_standard_balance igs_fi_balances.standard_balance%TYPE := NUll;
591: l_bal_hold igs_fi_balances.standard_balance%TYPE := NULL;
592: l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
593: l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
594:
595: l_n_table_standard_balance igs_fi_balances.standard_balance%TYPE := NUll;
596: l_n_table_fee_balance igs_fi_balances.fee_balance%TYPE := NULL;
597: l_n_table_holds_balance igs_fi_balances.holds_balance%TYPE := NUll;
598:
599:
601: BEGIN
602:
603: /* Fetch the existin table balances */
604: OPEN c_std_balance;
605: FETCH c_std_balance INTO l_n_table_standard_balance;
606: CLOSE c_std_balance;
607:
608: OPEN c_fee_balance;
609: FETCH c_fee_balance INTO l_n_table_fee_balance;
614: CLOSE c_holds_balance;
615:
616:
617: /** Based on The balance type the new balance p_amount is added to the existing balance as below used for update_row and insert_row**/
618: IF p_balance_type = 'STANDARD' THEN
619: l_n_table_standard_balance := NVL(l_n_table_standard_balance,0) + NVL(p_amount,0);
620: ELSIF p_balance_type = 'FEE' THEN
621: l_n_table_fee_balance := NVL(l_n_table_fee_balance,0) + NVL(p_amount,0);
622: ELSIF p_balance_type = 'HOLDS' THEN
615:
616:
617: /** Based on The balance type the new balance p_amount is added to the existing balance as below used for update_row and insert_row**/
618: IF p_balance_type = 'STANDARD' THEN
619: l_n_table_standard_balance := NVL(l_n_table_standard_balance,0) + NVL(p_amount,0);
620: ELSIF p_balance_type = 'FEE' THEN
621: l_n_table_fee_balance := NVL(l_n_table_fee_balance,0) + NVL(p_amount,0);
622: ELSIF p_balance_type = 'HOLDS' THEN
623: l_n_table_holds_balance := NVL(l_n_table_holds_balance,0) + NVL(p_amount,0);
636:
637: IF l_b_insert THEN
638: /* When insert required then the corresping colummns balance amount + origibal table balance (will be 0 if nothing was present) and rule id is populated depending on the balance type - We prefer to keep the other fields null */
639:
640: IF p_balance_type = 'STANDARD' THEN
641: l_bal_standard := NVL(l_n_table_standard_balance,0) ;
642: l_bal_fee := NULL ;
643: l_bal_hold := NULL ;
644: l_bal_rule_fee := NULL ;
637: IF l_b_insert THEN
638: /* When insert required then the corresping colummns balance amount + origibal table balance (will be 0 if nothing was present) and rule id is populated depending on the balance type - We prefer to keep the other fields null */
639:
640: IF p_balance_type = 'STANDARD' THEN
641: l_bal_standard := NVL(l_n_table_standard_balance,0) ;
642: l_bal_fee := NULL ;
643: l_bal_hold := NULL ;
644: l_bal_rule_fee := NULL ;
645: l_bal_rule_hold := NULL ;
643: l_bal_hold := NULL ;
644: l_bal_rule_fee := NULL ;
645: l_bal_rule_hold := NULL ;
646: ELSIF p_balance_type = 'FEE' THEN
647: l_bal_standard := NULL ;
648: l_bal_fee := NVL(l_n_table_fee_balance,0) ;
649: l_bal_hold := NULL ;
650: l_bal_rule_fee := p_balance_rule_id ;
651: l_bal_rule_hold := NULL ;
649: l_bal_hold := NULL ;
650: l_bal_rule_fee := p_balance_rule_id ;
651: l_bal_rule_hold := NULL ;
652: ELSIF p_balance_type = 'HOLDS' THEN
653: l_bal_standard := NULL ;
654: l_bal_fee := NULL ;
655: l_bal_hold := NVL(l_n_table_holds_balance,0) ;
656: l_bal_rule_fee := NULL ;
657: l_bal_rule_hold := p_balance_rule_id ;
661: END IF;
662:
663: /* Start of insert into the IGS_FI_BALANCES */
664: BEGIN
665: l_bal_standard := igs_fi_gen_gl.get_formatted_amount(l_bal_standard);
666: l_bal_fee := igs_fi_gen_gl.get_formatted_amount(l_bal_fee);
667: l_bal_hold := igs_fi_gen_gl.get_formatted_amount(l_bal_hold);
668:
669:
670: igs_fi_balances_pkg.insert_row ( X_ROWID => l_rowid ,
671: X_BALANCE_ID => l_balance_id ,
672: X_PARTY_ID => p_party_id ,
673: /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
674: X_STANDARD_BALANCE => l_bal_standard ,
675: X_FEE_BALANCE => l_bal_fee ,
676: X_HOLDS_BALANCE => l_bal_hold ,
677: X_BALANCE_DATE => p_balance_date ,
678: X_FEE_BALANCE_RULE_ID => l_bal_rule_fee ,
687:
688: ELSIF NOT l_b_insert THEN
689: /* For update we update only the corresponding balance amoounts abd leave others same */
690:
691: IF p_balance_type = 'STANDARD' THEN
692: BEGIN
693: l_n_table_standard_balance :=igs_fi_gen_gl.get_formatted_amount(l_n_table_standard_balance);
694: igs_fi_balances_pkg.update_row
695: (
689: /* For update we update only the corresponding balance amoounts abd leave others same */
690:
691: IF p_balance_type = 'STANDARD' THEN
692: BEGIN
693: l_n_table_standard_balance :=igs_fi_gen_gl.get_formatted_amount(l_n_table_standard_balance);
694: igs_fi_balances_pkg.update_row
695: (
696: X_ROWID => rec_upd_balance.rowid ,
697: X_BALANCE_ID => rec_upd_balance.balance_id ,
696: X_ROWID => rec_upd_balance.rowid ,
697: X_BALANCE_ID => rec_upd_balance.balance_id ,
698: X_PARTY_ID => rec_upd_balance.party_id ,
699: /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
700: X_STANDARD_BALANCE => l_n_table_standard_balance ,
701: X_FEE_BALANCE => rec_upd_balance.fee_balance,
702: X_HOLDS_BALANCE => rec_upd_balance.holds_balance ,
703: X_BALANCE_DATE => rec_upd_balance.balance_date ,
704: X_FEE_BALANCE_RULE_ID => rec_upd_balance.fee_balance_rule_id ,
719: ( X_ROWID => rec_upd_balance.rowid ,
720: X_BALANCE_ID => rec_upd_balance.balance_id ,
721: X_PARTY_ID => rec_upd_balance.party_id ,
722: /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
723: X_STANDARD_BALANCE => rec_upd_balance.standard_balance ,
724: X_FEE_BALANCE => l_n_table_fee_balance,
725: X_HOLDS_BALANCE => rec_upd_balance.holds_balance ,
726: X_BALANCE_DATE => rec_upd_balance.balance_date ,
727: X_FEE_BALANCE_RULE_ID => p_balance_rule_id ,
741: ( X_ROWID => rec_upd_balance.rowid ,
742: X_BALANCE_ID => rec_upd_balance.balance_id ,
743: X_PARTY_ID => rec_upd_balance.party_id ,
744: /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
745: X_STANDARD_BALANCE => rec_upd_balance.standard_balance ,
746: X_FEE_BALANCE => rec_upd_balance.fee_balance,
747: X_HOLDS_BALANCE => l_n_table_holds_balance ,
748: X_BALANCE_DATE => rec_upd_balance.balance_date ,
749: X_FEE_BALANCE_RULE_ID => rec_upd_balance.fee_balance_rule_id ,
778: IF p_amount <> 0 THEN
779: -- The entire update balance process happens only if the passed parameter p_amount is not equal
780: -- to 0 since there is no use updating a 0 balance.
781:
782: --For Standard balance,there is no need to derive the balance_rule_id and hence no exclusion
783: --rules can be checked.
784: IF p_balance_type <> l_std_bal_type THEN
785: --Fetch balance_rule_id of active Holds balance_type.
786: IF p_balance_type = l_hold_bal_type THEN
898: -- vvutukur 04-Oct-2002 Enh#2562745.Added a new mandatory parametre p_balance_rule_id.Removed
899: -- cursor c_balance and its usage in the code.Modified cursor c_bal_type
900: -- as 'INSTALLMENT','OTHER' balance types have been obsoleted.
901: -- smvk 17-Sep-2002 Removed the references to subaccount_id, as a part of Bug # 2564643
902: -- vvutukur 01-may-2002 Bug 2329042. Modified to return FALSE for Standard Balance Type as there
903: -- there will be no exclusion rules defined for Standard Bal. type.
904: -- smadathi 10-APR-2002 Bug 2289191. References to enabled_flag column removed from cursor
905: -- c_subacct_excl, c_ftype_excl, c_ctyp_excl select list.
906: -- (reverse chronological order - newest change first)
899: -- cursor c_balance and its usage in the code.Modified cursor c_bal_type
900: -- as 'INSTALLMENT','OTHER' balance types have been obsoleted.
901: -- smvk 17-Sep-2002 Removed the references to subaccount_id, as a part of Bug # 2564643
902: -- vvutukur 01-may-2002 Bug 2329042. Modified to return FALSE for Standard Balance Type as there
903: -- there will be no exclusion rules defined for Standard Bal. type.
904: -- smadathi 10-APR-2002 Bug 2289191. References to enabled_flag column removed from cursor
905: -- c_subacct_excl, c_ftype_excl, c_ctyp_excl select list.
906: -- (reverse chronological order - newest change first)
907: -------------------------------------------------------------------------------
907: -------------------------------------------------------------------------------
908: l_fee_type igs_fi_inv_int_v.fee_type%TYPE;
909: l_credit_type_id igs_fi_credits_v.credit_type_id%TYPE;
910: l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE := p_balance_rule_id;
911: l_std_bal igs_lookup_values.lookup_code%TYPE := 'STANDARD';
912: l_sysdate DATE := TRUNC(SYSDATE);
913: l_lkp_type igs_lookup_values.lookup_type%TYPE := 'IGS_FI_BALANCE_TYPE';
914:
915: CURSOR c_bal_type(cp_balance_type igs_fi_balance_rules.balance_name%TYPE) IS
1111: -- for retroactive updation all the records whose balance date is greater than the
1112: -- parameter balance date will be fetched and updated with the amount
1113: FOR rec_c_igs_fi_balances IN c_igs_fi_balances
1114: LOOP
1115: IF p_c_balance_type = 'STANDARD'
1116: THEN
1117: BEGIN
1118: igs_fi_balances_pkg.update_row
1119: (
1120: X_ROWID => rec_c_igs_fi_balances.rowid ,
1121: X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1122: X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1123: /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1124: X_STANDARD_BALANCE => igs_fi_gen_gl.get_formatted_amount(NVL(rec_c_igs_fi_balances.standard_balance,0) + NVL(p_n_amount,0)) ,
1125: X_FEE_BALANCE => rec_c_igs_fi_balances.fee_balance ,
1126: X_HOLDS_BALANCE => rec_c_igs_fi_balances.holds_balance ,
1127: X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1128: X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1143: X_ROWID => rec_c_igs_fi_balances.rowid ,
1144: X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1145: X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1146: /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1147: X_STANDARD_BALANCE => rec_c_igs_fi_balances.standard_balance ,
1148: X_FEE_BALANCE => igs_fi_gen_gl.get_formatted_amount(NVL(rec_c_igs_fi_balances.fee_balance,0) + NVL(p_n_amount,0) ) ,
1149: X_HOLDS_BALANCE => rec_c_igs_fi_balances.holds_balance ,
1150: X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1151: X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1166: X_ROWID => rec_c_igs_fi_balances.rowid ,
1167: X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1168: X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1169: /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1170: X_STANDARD_BALANCE => rec_c_igs_fi_balances.standard_balance ,
1171: X_FEE_BALANCE => rec_c_igs_fi_balances.fee_balance ,
1172: X_HOLDS_BALANCE => igs_fi_gen_gl.get_formatted_amount( NVL(rec_c_igs_fi_balances.holds_balance,0) + NVL(p_n_amount,0) ) ,
1173: X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1174: X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1528: -- and the balance_rule_id under holds_balance_rule_id
1529: IGS_FI_BALANCES_PKG.update_row ( x_rowid => l_rec_get_balances.rowid,
1530: x_balance_id => l_rec_get_balances.balance_id,
1531: x_party_id => l_rec_get_balances.party_id,
1532: x_standard_balance => l_rec_get_balances.standard_balance,
1533: x_fee_balance => l_rec_get_balances.fee_balance,
1534: x_holds_balance => l_balance_sum,
1535: x_balance_date => l_rec_get_balances.balance_date,
1536: x_fee_balance_rule_id => l_rec_get_balances.fee_balance_rule_id,