[Home] [Help]
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,
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(
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,
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,
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,
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);
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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: --
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')
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: --
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,
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,
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
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'),
515: RETURN;
516: /* Bug 2106408. Added the following UPDATE */
517: <
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'),
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.
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,
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,
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,
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,
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);
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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: --
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: --
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')
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: --
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,
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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
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
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: --
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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;
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;
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;
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;
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: --
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;
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;
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;
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
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
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);
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;
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;
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;
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);
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
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,
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
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
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
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
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
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
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
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
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
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'
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
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: )
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
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:
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,
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,
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