[Home] [Help]
PACKAGE BODY: APPS.LNS_APPROVAL_ACTION_PUB
Source
1 PACKAGE BODY LNS_APPROVAL_ACTION_PUB AS
2 /* $Header: LNS_APACT_PUBP_B.pls 120.44.12010000.2 2008/09/16 16:56:39 gparuchu ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_APPROVAL_ACTION_PUB';
11 -- G_AF_DO_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
13 --------------------------------------------------
14 -- declaration of private procedures and functions
15 --------------------------------------------------
16
17 procedure logMessage(log_level in number
18 ,module in varchar2
19 ,message in varchar2)
20 is
21
22 begin
23
24 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
25 FND_LOG.STRING(log_level, module, message);
26 END IF;
27
28 end;
29
30 PROCEDURE do_create_approval_action (
31 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
32 ,x_action_id OUT NOCOPY NUMBER
33 ,x_return_status IN OUT NOCOPY VARCHAR2
34 );
35
36 PROCEDURE do_update_approval_action (
37 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
38 ,p_object_version_number IN OUT NOCOPY NUMBER
39 ,x_return_status IN OUT NOCOPY VARCHAR2
40 );
41
42 PROCEDURE do_delete_approval_action (
43 p_action_id IN NUMBER
44 ,x_return_status IN OUT NOCOPY VARCHAR2
45 );
46
47 /*===========================================================================+
48 | PROCEDURE
49 | do_create_approval_action
50 |
51 | DESCRIPTION
52 | Creates approval action.
53 |
54 | SCOPE - PRIVATE
55 |
56 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
57 |
58 | ARGUMENTS : IN:
59 | OUT:
60 | x_action_id
61 | IN/OUT:
62 | p_approval_action_rec
63 | x_return_status
64 |
65 | RETURNS : NONE
66 |
67 | NOTES
68 |
69 | MODIFICATION HISTORY
70 |
71 | 22-Jan-2004 Bernice Lam Created.
72 +===========================================================================*/
73
74 PROCEDURE do_create_approval_action(
75 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
76 ,x_action_id OUT NOCOPY NUMBER
77 ,x_return_status IN OUT NOCOPY VARCHAR2
78 ) IS
79
80 l_action_id NUMBER;
81 l_rowid ROWID := NULL;
82 l_dummy VARCHAR2(1);
83 l_msg_count NUMBER;
84 l_msg_data VARCHAR2(2000);
85
86 BEGIN
87 l_action_id := p_approval_action_rec.action_id;
88 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_approval_action procedure');
89
90 -- if primary key value is passed, check for uniqueness.
91 IF l_action_id IS NOT NULL AND
92 l_action_id <> FND_API.G_MISS_NUM
93 THEN
94 BEGIN
95 SELECT 'Y'
96 INTO l_dummy
97 FROM LNS_APPROVAL_ACTIONS
98 WHERE action_id = l_action_id;
99
100 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
101 FND_MESSAGE.SET_TOKEN('COLUMN', 'action_id');
102 FND_MSG_PUB.ADD;
103 RAISE FND_API.G_EXC_ERROR;
104
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 NULL;
108 END;
109 END IF;
110
111 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Insert_Row');
112
113 -- call table-handler.
114 LNS_APPROVAL_ACTIONS_PKG.Insert_Row(X_ACTION_ID => p_approval_action_rec.action_id
115 ,P_OBJECT_VERSION_NUMBER => 1
116 ,P_LOAN_ID => p_approval_action_rec.loan_id
117 ,P_ACTION_TYPE => p_approval_action_rec.action_type
118 ,P_AMOUNT => p_approval_action_rec.amount
119 ,P_REASON_CODE => p_approval_action_rec.reason_code
120 ,P_ATTRIBUTE_CATEGORY => p_approval_action_rec.attribute_category
121 ,P_ATTRIBUTE1 => p_approval_action_rec.attribute1
122 ,P_ATTRIBUTE2 => p_approval_action_rec.attribute2
123 ,P_ATTRIBUTE3 => p_approval_action_rec.attribute3
124 ,P_ATTRIBUTE4 => p_approval_action_rec.attribute4
125 ,P_ATTRIBUTE5 => p_approval_action_rec.attribute5
126 ,P_ATTRIBUTE6 => p_approval_action_rec.attribute6
127 ,P_ATTRIBUTE7 => p_approval_action_rec.attribute7
128 ,P_ATTRIBUTE8 => p_approval_action_rec.attribute8
129 ,P_ATTRIBUTE9 => p_approval_action_rec.attribute9
130 ,P_ATTRIBUTE10 => p_approval_action_rec.attribute10
131 ,P_ATTRIBUTE11 => p_approval_action_rec.attribute11
132 ,P_ATTRIBUTE12 => p_approval_action_rec.attribute12
133 ,P_ATTRIBUTE13 => p_approval_action_rec.attribute13
134 ,P_ATTRIBUTE14 => p_approval_action_rec.attribute14
135 ,P_ATTRIBUTE15 => p_approval_action_rec.attribute15
136 ,P_ATTRIBUTE16 => p_approval_action_rec.attribute16
137 ,P_ATTRIBUTE17 => p_approval_action_rec.attribute17
138 ,P_ATTRIBUTE18 => p_approval_action_rec.attribute18
139 ,P_ATTRIBUTE19 => p_approval_action_rec.attribute19
140 ,P_ATTRIBUTE20 => p_approval_action_rec.attribute20);
141
142 x_action_id := p_approval_action_rec.action_id;
143
144 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_approval_action procedure: After call to LNS_APPROVAL_ACTION.Insert_Row');
145
146 END do_create_approval_action;
147
148 /*===========================================================================+
149 | PROCEDURE
150 | do_update_approval_action
151 |
152 | DESCRIPTION
153 | Updates approval action.
154 |
155 | SCOPE - PRIVATE
156 |
157 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
158 |
159 | ARGUMENTS : IN:
160 | OUT:
161 | IN/OUT:
162 | p_approval_action_rec
163 | p_object_version_number
164 | x_return_status
165 |
166 | RETURNS : NONE
167 |
168 | NOTES
169 |
170 | MODIFICATION HISTORY
171 |
172 | 22-Jan-2004 Bernice Lam Created.
173 +===========================================================================*/
174
175 PROCEDURE do_update_approval_action(
176 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
177 ,p_object_version_number IN OUT NOCOPY NUMBER
178 ,x_return_status IN OUT NOCOPY VARCHAR2) IS
179
180 l_object_version_number NUMBER;
181 l_rowid ROWID;
182 ldup_rowid ROWID;
183
184 BEGIN
185
186 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_update_approval_action procedure');
187
188 -- check whether record has been updated by another user. If not, lock it.
189 BEGIN
190 SELECT OBJECT_VERSION_NUMBER,
191 ROWID
192 INTO l_object_version_number,
193 l_rowid
194 FROM LNS_APPROVAL_ACTIONS
195 WHERE ACTION_ID = p_approval_action_rec.action_id
196 FOR UPDATE OF ACTION_ID NOWAIT;
197
198 IF NOT
199 (
200 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
201 OR
202 (p_object_version_number IS NOT NULL AND
203 l_object_version_number IS NOT NULL AND
204 p_object_version_number = l_object_version_number
205 )
206 )
207 THEN
208 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
209 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_approval_actions');
210 FND_MSG_PUB.ADD;
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 p_object_version_number := nvl(l_object_version_number, 1) + 1;
215
216 EXCEPTION WHEN NO_DATA_FOUND THEN
217 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
218 FND_MESSAGE.SET_TOKEN('RECORD', 'approval action_rec');
219 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_approval_action_rec.action_id), 'null'));
220 FND_MSG_PUB.ADD;
221 RAISE FND_API.G_EXC_ERROR;
222 END;
223
224 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Update_Row');
225
226 -- log history
227 LNS_LOAN_HISTORY_PUB.log_record_pre(p_approval_action_rec.action_id,
228 'ACTION_ID',
229 'LNS_APPROVAL_ACTIONS');
230
231 --Call to table-handler
232 LNS_APPROVAL_ACTIONS_PKG.Update_Row (X_ACTION_ID => p_approval_action_rec.action_id
233 ,P_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER
234 ,P_LOAN_ID => p_approval_action_rec.LOAN_ID
235 ,P_ACTION_TYPE => p_approval_action_rec.ACTION_TYPE
236 ,P_AMOUNT => p_approval_action_rec.AMOUNT
237 ,P_REASON_CODE => p_approval_action_rec.REASON_CODE
238 ,P_ATTRIBUTE_CATEGORY => p_approval_action_rec.attribute_category
239 ,P_ATTRIBUTE1 => p_approval_action_rec.attribute1
240 ,P_ATTRIBUTE2 => p_approval_action_rec.attribute2
241 ,P_ATTRIBUTE3 => p_approval_action_rec.attribute3
242 ,P_ATTRIBUTE4 => p_approval_action_rec.attribute4
243 ,P_ATTRIBUTE5 => p_approval_action_rec.attribute5
244 ,P_ATTRIBUTE6 => p_approval_action_rec.attribute6
245 ,P_ATTRIBUTE7 => p_approval_action_rec.attribute7
246 ,P_ATTRIBUTE8 => p_approval_action_rec.attribute8
247 ,P_ATTRIBUTE9 => p_approval_action_rec.attribute9
248 ,P_ATTRIBUTE10 => p_approval_action_rec.attribute10
249 ,P_ATTRIBUTE11 => p_approval_action_rec.attribute11
250 ,P_ATTRIBUTE12 => p_approval_action_rec.attribute12
251 ,P_ATTRIBUTE13 => p_approval_action_rec.attribute13
252 ,P_ATTRIBUTE14 => p_approval_action_rec.attribute14
253 ,P_ATTRIBUTE15 => p_approval_action_rec.attribute15
254 ,P_ATTRIBUTE16 => p_approval_action_rec.attribute16
255 ,P_ATTRIBUTE17 => p_approval_action_rec.attribute17
256 ,P_ATTRIBUTE18 => p_approval_action_rec.attribute18
257 ,P_ATTRIBUTE19 => p_approval_action_rec.attribute19
258 ,P_ATTRIBUTE20 => p_approval_action_rec.attribute20);
259
260
261 -- log record changes
262 LNS_LOAN_HISTORY_PUB.log_record_post(p_approval_action_rec.action_id,
263 'ACTION_ID',
264 'LNS_APPROVAL_ACTIONS',
265 p_approval_action_rec.loan_id);
266
267 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_approval_action procedure: After call to LNS_APPROVAL_ACTIONS_PKG.Update_Row');
268
269 END do_update_approval_action;
270
271 /*===========================================================================+
272 | PROCEDURE
273 | do_delete_approval_action
274 |
275 | DESCRIPTION
276 | Deletes approval action.
277 |
278 | SCOPE - PRIVATE
279 |
280 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
281 |
282 | ARGUMENTS : IN:
283 | OUT:
284 | IN/OUT:
285 | p_action_id
286 | p_object_version_number
287 | x_return_status
288 |
289 | RETURNS : NONE
290 |
291 | NOTES
292 |
293 | MODIFICATION HISTORY
294 |
295 | 22-Jan-2004 Bernice Lam Created.
296 +===========================================================================*/
297
298 PROCEDURE do_delete_approval_action(
299 p_action_id NUMBER,
300 x_return_status IN OUT NOCOPY VARCHAR2
301 ) IS
302
303 l_dummy VARCHAR2(1);
304 BEGIN
305
306 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_delete_approval_action procedure');
307
308 IF p_action_id IS NOT NULL AND
309 p_action_id <> FND_API.G_MISS_NUM
310 THEN
311 -- check whether record has been deleted by another user. If not, lock it.
312 BEGIN
313 SELECT 'Y'
314 INTO l_dummy
315 FROM LNS_APPROVAL_ACTIONS
316 WHERE ACTION_ID = p_action_id;
317
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
321 FND_MESSAGE.SET_TOKEN('RECORD', 'approval action_rec');
322 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_action_id), 'null'));
323 FND_MSG_PUB.ADD;
324 RAISE FND_API.G_EXC_ERROR;
325 END;
326 END IF;
327
328 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Delete_Row');
329
330 --Call to table-handler
331 LNS_APPROVAL_ACTIONS_PKG.Delete_Row (P_ACTION_ID => p_action_id);
332
333 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_approval_action procedure: After call to LNS_APPROVAL_ACTIONS_PKG.Delete_Row');
334
335 END do_delete_approval_action;
336
337 PROCEDURE validate_loan (
338 p_approval_action_rec APPROVAL_ACTION_REC_TYPE
339 ,x_return_status IN OUT NOCOPY VARCHAR2) IS
340 l_install_number NUMBER;
341 l_dummy VARCHAR2(1);
342
343 CURSOR C_Check_Fee_Violation (X_Loan_Id NUMBER, X_Install_Num NUMBER) IS
344 SELECT 'X'
345 FROM LNS_FEE_ASSIGNMENTS
346 WHERE loan_id = X_LOAN_ID
347 AND end_installment_number > x_install_num;
348
349 BEGIN
350
351 IF (p_approval_action_rec.action_type in ('SUBMIT_FOR_APPR', 'APPROVE')) THEN
352 l_install_number := LNS_FIN_UTILS.GETNUMBERINSTALLMENTS(p_approval_action_rec.loan_id);
353 OPEN C_Check_Fee_Violation (p_approval_action_rec.loan_id, l_install_number);
354 FETCH C_Check_Fee_Violation INTO l_dummy;
355 IF C_Check_Fee_Violation%FOUND THEN
356 x_return_status := FND_API.G_RET_STS_ERROR;
357 FND_MESSAGE.SET_NAME('LNS', 'LNS_NEGATIVE_NUMBER_ERROR');
358 FND_MSG_PUB.ADD;
359 CLOSE C_Check_Fee_Violation;
360 RAISE FND_API.G_EXC_ERROR;
361
362 END IF;
363 CLOSE C_Check_Fee_Violation;
364 END IF;
365
366 END validate_loan;
367
368 ----------------------------
369 -- body of public procedures
370 ----------------------------
371
372 /*===========================================================================+
373 | PROCEDURE
374 | create_approval_action
375 |
376 | DESCRIPTION
377 | Creates approval action.
378 |
379 | SCOPE - PUBLIC
380 |
381 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
382 |
383 | ARGUMENTS : IN:
384 | p_init_msg_list
385 | p_approval_action_rec
386 | OUT:
387 | x_return_status
388 | x_msg_count
389 | x_msg_data
390 | x_action_id
391 | IN/OUT:
392 |
393 | RETURNS : NONE
394 |
395 | NOTES
396 |
397 | MODIFICATION HISTORY
398 | 22-Jan-2004 Bernice Lam Created.
399 | 18-JUL-2007 MBOLLI Bug#6169438 - Added new paramter to the API shiftLoanDates .
400 +===========================================================================*/
401 PROCEDURE create_approval_action (p_init_msg_list IN VARCHAR2
402 ,p_approval_action_rec IN APPROVAL_ACTION_REC_TYPE
403 ,x_action_id OUT NOCOPY NUMBER
404 ,x_return_status OUT NOCOPY VARCHAR2
405 ,x_msg_count OUT NOCOPY NUMBER
406 ,x_msg_data OUT NOCOPY VARCHAR2
407 ) IS
408
409 l_api_name CONSTANT VARCHAR2(30) := 'create_approval_action';
410 l_approval_action_rec APPROVAL_ACTION_REC_TYPE;
411 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
412 l_object_version_number NUMBER;
413 l_resource_id NUMBER;
414 l_status VARCHAR2(30);
415 l_currency_code VARCHAR2(15);
416 l_loan_number VARCHAR2(60);
417 l_loan_class_code VARCHAR2(30);
418 l_loan_type VARCHAR2(30);
419 l_gl_date DATE;
420 l_distribution_id NUMBER;
421 l_code_comb_id NUMBER;
422 l_index NUMBER := 1;
423 l_percent NUMBER;
424 l_group_id number;
425 l_requested_amt NUMBER;
426 l_submit_request_id NUMBER;
427 l_reference_type VARCHAR2(30);
428 l_reference_id NUMBER;
429 l_cust_trx_id NUMBER;
430 l_invoice_amt NUMBER;
431 l_funding_advice_id NUMBER;
432 l_request_id number;
433 l_notify boolean;
434 l_legal_entity_id number;
435 l_current_phase varchar2(30);
436 l_multiple_funding_flag varchar2(1);
437 l_open_to_term_flag varchar2(1);
438 l_budget_req_approval varchar2(1);
439 l_loan_needs_approval varchar2(1);
440 l_term_rec lns_terms_pub.loan_term_rec_type;
441 l_loan_details lns_financials.loan_details_rec; --used for shiftloandates api
442 l_term_id number;
443 l_term_version_number number;
444 -- for xla accounting events
445 l_budgetary_status varchar2(30);
446 l_last_api_called varchar2(500); --Store the last api that was called before exception
447 l_secondary_status varchar2(30);
448 l_prev_sec_status varchar2(30);
449 l_prev_loan_status varchar2(30);
450 l_xml_output BOOLEAN;
451 l_iso_language FND_LANGUAGES.iso_language%TYPE;
452 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
453 l_dates_shifted_flag VARCHAR2(1);
454 --Bug 6938125 - FP:11i-R12:ENHANCEMENTS TO CUSTOM AMORTIZATION SCHEDULE
455 l_customized varchar2(1);
456
457
458 CURSOR C_Get_Loan_Info (X_Loan_Id NUMBER) IS
459 SELECT H.OBJECT_VERSION_NUMBER
460 ,H.LOAN_STATUS
461 ,H.LOAN_CURRENCY
462 ,H.LOAN_NUMBER
463 ,H.LOAN_CLASS_CODE
464 ,H.LOAN_TYPE
465 ,H.GL_DATE
466 ,H.REQUESTED_AMOUNT
467 ,H.REFERENCE_TYPE
468 ,H.REFERENCE_ID
469 ,H.CURRENT_PHASE
470 ,H.MULTIPLE_FUNDING_FLAG
471 ,H.OPEN_TO_TERM_FLAG
472 ,NVL(P.BDGT_REQ_FOR_APPR_FLAG, 'N') BDGT_REQ_FOR_APPR_FLAG
473 ,NVL(P.LOAN_APPR_REQ_FLAG, 'Y') LOAN_APPR_REQ_FLAG
474 ,H.SECONDARY_STATUS
475 ,nvl(custom_payments_flag, 'N')
476 FROM LNS_LOAN_HEADERS_ALL H,
477 LNS_LOAN_PRODUCTS_ALL P
478 WHERE H.LOAN_ID = X_Loan_Id
479 and H.product_id = P.loan_product_id(+);
480
481 CURSOR C_Get_Resource_Id (X_User_Id NUMBER) IS
482 SELECT RESOURCE_ID
483 FROM JTF_RS_RESOURCE_EXTNS
484 WHERE USER_ID = X_USER_ID;
485
486 CURSOR C_Get_Distribution (X_Loan_Id NUMBER, X_Acct_Type VARCHAR2, X_Acct_Name VARCHAR2, X_Line_Type VARCHAR2) IS
487 select DISTRIBUTION_ID
488 ,CODE_COMBINATION_ID
489 ,DISTRIBUTION_PERCENT
490 from lns_distributions
491 where LOAN_ID = x_loan_id
492 and account_type = x_acct_type
493 and account_name = x_acct_name
494 and line_type = x_line_type
495 and distribution_type = 'ORIGINATION';
496
497 cursor c_sob_id is
498 select so.set_of_books_id
499 from lns_system_options sb,
500 gl_sets_of_books so
501 where sb.set_of_books_id = so.set_of_books_id;
502
503 /* query term version */
504 CURSOR term_version_cur(P_LOAN_ID number) IS
505 select TERM_ID
506 ,OBJECT_VERSION_NUMBER
507 from LNS_TERMS
508 where LOAN_ID = P_LOAN_ID;
509
510 CURSOR loan_version_cur(P_LOAN_ID number) IS
511 select OBJECT_VERSION_NUMBER
512 from LNS_LOAN_HEADERS
513 where LOAN_ID = P_LOAN_ID;
514
515 -- getting loan previous secondary status to reset if Conversion request is rejected
516 CURSOR prev_sec_status_cur(P_LOAN_ID number) IS
517 select old_value
518 from lns_loan_histories_h
519 where table_name = 'LNS_LOAN_HEADERS_ALL' and
520 column_name = 'SECONDARY_STATUS' and
521 new_value = 'PENDING_CANCELLATION' and
522 loan_id = P_LOAN_ID and
523 loan_history_id =
524 (select max(loan_history_id)
525 from lns_loan_histories_h
526 where table_name = 'LNS_LOAN_HEADERS_ALL' and
527 column_name = 'SECONDARY_STATUS' and
528 loan_id = P_LOAN_ID);
529
530 BEGIN
531 l_last_api_called := '';
532 l_approval_action_rec := p_approval_action_rec;
533 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Begin');
534
535 -- standard start of API savepoint
536 SAVEPOINT create_approval_action;
537
538 -- initialize message list if p_init_msg_list is set to TRUE.
539 IF FND_API.to_Boolean(p_init_msg_list) THEN
540 FND_MSG_PUB.initialize;
541 END IF;
542
543 -- initialize API return status to success.
544 x_return_status := FND_API.G_RET_STS_SUCCESS;
545
546 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to do_create_approval_action proc');
547
548 -- call to business logic.
549 do_create_approval_action(l_approval_action_rec
550 ,x_action_id
551 ,x_return_status);
552
553 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to do_create_approval_action proc');
554 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_LOAN_HEADER_PUB.update_loan proc');
555
556 OPEN C_Get_Loan_Info(l_approval_action_rec.loan_id);
557 FETCH C_Get_Loan_Info
558 INTO l_object_version_number
559 ,l_status
560 ,l_currency_code
561 ,l_loan_number
562 ,l_loan_class_code
563 ,l_loan_type
564 ,l_gl_date
565 ,l_requested_amt
566 ,l_reference_type
567 ,l_reference_id
568 ,l_current_phase
569 ,l_multiple_funding_flag
570 ,l_open_to_term_flag
571 ,l_budget_req_approval
572 ,l_loan_needs_approval
573 ,l_secondary_status
574 ,l_customized;
575 IF C_Get_Loan_Info%NOTFOUND THEN
576 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
577 FND_MESSAGE.SET_TOKEN('RECORD', 'loan header');
578 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(l_loan_header_rec.loan_id), 'null'));
579 FND_MSG_PUB.ADD;
580 CLOSE C_Get_Loan_Info;
581 l_last_api_called := 'C_Get_Loan_Info';
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584 CLOSE C_Get_Loan_Info;
585 l_prev_loan_status := l_status;
586
587 OPEN C_Get_Resource_Id(LNS_UTILITY_PUB.Created_By);
588 FETCH C_Get_Resource_Id INTO l_resource_id;
589 CLOSE C_Get_Resource_Id;
590
591 -- validate status transitions
592 l_loan_header_rec.loan_id := l_approval_action_rec.loan_id;
593 IF (l_approval_action_rec.action_type = 'SUBMIT_FOR_APPR') THEN
594 IF l_status = 'INCOMPLETE' THEN
595 l_loan_header_rec.loan_status := 'PENDING';
596 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
597 ELSE
598 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
599 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
600 FND_MSG_PUB.ADD;
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 ELSIF (l_approval_action_rec.action_type = 'SUBMIT_FOR_CONV') THEN
605 IF (l_status = 'ACTIVE' AND l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y') THEN
606 l_loan_header_rec.secondary_status := 'PENDING_CONVERSION';
607 ELSE
608 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
609 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
610 FND_MSG_PUB.ADD;
611 RAISE FND_API.G_EXC_ERROR;
612 END IF;
613
614 ELSIF (l_approval_action_rec.action_type = 'SUBMIT_FOR_CNCL') THEN
615 IF (l_loan_class_code = 'DIRECT') THEN
616 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE');
617
618 LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE(P_API_VERSION => 1.0
619 ,P_INIT_MSG_LIST => FND_API.G_TRUE
620 ,P_COMMIT => FND_API.G_FALSE
621 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
622 ,P_LOAN_ID => l_loan_header_rec.loan_id
623 ,X_RETURN_STATUS => x_return_status
624 ,X_MSG_COUNT => x_msg_count
625 ,X_MSG_DATA => x_msg_data);
626
627 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE');
628
629 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
630 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE failed with status ' || x_return_status);
631 l_last_api_called := 'LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE';
632 RAISE FND_API.G_EXC_ERROR;
633 END IF;
634 ELSE
635 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
636 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
637 FND_MSG_PUB.ADD;
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 ELSIF (l_approval_action_rec.action_type = 'APPROVE') THEN
642
643 -- approval action type = 'APPROVE'
644 IF ( l_status = 'PENDING' OR (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') ) THEN
645
646 if l_loan_class_code = 'ERS' then
647 -- use resource ID
648 l_loan_header_rec.loan_approved_by := l_resource_id;
649 l_loan_header_rec.loan_approval_date := sysdate;
650 --if (l_gl_date is null) then
651 -- CMS requirement karamach bug5129367
652 l_loan_header_rec.gl_date := sysdate;
653 --end if;
654 l_loan_header_rec.loan_status := 'APPROVED';
655 l_loan_header_rec.secondary_status := 'UNACCOUNTED'; --- raverma added new secondary status
656 l_loan_header_rec.funded_amount := l_requested_amt;
657 l_loan_header_rec.initial_loan_balance := l_requested_amt;
658
659 elsif l_loan_class_code = 'DIRECT' then
660 l_loan_header_rec.loan_status := 'APPROVED';
661 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
662 -- use resource ID
663 l_loan_header_rec.loan_approved_by := l_resource_id;
664 l_loan_header_rec.loan_approval_date := sysdate;
665 --if (l_gl_date is null) then
666 -- CMS requirement karamach bug5129367
667 l_loan_header_rec.gl_date := sysdate;
668 --end if;
669 end if;
670
671 -- approval action type = 'APPROVE'
672 ELSIF l_status = 'PENDING_CANCELLATION' THEN
673 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB');
674
675 LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB(P_API_VERSION => 1.0
676 ,P_INIT_MSG_LIST => FND_API.G_TRUE
677 ,P_COMMIT => FND_API.G_FALSE
678 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
679 ,P_LOAN_ID => l_loan_header_rec.loan_id
680 ,X_RETURN_STATUS => x_return_status
681 ,X_MSG_COUNT => x_msg_count
682 ,X_MSG_DATA => x_msg_data);
683
684 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB');
685
686 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
687 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB failed with status ' || x_return_status);
688 l_last_api_called := 'LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB';
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691
692 -- approval action type = 'APPROVE'
693 ELSIF (l_status = 'ACTIVE' AND l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y') THEN
694
695 /**********************Begin Permanent Conversion to Term Phase*************/
696 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calling LNS_FINANCIALS.shiftLoanDates');
697
698 -- we are moving the loan to the TERM PHASE -- first shift the dates based on conversion date
699 -- Bug#6169438 Added new parameter to the shiftLoanDates API Invocation
700 LNS_FINANCIALS.shiftLoanDates(p_loan_id => l_loan_header_rec.loan_id
701 ,p_new_start_date => sysdate
702 ,p_phase => 'TERM'
703 ,x_loan_details => l_loan_details
704 ,x_dates_shifted_flag => l_dates_shifted_flag
705 ,x_return_status => x_return_status
706 ,x_msg_count => x_msg_count
707 ,x_msg_data => x_msg_data);
708
709 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
710 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - LNS_FINANCIALS.shiftLoanDates failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
711 l_last_api_called := 'LNS_FINANCIALS.shiftLoanDates';
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714
715 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New loan start date: ' || l_loan_details.loan_start_date);
716 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New first payment date: ' || l_loan_details.first_payment_Date);
717 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New maturity date: ' || l_loan_details.maturity_date);
718
719 /* query term version */
720 open term_version_cur(l_loan_header_rec.loan_id);
721 fetch term_version_cur into l_TERM_ID, l_TERM_VERSION_NUMBER;
722 close term_version_cur;
723
724 /* setting term data for do term update */
725 l_term_rec.TERM_ID := l_TERM_ID;
726 l_term_rec.LOAN_ID := l_loan_header_rec.loan_id;
727 l_term_rec.FIRST_PAYMENT_DATE := l_loan_details.first_payment_Date;
728 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_loan_details.first_payment_Date;
729
730 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updating lns_terms w following values:');
731 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'TERM_ID: ' || l_term_rec.TERM_ID);
732 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_ID: ' || l_term_rec.LOAN_ID);
733 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'FIRST_PAYMENT_DATE: ' || l_term_rec.FIRST_PAYMENT_DATE);
734 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'NEXT_PAYMENT_DUE_DATE: ' || l_term_rec.NEXT_PAYMENT_DUE_DATE);
735
736 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER
737 ,p_init_msg_list => FND_API.G_FALSE
738 ,p_loan_term_rec => l_term_rec
739 ,X_RETURN_STATUS => x_return_status
740 ,X_MSG_COUNT => x_msg_count
741 ,X_MSG_DATA => x_msg_data);
742
743 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'x_return_status: ' || x_return_status);
744
745 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
746 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Successfully update LNS_TERMS');
747 ELSE
748 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
749 FND_MSG_PUB.Add;
750 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - LNS_TERMS_PUB.update_term failed with message: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
751 l_last_api_called := 'LNS_TERMS_PUB.update_term';
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754
755 /* setting data for future loan update */
756 l_loan_header_rec.LOAN_START_DATE := l_loan_details.loan_start_date;
757 l_loan_header_rec.LOAN_MATURITY_DATE := l_loan_details.maturity_date;
758 l_loan_header_rec.current_phase := 'TERM';
759 l_loan_header_rec.secondary_status := 'CONVERTED_TO_TERM_PHASE';
760 l_loan_header_rec.LAST_PAYMENT_NUMBER := FND_API.G_MISS_NUM;
761 l_loan_header_rec.LAST_AMORTIZATION_ID := FND_API.G_MISS_NUM;
762
763 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Data to Update loan header with during conversion...');
764 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'loan_id: ' || l_loan_header_rec.loan_id);
765 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_START_DATE: ' || l_loan_header_rec.LOAN_START_DATE);
766 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_MATURITY_DATE: ' || l_loan_header_rec.LOAN_MATURITY_DATE);
767 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'current_phase: ' || l_loan_header_rec.current_phase);
768 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'secondary_status: ' || l_loan_header_rec.secondary_status);
769 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LAST_PAYMENT_NUMBER: ' || l_loan_header_rec.LAST_PAYMENT_NUMBER);
770 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LAST_AMORTIZATION_ID: ' || l_loan_header_rec.LAST_AMORTIZATION_ID);
771
772 --Process Conversion Fees
773 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before calling lns_fee_engine.processDisbursementFees to process Conversion fees for permanent conversion to TERM phase');
774 lns_fee_engine.processDisbursementFees(p_init_msg_list => FND_API.G_TRUE
775 ,p_commit => FND_API.G_FALSE
776 ,p_phase => 'TERM'
777 ,p_loan_id => l_loan_header_rec.loan_id
778 ,p_disb_head_id => NULL
779 ,x_return_status => x_return_status
780 ,x_msg_count => x_msg_count
781 ,x_msg_data => x_msg_data);
782
783 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - lns_fee_engine.processDisbursementFees to process Conversion fees for permanent conversion to TERM phase');
784
785 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
786 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - lns_fee_engine.processDisbursementFees to process Conversion fees for TERM phase failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
787 l_last_api_called := 'lns_fee_engine.processDisbursementFees';
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790 /**********************End Permanent Conversion to Term Phase*************/
791
792 ELSE
793 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
794 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
795 FND_MSG_PUB.ADD;
796 RAISE FND_API.G_EXC_ERROR;
797 END IF;
798
799 ELSIF (l_approval_action_rec.action_type = 'REJECT') THEN
800 IF ( l_status = 'PENDING' OR (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') ) THEN
801 l_loan_header_rec.loan_status := 'REJECTED';
802 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
803 ELSIF (l_status = 'PENDING_CANCELLATION') THEN
804 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
805
806 LNS_FUNDING_PUB.REJECT_CANCEL_DISB(P_API_VERSION => 1.0
807 ,P_INIT_MSG_LIST => FND_API.G_TRUE
808 ,P_COMMIT => FND_API.G_FALSE
809 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
810 ,P_LOAN_ID => l_loan_header_rec.loan_id
811 ,X_RETURN_STATUS => x_return_status
812 ,X_MSG_COUNT => x_msg_count
813 ,X_MSG_DATA => x_msg_data);
814
815 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
816
817 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
818 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LNS_FUNDING_PUB.REJECT_CANCEL_DISB failed with status ' || x_return_status);
819 l_last_api_called := 'LNS_FUNDING_PUB.REJECT_CANCEL_DISB';
820 RAISE FND_API.G_EXC_ERROR;
821 END IF;
822 ELSIF (l_status = 'ACTIVE' and l_secondary_status = 'PENDING_CONVERSION') THEN
823 -- getting loan previous status
824 open prev_sec_status_cur(l_loan_header_rec.loan_id);
825 fetch prev_sec_status_cur into l_prev_sec_status;
826 close prev_sec_status_cur;
827 l_loan_header_rec.secondary_status := nvl(l_prev_sec_status,FND_API.G_MISS_CHAR);
828 ELSE
829 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
830 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
831 FND_MSG_PUB.ADD;
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834
835 ELSIF (l_approval_action_rec.action_type = 'REQUEST_FOR_INFO') THEN
836 IF (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') THEN
837 --Loan is already in INCOMPLETE status, so do nothing
838 null;
839 ELSIF l_status = 'PENDING' THEN
840 l_loan_header_rec.loan_status := 'INCOMPLETE';
841 ELSIF (l_status = 'PENDING_CANCELLATION') THEN
842 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
843
844 LNS_FUNDING_PUB.REJECT_CANCEL_DISB(P_API_VERSION => 1.0
845 ,P_INIT_MSG_LIST => FND_API.G_TRUE
846 ,P_COMMIT => FND_API.G_FALSE
847 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
848 ,P_LOAN_ID => l_loan_header_rec.loan_id
849 ,X_RETURN_STATUS => x_return_status
850 ,X_MSG_COUNT => x_msg_count
851 ,X_MSG_DATA => x_msg_data);
852
853 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
854
855 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
856 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB failed with status ' || x_return_status);
857 l_last_api_called := 'LNS_FUNDING_PUB.REJECT_CANCEL_DISB';
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 --Loan Status change is already handled in the above procedure call. no need to update loan header again
862 ELSIF l_status = 'ACTIVE' then
863 if l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y' THEN
864 l_loan_header_rec.secondary_status := 'MORE_INFO_REQUESTED';
865 end if;
866 ELSE
867 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
868 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
869 FND_MSG_PUB.ADD;
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872 END IF;
873
874 /* 08-12-2005 raverma added defaultDistributions call for DIRECT loan */
875 --Question for Raj:
876 --Do we need to check for l_prev_loan_status = 'PENDING' to call code below on initial loan approval only?
877 IF (l_approval_action_rec.action_type = 'APPROVE' and (l_loan_header_rec.loan_status = 'ACTIVE' or l_loan_header_rec.loan_status = 'APPROVED') and l_loan_class_code = 'DIRECT') THEN
878
879 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - before default distributions');
880 Lns_distributions_pub.defaultDistributions(p_api_version => 1.0
881 ,p_init_msg_list => FND_API.G_TRUE
882 ,p_commit => FND_API.G_FALSE
883 ,p_loan_id => l_loan_header_rec.loan_id
884 ,p_loan_class_code => l_loan_class_code
885 ,x_return_status => x_return_status
886 ,x_msg_count => x_msg_count
887 ,x_msg_data => x_msg_data);
888 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after default distributions ' || x_return_status);
889
890 begin
891 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'calling lns_distributions_pub.budgetary_control');
892 lns_distributions_pub.budgetary_control(p_init_msg_list => FND_API.G_FALSE
893 ,p_commit => FND_API.G_FALSE
894 ,p_loan_id => l_approval_action_rec.loan_id
895 ,p_budgetary_control_mode => 'R'
896 ,x_budgetary_status_code => l_budgetary_status
897 ,x_return_status => x_return_status
898 ,x_msg_count => x_msg_count
899 ,x_msg_data => x_msg_data);
900 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to Lns_distributions_pub.budgetary_control return status ' || x_return_status);
901 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_budgetary_status' || l_budgetary_status);
902 --FND_MSG_PUB.initialize;
903 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
904
905 RAISE FND_API.G_EXC_ERROR;
906
907 else -- BC Call Returned 'S'
908 if l_budgetary_status = 'ADVISORY' or l_budgetary_status = 'SUCCESS' then
909 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'budget reserved');
910 l_loan_header_rec.secondary_status := 'BUDGET_RESERVED';
911
912 elsif l_budgetary_status = 'FAIL' or l_budgetary_status = 'PARTIAL' or l_budgetary_status = 'XLA_ERROR' then
913 if l_budget_req_approval = 'Y' then
914 -- continue as if nothing happen
915 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'budget required: NO BUDGET');
916 RAISE FND_API.G_EXC_ERROR;
917 else
918 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'NO BUDGET');
919 FND_MSG_PUB.initialize;
920 l_loan_header_rec.secondary_status := 'NO_BUDGET';
921 end if;
922
923 end if;
924
925 end if;
926 end;
927
928 END IF;
929
930 --if loan status is cancelled, the funding api to cancel remaining disbursements would have updated loan header already no need to do it again
931
932 IF (l_approval_action_rec.action_type = 'APPROVE' and l_loan_header_rec.loan_status = 'APPROVED' and l_loan_header_rec.secondary_status = 'UNACCOUNTED' and l_loan_class_code = 'ERS') THEN
933 -- fix for bug 6133313: if this loan is customized then set ORIG_PAY_CALC_METHOD to null
934 if l_customized = 'Y' then
935
936 -- getting terms version for future update
937 select term_id, object_version_number into l_term_id, l_object_version_number
938 from lns_terms
939 where loan_id = l_loan_header_rec.loan_id;
940
941 -- Updating terms
942 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
943 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms...');
944 END IF;
945
946 l_term_rec.TERM_ID := l_term_id;
947 l_term_rec.LOAN_ID := l_loan_header_rec.loan_id;
948 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
949
950 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version_number,
951 p_init_msg_list => FND_API.G_FALSE,
952 p_loan_term_rec => l_term_rec,
953 X_RETURN_STATUS => x_return_status,
954 X_MSG_COUNT => x_msg_count,
955 X_MSG_DATA => x_msg_data);
956
957
958 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || x_return_status);
959 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 ELSE
962 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
963 END IF;
964
965 end if;
966
967 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to submit adjustment request');
968 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
969
970 LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE(P_API_VERSION => 1.0
971 ,P_INIT_MSG_LIST => FND_API.G_TRUE
972 ,P_COMMIT => FND_API.G_FALSE
973 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
974 ,P_LOAN_ID => l_loan_header_rec.loan_id
975 ,X_RETURN_STATUS => x_return_status
976 ,X_MSG_COUNT => x_msg_count
977 ,X_MSG_DATA => x_msg_data);
978
979 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
980 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE failed with status ' || x_return_status);
981 l_last_api_called := 'LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE';
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984
985 end if;
986
987 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
988
989 -- moved the defaultDistributions call to Concurrent Process
990 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to submit gen distributions request');
991 l_notify := FND_REQUEST.ADD_NOTIFICATION(FND_GLOBAL.USER_NAME);
992 --call fnd_request_api....
993 FND_REQUEST.SET_ORG_ID(MO_GLOBAL.GET_CURRENT_ORG_ID());
994 l_request_id := FND_REQUEST.SUBMIT_REQUEST('LNS'
995 ,'LNS_GEN_DIST'
996 ,'', '', FALSE
997 ,l_loan_header_rec.loan_id);
998 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after call to submit gen distributions request ' || l_request_id);
999
1000 end if; --END if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1001
1002 END IF; -- END IF (l_approval_action_rec.action_type = 'APPROVE' and l_loan_header_rec.loan_status = 'ACTIVE' and l_loan_class_code = 'ERS') THEN
1003
1004 IF (l_loan_header_rec.loan_status <> 'CANCELLED') then
1005
1006 open loan_version_cur(l_loan_header_rec.loan_id);
1007 fetch loan_version_cur into l_object_version_number;
1008 close loan_version_cur;
1009
1010 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan');
1011 LNS_LOAN_HEADER_PUB.update_loan(p_init_msg_list => FND_API.G_FALSE
1012 ,p_loan_header_rec => l_loan_header_rec
1013 ,p_object_version_number => l_object_version_number
1014 ,x_return_status => x_return_status
1015 ,x_msg_count => x_msg_count
1016 ,x_msg_data => x_msg_data);
1017 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'After call to LNS_LOAN_HEADER_PUB.update_loan');
1018
1019 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1020 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LNS_LOAN_HEADER_PUB.update_loan failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
1021 l_last_api_called := 'LNS_LOAN_HEADER_PUB.update_loan';
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to LNS_LOAN_HEADER_PUB.update_loan proc');
1025
1026 END IF;
1027
1028 if (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_approval_action_rec.action_type = 'APPROVE' and (l_loan_header_rec.loan_status = 'ACTIVE' or l_loan_header_rec.loan_status = 'APPROVED')) then
1029
1030 /* begin submit request to generate Loan Agreement Report */
1031 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Before calling FND_REQUEST.SUBMIT_REQUEST for LNS_AGREEMENT (Loan Agreement Report) for loan_id: ' || l_loan_header_rec.loan_id);
1032 l_notify := FND_REQUEST.ADD_NOTIFICATION(FND_GLOBAL.USER_NAME);
1033 FND_REQUEST.SET_ORG_ID(MO_GLOBAL.GET_CURRENT_ORG_ID());
1034 -- Bug#5936252 : Invoke the function add_layout to specify the template type,code etc., before submitting request
1035 SELECT
1036 lower(iso_language),iso_territory
1037 INTO
1038 l_iso_language,l_iso_territory
1039 FROM
1040 FND_LANGUAGES
1041 WHERE
1042 language_code = USERENV('LANG');
1043
1044
1045 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Before calling FND_REQUEST.ADD_LAYOUT for LNS_AGREEMENT (Loan Agreement Report) for loan_id: ' || l_loan_header_rec.loan_id);
1046
1047 l_xml_output:= fnd_request.add_layout(
1048 template_appl_name => 'LNS',
1049 template_code => 'LNSRPTAG',
1050 template_language => l_iso_language,
1051 template_territory => l_iso_territory,
1052 output_format => 'PDF'
1053 );
1054 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' After calling FND_REQUEST.ADD_LAYOUT for LNS_AGREEMENT (Loan Agreement Report) for loan_id: ' || l_loan_header_rec.loan_id);
1055
1056
1057 l_request_id := FND_REQUEST.SUBMIT_REQUEST('LNS'
1058 ,'LNS_AGREEMENT'
1059 ,'', '', FALSE
1060 ,l_loan_header_rec.loan_id);
1061
1062 if l_request_id = 0 then
1063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1064 FND_MESSAGE.SET_NAME('LNS', 'LNS_AGREEMENT_REQUEST_FAILED');
1065 FND_MSG_PUB.Add;
1066 l_last_api_called := 'FND_REQUEST.SUBMIT_REQUEST for Loan Agreement Report Generation';
1067 RAISE FND_API.G_EXC_ERROR;
1068 else
1069 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Successfully submited Loan Agreement Report Generation Concurrent Program. Request id: ' || l_request_id);
1070 end if;
1071
1072 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' After calling FND_REQUEST.SUBMIT_REQUEST for LNS_AGREEMENT (Loan Agreement Report) for loan_id: ' || l_loan_header_rec.loan_id);
1073 /* end submit request to generate Loan Agreement Report */
1074
1075 end if;
1076
1077 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - End ');
1078
1079 EXCEPTION
1080 WHEN FND_API.G_EXC_ERROR THEN
1081 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'before rollback');
1082 ROLLBACK TO create_approval_action;
1083 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after rollback');
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1086 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1087 FND_MSG_PUB.ADD;
1088 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1089 p_count => x_msg_count,
1090 p_data => x_msg_data);
1091
1092 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1093 ROLLBACK TO create_approval_action;
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1095 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1096 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1097 FND_MSG_PUB.ADD;
1098 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data);
1101
1102 WHEN OTHERS THEN
1103 ROLLBACK TO create_approval_action;
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1106 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1107 FND_MSG_PUB.ADD;
1108 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1109 p_count => x_msg_count,
1110 p_data => x_msg_data);
1111
1112 END create_approval_action;
1113
1114 /*===========================================================================+
1115 | PROCEDURE
1116 | update_approval_action
1117 |
1118 | DESCRIPTION
1119 | Updates approval action.
1120 |
1121 | SCOPE - PUBLIC
1122 |
1123 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1124 |
1125 | ARGUMENTS : IN:
1126 | p_init_msg_list
1127 | p_approval_action_rec
1128 | OUT:
1129 | x_return_status
1130 | x_msg_count
1131 | x_msg_data
1132 | IN/OUT:
1133 | p_object_version_number
1134 |
1135 | RETURNS : NONE
1136 |
1137 | NOTES
1138 |
1139 | MODIFICATION HISTORY
1140 | 22-Jan-2004 Bernice Lam Created
1141 +===========================================================================*/
1142
1143 PROCEDURE update_approval_action (
1144 p_init_msg_list IN VARCHAR2,
1145 p_approval_action_rec IN APPROVAL_ACTION_REC_TYPE,
1146 p_object_version_number IN OUT NOCOPY NUMBER,
1147 x_return_status OUT NOCOPY VARCHAR2,
1148 x_msg_count OUT NOCOPY NUMBER,
1149 x_msg_data OUT NOCOPY VARCHAR2
1150 ) IS
1151
1152 l_api_name CONSTANT VARCHAR2(30) := 'update_approval_action';
1153 l_approval_action_rec APPROVAL_ACTION_REC_TYPE;
1154 l_old_approval_action_rec APPROVAL_ACTION_REC_TYPE;
1155 BEGIN
1156 l_approval_action_rec := p_approval_action_rec;
1157 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_approval_action procedure');
1158
1159 -- standard start of API savepoint
1160 SAVEPOINT update_approval_action;
1161
1162 -- initialize message list if p_init_msg_list is set to TRUE.
1163 IF FND_API.to_Boolean(p_init_msg_list) THEN
1164 FND_MSG_PUB.initialize;
1165 END IF;
1166
1167 -- initialize API return status to success.
1168 x_return_status := FND_API.G_RET_STS_SUCCESS;
1169 /*
1170 -- Get old record. Will be used by history package.
1171 get_approval_action_rec (
1172 p_action_id => l_approval_action_rec.action_id,
1173 x_approval_action_rec => l_old_approval_action_rec,
1174 x_return_status => x_return_status,
1175 x_msg_count => x_msg_count,
1176 x_msg_data => x_msg_data );
1177 */
1178 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1179 RAISE FND_API.G_EXC_ERROR;
1180 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1182 END IF;
1183
1184 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: Before call to do_update_approval_action proc');
1185
1186 -- call to business logic.
1187 do_update_approval_action(
1188 l_approval_action_rec,
1189 p_object_version_number,
1190 x_return_status);
1191
1192 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: After call to do_update_approval_action proc');
1193
1194 EXCEPTION
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 ROLLBACK TO update_approval_action;
1197 x_return_status := FND_API.G_RET_STS_ERROR;
1198 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1199 p_count => x_msg_count,
1200 p_data => x_msg_data);
1201
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 ROLLBACK TO update_approval_action;
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1206 p_count => x_msg_count,
1207 p_data => x_msg_data);
1208
1209 WHEN OTHERS THEN
1210 ROLLBACK TO update_approval_action;
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1213 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1214 FND_MSG_PUB.ADD;
1215 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data);
1218
1219 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_approval_action procedure');
1220
1221 END update_approval_action;
1222
1223 /*===========================================================================+
1224 | PROCEDURE
1225 | delete_approval_action
1226 |
1227 | DESCRIPTION
1228 | Deletes approval action
1229 |
1230 | SCOPE - PUBLIC
1231 |
1232 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1233 |
1234 | ARGUMENTS : IN:
1235 | p_init_msg_list
1236 | p_action_id
1237 | OUT:
1238 | x_return_status
1239 | x_msg_count
1240 | x_msg_data
1241 | IN/OUT:
1242 |
1243 | RETURNS : NONE
1244 |
1245 | NOTES
1246 |
1247 | MODIFICATION HISTORY
1248 | 06-Jan-2004 Bernice Lam Created.
1249 +===========================================================================*/
1250
1251 PROCEDURE delete_approval_action (
1252 p_init_msg_list IN VARCHAR2,
1253 p_action_id IN NUMBER,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_count OUT NOCOPY NUMBER,
1256 x_msg_data OUT NOCOPY VARCHAR2
1257 ) IS
1258
1259 l_api_name CONSTANT VARCHAR2(30) := 'delete_approval_action';
1260 l_action_id NUMBER;
1261
1262 BEGIN
1263 l_action_id := p_action_id;
1264 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_approval_action procedure');
1265
1266 -- standard start of API savepoint
1267 SAVEPOINT delete_approval_action;
1268
1269 -- initialize message list if p_init_msg_list is set to TRUE.
1270 IF FND_API.to_Boolean(p_init_msg_list) THEN
1271 FND_MSG_PUB.initialize;
1272 END IF;
1273
1274 -- initialize API return status to success.
1275 x_return_status := FND_API.G_RET_STS_SUCCESS;
1276
1277 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: Before call to do_delete_approval_action proc');
1278
1279 -- call to business logic.
1280 do_delete_approval_action(
1281 l_action_id,
1282 x_return_status);
1283
1284 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: After call to do_delete_approval_action proc');
1285
1286 EXCEPTION
1287 WHEN FND_API.G_EXC_ERROR THEN
1288 ROLLBACK TO delete_approval_action;
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1291 p_count => x_msg_count,
1292 p_data => x_msg_data);
1293
1294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1295 ROLLBACK TO delete_approval_action;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data);
1300
1301 WHEN OTHERS THEN
1302 ROLLBACK TO delete_approval_action;
1303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1305 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1306 FND_MSG_PUB.ADD;
1307 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1308 p_count => x_msg_count,
1309 p_data => x_msg_data);
1310
1311 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_approval_action procedure');
1312
1313 END delete_approval_action;
1314
1315 END LNS_APPROVAL_ACTION_PUB;