Tips to work on the payment accounting error
There are majorly two reasons for failure of payment accounting:
1) Related invoice is not acounted completely
2) Previous payment accounting not accounted completely
Related invoice is not accounted completely
There are following most occouring cases:
* Upgraded invoices do not have complete or appropriate data in XLA_DISTRIBUTION_LINKS and payment time bflow will fail to get upstream.
* Matching records for invoice_distribution_id available in AP_INVOICE_DISTRIBUTIONS_ALL are not present in source_distribution_id_num_1 in XLA_DISTRIBUTION_LINKS.
* Some rows in ap_invoice_distributions_all are not accounted at all
* Invoice is having DIST_VARIANCE hold due to tax reversal have not generated properly.
* For upgrade invoice is on DIST VARIANCE hold because Tax reversal is in different invoice line and line amonut is set to 0

Tips to work in such cases:

1)Run Following Query:
select distinct aid.invoice_id from ap_invoice_distributions_all aid
,xla_ae_headers xah,xla_ae_lines xal
where aid.invoice_id in ( select distinct invoice_id
from ap_invoice_payments_all
where check_id = &check_id )
and aid.accounting_event_id = xah.event_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id =200
and xal.application_id =200
and not exists
( select 1 from xla_distribution_links xdl
where xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and aid.accounting_event_id = xdl.event_id
and xdl.application_id =200);

This query is returning result in case of upgraded data having no/partial records in XlA_DSITRIBUTION_LINKS.
If this query returns result, check the data in XLA_AE_LINES and XLA_DISTRIBUTION_LINKS.

a)In case of upgraded data if accounting_class_code in XLA_AE_LINES is ’CHARGE’, ’RECOVERABLE TAX’ or ’NONRECOVERABLE TAX’, raise an datafix SR.
b)In canse of upgraded data, if BUSINESS_CLASS_CODE in XLA_AE_LINES is null for ACCOUNTING_CLASS_CODE ’LIABILITY’, and UPG_BATCH_ID is –5672 or it is null , raise an datafix SR.

c) In case of Upgraded Data, if ’SOURCE_ID’ and ’SOURCE_TABLE’ is null and UPG_BATCH_ID is –5672, raise an datafix SR.

SOURCE ID is invoice_distribution_id or invoice_id on the basis of ACCOUNTING_CLASS_CODE .SOURCE_TABLE is ’AP_INVOICE_DISTRIBUTIONS’ or ’AP_INVOICES’ on the basis of ACCOUNTING_CLASS_CODE.

2)Run Following query:

select distinct invoice_id ,
accounting_event_id,line_type_lookup_code,
amount,invoice_distribution_id,parent_reversal_id,
charge_applicable_to_dist_id,summary_tax_line_id
from ap_invoice_distributions_all aid
where aid.invoice_id in
(select invoice_id from ap_invoice_payments_all where check_id = &check_id )
and not exists
(select 1 from xla_distribution_links xdl
where xdl.event_id = aid.accounting_event_id
and xdl.source_distribution_id_num_1 = aid.invoice_distribution_id)
order by invoice_id;

This query will return the result in case of there is any mismatch between AP_INVOICE_DISTRIBUTIONS_ALL and XLA_DISTRIBUTION_LINKS.


a)If above query returning any data then check invoice_distibution_id and source_distributions_id_num_1. If there is some distributions unaccounted till now. Try to account them. If invoice is having any kind of variance, release the variance and try to complete accounting.

b) There may be some case where source_distribution_id_num_1 is null or have any data that is not available in AP_INVOICE_DISTRIBUTIONS_ALL as invoice_distribution_id. To resolve it run undo accounting and account it once again.

c) Check data for applied_to columns in XLA_DISTRIBUTION_LINKS, if it is not populated, raise an datafix SR.

Note: This query will return you data for AWT lines if there is payment time AWT. Please ignore them.

3) Run Following Query:

Select ail.line_number,ail.line_type_lookup_code,ail.amount,sum(aid.amount)
From ap_invoice_lines_all ail,ap_invoice_distributions_all aid
Where ail.invoice_id = aid.invoice_id
and ail.invoice_id = &invoice_id
and ail.line_number = aid.invoice_line_number
having ail.amount <> sum(aid.amount)
group by ail.line_number,ail.line_type_lookup_code,ail.amount

This Query will return you data in case of Dist_variance on invoice.

Solution :

* If line_type_lookup_code is ’TAX’ and historical_flag is ’Y’ then raise an datafix SR with output of above query.
* Confirm that there is no orphan distributions, it may be possible that required distributions are available as orphan. If it is so then please use orphan record removal GDF provided by Ebiz AP.

4)

Run Following Query:

Select * from ap_invoice_distributions_all aid

Where aid.invoice_id = &invoice_id
and not exists
(select 1 from ap_invoice_lines_all ail
where ail.invoice_id = aid.invoice_id
and ail.line_number = aid.line_number)

This query will return you result in case of orphan distributions.

Solution:

* Analyze the invoice, if there is any way to attach it back to invoice because of missing link, attach it.
* If it is not possible to attach it back to invoice and you want to delete them, confirm you are deleting accounting side data as well if associated.
* There is one Select and fix GDF available for invoice orphans. Please refer them for further details.

Previous payment accounting not accounted completely

Following are some common cases

* Previous payment accounting data not generated/upgraded successfully
* Accounting date is not in open period
* Bflow data is not correct.
* There is more than one payment_created transaction_type in AP_PAYMENT_HISTORY_ALL
* Related invoice_accounting do not have LIABILITY Line in XLA_AE_LINES
* AP_INVOICE_PAYMENTS_ALL does not have accounting_event_id in case of upgraded data
* PAYMENT_UNCLEARING event is not getting accounted.

Tips to work in such cases:

1)Run following Query :

select distinct ac.check_id
from ap_payment_history_all aph, ap_checks_all ac
where ac.org_id=&org_id
and ac.check_date >
’&approx_date_check_created’
and aph.check_id=ac.check_id
and aph.transaction_type like ’PAYMENT CREATED’
and nvl(aph.historical_flag,’N’)
=’Y’
and not exists
(select 1 from ap_payment_hist_dists aphd
where aphd.payment_history_id=aph.payment_history_id)

This query will return you the checks not having data in AP_PAYMENT_HIST_DISTS.

Solution:

There is one GDF available to populate AP_PAYMENT_HIST_DIST and XLA_DISTRIBUTION_LINKS. Please use it to populate the data

2)Run Following Query:

select aph.*
from ap_payment_history_all aph
where aph.transaction_type = ’PAYMENT CREATED’
and exists (select 1
from ap_accounting_events_all aae
where aae.accounting_event_id=aph.accounting_event_id
and aae.ax_accounted_flag=’Y’);

If above query returns any data, it means there is multiple PAYMENT_CREATED event because of AX Accounting.
Solution

Delete this data after checking if it is not accounted (it should not be in accounted status)

3) For distributions in AP_NIVOICE_DISTRIBUTIONS_ALL having 0 amount and there is no data in XLA_AE_LINES and XLA_DISTRIBUTION_LINKS.(AX Upgraded Data)

4)

While running upgrade script for payment, No data gets generated for ap_payment_hist_dist.

Solution:

Check if Accounting_evnet_id in AP_INVOICE_PAYMENTS_ALL is null. If it is so then populatye it with accounting_evnet_id of ’PAYMENT_CREATED" transaction type of ap_payment_history_all .

Check if recon_accounting_flag is ’Y’ where XLA_AE_LINES have CASH lines in place of CASH_CLEARING accounting_class_code .

Check if Source_id and Source_table is null.

5) PAYMENT_UNCLEARING trnasaction type in AP_PAYMENT_HISTORY_ALL is not getting accounted where PAYMENT_CANCELED is already accounted.

This Transaction is not necessary to get accounted as payment is already gets cancelled.

Set event_stastus_code as ’N’ process_status_code as ’P’ in XLA_EVENTS and set posted_flag as ’P’ in AP_PAYMENT_HISTORY_ALL.