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