1 PACKAGE BODY LNS_LOAN_LINE_PUB AS
2 /* $Header: LNS_LINE_PUBP_B.pls 120.7.12010000.2 2008/10/23 21:57:03 mbolli ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_LOAN_LINE_PUB';
8
9 --These package variables used for ERS rules bulk processing api.
10 --The values are set in the function GET_RULES_DERIVED_ERS_AMOUNT
11 --The three public functions get_loan_party_id, get_loan_currency_code and get_loan_org_id
12 --retrieve these for external api usage
13 LNS_LOAN_PARTY_ID NUMBER(15);
14 LNS_LOAN_CURRENCY_CODE VARCHAR2(15);
15 LNS_LOAN_ORG_ID NUMBER(15);
16
17 procedure logMessage(log_level in number
18 ,module in varchar2
19 ,message in varchar2)
20 is
21
22 begin
23
24 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
25 FND_LOG.STRING(log_level, module, message);
26 END IF;
27
28 end;
29
30 /*========================================================================
31 | PUBLIC PROCEDURE UPDATE_LINE_ADJUSTMENT_NUMBER
32 |
33 | DESCRIPTION
34 | This procedure updates the rec number column in loan lines table based on AR Adjustment api out parameter during loan approval
35 |
36 | NOTES
37 | There are no table-handler apis for loan lines table since it uses java-based EO
38 |
39 | MODIFICATION HISTORY
40 | Date Author Description of Changes
41 | 10-Jan-2006 karamach Added payment_schedule_id and installment_number for lns_loan_lines per bug#4887994
42 | 20-Dec-2004 karamach Created
43 |
44 *=======================================================================*/
45 PROCEDURE UPDATE_LINE_ADJUSTMENT_NUMBER(
46 p_init_msg_list IN VARCHAR2
47 ,p_loan_id IN NUMBER
48 ,p_loan_line_id IN NUMBER
49 ,p_rec_adjustment_number IN VARCHAR2
50 ,p_rec_adjustment_id IN NUMBER
51 ,p_payment_schedule_id IN NUMBER
52 ,p_installment_number IN NUMBER
53 ,x_return_status OUT NOCOPY VARCHAR2
54 ,x_msg_count OUT NOCOPY NUMBER
55 ,x_msg_data OUT NOCOPY VARCHAR2) IS
56
57 l_return_status VARCHAR2(1);
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2(32767);
60
61
62 BEGIN
63
64 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
65 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin UPDATE_LINE_ADJUSTMENT_NUMBER for loan_line_id: '|| p_loan_line_id);
66 END IF;
67
68 -- Initialize message list IF p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean(p_init_msg_list) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- Initialize API return status to SUCCESS
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76
77 --Call to record history
78 LNS_LOAN_HISTORY_PUB.log_record_pre(
79 p_id => p_loan_line_id,
80 p_primary_key_name => 'LOAN_LINE_ID',
81 p_table_name => 'LNS_LOAN_LINES'
82 );
83
84 -- update loan line
85 UPDATE LNS_LOAN_LINES
86 SET REC_ADJUSTMENT_NUMBER = p_rec_adjustment_number,
87 REC_ADJUSTMENT_ID = p_rec_adjustment_id,
88 PAYMENT_SCHEDULE_ID = p_payment_schedule_id,
89 INSTALLMENT_NUMBER = p_installment_number,
90 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
92 LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
93 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
94 WHERE LOAN_LINE_ID = p_loan_line_id
95 AND LOAN_ID = p_loan_id;
96
97 --Call to record history
98 LNS_LOAN_HISTORY_PUB.log_record_post(
99 p_id => p_loan_line_id,
100 p_primary_key_name => 'LOAN_LINE_ID',
101 p_table_name => 'LNS_LOAN_LINES',
102 p_loan_id => p_loan_id
103 );
104
105
106 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
107 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End UPDATE_LINE_ADJUSTMENT_NUMBER for loan_line_id: '|| p_loan_line_id);
108 END IF;
109
110 EXCEPTION
111 WHEN FND_API.G_EXC_ERROR THEN
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
114 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
115 END IF;
116
117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
118 x_return_status := FND_API.G_RET_STS_ERROR;
119 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
120 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, sqlerrm);
121 END IF;
122
123 WHEN OTHERS THEN
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
126 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
127 END IF;
128
129 END UPDATE_LINE_ADJUSTMENT_NUMBER;
130
131
132 /*========================================================================
133 | PUBLIC FUNCTION GET_LOAN_PARTY_ID
134 |
135 | DESCRIPTION
136 | This function will be used by rules engine as a filter to the bulk processing rules api when executing query.
137 | The function returns the value for the package variable LNS_LOAN_PARTY_ID
138 |
139 | NOTES
140 | This function is used in the bulk rule processing api for better performance
141 |
142 | MODIFICATION HISTORY
143 | Date Author Description of Changes
144 | 13-Jan-2006 karamach Created
145 *=======================================================================*/
146 FUNCTION GET_LOAN_PARTY_ID RETURN NUMBER
147 IS
148 BEGIN
149 return LNS_LOAN_PARTY_ID;
150 END GET_LOAN_PARTY_ID;
151
152 /*========================================================================
153 | PUBLIC FUNCTION GET_LOAN_CURRENCY_CODE
154 |
155 | DESCRIPTION
156 | This function will be used by rules engine as a filter to the bulk processing rules api when executing query.
157 | The function returns the value for the package variable LNS_LOAN_CURRENCY_CODE
158 |
159 | NOTES
160 | This function is used in the bulk rule processing api for better performance
161 |
162 | MODIFICATION HISTORY
163 | Date Author Description of Changes
164 | 13-Jan-2006 karamach Created
165 *=======================================================================*/
166 FUNCTION GET_LOAN_CURRENCY_CODE RETURN VARCHAR2
167 IS
168 BEGIN
169 return LNS_LOAN_CURRENCY_CODE;
170 END GET_LOAN_CURRENCY_CODE;
171
172 /*========================================================================
173 | PUBLIC FUNCTION GET_LOAN_ORG_ID
174 |
175 | DESCRIPTION
176 | This function will be used by rules engine as a filter to the bulk processing rules api when executing query.
177 | The function returns the value for the package variable LNS_LOAN_ORG_ID
178 |
179 | NOTES
180 | This function is used in the bulk rule processing api for better performance
181 |
182 | MODIFICATION HISTORY
183 | Date Author Description of Changes
184 | 13-Jan-2006 karamach Created
185 *=======================================================================*/
186 FUNCTION GET_LOAN_ORG_ID RETURN NUMBER
187 IS
188 BEGIN
189 return LNS_LOAN_ORG_ID;
190 END GET_LOAN_ORG_ID;
191
192 /*========================================================================
193 | PUBLIC FUNCTION GET_RULES_DERIVED_ERS_AMOUNT
194 |
195 | DESCRIPTION
196 | This function applies rules defined on the loan product
197 | for ERS loan receivables derivation and inserts into loan lines table.
198 | If NO rules have been defined for the loan product, calling this api retrieves
199 | ALL OPEN Receivables for the customer and inserts them into loan lines.
200 | The function returns the total requested amount for updating loan header
201 | after inserting the receivables into lns_loan_lines table.
202 |
203 | NOTES
204 | This api does a bulk select if max_requested_amount is NOT specified on the product.
205 | This api does bulk insert into lns_loan_lines after retrieving the matching receivables into table types.
206 | Incase an error is encountered during processing the api returns zero with error message in the stack.
207 | The api also returns zero if no receivables found for inserting into loan lines.
208 |
209 | MODIFICATION HISTORY
210 | Date Author Description of Changes
211 | 13-Jan-2006 karamach Created
212 *=======================================================================*/
213 FUNCTION GET_RULES_DERIVED_ERS_AMOUNT(
214 p_loan_id IN NUMBER,
215 p_primary_borrower_id IN NUMBER,
216 p_currency_code IN VARCHAR2,
217 p_org_id IN NUMBER,
218 p_loan_product_id IN NUMBER
219 ) RETURN NUMBER
220 IS
221 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULES_DERIVED_ERS_AMOUNT';
222 l_loan_id NUMBER;
223 l_party_id NUMBER;
224 l_currency_code VARCHAR2(15);
225 l_org_id NUMBER;
226 l_loan_product_id NUMBER;
227 l_loan_product_name VARCHAR2(80);
228 l_max_amount NUMBER;
229 l_loan_amount NUMBER;
230 l_inv_conv_amount NUMBER;
231 l_record_count NUMBER;
232 l_user_id NUMBER;
233 l_login_id NUMBER;
234 l_date DATE;
235 l_loan_line_exists VARCHAR2(1);
236 l_last_api_called varchar2(100); --Store the last api that was called before exception
237 l_sort_attribute VARCHAR2(1024);
238 l_rule_exists VARCHAR2(1);
239 l_bulk_process VARCHAR2(1);
240
241 CURSOR c_check_existing_line(pLoanId Number) IS
242 select 'Y' line_exists
243 from dual
244 where exists
245 (select null from lns_loan_lines where loan_id = pLoanId and end_date is null);
246
247 CURSOR c_loan_product(pLoanProductId Number, pOrgId Number) IS
248 select loan_product_name,MAX_REQUESTED_AMOUNT
249 from lns_loan_products_all_vl
250 where loan_product_id = pLoanProductId
251 and org_id = pOrgId;
252
253 --Need to define separate table types for each column for bulk insert since table type with all columns is not supported for bulk processing
254 TYPE lns_pmt_sch_id_type IS TABLE OF LNS_LOAN_LINES.PAYMENT_SCHEDULE_ID%TYPE INDEX BY PLS_INTEGER;
255 l_pmt_sch_id_tbl lns_pmt_sch_id_type;
256
257 TYPE lns_reference_id_type IS TABLE OF LNS_LOAN_LINES.REFERENCE_ID%TYPE INDEX BY PLS_INTEGER;
258 l_reference_id_tbl lns_reference_id_type;
259
260 TYPE lns_reference_number_type IS TABLE OF LNS_LOAN_LINES.REFERENCE_NUMBER%TYPE INDEX BY PLS_INTEGER;
261 l_reference_number_tbl lns_reference_number_type;
262
263 TYPE lns_reference_amount_type IS TABLE OF LNS_LOAN_LINES.REFERENCE_AMOUNT%TYPE INDEX BY PLS_INTEGER;
264 l_reference_amount_tbl lns_reference_amount_type;
265
266 TYPE lns_requested_amount_type IS TABLE OF LNS_LOAN_LINES.REQUESTED_AMOUNT%TYPE INDEX BY PLS_INTEGER;
267 l_requested_amount_tbl lns_requested_amount_type;
268
269 TYPE lns_installment_number_type IS TABLE OF LNS_LOAN_LINES.INSTALLMENT_NUMBER%TYPE INDEX BY PLS_INTEGER;
270 l_installment_number_tbl lns_installment_number_type;
271
272 CURSOR c_get_rule_object(pRuleObjectName VARCHAR2, pApplicationId NUMBER, pLoanProductId NUMBER, pOrgId NUMBER) IS
273 select 'Y' rule_exists, attr.DEFAULT_VALUE sort_attribute
274 FROM
275 FUN_RULE_OBJECTS_B obj, FUN_RULE_OBJ_ATTRIBUTES attr
276 WHERE obj.RULE_OBJECT_NAME = pRuleObjectName
277 AND obj.application_id = pApplicationId
278 AND obj.instance_label = to_char(pLoanProductId)
279 AND obj.ORG_ID = pOrgId
280 AND obj.PARENT_RULE_OBJECT_ID is not null
281 AND obj.RULE_OBJECT_ID = attr.RULE_OBJECT_ID;
282
283 CURSOR c_rule_result_invoices_sorted(pSortAttribute VARCHAR2) IS
284 select inv.payment_schedule_id,
285 inv.customer_trx_id,
286 inv.trx_number,
287 inv.AMOUNT_DUE_REMAINING,
288 inv.TERMS_SEQUENCE_NUMBER
289 FROM
290 LNS_OPEN_RECEIVABLES_V inv,
291 FUN_RULE_BULK_RESULT_GT results,
292 FUN_RULE_DETAILS rules
293 WHERE inv.payment_schedule_id = results.id
294 AND rules.RULE_DETAIL_ID = results.RULE_DETAIL_ID
295 AND results.RULE_DETAIL_ID <> -99
296 ORDER BY rules.SEQ, pSortAttribute;
297
298 /* -- pSortAttribute
299 (select DEFAULT_VALUE from
300 FUN_RULE_OBJ_ATTRIBUTES attr, FUN_RULE_DETAILS rule, FUN_RULE_BULK_RESULT_GT gt
301 where attr.RULE_OBJECT_ID = rule.rule_object_id
302 and rule.rule_detail_id = gt.rule_detail_id
303 and rownum < 2);
304 */
305
306 CURSOR c_rule_result_invoices_nosort IS
307 select inv.payment_schedule_id,
308 inv.customer_trx_id,
309 inv.trx_number,
310 inv.AMOUNT_DUE_REMAINING reference_amount,
311 inv.AMOUNT_DUE_REMAINING,
312 inv.TERMS_SEQUENCE_NUMBER
313 FROM
314 LNS_OPEN_RECEIVABLES_V inv,
315 FUN_RULE_BULK_RESULT_GT results
316 WHERE inv.payment_schedule_id = results.id
317 AND results.RULE_DETAIL_ID <> -99;
318
319 CURSOR c_all_open_invoices_nosort(pPartyId NUMBER, pOrgId NUMBER, pCurrencyCode VARCHAR2) IS
320 select inv.payment_schedule_id,
321 inv.customer_trx_id,
322 inv.trx_number,
323 inv.AMOUNT_DUE_REMAINING reference_amount,
324 inv.AMOUNT_DUE_REMAINING,
325 inv.TERMS_SEQUENCE_NUMBER
326 FROM
327 LNS_OPEN_RECEIVABLES_V inv
328 WHERE inv.party_id = pPartyId
329 AND inv.org_id = pOrgId
330 AND inv.invoice_currency_code = pCurrencyCode;
331
332 CURSOR c_get_bulk_total(pLoanId NUMBER) IS
333 select sum(requested_amount) total_amount, count(loan_line_id) record_count
334 from lns_loan_lines
335 where loan_id = pLoanId
336 and end_date is null;
337
338 BEGIN
339
340 l_last_api_called := '';
341 l_bulk_process := 'N';
342 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
343 -- Standard Start of API savepoint
344 SAVEPOINT loan_lines_derivation;
345
346
347 if (p_loan_id is null) then
348
349 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, l_api_name || ': ' || ' - missing loan_id');
350
351 --throw exception
352 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_LOAN_ID');
353 FND_MSG_PUB.Add;
354 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ': ' || FND_MSG_PUB.Get(p_encoded => 'F'));
355 RAISE FND_API.G_EXC_ERROR;
356 end if;
357
358 --Initialize the local variables
359 l_loan_amount := 0;
360 l_record_count := 0;
361 l_loan_id := p_loan_id;
362 l_party_id := p_primary_borrower_id;
363 l_currency_code := p_currency_code;
364 l_org_id := p_org_id;
365 l_loan_product_id := p_loan_product_id;
366 l_user_id := LNS_UTILITY_PUB.created_by;
367 l_login_id := LNS_UTILITY_PUB.last_update_login;
368 l_date := sysdate;
369 l_loan_line_exists := 'N';
370
371 open c_check_existing_line(l_loan_id);
372 fetch c_check_existing_line into l_loan_line_exists;
373 close c_check_existing_line;
374
375 --if loan lines already exist, then the user should delete them explicitly before inheriting new receivables
376 if (l_loan_line_exists = 'Y') then
377
378 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, l_api_name || ': ' || ' - loan lines already exist for this loan_id');
379
380 --throw exception
381 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_LINES_EXIST');
382 FND_MSG_PUB.Add;
383 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ': ' || FND_MSG_PUB.Get(p_encoded => 'F'));
384 RAISE FND_API.G_EXC_ERROR;
385
386 end if;
387
388 if (p_primary_borrower_id is null OR p_currency_code is null OR p_org_id is null OR p_loan_product_id is null) then
389
390 --try to derive other id values from loan_id
391 null;
392
393 end if;
394
395 if (l_party_id is null OR l_currency_code is null OR l_org_id is null OR l_loan_product_id is null) then
396
397 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, l_api_name || ': ' || ' - invalid parameters');
398
399 --throw exception
400 FND_MESSAGE.SET_NAME('LNS', 'LNS_SOME_REQ_FIELDS_EMPTY');
401 FND_MSG_PUB.Add;
402 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ': ' || FND_MSG_PUB.Get(p_encoded => 'F'));
403 RAISE FND_API.G_EXC_ERROR;
404
405 end if;
406
407
408 --Get loan product name
409 open c_loan_product(l_loan_product_id,l_org_id);
410 fetch c_loan_product into l_loan_product_name,l_max_amount;
411 close c_loan_product;
412
413 if (l_loan_product_name is null) then
414
415 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, l_api_name || ': ' || ' - invalid loan_product_id');
416
417 --throw exception
418 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
419 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_loan_product_id');
420 FND_MESSAGE.SET_TOKEN('VALUE', l_loan_product_id);
421 FND_MSG_PUB.Add;
422 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ': ' || FND_MSG_PUB.Get(p_encoded => 'F'));
423 RAISE FND_API.G_EXC_ERROR;
424
425 end if;
426
427 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - passed IN parameters:');
428 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_loan_id = ' || l_loan_id);
429 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_primary_borrower_id = ' || l_party_id);
430 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_currency_code = ' || l_currency_code);
431 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_org_id = ' || l_org_id);
432 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_loan_product_id = ' || l_loan_product_id);
433
434 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Maximum Budgeted Amount for this loan:' || l_max_amount);
435
436 OPEN c_get_rule_object('LNS_ERS_LOAN_PRODUCT', 206, l_loan_product_id, l_org_id);
437 FETCH c_get_rule_object INTO l_rule_exists,l_sort_attribute;
438 CLOSE c_get_rule_object;
439
440 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - l_rule_exists: ' || l_rule_exists);
441
442 IF (l_rule_exists = 'Y') THEN
443
444 --Begin code to apply rules
445
446 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Before calling api FUN_RULE_PUB.SET_INSTANCE_CONTEXT');
447 l_last_api_called := 'FUN_RULE_PUB.SET_INSTANCE_CONTEXT';
448 FUN_RULE_PUB.SET_INSTANCE_CONTEXT(P_RULE_OBJECT_NAME => 'LNS_ERS_LOAN_PRODUCT',
449 P_APPLICATION_SHORT_NAME => 'LNS',
450 P_INSTANCE_LABEL => to_char(l_loan_product_id),
451 P_ORG_ID => l_org_id
452 );
453
454 LNS_LOAN_PARTY_ID := l_party_id;
455 LNS_LOAN_CURRENCY_CODE := l_currency_code;
456 LNS_LOAN_ORG_ID := l_org_id;
457
458 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Before calling api FUN_RULE_PUB.apply_rule_bulk');
459 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - where clause for apply_rule_bulk: ');
460 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'PARTY_ID = ' || l_party_id || ' AND INVOICE_CURRENCY_CODE = ' || l_currency_code || ' AND ORG_ID = ' || l_org_id);
461
462 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || 'LNS_LOAN_LINE_PUB.LNS_LOAN_PARTY_ID = ' || LNS_LOAN_LINE_PUB.LNS_LOAN_PARTY_ID);
463 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || 'LNS_LOAN_LINE_PUB.LNS_LOAN_CURRENCY_CODE = ' || LNS_LOAN_LINE_PUB.LNS_LOAN_CURRENCY_CODE);
464 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || 'LNS_LOAN_LINE_PUB.LNS_LOAN_ORG_ID = ' || LNS_LOAN_LINE_PUB.LNS_LOAN_ORG_ID);
465 l_last_api_called := 'FUN_RULE_PUB.apply_rule_bulk';
466 FUN_RULE_PUB.apply_rule_bulk(p_application_short_name => 'LNS',
467 p_rule_object_name => 'LNS_ERS_LOAN_PRODUCT',
468 p_param_view_name => 'LNS_OPEN_RECEIVABLES_V',
469 p_additional_where_clause => 'PARTY_ID = LNS_LOAN_LINE_PUB.GET_LOAN_PARTY_ID AND INVOICE_CURRENCY_CODE = LNS_LOAN_LINE_PUB.GET_LOAN_CURRENCY_CODE AND ORG_ID = LNS_LOAN_LINE_PUB.GET_LOAN_ORG_ID',
470 p_primary_key_column_name => 'PAYMENT_SCHEDULE_ID'
471 );
472
473 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - After calling api FUN_RULE_PUB.apply_rule_bulk');
474
475 END IF; -- IF (l_rule_exists = 'Y') THEN
476
477 IF (l_max_amount IS NOT NULL) THEN
478
479 if (l_rule_exists = 'Y') then
480 --Based on rules, fetch matching invoices in the specified sort order until the max requested amount is reached
481 --There could be an invoice with only a partial amount added from it if the last invoice that is picked has its balance amount
482 --more than the remaining balance on the specified maximum requested amount
483 l_last_api_called := 'Fetch in loop using cursor c_rule_result_invoices_sorted';
484 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - l_last_api_called: ' || l_last_api_called);
485 for c_result_inv_rec in c_rule_result_invoices_sorted(l_sort_attribute) loop
486 --calculate the invoice conversion amount since this could be less than the remaining balance on the invoice
487 --based on the amount remaining in the max requested amount limit for this loan
488 l_inv_conv_amount := least(c_result_inv_rec.AMOUNT_DUE_REMAINING,l_max_amount - l_loan_amount);
489 if (l_inv_conv_amount > 0) then
490 l_record_count := l_record_count + 1;
491 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Begin processing Row#' || l_record_count);
492 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Payment_schedule_id is: ' || c_result_inv_rec.payment_schedule_id);
493 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Trx_Number: ' || c_result_inv_rec.trx_number);
494 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Amount_Due_Remaining: ' || c_result_inv_rec.AMOUNT_DUE_REMAINING);
495 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'The receivable conversion amount(loan line requested amount) is ' || l_inv_conv_amount);
496 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Total Loan Amount:' || l_loan_amount);
497 l_pmt_sch_id_tbl(l_record_count) := c_result_inv_rec.payment_schedule_id;
498 l_installment_number_tbl(l_record_count) := c_result_inv_rec.TERMS_SEQUENCE_NUMBER;
499 l_reference_id_tbl(l_record_count) := c_result_inv_rec.customer_trx_id;
500 l_reference_number_tbl(l_record_count) := c_result_inv_rec.trx_number;
501 l_reference_amount_tbl(l_record_count) := c_result_inv_rec.AMOUNT_DUE_REMAINING;
502 l_requested_amount_tbl(l_record_count) := l_inv_conv_amount;
503 l_loan_amount := l_loan_amount + l_inv_conv_amount;
504 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - End processing Row#' || l_record_count);
505 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Total Loan Amount:' || l_loan_amount);
506 end if;
507 exit when (l_loan_amount >= l_max_amount);
508 end loop;
509 else -- else part for if (l_rule_exists = 'Y') then
510 --Since rules do not exist, fetch all open invoices in no particular order until the max requested amount is reached
511 --There could be an invoice with only a partial amount added from it if the last invoice that is picked has its balance amount
512 --more than the remaining balance on the specified maximum requested amount
513 l_last_api_called := 'Fetch in loop using cursor c_all_open_invoices_nosort';
514 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - l_last_api_called: ' || l_last_api_called);
515 for c_result_inv_rec in c_all_open_invoices_nosort(l_party_id, l_org_id, l_currency_code) loop
516 --calculate the invoice conversion amount since this could be less than the remaining balance on the invoice
517 --based on the amount remaining in the max requested amount limit for this loan
518 l_inv_conv_amount := least(c_result_inv_rec.AMOUNT_DUE_REMAINING,l_max_amount - l_loan_amount);
519 if (l_inv_conv_amount > 0) then
520 l_record_count := l_record_count + 1;
521 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Begin processing Row#' || l_record_count);
522 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Payment_schedule_id is: ' || c_result_inv_rec.payment_schedule_id);
523 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Trx_Number: ' || c_result_inv_rec.trx_number);
524 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Amount_Due_Remaining: ' || c_result_inv_rec.AMOUNT_DUE_REMAINING);
525 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'The receivable conversion amount(loan line requested amount) is ' || l_inv_conv_amount);
526 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Total Loan Amount:' || l_loan_amount);
527 l_pmt_sch_id_tbl(l_record_count) := c_result_inv_rec.payment_schedule_id;
528 l_installment_number_tbl(l_record_count) := c_result_inv_rec.TERMS_SEQUENCE_NUMBER;
529 l_reference_id_tbl(l_record_count) := c_result_inv_rec.customer_trx_id;
530 l_reference_number_tbl(l_record_count) := c_result_inv_rec.trx_number;
531 l_reference_amount_tbl(l_record_count) := c_result_inv_rec.AMOUNT_DUE_REMAINING;
532 l_requested_amount_tbl(l_record_count) := l_inv_conv_amount;
533 l_loan_amount := l_loan_amount + l_inv_conv_amount;
534 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - End processing Row#' || l_record_count);
535 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - Total Loan Amount:' || l_loan_amount);
536 end if;
537 exit when (l_loan_amount >= l_max_amount);
538 end loop;
539 end if; -- if (l_rule_exists = 'Y') then
540
541 ELSE --if (l_max_amount is not null) then
542
543 l_bulk_process := 'Y';
544 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - l_bulk_process: ' || l_bulk_process);
545 if (l_rule_exists = 'Y') then
546 -- bulk fetch rule results without sort
547 l_last_api_called := 'Bulk Collect using cursor c_rule_result_invoices_nosort';
548 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - BEGIN: ' || l_last_api_called);
549 open c_rule_result_invoices_nosort;
550 fetch c_rule_result_invoices_nosort bulk collect into l_pmt_sch_id_tbl,l_reference_id_tbl,l_reference_number_tbl,l_reference_amount_tbl,l_requested_amount_tbl,l_installment_number_tbl;
551 close c_rule_result_invoices_nosort;
552 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - END: ' || l_last_api_called);
553 else
554 --bulk fetch all open invoices without sort
555 l_last_api_called := 'Bulk Collect using cursor c_all_open_invoices_nosort';
556 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - BEGIN: ' || l_last_api_called);
557 open c_all_open_invoices_nosort(l_party_id, l_org_id, l_currency_code);
558 fetch c_all_open_invoices_nosort bulk collect into l_pmt_sch_id_tbl,l_reference_id_tbl,l_reference_number_tbl,l_reference_amount_tbl,l_requested_amount_tbl,l_installment_number_tbl;
559 close c_all_open_invoices_nosort;
560 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - END: ' || l_last_api_called);
561 end if; -- if (l_rule_exists = 'Y') then
562
563 END IF; -- IF (l_max_amount IS NOT NULL) THEN
564
565 IF (l_pmt_sch_id_tbl.count <= 0) THEN
566 --No open receivable found for derivation.
567 --throw exception
568 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_REC_FOUND');
569 FND_MSG_PUB.Add;
570 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ': ' || FND_MSG_PUB.Get(p_encoded => 'F'));
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573
574 l_last_api_called := 'Bulk insert into lns_loan_lines';
575 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - BEGIN: ' || l_last_api_called);
576
577 forall i in l_pmt_sch_id_tbl.first..l_pmt_sch_id_tbl.last
578 insert into lns_loan_lines(
579 LOAN_LINE_ID
580 ,LOAN_ID
581 ,LAST_UPDATE_DATE
582 ,LAST_UPDATED_BY
583 ,LAST_UPDATE_LOGIN
584 ,CREATION_DATE
585 ,CREATED_BY
586 ,OBJECT_VERSION_NUMBER
587 ,REFERENCE_TYPE
588 ,REFERENCE_ID
589 ,REFERENCE_NUMBER
590 ,REFERENCE_DESCRIPTION
591 ,REFERENCE_AMOUNT
592 ,REQUESTED_AMOUNT
593 ,REC_ADJUSTMENT_NUMBER
594 ,END_DATE
595 ,REC_ADJUSTMENT_ID
596 ,PAYMENT_SCHEDULE_ID
597 ,INSTALLMENT_NUMBER
598 )
599 values(
600 LNS_LOAN_LINE_S.nextval
601 ,l_loan_id
602 ,l_date
603 ,l_user_id
604 ,l_login_id
605 ,l_date
606 ,l_user_id
607 ,1
608 ,'RECEIVABLE'
609 ,l_reference_id_tbl(i)
610 ,l_reference_number_tbl(i)
611 ,null
612 ,l_reference_amount_tbl(i)
613 ,l_requested_amount_tbl(i)
614 ,null
615 ,null
616 ,null
617 ,l_pmt_sch_id_tbl(i)
618 ,l_installment_number_tbl(i)
619 );
620
621 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ': ' || ' - END: ' || l_last_api_called);
622
623 IF (l_bulk_process = 'Y') THEN
624 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Record fetch was performed as a bulk operation since maximum loan amount is NOT specified in loan product');
625 /* --open cursor to get number of records and total requested amount from lns_loan_lines
626 OPEN c_get_bulk_total(l_loan_id);
627 FETCH c_get_bulk_total INTO l_loan_amount, l_record_count;
628 CLOSE c_get_bulk_total;
629 --the return value should not be null
630 --handle case when no loan lines were inserted by this procedure
631 if (l_loan_amount is null) then
632 l_loan_amount := 0;
633 end if;
634 */
635 l_loan_amount := 0;
636 l_record_count := 0;
637 for j in l_requested_amount_tbl.first..l_requested_amount_tbl.last loop
638 l_record_count := l_record_count + 1;
639 l_loan_amount := l_loan_amount + l_requested_amount_tbl(j);
640 end loop;
641 END IF;
642 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Inserted '|| l_record_count || ' rows into lns_loan_lines successfully!');
643 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - The total loan amount processed is ' || l_loan_amount);
644
645 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
646
647 return l_loan_amount;
648
649 EXCEPTION
650 WHEN FND_API.G_EXC_ERROR THEN
651 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - before rollback');
652 ROLLBACK TO loan_lines_derivation;
653 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after rollback');
654 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
655 return 0;
656 WHEN OTHERS THEN
657 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - before rollback');
658 ROLLBACK TO loan_lines_derivation;
659 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after rollback');
660 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
661 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
662 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
663 FND_MSG_PUB.ADD;
664 return 0;
665
666 END GET_RULES_DERIVED_ERS_AMOUNT;
667
668 END LNS_LOAN_LINE_PUB;
[Home] [Help]