[Home] [Help]
PACKAGE BODY: APPS.LNS_REP_UTILS
Source
1 PACKAGE BODY LNS_REP_UTILS as
2 /* $Header: LNS_REP_UTILS_B.pls 120.49.12020000.3 2012/08/14 18:28:41 scherkas ship $ */
3
4 /*========================================================================
5 | PUBLIC PROCEDURE PRINT_CLOB
6 |
7 | DESCRIPTION
8 | This process selects the process to run.
9 |
10 | PSEUDO CODE/LOGIC
11 |
12 | PARAMETERS
13 | P_PARAM1 IN Standard in parameter
14 | X_PARAM2 OUT NOCOPY Standard out parameter
15 |
16 | KNOWN ISSUES
17 | None
18 |
19 | NOTES
20 |
21 | MODIFICATION HISTORY
22 | Date Author Description of Changes
23 | 17-Jan-2005 GBELLARY Created
24 |
25 *=======================================================================*/
26
27
28 /*=======================================================================+
29 | Package Global Constants
30 +=======================================================================*/
31 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_REP_UTILS';
32 G_LOG_ENABLED varchar2(5);
33 G_MSG_LEVEL NUMBER;
34 g_org_id number;
35
36 /*========================================================================+
37 Function which returns the global variable g_loan_start_date_from
38 ========================================================================*/
39
40 FUNCTION get_loan_start_date_from return DATE is
41 BEGIN
42 return lns_rep_utils.g_loan_start_date_from;
43 END get_loan_start_date_from;
44
45 /*========================================================================+
46 Function which returns the global variable g_loan_start_date_to
47 ========================================================================*/
48
49 FUNCTION get_loan_start_date_to return DATE is
50 BEGIN
51 return lns_rep_utils.g_loan_start_date_to;
52 END get_loan_start_date_to;
53 /*========================================================================+
54 Function which returns the global variable g_bill_due_date_from
55 ========================================================================*/
56
57 FUNCTION get_bill_due_date_from return DATE is
58 BEGIN
59 return lns_rep_utils.g_bill_due_date_from;
60 END get_bill_due_date_from;
61
62 /*========================================================================+
63 Function which returns the global variable g_bill_due_date_to
64 ========================================================================*/
65
66 FUNCTION get_bill_due_date_to return DATE is
67 BEGIN
68 return lns_rep_utils.g_bill_due_date_to;
69 END get_bill_due_date_to;
70 /*========================================================================
71 | PRIVATE PROCEDURE LogMessage
72 |
73 | DESCRIPTION
74 | This procedure logs debug messages to db and to CM log
75 |
76 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
77 |
78 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
79 | None
80 |
81 | PARAMETERS
82 | p_msg_level IN Debug msg level
83 | p_msg IN Debug msg itself
84 |
85 | KNOWN ISSUES
86 | None
87 |
88 |
89 | NOTES
90 | Any interesting aspect of the code in the package body which needs
91 | to be stated.
92 |
93 | MODIFICATION HISTORY
94 | Date Author Description of Changes
95 | 17-Jan-2005 GBELLARY Created
96 |
97 *=======================================================================*/
98 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
99 IS
100 BEGIN
101 if (p_msg_level >= G_MSG_LEVEL) then
102
103 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
104
105 end if;
106
107 if FND_GLOBAL.Conc_Request_Id is not null then
108 fnd_file.put_line(FND_FILE.LOG, p_msg);
109 end if;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
114 END;
115
116
117
118 PROCEDURE PRINT_CLOB (lob_loc in clob) IS
119
120 /*-----------------------------------------------------------------------+
121 | Local Variable Declarations and initializations |
122 +-----------------------------------------------------------------------*/
123
124 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
125 l_api_version CONSTANT NUMBER := 1.0;
126 c_endline CONSTANT VARCHAR2 (1) := '
127 ';
128 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
129 l_start NUMBER := 1;
130 l_end NUMBER;
131 l_one_line VARCHAR2 (17000);
132 l_charset VARCHAR2(100);
133
134 /*-----------------------------------------------------------------------+
135 | Cursor Declarations |
136 +-----------------------------------------------------------------------*/
137 BEGIN
138 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
139
140 LOOP
141 l_end :=
142 DBMS_LOB.INSTR (lob_loc => lob_loc,
143 pattern => c_endline,
144 offset => l_start,
145 nth => 1
146 );
147
148 IF (NVL (l_end, 0) < 1)
149 THEN
150 EXIT;
151 END IF;
152
153 l_one_line :=
154 DBMS_LOB.SUBSTR (lob_loc => lob_loc,
155 amount => l_end - l_start,
156 offset => l_start
157 );
158 l_start := l_end + c_endline_len;
159 --Fnd_File.PUT_line(Fnd_File.LOG,l_one_line);
160 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
161
162 END LOOP;
163 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
164 END PRINT_CLOB;
165
166 FUNCTION REPLACE_SPECIAL_CHARS(P_XML_DATA IN VARCHAR2)
167 RETURN VARCHAR2
168 IS
169
170 l_api_name CONSTANT VARCHAR2(30) := 'REPLACE_SPECIAL_CHARS';
171 l_xml VARCHAR2(32767);
172
173 BEGIN
174 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
175 l_xml := REPLACE(P_XML_DATA,'&','&');
176 l_xml := REPLACE(l_xml,'&','&');
177 RETURN l_xml;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
182 || ' -');
183 RAISE;
184 END REPLACE_SPECIAL_CHARS;
185
186
187 PROCEDURE PROCESS_PORTFOLIO_REPORT(ERRBUF OUT NOCOPY VARCHAR2
188 ,RETCODE OUT NOCOPY VARCHAR2
189 ,LOAN_CLASS IN VARCHAR2
190 ,LOAN_TYPE_ID IN NUMBER
191 ,CURRENCY_CODE IN VARCHAR2
192 ,LOAN_NUMBER IN VARCHAR2
193 ,BORROWER_ID IN NUMBER
194 ,CUST_ACCOUNT_ID IN NUMBER
195 ,LOAN_START_DATE_FROM IN VARCHAR2
196 ,LOAN_START_DATE_TO IN VARCHAR2
197 ,LEGAL_ENTITY_ID IN NUMBER
198 ,PRODUCT_ID IN NUMBER
199 ,LOAN_ASSIGNED_TO IN NUMBER
200 ,LOAN_STATUS1 IN VARCHAR2
201 ,LOAN_STATUS2 IN VARCHAR2
202 ,INCLUDE_CHARTS IN VARCHAR2
203 ) IS
204 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PORTFOLIO_REPORT';
205 l_api_version CONSTANT NUMBER := 1.0;
206 ctx DBMS_XMLQUERY.ctxType;
207 result CLOB;
208 qryCtx DBMS_XMLGEN.ctxHandle;
209 l_result CLOB;
210 tempResult CLOB;
211 l_version varchar2(20);
212 l_compatibility varchar2(20);
213 l_suffix varchar2(2);
214 l_majorVersion number;
215 l_resultOffset number;
216 l_xml_header varchar2(3000);
217 l_xml_header_length number;
218 l_errNo NUMBER;
219 l_errMsg VARCHAR2(200);
220 queryCtx DBMS_XMLquery.ctxType;
221 l_xml_query VARCHAR2(32767);
222 TYPE ref_cur IS REF CURSOR;
223 l_xml_stmt ref_cur;
224 l_rows_processed NUMBER;
225 l_new_line VARCHAR2(1);
226 l_org_id hr_operating_units.organization_id%TYPE;
227 l_org_name hr_operating_units.NAME%TYPE;
228 l_borrower_name hz_parties.party_name%TYPE;
229 l_account_number hz_cust_accounts.account_number%TYPE;
230 l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
231 l_start_date_from lns_loan_headers_all.loan_start_date%TYPE;
232 l_start_date_to lns_loan_headers_all.loan_start_date%TYPE;
233 l_loan_type_desc lns_loan_types.loan_type_desc%TYPE;
234 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
235 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
236 l_legal_entity_name xle_entity_profiles.name%TYPE;
237 l_loan_product_name lns_loan_products_all.loan_product_name%TYPE;
238 l_loan_officer jtf_rs_resource_extns.source_name%TYPE;
239 l_loan_status1_desc lns_lookups.meaning%TYPE;
240 l_loan_status2_desc lns_lookups.meaning%TYPE;
241 l_include_charts VARCHAR2(30);
242 l_close_tag VARCHAR2(100);
243 l_query VARCHAR2(5000) :=
244 'select ' ||
245 'lh.LOAN_ID, ' ||
246 'lh.loan_number ' ||
247 ',hp.party_name customer ' ||
248 ',llk.meaning || decode(lh.secondary_status,null,'''','': '') || llks.meaning loan_status_meaning ' ||
249 ',lh.loan_status || decode(lh.secondary_status,null,'''','': '') || lh.secondary_status loan_status ' ||
250 ',lh.loan_status primary_loan_status ' ||
251 ',lh.secondary_status secondary_loan_status ' ||
252 ',pay.TOTAL_PRINCIPAL_BALANCE loan_amount ' ||
253 ',(CASE
254 WHEN
255 (
256 lh.LOAN_STATUS = ''ACTIVE''
257 OR lh.LOAN_STATUS = ''DEFAULT''
258 OR lh.LOAN_STATUS = ''DELINQUENT''
259 OR lh.LOAN_STATUS = ''PAIDOFF''
260 )
261 THEN
262 pay.TOTAL_PRINCIPAL_BALANCE
263 ELSE
264 lh.REQUESTED_AMOUNT
265 END
266 ) loan_req_bal_amount' ||
267 ',lh.loan_description ' ||
268 ',to_char(lh.loan_maturity_date, ''YYYY-MM-DD'') loan_maturity_date ' ||
269 ',lh.loan_term || '' '' || llktt.meaning loan_length ' ||
270 ',to_char(lh.LOAN_APPLICATION_DATE, ''YYYY-MM-DD'') LOAN_APPLICATION_DATE ' ||
271 ',lh.ORG_ID ' ||
272 ',lh.LOAN_TYPE ' ||
273 ',lh.LOAN_CLASS_CODE ' ||
274 ',lh.LOAN_CURRENCY ' ||
275 ',LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate ' ||
276 ', llkrt.meaning interest_type ' ||
277 ',lh.LOAN_SUBTYPE ' ||
278 ',(select max(last_update_date) from LNS_APPROVAL_ACTIONS ' ||
279 'where loan_id = lh.LOAN_ID ' ||
280 'and ACTION_TYPE = ''SUBMIT_FOR_APPR'') submit_for_approval_date ' ||
281 ',lh.LAST_BILLED_DATE ' ||
282 ',(lh.REQUESTED_AMOUNT + nvl(lh.ADD_REQUESTED_AMOUNT, 0)) original_requested_amount ' ||
283 ',lh.LOAN_APPROVAL_DATE ' ||
284 ',pay.TOTAL_PRIN_PAID_TODATE principal_paid ' ||
285 ',pay.INTEREST_PAID_TODATE interest_paid ' ||
286 ',pay.FEE_PAID_TODATE fees_paid ' ||
287 ',pay.NEXT_PAYMENT_DUE_DATE next_payment_due_date ' ||
288 ',pay.NEXT_PAYMENT_PRINCIPAL_DUE next_principal_due ' ||
289 ',pay.NEXT_PAYMENT_INTEREST_DUE next_interest_due ' ||
290 ',pay.NEXT_PAYMENT_FEE_DUE next_fees_due ' ||
291 ',pay.NEXT_PAYMENT_TOTAL_DUE next_payment_due ' ||
292 ',account.ACCOUNT_NUMBER ' ||
293 ', CURSOR (select ' ||
294 'REQUESTED_AMOUNT loan_requested_amount, ' ||
295 'REFERENCE_AMOUNT original_rec_balance, ' ||
296 'REFERENCE_NUMBER original_rec_desc ' ||
297 'from LNS_LOAN_LINES ' ||
298 'where loan_id = lh.LOAN_ID ' ||
299 'and end_date is null) AS ORIGINAL_RECEIVABLES ' ||
300 'from ' ||
301 'lns_loan_headers_all_vl lh, ' ||
302 'hz_parties hp, ' ||
303 'lns_terms t, ' ||
304 'lns_lookups llk, ' ||
305 'LNS_PAY_SUM_V pay, ' ||
306 'hz_cust_accounts_all account, ' ||
307 'lns_lookups llkrt, ' ||
308 'lns_lookups llktt, ' ||
309 'lns_lookups llks ' ||
310 'where ' ||
311 'lh.primary_borrower_id = hp.party_id and ' ||
312 'lh.loan_id = pay.loan_id and ' ||
313 'lh.loan_id = t.loan_id and ' ||
314 'llk.lookup_code = lh.loan_status and ' ||
315 'llk.lookup_type = ''LOAN_STATUS'' and ' ||
316 'llktt.lookup_code = lh.loan_term_period and ' ||
317 'llktt.lookup_type = ''PERIOD'' and ' ||
318 'llkrt.lookup_code = t.rate_type and ' ||
319 'llkrt.lookup_type = ''RATE_TYPE'' and ' ||
320 'llks.lookup_code(+) = lh.secondary_status and ' ||
321 'llks.lookup_type(+) = ''SECONDARY_STATUS'' and ' ||
322 'lh.loan_status <> ''DELETED'' and ' ||
323 'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
324 'lh.loan_class_code = :LOAN_CLASS and ' ||
325 'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
326 'lh.loan_currency = :CURRENCY_CODE and ' ||
327 'lh.org_id = :ORG_ID';
328 l_temp_where_clause VARCHAR2(200);
329 BEGIN
330 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
331
332 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters: ');
333 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_CLASS = ' || LOAN_CLASS);
334 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_TYPE_ID = ' || LOAN_TYPE_ID);
335 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CURRENCY_CODE = ' || CURRENCY_CODE);
336 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_NUMBER = ' || LOAN_NUMBER);
337 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_ID = ' || BORROWER_ID);
338 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUST_ACCOUNT_ID = ' || CUST_ACCOUNT_ID);
339 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_START_DATE_FROM = ' || LOAN_START_DATE_FROM);
340 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_START_DATE_TO = ' || LOAN_START_DATE_TO);
341 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_ID = ' || LEGAL_ENTITY_ID);
342 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_ID = ' || PRODUCT_ID);
343 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ASSIGNED_TO = ' || LOAN_ASSIGNED_TO);
344 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_STATUS1 = ' || LOAN_STATUS1);
345 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_STATUS2 = ' || LOAN_STATUS2);
346 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INCLUDE_CHARTS = ' || INCLUDE_CHARTS);
347
348 l_loan_type_id := loan_type_id;
349 l_legal_entity_id := legal_entity_id;
350 g_loan_start_date_from := trunc(fnd_date.canonical_to_date(loan_start_date_from));
351 g_loan_start_date_to := trunc(fnd_date.canonical_to_date(loan_start_date_to));
352 l_cust_account_id := cust_account_id;
353 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
354 IF nvl(INCLUDE_CHARTS, 'N') = 'Y' THEN
355 l_include_charts := 'Yes';
356 ELSE
357 l_include_charts := 'No';
358 END IF;
359
360 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_org_id = ' || l_org_id);
361 LogMessage(FND_LOG.LEVEL_STATEMENT, 'g_loan_start_date_from = ' || g_loan_start_date_from);
362 LogMessage(FND_LOG.LEVEL_STATEMENT, 'g_loan_start_date_to = ' || g_loan_start_date_to);
363
364 BEGIN
365 SELECT loan_type_desc
366 into l_loan_type_desc
367 from lns_loan_types_vl
368 where loan_type_id = l_loan_type_id;
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN null;
371 END;
372 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_type_desc = ' || l_loan_type_desc);
373
374 -- Build Bind Placeholders for all non-mandatory parameters
375 IF loan_number IS NOT NULL
376 THEN
377 l_query := l_query || ' and lh.loan_number like :LOAN_NUMBER';
378 END IF;
379 IF borrower_id IS NOT NULL
380 THEN
381 l_query := l_query || ' and lh.primary_borrower_id = :BORROWER_ID';
382 END IF;
383 IF cust_account_id IS NOT NULL
384 THEN
385 l_query := l_query || ' and lh.cust_account_id = :CUST_ACCOUNT_ID';
386 END IF;
387 IF loan_start_date_from IS NOT NULL and loan_start_date_to is NULL THEN
388 l_query := l_query || ' and trunc(lh.loan_start_date) >= lns_rep_utils.get_loan_start_date_from()';
389 END IF;
390 IF loan_start_date_to IS NOT NULL and loan_start_date_from is NULL THEN
391 l_query := l_query || ' and trunc(lh.loan_start_date) <= lns_rep_utils.get_loan_start_date_to()';
392 END IF;
393 IF loan_start_date_from IS NOT NULL and loan_start_date_to is NOT NULL THEN
394 l_query := l_query || ' and trunc(lh.loan_start_date) between lns_rep_utils.get_loan_start_date_from() and lns_rep_utils.get_loan_start_date_to()';
395 END IF;
396 IF legal_entity_id IS NOT NULL
397 THEN
398 l_query := l_query || ' and lh.legal_entity_id = :LEGAL_ENTITY_ID';
399 END IF;
400 IF product_id IS NOT NULL
401 THEN
402 l_query := l_query || ' and lh.product_id = :PRODUCT_ID';
403 END IF;
404 IF loan_assigned_to IS NOT NULL
405 THEN
406 l_query := l_query || ' and lh.loan_assigned_to = :LOAN_ASSIGNED_TO';
407 END IF;
408 IF loan_status1 IS NOT NULL and loan_status2 IS NULL
409 THEN
410 l_query := l_query || ' and lh.loan_status = ''' || LOAN_STATUS1 ||'''';
411 END IF;
412 IF loan_status2 IS NOT NULL and loan_status1 IS NULL
413 THEN
414 l_query := l_query || ' and lh.loan_status = ''' || LOAN_STATUS2 ||'''';
415 END IF;
416 IF loan_status1 IS NOT NULL and loan_status2 IS NOT NULL
417 THEN
418 --l_query := l_query || ' and (lh.loan_status = :LOAN_STATUS1 or lh.loan_status = :LOAN_STATUS2)';
419 l_query := l_query || ' and (lh.loan_status = ''' || LOAN_STATUS1 ||''' or lh.loan_status = ''' || LOAN_STATUS2 ||''')';
420 END IF;
421
422 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Final query = ' || l_query);
423
424 -- Start Getting Parameter Display Values
425 SELECT name
426 INTO l_org_name
427 FROM HR_ALL_ORGANIZATION_UNITS_TL
428 WHERE organization_id = l_org_id
429 AND language = userenv('LANG');
430
431 IF borrower_id is NOT NULL
432 THEN
433 SELECT PARTY_NAME
434 INTO l_borrower_name
435 FROM hz_parties
436 WHERE party_id = borrower_id;
437 END IF;
438
439 IF cust_account_id is NOT NULL
440 THEN
441 SELECT account_number
442 into l_account_number
443 FROM hz_cust_accounts
444 WHERE cust_account_id = l_cust_account_id;
445 END IF;
446 IF loan_status1 is NOT NULL
447 THEN
448 SELECT meaning
449 into l_loan_status1_desc
450 from lns_lookups
451 where lookup_type = 'LOAN_STATUS'
452 and lookup_code = loan_status1;
453 END IF;
454 IF loan_status2 is NOT NULL
455 THEN
456 SELECT meaning
457 into l_loan_status2_desc
458 from lns_lookups
459 where lookup_type = 'LOAN_STATUS'
460 and lookup_code = loan_status2;
461 END IF;
462 IF legal_entity_id is NOT NULL
463 THEN
464 SELECT NAME
465 INTO l_legal_entity_name
466 FROM xle_entity_profiles
467 WHERE legal_entity_id = l_legal_entity_id;
468 END IF;
469 IF product_id is NOT NULL
470 THEN
471 SELECT loan_product_name
472 INTO l_loan_product_name
473 FROM lns_loan_products_all_vl
474 WHERE loan_product_id = product_id;
475 END IF;
476 IF loan_assigned_to is NOT NULL
477 THEN
478 SELECT source_name
479 INTO l_loan_officer
480 FROM jtf_rs_resource_extns
481 WHERE resource_id = loan_assigned_to;
482 END IF;
483 ctx := DBMS_XMLQUERY.newContext(l_query);
484 LogMessage(FND_LOG.LEVEL_STATEMENT, 'after DBMS_XMLQUERY.newContext');
485
486 -- Bind Mandatory Variables
487 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_CLASS', loan_class);
488 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_TYPE_ID', loan_type_id);
489 DBMS_XMLQuery.setBindValue(ctx, 'CURRENCY_CODE', currency_code);
490 DBMS_XMLQuery.setBindValue(ctx, 'ORG_ID', l_org_id);
491 LogMessage(FND_LOG.LEVEL_STATEMENT, 'after DBMS_XMLQuery.setBindValue');
492
493 -- Bind Optional Variables if they are NOT NULL
494 IF borrower_id is NOT NULL
495 THEN
496 DBMS_XMLQuery.setBindValue(ctx, 'BORROWER_ID', borrower_id);
497 END IF;
498 IF loan_number is NOT NULL
499 THEN
500 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_NUMBER', loan_number);
501 END IF;
502 IF cust_account_id is NOT NULL
503 THEN
504 DBMS_XMLQuery.setBindValue(ctx, 'CUST_ACCOUNT_ID', cust_account_id);
505 END IF;
506 IF legal_entity_id is NOT NULL
507 THEN
508 DBMS_XMLQuery.setBindValue(ctx, 'LEGAL_ENTITY_ID', legal_entity_id);
509 END IF;
510 IF loan_assigned_to is NOT NULL
511 THEN
512 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_ASSIGNED_TO', loan_assigned_to);
513 END IF;
514 IF product_id is NOT NULL
515 THEN
516 DBMS_XMLQuery.setBindValue(ctx, 'PRODUCT_ID', product_id);
517 END IF;
518
519 -- now get the result
520 BEGIN
521 l_result := DBMS_XMLQUERY.getXML(ctx);
522 LogMessage(FND_LOG.LEVEL_STATEMENT, 'after DBMS_XMLQUERY.getXML');
523 DBMS_XMLQuery.closeContext(ctx);
524 l_rows_processed := 1;
525 EXCEPTION
526 WHEN OTHERS THEN
527 LogMessage(FND_LOG.LEVEL_STATEMENT, 'in exception');
528 DBMS_XMLQuery.getExceptionContent(ctx,l_errNo,l_errMsg);
529 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_errNo = ' || l_errNo);
530 IF l_errNo = 1403 THEN
531 l_rows_processed := 0;
532 END IF;
533 DBMS_XMLQuery.closeContext(ctx);
534 END;
535 -- We are adding the LNSPORTFOLIO and PARAMETERS TAGs so we have
536 -- to offset the first line.
537 IF l_rows_processed <> 0 THEN
538 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
539 tempResult := l_result;
540 ELSE
541 l_resultOffset := 0;
542 END IF;
543
544 l_new_line := '
545 ';
546 /* Prepare the tag for the report heading */
547 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
548 l_xml_header := l_xml_header ||l_new_line||'<LNSPORTFOLIO>';
549 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
550 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||REPLACE_SPECIAL_CHARS(l_org_name)||'</ORG_NAME>';
551 l_xml_header := l_xml_header ||l_new_line||' <LOAN_CLASS_CODE>' ||REPLACE_SPECIAL_CHARS(loan_class) ||'</LOAN_CLASS_CODE>';
552 l_xml_header := l_xml_header ||l_new_line||' <LOAN_TYPE_DESC>' ||REPLACE_SPECIAL_CHARS(l_loan_type_desc) ||'</LOAN_TYPE_DESC>';
553 l_xml_header := l_xml_header ||l_new_line||' <CURRENCY_CODE>' ||REPLACE_SPECIAL_CHARS(currency_code) ||'</CURRENCY_CODE>';
554 l_xml_header := l_xml_header ||l_new_line||' <LOAN_NUMBER>' ||REPLACE_SPECIAL_CHARS(loan_number) ||'</LOAN_NUMBER>';
555 l_xml_header := l_xml_header ||l_new_line||' <BORROWER_NAME>' ||REPLACE_SPECIAL_CHARS(l_borrower_name) ||'</BORROWER_NAME>';
556 l_xml_header := l_xml_header ||l_new_line||' <ACCOUNT_NUMBER>' ||l_account_number ||'</ACCOUNT_NUMBER>';
557 l_xml_header := l_xml_header ||l_new_line||' <LOAN_START_DATE_FROM>' || to_char(to_date(loan_start_date_from,'YYYY/MM/DD HH24:MI:SS'),'YYYY-MM-DD') ||'</LOAN_START_DATE_FROM>';
558 l_xml_header := l_xml_header ||l_new_line||' <LOAN_START_DATE_TO>' || to_char(to_date(loan_start_date_to,'YYYY/MM/DD HH24:MI:SS'),'YYYY-MM-DD') ||'</LOAN_START_DATE_TO>';
559 l_xml_header := l_xml_header ||l_new_line||' <LOAN_STATUS1_DESC>' ||REPLACE_SPECIAL_CHARS(l_loan_status1_desc) ||'</LOAN_STATUS1_DESC>';
560 l_xml_header := l_xml_header ||l_new_line||' <LOAN_STATUS2_DESC>' ||REPLACE_SPECIAL_CHARS(l_loan_status2_desc) ||'</LOAN_STATUS2_DESC>';
561 l_xml_header := l_xml_header ||l_new_line||' <LEGAL_ENTITY_NAME>' ||REPLACE_SPECIAL_CHARS(l_legal_entity_name) ||'</LEGAL_ENTITY_NAME>';
562 l_xml_header := l_xml_header ||l_new_line||' <LOAN_PRODUCT_NAME>' ||REPLACE_SPECIAL_CHARS(l_loan_product_name) ||'</LOAN_PRODUCT_NAME>';
563 l_xml_header := l_xml_header ||l_new_line||' <LOAN_OFFICER>' ||REPLACE_SPECIAL_CHARS(l_loan_officer) ||'</LOAN_OFFICER>';
564 l_xml_header := l_xml_header ||l_new_line||' <INCLUDE_CHARTS>' ||l_include_charts ||'</INCLUDE_CHARTS>';
565 l_xml_header := l_xml_header ||l_new_line||' <REPORT_GENERATION_DATE>' || to_char(sysdate,'YYYY-MM-DD') || '</REPORT_GENERATION_DATE>';
566 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
567 l_close_tag := l_new_line||'</LNSPORTFOLIO>'||l_new_line;
568 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added parameters');
569
570 l_xml_header_length := length(l_xml_header);
571 IF l_rows_processed <> 0 THEN
572 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
573 dbms_lob.copy(tempResult,l_result
574 ,dbms_lob.getlength(l_result)-l_resultOffset
575 ,l_xml_header_length,l_resultOffset);
576 ELSE
577 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
578 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
579 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
580 END IF;
581 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added content');
582
583 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
584 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Added closing tag');
585
586 print_clob(lob_loc => tempResult);
587 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
588 EXCEPTION
589 WHEN OTHERS THEN
590 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
591 || ' -');
592 RAISE;
593 END PROCESS_PORTFOLIO_REPORT;
594 PROCEDURE PROCESS_RECON_REPORT(ERRBUF OUT NOCOPY VARCHAR2
595 ,RETCODE OUT NOCOPY VARCHAR2
596 ,LOAN_CLASS IN VARCHAR2
597 ,LOAN_TYPE_ID IN NUMBER
598 ,CURRENCY_CODE IN VARCHAR2
599 ,BILL_DUE_DATE_FROM IN VARCHAR2
600 ,BILL_DUE_DATE_TO IN VARCHAR2
601 ,LEGAL_ENTITY_ID IN NUMBER
602 ,LOAN_NUMBER IN VARCHAR2
603 ,BORROWER_ID IN NUMBER
604 ,CUST_ACCOUNT_ID IN NUMBER
605 ,LOAN_ASSIGNED_TO IN NUMBER
606 ) IS
607 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_RECON_REPORT';
608 l_api_version CONSTANT NUMBER := 1.0;
609 ctx DBMS_XMLQUERY.ctxType;
610 result CLOB;
611 qryCtx DBMS_XMLGEN.ctxHandle;
612 l_result CLOB;
613 tempResult CLOB;
614 l_version varchar2(20);
615 l_compatibility varchar2(20);
616 l_suffix varchar2(2);
617 l_majorVersion number;
618 l_resultOffset number;
619 l_xml_header varchar2(3000);
620 l_xml_header_length number;
621 l_errNo NUMBER;
622 l_errMsg VARCHAR2(200);
623 queryCtx DBMS_XMLquery.ctxType;
624 l_xml_query VARCHAR2(32767);
625 TYPE ref_cur IS REF CURSOR;
626 l_xml_stmt ref_cur;
627 l_rows_processed NUMBER;
628 l_new_line VARCHAR2(1);
629 l_org_id hr_operating_units.organization_id%TYPE;
630 l_org_name hr_operating_units.NAME%TYPE;
631 l_borrower_name hz_parties.party_name%TYPE;
632 l_account_number hz_cust_accounts.account_number%TYPE;
633 l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
634 l_start_date_from lns_loan_headers_all.loan_start_date%TYPE;
635 l_start_date_to lns_loan_headers_all.loan_start_date%TYPE;
636 l_loan_type_desc lns_loan_types.loan_type_desc%TYPE;
637 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
638 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
639 l_legal_entity_name xle_entity_profiles.name%TYPE;
640 l_loan_officer jtf_rs_resource_extns.source_name%TYPE;
641 l_loan_status1_desc lns_lookups.meaning%TYPE;
642 l_loan_status2_desc lns_lookups.meaning%TYPE;
643 l_close_tag VARCHAR2(100);
644 l_query VARCHAR2(11000) :=
645 'select ' ||
646 'lh.LOAN_ID, ' ||
647 'lh.loan_number ' ||
648 ',hp.party_name customer ' ||
649 ',llk.meaning loan_status_meaning ' ||
650 ',lh.loan_status ' ||
651 ',pay.TOTAL_PRINCIPAL_BALANCE loan_balance ' ||
652 ',lh.loan_description ' ||
653 ',account.ACCOUNT_NUMBER ' ||
654 ',lh.FUNDED_AMOUNT original_loan_amount ' ||
655 ',to_char(lh.loan_maturity_date, ''YYYY-MM-DD'') loan_maturity_date ' ||
656 ',lh.ORG_ID ' ||
657 ',lot.loan_type_name loan_type ' ||
658 ',lh.LOAN_CLASS_CODE ' ||
659 ',lh.LOAN_CURRENCY ' ||
660 ',(loc.address1 || '' '' || loc.city || '' '' || loc.state || '' '' || ' ||
661 'loc.postal_code || '' '' || terr.TERRITORY_SHORT_NAME) Address ' ||
662 ',ou.name operating_unit ' ||
663 ',lh.LEGAL_ENTITY_ID ' ||
664 ',le.NAME LEGAL_ENTITY_NAME ' ||
665 ',lh.product_id ' ||
666 ',product.loan_product_name loan_product ' ||
667 ',nvl((select sum(line.line_amount) ' ||
668 ' from lns_disb_lines line, ' ||
669 ' lns_disb_headers head ' ||
670 ' where head.loan_id = lh.LOAN_ID and ' ||
671 ' head.disb_header_id = line.disb_header_id and ' ||
672 ' line.status = ''FULLY_FUNDED''), 0) disbursed_amount ' ||
673 ',nvl(lh.CURRENT_PHASE, ''TERM'') current_phase ' ||
674 ', CURSOR (select ' ||
675 ' LOAN_ID loan_id, ' ||
676 ' REFERENCE_NUMBER original_rec_number, ' ||
677 ' REFERENCE_AMOUNT original_rec_balance, ' ||
678 ' REQUESTED_AMOUNT loan_requested_amount, ' ||
679 ' REFERENCE_NUMBER original_rec_desc ' ||
680 ' from LNS_LOAN_LINES ' ||
681 ' where loan_id = lh.LOAN_ID ' ||
682 ' and end_date is null ' ||
683 ') AS ORIGINAL_RECEIVABLES ' ||
684 ', CURSOR ( ' ||
685 ' select ' ||
686 ' max(amv.loan_id) loan_id, ' ||
687 ' sum(amv.PRINCIPAL_AMOUNT) prin_amortization_amount, ' ||
688 ' sum(amv.PRIN_CASH) prin_receipt_payments, ' ||
689 ' sum(amv.PRIN_NET_CREDIT) prin_credit_netting, ' ||
690 ' sum(amv.PRIN_LOAN_PMT_CREDIT) prin_credit_payments, ' ||
691 ' sum(amv.PRIN_OTHER_CREDIT) prin_credit_non_payments, ' ||
692 ' sum(amv.PRIN_ADJ) prin_adjustments, ' ||
693 ' sum(amv.PRINCIPAL_REMAINING) prin_remaining_amount, ' ||
694 ' sum(amv.INTEREST_AMOUNT) int_amortization_amount, ' ||
695 ' sum(amv.INT_CASH) int_receipt_payments, ' ||
696 ' sum(amv.INT_NET_CREDIT) int_credit_netting, ' ||
697 ' sum(amv.INT_LOAN_PMT_CREDIT) int_credit_payments, ' ||
698 ' sum(amv.INT_OTHER_CREDIT) int_credit_non_payments, ' ||
699 ' sum(amv.INT_ADJ) int_adjustments, ' ||
700 ' sum(amv.INTEREST_REMAINING) int_remaining_amount, ' ||
701 ' sum(amv.FEE_AMOUNT) fee_amortization_amount, ' ||
702 ' sum(amv.FEE_CASH) fee_receipt_payments, ' ||
703 ' sum(amv.FEE_NET_CREDIT) fee_credit_netting, ' ||
704 ' sum(amv.FEE_LOAN_PMT_CREDIT) fee_credit_payments, ' ||
705 ' sum(amv.FEE_OTHER_CREDIT) fee_credit_non_payments, ' ||
706 ' sum(amv.FEE_ADJ) fee_adjustments, ' ||
707 ' sum(amv.FEE_REMAINING) fee_remaining_amount ' ||
708 ' from ' ||
709 ' LNS_AM_SCHEDS_DTL_V amv ' ||
710 ' where amv.loan_id = lh.LOAN_ID and ' ||
711 ' amv.REVERSED_CODE = ''N'' and ' ||
712 ' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
713 ') AS BILL_PAY_SUMMARY ' ||
714 ', CURSOR ( ' ||
715 ' select ' ||
716 ' am.PAYMENT_NUMBER, ' ||
717 ' lok.MEANING purpose, ' ||
718 ' decode(trx.customer_trx_id, ' ||
719 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
720 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
721 ' am.FEE_TRX_ID, am.FEE_AMOUNT) amortization_amount, ' ||
722 ' psa.AMOUNT_DUE_ORIGINAL ar_trx_amount, ' ||
723 ' (decode(trx.customer_trx_id, ' ||
724 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
725 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
726 ' am.FEE_TRX_ID, am.FEE_AMOUNT) ' ||
727 ' - psa.AMOUNT_DUE_ORIGINAL) difference, ' ||
728 ' trx.INTERFACE_HEADER_ATTRIBUTE1 ar_trx_reference ' ||
729 ' from ' ||
730 ' lns_amortization_scheds am, ' ||
731 ' ar_payment_schedules_all psa, ' ||
732 ' RA_CUSTOMER_TRX_ALL trx, ' ||
733 ' LNS_LOOKUPS lok ' ||
734 ' where am.loan_id = lh.LOAN_ID and ' ||
735 ' (am.REVERSED_FLAG is null or am.REVERSED_FLAG = ''N'') and ' ||
736 ' trunc(am.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt and ' ||
737 ' (trx.customer_trx_id = am.PRINCIPAL_TRX_ID or ' ||
738 ' trx.customer_trx_id = am.INTEREST_TRX_ID or ' ||
739 ' trx.customer_trx_id = am.FEE_TRX_ID) and ' ||
740 ' psa.customer_trx_id = trx.customer_trx_id and ' ||
741 ' lok.lookup_type = ''PAYMENT_APPLICATION_TYPE'' and ' ||
742 ' lok.lookup_code = decode(trx.customer_trx_id, am.PRINCIPAL_TRX_ID, ' ||
743 ' ''PRIN'', am.INTEREST_TRX_ID, ''INT'', am.FEE_TRX_ID, ''FEES'') and ' ||
744 ' (decode(trx.customer_trx_id, ' ||
745 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
746 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
747 ' am.FEE_TRX_ID, am.FEE_AMOUNT) <> psa.AMOUNT_DUE_ORIGINAL) ' ||
748 ') AS EXCEPTION_FLAGS , ' ||
749 'CURSOR ( ' ||
750 ' select ' ||
751 ' amv.PAYMENT_NUMBER, ' ||
752 ' amv.BILL_TYPE_DESC meaning, ' ||
753 ' amv.PRINCIPAL_AMOUNT billed_principal_amount, ' ||
754 ' amv.PRIN_CASH prin_receipt_payments, ' ||
755 ' amv.PRIN_NET_CREDIT prin_credit_netting, ' ||
756 ' amv.PRIN_LOAN_PMT_CREDIT prin_credit_payments, ' ||
757 ' amv.PRIN_OTHER_CREDIT prin_credit_non_payments, ' ||
758 ' amv.PRIN_ADJ prin_adjustments, ' ||
759 ' amv.PRINCIPAL_REMAINING prin_remaining_amount, ' ||
760 ' amv.INTEREST_AMOUNT billed_int_amount, ' ||
761 ' amv.INT_CASH int_receipt_payments, ' ||
762 ' amv.INT_NET_CREDIT int_credit_netting, ' ||
763 ' amv.INT_LOAN_PMT_CREDIT int_credit_payments, ' ||
764 ' amv.INT_OTHER_CREDIT int_credit_non_payments, ' ||
765 ' amv.INT_ADJ int_adjustments, ' ||
766 ' amv.INTEREST_REMAINING int_remaining_amount, ' ||
767 ' amv.FEE_AMOUNT billed_fee_amount, ' ||
768 ' amv.FEE_CASH fee_receipt_payments, ' ||
769 ' amv.FEE_NET_CREDIT fee_credit_netting, ' ||
770 ' amv.FEE_LOAN_PMT_CREDIT fee_credit_payments, ' ||
771 ' amv.FEE_OTHER_CREDIT fee_credit_non_payments, ' ||
772 ' amv.FEE_ADJ fee_adjustments, ' ||
773 ' amv.FEE_REMAINING fee_remaining_amount, ' ||
774 ' to_char(amv.DUE_DATE,''YYYY-MM-DD'') due_date, ' ||
775 ' amv.PHASE, ' ||
776 ' CURSOR ( ' ||
777 ' select ' ||
778 ' rec.PAYMENT_NUMBER, ' ||
779 ' rec.line_type_desc, ' ||
780 ' rec.line_desc, ' ||
781 ' rec.activity_desc, ' ||
782 ' rec.activity_amount, ' ||
783 ' rec.activity_number, ' ||
784 ' to_char(rec.activity_date,''YYYY-MM-DD'') activity_date, ' ||
785 ' rec.trx_currency, ' ||
786 ' rec.receipt_amt_applied_from, ' ||
787 ' rec.receipt_currency, ' ||
788 ' rec.trx_to_receipt_rate ' ||
789 ' from ' ||
790 ' LNS_REC_ACT_CASH_CM_V rec ' ||
791 ' where ' ||
792 ' amv.loan_id = rec.loan_id and ' ||
793 ' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
794 ' ) AS PAYMENT_ACTIVITY_CASH_CM, ' ||
795 ' CURSOR ( ' ||
796 ' select ' ||
797 ' rec.PAYMENT_NUMBER, ' ||
798 ' rec.line_type_desc, ' ||
799 ' rec.line_desc, ' ||
800 ' rec.activity_desc, ' ||
801 ' rec.activity_amount, ' ||
802 ' rec.activity_number, ' ||
803 ' to_char(rec.activity_date,''YYYY-MM-DD'') activity_date, ' ||
804 ' rec.trx_currency, ' ||
805 ' rec.receipt_amt_applied_from, ' ||
806 ' rec.receipt_currency, ' ||
807 ' rec.trx_to_receipt_rate ' ||
808 ' from ' ||
809 ' LNS_REC_ACT_ADJ_V rec ' ||
810 ' where ' ||
811 ' amv.loan_id = rec.loan_id and ' ||
812 ' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
813 ' ) AS PAYMENT_ACTIVITY_ADJ ' ||
814 ' from ' ||
815 ' LNS_AM_SCHEDS_DTL_V amv ' ||
816 ' where amv.loan_id = lh.LOAN_ID and ' ||
817 ' amv.REVERSED_CODE = ''N'' and ' ||
818 ' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
819 ') AS BILL_PAY_DTL_BY_PAY_NUM, ' ||
820 'CURSOR( ' ||
821 ' select head.DISB_HEADER_ID, ' ||
822 ' head.DISBURSEMENT_NUMBER, ' ||
823 ' to_char(head.TARGET_DATE, ''MM/DD/YYYY'') target_date, ' ||
824 ' to_char(head.PAYMENT_REQUEST_DATE, ''MM/DD/YYYY'') payment_request_date, ' ||
825 ' head.HEADER_PERCENT, ' ||
826 ' head.HEADER_AMOUNT, ' ||
827 ' fund_status.meaning status, ' ||
828 ' fund_act.meaning activity_name, ' ||
829 ' (select to_char(max(DISBURSEMENT_DATE),''YYYY-MM-DD'') from lns_disb_lines where DISB_HEADER_ID = head.DISB_HEADER_ID) DISBURSEMENT_DATE ' ||
830 ' from lns_disb_headers head, ' ||
831 ' lns_lookups fund_status, ' ||
832 ' lns_lookups fund_act ' ||
833 ' where head.loan_id = lh.loan_id and ' ||
834 ' fund_status.lookup_type(+) = ''FUNDING_STATUS'' and ' ||
835 ' fund_status.lookup_code(+) = head.STATUS and ' ||
836 ' fund_act.lookup_type(+) = ''DISB_ACTIVITY'' and ' ||
837 ' fund_act.lookup_code(+) = head.ACTIVITY_CODE) ' ||
838 'AS Disbursement_Schedule ' ||
839 'from ' ||
840 'lns_loan_headers_all_vl lh, ' ||
841 '(select lns_rep_utils.get_bill_due_date_from() from_dt, ' ||
842 ' lns_rep_utils.get_bill_due_date_to() to_dt ' ||
843 ' from dual) dateparameters, ' ||
844 'hz_parties hp, ' ||
845 'lns_lookups llk, ' ||
846 'LNS_PAY_SUM_V pay, ' ||
847 'hz_cust_accounts_all account, ' ||
848 'hz_locations loc, ' ||
849 'fnd_territories_vl terr, ' ||
850 'hz_party_sites site, ' ||
851 'hz_cust_acct_sites_all acct_site, ' ||
852 'lns_loan_products_vl product, ' ||
853 'xle_entity_profiles le, ' ||
854 'hr_operating_units ou, ' ||
855 'lns_loan_types_vl lot ' ||
856 'where ' ||
857 'lh.primary_borrower_id = hp.party_id and ' ||
858 'lh.loan_id = pay.loan_id and ' ||
859 'llk.lookup_code = lh.loan_status and ' ||
860 'llk.lookup_type = ''LOAN_STATUS'' and ' ||
861 'lh.loan_status <> ''DELETED'' and ' ||
862 'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
863 'acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and ' ||
864 'acct_site.org_id = lh.org_id and ' ||
865 'site.party_site_id = acct_site.party_site_id and ' ||
866 'site.location_id = loc.location_id and ' ||
867 'loc.country = terr.TERRITORY_CODE and ' ||
868 'lh.loan_class_code = :LOAN_CLASS and ' ||
869 'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
870 'lh.loan_currency = :CURRENCY_CODE and ' ||
871 'lh.product_id = product.LOAN_PRODUCT_ID(+) and ' ||
872 'le.LEGAL_ENTITY_ID = lh.LEGAL_ENTITY_ID and ' ||
873 'ou.organization_id = lh.org_id and ' ||
874 'lh.org_id = :ORG_ID and ' ||
875 'lot.loan_type_id = lh.loan_type_id and ' ||
876 'EXISTS ' ||
877 ' (select loan_id ' ||
878 ' from lns_amortization_scheds am ' ||
879 ' where am.loan_id = lh.loan_id and ' ||
880 ' (REVERSED_FLAG is null or REVERSED_FLAG = ''N'') and ' ||
881 ' trunc(am.DUE_DATE) between lns_rep_utils.get_bill_due_date_from() ' ||
882 ' and lns_rep_utils.get_bill_due_date_to())';
883 l_temp_where_clause VARCHAR2(200);
884 BEGIN
885 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
886 l_loan_type_id := loan_type_id;
887 l_legal_entity_id := legal_entity_id;
888 g_bill_due_date_from := trunc(fnd_date.canonical_to_date(bill_due_date_from));
889 g_bill_due_date_to := trunc(fnd_date.canonical_to_date(bill_due_date_to));
890 l_cust_account_id := cust_account_id;
891 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
892 BEGIN
893 SELECT loan_type_desc
894 into l_loan_type_desc
895 from lns_loan_types_vl
896 where loan_type_id = l_loan_type_id;
897 EXCEPTION
898 WHEN NO_DATA_FOUND THEN null;
899 END;
900 -- Build Bind Placeholders for all non-mandatory parameters
901
902 IF loan_number IS NOT NULL
903 THEN
904 l_query := l_query || ' and lh.loan_number like :LOAN_NUMBER';
905 END IF;
906 IF borrower_id IS NOT NULL
907 THEN
908 l_query := l_query || ' and lh.primary_borrower_id = :BORROWER_ID';
909 END IF;
910 IF cust_account_id IS NOT NULL
911 THEN
912 l_query := l_query || ' and lh.cust_account_id = :CUST_ACCOUNT_ID';
913 END IF;
914 IF legal_entity_id IS NOT NULL
915 THEN
916 l_query := l_query || ' and lh.legal_entity_id = :LEGAL_ENTITY_ID';
917 END IF;
918 IF loan_assigned_to IS NOT NULL
919 THEN
920 l_query := l_query || ' and lh.loan_assigned_to = :LOAN_ASSIGNED_TO';
921 END IF;
922
923 -- Start Getting Parameter Display Values
924 SELECT name
925 INTO l_org_name
926 FROM HR_ALL_ORGANIZATION_UNITS_TL
927 WHERE organization_id = l_org_id
928 AND language = userenv('LANG');
929
930 IF borrower_id is NOT NULL
931 THEN
932 SELECT PARTY_NAME
933 INTO l_borrower_name
934 FROM hz_parties
935 WHERE party_id = borrower_id;
936 END IF;
937
938 IF cust_account_id is NOT NULL
939 THEN
940 SELECT account_number
941 into l_account_number
942 FROM hz_cust_accounts
943 WHERE cust_account_id = l_cust_account_id;
944 END IF;
945 IF legal_entity_id is NOT NULL
946 THEN
947 SELECT NAME
948 INTO l_legal_entity_name
949 FROM xle_entity_profiles
950 WHERE legal_entity_id = l_legal_entity_id;
951 END IF;
952 IF loan_assigned_to is NOT NULL
953 THEN
954 SELECT source_name
955 INTO l_loan_officer
956 FROM jtf_rs_resource_extns
957 WHERE resource_id = loan_assigned_to;
958 END IF;
959 ctx := DBMS_XMLQUERY.newContext(l_query);
960 DBMS_XMLQuery.setRaiseNoRowsException(ctx,TRUE);
961 -- Bind Mandatory Variables
962 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_CLASS', loan_class);
963 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_TYPE_ID', loan_type_id);
964 DBMS_XMLQuery.setBindValue(ctx, 'CURRENCY_CODE', currency_code);
965 DBMS_XMLQuery.setBindValue(ctx, 'ORG_ID', l_org_id);
966
967 -- Bind Optional Variables if they are NOT NULL
968 IF borrower_id is NOT NULL
969 THEN
970 DBMS_XMLQuery.setBindValue(ctx, 'BORROWER_ID', borrower_id);
971 END IF;
972 IF loan_number is NOT NULL
973 THEN
974 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_NUMBER', loan_number);
975 END IF;
976 IF cust_account_id is NOT NULL
977 THEN
978 DBMS_XMLQuery.setBindValue(ctx, 'CUST_ACCOUNT_ID', cust_account_id);
979 END IF;
980 IF legal_entity_id is NOT NULL
981 THEN
982 DBMS_XMLQuery.setBindValue(ctx, 'LEGAL_ENTITY_ID', legal_entity_id);
983 END IF;
984 IF loan_assigned_to is NOT NULL
985 THEN
986 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_ASSIGNED_TO', loan_assigned_to);
987 END IF;
988
989 -- now get the result
990 BEGIN
991 l_result := DBMS_XMLQUERY.getXML(ctx);
992 DBMS_XMLQuery.closeContext(ctx);
993 l_rows_processed := 1;
994 EXCEPTION
995 WHEN OTHERS THEN
996 DBMS_XMLQuery.getExceptionContent(ctx,l_errNo,l_errMsg);
997 IF l_errNo = 1403 THEN
998 l_rows_processed := 0;
999 END IF;
1000 DBMS_XMLQuery.closeContext(ctx);
1001 END;
1002 -- We are adding the LNSPORTFOLIO and PARAMETERS TAGs so we have
1003 -- to offset the first line.
1004 IF l_rows_processed <> 0 THEN
1005 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1006 tempResult := l_result;
1007 ELSE
1008 l_resultOffset := 0;
1009 END IF;
1010
1011 l_new_line := '
1012 ';
1013 /* Prepare the tag for the report heading */
1014 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
1015 l_xml_header := l_xml_header ||l_new_line||'<LNSRECONCILIATION>';
1016 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1017 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||REPLACE_SPECIAL_CHARS(l_org_name)||'</ORG_NAME>';
1018 l_xml_header := l_xml_header ||l_new_line||' <LOAN_CLASS_CODE>' ||REPLACE_SPECIAL_CHARS(loan_class) ||'</LOAN_CLASS_CODE>';
1019 l_xml_header := l_xml_header ||l_new_line||' <LOAN_TYPE_DESC>' ||REPLACE_SPECIAL_CHARS(l_loan_type_desc) ||'</LOAN_TYPE_DESC>';
1020 l_xml_header := l_xml_header ||l_new_line||' <CURRENCY_CODE>' ||REPLACE_SPECIAL_CHARS(currency_code) ||'</CURRENCY_CODE>';
1021 l_xml_header := l_xml_header ||l_new_line||' <BILL_DUE_DATE_FROM>' || to_char(to_date(bill_due_date_from,'YYYY/MM/DD HH24:MI:SS'),'YYYY-MM-DD') ||'</BILL_DUE_DATE_FROM>';
1022 l_xml_header := l_xml_header ||l_new_line||' <BILL_DUE_DATE_TO>' || to_char(to_date(bill_due_date_to,'YYYY/MM/DD HH24:MI:SS'),'YYYY-MM-DD') ||'</BILL_DUE_DATE_TO>';
1023 l_xml_header := l_xml_header ||l_new_line||' <LEGAL_ENTITY_NAME>' ||REPLACE_SPECIAL_CHARS(l_legal_entity_name) ||'</LEGAL_ENTITY_NAME>';
1024 l_xml_header := l_xml_header ||l_new_line||' <LOAN_NUMBER>' ||REPLACE_SPECIAL_CHARS(loan_number) ||'</LOAN_NUMBER>';
1025 l_xml_header := l_xml_header ||l_new_line||' <BORROWER_NAME>' ||REPLACE_SPECIAL_CHARS(l_borrower_name) ||'</BORROWER_NAME>';
1026 l_xml_header := l_xml_header ||l_new_line||' <ACCOUNT_NUMBER>' ||l_account_number ||'</ACCOUNT_NUMBER>';
1027 l_xml_header := l_xml_header ||l_new_line||' <LOAN_OFFICER>' ||REPLACE_SPECIAL_CHARS(l_loan_officer) ||'</LOAN_OFFICER>';
1028 l_xml_header := l_xml_header ||l_new_line||' <REPORT_GENERATION_DATE>' || to_char(sysdate,'YYYY-MM-DD') || '</REPORT_GENERATION_DATE>';
1029 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
1030 l_close_tag := l_new_line||'</LNSRECONCILIATION>'||l_new_line;
1031 l_xml_header_length := length(l_xml_header);
1032 IF l_rows_processed <> 0 THEN
1033 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
1034 dbms_lob.copy(tempResult,l_result
1035 ,dbms_lob.getlength(l_result)-l_resultOffset
1036 ,l_xml_header_length,l_resultOffset);
1037 ELSE
1038 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1039 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1040 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1041 END IF;
1042
1043 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1044 print_clob(lob_loc => tempResult);
1045 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1049 || ' -');
1050 RAISE;
1051 END PROCESS_RECON_REPORT;
1052
1053 PROCEDURE GEN_AGREEMENT_XML(p_AgreementXML OUT NOCOPY CLOB
1054 ,p_LOAN_ID IN NUMBER
1055 ,p_based_on_terms IN VARCHAR2)
1056 IS
1057 l_api_name CONSTANT VARCHAR2(30) := 'GEN_AGREEMENT_XML';
1058 l_api_version CONSTANT NUMBER := 1.0;
1059 ctx DBMS_XMLQUERY.ctxType;
1060 result CLOB;
1061 qryCtx DBMS_XMLGEN.ctxHandle;
1062 l_result CLOB;
1063 tempResult CLOB;
1064 l_version varchar2(20);
1065 l_compatibility varchar2(20);
1066 l_suffix varchar2(2);
1067 l_majorVersion number;
1068 l_resultOffset number;
1069 l_xml_header varchar2(3000);
1070 l_xml_header_length number;
1071 l_close_tag VARCHAR2(100);
1072 l_errNo NUMBER;
1073 l_errMsg VARCHAR2(200);
1074 queryCtx DBMS_XMLquery.ctxType;
1075 l_xml_query VARCHAR2(32767);
1076 TYPE ref_cur IS REF CURSOR;
1077 l_xml_stmt ref_cur;
1078 l_rows_processed NUMBER;
1079 l_new_line VARCHAR2(1);
1080 l_org_name hr_operating_units.NAME%TYPE;
1081 l_sob_currency_code gl_sets_of_books.CURRENCY_CODE%TYPE;
1082 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1083 l_return_status VARCHAR2(10);
1084 l_msg_count NUMBER;
1085 l_msg_data VARCHAR2(32767);
1086
1087 type INSTALLMENT_NUMBER_tab_t is table of lns_amort_scheds_gt.INSTALLMENT_NUMBER%TYPE
1088 index by pls_integer;
1089 v_INSTALLMENT_NUMBER INSTALLMENT_NUMBER_tab_t;
1090 type DUE_DATE_tab_t is table of lns_amort_scheds_gt.DUE_DATE%TYPE
1091 index by pls_integer;
1092 v_DUE_DATE DUE_DATE_tab_t;
1093 type PRINCIPAL_AMOUNT_tab_t is table of lns_amort_scheds_gt.PRINCIPAL_AMOUNT%TYPE
1094 index by pls_integer;
1095 v_PRINCIPAL_AMOUNT PRINCIPAL_AMOUNT_tab_t;
1096 type INTEREST_AMOUNT_tab_t is table of lns_amort_scheds_gt.INTEREST_AMOUNT%TYPE
1097 index by pls_integer;
1098 v_INTEREST_AMOUNT INTEREST_AMOUNT_tab_t;
1099 type FEE_AMOUNT_tab_t is table of lns_amort_scheds_gt.FEE_AMOUNT%TYPE
1100 index by pls_integer;
1101 v_FEE_AMOUNT FEE_AMOUNT_tab_t;
1102 type OTHER_AMOUNT_tab_t is table of lns_amort_scheds_gt.OTHER_AMOUNT%TYPE
1103 index by pls_integer;
1104 v_OTHER_AMOUNT OTHER_AMOUNT_tab_t;
1105 type BEGIN_BALANCE_tab_t is table of lns_amort_scheds_gt.BEGIN_BALANCE%TYPE
1106 index by pls_integer;
1107 v_BEGIN_BALANCE BEGIN_BALANCE_tab_t;
1108 type END_BALANCE_tab_t is table of lns_amort_scheds_gt.END_BALANCE%TYPE
1109 index by pls_integer;
1110 v_END_BALANCE END_BALANCE_tab_t;
1111 type TOTAL_tab_t is table of lns_amort_scheds_gt.TOTAL%TYPE
1112 index by pls_integer;
1113 v_TOTAL TOTAL_tab_t;
1114 type INTEREST_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.INTEREST_CUMULATIVE%TYPE
1115 index by pls_integer;
1116 v_INTEREST_CUMULATIVE INTEREST_CUMULATIVE_tab_t;
1117 type PRINCIPAL_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.PRINCIPAL_CUMULATIVE%TYPE
1118 index by pls_integer;
1119 v_PRINCIPAL_CUMULATIVE PRINCIPAL_CUMULATIVE_tab_t;
1120 type FEES_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.FEES_CUMULATIVE%TYPE
1121 index by pls_integer;
1122 v_FEES_CUMULATIVE FEES_CUMULATIVE_tab_t;
1123 type OTHER_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.OTHER_CUMULATIVE%TYPE
1124 index by pls_integer;
1125 v_OTHER_CUMULATIVE OTHER_CUMULATIVE_tab_t;
1126 type RATE_ID_tab_t is table of lns_amort_scheds_gt.RATE_ID%TYPE
1127 index by pls_integer;
1128 v_RATE_ID RATE_ID_tab_t;
1129 type SOURCE_tab_t is table of lns_amort_scheds_gt.SOURCE%TYPE
1130 index by pls_integer;
1131 v_SOURCE SOURCE_tab_t;
1132 type GRAND_TOTAL_FLAG_tab_t is table of lns_amort_scheds_gt.GRAND_TOTAL_FLAG%TYPE
1133 index by pls_integer;
1134 v_GRAND_TOTAL_FLAG GRAND_TOTAL_FLAG_tab_t;
1135 type PREV_DEFERRED_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.PREV_DEFERRED_INT_AMOUNT%TYPE
1136 index by pls_integer;
1137 v_PREV_DEFERRED_INT_AMOUNT PREV_DEFERRED_INT_AMOUNT_tab_t;
1138 type DEFERRED_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.DEFERRED_INT_AMOUNT%TYPE
1139 index by pls_integer;
1140 v_DEFERRED_INT_AMOUNT DEFERRED_INT_AMOUNT_tab_t;
1141 type PREV_CAP_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.PREV_CAP_INT_AMOUNT%TYPE
1142 index by pls_integer;
1143 v_PREV_CAP_INT_AMOUNT PREV_CAP_INT_AMOUNT_tab_t;
1144 type CURR_CAP_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.CURR_CAP_INT_AMOUNT%TYPE
1145 index by pls_integer;
1146 v_CURR_CAP_INT_AMOUNT CURR_CAP_INT_AMOUNT_tab_t;
1147 type CAP_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.CAP_INT_AMOUNT%TYPE
1148 index by pls_integer;
1149 v_CAP_INT_AMOUNT CAP_INT_AMOUNT_tab_t;
1150 type NORMAL_INT_AMOUNT_tab_t is table of lns_amort_scheds_gt.NORMAL_INT_AMOUNT%TYPE
1151 index by pls_integer;
1152 v_NORMAL_INT_AMOUNT NORMAL_INT_AMOUNT_tab_t;
1153 type DISBURSEMENT_AMOUNT_tab_t is table of lns_amort_scheds_gt.DISBURSEMENT_AMOUNT%TYPE
1154 index by pls_integer;
1155 v_DISBURSEMENT_AMOUNT DISBURSEMENT_AMOUNT_tab_t;
1156 type NORMAL_INT_DETAILS_tab_t is table of lns_amort_scheds_gt.NORMAL_INT_DETAILS%TYPE
1157 index by pls_integer;
1158 v_NORMAL_INT_DETAILS NORMAL_INT_DETAILS_tab_t;
1159 type DEFERRED_INT_DETAILS_tab_t is table of lns_amort_scheds_gt.DEFERRED_INT_DETAILS%TYPE
1160 index by pls_integer;
1161 v_DEFERRED_INT_DETAILS DEFERRED_INT_DETAILS_tab_t;
1162 type CAP_INT_DETAILS_tab_t is table of lns_amort_scheds_gt.CAP_INT_DETAILS%TYPE
1163 index by pls_integer;
1164 v_CAP_INT_DETAILS CAP_INT_DETAILS_tab_t;
1165
1166 --Bug5262505 --karamach
1167 l_total_principal_balance NUMBER;
1168 CURSOR c_get_principal_balance(pLoanId Number) IS
1169 select pay.TOTAL_PRINCIPAL_BALANCE loan_amount
1170 from LNS_PAY_SUM_V pay
1171 where pay.loan_id = pLoanId;
1172
1173 CURSOR C_agreement (X_Loan_Id NUMBER
1174 ,pTotalPrincipalBalance Number) IS
1175 select sys_XMLGen(
1176 XMLELEMENT("ROW", XMLATTRIBUTES (1 AS "num"),
1177 XMLFOREST (
1178 lh.LOAN_ID,
1179 lh.loan_number
1180 ,lh.multiple_funding_flag
1181 ,xle.name lending_org_name
1182 ,hp.party_name borrower_name
1183 ,(lh.REQUESTED_AMOUNT + nvl(lh.ADD_REQUESTED_AMOUNT, 0)) loan_requested_amount
1184 ,lh.LOAN_CURRENCY
1185 ,llklc.meaning LOAN_CLASS
1186 ,llt.loan_type_name LOAN_TYPE
1187 ,to_char(lh.LOAN_APPLICATION_DATE, 'YYYY-MM-DD') LOAN_APPLICATION_DATE
1188 ,to_char(lh.LOAN_START_DATE, 'YYYY-MM-DD') LOAN_START_DATE
1189 ,lh.loan_term || ' ' || llktt.meaning loan_term
1190 ,ratesch.current_interest_rate initial_interest_rate
1191 ,to_char(t.first_payment_date, 'YYYY-MM-DD') payment_start_date
1192 ,t.loan_payment_frequency payment_frequency_code
1193 ,llkfq.meaning payment_frequency
1194 , llkrt.meaning interest_type
1195 ,res.source_name loan_officer
1196 ,llkst.meaning loan_subtype
1197 ,nvl(lh.collateral_percent,0) COLLATERAL_PERCENTAGE
1198 ,llkp.meaning loan_purpose
1199 ,intrt.interest_rate_name index_name
1200 ,llkdc.meaning day_count_method
1201 ,llkic.meaning interest_calculation_method
1202 ,t.calculation_method int_calc_method_code
1203 ,t.day_count_method day_count_method_code
1204 ,t.delinquency_threshold_amount delinquency_overdue_amount
1205 ,nvl(t.reamortize_over_payment,'N') reamortize_over_payment
1206 ,t.ceiling_rate
1207 ,t.floor_rate
1208 ,to_char(t.lock_in_date,'YYYY-MM-DD') lock_in_date
1209 ,to_char(t.lock_to_date,'YYYY-MM-DD') lock_expiration_date
1210 ,llkfqf.meaning floating_frequency
1211 ,llkfqf.meaning open_payment_frequency
1212 ,to_char(t.open_first_payment_date,'YYYY-MM-DD') open_first_payment_date
1213 ,to_char(ldh.target_date,'YYYY-MM-DD') first_disbursement_date
1214 ,ldh.header_percent first_disbursement_percent
1215 ,ldh.header_amount first_disbursement_amount
1216 ,lh.open_to_term_flag
1217 ,decode(nvl(lh.open_to_term_flag,'N'),'Y','with','without') open_to_term_str
1218 ,nvl(lh.funded_amount,lh.requested_amount) * nvl(lh.collateral_percent,0)
1219 / 100 collateral_required
1220 ,lh.collateral_percent loan_to_value_ratio
1221 ,lh.loan_status
1222 --,pay.TOTAL_PRINCIPAL_BALANCE loan_amount --Bug5262505
1223 ,pTotalPrincipalBalance loan_amount
1224 ,lh.loan_description
1225 ,to_char(lh.loan_maturity_date, 'YYYY-MM-DD') loan_maturity_date
1226 ,lh.ORG_ID
1227 ,lh.LOAN_TYPE LOAN_TYPE_CODE
1228 ,lh.LOAN_CLASS_CODE
1229 ,LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate
1230 ,lh.LOAN_SUBTYPE
1231 ,lh.LAST_BILLED_DATE
1232 ,to_char(lh.LOAN_APPROVAL_DATE, 'YYYY-MM-DD') LOAN_APPROVAL_DATE
1233 ,loc.address1 || ' ' || loc.city || ' ' || loc.state || ' ' ||
1234 loc.postal_code || ' ' || terr.TERRITORY_SHORT_NAME primary_borrower_addr
1235 ,xle.ADDRESS_LINE_1 || ' ' || xle.ADDRESS_LINE_2 || ' ' || xle.ADDRESS_LINE_3 || ' ' ||
1236 xle.POSTAL_CODE || ' ' ||xle.COUNTRY Lender_address
1237 ,to_char(lh.loan_approval_date, 'YYYY-MM-DD') loan_approval_date
1238 ,lh.exchange_rate_type
1239 ,lh.exchange_rate
1240 ,to_char(lh.exchange_date, 'YYYY-MM-DD') exchange_date
1241 ,product.loan_product_name loan_product
1242 ),
1243 -- Loan_Participants
1244 (select
1245 XMLELEMENT("LOAN_PARTICIPANTS",
1246 XMLAGG(
1247 XMLELEMENT("LOAN_PARTICIPANTS_ROW", XMLATTRIBUTES (rownum AS "num"),
1248 XMLFOREST(
1249 party.party_name participant_name,
1250 party.JGZZ_FISCAL_CODE participant_tax_id,
1251 lkup.meaning participant_type,
1252 party.party_type participant_type_code,
1253 party.party_number participant_number,
1254 lnslkup.meaning participant_role,
1255 lp.LOAN_PARTICIPANT_TYPE participant_role_code,
1256 party.address1 || ' ' || party.address2 || ' '
1257 || party.address3 || ' ' || party.address4 || ' '
1258 || party.city || ' ' || party.state || ' ' ||
1259 party.postal_code || ' ' || party.county || ' ' ||
1260 ter.territory_short_name participant_address,
1261 party.email_address participant_email_address,
1262 party.URL participant_url,
1263 cp.raw_phone_number primary_phone_number,
1264 contact_person.party_name contact_person_name,
1265 contact_party.email_address contact_email_address,
1266 con_phone.raw_phone_number contact_phone_number
1267 )
1268 )
1269 )
1270 )
1271 FROM LNS_PARTICIPANTS lp, HZ_PARTIES party, AR_LOOKUPS lkup, LNS_LOOKUPS lnslkup,
1272 FND_TERRITORIES_TL ter, HZ_CONTACT_POINTS cp, HZ_PARTIES contact_person,
1273 HZ_PARTIES contact_party, HZ_CONTACT_POINTS con_phone
1274 WHERE party.party_id =lp.HZ_PARTY_ID
1275 AND party.party_type = lkup.lookup_code
1276 AND lkup.lookup_type = 'PARTY_TYPE'
1277 AND lp.LOAN_PARTICIPANT_TYPE = lnslkup.lookup_code
1278 AND lnslkup.lookup_type = 'LNS_PARTICIPANT_TYPE'
1279 AND party.country = ter.TERRITORY_CODE(+)
1280 AND ter.LANGUAGE(+) = userenv('LANG')
1281 AND party.party_id = cp.owner_table_id(+)
1282 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1283 AND cp.contact_point_type(+) = 'PHONE'
1284 AND cp.primary_flag(+) = 'Y'
1285 AND contact_person.party_id(+) =lp.contact_pers_party_id
1286 AND contact_party.party_id(+) =lp.contact_rel_party_id
1287 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
1288 AND con_phone.owner_table_id(+) =lp.contact_rel_party_id
1289 AND con_phone.primary_flag(+) = 'Y'
1290 AND con_phone.status(+) = 'A'
1291 AND con_phone.contact_point_type(+) = 'PHONE'
1292 AND LOAN_ID = lh.loan_id
1293 ), -- end of LOAN_PARTICIPANTS
1294 -- DISB_RATE_SCHEDULE
1295 (select
1296 XMLELEMENT("DISB_RATE_SCHEDULE",
1297 XMLAGG(
1298 XMLELEMENT("DISB_RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
1299 XMLFOREST(
1300 BEGIN_INSTALLMENT_NUMBER installment_from
1301 ,END_INSTALLMENT_NUMBER installment_to
1302 ,to_char(index_date, 'YYYY-MM-DD') index_date
1303 ,current_interest_rate interest_rate
1304 )
1305 )
1306 )
1307 )
1308 from lns_rate_schedules rsh
1309 where rsh.term_id = t.term_id
1310 and rsh.end_date_active is null
1311 and nvl(rsh.phase,'TERM') = 'OPEN'
1312 ), -- end of DISB_RATE_SCHEDULE
1313 -- RATE_SCHEDULE
1314 (select
1315 XMLELEMENT("RATE_SCHEDULE",
1316 XMLAGG(
1317 XMLELEMENT("RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
1318 XMLFOREST(
1319 BEGIN_INSTALLMENT_NUMBER installment_from
1320 ,END_INSTALLMENT_NUMBER installment_to
1321 ,to_char(index_date, 'YYYY-MM-DD') index_date
1322 ,current_interest_rate interest_rate
1323 )
1324 )
1325 )
1326 )
1327 from lns_rate_schedules rsh
1328 where rsh.term_id = t.term_id
1329 and rsh.end_date_active is null
1330 and nvl(rsh.phase,'TERM') = 'TERM'
1331 ), -- end of RATE_SCHEDULE
1332 -- COLLATERAL
1333 (select
1334 XMLELEMENT("COLLATERAL",
1335 XMLAGG(
1336 XMLELEMENT("COLLATERAL_ROW", XMLATTRIBUTES (rownum AS "num"),
1337 XMLFOREST(
1338 assetassign.PLEDGED_AMOUNT,
1339 assetassign.START_DATE_ACTIVE,
1340 assetassign.END_DATE_ACTIVE,
1341 lkps1.meaning participant_role,
1342 party.party_name participant_name,
1343 lkps2.meaning asset_class,
1344 lkps3.meaning asset_type,
1345 asset.quantity || ' ' || lkps4.meaning asset_quantity,
1346 lkps5.meaning || ': ' || asset.reference_name reference,
1347 asset.appraiser_name,
1348 asset.next_evaluation_date,
1349 lkps6.meaning valuation_method,
1350 asset.lien_amount,
1351 asset.description, asset.currency_code,
1352 asset.valuation, asset.start_date_active acquired_date
1353 )
1354 )
1355 )
1356 )
1357 FROM LNS_ASSET_ASSIGNMENTS assetassign,
1358 LNS_ASSETS asset,
1359 LNS_LOOKUPS lkps1,
1360 LNS_LOOKUPS lkps2,
1361 LNS_LOOKUPS lkps3,
1362 LNS_LOOKUPS lkps4,
1363 LNS_LOOKUPS lkps5,
1364 LNS_LOOKUPS lkps6,
1365 HZ_PARTIES party,
1366 LNS_PARTICIPANTS par
1367 WHERE assetassign.asset_id = asset.asset_id and
1368 asset.asset_owner_id = par.hz_party_id and
1369 party.party_id = par.hz_party_id and
1370 assetassign.loan_id = par.loan_id and
1371 par.loan_participant_type = lkps1.lookup_code and
1372 lkps1.lookup_type = 'LNS_PARTICIPANT_TYPE' and
1373 asset.asset_class_code = lkps2.lookup_code and
1374 lkps2.lookup_type = 'ASSET_CLASSES' and
1375 asset.asset_type_code = lkps3.lookup_code and
1376 lkps3.lookup_type = asset.asset_class_code and
1377 asset.uom_code = lkps4.lookup_code and
1378 lkps4.lookup_type = 'ASSET_QNT_'||asset.asset_class_code and
1379 asset.reference_type = lkps5.lookup_code and
1380 lkps5.lookup_type = 'ASSET_REF_'||asset.asset_class_code and
1381 asset.valuation_method_code = lkps6.lookup_code and
1382 lkps6.lookup_type = 'VALUATION_METHOD' and
1383 assetassign.loan_id = lh.loan_id
1384 ), -- end of COLLATERAL
1385 -- FEES
1386 (select
1387 XMLELEMENT("FEES",
1388 XMLAGG(
1389 XMLELEMENT("FEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1390 XMLFOREST(
1391 lfa.FEE, lfa.FEE_TYPE, lfa.FEE_BASIS, lfa.RATE_TYPE,
1392 lfa.BILLING_OPTION BILLING_OPTION_CODE,
1393 llkbo.meaning BILLING_OPTION,
1394 lf.FEE_NAME,
1395 lf.FEE_CATEGORY FEE_CATEGORY_CODE,
1396 llkfc.meaning FEE_CATEGORY,
1397 lf.rate_type method_code,
1398 llkrt.meaning Fee_method,
1399 fl.meaning update_allowed,
1400 lf.FEE_DESCRIPTION,
1401 decode(lf.RATE_TYPE, 'FIXED',
1402 to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
1403 (nvl(lf.CURRENCY_CODE,'USD'),25))
1404 , to_char(lf.FEE) || '%' ||
1405 decode(lf.FEE_BASIS, null, '', ', ')
1406 || lkps1.meaning) FEE_VAR_AMOUNT_PERCENT
1407 )
1408 )
1409 )
1410 )
1411 FROM LNS_FEE_ASSIGNMENTS lfa, LNS_FEES_ALL lf, LNS_LOOKUPS lkps1,
1412 LNS_LOOKUPS llkbo,
1413 LNS_LOOKUPS llkrt,
1414 LNS_LOOKUPS llkfc,
1415 fnd_lookups fl
1416 WHERE lfa.FEE_ID = lf.FEE_ID AND
1417 lfa.loan_id = lh.loan_id and
1418 llkfc.lookup_type = 'FEE_CATEGORY' and
1419 llkfc.lookup_code = lf.fee_category and
1420 lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
1421 llkbo.lookup_code(+) = lf.BILLING_OPTION AND
1422 llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
1423 llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
1424 fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
1425 ), -- end of FEES
1426 -- CONDITIONS
1427 (select
1428 XMLELEMENT("CONDITIONS",
1429 XMLAGG(
1430 XMLELEMENT("CONDITIONS_ROW", XMLATTRIBUTES (rownum AS "num"),
1431 XMLFOREST(
1432 lc.CONDITION_NAME,
1433 lca.CONDITION_DESCRIPTION,
1434 lc.CONDITION_TYPE CONDITION_TYPE_CODE,
1435 lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
1436 )
1437 )
1438 )
1439 )
1440 FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
1441 LNS_LOOKUPS lkps1 , fnd_lookups fl
1442 WHERE lca.CONDITION_ID = lc.CONDITION_ID
1443 AND lkps1.lookup_type = 'CONDITION_TYPE'
1444 AND lkps1.lookup_code = lc.CONDITION_TYPE
1445 AND fl.lookup_type = 'YES_NO'
1446 AND fl.lookup_code = lca.MANDATORY_FLAG
1447 AND nvl(lca.start_date_active, sysdate) <= sysdate
1448 AND nvl(lca.end_date_active, sysdate) >= sysdate
1449 AND lca.LOAN_ID = lh.loan_id
1450 ), -- end of CONDITIONS
1451 -- ORIGINAL_RECEIVABLES
1452 (select
1453 XMLELEMENT("ORIGINAL_RECEIVABLES",
1454 XMLAGG(
1455 XMLELEMENT("ORIGINAL_RECEIVABLES_ROW", XMLATTRIBUTES (rownum AS "num"),
1456 XMLFOREST(
1457 lll.REFERENCE_NUMBER original_rec_number,
1458 lll.REFERENCE_AMOUNT original_rec_balance,
1459 lll.REQUESTED_AMOUNT loan_requested_amount,
1460 lll.REFERENCE_NUMBER original_rec_desc,
1461 to_char(cust_trx.term_due_date,'YYYY-MM-DD')
1462 INVOICE_DUE_DATE,
1463 trx_type.name || ' - ' || trx_type_lkup.meaning
1464 inv_trx_type
1465 )
1466 )
1467 )
1468 )
1469 from LNS_LOAN_LINES lll,
1470 RA_CUSTOMER_TRX_ALL cust_trx,
1471 RA_CUST_TRX_TYPES_ALL trx_type,
1472 ar_lookups trx_type_lkup
1473 where lll.loan_id = lh.LOAN_ID and
1474 lll.end_date is null and
1475 cust_trx.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
1476 trx_type.org_id = lh.org_id and
1477 trx_type_lkup.lookup_type = 'INV/CM' and
1478 trx_type_lkup.lookup_code = trx_type.type and
1479 cust_trx.customer_trx_id = lll.reference_id
1480 ), -- end of ORIGINAL_RECEIVABLES
1481 -- DISBURSEMENTS
1482 (select
1483 XMLELEMENT("DISBURSEMENTS",
1484 XMLAGG(
1485 XMLELEMENT("DISBURSEMENTS_ROW", XMLATTRIBUTES (rownum AS "num"),
1486 XMLFOREST(
1487 disbursement_number
1488 ,llkac.meaning disbursement_activity
1489 ,to_char(ldih.target_date,'YYYY-MM-DD') target_date
1490 ,ldih.header_percent disbursement_percent
1491 ,ldih.header_amount amount_of_disbursement
1492 ),
1493 -- PAYEES
1494 (select
1495 XMLELEMENT("PAYEES",
1496 XMLAGG(
1497 XMLELEMENT("PAYEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1498 XMLFOREST(
1499 party.party_name payee_name
1500 ,party.ADDRESS1 || ' ' || party.ADDRESS2 || ' ' ||
1501 party.ADDRESS3 || ' ' || party.STATE || ' ' ||
1502 party.POSTAL_CODE || ' ' ||party.COUNTRY payee_address
1503 ,ibypm.payment_method_name
1504 ,line_percent payee_percent
1505 ,line_amount payee_amount
1506 )
1507 )
1508 )
1509 )
1510 from lns_disb_lines ldl
1511 ,hz_parties party
1512 ,IBY_PAYMENT_METHODS_TL ibypm
1513 where ldl.disb_header_id = ldih.disb_header_id
1514 and party.party_id = ldl.payee_party_id and
1515 ibypm.payment_method_code (+) = ldl.payment_method_code and
1516 ibypm.LANGUAGE (+) = userenv('LANG')
1517 ),-- end of PAYEES
1518 -- DISBFEES
1519 (select
1520 XMLELEMENT("DISBFEES",
1521 XMLAGG(
1522 XMLELEMENT("DISBFEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1523 XMLFOREST(
1524 lfa.FEE, lfa.FEE_TYPE,
1525 lfa.FEE_BASIS,
1526 lfa.RATE_TYPE,
1527 lfa.BILLING_OPTION BILLING_OPTION_CODE,
1528 llkbo.meaning BILLING_OPTION,
1529 lf.FEE_NAME,
1530 lf.FEE_CATEGORY FEE_CATEGORY_CODE,
1531 llkfc.meaning FEE_CATEGORY,
1532 lf.rate_type method_code,
1533 llkrt.meaning Fee_method,
1534 fl.meaning update_allowed,
1535 lf.FEE_DESCRIPTION,
1536 decode(lf.RATE_TYPE, 'FIXED', to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
1537 (nvl(lf.CURRENCY_CODE,'USD'),25)), to_char(lf.FEE) || '%' ||
1538 decode(lf.FEE_BASIS, null, '', ', ') || lkps1.meaning)
1539 FEE_VAR_AMOUNT_PERCENT
1540 )
1541 )
1542 )
1543 )
1544 FROM LNS_FEE_ASSIGNMENTS lfa,
1545 LNS_FEES_ALL lf,
1546 LNS_LOOKUPS lkps1,
1547 LNS_LOOKUPS llkbo,
1548 LNS_LOOKUPS llkrt,
1549 LNS_LOOKUPS llkfc,
1550 fnd_lookups fl
1551 WHERE lfa.FEE_ID = lf.FEE_ID AND
1552 lfa.disb_header_id = ldih.disb_header_id and
1553 llkfc.lookup_type = 'FEE_CATEGORY' and
1554 llkfc.lookup_code = lf.fee_category and
1555 lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
1556 llkbo.lookup_code(+) = lf.BILLING_OPTION AND
1557 llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
1558 llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
1559 fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
1560 ), -- end of DISBFEES
1561 -- DISBCOND
1562 (select
1563 XMLELEMENT("DISBCOND",
1564 XMLAGG(
1565 XMLELEMENT("DISBCOND_ROW", XMLATTRIBUTES (rownum AS "num"),
1566 XMLFOREST(
1567 lc.CONDITION_NAME,
1568 lca.CONDITION_DESCRIPTION,
1569 lc.CONDITION_TYPE CONDITION_TYPE_CODE,
1570 lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
1571 )
1572 )
1573 )
1574 )
1575 FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
1576 LNS_LOOKUPS lkps1 , fnd_lookups fl
1577 WHERE lca.CONDITION_ID = lc.CONDITION_ID
1578 AND lkps1.lookup_type = 'CONDITION_TYPE'
1579 AND lkps1.lookup_code = lc.CONDITION_TYPE
1580 AND fl.lookup_type = 'YES_NO'
1581 AND fl.lookup_code = lca.MANDATORY_FLAG
1582 AND nvl(lca.start_date_active, sysdate) <= sysdate
1583 AND nvl(lca.end_date_active, sysdate) >= sysdate
1584 AND lca.disb_header_id = ldih.disb_header_id
1585 ) -- end of DISBCOND
1586 )
1587 )
1588 )
1589 FROM lns_disb_headers ldih
1590 ,lns_lookups llkac
1591 WHERE ldih.loan_id = lh.loan_id and
1592 llkac.lookup_code = ldih.activity_code
1593 ), -- end of DISBURSEMENTS
1594 -- AMORTIZATION
1595 (select
1596 XMLELEMENT("AMORTIZATION",
1597 XMLAGG(
1598 XMLELEMENT("AMORTIZATION_ROW", XMLATTRIBUTES (rownum AS "num"),
1599 XMLFOREST(
1600 INSTALLMENT_NUMBER PAYMENT_NUMBER
1601 ,to_char(DUE_DATE, 'YYYY-MM-DD') DUE_DATE
1602 ,PRINCIPAL_AMOUNT PAYMENT_PRINCIPAL
1603 ,INTEREST_AMOUNT PAYMENT_INTEREST
1604 ,FEE_AMOUNT PAYMENT_FEES
1605 ,(PRINCIPAL_AMOUNT+INTEREST_AMOUNT+FEE_AMOUNT) PAYMENT_TOTAL
1606 ,OTHER_AMOUNT
1607 ,BEGIN_BALANCE BEGINNING_BALANCE
1608 ,END_BALANCE ENDING_BALANCE
1609 ,INTEREST_CUMULATIVE
1610 ,PRINCIPAL_CUMULATIVE
1611 ,FEES_CUMULATIVE
1612 ,OTHER_CUMULATIVE
1613 ,SOURCE
1614 ,NORMAL_INT_AMOUNT
1615 ,DISBURSEMENT_AMOUNT
1616 ,PREV_DEFERRED_INT_AMOUNT
1617 ,DEFERRED_INT_AMOUNT
1618 ,PREV_CAP_INT_AMOUNT
1619 ,CURR_CAP_INT_AMOUNT
1620 ,CAP_INT_AMOUNT
1621 ,NORMAL_INT_DETAILS
1622 ,DEFERRED_INT_DETAILS
1623 ,CAP_INT_DETAILS
1624 )
1625 )
1626 )
1627 )
1628 FROM lns_amort_scheds_gt agt
1629 where agt.loan_id = lh.loan_id
1630 ) -- end of AMORTIZATION
1631 ), XMLFormat.createformat('ROWSET')).getClobVal()
1632 from
1633 lns_loan_headers_all_vl lh,
1634 hz_parties hp,
1635 lns_terms t,
1636 lns_int_rate_headers_vl intrt,
1637 lns_rate_schedules ratesch,
1638 hz_locations loc,
1639 fnd_territories_tl terr,
1640 hz_party_sites site,
1641 hz_cust_acct_sites_all acct_site,
1642 --LNS_PAY_SUM_V pay, --Bug5262505
1643 xle_firstparty_information_v xle,
1644 lns_lookups llkrt,
1645 lns_lookups llktt,
1646 lns_loan_types_vl llt,
1647 lns_lookups llklc,
1648 lns_lookups llkp,
1649 lns_lookups llkst,
1650 lns_lookups llkdc,
1651 lns_lookups llkfq,
1652 lns_lookups llkfqf,
1653 lns_lookups llkic,
1654 jtf_rs_resource_extns res,
1655 lns_disb_headers ldh,
1656 lns_loan_products_all_vl product
1657 where
1658 lh.primary_borrower_id = hp.party_id and
1659 --lh.loan_id = pay.loan_id and --Bug5262505
1660 lh.loan_id = t.loan_id and
1661 ldh.loan_id(+) = lh.loan_id and
1662 ldh.disbursement_number(+) = 1 and
1663 product.loan_product_id(+) = lh.product_id and
1664 t.term_id = ratesch.term_id and
1665 ratesch.begin_installment_number = 1 and
1666 ratesch.end_date_active is null and
1667 ((lh.multiple_funding_flag = 'Y' and lh.open_to_term_flag = 'N' and
1668 ratesch.phase = 'OPEN') OR ( ratesch.phase = 'TERM')) and
1669 intrt.interest_rate_id = t.index_rate_id and
1670 xle.legal_entity_id = lh.legal_entity_id and
1671 llktt.lookup_code = lh.loan_term_period and
1672 llktt.lookup_type = 'PERIOD' and
1673 llkrt.lookup_code = t.rate_type and
1674 llkrt.lookup_type = 'RATE_TYPE' and
1675 llkic.lookup_code = t.calculation_method and
1676 llkic.lookup_type = 'INTEREST_CALCULATION_METHOD' and
1677 llklc.lookup_code = lh.loan_class_code and
1678 llklc.lookup_type = 'LOAN_CLASS' and
1679 llt.loan_type_id = lh.loan_type_id and
1680 llkdc.lookup_code = t.day_count_method and
1681 llkdc.lookup_type = 'DAY_COUNT_METHOD' and
1682 llkp.lookup_code (+) = lh.loan_purpose_code and
1683 llkp.lookup_type (+) = 'LOAN_PURPOSE' and
1684 llkst.lookup_code (+) = lh.loan_subtype and
1685 llkst.lookup_type (+) = 'LOAN_SUBTYPE' and
1686 lh.loan_assigned_to = res.resource_id and
1687 llkfq.lookup_code (+) = t.loan_payment_frequency and
1688 llkfq.lookup_type (+) = 'FREQUENCY' and
1689 llkfqf.lookup_code (+) = t.open_payment_frequency and
1690 llkfqf.lookup_type (+) = 'FREQUENCY' and
1691 res.category = 'EMPLOYEE' and
1692 lh.loan_status <> 'DELETED' and
1693 acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and
1694 acct_site.org_id = lh.org_id and
1695 site.party_site_id = acct_site.party_site_id and
1696 site.location_id = loc.location_id and
1697 loc.country = terr.TERRITORY_CODE and
1698 terr.language = userenv('LANG') and
1699 lh.loan_id = X_Loan_Id and
1700 lh.org_id = lh.org_id;
1701 BEGIN
1702 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1703
1704 lns_financials.runAmortization(p_api_version => 1.0
1705 ,p_init_msg_list => 'T'
1706 ,p_commit => 'F'
1707 ,p_loan_ID => p_loan_id
1708 ,p_based_on_terms => p_based_on_terms --'CURRENT'
1709 ,x_amort_tbl => l_amort_tbl
1710 ,x_return_status => l_return_Status
1711 ,x_msg_count => l_msg_count
1712 ,x_msg_data => l_msg_data);
1713 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Returned amortization schedule count: ' || l_amort_tbl.count);
1714
1715
1716 FOR j in 1..l_amort_tbl.count
1717 LOOP
1718 v_INSTALLMENT_NUMBER(j) := l_amort_tbl(j).INSTALLMENT_NUMBER;
1719 v_DUE_DATE(j) := l_amort_tbl(j).DUE_DATE;
1720 v_PRINCIPAL_AMOUNT(j) := l_amort_tbl(j).PRINCIPAL_AMOUNT;
1721 v_INTEREST_AMOUNT(j) := l_amort_tbl(j).INTEREST_AMOUNT;
1722 v_FEE_AMOUNT(j) := l_amort_tbl(j).FEE_AMOUNT;
1723 v_OTHER_AMOUNT(j) := l_amort_tbl(j).OTHER_AMOUNT;
1724 v_BEGIN_BALANCE(j) := l_amort_tbl(j).BEGIN_BALANCE;
1725 v_END_BALANCE(j) := l_amort_tbl(j).END_BALANCE;
1726 v_TOTAL(j) := l_amort_tbl(j).TOTAL;
1727 v_INTEREST_CUMULATIVE(j) := l_amort_tbl(j).INTEREST_CUMULATIVE;
1728 v_PRINCIPAL_CUMULATIVE(j) := l_amort_tbl(j).PRINCIPAL_CUMULATIVE;
1729 v_FEES_CUMULATIVE(j) := l_amort_tbl(j).FEES_CUMULATIVE;
1730 v_OTHER_CUMULATIVE(j) := l_amort_tbl(j).OTHER_CUMULATIVE;
1731 v_RATE_ID(j) := l_amort_tbl(j).RATE_ID;
1732 v_SOURCE(j) := l_amort_tbl(j).SOURCE;
1733 v_GRAND_TOTAL_FLAG(j) := l_amort_tbl(j).GRAND_TOTAL_FLAG;
1734 v_NORMAL_INT_AMOUNT(j) := l_amort_tbl(j).NORMAL_INT_AMOUNT;
1735 v_DISBURSEMENT_AMOUNT(j) := l_amort_tbl(j).DISBURSEMENT_AMOUNT;
1736 v_PREV_DEFERRED_INT_AMOUNT(j) := l_amort_tbl(j).PREV_DEFERRED_INT_AMOUNT;
1737 v_DEFERRED_INT_AMOUNT(j) := l_amort_tbl(j).DEFERRED_INT_AMOUNT;
1738 v_PREV_CAP_INT_AMOUNT(j) := l_amort_tbl(j).PREV_CAP_INT_AMOUNT;
1739 v_CURR_CAP_INT_AMOUNT(j) := l_amort_tbl(j).CURR_CAP_INT_AMOUNT;
1740 v_CAP_INT_AMOUNT(j) := l_amort_tbl(j).CAP_INT_AMOUNT;
1741 v_NORMAL_INT_DETAILS(j) := l_amort_tbl(j).NORMAL_INT_DETAILS;
1742 v_DEFERRED_INT_DETAILS(j) := l_amort_tbl(j).DEFERRED_INT_DETAILS;
1743 v_CAP_INT_DETAILS(j) := l_amort_tbl(j).CAP_INT_DETAILS;
1744 END LOOP;
1745
1746 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into lns_amort_scheds_gt...');
1747 IF (v_INSTALLMENT_NUMBER.first iS NOT NULL) THEN
1748 FORALL j in v_INSTALLMENT_NUMBER.first..v_INSTALLMENT_NUMBER.last
1749 insert into lns_amort_scheds_gt
1750 (
1751 LOAN_ID
1752 ,INSTALLMENT_NUMBER
1753 ,DUE_DATE
1754 ,PRINCIPAL_AMOUNT
1755 ,INTEREST_AMOUNT
1756 ,FEE_AMOUNT
1757 ,OTHER_AMOUNT
1758 ,BEGIN_BALANCE
1759 ,END_BALANCE
1760 ,TOTAL
1761 ,INTEREST_CUMULATIVE
1762 ,PRINCIPAL_CUMULATIVE
1763 ,FEES_CUMULATIVE
1764 ,OTHER_CUMULATIVE
1765 ,RATE_ID
1766 ,SOURCE
1767 ,GRAND_TOTAL_FLAG
1768 ,NORMAL_INT_AMOUNT
1769 ,DISBURSEMENT_AMOUNT
1770 ,PREV_DEFERRED_INT_AMOUNT
1771 ,DEFERRED_INT_AMOUNT
1772 ,PREV_CAP_INT_AMOUNT
1773 ,CURR_CAP_INT_AMOUNT
1774 ,CAP_INT_AMOUNT
1775 ,NORMAL_INT_DETAILS
1776 ,DEFERRED_INT_DETAILS
1777 ,CAP_INT_DETAILS
1778 )
1779 VALUES (
1780 p_loan_id
1781 ,v_INSTALLMENT_NUMBER(j)
1782 ,v_DUE_DATE(j)
1783 ,v_PRINCIPAL_AMOUNT(j)
1784 ,v_INTEREST_AMOUNT(j)
1785 ,v_FEE_AMOUNT(j)
1786 ,v_OTHER_AMOUNT(j)
1787 ,v_BEGIN_BALANCE(j)
1788 ,v_END_BALANCE(j)
1789 ,v_TOTAL(j)
1790 ,v_INTEREST_CUMULATIVE(j)
1791 ,v_PRINCIPAL_CUMULATIVE(j)
1792 ,v_FEES_CUMULATIVE(j)
1793 ,v_OTHER_CUMULATIVE(j)
1794 ,v_RATE_ID(j)
1795 ,v_SOURCE(j)
1796 ,v_GRAND_TOTAL_FLAG(j)
1797 ,v_NORMAL_INT_AMOUNT(j)
1798 ,v_DISBURSEMENT_AMOUNT(j)
1799 ,v_PREV_DEFERRED_INT_AMOUNT(j)
1800 ,v_DEFERRED_INT_AMOUNT(j)
1801 ,v_PREV_CAP_INT_AMOUNT(j)
1802 ,v_CURR_CAP_INT_AMOUNT(j)
1803 ,v_CAP_INT_AMOUNT(j)
1804 ,v_NORMAL_INT_DETAILS(j)
1805 ,v_DEFERRED_INT_DETAILS(j)
1806 ,v_CAP_INT_DETAILS(j)
1807 );
1808 end IF;
1809
1810 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Opening c_get_principal_balance');
1811 open c_get_principal_balance(p_loan_id);
1812 fetch c_get_principal_balance into l_total_principal_balance;
1813 close c_get_principal_balance;
1814
1815 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Opening C_agreement');
1816 OPEN C_agreement(X_Loan_Id => p_loan_id,
1817 pTotalPrincipalBalance => l_total_principal_balance);
1818 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Fetching C_agreement');
1819 FETCH C_agreement INTO l_result;
1820 IF C_agreement%ROWCOUNT <> 0 THEN
1821 l_rows_processed := 1;
1822 END IF;
1823 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'C_agreement%ROWCOUNT ' || C_agreement%ROWCOUNT);
1824 CLOSE C_agreement;
1825 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_rows_processed ' || l_rows_processed);
1826
1827 -- We are adding the LNSAGREEMENT and PARAMETERS TAGs so we have
1828 -- to offset the first line.
1829 IF l_rows_processed <> 0 THEN
1830 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1831 ELSE
1832 l_resultOffset := 0;
1833 END IF;
1834 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_resultOffset ' || l_resultOffset);
1835
1836 -- Start Getting Parameter Display Values
1837 SELECT hou.name, gsb.currency_code
1838 INTO l_org_name, l_sob_currency_code
1839 FROM hr_operating_units hou,
1840 gl_sets_of_books gsb,
1841 lns_loan_headers_all loan
1842 WHERE hou.organization_id = loan.org_id
1843 AND gsb.set_of_books_id = hou.set_of_books_id
1844 AND loan.loan_id = p_loan_id;
1845
1846 l_new_line := '
1847 ';
1848 /* Prepare the tag for the report heading */
1849 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
1850 l_xml_header := l_xml_header ||l_new_line||'<LNSAGREEMENT>';
1851 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1852 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||REPLACE_SPECIAL_CHARS(l_org_name)||'</ORG_NAME>';
1853 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS_CURRENCY_CODE>'||REPLACE_SPECIAL_CHARS(l_sob_currency_code)||'</SET_OF_BOOKS_CURRENCY_CODE>';
1854 l_xml_header := l_xml_header ||l_new_line||' <LOAN_ID>' ||p_loan_id ||'</LOAN_ID>';
1855 l_xml_header := l_xml_header ||l_new_line||' <REPORT_GENERATION_DATE>' || to_char(sysdate,'YYYY-MM-DD') || '</REPORT_GENERATION_DATE>';
1856 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
1857 l_close_tag := l_new_line||'</LNSAGREEMENT>'||l_new_line;
1858 l_xml_header_length := length(l_xml_header);
1859 IF l_rows_processed <> 0 THEN
1860 LogMessage(FND_LOG.LEVEL_PROCEDURE, ' l_xml_header_length ' || l_xml_header_length);
1861 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1862 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1863 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1864
1865 LogMessage(FND_LOG.LEVEL_PROCEDURE, ' before dbms_lob.copy ' );
1866 dbms_lob.copy(tempResult,l_result
1867 ,dbms_lob.getlength(l_result)-l_resultOffset
1868 ,l_xml_header_length,l_resultOffset);
1869 ELSE
1870 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1871 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1872 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1873 END IF;
1874
1875 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1876 p_AgreementXML := tempResult;
1877 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1878 EXCEPTION
1879
1880 WHEN OTHERS THEN
1881 RAISE;
1882 END GEN_AGREEMENT_XML;
1883
1884 PROCEDURE CLOB_TO_FILE( p_clob IN CLOB )
1885 IS
1886 l_clob_size NUMBER;
1887 l_offset NUMBER;
1888 l_res_offset NUMBER;
1889 l_chunk_size INTEGER;
1890 l_chunk_limit_size INTEGER;
1891 l_chunk VARCHAR2(32767);
1892 l_new_line VARCHAR2(1);
1893 l_api_name CONSTANT VARCHAR2(30) := 'CLOB_TO_FILE';
1894
1895 BEGIN
1896
1897 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1898 -- get length of internal lob and open the dest. file.
1899 l_clob_size := dbms_lob.getlength(p_clob);
1900
1901 IF (l_clob_size = 0) THEN
1902 LogMessage(FND_LOG.LEVEL_PROCEDURE,'CLOB is empty');
1903 RETURN;
1904 END IF;
1905
1906 l_offset := 1;
1907 l_chunk_size := 3000;
1908 l_chunk_limit_size := l_chunk_size;
1909
1910 -- To get the maximum value which should be multiple of l_chunk_size and also less than 32767
1911 WHILE(l_chunk_limit_size < 32767)
1912 LOOP
1913 l_chunk_limit_size := l_chunk_limit_size + l_chunk_size;
1914 END LOOP;
1915 l_chunk_limit_size := l_chunk_limit_size - l_chunk_size;
1916
1917 LogMessage(FND_LOG.LEVEL_PROCEDURE,'chunk limit size is '||l_chunk_limit_size);
1918
1919 l_new_line := '
1920 ';
1921
1922 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Unloading... ' || l_clob_size);
1923
1924 WHILE (l_clob_size > 0) LOOP
1925
1926 -- LogMessage(FND_LOG.LEVEL_PROCEDURE,'Off Set: ' || l_offset);
1927
1928 l_chunk := dbms_lob.substr (p_clob, l_chunk_size, l_offset);
1929
1930 --LogMessage(FND_LOG.LEVEL_PROCEDURE,'Off Set: ' || l_offset);
1931 --LogMessage(FND_LOG.LEVEL_PROCEDURE,'l_chunk ' || l_chunk);
1932
1933 -- There should be one new line character(chr(10)) for every 32k when
1934 -- writing into the file using 'utl_file' package.
1935
1936 if((mod(l_offset,l_chunk_limit_size) = 1) AND (l_offset <> 1)) then
1937 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Reached into the Limit Size');
1938
1939 -- Inserting new line character(chr(10)) after the first appearing
1940 -- closing XML tag
1941 l_res_offset := instr(l_chunk,'>');
1942
1943 LogMessage(FND_LOG.LEVEL_PROCEDURE,'result Offset is '||l_res_offset);
1944
1945 if(l_res_offset > 0) then
1946 l_chunk := substr(l_chunk,1,l_res_offset)||l_new_line||substr(l_chunk,(l_res_offset+1),length(l_chunk));
1947 end if;
1948 end if;
1949
1950 fnd_file.put(
1951 which => fnd_file.output,
1952 buff => l_chunk);
1953
1954 l_clob_size := l_clob_size - l_chunk_size;
1955 l_offset := l_offset + l_chunk_size;
1956
1957 END LOOP;
1958
1959 fnd_file.new_line(fnd_file.output,1);
1960 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 LogMessage(FND_LOG.LEVEL_PROCEDURE,'EXCEPTION: OTHERS clob_to_file');
1965 LogMessage(FND_LOG.LEVEL_PROCEDURE,sqlcode);
1966 LogMessage(FND_LOG.LEVEL_PROCEDURE,sqlerrm);
1967 RAISE;
1968
1969 END;
1970
1971 -- Bug#6169438, Added new parameter P_GENERATE_AGREEMENT which forces the API to regenerate the loan
1972 -- agreement ,if the value is 'Y', and also create the history record in loans schema.
1973
1974 PROCEDURE PROCESS_AGREEMENT_REPORT(ERRBUF OUT NOCOPY VARCHAR2
1975 ,RETCODE OUT NOCOPY VARCHAR2
1976 ,LOAN_ID IN NUMBER
1977 ,P_GENERATE_AGREEMENT IN VARCHAR2 DEFAULT 'N'
1978 ,P_REASON IN VARCHAR2 DEFAULT NULL
1979 ) IS
1980 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_AGREEMENT_REPORT';
1981 l_api_version CONSTANT NUMBER := 1.0;
1982 l_result_xml CLOB;
1983 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1984 l_loan_status LNS_LOAN_HEADERS_ALL.LOAN_STATUS%TYPE;
1985 l_document_id LNS_LOAN_DOCUMENTS.DOCUMENT_ID%TYPE;
1986 l_version LNS_LOAN_DOCUMENTS.VERSION%TYPE := -1;
1987 l_reason LNS_LOAN_DOCUMENTS.REASON%TYPE;
1988 l_object_version_number NUMBER;
1989 l_count NUMBER;
1990 l_agreement_exist_flag VARCHAR2(1) := 'Y';
1991
1992 BEGIN
1993 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1994
1995 l_loan_id := LOAN_ID;
1996
1997 select lh.loan_status into l_loan_status
1998 from lns_loan_headers_all lh
1999 where lh.loan_id = l_loan_id;
2000
2001 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loan Id = ' || LOAN_ID);
2002 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REASON = ' || P_REASON);
2003 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_STATUS = ' || l_loan_status);
2004
2005 IF (l_loan_status <> 'DELETED' AND l_loan_status <> 'PAIDOFF' AND l_loan_status <> 'REJECTED') THEN
2006
2007 select max(version) into l_version
2008 from lns_loan_documents
2009 where source_table = 'LNS_LOAN_HEADERS_ALL'
2010 and source_id = l_loan_id
2011 and document_type = 'LOAN_AGREEMENT';
2012
2013 IF (l_version is null or l_version = 0 or l_loan_status = 'INCOMPLETE' or l_loan_status = 'PENDING') then
2014 l_version := 1;
2015 l_object_version_number := 1;
2016 else
2017 l_version := l_version+1;
2018 l_object_version_number := l_version;
2019 end if;
2020
2021 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Version = ' || l_version);
2022
2023 IF(P_REASON IS NULL OR P_REASON = '') THEN
2024 -- Retrieve default agreement reason
2025 FND_MESSAGE.SET_NAME('LNS', 'LNS_ORIG_AGR_REASON');
2026 FND_MSG_PUB.Add;
2027 l_reason := FND_MSG_PUB.Get(p_encoded => 'F');
2028 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
2029 ELSE
2030 l_reason := P_REASON;
2031 END IF;
2032
2033 IF(l_loan_status = 'INCOMPLETE' or l_loan_status = 'PENDING') THEN
2034
2035 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Generating agreement report1...in process agreement report');
2036 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
2037 p_loan_id => l_loan_id,
2038 p_based_on_terms => 'ORIGINAL');
2039
2040 select count(*) into l_count
2041 from lns_loan_documents
2042 where source_id = l_loan_id;
2043
2044 IF(l_count = 0) THEN
2045 LogMessage(FND_LOG.LEVEL_STATEMENT,'For incomplete loan, inserting loan agreement for first time');
2046 LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
2047 ,P_SOURCE_ID => l_loan_id
2048 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
2049 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
2050 ,P_VERSION => l_version
2051 ,P_DOCUMENT_XML => l_result_xml
2052 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2053 ,P_REASON => l_reason);
2054 ELSE
2055 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating the existing agreement instead of creating a new record in DB');
2056 LNS_LOAN_DOCUMENTS_PKG.Update_Row(X_DOCUMENT_ID => l_document_id
2057 ,P_SOURCE_ID => l_loan_id
2058 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
2059 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
2060 ,P_VERSION => l_version
2061 ,P_DOCUMENT_XML => l_result_xml
2062 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2063 ,P_REASON => l_reason);
2064 END IF;
2065
2066 ELSE
2067
2068 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Generating agreement report2...in process agreement report');
2069 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
2070 p_loan_id => l_loan_id,
2071 p_based_on_terms => 'CURRENT');
2072
2073 LogMessage(FND_LOG.LEVEL_STATEMENT,'Creating a new Agreement record in DB');
2074 LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
2075 ,P_SOURCE_ID => l_loan_id
2076 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
2077 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
2078 ,P_VERSION => l_version
2079 ,P_DOCUMENT_XML => l_result_xml
2080 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2081 ,P_REASON => l_reason);
2082 END IF;
2083
2084 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Printing...');
2085 clob_to_file(l_result_xml);
2086
2087 ELSE
2088 LogMessage(FND_LOG.LEVEL_STATEMENT, 'No agreement report will be generated');
2089 END IF;
2090 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2091
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception');
2095 RAISE;
2096 END PROCESS_AGREEMENT_REPORT;
2097
2098 PROCEDURE STORE_LOAN_AGREEMENT (P_LOAN_ID IN NUMBER
2099 ,P_AGREEMENT_REASON IN VARCHAR2 DEFAULT NULL) IS
2100 l_api_name CONSTANT VARCHAR2(30) := 'STORE_LOAN_AGREEMENT_REPORT';
2101 l_api_version CONSTANT NUMBER := 1.0;
2102 l_result_xml CLOB;
2103 l_document_id LNS_LOAN_DOCUMENTS.DOCUMENT_ID%TYPE;
2104 l_reason LNS_LOAN_DOCUMENTS.REASON%TYPE;
2105 l_loan_status LNS_LOAN_HEADERS_ALL.LOAN_STATUS%TYPE;
2106 l_version NUMBER;
2107 l_object_version_number NUMBER;
2108
2109 cursor csr_loan_agreement IS
2110 select document_xml
2111 from lns_loan_documents
2112 where source_table = 'LNS_LOAN_HEADERS_ALL'
2113 and source_id = p_loan_id
2114 and version = 1
2115 and document_type = 'LOAN_AGREEMENT';
2116
2117 BEGIN
2118
2119 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2120
2121 select lh.loan_status into l_loan_status
2122 from lns_loan_headers_all lh
2123 where lh.loan_id = P_LOAN_ID;
2124
2125 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_ID = ' || P_LOAN_ID);
2126 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_AGREEMENT_REASON = ' || P_AGREEMENT_REASON);
2127 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_STATUS = ' || l_loan_status);
2128
2129 IF(P_AGREEMENT_REASON IS NULL OR P_AGREEMENT_REASON = '') THEN
2130 -- Retrieve default agreement reason
2131 FND_MESSAGE.SET_NAME('LNS', 'LNS_ORIG_AGR_REASON');
2132 FND_MSG_PUB.Add;
2133 l_reason := FND_MSG_PUB.Get(p_encoded => 'F');
2134 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
2135 ELSE
2136 l_reason := P_AGREEMENT_REASON;
2137 END IF;
2138 l_version := 1;
2139 l_object_version_number := 1;
2140
2141 OPEN csr_loan_agreement;
2142 FETCH csr_loan_agreement
2143 INTO l_result_xml;
2144
2145 IF(l_loan_status = 'INCOMPLETE' or l_loan_status = 'PENDING') THEN
2146
2147 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Generating agreement report1...');
2148 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
2149 p_loan_id => p_loan_id,
2150 p_based_on_terms => 'ORIGINAL');
2151
2152 ELSE
2153
2154 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Generating agreement report2...');
2155 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
2156 p_loan_id => p_loan_id,
2157 p_based_on_terms => 'CURRENT');
2158
2159 END IF;
2160
2161 IF csr_loan_agreement%NOTFOUND THEN
2162
2163 LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting loan agreement for first time...');
2164 LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
2165 ,P_SOURCE_ID => p_loan_id
2166 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
2167 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
2168 ,P_VERSION => 1
2169 ,P_DOCUMENT_XML => l_result_xml
2170 ,P_OBJECT_VERSION_NUMBER => 1
2171 ,P_REASON => l_reason);
2172
2173 ELSE
2174
2175 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating the existing agreement...');
2176 LNS_LOAN_DOCUMENTS_PKG.Update_Row(X_DOCUMENT_ID => l_document_id
2177 ,P_SOURCE_ID => p_loan_id
2178 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
2179 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
2180 ,P_VERSION => l_version
2181 ,P_DOCUMENT_XML => l_result_xml
2182 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2183 ,P_REASON => l_reason);
2184
2185 END IF;
2186 CLOSE csr_loan_agreement;
2187
2188 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2189
2190 EXCEPTION
2191 WHEN OTHERS THEN
2192 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
2193 || ' -');
2194 RAISE;
2195 END STORE_LOAN_AGREEMENT;
2196
2197
2198 PROCEDURE STORE_LOAN_AGREEMENT_CP (P_LOAN_ID IN NUMBER
2199 ,P_AGREEMENT_REASON IN VARCHAR2)
2200 IS
2201 l_api_name CONSTANT VARCHAR2(30) := 'STORE_LOAN_AGREEMENT_CP';
2202 l_xml_output BOOLEAN;
2203 l_iso_language FND_LANGUAGES.iso_language%TYPE;
2204 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
2205 l_notify boolean;
2206 l_request_id number;
2207 l_org_id number;
2208
2209 CURSOR loan_cur(P_LOAN_ID number) IS
2210 select org_id from lns_loan_headers_all where loan_id = P_LOAN_ID;
2211
2212 BEGIN
2213
2214 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2215
2216 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_ID = ' || P_LOAN_ID);
2217 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_AGREEMENT_REASON = ' || P_AGREEMENT_REASON);
2218
2219 /* verify input parameters */
2220 if P_LOAN_ID is null then
2221
2222 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
2223 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_LOAN_ID' );
2224 FND_MSG_PUB.Add;
2225 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
2226 RAISE FND_API.G_EXC_ERROR;
2227
2228 end if;
2229
2230 if P_AGREEMENT_REASON is null then
2231
2232 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
2233 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_AGREEMENT_REASON' );
2234 FND_MSG_PUB.Add;
2235 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
2236 RAISE FND_API.G_EXC_ERROR;
2237
2238 end if;
2239
2240 /* begin submit request to generate Loan Agreement Report */
2241 l_notify := FND_REQUEST.ADD_NOTIFICATION(FND_GLOBAL.USER_NAME);
2242
2243 open loan_cur(P_LOAN_ID);
2244 fetch loan_cur into l_org_id;
2245 close loan_cur;
2246
2247 FND_REQUEST.SET_ORG_ID(l_org_id);
2248
2249 SELECT
2250 lower(iso_language),iso_territory
2251 INTO
2252 l_iso_language,l_iso_territory
2253 FROM
2254 FND_LANGUAGES
2255 WHERE
2256 language_code = USERENV('LANG');
2257
2258 logMessage(FND_LOG.LEVEL_STATEMENT, 'Calling fnd_request.add_layout...');
2259 l_xml_output:= fnd_request.add_layout(
2260 template_appl_name => 'LNS',
2261 template_code => 'LNSRPTAG',
2262 template_language => l_iso_language,
2263 template_territory => l_iso_territory,
2264 output_format => 'PDF'
2265 );
2266
2267 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_iso_language = ' || l_iso_language);
2268 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_iso_territory = ' || l_iso_territory);
2269
2270 logMessage(FND_LOG.LEVEL_STATEMENT, 'Submitting LNS_AGREEMENT cp...');
2271 l_request_id := FND_REQUEST.SUBMIT_REQUEST('LNS'
2272 ,'LNS_AGREEMENT'
2273 ,'', '', FALSE
2274 ,P_LOAN_ID
2275 ,'Y'
2276 ,P_AGREEMENT_REASON);
2277
2278 if l_request_id = 0 then
2279 FND_MESSAGE.SET_NAME('LNS', 'LNS_AGREEMENT_REQUEST_FAILED');
2280 FND_MSG_PUB.Add;
2281 RAISE FND_API.G_EXC_ERROR;
2282 end if;
2283 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully submited Loan Agreement Report Generation Concurrent Program. Request id: ' || l_request_id);
2284
2285 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2286
2287 EXCEPTION
2288 WHEN OTHERS THEN
2289 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' -');
2290 END;
2291
2292
2293
2294 /*========================================================================
2295 | PUBLIC PROCEDURE LNS_TRANSFER_LOANS_CONCUR
2296 |
2297 | DESCRIPTION
2298 | This procedure got called from concurent manager to change loan agent for loans.
2299 |
2300 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2301 | None
2302 |
2303 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2304 | LogMessage
2305 |
2306 | PARAMETERS
2307 | ERRBUF OUT Returns errors to CM
2308 | RETCODE OUT Returns error code to CM
2309 | P_FROM_LOAN_OFFICER IN Inputs from loan officer
2310 | P_TO_LOAN_OFFICER IN Inputs to loan officer
2311 |
2312 | KNOWN ISSUES
2313 | None
2314 |
2315 | NOTES
2316 | Any interesting aspect of the code in the package body which needs
2317 | to be stated.
2318 |
2319 | MODIFICATION HISTORY
2320 | Date Author Description of Changes
2321 | 24-04-2009 gparuchu Created
2322 |
2323 *=======================================================================*/
2324 PROCEDURE LNS_TRANSFER_LOANS_CONCUR(
2325 ERRBUF OUT NOCOPY VARCHAR2,
2326 RETCODE OUT NOCOPY VARCHAR2,
2327 P_FROM_LOAN_OFFICER IN NUMBER,
2328 P_TO_LOAN_OFFICER IN NUMBER)
2329 IS
2330
2331 /*-----------------------------------------------------------------------+
2332 | Local Variable Declarations and initializations |
2333 +-----------------------------------------------------------------------*/
2334 l_api_name CONSTANT VARCHAR2(30) := 'LNS_TRANSFER_LOANS_CONCUR';
2335 l_msg_count NUMBER;
2336 l_msg_data VARCHAR2(32767);
2337 l_return BOOLEAN;
2338 l_count NUMBER;
2339 l_loan_id NUMBER;
2340 l_loan_number VARCHAR2(60);
2341 l_version_number NUMBER;
2342 l_start DATE;
2343 l_end DATE;
2344 l_return_status VARCHAR2(1);
2345 g_cr_return_status VARCHAR2(10);
2346
2347 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
2348
2349 /*-----------------------------------------------------------------------+
2350 | Cursor Declarations |
2351 +-----------------------------------------------------------------------*/
2352
2353 /* query all the loans that are with a given loan officer */
2354 CURSOR loans_to_be_transferred_cur IS
2355 select
2356 head.OBJECT_VERSION_NUMBER,
2357 head.loan_id,
2358 head.loan_number
2359 from
2360 LNS_LOAN_HEADERS_ALL head
2361 where
2362 head.loan_assigned_to = p_from_loan_officer;
2363 BEGIN
2364
2365 g_cr_return_status := 'NORMAL';
2366
2367 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2368 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters:');
2369 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_FROM_LOAN_OFFICER = ' || P_FROM_LOAN_OFFICER);
2370 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_TO_LOAN_OFFICER = ' || P_TO_LOAN_OFFICER);
2371
2372 if P_FROM_LOAN_OFFICER is null then
2373 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2374 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_FROM_LOAN_OFFICER');
2375 FND_MESSAGE.SET_TOKEN('VALUE', P_FROM_LOAN_OFFICER);
2376 FND_MSG_PUB.ADD;
2377 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
2378 RAISE FND_API.G_EXC_ERROR;
2379 end if;
2380
2381 if P_TO_LOAN_OFFICER is null then
2382 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2383 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_TO_LOAN_OFFICER');
2384 FND_MESSAGE.SET_TOKEN('VALUE', P_TO_LOAN_OFFICER);
2385 FND_MSG_PUB.ADD;
2386 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
2387 RAISE FND_API.G_EXC_ERROR;
2388 end if;
2389
2390 /* init variables */
2391 l_count := 0;
2392 l_start := sysdate;
2393
2394 if P_FROM_LOAN_OFFICER <> P_TO_LOAN_OFFICER then
2395
2396 open loans_to_be_transferred_cur;
2397
2398 LOOP
2399
2400 fetch loans_to_be_transferred_cur into
2401 l_version_number,
2402 l_loan_id,
2403 l_loan_number;
2404 exit when loans_to_be_transferred_cur%NOTFOUND;
2405
2406 l_count := l_count + 1;
2407 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2408 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing Loan ' || l_loan_number || ' id ' || l_loan_id);
2409
2410 BEGIN
2411 /* updating loan header table */
2412 l_loan_header_rec.loan_id := l_loan_id;
2413 l_loan_header_rec.loan_assigned_to := P_TO_LOAN_OFFICER;
2414
2415 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
2416 P_LOAN_HEADER_REC => l_loan_header_rec,
2417 P_INIT_MSG_LIST => FND_API.G_FALSE,
2418 X_RETURN_STATUS => l_return_status,
2419 X_MSG_COUNT => l_msg_count,
2420 X_MSG_DATA => l_msg_data);
2421
2422
2423 IF l_return_status <> 'S' THEN
2424 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'l_return_status: ' || l_return_status);
2425 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
2426 FND_MSG_PUB.Add;
2427 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
2428 RAISE FND_API.G_EXC_ERROR;
2429 END IF;
2430
2431 --Everything successful here then go ahead and commit the updates
2432 COMMIT WORK;
2433 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully processed loan ' || l_loan_number);
2434
2435
2436 EXCEPTION
2437 WHEN OTHERS THEN
2438 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Failed to process loan ' || l_loan_number);
2439 g_cr_return_status := 'WARNING';
2440 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2441 status => g_cr_return_status,
2442 message => 'Not all loans were transfered successfully. Please review log file.');
2443 END;
2444
2445 END LOOP;
2446
2447 close loans_to_be_transferred_cur;
2448
2449 end if;
2450
2451 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
2452 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed ' || l_count || ' loan(s)');
2453 l_end := sysdate;
2454 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Timing: ' || round((l_end - l_start)*86400, 2) || ' sec');
2455
2456 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2457
2458 EXCEPTION
2459 WHEN OTHERS THEN
2460 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to Transfer Loans');
2461 g_cr_return_status := 'ERROR';
2462 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2463 status => g_cr_return_status,
2464 message => 'Failed to Transfer Loans. Please review log file.');
2465
2466 END LNS_TRANSFER_LOANS_CONCUR;
2467
2468
2469
2470 /*========================================================================
2471 | PUBLIC PROCEDURE LNS_SET_ACCESS_LEVEL_CONCUR
2472 |
2473 | DESCRIPTION
2474 | This procedure does mass access level assignment for borrowers, co-borrowers and guarantors.
2475 |
2476 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2477 | None
2478 |
2479 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2480 | LogMessage
2481 |
2482 | PARAMETERS
2483 | ERRBUF OUT Returns errors to CM
2484 | RETCODE OUT Returns error code to CM
2485 | ORG_ID IN Inputs org id
2486 | BORROWER_ID IN Inputs borrower id
2487 | PRODUCT_ID IN Inputs product id
2488 | BORR_ACCESS_LEVEL IN Inputs primary borrower access level
2489 | COBORR_ACCESS_LEVEL IN Inputs co-borrower access level
2490 | GUAR_ACCESS_LEVEL IN Inputs guarantor access level
2491 | FROM_LOAN_OFFICER IN Inputs from loan officer
2492 | TO_LOAN_OFFICER IN Inputs to loan officer
2493 |
2494 | KNOWN ISSUES
2495 | None
2496 |
2497 | NOTES
2498 | Any interesting aspect of the code in the package body which needs
2499 | to be stated.
2500 |
2501 | MODIFICATION HISTORY
2502 | Date Author Description of Changes
2503 | 28-07-2010 scherkas Created
2504 |
2505 *=======================================================================*/
2506 PROCEDURE LNS_SET_ACCESS_LEVEL_CONCUR(
2507 ERRBUF OUT NOCOPY VARCHAR2,
2508 RETCODE OUT NOCOPY VARCHAR2,
2509 ORG_ID IN NUMBER,
2510 BORROWER_ID IN NUMBER,
2511 PRODUCT_ID IN NUMBER,
2512 BORR_ACCESS_LEVEL IN VARCHAR2,
2513 COBORR_ACCESS_LEVEL IN VARCHAR2,
2514 GUAR_ACCESS_LEVEL IN VARCHAR2)
2515 IS
2516
2517 /*-----------------------------------------------------------------------+
2518 | Local Variable Declarations and initializations |
2519 +-----------------------------------------------------------------------*/
2520 l_api_name CONSTANT VARCHAR2(30) := 'LNS_SET_ACCESS_LEVEL_CONCUR';
2521 l_msg_count NUMBER;
2522 l_msg_data VARCHAR2(32767);
2523 l_return_status VARCHAR2(1);
2524 l_return BOOLEAN;
2525 l_count NUMBER;
2526 l_loan_id NUMBER;
2527 l_loan_number VARCHAR2(60);
2528 l_OBJECT_VERSION_NUMBER NUMBER;
2529 l_ACCESS_LEVEL VARCHAR2(30);
2530 l_start DATE;
2531 l_end DATE;
2532
2533 l_participant_rec LNS_PARTICIPANTS_PUB.loan_participant_rec_type;
2534
2535 /*-----------------------------------------------------------------------+
2536 | Cursor Declarations |
2537 +-----------------------------------------------------------------------*/
2538
2539 /* query all participants whose access levels need to be updated */
2540 CURSOR part_to_be_updated_cur (P_ORG_ID NUMBER, P_BORROWER_ID NUMBER, P_PRODUCT_ID NUMBER, P_LOAN_PARTICIPANT_TYPE VARCHAR2, P_ACCESS_LEVEL VARCHAR2) IS
2541 select parts.PARTICIPANT_ID,
2542 parts.LOAN_ID,
2543 loans.LOAN_NUMBER,
2544 parts.OBJECT_VERSION_NUMBER,
2545 parts.ACCESS_LEVEL
2546 from LNS_PARTICIPANTS parts,
2547 LNS_LOAN_HEADERS loans
2548 where loans.loan_id = parts.loan_id
2549 and loans.ORG_ID = nvl(P_ORG_ID, loans.ORG_ID)
2550 and loans.PRIMARY_BORROWER_ID = nvl(P_BORROWER_ID, loans.PRIMARY_BORROWER_ID)
2551 and loans.PRODUCT_ID = nvl(P_PRODUCT_ID, loans.PRODUCT_ID)
2552 and parts.LOAN_PARTICIPANT_TYPE = P_LOAN_PARTICIPANT_TYPE
2553 and nvl(parts.ACCESS_LEVEL, 'GLOBAL') <> decode(P_ACCESS_LEVEL, 'GLOBAL', 'GLOBAL', P_ACCESS_LEVEL)
2554 order by parts.LOAN_ID, parts.PARTICIPANT_ID;
2555
2556 BEGIN
2557
2558 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2559 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters:');
2560 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_ID = ' || ORG_ID);
2561 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_ID = ' || BORROWER_ID);
2562 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRODUCT_ID = ' || PRODUCT_ID);
2563 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORR_ACCESS_LEVEL = ' || BORR_ACCESS_LEVEL);
2564 LogMessage(FND_LOG.LEVEL_STATEMENT, 'COBORR_ACCESS_LEVEL = ' || COBORR_ACCESS_LEVEL);
2565 LogMessage(FND_LOG.LEVEL_STATEMENT, 'GUAR_ACCESS_LEVEL = ' || GUAR_ACCESS_LEVEL);
2566
2567 /* init variables */
2568 l_start := sysdate;
2569 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Start time: ' || to_char(l_start, 'DD-MON-YYYY HH24:MI:SS'));
2570
2571 -- processing co-borrowers
2572 if BORR_ACCESS_LEVEL is not null then
2573
2574 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2575 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing borrowers...');
2576 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Assigning new access level ' || BORR_ACCESS_LEVEL);
2577 l_count := 0;
2578 open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'PRIMARY_BORROWER', BORR_ACCESS_LEVEL);
2579 LOOP
2580
2581 fetch part_to_be_updated_cur into
2582 l_participant_rec.PARTICIPANT_ID,
2583 l_loan_id,
2584 l_loan_number,
2585 l_OBJECT_VERSION_NUMBER,
2586 l_ACCESS_LEVEL;
2587 exit when part_to_be_updated_cur%NOTFOUND;
2588
2589 BEGIN
2590
2591 l_count := l_count+1;
2592 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2593 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Record #' || l_count);
2594 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN: ' || l_loan_number);
2595 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_loan_id);
2596 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTICIPANT_ID: ' || l_participant_rec.PARTICIPANT_ID);
2597 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current ACCESS_LEVEL: ' || l_ACCESS_LEVEL);
2598
2599 if BORR_ACCESS_LEVEL = 'GLOBAL' then
2600 l_participant_rec.ACCESS_LEVEL := FND_API.G_MISS_CHAR;
2601 else
2602 l_participant_rec.ACCESS_LEVEL := BORR_ACCESS_LEVEL;
2603 end if;
2604
2605 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
2606 LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
2607 p_init_msg_list => FND_API.G_TRUE
2608 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2609 ,p_loan_participant_rec => l_participant_rec
2610 ,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
2611 ,x_return_status => l_return_status
2612 ,x_msg_count => l_msg_count
2613 ,x_msg_data => l_msg_data
2614 );
2615 IF l_return_status <> 'S' THEN
2616 RAISE FND_API.G_EXC_ERROR;
2617 END IF;
2618
2619 COMMIT WORK;
2620 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
2621
2622 EXCEPTION
2623 WHEN OTHERS THEN
2624 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process participant ' || l_participant_rec.PARTICIPANT_ID);
2625 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2626 status => 'WARNING',
2627 message => 'Not all Loan Access Levels were assigned successfully. Please review log file.');
2628 END;
2629
2630 END LOOP;
2631
2632 close part_to_be_updated_cur;
2633
2634 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
2635 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed ' || l_count || ' borrowers');
2636
2637 else
2638 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2639 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Will not process borrowers access levels.');
2640 end if;
2641
2642 l_end := sysdate;
2643 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Current time: ' || to_char(l_end, 'DD-MON-YYYY HH24:MI:SS'));
2644 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Borrowers processing time: ' || round((l_end - l_start)*86400, 2) || ' sec');
2645
2646
2647 -- processing co-borrowers
2648 if COBORR_ACCESS_LEVEL is not null then
2649
2650 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2651 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing co-borrowers...');
2652 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Assigning new access level ' || COBORR_ACCESS_LEVEL);
2653 l_count := 0;
2654 open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'COBORROWER', COBORR_ACCESS_LEVEL);
2655 LOOP
2656
2657 fetch part_to_be_updated_cur into
2658 l_participant_rec.PARTICIPANT_ID,
2659 l_loan_id,
2660 l_loan_number,
2661 l_OBJECT_VERSION_NUMBER,
2662 l_ACCESS_LEVEL;
2663 exit when part_to_be_updated_cur%NOTFOUND;
2664
2665 BEGIN
2666
2667 l_count := l_count+1;
2668 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2669 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Record #' || l_count);
2670 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN: ' || l_loan_number);
2671 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_loan_id);
2672 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTICIPANT_ID: ' || l_participant_rec.PARTICIPANT_ID);
2673 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current ACCESS_LEVEL: ' || l_ACCESS_LEVEL);
2674
2675 if COBORR_ACCESS_LEVEL = 'GLOBAL' then
2676 l_participant_rec.ACCESS_LEVEL := FND_API.G_MISS_CHAR;
2677 else
2678 l_participant_rec.ACCESS_LEVEL := COBORR_ACCESS_LEVEL;
2679 end if;
2680
2681 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
2682 LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
2683 p_init_msg_list => FND_API.G_TRUE
2684 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2685 ,p_loan_participant_rec => l_participant_rec
2686 ,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
2687 ,x_return_status => l_return_status
2688 ,x_msg_count => l_msg_count
2689 ,x_msg_data => l_msg_data
2690 );
2691 IF l_return_status <> 'S' THEN
2692 RAISE FND_API.G_EXC_ERROR;
2693 END IF;
2694
2695 COMMIT WORK;
2696 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
2697
2698 EXCEPTION
2699 WHEN OTHERS THEN
2700 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process participant ' || l_participant_rec.PARTICIPANT_ID);
2701 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2702 status => 'WARNING',
2703 message => 'Not all Loan Access Levels were assigned successfully. Please review log file.');
2704 END;
2705
2706 END LOOP;
2707
2708 close part_to_be_updated_cur;
2709
2710 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
2711 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed ' || l_count || ' co-borrowers');
2712
2713 else
2714 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2715 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Will not process co-borrowers.');
2716 end if;
2717 l_end := sysdate;
2718 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Current time: ' || to_char(l_end, 'DD-MON-YYYY HH24:MI:SS'));
2719 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Co-borrowers processing time: ' || round((l_end - l_start)*86400, 2) || ' sec');
2720
2721
2722 -- processing guarantors
2723 if GUAR_ACCESS_LEVEL is not null then
2724
2725 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2726 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing guarantors...');
2727 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Assigning new access level ' || GUAR_ACCESS_LEVEL);
2728 l_count := 0;
2729 open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'GUARANTOR', GUAR_ACCESS_LEVEL);
2730 LOOP
2731
2732 fetch part_to_be_updated_cur into
2733 l_participant_rec.PARTICIPANT_ID,
2734 l_loan_id,
2735 l_loan_number,
2736 l_OBJECT_VERSION_NUMBER,
2737 l_ACCESS_LEVEL;
2738 exit when part_to_be_updated_cur%NOTFOUND;
2739
2740 BEGIN
2741
2742 l_count := l_count+1;
2743 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2744 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Record #' || l_count);
2745 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN: ' || l_loan_number);
2746 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_loan_id);
2747 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTICIPANT_ID: ' || l_participant_rec.PARTICIPANT_ID);
2748 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current ACCESS_LEVEL: ' || l_ACCESS_LEVEL);
2749
2750 if GUAR_ACCESS_LEVEL = 'GLOBAL' then
2751 l_participant_rec.ACCESS_LEVEL := FND_API.G_MISS_CHAR;
2752 else
2753 l_participant_rec.ACCESS_LEVEL := GUAR_ACCESS_LEVEL;
2754 end if;
2755
2756 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
2757 LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
2758 p_init_msg_list => FND_API.G_TRUE
2759 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2760 ,p_loan_participant_rec => l_participant_rec
2761 ,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
2762 ,x_return_status => l_return_status
2763 ,x_msg_count => l_msg_count
2764 ,x_msg_data => l_msg_data
2765 );
2766 IF l_return_status <> 'S' THEN
2767 RAISE FND_API.G_EXC_ERROR;
2768 END IF;
2769
2770 COMMIT WORK;
2771 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
2772
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process participant ' || l_participant_rec.PARTICIPANT_ID);
2776 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2777 status => 'WARNING',
2778 message => 'Not all Loan Access Levels were assigned successfully. Please review log file.');
2779 END;
2780
2781 END LOOP;
2782
2783 close part_to_be_updated_cur;
2784
2785 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
2786 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed ' || l_count || ' guarantors');
2787
2788 else
2789 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2790 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Will not process guarantors.');
2791 end if;
2792 l_end := sysdate;
2793 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'End time: ' || to_char(l_end, 'DD-MON-YYYY HH24:MI:SS'));
2794 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Guarantors processing time: ' || round((l_end - l_start)*86400, 2) || ' sec');
2795
2796 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2797 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processing time: ' || round((l_end - l_start)*86400, 2) || ' sec');
2798
2799 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
2800 COMMIT WORK;
2801
2802 EXCEPTION
2803 WHEN OTHERS THEN
2804 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to Assign Loan Access Levels');
2805 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
2806 status => 'ERROR',
2807 message => 'Failed to Assign Loan Access Levels. Please review log file.');
2808
2809 END;
2810
2811
2812
2813 PROCEDURE LNS_EXPORT_AM_SCHEDS_CONCUR(
2814 ERRBUF OUT NOCOPY VARCHAR2,
2815 RETCODE OUT NOCOPY VARCHAR2,
2816 ORG_ID IN NUMBER, -- if null, will not filter by lorg
2817 BORROWER_ID IN NUMBER, -- if null, will not filter by borrower
2818 PRODUCT_ID IN NUMBER, -- if null, will not filter by product
2819 LOAN_STATUS1 IN VARCHAR2, -- if null, will not filter by loan status
2820 LOAN_STATUS2 IN VARCHAR2, -- if null, will not filter by loan status
2821 LOAN_STATUS3 IN VARCHAR2, -- if null, will not filter by loan status
2822 LOAN_ID IN NUMBER, -- if null, will not filter by loan
2823 SCHEDULE_TYPE IN VARCHAR2, -- values: CURRENT/ORIGINAL/BOTH; if null, will export CURRENT schedule
2824 TRUNCATE_HISTORY IN VARCHAR2) -- values: NO/PROCESSED_LOANS/ALL; if null, will use NO
2825 IS
2826
2827 /*-----------------------------------------------------------------------+
2828 | Local Variable Declarations and initializations |
2829 +-----------------------------------------------------------------------*/
2830 l_api_name CONSTANT VARCHAR2(30) := 'LNS_EXPORT_AM_SCHEDS_CONCUR';
2831 l_msg_count NUMBER;
2832 l_msg_data VARCHAR2(32767);
2833 l_return_status VARCHAR2(1);
2834 l_return BOOLEAN;
2835 l_count NUMBER;
2836 l_start DATE;
2837 l_end DATE;
2838 l_start1 DATE;
2839 l_end1 DATE;
2840 l_BASED_ON_TERMS varchar2(30);
2841 l_LOAN_ID NUMBER;
2842 l_LOAN_NUMBER VARCHAR2(60);
2843 l_LOAN_DESCRIPTION VARCHAR2(250);
2844 l_PRIMARY_BORROWER_ID NUMBER;
2845 l_FUNDED_AMOUNT NUMBER;
2846 l_LOAN_CURRENCY VARCHAR2(15);
2847 l_ORG_ID NUMBER;
2848 l_LEGAL_ENTITY_ID NUMBER;
2849 l_LOAN_STATUS VARCHAR2(30);
2850 l_TRUNCATE_HISTORY VARCHAR2(10);
2851 l_failed_count number;
2852 l_succeed_count number;
2853 l_LOAN_AMOUNT number;
2854
2855 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2856 l_cursor SYS_REFCURSOR;
2857 l_sql varchar2(500);
2858 l_where varchar2(200);
2859 l_clause varchar2(100);
2860
2861 type INSTALLMENT_NUMBER_tab_t is table of lns_am_scheds_exports.INSTALLMENT_NUMBER%TYPE index by pls_integer;
2862 v_INSTALLMENT_NUMBER INSTALLMENT_NUMBER_tab_t;
2863 type DUE_DATE_tab_t is table of lns_am_scheds_exports.DUE_DATE%TYPE index by pls_integer;
2864 v_DUE_DATE DUE_DATE_tab_t;
2865 type PRINCIPAL_AMOUNT_tab_t is table of lns_am_scheds_exports.PRINCIPAL_AMOUNT%TYPE index by pls_integer;
2866 v_PRINCIPAL_AMOUNT PRINCIPAL_AMOUNT_tab_t;
2867 type INTEREST_AMOUNT_tab_t is table of lns_am_scheds_exports.INTEREST_AMOUNT%TYPE index by pls_integer;
2868 v_INTEREST_AMOUNT INTEREST_AMOUNT_tab_t;
2869 type FEE_AMOUNT_tab_t is table of lns_am_scheds_exports.FEE_AMOUNT%TYPE index by pls_integer;
2870 v_FEE_AMOUNT FEE_AMOUNT_tab_t;
2871 type OTHER_AMOUNT_tab_t is table of lns_am_scheds_exports.OTHER_AMOUNT%TYPE index by pls_integer;
2872 v_OTHER_AMOUNT OTHER_AMOUNT_tab_t;
2873 type BEGIN_BALANCE_tab_t is table of lns_am_scheds_exports.BEGIN_BALANCE%TYPE index by pls_integer;
2874 v_BEGIN_BALANCE BEGIN_BALANCE_tab_t;
2875 type END_BALANCE_tab_t is table of lns_am_scheds_exports.END_BALANCE%TYPE index by pls_integer;
2876 v_END_BALANCE END_BALANCE_tab_t;
2877 type TOTAL_tab_t is table of lns_am_scheds_exports.TOTAL%TYPE index by pls_integer;
2878 v_TOTAL TOTAL_tab_t;
2879 type INTEREST_CUMULATIVE_tab_t is table of lns_am_scheds_exports.INTEREST_CUMULATIVE%TYPE index by pls_integer;
2880 v_INTEREST_CUMULATIVE INTEREST_CUMULATIVE_tab_t;
2881 type PRINCIPAL_CUMULATIVE_tab_t is table of lns_am_scheds_exports.PRINCIPAL_CUMULATIVE%TYPE index by pls_integer;
2882 v_PRINCIPAL_CUMULATIVE PRINCIPAL_CUMULATIVE_tab_t;
2883 type FEES_CUMULATIVE_tab_t is table of lns_am_scheds_exports.FEES_CUMULATIVE%TYPE index by pls_integer;
2884 v_FEES_CUMULATIVE FEES_CUMULATIVE_tab_t;
2885 type OTHER_CUMULATIVE_tab_t is table of lns_am_scheds_exports.OTHER_CUMULATIVE%TYPE index by pls_integer;
2886 v_OTHER_CUMULATIVE OTHER_CUMULATIVE_tab_t;
2887 type UNPAID_PRIN_tab_t is table of lns_am_scheds_exports.UNPAID_PRIN%TYPE index by pls_integer;
2888 v_UNPAID_PRIN UNPAID_PRIN_tab_t;
2889 type UNPAID_INT_tab_t is table of lns_am_scheds_exports.UNPAID_INT%TYPE index by pls_integer;
2890 v_UNPAID_INT UNPAID_INT_tab_t;
2891 type INTEREST_RATE_tab_t is table of lns_am_scheds_exports.INTEREST_RATE%TYPE index by pls_integer;
2892 v_INTEREST_RATE INTEREST_RATE_tab_t;
2893 type PERIOD_tab_t is table of lns_am_scheds_exports.PERIOD%TYPE index by pls_integer;
2894 v_PERIOD PERIOD_tab_t;
2895 type DEFERRED_INT_AMOUNT_tab_t is table of lns_am_scheds_exports.DEFERRED_INT_AMOUNT%TYPE index by pls_integer;
2896 v_DEFERRED_INT_AMOUNT DEFERRED_INT_AMOUNT_tab_t;
2897
2898 BEGIN
2899
2900 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
2901
2902 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Exporting loans amortization schedules...');
2903 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Input parameters:');
2904 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ORG_ID = ' || ORG_ID);
2905 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'BORROWER_ID = ' || BORROWER_ID);
2906 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'PRODUCT_ID = ' || PRODUCT_ID);
2907 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_ID = ' || LOAN_ID);
2908 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_STATUS1 = ' || LOAN_STATUS1);
2909 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_STATUS2 = ' || LOAN_STATUS2);
2910 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_STATUS3 = ' || LOAN_STATUS3);
2911 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'SCHEDULE_TYPE = ' || SCHEDULE_TYPE);
2912 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'TRUNCATE_HISTORY = ' || TRUNCATE_HISTORY);
2913
2914 /* init variables */
2915 l_start := sysdate;
2916 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Start time: ' || to_char(l_start, 'DD-MON-YYYY HH24:MI:SS'));
2917
2918 if SCHEDULE_TYPE is null or SCHEDULE_TYPE = 'CURRENT' or SCHEDULE_TYPE = 'BOTH' then
2919 l_BASED_ON_TERMS := 'CURRENT';
2920 elsif SCHEDULE_TYPE = 'ORIGINAL' then
2921 l_BASED_ON_TERMS := 'ORIGINAL';
2922 end if;
2923
2924 if TRUNCATE_HISTORY is null or TRUNCATE_HISTORY = 'NO' then
2925 l_TRUNCATE_HISTORY := 'NO';
2926 elsif TRUNCATE_HISTORY = 'YES' then
2927 l_TRUNCATE_HISTORY := 'YES';
2928 elsif TRUNCATE_HISTORY = 'ALL' then
2929 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Truncating table...');
2930 begin
2931 execute Immediate 'TRUNCATE TABLE lns.lns_am_scheds_exports';
2932 end;
2933 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
2934 end if;
2935
2936 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
2937 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying loans...');
2938 l_count := 0;
2939 l_failed_count := 0;
2940 l_succeed_count := 0;
2941
2942 l_sql :=
2943 'select LOAN_ID, ' ||
2944 'LOAN_NUMBER, ' ||
2945 'LOAN_DESCRIPTION, ' ||
2946 'PRIMARY_BORROWER_ID, ' ||
2947 '(REQUESTED_AMOUNT + NVL(ADD_REQUESTED_AMOUNT, 0)), ' ||
2948 'FUNDED_AMOUNT, ' ||
2949 'LOAN_CURRENCY, ' ||
2950 'ORG_ID, ' ||
2951 'LEGAL_ENTITY_ID, ' ||
2952 'LOAN_STATUS ' ||
2953 'from LNS_LOAN_HEADERS_ALL_VL';
2954
2955 if ORG_ID is not null then
2956 if l_where is not null then
2957 l_where := l_where || ' and ';
2958 end if;
2959 l_where := l_where || 'ORG_ID = ' || ORG_ID;
2960 end if;
2961
2962 if BORROWER_ID is not null then
2963 if l_where is not null then
2964 l_where := l_where || ' and ';
2965 end if;
2966 l_where := l_where || 'PRIMARY_BORROWER_ID = ' || BORROWER_ID;
2967 end if;
2968
2969 if PRODUCT_ID is not null then
2970 if l_where is not null then
2971 l_where := l_where || ' and ';
2972 end if;
2973 l_where := l_where || 'PRODUCT_ID = ' || PRODUCT_ID;
2974 end if;
2975
2976 if LOAN_ID is not null then
2977 if l_where is not null then
2978 l_where := l_where || ' and ';
2979 end if;
2980 l_where := l_where || 'LOAN_ID = ' || LOAN_ID;
2981 end if;
2982
2983 if LOAN_STATUS1 is not null or LOAN_STATUS2 is not null or LOAN_STATUS3 is not null then
2984
2985 if l_where is not null then
2986 l_where := l_where || ' and ';
2987 end if;
2988
2989 if LOAN_STATUS1 is not null then
2990 if l_clause is not null then
2991 l_clause := l_clause || ', ';
2992 end if;
2993 l_clause := l_clause || '''' || LOAN_STATUS1 || '''';
2994 end if;
2995
2996 if LOAN_STATUS2 is not null then
2997 if l_clause is not null then
2998 l_clause := l_clause || ', ';
2999 end if;
3000 l_clause := l_clause || '''' || LOAN_STATUS2 || '''';
3001 end if;
3002
3003 if LOAN_STATUS3 is not null then
3004 if l_clause is not null then
3005 l_clause := l_clause || ', ';
3006 end if;
3007 l_clause := l_clause || '''' || LOAN_STATUS3 || '''';
3008 end if;
3009
3010 if l_clause is not null then
3011 l_clause := 'LOAN_STATUS in (' || l_clause || ')';
3012 end if;
3013 l_where := l_where || l_clause;
3014 end if;
3015
3016 if l_where is not null then
3017 l_where := ' where ' || l_where;
3018 end if;
3019
3020 l_sql := l_sql || l_where || ' order by LOAN_ID';
3021
3022 LogMessage(FND_LOG.LEVEL_STATEMENT, l_sql);
3023
3024 OPEN l_cursor FOR l_sql;
3025 LOOP
3026
3027 fetch l_cursor into
3028 l_LOAN_ID,
3029 l_LOAN_NUMBER,
3030 l_LOAN_DESCRIPTION,
3031 l_PRIMARY_BORROWER_ID,
3032 l_LOAN_AMOUNT,
3033 l_FUNDED_AMOUNT,
3034 l_LOAN_CURRENCY,
3035 l_ORG_ID,
3036 l_LEGAL_ENTITY_ID,
3037 l_LOAN_STATUS;
3038 exit when l_cursor%NOTFOUND;
3039
3040 l_start1 := sysdate;
3041 l_count := l_count+1;
3042 LogMessage(FND_LOG.LEVEL_UNEXPECTED, ' ');
3043 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Processing loan #' || l_count);
3044 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Start time: ' || to_char(l_start1, 'DD-MON-YYYY HH24:MI:SS'));
3045 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_NUMBER: ' || l_LOAN_NUMBER);
3046 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_ID: ' || l_LOAN_ID);
3047 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_DESCRIPTION: ' || l_LOAN_DESCRIPTION);
3048 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'PRIMARY_BORROWER_ID: ' || l_PRIMARY_BORROWER_ID);
3049 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_AMOUNT: ' || l_LOAN_AMOUNT);
3050 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FUNDED_AMOUNT: ' || l_FUNDED_AMOUNT);
3051 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_CURRENCY: ' || l_LOAN_CURRENCY);
3052 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_ID: ' || l_ORG_ID);
3053 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_ID: ' || l_LEGAL_ENTITY_ID);
3054 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_STATUS: ' || l_LOAN_STATUS);
3055 BEGIN
3056
3057
3058 if l_TRUNCATE_HISTORY = 'YES' then
3059
3060 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleting history...');
3061
3062 delete from lns_am_scheds_exports
3063 where loan_id = l_LOAN_ID
3064 and SCHEDULE_TYPE = l_BASED_ON_TERMS;
3065
3066 if SCHEDULE_TYPE = 'BOTH' then
3067 delete from lns_am_scheds_exports
3068 where loan_id = l_LOAN_ID
3069 and SCHEDULE_TYPE = 'ORIGINAL';
3070 end if;
3071
3072 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
3073
3074 end if;
3075
3076 l_amort_tbl.delete;
3077 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_FINANCIALS.runAmortization for ' || l_BASED_ON_TERMS || ' SCHEDULE_TYPE...');
3078 LNS_FINANCIALS.runAmortization(
3079 P_API_VERSION => 1.0,
3080 P_INIT_MSG_LIST => FND_API.G_TRUE,
3081 P_COMMIT => FND_API.G_FALSE,
3082 P_LOAN_ID => l_loan_id,
3083 P_BASED_ON_TERMS => l_BASED_ON_TERMS,
3084 x_amort_tbl => l_amort_tbl,
3085 x_return_status => l_return_status,
3086 x_msg_count => l_msg_count,
3087 x_msg_data => l_msg_data);
3088
3089 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
3090 IF l_return_status <> 'S' THEN
3091 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ERROR: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
3092 RAISE FND_API.G_EXC_ERROR;
3093 END IF;
3094
3095 LogMessage(FND_LOG.LEVEL_UNEXPECTED, l_BASED_ON_TERMS || ' amortization schedule:');
3096 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '# DD RATE BB UP UI PAY PRIN INT FEE EB');
3097 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '--- ------- ---- ------ ----- ----- ------- ------ ------ ------ ------');
3098 if l_amort_tbl.count > 0 then
3099
3100 v_INSTALLMENT_NUMBER.delete;
3101 v_DUE_DATE.delete;
3102 v_PRINCIPAL_AMOUNT.delete;
3103 v_INTEREST_AMOUNT.delete;
3104 v_FEE_AMOUNT.delete;
3105 v_OTHER_AMOUNT.delete;
3106 v_BEGIN_BALANCE.delete;
3107 v_END_BALANCE.delete;
3108 v_TOTAL.delete;
3109 v_INTEREST_CUMULATIVE.delete;
3110 v_PRINCIPAL_CUMULATIVE.delete;
3111 v_FEES_CUMULATIVE.delete;
3112 v_OTHER_CUMULATIVE.delete;
3113 v_UNPAID_PRIN.delete;
3114 v_UNPAID_INT.delete;
3115 v_INTEREST_RATE.delete;
3116 v_PERIOD.delete;
3117 v_DEFERRED_INT_AMOUNT.delete;
3118
3119 for i in 1..l_amort_tbl.count loop
3120 v_INSTALLMENT_NUMBER(i) := l_amort_tbl(i).INSTALLMENT_NUMBER;
3121 v_DUE_DATE(i) := l_amort_tbl(i).DUE_DATE;
3122 v_PRINCIPAL_AMOUNT(i) := l_amort_tbl(i).PRINCIPAL_AMOUNT;
3123 v_INTEREST_AMOUNT(i) := l_amort_tbl(i).INTEREST_AMOUNT;
3124 v_FEE_AMOUNT(i) := l_amort_tbl(i).FEE_AMOUNT;
3125 v_OTHER_AMOUNT(i) := l_amort_tbl(i).OTHER_AMOUNT;
3126 v_BEGIN_BALANCE(i) := l_amort_tbl(i).BEGIN_BALANCE;
3127 v_END_BALANCE(i) := l_amort_tbl(i).END_BALANCE;
3128 v_TOTAL(i) := l_amort_tbl(i).TOTAL;
3129 v_INTEREST_CUMULATIVE(i) := l_amort_tbl(i).INTEREST_CUMULATIVE;
3130 v_PRINCIPAL_CUMULATIVE(i) := l_amort_tbl(i).PRINCIPAL_CUMULATIVE;
3131 v_FEES_CUMULATIVE(i) := l_amort_tbl(i).FEES_CUMULATIVE;
3132 v_OTHER_CUMULATIVE(i) := l_amort_tbl(i).OTHER_CUMULATIVE;
3133 v_UNPAID_PRIN(i) := l_amort_tbl(i).UNPAID_PRIN;
3134 v_UNPAID_INT(i) := l_amort_tbl(i).UNPAID_INT;
3135 v_INTEREST_RATE(i) := l_amort_tbl(i).INTEREST_RATE;
3136 v_PERIOD(i) := l_amort_tbl(i).PERIOD;
3137 v_DEFERRED_INT_AMOUNT(i) := l_amort_tbl(i).DEFERRED_INT_AMOUNT;
3138
3139 LogMessage(FND_LOG.LEVEL_UNEXPECTED,
3140 l_amort_tbl(i).INSTALLMENT_NUMBER || ' ' ||
3141 l_amort_tbl(i).DUE_DATE || ' ' ||
3142 l_amort_tbl(i).INTEREST_RATE || ' ' ||
3143 l_amort_tbl(i).BEGIN_BALANCE || ' ' ||
3144 l_amort_tbl(i).UNPAID_PRIN || ' ' ||
3145 l_amort_tbl(i).UNPAID_INT || ' ' ||
3146 l_amort_tbl(i).TOTAL || ' ' ||
3147 l_amort_tbl(i).PRINCIPAL_AMOUNT || ' ' ||
3148 l_amort_tbl(i).INTEREST_AMOUNT || ' ' ||
3149 l_amort_tbl(i).FEE_AMOUNT || ' ' ||
3150 l_amort_tbl(i).END_BALANCE);
3151 end loop;
3152
3153 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records...');
3154 FORALL i in v_INSTALLMENT_NUMBER.first..v_INSTALLMENT_NUMBER.last
3155 insert into lns_am_scheds_exports
3156 (
3157 LOAN_ID
3158 ,SCHEDULE_TYPE
3159 ,CREATION_DATE
3160 ,INSTALLMENT_NUMBER
3161 ,DUE_DATE
3162 ,PRINCIPAL_AMOUNT
3163 ,INTEREST_AMOUNT
3164 ,FEE_AMOUNT
3165 ,OTHER_AMOUNT
3166 ,BEGIN_BALANCE
3167 ,END_BALANCE
3168 ,TOTAL
3169 ,INTEREST_CUMULATIVE
3170 ,PRINCIPAL_CUMULATIVE
3171 ,FEES_CUMULATIVE
3172 ,OTHER_CUMULATIVE
3173 ,UNPAID_PRIN
3174 ,UNPAID_INT
3175 ,INTEREST_RATE
3176 ,PERIOD
3177 ,DEFERRED_INT_AMOUNT
3178 ,REQUEST_ID
3179 )
3180 VALUES
3181 (
3182 l_loan_id
3183 ,l_BASED_ON_TERMS
3184 ,sysdate
3185 ,v_INSTALLMENT_NUMBER(i)
3186 ,v_DUE_DATE(i)
3187 ,v_PRINCIPAL_AMOUNT(i)
3188 ,v_INTEREST_AMOUNT(i)
3189 ,v_FEE_AMOUNT(i)
3190 ,v_OTHER_AMOUNT(i)
3191 ,v_BEGIN_BALANCE(i)
3192 ,v_END_BALANCE(i)
3193 ,v_TOTAL(i)
3194 ,v_INTEREST_CUMULATIVE(i)
3195 ,v_PRINCIPAL_CUMULATIVE(i)
3196 ,v_FEES_CUMULATIVE(i)
3197 ,v_OTHER_CUMULATIVE(i)
3198 ,v_UNPAID_PRIN(i)
3199 ,v_UNPAID_INT(i)
3200 ,v_INTEREST_RATE(i)
3201 ,v_PERIOD(i)
3202 ,v_DEFERRED_INT_AMOUNT(i)
3203 ,FND_GLOBAL.Conc_Request_Id
3204 );
3205 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
3206
3207 end if;
3208
3209 if SCHEDULE_TYPE = 'BOTH' then
3210
3211 l_amort_tbl.delete;
3212 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_FINANCIALS.runAmortization for ORIGINAL SCHEDULE_TYPE...');
3213 LNS_FINANCIALS.runAmortization(
3214 P_API_VERSION => 1.0,
3215 P_INIT_MSG_LIST => FND_API.G_TRUE,
3216 P_COMMIT => FND_API.G_FALSE,
3217 P_LOAN_ID => l_loan_id,
3218 P_BASED_ON_TERMS => 'ORIGINAL',
3219 x_amort_tbl => l_amort_tbl,
3220 x_return_status => l_return_status,
3221 x_msg_count => l_msg_count,
3222 x_msg_data => l_msg_data);
3223
3224 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
3225 IF l_return_status <> 'S' THEN
3226 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ERROR: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
3227 RAISE FND_API.G_EXC_ERROR;
3228 END IF;
3229
3230 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ORIGINAL amortization schedule:');
3231 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '# DD RATE BB UP UI PAY PRIN INT FEE EB');
3232 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '--- ------- ---- ------ ----- ----- ------- ------ ------ ------ ------');
3233 if l_amort_tbl.count > 0 then
3234
3235 v_INSTALLMENT_NUMBER.delete;
3236 v_DUE_DATE.delete;
3237 v_PRINCIPAL_AMOUNT.delete;
3238 v_INTEREST_AMOUNT.delete;
3239 v_FEE_AMOUNT.delete;
3240 v_OTHER_AMOUNT.delete;
3241 v_BEGIN_BALANCE.delete;
3242 v_END_BALANCE.delete;
3243 v_TOTAL.delete;
3244 v_INTEREST_CUMULATIVE.delete;
3245 v_PRINCIPAL_CUMULATIVE.delete;
3246 v_FEES_CUMULATIVE.delete;
3247 v_OTHER_CUMULATIVE.delete;
3248 v_UNPAID_PRIN.delete;
3249 v_UNPAID_INT.delete;
3250 v_INTEREST_RATE.delete;
3251 v_PERIOD.delete;
3252 v_DEFERRED_INT_AMOUNT.delete;
3253
3254 for i in 1..l_amort_tbl.count loop
3255 v_INSTALLMENT_NUMBER(i) := l_amort_tbl(i).INSTALLMENT_NUMBER;
3256 v_DUE_DATE(i) := l_amort_tbl(i).DUE_DATE;
3257 v_PRINCIPAL_AMOUNT(i) := l_amort_tbl(i).PRINCIPAL_AMOUNT;
3258 v_INTEREST_AMOUNT(i) := l_amort_tbl(i).INTEREST_AMOUNT;
3259 v_FEE_AMOUNT(i) := l_amort_tbl(i).FEE_AMOUNT;
3260 v_OTHER_AMOUNT(i) := l_amort_tbl(i).OTHER_AMOUNT;
3261 v_BEGIN_BALANCE(i) := l_amort_tbl(i).BEGIN_BALANCE;
3262 v_END_BALANCE(i) := l_amort_tbl(i).END_BALANCE;
3263 v_TOTAL(i) := l_amort_tbl(i).TOTAL;
3264 v_INTEREST_CUMULATIVE(i) := l_amort_tbl(i).INTEREST_CUMULATIVE;
3265 v_PRINCIPAL_CUMULATIVE(i) := l_amort_tbl(i).PRINCIPAL_CUMULATIVE;
3266 v_FEES_CUMULATIVE(i) := l_amort_tbl(i).FEES_CUMULATIVE;
3267 v_OTHER_CUMULATIVE(i) := l_amort_tbl(i).OTHER_CUMULATIVE;
3268 v_UNPAID_PRIN(i) := l_amort_tbl(i).UNPAID_PRIN;
3269 v_UNPAID_INT(i) := l_amort_tbl(i).UNPAID_INT;
3270 v_INTEREST_RATE(i) := l_amort_tbl(i).INTEREST_RATE;
3271 v_PERIOD(i) := l_amort_tbl(i).PERIOD;
3272 v_DEFERRED_INT_AMOUNT(i) := l_amort_tbl(i).DEFERRED_INT_AMOUNT;
3273
3274 LogMessage(FND_LOG.LEVEL_UNEXPECTED,
3275 l_amort_tbl(i).INSTALLMENT_NUMBER || ' ' ||
3276 l_amort_tbl(i).DUE_DATE || ' ' ||
3277 l_amort_tbl(i).INTEREST_RATE || ' ' ||
3278 l_amort_tbl(i).BEGIN_BALANCE || ' ' ||
3279 l_amort_tbl(i).UNPAID_PRIN || ' ' ||
3280 l_amort_tbl(i).UNPAID_INT || ' ' ||
3281 l_amort_tbl(i).TOTAL || ' ' ||
3282 l_amort_tbl(i).PRINCIPAL_AMOUNT || ' ' ||
3283 l_amort_tbl(i).INTEREST_AMOUNT || ' ' ||
3284 l_amort_tbl(i).FEE_AMOUNT || ' ' ||
3285 l_amort_tbl(i).END_BALANCE);
3286 end loop;
3287
3288 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records...');
3289 FORALL i in v_INSTALLMENT_NUMBER.first..v_INSTALLMENT_NUMBER.last
3290 insert into lns_am_scheds_exports
3291 (
3292 LOAN_ID
3293 ,SCHEDULE_TYPE
3294 ,CREATION_DATE
3295 ,INSTALLMENT_NUMBER
3296 ,DUE_DATE
3297 ,PRINCIPAL_AMOUNT
3298 ,INTEREST_AMOUNT
3299 ,FEE_AMOUNT
3300 ,OTHER_AMOUNT
3301 ,BEGIN_BALANCE
3302 ,END_BALANCE
3303 ,TOTAL
3304 ,INTEREST_CUMULATIVE
3305 ,PRINCIPAL_CUMULATIVE
3306 ,FEES_CUMULATIVE
3307 ,OTHER_CUMULATIVE
3308 ,UNPAID_PRIN
3309 ,UNPAID_INT
3310 ,INTEREST_RATE
3311 ,PERIOD
3312 ,DEFERRED_INT_AMOUNT
3313 ,REQUEST_ID
3314 )
3315 VALUES
3316 (
3317 l_loan_id
3318 ,'ORIGINAL'
3319 ,sysdate
3320 ,v_INSTALLMENT_NUMBER(i)
3321 ,v_DUE_DATE(i)
3322 ,v_PRINCIPAL_AMOUNT(i)
3323 ,v_INTEREST_AMOUNT(i)
3324 ,v_FEE_AMOUNT(i)
3325 ,v_OTHER_AMOUNT(i)
3326 ,v_BEGIN_BALANCE(i)
3327 ,v_END_BALANCE(i)
3328 ,v_TOTAL(i)
3329 ,v_INTEREST_CUMULATIVE(i)
3330 ,v_PRINCIPAL_CUMULATIVE(i)
3331 ,v_FEES_CUMULATIVE(i)
3332 ,v_OTHER_CUMULATIVE(i)
3333 ,v_UNPAID_PRIN(i)
3334 ,v_UNPAID_INT(i)
3335 ,v_INTEREST_RATE(i)
3336 ,v_PERIOD(i)
3337 ,v_DEFERRED_INT_AMOUNT(i)
3338 ,FND_GLOBAL.Conc_Request_Id
3339 );
3340 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
3341 end if;
3342
3343 end if;
3344
3345 --Everything successful here then go ahead and commit the updates
3346 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully processed loan ' || l_LOAN_NUMBER);
3347 l_succeed_count := l_succeed_count + 1;
3348
3349 EXCEPTION
3350 WHEN OTHERS THEN
3351 l_failed_count := l_failed_count + 1;
3352 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process loan ' || l_LOAN_NUMBER || '. ERROR: ' || sqlerrm);
3353 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
3354 status => 'WARNING',
3355 message => 'Not all amortization schedules exported successfully. Please review log file.');
3356 END;
3357
3358 l_end1 := sysdate;
3359 LogMessage(FND_LOG.LEVEL_STATEMENT, 'End time: ' || to_char(l_end1, 'DD-MON-YYYY HH24:MI:SS'));
3360 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loan processing time: ' || round((l_end1 - l_start1)*86400, 2) || ' sec');
3361 COMMIT WORK;
3362
3363 END LOOP;
3364
3365 close l_cursor;
3366
3367 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '______________');
3368 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed loans: ' || l_failed_count);
3369 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Succeeded loans: ' || l_succeed_count);
3370 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processed loans: ' || l_count);
3371
3372 l_end := sysdate;
3373 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'End time: ' || to_char(l_end, 'DD-MON-YYYY HH24:MI:SS'));
3374 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Total processing time: ' || round((l_end - l_start)*86400, 2) || ' sec');
3375
3376 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
3377
3378 EXCEPTION
3379 WHEN OTHERS THEN
3380 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to Export Loans Amortization Schedules. ERROR: ' || sqlerrm);
3381 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
3382 status => 'ERROR',
3383 message => 'Failed to Export Loans Amortization Schedules. Please review log file.');
3384
3385 END;
3386
3387
3388
3389 BEGIN
3390 G_LOG_ENABLED := 'N';
3391 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
3392
3393 /* getting msg logging info */
3394 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
3395 if (G_LOG_ENABLED = 'N') then
3396 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
3397 else
3398 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
3399 end if;
3400
3401 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
3402 END LNS_REP_UTILS;