[Home] [Help]
PACKAGE BODY: APPS.LNS_APPL_ENGINE_PUB
Source
1 PACKAGE BODY LNS_APPL_ENGINE_PUB as
2 /* $Header: LNS_APPL_ENG_B.pls 120.2.12010000.2 2008/10/23 21:56:20 mbolli ship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_APPL_ENGINE_PUB';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11 g_org_id number;
12 g_cr_return_status varchar2(10);
13 g_day_togl_after_dd number;
14 g_last_rc_appl_report clob;
15 g_last_all_appl_stmt clob;
16
17 /*========================================================================
18 | PRIVATE PROCEDURE LogMessage
19 |
20 | DESCRIPTION
21 | This procedure logs debug messages to db and to CM log
22 |
23 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
24 |
25 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
26 | None
27 |
28 | PARAMETERS
29 | p_msg_level IN Debug msg level
30 | p_msg IN Debug msg itself
31 |
32 | KNOWN ISSUES
33 | None
34 |
35 | NOTES
36 | Any interesting aspect of the code in the package body which needs
37 | to be stated.
38 |
39 | MODIFICATION HISTORY
40 | Date Author Description of Changes
41 | 21-10-2005 scherkas Created
42 |
43 *=======================================================================*/
44 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
45 IS
46 BEGIN
47 if (p_msg_level >= G_MSG_LEVEL) then
48
49 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
50 if FND_GLOBAL.Conc_Request_Id is not null then
51 fnd_file.put_line(FND_FILE.LOG, p_msg);
52 end if;
53
54 end if;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
59 END;
60
61
62
63 /*========================================================================
64 | PRIVATE PROCEDURE init
65 |
66 | DESCRIPTION
67 | This procedure inits data needed for processing
68 |
69 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
70 |
71 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
72 | LogMessage
73 |
74 | PARAMETERS
75 | None
76 |
77 | KNOWN ISSUES
78 | None
79 |
80 | NOTES
81 | Any interesting aspect of the code in the package body which needs
82 | to be stated.
83 |
84 | MODIFICATION HISTORY
85 | Date Author Description of Changes
86 | 21-10-2005 scherkas Created
87 |
88 *=======================================================================*/
89 Procedure init
90 IS
91 l_api_name CONSTANT VARCHAR2(30) := 'INIT';
92 l_org_status varchar2(1);
93
94 BEGIN
95
96 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
97
98 /* getting msg logging info */
99 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
100 if (G_LOG_ENABLED = 'N') then
101 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
102 else
103 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
104 end if;
105
106 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
107 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
108
109 /* getting org_id */
110 -- g_org_id := to_number(fnd_profile.value('ORG_ID'));
111 g_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
112 LogMessage(FND_LOG.LEVEL_STATEMENT, 'g_org_id: ' || g_org_id);
113 l_org_status := MO_GLOBAL.check_valid_org(g_org_id);
114 LogMessage(FND_LOG.LEVEL_STATEMENT, 'MO_GLOBAL.check_valid_org(' || g_org_id || '): ' || l_org_status);
115
116 /* getting system options */
117 select DAYS_TOGL_AFTER_DUE_DATE
118 into g_day_togl_after_dd
119 FROM LNS_SYSTEM_OPTIONS
120 WHERE ORG_ID = g_org_id;
121
122 LogMessage(FND_LOG.LEVEL_STATEMENT, 'System options:');
123 LogMessage(FND_LOG.LEVEL_STATEMENT, 'day_togl_after_dd: ' || g_day_togl_after_dd);
124
125 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
126
127 END;
128
129
130
131 /*========================================================================
132 | PRIVATE PROCEDURE BUILD_RC_APPL_REPORT
133 |
134 | DESCRIPTION
135 | This procedure builds receipts applications report.
136 |
137 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
138 |
139 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
140 | LogMessage
141 |
142 | PARAMETERS
143 | P_SEARCH_REC IN Search record
144 |
145 | KNOWN ISSUES
146 | None
147 |
148 | NOTES
149 | Any interesting aspect of the code in the package body which needs
150 | to be stated.
151 |
152 | MODIFICATION HISTORY
153 | Date Author Description of Changes
154 | 11-02-2005 scherkas Created
155 |
156 *=======================================================================*/
157 PROCEDURE BUILD_RC_APPL_REPORT(
158 P_SEARCH_REC IN LNS_APPL_ENGINE_PUB.SEARCH_REC)
159 IS
160
161 /*-----------------------------------------------------------------------+
162 | Local Variable Declarations and initializations |
163 +-----------------------------------------------------------------------*/
164
165 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_RC_APPL_REPORT';
166 l_new_line varchar2(1);
167 l_header varchar2(1000);
168 l_footer varchar2(100);
169 l_parameters varchar2(1000);
170 l_borrower VARCHAR2(360);
171 l_loan VARCHAR2(60);
172 l_org_name VARCHAR2(240);
173 l_product VARCHAR2(80);
174 l_unapply_flag VARCHAR2(80);
175 l_receipt VARCHAR2(30);
176 /*-----------------------------------------------------------------------+
177 | Cursor Declarations |
178 +-----------------------------------------------------------------------*/
179
180 /* query for borrower name */
181 CURSOR borrower_cur(P_BORROWER_ID number) IS
182 select party_name from hz_parties party where party_id = P_BORROWER_ID;
183
184 /* query for product name */
185 CURSOR product_cur(P_PRODUCT_ID number) IS
186 select LOAN_PRODUCT_NAME from lns_loan_products_vl where loan_product_id = P_PRODUCT_ID;
187
188 /* query for loan number */
189 CURSOR loan_cur(P_LOAN_ID number) IS
190 select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;
191
192 /* query for flag meaning */
193 CURSOR unapply_flag_cur(P_UNAPPLY_FLAG varchar2) IS
194 select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = P_UNAPPLY_FLAG;
195
196 /* query for flag meaning */
197 CURSOR receipt_cur(P_RECEIPT_ID number) IS
198 select receipt_number from ar_cash_receipts where cash_receipt_id = P_RECEIPT_ID;
199
200 /* query for org name */
201 CURSOR org_cur(P_ORG_ID number) IS
202 select name
203 from hr_all_organization_units_tl
204 where ORGANIZATION_ID = P_ORG_ID and
205 language(+) = userenv('LANG');
206
207 BEGIN
208
209 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
210 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
211
212 /* init report clob */
213 dbms_lob.createtemporary(g_last_rc_appl_report, FALSE, DBMS_LOB.CALL);
214 dbms_lob.open(g_last_rc_appl_report, dbms_lob.lob_readwrite);
215
216 l_new_line := '
217 ';
218 l_header := '<?xml version="1.0" encoding="UTF-8"?>' || l_new_line || '<RCAPPLBATCH>';
219 l_footer := l_new_line || '</LOANSET>' || l_new_line || '</RCAPPLBATCH>' || l_new_line;
220 l_parameters := l_new_line || '<PARAMETERS>';
221
222 /* adding org name to parameter list */
223 open org_cur(g_org_id);
224 fetch org_cur into l_org_name;
225 close org_cur;
226 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_org_name: ' || l_org_name);
227 l_parameters := l_parameters || l_new_line || '<ORG_NAME>' || l_org_name || '</ORG_NAME>';
228
229 /* adding borrower to parameter list */
230 if P_SEARCH_REC.LOAN_BORROWER_ID is not null then
231 open borrower_cur(P_SEARCH_REC.LOAN_BORROWER_ID);
232 fetch borrower_cur into l_borrower;
233 close borrower_cur;
234 end if;
235 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_borrower: ' || l_borrower);
236 l_parameters := l_parameters || l_new_line || '<BORROWER_NAME>' || l_borrower || '</BORROWER_NAME>';
237
238 /* adding loan product to parameter list */
239 if P_SEARCH_REC.LOAN_PRODUCT_ID is not null then
240 open product_cur(P_SEARCH_REC.LOAN_PRODUCT_ID);
241 fetch product_cur into l_product;
242 close product_cur;
243 end if;
244 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_product: ' || l_product);
245 l_parameters := l_parameters || l_new_line || '<PRODUCT_NAME>' || l_product || '</PRODUCT_NAME>';
246
247 /* adding loan to parameter list */
248 if P_SEARCH_REC.LOAN_ID is not null then
249 open loan_cur(P_SEARCH_REC.LOAN_ID);
250 fetch loan_cur into l_loan;
251 close loan_cur;
252 end if;
253 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan: ' || l_loan);
254 l_parameters := l_parameters || l_new_line || '<LOAN_NUMBER>' || l_loan || '</LOAN_NUMBER>';
255
256 /* adding receipt to parameter list */
257 if P_SEARCH_REC.RECEIPT_ID is not null then
258 open receipt_cur(P_SEARCH_REC.RECEIPT_ID);
259 fetch receipt_cur into l_receipt;
260 close receipt_cur;
261 end if;
262 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_receipt: ' || l_receipt);
263 l_parameters := l_parameters || l_new_line || '<RECEIPT_NUMBER>' || l_receipt || '</RECEIPT_NUMBER>';
264
265 /* adding from receipt date to parameter list */
266 l_parameters := l_parameters || l_new_line || '<RC_FROM_DATE>' || P_SEARCH_REC.RC_FROM_DATE || '</RC_FROM_DATE>';
267
268 /* adding to receipt date to parameter list */
269 l_parameters := l_parameters || l_new_line || '<RC_TO_DATE>' || P_SEARCH_REC.RC_TO_DATE || '</RC_TO_DATE>';
270
271 /* adding unapply flag to parameter list */
272 if P_SEARCH_REC.UNAPPLY_FLAG is not null then
273 open unapply_flag_cur(P_SEARCH_REC.UNAPPLY_FLAG);
274 fetch unapply_flag_cur into l_unapply_flag;
275 close unapply_flag_cur;
276 end if;
277 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_unapply_flag: ' || l_unapply_flag);
278 l_parameters := l_parameters || l_new_line || '<UNAPPLY_FLAG>' || l_unapply_flag || '</UNAPPLY_FLAG>';
279
280 l_parameters := l_parameters || l_new_line || '</PARAMETERS>' || l_new_line || '<LOANSET>' || l_new_line;
281 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_parameters: ' || l_parameters);
282
283 /* add header to report */
284 DBMS_LOB.write(g_last_rc_appl_report, length(l_header), 1, l_header);
285 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added header to report');
286
287 /* add parameters to report */
288 dbms_lob.writeAppend(g_last_rc_appl_report, length(l_parameters), l_parameters);
289 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added parameters to report');
290
291 /* add all statements to report */
292 if dbms_lob.getlength(g_last_all_appl_stmt) > 0 then
293 DBMS_LOB.Append(g_last_rc_appl_report, g_last_all_appl_stmt);
294 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added statements to report');
295 end if;
296
297 /* add footer to report */
298 dbms_lob.writeAppend(g_last_rc_appl_report, length(l_footer), l_footer);
299 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added footer to report');
300
301 /* print report to output file */
302 LNS_REP_UTILS.PRINT_CLOB(g_last_rc_appl_report);
303 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Saved output file');
304
305 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate receipts application report');
310 END;
311
312
313
314 /*========================================================================
315 | PRIVATE PROCEDURE ADD_LOAN_TAG
316 |
317 | DESCRIPTION
318 | This procedure adds loan info to statement.
319 |
320 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
321 |
322 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
323 | LogMessage
324 |
325 | PARAMETERS
326 | P_LOAN_REC IN Loan record
327 | P_OPEN_CLOSE_FLAG IN Open or close flag
328 |
329 | KNOWN ISSUES
330 | None
331 |
332 | NOTES
333 | Any interesting aspect of the code in the package body which needs
334 | to be stated.
335 |
336 | MODIFICATION HISTORY
337 | Date Author Description of Changes
338 | 11-02-2005 scherkas Created
339 |
340 *=======================================================================*/
341 PROCEDURE ADD_LOAN_TAG(
342 P_LOAN_REC IN LNS_APPL_ENGINE_PUB.LOAN_REC,
343 P_OPEN_CLOSE_FLAG IN VARCHAR2)
344 IS
345
346 /*-----------------------------------------------------------------------+
347 | Local Variable Declarations and initializations |
348 +-----------------------------------------------------------------------*/
349
350 l_api_name CONSTANT VARCHAR2(30) := 'ADD_LOAN_TAG';
351 l_new_line varchar2(1);
352 l_data varchar2(1000);
353 l_statement_xml clob;
354
355 /*-----------------------------------------------------------------------+
356 | Cursor Declarations |
357 +-----------------------------------------------------------------------*/
358
359 BEGIN
360
361 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
362 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
363
364 /* init statement clob */
365 dbms_lob.createtemporary(l_statement_xml, FALSE, DBMS_LOB.CALL);
366 dbms_lob.open(l_statement_xml, dbms_lob.lob_readwrite);
367
368 l_new_line := '
369 ';
370 if P_OPEN_CLOSE_FLAG = 'OPEN' then
371 l_data := l_new_line || '<LOAN num="' || P_LOAN_REC.SEQUENCE_NUMBER || '">' ||
372 l_new_line || '<LOAN_ID>' || P_LOAN_REC.LOAN_ID || '</LOAN_ID>' ||
373 l_new_line || '<LOAN_NUMBER>' || P_LOAN_REC.LOAN_NUMBER || '</LOAN_NUMBER>' ||
374 l_new_line || '<LOAN_BORROWER_ID>' || P_LOAN_REC.LOAN_BORROWER_ID || '</LOAN_BORROWER_ID>' ||
375 l_new_line || '<BORROWER_NAME>' || P_LOAN_REC.BORROWER_NAME || '</BORROWER_NAME>' ||
376 l_new_line || '<LOAN_PRODUCT_ID>' || P_LOAN_REC.LOAN_PRODUCT_ID || '</LOAN_PRODUCT_ID>' ||
377 l_new_line || '<PRODUCT_NAME>' || P_LOAN_REC.PRODUCT_NAME || '</PRODUCT_NAME>' ||
378 l_new_line || '<LOAN_CURRENCY>' || P_LOAN_REC.LOAN_CURRENCY || '</LOAN_CURRENCY>';
379 elsif P_OPEN_CLOSE_FLAG = 'CLOSE' then
380 l_data := l_new_line || '</LOAN>';
381 end if;
382
383 /* add header to stmt */
384 DBMS_LOB.write(l_statement_xml, length(l_data), 1, l_data);
385 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added loan to stmt');
386
387 /* add statements to all statement */
388 if dbms_lob.getlength(l_statement_xml) > 0 then
389 DBMS_LOB.Append(g_last_all_appl_stmt, l_statement_xml);
390 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added stmt to all statement');
391 end if;
392
393 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to add loan to all statement');
398 RAISE FND_API.G_EXC_ERROR;
399 END;
400
401
402
403 /*========================================================================
404 | PRIVATE PROCEDURE ADD_APPL_UNAPPL_TAGS
405 |
406 | DESCRIPTION
407 | This procedure adds apply unapply group tags.
408 |
409 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
410 |
411 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
412 | LogMessage
413 |
414 | PARAMETERS
415 | P_ACTION IN Action
416 | P_OPEN_CLOSE_FLAG IN Open or close flag
417 |
418 | KNOWN ISSUES
419 | None
420 |
421 | NOTES
422 | Any interesting aspect of the code in the package body which needs
423 | to be stated.
424 |
425 | MODIFICATION HISTORY
426 | Date Author Description of Changes
427 | 11-02-2005 scherkas Created
428 |
429 *=======================================================================*/
430 PROCEDURE ADD_APPL_UNAPPL_TAGS(
431 P_ACTION_FLAG IN VARCHAR2,
432 P_OPEN_CLOSE_FLAG IN VARCHAR2)
433 IS
434
435 /*-----------------------------------------------------------------------+
436 | Local Variable Declarations and initializations |
437 +-----------------------------------------------------------------------*/
438
439 l_api_name CONSTANT VARCHAR2(30) := 'ADD_APPL_UNAPPL_TAGS';
440 l_new_line varchar2(1);
441 l_data varchar2(1000);
442 l_statement_xml clob;
443
444 /*-----------------------------------------------------------------------+
445 | Cursor Declarations |
446 +-----------------------------------------------------------------------*/
447
448 BEGIN
449
450 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
451 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
452
453 /* init statement clob */
454 dbms_lob.createtemporary(l_statement_xml, FALSE, DBMS_LOB.CALL);
455 dbms_lob.open(l_statement_xml, dbms_lob.lob_readwrite);
456
457 l_new_line := '
458 ';
459 if P_ACTION_FLAG = 'APPLY' then
460 if P_OPEN_CLOSE_FLAG = 'OPEN' then
461 l_data := l_new_line || '<APPLICATIONS>';
462 elsif P_OPEN_CLOSE_FLAG = 'CLOSE' then
463 l_data := l_new_line || '</APPLICATIONS>';
464 end if;
465 elsif P_ACTION_FLAG = 'UNAPPLY' then
466 if P_OPEN_CLOSE_FLAG = 'OPEN' then
467 l_data := l_new_line || '<UNAPPLICATIONS>';
468 elsif P_OPEN_CLOSE_FLAG = 'CLOSE' then
469 l_data := l_new_line || '</UNAPPLICATIONS>';
470 end if;
471 end if;
472
473 /* add header to stmt */
474 DBMS_LOB.write(l_statement_xml, length(l_data), 1, l_data);
475 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added stmt');
476
477 /* add statements to all statement */
478 if dbms_lob.getlength(l_statement_xml) > 0 then
479 DBMS_LOB.Append(g_last_all_appl_stmt, l_statement_xml);
480 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added stmt to all statement');
481 end if;
482
483 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to add apply/unapply group tags loan to all statement');
488 RAISE FND_API.G_EXC_ERROR;
489 END;
490
491
492
493 /*========================================================================
494 | PRIVATE PROCEDURE BUILD_STMT
495 |
496 | DESCRIPTION
497 | This procedure builds single apply/unapply statement and add this to final all statement.
498 |
499 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
500 |
501 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
502 | LogMessage
503 |
504 | PARAMETERS
505 | P_LOAN_INVOICES_REC IN Invoice record
506 | P_RECEIPT_REC IN Receipt record
507 |
508 | KNOWN ISSUES
509 | None
510 |
511 | NOTES
512 | Any interesting aspect of the code in the package body which needs
513 | to be stated.
514 |
515 | MODIFICATION HISTORY
516 | Date Author Description of Changes
517 | 11-02-2005 scherkas Created
518 |
519 *=======================================================================*/
520 PROCEDURE BUILD_STMT(
521 P_LOAN_INVOICES_REC IN LNS_APPL_ENGINE_PUB.LOAN_INVOICE_REC,
522 P_RECEIPT_REC IN LNS_APPL_ENGINE_PUB.RECEIPT_REC,
523 P_SEQUENCE_NUMBER IN NUMBER,
524 P_INV_APPLY_AMOUNT IN NUMBER,
525 P_RC_APPLY_AMOUNT IN NUMBER,
526 P_ERROR IN VARCHAR2,
527 P_ACTION IN VARCHAR2)
528 IS
529
530 /*-----------------------------------------------------------------------+
531 | Local Variable Declarations and initializations |
532 +-----------------------------------------------------------------------*/
533
534 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_STMT';
535 l_new_line varchar2(1);
536 l_header varchar2(1000);
537 l_footer varchar2(100);
538 l_statement_xml clob;
539 l_application varchar2(2000);
540
541 /*-----------------------------------------------------------------------+
542 | Cursor Declarations |
543 +-----------------------------------------------------------------------*/
544
545 BEGIN
546
547 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
548 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
549
550 /* init statement clob */
551 dbms_lob.createtemporary(l_statement_xml, FALSE, DBMS_LOB.CALL);
552 dbms_lob.open(l_statement_xml, dbms_lob.lob_readwrite);
553
554 l_new_line := '
555 ';
556 if P_ACTION = 'APPLY' then
557 l_header := l_new_line || '<APPLICATION num="' || P_SEQUENCE_NUMBER || '">';
558 l_footer := l_new_line || '</APPLICATION>' || l_new_line;
559 elsif P_ACTION = 'UNAPPLY' then
560 l_header := l_new_line || '<UNAPPLICATION num="' || P_SEQUENCE_NUMBER || '">';
561 l_footer := l_new_line || '</UNAPPLICATION>' || l_new_line;
562 end if;
563 -- LogMessage(FND_LOG.LEVEL_STATEMENT, 'Step1');
564
565 l_application := l_application || l_new_line || '<LOAN_NUMBER>' || P_LOAN_INVOICES_REC.LOAN_NUMBER ||'</LOAN_NUMBER>' ||
566 l_new_line || '<PHASE>' || P_LOAN_INVOICES_REC.PHASE ||'</PHASE>' ||
567 l_new_line || '<PAYMENT_NUMBER>' || P_LOAN_INVOICES_REC.PAYMENT_NUMBER ||'</PAYMENT_NUMBER>' ||
568 l_new_line || '<INVOICE_TYPE_CODE>' || P_LOAN_INVOICES_REC.INVOICE_TYPE_CODE ||'</INVOICE_TYPE_CODE>' ||
569 l_new_line || '<INVOICE_TYPE_DESC>' || P_LOAN_INVOICES_REC.INVOICE_TYPE_DESC ||'</INVOICE_TYPE_DESC>' ||
570 l_new_line || '<CUST_TRX_ID>' || P_LOAN_INVOICES_REC.CUST_TRX_ID || '</CUST_TRX_ID>' ||
571 l_new_line || '<PAYMENT_SCHEDULE_ID>' || P_LOAN_INVOICES_REC.PAYMENT_SCHEDULE_ID || '</PAYMENT_SCHEDULE_ID>' ||
572 l_new_line || '<TRX_NUMBER>' || P_LOAN_INVOICES_REC.TRX_NUMBER || '</TRX_NUMBER>' ||
573 l_new_line || '<LOAN_BORROWER_ID>' || P_LOAN_INVOICES_REC.LOAN_BORROWER_ID ||'</LOAN_BORROWER_ID>' ||
574 l_new_line || '<BORROWER_NAME>' || P_LOAN_INVOICES_REC.BORROWER_NAME ||'</BORROWER_NAME>' ||
575 l_new_line || '<LOAN_PRODUCT_ID>' || P_LOAN_INVOICES_REC.LOAN_PRODUCT_ID ||'</LOAN_PRODUCT_ID>' ||
576 l_new_line || '<PRODUCT_NAME>' || P_LOAN_INVOICES_REC.PRODUCT_NAME ||'</PRODUCT_NAME>' ||
577 l_new_line || '<LOAN_ID>' || P_LOAN_INVOICES_REC.LOAN_ID ||'</LOAN_ID>' ||
578 l_new_line || '<AMORTIZATION_ID>' || P_LOAN_INVOICES_REC.AMORTIZATION_ID ||'</AMORTIZATION_ID>' ||
579 l_new_line || '<DUE_DATE>' || fnd_date.date_to_chardate(P_LOAN_INVOICES_REC.DUE_DATE) ||'</DUE_DATE>' ||
580 l_new_line || '<BILL_DATE>' || fnd_date.date_to_chardate(P_LOAN_INVOICES_REC.BILL_DATE) ||'</BILL_DATE>' ||
581 l_new_line || '<INVOICE_CURRENCY>' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY ||'</INVOICE_CURRENCY>' ||
582 l_new_line || '<EXCHANGE_RATE>' || P_LOAN_INVOICES_REC.EXCHANGE_RATE ||'</EXCHANGE_RATE>' ||
583 l_new_line || '<RECEIPT_ID>' || P_RECEIPT_REC.RECEIPT_ID ||'</RECEIPT_ID>' ||
584 l_new_line || '<RECEIPT_NUMBER>' || P_RECEIPT_REC.RECEIPT_NUMBER ||'</RECEIPT_NUMBER>' ||
585 l_new_line || '<RECEIPT_DATE>' || fnd_date.date_to_chardate(P_RECEIPT_REC.RECEIPT_DATE) ||'</RECEIPT_DATE>' ||
586 l_new_line || '<PARTY_NAME>' || P_RECEIPT_REC.PARTY_NAME ||'</PARTY_NAME>' ||
587 l_new_line || '<RECEIPT_CURRENCY>' || P_RECEIPT_REC.RECEIPT_CURRENCY || '</RECEIPT_CURRENCY>';
588
589 -- LogMessage(FND_LOG.LEVEL_STATEMENT, 'Step2');
590 if P_ACTION = 'APPLY' then
591 l_application := l_application || l_new_line || '<INV_APPLY_AMOUNT>' || to_char(P_INV_APPLY_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</INV_APPLY_AMOUNT>' ||
592 l_new_line || '<RC_APPLY_AMOUNT>' || to_char(P_RC_APPLY_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</RC_APPLY_AMOUNT>' ||
593 l_new_line || '<BEGIN_INV_BALANCE>' || to_char(P_LOAN_INVOICES_REC.REMAINING_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</BEGIN_INV_BALANCE>' ||
594 l_new_line || '<END_INV_BALANCE>' || to_char((P_LOAN_INVOICES_REC.REMAINING_AMOUNT - P_INV_APPLY_AMOUNT), FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</END_INV_BALANCE>' ||
595 l_new_line || '<BEGIN_RC_BALANCE>' || to_char(P_RECEIPT_REC.REMAINING_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</BEGIN_RC_BALANCE>' ||
596 l_new_line || '<END_RC_BALANCE>' || to_char((P_RECEIPT_REC.REMAINING_AMOUNT - P_RC_APPLY_AMOUNT), FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</END_RC_BALANCE>';
597
598 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
599 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Application #' || P_SEQUENCE_NUMBER);
600 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt: ' || P_RECEIPT_REC.RECEIPT_NUMBER || '(id ' || P_RECEIPT_REC.RECEIPT_ID || ')');
601 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt date: ' || P_RECEIPT_REC.RECEIPT_DATE);
602 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt balance before application: ' || P_RECEIPT_REC.REMAINING_AMOUNT || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
603 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt balance after application: ' || (P_RECEIPT_REC.REMAINING_AMOUNT - P_RC_APPLY_AMOUNT) || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
604 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice ' || P_LOAN_INVOICES_REC.TRX_NUMBER || '(id ' || P_LOAN_INVOICES_REC.CUST_TRX_ID || ')');
605 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Installment # ' || P_LOAN_INVOICES_REC.PAYMENT_NUMBER || ' ' || P_LOAN_INVOICES_REC.INVOICE_TYPE_DESC);
606 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Phase: ' || P_LOAN_INVOICES_REC.PHASE);
607 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Due date: ' || P_LOAN_INVOICES_REC.DUE_DATE);
608 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice balance before application: ' || P_LOAN_INVOICES_REC.REMAINING_AMOUNT || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
609 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice balance after application: ' || (P_LOAN_INVOICES_REC.REMAINING_AMOUNT - P_INV_APPLY_AMOUNT) || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
610 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Applied amount in invoice currency: ' || P_INV_APPLY_AMOUNT || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
611 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Applied amount in receipt currency: ' || P_RC_APPLY_AMOUNT || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
612
613 elsif P_ACTION = 'UNAPPLY' then
614 l_application := l_application || l_new_line || '<INV_UNAPPLY_AMOUNT>' || to_char(P_INV_APPLY_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</INV_UNAPPLY_AMOUNT>' ||
615 l_new_line || '<RC_UNAPPLY_AMOUNT>' || to_char(P_RC_APPLY_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</RC_UNAPPLY_AMOUNT>' ||
616 l_new_line || '<BEGIN_INV_BALANCE>' || to_char(P_LOAN_INVOICES_REC.REMAINING_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</BEGIN_INV_BALANCE>' ||
617 l_new_line || '<END_INV_BALANCE>' || to_char((P_LOAN_INVOICES_REC.REMAINING_AMOUNT + P_INV_APPLY_AMOUNT), FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_LOAN_INVOICES_REC.INVOICE_CURRENCY,50)) || '</END_INV_BALANCE>' ||
618 l_new_line || '<BEGIN_RC_BALANCE>' || to_char(P_RECEIPT_REC.REMAINING_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</BEGIN_RC_BALANCE>' ||
619 l_new_line || '<END_RC_BALANCE>' || to_char((P_RECEIPT_REC.REMAINING_AMOUNT + P_RC_APPLY_AMOUNT), FND_CURRENCY.SAFE_GET_FORMAT_MASK(P_RECEIPT_REC.RECEIPT_CURRENCY,50)) || '</END_RC_BALANCE>';
620
621 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
622 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Unapplication #' || P_SEQUENCE_NUMBER);
623 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt: ' || P_RECEIPT_REC.RECEIPT_NUMBER || '(id ' || P_RECEIPT_REC.RECEIPT_ID || ')');
624 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt date: ' || P_RECEIPT_REC.RECEIPT_DATE);
625 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt balance before application: ' || P_RECEIPT_REC.REMAINING_AMOUNT || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
626 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt balance after application: ' || (P_RECEIPT_REC.REMAINING_AMOUNT + P_RC_APPLY_AMOUNT) || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
627 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice ' || P_LOAN_INVOICES_REC.TRX_NUMBER || '(id ' || P_LOAN_INVOICES_REC.CUST_TRX_ID || ')');
628 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Installment # ' || P_LOAN_INVOICES_REC.PAYMENT_NUMBER || ' ' || P_LOAN_INVOICES_REC.INVOICE_TYPE_DESC);
629 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Phase: ' || P_LOAN_INVOICES_REC.PHASE);
630 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Due date: ' || P_LOAN_INVOICES_REC.DUE_DATE);
631 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice balance before application: ' || P_LOAN_INVOICES_REC.REMAINING_AMOUNT || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
632 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Invoice balance after application: ' || (P_LOAN_INVOICES_REC.REMAINING_AMOUNT + P_INV_APPLY_AMOUNT) || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
633 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Applied amount in invoice currency: ' || P_INV_APPLY_AMOUNT || ' ' || P_LOAN_INVOICES_REC.INVOICE_CURRENCY);
634 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Applied amount in receipt currency: ' || P_RC_APPLY_AMOUNT || ' ' || P_RECEIPT_REC.RECEIPT_CURRENCY);
635
636 end if;
637
638 -- LogMessage(FND_LOG.LEVEL_STATEMENT, 'Step3');
639 l_application := l_application || l_new_line || '<ERROR>' || P_ERROR ||'</ERROR>';
640 -- LogMessage(FND_LOG.LEVEL_STATEMENT, 'Step4');
641
642 /* add header to stmt */
643 DBMS_LOB.write(l_statement_xml, length(l_header), 1, l_header);
644 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added header to stmt');
645
646 /* add data to stmt */
647 dbms_lob.writeAppend(l_statement_xml, length(l_application), l_application);
648 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added data to stmt');
649
650 /* add footer to stmt */
651 dbms_lob.writeAppend(l_statement_xml, length(l_footer), l_footer);
652 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added footer to stmt');
653
654 /* add statements to all statement */
655 if dbms_lob.getlength(l_statement_xml) > 0 then
656 DBMS_LOB.Append(g_last_all_appl_stmt, l_statement_xml);
657 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added statement to all statement');
658 end if;
659
660 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate application statement');
665 RAISE FND_API.G_EXC_ERROR;
666 END;
667
668
669
670
671 /*========================================================================
672 | PRIVATE PROCEDURE UNAPPLY_RECEIPTS
673 |
674 | DESCRIPTION
675 | This procedure unapplies incorrect applications from loan invoices
676 |
677 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
678 |
679 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
680 | LogMessage
681 |
682 | PARAMETERS
683 | P_RECEIPTS_TBL OUT NOCOPY Receipts table
684 |
685 | KNOWN ISSUES
686 | None
687 |
688 | NOTES
689 | Any interesting aspect of the code in the package body which needs
690 | to be stated.
691 |
692 | MODIFICATION HISTORY
693 | Date Author Description of Changes
694 | 25-10-2005 scherkas Created
695 |
696 *=======================================================================*/
697 PROCEDURE UNAPPLY_RECEIPTS(
698 P_LOAN_INVOICES_TBL IN LNS_APPL_ENGINE_PUB.LOAN_INVOICES_TBL)
699 IS
700
701 /*-----------------------------------------------------------------------+
702 | Local Variable Declarations and initializations |
703 +-----------------------------------------------------------------------*/
704
705 l_api_name CONSTANT VARCHAR2(30) := 'UNAPPLY_RECEIPTS';
706 l_Count NUMBER;
707 l_next_inv_appl_ok varchar2(1);
708 l_Count1 number;
709 l_return_status VARCHAR2(1);
710 l_msg_count NUMBER;
711 l_msg_data VARCHAR2(32767);
712 l_amount_applied number;
713 l_amount_applied_from number;
714 l_receivable_application_id number;
715 l_application number;
716 l_inv_count number;
717 l_error varchar2(32767);
718
719 l_RECEIPT_REC LNS_APPL_ENGINE_PUB.RECEIPT_REC;
720 l_RECEIPTS_TBL LNS_APPL_ENGINE_PUB.RECEIPTS_TBL;
721
722 /*-----------------------------------------------------------------------+
723 | Cursor Declarations |
724 +-----------------------------------------------------------------------*/
725
726 -- search for receipts to unapply
727 CURSOR receipts_cur(P_PAYMENT_SCHEDULE_ID number) IS
728 select
729 ra.RECEIVABLE_APPLICATION_ID,
730 psa.CASH_RECEIPT_ID,
731 psa.trx_number,
732 psa.trx_date,
733 abs(psa.amount_due_remaining),
734 psa.invoice_currency_code,
735 party.party_name,
736 ra.AMOUNT_APPLIED,
737 nvl(ra.amount_applied_from, ra.AMOUNT_APPLIED)
738 from ar_receivable_applications ra,
739 ar_payment_schedules psa,
740 hz_cust_accounts cust,
741 hz_parties party
742 where
743 ra.APPLIED_PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID and
744 ra.application_type = 'CASH' and
745 ra.display = 'Y' and
746 ra.cash_receipt_id = psa.cash_receipt_id and
747 psa.class = 'PMT' and
748 psa.customer_id = cust.cust_account_id and
749 cust.status = 'A' and
750 cust.party_id = party.party_id
751 order by ra.APPLY_DATE desc;
752
753 BEGIN
754
755 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
756
757 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Unapplying receipts...');
758
759 l_Count1 := 0;
760 l_next_inv_appl_ok := 'Y';
761 l_application := 0;
762 l_inv_count := 0;
763
764 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
765 P_ACTION_FLAG => 'UNAPPLY',
766 P_OPEN_CLOSE_FLAG => 'OPEN');
767
768 -- looping thru invoices and apply receipts
769 FOR l_Count IN 1..P_LOAN_INVOICES_TBL.count LOOP
770
771 if l_next_inv_appl_ok = 'Y' then
772 if P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT = 0 then
773 l_next_inv_appl_ok := 'Y';
774 else
775 l_next_inv_appl_ok := 'N';
776 end if;
777 else
778
779 open receipts_cur(P_LOAN_INVOICES_TBL(l_Count).PAYMENT_SCHEDULE_ID);
780 LOOP
781
782 fetch receipts_cur into l_receivable_application_id,
783 l_RECEIPT_REC.RECEIPT_ID,
784 l_RECEIPT_REC.RECEIPT_NUMBER,
785 l_RECEIPT_REC.RECEIPT_DATE,
786 l_RECEIPT_REC.REMAINING_AMOUNT,
787 l_RECEIPT_REC.RECEIPT_CURRENCY,
788 l_RECEIPT_REC.PARTY_NAME,
789 l_amount_applied,
790 l_amount_applied_from;
791
792 exit when receipts_cur%NOTFOUND;
793
794 l_Count1 := l_Count1+1;
795
796 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
797 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Invoice to unapply #' || l_Count1);
798 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_SCHEDULE_ID: ' || P_LOAN_INVOICES_TBL(l_Count).PAYMENT_SCHEDULE_ID);
799 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUST_TRX_ID: ' || P_LOAN_INVOICES_TBL(l_Count).CUST_TRX_ID);
800 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TRX_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).TRX_NUMBER);
801 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_BORROWER_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_BORROWER_ID);
802 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || P_LOAN_INVOICES_TBL(l_Count).BORROWER_NAME);
803 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_PRODUCT_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_PRODUCT_ID);
804 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_NAME: ' || P_LOAN_INVOICES_TBL(l_Count).PRODUCT_NAME);
805 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_ID);
806 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_NUMBER);
807 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZATION_ID: ' || P_LOAN_INVOICES_TBL(l_Count).AMORTIZATION_ID);
808 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).PAYMENT_NUMBER);
809 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DUE_DATE: ' || P_LOAN_INVOICES_TBL(l_Count).DUE_DATE);
810 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BILL_DATE: ' || P_LOAN_INVOICES_TBL(l_Count).BILL_DATE);
811 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PHASE: ' || P_LOAN_INVOICES_TBL(l_Count).PHASE);
812 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_CODE: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_TYPE_CODE);
813 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_DESC: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_TYPE_DESC);
814 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT);
815 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_CURRENCY: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_CURRENCY);
816 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXCHANGE_RATE: ' || P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE);
817
818 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
819 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Receipt to unapply #' || l_Count1);
820 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_ID: ' || l_RECEIPT_REC.RECEIPT_ID);
821 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_NUMBER: ' || l_RECEIPT_REC.RECEIPT_NUMBER);
822 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_DATE: ' || l_RECEIPT_REC.RECEIPT_DATE);
823 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || l_RECEIPT_REC.REMAINING_AMOUNT);
824 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_CURRENCY: ' || l_RECEIPT_REC.RECEIPT_CURRENCY);
825 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTY_NAME: ' || l_RECEIPT_REC.PARTY_NAME);
826 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_receivable_application_id: ' || l_receivable_application_id);
827 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_amount_applied: ' || l_amount_applied);
828 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_amount_applied_from: ' || l_amount_applied_from);
829
830 l_application := l_application + 1;
831
832 BEGIN
833
834 l_error := null;
835 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling ar_receipt_api_pub.unapply...');
836 ar_receipt_api_pub.unapply(
837 p_api_version => 1.0,
838 p_init_msg_list => FND_API.G_TRUE,
839 p_commit => FND_API.G_FALSE,
840 p_receivable_application_id => l_receivable_application_id,
841 x_return_status => l_return_status,
842 x_msg_count => l_msg_count,
843 x_msg_data => l_msg_data);
844
845 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
846 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_msg_data: ' || l_msg_data);
847
848 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
849 l_error := FND_MSG_PUB.Get(p_encoded => 'F');
850 end if;
851
852 EXCEPTION
853 WHEN OTHERS THEN
854 l_return_status := FND_API.G_RET_STS_ERROR;
855 l_error := SQLERRM;
856 END;
857
858 LNS_APPL_ENGINE_PUB.BUILD_STMT(
859 P_LOAN_INVOICES_REC => P_LOAN_INVOICES_TBL(l_Count),
860 P_RECEIPT_REC => l_RECEIPT_REC,
861 P_SEQUENCE_NUMBER => l_application,
862 P_INV_APPLY_AMOUNT => l_amount_applied,
863 P_RC_APPLY_AMOUNT => l_amount_applied_from,
864 P_ERROR => l_error,
865 P_ACTION => 'UNAPPLY');
866
867 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
868 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ar_receipt_api_pub.unapply failed');
869 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Reported error: ' || l_error);
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872
873 l_inv_count := l_inv_count + 1;
874 l_next_inv_appl_ok := 'N';
875
876 END LOOP;
877 close receipts_cur;
878
879 end if;
880
881 END LOOP;
882
883 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
884 P_ACTION_FLAG => 'UNAPPLY',
885 P_OPEN_CLOSE_FLAG => 'CLOSE');
886
887 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
888 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully unapplied ' || l_Count1 || ' receipts from ' || l_inv_count || ' invoices for loan ' || P_LOAN_INVOICES_TBL(l_inv_count).LOAN_NUMBER);
889
890 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In ' || l_api_name || ' exception handling');
895
896 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
897 P_ACTION_FLAG => 'UNAPPLY',
898 P_OPEN_CLOSE_FLAG => 'CLOSE');
899
900 RAISE FND_API.G_EXC_ERROR;
901 END;
902
903
904
905 /*========================================================================
906 | PRIVATE PROCEDURE APPLY_RECEIPTS
907 |
908 | DESCRIPTION
909 | This procedure applies receipts to loan invoices
910 |
911 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
912 |
913 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
914 | LogMessage
915 |
916 | PARAMETERS
917 | P_RECEIPTS_TBL OUT NOCOPY Receipts table
918 |
919 | KNOWN ISSUES
920 | None
921 |
922 | NOTES
923 | Any interesting aspect of the code in the package body which needs
924 | to be stated.
925 |
926 | MODIFICATION HISTORY
927 | Date Author Description of Changes
928 | 25-10-2005 scherkas Created
929 |
930 *=======================================================================*/
931 PROCEDURE APPLY_RECEIPTS(
932 P_LOAN_INVOICES_TBL IN OUT NOCOPY LNS_APPL_ENGINE_PUB.LOAN_INVOICES_TBL,
933 P_RECEIPTS_TBL IN OUT NOCOPY LNS_APPL_ENGINE_PUB.RECEIPTS_TBL)
934 IS
935
936 /*-----------------------------------------------------------------------+
937 | Local Variable Declarations and initializations |
938 +-----------------------------------------------------------------------*/
939
940 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_RECEIPTS';
941 l_Count number;
942 l_Count1 number;
943 l_receipt number;
944 l_exit_loop varchar2(1);
945 l_quit varchar2(1);
946 l_apply_amount number;
947 l_application number;
948 l_return_status VARCHAR2(1);
949 l_msg_count NUMBER;
950 l_msg_data VARCHAR2(32767);
951 l_trans_to_receipt_rate number;
952 l_receipt_amount_from number; -- in receipt currency
953 l_rc_precision number;
954 l_rc_ext_precision number;
955 l_rc_min_acct_unit number;
956 l_inv_precision number;
957 l_inv_ext_precision number;
958 l_inv_min_acct_unit number;
959 l_apply_date date;
960 l_inv_am_in_funct_cur number;
961 l_inv_count number;
962 l_rc_count number;
963 l_error varchar2(32767);
964
965
966 /*-----------------------------------------------------------------------+
967 | Cursor Declarations |
968 +-----------------------------------------------------------------------*/
969
970 BEGIN
971
972 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
973
974 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Applying receipts...');
975
976 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
977 P_ACTION_FLAG => 'APPLY',
978 P_OPEN_CLOSE_FLAG => 'OPEN');
979
980 l_receipt := 1;
981 l_quit := 'N';
982 l_application := 0;
983 l_inv_count := 0;
984 l_rc_count := 0;
985 -- looping thru invoices and apply receipts
986 FOR l_Count IN 1..P_LOAN_INVOICES_TBL.count LOOP
987
988 l_inv_count := l_inv_count + 1;
989 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
990 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Invoice #' || l_Count);
991 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_SCHEDULE_ID: ' || P_LOAN_INVOICES_TBL(l_Count).PAYMENT_SCHEDULE_ID);
992 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUST_TRX_ID: ' || P_LOAN_INVOICES_TBL(l_Count).CUST_TRX_ID);
993 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TRX_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).TRX_NUMBER);
994 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_BORROWER_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_BORROWER_ID);
995 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || P_LOAN_INVOICES_TBL(l_Count).BORROWER_NAME);
996 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_PRODUCT_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_PRODUCT_ID);
997 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_NAME: ' || P_LOAN_INVOICES_TBL(l_Count).PRODUCT_NAME);
998 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_ID);
999 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).LOAN_NUMBER);
1000 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZATION_ID: ' || P_LOAN_INVOICES_TBL(l_Count).AMORTIZATION_ID);
1001 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_NUMBER: ' || P_LOAN_INVOICES_TBL(l_Count).PAYMENT_NUMBER);
1002 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DUE_DATE: ' || P_LOAN_INVOICES_TBL(l_Count).DUE_DATE);
1003 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BILL_DATE: ' || P_LOAN_INVOICES_TBL(l_Count).BILL_DATE);
1004 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PHASE: ' || P_LOAN_INVOICES_TBL(l_Count).PHASE);
1005 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_CODE: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_TYPE_CODE);
1006 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_DESC: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_TYPE_DESC);
1007 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT);
1008 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_CURRENCY: ' || P_LOAN_INVOICES_TBL(l_Count).INVOICE_CURRENCY);
1009 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXCHANGE_RATE: ' || P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE);
1010
1011 FOR l_Count1 IN l_receipt..P_RECEIPTS_TBL.count LOOP
1012
1013 l_rc_count := l_receipt;
1014 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1015 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Receipt #' || l_Count1);
1016 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_ID: ' || P_RECEIPTS_TBL(l_Count1).RECEIPT_ID);
1017 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_NUMBER: ' || P_RECEIPTS_TBL(l_Count1).RECEIPT_NUMBER);
1018 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_DATE: ' || P_RECEIPTS_TBL(l_Count1).RECEIPT_DATE);
1019 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT);
1020 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_CURRENCY: ' || P_RECEIPTS_TBL(l_Count1).RECEIPT_CURRENCY);
1021 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTY_NAME: ' || P_RECEIPTS_TBL(l_Count1).PARTY_NAME);
1022
1023 BEGIN
1024
1025 -- calculate amounts using curencies
1026 if P_RECEIPTS_TBL(l_Count1).RECEIPT_CURRENCY = P_LOAN_INVOICES_TBL(l_Count).INVOICE_CURRENCY then
1027
1028 if P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT > P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT then
1029
1030 l_apply_amount := P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT;
1031 l_exit_loop := 'N';
1032
1033 elsif P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT < P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT then
1034
1035 l_apply_amount := P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT;
1036 l_exit_loop := 'Y';
1037
1038 else
1039
1040 l_apply_amount := P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT;
1041 l_exit_loop := 'Y';
1042
1043 end if;
1044
1045 l_receipt_amount_from := null;
1046 l_trans_to_receipt_rate := null;
1047
1048 else -- if trx currency <> receipt currency then receipt in functional currency
1049
1050 -- get rc/functional currency precision
1051 fnd_currency.GET_INFO(CURRENCY_CODE => P_RECEIPTS_TBL(l_Count1).RECEIPT_CURRENCY,
1052 PRECISION => l_rc_precision,
1053 EXT_PRECISION => l_rc_ext_precision,
1054 MIN_ACCT_UNIT => l_rc_min_acct_unit);
1055
1056 -- get invoice currency precision
1057 fnd_currency.GET_INFO(CURRENCY_CODE => P_LOAN_INVOICES_TBL(l_Count).INVOICE_CURRENCY,
1058 PRECISION => l_inv_precision,
1059 EXT_PRECISION => l_inv_ext_precision,
1060 MIN_ACCT_UNIT => l_inv_min_acct_unit);
1061
1062
1063 l_inv_am_in_funct_cur := round(P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT * P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE, l_rc_precision);
1064
1065 if l_inv_am_in_funct_cur > P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT then
1066
1067 l_receipt_amount_from := P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT;
1068 l_apply_amount := round(l_receipt_amount_from / P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE, l_inv_precision);
1069 l_exit_loop := 'N';
1070
1071 elsif l_inv_am_in_funct_cur < P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT then
1072
1073 l_apply_amount := P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT;
1074 l_receipt_amount_from := round(l_apply_amount * P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE, l_rc_precision);
1075 l_exit_loop := 'Y';
1076
1077 else
1078
1079 l_apply_amount := P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT;
1080 l_receipt_amount_from := P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT;
1081 l_exit_loop := 'Y';
1082
1083 end if;
1084
1085 l_trans_to_receipt_rate := P_LOAN_INVOICES_TBL(l_Count).EXCHANGE_RATE;
1086
1087 end if;
1088
1089 if trunc(sysdate) > trunc(P_RECEIPTS_TBL(l_Count1).RECEIPT_DATE) and
1090 trunc(sysdate) > trunc(P_LOAN_INVOICES_TBL(l_Count).DUE_DATE) then
1091 l_apply_date := sysdate;
1092 elsif trunc(P_LOAN_INVOICES_TBL(l_Count).DUE_DATE) > trunc(P_RECEIPTS_TBL(l_Count1).RECEIPT_DATE) then
1093 l_apply_date := P_LOAN_INVOICES_TBL(l_Count).DUE_DATE;
1094 else
1095 l_apply_date := P_RECEIPTS_TBL(l_Count1).RECEIPT_DATE;
1096 end if;
1097
1098 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling AR_RECEIPT_API_PUB.APPLY with following parameters:');
1099 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_cash_receipt_id: ' || P_RECEIPTS_TBL(l_Count1).RECEIPT_ID);
1100 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_applied_payment_schedule_id: ' || P_LOAN_INVOICES_TBL(l_Count).PAYMENT_SCHEDULE_ID);
1101 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_apply_date: ' || l_apply_date);
1102 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_apply_gl_date: ' || (l_apply_date + nvl(g_day_togl_after_dd, 0)));
1103 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_amount_applied: ' || l_apply_amount);
1104 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_amount_applied_from: ' || l_receipt_amount_from);
1105 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_trans_to_receipt_rate: ' || l_trans_to_receipt_rate);
1106
1107 BEGIN
1108
1109 l_error := null;
1110 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling AR_RECEIPT_API_PUB.APPLY...');
1111 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1112 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1113 ,P_COMMIT => FND_API.G_FALSE
1114 ,X_RETURN_STATUS => l_return_status
1115 ,X_MSG_COUNT => l_msg_count
1116 ,X_MSG_DATA => l_msg_data
1117 ,p_cash_receipt_id => P_RECEIPTS_TBL(l_Count1).RECEIPT_ID
1118 ,p_applied_payment_schedule_id => P_LOAN_INVOICES_TBL(l_Count).PAYMENT_SCHEDULE_ID
1119 ,p_apply_date => l_apply_date
1120 ,p_apply_gl_date => l_apply_date + nvl(g_day_togl_after_dd, 0)
1121 ,p_amount_applied => l_apply_amount
1122 ,p_amount_applied_from => l_receipt_amount_from
1123 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate);
1124
1125 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
1126 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_msg_data: ' || l_msg_data);
1127
1128 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1129 l_error := FND_MSG_PUB.Get(p_encoded => 'F');
1130 END IF;
1131
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 l_return_status := FND_API.G_RET_STS_ERROR;
1135 l_error := SQLERRM;
1136 END;
1137
1138 if P_RECEIPTS_TBL(l_Count1).RECEIPT_CURRENCY = P_LOAN_INVOICES_TBL(l_Count).INVOICE_CURRENCY then
1139 l_receipt_amount_from := l_apply_amount;
1140 end if;
1141
1142 l_application := l_application + 1;
1143 LNS_APPL_ENGINE_PUB.BUILD_STMT(
1144 P_LOAN_INVOICES_REC => P_LOAN_INVOICES_TBL(l_Count),
1145 P_RECEIPT_REC => P_RECEIPTS_TBL(l_Count1),
1146 P_SEQUENCE_NUMBER => l_application,
1147 P_INV_APPLY_AMOUNT => l_apply_amount,
1148 P_RC_APPLY_AMOUNT => l_receipt_amount_from,
1149 P_ERROR => l_error,
1150 P_ACTION => 'APPLY');
1151
1152 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1153 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'AR_RECEIPT_API_PUB.APPLY failed');
1154 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Reported error: ' || l_error);
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157
1158 P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT := P_LOAN_INVOICES_TBL(l_Count).REMAINING_AMOUNT - l_apply_amount;
1159 P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT := P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT - l_receipt_amount_from;
1160
1161 if P_RECEIPTS_TBL(l_Count1).REMAINING_AMOUNT = 0 then
1162
1163 if l_receipt = P_RECEIPTS_TBL.count then
1164 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Last receipt has been exhausted - exiting receipts loop');
1165 l_quit := 'Y';
1166 exit;
1167 else
1168 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Receipt #' || l_receipt || ' has been exhausted - continue with next receipt');
1169 l_quit := 'N';
1170 l_receipt := l_receipt + 1;
1171 end if;
1172
1173 end if;
1174
1175 if l_exit_loop = 'Y' then
1176 exit;
1177 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Invoice #' || l_Count || ' has been fully paid - exiting receipts loop');
1178 end if;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 RAISE FND_API.G_EXC_ERROR;
1183 END;
1184
1185 END LOOP;
1186
1187 if l_quit = 'Y' then
1188 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Exiting invoices loop');
1189 exit;
1190 end if;
1191
1192 END LOOP;
1193
1194
1195 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
1196 P_ACTION_FLAG => 'APPLY',
1197 P_OPEN_CLOSE_FLAG => 'CLOSE');
1198
1199 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
1200 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully applied ' || l_rc_count || ' receipts to ' || l_inv_count || ' invoices for loan ' || P_LOAN_INVOICES_TBL(l_inv_count).LOAN_NUMBER);
1201
1202 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1203
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In ' || l_api_name || ' exception handling');
1207
1208 LNS_APPL_ENGINE_PUB.ADD_APPL_UNAPPL_TAGS(
1209 P_ACTION_FLAG => 'APPLY',
1210 P_OPEN_CLOSE_FLAG => 'CLOSE');
1211
1212 RAISE FND_API.G_EXC_ERROR;
1213 END;
1214
1215
1216
1217 /*========================================================================
1218 | PUBLIC PROCEDURE SEARCH_RECEIPTS
1219 |
1220 | DESCRIPTION
1221 | This procedure searches for receipts using passed search criteria record
1222 |
1223 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1224 |
1225 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1226 | LogMessage
1227 |
1228 | PARAMETERS
1229 | P_SEARCH_RECEIPTS_REC IN Search record
1230 | X_RECEIPTS_TBL OUT NOCOPY Receipts table
1231 |
1232 | KNOWN ISSUES
1233 | None
1234 |
1235 | NOTES
1236 | Any interesting aspect of the code in the package body which needs
1237 | to be stated.
1238 |
1239 | MODIFICATION HISTORY
1240 | Date Author Description of Changes
1241 | 25-10-2005 scherkas Created
1242 |
1243 *=======================================================================*/
1244 PROCEDURE SEARCH_RECEIPTS(
1245 P_SEARCH_RECEIPTS_REC IN LNS_APPL_ENGINE_PUB.SEARCH_RECEIPTS_REC,
1246 X_RECEIPTS_TBL OUT NOCOPY LNS_APPL_ENGINE_PUB.RECEIPTS_TBL)
1247 IS
1248
1249 /*-----------------------------------------------------------------------+
1250 | Local Variable Declarations and initializations |
1251 +-----------------------------------------------------------------------*/
1252
1253 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_RECEIPTS';
1254 l_Count number;
1255
1256 l_RECEIPT_REC LNS_APPL_ENGINE_PUB.RECEIPT_REC;
1257
1258 /*-----------------------------------------------------------------------+
1259 | Cursor Declarations |
1260 +-----------------------------------------------------------------------*/
1261
1262 -- search for receipts
1263 CURSOR receipts_cur(P_LOAN_ID number, P_RECEIPT_ID number, P_FROM_RC_DATE date, P_TO_RC_DATE date) IS
1264 select
1265 psa.cash_receipt_id,
1266 psa.trx_number,
1267 psa.trx_date,
1268 abs(psa.amount_due_remaining),
1269 psa.invoice_currency_code,
1270 party.party_name
1271 from
1272 lns_participants par,
1273 lns_loan_headers loan,
1274 hz_cust_accounts cust,
1275 ar_payment_schedules psa,
1276 lns_system_options sys,
1277 gl_sets_of_books books,
1278 hz_parties party,
1279 AR_SYSTEM_PARAMETERS arsys
1280 where
1281 loan.loan_id = P_LOAN_ID and
1282 loan.loan_id = par.loan_id and
1283 (par.loan_participant_type = 'PRIMARY_BORROWER' or
1284 par.loan_participant_type = decode(arsys.pay_unrelated_invoices_flag, 'N', 'PRIMARY_BORROWER', 'Y', 'COBORROWER') or
1285 par.loan_participant_type = decode(arsys.pay_unrelated_invoices_flag, 'N', 'PRIMARY_BORROWER', 'Y', 'GUARANTOR')) and
1286 cust.party_id = par.hz_party_id and
1287 cust.status = 'A' and
1288 psa.customer_id = cust.cust_account_id and
1289 psa.class = 'PMT' and
1290 psa.status = 'OP' and
1291 psa.amount_due_remaining <> 0 and
1292 psa.org_id = loan.org_id and
1293 trunc(psa.trx_date) >= trunc(nvl(P_FROM_RC_DATE, psa.trx_date)) and
1294 trunc(psa.trx_date) <= trunc(nvl(P_TO_RC_DATE, psa.trx_date)) and
1295 psa.cash_receipt_id = nvl(P_RECEIPT_ID, psa.cash_receipt_id) and
1296 (psa.invoice_currency_code = loan.loan_currency or
1297 psa.invoice_currency_code = books.currency_code) and
1298 sys.set_of_books_id = books.set_of_books_id and
1299 par.hz_party_id = party.party_id
1300 order by psa.trx_date, psa.cash_receipt_id;
1301
1302 BEGIN
1303
1304 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1305
1306 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Searching receipts...');
1307 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1308 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || P_SEARCH_RECEIPTS_REC.LOAN_ID);
1309 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_ID: ' || P_SEARCH_RECEIPTS_REC.RECEIPT_ID);
1310 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RC_FROM_DATE: ' || P_SEARCH_RECEIPTS_REC.RC_FROM_DATE);
1311 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RC_TO_DATE: ' || P_SEARCH_RECEIPTS_REC.RC_TO_DATE);
1312
1313
1314 if P_SEARCH_RECEIPTS_REC.LOAN_ID is null then
1315
1316 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_LOAN');
1317 FND_MSG_PUB.Add;
1318 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1319 RAISE FND_API.G_EXC_ERROR;
1320
1321 end if;
1322
1323 -- search for receipts
1324 if P_SEARCH_RECEIPTS_REC.RECEIPT_ID is null then
1325 open receipts_cur(P_SEARCH_RECEIPTS_REC.LOAN_ID, to_number(null), P_SEARCH_RECEIPTS_REC.RC_FROM_DATE, P_SEARCH_RECEIPTS_REC.RC_TO_DATE);
1326 else
1327 open receipts_cur(P_SEARCH_RECEIPTS_REC.LOAN_ID, P_SEARCH_RECEIPTS_REC.RECEIPT_ID, to_date(null), to_date(null));
1328 end if;
1329
1330 l_Count := 0;
1331 LOOP
1332
1333 fetch receipts_cur into l_RECEIPT_REC.RECEIPT_ID,
1334 l_RECEIPT_REC.RECEIPT_NUMBER,
1335 l_RECEIPT_REC.RECEIPT_DATE,
1336 l_RECEIPT_REC.REMAINING_AMOUNT,
1337 l_RECEIPT_REC.RECEIPT_CURRENCY,
1338 l_RECEIPT_REC.PARTY_NAME;
1339
1340 exit when receipts_cur%NOTFOUND;
1341
1342 l_Count := l_Count+1;
1343
1344 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Receipt #' || l_Count);
1345 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_ID: ' || l_RECEIPT_REC.RECEIPT_ID);
1346 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_NUMBER: ' || l_RECEIPT_REC.RECEIPT_NUMBER);
1347 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_DATE: ' || l_RECEIPT_REC.RECEIPT_DATE);
1348 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || l_RECEIPT_REC.REMAINING_AMOUNT);
1349 LogMessage(FND_LOG.LEVEL_STATEMENT, 'RECEIPT_CURRENCY: ' || l_RECEIPT_REC.RECEIPT_CURRENCY);
1350 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTY_NAME: ' || l_RECEIPT_REC.PARTY_NAME);
1351
1352 X_RECEIPTS_TBL(l_Count) := l_RECEIPT_REC;
1353
1354 END LOOP;
1355 close receipts_cur;
1356
1357 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1358 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total found ' || X_RECEIPTS_TBL.count || ' receipts');
1359
1360 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In ' || l_api_name || ' exception handling');
1365 RAISE FND_API.G_EXC_ERROR;
1366 END;
1367
1368
1369
1370 /*========================================================================
1371 | PUBLIC PROCEDURE SEARCH_LOAN_INVOICES
1372 |
1373 | DESCRIPTION
1374 | This procedure searches for available loan invoices
1375 |
1376 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1377 |
1378 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1379 | LogMessage
1380 |
1381 | PARAMETERS
1382 | P_LOAN_ID IN Loan
1383 | X_LOAN_INVOICES_TBL OUT NOCOPY Table of loan invoices
1384 |
1385 | KNOWN ISSUES
1386 | None
1387 |
1388 | NOTES
1389 | Any interesting aspect of the code in the package body which needs
1390 | to be stated.
1391 |
1392 | MODIFICATION HISTORY
1393 | Date Author Description of Changes
1394 | 25-10-2005 scherkas Created
1395 |
1396 *=======================================================================*/
1397 PROCEDURE SEARCH_LOAN_INVOICES(
1398 P_LOAN_ID IN NUMBER,
1399 P_FOR_ACTION IN VARCHAR2,
1400 X_LOAN_INVOICES_TBL OUT NOCOPY LNS_APPL_ENGINE_PUB.LOAN_INVOICES_TBL)
1401 IS
1402
1403 /*-----------------------------------------------------------------------+
1404 | Local Variable Declarations and initializations |
1405 +-----------------------------------------------------------------------*/
1406
1407 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_LOAN_INVOICES';
1408 l_Count number;
1409 l_order varchar2(30);
1410 l_search_str varchar2(1);
1411 l_start_pos number;
1412 l_end_pos number;
1413 l_Count1 number;
1414 l_scope varchar2(30);
1415 l_amortization_sched_id number;
1416
1417 l_LOAN_INVOICE_REC LNS_APPL_ENGINE_PUB.LOAN_INVOICE_REC;
1418 l_order_tbl DBMS_SQL.VARCHAR2_TABLE;
1419
1420 /*-----------------------------------------------------------------------+
1421 | Cursor Declarations |
1422 +-----------------------------------------------------------------------*/
1423
1424 -- get payment application order
1425 CURSOR order_cur(P_LOAN_ID number) IS
1426 select PAYMENT_APPLICATION_ORDER,
1427 nvl(PMT_APPL_ORDER_SCOPE, 'ACROSS_INSTALLMENTS')
1428 from lns_terms
1429 where loan_id = P_LOAN_ID;
1430
1431 -- get all amortizations
1432 CURSOR am_scheds_cur(P_LOAN_ID number) IS
1433 select amortization_schedule_id
1434 from lns_amortization_scheds
1435 where loan_id = P_LOAN_ID and
1436 reversed_flag = 'N'
1437 order by phase, payment_number, amortization_schedule_id;
1438
1439 -- search for invoices 1 - all interest, then all principal, the all fee
1440 CURSOR invoices1_cur(P_LOAN_ID number, P_INVOICE_TYPE varchar2, P_FOR_ACTION varchar2) IS
1441 select
1442 decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID),
1443 psa.PAYMENT_SCHEDULE_ID,
1444 psa.trx_number,
1445 loan.primary_borrower_id,
1446 party.party_name,
1447 loan.product_id,
1448 product.loan_product_name,
1449 loan.loan_id,
1450 loan.loan_number,
1451 am.amortization_schedule_id,
1452 am.payment_number,
1453 am.due_date,
1454 am.creation_date,
1455 am.phase,
1456 P_INVOICE_TYPE,
1457 look.meaning,
1458 psa.amount_due_remaining,
1459 psa.invoice_currency_code,
1460 loan.EXCHANGE_RATE
1461 from
1462 lns_amortization_scheds am,
1463 lns_loan_headers loan,
1464 ar_payment_schedules psa,
1465 hz_parties party,
1466 lns_loan_products_vl product,
1467 lns_lookups look
1468 where
1469 am.loan_id = P_LOAN_ID and
1470 loan.loan_id = am.loan_id and
1471 am.reversed_flag = 'N' and
1472 psa.customer_trx_id = decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID) and
1473 psa.amount_due_remaining > decode(P_FOR_ACTION, 'APPLY', 0, 'UNAPPLY', 0) and
1474 psa.status = decode(P_FOR_ACTION, 'APPLY', 'OP', 'UNAPPLY', 'OP') and
1475 (am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
1476 party.party_id = loan.primary_borrower_id and
1477 product.loan_product_id = loan.product_id and
1478 look.lookup_type = 'PAYMENT_APPLICATION_TYPE' and
1479 look.lookup_code = P_INVOICE_TYPE
1480 order by am.phase, am.payment_number, am.amortization_schedule_id;
1481
1482 -- search for invoices 2 - interest, then principal, then fee for single installment then next installment
1483 CURSOR invoices2_cur(P_LOAN_ID number, P_AMORTIZATION_SCHED_ID number, P_INVOICE_TYPE varchar2, P_FOR_ACTION varchar2) IS
1484 select
1485 decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID),
1486 psa.PAYMENT_SCHEDULE_ID,
1487 psa.trx_number,
1488 loan.primary_borrower_id,
1489 party.party_name,
1490 loan.product_id,
1491 product.loan_product_name,
1492 loan.loan_id,
1493 loan.loan_number,
1494 am.amortization_schedule_id,
1495 am.payment_number,
1496 am.due_date,
1497 am.creation_date,
1498 am.phase,
1499 P_INVOICE_TYPE,
1500 look.meaning,
1501 psa.amount_due_remaining,
1502 psa.invoice_currency_code,
1503 loan.EXCHANGE_RATE
1504 from
1505 lns_amortization_scheds am,
1506 lns_loan_headers loan,
1507 ar_payment_schedules psa,
1508 hz_parties party,
1509 lns_loan_products_vl product,
1510 lns_lookups look
1511 where
1512 am.loan_id = P_LOAN_ID and
1513 am.amortization_schedule_id = P_AMORTIZATION_SCHED_ID and
1514 loan.loan_id = am.loan_id and
1515 am.reversed_flag = 'N' and
1516 psa.customer_trx_id = decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID) and
1517 psa.amount_due_remaining > decode(P_FOR_ACTION, 'APPLY', 0, 'UNAPPLY', 0) and
1518 psa.status = decode(P_FOR_ACTION, 'APPLY', 'OP', 'UNAPPLY', 'OP') and
1519 (am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
1520 party.party_id = loan.primary_borrower_id and
1521 product.loan_product_id = loan.product_id and
1522 look.lookup_type = 'PAYMENT_APPLICATION_TYPE' and
1523 look.lookup_code = P_INVOICE_TYPE;
1524
1525 BEGIN
1526
1527 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1528
1529 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Searching loan invoices...');
1530 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1531 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || P_LOAN_ID);
1532 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_FOR_ACTION: ' || P_FOR_ACTION);
1533
1534 if P_LOAN_ID is null then
1535
1536 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_LOAN');
1537 FND_MSG_PUB.Add;
1538 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1539 RAISE FND_API.G_EXC_ERROR;
1540
1541 end if;
1542
1543 -- get payment application order
1544 open order_cur(P_LOAN_ID);
1545 fetch order_cur into l_order, l_scope;
1546 close order_cur;
1547
1548 l_count := 0;
1549 l_search_str := '_';
1550 l_start_pos := 1;
1551 l_end_pos := instr(l_order, l_search_str, l_start_pos, 1);
1552
1553 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Payment application order:');
1554 LOOP
1555 l_count := l_count + 1;
1556 if l_end_pos <> 0 then
1557 l_order_tbl(l_count) := substr(l_order, l_start_pos, l_end_pos-l_start_pos);
1558 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_count: ' || l_order_tbl(l_count));
1559 else
1560 l_order_tbl(l_count) := substr(l_order, l_start_pos, LENGTH(l_order)-l_start_pos+1);
1561 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_count: ' || l_order_tbl(l_count));
1562 exit;
1563 end if;
1564 l_start_pos := l_end_pos+1;
1565 l_end_pos := instr(l_order, l_search_str, l_start_pos, 1);
1566 END LOOP;
1567
1568 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Payment application order scope: ' || l_scope);
1569
1570 if l_scope = 'ACROSS_INSTALLMENTS' then
1571
1572 l_Count1 := 0;
1573 -- looping thru payment application order table and fetch invoices in order to be applied
1574 FOR l_Count IN 1..l_order_tbl.count LOOP
1575
1576 open invoices1_cur(P_LOAN_ID, l_order_tbl(l_Count), P_FOR_ACTION);
1577 LOOP
1578
1579 fetch invoices1_cur into l_LOAN_INVOICE_REC.CUST_TRX_ID,
1580 l_LOAN_INVOICE_REC.PAYMENT_SCHEDULE_ID,
1581 l_LOAN_INVOICE_REC.TRX_NUMBER,
1582 l_LOAN_INVOICE_REC.LOAN_BORROWER_ID,
1583 l_LOAN_INVOICE_REC.BORROWER_NAME,
1584 l_LOAN_INVOICE_REC.LOAN_PRODUCT_ID,
1585 l_LOAN_INVOICE_REC.PRODUCT_NAME,
1586 l_LOAN_INVOICE_REC.LOAN_ID,
1587 l_LOAN_INVOICE_REC.LOAN_NUMBER,
1588 l_LOAN_INVOICE_REC.AMORTIZATION_ID,
1589 l_LOAN_INVOICE_REC.PAYMENT_NUMBER,
1590 l_LOAN_INVOICE_REC.DUE_DATE,
1591 l_LOAN_INVOICE_REC.BILL_DATE,
1592 l_LOAN_INVOICE_REC.PHASE,
1593 l_LOAN_INVOICE_REC.INVOICE_TYPE_CODE,
1594 l_LOAN_INVOICE_REC.INVOICE_TYPE_DESC,
1595 l_LOAN_INVOICE_REC.REMAINING_AMOUNT,
1596 l_LOAN_INVOICE_REC.INVOICE_CURRENCY,
1597 l_LOAN_INVOICE_REC.EXCHANGE_RATE;
1598
1599 exit when invoices1_cur%NOTFOUND;
1600
1601 l_Count1 := l_Count1 + 1;
1602
1603 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Invoice #' || l_Count1);
1604 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_SCHEDULE_ID: ' || l_LOAN_INVOICE_REC.PAYMENT_SCHEDULE_ID);
1605 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUST_TRX_ID: ' || l_LOAN_INVOICE_REC.CUST_TRX_ID);
1606 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TRX_NUMBER: ' || l_LOAN_INVOICE_REC.TRX_NUMBER);
1607 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_BORROWER_ID: ' || l_LOAN_INVOICE_REC.LOAN_BORROWER_ID);
1608 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || l_LOAN_INVOICE_REC.BORROWER_NAME);
1609 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_PRODUCT_ID: ' || l_LOAN_INVOICE_REC.LOAN_PRODUCT_ID);
1610 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_NAME: ' || l_LOAN_INVOICE_REC.PRODUCT_NAME);
1611 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_LOAN_INVOICE_REC.LOAN_ID);
1612 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_NUMBER: ' || l_LOAN_INVOICE_REC.LOAN_NUMBER);
1613 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZATION_ID: ' || l_LOAN_INVOICE_REC.AMORTIZATION_ID);
1614 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_NUMBER: ' || l_LOAN_INVOICE_REC.PAYMENT_NUMBER);
1615 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DUE_DATE: ' || l_LOAN_INVOICE_REC.DUE_DATE);
1616 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BILL_DATE: ' || l_LOAN_INVOICE_REC.BILL_DATE);
1617 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PHASE: ' || l_LOAN_INVOICE_REC.PHASE);
1618 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_CODE: ' || l_LOAN_INVOICE_REC.INVOICE_TYPE_CODE);
1619 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_DESC: ' || l_LOAN_INVOICE_REC.INVOICE_TYPE_DESC);
1620 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || l_LOAN_INVOICE_REC.REMAINING_AMOUNT);
1621 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_CURRENCY: ' || l_LOAN_INVOICE_REC.INVOICE_CURRENCY);
1622 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXCHANGE_RATE: ' || l_LOAN_INVOICE_REC.EXCHANGE_RATE);
1623
1624 X_LOAN_INVOICES_TBL(l_Count1) := l_LOAN_INVOICE_REC;
1625
1626 END LOOP;
1627 close invoices1_cur;
1628
1629 END LOOP;
1630
1631 elsif l_scope = 'WITHIN_INSTALLMENT' then
1632
1633 l_Count1 := 0;
1634
1635 -- get all amortizations
1636 open am_scheds_cur(P_LOAN_ID);
1637
1638 LOOP
1639
1640 fetch am_scheds_cur into l_amortization_sched_id;
1641 exit when am_scheds_cur%NOTFOUND;
1642
1643 -- looping thru payment application order table and fetch invoices in order to be applied
1644 FOR l_Count IN 1..l_order_tbl.count LOOP
1645
1646 open invoices2_cur(P_LOAN_ID, l_amortization_sched_id, l_order_tbl(l_Count), P_FOR_ACTION);
1647 fetch invoices2_cur into l_LOAN_INVOICE_REC.CUST_TRX_ID,
1648 l_LOAN_INVOICE_REC.PAYMENT_SCHEDULE_ID,
1649 l_LOAN_INVOICE_REC.TRX_NUMBER,
1650 l_LOAN_INVOICE_REC.LOAN_BORROWER_ID,
1651 l_LOAN_INVOICE_REC.BORROWER_NAME,
1652 l_LOAN_INVOICE_REC.LOAN_PRODUCT_ID,
1653 l_LOAN_INVOICE_REC.PRODUCT_NAME,
1654 l_LOAN_INVOICE_REC.LOAN_ID,
1655 l_LOAN_INVOICE_REC.LOAN_NUMBER,
1656 l_LOAN_INVOICE_REC.AMORTIZATION_ID,
1657 l_LOAN_INVOICE_REC.PAYMENT_NUMBER,
1658 l_LOAN_INVOICE_REC.DUE_DATE,
1659 l_LOAN_INVOICE_REC.BILL_DATE,
1660 l_LOAN_INVOICE_REC.PHASE,
1661 l_LOAN_INVOICE_REC.INVOICE_TYPE_CODE,
1662 l_LOAN_INVOICE_REC.INVOICE_TYPE_DESC,
1663 l_LOAN_INVOICE_REC.REMAINING_AMOUNT,
1664 l_LOAN_INVOICE_REC.INVOICE_CURRENCY,
1665 l_LOAN_INVOICE_REC.EXCHANGE_RATE;
1666 close invoices2_cur;
1667
1668 if l_LOAN_INVOICE_REC.CUST_TRX_ID is not null then
1669
1670 l_Count1 := l_Count1 + 1;
1671
1672 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Invoice #' || l_Count1);
1673 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_SCHEDULE_ID: ' || l_LOAN_INVOICE_REC.PAYMENT_SCHEDULE_ID);
1674 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUST_TRX_ID: ' || l_LOAN_INVOICE_REC.CUST_TRX_ID);
1675 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TRX_NUMBER: ' || l_LOAN_INVOICE_REC.TRX_NUMBER);
1676 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_BORROWER_ID: ' || l_LOAN_INVOICE_REC.LOAN_BORROWER_ID);
1677 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || l_LOAN_INVOICE_REC.BORROWER_NAME);
1678 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_PRODUCT_ID: ' || l_LOAN_INVOICE_REC.LOAN_PRODUCT_ID);
1679 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_NAME: ' || l_LOAN_INVOICE_REC.PRODUCT_NAME);
1680 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_LOAN_INVOICE_REC.LOAN_ID);
1681 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_NUMBER: ' || l_LOAN_INVOICE_REC.LOAN_NUMBER);
1682 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZATION_ID: ' || l_LOAN_INVOICE_REC.AMORTIZATION_ID);
1683 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PAYMENT_NUMBER: ' || l_LOAN_INVOICE_REC.PAYMENT_NUMBER);
1684 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DUE_DATE: ' || l_LOAN_INVOICE_REC.DUE_DATE);
1685 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BILL_DATE: ' || l_LOAN_INVOICE_REC.BILL_DATE);
1686 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PHASE: ' || l_LOAN_INVOICE_REC.PHASE);
1687 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_CODE: ' || l_LOAN_INVOICE_REC.INVOICE_TYPE_CODE);
1688 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_TYPE_DESC: ' || l_LOAN_INVOICE_REC.INVOICE_TYPE_DESC);
1689 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REMAINING_AMOUNT: ' || l_LOAN_INVOICE_REC.REMAINING_AMOUNT);
1690 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INVOICE_CURRENCY: ' || l_LOAN_INVOICE_REC.INVOICE_CURRENCY);
1691 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXCHANGE_RATE: ' || l_LOAN_INVOICE_REC.EXCHANGE_RATE);
1692
1693 X_LOAN_INVOICES_TBL(l_Count1) := l_LOAN_INVOICE_REC;
1694
1695 end if;
1696
1697 END LOOP;
1698
1699 END LOOP;
1700 close am_scheds_cur;
1701
1702 end if;
1703
1704 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1705 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total found ' || X_LOAN_INVOICES_TBL.count || ' invoices');
1706
1707 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1708
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In ' || l_api_name || ' exception handling');
1712 RAISE FND_API.G_EXC_ERROR;
1713 END;
1714
1715
1716
1717 /*========================================================================
1718 | PUBLIC PROCEDURE SEARCH_AND_APPLY
1719 |
1720 | DESCRIPTION
1721 | This procedure applies receipts to loan invoices based on passed search criteria
1722 |
1723 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1724 | LNS_RC_APPL_ENG_CONCUR
1725 |
1726 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1727 | init
1728 | LogMessage
1729 |
1730 | PARAMETERS
1731 | P_API_VERSION IN Standard in parameter
1732 | P_INIT_MSG_LIST IN Standard in parameter
1733 | P_COMMIT IN Standard in parameter
1734 | P_VALIDATION_LEVEL IN Standard in parameter
1735 | P_SEARCH_REC IN Search record
1736 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1737 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1738 | X_MSG_DATA OUT NOCOPY Standard out parameter
1739 |
1740 | KNOWN ISSUES
1741 | None
1742 |
1743 | NOTES
1744 | Any interesting aspect of the code in the package body which needs
1745 | to be stated.
1746 |
1747 | MODIFICATION HISTORY
1748 | Date Author Description of Changes
1749 | 26-10-2005 scherkas Created
1750 |
1751 *=======================================================================*/
1752 PROCEDURE SEARCH_AND_APPLY(
1753 P_API_VERSION IN NUMBER,
1754 P_INIT_MSG_LIST IN VARCHAR2,
1755 P_COMMIT IN VARCHAR2,
1756 P_VALIDATION_LEVEL IN NUMBER,
1757 P_SEARCH_REC IN LNS_APPL_ENGINE_PUB.SEARCH_REC,
1758 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1759 X_MSG_COUNT OUT NOCOPY NUMBER,
1760 X_MSG_DATA OUT NOCOPY VARCHAR2)
1761 IS
1762
1763 /*-----------------------------------------------------------------------+
1764 | Local Variable Declarations and initializations |
1765 +-----------------------------------------------------------------------*/
1766
1767 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_AND_APPLY';
1768 l_api_version CONSTANT NUMBER := 1.0;
1769 l_return_status VARCHAR2(1);
1770 l_msg_count NUMBER;
1771 l_msg_data VARCHAR2(32767);
1772 l_Count number;
1773
1774 l_LOAN_INVOICES_TBL LNS_APPL_ENGINE_PUB.LOAN_INVOICES_TBL;
1775 l_SEARCH_RECEIPTS_REC LNS_APPL_ENGINE_PUB.SEARCH_RECEIPTS_REC;
1776 l_RECEIPTS_TBL LNS_APPL_ENGINE_PUB.RECEIPTS_TBL;
1777 l_LOAN_REC LNS_APPL_ENGINE_PUB.LOAN_REC;
1778 /*-----------------------------------------------------------------------+
1779 | Cursor Declarations |
1780 +-----------------------------------------------------------------------*/
1781
1782 -- get loans to pay
1783 CURSOR loans_cur(P_BORROWER_ID number, P_PRODUCT_ID number, P_LOAN_ID number, P_UNAPPLY_FLAG varchar2) IS
1784 select loan.loan_id,
1785 loan.loan_number,
1786 loan.primary_borrower_id,
1787 party.party_name,
1788 loan.product_id,
1789 product.loan_product_name,
1790 loan.loan_currency
1791 from lns_loan_headers loan,
1792 hz_parties party,
1793 lns_loan_products_vl product
1794 where
1795 loan.primary_borrower_id = nvl(P_BORROWER_ID, loan.primary_borrower_id) and
1796 loan.product_id = nvl(P_PRODUCT_ID, loan.product_id) and
1797 loan.loan_id = nvl(P_LOAN_ID, loan.loan_id) and
1798 party.party_id = loan.primary_borrower_id and
1799 product.loan_product_id = loan.product_id and
1800 ((select count(1)
1801 from
1802 lns_amortization_scheds am,
1803 ar_payment_schedules psa
1804 where
1805 am.loan_id = loan.loan_id and
1806 (am.reversed_flag is null or am.reversed_flag = 'N') and
1807 psa.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and
1808 psa.amount_due_remaining > decode(P_UNAPPLY_FLAG, 'Y', -1, 'N', 0) and
1809 psa.status = decode(P_UNAPPLY_FLAG, 'Y', psa.status, 'N', 'OP')) > 0)
1810 order by trunc(nvl(loan.open_loan_start_date, loan.loan_start_date)), loan.loan_id;
1811
1812 BEGIN
1813
1814 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1815
1816 -- Standard start of API savepoint
1817 SAVEPOINT SEARCH_AND_APPLY;
1818 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1819
1820 -- Standard call to check for call compatibility
1821 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1823 END IF;
1824
1825 -- Initialize message list if p_init_msg_list is set to TRUE
1826 IF FND_API.To_Boolean(p_init_msg_list) THEN
1827 FND_MSG_PUB.initialize;
1828 END IF;
1829
1830 -- Initialize API return status to success
1831 l_return_status := FND_API.G_RET_STS_SUCCESS;
1832
1833 -- START OF BODY OF API
1834
1835 init;
1836
1837 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Input parameters:');
1838 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Loan Borrower ID: ' || P_SEARCH_REC.LOAN_BORROWER_ID);
1839 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Loan Product ID: ' || P_SEARCH_REC.LOAN_PRODUCT_ID);
1840 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Loan ID: ' || P_SEARCH_REC.LOAN_ID);
1841 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Receipt ID: ' || P_SEARCH_REC.RECEIPT_ID);
1842 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'From Receipt Date: ' || P_SEARCH_REC.RC_FROM_DATE);
1843 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'To Receipt Date: ' || P_SEARCH_REC.RC_TO_DATE);
1844 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Fix Incorrect Applications: ' || P_SEARCH_REC.UNAPPLY_FLAG);
1845
1846 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Searching for loan to pay...');
1847
1848 /* init all statement clob */
1849 dbms_lob.createtemporary(g_last_all_appl_stmt, FALSE, DBMS_LOB.CALL);
1850 dbms_lob.open(g_last_all_appl_stmt, dbms_lob.lob_readwrite);
1851
1852 l_Count := 0;
1853 open loans_cur(P_SEARCH_REC.LOAN_BORROWER_ID, P_SEARCH_REC.LOAN_PRODUCT_ID, P_SEARCH_REC.LOAN_ID, P_SEARCH_REC.UNAPPLY_FLAG);
1854 LOOP
1855
1856 fetch loans_cur into l_LOAN_REC.LOAN_ID,
1857 l_LOAN_REC.LOAN_NUMBER,
1858 l_LOAN_REC.LOAN_BORROWER_ID,
1859 l_LOAN_REC.BORROWER_NAME,
1860 l_LOAN_REC.LOAN_PRODUCT_ID,
1861 l_LOAN_REC.PRODUCT_NAME,
1862 l_LOAN_REC.LOAN_CURRENCY;
1863 exit when loans_cur%NOTFOUND;
1864
1865 l_Count := l_Count + 1;
1866
1867 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
1868 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Processing loan ' || l_LOAN_REC.LOAN_NUMBER || '(' || l_LOAN_REC.LOAN_ID || ')...');
1869
1870 l_LOAN_REC.SEQUENCE_NUMBER := l_Count;
1871 LNS_APPL_ENGINE_PUB.ADD_LOAN_TAG(
1872 P_LOAN_REC => l_LOAN_REC,
1873 P_OPEN_CLOSE_FLAG => 'OPEN');
1874
1875 BEGIN
1876
1877 SAVEPOINT PAY_SINGLE_LOAN;
1878
1879 -- do unapply
1880 if P_SEARCH_REC.UNAPPLY_FLAG = 'Y' then
1881
1882 l_LOAN_INVOICES_TBL.delete;
1883 LNS_APPL_ENGINE_PUB.SEARCH_LOAN_INVOICES(
1884 P_LOAN_ID => l_LOAN_REC.LOAN_ID,
1885 P_FOR_ACTION => 'UNAPPLY',
1886 X_LOAN_INVOICES_TBL => l_LOAN_INVOICES_TBL);
1887
1888 if l_LOAN_INVOICES_TBL.count > 0 then
1889 LNS_APPL_ENGINE_PUB.UNAPPLY_RECEIPTS(
1890 P_LOAN_INVOICES_TBL => l_LOAN_INVOICES_TBL);
1891 end if;
1892
1893 end if;
1894
1895 -- do apply
1896 l_LOAN_INVOICES_TBL.delete;
1897 LNS_APPL_ENGINE_PUB.SEARCH_LOAN_INVOICES(
1898 P_LOAN_ID => l_LOAN_REC.LOAN_ID,
1899 P_FOR_ACTION => 'APPLY',
1900 X_LOAN_INVOICES_TBL => l_LOAN_INVOICES_TBL);
1901
1902 if l_LOAN_INVOICES_TBL.count > 0 then
1903
1904 l_SEARCH_RECEIPTS_REC.LOAN_ID := l_LOAN_REC.LOAN_ID;
1905 l_SEARCH_RECEIPTS_REC.RECEIPT_ID := P_SEARCH_REC.RECEIPT_ID;
1906 l_SEARCH_RECEIPTS_REC.RC_FROM_DATE := P_SEARCH_REC.RC_FROM_DATE;
1907 l_SEARCH_RECEIPTS_REC.RC_TO_DATE := P_SEARCH_REC.RC_TO_DATE;
1908
1909 l_RECEIPTS_TBL.delete;
1910 LNS_APPL_ENGINE_PUB.SEARCH_RECEIPTS(
1911 P_SEARCH_RECEIPTS_REC => l_SEARCH_RECEIPTS_REC,
1912 X_RECEIPTS_TBL => l_RECEIPTS_TBL);
1913
1914 if l_RECEIPTS_TBL.count > 0 then
1915
1916 LNS_APPL_ENGINE_PUB.APPLY_RECEIPTS(
1917 P_LOAN_INVOICES_TBL => l_LOAN_INVOICES_TBL,
1918 P_RECEIPTS_TBL => l_RECEIPTS_TBL);
1919
1920
1921 else
1922 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'No receipts found.');
1923 end if;
1924
1925 else
1926 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'No invoices found.');
1927 end if;
1928
1929 if P_COMMIT = FND_API.G_TRUE then
1930 COMMIT WORK;
1931 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited loan ' || l_LOAN_REC.LOAN_NUMBER);
1932 end if;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 ROLLBACK TO PAY_SINGLE_LOAN;
1937 g_cr_return_status := 'WARNING';
1938 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollback loan ' || l_LOAN_REC.LOAN_NUMBER);
1939 END;
1940
1941 LNS_APPL_ENGINE_PUB.ADD_LOAN_TAG(
1942 P_LOAN_REC => l_LOAN_REC,
1943 P_OPEN_CLOSE_FLAG => 'CLOSE');
1944
1945 END LOOP;
1946 close loans_cur;
1947
1948 if l_Count = 0 then
1949 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
1950 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total found ' || l_Count || ' loans');
1951 else
1952 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
1953 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed ' || l_Count || ' loans');
1954 end if;
1955
1956 -- END OF BODY OF API
1957 x_return_status := FND_API.G_RET_STS_SUCCESS;
1958
1959 -- Standard call to get message count and if count is 1, get message info
1960 FND_MSG_PUB.Count_And_Get(
1961 p_encoded => FND_API.G_FALSE,
1962 p_count => x_msg_count,
1963 p_data => x_msg_data);
1964
1965 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1966
1967 EXCEPTION
1968 WHEN FND_API.G_EXC_ERROR THEN
1969 ROLLBACK TO SEARCH_AND_APPLY;
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1972 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked loans');
1973 g_cr_return_status := 'ERROR';
1974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1975 ROLLBACK TO SEARCH_AND_APPLY;
1976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1977 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1978 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked loans');
1979 g_cr_return_status := 'ERROR';
1980 WHEN OTHERS THEN
1981 ROLLBACK TO SEARCH_AND_APPLY;
1982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1983 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1984 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1985 END IF;
1986 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1987 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked loans');
1988 g_cr_return_status := 'ERROR';
1989 END;
1990
1991
1992
1993 /*========================================================================
1994 | PUBLIC PROCEDURE LNS_APPL_RC_CONCUR
1995 |
1996 | DESCRIPTION
1997 | This procedure got called from concurent manager to apply receipts to loan invoices
1998 |
1999 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2000 | None
2001 |
2002 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2003 | LogMessage
2004 |
2005 | PARAMETERS
2006 | ERRBUF OUT Returns errors to CM
2007 | RETCODE OUT Returns error code to CM
2008 | LOAN_BORROWER_ID IN Loan primary borrower id
2009 | LOAN_PRODUCT_ID IN Loan product id
2010 | LOAN_ID IN Loan id
2011 | RECEIPT_ID IN Receipt id
2012 | RC_FROM_DATE IN From receipt date
2013 | RC_TO_DATE IN To receipt date
2014 | UNAPPLY_FLAG IN Fix incorrect applications
2015 |
2016 | KNOWN ISSUES
2017 | None
2018 |
2019 | NOTES
2020 | Any interesting aspect of the code in the package body which needs
2021 | to be stated.
2022 |
2023 | MODIFICATION HISTORY
2024 | Date Author Description of Changes
2025 | 24-10-2005 scherkas Created
2026 |
2027 *=======================================================================*/
2028 PROCEDURE LNS_APPL_RC_CONCUR(
2029 ERRBUF OUT NOCOPY VARCHAR2,
2030 RETCODE OUT NOCOPY VARCHAR2,
2031 LOAN_BORROWER_ID IN NUMBER,
2032 LOAN_PRODUCT_ID IN NUMBER,
2033 LOAN_ID IN NUMBER,
2034 RECEIPT_ID IN NUMBER,
2035 RC_FROM_DATE IN DATE,
2036 RC_TO_DATE IN DATE,
2037 UNAPPLY_FLAG IN VARCHAR2)
2038 IS
2039
2040 /*-----------------------------------------------------------------------+
2041 | Local Variable Declarations and initializations |
2042 +-----------------------------------------------------------------------*/
2043 l_msg_count number;
2044 l_return boolean;
2045 l_SEARCH_REC LNS_APPL_ENGINE_PUB.SEARCH_REC;
2046
2047 BEGIN
2048
2049 g_cr_return_status := 'NORMAL';
2050
2051 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
2052 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '<<--------Applying AR cash receipts to loan invoices...-------->>');
2053
2054 l_SEARCH_REC.LOAN_BORROWER_ID := LOAN_BORROWER_ID;
2055 l_SEARCH_REC.LOAN_PRODUCT_ID := LOAN_PRODUCT_ID;
2056 l_SEARCH_REC.LOAN_ID := LOAN_ID;
2057 l_SEARCH_REC.RECEIPT_ID := RECEIPT_ID;
2058 l_SEARCH_REC.RC_FROM_DATE := RC_FROM_DATE;
2059 l_SEARCH_REC.RC_TO_DATE := RC_TO_DATE;
2060 l_SEARCH_REC.UNAPPLY_FLAG := UNAPPLY_FLAG;
2061
2062 LNS_APPL_ENGINE_PUB.SEARCH_AND_APPLY(
2063 P_API_VERSION => 1.0,
2064 P_INIT_MSG_LIST => FND_API.G_TRUE,
2065 P_COMMIT => FND_API.G_TRUE,
2066 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2067 P_SEARCH_REC => l_SEARCH_REC,
2068 X_RETURN_STATUS => RETCODE,
2069 X_MSG_COUNT => l_msg_count,
2070 X_MSG_DATA => ERRBUF);
2071
2072 LNS_APPL_ENGINE_PUB.BUILD_RC_APPL_REPORT(
2073 P_SEARCH_REC => l_SEARCH_REC);
2074
2075 if g_cr_return_status = 'WARNING' then
2076 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2077 status => g_cr_return_status,
2078 message => 'Not all applications were successfully. Please review log file.');
2079 elsif g_cr_return_status = 'ERROR' then
2080 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2081 status => g_cr_return_status,
2082 message => 'Application process has failed. Please review log file.');
2083 end if;
2084
2085 END;
2086
2087
2088
2089 BEGIN
2090 G_LOG_ENABLED := 'N';
2091 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
2092
2093 /* getting msg logging info */
2094 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
2095 if (G_LOG_ENABLED = 'N') then
2096 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
2097 else
2098 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
2099 end if;
2100
2101 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
2102 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
2103
2104 END;