[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.9 2011/08/22 10:37:57 gparuchu ship $ */
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_count_cur(P_PARTY_ID number, P_ORG_ID number) IS
310 select
311 nvl(count(head.loan_id), 0)
312 from
313 lns_participants par,
314 lns_loan_headers_all head
315 where
316 par.hz_party_id = P_PARTY_ID and
317 par.loan_id = head.loan_id and
318 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER', 'GUARANTOR') and
319 head.loan_status in ('INCOMPLETE','PENDING','ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
320 head.org_id = P_ORG_ID;
321
322 CURSOR loans_cur(P_PARTY_ID number, P_ORG_ID number, P_CURRENCY varchar2) IS
323 select
324 --nvl(count(head.loan_id), 0),
325 nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.REQUESTED_AMOUNT + head.ADD_REQUESTED_AMOUNT, ((head.REQUESTED_AMOUNT+ head.ADD_REQUESTED_AMOUNT)*head.EXCHANGE_RATE))), 0),
326 nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.FUNDED_AMOUNT, (head.FUNDED_AMOUNT*head.EXCHANGE_RATE))), 0)
327 from
328 lns_participants par,
329 lns_loan_headers_all head
330 where
331 par.hz_party_id = P_PARTY_ID and
332 par.loan_id = head.loan_id and
333 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER', 'GUARANTOR') and
334 head.loan_status in ('INCOMPLETE','PENDING','ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
335 head.org_id = P_ORG_ID;
336
337 CURSOR func_curr_cur(P_ORG_ID number) IS
338 select books.currency_code
339 from lns_system_options_all sys,
340 gl_sets_of_books books
341 where sys.org_id = P_ORG_ID and
342 sys.set_of_books_id = books.set_of_books_id;
343
344 CURSOR amount_ytd_cur(P_PARTY_ID number, P_LINE_TYPE VARCHAR2, P_TIME_FLAG VARCHAR2, P_ORG_ID number) IS
345 select
346 nvl(sum(rec.ACCTD_AMOUNT_APPLIED_TO), 0)
347 from
348 lns_loan_headers_ALL loan,
349 LNS_AMORTIZATION_SCHEDS am,
350 ar_receivable_applications_ALL rec,
351 lns_participants par
352 where
353 par.hz_party_id = P_PARTY_ID and
354 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER', 'GUARANTOR') and
355 par.loan_id = loan.loan_id and
356 loan.loan_status in ('ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
357 loan.loan_id = am.loan_id and
358 loan.org_id = P_ORG_ID and
359 rec.org_id = loan.org_id and
360 rec.APPLIED_CUSTOMER_TRX_ID =
361 decode(P_LINE_TYPE, 'PRIN', am.principal_trx_id, 'INT', am.interest_trx_id, 'FEE', am.fee_trx_id) and
362 rec.application_type = 'CASH' and
363 trunc(rec.APPLY_DATE) >= decode(P_TIME_FLAG, 'YTD', trunc(sysdate, 'YYYY'), trunc(rec.APPLY_DATE)) and
364 trunc(rec.APPLY_DATE) <= decode(P_TIME_FLAG, 'YTD', trunc(add_months(sysdate, 12), 'YYYY')-1, trunc(rec.APPLY_DATE));
365
366 BEGIN
367
368 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
369
370 FOR l_Count IN 1..P_BORROWER_TBL.COUNT LOOP
371
372 open func_curr_cur(P_BORROWER_TBL(l_Count).ORG_ID);
373 fetch func_curr_cur into P_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY;
374 close func_curr_cur;
375
376 open loans_count_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, P_BORROWER_TBL(l_Count).ORG_ID);
377 fetch loans_count_cur into P_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS;
378 close loans_count_cur;
379
380
381 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);
382 fetch loans_cur into
383 --P_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS,
384 P_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT,
385 P_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT;
386 close loans_cur;
387
388 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'PRIN', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
389 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD;
390 close amount_ytd_cur;
391
392 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'INT', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
393 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD;
394 close amount_ytd_cur;
395
396 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'FEE', 'YTD', P_BORROWER_TBL(l_Count).ORG_ID);
397 fetch amount_ytd_cur into P_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD;
398 close amount_ytd_cur;
399
400 open amount_ytd_cur(P_BORROWER_TBL(l_Count).BORROWER_PARTY_ID, 'PRIN', 'ALL', P_BORROWER_TBL(l_Count).ORG_ID);
401 fetch amount_ytd_cur into l_total_paid_principal;
402 close amount_ytd_cur;
403
404 P_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL := P_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT - l_total_paid_principal;
405
406 P_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT := 0;
407 P_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE := null;
408
409 END LOOP;
410
411 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate loans summary');
416 END;
417
418
419
420 /*========================================================================
421 | PRIVATE PROCEDURE GET_CREDIT_SUMMARY
422 |
423 | DESCRIPTION
424 | This procedure generates credit summary part.
425 |
426 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
427 | GEN_SINGLE_BORROWER_SUMMARY
428 |
429 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
430 | LogMessage
431 |
432 | PARAMETERS
433 | P_BORROWER_TBL IN OUT NOCOPY borrower table
434 |
435 | KNOWN ISSUES
436 | None
437 |
438 | NOTES
439 | Any interesting aspect of the code in the package body which needs
440 | to be stated.
441 |
442 | MODIFICATION HISTORY
443 | Date Author Description of Changes
444 | 04-14-2004 scherkas Created
445 |
446 *=======================================================================*/
447 PROCEDURE GET_CREDIT_SUMMARY(P_BORROWER_TBL IN OUT NOCOPY LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL)
448 IS
449
450 /*-----------------------------------------------------------------------+
451 | Local Variable Declarations and initializations |
452 +-----------------------------------------------------------------------*/
453
454 l_api_name CONSTANT VARCHAR2(30) := 'GET_CREDIT_SUMMARY';
455 l_Count number;
456
457 /*-----------------------------------------------------------------------+
458 | Cursor Declarations |
459 +-----------------------------------------------------------------------*/
460
461 CURSOR credit_cur(P_PARTY_ID number) IS
462 select
463 CREDIT_CLASSIFICATION,
464 REVIEW_CYCLE,
465 LAST_CREDIT_REVIEW_DATE,
466 CREDIT_RATING,
467 CREDIT_HOLD,
468 CREDIT_CHECKING,
469 TOLERANCE
470 from HZ_CUSTOMER_PROFILES
471 where party_id = P_PARTY_ID and
472 cust_account_id = -1 and
473 site_use_id is null;
474
475 BEGIN
476
477 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
478
479 open credit_cur(P_BORROWER_TBL(1).BORROWER_PARTY_ID);
480 fetch credit_cur into
481 P_BORROWER_TBL(1).CREDIT_CLASSIFICATION,
482 P_BORROWER_TBL(1).REVIEW_CYCLE,
483 P_BORROWER_TBL(1).LAST_CREDIT_REVIEW_DATE,
484 P_BORROWER_TBL(1).CREDIT_RATING,
485 P_BORROWER_TBL(1).CREDIT_HOLD,
486 P_BORROWER_TBL(1).CREDIT_CHECKING,
487 P_BORROWER_TBL(1).TOLERANCE;
488 close credit_cur;
489
490 FOR l_Count IN 2..P_BORROWER_TBL.COUNT LOOP
491 P_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION := P_BORROWER_TBL(1).CREDIT_CLASSIFICATION;
492 P_BORROWER_TBL(l_Count).REVIEW_CYCLE := P_BORROWER_TBL(1).REVIEW_CYCLE;
493 P_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE := P_BORROWER_TBL(1).LAST_CREDIT_REVIEW_DATE;
494 P_BORROWER_TBL(l_Count).CREDIT_RATING := P_BORROWER_TBL(1).CREDIT_RATING;
495 P_BORROWER_TBL(l_Count).CREDIT_HOLD := P_BORROWER_TBL(1).CREDIT_HOLD;
496 P_BORROWER_TBL(l_Count).CREDIT_CHECKING := P_BORROWER_TBL(1).CREDIT_CHECKING;
497 P_BORROWER_TBL(l_Count).TOLERANCE := P_BORROWER_TBL(1).TOLERANCE;
498 END LOOP;
499
500 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to generate credit summary');
505 END;
506
507
508
509
510 /*========================================================================
511 | PRIVATE PROCEDURE GEN_SINGLE_BORROWER_SUMMARY
512 |
513 | DESCRIPTION
514 | This procedure generates a single borrower summary.
515 |
516 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
517 | GENERATE_BORROWERS_SUMMARY
518 |
519 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
520 | LogMessage
521 |
522 | PARAMETERS
523 | P_LOAN_REC IN OUT NOCOPY Loan record to bill
524 |
525 | KNOWN ISSUES
526 | None
527 |
528 | NOTES
529 | Any interesting aspect of the code in the package body which needs
530 | to be stated.
531 |
532 | MODIFICATION HISTORY
533 | Date Author Description of Changes
534 | 04-14-2004 scherkas Created
535 |
536 *=======================================================================*/
537 PROCEDURE GEN_SINGLE_BORROWER_SUMMARY(
538 P_API_VERSION IN NUMBER,
539 P_INIT_MSG_LIST IN VARCHAR2,
540 P_COMMIT IN VARCHAR2,
541 P_VALIDATION_LEVEL IN NUMBER,
542 P_BORROWER_PARTY_ID IN NUMBER,
543 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
544 X_MSG_COUNT OUT NOCOPY NUMBER,
545 X_MSG_DATA OUT NOCOPY VARCHAR2)
546 IS
547
548 /*-----------------------------------------------------------------------+
549 | Local Variable Declarations and initializations |
550 +-----------------------------------------------------------------------*/
551
552 l_api_name CONSTANT VARCHAR2(30) := 'GEN_SINGLE_BORROWER_SUMMARY';
553 l_api_version CONSTANT NUMBER := 1.0;
554 l_return_status VARCHAR2(1);
555 l_msg_count NUMBER;
556 l_msg_data VARCHAR2(32767);
557 l_borrower_id number;
558 l_ORG_ID NUMBER;
559 l_ORG_NAME VARCHAR2(240);
560 l_LEGAL_ENTITY_ID NUMBER;
561 l_LEGAL_ENTITY_NAME VARCHAR2(240);
562 l_Count number;
563
564 l_borrower_tbl LNS_BORROWERS_SUMMARY_PUB.BORROWER_TBL;
565
566 /*-----------------------------------------------------------------------+
567 | Cursor Declarations |
568 +-----------------------------------------------------------------------*/
569
570 CURSOR orgs_cur IS
571 select org.ORGANIZATION_ID,
572 org.name,
573 null,
574 null
575 from lns_system_options_all sys,
576 hr_operating_units org
577 where sys.ORG_ID = org.ORGANIZATION_ID;
578
579 BEGIN
580
581 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
582
583 -- Standard start of API savepoint
584 SAVEPOINT PROCESS_SINGLE_LOAN_PVT;
585 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
586
587 -- Standard call to check for call compatibility
588 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
590 END IF;
591
592 -- Initialize message list if p_init_msg_list is set to TRUE
593 IF FND_API.To_Boolean(p_init_msg_list) THEN
594 FND_MSG_PUB.initialize;
595 END IF;
596
597 -- Initialize API return status to success
598 l_return_status := FND_API.G_RET_STS_SUCCESS;
599
600 -- START OF BODY OF API
601
602 l_Count := 0;
603 open orgs_cur;
604 LOOP
605
606 fetch orgs_cur into l_ORG_ID,
607 l_ORG_NAME,
608 l_LEGAL_ENTITY_ID,
609 l_LEGAL_ENTITY_NAME;
610 exit when orgs_cur%NOTFOUND;
611
612 l_Count := l_Count + 1;
613 l_borrower_tbl(l_Count).ORG_ID := l_ORG_ID;
614 l_borrower_tbl(l_Count).ORG_NAME := l_ORG_NAME;
615 l_borrower_tbl(l_Count).LEGAL_ENTITY_ID := l_LEGAL_ENTITY_ID;
616 l_borrower_tbl(l_Count).LEGAL_ENTITY_NAME := l_LEGAL_ENTITY_NAME;
617 l_borrower_tbl(l_Count).BORROWER_PARTY_ID := P_BORROWER_PARTY_ID;
618
619 END LOOP;
620 close orgs_cur;
621
622 /* generate borrower overview part */
623 GET_BORROWER_OVERVIEW(l_borrower_tbl);
624
625 /* generate loans summary part */
626 GET_LOAN_SUMMARY(l_borrower_tbl);
627
628 /* generate credit summary part */
629 GET_CREDIT_SUMMARY(l_borrower_tbl);
630
631 delete from LNS_BORROWER_SUMMARIES
632 where BORROWER_PARTY_ID = P_BORROWER_PARTY_ID;
633
634 -- borrower summary
635 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
636 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Borrower summary');
637 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_PARTY_ID: ' || l_BORROWER_TBL(l_Count).BORROWER_PARTY_ID);
638 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_NAME: ' || l_BORROWER_TBL(l_Count).BORROWER_NAME);
639 LogMessage(FND_LOG.LEVEL_STATEMENT, 'tax_payer_code: ' || l_BORROWER_TBL(l_Count).tax_payer_code);
640 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOMER_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION);
641 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INDUSTRIAL_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION);
642 LogMessage(FND_LOG.LEVEL_STATEMENT, 'YEAR_ESTABLISHED: ' || l_BORROWER_TBL(l_Count).YEAR_ESTABLISHED);
643 LogMessage(FND_LOG.LEVEL_STATEMENT, 'COUNTRY: ' || l_BORROWER_TBL(l_Count).COUNTRY);
644 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS1: ' || l_BORROWER_TBL(l_Count).ADDRESS1);
645 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS2: ' || l_BORROWER_TBL(l_Count).ADDRESS2);
646 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS3: ' || l_BORROWER_TBL(l_Count).ADDRESS3);
647 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ADDRESS4: ' || l_BORROWER_TBL(l_Count).ADDRESS4);
648 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CITY: ' || l_BORROWER_TBL(l_Count).CITY);
649 LogMessage(FND_LOG.LEVEL_STATEMENT, 'POSTAL_CODE: ' || l_BORROWER_TBL(l_Count).POSTAL_CODE);
650 LogMessage(FND_LOG.LEVEL_STATEMENT, 'STATE: ' || l_BORROWER_TBL(l_Count).STATE);
651 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_CONTACT_NAME: ' || l_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME);
652 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_COUNTRY_CODE: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE);
653 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_AREA_CODE: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE);
654 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_NUMBER: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER);
655 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PRIMARY_PHONE_EXTENSION: ' || l_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION);
656 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ANNUAL_REVENUE: ' || l_BORROWER_TBL(l_Count).ANNUAL_REVENUE);
657 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CUSTOMER_SINCE: ' || l_BORROWER_TBL(l_Count).CUSTOMER_SINCE);
658
659 -- loans summary
660 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
661 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Loans Summary');
662 FOR l_Count IN 1..l_borrower_tbl.COUNT LOOP
663 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Record #' || l_Count);
664 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_ID: ' || l_BORROWER_TBL(l_Count).ORG_ID);
665 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ORG_NAME: ' || l_BORROWER_TBL(l_Count).ORG_NAME);
666 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_ID: ' || l_BORROWER_TBL(l_Count).LEGAL_ENTITY_ID);
667 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LEGAL_ENTITY_NAME: ' || l_BORROWER_TBL(l_Count).LEGAL_ENTITY_NAME);
668 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NUM_ACTIVE_LOANS: ' || l_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS);
669 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_REMAINING_PRINCIPAL: ' || l_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL);
670 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_PRINCIPAL_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD);
671 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_INTEREST_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD);
672 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_FEE_PAID_YTD: ' || l_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD);
673 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_REQUESTED_LOAN_AMOUNT: ' || l_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT);
674 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOTAL_APPROVED_LOAN_AMOUNT: ' || l_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT);
675 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PLEDGED_COLL_AMOUNT: ' || l_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT);
676 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LAST_COLL_VALUATION_DATE: ' || l_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE);
677 LogMessage(FND_LOG.LEVEL_STATEMENT, 'FUNCTIONAL_CURRENCY: ' || l_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
678 END LOOP;
679
680 -- credit summary
681 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
682 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Credit Summary');
683 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_CLASSIFICATION: ' || l_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION);
684 LogMessage(FND_LOG.LEVEL_STATEMENT, 'REVIEW_CYCLE: ' || l_BORROWER_TBL(l_Count).REVIEW_CYCLE);
685 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LAST_CREDIT_REVIEW_DATE: ' || l_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE);
686 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_RATING: ' || l_BORROWER_TBL(l_Count).CREDIT_RATING);
687 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_HOLD: ' || l_BORROWER_TBL(l_Count).CREDIT_HOLD);
688 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CREDIT_CHECKING: ' || l_BORROWER_TBL(l_Count).CREDIT_CHECKING);
689 LogMessage(FND_LOG.LEVEL_STATEMENT, 'TOLERANCE: ' || l_BORROWER_TBL(l_Count).TOLERANCE);
690
691 FOR l_Count IN 1..l_borrower_tbl.COUNT LOOP
692 insert into LNS_BORROWER_SUMMARIES(
693 BORROWER_PARTY_ID,
694 BORROWER_NAME,
695 tax_payer_code,
696 CUSTOMER_CLASSIFICATION,
697 INDUSTRIAL_CLASSIFICATION,
698 YEAR_ESTABLISHED,
699 COUNTRY,
700 ADDRESS1,
701 ADDRESS2,
702 ADDRESS3,
703 ADDRESS4,
704 CITY,
705 POSTAL_CODE,
706 STATE,
707 PRIMARY_CONTACT_NAME,
708 PRIMARY_PHONE_COUNTRY_CODE,
709 PRIMARY_PHONE_AREA_CODE,
710 PRIMARY_PHONE_NUMBER,
711 PRIMARY_PHONE_EXTENSION,
712 ANNUAL_REVENUE,
713 CUSTOMER_SINCE,
714 NUM_ACTIVE_LOANS,
715 TOTAL_REMAINING_PRINCIPAL,
716 TOTAL_PRINCIPAL_PAID_YTD,
717 TOTAL_INTEREST_PAID_YTD,
718 TOTAL_FEE_PAID_YTD,
719 TOTAL_REQUESTED_LOAN_AMOUNT,
720 TOTAL_APPROVED_LOAN_AMOUNT,
721 PLEDGED_COLL_AMOUNT,
722 LAST_COLL_VALUATION_DATE,
723 CREDIT_CLASSIFICATION,
724 REVIEW_CYCLE,
725 LAST_CREDIT_REVIEW_DATE,
726 CREDIT_RATING,
727 CREDIT_HOLD,
728 CREDIT_CHECKING,
729 TOLERANCE,
730 LAST_UPDATE_DATE,
731 LAST_UPDATED_BY,
732 LAST_UPDATE_LOGIN,
733 CREATION_DATE,
734 CREATED_BY,
735 REQUEST_ID,
736 PROGRAM_APPLICATION_ID,
737 PROGRAM_UPDATE_DATE,
738 OBJECT_VERSION_NUMBER,
739 ORG_ID,
740 ORG_NAME,
741 LEGAL_ENTITY_ID,
742 LEGAL_ENTITY_NAME,
743 FUNCTIONAL_CURRENCY)
744 values(
745 l_BORROWER_TBL(l_Count).BORROWER_PARTY_ID,
746 l_BORROWER_TBL(l_Count).BORROWER_NAME,
747 l_BORROWER_TBL(l_Count).tax_payer_code,
748 l_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION,
749 l_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION,
750 l_BORROWER_TBL(l_Count).YEAR_ESTABLISHED,
751 l_BORROWER_TBL(l_Count).COUNTRY,
752 l_BORROWER_TBL(l_Count).ADDRESS1,
753 l_BORROWER_TBL(l_Count).ADDRESS2,
754 l_BORROWER_TBL(l_Count).ADDRESS3,
755 l_BORROWER_TBL(l_Count).ADDRESS4,
756 l_BORROWER_TBL(l_Count).CITY,
757 l_BORROWER_TBL(l_Count).POSTAL_CODE,
758 l_BORROWER_TBL(l_Count).STATE,
759 l_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME,
760 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE,
761 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE,
762 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER,
763 l_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION,
764 l_BORROWER_TBL(l_Count).ANNUAL_REVENUE,
765 l_BORROWER_TBL(l_Count).CUSTOMER_SINCE,
766 l_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS,
767 l_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL,
768 l_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD,
769 l_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD,
770 l_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD,
771 l_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT,
772 l_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT,
773 l_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT,
774 l_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE,
775 l_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION,
776 l_BORROWER_TBL(l_Count).REVIEW_CYCLE,
777 l_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE,
778 l_BORROWER_TBL(l_Count).CREDIT_RATING,
779 l_BORROWER_TBL(l_Count).CREDIT_HOLD,
780 l_BORROWER_TBL(l_Count).CREDIT_CHECKING,
781 l_BORROWER_TBL(l_Count).TOLERANCE,
782 sysdate,
783 FND_GLOBAL.USER_ID,
784 FND_GLOBAL.LOGIN_ID,
785 sysdate,
786 FND_GLOBAL.USER_ID,
787 FND_GLOBAL.Conc_Request_Id,
788 FND_GLOBAL.PROG_APPL_ID,
789 sysdate,
790 1,
791 l_BORROWER_TBL(l_Count).ORG_ID,
792 l_BORROWER_TBL(l_Count).ORG_NAME,
793 l_BORROWER_TBL(l_Count).LEGAL_ENTITY_ID,
794 l_BORROWER_TBL(l_Count).LEGAL_ENTITY_NAME,
795 l_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
796
797 END LOOP;
798
799 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Success!');
800
801 if P_COMMIT = FND_API.G_TRUE then
802 COMMIT WORK;
803 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited borrower');
804 end if;
805
806 -- END OF BODY OF API
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808
809 -- Standard call to get message count and if count is 1, get message info
810 FND_MSG_PUB.Count_And_Get(
811 p_encoded => FND_API.G_FALSE,
812 p_count => x_msg_count,
813 p_data => x_msg_data);
814
815 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully processed borrower');
816 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
817
818 EXCEPTION
819 WHEN FND_API.G_EXC_ERROR THEN
820 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
823 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
825 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
828 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
829 WHEN OTHERS THEN
830 ROLLBACK TO PROCESS_SINGLE_LOAN_PVT;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
833 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
834 END IF;
835 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
836 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked borrower');
837 END;
838
839
840
841
842 /*========================================================================
843 | PUBLIC PROCEDURE LNS_BORR_SUM_CONCUR
844 |
845 | DESCRIPTION
846 | This procedure gets called from CM to start borrower summary generation program.
847 |
848 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
849 | None
850 |
851 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
852 | LogMessage
853 |
854 | PARAMETERS
855 | ERRBUF OUT Returns errors to CM
856 | RETCODE OUT Returns error code to CM
857 | BORROWER_PARTY_ID IN Inputs borrower party id
858 |
859 | KNOWN ISSUES
860 | None
861 |
862 | NOTES
863 | Any interesting aspect of the code in the package body which needs
864 | to be stated.
865 |
866 | MODIFICATION HISTORY
867 | Date Author Description of Changes
868 | 04-14-2004 scherkas Created
869 |
870 *=======================================================================*/
871 PROCEDURE LNS_BORR_SUM_CONCUR(
872 ERRBUF OUT NOCOPY VARCHAR2,
873 RETCODE OUT NOCOPY VARCHAR2,
874 BORROWER_PARTY_ID IN NUMBER)
875 IS
876
877 /*-----------------------------------------------------------------------+
878 | Local Variable Declarations and initializations |
879 +-----------------------------------------------------------------------*/
880 l_msg_count number;
881
882 BEGIN
883
884 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
885 LogMessage(FND_LOG.LEVEL_STATEMENT, '<<--------Generating borrowers summary...-------->>');
886
887 GENERATE_BORROWERS_SUMMARY(
888 P_API_VERSION => 1.0,
889 P_INIT_MSG_LIST => FND_API.G_TRUE,
890 P_COMMIT => FND_API.G_TRUE,
891 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
892 P_BORROWER_PARTY_ID => BORROWER_PARTY_ID,
893 X_RETURN_STATUS => RETCODE,
894 X_MSG_COUNT => l_msg_count,
895 X_MSG_DATA => ERRBUF);
896
897 END;
898
899
900
901 /*========================================================================
902 | PUBLIC PROCEDURE GENERATE_BORROWERS_SUMMARY
903 |
904 | DESCRIPTION
905 | This procedure generates summary info for all available borrowers
906 |
907 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
908 | LNS_BORR_SUM_CONCUR
909 |
910 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
911 | LogMessage
912 | init
913 |
914 | PARAMETERS
915 | P_API_VERSION IN Standard in parameter
916 | P_INIT_MSG_LIST IN Standard in parameter
917 | P_COMMIT IN Standard in parameter
918 | P_VALIDATION_LEVEL IN Standard in parameter
919 | P_BORROWER_PARTY_ID IN Inputs borrower party id
920 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
921 | X_MSG_COUNT OUT NOCOPY Standard out parameter
922 | X_MSG_DATA OUT NOCOPY Standard out parameter
923 |
924 | KNOWN ISSUES
925 | None
926 |
927 | NOTES
928 | Any interesting aspect of the code in the package body which needs
929 | to be stated.
930 |
931 | MODIFICATION HISTORY
932 | Date Author Description of Changes
933 | 04-14-2004 scherkas Created
934 |
935 *=======================================================================*/
936 PROCEDURE GENERATE_BORROWERS_SUMMARY(
937 P_API_VERSION IN NUMBER,
938 P_INIT_MSG_LIST IN VARCHAR2,
939 P_COMMIT IN VARCHAR2,
940 P_VALIDATION_LEVEL IN NUMBER,
941 P_BORROWER_PARTY_ID IN NUMBER,
942 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
943 X_MSG_COUNT OUT NOCOPY NUMBER,
944 X_MSG_DATA OUT NOCOPY VARCHAR2)
945 IS
946
947 /*-----------------------------------------------------------------------+
948 | Local Variable Declarations and initializations |
949 +-----------------------------------------------------------------------*/
950
951 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_BORROWERS_SUMMARY';
952 l_api_version CONSTANT NUMBER := 1.0;
953 l_return_status VARCHAR2(1);
954 l_msg_count NUMBER;
955 l_msg_data VARCHAR2(32767);
956 l_Count number;
957 l_Count1 number;
958 l_party_id number;
959 l_party_number VARCHAR2(30);
960 l_party_name VARCHAR2(360);
961
962 l_borrowers_tbl DBMS_SQL.NUMBER_TABLE;
963
964 /*-----------------------------------------------------------------------+
965 | Cursor Declarations |
966 +-----------------------------------------------------------------------*/
967
968 CURSOR borrowers_cur(P_BORROWER_ID number) IS
969 select
970 distinct party.party_id,
971 party.PARTY_NUMBER,
972 party.party_name
973 from
974 lns_participants par,
975 hz_parties party,
976 lns_loan_headers_all head
977 where
978 party.party_id = nvl(P_BORROWER_ID, party.party_id) and
979 party.party_id = par.hz_party_id and
980 par.loan_id = head.loan_id and
981 par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER', 'GUARANTOR');
982
983 BEGIN
984
985 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
986
987 -- Standard start of API savepoint
988 SAVEPOINT GENERATE_BORR_SUM;
989 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
990
991 -- Standard call to check for call compatibility
992 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995
996 -- Initialize message list if p_init_msg_list is set to TRUE
997 IF FND_API.To_Boolean(p_init_msg_list) THEN
998 FND_MSG_PUB.initialize;
999 END IF;
1000
1001 -- Initialize API return status to success
1002 l_return_status := FND_API.G_RET_STS_SUCCESS;
1003
1004 -- START OF BODY OF API
1005
1006 init;
1007
1008 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters:');
1009 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BORROWER_PARTY_ID: ' || P_BORROWER_PARTY_ID);
1010 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Query for all available borrowers...');
1011
1012 open borrowers_cur(P_BORROWER_PARTY_ID);
1013
1014 l_Count1 := 0;
1015
1016 LOOP
1017
1018 fetch borrowers_cur into
1019 l_party_id,
1020 l_party_number,
1021 l_party_name;
1022
1023 exit when borrowers_cur%NOTFOUND;
1024
1025 l_Count1 := l_Count1 + 1;
1026 l_borrowers_tbl(l_Count1) := l_party_id;
1027
1028 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1029 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Borrower #' || l_Count1);
1030 LogMessage(FND_LOG.LEVEL_STATEMENT, 'PARTY_ID: ' || l_borrowers_tbl(l_Count1));
1031 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NUMBER: ' || l_party_number);
1032 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NAME: ' || l_party_name);
1033
1034 END LOOP;
1035
1036 close borrowers_cur;
1037
1038 l_Count := l_borrowers_tbl.count;
1039 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1040 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total found ' || l_Count || ' borrowers');
1041
1042 if l_Count > 0 then
1043
1044 /* bill all selected loans */
1045 FOR l_Count1 IN 1..l_Count LOOP
1046
1047 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1048 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Processing borrower #' || l_Count1);
1049
1050 GEN_SINGLE_BORROWER_SUMMARY(
1051 P_API_VERSION => 1.0,
1052 P_INIT_MSG_LIST => FND_API.G_FALSE,
1053 P_COMMIT => P_COMMIT,
1054 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1055 P_BORROWER_PARTY_ID => l_borrowers_tbl(l_Count1),
1056 X_RETURN_STATUS => l_return_status,
1057 X_MSG_COUNT => l_msg_count,
1058 X_MSG_DATA => l_msg_data);
1059
1060 END LOOP;
1061
1062 END IF;
1063
1064 LogMessage(FND_LOG.LEVEL_STATEMENT, '______________');
1065 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total processed ' || l_Count || ' borrowers');
1066
1067 if P_COMMIT = FND_API.G_TRUE then
1068 COMMIT WORK;
1069 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1070 end if;
1071
1072 -- END OF BODY OF API
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074
1075 -- Standard call to get message count and if count is 1, get message info
1076 FND_MSG_PUB.Count_And_Get(
1077 p_encoded => FND_API.G_FALSE,
1078 p_count => x_msg_count,
1079 p_data => x_msg_data);
1080
1081 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1082
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 ROLLBACK TO GENERATE_BORR_SUM;
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1088 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 ROLLBACK TO GENERATE_BORR_SUM;
1091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1092 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1093 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1094 WHEN OTHERS THEN
1095 ROLLBACK TO GENERATE_BORR_SUM;
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1098 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1099 END IF;
1100 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1101 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1102 END;
1103
1104 BEGIN
1105 G_LOG_ENABLED := 'N';
1106 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1107
1108 /* getting msg logging info */
1109 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1110 /*
1111 if (G_LOG_ENABLED = 'N') then
1112 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1113 else
1114 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1115 end if;
1116 */
1117
1118 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1119 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1120
1121 END;