Merged Vendors Show Twice on Trial Balance Report after 11i Implementation (Doc ID 356344.1)

Last updated on SEPTEMBER 06, 2017

Applies to:

Oracle Payables - Version 11.5.1 to 11.5.1 [Release 11.5]
Information in this document applies to any platform.
This problem can occur on any platform.
REPORT:APXTRBAL.RDF - Accounts Payable Trial Balance

Symptoms

Paid invoices show up on the trial balance twice, under two different vendors. The sum of these entries
nets to 0 so the total of the Trial Balance is correct but each vendor entry is not.

If any of the following scripts return any rows, you may be experiencing this issue.

select c.check_id
, c.vendor_id
, i.vendor_id
, c.vendor_site_id
, i.vendor_site_id
, count(distinct p.invoice_payment_id)
, count(distinct i.invoice_id)
from ap_checks_all c
, ap_invoice_payments_all p
, ap_invoices_all i
where c.check_id = p.check_id
and p.invoice_id = i.invoice_id
and (c.vendor_id != i.vendor_id
or c.vendor_site_id != i.vendor_site_id)
group by c.check_id
, c.vendor_id
, i.vendor_id
, c.vendor_site_id
, i.vendor_site_id
order by c.check_id
, i.vendor_id;

 

select distinct l.reference3 check_id
from ap_ae_lines_all l
, ap_checks_all c
where l.source_table in ('AP_CHECKS', 'AP_INVOICE_PAYMENTS')
and to_number(l.reference3) = c.check_id
and l.third_party_id != c.vendor_id;

select distinct l.reference2 invoice_id
from ap_ae_lines_all l
, ap_invoices_all i
where l.source_table in ('AP_INVOICE_DISTRIBUTIONS', 'AP_INVOICES')
and to_number(l.reference2) = i.invoice_id
and l.third_party_id != i.vendor_id;

select distinct c3.check_id
from ap_checks_all c3
, ap_invoice_payments_all p3
, ap_invoices_all i3
where c3.check_id = p3.check_id
and p3.invoice_id = i3.invoice_id
and c3.vendor_id != i3.vendor_id
MINUS
select c.check_id
from ap_checks_all c
, ap_invoice_payments_all p
, ap_invoices_all i
where i.invoice_id = p.invoice_id
and p.check_id = c.check_id
and i.vendor_id != c.vendor_id
and exists (
select i2.invoice_id
from ap_invoice_payments_all p2
, ap_invoices_all i2
where p2.invoice_id = i2.invoice_id
and p2.check_id = c.check_id
and i2.vendor_id = c.vendor_id)
and exists
(select 'a payment and an non-upgraded payment adjustment'
from ap_accounting_events_all e
, ap_accounting_events_all e2
, ap_ae_headers_all h
where e.source_id = c.check_id
and e.accounting_event_id = h.accounting_event_id (+)
and e.event_type_code = 'PAYMENT ADJUSTMENT'
and e2.source_id = c.check_id
and e2.event_type_code = 'PAYMENT'
and nvl(h.description, 'not upgraded') != 'R11.5 Upgrade');

 

Changes

This issue typically occurs because of a data conversion after an upgrade to 11i from 10.7 or 11.0.3.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms