[Home] [Help]
PACKAGE BODY: APPS.LNS_IMPORT_LOAN_PUB
Source
1 PACKAGE BODY LNS_IMPORT_LOAN_PUB as
2 /* $Header: LNS_IMPORT_LOAN_B.pls 120.5 2011/01/06 14:02:21 scherkas noship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 g_pkg_name constant VARCHAR2(30) := 'LNS_IMPORT_LOAN_PUB';
9 g_log_enabled VARCHAR2(5);
10 g_msg_level NUMBER;
11 g_errors_rec Loan_create_errors_type := Loan_create_errors_type();
12 g_error_count number := 0;
13
14 PROCEDURE validate_pay_history(P_Loan_Details_Rec IN OUT nocopy lns_import_loan_pub.loan_details_rec_type
15 ,P_PAY_HIST_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE
16 , x_return_status OUT nocopy VARCHAR2
17 , x_msg_count OUT nocopy NUMBER
18 , x_msg_data OUT nocopy VARCHAR2);
19
20 PROCEDURE validate_disbursements(P_Loan_Details_Rec IN OUT nocopy lns_import_loan_pub.loan_details_rec_type
21 ,P_DISB_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Disb_Tbl_Type
22 , x_return_status OUT nocopy VARCHAR2
23 , x_msg_count OUT nocopy NUMBER
24 , x_msg_data OUT nocopy VARCHAR2);
25
26 /*========================================================================
27 | PRIVATE PROCEDURE LogMessage
28 |
29 | DESCRIPTION
30 | This procedure logs debug messages to db and to CM log
31 |
32 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
33 |
34 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
35 | None
36 |
37 | PARAMETERS
38 | p_msg_level IN Debug msg level
39 | p_msg IN Debug msg itself
40 |
41 | KNOWN ISSUES
42 | None
43 |
44 |
45 | NOTES
46 | Any interesting aspect of the code in the package body which needs
47 | to be stated.
48 |
49 | MODIFICATION HISTORY
50 | Date Author Description of Changes
51 | 17-Jan-2006 GBELLARY Created
52 |
53 *=======================================================================*/
54 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
55 IS
56 BEGIN
57 if (p_msg_level >= G_MSG_LEVEL) then
58
59 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
60
61 end if;
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
66 END;
67
68
69
70 /*========================================================================
71 | PRIVATE PROCEDURE LogErrors
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 | This procedure builds the error message and stores it (alongwith
91 | other columns in LNS_LOAN_API_ERRORS_GT) in g_errors_rec.
92 |
93 | MODIFICATION HISTORY
94 | Date Author Description of Changes
95 | 17-Jan-2006 GBELLARY Created
96 |
97 *=======================================================================*/
98 Procedure LogErrors( p_message_name IN VARCHAR2
99 ,p_line_number IN NUMBER DEFAULT NULL
100 ,p_token1 IN VARCHAR2 DEFAULT NULL
101 ,p_token2 IN VARCHAR2 DEFAULT NULL
102 ,p_token3 IN VARCHAR2 DEFAULT NULL)
103 IS
104 l_text LNS_LOAN_API_ERRORS_GT.MESSAGE_TEXT%TYPE;
105
106 BEGIN
107
108 fnd_message.set_name('LNS', p_message_name);
109
110 if p_token1 is NOT NULL THEN
111 fnd_message.set_token('TOKEN1',p_token1);
112 end if;
113
114 IF p_token2 is NOT NULL THEN
115 fnd_message.set_token('TOKEN2',p_token2);
116 END IF;
117
118 IF p_token3 is NOT NULL THEN
119 fnd_message.set_token('TOKEN3',p_token3);
120 END IF;
121
122 FND_MSG_PUB.Add;
123 l_text := substrb(fnd_message.get,1,2000);
124 g_error_count := g_error_count+1;
125 g_errors_rec.extend(1);
126 g_errors_rec(g_error_count).ERROR_NUMBER := g_error_count;
127 g_errors_rec(g_error_count).MESSAGE_NAME := p_message_name;
128 g_errors_rec(g_error_count).MESSAGE_TEXT := l_text;
129 g_errors_rec(g_error_count).LINE_NUMBER := p_line_number;
130 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || p_message_name || '(' || p_token1 || ',' || p_token2 || ',' || p_token3 || ') - ' || l_text);
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
135 END;
136
137
138
139 PROCEDURE validate_pay_history(P_Loan_Details_Rec IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Details_Rec_Type
140 ,P_PAY_HIST_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE
141 , x_return_status OUT nocopy VARCHAR2
142 , x_msg_count OUT nocopy NUMBER
143 , x_msg_data OUT nocopy VARCHAR2)
144 IS
145 l_api_name constant VARCHAR2(30) := 'VALIDATE_PAY_HISTORY';
146 i NUMBER;
147 j NUMBER;
148 l_temp LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_REC_TYPE;
149 l_tem_pay_hist_tbl LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE;
150
151 BEGIN
152 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
153
154 P_Loan_Details_Rec.next_payment_due_date := P_Loan_Details_Rec.first_payment_date;
155
156 l_tem_pay_hist_tbl := P_PAY_HIST_TBL;
157 FOR i IN 1 .. l_tem_pay_hist_tbl.COUNT LOOP
158
159 logmessage(fnd_log.level_statement, 'Payment record ' || i);
160 logmessage(fnd_log.level_statement, 'PAYMENT_NUMBER = ' || l_tem_pay_hist_tbl(i).PAYMENT_NUMBER);
161 logmessage(fnd_log.level_statement, 'DUE_DATE = ' || l_tem_pay_hist_tbl(i).DUE_DATE);
162 logmessage(fnd_log.level_statement, 'BILLED_PRIN = ' || l_tem_pay_hist_tbl(i).BILLED_PRIN);
163 logmessage(fnd_log.level_statement, 'BILLED_INT = ' || l_tem_pay_hist_tbl(i).BILLED_INT);
164 logmessage(fnd_log.level_statement, 'BILLED_FEE = ' || l_tem_pay_hist_tbl(i).BILLED_FEE);
165 logmessage(fnd_log.level_statement, 'SOURCE = ' || l_tem_pay_hist_tbl(i).SOURCE);
166 logmessage(fnd_log.level_statement, 'PAID_PRIN = ' || l_tem_pay_hist_tbl(i).PAID_PRIN);
167 logmessage(fnd_log.level_statement, 'PAID_INT = ' || l_tem_pay_hist_tbl(i).PAID_INT);
168 logmessage(fnd_log.level_statement, 'PAID_FEE = ' || l_tem_pay_hist_tbl(i).PAID_FEE);
169 logmessage(fnd_log.level_statement, 'PAID_DATE = ' || l_tem_pay_hist_tbl(i).PAID_DATE);
170 logmessage(fnd_log.level_statement, 'RC_ID = ' || l_tem_pay_hist_tbl(i).RC_ID);
171 logmessage(fnd_log.level_statement, 'RC_METHOD_ID = ' || l_tem_pay_hist_tbl(i).RC_METHOD_ID);
172
173 if l_tem_pay_hist_tbl(i).PAYMENT_NUMBER is null then
174 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
175 p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAYMENT_NUMBER');
176 end if;
177
178 if l_tem_pay_hist_tbl(i).PAYMENT_NUMBER < 0 then
179 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
180 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAYMENT_NUMBER'
181 , p_token2 => l_tem_pay_hist_tbl(i).PAYMENT_NUMBER);
182 end if;
183
184 if l_tem_pay_hist_tbl(i).DUE_DATE is null then
185 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
186 p_token1 => 'P_PAY_HIST_TBL(' || i || ').DUE_DATE');
187 end if;
188
189 if l_tem_pay_hist_tbl(i).DUE_DATE < P_Loan_Details_Rec.loan_start_date then
190 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
191 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').DUE_DATE'
192 , p_token2 => l_tem_pay_hist_tbl(i).DUE_DATE);
193 end if;
194
195 if l_tem_pay_hist_tbl(i).SOURCE is null then
196 l_tem_pay_hist_tbl(i).SOURCE := 'SCHEDULED';
197 end if;
198
199 if l_tem_pay_hist_tbl(i).BILLED_PRIN is null then
200 l_tem_pay_hist_tbl(i).BILLED_PRIN := 0;
201 end if;
202
203 if l_tem_pay_hist_tbl(i).BILLED_INT is null then
204 l_tem_pay_hist_tbl(i).BILLED_INT := 0;
205 end if;
206
207 if l_tem_pay_hist_tbl(i).BILLED_FEE is null then
208 l_tem_pay_hist_tbl(i).BILLED_FEE := 0;
209 end if;
210
211 if l_tem_pay_hist_tbl(i).BILLED_PRIN < 0 then
212 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
213 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').BILLED_PRIN'
214 , p_token2 => l_tem_pay_hist_tbl(i).BILLED_PRIN);
215 end if;
216
217 if l_tem_pay_hist_tbl(i).BILLED_INT < 0 then
218 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
219 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').BILLED_INT'
220 , p_token2 => l_tem_pay_hist_tbl(i).BILLED_INT);
221 end if;
222
223 if l_tem_pay_hist_tbl(i).BILLED_FEE < 0 then
224 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
225 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').BILLED_FEE'
226 , p_token2 => l_tem_pay_hist_tbl(i).BILLED_FEE);
227 end if;
228
229 if l_tem_pay_hist_tbl(i).SOURCE <> 'SCHEDULED' then
230 if l_tem_pay_hist_tbl(i).BILLED_INT > 0 then
231 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
232 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').BILLED_INT'
233 , p_token2 => l_tem_pay_hist_tbl(i).BILLED_INT);
234 elsif l_tem_pay_hist_tbl(i).BILLED_FEE > 0 then
235 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
236 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').BILLED_FEE'
237 , p_token2 => l_tem_pay_hist_tbl(i).BILLED_FEE);
238 end if;
239 end if;
240
241 if l_tem_pay_hist_tbl(i).PAID_PRIN is null then
242 l_tem_pay_hist_tbl(i).PAID_PRIN := 0;
243 end if;
244
245 if l_tem_pay_hist_tbl(i).PAID_INT is null then
246 l_tem_pay_hist_tbl(i).PAID_INT := 0;
247 end if;
248
249 if l_tem_pay_hist_tbl(i).PAID_FEE is null then
250 l_tem_pay_hist_tbl(i).PAID_FEE := 0;
251 end if;
252
253 if l_tem_pay_hist_tbl(i).PAID_PRIN < 0 then
254 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
255 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAID_PRIN'
256 , p_token2 => l_tem_pay_hist_tbl(i).PAID_PRIN);
257 end if;
258
259 if l_tem_pay_hist_tbl(i).PAID_INT < 0 then
260 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
261 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAID_INT'
262 , p_token2 => l_tem_pay_hist_tbl(i).PAID_INT);
263 end if;
264
265 if l_tem_pay_hist_tbl(i).PAID_FEE < 0 then
266 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
267 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAID_FEE'
268 , p_token2 => l_tem_pay_hist_tbl(i).PAID_FEE);
269 end if;
270
271 if l_tem_pay_hist_tbl(i).PAID_DATE is null then
272 l_tem_pay_hist_tbl(i).PAID_DATE := l_tem_pay_hist_tbl(i).DUE_DATE;
273 end if;
274
275 if l_tem_pay_hist_tbl(i).PAID_DATE < l_tem_pay_hist_tbl(i).DUE_DATE then
276 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
277 , p_token1 => 'P_PAY_HIST_TBL(' || i || ').PAID_DATE'
278 , p_token2 => l_tem_pay_hist_tbl(i).PAID_DATE);
279 end if;
280
281 IF l_tem_pay_hist_tbl(i).RC_ID IS NULL and
282 (l_tem_pay_hist_tbl(i).PAID_PRIN + l_tem_pay_hist_tbl(i).PAID_INT + l_tem_pay_hist_tbl(i).PAID_FEE) > 0
283 THEN
284 IF l_tem_pay_hist_tbl(i).RC_METHOD_ID IS NULL THEN
285 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
286 p_token1 => 'P_PAY_HIST_TBL(' || i || ').RC_METHOD_ID');
287 END IF;
288 END IF;
289
290 if l_tem_pay_hist_tbl(i).SOURCE = 'SCHEDULED' and
291 l_tem_pay_hist_tbl(i).DUE_DATE > P_Loan_Details_Rec.next_payment_due_date then
292 P_Loan_Details_Rec.next_payment_due_date := l_tem_pay_hist_tbl(i).DUE_DATE;
293 end if;
294
295 END LOOP;
296
297 -- sort table
298 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Sorting by payment number...');
299 for i in REVERSE 1..l_tem_pay_hist_tbl.count loop
300 for j in 1..(i-1) loop
301 if l_tem_pay_hist_tbl(j).PAYMENT_NUMBER > l_tem_pay_hist_tbl(j+1).PAYMENT_NUMBER or
302 (l_tem_pay_hist_tbl(j).PAYMENT_NUMBER = l_tem_pay_hist_tbl(j+1).PAYMENT_NUMBER and
303 l_tem_pay_hist_tbl(j).SOURCE <> 'SCHEDULED' and l_tem_pay_hist_tbl(j+1).SOURCE = 'SCHEDULED') then
304
305 l_temp := l_tem_pay_hist_tbl(j);
306 l_tem_pay_hist_tbl(j) := l_tem_pay_hist_tbl(j+1);
307 l_tem_pay_hist_tbl(j+1) := l_temp;
308
309 elsif l_tem_pay_hist_tbl(j).PAYMENT_NUMBER = l_tem_pay_hist_tbl(j+1).PAYMENT_NUMBER and
310 l_tem_pay_hist_tbl(j).SOURCE = 'SCHEDULED' and l_tem_pay_hist_tbl(j+1).SOURCE = 'SCHEDULED' then
311
312 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
313 , p_token1 => 'P_PAY_HIST_TBL(' || (j+1) || ').SOURCE'
314 , p_token2 => l_tem_pay_hist_tbl(j+1).SOURCE);
315
316 end if;
317 end loop;
318 end loop;
319 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done sorting.');
320
321 IF g_error_count > 0 THEN
322 RAISE fnd_api.g_exc_error;
323 END IF;
324
325 P_PAY_HIST_TBL := l_tem_pay_hist_tbl;
326
327 -- END OF BODY OF API
328 x_return_status := fnd_api.g_ret_sts_success;
329
330 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
331
332 EXCEPTION
333 WHEN fnd_api.g_exc_error THEN
334 x_return_status := fnd_api.g_ret_sts_error;
335 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
336 WHEN fnd_api.g_exc_unexpected_error THEN
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
339 WHEN others THEN
340 x_return_status := fnd_api.g_ret_sts_unexp_error;
341 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
342 x_msg_count := 1;
343 x_msg_data := sqlerrm;
344
345 END;
346
347
348
349 PROCEDURE validate_disbursements(P_Loan_Details_Rec IN OUT nocopy lns_import_loan_pub.loan_details_rec_type
350 ,P_DISB_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Disb_Tbl_Type
351 , x_return_status OUT nocopy VARCHAR2
352 , x_msg_count OUT nocopy NUMBER
353 , x_msg_data OUT nocopy VARCHAR2)
354 IS
355 l_api_name constant VARCHAR2(30) := 'validate_disbursements';
356 l_funded_amount NUMBER;
357 l_dummy VARCHAR2(30);
358
359 BEGIN
360 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
361
362 l_funded_amount := 0;
363 FOR i IN 1 .. P_DISB_TBL.COUNT LOOP
364
365 logmessage(fnd_log.level_statement, 'Disbursement record ' || i);
366 logmessage(fnd_log.level_statement, 'ACTIVITY_CODE = ' || P_DISB_TBL(i).ACTIVITY_CODE);
367 logmessage(fnd_log.level_statement, 'DESCRIPTION = ' || P_DISB_TBL(i).DESCRIPTION);
368 logmessage(fnd_log.level_statement, 'AMOUNT = ' || P_DISB_TBL(i).AMOUNT);
369 logmessage(fnd_log.level_statement, 'DUE_DATE = ' || P_DISB_TBL(i).DUE_DATE);
370 logmessage(fnd_log.level_statement, 'SUBMISSION_DATE = ' || P_DISB_TBL(i).SUBMISSION_DATE);
371 logmessage(fnd_log.level_statement, 'DISBURSEMENT_DATE = ' || P_DISB_TBL(i).DISBURSEMENT_DATE);
372 logmessage(fnd_log.level_statement, 'PAYEE_PARTY_ID = ' || P_DISB_TBL(i).PAYEE_PARTY_ID);
373 logmessage(fnd_log.level_statement, 'BANK_ACCOUNT_ID = ' || P_DISB_TBL(i).BANK_ACCOUNT_ID);
374 logmessage(fnd_log.level_statement, 'PAYMENT_METHOD_CODE = ' || P_DISB_TBL(i).PAYMENT_METHOD_CODE);
375
376 if P_DISB_TBL(i).ACTIVITY_CODE is null and P_DISB_TBL(i).DESCRIPTION is null then
377 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
378 p_token1 => 'P_DISB_TBL(' || i || ').ACTIVITY_CODE, P_DISB_TBL(' || i || ').DESCRIPTION');
379 end if;
380
381 if P_DISB_TBL(i).AMOUNT is null then
382 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
383 p_token1 => 'P_DISB_TBL(' || i || ').AMOUNT');
384 end if;
385
386 if P_DISB_TBL(i).AMOUNT < 0 or P_DISB_TBL(i).AMOUNT > P_Loan_Details_Rec.requested_amount then
387 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
388 , p_token1 => 'P_DISB_TBL(' || i || ').AMOUNT'
389 , p_token2 => P_DISB_TBL(i).AMOUNT);
390 end if;
391
392 if P_DISB_TBL(i).DUE_DATE is null then
393 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
394 p_token1 => 'P_DISB_TBL(' || i || ').DUE_DATE');
395 end if;
396
397 if P_DISB_TBL(i).DUE_DATE < P_Loan_Details_Rec.loan_start_date or
398 P_DISB_TBL(i).DUE_DATE >= P_Loan_Details_Rec.maturity_date
399 then
400 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
401 , p_token1 => 'P_DISB_TBL(' || i || ').DUE_DATE'
402 , p_token2 => P_DISB_TBL(i).DUE_DATE);
403 end if;
404
405 if P_DISB_TBL(i).PAYEE_PARTY_ID is null then
406 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
407 p_token1 => 'P_DISB_TBL(' || i || ').PAYEE_PARTY_ID');
408 else
409 BEGIN
410 SELECT 'Y'
411 INTO l_dummy
412 FROM hz_parties hzp
413 WHERE hzp.party_id = P_DISB_TBL(i).PAYEE_PARTY_ID
414 AND hzp.status = 'A';
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
418 ,p_token1=>'P_DISB_TBL(' || i || ').PAYEE_PARTY_ID'
419 ,p_token2=>P_DISB_TBL(i).PAYEE_PARTY_ID);
420 END;
421 end if;
422
423 if P_DISB_TBL(i).DISBURSEMENT_DATE is not null then
424
425 if (P_DISB_TBL(i).DISBURSEMENT_DATE < P_Loan_Details_Rec.loan_start_date or
426 P_DISB_TBL(i).DISBURSEMENT_DATE >= P_Loan_Details_Rec.maturity_date)
427 then
428 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE'
429 , p_token1 => 'P_DISB_TBL(' || i || ').DISBURSEMENT_DATE'
430 , p_token2 => P_DISB_TBL(i).DISBURSEMENT_DATE);
431 end if;
432
433 if P_DISB_TBL(i).SUBMISSION_DATE is null then
434 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
435 p_token1 => 'P_DISB_TBL(' || i || ').SUBMISSION_DATE');
436 end if;
437
438 if P_DISB_TBL(i).PAYMENT_METHOD_CODE is null then
439 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
440 p_token1 => 'P_DISB_TBL(' || i || ').PAYMENT_METHOD_CODE');
441 end if;
442
443 l_funded_amount := l_funded_amount + P_DISB_TBL(i).AMOUNT;
444
445 end if;
446
447 END LOOP;
448
449 P_Loan_Details_Rec.funded_amount := l_funded_amount;
450
451 IF g_error_count > 0 THEN
452 RAISE fnd_api.g_exc_error;
453 END IF;
454
455 -- END OF BODY OF API
456 x_return_status := fnd_api.g_ret_sts_success;
457
458 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' -');
459
460 EXCEPTION
461 WHEN fnd_api.g_exc_error THEN
462 x_return_status := fnd_api.g_ret_sts_error;
463 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
464 WHEN fnd_api.g_exc_unexpected_error THEN
465 x_return_status := fnd_api.g_ret_sts_unexp_error;
466 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
467 WHEN others THEN
468 x_return_status := fnd_api.g_ret_sts_unexp_error;
469 logerrors('Exception at '||g_pkg_name || '.' || l_api_name ||' is '||sqlerrm);
470 x_msg_count := 1;
471 x_msg_data := sqlerrm;
472
473 END;
474
475
476 /*========================================================================
477 | PUBLIC PROCEDURE IMPORT_LOAN
478 |
479 | DESCRIPTION
480 | This procedure imports single loan.
481 |
482 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
483 |
484 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
485 | None
486 |
487 | PARAMETERS
488 | p_msg_level IN Debug msg level
489 | p_msg IN Debug msg itself
490 |
491 | KNOWN ISSUES
492 | None
493 |
494 | NOTES
495 | Any interesting aspect of the code in the package body which needs
496 | to be stated.
497 |
498 | MODIFICATION HISTORY
499 | Date Author Description of Changes
500 | 07-17-2007 scherkas Created
501 |
502 *=======================================================================*/
503
504 PROCEDURE IMPORT_LOAN(
505 P_API_VERSION IN NUMBER,
506 P_INIT_MSG_LIST IN VARCHAR2,
507 P_COMMIT IN VARCHAR2,
508 P_VALIDATION_LEVEL IN NUMBER,
509 P_Loan_Details_Rec IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Details_Rec_Type, -- mandatory
510 P_Loan_Lines_Tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Lines_Tbl_Type, -- mandatory for ERS loans
511 P_DISB_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Disb_Tbl_Type, -- mandatory for direct loans
512 P_LOAN_PART_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_PART_TBL_TYPE, -- optional
513 P_LOAN_RATES_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- mandatory
514 p_loan_cust_sched_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.loan_cust_sched_tbl_type, -- optional
515 p_distribution_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.distribution_tbl, -- optional
516 P_PAY_HIST_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE, -- optional
517 X_LOAN_ID OUT NOCOPY NUMBER,
518 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
519 X_MSG_COUNT OUT NOCOPY NUMBER,
520 X_MSG_DATA OUT NOCOPY VARCHAR2)
521 IS
522
523 /*-----------------------------------------------------------------------+
524 | Local Variable Declarations and initializations |
525 +-----------------------------------------------------------------------*/
526 l_api_name constant VARCHAR2(30) := 'IMPORT_LOAN';
527 l_api_version constant NUMBER := 1.0;
528 l_return_status VARCHAR2(1);
529 l_msg_count NUMBER;
530 l_msg_data VARCHAR2(32767);
531
532 l_loan_id NUMBER;
533 l_loan_number VARCHAR2(60);
534 l_index number := 0;
535 l_indexNo number := 1;
536 l_msg varchar2(4000) := null;
537 l_dummy VARCHAR2(30);
538 l_term_id NUMBER;
539 l_version_number NUMBER;
540 l_lines_count number;
541 l_paid_total NUMBER;
542 l_cr_id NUMBER;
543 l_principal_trx_id NUMBER;
544 l_interest_trx_id NUMBER;
545 l_fee_trx_id NUMBER;
546 i number;
547 l_receipt_number varchar2(30);
548 l_loan_class varchar2(30);
549
550 l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
551 l_LOAN_LINES_TBL LNS_LOAN_PUB.Loan_Lines_Tbl_Type;
552 l_LOAN_PART_TBL LNS_LOAN_PUB.LOAN_PART_TBL_TYPE;
553 l_LOAN_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
554 l_term_rec lns_terms_pub.loan_term_rec_type;
555 l_bill_headers_tbl lns_billing_batch_pub.bill_headers_tbl;
556 l_bill_lines_tbl lns_billing_batch_pub.bill_lines_tbl;
557 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
558 l_OPEN_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
559 l_loan_cust_sched_tbl LNS_LOAN_PUB.loan_cust_sched_tbl_type;
560 l_LOAN_RATES_REC LNS_IMPORT_LOAN_PUB.LOAN_RATES_REC_TYPE;
561 l_distribution_tbl lns_distributions_pub.distribution_tbl;
562 l_DISB_HEADER_REC LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC;
563 l_DISB_LINE_REC LNS_FUNDING_PUB.LNS_DISB_LINES_REC;
564 l_DISB_TBL LNS_LOAN_PUB.Loan_Disb_Tbl_Type;
565
566 /*-----------------------------------------------------------------------+
567 | Cursor Declarations |
568 +-----------------------------------------------------------------------*/
569
570 CURSOR c_get_rate_sch_info(termId NUMBER) IS
571 SELECT
572 begin_installment_number,
573 end_installment_number,
574 index_date,
575 index_rate,
576 spread,
577 INTEREST_ONLY_FLAG
578 FROM lns_rate_schedules
579 WHERE end_date_active IS NULL
580 AND term_id = termId
581 AND PHASE = 'TERM'
582 order by begin_installment_number;
583
584 BEGIN
585
586 logmessage(fnd_log.level_procedure, g_pkg_name || '.' || l_api_name || ' +');
587
588 -- Standard start of API savepoint
589 SAVEPOINT import_loan;
590 logmessage(fnd_log.level_statement, 'Savepoint is established');
591
592 -- Standard call to check for call compatibility
593
594 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
595 RAISE fnd_api.g_exc_unexpected_error;
596 END IF;
597
598 -- Initialize message list if p_init_msg_list is set to TRUE
599 IF fnd_api.to_boolean(p_init_msg_list) THEN
600 fnd_msg_pub.initialize;
601 END IF;
602
603 -- Initialize API return status to success
604 l_return_status := fnd_api.g_ret_sts_success;
605
606 -- START OF BODY OF API
607
608 -- Initialize Collections and Variables
609 g_errors_rec.delete;
610 g_error_count := 0;
611
612 BEGIN
613 SELECT loan_type.loan_class_code
614 INTO l_loan_class
615 from lns_loan_types loan_type,
616 lns_loan_products_all loan_product
617 where loan_product.loan_product_id = P_Loan_Details_Rec.product_id AND
618 loan_type.loan_type_id = loan_product.loan_type_id ;
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
622 p_token1 => 'P_Loan_Details_Rec.product_id',
623 p_token2 => P_Loan_Details_Rec.product_id);
624 RAISE fnd_api.g_exc_error;
625 END;
626
627 /*
628 logmessage(fnd_log.level_statement, 'Validating product...');
629 BEGIN
630 SELECT 'Y'
631 INTO l_dummy
632 from lns_loan_types loan_type,
633 lns_loan_products_all loan_product
634 where loan_product.loan_product_id = P_Loan_Details_Rec.product_id AND
635 loan_type.loan_type_id = loan_product.loan_type_id AND
636 loan_type.loan_class_code = 'ERS';
637 EXCEPTION
638 WHEN NO_DATA_FOUND THEN
639 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
640 p_token1 => 'P_Loan_Details_Rec.product_id',
641 p_token2 => P_Loan_Details_Rec.product_id);
642 RAISE fnd_api.g_exc_error;
643 END;
644 */
645 logmessage(fnd_log.level_statement, 'Preparing P_Loan_Details_Rec data...');
646
647 -- common attributes
648 l_LOAN_DTL_REC.product_id := P_Loan_Details_Rec.product_id;
649 l_LOAN_DTL_REC.LOAN_NUMBER := P_Loan_Details_Rec.LOAN_NUMBER;
650 l_LOAN_DTL_REC.LOAN_DESCRIPTION := P_Loan_Details_Rec.LOAN_DESCRIPTION;
651 l_LOAN_DTL_REC.LOAN_ASSIGNED_TO := P_Loan_Details_Rec.LOAN_ASSIGNED_TO;
652 l_LOAN_DTL_REC.legal_entity_id := P_Loan_Details_Rec.legal_entity_id;
653 l_LOAN_DTL_REC.requested_amount := P_Loan_Details_Rec.requested_amount;
654 l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := P_Loan_Details_Rec.LOAN_APPLICATION_DATE;
655 l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := P_Loan_Details_Rec.EXCHANGE_RATE_TYPE;
656 l_LOAN_DTL_REC.EXCHANGE_DATE := P_Loan_Details_Rec.EXCHANGE_DATE;
657 l_LOAN_DTL_REC.EXCHANGE_RATE := P_Loan_Details_Rec.EXCHANGE_RATE;
658 l_LOAN_DTL_REC.LOAN_PURPOSE_CODE := P_Loan_Details_Rec.LOAN_PURPOSE_CODE;
659 l_LOAN_DTL_REC.LOAN_SUBTYPE := P_Loan_Details_Rec.LOAN_SUBTYPE;
660 l_LOAN_DTL_REC.credit_review_flag := P_Loan_Details_Rec.credit_review_flag;
661 l_LOAN_DTL_REC.trx_type_id := P_Loan_Details_Rec.trx_type_id;
662 l_LOAN_DTL_REC.COLLATERAL_PERCENT := P_Loan_Details_Rec.COLLATERAL_PERCENT;
663 l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := P_Loan_Details_Rec.CUSTOM_PAYMENTS_FLAG;
664 l_LOAN_DTL_REC.FORGIVENESS_FLAG := P_Loan_Details_Rec.FORGIVENESS_FLAG;
665 l_LOAN_DTL_REC.FORGIVENESS_PERCENT := P_Loan_Details_Rec.FORGIVENESS_PERCENT;
666
667 -- primary borrower attributes
668 l_LOAN_DTL_REC.primary_borrower_party_id := P_Loan_Details_Rec.primary_borrower_party_id;
669 l_LOAN_DTL_REC.CUST_ACCOUNT_ID := P_Loan_Details_Rec.CUST_ACCOUNT_ID;
670 l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := P_Loan_Details_Rec.BILL_TO_ACCT_SITE_ID;
671 l_LOAN_DTL_REC.contact_rel_party_id := P_Loan_Details_Rec.contact_rel_party_id;
672 l_LOAN_DTL_REC.CONTACT_PERS_PARTY_ID := P_Loan_Details_Rec.CONTACT_PERS_PARTY_ID;
673
674 -- common term attributes
675 l_LOAN_DTL_REC.RATE_TYPE := P_Loan_Details_Rec.RATE_TYPE;
676 l_LOAN_DTL_REC.INDEX_RATE_ID := P_Loan_Details_Rec.INDEX_RATE_ID;
677 l_LOAN_DTL_REC.DAY_COUNT_METHOD := P_Loan_Details_Rec.DAY_COUNT_METHOD;
678 l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := P_Loan_Details_Rec.LOAN_PAYMENT_FREQUENCY;
679 l_LOAN_DTL_REC.CALCULATION_METHOD := P_Loan_Details_Rec.CALCULATION_METHOD;
680 l_LOAN_DTL_REC.INTEREST_COMPOUNDING_FREQ := P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ;
681 l_LOAN_DTL_REC.PAYMENT_CALC_METHOD := P_Loan_Details_Rec.PAYMENT_CALC_METHOD;
682 l_LOAN_DTL_REC.CUSTOM_CALC_METHOD := P_Loan_Details_Rec.CUSTOM_CALC_METHOD;
683 l_LOAN_DTL_REC.ORIG_PAY_CALC_METHOD := P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD;
684 l_LOAN_DTL_REC.PENAL_INT_RATE := P_Loan_Details_Rec.PENAL_INT_RATE;
685 l_LOAN_DTL_REC.PENAL_INT_GRACE_DAYS := P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS;
686 l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_PRIN := P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN;
687 l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_INT := P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT;
688 l_LOAN_DTL_REC.LOCK_DATE := P_Loan_Details_Rec.LOCK_DATE;
689 l_LOAN_DTL_REC.LOCK_EXP_DATE := P_Loan_Details_Rec.LOCK_EXP_DATE;
690
691 -- 'term phase' term attributes
692 l_LOAN_DTL_REC.LOAN_TERM := P_Loan_Details_Rec.LOAN_TERM;
693 l_LOAN_DTL_REC.LOAN_TERM_PERIOD := P_Loan_Details_Rec.LOAN_TERM_PERIOD;
694 l_LOAN_DTL_REC.balloon_payment_type := P_Loan_Details_Rec.balloon_payment_type;
695 l_LOAN_DTL_REC.balloon_payment_amount := P_Loan_Details_Rec.balloon_payment_amount;
696 l_LOAN_DTL_REC.balloon_term := P_Loan_Details_Rec.balloon_term;
697 l_LOAN_DTL_REC.LOAN_START_DATE := P_Loan_Details_Rec.LOAN_START_DATE;
698 l_LOAN_DTL_REC.FIRST_PAYMENT_DATE := P_Loan_Details_Rec.FIRST_PAYMENT_DATE;
699 l_LOAN_DTL_REC.PRIN_FIRST_PAY_DATE := P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE;
700 l_LOAN_DTL_REC.PRIN_PAYMENT_FREQUENCY := P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY;
701 l_LOAN_DTL_REC.floor_rate := P_Loan_Details_Rec.floor_rate;
702 l_LOAN_DTL_REC.ceiling_rate := P_Loan_Details_Rec.ceiling_rate;
703 l_LOAN_DTL_REC.percent_increase := P_Loan_Details_Rec.percent_increase;
704 l_LOAN_DTL_REC.percent_increase_life := P_Loan_Details_Rec.percent_increase_life;
705
706 -- dont pass any of open phase parameters
707
708 -- Terms for Payment attributes
709 l_LOAN_DTL_REC.REAMORTIZE_OVER_PAYMENT := P_Loan_Details_Rec.REAMORTIZE_OVER_PAYMENT;
710 l_LOAN_DTL_REC.DELINQUENCY_THRESHOLD_AMOUNT := P_Loan_Details_Rec.DELINQUENCY_THRESHOLD_AMOUNT;
711 l_LOAN_DTL_REC.PAYMENT_APPLICATION_ORDER := P_Loan_Details_Rec.PAYMENT_APPLICATION_ORDER;
712 l_LOAN_DTL_REC.PMT_APPL_ORDER_SCOPE := P_Loan_Details_Rec.PMT_APPL_ORDER_SCOPE;
713 l_LOAN_DTL_REC.REAMORTIZE_ON_FUNDING := P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING;
714
715 -- additional optional attributes
716 l_LOAN_DTL_REC.ATTRIBUTE_CATEGORY := P_Loan_Details_Rec.ATTRIBUTE_CATEGORY;
717 l_LOAN_DTL_REC.ATTRIBUTE1 := P_Loan_Details_Rec.ATTRIBUTE1;
718 l_LOAN_DTL_REC.ATTRIBUTE2 := P_Loan_Details_Rec.ATTRIBUTE2;
719 l_LOAN_DTL_REC.ATTRIBUTE3 := P_Loan_Details_Rec.ATTRIBUTE3;
720 l_LOAN_DTL_REC.ATTRIBUTE4 := P_Loan_Details_Rec.ATTRIBUTE4;
721 l_LOAN_DTL_REC.ATTRIBUTE5 := P_Loan_Details_Rec.ATTRIBUTE5;
722 l_LOAN_DTL_REC.ATTRIBUTE6 := P_Loan_Details_Rec.ATTRIBUTE6;
723 l_LOAN_DTL_REC.ATTRIBUTE7 := P_Loan_Details_Rec.ATTRIBUTE7;
724 l_LOAN_DTL_REC.ATTRIBUTE8 := P_Loan_Details_Rec.ATTRIBUTE8;
725 l_LOAN_DTL_REC.ATTRIBUTE9 := P_Loan_Details_Rec.ATTRIBUTE9;
726 l_LOAN_DTL_REC.ATTRIBUTE10 := P_Loan_Details_Rec.ATTRIBUTE10;
727 l_LOAN_DTL_REC.ATTRIBUTE11 := P_Loan_Details_Rec.ATTRIBUTE11;
728 l_LOAN_DTL_REC.ATTRIBUTE12 := P_Loan_Details_Rec.ATTRIBUTE12;
729 l_LOAN_DTL_REC.ATTRIBUTE13 := P_Loan_Details_Rec.ATTRIBUTE13;
730 l_LOAN_DTL_REC.ATTRIBUTE14 := P_Loan_Details_Rec.ATTRIBUTE14;
731 l_LOAN_DTL_REC.ATTRIBUTE15 := P_Loan_Details_Rec.ATTRIBUTE15;
732 l_LOAN_DTL_REC.ATTRIBUTE16 := P_Loan_Details_Rec.ATTRIBUTE16;
733 l_LOAN_DTL_REC.ATTRIBUTE17 := P_Loan_Details_Rec.ATTRIBUTE17;
734 l_LOAN_DTL_REC.ATTRIBUTE18 := P_Loan_Details_Rec.ATTRIBUTE18;
735 l_LOAN_DTL_REC.ATTRIBUTE19 := P_Loan_Details_Rec.ATTRIBUTE19;
736 l_LOAN_DTL_REC.ATTRIBUTE20 := P_Loan_Details_Rec.ATTRIBUTE20;
737
738 P_Loan_Details_Rec.funded_amount := 0;
739 if l_loan_class = 'ERS' then
740 -- copy loan lines
741 logmessage(fnd_log.level_statement, 'Preparing p_loan_lines_tbl data...');
742 FOR i IN 1 .. p_loan_lines_tbl.count LOOP
743
744 l_LOAN_LINES_TBL(i).line_number := i;
745 l_LOAN_LINES_TBL(i).payment_schedule_id := -1;
746 l_LOAN_LINES_TBL(i).requested_amount := P_LOAN_LINES_TBL(i).amount;
747 l_LOAN_LINES_TBL(i).REFERENCE_DESCRIPTION := P_LOAN_LINES_TBL(i).REFERENCE_DESCRIPTION;
748 l_LOAN_LINES_TBL(i).REFERENCE_NUMBER := P_LOAN_LINES_TBL(i).REFERENCE_NUMBER;
749 P_Loan_Details_Rec.funded_amount := P_Loan_Details_Rec.funded_amount + l_LOAN_LINES_TBL(i).requested_amount;
750
751 END LOOP;
752 end if;
753
754 -- copy loan participants
755 logmessage(fnd_log.level_statement, 'Preparing p_loan_part_tbl data...');
756 FOR i IN 1 .. p_loan_part_tbl.count LOOP
757
758 l_LOAN_PART_TBL(i).HZ_PARTY_ID := P_LOAN_PART_TBL(i).HZ_PARTY_ID;
759 l_LOAN_PART_TBL(i).LOAN_PARTICIPANT_TYPE := P_LOAN_PART_TBL(i).LOAN_PARTICIPANT_TYPE;
760 l_LOAN_PART_TBL(i).START_DATE_ACTIVE := P_LOAN_PART_TBL(i).START_DATE_ACTIVE;
761 l_LOAN_PART_TBL(i).END_DATE_ACTIVE := P_LOAN_PART_TBL(i).END_DATE_ACTIVE;
762 l_LOAN_PART_TBL(i).CUST_ACCOUNT_ID := P_LOAN_PART_TBL(i).CUST_ACCOUNT_ID;
763 l_LOAN_PART_TBL(i).BILL_TO_ACCT_SITE_ID := P_LOAN_PART_TBL(i).BILL_TO_ACCT_SITE_ID;
764 l_LOAN_PART_TBL(i).CONTACT_PERS_PARTY_ID := P_LOAN_PART_TBL(i).CONTACT_PERS_PARTY_ID;
765 l_LOAN_PART_TBL(i).CONTACT_REL_PARTY_ID := P_LOAN_PART_TBL(i).CONTACT_REL_PARTY_ID;
766
767 END LOOP;
768
769 -- copy rate schedule
770 logmessage(fnd_log.level_statement, 'Preparing p_loan_rates_tbl data...');
771 FOR i IN 1 .. p_loan_rates_tbl.count LOOP
772
773 l_LOAN_RATES_TBL(i).INDEX_RATE := p_loan_rates_tbl(i).INDEX_RATE;
774 l_LOAN_RATES_TBL(i).SPREAD := p_loan_rates_tbl(i).SPREAD;
775 l_LOAN_RATES_TBL(i).INDEX_DATE := p_loan_rates_tbl(i).INDEX_DATE;
776 l_LOAN_RATES_TBL(i).BEGIN_INSTALLMENT_NUMBER := p_loan_rates_tbl(i).BEGIN_INSTALLMENT_NUMBER;
777 l_LOAN_RATES_TBL(i).END_INSTALLMENT_NUMBER := p_loan_rates_tbl(i).END_INSTALLMENT_NUMBER;
778 l_LOAN_RATES_TBL(i).INTEREST_ONLY_FLAG := p_loan_rates_tbl(i).INTEREST_ONLY_FLAG;
779
780 END LOOP;
781
782 -- copy custom schedule
783 logmessage(fnd_log.level_statement, 'Preparing p_loan_cust_sched_tbl data...');
784 FOR i IN 1 .. p_loan_cust_sched_tbl.count LOOP
785
786 l_loan_cust_sched_tbl(i).DUE_DATE := p_loan_cust_sched_tbl(i).DUE_DATE;
787 l_loan_cust_sched_tbl(i).RELATIVE_DATE := p_loan_cust_sched_tbl(i).RELATIVE_DATE;
788 l_loan_cust_sched_tbl(i).PRINCIPAL_AMOUNT := p_loan_cust_sched_tbl(i).PRINCIPAL_AMOUNT;
789 l_loan_cust_sched_tbl(i).PRINCIPAL_PERCENT := p_loan_cust_sched_tbl(i).PRINCIPAL_PERCENT;
790 l_loan_cust_sched_tbl(i).INTEREST_AMOUNT := p_loan_cust_sched_tbl(i).INTEREST_AMOUNT;
791 l_loan_cust_sched_tbl(i).LOCK_PRIN := p_loan_cust_sched_tbl(i).LOCK_PRIN;
792 l_loan_cust_sched_tbl(i).LOCK_INT := p_loan_cust_sched_tbl(i).LOCK_INT;
793
794 END LOOP;
795
796 logmessage(fnd_log.level_statement, 'Calling LNS_LOAN_PUB.CREATE_LOAN...');
797 LNS_LOAN_PUB.CREATE_LOAN(
798 P_API_VERSION => 1.0,
799 P_INIT_MSG_LIST => FND_API.G_TRUE,
800 P_COMMIT => FND_API.G_FALSE,
801 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
802 P_Loan_Details_Rec => l_LOAN_DTL_REC,
803 P_Loan_Lines_Tbl => l_LOAN_LINES_TBL,
804 P_DISB_TBL => l_DISB_TBL,
805 P_LOAN_PART_TBL => l_LOAN_PART_TBL,
806 P_OPEN_RATES_TBL => l_OPEN_RATES_TBL,
807 P_TERM_RATES_TBL => l_LOAN_RATES_TBL,
808 p_loan_cust_sched_tbl => l_loan_cust_sched_tbl,
809 P_Application_id => 206,
810 P_Created_by_module => g_pkg_name || '.' || l_api_name,
811 X_LOAN_ID => l_loan_id,
812 X_RETURN_STATUS => l_return_status,
813 X_MSG_COUNT => l_msg_count,
814 X_MSG_DATA => l_msg_data);
815
816 IF l_return_status <> 'S' THEN
817 RAISE fnd_api.g_exc_error;
818 END IF;
819
820 logmessage(fnd_log.level_unexpected, 'Loan object created successfully. New loan_id = ' || l_loan_id);
821
822 -- copy loan details data back
823 -- common attributes
824 P_Loan_Details_Rec.product_id := l_LOAN_DTL_REC.product_id;
825 P_Loan_Details_Rec.LOAN_NUMBER := l_LOAN_DTL_REC.LOAN_NUMBER;
826 P_Loan_Details_Rec.LOAN_DESCRIPTION := l_LOAN_DTL_REC.LOAN_DESCRIPTION;
827 P_Loan_Details_Rec.LOAN_ASSIGNED_TO := l_LOAN_DTL_REC.LOAN_ASSIGNED_TO;
828 P_Loan_Details_Rec.legal_entity_id := l_LOAN_DTL_REC.legal_entity_id;
829 -- P_Loan_Details_Rec.requested_amount := null;
830 P_Loan_Details_Rec.LOAN_APPLICATION_DATE := l_LOAN_DTL_REC.LOAN_APPLICATION_DATE;
831 P_Loan_Details_Rec.EXCHANGE_RATE_TYPE := l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE;
832 P_Loan_Details_Rec.EXCHANGE_DATE := l_LOAN_DTL_REC.EXCHANGE_DATE;
833 P_Loan_Details_Rec.EXCHANGE_RATE := l_LOAN_DTL_REC.EXCHANGE_RATE;
834 P_Loan_Details_Rec.LOAN_PURPOSE_CODE := l_LOAN_DTL_REC.LOAN_PURPOSE_CODE;
835 P_Loan_Details_Rec.LOAN_SUBTYPE := l_LOAN_DTL_REC.LOAN_SUBTYPE;
836 P_Loan_Details_Rec.credit_review_flag := l_LOAN_DTL_REC.credit_review_flag;
837 P_Loan_Details_Rec.trx_type_id := l_LOAN_DTL_REC.trx_type_id;
838 P_Loan_Details_Rec.COLLATERAL_PERCENT := l_LOAN_DTL_REC.COLLATERAL_PERCENT;
839 P_Loan_Details_Rec.CUSTOM_PAYMENTS_FLAG := l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG;
840 P_Loan_Details_Rec.FORGIVENESS_FLAG := l_LOAN_DTL_REC.FORGIVENESS_FLAG;
841 P_Loan_Details_Rec.FORGIVENESS_PERCENT := l_LOAN_DTL_REC.FORGIVENESS_PERCENT;
842
843 -- primary borrower attributes
844 P_Loan_Details_Rec.primary_borrower_party_id := l_LOAN_DTL_REC.primary_borrower_party_id;
845 P_Loan_Details_Rec.CUST_ACCOUNT_ID := l_LOAN_DTL_REC.CUST_ACCOUNT_ID;
846 P_Loan_Details_Rec.BILL_TO_ACCT_SITE_ID := l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID;
847 P_Loan_Details_Rec.contact_rel_party_id := l_LOAN_DTL_REC.contact_rel_party_id;
848 P_Loan_Details_Rec.CONTACT_PERS_PARTY_ID := l_LOAN_DTL_REC.CONTACT_PERS_PARTY_ID;
849
850 -- common term attributes
851 P_Loan_Details_Rec.RATE_TYPE := l_LOAN_DTL_REC.RATE_TYPE;
852 P_Loan_Details_Rec.INDEX_RATE_ID := l_LOAN_DTL_REC.INDEX_RATE_ID;
853 P_Loan_Details_Rec.DAY_COUNT_METHOD := l_LOAN_DTL_REC.DAY_COUNT_METHOD;
854 P_Loan_Details_Rec.LOAN_PAYMENT_FREQUENCY := l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY;
855 P_Loan_Details_Rec.CALCULATION_METHOD := l_LOAN_DTL_REC.CALCULATION_METHOD;
856 P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ := l_LOAN_DTL_REC.INTEREST_COMPOUNDING_FREQ;
857 P_Loan_Details_Rec.PAYMENT_CALC_METHOD := l_LOAN_DTL_REC.PAYMENT_CALC_METHOD;
858 P_Loan_Details_Rec.CUSTOM_CALC_METHOD := l_LOAN_DTL_REC.CUSTOM_CALC_METHOD;
859 P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD := l_LOAN_DTL_REC.ORIG_PAY_CALC_METHOD;
860 P_Loan_Details_Rec.PENAL_INT_RATE := l_LOAN_DTL_REC.PENAL_INT_RATE;
861 P_Loan_Details_Rec.PENAL_INT_GRACE_DAYS := l_LOAN_DTL_REC.PENAL_INT_GRACE_DAYS;
862 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_PRIN := l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_PRIN;
863 P_Loan_Details_Rec.CALC_ADD_INT_UNPAID_INT := l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_INT;
864 P_Loan_Details_Rec.LOCK_DATE := l_LOAN_DTL_REC.LOCK_DATE;
865 P_Loan_Details_Rec.LOCK_EXP_DATE := l_LOAN_DTL_REC.LOCK_EXP_DATE;
866
867 -- 'term phase' term attributes
868 P_Loan_Details_Rec.LOAN_TERM := l_LOAN_DTL_REC.LOAN_TERM;
869 P_Loan_Details_Rec.LOAN_TERM_PERIOD := l_LOAN_DTL_REC.LOAN_TERM_PERIOD;
870 P_Loan_Details_Rec.balloon_payment_type := l_LOAN_DTL_REC.balloon_payment_type;
871 P_Loan_Details_Rec.balloon_payment_amount := l_LOAN_DTL_REC.balloon_payment_amount;
872 P_Loan_Details_Rec.balloon_term := l_LOAN_DTL_REC.balloon_term;
873 P_Loan_Details_Rec.LOAN_START_DATE := l_LOAN_DTL_REC.LOAN_START_DATE;
874 P_Loan_Details_Rec.FIRST_PAYMENT_DATE := l_LOAN_DTL_REC.FIRST_PAYMENT_DATE;
875 P_Loan_Details_Rec.PRIN_FIRST_PAY_DATE := l_LOAN_DTL_REC.PRIN_FIRST_PAY_DATE;
876 P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY := l_LOAN_DTL_REC.PRIN_PAYMENT_FREQUENCY;
877 P_Loan_Details_Rec.floor_rate := l_LOAN_DTL_REC.floor_rate;
878 P_Loan_Details_Rec.ceiling_rate := l_LOAN_DTL_REC.ceiling_rate;
879 P_Loan_Details_Rec.percent_increase := l_LOAN_DTL_REC.percent_increase;
880 P_Loan_Details_Rec.percent_increase_life := l_LOAN_DTL_REC.percent_increase_life;
881
882 -- Terms for Payment attributes
883 P_Loan_Details_Rec.REAMORTIZE_OVER_PAYMENT := l_LOAN_DTL_REC.REAMORTIZE_OVER_PAYMENT;
884 P_Loan_Details_Rec.DELINQUENCY_THRESHOLD_AMOUNT := l_LOAN_DTL_REC.DELINQUENCY_THRESHOLD_AMOUNT;
885 P_Loan_Details_Rec.PAYMENT_APPLICATION_ORDER := l_LOAN_DTL_REC.PAYMENT_APPLICATION_ORDER;
886 P_Loan_Details_Rec.PMT_APPL_ORDER_SCOPE := l_LOAN_DTL_REC.PMT_APPL_ORDER_SCOPE;
887 P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING := l_LOAN_DTL_REC.REAMORTIZE_ON_FUNDING;
888
889 -- others
890 P_Loan_Details_Rec.org_id := l_LOAN_DTL_REC.org_id;
891 P_Loan_Details_Rec.loan_type_id := l_LOAN_DTL_REC.loan_type_id;
892 P_Loan_Details_Rec.loan_class_code := l_LOAN_DTL_REC.loan_class_code;
893 P_Loan_Details_Rec.loan_currency := l_LOAN_DTL_REC.loan_currency;
894 P_Loan_Details_Rec.maturity_date := l_LOAN_DTL_REC.maturity_date;
895
896 -- additional optional attributes
897 P_Loan_Details_Rec.ATTRIBUTE_CATEGORY := l_LOAN_DTL_REC.ATTRIBUTE_CATEGORY;
898 P_Loan_Details_Rec.ATTRIBUTE1 := l_LOAN_DTL_REC.ATTRIBUTE1;
899 P_Loan_Details_Rec.ATTRIBUTE2 := l_LOAN_DTL_REC.ATTRIBUTE2;
900 P_Loan_Details_Rec.ATTRIBUTE3 := l_LOAN_DTL_REC.ATTRIBUTE3;
901 P_Loan_Details_Rec.ATTRIBUTE4 := l_LOAN_DTL_REC.ATTRIBUTE4;
902 P_Loan_Details_Rec.ATTRIBUTE5 := l_LOAN_DTL_REC.ATTRIBUTE5;
903 P_Loan_Details_Rec.ATTRIBUTE6 := l_LOAN_DTL_REC.ATTRIBUTE6;
904 P_Loan_Details_Rec.ATTRIBUTE7 := l_LOAN_DTL_REC.ATTRIBUTE7;
905 P_Loan_Details_Rec.ATTRIBUTE8 := l_LOAN_DTL_REC.ATTRIBUTE8;
906 P_Loan_Details_Rec.ATTRIBUTE9 := l_LOAN_DTL_REC.ATTRIBUTE9;
907 P_Loan_Details_Rec.ATTRIBUTE10 := l_LOAN_DTL_REC.ATTRIBUTE10;
908 P_Loan_Details_Rec.ATTRIBUTE11 := l_LOAN_DTL_REC.ATTRIBUTE11;
909 P_Loan_Details_Rec.ATTRIBUTE12 := l_LOAN_DTL_REC.ATTRIBUTE12;
910 P_Loan_Details_Rec.ATTRIBUTE13 := l_LOAN_DTL_REC.ATTRIBUTE13;
911 P_Loan_Details_Rec.ATTRIBUTE14 := l_LOAN_DTL_REC.ATTRIBUTE14;
912 P_Loan_Details_Rec.ATTRIBUTE15 := l_LOAN_DTL_REC.ATTRIBUTE15;
913 P_Loan_Details_Rec.ATTRIBUTE16 := l_LOAN_DTL_REC.ATTRIBUTE16;
914 P_Loan_Details_Rec.ATTRIBUTE17 := l_LOAN_DTL_REC.ATTRIBUTE17;
915 P_Loan_Details_Rec.ATTRIBUTE18 := l_LOAN_DTL_REC.ATTRIBUTE18;
916 P_Loan_Details_Rec.ATTRIBUTE19 := l_LOAN_DTL_REC.ATTRIBUTE19;
917 P_Loan_Details_Rec.ATTRIBUTE20 := l_LOAN_DTL_REC.ATTRIBUTE20;
918
919 -- copy participants data back
920 p_LOAN_PART_TBL.delete;
921 FOR i IN 1 .. l_loan_part_tbl.count LOOP
922 p_LOAN_PART_TBL(i).HZ_PARTY_ID := l_LOAN_PART_TBL(i).HZ_PARTY_ID;
923 p_LOAN_PART_TBL(i).LOAN_PARTICIPANT_TYPE := l_LOAN_PART_TBL(i).LOAN_PARTICIPANT_TYPE;
924 p_LOAN_PART_TBL(i).START_DATE_ACTIVE := l_LOAN_PART_TBL(i).START_DATE_ACTIVE;
925 p_LOAN_PART_TBL(i).END_DATE_ACTIVE := l_LOAN_PART_TBL(i).END_DATE_ACTIVE;
926 p_LOAN_PART_TBL(i).CUST_ACCOUNT_ID := l_LOAN_PART_TBL(i).CUST_ACCOUNT_ID;
927 p_LOAN_PART_TBL(i).BILL_TO_ACCT_SITE_ID := l_LOAN_PART_TBL(i).BILL_TO_ACCT_SITE_ID;
928 p_LOAN_PART_TBL(i).CONTACT_PERS_PARTY_ID := l_LOAN_PART_TBL(i).CONTACT_PERS_PARTY_ID;
929 p_LOAN_PART_TBL(i).CONTACT_REL_PARTY_ID := l_LOAN_PART_TBL(i).CONTACT_REL_PARTY_ID;
930 END LOOP;
931
932 -- copy rate schedule data back
933 p_LOAN_RATES_TBL.delete;
934 FOR i IN 1 .. l_loan_rates_tbl.count LOOP
935 p_LOAN_RATES_TBL(i).INDEX_RATE := l_loan_rates_tbl(i).INDEX_RATE;
936 p_LOAN_RATES_TBL(i).SPREAD := l_loan_rates_tbl(i).SPREAD;
937 p_LOAN_RATES_TBL(i).INDEX_DATE := l_loan_rates_tbl(i).INDEX_DATE;
938 p_LOAN_RATES_TBL(i).BEGIN_INSTALLMENT_NUMBER := l_loan_rates_tbl(i).BEGIN_INSTALLMENT_NUMBER;
939 p_LOAN_RATES_TBL(i).END_INSTALLMENT_NUMBER := l_loan_rates_tbl(i).END_INSTALLMENT_NUMBER;
940 p_LOAN_RATES_TBL(i).INTEREST_ONLY_FLAG := l_loan_rates_tbl(i).INTEREST_ONLY_FLAG;
941 END LOOP;
942
943 -- copy custom schedule data back
944 p_loan_cust_sched_tbl.delete;
945 FOR i IN 1 .. l_loan_cust_sched_tbl.count LOOP
946
947 p_loan_cust_sched_tbl(i).PAYMENT_NUMBER := l_loan_cust_sched_tbl(i).PAYMENT_NUMBER;
948 p_loan_cust_sched_tbl(i).DUE_DATE := l_loan_cust_sched_tbl(i).DUE_DATE;
949 p_loan_cust_sched_tbl(i).RELATIVE_DATE := l_loan_cust_sched_tbl(i).RELATIVE_DATE;
950 p_loan_cust_sched_tbl(i).PRINCIPAL_AMOUNT := l_loan_cust_sched_tbl(i).PRINCIPAL_AMOUNT;
951 p_loan_cust_sched_tbl(i).PRINCIPAL_PERCENT := l_loan_cust_sched_tbl(i).PRINCIPAL_PERCENT;
952 p_loan_cust_sched_tbl(i).INTEREST_AMOUNT := l_loan_cust_sched_tbl(i).INTEREST_AMOUNT;
953 p_loan_cust_sched_tbl(i).LOCK_PRIN := l_loan_cust_sched_tbl(i).LOCK_PRIN;
954 p_loan_cust_sched_tbl(i).LOCK_INT := l_loan_cust_sched_tbl(i).LOCK_INT;
955
956 END LOOP;
957
958 logmessage(fnd_log.level_statement, 'Validating Loan Approved Date...');
959
960 IF P_Loan_Details_Rec.loan_approval_date IS NULL THEN
961 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
962 p_token1 => 'P_Loan_Details_Rec.loan_approval_date');
963 ELSE
964 IF ((P_Loan_Details_Rec.loan_approval_date < P_Loan_Details_Rec.loan_start_date) OR
965 (P_Loan_Details_Rec.loan_approval_date > P_Loan_Details_Rec.maturity_date))
966 THEN
967 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
968 p_token1 => 'P_Loan_Details_Rec.loan_approval_date',
969 p_token2 => P_Loan_Details_Rec.loan_approval_date);
970 END IF;
971 END IF;
972
973 logmessage(fnd_log.level_statement, 'Validating Loan Approved By...');
974
975 IF P_Loan_Details_Rec.loan_approved_by IS NULL THEN
976 logerrors(p_message_name => 'LNS_LCREATE_NULL_VALUE',
977 p_token1 => 'P_Loan_Details_Rec.loan_approved_by');
978 ELSE
979 BEGIN
980
981 SELECT
982 'Y' INTO l_dummy
983 FROM
984 jtf_rs_role_relations rel ,
985 jtf_rs_roles_b rol
986 WHERE
987 rel.role_id = rol.role_id
988 and rel.delete_flag <> 'Y'
989 and rol.role_type_code = 'LOANS'
990 and rol.role_code = 'LOAN_MGR'
991 and rol.active_flag = 'Y'
992 and rol.manager_flag = 'Y'
993 and rel.role_resource_id = P_Loan_Details_Rec.loan_approved_by;
994 EXCEPTION
995 WHEN no_data_found THEN
996 logerrors(p_message_name => 'LNS_LCREATE_INVALID_ATTRIBUTE',
997 p_token1 => 'P_Loan_Details_Rec.loan_approved_by',
998 p_token2 => P_Loan_Details_Rec.loan_approved_by);
999 END;
1000 END IF;
1001
1002 IF g_error_count > 0 THEN
1003 RAISE fnd_api.g_exc_error;
1004 END IF;
1005
1006 if l_loan_class = 'DIRECT' then
1007
1008 logmessage(fnd_log.level_unexpected, 'Calling validate_disbursements...');
1009 validate_disbursements(P_Loan_Details_Rec
1010 ,P_DISB_TBL
1011 , l_return_status
1012 , l_msg_count
1013 , l_msg_data);
1014
1015 IF l_return_status <> 'S' THEN
1016 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_disbursements()');
1017 RAISE fnd_api.g_exc_error;
1018 END IF;
1019
1020 IF g_error_count > 0 THEN
1021 RAISE fnd_api.g_exc_error;
1022 END IF;
1023
1024 FOR l_count IN 1 .. P_DISB_TBL.COUNT LOOP
1025
1026 -- create disb header
1027 select lns_disb_headers_s.NEXTVAL into l_DISB_HEADER_REC.DISB_HEADER_ID from dual;
1028
1029 l_DISB_HEADER_REC.LOAN_ID := l_loan_id;
1030 l_DISB_HEADER_REC.HEADER_AMOUNT := P_DISB_TBL(l_count).AMOUNT;
1031 l_DISB_HEADER_REC.PAYMENT_REQUEST_DATE := P_DISB_TBL(l_count).DUE_DATE;
1032 l_DISB_HEADER_REC.OBJECT_VERSION_NUMBER := 1;
1033 l_DISB_HEADER_REC.PHASE := 'TERM';
1034 l_DISB_HEADER_REC.ACTIVITY_CODE := P_DISB_TBL(l_count).ACTIVITY_CODE;
1035 l_DISB_HEADER_REC.DESCRIPTION := P_DISB_TBL(l_count).DESCRIPTION;
1036
1037 if P_DISB_TBL(l_count).DISBURSEMENT_DATE is not null then
1038 l_DISB_HEADER_REC.STATUS := 'FULLY_FUNDED';
1039 end if;
1040
1041 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_HEADER...');
1042 LNS_FUNDING_PUB.INSERT_DISB_HEADER(
1043 P_API_VERSION => 1.0,
1044 P_INIT_MSG_LIST => FND_API.G_TRUE,
1045 P_COMMIT => FND_API.G_FALSE,
1046 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1047 P_DISB_HEADER_REC => l_DISB_HEADER_REC,
1048 X_RETURN_STATUS => l_return_status,
1049 X_MSG_COUNT => l_msg_count,
1050 X_MSG_DATA => l_msg_data);
1051
1052 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
1053 IF l_return_status <> 'S' THEN
1054 logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_HEADER failed');
1055 RAISE fnd_api.g_exc_error;
1056 END IF;
1057
1058 -- create disb line
1059 select lns_disb_lines_s.NEXTVAL into l_DISB_LINE_REC.DISB_LINE_ID from dual;
1060
1061 l_DISB_LINE_REC.DISB_HEADER_ID := l_DISB_HEADER_REC.DISB_HEADER_ID;
1062 l_DISB_LINE_REC.DISB_LINE_NUMBER := 1;
1063 l_DISB_LINE_REC.LINE_AMOUNT := P_DISB_TBL(l_count).AMOUNT;
1064 l_DISB_LINE_REC.LINE_PERCENT := 100;
1065 l_DISB_LINE_REC.PAYEE_PARTY_ID := P_DISB_TBL(l_count).PAYEE_PARTY_ID;
1066 l_DISB_LINE_REC.BANK_ACCOUNT_ID := P_DISB_TBL(l_count).BANK_ACCOUNT_ID;
1067 l_DISB_LINE_REC.PAYMENT_METHOD_CODE := P_DISB_TBL(l_count).PAYMENT_METHOD_CODE;
1068 l_DISB_LINE_REC.OBJECT_VERSION_NUMBER := 1;
1069 l_DISB_LINE_REC.STATUS := l_DISB_HEADER_REC.STATUS;
1070 l_DISB_LINE_REC.REQUEST_DATE := P_DISB_TBL(l_count).SUBMISSION_DATE;
1071 l_DISB_LINE_REC.DISBURSEMENT_DATE := P_DISB_TBL(l_count).DISBURSEMENT_DATE;
1072
1073 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_LINE...');
1074 LNS_FUNDING_PUB.INSERT_DISB_LINE(
1075 P_API_VERSION => 1.0,
1076 P_INIT_MSG_LIST => FND_API.G_TRUE,
1077 P_COMMIT => FND_API.G_FALSE,
1078 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1079 P_DISB_LINE_REC => l_DISB_LINE_REC,
1080 X_RETURN_STATUS => l_return_status,
1081 X_MSG_COUNT => l_msg_count,
1082 X_MSG_DATA => l_msg_data);
1083
1084 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
1085 IF l_return_status <> 'S' THEN
1086 logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_LINE failed');
1087 RAISE fnd_api.g_exc_error;
1088 END IF;
1089
1090 END LOOP;
1091
1092 elsif l_loan_class = 'ERS' then
1093
1094 -- update all loan lines with status APPROVED and ADJUSTMENT_DATE = P_Loan_Details_Rec.LOAN_START_DATE
1095 UPDATE LNS_LOAN_LINES
1096 SET ADJUSTMENT_DATE = P_Loan_Details_Rec.LOAN_START_DATE,
1097 STATUS = 'APPROVED'
1098 WHERE LOAN_ID = l_loan_id;
1099 logmessage(fnd_log.level_unexpected, 'Updated loan lines');
1100
1101 end if;
1102
1103 -- store accounting
1104 for i in 1..p_distribution_tbl.count loop
1105 l_distribution_tbl(i).DISTRIBUTION_ID := null;
1106 l_distribution_tbl(i).LOAN_ID := l_loan_id;
1107 l_distribution_tbl(i).LINE_TYPE := p_distribution_tbl(i).LINE_TYPE;
1108 l_distribution_tbl(i).ACCOUNT_NAME := p_distribution_tbl(i).ACCOUNT_NAME;
1109 l_distribution_tbl(i).CODE_COMBINATION_ID := p_distribution_tbl(i).CODE_COMBINATION_ID;
1110 l_distribution_tbl(i).ACCOUNT_TYPE := p_distribution_tbl(i).ACCOUNT_TYPE;
1111 l_distribution_tbl(i).DISTRIBUTION_PERCENT := p_distribution_tbl(i).DISTRIBUTION_PERCENT;
1112 l_distribution_tbl(i).DISTRIBUTION_AMOUNT := p_distribution_tbl(i).DISTRIBUTION_AMOUNT;
1113 l_distribution_tbl(i).DISTRIBUTION_TYPE := p_distribution_tbl(i).DISTRIBUTION_TYPE;
1114 if l_distribution_tbl(i).DISTRIBUTION_TYPE = 'ORIGINATION' then
1115 l_distribution_tbl(i).ACTIVITY := 'LNS_APPROVAL';
1116 end if;
1117 end loop;
1118
1119 LNS_DISTRIBUTIONS_PUB.createDistrForImport(
1120 p_api_version => 1.0,
1121 p_init_msg_list => FND_API.G_FALSE,
1122 p_commit => FND_API.G_FALSE,
1123 p_loan_id => l_loan_id,
1124 x_distribution_tbl => l_distribution_tbl,
1125 x_return_status => l_return_status,
1126 x_msg_count => l_msg_count,
1127 x_msg_data => l_msg_data);
1128
1129 IF l_return_status <> 'S' THEN
1130 fnd_message.set_name('LNS', 'LNS_DISTRIBUTION_INVALID');
1131 fnd_msg_pub.ADD;
1132 logmessage(fnd_log.level_unexpected, fnd_msg_pub.GET(p_encoded => 'F'));
1133 RAISE fnd_api.g_exc_error;
1134 END IF;
1135
1136 logmessage(fnd_log.level_unexpected, 'Distributions created successfully');
1137
1138 -- generate and store agreement report
1139 LNS_REP_UTILS.STORE_LOAN_AGREEMENT(l_loan_id);
1140 logmessage(fnd_log.level_unexpected, 'Agreement report created successfully');
1141
1142 logmessage(fnd_log.LEVEL_STATEMENT, 'Updating Loan object...');
1143 SELECT object_version_number, loan_number
1144 INTO l_version_number, l_loan_number
1145 FROM lns_loan_headers_all
1146 WHERE loan_id = l_loan_id;
1147
1148 l_loan_header_rec.loan_id := l_loan_id;
1149 l_loan_header_rec.LOAN_APPROVAL_DATE := P_Loan_Details_Rec.loan_approval_date;
1150 l_loan_header_rec.LOAN_APPROVED_BY := P_Loan_Details_Rec.loan_approved_by;
1151 l_loan_header_rec.loan_status := 'ACTIVE';
1152 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
1153 l_loan_header_rec.funded_amount := P_Loan_Details_Rec.funded_amount;
1154
1155 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
1156 P_LOAN_HEADER_REC => l_loan_header_rec,
1157 P_INIT_MSG_LIST => FND_API.G_FALSE,
1158 X_RETURN_STATUS => l_return_status,
1159 X_MSG_COUNT => l_msg_count,
1160 X_MSG_DATA => l_msg_data);
1161
1162 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
1163
1164 IF l_return_status <> 'S' THEN
1165 logerrors(p_message_name => 'LNS_UPD_LOAN_FAIL');
1166 RAISE fnd_api.g_exc_error;
1167 END IF;
1168 logmessage(fnd_log.level_unexpected, 'Loan object updated successfully');
1169
1170 logmessage(fnd_log.level_unexpected, 'Calling validate_pay_history...');
1171 validate_pay_history(P_Loan_Details_Rec
1172 ,P_PAY_HIST_TBL
1173 , l_return_status
1174 , l_msg_count
1175 , l_msg_data);
1176
1177 IF l_return_status <> 'S' THEN
1178 logmessage(fnd_log.level_unexpected, 'Validation failed in module - validate_pay_history()');
1179 RAISE fnd_api.g_exc_error;
1180 END IF;
1181
1182 IF g_error_count > 0 THEN
1183 RAISE fnd_api.g_exc_error;
1184 END IF;
1185
1186 FOR l_count IN 1 .. P_PAY_HIST_TBL.COUNT LOOP
1187
1188 IF P_PAY_HIST_TBL(l_count).BILLED_PRIN > 0 or
1189 P_PAY_HIST_TBL(l_count).BILLED_INT > 0 or
1190 P_PAY_HIST_TBL(l_count).BILLED_FEE > 0
1191 THEN
1192
1193 l_bill_headers_tbl.delete;
1194 l_bill_lines_tbl.delete;
1195
1196 logmessage(fnd_log.level_statement, 'Creating bill ' || P_PAY_HIST_TBL(l_count).PAYMENT_NUMBER || ' ' || P_PAY_HIST_TBL(l_count).SOURCE || '...');
1197 l_bill_headers_tbl(1).header_id := 101;
1198 l_bill_headers_tbl(1).loan_id := l_loan_id;
1199 l_bill_headers_tbl(1).assoc_payment_num := P_PAY_HIST_TBL(l_count).PAYMENT_NUMBER;
1200 l_bill_headers_tbl(1).due_date := P_PAY_HIST_TBL(l_count).DUE_DATE;
1201
1202 l_lines_count := 0;
1203
1204 IF(P_PAY_HIST_TBL(l_count).BILLED_PRIN > 0) THEN
1205 l_lines_count := l_lines_count + 1;
1206 l_bill_lines_tbl(l_lines_count).line_id := 100 + l_lines_count;
1207 l_bill_lines_tbl(l_lines_count).header_id := l_bill_headers_tbl(1).header_id;
1208 l_bill_lines_tbl(l_lines_count).line_amount := P_PAY_HIST_TBL(l_count).BILLED_PRIN;
1209 l_bill_lines_tbl(l_lines_count).line_type := 'PRIN';
1210 l_bill_lines_tbl(l_lines_count).line_desc := P_Loan_Details_Rec.legacy_reference;
1211 logmessage(fnd_log.level_statement, 'Principal = ' || l_bill_lines_tbl(l_lines_count).line_amount);
1212 END IF;
1213
1214 IF(P_PAY_HIST_TBL(l_count).BILLED_INT > 0) THEN
1215 l_lines_count := l_lines_count + 1;
1216 l_bill_lines_tbl(l_lines_count).line_id := 100 + l_lines_count;
1217 l_bill_lines_tbl(l_lines_count).header_id := l_bill_headers_tbl(1).header_id;
1218 l_bill_lines_tbl(l_lines_count).line_amount := P_PAY_HIST_TBL(l_count).BILLED_INT;
1219 l_bill_lines_tbl(l_lines_count).line_type := 'INT';
1220 l_bill_lines_tbl(l_lines_count).line_desc := P_Loan_Details_Rec.legacy_reference;
1221 logmessage(fnd_log.level_statement, 'Interest = ' || l_bill_lines_tbl(l_lines_count).line_amount);
1222 END IF;
1223
1224 IF(P_PAY_HIST_TBL(l_count).BILLED_FEE > 0) THEN
1225 l_lines_count := l_lines_count + 1;
1226 l_bill_lines_tbl(l_lines_count).line_id := 100 + l_lines_count;
1227 l_bill_lines_tbl(l_lines_count).header_id := l_bill_headers_tbl(1).header_id;
1228 l_bill_lines_tbl(l_lines_count).line_amount := P_PAY_HIST_TBL(l_count).BILLED_FEE;
1229 l_bill_lines_tbl(l_lines_count).line_type := 'FEE';
1230 l_bill_lines_tbl(l_lines_count).line_desc := P_Loan_Details_Rec.legacy_reference;
1231 logmessage(fnd_log.level_statement, 'Fees = ' || l_bill_lines_tbl(l_lines_count).line_amount);
1232 END IF;
1233
1234 LNS_BILLING_BATCH_PUB.CREATE_OFFCYCLE_BILLS(
1235 P_API_VERSION => 1.0,
1236 P_INIT_MSG_LIST => FND_API.G_FALSE,
1237 P_COMMIT => FND_API.G_FALSE,
1238 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1239 P_BILL_HEADERS_TBL => l_BILL_HEADERS_TBL,
1240 P_BILL_LINES_TBL => l_BILL_LINES_TBL,
1241 x_return_status => l_return_status,
1242 x_msg_count => l_msg_count,
1243 x_msg_data => l_msg_data);
1244
1245 IF l_return_status <> 'S' THEN
1246 RAISE fnd_api.g_exc_error;
1247 END IF;
1248
1249 logmessage(fnd_log.level_unexpected, 'Bill ' || P_PAY_HIST_TBL(l_count).PAYMENT_NUMBER || ' created successfully');
1250
1251 if P_PAY_HIST_TBL(l_count).SOURCE = 'SCHEDULED' then
1252
1253 UPDATE lns_amortization_scheds
1254 SET parent_amortization_id = NULL,
1255 REAMORTIZE_TO_INSTALLMENT = -1 -- marking this record as IMPORTED (-1); for original installments this field is always null
1256 WHERE amortization_schedule_id =
1257 (SELECT last_amortization_id
1258 FROM lns_loan_headers_all
1259 WHERE loan_id = l_loan_id);
1260 logmessage(fnd_log.level_statement, 'Updated lns_amortization_scheds');
1261
1262 else
1263
1264 UPDATE lns_amortization_scheds
1265 SET REAMORTIZE_TO_INSTALLMENT = -1 -- marking this record as IMPORTED (-1); for original installments this field is always null
1266 WHERE amortization_schedule_id =
1267 (SELECT last_amortization_id
1268 FROM lns_loan_headers_all
1269 WHERE loan_id = l_loan_id);
1270 logmessage(fnd_log.level_statement, 'Updated lns_amortization_scheds');
1271
1272 end if;
1273
1274 l_paid_total := P_PAY_HIST_TBL(l_count).PAID_PRIN + P_PAY_HIST_TBL(l_count).PAID_INT + P_PAY_HIST_TBL(l_count).PAID_FEE;
1275
1276 if l_paid_total > 0 then
1277
1278 if P_PAY_HIST_TBL(l_count).RC_ID is null then
1279
1280 logmessage(fnd_log.level_statement, 'Creating cash receipt...');
1281
1282 if P_Loan_Details_Rec.LEGACY_REFERENCE is null then
1283 l_receipt_number := l_loan_number;
1284 else
1285 l_receipt_number := P_Loan_Details_Rec.LEGACY_REFERENCE || ' - '|| l_loan_number;
1286 end if;
1287
1288 AR_RECEIPT_API_PUB.CREATE_CASH(
1289 P_API_VERSION => 1.0,
1290 P_INIT_MSG_LIST => FND_API.G_FALSE,
1291 P_COMMIT => FND_API.G_FALSE,
1292 P_CURRENCY_CODE => P_Loan_Details_Rec.LOAN_CURRENCY,
1293 P_EXCHANGE_RATE_TYPE => P_Loan_Details_Rec.EXCHANGE_RATE_TYPE,
1294 P_EXCHANGE_RATE => P_Loan_Details_Rec.EXCHANGE_RATE,
1295 P_EXCHANGE_RATE_DATE => P_Loan_Details_Rec.EXCHANGE_DATE,
1296 P_AMOUNT => l_paid_total,
1297 P_RECEIPT_DATE => P_PAY_HIST_TBL(l_count).PAID_DATE,
1298 P_RECEIPT_METHOD_ID => P_PAY_HIST_TBL(l_count).RC_METHOD_ID,
1299 P_RECEIPT_NUMBER => l_receipt_number,
1300 P_CUSTOMER_ID => P_Loan_Details_Rec.CUST_ACCOUNT_ID,
1301 P_CR_ID => l_cr_id,
1302 X_RETURN_STATUS => L_RETURN_STATUS,
1303 X_MSG_COUNT => L_MSG_COUNT,
1304 X_MSG_DATA => L_MSG_DATA);
1305
1306 IF l_return_status <> 'S' THEN
1307 RAISE fnd_api.g_exc_error;
1308 END IF;
1309
1310 logmessage(fnd_log.level_unexpected, 'Cash receipt created successfully; id = ' || l_cr_id);
1311 ELSE
1312 l_cr_id := P_PAY_HIST_TBL(l_count).RC_ID;
1313 logmessage(fnd_log.level_statement, 'Using passed cash receipt id = ' || l_cr_id);
1314 END IF;
1315
1316 SELECT principal_trx_id,
1317 interest_trx_id,
1318 fee_trx_id
1319 INTO l_principal_trx_id,
1320 l_interest_trx_id,
1321 l_fee_trx_id
1322 FROM lns_amortization_scheds
1323 WHERE amortization_schedule_id =
1324 (SELECT last_amortization_id
1325 FROM lns_loan_headers_all
1326 WHERE loan_id = l_loan_id);
1327
1328 -- applying cash receipt
1329 IF(P_PAY_HIST_TBL(l_count).BILLED_PRIN > 0 AND
1330 P_PAY_HIST_TBL(l_count).PAID_PRIN > 0 AND
1331 l_principal_trx_id IS NOT NULL) THEN
1332
1333 logmessage(fnd_log.level_statement, 'Paying principal amount ' || P_PAY_HIST_TBL(l_count).PAID_PRIN ||' for transactionId ' || l_principal_trx_id);
1334 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1335 ,P_INIT_MSG_LIST => FND_API.G_FALSE
1336 ,P_COMMIT => FND_API.G_FALSE
1337 ,X_RETURN_STATUS => l_return_status
1338 ,X_MSG_COUNT => l_msg_count
1339 ,X_MSG_DATA => l_msg_data
1340 ,p_cash_receipt_id => l_cr_id
1341 ,p_customer_trx_id => l_PRINCIPAL_TRX_ID
1342 ,p_apply_date => P_PAY_HIST_TBL(l_count).PAID_DATE
1343 --,p_apply_gl_date => l_apply_date + nvl(g_day_togl_after_dd, 0)
1344 ,p_amount_applied => P_PAY_HIST_TBL(l_count).PAID_PRIN
1345 --,p_amount_applied_from => l_receipt_amount_from
1346 --,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1347 );
1348
1349 IF l_return_status <> 'S' THEN
1350 fnd_message.set_name('LNS', 'LNS_APPL_CR_FAIL');
1351 fnd_msg_pub.ADD;
1352 logmessage(fnd_log.level_unexpected, fnd_msg_pub.GET(p_encoded => 'F'));
1353 RAISE fnd_api.g_exc_error;
1354 END IF;
1355
1356 logmessage(fnd_log.level_unexpected, 'Principal paid successfully');
1357
1358 END IF;
1359
1360 IF(P_PAY_HIST_TBL(l_count).BILLED_INT > 0 AND
1361 P_PAY_HIST_TBL(l_count).PAID_INT > 0 AND
1362 l_interest_trx_id IS NOT NULL) THEN
1363
1364 logmessage(fnd_log.level_statement, 'Paying interest amount ' || P_PAY_HIST_TBL(l_count).PAID_INT || ' with transactionid ' || l_interest_trx_id);
1365
1366 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1367 ,P_INIT_MSG_LIST => FND_API.G_FALSE
1368 ,P_COMMIT => FND_API.G_FALSE
1369 ,X_RETURN_STATUS => l_return_status
1370 ,X_MSG_COUNT => l_msg_count
1371 ,X_MSG_DATA => l_msg_data
1372 ,p_cash_receipt_id => l_cr_id
1373 ,p_customer_trx_id => l_INTEREST_TRX_ID
1374 ,p_apply_date => P_PAY_HIST_TBL(l_count).PAID_DATE
1375 --,p_apply_gl_date => l_apply_date + nvl(g_day_togl_after_dd, 0)
1376 ,p_amount_applied => P_PAY_HIST_TBL(l_count).PAID_INT
1377 --,p_amount_applied_from => l_receipt_amount_from
1378 --,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1379 );
1380
1381 IF l_return_status <> 'S' THEN
1382 fnd_message.set_name('LNS', 'LNS_APPL_CR_FAIL');
1383 fnd_msg_pub.ADD;
1384 logmessage(fnd_log.level_unexpected, fnd_msg_pub.GET(p_encoded => 'F'));
1385 RAISE fnd_api.g_exc_error;
1386 END IF;
1387
1388 logmessage(fnd_log.level_unexpected, 'Interest paid successfully');
1389
1390 END IF;
1391
1392 IF(P_PAY_HIST_TBL(l_count).BILLED_FEE > 0 AND
1393 P_PAY_HIST_TBL(l_count).PAID_FEE > 0 AND
1394 l_fee_trx_id IS NOT NULL) THEN
1395
1396 logmessage(fnd_log.level_statement, 'Paying fee amount ' || P_PAY_HIST_TBL(l_count).PAID_FEE || ' with transactionid ' || l_fee_trx_id);
1397
1398 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1399 ,P_INIT_MSG_LIST => FND_API.G_FALSE
1400 ,P_COMMIT => FND_API.G_FALSE
1401 ,X_RETURN_STATUS => l_return_status
1402 ,X_MSG_COUNT => l_msg_count
1403 ,X_MSG_DATA => l_msg_data
1404 ,p_cash_receipt_id => l_cr_id
1405 ,p_customer_trx_id => l_FEE_TRX_ID
1406 ,p_apply_date => P_PAY_HIST_TBL(l_count).PAID_DATE
1407 --,p_apply_gl_date => l_apply_date + nvl(g_day_togl_after_dd, 0)
1408 ,p_amount_applied => P_PAY_HIST_TBL(l_count).PAID_FEE
1409 --,p_amount_applied_from => l_receipt_amount_from
1410 --,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1411 );
1412
1413 IF l_return_status <> 'S' THEN
1414 fnd_message.set_name('LNS', 'LNS_APPL_CR_FAIL');
1415 fnd_msg_pub.ADD;
1416 logmessage(fnd_log.level_unexpected, fnd_msg_pub.GET(p_encoded => 'F'));
1417 RAISE fnd_api.g_exc_error;
1418 END IF;
1419
1420 logmessage(fnd_log.level_unexpected, 'Fee paid successfully');
1421
1422 END IF;
1423
1424 END IF; --if l_paid_total > 0 then
1425
1426 END IF; -- IF P_PAY_HIST_TBL(l_count).BILLED_PRIN > 0 or...
1427
1428 END LOOP;
1429
1430 -- updating lns_terms
1431 SELECT object_version_number, term_id
1432 INTO l_version_number, l_term_id
1433 FROM lns_terms
1434 WHERE loan_id = l_loan_id;
1435
1436 l_term_rec.term_id := l_term_id;
1437 l_term_rec.loan_id := l_loan_id;
1438 l_term_rec.next_payment_due_date := P_Loan_Details_Rec.next_payment_due_date;
1439
1440 lns_terms_pub.update_term(p_object_version_number => l_version_number
1441 , p_init_msg_list => fnd_api.g_false
1442 , p_loan_term_rec => l_term_rec
1443 , x_return_status => l_return_status
1444 , x_msg_count => l_msg_count
1445 , x_msg_data => l_msg_data);
1446
1447 IF l_return_status <> 'S' THEN
1448 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Failed to update term object.');
1449 fnd_message.set_name('LNS', 'LNS_IMRT_FAIL_UPD_TERM');
1450 fnd_msg_pub.ADD;
1451 logmessage(fnd_log.level_unexpected, fnd_msg_pub.GET(p_encoded => 'F'));
1452 RAISE fnd_api.g_exc_error;
1453 END IF;
1454
1455 logmessage(fnd_log.level_unexpected, 'Term object updated successfully');
1456
1457 -- if rate_type = 'FLOATING', run UPDATE_LOAN_FLOATING_RATE
1458 if P_Loan_Details_Rec.RATE_TYPE = 'FLOATING' then
1459
1460 logmessage(fnd_log.level_statement, 'Updating floating rate schedule...');
1461 LNS_INDEX_RATES_PUB.UPDATE_LOAN_FLOATING_RATE(
1462 P_API_VERSION => 1.0,
1463 P_INIT_MSG_LIST => FND_API.G_FALSE,
1464 P_COMMIT => FND_API.G_FALSE,
1465 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1466 P_LOAN_ID => l_loan_id,
1467 x_return_status => l_return_status,
1468 x_msg_count => l_msg_count,
1469 x_msg_data => l_msg_data);
1470
1471 IF l_return_status <> 'S' THEN
1472 RAISE fnd_api.g_exc_error;
1473 END IF;
1474
1475 logmessage(fnd_log.LEVEL_UNEXPECTED, 'Floating rate schedule updated successfully');
1476
1477 -- fetching new rate schedule
1478 p_LOAN_RATES_TBL.delete;
1479 i := 0;
1480 open c_get_rate_sch_info(l_term_id);
1481 LOOP
1482
1483 fetch c_get_rate_sch_info into
1484 l_LOAN_RATES_REC.BEGIN_INSTALLMENT_NUMBER,
1485 l_LOAN_RATES_REC.END_INSTALLMENT_NUMBER,
1486 l_LOAN_RATES_REC.INDEX_DATE,
1487 l_LOAN_RATES_REC.INDEX_RATE,
1488 l_LOAN_RATES_REC.SPREAD,
1489 l_LOAN_RATES_REC.INTEREST_ONLY_FLAG;
1490 exit when c_get_rate_sch_info%NOTFOUND;
1491
1492 i := i + 1;
1493 p_LOAN_RATES_TBL(i) := l_LOAN_RATES_REC;
1494
1495 END LOOP;
1496 close c_get_rate_sch_info;
1497
1498 end if;
1499
1500 IF p_commit = fnd_api.g_true THEN
1501 COMMIT WORK;
1502 logmessage(fnd_log.level_statement, 'Commited');
1503 END IF;
1504
1505 x_loan_id := l_loan_id;
1506
1507 -- END OF BODY OF API
1508 x_return_status := fnd_api.g_ret_sts_success;
1509
1510 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1511
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 --InsertErrors;
1515 ROLLBACK TO import_loan;
1516 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1517 X_RETURN_STATUS := 'E';
1518 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1519 END IMPORT_LOAN;
1520
1521
1522
1523 BEGIN
1524 G_LOG_ENABLED := 'N';
1525 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1526
1527 /* getting msg logging info */
1528 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1529 if (G_LOG_ENABLED = 'N') then
1530 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1531 else
1532 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1533 end if;
1534
1535 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1536 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1537
1538 END;