[Home] [Help]
PACKAGE BODY: APPS.OKL_LCKBX_CSH_APP_PVT
Source
1 PACKAGE BODY okl_lckbx_csh_app_pvt AS
2 /* $Header: OKLRLBXB.pls 120.29.12010000.4 2008/10/24 04:03:04 nikshah ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 l_module VARCHAR2 (40) := 'LEASE.RECEIVABLES.LOCKBOX';
5 l_debug_enabled CONSTANT VARCHAR2 (10)
6 := okl_debug_pub.check_log_enabled;
7 l_level_statement NUMBER;
8 --akrangan added for debug logging begin
9 g_module VARCHAR2 (255)
10 := 'okl.am.plsql.okl_lckbx_csh_app_pvt';
11 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
12 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
13 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
14 -- akrangan added for debug feature start
15 is_debug_exception_on BOOLEAN
16 := okl_debug_pub.check_log_on (g_module, g_level_exception);
17 is_debug_procedure_on BOOLEAN
18 := okl_debug_pub.check_log_on (g_module, g_level_procedure);
19 is_debug_statement_on BOOLEAN
20 := okl_debug_pub.check_log_on (g_module, g_level_statement);
21
22 -- akrangan added for debug feature end
23 --akrangan added for debug logging end
24
25 -- End of wraper code generated automatically by Debug code generator
26 ---------------------------------------------------------------------------
27 -- FURTURE ENHANCEMENTS
28 -- 1/ Include auto associate code checking when customer_number not specified
29 -- 2/ Org_id
30 ---------------------------------------------------------------------------
31 ---------------------------------------------------------------------------
32 -- Function get_req_recs
33 ---------------------------------------------------------------------------
34 FUNCTION get_rec_count (req_id NUMBER)
35 RETURN NUMBER IS
36 trans_count NUMBER DEFAULT NULL;
37 BEGIN
38 SELECT COUNT (*)
39 INTO trans_count
40 FROM ar_payments_interface_all
41 WHERE transmission_request_id = req_id;
42
43 RETURN trans_count;
44 END get_rec_count;
45
46 --asawanka added for llca start
47 PROCEDURE log_debug (p_message IN VARCHAR2) IS
48 BEGIN
49 IF (is_debug_statement_on) THEN
50 -- dbms_output.put_line('p_message = '||p_message);
51 okl_debug_pub.logmessage (p_message, l_level_statement, 'Y');
52 END IF;
53 END log_debug;
54
55 --asawanka added for llca end
56 FUNCTION valid_ar_reference (ar_inv_ref VARCHAR, p_org_id IN NUMBER)
57 RETURN BOOLEAN IS
58 inv_count NUMBER DEFAULT NULL;
59 BEGIN
60 --asawanka modified for lla start
61 --this function should return true if ar_inv_ref is a valid non okl ar invoice
62 SELECT COUNT (*)
63 INTO inv_count
64 FROM ra_customer_trx_all
65 WHERE trx_number = ar_inv_ref
66 AND org_id = p_org_id
67 AND interface_header_context <> 'OKL_CONTRACTS';
68
69 --asawanka modified for lla end
70 IF inv_count > 0 THEN
71 RETURN TRUE;
72 ELSE
73 RETURN FALSE;
74 END IF;
75 END valid_ar_reference;
76
77 --asawanka modified for llca start
78 --added function
79 FUNCTION get_precision (
80 p_currency_code IN VARCHAR2,
81 p_trans_req_id IN NUMBER,
82 p_record_type IN VARCHAR2,
83 p_column_type IN VARCHAR2
84 )
85 RETURN NUMBER IS
86 CURSOR c_get_format_flag (
87 cp_trans_req_id IN NUMBER,
88 cp_record_type IN VARCHAR2,
89 cp_column_type IN VARCHAR2
90 ) IS
91 SELECT NVL (fld.amount_format_lookup_code, 'N')
92 FROM ar_transmissions_all trans,
93 ar_trans_record_formats rec,
94 ar_trans_field_formats fld
95 WHERE trans.transmission_request_id = cp_trans_req_id
96 AND trans.requested_trans_format_id = rec.transmission_format_id
97 AND rec.record_type_lookup_code = cp_record_type
98 AND rec.record_format_id = fld.record_format_id
99 AND fld.field_type_lookup_code = cp_column_type;
100
101 CURSOR c_get_precision (cp_currency_code IN VARCHAR2) IS
102 SELECT NVL (PRECISION, 0)
103 FROM fnd_currencies_vl cur
104 WHERE cur.currency_code = cp_currency_code;
105
106 l_format_yn VARCHAR2 (10) := 'N';
107 l_precision NUMBER;
108 BEGIN
109 OPEN c_get_format_flag (p_trans_req_id, p_record_type, p_column_type);
110
111 FETCH c_get_format_flag
112 INTO l_format_yn;
113
114 CLOSE c_get_format_flag;
115
116 IF l_format_yn = 'Y' THEN
117 OPEN c_get_precision (p_currency_code);
118
119 FETCH c_get_precision
120 INTO l_precision;
121
122 CLOSE c_get_precision;
123
124 RETURN l_precision;
125 ELSE
126 RETURN 0;
127 END IF;
128 END get_precision;
129
130 FUNCTION is_valid_reference ( p_reference_type IN VARCHAR2,
131 p_reference IN VARCHAR2,
132 p_currency_code IN VARCHAR2,
133 p_trans_req_id IN NUMBER,
134 p_org_id IN NUMBER,
135 p_index IN NUMBER,
136 p_record_type IN VARCHAR2,
137 p_column_type_to IN VARCHAR2,
138 p_column_type_from IN VARCHAR2,
139 p_customer_num IN VARCHAR2,
140 p_amount_to IN NUMBER,
141 p_amount_from IN NUMBER,
142 x_currency_code OUT NOCOPY VARCHAR2,
143 x_amount_to OUT NOCOPY NUMBER,
144 x_amount_from OUT NOCOPY NUMBER,
145 x_precision_to OUT NOCOPY NUMBER,
146 x_precision_from OUT NOCOPY NUMBER)
147 RETURN BOOLEAN
148 IS
149
150 CURSOR c_valid_arinv (cp_invoice_number VARCHAR2,
151 cp_org_id NUMBER,
152 cp_customer_num VARCHAR2,
153 cp_currency_code VARCHAR2) IS
154 SELECT INVOICE_CURRENCY_CODE
155 FROM okl_rcpt_arinv_balances_uv
156 WHERE invoice_number = cp_invoice_number
157 --commented for bug 5391874
158 -- AND customer_account_number = cp_customer_num
159 AND org_id = cp_org_id
160 and INVOICE_CURRENCY_CODE = nvl(cp_currency_code,INVOICE_CURRENCY_CODE)
161 AND status = 'OP';
162
163 CURSOR c_valid_consinv (cp_cons_invoice_number VARCHAR2,
164 cp_org_id NUMBER,
165 cp_customer_num VARCHAR2,
166 cp_currency_code VARCHAR2) IS
167 SELECT CURRENCY_CODE
168 FROM okl_rcpt_consinv_balances_uv
169 WHERE consolidated_invoice_number = cp_cons_invoice_number
170 --commented for bug 5391874
171 -- AND customer_account_number = cp_customer_num
172 AND org_id = cp_org_id
173 and CURRENCY_CODE = nvl(cp_currency_code,CURRENCY_CODE)
174 AND status = 'OP';
175
176 CURSOR c_valid_contract(cp_contract_number VARCHAR2,
177 cp_org_id NUMBER,
178 cp_customer_num VARCHAR2,
179 cp_currency_code VARCHAR2) IS
180 SELECT CURRENCY_CODE
181 -- FROM okl_rcpt_consinv_balances_uv -- incorrect view .. bug 7018894
182 FROM okl_rcpt_cust_cont_balances_uv
183 WHERE contract_number = cp_contract_number
184 --commented for bug 5391874
185 -- AND customer_account_number = cp_customer_num
186 AND org_id = cp_org_id
187 and CURRENCY_CODE = nvl(cp_currency_code,CURRENCY_CODE)
188 AND status = 'OP';
189
190 l_invoice_currency_code RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE%TYPE;
191 l_precision NUMBER;
192 l_amount_to NUMBER;
193 l_amount_from NUMBER;
194 BEGIN
195 --Log the input parameters
196 log_debug('p_reference_type: ' || p_reference_type);
197 log_debug('p_reference: ' || p_reference);
198 log_debug('p_currency_code: ' || p_currency_code);
199 log_debug('p_trans_req_id: ' || p_trans_req_id);
200 log_debug('p_org_id: ' || p_org_id);
201 log_debug('p_record_type: ' || p_record_type);
202 log_debug('p_index: ' || p_index);
203 log_debug('p_column_type_to: ' || p_column_type_to);
204 log_debug('p_column_type_from: ' || p_column_type_from);
205 log_debug('p_customer_num: ' || p_customer_num);
206 log_debug('p_amount_to: ' || p_amount_to);
207 log_debug('p_amount_from: ' || p_amount_from);
208
209 --Check whether it is valid AR Invoice Number
210 --If not valid return false
211 IF p_reference_type = 'INVOICE' THEN
212 OPEN c_valid_arinv(p_reference, p_org_id, p_customer_num, p_currency_code);
213 FETCH c_valid_arinv INTO l_invoice_currency_code;
214 IF c_valid_arinv%NOTFOUND THEN
215 RETURN FALSE;
216 END IF;
217 CLOSE c_valid_arinv;
218 --Check whether it is valid consolidated Invoice Number
219 --If not valid return false
220 ELSIF p_reference_type = 'CONS-INVOICE' THEN
221 OPEN c_valid_consinv(p_reference, p_org_id, p_customer_num, p_currency_code);
222 FETCH c_valid_consinv INTO l_invoice_currency_code;
223 IF c_valid_consinv%NOTFOUND THEN
224 RETURN FALSE;
225 END IF;
226 CLOSE c_valid_consinv;
227 --Check whether it is valid contract Number
228 --If not valid return false
229 ELSIF p_reference_type = 'CONTRACT' THEN
230 OPEN c_valid_contract(p_reference, p_org_id, p_customer_num, p_currency_code);
231 FETCH c_valid_contract INTO l_invoice_currency_code;
232 IF c_valid_contract%NOTFOUND THEN
233 RETURN FALSE;
234 END IF;
235 CLOSE c_valid_contract;
236 END IF;
237
238 --Get new precision, new amount applied and new amount applied from
239 --based on invoice/cons invoice/contract currency we got from above cursor
240
241 x_currency_code := l_invoice_currency_code;
242
243 l_precision := get_precision (l_invoice_currency_code,
244 p_trans_req_id,
245 p_record_type,
246 p_column_type_to || ' ' || p_index);
247 l_amount_to := ROUND (p_amount_to / POWER (10, l_precision), l_precision);
248 x_precision_to := l_precision;
249 x_amount_to := l_amount_to;
250
251 l_precision := get_precision (l_invoice_currency_code,
252 p_trans_req_id,
253 p_record_type,
254 p_column_type_from || ' ' || p_index);
255 l_amount_from := ROUND (p_amount_from / POWER (10, l_precision), l_precision);
256 x_precision_from := l_precision;
257 x_amount_from := l_amount_from;
258
259 RETURN TRUE;
260
261 END is_valid_reference;
262
263 ---------------------------------------------------------------------------
264 --FUNCTION - To return whether we need to make Line level application/not
265 ---------------------------------------------------------------------------
266 FUNCTION get_line_level_app (p_arinv_id IN NUMBER, p_org_id IN NUMBER)
267 RETURN VARCHAR2 AS
268 CURSOR c_is_llevel_balance (p_inv_id NUMBER, p_org_id NUMBER) IS
269 SELECT SUM (amount_due_remaining) due
270 FROM ra_customer_trx_lines
271 WHERE customer_trx_id = p_inv_id
272 AND org_id = p_org_id
273 AND line_type = 'LINE';
274
275 CURSOR c_get_activity (p_inv_id NUMBER, p_org_id NUMBER) IS
276 SELECT arpt_sql_func_util.get_activity_flag
277 (ct.customer_trx_id,
278 ctt.accounting_affect_flag,
279 ct.complete_flag,
280 ctt.TYPE,
281 ct.initial_customer_trx_id,
282 ct.previous_customer_trx_id
283 ) activity
284 FROM ra_cust_trx_types ctt, ra_customer_trx ct
285 WHERE ct.customer_trx_id = p_inv_id
286 AND ct.org_id = p_org_id
287 AND ct.cust_trx_type_id = ctt.cust_trx_type_id;
288
289 l_return_flag VARCHAR2 (1) DEFAULT 'Y';
290 l_balance_exist VARCHAR (1) DEFAULT 'Y';
291 l_due NUMBER;
292 l_activity VARCHAR2 (1);
293 BEGIN
294 OPEN c_is_llevel_balance (p_arinv_id, p_org_id);
295
296 FETCH c_is_llevel_balance
297 INTO l_due;
298
299 CLOSE c_is_llevel_balance;
300
301 IF l_due IS NULL THEN
302 l_balance_exist := 'N';
303 END IF;
304
305 IF l_balance_exist = 'N' THEN
306 OPEN c_get_activity (p_arinv_id, p_org_id);
307
308 FETCH c_get_activity
309 INTO l_activity;
310
311 CLOSE c_get_activity;
312
313 IF l_activity = 'Y' THEN
314 l_return_flag := 'N';
315 END IF;
316 END IF;
317
318 RETURN l_return_flag;
319 END;
320
321 ---------------------------------------------------------------------------
322 -- PROCEDURE handle_auto_pay
323 ---------------------------------------------------------------------------
324 PROCEDURE handle_auto_pay (
325 p_api_version IN NUMBER,
326 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 p_trans_req_id IN ar_payments_interface.transmission_request_id%TYPE
331 ) IS
332 ---------------------------
333 -- DECLARE Local Variables
334 ---------------------------
335 l_lockbox_run_already VARCHAR (1) DEFAULT NULL;
336 --asawanka modified for lla start
337 -- removed default assignement to mo_global.current_org_id as current org id might not be set from AR side
338 -- we need to take org_id from interface records instead
339 l_org_id ar_payments_interface_all.org_id%TYPE;
340 --asawanka modified for lla end
341 l_status CONSTANT ar_payments_interface_all.status%TYPE
342 := 'AR_PLB_NEW_RECORD';
343 l_trans_req_id ar_payments_interface_all.transmission_request_id%TYPE
344 DEFAULT NULL;
345 l_transmission_id ar_payments_interface_all.transmission_id%TYPE
346 DEFAULT NULL;
347 l_transmission_record_id ar_payments_interface_all.transmission_record_id%TYPE
348 DEFAULT NULL;
349 l_last_transmission_record_id ar_payments_interface_all.transmission_record_id%TYPE
350 DEFAULT NULL;
351 l_overflow_sequence ar_payments_interface_all.overflow_sequence%TYPE
352 DEFAULT NULL;
353 l_lockbox_number ar_payments_interface_all.lockbox_number%TYPE
354 DEFAULT NULL;
355 l_batch_name ar_payments_interface_all.batch_name%TYPE
356 DEFAULT NULL;
357 l_transmission_format_id ar_transmission_formats.transmission_format_id%TYPE
358 DEFAULT NULL;
359 l_record_identifier ar_trans_record_formats.record_identifier%TYPE
360 DEFAULT NULL;
361 l_record_type_lookup_code ar_trans_record_formats.record_type_lookup_code%TYPE
362 DEFAULT NULL;
363 l_overflow_rec_indicator ar_trans_field_formats.overflow_rec_indicator%TYPE
364 DEFAULT NULL;
365 l_transmission_hdr ar_trans_record_formats.record_identifier%TYPE
366 DEFAULT NULL;
367 l_transmission_trl ar_trans_record_formats.record_identifier%TYPE
368 DEFAULT NULL;
369 l_payment ar_trans_record_formats.record_identifier%TYPE
370 DEFAULT NULL;
371 l_overflow ar_trans_record_formats.record_identifier%TYPE
372 DEFAULT NULL;
373 l_service ar_trans_record_formats.record_identifier%TYPE
374 DEFAULT NULL;
375 l_customer_number ar_payments_interface_all.customer_number%TYPE
376 DEFAULT NULL;
377 l_remittance_amount ar_payments_interface_all.remittance_amount%TYPE
378 DEFAULT NULL;
379 l_currency_code ar_payments_interface_all.currency_code%TYPE
380 DEFAULT NULL;
381 l_item_number ar_payments_interface_all.item_number%TYPE
382 DEFAULT NULL;
383 l_inv_ref ar_payments_interface_all.invoice1%TYPE
384 DEFAULT NULL;
385 l_invoice ar_payments_interface_all.invoice1%TYPE
386 DEFAULT NULL;
387 l_amt_appl ar_payments_interface_all.amount_applied1%TYPE
388 DEFAULT NULL;
389 l_amount_applied ar_payments_interface_all.amount_applied1%TYPE
390 DEFAULT NULL;
391 l_check_number ar_payments_interface_all.check_number%TYPE
392 DEFAULT NULL;
393 l_receipt_date ar_payments_interface_all.receipt_date%TYPE
394 DEFAULT NULL;
395 l_transit_routing_number ar_payments_interface_all.transit_routing_number%TYPE
396 DEFAULT NULL;
397 l_account ar_payments_interface_all.ACCOUNT%TYPE
398 DEFAULT NULL;
399 l_invoice1 ar_payments_interface_all.invoice1%TYPE
400 DEFAULT NULL;
401 l_invoice2 ar_payments_interface_all.invoice2%TYPE
402 DEFAULT NULL;
403 l_invoice3 ar_payments_interface_all.invoice3%TYPE
404 DEFAULT NULL;
405 l_invoice4 ar_payments_interface_all.invoice4%TYPE
406 DEFAULT NULL;
407 l_invoice5 ar_payments_interface_all.invoice5%TYPE
408 DEFAULT NULL;
409 l_invoice6 ar_payments_interface_all.invoice6%TYPE
410 DEFAULT NULL;
411 l_invoice7 ar_payments_interface_all.invoice7%TYPE
412 DEFAULT NULL;
413 l_invoice8 ar_payments_interface_all.invoice8%TYPE
414 DEFAULT NULL;
415 l_new_invoice1 ar_payments_interface_all.invoice1%TYPE
416 DEFAULT NULL;
417 l_new_invoice2 ar_payments_interface_all.invoice2%TYPE
418 DEFAULT NULL;
419 l_new_invoice3 ar_payments_interface_all.invoice3%TYPE
420 DEFAULT NULL;
421 l_new_invoice4 ar_payments_interface_all.invoice4%TYPE
422 DEFAULT NULL;
423 l_new_invoice5 ar_payments_interface_all.invoice5%TYPE
424 DEFAULT NULL;
425 l_new_invoice6 ar_payments_interface_all.invoice6%TYPE
426 DEFAULT NULL;
427 l_new_invoice7 ar_payments_interface_all.invoice7%TYPE
428 DEFAULT NULL;
429 l_new_invoice8 ar_payments_interface_all.invoice8%TYPE
430 DEFAULT NULL;
431
432 -- varao - Bug#5075248 - Modified - Start
433 -- Datatype matched with the new column from which amount is queried from
434 l_amount_applied1 ar_payments_interface_all.tmp_amt_applied1%TYPE
435 DEFAULT NULL;
436 l_amount_applied2 ar_payments_interface_all.tmp_amt_applied2%TYPE
437 DEFAULT NULL;
438 l_amount_applied3 ar_payments_interface_all.tmp_amt_applied3%TYPE
439 DEFAULT NULL;
440 l_amount_applied4 ar_payments_interface_all.tmp_amt_applied4%TYPE
441 DEFAULT NULL;
442 l_amount_applied5 ar_payments_interface_all.tmp_amt_applied5%TYPE
443 DEFAULT NULL;
444 l_amount_applied6 ar_payments_interface_all.tmp_amt_applied6%TYPE
445 DEFAULT NULL;
446 l_amount_applied7 ar_payments_interface_all.tmp_amt_applied7%TYPE
447 DEFAULT NULL;
448 l_amount_applied8 ar_payments_interface_all.tmp_amt_applied8%TYPE
449 DEFAULT NULL;
450 l_amount_app_from1 ar_payments_interface_all.tmp_amt_applied_from1%TYPE DEFAULT NULL;
451 l_amount_app_from2 ar_payments_interface_all.tmp_amt_applied_from2%TYPE DEFAULT NULL;
452 l_amount_app_from3 ar_payments_interface_all.tmp_amt_applied_from3%TYPE DEFAULT NULL;
453 l_amount_app_from4 ar_payments_interface_all.tmp_amt_applied_from4%TYPE DEFAULT NULL;
454 l_amount_app_from5 ar_payments_interface_all.tmp_amt_applied_from5%TYPE DEFAULT NULL;
455 l_amount_app_from6 ar_payments_interface_all.tmp_amt_applied_from6%TYPE DEFAULT NULL;
456 l_amount_app_from7 ar_payments_interface_all.tmp_amt_applied_from7%TYPE DEFAULT NULL;
457 l_amount_app_from8 ar_payments_interface_all.tmp_amt_applied_from8%TYPE DEFAULT NULL;
458 l_trans_to_receipt_rate1 ar_payments_interface_all.trans_to_receipt_rate1%TYPE DEFAULT NULL;
459 l_trans_to_receipt_rate2 ar_payments_interface_all.trans_to_receipt_rate2%TYPE DEFAULT NULL;
460 l_trans_to_receipt_rate3 ar_payments_interface_all.trans_to_receipt_rate3%TYPE DEFAULT NULL;
461 l_trans_to_receipt_rate4 ar_payments_interface_all.trans_to_receipt_rate4%TYPE DEFAULT NULL;
462 l_trans_to_receipt_rate5 ar_payments_interface_all.trans_to_receipt_rate5%TYPE DEFAULT NULL;
463 l_trans_to_receipt_rate6 ar_payments_interface_all.trans_to_receipt_rate6%TYPE DEFAULT NULL;
464 l_trans_to_receipt_rate7 ar_payments_interface_all.trans_to_receipt_rate7%TYPE DEFAULT NULL;
465 l_trans_to_receipt_rate8 ar_payments_interface_all.trans_to_receipt_rate8%TYPE DEFAULT NULL;
466
467 l_returned_curr_code ar_payments_interface_all.currency_code%TYPE DEFAULT NULL;
468 l_returned_amount_to ar_payments_interface_all.tmp_amt_applied1%TYPE DEFAULT NULL;
469 l_returned_amount_from ar_payments_interface_all.tmp_amt_applied_from1%TYPE DEFAULT NULL;
470 l_returned_precision_to NUMBER;
471 l_returned_precision_from NUMBER;
472 l_tmp_amount_applied ar_payments_interface_all.tmp_amt_applied1%TYPE DEFAULT NULL;
473 l_tmp_amount_app_from ar_payments_interface_all.tmp_amt_applied_from1%TYPE DEFAULT NULL;
474 l_tmp_currency_code ar_payments_interface_all.currency_code%TYPE DEFAULT NULL;
475
476 -- varao - Bug#5075248 - Modified - End
477 l_new_amount_applied1 ar_payments_interface_all.amount_applied1%TYPE
478 DEFAULT NULL;
479 l_new_amount_applied2 ar_payments_interface_all.amount_applied2%TYPE
480 DEFAULT NULL;
481 l_new_amount_applied3 ar_payments_interface_all.amount_applied3%TYPE
482 DEFAULT NULL;
483 l_new_amount_applied4 ar_payments_interface_all.amount_applied4%TYPE
484 DEFAULT NULL;
485 l_new_amount_applied5 ar_payments_interface_all.amount_applied5%TYPE
486 DEFAULT NULL;
487 l_new_amount_applied6 ar_payments_interface_all.amount_applied6%TYPE
488 DEFAULT NULL;
489 l_new_amount_applied7 ar_payments_interface_all.amount_applied7%TYPE
490 DEFAULT NULL;
491 l_new_amount_applied8 ar_payments_interface_all.amount_applied8%TYPE
492 DEFAULT NULL;
493
494 l_new_amount_applied_from1 ar_payments_interface_all.amount_applied_from1%TYPE DEFAULT NULL;
495 l_new_amount_applied_from2 ar_payments_interface_all.amount_applied_from2%TYPE DEFAULT NULL;
496 l_new_amount_applied_from3 ar_payments_interface_all.amount_applied_from3%TYPE DEFAULT NULL;
497 l_new_amount_applied_from4 ar_payments_interface_all.amount_applied_from4%TYPE DEFAULT NULL;
498 l_new_amount_applied_from5 ar_payments_interface_all.amount_applied_from5%TYPE DEFAULT NULL;
499 l_new_amount_applied_from6 ar_payments_interface_all.amount_applied_from6%TYPE DEFAULT NULL;
500 l_new_amount_applied_from7 ar_payments_interface_all.amount_applied_from7%TYPE DEFAULT NULL;
501 l_new_amount_applied_from8 ar_payments_interface_all.amount_applied_from8%TYPE DEFAULT NULL;
502
503 l_currency_code1 ar_payments_interface_all.INVOICE_CURRENCY_CODE1%TYPE DEFAULT NULL;
504 l_currency_code2 ar_payments_interface_all.INVOICE_CURRENCY_CODE2%TYPE DEFAULT NULL;
505 l_currency_code3 ar_payments_interface_all.INVOICE_CURRENCY_CODE3%TYPE DEFAULT NULL;
506 l_currency_code4 ar_payments_interface_all.INVOICE_CURRENCY_CODE4%TYPE DEFAULT NULL;
507 l_currency_code5 ar_payments_interface_all.INVOICE_CURRENCY_CODE5%TYPE DEFAULT NULL;
508 l_currency_code6 ar_payments_interface_all.INVOICE_CURRENCY_CODE6%TYPE DEFAULT NULL;
509 l_currency_code7 ar_payments_interface_all.INVOICE_CURRENCY_CODE7%TYPE DEFAULT NULL;
510 l_currency_code8 ar_payments_interface_all.INVOICE_CURRENCY_CODE8%TYPE DEFAULT NULL;
511
512 l_new_currency_code1 ar_payments_interface_all.INVOICE_CURRENCY_CODE1%TYPE DEFAULT NULL;
513 l_new_currency_code2 ar_payments_interface_all.INVOICE_CURRENCY_CODE2%TYPE DEFAULT NULL;
514 l_new_currency_code3 ar_payments_interface_all.INVOICE_CURRENCY_CODE3%TYPE DEFAULT NULL;
515 l_new_currency_code4 ar_payments_interface_all.INVOICE_CURRENCY_CODE4%TYPE DEFAULT NULL;
516 l_new_currency_code5 ar_payments_interface_all.INVOICE_CURRENCY_CODE5%TYPE DEFAULT NULL;
517 l_new_currency_code6 ar_payments_interface_all.INVOICE_CURRENCY_CODE6%TYPE DEFAULT NULL;
518 l_new_currency_code7 ar_payments_interface_all.INVOICE_CURRENCY_CODE7%TYPE DEFAULT NULL;
519 l_new_currency_code8 ar_payments_interface_all.INVOICE_CURRENCY_CODE8%TYPE DEFAULT NULL;
520
521 l_new_trans_to_rct_rate1 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE1%TYPE DEFAULT NULL;
522 l_new_trans_to_rct_rate2 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE2%TYPE DEFAULT NULL;
523 l_new_trans_to_rct_rate3 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE3%TYPE DEFAULT NULL;
524 l_new_trans_to_rct_rate4 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE4%TYPE DEFAULT NULL;
525 l_new_trans_to_rct_rate5 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE5%TYPE DEFAULT NULL;
526 l_new_trans_to_rct_rate6 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE6%TYPE DEFAULT NULL;
527 l_new_trans_to_rct_rate7 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE7%TYPE DEFAULT NULL;
528 l_new_trans_to_rct_rate8 ar_payments_interface_all.TRANS_TO_RECEIPT_RATE8%TYPE DEFAULT NULL;
529
530
531 l_tolerance okl_cash_allctn_rls.amount_tolerance_percent%TYPE
532 DEFAULT NULL;
533 l_days_past_quote_valid okl_cash_allctn_rls.days_past_quote_valid_toleranc%TYPE
534 DEFAULT NULL;
535 l_quote_id okl_trx_quotes_v.ID%TYPE DEFAULT NULL;
536 l_quote_amount okl_txl_quote_lines_v.amount%TYPE
537 DEFAULT NULL;
538 l_quote_number okl_trx_quotes_v.quote_number%TYPE
539 DEFAULT NULL;
540 l_quote_date_effective_to okl_trx_quotes_v.date_effective_to%TYPE
541 DEFAULT NULL;
542 l_party_id okx_customer_accounts_v.party_id%TYPE
543 DEFAULT NULL;
544 l_err_msg VARCHAR2 (2000);
545 l_valid_ar_reference VARCHAR2 (1);
546 l_no_match_indicator NUMBER := 0;
547 -- refer to bug number 3783202
548 i NUMBER DEFAULT NULL;
549 j NUMBER DEFAULT NULL;
550 k NUMBER DEFAULT NULL;
551 seq_num NUMBER DEFAULT NULL;
552 l_trans_rec_count NUMBER DEFAULT NULL;
553 l_api_version NUMBER := 1.0;
554 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
555 l_return_status VARCHAR2 (1);
556 l_msg_count NUMBER;
557 l_msg_data VARCHAR2 (2000);
558 --asawanka modified for llaca start
559 l_hdr_idx NUMBER;
560 l_inv_indx NUMBER;
561 l_line_indx NUMBER;
562 l_transmission_rec_id_of NUMBER;
563 l_amt1_prec NUMBER;
564 l_amt2_prec NUMBER;
565 l_amt3_prec NUMBER;
566 l_amt4_prec NUMBER;
567 l_amt5_prec NUMBER;
568 l_amt6_prec NUMBER;
569 l_amt7_prec NUMBER;
570 l_amt8_prec NUMBER;
571 l_amt_from1_prec NUMBER;
572 l_amt_from2_prec NUMBER;
573 l_amt_from3_prec NUMBER;
574 l_amt_from4_prec NUMBER;
575 l_amt_from5_prec NUMBER;
576 l_amt_from6_prec NUMBER;
577 l_amt_from7_prec NUMBER;
578 l_amt_from8_prec NUMBER;
579 l_prec_to_use NUMBER;
580 l_prec_to_use_from NUMBER;
581 l_ovf_ind VARCHAR2 (3);
582 --START: Fixed as part of bug 6780241 by nikshah
583 l_amount_in_inv_curr NUMBER;
584 l_amount_in_rct_curr NUMBER;
585 --END: Fixed as part of bug 6780241 by nikshah
586
587 --asawanka modified for llaca end
588 -- l_zero_receipt EXCEPTION;
589 -- l_zero_invoice EXCEPTION;
590 -- l_no_cash_rule EXCEPTION;
591 -- l_del_int EXCEPTION;
592 ------------------------------
593 -- DECLARE Record/Table Types
594 ------------------------------
595 -- Internal Trans
596 TYPE l_orig_rcpt_rec_type IS RECORD (
597 invoice_number ar_payments_interface_all.invoice1%TYPE
598 DEFAULT NULL,
599 amount_applied ar_payments_interface_all.amount_applied1%TYPE
600 DEFAULT NULL,
601 amount_applied_from ar_payments_interface_all.AMOUNT_APPLIED_FROM1%TYPE
602 DEFAULT NULL,
603 trans_to_receipt_rate ar_payments_interface_all.TRANS_TO_RECEIPT_RATE1%TYPE
604 DEFAULT NULL,
605 currency_code ar_payments_interface_all.currency_code%TYPE
606 DEFAULT NULL
607 );
608
609 TYPE l_orig_rcpt_tbl_type IS TABLE OF l_orig_rcpt_rec_type
610 INDEX BY BINARY_INTEGER;
611
612 l_orig_rcpt_tbl l_orig_rcpt_tbl_type;
613 l_no_mtch_rcpt_tbl l_orig_rcpt_tbl_type;
614 l_initialize_table l_orig_rcpt_tbl_type;
615 l_qtev_rec okl_qte_pvt.qtev_rec_type;
616 x_qtev_rec okl_qte_pvt.qtev_rec_type;
617 -- Begin - Changes for CAR API structure change
618 l_onacc_amount NUMBER := 0;
619 l_unapply_amount NUMBER := 0;
620 l_onacc_unapp_exist VARCHAR2 (1) := 'N';
621
622 -- End - Changes for CAR API structure change
623 -------------------
624 -- DECLARE Cursors
625 -------------------
626 -- get customer number if not known
627 --asawanka modified for llca start
628 -- modified query to get rid of obsolete view
629 CURSOR c_get_cust_via_micr (
630 cp_transit_routing_number IN VARCHAR2,
631 cp_account IN VARCHAR2
632 ) IS
633 SELECT hca.account_number
634 FROM iby_ext_bank_accounts_v a,
635 iby_ext_bank_accounts b,
636 iby_pmt_instr_uses_all ipiua,
637 iby_external_payers_all iepa,
638 hz_cust_accounts hca
639 WHERE a.ext_bank_account_id = b.ext_bank_account_id
640 AND ipiua.instrument_id = a.ext_bank_account_id
641 AND ipiua.ext_pmt_party_id = iepa.ext_payer_id
642 AND ipiua.payment_flow = 'FUNDS_CAPTURE'
643 AND iepa.cust_account_id = hca.cust_account_id
644 AND a.bank_number = cp_transit_routing_number
645 AND b.bank_account_num = cp_account;
646
647 --asawanka modified for llca end
648 --------
649 -- get tranmission format record identifiers
650 CURSOR c_get_trans_fmt (cp_trans_req_id IN NUMBER) IS
651 SELECT b.record_identifier,
652 b.record_type_lookup_code,
653 b.transmission_format_id
654 FROM ar_transmissions_all a, ar_trans_record_formats b
655 WHERE a.requested_trans_format_id = b.transmission_format_id
656 AND a.transmission_request_id = cp_trans_req_id
657 ORDER BY b.record_identifier;
658
659 ----------
660 -- get overflow indicator
661 CURSOR c_get_ovr_flw_indicator (cp_trans_frmt_id IN NUMBER) IS
662 SELECT overflow_rec_indicator
663 FROM ar_trans_field_formats
664 WHERE transmission_format_id = cp_trans_frmt_id
665 AND overflow_rec_indicator IS NOT NULL;
666
667 ----------
668 -- varao - Bug#5075248 - Modified - Start
669 -- Changing the column to query for the amounts to be applied against invoices to TMP_AMT_APPLIED columns instead
670 -- of the AMOUNT_APPLIED columns
671 -- get lockbox payment records
672 CURSOR c_get_pmt_recs (cp_trans_req_id IN NUMBER, cp_rec_type IN VARCHAR) IS
673 SELECT transmission_record_id,
674 transmission_id,
675 lockbox_number,
676 batch_name,
677 remittance_amount,
678 NVL (receipt_date, SYSDATE),
679 item_number,
680 currency_code,
681 customer_number,
682 check_number,
683 transit_routing_number,
684 ACCOUNT,
685 TRIM (invoice1),
686 TRIM (invoice2),
687 TRIM (invoice3),
688 TRIM (invoice4),
689 TRIM (invoice5),
690 TRIM (invoice6),
691 TRIM (invoice7),
692 TRIM (invoice8),
693 tmp_amt_applied1,
694 tmp_amt_applied2,
695 tmp_amt_applied3,
696 tmp_amt_applied4,
697 tmp_amt_applied5,
698 tmp_amt_applied6,
699 tmp_amt_applied7,
700 tmp_amt_applied8,
701 AMOUNT_APPLIED_FROM1,
702 AMOUNT_APPLIED_FROM2,
703 AMOUNT_APPLIED_FROM3,
704 AMOUNT_APPLIED_FROM4,
705 AMOUNT_APPLIED_FROM5,
706 AMOUNT_APPLIED_FROM6,
707 AMOUNT_APPLIED_FROM7,
708 AMOUNT_APPLIED_FROM8,
709 TRANS_TO_RECEIPT_RATE1,
710 TRANS_TO_RECEIPT_RATE2,
711 TRANS_TO_RECEIPT_RATE3,
712 TRANS_TO_RECEIPT_RATE4,
713 TRANS_TO_RECEIPT_RATE5,
714 TRANS_TO_RECEIPT_RATE6,
715 TRANS_TO_RECEIPT_RATE7,
716 TRANS_TO_RECEIPT_RATE8,
717 INVOICE_CURRENCY_CODE1,
718 INVOICE_CURRENCY_CODE2,
719 INVOICE_CURRENCY_CODE3,
720 INVOICE_CURRENCY_CODE4,
721 INVOICE_CURRENCY_CODE5,
722 INVOICE_CURRENCY_CODE6,
723 INVOICE_CURRENCY_CODE7,
724 INVOICE_CURRENCY_CODE8,
725 /* tmp_amt_applied_from1,
726 tmp_amt_applied_from2,
727 tmp_amt_applied_from3,
728 tmp_amt_applied_from4,
729 tmp_amt_applied_from5,
730 tmp_amt_applied_from6,
731 tmp_amt_applied_from7,
732 tmp_amt_applied_from8,
733 TMP_TRANS_TO_RCPT_RATE1,
734 TMP_TRANS_TO_RCPT_RATE2,
735 TMP_TRANS_TO_RCPT_RATE3,
736 TMP_TRANS_TO_RCPT_RATE4,
737 TMP_TRANS_TO_RCPT_RATE5,
738 TMP_TRANS_TO_RCPT_RATE6,
739 TMP_TRANS_TO_RCPT_RATE7,
740 TMP_TRANS_TO_RCPT_RATE8,
741 TMP_INV_CURRENCY_CODE1,
742 TMP_INV_CURRENCY_CODE2,
743 TMP_INV_CURRENCY_CODE3,
744 TMP_INV_CURRENCY_CODE4,
745 TMP_INV_CURRENCY_CODE5,
746 TMP_INV_CURRENCY_CODE6,
747 TMP_INV_CURRENCY_CODE7,
748 TMP_INV_CURRENCY_CODE8, */
749 org_id
750 FROM ar_payments_interface_all
751 WHERE transmission_request_id = cp_trans_req_id
752 AND record_type = cp_rec_type
753 ORDER BY item_number;
754
755 ----------
756 -- Changing the column to query for the amounts to be applied against invoices to TMP_AMT_APPLIED columns instead
757 -- of the AMOUNT_APPLIED columns
758 -- get lockbox overflow records
759 CURSOR c_get_ovrflw_recs (
760 cp_trans_req_id IN NUMBER,
761 cp_rec_type IN VARCHAR,
762 cp_item_number IN NUMBER,
763 cp_batch_name IN VARCHAR2
764 ) IS
765 SELECT transmission_record_id,
766 -- currency_code, we will fetch currecny code from payment record only
767 TRIM (invoice1),
768 TRIM (invoice2),
769 TRIM (invoice3),
770 TRIM (invoice4),
771 TRIM (invoice5),
772 TRIM (invoice6),
773 TRIM (invoice7),
774 TRIM (invoice8),
775 tmp_amt_applied1,
776 tmp_amt_applied2,
777 tmp_amt_applied3,
778 tmp_amt_applied4,
779 tmp_amt_applied5,
780 tmp_amt_applied6,
781 tmp_amt_applied7,
782 tmp_amt_applied8,
783 AMOUNT_APPLIED_FROM1,
784 AMOUNT_APPLIED_FROM2,
785 AMOUNT_APPLIED_FROM3,
786 AMOUNT_APPLIED_FROM4,
787 AMOUNT_APPLIED_FROM5,
788 AMOUNT_APPLIED_FROM6,
789 AMOUNT_APPLIED_FROM7,
790 AMOUNT_APPLIED_FROM8,
791 TRANS_TO_RECEIPT_RATE1,
792 TRANS_TO_RECEIPT_RATE2,
793 TRANS_TO_RECEIPT_RATE3,
794 TRANS_TO_RECEIPT_RATE4,
795 TRANS_TO_RECEIPT_RATE5,
796 TRANS_TO_RECEIPT_RATE6,
797 TRANS_TO_RECEIPT_RATE7,
798 TRANS_TO_RECEIPT_RATE8,
799 INVOICE_CURRENCY_CODE1,
800 INVOICE_CURRENCY_CODE2,
801 INVOICE_CURRENCY_CODE3,
802 INVOICE_CURRENCY_CODE4,
803 INVOICE_CURRENCY_CODE5,
804 INVOICE_CURRENCY_CODE6,
805 INVOICE_CURRENCY_CODE7,
806 INVOICE_CURRENCY_CODE8
807 /* tmp_amt_applied_from1,
808 tmp_amt_applied_from2,
809 tmp_amt_applied_from3,
810 tmp_amt_applied_from4,
811 tmp_amt_applied_from5,
812 tmp_amt_applied_from6,
813 tmp_amt_applied_from7,
814 tmp_amt_applied_from8,
815 TMP_TRANS_TO_RCPT_RATE1,
816 TMP_TRANS_TO_RCPT_RATE2,
817 TMP_TRANS_TO_RCPT_RATE3,
818 TMP_TRANS_TO_RCPT_RATE4,
819 TMP_TRANS_TO_RCPT_RATE5,
820 TMP_TRANS_TO_RCPT_RATE6,
821 TMP_TRANS_TO_RCPT_RATE7,
822 TMP_TRANS_TO_RCPT_RATE8,
823 TMP_INV_CURRENCY_CODE1,
824 TMP_INV_CURRENCY_CODE2,
825 TMP_INV_CURRENCY_CODE3,
826 TMP_INV_CURRENCY_CODE4,
827 TMP_INV_CURRENCY_CODE5,
828 TMP_INV_CURRENCY_CODE6,
829 TMP_INV_CURRENCY_CODE7,
830 TMP_INV_CURRENCY_CODE8*/
831 FROM ar_payments_interface_all
832 WHERE transmission_request_id = cp_trans_req_id
833 AND record_type = cp_rec_type
834 AND item_number = cp_item_number
835 AND overflow_sequence IS NOT NULL
836 AND (batch_name IS NULL OR batch_name = cp_batch_name
837 ) --Fixed bug 6033325
838 ORDER BY overflow_sequence;
839
840 -- varao - Bug#5075248 - Modified - End
841 ----------
842 -- get NEW lockbox overflow records
843 CURSOR c_get_ovrflw_recs_new (
844 cp_trans_req_id IN NUMBER,
845 cp_rec_type IN VARCHAR,
846 cp_item_number IN NUMBER,
847 cp_batch_name IN VARCHAR2
848 ) IS
849 SELECT transmission_record_id
850 FROM ar_payments_interface_all
851 WHERE transmission_request_id = cp_trans_req_id
852 AND record_type = cp_rec_type
853 AND item_number = cp_item_number
854 AND overflow_sequence IS NULL
855 AND (batch_name IS NULL OR batch_name = cp_batch_name
856 ) --Fixed bug 6033325
857 ORDER BY transmission_record_id;
858
859 ----------
860 -- get transmission count
861 CURSOR c_get_trans_rec_count (cp_trans_req_id IN NUMBER) IS
862 -- , cp_rec_type IN VARCHAR2 ) IS
863 SELECT COUNT (*)
864 FROM ar_payments_interface_all
865 WHERE transmission_request_id = cp_trans_req_id;
866
867 --AND record_type <> cp_rec_type;
868 ----------
869 -- get cash applic rules
870 --asawanka modified for llca start
871 -- added org_id parameter to filter by org_id. Also added default_rule filter as we want default rule
872 -- set for that org
873 CURSOR c_cash_applic_rules (cp_org_id IN NUMBER) IS
874 SELECT days_past_quote_valid_toleranc,
875 amount_tolerance_percent
876 FROM okl_cash_allctn_rls
877 WHERE default_rule = 'YES' AND org_id = cp_org_id;
878
879 --asawanka modified for llca end
880 ----------
881 --asawanka modified for llca start
882 --Instead oh checkin. OKL_EXT_CSH_RCPTS_B table ,which is obsolete,
883 -- we will check if
884
885 -- check to see if lock box already has line level data for the invocie in theis transmission
886 CURSOR c_lckbx_status (
887 cp_transmission_request_id IN NUMBER,
888 cp_transmission_record_id IN VARCHAR2,
889 cp_invoice_number IN VARCHAR2
890 ) IS
891 SELECT 'Y'
892 FROM ar_pmts_interface_line_details
893 WHERE transmission_request_id = cp_transmission_request_id
894 AND transmission_record_id = cp_transmission_record_id
895 AND invoice_number = cp_invoice_number;
896
897 --asawanka modified for llca end
898 ----------
899 /*
900 -- get termination quote
901 CURSOR c_check_termination (cp_cust_id IN NUMBER,
902 cp_rcpt_date IN DATE,
903 cp_date_tol IN NUMBER)IS
904 SELECT tq.id quote_id,
905 tq.date_effective_to,
906 SUM(NVL(tl.amount,0)) quote_amount
907 FROM okc_k_party_roles_v pr,
908 okl_quote_parties_v qp,
909 okl_trx_quotes_v tq,
910 okl_txl_quote_lines_v tl
911 WHERE pr.jtot_object1_code = 'OKX_PARTY'
912 AND pr.object1_id2 = '#'
913 AND pr.object1_id1 = cp_cust_id
914 AND qp.cpl_id = pr.id
915 AND qp.qpt_code = 'RECIPIENT'
916 AND tq.id = qp.qte_id
917 AND TRUNC(cp_rcpt_date) BETWEEN TRUNC (tq.date_effective_from)
918 AND TRUNC(NVL((tq.date_effective_to + cp_date_tol), SYSDATE))
919 AND tl.qte_id = tq.id
920 AND NVL(tq.accepted_yn,'N') = 'N' and tq.qtp_code like 'TER%'
921 AND NVL(tq.payment_received_yn,'N') = 'N'
922 AND NVL(tq.preproceeds_yn,'N') = 'N'
923 GROUP BY pr.object1_id1, tq.id, tq.date_effective_to;
924 */
925 ----------
926 -- get termination quote
927 CURSOR c_check_termination (
928 cp_cust_id IN NUMBER,
929 cp_rcpt_date IN DATE,
930 cp_date_tol IN NUMBER,
931 cp_quote_number IN VARCHAR2
932 ) IS
933 SELECT tq.ID quote_id,
934 tq.date_effective_to,
935 tq.quote_number,
936 SUM (NVL (tl.amount, 0)) quote_amount
937 FROM okc_k_party_roles_v pr,
938 okl_quote_parties_v qp,
939 okl_trx_quotes_v tq,
940 okl_txl_quote_lines_v tl
941 WHERE pr.jtot_object1_code = 'OKX_PARTY'
942 AND pr.object1_id2 = '#'
943 AND pr.object1_id1 = cp_cust_id
944 AND qp.cpl_id = pr.ID
945 AND qp.qpt_code = 'RECIPIENT'
946 AND tq.ID = qp.qte_id
947 AND TRUNC (cp_rcpt_date) BETWEEN TRUNC (tq.date_effective_from)
948 AND TRUNC
949 (NVL
950 (( tq.date_effective_to
951 + cp_date_tol
952 ),
953 SYSDATE
954 )
955 )
956 AND tl.qte_id = tq.ID
957 AND NVL (tq.accepted_yn, 'N') = 'N'
958 AND tq.qtp_code LIKE 'TER%'
959 AND NVL (tq.payment_received_yn, 'N') = 'N'
960 AND NVL (tq.preproceeds_yn, 'N') = 'N'
961 AND TO_CHAR (tq.quote_number) = cp_quote_number
962 AND tq.qst_code IN ('APPROVED')
963 AND tl.qlt_code NOT IN ('AMCFIA')
964 AND qtp_code NOT IN
965 ('TER_ROLL_PURCHASE', 'TER_ROLL_WO_PURCHASE')
966 -- Added Bug 3953303
967 GROUP BY pr.object1_id1, tq.ID, tq.date_effective_to,
968 tq.quote_number;
969
970 ----------
971 -- get party id for checking termination quote
972 CURSOR c_get_party_id (cp_cust_num IN NUMBER) IS
973 SELECT party_id
974 FROM okx_customer_accounts_v
975 WHERE description = TO_CHAR (cp_cust_num);
976
977 ----------
978 l_linelevel_app VARCHAR2 (1) DEFAULT 'Y';
979 -- akrangan added for debug feature start
980 l_module_name VARCHAR2 (500)
981 := g_module || 'handle_auto_pay';
982 -- akrangan added for debug feature end
983 BEGIN
984 --asawanka modified for llca start
985 -- commenting out as it is now moved below inside loop
986 /*
987 OPEN c_Cash_apPlic_Rules;
988 FETCH c_Cash_apPlic_Rules INTO l_Days_Past_Quote_Valid,l_Tolerance;
989 CLOSE c_Cash_apPlic_Rules;
990 */
991 IF (l_debug_enabled = 'Y') THEN
992 l_level_statement := fnd_log.level_statement;
993 is_debug_statement_on :=
994 okl_debug_pub.check_log_on (l_module, l_level_statement);
995 END IF;
996
997 log_debug
998 ('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
999 );
1000 log_debug
1001 ( ' okl_lckbx_csh_app_pvt.handle_auto_pay start. Parameter p_trans_req_id = '
1002 || p_trans_req_id
1003 );
1004 --asawanka modified for llca end
1005 l_trans_req_id := p_trans_req_id;
1006 -- get transmission record format for this lock box.
1007 log_debug ('Fetching Transmission format Identifiers');
1008
1009 OPEN c_get_trans_fmt (l_trans_req_id);
1010
1011 LOOP
1012 FETCH c_get_trans_fmt
1013 INTO l_record_identifier,
1014 l_record_type_lookup_code,
1015 l_transmission_format_id;
1016
1017 EXIT WHEN c_get_trans_fmt%NOTFOUND;
1018
1019 IF l_record_type_lookup_code = 'TRANS HDR' THEN
1020 l_transmission_hdr := l_record_identifier;
1021 ELSIF l_record_type_lookup_code = 'TRANS TRL' THEN
1022 l_transmission_trl := l_record_identifier;
1023 ELSIF l_record_type_lookup_code = 'PAYMENT' THEN
1024 l_payment := l_record_identifier;
1025 ELSIF l_record_type_lookup_code = 'OVRFLW PAYMENT' THEN
1026 l_overflow := l_record_identifier;
1027 ELSIF l_record_type_lookup_code = 'SERVICE HDR' THEN
1028 l_service := l_record_identifier;
1029 END IF;
1030 END LOOP;
1031
1032 CLOSE c_get_trans_fmt;
1033
1034 OPEN c_get_ovr_flw_indicator (l_transmission_format_id);
1035
1036 FETCH c_get_ovr_flw_indicator
1037 INTO l_overflow_rec_indicator;
1038
1039 CLOSE c_get_ovr_flw_indicator;
1040
1041 -- got what we need - lets start processing.
1042 -- get payment and related over flow records in lockbox.
1043 log_debug ('Fetching Payment Records');
1044
1045 OPEN c_get_pmt_recs (l_trans_req_id, l_payment);
1046
1047 LOOP -- (1) Payments through each record of type Payment.
1048 FETCH c_get_pmt_recs
1049 INTO l_transmission_record_id,
1050 l_transmission_id,
1051 l_lockbox_number,
1052 l_batch_name,
1053 l_remittance_amount,
1054 l_receipt_date,
1055 l_item_number,
1056 l_currency_code,
1057 l_customer_number,
1058 l_check_number,
1059 l_transit_routing_number, -- Used for MICR customers
1060 l_account, -- Used for MICR customers
1061 l_invoice1,
1062 l_invoice2,
1063 l_invoice3,
1064 l_invoice4,
1065 l_invoice5,
1066 l_invoice6,
1067 l_invoice7,
1068 l_invoice8,
1069 l_amount_applied1,
1070 l_amount_applied2,
1071 l_amount_applied3,
1072 l_amount_applied4,
1073 l_amount_applied5,
1074 l_amount_applied6,
1075 l_amount_applied7,
1076 l_amount_applied8,
1077 l_amount_app_from1,
1078 l_amount_app_from2,
1079 l_amount_app_from3,
1080 l_amount_app_from4,
1081 l_amount_app_from5,
1082 l_amount_app_from6,
1083 l_amount_app_from7,
1084 l_amount_app_from8,
1085 l_trans_to_receipt_rate1,
1086 l_trans_to_receipt_rate2,
1087 l_trans_to_receipt_rate3,
1088 l_trans_to_receipt_rate4,
1089 l_trans_to_receipt_rate5,
1090 l_trans_to_receipt_rate6,
1091 l_trans_to_receipt_rate7,
1092 l_trans_to_receipt_rate8,
1093 l_currency_code1,
1094 l_currency_code2,
1095 l_currency_code3,
1096 l_currency_code4,
1097 l_currency_code5,
1098 l_currency_code6,
1099 l_currency_code7,
1100 l_currency_code8,
1101 l_org_id;
1102
1103 EXIT WHEN c_get_pmt_recs%NOTFOUND;
1104 log_debug (' ');
1105 log_debug ( 'Processiong payment record. Tranmission Record Id = '
1106 || l_transmission_record_id
1107 );
1108
1109 --asawanka modified for llca start
1110 -- moved from above to here as we need to pass org id to get default rule
1111 OPEN c_cash_applic_rules (l_org_id);
1112
1113 FETCH c_cash_applic_rules
1114 INTO l_days_past_quote_valid,
1115 l_tolerance;
1116
1117 CLOSE c_cash_applic_rules;
1118
1119 --asawanka modified for llca end
1120 -- Check for customer number.
1121 IF l_customer_number IS NULL THEN -- check MICR Number
1122 log_debug
1123 ('Customer Number not specified. Trying to find out from transit routing number and bank account '
1124 );
1125
1126 IF l_transit_routing_number IS NOT NULL AND l_account IS NOT NULL THEN
1127 -- this piece of code is not correct. cursor c_get_cust_via_micr needs to be modified
1128 -- to fetch correct customer number from micr
1129 OPEN c_get_cust_via_micr (l_transit_routing_number, l_account);
1130
1131 FETCH c_get_cust_via_micr
1132 INTO l_customer_number;
1133
1134 CLOSE c_get_cust_via_micr;
1135 ELSE
1136 -- next payment record as we have no customer_number ( see enhancements up top )
1137 log_debug ('Not able to identify customer number');
1138 EXIT; -- LOOP (1) move on to next payment record ...
1139 END IF;
1140 END IF;
1141
1142 log_debug ('Customer Number = ' || l_customer_number);
1143 log_debug ('Fetching precisions for amount columns');
1144 --asawanka modified for llca start
1145 -- fetch format precision for amounts in interface table for the receipt currency
1146 -- Used for search rules, receipt mismatch and termination quote where cross currency
1147 -- application is blocked and for no reference matched also
1148 l_amt1_prec := get_precision (NVL(l_currency_code1,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 1');
1149 l_amt2_prec := get_precision (NVL(l_currency_code2,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 2');
1150 l_amt3_prec := get_precision (NVL(l_currency_code3,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 3');
1151 l_amt4_prec := get_precision (NVL(l_currency_code4,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 4');
1152 l_amt5_prec := get_precision (NVL(l_currency_code5,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 5');
1153 l_amt6_prec := get_precision (NVL(l_currency_code6,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 6');
1154 l_amt7_prec := get_precision (NVL(l_currency_code7,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 7');
1155 l_amt8_prec := get_precision (NVL(l_currency_code8,l_currency_code),p_trans_req_id,'PAYMENT','AMT APP 8');
1156 --asawanka modified for llca end
1157
1158 -- process payment record invoice(s)
1159 -- new overflow rec for each payment rec inv ref
1160 -- update payment record once and for all.
1161 i := 1;
1162 j := 1;
1163 -- initialize all pl/sql tables used by this process
1164 l_orig_rcpt_tbl := l_initialize_table;
1165 l_no_mtch_rcpt_tbl := l_initialize_table;
1166 l_okl_rcpt_tbl := l_okl_init_tbl;
1167 l_orig_rcpt_tbl (i).invoice_number := l_invoice1;
1168 l_orig_rcpt_tbl (i).amount_applied :=
1169 ROUND (l_amount_applied1 / POWER (10, l_amt1_prec), l_amt1_prec);
1170 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from1/power(10,l_amt_from1_prec),l_amt_from1_prec);
1171 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate1;
1172 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1173 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1174 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1175 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1176
1177 i := i + 1;
1178 j := j + 1;
1179 l_orig_rcpt_tbl (i).invoice_number := l_invoice2;
1180 l_orig_rcpt_tbl (i).amount_applied :=
1181 ROUND (l_amount_applied2 / POWER (10, l_amt2_prec), l_amt2_prec);
1182 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from2/power(10,l_amt_from2_prec),l_amt_from2_prec);
1183 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate2;
1184 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1185 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1186 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1187 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1188
1189 i := i + 1;
1190 j := j + 1;
1191 l_orig_rcpt_tbl (i).invoice_number := l_invoice3;
1192 l_orig_rcpt_tbl (i).amount_applied :=
1193 ROUND (l_amount_applied3 / POWER (10, l_amt3_prec), l_amt3_prec);
1194 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from3/power(10,l_amt_from3_prec),l_amt_from3_prec);
1195 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate3;
1196 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1197 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1198 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1199 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1200
1201 i := i + 1;
1202 j := j + 1;
1203 l_orig_rcpt_tbl (i).invoice_number := l_invoice4;
1204 l_orig_rcpt_tbl (i).amount_applied :=
1205 ROUND (l_amount_applied4 / POWER (10, l_amt4_prec), l_amt4_prec);
1206 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from4/power(10,l_amt_from4_prec),l_amt_from4_prec);
1207 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate4;
1208 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1209 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1210 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1211 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1212
1213 i := i + 1;
1214 j := j + 1;
1215 l_orig_rcpt_tbl (i).invoice_number := l_invoice5;
1216 l_orig_rcpt_tbl (i).amount_applied :=
1217 ROUND (l_amount_applied5 / POWER (10, l_amt5_prec), l_amt5_prec);
1218 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from5/power(10,l_amt_from5_prec),l_amt_from5_prec);
1219 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate5;
1220 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1221 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1222 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1223 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1224
1225 i := i + 1;
1226 j := j + 1;
1227 l_orig_rcpt_tbl (i).invoice_number := l_invoice6;
1228 l_orig_rcpt_tbl (i).amount_applied :=
1229 ROUND (l_amount_applied6 / POWER (10, l_amt6_prec), l_amt6_prec);
1230 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from6/power(10,l_amt_from6_prec),l_amt_from6_prec);
1231 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate6;
1232 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1233 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1234 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1235 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1236
1237 i := i + 1;
1238 j := j + 1;
1239 l_orig_rcpt_tbl (i).invoice_number := l_invoice7;
1240 l_orig_rcpt_tbl (i).amount_applied :=
1241 ROUND (l_amount_applied7 / POWER (10, l_amt7_prec), l_amt7_prec);
1242 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from7/power(10,l_amt_from7_prec),l_amt_from7_prec);
1243 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate7;
1244 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1245 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1246 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1247 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1248
1249 i := i + 1;
1250 j := j + 1;
1251 l_orig_rcpt_tbl (i).invoice_number := l_invoice8;
1252 l_orig_rcpt_tbl (i).amount_applied :=
1253 ROUND (l_amount_applied8 / POWER (10, l_amt8_prec), l_amt8_prec);
1254 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from8/power(10,l_amt_from8_prec),l_amt_from8_prec);
1255 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate8;
1256 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
1257 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
1258 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
1259 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
1260
1261 i := 1; -- first record
1262 j := 1;
1263 log_debug ('Processing invoice references in this payment record...');
1264
1265 LOOP
1266 -- (2) Loop through all payment record invoice/payment references
1267 l_okl_rcpt_tbl := l_okl_init_tbl;
1268 select decode(i,1,l_amount_applied1,2,l_amount_applied2,
1269 3, l_amount_applied3,4,l_amount_applied4,
1270 5,l_amount_applied5,6,l_amount_applied6,
1271 7,l_amount_applied7,8,l_amount_applied8),
1272 decode(i,1,l_amount_app_from1,2,l_amount_app_from2,
1273 3, l_amount_app_from3,4,l_amount_app_from4,
1274 5,l_amount_app_from5,6,l_amount_app_from6,
1275 7,l_amount_app_from7,8,l_amount_app_from8),
1276 decode(i,1,l_currency_code1,2,l_currency_code2,
1277 3,l_currency_code3,4,l_currency_code4,
1278 5,l_currency_code5,6,l_currency_code6,
1279 7,l_currency_code7,8,l_currency_code8)
1280 into l_tmp_amount_applied, l_tmp_amount_app_from, l_tmp_currency_code
1281 from dual;
1282
1283
1284 IF l_orig_rcpt_tbl(i).invoice_number IS NOT NULL AND ( l_tmp_amount_applied IS NOT NULL OR l_tmp_amount_app_from IS NOT NULL)
1285 AND ( l_tmp_amount_applied <> 0 OR l_tmp_amount_app_from <> 0 ) THEN
1286 log_debug('Invoice Reference = '||l_orig_rcpt_tbl(i).invoice_number);
1287 log_debug('Amount to apply to = '|| l_tmp_amount_applied);
1288 log_debug('Amount to apply from = '|| l_tmp_amount_app_from);
1289 log_debug('Transaction to Receipt Rate= '|| l_orig_rcpt_tbl(i).trans_to_receipt_rate);
1290
1291 -- invoke okl standard cash application rules
1292 ----------------------------------------------------------------------
1293 -- check to see if given invoice reference maps to a valid non okl ar invoice.
1294 -- if so then this reference should be left in lockbox ...
1295 -- bug ref: 3068497
1296 IF valid_ar_reference (l_orig_rcpt_tbl (i).invoice_number,
1297 l_org_id
1298 ) THEN
1299 l_valid_ar_reference := 'Y';
1300 ELSE
1301 l_valid_ar_reference := 'N';
1302 END IF;
1303
1304 ----------------------------------------------------------------------
1305 --asawanka modified for llca start
1306 -- check if line level data exists for this invoice
1307 l_lockbox_run_already := 'N';
1308
1309 OPEN c_lckbx_status (l_trans_req_id,
1310 l_transmission_record_id,
1311 l_orig_rcpt_tbl (i).invoice_number
1312 );
1313
1314 FETCH c_lckbx_status
1315 INTO l_lockbox_run_already;
1316
1317 CLOSE c_lckbx_status;
1318
1319 --asawanka modified for llca start
1320 -- if line level data already exists for this invoice then do not do any processing for this invoice
1321 IF l_lockbox_run_already = 'N' THEN
1322 log_debug
1323 ('No line level data for invoice reference. Further processing invoice reference'
1324 );
1325
1326 --changed api calls.
1327 IF l_valid_ar_reference = 'N' THEN
1328 log_debug
1329 ('Invoice reference is not valid non OKL AR invoice Number.'
1330 );
1331 log_debug
1332 ('Trying to match Invocie Reference to OKL related AR invoice. ( Post R12 OKL Invoice)'
1333 );
1334 log_debug
1335 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv with parameters: '
1336 );
1337 log_debug ('p_customer_num = ' || l_customer_number);
1338 log_debug ( 'p_arinv_number = '
1339 || l_orig_rcpt_tbl (i).invoice_number
1340 );
1341 log_debug ('p_currency_code = ' || l_currency_code);
1342 log_debug ( 'p_rcpt_amount = '
1343 || l_orig_rcpt_tbl (i).amount_applied
1344 );
1345 log_debug('p_inv_to_rct_rate = '|| l_orig_rcpt_tbl(i).trans_to_receipt_rate);
1346 log_debug('p_receipt_date = ' || l_receipt_date);
1347 log_debug ('p_org_id = ' || l_org_id);
1348
1349 IF is_valid_reference('INVOICE',
1350 l_orig_rcpt_tbl (i).invoice_number,
1351 l_currency_code1,
1352 l_trans_req_id,
1353 l_org_id,
1354 i,
1355 'PAYMENT',
1356 'AMT APP',
1357 'AMT APP FROM',
1358 l_customer_number,
1359 l_tmp_amount_applied,
1360 l_tmp_amount_app_from,
1361 l_returned_curr_code,
1362 l_returned_amount_to,
1363 l_returned_amount_from,
1364 l_returned_precision_to,
1365 l_returned_precision_from)
1366 THEN
1367 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
1368 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
1369 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
1370 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
1371 log_debug('invoice currency = '|| l_returned_curr_code);
1372 --Check if reference number matches any okl related AR invoices(Post R12 okl invoices)
1373 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv
1374 (p_api_version => l_api_version,
1375 p_init_msg_list => l_init_msg_list,
1376 x_return_status => l_return_status,
1377 x_msg_count => l_msg_count,
1378 x_msg_data => l_msg_data,
1379 -- changed for bug 5391874
1380 -- p_customer_num => l_customer_number,
1381 p_customer_num => null,
1382 p_arinv_number => l_orig_rcpt_tbl (i).invoice_number,
1383 p_currency_code => l_currency_code,
1384 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
1385 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
1386 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
1387 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
1388 p_receipt_date => l_receipt_date,
1389 p_org_id => l_org_id,
1390 x_appl_tbl => l_okl_rcpt_tbl,
1391 x_onacc_amount => l_onacc_amount,
1392 x_unapply_amount => l_unapply_amount
1393 );
1394 log_debug
1395 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv return status = '
1396 || l_return_status
1397 );
1398 log_debug
1399 ( 'Number of invoices identified for application purpose = '
1400 || l_okl_rcpt_tbl.COUNT
1401 );
1402
1403 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
1404 l_onacc_unapp_exist := 'Y';
1405 ELSE
1406 l_onacc_unapp_exist := 'N';
1407 END IF;
1408
1409 log_debug ( ' On-Account / Unapplied amount exists : '
1410 || l_onacc_unapp_exist
1411 );
1412 END IF;
1413 END IF;
1414
1415 IF l_okl_rcpt_tbl.COUNT = 0
1416 AND l_valid_ar_reference = 'N'
1417 AND l_onacc_unapp_exist = 'N' THEN
1418 log_debug
1419 ('Invoice Reference did not match to any OKL relate AR Invoice Number'
1420 );
1421 log_debug
1422 ('Trying to match Invocie Reference to OKL Consolidated invoice. ( Pre R12 OKL Invoice)'
1423 );
1424 log_debug
1425 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv with parameters: '
1426 );
1427 log_debug ('p_customer_num = ' || l_customer_number);
1428 log_debug ( 'p_cons_inv = '
1429 || l_orig_rcpt_tbl (i).invoice_number
1430 );
1431 log_debug ('p_currency_code = ' || l_currency_code);
1432 log_debug ( 'p_rcpt_amount = '
1433 || l_orig_rcpt_tbl (i).amount_applied
1434 );
1435 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
1436 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
1437 log_debug('p_inv_to_rct_rate = '|| l_orig_rcpt_tbl(i).trans_to_receipt_rate);
1438 log_debug('p_receipt_date = ' || l_receipt_date);
1439 log_debug ('p_org_id = ' || l_org_id);
1440 IF is_valid_reference('CONS-INVOICE',
1441 l_orig_rcpt_tbl (i).invoice_number,
1442 l_currency_code1,
1443 l_trans_req_id,
1444 l_org_id,
1445 i,
1446 'PAYMENT',
1447 'AMT APP',
1448 'AMT APP FROM',
1449 l_customer_number,
1450 l_tmp_amount_applied,
1451 l_tmp_amount_app_from,
1452 l_returned_curr_code,
1453 l_returned_amount_to,
1454 l_returned_amount_from,
1455 l_returned_precision_to,
1456 l_returned_precision_from)
1457 THEN
1458 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
1459 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
1460 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
1461 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
1462 log_debug('invoice currency = '|| l_returned_curr_code);
1463 --Check if reference number matches any okl consolidated invoices (PRE R12 okl invoices)
1464 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv
1465 (p_api_version => l_api_version,
1466 p_init_msg_list => l_init_msg_list,
1467 x_return_status => l_return_status,
1468 x_msg_count => l_msg_count,
1469 x_msg_data => l_msg_data,
1470 -- changed for bug 5391874
1471 -- p_customer_num => l_customer_number,
1472 p_customer_num => null,
1473 p_cons_inv => l_orig_rcpt_tbl (i).invoice_number,
1474 p_currency_code => l_currency_code,
1475 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
1476 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
1477 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
1478 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
1479 p_receipt_date => l_receipt_date,
1480 p_org_id => l_org_id,
1481 x_appl_tbl => l_okl_rcpt_tbl,
1482 x_onacc_amount => l_onacc_amount,
1483 x_unapply_amount => l_unapply_amount
1484 );
1485 log_debug
1486 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv return status = '
1487 || l_return_status
1488 );
1489 log_debug
1490 ( 'Number of invoices identified for application purpose = '
1491 || l_okl_rcpt_tbl.COUNT
1492 );
1493
1494 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
1495 l_onacc_unapp_exist := 'Y';
1496 ELSE
1497 l_onacc_unapp_exist := 'N';
1498 END IF;
1499
1500 log_debug ( ' On-Account / Unapplied amount exists : '
1501 || l_onacc_unapp_exist
1502 );
1503 END IF;
1504 END IF;
1505
1506 IF l_okl_rcpt_tbl.COUNT = 0
1507 AND l_valid_ar_reference = 'N'
1508 AND l_onacc_unapp_exist = 'N' THEN
1509 log_debug
1510 ('Invoice Reference did not match to any OKL Consolidated Invoice Number'
1511 );
1512 log_debug
1513 ('Trying to match Invocie Reference to OKL Contract Number.'
1514 );
1515 log_debug
1516 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract with parameters: '
1517 );
1518 log_debug ('p_customer_num = ' || l_customer_number);
1519 log_debug ( 'p_contract_num = '
1520 || l_orig_rcpt_tbl (i).invoice_number
1521 );
1522 log_debug ('p_currency_code = ' || l_currency_code);
1523 log_debug ( 'p_rcpt_amount = '
1524 || l_orig_rcpt_tbl (i).amount_applied
1525 );
1526 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
1527 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
1528 log_debug('p_inv_to_rct_rate = '|| l_orig_rcpt_tbl(i).trans_to_receipt_rate);
1529 log_debug('p_receipt_date = ' || l_receipt_date);
1530 log_debug ('p_org_id = ' || l_org_id);
1531 IF is_valid_reference('CONTRACT',
1532 l_orig_rcpt_tbl (i).invoice_number,
1533 l_currency_code, -- bug 7018894. from l_currency_code1
1534 l_trans_req_id,
1535 l_org_id,
1536 i,
1537 'PAYMENT',
1538 'AMT APP',
1539 'AMT APP FROM',
1540 l_customer_number,
1541 l_tmp_amount_applied,
1542 l_tmp_amount_app_from,
1543 l_returned_curr_code,
1544 l_returned_amount_to,
1545 l_returned_amount_from,
1546 l_returned_precision_to,
1547 l_returned_precision_from)
1548 THEN
1549 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
1550 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
1551 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
1552 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
1553 log_debug('invoice currency = '|| l_returned_curr_code);
1554 --Check if reference number matches any okl contracts
1555 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract
1556 (p_api_version => l_api_version,
1557 p_init_msg_list => l_init_msg_list,
1558 x_return_status => l_return_status,
1559 x_msg_count => l_msg_count,
1560 x_msg_data => l_msg_data,
1561 -- changed for bug 5391874
1562 -- p_customer_num => l_customer_number,
1563 p_customer_num => null,
1564 p_contract_num => l_orig_rcpt_tbl (i).invoice_number,
1565 p_currency_code => l_currency_code,
1566 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
1567 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
1568 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
1569 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
1570 p_receipt_date => l_receipt_date,
1571 p_org_id => l_org_id,
1572 x_appl_tbl => l_okl_rcpt_tbl,
1573 x_onacc_amount => l_onacc_amount,
1574 x_unapply_amount => l_unapply_amount
1575 );
1576 log_debug
1577 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract return status = '
1578 || l_return_status
1579 );
1580 log_debug
1581 ( 'Number of invoices identified for application purpose = '
1582 || l_okl_rcpt_tbl.COUNT
1583 );
1584
1585 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
1586 l_onacc_unapp_exist := 'Y';
1587 ELSE
1588 l_onacc_unapp_exist := 'N';
1589 END IF;
1590
1591 log_debug ( ' On-Account / Unapplied amount exists : '
1592 || l_onacc_unapp_exist
1593 );
1594 END IF;
1595 END IF;
1596
1597 --asawanka modified for llca end
1598 IF l_okl_rcpt_tbl.COUNT = 0
1599 AND l_valid_ar_reference = 'N'
1600 AND l_onacc_unapp_exist = 'N' THEN
1601 -- lets use some combination rules.
1602 log_debug
1603 ('Invoice Reference did not match to any OKL Contract Number'
1604 );
1605 log_debug ('Trying to use Search Rules');
1606 log_debug
1607 ('Calling okl_combi_cash_app_rls_pvt.handle_combi_pay with parameters: '
1608 );
1609 log_debug ('p_customer_number = ' || l_customer_number);
1610 log_debug ( 'p_rcpt_amount = '
1611 || l_orig_rcpt_tbl (i).amount_applied
1612 );
1613 log_debug ('p_org_id = ' || l_org_id);
1614
1615 IF l_tmp_currency_code IS NULL OR l_tmp_currency_code = l_currency_code
1616 THEN
1617
1618 okl_combi_cash_app_rls_pvt.handle_combi_pay
1619 (p_api_version => l_api_version,
1620 p_init_msg_list => l_init_msg_list,
1621 x_return_status => l_return_status,
1622 x_msg_count => l_msg_count,
1623 x_msg_data => l_msg_data,
1624 p_customer_number => l_customer_number,
1625 p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
1626 -- p_receipt_date => l_receipt_date,
1627 p_org_id => l_org_id,
1628 p_currency_code => l_currency_code,
1629 x_appl_tbl => l_okl_rcpt_tbl
1630 );
1631 log_debug
1632 ( 'okl_auto_cash_appl_rules_pvt.handle_combi_pay return status = '
1633 || l_return_status
1634 );
1635 log_debug
1636 ( 'Number of invoices identified for application purpose = '
1637 || l_okl_rcpt_tbl.COUNT
1638 );
1639 END IF;
1640 END IF;
1641
1642 IF l_okl_rcpt_tbl.COUNT = 0
1643 --asawanka modified llca start
1644 --commenting out l_lockbox_run_already condition as it is no longer required
1645 -- AND l_lockbox_run_already = NULL
1646 --asawanka modified for llca end
1647 AND l_valid_ar_reference = 'N'
1648 AND l_onacc_unapp_exist = 'N' THEN
1649 -- still no match, lets apply to customers oldest/newest invoice(s)
1650 log_debug ('Search Rules failed.');
1651 log_debug ('Trying to use Receipt Mismatch Rules');
1652 log_debug
1653 ('Calling okl_auto_cash_appl_rules_pvt.receipt_mismatch with parameters: '
1654 );
1655 log_debug ('p_customer_num = ' || l_customer_number);
1656 log_debug ('p_currency_code = ' || l_currency_code);
1657 log_debug ( 'p_rcpt_amount = '
1658 || l_orig_rcpt_tbl (i).amount_applied
1659 );
1660 log_debug ('p_org_id = ' || l_org_id);
1661 --asawanka modified for llca start
1662 --changed api call
1663 IF l_tmp_currency_code IS NULL OR l_tmp_currency_code = l_currency_code
1664 THEN
1665 okl_auto_cash_appl_rules_pvt.receipt_mismatch
1666 (p_api_version => l_api_version,
1667 p_init_msg_list => l_init_msg_list,
1668 x_return_status => l_return_status,
1669 x_msg_count => l_msg_count,
1670 x_msg_data => l_msg_data,
1671 p_customer_num => l_customer_number,
1672 p_currency_code => l_currency_code,
1673 p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
1674 p_org_id => l_org_id,
1675 p_receipt_date => l_receipt_date,
1676 x_appl_tbl => l_okl_rcpt_tbl,
1677 x_onacc_amount => l_onacc_amount
1678 );
1679 --asawanka modified for llca end
1680 log_debug
1681 ( 'okl_auto_cash_appl_rules_pvt.receipt_mismatch return status = '
1682 || l_return_status
1683 );
1684 log_debug
1685 ( 'Number of invoices identified for application purpose = '
1686 || l_okl_rcpt_tbl.COUNT
1687 );
1688
1689 IF (l_onacc_amount > 0) THEN
1690 l_onacc_unapp_exist := 'Y';
1691 ELSE
1692 l_onacc_unapp_exist := 'N';
1693 END IF;
1694
1695 log_debug ( ' On-Account amount exists : '
1696 || l_onacc_unapp_exist
1697 );
1698 END IF;
1699 END IF;
1700
1701 IF l_okl_rcpt_tbl.COUNT = 0
1702 AND l_customer_number IS NOT NULL
1703 AND l_onacc_unapp_exist = 'N' THEN
1704 -- nothing found for receipt reference, retain invoice ref...
1705 log_debug
1706 ('Nothing found for Invoice Reference. Retaining Invoice Refence in interface table.'
1707 );
1708 l_no_mtch_rcpt_tbl (j).invoice_number :=
1709 l_orig_rcpt_tbl (i).invoice_number;
1710 l_no_mtch_rcpt_tbl (j).amount_applied :=
1711 l_orig_rcpt_tbl (i).amount_applied;
1712 j := j + 1;
1713
1714 -- ...and check for termination quotes
1715 IF l_valid_ar_reference = 'N' THEN
1716 log_debug
1717 ('Checking if invoice reference matches any Approved termination quote number'
1718 );
1719
1720 OPEN c_get_party_id (l_customer_number);
1721
1722 FETCH c_get_party_id
1723 INTO l_party_id;
1724
1725 CLOSE c_get_party_id;
1726
1727 IF l_party_id IS NOT NULL THEN
1728 OPEN c_check_termination
1729 (l_party_id,
1730 l_receipt_date,
1731 l_days_past_quote_valid,
1732 l_orig_rcpt_tbl (i).invoice_number
1733 );
1734
1735 LOOP
1736 FETCH c_check_termination
1737 INTO l_quote_id,
1738 l_quote_date_effective_to,
1739 l_quote_number,
1740 l_quote_amount;
1741
1742 EXIT WHEN c_check_termination%NOTFOUND;
1743 log_debug
1744 ('Invoice Reference matches Termination Quote Number. Checking if amount to appply matches Termination Quote Amount'
1745 );
1746
1747 IF l_quote_amount =
1748 l_orig_rcpt_tbl (i).amount_applied THEN
1749 l_qtev_rec.ID := l_quote_id;
1750 l_qtev_rec.payment_received_yn := 'Y';
1751 l_qtev_rec.date_payment_received :=
1752 l_receipt_date;
1753 l_qtev_rec.date_effective_to :=
1754 l_quote_date_effective_to;
1755 l_qtev_rec.accepted_yn := 'Y';
1756 l_qtev_rec.preproceeds_yn := 'N';
1757 log_debug
1758 ('Amount matches. Calling api okl_am_termnt_quote_pub.terminate_quote with parameters:'
1759 );
1760 log_debug ('p_term_rec.id = '
1761 || l_qtev_rec.ID
1762 );
1763 log_debug
1764 ( 'p_term_rec.payment_received_yn = '
1765 || l_qtev_rec.payment_received_yn
1766 );
1767 log_debug
1768 ( 'p_term_rec.date_payment_received = '
1769 || l_qtev_rec.date_payment_received
1770 );
1771 log_debug
1772 ( 'p_term_rec.date_effective_to = '
1773 || l_qtev_rec.accepted_yn
1774 );
1775 log_debug ( 'p_term_rec.accepted_yn = '
1776 || l_qtev_rec.accepted_yn
1777 );
1778 log_debug ( 'p_term_rec.preproceeds_yn = '
1779 || l_qtev_rec.preproceeds_yn
1780 );
1781
1782 IF (is_debug_statement_on) THEN
1783 okl_debug_pub.log_debug
1784 (g_level_statement,
1785 l_module_name,
1786 'before okl_am_termnt_quote_pub.terminate_quote '
1787 );
1788 okl_debug_pub.log_debug
1789 (g_level_statement,
1790 l_module_name,
1791 'l_return_status: '
1792 || l_return_status
1793 );
1794 END IF;
1795
1796 okl_am_termnt_quote_pub.terminate_quote
1797 (p_api_version => l_api_version,
1798 p_init_msg_list => l_init_msg_list,
1799 x_return_status => l_return_status,
1800 x_msg_count => l_msg_count,
1801 x_msg_data => l_msg_data,
1802 p_term_rec => l_qtev_rec,
1803 x_term_rec => x_qtev_rec,
1804 x_err_msg => l_err_msg
1805 );
1806 log_debug
1807 ( 'okl_am_termnt_quote_pub.terminate_quote return status = '
1808 || l_return_status
1809 );
1810
1811 IF (is_debug_statement_on) THEN
1812 okl_debug_pub.log_debug
1813 (g_level_statement,
1814 l_module_name,
1815 'After okl_am_termnt_quote_pub.terminate_quote '
1816 );
1817 okl_debug_pub.log_debug
1818 (g_level_statement,
1819 l_module_name,
1820 'l_return_status: '
1821 || l_return_status
1822 );
1823 END IF;
1824
1825 EXIT;
1826 END IF;
1827 END LOOP;
1828
1829 CLOSE c_check_termination;
1830 END IF;
1831 END IF;
1832 ELSIF l_okl_rcpt_tbl.COUNT > 0 THEN
1833 -- we found some invoices to apply
1834 log_debug
1835 ('Invoice Appliactions identified. Processing Further...'
1836 );
1837 k := l_okl_rcpt_tbl.FIRST;
1838
1839 LOOP
1840 -- (3) Loop through all new invoice(s)/invoice amount(s)
1841 l_hdr_idx := k;
1842 l_amt1_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 1');
1843 l_amt_from1_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 1');
1844 l_new_invoice1 :=
1845 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1846 l_new_amount_applied1 :=
1847 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1848 * POWER (10, l_amt1_prec);
1849 l_new_amount_applied_from1 :=
1850 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1851 * POWER (10, l_amt_from1_prec);
1852 l_new_currency_code1 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1853 l_new_trans_to_rct_rate1 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1854
1855 k := k + 1;
1856 l_amt2_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 2');
1857 l_amt_from2_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 2');
1858 l_new_invoice2 :=
1859 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1860 l_new_amount_applied2 :=
1861 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1862 * POWER (10, l_amt2_prec);
1863 l_new_amount_applied_from2 :=
1864 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1865 * POWER (10, l_amt_from2_prec);
1866 l_new_currency_code2 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1867 l_new_trans_to_rct_rate2 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1868
1869 k := k + 1;
1870 l_amt3_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 3');
1871 l_amt_from3_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 3');
1872 l_new_invoice3 :=
1873 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1874 l_new_amount_applied3 :=
1875 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1876 * POWER (10, l_amt3_prec);
1877 l_new_amount_applied_from3 :=
1878 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1879 * POWER (10, l_amt_from3_prec);
1880 l_new_currency_code3 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1881 l_new_trans_to_rct_rate3 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1882
1883 k := k + 1;
1884 l_amt4_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 4');
1885 l_amt_from4_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 4');
1886 l_new_invoice4 :=
1887 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1888 l_new_amount_applied4 :=
1889 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1890 * POWER (10, l_amt4_prec);
1891 l_new_amount_applied_from4 :=
1892 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1893 * POWER (10, l_amt_from4_prec);
1894 l_new_currency_code4 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1895 l_new_trans_to_rct_rate4 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1896
1897 k := k + 1;
1898 l_amt5_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 5');
1899 l_amt_from5_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 5');
1900 l_new_invoice5 :=
1901 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1902 l_new_amount_applied5 :=
1903 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1904 * POWER (10, l_amt5_prec);
1905 l_new_amount_applied_from5 :=
1906 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1907 * POWER (10, l_amt_from5_prec);
1908 l_new_currency_code5 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1909 l_new_trans_to_rct_rate5 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1910
1911 k := k + 1;
1912 l_amt6_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 6');
1913 l_amt_from6_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 6');
1914 l_new_invoice6 :=
1915 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1916 l_new_amount_applied6 :=
1917 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1918 * POWER (10, l_amt6_prec);
1919 l_new_amount_applied_from6 :=
1920 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1921 * POWER (10, l_amt_from6_prec);
1922 l_new_currency_code6 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1923 l_new_trans_to_rct_rate6 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1924
1925 k := k + 1;
1926 l_amt7_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 7');
1927 l_amt_from7_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 7');
1928 l_new_invoice7 :=
1929 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1930 l_new_amount_applied7 :=
1931 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1932 * POWER (10, l_amt7_prec);
1933 l_new_amount_applied_from7 :=
1934 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1935 * POWER (10, l_amt_from7_prec);
1936 l_new_currency_code7 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1937 l_new_trans_to_rct_rate7 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1938
1939 k := k + 1;
1940 l_amt8_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP 8');
1941 l_amt_from8_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'PAYMENT','AMT APP FROM 8');
1942 l_new_invoice8 :=
1943 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
1944 l_new_amount_applied8 :=
1945 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
1946 * POWER (10, l_amt8_prec);
1947 l_new_amount_applied_from8 :=
1948 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
1949 * POWER (10, l_amt_from8_prec);
1950 l_new_currency_code8 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
1951 l_new_trans_to_rct_rate8 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
1952
1953 SELECT ar_payments_interface_s.NEXTVAL
1954 INTO l_transmission_rec_id_of
1955 FROM DUAL;
1956
1957 log_debug
1958 ('Inserting overflow record with application information for the current payment record'
1959 );
1960 log_debug
1961 ( 'Transmission Record Id of new overflow record = '
1962 || l_transmission_rec_id_of
1963 );
1964
1965 INSERT INTO ar_payments_interface_all
1966 (transmission_record_id,
1967 item_number,
1968 record_type,
1969 status,
1970 transmission_id,
1971 transmission_request_id,
1972 lockbox_number,
1973 batch_name,
1974 invoice1,
1975 amount_applied1,
1976 amount_applied_from1,
1977 invoice_currency_code1,
1978 trans_to_receipt_rate1,
1979 invoice2,
1980 amount_applied2,
1981 amount_applied_from2,
1982 invoice_currency_code2,
1983 trans_to_receipt_rate2,
1984 invoice3,
1985 amount_applied3,
1986 amount_applied_from3,
1987 invoice_currency_code3,
1988 trans_to_receipt_rate3,
1989 invoice4,
1990 amount_applied4,
1991 amount_applied_from4,
1992 invoice_currency_code4,
1993 trans_to_receipt_rate4,
1994 invoice5,
1995 amount_applied5,
1996 amount_applied_from5,
1997 invoice_currency_code5,
1998 trans_to_receipt_rate5,
1999 invoice6,
2000 amount_applied6,
2001 amount_applied_from6,
2002 invoice_currency_code6,
2003 trans_to_receipt_rate6,
2004 invoice7,
2005 amount_applied7,
2006 amount_applied_from7,
2007 invoice_currency_code7,
2008 trans_to_receipt_rate7,
2009 invoice8,
2010 amount_applied8,
2011 amount_applied_from8,
2012 invoice_currency_code8,
2013 trans_to_receipt_rate8,
2014 org_id,
2015 creation_date,
2016 last_update_date
2017 )
2018 VALUES (l_transmission_rec_id_of,
2019 l_item_number,
2020 l_overflow,
2021 l_status,
2022 l_transmission_id,
2023 l_trans_req_id,
2024 l_lockbox_number,
2025 l_batch_name,
2026 l_new_invoice1,
2027 l_new_amount_applied1,
2028 l_new_amount_applied_from1,
2029 l_new_currency_code1,
2030 l_new_trans_to_rct_rate1,
2031 l_new_invoice2,
2032 l_new_amount_applied2,
2033 l_new_amount_applied_from2,
2034 l_new_currency_code2,
2035 l_new_trans_to_rct_rate2,
2036 l_new_invoice3,
2037 l_new_amount_applied3,
2038 l_new_amount_applied_from3,
2039 l_new_currency_code3,
2040 l_new_trans_to_rct_rate3,
2041 l_new_invoice4,
2042 l_new_amount_applied4,
2043 l_new_amount_applied_from4,
2044 l_new_currency_code4,
2045 l_new_trans_to_rct_rate4,
2046 l_new_invoice5,
2047 l_new_amount_applied5,
2048 l_new_amount_applied_from5,
2049 l_new_currency_code5,
2050 l_new_trans_to_rct_rate5,
2051 l_new_invoice6,
2052 l_new_amount_applied6,
2053 l_new_amount_applied_from6,
2054 l_new_currency_code6,
2055 l_new_trans_to_rct_rate6,
2056 l_new_invoice7,
2057 l_new_amount_applied7,
2058 l_new_amount_applied_from7,
2059 l_new_currency_code7,
2060 l_new_trans_to_rct_rate7,
2061 l_new_invoice8,
2062 l_new_amount_applied8,
2063 l_new_amount_applied_from8,
2064 l_new_currency_code8,
2065 l_new_trans_to_rct_rate8,
2066 l_org_id,
2067 SYSDATE,
2068 TRUNC (SYSDATE)
2069 );
2070
2071 /*************************************************************************************
2072 insert line level details in AR table here
2073 *************************************************************************************/
2074 log_debug
2075 ('Processing line level application data for currently inserted overflow record'
2076 );
2077
2078 FOR l_inv_indx IN l_hdr_idx .. k
2079 LOOP
2080 IF l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number IS NOT NULL THEN
2081 l_linelevel_app :=
2082 get_line_level_app
2083 (l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_id,
2084 l_org_id
2085 );
2086
2087 IF l_linelevel_app = 'Y' THEN
2088 /* -- first, delete any existing line level application data
2089 DELETE AR_PMTS_INTERFACE_LINE_DETAILS
2090 WHERE transmission_req_id = l_trans_req_id
2091 AND transmission_record_id = l_transmission_record_id
2092 AND invoice_number = l_okl_rcpt_tbl(l_inv_indx).inv_hdr_rec.invoice_number;*/-- now create line level application data
2093 IF l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl.COUNT >
2094 0 THEN
2095 log_debug
2096 ( 'Inserting line level application data for Invoice Number = '
2097 || l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number
2098 );
2099
2100 FOR l_line_indx IN
2101 l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl.FIRST .. l_okl_rcpt_tbl
2102 (l_inv_indx
2103 ).inv_lines_tbl.LAST
2104 LOOP
2105 --START: Fixed as part of bug 6780241 by nikshah
2106 if (l_currency_code = l_okl_rcpt_tbl(l_inv_indx).inv_hdr_rec.invoice_currency_code) then
2107 l_amount_in_inv_curr :=l_okl_rcpt_tbl(l_inv_indx).inv_lines_tbl(l_line_indx).amount_applied;
2108 l_amount_in_rct_curr := null;
2109 else
2110 l_amount_in_inv_curr := null;
2111 l_amount_in_rct_curr := l_okl_rcpt_tbl(l_inv_indx).inv_lines_tbl(l_line_indx).amount_applied;
2112 end if;
2113
2114 INSERT INTO ar_pmts_interface_line_details
2115 (status,
2116 transmission_request_id,
2117 transmission_record_id,
2118 invoice_number,
2119 apply_to,
2120 allocated_receipt_amount,
2121 amount_applied
2122 )
2123 VALUES ('AR_PLB_NEW_RECORD',
2124 l_trans_req_id,
2125 l_transmission_rec_id_of,
2126 l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number,
2127 l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl
2128 (l_line_indx).invoice_line_number,
2129 l_amount_in_inv_curr,
2130 l_amount_in_rct_curr
2131 );
2132 --END: Fixed as part of bug 6780241 by nikshah
2133 END LOOP;
2134 END IF;
2135 END IF;
2136 END IF;
2137 END LOOP;
2138
2139 EXIT WHEN k = (l_okl_rcpt_tbl.LAST);
2140 -- records should always be in mutiples of 8.
2141 k := k + 1;
2142 -- k := k + 1;
2143 -- EXIT WHEN k = (l_okl_rcpt_tbl.LAST + 1); -- records should always be in mutiples of 8.
2144 END LOOP;
2145 -- (3) end looping through all new invoice(s)/invoice amount(s)
2146 ELSE
2147 log_debug
2148 ('Invoice Reference matches valid non okl AR Invoice Number. Skipping the invoice reference'
2149 );
2150 END IF;
2151 ELSE
2152 --lock box run already so retain the invoice reference
2153 log_debug
2154 ('Line Level data already exists for this invoice reference. Skipping the invoice reference'
2155 );
2156 l_prec_to_use := 0;
2157 l_prec_to_use_from := 0;
2158
2159 IF i = 1 THEN
2160 l_prec_to_use := l_amt1_prec;
2161 l_prec_to_use_from := l_amt_from1_prec;
2162 ELSIF i = 2 THEN
2163 l_prec_to_use := l_amt2_prec;
2164 l_prec_to_use_from := l_amt_from2_prec;
2165 ELSIF i = 3 THEN
2166 l_prec_to_use := l_amt3_prec;
2167 l_prec_to_use_from := l_amt_from3_prec;
2168 ELSIF i = 4 THEN
2169 l_prec_to_use := l_amt4_prec;
2170 l_prec_to_use_from := l_amt_from4_prec;
2171 ELSIF i = 5 THEN
2172 l_prec_to_use := l_amt5_prec;
2173 l_prec_to_use_from := l_amt_from5_prec;
2174 ELSIF i = 6 THEN
2175 l_prec_to_use := l_amt6_prec;
2176 l_prec_to_use_from := l_amt_from6_prec;
2177 ELSIF i = 7 THEN
2178 l_prec_to_use := l_amt7_prec;
2179 l_prec_to_use_from := l_amt_from7_prec;
2180 ELSIF i = 8 THEN
2181 l_prec_to_use := l_amt8_prec;
2182 l_prec_to_use_from := l_amt_from8_prec;
2183 END IF;
2184
2185 l_no_mtch_rcpt_tbl (j).invoice_number :=
2186 l_orig_rcpt_tbl (i).invoice_number;
2187 l_no_mtch_rcpt_tbl (j).amount_applied :=
2188 l_orig_rcpt_tbl (i).amount_applied
2189 * POWER (10, l_prec_to_use);
2190 l_no_mtch_rcpt_tbl(j).amount_applied_from := l_orig_rcpt_tbl(i).amount_applied_from * power(10,l_prec_to_use_from);
2191 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := l_orig_rcpt_tbl(i).trans_to_receipt_rate;
2192 j := j + 1;
2193 END IF; -- lock box run already
2194
2195 EXIT WHEN i = 8;
2196 -- move to next payment record if all 8 invoice references are processed
2197 i := i + 1;
2198 -- move to next invoice reference in this payment record
2199 ELSE -- no invoice reference found
2200 IF l_orig_rcpt_tbl (i).invoice_number IS NOT NULL
2201 AND l_orig_rcpt_tbl (i).amount_applied IS NOT NULL THEN
2202 log_debug
2203 ('Invoice Reference exists but amount to apply is zero after considering precision. Keeping Invoice reference.'
2204 );
2205 l_prec_to_use := 0;
2206 l_prec_to_use_from := 0;
2207
2208 IF i = 1 THEN
2209 l_prec_to_use := l_amt1_prec;
2210 l_prec_to_use_from := l_amt_from1_prec;
2211 ELSIF i = 2 THEN
2212 l_prec_to_use := l_amt2_prec;
2213 l_prec_to_use_from := l_amt_from2_prec;
2214 ELSIF i = 3 THEN
2215 l_prec_to_use := l_amt3_prec;
2216 l_prec_to_use_from := l_amt_from3_prec;
2217 ELSIF i = 4 THEN
2218 l_prec_to_use := l_amt4_prec;
2219 l_prec_to_use_from := l_amt_from4_prec;
2220 ELSIF i = 5 THEN
2221 l_prec_to_use := l_amt5_prec;
2222 l_prec_to_use_from := l_amt_from5_prec;
2223 ELSIF i = 6 THEN
2224 l_prec_to_use := l_amt6_prec;
2225 l_prec_to_use_from := l_amt_from6_prec;
2226 ELSIF i = 7 THEN
2227 l_prec_to_use := l_amt7_prec;
2228 l_prec_to_use_from := l_amt_from7_prec;
2229 ELSIF i = 8 THEN
2230 l_prec_to_use := l_amt8_prec;
2231 l_prec_to_use_from := l_amt_from8_prec;
2232 END IF;
2233
2234 l_no_mtch_rcpt_tbl (j).invoice_number :=
2235 l_orig_rcpt_tbl (i).invoice_number;
2236 l_no_mtch_rcpt_tbl (j).amount_applied :=
2237 l_orig_rcpt_tbl (i).amount_applied
2238 * POWER (10, l_prec_to_use);
2239 l_no_mtch_rcpt_tbl(j).amount_applied_from := l_orig_rcpt_tbl(i).amount_applied_from * power(10,l_prec_to_use_from);
2240 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := l_orig_rcpt_tbl(i).trans_to_receipt_rate;
2241 j := j + 1;
2242 END IF;
2243
2244 EXIT WHEN i = 8;
2245 -- move to next payment record if all 8 invoice references are processed
2246 i := i + 1;
2247 -- move to next invoice reference in this payment record
2248 END IF;
2249 END LOOP;
2250 -- (2) -- end processing payment record. use context of payment rec to process ovrflw rec.
2251
2252 log_debug ('All invoice references in payment record processed. ');
2253 log_debug
2254 ('Nulling out invoice references which were matched successfully and retaining those which did not match at all'
2255 );
2256 j := 1;
2257
2258 -- need to keep references that returned nothing and
2259 -- need to null out invoice references for which we found match
2260 UPDATE ar_payments_interface_all
2261 SET invoice1 = l_no_mtch_rcpt_tbl (j).invoice_number,
2262 amount_applied1 = l_no_mtch_rcpt_tbl (j).amount_applied,
2263 resolved_matching_number1 =
2264 l_no_mtch_rcpt_tbl (j).invoice_number,
2265 amount_applied_from1 = l_no_mtch_rcpt_tbl(j).amount_applied_from,
2266 trans_to_receipt_rate1 = l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate,
2267 invoice2 = l_no_mtch_rcpt_tbl (j + 1).invoice_number,
2268 amount_applied2 = l_no_mtch_rcpt_tbl (j + 1).amount_applied,
2269 resolved_matching_number2 =
2270 l_no_mtch_rcpt_tbl (j + 1).invoice_number,
2271 amount_applied_from2 = l_no_mtch_rcpt_tbl(j + 1).amount_applied_from,
2272 trans_to_receipt_rate2 = l_no_mtch_rcpt_tbl(j + 1).trans_to_receipt_rate,
2273 invoice3 = l_no_mtch_rcpt_tbl (j + 2).invoice_number,
2274 amount_applied3 = l_no_mtch_rcpt_tbl (j + 2).amount_applied,
2275 resolved_matching_number3 =
2276 l_no_mtch_rcpt_tbl (j + 2).invoice_number,
2277 amount_applied_from3 = l_no_mtch_rcpt_tbl(j + 2).amount_applied_from,
2278 trans_to_receipt_rate3 = l_no_mtch_rcpt_tbl(j + 2).trans_to_receipt_rate,
2279 invoice4 = l_no_mtch_rcpt_tbl (j + 3).invoice_number,
2280 amount_applied4 = l_no_mtch_rcpt_tbl (j + 3).amount_applied,
2281 resolved_matching_number4 =
2282 l_no_mtch_rcpt_tbl (j + 3).invoice_number,
2283 amount_applied_from4 = l_no_mtch_rcpt_tbl(j + 3).amount_applied_from,
2284 trans_to_receipt_rate4 = l_no_mtch_rcpt_tbl(j + 3).trans_to_receipt_rate,
2285 invoice5 = l_no_mtch_rcpt_tbl (j + 4).invoice_number,
2286 amount_applied5 = l_no_mtch_rcpt_tbl (j + 4).amount_applied,
2287 resolved_matching_number5 =
2288 l_no_mtch_rcpt_tbl (j + 4).invoice_number,
2289 amount_applied_from5 = l_no_mtch_rcpt_tbl(j + 4).amount_applied_from,
2290 trans_to_receipt_rate5 = l_no_mtch_rcpt_tbl(j + 4).trans_to_receipt_rate,
2291 invoice6 = l_no_mtch_rcpt_tbl (j + 5).invoice_number,
2292 amount_applied6 = l_no_mtch_rcpt_tbl (j + 5).amount_applied,
2293 resolved_matching_number6 =
2294 l_no_mtch_rcpt_tbl (j + 5).invoice_number,
2295 amount_applied_from6 = l_no_mtch_rcpt_tbl(j + 5).amount_applied_from,
2296 trans_to_receipt_rate6 = l_no_mtch_rcpt_tbl(j + 5).trans_to_receipt_rate,
2297 invoice7 = l_no_mtch_rcpt_tbl (j + 6).invoice_number,
2298 amount_applied7 = l_no_mtch_rcpt_tbl (j + 6).amount_applied,
2299 resolved_matching_number7 =
2300 l_no_mtch_rcpt_tbl (j + 6).invoice_number,
2301 amount_applied_from7 = l_no_mtch_rcpt_tbl(j + 6).amount_applied_from,
2302 trans_to_receipt_rate7 = l_no_mtch_rcpt_tbl(j + 6).trans_to_receipt_rate,
2303 invoice8 = l_no_mtch_rcpt_tbl (j + 7).invoice_number,
2304 amount_applied8 = l_no_mtch_rcpt_tbl (j + 7).amount_applied,
2305 resolved_matching_number8 =
2306 l_no_mtch_rcpt_tbl (j + 7).invoice_number,
2307 amount_applied_from8 = l_no_mtch_rcpt_tbl(j + 7).amount_applied_from,
2308 trans_to_receipt_rate8 = l_no_mtch_rcpt_tbl(j + 7).trans_to_receipt_rate
2309 WHERE transmission_record_id = l_transmission_record_id;
2310
2311 -- now retrieve all overflow records for
2312 -- payment in question.
2313 log_debug
2314 ('Processing all overflow records for the current payment record');
2315 OPEN c_get_ovrflw_recs (l_trans_req_id,
2316 l_overflow,
2317 l_item_number,
2318 l_batch_name
2319 );
2320
2321 LOOP -- (2) loop through overflow recs
2322 FETCH c_get_ovrflw_recs
2323 INTO l_transmission_record_id,
2324 -- l_currency_code, -- use currency from payment record. currency in overflow record may not be present
2325 l_invoice1,
2326 l_invoice2,
2327 l_invoice3,
2328 l_invoice4,
2329 l_invoice5,
2330 l_invoice6,
2331 l_invoice7,
2332 l_invoice8,
2333 l_amount_applied1,
2334 l_amount_applied2,
2335 l_amount_applied3,
2336 l_amount_applied4,
2337 l_amount_applied5,
2338 l_amount_applied6,
2339 l_amount_applied7,
2340 l_amount_applied8,
2341 l_amount_app_from1,
2342 l_amount_app_from2,
2343 l_amount_app_from3,
2344 l_amount_app_from4,
2345 l_amount_app_from5,
2346 l_amount_app_from6,
2347 l_amount_app_from7,
2348 l_amount_app_from8,
2349 l_trans_to_receipt_rate1,
2350 l_trans_to_receipt_rate2,
2351 l_trans_to_receipt_rate3,
2352 l_trans_to_receipt_rate4,
2353 l_trans_to_receipt_rate5,
2354 l_trans_to_receipt_rate6,
2355 l_trans_to_receipt_rate7,
2356 l_trans_to_receipt_rate8,
2357 l_currency_code1,
2358 l_currency_code2,
2359 l_currency_code3,
2360 l_currency_code4,
2361 l_currency_code5,
2362 l_currency_code6,
2363 l_currency_code7,
2364 l_currency_code8;
2365
2366 EXIT WHEN c_get_ovrflw_recs%NOTFOUND;
2367 log_debug ('Fetching precisions for amount columns');
2368 --asawanka modified for llca start
2369 -- fetch format precision for amounts in interface table for the receipt currency
2370
2371 l_amt1_prec := get_precision (NVL(l_currency_code1,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 1');
2372 l_amt2_prec := get_precision (NVL(l_currency_code2,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 2');
2373 l_amt3_prec := get_precision (NVL(l_currency_code3,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 3');
2374 l_amt4_prec := get_precision (NVL(l_currency_code4,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 4');
2375 l_amt5_prec := get_precision (NVL(l_currency_code5,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 5');
2376 l_amt6_prec := get_precision (NVL(l_currency_code6,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 6');
2377 l_amt7_prec := get_precision (NVL(l_currency_code7,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 7');
2378 l_amt8_prec := get_precision (NVL(l_currency_code8,l_currency_code),p_trans_req_id,'OVRFLW PAYMENT','AMT APP 8');
2379
2380 --asawanka modified for llca end
2381
2382 -- process overflow record invoice(s)
2383 -- new overflow record(s) for each payment record invoice reference
2384 -- update payment record once and for all.
2385 i := 1;
2386 j := 1;
2387 -- initialize all pl/sql tables used by this process
2388 l_orig_rcpt_tbl := l_initialize_table;
2389 l_no_mtch_rcpt_tbl := l_initialize_table;
2390 l_okl_rcpt_tbl := l_okl_init_tbl;
2391
2392 l_orig_rcpt_tbl (i).invoice_number := l_invoice1;
2393 l_orig_rcpt_tbl (i).amount_applied :=
2394 ROUND (l_amount_applied1 / POWER (10, l_amt1_prec),
2395 l_amt1_prec);
2396 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from1/power(10,l_amt_from1_prec),l_amt_from1_prec);
2397 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate1;
2398 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2399 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2400 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2401 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2402
2403 i := i + 1;
2404 j := j + 1;
2405 l_orig_rcpt_tbl (i).invoice_number := l_invoice2;
2406 l_orig_rcpt_tbl (i).amount_applied :=
2407 ROUND (l_amount_applied2 / POWER (10, l_amt2_prec),
2408 l_amt2_prec);
2409 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from2/power(10,l_amt_from2_prec),l_amt_from2_prec);
2410 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate2;
2411 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2412 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2413 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2414 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2415
2416 i := i + 1;
2417 j := j + 1;
2418 l_orig_rcpt_tbl (i).invoice_number := l_invoice3;
2419 l_orig_rcpt_tbl (i).amount_applied :=
2420 ROUND (l_amount_applied3 / POWER (10, l_amt3_prec),
2421 l_amt3_prec);
2422 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from3/power(10,l_amt_from3_prec),l_amt_from3_prec);
2423 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate3;
2424 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2425 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2426 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2427 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2428
2429 i := i + 1;
2430 j := j + 1;
2431 l_orig_rcpt_tbl (i).invoice_number := l_invoice4;
2432 l_orig_rcpt_tbl (i).amount_applied :=
2433 ROUND (l_amount_applied4 / POWER (10, l_amt4_prec),
2434 l_amt4_prec);
2435 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from4/power(10,l_amt_from4_prec),l_amt_from4_prec);
2436 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate4;
2437 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2438 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2439 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2440 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2441
2442 i := i + 1;
2443 j := j + 1;
2444 l_orig_rcpt_tbl (i).invoice_number := l_invoice5;
2445 l_orig_rcpt_tbl (i).amount_applied :=
2446 ROUND (l_amount_applied5 / POWER (10, l_amt5_prec),
2447 l_amt5_prec);
2448 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from5/power(10,l_amt_from5_prec),l_amt_from5_prec);
2449 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate5;
2450 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2451 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2452 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2453 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2454
2455 i := i + 1;
2456 j := j + 1;
2457 l_orig_rcpt_tbl (i).invoice_number := l_invoice6;
2458 l_orig_rcpt_tbl (i).amount_applied :=
2459 ROUND (l_amount_applied6 / POWER (10, l_amt6_prec),
2460 l_amt6_prec);
2461 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from6/power(10,l_amt_from6_prec),l_amt_from6_prec);
2462 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate6;
2463 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2464 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2465 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2466 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2467
2468 i := i + 1;
2469 j := j + 1;
2470 l_orig_rcpt_tbl (i).invoice_number := l_invoice7;
2471 l_orig_rcpt_tbl (i).amount_applied :=
2472 ROUND (l_amount_applied7 / POWER (10, l_amt7_prec),
2473 l_amt7_prec);
2474 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from7/power(10,l_amt_from7_prec),l_amt_from7_prec);
2475 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate7;
2476 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2477 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2478 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2479 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2480
2481 i := i + 1;
2482 j := j + 1;
2483 l_orig_rcpt_tbl (i).invoice_number := l_invoice8;
2484 l_orig_rcpt_tbl (i).amount_applied :=
2485 ROUND (l_amount_applied8 / POWER (10, l_amt8_prec),
2486 l_amt8_prec);
2487 l_orig_rcpt_tbl(i).amount_applied_from := round(l_amount_app_from8/power(10,l_amt_from8_prec),l_amt_from8_prec);
2488 l_orig_rcpt_tbl(i).trans_to_receipt_rate := l_trans_to_receipt_rate8;
2489 l_no_mtch_rcpt_tbl (j).invoice_number := NULL;
2490 l_no_mtch_rcpt_tbl (j).amount_applied := NULL;
2491 l_no_mtch_rcpt_tbl(j).amount_applied_from := NULL;
2492 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := NULL;
2493
2494 i := 1;
2495 j := 1;
2496 log_debug
2497 ( 'Processing invoice references in this overflow record. Transmission record id = '
2498 || l_transmission_record_id
2499 );
2500
2501 LOOP
2502 -- (3) Loop through all overflow record invoice/payment references
2503 l_okl_rcpt_tbl := l_okl_init_tbl;
2504
2505 select decode(i,1,l_amount_applied1,2,l_amount_applied2,
2506 3, l_amount_applied3,4,l_amount_applied4,
2507 5,l_amount_applied5,6,l_amount_applied6,
2508 7,l_amount_applied7,8,l_amount_applied8),
2509 decode(i,1,l_amount_app_from1,2,l_amount_app_from2,
2510 3, l_amount_app_from3,4,l_amount_app_from4,
2511 5,l_amount_app_from5,6,l_amount_app_from6,
2512 7,l_amount_app_from7,8,l_amount_app_from8),
2513 decode(i,1,l_currency_code1,2,l_currency_code2,
2514 3,l_currency_code3,4,l_currency_code4,
2515 5,l_currency_code5,6,l_currency_code6,
2516 7,l_currency_code7,8,l_currency_code8)
2517 into l_tmp_amount_applied, l_tmp_amount_app_from, l_tmp_currency_code
2518 from dual;
2519
2520 IF l_orig_rcpt_tbl (i).invoice_number IS NOT NULL
2521 AND ( l_tmp_amount_applied IS NOT NULL OR l_tmp_amount_app_from IS NOT NULL)
2522 AND ( l_tmp_amount_applied <> 0 OR l_tmp_amount_app_from <> 0 ) THEN
2523 log_debug ( 'Invoice Reference = '
2524 || l_orig_rcpt_tbl (i).invoice_number
2525 );
2526 log_debug ( 'Amount to apply = '
2527 || l_tmp_amount_applied
2528 );
2529 log_debug ( 'Amount to apply from = '
2530 || l_tmp_amount_app_from
2531 );
2532 log_debug ( 'Trans to receipt rate = '
2533 || l_orig_rcpt_tbl (i).trans_to_receipt_rate
2534 );
2535
2536 -- invoke okl standard cash application rules
2537 ----------------------------------------------------------------------
2538 -- check to see if given invoice reference maps to a valid non okl ar invoice.
2539 -- if so then this reference should be left in lockbox ...
2540 -- bug ref: 3068497
2541 IF valid_ar_reference (l_orig_rcpt_tbl (i).invoice_number,
2542 l_org_id
2543 ) THEN
2544 l_valid_ar_reference := 'Y';
2545 ELSE
2546 l_valid_ar_reference := 'N';
2547 END IF;
2548
2549 ----------------------------------------------------------------------
2550 --asawanka modified for llca start
2551 -- check if line level data exists for this inv ref
2552 l_lockbox_run_already := 'N';
2553
2554 OPEN c_lckbx_status (l_trans_req_id,
2555 l_transmission_record_id,
2556 l_orig_rcpt_tbl (i).invoice_number
2557 );
2558
2559 FETCH c_lckbx_status
2560 INTO l_lockbox_run_already;
2561
2562 CLOSE c_lckbx_status;
2563
2564 -- Start of wraper code generated automatically by Debug code generator for okl_auto_cash_appl_rules_pvt.auto_cash_app
2565 IF (l_debug_enabled = 'Y') THEN
2566 l_level_statement := fnd_log.level_procedure;
2567 is_debug_statement_on :=
2568 okl_debug_pub.check_log_on (l_module,
2569 l_level_statement
2570 );
2571 END IF;
2572
2573 --asawanka modified for llca start
2574 -- if line level data already exists for this invoice then do not do any processing for this invoice
2575 IF l_lockbox_run_already = 'N' THEN
2576 log_debug
2577 ('No line level data for invoice reference. Further processing invoice reference'
2578 );
2579
2580 --asawanka modified for llca start
2581 --changed api calls.
2582 IF l_valid_ar_reference = 'N' THEN
2583 log_debug
2584 ('Invoice reference is not valid non OKL AR invoice Number.'
2585 );
2586 log_debug
2587 ('Trying to match Invocie Reference to OKL related AR invoice. ( Post R12 OKL Invoice)'
2588 );
2589 log_debug
2590 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv with parameters: '
2591 );
2592 log_debug ('p_customer_num = ' || l_customer_number);
2593 log_debug ( 'p_arinv_number = '
2594 || l_orig_rcpt_tbl (i).invoice_number
2595 );
2596 log_debug ('p_currency_code = ' || l_currency_code);
2597 log_debug ( 'amount applied = '
2598 || l_orig_rcpt_tbl (i).amount_applied
2599 );
2600 log_debug ( 'amount applied from = '
2601 || l_orig_rcpt_tbl (i).amount_applied_from
2602 );
2603 log_debug ( 'trans to receipt rate = '
2604 || l_orig_rcpt_tbl (i).trans_to_receipt_rate
2605 );
2606 log_debug ('p_org_id = ' || l_org_id);
2607
2608 IF is_valid_reference('INVOICE',
2609 l_orig_rcpt_tbl (i).invoice_number,
2610 l_currency_code1,
2611 l_trans_req_id,
2612 l_org_id,
2613 i,
2614 'OVRFLW PAYMENT',
2615 'AMT APP',
2616 'AMT APP FROM',
2617 l_customer_number,
2618 l_tmp_amount_applied,
2619 l_tmp_amount_app_from,
2620 l_returned_curr_code,
2621 l_returned_amount_to,
2622 l_returned_amount_from,
2623 l_returned_precision_to,
2624 l_returned_precision_from)
2625 THEN
2626 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
2627 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
2628 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
2629 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
2630 log_debug('invoice currency = '|| l_returned_curr_code);
2631 --Check if reference number matches any okl related AR invoices(Post R12 okl invoices)
2632 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv
2633 (p_api_version => l_api_version,
2634 p_init_msg_list => l_init_msg_list,
2635 x_return_status => l_return_status,
2636 x_msg_count => l_msg_count,
2637 x_msg_data => l_msg_data,
2638 -- changed for bug 5391874
2639 -- p_customer_num => l_customer_number,
2640 p_customer_num => null,
2641 p_arinv_number => l_orig_rcpt_tbl (i).invoice_number,
2642 p_currency_code => l_currency_code,
2643 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
2644 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
2645 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
2646 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
2647 p_receipt_date => l_receipt_date,
2648 p_org_id => l_org_id,
2649 x_appl_tbl => l_okl_rcpt_tbl,
2650 x_onacc_amount => l_onacc_amount,
2651 x_unapply_amount => l_unapply_amount
2652 );
2653 log_debug
2654 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv return status = '
2655 || l_return_status
2656 );
2657 log_debug
2658 ( 'Number of invoices identified for application purpose = '
2659 || l_okl_rcpt_tbl.COUNT
2660 );
2661
2662 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
2663 l_onacc_unapp_exist := 'Y';
2664 ELSE
2665 l_onacc_unapp_exist := 'N';
2666 END IF;
2667
2668 log_debug
2669 ( ' On-Account / Unapplied amount exists : '
2670 || l_onacc_unapp_exist
2671 );
2672 END IF;
2673 END IF;
2674
2675 IF l_okl_rcpt_tbl.COUNT = 0
2676 AND l_valid_ar_reference = 'N'
2677 AND l_onacc_unapp_exist = 'N' THEN
2678 log_debug
2679 ('Invoice Reference did not match to any OKL relate AR Invoice Number'
2680 );
2681 log_debug
2682 ('Trying to match Invocie Reference to OKL Consolidated invoice. ( Pre R12 OKL Invoice)'
2683 );
2684 log_debug
2685 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv with parameters: '
2686 );
2687 log_debug ('p_customer_num = ' || l_customer_number);
2688 log_debug ( 'p_cons_inv = '
2689 || l_orig_rcpt_tbl (i).invoice_number
2690 );
2691 log_debug ('p_currency_code = ' || l_currency_code);
2692 log_debug ( 'amount applied = '
2693 || l_orig_rcpt_tbl (i).amount_applied
2694 );
2695 log_debug ( 'amount applied from = '
2696 || l_orig_rcpt_tbl (i).amount_applied_from
2697 );
2698 log_debug ( 'trans to receipt rate = '
2699 || l_orig_rcpt_tbl (i).trans_to_receipt_rate
2700 );
2701 log_debug ('p_org_id = ' || l_org_id);
2702 IF is_valid_reference('CONS-INVOICE',
2703 l_orig_rcpt_tbl (i).invoice_number,
2704 l_currency_code1,
2705 l_trans_req_id,
2706 l_org_id,
2707 i,
2708 'OVRFLW PAYMENT',
2709 'AMT APP',
2710 'AMT APP FROM',
2711 l_customer_number,
2712 l_tmp_amount_applied,
2713 l_tmp_amount_app_from,
2714 l_returned_curr_code,
2715 l_returned_amount_to,
2716 l_returned_amount_from,
2717 l_returned_precision_to,
2718 l_returned_precision_from)
2719 THEN
2720 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
2721 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
2722 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
2723 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
2724 log_debug('invoice currency = '|| l_returned_curr_code);
2725 --Check if reference number matches any okl consolidated invoices (PRE R12 okl invoices)
2726 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv
2727 (p_api_version => l_api_version,
2728 p_init_msg_list => l_init_msg_list,
2729 x_return_status => l_return_status,
2730 x_msg_count => l_msg_count,
2731 x_msg_data => l_msg_data,
2732 -- changed for bug 5391874
2733 -- p_customer_num => l_customer_number,
2734 p_customer_num => null,
2735 p_cons_inv => l_orig_rcpt_tbl (i).invoice_number,
2736 p_currency_code => l_currency_code,
2737 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
2738 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
2739 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
2740 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
2741 p_receipt_date => l_receipt_date,
2742 p_org_id => l_org_id,
2743 x_appl_tbl => l_okl_rcpt_tbl,
2744 x_onacc_amount => l_onacc_amount,
2745 x_unapply_amount => l_unapply_amount
2746 );
2747 log_debug
2748 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv return status = '
2749 || l_return_status
2750 );
2751 log_debug
2752 ( 'Number of invoices identified for application purpose = '
2753 || l_okl_rcpt_tbl.COUNT
2754 );
2755
2756 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
2757 l_onacc_unapp_exist := 'Y';
2758 ELSE
2759 l_onacc_unapp_exist := 'N';
2760 END IF;
2761
2762 log_debug
2763 ( ' On-Account / Unapplied amount exists : '
2764 || l_onacc_unapp_exist
2765 );
2766 END IF;
2767 END IF;
2768
2769 IF l_okl_rcpt_tbl.COUNT = 0
2770 AND l_valid_ar_reference = 'N'
2771 AND l_onacc_unapp_exist = 'N' THEN
2772 log_debug
2773 ('Invoice Reference did not match to any OKL Consolidated Invoice Number'
2774 );
2775 log_debug
2776 ('Trying to match Invocie Reference to OKL Contract Number.'
2777 );
2778 log_debug
2779 ('Calling okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract with parameters: '
2780 );
2781 log_debug ('p_customer_num = ' || l_customer_number);
2782 log_debug ( 'p_contract_num = '
2783 || l_orig_rcpt_tbl (i).invoice_number
2784 );
2785 log_debug ('p_currency_code = ' || l_currency_code);
2786 log_debug ( 'amount applied = '
2787 || l_orig_rcpt_tbl (i).amount_applied
2788 );
2789 log_debug ( 'amount applied from = '
2790 || l_orig_rcpt_tbl (i).amount_applied_from
2791 );
2792 log_debug ( 'trans to receipt rate = '
2793 || l_orig_rcpt_tbl (i).trans_to_receipt_rate
2794 );
2795 log_debug ('p_org_id = ' || l_org_id);
2796 IF is_valid_reference('CONTRACT',
2797 l_orig_rcpt_tbl (i).invoice_number,
2798 l_currency_code1,
2799 l_trans_req_id,
2800 l_org_id,
2801 i,
2802 'OVRFLW PAYMENT',
2803 'AMT APP',
2804 'AMT APP FROM',
2805 l_customer_number,
2806 l_tmp_amount_applied,
2807 l_tmp_amount_app_from,
2808 l_returned_curr_code,
2809 l_returned_amount_to,
2810 l_returned_amount_from,
2811 l_returned_precision_to,
2812 l_returned_precision_from)
2813 THEN
2814 l_orig_rcpt_tbl (i).amount_applied := l_returned_amount_to;
2815 l_orig_rcpt_tbl (i).amount_applied_from := l_returned_amount_from;
2816 log_debug('p_amount_app_to = '|| l_orig_rcpt_tbl(i).amount_applied);
2817 log_debug('p_amount_app_from = '|| l_orig_rcpt_tbl(i).amount_applied_from);
2818 log_debug('invoice currency = '|| l_returned_curr_code);
2819 --Check if reference number matches any okl contracts
2820 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract
2821 (p_api_version => l_api_version,
2822 p_init_msg_list => l_init_msg_list,
2823 x_return_status => l_return_status,
2824 x_msg_count => l_msg_count,
2825 x_msg_data => l_msg_data,
2826 -- changed for bug 5391874
2827 -- p_customer_num => l_customer_number,
2828 p_customer_num => null,
2829 p_contract_num => l_orig_rcpt_tbl (i).invoice_number,
2830 p_currency_code => l_currency_code,
2831 -- p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
2832 p_amount_app_to => l_orig_rcpt_tbl(i).amount_applied,
2833 p_amount_app_from => l_orig_rcpt_tbl(i).amount_applied_from,
2834 p_inv_to_rct_rate => l_orig_rcpt_tbl(i).trans_to_receipt_rate,
2835 p_receipt_date => l_receipt_date,
2836 p_org_id => l_org_id,
2837 x_appl_tbl => l_okl_rcpt_tbl,
2838 x_onacc_amount => l_onacc_amount,
2839 x_unapply_amount => l_unapply_amount
2840 );
2841 log_debug
2842 ( 'okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract return status = '
2843 || l_return_status
2844 );
2845 log_debug
2846 ( 'Number of invoices identified for application purpose = '
2847 || l_okl_rcpt_tbl.COUNT
2848 );
2849
2850 IF (l_onacc_amount > 0) OR (l_unapply_amount > 0) THEN
2851 l_onacc_unapp_exist := 'Y';
2852 ELSE
2853 l_onacc_unapp_exist := 'N';
2854 END IF;
2855
2856 log_debug
2857 ( ' On-Account / Unapplied amount exists : '
2858 || l_onacc_unapp_exist
2859 );
2860 END IF;
2861 END IF;
2862
2863 --asawanka modified for llca end
2864 IF l_okl_rcpt_tbl.COUNT = 0
2865 AND l_valid_ar_reference = 'N'
2866 AND l_onacc_unapp_exist = 'N' THEN
2867 -- lets use some combination rules.
2868 log_debug
2869 ('Invoice Reference did not match to any OKL Contract Number'
2870 );
2871 log_debug ('Trying to use Search Rules');
2872 log_debug
2873 ('Calling okl_combi_cash_app_rls_pvt.handle_combi_pay with parameters: '
2874 );
2875 log_debug ('p_customer_number = ' || l_customer_number);
2876 log_debug ( 'p_rcpt_amount = '
2877 || l_orig_rcpt_tbl (i).amount_applied
2878 );
2879 log_debug ('p_org_id = ' || l_org_id);
2880 IF l_tmp_currency_code IS NULL OR l_tmp_currency_code = l_currency_code
2881 THEN
2882 okl_combi_cash_app_rls_pvt.handle_combi_pay
2883 (p_api_version => l_api_version,
2884 p_init_msg_list => l_init_msg_list,
2885 x_return_status => l_return_status,
2886 x_msg_count => l_msg_count,
2887 x_msg_data => l_msg_data,
2888 p_customer_number => l_customer_number,
2889 p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
2890 -- p_receipt_date => l_receipt_date,
2891 p_org_id => l_org_id,
2892 p_currency_code => l_currency_code,
2893 x_appl_tbl => l_okl_rcpt_tbl
2894 );
2895 log_debug
2896 ( 'okl_auto_cash_appl_rules_pvt.handle_combi_pay return status = '
2897 || l_return_status
2898 );
2899 log_debug
2900 ( 'Number of invoices identified for application purpose = '
2901 || l_okl_rcpt_tbl.COUNT
2902 );
2903 END IF;
2904 END IF;
2905
2906 IF l_okl_rcpt_tbl.COUNT = 0
2907 --asawanka modified llca start
2908 --commenting out l_lockbox_run_already condition as it is no longer required
2909 -- AND l_lockbox_run_already = NULL
2910 --asawanka modified for llca end
2911 AND l_valid_ar_reference = 'N'
2912 AND l_onacc_unapp_exist = 'N' THEN
2913 -- still no match, lets apply to customers oldest/newest invoice(s)
2914 log_debug ('Search Rules failed.');
2915 log_debug ('Trying to use Receipt Mismatch Rules');
2916 log_debug
2917 ('Calling okl_auto_cash_appl_rules_pvt.receipt_mismatch with parameters: '
2918 );
2919 log_debug ('p_customer_num = ' || l_customer_number);
2920 log_debug ('p_currency_code = ' || l_currency_code);
2921 log_debug ( 'p_rcpt_amount = '
2922 || l_orig_rcpt_tbl (i).amount_applied
2923 );
2924 log_debug ('p_org_id = ' || l_org_id);
2925 --asawanka modified for llca start
2926 --changed api call
2927 IF l_tmp_currency_code IS NULL OR l_tmp_currency_code = l_currency_code
2928 THEN
2929 okl_auto_cash_appl_rules_pvt.receipt_mismatch
2930 (p_api_version => l_api_version,
2931 p_init_msg_list => l_init_msg_list,
2932 x_return_status => l_return_status,
2933 x_msg_count => l_msg_count,
2934 x_msg_data => l_msg_data,
2935 p_customer_num => l_customer_number,
2936 p_currency_code => l_currency_code,
2937 p_rcpt_amount => l_orig_rcpt_tbl (i).amount_applied,
2938 p_org_id => l_org_id,
2939 p_receipt_date => l_receipt_date,
2940 x_appl_tbl => l_okl_rcpt_tbl,
2941 x_onacc_amount => l_onacc_amount
2942 );
2943 --asawanka modified for llca end
2944 log_debug
2945 ( 'okl_auto_cash_appl_rules_pvt.receipt_mismatch return status = '
2946 || l_return_status
2947 );
2948 log_debug
2949 ( 'Number of invoices identified for application purpose = '
2950 || l_okl_rcpt_tbl.COUNT
2951 );
2952
2953 IF (l_onacc_amount > 0) THEN
2954 l_onacc_unapp_exist := 'Y';
2955 ELSE
2956 l_onacc_unapp_exist := 'N';
2957 END IF;
2958
2959 log_debug
2960 ( ' On-Account / Unapplied amount exists : '
2961 || l_onacc_unapp_exist
2962 );
2963 END IF;
2964 END IF;
2965
2966 IF l_okl_rcpt_tbl.COUNT = 0
2967 AND l_customer_number IS NOT NULL
2968 AND l_onacc_unapp_exist = 'N' THEN
2969 -- nothing found for receipt reference, retain invoice ref...
2970 log_debug
2971 ('Nothing found for Invoice Reference. Retaining Invoice Refence in interface table.'
2972 );
2973 l_no_mtch_rcpt_tbl (j).invoice_number :=
2974 l_orig_rcpt_tbl (i).invoice_number;
2975 l_no_mtch_rcpt_tbl (j).amount_applied :=
2976 l_orig_rcpt_tbl (i).amount_applied;
2977 j := j + 1;
2978
2979 -- ...and check for termination quotes
2980 IF l_valid_ar_reference = 'N' THEN
2981 log_debug
2982 ('Checking if invoice reference matches any Approved termination quote number'
2983 );
2984
2985 OPEN c_get_party_id (l_customer_number);
2986
2987 FETCH c_get_party_id
2988 INTO l_party_id;
2989
2990 CLOSE c_get_party_id;
2991
2992 IF l_party_id IS NOT NULL THEN
2993 OPEN c_check_termination
2994 (l_party_id,
2995 l_receipt_date,
2996 l_days_past_quote_valid,
2997 l_orig_rcpt_tbl (i).invoice_number
2998 );
2999
3000 LOOP
3001 FETCH c_check_termination
3002 INTO l_quote_id,
3003 l_quote_date_effective_to,
3004 l_quote_number,
3005 l_quote_amount;
3006
3007 EXIT WHEN c_check_termination%NOTFOUND;
3008 log_debug
3009 ('Invoice Reference matches Termination Quote Number. Checking if amount to appply matches Termination Quote Amount'
3010 );
3011
3012 IF l_quote_amount =
3013 l_orig_rcpt_tbl (i).amount_applied THEN
3014 l_qtev_rec.ID := l_quote_id;
3015 l_qtev_rec.payment_received_yn := 'Y';
3016 l_qtev_rec.date_payment_received :=
3017 l_receipt_date;
3018 l_qtev_rec.date_effective_to :=
3019 l_quote_date_effective_to;
3020 l_qtev_rec.accepted_yn := 'Y';
3021 l_qtev_rec.preproceeds_yn := 'N';
3022 log_debug
3023 ('Amount matches. Calling api okl_am_termnt_quote_pub.terminate_quote with parameters:'
3024 );
3025 log_debug ( 'p_term_rec.id = '
3026 || l_qtev_rec.ID
3027 );
3028 log_debug
3029 ( 'p_term_rec.payment_received_yn = '
3030 || l_qtev_rec.payment_received_yn
3031 );
3032 log_debug
3033 ( 'p_term_rec.date_payment_received = '
3034 || l_qtev_rec.date_payment_received
3035 );
3036 log_debug
3037 ( 'p_term_rec.date_effective_to = '
3038 || l_qtev_rec.accepted_yn
3039 );
3040 log_debug ( 'p_term_rec.accepted_yn = '
3041 || l_qtev_rec.accepted_yn
3042 );
3043 log_debug
3044 ( 'p_term_rec.preproceeds_yn = '
3045 || l_qtev_rec.preproceeds_yn
3046 );
3047 okl_am_termnt_quote_pub.terminate_quote
3048 (p_api_version => l_api_version,
3049 p_init_msg_list => l_init_msg_list,
3050 x_return_status => l_return_status,
3051 x_msg_count => l_msg_count,
3052 x_msg_data => l_msg_data,
3053 p_term_rec => l_qtev_rec,
3054 x_term_rec => x_qtev_rec,
3055 x_err_msg => l_err_msg
3056 );
3057 log_debug
3058 ( 'okl_am_termnt_quote_pub.terminate_quote return status = '
3059 || l_return_status
3060 );
3061 EXIT;
3062 END IF;
3063 END LOOP;
3064
3065 CLOSE c_check_termination;
3066 END IF;
3067 END IF;
3068 ELSIF l_okl_rcpt_tbl.COUNT > 0 THEN
3069 -- we found some invoices to apply
3070 log_debug
3071 ('Invoice Appliactions identified. Processing Further...'
3072 );
3073 k := l_okl_rcpt_tbl.FIRST;
3074
3075 LOOP
3076 -- (3) Loop through all new invoice(s)/invoice amount(s)
3077 l_hdr_idx := k;
3078 l_amt1_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 1');
3079 l_amt_from1_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 1');
3080 l_new_invoice1 :=
3081 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3082 l_new_amount_applied1 :=
3083 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3084 * POWER (10, l_amt1_prec);
3085 l_new_amount_applied_from1 :=
3086 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3087 * POWER (10, l_amt_from1_prec);
3088 l_new_currency_code1 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3089 l_new_trans_to_rct_rate1 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3090
3091 k := k + 1;
3092 l_amt2_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 2');
3093 l_amt_from2_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 2');
3094 l_new_invoice2 :=
3095 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3096 l_new_amount_applied2 :=
3097 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3098 * POWER (10, l_amt2_prec);
3099 l_new_amount_applied_from2 :=
3100 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3101 * POWER (10, l_amt_from2_prec);
3102 l_new_currency_code2 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3103 l_new_trans_to_rct_rate2 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3104
3105 k := k + 1;
3106 l_amt3_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 3');
3107 l_amt_from3_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 3');
3108 l_new_invoice3 :=
3109 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3110 l_new_amount_applied3 :=
3111 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3112 * POWER (10, l_amt3_prec);
3113 l_new_amount_applied_from3 :=
3114 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3115 * POWER (10, l_amt_from3_prec);
3116 l_new_currency_code3 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3117 l_new_trans_to_rct_rate3 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3118
3119 k := k + 1;
3120 l_amt4_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 4');
3121 l_amt_from4_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 4');
3122 l_new_invoice4 :=
3123 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3124 l_new_amount_applied4 :=
3125 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3126 * POWER (10, l_amt4_prec);
3127 l_new_amount_applied_from4 :=
3128 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3129 * POWER (10, l_amt_from4_prec);
3130 l_new_currency_code4 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3131 l_new_trans_to_rct_rate4 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3132
3133 k := k + 1;
3134 l_amt5_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 5');
3135 l_amt_from5_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 5');
3136 l_new_invoice5 :=
3137 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3138 l_new_amount_applied5 :=
3139 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3140 * POWER (10, l_amt5_prec);
3141 l_new_amount_applied_from5 :=
3142 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3143 * POWER (10, l_amt_from5_prec);
3144 l_new_currency_code5 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3145 l_new_trans_to_rct_rate5 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3146
3147 k := k + 1;
3148 l_amt6_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 6');
3149 l_amt_from6_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 6');
3150 l_new_invoice6 :=
3151 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3152 l_new_amount_applied6 :=
3153 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3154 * POWER (10, l_amt6_prec);
3155 l_new_amount_applied_from6 :=
3156 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3157 * POWER (10, l_amt_from6_prec);
3158 l_new_currency_code6 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3159 l_new_trans_to_rct_rate6 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3160
3161 k := k + 1;
3162 l_amt7_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 7');
3163 l_amt_from7_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 7');
3164 l_new_invoice7 :=
3165 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3166 l_new_amount_applied7 :=
3167 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3168 * POWER (10, l_amt7_prec);
3169 l_new_amount_applied_from7 :=
3170 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3171 * POWER (10, l_amt_from7_prec);
3172 l_new_currency_code7 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3173 l_new_trans_to_rct_rate7 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3174
3175 k := k + 1;
3176 l_amt8_prec := get_precision (l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP 8');
3177 l_amt_from8_prec := get_precision(l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code,p_trans_req_id,'OVRFLW PAYMENT','AMT APP FROM 8');
3178 l_new_invoice8 :=
3179 l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_number;
3180 l_new_amount_applied8 :=
3181 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied
3182 * POWER (10, l_amt8_prec);
3183 l_new_amount_applied_from8 :=
3184 l_okl_rcpt_tbl (k).inv_hdr_rec.amount_applied_from
3185 * POWER (10, l_amt_from8_prec);
3186 l_new_currency_code8 := l_okl_rcpt_tbl (k).inv_hdr_rec.invoice_currency_code;
3187 l_new_trans_to_rct_rate8 := l_okl_rcpt_tbl (k).inv_hdr_rec.trans_to_receipt_rate;
3188
3189 -- create new overflow records
3190 SELECT ar_payments_interface_s.NEXTVAL
3191 INTO l_transmission_rec_id_of
3192 FROM DUAL;
3193
3194 log_debug
3195 ('Inserting overflow record with application information for the current payment record'
3196 );
3197 log_debug
3198 ( 'Transmission Record Id of new overflow record = '
3199 || l_transmission_rec_id_of
3200 );
3201
3202 INSERT INTO ar_payments_interface_all
3203 (transmission_record_id,
3204 item_number,
3205 record_type,
3206 status,
3207 transmission_id,
3208 transmission_request_id,
3209 lockbox_number,
3210 batch_name,
3211 invoice1,
3212 amount_applied1,
3213 amount_applied_from1,
3214 invoice_currency_code1,
3215 trans_to_receipt_rate1,
3216 invoice2,
3217 amount_applied2,
3218 amount_applied_from2,
3219 invoice_currency_code2,
3220 trans_to_receipt_rate2,
3221 invoice3,
3222 amount_applied3,
3223 amount_applied_from3,
3224 invoice_currency_code3,
3225 trans_to_receipt_rate3,
3226 invoice4,
3227 amount_applied4,
3228 amount_applied_from4,
3229 invoice_currency_code4,
3230 trans_to_receipt_rate4,
3231 invoice5,
3232 amount_applied5,
3233 amount_applied_from5,
3234 invoice_currency_code5,
3235 trans_to_receipt_rate5,
3236 invoice6,
3237 amount_applied6,
3238 amount_applied_from6,
3239 invoice_currency_code6,
3240 trans_to_receipt_rate6,
3241 invoice7,
3242 amount_applied7,
3243 amount_applied_from7,
3244 invoice_currency_code7,
3245 trans_to_receipt_rate7,
3246 invoice8,
3247 amount_applied8,
3248 amount_applied_from8,
3249 invoice_currency_code8,
3250 trans_to_receipt_rate8,
3251 org_id,
3252 creation_date,
3253 last_update_date
3254 )
3255 VALUES (l_transmission_rec_id_of,
3256 l_item_number,
3257 l_overflow,
3258 l_status,
3259 l_transmission_id,
3260 l_trans_req_id,
3261 l_lockbox_number,
3262 l_batch_name,
3263 l_new_invoice1,
3264 l_new_amount_applied1,
3265 l_new_amount_applied_from1,
3266 l_new_currency_code1,
3267 l_new_trans_to_rct_rate1,
3268 l_new_invoice2,
3269 l_new_amount_applied2,
3270 l_new_amount_applied_from2,
3271 l_new_currency_code2,
3272 l_new_trans_to_rct_rate2,
3273 l_new_invoice3,
3274 l_new_amount_applied3,
3275 l_new_amount_applied_from3,
3276 l_new_currency_code3,
3277 l_new_trans_to_rct_rate3,
3278 l_new_invoice4,
3279 l_new_amount_applied4,
3280 l_new_amount_applied_from4,
3281 l_new_currency_code4,
3282 l_new_trans_to_rct_rate4,
3283 l_new_invoice5,
3284 l_new_amount_applied5,
3285 l_new_amount_applied_from5,
3286 l_new_currency_code5,
3287 l_new_trans_to_rct_rate5,
3288 l_new_invoice6,
3289 l_new_amount_applied6,
3290 l_new_amount_applied_from6,
3291 l_new_currency_code6,
3292 l_new_trans_to_rct_rate6,
3293 l_new_invoice7,
3294 l_new_amount_applied7,
3295 l_new_amount_applied_from7,
3296 l_new_currency_code7,
3297 l_new_trans_to_rct_rate7,
3298 l_new_invoice8,
3299 l_new_amount_applied8,
3300 l_new_amount_applied_from8,
3301 l_new_currency_code8,
3302 l_new_trans_to_rct_rate8,
3303 l_org_id,
3304 SYSDATE,
3305 TRUNC (SYSDATE)
3306 );
3307
3308 /*************************************************************************************
3309 insert line level details in AR table here
3310 *************************************************************************************/
3311 log_debug
3312 ('Processing line level application data for currently inserted overflow record'
3313 );
3314 FOR l_inv_indx IN l_hdr_idx .. k
3315 LOOP
3316 IF l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number IS NOT NULL THEN
3317 l_linelevel_app :=
3318 get_line_level_app
3319 (l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_id,
3320 l_org_id
3321 );
3322
3323 IF l_linelevel_app = 'Y' THEN
3324 /* -- first, delete any existing line level application data
3325 DELETE AR_PMTS_INTERFACE_LINE_DETAILS
3326 WHERE transmission_req_id = l_trans_req_id
3327 AND transmission_record_id = l_transmission_record_id
3328 AND invoice_number = l_okl_rcpt_tbl(l_inv_indx).inv_hdr_rec.invoice_number;*/-- now create line level application data
3329 IF l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl.COUNT >
3330 0 THEN
3331 log_debug
3332 ( 'Inserting line level application data for Invoice Number = '
3333 || l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number
3334 );
3335
3336 FOR l_line_indx IN
3337 l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl.FIRST .. l_okl_rcpt_tbl
3338 (l_inv_indx
3339 ).inv_lines_tbl.LAST
3340 LOOP
3341 --START: Fixed as part of bug 6780241 by nikshah
3342 if (l_currency_code = l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_currency_code) then
3343 l_amount_in_inv_curr :=l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl
3344 (l_line_indx).amount_applied;
3345 l_amount_in_rct_curr := null;
3346 else
3347 l_amount_in_inv_curr := null;
3348 l_amount_in_rct_curr := l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl
3349 (l_line_indx).amount_applied;
3350 end if;
3351 INSERT INTO ar_pmts_interface_line_details
3352 (status,
3353 transmission_request_id,
3354 transmission_record_id,
3355 invoice_number,
3356 apply_to,
3357 allocated_receipt_amount,
3358 amount_applied
3359 )
3360 VALUES ('AR_PLB_NEW_RECORD',
3361 l_trans_req_id,
3362 l_transmission_rec_id_of,
3363 l_okl_rcpt_tbl
3364 (l_inv_indx).inv_hdr_rec.invoice_number,
3365 l_okl_rcpt_tbl
3366 (l_inv_indx).inv_lines_tbl
3367 (l_line_indx).invoice_line_number,
3368 l_amount_in_inv_curr,
3369 l_amount_in_rct_curr
3370 );
3371 --END: Fixed as part of bug 6780241 by nikshah
3372 END LOOP;
3373 END IF;
3374 END IF;
3375 END IF;
3376 END LOOP;
3377
3378 EXIT WHEN k = (l_okl_rcpt_tbl.LAST);
3379 -- records should always be in mutiples of 8.
3380 k := k + 1;
3381 -- k := k + 1;
3382 -- EXIT WHEN k = (l_okl_rcpt_tbl.LAST + 1); -- records should always be in mutiples of 8.
3383 END LOOP;
3384 -- (4) end looping through all new invoice(s)/invoice amount(s)
3385 ELSE
3386 log_debug
3387 ('Invoice Reference matches valid non okl AR Invoice Number. Skipping the invoice reference'
3388 );
3389 END IF;
3390 ELSE
3391 -- lockbox already null. so keep invoice ref.
3392 log_debug
3393 ('Line Level data already exists for this invoice reference. Skipping the invoice reference'
3394 );
3395 l_prec_to_use := 0;
3396 l_prec_to_use_from := 0;
3397
3398 IF i = 1 THEN
3399 l_prec_to_use := l_amt1_prec;
3400 l_prec_to_use_from := l_amt_from1_prec;
3401 ELSIF i = 2 THEN
3402 l_prec_to_use := l_amt2_prec;
3403 l_prec_to_use_from := l_amt_from2_prec;
3404 ELSIF i = 3 THEN
3405 l_prec_to_use := l_amt3_prec;
3406 l_prec_to_use_from := l_amt_from3_prec;
3407 ELSIF i = 4 THEN
3408 l_prec_to_use := l_amt4_prec;
3409 l_prec_to_use_from := l_amt_from4_prec;
3410 ELSIF i = 5 THEN
3411 l_prec_to_use := l_amt5_prec;
3412 l_prec_to_use_from := l_amt_from5_prec;
3413 ELSIF i = 6 THEN
3414 l_prec_to_use := l_amt6_prec;
3415 l_prec_to_use_from := l_amt_from6_prec;
3416 ELSIF i = 7 THEN
3417 l_prec_to_use := l_amt7_prec;
3418 l_prec_to_use_from := l_amt_from7_prec;
3419 ELSIF i = 8 THEN
3420 l_prec_to_use := l_amt8_prec;
3421 l_prec_to_use_from := l_amt_from8_prec;
3422 END IF;
3423
3424 l_no_mtch_rcpt_tbl (j).invoice_number :=
3425 l_orig_rcpt_tbl (i).invoice_number;
3426 l_no_mtch_rcpt_tbl (j).amount_applied :=
3427 l_orig_rcpt_tbl (i).amount_applied
3428 * POWER (10, l_prec_to_use);
3429 l_no_mtch_rcpt_tbl(j).amount_applied_from := l_orig_rcpt_tbl(i).amount_applied_from * power(10,l_prec_to_use_from);
3430 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := l_orig_rcpt_tbl(i).trans_to_receipt_rate;
3431 j := j + 1;
3432 END IF;
3433
3434 EXIT WHEN i = 8;
3435 -- all 8 invoice references were processed in this overflow record, so move to nxt overflow rec
3436 i := i + 1;
3437 -- invoice ref processed. move to nxt inv ref in this overflow rec
3438 ELSE -- no invoice ref
3439 IF l_orig_rcpt_tbl (i).invoice_number IS NOT NULL
3440 AND l_orig_rcpt_tbl (i).amount_applied IS NOT NULL THEN
3441 log_debug
3442 ('Invoice Reference exists but amount to apply is zero after considering precision. Keeping Invoice reference.'
3443 );
3444 l_prec_to_use := 0;
3445 l_prec_to_use_from := 0;
3446
3447 IF i = 1 THEN
3448 l_prec_to_use := l_amt1_prec;
3449 l_prec_to_use_from := l_amt_from1_prec;
3450 ELSIF i = 2 THEN
3451 l_prec_to_use := l_amt2_prec;
3452 l_prec_to_use_from := l_amt_from2_prec;
3453 ELSIF i = 3 THEN
3454 l_prec_to_use := l_amt3_prec;
3455 l_prec_to_use_from := l_amt_from3_prec;
3456 ELSIF i = 4 THEN
3457 l_prec_to_use := l_amt4_prec;
3458 l_prec_to_use_from := l_amt_from4_prec;
3459 ELSIF i = 5 THEN
3460 l_prec_to_use := l_amt5_prec;
3461 l_prec_to_use_from := l_amt_from5_prec;
3462 ELSIF i = 6 THEN
3463 l_prec_to_use := l_amt6_prec;
3464 l_prec_to_use_from := l_amt_from6_prec;
3465 ELSIF i = 7 THEN
3466 l_prec_to_use := l_amt7_prec;
3467 l_prec_to_use_from := l_amt_from7_prec;
3468 ELSIF i = 8 THEN
3469 l_prec_to_use := l_amt8_prec;
3470 l_prec_to_use_from := l_amt_from8_prec;
3471 END IF;
3472
3473 l_no_mtch_rcpt_tbl (j).invoice_number :=
3474 l_orig_rcpt_tbl (i).invoice_number;
3475 l_no_mtch_rcpt_tbl (j).amount_applied :=
3476 l_orig_rcpt_tbl (i).amount_applied
3477 * POWER (10, l_prec_to_use);
3478 l_no_mtch_rcpt_tbl(j).amount_applied_from := l_orig_rcpt_tbl(i).amount_applied_from * power(10,l_prec_to_use_from);
3479 l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate := l_orig_rcpt_tbl(i).trans_to_receipt_rate;
3480 j := j + 1;
3481 END IF;
3482
3483 EXIT WHEN i = 8;
3484 -- all 8 invoice references were processed in this overflow record, so move to nxt overflow rec
3485 i := i + 1;
3486 -- invoice ref processed. move to nxt inv ref in this overflow rec
3487 END IF;
3488 END LOOP; -- (3)
3489
3490 -- delete/modify original overflow recs using l_transmission_record_id
3491 log_debug ('All invoice references in overflow record processed. ');
3492 j := 1;
3493
3494 -- if all 8 inv refs in this overflow rec matched then delete this over flow rec
3495 IF l_no_mtch_rcpt_tbl (j).invoice_number IS NULL
3496 AND (l_no_mtch_rcpt_tbl (j).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j).amount_applied_from IS NULL)
3497 AND l_no_mtch_rcpt_tbl (j + 1).invoice_number IS NULL
3498 AND (l_no_mtch_rcpt_tbl (j + 1).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+1).amount_applied_from IS NULL)
3499 AND l_no_mtch_rcpt_tbl (j + 2).invoice_number IS NULL
3500 AND (l_no_mtch_rcpt_tbl (j + 2).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+2).amount_applied_from IS NULL)
3501 AND l_no_mtch_rcpt_tbl (j + 3).invoice_number IS NULL
3502 AND (l_no_mtch_rcpt_tbl (j + 3).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+3).amount_applied_from IS NULL)
3503 AND l_no_mtch_rcpt_tbl (j + 4).invoice_number IS NULL
3504 AND (l_no_mtch_rcpt_tbl (j + 4).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+4).amount_applied_from IS NULL)
3505 AND l_no_mtch_rcpt_tbl (j + 5).invoice_number IS NULL
3506 AND (l_no_mtch_rcpt_tbl (j + 5).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+5).amount_applied_from IS NULL)
3507 AND l_no_mtch_rcpt_tbl (j + 6).invoice_number IS NULL
3508 AND (l_no_mtch_rcpt_tbl (j + 6).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+6).amount_applied_from IS NULL)
3509 AND l_no_mtch_rcpt_tbl (j + 7).invoice_number IS NULL
3510 AND (l_no_mtch_rcpt_tbl (j + 7).amount_applied IS NULL OR l_no_mtch_rcpt_tbl (j+7).amount_applied_from IS NULL)
3511 THEN
3512 log_debug
3513 ('All invoice references in this overflow record matched.Deleting this over flow record.'
3514 );
3515
3516 DELETE FROM ar_payments_interface_all
3517 -- no need to keep old overflow rec
3518 WHERE transmission_record_id = l_transmission_record_id;
3519 END IF;
3520
3521 -- if some of the 8 inv refs matched then
3522 -- keep the non matched inv refs and update resolved_match_numbers for them
3523 -- null out the matched inv refs
3524 IF l_no_mtch_rcpt_tbl (j).invoice_number IS NOT NULL
3525 OR l_no_mtch_rcpt_tbl (j).amount_applied IS NOT NULL
3526 OR l_no_mtch_rcpt_tbl (j).amount_applied_from IS NOT NULL
3527 OR l_no_mtch_rcpt_tbl (j + 1).invoice_number IS NOT NULL
3528 OR l_no_mtch_rcpt_tbl (j + 1).amount_applied IS NOT NULL
3529 OR l_no_mtch_rcpt_tbl (j + 1).amount_applied_from IS NOT NULL
3530 OR l_no_mtch_rcpt_tbl (j + 2).invoice_number IS NOT NULL
3531 OR l_no_mtch_rcpt_tbl (j + 2).amount_applied IS NOT NULL
3532 OR l_no_mtch_rcpt_tbl (j + 2).amount_applied_from IS NOT NULL
3533 OR l_no_mtch_rcpt_tbl (j + 3).invoice_number IS NOT NULL
3534 OR l_no_mtch_rcpt_tbl (j + 3).amount_applied IS NOT NULL
3535 OR l_no_mtch_rcpt_tbl (j + 3).amount_applied_from IS NOT NULL
3536 OR l_no_mtch_rcpt_tbl (j + 4).invoice_number IS NOT NULL
3537 OR l_no_mtch_rcpt_tbl (j + 4).amount_applied IS NOT NULL
3538 OR l_no_mtch_rcpt_tbl (j + 4).amount_applied_from IS NOT NULL
3539 OR l_no_mtch_rcpt_tbl (j + 5).invoice_number IS NOT NULL
3540 OR l_no_mtch_rcpt_tbl (j + 5).amount_applied IS NOT NULL
3541 OR l_no_mtch_rcpt_tbl (j + 5).amount_applied_from IS NOT NULL
3542 OR l_no_mtch_rcpt_tbl (j + 6).invoice_number IS NOT NULL
3543 OR l_no_mtch_rcpt_tbl (j + 6).amount_applied IS NOT NULL
3544 OR l_no_mtch_rcpt_tbl (j + 6).amount_applied_from IS NOT NULL
3545 OR l_no_mtch_rcpt_tbl (j + 7).invoice_number IS NOT NULL
3546 OR l_no_mtch_rcpt_tbl (j + 7).amount_applied IS NOT NULL
3547 OR l_no_mtch_rcpt_tbl (j + 7).amount_applied_from IS NOT NULL THEN
3548 -- l_no_match_indicator = 1 THEN
3549 -- some reference did not evaluate to anything,
3550 -- but we need to keep them nonetheless ...
3551 log_debug
3552 ('Nulling out invoice references which were matched successfully and retaining those which did not match at all'
3553 );
3554
3555 UPDATE ar_payments_interface_all
3556 SET overflow_sequence =
3557 NULL
3558 -- we'll take care of this later.see below
3559 ,
3560 overflow_indicator =
3561 NULL
3562 -- we'll take care of this later.see below
3563 ,
3564 invoice1 = l_no_mtch_rcpt_tbl (j).invoice_number,
3565 amount_applied1 = l_no_mtch_rcpt_tbl (j).amount_applied,
3566 resolved_matching_number1 =
3567 l_no_mtch_rcpt_tbl (j).invoice_number,
3568 amount_applied_from1 = l_no_mtch_rcpt_tbl(j).amount_applied_from,
3569 trans_to_receipt_rate1 = l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate,
3570 invoice2 = l_no_mtch_rcpt_tbl (j + 1).invoice_number,
3571 amount_applied2 =
3572 l_no_mtch_rcpt_tbl (j + 1).amount_applied,
3573 resolved_matching_number2 =
3574 l_no_mtch_rcpt_tbl (j + 1).invoice_number,
3575 amount_applied_from2 = l_no_mtch_rcpt_tbl(j+1).amount_applied_from,
3576 trans_to_receipt_rate2 = l_no_mtch_rcpt_tbl(j+1).trans_to_receipt_rate,
3577 invoice3 = l_no_mtch_rcpt_tbl (j + 2).invoice_number,
3578 amount_applied3 =
3579 l_no_mtch_rcpt_tbl (j + 2).amount_applied,
3580 resolved_matching_number3 =
3581 l_no_mtch_rcpt_tbl (j + 2).invoice_number,
3582 amount_applied_from3 = l_no_mtch_rcpt_tbl(j + 2).amount_applied_from,
3583 trans_to_receipt_rate3 = l_no_mtch_rcpt_tbl(j + 2).trans_to_receipt_rate,
3584 invoice4 = l_no_mtch_rcpt_tbl (j + 3).invoice_number,
3585 amount_applied4 =
3586 l_no_mtch_rcpt_tbl (j + 3).amount_applied,
3587 resolved_matching_number4 =
3588 l_no_mtch_rcpt_tbl (j + 3).invoice_number,
3589 amount_applied_from4 = l_no_mtch_rcpt_tbl(j + 3).amount_applied_from,
3590 trans_to_receipt_rate4 = l_no_mtch_rcpt_tbl(j + 3).trans_to_receipt_rate,
3591 invoice5 = l_no_mtch_rcpt_tbl (j + 4).invoice_number,
3592 amount_applied5 =
3593 l_no_mtch_rcpt_tbl (j + 4).amount_applied,
3594 resolved_matching_number5 =
3595 l_no_mtch_rcpt_tbl (j + 4).invoice_number,
3596 amount_applied_from5 = l_no_mtch_rcpt_tbl(j + 4).amount_applied_from,
3597 trans_to_receipt_rate5 = l_no_mtch_rcpt_tbl(j + 4).trans_to_receipt_rate,
3598 invoice6 = l_no_mtch_rcpt_tbl (j + 5).invoice_number,
3599 amount_applied6 =
3600 l_no_mtch_rcpt_tbl (j + 5).amount_applied,
3601 resolved_matching_number6 =
3602 l_no_mtch_rcpt_tbl (j + 5).invoice_number,
3603 amount_applied_from6 = l_no_mtch_rcpt_tbl(j + 5).amount_applied_from,
3604 trans_to_receipt_rate6 = l_no_mtch_rcpt_tbl(j + 5).trans_to_receipt_rate,
3605 invoice7 = l_no_mtch_rcpt_tbl (j + 6).invoice_number,
3606 amount_applied7 =
3607 l_no_mtch_rcpt_tbl (j + 6).amount_applied,
3608 resolved_matching_number7 =
3609 l_no_mtch_rcpt_tbl (j + 6).invoice_number,
3610 amount_applied_from7 = l_no_mtch_rcpt_tbl(j + 6).amount_applied_from,
3611 trans_to_receipt_rate7 = l_no_mtch_rcpt_tbl(j + 6).trans_to_receipt_rate,
3612 invoice8 = l_no_mtch_rcpt_tbl (j + 7).invoice_number,
3613 amount_applied8 =
3614 l_no_mtch_rcpt_tbl (j + 7).amount_applied,
3615 resolved_matching_number8 =
3616 l_no_mtch_rcpt_tbl (j + 7).invoice_number,
3617 amount_applied_from8 = l_no_mtch_rcpt_tbl(j + 7).amount_applied_from,
3618 trans_to_receipt_rate8 = l_no_mtch_rcpt_tbl(j + 7).trans_to_receipt_rate
3619 WHERE transmission_record_id = l_transmission_record_id;
3620 END IF;
3621 END LOOP; --(2) end loop through overflow recs
3622
3623 CLOSE c_get_ovrflw_recs;
3624
3625 -- still in context of original payment ...
3626 -- update sequence numbers and overflow indicators here.
3627 -- at this stage all the oveflow recs for this payment will have overfow_seq aand overflow indicator as null
3628 OPEN c_get_ovrflw_recs_new (l_trans_req_id,
3629 l_overflow,
3630 l_item_number,
3631 l_batch_name
3632 );
3633
3634 seq_num := 1;
3635
3636 LOOP -- (2) loop through overflow recs
3637 FETCH c_get_ovrflw_recs_new
3638 INTO l_transmission_record_id;
3639
3640 EXIT WHEN c_get_ovrflw_recs_new%NOTFOUND;
3641
3642 UPDATE ar_payments_interface_all
3643 SET overflow_sequence = seq_num,
3644 overflow_indicator = l_overflow_rec_indicator
3645 WHERE transmission_record_id = l_transmission_record_id;
3646
3647 seq_num := seq_num + 1;
3648 l_last_transmission_record_id := l_transmission_record_id;
3649 END LOOP;
3650
3651 -- set overflow indicator on last overflow record
3652 IF l_overflow_rec_indicator = '0' THEN
3653 l_ovf_ind := '1';
3654 ELSE
3655 l_ovf_ind := '0';
3656 END IF;
3657
3658 UPDATE ar_payments_interface_all
3659 SET overflow_indicator = l_ovf_ind
3660 WHERE transmission_record_id = l_last_transmission_record_id;
3661
3662 CLOSE c_get_ovrflw_recs_new;
3663
3664 log_debug ('All overflow records processed.');
3665 END LOOP; -- (1)
3666
3667 CLOSE c_get_pmt_recs;
3668
3669 -- tidy up
3670 -- UPDATE transmission total record count, both header and trailer.
3671 -- update transmission records in lock box.
3672 log_debug ('All payment records processed.');
3673 l_trans_rec_count := get_rec_count (l_trans_req_id);
3674
3675 UPDATE ar_payments_interface_all
3676 SET transmission_record_count = l_trans_rec_count
3677 WHERE transmission_request_id = l_trans_req_id
3678 AND record_type IN (l_transmission_hdr, l_transmission_trl);
3679
3680 x_return_status := okl_api.g_ret_sts_success;
3681 log_debug (' okl_lckbx_csh_app_pvt.handle_auto_pay end.');
3682 log_debug
3683 ('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
3684 );
3685 EXCEPTION
3686 /*
3687 WHEN Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3688 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3689 x_msg_count := l_msg_count ;
3690 l_msg_data := 'something wrong';
3691 x_msg_data := l_msg_data ;
3692 */
3693 WHEN OTHERS THEN
3694 x_return_status := okl_api.g_ret_sts_unexp_error;
3695 x_msg_count := l_msg_count;
3696 x_msg_data := l_msg_data;
3697 END handle_auto_pay;
3698 END okl_lckbx_csh_app_pvt;