[Home] [Help]
PACKAGE BODY: APPS.LNS_REP_UTILS
Source
1 PACKAGE BODY LNS_REP_UTILS as
2 /* $Header: LNS_REP_UTILS_B.pls 120.29.12010000.5 2009/01/09 11:51:34 mbolli 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 EXCEPTION
108 WHEN OTHERS THEN
109 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
110 END;
111
112
113
114 PROCEDURE PRINT_CLOB (lob_loc in clob) IS
115
116 /*-----------------------------------------------------------------------+
117 | Local Variable Declarations and initializations |
118 +-----------------------------------------------------------------------*/
119
120 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
121 l_api_version CONSTANT NUMBER := 1.0;
122 c_endline CONSTANT VARCHAR2 (1) := '
123 ';
124 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
125 l_start NUMBER := 1;
126 l_end NUMBER;
127 l_one_line VARCHAR2 (7000);
128 l_charset VARCHAR2(100);
129
130 /*-----------------------------------------------------------------------+
131 | Cursor Declarations |
132 +-----------------------------------------------------------------------*/
133 BEGIN
134 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
135
136 LOOP
137 l_end :=
138 DBMS_LOB.INSTR (lob_loc => lob_loc,
139 pattern => c_endline,
140 offset => l_start,
141 nth => 1
142 );
143
144 IF (NVL (l_end, 0) < 1)
145 THEN
146 EXIT;
147 END IF;
148
149 l_one_line :=
150 DBMS_LOB.SUBSTR (lob_loc => lob_loc,
151 amount => l_end - l_start,
152 offset => l_start
153 );
154 l_start := l_end + c_endline_len;
155 --Fnd_File.PUT_line(Fnd_File.LOG,l_one_line);
156 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
157
158 END LOOP;
159 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
160 END PRINT_CLOB;
161 PROCEDURE PROCESS_PORTFOLIO_REPORT(ERRBUF OUT NOCOPY VARCHAR2
162 ,RETCODE OUT NOCOPY VARCHAR2
163 ,LOAN_CLASS IN VARCHAR2
164 ,LOAN_TYPE_ID IN NUMBER
165 ,CURRENCY_CODE IN VARCHAR2
166 ,LOAN_NUMBER IN VARCHAR2
167 ,BORROWER_ID IN NUMBER
168 ,CUST_ACCOUNT_ID IN NUMBER
169 ,LOAN_START_DATE_FROM IN VARCHAR2
170 ,LOAN_START_DATE_TO IN VARCHAR2
171 ,LEGAL_ENTITY_ID IN NUMBER
172 ,PRODUCT_ID IN NUMBER
173 ,LOAN_ASSIGNED_TO IN NUMBER
174 ,LOAN_STATUS1 IN VARCHAR2
175 ,LOAN_STATUS2 IN VARCHAR2
176 ,INCLUDE_CHARTS IN VARCHAR2
177 ) IS
178 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PORTFOLIO_REPORT';
179 l_api_version CONSTANT NUMBER := 1.0;
180 ctx DBMS_XMLQUERY.ctxType;
181 result CLOB;
182 qryCtx DBMS_XMLGEN.ctxHandle;
183 l_result CLOB;
184 tempResult CLOB;
185 l_version varchar2(20);
186 l_compatibility varchar2(20);
187 l_suffix varchar2(2);
188 l_majorVersion number;
189 l_resultOffset number;
190 l_xml_header varchar2(3000);
191 l_xml_header_length number;
192 l_errNo NUMBER;
193 l_errMsg VARCHAR2(200);
194 queryCtx DBMS_XMLquery.ctxType;
195 l_xml_query VARCHAR2(32767);
196 TYPE ref_cur IS REF CURSOR;
197 l_xml_stmt ref_cur;
198 l_rows_processed NUMBER;
199 l_new_line VARCHAR2(1);
200 l_org_id hr_operating_units.organization_id%TYPE;
201 l_org_name hr_operating_units.NAME%TYPE;
202 l_borrower_name hz_parties.party_name%TYPE;
203 l_account_number hz_cust_accounts.account_number%TYPE;
204 l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
205 l_start_date_from lns_loan_headers_all.loan_start_date%TYPE;
206 l_start_date_to lns_loan_headers_all.loan_start_date%TYPE;
207 l_loan_type_desc lns_loan_types.loan_type_desc%TYPE;
208 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
209 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
210 l_legal_entity_name xle_entity_profiles.name%TYPE;
211 l_loan_product_name lns_loan_products_all.loan_product_name%TYPE;
212 l_loan_officer jtf_rs_resource_extns.source_name%TYPE;
213 l_loan_status1_desc lns_lookups.meaning%TYPE;
214 l_loan_status2_desc lns_lookups.meaning%TYPE;
215 l_include_charts VARCHAR2(30);
216 l_close_tag VARCHAR2(100);
217 l_query VARCHAR2(5000) :=
218 'select ' ||
219 'lh.LOAN_ID, ' ||
220 'lh.loan_number ' ||
221 ',hp.party_name customer ' ||
222 ',llk.meaning || decode(lh.secondary_status,null,'''','': '') || llks.meaning loan_status_meaning ' ||
223 ',lh.loan_status || decode(lh.secondary_status,null,'''','': '') || lh.secondary_status loan_status ' ||
224 ',lh.loan_status primary_loan_status ' ||
225 ',lh.secondary_status secondary_loan_status ' ||
226 ',pay.TOTAL_PRINCIPAL_BALANCE loan_amount ' ||
227 ',(CASE
228 WHEN
229 (
230 lh.LOAN_STATUS = ''ACTIVE''
231 OR lh.LOAN_STATUS = ''DEFAULT''
232 OR lh.LOAN_STATUS = ''DELINQUENT''
233 OR lh.LOAN_STATUS = ''PAIDOFF''
234 )
235 THEN
236 pay.TOTAL_PRINCIPAL_BALANCE
237 ELSE
238 lh.REQUESTED_AMOUNT
239 END
240 ) loan_req_bal_amount' ||
241 ',lh.loan_description ' ||
242 ',to_char(lh.loan_maturity_date, ''MM/DD/YYYY'') loan_maturity_date ' ||
243 ',lh.loan_term || '' '' || llktt.meaning loan_length ' ||
244 ',to_char(lh.LOAN_APPLICATION_DATE, ''MM/DD/YYYY'') LOAN_APPLICATION_DATE ' ||
245 ',lh.ORG_ID ' ||
246 ',lh.LOAN_TYPE ' ||
247 ',lh.LOAN_CLASS_CODE ' ||
248 ',lh.LOAN_CURRENCY ' ||
249 ',LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate ' ||
250 ', llkrt.meaning interest_type ' ||
251 ',lh.LOAN_SUBTYPE ' ||
252 ',(select max(last_update_date) from LNS_APPROVAL_ACTIONS ' ||
253 'where loan_id = lh.LOAN_ID ' ||
254 'and ACTION_TYPE = ''SUBMIT_FOR_APPR'') submit_for_approval_date ' ||
255 ',lh.LAST_BILLED_DATE ' ||
256 ',lh.REQUESTED_AMOUNT original_requested_amount ' ||
257 ',lh.LOAN_APPROVAL_DATE ' ||
258 ',pay.TOTAL_PRIN_PAID_TODATE principal_paid ' ||
259 ',pay.INTEREST_PAID_TODATE interest_paid ' ||
260 ',pay.FEE_PAID_TODATE fees_paid ' ||
261 ',pay.NEXT_PAYMENT_DUE_DATE next_payment_due_date ' ||
262 ',pay.NEXT_PAYMENT_PRINCIPAL_DUE next_principal_due ' ||
263 ',pay.NEXT_PAYMENT_INTEREST_DUE next_interest_due ' ||
264 ',pay.NEXT_PAYMENT_FEE_DUE next_fees_due ' ||
265 ',pay.NEXT_PAYMENT_TOTAL_DUE next_payment_due ' ||
266 ',account.ACCOUNT_NUMBER ' ||
267 ', CURSOR (select ' ||
268 'REQUESTED_AMOUNT loan_requested_amount, ' ||
269 'REFERENCE_AMOUNT original_rec_balance, ' ||
270 'REFERENCE_NUMBER original_rec_desc ' ||
271 'from LNS_LOAN_LINES ' ||
272 'where loan_id = lh.LOAN_ID ' ||
273 'and end_date is null ) AS ORIGINAL_RECEIVABLES ' ||
274 'from ' ||
275 'lns_loan_headers_all lh, ' ||
276 'hz_parties hp, ' ||
277 'lns_terms t, ' ||
278 'lns_lookups llk, ' ||
279 'LNS_PAY_SUM_V pay, ' ||
280 'hz_cust_accounts_all account, ' ||
281 'lns_lookups llkrt, ' ||
282 'lns_lookups llktt, ' ||
283 'lns_lookups llks ' ||
284 'where ' ||
285 'lh.primary_borrower_id = hp.party_id and ' ||
286 'lh.loan_id = pay.loan_id and ' ||
287 'lh.loan_id = t.loan_id and ' ||
288 'llk.lookup_code = lh.loan_status and ' ||
289 'llk.lookup_type = ''LOAN_STATUS'' and ' ||
290 'llktt.lookup_code = lh.loan_term_period and ' ||
291 'llktt.lookup_type = ''PERIOD'' and ' ||
292 'llkrt.lookup_code = t.rate_type and ' ||
293 'llkrt.lookup_type = ''RATE_TYPE'' and ' ||
294 'llks.lookup_code(+) = lh.secondary_status and ' ||
295 'llks.lookup_type(+) = ''SECONDARY_STATUS'' and ' ||
296 'lh.loan_status <> ''DELETED'' and ' ||
297 'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
298 'lh.loan_class_code = :LOAN_CLASS and ' ||
299 'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
300 'lh.loan_currency = :CURRENCY_CODE and ' ||
301 'lh.org_id = :ORG_ID';
302 l_temp_where_clause VARCHAR2(200);
303 BEGIN
304 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
305 l_loan_type_id := loan_type_id;
306 l_legal_entity_id := legal_entity_id;
307 g_loan_start_date_from := trunc(fnd_date.canonical_to_date(loan_start_date_from));
308 g_loan_start_date_to := trunc(fnd_date.canonical_to_date(loan_start_date_to));
309 l_cust_account_id := cust_account_id;
310 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
311 IF nvl(INCLUDE_CHARTS, 'N') = 'Y' THEN
312 l_include_charts := 'Yes';
313 ELSE
314 l_include_charts := 'No';
315 END IF;
316 BEGIN
317 SELECT loan_type_desc
318 into l_loan_type_desc
319 from lns_loan_types_vl
320 where loan_type_id = l_loan_type_id;
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN null;
323 END;
324 -- Build Bind Placeholders for all non-mandatory parameters
325
326 IF loan_number IS NOT NULL
327 THEN
328 l_query := l_query || ' and lh.loan_number like :LOAN_NUMBER';
329 END IF;
330 IF borrower_id IS NOT NULL
331 THEN
332 l_query := l_query || ' and lh.primary_borrower_id = :BORROWER_ID';
333 END IF;
334 IF cust_account_id IS NOT NULL
335 THEN
336 l_query := l_query || ' and lh.cust_account_id = :CUST_ACCOUNT_ID';
337 END IF;
338 IF loan_start_date_from IS NOT NULL and loan_start_date_to is NULL THEN
339 l_query := l_query || ' and trunc(lh.loan_start_date) >= lns_rep_utils.get_loan_start_date_from()';
340 END IF;
341 IF loan_start_date_to IS NOT NULL and loan_start_date_from is NULL THEN
342 l_query := l_query || ' and trunc(lh.loan_start_date) <= lns_rep_utils.get_loan_start_date_to()';
343 END IF;
344 IF loan_start_date_from IS NOT NULL and loan_start_date_to is NOT NULL THEN
345 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()';
346 END IF;
347 IF legal_entity_id IS NOT NULL
348 THEN
349 l_query := l_query || ' and lh.legal_entity_id = :LEGAL_ENTITY_ID';
350 END IF;
351 IF product_id IS NOT NULL
352 THEN
353 l_query := l_query || ' and lh.product_id = :PRODUCT_ID';
354 END IF;
355 IF loan_assigned_to IS NOT NULL
356 THEN
357 l_query := l_query || ' and lh.loan_assigned_to = :LOAN_ASSIGNED_TO';
358 END IF;
359 IF loan_status1 IS NOT NULL and loan_status2 IS NULL
360 THEN
361 l_query := l_query || ' and lh.loan_status = ''' || LOAN_STATUS1 ||'''';
362 END IF;
363 IF loan_status2 IS NOT NULL and loan_status1 IS NULL
364 THEN
365 l_query := l_query || ' and lh.loan_status = ''' || LOAN_STATUS2 ||'''';
366 END IF;
367 IF loan_status1 IS NOT NULL and loan_status2 IS NOT NULL
368 THEN
369 --l_query := l_query || ' and (lh.loan_status = :LOAN_STATUS1 or lh.loan_status = :LOAN_STATUS2)';
370 l_query := l_query || ' and (lh.loan_status = ''' || LOAN_STATUS1 ||''' or lh.loan_status = ''' || LOAN_STATUS2 ||''')';
371 END IF;
372
373 -- Start Getting Parameter Display Values
374 SELECT name
375 INTO l_org_name
376 FROM HR_ALL_ORGANIZATION_UNITS_TL
377 WHERE organization_id = l_org_id
378 AND language = userenv('LANG');
379
380 IF borrower_id is NOT NULL
381 THEN
382 SELECT PARTY_NAME
383 INTO l_borrower_name
384 FROM hz_parties
385 WHERE party_id = borrower_id;
386 END IF;
387
388 IF cust_account_id is NOT NULL
389 THEN
390 SELECT account_number
391 into l_account_number
392 FROM hz_cust_accounts
393 WHERE cust_account_id = l_cust_account_id;
394 END IF;
395 IF loan_status1 is NOT NULL
396 THEN
397 SELECT meaning
398 into l_loan_status1_desc
399 from lns_lookups
400 where lookup_type = 'LOAN_STATUS'
401 and lookup_code = loan_status1;
402 END IF;
403 IF loan_status2 is NOT NULL
404 THEN
405 SELECT meaning
406 into l_loan_status2_desc
407 from lns_lookups
408 where lookup_type = 'LOAN_STATUS'
409 and lookup_code = loan_status2;
410 END IF;
411 IF legal_entity_id is NOT NULL
412 THEN
413 SELECT NAME
414 INTO l_legal_entity_name
415 FROM xle_entity_profiles
416 WHERE legal_entity_id = l_legal_entity_id;
417 END IF;
418 IF product_id is NOT NULL
419 THEN
420 SELECT loan_product_name
421 INTO l_loan_product_name
422 FROM lns_loan_products_all_vl
423 WHERE loan_product_id = product_id;
424 END IF;
425 IF loan_assigned_to is NOT NULL
426 THEN
427 SELECT source_name
428 INTO l_loan_officer
429 FROM jtf_rs_resource_extns
430 WHERE resource_id = loan_assigned_to;
431 END IF;
432 ctx := DBMS_XMLQUERY.newContext(l_query);
433 -- Bind Mandatory Variables
434 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_CLASS', loan_class);
435 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_TYPE_ID', loan_type_id);
436 DBMS_XMLQuery.setBindValue(ctx, 'CURRENCY_CODE', currency_code);
437 DBMS_XMLQuery.setBindValue(ctx, 'ORG_ID', l_org_id);
438
439 -- Bind Optional Variables if they are NOT NULL
440 IF borrower_id is NOT NULL
441 THEN
442 DBMS_XMLQuery.setBindValue(ctx, 'BORROWER_ID', borrower_id);
443 END IF;
444 IF loan_number is NOT NULL
445 THEN
446 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_NUMBER', loan_number);
447 END IF;
448 IF cust_account_id is NOT NULL
449 THEN
450 DBMS_XMLQuery.setBindValue(ctx, 'CUST_ACCOUNT_ID', cust_account_id);
451 END IF;
452 IF legal_entity_id is NOT NULL
453 THEN
454 DBMS_XMLQuery.setBindValue(ctx, 'LEGAL_ENTITY_ID', legal_entity_id);
455 END IF;
456 IF loan_assigned_to is NOT NULL
457 THEN
458 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_ASSIGNED_TO', loan_assigned_to);
459 END IF;
460 IF product_id is NOT NULL
461 THEN
462 DBMS_XMLQuery.setBindValue(ctx, 'PRODUCT_ID', product_id);
463 END IF;
464
465 -- now get the result
466 BEGIN
467 l_result := DBMS_XMLQUERY.getXML(ctx);
468 DBMS_XMLQuery.closeContext(ctx);
469 l_rows_processed := 1;
470 EXCEPTION
471 WHEN OTHERS THEN
472 DBMS_XMLQuery.getExceptionContent(ctx,l_errNo,l_errMsg);
473 IF l_errNo = 1403 THEN
474 l_rows_processed := 0;
475 END IF;
476 DBMS_XMLQuery.closeContext(ctx);
477 END;
478 -- We are adding the LNSPORTFOLIO and PARAMETERS TAGs so we have
479 -- to offset the first line.
480 IF l_rows_processed <> 0 THEN
481 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
482 tempResult := l_result;
483 ELSE
484 l_resultOffset := 0;
485 END IF;
486
487 l_new_line := '
488 ';
489 /* Prepare the tag for the report heading */
490 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
491 l_xml_header := l_xml_header ||l_new_line||'<LNSPORTFOLIO>';
492 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
493 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||l_org_name||'</ORG_NAME>';
494 l_xml_header := l_xml_header ||l_new_line||' <LOAN_CLASS_CODE>' ||loan_class ||'</LOAN_CLASS_CODE>';
495 l_xml_header := l_xml_header ||l_new_line||' <LOAN_TYPE_DESC>' ||l_loan_type_desc ||'</LOAN_TYPE_DESC>';
496 l_xml_header := l_xml_header ||l_new_line||' <CURRENCY_CODE>' ||currency_code ||'</CURRENCY_CODE>';
497 l_xml_header := l_xml_header ||l_new_line||' <LOAN_NUMBER>' ||loan_number ||'</LOAN_NUMBER>';
498 l_xml_header := l_xml_header ||l_new_line||' <BORROWER_NAME>' ||l_borrower_name ||'</BORROWER_NAME>';
499 l_xml_header := l_xml_header ||l_new_line||' <ACCOUNT_NUMBER>' ||l_account_number ||'</ACCOUNT_NUMBER>';
500 l_xml_header := l_xml_header ||l_new_line||' <LOAN_START_DATE_FROM>' || loan_start_date_from ||'</LOAN_START_DATE_FROM>';
501 l_xml_header := l_xml_header ||l_new_line||' <LOAN_START_DATE_TO>' || loan_start_date_to ||'</LOAN_START_DATE_TO>';
502 l_xml_header := l_xml_header ||l_new_line||' <LOAN_STATUS1_DESC>' ||l_loan_status1_desc ||'</LOAN_STATUS1_DESC>';
503 l_xml_header := l_xml_header ||l_new_line||' <LOAN_STATUS2_DESC>' ||l_loan_status2_desc ||'</LOAN_STATUS2_DESC>';
504 l_xml_header := l_xml_header ||l_new_line||' <LEGAL_ENTITY_NAME>' ||l_legal_entity_name ||'</LEGAL_ENTITY_NAME>';
505 l_xml_header := l_xml_header ||l_new_line||' <LOAN_PRODUCT_NAME>' ||l_loan_product_name ||'</LOAN_PRODUCT_NAME>';
506 l_xml_header := l_xml_header ||l_new_line||' <LOAN_OFFICER>' ||l_loan_officer ||'</LOAN_OFFICER>';
507 l_xml_header := l_xml_header ||l_new_line||' <INCLUDE_CHARTS>' ||l_include_charts ||'</INCLUDE_CHARTS>';
508 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
509 l_close_tag := l_new_line||'</LNSPORTFOLIO>'||l_new_line;
510 l_xml_header_length := length(l_xml_header);
511 IF l_rows_processed <> 0 THEN
512 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
513 dbms_lob.copy(tempResult,l_result
514 ,dbms_lob.getlength(l_result)-l_resultOffset
515 ,l_xml_header_length,l_resultOffset);
516 ELSE
517 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
518 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
519 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
520 END IF;
521
522 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
523 print_clob(lob_loc => tempResult);
524 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
525 EXCEPTION
526 WHEN OTHERS THEN
527 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
528 || ' -');
529 RAISE;
530 END PROCESS_PORTFOLIO_REPORT;
531 PROCEDURE PROCESS_RECON_REPORT(ERRBUF OUT NOCOPY VARCHAR2
532 ,RETCODE OUT NOCOPY VARCHAR2
533 ,LOAN_CLASS IN VARCHAR2
534 ,LOAN_TYPE_ID IN NUMBER
535 ,CURRENCY_CODE IN VARCHAR2
536 ,BILL_DUE_DATE_FROM IN VARCHAR2
537 ,BILL_DUE_DATE_TO IN VARCHAR2
538 ,LEGAL_ENTITY_ID IN NUMBER
539 ,LOAN_NUMBER IN VARCHAR2
540 ,BORROWER_ID IN NUMBER
541 ,CUST_ACCOUNT_ID IN NUMBER
542 ,LOAN_ASSIGNED_TO IN NUMBER
543 ) IS
544 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_RECON_REPORT';
545 l_api_version CONSTANT NUMBER := 1.0;
546 ctx DBMS_XMLQUERY.ctxType;
547 result CLOB;
548 qryCtx DBMS_XMLGEN.ctxHandle;
549 l_result CLOB;
550 tempResult CLOB;
551 l_version varchar2(20);
552 l_compatibility varchar2(20);
553 l_suffix varchar2(2);
554 l_majorVersion number;
555 l_resultOffset number;
556 l_xml_header varchar2(3000);
557 l_xml_header_length number;
558 l_errNo NUMBER;
559 l_errMsg VARCHAR2(200);
560 queryCtx DBMS_XMLquery.ctxType;
561 l_xml_query VARCHAR2(32767);
562 TYPE ref_cur IS REF CURSOR;
563 l_xml_stmt ref_cur;
564 l_rows_processed NUMBER;
565 l_new_line VARCHAR2(1);
566 l_org_id hr_operating_units.organization_id%TYPE;
567 l_org_name hr_operating_units.NAME%TYPE;
568 l_borrower_name hz_parties.party_name%TYPE;
569 l_account_number hz_cust_accounts.account_number%TYPE;
570 l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
571 l_start_date_from lns_loan_headers_all.loan_start_date%TYPE;
572 l_start_date_to lns_loan_headers_all.loan_start_date%TYPE;
573 l_loan_type_desc lns_loan_types.loan_type_desc%TYPE;
574 l_loan_type_id lns_loan_types.loan_type_id%TYPE;
575 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
576 l_legal_entity_name xle_entity_profiles.name%TYPE;
577 l_loan_officer jtf_rs_resource_extns.source_name%TYPE;
578 l_loan_status1_desc lns_lookups.meaning%TYPE;
579 l_loan_status2_desc lns_lookups.meaning%TYPE;
580 l_close_tag VARCHAR2(100);
581 l_query VARCHAR2(11000) :=
582 'select ' ||
583 'lh.LOAN_ID, ' ||
584 'lh.loan_number ' ||
585 ',hp.party_name customer ' ||
586 ',llk.meaning loan_status_meaning ' ||
587 ',lh.loan_status ' ||
588 ',pay.TOTAL_PRINCIPAL_BALANCE loan_balance ' ||
589 ',lh.loan_description ' ||
590 ',account.ACCOUNT_NUMBER ' ||
591 ',lh.FUNDED_AMOUNT original_loan_amount ' ||
592 ',to_char(lh.loan_maturity_date, ''MM/DD/YYYY'') loan_maturity_date ' ||
593 ',lh.ORG_ID ' ||
594 ',lot.loan_type_name loan_type ' ||
595 ',lh.LOAN_CLASS_CODE ' ||
596 ',lh.LOAN_CURRENCY ' ||
597 ',(loc.address1 || '' '' || loc.city || '' '' || loc.state || '' '' || ' ||
598 'loc.postal_code || '' '' || terr.TERRITORY_SHORT_NAME) Address ' ||
599 ',ou.name operating_unit ' ||
600 ',lh.LEGAL_ENTITY_ID ' ||
601 ',le.NAME LEGAL_ENTITY_NAME ' ||
602 ',lh.product_id ' ||
603 ',product.loan_product_name loan_product ' ||
604 ',nvl((select sum(line.line_amount) ' ||
605 ' from lns_disb_lines line, ' ||
606 ' lns_disb_headers head ' ||
607 ' where head.loan_id = lh.LOAN_ID and ' ||
608 ' head.disb_header_id = line.disb_header_id and ' ||
609 ' line.status = ''FULLY_FUNDED''), 0) disbursed_amount ' ||
610 ',nvl(lh.CURRENT_PHASE, ''TERM'') current_phase ' ||
611 ', CURSOR (select ' ||
612 ' LOAN_ID loan_id, ' ||
613 ' REFERENCE_NUMBER original_rec_number, ' ||
614 ' REFERENCE_AMOUNT original_rec_balance, ' ||
615 ' REQUESTED_AMOUNT loan_requested_amount, ' ||
616 ' REFERENCE_NUMBER original_rec_desc ' ||
617 ' from LNS_LOAN_LINES ' ||
618 ' where loan_id = lh.LOAN_ID ' ||
619 ' and end_date is null ' ||
620 ') AS ORIGINAL_RECEIVABLES ' ||
621 ', CURSOR ( ' ||
622 ' select ' ||
623 ' max(amv.loan_id) loan_id, ' ||
624 ' sum(amv.PRINCIPAL_AMOUNT) prin_amortization_amount, ' ||
625 ' sum(amv.PRIN_CASH) prin_receipt_payments, ' ||
626 ' sum(amv.PRIN_NET_CREDIT) prin_credit_netting, ' ||
627 ' sum(amv.PRIN_LOAN_PMT_CREDIT) prin_credit_payments, ' ||
628 ' sum(amv.PRIN_OTHER_CREDIT) prin_credit_non_payments, ' ||
629 ' sum(amv.PRIN_ADJ) prin_adjustments, ' ||
630 ' sum(amv.PRINCIPAL_REMAINING) prin_remaining_amount, ' ||
631 ' sum(amv.INTEREST_AMOUNT) int_amortization_amount, ' ||
632 ' sum(amv.INT_CASH) int_receipt_payments, ' ||
633 ' sum(amv.INT_NET_CREDIT) int_credit_netting, ' ||
634 ' sum(amv.INT_LOAN_PMT_CREDIT) int_credit_payments, ' ||
635 ' sum(amv.INT_OTHER_CREDIT) int_credit_non_payments, ' ||
636 ' sum(amv.INT_ADJ) int_adjustments, ' ||
637 ' sum(amv.INTEREST_REMAINING) int_remaining_amount, ' ||
638 ' sum(amv.FEE_AMOUNT) fee_amortization_amount, ' ||
639 ' sum(amv.FEE_CASH) fee_receipt_payments, ' ||
640 ' sum(amv.FEE_NET_CREDIT) fee_credit_netting, ' ||
641 ' sum(amv.FEE_LOAN_PMT_CREDIT) fee_credit_payments, ' ||
642 ' sum(amv.FEE_OTHER_CREDIT) fee_credit_non_payments, ' ||
643 ' sum(amv.FEE_ADJ) fee_adjustments, ' ||
644 ' sum(amv.FEE_REMAINING) fee_remaining_amount ' ||
645 ' from ' ||
646 ' LNS_AM_SCHEDS_DTL_V amv ' ||
647 ' where amv.loan_id = lh.LOAN_ID and ' ||
648 ' amv.REVERSED_CODE = ''N'' and ' ||
649 ' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
650 ') AS BILL_PAY_SUMMARY ' ||
651 ', CURSOR ( ' ||
652 ' select ' ||
653 ' am.PAYMENT_NUMBER, ' ||
654 ' lok.MEANING purpose, ' ||
655 ' decode(trx.customer_trx_id, ' ||
656 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
657 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
658 ' am.FEE_TRX_ID, am.FEE_AMOUNT) amortization_amount, ' ||
659 ' psa.AMOUNT_DUE_ORIGINAL ar_trx_amount, ' ||
660 ' (decode(trx.customer_trx_id, ' ||
661 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
662 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
663 ' am.FEE_TRX_ID, am.FEE_AMOUNT) ' ||
664 ' - psa.AMOUNT_DUE_ORIGINAL) difference, ' ||
665 ' trx.INTERFACE_HEADER_ATTRIBUTE1 ar_trx_reference ' ||
666 ' from ' ||
667 ' lns_amortization_scheds am, ' ||
668 ' ar_payment_schedules_all psa, ' ||
669 ' RA_CUSTOMER_TRX_ALL trx, ' ||
670 ' LNS_LOOKUPS lok ' ||
671 ' where am.loan_id = lh.LOAN_ID and ' ||
672 ' (am.REVERSED_FLAG is null or am.REVERSED_FLAG = ''N'') and ' ||
673 ' trunc(am.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt and ' ||
674 ' (trx.customer_trx_id = am.PRINCIPAL_TRX_ID or ' ||
675 ' trx.customer_trx_id = am.INTEREST_TRX_ID or ' ||
676 ' trx.customer_trx_id = am.FEE_TRX_ID) and ' ||
677 ' psa.customer_trx_id = trx.customer_trx_id and ' ||
678 ' lok.lookup_type = ''PAYMENT_APPLICATION_TYPE'' and ' ||
679 ' lok.lookup_code = decode(trx.customer_trx_id, am.PRINCIPAL_TRX_ID, ' ||
680 ' ''PRIN'', am.INTEREST_TRX_ID, ''INT'', am.FEE_TRX_ID, ''FEES'') and ' ||
681 ' (decode(trx.customer_trx_id, ' ||
682 ' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
683 ' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
684 ' am.FEE_TRX_ID, am.FEE_AMOUNT) <> psa.AMOUNT_DUE_ORIGINAL) ' ||
685 ') AS EXCEPTION_FLAGS , ' ||
686 'CURSOR ( ' ||
687 ' select ' ||
688 ' amv.PAYMENT_NUMBER, ' ||
689 ' amv.BILL_TYPE_DESC meaning, ' ||
690 ' amv.PRINCIPAL_AMOUNT billed_principal_amount, ' ||
691 ' amv.PRIN_CASH prin_receipt_payments, ' ||
692 ' amv.PRIN_NET_CREDIT prin_credit_netting, ' ||
693 ' amv.PRIN_LOAN_PMT_CREDIT prin_credit_payments, ' ||
694 ' amv.PRIN_OTHER_CREDIT prin_credit_non_payments, ' ||
695 ' amv.PRIN_ADJ prin_adjustments, ' ||
696 ' amv.PRINCIPAL_REMAINING prin_remaining_amount, ' ||
697 ' amv.INTEREST_AMOUNT billed_int_amount, ' ||
698 ' amv.INT_CASH int_receipt_payments, ' ||
699 ' amv.INT_NET_CREDIT int_credit_netting, ' ||
700 ' amv.INT_LOAN_PMT_CREDIT int_credit_payments, ' ||
701 ' amv.INT_OTHER_CREDIT int_credit_non_payments, ' ||
702 ' amv.INT_ADJ int_adjustments, ' ||
703 ' amv.INTEREST_REMAINING int_remaining_amount, ' ||
704 ' amv.FEE_AMOUNT billed_fee_amount, ' ||
705 ' amv.FEE_CASH fee_receipt_payments, ' ||
706 ' amv.FEE_NET_CREDIT fee_credit_netting, ' ||
707 ' amv.FEE_LOAN_PMT_CREDIT fee_credit_payments, ' ||
708 ' amv.FEE_OTHER_CREDIT fee_credit_non_payments, ' ||
709 ' amv.FEE_ADJ fee_adjustments, ' ||
710 ' amv.FEE_REMAINING fee_remaining_amount, ' ||
711 ' to_char(amv.DUE_DATE,''MM/DD/YYYY'') due_date, ' ||
712 ' amv.PHASE, ' ||
713 ' CURSOR ( ' ||
714 ' select ' ||
715 ' rec.PAYMENT_NUMBER, ' ||
716 ' rec.line_type_desc, ' ||
717 ' rec.line_desc, ' ||
718 ' rec.activity_desc, ' ||
719 ' rec.activity_amount, ' ||
720 ' rec.activity_number, ' ||
721 ' to_char(rec.activity_date,''MM/DD/YYYY'') activity_date, ' ||
722 ' rec.trx_currency, ' ||
723 ' rec.receipt_amt_applied_from, ' ||
724 ' rec.receipt_currency, ' ||
725 ' rec.trx_to_receipt_rate ' ||
726 ' from ' ||
727 ' LNS_REC_ACT_CASH_CM_V rec ' ||
728 ' where ' ||
729 ' amv.loan_id = rec.loan_id and ' ||
730 ' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
731 ' ) AS PAYMENT_ACTIVITY_CASH_CM, ' ||
732 ' CURSOR ( ' ||
733 ' select ' ||
734 ' rec.PAYMENT_NUMBER, ' ||
735 ' rec.line_type_desc, ' ||
736 ' rec.line_desc, ' ||
737 ' rec.activity_desc, ' ||
738 ' rec.activity_amount, ' ||
739 ' rec.activity_number, ' ||
740 ' to_char(rec.activity_date,''MM/DD/YYYY'') activity_date, ' ||
741 ' rec.trx_currency, ' ||
742 ' rec.receipt_amt_applied_from, ' ||
743 ' rec.receipt_currency, ' ||
744 ' rec.trx_to_receipt_rate ' ||
745 ' from ' ||
746 ' LNS_REC_ACT_ADJ_V rec ' ||
747 ' where ' ||
748 ' amv.loan_id = rec.loan_id and ' ||
749 ' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
750 ' ) AS PAYMENT_ACTIVITY_ADJ ' ||
751 ' from ' ||
752 ' LNS_AM_SCHEDS_DTL_V amv ' ||
753 ' where amv.loan_id = lh.LOAN_ID and ' ||
754 ' amv.REVERSED_CODE = ''N'' and ' ||
755 ' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
756 ') AS BILL_PAY_DTL_BY_PAY_NUM, ' ||
757 'CURSOR( ' ||
758 ' select head.DISB_HEADER_ID, ' ||
759 ' head.DISBURSEMENT_NUMBER, ' ||
760 ' to_char(head.TARGET_DATE, ''MM/DD/YYYY'') target_date, ' ||
761 ' to_char(head.PAYMENT_REQUEST_DATE, ''MM/DD/YYYY'') payment_request_date, ' ||
762 ' head.HEADER_PERCENT, ' ||
763 ' head.HEADER_AMOUNT, ' ||
764 ' fund_status.meaning status, ' ||
765 ' fund_act.meaning activity_name, ' ||
766 ' (select to_char(max(DISBURSEMENT_DATE),''MM/DD/YYYY'') from lns_disb_lines where DISB_HEADER_ID = head.DISB_HEADER_ID) DISBURSEMENT_DATE ' ||
767 ' from lns_disb_headers head, ' ||
768 ' lns_lookups fund_status, ' ||
769 ' lns_lookups fund_act ' ||
770 ' where head.loan_id = lh.loan_id and ' ||
771 ' fund_status.lookup_type(+) = ''FUNDING_STATUS'' and ' ||
772 ' fund_status.lookup_code(+) = head.STATUS and ' ||
773 ' fund_act.lookup_type(+) = ''DISB_ACTIVITY'' and ' ||
774 ' fund_act.lookup_code(+) = head.ACTIVITY_CODE) ' ||
775 'AS Disbursement_Schedule ' ||
776 'from ' ||
777 'lns_loan_headers_all lh, ' ||
778 '(select lns_rep_utils.get_bill_due_date_from() from_dt, ' ||
779 ' lns_rep_utils.get_bill_due_date_to() to_dt ' ||
780 ' from dual) dateparameters, ' ||
781 'hz_parties hp, ' ||
782 'lns_lookups llk, ' ||
783 'LNS_PAY_SUM_V pay, ' ||
784 'hz_cust_accounts_all account, ' ||
785 'hz_locations loc, ' ||
786 'fnd_territories_vl terr, ' ||
787 'hz_party_sites site, ' ||
788 'hz_cust_acct_sites_all acct_site, ' ||
789 'lns_loan_products_vl product, ' ||
790 'xle_entity_profiles le, ' ||
791 'hr_operating_units ou, ' ||
792 'lns_loan_types_vl lot ' ||
793 'where ' ||
794 'lh.primary_borrower_id = hp.party_id and ' ||
795 'lh.loan_id = pay.loan_id and ' ||
796 'llk.lookup_code = lh.loan_status and ' ||
797 'llk.lookup_type = ''LOAN_STATUS'' and ' ||
798 'lh.loan_status <> ''DELETED'' and ' ||
799 'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
800 'acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and ' ||
801 'acct_site.org_id = lh.org_id and ' ||
802 'site.party_site_id = acct_site.party_site_id and ' ||
803 'site.location_id = loc.location_id and ' ||
804 'loc.country = terr.TERRITORY_CODE and ' ||
805 'lh.loan_class_code = :LOAN_CLASS and ' ||
806 'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
807 'lh.loan_currency = :CURRENCY_CODE and ' ||
808 'lh.product_id = product.LOAN_PRODUCT_ID(+) and ' ||
809 'le.LEGAL_ENTITY_ID = lh.LEGAL_ENTITY_ID and ' ||
810 'ou.organization_id = lh.org_id and ' ||
811 'lh.org_id = :ORG_ID and ' ||
812 'lot.loan_type_id = lh.loan_type_id and ' ||
813 'EXISTS ' ||
814 ' (select loan_id ' ||
815 ' from lns_amortization_scheds am ' ||
816 ' where am.loan_id = lh.loan_id and ' ||
817 ' (REVERSED_FLAG is null or REVERSED_FLAG = ''N'') and ' ||
818 ' trunc(am.DUE_DATE) between lns_rep_utils.get_bill_due_date_from() ' ||
819 ' and lns_rep_utils.get_bill_due_date_to())';
820 l_temp_where_clause VARCHAR2(200);
821 BEGIN
822 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
823 l_loan_type_id := loan_type_id;
824 l_legal_entity_id := legal_entity_id;
825 g_bill_due_date_from := trunc(fnd_date.canonical_to_date(bill_due_date_from));
826 g_bill_due_date_to := trunc(fnd_date.canonical_to_date(bill_due_date_to));
827 l_cust_account_id := cust_account_id;
828 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
829 BEGIN
830 SELECT loan_type_desc
831 into l_loan_type_desc
832 from lns_loan_types_vl
833 where loan_type_id = l_loan_type_id;
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN null;
836 END;
837 -- Build Bind Placeholders for all non-mandatory parameters
838
839 IF loan_number IS NOT NULL
840 THEN
841 l_query := l_query || ' and lh.loan_number like :LOAN_NUMBER';
842 END IF;
843 IF borrower_id IS NOT NULL
844 THEN
845 l_query := l_query || ' and lh.primary_borrower_id = :BORROWER_ID';
846 END IF;
847 IF cust_account_id IS NOT NULL
848 THEN
849 l_query := l_query || ' and lh.cust_account_id = :CUST_ACCOUNT_ID';
850 END IF;
851 IF legal_entity_id IS NOT NULL
852 THEN
853 l_query := l_query || ' and lh.legal_entity_id = :LEGAL_ENTITY_ID';
854 END IF;
855 IF loan_assigned_to IS NOT NULL
856 THEN
857 l_query := l_query || ' and lh.loan_assigned_to = :LOAN_ASSIGNED_TO';
858 END IF;
859
860 -- Start Getting Parameter Display Values
861 SELECT name
862 INTO l_org_name
863 FROM HR_ALL_ORGANIZATION_UNITS_TL
864 WHERE organization_id = l_org_id
865 AND language = userenv('LANG');
866
867 IF borrower_id is NOT NULL
868 THEN
869 SELECT PARTY_NAME
870 INTO l_borrower_name
871 FROM hz_parties
872 WHERE party_id = borrower_id;
873 END IF;
874
875 IF cust_account_id is NOT NULL
876 THEN
877 SELECT account_number
878 into l_account_number
879 FROM hz_cust_accounts
880 WHERE cust_account_id = l_cust_account_id;
881 END IF;
882 IF legal_entity_id is NOT NULL
883 THEN
884 SELECT NAME
885 INTO l_legal_entity_name
886 FROM xle_entity_profiles
887 WHERE legal_entity_id = l_legal_entity_id;
888 END IF;
889 IF loan_assigned_to is NOT NULL
890 THEN
891 SELECT source_name
892 INTO l_loan_officer
893 FROM jtf_rs_resource_extns
894 WHERE resource_id = loan_assigned_to;
895 END IF;
896 ctx := DBMS_XMLQUERY.newContext(l_query);
897 DBMS_XMLQuery.setRaiseNoRowsException(ctx,TRUE);
898 -- Bind Mandatory Variables
899 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_CLASS', loan_class);
900 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_TYPE_ID', loan_type_id);
901 DBMS_XMLQuery.setBindValue(ctx, 'CURRENCY_CODE', currency_code);
902 DBMS_XMLQuery.setBindValue(ctx, 'ORG_ID', l_org_id);
903
904 -- Bind Optional Variables if they are NOT NULL
905 IF borrower_id is NOT NULL
906 THEN
907 DBMS_XMLQuery.setBindValue(ctx, 'BORROWER_ID', borrower_id);
908 END IF;
909 IF loan_number is NOT NULL
910 THEN
911 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_NUMBER', loan_number);
912 END IF;
913 IF cust_account_id is NOT NULL
914 THEN
915 DBMS_XMLQuery.setBindValue(ctx, 'CUST_ACCOUNT_ID', cust_account_id);
916 END IF;
917 IF legal_entity_id is NOT NULL
918 THEN
919 DBMS_XMLQuery.setBindValue(ctx, 'LEGAL_ENTITY_ID', legal_entity_id);
920 END IF;
921 IF loan_assigned_to is NOT NULL
922 THEN
923 DBMS_XMLQuery.setBindValue(ctx, 'LOAN_ASSIGNED_TO', loan_assigned_to);
924 END IF;
925
926 -- now get the result
927 BEGIN
928 l_result := DBMS_XMLQUERY.getXML(ctx);
929 DBMS_XMLQuery.closeContext(ctx);
930 l_rows_processed := 1;
931 EXCEPTION
932 WHEN OTHERS THEN
933 DBMS_XMLQuery.getExceptionContent(ctx,l_errNo,l_errMsg);
934 IF l_errNo = 1403 THEN
935 l_rows_processed := 0;
936 END IF;
937 DBMS_XMLQuery.closeContext(ctx);
938 END;
939 -- We are adding the LNSPORTFOLIO and PARAMETERS TAGs so we have
940 -- to offset the first line.
941 IF l_rows_processed <> 0 THEN
942 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
943 tempResult := l_result;
944 ELSE
945 l_resultOffset := 0;
946 END IF;
947
948 l_new_line := '
949 ';
950 /* Prepare the tag for the report heading */
951 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
952 l_xml_header := l_xml_header ||l_new_line||'<LNSRECONCILIATION>';
953 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
954 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||l_org_name||'</ORG_NAME>';
955 l_xml_header := l_xml_header ||l_new_line||' <LOAN_CLASS_CODE>' ||loan_class ||'</LOAN_CLASS_CODE>';
956 l_xml_header := l_xml_header ||l_new_line||' <LOAN_TYPE_DESC>' ||l_loan_type_desc ||'</LOAN_TYPE_DESC>';
957 l_xml_header := l_xml_header ||l_new_line||' <CURRENCY_CODE>' ||currency_code ||'</CURRENCY_CODE>';
958 l_xml_header := l_xml_header ||l_new_line||' <BILL_DUE_DATE_FROM>' || bill_due_date_from ||'</BILL_DUE_DATE_FROM>';
959 l_xml_header := l_xml_header ||l_new_line||' <BILL_DUE_DATE_TO>' || bill_due_date_to ||'</BILL_DUE_DATE_TO>';
960 l_xml_header := l_xml_header ||l_new_line||' <LEGAL_ENTITY_NAME>' ||l_legal_entity_name ||'</LEGAL_ENTITY_NAME>';
961 l_xml_header := l_xml_header ||l_new_line||' <LOAN_NUMBER>' ||loan_number ||'</LOAN_NUMBER>';
962 l_xml_header := l_xml_header ||l_new_line||' <BORROWER_NAME>' ||l_borrower_name ||'</BORROWER_NAME>';
963 l_xml_header := l_xml_header ||l_new_line||' <ACCOUNT_NUMBER>' ||l_account_number ||'</ACCOUNT_NUMBER>';
964 l_xml_header := l_xml_header ||l_new_line||' <LOAN_OFFICER>' ||l_loan_officer ||'</LOAN_OFFICER>';
965 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
966 l_close_tag := l_new_line||'</LNSRECONCILIATION>'||l_new_line;
967 l_xml_header_length := length(l_xml_header);
968 IF l_rows_processed <> 0 THEN
969 dbms_lob.write(tempResult,l_xml_header_length,1,l_xml_header);
970 dbms_lob.copy(tempResult,l_result
971 ,dbms_lob.getlength(l_result)-l_resultOffset
972 ,l_xml_header_length,l_resultOffset);
973 ELSE
974 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
975 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
976 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
977 END IF;
978
979 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
980 print_clob(lob_loc => tempResult);
981 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
982 EXCEPTION
983 WHEN OTHERS THEN
984 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
985 || ' -');
986 RAISE;
987 END PROCESS_RECON_REPORT;
988 PROCEDURE GEN_AGREEMENT_XML(p_AgreementXML OUT NOCOPY CLOB
989 ,p_LOAN_ID IN NUMBER
990 ) IS
991 l_api_name CONSTANT VARCHAR2(30) := 'GEN_AGREEMENT_XML';
992 l_api_version CONSTANT NUMBER := 1.0;
993 ctx DBMS_XMLQUERY.ctxType;
994 result CLOB;
995 qryCtx DBMS_XMLGEN.ctxHandle;
996 l_result CLOB;
997 tempResult CLOB;
998 l_version varchar2(20);
999 l_compatibility varchar2(20);
1000 l_suffix varchar2(2);
1001 l_majorVersion number;
1002 l_resultOffset number;
1003 l_xml_header varchar2(3000);
1004 l_xml_header_length number;
1005 l_close_tag VARCHAR2(100);
1006 l_errNo NUMBER;
1007 l_errMsg VARCHAR2(200);
1008 queryCtx DBMS_XMLquery.ctxType;
1009 l_xml_query VARCHAR2(32767);
1010 TYPE ref_cur IS REF CURSOR;
1011 l_xml_stmt ref_cur;
1012 l_rows_processed NUMBER;
1013 l_new_line VARCHAR2(1);
1014 l_org_id hr_operating_units.organization_id%TYPE;
1015 l_org_name hr_operating_units.NAME%TYPE;
1016 l_sob_currency_code gl_sets_of_books.CURRENCY_CODE%TYPE;
1017 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1018 l_return_status VARCHAR2(10);
1019 l_msg_count NUMBER;
1020 l_msg_data VARCHAR2(32767);
1021
1022 type INSTALLMENT_NUMBER_tab_t is table of lns_amort_scheds_gt.INSTALLMENT_NUMBER%TYPE
1023 index by pls_integer;
1024 v_INSTALLMENT_NUMBER INSTALLMENT_NUMBER_tab_t;
1025 type DUE_DATE_tab_t is table of lns_amort_scheds_gt.DUE_DATE%TYPE
1026 index by pls_integer;
1027 v_DUE_DATE DUE_DATE_tab_t;
1028 type PRINCIPAL_AMOUNT_tab_t is table of lns_amort_scheds_gt.PRINCIPAL_AMOUNT%TYPE
1029 index by pls_integer;
1030 v_PRINCIPAL_AMOUNT PRINCIPAL_AMOUNT_tab_t;
1031 type INTEREST_AMOUNT_tab_t is table of lns_amort_scheds_gt.INTEREST_AMOUNT%TYPE
1032 index by pls_integer;
1033 v_INTEREST_AMOUNT INTEREST_AMOUNT_tab_t;
1034 type FEE_AMOUNT_tab_t is table of lns_amort_scheds_gt.FEE_AMOUNT%TYPE
1035 index by pls_integer;
1036 v_FEE_AMOUNT FEE_AMOUNT_tab_t;
1037 type OTHER_AMOUNT_tab_t is table of lns_amort_scheds_gt.OTHER_AMOUNT%TYPE
1038 index by pls_integer;
1039 v_OTHER_AMOUNT OTHER_AMOUNT_tab_t;
1040 type BEGIN_BALANCE_tab_t is table of lns_amort_scheds_gt.BEGIN_BALANCE%TYPE
1041 index by pls_integer;
1042 v_BEGIN_BALANCE BEGIN_BALANCE_tab_t;
1043 type END_BALANCE_tab_t is table of lns_amort_scheds_gt.END_BALANCE%TYPE
1044 index by pls_integer;
1045 v_END_BALANCE END_BALANCE_tab_t;
1046 type TOTAL_tab_t is table of lns_amort_scheds_gt.TOTAL%TYPE
1047 index by pls_integer;
1048 v_TOTAL TOTAL_tab_t;
1049 type INTEREST_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.INTEREST_CUMULATIVE%TYPE
1050 index by pls_integer;
1051 v_INTEREST_CUMULATIVE INTEREST_CUMULATIVE_tab_t;
1052 type PRINCIPAL_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.PRINCIPAL_CUMULATIVE%TYPE
1053 index by pls_integer;
1054 v_PRINCIPAL_CUMULATIVE PRINCIPAL_CUMULATIVE_tab_t;
1055 type FEES_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.FEES_CUMULATIVE%TYPE
1056 index by pls_integer;
1057 v_FEES_CUMULATIVE FEES_CUMULATIVE_tab_t;
1058 type OTHER_CUMULATIVE_tab_t is table of lns_amort_scheds_gt.OTHER_CUMULATIVE%TYPE
1059 index by pls_integer;
1060 v_OTHER_CUMULATIVE OTHER_CUMULATIVE_tab_t;
1061 type RATE_ID_tab_t is table of lns_amort_scheds_gt.RATE_ID%TYPE
1062 index by pls_integer;
1063 v_RATE_ID RATE_ID_tab_t;
1064 type SOURCE_tab_t is table of lns_amort_scheds_gt.SOURCE%TYPE
1065 index by pls_integer;
1066 v_SOURCE SOURCE_tab_t;
1067 type GRAND_TOTAL_FLAG_tab_t is table of lns_amort_scheds_gt.GRAND_TOTAL_FLAG%TYPE
1068 index by pls_integer;
1069 v_GRAND_TOTAL_FLAG GRAND_TOTAL_FLAG_tab_t;
1070
1071 --Bug5262505 --karamach
1072 l_total_principal_balance NUMBER;
1073 CURSOR c_get_principal_balance(pLoanId Number) IS
1074 select pay.TOTAL_PRINCIPAL_BALANCE loan_amount
1075 from LNS_PAY_SUM_V pay
1076 where pay.loan_id = pLoanId;
1077
1078 CURSOR C_agreement (X_Loan_Id NUMBER
1079 ,X_Org_Id NUMBER
1080 ,pTotalPrincipalBalance Number) IS
1081 select sys_XMLGen(
1082 XMLELEMENT("ROW", XMLATTRIBUTES (1 AS "num"),
1083 XMLFOREST (
1084 lh.LOAN_ID,
1085 lh.loan_number
1086 ,lh.multiple_funding_flag
1087 ,xle.name lending_org_name
1088 ,hp.party_name borrower_name
1089 ,lh.REQUESTED_AMOUNT loan_requested_amount
1090 ,lh.LOAN_CURRENCY
1091 ,llklc.meaning LOAN_CLASS
1092 ,llt.loan_type_name LOAN_TYPE
1093 ,to_char(lh.LOAN_APPLICATION_DATE, 'MM/DD/YYYY') LOAN_APPLICATION_DATE
1094 ,to_char(lh.LOAN_START_DATE, 'MM/DD/YYYY') LOAN_START_DATE
1095 ,lh.loan_term || ' ' || llktt.meaning loan_term
1096 ,ratesch.current_interest_rate initial_interest_rate
1097 ,to_char(t.first_payment_date, 'MM/DD/YYYY') payment_start_date
1098 ,t.loan_payment_frequency payment_frequency_code
1099 ,llkfq.meaning payment_frequency
1100 , llkrt.meaning interest_type
1101 ,res.source_name loan_officer
1102 ,llkst.meaning loan_subtype
1103 ,nvl(lh.collateral_percent,0) COLLATERAL_PERCENTAGE
1104 ,llkp.meaning loan_purpose
1105 ,intrt.interest_rate_name index_name
1106 ,llkdc.meaning day_count_method
1107 ,llkic.meaning interest_calculation_method
1108 ,t.calculation_method int_calc_method_code
1109 ,t.day_count_method day_count_method_code
1110 ,t.delinquency_threshold_amount delinquency_overdue_amount
1111 ,nvl(t.reamortize_over_payment,'N') reamortize_over_payment
1112 ,t.ceiling_rate
1113 ,t.floor_rate
1114 ,to_char(t.lock_in_date,'MM/DD/YYYY') lock_in_date
1115 ,to_char(t.lock_to_date,'MM/DD/YYYY') lock_expiration_date
1116 ,llkfqf.meaning floating_frequency
1117 ,llkfqf.meaning open_payment_frequency
1118 ,to_char(t.open_first_payment_date,'MM/DD/YYYY') open_first_payment_date
1119 ,to_char(ldh.target_date,'MM/DD/YYYY') first_disbursement_date
1120 ,ldh.header_percent first_disbursement_percent
1121 ,ldh.header_amount first_disbursement_amount
1122 ,lh.open_to_term_flag
1123 ,decode(nvl(lh.open_to_term_flag,'N'),'Y','with','without') open_to_term_str
1124 ,nvl(lh.funded_amount,lh.requested_amount) * nvl(lh.collateral_percent,0)
1125 / 100 collateral_required
1126 ,lh.collateral_percent loan_to_value_ratio
1127 ,lh.loan_status
1128 --,pay.TOTAL_PRINCIPAL_BALANCE loan_amount --Bug5262505
1129 ,pTotalPrincipalBalance loan_amount
1130 ,lh.loan_description
1131 ,to_char(lh.loan_maturity_date, 'MM/DD/YYYY') loan_maturity_date
1132 ,lh.ORG_ID
1133 ,lh.LOAN_TYPE LOAN_TYPE_CODE
1134 ,lh.LOAN_CLASS_CODE
1135 ,LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate
1136 ,lh.LOAN_SUBTYPE
1137 ,lh.LAST_BILLED_DATE
1138 ,to_char(lh.LOAN_APPROVAL_DATE, 'MM/DD/YYYY') LOAN_APPROVAL_DATE
1139 ,loc.address1 || ' ' || loc.city || ' ' || loc.state || ' ' ||
1140 loc.postal_code || ' ' || terr.TERRITORY_SHORT_NAME primary_borrower_addr
1141 ,xle.ADDRESS_LINE_1 || ' ' || xle.ADDRESS_LINE_2 || ' ' || xle.ADDRESS_LINE_3 || ' ' ||
1142 xle.POSTAL_CODE || ' ' ||xle.COUNTRY Lender_address
1143 ,to_char(lh.loan_approval_date, 'MM/DD/YYYY') loan_approval_date
1144 ,lh.exchange_rate_type
1145 ,lh.exchange_rate
1146 ,to_char(lh.exchange_date, 'MM/DD/YYYY') exchange_date
1147 ,product.loan_product_name loan_product
1148 ),
1149 -- Loan_Participants
1150 (select
1151 XMLELEMENT("LOAN_PARTICIPANTS",
1152 XMLAGG(
1153 XMLELEMENT("LOAN_PARTICIPANTS_ROW", XMLATTRIBUTES (rownum AS "num"),
1154 XMLFOREST(
1155 party.party_name participant_name,
1156 party.JGZZ_FISCAL_CODE participant_tax_id,
1157 lkup.meaning participant_type,
1158 party.party_type participant_type_code,
1159 party.party_number participant_number,
1160 lnslkup.meaning participant_role,
1161 lp.LOAN_PARTICIPANT_TYPE participant_role_code,
1162 party.address1 || ' ' || party.address2 || ' '
1163 || party.address3 || ' ' || party.address4 || ' '
1164 || party.city || ' ' || party.state || ' ' ||
1165 party.postal_code || ' ' || party.county || ' ' ||
1166 ter.territory_short_name participant_address,
1167 party.email_address participant_email_address,
1168 party.URL participant_url,
1169 cp.raw_phone_number primary_phone_number,
1170 contact_person.party_name contact_person_name,
1171 contact_party.email_address contact_email_address,
1172 con_phone.raw_phone_number contact_phone_number
1173 )
1174 )
1175 )
1176 )
1177 FROM LNS_PARTICIPANTS lp, HZ_PARTIES party, AR_LOOKUPS lkup, LNS_LOOKUPS lnslkup,
1178 FND_TERRITORIES_TL ter, HZ_CONTACT_POINTS cp, HZ_PARTIES contact_person,
1179 HZ_PARTIES contact_party, HZ_CONTACT_POINTS con_phone
1180 WHERE party.party_id =lp.HZ_PARTY_ID
1181 AND party.party_type = lkup.lookup_code
1182 AND lkup.lookup_type = 'PARTY_TYPE'
1183 AND lp.LOAN_PARTICIPANT_TYPE = lnslkup.lookup_code
1184 AND lnslkup.lookup_type = 'LNS_PARTICIPANT_TYPE'
1185 AND party.country = ter.TERRITORY_CODE(+)
1186 AND ter.LANGUAGE(+) = userenv('LANG')
1187 AND party.party_id = cp.owner_table_id(+)
1188 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1189 AND cp.contact_point_type(+) = 'PHONE'
1190 AND cp.primary_flag(+) = 'Y'
1191 AND contact_person.party_id(+) =lp.contact_pers_party_id
1192 AND contact_party.party_id(+) =lp.contact_rel_party_id
1193 AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
1194 AND con_phone.owner_table_id(+) =lp.contact_rel_party_id
1195 AND con_phone.primary_flag(+) = 'Y'
1196 AND con_phone.status(+) = 'A'
1197 AND con_phone.contact_point_type(+) = 'PHONE'
1198 AND LOAN_ID = lh.loan_id
1199 ), -- end of LOAN_PARTICIPANTS
1200 -- DISB_RATE_SCHEDULE
1201 (select
1202 XMLELEMENT("DISB_RATE_SCHEDULE",
1203 XMLAGG(
1204 XMLELEMENT("DISB_RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
1205 XMLFOREST(
1206 BEGIN_INSTALLMENT_NUMBER installment_from
1207 ,END_INSTALLMENT_NUMBER installment_to
1208 ,to_char(index_date, 'MM/DD/YYYY') index_date
1209 ,current_interest_rate interest_rate
1210 )
1211 )
1212 )
1213 )
1214 from lns_rate_schedules rsh
1215 where rsh.term_id = t.term_id
1216 and rsh.end_date_active is null
1217 and nvl(rsh.phase,'TERM') = 'OPEN'
1218 ), -- end of DISB_RATE_SCHEDULE
1219 -- RATE_SCHEDULE
1220 (select
1221 XMLELEMENT("RATE_SCHEDULE",
1222 XMLAGG(
1223 XMLELEMENT("RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
1224 XMLFOREST(
1225 BEGIN_INSTALLMENT_NUMBER installment_from
1226 ,END_INSTALLMENT_NUMBER installment_to
1227 ,to_char(index_date, 'MM/DD/YYYY') index_date
1228 ,current_interest_rate interest_rate
1229 )
1230 )
1231 )
1232 )
1233 from lns_rate_schedules rsh
1234 where rsh.term_id = t.term_id
1235 and rsh.end_date_active is null
1236 and nvl(rsh.phase,'TERM') = 'TERM'
1237 ), -- end of RATE_SCHEDULE
1238 -- COLLATERAL
1239 (select
1240 XMLELEMENT("COLLATERAL",
1241 XMLAGG(
1242 XMLELEMENT("COLLATERAL_ROW", XMLATTRIBUTES (rownum AS "num"),
1243 XMLFOREST(
1244 assetassign.PLEDGED_AMOUNT,
1245 assetassign.START_DATE_ACTIVE,
1246 assetassign.END_DATE_ACTIVE,
1247 lkps1.meaning participant_role,
1248 party.party_name participant_name,
1249 lkps2.meaning asset_class,
1250 lkps3.meaning asset_type,
1251 asset.quantity || ' ' || lkps4.meaning asset_quantity,
1252 lkps5.meaning || ': ' || asset.reference_name reference,
1253 asset.appraiser_name,
1254 asset.next_evaluation_date,
1255 lkps6.meaning valuation_method,
1256 asset.lien_amount,
1257 asset.description, asset.currency_code,
1258 asset.valuation, asset.start_date_active acquired_date
1259 )
1260 )
1261 )
1262 )
1263 FROM LNS_ASSET_ASSIGNMENTS assetassign,
1264 LNS_ASSETS asset,
1265 LNS_LOOKUPS lkps1,
1266 LNS_LOOKUPS lkps2,
1267 LNS_LOOKUPS lkps3,
1268 LNS_LOOKUPS lkps4,
1269 LNS_LOOKUPS lkps5,
1270 LNS_LOOKUPS lkps6,
1271 HZ_PARTIES party,
1272 LNS_PARTICIPANTS par
1273 WHERE assetassign.asset_id = asset.asset_id and
1274 asset.asset_owner_id = par.hz_party_id and
1275 party.party_id = par.hz_party_id and
1276 assetassign.loan_id = par.loan_id and
1277 par.loan_participant_type = lkps1.lookup_code and
1278 lkps1.lookup_type = 'LNS_PARTICIPANT_TYPE' and
1279 asset.asset_class_code = lkps2.lookup_code and
1280 lkps2.lookup_type = 'ASSET_CLASSES' and
1281 asset.asset_type_code = lkps3.lookup_code and
1282 lkps3.lookup_type = asset.asset_class_code and
1283 asset.uom_code = lkps4.lookup_code and
1284 lkps4.lookup_type = 'ASSET_QNT_'||asset.asset_class_code and
1285 asset.reference_type = lkps5.lookup_code and
1286 lkps5.lookup_type = 'ASSET_REF_'||asset.asset_class_code and
1287 asset.valuation_method_code = lkps6.lookup_code and
1288 lkps6.lookup_type = 'VALUATION_METHOD' and
1289 assetassign.loan_id = lh.loan_id
1290 ), -- end of COLLATERAL
1291 -- FEES
1292 (select
1293 XMLELEMENT("FEES",
1294 XMLAGG(
1295 XMLELEMENT("FEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1296 XMLFOREST(
1297 lfa.FEE, lfa.FEE_TYPE, lfa.FEE_BASIS, lfa.RATE_TYPE,
1298 lfa.BILLING_OPTION BILLING_OPTION_CODE,
1299 llkbo.meaning BILLING_OPTION,
1300 lf.FEE_NAME,
1301 lf.FEE_CATEGORY FEE_CATEGORY_CODE,
1302 llkfc.meaning FEE_CATEGORY,
1303 lf.rate_type method_code,
1304 llkrt.meaning Fee_method,
1305 fl.meaning update_allowed,
1306 lf.FEE_DESCRIPTION,
1307 decode(lf.RATE_TYPE, 'FIXED',
1308 to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
1309 (nvl(lf.CURRENCY_CODE,'USD'),25))
1310 , to_char(lf.FEE) || '%' ||
1311 decode(lf.FEE_BASIS, null, '', ', ')
1312 || lkps1.meaning) FEE_VAR_AMOUNT_PERCENT
1313 )
1314 )
1315 )
1316 )
1317 FROM LNS_FEE_ASSIGNMENTS lfa, LNS_FEES_ALL lf, LNS_LOOKUPS lkps1,
1318 LNS_LOOKUPS llkbo,
1319 LNS_LOOKUPS llkrt,
1320 LNS_LOOKUPS llkfc,
1321 fnd_lookups fl
1322 WHERE lfa.FEE_ID = lf.FEE_ID AND
1323 lfa.loan_id = lh.loan_id and
1324 llkfc.lookup_type = 'FEE_CATEGORY' and
1325 llkfc.lookup_code = lf.fee_category and
1326 lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
1327 llkbo.lookup_code(+) = lf.BILLING_OPTION AND
1328 llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
1329 llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
1330 fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
1331 ), -- end of FEES
1332 -- CONDITIONS
1333 (select
1334 XMLELEMENT("CONDITIONS",
1335 XMLAGG(
1336 XMLELEMENT("CONDITIONS_ROW", XMLATTRIBUTES (rownum AS "num"),
1337 XMLFOREST(
1338 lc.CONDITION_NAME,
1339 lca.CONDITION_DESCRIPTION,
1340 lc.CONDITION_TYPE CONDITION_TYPE_CODE,
1341 lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
1342 )
1343 )
1344 )
1345 )
1346 FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
1347 LNS_LOOKUPS lkps1 , fnd_lookups fl
1348 WHERE lca.CONDITION_ID = lc.CONDITION_ID
1349 AND lkps1.lookup_type = 'CONDITION_TYPE'
1350 AND lkps1.lookup_code = lc.CONDITION_TYPE
1351 AND fl.lookup_type = 'YES_NO'
1352 AND fl.lookup_code = lca.MANDATORY_FLAG
1353 AND nvl(lca.start_date_active, sysdate) <= sysdate
1354 AND nvl(lca.end_date_active, sysdate) >= sysdate
1355 AND lca.LOAN_ID = lh.loan_id
1356 ), -- end of CONDITIONS
1357 -- ORIGINAL_RECEIVABLES
1358 (select
1359 XMLELEMENT("ORIGINAL_RECEIVABLES",
1360 XMLAGG(
1361 XMLELEMENT("ORIGINAL_RECEIVABLES_ROW", XMLATTRIBUTES (rownum AS "num"),
1362 XMLFOREST(
1363 lll.REFERENCE_NUMBER original_rec_number,
1364 lll.REFERENCE_AMOUNT original_rec_balance,
1365 lll.REQUESTED_AMOUNT loan_requested_amount,
1366 lll.REFERENCE_NUMBER original_rec_desc,
1367 to_char(cust_trx.term_due_date,'MM/DD/YYYY')
1368 INVOICE_DUE_DATE,
1369 trx_type.name || ' - ' || trx_type_lkup.meaning
1370 inv_trx_type
1371 )
1372 )
1373 )
1374 )
1375 from LNS_LOAN_LINES lll,
1376 RA_CUSTOMER_TRX_ALL cust_trx,
1377 RA_CUST_TRX_TYPES_ALL trx_type,
1378 ar_lookups trx_type_lkup
1379 where lll.loan_id = lh.LOAN_ID and
1380 lll.end_date is null and
1381 cust_trx.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
1382 trx_type.org_id = lh.org_id and
1383 trx_type_lkup.lookup_type = 'INV/CM' and
1384 trx_type_lkup.lookup_code = trx_type.type and
1385 cust_trx.customer_trx_id = lll.reference_id
1386 ), -- end of ORIGINAL_RECEIVABLES
1387 -- DISBURSEMENTS
1388 (select
1389 XMLELEMENT("DISBURSEMENTS",
1390 XMLAGG(
1391 XMLELEMENT("DISBURSEMENTS_ROW", XMLATTRIBUTES (rownum AS "num"),
1392 XMLFOREST(
1393 disbursement_number
1394 ,llkac.meaning disbursement_activity
1395 ,to_char(ldih.target_date,'MM/DD/YYYY') target_date
1396 ,ldih.header_percent disbursement_percent
1397 ,ldih.header_amount amount_of_disbursement
1398 ),
1399 -- PAYEES
1400 (select
1401 XMLELEMENT("PAYEES",
1402 XMLAGG(
1403 XMLELEMENT("PAYEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1404 XMLFOREST(
1405 party.party_name payee_name
1406 ,party.ADDRESS1 || ' ' || party.ADDRESS2 || ' ' ||
1407 party.ADDRESS3 || ' ' || party.STATE || ' ' ||
1408 party.POSTAL_CODE || ' ' ||party.COUNTRY payee_address
1409 ,ibypm.payment_method_name
1410 ,line_percent payee_percent
1411 ,line_amount payee_amount
1412 )
1413 )
1414 )
1415 )
1416 from lns_disb_lines ldl
1417 ,hz_parties party
1418 ,IBY_PAYMENT_METHODS_TL ibypm
1419 where ldl.disb_header_id = ldih.disb_header_id
1420 and party.party_id = ldl.payee_party_id and
1421 ibypm.payment_method_code (+) = ldl.payment_method_code and
1422 ibypm.LANGUAGE (+) = userenv('LANG')
1423 ),-- end of PAYEES
1424 -- DISBFEES
1425 (select
1426 XMLELEMENT("DISBFEES",
1427 XMLAGG(
1428 XMLELEMENT("DISBFEES_ROW", XMLATTRIBUTES (rownum AS "num"),
1429 XMLFOREST(
1430 lfa.FEE, lfa.FEE_TYPE,
1431 lfa.FEE_BASIS,
1432 lfa.RATE_TYPE,
1433 lfa.BILLING_OPTION BILLING_OPTION_CODE,
1434 llkbo.meaning BILLING_OPTION,
1435 lf.FEE_NAME,
1436 lf.FEE_CATEGORY FEE_CATEGORY_CODE,
1437 llkfc.meaning FEE_CATEGORY,
1438 lf.rate_type method_code,
1439 llkrt.meaning Fee_method,
1440 fl.meaning update_allowed,
1441 lf.FEE_DESCRIPTION,
1442 decode(lf.RATE_TYPE, 'FIXED', to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
1443 (nvl(lf.CURRENCY_CODE,'USD'),25)), to_char(lf.FEE) || '%' ||
1444 decode(lf.FEE_BASIS, null, '', ', ') || lkps1.meaning)
1445 FEE_VAR_AMOUNT_PERCENT
1446 )
1447 )
1448 )
1449 )
1450 FROM LNS_FEE_ASSIGNMENTS lfa,
1451 LNS_FEES_ALL lf,
1452 LNS_LOOKUPS lkps1,
1453 LNS_LOOKUPS llkbo,
1454 LNS_LOOKUPS llkrt,
1455 LNS_LOOKUPS llkfc,
1456 fnd_lookups fl
1457 WHERE lfa.FEE_ID = lf.FEE_ID AND
1458 lfa.disb_header_id = ldih.disb_header_id and
1459 llkfc.lookup_type = 'FEE_CATEGORY' and
1460 llkfc.lookup_code = lf.fee_category and
1461 lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
1462 llkbo.lookup_code(+) = lf.BILLING_OPTION AND
1463 llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
1464 llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
1465 fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
1466 ), -- end of DISBFEES
1467 -- DISBCOND
1468 (select
1469 XMLELEMENT("DISBCOND",
1470 XMLAGG(
1471 XMLELEMENT("DISBCOND_ROW", XMLATTRIBUTES (rownum AS "num"),
1472 XMLFOREST(
1473 lc.CONDITION_NAME,
1474 lca.CONDITION_DESCRIPTION,
1475 lc.CONDITION_TYPE CONDITION_TYPE_CODE,
1476 lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
1477 )
1478 )
1479 )
1480 )
1481 FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
1482 LNS_LOOKUPS lkps1 , fnd_lookups fl
1483 WHERE lca.CONDITION_ID = lc.CONDITION_ID
1484 AND lkps1.lookup_type = 'CONDITION_TYPE'
1485 AND lkps1.lookup_code = lc.CONDITION_TYPE
1486 AND fl.lookup_type = 'YES_NO'
1487 AND fl.lookup_code = lca.MANDATORY_FLAG
1488 AND nvl(lca.start_date_active, sysdate) <= sysdate
1489 AND nvl(lca.end_date_active, sysdate) >= sysdate
1490 AND lca.disb_header_id = ldih.disb_header_id
1491 ) -- end of DISBCOND
1492 )
1493 )
1494 )
1495 FROM lns_disb_headers ldih
1496 ,lns_lookups llkac
1497 WHERE ldih.loan_id = lh.loan_id and
1498 llkac.lookup_code = ldih.activity_code
1499 ), -- end of DISBURSEMENTS
1500 -- AMORTIZATION
1501 (select
1502 XMLELEMENT("AMORTIZATION",
1503 XMLAGG(
1504 XMLELEMENT("AMORTIZATION_ROW", XMLATTRIBUTES (rownum AS "num"),
1505 XMLFOREST(
1506 INSTALLMENT_NUMBER PAYMENT_NUMBER
1507 ,to_char(DUE_DATE, 'MM/DD/YYYY') DUE_DATE
1508 ,PRINCIPAL_AMOUNT PAYMENT_PRINCIPAL
1509 ,INTEREST_AMOUNT PAYMENT_INTEREST
1510 ,FEE_AMOUNT PAYMENT_FEES
1511 ,(PRINCIPAL_AMOUNT+INTEREST_AMOUNT+FEE_AMOUNT) PAYMENT_TOTAL
1512 ,OTHER_AMOUNT
1513 ,BEGIN_BALANCE BEGINNING_BALANCE
1514 ,END_BALANCE ENDING_BALANCE
1515 ,INTEREST_CUMULATIVE
1516 ,PRINCIPAL_CUMULATIVE
1517 ,FEES_CUMULATIVE
1518 ,OTHER_CUMULATIVE
1519 ,SOURCE
1520 )
1521 )
1522 )
1523 )
1524 FROM lns_amort_scheds_gt agt
1525 where agt.loan_id = lh.loan_id
1526 ) -- end of AMORTIZATION
1527 ), XMLFormat.createformat('ROWSET')).getClobVal()
1528 from
1529 lns_loan_headers_all lh,
1530 hz_parties hp,
1531 lns_terms t,
1532 lns_int_rate_headers intrt,
1533 lns_rate_schedules ratesch,
1534 hz_locations loc,
1535 fnd_territories_tl terr,
1536 hz_party_sites site,
1537 hz_cust_acct_sites_all acct_site,
1538 --LNS_PAY_SUM_V pay, --Bug5262505
1539 xle_firstparty_information_v xle,
1540 lns_lookups llkrt,
1541 lns_lookups llktt,
1542 lns_loan_types_vl llt,
1543 lns_lookups llklc,
1544 lns_lookups llkp,
1545 lns_lookups llkst,
1546 lns_lookups llkdc,
1547 lns_lookups llkfq,
1548 lns_lookups llkfqf,
1549 lns_lookups llkic,
1550 jtf_rs_resource_extns res,
1551 lns_disb_headers ldh,
1552 lns_loan_products_all_vl product
1553 where
1554 lh.primary_borrower_id = hp.party_id and
1555 --lh.loan_id = pay.loan_id and --Bug5262505
1556 lh.loan_id = t.loan_id and
1557 ldh.loan_id(+) = lh.loan_id and
1558 ldh.disbursement_number(+) = 1 and
1559 product.loan_product_id(+) = lh.product_id and
1560 t.term_id = ratesch.term_id and
1561 ratesch.begin_installment_number = 1 and
1562 ratesch.end_date_active is null and
1563 ((lh.multiple_funding_flag = 'Y' and lh.open_to_term_flag = 'N' and
1564 ratesch.phase = 'OPEN') OR ( ratesch.phase = 'TERM')) and
1565 intrt.interest_rate_id = t.index_rate_id and
1566 xle.legal_entity_id = lh.legal_entity_id and
1567 llktt.lookup_code = lh.loan_term_period and
1568 llktt.lookup_type = 'PERIOD' and
1569 llkrt.lookup_code = t.rate_type and
1570 llkrt.lookup_type = 'RATE_TYPE' and
1571 llkic.lookup_code = t.calculation_method and
1572 llkic.lookup_type = 'INTEREST_CALCULATION_METHOD' and
1573 llklc.lookup_code = lh.loan_class_code and
1574 llklc.lookup_type = 'LOAN_CLASS' and
1575 llt.loan_type_id = lh.loan_type_id and
1576 llkdc.lookup_code = t.day_count_method and
1577 llkdc.lookup_type = 'DAY_COUNT_METHOD' and
1578 llkp.lookup_code (+) = lh.loan_purpose_code and
1579 llkp.lookup_type (+) = 'LOAN_PURPOSE' and
1580 llkst.lookup_code (+) = lh.loan_subtype and
1581 llkst.lookup_type (+) = 'LOAN_SUBTYPE' and
1582 lh.loan_assigned_to = res.resource_id and
1583 llkfq.lookup_code (+) = t.loan_payment_frequency and
1584 llkfq.lookup_type (+) = 'FREQUENCY' and
1585 llkfqf.lookup_code (+) = t.open_payment_frequency and
1586 llkfqf.lookup_type (+) = 'FREQUENCY' and
1587 res.category = 'EMPLOYEE' and
1588 lh.loan_status <> 'DELETED' and
1589 acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and
1590 acct_site.org_id = lh.org_id and
1591 site.party_site_id = acct_site.party_site_id and
1592 site.location_id = loc.location_id and
1593 loc.country = terr.TERRITORY_CODE and
1594 terr.language = userenv('LANG') and
1595 lh.loan_id = X_Loan_Id and
1596 lh.org_id = X_Org_Id;
1597 BEGIN
1598 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1599 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
1600 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'org_id = ' || l_org_id);
1601 lns_financials.runAmortization(p_api_version => 1.0
1602 ,p_init_msg_list => 'T'
1603 ,p_commit => 'F'
1604 ,p_loan_ID => p_loan_id
1605 ,p_based_on_terms => 'ORIGINATION'
1606 ,x_amort_tbl => l_amort_tbl
1607 ,x_return_status => l_return_Status
1608 ,x_msg_count => l_msg_count
1609 ,x_msg_data => l_msg_data);
1610 FOR j in 1..l_amort_tbl.count
1611 LOOP
1612 v_INSTALLMENT_NUMBER(j) := l_amort_tbl(j).INSTALLMENT_NUMBER;
1613 v_DUE_DATE(j) := l_amort_tbl(j).DUE_DATE;
1614 v_PRINCIPAL_AMOUNT(j) := l_amort_tbl(j).PRINCIPAL_AMOUNT;
1615 v_INTEREST_AMOUNT(j) := l_amort_tbl(j).INTEREST_AMOUNT;
1616 v_FEE_AMOUNT(j) := l_amort_tbl(j).FEE_AMOUNT;
1617 v_OTHER_AMOUNT(j) := l_amort_tbl(j).OTHER_AMOUNT;
1618 v_BEGIN_BALANCE(j) := l_amort_tbl(j).BEGIN_BALANCE;
1619 v_END_BALANCE(j) := l_amort_tbl(j).END_BALANCE;
1620 v_TOTAL(j) := l_amort_tbl(j).TOTAL;
1621 v_INTEREST_CUMULATIVE(j) := l_amort_tbl(j).INTEREST_CUMULATIVE;
1622 v_PRINCIPAL_CUMULATIVE(j) := l_amort_tbl(j).PRINCIPAL_CUMULATIVE;
1623 v_FEES_CUMULATIVE(j) := l_amort_tbl(j).FEES_CUMULATIVE;
1624 v_OTHER_CUMULATIVE(j) := l_amort_tbl(j).OTHER_CUMULATIVE;
1625 v_RATE_ID(j) := l_amort_tbl(j).RATE_ID;
1626 v_SOURCE(j) := l_amort_tbl(j).SOURCE;
1627 v_GRAND_TOTAL_FLAG(j) := l_amort_tbl(j).GRAND_TOTAL_FLAG;
1628 END LOOP;
1629 FORALL j in v_INSTALLMENT_NUMBER.first..v_INSTALLMENT_NUMBER.last
1630 insert into lns_amort_scheds_gt
1631 (
1632 LOAN_ID
1633 ,INSTALLMENT_NUMBER
1634 ,DUE_DATE
1635 ,PRINCIPAL_AMOUNT
1636 ,INTEREST_AMOUNT
1637 ,FEE_AMOUNT
1638 ,OTHER_AMOUNT
1639 ,BEGIN_BALANCE
1640 ,END_BALANCE
1641 ,TOTAL
1642 ,INTEREST_CUMULATIVE
1643 ,PRINCIPAL_CUMULATIVE
1644 ,FEES_CUMULATIVE
1645 ,OTHER_CUMULATIVE
1646 ,RATE_ID
1647 ,SOURCE
1648 ,GRAND_TOTAL_FLAG
1649 )
1650 VALUES (
1651 p_loan_id
1652 ,v_INSTALLMENT_NUMBER(j)
1653 ,v_DUE_DATE(j)
1654 ,v_PRINCIPAL_AMOUNT(j)
1655 ,v_INTEREST_AMOUNT(j)
1656 ,v_FEE_AMOUNT(j)
1657 ,v_OTHER_AMOUNT(j)
1658 ,v_BEGIN_BALANCE(j)
1659 ,v_END_BALANCE(j)
1660 ,v_TOTAL(j)
1661 ,v_INTEREST_CUMULATIVE(j)
1662 ,v_PRINCIPAL_CUMULATIVE(j)
1663 ,v_FEES_CUMULATIVE(j)
1664 ,v_OTHER_CUMULATIVE(j)
1665 ,v_RATE_ID(j)
1666 ,v_SOURCE(j)
1667 ,v_GRAND_TOTAL_FLAG(j)
1668 );
1669 -- Start Getting Parameter Display Values
1670 SELECT hou.name, gsb.currency_code
1671 INTO l_org_name,
1672 l_sob_currency_code
1673 FROM hr_operating_units hou,
1674 gl_sets_of_books gsb
1675 WHERE hou.organization_id = l_org_id
1676 AND gsb.set_of_books_id = hou.set_of_books_id;
1677
1678 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Opening c_get_principal_balance');
1679 open c_get_principal_balance(p_loan_id);
1680 fetch c_get_principal_balance into l_total_principal_balance;
1681 close c_get_principal_balance;
1682 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Opening C_agreement');
1683 OPEN C_agreement(X_Loan_Id => p_loan_id,
1684 X_Org_Id => l_org_id,
1685 pTotalPrincipalBalance => l_total_principal_balance);
1686 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Fetching C_agreement');
1687 FETCH C_agreement INTO l_result;
1688 IF C_agreement%ROWCOUNT <> 0
1689 THEN
1690 l_rows_processed := 1;
1691 END IF;
1692 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'C_agreement%ROWCOUNT ' || C_agreement%ROWCOUNT);
1693 CLOSE C_agreement;
1694 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_rows_processed ' || l_rows_processed);
1695 -- We are adding the LNSAGREEMENT and PARAMETERS TAGs so we have
1696 -- to offset the first line.
1697 IF l_rows_processed <> 0 THEN
1698 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
1699 ELSE
1700 l_resultOffset := 0;
1701 END IF;
1702 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_resultOffset ' || l_resultOffset);
1703
1704 l_new_line := '
1705 ';
1706 /* Prepare the tag for the report heading */
1707 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
1708 l_xml_header := l_xml_header ||l_new_line||'<LNSAGREEMENT>';
1709 l_xml_header := l_xml_header ||l_new_line||' <PARAMETERS>';
1710 l_xml_header := l_xml_header ||l_new_line||' <ORG_NAME>'||l_org_name||'</ORG_NAME>';
1711 l_xml_header := l_xml_header ||l_new_line||' <SET_OF_BOOKS_CURRENCY_CODE>'||l_sob_currency_code||'</SET_OF_BOOKS_CURRENCY_CODE>';
1712 l_xml_header := l_xml_header ||l_new_line||' <LOAN_ID>' ||p_loan_id ||'</LOAN_ID>';
1713 l_xml_header := l_xml_header ||l_new_line||' </PARAMETERS>';
1714 l_close_tag := l_new_line||'</LNSAGREEMENT>'||l_new_line;
1715 l_xml_header_length := length(l_xml_header);
1716 IF l_rows_processed <> 0 THEN
1717 LogMessage(FND_LOG.LEVEL_PROCEDURE, ' l_xml_header_length ' || l_xml_header_length);
1718 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1719 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1720 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1721
1722 LogMessage(FND_LOG.LEVEL_PROCEDURE, ' before dbms_lob.copy ' );
1723 dbms_lob.copy(tempResult,l_result
1724 ,dbms_lob.getlength(l_result)-l_resultOffset
1725 ,l_xml_header_length,l_resultOffset);
1726 ELSE
1727 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1728 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1729 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1730 END IF;
1731
1732 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1733 p_AgreementXML := tempResult;
1734 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 RAISE;
1738 END GEN_AGREEMENT_XML;
1739 PROCEDURE CLOB_TO_FILE( p_clob IN CLOB )
1740 IS
1741 l_clob_size NUMBER;
1742 l_offset NUMBER;
1743 l_res_offset NUMBER;
1744 l_chunk_size INTEGER;
1745 l_chunk_limit_size INTEGER;
1746 l_chunk VARCHAR2(32767);
1747 l_new_line VARCHAR2(1);
1748 l_api_name CONSTANT VARCHAR2(30) := 'CLOB_TO_FILE';
1749
1750 BEGIN
1751
1752 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1753 -- get length of internal lob and open the dest. file.
1754 l_clob_size := dbms_lob.getlength(p_clob);
1755
1756 IF (l_clob_size = 0) THEN
1757 LogMessage(FND_LOG.LEVEL_PROCEDURE,'CLOB is empty');
1758 RETURN;
1759 END IF;
1760
1761 l_offset := 1;
1762 l_chunk_size := 3000;
1763 l_chunk_limit_size := l_chunk_size;
1764
1765 -- To get the maximum value which should be multiple of l_chunk_size and also less than 32767
1766 WHILE(l_chunk_limit_size < 32767)
1767 LOOP
1768 l_chunk_limit_size := l_chunk_limit_size + l_chunk_size;
1769 END LOOP;
1770 l_chunk_limit_size := l_chunk_limit_size - l_chunk_size;
1771
1772 LogMessage(FND_LOG.LEVEL_PROCEDURE,'chunk limit size is '||l_chunk_limit_size);
1773
1774 l_new_line := '
1775 ';
1776
1777 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Unloading... ' || l_clob_size);
1778
1779 WHILE (l_clob_size > 0) LOOP
1780
1781 -- LogMessage(FND_LOG.LEVEL_PROCEDURE,'Off Set: ' || l_offset);
1782
1783 l_chunk := dbms_lob.substr (p_clob, l_chunk_size, l_offset);
1784
1785 --LogMessage(FND_LOG.LEVEL_PROCEDURE,'Off Set: ' || l_offset);
1786 --LogMessage(FND_LOG.LEVEL_PROCEDURE,'l_chunk ' || l_chunk);
1787
1788 -- There should be one new line character(chr(10)) for every 32k when
1789 -- writing into the file using 'utl_file' package.
1790
1791 if((mod(l_offset,l_chunk_limit_size) = 1) AND (l_offset <> 1)) then
1792 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Reached into the Limit Size');
1793
1794 -- Inserting new line character(chr(10)) after the first appearing
1795 -- closing XML tag
1796 l_res_offset := instr(l_chunk,'>');
1797
1798 LogMessage(FND_LOG.LEVEL_PROCEDURE,'result Offset is '||l_res_offset);
1799
1800 if(l_res_offset > 0) then
1801 l_chunk := substr(l_chunk,1,l_res_offset)||l_new_line||substr(l_chunk,(l_res_offset+1),length(l_chunk));
1802 end if;
1803 end if;
1804
1805 fnd_file.put(
1806 which => fnd_file.output,
1807 buff => l_chunk);
1808
1809 l_clob_size := l_clob_size - l_chunk_size;
1810 l_offset := l_offset + l_chunk_size;
1811
1812 END LOOP;
1813
1814 fnd_file.new_line(fnd_file.output,1);
1815 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 LogMessage(FND_LOG.LEVEL_PROCEDURE,'EXCEPTION: OTHERS clob_to_file');
1820 LogMessage(FND_LOG.LEVEL_PROCEDURE,sqlcode);
1821 LogMessage(FND_LOG.LEVEL_PROCEDURE,sqlerrm);
1822 RAISE;
1823
1824 END;
1825
1826 -- Bug#6169438, Added new parameter P_GENERATE_AGREEMENT which forces the API to regenerate the loan
1827 -- agreement ,if the value is 'Y', and also create the history record in loans schema.
1828
1829 PROCEDURE PROCESS_AGREEMENT_REPORT(ERRBUF OUT NOCOPY VARCHAR2
1830 ,RETCODE OUT NOCOPY VARCHAR2
1831 ,LOAN_ID IN NUMBER
1832 ,P_GENERATE_AGREEMENT IN VARCHAR2 DEFAULT 'N'
1833 ) IS
1834 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_AGREEMENT_REPORT';
1835 l_api_version CONSTANT NUMBER := 1.0;
1836 l_result_xml CLOB;
1837 l_loan_id LNS_LOAN_HEADERS_ALL.LOAN_ID%TYPE;
1838 l_loan_status LNS_LOAN_HEADERS_ALL.LOAN_STATUS%TYPE;
1839 l_document_id LNS_LOAN_DOCUMENTS.DOCUMENT_ID%TYPE;
1840 l_version LNS_LOAN_DOCUMENTS.VERSION%TYPE := -1;
1841 l_agreement_exist_flag VARCHAR2(1) := 'Y';
1842
1843 cursor csr_loan_agreement IS
1844 select document_id, document_xml, version
1845 from lns_loan_documents ld
1846 where ld.source_table = 'LNS_LOAN_HEADERS_ALL'
1847 and ld.source_id = l_loan_id
1848 and ld.document_type = 'LOAN_AGREEMENT'
1849 ORDER BY ld.version DESC;
1850 cursor csr_loan_status IS
1851 select loan_status
1852 from lns_loan_headers_all lh
1853 where lh.loan_id = l_loan_id;
1854 BEGIN
1855 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1856 l_loan_id := loan_id;
1857 OPEN csr_loan_agreement;
1858 FETCH csr_loan_agreement
1859 INTO l_document_id, l_result_xml, l_version;
1860
1861 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_GENERATE_AGREEMENT VALUE IS '||P_GENERATE_AGREEMENT);
1862 IF csr_loan_agreement%NOTFOUND THEN
1863 l_agreement_exist_flag := 'N';
1864 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Agreement not found in lns_loan_documents');
1865 END IF;
1866
1867
1868 IF ((P_GENERATE_AGREEMENT = 'Y') OR (l_agreement_exist_flag = 'N')) THEN
1869 OPEN csr_loan_status;
1870 FETCH csr_loan_status into l_loan_status;
1871 CLOSE csr_loan_status;
1872 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_loan_id is ' || l_loan_id ||
1873 'l_loan_status is ' || l_loan_status);
1874 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
1875 p_loan_id => loan_id);
1876 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'After call to GEN_AGREEMENT_XML ');
1877 IF l_loan_status NOT IN ('INCOMPLETE','PENDING') THEN
1878 IF (l_agreement_exist_flag = 'N') THEN
1879 l_version := 1;
1880 ELSE
1881 l_version := l_version + 1;
1882 /*
1883 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Creating History for the Loan Agreement - PRE API');
1884 --Call to record history
1885 LNS_LOAN_HISTORY_PUB.log_record_pre(
1886 p_id => l_document_id,
1887 p_primary_key_name => 'DOCUMENT_ID',
1888 p_table_name => 'LNS_LOAN_DOCUMENTS'
1889 );
1890 */
1891 END IF;
1892
1893 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'l_version is ' || l_version);
1894
1895 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Inserting agreement in lns_loan_documents');
1896 LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
1897 ,P_SOURCE_ID => loan_id
1898 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
1899 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
1900 ,P_VERSION => l_version
1901 ,P_DOCUMENT_XML => l_result_xml
1902 ,P_OBJECT_VERSION_NUMBER => 1);
1903 /*
1904 IF (l_agreement_exist_flag = 'Y') THEN
1905 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Creating History for the Loan Agreement - POST API');
1906 LNS_LOAN_HISTORY_PUB.log_record_post(
1907 p_id => l_document_id,
1908 p_primary_key_name => 'DOCUMENT_ID',
1909 p_table_name => 'LNS_LOAN_DOCUMENTS',
1910 p_loan_id => loan_id
1911 );
1912 END IF;
1913 */
1914 END IF;
1915 END IF;
1916 CLOSE csr_loan_agreement;
1917 clob_to_file(l_result_xml);
1918 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1922 || ' -');
1923 RAISE;
1924 END PROCESS_AGREEMENT_REPORT;
1925 PROCEDURE STORE_LOAN_AGREEMENT (P_LOAN_ID IN NUMBER) IS
1926 l_api_name CONSTANT VARCHAR2(30) := 'STORE_LOAN_AGREEMENT_REPORT';
1927 l_api_version CONSTANT NUMBER := 1.0;
1928 l_result_xml CLOB;
1929 l_document_id LNS_LOAN_DOCUMENTS.DOCUMENT_ID%TYPE;
1930 cursor csr_loan_agreement IS
1931 select document_xml
1932 from lns_loan_documents
1933 where source_table = 'LNS_LOAN_HEADERS_ALL'
1934 and source_id = p_loan_id
1935 and version = 1
1936 and document_type = 'LOAN_AGREEMENT';
1937 BEGIN
1938 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1939 OPEN csr_loan_agreement;
1940 FETCH csr_loan_agreement
1941 INTO l_result_xml;
1942 IF csr_loan_agreement%NOTFOUND THEN
1943 GEN_AGREEMENT_XML(p_AgreementXML=> l_result_xml,
1944 p_loan_id => p_loan_id);
1945 LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
1946 ,P_SOURCE_ID => p_loan_id
1947 ,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
1948 ,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
1949 ,P_VERSION => 1
1950 ,P_DOCUMENT_XML => l_result_xml
1951 ,P_OBJECT_VERSION_NUMBER => 1);
1952
1953 END IF;
1954 CLOSE csr_loan_agreement;
1955 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name
1959 || ' -');
1960 RAISE;
1961 END STORE_LOAN_AGREEMENT;
1962 BEGIN
1963 G_LOG_ENABLED := 'N';
1964 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1965
1966 /* getting msg logging info */
1967 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1968 if (G_LOG_ENABLED = 'N') then
1969 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1970 else
1971 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1972 end if;
1973
1974 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1975 END LNS_REP_UTILS;