[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.58.12020000.3 2013/02/21 21:12:28 scherkas 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 g_last_all_statements CLOB;
13
14 --------------------------------------------------
15 -- declaration of private procedures and functions
16 --------------------------------------------------
17
18 procedure logMessage(log_level in number
19 ,module in varchar2
20 ,message in varchar2)
21 is
22
23 begin
24
25 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
26 FND_LOG.STRING(log_level, module, message);
27 END IF;
28
29 end;
30
31 PROCEDURE do_create_approval_action (
32 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
33 ,x_action_id OUT NOCOPY NUMBER
34 ,x_return_status IN OUT NOCOPY VARCHAR2
35 );
36
37 PROCEDURE do_update_approval_action (
38 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
39 ,p_object_version_number IN OUT NOCOPY NUMBER
40 ,x_return_status IN OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE do_delete_approval_action (
44 p_action_id IN NUMBER
45 ,x_return_status IN OUT NOCOPY VARCHAR2
46 );
47
48 /*===========================================================================+
49 | PROCEDURE
50 | do_create_approval_action
51 |
52 | DESCRIPTION
53 | Creates approval action.
54 |
55 | SCOPE - PRIVATE
56 |
57 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
58 |
59 | ARGUMENTS : IN:
60 | OUT:
61 | x_action_id
62 | IN/OUT:
63 | p_approval_action_rec
64 | x_return_status
65 |
66 | RETURNS : NONE
67 |
68 | NOTES
69 |
70 | MODIFICATION HISTORY
71 |
72 | 22-Jan-2004 Bernice Lam Created.
73 +===========================================================================*/
74
75 PROCEDURE do_create_approval_action(
76 p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
77 ,x_action_id OUT NOCOPY NUMBER
78 ,x_return_status IN OUT NOCOPY VARCHAR2
79 ) IS
80
81 l_action_id NUMBER;
82 l_rowid ROWID := NULL;
83 l_dummy VARCHAR2(1);
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2(2000);
86
87 BEGIN
88 l_action_id := p_approval_action_rec.action_id;
89 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_approval_action procedure');
90
91 -- if primary key value is passed, check for uniqueness.
92 IF l_action_id IS NOT NULL AND
93 l_action_id <> FND_API.G_MISS_NUM
94 THEN
95 BEGIN
96 SELECT 'Y'
97 INTO l_dummy
98 FROM LNS_APPROVAL_ACTIONS
99 WHERE action_id = l_action_id;
100
101 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
102 FND_MESSAGE.SET_TOKEN('COLUMN', 'action_id');
103 FND_MSG_PUB.ADD;
104 RAISE FND_API.G_EXC_ERROR;
105
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 NULL;
109 END;
110 END IF;
111
112 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Insert_Row');
113
114 -- call table-handler.
115 LNS_APPROVAL_ACTIONS_PKG.Insert_Row(X_ACTION_ID => p_approval_action_rec.action_id
116 ,P_OBJECT_VERSION_NUMBER => 1
117 ,P_LOAN_ID => p_approval_action_rec.loan_id
118 ,P_ACTION_TYPE => p_approval_action_rec.action_type
119 ,P_AMOUNT => p_approval_action_rec.amount
120 ,P_REASON_CODE => p_approval_action_rec.reason_code
121 ,P_ATTRIBUTE_CATEGORY => p_approval_action_rec.attribute_category
122 ,P_ATTRIBUTE1 => p_approval_action_rec.attribute1
123 ,P_ATTRIBUTE2 => p_approval_action_rec.attribute2
124 ,P_ATTRIBUTE3 => p_approval_action_rec.attribute3
125 ,P_ATTRIBUTE4 => p_approval_action_rec.attribute4
126 ,P_ATTRIBUTE5 => p_approval_action_rec.attribute5
127 ,P_ATTRIBUTE6 => p_approval_action_rec.attribute6
128 ,P_ATTRIBUTE7 => p_approval_action_rec.attribute7
129 ,P_ATTRIBUTE8 => p_approval_action_rec.attribute8
130 ,P_ATTRIBUTE9 => p_approval_action_rec.attribute9
131 ,P_ATTRIBUTE10 => p_approval_action_rec.attribute10
132 ,P_ATTRIBUTE11 => p_approval_action_rec.attribute11
133 ,P_ATTRIBUTE12 => p_approval_action_rec.attribute12
134 ,P_ATTRIBUTE13 => p_approval_action_rec.attribute13
135 ,P_ATTRIBUTE14 => p_approval_action_rec.attribute14
136 ,P_ATTRIBUTE15 => p_approval_action_rec.attribute15
137 ,P_ATTRIBUTE16 => p_approval_action_rec.attribute16
138 ,P_ATTRIBUTE17 => p_approval_action_rec.attribute17
139 ,P_ATTRIBUTE18 => p_approval_action_rec.attribute18
140 ,P_ATTRIBUTE19 => p_approval_action_rec.attribute19
141 ,P_ATTRIBUTE20 => p_approval_action_rec.attribute20);
142
143 x_action_id := p_approval_action_rec.action_id;
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 l_offset number(38);
457 l_statement_xml clob;
458 x_billed_yn varchar2(1);
459 l_fee_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
460 l_appr_dist_cnt NUMBER;
461 l_return_status VARCHAR2(1);
462 l_msg_count NUMBER;
463 l_msg_data VARCHAR2(32767);
464 l_bill_on_appr_amounts varchar2(1);
465
466 CURSOR C_Get_Loan_Info (X_Loan_Id NUMBER) IS
467 SELECT H.OBJECT_VERSION_NUMBER
468 ,H.LOAN_STATUS
469 ,H.LOAN_CURRENCY
470 ,H.LOAN_NUMBER
471 ,H.LOAN_CLASS_CODE
472 ,H.LOAN_TYPE
473 ,H.GL_DATE
474 ,H.REQUESTED_AMOUNT
475 ,H.REFERENCE_TYPE
476 ,H.REFERENCE_ID
477 ,H.CURRENT_PHASE
478 ,H.MULTIPLE_FUNDING_FLAG
479 ,H.OPEN_TO_TERM_FLAG
480 ,NVL(P.BDGT_REQ_FOR_APPR_FLAG, 'N') BDGT_REQ_FOR_APPR_FLAG
481 ,NVL(P.LOAN_APPR_REQ_FLAG, 'Y') LOAN_APPR_REQ_FLAG
482 ,H.SECONDARY_STATUS
483 ,nvl(custom_payments_flag, 'N')
484 FROM LNS_LOAN_HEADERS_ALL H,
485 LNS_LOAN_PRODUCTS_ALL P
486 WHERE H.LOAN_ID = X_Loan_Id
487 and H.product_id = P.loan_product_id(+);
488
489 CURSOR C_Get_Resource_Id (X_User_Id NUMBER) IS
490 SELECT RESOURCE_ID
491 FROM JTF_RS_RESOURCE_EXTNS
492 WHERE USER_ID = X_USER_ID;
493
494 CURSOR C_Get_Distribution (X_Loan_Id NUMBER, X_Acct_Type VARCHAR2, X_Acct_Name VARCHAR2, X_Line_Type VARCHAR2) IS
495 select DISTRIBUTION_ID
496 ,CODE_COMBINATION_ID
497 ,DISTRIBUTION_PERCENT
498 from lns_distributions
499 where LOAN_ID = x_loan_id
500 and account_type = x_acct_type
501 and account_name = x_acct_name
502 and line_type = x_line_type
503 and distribution_type = 'ORIGINATION';
504
505 cursor c_sob_id is
506 select so.set_of_books_id
507 from lns_system_options sb,
508 gl_sets_of_books so
509 where sb.set_of_books_id = so.set_of_books_id;
510
511 /* query term version */
512 CURSOR term_version_cur(P_LOAN_ID number) IS
513 select TERM_ID
514 ,OBJECT_VERSION_NUMBER
515 from LNS_TERMS
516 where LOAN_ID = P_LOAN_ID;
517
518 CURSOR loan_version_cur(P_LOAN_ID number) IS
519 select OBJECT_VERSION_NUMBER
520 from LNS_LOAN_HEADERS
521 where LOAN_ID = P_LOAN_ID;
522
523 -- getting loan previous secondary status to reset if Conversion request is rejected
524 CURSOR prev_sec_status_cur(P_LOAN_ID number) IS
525 select old_value
526 from lns_loan_histories_h
527 where table_name = 'LNS_LOAN_HEADERS_ALL' and
528 column_name = 'SECONDARY_STATUS' and
529 new_value = 'PENDING_CANCELLATION' and
530 loan_id = P_LOAN_ID and
531 loan_history_id =
532 (select max(loan_history_id)
533 from lns_loan_histories_h
534 where table_name = 'LNS_LOAN_HEADERS_ALL' and
535 column_name = 'SECONDARY_STATUS' and
536 loan_id = P_LOAN_ID);
537
538 /* get statement after its billed */
539 CURSOR get_statement_cur(P_LOAN_ID number) IS
540 select STATEMENT_XML
541 from LNS_LOAN_HEADERS loan,
542 lns_amortization_scheds am
543 where loan.loan_id = am.loan_id and
544 am.AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID and
545 am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and
546 loan.loan_id = P_LOAN_ID;
547
548 CURSOR cur_getApprDistCount(C_LOAN_ID Number) IS
549 SELECT count(1)
550 FROM lns_distributions dist
551 WHERE dist.loan_id = C_LOAN_ID
552 AND dist.activity = 'LNS_APPROVAL';
553
554 cursor c_get_bill_opt(p_loan_id number) is
555 select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
556 from lns_loan_headers_all
557 where loan_id = p_loan_id;
558
559 cursor c_gl_date (p_loan_id number) is
560 select adj.gl_date
561 from ar_adjustments_all adj, lns_loan_lines lines
562 where lines.loan_id = p_loan_id
563 and lines.STATUS = 'APPROVED'
564 and lines.end_date is null
565 and lines.rec_adjustment_id = adj.adjustment_id
566 order by loan_line_id;
567
568 BEGIN
569 l_last_api_called := '';
570 l_approval_action_rec := p_approval_action_rec;
571 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Begin');
572 -- standard start of API savepoint
573 SAVEPOINT create_approval_action;
574
575 -- initialize message list if p_init_msg_list is set to TRUE.
576 IF FND_API.to_Boolean(p_init_msg_list) THEN
577 FND_MSG_PUB.initialize;
578 END IF;
579
580 -- initialize API return status to success.
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582
583
584 -- START OF BODY OF API
585 /* init all_statements clob */
586 dbms_lob.createtemporary(g_last_all_statements, FALSE, DBMS_LOB.CALL);
587 dbms_lob.open(g_last_all_statements, dbms_lob.lob_readwrite);
588
589 -- init;
590
591 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to do_create_approval_action proc');
592 -- call to business logic.
593 IF l_approval_action_rec.action_type <> 'APPROVE_SINGLE' THEN
594 do_create_approval_action(l_approval_action_rec
595 ,x_action_id
596 ,x_return_status);
597
598 END IF;
599 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to do_create_approval_action proc');
600 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_LOAN_HEADER_PUB.update_loan proc');
601 OPEN C_Get_Loan_Info(l_approval_action_rec.loan_id);
602 FETCH C_Get_Loan_Info
603 INTO l_object_version_number
604 ,l_status
605 ,l_currency_code
606 ,l_loan_number
607 ,l_loan_class_code
608 ,l_loan_type
609 ,l_gl_date
610 ,l_requested_amt
611 ,l_reference_type
612 ,l_reference_id
613 ,l_current_phase
614 ,l_multiple_funding_flag
615 ,l_open_to_term_flag
616 ,l_budget_req_approval
617 ,l_loan_needs_approval
618 ,l_secondary_status
619 ,l_customized;
620 IF C_Get_Loan_Info%NOTFOUND THEN
621 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
622 FND_MESSAGE.SET_TOKEN('RECORD', 'loan header');
623 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(l_loan_header_rec.loan_id), 'null'));
624 FND_MSG_PUB.ADD;
625 CLOSE C_Get_Loan_Info;
626 l_last_api_called := 'C_Get_Loan_Info';
627 RAISE FND_API.G_EXC_ERROR;
628 END IF;
629 CLOSE C_Get_Loan_Info;
630 l_prev_loan_status := l_status;
631
632 OPEN C_Get_Resource_Id(LNS_UTILITY_PUB.Created_By);
633 FETCH C_Get_Resource_Id INTO l_resource_id;
634 CLOSE C_Get_Resource_Id;
635
636 -- validate status transitions
637 l_loan_header_rec.loan_id := l_approval_action_rec.loan_id;
638 IF (l_approval_action_rec.action_type = 'SUBMIT_FOR_APPR') THEN
639 IF l_status = 'INCOMPLETE' THEN
640 l_loan_header_rec.loan_status := 'PENDING';
641 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
642
643 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to generate bill for SubmtForApproval Fee');
644
645 LNS_BILLING_BATCH_PUB.BILL_SING_LOAN_SUBMIT_APPR_FEE(P_API_VERSION => 1.0
646 ,P_INIT_MSG_LIST => FND_API.G_TRUE
647 ,P_COMMIT => FND_API.G_FALSE
648 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
649 ,P_LOAN_ID => l_approval_action_rec.loan_id
650 ,X_BILLED_YN => x_billed_yn
651 ,X_RETURN_STATUS => x_return_status
652 ,X_MSG_COUNT => x_msg_count
653 ,X_MSG_DATA => x_msg_data);
654
655 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
656 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_BILLING_BATCH_PUB.BILL_SUBMIT_APPROVAL_FEE failed with status ' || x_return_status);
657
658 l_last_api_called := 'LNS_BILLING_BATCH_PUB.BILL_SUBMIT_APPROVAL_FEE';
659 RAISE FND_API.G_EXC_ERROR;
660
661 ELSE
662 /* get statement after its billed */
663 open get_statement_cur(l_approval_action_rec.loan_id);
664 fetch get_statement_cur into l_statement_xml;
665 close get_statement_cur;
666
667 IF (l_statement_xml IS NULL) THEN
668 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'There is no xml for Bill Statement');
669 ELSE
670 /* remove xml header */
671 l_offset := DBMS_LOB.INSTR(lob_loc => l_statement_xml,
672 pattern => '>',
673 offset => 1,
674 nth => 1);
675 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Have removed header from the statement');
676 dbms_lob.Append(g_last_all_statements, l_statement_xml);
677 END IF;
678
679 END IF;
680
681 ELSE
682 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
683 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
684 FND_MSG_PUB.ADD;
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 ELSIF (l_approval_action_rec.action_type = 'SUBMIT_FOR_CONV') THEN
689 IF (l_status = 'ACTIVE' AND l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y') THEN
690 l_loan_header_rec.secondary_status := 'PENDING_CONVERSION';
691 ELSE
692 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
693 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
694 FND_MSG_PUB.ADD;
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697
698 ELSIF (l_approval_action_rec.action_type = 'SUBMIT_FOR_CNCL') THEN
699 IF (l_loan_class_code = 'DIRECT') THEN
700 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE');
701
702 LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE(P_API_VERSION => 1.0
703 ,P_INIT_MSG_LIST => FND_API.G_TRUE
704 ,P_COMMIT => FND_API.G_FALSE
705 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
706 ,P_LOAN_ID => l_loan_header_rec.loan_id
707 ,X_RETURN_STATUS => x_return_status
708 ,X_MSG_COUNT => x_msg_count
709 ,X_MSG_DATA => x_msg_data);
710
711 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE');
712
713 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
714 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE failed with status ' || x_return_status);
715 l_last_api_called := 'LNS_FUNDING_PUB.CANCEL_DISB_SCHEDULE';
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718 ELSE
719 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
720 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
721 FND_MSG_PUB.ADD;
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724
725 ELSIF (l_approval_action_rec.action_type = 'APPROVE') THEN
726
727 -- approval action type = 'APPROVE'
728 IF ( l_status = 'PENDING' OR (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') ) THEN
729
730 if l_loan_class_code = 'ERS' then
731
732 -- use resource ID
733 l_loan_header_rec.loan_approved_by := l_resource_id;
734 l_loan_header_rec.loan_approval_date := sysdate;
735 --if (l_gl_date is null) then
736 -- CMS requirement karamach bug5129367
737 l_loan_header_rec.gl_date := sysdate;
738 --end if;
739 l_loan_header_rec.loan_status := 'APPROVED';
740 l_loan_header_rec.secondary_status := 'UNACCOUNTED'; --- raverma added new secondary status
741 l_loan_header_rec.funded_amount := l_requested_amt;
742 l_loan_header_rec.initial_loan_balance := l_requested_amt;
743
744 elsif l_loan_class_code = 'DIRECT' then
745
746 l_loan_header_rec.loan_status := 'APPROVED';
747 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
748 -- use resource ID
749 l_loan_header_rec.loan_approved_by := l_resource_id;
750 l_loan_header_rec.loan_approval_date := sysdate;
751 --if (l_gl_date is null) then
752 -- CMS requirement karamach bug5129367
753 l_loan_header_rec.gl_date := sysdate;
754 --end if;
755
756 end if;
757
758 -- approval action type = 'APPROVE'
759 ELSIF l_status = 'PENDING_CANCELLATION' THEN
760
761 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB');
762
763 LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB(P_API_VERSION => 1.0
764 ,P_INIT_MSG_LIST => FND_API.G_TRUE
765 ,P_COMMIT => FND_API.G_FALSE
766 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
767 ,P_LOAN_ID => l_loan_header_rec.loan_id
768 ,X_RETURN_STATUS => x_return_status
769 ,X_MSG_COUNT => x_msg_count
770 ,X_MSG_DATA => x_msg_data);
771
772 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB');
773
774 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
775 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB failed with status ' || x_return_status);
776 l_last_api_called := 'LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB';
777 RAISE FND_API.G_EXC_ERROR;
778 END IF;
779
780 -- approval action type = 'APPROVE'
781 ELSIF (l_status = 'ACTIVE' AND l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y') THEN
782
783 /**********************Begin Permanent Conversion to Term Phase*************/
784 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calling LNS_FINANCIALS.shiftLoanDates');
785
786 -- we are moving the loan to the TERM PHASE -- first shift the dates based on conversion date
787 -- Bug#6169438 Added new parameter to the shiftLoanDates API Invocation
788 LNS_FINANCIALS.shiftLoanDates(p_loan_id => l_loan_header_rec.loan_id
789 ,p_new_start_date => sysdate
790 ,p_phase => 'TERM'
791 ,x_loan_details => l_loan_details
792 ,x_dates_shifted_flag => l_dates_shifted_flag
793 ,x_return_status => x_return_status
794 ,x_msg_count => x_msg_count
795 ,x_msg_data => x_msg_data);
796
797 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
798 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - LNS_FINANCIALS.shiftLoanDates failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
799 l_last_api_called := 'LNS_FINANCIALS.shiftLoanDates';
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New loan start date: ' || l_loan_details.loan_start_date);
804 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New first payment date: ' || l_loan_details.first_payment_Date);
805 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'New maturity date: ' || l_loan_details.maturity_date);
806
807 /* query term version */
808 open term_version_cur(l_loan_header_rec.loan_id);
809 fetch term_version_cur into l_TERM_ID, l_TERM_VERSION_NUMBER;
810 close term_version_cur;
811
812 /* setting term data for do term update */
813 l_term_rec.TERM_ID := l_TERM_ID;
814 l_term_rec.LOAN_ID := l_loan_header_rec.loan_id;
815 l_term_rec.FIRST_PAYMENT_DATE := l_loan_details.first_payment_Date;
816 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_loan_details.first_payment_Date;
817
818 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updating lns_terms w following values:');
819 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'TERM_ID: ' || l_term_rec.TERM_ID);
820 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_ID: ' || l_term_rec.LOAN_ID);
821 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'FIRST_PAYMENT_DATE: ' || l_term_rec.FIRST_PAYMENT_DATE);
822 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'NEXT_PAYMENT_DUE_DATE: ' || l_term_rec.NEXT_PAYMENT_DUE_DATE);
823
824 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER
825 ,p_init_msg_list => FND_API.G_FALSE
826 ,p_loan_term_rec => l_term_rec
827 ,X_RETURN_STATUS => x_return_status
828 ,X_MSG_COUNT => x_msg_count
829 ,X_MSG_DATA => x_msg_data);
830
831 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'x_return_status: ' || x_return_status);
832
833 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
834 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Successfully update LNS_TERMS');
835 ELSE
836 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
837 FND_MSG_PUB.Add;
838 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'));
839 l_last_api_called := 'LNS_TERMS_PUB.update_term';
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842
843 /* setting data for future loan update */
844 l_loan_header_rec.LOAN_START_DATE := l_loan_details.loan_start_date;
845 l_loan_header_rec.LOAN_MATURITY_DATE := l_loan_details.maturity_date;
846 l_loan_header_rec.current_phase := 'TERM';
847 l_loan_header_rec.secondary_status := 'CONVERTED_TO_TERM_PHASE';
848 l_loan_header_rec.LAST_PAYMENT_NUMBER := FND_API.G_MISS_NUM;
849 l_loan_header_rec.LAST_AMORTIZATION_ID := FND_API.G_MISS_NUM;
850
851 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Data to Update loan header with during conversion...');
852 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'loan_id: ' || l_loan_header_rec.loan_id);
853 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_START_DATE: ' || l_loan_header_rec.LOAN_START_DATE);
854 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN_MATURITY_DATE: ' || l_loan_header_rec.LOAN_MATURITY_DATE);
855 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'current_phase: ' || l_loan_header_rec.current_phase);
856 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'secondary_status: ' || l_loan_header_rec.secondary_status);
857 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LAST_PAYMENT_NUMBER: ' || l_loan_header_rec.LAST_PAYMENT_NUMBER);
858 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LAST_AMORTIZATION_ID: ' || l_loan_header_rec.LAST_AMORTIZATION_ID);
859
860 /* Bug#9255294 - No need to call this now, as the below method inserts records of conversionFees into feeSchds table
861 However, now the conversionFees insert into feeScheds when this fee is assigned to the loan
862
863 --Process Conversion Fees
864 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before calling lns_fee_engine.processDisbursementFees to process Conversion fees for permanent conversion to TERM phase');
865 lns_fee_engine.processDisbursementFees(p_init_msg_list => FND_API.G_TRUE
866 ,p_commit => FND_API.G_FALSE
867 ,p_phase => 'TERM'
868 ,p_loan_id => l_loan_header_rec.loan_id
869 ,p_disb_head_id => NULL
870 ,x_return_status => x_return_status
871 ,x_msg_count => x_msg_count
872 ,x_msg_data => x_msg_data);
873
874 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');
875
876 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
877 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'));
878 l_last_api_called := 'lns_fee_engine.processDisbursementFees';
879 RAISE FND_API.G_EXC_ERROR;
880 END IF;
881 */
882 /**********************End Permanent Conversion to Term Phase*************/
883
884 ELSE
885 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
886 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
887 FND_MSG_PUB.ADD;
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890
891 ELSIF (l_approval_action_rec.action_type = 'REJECT') THEN
892 IF ( l_status = 'PENDING' OR (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') ) THEN
893 l_loan_header_rec.loan_status := 'REJECTED';
894 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
895 ELSIF (l_status = 'PENDING_CANCELLATION') THEN
896 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
897
898 LNS_FUNDING_PUB.REJECT_CANCEL_DISB(P_API_VERSION => 1.0
899 ,P_INIT_MSG_LIST => FND_API.G_TRUE
900 ,P_COMMIT => FND_API.G_FALSE
901 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
902 ,P_LOAN_ID => l_loan_header_rec.loan_id
903 ,X_RETURN_STATUS => x_return_status
904 ,X_MSG_COUNT => x_msg_count
905 ,X_MSG_DATA => x_msg_data);
906
907 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
908
909 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
910 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LNS_FUNDING_PUB.REJECT_CANCEL_DISB failed with status ' || x_return_status);
911 l_last_api_called := 'LNS_FUNDING_PUB.REJECT_CANCEL_DISB';
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914 ELSIF (l_status = 'ACTIVE' and l_secondary_status = 'PENDING_CONVERSION') THEN
915 -- getting loan previous status
916 open prev_sec_status_cur(l_loan_header_rec.loan_id);
917 fetch prev_sec_status_cur into l_prev_sec_status;
918 close prev_sec_status_cur;
919 l_loan_header_rec.secondary_status := nvl(l_prev_sec_status,FND_API.G_MISS_CHAR);
920 ELSE
921 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
922 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
923 FND_MSG_PUB.ADD;
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927 ELSIF (l_approval_action_rec.action_type = 'REQUEST_FOR_INFO') THEN
928
929 IF (l_status = 'INCOMPLETE' AND l_loan_needs_approval = 'N') THEN
930 --Loan is already in INCOMPLETE status, so do nothing
931 null;
932 ELSIF l_status = 'PENDING' THEN
933 l_loan_header_rec.loan_status := 'INCOMPLETE';
934
935 /* Bug#8937530get Billed and Reversed 'Submit For Approval' Fees using getSubmitForApprFeeSchedule*/
936 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling Billed LNS_FEES_ENGINE.getSubmitForApprFeeSchedule...');
937
938 LNS_FEE_ENGINE.getSubmitForApprFeeSchedule(p_init_msg_list => FND_API.G_TRUE,
939 p_loan_Id => l_approval_action_rec.loan_id,
940 p_billed_flag => 'Y',
941 x_fees_tbl => l_fee_tbl,
942 X_RETURN_STATUS => l_return_status,
943 X_MSG_COUNT => l_msg_count,
944 X_MSG_DATA => l_msg_data);
945
946 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
947 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_msg_data: ' || substr(l_msg_data,1,225));
948
949 if l_return_status <> 'S' then
950 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Failed in API LNS_FEES_ENGINE.getSubmitForApprFeeSchedule');
951 RAISE FND_API.G_EXC_ERROR;
952 end if;
953
954 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Toal No. of Eligible Billed and Reversed SubmitApproval Fees are: ' || l_fee_tbl.count);
955
956 FOR l_Count IN 1..l_fee_tbl.count LOOP
957 LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_tbl(l_Count).fee_schedule_id
958 ,P_FEE_ID => null
959 ,P_LOAN_ID => l_approval_action_rec.loan_id
960 ,P_FEE_AMOUNT => null
961 ,P_FEE_INSTALLMENT => null
962 ,P_FEE_DESCRIPTION => null
963 ,P_ACTIVE_FLAG => null
964 ,P_BILLED_FLAG => 'N' -- Make BilledFlag to 'N'
965 ,P_FEE_WAIVABLE_FLAG => null
966 ,P_WAIVED_AMOUNT => null
967 ,P_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY
968 ,P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE
969 ,P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
970 ,P_PROGRAM_ID => null
971 ,P_REQUEST_ID => null
972 ,P_OBJECT_VERSION_NUMBER => null);
973 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' fee_schedule_id : '|| l_fee_tbl(l_Count).fee_schedule_id||' updated succesfully ');
974
975 END LOOP;
976 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Toal No. of Billed and Reversed SubmitApproval Fees are: ' || l_fee_tbl.count);
977
978 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Uncompleting all custom conditions...');
979 update LNS_COND_ASSIGNMENTS
980 set CONDITION_MET_FLAG = 'N',
981 FULFILLMENT_DATE = null,
982 FULFILLMENT_UPDATED_BY = null
983 where LOAN_ID = l_approval_action_rec.loan_id
984 and OWNER_OBJECT_ID is null
985 and OWNER_TABLE is null
986 and end_date_active is null
987 and CONDITION_ID in
988 (select CONDITION_ID
989 from LNS_CONDITIONS
990 where CONDITION_TYPE = 'APPROVAL'
991 and CUSTOM_PROCEDURE is not null);
992 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
993
994
995 ELSIF (l_status = 'PENDING_CANCELLATION') THEN
996 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
997
998 LNS_FUNDING_PUB.REJECT_CANCEL_DISB(P_API_VERSION => 1.0
999 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1000 ,P_COMMIT => FND_API.G_FALSE
1001 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
1002 ,P_LOAN_ID => l_loan_header_rec.loan_id
1003 ,X_RETURN_STATUS => x_return_status
1004 ,X_MSG_COUNT => x_msg_count
1005 ,X_MSG_DATA => x_msg_data);
1006
1007 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - AFTER call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB');
1008
1009 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1010 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_FUNDING_PUB.REJECT_CANCEL_DISB failed with status ' || x_return_status);
1011 l_last_api_called := 'LNS_FUNDING_PUB.REJECT_CANCEL_DISB';
1012 RAISE FND_API.G_EXC_ERROR;
1013 END IF;
1014
1015 --Loan Status change is already handled in the above procedure call. no need to update loan header again
1016 ELSIF l_status = 'ACTIVE' then
1017 if l_current_phase = 'OPEN' AND l_multiple_funding_flag = 'Y' AND l_open_to_term_flag = 'Y' THEN
1018 l_loan_header_rec.secondary_status := 'MORE_INFO_REQUESTED';
1019 end if;
1020 ELSE
1021 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_INVALID_STATUS');
1022 FND_MESSAGE.SET_TOKEN('VALUE', l_status);
1023 FND_MSG_PUB.ADD;
1024 RAISE FND_API.G_EXC_ERROR;
1025 END IF;
1026 --This code is passed from workflow AME Approval process for just creating Loan Approval Action
1027 --for the current approver.
1028 ELSIF (l_approval_action_rec.action_type = 'APPROVE_SINGLE') THEN
1029 -- Create an Approval Actions with 'APPROVE'
1030
1031 IF (l_status = 'PENDING' ) THEN
1032 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Approval Action created for action_type = APPROVE_SINGLE');
1033 --Change Action Type = 'APPROVE' instead of current value of 'APPROVE_SINGLE'
1034 l_approval_action_rec.action_type := 'APPROVE';
1035 do_create_approval_action(l_approval_action_rec
1036 ,x_action_id
1037 ,x_return_status);
1038 ELSE
1039 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Incorrect Loan status ' || l_status || ' for action_type = APPROVE_SINGLE');
1040 END IF;
1041 END IF;
1042
1043 /* 08-12-2005 raverma added defaultDistributions call for DIRECT loan */
1044 --Question for Raj:
1045 --Do we need to check for l_prev_loan_status = 'PENDING' to call code below on initial loan approval only?
1046 IF (l_approval_action_rec.action_type = 'APPROVE' and
1047 (l_loan_header_rec.loan_status = 'ACTIVE' or l_loan_header_rec.loan_status = 'APPROVED') and
1048 l_loan_class_code = 'DIRECT')
1049 THEN
1050
1051 -- Bug#7406404 - Distributions defaulted at Loan Creation Time. And also the
1052 -- distributions at Loan Level are updated for 'Incomplete' loans. Before implementing this feature,
1053 -- there might be incomplete loans, which doesn't have dist records created at LoanCreation time
1054
1055 OPEN cur_getApprDistCount(l_loan_header_rec.loan_id);
1056 FETCH cur_getApprDistCount INTO l_appr_dist_cnt;
1057 CLOSE cur_getApprDistCount;
1058
1059 IF (l_appr_dist_cnt < 2) THEN
1060
1061 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - before default distributions');
1062 Lns_distributions_pub.defaultDistributions(p_api_version => 1.0
1063 ,p_init_msg_list => FND_API.G_TRUE
1064 ,p_commit => FND_API.G_FALSE
1065 ,p_loan_id => l_loan_header_rec.loan_id
1066 ,p_loan_class_code => l_loan_class_code
1067 ,x_return_status => x_return_status
1068 ,x_msg_count => x_msg_count
1069 ,x_msg_data => x_msg_data);
1070 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after default distributions ' || x_return_status);
1071 END IF;
1072
1073 -- Bug#9685116 - Only for federal/encumbrance customers, it should do the budgetary_control
1074 -- Bug#10126147 - For Encumbrance also
1075 IF (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' OR lns_utility_pub.IS_ENCUM_FIN_ENABLED = 'Y') THEN
1076 begin
1077 l_last_api_called := null;
1078 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'calling lns_distributions_pub.budgetary_control');
1079 lns_distributions_pub.budgetary_control(p_init_msg_list => FND_API.G_FALSE
1080 ,p_commit => FND_API.G_FALSE
1081 ,p_loan_id => l_approval_action_rec.loan_id
1082 ,p_budgetary_control_mode => 'R'
1083 ,x_budgetary_status_code => l_budgetary_status
1084 ,x_return_status => x_return_status
1085 ,x_msg_count => x_msg_count
1086 ,x_msg_data => x_msg_data);
1087 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to Lns_distributions_pub.budgetary_control return status ' || x_return_status);
1088 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_budgetary_status' || l_budgetary_status);
1089 --FND_MSG_PUB.initialize;
1090 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1091 RAISE FND_API.G_EXC_ERROR;
1092
1093 else -- BC Call Returned 'S'
1094 if l_budgetary_status = 'ADVISORY' or l_budgetary_status = 'SUCCESS' then
1095 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'budget reserved');
1096 l_loan_header_rec.secondary_status := 'BUDGET_RESERVED';
1097
1098 elsif l_budgetary_status = 'FAIL' or l_budgetary_status = 'PARTIAL' or l_budgetary_status = 'XLA_ERROR' then
1099 if l_budget_req_approval = 'Y' then
1100 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'budget required: NO BUDGET');
1101 RAISE FND_API.G_EXC_ERROR;
1102 else
1103 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'NO BUDGET');
1104 FND_MSG_PUB.initialize;
1105 l_loan_header_rec.secondary_status := 'NO_BUDGET';
1106 end if;
1107
1108 end if;
1109
1110 end if;
1111 end;
1112 END IF; -- if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y')
1113
1114 END IF;
1115
1116 --if loan status is cancelled, the funding api to cancel remaining disbursements would have updated loan header already no need to do it again
1117
1118 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
1119
1120 -- fix for bug 6133313: if this loan is customized then set ORIG_PAY_CALC_METHOD to null
1121 if l_customized = 'Y' then
1122
1123 -- getting terms version for future update
1124 select term_id, object_version_number into l_term_id, l_object_version_number
1125 from lns_terms
1126 where loan_id = l_loan_header_rec.loan_id;
1127
1128 -- Updating terms
1129 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1130 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms...');
1131 END IF;
1132
1133 l_term_rec.TERM_ID := l_term_id;
1134 l_term_rec.LOAN_ID := l_loan_header_rec.loan_id;
1135 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
1136
1137 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version_number,
1138 p_init_msg_list => FND_API.G_FALSE,
1139 p_loan_term_rec => l_term_rec,
1140 X_RETURN_STATUS => x_return_status,
1141 X_MSG_COUNT => x_msg_count,
1142 X_MSG_DATA => x_msg_data);
1143
1144
1145 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || x_return_status);
1146 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148
1149 ELSE
1150 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
1151 END IF;
1152
1153 end if;
1154
1155 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to submit adjustment request');
1156 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1157
1158 LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE(P_API_VERSION => 1.0
1159 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1160 ,P_COMMIT => FND_API.G_FALSE
1161 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
1162 ,P_LOAN_ID => l_loan_header_rec.loan_id
1163 ,X_RETURN_STATUS => x_return_status
1164 ,X_MSG_COUNT => x_msg_count
1165 ,X_MSG_DATA => x_msg_data);
1166
1167 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1168 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Call to LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE failed with status ' || x_return_status);
1169 l_last_api_called := 'LNS_BILLING_BATCH_PUB.ADJUST_ORIGINAL_RECEIVABLE';
1170 RAISE FND_API.G_EXC_ERROR;
1171
1172 END IF;
1173
1174 -- fix for bug 16278332: set new loan gl_date
1175 open c_gl_date(l_loan_header_rec.loan_id);
1176 fetch c_gl_date into l_loan_header_rec.gl_date;
1177 close c_gl_date;
1178 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'new l_gl_date = ' || l_loan_header_rec.gl_date);
1179
1180 end if;
1181
1182 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1183
1184 -- moved the defaultDistributions call to Concurrent Process
1185 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to submit gen distributions request');
1186 l_notify := FND_REQUEST.ADD_NOTIFICATION(FND_GLOBAL.USER_NAME);
1187 --call fnd_request_api....
1188 FND_REQUEST.SET_ORG_ID(MO_GLOBAL.GET_CURRENT_ORG_ID());
1189 l_request_id := FND_REQUEST.SUBMIT_REQUEST('LNS'
1190 ,'LNS_GEN_DIST'
1191 ,'', '', FALSE
1192 ,l_loan_header_rec.loan_id);
1193 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after call to submit gen distributions request ' || l_request_id);
1194
1195 end if; --END if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1196
1197 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
1198
1199 if (x_return_status = FND_API.G_RET_STS_SUCCESS AND
1200 l_approval_action_rec.action_type = 'APPROVE' and
1201 (l_loan_header_rec.loan_status = 'ACTIVE' or l_loan_header_rec.loan_status = 'APPROVED')) then
1202
1203 -- generate Loan Agreement Report
1204 l_last_api_called := 'LNS_REP_UTILS.STORE_LOAN_AGREEMENT';
1205 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to LNS_REP_UTILS.STORE_LOAN_AGREEMENT');
1206 LNS_REP_UTILS.STORE_LOAN_AGREEMENT(l_loan_header_rec.loan_id);
1207 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'After call to LNS_REP_UTILS.STORE_LOAN_AGREEMENT');
1208 /*
1209 -- begin submit request to generate Loan Agreement Report
1210 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);
1211 l_notify := FND_REQUEST.ADD_NOTIFICATION(FND_GLOBAL.USER_NAME);
1212 FND_REQUEST.SET_ORG_ID(MO_GLOBAL.GET_CURRENT_ORG_ID());
1213 -- Bug#5936252 : Invoke the function add_layout to specify the template type,code etc., before submitting request
1214 SELECT
1215 lower(iso_language),iso_territory
1216 INTO
1217 l_iso_language,l_iso_territory
1218 FROM
1219 FND_LANGUAGES
1220 WHERE
1221 language_code = USERENV('LANG');
1222
1223
1224 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);
1225
1226 l_xml_output:= fnd_request.add_layout(
1227 template_appl_name => 'LNS',
1228 template_code => 'LNSRPTAG',
1229 template_language => l_iso_language,
1230 template_territory => l_iso_territory,
1231 output_format => 'PDF'
1232 );
1233 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);
1234
1235
1236 l_request_id := FND_REQUEST.SUBMIT_REQUEST('LNS'
1237 ,'LNS_AGREEMENT'
1238 ,'', '', FALSE
1239 ,l_loan_header_rec.loan_id);
1240
1241 if l_request_id = 0 then
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243 FND_MESSAGE.SET_NAME('LNS', 'LNS_AGREEMENT_REQUEST_FAILED');
1244 FND_MSG_PUB.Add;
1245 l_last_api_called := 'FND_REQUEST.SUBMIT_REQUEST for Loan Agreement Report Generation';
1246 RAISE FND_API.G_EXC_ERROR;
1247 else
1248 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Successfully submited Loan Agreement Report Generation Concurrent Program. Request id: ' || l_request_id);
1249 end if;
1250
1251 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);
1252 -- end submit request to generate Loan Agreement Report
1253 */
1254 end if;
1255
1256 IF (l_loan_header_rec.loan_status <> 'CANCELLED') then
1257
1258 open loan_version_cur(l_loan_header_rec.loan_id);
1259 fetch loan_version_cur into l_object_version_number;
1260 close loan_version_cur;
1261
1262 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan');
1263 LNS_LOAN_HEADER_PUB.update_loan(p_init_msg_list => FND_API.G_FALSE
1264 ,p_loan_header_rec => l_loan_header_rec
1265 ,p_object_version_number => l_object_version_number
1266 ,x_return_status => x_return_status
1267 ,x_msg_count => x_msg_count
1268 ,x_msg_data => x_msg_data);
1269 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to LNS_LOAN_HEADER_PUB.update_loan proc');
1270 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - x_return_status = ' || x_return_status);
1271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1272 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'));
1273 l_last_api_called := 'LNS_LOAN_HEADER_PUB.update_loan';
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276
1277 -- activate loan if BILL_ON_APPR_AMOUNT_FLAG = 'Y'
1278 open c_get_bill_opt(l_loan_header_rec.loan_id);
1279 fetch c_get_bill_opt into l_bill_on_appr_amounts;
1280 close c_get_bill_opt;
1281 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_bill_on_appr_amounts = ' || l_bill_on_appr_amounts);
1282
1283 IF (l_bill_on_appr_amounts = 'Y' and
1284 l_approval_action_rec.action_type = 'APPROVE' and
1285 l_loan_header_rec.loan_status = 'APPROVED' and
1286 l_loan_class_code = 'DIRECT')
1287 THEN
1288
1289 open loan_version_cur(l_loan_header_rec.loan_id);
1290 fetch loan_version_cur into l_object_version_number;
1291 close loan_version_cur;
1292
1293 l_loan_header_rec := null;
1294 l_loan_header_rec.loan_id := l_approval_action_rec.loan_id;
1295 l_loan_header_rec.loan_status := 'ACTIVE';
1296
1297 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan 2');
1298 LNS_LOAN_HEADER_PUB.update_loan(p_init_msg_list => FND_API.G_FALSE
1299 ,p_loan_header_rec => l_loan_header_rec
1300 ,p_object_version_number => l_object_version_number
1301 ,x_return_status => x_return_status
1302 ,x_msg_count => x_msg_count
1303 ,x_msg_data => x_msg_data);
1304 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to LNS_LOAN_HEADER_PUB.update_loan 2 proc');
1305 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - x_return_status = ' || x_return_status);
1306 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1307 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'));
1308 l_last_api_called := 'LNS_LOAN_HEADER_PUB.update_loan';
1309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1310 END IF;
1311
1312 END IF;
1313
1314 END IF;
1315
1316 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - End ');
1317
1318 EXCEPTION
1319 WHEN FND_API.G_EXC_ERROR THEN
1320 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'before rollback');
1321 ROLLBACK TO create_approval_action;
1322 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after rollback');
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 if l_last_api_called is not null then
1325 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1326 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1327 FND_MSG_PUB.ADD;
1328 end if;
1329 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1330 p_count => x_msg_count,
1331 p_data => x_msg_data);
1332
1333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1334 ROLLBACK TO create_approval_action;
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 if l_last_api_called is not null then
1337 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1338 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_last_api_called || ' SQLERRM: ' || SQLERRM);
1339 FND_MSG_PUB.ADD;
1340 end if;
1341 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1342 p_count => x_msg_count,
1343 p_data => x_msg_data);
1344
1345 WHEN OTHERS THEN
1346 ROLLBACK TO create_approval_action;
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 if l_last_api_called is not null then
1349 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1350 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1351 FND_MSG_PUB.ADD;
1352 end if;
1353 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => x_msg_data);
1356
1357 END create_approval_action;
1358
1359 /*===========================================================================+
1360 | PROCEDURE
1361 | update_approval_action
1362 |
1363 | DESCRIPTION
1364 | Updates approval action.
1365 |
1366 | SCOPE - PUBLIC
1367 |
1368 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1369 |
1370 | ARGUMENTS : IN:
1371 | p_init_msg_list
1372 | p_approval_action_rec
1373 | OUT:
1374 | x_return_status
1375 | x_msg_count
1376 | x_msg_data
1377 | IN/OUT:
1378 | p_object_version_number
1379 |
1380 | RETURNS : NONE
1381 |
1382 | NOTES
1383 |
1384 | MODIFICATION HISTORY
1385 | 22-Jan-2004 Bernice Lam Created
1386 +===========================================================================*/
1387
1388 PROCEDURE update_approval_action (
1389 p_init_msg_list IN VARCHAR2,
1390 p_approval_action_rec IN APPROVAL_ACTION_REC_TYPE,
1391 p_object_version_number IN OUT NOCOPY NUMBER,
1392 x_return_status OUT NOCOPY VARCHAR2,
1393 x_msg_count OUT NOCOPY NUMBER,
1394 x_msg_data OUT NOCOPY VARCHAR2
1395 ) IS
1396
1397 l_api_name CONSTANT VARCHAR2(30) := 'update_approval_action';
1398 l_approval_action_rec APPROVAL_ACTION_REC_TYPE;
1399 l_old_approval_action_rec APPROVAL_ACTION_REC_TYPE;
1400 BEGIN
1401 l_approval_action_rec := p_approval_action_rec;
1402 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_approval_action procedure');
1403
1404 -- standard start of API savepoint
1405 SAVEPOINT update_approval_action;
1406
1407 -- initialize message list if p_init_msg_list is set to TRUE.
1408 IF FND_API.to_Boolean(p_init_msg_list) THEN
1409 FND_MSG_PUB.initialize;
1410 END IF;
1411
1412 -- initialize API return status to success.
1413 x_return_status := FND_API.G_RET_STS_SUCCESS;
1414 /*
1415 -- Get old record. Will be used by history package.
1416 get_approval_action_rec (
1417 p_action_id => l_approval_action_rec.action_id,
1418 x_approval_action_rec => l_old_approval_action_rec,
1419 x_return_status => x_return_status,
1420 x_msg_count => x_msg_count,
1421 x_msg_data => x_msg_data );
1422 */
1423 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1424 RAISE FND_API.G_EXC_ERROR;
1425 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427 END IF;
1428
1429 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: Before call to do_update_approval_action proc');
1430
1431 -- call to business logic.
1432 do_update_approval_action(
1433 l_approval_action_rec,
1434 p_object_version_number,
1435 x_return_status);
1436
1437 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: After call to do_update_approval_action proc');
1438
1439 EXCEPTION
1440 WHEN FND_API.G_EXC_ERROR THEN
1441 ROLLBACK TO update_approval_action;
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1444 p_count => x_msg_count,
1445 p_data => x_msg_data);
1446
1447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1448 ROLLBACK TO update_approval_action;
1449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1451 p_count => x_msg_count,
1452 p_data => x_msg_data);
1453
1454 WHEN OTHERS THEN
1455 ROLLBACK TO update_approval_action;
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1458 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1459 FND_MSG_PUB.ADD;
1460 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1461 p_count => x_msg_count,
1462 p_data => x_msg_data);
1463
1464 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_approval_action procedure');
1465
1466 END update_approval_action;
1467
1468 /*===========================================================================+
1469 | PROCEDURE
1470 | delete_approval_action
1471 |
1472 | DESCRIPTION
1473 | Deletes approval action
1474 |
1475 | SCOPE - PUBLIC
1476 |
1477 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1478 |
1479 | ARGUMENTS : IN:
1480 | p_init_msg_list
1481 | p_action_id
1482 | OUT:
1483 | x_return_status
1484 | x_msg_count
1485 | x_msg_data
1486 | IN/OUT:
1487 |
1488 | RETURNS : NONE
1489 |
1490 | NOTES
1491 |
1492 | MODIFICATION HISTORY
1493 | 06-Jan-2004 Bernice Lam Created.
1494 +===========================================================================*/
1495
1496 PROCEDURE delete_approval_action (
1497 p_init_msg_list IN VARCHAR2,
1498 p_action_id IN NUMBER,
1499 x_return_status OUT NOCOPY VARCHAR2,
1500 x_msg_count OUT NOCOPY NUMBER,
1501 x_msg_data OUT NOCOPY VARCHAR2
1502 ) IS
1503
1504 l_api_name CONSTANT VARCHAR2(30) := 'delete_approval_action';
1505 l_action_id NUMBER;
1506
1507 BEGIN
1508 l_action_id := p_action_id;
1509 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_approval_action procedure');
1510
1511 -- standard start of API savepoint
1512 SAVEPOINT delete_approval_action;
1513
1514 -- initialize message list if p_init_msg_list is set to TRUE.
1515 IF FND_API.to_Boolean(p_init_msg_list) THEN
1516 FND_MSG_PUB.initialize;
1517 END IF;
1518
1519 -- initialize API return status to success.
1520 x_return_status := FND_API.G_RET_STS_SUCCESS;
1521
1522 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: Before call to do_delete_approval_action proc');
1523
1524 -- call to business logic.
1525 do_delete_approval_action(
1526 l_action_id,
1527 x_return_status);
1528
1529 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: After call to do_delete_approval_action proc');
1530
1531 EXCEPTION
1532 WHEN FND_API.G_EXC_ERROR THEN
1533 ROLLBACK TO delete_approval_action;
1534 x_return_status := FND_API.G_RET_STS_ERROR;
1535 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1536 p_count => x_msg_count,
1537 p_data => x_msg_data);
1538
1539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1540 ROLLBACK TO delete_approval_action;
1541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1542 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1543 p_count => x_msg_count,
1544 p_data => x_msg_data);
1545
1546 WHEN OTHERS THEN
1547 ROLLBACK TO delete_approval_action;
1548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1549 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
1550 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1551 FND_MSG_PUB.ADD;
1552 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1553 p_count => x_msg_count,
1554 p_data => x_msg_data);
1555
1556 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_approval_action procedure');
1557
1558 END delete_approval_action;
1559
1560
1561
1562 /*========================================================================
1563 | PUBLIC PROCEDURE APPROVE_ADD_RECEIVABLE
1564 |
1565 | DESCRIPTION
1566 | This procedure executes all actions required during approval of loan additional receivable
1567 |
1568 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1569 | None
1570 |
1571 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1572 | init
1573 | LogMessage
1574 |
1575 | PARAMETERS
1576 | P_API_VERSION IN Standard in parameter
1577 | P_INIT_MSG_LIST IN Standard in parameter
1578 | P_COMMIT IN Standard in parameter
1579 | P_VALIDATION_LEVEL IN Standard in parameter
1580 | P_LOAN_LINE_ID IN Loan Line ID
1581 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1582 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1583 | X_MSG_DATA OUT NOCOPY Standard out parameter
1584 |
1585 | KNOWN ISSUES
1586 | None
1587 |
1588 | NOTES
1589 | Any interesting aspect of the code in the package body which needs
1590 | to be stated.
1591 |
1592 | MODIFICATION HISTORY
1593 | Date Author Description of Changes
1594 | 03-05-2010 scherkas Created
1595 |
1596 *=======================================================================*/
1597 PROCEDURE APPROVE_ADD_RECEIVABLE(
1598 P_API_VERSION IN NUMBER,
1599 P_INIT_MSG_LIST IN VARCHAR2,
1600 P_COMMIT IN VARCHAR2,
1601 P_VALIDATION_LEVEL IN NUMBER,
1602 P_LOAN_LINE_ID IN NUMBER,
1603 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1604 X_MSG_COUNT OUT NOCOPY NUMBER,
1605 X_MSG_DATA OUT NOCOPY VARCHAR2)
1606 IS
1607
1608 /*-----------------------------------------------------------------------+
1609 | Local Variable Declarations and initializations |
1610 +-----------------------------------------------------------------------*/
1611
1612 l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_ADD_RECEIVABLE';
1613 l_api_version CONSTANT NUMBER := 1.0;
1614 l_return_status VARCHAR2(1);
1615 l_msg_count NUMBER;
1616 l_msg_data VARCHAR2(32767);
1617 l_trx_number varchar2(20);
1618 l_loan_id number;
1619 -- l_xml_output BOOLEAN;
1620 -- l_iso_language FND_LANGUAGES.iso_language%TYPE;
1621 -- l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
1622 l_agreement_reason varchar2(500);
1623 -- l_notify boolean;
1624 -- l_request_id number;
1625 l_version_number number;
1626 l_loan_status varchar2(30);
1627 l_adj_amount number;
1628 l_funded_amount number;
1629 l_add_req_amount number;
1630 l_cond_count number;
1631 l_adj_date date;
1632 l_currency varchar2(15);
1633
1634 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
1635
1636 /*-----------------------------------------------------------------------+
1637 | Cursor Declarations |
1638 +-----------------------------------------------------------------------*/
1639
1640 CURSOR loan_info_cur(P_LOAN_LINE_ID number) IS
1641 select loan.loan_id,
1642 loan.LOAN_CURRENCY,
1643 line.REFERENCE_NUMBER,
1644 line.REQUESTED_AMOUNT,
1645 line.ADJUSTMENT_DATE,
1646 nvl(loan.FUNDED_AMOUNT, 0),
1647 nvl(loan.ADD_REQUESTED_AMOUNT, 0),
1648 loan.loan_status,
1649 loan.OBJECT_VERSION_NUMBER
1650 from lns_loan_lines line,
1651 lns_loan_headers_all loan
1652 where line.loan_line_id = P_LOAN_LINE_ID and
1653 line.loan_id = loan.loan_id;
1654
1655 -- checking for conditions
1656 CURSOR conditions_cur(P_LOAN_LINE_ID number) IS
1657 select count(1)
1658 from LNS_COND_ASSIGNMENTS
1659 where
1660 OWNER_OBJECT_ID = P_LOAN_LINE_ID and
1661 OWNER_TABLE = 'LNS_LOAN_LINES' and
1662 MANDATORY_FLAG = 'Y' and
1663 (CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
1664 (end_date_active is null or trunc(end_date_active) > trunc(sysdate));
1665
1666 BEGIN
1667
1668 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1669
1670 -- Standard start of API savepoint
1671 SAVEPOINT APPROVE_ADD_RECEIVABLE;
1672 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
1673
1674 -- Standard call to check for call compatibility
1675 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1677 END IF;
1678
1679 -- Initialize message list if p_init_msg_list is set to TRUE
1680 IF FND_API.To_Boolean(p_init_msg_list) THEN
1681 FND_MSG_PUB.initialize;
1682 END IF;
1683
1684 -- Initialize API return status to success
1685 l_return_status := FND_API.G_RET_STS_SUCCESS;
1686
1687 -- START OF BODY OF API
1688
1689 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_LOAN_LINE_ID = ' || P_LOAN_LINE_ID);
1690
1691 /* verify input parameters */
1692 if P_LOAN_LINE_ID is null then
1693
1694 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
1695 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_LOAN_LINE_ID' );
1696 FND_MSG_PUB.Add;
1697 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1698 RAISE FND_API.G_EXC_ERROR;
1699
1700 end if;
1701
1702 -- checking for conditions
1703 open conditions_cur(P_LOAN_LINE_ID);
1704 fetch conditions_cur into l_cond_count;
1705 close conditions_cur;
1706
1707 if l_cond_count > 0 then
1708 FND_MESSAGE.SET_NAME('LNS', 'LNS_NOT_ALL_COND_MET');
1709 FND_MSG_PUB.Add;
1710 RAISE FND_API.G_EXC_ERROR;
1711 end if;
1712
1713 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_BILLING_BATCH_PUB.ADJUST_ADD_RECEIVABLE...');
1714 LNS_BILLING_BATCH_PUB.ADJUST_ADD_RECEIVABLE(P_API_VERSION => 1.0
1715 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1716 ,P_COMMIT => FND_API.G_FALSE
1717 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
1718 ,P_LOAN_LINE_ID => P_LOAN_LINE_ID
1719 ,X_RETURN_STATUS => l_return_status
1720 ,X_MSG_COUNT => l_msg_count
1721 ,X_MSG_DATA => l_msg_data);
1722
1723 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
1724 IF l_return_status <> 'S' THEN
1725 RAISE FND_API.G_EXC_ERROR;
1726 END IF;
1727
1728 open loan_info_cur(P_LOAN_LINE_ID);
1729 fetch loan_info_cur into l_loan_id,
1730 l_currency,
1731 l_trx_number,
1732 l_adj_amount,
1733 l_adj_date,
1734 l_funded_amount,
1735 l_add_req_amount,
1736 l_loan_status,
1737 l_version_number;
1738 close loan_info_cur;
1739
1740 -- create distributions for new approved additional receivable?
1741 LNS_DISTRIBUTIONS_PUB.createDistrForAddRec(P_API_VERSION => 1.0
1742 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1743 ,P_COMMIT => FND_API.G_FALSE
1744 ,P_LOAN_ID => l_loan_id
1745 ,P_LOAN_LINE_ID => P_LOAN_LINE_ID
1746 ,X_RETURN_STATUS => l_return_status
1747 ,X_MSG_COUNT => l_msg_count
1748 ,X_MSG_DATA => l_msg_data);
1749 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
1750 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_msg_count = ' || l_msg_count);
1751 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_msg_data = ' || l_msg_data);
1752 IF l_return_status <> 'S' THEN
1753 RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755
1756 /* updating loan header table */
1757 l_loan_header_rec.loan_id := l_loan_id;
1758 l_loan_header_rec.funded_amount := (l_funded_amount + l_adj_amount);
1759 l_loan_header_rec.ADD_REQUESTED_AMOUNT := (l_add_req_amount + l_adj_amount);
1760
1761 if l_loan_status <> 'ACTIVE' and
1762 l_loan_status <> 'DEFAULT' and
1763 l_loan_status <> 'DELINQUENT'
1764 then
1765 l_loan_header_rec.LOAN_STATUS := 'ACTIVE';
1766 end if;
1767
1768 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating loan header info w following values:');
1769 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_STATUS: ' || l_loan_header_rec.LOAN_STATUS);
1770 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'funded_amount: ' || l_loan_header_rec.funded_amount);
1771 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ADD_REQUESTED_AMOUNT: ' || l_loan_header_rec.ADD_REQUESTED_AMOUNT);
1772
1773 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
1774 P_LOAN_HEADER_REC => l_loan_header_rec,
1775 P_INIT_MSG_LIST => FND_API.G_FALSE,
1776 X_RETURN_STATUS => l_return_status,
1777 X_MSG_COUNT => l_msg_count,
1778 X_MSG_DATA => l_msg_data);
1779
1780 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
1781
1782 IF l_return_status <> 'S' THEN
1783 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
1784 FND_MSG_PUB.Add;
1785 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1786 RAISE FND_API.G_EXC_ERROR;
1787 END IF;
1788
1789 FND_MESSAGE.SET_NAME('LNS', 'LNS_ADD_REC_AGR_REASON');
1790 FND_MESSAGE.SET_TOKEN('REC', l_trx_number);
1791 FND_MESSAGE.SET_TOKEN('AMOUNT', to_char(l_adj_amount, FND_CURRENCY.SAFE_GET_FORMAT_MASK(l_currency,50)));
1792 FND_MESSAGE.SET_TOKEN('CURR', l_currency);
1793 FND_MESSAGE.SET_TOKEN('DATE', l_adj_date);
1794 FND_MSG_PUB.Add;
1795 l_agreement_reason := FND_MSG_PUB.Get(p_encoded => 'F');
1796 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
1797
1798 LNS_REP_UTILS.STORE_LOAN_AGREEMENT_CP(l_loan_id, l_agreement_reason);
1799
1800 if P_COMMIT = FND_API.G_TRUE then
1801 COMMIT WORK;
1802 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
1803 end if;
1804
1805 -- END OF BODY OF API
1806 x_return_status := FND_API.G_RET_STS_SUCCESS;
1807
1808 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully approved additional receivable ' || l_trx_number);
1809
1810 -- Standard call to get message count and if count is 1, get message info
1811 FND_MSG_PUB.Count_And_Get(
1812 p_encoded => FND_API.G_FALSE,
1813 p_count => x_msg_count,
1814 p_data => x_msg_data);
1815
1816 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
1817
1818 EXCEPTION
1819 WHEN FND_API.G_EXC_ERROR THEN
1820 ROLLBACK TO APPROVE_ADD_RECEIVABLE;
1821 x_return_status := FND_API.G_RET_STS_ERROR;
1822 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1823 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1825 ROLLBACK TO APPROVE_ADD_RECEIVABLE;
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1827 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1828 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1829 WHEN OTHERS THEN
1830 ROLLBACK TO APPROVE_ADD_RECEIVABLE;
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1833 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1834 END IF;
1835 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1836 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1837 END;
1838
1839
1840
1841 /*========================================================================
1842 | PUBLIC PROCEDURE APPROVE_ADD_RECEIVABLE
1843 |
1844 | DESCRIPTION
1845 | This procedure executes actions required during approval of loan amount adjustment
1846 |
1847 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1848 | None
1849 |
1850 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1851 | init
1852 | LogMessage
1853 |
1854 | PARAMETERS
1855 | P_API_VERSION IN Standard in parameter
1856 | P_INIT_MSG_LIST IN Standard in parameter
1857 | P_COMMIT IN Standard in parameter
1858 | P_VALIDATION_LEVEL IN Standard in parameter
1859 | P_LOAN_AMOUNT_ADJ_ID IN Loan amount adjutsment ID
1860 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1861 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1862 | X_MSG_DATA OUT NOCOPY Standard out parameter
1863 |
1864 | KNOWN ISSUES
1865 | None
1866 |
1867 | NOTES
1868 | Any interesting aspect of the code in the package body which needs
1869 | to be stated.
1870 |
1871 | MODIFICATION HISTORY
1872 | Date Author Description of Changes
1873 | 04-05-2010 scherkas Created
1874 |
1875 *=======================================================================*/
1876 PROCEDURE APPROVE_LOAN_AM_ADJ(
1877 P_API_VERSION IN NUMBER,
1878 P_INIT_MSG_LIST IN VARCHAR2,
1879 P_COMMIT IN VARCHAR2,
1880 P_VALIDATION_LEVEL IN NUMBER,
1881 P_LOAN_AMOUNT_ADJ_ID IN NUMBER,
1882 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1883 X_MSG_COUNT OUT NOCOPY NUMBER,
1884 X_MSG_DATA OUT NOCOPY VARCHAR2)
1885 IS
1886
1887 /*-----------------------------------------------------------------------+
1888 | Local Variable Declarations and initializations |
1889 +-----------------------------------------------------------------------*/
1890
1891 l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_LOAN_AM_ADJ';
1892 l_api_version CONSTANT NUMBER := 1.0;
1893 l_return_status VARCHAR2(1);
1894 l_msg_count NUMBER;
1895 l_msg_data VARCHAR2(32767);
1896 l_loan_id number;
1897 -- l_xml_output BOOLEAN;
1898 -- l_iso_language FND_LANGUAGES.iso_language%TYPE;
1899 -- l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
1900 l_agreement_reason varchar2(500);
1901 -- l_notify boolean;
1902 -- l_request_id number;
1903 l_cond_count number;
1904 l_adj_date date;
1905 l_adj_amount_str varchar2(30);
1906 l_currency varchar2(15);
1907 l_DESCRIPTION VARCHAR2(250);
1908
1909 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
1910
1911 /*-----------------------------------------------------------------------+
1912 | Cursor Declarations |
1913 +-----------------------------------------------------------------------*/
1914
1915 CURSOR loan_am_adj_cur(P_LOAN_AMOUNT_ADJ_ID number) IS
1916 select loan.loan_id,
1917 loan.LOAN_CURRENCY,
1918 adj.DESCRIPTION,
1919 to_char(adj.ADJUSTMENT_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
1920 adj.EFFECTIVE_DATE
1921 from LNS_LOAN_AMOUNT_ADJS adj,
1922 lns_loan_headers_all loan
1923 where adj.LOAN_AMOUNT_ADJ_ID = P_LOAN_AMOUNT_ADJ_ID and
1924 adj.loan_id = loan.loan_id;
1925
1926 -- checking for conditions
1927 CURSOR conditions_cur(P_LOAN_AMOUNT_ADJ_ID number) IS
1928 select count(1)
1929 from LNS_COND_ASSIGNMENTS
1930 where
1931 OWNER_OBJECT_ID = P_LOAN_AMOUNT_ADJ_ID and
1932 OWNER_TABLE = 'LNS_LOAN_AMOUNT_ADJS' and
1933 MANDATORY_FLAG = 'Y' and
1934 (CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
1935 (end_date_active is null or trunc(end_date_active) > trunc(sysdate));
1936
1937 BEGIN
1938
1939 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1940
1941 -- Standard start of API savepoint
1942 SAVEPOINT APPROVE_LOAN_AM_ADJ;
1943 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
1944
1945 -- Standard call to check for call compatibility
1946 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1948 END IF;
1949
1950 -- Initialize message list if p_init_msg_list is set to TRUE
1951 IF FND_API.To_Boolean(p_init_msg_list) THEN
1952 FND_MSG_PUB.initialize;
1953 END IF;
1954
1955 -- Initialize API return status to success
1956 l_return_status := FND_API.G_RET_STS_SUCCESS;
1957
1958 -- START OF BODY OF API
1959
1960 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_LOAN_AMOUNT_ADJ_ID = ' || P_LOAN_AMOUNT_ADJ_ID);
1961
1962 /* verify input parameters */
1963 if P_LOAN_AMOUNT_ADJ_ID is null then
1964
1965 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
1966 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_LOAN_AMOUNT_ADJ_ID' );
1967 FND_MSG_PUB.Add;
1968 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1969 RAISE FND_API.G_EXC_ERROR;
1970
1971 end if;
1972
1973 -- checking for conditions
1974 open conditions_cur(P_LOAN_AMOUNT_ADJ_ID);
1975 fetch conditions_cur into l_cond_count;
1976 close conditions_cur;
1977
1978 if l_cond_count > 0 then
1979 FND_MESSAGE.SET_NAME('LNS', 'LNS_NOT_ALL_COND_MET');
1980 FND_MSG_PUB.Add;
1981 RAISE FND_API.G_EXC_ERROR;
1982 end if;
1983
1984 open loan_am_adj_cur(P_LOAN_AMOUNT_ADJ_ID);
1985 fetch loan_am_adj_cur into l_loan_id,
1986 l_currency,
1987 l_DESCRIPTION,
1988 l_adj_amount_str,
1989 l_adj_date;
1990 close loan_am_adj_cur;
1991
1992 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_AM_ADJ_AGR_REASON');
1993 FND_MESSAGE.SET_TOKEN('ADJ', l_DESCRIPTION);
1994 FND_MESSAGE.SET_TOKEN('AMOUNT', l_adj_amount_str);
1995 FND_MESSAGE.SET_TOKEN('CURR', l_currency);
1996 FND_MESSAGE.SET_TOKEN('DATE', l_adj_date);
1997 FND_MSG_PUB.Add;
1998 l_agreement_reason := FND_MSG_PUB.Get(p_encoded => 'F');
1999 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
2000
2001 LNS_REP_UTILS.STORE_LOAN_AGREEMENT_CP(l_loan_id, l_agreement_reason);
2002
2003 if P_COMMIT = FND_API.G_TRUE then
2004 COMMIT WORK;
2005 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
2006 end if;
2007
2008 -- END OF BODY OF API
2009 x_return_status := FND_API.G_RET_STS_SUCCESS;
2010
2011 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully loan amount adjustment ' || l_DESCRIPTION);
2012
2013 -- Standard call to get message count and if count is 1, get message info
2014 FND_MSG_PUB.Count_And_Get(
2015 p_encoded => FND_API.G_FALSE,
2016 p_count => x_msg_count,
2017 p_data => x_msg_data);
2018
2019 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2020
2021 EXCEPTION
2022 WHEN FND_API.G_EXC_ERROR THEN
2023 ROLLBACK TO APPROVE_LOAN_AM_ADJ;
2024 x_return_status := FND_API.G_RET_STS_ERROR;
2025 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2026 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2028 ROLLBACK TO APPROVE_LOAN_AM_ADJ;
2029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2030 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2031 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2032 WHEN OTHERS THEN
2033 ROLLBACK TO APPROVE_LOAN_AM_ADJ;
2034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2035 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
2036 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2037 END IF;
2038 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2039 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2040 END;
2041
2042
2043 /*========================================================================
2044 | PUBLIC FUNCTION IS_AME_ENABLED
2045 |
2046 | DESCRIPTION
2047 | This procedure checks whether AME is enabled (whether AME:Installed profile is set to Yes)
2048 | and whether the logged in user is the next Approver in line as per AME Engine. If both the
2049 | conditions are met then returns Y else N.
2050 |
2051 | PARAMETERS
2052 | P_LOAN_ID IN Loan ID
2053 | P_LOGGED_IN_USER_NAME IN User Name of Logged User
2054 |
2055 | KNOWN ISSUES
2056 | None
2057 |
2058 | NOTES
2059 | Any interesting aspect of the code in the package body which needs
2060 | to be stated.
2061 |
2062 | MODIFICATION HISTORY
2063 | Date Author Description of Changes
2064 | 24-09-2010 gparuchu Created
2065 |
2066 *=======================================================================*/
2067 FUNCTION IS_AME_ENABLED(
2068 P_LOAN_ID IN NUMBER,
2069 P_LOGGED_IN_USER_NAME IN VARCHAR2) RETURN VARCHAR2
2070 IS
2071 /*-----------------------------------------------------------------------+
2072 | Local Variable Declarations and initializations |
2073 +-----------------------------------------------------------------------*/
2074
2075 l_api_name CONSTANT VARCHAR2(30) := 'IS_AME_ENABLED';
2076 l_result VARCHAR2(1);
2077 l_is_ame_profile_set fnd_profile_option_values.profile_option_value%TYPE;
2078 l_next_approver ame_util.approversTable2;
2079 l_approval_complete VARCHAR2(20);
2080 l_next_approver_name VARCHAR2(50);
2081 l_transaction_type VARCHAR2(30);
2082 i NUMBER;
2083
2084
2085
2086 BEGIN
2087 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2088 l_transaction_type := FND_PROFILE.value('LNS_AME_TRANSACTION_TYPE');
2089 l_result := 'N';
2090
2091
2092
2093 -- START OF BODY OF API
2094 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2095 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2096 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOGGED_IN_USER_NAME: ' || P_LOGGED_IN_USER_NAME);
2097
2098 l_is_ame_profile_set := fnd_profile.value('AME_INSTALLED_FLAG');
2099 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Is AME_INSTALLED_FLAG set : ' || l_is_ame_profile_set);
2100
2101 --The below api wont work for us as it gets the next non-notified approver.
2102 /* ame_api2.getNextApprovers4(
2103 applicationIdIn => 206 ,
2104 transactionTypeIn => l_transaction_type ,
2105 transactionIdIn => p_loan_id,
2106 flagApproversAsNotifiedIn => ame_util.booleanFalse,
2107 approvalProcessCompleteYNOut => l_approval_complete,
2108 nextApproversOut => l_next_approver);
2109 */
2110
2111 --We want the current notified approver
2112 ame_api2.getPendingApprovers(applicationIdIn => 206,
2113 transactionTypeIn => l_transaction_type,
2114 transactionIdIn => p_loan_id,
2115 approvalProcessCompleteYNOut => l_approval_complete,
2116 approversOut => l_next_approver);
2117
2118 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'After calling ame_api2.getPendingApprovers');
2119 i := 1;
2120
2121 --GPARUCHU - The following approach is for serial mode of approvals. For parallel approvals we should loop
2122 --around the l_next_approver and find if the current logged in user is same as any of the notified
2123 --approvers and then decide whether to show the Approve,Reject or Request For Information buttons on UI.
2124
2125 IF (l_next_approver.count < 1)
2126 THEN
2127 -- no more approvers left
2128 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'No Approvers Found');
2129 RETURN l_result;
2130 ELSE
2131 l_next_approver_name := l_next_approver(i).name;
2132 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Next Approver User Name : ' || l_next_approver_name);
2133
2134 END IF;
2135
2136 IF NVL(l_is_ame_profile_set,'N') = 'Y' AND p_logged_in_user_name = l_next_approver_name
2137 THEN
2138 l_result := 'Y';
2139 ELSE
2140 l_result := 'N';
2141 END IF;
2142
2143 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Returned Value : ' || l_result);
2144 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2145 return l_result;
2146
2147 EXCEPTION
2148 WHEN OTHERS THEN
2149 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_APPROVAL_ACTION_PUB.IS_AME_ENABLED: Unexpected ERROR in the function call. SQLERRM is: ' || SQLERRM);
2151 END IF;
2152
2153 return l_result;
2154
2155
2156 End IS_AME_ENABLED;
2157
2158
2159 /*========================================================================
2160 | PUBLIC PROCEDURE AME_APPROVAL_ACTION_FROM_UI
2161 |
2162 | DESCRIPTION
2163 | This procedure completes the workflow notificatin activity with the appropriate
2164 | Approval Action taken by the Approver on Loans UI.
2165 |
2166 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2167 | None
2168 |
2169 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2170 | init
2171 | LogMessage
2172 |
2173 | PARAMETERS
2174 | P_LOAN_NUMBER IN VARCHAR2,
2175 | P_ACTION_TYPE IN VARCHAR2,
2176 | P_MESSAGE_NAME IN VARCHAR2,
2177 | P_CURRENT_APPROVER IN VARCHAR2,
2178 | P_NOTES IN VARCHAR2
2179 |
2180 | KNOWN ISSUES
2181 | None
2182 |
2183 | NOTES
2184 | Any interesting aspect of the code in the package body which needs
2185 | to be stated.
2186 |
2187 | MODIFICATION HISTORY
2188 | Date Author Description of Changes
2189 | 23-02-2010 gparuchu Created
2190 |
2191 *=======================================================================*/
2192 PROCEDURE AME_APPROVAL_ACTION_FROM_UI(
2193 P_LOAN_NUMBER IN VARCHAR2,
2194 P_ACTION_TYPE IN VARCHAR2,
2195 P_MESSAGE_NAME IN VARCHAR2,
2196 P_CURRENT_APPROVER IN VARCHAR2,
2197 P_NOTES IN VARCHAR2)
2198 IS
2199
2200 /*-----------------------------------------------------------------------+
2201 | Local Variable Declarations and initializations |
2202 +-----------------------------------------------------------------------*/
2203
2204 l_api_name CONSTANT VARCHAR2(40) := 'AME_APPROVAL_ACTION_FROM_UI';
2205 l_loan_number VARCHAR2(240);
2206 l_notification_id NUMBER;
2207 l_action_type VARCHAR2(40);
2208
2209 /*-----------------------------------------------------------------------+
2210 | Cursor Declarations |
2211 +-----------------------------------------------------------------------*/
2212
2213 CURSOR get_notification_id_cur(l_loan_number VARCHAR2, P_MESSAGE_NAME VARCHAR2, P_CURRENT_APPROVER VARCHAR2) IS
2214 select wfn.notification_id
2215 from WF_NOTIFICATIONS wfn
2216 where item_key like l_loan_number
2217 and message_name = P_MESSAGE_NAME
2218 and recipient_role = P_CURRENT_APPROVER
2219 and status = 'OPEN';
2220
2221
2222
2223 BEGIN
2224
2225 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2226
2227 -- START OF BODY OF API
2228
2229 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_LOAN_NUMBER = ' || P_LOAN_NUMBER);
2230 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_ACTION_TYPE = ' || P_ACTION_TYPE);
2231 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_MESSAGE_NAME = ' || P_MESSAGE_NAME);
2232 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_CURRENT_APPROVER = ' || P_CURRENT_APPROVER);
2233 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input P_NOTES = ' || P_NOTES);
2234
2235 /* verify input parameters */
2236 if P_LOAN_NUMBER is null then
2237
2238 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
2239 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_LOAN_NUMBER' );
2240 FND_MSG_PUB.Add;
2241 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2242 RAISE FND_API.G_EXC_ERROR;
2243
2244 end if;
2245
2246 if P_ACTION_TYPE is null then
2247
2248 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
2249 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_ACTION_TYPE' );
2250 FND_MSG_PUB.Add;
2251 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2252 RAISE FND_API.G_EXC_ERROR;
2253
2254 end if;
2255
2256 if P_CURRENT_APPROVER is null then
2257
2258 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
2259 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_CURRENT_APPROVER' );
2260 FND_MSG_PUB.Add;
2261 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2262 RAISE FND_API.G_EXC_ERROR;
2263
2264 end if;
2265
2266 --Main Logic to complete the workflow notification activity with user comments
2267
2268 --The Action_Type for Request_For_Add_Information on UI and on workflow is different.
2269 --so changing accoring to workflow requirement.
2270
2271 l_action_type := P_ACTION_TYPE;
2272
2273 IF l_action_type = 'REQUEST_FOR_INFO'
2274 THEN
2275 l_action_type := 'REQUEST_INFORMATION';
2276 END IF;
2277
2278 l_loan_number := P_LOAN_NUMBER || '%';
2279
2280 open get_notification_id_cur(l_loan_number, P_MESSAGE_NAME, P_CURRENT_APPROVER);
2281 fetch get_notification_id_cur into l_notification_id;
2282 close get_notification_id_cur;
2283
2284 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Notification Id fetched - '|| l_notification_id);
2285 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Before completing the notification activity');
2286
2287 WF_NOTIFICATION.SETATTRTEXT(l_notification_id,'RESULT',l_action_type);
2288 --Test code to check whether following code captures the notes entered by the user--
2289 --WF_NOTIFICATION.SETATTRTEXT(l_notification_id,'LNS_LOAN_NOTE',P_NOTES);
2290 WF_NOTIFICATION.SETATTRTEXT(l_notification_id,'WF_NOTE',P_NOTES);
2291 --End Test Code
2292
2293 WF_NOTIFICATION.RESPOND(l_notification_id,P_NOTES,P_CURRENT_APPROVER);
2294
2295 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'After completing the notification activity');
2296
2297
2298 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2299
2300 EXCEPTION
2301 WHEN FND_API.G_EXC_ERROR THEN
2302 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'FND_API.G_EXC_ERROR exception block');
2303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2304 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'FND_API.G_EXC_UNEXPECTED_ERROR exception block');
2305 WHEN OTHERS THEN
2306 ROLLBACK TO APPROVE_LOAN_AM_ADJ;
2307 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'OTHERS exception block');
2308 END;
2309
2310 END LNS_APPROVAL_ACTION_PUB;