DBA Data[Home] [Help]

PACKAGE BODY: APPS.ARP_PROCESS_LOCKBOX

Source


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