[Home] [Help]
1: PACKAGE BODY LNS_FINANCIALS AS
2: /* $Header: LNS_FINANCIAL_B.pls 120.79.12020000.4 2013/02/11 16:13:32 scherkas ship $ */
3:
4:
5: --------------------------------------------
6: -- declaration of global variables and types
7: --------------------------------------------
8: G_DEBUG_COUNT NUMBER := 0;
9: G_DEBUG BOOLEAN := FALSE;
10: G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS_B.pls';
11: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS';
12:
13: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
14: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
7: --------------------------------------------
8: G_DEBUG_COUNT NUMBER := 0;
9: G_DEBUG BOOLEAN := FALSE;
10: G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS_B.pls';
11: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS';
12:
13: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
14: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
15:
9: G_DEBUG BOOLEAN := FALSE;
10: G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS_B.pls';
11: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS';
12:
13: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
14: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
15:
16: --------------------------------------------
17: -- internal package routines
10: G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS_B.pls';
11: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FINANCIALS';
12:
13: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
14: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
15:
16: --------------------------------------------
17: -- internal package routines
18: --------------------------------------------
55: ||
56: || Creation date: 12/08/2003 6:31PM
57: ||
58: */
59: procedure printAmortizationTable(p_amort_tbl IN lns_financials.amortization_tbl)
60:
61: is
62:
63: l_api_name varchar2(30);
122: end printAmortizationTable;
123:
124: /* routine will sort loanActivities by activityDate
125: */
126: procedure sortRows(p_loan_activity_tbl in out nocopy LNS_FINANCIALS.LOAN_ACTIVITY_TBL)
127:
128: is
129: j number; -- counter
130: l_tmp_row LNS_FINANCIALS.LOAN_ACTIVITY_REC; -- to store temp row
126: procedure sortRows(p_loan_activity_tbl in out nocopy LNS_FINANCIALS.LOAN_ACTIVITY_TBL)
127:
128: is
129: j number; -- counter
130: l_tmp_row LNS_FINANCIALS.LOAN_ACTIVITY_REC; -- to store temp row
131: l_min date; -- minimum date
132:
133: begin
134:
419: l_return_status VARCHAR2(1);
420: l_msg_count NUMBER;
421: l_msg_data VARCHAR2(32767);
422:
423: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
424: l_amount number;
425: l_status varchar2(30);
426:
427: CURSOR c_terms(p_Loan_id NUMBER) IS
470: RAISE FND_API.G_EXC_ERROR;
471: end;
472:
473: -- rate schedules should have one row
474: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
475:
476: if l_rate_tbl.count = 0 then
477: FND_MESSAGE.Set_Name('LNS', 'LNS_NO_RATE_SCHEDULE');
478: FND_MSG_PUB.Add;
479: RAISE FND_API.G_EXC_ERROR;
480: end if;
481:
482: Begin
483: l_amount := lns_financials.getRemainingBalance(p_loan_id);
484: if l_amount <= 0 then
485: FND_MESSAGE.Set_Name('LNS', 'LNS_NO_AMOUNT');
486: FND_MSG_PUB.Add;
487: RAISE FND_API.G_EXC_ERROR;
545: ,p_init_msg_list IN VARCHAR2
546: ,p_commit IN VARCHAR2
547: ,p_loan_ID IN NUMBER
548: ,p_based_on_terms IN VARCHAR2
549: ,x_amort_tbl OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_TBL
550: ,x_return_status OUT NOCOPY VARCHAR2
551: ,x_msg_count OUT NOCOPY NUMBER
552: ,x_msg_data OUT NOCOPY VARCHAR2)
553:
557: l_return_status VARCHAR2(1);
558: l_msg_count NUMBER;
559: l_msg_data VARCHAR2(32767);
560:
561: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
562: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
563: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
564: l_key NUMBER;
565: b_showActual boolean := false;
558: l_msg_count NUMBER;
559: l_msg_data VARCHAR2(32767);
560:
561: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
562: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
563: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
564: l_key NUMBER;
565: b_showActual boolean := false;
566: l_last_installment_billed number;
559: l_msg_data VARCHAR2(32767);
560:
561: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
562: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
563: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
564: l_key NUMBER;
565: b_showActual boolean := false;
566: l_last_installment_billed number;
567:
583: l_manual_fee_amount number;
584: l_records_to_destroy number;
585: l_start_date number;
586: l_funded_amount number;
587: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
588: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
589:
590: l_last_payment number;
591: l_disb_header_id number;
584: l_records_to_destroy number;
585: l_start_date number;
586: l_funded_amount number;
587: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
588: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
589:
590: l_last_payment number;
591: l_disb_header_id number;
592: l_billed varchar2(1);
597: l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
598: l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
599: l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
600: l_AMORT_METHOD varchar2(30);
601: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
602: l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
603:
604: cursor c_fund_sched_exist(p_loan_id number) is
605: select decode(loan.loan_class_code,
655: FND_MSG_PUB.ADD;
656: RAISE FND_API.G_EXC_ERROR;
657: end if;
658:
659: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
660: ,p_based_on_terms => p_based_on_terms
661: ,p_phase => 'TERM');
662:
663: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_STATUS = ' || l_loan_details.LOAN_STATUS);
702:
703: end if;
704:
705: -- call amortization API
706: lns_financials.amortizeLoan(p_loan_Id => p_loan_id
707: ,p_based_on_terms => p_based_on_terms
708: ,p_installment_number => null
709: ,x_loan_amort_tbl => l_amort_tbl);
710: else
798: l_amort_tbl(i).CAP_INT_AMOUNT := 0;
799: l_amort_tbl(i).EARLY_PAY_CR_AMOUNT := 0;
800: -- add the record to the amortization table
801:
802: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
803: l_amort_tbl(i).INTEREST_RATE := l_rate_tbl(1).annual_rate;
804:
805: l_amort_tbl(i).total := l_fee_amount;
806: end if;
1082: ,p_init_msg_list IN VARCHAR2
1083: ,p_commit IN VARCHAR2
1084: ,p_loan_ID IN NUMBER
1085: ,p_installment_number IN NUMBER
1086: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
1087: ,x_return_status OUT NOCOPY VARCHAR2
1088: ,x_msg_count OUT NOCOPY NUMBER
1089: ,x_msg_data OUT NOCOPY VARCHAR2)
1090: is
1087: ,x_return_status OUT NOCOPY VARCHAR2
1088: ,x_msg_count OUT NOCOPY NUMBER
1089: ,x_msg_data OUT NOCOPY VARCHAR2)
1090: is
1091: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1092: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1093: l_count NUMBER;
1094: l_api_name varchar2(40);
1095: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1088: ,x_msg_count OUT NOCOPY NUMBER
1089: ,x_msg_data OUT NOCOPY VARCHAR2)
1090: is
1091: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1092: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1093: l_count NUMBER;
1094: l_api_name varchar2(40);
1095: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1096:
1091: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1092: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1093: l_count NUMBER;
1094: l_api_name varchar2(40);
1095: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1096:
1097: l_theoretical_balance NUMBER;
1098: l_actual_balance NUMBER;
1099: l_api_version_number number;
1158: else
1159: i := p_installment_number;
1160: end if;
1161: */
1162: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
1163: ,p_based_on_terms => 'CURRENT'
1164: ,p_phase => 'TERM');
1165:
1166: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting next installment info...');
1167: if l_loan_details.custom_schedule = 'N' then
1168: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - regular loan');
1169:
1170: -- this is a standard non-customized loan
1171: lns_financials.amortizeLoan(p_loan_Id => p_loan_id
1172: ,p_based_on_terms => 'CURRENT'
1173: ,p_installment_number => p_installment_number
1174: ,x_loan_amort_tbl => l_amort_tbl);
1175: l_count := l_amort_tbl.count;
1226: then --and p_installment_number > 1 then
1227:
1228: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - checking if we need to reamortize...');
1229:
1230: lns_financials.amortizeLoan(p_loan_Id => p_loan_id
1231: ,p_based_on_terms => 'ORIGINAL'
1232: ,p_installment_number => p_installment_number
1233: ,x_loan_amort_tbl => l_amort_tbl);
1234:
1415: procedure preProcessOpenInstallment(p_init_msg_list IN VARCHAR2
1416: ,p_commit IN VARCHAR2
1417: ,p_loan_ID IN NUMBER
1418: ,p_installment_number IN NUMBER
1419: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
1420: ,x_return_status OUT NOCOPY VARCHAR2
1421: ,x_msg_count OUT NOCOPY NUMBER
1422: ,x_msg_data OUT NOCOPY VARCHAR2)
1423: is
1420: ,x_return_status OUT NOCOPY VARCHAR2
1421: ,x_msg_count OUT NOCOPY NUMBER
1422: ,x_msg_data OUT NOCOPY VARCHAR2)
1423: is
1424: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1425: l_count NUMBER;
1426: l_api_name varchar2(40);
1427: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1428:
1423: is
1424: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1425: l_count NUMBER;
1426: l_api_name varchar2(40);
1427: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1428:
1429: l_return_status VARCHAR2(1);
1430: l_msg_count NUMBER;
1431: l_msg_data VARCHAR2(32767);
1428:
1429: l_return_status VARCHAR2(1);
1430: l_msg_count NUMBER;
1431: l_msg_data VARCHAR2(32767);
1432: l_fees_tbl LNS_FINANCIALS.FEES_TBL;
1433: i number;
1434: l_installment number;
1435:
1436: begin
1454: -- ---------------------------------------------------------------
1455: -- Beginning of API body
1456: --
1457: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting loan details');
1458: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
1459: ,p_based_on_terms => 'CURRENT'
1460: ,p_phase => 'OPEN');
1461:
1462: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting Open Installment');
1459: ,p_based_on_terms => 'CURRENT'
1460: ,p_phase => 'OPEN');
1461:
1462: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting Open Installment');
1463: lns_financials.getOpenInstallment(p_init_msg_list => 'T'
1464: ,p_loan_ID => p_loan_ID
1465: ,p_installment_number => p_installment_number
1466: ,x_fees_tbl => l_fees_tbl
1467: ,x_amortization_rec => l_amortization_rec
1553: ,p_init_msg_list IN VARCHAR2
1554: ,p_commit IN VARCHAR2
1555: ,p_loan_Id in number
1556: ,p_installment_number in number
1557: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
1558: ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
1559: ,x_return_status OUT NOCOPY VARCHAR2
1560: ,x_msg_count OUT NOCOPY NUMBER
1561: ,x_msg_data OUT NOCOPY VARCHAR2)
1554: ,p_commit IN VARCHAR2
1555: ,p_loan_Id in number
1556: ,p_installment_number in number
1557: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
1558: ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
1559: ,x_return_status OUT NOCOPY VARCHAR2
1560: ,x_msg_count OUT NOCOPY NUMBER
1561: ,x_msg_data OUT NOCOPY VARCHAR2)
1562: is
1559: ,x_return_status OUT NOCOPY VARCHAR2
1560: ,x_msg_count OUT NOCOPY NUMBER
1561: ,x_msg_data OUT NOCOPY VARCHAR2)
1562: is
1563: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1564: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1565: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1566: l_count NUMBER;
1567: l_api_version_number number;
1560: ,x_msg_count OUT NOCOPY NUMBER
1561: ,x_msg_data OUT NOCOPY VARCHAR2)
1562: is
1563: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1564: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1565: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1566: l_count NUMBER;
1567: l_api_version_number number;
1568: l_return_status VARCHAR2(1);
1561: ,x_msg_data OUT NOCOPY VARCHAR2)
1562: is
1563: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
1564: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1565: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1566: l_count NUMBER;
1567: l_api_version_number number;
1568: l_return_status VARCHAR2(1);
1569: l_msg_count NUMBER;
1570: l_msg_data VARCHAR2(32767);
1571: l_api_name varchar2(25);
1572: l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1573: l_fees_tbl_1 LNS_FEE_ENGINE.FEE_CALC_TBL;
1574: l_fees_tbl_2 LNS_FINANCIALS.FEES_TBL;
1575: l_total_fees number;
1576: l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1577: l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL; -- define what event(s) we are processing fees for
1578: i number;
1635: l_amortization_rec.begin_balance := 0;
1636: l_amortization_rec.end_balance := 0;
1637:
1638: -- move logic for billing custom loans into FINANCIALS API
1639: l_loan_Details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', 'TERM');
1640:
1641: if l_loan_details.custom_schedule = 'N' then
1642:
1643: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan is not customized');
1656: ,x_msg_data => l_msg_data);
1657: end if;
1658:
1659: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calling amortizeLoan....');
1660: lns_financials.amortizeLoan(p_loan_Id => p_loan_id
1661: ,p_installment_number => p_installment_number
1662: ,p_based_on_terms => 'CURRENT'
1663: ,x_loan_amort_tbl => l_amortization_tbl);
1664:
1910: end getInstallment;
1911:
1912:
1913: -- created for bug 6599682: EQUALLY SPREAD PRINCIPAL FROM IO PERIODS FOR EPRP LOANS
1914: function get_num_non_ro_instal(p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
1915: ,p_from_installment in NUMBER
1916: ,p_to_installment in NUMBER) return NUMBER
1917: is
1918: l_local_rate_schedule LNS_FINANCIALS.RATE_SCHEDULE_TBL;
1914: function get_num_non_ro_instal(p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
1915: ,p_from_installment in NUMBER
1916: ,p_to_installment in NUMBER) return NUMBER
1917: is
1918: l_local_rate_schedule LNS_FINANCIALS.RATE_SCHEDULE_TBL;
1919: l_num_non_ro_payments number;
1920: l_total_installments number;
1921: i number;
1922: j number;
2013: *=======================================================================*/
2014: procedure getOpenInstallment(p_init_msg_list IN VARCHAR2
2015: ,p_loan_Id in number
2016: ,p_installment_number in number
2017: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
2018: ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
2019: ,x_return_status OUT NOCOPY VARCHAR2
2020: ,x_msg_count OUT NOCOPY NUMBER
2021: ,x_msg_data OUT NOCOPY VARCHAR2)
2014: procedure getOpenInstallment(p_init_msg_list IN VARCHAR2
2015: ,p_loan_Id in number
2016: ,p_installment_number in number
2017: ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
2018: ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
2019: ,x_return_status OUT NOCOPY VARCHAR2
2020: ,x_msg_count OUT NOCOPY NUMBER
2021: ,x_msg_data OUT NOCOPY VARCHAR2)
2022: is
2019: ,x_return_status OUT NOCOPY VARCHAR2
2020: ,x_msg_count OUT NOCOPY NUMBER
2021: ,x_msg_data OUT NOCOPY VARCHAR2)
2022: is
2023: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2024: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2020: ,x_msg_count OUT NOCOPY NUMBER
2021: ,x_msg_data OUT NOCOPY VARCHAR2)
2022: is
2023: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2024: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2028: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2022: is
2023: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2024: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2028: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2029: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2030:
2023: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2024: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2028: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2029: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2030:
2031: l_count NUMBER;
2024: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2028: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2029: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2030:
2031: l_count NUMBER;
2032: l_return_status VARCHAR2(1);
2025: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2026: l_rate_schedule_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2027: l_rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2028: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2029: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2030:
2031: l_count NUMBER;
2032: l_return_status VARCHAR2(1);
2033: l_msg_count NUMBER;
2035: l_api_name varchar2(25);
2036:
2037: l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2038: l_fees_tbl_1 LNS_FEE_ENGINE.FEE_CALC_TBL;
2039: l_fees_tbl_2 LNS_FINANCIALS.FEES_TBL;
2040: l_total_fees number;
2041: l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
2042: l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL; -- define what event(s) we are processing fees for
2043: i number;
2103: l_amortization_rec.begin_balance := 0;
2104: l_amortization_rec.end_balance := 0;
2105:
2106: -- move logic for billing custom loans into FINANCIALS API
2107: l_loan_Details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', 'OPEN');
2108: l_rates_tbl := lns_financials.getRateSchedule(p_loan_id, 'OPEN');
2109:
2110: -- call projection API
2111: lns_financials.loanProjection(p_loan_details => l_loan_Details
2104: l_amortization_rec.end_balance := 0;
2105:
2106: -- move logic for billing custom loans into FINANCIALS API
2107: l_loan_Details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', 'OPEN');
2108: l_rates_tbl := lns_financials.getRateSchedule(p_loan_id, 'OPEN');
2109:
2110: -- call projection API
2111: lns_financials.loanProjection(p_loan_details => l_loan_Details
2112: ,p_based_on_terms => 'CURRENT'
2107: l_loan_Details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', 'OPEN');
2108: l_rates_tbl := lns_financials.getRateSchedule(p_loan_id, 'OPEN');
2109:
2110: -- call projection API
2111: lns_financials.loanProjection(p_loan_details => l_loan_Details
2112: ,p_based_on_terms => 'CURRENT'
2113: ,p_rate_schedule => l_rates_tbl
2114: ,x_loan_amort_tbl => l_amortization_tbl);
2115:
2218: *=======================================================================*/
2219: function getRatesTable(p_index_rate_id in number
2220: ,p_index_date in date
2221: ,p_rate_change_frequency in varchar2
2222: ,p_maturity_date in date) return LNS_FINANCIALS.RATE_SCHEDULE_TBL is
2223:
2224: l_intial_date date;
2225: l_rate_date date;
2226: l_rate number;
2223:
2224: l_intial_date date;
2225: l_rate_date date;
2226: l_rate number;
2227: l_Rates_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2228: i number;
2229: l_api_name varchar2(25);
2230:
2231: begin
2314: || 16/01/2008 scherkas Fixed bug 6749924
2315: || 08/04/2008 scherkas Fixed bug 6945153: change procedure to query past installments
2316: || 18/06/2008 scherkas Fixed bug 7184830
2317: *=======================================================================*/
2318: procedure amortizeSIPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
2319: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
2320: ,p_based_on_terms in varchar2
2321: ,p_installment_number in number
2322: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
2315: || 08/04/2008 scherkas Fixed bug 6945153: change procedure to query past installments
2316: || 18/06/2008 scherkas Fixed bug 7184830
2317: *=======================================================================*/
2318: procedure amortizeSIPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
2319: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
2320: ,p_based_on_terms in varchar2
2321: ,p_installment_number in number
2322: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
2323: is
2318: procedure amortizeSIPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
2319: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
2320: ,p_based_on_terms in varchar2
2321: ,p_installment_number in number
2322: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
2323: is
2324: l_return_status varchar2(1);
2325: l_msg_count NUMBER;
2326: l_msg_data VARCHAR2(32767);
2341: l_reamortize_from_installment number;
2342: l_reamortize_amount number;
2343: l_annualized_rate number; -- annual rate on the loan
2344: l_intervals_remaining number;
2345: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2346: l_current_rate_id number;
2347: l_previous_rate_id number;
2348: l_precision number;
2349:
2352: l_periodic_rate number;
2353: l_maturity_date date;
2354: l_amortized_maturity_date date;
2355:
2356: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2357: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2358: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2359: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2360: l_principal_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2353: l_maturity_date date;
2354: l_amortized_maturity_date date;
2355:
2356: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2357: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2358: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2359: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2360: l_principal_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2361: l_loan_start_date date;
2354: l_amortized_maturity_date date;
2355:
2356: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
2357: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2358: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2359: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2360: l_principal_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2361: l_loan_start_date date;
2362: l_num_pay_dates number; -- number of dates on installment schedule
3089: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ': l_num_prin_payments = ' || l_num_prin_payments);
3090:
3091: l_remaining_balance := l_remaining_balance_theory + l_increased_amount1;
3092:
3093: l_hidden_periodic_prin := lns_financials.calculateEPPayment(p_loan_amount => l_remaining_balance
3094: ,p_num_intervals => l_num_prin_payments
3095: ,p_ending_balance=> l_balloon_amount
3096: ,p_pay_in_arrears=> l_prin_pay_in_arrears);
3097:
3125: -- now we will calculate the interest due for this period
3126: if (p_based_on_terms = 'CURRENT' and l_detail_int_calc_flag = true) then
3127:
3128: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
3129: LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => l_loan_id,
3130: p_calc_method => l_calc_method,
3131: p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date,
3132: p_period_end_date => l_payment_tbl(l_installment_number).period_end_date,
3133: p_interest_rate => l_annualized_rate,
3162: end if;
3163:
3164: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
3165: -- calculate additional interest on unpaid principal
3166: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
3167: p_calc_method => l_calc_method,
3168: p_period_start_date => l_add_start_date,
3169: p_period_end_date => l_add_end_date,
3170: p_interest_rate => l_annualized_rate,
3183: l_add_prin_interest := round(l_add_prin_interest, l_precision);
3184:
3185: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
3186: -- calculate additional interest on unpaid interest
3187: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
3188: p_calc_method => l_calc_method,
3189: p_period_start_date => l_add_start_date,
3190: p_period_end_date => l_add_end_date,
3191: p_interest_rate => l_annualized_rate,
3210: end if;
3211:
3212: -- calculate interest credit on early payment
3213: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating early payment credit amount...');
3214: LNS_FINANCIALS.CALC_EARLY_PAY_CR(p_loan_id => l_loan_id,
3215: p_calc_method => l_calc_method,
3216: p_installment => l_installment_number,
3217: p_interest_rate => l_previous_annualized,
3218: p_day_count_method => l_day_count_method,
3227:
3228: if (l_calc_method = 'SIMPLE') then
3229:
3230: -- recalculate periodic rate for each period if day counting methodolgy varies
3231: l_periodic_rate := lns_financials.getPeriodicRate(
3232: p_payment_freq => l_payment_frequency
3233: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
3234: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
3235: ,p_annualized_rate => l_annualized_rate
3247: ,p_target => 'INTEREST');
3248:
3249: end if;
3250:
3251: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
3252: ,p_from_date => l_payment_tbl(l_installment_number).period_begin_date
3253: ,p_to_date => l_payment_tbl(l_installment_number).period_end_date
3254: ,p_calc_method => 'TARGET'
3255: ,p_phase => 'TERM'
3259: ,x_begin_balance => l_balance1
3260: ,x_end_balance => l_balance2);
3261:
3262: l_wtd_balance := l_wtd_balance + l_prev_cap_int;
3263: l_norm_interest := lns_financials.calculateInterest(p_amount => l_wtd_balance
3264: ,p_periodic_rate => l_periodic_rate
3265: ,p_compounding_period => null);
3266: l_norm_interest := round(l_norm_interest, l_precision);
3267:
3278:
3279: if (l_calc_method = 'SIMPLE') then
3280:
3281: -- recalculate periodic rate for each period if day counting methodolgy varies
3282: l_periodic_rate := lns_financials.getPeriodicRate(
3283: p_payment_freq => l_payment_frequency
3284: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
3285: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
3286: ,p_annualized_rate => l_annualized_rate
3298: ,p_target => 'INTEREST');
3299:
3300: end if;
3301:
3302: l_norm_interest := lns_financials.calculateInterest(p_amount => l_remaining_balance_theory1
3303: ,p_periodic_rate => l_periodic_rate
3304: ,p_compounding_period => null);
3305: l_norm_interest := round(l_norm_interest, l_precision);
3306:
3734: | Date Author Description of Changes
3735: || 09/13/2007 scherkas Created
3736: || 16/01/2008 scherkas Fixed bug 6749924
3737: *=======================================================================*/
3738: procedure amortizeEPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
3739: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
3740: ,p_based_on_terms in varchar2
3741: ,p_installment_number in number
3742: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
3735: || 09/13/2007 scherkas Created
3736: || 16/01/2008 scherkas Fixed bug 6749924
3737: *=======================================================================*/
3738: procedure amortizeEPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
3739: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
3740: ,p_based_on_terms in varchar2
3741: ,p_installment_number in number
3742: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
3743: is
3738: procedure amortizeEPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
3739: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
3740: ,p_based_on_terms in varchar2
3741: ,p_installment_number in number
3742: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
3743: is
3744: l_return_status varchar2(1);
3745: l_msg_count NUMBER;
3746: l_msg_data VARCHAR2(32767);
3762: l_reamortize_amount number;
3763: l_annualized_rate number; -- annual rate on the loan
3764: l_intervals_remaining number;
3765: l_amortization_intervals number; -- number of intervals to amortize over
3766: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
3767: l_current_rate_id number;
3768: l_previous_rate_id number;
3769: l_precision number;
3770:
3773: l_periodic_rate number;
3774: l_maturity_date date;
3775: l_amortized_maturity_date date;
3776:
3777: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
3778: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
3779: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
3780: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3781: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3774: l_maturity_date date;
3775: l_amortized_maturity_date date;
3776:
3777: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
3778: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
3779: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
3780: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3781: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3782: l_loan_start_date date;
3775: l_amortized_maturity_date date;
3776:
3777: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
3778: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
3779: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
3780: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3781: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3782: l_loan_start_date date;
3783: l_num_pay_dates number; -- number of dates on installment schedule
4177: if l_rate_tbl.count = 1 then
4178: l_previous_annualized := l_rate_tbl(1).annual_rate;
4179: l_previous_interest_only_flag := l_rate_tbl(1).interest_only_flag;
4180: else
4181: l_rate_details := lns_financials.getRateDetails(p_installment => l_last_installment_billed
4182: ,p_rate_tbl => l_rate_tbl);
4183: l_previous_annualized := l_rate_details.annual_rate;
4184: l_previous_interest_only_flag := l_rate_details.interest_only_flag;
4185: end if;
4408:
4409: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ': l_non_ro_intervals = ' || l_non_ro_intervals);
4410: l_remaining_balance := l_remaining_balance_theory + l_increased_amount1;
4411:
4412: l_periodic_principal := lns_financials.calculateEPPayment(p_loan_amount => l_remaining_balance
4413: ,p_num_intervals => l_non_ro_intervals
4414: --,p_num_intervals => l_amortization_intervals
4415: ,p_ending_balance=> l_balloon_amount
4416: ,p_pay_in_arrears=> l_pay_in_arrears);
4441: -- now we will caculate the interest due for this period
4442: if (p_based_on_terms = 'CURRENT' and l_detail_int_calc_flag = true) then
4443:
4444: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
4445: LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => l_loan_id,
4446: p_calc_method => l_calc_method,
4447: p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date,
4448: p_period_end_date => l_payment_tbl(l_installment_number).period_end_date,
4449: p_interest_rate => l_annualized_rate,
4478: end if;
4479:
4480: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
4481: -- calculate additional interest on unpaid principal
4482: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
4483: p_calc_method => l_calc_method,
4484: p_period_start_date => l_add_start_date,
4485: p_period_end_date => l_add_end_date,
4486: p_interest_rate => l_annualized_rate,
4499: l_add_prin_interest := round(l_add_prin_interest, l_precision);
4500:
4501: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
4502: -- calculate additional interest on unpaid interest
4503: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
4504: p_calc_method => l_calc_method,
4505: p_period_start_date => l_add_start_date,
4506: p_period_end_date => l_add_end_date,
4507: p_interest_rate => l_annualized_rate,
4526: end if;
4527:
4528: -- calculate interest credit on early payment
4529: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating early payment credit amount...');
4530: LNS_FINANCIALS.CALC_EARLY_PAY_CR(p_loan_id => l_loan_id,
4531: p_calc_method => l_calc_method,
4532: p_installment => l_installment_number,
4533: p_interest_rate => l_previous_annualized,
4534: p_day_count_method => l_day_count_method,
4542: elsif (p_based_on_terms <> 'CURRENT' and l_detail_int_calc_flag = true and l_bill_on_appr_amounts = 'N') then
4543:
4544: if (l_calc_method = 'SIMPLE') then
4545:
4546: l_periodic_rate := lns_financials.getPeriodicRate(
4547: p_payment_freq => l_payment_frequency
4548: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
4549: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
4550: ,p_annualized_rate => l_annualized_rate
4562: ,p_target => 'INTEREST');
4563:
4564: end if;
4565:
4566: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
4567: ,p_from_date => l_payment_tbl(l_installment_number).period_begin_date
4568: ,p_to_date => l_payment_tbl(l_installment_number).period_end_date
4569: ,p_calc_method => 'TARGET'
4570: ,p_phase => 'TERM'
4574: ,x_begin_balance => l_balance1
4575: ,x_end_balance => l_balance2);
4576:
4577: l_wtd_balance := l_wtd_balance + l_prev_cap_int;
4578: l_norm_interest := lns_financials.calculateInterest(p_amount => l_wtd_balance
4579: ,p_periodic_rate => l_periodic_rate
4580: ,p_compounding_period => null);
4581: l_norm_interest := round(l_norm_interest, l_precision);
4582:
4592: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': interest is based upon an amount of ' || l_remaining_balance_theory1);
4593:
4594: if (l_calc_method = 'SIMPLE') then
4595:
4596: l_periodic_rate := lns_financials.getPeriodicRate(
4597: p_payment_freq => l_payment_frequency
4598: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
4599: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
4600: ,p_annualized_rate => l_annualized_rate
4612: ,p_target => 'INTEREST');
4613:
4614: end if;
4615:
4616: l_norm_interest := lns_financials.calculateInterest(p_amount => l_remaining_balance_theory1
4617: ,p_periodic_rate => l_periodic_rate
4618: ,p_compounding_period => null);
4619: l_norm_interest := round(l_norm_interest, l_precision);
4620:
4948: || Date Author Description of Changes
4949: || 12/12/2003 11:35AM raverma Created
4950: || 2/26/2004 raverma coded in multiple rates
4951: || 10/28/2004 raverma added interest only flag
4952: || 06/20/2008 scherkas Synch amortizeLoan procedure with LNS_FINANCIALS 115.112 version
4953: *=======================================================================*/
4954: procedure amortizeLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
4955: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
4956: ,p_based_on_terms in varchar2
4950: || 2/26/2004 raverma coded in multiple rates
4951: || 10/28/2004 raverma added interest only flag
4952: || 06/20/2008 scherkas Synch amortizeLoan procedure with LNS_FINANCIALS 115.112 version
4953: *=======================================================================*/
4954: procedure amortizeLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
4955: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
4956: ,p_based_on_terms in varchar2
4957: ,p_installment_number in number
4958: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
4951: || 10/28/2004 raverma added interest only flag
4952: || 06/20/2008 scherkas Synch amortizeLoan procedure with LNS_FINANCIALS 115.112 version
4953: *=======================================================================*/
4954: procedure amortizeLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
4955: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
4956: ,p_based_on_terms in varchar2
4957: ,p_installment_number in number
4958: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
4959: is
4954: procedure amortizeLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
4955: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
4956: ,p_based_on_terms in varchar2
4957: ,p_installment_number in number
4958: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
4959: is
4960: l_return_status varchar2(1);
4961: l_msg_count NUMBER;
4962: l_msg_data VARCHAR2(32767);
4979: l_raw_rate number; --
4980: l_intervals_remaining number;
4981: l_amortization_intervals_orig number;
4982: l_amortization_intervals number; -- number of intervals to amortize over
4983: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
4984: l_current_rate_id number;
4985: l_previous_rate_id number;
4986: l_precision number;
4987: l_rate_type varchar2(30);
4999: l_periodic_rate number;
5000: l_maturity_date date;
5001: l_amortized_maturity_date date;
5002:
5003: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
5004: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
5005: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
5006: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5007: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5000: l_maturity_date date;
5001: l_amortized_maturity_date date;
5002:
5003: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
5004: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
5005: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
5006: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5007: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5008: l_loan_start_date date;
5001: l_amortized_maturity_date date;
5002:
5003: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
5004: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
5005: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
5006: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5007: l_amortized_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
5008: l_loan_start_date date;
5009: l_num_pay_dates number; -- number of dates on installment schedule
5442: if l_rate_tbl.count = 1 then
5443: l_previous_annualized := l_rate_tbl(1).annual_rate;
5444: l_previous_interest_only_flag := l_rate_tbl(1).interest_only_flag;
5445: else
5446: l_rate_details := lns_financials.getRateDetails(p_installment => l_last_installment_billed
5447: ,p_rate_tbl => l_rate_tbl);
5448: l_previous_annualized := l_rate_details.annual_rate;
5449: l_previous_interest_only_flag := l_rate_details.interest_only_flag;
5450: end if;
5674: l_amortization_intervals := l_amortization_intervals_orig - l_installment_number + 1;
5675:
5676: if (l_calc_method = 'SIMPLE') then
5677:
5678: l_rate_to_calculate := lns_financials.getPeriodicRate(
5679: p_payment_freq => l_payment_frequency
5680: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
5681: ,p_period_end_date => l_maturity_date
5682: ,p_annualized_rate => l_annualized_rate
5701:
5702: l_remaining_balance := l_remaining_balance_theory + l_increased_amount1;
5703:
5704: if l_rate_details.rate_id = l_rate_tbl(1).rate_id then
5705: l_periodic_payment := lns_financials.calculatePayment(p_loan_amount => l_remaining_balance
5706: ,p_periodic_rate => l_rate_to_calculate
5707: ,p_num_intervals => l_amortization_intervals
5708: ,p_ending_balance=> l_balloon_amount
5709: ,p_pay_in_arrears=> l_pay_in_arrears);
5707: ,p_num_intervals => l_amortization_intervals
5708: ,p_ending_balance=> l_balloon_amount
5709: ,p_pay_in_arrears=> l_pay_in_arrears);
5710: else
5711: l_periodic_payment := lns_financials.calculatePayment(p_loan_amount => l_remaining_balance
5712: ,p_periodic_rate => l_rate_to_calculate
5713: ,p_num_intervals => l_amortization_intervals
5714: ,p_ending_balance=> l_balloon_amount
5715: ,p_pay_in_arrears=> true);
5739: -- now we will caculate the interest due for this period
5740: if (p_based_on_terms = 'CURRENT' and l_detail_int_calc_flag = true) then
5741:
5742: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
5743: LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => l_loan_id,
5744: p_calc_method => l_calc_method,
5745: p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date,
5746: p_period_end_date => l_payment_tbl(l_installment_number).period_end_date,
5747: p_interest_rate => l_annualized_rate,
5776: end if;
5777:
5778: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
5779: -- calculate additional interest on unpaid principal
5780: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
5781: p_calc_method => l_calc_method,
5782: p_period_start_date => l_add_start_date,
5783: p_period_end_date => l_add_end_date,
5784: p_interest_rate => l_annualized_rate,
5797: l_add_prin_interest := round(l_add_prin_interest, l_precision);
5798:
5799: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
5800: -- calculate additional interest on unpaid interest
5801: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => l_loan_id,
5802: p_calc_method => l_calc_method,
5803: p_period_start_date => l_add_start_date,
5804: p_period_end_date => l_add_end_date,
5805: p_interest_rate => l_annualized_rate,
5824: end if;
5825:
5826: -- calculate interest credit on early payment
5827: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating early payment credit amount...');
5828: LNS_FINANCIALS.CALC_EARLY_PAY_CR(p_loan_id => l_loan_id,
5829: p_calc_method => l_calc_method,
5830: p_installment => l_installment_number,
5831: p_interest_rate => l_previous_annualized,
5832: p_day_count_method => l_day_count_method,
5840: elsif (p_based_on_terms <> 'CURRENT' and l_detail_int_calc_flag = true and l_bill_on_appr_amounts = 'N') then
5841:
5842: if (l_calc_method = 'SIMPLE') then
5843:
5844: l_periodic_rate := lns_financials.getPeriodicRate(
5845: p_payment_freq => l_payment_frequency
5846: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
5847: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
5848: ,p_annualized_rate => l_annualized_rate
5860: ,p_target => 'INTEREST');
5861:
5862: end if;
5863:
5864: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
5865: ,p_from_date => l_payment_tbl(l_installment_number).period_begin_date
5866: ,p_to_date => l_payment_tbl(l_installment_number).period_end_date
5867: ,p_calc_method => 'TARGET'
5868: ,p_phase => 'TERM'
5872: ,x_begin_balance => l_balance1
5873: ,x_end_balance => l_balance2);
5874:
5875: l_wtd_balance := l_wtd_balance + l_prev_cap_int;
5876: l_norm_interest := lns_financials.calculateInterest(p_amount => l_wtd_balance
5877: ,p_periodic_rate => l_periodic_rate
5878: ,p_compounding_period => null);
5879: l_norm_interest := round(l_norm_interest, l_precision);
5880:
5890: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': interest is based upon an amount of ' || l_remaining_balance_theory1);
5891:
5892: if (l_calc_method = 'SIMPLE') then
5893:
5894: l_periodic_rate := lns_financials.getPeriodicRate(
5895: p_payment_freq => l_payment_frequency
5896: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
5897: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
5898: ,p_annualized_rate => l_annualized_rate
5910: ,p_target => 'INTEREST');
5911:
5912: end if;
5913:
5914: l_norm_interest := lns_financials.calculateInterest(p_amount => l_remaining_balance_theory1
5915: ,p_periodic_rate => l_periodic_rate
5916: ,p_compounding_period => null);
5917:
5918: l_norm_interest := round(l_norm_interest, l_precision);
6277: *=======================================================================*/
6278: procedure amortizeLoan (p_loan_Id in number
6279: ,p_based_on_terms in varchar2
6280: ,p_installment_number in number
6281: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
6282:
6283: is
6284: l_loan_details LOAN_DETAILS_REC;
6285: l_amortization_tbl amortization_tbl;
6288: begin
6289:
6290: l_api_name := 'amortizeLoan';
6291: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
6292: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
6293: ,p_based_on_terms => p_based_on_terms
6294: ,p_phase => 'TERM');
6295:
6296: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
6292: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
6293: ,p_based_on_terms => p_based_on_terms
6294: ,p_phase => 'TERM');
6295:
6296: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
6297:
6298: if (l_loan_details.PAYMENT_CALC_METHOD = 'EQUAL_PRINCIPAL') then
6299:
6300: lns_financials.amortizeEPLoan(p_loan_details => l_loan_details
6296: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
6297:
6298: if (l_loan_details.PAYMENT_CALC_METHOD = 'EQUAL_PRINCIPAL') then
6299:
6300: lns_financials.amortizeEPLoan(p_loan_details => l_loan_details
6301: ,p_rate_schedule => l_rate_tbl
6302: ,p_based_on_terms => p_based_on_terms
6303: ,p_installment_number => p_installment_number
6304: ,x_loan_amort_tbl => l_amortization_tbl);
6304: ,x_loan_amort_tbl => l_amortization_tbl);
6305:
6306: elsif (l_loan_details.PAYMENT_CALC_METHOD = 'SEPARATE_SCHEDULES') then
6307:
6308: lns_financials.amortizeSIPLoan(p_loan_details => l_loan_details
6309: ,p_rate_schedule => l_rate_tbl
6310: ,p_based_on_terms => p_based_on_terms
6311: ,p_installment_number => p_installment_number
6312: ,x_loan_amort_tbl => l_amortization_tbl);
6312: ,x_loan_amort_tbl => l_amortization_tbl);
6313:
6314: elsif (l_loan_details.PAYMENT_CALC_METHOD = 'EQUAL_PAYMENT') then
6315:
6316: lns_financials.amortizeLoan(p_loan_details => l_loan_details
6317: ,p_rate_schedule => l_rate_tbl
6318: ,p_based_on_terms => p_based_on_terms
6319: ,p_installment_number => p_installment_number
6320: ,x_loan_amort_tbl => l_amortization_tbl);
6356: || MODIFICATION HISTORY
6357: || Date Author Description of Changes
6358: || 07/20/2005 11:35AM raverma Created
6359: *=======================================================================*/
6360: procedure loanProjection(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
6361: ,p_based_on_terms in varchar2
6362: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
6363: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
6364: is
6358: || 07/20/2005 11:35AM raverma Created
6359: *=======================================================================*/
6360: procedure loanProjection(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
6361: ,p_based_on_terms in varchar2
6362: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
6363: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
6364: is
6365: l_return_status varchar2(1);
6366: l_msg_count NUMBER;
6359: *=======================================================================*/
6360: procedure loanProjection(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
6361: ,p_based_on_terms in varchar2
6362: ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
6363: ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
6364: is
6365: l_return_status varchar2(1);
6366: l_msg_count NUMBER;
6367: l_msg_data VARCHAR2(32767);
6374: l_day_count_method varchar2(30);
6375: l_intervals_original number;
6376: l_intervals number;
6377: l_intervals_remaining number;
6378: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
6379: l_precision number;
6380:
6381: l_period_start_Date date;
6382: l_period_end_date date;
6382: l_period_end_date date;
6383: l_periodic_rate number;
6384: l_maturity_date date;
6385:
6386: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
6387: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6388: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
6389: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
6390: l_loan_start_date date;
6383: l_periodic_rate number;
6384: l_maturity_date date;
6385:
6386: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
6387: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6388: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
6389: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
6390: l_loan_start_date date;
6391: l_num_pay_dates number; -- number of dates on installment schedule
6384: l_maturity_date date;
6385:
6386: l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
6387: l_amortization_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6388: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
6389: l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
6390: l_loan_start_date date;
6391: l_num_pay_dates number; -- number of dates on installment schedule
6392: l_periodic_payment number;
6515:
6516: begin
6517: if p_based_on_terms = 'CURRENT' then
6518:
6519: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
6520: ,p_from_date => l_loan_start_date
6521: ,p_to_date => l_loan_start_date
6522: ,p_calc_method => 'ACTUAL'
6523: ,p_phase => 'OPEN'
6528: ,x_end_balance => l_end_balance);
6529:
6530: else
6531:
6532: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
6533: ,p_from_date => l_loan_start_date
6534: ,p_to_date => l_loan_start_date
6535: ,p_calc_method => 'TARGET'
6536: ,p_phase => 'OPEN'
6652: -- get the weighted balance for the period
6653: begin
6654: if p_based_on_terms = 'CURRENT' then
6655:
6656: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
6657: ,p_from_date => l_payment_tbl(l_installment_number).period_begin_date
6658: ,p_to_date => l_payment_tbl(l_installment_number).period_end_date
6659: ,p_calc_method => 'ACTUAL'
6660: ,p_phase => 'OPEN'
6665: ,x_end_balance => l_end_balance);
6666:
6667: else
6668:
6669: lns_financials.getWeightedBalance(p_loan_id => l_loan_id
6670: ,p_from_date => l_payment_tbl(l_installment_number).period_begin_date
6671: ,p_to_date => l_payment_tbl(l_installment_number).period_end_date
6672: ,p_calc_method => 'TARGET'
6673: ,p_phase => 'OPEN'
6702:
6703: -- recalculate periodic rate for each period if day counting methodolgy varies
6704: if (l_calc_method = 'SIMPLE') then
6705:
6706: l_periodic_rate := lns_financials.getPeriodicRate(
6707: p_payment_freq => l_payment_frequency
6708: ,p_period_start_date => l_payment_tbl(l_installment_number).period_begin_date
6709: ,p_period_end_date => l_payment_tbl(l_installment_number).period_end_date
6710: ,p_annualized_rate => l_annualized_rate
6726:
6727: -- if we are going to compound, then based on compounding the amount should be passed to calculateInterest call here.
6728: -- how do we determine what the amount to compound on
6729: -- for example: compound daily at .5% over 30 day period
6730: l_periodic_interest := lns_financials.calculateInterest(p_amount => l_wtd_balance
6731: ,p_periodic_rate => l_periodic_rate
6732: ,p_compounding_period => null);
6733:
6734: l_periodic_interest := round(l_periodic_interest, l_precision);
6912: *=======================================================================*/
6913: procedure runOpenProjection(p_init_msg_list IN VARCHAR2
6914: ,p_loan_ID IN NUMBER
6915: ,p_based_on_terms IN VARCHAR2
6916: ,x_amort_tbl OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_TBL
6917: ,x_return_status OUT NOCOPY VARCHAR2
6918: ,x_msg_count OUT NOCOPY NUMBER
6919: ,x_msg_data OUT NOCOPY VARCHAR2)
6920: is
6924: l_return_status VARCHAR2(1);
6925: l_msg_count NUMBER;
6926: l_msg_data VARCHAR2(32767);
6927:
6928: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6929: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
6930: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
6931: b_showActual boolean := false;
6932: l_last_installment_billed number;
6925: l_msg_count NUMBER;
6926: l_msg_data VARCHAR2(32767);
6927:
6928: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6929: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
6930: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
6931: b_showActual boolean := false;
6932: l_last_installment_billed number;
6933:
6926: l_msg_data VARCHAR2(32767);
6927:
6928: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
6929: l_amort_tbl2 LNS_FINANCIALS.AMORTIZATION_TBL;
6930: l_total_amortization LNS_FINANCIALS.AMORTIZATION_REC;
6931: b_showActual boolean := false;
6932: l_last_installment_billed number;
6933:
6934: l_num_records number;
6940: l_manual_fee_amount number;
6941: l_records_to_destroy number;
6942: l_start_date number;
6943: l_funded_amount number;
6944: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
6945: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
6946: l_disb_amount number;
6947: l_invalid_disb number;
6948:
6941: l_records_to_destroy number;
6942: l_start_date number;
6943: l_funded_amount number;
6944: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
6945: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
6946: l_disb_amount number;
6947: l_invalid_disb number;
6948:
6949: cursor c_disbursements(p_loan_id number) is
7000: RAISE FND_API.G_EXC_ERROR;
7001: end if;
7002:
7003: if l_disb_amount > 0 then
7004: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'OPEN');
7005: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
7006: ,p_based_on_terms => p_based_on_terms
7007: ,p_phase => 'OPEN');
7008:
7001: end if;
7002:
7003: if l_disb_amount > 0 then
7004: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'OPEN');
7005: l_loan_details := lns_financials.getLoanDetails(p_loan_Id => p_loan_id
7006: ,p_based_on_terms => p_based_on_terms
7007: ,p_phase => 'OPEN');
7008:
7009: -- call projection API
7006: ,p_based_on_terms => p_based_on_terms
7007: ,p_phase => 'OPEN');
7008:
7009: -- call projection API
7010: lns_financials.loanProjection(p_loan_details => l_loan_details
7011: ,p_based_on_terms => p_based_on_terms
7012: ,p_rate_schedule => l_rate_tbl
7013: ,x_loan_amort_tbl => l_amort_tbl);
7014:
7113: || Date Author Description of Changes
7114: || 07/18/2005 6:42PM raverma Created
7115: *=======================================================================*/
7116: function getRateSchedule(p_loan_id in number
7117: ,p_phase in varchar2) return LNS_FINANCIALS.RATE_SCHEDULE_TBL
7118: is
7119:
7120: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
7121: l_rate_id number;
7116: function getRateSchedule(p_loan_id in number
7117: ,p_phase in varchar2) return LNS_FINANCIALS.RATE_SCHEDULE_TBL
7118: is
7119:
7120: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
7121: l_rate_id number;
7122: l_annual_rate number;
7123: l_spread number;
7124: l_start_date date;
7293: || Date Author Description of Changes
7294: || 2/09/2003 1:51PM raverma Created
7295: ||
7296: *=======================================================================*/
7297: function getWeightedRate(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
7298: ,p_start_date in date
7299: ,p_end_date in date
7300: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return number
7301: is
7296: *=======================================================================*/
7297: function getWeightedRate(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
7298: ,p_start_date in date
7299: ,p_end_date in date
7300: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return number
7301: is
7302: l_api_name varchar2(25);
7303: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
7304: l_pay_dates LNS_FINANCIALS.DATE_TBL;
7299: ,p_end_date in date
7300: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return number
7301: is
7302: l_api_name varchar2(25);
7303: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
7304: l_pay_dates LNS_FINANCIALS.DATE_TBL;
7305: l_days_at_rate number;
7306: l_weighted_rate number;
7307: l_running_weight number;
7300: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return number
7301: is
7302: l_api_name varchar2(25);
7303: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
7304: l_pay_dates LNS_FINANCIALS.DATE_TBL;
7305: l_days_at_rate number;
7306: l_weighted_rate number;
7307: l_running_weight number;
7308: l_total_days number;
7496: || Date Author Description of Changes
7497: || 2/24/2003 4:28PM raverma Created
7498: *=======================================================================*/
7499: function getRateDetails(p_installment IN NUMBER
7500: ,p_rate_tbl IN LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC
7501: is
7502: x number;
7503: l_rate number;
7504: l_rate_rec LNS_FINANCIALS.INTEREST_RATE_REC;
7500: ,p_rate_tbl IN LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC
7501: is
7502: x number;
7503: l_rate number;
7504: l_rate_rec LNS_FINANCIALS.INTEREST_RATE_REC;
7505: l_api_name varchar2(25);
7506:
7507: begin
7508:
7571: || 2/24/2003 4:28PM raverma Created
7572: ||
7573: *=======================================================================*/
7574: function getRateDetails(p_date in date
7575: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC
7576: is
7577: x number;
7578: l_rate number;
7579: l_rate_rec LNS_FINANCIALS.INTEREST_RATE_REC;
7575: ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC
7576: is
7577: x number;
7578: l_rate number;
7579: l_rate_rec LNS_FINANCIALS.INTEREST_RATE_REC;
7580: l_api_name varchar2(25);
7581:
7582: begin
7583:
7644: || 01/19/2007 scherkas Fixed bug 5842639
7645: *=======================================================================*/
7646: function getLoanDetails(p_loan_id in number
7647: ,p_based_on_terms in varchar2
7648: ,p_phase in varchar2) return LNS_FINANCIALS.LOAN_DETAILS_REC
7649:
7650: is
7651:
7652:
7663: ,decode(p_phase, 'TERM', trunc(h.loan_start_date), 'OPEN' , trunc(h.open_loan_start_date), trunc(h.loan_start_date)) START_DATE
7664: ,decode(p_phase, 'TERM', trunc(t.first_payment_date), 'OPEN' , trunc(t.open_first_payment_date), trunc(t.first_payment_date)) FIRST_PAY_DATE
7665: ,h.requested_amount REQUEST_AMOUNT
7666: ,h.funded_amount FUNDED_AMOUNT
7667: ,lns_financials.getRemainingBalance(p_loan_id) BALANCE
7668: --,decode(p_based_on_terms, 'CURRENT', lns_financials.getRemainingBalance(p_loan_id), 'ORIGINAL', h.requested_amount) BALANCE -- see bug #3881401
7669: ,decode(p_phase, 'TERM', trunc(h.loan_maturity_date), 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date)) MATURITY_DATE
7670: ,NVL(t.reamortize_over_payment, 'N')
7671: ,NVL(t.reamortize_under_payment, 'N')
7664: ,decode(p_phase, 'TERM', trunc(t.first_payment_date), 'OPEN' , trunc(t.open_first_payment_date), trunc(t.first_payment_date)) FIRST_PAY_DATE
7665: ,h.requested_amount REQUEST_AMOUNT
7666: ,h.funded_amount FUNDED_AMOUNT
7667: ,lns_financials.getRemainingBalance(p_loan_id) BALANCE
7668: --,decode(p_based_on_terms, 'CURRENT', lns_financials.getRemainingBalance(p_loan_id), 'ORIGINAL', h.requested_amount) BALANCE -- see bug #3881401
7669: ,decode(p_phase, 'TERM', trunc(h.loan_maturity_date), 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date)) MATURITY_DATE
7670: ,NVL(t.reamortize_over_payment, 'N')
7671: ,NVL(t.reamortize_under_payment, 'N')
7672: ,NVL(t.reamortize_with_interest, 'N')
7818:
7819: l_billed_principal number;
7820: l_amortized_to_Date date;
7821: --l_pay_in_arrears boolean;
7822: l_loan_Details LNS_FINANCIALS.LOAN_DETAILS_REC;
7823: l_amortize_dates LNS_FIN_UTILS.DATE_TBL;
7824: l_loan_id number;
7825: l_api_name varchar2(25);
7826:
8407: *====================================================================================================*/
8408: procedure shiftLoanDates(p_loan_id in number
8409: ,p_new_start_date in date
8410: ,p_phase in varchar2
8411: ,x_loan_details out NOCOPY lns_financials.loan_details_rec
8412: ,x_dates_shifted_flag OUT NOCOPY VARCHAR2
8413: ,x_return_status OUT NOCOPY VARCHAR2
8414: ,x_msg_count OUT NOCOPY NUMBER
8415: ,x_msg_data OUT NOCOPY VARCHAR2)
8417: l_return_status VARCHAR2(1);
8418: l_msg_count NUMBER;
8419: l_msg_data VARCHAR2(32767);
8420: l_api_name varchar2(25);
8421: l_loan_details lns_financials.loan_Details_rec;
8422: l_day_difference number;
8423: l_new_maturity_date date;
8424: l_new_first_payment_date date;
8425: x number;
8463: FND_MSG_PUB.ADD;
8464: RAISE FND_API.G_EXC_ERROR;
8465: end if;
8466:
8467: l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
8468: ,p_based_on_terms => 'ORIGINAL'
8469: ,p_phase => p_phase);
8470:
8471: if l_loan_Details.loan_start_date = p_new_start_date then
8860:
8861:
8862:
8863:
8864: procedure validatePayoff(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
8865: ,p_payoff_date in date
8866: ,p_reason in VARCHAR2
8867: ,x_return_status OUT NOCOPY VARCHAR2
8868: ,x_msg_count OUT NOCOPY NUMBER
8994: || MODIFICATION HISTORY
8995: || Date Author Description of Changes
8996: || 10/26/2004 12:55PM raverma Created
8997: *=======================================================================*/
8998: procedure validatePayoff(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
8999: ,p_payoff_date in date
9000: ,x_return_status OUT NOCOPY VARCHAR2
9001: ,x_msg_count OUT NOCOPY NUMBER
9002: ,x_msg_data OUT NOCOPY VARCHAR2)
9003: is
9004:
9005: begin
9006:
9007: lns_financials.validatePayoff(p_loan_details => p_loan_details
9008: ,p_payoff_date => p_payoff_date
9009: ,p_reason => 'PAYOFF'
9010: ,x_return_status => x_return_status
9011: ,x_msg_count => x_msg_count
9020: ,p_init_msg_list IN VARCHAR2
9021: ,p_loan_id in number
9022: ,p_date in date
9023: ,p_reason in varchar2
9024: ,x_payoff_tbl OUT NOCOPY LNS_FINANCIALS.PAYOFF_TBL2
9025: ,x_return_status OUT NOCOPY VARCHAR2
9026: ,x_msg_count OUT NOCOPY NUMBER
9027: ,x_msg_data OUT NOCOPY VARCHAR2)
9028: IS
9032: l_return_status VARCHAR2(1);
9033: l_msg_count NUMBER;
9034: l_msg_data VARCHAR2(32767);
9035:
9036: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
9037: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
9038: l_annualized_rate number;
9039: l_periodic_rate number;
9040: l_additional_interest number;
9033: l_msg_count NUMBER;
9034: l_msg_data VARCHAR2(32767);
9035:
9036: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
9037: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
9038: l_annualized_rate number;
9039: l_periodic_rate number;
9040: l_additional_interest number;
9041: l_additional_fees number;
9038: l_annualized_rate number;
9039: l_periodic_rate number;
9040: l_additional_interest number;
9041: l_additional_fees number;
9042: l_payoff_tbl LNS_FINANCIALS.PAYOFF_TBL2;
9043: l_principal_unpaid number;
9044: l_interest_unpaid number;
9045: l_fees_unpaid number;
9046: l_payoff_date date;
9074:
9075: l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
9076: l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
9077: l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
9078: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
9079:
9080: cursor c_additional_fees(p_loan_id number, p_phase varchar2, p_installment number) is
9081: select nvl(sum(sched.fee_amount), 0)
9082: from lns_fee_schedules sched
9153: fetch c_loan_info into l_current_phase;
9154: close c_loan_info;
9155: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - current phase ' || l_current_phase);
9156:
9157: l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
9158: ,p_based_on_terms => 'CURRENT'
9159: ,p_phase => l_current_phase);
9160: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, l_current_phase);
9161: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - got loan details and rate info');
9156:
9157: l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
9158: ,p_based_on_terms => 'CURRENT'
9159: ,p_phase => l_current_phase);
9160: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, l_current_phase);
9161: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - got loan details and rate info');
9162:
9163: lns_financials.validatePayoff(p_loan_details => l_loan_details
9164: ,p_payoff_date => p_date
9159: ,p_phase => l_current_phase);
9160: l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, l_current_phase);
9161: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - got loan details and rate info');
9162:
9163: lns_financials.validatePayoff(p_loan_details => l_loan_details
9164: ,p_payoff_date => p_date
9165: ,p_reason => p_reason
9166: ,x_return_status => l_return_status
9167: ,x_msg_count => l_msg_count
9228: l_from_date := p_date;
9229: l_to_date := l_loan_details.last_interest_accrual;
9230: l_multipler := -1;
9231: /*
9232: l_balance := lns_financials.getAverageDailyBalance(p_loan_id => p_loan_id
9233: ,p_term_id => null
9234: ,p_from_date => p_date
9235: ,p_to_date => l_loan_details.last_interest_accrual
9236: ,p_calc_method => null);
9274: -- get the wtd rate if necessary
9275: if l_rate_tbl.count = 1 then
9276: l_annualized_rate := l_rate_tbl(1).annual_rate;
9277: else
9278: l_annualized_rate := lns_financials.getWeightedRate(p_loan_details => l_loan_details
9279: ,p_start_date => l_from_date
9280: ,p_end_date => l_to_date
9281: ,p_rate_tbl => l_rate_tbl);
9282: end if;
9320: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_prev_cap_int = ' || l_prev_cap_int);
9321:
9322: -- calculate normal interest
9323: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
9324: LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => p_loan_id,
9325: p_calc_method => l_loan_details.CALCULATION_METHOD,
9326: p_period_start_date => l_from_date,
9327: p_period_end_date => l_to_date,
9328: p_interest_rate => l_annualized_rate,
9337: l_norm_interest := round(l_norm_interest, l_loan_details.currency_precision);
9338:
9339: -- calculate additional interest on unpaid principal
9340: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
9341: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
9342: p_calc_method => l_loan_details.CALCULATION_METHOD,
9343: p_period_start_date => l_add_start_date,
9344: p_period_end_date => l_add_end_date,
9345: p_interest_rate => l_annualized_rate,
9358: l_add_prin_interest := round(l_add_prin_interest, l_loan_details.currency_precision);
9359:
9360: -- calculate additional interest on unpaid interest
9361: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
9362: LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
9363: p_calc_method => l_loan_details.CALCULATION_METHOD,
9364: p_period_start_date => l_add_start_date,
9365: p_period_end_date => l_add_end_date,
9366: p_interest_rate => l_annualized_rate,
9383: l_previous_annualized := l_rate_details.annual_rate;
9384:
9385: -- calculate interest credit on early payment
9386: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating early payment credit amount...');
9387: LNS_FINANCIALS.CALC_EARLY_PAY_CR(p_loan_id => p_loan_id,
9388: p_calc_method => l_loan_details.CALCULATION_METHOD,
9389: p_installment => (l_loan_details.last_installment_billed + 1),
9390: p_interest_rate => l_previous_annualized,
9391: p_day_count_method => l_loan_details.day_count_method,
9502: procedure calculatePayoff(p_api_version IN NUMBER
9503: ,p_init_msg_list IN VARCHAR2
9504: ,p_loan_id in number
9505: ,p_payoff_date in date
9506: ,x_payoff_tbl OUT NOCOPY LNS_FINANCIALS.PAYOFF_TBL2
9507: ,x_return_status OUT NOCOPY VARCHAR2
9508: ,x_msg_count OUT NOCOPY NUMBER
9509: ,x_msg_data OUT NOCOPY VARCHAR2)
9510: IS
9507: ,x_return_status OUT NOCOPY VARCHAR2
9508: ,x_msg_count OUT NOCOPY NUMBER
9509: ,x_msg_data OUT NOCOPY VARCHAR2)
9510: IS
9511: l_payoff_tbl LNS_FINANCIALS.PAYOFF_TBL2;
9512: begin
9513:
9514: calcLoanRemainingAmounts(p_api_version => 1.0
9515: ,p_init_msg_list => p_init_msg_list
10009: ||
10010: *=======================================================================*/
10011: function getActiveRate(p_loan_id in number) return number
10012: is
10013: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
10014: l_last_installment number;
10015: l_active_rate number;
10016: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
10017:
10012: is
10013: l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
10014: l_last_installment number;
10015: l_active_rate number;
10016: l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
10017:
10018:
10019: begin
10020:
10394: | MODIFICATION HISTORY
10395: | Date Author Description of Changes
10396: | 07/19/05 4:13:PM raverma Created
10397: *=======================================================================*/
10398: function weightBalance(p_loan_activities IN LNS_FINANCIALS.LOAN_ACTIVITY_TBL
10399: ,p_from_date in date
10400: ,p_to_date in date
10401: ,p_day_count_method in varchar2) return number
10402: is
10409: l_api_name varchar2(25);
10410: l_begin_balance number;
10411: l_end_balance number;
10412: l_total_activity_amount number;
10413: l_loan_activities LNS_FINANCIALS.LOAN_ACTIVITY_TBL;
10414:
10415:
10416: begin
10417: l_api_name := 'weightBalance';
10546: ,x_end_balance out NOCOPY number)
10547:
10548: is
10549: l_api_name varchar2(25);
10550: l_loan_activities LNS_FINANCIALS.LOAN_ACTIVITY_TBL;
10551: i number;
10552: l_activity_date date;
10553: l_activity_amount number;
10554: l_loan_start_date date;
10803: group by activity_date
10804: union all
10805: select trunc(loan_maturity_date) activity_date
10806: ,null
10807: ,lns_financials.getRemainingBalance(p_loan_id)
10808: from lns_loan_headers
10809: where loan_id = p_loan_id
10810: order by activity_date asc;
10811:
10819: l_activity_amount number;
10820: l_balance_days number;
10821: l_num_days number;
10822: l_total_days number;
10823: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
10824: l_loan_activities LNS_FINANCIALS.LOAN_ACTIVITY_TBL;
10825: l_average_daily_balance number;
10826: k number;
10827: m number;
10820: l_balance_days number;
10821: l_num_days number;
10822: l_total_days number;
10823: l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
10824: l_loan_activities LNS_FINANCIALS.LOAN_ACTIVITY_TBL;
10825: l_average_daily_balance number;
10826: k number;
10827: m number;
10828: l_num_balance_changes number;
10856: OPEN c_loan_phase(p_loan_id);
10857: FETCH c_loan_phase INTO l_loan_phase;
10858: CLOSE c_loan_phase;
10859:
10860: l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
10861: ,p_based_on_terms => 'CURRENT'
10862: --karamach bug5237022
10863: --,p_phase => 'TERM');
10864: ,p_phase => l_loan_phase);
11137: if (p_calc_method = 'SIMPLE') then
11138:
11139: -- recalculate periodic rate for each period if day counting methodolgy varies
11140:
11141: l_periodic_rate := lns_financials.getPeriodicRate(
11142: p_payment_freq => p_payment_freq
11143: ,p_period_start_date => l_norm_prev_act_date
11144: ,p_period_end_date => l_activity_date
11145: ,p_annualized_rate => p_interest_rate
11158:
11159: end if;
11160: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate = ' || l_periodic_rate);
11161:
11162: l_norm_interest := lns_financials.calculateInterest(p_amount => l_norm_prev_amount
11163: ,p_periodic_rate => l_periodic_rate
11164: ,p_compounding_period => null);
11165: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'normal interest = ' || l_norm_interest);
11166:
11207: if (p_calc_method = 'SIMPLE') then
11208:
11209: -- recalculate periodic rate for each period if day counting methodolgy varies
11210:
11211: l_periodic_rate := lns_financials.getPeriodicRate(
11212: p_payment_freq => p_payment_freq
11213: ,p_period_start_date => l_norm_prev_act_date
11214: ,p_period_end_date => l_activity_date
11215: ,p_annualized_rate => p_interest_rate
11227:
11228: end if;
11229: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate = ' || l_periodic_rate);
11230:
11231: l_norm_interest := lns_financials.calculateInterest(p_amount => l_norm_prev_amount
11232: ,p_periodic_rate => l_periodic_rate
11233: ,p_compounding_period => null);
11234: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'normal interest = ' || l_norm_interest);
11235:
11466: if (p_calc_method = 'SIMPLE') then
11467:
11468: -- recalculate periodic rate for each period if day counting methodolgy varies
11469:
11470: l_periodic_rate := lns_financials.getPeriodicRate(
11471: p_payment_freq => p_payment_freq
11472: ,p_period_start_date => l_add_prev_act_date
11473: ,p_period_end_date => l_activity_date
11474: ,p_annualized_rate => p_interest_rate
11487:
11488: end if;
11489: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate = ' || l_periodic_rate);
11490:
11491: l_add_interest := lns_financials.calculateInterest(p_amount => l_add_prev_amount
11492: ,p_periodic_rate => l_periodic_rate
11493: ,p_compounding_period => null);
11494: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'additional interest = ' || l_add_interest);
11495:
11535:
11536: -- calc penal interest rate
11537: if (p_calc_method = 'SIMPLE') then
11538:
11539: l_penal_period_rate := lns_financials.getPeriodicRate(
11540: p_payment_freq => p_payment_freq
11541: ,p_period_start_date => l_add_prev_act_date
11542: ,p_period_end_date => l_activity_date
11543: ,p_annualized_rate => p_penal_int_rate
11557: end if;
11558:
11559: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'penal periodic_rate = ' || l_penal_period_rate);
11560:
11561: l_penal_interest := lns_financials.calculateInterest(p_amount => l_add_prev_amount
11562: ,p_periodic_rate => l_penal_period_rate
11563: ,p_compounding_period => null);
11564: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'penal interest = ' || l_penal_interest);
11565:
11628: if (p_calc_method = 'SIMPLE') then
11629:
11630: -- recalculate periodic rate for each period if day counting methodolgy varies
11631:
11632: l_periodic_rate := lns_financials.getPeriodicRate(
11633: p_payment_freq => p_payment_freq
11634: ,p_period_start_date => l_add_prev_act_date
11635: ,p_period_end_date => l_activity_date
11636: ,p_annualized_rate => p_interest_rate
11649:
11650: end if;
11651: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate = ' || l_periodic_rate);
11652:
11653: l_add_interest := lns_financials.calculateInterest(p_amount => l_add_prev_amount
11654: ,p_periodic_rate => l_periodic_rate
11655: ,p_compounding_period => null);
11656: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'additional interest = ' || l_add_interest);
11657:
11697:
11698: -- calc penal interest rate
11699: if (p_calc_method = 'SIMPLE') then
11700:
11701: l_penal_period_rate := lns_financials.getPeriodicRate(
11702: p_payment_freq => p_payment_freq
11703: ,p_period_start_date => l_add_prev_act_date
11704: ,p_period_end_date => l_activity_date
11705: ,p_annualized_rate => p_penal_int_rate
11719: end if;
11720:
11721: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'penal periodic_rate = ' || l_penal_period_rate);
11722:
11723: l_penal_interest := lns_financials.calculateInterest(p_amount => l_add_prev_amount
11724: ,p_periodic_rate => l_penal_period_rate
11725: ,p_compounding_period => null);
11726: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'penal interest = ' || l_penal_interest);
11727:
11791:
11792:
11793:
11794: -- this procedure loads original amortization schedule from agreement report
11795: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
11796: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
11797: is
11798: /*-----------------------------------------------------------------------+
11799: | Local Variable Declarations and initializations |
11792:
11793:
11794: -- this procedure loads original amortization schedule from agreement report
11795: procedure LOAD_ORIGINAL_SCHEDULE(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC,
11796: x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL)
11797: is
11798: /*-----------------------------------------------------------------------+
11799: | Local Variable Declarations and initializations |
11800: +-----------------------------------------------------------------------*/
11816: l_value varchar2(100);
11817: l_date_format varchar2(20);
11818: l_unknown_date_format varchar2(20);
11819:
11820: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
11821: l_rate_schedule LNS_FINANCIALS.RATE_SCHEDULE_TBL;
11822:
11823: /*-----------------------------------------------------------------------+
11824: | Cursor Declarations |
11817: l_date_format varchar2(20);
11818: l_unknown_date_format varchar2(20);
11819:
11820: l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
11821: l_rate_schedule LNS_FINANCIALS.RATE_SCHEDULE_TBL;
11822:
11823: /*-----------------------------------------------------------------------+
11824: | Cursor Declarations |
11825: +-----------------------------------------------------------------------*/
12292: if (p_calc_method = 'SIMPLE') then
12293:
12294: -- recalculate periodic rate for each period if day counting methodolgy varies
12295:
12296: l_periodic_rate := lns_financials.getPeriodicRate(
12297: p_payment_freq => p_payment_freq
12298: ,p_period_start_date => l_from_date
12299: ,p_period_end_date => l_to_date
12300: ,p_annualized_rate => p_interest_rate
12313:
12314: end if;
12315: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate = ' || l_periodic_rate);
12316:
12317: l_cr_amount := lns_financials.calculateInterest(p_amount => l_amount
12318: ,p_periodic_rate => l_periodic_rate
12319: ,p_compounding_period => null);
12320: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calculated credit amount = ' || l_cr_amount);
12321:
12350: end;
12351:
12352:
12353:
12354: END LNS_FINANCIALS;