[Home] [Help]
1: PACKAGE BODY LNS_DISTRIBUTIONS_PUB AS
2: /* $Header: LNS_DIST_PUBP_B.pls 120.75.12020000.6 2013/03/07 16:32:04 scherkas ship $ */
3:
4: /*========================================================================+
5: | Package Global Constants
6: +=======================================================================*/
7: G_DEBUG_COUNT NUMBER := 0;
8: G_DEBUG BOOLEAN := FALSE;
9:
10: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_DISTRIBUTIONS_PUB';
11: g_cr_return_status VARCHAR2(10);
12: g_last_accrual_report CLOB;
13: g_last_all_statements CLOB;
14: g_cp_statements CLOB;
103: -- Initialize API return status to SUCCESS
104: x_return_status := FND_API.G_RET_STS_SUCCESS;
105:
106: -- first complete accounting for any unprocessed events / documents for the loan transaction
107: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.onlineAccounting...');
108: lns_distributions_pub.onlineAccounting(p_loan_id => p_loan_id
109: ,p_init_msg_list => fnd_api.g_false
110: ,p_accounting_mode => 'F'
111: ,p_transfer_flag => 'Y'
104: x_return_status := FND_API.G_RET_STS_SUCCESS;
105:
106: -- first complete accounting for any unprocessed events / documents for the loan transaction
107: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.onlineAccounting...');
108: lns_distributions_pub.onlineAccounting(p_loan_id => p_loan_id
109: ,p_init_msg_list => fnd_api.g_false
110: ,p_accounting_mode => 'F'
111: ,p_transfer_flag => 'Y'
112: ,p_offline_flag => 'N'
128: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_disbusement_id ' || l_disbursement_id);
129:
130: if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' OR LNS_UTILITY_PUB.IS_ENCUM_FIN_ENABLED = 'Y') then
131:
132: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoking LNS_DISTRIBUTIONS_PUB.generateCancelDistributions...');
133: LNS_DISTRIBUTIONS_PUB.generateCancelDistributions(p_api_version => 1
134: ,p_init_msg_list => 'F'
135: ,p_commit => 'F'
136: ,p_loan_id => p_loan_id
129:
130: if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' OR LNS_UTILITY_PUB.IS_ENCUM_FIN_ENABLED = 'Y') then
131:
132: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoking LNS_DISTRIBUTIONS_PUB.generateCancelDistributions...');
133: LNS_DISTRIBUTIONS_PUB.generateCancelDistributions(p_api_version => 1
134: ,p_init_msg_list => 'F'
135: ,p_commit => 'F'
136: ,p_loan_id => p_loan_id
137: ,x_return_status => l_return_status
177: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178: END IF;
179:
180: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping new event_id on distributions');
181: update lns_distributions
182: set event_id = l_event_id
183: ,last_update_date = sysdate
184: ,object_Version_number = object_version_number + 1
185: where distribution_type = 'ORIGINATION'
632: logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
633: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634: END IF;
635:
636: -- stamp the eventID onto the lns_distributions table
637: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
638:
639: update lns_distributions
640: set event_id = x_event_id
633: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634: END IF;
635:
636: -- stamp the eventID onto the lns_distributions table
637: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
638:
639: update lns_distributions
640: set event_id = x_event_id
641: ,last_update_date = sysdate
635:
636: -- stamp the eventID onto the lns_distributions table
637: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
638:
639: update lns_distributions
640: set event_id = x_event_id
641: ,last_update_date = sysdate
642: where distribution_type = 'ORIGINATION'
643: and loan_id = p_loan_id
660: IF l_event_id IS NOT NULL THEN
661:
662: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
663:
664: -- Bug#9328437, First time, when we do fundsCheck, the event_id creates and updates in lns_distributions table.
665: -- However if we do fundsCheck/fundsReserver later, existed distribtuions are deleted and again
666: -- defaulted, which has event_id as NULL. So, update the event_id if it is already created whose event_id is NULL
667: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions whose eventID is NULL');
668: update lns_distributions
663:
664: -- Bug#9328437, First time, when we do fundsCheck, the event_id creates and updates in lns_distributions table.
665: -- However if we do fundsCheck/fundsReserver later, existed distribtuions are deleted and again
666: -- defaulted, which has event_id as NULL. So, update the event_id if it is already created whose event_id is NULL
667: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions whose eventID is NULL');
668: update lns_distributions
669: set event_id = l_event_id
670: ,last_update_date = sysdate
671: where distribution_type = 'ORIGINATION'
664: -- Bug#9328437, First time, when we do fundsCheck, the event_id creates and updates in lns_distributions table.
665: -- However if we do fundsCheck/fundsReserver later, existed distribtuions are deleted and again
666: -- defaulted, which has event_id as NULL. So, update the event_id if it is already created whose event_id is NULL
667: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions whose eventID is NULL');
668: update lns_distributions
669: set event_id = l_event_id
670: ,last_update_date = sysdate
671: where distribution_type = 'ORIGINATION'
672: and event_id IS NULL
820: /*=========================================================================
821: || PRIVATE PROCEDURE do_insert_distributions
822: ||
823: || DESCRIPTION
824: || this procedure insert records into lns_distributions table
825: ||
826: || PARAMETERS p_distributions_tbl => table -f distribution records
827: ||
828: || Return value: NA
828: || Return value: NA
829: ||
830: || Source Tables:
831: ||
832: || Target Tables: LNS_DISTRIBUTIONS
833: ||
834: || KNOWN ISSUES
835: ||
836: || NOTES
838: || MODIFICATION HISTORY
839: || Date Author Description of Changes
840: || 04-20-2005 raverma Created
841: *=======================================================================*/
842: procedure do_insert_distributions(p_distributions_tbl in lns_distributions_pub.distribution_tbl
843: ,p_loan_id in number)
844:
845: is
846: l_total_distributions number;
878: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'EXPENDITURE_DATE = ' || p_distributions_tbl(k).EXPENDITURE_ITEM_DATE);
879:
880:
881:
882: Insert into lns_distributions
883: (DISTRIBUTION_ID
884: ,LOAN_ID
885: ,LINE_TYPE
886: ,ACCOUNT_NAME
907: ,TASK_ID
908: ,EXPENDITURE_TYPE
909: ,EXPENDITURE_ITEM_DATE)
910: values
911: (LNS_DISTRIBUTIONS_S.nextval
912: ,p_loan_id
913: ,p_distributions_tbl(k).line_type
914: ,p_distributions_tbl(k).account_name
915: ,p_distributions_tbl(k).code_combination_id
990: ,p_disb_header_id IN NUMBER
991: ,p_loan_amount_adj_id IN NUMBER DEFAULT NULL
992: ,p_include_loan_receivables IN VARCHAR2
993: ,p_distribution_type IN VARCHAR2
994: ,x_distribution_tbl OUT NOCOPY lns_distributions_pub.distribution_tbl
995: ,x_return_status OUT NOCOPY VARCHAR2
996: ,x_msg_count OUT NOCOPY NUMBER
997: ,x_msg_data OUT NOCOPY VARCHAR2)
998:
1016: l_funded_amount number;
1017: l_adj_reversal varchar2(1);
1018: l_loan_receivables_count number;
1019: l_loan_payables_count number;
1020: l_distributions lns_distributions_pub.distribution_tbl;
1021: l_running_amount1 number;
1022: l_running_amount2 number;
1023: l_running_amount3 number;
1024: l_running_amount4 number;
1025: k number;
1026: n number;
1027: l number;
1028: m number;
1029: l_ledger_details lns_distributions_pub.gl_ledger_details;
1030: Type refCur is ref cursor;
1031: sql_Cur refCur;
1032: vSqlCur varchar2(1000);
1033: vPLSQL VARCHAR2(1000);
1127: l_running_amount1 := 0;
1128: l_running_amount2 := 0;
1129: l_running_amount3 := 0;
1130: l_running_amount4 := 0;
1131: l_ledger_details := lns_distributions_pub.getLedgerDetails;
1132:
1133: -- get class and type for the loan
1134: l_adj_reversal := 'N';
1135: if p_loan_amount_adj_id is not null then
1376: /*=========================================================================
1377: || PUBLIC PROCEDURE create_event
1378: ||
1379: || DESCRIPTION
1380: || Overview: will write to xla_events table and update lns_distributions
1381: || this can handle a set of accounting event records
1382: ||
1383: || PSEUDO CODE/LOGIC
1384: ||
1403: || Date Author Description of Changes
1404: || 8/3/2005 raverma Created
1405: ||
1406: *=======================================================================*/
1407: procedure create_event(p_acc_event_tbl in LNS_DISTRIBUTIONS_PUB.acc_event_tbl
1408: ,p_init_msg_list in varchar2
1409: ,p_commit in varchar2
1410: ,x_return_status out nocopy varchar2
1411: ,x_msg_count out nocopy number
1414: is
1415: l_api_name varchar2(25);
1416: l_loan_class varchar2(30);
1417: l_loan_type_id number;
1418: l_distributions LNS_DISTRIBUTIONS_PUB.distribution_tbl;
1419: l_msg_count NUMBER;
1420: l_msg_data VARCHAR2(2000);
1421: l_return_Status VARCHAR2(1);
1422: l_event_id number;
1484: logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1485: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1486: ELSE
1487: -- update the distributions table with proper event_id for valid disb_header_id/loan_amount_adj_id
1488: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
1489:
1490: l_event_type_code := p_acc_event_tbl(k).event_type_code;
1491:
1492: IF (l_event_type_code = 'DIRECT_LOAN_APPROVED' OR l_event_type_code = 'APPROVED') THEN
1502: END IF;
1503:
1504: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_activity is '||l_activity);
1505:
1506: update lns_distributions
1507: set event_id = l_event_id
1508: ,last_update_date = sysdate
1509: ,object_version_number = object_version_number + 1
1510: where loan_id = p_acc_event_tbl(k).loan_id
1511: and activity = l_activity
1512: and nvl(disb_header_id, -1) = nvl(p_acc_event_tbl(k).disb_header_id,-1)
1513: and nvl(loan_amount_adj_id, -1) = nvl(p_acc_event_tbl(k).loan_amount_adj_id, -1);
1514:
1515: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'In LNS_DISTRIBUTIONS_PUB.createEvent(), updation of event_id is done for '||SQL%ROWCOUNT||' rows');
1516:
1517: end if;
1518:
1519: end loop;
1761: ,p_loan_id number) return number
1762: is
1763:
1764: l_new_cc_id number;
1765: l_ledger_details lns_distributions_pub.gl_ledger_details;
1766: l_natural_account_rec varchar2(25); -- the lns_def_distribs replacement for Loans Receivable
1767: l_nat_acct_seg_number number;
1768: l_api_name varchar2(25);
1769:
1778: and h.loan_type_id = d.loan_type_id;
1779:
1780: begin
1781:
1782: l_ledger_details := lns_distributions_pub.getLedgerDetails;
1783: -- given a code_combination
1784: if p_distribution_type = 'LOAN_RECEIVABLE' then
1785:
1786: -- build new cc_id
1853: || p_distribution_type = 'ORIGINATION' , 'BILLING', 'FUNDING'
1854: ||
1855: || Return value: table of distribution entities
1856: ||
1857: || Source Tables: lns_distributions
1858: ||
1859: || Target Tables:
1860: ||
1861: || KNOWN ISSUES
1872: ,p_loan_type_id in number
1873: ,p_account_type in varchar2
1874: ,p_account_name in varchar2
1875: ,p_line_type in varchar2
1876: ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.default_distributions_tbl
1877: is
1878: x_distribution_tbl LNS_DISTRIBUTIONS_PUB.default_distributions_tbl;
1879: l_index number := 1;
1880: l_loan_id number;
1874: ,p_account_name in varchar2
1875: ,p_line_type in varchar2
1876: ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.default_distributions_tbl
1877: is
1878: x_distribution_tbl LNS_DISTRIBUTIONS_PUB.default_distributions_tbl;
1879: l_index number := 1;
1880: l_loan_id number;
1881: l_loan_class varchar2(30);
1882: l_loan_type_id number;
2040: || p_distribution_type = 'ORIGINATION' , 'BILLING' , ' FUNDING'
2041: ||
2042: || Return value: table of distribution entities
2043: ||
2044: || Source Tables: lns_distributions
2045: ||
2046: || Target Tables:
2047: ||
2048: || KNOWN ISSUES
2058: function getDistributions(p_loan_id in number
2059: ,p_account_type in varchar2
2060: ,p_account_name in varchar2
2061: ,p_line_type in varchar2
2062: ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.distribution_tbl
2063:
2064: is
2065:
2066: l_api_name varchar2(30);
2063:
2064: is
2065:
2066: l_api_name varchar2(30);
2067: x_distribution_tbl lns_distributions_pub.distribution_tbl;
2068: l_index number;
2069: l_loan_id number;
2070: l_distribution_id number;
2071: l_line_type varchar2(30);
2100: ,d.task_id
2101: ,d.award_id
2102: ,d.expenditure_type
2103: ,d.expenditure_item_date
2104: from lns_distributions d
2105: where d.loan_id = x_loan_id
2106: and d.account_type = x_acct_type
2107: and d.account_name = x_acct_name
2108: and d.line_type = x_line_type
2203: return x_distribution_tbl;
2204:
2205: end getDistributions;
2206:
2207: function getDistributions(p_distribution_id in number) return LNS_DISTRIBUTIONS_PUB.distribution_rec
2208:
2209: is
2210:
2211: x_distribution_rec lns_distributions_pub.distribution_rec;
2207: function getDistributions(p_distribution_id in number) return LNS_DISTRIBUTIONS_PUB.distribution_rec
2208:
2209: is
2210:
2211: x_distribution_rec lns_distributions_pub.distribution_rec;
2212: l_api_name varchar2(30);
2213:
2214: cursor c_get_distribution(x_distribution_id number) is
2215: select distribution_id
2227: ,task_id
2228: ,award_id
2229: ,expenditure_type
2230: ,expenditure_item_date
2231: from lns_distributions
2232: where distribution_id = x_distribution_id
2233: and distribution_percent >= 0;
2234:
2235: begin
2297: || p_distribution_type = 'ORIGINATION' , 'BILLING' , ' FUNDING'
2298: ||
2299: || Return value: table of distribution entities
2300: ||
2301: || Source Tables: lns_distributions
2302: ||
2303: || Target Tables:
2304: ||
2305: || KNOWN ISSUES
2316: ,p_loan_line_id in number
2317: ,p_account_type in varchar2
2318: ,p_account_name in varchar2
2319: ,p_line_type in varchar2
2320: ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.distribution_tbl
2321:
2322: is
2323:
2324: l_api_name varchar2(30);
2321:
2322: is
2323:
2324: l_api_name varchar2(30);
2325: x_distribution_tbl lns_distributions_pub.distribution_tbl;
2326: l_index number;
2327: l_loan_id number;
2328: l_distribution_id number;
2329: l_line_type varchar2(30);
2361: ,d.task_id
2362: ,d.award_id
2363: ,d.expenditure_type
2364: ,d.expenditure_item_date
2365: from lns_distributions d
2366: where d.loan_id = x_loan_id
2367: and d.loan_line_id = x_loan_line_id
2368: and d.account_type = x_acct_type
2369: and d.account_name = x_acct_name
2507: || Date Author Description of Changes
2508: || 02-18-2004 raverma Created
2509: ||
2510: *=======================================================================*/
2511: function getLedgerDetails return lns_distributions_pub.gl_ledger_details
2512: is
2513: cursor c_ledger
2514: is
2515: SELECT so.set_of_books_id
2524: fnd_currencies fndc
2525: WHERE sb.ledger_id = so.set_of_books_id
2526: and sb.currency_code = fndc.currency_code;
2527:
2528: l_ledger_details lns_distributions_pub.gl_ledger_details;
2529:
2530: begin
2531:
2532: begin
2566: ||
2567: || DESCRIPTION
2568: || calculatest the distribution amount based on the distribution percentage
2569: || this api assumes the defaultDistributions API has been called to
2570: || store the distributions on LNS_DISTRIBUTIONS
2571: ||
2572: || PSEUDO CODE/LOGIC
2573: ||
2574: || PARAMETERS
2571: ||
2572: || PSEUDO CODE/LOGIC
2573: ||
2574: || PARAMETERS
2575: || p_distribution_id = pk to LNS_DISTRIBUTIONS
2576: ||
2577: || Return value: amount of distribution based from loan funded amount
2578: ||
2579: || Source Tables: lns_distributions, lns_loan_headers
2575: || p_distribution_id = pk to LNS_DISTRIBUTIONS
2576: ||
2577: || Return value: amount of distribution based from loan funded amount
2578: ||
2579: || Source Tables: lns_distributions, lns_loan_headers
2580: ||
2581: || Target Tables: NA
2582: ||
2583: || KNOWN ISSUES
2595:
2596: is
2597: l_api_name varchar2(50);
2598: l_distribution_amount number;
2599: l_ledger_details lns_distributions_pub.gl_ledger_details;
2600: l_distribution_rec lns_distributions_pub.distribution_rec;
2601: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2602: l_loan_id number;
2603: l_max_distribution_id number;
2596: is
2597: l_api_name varchar2(50);
2598: l_distribution_amount number;
2599: l_ledger_details lns_distributions_pub.gl_ledger_details;
2600: l_distribution_rec lns_distributions_pub.distribution_rec;
2601: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2602: l_loan_id number;
2603: l_max_distribution_id number;
2604:
2597: l_api_name varchar2(50);
2598: l_distribution_amount number;
2599: l_ledger_details lns_distributions_pub.gl_ledger_details;
2600: l_distribution_rec lns_distributions_pub.distribution_rec;
2601: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2602: l_loan_id number;
2603: l_max_distribution_id number;
2604:
2605: cursor c_get_distribution(p_distribution_id number) is
2603: l_max_distribution_id number;
2604:
2605: cursor c_get_distribution(p_distribution_id number) is
2606: select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
2607: from lns_distributions lnd
2608: ,lns_loan_headers lnh
2609: ,fnd_currencies curr
2610: where lnh.loan_id = lnd.loan_id
2611: and curr.currency_code = lnh.loan_currency
2615: cursor c_max_dist(p_loan_id number
2616: ,p_distribution_type varchar
2617: ,p_account_type varchar2) is
2618: select max(distribution_id)
2619: from lns_distributions lnd
2620: ,lns_loan_headers lnh
2621: where lnh.loan_id = lnd.loan_id
2622: and lnd.distribution_type = p_distribution_type
2623: and lnd.account_type = p_account_type
2630: select
2631: lnh.funded_amount -
2632: (round(lnh.funded_amount *
2633: (select sum(distribution_percent) / 100
2634: from lns_distributions
2635: where distribution_id <> p_distribution_id
2636: and distribution_type = p_distribution_type
2637: and account_type = p_account_type
2638: and loan_id = p_loan_id), curr.precision))
2635: where distribution_id <> p_distribution_id
2636: and distribution_type = p_distribution_type
2637: and account_type = p_account_type
2638: and loan_id = p_loan_id), curr.precision))
2639: from lns_distributions lnd
2640: ,lns_loan_headers lnh
2641: ,fnd_currencies curr
2642: where lnh.loan_id = lnd.loan_id
2643: and lnh.loan_id = p_loan_id
2646: begin
2647:
2648: l_api_name := 'calculateDistributionAmount';
2649: --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2650: l_ledger_details := lns_distributions_pub.getLedgerDetails;
2651: l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2652:
2653: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2654: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2647:
2648: l_api_name := 'calculateDistributionAmount';
2649: --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2650: l_ledger_details := lns_distributions_pub.getLedgerDetails;
2651: l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2652:
2653: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2654: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2655: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'disttype = ' || l_distribution_rec.distribution_type);
2655: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'disttype = ' || l_distribution_rec.distribution_type);
2656: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounttype = ' || l_distribution_rec.account_type);
2657:
2658: if l_distribution_rec.distribution_amount is null then
2659: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.getDistributions...');
2660: l_distribution_tbl := lns_distributions_pub.getDistributions(p_loan_id => l_distribution_rec.loan_id
2661: ,p_account_type => l_distribution_rec.account_type
2662: ,p_account_name => l_distribution_rec.account_name
2663: ,p_line_type => l_distribution_rec.line_type
2656: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounttype = ' || l_distribution_rec.account_type);
2657:
2658: if l_distribution_rec.distribution_amount is null then
2659: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.getDistributions...');
2660: l_distribution_tbl := lns_distributions_pub.getDistributions(p_loan_id => l_distribution_rec.loan_id
2661: ,p_account_type => l_distribution_rec.account_type
2662: ,p_account_name => l_distribution_rec.account_name
2663: ,p_line_type => l_distribution_rec.line_type
2664: ,p_distribution_type => l_distribution_rec.distribution_type);
2701: ||
2702: || DESCRIPTION
2703: || calculatest the distribution amount based on the distribution percentage
2704: || this api assumes the defaultDistributions API has been called to
2705: || store the distributions on LNS_DISTRIBUTIONS
2706: ||
2707: || PSEUDO CODE/LOGIC
2708: ||
2709: || PARAMETERS
2706: ||
2707: || PSEUDO CODE/LOGIC
2708: ||
2709: || PARAMETERS
2710: || p_distribution_id = pk to LNS_DISTRIBUTIONS
2711: || p_accounted_flag = 'Y' to get amount in set_of_books currency
2712: || 'N' to get amount in loan currency
2713: ||
2714: || Return value: amount of distribution based from loan funded amount
2712: || 'N' to get amount in loan currency
2713: ||
2714: || Return value: amount of distribution based from loan funded amount
2715: ||
2716: || Source Tables: lns_distributions, lns_loan_headers
2717: ||
2718: || Target Tables: NA
2719: ||
2720: || KNOWN ISSUES
2734: is
2735: l_api_name varchar2(50);
2736: l_distribution_amount number;
2737: l_return number;
2738: l_ledger_details lns_distributions_pub.gl_ledger_details;
2739: l_distribution_rec lns_distributions_pub.distribution_rec;
2740: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2741: l_loan_id number;
2742: l_max_distribution_id number;
2735: l_api_name varchar2(50);
2736: l_distribution_amount number;
2737: l_return number;
2738: l_ledger_details lns_distributions_pub.gl_ledger_details;
2739: l_distribution_rec lns_distributions_pub.distribution_rec;
2740: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2741: l_loan_id number;
2742: l_max_distribution_id number;
2743: l_currency_code varchar2(10);
2736: l_distribution_amount number;
2737: l_return number;
2738: l_ledger_details lns_distributions_pub.gl_ledger_details;
2739: l_distribution_rec lns_distributions_pub.distribution_rec;
2740: l_distribution_tbl lns_distributions_pub.distribution_tbl;
2741: l_loan_id number;
2742: l_max_distribution_id number;
2743: l_currency_code varchar2(10);
2744: l_exchange_rate_type varchar2(30);
2754: where loan_id = p_loan_id;
2755:
2756: cursor c_get_distribution(p_distribution_id number) is
2757: select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
2758: from lns_distributions lnd
2759: ,lns_loan_headers lnh
2760: ,fnd_currencies curr
2761: where lnh.loan_id = lnd.loan_id
2762: and curr.currency_code = lnh.loan_currency
2769: select
2770: lnh.funded_amount -
2771: (round(lnh.funded_amount *
2772: (select sum(distribution_percent) / 100
2773: from lns_distributions
2774: where distribution_id <> p_distribution_id
2775: and distribution_type = p_distribution_type
2776: and account_type = p_account_type
2777: and loan_id = p_loan_id), curr.precision))
2774: where distribution_id <> p_distribution_id
2775: and distribution_type = p_distribution_type
2776: and account_type = p_account_type
2777: and loan_id = p_loan_id), curr.precision))
2778: from lns_distributions lnd
2779: ,lns_loan_headers lnh
2780: ,fnd_currencies curr
2781: where lnh.loan_id = lnd.loan_id
2782: and lnh.loan_id = p_loan_id
2786:
2787: l_api_name := 'calculateDistributionAmount';
2788:
2789: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2790: l_ledger_details := lns_distributions_pub.getLedgerDetails;
2791: l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2792:
2793: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2794: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2787: l_api_name := 'calculateDistributionAmount';
2788:
2789: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2790: l_ledger_details := lns_distributions_pub.getLedgerDetails;
2791: l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2792:
2793: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2794: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2795: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'disttype = ' || l_distribution_rec.distribution_type);
2844:
2845: cursor c_currency1(p_distribution_id number) is
2846: select lnh.loan_currency
2847: from lns_loan_headers lnh
2848: ,lns_distributions lnd
2849: where lnh.loan_id = lnd.loan_id
2850: and lnd.distribution_id = p_distribution_id;
2851:
2852: cursor c_currency2 is
2869: fetch c_currency1 into l_currency;
2870: close c_currency1;
2871: end if;
2872:
2873: l_amount := lns_distributions_pub.calculateDistributionAmount(p_distribution_id => p_distribution_id
2874: ,p_accounted_flag => p_accounted_flag);
2875: l_char := to_char(l_amount, fnd_currency.safe_get_format_mask(l_currency,25));
2876:
2877: l_return := l_char || ' ' || l_currency;
3054: ,x_msg_count OUT NOCOPY NUMBER
3055: ,x_msg_data OUT NOCOPY VARCHAR2)
3056: is
3057:
3058: --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3055: ,x_msg_data OUT NOCOPY VARCHAR2)
3056: is
3057:
3058: --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3056: is
3057:
3058: --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3057:
3058: --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3065: l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3058: --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3065: l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3066:
3059: l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3065: l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3066:
3067: l_dist_percent_rec_orig number;
3060: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3065: l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3066:
3067: l_dist_percent_rec_orig number;
3068: l_dist_percent_rec_bill number;
3061: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3062: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3063: l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3064: l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3065: l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
3066:
3067: l_dist_percent_rec_orig number;
3068: l_dist_percent_rec_bill number;
3069: l_dist_percent_clear_orig number;
3101: ,dist.distribution_percent
3102: ,dist.distribution_amount
3103: ,dist.distribution_type
3104: ,dist.event_id
3105: from lns_distributions dist
3106: where dist.loan_id = C_LOAN_ID
3107: and dist.account_type = 'CR'
3108: and dist.account_name = 'LOAN_RECEIVABLE'
3109: and dist.line_type = 'PRIN'
3144: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_class = ' || l_loan_class);
3145: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_status = ' || l_loan_status);
3146: -- get the distributions details
3147: /*
3148: l_loan_liability_fund := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3149: ,p_account_type => 'DR'
3150: ,p_account_name => 'LOAN_LIABILITY'
3151: ,p_line_type => 'ORIG'
3152: ,p_distribution_type => 'FUNDING');
3152: ,p_distribution_type => 'FUNDING');
3153: */
3154: if l_loan_class = 'ERS' then
3155:
3156: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 1...');
3157: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3158: ,p_account_type => 'CR'
3159: ,p_account_name => 'LOAN_CLEARING'
3160: ,p_line_type => 'CLEAR'
3153: */
3154: if l_loan_class = 'ERS' then
3155:
3156: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 1...');
3157: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3158: ,p_account_type => 'CR'
3159: ,p_account_name => 'LOAN_CLEARING'
3160: ,p_line_type => 'CLEAR'
3161: ,p_distribution_type => 'ORIGINATION');
3160: ,p_line_type => 'CLEAR'
3161: ,p_distribution_type => 'ORIGINATION');
3162: elsif l_loan_class = 'DIRECT' then
3163:
3164: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 2...');
3165: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3166: ,p_account_type => 'CR'
3167: ,p_account_name => 'LOAN_PAYABLE'
3168: ,p_line_type => 'CLEAR'
3161: ,p_distribution_type => 'ORIGINATION');
3162: elsif l_loan_class = 'DIRECT' then
3163:
3164: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 2...');
3165: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3166: ,p_account_type => 'CR'
3167: ,p_account_name => 'LOAN_PAYABLE'
3168: ,p_line_type => 'CLEAR'
3169: ,p_distribution_type => 'ORIGINATION');
3168: ,p_line_type => 'CLEAR'
3169: ,p_distribution_type => 'ORIGINATION');
3170: end if;
3171:
3172: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 3...');
3173: l_loan_receivables_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3174: ,p_account_type => 'DR'
3175: ,p_account_name => 'LOAN_RECEIVABLE'
3176: ,p_line_type => 'ORIG'
3169: ,p_distribution_type => 'ORIGINATION');
3170: end if;
3171:
3172: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 3...');
3173: l_loan_receivables_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3174: ,p_account_type => 'DR'
3175: ,p_account_name => 'LOAN_RECEIVABLE'
3176: ,p_line_type => 'ORIG'
3177: ,p_distribution_type => 'ORIGINATION');
3175: ,p_account_name => 'LOAN_RECEIVABLE'
3176: ,p_line_type => 'ORIG'
3177: ,p_distribution_type => 'ORIGINATION');
3178:
3179: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 4...');
3180: IF ((l_loan_class = 'ERS') AND (l_loan_status NOT IN ('INCOMPLETE', 'PENDING', 'APPROVED')) ) THEN
3181: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FETCH ERS ACTIVE distributions');
3182: l_index := 0;
3183: OPEN c_get_prin_distr(p_loan_id);
3211:
3212: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Fetched ' || l_loan_receivables_bill.count || ' records');
3213:
3214: ELSE
3215: l_loan_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3216: ,p_account_type => 'CR'
3217: ,p_account_name => 'LOAN_RECEIVABLE'
3218: ,p_line_type => 'PRIN'
3219: ,p_distribution_type => 'BILLING');
3218: ,p_line_type => 'PRIN'
3219: ,p_distribution_type => 'BILLING');
3220: END IF;
3221:
3222: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 5...');
3223: l_prin_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3224: ,p_account_type => 'DR'
3225: ,p_account_name => 'PRINCIPAL_RECEIVABLE'
3226: ,p_line_type => 'PRIN'
3219: ,p_distribution_type => 'BILLING');
3220: END IF;
3221:
3222: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 5...');
3223: l_prin_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3224: ,p_account_type => 'DR'
3225: ,p_account_name => 'PRINCIPAL_RECEIVABLE'
3226: ,p_line_type => 'PRIN'
3227: ,p_distribution_type => 'BILLING');
3225: ,p_account_name => 'PRINCIPAL_RECEIVABLE'
3226: ,p_line_type => 'PRIN'
3227: ,p_distribution_type => 'BILLING');
3228:
3229: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 6...');
3230: l_int_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3231: ,p_account_type => 'DR'
3232: ,p_account_name => 'INTEREST_RECEIVABLE'
3233: ,p_line_type => 'INT'
3226: ,p_line_type => 'PRIN'
3227: ,p_distribution_type => 'BILLING');
3228:
3229: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 6...');
3230: l_int_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3231: ,p_account_type => 'DR'
3232: ,p_account_name => 'INTEREST_RECEIVABLE'
3233: ,p_line_type => 'INT'
3234: ,p_distribution_type => 'BILLING');
3232: ,p_account_name => 'INTEREST_RECEIVABLE'
3233: ,p_line_type => 'INT'
3234: ,p_distribution_type => 'BILLING');
3235:
3236: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 7...');
3237: l_int_income_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3238: ,p_account_type => 'CR'
3239: ,p_account_name => 'INTEREST_INCOME'
3240: ,p_line_type => 'INT'
3233: ,p_line_type => 'INT'
3234: ,p_distribution_type => 'BILLING');
3235:
3236: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 7...');
3237: l_int_income_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
3238: ,p_account_type => 'CR'
3239: ,p_account_name => 'INTEREST_INCOME'
3240: ,p_line_type => 'INT'
3241: ,p_distribution_type => 'BILLING');
3771: l_api_name varchar2(30);
3772: l_msg_count NUMBER;
3773: l_msg_data VARCHAR2(2000);
3774: l_return_Status VARCHAR2(1);
3775: l_distributions lns_distributions_pub.distribution_tbl;
3776: l_exists number;
3777: l_disb_header_id number;
3778: l_loan_amount_adj_id number;
3779: l_distributions_count number;
3785:
3786:
3787: CURSOR c_distribs_exist(c_loan_id NUMBER, c_disb_header_id NUMBER, c_loan_amount_adj_id NUMBER, c_activity VARCHAR2) IS
3788: SELECT count(1)
3789: FROM lns_distributions
3790: WHERE loan_id = c_loan_id
3791: AND nvl(disb_header_id, -1) = nvl(c_disb_header_id, -1)
3792: AND nvl(loan_amount_adj_id, -1) = nvl(c_loan_amount_adj_id, -1)
3793: AND activity = c_activity
3799: where loan_id = p_loan_id;
3800:
3801: cursor c_subsidy_rows_exist(p_loan_id number) is
3802: select count(1)
3803: from lns_distributions
3804: where loan_id = p_loan_id
3805: and line_type = 'SUBSIDY'
3806: and distribution_type = 'ORIGINATION'
3807: --and event_id is not null;
4048: || Return value: Standard S = Success E = Error U = Unexpected
4049: ||
4050: || Source Tables: LNS_DEFAULT_DISTRIBUTIONS, lns_loan_headers_all
4051: ||
4052: || Target Tables: LNS_DISTRIBUTIONS
4053: ||
4054: || KNOWN ISSUES
4055: ||
4056: || NOTES
4086: i number;
4087: n number;
4088: l_code_combination_id number;
4089: l_code_combination_id_new_rec number;
4090: l_distributions lns_distributions_pub.distribution_tbl;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4087: n number;
4088: l_code_combination_id number;
4089: l_code_combination_id_new_rec number;
4090: l_distributions lns_distributions_pub.distribution_tbl;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4095: l_distributionsALL lns_distributions_pub.distribution_tbl;
4088: l_code_combination_id number;
4089: l_code_combination_id_new_rec number;
4090: l_distributions lns_distributions_pub.distribution_tbl;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4095: l_distributionsALL lns_distributions_pub.distribution_tbl;
4096: l_distributions_count number;
4089: l_code_combination_id_new_rec number;
4090: l_distributions lns_distributions_pub.distribution_tbl;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4095: l_distributionsALL lns_distributions_pub.distribution_tbl;
4096: l_distributions_count number;
4097: l_distributionsCatch_count number;
4090: l_distributions lns_distributions_pub.distribution_tbl;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4095: l_distributionsALL lns_distributions_pub.distribution_tbl;
4096: l_distributions_count number;
4097: l_distributionsCatch_count number;
4098: l_total_distributions number;
4091: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
4092: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
4093: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
4094: l_distributionsCatch lns_distributions_pub.distribution_tbl;
4095: l_distributionsALL lns_distributions_pub.distribution_tbl;
4096: l_distributions_count number;
4097: l_distributionsCatch_count number;
4098: l_total_distributions number;
4099: l_ers_distribution_amount number;
4097: l_distributionsCatch_count number;
4098: l_total_distributions number;
4099: l_ers_distribution_amount number;
4100: l_orig_distribution_amount number;
4101: l_ledger_details lns_distributions_pub.gl_ledger_details;
4102: l_include_receivables varchar2(1);
4103: l_sum number;
4104: l_multifund number;
4105: l_multifund_exists number;
4302: OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))));
4303:
4304: CURSOR cur_bill_dist_count(c_loan_id NUMBER) IS
4305: SELECT count(1)
4306: FROM lns_distributions
4307: WHERE loan_id = p_loan_id
4308: AND account_name IN ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME');
4309:
4310: CURSOR cur_default_ar_adj(c_loan_id NUMBER) IS
4377: IF (p_loan_class_code = 'ERS'AND l_loan_status = 'INCOMPLETE') THEN
4378: return;
4379: END IF;
4380: */
4381: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateDefaultAccounting...');
4382: lns_distributions_pub.validateDefaultAccounting(p_loan_class => l_loan_class
4383: ,p_loan_type_id => l_loan_type_id
4384: ,p_init_msg_list => p_init_msg_list
4385: ,x_return_status => l_return_status
4378: return;
4379: END IF;
4380: */
4381: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateDefaultAccounting...');
4382: lns_distributions_pub.validateDefaultAccounting(p_loan_class => l_loan_class
4383: ,p_loan_type_id => l_loan_type_id
4384: ,p_init_msg_list => p_init_msg_list
4385: ,x_return_status => l_return_status
4386: ,x_msg_count => l_msg_count
4396:
4397: /* delete any rows for this loan before inheritance do not delete FEE_RECEIVABLE or FEE_INCOME rows*/
4398: /* Commented Bug#7406404 - Defaulting the laon distributions happens only at Loan Creation time and later only updation at loan level is changed.
4399: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing accounting rows except FEE_RECEIVABLE or FEE_INCOME...');
4400: delete from lns_distributions
4401: where loan_id = p_loan_id
4402: and account_name in ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME', 'LOAN_RECEIVABLE', 'LOAN_CLEARING', 'LOAN_LIABILITY', 'LOAN_PAYABLE');
4403: --and event_id is null; --fix for bug 8815841: delete all rows including rows with event_id not null
4404: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Done');
4493:
4494: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'ERS LOAN INHERITANCE');
4495: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing ERS loan Booking distributions and Principal LOAN_RECEIVABLE');
4496:
4497: delete from lns_distributions
4498: where loan_id = p_loan_id
4499: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING');
4500:
4501: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleted '||SQL%ROWCOUNT||' rows');
4501: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleted '||SQL%ROWCOUNT||' rows');
4502:
4503: -- this switch is for the CatchAll Procedure
4504: l_include_receivables := 'N';
4505: l_ledger_details := lns_distributions_pub.getLedgerDetails;
4506: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'chart_of_accounts_id = ' || l_ledger_details.chart_of_accounts_id);
4507:
4508: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Fetching documents to account...');
4509: open c_get_loan_documents(p_loan_id);
4856: --l_percent := round(l_distributionsCLEAR_ORIG(k).distribution_amount / l_clearing_total_amount_due * 100,4);
4857: --l_percent := round(l_distributionsREC_ORIG(k).distribution_amount / l_receivable_total_amount_due * 100,4);
4858: l_distributionsREC_ORIG(k).distribution_percent := l_percent;
4859: l_distributionsREC_BILL(k).distribution_percent := l_percent;
4860: -- ensure this amount is accurate it will get inserted into lns_distributions for loans booking
4861: l_distributionsREC_ORIG(k).distribution_amount := l_percent / 100 * l_funded_amount;
4862: else
4863: -- last row ensure that amounts = 100% and total = funded amount of loan
4864: l_percent := 100 - l_running_percent;
4921: IF l_bill_dist_cnt < 3 THEN
4922: -- inherit remaining account_names based on loan class + type for
4923: -- principal / interest receivable, interest income
4924:
4925: delete from lns_distributions
4926: where loan_id = p_loan_id
4927: and account_name IN ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME');
4928:
4929: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Billing rows - deleted ||'||SQL%ROWCOUNT||' rows');
4988: do_insert_distributions(p_distributions_tbl => l_distributionsALL
4989: ,p_loan_id => p_loan_id);
4990:
4991: -- validate the accounting rows here
4992: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateAccounting...');
4993: lns_distributions_pub.validateAccounting(p_loan_id => p_loan_id
4994: ,p_init_msg_list => p_init_msg_list
4995: ,x_return_status => l_return_status
4996: ,x_msg_count => l_msg_count
4989: ,p_loan_id => p_loan_id);
4990:
4991: -- validate the accounting rows here
4992: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateAccounting...');
4993: lns_distributions_pub.validateAccounting(p_loan_id => p_loan_id
4994: ,p_init_msg_list => p_init_msg_list
4995: ,x_return_status => l_return_status
4996: ,x_msg_count => l_msg_count
4997: ,x_msg_data => l_msg_data);
5103: and source_id_int_1 = p_loan_id
5104: and source_id_int_2 IN
5105: (select disb.disb_header_id
5106: from lns_disb_headers dh
5107: ,lns_distributions disb
5108: where disb.loan_id = p_loan_id
5109: and disb.disb_header_id = dh.disb_header_id
5110: and disb.account_name = 'LOAN_RECEIVABLE'
5111: and dh.status = 'FULLY_FUNDED'
5127:
5128: and source_id_int_3 IN
5129: (select disb.loan_amount_adj_id
5130: from lns_loan_amount_adjs ladj
5131: ,lns_distributions disb
5132: where disb.loan_id = p_loan_id
5133: and disb.loan_amount_adj_id = ladj.loan_amount_adj_id
5134: and disb.account_name = 'LOAN_RECEIVABLE'
5135: and ladj.status = 'APPROVED'
5190: else
5191:
5192: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIRECT loan accounting');
5193:
5194: -- can we make join thru lns_distributions
5195: insert into XLA_ACCT_PROG_DOCS_GT
5196: (entity_id)
5197: select entity_id from xla_transaction_entities
5198: where application_id = 206
5200: and source_id_int_1 = p_loan_id
5201: and (source_id_int_2 = -1
5202: OR source_id_int_2 in (select disb.disb_header_id
5203: from lns_disb_headers dh
5204: ,lns_distributions disb
5205: where disb.loan_id = p_loan_id
5206: and disb.disb_header_id = dh.disb_header_id
5207: and disb.account_name = 'LOAN_RECEIVABLE'
5208: and ((disb.activity = 'LNS_SUBMIT_DISBURSEMENT') OR (disb.activity = 'DISBURSEMENT' AND dh.status = 'FULLY_FUNDED'))
5211: and (source_id_int_3 IS NULL -- Before introducing MD loanAdjustment, source_id_int_3 values are NULL
5212: OR source_id_int_3 = -1
5213: OR source_id_int_3 in (select disb.loan_amount_adj_id
5214: from lns_loan_amount_adjs ladj
5215: ,lns_distributions disb
5216: where disb.loan_id = p_loan_id
5217: and disb.loan_amount_adj_id = ladj.loan_amount_adj_id
5218: and disb.account_name = 'LOAN_RECEIVABLE'
5219: and ladj.status = 'APPROVED')
5434:
5435:
5436:
5437: -- we should do online accounting in batch mode here
5438: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling Lns_distributions_pub.defaultDistributions...');
5439: Lns_distributions_pub.defaultDistributions(p_api_version => 1.0
5440: ,p_init_msg_list => FND_API.G_TRUE
5441: ,p_commit => FND_API.G_FALSE
5442: ,p_loan_id => P_LOAN_ID
5435:
5436:
5437: -- we should do online accounting in batch mode here
5438: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling Lns_distributions_pub.defaultDistributions...');
5439: Lns_distributions_pub.defaultDistributions(p_api_version => 1.0
5440: ,p_init_msg_list => FND_API.G_TRUE
5441: ,p_commit => FND_API.G_FALSE
5442: ,p_loan_id => P_LOAN_ID
5443: ,p_loan_class_code => l_loan_class_code
5446: ,x_msg_data => l_msg_data);
5447: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
5448:
5449: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5450: -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Call to Lns_distributions_pub.defaultDistributions failed with status ' || l_return_status);
5451: -- fnd_file.put_line(FND_FILE.LOG, 'FAILED TO INHERIT DISTRIBUTIONS');
5452: --l_last_api_called := 'Lns_distributions_pub.defaultDistributions';
5453: RAISE FND_API.G_EXC_ERROR;
5454: ELSE
5448:
5449: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5450: -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Call to Lns_distributions_pub.defaultDistributions failed with status ' || l_return_status);
5451: -- fnd_file.put_line(FND_FILE.LOG, 'FAILED TO INHERIT DISTRIBUTIONS');
5452: --l_last_api_called := 'Lns_distributions_pub.defaultDistributions';
5453: RAISE FND_API.G_EXC_ERROR;
5454: ELSE
5455:
5456: if x_event_id is not null then
5453: RAISE FND_API.G_EXC_ERROR;
5454: ELSE
5455:
5456: if x_event_id is not null then
5457: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
5458: update lns_distributions
5459: set event_id = x_event_id
5460: where loan_id = P_LOAN_ID
5461: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
5454: ELSE
5455:
5456: if x_event_id is not null then
5457: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
5458: update lns_distributions
5459: set event_id = x_event_id
5460: where loan_id = P_LOAN_ID
5461: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
5462: and distribution_type = 'ORIGINATION'
5564: procedure createDistrForImport(p_api_version IN NUMBER
5565: ,p_init_msg_list IN VARCHAR2
5566: ,p_commit IN VARCHAR2
5567: ,p_loan_id IN NUMBER
5568: ,x_distribution_tbl IN OUT NOCOPY lns_distributions_pub.distribution_tbl
5569: ,x_return_status OUT NOCOPY VARCHAR2
5570: ,x_msg_count OUT NOCOPY NUMBER
5571: ,x_msg_data OUT NOCOPY VARCHAR2)
5572:
5605: x_return_status := FND_API.G_RET_STS_SUCCESS;
5606:
5607: /* deleting any existing accounting rows */
5608: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' deleting any existing accounting rows');
5609: delete from lns_distributions
5610: where loan_id = p_loan_id;
5611:
5612: do_insert_distributions(p_distributions_tbl => x_distribution_tbl
5613: ,p_loan_id => p_loan_id);
5613: ,p_loan_id => p_loan_id);
5614:
5615: -- validate the accounting rows here
5616: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' validating Accounting');
5617: lns_distributions_pub.validateAccounting(p_loan_id => p_loan_id
5618: ,p_init_msg_list => p_init_msg_list
5619: ,x_return_status => l_return_status
5620: ,x_msg_count => l_msg_count
5621: ,x_msg_data => l_msg_data);
5660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5661: END IF;
5662:
5663: if l_event_id is not null then
5664: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
5665: update lns_distributions
5666: set event_id = l_event_id
5667: where loan_id = P_LOAN_ID
5668: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
5661: END IF;
5662:
5663: if l_event_id is not null then
5664: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
5665: update lns_distributions
5666: set event_id = l_event_id
5667: where loan_id = P_LOAN_ID
5668: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
5669: and distribution_type = 'ORIGINATION'
5862: ELSE
5863: /* delete any rows for this loan before inheritance do not delete FEE_RECEIVABLE or FEE_INCOME rows*/
5864: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing adjustment accounting rows for '||l_loan_id||'-'||l_loan_amount_adj_id);
5865:
5866: delete from lns_distributions
5867: where loan_id = l_loan_id
5868: and loan_amount_adj_id = l_loan_amount_adj_id
5869: and account_name in ('LOAN_RECEIVABLE', 'LOAN_PAYABLE');
5870:
5872:
5873: END IF;
5874:
5875: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Calling create_DisbursementDistribs');
5876: LNS_DISTRIBUTIONS_PUB.create_DisbursementDistribs(p_api_version => 1
5877: ,p_init_msg_list => 'F'
5878: ,p_commit => 'T'
5879: ,p_loan_id => l_loan_id
5880: ,p_disb_header_id => NULL
6179: logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
6180: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6181: END IF;
6182:
6183: -- stamp the eventID onto the lns_distributions table
6184: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
6185:
6186:
6187:
6180: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6181: END IF;
6182:
6183: -- stamp the eventID onto the lns_distributions table
6184: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
6185:
6186:
6187:
6188: update lns_distributions
6184: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
6185:
6186:
6187:
6188: update lns_distributions
6189: set event_id = l_event_id
6190: ,last_update_date = sysdate
6191: where distribution_type = 'ORIGINATION'
6192: and loan_id = p_loan_id
6369: ,x_msg_count OUT NOCOPY NUMBER
6370: ,x_msg_data OUT NOCOPY VARCHAR2)
6371: is
6372:
6373: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6374: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6375: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6376: l_dist_percent_rec_bill number;
6377: l_api_name varchar2(30);
6370: ,x_msg_data OUT NOCOPY VARCHAR2)
6371: is
6372:
6373: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6374: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6375: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6376: l_dist_percent_rec_bill number;
6377: l_api_name varchar2(30);
6378:
6371: is
6372:
6373: l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6374: l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6375: l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
6376: l_dist_percent_rec_bill number;
6377: l_api_name varchar2(30);
6378:
6379: begin
6390: x_return_status := FND_API.G_RET_STS_SUCCESS;
6391:
6392: l_dist_percent_rec_bill := 0;
6393:
6394: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 1...');
6395: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6396: ,p_loan_line_id => p_loan_line_id
6397: ,p_account_type => 'CR'
6398: ,p_account_name => 'LOAN_CLEARING'
6391:
6392: l_dist_percent_rec_bill := 0;
6393:
6394: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 1...');
6395: l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6396: ,p_loan_line_id => p_loan_line_id
6397: ,p_account_type => 'CR'
6398: ,p_account_name => 'LOAN_CLEARING'
6399: ,p_line_type => 'CLEAR'
6399: ,p_line_type => 'CLEAR'
6400: ,p_distribution_type => 'ORIGINATION');
6401: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_clearing_orig count = ' || l_loan_clearing_orig.count);
6402:
6403: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 2...');
6404: l_loan_receivables_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6405: ,p_loan_line_id => p_loan_line_id
6406: ,p_account_type => 'DR'
6407: ,p_account_name => 'LOAN_RECEIVABLE'
6400: ,p_distribution_type => 'ORIGINATION');
6401: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_clearing_orig count = ' || l_loan_clearing_orig.count);
6402:
6403: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 2...');
6404: l_loan_receivables_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6405: ,p_loan_line_id => p_loan_line_id
6406: ,p_account_type => 'DR'
6407: ,p_account_name => 'LOAN_RECEIVABLE'
6408: ,p_line_type => 'ORIG'
6408: ,p_line_type => 'ORIG'
6409: ,p_distribution_type => 'ORIGINATION');
6410: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_receivables_orig count = ' || l_loan_receivables_orig.count);
6411:
6412: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 3...');
6413: l_loan_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6414: ,p_loan_line_id => p_loan_line_id
6415: ,p_account_type => 'CR'
6416: ,p_account_name => 'LOAN_RECEIVABLE'
6409: ,p_distribution_type => 'ORIGINATION');
6410: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_receivables_orig count = ' || l_loan_receivables_orig.count);
6411:
6412: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 3...');
6413: l_loan_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
6414: ,p_loan_line_id => p_loan_line_id
6415: ,p_account_type => 'CR'
6416: ,p_account_name => 'LOAN_RECEIVABLE'
6417: ,p_line_type => 'PRIN'
6507: n number;
6508: y number;
6509: l_code_combination_id number;
6510: l_code_combination_id_new_rec number;
6511: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
6512: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
6513: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
6514: l_distributionsALL lns_distributions_pub.distribution_tbl;
6515: l_ers_distribution_amount number;
6508: y number;
6509: l_code_combination_id number;
6510: l_code_combination_id_new_rec number;
6511: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
6512: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
6513: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
6514: l_distributionsALL lns_distributions_pub.distribution_tbl;
6515: l_ers_distribution_amount number;
6516: l_ledger_details lns_distributions_pub.gl_ledger_details;
6509: l_code_combination_id number;
6510: l_code_combination_id_new_rec number;
6511: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
6512: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
6513: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
6514: l_distributionsALL lns_distributions_pub.distribution_tbl;
6515: l_ers_distribution_amount number;
6516: l_ledger_details lns_distributions_pub.gl_ledger_details;
6517: l_sum number;
6510: l_code_combination_id_new_rec number;
6511: l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
6512: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
6513: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
6514: l_distributionsALL lns_distributions_pub.distribution_tbl;
6515: l_ers_distribution_amount number;
6516: l_ledger_details lns_distributions_pub.gl_ledger_details;
6517: l_sum number;
6518: l_natural_account_rec varchar2(25); -- the lns_def_distribs replacement for Loans Receivable
6512: l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
6513: l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
6514: l_distributionsALL lns_distributions_pub.distribution_tbl;
6515: l_ers_distribution_amount number;
6516: l_ledger_details lns_distributions_pub.gl_ledger_details;
6517: l_sum number;
6518: l_natural_account_rec varchar2(25); -- the lns_def_distribs replacement for Loans Receivable
6519: l_nat_acct_seg_number number;
6520: l_adjustment_exists boolean;
6652: group by ael.code_combination_id;
6653:
6654: cursor c_get_funded_amount(p_loan_id number, p_code_combination_id number, p_adj_date date) is
6655: select nvl(sum(dist.distribution_amount), 0)
6656: from lns_distributions dist,
6657: lns_loan_lines lines
6658: where dist.distribution_type = 'ORIGINATION'
6659: and dist.line_type = 'ORIG'
6660: and dist.account_name = 'LOAN_RECEIVABLE'
6696: ,dist.code_combination_id
6697: ,dist.distribution_percent
6698: ,dist.distribution_amount
6699: ,dist.distribution_type
6700: from lns_distributions dist
6701: where dist.loan_id = C_LOAN_ID
6702: and dist.account_type = 'CR'
6703: and dist.account_name = 'LOAN_RECEIVABLE'
6704: and dist.line_type = 'PRIN'
6753: end if;
6754:
6755: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Starting ERS INHERITANCE');
6756:
6757: l_ledger_details := lns_distributions_pub.getLedgerDetails;
6758: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'chart_of_accounts_id = ' || l_ledger_details.chart_of_accounts_id);
6759:
6760: logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Fetching documents to account...');
6761: open c_get_line_documents(p_loan_line_id);
7183: ,p_loan_id => p_loan_id);
7184:
7185: -- validate the accounting rows here
7186: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' validating Accounting');
7187: lns_distributions_pub.validateAddRecAccounting(p_loan_id => p_loan_id
7188: ,p_loan_line_id => p_loan_line_id
7189: ,p_init_msg_list => p_init_msg_list
7190: ,x_return_status => l_return_status
7191: ,x_msg_count => l_msg_count
7224: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7225: END IF;
7226:
7227: if l_event_id is not null then
7228: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
7229: update lns_distributions
7230: set event_id = l_event_id
7231: where loan_id = P_LOAN_ID
7232: and loan_line_id = p_loan_line_id
7225: END IF;
7226:
7227: if l_event_id is not null then
7228: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
7229: update lns_distributions
7230: set event_id = l_event_id
7231: where loan_id = P_LOAN_ID
7232: and loan_line_id = p_loan_line_id
7233: and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
7288: || Parameter: p_loan_id => loan to default
7289: ||
7290: || Return value: Standard S = Success E = Error U = Unexpected
7291: ||
7292: || Source Tables: LNS_DISTRIBUTIONS, lns_loan_headers_all
7293: ||
7294: || Target Tables: LNS_DISTRIBUTIONS
7295: ||
7296: || KNOWN ISSUES
7290: || Return value: Standard S = Success E = Error U = Unexpected
7291: ||
7292: || Source Tables: LNS_DISTRIBUTIONS, lns_loan_headers_all
7293: ||
7294: || Target Tables: LNS_DISTRIBUTIONS
7295: ||
7296: || KNOWN ISSUES
7297: ||
7298: || NOTES
7321: l_requested_amount NUMBER;
7322: l_funded_amount NUMBER;
7323: l_cancel_amount NUMBER;
7324:
7325: l_distributions lns_distributions_pub.distribution_tbl;
7326: l_line_type VARCHAR2(30);
7327: l_account_name VARCHAR2(30);
7328: l_code_combination_id NUMBER;
7329: l_account_type VARCHAR2(30);
7334: l_receivSubsidyCnt NUMBER;
7335: l_clearOrigCnt NUMBER;
7336: l_clearSubsidyCnt NUMBER;
7337: l_loan_receivables_count NUMBER;
7338: l_ledger_details lns_distributions_pub.gl_ledger_details;
7339:
7340: l_run_rec_org_amt NUMBER;
7341: l_run_rec_sub_amt NUMBER;
7342: l_run_clear_org_amt NUMBER;
7355: ,d.code_combination_id
7356: ,d.account_type
7357: ,d.distribution_percent
7358: ,d.distribution_type
7359: from lns_distributions d
7360: where d.loan_id = c_loan_id
7361: and d.distribution_type = 'ORIGINATION'
7362: and d.distribution_percent > 0
7363: and d.disb_header_id IS NULL
7374:
7375: cursor c_num_receivables(c_loan_id number)
7376: is
7377: select count(1)
7378: from lns_distributions
7379: where loan_id = c_loan_id
7380: and account_name = 'LOAN_RECEIVABLE'
7381: and distribution_type = 'ORIGINATION'
7382: and account_type = 'DR'
7384: /*
7385: cursor c_num_payables(c_loan_id number)
7386: is
7387: select count(1)
7388: from lns_distributions
7389: where loan_id = c_loan_id
7390: and account_name in ('LOAN_PAYABLE', 'LOAN_CLEARING')
7391: and distribution_type = 'ORIGINATION'
7392: and account_type = 'CR'
7420: l_run_rec_org_amt := 0;
7421: l_run_rec_sub_amt := 0;
7422: l_run_clear_org_amt:= 0;
7423: l_run_clear_sub_amt:= 0;
7424: l_ledger_details := lns_distributions_pub.getLedgerDetails;
7425:
7426: lns_utility_pub.validate_any_id(p_api_version => 1.0
7427: ,p_init_msg_list => p_init_msg_list
7428: ,x_msg_count => l_msg_count
7695:
7696:
7697: IF p_activity = 'LNS_SUBMITTED_DISB_CANCEL' THEN
7698: -- first complete accounting for any unprocessed events / documents for the loan transaction
7699: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.onlineAccounting...');
7700: lns_distributions_pub.onlineAccounting(p_loan_id => p_loan_id
7701: ,p_init_msg_list => fnd_api.g_false
7702: ,p_accounting_mode => 'F'
7703: ,p_transfer_flag => 'Y'
7696:
7697: IF p_activity = 'LNS_SUBMITTED_DISB_CANCEL' THEN
7698: -- first complete accounting for any unprocessed events / documents for the loan transaction
7699: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.onlineAccounting...');
7700: lns_distributions_pub.onlineAccounting(p_loan_id => p_loan_id
7701: ,p_init_msg_list => fnd_api.g_false
7702: ,p_accounting_mode => 'F'
7703: ,p_transfer_flag => 'Y'
7704: ,p_offline_flag => 'N'
7721: */
7722:
7723: if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y') then
7724:
7725: -- create disbursement distribution records in lns_distributions for Disbursement Submission
7726: LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling create_DisbursementDistribs...');
7727: LNS_DISTRIBUTIONS_PUB.create_DisbursementDistribs(
7728: p_api_version => 1.0,
7729: p_init_msg_list => FND_API.G_FALSE,
7723: if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y') then
7724:
7725: -- create disbursement distribution records in lns_distributions for Disbursement Submission
7726: LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling create_DisbursementDistribs...');
7727: LNS_DISTRIBUTIONS_PUB.create_DisbursementDistribs(
7728: p_api_version => 1.0,
7729: p_init_msg_list => FND_API.G_FALSE,
7730: p_commit => FND_API.G_FALSE,
7731: p_loan_id => p_loan_id,
7736: x_msg_data => l_msg_data);
7737:
7738: LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Return status: ' || l_return_status);
7739: IF l_return_status <> 'S' THEN
7740: LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Call to LNS_DISTRIBUTIONS_PUB.create_DisbursementDistribs for Disb Submit failed');
7741: RAISE FND_API.G_EXC_ERROR;
7742: END IF;
7743:
7744: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'federal enabled');
7786: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7787: END IF;
7788:
7789: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping new event_id on distributions');
7790: update lns_distributions
7791: set event_id = l_event_id
7792: ,last_update_date = sysdate
7793: ,object_Version_number = object_version_number + 1
7794: where distribution_type = 'ORIGINATION'
8174: CURSOR cur_last_accrual_date(c_loan_id NUMBER) IS
8175: SELECT
8176: max(ldist.accrual_date)
8177: FROM
8178: lns_distributions ldist
8179: WHERE ldist.loan_id = c_loan_id
8180: AND ldist.distribution_type = 'ACCRUAL'
8181: AND ldist. activity = 'INTEREST_ACCRUAL';
8182:
8420: l_api_name varchar2(30);
8421: l_msg_count NUMBER;
8422: l_msg_data VARCHAR2(2000);
8423: l_return_Status VARCHAR2(1);
8424: l_distributions lns_distributions_pub.distribution_tbl;
8425: l_distributions_count NUMBER;
8426: l_activity VARCHAR2(30);
8427: l_exists NUMBER;
8428: l_accrued_amt NUMBER;
8440: l_int_rec_line_cnt NUMBER;
8441: l_int_income_line_cnt NUMBER;
8442: l_int_rec_running_amt NUMBER;
8443: l_int_income_running_amt NUMBER;
8444: l_ledger_details lns_distributions_pub.gl_ledger_details;
8445: l_loan_num VARCHAR2(30);
8446:
8447:
8448:
8456: loan.loan_id = C_LOAN_ID;
8457:
8458: CURSOR c_accrual_date_distrbs_exists(c_loan_id NUMBER, c_accrual_date VARCHAR2) IS
8459: SELECT count(1)
8460: FROM lns_distributions ldist
8461: WHERE ldist.loan_id = c_loan_id
8462: AND ldist.accrual_date >= c_accrual_date
8463: AND ldist.distribution_type = 'ACCRUAL'
8464: AND ldist.accrual_completed_flag = 'Y';
8469: ,ldist.code_combination_id
8470: ,ldist.account_type
8471: ,ldist.distribution_percent
8472: ,ldist.distribution_type
8473: from lns_distributions ldist
8474: where ldist.loan_id = c_loan_id
8475: and ldist.line_type = 'INT'
8476: and ldist.distribution_type = 'BILLING'
8477: and ldist.distribution_percent >= 0
8478: order by ldist.account_type, ldist.code_combination_id;
8479:
8480: CURSOR c_num_int_receivables(c_loan_id number) IS
8481: select count(1)
8482: from lns_distributions ldist
8483: where ldist.loan_id = c_loan_id
8484: and ldist.line_type = 'INT'
8485: and ldist.distribution_type = 'BILLING'
8486: and ldist.distribution_percent >= 0
8488: and ldist.account_name = 'INTEREST_RECEIVABLE';
8489:
8490: CURSOR c_num_int_incomes(c_loan_id number) IS
8491: select count(1)
8492: from lns_distributions ldist
8493: where ldist.loan_id = c_loan_id
8494: and ldist.line_type = 'INT'
8495: and ldist.distribution_type = 'BILLING'
8496: and ldist.distribution_percent >= 0
8540: l_int_rec_line_cnt := 0;
8541: l_int_income_line_cnt := 0;
8542: l_int_rec_running_amt := 0;
8543: l_int_income_running_amt := 0;
8544: l_ledger_details := lns_distributions_pub.getLedgerDetails;
8545: ------------------------------
8546: l_accrued_amount := p_accrued_amount;
8547: -------------------------------
8548:
9050: CURSOR cur_last_accrual_date(c_loan_id NUMBER) IS
9051: SELECT
9052: max(ldist.accrual_date)
9053: FROM
9054: lns_distributions ldist
9055: WHERE ldist.loan_id = c_loan_id
9056: AND ldist.distribution_type = 'ACCRUAL'
9057: AND ldist. activity = 'INTEREST_ACCRUAL'
9058: AND ldist.accrual_completed_flag = 'Y';
9066: ,distribution_percent
9067: ,distribution_amount
9068: ,distribution_type
9069: FROM
9070: lns_distributions ldist
9071: WHERE ldist.loan_id = c_loan_id
9072: AND ldist.accrual_date = c_accrual_date
9073: AND ldist.distribution_type = 'ACCRUAL'
9074: AND ldist. activity = 'INTEREST_ACCRUAL'
9109:
9110: -- Retrieve the loans's sla event and its status of drafted accruals
9111: CURSOR cur_get_draft_events(c_loan_id NUMBER) IS
9112: SELECT distinct ldist.event_id, evnt.event_status_code
9113: FROM lns_distributions ldist, xla_events evnt
9114: WHERE ldist.event_id = evnt.event_id
9115: AND ldist.loan_id = C_LOAN_ID
9116: AND ldist.activity = 'INTEREST_ACCRUAL'
9117: AND ldist.distribution_type = 'ACCRUAL'
9275: CLOSE cur_get_draft_events;
9276:
9277: -- l_last_accrued_date is the last accrued date for that loan which is Final Accounted in SLA
9278: -- So delete all the above distributions above that date
9279: DELETE FROM lns_distributions ldist
9280: WHERE ldist.loan_id = P_LOAN_ID
9281: AND ldist.activity = 'INTEREST_ACCRUAL'
9282: AND ldist.distribution_type = 'ACCRUAL'
9283: AND ldist.accrual_completed_flag <> 'Y'
9342: logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
9343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9344: END IF;
9345:
9346: --3. stamp the eventID onto the lns_distributions table
9347: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
9348:
9349:
9350:
9343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9344: END IF;
9345:
9346: --3. stamp the eventID onto the lns_distributions table
9347: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
9348:
9349:
9350:
9351: update lns_distributions
9347: logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
9348:
9349:
9350:
9351: update lns_distributions
9352: set event_id = l_event_id
9353: ,last_update_date = sysdate
9354: where distribution_type = 'ACCRUAL'
9355: and loan_id = p_loan_id
9847: FOR l_Count1 IN 1..l_loans_to_accrue_tbl.COUNT LOOP
9848:
9849: LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Processing loan #' || l_Count1||' - '||l_loans_to_accrue_tbl(l_Count1));
9850:
9851: LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoking LNS_DISTRIBUTIONS_PUB.do_int_accrual_reversal..');
9852:
9853: l_start := sysdate;
9854: LNS_DISTRIBUTIONS_PUB.do_int_accrual_reversal(p_api_version => 1
9855: ,p_init_msg_list => 'T'
9850:
9851: LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoking LNS_DISTRIBUTIONS_PUB.do_int_accrual_reversal..');
9852:
9853: l_start := sysdate;
9854: LNS_DISTRIBUTIONS_PUB.do_int_accrual_reversal(p_api_version => 1
9855: ,p_init_msg_list => 'T'
9856: ,p_commit => 'F'
9857: ,p_loan_id => l_loans_to_accrue_tbl(l_Count1)
9858: ,p_period_name => p_period_name
10085: ||
10086: ||
10087: || Return value: x_distribution_tbl distribution table set to write to database
10088: ||
10089: || Source Tables: lns_distributions
10090: ||
10091: || Target Tables: NA
10092: ||
10093: || KNOWN ISSUES
10105: ,p_loan_id IN NUMBER
10106: ,p_disb_header_id IN NUMBER DEFAULT NULL
10107: ,p_loan_amount_adj_id IN NUMBER DEFAULT NULL
10108: ,p_activity IN VARCHAR2
10109: ,x_distribution_tbl OUT NOCOPY lns_distributions_pub.distribution_tbl
10110: ,x_return_status OUT NOCOPY VARCHAR2
10111: ,x_msg_count OUT NOCOPY NUMBER
10112: ,x_msg_data OUT NOCOPY VARCHAR2)
10113:
10134: l_funded_amount number;
10135: l_adj_reversal varchar2(1);
10136: l_loan_receivables_count number;
10137: l_loan_payables_count number;
10138: l_distributions lns_distributions_pub.distribution_tbl;
10139: l_dr_run_amount number;
10140: l_cr_run_amount number;
10141: drIndex number;
10142: crIndex number;
10139: l_dr_run_amount number;
10140: l_cr_run_amount number;
10141: drIndex number;
10142: crIndex number;
10143: l_ledger_details lns_distributions_pub.gl_ledger_details;
10144: Type refCur is ref cursor;
10145: sql_Cur refCur;
10146: vSqlCur varchar2(1000);
10147: vPLSQL VARCHAR2(1000);
10168:
10169: cursor c_num_receivables(c_loan_id number, c_activity varchar2)
10170: is
10171: select count(1)
10172: from lns_distributions
10173: where loan_id = c_loan_id
10174: and activity = c_activity
10175: and account_type = 'DR';
10176:
10176:
10177: cursor c_num_payables(c_loan_id number, c_activity varchar2)
10178: is
10179: select count(1)
10180: from lns_distributions
10181: where loan_id = c_loan_id
10182: and activity = c_activity
10183: and account_type = 'CR';
10184:
10191: ,d.code_combination_id
10192: ,d.distribution_percent
10193: ,d.distribution_amount
10194: ,d.distribution_type
10195: from lns_distributions d
10196: where d.loan_id = c_loan_id
10197: and d.activity = 'LNS_APPROVAL'
10198: order by d.account_type, d.code_combination_id;
10199:
10219: drIndex := 0;
10220: crIndex := 0;
10221: l_dr_run_amount := 0;
10222: l_cr_run_amount := 0;
10223: l_ledger_details := lns_distributions_pub.getLedgerDetails;
10224:
10225: -- get fund amount
10226: l_adj_reversal := 'N';
10227: if p_activity = 'LOAN_AMOUNT_ADJUSTMENT' then
10397: || p_expenditure_date = expenditure date
10398: ||
10399: || Return value: CCID corresponding to the Project, Task, Award, Expenditure Type combination
10400: || Concatenated Segment value
10401: || Source Tables: lns_distributions
10402: ||
10403: || Target Tables:
10404: ||
10405: || KNOWN ISSUES
10743: END GENERATE_CCID_FOR_PROJECTS;
10744:
10745:
10746:
10747: END LNS_DISTRIBUTIONS_PUB;