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