[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_LOCKBOX
Source
1 PACKAGE BODY ARP_PROCESS_LOCKBOX AS
2 /*$Header: ARRPRLBB.pls 120.36.12020000.3 2012/11/21 07:15:15 ashlkuma ship $*/
3 TYPE option_record IS RECORD(
4 option_name ar_lookups.lookup_code%type,
5 cursor_name INTEGER);
6 TYPE opened_cursors IS table of option_record
7 INDEX BY BINARY_INTEGER;
8 TYPE curr_record IS RECORD ( currency_code fnd_currencies.currency_code%TYPE,
9 precision fnd_currencies.precision%TYPE );
10 TYPE table_curr IS TABLE OF curr_record INDEX BY BINARY_INTEGER;
11 --
12 opened_cursor_table opened_cursors;
13 g_matching_option ar_lookups.lookup_code%type;
14 g_cursor_name INTEGER;
15 g_total_maching_options INTEGER;
16 g_cursor_string VARCHAR2(32767);
17 l_cursor_string VARCHAR2(32767);
18 g_include_closed_inv VARCHAR2(1); /* Bug 9156980 */
19 l_table_curr table_curr;
20 --
21 CURSOR all_matching_options IS
22 select LOOKUP_CODE
23 from ar_lookups
24 where LOOKUP_TYPE = 'ARLPLB_MATCHING_OPTION'
25 order by decode(LOOKUP_CODE, 'INVOICE', 1,
26 'SALES_ORDER', 2,
27 'PURCHASE_ORDER', 3,
28 'CONSOLIDATE_BILL', 4,
29 100);
30 --
31 --
32 /*----------------------------------------------------------------------------
33 | Procedure
34 | auto_associate
35 |
36 | Logic:
37 | Fetch (using cursor matching_number) all 8 matching numbers, matching dates
38 | and installments for each record, for given lockbox/batch and item number.
39 | Call find_cust_and_trx_num for each payment/overflow record to get the
40 | customer id and trx numbers.
41 | If find_cust_and_trx_num returns matched_flag = TRUE, then compare the
42 | l_customer_id with l_prev_customer_id. If they are same update the
43 | record in ar_payments_interface for returned trx_number, trx_date and
44 | installment number.
45 | If find_cust_and_trx_num returns matched_flag = FALSE, then rollback
46 | the updates for current item number.
47 | If all the customers are same for given item number,
48 | return p_out_customer_identified = 1, p_out_customer_id = identfied customer id.
49 | Else return p_out_customer_identified = 0, p_out_customer_id = NULL.
50 |
51 | Modification History
52 | 28-Jul-97 K Trivedi Created.
53 | 24-Sep-97 K Trivedi Modified to populate match_resolved_using
54 | column in ar_payments_interface.
55 ----------------------------------------------------------------------------*/
56 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
57
58 PROCEDURE auto_associate(
59 p_transmission_id IN VARCHAR2,
60 p_payment_rec_type IN VARCHAR2,
61 p_overflow_rec_type IN VARCHAR2,
62 p_item_num IN ar_payments_interface.item_number%type,
63 p_batch_name IN ar_payments_interface.batch_name%type,
64 p_lockbox_number IN ar_payments_interface.lockbox_number%type,
65 p_batches IN VARCHAR2,
66 p_only_one_lb IN VARCHAR2,
67 p_use_matching_date IN ar_lockboxes.use_matching_date%type,
68 p_lockbox_matching_option IN ar_lockboxes.lockbox_matching_option%type,
69 p_pay_unrelated_invoices IN VARCHAR2,
70 p_out_customer_id OUT NOCOPY NUMBER,
71 p_out_customer_identified OUT NOCOPY NUMBER
72 ) IS
73 --
74 l_transmission_id VARCHAR2(50);
75 l_payment_rec_type VARCHAR2(3);
76 l_overflow_rec_type VARCHAR2(3);
77 l_item_num ar_payments_interface.item_number%type;
78 l_batch_name ar_payments_interface.batch_name%type;
79 l_lockbox_number ar_payments_interface.lockbox_number%type;
80 l_batches VARCHAR2(2);
81 l_only_one_lb VARCHAR2(2);
82 l_use_matching_date ar_lockboxes.use_matching_date%type;
83 l_lockbox_matching_option ar_lockboxes.lockbox_matching_option%type;
84 l_match_flag VARCHAR2(10);
85 l_rowid ROWID;
86 l_pay_unrelated_invoices VARCHAR2(2);
87 l_receipt_date ar_payments_interface.receipt_date%type;
88 l_no_batch_or_lb VARCHAR2(2); -- bug2980051
89 --
90 l_matching_number1 ar_payments_interface.invoice1%type;
91 l_matching_number2 ar_payments_interface.invoice2%type;
92 l_matching_number3 ar_payments_interface.invoice3%type;
93 l_matching_number4 ar_payments_interface.invoice4%type;
94 l_matching_number5 ar_payments_interface.invoice5%type;
95 l_matching_number6 ar_payments_interface.invoice6%type;
96 l_matching_number7 ar_payments_interface.invoice7%type;
97 l_matching_number8 ar_payments_interface.invoice8%type;
98 --
99 l_matching1_date ar_payments_interface.matching1_date%type;
100 l_matching2_date ar_payments_interface.matching2_date%type;
101 l_matching3_date ar_payments_interface.matching3_date%type;
102 l_matching4_date ar_payments_interface.matching4_date%type;
103 l_matching5_date ar_payments_interface.matching5_date%type;
104 l_matching6_date ar_payments_interface.matching6_date%type;
105 l_matching7_date ar_payments_interface.matching7_date%type;
106 l_matching8_date ar_payments_interface.matching8_date%type;
107 --
108 l_matching1_installment ar_payments_interface.invoice1_installment%type;
109 l_matching2_installment ar_payments_interface.invoice2_installment%type;
110 l_matching3_installment ar_payments_interface.invoice3_installment%type;
111 l_matching4_installment ar_payments_interface.invoice4_installment%type;
112 l_matching5_installment ar_payments_interface.invoice5_installment%type;
113 l_matching6_installment ar_payments_interface.invoice6_installment%type;
114 l_matching7_installment ar_payments_interface.invoice7_installment%type;
115 l_matching8_installment ar_payments_interface.invoice8_installment%type;
116 --
117 l_match1_status ar_payments_interface.invoice1_status%type;
118 l_match2_status ar_payments_interface.invoice2_status%type;
119 l_match3_status ar_payments_interface.invoice3_status%type;
120 l_match4_status ar_payments_interface.invoice4_status%type;
121 l_match5_status ar_payments_interface.invoice5_status%type;
122 l_match6_status ar_payments_interface.invoice6_status%type;
123 l_match7_status ar_payments_interface.invoice7_status%type;
124 l_match8_status ar_payments_interface.invoice8_status%type;
125 --
126 l_prev_customer_id ar_payments_interface.customer_id%type;
127 l_customer_id ar_payments_interface.customer_id%type;
128 l_matching_option ar_lookups.lookup_code%type;
129 --
130 unexpected_program_error EXCEPTION;
131 --
132 /* Bug2980051. Replaced l_only_one_lb with l_no_batch_or_lb */
133 CURSOR matching_numbers IS
134 select
135 rowid,
136 receipt_date,
137 invoice1, matching1_date, invoice1_installment,
138 invoice2, matching2_date, invoice2_installment,
139 invoice3, matching3_date, invoice3_installment,
140 invoice4, matching4_date, invoice4_installment,
141 invoice5, matching5_date, invoice5_installment,
142 invoice6, matching6_date, invoice6_installment,
143 invoice7, matching7_date, invoice7_installment,
144 invoice8, matching8_date, invoice8_installment
145 from ar_payments_interface pi
146 where pi.transmission_id = l_transmission_id
147 and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
148 and pi.customer_id is null
149 and pi.status in ('AR_PLB_CONTROLS_OK','AR_PLB_NO_CUST')
150 and pi.item_number = l_item_num
151 and ( pi.batch_name = l_batch_name
152 or
153 ( pi.lockbox_number = l_lockbox_number
154 and
155 l_batches = 'N'
156 )
157 or
158 l_no_batch_or_lb = 'Y'
159 );
160 --
161 BEGIN
162 debug1('arp_process_lockbox.auto_associate()+');
163 --
164 -- Assign variables to local values.
165 --
166 l_transmission_id := p_transmission_id;
167 l_payment_rec_type := p_payment_rec_type;
168 l_overflow_rec_type := p_overflow_rec_type;
169 l_item_num := p_item_num;
170 l_batch_name := p_batch_name;
171 l_lockbox_number := p_lockbox_number;
172 l_batches := p_batches;
173 l_only_one_lb := p_only_one_lb;
174 l_use_matching_date := p_use_matching_date;
175 l_lockbox_matching_option := p_lockbox_matching_option;
176 l_pay_unrelated_invoices := p_pay_unrelated_invoices;
177
178 /* Bugfix 2980051 */
179 IF l_batches = 'N' AND l_only_one_lb = 'Y' THEN
180 l_no_batch_or_lb := 'Y';
181 ELSE
182 l_no_batch_or_lb := 'N';
183 END IF;
184 --
185 debug1('Input parameters to the package...');
186 debug1('l_transmission_id ' || l_transmission_id);
187 debug1('l_payment_rec_type ' || l_payment_rec_type);
188 debug1('l_overflow_rec_type ' || l_overflow_rec_type);
189 debug1('l_item_num ' || to_char(l_item_num));
190 debug1('l_batch_name ' || l_batch_name);
191 debug1('l_lockbox_number ' || l_lockbox_number);
192 debug1('l_batches ' || l_batches);
193 debug1('l_only_one_lb ' || l_only_one_lb);
194 debug1('l_use_matching_date ' || l_use_matching_date);
195 debug1('l_lockbox_matching_option ' || l_lockbox_matching_option);
196 debug1('l_pay_unrelated_invoices ' || l_pay_unrelated_invoices);
197 debug1('l_no_batch_or_lb ' || l_no_batch_or_lb); -- bug2980051
198 --
199 -- Define Save point here. If unique customer cannot be identified for
200 -- all the matching numbers passed for given item, we need to rollback
201 -- the updates made in this procedure. That time we will rollback to
202 -- this point.
203 --
204 --
205 SAVEPOINT before_this_item;
206 --
207 /* l_prev_customer_id = -8888, indicates that sofar find_cust_and_trx_num
208 has NOT returned match_flag TRUE. If find_cust_and_trx_num returns
209 TRUE, it means there was atleast one matching number for which the
210 customer could be identified. */
211 --
212 l_prev_customer_id := -8888;
213 --
214 /* Logic with l_matching_option is as follows:
215 Let's say there are four overflow records with a receipt record.
216 If the invoice1 mentioned on 1st record matches to a purchase order
217 number, the program should expect other numbers on that receipt also
218 as purchase order number and need not match them with Invoice and/or
219 Sales order number.
220
221 When there is a new receipt record (i.e., new item number)
222 it should start matching with All. Therefore initilising
223 l_matching_option as 'ALL' everytime there is a new item number.
224
225 find_cust_and_trx_num will return p_in_out_matching_option, once it finds
226 a match. Next record in loop onwards till the end of loop,
227 find_cust_and_trx_num will match only using that l_matching_option.
228 */
229 --
230 l_matching_option := 'ALL';
231 --
232 --
233 OPEN matching_numbers;
234 debug1('Opened cursor matching_numbers.');
235 --
236 LOOP
237 FETCH matching_numbers INTO
238 l_rowid,
239 l_receipt_date,
240 l_matching_number1, l_matching1_date, l_matching1_installment,
241 l_matching_number2, l_matching2_date, l_matching2_installment,
242 l_matching_number3, l_matching3_date, l_matching3_installment,
243 l_matching_number4, l_matching4_date, l_matching4_installment,
244 l_matching_number5, l_matching5_date, l_matching5_installment,
245 l_matching_number6, l_matching6_date, l_matching6_installment,
246 l_matching_number7, l_matching7_date, l_matching7_installment,
247 l_matching_number8, l_matching8_date, l_matching8_installment;
248 EXIT WHEN matching_numbers%NOTFOUND;
249 --
250 debug1('Fetched data for cursor matching_numbers.');
251 debug1('l_matching_number1 ' || l_matching_number1);
252 debug1('l_matching1_installment ' || l_matching1_installment);
253 debug1('l_matching1_date ' || to_char(l_matching1_date));
254 --
255 -- There are two matching options passed to find_cust_and_trx_num.
256 -- lockbox_matching_option is the one, which is setup at the
257 -- lockbox level for the current lockbox.
258 -- whereas, l_matching_option is the one matched for the current
259 -- receipt.
260 --
261 find_cust_and_trx_num(
262 p_transmission_id=>l_transmission_id,
263 p_payment_rec_type=>l_payment_rec_type,
264 p_overflow_rec_type=>l_overflow_rec_type,
265 p_item_num=>l_item_num,
266 p_batch_name=>l_batch_name,
267 p_lockbox_number=>l_lockbox_number,
268 p_batches=>l_batches,
269 p_receipt_date=>l_receipt_date,
270 p_only_one_lb=>l_only_one_lb,
271 p_use_matching_date=>l_use_matching_date,
272 p_lockbox_matching_option=>l_lockbox_matching_option,
273 p_pay_unrelated_invoices=>l_pay_unrelated_invoices,
274 p_matching_number1=>l_matching_number1,
275 p_matching1_date=>l_matching1_date,
276 p_matching1_installment=>l_matching1_installment,
277 p_matching_number2=>l_matching_number2,
278 p_matching2_date=>l_matching2_date,
279 p_matching2_installment=>l_matching2_installment,
280 p_matching_number3=>l_matching_number3,
281 p_matching3_date=>l_matching3_date,
282 p_matching3_installment=>l_matching3_installment,
283 p_matching_number4=>l_matching_number4,
284 p_matching4_date=>l_matching4_date,
285 p_matching4_installment=>l_matching4_installment,
286 p_matching_number5=>l_matching_number5,
287 p_matching5_date=>l_matching5_date,
288 p_matching5_installment=>l_matching5_installment,
289 p_matching_number6=>l_matching_number6,
290 p_matching6_date=>l_matching6_date,
291 p_matching6_installment=>l_matching6_installment,
292 p_matching_number7=>l_matching_number7,
293 p_matching7_date=>l_matching7_date,
294 p_matching7_installment=>l_matching7_installment,
295 p_matching_number8=>l_matching_number8,
296 p_matching8_date=>l_matching8_date,
297 p_matching8_installment=>l_matching8_installment,
298 p_matched_flag=>l_match_flag,
299 p_customer_id=>l_customer_id,
300 p_matching_option=>l_matching_option,
301 p_match1_status=>l_match1_status,
302 p_match2_status=>l_match2_status,
303 p_match3_status=>l_match3_status,
304 p_match4_status=>l_match4_status,
305 p_match5_status=>l_match5_status,
306 p_match6_status=>l_match6_status,
307 p_match7_status=>l_match7_status,
308 p_match8_status=>l_match8_status
309 );
310 --
311 IF (l_match_flag = 'PROGRAM_ERROR') THEN
312 raise unexpected_program_error;
313 END IF;
314 --
315 IF ((l_match_flag = 'TRUE') and (l_prev_customer_id = -8888) and
316 (l_customer_id is NOT NULL)) THEN
317 --
318 debug1('first_customer has been identified using matching number');
319 --
320 -- match_flag will be returned TRUE only if there was no mismatch
321 -- among eight invoices passed and customer could be identified
322 --
323 l_prev_customer_id := l_customer_id;
324 END IF;
325 --
326 IF ((l_customer_id = l_prev_customer_id) AND (l_match_flag = 'TRUE')) THEN
327 --
328 -- If the customer_id was identified uniquely,
329 -- update the current record of ar_payments_interface table
330 -- with returned values from find_cust_and_trx_num procedure.
331 -- Also update the status to indicate that Resolved columns were
332 -- populated.
333 -- customer_id is populated in arlvaa(), here we are updating the
334 -- trx_number values and related details. It is possible that
335 -- customer_id was identified, but trx_number was not identified
336 -- uniquely. In that case matching_number will be NULL.
337 --
338 debug1('Updating ar_payments_interface for resolved columns..');
339 debug1('l_matching_number1 ' || l_matching_number1);
340 debug1('l_matching1_installment ' || l_matching1_installment);
341 debug1('l_matching1_date ' || to_char(l_matching1_date));
342 --
343 UPDATE ar_payments_interface
344 SET resolved_matching_number1 = l_matching_number1,
345 resolved_matching1_installment = l_matching1_installment,
346 resolved_matching1_date = l_matching1_date,
347 invoice1_status = l_match1_status,
348 resolved_matching_number2 = l_matching_number2,
349 resolved_matching2_installment = l_matching2_installment,
350 resolved_matching2_date = l_matching2_date,
351 invoice2_status = l_match2_status,
352 resolved_matching_number3 = l_matching_number3,
353 resolved_matching3_installment = l_matching3_installment,
354 resolved_matching3_date = l_matching3_date,
355 invoice3_status = l_match3_status,
356 resolved_matching_number4 = l_matching_number4,
357 resolved_matching4_installment = l_matching4_installment,
358 resolved_matching4_date = l_matching4_date,
359 invoice4_status = l_match4_status,
360 resolved_matching_number5 = l_matching_number5,
361 resolved_matching5_installment = l_matching5_installment,
362 resolved_matching5_date = l_matching5_date,
363 invoice5_status = l_match5_status,
364 resolved_matching_number6 = l_matching_number6,
365 resolved_matching6_installment = l_matching6_installment,
366 resolved_matching6_date = l_matching6_date,
367 invoice6_status = l_match6_status,
368 resolved_matching_number7 = l_matching_number7,
369 resolved_matching7_installment = l_matching7_installment,
370 resolved_matching7_date = l_matching7_date,
371 invoice7_status = l_match7_status,
372 resolved_matching_number8 = l_matching_number8,
373 resolved_matching8_installment = l_matching8_installment,
374 resolved_matching8_date = l_matching8_date,
375 invoice8_status = l_match8_status,
376 match_resolved_using = l_matching_option
377 WHERE rowid = l_rowid;
378 /*5052049*/
379 IF l_matching_option='CONSOLIDATE_BILL' then
380 UPDATE AR_PAYMENTS_INTERFACE SET tmp_amt_applied1=amount_applied1,
381 tmp_amt_applied2=amount_applied2,
382 tmp_amt_applied3=amount_applied3,
383 tmp_amt_applied4=amount_applied4,
384 tmp_amt_applied5=amount_applied5,
385 tmp_amt_applied6=amount_applied6,
386 tmp_amt_applied7=amount_applied7,
387 tmp_amt_applied8=amount_applied8,
388 amount_applied1 = null,
389 amount_applied2= null,
390 amount_applied3= null,
391 amount_applied4= null,
392 amount_applied5= null,
393 amount_applied6= null,
394 amount_applied7= null,
395 amount_applied8= null
396 WHERE rowid = l_rowid and match_resolved_using='CONSOLIDATE_BILL';
397 END IF;
398
399 --
400 -- Using rowid here for getting the current record, as ar_payments_interface
401 -- does not have any other primary key.
402 --
403 /* Bug 2106408. Added the following ELSIF */
404 ELSIF (l_match_flag = 'FALSE') AND
405 (l_customer_id = -7777)
406 THEN
407 debug1('Auto_associate : Duplicate Matching Number');
408 CLOSE matching_numbers;
409 ROLLBACK TO SAVEPOINT before_this_item;
410 GOTO return_duplicate;
411 --
412 ELSIF ((l_match_flag = 'FALSE') OR
413 (l_customer_id <> l_prev_customer_id) OR
414 (l_match_flag = 'NO_MATCH'))
415 THEN
416 --
417 -- If find_cust_and_trx_num returns l_match_flag = FALSE
418 -- or it returns the customer_id that was not same as l_prev_customer_id
419 -- we cannot identify the customer uniquely.
420 -- Close the cursor, Rollback to savepoint and Goto return_no_match
421 -- in this case.
422 --
423 debug1('Rolling back to the savepoint l_match_flag ' || l_match_flag);
424 debug1('l_customer_id ' || to_char(l_customer_id));
425 debug1('l_prev_customer_id ' || to_char(l_prev_customer_id));
426 --
427 CLOSE matching_numbers;
428 ROLLBACK TO SAVEPOINT before_this_item;
429 --
430 debug1('Closed cursor matching_numbers and Rolled back ');
431 GOTO return_no_match;
432 END IF;
433 --
434 --
435 END LOOP; -- End loop for matching_numbers cursor.
436 CLOSE matching_numbers;
437 --
438 -- Check if there was any record that had matching numbers
439 -- and we could identify the customer from that.
440 --
441 IF (l_prev_customer_id = -8888) THEN
442 -- actually in this case there were no database changes made
443 -- but as we could not resolve customer_id, so we want to
444 -- go back to a point from where we started.
445 ROLLBACK TO SAVEPOINT before_this_item;
446 GOTO return_no_match;
447 END IF;
448 --
449 -- If the program reached here, means all records were successfully
450 -- identified to a single customer.
451 --
452 p_out_customer_id := l_customer_id;
453 p_out_customer_identified := 1;
454 debug1('arp_process_lockbox.auto_associate(1)-');
455 RETURN;
456 --
457 <<return_no_match>>
458 -- If there was no match found, then also we must populate the
459 -- resolved columns as the program looks at resolved columns only
460 -- from hence forth.
461 /* Bug2980051. Replaced l_only_one_lb with l_no_batch_or_lb */
462 UPDATE ar_payments_interface pi
463 SET resolved_matching_number1 = SUBSTRB(invoice1, 1, 20),
464 resolved_matching1_installment = invoice1_installment,
465 resolved_matching1_date = matching1_date,
466 invoice1_status = decode(invoice1, null, null, 'AR_PLB_INVALID_MATCH'),
467 resolved_matching_number2 = SUBSTRB(invoice2, 1, 20),
468 resolved_matching2_installment = invoice2_installment,
469 resolved_matching2_date = matching2_date,
470 invoice2_status = decode(invoice2, null, null, 'AR_PLB_INVALID_MATCH'),
471 resolved_matching_number3 = SUBSTRB(invoice3, 1, 20),
472 resolved_matching3_installment = invoice3_installment,
473 resolved_matching3_date = matching3_date,
474 invoice3_status = decode(invoice3, null, null, 'AR_PLB_INVALID_MATCH'),
475 resolved_matching_number4 = SUBSTRB(invoice4, 1, 20),
476 resolved_matching4_installment = invoice4_installment,
477 resolved_matching4_date = matching4_date,
478 invoice4_status = decode(invoice4, null, null, 'AR_PLB_INVALID_MATCH'),
479 resolved_matching_number5 = SUBSTRB(invoice5, 1, 20),
480 resolved_matching5_installment = invoice5_installment,
481 resolved_matching5_date = matching5_date,
482 invoice5_status = decode(invoice5, null, null, 'AR_PLB_INVALID_MATCH'),
483 resolved_matching_number6 = SUBSTRB(invoice6, 1, 20),
484 resolved_matching6_installment = invoice6_installment,
485 resolved_matching6_date = matching6_date,
486 invoice6_status = decode(invoice6, null, null, 'AR_PLB_INVALID_MATCH'),
487 resolved_matching_number7 = SUBSTRB(invoice7, 1, 20),
488 resolved_matching7_installment = invoice7_installment,
489 resolved_matching7_date = matching7_date,
490 invoice7_status = decode(invoice7, null, null, 'AR_PLB_INVALID_MATCH'),
491 resolved_matching_number8 = SUBSTRB(invoice8, 1, 20),
492 resolved_matching8_installment = invoice8_installment,
493 resolved_matching8_date = matching8_date,
494 invoice8_status = decode(invoice8, null, null, 'AR_PLB_INVALID_MATCH'),
495 match_resolved_using = null
496 where pi.transmission_id = l_transmission_id
497 and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
498 and pi.customer_id is null
499 and pi.status = 'AR_PLB_CONTROLS_OK'
500 and pi.item_number = l_item_num
501 and ( pi.batch_name = l_batch_name
502 or
503 ( pi.lockbox_number = l_lockbox_number
504 and
505 l_batches = 'N'
506 )
507 or
508 l_no_batch_or_lb = 'Y'
509 );
510 --
511 -- Following values will be used by arlvaa()
512 p_out_customer_identified := 0;
513 p_out_customer_id := NULL;
514 debug1('arp_process_lockbox.auto_associate(2)-');
515 RETURN;
516 /* Bug 2106408. Added the following UPDATE */
517 <<return_duplicate>>
518 /* Bug2980051. Replaced l_only_one_lb with l_no_batch_or_lb */
519 UPDATE ar_payments_interface pi
520 SET resolved_matching_number1 = SUBSTRB(invoice1, 1, 20),
521 resolved_matching1_installment = invoice1_installment,
522 resolved_matching1_date = matching1_date,
523 invoice1_status = decode(invoice1, null, null, 'AR_PLB_DUP_INV'),
524 resolved_matching_number2 = SUBSTRB(invoice2, 1, 20),
525 resolved_matching2_installment = invoice2_installment,
526 resolved_matching2_date = matching2_date,
527 invoice2_status = decode(invoice2, null, null, 'AR_PLB_DUP_INV'),
528 resolved_matching_number3 = SUBSTRB(invoice3, 1, 20),
529 resolved_matching3_installment = invoice3_installment,
530 resolved_matching3_date = matching3_date,
531 invoice3_status = decode(invoice3, null, null, 'AR_PLB_DUP_INV'),
532 resolved_matching_number4 = SUBSTRB(invoice4, 1, 20),
533 resolved_matching4_installment = invoice4_installment,
534 resolved_matching4_date = matching4_date,
535 invoice4_status = decode(invoice4, null, null, 'AR_PLB_DUP_INV'),
536 resolved_matching_number5 = SUBSTRB(invoice5, 1, 20),
537 resolved_matching5_installment = invoice5_installment,
538 resolved_matching5_date = matching5_date,
539 invoice5_status = decode(invoice5, null, null, 'AR_PLB_DUP_INV'),
540 resolved_matching_number6 = SUBSTRB(invoice6, 1, 20),
541 resolved_matching6_installment = invoice6_installment,
542 resolved_matching6_date = matching6_date,
543 invoice6_status = decode(invoice6, null, null, 'AR_PLB_DUP_INV'),
544 resolved_matching_number7 = SUBSTRB(invoice7, 1, 20),
545 resolved_matching7_installment = invoice7_installment,
546 resolved_matching7_date = matching7_date,
547 invoice7_status = decode(invoice7, null, null, 'AR_PLB_DUP_INV'),
548 resolved_matching_number8 = SUBSTRB(invoice8, 1, 20),
549 resolved_matching8_installment = invoice8_installment,
550 resolved_matching8_date = matching8_date,
551 invoice8_status = decode(invoice8, null, null, 'AR_PLB_DUP_INV'),
552 match_resolved_using = null
553 where pi.transmission_id = l_transmission_id
554 and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
555 and pi.customer_id is null
556 and pi.status = 'AR_PLB_CONTROLS_OK'
557 and pi.item_number = l_item_num
558 and ( pi.batch_name = l_batch_name
559 or
560 ( pi.lockbox_number = l_lockbox_number
561 and
562 l_batches = 'N'
563 )
564 or
565 l_no_batch_or_lb = 'Y'
566 );
567 p_out_customer_identified := 0;
568 p_out_customer_id := NULL;
569 debug1('arp_process_lockbox.auto_associate : Duplicate Matching');
570 RETURN;
571 --
572 EXCEPTION
573 -- WHEN no_data_found
574 -- THEN null;
575 WHEN others
576 THEN RAISE;
577 debug1('arp_process_lockbox.auto_associate(3)-');
578 RETURN;
579 END auto_associate;
580 --
581 --
582 /*----------------------------------------------------------------------------
583 | Procedure
584 | populate_resolved_columns
585 |
586 | Logic:
587 | Fetch (using cursor matching_number) all 8 matching numbers, matching dates
588 | and installments for each record, for given lockbox/batch and item number.
589 | Call find_cust_and_trx_num for each payment/overflow record to get the
590 | trx numbers.
591 | Update record in ar_payments_interface for returned trx_number, trx_date and
592 | installment number.
593 |
594 | Modification History
595 | 30-Jul-97 K Trivedi Created. Rel 11 related changes.
596 | 24-Sep-97 K Trivedi Modified to populate match_resolved_using
597 | column in ar_payments_interface.
598 |
599 ----------------------------------------------------------------------------*/
600 PROCEDURE populate_resolved_columns(
601 p_transmission_id IN VARCHAR2,
602 p_payment_rec_type IN VARCHAR2,
603 p_overflow_rec_type IN VARCHAR2,
604 p_item_num IN ar_payments_interface.item_number%type,
605 p_batch_name IN ar_payments_interface.batch_name%type,
606 p_lockbox_number IN ar_payments_interface.lockbox_number%type,
607 p_batches IN VARCHAR2,
608 p_only_one_lb IN VARCHAR2,
609 p_use_matching_date IN ar_lockboxes.use_matching_date%type,
610 p_lockbox_matching_option IN ar_lockboxes.lockbox_matching_option%type,
611 p_pay_unrelated_invoices IN VARCHAR2
612 ) IS
613 --
614 l_transmission_id VARCHAR2(50);
615 l_payment_rec_type VARCHAR2(3);
616 l_overflow_rec_type VARCHAR2(3);
617 l_item_num ar_payments_interface.item_number%type;
618 l_batch_name ar_payments_interface.batch_name%type;
619 l_lockbox_number ar_payments_interface.lockbox_number%type;
620 l_batches VARCHAR2(2);
621 l_only_one_lb VARCHAR2(2);
622 l_use_matching_date ar_lockboxes.use_matching_date%type;
623 l_lockbox_matching_option ar_lockboxes.lockbox_matching_option%type;
624 l_match_flag VARCHAR2(10);
625 l_rowid ROWID;
626 l_pay_unrelated_invoices VARCHAR2(2);
627 l_receipt_date ar_payments_interface.receipt_date%type;
628
629 l_no_batch_or_lb VARCHAR2(2); -- bug2980051
630 --
631 l_matching_number1 ar_payments_interface.invoice1%type;
632 l_matching_number2 ar_payments_interface.invoice2%type;
633 l_matching_number3 ar_payments_interface.invoice3%type;
634 l_matching_number4 ar_payments_interface.invoice4%type;
635 l_matching_number5 ar_payments_interface.invoice5%type;
636 l_matching_number6 ar_payments_interface.invoice6%type;
637 l_matching_number7 ar_payments_interface.invoice7%type;
638 l_matching_number8 ar_payments_interface.invoice8%type;
639 --
640 l_matching1_date ar_payments_interface.matching1_date%type;
641 l_matching2_date ar_payments_interface.matching2_date%type;
642 l_matching3_date ar_payments_interface.matching3_date%type;
643 l_matching4_date ar_payments_interface.matching4_date%type;
644 l_matching5_date ar_payments_interface.matching5_date%type;
645 l_matching6_date ar_payments_interface.matching6_date%type;
646 l_matching7_date ar_payments_interface.matching7_date%type;
647 l_matching8_date ar_payments_interface.matching8_date%type;
648 --
649 l_matching1_installment ar_payments_interface.invoice1_installment%type;
650 l_matching2_installment ar_payments_interface.invoice2_installment%type;
651 l_matching3_installment ar_payments_interface.invoice3_installment%type;
652 l_matching4_installment ar_payments_interface.invoice4_installment%type;
653 l_matching5_installment ar_payments_interface.invoice5_installment%type;
654 l_matching6_installment ar_payments_interface.invoice6_installment%type;
655 l_matching7_installment ar_payments_interface.invoice7_installment%type;
656 l_matching8_installment ar_payments_interface.invoice8_installment%type;
657 --
658 l_match1_status ar_payments_interface.invoice1_status%type;
659 l_match2_status ar_payments_interface.invoice2_status%type;
660 l_match3_status ar_payments_interface.invoice3_status%type;
661 l_match4_status ar_payments_interface.invoice4_status%type;
662 l_match5_status ar_payments_interface.invoice5_status%type;
663 l_match6_status ar_payments_interface.invoice6_status%type;
664 l_match7_status ar_payments_interface.invoice7_status%type;
665 l_match8_status ar_payments_interface.invoice8_status%type;
666 --
667 l_customer_id ar_payments_interface.customer_id%type;
668 l_matching_option ar_lookups.lookup_code%type;
669 --
670 unexpected_program_error EXCEPTION;
671 --
672 /* Bug2980051. Replaced l_only_one_lb with l_no_batch_or_lb */
673 CURSOR matching_numbers IS
674 select
675 rowid, customer_id,
676 receipt_date,
677 invoice1, matching1_date, invoice1_installment,
678 invoice2, matching2_date, invoice2_installment,
679 invoice3, matching3_date, invoice3_installment,
680 invoice4, matching4_date, invoice4_installment,
681 invoice5, matching5_date, invoice5_installment,
682 invoice6, matching6_date, invoice6_installment,
683 invoice7, matching7_date, invoice7_installment,
684 invoice8, matching8_date, invoice8_installment
685 from ar_payments_interface pi
686 where pi.transmission_id = l_transmission_id
687 and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
688 and pi.customer_id is NOT null
689 and pi.status in ('AR_PLB_CUST_OK', 'AR_PLB_MICR_OK')
690 and pi.item_number = l_item_num
691 and ( pi.batch_name = l_batch_name
692 or
693 ( pi.lockbox_number = l_lockbox_number
694 and
695 l_batches = 'N'
696 )
697 or
698 l_no_batch_or_lb = 'Y'
699 );
700 --
701 BEGIN
702 debug1('arp_process_lockbox.populate_resolved_columns()+');
703 --
704 -- Assign variables to local values.
705 --
706 l_transmission_id := p_transmission_id;
707 l_payment_rec_type := p_payment_rec_type;
708 l_overflow_rec_type := p_overflow_rec_type;
709 l_item_num := p_item_num;
710 l_batch_name := p_batch_name;
711 l_lockbox_number := p_lockbox_number;
712 l_batches := p_batches;
713 l_only_one_lb := p_only_one_lb;
714 l_pay_unrelated_invoices := p_pay_unrelated_invoices;
715 l_lockbox_matching_option := p_lockbox_matching_option;
716 l_use_matching_date := p_use_matching_date;
717
718 /* Bugfix 2980051 */
719 IF l_batches = 'N' AND l_only_one_lb = 'Y' THEN
720 l_no_batch_or_lb := 'Y';
721 ELSE
722 l_no_batch_or_lb := 'N';
723 END IF;
724 --
725 debug1('Input parameters to the package...');
726 debug1('l_transmission_id ' || l_transmission_id);
727 debug1('l_payment_rec_type ' || l_payment_rec_type);
728 debug1('l_overflow_rec_type ' || l_overflow_rec_type);
729 debug1('l_item_num ' || to_char(l_item_num));
730 debug1('l_batch_name ' || l_batch_name);
731 debug1('l_lockbox_number ' || l_lockbox_number);
732 debug1('l_batches ' || l_batches);
733 debug1('l_only_one_lb ' || l_only_one_lb);
734 debug1('l_pay_unrelated_invoices ' || l_pay_unrelated_invoices);
735 debug1('l_lockbox_matching_option ' || l_lockbox_matching_option);
736 debug1('l_use_matching_date ' || l_use_matching_date);
737 debug1('l_no_batch_or_lb ' || l_no_batch_or_lb); --bug2980051
738 --
739 --
740 /* Logic with l_matching_option is as follows:
741 Let's say there are four overflow records with a receipt record.
742 If the invoice1 mentioned on 1st record matches to a purchase order
743 number, the program should expect other numbers on that receipt also
744 as purchase order number and need not match them with Invoice and/or
745 Sales order number.
746
747 When there is a new receipt record (i.e., new item number)
748 it should start matching with All. Therefore initilising
749 l_matching_option as 'ALL' everytime there is a new item number.
750
751 find_cust_and_trx_num will return p_in_out_matching_option, once it finds
752 a match. Next record in loop onwards till the end of loop,
753 find_cust_and_trx_num will match only using that l_matching_option.
754 */
755 --
756 l_matching_option := 'ALL';
757 --
758 --
759 OPEN matching_numbers;
760 debug1('Opened cursor matching_numbers.');
761 --
762 LOOP
763 FETCH matching_numbers INTO
764 l_rowid, l_customer_id,
765 l_receipt_date,
766 l_matching_number1, l_matching1_date, l_matching1_installment,
767 l_matching_number2, l_matching2_date, l_matching2_installment,
768 l_matching_number3, l_matching3_date, l_matching3_installment,
769 l_matching_number4, l_matching4_date, l_matching4_installment,
770 l_matching_number5, l_matching5_date, l_matching5_installment,
771 l_matching_number6, l_matching6_date, l_matching6_installment,
772 l_matching_number7, l_matching7_date, l_matching7_installment,
773 l_matching_number8, l_matching8_date, l_matching8_installment;
774 EXIT WHEN matching_numbers%NOTFOUND;
775 --
776 debug1('Fetched data for cursor matching_numbers.');
777 debug1('l_matching_number1 ' || l_matching_number1);
778 debug1('l_matching1_installment ' || l_matching1_installment);
779 debug1('l_matching1_date ' || to_char(l_matching1_date));
780 debug1('l_customer_id ' || to_char(l_customer_id));
781 --
782 find_cust_and_trx_num(
783 p_transmission_id=>l_transmission_id,
784 p_payment_rec_type=>l_payment_rec_type,
785 p_overflow_rec_type=>l_overflow_rec_type,
786 p_item_num=>l_item_num,
787 p_batch_name=>l_batch_name,
788 p_lockbox_number=>l_lockbox_number,
789 p_batches=>l_batches,
790 p_receipt_date=>l_receipt_date,
791 p_only_one_lb=>l_only_one_lb,
792 p_use_matching_date=>l_use_matching_date,
793 p_lockbox_matching_option=>l_lockbox_matching_option,
794 p_pay_unrelated_invoices=>l_pay_unrelated_invoices,
795 p_matching_number1=>l_matching_number1,
796 p_matching1_date=>l_matching1_date,
797 p_matching1_installment=>l_matching1_installment,
798 p_matching_number2=>l_matching_number2,
799 p_matching2_date=>l_matching2_date,
800 p_matching2_installment=>l_matching2_installment,
801 p_matching_number3=>l_matching_number3,
802 p_matching3_date=>l_matching3_date,
803 p_matching3_installment=>l_matching3_installment,
804 p_matching_number4=>l_matching_number4,
805 p_matching4_date=>l_matching4_date,
806 p_matching4_installment=>l_matching4_installment,
807 p_matching_number5=>l_matching_number5,
808 p_matching5_date=>l_matching5_date,
809 p_matching5_installment=>l_matching5_installment,
810 p_matching_number6=>l_matching_number6,
811 p_matching6_date=>l_matching6_date,
812 p_matching6_installment=>l_matching6_installment,
813 p_matching_number7=>l_matching_number7,
814 p_matching7_date=>l_matching7_date,
815 p_matching7_installment=>l_matching7_installment,
816 p_matching_number8=>l_matching_number8,
817 p_matching8_date=>l_matching8_date,
818 p_matching8_installment=>l_matching8_installment,
819 p_matched_flag=>l_match_flag,
820 p_customer_id=>l_customer_id,
821 p_matching_option=>l_matching_option,
822 p_match1_status=>l_match1_status,
823 p_match2_status=>l_match2_status,
824 p_match3_status=>l_match3_status,
825 p_match4_status=>l_match4_status,
826 p_match5_status=>l_match5_status,
827 p_match6_status=>l_match6_status,
828 p_match7_status=>l_match7_status,
829 p_match8_status=>l_match8_status
830 );
831 --
832 IF (l_match_flag = 'PROGRAM_ERROR') THEN
833 raise unexpected_program_error;
834 END IF;
835 --
836 -- If there were all invalid items on record, find_cust_and_trx_num
837 -- would have returned error in the l_matchX_status. So, that record
838 -- will error out NOCOPY (or that amount will go UNAPP) in arlvin. In this
839 -- case l_match_flag would be no_match.
840 --
841 -- If there were some matching numbers not matching, l_match_flag
842 -- would be TRUE and only those matching_numbers will have corresponding
843 -- l_matchX_status as error.
844 --
845 -- If all matching_numbers were fine then we are updating the
846 -- resolved columns correctly.
847 --
848 -- We donot want to update the record, in case there were no matching_numbers
849 -- in the record.
850 --
851 IF (l_match_flag <> 'NO_ITEM') THEN
852 --
853 debug1('Updating ar_payments_interface for resolved columns...');
854 debug1('l_matching_number1 ' || l_matching_number1);
855 debug1('l_matching1_installment ' || l_matching1_installment);
856 debug1('l_matching1_date ' || to_char(l_matching1_date));
857 --
858 UPDATE ar_payments_interface
859 SET resolved_matching_number1 = SUBSTRB(l_matching_number1, 1, 20),
860 resolved_matching1_installment = l_matching1_installment,
861 resolved_matching1_date = l_matching1_date,
862 invoice1_status = l_match1_status,
863 resolved_matching_number2 = SUBSTRB(l_matching_number2, 1, 20),
864 resolved_matching2_installment = l_matching2_installment,
865 resolved_matching2_date = l_matching2_date,
866 invoice2_status = l_match2_status,
867 resolved_matching_number3 = SUBSTRB(l_matching_number3, 1, 20),
868 resolved_matching3_installment = l_matching3_installment,
869 resolved_matching3_date = l_matching3_date,
870 invoice3_status = l_match3_status,
871 resolved_matching_number4 = SUBSTRB(l_matching_number4, 1, 20),
872 resolved_matching4_installment = l_matching4_installment,
873 resolved_matching4_date = l_matching4_date,
874 invoice4_status = l_match4_status,
875 resolved_matching_number5 = SUBSTRB(l_matching_number5, 1, 20),
876 resolved_matching5_installment = l_matching5_installment,
877 resolved_matching5_date = l_matching5_date,
878 invoice5_status = l_match5_status,
879 resolved_matching_number6 = SUBSTRB(l_matching_number6, 1, 20),
880 resolved_matching6_installment = l_matching6_installment,
881 resolved_matching6_date = l_matching6_date,
882 invoice6_status = l_match6_status,
883 resolved_matching_number7 = SUBSTRB(l_matching_number7, 1, 20),
884 resolved_matching7_installment = l_matching7_installment,
885 resolved_matching7_date = l_matching7_date,
886 invoice7_status = l_match7_status,
887 resolved_matching_number8 = SUBSTRB(l_matching_number8, 1, 20),
888 resolved_matching8_installment = l_matching8_installment,
889 resolved_matching8_date = l_matching8_date,
890 invoice8_status = l_match8_status,
891 match_resolved_using = l_matching_option
892 WHERE rowid = l_rowid;
893 --
894 -- Using rowid here for getting the current record, as ar_payments_interface
895 -- does not have any other primary key.
896 --
897 /*5052049*/
898 IF l_matching_option='CONSOLIDATE_BILL' then
899 UPDATE AR_PAYMENTS_INTERFACE SET tmp_amt_applied1=amount_applied1,
900 tmp_amt_applied2=amount_applied2,
901 tmp_amt_applied3=amount_applied3,
902 tmp_amt_applied4=amount_applied4,
903 tmp_amt_applied5=amount_applied5,
904 tmp_amt_applied6=amount_applied6,
905 tmp_amt_applied7=amount_applied7,
906 tmp_amt_applied8=amount_applied8,
907 amount_applied1 = null,
908 amount_applied2= null,
909 amount_applied3= null,
910 amount_applied4= null,
911 amount_applied5= null,
912 amount_applied6= null,
913 amount_applied7= null,
914 amount_applied8= null
915 WHERE rowid = l_rowid and match_resolved_using='CONSOLIDATE_BILL';
916 END IF;
917 END IF;
918 --
919 --
920 END LOOP; -- End loop for matching_numbers cursor.
921 CLOSE matching_numbers;
922 --
923 -- Check if there was any record that had matching numbers
924 -- and we could identify the customer from that.
925 --
926 -- If the program reached here, means all records were successfully
927 -- identified to a single customer.
928 --
929 debug1('arp_process_lockbox.populate_resolved_columns(1)-');
930 RETURN;
931 --
932 --
933 EXCEPTION
934 -- WHEN no_data_found
935 -- THEN null;
936 WHEN others
937 THEN RAISE;
938 debug1('arp_process_lockbox.populate_resolved_columns(2)-');
939 RETURN;
940 END populate_resolved_columns;
941 --
942 --
943 /*----------------------------------------------------------------------------
944 | Procedure
945 | find_cust_and_trx_num
946 |
947 | Logic:
948 | Check, if there is no matching_number given out NOCOPY of given 8 invoices,
949 | return NO_ITEM.
950 | LOOP
951 | Fetch available matching_options using matching_options cursor.
952 | Call get_cursor_name to get the parsed cursor for that matching_option.
953 | Initialise the variables to NULL/-9999 as required.
954 | For 1..8
955 | Assign values of matching_number, date and installment to *current* variables.
956 | If the current_matching_number is NOT NULL then
957 | Bind the variables. - Required for dynamic SQL.
958 | define_columns - Required for dynamic SQL.
959 | execute_and_fetch - Required for dynamic SQL
960 | column_value - Required for dynamic SQL
961 | Assign values back to matching_number, date and installment.
962 | Else current_customer_idX = -9999.
963 | End Loop.
964 | If p_customer_id is not null /o procedure called from populate_resolved_columns o/
965 | then
966 | return correct status for each of the 8 matching_numbers and invoice numbers
967 | also return p_matching_option, as you need to match only on that next time.
968 | return match_flag as TRUE.
969 | else /o procedure was called from auto_associate o/
970 | if any customer_id was -7777, return match_flag as FALSE.
971 | /o more than one customers associated with given matching number. o/
972 | Compare all customer_id and then return error in case all of them are not same.
973 | return correct status for each of the 8 matching_numbers and invoice numbers
974 | also return p_matching_option, as you need to match only on that next time.
975 | return match_flag as TRUE.
976 | END LOOP for matching_options, close cursor matching_options.
977 | If at the end of all matching_rules, given numbers have not matched
978 | then return match_flag as NO_MATCH.
979 |
980 |
981 | Description:
982 | This procedure is called from auto_associate and populate_resolved_columns
983 | When it is called from auto_associate, it returns the customer_id.
984 | When it is called from populate_resolved_columns,
985 | it accepts the customer_id.
986 |
987 |
988 | Notes for future enhancements/customisation:
989 | If you wants to add one more matching algorithm,
990 | you should add that using this procedure.
991 |
992 | Modification History:
993 | 28-Jul-97 K Trivedi Created.
994 |
995 -----------------------------------------------------------------------------*/
996 PROCEDURE
997 find_cust_and_trx_num(
998 p_transmission_id IN VARCHAR2,
999 p_payment_rec_type IN VARCHAR2,
1000 p_overflow_rec_type IN VARCHAR2,
1001 p_item_num IN ar_payments_interface.item_number%type,
1002 p_batch_name IN ar_payments_interface.batch_name%type,
1003 p_lockbox_number IN ar_payments_interface.lockbox_number%type,
1004 p_receipt_date IN ar_payments_interface.receipt_date%type,
1005 p_batches IN VARCHAR2,
1006 p_only_one_lb IN VARCHAR2,
1007 p_use_matching_date IN ar_lockboxes.use_matching_date%type,
1008 p_lockbox_matching_option IN ar_lockboxes.lockbox_matching_option%type,
1009 p_pay_unrelated_invoices IN VARCHAR2,
1010 p_matching_number1 IN OUT NOCOPY ar_payments_interface.invoice1%type,
1011 p_matching1_date IN OUT NOCOPY ar_payments_interface.matching1_date%type,
1012 p_matching1_installment IN OUT NOCOPY ar_payments_interface.invoice1_installment%type,
1013 p_matching_number2 IN OUT NOCOPY ar_payments_interface.invoice2%type,
1014 p_matching2_date IN OUT NOCOPY ar_payments_interface.matching2_date%type,
1015 p_matching2_installment IN OUT NOCOPY ar_payments_interface.invoice2_installment%type,
1016 p_matching_number3 IN OUT NOCOPY ar_payments_interface.invoice3%type,
1017 p_matching3_date IN OUT NOCOPY ar_payments_interface.matching3_date%type,
1018 p_matching3_installment IN OUT NOCOPY ar_payments_interface.invoice3_installment%type,
1019 p_matching_number4 IN OUT NOCOPY ar_payments_interface.invoice4%type,
1020 p_matching4_date IN OUT NOCOPY ar_payments_interface.matching4_date%type,
1021 p_matching4_installment IN OUT NOCOPY ar_payments_interface.invoice4_installment%type,
1022 p_matching_number5 IN OUT NOCOPY ar_payments_interface.invoice5%type,
1023 p_matching5_date IN OUT NOCOPY ar_payments_interface.matching5_date%type,
1024 p_matching5_installment IN OUT NOCOPY ar_payments_interface.invoice5_installment%type,
1025 p_matching_number6 IN OUT NOCOPY ar_payments_interface.invoice6%type,
1026 p_matching6_date IN OUT NOCOPY ar_payments_interface.matching6_date%type,
1027 p_matching6_installment IN OUT NOCOPY ar_payments_interface.invoice6_installment%type,
1028 p_matching_number7 IN OUT NOCOPY ar_payments_interface.invoice7%type,
1029 p_matching7_date IN OUT NOCOPY ar_payments_interface.matching7_date%type,
1030 p_matching7_installment IN OUT NOCOPY ar_payments_interface.invoice7_installment%type,
1031 p_matching_number8 IN OUT NOCOPY ar_payments_interface.invoice8%type,
1032 p_matching8_date IN OUT NOCOPY ar_payments_interface.matching8_date%type,
1033 p_matching8_installment IN OUT NOCOPY ar_payments_interface.invoice8_installment%type,
1034 p_matched_flag OUT NOCOPY VARCHAR2,
1035 p_customer_id IN OUT NOCOPY NUMBER,
1036 p_matching_option IN OUT NOCOPY ar_lookups.lookup_code%type,
1037 p_match1_status OUT NOCOPY ar_payments_interface.invoice1_status%type,
1038 p_match2_status OUT NOCOPY ar_payments_interface.invoice2_status%type,
1039 p_match3_status OUT NOCOPY ar_payments_interface.invoice3_status%type,
1040 p_match4_status OUT NOCOPY ar_payments_interface.invoice4_status%type,
1041 p_match5_status OUT NOCOPY ar_payments_interface.invoice5_status%type,
1042 p_match6_status OUT NOCOPY ar_payments_interface.invoice6_status%type,
1043 p_match7_status OUT NOCOPY ar_payments_interface.invoice7_status%type,
1044 p_match8_status OUT NOCOPY ar_payments_interface.invoice8_status%type
1045 ) IS
1046 --
1047 bind_var_does_not_exist EXCEPTION;
1048 not_all_var_bound EXCEPTION;
1049 PRAGMA EXCEPTION_INIT(bind_var_does_not_exist, -01006);
1050 PRAGMA EXCEPTION_INIT(not_all_var_bound, -01008);
1051 --
1052 i integer; -- Index variable, used for looping
1053 -- thru' 1 to 8 for 8 matching number.
1054 --
1055 l_current_matching_number ar_payments_interface.invoice1%type;
1056 l_current_matching_date ar_payments_interface.matching1_date%type;
1057 l_current_invoice_number ar_payments_interface.invoice1%type;
1058 l_current_invoice_date ar_payments_interface.matching1_date%type;
1059 l_current_customer_id ar_payments_interface.customer_id%type;
1060 l_current_customer_id1 ar_payments_interface.customer_id%type;
1061 l_current_customer_id2 ar_payments_interface.customer_id%type;
1062 l_current_customer_id3 ar_payments_interface.customer_id%type;
1063 l_current_customer_id4 ar_payments_interface.customer_id%type;
1064 l_current_customer_id5 ar_payments_interface.customer_id%type;
1065 l_current_customer_id6 ar_payments_interface.customer_id%type;
1066 l_current_customer_id7 ar_payments_interface.customer_id%type;
1067 l_current_customer_id8 ar_payments_interface.customer_id%type;
1068 l_current_installment ar_payments_interface.invoice1_installment%type;
1069 first_customer ar_payments_interface.customer_id%type;
1070 l_matching_option ar_lookups.lookup_code%type;
1071 l_cursor_name INTEGER;
1072 l_cursor_found BOOLEAN;
1073 --
1074 l_matching_number1 ar_payments_interface.invoice1%type;
1075 l_matching1_date ar_payments_interface.matching1_date%type;
1076 l_matching_number2 ar_payments_interface.invoice2%type;
1077 l_matching2_date ar_payments_interface.matching2_date%type;
1078 l_matching_number3 ar_payments_interface.invoice3%type;
1079 l_matching3_date ar_payments_interface.matching3_date%type;
1080 l_matching_number4 ar_payments_interface.invoice4%type;
1081 l_matching4_date ar_payments_interface.matching4_date%type;
1082 l_matching_number5 ar_payments_interface.invoice5%type;
1083 l_matching5_date ar_payments_interface.matching5_date%type;
1084 l_matching_number6 ar_payments_interface.invoice6%type;
1085 l_matching6_date ar_payments_interface.matching6_date%type;
1086 l_matching_number7 ar_payments_interface.invoice7%type;
1087 l_matching7_date ar_payments_interface.matching7_date%type;
1088 l_matching_number8 ar_payments_interface.invoice8%type;
1089 l_matching8_date ar_payments_interface.matching8_date%type;
1090 --
1091 r_invoice_number1 ar_payments_interface.invoice1%type;
1092 r_invoice_number2 ar_payments_interface.invoice2%type;
1093 r_invoice_number3 ar_payments_interface.invoice3%type;
1094 r_invoice_number4 ar_payments_interface.invoice4%type;
1095 r_invoice_number5 ar_payments_interface.invoice5%type;
1096 r_invoice_number6 ar_payments_interface.invoice6%type;
1097 r_invoice_number7 ar_payments_interface.invoice7%type;
1098 r_invoice_number8 ar_payments_interface.invoice8%type;
1099 --
1100 r_invoice1_date ar_payments_interface.matching1_date%type;
1101 r_invoice2_date ar_payments_interface.matching2_date%type;
1102 r_invoice3_date ar_payments_interface.matching3_date%type;
1103 r_invoice4_date ar_payments_interface.matching4_date%type;
1104 r_invoice5_date ar_payments_interface.matching5_date%type;
1105 r_invoice6_date ar_payments_interface.matching6_date%type;
1106 r_invoice7_date ar_payments_interface.matching7_date%type;
1107 r_invoice8_date ar_payments_interface.matching8_date%type;
1108 --
1109 r_current_invoice_number ar_payments_interface.invoice1%type;
1110 r_current_invoice_date ar_payments_interface.matching1_date%type;
1111 r_temp_int INTEGER;
1112 --
1113 CURSOR matching_options IS
1114 select LOOKUP_CODE
1115 from ar_lookups
1116 where LOOKUP_TYPE = 'ARLPLB_MATCHING_OPTION'
1117 and LOOKUP_CODE = decode(p_matching_option, 'ALL', LOOKUP_CODE, p_matching_option)
1118 order by decode(LOOKUP_CODE, 'INVOICE', 1,
1119 'SALES_ORDER', 2,
1120 'PURCHASE_ORDER', 3,
1121 'CONSOLIDATE_BILL', 4,
1122 100);
1123 --
1124 BEGIN
1125 debug1('arp_process_lockbox.find_cust_and_trx_num()+');
1126 --
1127 -- Note that the installment numbers are passed till
1128 -- this point, but are not used, as the current functionality
1129 -- matches are on invoice (where we need to return installment
1130 -- number as it is). For match with PO and SO, installement
1131 -- number is not currently used.
1132 -- If required, installment number can be made use of in
1133 -- any other future enhancements.
1134 --
1135 -- Assigning the parameters to Local variables.
1136 --
1137 l_matching_number1 := p_matching_number1;
1138 l_matching1_date := p_matching1_date;
1139 l_matching_number2 := p_matching_number2;
1140 l_matching2_date := p_matching2_date;
1141 l_matching_number3 := p_matching_number3;
1142 l_matching3_date := p_matching3_date;
1143 l_matching_number4 := p_matching_number4;
1144 l_matching4_date := p_matching4_date;
1145 l_matching_number5 := p_matching_number5;
1146 l_matching5_date := p_matching5_date;
1147 l_matching_number6 := p_matching_number6;
1148 l_matching6_date := p_matching6_date;
1149 l_matching_number7 := p_matching_number7;
1150 l_matching7_date := p_matching7_date;
1151 l_matching_number8 := p_matching_number8;
1152 l_matching8_date := p_matching8_date;
1153 --
1154 --
1155 -- Program should proceed, only if there was atleast one matching number
1156 -- being passed. In that case, we will return p_matched_flag := 'NO_ITEM'
1157 --
1158 IF ((p_matching_number1 is NULL) and
1159 (p_matching_number2 is NULL) and
1160 (p_matching_number3 is NULL) and
1161 (p_matching_number4 is NULL) and
1162 (p_matching_number5 is NULL) and
1163 (p_matching_number6 is NULL) and
1164 (p_matching_number7 is NULL) and
1165 (p_matching_number8 is NULL))
1166 THEN
1167 -- Note: Let the p_matching_option as it is, as there was no item.
1168 -- Also the p_customer_id is returned unchanged.
1169 debug1('No items were found.. returning NO_ITEM');
1170 p_matched_flag := 'NO_ITEM';
1171 debug1('arp_process_lockbox.find_cust_and_trx_num(1)-');
1172 RETURN;
1173 END IF;
1174 --
1175 OPEN matching_options;
1176 debug1('Opened cursor matching_options with p_matching_option ' || p_matching_option );
1177 --
1178 <<matching_options_loop>>
1179 LOOP
1180 FETCH matching_options
1181 INTO l_matching_option;
1182 debug1('Fetched cursor matching_options found l_matching_option = ' || l_matching_option );
1183 --
1184 EXIT matching_options_loop WHEN matching_options%NOTFOUND;
1185 --
1186 arp_process_lockbox.get_cursor_name(p_matching_option=>l_matching_option,
1187 p_cursor_name=>l_cursor_name,
1188 p_match_successful=>l_cursor_found);
1189 IF l_cursor_found = FALSE THEN
1190 -- This condition will never arise...
1191 p_matched_flag := 'PROGRAM_ERROR';
1192 debug1('PROGRAM_ERROR');
1193 debug1('arp_process_lockbox.find_cust_and_trx_num(6)-');
1194 END IF;
1195 --
1196 debug1('Got cursor ' || to_char(l_cursor_name) ||
1197 ' for matching option ' || l_matching_option);
1198 --
1199 first_customer := -9999;
1200 --
1201 -- Call a procedure to Create and Parse the cursor for :p_matching_option
1202 --
1203 -- Initilise the variables to NULL before starting loop again everytime.
1204 r_invoice_number1 := NULL;
1205 r_invoice_number2 := NULL;
1206 r_invoice_number3 := NULL;
1207 r_invoice_number4 := NULL;
1208 r_invoice_number5 := NULL;
1209 r_invoice_number6 := NULL;
1210 r_invoice_number7 := NULL;
1211 r_invoice_number8 := NULL;
1212 --
1213 r_invoice1_date := NULL;
1214 r_invoice2_date := NULL;
1215 r_invoice3_date := NULL;
1216 r_invoice4_date := NULL;
1217 r_invoice5_date := NULL;
1218 r_invoice6_date := NULL;
1219 r_invoice7_date := NULL;
1220 r_invoice8_date := NULL;
1221 --
1222 l_current_customer_id1 := -9999;
1223 l_current_customer_id2 := -9999;
1224 l_current_customer_id3 := -9999;
1225 l_current_customer_id4 := -9999;
1226 l_current_customer_id5 := -9999;
1227 l_current_customer_id6 := -9999;
1228 l_current_customer_id7 := -9999;
1229 l_current_customer_id8 := -9999;
1230 --
1231 -- Loop for getting the invoice number, invoice date and
1232 -- customer_id (only if called from auto_associate) for each matching number.
1233 --
1234 FOR i in 1 .. 8
1235 LOOP
1236 --
1237 IF (i = 1) THEN
1238 l_current_matching_number := p_matching_number1;
1239 l_current_matching_date := p_matching1_date;
1240 l_current_installment := p_matching1_installment;
1241 ELSIF (i = 2) THEN
1242 l_current_matching_number := p_matching_number2;
1243 l_current_matching_date := p_matching2_date;
1244 l_current_installment := p_matching2_installment;
1245 ELSIF (i = 3) THEN
1246 l_current_matching_number := p_matching_number3;
1247 l_current_matching_date := p_matching3_date;
1248 l_current_installment := p_matching3_installment;
1249 ELSIF (i = 4) THEN
1250 l_current_matching_number := p_matching_number4;
1251 l_current_matching_date := p_matching4_date;
1252 l_current_installment := p_matching4_installment;
1253 ELSIF (i = 5) THEN
1254 l_current_matching_number := p_matching_number5;
1255 l_current_matching_date := p_matching5_date;
1256 l_current_installment := p_matching5_installment;
1257 ELSIF (i = 6) THEN
1258 l_current_matching_number := p_matching_number6;
1259 l_current_matching_date := p_matching6_date;
1260 l_current_installment := p_matching6_installment;
1261 ELSIF (i = 7) THEN
1262 l_current_matching_number := p_matching_number7;
1263 l_current_matching_date := p_matching7_date;
1264 l_current_installment := p_matching7_installment;
1265 ELSIF (i = 8) THEN
1266 l_current_matching_number := p_matching_number8;
1267 l_current_matching_date := p_matching8_date;
1268 l_current_installment := p_matching8_installment;
1269 END IF;
1270 --
1271 -- If the l_current_matching_number is NULL, we should not
1272 -- execute the SQL statement, as it cannot return any
1273 -- value for invoice number. So, bind and execute the
1274 -- SQL statement only for l_current_matching_number <> NULL.
1275 --
1276 IF (l_current_matching_number is NOT NULL) THEN
1277 debug1('l_current_matching_number was found ' || l_current_matching_number );
1278 debug1('for i = ' || to_char(i));
1279 -- Bind the variables p_customer_id, l_current_matching_number, l_current_matching_date
1280 -- Fetch values from the cursor
1281 -- into :l_current_customer_id, :r_current_invoice_number, :r_current_invoice_date;
1282 debug1('Now binding the variables for Dyn SQL.');
1283 begin
1284 debug1('Binding b_current_matching_date with: ' || to_char(l_current_matching_date));
1285 dbms_sql.bind_variable(l_cursor_name, ':b_current_matching_date', l_current_matching_date);
1286 exception
1287 when bind_var_does_not_exist then null;
1288 when others then raise;
1289 end;
1290 begin
1291 debug1('Binding b_current_matching_number with: ' || l_current_matching_number);
1292 dbms_sql.bind_variable(l_cursor_name, ':b_current_matching_number', l_current_matching_number);
1293 exception
1294 when bind_var_does_not_exist then null;
1295 when others then raise;
1296 end;
1297 begin
1298 debug1('Binding b_pay_unrelated_customers with: ' || p_pay_unrelated_invoices);
1299 dbms_sql.bind_variable(l_cursor_name, ':b_pay_unrelated_customers', p_pay_unrelated_invoices);
1300 exception
1301 when bind_var_does_not_exist then null;
1302 when others then raise;
1303 end;
1304 begin
1305 debug1('Binding b_customer_id with: ' || to_char(p_customer_id));
1306 dbms_sql.bind_variable(l_cursor_name, ':b_customer_id', p_customer_id);
1307 exception
1308 when bind_var_does_not_exist then null;
1309 when others then raise;
1310 end;
1311 begin
1312 debug1('Binding b_receipt_date with: ' || to_char(p_receipt_date,'YYYYMMDD'));
1313 dbms_sql.bind_variable(l_cursor_name, ':b_receipt_date', to_char(p_receipt_date,'YYYYMMDD'));
1314 exception
1315 when bind_var_does_not_exist then null;
1316 when others then raise;
1317 end;
1318 begin
1319 debug1('Binding b_current_installment with: ' || to_char(l_current_installment));
1320 dbms_sql.bind_variable(l_cursor_name, ':b_current_installment', l_current_installment);
1321 exception
1322 when bind_var_does_not_exist then null;
1323 when others then raise;
1324 end;
1325 begin
1326 debug1('Binding b_lockbox_matching_option with: ' || p_lockbox_matching_option);
1327 dbms_sql.bind_variable(l_cursor_name, ':b_lockbox_matching_option', p_lockbox_matching_option);
1328 exception
1329 when bind_var_does_not_exist then null;
1330 when others then raise;
1331 end;
1332 begin
1333 debug1('Binding b_use_matching_date with: ' || p_use_matching_date);
1334 dbms_sql.bind_variable(l_cursor_name, ':b_use_matching_date', p_use_matching_date);
1335 exception
1336 when bind_var_does_not_exist then null;
1337 when others then raise;
1338 end;
1339 -- Define the columns to fetch the values INTO appropriate variables.
1340 debug1('Now Defining columns for Dyn SQL.');
1341 dbms_sql.define_column(l_cursor_name, 1, l_current_customer_id);
1342 dbms_sql.define_column(l_cursor_name, 2, r_current_invoice_number, 50);
1343 dbms_sql.define_column(l_cursor_name, 3, r_current_invoice_date);
1344 -- Fetch and execute the record.
1345 -- At this point we expect the query to return only one record,
1346 -- so it should raise error exception in case it fetches more than one
1347 -- record.
1348 --
1349 debug1('Now executing and fetching data from Dyn SQL..');
1350 begin
1351 r_temp_int := dbms_sql.execute_and_fetch(l_cursor_name, TRUE);
1352 /** r_temp_int := dbms_sql.execute(l_cursor_name);
1353 debug1('After exeucute .. ');
1354 r_temp_int := dbms_sql.fetch_rows(l_cursor_name);
1355 **/
1356 debug1('After fetch .. ');
1357 exception
1358 when not_all_var_bound then
1359 debug1('Some bind variables are not assigned value');
1360 raise;
1361 when others then
1362 debug1('exe and fetch :' || SQLERRM(SQLCODE));
1363 raise;
1364 end;
1365 --
1366 debug1('Now putting data in columns..Dyn SQL..');
1367 dbms_sql.column_value(l_cursor_name, 1, l_current_customer_id);
1368 dbms_sql.column_value(l_cursor_name, 2, r_current_invoice_number);
1369 dbms_sql.column_value(l_cursor_name, 3, r_current_invoice_date);
1370 debug1('l_current_customer_id is ' || to_char(l_current_customer_id));
1371 debug1('r_current_invoice_number is ' || r_current_invoice_number);
1372 --
1373 IF (i = 1) THEN
1374 r_invoice_number1 := r_current_invoice_number;
1375 r_invoice1_date := r_current_invoice_date;
1376 /* Bug 2651127: setting a new cust id to distinquish between
1377 not a match and not given */
1378 if ( l_current_customer_id = -9999) THEN
1379 /* we have not found a unique match */
1380 l_current_customer_id1 := -6666;
1381 else
1382 l_current_customer_id1 := l_current_customer_id;
1383 end if;
1384 ELSIF (i = 2) THEN
1385 r_invoice_number2 := r_current_invoice_number;
1386 r_invoice2_date := r_current_invoice_date;
1387 /* Bug 2651127: setting a new cust id to distinquish between
1388 not a match and not given */
1389 if ( l_current_customer_id = -9999) THEN
1390 /* we have not found a unique match */
1391 l_current_customer_id2 := -6666;
1392 else
1393 l_current_customer_id2 := l_current_customer_id;
1394 end if;
1395 ELSIF (i = 3) THEN
1396 r_invoice_number3 := r_current_invoice_number;
1397 r_invoice3_date := r_current_invoice_date;
1398 /* Bug 2651127: setting a new cust id to distinquish between
1399 not a match and not given */
1400 if ( l_current_customer_id = -9999) THEN
1401 /* we have not found a unique match */
1402 l_current_customer_id3 := -6666;
1403 else
1404 l_current_customer_id3 := l_current_customer_id;
1405 end if;
1406 ELSIF (i = 4) THEN
1407 r_invoice_number4 := r_current_invoice_number;
1408 r_invoice4_date := r_current_invoice_date;
1409 /* Bug 2651127: setting a new cust id to distinquish between
1410 not a match and not given */
1411 if ( l_current_customer_id = -9999) THEN
1412 /* we have not found a unique match */
1413 l_current_customer_id4 := -6666;
1414 else
1415 l_current_customer_id4 := l_current_customer_id;
1416 end if;
1417 ELSIF (i = 5) THEN
1418 r_invoice_number5 := r_current_invoice_number;
1419 r_invoice5_date := r_current_invoice_date;
1420 /* Bug 2651127: setting a new cust id to distinquish between
1421 not a match and not given */
1422 if ( l_current_customer_id = -9999) THEN
1423 /* we have not found a unique match */
1424 l_current_customer_id5 := -6666;
1425 else
1426 l_current_customer_id5 := l_current_customer_id;
1427 end if;
1428 ELSIF (i = 6) THEN
1429 r_invoice_number6 := r_current_invoice_number;
1430 r_invoice6_date := r_current_invoice_date;
1431 /* Bug 2651127: setting a new cust id to distinquish between
1432 not a match and not given */
1433 if ( l_current_customer_id = -9999) THEN
1434 /* we have not found a unique match */
1435 l_current_customer_id6 := -6666;
1436 else
1437 l_current_customer_id6 := l_current_customer_id;
1438 end if;
1439 ELSIF (i = 7) THEN
1440 r_invoice_number7 := r_current_invoice_number;
1441 r_invoice7_date := r_current_invoice_date;
1442 /* Bug 2651127: setting a new cust id to distinquish between
1443 not a match and not given */
1444 if ( l_current_customer_id = -9999) THEN
1445 /* we have not found a unique match */
1446 l_current_customer_id7 := -6666;
1447 else
1448 l_current_customer_id7 := l_current_customer_id;
1449 end if;
1450 ELSIF (i = 8) THEN
1451 r_invoice_number8 := r_current_invoice_number;
1452 r_invoice8_date := r_current_invoice_date;
1453 /* Bug 2651127: setting a new cust id to distinquish between
1454 not a match and not given */
1455 if ( l_current_customer_id = -9999) THEN
1456 /* we have not found a unique match */
1457 l_current_customer_id8 := -6666;
1458 else
1459 l_current_customer_id8 := l_current_customer_id;
1460 end if;
1461 END IF;
1462 --
1463 -- No need to close the cursor here.
1464 -- cursors will be closed thru' close_cursors called from C program
1465 -- which will check, if the cursors are open. It will close them
1466 -- if they are open.
1467 ELSE -- If the l_current_matching_number is null.
1468 debug1('l_current_matching_number is null for i =' || to_char(i));
1469 IF (i = 1) THEN
1470 l_current_customer_id1 := -9999;
1471 ELSIF (i = 2) THEN
1472 l_current_customer_id2 := -9999;
1473 ELSIF (i = 3) THEN
1474 l_current_customer_id3 := -9999;
1475 ELSIF (i = 4) THEN
1476 l_current_customer_id4 := -9999;
1477 ELSIF (i = 5) THEN
1478 l_current_customer_id5 := -9999;
1479 ELSIF (i = 6) THEN
1480 l_current_customer_id6 := -9999;
1481 ELSIF (i = 7) THEN
1482 l_current_customer_id7 := -9999;
1483 ELSIF (i = 8) THEN
1484 l_current_customer_id8 := -9999;
1485 END IF;
1486 END IF; -- End if for l_current_matching_number is NOT NULL
1487 --
1488 --
1489 END LOOP; -- End loop for 1 to 8 matching numbers.
1490 --
1491 --
1492 /*5052049*/
1493 IF (p_customer_id is NOT NULL AND l_matching_option <> 'CONSOLIDATE_BILL') THEN
1494 -- Procedure was called from populate_resolved_columns
1495 -- and not from Auto_Associate.
1496 debug1('Procedure was called from populate_resolved_columns.');
1497 IF
1498 ((r_invoice_number1 IS NOT NULL) or
1499 (r_invoice_number2 IS NOT NULL) or
1500 (r_invoice_number3 IS NOT NULL) or
1501 (r_invoice_number4 IS NOT NULL) or
1502 (r_invoice_number5 IS NOT NULL) or
1503 (r_invoice_number6 IS NOT NULL) or
1504 (r_invoice_number7 IS NOT NULL) or
1505 (r_invoice_number8 IS NOT NULL)) THEN
1506 --
1507 -- Program found atleast one matching number out NOCOPY of
1508 -- all eight that matched with Invoice/SO/PO.
1509 -- Rest all txn_numbers and txn_dates on this record are selected now.
1510 -- populate_resolved_columns expects only txn details
1511 -- from this routine.
1512 --
1513 -- If input value p_matching_number1 was not null
1514 -- and result r_invoice_number1 is NULL, it means that
1515 -- the program could not resolve the matching number.
1516 -- Return Error for such matching number.
1517 --
1518 -- If the resolved matching number is -1111, it means that the invoice
1519 -- is closed. So flag the invoice as 'Invalid Match'. Bug 7431540.
1520 --
1521 IF ((r_invoice_number1 IS NULL OR r_invoice_number1 = '-1111') AND
1522 (p_matching_number1 IS NOT NULL))
1523 THEN
1524 --
1525 -- In this error condition, we need to return the same
1526 -- values for p_matching_number1, p_matching1_date,
1527 -- p_matching1_installment, if called from populate_resolved_columns.
1528 --
1529 p_match1_status := 'AR_PLB_INVALID_MATCH';
1530 -- p_matching_number1 := NULL;
1531 -- p_matching1_date := NULL;
1532 -- p_matching1_installment := NULL;
1533 ELSE
1534 -- This covers three possiblities.
1535 -- 1. p_matching_number1 was null and r_invoice_number1 was also null.
1536 -- program would have never opened, fetched and executed the cursor.
1537 -- So, r_invoice_number1 and r_invoice1_date will also be null in that case.
1538 -- Return nothing for no input.
1539 -- 2. p_matching_number1 was not null and r_invoice_number1 was also not null.
1540 -- We are returning the correct return values.
1541 -- 3. p_matching_number1 was null and r_invoice_number1 was not null.
1542 -- We don't expect the values in r_invoice_number1 in this case for the
1543 -- reasons mentioned in 1.
1544 --
1545 p_match1_status := NULL;
1546 p_matching_number1 := r_invoice_number1;
1547 p_matching1_date := r_invoice1_date;
1548 -- Return p_matching1_installment as it is.
1549 END IF;
1550 -- Repeat the same for all other seven matching numbers.
1551 --
1552 IF ((r_invoice_number2 IS NULL OR r_invoice_number2 = '-1111') AND
1553 (p_matching_number2 IS NOT NULL))
1554 THEN
1555 p_match2_status := 'AR_PLB_INVALID_MATCH';
1556 -- p_matching_number2 := NULL;
1557 -- p_matching2_date := NULL;
1558 -- p_matching2_installment := NULL;
1559 ELSE
1560 p_match2_status := NULL;
1561 p_matching_number2 := r_invoice_number2;
1562 p_matching2_date := r_invoice2_date;
1563 -- Return p_matching2_installment as it is.
1564 END IF;
1565 IF ((r_invoice_number3 IS NULL OR r_invoice_number3 = '-1111') AND
1566 (p_matching_number3 IS NOT NULL))
1567 THEN
1568 p_match3_status := 'AR_PLB_INVALID_MATCH';
1569 p_matching_number3 := NULL;
1570 p_matching3_date := NULL;
1571 p_matching3_installment := NULL;
1572 ELSE
1573 p_match3_status := NULL;
1574 p_matching_number3 := r_invoice_number3;
1575 p_matching3_date := r_invoice3_date;
1576 -- Return p_matching3_installment as it is.
1577 END IF;
1578 IF ((r_invoice_number4 IS NULL OR r_invoice_number4 = '-1111') AND
1579 (p_matching_number4 IS NOT NULL))
1580 THEN
1581 p_match4_status := 'AR_PLB_INVALID_MATCH';
1582 -- p_matching_number4 := NULL;
1583 -- p_matching4_date := NULL;
1584 -- p_matching4_installment := NULL;
1585 ELSE
1586 p_match4_status := NULL;
1587 p_matching_number4 := r_invoice_number4;
1588 p_matching4_date := r_invoice4_date;
1589 -- Return p_matching4_installment as it is.
1590 END IF;
1591 IF ((r_invoice_number5 IS NULL OR r_invoice_number5 = '-1111') AND
1592 (p_matching_number5 IS NOT NULL))
1593 THEN
1594 p_match5_status := 'AR_PLB_INVALID_MATCH';
1595 -- p_matching_number5 := NULL;
1596 -- p_matching5_date := NULL;
1597 -- p_matching5_installment := NULL;
1598 ELSE
1599 p_match5_status := NULL;
1600 p_matching_number5 := r_invoice_number5;
1601 p_matching5_date := r_invoice5_date;
1602 -- Return p_matching5_installment as it is.
1603 END IF;
1604 IF ((r_invoice_number6 IS NULL OR r_invoice_number6 = '-1111') AND
1605 (p_matching_number6 IS NOT NULL))
1606 THEN
1607 p_match6_status := 'AR_PLB_INVALID_MATCH';
1608 -- p_matching_number6 := NULL;
1609 -- p_matching6_date := NULL;
1610 -- p_matching6_installment := NULL;
1611 ELSE
1612 p_match6_status := NULL;
1613 p_matching_number6 := r_invoice_number6;
1614 p_matching6_date := r_invoice6_date;
1615 -- Return p_matching6_installment as it is.
1616 END IF;
1617 IF ((r_invoice_number7 IS NULL OR r_invoice_number7 = '-1111') AND
1618 (p_matching_number7 IS NOT NULL))
1619 THEN
1620 p_match7_status := 'AR_PLB_INVALID_MATCH';
1621 -- p_matching_number7 := NULL;
1622 -- p_matching7_date := NULL;
1623 -- p_matching7_installment := NULL;
1624 ELSE
1625 p_match7_status := NULL;
1626 p_matching_number7 := r_invoice_number7;
1627 p_matching7_date := r_invoice7_date;
1628 -- Return p_matching7_installment as it is.
1629 END IF;
1630 IF ((r_invoice_number8 IS NULL OR r_invoice_number8 = '-1111') AND
1631 (p_matching_number8 IS NOT NULL))
1632 THEN
1633 p_match8_status := 'AR_PLB_INVALID_MATCH';
1634 -- p_matching_number8 := NULL;
1635 -- p_matching8_date := NULL;
1636 -- p_matching8_installment := NULL;
1637 ELSE
1638 p_match8_status := NULL;
1639 p_matching_number8 := r_invoice_number8;
1640 p_matching8_date := r_invoice8_date;
1641 -- Return p_matching8_installment as it is.
1642 END IF;
1643 p_matching_option := l_matching_option;
1644 p_matched_flag := 'TRUE';
1645 debug1('arp_process_lockbox.find_cust_and_trx_num(2)-');
1646 RETURN;
1647
1648 ELSE
1649 -- None of the eight matching_numbers matched to
1650 -- current matching_opition. So try with next matching_opition.
1651 debug1('None of the eight matching_numbers matched to current matching_opition');
1652 GOTO end_of_current_matching_option;
1653 --
1654 END IF; -- End if for 8 or conditions.
1655 END IF; -- End if for Procedure was called from populate_resolved_columns
1656 --
1657 --
1658 debug1('Procedure was called from auto_associate.');
1659 --
1660 -- If any of the customer_id is -7777, that means that
1661 -- the invoice number and invoice date were such that there were multiple
1662 -- customers associated with these numbers. In other words, the
1663 -- there were two invoices asssociated with two different customers
1664 -- having the same invoice numbers and same invoice date.
1665 -- This should read as SO/PO/Cons_Bill number and SO/PO date in case the
1666 -- matching option was SO/PO/Cons_Bill.
1667 --
1668 IF (
1669 (l_current_customer_id1 = -7777) or
1670 (l_current_customer_id2 = -7777) or
1671 (l_current_customer_id3 = -7777) or
1672 (l_current_customer_id4 = -7777) or
1673 (l_current_customer_id5 = -7777) or
1674 (l_current_customer_id6 = -7777) or
1675 (l_current_customer_id7 = -7777) or
1676 (l_current_customer_id8 = -7777)
1677 ) THEN
1678 p_matched_flag := 'FALSE';
1679 /* Bug 2106408. The p_customer_id should be passed as -7777 whenever
1680 there is a duplicate invoice number. */
1681 debug1('arp_process_lockbox.find_cust_and_trx_num : Duplicate Invoice');
1682 p_customer_id := -7777;
1683 debug1('arp_process_lockbox.find_cust_and_trx_num(8)-');
1684 RETURN;
1685 END IF;
1686
1687 /* Bug2651127: if any customer_id is -6666 that means that we have an invalid
1688 invoice number and the customer should not be uniquely identified */
1689 IF (
1690 (l_current_customer_id1 = -6666) or
1691 (l_current_customer_id2 = -6666) or
1692 (l_current_customer_id3 = -6666) or
1693 (l_current_customer_id4 = -6666) or
1694 (l_current_customer_id5 = -6666) or
1695 (l_current_customer_id6 = -6666) or
1696 (l_current_customer_id7 = -6666) or
1697 (l_current_customer_id8 = -6666)
1698 ) THEN
1699 p_matched_flag := 'FALSE';
1700 debug1('arp_process_lockbox.find_cust_and_trx_num : Invalid Invoice');
1701 debug1('arp_process_lockbox.find_cust_and_trx_num(8.1)-');
1702 /*RETURN;*/ /*5052049*/
1703 END IF;
1704 --
1705 /*5052049*/
1706 IF l_current_customer_id1 NOT IN (-9999,-6666) then
1707 debug1('l_current_customer_id1 = ' || to_char(l_current_customer_id1));
1708 first_customer := l_current_customer_id1;
1709 ELSIF l_current_customer_id2 NOT IN (-9999,-6666) then
1710 debug1('l_current_customer_id2 = ' || to_char(l_current_customer_id2));
1711 first_customer := l_current_customer_id2;
1712 ELSIF l_current_customer_id3 NOT IN (-9999,-6666) then
1713 first_customer := l_current_customer_id3;
1714 ELSIF l_current_customer_id4 NOT IN (-9999,-6666) then
1715 first_customer := l_current_customer_id4;
1716 ELSIF l_current_customer_id5 NOT IN (-9999,-6666) then
1717 first_customer := l_current_customer_id5;
1718 ELSIF l_current_customer_id6 NOT IN (-9999,-6666) then
1719 first_customer := l_current_customer_id6;
1720 ELSIF l_current_customer_id7 NOT IN (-9999,-6666) then
1721 first_customer := l_current_customer_id7;
1722 ELSIF l_current_customer_id8 NOT IN (-9999,-6666) then
1723 first_customer := l_current_customer_id8;
1724 END IF;
1725
1726 debug1('first_customer is '|| to_char(first_customer));
1727
1728 IF (first_customer not in (-9999,-6666)) THEN /*5052049*/
1729 IF (
1730 ((l_current_customer_id1 = first_customer) or (l_current_customer_id1 IN (-9999,-6666))) AND
1731 ((l_current_customer_id2 = first_customer) or (l_current_customer_id2 IN (-9999,-6666))) AND
1732 ((l_current_customer_id3 = first_customer) or (l_current_customer_id3 IN (-9999,-6666))) AND
1733 ((l_current_customer_id4 = first_customer) or (l_current_customer_id4 IN (-9999,-6666))) AND
1734 ((l_current_customer_id5 = first_customer) or (l_current_customer_id5 IN (-9999,-6666))) AND
1735 ((l_current_customer_id6 = first_customer) or (l_current_customer_id6 IN (-9999,-6666))) AND
1736 ((l_current_customer_id7 = first_customer) or (l_current_customer_id7 IN (-9999,-6666))) AND
1737 ((l_current_customer_id8 = first_customer) or (l_current_customer_id8 IN (-9999,-6666)))
1738 )
1739 THEN /* Identified the customer uniquely */
1740 debug1('r_invoice_number1 is ' || r_invoice_number1);
1741 debug1('p_matching_number1 is ' || p_matching_number1);
1742 --
1743 -- We have identified the customer uniquely. However, it is
1744 -- possible that we could not identify the invoice for the given
1745 -- matching number uniquely. For example, there were more than one
1746 -- invoices with same invoice number for the same customer.
1747 -- This is more likely in case of PO/SO. So, we need to check here.
1748 --
1749
1750 /* bug3252655 Changed IF condition because r_invoice_number1 is
1751 always null regardless of autoassociation result for consolidated
1752 billing invoice matching. When customer is identified by
1753 Invoice/PO/SO matching, trx_number is selected like max(trx_number).
1754 Hence, Need to check l_current_customer_id to check the auto
1755 association result.
1756 */
1757 /* bug3252655
1758 IF ((r_invoice_number1 IS NULL) AND
1759 (p_matching_number1 IS NOT NULL))
1760 */
1761 /*5052049 added -6666 along with -9999 in the if structure
1762 for all l_current_customer_id 1 to 8*/
1763 -- If the resolved matching number is -1111, it means that the invoice
1764 -- is closed. So flag the invoice as 'Invalid Match'. Bug 7431540.
1765 IF ((l_current_customer_id1 IN (-9999,-6666) OR r_invoice_number1 = '-1111') AND
1766 (p_matching_number1 IS NOT NULL))
1767 THEN
1768 debug1('Invalid Match for 1st match num');
1769 p_match1_status := 'AR_PLB_INVALID_MATCH';
1770 p_matching_number1 := NULL;
1771 p_matching1_date := NULL;
1772 p_matching1_installment := NULL;
1773 ELSE
1774 debug1('Valid Match for 1st match num');
1775 p_match1_status := NULL;
1776 p_matching_number1 := r_invoice_number1;
1777 p_matching1_date := r_invoice1_date;
1778 -- Return p_matching1_installment as it is.
1779 END IF;
1780 /* bug3252655
1781 IF ((r_invoice_number2 IS NULL) AND
1782 (p_matching_number2 IS NOT NULL))
1783 */
1784 IF ((l_current_customer_id2 IN (-9999,-6666) OR r_invoice_number2 = '-1111') AND
1785 (p_matching_number2 IS NOT NULL))
1786 THEN
1787 p_match2_status := 'AR_PLB_INVALID_MATCH';
1788 p_matching_number2 := NULL;
1789 p_matching2_date := NULL;
1790 p_matching2_installment := NULL;
1791 ELSE
1792 p_match2_status := NULL;
1793 p_matching_number2 := r_invoice_number2;
1794 p_matching2_date := r_invoice2_date;
1795 -- Return p_matching2_installment as it is.
1796 END IF;
1797 /* bug3252655
1798 IF ((r_invoice_number3 IS NULL) AND
1799 (p_matching_number3 IS NOT NULL))
1800 */
1801 IF ((l_current_customer_id3 IN (-9999,-6666) OR r_invoice_number3 = '-1111') AND
1802 (p_matching_number3 IS NOT NULL))
1803 THEN
1804 p_match3_status := 'AR_PLB_INVALID_MATCH';
1805 p_matching_number3 := NULL;
1806 p_matching3_date := NULL;
1807 p_matching3_installment := NULL;
1808 ELSE
1809 p_match3_status := NULL;
1810 p_matching_number3 := r_invoice_number3;
1811 p_matching3_date := r_invoice3_date;
1812 -- Return p_matching3_installment as it is.
1813 END IF;
1814 /* bug3252655
1815 IF ((r_invoice_number4 IS NULL) AND
1816 (p_matching_number4 IS NOT NULL))
1817 */
1818 IF ((l_current_customer_id4 IN (-9999,-6666) OR r_invoice_number4 = '-1111') AND
1819 (p_matching_number4 IS NOT NULL))
1820 THEN
1821 p_match4_status := 'AR_PLB_INVALID_MATCH';
1822 p_matching_number4 := NULL;
1823 p_matching4_date := NULL;
1824 p_matching4_installment := NULL;
1825 ELSE
1826 p_match4_status := NULL;
1827 p_matching_number4 := r_invoice_number4;
1828 p_matching4_date := r_invoice4_date;
1829 -- Return p_matching4_installment as it is.
1830 END IF;
1831 /* bug3252655
1832 IF ((r_invoice_number5 IS NULL) AND
1833 (p_matching_number5 IS NOT NULL))
1834 */
1835 IF ((l_current_customer_id5 IN (-9999,-6666) OR r_invoice_number5 = '-1111') AND
1836 (p_matching_number5 IS NOT NULL))
1837 THEN
1838 p_match5_status := 'AR_PLB_INVALID_MATCH';
1839 p_matching_number5 := NULL;
1840 p_matching5_date := NULL;
1841 p_matching5_installment := NULL;
1842 ELSE
1843 p_match5_status := NULL;
1844 p_matching_number5 := r_invoice_number5;
1845 p_matching5_date := r_invoice5_date;
1846 -- Return p_matching5_installment as it is.
1847 END IF;
1848 /* bug3252655
1849 IF ((r_invoice_number6 IS NULL) AND
1850 (p_matching_number6 IS NOT NULL))
1851 */
1852 IF ((l_current_customer_id6 IN (-9999,-6666) OR r_invoice_number6 = '-1111') AND
1853 (p_matching_number6 IS NOT NULL))
1854 THEN
1855 p_match6_status := 'AR_PLB_INVALID_MATCH';
1856 p_matching_number6 := NULL;
1857 p_matching6_date := NULL;
1858 p_matching6_installment := NULL;
1859 ELSE
1860 p_match6_status := NULL;
1861 p_matching_number6 := r_invoice_number6;
1862 p_matching6_date := r_invoice6_date;
1863 -- Return p_matching6_installment as it is.
1864 END IF;
1865 /* bug3252655
1866 IF ((r_invoice_number7 IS NULL) AND
1867 (p_matching_number7 IS NOT NULL))
1868 */
1869 IF ((l_current_customer_id7 IN (-9999,-6666) OR r_invoice_number7 = '-1111') AND
1870 (p_matching_number7 IS NOT NULL))
1871 THEN
1872 p_match7_status := 'AR_PLB_INVALID_MATCH';
1873 p_matching_number7 := NULL;
1874 p_matching7_date := NULL;
1875 p_matching7_installment := NULL;
1876 ELSE
1877 p_match7_status := NULL;
1878 p_matching_number7 := r_invoice_number7;
1879 p_matching7_date := r_invoice7_date;
1880 -- Return p_matching7_installment as it is.
1881 END IF;
1882 /* bug3252655
1883 IF ((r_invoice_number8 IS NULL) AND
1884 (p_matching_number8 IS NOT NULL))
1885 */
1886 IF ((l_current_customer_id8 IN (-9999,-6666) OR r_invoice_number8 = '-1111') AND
1887 (p_matching_number8 IS NOT NULL))
1888 THEN
1889 p_match8_status := 'AR_PLB_INVALID_MATCH';
1890 p_matching_number8 := NULL;
1891 p_matching8_date := NULL;
1892 p_matching8_installment := NULL;
1893 ELSE
1894 p_match8_status := NULL;
1895 p_matching_number8 := r_invoice_number8;
1896 p_matching8_date := r_invoice8_date;
1897 -- Return p_matching8_installment as it is.
1898 END IF;
1899 p_matching_option := l_matching_option;
1900 p_customer_id := first_customer;
1901 p_matched_flag := 'TRUE';
1902 debug1('arp_process_lockbox.find_cust_and_trx_num(3)-');
1903 RETURN;
1904 ELSE /* Could not identify the customer uniquely */
1905 /* Let the p_customer_id be whatever was the input value,
1906 It will be null, if it is called form Auto_Associate or it
1907 will hold some valid value, if called from populate_resolved_coulmns */
1908 p_matched_flag := 'FALSE';
1909 debug1('arp_process_lockbox.find_cust_and_trx_num(4)-');
1910 RETURN;
1911 END IF;
1912 END IF; -- End if for first_customer <> -9999.
1913 /* Note : Do nothing and countinue with the next option in
1914 cursor matching_options in case first_customer = -9999 */
1915 --
1916 <<end_of_current_matching_option>>
1917 null;
1918 END LOOP matching_options_loop;
1919 CLOSE matching_options;
1920 --
1921 -- If the program has reached here, it means that there were no
1922 -- matches to the input number.
1923 -- Note: Let the p_matching_option as it is, as there was no item.
1924 -- Also returning p_matching_number and p_matching_date unchanged.
1925 IF (p_matching_number1 IS NOT NULL)
1926 THEN
1927 p_match1_status := 'AR_PLB_INVALID_MATCH';
1928 ELSE
1929 p_match1_status := NULL;
1930 END IF;
1931 IF (p_matching_number2 IS NOT NULL)
1932 THEN
1933 p_match2_status := 'AR_PLB_INVALID_MATCH';
1934 ELSE
1935 p_match2_status := NULL;
1936 END IF;
1937 IF (p_matching_number3 IS NOT NULL)
1938 THEN
1939 p_match3_status := 'AR_PLB_INVALID_MATCH';
1940 ELSE
1941 p_match3_status := NULL;
1942 END IF;
1943 IF (p_matching_number4 IS NOT NULL)
1944 THEN
1945 p_match4_status := 'AR_PLB_INVALID_MATCH';
1946 ELSE
1947 p_match4_status := NULL;
1948 END IF;
1949 IF (p_matching_number5 IS NOT NULL)
1950 THEN
1951 p_match5_status := 'AR_PLB_INVALID_MATCH';
1952 ELSE
1953 p_match5_status := NULL;
1954 END IF;
1955 IF (p_matching_number6 IS NOT NULL)
1956 THEN
1957 p_match6_status := 'AR_PLB_INVALID_MATCH';
1958 ELSE
1959 p_match6_status := NULL;
1960 END IF;
1961 IF (p_matching_number7 IS NOT NULL)
1962 THEN
1963 p_match7_status := 'AR_PLB_INVALID_MATCH';
1964 ELSE
1965 p_match7_status := NULL;
1966 END IF;
1967 IF (p_matching_number8 IS NOT NULL)
1968 THEN
1969 p_match8_status := 'AR_PLB_INVALID_MATCH';
1970 ELSE
1971 p_match8_status := NULL;
1972 END IF;
1973 p_matched_flag := 'NO_MATCH';
1974 debug1('arp_process_lockbox.find_cust_and_trx_num(5)-');
1975 RETURN;
1976 --
1977 EXCEPTION
1978 WHEN others
1979 THEN
1980 debug1('arp_process_lockbox.find_cust_and_trx_num(7)-');
1981 RAISE;
1982 END find_cust_and_trx_num;
1983 --
1984 /*----------------------------------------------------------------------------
1985 This procedure calls arp_util.debug for the string passed.
1986 Till arp_util.debug is changed to provide an option to write to a
1987 file, we can use this procedure to write to a file at the time of testing.
1988 Un comment lines calling fnd_file package and that will write to a file.
1989 Please change the directory name so that it does not raise any exception.
1990 ----------------------------------------------------------------------------*/
1991 PROCEDURE debug1(str IN VARCHAR2) IS
1992 -- myfile utl_file.file_type;
1993 -- dir_name varchar2(100);
1994 -- out_file_name varchar2(8);
1995 -- log_file_name varchar2(8);
1996 BEGIN
1997 --
1998 -- Check for the directory name.
1999 -- dir_name := '/sqlcom/inbound';
2000 -- log_file_name := 'ar.log';
2001 -- out_file_name := 'ar.out';
2002 -- myfile := utl_file.fopen(dir_name, out_file_name, 'a');
2003 -- utl_file.put(myfile, str);
2004 -- utl_file.fclose(myfile);
2005 --
2006 IF PG_DEBUG in ('Y', 'C') THEN
2007 arp_util.debug(str);
2008 END IF;
2009 END;
2010 --
2011 /*----------------------------------------------------------------------------
2012 | Procedure
2013 | get_cursor_name
2014 |
2015 | Logic:
2016 | Loop thru' the table opened_cursors_table and find the
2017 | record for p_matching_option
2018 | return cursor_name when you find match.
2019 | In case you don't find a match, return Failure.
2020 |
2021 |
2022 | Modification History
2023 | 05-Aug-97 K Trivedi Created. Rel 11 related changes.
2024 |
2025 ----------------------------------------------------------------------------*/
2026 PROCEDURE
2027 get_cursor_name(p_matching_option IN ar_lookups.lookup_code%type,
2028 p_cursor_name OUT NOCOPY INTEGER,
2029 p_match_successful OUT NOCOPY BOOLEAN) IS
2030 --
2031 --
2032 BEGIN
2033 debug1('arp_process_lockbox.get_cursor_name()+');
2034 FOR i in 1 .. g_total_maching_options
2035 LOOP
2036 IF (opened_cursor_table(i).option_name = p_matching_option) THEN
2037 p_cursor_name := opened_cursor_table(i).cursor_name;
2038 p_match_successful := TRUE;
2039 debug1('arp_process_lockbox.get_cursor_name(1)-');
2040 RETURN;
2041 END IF;
2042 END LOOP; -- End loop for 1 to g_total_maching_options matching numbers.
2043 --
2044 -- If the program control has reached here, it means that requested
2045 -- cursor was not defined. .. will return error in this case.
2046 --
2047 p_cursor_name := NULL;
2048 p_match_successful := FALSE;
2049 debug1('arp_process_lockbox.get_cursor_name(2)-');
2050 RETURN;
2051 --
2052 END get_cursor_name;
2053 --
2054 /*----------------------------------------------------------------------------
2055 | Procedure
2056 | close_cursors
2057 |
2058 | Logic:
2059 | Loop from 1 to g_total_opened_cursors.
2060 | Check if the cursor is Open.
2061 | Close it if it is open.
2062 |
2063 | Modification History
2064 | 05-Aug-97 K Trivedi Created. Rel 11 related changes.
2065 |
2066 ----------------------------------------------------------------------------*/
2067 PROCEDURE
2068 close_cursors IS
2069 Begin
2070 debug1('arp_process_lockbox.get_cursor_name()+');
2071 --
2072 FOR i in 1 .. g_total_maching_options
2073 LOOP
2074 IF (dbms_sql.is_open(opened_cursor_table(i).cursor_name)) THEN
2075 debug1('Closing Cursor for index ' || to_char(i));
2076 dbms_sql.close_cursor(opened_cursor_table(i).cursor_name);
2077 END IF;
2078 END LOOP; -- End loop for 1 to g_total_maching_options matching numbers.
2079 --
2080 debug1('arp_process_lockbox.get_cursor_name()-');
2081 End close_cursors;
2082
2083 --
2084 /*===========================================================================+
2085 | PROCEDURE |
2086 | validate_llca_interface_data |
2087 | |
2088 | DESCRIPTION |
2089 | Validate interface data for LLCA |
2090 | |
2091 | SCOPE - PUBLIC |
2092 | |
2093 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2094 | |
2095 | ARGUMENTS : IN: |
2096 | p_trans_request_id - Id of transmission request whose llca |
2097 | data to be validated. |
2098 | RETURNS : NONE |
2099 | |
2100 | MODIFICATION HISTORY : |
2101 | |
2102 | 28-Jul-07 vpusulur Created. |
2103 | 24-JUN-08 aghoraka Modified the design. |
2104 | Used queries to process teh entire data. |
2105 | Refer Bug 7195038. |
2106 +===========================================================================*/
2107 PROCEDURE validate_llca_interface_data(
2108 p_trans_request_id IN varchar2,
2109 p_allow_invalid_trx_num IN varchar2,
2110 p_format_amount IN varchar2,
2111 p_return_status OUT NOCOPY varchar2
2112 ) IS
2113 CURSOR customer_trx_cursor(p_trans_request_id IN NUMBER) IS
2114 SELECT DISTINCT customer_trx_id
2115 FROM ar_pmts_interface_header_gt
2116 WHERE transmission_request_id = p_trans_request_id;
2117
2118 CURSOR apply_to_rec(p_trans_request_id IN NUMBER,
2119 p_customer_trx_id IN NUMBER) IS
2120 SELECT distinct apply_to
2121 FROM ar_pmts_interface_line_details
2122 WHERE transmission_request_id = p_trans_request_id
2123 AND customer_trx_id = p_customer_trx_id;
2124
2125 CURSOR trans_rec_cur(p_trans_request_id IN NUMBER) IS
2126 SELECT distinct transmission_record_id
2127 FROM ar_pmts_interface_line_details
2128 WHERE transmission_request_id = p_trans_request_id
2129 AND status = 'AR_PLB_NEW_RECORD';
2130
2131 CURSOR invoice_rec_cur(p_trans_request_id IN NUMBER,
2132 p_transmission_record_id IN NUMBER) IS
2133 SELECT distinct customer_trx_id
2134 FROM ar_pmts_interface_line_details
2135 WHERE transmission_request_id = p_trans_request_id
2136 AND transmission_record_id = p_transmission_record_id;
2137
2138 CURSOR app_to_cur(p_trans_req_id IN NUMBER,
2139 p_transmission_record_id IN NUMBER,
2140 p_customer_trx_id IN NUMBER) IS
2141 SELECT apply_to,
2142 line_amount,
2143 tax,
2144 amount_applied
2145 FROM ar_pmts_interface_line_details
2146 WHERE transmission_request_id = p_trans_req_id
2147 AND transmission_record_id = p_transmission_record_id
2148 AND customer_trx_id = p_customer_trx_id
2149 AND apply_to NOT IN ('FREIGHT','CHARGES')
2150 AND status = 'AR_PLB_NEW_RECORD';
2151
2152 tot_app_count NUMBER;
2153 line_app_count NUMBER;
2154 l_invoice_number ar_payments_interface.INVOICE1%TYPE;
2155 l_inv_currency_code ar_payments_interface.CURRENCY_CODE%TYPE;
2156 l_currency_code ar_payments_interface.CURRENCY_CODE%TYPE;
2157 l_trans_to_receipt_rate ar_payments_interface.TRANS_TO_RECEIPT_RATE1%TYPE;
2158 l_default_by VARCHAR2(20);
2159 l_line_amount_remaining NUMBER;
2160 l_line_tax_remaining NUMBER;
2161 line_amt_due_original NUMBER;
2162 l_customer_trx ra_customer_trx%rowtype;
2163 ll_leg_app varchar2(1);
2164 ll_mfar_app varchar2(1);
2165 ll_leg_adj varchar2(1);
2166 ll_mfar_adj varchar2(1);
2167 l_return_status varchar2(2) := 'S';
2168 p_trans_req_id number := 0;
2169 l_msg_count number;
2170 l_msg_data varchar2(50);
2171 l_tot_amt_app NUMBER;
2172 l_tot_amt_app_from NUMBER;
2173 hdr_amt_app NUMBER;
2174 hdr_amt_app_frm NUMBER;
2175 l_calc_per_line NUMBER;
2176 l_calc_tot_amount_app NUMBER;
2177 l_calc_line_amount NUMBER;
2178 l_calc_tax_amount NUMBER;
2179 l_trans_record_id ar_payments_interface.TRANSMISSION_RECORD_ID%TYPE;
2180 format_amount_app1 varchar2(2);
2181 format_amount_app2 varchar2(2);
2182 format_amount_app3 varchar2(2);
2183 format_amount_app4 varchar2(2);
2184 format_amount_app5 varchar2(2);
2185 format_amount_app6 varchar2(2);
2186 format_amount_app7 varchar2(2);
2187 format_amount_app8 varchar2(2);
2188 format_amount1 varchar2(2);
2189 format_amount2 varchar2(2);
2190 format_amount3 varchar2(2);
2191 format_amount4 varchar2(2);
2192 format_amount5 varchar2(2);
2193 format_amount6 varchar2(2);
2194 format_amount7 varchar2(2);
2195 format_amount8 varchar2(2);
2196
2197 BEGIN
2198 debug1('arp_process_lockbox.validate_llca_interface_data()+');
2199 p_trans_req_id := to_number(p_trans_request_id);
2200
2201 UPDATE ar_pmts_interface_line_details
2202 SET status = 'AR_PLB_NEW_RECORD'
2203 WHERE transmission_request_id = p_trans_req_id;
2204
2205 UPDATE ar_pmts_interface_line_details
2206 SET amount_applied = decode(apply_to,'FREIGHT',freight,charges)
2207 WHERE transmission_request_id = p_trans_req_id
2208 AND apply_to IN ('FREIGHT', 'CHARGES')
2209 AND amount_applied IS NULL
2210 AND allocated_receipt_amount IS NULL;
2211
2212 UPDATE ar_pmts_interface_line_details line_details
2213 SET status = 'AR_PLB_INVALID_REC_ID'
2214 WHERE transmission_record_id in ( SELECT transmission_record_id
2215 FROM ar_payments_interface interface
2216 WHERE interface.transmission_request_id = p_trans_req_id
2217 AND interface.transmission_record_id = line_details.transmission_record_id
2218 GROUP BY transmission_record_id
2219 HAVING count(transmission_record_id) <> 1
2220 )
2221 AND transmission_request_id = p_trans_req_id
2222 AND status = 'AR_PLB_NEW_RECORD';
2223
2224 UPDATE ar_pmts_interface_line_details line_details
2225 SET status = 'AR_PLB_INVALID_RECORD'
2226 WHERE transmission_record_id in ( SELECT transmission_record_id
2227 FROM ar_payments_interface interface
2228 WHERE interface.transmission_request_id = p_trans_req_id
2229 AND interface.transmission_record_id = line_details.transmission_record_id
2230 AND status <> 'AR_PLB_APP_OK')
2231 AND transmission_request_id = p_trans_req_id
2232 AND status = 'AR_PLB_NEW_RECORD';
2233
2234 SELECT transmission_record_id
2235 INTO l_trans_record_id
2236 FROM ar_payments_interface
2237 WHERE transmission_request_id = p_trans_req_id
2238 AND rownum = 1;
2239
2240 format_amount1 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2241 l_trans_record_id,'AMT APP 1');
2242 format_amount2 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2243 l_trans_record_id,'AMT APP 2');
2244 format_amount3 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2245 l_trans_record_id,'AMT APP 3');
2246 format_amount4 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2247 l_trans_record_id,'AMT APP 4');
2248 format_amount5 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2249 l_trans_record_id,'AMT APP 5');
2250 format_amount6 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2251 l_trans_record_id,'AMT APP 6');
2252 format_amount7 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2253 l_trans_record_id,'AMT APP 7');
2254 format_amount8 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2255 l_trans_record_id,'AMT APP 8');
2256 format_amount_app1 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2257 l_trans_record_id,'AMT APP FROM 1');
2258 format_amount_app2 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2259 l_trans_record_id,'AMT APP FROM 2');
2260 format_amount_app3 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2261 l_trans_record_id,'AMT APP FROM 3');
2262 format_amount_app4 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2263 l_trans_record_id,'AMT APP FROM 4');
2264 format_amount_app5 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2265 l_trans_record_id,'AMT APP FROM 5');
2266 format_amount_app6 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2267 l_trans_record_id,'AMT APP FROM 6');
2268 format_amount_app7 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2269 l_trans_record_id,'AMT APP FROM 7');
2270 format_amount_app8 := ARP_PROCESS_LOCKBOX.get_format_amount(p_trans_req_id,
2271 l_trans_record_id,'AMT APP FROM 8');
2272
2273 INSERT INTO ar_pmts_interface_header_gt
2274 (transmission_request_id,
2275 transmission_record_id,
2276 currency_code,
2277 invoice_number,
2278 amount_applied_from,
2279 amount_applied,
2280 trans_to_receipt_rate,
2281 invoice_currency_code,
2282 record_status )
2283 SELECT transmission_request_id,
2284 transmission_record_id,
2285 interface.currency_code,
2286 invoice1,
2287 decode(format_amount_app1, 'Y',
2288 round(amount_applied_from1/power(10,fc1.precision),fc1.precision),
2289 amount_applied_from1),
2290 decode(format_amount1, 'Y',
2291 round(amount_applied1/power(10,fc.precision),fc.precision),
2292 amount_applied1),
2293 nvl(trans_to_receipt_rate1,1),
2294 invoice_currency_code1,
2295 status
2296 FROM ar_payments_interface interface,
2297 fnd_currencies fc,
2298 fnd_currencies fc1
2299 WHERE invoice1 is NOT NULL
2300 AND transmission_request_id = p_trans_req_id
2301 AND status = 'AR_PLB_APP_OK'
2302 AND fc.currency_code = interface.currency_code
2303 AND fc1.currency_code = nvl(interface.invoice_currency_code1,
2304 interface.currency_code)
2305 AND EXISTS ( SELECT 'X'
2306 FROM ar_pmts_interface_line_details line_details
2307 WHERE line_details.transmission_record_id = interface.transmission_record_id
2308 AND line_details.invoice_number = interface.invoice1);
2309
2310 INSERT INTO ar_pmts_interface_header_gt
2311 (transmission_request_id,
2312 transmission_record_id,
2313 currency_code,
2314 invoice_number,
2315 amount_applied_from,
2316 amount_applied,
2317 trans_to_receipt_rate,
2318 invoice_currency_code,
2319 record_status )
2320 SELECT transmission_request_id,
2321 transmission_record_id,
2322 interface.currency_code,
2323 invoice2,
2324 decode(format_amount_app2, 'Y',
2325 round(amount_applied_from2/power(10,fc1.precision),fc1.precision),
2326 amount_applied_from2),
2327 decode(format_amount2, 'Y',
2328 round(amount_applied2/power(10,fc.precision),fc.precision),
2329 amount_applied2),
2330 nvl(trans_to_receipt_rate2,1),
2331 invoice_currency_code2,
2332 status
2333 FROM ar_payments_interface interface,
2334 fnd_currencies fc,
2335 fnd_currencies fc1
2336 WHERE invoice2 is NOT NULL
2337 AND transmission_request_id = p_trans_req_id
2338 AND status = 'AR_PLB_APP_OK'
2339 AND fc.currency_code = interface.currency_code
2340 AND fc1.currency_code = nvl(interface.invoice_currency_code2,
2341 interface.currency_code)
2342 AND EXISTS ( SELECT 'X'
2343 FROM ar_pmts_interface_line_details line_details
2344 WHERE line_details.transmission_record_id = interface.transmission_record_id
2345 AND line_details.invoice_number = interface.invoice2);
2346
2347 INSERT INTO ar_pmts_interface_header_gt
2348 (transmission_request_id,
2349 transmission_record_id,
2350 currency_code,
2351 invoice_number,
2352 amount_applied_from,
2353 amount_applied,
2354 trans_to_receipt_rate,
2355 invoice_currency_code,
2356 record_status )
2357 SELECT transmission_request_id,
2358 transmission_record_id,
2359 interface.currency_code,
2360 invoice3,
2361 decode(format_amount_app3, 'Y',
2362 round(amount_applied_from3/power(10,fc1.precision),fc1.precision),
2363 amount_applied_from3),
2364 decode(format_amount3, 'Y',
2365 round(amount_applied3/power(10,fc.precision),fc.precision),
2366 amount_applied3),
2367 nvl(trans_to_receipt_rate3,1),
2368 invoice_currency_code3,
2369 status
2370 FROM ar_payments_interface interface,
2371 fnd_currencies fc,
2372 fnd_currencies fc1
2373 WHERE invoice3 is NOT NULL
2374 AND transmission_request_id = p_trans_req_id
2375 AND status = 'AR_PLB_APP_OK'
2376 AND fc.currency_code = interface.currency_code
2377 AND fc1.currency_code = nvl(interface.invoice_currency_code3,
2378 interface.currency_code)
2379 AND EXISTS ( SELECT 'X'
2380 FROM ar_pmts_interface_line_details line_details
2381 WHERE line_details.transmission_record_id = interface.transmission_record_id
2382 AND line_details.invoice_number = interface.invoice3);
2383
2384 INSERT INTO ar_pmts_interface_header_gt
2385 (transmission_request_id,
2386 transmission_record_id,
2387 currency_code,
2388 invoice_number,
2389 amount_applied_from,
2390 amount_applied,
2391 trans_to_receipt_rate,
2392 invoice_currency_code,
2393 record_status )
2394 SELECT transmission_request_id,
2395 transmission_record_id,
2396 interface.currency_code,
2397 invoice4,
2398 decode(format_amount_app4, 'Y',
2399 round(amount_applied_from4/power(10,fc1.precision),fc1.precision),
2400 amount_applied_from4),
2401 decode(format_amount4, 'Y',
2402 round(amount_applied4/power(10,fc.precision),fc.precision),
2403 amount_applied4),
2404 nvl(trans_to_receipt_rate4,1),
2405 invoice_currency_code4,
2406 status
2407 FROM ar_payments_interface interface,
2408 fnd_currencies fc,
2409 fnd_currencies fc1
2410 WHERE invoice4 is NOT NULL
2411 AND transmission_request_id = p_trans_req_id
2412 AND status = 'AR_PLB_APP_OK'
2413 AND fc.currency_code = interface.currency_code
2414 AND fc1.currency_code = nvl(interface.invoice_currency_code4,
2415 interface.currency_code)
2416 AND EXISTS ( SELECT 'X'
2417 FROM ar_pmts_interface_line_details line_details
2418 WHERE line_details.transmission_record_id = interface.transmission_record_id
2419 AND line_details.invoice_number = interface.invoice4);
2420
2421 INSERT INTO ar_pmts_interface_header_gt
2422 (transmission_request_id,
2423 transmission_record_id,
2424 currency_code,
2425 invoice_number,
2426 amount_applied_from,
2427 amount_applied,
2428 trans_to_receipt_rate,
2429 invoice_currency_code,
2430 record_status )
2431 SELECT transmission_request_id,
2432 transmission_record_id,
2433 interface.currency_code,
2434 invoice5,
2435 decode(format_amount_app5, 'Y',
2436 round(amount_applied_from5/power(10,fc1.precision),fc1.precision),
2437 amount_applied_from5),
2438 decode(format_amount5, 'Y',
2439 round(amount_applied5/power(10,fc.precision),fc.precision),
2440 amount_applied5),
2441 nvl(trans_to_receipt_rate5,1),
2442 invoice_currency_code5,
2443 status
2444 FROM ar_payments_interface interface,
2445 fnd_currencies fc,
2446 fnd_currencies fc1
2447 WHERE invoice5 is NOT NULL
2448 AND transmission_request_id = p_trans_req_id
2449 AND status = 'AR_PLB_APP_OK'
2450 AND fc.currency_code = interface.currency_code
2451 AND fc1.currency_code = nvl(interface.invoice_currency_code5,
2452 interface.currency_code)
2453 AND EXISTS ( SELECT 'X'
2454 FROM ar_pmts_interface_line_details line_details
2455 WHERE line_details.transmission_record_id = interface.transmission_record_id
2456 AND line_details.invoice_number = interface.invoice5);
2457
2458 INSERT INTO ar_pmts_interface_header_gt
2459 (transmission_request_id,
2460 transmission_record_id,
2461 currency_code,
2462 invoice_number,
2463 amount_applied_from,
2464 amount_applied,
2465 trans_to_receipt_rate,
2466 invoice_currency_code,
2467 record_status )
2468 SELECT transmission_request_id,
2469 transmission_record_id,
2470 interface.currency_code,
2471 invoice6,
2472 decode(format_amount_app6, 'Y',
2473 round(amount_applied_from6/power(10,fc1.precision),fc1.precision),
2474 amount_applied_from6),
2475 decode(format_amount6, 'Y',
2476 round(amount_applied6/power(10,fc.precision),fc.precision),
2477 amount_applied6),
2478 nvl(trans_to_receipt_rate6,1),
2479 invoice_currency_code6,
2480 status
2481 FROM ar_payments_interface interface,
2482 fnd_currencies fc,
2483 fnd_currencies fc1
2484 WHERE invoice6 is NOT NULL
2485 AND transmission_request_id = p_trans_req_id
2486 AND status = 'AR_PLB_APP_OK'
2487 AND fc.currency_code = interface.currency_code
2488 AND fc1.currency_code = nvl(interface.invoice_currency_code6,
2489 interface.currency_code)
2490 AND EXISTS ( SELECT 'X'
2491 FROM ar_pmts_interface_line_details line_details
2492 WHERE line_details.transmission_record_id = interface.transmission_record_id
2493 AND line_details.invoice_number = interface.invoice6);
2494
2495 INSERT INTO ar_pmts_interface_header_gt
2496 (transmission_request_id,
2497 transmission_record_id,
2498 currency_code,
2499 invoice_number,
2500 amount_applied_from,
2501 amount_applied,
2502 trans_to_receipt_rate,
2503 invoice_currency_code,
2504 record_status )
2505 SELECT transmission_request_id,
2506 transmission_record_id,
2507 interface.currency_code,
2508 invoice7,
2509 decode(format_amount_app7, 'Y',
2510 round(amount_applied_from7/power(10,fc1.precision),fc1.precision),
2511 amount_applied_from7),
2512 decode(format_amount7, 'Y',
2513 round(amount_applied7/power(10,fc.precision),fc.precision),
2514 amount_applied7),
2515 nvl(trans_to_receipt_rate7,1),
2516 invoice_currency_code7,
2517 status
2518 FROM ar_payments_interface interface,
2519 fnd_currencies fc,
2520 fnd_currencies fc1
2521 WHERE invoice7 is NOT NULL
2522 AND transmission_request_id = p_trans_req_id
2523 AND status = 'AR_PLB_APP_OK'
2524 AND fc.currency_code = interface.currency_code
2525 AND fc1.currency_code = nvl(interface.invoice_currency_code7,
2526 interface.currency_code)
2527 AND EXISTS ( SELECT 'X'
2528 FROM ar_pmts_interface_line_details line_details
2529 WHERE line_details.transmission_record_id = interface.transmission_record_id
2530 AND line_details.invoice_number = interface.invoice7);
2531
2532 INSERT INTO ar_pmts_interface_header_gt
2533 (transmission_request_id,
2534 transmission_record_id,
2535 currency_code,
2536 invoice_number,
2537 amount_applied_from,
2538 amount_applied,
2539 trans_to_receipt_rate,
2540 invoice_currency_code,
2541 record_status )
2542 SELECT transmission_request_id,
2543 transmission_record_id,
2544 interface.currency_code,
2545 invoice8,
2546 decode(format_amount_app8, 'Y',
2547 round(amount_applied_from8/power(10,fc1.precision),fc1.precision),
2548 amount_applied_from8),
2549 decode(format_amount8, 'Y',
2550 round(amount_applied8/power(10,fc.precision),fc.precision),
2551 amount_applied8),
2552 nvl(trans_to_receipt_rate8,1),
2553 invoice_currency_code8,
2554 status
2555 FROM ar_payments_interface interface,
2556 fnd_currencies fc,
2557 fnd_currencies fc1
2558 WHERE invoice8 is NOT NULL
2559 AND transmission_request_id = p_trans_req_id
2560 AND status = 'AR_PLB_APP_OK'
2561 AND fc.currency_code = interface.currency_code
2562 AND fc1.currency_code = nvl(interface.invoice_currency_code8,
2563 interface.currency_code)
2564 AND EXISTS ( SELECT 'X'
2565 FROM ar_pmts_interface_line_details line_details
2566 WHERE line_details.transmission_record_id = interface.transmission_record_id
2567 AND line_details.invoice_number = interface.invoice8);
2568
2569 UPDATE ar_pmts_interface_line_details line_details
2570 SET status = 'AR_PLB_DUP_INVOICE'
2571 WHERE invoice_number IN ( SELECT invoice_number
2572 FROM ar_pmts_interface_header_gt
2573 WHERE transmission_record_id = line_details.transmission_record_id
2574 GROUP BY invoice_number
2575 HAVING count(invoice_number) > 1 )
2576 AND transmission_request_id = p_trans_req_id
2577 AND status = 'AR_PLB_NEW_RECORD';
2578
2579 UPDATE ar_pmts_interface_line_details line_details
2580 SET status = 'AR_PLB_DUP_FRGT_CHRG'
2581 WHERE (transmission_record_id, invoice_number, apply_to)
2582 IN (SELECT transmission_record_id, invoice_number, apply_to
2583 FROM ar_pmts_interface_line_details ld
2584 WHERE ld.transmission_record_id = line_details.transmission_record_id
2585 AND ld.invoice_number = line_details.invoice_number
2586 AND ld.apply_to = line_details.apply_to
2587 AND ld.transmission_request_id = p_trans_req_id
2588 GROUP BY transmission_record_id, invoice_number, apply_to
2589 HAVING count(*) > 1)
2590 AND transmission_request_id = p_trans_req_id
2591 AND apply_to IN ('FREIGHT', 'CHARGES')
2592 AND status = 'AR_PLB_NEW_RECORD';
2593
2594 UPDATE ar_pmts_interface_line_details
2595 SET status = 'AR_PLB_NO_APP_INFO'
2596 WHERE transmission_request_id = p_trans_req_id
2597 AND amount_applied IS NULL
2598 AND line_amount IS NULL
2599 AND allocated_receipt_amount IS NULL
2600 AND status = 'AR_PLB_NEW_RECORD';
2601
2602 /* Does we need this since we are defaulting values? */
2603 /*UPDATE ar_pmts_interface_line_details
2604 SET status = 'AR_PLB_LINE_TAX_TOT_MISMATCH'
2605 WHERE transmission_request_id = p_trans_req_id
2606 AND amount_applied IS NOT NULL
2607 AND line_amount IS NOT NULL
2608 AND tax_amount IS NOT NULL
2609 AND amount_applied <> line_amount + tax_amount
2610 AND status = 'AR_PLB_NEW_RECORD'; */
2611
2612 /* We may need to handle duplicate invoices in future, here */
2613 UPDATE ar_pmts_interface_header_gt gt
2614 SET (customer_trx_id) = ( SELECT customer_trx_id
2615 FROM ra_customer_trx
2616 WHERE trx_number = gt.invoice_number ) ;
2617
2618 UPDATE ar_pmts_interface_line_details ld
2619 SET customer_trx_id = (SELECT customer_trx_id
2620 FROM ar_pmts_interface_header_gt
2621 WHERE invoice_number = ld.invoice_number
2622 AND transmission_record_id = ld.transmission_record_id
2623 )
2624 WHERE transmission_request_id = p_trans_req_id;
2625
2626 /*UPDATE ar_pmts_interface_header_gt gt
2627 SET precision = get_currency_precision(gt.currency_code),
2628 inv_precision = get_currency_precision(NVL(gt.inv_currency_code, gt.currency_code));*/
2629
2630 UPDATE ar_pmts_interface_line_details line_details
2631 SET amount_applied = allocated_receipt_amount
2632 WHERE amount_applied IS NULL
2633 AND allocated_receipt_amount IS NOT NULL
2634 AND invoice_number IN ( SELECT invoice_number
2635 FROM ar_pmts_interface_header_gt header
2636 WHERE header.transmission_record_id = line_details.transmission_record_id
2637 AND header.invoice_number = line_details.invoice_number
2638 AND header.currency_code = header.invoice_currency_code
2639 AND transmission_request_id = p_trans_req_id)
2640 AND transmission_request_id = p_trans_req_id
2641 AND status = 'AR_PLB_NEW_RECORD';
2642
2643 For cur_var in customer_trx_cursor(p_trans_req_id) LOOP
2644
2645 SELECT amount_due_original
2646 INTO line_amt_due_original
2647 FROM ra_customer_trx_lines TL,ra_customer_trx T
2648 WHERE T.customer_trx_id = TL.customer_trx_id
2649 AND T.customer_trx_id = cur_var.customer_trx_id
2650 AND TL.line_type = 'LINE'
2651 AND rownum = 1;
2652
2653 if line_amt_due_original is null then
2654
2655 arp_det_dist_pkg.check_legacy_status
2656 (p_trx_id => cur_var.customer_trx_id,
2657 x_11i_adj => ll_leg_adj,
2658 x_mfar_adj => ll_mfar_adj,
2659 x_11i_app => ll_leg_app,
2660 x_mfar_app => ll_mfar_app );
2661
2662 IF (ll_leg_adj = 'Y') OR (ll_leg_app = 'Y') then
2663 UPDATE ar_pmts_interface_line_details
2664 SET status = 'AR_PLB_BAL_STAMP_FAILED'
2665 WHERE transmission_request_id = p_trans_req_id
2666 AND invoice_number = ( SELECT invoice_number
2667 FROM ar_pmts_interface_header_gt
2668 WHERE transmission_request_id = p_trans_req_id
2669 AND customer_trx_id = cur_var.customer_trx_id);
2670 else
2671 l_customer_trx.customer_trx_id := cur_var.customer_trx_id;
2672
2673 ARP_DET_DIST_PKG.SET_ORIGINAL_REM_AMT_R12(
2674 p_customer_trx => l_customer_trx,
2675 x_return_status => l_return_status,
2676 x_msg_count => l_msg_count,
2677 x_msg_data => l_msg_data,
2678 p_from_llca => 'Y'
2679 );
2680 end if;
2681
2682 end if;
2683 END LOOP;
2684
2685 UPDATE ar_pmts_interface_line_details line_details
2686 SET status = 'AR_PLB_INVALID_LINE_NUM'
2687 WHERE transmission_request_id = p_trans_req_id
2688 AND apply_to NOT in ('FREIGHT','CHARGES')
2689 AND NOT EXISTS ( select 'x'
2690 FROM ra_customer_trx trx,
2691 ra_customer_trx_lines lines,
2692 ar_pmts_interface_header_gt header
2693 WHERE trx.customer_trx_id = lines.customer_trx_id
2694 AND header.invoice_number = line_details.invoice_number
2695 AND header.transmission_record_id = line_details.transmission_record_id
2696 AND trx.customer_trx_id = header.customer_trx_id
2697 AND lines.line_type = 'LINE'
2698 AND lines.line_number = line_details.apply_to );
2699
2700 UPDATE ar_pmts_interface_line_details line_details
2701 SET status = 'AR_PLB_INVALID_LINE_NUM'
2702 WHERE transmission_request_id = p_trans_req_id
2703 AND apply_to IN ('FREIGHT', 'CHARGES')
2704 AND NOT EXISTS ( select 'x'
2705 FROM ra_customer_trx trx,
2706 ra_customer_trx_lines lines,
2707 ar_pmts_interface_header_gt header
2708 WHERE trx.customer_trx_id = lines.customer_trx_id
2709 AND header.invoice_number = line_details.invoice_number
2710 AND header.transmission_record_id = line_details.transmission_record_id
2711 AND trx.customer_trx_id = header.customer_trx_id
2712 AND lines.line_type = line_details.apply_to );
2713
2714 FOR cur_var IN customer_trx_cursor(p_trans_req_id) LOOP
2715 SELECT invoice_number,
2716 trans_to_receipt_rate,
2717 invoice_currency_code,
2718 currency_code
2719 INTO l_invoice_number,
2720 l_trans_to_receipt_rate,
2721 l_inv_currency_code,
2722 l_currency_code
2723 FROM ar_pmts_interface_header_gt
2724 WHERE transmission_request_id = p_trans_req_id
2725 AND customer_trx_id = cur_var.customer_trx_id
2726 AND rownum = 1;
2727
2728 FOR cur_var1 in apply_to_rec(p_trans_req_id,
2729 cur_var.customer_trx_id) LOOP
2730 INSERT INTO ar_pmts_interface_lines_gt(
2731 transmission_request_id,
2732 customer_trx_id,
2733 currency_code,
2734 invoice_number,
2735 invoice_currency_code,
2736 trans_to_receipt_rate,
2737 apply_to
2738 )VALUES(
2739 p_trans_req_id,
2740 cur_var.customer_trx_id,
2741 l_currency_code,
2742 l_invoice_number,
2743 l_inv_currency_code,
2744 l_trans_to_receipt_rate,
2745 cur_var1.apply_to
2746 );
2747 END LOOP;
2748 END LOOP;
2749
2750 UPDATE ar_pmts_interface_lines_gt lines
2751 SET line_amt_remaining
2752 = (select sum(nvl(TL.amount_due_remaining,0))
2753 from ra_customer_trx_lines TL
2754 where TL.customer_trx_id = lines.customer_trx_id
2755 and TL.line_number = lines.apply_to
2756 and TL.line_type = 'LINE'
2757 )
2758 WHERE lines.apply_to NOT IN ('FREIGHT', 'CHARGES');
2759
2760 UPDATE ar_pmts_interface_lines_gt lines
2761 SET tax_remaining
2762 = (select sum(nvl(TL.amount_due_remaining,0))
2763 from ra_customer_trx_lines TL
2764 where TL.link_to_cust_trx_line_id = (
2765 select l.customer_trx_line_id
2766 from ra_customer_trx_lines l
2767 where l.customer_trx_id = lines.customer_trx_id
2768 and l.line_type = 'LINE'
2769 and l.line_number = lines.apply_to)
2770 and TL.line_type = 'TAX'
2771 )
2772 WHERE lines.apply_to NOT IN ('FREIGHT', 'CHARGES');
2773
2774 UPDATE ar_pmts_interface_lines_gt lines
2775 SET freight_remaining
2776 = ( SELECT sum(nvl(TL.amount_due_remaining,0))
2777 FROM ra_customer_trx_lines TL
2778 WHERE TL.customer_trx_id = lines.customer_trx_id
2779 AND TL.line_type = lines.apply_to)
2780 WHERE lines.apply_to = 'FREIGHT';
2781
2782 UPDATE ar_pmts_interface_lines_gt lines
2783 SET charges_remaining
2784 = ( SELECT sum(nvl(TL.amount_due_remaining,0))
2785 FROM ra_customer_trx_lines TL
2786 WHERE TL.customer_trx_id = lines.customer_trx_id
2787 AND TL.line_type = lines.apply_to)
2788 WHERE lines.apply_to = 'CHARGES';
2789
2790 UPDATE ar_pmts_interface_line_details
2791 SET status = 'AR_PLB_INVALID_APP'
2792 WHERE transmission_request_id = p_trans_req_id
2793 AND (customer_trx_id, apply_to) IN
2794 (SELECT customer_trx_id, apply_to
2795 FROM ar_pmts_interface_lines_gt
2796 WHERE nvl(line_amt_remaining, 0) = 0
2797 AND nvl(tax_remaining, 0) = 0
2798 AND apply_to NOT IN ('FREIGHT', 'CHARGES'));
2799
2800 UPDATE ar_pmts_interface_line_details
2801 SET status = 'AR_PLB_INVALID_APP'
2802 WHERE transmission_request_id = p_trans_req_id
2803 AND (customer_trx_id, apply_to) IN
2804 (SELECT customer_trx_id, apply_to
2805 FROM ar_pmts_interface_lines_gt
2806 WHERE freight_remaining = 0
2807 AND apply_to = 'FREIGHT');
2808
2809 UPDATE ar_pmts_interface_line_details
2810 SET status = 'AR_PLB_INVALID_APP'
2811 WHERE transmission_request_id = p_trans_req_id
2812 AND (customer_trx_id, apply_to) IN
2813 (SELECT customer_trx_id, apply_to
2814 FROM ar_pmts_interface_lines_gt
2815 WHERE charges_remaining = 0
2816 AND apply_to = 'CHARGES' );
2817
2818 UPDATE ar_pmts_interface_header_gt header
2819 SET default_by = 'LINE_AMT'
2820 WHERE transmission_request_id = p_trans_req_id
2821 AND invoice_number IN (SELECT invoice_number
2822 FROM ar_pmts_interface_line_details
2823 WHERE transmission_request_id = p_trans_req_id
2824 AND transmission_record_id = header.transmission_record_id
2825 AND allocated_receipt_amount IS NULL
2826 AND line_amount IS NOT NULL);
2827
2828 For cur_var IN trans_rec_cur(p_trans_req_id) loop
2829 FOR cur_var1 IN invoice_rec_cur(p_trans_req_id, cur_var.transmission_record_id) LOOP
2830 SELECT invoice_currency_code,
2831 trans_to_receipt_rate,
2832 currency_code,
2833 invoice_number
2834 INTO l_inv_currency_code,
2835 l_trans_to_receipt_rate,
2836 l_currency_code,
2837 l_invoice_number
2838 FROM ar_pmts_interface_header_gt
2839 WHERE customer_trx_id = cur_var1.customer_trx_id
2840 AND transmission_request_id = p_trans_req_id
2841 AND rownum = 1;
2842
2843 l_default_by := NULL;
2844
2845 SELECT amount_applied, amount_applied_from
2846 INTO hdr_amt_app, hdr_amt_app_frm
2847 FROM ar_pmts_interface_header_gt
2848 WHERE transmission_record_id = cur_var.transmission_record_id
2849 AND invoice_number = l_invoice_number
2850 AND transmission_request_id = p_trans_req_id;
2851
2852 SELECT SUM(DECODE(apply_to,'FREIGHT',0, 'CHARGES',0,1)) line_app_count,
2853 count(*) tot_app_count
2854 INTO line_app_count, tot_app_count
2855 FROM ar_pmts_interface_line_details
2856 WHERE transmission_record_id = cur_var.transmission_record_id
2857 AND invoice_number = l_invoice_number
2858 AND transmission_request_id = p_trans_req_id ;
2859
2860 UPDATE ar_pmts_interface_line_details
2861 SET amount_applied = ARPCURR.CurrRound( allocated_receipt_amount/l_trans_to_receipt_rate,
2862 l_currency_code),
2863 line_amount = NULL,
2864 tax = NULL
2865 WHERE allocated_receipt_amount IS NOT NULL
2866 AND amount_applied IS NULL
2867 AND transmission_record_id = cur_var.transmission_record_id
2868 AND invoice_number = l_invoice_number;
2869
2870 IF SQL%ROWCOUNT = tot_app_count THEN
2871 UPDATE ar_pmts_interface_header_gt
2872 SET default_by = 'AMT_APP_FRM'
2873 WHERE transmission_record_id = cur_var.transmission_record_id
2874 AND invoice_number = l_invoice_number;
2875
2876 l_default_by := 'AMT_APP_FRM';
2877 END IF;
2878
2879 IF line_app_count > 0 THEN
2880 FOR cur_var2 IN app_to_cur(p_trans_req_id,
2881 cur_var.transmission_record_id,
2882 cur_var1.customer_trx_id) LOOP
2883
2884 SELECT line_amt_remaining,
2885 tax_remaining
2886 INTO l_line_amount_remaining,
2887 l_line_tax_remaining
2888 FROM ar_pmts_interface_lines_gt
2889 WHERE customer_trx_id = cur_var1.customer_trx_id
2890 AND transmission_request_id = p_trans_req_id
2891 AND apply_to = cur_var2.apply_to;
2892
2893 IF PG_DEBUG in ('Y', 'C') THEN
2894 arp_util.debug('Apply_to ' || cur_var2.apply_to);
2895 arp_util.debug('Line_Amt '||l_line_amount_remaining);
2896 arp_util.debug('Tax_Amt '||l_line_tax_remaining);
2897 END IF;
2898
2899 l_calc_per_line := ( Nvl(l_line_amount_remaining,0)
2900 / ( Nvl(l_line_amount_remaining,0)
2901 + Nvl(l_line_tax_remaining,0)));
2902
2903 IF PG_DEBUG in ('Y', 'C') THEN
2904 arp_util.debug('l_calc_per_line ' || l_calc_per_line);
2905 END IF;
2906
2907 IF l_calc_per_line = 0 THEN
2908 l_calc_per_line := 1;
2909 END IF;
2910
2911 If cur_var2.line_amount IS NOT NULL
2912 Then
2913 IF PG_DEBUG in ('Y', 'C') THEN
2914 arp_util.debug('First priority : Line Amount ');
2915 arp_util.debug('Line Amount has taken precedence over the amount applied ');
2916 END IF;
2917 l_calc_tot_amount_app := ARPCURR.CurrRound(
2918 ( cur_var2.line_amount
2919 / l_calc_per_line
2920 )
2921 ,l_inv_currency_code);
2922 l_calc_line_amount := ARPCURR.CurrRound(cur_var2.line_amount
2923 ,l_inv_currency_code);
2924
2925 -- Calculate Line amount based on the Amount Applied.
2926 Elsif cur_var2.amount_applied IS NOT NULL
2927 Then
2928 IF PG_DEBUG in ('Y', 'C') THEN
2929 arp_util.debug('Considered the Amount Applied value ');
2930 End If;
2931
2932 l_calc_tot_amount_app := ARPCURR.CurrRound(cur_var2.amount_applied
2933 ,l_inv_currency_code);
2934 l_calc_line_amount := ARPCURR.CurrRound((l_calc_tot_amount_app
2935 * l_calc_per_line),l_inv_currency_code);
2936 End If;
2937
2938 IF PG_DEBUG in ('Y', 'C') THEN
2939 arp_util.debug('l_calc_tot_amount_app -> '||to_char(l_calc_tot_amount_app));
2940 arp_util.debug('l_calc_line_amount -> '||to_char(l_calc_line_amount));
2941 END IF;
2942
2943 -- Tax amount has taken precedence over the Line / amount applied
2944 If cur_var2.tax IS NOT NULL
2945 THEN
2946 IF PG_DEBUG in ('Y', 'C') THEN
2947 arp_util.debug('Tax Amount has taken precedence over the amount applied ');
2948 End If;
2949 l_calc_tax_amount := ARPCURR.CurrRound(cur_var2.tax
2950 ,l_inv_currency_code);
2951
2952 l_calc_tot_amount_app := l_calc_line_amount +
2953 l_calc_tax_amount;
2954 Else
2955 IF PG_DEBUG in ('Y', 'C') THEN
2956 arp_util.debug('Amount Applied has taken precedence over the Tax Amount');
2957 End If;
2958 -- Amount applied has taken precedence over the tax amount
2959 l_calc_tax_amount := ARPCURR.CurrRound((Nvl(l_calc_tot_amount_app,0)
2960 - Nvl(l_calc_line_amount,0))
2961 ,l_inv_currency_code);
2962 End If;
2963
2964 UPDATE ar_pmts_interface_line_details
2965 SET amount_applied = l_calc_tot_amount_app,
2966 line_amount = l_calc_line_amount,
2967 tax = l_calc_tax_amount
2968 WHERE transmission_record_id = cur_var.transmission_record_id
2969 AND invoice_number = l_invoice_number
2970 AND apply_to = cur_var2.apply_to
2971 AND apply_to NOT IN ('FREIGHT','CHARGES');
2972 END LOOP;
2973 END IF;
2974
2975 IF l_inv_currency_code <> l_currency_code THEN
2976 UPDATE ar_pmts_interface_line_details line_details
2977 SET allocated_receipt_amount
2978 = ARPCURR.CurrRound( amount_applied * l_trans_to_receipt_rate,
2979 l_inv_currency_code)
2980 WHERE amount_applied IS NOT NULL
2981 AND allocated_receipt_amount IS NULL
2982 AND transmission_request_id = p_trans_req_id
2983 AND transmission_record_id = cur_var.transmission_record_id
2984 AND invoice_number = l_invoice_number;
2985
2986 IF SQL%ROWCOUNT = tot_app_count THEN
2987 UPDATE ar_pmts_interface_header_gt
2988 SET default_by = 'AMT_APP'
2989 WHERE transmission_record_id = cur_var.transmission_record_id
2990 AND invoice_number = l_invoice_number
2991 AND default_by <> 'LINE_AMT';
2992 IF SQL%ROWCOUNT > 0 THEN
2993 l_default_by := 'AMT_APP';
2994 END IF;
2995 END IF;
2996 END IF;
2997
2998 SELECT SUM(amount_applied), SUM(allocated_receipt_amount)
2999 INTO l_tot_amt_app, l_tot_amt_app_from
3000 FROM ar_pmts_interface_line_details
3001 WHERE transmission_request_id = p_trans_req_id
3002 AND transmission_record_id = cur_var.transmission_record_id
3003 AND invoice_number = l_invoice_number;
3004
3005 IF l_default_by = 'AMT_APP_FRM' THEN
3006 arp_util.debug(' Deafult By '||l_default_by);
3007 UPDATE ar_pmts_interface_line_details
3008 SET amount_applied = amount_applied + (hdr_amt_app - l_tot_amt_app )
3009 WHERE transmission_request_id = p_trans_req_id
3010 AND transmission_record_id = cur_var.transmission_record_id
3011 AND invoice_number = l_invoice_number
3012 AND rownum = 1;
3013 END IF;
3014
3015 IF l_default_by = 'AMT_APP' THEN
3016 arp_util.debug(' Deafult By '||l_default_by);
3017 UPDATE ar_pmts_interface_line_details
3018 SET allocated_receipt_amount = allocated_receipt_amount + (hdr_amt_app_frm - l_tot_amt_app_from )
3019 WHERE transmission_request_id = p_trans_req_id
3020 AND transmission_record_id = cur_var.transmission_record_id
3021 AND invoice_number = l_invoice_number
3022 AND rownum = 1;
3023 END IF;
3024
3025 END LOOP;
3026 END LOOP;
3027 /* At this stage we will have all values calculated with us. So we can go for validation */
3028 UPDATE ar_pmts_interface_line_details ld
3029 SET status = 'AR_PLB_EXCEED_LINE_AMT'
3030 WHERE transmission_request_id = p_trans_req_id
3031 AND (customer_trx_id, apply_to) IN
3032 ( select lines_gt.customer_trx_id, lines_gt.apply_to
3033 from ar_pmts_interface_lines_gt ld1,
3034 ( select customer_trx_id,
3035 apply_to,
3036 sum(lines.line_amount) tot_amt
3037 from ar_pmts_interface_line_details lines
3038 where lines.transmission_request_id = p_trans_req_id
3039 group by customer_trx_id, apply_to ) lines_gt
3040 where lines_gt.customer_trx_id = ld1.customer_trx_id
3041 and lines_gt.apply_to = ld1.apply_to
3042 and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3043 and ld1.line_amt_remaining < lines_gt.tot_amt)
3044 AND status = 'AR_PLB_NEW_RECORD';
3045
3046 UPDATE ar_pmts_interface_line_details ld
3047 SET status = 'AR_PLB_EXCEED_TAX_AMT'
3048 WHERE transmission_request_id = p_trans_req_id
3049 AND (customer_trx_id, apply_to) IN
3050 ( select lines_gt.customer_trx_id, lines_gt.apply_to
3051 from ar_pmts_interface_lines_gt ld1,
3052 ( select customer_trx_id,
3053 apply_to,
3054 sum(lines.tax) tot_amt
3055 from ar_pmts_interface_line_details lines
3056 where lines.transmission_request_id = p_trans_req_id
3057 group by customer_trx_id, apply_to ) lines_gt
3058 where lines_gt.customer_trx_id = ld1.customer_trx_id
3059 and lines_gt.apply_to = ld1.apply_to
3060 and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3061 and ld1.tax_remaining < lines_gt.tot_amt)
3062 AND status = 'AR_PLB_NEW_RECORD';
3063
3064 UPDATE ar_pmts_interface_line_details ld
3065 SET status = 'AR_PLB_EXCEED_FRGT_AMT'
3066 WHERE transmission_request_id = p_trans_req_id
3067 AND (customer_trx_id, apply_to) IN
3068 ( select ld1.customer_trx_id, ld1.apply_to
3069 from ar_pmts_interface_lines_gt ld1,
3070 ar_pmts_interface_line_details ld2
3071 where ld1.customer_trx_id = ld2.customer_trx_id
3072 and ld1.apply_to = ld2.apply_to
3073 and ld2.apply_to = 'FREIGHT'
3074 and ld2.transmission_request_id = p_trans_req_id
3075 and ld1.freight_remaining < ld2.amount_applied)
3076 AND status = 'AR_PLB_NEW_RECORD';
3077
3078 UPDATE ar_pmts_interface_line_details ld
3079 SET status = 'AR_PLB_EXCEED_CHRG_AMT'
3080 WHERE transmission_request_id = p_trans_req_id
3081 AND (customer_trx_id, apply_to) IN
3082 ( select ld1.customer_trx_id, ld1.apply_to
3083 from ar_pmts_interface_lines_gt ld1,
3084 ar_pmts_interface_line_details ld2
3085 where ld1.customer_trx_id = ld2.customer_trx_id
3086 and ld1.apply_to = ld2.apply_to
3087 and ld2.transmission_request_id = p_trans_req_id
3088 and ld2.apply_to = 'CHARGES'
3089 and ld1.charges_remaining < ld2.amount_applied)
3090 AND status = 'AR_PLB_NEW_RECORD';
3091
3092 UPDATE ar_pmts_interface_line_details line_details
3093 SET status = 'AR_PLB_AMT_APP_INVALID'
3094 WHERE transmission_request_id = p_trans_req_id
3095 AND (transmission_record_id, customer_trx_id) IN
3096 (select header.transmission_record_id, header.customer_trx_id
3097 from ar_pmts_interface_header_gt header,
3098 (select transmission_record_id,
3099 customer_trx_id,
3100 sum(amount_applied) aa
3101 from ar_pmts_interface_line_details ld
3102 where ld.transmission_request_id = p_trans_req_id
3103 group by transmission_record_id, customer_trx_id) line
3104 where header.transmission_record_id = line.transmission_record_id
3105 and header.customer_trx_id = line.customer_trx_id
3106 and header.transmission_request_id = p_trans_req_id
3107 and header.amount_applied <> line.aa)
3108 AND status = 'AR_PLB_NEW_RECORD';
3109
3110 UPDATE ar_pmts_interface_line_details line_details
3111 SET status = 'AR_PLB_AMT_APP_FRM_INVALID'
3112 WHERE transmission_request_id = p_trans_req_id
3113 AND (transmission_record_id, customer_trx_id) IN
3114 (select header.transmission_record_id, header.customer_trx_id
3115 from ar_pmts_interface_header_gt header,
3116 (select transmission_record_id,
3117 customer_trx_id,
3118 sum(allocated_receipt_amount) aa
3119 from ar_pmts_interface_line_details ld
3120 where ld.transmission_request_id = p_trans_req_id
3121 group by transmission_record_id, customer_trx_id) line
3122 where header.transmission_record_id = line.transmission_record_id
3123 and header.customer_trx_id = line.customer_trx_id
3124 and header.transmission_request_id = p_trans_req_id
3125 and header.amount_applied_from <> line.aa)
3126 AND status = 'AR_PLB_NEW_RECORD';
3127
3128 UPDATE ar_pmts_interface_line_details
3129 SET status = 'AR_PLB_LINE_OK'
3130 WHERE status = 'AR_PLB_NEW_RECORD'
3131 AND transmission_request_id = p_trans_req_id;
3132
3133 IF p_allow_invalid_trx_num = 'Y' THEN
3134 UPDATE ar_payments_interface interface
3135 SET resolved_matching_number1 = NULL
3136 WHERE (transmission_record_id, resolved_matching_number1)
3137 IN (SELECT transmission_record_id, invoice_number
3138 FROM ar_pmts_interface_line_details line_details
3139 WHERE transmission_request_id = p_trans_req_id
3140 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3141 AND transmission_request_id = p_trans_req_id;
3142
3143 UPDATE ar_payments_interface interface
3144 SET resolved_matching_number2 = NULL
3145 WHERE (transmission_record_id, resolved_matching_number2)
3146 IN (SELECT transmission_record_id, invoice_number
3147 FROM ar_pmts_interface_line_details line_details
3148 WHERE transmission_request_id = p_trans_req_id
3149 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3150 AND transmission_request_id = p_trans_req_id;
3151
3152 UPDATE ar_payments_interface interface
3153 SET resolved_matching_number3 = NULL
3154 WHERE (transmission_record_id, resolved_matching_number3)
3155 IN (SELECT transmission_record_id, invoice_number
3156 FROM ar_pmts_interface_line_details line_details
3157 WHERE transmission_request_id = p_trans_req_id
3158 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3159 AND transmission_request_id = p_trans_req_id;
3160
3161 UPDATE ar_payments_interface interface
3162 SET resolved_matching_number4 = NULL
3163 WHERE (transmission_record_id, resolved_matching_number4)
3164 IN (SELECT transmission_record_id, invoice_number
3165 FROM ar_pmts_interface_line_details line_details
3166 WHERE transmission_request_id = p_trans_req_id
3167 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3168 AND transmission_request_id = p_trans_req_id;
3169
3170 UPDATE ar_payments_interface interface
3171 SET resolved_matching_number5 = NULL
3172 WHERE (transmission_record_id, resolved_matching_number5)
3173 IN (SELECT transmission_record_id, invoice_number
3174 FROM ar_pmts_interface_line_details line_details
3175 WHERE transmission_request_id = p_trans_req_id
3176 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3177 AND transmission_request_id = p_trans_req_id;
3178
3179 UPDATE ar_payments_interface interface
3180 SET resolved_matching_number6 = NULL
3181 WHERE (transmission_record_id, resolved_matching_number6)
3182 IN (SELECT transmission_record_id, invoice_number
3183 FROM ar_pmts_interface_line_details line_details
3184 WHERE transmission_request_id = p_trans_req_id
3185 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3186 AND transmission_request_id = p_trans_req_id;
3187
3188 UPDATE ar_payments_interface interface
3189 SET resolved_matching_number7 = NULL
3190 WHERE (transmission_record_id, resolved_matching_number7)
3191 IN (SELECT transmission_record_id, invoice_number
3192 FROM ar_pmts_interface_line_details line_details
3193 WHERE transmission_request_id = p_trans_req_id
3194 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3195 AND transmission_request_id = p_trans_req_id;
3196
3197 UPDATE ar_payments_interface interface
3198 SET resolved_matching_number8 = NULL
3199 WHERE (transmission_record_id, resolved_matching_number8)
3200 IN (SELECT transmission_record_id, invoice_number
3201 FROM ar_pmts_interface_line_details line_details
3202 WHERE transmission_request_id = p_trans_req_id
3203 AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3204 AND transmission_request_id = p_trans_req_id;
3205 ELSE
3206 UPDATE ar_payments_interface interface
3207 SET invoice1_status = 'AR_PLB_INVALID_LINE_DET'
3208 WHERE (transmission_record_id, resolved_matching_number1)
3209 IN (SELECT transmission_record_id, invoice_number
3210 FROM ar_pmts_interface_line_details line_details
3211 WHERE transmission_request_id = p_trans_req_id
3212 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3213 AND transmission_request_id = p_trans_req_id;
3214
3215 UPDATE ar_payments_interface interface
3216 SET invoice2_status = 'AR_PLB_INVALID_LINE_DET'
3217 WHERE (transmission_record_id, resolved_matching_number2)
3218 IN (SELECT transmission_record_id, invoice_number
3219 FROM ar_pmts_interface_line_details line_details
3220 WHERE transmission_request_id = p_trans_req_id
3221 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3222 AND transmission_request_id = p_trans_req_id;
3223
3224 UPDATE ar_payments_interface interface
3225 SET invoice3_status = 'AR_PLB_INVALID_LINE_DET'
3226 WHERE (transmission_record_id, resolved_matching_number3)
3227 IN (SELECT transmission_record_id, invoice_number
3228 FROM ar_pmts_interface_line_details line_details
3229 WHERE transmission_request_id = p_trans_req_id
3230 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3231 AND transmission_request_id = p_trans_req_id;
3232
3233 UPDATE ar_payments_interface interface
3234 SET invoice4_status = 'AR_PLB_INVALID_LINE_DET'
3235 WHERE (transmission_record_id, resolved_matching_number4)
3236 IN (SELECT transmission_record_id, invoice_number
3237 FROM ar_pmts_interface_line_details line_details
3238 WHERE transmission_request_id = p_trans_req_id
3239 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3240 AND transmission_request_id = p_trans_req_id;
3241
3242 UPDATE ar_payments_interface interface
3243 SET invoice5_status = 'AR_PLB_INVALID_LINE_DET'
3244 WHERE (transmission_record_id, resolved_matching_number5)
3245 IN (SELECT transmission_record_id, invoice_number
3246 FROM ar_pmts_interface_line_details line_details
3247 WHERE transmission_request_id = p_trans_req_id
3248 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3249 AND transmission_request_id = p_trans_req_id;
3250
3251 UPDATE ar_payments_interface interface
3252 SET invoice6_status = 'AR_PLB_INVALID_LINE_DET'
3253 WHERE (transmission_record_id, resolved_matching_number6)
3254 IN (SELECT transmission_record_id, invoice_number
3255 FROM ar_pmts_interface_line_details line_details
3256 WHERE transmission_request_id = p_trans_req_id
3257 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3258 AND transmission_request_id = p_trans_req_id;
3259
3260 UPDATE ar_payments_interface interface
3261 SET invoice7_status = 'AR_PLB_INVALID_LINE_DET'
3262 WHERE (transmission_record_id, resolved_matching_number7)
3263 IN (SELECT transmission_record_id, invoice_number
3264 FROM ar_pmts_interface_line_details line_details
3265 WHERE transmission_request_id = p_trans_req_id
3266 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3267 AND transmission_request_id = p_trans_req_id;
3268
3269 UPDATE ar_payments_interface interface
3270 SET invoice8_status = 'AR_PLB_INVALID_LINE_DET'
3271 WHERE (transmission_record_id, resolved_matching_number8)
3272 IN (SELECT transmission_record_id, invoice_number
3273 FROM ar_pmts_interface_line_details line_details
3274 WHERE transmission_request_id = p_trans_req_id
3275 AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3276 AND transmission_request_id = p_trans_req_id;
3277
3278 UPDATE ar_payments_interface interface
3279 SET status = 'AR_PLB_INVALID_RECEIPT'
3280 WHERE (item_number, nvl(batch_name, -1))
3281 IN (SELECT item_number, nvl(batch_name, -1)
3282 FROM ar_payments_interface interface1
3283 WHERE interface1.transmission_request_id = p_trans_req_id
3284 AND ( interface1.invoice1_status = 'AR_PLB_INVALID_LINE_DET'
3285 OR interface1.invoice2_status = 'AR_PLB_INVALID_LINE_DET'
3286 OR interface1.invoice3_status = 'AR_PLB_INVALID_LINE_DET'
3287 OR interface1.invoice4_status = 'AR_PLB_INVALID_LINE_DET'
3288 OR interface1.invoice5_status = 'AR_PLB_INVALID_LINE_DET'
3289 OR interface1.invoice6_status = 'AR_PLB_INVALID_LINE_DET'
3290 OR interface1.invoice7_status = 'AR_PLB_INVALID_LINE_DET'
3291 OR interface1.invoice8_status = 'AR_PLB_INVALID_LINE_DET'))
3292 AND transmission_request_id = p_trans_req_id;
3293 END IF;
3294 debug1('arp_process_lockbox.validate_llca_interface_data()-');
3295 EXCEPTION
3296 WHEN OTHERS THEN
3297 p_return_status := 'E';
3298 debug1('Error '|| SQLERRM);
3299 debug1('validate_llca_interface_data : p_return_status ' || p_return_status);
3300 RAISE;
3301 END validate_llca_interface_data;
3302 --
3303 FUNCTION get_format_amount (
3304 p_trans_req_id IN NUMBER,
3305 p_trans_rec_id IN NUMBER,
3306 p_column_type IN varchar2)
3307 RETURN VARCHAR2
3308 IS
3309 l_format_yn varchar2(10) := 'N';
3310 BEGIN
3311
3312 select amount_format_lookup_code
3313 into l_format_yn
3314 from
3315 AR_TRANS_FIELD_FORMATS FF,
3316 AR_TRANS_RECORD_FORMATS RF,
3317 AR_PAYMENTS_INTERFACE_all PI,
3318 AR_TRANSMISSIONS_all TR
3319 where TR.transmission_request_id = p_trans_req_id
3320 and PI.transmission_record_id = p_trans_rec_id
3321 and RF.transmission_format_id = TR.requested_trans_format_id
3322 and RF.record_identifier = PI.record_type
3323 and FF.transmission_format_id = RF.transmission_format_id
3324 and FF.record_format_id = RF.record_format_id
3325 and field_type_lookup_code= p_column_type;
3326 if l_format_yn = 'Y' then
3327 return l_format_yn;
3328 else
3329 return 'N';
3330 end if;
3331 EXCEPTION
3332 WHEN OTHERS THEN
3333 return 'E';
3334 END get_format_amount;
3335 --
3336 FUNCTION get_currency_precision(p_currency_code IN fnd_currencies.currency_code%TYPE )
3337 RETURN NUMBER IS
3338 table_size NUMBER;
3339 i NUMBER;
3340 l_precision NUMBER;
3341 BEGIN
3342 table_size := NVL(l_table_curr.last, 0);
3343
3344 FOR i in 1..table_size
3345 LOOP
3346 IF l_table_curr(i).currency_code = p_currency_code THEN
3347 RETURN l_table_curr(i).precision;
3348 END IF ;
3349 END LOOP ;
3350
3351 SELECT precision
3352 INTO l_precision
3353 FROM fnd_currencies
3354 WHERE currency_code = p_currency_code ;
3355
3356 l_table_curr(table_size+1).currency_code := p_currency_code ;
3357 l_table_curr(table_size+1).precision := l_precision ;
3358
3359 RETURN l_precision;
3360
3361 EXCEPTION
3362 WHEN NO_DATA_FOUND THEN
3363 RAISE;
3364 END;
3365 --
3366 /*===========================================================================+
3367 | PROCEDURE |
3368 | insert_interim_line_details |
3369 | |
3370 | DESCRIPTION |
3371 | insert records into AR_INTERIM_CASH_LINE_DETAILS |
3372 | |
3373 | SCOPE - PRIVATE |
3374 | |
3375 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
3376 | |
3377 | ARGUMENTS : IN: |
3378 | |
3379 | MODIFICATION HISTORY : |
3380 | |
3381 | 28-Jul-07 vpusulur Created. |
3382 +===========================================================================*/
3383 PROCEDURE insert_interim_line_details(
3384 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
3385 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%type,
3386 p_cash_receipt_line_id IN NUMBER,
3387 p_trans_req_id IN ar_payments_interface.transmission_request_id%type,
3388 p_batch_name IN ar_payments_interface.batch_name%type,
3389 p_item_num IN ar_payments_interface.item_number%type,
3390 p_return_status OUT NOCOPY varchar2
3391 )
3392 IS
3393
3394 status_count number := 0;
3395 inv_number ra_customer_trx.trx_number%type;
3396 l_line_id number;
3397 tot_frgt_chrg_amt number := 0;
3398 prorated_frgt_chrg_amt number := 0;
3399 prorated_frgt_chrg_amt_frm number := 0;
3400 remaining_frgt_chrg_amt number := 0;
3401 remaining_frgt_chrg_amt_frm number := 0;
3402 l_currency_code varchar2(15);
3403 l_inv_currency_code VARCHAR2(15);
3404 l_trans_to_receipt_rate NUMBER;
3405 l_amount_applied_from NUMBER;
3406 l_amount_applied NUMBER;
3407 l_item_number ar_payments_interface.item_number%type;
3408 l_batch_name ar_payments_interface.batch_name%type;
3409
3410 cursor interface_det_lines(inv_num in varchar2,req_id in number, receipt_id in number) is
3411 select status,
3412 invoice_number,
3413 apply_to,
3414 allocated_receipt_amount,
3415 amount_applied,
3416 line_amount,
3417 tax,
3418 freight,
3419 charges,
3420 line_discount,
3421 tax_discount,
3422 freight_discount,
3423 comments,
3424 transmission_record_id
3425 from ar_pmts_interface_line_details
3426 where transmission_request_id = req_id
3427 and invoice_number = inv_num
3428 and transmission_record_id in (
3429 select overflow.transmission_record_id
3430 from ar_payments_interface payment, ar_payments_interface overflow,
3431 ar_trans_record_formats format, ar_transmissions transmissions,
3432 ar_interim_cash_receipts cash
3433 where payment.check_number = cash.receipt_number
3434 and payment.transmission_request_id = overflow.transmission_request_id
3435 and payment.item_number = overflow.item_number
3436 and nvl(payment.batch_name, -1) = nvl(overflow.batch_name, -1)
3437 and overflow.record_type = format.record_identifier
3438 and transmissions.requested_trans_format_id = format.transmission_format_id
3439 AND transmissions.transmission_request_id = payment.transmission_request_id
3440 AND format.record_type_lookup_code = 'OVRFLW PAYMENT'
3441 and cash.cash_receipt_id = receipt_id
3442 and payment.transmission_request_id = req_id
3443 and payment.item_number = l_item_number
3444 and nvl(payment.batch_name, -1) = nvl(l_batch_name, -1) ) ;
3445
3446
3447 cursor frgt_chrg_prorate(ct_id in number,app_to in varchar2) is
3448 select customer_trx_line_id,
3449 amount_due_remaining
3450 from ra_customer_trx_lines
3451 where customer_trx_id = ct_id
3452 and line_type = app_to;
3453
3454 BEGIN
3455 debug1('insert_interim_line_details()+ ' ||p_cash_receipt_line_id );
3456 l_item_number := p_item_num;
3457 l_batch_name := p_batch_name;
3458
3459
3460 select trx_number
3461 into inv_number
3462 from ra_customer_trx
3463 where customer_trx_id = p_customer_trx_id;
3464
3465 select currency_code
3466 into l_currency_code
3467 from ar_interim_cash_receipts
3468 where cash_receipt_id = p_cash_receipt_id;
3469
3470 select count(distinct status)
3471 into status_count
3472 from ar_pmts_interface_line_details
3473 where transmission_request_id = p_trans_req_id
3474 and invoice_number = inv_number;
3475
3476 select count(distinct status)
3477 into status_count
3478 from ar_pmts_interface_line_details
3479 where transmission_request_id = p_trans_req_id
3480 and invoice_number = inv_number;
3481
3482 if status_count = 1 then
3483 FOR det_line IN interface_det_lines(inv_number,p_trans_req_id,p_cash_receipt_id) LOOP
3484 if det_line.status = 'AR_PLB_LINE_OK' then
3485 if det_line.apply_to not in ('FREIGHT','CHARGES') then
3486
3487 Select ar_activity_details_s.nextval
3488 INTO l_line_id
3489 from dual;
3490
3491 insert into AR_INTERIM_CASH_LINE_DETAILS(
3492 cash_receipt_id,
3493 customer_trx_line_id,
3494 source_id,
3495 source_table,
3496 allocated_receipt_amount,
3497 amount,
3498 tax,
3499 freight,
3500 charges,
3501 last_update_date,
3502 last_updated_by,
3503 line_discount,
3504 tax_discount,
3505 freight_discount,
3506 line_balance,
3507 tax_balance,
3508 creation_date,
3509 created_by,
3510 last_update_login,
3511 comments,
3512 apply_to,
3513 attribute1,
3514 attribute2,
3515 attribute3,
3516 attribute4,
3517 attribute5,
3518 attribute6,
3519 attribute7,
3520 attribute8,
3521 attribute9,
3522 attribute10,
3523 attribute11,
3524 attribute12,
3525 attribute13,
3526 attribute14,
3527 attribute15,
3528 attribute_category,
3529 reference1,
3530 reference2,
3531 reference3,
3532 reference4,
3533 reference5,
3534 group_id,
3535 object_version_number,
3536 created_by_module,
3537 line_id)
3538 select
3539 p_cash_receipt_id,
3540 l.customer_trx_line_id,
3541 null,
3542 null,
3543 nvl(det_line.allocated_receipt_amount,0),
3544 nvl(det_line.line_amount,0),
3545 nvl(det_line.tax,0),
3546 nvl(det_line.freight,0),
3547 nvl(det_line.charges,0),
3548 sysdate,
3549 null,
3550 nvl(det_line.line_discount,0),
3551 nvl(det_line.tax_discount,0),
3552 nvl(det_line.freight_discount,0),
3553 null,
3554 null,
3555 sysdate,
3556 null,
3557 null,
3558 det_line.comments,
3559 det_line.apply_to,
3560 null,
3561 null,
3562 null,
3563 null,
3564 null,
3565 null,
3566 null,
3567 null,
3568 null,
3569 null,
3570 null,
3571 null,
3572 null,
3573 null,
3574 null,
3575 null,
3576 null,
3577 null,
3578 null,
3579 null,
3580 null,
3581 null,
3582 1,
3583 'ARLPLB',
3584 l_line_id
3585 from ra_customer_trx_lines l
3586 where l.customer_trx_id = p_customer_trx_id
3587 and l.line_type = 'LINE'
3588 and l.line_number = det_line.apply_to;
3589 else
3590 Select sum(nvl(amount_due_remaining,0))
3591 into tot_frgt_chrg_amt
3592 from ra_customer_trx_lines
3593 where customer_trx_id = p_customer_trx_id
3594 and line_type = det_line.apply_to;
3595
3596 SELECT invoice_currency_code,
3597 trans_to_receipt_rate
3598 INTO l_inv_currency_code,
3599 l_trans_to_receipt_rate
3600 FROM ar_pmts_interface_header_gt
3601 WHERE customer_trx_id = p_customer_trx_id
3602 AND transmission_request_id = p_trans_req_id
3603 AND rownum = 1;
3604
3605 remaining_frgt_chrg_amt := det_line.amount_applied;
3606 remaining_frgt_chrg_amt_frm := nvl(det_line.allocated_receipt_amount, 0);
3607
3608 FOR line IN frgt_chrg_prorate(p_customer_trx_id, det_line.apply_to) LOOP
3609
3610 prorated_frgt_chrg_amt := ARPCURR.CurrRound(
3611 remaining_frgt_chrg_amt*
3612 (line.amount_due_remaining/tot_frgt_chrg_amt),
3613 l_inv_currency_code);
3614
3615 IF l_currency_code <> l_inv_currency_code THEN
3616 prorated_frgt_chrg_amt_frm := ARPCURR.CurrRound(
3617 remaining_frgt_chrg_amt_frm*
3618 (line.amount_due_remaining/tot_frgt_chrg_amt),
3619 l_currency_code);
3620 END IF;
3621
3622 Select ar_activity_details_s.nextval
3623 INTO l_line_id
3624 from dual;
3625
3626 insert into AR_INTERIM_CASH_LINE_DETAILS(
3627 cash_receipt_id,
3628 customer_trx_line_id,
3629 source_id,
3630 source_table,
3631 allocated_receipt_amount,
3632 amount,
3633 tax,
3634 freight,
3635 charges,
3636 last_update_date,
3637 last_updated_by,
3638 line_discount,
3639 tax_discount,
3640 freight_discount,
3641 line_balance,
3642 tax_balance,
3643 creation_date,
3644 created_by,
3645 last_update_login,
3646 comments,
3647 apply_to,
3648 attribute1,
3649 attribute2,
3650 attribute3,
3651 attribute4,
3652 attribute5,
3653 attribute6,
3654 attribute7,
3655 attribute8,
3656 attribute9,
3657 attribute10,
3658 attribute11,
3659 attribute12,
3660 attribute13,
3661 attribute14,
3662 attribute15,
3663 attribute_category,
3664 reference1,
3665 reference2,
3666 reference3,
3667 reference4,
3668 reference5,
3669 group_id,
3670 object_version_number,
3671 created_by_module,
3672 line_id)
3673 values(
3674 p_cash_receipt_id,
3675 line.customer_trx_line_id,
3676 null,
3677 null,
3678 prorated_frgt_chrg_amt_frm,
3679 nvl(det_line.line_amount,0),
3680 nvl(det_line.tax,0),
3681 decode(det_line.apply_to,'FREIGHT',prorated_frgt_chrg_amt,0),
3682 decode(det_line.apply_to,'CHARGES',prorated_frgt_chrg_amt,0),
3683 sysdate,
3684 null,
3685 nvl(det_line.line_discount,0),
3686 nvl(det_line.tax_discount,0),
3687 decode(det_line.apply_to,'FREIGHT',
3688 nvl(det_line.freight_discount,0)*(nvl(line.amount_due_remaining,0)/
3689 tot_frgt_chrg_amt),0),
3690 null,
3691 null,
3692 sysdate,
3693 null,
3694 null,
3695 det_line.comments,
3696 det_line.apply_to,
3697 null,
3698 null,
3699 null,
3700 null,
3701 null,
3702 null,
3703 null,
3704 null,
3705 null,
3706 null,
3707 null,
3708 null,
3709 null,
3710 null,
3711 null,
3712 null,
3713 null,
3714 null,
3715 null,
3716 null,
3717 null,
3718 null,
3719 1,
3720 'ARLPLB',
3721 l_line_id);
3722
3723 remaining_frgt_chrg_amt := remaining_frgt_chrg_amt - prorated_frgt_chrg_amt;
3724 remaining_frgt_chrg_amt_frm := remaining_frgt_chrg_amt_frm - nvl(prorated_frgt_chrg_amt_frm, 0);
3725 tot_frgt_chrg_amt := tot_frgt_chrg_amt - line.amount_due_remaining;
3726
3727 END LOOP;
3728 end if;
3729 end if;
3730 delete from ar_pmts_interface_line_details
3731 where transmission_request_id = p_trans_req_id
3732 and invoice_number = inv_number
3733 and transmission_record_id = det_line.transmission_record_id
3734 and status = 'AR_PLB_LINE_OK';
3735 END LOOP;
3736 end if;
3737 p_return_status := 'S';
3738 debug1('insert_interim_line_details()-');
3739 EXCEPTION
3740 WHEN OTHERS THEN
3741 p_return_status := 'E';
3742 debug1('update_statuses : p_return_status ' || p_return_status);
3743 debug1('Exception : '||SQLERRM);
3744 RAISE;
3745 END insert_interim_line_details;
3746 --
3747 --
3748 -- This is a First-Time-Only code. It will get executed only
3749 -- the first time the package is instantiated.
3750 -- It fetches the matching_rules specified in ar_lookups
3751 -- Opens and parses the cursor for that.
3752 -- It stores the cursor_names in table opened_cursor_table.
3753 --
3754 -- Logic:
3755 -- Open the cursor for the matching_option,
3756 -- If the matching option is one of the standard ones.
3757 -- i.e., INVOICE
3758 -- PURCHASE_ORDER
3759 -- SALES_ORDER
3760 -- CONSOLIDATE_BILL
3761 -- Else
3762 -- Call arp_lockbox_hook.custom_cursor
3763 -- Parse the cursor
3764 -- Return cursor_name to find_cust_and_trx_num
3765 --
3766 --
3767 BEGIN
3768 g_include_closed_inv := NVL(FND_PROFILE.value('AR_INC_CLINV_AUTO_ASSO'), 'Y');
3769 g_total_maching_options := 0;
3770 debug1('Common code for arp_process_lockbox +');
3771 Open all_matching_options;
3772 debug1('Opened all_matching_options cursor');
3773 LOOP
3774 FETCH all_matching_options
3775 INTO g_matching_option;
3776 EXIT WHEN all_matching_options%NOTFOUND;
3777 debug1('Fetched cursor matching_options found g_matching_option = ' || g_matching_option);
3778 IF (g_matching_option = 'INVOICE') THEN
3779 g_cursor_string := NULL; -- Initilising, may not be required.
3780 /* Constructing the SELECT clause */
3781 g_cursor_string := 'select ';
3782 g_cursor_string := g_cursor_string ||
3783 'decode(count(distinct ps.customer_id), ' ||
3784 ' 0, -9999,' || /* No Matching transaction */
3785 ' 1, max(ps.customer_id),' ||
3786 ' decode(:b_use_matching_date,''NEVER'', -7777,' || /* More than one customer, without matching date */
3787 ' ''ALWAYS'', -7777,' || /* More than one customer on Matching date */
3788 ' ''FOR_DUPLICATES'', decode(sum(decode(ps.trx_date, :b_current_matching_date, 1,' ||
3789 ' 0)), 1,' ||
3790 ' max(decode(ps.trx_date, :b_current_matching_date, ps.customer_id, -7777 )),' ||
3791 ' -7777))), ';
3792 --
3793 /* Bugfix 2651113. Added DISTINCT so that lockbox does not error out
3794 for invoice with multiple terms.
3795 Bug 7431540 : Modified Logic to handle closed invoices in data file
3796 If a invoice is closed, then matching number will be passes ad -1111
3797 whereas customer information is retained. Later, the application will
3798 be rejected as 'AR_PLB_INVALID_MATCH'.
3799 Bug 9156980 : Modified the Logic so that the closed invoices are
3800 considered for customer identification only based on the profile
3801 option 'AR_INC_CL_INV_FOR_CUST_ID'*/
3802 g_cursor_string := g_cursor_string ||
3803 'decode(count(distinct ps.customer_id), ' ||
3804 ' 0, NULL, '; /* No Matching transaction */
3805 IF g_include_closed_inv = 'Y' THEN
3806 g_cursor_string := g_cursor_string ||
3807 ' 1, decode(count(distinct ps.status), 1, decode(max(ps.status), ''CL'', '||
3808 ' (decode(max(tt.allow_overapplication_flag), ''N'', ''-1111'', max(ps.trx_number))), max(ps.trx_number)), max(ps.trx_number)), ';
3809 ELSE
3810 g_cursor_string := g_cursor_string ||
3811 ' 1, max(ps.trx_number), ';
3812 END IF;
3813 g_cursor_string := g_cursor_string ||
3814 ' decode(:b_use_matching_date,''NEVER'', NULL,' || /* More than one customer, without matching date */
3815 ' ''ALWAYS'', NULL,' || /* More than one customer on Matching date */
3816 ' ''FOR_DUPLICATES'', decode(sum(decode(ps.trx_date, :b_current_matching_date, 1,' ||
3817 ' 0)), 1,';
3818 IF g_include_closed_inv = 'Y' THEN
3819 g_cursor_string := g_cursor_string ||
3820 ' max(decode(ps.trx_date, :b_current_matching_date, ' ||
3821 ' decode(ps.status, ''CL'', (decode(tt.allow_overapplication_flag, ''N'', ''-1111'', '||
3822 ' ps.trx_number)), ps.trx_number), NULL )),';
3823 ELSE
3824 g_cursor_string := g_cursor_string ||
3825 ' max(decode(ps.trx_date, :b_current_matching_date, ps.trx_number, NULL )),';
3826 END IF;
3827 g_cursor_string := g_cursor_string ||
3828 ' NULL))), ';
3829 --
3830 /* Bugfix 2651113. Added DISTINCT so that lockbox does not error out
3831 for invoice with multiple terms. */
3832 g_cursor_string := g_cursor_string ||
3833 'decode(count(distinct ps.customer_id), ' ||
3834 ' 0, NULL, ' || /* No Matching transaction */
3835 ' 1, min(ps.trx_date), ' ||
3836 ' decode(:b_use_matching_date,''NEVER'', NULL,' || /* More than one customer, without matching date */
3837 ' ''ALWAYS'', NULL,' || /* More than one customer on Matching date */
3838 ' ''FOR_DUPLICATES'', decode(sum(decode(ps.trx_date, :b_current_matching_date, 1,' ||
3839 ' 0)), 1,' ||
3840 ' max(decode(ps.trx_date, :b_current_matching_date, ps.trx_date, NULL )),' ||
3841 ' NULL))) ';
3842 --
3843 /* Constructing the FROM clause */
3844 g_cursor_string := g_cursor_string ||
3845 ' from ' ||
3846 ' hz_customer_profiles cps, ' || /* For site level profile */
3847 ' hz_customer_profiles cpc, ' || /* For customer level profile */
3848 ' ra_cust_trx_types tt, ' ||
3849 ' ar_payment_schedules ps ';
3850 /* Constructing WHERE clause */
3851 /* Bug2106408. Modified the WHERE clause so that
3852 ps.payment_schedule_id included all the ps_id satisfying the
3853 matching condition instead of just the min(ps_id). */
3854 g_cursor_string := g_cursor_string ||
3855 ' where ' ||
3856 ' ps.trx_number = :b_current_matching_number ' ||
3857 ' and EXISTS ( select ''Exists dual'' from dual ' ||
3858 ' where decode(:b_pay_unrelated_customers, ''Y'', ps.customer_id, ' ||
3859 ' nvl(:b_customer_id, ps.customer_id)) = ps.customer_id ' ||
3860 ' UNION ' ||
3861 ' select ''Exists ha_cust_acct_relate'' ' ||
3862 ' from hz_cust_acct_relate rel ' ||
3863 ' where rel.cust_account_id = :b_customer_id ' ||
3864 ' and rel.bill_to_flag = ''Y'' ' ||
3865 ' and rel.status = ''A'' ' ||
3866 ' UNION ' ||
3867 ' select ''Exists ar_paying_relationships_v'' ' ||
3868 ' from ar_paying_relationships_v rel,' ||
3869 ' hz_cust_accounts acc ' ||
3870 ' where acc.cust_account_id = :b_customer_id ' ||
3871 ' and acc.party_id = rel.party_id ' ||
3872 ' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
3873 ' AND effective_end_date ) ';
3874 /* bug2958709 : Added 'group by' and min(ps_id) */
3875 /* bug3518714 : Added ps.trx_date in 'group by' clause */
3876 /* Bug 7431540: Logic to fetch payment_schedule_id is modified such that, if all the installments for a particular
3877 invoice are closed, then fetch minimum payment_schedule_id for that transaction. Otherwise, fetch minimum open
3878 payment_schedule_id. Commented out reference to ra_cust_trx_types as the query needs to fetch trx info even when
3879 it is closed.
3880 Bug 9156980 : Modified the Logic so that the closed invoices are
3881 considered for customer identification only based on the profile
3882 option 'AR_INC_CL_INV_FOR_CUST_ID'*/
3883 IF g_include_closed_inv = 'Y' THEN
3884 g_cursor_string := g_cursor_string ||
3885 ' and ( (ps.payment_schedule_id IN ' ||
3886 ' (select decode( min( decode(ps.status, ''CL'', 99999999999999999999, ps.payment_schedule_id)), '||
3887 ' 99999999999999999999, min(ps.payment_schedule_id), '||
3888 ' min( decode(ps.status, ''CL'', 99999999999999999999, ps.payment_schedule_id))) ' ||
3889 ' from ar_payment_schedules ps '
3890 /* ' ra_cust_trx_types tt ' || */;
3891 ELSE
3892 g_cursor_string := g_cursor_string ||
3893 ' and ( (ps.payment_schedule_id IN ' ||
3894 ' (select min(ps.payment_schedule_id) ' ||
3895 ' from ar_payment_schedules ps, ' ||
3896 ' ra_cust_trx_types tt ';
3897 END IF;
3898 g_cursor_string := g_cursor_string ||
3899 ' where ps.trx_number = :b_current_matching_number ' ||
3900 ' and ps.class not in (''PMT'', ''GUAR'') ';
3901 IF g_include_closed_inv = 'N' THEN
3902 g_cursor_string := g_cursor_string ||
3903 ' and ps.status = decode(tt.allow_overapplication_flag,' ||
3904 ' ''N'', ''OP'', ps.status) ';
3905 END IF;
3906 g_cursor_string := g_cursor_string ||
3907 ' and EXISTS ( select ''Exists dual'' from dual ' ||
3908 ' where decode(:b_pay_unrelated_customers, ''Y'', ps.customer_id, ' ||
3909 ' nvl(:b_customer_id, ps.customer_id)) = ps.customer_id ' ||
3910 ' UNION ' ||
3911 ' select ''Exists ha_cust_acct_relate'' ' ||
3912 ' from hz_cust_acct_relate rel ' ||
3913 ' where rel.cust_account_id = :b_customer_id ' ||
3914 ' and rel.bill_to_flag = ''Y'' ' ||
3915 ' and rel.status = ''A'' ' ||
3916 ' UNION ' ||
3917 ' select ''Exists ar_paying_relationships_v'' ' ||
3918 ' from ar_paying_relationships_v rel,' ||
3919 ' hz_cust_accounts acc ' ||
3920 ' where acc.cust_account_id = :b_customer_id ' ||
3921 ' and acc.party_id = rel.party_id ' ||
3922 ' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
3923 ' AND effective_end_date ) ';
3924 IF g_include_closed_inv = 'N' THEN
3925 g_cursor_string := g_cursor_string ||
3926 ' and ps.cust_trx_type_id = tt.cust_trx_type_id ';
3927 END IF;
3928 g_cursor_string := g_cursor_string ||
3929 ' group by ps.customer_id, ps.trx_date)) or ' ||
3930 ' (ps.class = ''CM'' and ps.terms_sequence_number = 1)) ';
3931 IF g_include_closed_inv = 'N' THEN
3932 g_cursor_string := g_cursor_string ||
3933 ' and ps.status = decode(tt.allow_overapplication_flag,' ||
3934 ' ''N'', ''OP'', ps.status) ';
3935 END IF;
3936 g_cursor_string := g_cursor_string ||
3937 ' and ps.class not in (''PMT'', ''GUAR'') ' ||
3938 ' and ps.cust_trx_type_id = tt.cust_trx_type_id ' ||
3939 ' and ps.customer_id = cpc.cust_account_id ' ||
3940 ' and cpc.site_use_id is NULL ' ||
3941 ' and ps.customer_site_use_id = cps.SITE_USE_ID (+) ' ||
3942 ' and nvl(cps.lockbox_matching_option, ' ||
3943 ' nvl(cpc.lockbox_matching_option, :b_lockbox_matching_option)) = ''INVOICE'' ' ||
3944 ' and ps.trx_date = decode(:b_use_matching_date, ''ALWAYS'', :b_current_matching_date, ' ||
3945 ' ps.trx_date) ';
3946 --
3947 -- Added distinct condition to the second and third select statements
3948 -- to fix bug 1209136. The first select always had the distinct condition
3949 -- when code was added for matching with sales order
3950 --
3951 -- Changed count(distinct customer_id) to count(distinct customer_trx_id).
3952 -- Also added a condition to check if the invoice is open.
3953 -- Both are required to fix bug 1629752.
3954
3955 -- Bug 2466415 : Changed the condition count(distinct customer_trx_id)
3956 -- to count(distinct rct.bill_to_customer_id), reverting part of the fix done
3957 -- for bug 1629752
3958
3959 -- Bug 2466415 : Changed the condition count(distinct customer_trx_id)
3960 -- to count(distinct rct.bill_to_customer_id), reverting part of the fix done
3961 -- for bug 1629752
3962
3963 ELSIF (g_matching_option = 'SALES_ORDER') THEN
3964 /* Constructing SELECT CLAUSE */
3965 g_cursor_string := 'select ';
3966 g_cursor_string := g_cursor_string ||
3967 ' decode(count(distinct rct.bill_to_customer_id), ' ||
3968 ' 0, -9999, ' || /* No such SO Num */
3969 ' 1, max(rct.bill_to_customer_id), ' ||
3970 ' decode(:b_use_matching_date,''NEVER'', -7777, ' || /* Multiple customers without matching date */
3971 ' ''ALWAYS'', -7777, ' || /* Multiple customerswith matching date */
3972 ' ''FOR_DUPLICATES'', decode(sum(decode(rctl.sales_order_date, :b_current_matching_date, 1, ' ||
3973 ' 0)), 1, ' ||
3974 ' max(decode(rctl.sales_order_date, :b_current_matching_date, rct.bill_to_customer_id, -7777)), ' ||
3975 ' -7777))), ';
3976 --
3977 g_cursor_string := g_cursor_string ||
3978 ' decode(count(distinct rct.bill_to_customer_id), ' ||
3979 ' 0, null, '; /* No such SO Num */
3980 IF g_include_closed_inv = 'Y' THEN
3981 g_cursor_string := g_cursor_string ||
3982 ' 1, decode(count(distinct ps.status), 1, decode(max(ps.status), ''CL'', '||
3983 ' (decode(max(rctt.allow_overapplication_flag), ''N'', ''-1111'', max(ps.trx_number))), max(ps.trx_number)), max(decode(ps.status, ''CL'', '''', ps.trx_number))), ';
3984 ELSE
3985 g_cursor_string := g_cursor_string ||
3986 ' 1, max(ps.trx_number), ';
3987 END IF;
3988 g_cursor_string := g_cursor_string ||
3989 ' decode(:b_use_matching_date,''NEVER'', null, ' || /* Multiple customers without matching date */
3990 ' ''ALWAYS'', null, ' || /* Multiple customerswith matching date */
3991 ' ''FOR_DUPLICATES'', decode(sum(decode(rctl.sales_order_date, :b_current_matching_date, 1, ' ||
3992 ' 0)), 1, ';
3993 IF g_include_closed_inv = 'Y' THEN
3994 g_cursor_string := g_cursor_string ||
3995 ' max(decode(rctl.sales_order_date, :b_current_matching_date, ' ||
3996 ' decode(ps.status, ''CL'', (decode(rctt.allow_overapplication_flag, ''N'', ''-1111'', '||
3997 ' ps.trx_number)), ps.trx_number), NULL )),';
3998 ELSE
3999 g_cursor_string := g_cursor_string ||
4000 ' max(decode(rctl.sales_order_date, :b_current_matching_date, ps.trx_number, NULL )),';
4001 END IF;
4002 g_cursor_string := g_cursor_string ||
4003 ' null))), ';
4004 --
4005 g_cursor_string := g_cursor_string ||
4006 ' decode(count(distinct rct.bill_to_customer_id), ' ||
4007 ' 0, null, ' || /* No such SO Num */
4008 ' 1, max(rct.trx_date), ' ||
4009 ' decode(:b_use_matching_date,''NEVER'', null, ' || /* Multiple customers without matching date */
4010 ' ''ALWAYS'', null, ' || /* Multiple customers with matching date */
4011 ' ''FOR_DUPLICATES'', decode(sum(decode(rctl.sales_order_date, :b_current_matching_date, 1, ' ||
4012 ' 0)), 1, ' ||
4013 ' max(decode(rctl.sales_order_date, :b_current_matching_date, rct.trx_date, null)), ' ||
4014 ' null))) ';
4015 --
4016 -- Constructing FROM clause
4017 g_cursor_string := g_cursor_string ||
4018 ' from ' ||
4019 ' ra_cust_trx_types rctt, ' ||
4020 ' hz_customer_profiles cpc, ' ||
4021 ' hz_customer_profiles cps, ' ||
4022 ' ar_payment_schedules ps, ' ||
4023 ' ra_customer_trx rct, ' ||
4024 ' ra_customer_trx_lines rctl ' ;
4025 --
4026 -- Constructing where clause
4027 g_cursor_string := g_cursor_string ||
4028 ' where ' ||
4029 ' rctl.sales_order = :b_current_matching_number ' ||
4030 ' and EXISTS ( select ''Exists dual'' from dual ' ||
4031 ' where decode(:b_pay_unrelated_customers, ''Y'', rct.bill_to_customer_id, ' ||
4032 ' nvl(:b_customer_id, rct.bill_to_customer_id)) = rct.bill_to_customer_id ' ||
4033 ' UNION ' ||
4034 ' select ''Exists ha_cust_acct_relate'' ' ||
4035 ' from hz_cust_acct_relate rel ' ||
4036 ' where rel.cust_account_id = :b_customer_id ' ||
4037 ' and rel.bill_to_flag = ''Y'' ' ||
4038 ' and rel.status = ''A'' ' ||
4039 ' UNION ' ||
4040 ' select ''Exists ar_paying_relationships_v'' ' ||
4041 ' from ar_paying_relationships_v rel,' ||
4042 ' hz_cust_accounts acc ' ||
4043 ' where acc.cust_account_id = :b_customer_id ' ||
4044 ' and acc.party_id = rel.party_id ' ||
4045 ' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
4046 ' AND effective_end_date ) ' ||
4047 ' and rctt.accounting_affect_flag = ''Y'' ' ||
4048 ' and rctl.customer_trx_id = rct.customer_trx_id ' ||
4049 ' and rct.cust_trx_type_id = rctt.cust_trx_type_id ' ||
4050 ' and rctt.type not in (''PMT'', ''GUAR'') ' ||
4051 ' and rct.complete_flag = ''Y'' ' ||
4052 ' and rct.bill_to_customer_id = cpc.cust_account_id ' ||
4053 ' and cpc.site_use_id is NULL ' ||
4054 ' and rct.bill_to_site_use_id = cps.site_use_id (+) ' ||
4055 ' and rct.customer_trx_id = ps.customer_trx_id ' ;
4056 IF g_include_closed_inv = 'N' THEN
4057 g_cursor_string := g_cursor_string ||
4058 ' and ps.status = decode(rctt.allow_overapplication_flag, ' ||
4059 ' ''N'', ''OP'', ps.status) ';
4060 END IF;
4061 g_cursor_string := g_cursor_string ||
4062 ' and nvl(cps.lockbox_matching_option, ' ||
4063 ' nvl(cpc.lockbox_matching_option, :b_lockbox_matching_option)) = ''SALES_ORDER'' ' ||
4064 ' and decode(rctl.sales_order_date, ' ||
4065 ' null, to_date(''01/01/1952'', ''MM/DD/YYYY''), rctl.sales_order_date) ' ||
4066 ' = decode(:b_use_matching_date, ''ALWAYS'', :b_current_matching_date, ' ||
4067 ' decode(rctl.sales_order_date, ' ||
4068 ' null, to_date(''01/01/1952'', ''MM/DD/YYYY''), ' ||
4069 ' rctl.sales_order_date)) ';
4070
4071 -- Added distinct condition to the second and third select statements
4072 -- to fix bug 1209136. The first select always had the distinct condition
4073 -- when code was added for matching with purchase order
4074 --
4075 -- Changed count(distinct customer_id) to count(distinct customer_trx_id).
4076 -- Also added a condition to check if the invoice is open.
4077 -- Both are required to fix bug 1629752.
4078
4079 ELSIF (g_matching_option = 'PURCHASE_ORDER') THEN
4080 -- Constructing SELECT clause
4081 g_cursor_string := 'select ';
4082 g_cursor_string := g_cursor_string ||
4083 ' decode(count(distinct rct.bill_to_customer_id), ' ||
4084 ' 0, -9999, ' || /* No such PO Num */
4085 ' 1, max(rct.bill_to_customer_id), ' ||
4086 ' decode(:b_use_matching_date,''NEVER'', -7777, ' || /* Multiple customers without matching date */
4087 ' ''ALWAYS'', -7777, ' || /* Multiple customers with matching date */
4088 ' ''FOR_DUPLICATES'', decode(sum(decode(rct.purchase_order_date, :b_current_matching_date, 1, ' ||
4089 ' 0)), 1, ' ||
4090 ' max(decode(rct.purchase_order_date, :b_current_matching_date, rct.bill_to_customer_id, -7777)), ' ||
4091 ' -7777 ))), ';
4092 --
4093 g_cursor_string := g_cursor_string ||
4094 ' decode(count(distinct rct.bill_to_customer_id), ' ||
4095 ' 0, null, '; /* No such PO Num */
4096 IF g_include_closed_inv = 'Y' THEN
4097 g_cursor_string := g_cursor_string ||
4098 ' 1, decode(count(distinct ps.status), 1, decode(max(ps.status), ''CL'', '||
4099 ' (decode(max(rctt.allow_overapplication_flag), ''N'', ''-1111'', max(ps.trx_number))), max(ps.trx_number)), max(decode(ps.status, ''CL'', '''', ps.trx_number))), ';
4100 ELSE
4101 g_cursor_string := g_cursor_string ||
4102 ' 1, max(ps.trx_number), ';
4103 END IF;
4104 g_cursor_string := g_cursor_string ||
4105 ' decode(:b_use_matching_date,''NEVER'', null, ' || /* Multiple customers without matching date */
4106 ' ''ALWAYS'', null, ' ||/* Multiple customerswith matching date */
4107 ' ''FOR_DUPLICATES'', decode(sum(decode(rct.purchase_order_date, :b_current_matching_date, 1, ' ||
4108 ' 0)), 1, ' ;
4109 IF g_include_closed_inv = 'Y' THEN
4110 g_cursor_string := g_cursor_string ||
4111 ' max(decode(rct.purchase_order_date, :b_current_matching_date, ' ||
4112 ' decode(ps.status, ''CL'', (decode(rctt.allow_overapplication_flag, ''N'', ''-1111'', '||
4113 ' ps.trx_number)), ps.trx_number), NULL )),';
4114 ELSE
4115 g_cursor_string := g_cursor_string ||
4116 ' max(decode(rct.purchase_order_date, :b_current_matching_date, ps.trx_number, NULL )),';
4117 END IF;
4118 g_cursor_string := g_cursor_string ||
4119 ' null))), ';
4120 --
4121 g_cursor_string := g_cursor_string ||
4122 ' decode(count(distinct rct.bill_to_customer_id), ' ||
4123 ' 0, null, ' || /* No such PO Num */
4124 ' 1, max(rct.trx_date), ' ||
4125 ' decode(:b_use_matching_date,''NEVER'', null, ' || /* Multiple customers without matching date */
4126 ' ''ALWAYS'', null, ' || /* Multiple customers with matching date */
4127 ' ''FOR_DUPLICATES'', decode(sum(decode(rct.purchase_order_date, :b_current_matching_date, 1, ' ||
4128 ' 0)), 1, ' ||
4129 ' max(decode(rct.purchase_order_date, :b_current_matching_date, rct.trx_date, null)), ' ||
4130 ' null))) ';
4131 --
4132 -- Constructing FROM clause
4133 g_cursor_string := g_cursor_string ||
4134 ' from ' ||
4135 ' ra_cust_trx_types rctt, ' ||
4136 ' hz_customer_profiles cpc, ' ||
4137 ' hz_customer_profiles cps, ' ||
4138 ' ar_payment_schedules ps, ' ||
4139 ' ra_customer_trx rct ' ;
4140 --
4141 -- Constructing WHERE clause
4142 g_cursor_string := g_cursor_string ||
4143 ' where ' ||
4144 ' rct.purchase_order = :b_current_matching_number ' ||
4145 ' and EXISTS ( select ''Exists dual'' from dual ' ||
4146 ' where decode(:b_pay_unrelated_customers, ''Y'', rct.bill_to_customer_id, ' ||
4147 ' nvl(:b_customer_id, rct.bill_to_customer_id)) = rct.bill_to_customer_id ' ||
4148 ' UNION ' ||
4149 ' select ''Exists ha_cust_acct_relate'' ' ||
4150 ' from hz_cust_acct_relate rel ' ||
4151 ' where rel.cust_account_id = :b_customer_id ' ||
4152 ' and rel.bill_to_flag = ''Y'' ' ||
4153 ' and rel.status = ''A'' ' ||
4154 ' UNION ' ||
4155 ' select ''Exists ar_paying_relationships_v'' ' ||
4156 ' from ar_paying_relationships_v rel,' ||
4157 ' hz_cust_accounts acc ' ||
4158 ' where acc.cust_account_id = :b_customer_id ' ||
4159 ' and acc.party_id = rel.party_id ' ||
4160 ' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
4161 ' AND effective_end_date ) ' ||
4162 ' and rctt.accounting_affect_flag = ''Y'' ' ||
4163 ' and rct.cust_trx_type_id = rctt.cust_trx_type_id ' ||
4164 ' and rctt.type not in (''PMT'', ''GUAR'') ' ||
4165 ' and rct.complete_flag = ''Y'' ' ||
4166 ' and rct.bill_to_customer_id = cpc.cust_account_id ' ||
4167 ' and cpc.site_use_id is NULL ' ||
4168 ' and rct.bill_to_site_use_id = cps.site_use_id (+) ' ||
4169 ' and rct.customer_trx_id = ps.customer_trx_id ';
4170 IF g_include_closed_inv = 'N' THEN
4171 g_cursor_string := g_cursor_string ||
4172 ' and ps.status = decode(rctt.allow_overapplication_flag, ' ||
4173 ' ''N'', ''OP'', ps.status) ';
4174 END IF;
4175 g_cursor_string := g_cursor_string ||
4176 ' and nvl(cps.lockbox_matching_option, ' ||
4177 ' nvl(cpc.lockbox_matching_option, :b_lockbox_matching_option)) = ''PURCHASE_ORDER'' ' ||
4178 ' and decode(rct.purchase_order_date, ' ||
4179 ' null, to_date(''01/01/1952'', ''MM/DD/YYYY''), rct.purchase_order_date) ' ||
4180 ' = decode(:b_use_matching_date, ''ALWAYS'', :b_current_matching_date, ' ||
4181 ' decode(rct.purchase_order_date, ' ||
4182 ' null, to_date(''01/01/1952'', ''MM/DD/YYYY''), ' ||
4183 ' rct.purchase_order_date)) ';
4184 --
4185 ELSIF (g_matching_option = 'CONSOLIDATE_BILL') THEN
4186 --
4187 --
4188 -- Constructing SELECT clause
4189 g_cursor_string := 'select ';
4190 g_cursor_string := g_cursor_string ||
4191 ' decode(count(distinct ci.customer_id), ' ||
4192 ' 0, -9999, ' || /* No such consolidated billing num */
4193 ' 1, max(ci.customer_id), ' ||
4194 ' decode(:b_use_matching_date, ''NEVER '', -7777, ' || /* Multiple customers without matching date */
4195 ' ''ALWAYS '', -7777, ' || /* Multiple customers with matching date */
4196 ' ''FOR_DUPLICATES '', decode(sum(decode(ci.issue_date, :b_current_matching_date, 1, ' ||
4197 ' 0)), 1, ' ||
4198 ' max(decode(ci.issue_date, :b_current_matching_date, ci.customer_id, -7777)), ' ||
4199 ' -7777))), ' ||
4200 ' null, ' || /* No trx num information for match on consolidated bill */
4201 ' null '; /* No trx date information for match on consolidated bill */
4202 --
4203 -- Constructing FROM clause
4204 g_cursor_string := g_cursor_string ||
4205 ' from ' ||
4206 ' hz_customer_profiles cpc, ' ||
4207 ' hz_customer_profiles cps, ' ||
4208 ' ar_cons_inv ci ';
4209 --
4210 -- Constructing WHERE clause
4211 g_cursor_string := g_cursor_string ||
4212 ' where ' ||
4213 ' ci.cons_billing_number = :b_current_matching_number ' ||
4214 ' and EXISTS ( select ''Exists dual'' from dual ' ||
4215 ' where decode(:b_pay_unrelated_customers, ''Y'', ci.customer_id, ' ||
4216 ' nvl(:b_customer_id, ci.customer_id)) = ci.customer_id ' ||
4217 ' UNION ' ||
4218 ' select ''Exists ha_cust_acct_relate'' ' ||
4219 ' from hz_cust_acct_relate rel ' ||
4220 ' where rel.cust_account_id = :b_customer_id ' ||
4221 ' and rel.bill_to_flag = ''Y'' ' ||
4222 ' and rel.status = ''A'' ' ||
4223 ' UNION ' ||
4224 ' select ''Exists ar_paying_relationships_v'' ' ||
4225 ' from ar_paying_relationships_v rel,' ||
4226 ' hz_cust_accounts acc ' ||
4227 ' where acc.cust_account_id = :b_customer_id ' ||
4228 ' and acc.party_id = rel.party_id ' ||
4229 ' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
4230 ' AND effective_end_date ) ' ||
4231 ' and ci.customer_id = cpc.cust_account_id ' ||
4232 ' and cpc.site_use_id is NULL ' ||
4233 ' and ci.site_use_id = cps.site_use_id (+) ' ||
4234 ' and nvl(cps.lockbox_matching_option, ' ||
4235 ' nvl(cpc.lockbox_matching_option, :b_lockbox_matching_option)) = ''CONSOLIDATE_BILL'' ' ||
4236 ' and trunc(ci.issue_date) ' ||
4237 ' = decode(:b_use_matching_date, ''ALWAYS'', :b_current_matching_date, trunc(ci.issue_date)) ';
4238 ELSE /* Custom option */
4239 arp_lockbox_hook.cursor_for_matching_rule(p_matching_option=>g_matching_option,
4240 p_cursor_string=>l_cursor_string);
4241 -- Bug 2045569. Have to set g_cursor_string.
4242 g_cursor_string := l_cursor_string;
4243 null;
4244 END IF;
4245 --
4246 debug1('Now Opening the cursor..');
4247 g_cursor_name := dbms_sql.open_cursor;
4248 debug1(g_cursor_string);
4249 debug1('Opened the cursor.. Now Parsing cursor..');
4250 dbms_sql.parse(g_cursor_name, g_cursor_string, dbms_sql.NATIVE);
4251 debug1('Parsed cursor.');
4252 g_total_maching_options := g_total_maching_options + 1;
4253 -- Insert into PL/SQL table here values of matching_option, cursor_name
4254 --
4255 debug1('Inserting values into PL/SQL Table for index ' || to_char(g_total_maching_options));
4256 debug1('Cursor Name is ' || to_char(g_cursor_name));
4257 opened_cursor_table(g_total_maching_options).option_name := g_matching_option;
4258 opened_cursor_table(g_total_maching_options).cursor_name := g_cursor_name;
4259 END LOOP;
4260 --
4261 --
4262 debug1('Common code for arp_process_lockbox -');
4263 END; -- Common First-Time-Only code.
4264 --
4265 -- END arp_process_lockbox;