[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| invoice1_status was updated wrongly.
| 10/23/2001 Shin Matsuda There are some other places where it updates
| invoice1_status instead of invoiceN_status.
| Corrected all of them.
| 10/31/2001 Shin Matsuda Bug 2066392. Populate amount_appliedN with
| amount_applied_fromN if single currency
| application.
| 11/21/2001 Shin Matsuda Bug 2057282. Validate/populate currency
| and amount info only if l_tot_trxN is 1.
| 11/23/2001 Shin Matsuda Bug 2119596. Backed out NOCOPY the previous fix.
| Put exception clause for no_data_found case.
| 10/28/2002 Shin Matsuda Bug 2626005. Modified where clause of most
| update statements. We validate CC info row
| by row and we know rowid of the record, thus
| the where clause can be simple rowid=l_rowid
| 05/07/2003 Rahna Kader Bug 2926664.Modified the select statement
| which is used for setting AR_PLB_DUP_INV.
| Also modified the select statement which selects
| the invoice currency code and amount due
| remaining.
| 08/14/2003 H Yoshihara Bug 2980051. Replaced l_only_one_lb with
| l_no_batch_or_lb in get_app_info cursor
| to identify record correctly.
| 22/10/2003 SAPN Sarma Bug 3113104. Replaced the get_rate function with
| get_rate_sql function.Also, made a check for the
| exchange_rate_value that is returned.
+---------------------------------------------------------------------------*/
PROCEDURE populate_add_inv_details(
p_transmission_id IN VARCHAR2,
p_payment_rec_type IN VARCHAR2,
p_overflow_rec_type IN VARCHAR2,
p_item_num IN ar_payments_interface.item_number%type,
p_batch_name IN ar_payments_interface.batch_name%type,
p_lockbox_number IN ar_payments_interface.lockbox_number%type,
p_batches IN VARCHAR2,
p_only_one_lb IN VARCHAR2,
p_pay_unrelated_invoices IN VARCHAR2,
p_default_exchange_rate_type IN VARCHAR2,
enable_cross_currency IN VARCHAR2,
p_format_amount1 IN VARCHAR,
p_format_amount2 IN VARCHAR,
p_format_amount3 IN VARCHAR,
p_format_amount4 IN VARCHAR,
p_format_amount5 IN VARCHAR,
p_format_amount6 IN VARCHAR,
p_format_amount7 IN VARCHAR,
p_format_amount8 IN VARCHAR,
p_format_amount_applied_from1 IN VARCHAR,
p_format_amount_applied_from2 IN VARCHAR,
p_format_amount_applied_from3 IN VARCHAR,
p_format_amount_applied_from4 IN VARCHAR,
p_format_amount_applied_from5 IN VARCHAR,
p_format_amount_applied_from6 IN VARCHAR,
p_format_amount_applied_from7 IN VARCHAR,
p_format_amount_applied_from8 IN VARCHAR
) IS
--
l_transmission_id VARCHAR2(50);
select list */
/* Bug 1513671: modified cursor because amount_applied was being rounded
to precision of receipt currency */
/* Bug 2980051: Replaced l_only_one_lb with l_no_batch_or_lb */
CURSOR get_app_info IS
select
pi.rowid,
pi.resolved_matching_number1,
pi.resolved_matching1_date,
pi.resolved_matching1_installment,
pi.customer_id,
pi.trans_to_receipt_rate1,
pi.invoice_currency_code1,
pi.amount_applied1,
decode(l_format_amount_applied_from1,'Y',
round(pi.amount_applied_from1/power(10,fc.precision),
fc.precision),
pi.amount_applied_from1),
pi.resolved_matching_number2,
pi.resolved_matching2_date,
pi.resolved_matching2_installment,
pi.trans_to_receipt_rate2,
pi.invoice_currency_code2,
pi.amount_applied2,
decode(l_format_amount_applied_from2,'Y',
round(pi.amount_applied_from2/power(10,fc.precision),
fc.precision),
pi.amount_applied_from2),
pi.resolved_matching_number3,
pi.resolved_matching3_date,
pi.resolved_matching3_installment,
pi.trans_to_receipt_rate3,
pi.invoice_currency_code3,
pi.amount_applied3,
decode(l_format_amount_applied_from3,'Y',
round(pi.amount_applied_from3/power(10,fc.precision),
fc.precision),
pi.amount_applied_from3),
pi.resolved_matching_number4,
pi.resolved_matching4_date,
pi.resolved_matching4_installment,
pi.trans_to_receipt_rate4,
pi.invoice_currency_code4,
pi.amount_applied4,
decode(l_format_amount_applied_from4,'Y',
round(pi.amount_applied_from4/power(10,fc.precision),
fc.precision),
pi.amount_applied_from4),
pi.resolved_matching_number5,
pi.resolved_matching5_date,
pi.resolved_matching5_installment,
pi.trans_to_receipt_rate5,
pi.invoice_currency_code5,
pi.amount_applied5,
decode(l_format_amount_applied_from5,'Y',
round(pi.amount_applied_from5/power(10,fc.precision),
fc.precision),
pi.amount_applied_from5),
pi.resolved_matching_number6,
pi.resolved_matching6_date,
pi.resolved_matching6_installment,
pi.trans_to_receipt_rate6,
pi.invoice_currency_code6,
pi.amount_applied6,
decode(l_format_amount_applied_from6,'Y',
round(pi.amount_applied_from6/power(10,fc.precision),
fc.precision),
pi.amount_applied_from6),
pi.resolved_matching_number7,
pi.resolved_matching7_date,
pi.resolved_matching7_installment,
pi.trans_to_receipt_rate7,
pi.invoice_currency_code7,
pi.amount_applied7,
decode(l_format_amount_applied_from7,'Y',
round(pi.amount_applied_from7/power(10,fc.precision),
fc.precision),
pi.amount_applied_from7),
pi.resolved_matching_number8,
pi.resolved_matching8_date,
pi.resolved_matching8_installment,
pi.trans_to_receipt_rate8,
pi.invoice_currency_code8,
pi.amount_applied8,
decode(l_format_amount_applied_from8,'Y',
round(pi.amount_applied_from8/power(10,fc.precision),
fc.precision),
pi.amount_applied_from8),
pi.currency_code, /* currency code of the receipt */
pi.receipt_date
from ar_payments_interface pi, fnd_currencies fc
where pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
and pi.item_number = l_item_num
and pi.currency_code = fc.currency_code
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
SELECT receipt_date
INTO l_receipt_date
FROM ar_payments_interface pi
WHERE pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type )
and pi.item_number = l_item_num
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx1
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number1
AND ps.trx_date = l_resolved_matching1_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice1_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code1,
trx_amt_due_rem1
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number1
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number1
and ps.trx_date = l_resolved_matching1_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status))
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code1 = l_invoice_currency_code1
WHERE rowid = l_rowid ;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid ;
UPDATE ar_payments_interface
SET trans_to_receipt_rate1 =
l_trans_to_receipt_rate1
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate1 =
l_trans_to_receipt_rate1
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate1 =
l_trans_to_receipt_rate1
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from1 =
l_amount_applied_from1
where rowid = l_rowid;
update ar_payments_interface
set amount_applied1 =
l_amount_applied1
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from1 =
l_amount_applied_from1
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied1 =
l_amount_applied1
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice1_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied1 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx2
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number2
AND ps.trx_date = l_resolved_matching2_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice2_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code2,
trx_amt_due_rem2
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number2
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number2
and ps.trx_date = l_resolved_matching2_date /* Bug fix 2926664 */
and (EXISTS /* subquery */
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N' , 'OP',
ps.status))
and (EXISTS /* main query */
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
UPDATE ar_payments_interface pi
SET pi.invoice_currency_code2 = l_invoice_currency_code2
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate2 =
l_trans_to_receipt_rate2
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate2 =
l_trans_to_receipt_rate2
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate2 =
l_trans_to_receipt_rate2
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET amount_applied_from2 =
l_amount_applied_from2
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET amount_applied2 =
l_amount_applied2
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET amount_applied_from2 =
l_amount_applied_from2
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET amount_applied2 =
l_amount_applied2
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice2_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied2 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx3
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number3
AND ps.trx_date = l_resolved_matching3_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice3_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code3,
trx_amt_due_rem3
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number3
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number3
and ps.trx_date = l_resolved_matching3_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N','OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code3 = l_invoice_currency_code3
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate3 =
l_trans_to_receipt_rate3
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate3 =
l_trans_to_receipt_rate3
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate3 =
l_trans_to_receipt_rate3
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from3 =
l_amount_applied_from3
where rowid = l_rowid;
update ar_payments_interface
set amount_applied3 =
l_amount_applied3
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from3 =
l_amount_applied_from3
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied3 =
l_amount_applied3
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice3_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied3 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx4
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number4
AND ps.trx_date = l_resolved_matching4_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice4_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code4,
trx_amt_due_rem4
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number4
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number4
and ps.trx_date = l_resolved_matching4_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N' , 'OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code4 = l_invoice_currency_code4
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate4 =
l_trans_to_receipt_rate4
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate4 =
l_trans_to_receipt_rate4
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate4 =
l_trans_to_receipt_rate4
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from4 =
l_amount_applied_from4
where rowid = l_rowid;
update ar_payments_interface
set amount_applied4 =
l_amount_applied4
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from4 =
l_amount_applied_from4
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied4 =
l_amount_applied4
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice4_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied4 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx5
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number5
AND ps.trx_date = l_resolved_matching5_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice5_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code5,
trx_amt_due_rem5
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number5
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number5
and ps.trx_date = l_resolved_matching5_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code5 = l_invoice_currency_code5
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate5 =
l_trans_to_receipt_rate5
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate5 =
l_trans_to_receipt_rate5
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate5 =
l_trans_to_receipt_rate5
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from5 =
l_amount_applied_from5
where rowid = l_rowid;
update ar_payments_interface
set amount_applied5 =
l_amount_applied5
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from5 =
l_amount_applied_from5
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied5 =
l_amount_applied5
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice5_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied5 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx6
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number6
AND ps.trx_date = l_resolved_matching6_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice6_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code6,
trx_amt_due_rem6
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number6
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number6
and ps.trx_date = l_resolved_matching6_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code6 = l_invoice_currency_code6
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate6 =
l_trans_to_receipt_rate6
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate6 =
l_trans_to_receipt_rate6
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate6 =
l_trans_to_receipt_rate6
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from6 =
l_amount_applied_from6
where rowid = l_rowid;
update ar_payments_interface
set amount_applied6 =
l_amount_applied6
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from6 =
l_amount_applied_from6
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied6 =
l_amount_applied6
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice6_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied6 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx7
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number7
AND ps.trx_date = l_resolved_matching7_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice7_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code7,
trx_amt_due_rem7
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number7
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number7
and ps.trx_date = l_resolved_matching7_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N' , 'OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code7 = l_invoice_currency_code7
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate7 =
l_trans_to_receipt_rate7
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate7 =
l_trans_to_receipt_rate7
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate7 =
l_trans_to_receipt_rate7
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from7 =
l_amount_applied_from7
where rowid = l_rowid;
update ar_payments_interface
set amount_applied7 =
l_amount_applied7
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from7 =
l_amount_applied_from7
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied7 =
l_amount_applied7
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice7_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied7 = l_unformat_amount
WHERE rowid = l_rowid;
SELECT sum(count(distinct ps.customer_trx_id))
INTO l_tot_trx8
FROM ar_payment_schedules ps
WHERE ps.trx_number = l_matching_number8
AND ps.trx_date = l_resolved_matching8_date /* Bug fix 2926664 */
AND (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
GROUP BY ps.customer_trx_id
HAVING sum(ps.amount_due_remaining) <> 0;
update ar_payments_interface pi
set invoice8_status = 'AR_PLB_DUP_INV'
where rowid = l_rowid;
SELECT invoice_currency_code,
amount_due_remaining
INTO ps_currency_code8,
trx_amt_due_rem8
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_matching_number8
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND ps.payment_schedule_id =
(select min(ps.payment_schedule_id)
from ar_payment_schedules ps,
ra_cust_trx_types tt
where ps.trx_number = l_matching_number8
and ps.trx_date = l_resolved_matching8_date /* Bug fix 2926664 */
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
and ps.cust_trx_type_id = tt.cust_trx_type_id
and ps.class NOT IN ('PMT','GUAR')
and ps.status=decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status))
and (EXISTS
(
select 'Exists from dual' from dual
where l_customer_id = ps.customer_id
union
select 'Exists from hz_cust_acct_relate'
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and related_cust_account_id = ps.customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select 'Exists ar_paying_relationships_v'
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
and rel.related_cust_account_id = ps.customer_id
and l_receipt_date BETWEEN effective_start_date
AND effective_end_date
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
/* update ar_payment_interface to have the invoice currency_code */
UPDATE ar_payments_interface
SET invoice_currency_code8 = l_invoice_currency_code8
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_CURRENCY_BAD'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate8 =
l_trans_to_receipt_rate8
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status =
'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate8 =
l_trans_to_receipt_rate8
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
UPDATE ar_payments_interface
SET trans_to_receipt_rate8 =
l_trans_to_receipt_rate8
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from8 =
l_amount_applied_from8
where rowid = l_rowid;
update ar_payments_interface
set amount_applied8 =
l_amount_applied8
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_NO_EXCHANGE_RATE'
WHERE rowid = l_rowid;
update ar_payments_interface
set amount_applied_from8 =
l_amount_applied_from8
where rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied8 =
l_amount_applied8
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_CURR_CONFLICT'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET invoice8_status = 'AR_PLB_CC_INVALID_VALUE'
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE
SET amount_applied8 = l_unformat_amount
WHERE rowid = l_rowid;
update ar_payments_interface pi
set tmp_amt_applied_from1 = pi.amount_applied_from1,
tmp_amt_applied_from2 = pi.amount_applied_from2,
tmp_amt_applied_from3 = pi.amount_applied_from3,
tmp_amt_applied_from4 = pi.amount_applied_from4,
tmp_amt_applied_from5 = pi.amount_applied_from5,
tmp_amt_applied_from6 = pi.amount_applied_from6,
tmp_amt_applied_from7 = pi.amount_applied_from7,
tmp_amt_applied_from8 = pi.amount_applied_from8,
tmp_amt_applied1 = pi.amount_applied1,
tmp_amt_applied2 = pi.amount_applied2,
tmp_amt_applied3 = pi.amount_applied3,
tmp_amt_applied4 = pi.amount_applied4,
tmp_amt_applied5 = pi.amount_applied5,
tmp_amt_applied6 = pi.amount_applied6,
tmp_amt_applied7 = pi.amount_applied7,
tmp_amt_applied8 = pi.amount_applied8,
tmp_inv_currency_code1 = pi.invoice_currency_code1,
tmp_inv_currency_code2 = pi.invoice_currency_code2,
tmp_inv_currency_code3 = pi.invoice_currency_code3,
tmp_inv_currency_code4 = pi.invoice_currency_code4,
tmp_inv_currency_code5 = pi.invoice_currency_code5,
tmp_inv_currency_code6 = pi.invoice_currency_code6,
tmp_inv_currency_code7 = pi.invoice_currency_code7,
tmp_inv_currency_code8 = pi.invoice_currency_code8,
tmp_trans_to_rcpt_rate1 = pi.trans_to_receipt_rate1,
tmp_trans_to_rcpt_rate2 = pi.trans_to_receipt_rate2,
tmp_trans_to_rcpt_rate3 = pi.trans_to_receipt_rate3,
tmp_trans_to_rcpt_rate4 = pi.trans_to_receipt_rate4,
tmp_trans_to_rcpt_rate5 = pi.trans_to_receipt_rate5,
tmp_trans_to_rcpt_rate6 = pi.trans_to_receipt_rate6,
tmp_trans_to_rcpt_rate7 = pi.trans_to_receipt_rate7,
tmp_trans_to_rcpt_rate8 = pi.trans_to_receipt_rate8;
update ar_payments_interface
set amount_applied_from1 = tmp_amt_applied_from1,
amount_applied_from2 = tmp_amt_applied_from2,
amount_applied_from3 = tmp_amt_applied_from3,
amount_applied_from4 = tmp_amt_applied_from4,
amount_applied_from5 = tmp_amt_applied_from5,
amount_applied_from6 = tmp_amt_applied_from6,
amount_applied_from7 = tmp_amt_applied_from7,
amount_applied_from8 = tmp_amt_applied_from8,
amount_applied1 = tmp_amt_applied1,
amount_applied2 = tmp_amt_applied2,
amount_applied3 = tmp_amt_applied3,
amount_applied4 = tmp_amt_applied4,
amount_applied5 = tmp_amt_applied5,
amount_applied6 = tmp_amt_applied6,
amount_applied7 = tmp_amt_applied7,
amount_applied8 = tmp_amt_applied8,
invoice_currency_code1 = tmp_inv_currency_code1,
invoice_currency_code2 = tmp_inv_currency_code2,
invoice_currency_code3 = tmp_inv_currency_code3,
invoice_currency_code4 = tmp_inv_currency_code4,
invoice_currency_code5 = tmp_inv_currency_code5,
invoice_currency_code6 = tmp_inv_currency_code6,
invoice_currency_code7 = tmp_inv_currency_code7,
invoice_currency_code8 = tmp_inv_currency_code8,
trans_to_receipt_rate1 = tmp_trans_to_rcpt_rate1,
trans_to_receipt_rate2 = tmp_trans_to_rcpt_rate2,
trans_to_receipt_rate3 = tmp_trans_to_rcpt_rate3,
trans_to_receipt_rate4 = tmp_trans_to_rcpt_rate4,
trans_to_receipt_rate5 = tmp_trans_to_rcpt_rate5,
trans_to_receipt_rate6 = tmp_trans_to_rcpt_rate6,
trans_to_receipt_rate7 = tmp_trans_to_rcpt_rate7,
trans_to_receipt_rate8 = tmp_trans_to_rcpt_rate8
where status <> 'AR_PLB_TRANSFERRED';