[Home] [Help]
PACKAGE BODY: APPS.LNS_BORROWERS_SUMMARY_PUB
Source
1 PACKAGE BODY LNS_BORROWERS_SUMMARY_PUB as
2 /* $Header: LNS_BORR_SUM_B.pls 120.7 2006/07/11 23:09:48 karamach noship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_BORROWERS_SUMMARY_PUB';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11
12
13 /*========================================================================
14 | PRIVATE PROCEDURE LogMessage
15 |
16 | DESCRIPTION
17 | This procedure logs debug messages to db and to CM log
18 |
19 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
20 | LNS_BORR_SUM_CONCUR
21 |
22 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
23 | None
24 |
25 | PARAMETERS
26 | p_msg_level IN Debug msg level
27 | p_msg IN Debug msg itself
28 |
29 | KNOWN ISSUES
30 | None
31 |
32 | NOTES
33 | Any interesting aspect of the code in the package body which needs
34 | to be stated.
35 |
36 | MODIFICATION HISTORY
37 | Date Author Description of Changes
38 | 01-01-2004 scherkas Created
39 |
40 *=======================================================================*/
41 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
42 IS
43 BEGIN
44 if (p_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
45
46 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
47 if FND_GLOBAL.Conc_Request_Id is not null then
48 fnd_file.put_line(FND_FILE.LOG, p_msg);
49 end if;
50
51 end if;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
56 END;
57
58
59
60 /*========================================================================
61 | PRIVATE PROCEDURE init
62 |
63 | DESCRIPTION
64 | This procedure inits data needed for processing
65 |
66 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
67 | GENERATE_BORROWER_SUMMARY
68 |
69 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
70 | LogMessage
71 |
72 | PARAMETERS
73 | None
74 |
75 | KNOWN ISSUES
76 | None
77 |
78 | NOTES
79 | Any interesting aspect of the code in the package body which needs
80 | to be stated.
81 |
82 | MODIFICATION HISTORY
83 | Date Author Description of Changes
84 | 04-14-2004 scherkas Created
85 |
86 *=======================================================================*/
87 Procedure init
88 IS
89 BEGIN
90
91 /* getting msg logging info */
92 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
93 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
94 /*
95 if (G_LOG_ENABLED = 'N') then
96 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
97 else
98 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
99 end if;
100 */
101 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
102 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
103
104 END;
105
106
107
108 /*========================================================================
109 | PRIVATE PROCEDURE GET_BORROWER_OVERVIEW
110 |
111 | DESCRIPTION
112 | This procedure generates borrower overview part.
113 |
114 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
115 | GEN_SINGLE_BORROWER_SUMMARY
116 |
117 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
118 | LogMessage
119 |
120 | PARAMETERS
121 | P_BORROWER_TBL IN OUT NOCOPY borrower table
122 |
123 | KNOWN ISSUES
124 | None
125 |
126 | NOTES
127 | Any interesting aspect of the code in the package body which needs
128 | to be stated.
129 |
130 | MODIFICATION HISTORY
131 | Date Author Description of Changes
132 | 04-14-2004 scherkas Created
133 |
134 *=======================================================================*/
135 PROCEDURE GET_BORROWER_OVERVIEW(P_BORROWER_TBL IN OUT NOCOPY LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL)
136 IS
137
138 /*-----------------------------------------------------------------------+
139 | Local Variable Declarations and initializations |
140 +-----------------------------------------------------------------------*/
141
142 l_api_name CONSTANT VARCHAR2(30) := 'GET_BORROWER_OVERVIEW';
143 l_Count number;
144
145 /*-----------------------------------------------------------------------+
146 | Cursor Declarations |
147 +-----------------------------------------------------------------------*/
148
149 CURSOR party1_cur(P_PARTY_ID number) IS
150 select
151 party.party_name BORROWER_NAME,
152 party.JGZZ_FISCAL_CODE,
153 ass.CLASS_CODE,
154 party.SIC_CODE,
155 party.YEAR_ESTABLISHED,
156 null PRIMARY_CONTACT_NAME,
157 party.PRIMARY_PHONE_COUNTRY_CODE,
158 party.PRIMARY_PHONE_AREA_CODE,
159 party.PRIMARY_PHONE_NUMBER,
160 party.PRIMARY_PHONE_EXTENSION,
161 party.CURR_FY_POTENTIAL_REVENUE
162 from hz_parties party,
163 HZ_CODE_ASSIGNMENTS ass
164 where party.party_id = P_PARTY_ID and
165 ass.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' and
166 ass.OWNER_TABLE_ID(+) = party.party_id and
167 ass.CLASS_CATEGORY(+) = 'CUSTOMER_CATEGORY'and
168 ass.PRIMARY_FLAG(+) = 'Y' and
169 ass.START_DATE_ACTIVE(+) <= sysdate and
170 nvl(ass.END_DATE_ACTIVE(+), sysdate) >= sysdate;
171
172 CURSOR party2_cur(P_PARTY_ID number) IS
173 select
174 loc.COUNTRY,
175 loc.address1,
176 loc.address2,
177 loc.address3,
178 loc.address4,
179 loc.city,
180 loc.postal_code,
181 loc.state
182 from hz_party_sites site,
183 hz_locations loc,
184 HZ_PARTY_SITE_USES use
185 where site.party_id = P_PARTY_ID and
186 site.PARTY_SITE_ID = use.PARTY_SITE_ID(+) and
187 use.SITE_USE_TYPE(+) = 'BILL_TO' and
188 use.PRIMARY_PER_TYPE(+) = 'Y' and
189 use.status(+) = 'A' and
190 site.location_id = loc.location_id
191 order by use.PRIMARY_PER_TYPE;
192
193 CURSOR customer_since_cur(P_PARTY_ID number) IS
194 select min(account_established_date)
195 from hz_cust_accounts_all
196 where
197 account_established_date is not null and
198 party_id = P_PARTY_ID;
199
200 BEGIN
201
202 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
203
204 open party1_cur(P_BORROWER_TBL(1).BORROWER_PARTY_ID);
205 fetch party1_cur into
206 P_BORROWER_TBL(1).BORROWER_NAME,
207 P_BORROWER_TBL(1).tax_payer_code,
208 P_BORROWER_TBL(1).CUSTOMER_CLASSIFICATION,
209 P_BORROWER_TBL(1).INDUSTRIAL_CLASSIFICATION,
210 P_BORROWER_TBL(1).YEAR_ESTABLISHED,
211 P_BORROWER_TBL(1).PRIMARY_CONTACT_NAME,
212 P_BORROWER_TBL(1).PRIMARY_PHONE_COUNTRY_CODE,
213 P_BORROWER_TBL(1).PRIMARY_PHONE_AREA_CODE,
214 P_BORROWER_TBL(1).PRIMARY_PHONE_NUMBER,
215 P_BORROWER_TBL(1).PRIMARY_PHONE_EXTENSION,
216 P_BORROWER_TBL(1).ANNUAL_REVENUE;
217 close party1_cur;
218
219 open party2_cur(P_BORROWER_TBL(1).BORROWER_PARTY_ID);
220 fetch party2_cur into
221 P_BORROWER_TBL(1).COUNTRY,
222 P_BORROWER_TBL(1).ADDRESS1,
223 P_BORROWER_TBL(1).ADDRESS2,
224 P_BORROWER_TBL(1).ADDRESS3,
225 P_BORROWER_TBL(1).ADDRESS4,
226 P_BORROWER_TBL(1).CITY,
227 P_BORROWER_TBL(1).POSTAL_CODE,
228 P_BORROWER_TBL(1).STATE;
229 close party2_cur;
230
231 open customer_since_cur(P_BORROWER_TBL(1).BORROWER_PARTY_ID);
232 fetch customer_since_cur into P_BORROWER_TBL(1).CUSTOMER_SINCE;
233 close customer_since_cur;
234
235 FOR l_Count IN 2..P_BORROWER_TBL.COUNT LOOP
236 P_BORROWER_TBL(l_Count).BORROWER_NAME := P_BORROWER_TBL(1).BORROWER_NAME;
237 P_BORROWER_TBL(l_Count).tax_payer_code := P_BORROWER_TBL(1).tax_payer_code;
238 P_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION := P_BORROWER_TBL(1).CUSTOMER_CLASSIFICATION;
239 P_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION := P_BORROWER_TBL(1).INDUSTRIAL_CLASSIFICATION;
240 P_BORROWER_TBL(l_Count).YEAR_ESTABLISHED := P_BORROWER_TBL(1).YEAR_ESTABLISHED;
241 P_BORROWER_TBL(l_Count).COUNTRY := P_BORROWER_TBL(1).COUNTRY;
242 P_BORROWER_TBL(l_Count).ADDRESS1 := P_BORROWER_TBL(1).ADDRESS1;
243 P_BORROWER_TBL(l_Count).ADDRESS2 := P_BORROWER_TBL(1).ADDRESS2;
244 P_BORROWER_TBL(l_Count).ADDRESS3 := P_BORROWER_TBL(1).ADDRESS3;
245 P_BORROWER_TBL(l_Count).ADDRESS4 := P_BORROWER_TBL(1).ADDRESS4;
246 P_BORROWER_TBL(l_Count).CITY := P_BORROWER_TBL(1).CITY;
247 P_BORROWER_TBL(l_Count).POSTAL_CODE := P_BORROWER_TBL(1).POSTAL_CODE;
248 P_BORROWER_TBL(l_Count).STATE := P_BORROWER_TBL(1).STATE;
249 P_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME := P_BORROWER_TBL(1).PRIMARY_CONTACT_NAME;
250 P_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE := P_BORROWER_TBL(1).PRIMARY_PHONE_COUNTRY_CODE;
251 P_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE := P_BORROWER_TBL(1).PRIMARY_PHONE_AREA_CODE;
252 P_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER := P_BORROWER_TBL(1).PRIMARY_PHONE_NUMBER;
253 P_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION := P_BORROWER_TBL(1).PRIMARY_PHONE_EXTENSION;
254 P_BORROWER_TBL(l_Count).ANNUAL_REVENUE := P_BORROWER_TBL(1).ANNUAL_REVENUE;
255 END LOOP;
256
257 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate borrower overview');
262 END;
263
264
265
266
267 /*========================================================================
268 | PRIVATE PROCEDURE GET_LOAN_SUMMARY
269 |
270 | DESCRIPTION
271 | This procedure generates loans summary part.
272 |
273 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
274 | GEN_SINGLE_BORROWER_SUMMARY
275 |
276 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
277 | LogMessage
278 |
279 | PARAMETERS
280 | P_BORROWER_TBL IN OUT NOCOPY borrower table
281 |
282 | KNOWN ISSUES
283 | None
284 |
285 | NOTES
286 | Any interesting aspect of the code in the package body which needs
287 | to be stated.
288 |
289 | MODIFICATION HISTORY
290 | Date Author Description of Changes
291 | 04-14-2004 scherkas Created
292 |
293 *=======================================================================*/
294 PROCEDURE GET_LOAN_SUMMARY(P_BORROWER_TBL IN OUT NOCOPY LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL)
295 IS
296
297 /*-----------------------------------------------------------------------+
298 | Local Variable Declarations and initializations |
299 +-----------------------------------------------------------------------*/
300
301 l_api_name CONSTANT VARCHAR2(30) := 'GET_LOAN_SUMMARY';
302 l_total_paid_principal NUMBER;
303 l_Count number;
304
305 /*-----------------------------------------------------------------------+
306 | Cursor Declarations |
307 +-----------------------------------------------------------------------*/
308
309 CURSOR loans_cur(P_PARTY_ID number, P_ORG_ID number, P_CURRENCY varchar2) IS
310 select
311 nvl(count(head.loan_id), 0),
312 nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.REQUESTED_AMOUNT, (head.REQUESTED_AMOUNT*head.EXCHANGE_RATE))), 0),
313 nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.FUNDED_AMOUNT, (head.FUNDED_AMOUNT*head.EXCHANGE_RATE))), 0)
314 from
315 lns_participants par,
316 lns_loan_headers_all head
317 where
318 par.hz_party_id = P_PARTY_ID and
319 par.loan_id = head.loan_id and
320 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER') and
324 CURSOR func_curr_cur(P_ORG_ID number) IS
321 head.loan_status in ('ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
322 head.org_id = P_ORG_ID;
323
325 select books.currency_code
326 from lns_system_options_all sys,
327 gl_sets_of_books books
328 where sys.org_id = P_ORG_ID and
329 sys.set_of_books_id = books.set_of_books_id;
330
331 CURSOR amount_ytd_cur(P_PARTY_ID number, P_LINE_TYPE VARCHAR2, P_TIME_FLAG VARCHAR2, P_ORG_ID number) IS
332 select
333 nvl(sum(rec.ACCTD_AMOUNT_APPLIED_TO), 0)
334 from
335 lns_loan_headers_ALL loan,
336 LNS_AMORTIZATION_SCHEDS am,
337 ar_receivable_applications_ALL rec,
338 lns_participants par
339 where
340 par.hz_party_id = P_PARTY_ID and
341 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER') and
342 par.loan_id = loan.loan_id and
343 loan.loan_status in ('ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
344 loan.loan_id = am.loan_id and
345 loan.org_id = P_ORG_ID and
346 rec.org_id = loan.org_id and
347 rec.APPLIED_CUSTOMER_TRX_ID =
348 decode(P_LINE_TYPE, 'PRIN', am.principal_trx_id, 'INT', am.interest_trx_id, 'FEE', am.fee_trx_id) and
349 rec.application_type = 'CASH' and
350 trunc(rec.APPLY_DATE) >= decode(P_TIME_FLAG, 'YTD', trunc(sysdate, 'YYYY'), trunc(rec.APPLY_DATE)) and
351 trunc(rec.APPLY_DATE) <= decode(P_TIME_FLAG, 'YTD', trunc(add_months(sysdate, 12), 'YYYY')-1, trunc(rec.APPLY_DATE));
352
353 BEGIN
354
355 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
356
357 FOR l_Count IN 1..P_BORROWER_TBL.COUNT LOOP
358
359 open func_curr_cur(P_BORROWER_TBL(l_Count).ORG_ID);
360 fetch func_curr_cur into P_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY;
361 close func_curr_cur;
362
363 open loans_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, P_BORROWER_TBL(l_Count).ORG_ID,P_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
364 fetch loans_cur into
365 P_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS,
366 P_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT,
367 P_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT;
368 close loans_cur;
369
370 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'PRIN', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
371 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD;
372 close amount_ytd_cur;
373
374 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'INT', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
375 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD;
376 close amount_ytd_cur;
377
378 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'FEE', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
379 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD;
380 close amount_ytd_cur;
381
382 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'PRIN', 'ALL', P_BORROWER_TBL(l_Count).ORG_ID);
383 fetch amount_ytd_cur into l_total_paid_principal;
384 close amount_ytd_cur;
385
386 P_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL := P_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT - l_total_paid_principal;
387
388 P_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT := 0;
389 P_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE := null;
390
391 END LOOP;
392
393 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate loans summary');
398 END;
399
400
401
402 /*========================================================================
403 | PRIVATE PROCEDURE GET_CREDIT_SUMMARY
404 |
405 | DESCRIPTION
406 | This procedure generates credit summary part.
407 |
408 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
409 | GEN_SINGLE_BORROWER_SUMMARY
410 |
411 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
412 | LogMessage
413 |
414 | PARAMETERS
415 | P_BORROWER_TBL IN OUT NOCOPY borrower table
416 |
417 | KNOWN ISSUES
418 | None
419 |
420 | NOTES
421 | Any interesting aspect of the code in the package body which needs
422 | to be stated.
423 |
424 | MODIFICATION HISTORY
425 | Date Author Description of Changes
426 | 04-14-2004 scherkas Created
427 |
428 *=======================================================================*/
429 PROCEDURE GET_CREDIT_SUMMARY(P_BORROWER_TBL IN OUT NOCOPY LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL)
430 IS
431
432 /*-----------------------------------------------------------------------+
433 | Local Variable Declarations and initializations |
434 +-----------------------------------------------------------------------*/
435
436 l_api_name CONSTANT VARCHAR2(30) := 'GET_CREDIT_SUMMARY';
437 l_Count number;
438
439 /*-----------------------------------------------------------------------+
440 | Cursor Declarations |
441 +-----------------------------------------------------------------------*/
442
443 CURSOR credit_cur(P_PARTY_ID number) IS
444 select
445 CREDIT_CLASSIFICATION,
446 REVIEW_CYCLE,
450 CREDIT_CHECKING,
447 LAST_CREDIT_REVIEW_DATE,
448 CREDIT_RATING,
449 CREDIT_HOLD,
451 TOLERANCE
452 from HZ_CUSTOMER_PROFILES
453 where party_id = P_PARTY_ID and
454 cust_account_id = -1 and
455 site_use_id is null;
456
457 BEGIN
458
459 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
460
461 open credit_cur(P_BORROWER_TBL(1).BORROWER_PARTY_ID);
462 fetch credit_cur into
463 P_BORROWER_TBL(1).CREDIT_CLASSIFICATION,
464 P_BORROWER_TBL(1).REVIEW_CYCLE,
465 P_BORROWER_TBL(1).LAST_CREDIT_REVIEW_DATE,
466 P_BORROWER_TBL(1).CREDIT_RATING,
467 P_BORROWER_TBL(1).CREDIT_HOLD,
468 P_BORROWER_TBL(1).CREDIT_CHECKING,
469 P_BORROWER_TBL(1).TOLERANCE;
470 close credit_cur;
471
472 FOR l_Count IN 2..P_BORROWER_TBL.COUNT LOOP
473 P_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION := P_BORROWER_TBL(1).CREDIT_CLASSIFICATION;
474 P_BORROWER_TBL(l_Count).REVIEW_CYCLE := P_BORROWER_TBL(1).REVIEW_CYCLE;
475 P_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE := P_BORROWER_TBL(1).LAST_CREDIT_REVIEW_DATE;
476 P_BORROWER_TBL(l_Count).CREDIT_RATING := P_BORROWER_TBL(1).CREDIT_RATING;
477 P_BORROWER_TBL(l_Count).CREDIT_HOLD := P_BORROWER_TBL(1).CREDIT_HOLD;
478 P_BORROWER_TBL(l_Count).CREDIT_CHECKING := P_BORROWER_TBL(1).CREDIT_CHECKING;
479 P_BORROWER_TBL(l_Count).TOLERANCE := P_BORROWER_TBL(1).TOLERANCE;
480 END LOOP;
481
482 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate credit summary');
487 END;
488
489
490
491
492 /*========================================================================
493 | PRIVATE PROCEDURE GEN_SINGLE_BORROWER_SUMMARY
494 |
495 | DESCRIPTION
496 | This procedure generates a single borrower summary.
497 |
498 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
499 | GENERATE_BORROWERS_SUMMARY
500 |
501 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
502 | LogMessage
503 |
504 | PARAMETERS
505 | P_LOAN_REC IN OUT NOCOPY Loan record to bill
506 |
507 | KNOWN ISSUES
508 | None
509 |
510 | NOTES
511 | Any interesting aspect of the code in the package body which needs
512 | to be stated.
513 |
514 | MODIFICATION HISTORY
515 | Date Author Description of Changes
516 | 04-14-2004 scherkas Created
517 |
518 *=======================================================================*/
519 PROCEDURE GEN_SINGLE_BORROWER_SUMMARY(
520 P_API_VERSION IN NUMBER,
521 P_INIT_MSG_LIST IN VARCHAR2,
522 P_COMMIT IN VARCHAR2,
523 P_VALIDATION_LEVEL IN NUMBER,
524 P_BORROWER_PARTY_ID IN NUMBER,
525 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
526 X_MSG_COUNT OUT NOCOPY NUMBER,
527 X_MSG_DATA OUT NOCOPY VARCHAR2)
528 IS
529
530 /*-----------------------------------------------------------------------+
531 | Local Variable Declarations and initializations |
532 +-----------------------------------------------------------------------*/
533
534 l_api_name CONSTANT VARCHAR2(30) := 'GEN_SINGLE_BORROWER_SUMMARY';
535 l_api_version CONSTANT NUMBER := 1.0;
536 l_return_status VARCHAR2(1);
537 l_msg_count NUMBER;
538 l_msg_data VARCHAR2(32767);
539 l_borrower_id number;
540 l_ORG_ID NUMBER;
541 l_ORG_NAME VARCHAR2(240);
542 l_LEGAL_ENTITY_ID NUMBER;
543 l_LEGAL_ENTITY_NAME VARCHAR2(240);
544 l_Count number;
545
546 l_borrower_tbl LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL;
547
548 /*-----------------------------------------------------------------------+
549 | Cursor Declarations |
550 +-----------------------------------------------------------------------*/
551
552 CURSOR orgs_cur IS
553 select org.ORGANIZATION_ID,
554 org.name,
555 null,
556 null
557 from lns_system_options_all sys,
558 hr_operating_units org
559 where sys.ORG_ID = org.ORGANIZATION_ID;
560
561 BEGIN
562
563 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
564
565 -- Standard start of API savepoint
566 SAVEPOINT PROCESS_SINGLE_LOAN_PVT;
567 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
568
569 -- Standard call to check for call compatibility
570 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572 END IF;
573
574 -- Initialize message list if p_init_msg_list is set to TRUE
575 IF FND_API.To_Boolean(p_init_msg_list) THEN
576 FND_MSG_PUB.initialize;
577 END IF;
578
579 -- Initialize API return status to success
580 l_return_status := FND_API.G_RET_STS_SUCCESS;
581
582 -- START OF BODY OF API
583
584 l_Count := 0;
585 open orgs_cur;
586 LOOP
587
588 fetch orgs_cur into l_ORG_ID,
589 l_ORG_NAME,
590 l_LEGAL_ENTITY_ID,
594 l_Count := l_Count + 1;
591 l_LEGAL_ENTITY_NAME;
592 exit when orgs_cur%NOTFOUND;
593
595 l_borrower_tbl(l_Count).ORG_ID := l_ORG_ID;
596 l_borrower_tbl(l_Count).ORG_NAME := l_ORG_NAME;
597 l_borrower_tbl(l_Count).LEGAL_ENTITY_ID := l_LEGAL_ENTITY_ID;
598 l_borrower_tbl(l_Count).LEGAL_ENTITY_NAME := l_LEGAL_ENTITY_NAME;
599 l_borrower_tbl(l_Count).BORROWER_PARTY_ID := P_BORROWER_PARTY_ID;
600
601 END LOOP;
602 close orgs_cur;
603
604 /* generate borrower overview part */
605 GET_BORROWER_OVERVIEW(l_borrower_tbl);
606
607 /* generate loans summary part */
608 GET_LOAN_SUMMARY(l_borrower_tbl);
609
610 /* generate credit summary part */
611 GET_CREDIT_SUMMARY(l_borrower_tbl);
612
613 delete from LNS_BORROWER_SUMMARIES
614 where BORROWER_PARTY_ID = P_BORROWER_PARTY_ID;
615
616 -- borrower summary
617 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
618 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Borrower summary');
619 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_PARTY_ID: ' || l_BORROWER_TBL(l_Count).BORROWER_PARTY_ID);
620 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || l_BORROWER_TBL(l_Count).BORROWER_NAME);
621 LogMessage(FND_LOG.LEVEL_STATEMENT, 'tax_payer_code: ' || l_BORROWER_TBL(l_Count).tax_payer_code);
622 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOMER_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION);
623 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INDUSTRIAL_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION);
624 LogMessage(FND_LOG.LEVEL_STATEMENT, 'YEAR_ESTABLISHED: ' || l_BORROWER_TBL(l_Count).YEAR_ESTABLISHED);
625 LogMessage(FND_LOG.LEVEL_STATEMENT, 'COUNTRY: ' || l_BORROWER_TBL(l_Count).COUNTRY);
626 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS1: ' || l_BORROWER_TBL(l_Count).ADDRESS1);
627 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS2: ' || l_BORROWER_TBL(l_Count).ADDRESS2);
628 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS3: ' || l_BORROWER_TBL(l_Count).ADDRESS3);
629 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS4: ' || l_BORROWER_TBL(l_Count).ADDRESS4);
630 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CITY: ' || l_BORROWER_TBL(l_Count).CITY);
631 LogMessage(FND_LOG.LEVEL_STATEMENT, 'POSTAL_CODE: ' || l_BORROWER_TBL(l_Count).POSTAL_CODE);
632 LogMessage(FND_LOG.LEVEL_STATEMENT, 'STATE: ' || l_BORROWER_TBL(l_Count).STATE);
633 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_CONTACT_NAME: ' || l_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME);
634 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_COUNTRY_CODE: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE);
635 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_AREA_CODE: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE);
636 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_NUMBER: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER);
637 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_EXTENSION: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION);
638 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ANNUAL_REVENUE: ' || l_BORROWER_TBL(l_Count).ANNUAL_REVENUE);
639 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOMER_SINCE: ' || l_BORROWER_TBL(l_Count).CUSTOMER_SINCE);
640
641 -- loans summary
642 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
643 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loans Summary');
644 FOR l_Count IN 1..l_borrower_tbl.COUNT LOOP
645 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Record #' || l_Count);
646 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_ID: ' || l_BORROWER_TBL(l_Count).ORG_ID);
647 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_NAME: ' || l_BORROWER_TBL(l_Count).ORG_NAME);
648 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_ID: ' || l_BORROWER_TBL(l_Count).LEGAL_ENTITY_ID);
649 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_NAME: ' || l_BORROWER_TBL(l_Count).LEGAL_ENTITY_NAME);
650 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NUM_ACTIVE_LOANS: ' || l_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS);
651 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_REMAINING_PRINCIPAL: ' || l_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL);
652 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_PRINCIPAL_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD);
653 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_INTEREST_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD);
654 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_FEE_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD);
655 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_REQUESTED_LOAN_AMOUNT: ' || l_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT);
656 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_APPROVED_LOAN_AMOUNT: ' || l_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT);
657 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PLEDGED_COLL_AMOUNT: ' || l_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT);
658 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LAST_COLL_VALUATION_DATE: ' || l_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE);
659 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FUNCTIONAL_CURRENCY: ' || l_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
660 END LOOP;
661
662 -- credit summary
663 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
664 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Credit Summary');
665 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION);
666 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REVIEW_CYCLE: ' || l_BORROWER_TBL(l_Count).REVIEW_CYCLE);
667 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LAST_CREDIT_REVIEW_DATE: ' || l_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE);
668 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_RATING: ' || l_BORROWER_TBL(l_Count).CREDIT_RATING);
669 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_HOLD: ' || l_BORROWER_TBL(l_Count).CREDIT_HOLD);
670 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_CHECKING: ' || l_BORROWER_TBL(l_Count).CREDIT_CHECKING);
671 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOLERANCE: ' || l_BORROWER_TBL(l_Count).TOLERANCE);
672
673 FOR l_Count IN 1..l_borrower_tbl.COUNT LOOP
674 insert into LNS_BORROWER_SUMMARIES(
675 BORROWER_PARTY_ID,
679 INDUSTRIAL_CLASSIFICATION,
676 BORROWER_NAME,
677 tax_payer_code,
678 CUSTOMER_CLASSIFICATION,
680 YEAR_ESTABLISHED,
681 COUNTRY,
682 ADDRESS1,
683 ADDRESS2,
684 ADDRESS3,
685 ADDRESS4,
686 CITY,
687 POSTAL_CODE,
688 STATE,
689 PRIMARY_CONTACT_NAME,
690 PRIMARY_PHONE_COUNTRY_CODE,
691 PRIMARY_PHONE_AREA_CODE,
692 PRIMARY_PHONE_NUMBER,
693 PRIMARY_PHONE_EXTENSION,
694 ANNUAL_REVENUE,
695 CUSTOMER_SINCE,
696 NUM_ACTIVE_LOANS,
697 TOTAL_REMAINING_PRINCIPAL,
698 TOTAL_PRINCIPAL_PAID_YTD,
699 TOTAL_INTEREST_PAID_YTD,
700 TOTAL_FEE_PAID_YTD,
701 TOTAL_REQUESTED_LOAN_AMOUNT,
702 TOTAL_APPROVED_LOAN_AMOUNT,
703 PLEDGED_COLL_AMOUNT,
704 LAST_COLL_VALUATION_DATE,
705 CREDIT_CLASSIFICATION,
706 REVIEW_CYCLE,
707 LAST_CREDIT_REVIEW_DATE,
708 CREDIT_RATING,
709 CREDIT_HOLD,
710 CREDIT_CHECKING,
711 TOLERANCE,
712 LAST_UPDATE_DATE,
713 LAST_UPDATED_BY,
714 LAST_UPDATE_LOGIN,
715 CREATION_DATE,
716 CREATED_BY,
717 REQUEST_ID,
718 PROGRAM_APPLICATION_ID,
719 PROGRAM_UPDATE_DATE,
720 OBJECT_VERSION_NUMBER,
721 ORG_ID,
722 ORG_NAME,
723 LEGAL_ENTITY_ID,
724 LEGAL_ENTITY_NAME,
725 FUNCTIONAL_CURRENCY)
726 values(
727 l_BORROWER_TBL(l_Count).BORROWER_PARTY_ID,
728 l_BORROWER_TBL(l_Count).BORROWER_NAME,
729 l_BORROWER_TBL(l_Count).tax_payer_code,
730 l_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION,
731 l_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION,
732 l_BORROWER_TBL(l_Count).YEAR_ESTABLISHED,
733 l_BORROWER_TBL(l_Count).COUNTRY,
734 l_BORROWER_TBL(l_Count).ADDRESS1,
735 l_BORROWER_TBL(l_Count).ADDRESS2,
736 l_BORROWER_TBL(l_Count).ADDRESS3,
737 l_BORROWER_TBL(l_Count).ADDRESS4,
738 l_BORROWER_TBL(l_Count).CITY,
739 l_BORROWER_TBL(l_Count).POSTAL_CODE,
740 l_BORROWER_TBL(l_Count).STATE,
741 l_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME,
742 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE,
743 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE,
744 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER,
745 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION,
746 l_BORROWER_TBL(l_Count).ANNUAL_REVENUE,
747 l_BORROWER_TBL(l_Count).CUSTOMER_SINCE,
748 l_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS,
749 l_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL,
750 l_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD,
751 l_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD,
752 l_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD,
753 l_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT,
754 l_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT,
755 l_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT,
756 l_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE,
757 l_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION,
758 l_BORROWER_TBL(l_Count).REVIEW_CYCLE,
759 l_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE,
760 l_BORROWER_TBL(l_Count).CREDIT_RATING,
761 l_BORROWER_TBL(l_Count).CREDIT_HOLD,
762 l_BORROWER_TBL(l_Count).CREDIT_CHECKING,
763 l_BORROWER_TBL(l_Count).TOLERANCE,
764 sysdate,
765 FND_GLOBAL.USER_ID,
766 FND_GLOBAL.LOGIN_ID,
767 sysdate,
768 FND_GLOBAL.USER_ID,
769 FND_GLOBAL.Conc_Request_Id,
770 FND_GLOBAL.PROG_APPL_ID,
771 sysdate,
772 1,
773 l_BORROWER_TBL(l_Count).ORG_ID,
774 l_BORROWER_TBL(l_Count).ORG_NAME,
775 l_BORROWER_TBL(l_Count).LEGAL_ENTITY_ID,
776 l_BORROWER_TBL(l_Count).LEGAL_ENTITY_NAME,
777 l_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
778
779 END LOOP;
780
781 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Success!');
782
783 if P_COMMIT = FND_API.G_TRUE then
784 COMMIT WORK;
785 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited borrower');
786 end if;
787
788 -- END OF BODY OF API
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790
791 -- Standard call to get message count and if count is 1, get message info
792 FND_MSG_PUB.Count_And_Get(
793 p_encoded => FND_API.G_FALSE,
794 p_count => x_msg_count,
795 p_data => x_msg_data);
796
797 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully processed borrower');
798 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
799
800 EXCEPTION
801 WHEN FND_API.G_EXC_ERROR THEN
802 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
805 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
811 WHEN OTHERS THEN
812 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
814 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
815 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
816 END IF;
817 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
818 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
819 END;
820
821
822
823
824 /*========================================================================
825 | PUBLIC PROCEDURE LNS_BORR_SUM_CONCUR
826 |
827 | DESCRIPTION
828 | This procedure gets called from CM to start borrower summary generation program.
829 |
830 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
831 | None
832 |
833 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
834 | LogMessage
835 |
836 | PARAMETERS
837 | ERRBUF OUT Returns errors to CM
838 | RETCODE OUT Returns error code to CM
839 | BORROWER_PARTY_ID IN Inputs borrower party id
840 |
841 | KNOWN ISSUES
842 | None
843 |
844 | NOTES
845 | Any interesting aspect of the code in the package body which needs
846 | to be stated.
847 |
848 | MODIFICATION HISTORY
849 | Date Author Description of Changes
850 | 04-14-2004 scherkas Created
851 |
852 *=======================================================================*/
853 PROCEDURE LNS_BORR_SUM_CONCUR(
854 ERRBUF OUT NOCOPY VARCHAR2,
855 RETCODE OUT NOCOPY VARCHAR2,
856 BORROWER_PARTY_ID IN NUMBER)
857 IS
858
859 /*-----------------------------------------------------------------------+
860 | Local Variable Declarations and initializations |
861 +-----------------------------------------------------------------------*/
862 l_msg_count number;
863
864 BEGIN
865
866 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
867 LogMessage(FND_LOG.LEVEL_STATEMENT, '<<--------Generating borrowers summary...-------->>');
868
869 GENERATE_BORROWERS_SUMMARY(
870 P_API_VERSION => 1.0,
871 P_INIT_MSG_LIST => FND_API.G_TRUE,
872 P_COMMIT => FND_API.G_TRUE,
873 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
874 P_BORROWER_PARTY_ID => BORROWER_PARTY_ID,
875 X_RETURN_STATUS => RETCODE,
876 X_MSG_COUNT => l_msg_count,
877 X_MSG_DATA => ERRBUF);
878
879 END;
880
881
882
883 /*========================================================================
884 | PUBLIC PROCEDURE GENERATE_BORROWERS_SUMMARY
885 |
886 | DESCRIPTION
887 | This procedure generates summary info for all available borrowers
888 |
889 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
890 | LNS_BORR_SUM_CONCUR
891 |
892 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
893 | LogMessage
894 | init
895 |
896 | PARAMETERS
897 | P_API_VERSION IN Standard in parameter
898 | P_INIT_MSG_LIST IN Standard in parameter
899 | P_COMMIT IN Standard in parameter
900 | P_VALIDATION_LEVEL IN Standard in parameter
901 | P_BORROWER_PARTY_ID IN Inputs borrower party id
902 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
903 | X_MSG_COUNT OUT NOCOPY Standard out parameter
904 | X_MSG_DATA OUT NOCOPY Standard out parameter
905 |
906 | KNOWN ISSUES
907 | None
908 |
909 | NOTES
910 | Any interesting aspect of the code in the package body which needs
911 | to be stated.
912 |
913 | MODIFICATION HISTORY
914 | Date Author Description of Changes
915 | 04-14-2004 scherkas Created
916 |
917 *=======================================================================*/
918 PROCEDURE GENERATE_BORROWERS_SUMMARY(
919 P_API_VERSION IN NUMBER,
920 P_INIT_MSG_LIST IN VARCHAR2,
921 P_COMMIT IN VARCHAR2,
922 P_VALIDATION_LEVEL IN NUMBER,
923 P_BORROWER_PARTY_ID IN NUMBER,
924 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
925 X_MSG_COUNT OUT NOCOPY NUMBER,
926 X_MSG_DATA OUT NOCOPY VARCHAR2)
927 IS
928
929 /*-----------------------------------------------------------------------+
930 | Local Variable Declarations and initializations |
931 +-----------------------------------------------------------------------*/
932
933 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_BORROWERS_SUMMARY';
934 l_api_version CONSTANT NUMBER := 1.0;
935 l_return_status VARCHAR2(1);
936 l_msg_count NUMBER;
937 l_msg_data VARCHAR2(32767);
938 l_Count number;
939 l_Count1 number;
940 l_party_id number;
941 l_party_number VARCHAR2(30);
942 l_party_name VARCHAR2(360);
943
944 l_borrowers_tbl DBMS_SQL.NUMBER_TABLE;
945
946 /*-----------------------------------------------------------------------+
947 | Cursor Declarations |
948 +-----------------------------------------------------------------------*/
949
950 CURSOR borrowers_cur(P_BORROWER_ID number) IS
951 select
952 distinct party.party_id,
956 lns_participants par,
953 party.PARTY_NUMBER,
954 party.party_name
955 from
957 hz_parties party,
958 lns_loan_headers_all head
959 where
960 party.party_id = nvl(P_BORROWER_ID, party.party_id) and
961 party.party_id = par.hz_party_id and
962 par.loan_id = head.loan_id and
963 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER');
964
965 BEGIN
966
967 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
968
969 -- Standard start of API savepoint
970 SAVEPOINT GENERATE_BORR_SUM;
971 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
972
973 -- Standard call to check for call compatibility
974 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 -- Initialize message list if p_init_msg_list is set to TRUE
979 IF FND_API.To_Boolean(p_init_msg_list) THEN
980 FND_MSG_PUB.initialize;
981 END IF;
982
983 -- Initialize API return status to success
984 l_return_status := FND_API.G_RET_STS_SUCCESS;
985
986 -- START OF BODY OF API
987
988 init;
989
990 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters:');
991 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_PARTY_ID: ' || P_BORROWER_PARTY_ID);
992 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Query for all available borrowers...');
993
994 open borrowers_cur(P_BORROWER_PARTY_ID);
995
996 l_Count1 := 0;
997
998 LOOP
999
1000 fetch borrowers_cur into
1001 l_party_id,
1002 l_party_number,
1003 l_party_name;
1004
1005 exit when borrowers_cur%NOTFOUND;
1006
1007 l_Count1 := l_Count1 + 1;
1008 l_borrowers_tbl(l_Count1) := l_party_id;
1009
1010 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1011 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Borrower #' || l_Count1);
1012 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTY_ID: ' || l_borrowers_tbl(l_Count1));
1013 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NUMBER: ' || l_party_number);
1014 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NAME: ' || l_party_name);
1015
1016 END LOOP;
1017
1018 close borrowers_cur;
1019
1020 l_Count := l_borrowers_tbl.count;
1021 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1022 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total found ' || l_Count || ' borrowers');
1023
1024 if l_Count > 0 then
1025
1026 /* bill all selected loans */
1027 FOR l_Count1 IN 1..l_Count LOOP
1028
1029 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1030 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing borrower #' || l_Count1);
1031
1032 GEN_SINGLE_BORROWER_SUMMARY(
1033 P_API_VERSION => 1.0,
1034 P_INIT_MSG_LIST => FND_API.G_FALSE,
1035 P_COMMIT => P_COMMIT,
1036 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1037 P_BORROWER_PARTY_ID => l_borrowers_tbl(l_Count1),
1038 X_RETURN_STATUS => l_return_status,
1039 X_MSG_COUNT => l_msg_count,
1040 X_MSG_DATA => l_msg_data);
1041
1042 END LOOP;
1043
1044 END IF;
1045
1046 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1047 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total processed ' || l_Count || ' borrowers');
1048
1049 if P_COMMIT = FND_API.G_TRUE then
1050 COMMIT WORK;
1051 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1052 end if;
1053
1054 -- END OF BODY OF API
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056
1057 -- Standard call to get message count and if count is 1, get message info
1058 FND_MSG_PUB.Count_And_Get(
1059 p_encoded => FND_API.G_FALSE,
1060 p_count => x_msg_count,
1061 p_data => x_msg_data);
1062
1063 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1064
1065 EXCEPTION
1066 WHEN FND_API.G_EXC_ERROR THEN
1067 ROLLBACK TO GENERATE_BORR_SUM;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1070 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1071 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1072 ROLLBACK TO GENERATE_BORR_SUM;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1075 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1076 WHEN OTHERS THEN
1077 ROLLBACK TO GENERATE_BORR_SUM;
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1080 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1081 END IF;
1082 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1083 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1084 END;
1085
1086 BEGIN
1087 G_LOG_ENABLED := 'N';
1088 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1089
1090 /* getting msg logging info */
1091 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1092 /*
1093 if (G_LOG_ENABLED = 'N') then
1094 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1095 else
1096 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1097 end if;
1098 */
1099
1100 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1101 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1102
1103 END;