[Home] [Help]
PACKAGE BODY: APPS.LNS_PAYOFF_PUB
Source
1 PACKAGE BODY LNS_PAYOFF_PUB AS
2 /* $Header: LNS_PAYOFF_B.pls 120.5.12010000.2 2008/12/22 10:45:41 gparuchu 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)
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
194 l_fee_amount number;
195 l_fee_name varchar2(50);
196 l_fee_installment number;
197 l_sob_currency varchar2(30);
198
199 cursor c_sob_currency is
200 SELECT sb.currency_code
201 FROM lns_system_options so,
202 gl_sets_of_books sb
203 WHERE sb.set_of_books_id = so.set_of_books_id;
204
205 begin
206 l_api_name := 'processPayoff';
207 l_api_version_number := 1;
208 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
209
210 -- Standard Start of API savepoint
211 SAVEPOINT processPayoff;
212
213 -- Standard call to check for call compatibility.
214 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
215 l_api_name, G_PKG_NAME)
216 THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 -- Initialize message list IF p_init_msg_list is set to TRUE.
221 IF FND_API.to_Boolean(p_init_msg_list) THEN
222 FND_MSG_PUB.initialize;
223 END IF;
224
225 -- Initialize API return status to SUCCESS
226 x_return_status := FND_API.G_RET_STS_SUCCESS;
227
228 --
229 -- Api body
230 -- --------------------------------------------------------------------
231 -- initialize any variables here
232 l_cash_receipt_ids := p_cash_receipt_ids;
233 l_cash_receipts_count := p_cash_receipt_ids.count;
234 l_total_payoff_amount := 0;
235 l_total_receipts_amount := 0;
236 l_receipt_amount := 0;
237 l_amount_to_apply := 0;
238 l_new_interest := 0;
239 l_receipt_amount_remaining := 0;
240 l_invoice_amount_remaining := 0;
241 i := 0;
242 s := 0;
243 b_recalculate_interest := true;
244 l_final_balance := -1; --this is for final balance check
245
246 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - receipts passed ' || l_cash_receipts_count);
247 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Payoff loanID ' || p_loan_id);
248 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Payoff date ' || p_payoff_date);
249 open loans_cur(p_loan_id);
250 fetch loans_cur into
251 l_loan_currency
252 ,l_loan_exchange_rate
253 ,l_loan_exchange_date
254 ,l_loan_exchange_rate_type
255 ,l_phase;
256 close loans_cur;
257
258 open c_sob_currency;
259 fetch c_sob_currency into l_sob_currency;
260 close c_sob_currency;
261 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LOAN CURRENCY ' || l_loan_currency);
262 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_exchange_rate ' || l_loan_exchange_rate);
263
264 /*
265 -- if loan status is not valid, exit
266 l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
267 ,p_based_on_terms => 'CURRENT'
268 ,p_phase => l_phase);
269
270 lns_financials.validatePayoff(p_loan_details => l_loan_details
271 ,p_payoff_date => p_payoff_date
272 ,x_return_status => l_return_status
273 ,x_msg_count => l_msg_count
274 ,x_msg_data => l_msg_data);
275 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
276 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || FND_MSG_PUB.Get(p_encoded => 'F'));
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279 */
280 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating Payoff ');
281 lns_financials.calculatePayoff(p_api_version => 1.0
282 ,p_init_msg_list => p_init_msg_list
283 ,p_loan_id => p_loan_id
284 ,p_payoff_date => p_payoff_date
285 ,x_payoff_tbl => l_payoff_tbl2
286 ,x_return_status => l_return_status
287 ,x_msg_count => l_msg_count
288 ,x_msg_data => l_msg_data);
289
290 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
291 FND_MESSAGE.SET_NAME('LNS', 'LNS_CALCULATE_PAYOFF_ERROR');
292 FND_MSG_PUB.Add;
293 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || FND_MSG_PUB.Get(p_encoded => 'F'));
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296
297 --2. create payoff document(s) in receivables
298 -- build the header for the loan document(s) to be created
299 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Building Invoice header/lines');
300 l_bill_headers_tbl(1).HEADER_ID := 101;
301 l_bill_headers_tbl(1).LOAN_ID := p_loan_id;
302 l_bill_headers_tbl(1).ASSOC_PAYMENT_NUM := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
303 l_bill_headers_tbl(1).DUE_DATE := p_payoff_date;
304
305 -- now build the lines for the loan document(s) to be created
306 for i in 1..l_payoff_tbl2.count
307 loop
308 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - purpose ' || l_payoff_tbl2(i).payoff_purpose);
309 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount ' || l_payoff_tbl2(i).unbilled_amount);
310
311 if l_payoff_tbl2(i).unbilled_amount > 0 and l_payoff_tbl2(i).payoff_purpose <> 'FEE' then
312 s := s + 1;
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_payoff_tbl2(i).unbilled_amount;
316 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).PAYOFF_PURPOSE;
317 --l_BILL_LINES_TBL(1).LINE_DESC := 'Extra principal';
318
319 elsif l_payoff_tbl2(i).unbilled_amount > 0 and l_payoff_tbl2(i).payoff_purpose = 'FEE' then
320
321 open c_fees(p_loan_id);
322 loop
323 s := s + 1;
324 fetch c_fees into
325 l_fee_amount, l_fee_name, l_fee_installment;
326 exit when c_fees%notfound;
327 l_bill_lines_tbl(s).LINE_ID := i;
328 l_bill_lines_tbl(s).HEADER_ID := 101;
329 l_bill_lines_tbl(s).LINE_AMOUNT := l_fee_amount;
330 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).payoff_purpose;
331 l_bill_lines_tbl(s).LINE_DESC := l_fee_name;
332 --l_invoices_tbl(i).INSTALLMENT_NUMBER := l_fee_installment;
333 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - fee found');
334 end loop;
335 close c_fees;
336
337 elsif l_payoff_tbl2(i).unbilled_amount < 0 and l_payoff_tbl2(i).payoff_purpose = 'INT' then
338
339 -- we have an interest credit due we will credit out the interest on the last amortization
340 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - interest credit is due');
341 -- 1. check to see if documents have been paid,
342 -- unapply all cash from receivable applications
343 open c_applications(p_loan_id);
344 loop
345 fetch c_applications
346 into l_rec_application_id
347 ,l_cash_receipt_id
348 ,l_amount_applied
349 ,l_customer_trx_id
350 ,l_app_pay_sched_id
351 ,l_receipt_number
352 ,l_currency_code
353 ,l_rec_exchange_rate
354 ,l_rec_exchange_date
355 ,l_rec_exchange_rate_type
356 ,l_interest_trx_id
357 ,l_principal_trx_id
358 ,l_fee_trx_id;
359
360 exit when c_applications%notfound;
361
362 -- 2. if so, unapply all cash_receipts from document
363 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - unapplying cash from pay_sched_id: ' || l_rec_application_id);
364 ar_receipt_api_pub.unapply(p_api_version => 1.0
365 ,p_init_msg_list => p_init_msg_list
366 ,p_commit => FND_API.G_FALSE
367 ,p_receivable_application_id => l_rec_application_id
368 ,x_return_status => l_return_status
369 ,x_msg_count => l_msg_count
370 ,x_msg_data => l_msg_data);
371
372 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373 FND_MESSAGE.SET_NAME('LNS', 'LNS_UNAPPLY_CASH_ERROR');
374 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RECEIPT_NUMBER');
375 FND_MESSAGE.SET_TOKEN('VALUE', l_receipt_number);
376 FND_MSG_PUB.Add;
377 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
378 RAISE FND_API.G_EXC_ERROR;
379 end if;
380
381
382 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - unapplied cash successfully');
383 -- 2a. keep unapplied cash_receipts in memory to use for payoff
384 -- bug #4191794
385 -- if this cash receipt_id exists in the set of selected cash receipts then
386 -- we will have to add it back to existing cash_receipt_id
387 l_bool_match := false;
388 for j in 1..l_cash_receipt_ids.count loop
389 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - check existing cr_id: ' || l_cash_receipt_ids(j).cash_receipt_id);
390 if l_cash_receipt_id = l_cash_receipt_ids(j).cash_receipt_id then
391 l_cash_receipt_ids(j).receipt_amount := l_cash_receipt_ids(j).receipt_amount + l_amount_applied;
392 l_bool_match := true;
393 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - matched existing cr_id: ' || l_cash_receipt_ids(j).cash_receipt_id);
394 exit;
395 end if;
396 end loop;
397
398 if not l_bool_match then
399 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - receipts count is: ' || l_cash_receipts_count);
400 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - adding applied cr_id: ' || l_cash_receipt_id);
401 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - cr_id amount: ' || l_amount_applied);
402 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - cr_id currency: ' || l_loan_currency);
403 l_cash_receipts_count := l_cash_receipts_count + 1;
404 l_cash_receipt_ids(l_cash_receipts_count).CASH_RECEIPT_ID := l_cash_receipt_id;
405 l_cash_receipt_ids(l_cash_receipts_count).RECEIPT_AMOUNT := l_amount_applied;
406 l_cash_receipt_ids(l_cash_receipts_count).RECEIPT_CURRENCY := l_loan_currency;
407
408 if l_currency_code <> l_loan_currency then
409 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_RATE := l_rec_exchange_rate;
410 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_DATE := l_rec_exchange_date;
411 l_cash_receipt_ids(l_cash_receipts_count).EXCHANGE_RATE_TYPE := l_rec_exchange_rate_type;
412 end if;
413 end if;
414 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - added cash rec id ' || l_cash_receipt_id);
415 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - added cash amount ' || l_amount_applied || ' ' || l_currency_code);
416
417 end loop; --end unapplication loop
418
419 -- 3. credit document
420 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - crediting final interest document');
421 /*=======================================================================*/
422 LNS_BILLING_BATCH_PUB.CREDIT_AMORTIZATION_PARTIAL(P_API_VERSION => 1.0
423 ,P_INIT_MSG_LIST => p_init_msg_list
424 ,P_COMMIT => FND_API.G_FALSE
425 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
426 ,P_LOAN_ID => p_loan_id
427 ,P_LINE_TYPE => 'INT'
428 ,X_RETURN_STATUS => l_return_status
429 ,X_MSG_COUNT => l_msg_count
430 ,X_MSG_DATA => l_msg_data);
431
432 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
433 FND_MESSAGE.SET_NAME('LNS', 'LNS_CREDIT_MEMO_ERROR');
434 FND_MSG_PUB.Add;
435 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
436 RAISE FND_API.G_EXC_ERROR;
437 end if;
438
439 -- 4. create new interest and principal document for difference
440 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATING INTEREST');
441 if b_recalculate_interest then
442 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - l_interest_trx_Id: ' || l_interest_trx_Id);
443 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - l_customer_trx_id: ' || l_customer_trx_id);
444
445 if l_interest_trx_id = l_customer_trx_id then
446 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATED INTEREST DUE: ' || l_new_interest);
447
448 -- over interest charge had previous applications
449 select amount_due_original + l_payoff_tbl2(i).unbilled_amount
450 into l_new_interest
451 from ar_payment_schedules
452 where payment_schedule_id = l_app_pay_sched_id;
453
454 else
455 -- over interest charge had no previous applications
456 select interest_amount + l_payoff_tbl2(i).unbilled_amount
457 into l_new_interest
458 from lns_amortization_scheds
459 where loan_id = p_loan_id
460 and reamortization_amount is null
461 and parent_amortization_id is null
462 --and reversed_flag = 'N'
463 and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
464
465 end if;
466 b_recalculate_interest := false;
467 end if;
468
469 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - RECALCULATED INTEREST DUE: ' || l_new_interest);
470
471 -- we will only create a new interest document IF there is an interest amount due
472 if l_new_interest > 0 then
473 s := s + 1;
474 l_bill_lines_tbl(s).LINE_ID := i;
475 l_bill_lines_tbl(s).HEADER_ID := 101;
476 l_bill_lines_tbl(s).LINE_AMOUNT := l_new_interest;
477 l_bill_lines_tbl(s).LINE_TYPE := l_payoff_tbl2(i).PAYOFF_PURPOSE;
478 end if;
479
480 end if;
481
482 end loop;
483
484 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - billing lines' || l_bill_lines_tbl.count);
485 if l_bill_lines_tbl.count >= 1 then
486
487 -- we have at least 1 invoice to create with amount > 0
488 -- must pass false for commit here or else payoff documents will exist in receivables
489 lns_billing_batch_pub.create_offcycle_bills(p_api_version => 1.0
490 ,p_init_msg_list => p_init_msg_list
491 ,p_commit => FND_API.G_FALSE
492 ,p_validation_level => 100
493 ,p_bill_headers_tbl => l_bill_headers_tbl
494 ,p_bill_lines_tbl => l_bill_lines_tbl
495 ,x_return_status => l_return_status
496 ,x_msg_count => l_msg_count
497 ,x_msg_data => l_msg_data);
498
499 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_CREATION_ERROR');
501 FND_MSG_PUB.Add;
502 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
503 RAISE FND_API.G_EXC_ERROR;
504 END IF;
505
506 end if;
507
508 --3. getLoanInvoices (should return newly created documents)
509 -- pass payoff_date = null to avoid recalculating payoff amounts
510 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting loan invoices');
511 lns_payoff_pub.getLoanInvoices(p_api_version => 1
512 ,p_init_msg_list => p_init_msg_list
513 ,p_loan_ID => p_loan_id
514 ,p_payoff_date => null
515 ,x_invoices_tbl => l_invoices_tbl
516 ,x_return_status => l_return_status
517 ,x_msg_count => l_msg_count
518 ,x_msg_data => l_msg_data);
519
520 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
521 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVOICE_SUMMARY_ERROR');
522 FND_MSG_PUB.Add;
523 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
524 RAISE FND_API.G_EXC_ERROR;
525 END IF;
526
527 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - # of unpaid invoices to process = ' || l_invoices_tbl.count);
528
529 --4. check if sum of receipts covers the remaining loan amount
530 -- if we need to do multi-currency, the receipt amounts should be
531 -- converted to loan_currency before making comparison
532 -- check if any of the receipts have been applied to any of the invoices
533 for k in 1..l_invoices_tbl.count
534 loop
535 l_total_payoff_amount := l_total_payoff_amount + l_invoices_tbl(k).REMAINING_AMOUNT;
536 end loop;
537
538 for j in 1..l_cash_receipt_ids.count
539 loop
540
541 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - receipt currency: ' || l_cash_receipt_ids(j).receipt_currency);
542 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - receipt amount: ' || l_cash_receipt_ids(j).receipt_amount);
543 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - xchg rate: ' || l_cash_receipt_ids(j).exchange_rate);
544
545 open c_receipt_number(l_cash_receipt_ids(j).cash_receipt_id);
546 fetch c_receipt_number into l_receipt_number, l_receipt_currency_orig;
547 close c_receipt_number ;
548
549 l_cash_receipt_ids(j).original_currency := l_receipt_currency_orig;
550 l_cash_receipt_ids(j).receipt_number := l_receipt_number;
551 l_total_receipts_amount := l_total_receipts_amount + l_cash_receipt_ids(j).receipt_amount;
552
553 end loop;
554
555 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - cash receipts to process: ' || l_cash_receipt_ids.count);
556 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - total receipts: ' || l_total_receipts_amount);
557 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - total payoff amount: ' || l_total_payoff_amount);
558
559 if l_total_receipts_amount < l_total_payoff_amount then
560 FND_MESSAGE.SET_NAME('LNS', 'LNS_PAYOFF_SHORT_CASH');
561 FND_MSG_PUB.Add;
562 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
563 RAISE FND_API.G_EXC_ERROR;
564 end if;
565
566 --5. apply cash
567 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - step 5');
568 for k in 1..l_invoices_tbl.count
569 loop
570 -- find the first cash receipt that has not been applied previously to the invoice
571 -- if the amount of the receipt is > invoice then move on
572 -- else find the next receipt that has not been applied previously to the invoice
573 j := 1;
574 l_invoice_amount_remaining := l_invoices_tbl(k).REMAINING_AMOUNT;
575 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' --------- next invoice ----------');
576 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - payment_schedule_id: ' || l_invoices_tbl(k).payment_schedule_id );
577 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - invoice remaining amount: ' || l_invoices_tbl(k).REMAINING_AMOUNT);
578
579 --l_receipt_amount_remaining := l_cash_receipt_ids(j).RECEIPT_AMOUNT;
580 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - receipt amount: # ' || j || ' =' ||l_cash_receipt_ids(j).RECEIPT_AMOUNT);
581
582 while l_invoices_tbl(k).REMAINING_AMOUNT > 0
583 loop
584 -- skip the receipts already exhausted
585 loop
586 exit when l_cash_receipt_ids(j).RECEIPT_AMOUNT > 0;
587 j := j + 1;
588 end loop;
589 l_receipt_amount_remaining := l_cash_receipt_ids(j).RECEIPT_AMOUNT;
590 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - receipt amount remaining: ' || j || '# =' ||l_receipt_amount_remaining);
591
592 Begin
593 select 'X'
594 into l_found
595 from ar_receivable_applications rap
596 where rap.cash_receipt_id = l_cash_receipt_ids(j).cash_receipt_id
597 and rap.applied_payment_schedule_id = l_invoices_tbl(k).payment_schedule_id
598 and rap.display = 'Y'
599 and rap.status = 'APP';
600 exception
601 when no_data_found then
602 null;
603 --when too_many_rows then
604 -- FND_MESSAGE.set_name ('AR', 'AR_RW_PAID_INVOICE_TWICE' );
605 -- APP_EXCEPTION.raise_exception;
606 end;
607
608 if l_found = 'X' then
609 if j = l_cash_receipt_ids.count then
610 -- we have gone thru all the cash receipts and there is not enough
611 -- open receipts in the list to cover this invoice
612 FND_MESSAGE.SET_NAME('LNS', 'LNS_ALL_RECEIPTS_EXHAUSTED');
613 FND_MSG_PUB.Add;
614 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
615 RAISE FND_API.G_EXC_ERROR;
616 end if;
617
618 else
619 -- receipt has not been previously applied to invoice
620 -- determine amount to apply
621 if l_receipt_amount_remaining > l_invoice_amount_remaining then
622 l_amount_to_apply := l_invoice_amount_remaining;
623 else
624 l_amount_to_apply := l_receipt_amount_remaining;
625 end if;
626
627 if l_cash_receipt_ids(j).original_currency = l_cash_receipt_ids(j).receipt_currency then
628 l_trans_to_receipt_rate := null;
629 l_receipt_amount_from := null;
630 else
631 logMessage(FND_LOG.LEVEL_PROCEDURE, 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);
632 if l_cash_receipt_ids(j).exchange_rate is not null then
633 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - using receipt exchange rate ' || l_cash_receipt_ids(j).exchange_rate);
634 l_trans_to_receipt_rate := l_cash_receipt_ids(j).exchange_rate;
635 else
636 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - using loan exchange rate ' || l_loan_exchange_rate);
637 l_trans_to_receipt_rate := l_loan_exchange_rate;
638 end if;
639 l_receipt_amount_from := l_amount_to_apply * l_trans_to_receipt_rate;
640 end if;
641
642 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - applying cash : ' || l_amount_to_apply);
643 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - applying cash from: ' || l_receipt_amount_from);
644 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - exchange rate: ' || l_trans_to_receipt_rate);
645
646 lns_payoff_pub.apply_receipt(p_cash_receipt_id => l_cash_receipt_ids(j).CASH_RECEIPT_ID
647 ,p_payment_schedule_id => l_invoices_tbl(k).payment_schedule_id
648 ,p_apply_amount => l_amount_to_apply -- in loan currency
649 ,p_apply_date => p_payoff_date
650 ,p_apply_amount_from => l_receipt_amount_from -- in receipt currency
651 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
652 ,x_return_status => l_return_status
653 ,x_msg_count => l_msg_count
654 ,x_msg_data => l_msg_data);
655
656 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - apply cash status: ' || l_return_status);
657 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
658 FND_MESSAGE.SET_NAME('LNS', 'LNS_APPLY_CASH_ERROR');
659 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RECEIPT_NUMBER');
660 FND_MESSAGE.SET_TOKEN('VALUE', l_cash_receipt_ids(j).receipt_number);
661 FND_MSG_PUB.Add;
662 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 l_invoice_amount_remaining := l_invoice_amount_remaining - l_amount_to_apply;
667 l_receipt_amount_remaining := l_receipt_amount_remaining - l_amount_to_apply;
668 l_invoices_tbl(k).REMAINING_AMOUNT := l_invoices_tbl(k).REMAINING_AMOUNT - l_amount_to_apply;
669 l_cash_receipt_ids(j).RECEIPT_AMOUNT := l_cash_receipt_ids(j).RECEIPT_AMOUNT - l_amount_to_apply;
670 l_amount_to_apply := 0;
671
672 l_found := null;
673 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_invoice_amount_remaining : ' || l_invoice_amount_remaining);
674 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_receipt_amount_remaining : ' || l_receipt_amount_remaining);
675 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_invoices_tbl(k).REMAINING_AMOUNT : ' || l_invoices_tbl(k).REMAINING_AMOUNT);
676 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_cash_receipt_ids(j).RECEIPT_AMOUNT : ' || l_cash_receipt_ids(j).RECEIPT_AMOUNT);
677 end if;
678 j := j + 1;
679
680 end loop;
681
682 end loop;
683 -- end of step 5
684
685 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calling LNS_BILLING_BATCH_PUB.processPaidLoans');
686 LNS_BILLING_BATCH_PUB.PROCESS_PAID_LOANS(P_API_VERSION => 1.0
687 ,P_INIT_MSG_LIST => FND_API.G_FALSE
688 ,P_COMMIT => FND_API.G_FALSE
689 ,P_VALIDATION_LEVEL => 100
690 ,P_LOAN_ID => p_loan_id
691 ,P_PAYOFF_DATE => p_payoff_date
692 ,x_return_status => l_return_status
693 ,x_msg_count => l_msg_count
694 ,x_msg_data => l_msg_data);
695 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - LNS_BILLING_BATCH_PUB.processPaidLoans return status: ' || l_return_status);
696 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
697 COMMIT WORK;
698 else
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701
702
703 -- one last check on the final balance before we mark the loan as
704 -- PAID OFF
705 /*
706 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Getting final balance');
707 begin
708 -- changes as per scherkas 11-16-2005
709 select total_principal_balance into l_final_balance
710 from LNS_PAYMENTS_SUMMARY_V
711 where loan_id = p_loan_id;
712 Exception
713 when no_data_found then
714 null;
715 end;
716 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - final balance: ' || l_final_balance);
717
718 if l_final_balance = 0 then
719 open c_loans_obj_vers(p_loan_id);
720 fetch c_loans_obj_vers into l_object_version;
721 close c_loans_obj_vers;
722
723 --6. if success then update loan_stats = 'PAIDOFF'
724 --LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - get_object_version' || l_object_version);
725 l_loan_header_rec.loan_id := p_loan_id;
726 l_loan_header_rec.LOAN_STATUS := 'PAIDOFF';
727 l_loan_header_rec.SECONDARY_STATUS := null;
728
729
730 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Updating loan header info w following values:');
731 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - LOAN_STATUS: ' || l_loan_header_rec.LOAN_STATUS);
732 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - get_object_version' || l_object_version);
733 --l_loan_header_rec.object_version := l_object_version;
734
735 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_object_version,
736 P_LOAN_HEADER_REC => l_loan_header_rec,
737 P_INIT_MSG_LIST => FND_API.G_FALSE,
738 X_RETURN_STATUS => l_return_status,
739 X_MSG_COUNT => l_msg_count,
740 X_MSG_DATA => l_msg_data);
741 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
742 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPDATE_HEADER_ERROR');
743 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
744 RAISE FND_API.G_EXC_ERROR;
745
746 update lns_fee_schedules
747 set billed_flag = 'Y'
748 where loan_id = p_loan_id
749 and active_flag = 'Y'
750 and billed_flag = 'N'
751 and object_version_number = object_version_number + 1;
752
753 Else
754 --FND_MESSAGE.SET_NAME('LNS', 'LNS_PAYOFF_SUCCESS');
755 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - updating fee assignments');
756 -- end date the active fees on the loan as FDD section 4.1.3
757 update lns_fee_assignments
758 set end_date_active = p_payoff_date
759 where loan_id = p_loan_id
760 and (end_date_active is null OR end_date_active > p_payoff_date);
761
762 COMMIT WORK;
763
764 END IF;
765
766 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - header update status: ' || l_return_status);
767
768 else
769 FND_MESSAGE.SET_NAME('LNS', 'LNS_FINAL_BALANCE_ERROR');
770 FND_MSG_PUB.Add;
771 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
772 RAISE FND_API.G_EXC_ERROR;
773 end if;
774 */
775 -- ---------------------------------------------------------------------
776 -- End of API body
777 --
778
779 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
780
781 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
782
783 EXCEPTION
784 WHEN FND_API.G_EXC_ERROR THEN
785 ROLLBACK TO processPayoff;
786 x_return_status := FND_API.G_RET_STS_ERROR;
787 x_msg_count := l_msg_count;
788 x_msg_data := l_msg_data;
789 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
790 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
791
792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
793 ROLLBACK TO processPayoff;
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 x_msg_count := l_msg_count;
796 x_msg_data := l_msg_data;
797 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
798 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
799
800 WHEN OTHERS THEN
801 ROLLBACK TO processPayoff;
802 x_return_status := FND_API.G_RET_STS_ERROR;
803 x_msg_count := l_msg_count;
804 x_msg_data := l_msg_data;
805 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
806 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
807
808 end processPayoff;
809
810
811 /*=========================================================================
812 || PUBLIC PROCEDURE getLoanInvoices
813 ||
814 || DESCRIPTION
815 ||
816 || Overview: this function will return the invoices with a remaining balance
817 || for the loan
818 ||
819 || Parameter: p_loan_id = loan id
820 || p_payoff_date = include to be created payoff documents
821 || x_invoices_tbl = table of invoices
822 ||
823 || Source Tables: NA
824 ||
825 || Target Tables: NA
826 ||
827 || Return value:
828 ||
829 || KNOWN ISSUES
830 ||
831 || NOTES
832 ||
833 || MODIFICATION HISTORY
834 || Date Author Description of Changes
835 || 10/06/2004 1:51PM raverma Created
836 || 1/28/2005 raverma add late Fees processing
837 *=======================================================================*/
838 procedure getLoanInvoices(p_api_version IN NUMBER
839 ,p_init_msg_list IN VARCHAR2
840 ,p_loan_id in number
841 ,p_payoff_date in date
842 ,x_invoices_tbl OUT NOCOPY LNS_PAYOFF_PUB.INVOICE_DETAILS_TBL
843 ,x_return_status OUT NOCOPY VARCHAR2
844 ,x_msg_count OUT NOCOPY NUMBER
845 ,x_msg_data OUT NOCOPY VARCHAR2)
846
847 is
848
849 cursor c_loanInvoices(p_loan_id number)
850 is
851 select ps.customer_trx_id
852 ,payment_schedule_id
853 ,payment_number
854 ,trx_number
855 ,tty.name
856 ,amount_due_remaining
857 ,am.due_date
858 ,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
859 from lns_amortization_scheds am
860 ,ar_payment_schedules ps
861 ,ra_cust_trx_types tty
862 where (am.principal_trx_id = ps.customer_trx_id OR
863 am.interest_trx_id = ps.customer_trx_id OR
864 am.fee_trx_id = ps.customer_trx_id) and
865 ps.cust_trx_type_id = tty.cust_trx_type_id and
866 ps.amount_due_remaining > 0 and
867 am.reamortization_amount is null and
868 am.reversed_flag <> 'Y' and
869 am.loan_id = p_loan_id
870 order by payment_number, line_type;
871
872 cursor c_loanInfo(p_loan_id number) is
873 select loan_number
874 ,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
875 from lns_loan_headers
876 where loan_id = p_loan_id;
877
878 cursor c_lastInterestAmount(p_loan_id number)
879 is
880 select interest_amount
881 from lns_amortization_scheds
882 where loan_id = p_loan_id
883 and reversed_flag <> 'Y'
884 and reamortization_amount is null
885 and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
886
887 cursor c_fees(p_loan_id number)
888 is
889 select sched.fee_amount
890 ,fees.fee_name
891 ,sched.fee_installment
892 from lns_fee_schedules sched
893 ,lns_fees fees
894 where sched.loan_id = p_loan_id
895 and sched.fee_id = fees.fee_id
896 and sched.active_flag = 'Y'
897 and sched.billed_flag = 'N';
898
899 l_api_name varchar2(25);
900 l_api_version_number number;
901 l_return_status VARCHAR2(1);
902 l_msg_count NUMBER;
903 l_msg_data VARCHAR2(32767);
904
905 l_invoices_tbl LNS_PAYOFF_PUB.INVOICE_DETAILS_TBL;
906 l_payoff_tbl2 LNS_FINANCIALS.PAYOFF_TBL2;
907 i number;
908 l_cust_trx_id number;
909 l_payment_schedule_id number;
910 l_invoice_number varchar2(60);
911 l_installment number;
912 l_trans_type varchar2(20);
913 l_remaining_amt number;
914 l_due_date date;
915 l_purpose varchar2(30);
916 l_billed_flag varchar2(1);
917 l_document_type varchar2(20);
918 l_last_interest number;
919 l_credit_name varchar2(30);
920 l_fee_amount number;
921 l_fee_name varchar2(50);
922 l_fee_installment number;
923
924 begin
925
926 l_api_name := 'getLoanInvoices';
927 l_api_version_number := 1;
928 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
929
930 -- Standard Start of API savepoint
931 SAVEPOINT getLoanInvoices;
932
933 -- Standard call to check for call compatibility.
934 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
935 l_api_name, G_PKG_NAME)
936 THEN
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END IF;
939
940 -- Initialize message list IF p_init_msg_list is set to TRUE.
941 IF FND_API.to_Boolean(p_init_msg_list) THEN
942 FND_MSG_PUB.initialize;
943 END IF;
944
945 -- Initialize API return status to SUCCESS
946 x_return_status := FND_API.G_RET_STS_SUCCESS;
947
948 --
949 -- Api body
950 -- --------------------------------------------------------------------
951 i := 0;
952
953 open c_loanInvoices(p_loan_id);
954 LOOP
955 i := i + 1;
956 FETCH c_loanInvoices INTO
957 l_cust_trx_id
958 ,l_payment_schedule_id
959 ,l_installment
960 ,l_invoice_number
961 ,l_trans_type
962 ,l_remaining_amt
963 ,l_due_date
964 ,l_purpose;
965
966 EXIT WHEN c_loanInvoices%NOTFOUND;
967
968 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - installment #: ' || l_installment);
969 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_cust_trx_id: ' || l_cust_trx_id);
970 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_remaining_amt: ' || l_remaining_amt);
971 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_due_date: ' || l_due_date);
972 l_invoices_tbl(i).CUST_TRX_ID := l_cust_trx_id;
973 l_invoices_tbl(i).PAYMENT_SCHEDULE_ID:= l_payment_schedule_id;
974 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
975 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
976 l_invoices_tbl(i).TRANSACTION_TYPE := l_trans_type;
977 l_invoices_tbl(i).REMAINING_AMOUNT := l_remaining_amt;
978 l_invoices_tbl(i).DUE_DATE := l_due_date;
979 l_invoices_tbl(i).PURPOSE := l_purpose;
980 l_invoices_tbl(i).BILLED_FLAG := 'Y';
981
982 END LOOP;
983 close c_loanInvoices;
984 -- get the additional records "to be created"
985 if p_payoff_date is not null then
986 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting addition TO BE CREATED documents');
987 --dbms_output.put_line('calculating payoof amounts');
988 lns_financials.calculatePayoff(p_api_version => 1.0
989 ,p_init_msg_list => p_init_msg_list
990 ,p_loan_id => p_loan_id
991 ,p_payoff_date => p_payoff_date
992 ,x_payoff_tbl => l_payoff_tbl2
993 ,x_return_status => l_return_status
994 ,x_msg_count => l_msg_count
995 ,x_msg_data => l_msg_data);
996 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
997 --FND_MESSAGE.SET_NAME('LNS', 'LNS_UPDATE_HEADER_ERROR');
998 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
999 RAISE FND_API.G_EXC_ERROR;
1000 END IF;
1001
1002 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - payoff tbl count ' || l_payoff_tbl2.count);
1003
1004 open c_loanInfo(p_loan_id);
1005 fetch c_loanInfo into l_invoice_number, l_installment;
1006 close c_loanInfo;
1007
1008 for k in 1..l_payoff_tbl2.count
1009 loop
1010
1011 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || l_payoff_tbl2(k).payoff_purpose);
1012 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || l_payoff_tbl2(k).unbilled_amount);
1013
1014 i := l_invoices_tbl.count;
1015 if l_payoff_tbl2(k).unbilled_amount > 0 and l_payoff_tbl2(k).PAYOFF_PURPOSE <> 'FEE' then
1016 i := i + 1;
1017 l_invoices_tbl(i).CUST_TRX_ID := null;
1018 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1019 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1020 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName(l_payoff_tbl2(k).PAYOFF_PURPOSE);
1021 l_invoices_tbl(i).REMAINING_AMOUNT := l_payoff_tbl2(k).unbilled_amount;
1022 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1023 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1024 l_invoices_tbl(i).BILLED_FLAG := 'N';
1025
1026
1027 elsif l_payoff_tbl2(k).unbilled_amount < 0 and l_payoff_tbl2(k).PAYOFF_PURPOSE = 'INT' then
1028
1029 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting last interest documents ');
1030 i := l_invoices_tbl.count;
1031 -- find the last interest installment we will create a credit memo in that amount
1032 -- and we will create an invoice of INT type with
1033 open c_lastInterestAmount(p_loan_id);
1034 fetch c_lastInterestAmount into l_last_interest;
1035 close c_lastInterestAmount;
1036
1037 select meaning into l_credit_name
1038 from ar_lookups
1039 where lookup_type = 'INV/CM'
1040 and lookup_code = 'CM';
1041
1042 -- interest document (credit)
1043 i := i + 1;
1044 l_invoices_tbl(i).CUST_TRX_ID := null;
1045 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1046 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1047 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1048 l_invoices_tbl(i).TRANSACTION_TYPE := l_credit_name;
1049 l_invoices_tbl(i).REMAINING_AMOUNT := - l_last_interest;
1050 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1051 l_invoices_tbl(i).BILLED_FLAG := 'N';
1052
1053 if l_last_interest + l_payoff_tbl2(k).unbilled_amount > 0 then
1054 -- new interest document
1055 i := i + 1;
1056 l_invoices_tbl(i).CUST_TRX_ID := null;
1057 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_installment;
1058 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1059 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1060 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName(l_payoff_tbl2(k).PAYOFF_PURPOSE);
1061 l_invoices_tbl(i).REMAINING_AMOUNT := l_last_interest + l_payoff_tbl2(k).unbilled_amount;
1062 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', l_payoff_tbl2(k).PAYOFF_PURPOSE);
1063 l_invoices_tbl(i).BILLED_FLAG := 'N';
1064 end if;
1065
1066 end if;
1067
1068 end loop;
1069
1070 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - getting last fees ');
1071 i := l_invoices_tbl.count;
1072 open c_fees(p_loan_id);
1073 loop
1074 i := i + 1;
1075 fetch c_fees into
1076 l_fee_amount, l_fee_name, l_fee_installment;
1077
1078 exit when c_fees%notfound;
1079 l_invoices_tbl(i).CUST_TRX_ID := null;
1080 l_invoices_tbl(i).INSTALLMENT_NUMBER := l_fee_installment;
1081 l_invoices_tbl(i).INVOICE_NUMBER := l_invoice_number;
1082 l_invoices_tbl(i).DUE_DATE := p_payoff_date;
1083 l_invoices_tbl(i).TRANSACTION_TYPE := lns_utility_pub.getDocumentName('FEE');
1084 l_invoices_tbl(i).REMAINING_AMOUNT := l_fee_amount;
1085 l_invoices_tbl(i).PURPOSE := lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', 'FEE');
1086 l_invoices_tbl(i).BILLED_FLAG := 'N';
1087 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - fee found');
1088 end loop;
1089 close c_fees;
1090
1091 end if;
1092
1093 x_invoices_tbl := l_invoices_tbl;
1094 -- --------------------------------------------------------------------
1095 -- End of API body
1096 --
1097
1098 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1099
1100 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1101
1102 EXCEPTION
1103 WHEN FND_API.G_EXC_ERROR THEN
1104 ROLLBACK TO getLoanInvoices;
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106 x_msg_count := l_msg_count;
1107 x_msg_data := l_msg_data;
1108 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1109 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1110
1111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1112 ROLLBACK TO getLoanInvoices;
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114 x_msg_count := l_msg_count;
1115 x_msg_data := l_msg_data;
1116 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1117 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1118
1119 WHEN OTHERS THEN
1120 ROLLBACK TO getLoanInvoices;
1121 x_return_status := FND_API.G_RET_STS_ERROR;
1122 x_msg_count := l_msg_count;
1123 x_msg_data := l_msg_data;
1124 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1125 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1126
1127 end getLoanInvoices;
1128
1129 /*========================================================================
1130 | PRIVATE PROCEDURE APPLY_RECEIPT
1131 |
1132 | DESCRIPTION
1133 | This procedure applies cash receipt to invoice.
1134 |
1135 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1136 | CREATE_SINGLE_OFFCYCLE_BILL
1137 |
1138 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1139 | LogMessage
1140 |
1141 | PARAMETERS
1142 | P_CASH_RECEIPT_ID IN Cash receipt to apply
1143 | P_TRX_ID IN Apply receipt to this trx
1144 | P_TRX_LINE_ID IN Apply receipt to this trx line
1145 | P_APPLY_AMOUNT IN Apply amount
1146 |
1147 | KNOWN ISSUES
1148 | None
1149 |
1150 | NOTES
1151 | Any interesting aspect of the code in the package body which needs
1152 | to be stated.
1153 |
1154 | MODIFICATION HISTORY
1155 | Date Author Description of Changes
1156 | 01-01-2004 scherkas Created
1157 |
1158 *=======================================================================*/
1159 PROCEDURE APPLY_RECEIPT(P_CASH_RECEIPT_ID IN NUMBER
1160 ,P_PAYMENT_SCHEDULE_ID IN NUMBER
1161 ,P_APPLY_AMOUNT IN NUMBER
1162 ,P_APPLY_DATE IN DATE
1163 ,p_apply_amount_from IN NUMBER
1164 ,p_trans_to_receipt_rate IN NUMBER
1165 ,x_return_status OUT NOCOPY VARCHAR2
1166 ,x_msg_count OUT NOCOPY NUMBER
1167 ,x_msg_data OUT NOCOPY VARCHAR2)
1168
1169 IS
1170
1171 /*-----------------------------------------------------------------------+
1172 | Local Variable Declarations and initializations |
1173 +-----------------------------------------------------------------------*/
1174
1175 l_api_name VARCHAR2(30);
1176 l_return_status VARCHAR2(1);
1177 l_msg_count NUMBER;
1178 l_msg_data VARCHAR2(32767);
1179 l_receipt_rem_amount number;
1180 g_day_togl_after_dd number;
1181 l_apply_date date;
1182 l_due_date date;
1183
1184 /*-----------------------------------------------------------------------+
1185 | Cursor Declarations |
1186 +-----------------------------------------------------------------------*/
1187
1188 CURSOR receipt_cur(P_RECEIPT_ID number) IS
1189 select ABS(AMOUNT_DUE_REMAINING)
1190 from ar_payment_schedules
1191 where CASH_RECEIPT_ID = P_RECEIPT_ID
1192 and status = 'OP'
1193 and class = 'PMT';
1194
1195 cursor c_due_date(p_payment_schedule_id number) is
1196 select due_date
1197 from ar_payment_schedules
1198 where payment_schedule_id = p_payment_schedule_id;
1199
1200
1201 BEGIN
1202
1203 l_api_name := 'apply_receipt';
1204 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1205
1206 -- Standard Start of API savepoint
1207 SAVEPOINT APPLY_RECEIPT;
1208
1209 -- Initialize API return status to SUCCESS
1210 x_return_status := FND_API.G_RET_STS_SUCCESS;
1211
1212 --
1213 -- Api body
1214 -- --------------------------------------------------------------------
1215 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Applying cash receipt ' || P_CASH_RECEIPT_ID);
1216 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_PS_ID: ' || P_PAYMENT_SCHEDULE_ID);
1217 --LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_TRX_LINE_ID: ' || P_TRX_LINE_ID);
1218 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_APPLY_AMOUNT: ' || P_APPLY_AMOUNT);
1219 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' P_APPLY_AMOUNT_FROM: ' || P_APPLY_AMOUNT_FROM);
1220 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' p_trans_to_receipt_rate: ' || p_trans_to_receipt_rate);
1221
1222 /* verify input data */
1223 if P_CASH_RECEIPT_ID is null then
1224 FND_MESSAGE.SET_NAME('LNS', 'LNS_CR_NOT_SET');
1225 FND_MSG_PUB.Add;
1226 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1227 RAISE FND_API.G_EXC_ERROR;
1228 end if;
1229
1230 if P_APPLY_AMOUNT is null or P_APPLY_AMOUNT <= 0 then
1231 FND_MESSAGE.SET_NAME('LNS', 'LNS_AMOUNT_NOT_SET');
1232 FND_MSG_PUB.Add;
1233 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1234 RAISE FND_API.G_EXC_ERROR;
1235 end if;
1236
1237 /* verifying requested qpply amount */
1238 open receipt_cur(P_CASH_RECEIPT_ID);
1239 fetch receipt_cur into l_receipt_rem_amount;
1240
1241 if receipt_cur%NOTFOUND then
1242 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_CR_FOUND');
1243 FND_MSG_PUB.Add;
1244 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1245 RAISE FND_API.G_EXC_ERROR;
1246 end if;
1247
1248 close receipt_cur;
1249 /*
1250 if l_receipt_rem_amount < P_APPLY_AMOUNT then
1251 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_CR_FUNDS');
1252 FND_MSG_PUB.Add;
1253 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1254 RAISE FND_API.G_EXC_ERROR;
1255 end if;
1256 */
1257 select
1258 DAYS_TOGL_AFTER_DUE_DATE
1259 into g_day_togl_after_dd
1260 FROM LNS_SYSTEM_OPTIONS
1261 WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID() ;
1262
1263 open c_due_date(P_PAYMENT_SCHEDULE_ID);
1264 fetch c_due_date into l_due_date;
1265 close c_due_date;
1266
1267 if l_due_date > p_apply_date then
1268 l_apply_date := l_due_Date;
1269 else
1270 l_apply_date := p_apply_date;
1271 end if;
1272 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' due_date: ' || l_due_date);
1273 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' apply_date: ' || l_apply_date);
1274
1275 /* Applying cash receipt to invoice */
1276 AR_RECEIPT_API_PUB.APPLY(P_API_VERSION => 1.0
1277 ,P_INIT_MSG_LIST => FND_API.G_FALSE
1278 ,P_COMMIT => FND_API.G_FALSE
1279 ,X_RETURN_STATUS => L_RETURN_STATUS
1280 ,X_MSG_COUNT => L_MSG_COUNT
1281 ,X_MSG_DATA => L_MSG_DATA
1282 ,p_cash_receipt_id => P_CASH_RECEIPT_ID
1283 ,p_applied_payment_schedule_id => P_PAYMENT_SCHEDULE_ID
1284 ,p_apply_date => l_apply_date
1285 ,p_apply_gl_date => l_apply_date + g_day_togl_after_dd
1286 ,p_amount_applied => P_APPLY_AMOUNT
1287 ,p_amount_applied_from => p_apply_amount_from
1288 ,p_trans_to_receipt_rate => p_trans_to_receipt_rate);
1289
1290 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' return_status: ' || l_return_status);
1291 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' msg_data: ' || substr(l_msg_data,1,225));
1292
1293 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1294 FND_MESSAGE.SET_NAME('LNS', 'LNS_APPL_CR_FAIL');
1295 FND_MSG_PUB.Add;
1296 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1297 RAISE FND_API.G_EXC_ERROR;
1298 ELSE
1299 null;
1300 --LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully applied cash receipt to trx ' || P_TRX_ID || ' line ' || P_TRX_LINE_ID);
1301 END IF;
1302 -- END OF BODY OF API
1303
1304 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1305
1306 EXCEPTION
1307 WHEN FND_API.G_EXC_ERROR THEN
1308 ROLLBACK TO APPLY_RECEIPT;
1309 x_return_status := FND_API.G_RET_STS_ERROR;
1310 x_msg_count := l_msg_count;
1311 x_msg_data := l_msg_data;
1312 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1313 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1314
1315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1316 ROLLBACK TO APPLY_RECEIPT;
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1318 x_msg_count := l_msg_count;
1319 x_msg_data := l_msg_data;
1320 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1321 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1322
1323 WHEN OTHERS THEN
1324 ROLLBACK TO APPLY_RECEIPT;
1325 x_return_status := FND_API.G_RET_STS_ERROR;
1326 x_msg_count := l_msg_count;
1327 x_msg_data := l_msg_data;
1328 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1329 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1330 END;
1331
1332 /*=========================================================================
1333 || PUBLIC FUNCTION getConvertedReceiptAmount
1334 ||
1335 || DESCRIPTION
1336 ||
1337 || Overview: This function returns the receipt balance amount in loan currency
1338 || for the loan
1339 ||
1340 || Parameter: p_receipt_id = cash_receipt_id
1341 || p_loan_id = loan id
1342 || Source Tables: NA
1343 ||
1344 || Target Tables: NA
1345 ||
1346 || Return value: Converted receipt balance amount in loan currency
1347 ||
1348 || KNOWN ISSUES
1349 ||
1350 || NOTES
1351 ||
1352 || MODIFICATION HISTORY
1353 || Date Author Description of Changes
1354 || 10/20/2004 karamach Created
1355 *=======================================================================*/
1356 FUNCTION getConvertedReceiptAmount(p_receipt_id in number, p_loan_id in number) return NUMBER
1357 IS
1358
1359 Cursor getReceiptCurrencyBalance(p_recpt_id number) is
1360 select CR.CURRENCY_CODE RECEIPT_CURRENCY_CODE,
1361 (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,
1362 CR.EXCHANGE_RATE_TYPE,
1363 CR.EXCHANGE_DATE,
1364 CR.EXCHANGE_RATE
1365 from AR_CASH_RECEIPTS CR
1366 where CR.cash_receipt_id = p_recpt_id;
1367
1368 Cursor getLoanCurrencyConversion(p_loanId number) is
1369 select loan.loan_currency LOAN_CURRENCY_CODE
1370 from LNS_LOAN_HEADERS_ALL loan
1371 where loan.loan_id = p_loanId;
1372
1373 --receipt_currency_code varchar2(3);
1374 --loan_currency_code varchar2(3);
1375 receipt_currency_amount number;
1376 loan_currency_amount number;
1377
1378 l_loan_currency_code varchar2(15);
1379 l_loan_exchange_rate_type varchar2(30);
1380 l_loan_exchange_rate number;
1381 l_loan_exchange_date date;
1382 l_sob_currency_code varchar2(15);
1383
1384 l_rec_currency_code varchar2(15);
1385 l_rec_exchange_rate_type varchar2(30);
1386 l_rec_exchange_date date;
1387 l_rec_exchange_rate number;
1388
1389 l_return number;
1390
1391 cursor c_loan_exchange_info(p_loan_id number) is
1392 select lnh.exchange_rate_type
1393 ,lnh.exchange_rate
1394 ,lnh.exchange_date
1395 ,lnh.loan_currency
1396 from lns_loan_headers lnh
1397 where loan_id = p_loan_id;
1398
1399 cursor c_sob_currency is
1400 SELECT sb.currency_code
1401 FROM lns_system_options so,
1402 gl_sets_of_books sb
1403 WHERE sb.set_of_books_id = so.set_of_books_id;
1404
1405 BEGIN
1406
1407 open getReceiptCurrencyBalance(p_receipt_id);
1408 --fetch getReceiptCurrencyBalance into receipt_currency_code,receipt_currency_amount;
1409 fetch getReceiptCurrencyBalance into
1410 l_rec_currency_code
1411 ,receipt_currency_amount
1412 ,l_rec_exchange_rate_type
1413 ,l_rec_exchange_date
1414 ,l_rec_exchange_rate;
1415 close getReceiptCurrencyBalance;
1416
1417 open c_loan_exchange_info(p_loan_id);
1418 fetch c_loan_exchange_info into
1419 l_loan_exchange_rate_type
1420 ,l_loan_exchange_rate
1421 ,l_loan_exchange_date
1422 ,l_loan_currency_code;
1423 close c_loan_exchange_info;
1424
1425 if (l_rec_currency_code is null or receipt_currency_amount is null or l_loan_currency_code is null) then
1426 return 0;
1427 elsif (l_rec_currency_code = l_loan_currency_code) then
1428 return receipt_currency_amount;
1429 else -- rec currency <> loan currency
1430 open c_sob_currency;
1431 fetch c_sob_currency into l_sob_currency_code;
1432 close c_sob_currency;
1433
1434 if l_rec_currency_code = l_sob_currency_code then
1435 l_return := receipt_currency_amount / l_loan_exchange_rate;
1436
1437 else -- rec_currency <> loan_currency <> sob currency
1438 -- this is not valid unless user enters the conversion rate/date/type on payoff UI
1439 -- that is the approach AR takes
1440 l_return := 0;
1441 end if;
1442
1443 return l_return;
1444
1445 end if;
1446
1447 END getConvertedReceiptAmount;
1448
1449 END LNS_PAYOFF_PUB;