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