1 PACKAGE BODY ARP_LOCKBOX_HOOK_PVT AS
2 /*$Header: ARRLBHPB.pls 120.10.12010000.5 2008/10/24 16:51:47 aghoraka ship $*/
3 --
4 /* Private variables */
5 g_okl_installed boolean := FALSE;
6 g_custom_llca_installed boolean := FALSE;
7 g_second_validation_pvt boolean := FALSE;
8 g_second_validation_pub boolean := FALSE;
9 --
10 /*----------------------------------------------------------------------------
11 proc_before_validation
12
13 This procedure will be called before the validation is called from arlplb().
14 If this procedure returns 0,
15 arlplb.opc will understand that some processing had taken place in this
16 procedure and it returned success. It will proceed with validation then.
17 If this procedure returns 2,
18 arlplb.opc will understand that some error had occured during the
19 processing in this procedure and will exit rolling back the information.
20 If out_insert_records is returned as 'Y', the first validation will
21 insert the records into ar_interim_cash_receipt and receipt_line.
22 In non-custom mode, this parameter returns 'Y', because we do not call
23 validation second time. However, if you are planning to call the second
24 validation, for customising lockbox, assign this variable as 'N'.
25
26 ----------------------------------------------------------------------------*/
27 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 PROCEDURE proc_before_validation(out_errorbuf OUT NOCOPY VARCHAR2,
30 out_errorcode OUT NOCOPY VARCHAR2,
31 in_trans_req_id IN VARCHAR2,
32 out_insert_records OUT NOCOPY VARCHAR2) IS
33 --
34 l_okl_flag varchar2(1) := 'N';
35 pvt_errorbuf varchar2(255);
36 pvt_errorcode varchar2(255);
37 pvt_insert_records varchar2(1);
38 pub_errorbuf varchar2(255);
39 pub_errorcode varchar2(255);
40 pub_insert_records varchar2(1);
41 l_org_id number;
42 l_line_level_cash_app_rule varchar2(2);
43 --
44 BEGIN
45 IF PG_DEBUG in ('Y', 'C') THEN
46 arp_util.debug('arp_lockbox_hook_pvt.proc_before_validation()+');
47 END IF;
48
49 BEGIN
50 select nvl(a.LINE_LEVEL_CASH_APP_RULE,'N') into l_line_level_cash_app_rule
51 from ar_lockboxes_all a, ar_transmissions_all b
52 where b.transmission_request_id = in_trans_req_id
53 and a.lockbox_id = b.requested_lockbox_id;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 arp_util.debug('Lockbox Number is given through the data file');
57 /* * Bug 7504497 Line Level Cash Application Rule is fetched from ar_lockboxes *
58 * using Lockbox_number provided in the data file. However the data in the *
59 * interface table will not be trimmed at this point which might fail in *
60 * fetching data from ar_lockboxes. Hence trimmed Lockbox number here. The *
61 * data in interface table will be trimmed later in arlvtr.lpc. * */
62 BEGIN
63 UPDATE ar_payments_interface pi
64 SET pi.lockbox_number =
65 (SELECT decode(ff.justification_lookup_code,
66 'LEFT', RTRIM(pi.lockbox_number, decode(ff.fill_character_lookup_code, 'ZERO', '0', 'BLANK', ' ')),
67 'RIGHT', LTRIM(pi.lockbox_number,decode(ff.fill_character_lookup_code, 'ZERO', '0', 'BLANK', ' ')))
68 FROM ar_transmissions tr,
69 ar_trans_field_formats ff,
70 ar_trans_record_formats rf
71 WHERE tr.transmission_id = pi.transmission_id
72 AND ff.transmission_format_id = tr.requested_trans_format_id
73 AND rf.record_format_id = ff.record_format_id
74 AND rf.record_identifier = pi.record_type
75 AND ff.field_type_lookup_code IN('LB NUM'))
76 WHERE pi.transmission_request_id = in_trans_req_id
77 AND pi.lockbox_number IS NOT NULL;
78
79 /* Lockbox Number can be present on any record. Hence changed the logic
80 to fetch Lockbox Number from interface wherever it is available. Earlier
81 Lockbox Number is fetched only from LB Hdr/LB Trl records. */
82
83 select distinct( nvl(a.line_level_cash_app_rule, 'N'))
84 into l_line_level_cash_app_rule
85 from ar_lockboxes a
86 where a.lockbox_number in ( select distinct(lockbox_number)
87 from ar_payments_interface
88 where transmission_request_id = in_trans_req_id
89 and lockbox_number is not null );
90 EXCEPTION
91 WHEN TOO_MANY_ROWS THEN
92 arp_util.debug('Multiple Lockboxes are present with different LLCA Rules');
93 fnd_file.put_line(FND_FILE.LOG, 'Multiple Lockboxes are present with different LLCA Rules');
94 RAISE;
95 WHEN NO_DATA_FOUND THEN
96 arp_util.debug('No matching Lockbox found.');
97 RAISE;
98 WHEN OTHERS THEN
99 arp_util.debug('Error in fetching Line Level Cash Application Rule.');
100 arp_util.debug('Error Message '||SQLERRM);
101 RAISE;
102 END;
103 END;
104
105 /* Check if custom code for LLCA is installed. */
106 if l_line_level_cash_app_rule = 'C' then
107 g_custom_llca_installed := TRUE;
108 end if;
109
110 -- Check if OKL is installed
111 BEGIN
112 l_org_id := to_number(arp_standard.sysparm.org_id);
113
114 if okl_cash_appl_rules.okl_installed(l_org_id) and l_line_level_cash_app_rule = 'L' THEN
115 l_okl_flag := 'Y';
116 else
117 l_okl_flag := 'N';
118 end if;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF PG_DEBUG in ('Y', 'C') THEN
123 arp_util.debug('proc_before_validation: ' || 'Exception in checking if OKL is installed');
124 END IF;
125 l_okl_flag := 'N';
126 END;
127
128 IF l_okl_flag = 'Y' THEN
129 IF PG_DEBUG in ('Y', 'C') THEN
130 arp_util.debug('proc_before_validation: ' || 'OKL is installed');
131 END IF;
132 g_okl_installed := TRUE;
133 END IF;
134
135 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
136 -- Removed ARTA logic as functionality is obsolete in R12
137 -- See Bug 4936298
138 NULL; -- Do Nothing
139 ELSIF g_okl_installed THEN
140 pvt_errorcode := 0;
141 pvt_errorbuf := NULL;
142 pvt_insert_records := 'N';
143 ELSIF g_custom_llca_installed THEN
144 pvt_errorcode := 0;
145 pvt_errorbuf := NULL;
146 pvt_insert_records := 'N';
147 ELSE
148 pvt_errorcode := 0;
149 pvt_errorbuf := NULL;
150 pvt_insert_records := 'Y';
151 END IF;
152
153 IF pvt_insert_records = 'N' THEN
154 g_second_validation_pvt := TRUE;
155 END IF;
156
157 -- Now call public hook
158 arp_lockbox_hook.proc_before_validation(pub_errorbuf,pub_errorcode,in_trans_req_id,pub_insert_records);
159
160 IF pub_insert_records = 'N' THEN
161 g_second_validation_pub := TRUE;
162 END IF;
163
164 IF pvt_errorcode = 0 THEN
165 out_errorcode := pub_errorcode;
166 out_errorbuf := pub_errorbuf;
167 ELSE
168 out_errorcode := pvt_errorcode;
169 out_errorbuf := pvt_errorbuf;
170 END IF;
171
172 IF pvt_insert_records = 'N' THEN
173 out_insert_records := 'N';
174 ELSE
175 out_insert_records := pub_insert_records;
176 END IF;
177
178 IF PG_DEBUG in ('Y', 'C') THEN
179 arp_util.debug('arp_lockbox_hook_pvt.proc_before_validation()-');
180 END IF;
181 END proc_before_validation;
182 --
183 /*----------------------------------------------------------------------------
184 proc_after_validation
185
186 This procedure will be called after the validation is over from arlplb().
187 If this procedure returns 0,
188 arlplb.opc will understand that some processing had taken place in this
189 procedure and arlplb.opc will fire the validation (arlval) again.
190 If this procedure returns 2,
191 arlplb.opc will understand that some error had occured during the
192 processing in this procedure and will exit rolling back the information.
193 If this procedure returns 9,
194 arlplb.opc will not fire the validation second time and will go ahead
195 with arlprt(). This is the same path as it was taking in base Rel 10.7
196 If out_insert_records is returned as 'Y', the second validation will
197 insert the records into ar_interim_cash_receipt and receipt_line.
198 In non-custom mode, this parameter returns 'N', because we do not call
199 validation second time. However, if you are planning to call the second
200 validation and you have returned out_insert_records as 'N' in the
201 proc_before_validation, you should return 'Y' here. This parameter is
202 considered only if the out_errorcode was returned as 0.
203
204 ----------------------------------------------------------------------------*/
205 PROCEDURE proc_after_validation(out_errorbuf OUT NOCOPY VARCHAR2,
206 out_errorcode OUT NOCOPY VARCHAR2,
207 in_trans_req_id IN VARCHAR2,
208 out_insert_records OUT NOCOPY VARCHAR2) IS
209 --
210 l_okl_block varchar2(1000);
211 pvt_errorbuf varchar2(255);
212 pvt_errorcode varchar2(255);
213 pvt_insert_records varchar2(1);
214 pub_errorbuf varchar2(255);
215 pub_errorcode varchar2(255);
216 pub_insert_records varchar2(1);
217 p_api_version NUMBER := 1;
218 p_init_msg_list VARCHAR2(1) := 'F';
219 x_return_status VARCHAR2(1);
220 x_msg_count NUMBER;
221 x_msg_data VARCHAR(2000);
222 --
223 BEGIN
224 IF PG_DEBUG in ('Y', 'C') THEN
225 arp_util.debug('proc_after_validation: ' || 'arp_lockbox_hook_pvt.proc_after_validation()+');
226 END IF;
227 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
228 -- Removed ARTA logic as functionality is obsolete in R12
229 -- See Bug 4936298
230 NULL; -- Do Nothing
231 ELSIF g_okl_installed THEN
232 BEGIN
233 l_okl_block :=
234 'BEGIN ' ||
235 'OKL_LCKBX_CSH_APP_PUB.handle_auto_pay ( :1 ' ||
236 ',:2 ' ||
237 ',:3 ' ||
238 ',:4 ' ||
239 ',:5 ' ||
240 ',:6 ' ||
241 '); ' ||
242 'END;';
243 IF PG_DEBUG in ('Y', 'C') THEN
244 arp_util.debug('Calling OKL proc_after_validation');
245 END IF;
246 EXECUTE IMMEDIATE l_okl_block USING p_api_version, p_init_msg_list, OUT x_return_status, OUT x_msg_count, OUT x_msg_data, in_trans_req_id;
247 IF PG_DEBUG in ('Y', 'C') THEN
248 arp_util.debug('Returned from OKL proc_after_validation');
249 END IF;
250 EXCEPTION
251 -- We ignore any error in OKL and
252 -- continue the process as if OKL is not installed
253 WHEN OTHERS THEN
254 IF PG_DEBUG in ('Y', 'C') THEN
255 arp_util.debug('Exception in OKL proc_after_validation');
256 END IF;
257 null;
258 END;
259 pvt_errorcode := 0;
260 pvt_errorbuf := NULL;
261 pvt_insert_records := 'Y';
262 ELSIF g_custom_llca_installed THEN
263 /* Call the procedure for custom LLCA. */
264 proc_for_custom_llca(in_trans_req_id);
265
266 /* Ignore any error in Custom Code and proceed as if no custom code installed. */
267 pvt_errorcode := 0;
268 pvt_errorbuf := NULL;
269 pvt_insert_records := 'Y';
270 ELSE
271 pvt_errorcode := 9;
272 pvt_errorbuf := NULL;
273 pvt_insert_records := 'N';
274 END IF;
275
276 -- Now call the public hook
277 arp_lockbox_hook.proc_after_validation(pub_errorbuf,pub_errorcode,in_trans_req_id,pub_insert_records);
278
279 IF g_second_validation_pvt AND g_second_validation_pub THEN
280 IF pvt_errorcode = 0 THEN
281 out_errorcode := pub_errorcode;
282 out_errorbuf := pub_errorbuf;
283 out_insert_records := pub_insert_records;
284 ELSE
285 out_errorcode := pvt_errorcode;
286 out_errorbuf := pvt_errorbuf;
287 out_insert_records := pvt_insert_records;
288 END IF;
289 ELSIF g_second_validation_pvt THEN
290 out_errorcode := pvt_errorcode;
291 out_errorbuf := pvt_errorbuf;
292 out_insert_records := pvt_insert_records;
293 ELSE
294 out_errorcode := pub_errorcode;
295 out_errorbuf := pub_errorbuf;
296 out_insert_records := pub_insert_records;
297 END IF;
298
299 IF PG_DEBUG in ('Y', 'C') THEN
300 arp_util.debug('proc_after_validation: ' || 'arp_lockbox_hook_pvt.proc_after_validation()-');
301 END IF;
302 END proc_after_validation;
303 --
304 /*----------------------------------------------------------------------------
305 proc_after_second_validation
306
307 This procedure will be called after the second validation and before printing
308 Lockbox execution report. It is called from arlplb().
309 If this procedure returns 0,
310 arlplb.opc will understand that this procedure returned success.
311 It will proceed with printing report then.
312 If this procedure returns anything other than 0,
313 arlplb.opc will understand that some error had occured during the
314 processing in this procedure and will exit rolling back the information.
315
316 ----------------------------------------------------------------------------*/
317 PROCEDURE proc_after_second_validation(out_errorbuf OUT NOCOPY VARCHAR2,
318 out_errorcode OUT NOCOPY VARCHAR2,
319 in_trans_req_id IN VARCHAR2) IS
320 pvt_errorbuf varchar2(255);
321 pvt_errorcode varchar2(255);
322 pub_errorbuf varchar2(255);
323 pub_errorcode varchar2(255);
324 BEGIN
325 IF PG_DEBUG in ('Y', 'C') THEN
326 arp_util.debug('arp_lockbox_hook_pvt.proc_after_second_validation()+');
327 END IF;
328 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
329 -- Removed ARTA logic as functionality is obsolete in R12
330 -- See Bug 4936298
331 NULL; -- Do Nothing
332 ELSIF g_okl_installed THEN
333 pvt_errorcode := 0;
334 pvt_errorbuf := NULL;
335 ELSIF g_custom_llca_installed THEN
336 pvt_errorcode := 0;
337 pvt_errorbuf := NULL;
338 ELSE
339 pvt_errorcode := 0;
340 pvt_errorbuf := NULL;
341 END IF;
342
343 -- Now call the public hook
344 arp_lockbox_hook.proc_after_second_validation(pub_errorbuf,pub_errorcode,in_trans_req_id);
345
346 IF pvt_errorcode = 0 THEN
347 out_errorcode := pub_errorcode;
348 out_errorbuf := pub_errorbuf;
349 ELSE
350 out_errorcode := pvt_errorcode;
351 out_errorbuf := pvt_errorbuf;
352 END IF;
353
354 IF PG_DEBUG in ('Y', 'C') THEN
355 arp_util.debug('arp_lockbox_hook_pvt.proc_after_second_validation()-');
356 END IF;
357 END proc_after_second_validation;
358 --
359 /*----------------------------------------------------------------------------
360 proc_for_custom_llca (Added for Bug 6866475)
361
362 This procedure will be called from proc_after_validation if the setup for
363 line level cash application is selected to be custom in lockbox setup.
364
365 This procedure calls the custom package which gives the line level application
366 details, which will be processed in this proc and will be inserted in lockbox
367 interface tables.
368
369 ----------------------------------------------------------------------------*/
370 PROCEDURE proc_for_custom_llca(in_trans_req_id IN NUMBER) IS
371 l_invoice_array invoice_array;
372 l_line_array line_array;
373 l_unres_inv_array invoice_array;
374
375 l_transmission_rec_id_of number;
376 l_last_invoice_index number;
377 l_last_line_index number;
378 l_unres_invoice_index number;
379 l_res_invoice_index number;
380 l_sum_amount_applied_from number;
381 l_rem_amt_applied_from number;
382 i number;
383 j number;
384 k number := 1;
385 l_trans_format_id number;
386 l_overflow_rec varchar2(2);
387 l_transmission_id number;
388 l_lockbox_number varchar2(30);
389 l_batch_name varchar2(30);
390 l_format_amount varchar2(2);
391 l_currency_code varchar2(4);
392 l_precision number;
393 l_inv_precision number;
394 l_org_id number;
395 l_overflow_seq number := 1;
396 l_overflow_indicator varchar2(1) := '1';
397 l_final_rec_overflow_ind varchar2(1);
398 format_amount_app varchar2(2);
399 format_amount_app1 varchar2(2);
400 format_amount_app2 varchar2(2);
401 format_amount_app3 varchar2(2);
402 format_amount_app4 varchar2(2);
403 format_amount_app5 varchar2(2);
404 format_amount_app6 varchar2(2);
405 format_amount_app7 varchar2(2);
406 format_amount_app8 varchar2(2);
407 format_amount1 varchar2(2);
408 format_amount2 varchar2(2);
409 format_amount3 varchar2(2);
410 format_amount4 varchar2(2);
411 format_amount5 varchar2(2);
412 format_amount6 varchar2(2);
413 format_amount7 varchar2(2);
414 format_amount8 varchar2(2);
415 l_batches varchar2(1);
416 l_resolved_number number;
417 l_sql_stmt varchar2(2000);
418 l_upd_stmt varchar2(2000);
419 l_amount_applied_from number;
420 l_trans_to_receipt_rate number;
421 l_invoice_currency_code varchar2(15);
422 l_amount_applied number;
423 l_matching_date date;
424 l_error_flag varchar2(1);
425 l_pay_unrelated_invoices varchar2(1);
426 l_customer_id ar_payments_interface.customer_id%type;
427
428 cursor distinct_item_num( req_id in number ) is
429 select distinct item_number
430 from ar_payments_interface_all
431 where transmission_request_id = req_id;
432
433 cursor overflow_records( request_id in number,
434 itm_num in number,
435 rec_type in varchar) is
436 select transmission_record_id
437 from ar_payments_interface_all
438 where transmission_request_id = request_id
439 and item_number = itm_num
440 and record_type = rec_type
441 order by transmission_record_id;
442
443 CURSOR get_applications( req_id IN NUMBER ) IS
444 SELECT transmission_record_id,
445 trim(item_number) item_number,
446 trim(record_type) record_type,
447 trim(invoice1) invoice1,
448 trim(invoice2) invoice2,
449 trim(invoice3) invoice3,
450 trim(invoice4) invoice4,
451 trim(invoice5) invoice5,
452 trim(invoice6) invoice6,
453 trim(invoice7) invoice7,
454 trim(invoice8) invoice8,
455 amount_applied1,
456 amount_applied2,
457 amount_applied3,
458 amount_applied4,
459 amount_applied5,
460 amount_applied6,
461 amount_applied7,
462 amount_applied8,
463 batch_name
464 FROM ar_payments_interface_all
465 WHERE transmission_request_id = req_id
466 AND record_type in ( select a.record_identifier from ar_trans_record_formats a, ar_transmissions_all b
467 where b.transmission_request_id = req_id
468 and b.requested_trans_format_id = a.transmission_format_id
469 and a.record_type_lookup_code in ('PAYMENT','OVRFLW PAYMENT') );
470 BEGIN
471 IF PG_DEBUG in ('Y', 'C') THEN
472 arp_util.debug('arp_lockbox_hook_pvt.proc_for_custom_llca()+');
473 END IF;
474 /* Check if the format includes batches */
475 BEGIN
476 SELECT distinct 'Y'
477 INTO l_batches
478 FROM ar_trans_field_formats
479 WHERE transmission_format_id = (SELECT transmission_format_id
480 FROM ar_transmission_formats a,
481 ar_transmissions_all b
482 WHERE a.transmission_format_id = b.requested_trans_format_id
483 AND b.transmission_request_id = in_trans_req_id )
484 AND field_type_lookup_code = 'BATCH NAME';
485 EXCEPTION
486 WHEN NO_DATA_FOUND THEN
487 arp_util.debug('No batches present in the transmission');
488 l_batches := 'N';
489 END;
490 /* Get Pay_unrelated_invoices_Flag from ar_system_parameters */
491 BEGIN
492 SELECT nvl(pay_unrelated_invoices_flag, 'N')
493 INTO l_pay_unrelated_invoices
494 FROM ar_system_parameters;
495 EXCEPTION
496 WHEN NO_DATA_FOUND THEN
497 l_pay_unrelated_invoices := 'N';
498 END;
499 /* Populate the l_unres_inv_arr to be passed to the custom procedure
500 with mathcing numbers to be resolved. */
501 FOR app_rec IN get_applications( in_trans_req_id ) LOOP
502 format_amount1 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
503 app_rec.transmission_record_id,'AMT APP 1');
504 format_amount2 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
505 app_rec.transmission_record_id,'AMT APP 2');
506 format_amount3 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
507 app_rec.transmission_record_id,'AMT APP 3');
508 format_amount4 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
509 app_rec.transmission_record_id,'AMT APP 4');
510 format_amount5 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
511 app_rec.transmission_record_id,'AMT APP 5');
512 format_amount6 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
513 app_rec.transmission_record_id,'AMT APP 6');
514 format_amount7 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
515 app_rec.transmission_record_id,'AMT APP 7');
516 format_amount8 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
517 app_rec.transmission_record_id,'AMT APP 8');
518 format_amount_app1 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
519 app_rec.transmission_record_id,'AMT APP FROM 1');
520 format_amount_app2 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
521 app_rec.transmission_record_id,'AMT APP FROM 2');
522 format_amount_app3 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
523 app_rec.transmission_record_id,'AMT APP FROM 3');
524 format_amount_app4 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
525 app_rec.transmission_record_id,'AMT APP FROM 4');
526 format_amount_app5 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
527 app_rec.transmission_record_id,'AMT APP FROM 5');
528 format_amount_app6 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
529 app_rec.transmission_record_id,'AMT APP FROM 6');
530 format_amount_app7 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
531 app_rec.transmission_record_id,'AMT APP FROM 7');
532 format_amount_app8 := ARP_PROCESS_LOCKBOX.get_format_amount(in_trans_req_id,
533 app_rec.transmission_record_id,'AMT APP FROM 8');
534 format_amount_app := format_amount_app1;
535
536 select max(fc.precision)
537 into l_precision
538 from fnd_currencies fc
539 where fc.currency_code =
540 (select max(pi.currency_code)
541 from ar_payments_interface pi,
542 ar_payments_interface pi1
543 where pi.item_number = pi1.item_number
544 and pi1.transmission_request_id = in_trans_req_id
545 and pi1.transmission_record_id = app_rec.transmission_record_id);
546
547 if app_rec.invoice1 is not null then
548 l_unres_inv_array(k).item_number := app_rec.item_number;
549 l_unres_inv_array(k).matching_number := app_rec.invoice1;
550 if format_amount1 = 'Y' then
551 l_unres_inv_array(k).amount_applied := round(
552 app_rec.amount_applied1/power(10,l_precision),
553 l_precision);
554 else
555 l_unres_inv_array(k).amount_applied := app_rec.amount_applied1;
556 end if;
557 l_unres_inv_array(k).invoice_number := NULL;
558 l_unres_inv_array(k).batch_name := app_rec.batch_name;
559 l_unres_inv_array(k).record_type := app_rec.record_type;
560 k := k + 1;
561 end if;
562
563 if app_rec.invoice2 is not null then
564 l_unres_inv_array(k).item_number := app_rec.item_number;
565 l_unres_inv_array(k).matching_number := app_rec.invoice2;
566 if format_amount2 = 'Y' then
567 l_unres_inv_array(k).amount_applied := round(
568 app_rec.amount_applied2/power(10,l_precision),
569 l_precision);
570 else
571 l_unres_inv_array(k).amount_applied := app_rec.amount_applied2;
572 end if;
573 l_unres_inv_array(k).invoice_number := NULL;
574 l_unres_inv_array(k).batch_name := app_rec.batch_name;
575 l_unres_inv_array(k).record_type := app_rec.record_type;
576 k := k + 1;
577 end if;
578
579 if app_rec.invoice3 is not null then
580 l_unres_inv_array(k).item_number := app_rec.item_number;
581 l_unres_inv_array(k).matching_number := app_rec.invoice3;
582 if format_amount3 = 'Y' then
583 l_unres_inv_array(k).amount_applied := round(
584 app_rec.amount_applied3/power(10,l_precision),
585 l_precision);
586 else
587 l_unres_inv_array(k).amount_applied := app_rec.amount_applied3;
588 end if;
589 l_unres_inv_array(k).invoice_number := NULL;
590 l_unres_inv_array(k).batch_name := app_rec.batch_name;
591 l_unres_inv_array(k).record_type := app_rec.record_type;
592 k := k + 1;
593 end if;
594
595 if app_rec.invoice4 is not null then
596 l_unres_inv_array(k).item_number := app_rec.item_number;
597 l_unres_inv_array(k).matching_number := app_rec.invoice4;
598 if format_amount4 = 'Y' then
599 l_unres_inv_array(k).amount_applied := round(
600 app_rec.amount_applied4/power(10,l_precision),
601 l_precision);
602 else
603 l_unres_inv_array(k).amount_applied := app_rec.amount_applied4;
604 end if;
605 l_unres_inv_array(k).invoice_number := NULL;
606 l_unres_inv_array(k).batch_name := app_rec.batch_name;
607 l_unres_inv_array(k).record_type := app_rec.record_type;
608 k := k + 1;
609 end if;
610
611 if app_rec.invoice5 is not null then
612 l_unres_inv_array(k).item_number := app_rec.item_number;
613 l_unres_inv_array(k).matching_number := app_rec.invoice5;
614 if format_amount5 = 'Y' then
615 l_unres_inv_array(k).amount_applied := round(
616 app_rec.amount_applied5/power(10,l_precision),
617 l_precision);
618 else
619 l_unres_inv_array(k).amount_applied := app_rec.amount_applied5;
620 end if;
621 l_unres_inv_array(k).invoice_number := NULL;
622 l_unres_inv_array(k).batch_name := app_rec.batch_name;
623 l_unres_inv_array(k).record_type := app_rec.record_type;
624 k := k + 1;
625 end if;
626
627 if app_rec.invoice6 is not null then
628 l_unres_inv_array(k).item_number := app_rec.item_number;
629 l_unres_inv_array(k).matching_number := app_rec.invoice6;
630 if format_amount6 = 'Y' then
631 l_unres_inv_array(k).amount_applied := round(
632 app_rec.amount_applied6/power(10,l_precision),
633 l_precision);
634 else
635 l_unres_inv_array(k).amount_applied := app_rec.amount_applied6;
636 end if;
637 l_unres_inv_array(k).invoice_number := NULL;
638 l_unres_inv_array(k).batch_name := app_rec.batch_name;
639 l_unres_inv_array(k).record_type := app_rec.record_type;
640 k := k + 1;
641 end if;
642
643 if app_rec.invoice7 is not null then
644 l_unres_inv_array(k).item_number := app_rec.item_number;
645 l_unres_inv_array(k).matching_number := app_rec.invoice7;
646 if format_amount7 = 'Y' then
647 l_unres_inv_array(k).amount_applied := round(
648 app_rec.amount_applied7/power(10,l_precision),
649 l_precision);
650 else
651 l_unres_inv_array(k).amount_applied := app_rec.amount_applied7;
652 end if;
653 l_unres_inv_array(k).invoice_number := NULL;
654 l_unres_inv_array(k).batch_name := app_rec.batch_name;
655 l_unres_inv_array(k).record_type := app_rec.record_type;
656 k := k + 1;
657 end if;
658
659 if app_rec.invoice8 is not null then
660 l_unres_inv_array(k).item_number := app_rec.item_number;
661 l_unres_inv_array(k).matching_number := app_rec.invoice8;
662 if format_amount8 = 'Y' then
663 l_unres_inv_array(k).amount_applied := round(
664 app_rec.amount_applied8/power(10,l_precision),
665 l_precision);
666 else
667 l_unres_inv_array(k).amount_applied := app_rec.amount_applied8;
668 end if;
669 l_unres_inv_array(k).invoice_number := NULL;
670 l_unres_inv_array(k).batch_name := app_rec.batch_name;
671 l_unres_inv_array(k).record_type := app_rec.record_type;
672 k := k + 1;
673 end if;
674
675 END LOOP;
676
677 /* Calling the custom code to return the resolved matching numbers and LLCA Data. */
678 ARP_LOCKBOX_HOOK.cursor_for_custom_llca(l_unres_inv_array,
679 l_invoice_array,
680 l_line_array);
681
682 l_last_invoice_index := l_invoice_array.last;
683 l_unres_invoice_index := l_unres_inv_array.last;
684
685 IF l_unres_invoice_index IS NOT NULL THEN
686 IF l_last_invoice_index IS NOT NULL THEN
687
688 SELECT overflow_rec_indicator
689 INTO l_overflow_indicator
690 FROM ar_trans_field_formats a, ar_transmissions_all b
691 WHERE b.requested_trans_format_id = a.transmission_format_id
692 AND b.transmission_request_id = in_trans_req_id
693 AND a.FIELD_TYPE_LOOKUP_CODE = 'OVRFLW IND';
694
695 IF l_overflow_indicator = '0' THEN
696 l_final_rec_overflow_ind := '1';
697 ELSE
698 l_final_rec_overflow_ind := '0';
699 END IF;
700
701 SELECT b.record_identifier,
702 b.transmission_format_id,
703 a.transmission_id
704 INTO l_overflow_rec, l_trans_format_id, l_transmission_id
705 FROM ar_transmissions_all a,
706 ar_trans_record_formats b
707 WHERE a.requested_trans_format_id = b.transmission_format_id
708 AND a.transmission_request_id = in_trans_req_id
709 AND b.record_type_lookup_code = 'OVRFLW PAYMENT';
710
711 /*
712 * The logic below is like this. For each (matching) number in unresolved array we check *
713 * if it has been resolved. If so, we popualte the resolved array for all those *
714 * (resolved) numbers. If they pass through cross currency validations, if this a cross *
715 * currency application, then a record is inserted into ar_payments_interface *
716 * for each resolved number along with their line level details in ar_pmts_ *
717 * interface_line_details, if any. If a record failed in validation then a record is *
718 * inserted into ar_payments_interface for the matching number, which would eventually fail*
719 * in validation.
720 */
721 arp_util.debug('Number of Invoices inside custom code :' || l_last_invoice_index);
722 FOR i in 1..l_unres_invoice_index LOOP
723 k := 1;
724 DECLARE
725 l_resolved_array invoice_array;
726 BEGIN
727 FOR j in 1..l_last_invoice_index LOOP
728 arp_util.debug('For '|| l_unres_inv_array(i).matching_number);
729 arp_util.debug('And '|| l_invoice_array(j).matching_number);
730 arp_util.debug('And '|| l_invoice_array(j).invoice_number);
731 arp_util.debug('Size :'||l_resolved_array.last || 'K :' ||k);
732 IF l_unres_inv_array(i).matching_number = l_invoice_array(j).matching_number
733 AND l_unres_inv_array(i).item_number = l_invoice_array(j).item_number THEN
734 IF l_batches = 'Y' THEN
735 IF l_unres_inv_array(i).batch_name = l_invoice_array(j).batch_name THEN
736 l_resolved_array(k).matching_number := l_invoice_array(j).matching_number;
737 l_resolved_array(k).item_number := l_invoice_array(j).item_number;
738 l_resolved_array(k).invoice_number := l_invoice_array(j).invoice_number;
739 l_resolved_array(k).amount_applied := l_invoice_array(j).amount_applied;
740 l_resolved_array(k).amount_applied_from := l_invoice_array(j).amount_applied_from;
741 l_resolved_array(k).trans_to_receipt_rate := l_invoice_array(j).trans_to_receipt_rate;
742 l_resolved_array(k).invoice_currency_code := l_invoice_array(j).invoice_currency_code;
743 l_resolved_array(k).batch_name := l_invoice_array(j).batch_name;
744 k := k+1;
745 END IF;
746 ELSE
747 l_resolved_array(k).matching_number := l_invoice_array(j).matching_number;
748 l_resolved_array(k).item_number := l_invoice_array(j).item_number;
749 l_resolved_array(k).invoice_number := l_invoice_array(j).invoice_number;
750 l_resolved_array(k).amount_applied := l_invoice_array(j).amount_applied;
751 l_resolved_array(k).amount_applied_from := l_invoice_array(j).amount_applied_from;
752 l_resolved_array(k).trans_to_receipt_rate := l_invoice_array(j).trans_to_receipt_rate;
753 l_resolved_array(k).invoice_currency_code := l_invoice_array(j).invoice_currency_code;
754 l_resolved_array(k).batch_name := l_invoice_array(j).batch_name;
755 k := k+1;
756 END IF; /* End l_batches */
757 END IF; /* End Populate Resolved Array */
758 END LOOP; /* End inner For */
759 l_res_invoice_index := l_resolved_array.last;
760 l_inv_precision := 0;
761
762 arp_util.debug('Custom Number '||l_unres_inv_array(i).matching_number||' has been resolved into ');
763 arp_util.debug(nvl(l_res_invoice_index, 0)||' invoices.');
764
765 IF l_res_invoice_index IS NOT NULL THEN
766 SELECT a
767 INTO l_resolved_number
768 FROM
769 (SELECT decode(l_unres_inv_array(i).matching_number,
770 invoice1, 1,
771 invoice2, 2,
772 invoice3, 3,
773 invoice4, 4,
774 invoice5, 5,
775 invoice6, 6,
776 invoice7, 7,
777 invoice8, 8) a
778 FROM ar_payments_interface_all
779 WHERE transmission_request_id = in_trans_req_id
780 AND item_number = l_unres_inv_array(i).item_number
781 AND record_type = l_unres_inv_array(i).record_type
782 AND NVL(batch_name, -1)= NVL(l_unres_inv_array(i).batch_name, -1))
783 where a IS NOT NULL;
784
785 l_sql_stmt := 'SELECT amount_applied_from'||l_resolved_number||', trans_to_receipt_rate'
786 ||l_resolved_number||', invoice_currency_code'||l_resolved_number||', customer_id'
787 ||', amount_applied'||l_resolved_number||', matching'||l_resolved_number||'_date'
788 ||' FROM ar_payments_interface_all WHERE transmission_request_id = :1'
789 ||' AND item_number = :2'
790 ||' AND record_type = :3'
791 ||' AND invoice'||l_resolved_number ||'= :4'
792 ||' AND NVL(batch_name, -1) = :5';
793
794 EXECUTE IMMEDIATE l_sql_stmt
795 INTO l_amount_applied_from,
796 l_trans_to_receipt_rate,
797 l_invoice_currency_code,
798 l_customer_id,
799 l_amount_applied,
800 l_matching_date
801 USING in_trans_req_id,
802 l_unres_inv_array(i).item_number,
803 l_unres_inv_array(i).record_type,
804 l_unres_inv_array(i).matching_number,
805 nvl(l_unres_inv_array(i).batch_name, -1);
806
807 IF l_resolved_number = 1 AND format_amount_app1 = 'Y' THEN
808 l_amount_applied_from := round(
809 l_amount_applied_from/power(10,l_precision),
810 l_precision);
811 ELSIF l_resolved_number = 2 AND format_amount_app2 = 'Y' THEN
812 l_amount_applied_from := round(
813 l_amount_applied_from/power(10,l_precision),
814 l_precision);
815 ELSIF l_resolved_number = 3 AND format_amount_app3 = 'Y' THEN
816 l_amount_applied_from := round(
817 l_amount_applied_from/power(10,l_precision),
818 l_precision);
819 ELSIF l_resolved_number = 4 AND format_amount_app4 = 'Y' THEN
820 l_amount_applied_from := round(
821 l_amount_applied_from/power(10,l_precision),
822 l_precision);
823 ELSIF l_resolved_number = 5 AND format_amount_app5 = 'Y' THEN
824 l_amount_applied_from := round(
825 l_amount_applied_from/power(10,l_precision),
826 l_precision);
827 ELSIF l_resolved_number = 6 AND format_amount_app6 = 'Y' THEN
828 l_amount_applied_from := round(
829 l_amount_applied_from/power(10,l_precision),
830 l_precision);
831 ELSIF l_resolved_number = 7 AND format_amount_app7 = 'Y' THEN
832 l_amount_applied_from := round(
833 l_amount_applied_from/power(10,l_precision),
834 l_precision);
835 ELSIF l_resolved_number = 8 AND format_amount_app8 = 'Y' THEN
836 l_amount_applied_from := round(
837 l_amount_applied_from/power(10,l_precision),
838 l_precision);
839 END IF;
840
841
842 IF l_invoice_currency_code IS NOT NULL
843 OR l_trans_to_receipt_rate IS NOT NULL
844 OR l_amount_applied_from IS NOT NULL THEN
845 /* In case where Invoice_currency_code and/or trans_to_receipt_rate is mentioned at
846 the matching number, then they should be the same at the resolved numbers also */
847 FOR j IN 1..l_res_invoice_index LOOP
848 IF (l_trans_to_receipt_rate IS NOT NULL
849 AND l_resolved_array(j).trans_to_receipt_rate IS NOT NULL
850 AND l_trans_to_receipt_rate <> l_resolved_array(j).trans_to_receipt_rate) THEN
851 arp_util.debug('Trans_to_receipt_rate cannot be different to the rate specified at the matching number.');
852 arp_util.debug('For Matching Number '||l_unres_inv_array(i).matching_number);
853 arp_util.debug('For Resolved Number '||l_resolved_array(j).invoice_number);
854 l_error_flag := 'T';
855 exit;
856 END IF; /* End TTR check */
857 IF (l_invoice_currency_code IS NOT NULL
858 AND l_resolved_array(j).invoice_currency_code IS NOT NULL
859 AND l_invoice_currency_code <> l_resolved_array(j).invoice_currency_code) THEN
860 arp_util.debug('Invoice currency code cannot be different to the currency code specified at the matching number.');
861 arp_util.debug('For Matching Number '||l_unres_inv_array(i).matching_number);
862 arp_util.debug('For Resolved Number '||l_resolved_array(j).invoice_number);
863 l_error_flag := 'T';
864 exit;
865 END IF; /* End currency code check */
866 END LOOP;
867
868 /* In any case if amount_applied_from/invoice_currency_code/trans_to_receipt_rate is mentioned
869 at the header level then all the resolved numbers matching this number must be of the same
870 currency(= invoice_currency_code at the matching number, if provided) */
871
872 DECLARE
873 l_res_currency_code VARCHAR2(15);
874 l_currency_code1 VARCHAR2(15);
875 BEGIN
876 l_res_currency_code := trim(l_invoice_currency_code);
877 arp_util.debug('For Matching Number '||l_unres_inv_array(i).matching_number);
878 arp_util.debug('Currency Code '||l_res_currency_code);
879 FOR j in 1..l_res_invoice_index LOOP
880 SELECT distinct(invoice_currency_code)
881 INTO l_currency_code1
882 FROM ar_payment_schedules ps,
883 ra_cust_trx_types tt
884 WHERE ps.trx_number = l_resolved_array(j).invoice_number
885 AND ps.trx_date = nvl(l_matching_date, ps.trx_date)
886 AND ps.status = decode(tt.allow_overapplication_flag,
887 'N', 'OP',
888 ps.status)
889 AND ps.class NOT IN ('PMT','GUAR')
890 AND (ps.customer_id IN
891 (
892 select l_customer_id from dual
893 union
894 select related_cust_account_id
895 from hz_cust_acct_relate rel
896 where rel.cust_account_id = l_customer_id
897 and rel.status = 'A'
898 and rel.bill_to_flag = 'Y'
899 union
900 select rel.related_cust_account_id
901 from ar_paying_relationships_v rel,
902 hz_cust_accounts acc
903 where rel.party_id = acc.party_id
904 and acc.cust_account_id = l_customer_id
905 )
906 or
907 l_pay_unrelated_invoices = 'Y'
908 )
909 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
910
911 l_resolved_array(j).invoice_currency_code := trim(l_currency_code1);
912 l_res_currency_code := nvl(l_res_currency_code, trim(l_currency_code1));
913
914 arp_util.debug('For Resolved Number '||l_resolved_array(j).invoice_number);
915 arp_util.debug('Currency Code '|| l_currency_code1);
916 IF l_res_currency_code <> trim(l_currency_code1) THEN
917 arp_util.debug('All Resolved invoices does not belong to the same currency for matching number '||l_invoice_array(i).matching_number);
918 l_error_flag := 'T';
919 exit;
920 END IF;
921 l_invoice_currency_code := trim(l_currency_code1);
922 END LOOP;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 arp_util.debug('Invalid Application Number '||l_resolved_array(j).invoice_number);
926 l_error_flag := 'T';
927 WHEN TOO_MANY_ROWS THEN
928 arp_util.debug('Too many applications exist with name '||l_resolved_array(j).invoice_number);
929 l_error_flag := 'T';
930 WHEN OTHERS THEN
931 arp_util.debug('Exception occured in resolving '||l_resolved_array(j).invoice_number);
932 arp_util.debug('Error Message '||SQLERRM);
933 l_error_flag := 'T';
934 END; /* End check currency code when not provided */
935
936 IF l_error_flag <> 'T' AND l_invoice_currency_code IS NOT NULL THEN
937 SELECT decode(format_amount_app, 'Y', d.precision, 0)
938 INTO l_inv_precision
939 FROM fnd_currencies d
940 WHERE d.currency_code = trim(l_invoice_currency_code);
941 END IF;
942
943 /* If amount_applied_from is mentioned at both the matching number and resolved number
944 then sum of amount_applied_from's at the resolved numbers must be equal to the amount
945 _applied_from at the mathcing number */
946 IF l_amount_applied_from IS NOT NULL THEN
947 FOR j in 1..l_res_invoice_index LOOP
948 l_sum_amount_applied_from := l_sum_amount_applied_from +
949 nvl(l_resolved_array(j).amount_applied_from, 0);
950 END LOOP;
951 IF l_sum_amount_applied_from <> 0
952 AND l_sum_amount_applied_from <> l_amount_applied_from THEN
953 arp_util.debug('Sum of amount_applied_from at the resolved invoices is not equal to the amount_applied_from specified at matching number.');
954 arp_util.debug('For Matching Number '||l_unres_inv_array(i).matching_number);
955 l_error_flag := 'T';
956 END IF;
957 END IF;/* Check Sum of amount_applied_from */
958
959 /* Prorate Amount_applied_from to the resolved invoices if not provided in custom hook */
960 l_rem_amt_applied_from := nvl(l_amount_applied_from, 0);
961 IF l_error_flag <> 'T' THEN
962 arp_util.debug('Amount_applied_From '||l_amount_applied_from);
963 If l_amount_applied_from IS NOT NULL THEN
964 FOR j in 1..l_res_invoice_index LOOP
965 arp_util.debug('Prorating Amount_applied_from');
966 IF l_resolved_array(j).amount_applied_from IS NULL THEN
967 l_resolved_array(j).amount_applied_from := ROUND(
968 (l_resolved_array(j).amount_applied/l_amount_applied)
969 *l_amount_applied_from);
970 l_rem_amt_applied_from := l_rem_amt_applied_from -
971 l_resolved_array(j).amount_applied_from;
972 END IF;
973 END LOOP;
974 l_resolved_array(l_res_invoice_index).amount_applied_from :=
975 l_resolved_array(l_res_invoice_index).amount_applied_from
976 + l_rem_amt_applied_from;
977 END IF;
978 END IF;/* End prorate amount_applied_from */
979
980 END IF;/* End all validations */
981
982 IF l_error_flag = 'T' THEN
983
984 SELECT ar_payments_interface_s.nextval
985 INTO l_transmission_rec_id_of
986 FROM dual;
987
988 arp_util.debug('Validation failed for '||l_unres_inv_array(i).matching_number);
989
990 /* The Exchange rate info provided at the matching number level does not match
991 with the details provided at the resolved invoice level. So insert the Custom
992 number into the interface tables instead of resolved numbers as an invalid
993 application. */
994
995 l_upd_stmt := 'UPDATE ar_payments_interface_all'
996 ||' SET invoice'||l_resolved_number||'status = ''AR_PLB_INVALID_MATCH'''
997 ||' WHERE transmission_request_id = :1 AND item_number = :2'
998 ||' AND record_type = :3'
999 ||' AND invoice'||l_resolved_number ||'= :4'
1000 ||' AND NVL(batch_name, -1) = :5';
1001
1002 EXECUTE IMMEDIATE l_upd_stmt
1003 USING in_trans_req_id,
1004 l_unres_inv_array(i).item_number,
1005 l_unres_inv_array(i).record_type,
1006 l_unres_inv_array(i).matching_number,
1007 nvl(l_unres_inv_array(i).batch_name, -1);
1008
1009 ELSE
1010 l_upd_stmt := 'UPDATE ar_payments_interface_all'
1011 ||' SET invoice'||l_resolved_number||' = NULL'
1012 ||', amount_applied'||l_resolved_number||' = NULL'
1013 ||' WHERE transmission_request_id = :1 AND item_number = :2'
1014 ||' AND record_type = :3'
1015 ||' AND invoice'||l_resolved_number ||'= :4'
1016 ||' AND NVL(batch_name, -1) = :5';
1017
1018 EXECUTE IMMEDIATE l_upd_stmt
1019 USING in_trans_req_id,
1020 l_unres_inv_array(i).item_number,
1021 l_unres_inv_array(i).record_type,
1022 l_unres_inv_array(i).matching_number,
1023 nvl(l_unres_inv_array(i).batch_name, -1);
1024
1025 SELECT a.org_id,
1026 a.lockbox_number,
1027 a.batch_name,
1028 a.currency_code,
1029 decode(format_amount1,'Y',d.precision,0)
1030 INTO l_org_id, l_lockbox_number, l_batch_name, l_currency_code, l_precision
1031 FROM ar_payments_interface_all a,
1032 ar_transmissions_all b,
1033 ar_trans_record_formats c,
1034 fnd_currencies d
1035 WHERE a.transmission_request_id = b.transmission_request_id
1036 AND b.requested_trans_format_id = c.transmission_format_id
1037 AND c.record_identifier = a.record_type
1038 AND d.currency_code = a.currency_code
1039 AND a.transmission_request_id = in_trans_req_id
1040 AND c.record_type_lookup_code = 'PAYMENT'
1041 AND a.item_number = l_unres_inv_array(i).item_number
1042 AND NVL(a.batch_name, -1) = NVL(l_unres_inv_array(i).batch_name, -1);
1043
1044 IF format_amount_app <> 'Y' THEN
1045 l_inv_precision := 0;
1046 END IF;
1047
1048 FOR j in 1..l_res_invoice_index LOOP
1049
1050 SELECT ar_payments_interface_s.nextval
1051 INTO l_transmission_rec_id_of
1052 FROM dual;
1053
1054 /* Insert a new overflow record for the new invoice number resolved. */
1055 INSERT INTO ar_payments_interface_all(
1056 transmission_record_id,
1057 item_number,
1058 record_type,
1059 status,
1060 transmission_id,
1061 transmission_request_id,
1062 lockbox_number,
1063 batch_name,
1064 invoice1,
1065 amount_applied1,
1066 amount_applied_from1,
1067 trans_to_receipt_rate1,
1068 invoice_currency_code1,
1069 org_id,
1070 creation_date,
1071 last_update_date)
1072 VALUES(
1073 l_transmission_rec_id_of,
1074 l_resolved_array(j).item_number,
1075 l_overflow_rec,
1076 'AR_PLB_NEW_RECORD',
1077 l_transmission_id,
1078 in_trans_req_id,
1079 l_lockbox_number,
1080 l_batch_name,
1081 l_resolved_array(j).invoice_number,
1082 l_resolved_array(j).amount_applied * power(10,l_inv_precision),
1083 l_resolved_array(j).amount_applied_from * power(10,l_precision),
1084 nvl(l_resolved_array(j).trans_to_receipt_rate,
1085 l_trans_to_receipt_rate),
1086 trim(nvl(l_resolved_array(j).invoice_currency_code,
1087 l_invoice_currency_code)),
1088 l_org_id,
1089 sysdate,
1090 trunc(sysdate));
1091
1092 l_last_line_index := l_line_array.last;
1093 /* Check for any line details poulated in the line details table. */
1094 IF l_last_line_index IS NOT NULL THEN
1095 /* Transfer the line level details to the interface_table. */
1096 FOR k IN 1..l_last_line_index LOOP
1097 IF l_resolved_array(j).invoice_number = l_line_array(k).invoice_number
1098 AND l_resolved_array(j).item_number = l_line_array(k).item_number
1099 AND NVL(l_resolved_array(j).batch_name, -1) = NVL(l_line_array(k).batch_name, -1)THEN
1100 arp_util.debug('Inserting lines for '|| l_resolved_array(j).invoice_number);
1101 INSERT INTO AR_PMTS_INTERFACE_LINE_DETAILS (
1102 status,
1103 transmission_request_id,
1104 transmission_record_id,
1105 invoice_number,
1106 apply_to,
1107 amount_applied,
1108 allocated_receipt_amount,
1109 line_amount,
1110 tax,
1111 freight,
1112 charges )
1113 VALUES (
1114 'AR_PLB_NEW_RECORD',
1115 in_trans_req_id,
1116 l_transmission_rec_id_of,
1117 l_line_array(k).invoice_number,
1118 l_line_array(k).apply_to,
1119 l_line_array(k).amount_applied,
1120 l_line_array(k).allocated_receipt_amount,
1121 l_line_array(k).line_amount,
1122 l_line_array(k).tax_amount,
1123 l_line_array(k).freight,
1124 l_line_array(k).charges
1125 );
1126 END IF;
1127 END LOOP;
1128 END IF; /* Insert line Records */
1129 END LOOP; /* Insert Resolved Records */
1130 END IF; /* Insert Overflow records */
1131 END IF; /* End Process Resolved records */
1132 END;
1133 END LOOP; /* End Outer For */
1134
1135 /* Delete the old overflow records for all the receipts, where all the matching numbers in
1136 the overflow record are resolved in custom code i.e, no use in having overflow
1137 records with all invoice1 to invoice8 columns null. */
1138
1139 delete from ar_payments_interface_all
1140 where transmission_request_id = in_trans_req_id
1141 and invoice1 is null
1142 and invoice2 is null
1143 and invoice3 is null
1144 and invoice4 is null
1145 and invoice5 is null
1146 and invoice6 is null
1147 and invoice7 is null
1148 and invoice8 is null
1149 and record_type = l_overflow_rec;
1150
1151 /* Update the interface table overflow records for correct overflow sequence and
1152 indicators value. */
1153
1154 FOR item_num IN distinct_item_num( in_trans_req_id ) LOOP
1155 l_overflow_seq := 1;
1156 FOR record_id IN overflow_records(in_trans_req_id, item_num.item_number, l_overflow_rec ) LOOP
1157 update ar_payments_interface_all
1158 set overflow_sequence = l_overflow_seq,
1159 overflow_indicator = l_overflow_indicator
1160 where transmission_record_id = record_id.transmission_record_id;
1161
1162 l_overflow_seq := l_overflow_seq + 1;
1163 END LOOP;
1164
1165 /* Overflow the last overflow record's overflow indicator to indicate
1166 there are no more further overflow records for the receipt. */
1167
1168 update ar_payments_interface_all
1169 set overflow_indicator = l_final_rec_overflow_ind
1170 where transmission_record_id = (
1171 select max(transmission_record_id)
1172 from ar_payments_interface_all
1173 where transmission_request_id = in_trans_req_id
1174 and item_number = item_num.item_number
1175 and record_type = l_overflow_rec );
1176 END LOOP;
1177
1178 /* Update the transmission record count with correct value if there are
1179 transmission header or trailer records in the transmission. */
1180
1181 update ar_payments_interface_all
1182 set transmission_record_count = (
1183 select count(*) from ar_payments_interface_all
1184 where transmission_request_id = in_trans_req_id )
1185 where transmission_request_id = in_trans_req_id
1186 and record_type in ( select a.record_identifier
1187 from ar_trans_record_formats a, ar_transmissions_all b
1188 where b.transmission_request_id = in_trans_req_id
1189 and b.requested_trans_format_id = a.transmission_format_id
1190 and a.record_type_lookup_code in ('TRANS HDR','TRANS TRL') );
1191
1192 END IF;
1193 END IF;
1194
1195 IF PG_DEBUG in ('Y', 'C') THEN
1196 arp_util.debug('arp_lockbox_hook_pvt.proc_for_custom_llca()-');
1197 END IF;
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 IF PG_DEBUG in ('Y', 'C') THEN
1201 arp_util.debug('Exception in proc_for_custom_llca');
1202 arp_util.debug('Error : '||SQLERRM);
1203 fnd_file.put_line(fnd_file.log, 'Exception in proc_for_custom_llca');
1204 fnd_file.put_line(fnd_file.log, 'Error : '||SQLERRM);
1205 END IF;
1206 RAISE;
1207 END proc_for_custom_llca;
1208 --
1209 END arp_lockbox_hook_pvt;
[Home] [Help]