[Home] [Help]
PACKAGE BODY: APPS.LNS_PAYOFF_PUB
Source
1 PACKAGE BODY LNS_PAYOFF_PUB AS
2 /* $Header: LNS_PAYOFF_B.pls 120.10.12020000.3 2012/08/16 19:25:44 scherkas ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_PAYOFF_B.pls';
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_PAYOFF_PUB';
12
13 --------------------------------------------
14 -- internal package routines
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
31
32 /*=========================================================================
33 || PUBLIC PROCEDURE processPayoff
34 ||
35 || DESCRIPTION
36 ||
37 || Overview: this function will attempt to payoff the remaining invoices
38 || on a given loan and create final invoices for the
39 || remaining principal and any additional interest
40 || and pay those off as well
41 || if all goes right, then the loan_status will be set = 'PAIDOFF'
42 ||
43 || Parameter: p_loan_id = loan id
44 || p_payoff_date = date loan will be paid off
45 || p_cash_receipt_ids = table of receipts and amounts to payoff the loan
46 ||
47 || Source Tables: NA
48 ||
49 || Target Tables: NA
50 ||
51 || Return value:
52 ||
53 || KNOWN ISSUES
54 ||
55 || NOTES
56 || 1. calculate the payoff as of p_payoff_date
57 || 2. create payoff document(s) in receivables
58 || 3. getLoanInvoices (should return newly created documents) (payoff_date = null)
59 || 4. check if sum of receipts covers the remaining loan amount
60 || 5. apply cash
61 || 6. if success then update loan_stats = 'PAIDOFF'
62 ||
63 ||
64 || MODIFICATION HISTORY
65 || Date Author Description of Changes
66 || 10/06/2004 1:51PM raverma Created
67 *=======================================================================*/
68 procedure processPayoff(p_api_version IN NUMBER
69 ,p_init_msg_list IN VARCHAR2
70 ,p_loan_id in number
71 ,p_payoff_date in date
72 ,p_cash_receipt_ids in LNS_PAYOFF_PUB.CASH_RECEIPT_TBL
73 ,x_return_status OUT NOCOPY VARCHAR2
74 ,x_msg_count OUT NOCOPY NUMBER
75 ,x_msg_data OUT NOCOPY VARCHAR2)
76 is
77 l_api_name varchar2(25);
78 l_api_version_number number;
79 l_return_status VARCHAR2(1);
80 l_msg_count NUMBER;
81 l_msg_data VARCHAR2(32767);
82 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
83 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
84 l_invoices_tbl LNS_PAYOFF_PUB.INVOICE_DETAILS_TBL;
85 l_cash_receipt_ids LNS_PAYOFF_PUB.CASH_RECEIPT_TBL;
86 l_add_cash_receipt_ids LNS_PAYOFF_PUB.CASH_RECEIPT_TBL; -- for unapplied crs on last interest document only
87 l_bill_headers_tbl LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL; -- for invoice creation
88 l_bill_lines_tbl LNS_BILLING_BATCH_PUB.BILL_LINES_TBL; -- for invoice creation
89 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.Cm_Line_Tbl_Type_Cover%type;
90 l_total_payoff_amount number;
91 l_total_receipts_amount number;
92 l_object_version number;
93 l_amount_to_apply number;
94 i number;
95 j number;
96 s number;
97 l_receipt_amount_remaining number;
98 l_invoice_amount_remaining number;
99 l_found varchar2(1);
100 l_cash_receipts_count number;
101 l_rec_application_id number;
102 l_cash_receipt_id number;
103 l_customer_trx_id number;
104 l_interest_trx_id number;
105 l_principal_trx_id number;
106 l_fee_trx_id number;
107 l_app_pay_sched_id number; -- applied payment_schedule_id
108 l_amount_applied number;
109 l_currency_code varchar2(10);
110 l_new_interest number;
111 l_final_balance number;
112 l_loan_currency varchar2(10);
113 l_receipt_amount number; -- in loan currency
114 l_receipt_number varchar2(30);
115 l_receipt_currency_orig varchar2(30);
116 l_rec_exchange_rate number; -- rac exchg
117 l_rec_exchange_date date; -- rac exchg
118 l_rec_exchange_rate_type varchar2(30); -- rac exchg
119 b_recalculate_interest boolean;
120 l_loan_exchange_rate number; -- loan exchg
121 l_loan_exchange_date date; -- loan exchg
122 l_loan_exchange_rate_type varchar2(30); -- loan exchg
123 l_phase varchar2(30);
124 l_receipt_amount_from number; -- in receipt currency
125 l_trans_to_receipt_rate number;
126 l_bool_match boolean;
127
128 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
129
130 cursor c_receipt_number(p_cash_receipt_id number) is
131 select receipt_number, currency_code
132 from ar_cash_receipts
133 where cash_receipt_id = p_cash_receipt_id;
134
135 cursor c_loans_obj_vers(p_loan_id number) is
136 select OBJECT_VERSION_NUMBER
137 from lns_loan_headers
138 where loan_id = p_loan_id;
139
140 CURSOR loans_cur (p_loan_id number) IS
141 select
142 head.Loan_currency
143 ,nvl(head.exchange_rate, 1)
144 ,head.exchange_date
145 ,head.exchange_rate_type
146 ,head.current_phase
147 from LNS_LOAN_HEADERS head
148 where head.loan_id = p_loan_id and
149 head.loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
150
151 -- this cursor retrieves any applications to
152 -- final interest document INTEREST ONLY
153 cursor c_applications(p_loan_id number) is
154 select rap.receivable_application_id
155 ,rap.cash_receipt_id
156 ,rap.amount_applied -- this is in loan / transacation currency
157 ,trx.customer_trx_id
158 ,trx.payment_schedule_id
159 ,rac.receipt_number
160 ,rac.currency_code
161 ,rac.exchange_rate
162 ,rac.exchange_date
163 ,rac.exchange_rate_type
164 ,lam.interest_trx_id -- get this in order to create new interest document
165 ,lam.principal_trx_id
166 ,lam.fee_trx_id
167 from ar_receivable_applications rap
168 ,ar_cash_receipts rac
169 ,lns_amortization_scheds lam
170 ,ar_payment_schedules trx
171 where rap.cash_receipt_id = rac.cash_receipt_id
172 and rap.applied_customer_trx_id = trx.customer_trx_id
173 and trx.customer_trx_id = lam.interest_trx_id
174 and lam.payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
175 and lam.reversed_flag = 'N'
176 and lam.reamortization_amount is null
177 and lam.parent_amortization_id is null
178 and lam.loan_id = p_loan_id
179 and rap.display = 'Y'
180 and rap.status = 'APP';
181
182 cursor c_fees(p_loan_id number, p_phase varchar2, p_installment number)
183 is
184 select sched.fee_amount
185 ,fees.fee_name
186 ,sched.fee_installment
187 from lns_fee_schedules sched
188 ,lns_fees fees
189 where sched.loan_id = p_loan_id
190 and sched.fee_id = fees.fee_id
191 and sched.active_flag = 'Y'
192 and sched.billed_flag = 'N'
193 and sched.phase = p_phase
194 and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
195 and fees.fee_category <> 'RECUR';
196
197 l_fee_amount number;
198 l_fee_name varchar2(50);
199 l_fee_installment number;
200 l_sob_currency varchar2(30);
201
202 cursor c_sob_currency is
203 SELECT sb.currency_code
204 FROM lns_system_options so,
205 gl_sets_of_books sb
206 WHERE sb.set_of_books_id = so.set_of_books_id;
207
208 begin
209 l_api_name := 'processPayoff';
210 l_api_version_number := 1;
211 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
212
213 -- Standard Start of API savepoint
214 SAVEPOINT processPayoff;
215
216 -- Standard call to check for call compatibility.
217 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
218 l_api_name, G_PKG_NAME)
219 THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222
223 -- Initialize message list IF p_init_msg_list is set to TRUE.
224 IF FND_API.to_Boolean(p_init_msg_list) THEN
225 FND_MSG_PUB.initialize;
226 END IF;
227
228 -- Initialize API return status to SUCCESS
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 --
232 -- Api body
233 -- --------------------------------------------------------------------
234 -- initialize any variables here
235 l_cash_receipt_ids := p_cash_receipt_ids;
236 l_cash_receipts_count := p_cash_receipt_ids.count;
237 l_total_payoff_amount := 0;
238 l_total_receipts_amount := 0;
239 l_receipt_amount := 0;
240 l_amount_to_apply := 0;
241 l_new_interest := 0;
242 l_receipt_amount_remaining := 0;
243 l_invoice_amount_remaining := 0;
244 i := 0;
245 s := 0;
246 b_recalculate_interest := true;
247 l_final_balance := -1; --this is for final balance check
248
249 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipts passed ' || l_cash_receipts_count);
250 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Payoff loanID ' || p_loan_id);
251 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Payoff date ' || p_payoff_date);
252 open loans_cur(p_loan_id);
253 fetch loans_cur into
254 l_loan_currency
255 ,l_loan_exchange_rate
256 ,l_loan_exchange_date
257 ,l_loan_exchange_rate_type
258 ,l_phase;
259 close loans_cur;
260
261 open c_sob_currency;
262 fetch c_sob_currency into l_sob_currency;
263 close c_sob_currency;
264 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - LOAN CURRENCY ' || l_loan_currency);
265 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - loan_exchange_rate ' || l_loan_exchange_rate);
266
267 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - calculating Payoff ');
268 lns_financials.calculatePayoff(p_api_version => 1.0
269 ,p_init_msg_list => p_init_msg_list
270 ,p_loan_id => p_loan_id
271 ,p_payoff_date => p_payoff_date
272 ,x_payoff_tbl => l_payoff_tbl2
273 ,x_return_status => l_return_status
274 ,x_msg_count => l_msg_count
275 ,x_msg_data => l_msg_data);
276
277 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
278 FND_MESSAGE.SET_NAME('LNS', 'LNS_CALCULATE_PAYOFF_ERROR');
279 FND_MSG_PUB.Add;
280 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || FND_MSG_PUB.Get(p_encoded => 'F'));
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283
284 --2. create payoff document(s) in receivables
285 -- build the header for the loan document(s) to be created
286 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Building Invoice header/lines');
287 l_bill_headers_tbl(1).HEADER_ID := 101;
288 l_bill_headers_tbl(1).LOAN_ID := p_loan_id;
289 l_bill_headers_tbl(1).ASSOC_PAYMENT_NUM := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
290 l_bill_headers_tbl(1).DUE_DATE := p_payoff_date;
291
292 -- now build the lines for the loan document(s) to be created
293 for i in 1..l_payoff_tbl2.count
294 loop
295
296 if l_payoff_tbl2(i).unbilled_amount <> 0 and l_payoff_tbl2(i).payoff_purpose <> 'FEE' then
297 s := s + 1;
298 l_bill_lines_tbl(s).LINE_ID := i;
299 l_bill_lines_tbl(s).HEADER_ID := 101;
300 l_bill_lines_tbl(s).LINE_AMOUNT := l_payoff_tbl2(i).unbilled_amount;
301 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).PAYOFF_PURPOSE;
302 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - purpose ' || l_payoff_tbl2(i).payoff_purpose);
303 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - amount ' || l_payoff_tbl2(i).unbilled_amount);
304
305 elsif l_payoff_tbl2(i).unbilled_amount > 0 and l_payoff_tbl2(i).payoff_purpose = 'FEE' then
306
307 open c_fees(p_loan_id, l_phase, l_bill_headers_tbl(1).ASSOC_PAYMENT_NUM);
308 loop
309 s := s + 1;
310 fetch c_fees into
311 l_fee_amount, l_fee_name, l_fee_installment;
312 exit when c_fees%notfound;
313 l_bill_lines_tbl(s).LINE_ID := i;
314 l_bill_lines_tbl(s).HEADER_ID := 101;
315 l_bill_lines_tbl(s).LINE_AMOUNT := l_fee_amount;
316 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).payoff_purpose;
317 l_bill_lines_tbl(s).LINE_DESC := l_fee_name;
318 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - purpose ' || l_payoff_tbl2(i).payoff_purpose);
319 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - amount ' || l_payoff_tbl2(i).unbilled_amount);
320 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - fee ' || l_fee_name);
321 end loop;
322 close c_fees;
323 /*
324 elsif l_payoff_tbl2(i).unbilled_amount < 0 and l_payoff_tbl2(i).payoff_purpose = 'INT' then
325
326 -- we have an interest credit due we will credit out the interest on the last amortization
327 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - interest credit is due');
328 -- 1. check to see if documents have been paid,
329 -- unapply all cash from receivable applications
330 open c_applications(p_loan_id);
331 loop
332 fetch c_applications
333 into l_rec_application_id
334 ,l_cash_receipt_id
335 ,l_amount_applied
336 ,l_customer_trx_id
337 ,l_app_pay_sched_id
338 ,l_receipt_number
339 ,l_currency_code
340 ,l_rec_exchange_rate
341 ,l_rec_exchange_date
342 ,l_rec_exchange_rate_type
343 ,l_interest_trx_id
344 ,l_principal_trx_id
345 ,l_fee_trx_id;
346
347 exit when c_applications%notfound;
348
349 -- 2. if so, unapply all cash_receipts from document
350 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - unapplying cash from pay_sched_id: ' || l_rec_application_id);
351 ar_receipt_api_pub.unapply(p_api_version => 1.0
352 ,p_init_msg_list => p_init_msg_list
353 ,p_commit => FND_API.G_FALSE
354 ,p_receivable_application_id => l_rec_application_id
355 ,x_return_status => l_return_status
356 ,x_msg_count => l_msg_count
357 ,x_msg_data => l_msg_data);
358
359 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
360 FND_MESSAGE.SET_NAME('LNS', 'LNS_UNAPPLY_CASH_ERROR');
361 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RECEIPT_NUMBER');
362 FND_MESSAGE.SET_TOKEN('VALUE', l_receipt_number);
363 FND_MSG_PUB.Add;
364 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
365 RAISE FND_API.G_EXC_ERROR;
366 end if;
367
368
369 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - unapplied cash successfully');
370 -- 2a. keep unapplied cash_receipts in memory to use for payoff
371 -- bug #4191794
372 -- if this cash receipt_id exists in the set of selected cash receipts then
373 -- we will have to add it back to existing cash_receipt_id
374 l_bool_match := false;
375 for j in 1..l_cash_receipt_ids.count loop
376 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - check existing cr_id: ' || l_cash_receipt_ids(j).cash_receipt_id);
377 if l_cash_receipt_id = l_cash_receipt_ids(j).cash_receipt_id then
378 l_cash_receipt_ids(j).receipt_amount := l_cash_receipt_ids(j).receipt_amount + l_amount_applied;
379 l_bool_match := true;
380 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - matched existing cr_id: ' || l_cash_receipt_ids(j).cash_receipt_id);
381 exit;
382 end if;
383 end loop;
384
385 if not l_bool_match then
386 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - receipts count is: ' || l_cash_receipts_count);
387 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - adding applied cr_id: ' || l_cash_receipt_id);
388 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - cr_id amount: ' || l_amount_applied);
389 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - cr_id currency: ' || l_loan_currency);
390 l_cash_receipts_count := l_cash_receipts_count + 1;
391 l_cash_receipt_ids(l_cash_receipts_count).CASH_RECEIPT_ID := l_cash_receipt_id;
392 l_cash_receipt_ids(l_cash_receipts_count).RECEIPT_AMOUNT := l_amount_applied;
393 l_cash_receipt_ids(l_cash_receipts_count).RECEIPT_CURRENCY := l_loan_currency;
394
395 if l_currency_code <> l_loan_currency then
396 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_RATE := l_rec_exchange_rate;
397 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_DATE := l_rec_exchange_date;
398 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_RATE_TYPE := l_rec_exchange_rate_type;
399 end if;
400 end if;
401 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - added cash rec id ' || l_cash_receipt_id);
402 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - added cash amount ' || l_amount_applied || ' ' || l_currency_code);
403
404 end loop; --end unapplication loop
405
406 -- 3. credit document
407 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - crediting final interest document');
408 LNS_BILLING_BATCH_PUB.CREDIT_AMORTIZATION_PARTIAL(P_API_VERSION => 1.0
409 ,P_INIT_MSG_LIST => p_init_msg_list
410 ,P_COMMIT => FND_API.G_FALSE
411 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
412 ,P_LOAN_ID => p_loan_id
413 ,P_LINE_TYPE => 'INT'
414 ,X_RETURN_STATUS => l_return_status
415 ,X_MSG_COUNT => l_msg_count
416 ,X_MSG_DATA => l_msg_data);
417
418 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
419 FND_MESSAGE.SET_NAME('LNS', 'LNS_CREDIT_MEMO_ERROR');
420 FND_MSG_PUB.Add;
421 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
422 RAISE FND_API.G_EXC_ERROR;
423 end if;
424
425 -- 4. create new interest and principal document for difference
426 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATING INTEREST');
427 if b_recalculate_interest then
428 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - l_interest_trx_Id: ' || l_interest_trx_Id);
429 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - l_customer_trx_id: ' || l_customer_trx_id);
430
431 if l_interest_trx_id = l_customer_trx_id then
432 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATED INTEREST DUE: ' || l_new_interest);
433
434 -- over interest charge had previous applications
435 select amount_due_original + l_payoff_tbl2(i).unbilled_amount
436 into l_new_interest
437 from ar_payment_schedules
438 where payment_schedule_id = l_app_pay_sched_id;
439
440 else
441 -- over interest charge had no previous applications
442 select interest_amount + l_payoff_tbl2(i).unbilled_amount
443 into l_new_interest
444 from lns_amortization_scheds
445 where loan_id = p_loan_id
446 and reamortization_amount is null
447 and parent_amortization_id is null
448 --and reversed_flag = 'N'
449 and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
450
451 end if;
452 b_recalculate_interest := false;
453 end if;
454
455 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATED INTEREST DUE: ' || l_new_interest);
456
457 -- we will only create a new interest document IF there is an interest amount due
458 if l_new_interest > 0 then
459 s := s + 1;
460 l_bill_lines_tbl(s).LINE_ID := i;
461 l_bill_lines_tbl(s).HEADER_ID := 101;
462 l_bill_lines_tbl(s).LINE_AMOUNT := l_new_interest;
463 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).PAYOFF_PURPOSE;
464 end if;
465 */
466 end if;
467
468 end loop;
469
470 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - billing lines = ' || l_bill_lines_tbl.count);
471 if l_bill_lines_tbl.count >= 1 then
472
473 -- we have at least 1 invoice to create with amount > 0
474 -- must pass false for commit here or else payoff documents will exist in receivables
475 lns_billing_batch_pub.create_offcycle_bills(p_api_version => 1.0
476 ,p_init_msg_list => FND_API.G_TRUE
477 ,p_commit => FND_API.G_FALSE
478 ,p_validation_level => 100
479 ,p_bill_headers_tbl => l_bill_headers_tbl
480 ,p_bill_lines_tbl => l_bill_lines_tbl
481 ,x_return_status => l_return_status
482 ,x_msg_count => l_msg_count
483 ,x_msg_data => l_msg_data);
484
485 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_CREATION_ERROR');
487 FND_MSG_PUB.Add;
488 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 end if;
493
494 --3. getLoanInvoices (should return newly created documents)
495 -- pass payoff_date = null to avoid recalculating payoff amounts
496 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - getting loan invoices');
497 lns_payoff_pub.getLoanInvoices(p_api_version => 1
498 ,p_init_msg_list => p_init_msg_list
499 ,p_loan_ID => p_loan_id
500 ,p_payoff_date => null
501 ,x_invoices_tbl => l_invoices_tbl
502 ,x_return_status => l_return_status
503 ,x_msg_count => l_msg_count
504 ,x_msg_data => l_msg_data);
505
506 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
507 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_SUMMARY_ERROR');
508 FND_MSG_PUB.Add;
509 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512
513 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - # of unpaid invoices to process = ' || l_invoices_tbl.count);
514
515 --4. check if sum of receipts covers the remaining loan amount
516 -- if we need to do multi-currency, the receipt amounts should be
517 -- converted to loan_currency before making comparison
518 -- check if any of the receipts have been applied to any of the invoices
519 for k in 1..l_invoices_tbl.count
520 loop
521 l_total_payoff_amount := l_total_payoff_amount + l_invoices_tbl(k).REMAINING_AMOUNT;
522 end loop;
523
524 for j in 1..l_cash_receipt_ids.count
525 loop
526
527 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipt currency: ' || l_cash_receipt_ids(j).receipt_currency);
528 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipt amount: ' || l_cash_receipt_ids(j).receipt_amount);
529 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - xchg rate: ' || l_cash_receipt_ids(j).exchange_rate);
530
531 open c_receipt_number(l_cash_receipt_ids(j).cash_receipt_id);
532 fetch c_receipt_number into l_receipt_number, l_receipt_currency_orig;
533 close c_receipt_number ;
534
535 l_cash_receipt_ids(j).original_currency := l_receipt_currency_orig;
536 l_cash_receipt_ids(j).receipt_number := l_receipt_number;
537 l_total_receipts_amount := l_total_receipts_amount + l_cash_receipt_ids(j).receipt_amount;
538
539 end loop;
540
541 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - cash receipts to process: ' || l_cash_receipt_ids.count);
542 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - total receipts: ' || l_total_receipts_amount);
543 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - total payoff amount: ' || l_total_payoff_amount);
544
545 if l_total_receipts_amount < l_total_payoff_amount then
546 FND_MESSAGE.SET_NAME('LNS', 'LNS_PAYOFF_SHORT_CASH');
547 FND_MSG_PUB.Add;
548 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
549 RAISE FND_API.G_EXC_ERROR;
550 end if;
551
552 --5. apply cash
553 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - applying receipts...');
554 for k in 1..l_invoices_tbl.count
555 loop
556 -- find the first cash receipt that has not been applied previously to the invoice
557 -- if the amount of the receipt is > invoice then move on
558 -- else find the next receipt that has not been applied previously to the invoice
559 j := 1;
560 l_invoice_amount_remaining := l_invoices_tbl(k).REMAINING_AMOUNT;
561 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' --------- next invoice ----------');
562 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - payment_schedule_id: ' || l_invoices_tbl(k).payment_schedule_id );
563 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - invoice remaining amount: ' || l_invoices_tbl(k).REMAINING_AMOUNT);
564
565 --l_receipt_amount_remaining := l_cash_receipt_ids(j).RECEIPT_AMOUNT;
566 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipt amount: # ' || j || ' =' ||l_cash_receipt_ids(j).RECEIPT_AMOUNT);
567
568 while l_invoices_tbl(k).REMAINING_AMOUNT <> 0
569 loop
570 -- skip the receipts already exhausted
571 loop
572 exit when l_cash_receipt_ids(j).RECEIPT_AMOUNT > 0;
573 j := j + 1;
574 end loop;
575 l_receipt_amount_remaining := l_cash_receipt_ids(j).RECEIPT_AMOUNT;
576 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipt amount remaining: ' || j || '# =' ||l_receipt_amount_remaining);
577
578 Begin
579 select 'X'
580 into l_found
581 from ar_receivable_applications rap
582 where rap.cash_receipt_id = l_cash_receipt_ids(j).cash_receipt_id
583 and rap.applied_payment_schedule_id = l_invoices_tbl(k).payment_schedule_id
584 and rap.display = 'Y'
585 and rap.status = 'APP';
586 exception
587 when no_data_found then
588 null;
589 end;
590
591 if l_found = 'X' then
592 if j = l_cash_receipt_ids.count then
593 -- we have gone thru all the cash receipts and there is not enough
594 -- open receipts in the list to cover this invoice
595 FND_MESSAGE.SET_NAME('LNS', 'LNS_ALL_RECEIPTS_EXHAUSTED');
596 FND_MSG_PUB.Add;
597 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
598 RAISE FND_API.G_EXC_ERROR;
599 end if;
600
601 else
602 -- receipt has not been previously applied to invoice
603 -- determine amount to apply
604 if l_receipt_amount_remaining > l_invoice_amount_remaining then
605 l_amount_to_apply := l_invoice_amount_remaining;
606 else
607 l_amount_to_apply := l_receipt_amount_remaining;
608 end if;
609
610 if l_cash_receipt_ids(j).original_currency = l_cash_receipt_ids(j).receipt_currency then
611 l_trans_to_receipt_rate := null;
612 l_receipt_amount_from := null;
613 else
614 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - receipt currency orig: ' || l_cash_receipt_ids(j).original_currency || ' receipt currency passed: ' || l_cash_receipt_ids(j).receipt_currency);
615 if l_cash_receipt_ids(j).exchange_rate is not null then
616 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - using receipt exchange rate ' || l_cash_receipt_ids(j).exchange_rate);
617 l_trans_to_receipt_rate := l_cash_receipt_ids(j).exchange_rate;
618 else
619 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - using loan exchange rate ' || l_loan_exchange_rate);
620 l_trans_to_receipt_rate := l_loan_exchange_rate;
621 end if;
622 l_receipt_amount_from := l_amount_to_apply * l_trans_to_receipt_rate;
623 end if;
624
625 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - applying cash : ' || l_amount_to_apply);
626 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - applying cash from: ' || l_receipt_amount_from);
627 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - exchange rate: ' || l_trans_to_receipt_rate);
628
629 lns_payoff_pub.apply_receipt(p_cash_receipt_id => l_cash_receipt_ids(j).CASH_RECEIPT_ID
630 ,p_payment_schedule_id => l_invoices_tbl(k).payment_schedule_id
631 ,p_apply_amount => l_amount_to_apply -- in loan currency
632 ,p_apply_date => p_payoff_date
633 ,p_apply_amount_from => l_receipt_amount_from -- in receipt currency
634 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
635 ,x_return_status => l_return_status
636 ,x_msg_count => l_msg_count
637 ,x_msg_data => l_msg_data);
638
639 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - apply cash status: ' || l_return_status);
640 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
641 FND_MESSAGE.SET_NAME('LNS', 'LNS_APPLY_CASH_ERROR');
642 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RECEIPT_NUMBER');
643 FND_MESSAGE.SET_TOKEN('VALUE', l_cash_receipt_ids(j).receipt_number);
644 FND_MSG_PUB.Add;
645 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648
649 l_invoice_amount_remaining := l_invoice_amount_remaining - l_amount_to_apply;
650 l_receipt_amount_remaining := l_receipt_amount_remaining - l_amount_to_apply;
651 l_invoices_tbl(k).REMAINING_AMOUNT := l_invoices_tbl(k).REMAINING_AMOUNT - l_amount_to_apply;
652 l_cash_receipt_ids(j).RECEIPT_AMOUNT := l_cash_receipt_ids(j).RECEIPT_AMOUNT - l_amount_to_apply;
653 l_amount_to_apply := 0;
654
655 l_found := null;
656 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_invoice_amount_remaining : ' || l_invoice_amount_remaining);
657 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_receipt_amount_remaining : ' || l_receipt_amount_remaining);
658 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_invoices_tbl(k).REMAINING_AMOUNT : ' || l_invoices_tbl(k).REMAINING_AMOUNT);
659 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_cash_receipt_ids(j).RECEIPT_AMOUNT : ' || l_cash_receipt_ids(j).RECEIPT_AMOUNT);
660 end if;
661 j := j + 1;
662
663 end loop;
664
665 end loop;
666 -- end of step 5
667
668 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - calling LNS_BILLING_BATCH_PUB.processPaidLoans');
669 LNS_BILLING_BATCH_PUB.PROCESS_PAID_LOANS(P_API_VERSION => 1.0
670 ,P_INIT_MSG_LIST => FND_API.G_FALSE
671 ,P_COMMIT => FND_API.G_FALSE
672 ,P_VALIDATION_LEVEL => 100
673 ,P_LOAN_ID => p_loan_id
674 ,P_PAYOFF_DATE => p_payoff_date
675 ,x_return_status => l_return_status
676 ,x_msg_count => l_msg_count
677 ,x_msg_data => l_msg_data);
678 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - return status: ' || l_return_status);
679 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
680 COMMIT WORK;
681 else
682 RAISE FND_API.G_EXC_ERROR;
683 END IF;
684
685 -- ---------------------------------------------------------------------
686 -- End of API body
687 --
688
689 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
690
691 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
692
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 ROLLBACK TO processPayoff;
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 x_msg_count := l_msg_count;
698 x_msg_data := l_msg_data;
699 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
700 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
701
702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
703 ROLLBACK TO processPayoff;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 x_msg_count := l_msg_count;
706 x_msg_data := l_msg_data;
707 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
708 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
709
710 WHEN OTHERS THEN
711 ROLLBACK TO processPayoff;
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 x_msg_count := l_msg_count;
714 x_msg_data := l_msg_data;
715 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
716 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
717
718 end processPayoff;
719
720
721 /*=========================================================================
722 || PUBLIC PROCEDURE getLoanInvoices
723 ||
724 || DESCRIPTION
725 ||
726 || Overview: this function will return the invoices with a remaining balance
727 || for the loan
728 ||
729 || Parameter: p_loan_id = loan id
730 || p_payoff_date = include to be created payoff documents
731 || x_invoices_tbl = table of invoices
732 ||
733 || Source Tables: NA
734 ||
735 || Target Tables: NA
736 ||
737 || Return value:
738 ||
739 || KNOWN ISSUES
740 ||
741 || NOTES
742 ||
743 || MODIFICATION HISTORY
744 || Date Author Description of Changes
745 || 10/06/2004 1:51PM raverma Created
746 || 1/28/2005 raverma add late Fees processing
747 *=======================================================================*/
748 procedure getLoanInvoices(p_api_version IN NUMBER
749 ,p_init_msg_list IN VARCHAR2
750 ,p_loan_id in number
751 ,p_payoff_date in date
752 ,x_invoices_tbl OUT NOCOPY LNS_PAYOFF_PUB.INVOICE_DETAILS_TBL
753 ,x_return_status OUT NOCOPY VARCHAR2
754 ,x_msg_count OUT NOCOPY NUMBER
755 ,x_msg_data OUT NOCOPY VARCHAR2)
756
757 is
758
759 cursor c_loanInvoices(p_loan_id number)
760 is
761 select ps.customer_trx_id
762 ,payment_schedule_id
763 ,payment_number
764 ,trx_number
765 ,tty.name
766 ,amount_due_remaining
767 ,am.due_date
768 ,lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', decode(ps.customer_trx_id, principal_trx_Id, 'PRIN', interest_trx_id, 'INT', fee_trx_id, 'FEE')) line_type
769 ,ps.TRX_DATE
770 ,ps.GL_DATE
771 from lns_amortization_scheds am
772 ,ar_payment_schedules ps
773 ,ra_cust_trx_types tty
774 where (am.principal_trx_id = ps.customer_trx_id OR
775 am.interest_trx_id = ps.customer_trx_id OR
776 am.fee_trx_id = ps.customer_trx_id) and
777 ps.cust_trx_type_id = tty.cust_trx_type_id and
778 ps.amount_due_remaining <> 0 and
779 am.reamortization_amount is null and
780 am.reversed_flag <> 'Y' and
781 am.loan_id = p_loan_id
782 order by payment_number, line_type, amount_due_remaining;
783
784 cursor c_loanInfo(p_loan_id number) is
785 select loan_number
786 ,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
787 ,current_phase
788 from lns_loan_headers
789 where loan_id = p_loan_id;
790
791 cursor c_lastInterestAmount(p_loan_id number)
792 is
793 select interest_amount
794 from lns_amortization_scheds
795 where loan_id = p_loan_id
796 and reversed_flag <> 'Y'
797 and reamortization_amount is null
798 and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
799
800 cursor c_fees(p_loan_id number, p_phase varchar2, p_installment number)
801 is
802 select sched.fee_amount
803 ,fees.fee_name
804 ,sched.fee_installment
805 from lns_fee_schedules sched
806 ,lns_fees fees
807 where sched.loan_id = p_loan_id
808 and sched.fee_id = fees.fee_id
809 and sched.active_flag = 'Y'
810 and sched.billed_flag = 'N'
811 and sched.phase = p_phase
812 and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
813 and fees.fee_category <> 'RECUR';
814
815 l_api_name varchar2(25);
816 l_api_version_number number;
817 l_return_status VARCHAR2(1);
818 l_msg_count NUMBER;
819 l_msg_data VARCHAR2(32767);
820
821 l_invoices_tbl LNS_PAYOFF_PUB.INVOICE_DETAILS_TBL;
822 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
823 i number;
824 l_cust_trx_id number;
825 l_payment_schedule_id number;
826 l_invoice_number varchar2(60);
827 l_installment number;
828 l_trans_type varchar2(20);
829 l_remaining_amt number;
830 l_due_date date;
831 l_purpose varchar2(30);
832 l_billed_flag varchar2(1);
833 l_document_type varchar2(20);
834 l_last_interest number;
835 l_credit_name varchar2(30);
836 l_fee_amount number;
837 l_fee_name varchar2(50);
838 l_fee_installment number;
839 l_phase varchar2(30);
840 l_day_togl_after_dd number;
841 l_trx_date date;
842 l_term_id number;
843 l_sob number;
844 l_gl_date date;
845
846 begin
847
848 l_api_name := 'getLoanInvoices';
849 l_api_version_number := 1;
850 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
851
852 -- Standard Start of API savepoint
853 SAVEPOINT getLoanInvoices;
854
855 -- Standard call to check for call compatibility.
856 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
857 l_api_name, G_PKG_NAME)
858 THEN
859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
860 END IF;
861
862 -- Initialize message list IF p_init_msg_list is set to TRUE.
863 IF FND_API.to_Boolean(p_init_msg_list) THEN
864 FND_MSG_PUB.initialize;
865 END IF;
866
867 -- Initialize API return status to SUCCESS
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 --
871 -- Api body
872 -- --------------------------------------------------------------------
873 i := 0;
874
875 -- get billed unpaid invoices
876 open c_loanInvoices(p_loan_id);
877 LOOP
878 i := i + 1;
879 FETCH c_loanInvoices INTO
880 l_cust_trx_id
881 ,l_payment_schedule_id
882 ,l_installment
883 ,l_invoice_number
884 ,l_trans_type
885 ,l_remaining_amt
886 ,l_due_date
887 ,l_purpose
888 ,l_trx_date
889 ,l_gl_date;
890
891 EXIT WHEN c_loanInvoices%NOTFOUND;
892
893 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - installment #: ' || l_installment);
894 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_cust_trx_id: ' || l_cust_trx_id);
895 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_remaining_amt: ' || l_remaining_amt);
896 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_due_date: ' || l_due_date);
897 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_trx_date: ' || l_trx_date);
898 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_gl_date: ' || l_gl_date);
899 l_invoices_tbl(i).CUST_TRX_ID := l_cust_trx_id;
900 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID:= l_payment_schedule_id;
901 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
902 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
903 l_invoices_tbl(i).TRANSACTION_TYPE := l_trans_type;
904 l_invoices_tbl(i).REMAINING_AMOUNT := l_remaining_amt;
905 l_invoices_tbl(i).DUE_DATE := l_due_date;
906 l_invoices_tbl(i).TRX_DATE := l_trx_date;
907 l_invoices_tbl(i).GL_DATE := l_gl_date;
908 l_invoices_tbl(i).PURPOSE := l_purpose;
909 l_invoices_tbl(i).BILLED_FLAG := 'Y';
910
911 END LOOP;
912 close c_loanInvoices;
913
914 -- get the additional records "to be created"
915 if p_payoff_date is not null then
916 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting addition TO BE CREATED documents');
917 --dbms_output.put_line('calculating payoof amounts');
918 lns_financials.calculatePayoff(p_api_version => 1.0
919 ,p_init_msg_list => p_init_msg_list
920 ,p_loan_id => p_loan_id
921 ,p_payoff_date => p_payoff_date
922 ,x_payoff_tbl => l_payoff_tbl2
923 ,x_return_status => l_return_status
924 ,x_msg_count => l_msg_count
925 ,x_msg_data => l_msg_data);
926 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
927 --FND_MESSAGE.SET_NAME('LNS', 'LNS_UPDATE_HEADER_ERROR');
928 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
929 RAISE FND_API.G_EXC_ERROR;
930 END IF;
931
932 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - payoff tbl count ' || l_payoff_tbl2.count);
933
934 open c_loanInfo(p_loan_id);
935 fetch c_loanInfo into l_invoice_number, l_installment, l_phase;
936 close c_loanInfo;
937
938 -- get trx date
939 LNS_BILLING_UTIL_PUB.GET_TRX_DATE(P_LOAN_ID => p_loan_id,
940 P_DUE_DATE => p_payoff_date,
941 X_TRX_DATE => l_trx_date,
942 X_RA_TERM_ID => l_term_id);
943
944 select nvl(DAYS_TOGL_AFTER_DUE_DATE, 0)
945 into l_day_togl_after_dd
946 FROM LNS_SYSTEM_OPTIONS
947 WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID();
948
949 -- get gl_date
950 LNS_BILLING_UTIL_PUB.VALIDATE_AND_DEFAULT_GL_DATE(
951 p_gl_date => l_trx_date + nvl(l_day_togl_after_dd, 0),
952 p_trx_date => l_trx_date,
953 p_set_of_books_id => l_sob,
954 x_default_gl_date => l_gl_date);
955
956 for k in 1..l_payoff_tbl2.count
957 loop
958
959 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || l_payoff_tbl2(k).payoff_purpose);
960 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || l_payoff_tbl2(k).unbilled_amount);
961
962 i := l_invoices_tbl.count;
963
964 if l_payoff_tbl2(k).unbilled_amount <> 0 and l_payoff_tbl2(k).PAYOFF_PURPOSE <> 'FEE' then
965
966 i := i + 1;
967 l_invoices_tbl(i).CUST_TRX_ID := null;
968 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
969 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
970 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName(l_payoff_tbl2(k).PAYOFF_PURPOSE);
971 l_invoices_tbl(i).REMAINING_AMOUNT := l_payoff_tbl2(k).unbilled_amount;
972 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
973 l_invoices_tbl(i).TRX_DATE := l_trx_date;
974 l_invoices_tbl(i).GL_DATE := l_gl_date;
975 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
976 l_invoices_tbl(i).BILLED_FLAG := 'N';
977 /*
978 elsif l_payoff_tbl2(k).unbilled_amount < 0 and l_payoff_tbl2(k).PAYOFF_PURPOSE = 'INT' then
979
980 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting last interest documents ');
981 i := l_invoices_tbl.count;
982 -- find the last interest installment we will create a credit memo in that amount
983 -- and we will create an invoice of INT type with
984 open c_lastInterestAmount(p_loan_id);
985 fetch c_lastInterestAmount into l_last_interest;
986 close c_lastInterestAmount;
987
988 select meaning into l_credit_name
989 from ar_lookups
990 where lookup_type = 'INV/CM'
991 and lookup_code = 'CM';
992
993 -- interest document (credit)
994 i := i + 1;
995 l_invoices_tbl(i).CUST_TRX_ID := null;
996 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
997 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
998 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
999 l_invoices_tbl(i).TRANSACTION_TYPE := l_credit_name;
1000 l_invoices_tbl(i).REMAINING_AMOUNT := - l_last_interest;
1001 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1002 l_invoices_tbl(i).BILLED_FLAG := 'N';
1003
1004 if l_last_interest + l_payoff_tbl2(k).unbilled_amount > 0 then
1005 -- new interest document
1006 i := i + 1;
1007 l_invoices_tbl(i).CUST_TRX_ID := null;
1008 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1009 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1010 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1011 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName(l_payoff_tbl2(k).PAYOFF_PURPOSE);
1012 l_invoices_tbl(i).REMAINING_AMOUNT := l_last_interest + l_payoff_tbl2(k).unbilled_amount;
1013 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1014 l_invoices_tbl(i).BILLED_FLAG := 'N';
1015 end if;
1016 */
1017 end if;
1018
1019 end loop;
1020
1021 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting last fees ');
1022 i := l_invoices_tbl.count;
1023 open c_fees(p_loan_id, l_phase, l_installment);
1024 loop
1025 i := i + 1;
1026 fetch c_fees into
1027 l_fee_amount, l_fee_name, l_fee_installment;
1028
1029 exit when c_fees%notfound;
1030 l_invoices_tbl(i).CUST_TRX_ID := null;
1031 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1032 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1033 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1034 l_invoices_tbl(i).TRX_DATE := l_trx_date;
1035 l_invoices_tbl(i).GL_DATE := l_gl_date;
1036 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName('FEE');
1037 l_invoices_tbl(i).REMAINING_AMOUNT := l_fee_amount;
1038 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', 'FEE') || ' - ' || l_fee_name;
1039 l_invoices_tbl(i).BILLED_FLAG := 'N';
1040 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - fee found');
1041 end loop;
1042 close c_fees;
1043
1044 end if;
1045
1046 x_invoices_tbl := l_invoices_tbl;
1047 -- --------------------------------------------------------------------
1048 -- End of API body
1049 --
1050
1051 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1052
1053 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1054
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_ERROR THEN
1057 ROLLBACK TO getLoanInvoices;
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 x_msg_count := l_msg_count;
1060 x_msg_data := l_msg_data;
1061 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1062 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 ROLLBACK TO getLoanInvoices;
1066 x_return_status := FND_API.G_RET_STS_ERROR;
1067 x_msg_count := l_msg_count;
1068 x_msg_data := l_msg_data;
1069 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1070 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1071
1072 WHEN OTHERS THEN
1073 ROLLBACK TO getLoanInvoices;
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 x_msg_count := l_msg_count;
1076 x_msg_data := l_msg_data;
1077 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1078 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1079
1080 end getLoanInvoices;
1081
1082 /*========================================================================
1083 | PRIVATE PROCEDURE APPLY_RECEIPT
1084 |
1085 | DESCRIPTION
1086 | This procedure applies cash receipt to invoice.
1087 |
1088 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1089 | CREATE_SINGLE_OFFCYCLE_BILL
1090 |
1091 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1092 | LogMessage
1093 |
1094 | PARAMETERS
1095 | P_CASH_RECEIPT_ID IN Cash receipt to apply
1096 | P_TRX_ID IN Apply receipt to this trx
1097 | P_TRX_LINE_ID IN Apply receipt to this trx line
1098 | P_APPLY_AMOUNT IN Apply amount
1099 |
1100 | KNOWN ISSUES
1101 | None
1102 |
1103 | NOTES
1104 | Any interesting aspect of the code in the package body which needs
1105 | to be stated.
1106 |
1107 | MODIFICATION HISTORY
1108 | Date Author Description of Changes
1109 | 01-01-2004 scherkas Created
1110 |
1111 *=======================================================================*/
1112 PROCEDURE APPLY_RECEIPT(P_CASH_RECEIPT_ID IN NUMBER
1113 ,P_PAYMENT_SCHEDULE_ID IN NUMBER
1114 ,P_APPLY_AMOUNT IN NUMBER
1115 ,P_APPLY_DATE IN DATE
1116 ,p_apply_amount_from IN NUMBER
1117 ,p_trans_to_receipt_rate IN NUMBER
1118 ,x_return_status OUT NOCOPY VARCHAR2
1119 ,x_msg_count OUT NOCOPY NUMBER
1120 ,x_msg_data OUT NOCOPY VARCHAR2)
1121
1122 IS
1123
1124 /*-----------------------------------------------------------------------+
1125 | Local Variable Declarations and initializations |
1126 +-----------------------------------------------------------------------*/
1127
1128 l_api_name VARCHAR2(30);
1129 l_return_status VARCHAR2(1);
1130 l_msg_count NUMBER;
1131 l_msg_data VARCHAR2(32767);
1132 l_receipt_rem_amount number;
1133 g_day_togl_after_dd number;
1134 l_apply_date date;
1135 l_due_date date;
1136
1137 /*-----------------------------------------------------------------------+
1138 | Cursor Declarations |
1139 +-----------------------------------------------------------------------*/
1140
1141 CURSOR receipt_cur(P_RECEIPT_ID number) IS
1142 select ABS(AMOUNT_DUE_REMAINING)
1143 from ar_payment_schedules
1144 where CASH_RECEIPT_ID = P_RECEIPT_ID
1145 and status = 'OP'
1146 and class = 'PMT';
1147
1148 cursor c_due_date(p_payment_schedule_id number) is
1149 select due_date
1150 from ar_payment_schedules
1151 where payment_schedule_id = p_payment_schedule_id;
1152
1153
1154 BEGIN
1155
1156 l_api_name := 'apply_receipt';
1157 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1158
1159 -- Standard Start of API savepoint
1160 SAVEPOINT APPLY_RECEIPT;
1161
1162 -- Initialize API return status to SUCCESS
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164
1165 --
1166 -- Api body
1167 -- --------------------------------------------------------------------
1168 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Applying cash receipt ' || P_CASH_RECEIPT_ID);
1169 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_PS_ID: ' || P_PAYMENT_SCHEDULE_ID);
1170 --LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_TRX_LINE_ID: ' || P_TRX_LINE_ID);
1171 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_APPLY_AMOUNT: ' || P_APPLY_AMOUNT);
1172 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_APPLY_AMOUNT_FROM: ' || P_APPLY_AMOUNT_FROM);
1173 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' p_trans_to_receipt_rate: ' || p_trans_to_receipt_rate);
1174
1175 /* verify input data */
1176 if P_CASH_RECEIPT_ID is null then
1177 FND_MESSAGE.SET_NAME('LNS', 'LNS_CR_NOT_SET');
1178 FND_MSG_PUB.Add;
1179 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1180 RAISE FND_API.G_EXC_ERROR;
1181 end if;
1182
1183 if P_APPLY_AMOUNT is null or P_APPLY_AMOUNT = 0 then
1184 FND_MESSAGE.SET_NAME('LNS', 'LNS_AMOUNT_NOT_SET');
1185 FND_MSG_PUB.Add;
1186 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1187 RAISE FND_API.G_EXC_ERROR;
1188 end if;
1189
1190 /* verifying requested qpply amount */
1191 open receipt_cur(P_CASH_RECEIPT_ID);
1192 fetch receipt_cur into l_receipt_rem_amount;
1193
1194 if receipt_cur%NOTFOUND then
1195 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_CR_FOUND');
1196 FND_MSG_PUB.Add;
1197 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1198 RAISE FND_API.G_EXC_ERROR;
1199 end if;
1200
1201 close receipt_cur;
1202 /*
1203 if l_receipt_rem_amount < P_APPLY_AMOUNT then
1204 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_CR_FUNDS');
1205 FND_MSG_PUB.Add;
1206 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1207 RAISE FND_API.G_EXC_ERROR;
1208 end if;
1209 */
1210 select
1211 DAYS_TOGL_AFTER_DUE_DATE
1212 into g_day_togl_after_dd
1213 FROM LNS_SYSTEM_OPTIONS
1214 WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID() ;
1215
1216 open c_due_date(P_PAYMENT_SCHEDULE_ID);
1217 fetch c_due_date into l_due_date;
1218 close c_due_date;
1219
1220 if l_due_date > p_apply_date then
1221 l_apply_date := l_due_Date;
1222 else
1223 l_apply_date := p_apply_date;
1224 end if;
1225 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' due_date: ' || l_due_date);
1226 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' apply_date: ' || l_apply_date);
1227
1228 /* Applying cash receipt to invoice */
1229 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1230 ,P_INIT_MSG_LIST => FND_API.G_TRUE
1231 ,P_COMMIT => FND_API.G_FALSE
1232 ,X_RETURN_STATUS => L_RETURN_STATUS
1233 ,X_MSG_COUNT => L_MSG_COUNT
1234 ,X_MSG_DATA => L_MSG_DATA
1235 ,p_cash_receipt_id => P_CASH_RECEIPT_ID
1236 ,p_applied_payment_schedule_id => P_PAYMENT_SCHEDULE_ID
1237 ,p_apply_date => l_apply_date
1238 ,p_apply_gl_date => l_apply_date + g_day_togl_after_dd
1239 ,p_amount_applied => P_APPLY_AMOUNT
1240 ,p_amount_applied_from => p_apply_amount_from
1241 ,p_trans_to_receipt_rate => p_trans_to_receipt_rate);
1242
1243 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' return_status: ' || l_return_status);
1244 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' msg_data: ' || substr(l_msg_data,1,225));
1245
1246 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1247 FND_MESSAGE.SET_NAME('LNS', 'LNS_APPL_CR_FAIL');
1248 FND_MSG_PUB.Add;
1249 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1250 RAISE FND_API.G_EXC_ERROR;
1251 ELSE
1252 null;
1253 --LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully applied cash receipt to trx ' || P_TRX_ID || ' line ' || P_TRX_LINE_ID);
1254 END IF;
1255 -- END OF BODY OF API
1256
1257 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1258
1259 EXCEPTION
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 ROLLBACK TO APPLY_RECEIPT;
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 x_msg_count := l_msg_count;
1264 x_msg_data := l_msg_data;
1265 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1266 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1267
1268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1269 ROLLBACK TO APPLY_RECEIPT;
1270 x_return_status := FND_API.G_RET_STS_ERROR;
1271 x_msg_count := l_msg_count;
1272 x_msg_data := l_msg_data;
1273 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1274 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1275
1276 WHEN OTHERS THEN
1277 ROLLBACK TO APPLY_RECEIPT;
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279 x_msg_count := l_msg_count;
1280 x_msg_data := l_msg_data;
1281 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1282 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1283 END;
1284
1285 /*=========================================================================
1286 || PUBLIC FUNCTION getConvertedReceiptAmount
1287 ||
1288 || DESCRIPTION
1289 ||
1290 || Overview: This function returns the receipt balance amount in loan currency
1291 || for the loan
1292 ||
1293 || Parameter: p_receipt_id = cash_receipt_id
1294 || p_loan_id = loan id
1295 || Source Tables: NA
1296 ||
1297 || Target Tables: NA
1298 ||
1299 || Return value: Converted receipt balance amount in loan currency
1300 ||
1301 || KNOWN ISSUES
1302 ||
1303 || NOTES
1304 ||
1305 || MODIFICATION HISTORY
1306 || Date Author Description of Changes
1307 || 10/20/2004 karamach Created
1308 *=======================================================================*/
1309 FUNCTION getConvertedReceiptAmount(p_receipt_id in number, p_loan_id in number) return NUMBER
1310 IS
1311
1312 Cursor getReceiptCurrencyBalance(p_recpt_id number) is
1313 select CR.CURRENCY_CODE RECEIPT_CURRENCY_CODE,
1314 (select SUM(DECODE(app.status,'UNAPP',NVL(app.amount_applied,0),0)) amt from AR_RECEIVABLE_APPLICATIONS app where app.cash_receipt_id = CR.CASH_RECEIPT_ID) RECEIPT_CURRENCY_AMOUNT,
1315 CR.EXCHANGE_RATE_TYPE,
1316 CR.EXCHANGE_DATE,
1317 CR.EXCHANGE_RATE
1318 from AR_CASH_RECEIPTS CR
1319 where CR.cash_receipt_id = p_recpt_id;
1320
1321 Cursor getLoanCurrencyConversion(p_loanId number) is
1322 select loan.loan_currency LOAN_CURRENCY_CODE
1323 from LNS_LOAN_HEADERS_ALL loan
1324 where loan.loan_id = p_loanId;
1325
1326 --receipt_currency_code varchar2(3);
1327 --loan_currency_code varchar2(3);
1328 receipt_currency_amount number;
1329 loan_currency_amount number;
1330
1331 l_loan_currency_code varchar2(15);
1332 l_loan_exchange_rate_type varchar2(30);
1333 l_loan_exchange_rate number;
1334 l_loan_exchange_date date;
1335 l_sob_currency_code varchar2(15);
1336
1337 l_rec_currency_code varchar2(15);
1338 l_rec_exchange_rate_type varchar2(30);
1339 l_rec_exchange_date date;
1340 l_rec_exchange_rate number;
1341
1342 l_return number;
1343
1344 cursor c_loan_exchange_info(p_loan_id number) is
1345 select lnh.exchange_rate_type
1346 ,lnh.exchange_rate
1347 ,lnh.exchange_date
1348 ,lnh.loan_currency
1349 from lns_loan_headers lnh
1350 where loan_id = p_loan_id;
1351
1352 cursor c_sob_currency is
1353 SELECT sb.currency_code
1354 FROM lns_system_options so,
1355 gl_sets_of_books sb
1356 WHERE sb.set_of_books_id = so.set_of_books_id;
1357
1358 BEGIN
1359
1360 open getReceiptCurrencyBalance(p_receipt_id);
1361 --fetch getReceiptCurrencyBalance into receipt_currency_code,receipt_currency_amount;
1362 fetch getReceiptCurrencyBalance into
1363 l_rec_currency_code
1364 ,receipt_currency_amount
1365 ,l_rec_exchange_rate_type
1366 ,l_rec_exchange_date
1367 ,l_rec_exchange_rate;
1368 close getReceiptCurrencyBalance;
1369
1370 open c_loan_exchange_info(p_loan_id);
1371 fetch c_loan_exchange_info into
1372 l_loan_exchange_rate_type
1373 ,l_loan_exchange_rate
1374 ,l_loan_exchange_date
1375 ,l_loan_currency_code;
1376 close c_loan_exchange_info;
1377
1378 if (l_rec_currency_code is null or receipt_currency_amount is null or l_loan_currency_code is null) then
1379 return 0;
1380 elsif (l_rec_currency_code = l_loan_currency_code) then
1381 return receipt_currency_amount;
1382 else -- rec currency <> loan currency
1383 open c_sob_currency;
1384 fetch c_sob_currency into l_sob_currency_code;
1385 close c_sob_currency;
1386
1387 if l_rec_currency_code = l_sob_currency_code then
1388 l_return := receipt_currency_amount / l_loan_exchange_rate;
1389
1390 else -- rec_currency <> loan_currency <> sob currency
1391 -- this is not valid unless user enters the conversion rate/date/type on payoff UI
1392 -- that is the approach AR takes
1393 l_return := 0;
1394 end if;
1395
1396 return l_return;
1397
1398 end if;
1399
1400 END getConvertedReceiptAmount;
1401
1402
1403 procedure getInvoicesForLoanCancellation(p_api_version IN NUMBER
1404 ,p_init_msg_list IN VARCHAR2
1405 ,p_loan_id IN NUMBER
1406 ,p_cancellation_date IN DATE
1407 ,x_invoices_tbl OUT NOCOPY LNS_PAYOFF_PUB.LOAN_CANCEL_DETAILS_TBL
1408 ,x_return_status OUT NOCOPY VARCHAR2
1409 ,x_msg_count OUT NOCOPY NUMBER
1410 ,x_msg_data OUT NOCOPY VARCHAR2)
1411 is
1412
1413 l_api_name varchar2(50);
1414 l_api_version_number number;
1415 l_return_status VARCHAR2(1);
1416 l_msg_count NUMBER;
1417 l_msg_data VARCHAR2(32767);
1418
1419 l_invoices_tbl LNS_PAYOFF_PUB.LOAN_CANCEL_DETAILS_TBL;
1420 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
1421 i number;
1422 l_cust_trx_id number;
1423 l_payment_schedule_id number;
1424 l_invoice_number varchar2(60);
1425 l_installment number;
1426 l_invoice_number1 varchar2(60);
1427 l_installment1 number;
1428 l_trans_type varchar2(100);
1429 l_remaining_amt number;
1430 l_fee_amount number;
1431 l_fee_name varchar2(50);
1432 l_fee_installment number;
1433 l_phase varchar2(30);
1434 l_class varchar2(30);
1435 l_loan_line_id number;
1436 l_funded_amount number;
1437 l_remaining_prin number;
1438 l_precision number;
1439 l_sum_amount number;
1440 l_sum_percent number;
1441 l_purpose_code varchar2(30);
1442 l_purpose varchar2(80);
1443 l_due_date date;
1444 l_day_togl_after_dd number;
1445 l_sob number;
1446 l_trx_date date;
1447 l_gl_date date;
1448 l_term_id number;
1449
1450 CURSOR precision_cur(P_LOAN_ID number) IS
1451 select curr.precision
1452 from fnd_currencies curr,
1453 lns_loan_headers_all loan
1454 where loan.loan_id = P_LOAN_ID and
1455 curr.currency_code = loan.loan_currency;
1456
1457 cursor c_loanInfo(p_loan_id number) is
1458 select loan_number
1459 ,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
1460 ,current_phase
1461 ,LOAN_CLASS_CODE
1462 ,FUNDED_AMOUNT
1463 from lns_loan_headers
1464 where loan_id = p_loan_id;
1465
1466 cursor c_fees(p_loan_id number, p_phase varchar2, p_installment number) is
1467 select sched.fee_amount
1468 ,fees.fee_name
1469 ,sched.fee_installment
1470 from lns_fee_schedules sched
1471 ,lns_fees fees
1472 where sched.loan_id = p_loan_id
1473 and sched.fee_id = fees.fee_id
1474 and sched.active_flag = 'Y'
1475 and sched.billed_flag = 'N'
1476 and sched.phase = p_phase
1477 and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
1478 and fees.fee_category <> 'RECUR';
1479
1480 CURSOR c_get_loan_lines(P_LOAN_ID number) IS
1481 select lines.LOAN_LINE_ID,
1482 lines.REFERENCE_ID,
1483 lines.PAYMENT_SCHEDULE_ID,
1484 lines.REFERENCE_NUMBER,
1485 trx_type.name || ' - ' || trx_type_lkup.meaning,
1486 lines.REQUESTED_AMOUNT,
1487 lines.INSTALLMENT_NUMBER
1488 from lns_loan_lines lines,
1489 RA_CUSTOMER_TRX_ALL cust_trx,
1490 RA_CUST_TRX_TYPES trx_type,
1491 ar_lookups trx_type_lkup
1492 where lines.status = 'APPROVED'
1493 and lines.REFERENCE_ID <> -1
1494 and lines.LOAN_ID = P_LOAN_ID
1495 and cust_trx.customer_trx_id = lines.reference_id
1496 and cust_trx.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID
1497 and trx_type_lkup.lookup_type = 'INV/CM'
1498 and trx_type_lkup.lookup_code = trx_type.type
1499 order by lines.LOAN_LINE_ID;
1500
1501 CURSOR unpaid_inv_cur(P_LOAN_ID number) IS
1502 select ps.customer_trx_id
1503 ,payment_schedule_id
1504 ,payment_number
1505 ,trx_number
1506 ,tty.name
1507 ,amount_due_remaining
1508 ,decode(ps.customer_trx_id, principal_trx_Id, 'PRIN', interest_trx_id, 'INT', fee_trx_id, 'FEE')
1509 ,lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', decode(ps.customer_trx_id, principal_trx_Id, 'PRIN', interest_trx_id, 'INT', fee_trx_id, 'FEE')) line_type
1510 ,ps.due_date
1511 ,ps.TRX_DATE
1512 ,ps.GL_DATE
1513 from lns_amortization_scheds am
1514 ,ar_payment_schedules ps
1515 ,ra_cust_trx_types tty
1516 where (am.principal_trx_id = ps.customer_trx_id OR
1517 am.interest_trx_id = ps.customer_trx_id OR
1518 am.fee_trx_id = ps.customer_trx_id) and
1519 ps.cust_trx_type_id = tty.cust_trx_type_id and
1520 ps.amount_due_remaining > 0 and
1521 am.reamortization_amount is null and
1522 am.reversed_flag <> 'Y' and
1523 am.loan_id = p_loan_id
1524 order by payment_number, line_type;
1525
1526
1527 begin
1528
1529 l_api_name := 'getInvoicesForLoanCancellation';
1530 l_api_version_number := 1;
1531 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1532
1533 -- Standard Start of API savepoint
1534 SAVEPOINT getInvoicesForLoanCancellation;
1535
1536 -- Standard call to check for call compatibility.
1537 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
1538 l_api_name, G_PKG_NAME)
1539 THEN
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 END IF;
1542
1543 -- Initialize message list IF p_init_msg_list is set to TRUE.
1544 IF FND_API.to_Boolean(p_init_msg_list) THEN
1545 FND_MSG_PUB.initialize;
1546 END IF;
1547
1548 -- Initialize API return status to SUCCESS
1549 x_return_status := FND_API.G_RET_STS_SUCCESS;
1550
1551 --
1552 -- Api body
1553 -- --------------------------------------------------------------------
1554
1555 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1556 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID = ' || P_LOAN_ID);
1557 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_cancellation_date = ' || p_cancellation_date);
1558
1559 /* verify input parameters */
1560 if P_LOAN_ID is null then
1561 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1562 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_LOAN_ID');
1563 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
1564 FND_MSG_PUB.ADD;
1565 RAISE FND_API.G_EXC_ERROR;
1566 end if;
1567 if p_cancellation_date is null then
1568 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1569 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_cancellation_date');
1570 FND_MESSAGE.SET_TOKEN('VALUE', p_cancellation_date);
1571 FND_MSG_PUB.ADD;
1572 RAISE FND_API.G_EXC_ERROR;
1573 end if;
1574
1575 open c_loanInfo(p_loan_id);
1576 fetch c_loanInfo into l_invoice_number,
1577 l_installment,
1578 l_phase,
1579 l_class,
1580 l_funded_amount;
1581 close c_loanInfo;
1582 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_invoice_number = ' || l_invoice_number);
1583 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installment = ' || l_installment);
1584 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_phase = ' || l_phase);
1585 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_class = ' || l_class);
1586 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_funded_amount = ' || l_funded_amount);
1587
1588 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_financials.calcLoanRemainingAmounts...');
1589 lns_financials.calcLoanRemainingAmounts(p_api_version => 1.0
1590 ,p_init_msg_list => FND_API.G_TRUE
1591 ,p_loan_id => p_loan_id
1592 ,p_date => p_cancellation_date
1593 ,p_reason => 'LOAN_CANCELLATION'
1594 ,x_payoff_tbl => l_payoff_tbl2
1595 ,x_return_status => l_return_status
1596 ,x_msg_count => l_msg_count
1597 ,x_msg_data => l_msg_data);
1598 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
1599 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1600 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1601 RAISE FND_API.G_EXC_ERROR;
1602 END IF;
1603 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'payoff tbl count = ' || l_payoff_tbl2.count);
1604
1605 open precision_cur(P_LOAN_ID);
1606 fetch precision_cur into l_precision;
1607 close precision_cur;
1608
1609 -- get trx date
1610 LNS_BILLING_UTIL_PUB.GET_TRX_DATE(P_LOAN_ID => P_LOAN_ID,
1611 P_DUE_DATE => p_cancellation_date,
1612 X_TRX_DATE => l_trx_date,
1613 X_RA_TERM_ID => l_term_id);
1614
1615 select nvl(DAYS_TOGL_AFTER_DUE_DATE, 0), SET_OF_BOOKS_ID
1616 into l_day_togl_after_dd, l_sob
1617 FROM LNS_SYSTEM_OPTIONS
1618 WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID();
1619
1620 LNS_BILLING_UTIL_PUB.VALIDATE_AND_DEFAULT_GL_DATE(
1621 p_gl_date => (l_trx_date + l_day_togl_after_dd),
1622 p_trx_date => l_trx_date,
1623 p_set_of_books_id => l_sob,
1624 x_default_gl_date => l_gl_date);
1625
1626 -- filling up returned table
1627 i := 0;
1628 for k in 1..l_payoff_tbl2.count loop
1629
1630 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Payoff record ' || k);
1631 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYOFF_PURPOSE = ' || l_payoff_tbl2(k).PAYOFF_PURPOSE);
1632 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'BILLED_AMOUNT = ' || l_payoff_tbl2(k).BILLED_AMOUNT);
1633 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'UNBILLED_AMOUNT = ' || l_payoff_tbl2(k).UNBILLED_AMOUNT);
1634 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'TOTAL_AMOUNT = ' || l_payoff_tbl2(k).TOTAL_AMOUNT);
1635
1636 l_sum_amount := 0;
1637 l_sum_percent := 0;
1638 if l_payoff_tbl2(k).unbilled_amount <> 0 and
1639 (l_payoff_tbl2(k).PAYOFF_PURPOSE = 'INT' or l_payoff_tbl2(k).PAYOFF_PURPOSE = 'PRIN')
1640 then
1641
1642 if l_payoff_tbl2(k).PAYOFF_PURPOSE = 'PRIN' and l_class = 'ERS' then
1643 l_remaining_prin := l_payoff_tbl2(k).unbilled_amount;
1644 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_prin = ' || l_remaining_prin);
1645
1646 open c_get_loan_lines(p_loan_id);
1647 loop
1648 fetch c_get_loan_lines into
1649 l_loan_line_id,
1650 l_cust_trx_id,
1651 l_payment_schedule_id,
1652 l_invoice_number1,
1653 l_trans_type,
1654 l_remaining_amt,
1655 l_installment1;
1656 exit when c_get_loan_lines%notfound;
1657
1658 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_line_id = ' || l_loan_line_id);
1659 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_cust_trx_id = ' || l_cust_trx_id);
1660 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_schedule_id = ' || l_payment_schedule_id);
1661 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_invoice_number1 = ' || l_invoice_number1);
1662 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_trans_type = ' || l_trans_type);
1663 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_amt = ' || l_remaining_amt);
1664
1665 i := i + 1;
1666 l_invoices_tbl(i).CUST_TRX_ID := l_cust_trx_id;
1667 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID := l_payment_schedule_id;
1668 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment1;
1669 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number1;
1670 l_invoices_tbl(i).TRANSACTION_TYPE := l_trans_type;
1671 l_invoices_tbl(i).REMAINING_AMOUNT := l_remaining_amt;
1672 l_invoices_tbl(i).DUE_DATE := p_cancellation_date;
1673 l_invoices_tbl(i).TRX_DATE := p_cancellation_date;
1674 l_invoices_tbl(i).GL_DATE := p_cancellation_date;
1675 l_invoices_tbl(i).PURPOSE_CODE := 'PRIN';
1676 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', 'PRIN');
1677 l_invoices_tbl(i).LOAN_LINE_ID := l_loan_line_id;
1678 l_invoices_tbl(i).AMOUNT_TO_PROCESS := round(l_remaining_prin * (l_remaining_amt/l_funded_amount), l_precision);
1679 l_invoices_tbl(i).PERCENT_TO_PROCESS := round(l_remaining_amt*100/l_funded_amount, 4);
1680 l_invoices_tbl(i).BILLED_FLAG := 'N';
1681 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adding original receivable ' || l_invoices_tbl(i).INVOICE_NUMBER ||
1682 ' for ' || l_invoices_tbl(i).AMOUNT_TO_PROCESS);
1683
1684 if (l_sum_amount + l_invoices_tbl(i).AMOUNT_TO_PROCESS) > l_payoff_tbl2(k).unbilled_amount then
1685 l_invoices_tbl(i).AMOUNT_TO_PROCESS := l_payoff_tbl2(k).unbilled_amount - l_sum_amount;
1686 l_invoices_tbl(i).PERCENT_TO_PROCESS := 100 - l_sum_percent;
1687 end if;
1688
1689 l_sum_amount := l_sum_amount + l_invoices_tbl(i).AMOUNT_TO_PROCESS;
1690 l_sum_percent := l_sum_percent + l_invoices_tbl(i).PERCENT_TO_PROCESS;
1691
1692 end loop;
1693 close c_get_loan_lines;
1694 end if;
1695
1696 i := i + 1;
1697 l_invoices_tbl(i).CUST_TRX_ID := null;
1698 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1699 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1700 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName(l_payoff_tbl2(k).PAYOFF_PURPOSE);
1701 l_invoices_tbl(i).REMAINING_AMOUNT := l_payoff_tbl2(k).unbilled_amount;
1702 l_invoices_tbl(i).DUE_DATE := p_cancellation_date;
1703 l_invoices_tbl(i).TRX_DATE := l_trx_date;
1704 l_invoices_tbl(i).GL_DATE := l_gl_date;
1705 l_invoices_tbl(i).PURPOSE_CODE := l_payoff_tbl2(k).PAYOFF_PURPOSE;
1706 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1707 l_invoices_tbl(i).PERCENT_TO_PROCESS := 100 - l_sum_percent;
1708 l_invoices_tbl(i).AMOUNT_TO_PROCESS := round((l_payoff_tbl2(k).unbilled_amount-l_sum_amount), l_precision);
1709 l_invoices_tbl(i).BILLED_FLAG := 'N';
1710 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adding ' || l_invoices_tbl(i).PURPOSE ||
1711 ' for ' || l_invoices_tbl(i).AMOUNT_TO_PROCESS);
1712
1713 end if;
1714 end loop;
1715
1716 -- get fees
1717 open c_fees(p_loan_id, l_phase, l_installment);
1718 loop
1719 fetch c_fees into
1720 l_fee_amount, l_fee_name, l_fee_installment;
1721 exit when c_fees%notfound;
1722
1723 i := i + 1;
1724 l_invoices_tbl(i).CUST_TRX_ID := null;
1725 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1726 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1727 l_invoices_tbl(i).DUE_DATE := p_cancellation_date;
1728 l_invoices_tbl(i).TRX_DATE := l_trx_date;
1729 l_invoices_tbl(i).GL_DATE := l_gl_date;
1730 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName('FEE');
1731 l_invoices_tbl(i).REMAINING_AMOUNT := l_fee_amount;
1732 l_invoices_tbl(i).PURPOSE_CODE := 'FEE';
1733 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', 'FEE') || ' - ' || l_fee_name;
1734 l_invoices_tbl(i).PERCENT_TO_PROCESS := 100;
1735 l_invoices_tbl(i).AMOUNT_TO_PROCESS := round(l_fee_amount, l_precision);
1736 l_invoices_tbl(i).BILLED_FLAG := 'N';
1737 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adding ' || l_invoices_tbl(i).PURPOSE ||
1738 ' for ' || l_invoices_tbl(i).AMOUNT_TO_PROCESS);
1739 end loop;
1740 close c_fees;
1741
1742 -- get billed unpaid invoices
1743 open unpaid_inv_cur(p_loan_id);
1744 LOOP
1745 FETCH unpaid_inv_cur INTO
1746 l_cust_trx_id
1747 ,l_payment_schedule_id
1748 ,l_installment
1749 ,l_invoice_number
1750 ,l_trans_type
1751 ,l_remaining_amt
1752 ,l_purpose_code
1753 ,l_purpose
1754 ,l_due_date
1755 ,l_trx_date
1756 ,l_gl_date;
1757
1758 EXIT WHEN unpaid_inv_cur%NOTFOUND;
1759
1760 i := i + 1;
1761 l_invoices_tbl(i).CUST_TRX_ID := l_cust_trx_id;
1762 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID:= l_payment_schedule_id;
1763 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1764 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1765 l_invoices_tbl(i).TRANSACTION_TYPE := l_trans_type;
1766 l_invoices_tbl(i).REMAINING_AMOUNT := l_remaining_amt;
1767 l_invoices_tbl(i).PURPOSE_CODE := l_purpose_code;
1768 l_invoices_tbl(i).PURPOSE := l_purpose;
1769 l_invoices_tbl(i).PERCENT_TO_PROCESS := 100;
1770 l_invoices_tbl(i).AMOUNT_TO_PROCESS := l_remaining_amt;
1771 l_invoices_tbl(i).BILLED_FLAG := 'Y';
1772 l_invoices_tbl(i).DUE_DATE := l_due_date;
1773 l_invoices_tbl(i).TRX_DATE := l_trx_date;
1774 l_invoices_tbl(i).GL_DATE := l_gl_date;
1775 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adding billed unpaid ' || l_invoices_tbl(i).PURPOSE || ' (Installment ' ||
1776 l_invoices_tbl(i).INSTALLMENT_NUMBER || ') for ' || l_invoices_tbl(i).REMAINING_AMOUNT);
1777
1778 END LOOP;
1779 close unpaid_inv_cur;
1780
1781 x_invoices_tbl := l_invoices_tbl;
1782 -- --------------------------------------------------------------------
1783 -- End of API body
1784 --
1785
1786 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1787
1788 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 ROLLBACK TO getInvoicesForLoanCancellation;
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1795 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1796 end;
1797
1798
1799
1800 procedure validateLoanCancelRequest(p_loan_id IN NUMBER
1801 ,p_cancellation_date IN DATE
1802 ,p_LOAN_CANCELLATION_ID IN NUMBER
1803 ,p_invoices_tbl IN LNS_PAYOFF_PUB.LOAN_CANCEL_DETAILS_TBL)
1804 is
1805
1806 l_api_name varchar2(50);
1807 l_return_status VARCHAR2(1);
1808 l_msg_count NUMBER;
1809 l_msg_data VARCHAR2(32767);
1810 l_prin_to_bill number;
1811 l_int_to_bill number;
1812 l_fee_to_bill number;
1813 l_prin_due number;
1814 l_int_due number;
1815 l_fee_due number;
1816 l_count number;
1817 l_loan_status varchar2(30);
1818 l_status varchar2(30);
1819 l_CANCELLATION_DATE date;
1820 l_unbilled_prin number;
1821 l_billed_prin number;
1822 l_unbilled_int number;
1823 l_billed_int number;
1824 l_unbilled_fee number;
1825 l_billed_fee number;
1826 l_payoff_unbilled_prin number;
1827 l_payoff_billed_prin number;
1828 l_payoff_unbilled_int number;
1829 l_payoff_billed_int number;
1830 l_payoff_unbilled_fee number;
1831 l_payoff_billed_fee number;
1832
1833 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
1834
1835 CURSOR pending_count_cur(P_LOAN_ID number) IS
1836 select count(1)
1837 from LNS_LOAN_CANCELLATIONS
1838 where loan_id = P_LOAN_ID and
1839 STATUS = 'PENDING';
1840
1841 CURSOR cancel_request_cur(P_LOAN_CANCELLATION_ID number) IS
1842 select STATUS, CANCELLATION_DATE
1843 from LNS_LOAN_CANCELLATIONS
1844 where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
1845
1846 CURSOR loan_details_cur(P_LOAN_ID number) IS
1847 select LOAN_STATUS
1848 from lns_loan_headers_all
1849 where loan_id = P_LOAN_ID;
1850
1851 begin
1852
1853 l_api_name := 'validateLoanCancelRequest';
1854 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1855
1856 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1857 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID = ' || P_LOAN_ID);
1858 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_cancellation_date = ' || p_cancellation_date);
1859 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_CANCELLATION_ID = ' || p_LOAN_CANCELLATION_ID);
1860
1861 -- verify input parameters
1862 if P_LOAN_ID is null then
1863 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1864 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_LOAN_ID');
1865 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
1866 FND_MSG_PUB.ADD;
1867 RAISE FND_API.G_EXC_ERROR;
1868 end if;
1869 if p_cancellation_date is null then
1870 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1871 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_cancellation_date');
1872 FND_MESSAGE.SET_TOKEN('VALUE', p_cancellation_date);
1873 FND_MSG_PUB.ADD;
1874 RAISE FND_API.G_EXC_ERROR;
1875 end if;
1876
1877 open loan_details_cur(P_LOAN_ID);
1878 fetch loan_details_cur into l_loan_status;
1879 close loan_details_cur;
1880
1881 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_status: ' || l_loan_status);
1882
1883 if (l_loan_status <> 'ACTIVE' and
1884 l_loan_status <> 'APPROVED' and
1885 l_loan_status <> 'DEFAULT' and
1886 l_loan_status <> 'DELINQUENT' and
1887 l_loan_status <> 'FUNDING_ERROR' and
1888 l_loan_status <> 'IN_FUNDING')
1889 then
1890
1891 FND_MESSAGE.SET_NAME('LNS', 'LNS_CANCEL_INVAL_LN_STATUS');
1892 FND_MSG_PUB.Add;
1893 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1894 RAISE FND_API.G_EXC_ERROR;
1895
1896 end if;
1897
1898 if p_LOAN_CANCELLATION_ID is null then
1899 open pending_count_cur(P_LOAN_ID);
1900 fetch pending_count_cur into l_count;
1901 close pending_count_cur;
1902
1903 if (l_count > 0) then
1904 FND_MESSAGE.SET_NAME('LNS', 'LNS_CANCEL_ALREADY_EXIST');
1905 FND_MSG_PUB.Add;
1906 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1907 RAISE FND_API.G_EXC_ERROR;
1908 end if;
1909 else
1910 open cancel_request_cur(p_LOAN_CANCELLATION_ID);
1911 fetch cancel_request_cur into l_status, l_CANCELLATION_DATE;
1912 close cancel_request_cur;
1913
1914 if (l_status <> 'PENDING') then
1915 FND_MESSAGE.SET_NAME('LNS', 'LNS_CANCEL_INVAL_STATUS');
1916 FND_MSG_PUB.Add;
1917 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1918 RAISE FND_API.G_EXC_ERROR;
1919 end if;
1920
1921 if trunc(p_cancellation_date) <> trunc(l_CANCELLATION_DATE) then
1922 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1923 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_cancellation_date');
1924 FND_MESSAGE.SET_TOKEN('VALUE', p_cancellation_date);
1925 FND_MSG_PUB.ADD;
1926 RAISE FND_API.G_EXC_ERROR;
1927 end if;
1928
1929 end if;
1930
1931 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_financials.calcLoanRemainingAmounts...');
1932 lns_financials.calcLoanRemainingAmounts(p_api_version => 1.0
1933 ,p_init_msg_list => FND_API.G_TRUE
1934 ,p_loan_id => p_loan_id
1935 ,p_date => p_cancellation_date
1936 ,p_reason => 'LOAN_CANCELLATION'
1937 ,x_payoff_tbl => l_payoff_tbl2
1938 ,x_return_status => l_return_status
1939 ,x_msg_count => l_msg_count
1940 ,x_msg_data => l_msg_data);
1941 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
1942 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1943 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1944 RAISE FND_API.G_EXC_ERROR;
1945 END IF;
1946 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'payoff tbl count = ' || l_payoff_tbl2.count);
1947
1948 for k in 1..l_payoff_tbl2.count loop
1949 if l_payoff_tbl2(k).PAYOFF_PURPOSE = 'PRIN' then
1950 l_payoff_unbilled_prin := l_payoff_tbl2(k).unbilled_amount;
1951 l_payoff_billed_prin := l_payoff_tbl2(k).BILLED_AMOUNT;
1952 elsif l_payoff_tbl2(k).PAYOFF_PURPOSE = 'INT' then
1953 l_payoff_unbilled_int := l_payoff_tbl2(k).unbilled_amount;
1954 l_payoff_billed_int := l_payoff_tbl2(k).BILLED_AMOUNT;
1955 elsif l_payoff_tbl2(k).PAYOFF_PURPOSE = 'FEE' then
1956 l_payoff_unbilled_fee := l_payoff_tbl2(k).unbilled_amount;
1957 l_payoff_billed_fee := l_payoff_tbl2(k).BILLED_AMOUNT;
1958 end if;
1959 end loop;
1960
1961 l_unbilled_prin := 0;
1962 l_billed_prin := 0;
1963 l_unbilled_int := 0;
1964 l_billed_int := 0;
1965 l_unbilled_fee := 0;
1966 l_billed_fee := 0;
1967 for i in 1..p_invoices_tbl.count loop
1968 if p_invoices_tbl(i).PURPOSE_CODE = 'PRIN' and p_invoices_tbl(i).BILLED_FLAG = 'N' then
1969 l_unbilled_prin := l_unbilled_prin + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1970 elsif p_invoices_tbl(i).PURPOSE_CODE = 'PRIN' and p_invoices_tbl(i).BILLED_FLAG = 'Y' then
1971 l_billed_prin := l_billed_prin + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1972 elsif p_invoices_tbl(i).PURPOSE_CODE = 'INT' and p_invoices_tbl(i).BILLED_FLAG = 'N' then
1973 l_unbilled_int := l_unbilled_int + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1974 elsif p_invoices_tbl(i).PURPOSE_CODE = 'INT' and p_invoices_tbl(i).BILLED_FLAG = 'Y' then
1975 l_billed_int := l_billed_int + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1976 elsif p_invoices_tbl(i).PURPOSE_CODE = 'FEE' and p_invoices_tbl(i).BILLED_FLAG = 'N' then
1977 l_unbilled_fee := l_unbilled_fee + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1978 elsif p_invoices_tbl(i).PURPOSE_CODE = 'FEE' and p_invoices_tbl(i).BILLED_FLAG = 'Y' then
1979 l_billed_fee := l_billed_fee + p_invoices_tbl(i).AMOUNT_TO_PROCESS;
1980 end if;
1981
1982 end loop;
1983
1984 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_prin = ' || l_payoff_unbilled_prin);
1985 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_prin = ' || l_unbilled_prin);
1986 if l_payoff_unbilled_prin <> l_unbilled_prin then
1987 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1988 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled principal amount');
1989 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_prin);
1990 FND_MSG_PUB.ADD;
1991 RAISE FND_API.G_EXC_ERROR;
1992 end if;
1993
1994 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_prin = ' || l_payoff_billed_prin);
1995 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_prin = ' || l_billed_prin);
1996 if l_payoff_billed_prin <> l_billed_prin then
1997 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1998 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid principal amount');
1999 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_prin);
2000 FND_MSG_PUB.ADD;
2001 RAISE FND_API.G_EXC_ERROR;
2002 end if;
2003
2004 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_int = ' || l_payoff_unbilled_int);
2005 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_int = ' || l_unbilled_int);
2006 if l_payoff_unbilled_int < l_unbilled_int then
2007 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2008 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled interest amount');
2009 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_int);
2010 FND_MSG_PUB.ADD;
2011 RAISE FND_API.G_EXC_ERROR;
2012 end if;
2013
2014 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_int = ' || l_payoff_billed_int);
2015 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_int = ' || l_billed_int);
2016 if l_payoff_billed_int <> l_billed_int then
2017 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2018 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid interest amount');
2019 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_int);
2020 FND_MSG_PUB.ADD;
2021 RAISE FND_API.G_EXC_ERROR;
2022 end if;
2023
2024 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_fee = ' || l_payoff_unbilled_fee);
2025 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_fee = ' || l_unbilled_fee);
2026 if l_payoff_unbilled_fee < l_unbilled_fee then
2027 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2028 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled fee amount');
2029 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_fee);
2030 FND_MSG_PUB.ADD;
2031 RAISE FND_API.G_EXC_ERROR;
2032 end if;
2033
2034 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_fee = ' || l_payoff_billed_fee);
2035 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_fee = ' || l_billed_fee);
2036 if l_payoff_billed_fee <> l_billed_fee then
2037 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2038 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid fee amount');
2039 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_fee);
2040 FND_MSG_PUB.ADD;
2041 RAISE FND_API.G_EXC_ERROR;
2042 end if;
2043
2044 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2045
2046 end;
2047
2048
2049
2050
2051 procedure validateLoanCancelRequest(P_LOAN_CANCELLATION_ID IN NUMBER
2052 ,P_ACTION IN VARCHAR2)
2053 is
2054
2055 l_api_name varchar2(50);
2056 l_return_status VARCHAR2(1);
2057 l_msg_count NUMBER;
2058 l_msg_data VARCHAR2(32767);
2059 l_prin_to_bill number;
2060 l_int_to_bill number;
2061 l_fee_to_bill number;
2062 l_prin_due number;
2063 l_int_due number;
2064 l_fee_due number;
2065 l_count number;
2066 l_loan_status varchar2(30);
2067 l_status varchar2(30);
2068 l_LOAN_ID number;
2069 l_cancellation_date date;
2070 l_PURPOSE_CODE VARCHAR2(30);
2071 l_billed_flag varchar2(1);
2072 l_AMOUNT_TO_PROCESS number;
2073 l_unbilled_prin number;
2074 l_billed_prin number;
2075 l_unbilled_int number;
2076 l_billed_int number;
2077 l_unbilled_fee number;
2078 l_billed_fee number;
2079 l_payoff_unbilled_prin number;
2080 l_payoff_billed_prin number;
2081 l_payoff_unbilled_int number;
2082 l_payoff_billed_int number;
2083 l_payoff_unbilled_fee number;
2084 l_payoff_billed_fee number;
2085
2086 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
2087
2088 CURSOR loan_details_cur(P_LOAN_ID number) IS
2089 select LOAN_STATUS
2090 from lns_loan_headers_all
2091 where loan_id = P_LOAN_ID;
2092
2093 CURSOR cancel_request_cur(P_LOAN_CANCELLATION_ID number) IS
2094 select LOAN_ID,
2095 STATUS,
2096 CANCELLATION_DATE
2097 from LNS_LOAN_CANCELLATIONS
2098 where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
2099
2100 CURSOR cancel_details_cur(P_LOAN_CANCELLATION_ID number) IS
2101 select PURPOSE_CODE
2102 ,BILLED_FLAG
2103 ,AMOUNT_TO_PROCESS
2104 from LNS_LOAN_CANCEL_DETAILS
2105 where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
2106
2107 begin
2108
2109 l_api_name := 'validateLoanCancelRequest';
2110 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2111
2112 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2113 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_CANCELLATION_ID = ' || P_LOAN_CANCELLATION_ID);
2114
2115 -- verify input parameters
2116 if P_LOAN_CANCELLATION_ID is null then
2117 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2118 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_LOAN_CANCELLATION_ID');
2119 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_CANCELLATION_ID);
2120 FND_MSG_PUB.ADD;
2121 RAISE FND_API.G_EXC_ERROR;
2122 end if;
2123
2124 open cancel_request_cur(P_LOAN_CANCELLATION_ID);
2125 fetch cancel_request_cur
2126 into l_LOAN_ID,
2127 l_STATUS,
2128 l_cancellation_date;
2129 close cancel_request_cur;
2130
2131 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_LOAN_ID: ' || l_LOAN_ID);
2132 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_STATUS: ' || l_STATUS);
2133 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_cancellation_date: ' || l_cancellation_date);
2134
2135 open loan_details_cur(l_LOAN_ID);
2136 fetch loan_details_cur into l_loan_status;
2137 close loan_details_cur;
2138
2139 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_status: ' || l_loan_status);
2140
2141 if (l_loan_status <> 'ACTIVE' and
2142 l_loan_status <> 'APPROVED' and
2143 l_loan_status <> 'DEFAULT' and
2144 l_loan_status <> 'DELINQUENT' and
2145 l_loan_status <> 'FUNDING_ERROR' and
2146 l_loan_status <> 'IN_FUNDING')
2147 then
2148
2149 FND_MESSAGE.SET_NAME('LNS', 'LNS_CANCEL_INVAL_LN_STATUS');
2150 FND_MSG_PUB.Add;
2151 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2152 RAISE FND_API.G_EXC_ERROR;
2153
2154 end if;
2155
2156 if (l_STATUS <> 'PENDING') then
2157
2158 FND_MESSAGE.SET_NAME('LNS', 'LNS_CANCEL_INVAL_STATUS');
2159 FND_MSG_PUB.Add;
2160 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2161 RAISE FND_API.G_EXC_ERROR;
2162
2163 end if;
2164
2165 if (P_ACTION = 'APPROVE') then
2166
2167 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_financials.calcLoanRemainingAmounts...');
2168 lns_financials.calcLoanRemainingAmounts(p_api_version => 1.0
2169 ,p_init_msg_list => FND_API.G_TRUE
2170 ,p_loan_id => l_LOAN_ID
2171 ,p_date => l_cancellation_date
2172 ,p_reason => 'LOAN_CANCELLATION'
2173 ,x_payoff_tbl => l_payoff_tbl2
2174 ,x_return_status => l_return_status
2175 ,x_msg_count => l_msg_count
2176 ,x_msg_data => l_msg_data);
2177 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2178 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2179 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2180 RAISE FND_API.G_EXC_ERROR;
2181 END IF;
2182 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'payoff tbl count = ' || l_payoff_tbl2.count);
2183
2184 for k in 1..l_payoff_tbl2.count loop
2185 if l_payoff_tbl2(k).PAYOFF_PURPOSE = 'PRIN' then
2186 l_payoff_unbilled_prin := l_payoff_tbl2(k).unbilled_amount;
2187 l_payoff_billed_prin := l_payoff_tbl2(k).BILLED_AMOUNT;
2188 elsif l_payoff_tbl2(k).PAYOFF_PURPOSE = 'INT' then
2189 l_payoff_unbilled_int := l_payoff_tbl2(k).unbilled_amount;
2190 l_payoff_billed_int := l_payoff_tbl2(k).BILLED_AMOUNT;
2191 elsif l_payoff_tbl2(k).PAYOFF_PURPOSE = 'FEE' then
2192 l_payoff_unbilled_fee := l_payoff_tbl2(k).unbilled_amount;
2193 l_payoff_billed_fee := l_payoff_tbl2(k).BILLED_AMOUNT;
2194 end if;
2195 end loop;
2196
2197 l_unbilled_prin := 0;
2198 l_billed_prin := 0;
2199 l_unbilled_int := 0;
2200 l_billed_int := 0;
2201 l_unbilled_fee := 0;
2202 l_billed_fee := 0;
2203 open cancel_details_cur(P_LOAN_CANCELLATION_ID);
2204 LOOP
2205
2206 fetch cancel_details_cur
2207 into l_PURPOSE_CODE
2208 ,l_billed_flag
2209 ,l_AMOUNT_TO_PROCESS;
2210 exit when cancel_details_cur%NOTFOUND;
2211
2212 if l_PURPOSE_CODE = 'PRIN' and l_billed_flag = 'N' then
2213 l_unbilled_prin := l_unbilled_prin + l_AMOUNT_TO_PROCESS;
2214 elsif l_PURPOSE_CODE = 'PRIN' and l_billed_flag = 'Y' then
2215 l_billed_prin := l_billed_prin + l_AMOUNT_TO_PROCESS;
2216 elsif l_PURPOSE_CODE = 'INT' and l_billed_flag = 'N' then
2217 l_unbilled_int := l_unbilled_int + l_AMOUNT_TO_PROCESS;
2218 elsif l_PURPOSE_CODE = 'INT' and l_billed_flag = 'Y' then
2219 l_billed_int := l_billed_int + l_AMOUNT_TO_PROCESS;
2220 elsif l_PURPOSE_CODE = 'FEE' and l_billed_flag = 'N' then
2221 l_unbilled_fee := l_unbilled_fee + l_AMOUNT_TO_PROCESS;
2222 elsif l_PURPOSE_CODE = 'FEE' and l_billed_flag = 'Y' then
2223 l_billed_fee := l_billed_fee + l_AMOUNT_TO_PROCESS;
2224 end if;
2225
2226 end loop;
2227 close cancel_details_cur;
2228
2229 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_prin = ' || l_payoff_unbilled_prin);
2230 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_prin = ' || l_unbilled_prin);
2231 if l_payoff_unbilled_prin <> l_unbilled_prin then
2232 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2233 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled principal amount');
2234 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_prin);
2235 FND_MSG_PUB.ADD;
2236 RAISE FND_API.G_EXC_ERROR;
2237 end if;
2238
2239 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_prin = ' || l_payoff_billed_prin);
2240 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_prin = ' || l_billed_prin);
2241 if l_payoff_billed_prin <> l_billed_prin then
2242 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2243 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid principal amount');
2244 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_prin);
2245 FND_MSG_PUB.ADD;
2246 RAISE FND_API.G_EXC_ERROR;
2247 end if;
2248
2249 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_int = ' || l_payoff_unbilled_int);
2250 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_int = ' || l_unbilled_int);
2251 if l_payoff_unbilled_int < l_unbilled_int then
2252 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2253 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled interest amount');
2254 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_int);
2255 FND_MSG_PUB.ADD;
2256 RAISE FND_API.G_EXC_ERROR;
2257 end if;
2258
2259 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_int = ' || l_payoff_billed_int);
2260 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_int = ' || l_billed_int);
2261 if l_payoff_billed_int <> l_billed_int then
2262 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2263 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid interest amount');
2264 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_int);
2265 FND_MSG_PUB.ADD;
2266 RAISE FND_API.G_EXC_ERROR;
2267 end if;
2268
2269 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_unbilled_fee = ' || l_payoff_unbilled_fee);
2270 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_unbilled_fee = ' || l_unbilled_fee);
2271 if l_payoff_unbilled_fee < l_unbilled_fee then
2272 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2273 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total unbilled fee amount');
2274 FND_MESSAGE.SET_TOKEN('VALUE', l_unbilled_fee);
2275 FND_MSG_PUB.ADD;
2276 RAISE FND_API.G_EXC_ERROR;
2277 end if;
2278
2279 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payoff_billed_fee = ' || l_payoff_billed_fee);
2280 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_billed_fee = ' || l_billed_fee);
2281 if l_payoff_billed_fee <> l_billed_fee then
2282 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2283 FND_MESSAGE.SET_TOKEN('PARAMETER', 'total billed unpaid fee amount');
2284 FND_MESSAGE.SET_TOKEN('VALUE', l_billed_fee);
2285 FND_MSG_PUB.ADD;
2286 RAISE FND_API.G_EXC_ERROR;
2287 end if;
2288
2289 end if;
2290 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2291
2292 end;
2293
2294
2295
2296
2297 procedure saveLoanCancelRequest(p_api_version IN NUMBER
2298 ,p_init_msg_list IN VARCHAR2
2299 ,P_COMMIT IN VARCHAR2
2300 ,P_VALIDATION_LEVEL IN NUMBER
2301 ,p_loan_id IN NUMBER
2302 ,p_description IN VARCHAR2
2303 ,p_cancellation_date IN DATE
2304 ,p_disable_billing IN VARCHAR2
2305 ,p_action IN VARCHAR2
2306 ,p_LOAN_CANCELLATION_ID IN OUT NOCOPY NUMBER
2307 ,p_cancel_details_tbl IN OUT NOCOPY LNS_PAYOFF_PUB.LOAN_CANCEL_DETAILS_TBL
2308 ,x_return_status OUT NOCOPY VARCHAR2
2309 ,x_msg_count OUT NOCOPY NUMBER
2310 ,x_msg_data OUT NOCOPY VARCHAR2)
2311 is
2312
2313 l_api_name varchar2(50);
2314 l_api_version_number number;
2315 l_return_status VARCHAR2(1);
2316 l_msg_count NUMBER;
2317 l_msg_data VARCHAR2(32767);
2318 l_LOAN_CANCEL_DETAIL_ID number;
2319 l_precision number;
2320 l_disable_billing varchar2(1);
2321 l_version_number number;
2322 l_description varchar2(30);
2323 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
2324
2325 CURSOR precision_cur(P_LOAN_ID number) IS
2326 select curr.precision
2327 from fnd_currencies curr,
2328 lns_loan_headers_all loan
2329 where loan.loan_id = P_LOAN_ID and
2330 curr.currency_code = loan.loan_currency;
2331
2332 CURSOR loan_version_cur(P_LOAN_ID number) IS
2333 select OBJECT_VERSION_NUMBER
2334 from LNS_LOAN_HEADERS
2335 where LOAN_ID = P_LOAN_ID;
2336
2337 begin
2338
2339 l_api_name := 'saveLoanCancelRequest';
2340 l_api_version_number := 1;
2341 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2342
2343 -- Standard Start of API savepoint
2344 SAVEPOINT saveLoanCancelRequest;
2345
2346 -- Standard call to check for call compatibility.
2347 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
2348 l_api_name, G_PKG_NAME)
2349 THEN
2350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2351 END IF;
2352
2353 -- Initialize message list IF p_init_msg_list is set to TRUE.
2354 IF FND_API.to_Boolean(p_init_msg_list) THEN
2355 FND_MSG_PUB.initialize;
2356 END IF;
2357
2358 -- Initialize API return status to SUCCESS
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
2360
2361 --
2362 -- Api body
2363 -- --------------------------------------------------------------------
2364
2365 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2366 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID = ' || P_LOAN_ID);
2367 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_cancellation_date = ' || p_cancellation_date);
2368 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disable_billing = ' || p_disable_billing);
2369 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_description = ' || p_description);
2370 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_CANCELLATION_ID = ' || p_LOAN_CANCELLATION_ID);
2371 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_action = ' || p_action);
2372
2373 if p_action = 'INSERT' then
2374 validateLoanCancelRequest(p_loan_id => P_LOAN_ID
2375 ,p_cancellation_date => p_cancellation_date
2376 ,p_LOAN_CANCELLATION_ID => null
2377 ,p_invoices_tbl => p_cancel_details_tbl);
2378 else
2379 validateLoanCancelRequest(p_loan_id => P_LOAN_ID
2380 ,p_cancellation_date => p_cancellation_date
2381 ,p_LOAN_CANCELLATION_ID => p_LOAN_CANCELLATION_ID
2382 ,p_invoices_tbl => p_cancel_details_tbl);
2383 end if;
2384
2385 open precision_cur(P_LOAN_ID);
2386 fetch precision_cur into l_precision;
2387 close precision_cur;
2388
2389 l_description := substr(p_description, 1, 30);
2390 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_description = ' || l_description);
2391
2392 if p_action = 'INSERT' then
2393 if p_LOAN_CANCELLATION_ID is null then
2394 select LNS_LOAN_CANCELLATIONS_S.NEXTVAL into p_LOAN_CANCELLATION_ID from dual;
2395 end if;
2396
2397 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting into LNS_LOAN_CANCELLATIONS...');
2398 insert into LNS_LOAN_CANCELLATIONS
2399 (LOAN_CANCELLATION_ID
2400 ,LOAN_ID
2401 ,DESCRIPTION
2402 ,STATUS
2403 ,CANCELLATION_DATE
2404 ,APPR_REJECT_DATE
2405 ,APPR_REJECT_BY
2406 ,OBJECT_VERSION_NUMBER
2407 ,CREATION_DATE
2408 ,CREATED_BY
2409 ,LAST_UPDATE_DATE
2410 ,LAST_UPDATED_BY
2411 ,LAST_UPDATE_LOGIN)
2412 VALUES
2413 (p_LOAN_CANCELLATION_ID
2414 ,P_LOAN_ID
2415 ,l_description
2416 ,'PENDING'
2417 ,p_cancellation_date
2418 ,null
2419 ,null
2420 ,1
2421 ,sysdate
2422 ,lns_utility_pub.created_by
2423 ,sysdate
2424 ,lns_utility_pub.last_updated_by
2425 ,lns_utility_pub.LAST_UPDATE_LOGIN);
2426 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2427 else
2428 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating into LNS_LOAN_CANCELLATIONS...');
2429 update LNS_LOAN_CANCELLATIONS set
2430 DESCRIPTION = l_description
2431 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2432 ,LAST_UPDATE_DATE = sysdate
2433 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
2434 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
2435 where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
2436 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2437 end if;
2438
2439 -- save all cancellation details in table
2440 for i in 1..p_cancel_details_tbl.count loop
2441
2442 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoice #' || i);
2443 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_CANCEL_DETAIL_ID = ' || p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID);
2444 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INVOICE_NUMBER = ' || p_cancel_details_tbl(i).INVOICE_NUMBER);
2445 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT_NUMBER = ' || p_cancel_details_tbl(i).INSTALLMENT_NUMBER);
2446 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'TRANSACTION_TYPE = ' || p_cancel_details_tbl(i).TRANSACTION_TYPE);
2447 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PURPOSE_CODE = ' || p_cancel_details_tbl(i).PURPOSE_CODE);
2448 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PURPOSE = ' || p_cancel_details_tbl(i).PURPOSE);
2449 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'REMAINING_AMOUNT = ' || p_cancel_details_tbl(i).REMAINING_AMOUNT);
2450 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERCENT_TO_PROCESS = ' || p_cancel_details_tbl(i).PERCENT_TO_PROCESS);
2451 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMOUNT_TO_PROCESS = ' || p_cancel_details_tbl(i).AMOUNT_TO_PROCESS);
2452 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE_DATE = ' || p_cancel_details_tbl(i).DUE_DATE);
2453 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'TRX_DATE = ' || p_cancel_details_tbl(i).TRX_DATE);
2454 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'GL_DATE = ' || p_cancel_details_tbl(i).GL_DATE);
2455 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_LINE_ID = ' || p_cancel_details_tbl(i).LOAN_LINE_ID);
2456 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYMENT_SCHEDULE_ID = ' || p_cancel_details_tbl(i).PAYMENT_SCHEDULE_ID);
2457 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUST_TRX_ID = ' || p_cancel_details_tbl(i).CUST_TRX_ID);
2458 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'BILLED_FLAG = ' || p_cancel_details_tbl(i).BILLED_FLAG);
2459 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ACTION = ' || p_cancel_details_tbl(i).ACTION);
2460
2461 if p_action = 'INSERT' then
2462 if p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID is null then
2463 select LNS_LOAN_CANCEL_DETAILS_S.NEXTVAL into l_LOAN_CANCEL_DETAIL_ID from dual;
2464 p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID := l_LOAN_CANCEL_DETAIL_ID;
2465 end if;
2466
2467 p_cancel_details_tbl(i).REMAINING_AMOUNT := round(p_cancel_details_tbl(i).REMAINING_AMOUNT, l_precision);
2468 p_cancel_details_tbl(i).AMOUNT_TO_PROCESS := round(p_cancel_details_tbl(i).AMOUNT_TO_PROCESS, l_precision);
2469 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting into LNS_LOAN_CANCEL_DETAILS...');
2470 insert into LNS_LOAN_CANCEL_DETAILS
2471 (LOAN_CANCEL_DETAIL_ID
2472 ,LOAN_CANCELLATION_ID
2473 ,LOAN_ID
2474 ,INVOICE_NUMBER
2475 ,INSTALLMENT_NUMBER
2476 ,TRANSACTION_TYPE
2477 ,PURPOSE_CODE
2478 ,PURPOSE_DESC
2479 ,AMOUNT_DUE
2480 ,PERCENT_TO_PROCESS
2481 ,AMOUNT_TO_PROCESS
2482 ,DUE_DATE
2483 ,TRX_DATE
2484 ,GL_DATE
2485 ,BILLED_FLAG
2486 ,ACTION
2487 ,LOAN_LINE_ID
2488 ,PAYMENT_SCHEDULE_ID
2489 ,CUST_TRX_ID
2490 ,ADJUSTMENT_NUMBER
2491 ,ADJUSTMENT_ID
2492 ,OBJECT_VERSION_NUMBER
2493 ,CREATION_DATE
2494 ,CREATED_BY
2495 ,LAST_UPDATE_DATE
2496 ,LAST_UPDATED_BY
2497 ,LAST_UPDATE_LOGIN)
2498 VALUES
2499 (p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID
2500 ,p_LOAN_CANCELLATION_ID
2501 ,P_LOAN_ID
2502 ,p_cancel_details_tbl(i).INVOICE_NUMBER
2503 ,p_cancel_details_tbl(i).INSTALLMENT_NUMBER
2504 ,p_cancel_details_tbl(i).TRANSACTION_TYPE
2505 ,p_cancel_details_tbl(i).PURPOSE_CODE
2506 ,p_cancel_details_tbl(i).PURPOSE
2507 ,p_cancel_details_tbl(i).REMAINING_AMOUNT
2508 ,p_cancel_details_tbl(i).PERCENT_TO_PROCESS
2509 ,p_cancel_details_tbl(i).AMOUNT_TO_PROCESS
2510 ,p_cancel_details_tbl(i).DUE_DATE
2511 ,p_cancel_details_tbl(i).TRX_DATE
2512 ,p_cancel_details_tbl(i).GL_DATE
2513 ,p_cancel_details_tbl(i).BILLED_FLAG
2514 ,p_cancel_details_tbl(i).ACTION
2515 ,p_cancel_details_tbl(i).LOAN_LINE_ID
2516 ,p_cancel_details_tbl(i).PAYMENT_SCHEDULE_ID
2517 ,p_cancel_details_tbl(i).CUST_TRX_ID
2518 ,null
2519 ,null
2520 ,1
2521 ,sysdate
2522 ,lns_utility_pub.created_by
2523 ,sysdate
2524 ,lns_utility_pub.last_updated_by
2525 ,lns_utility_pub.LAST_UPDATE_LOGIN);
2526 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2527 else
2528 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating into LNS_LOAN_CANCEL_DETAILS...');
2529 update LNS_LOAN_CANCEL_DETAILS set
2530 PERCENT_TO_PROCESS = p_cancel_details_tbl(i).PERCENT_TO_PROCESS
2531 ,AMOUNT_TO_PROCESS = p_cancel_details_tbl(i).AMOUNT_TO_PROCESS
2532 ,ACTION = p_cancel_details_tbl(i).ACTION
2533 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2534 ,LAST_UPDATE_DATE = sysdate
2535 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
2536 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
2537 where LOAN_CANCEL_DETAIL_ID = p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID;
2538 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2539 end if;
2540
2541 end loop;
2542
2543 l_disable_billing := p_disable_billing;
2544 if l_disable_billing is null then
2545 l_disable_billing := 'N';
2546 end if;
2547
2548 if p_disable_billing = 'Y' then
2549 open loan_version_cur(P_LOAN_ID);
2550 fetch loan_version_cur into l_version_number;
2551 close loan_version_cur;
2552
2553 l_loan_header_rec.loan_id := P_LOAN_ID;
2554 l_loan_header_rec.DISABLE_BILLING_FLAG := 'Y';
2555
2556 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating loan header...');
2557 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
2558 P_LOAN_HEADER_REC => l_loan_header_rec,
2559 P_INIT_MSG_LIST => FND_API.G_FALSE,
2560 X_RETURN_STATUS => l_return_status,
2561 X_MSG_COUNT => l_msg_count,
2562 X_MSG_DATA => l_msg_data);
2563
2564 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2565
2566 IF l_return_status = 'S' THEN
2567 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
2568 ELSE
2569 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
2570 FND_MSG_PUB.Add;
2571 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2572 RAISE FND_API.G_EXC_ERROR;
2573 END IF;
2574 end if;
2575
2576 -- --------------------------------------------------------------------
2577 -- End of API body
2578 --
2579
2580 if P_COMMIT = FND_API.G_TRUE then
2581 COMMIT WORK;
2582 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
2583 end if;
2584
2585 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2586
2587 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2588
2589 EXCEPTION
2590 WHEN OTHERS THEN
2591 ROLLBACK TO saveLoanCancelRequest;
2592 x_return_status := FND_API.G_RET_STS_ERROR;
2593 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2594 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2595 end;
2596
2597
2598
2599
2600 procedure approveLoanCancelRequest(p_api_version IN NUMBER
2601 ,p_init_msg_list IN VARCHAR2
2602 ,P_COMMIT IN VARCHAR2
2603 ,P_VALIDATION_LEVEL IN NUMBER
2604 ,p_LOAN_CANCELLATION_ID IN NUMBER
2605 ,x_return_status OUT NOCOPY VARCHAR2
2606 ,x_msg_count OUT NOCOPY NUMBER
2607 ,x_msg_data OUT NOCOPY VARCHAR2)
2608 is
2609
2610 l_api_name varchar2(50);
2611 l_api_version_number number;
2612 l_return_status VARCHAR2(1);
2613 l_msg_count NUMBER;
2614 l_msg_data VARCHAR2(32767);
2615
2616 i number;
2617 y number;
2618 l_loan_number varchar2(60);
2619 l_org_id number;
2620 l_adj_number varchar2(20);
2621 l_adj_id number;
2622 l_receivables_trx_id number;
2623 l_version_number number;
2624
2625 l_LOAN_CANCEL_DETAIL_ID number;
2626 l_PURPOSE_CODE VARCHAR2(30);
2627 l_PURPOSE VARCHAR2(80);
2628 l_PERCENT_TO_PROCESS number;
2629 l_AMOUNT_TO_PROCESS number;
2630 l_DUE_DATE date;
2631 l_loan_id number;
2632 l_loan_class varchar2(30);
2633 l_CUST_TRX_ID number;
2634 l_PAYMENT_SCHEDULE_ID number;
2635 l_LOAN_LINE_ID number;
2636 l_BILLED_FLAG varchar2(1);
2637 l_ACTION varchar2(30);
2638 l_disable_billing varchar2(1);
2639 l_apply_date date;
2640 l_gl_date date;
2641 l_request_id number;
2642 l_due_date1 date;
2643 l_TRX_DATE date;
2644
2645 l_bill_headers_tbl LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL; -- for invoice creation
2646 l_bill_lines_tbl LNS_BILLING_BATCH_PUB.BILL_LINES_TBL; -- for invoice creation
2647 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
2648 l_invoices_tbl LNS_PAYOFF_PUB.LOAN_CANCEL_DETAILS_TBL;
2649 l_reverse_tbl LNS_BILLING_BATCH_PUB.REVERSE_TBL;
2650 l_cm_line_tbl AR_CREDIT_MEMO_API_PUB.Cm_Line_Tbl_Type_Cover%type;
2651
2652 -- query loan info
2653 CURSOR loan_info_cur(P_LOAN_CANCELLATION_ID number) IS
2654 select loan.loan_id,
2655 loan.loan_number,
2656 loan.org_id,
2657 sys.CANCELLATION_REC_TRX_ID,
2658 loan.LOAN_CLASS_CODE,
2659 nvl(loan.DISABLE_BILLING_FLAG, 'N')
2660 from lns_loan_headers loan,
2661 LNS_SYSTEM_OPTIONS sys,
2662 LNS_LOAN_CANCELLATIONS cncl
2663 where cncl.LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID and
2664 cncl.LOAN_ID = loan.LOAN_ID and
2665 loan.org_id = sys.org_id;
2666
2667 CURSOR cancel_details_cur(P_LOAN_CANCELLATION_ID number) IS
2668 select
2669 LOAN_CANCEL_DETAIL_ID
2670 ,PURPOSE_CODE
2671 ,PURPOSE_DESC
2672 ,PERCENT_TO_PROCESS
2673 ,AMOUNT_TO_PROCESS
2674 ,DUE_DATE
2675 ,TRX_DATE
2676 ,GL_DATE
2677 ,BILLED_FLAG
2678 ,ACTION
2679 ,LOAN_LINE_ID
2680 ,PAYMENT_SCHEDULE_ID
2681 ,CUST_TRX_ID
2682 from LNS_LOAN_CANCEL_DETAILS
2683 where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
2684
2685 CURSOR loan_version_cur(P_LOAN_ID number) IS
2686 select OBJECT_VERSION_NUMBER
2687 from LNS_LOAN_HEADERS
2688 where LOAN_ID = P_LOAN_ID;
2689
2690 CURSOR new_invoices_cur(P_LOAN_ID number) IS
2691 select ps.customer_trx_id
2692 ,ps.payment_schedule_id
2693 ,decode(ps.customer_trx_id, am.principal_trx_Id, 'PRIN', am.interest_trx_id, 'INT', am.fee_trx_id, 'FEE')
2694 ,ps.trx_date
2695 ,ps.gl_date
2696 from lns_loan_headers loan
2697 ,lns_amortization_scheds am
2698 ,ar_payment_schedules ps
2699 where loan.loan_id = P_LOAN_ID and
2700 loan.LAST_AMORTIZATION_ID = am.AMORTIZATION_SCHEDULE_ID and
2701 ps.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id);
2702
2703 begin
2704
2705 l_api_name := 'approveLoanCancelRequest';
2706 l_api_version_number := 1;
2707 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2708
2709 -- Standard Start of API savepoint
2710 SAVEPOINT approveLoanCancelRequest;
2711
2712 -- Standard call to check for call compatibility.
2713 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
2714 l_api_name, G_PKG_NAME)
2715 THEN
2716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2717 END IF;
2718
2719 -- Initialize message list IF p_init_msg_list is set to TRUE.
2720 IF FND_API.to_Boolean(p_init_msg_list) THEN
2721 FND_MSG_PUB.initialize;
2722 END IF;
2723
2724 -- Initialize API return status to SUCCESS
2725 x_return_status := FND_API.G_RET_STS_SUCCESS;
2726
2727 --
2728 -- Api body
2729 -- --------------------------------------------------------------------
2730
2731 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2732 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_CANCELLATION_ID = ' || p_LOAN_CANCELLATION_ID);
2733
2734 validateLoanCancelRequest(P_LOAN_CANCELLATION_ID => p_LOAN_CANCELLATION_ID
2735 ,p_action => 'APPROVE');
2736
2737 -- query loan version
2738 open loan_info_cur(p_LOAN_CANCELLATION_ID);
2739 fetch loan_info_cur into l_loan_id,
2740 l_loan_number,
2741 l_org_id,
2742 l_receivables_trx_id,
2743 l_loan_class,
2744 l_disable_billing;
2745 close loan_info_cur;
2746
2747 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_id = ' || l_loan_id);
2748 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_number = ' || l_loan_number);
2749 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_version_number = ' || l_version_number);
2750 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_org_id = ' || l_org_id);
2751 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_class = ' || l_loan_class);
2752 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_receivables_trx_id = ' || l_receivables_trx_id);
2753
2754 if l_receivables_trx_id is null then
2755 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Receivables activity name is not set in the system option.');
2756 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_REC_TRX_IN_SYS_OPT');
2757 FND_MSG_PUB.Add;
2758 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2759 RAISE FND_API.G_EXC_ERROR;
2760 end if;
2761
2762 if l_loan_class = 'DIRECT' then
2763 -- cancel all remaining active disbursements
2764 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB...');
2765 LNS_FUNDING_PUB.APPROVE_CANCEL_REM_DISB(
2766 P_API_VERSION => 1.0,
2767 P_INIT_MSG_LIST => FND_API.G_FALSE,
2768 P_COMMIT => FND_API.G_FALSE,
2769 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2770 P_LOAN_ID => l_loan_id,
2771 X_RETURN_STATUS => l_return_status,
2772 X_MSG_COUNT => l_msg_count,
2773 X_MSG_DATA => l_msg_data);
2774 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2775
2776 IF l_return_status <> 'S' THEN
2777 RAISE FND_API.G_EXC_ERROR;
2778 END IF;
2779 else
2780 -- cancel all remaining pending orig receivables
2781 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Rejecting all remaining pending orig receivables...');
2782 update LNS_LOAN_LINES set
2783 STATUS = 'REJECTED'
2784 ,LAST_UPDATE_DATE = sysdate
2785 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
2786 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
2787 ,APPR_REJECT_DATE = sysdate
2788 ,APPR_REJECT_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
2789 where LOAN_ID = l_loan_id and
2790 (STATUS is null or STATUS = 'PENDING');
2791 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2792 end if;
2793
2794 i := 0;
2795 open cancel_details_cur(P_LOAN_CANCELLATION_ID);
2796 LOOP
2797
2798 fetch cancel_details_cur
2799 into l_LOAN_CANCEL_DETAIL_ID
2800 ,l_PURPOSE_CODE
2801 ,l_PURPOSE
2802 ,l_PERCENT_TO_PROCESS
2803 ,l_AMOUNT_TO_PROCESS
2804 ,l_DUE_DATE
2805 ,l_TRX_DATE
2806 ,l_gl_date
2807 ,l_BILLED_FLAG
2808 ,l_ACTION
2809 ,l_LOAN_LINE_ID
2810 ,l_PAYMENT_SCHEDULE_ID
2811 ,l_CUST_TRX_ID;
2812 exit when cancel_details_cur%NOTFOUND;
2813
2814 i := i + 1;
2815 l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID := l_LOAN_CANCEL_DETAIL_ID;
2816 l_invoices_tbl(i).PURPOSE_CODE := l_PURPOSE_CODE;
2817 l_invoices_tbl(i).PURPOSE := l_PURPOSE;
2818 l_invoices_tbl(i).PERCENT_TO_PROCESS := l_PERCENT_TO_PROCESS;
2819 l_invoices_tbl(i).AMOUNT_TO_PROCESS := l_AMOUNT_TO_PROCESS;
2820 l_invoices_tbl(i).DUE_DATE := l_DUE_DATE;
2821 l_invoices_tbl(i).TRX_DATE := l_TRX_DATE;
2822 l_invoices_tbl(i).GL_DATE := l_gl_date;
2823 l_invoices_tbl(i).BILLED_FLAG := l_BILLED_FLAG;
2824 l_invoices_tbl(i).ACTION := l_ACTION;
2825 l_invoices_tbl(i).CUST_TRX_ID := l_CUST_TRX_ID;
2826 l_invoices_tbl(i).LOAN_LINE_ID := l_LOAN_LINE_ID;
2827 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID := l_PAYMENT_SCHEDULE_ID;
2828 l_invoices_tbl(i).CUST_TRX_ID := l_CUST_TRX_ID;
2829
2830 END LOOP;
2831 close cancel_details_cur;
2832
2833 y := 0;
2834 for i in 1..l_invoices_tbl.count loop
2835
2836 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Invoice #' || i);
2837 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_CANCEL_DETAIL_ID = ' || l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID);
2838 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PURPOSE_CODE = ' || l_invoices_tbl(i).PURPOSE_CODE);
2839 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PURPOSE = ' || l_invoices_tbl(i).PURPOSE);
2840 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERCENT_TO_PROCESS = ' || l_invoices_tbl(i).PERCENT_TO_PROCESS);
2841 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMOUNT_TO_PROCESS = ' || l_invoices_tbl(i).AMOUNT_TO_PROCESS);
2842 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE_DATE = ' || l_invoices_tbl(i).DUE_DATE);
2843 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'TRX_DATE = ' || l_invoices_tbl(i).TRX_DATE);
2844 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'GL_DATE = ' || l_invoices_tbl(i).GL_DATE);
2845 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'BILLED_FLAG = ' || l_invoices_tbl(i).BILLED_FLAG);
2846 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ACTION = ' || l_invoices_tbl(i).ACTION);
2847 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUST_TRX_ID = ' || l_invoices_tbl(i).CUST_TRX_ID);
2848 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_LINE_ID = ' || l_invoices_tbl(i).LOAN_LINE_ID);
2849 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYMENT_SCHEDULE_ID = ' || l_invoices_tbl(i).PAYMENT_SCHEDULE_ID);
2850 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUST_TRX_ID = ' || l_invoices_tbl(i).CUST_TRX_ID);
2851
2852 if l_invoices_tbl(i).AMOUNT_TO_PROCESS > 0 and
2853 ((l_invoices_tbl(i).PURPOSE_CODE = 'INT' and l_invoices_tbl(i).BILLED_FLAG = 'N') or
2854 (l_invoices_tbl(i).PURPOSE_CODE = 'FEE' and l_invoices_tbl(i).BILLED_FLAG = 'N') or
2855 (l_invoices_tbl(i).PURPOSE_CODE = 'PRIN' and l_invoices_tbl(i).LOAN_LINE_ID is null and l_invoices_tbl(i).BILLED_FLAG = 'N'))
2856 then
2857 y := y + 1;
2858 l_bill_lines_tbl(y).LINE_ID := y;
2859 l_bill_lines_tbl(y).HEADER_ID := 101;
2860 l_bill_lines_tbl(y).LINE_AMOUNT := l_invoices_tbl(i).AMOUNT_TO_PROCESS;
2861 l_bill_lines_tbl(y).LINE_TYPE := l_invoices_tbl(i).PURPOSE_CODE;
2862 l_bill_lines_tbl(y).LINE_DESC := l_invoices_tbl(i).PURPOSE;
2863 l_due_date1 := l_invoices_tbl(i).DUE_DATE;
2864
2865 elsif l_invoices_tbl(i).AMOUNT_TO_PROCESS > 0 and
2866 l_invoices_tbl(i).PURPOSE_CODE = 'PRIN' and
2867 l_invoices_tbl(i).LOAN_LINE_ID is not null
2868 then
2869
2870 -- checking for system options values required for adjustment
2871 if l_receivables_trx_id is null then
2872 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Receivables activity name is not set in the system option.');
2873 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_REC_TRX_IN_SYS_OPT');
2874 FND_MSG_PUB.Add;
2875 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2876 RAISE FND_API.G_EXC_ERROR;
2877 end if;
2878
2879 -- do adjustment
2880 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_BILLING_BATCH_PUB.CREATE_AR_ADJ...');
2881 LNS_BILLING_BATCH_PUB.CREATE_AR_ADJ(P_TYPE => 'INVOICE',
2882 P_PAYMENT_SCHEDULE_ID => l_invoices_tbl(i).PAYMENT_SCHEDULE_ID,
2883 P_RECEIVABLES_TRX_ID => l_receivables_trx_id,
2884 P_AMOUNT => l_invoices_tbl(i).AMOUNT_TO_PROCESS,
2885 P_APPLY_DATE => l_invoices_tbl(i).DUE_DATE,
2886 P_GL_DATE => l_invoices_tbl(i).GL_DATE,
2887 P_CUSTOMER_TRX_LINE_ID => null,
2888 P_CODE_COMBINATION_ID => null,
2889 P_USSGL_TRX_CODE => null,
2890 P_REASON_CODE => null,
2891 P_COMMENTS => 'This adjustment is part of cancellation of loan ' || l_loan_number,
2892 X_ADJ_ID => l_adj_id,
2893 X_ADJ_NUMBER => l_adj_number,
2894 P_ORG_ID => l_org_id);
2895
2896 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'l_adj_number = ' || l_adj_number || ' (l_adj_id = ' || l_adj_id || ')');
2897
2898 if l_adj_id is null and l_adj_number is null then
2899 RAISE FND_API.G_EXC_ERROR;
2900 end if;
2901
2902 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCEL_DETAILS...');
2903 update LNS_LOAN_CANCEL_DETAILS set
2904 ADJUSTMENT_NUMBER = l_adj_number
2905 ,ADJUSTMENT_ID = l_adj_id
2906 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2907 ,LAST_UPDATE_DATE = sysdate
2908 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
2909 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
2910 where LOAN_CANCEL_DETAIL_ID = l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID;
2911 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
2912
2913 end if;
2914
2915 end loop;
2916
2917 if l_bill_lines_tbl.count > 0 then
2918
2919 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Building Invoice header...');
2920 l_bill_headers_tbl(1).HEADER_ID := 101;
2921 l_bill_headers_tbl(1).LOAN_ID := l_loan_id;
2922 l_bill_headers_tbl(1).ASSOC_PAYMENT_NUM := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(l_loan_id);
2923 l_bill_headers_tbl(1).DUE_DATE := l_due_date1;
2924
2925 if l_disable_billing = 'Y' then
2926 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Re-enabling billing...');
2927 -- re-enable billing if need to
2928 open loan_version_cur(l_loan_id);
2929 fetch loan_version_cur into l_version_number;
2930 close loan_version_cur;
2931
2932 l_loan_header_rec.loan_id := l_loan_id;
2933 l_loan_header_rec.DISABLE_BILLING_FLAG := 'N';
2934
2935 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
2936 P_LOAN_HEADER_REC => l_loan_header_rec,
2937 P_INIT_MSG_LIST => FND_API.G_FALSE,
2938 X_RETURN_STATUS => l_return_status,
2939 X_MSG_COUNT => l_msg_count,
2940 X_MSG_DATA => l_msg_data);
2941
2942 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2943
2944 IF l_return_status = 'S' THEN
2945 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Billing is enabled');
2946 ELSE
2947 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
2948 FND_MSG_PUB.Add;
2949 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2950 RAISE FND_API.G_EXC_ERROR;
2951 END IF;
2952 end if;
2953
2954 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_billing_batch_pub.create_offcycle_bills...');
2955 lns_billing_batch_pub.create_offcycle_bills(p_api_version => 1.0
2956 ,p_init_msg_list => FND_API.G_TRUE
2957 ,p_commit => FND_API.G_FALSE
2958 ,p_validation_level => 100
2959 ,p_bill_headers_tbl => l_bill_headers_tbl
2960 ,p_bill_lines_tbl => l_bill_lines_tbl
2961 ,x_return_status => l_return_status
2962 ,x_msg_count => l_msg_count
2963 ,x_msg_data => l_msg_data);
2964
2965 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2966 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2967 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_CREATION_ERROR');
2968 FND_MSG_PUB.Add;
2969 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2970 RAISE FND_API.G_EXC_ERROR;
2971 END IF;
2972
2973 -- getting ids for the last billed invoices
2974 open new_invoices_cur(l_loan_id);
2975 LOOP
2976
2977 fetch new_invoices_cur
2978 into l_CUST_TRX_ID
2979 ,l_PAYMENT_SCHEDULE_ID
2980 ,l_PURPOSE_CODE
2981 ,l_trx_date
2982 ,l_gl_date;
2983 exit when new_invoices_cur%NOTFOUND;
2984
2985 for i in 1..l_invoices_tbl.count loop
2986 if l_invoices_tbl(i).PURPOSE_CODE = l_PURPOSE_CODE and
2987 l_invoices_tbl(i).BILLED_FLAG = 'N' and
2988 l_invoices_tbl(i).LOAN_LINE_ID is null
2989 then
2990 l_invoices_tbl(i).CUST_TRX_ID := l_CUST_TRX_ID;
2991 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID := l_PAYMENT_SCHEDULE_ID;
2992 l_invoices_tbl(i).TRX_DATE := l_trx_date;
2993 l_invoices_tbl(i).GL_DATE := l_gl_date;
2994 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Setting for newly billed ' || l_invoices_tbl(i).PURPOSE_CODE);
2995 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUST_TRX_ID = ' || l_invoices_tbl(i).CUST_TRX_ID);
2996 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYMENT_SCHEDULE_ID = ' || l_invoices_tbl(i).PAYMENT_SCHEDULE_ID);
2997 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'TRX_DATE = ' || l_invoices_tbl(i).TRX_DATE);
2998 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'GL_DATE = ' || l_invoices_tbl(i).GL_DATE);
2999
3000 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCEL_DETAILS...');
3001 update LNS_LOAN_CANCEL_DETAILS set
3002 CUST_TRX_ID = l_invoices_tbl(i).CUST_TRX_ID
3003 ,PAYMENT_SCHEDULE_ID = l_invoices_tbl(i).PAYMENT_SCHEDULE_ID
3004 ,TRX_DATE = l_invoices_tbl(i).TRX_DATE
3005 ,GL_DATE = l_invoices_tbl(i).GL_DATE
3006 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3007 ,LAST_UPDATE_DATE = sysdate
3008 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
3009 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
3010 where LOAN_CANCEL_DETAIL_ID = l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID;
3011 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
3012
3013 exit;
3014 end if;
3015 end loop;
3016
3017 END LOOP;
3018 close new_invoices_cur;
3019
3020 end if;
3021
3022 -- loop thru all invoices (old and new) and credit/adjust them as needed
3023 for i in 1..l_invoices_tbl.count loop
3024
3025 if l_invoices_tbl(i).LOAN_LINE_ID is null and
3026 l_invoices_tbl(i).ACTION is not null and
3027 l_invoices_tbl(i).ACTION = 'ADJUST'
3028 then
3029
3030 l_adj_id := null;
3031 l_adj_number := null;
3032
3033 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adjusting down ' || l_invoices_tbl(i).CUST_TRX_ID || '...');
3034 LNS_BILLING_BATCH_PUB.CREATE_AR_ADJ(P_TYPE => 'INVOICE',
3035 P_PAYMENT_SCHEDULE_ID => l_invoices_tbl(i).PAYMENT_SCHEDULE_ID,
3036 P_RECEIVABLES_TRX_ID => l_receivables_trx_id,
3037 P_AMOUNT => -l_invoices_tbl(i).AMOUNT_TO_PROCESS,
3038 P_APPLY_DATE => l_invoices_tbl(i).DUE_DATE,
3039 P_GL_DATE => l_invoices_tbl(i).GL_DATE,
3040 P_CUSTOMER_TRX_LINE_ID => null,
3041 P_CODE_COMBINATION_ID => null,
3042 P_USSGL_TRX_CODE => null,
3043 P_REASON_CODE => null,
3044 P_COMMENTS => 'This adjustment is part of cancellation of loan ' || l_loan_number,
3045 X_ADJ_ID => l_adj_id,
3046 X_ADJ_NUMBER => l_adj_number,
3047 P_ORG_ID => l_org_id);
3048 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'l_adj_number = ' || l_adj_number || ' (l_adj_id = ' || l_adj_id || ')');
3049
3050 if l_adj_id is null and l_adj_number is null then
3051 RAISE FND_API.G_EXC_ERROR;
3052 end if;
3053
3054 elsif l_invoices_tbl(i).LOAN_LINE_ID is null and
3055 l_invoices_tbl(i).ACTION is not null and
3056 l_invoices_tbl(i).ACTION = 'CREDIT'
3057 then
3058
3059 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Crediting ' || l_invoices_tbl(i).CUST_TRX_ID || '...');
3060 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_CUSTOMER_TRX_ID = ' || l_invoices_tbl(i).CUST_TRX_ID);
3061 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_line_amount = ' || -l_invoices_tbl(i).AMOUNT_TO_PROCESS);
3062 AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST(
3063 P_API_VERSION => 1.0,
3064 P_INIT_MSG_LIST => FND_API.G_TRUE,
3065 P_COMMIT => FND_API.G_FALSE,
3066 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3067 P_CUSTOMER_TRX_ID => l_invoices_tbl(i).CUST_TRX_ID,
3068 P_LINE_CREDIT_FLAG => 'N',
3069 p_line_amount => -l_invoices_tbl(i).AMOUNT_TO_PROCESS,
3070 P_CM_REASON_CODE => 'CANCELLATION',
3071 p_cm_line_tbl => l_cm_line_tbl,
3072 P_SKIP_WORKFLOW_FLAG => 'Y',
3073 P_CREDIT_METHOD_INSTALLMENTS => null,
3074 P_CREDIT_METHOD_RULES => null,
3075 P_BATCH_SOURCE_NAME => 'Loans Credit Memo',
3076 P_ORG_ID => l_org_id,
3077 X_REQUEST_ID => l_request_id,
3078 X_RETURN_STATUS => l_return_status,
3079 X_MSG_COUNT => l_msg_count,
3080 X_MSG_DATA => l_msg_data);
3081
3082 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3083 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_msg_data: ' || substr(l_msg_data,1,225));
3084 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_request_id: ' || l_request_id);
3085
3086 IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error OR
3087 l_request_id is null OR l_request_id = -1
3088 THEN
3089 RAISE FND_API.G_EXC_ERROR;
3090 END IF;
3091 end if;
3092
3093 end loop;
3094
3095 -- update cancellation record
3096 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCELLATIONS...');
3097 update LNS_LOAN_CANCELLATIONS set
3098 STATUS = 'APPROVED'
3099 ,APPR_REJECT_DATE = sysdate
3100 ,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
3101 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3102 ,LAST_UPDATE_DATE = sysdate
3103 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
3104 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
3105 where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
3106 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
3107
3108 open loan_version_cur(l_loan_id);
3109 fetch loan_version_cur into l_version_number;
3110 close loan_version_cur;
3111
3112 l_loan_header_rec.loan_id := l_loan_id;
3113 l_loan_header_rec.LOAN_STATUS := 'CANCELLED';
3114 l_loan_header_rec.SECONDARY_STATUS := FND_API.G_MISS_CHAR;
3115 l_loan_header_rec.DISABLE_BILLING_FLAG := 'N';
3116
3117 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating loan header...');
3118 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
3119 P_LOAN_HEADER_REC => l_loan_header_rec,
3120 P_INIT_MSG_LIST => FND_API.G_FALSE,
3121 X_RETURN_STATUS => l_return_status,
3122 X_MSG_COUNT => l_msg_count,
3123 X_MSG_DATA => l_msg_data);
3124
3125 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3126
3127 IF l_return_status = 'S' THEN
3128 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
3129 ELSE
3130 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3131 FND_MSG_PUB.Add;
3132 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3133 RAISE FND_API.G_EXC_ERROR;
3134 END IF;
3135
3136 -- --------------------------------------------------------------------
3137 -- End of API body
3138 --
3139
3140 if P_COMMIT = FND_API.G_TRUE then
3141 COMMIT WORK;
3142 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3143 end if;
3144
3145 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3146
3147 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3148
3149 EXCEPTION
3150 WHEN OTHERS THEN
3151 ROLLBACK TO approveLoanCancelRequest;
3152 x_return_status := FND_API.G_RET_STS_ERROR;
3153 FND_MESSAGE.SET_NAME('LNS', 'LNS_PLSQL_API_ERROR');
3154 FND_MESSAGE.SET_TOKEN('APINAME', l_api_name);
3155 FND_MESSAGE.SET_TOKEN('ERRMSG', sqlerrm);
3156 FND_MSG_PUB.Add;
3157 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3158 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3159 end;
3160
3161
3162
3163
3164 procedure rejectLoanCancelRequest(p_api_version IN NUMBER
3165 ,p_init_msg_list IN VARCHAR2
3166 ,P_COMMIT IN VARCHAR2
3167 ,P_VALIDATION_LEVEL IN NUMBER
3168 ,p_LOAN_CANCELLATION_ID IN NUMBER
3169 ,x_return_status OUT NOCOPY VARCHAR2
3170 ,x_msg_count OUT NOCOPY NUMBER
3171 ,x_msg_data OUT NOCOPY VARCHAR2)
3172 is
3173
3174 l_api_name varchar2(50);
3175 l_api_version_number number;
3176 l_return_status VARCHAR2(1);
3177 l_msg_count NUMBER;
3178 l_msg_data VARCHAR2(32767);
3179 l_loan_id number;
3180 l_version_number number;
3181
3182 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
3183
3184 CURSOR loan_version_cur(p_LOAN_CANCELLATION_ID number) IS
3185 select loan.LOAN_ID, loan.OBJECT_VERSION_NUMBER
3186 from LNS_LOAN_HEADERS loan, LNS_LOAN_CANCELLATIONS cncl
3187 where cncl.LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID and
3188 cncl.LOAN_ID = loan.LOAN_ID;
3189
3190 begin
3191
3192 l_api_name := 'rejectLoanCancelRequest';
3193 l_api_version_number := 1;
3194 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3195
3196 -- Standard Start of API savepoint
3197 SAVEPOINT rejectLoanCancelRequest;
3198
3199 -- Standard call to check for call compatibility.
3200 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
3201 l_api_name, G_PKG_NAME)
3202 THEN
3203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3204 END IF;
3205
3206 -- Initialize message list IF p_init_msg_list is set to TRUE.
3207 IF FND_API.to_Boolean(p_init_msg_list) THEN
3208 FND_MSG_PUB.initialize;
3209 END IF;
3210
3211 -- Initialize API return status to SUCCESS
3212 x_return_status := FND_API.G_RET_STS_SUCCESS;
3213
3214 --
3215 -- Api body
3216 -- --------------------------------------------------------------------
3217
3218 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3219 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_CANCELLATION_ID = ' || p_LOAN_CANCELLATION_ID);
3220
3221 validateLoanCancelRequest(P_LOAN_CANCELLATION_ID => p_LOAN_CANCELLATION_ID
3222 ,p_action => 'REJECT');
3223
3224 -- update cancellation record
3225 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCELLATIONS...');
3226 update LNS_LOAN_CANCELLATIONS set
3227 STATUS = 'REJECTED'
3228 ,APPR_REJECT_DATE = sysdate
3229 ,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
3230 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3231 ,LAST_UPDATE_DATE = sysdate
3232 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
3233 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
3234 where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
3235 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
3236
3237 open loan_version_cur(p_LOAN_CANCELLATION_ID);
3238 fetch loan_version_cur into l_loan_id, l_version_number;
3239 close loan_version_cur;
3240
3241 l_loan_header_rec.loan_id := l_loan_id;
3242 l_loan_header_rec.DISABLE_BILLING_FLAG := 'N';
3243
3244 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating loan header...');
3245 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
3246 P_LOAN_HEADER_REC => l_loan_header_rec,
3247 P_INIT_MSG_LIST => FND_API.G_FALSE,
3248 X_RETURN_STATUS => l_return_status,
3249 X_MSG_COUNT => l_msg_count,
3250 X_MSG_DATA => l_msg_data);
3251
3252 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3253
3254 IF l_return_status = 'S' THEN
3255 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
3256 ELSE
3257 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3258 FND_MSG_PUB.Add;
3259 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3260 RAISE FND_API.G_EXC_ERROR;
3261 END IF;
3262
3263 -- --------------------------------------------------------------------
3264 -- End of API body
3265 --
3266
3267 if P_COMMIT = FND_API.G_TRUE then
3268 COMMIT WORK;
3269 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3270 end if;
3271
3272 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3273
3274 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3275
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 ROLLBACK TO rejectLoanCancelRequest;
3279 x_return_status := FND_API.G_RET_STS_ERROR;
3280 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3281 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3282 end;
3283
3284
3285
3286 procedure approveLoanSplitRequest(p_api_version IN NUMBER
3287 ,p_init_msg_list IN VARCHAR2
3288 ,P_COMMIT IN VARCHAR2
3289 ,P_VALIDATION_LEVEL IN NUMBER
3290 ,p_LOAN_SPLIT_ID IN NUMBER
3291 ,x_return_status OUT NOCOPY VARCHAR2
3292 ,x_msg_count OUT NOCOPY NUMBER
3293 ,x_msg_data OUT NOCOPY VARCHAR2)
3294 is
3295
3296 l_api_name varchar2(50);
3297 l_api_version_number number;
3298 l_return_status VARCHAR2(1);
3299 l_msg_count NUMBER;
3300 l_msg_data VARCHAR2(32767);
3301
3302 l_LOAN_ID NUMBER;
3303 l_STATUS VARCHAR2(30);
3304 l_PRODUCT_ID NUMBER(15);
3305 l_ORG_ID NUMBER(15);
3306 l_LEGAL_ENTITY_ID NUMBER(15);
3307 l_LOAN_CLASS_CODE VARCHAR2(30);
3308 l_LOAN_TYPE_ID NUMBER(15);
3309 l_LOAN_PURPOSE_CODE VARCHAR2(30);
3310 l_LOAN_DESCRIPTION VARCHAR2(250);
3311 l_LOAN_APPLICATION_DATE DATE;
3312 l_LOAN_CURRENCY VARCHAR2(15);
3313 l_EXCHANGE_RATE_TYPE VARCHAR2(30);
3314 l_EXCHANGE_DATE DATE;
3315 l_EXCHANGE_RATE NUMBER;
3316 l_LOAN_ASSIGNED_TO NUMBER(15);
3317 l_PRIMARY_BORROWER_ID NUMBER(15);
3318 l_CUST_ACCOUNT_ID NUMBER(15);
3319 l_BILL_TO_ACCT_SITE_ID NUMBER(15);
3320 l_contact_rel_party_id NUMBER(15);
3321 l_CONTACT_PERS_PARTY_ID NUMBER(15);
3322 l_REFERENCE_TYPE_ID NUMBER(15);
3323 l_REQUESTED_AMOUNT NUMBER;
3324 l_LOAN_START_DATE DATE;
3325 l_LOAN_TERM NUMBER;
3326 l_LOAN_TERM_PERIOD VARCHAR2(30);
3327 l_BALLOON_PAYMENT_TYPE VARCHAR2(30);
3328 l_BALLOON_PAYMENT_AMOUNT NUMBER;
3329 l_BALLOON_TERM NUMBER;
3330 l_LOAN_SUBTYPE VARCHAR2(30);
3331 l_CREDIT_REVIEW_FLAG VARCHAR2(1);
3332 l_COLLATERAL_PERCENT NUMBER;
3333 l_RATE_TYPE VARCHAR2(30);
3334 l_INDEX_RATE_ID NUMBER(15);
3335 l_PAYMENT_CALC_METHOD VARCHAR2(30);
3336 l_DAY_COUNT_METHOD VARCHAR2(50);
3337 l_AMORTIZATION_FREQUENCY VARCHAR2(30);
3338 l_CALCULATION_METHOD VARCHAR2(30);
3339 l_INTEREST_COMPOUNDING_FREQ VARCHAR2(30);
3340 l_PRIN_PAYMENT_FREQUENCY VARCHAR2(30);
3341 l_LOAN_NUMBER VARCHAR2(20);
3342 l_TERM_INDEX_DATE DATE;
3343 l_INDEX_RATE NUMBER;
3344 l_SPREAD NUMBER;
3345 l_INT_RATE NUMBER;
3346 l_CUSTOM_SCHEDULE CLOB;
3347 l_new_loan_id NUMBER;
3348 l_header_counter number;
3349 l_line_counter number;
3350 l_payment_schedule_id number;
3351 l_new_am_sched_id number;
3352 l_split_request varchar2(30);
3353 l_borrower_name VARCHAR2(360);
3354 l_agreement_reason varchar2(500);
3355
3356 l_BILL_HEADERS_TBL LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL;
3357 l_BILL_LINES_TBL LNS_BILLING_BATCH_PUB.BILL_LINES_TBL;
3358 l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
3359 l_LOAN_LINES_TBL LNS_LOAN_PUB.Loan_Lines_Tbl_Type;
3360 l_LOAN_PART_TBL LNS_LOAN_PUB.LOAN_PART_TBL_TYPE;
3361 l_OPEN_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
3362 l_TERM_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
3363 l_loan_cust_sched_tbl LNS_LOAN_PUB.loan_cust_sched_tbl_type;
3364 l_DISB_TBL LNS_LOAN_PUB.Loan_Disb_Tbl_Type;
3365 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
3366 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
3367
3368
3369 CURSOR split_details_cur(p_LOAN_SPLIT_ID number) IS
3370 select
3371 spl.LOAN_ID
3372 ,spl.DESCRIPTION
3373 ,spl.STATUS
3374 ,spl.PRODUCT_ID
3375 ,spl.ORG_ID
3376 ,spl.LOAN_NUMBER
3377 ,spl.LEGAL_ENTITY_ID
3378 ,spl.LOAN_CLASS_CODE
3379 ,spl.LOAN_TYPE_ID
3380 ,spl.LOAN_PURPOSE_CODE
3381 ,spl.LOAN_DESCRIPTION
3382 ,spl.LOAN_APPLICATION_DATE
3383 ,spl.LOAN_CURRENCY
3384 ,spl.EXCHANGE_RATE_TYPE
3385 ,spl.EXCHANGE_DATE
3386 ,spl.EXCHANGE_RATE
3387 ,spl.LOAN_ASSIGNED_TO
3388 ,spl.PRIMARY_BORROWER_ID
3389 ,spl.CUST_ACCOUNT_ID
3390 ,spl.BILL_TO_ACCT_SITE_ID
3391 ,spl.contact_rel_party_id
3392 ,spl.CONTACT_PERS_PARTY_ID
3393 ,spl.REFERENCE_TYPE_ID
3394 ,spl.REQUESTED_AMOUNT
3395 ,spl.LOAN_START_DATE
3396 ,spl.LOAN_TERM
3397 ,spl.LOAN_TERM_PERIOD
3398 ,spl.BALLOON_PAYMENT_TYPE
3399 ,spl.BALLOON_PAYMENT_AMOUNT
3400 ,spl.BALLOON_TERM
3401 ,spl.LOAN_SUBTYPE
3402 ,spl.CREDIT_REVIEW_FLAG
3403 ,spl.COLLATERAL_PERCENT
3404 ,spl.RATE_TYPE
3405 ,spl.INDEX_RATE_ID
3406 ,spl.PAYMENT_CALC_METHOD
3407 ,spl.DAY_COUNT_METHOD
3408 ,spl.AMORTIZATION_FREQUENCY
3409 ,spl.CALCULATION_METHOD
3410 ,spl.INTEREST_COMPOUNDING_FREQ
3411 ,spl.PRIN_PAYMENT_FREQUENCY
3412 ,spl.TERM_INDEX_DATE
3413 ,spl.INDEX_RATE
3414 ,spl.SPREAD
3415 ,spl.INT_RATE
3416 ,spl.CUSTOM_SCHEDULE
3417 ,party. party_name
3418 from LNS_LOAN_SPLITS spl,
3419 HZ_PARTIES party
3420 where spl.LOAN_SPLIT_ID = p_LOAN_SPLIT_ID and
3421 party.party_id = spl.PRIMARY_BORROWER_ID;
3422
3423 CURSOR get_psa_cur(P_LOAN_ID number) IS
3424 SELECT psa.payment_schedule_id,
3425 am.AMORTIZATION_SCHEDULE_ID
3426 FROM ar_payment_schedules_all psa,
3427 LNS_LOAN_HEADERS_ALL loan,
3428 lns_amortization_scheds am
3429 WHERE loan.loan_id = am.loan_id and
3430 am.AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID and
3431 am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and
3432 loan.loan_id = P_LOAN_ID and
3433 psa.CUSTOMER_TRX_ID = am.principal_trx_id;
3434
3435 CURSOR get_loan_number(P_LOAN_ID number) IS
3436 select loan_number
3437 from lns_loan_headers_all
3438 where loan_id = P_LOAN_ID;
3439
3440 begin
3441
3442 l_api_name := 'approveLoanSplitRequest';
3443 l_api_version_number := 1;
3444 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3445
3446 -- Standard Start of API savepoint
3447 SAVEPOINT approveLoanSplitRequest;
3448
3449 -- Standard call to check for call compatibility.
3450 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
3451 l_api_name, G_PKG_NAME)
3452 THEN
3453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3454 END IF;
3455
3456 -- Initialize message list IF p_init_msg_list is set to TRUE.
3457 IF FND_API.to_Boolean(p_init_msg_list) THEN
3458 FND_MSG_PUB.initialize;
3459 END IF;
3460
3461 -- Initialize API return status to SUCCESS
3462 x_return_status := FND_API.G_RET_STS_SUCCESS;
3463
3464 --
3465 -- Api body
3466 -- --------------------------------------------------------------------
3467
3468 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3469 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_SPLIT_ID = ' || p_LOAN_SPLIT_ID);
3470
3471 open split_details_cur(p_LOAN_SPLIT_ID);
3472 fetch split_details_cur into
3473 l_LOAN_ID
3474 ,l_split_request
3475 ,l_STATUS
3476 ,l_PRODUCT_ID
3477 ,l_ORG_ID
3478 ,l_LOAN_NUMBER
3479 ,l_LEGAL_ENTITY_ID
3480 ,l_LOAN_CLASS_CODE
3481 ,l_LOAN_TYPE_ID
3482 ,l_LOAN_PURPOSE_CODE
3483 ,l_LOAN_DESCRIPTION
3484 ,l_LOAN_APPLICATION_DATE
3485 ,l_LOAN_CURRENCY
3486 ,l_EXCHANGE_RATE_TYPE
3487 ,l_EXCHANGE_DATE
3488 ,l_EXCHANGE_RATE
3489 ,l_LOAN_ASSIGNED_TO
3490 ,l_PRIMARY_BORROWER_ID
3491 ,l_CUST_ACCOUNT_ID
3492 ,l_BILL_TO_ACCT_SITE_ID
3493 ,l_contact_rel_party_id
3494 ,l_CONTACT_PERS_PARTY_ID
3495 ,l_REFERENCE_TYPE_ID
3496 ,l_REQUESTED_AMOUNT
3497 ,l_LOAN_START_DATE
3498 ,l_LOAN_TERM
3499 ,l_LOAN_TERM_PERIOD
3500 ,l_BALLOON_PAYMENT_TYPE
3501 ,l_BALLOON_PAYMENT_AMOUNT
3502 ,l_BALLOON_TERM
3503 ,l_LOAN_SUBTYPE
3504 ,l_CREDIT_REVIEW_FLAG
3505 ,l_COLLATERAL_PERCENT
3506 ,l_RATE_TYPE
3507 ,l_INDEX_RATE_ID
3508 ,l_PAYMENT_CALC_METHOD
3509 ,l_DAY_COUNT_METHOD
3510 ,l_AMORTIZATION_FREQUENCY
3511 ,l_CALCULATION_METHOD
3512 ,l_INTEREST_COMPOUNDING_FREQ
3513 ,l_PRIN_PAYMENT_FREQUENCY
3514 ,l_TERM_INDEX_DATE
3515 ,l_INDEX_RATE
3516 ,l_SPREAD
3517 ,l_INT_RATE
3518 ,l_CUSTOM_SCHEDULE
3519 ,l_borrower_name;
3520 close split_details_cur;
3521
3522 if (l_STATUS <> 'PENDING') then
3523 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3524 FND_MESSAGE.SET_TOKEN('PARAMETER', 'status');
3525 FND_MESSAGE.SET_TOKEN('VALUE', l_STATUS);
3526 FND_MSG_PUB.ADD;
3527 RAISE FND_API.G_EXC_ERROR;
3528 end if;
3529
3530 LNS_BILLING_BATCH_PUB.VALIDATE_PRIN_AMOUNT(l_loan_id, l_REQUESTED_AMOUNT, l_LOAN_START_DATE);
3531
3532 l_header_counter := 1;
3533 l_BILL_HEADERS_TBL(l_header_counter).HEADER_ID := l_header_counter;
3534 l_BILL_HEADERS_TBL(l_header_counter).LOAN_ID := l_loan_id;
3535 l_BILL_HEADERS_TBL(l_header_counter).ASSOC_PAYMENT_NUM := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(l_loan_id);
3536 l_BILL_HEADERS_TBL(l_header_counter).DUE_DATE := l_LOAN_START_DATE;
3537 l_BILL_HEADERS_TBL(l_header_counter).CUST_ACCOUNT_ID := l_CUST_ACCOUNT_ID;
3538 l_BILL_HEADERS_TBL(l_header_counter).BILL_TO_ADDRESS_ID := l_BILL_TO_ACCT_SITE_ID;
3539
3540 l_line_counter := 1;
3541 l_BILL_LINES_TBL(l_line_counter).LINE_ID := l_line_counter;
3542 l_BILL_LINES_TBL(l_line_counter).HEADER_ID := l_header_counter;
3543 l_BILL_LINES_TBL(l_line_counter).LINE_AMOUNT := l_REQUESTED_AMOUNT;
3544 l_BILL_LINES_TBL(l_line_counter).LINE_TYPE := 'PRIN';
3545 l_BILL_LINES_TBL(l_line_counter).LINE_DESC := 'Loan split';
3546
3547 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_billing_batch_pub.create_offcycle_bills...');
3548 lns_billing_batch_pub.create_offcycle_bills(p_api_version => 1.0
3549 ,p_init_msg_list => FND_API.G_TRUE
3550 ,p_commit => FND_API.G_FALSE
3551 ,p_validation_level => 100
3552 ,p_bill_headers_tbl => l_BILL_HEADERS_TBL
3553 ,p_bill_lines_tbl => l_BILL_LINES_TBL
3554 ,x_return_status => l_return_status
3555 ,x_msg_count => l_msg_count
3556 ,x_msg_data => l_msg_data);
3557
3558 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status ' || l_return_status);
3559 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3560 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_CREATION_ERROR');
3561 FND_MSG_PUB.Add;
3562 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3563 RAISE FND_API.G_EXC_ERROR;
3564 END IF;
3565
3566 open get_psa_cur(l_loan_id);
3567 fetch get_psa_cur into l_payment_schedule_id, l_new_am_sched_id;
3568 close get_psa_cur;
3569 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_new_am_sched_id = ' || l_new_am_sched_id);
3570 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_schedule_id = ' || l_payment_schedule_id);
3571
3572 -- call loan creation api
3573
3574 l_LOAN_DTL_REC.product_id := l_PRODUCT_ID;
3575 l_LOAN_DTL_REC.loan_number := l_LOAN_NUMBER;
3576 l_LOAN_DTL_REC.LOAN_DESCRIPTION := l_LOAN_DESCRIPTION;
3577 l_LOAN_DTL_REC.LOAN_ASSIGNED_TO := l_LOAN_ASSIGNED_TO;
3578 l_LOAN_DTL_REC.legal_entity_id := l_LEGAL_ENTITY_ID;
3579 l_LOAN_DTL_REC.requested_amount := l_REQUESTED_AMOUNT;
3580 l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := l_LOAN_APPLICATION_DATE;
3581 l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := l_EXCHANGE_RATE_TYPE;
3582 l_LOAN_DTL_REC.EXCHANGE_DATE := l_EXCHANGE_DATE;
3583 l_LOAN_DTL_REC.EXCHANGE_RATE := l_EXCHANGE_RATE;
3584 l_LOAN_DTL_REC.LOAN_PURPOSE_CODE := l_LOAN_PURPOSE_CODE;
3585 l_LOAN_DTL_REC.LOAN_SUBTYPE := l_LOAN_SUBTYPE;
3586 l_LOAN_DTL_REC.credit_review_flag := l_CREDIT_REVIEW_FLAG;
3587 l_LOAN_DTL_REC.COLLATERAL_PERCENT := l_COLLATERAL_PERCENT;
3588 l_LOAN_DTL_REC.FORGIVENESS_FLAG := null;
3589 l_LOAN_DTL_REC.FORGIVENESS_PERCENT := null;
3590
3591 IF l_PAYMENT_CALC_METHOD = 'CUSTOM' and l_CUSTOM_SCHEDULE <> empty_clob() THEN
3592 l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := 'Y';
3593 ELSE
3594 l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := 'N';
3595 END IF;
3596
3597 -- set trx_type_id
3598 l_LOAN_DTL_REC.trx_type_id := l_REFERENCE_TYPE_ID;
3599 if l_LOAN_DTL_REC.trx_type_id is null then
3600 select TRX_TYPE_ID INTO l_LOAN_DTL_REC.trx_type_id
3601 FROM LNS_SYSTEM_OPTIONS
3602 WHERE ORG_ID = l_ORG_ID;
3603 end if;
3604
3605 -- primary borrower attributes
3606 l_LOAN_DTL_REC.primary_borrower_party_id := l_PRIMARY_BORROWER_ID;
3607 l_LOAN_DTL_REC.CUST_ACCOUNT_ID := l_CUST_ACCOUNT_ID;
3608 l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := l_BILL_TO_ACCT_SITE_ID;
3609 l_LOAN_DTL_REC.contact_rel_party_id := l_contact_rel_party_id;
3610 l_LOAN_DTL_REC.CONTACT_PERS_PARTY_ID := l_CONTACT_PERS_PARTY_ID;
3611
3612 -- common term attributes
3613 l_LOAN_DTL_REC.RATE_TYPE := l_RATE_TYPE;
3614 l_LOAN_DTL_REC.INDEX_RATE_ID := l_INDEX_RATE_ID;
3615 l_LOAN_DTL_REC.DAY_COUNT_METHOD := l_DAY_COUNT_METHOD;
3616 l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := l_AMORTIZATION_FREQUENCY;
3617 l_LOAN_DTL_REC.CALCULATION_METHOD := l_CALCULATION_METHOD;
3618 l_LOAN_DTL_REC.INTEREST_COMPOUNDING_FREQ := l_INTEREST_COMPOUNDING_FREQ;
3619 l_LOAN_DTL_REC.PAYMENT_CALC_METHOD := l_PAYMENT_CALC_METHOD;
3620 l_LOAN_DTL_REC.CUSTOM_CALC_METHOD := null;
3621 l_LOAN_DTL_REC.ORIG_PAY_CALC_METHOD := null;
3622 l_LOAN_DTL_REC.PENAL_INT_RATE := null;
3623 l_LOAN_DTL_REC.PENAL_INT_GRACE_DAYS := null;
3624 l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_PRIN := null;
3625 l_LOAN_DTL_REC.CALC_ADD_INT_UNPAID_INT := null;
3626 l_LOAN_DTL_REC.LOCK_DATE := null;
3627 l_LOAN_DTL_REC.LOCK_EXP_DATE := null;
3628
3629 -- 'term phase' term attributes
3630 l_LOAN_DTL_REC.LOAN_TERM := l_LOAN_TERM;
3631 l_LOAN_DTL_REC.LOAN_TERM_PERIOD := l_LOAN_TERM_PERIOD;
3632 l_LOAN_DTL_REC.balloon_payment_type := l_BALLOON_PAYMENT_TYPE;
3633 l_LOAN_DTL_REC.balloon_payment_amount := l_BALLOON_PAYMENT_AMOUNT;
3634 l_LOAN_DTL_REC.balloon_term := l_BALLOON_TERM;
3635 l_LOAN_DTL_REC.loan_start_date := l_LOAN_START_DATE;
3636 l_LOAN_DTL_REC.FIRST_PAYMENT_DATE := null;
3637 l_LOAN_DTL_REC.PRIN_FIRST_PAY_DATE := null;
3638 l_LOAN_DTL_REC.PRIN_PAYMENT_FREQUENCY := l_PRIN_PAYMENT_FREQUENCY;
3639 l_LOAN_DTL_REC.floor_rate := null;
3640 l_LOAN_DTL_REC.ceiling_rate := null;
3641 l_LOAN_DTL_REC.percent_increase := null;
3642 l_LOAN_DTL_REC.percent_increase_life := null;
3643
3644 -- Terms for Payment attributes
3645 l_LOAN_DTL_REC.REAMORTIZE_OVER_PAYMENT := null;
3646 l_LOAN_DTL_REC.DELINQUENCY_THRESHOLD_AMOUNT := null;
3647 l_LOAN_DTL_REC.PAYMENT_APPLICATION_ORDER := null;
3648 l_LOAN_DTL_REC.PMT_APPL_ORDER_SCOPE := null;
3649 l_LOAN_DTL_REC.REAMORTIZE_ON_FUNDING := null;
3650
3651 -- additional optional attributes
3652 l_LOAN_DTL_REC.ATTRIBUTE_CATEGORY := null;
3653 l_LOAN_DTL_REC.ATTRIBUTE1 := null;
3654 l_LOAN_DTL_REC.ATTRIBUTE2 := null;
3655 l_LOAN_DTL_REC.ATTRIBUTE3 := null;
3656 l_LOAN_DTL_REC.ATTRIBUTE4 := null;
3657 l_LOAN_DTL_REC.ATTRIBUTE5 := null;
3658 l_LOAN_DTL_REC.ATTRIBUTE6 := null;
3659 l_LOAN_DTL_REC.ATTRIBUTE7 := null;
3660 l_LOAN_DTL_REC.ATTRIBUTE8 := null;
3661 l_LOAN_DTL_REC.ATTRIBUTE9 := null;
3662 l_LOAN_DTL_REC.ATTRIBUTE10 := null;
3663 l_LOAN_DTL_REC.ATTRIBUTE11 := null;
3664 l_LOAN_DTL_REC.ATTRIBUTE12 := null;
3665 l_LOAN_DTL_REC.ATTRIBUTE13 := null;
3666 l_LOAN_DTL_REC.ATTRIBUTE14 := null;
3667 l_LOAN_DTL_REC.ATTRIBUTE15 := null;
3668 l_LOAN_DTL_REC.ATTRIBUTE16 := null;
3669 l_LOAN_DTL_REC.ATTRIBUTE17 := null;
3670 l_LOAN_DTL_REC.ATTRIBUTE18 := null;
3671 l_LOAN_DTL_REC.ATTRIBUTE19 := null;
3672 l_LOAN_DTL_REC.ATTRIBUTE20 := null;
3673
3674 -- LOAN LINES
3675 l_LOAN_LINES_TBL(1).line_number := 1;
3676 l_LOAN_LINES_TBL(1).payment_schedule_id := l_payment_schedule_id;
3677 l_LOAN_LINES_TBL(1).requested_amount := l_REQUESTED_AMOUNT;
3678 --l_LOAN_LINES_TBL(1).REFERENCE_DESCRIPTION := 'Description of Reference1';
3679
3680 --rate schedule
3681 l_TERM_RATES_TBL(1).INDEX_RATE := l_INDEX_RATE;
3682 l_TERM_RATES_TBL(1).SPREAD := l_SPREAD;
3683 l_TERM_RATES_TBL(1).BEGIN_INSTALLMENT_NUMBER := 1;
3684 l_TERM_RATES_TBL(1).END_INSTALLMENT_NUMBER := -1;
3685 l_TERM_RATES_TBL(1).INTEREST_ONLY_FLAG := null;
3686
3687 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_LOAN_PUB.CREATE_LOAN...');
3688 LNS_LOAN_PUB.CREATE_LOAN(
3689 P_API_VERSION => 1.0,
3690 P_INIT_MSG_LIST => FND_API.G_TRUE,
3691 P_COMMIT => FND_API.G_FALSE,
3692 P_VALIDATION_LEVEL => 100,
3693 P_Loan_Details_Rec => l_LOAN_DTL_REC,
3694 P_Loan_Lines_Tbl => l_LOAN_LINES_TBL,
3695 P_DISB_TBL => l_DISB_TBL,
3696 P_LOAN_PART_TBL => l_LOAN_PART_TBL,
3697 P_OPEN_RATES_TBL => l_OPEN_RATES_TBL,
3698 P_TERM_RATES_TBL => l_TERM_RATES_TBL,
3699 p_loan_cust_sched_tbl => l_loan_cust_sched_tbl,
3700 P_Application_id => 206,
3701 P_Created_by_module => G_PKG_NAME || '.' || l_api_name,
3702 X_LOAN_ID => l_new_loan_id,
3703 X_RETURN_STATUS => l_return_status,
3704 X_MSG_COUNT => l_msg_count,
3705 X_MSG_DATA => l_msg_data);
3706
3707 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status ' || l_return_status);
3708 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3709 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3710 RAISE FND_API.G_EXC_ERROR;
3711 END IF;
3712
3713 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully created loan with id ' || l_new_loan_id);
3714
3715 open get_loan_number(l_new_loan_id);
3716 fetch get_loan_number into l_LOAN_NUMBER;
3717 close get_loan_number;
3718
3719 if l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG = 'Y' then
3720
3721 -- if this is custom schedule loan then parse it and save it
3722 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_CUSTOM_PUB.parseClob...');
3723 LNS_CUSTOM_PUB.parseClob(
3724 P_API_VERSION => 1.0,
3725 P_INIT_MSG_LIST => FND_API.G_TRUE,
3726 P_COMMIT => FND_API.G_FALSE,
3727 P_VALIDATION_LEVEL => 100,
3728 P_CLOB => l_CUSTOM_SCHEDULE,
3729 P_RETAIN_DATA => 'Y',
3730 x_return_status => l_return_status,
3731 x_msg_count => l_msg_count,
3732 x_msg_data => l_msg_data);
3733
3734 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status ' || l_return_status);
3735 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3736 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3737 RAISE FND_API.G_EXC_ERROR;
3738 END IF;
3739
3740 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_CUSTOM_PUB.saveCustomSchedule...');
3741 LNS_CUSTOM_PUB.saveCustomSchedule(
3742 P_API_VERSION => 1.0,
3743 P_INIT_MSG_LIST => FND_API.G_TRUE,
3744 P_COMMIT => FND_API.G_FALSE,
3745 P_VALIDATION_LEVEL => 100,
3746 P_LOAN_ID => l_new_loan_id,
3747 P_BASED_ON_TERMS => 'ORIGINATION',
3748 P_USE_RETAINED_DATA => 'Y',
3749 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
3750 P_CUSTOM_TBL => l_CUSTOM_TBL,
3751 x_return_status => l_return_status,
3752 x_msg_count => l_msg_count,
3753 x_msg_data => l_msg_data);
3754
3755 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status ' || l_return_status);
3756 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3757 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3758 RAISE FND_API.G_EXC_ERROR;
3759 END IF;
3760
3761 end if;
3762
3763 -- update split record
3764 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCELLATIONS...');
3765 update LNS_LOAN_SPLITS set
3766 NEW_AM_SCHED_ID = l_new_am_sched_id
3767 ,NEW_LOAN_ID = l_new_loan_id
3768 ,LOAN_NUMBER = l_LOAN_NUMBER
3769 ,STATUS = 'APPROVED'
3770 ,APPR_REJECT_DATE = sysdate
3771 ,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
3772 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3773 ,LAST_UPDATE_DATE = sysdate
3774 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
3775 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
3776 where LOAN_SPLIT_ID = p_LOAN_SPLIT_ID;
3777 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
3778
3779 -- fix for bug 14491711
3780 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_SPLIT_AGR_REASON');
3781 FND_MESSAGE.SET_TOKEN('SPLIT', l_split_request);
3782 FND_MESSAGE.SET_TOKEN('LOAN', l_LOAN_NUMBER);
3783 FND_MESSAGE.SET_TOKEN('AMOUNT', to_char(l_REQUESTED_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(l_LOAN_CURRENCY,50)));
3784 FND_MESSAGE.SET_TOKEN('CURR', l_LOAN_CURRENCY);
3785 FND_MESSAGE.SET_TOKEN('BORROWER', l_borrower_name);
3786 FND_MSG_PUB.Add;
3787 l_agreement_reason := FND_MSG_PUB.Get(p_encoded => 'F');
3788 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
3789
3790 LNS_REP_UTILS.STORE_LOAN_AGREEMENT_CP(l_loan_id, l_agreement_reason);
3791
3792 -- --------------------------------------------------------------------
3793 -- End of API body
3794 --
3795
3796 if P_COMMIT = FND_API.G_TRUE then
3797 COMMIT WORK;
3798 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3799 end if;
3800
3801 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3802
3803 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3804
3805 EXCEPTION
3806 WHEN OTHERS THEN
3807 ROLLBACK TO approveLoanSplitRequest;
3808 x_return_status := FND_API.G_RET_STS_ERROR;
3809 FND_MESSAGE.SET_NAME('LNS', 'LNS_PLSQL_API_ERROR');
3810 FND_MESSAGE.SET_TOKEN('APINAME', l_api_name);
3811 FND_MESSAGE.SET_TOKEN('ERRMSG', sqlerrm);
3812 FND_MSG_PUB.Add;
3813 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3814 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3815 end;
3816
3817
3818
3819 procedure rejectLoanSplitRequest(p_api_version IN NUMBER
3820 ,p_init_msg_list IN VARCHAR2
3821 ,P_COMMIT IN VARCHAR2
3822 ,P_VALIDATION_LEVEL IN NUMBER
3823 ,p_LOAN_SPLIT_ID IN NUMBER
3824 ,x_return_status OUT NOCOPY VARCHAR2
3825 ,x_msg_count OUT NOCOPY NUMBER
3826 ,x_msg_data OUT NOCOPY VARCHAR2)
3827 is
3828
3829 l_api_name varchar2(50);
3830 l_api_version_number number;
3831
3832 begin
3833
3834 l_api_name := 'rejectLoanSplitRequest';
3835 l_api_version_number := 1;
3836 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3837
3838 -- Standard Start of API savepoint
3839 SAVEPOINT rejectLoanCancelRequest;
3840
3841 -- Standard call to check for call compatibility.
3842 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
3843 l_api_name, G_PKG_NAME)
3844 THEN
3845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3846 END IF;
3847
3848 -- Initialize message list IF p_init_msg_list is set to TRUE.
3849 IF FND_API.to_Boolean(p_init_msg_list) THEN
3850 FND_MSG_PUB.initialize;
3851 END IF;
3852
3853 -- Initialize API return status to SUCCESS
3854 x_return_status := FND_API.G_RET_STS_SUCCESS;
3855
3856 --
3857 -- Api body
3858 -- --------------------------------------------------------------------
3859
3860 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3861 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_LOAN_SPLIT_ID = ' || p_LOAN_SPLIT_ID);
3862
3863 --validateLoanCancelRequest(P_LOAN_CANCELLATION_ID => p_LOAN_CANCELLATION_ID
3864 -- ,p_action => 'REJECT');
3865
3866 -- update split record
3867 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating LNS_LOAN_CANCELLATIONS...');
3868 update LNS_LOAN_SPLITS set
3869 STATUS = 'REJECTED'
3870 ,APPR_REJECT_DATE = sysdate
3871 ,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
3872 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3873 ,LAST_UPDATE_DATE = sysdate
3874 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
3875 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
3876 where LOAN_SPLIT_ID = p_LOAN_SPLIT_ID;
3877 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
3878
3879 -- --------------------------------------------------------------------
3880 -- End of API body
3881 --
3882
3883 if P_COMMIT = FND_API.G_TRUE then
3884 COMMIT WORK;
3885 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3886 end if;
3887
3888 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3889
3890 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3891
3892 EXCEPTION
3893 WHEN OTHERS THEN
3894 ROLLBACK TO rejectLoanSplitRequest;
3895 x_return_status := FND_API.G_RET_STS_ERROR;
3896 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3897 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3898 end;
3899
3900
3901
3902 END LNS_PAYOFF_PUB;