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