ap to gl reco

AP Does Not Balance to GL [ID 344367.1]

Modified 12-AUG-2009 Type TROUBLESHOOTING Status PUBLISHED

In this Document

Purpose

Reconciliation Process

Troubleshooting

Opening a Service Request

References

@ (AuthWiz 2.3.2)

@ Click here to edit in wizard.

Applies to:

Oracle Payables - Version: 11.5.2

Information in this document applies to any platform.

NOTE: This document does not apply to Global Accounting (AX) installations.

AP Trial Balance Report

Payables Account Analysis Report

GL Account Analysis Report

Posted Invoice Register

Posted Payment Register

Purpose

It is the application user's responsibility to reconcile AP to GL and, when appropriate, to make adjustments to

bring

the ledgers into balance. However, support recognizes that there are cases where unbalanced ledgers could be

caused by a

bug or where the source of the difference is difficult to find because of the quantity of data. This note sets out the

process for reconciling the ledgers, troubleshooting, and logging a service a request if necessary.

If you have completed the reconciliation and troubleshooting process below and have not found the problem or

have

questions about how to correct a problem, you can open a service request by following the steps in

the section labeled Opening a Service Request.

The AP subledger may be out of balance because of inconsistent data created by the

application users, for example, when a GL manual adjustment has been made to an AP liability account. In any

case

like this, it will be the application user's responsibility to determine the correct entry and either make an

adjustment

in the General Ledger, add a transaction in AP, or both. Oracle Support cannot recommend entries when the

resolution

of the problem requires the interpretation of an accounting issue.

This Note does not apply to all situations where an invoice appears incorrectly on the AP Trial Balance Report. In

many of those cases, the information will also be incorrect in the GL. If so, the troubleshooting steps below will

not

apply.

Reconciliation Process

Make sure all AP batches are imported from the gl_interface and posted in GL.

Reconcile the current period

Use the following as an example of how to balance. In this example, you are closing your accounting period for

April

and you have just posted your final invoice and payment batches to your general ledger system.

To reconcile your accounts payable activity for April, make the following calculation:

"Accounts Payable Trial Balance" as of March 31

+ "Posted Invoice Register" for the period between April 1 and April 30

- "Posted Payment Register" for the period between April 1 and April 30

26-Nov-10 My Oracle Support | Knowledge Brows…

support.oracle.com/CSP/ui/flash.html 1/8

jatin

Highlight

jatin

Highlight

= "Accounts Payable Trial Balance" as of April 30

You can also compare your AP liability accounts to GL by doing a query of the accounts in GL to identify the

account or

accounts out of balance. The trial balance total should be the same as the balance of your GL liability account.

Reconcile prior periods (if necessary)

If the current period does not reconcile, please complete the reconciliation process for all prior periods

from the most recent to the earliest until you get to one that reconciles.

Troubleshooting

Most of the problems with AP not balancing to GL are caused by the following:

Manual Entries

Manual journal entries in the general ledger that involve an AP liability account will cause the AP Trial

balance not to reconcile to the GL. These entries are not included in the AP subledger so they will not

be reflected on the AP Trial Balance Report.

To check for manual entries:

Run the GL "Account Analysis" report for the liability account and for the date range in question. Look for

transactions with a source other

than Payables. This can quickly pinpoint any transactions incorrectly charged to the account.

Any datafix that involves undo with sweep from one period to another.

If you performed a datafix in the past where you used the undo accounting script and swept a transaction

forward from a closed period to reaccount it, this will cause an imbalance between AP and GL. The imbalance

will be corrected in the period in which you made a GL adjustment to account for the fix. This is the

expected result and is not correctable.

If you did not make a GL adjustment to account for the fix, you will have a permanent imbalance that will

need to be corrected with a GL adjustment.

Corrections made during the journal import process

Any correction you make during the journal import process will result in the line being changed in the

general ledger, but not in AP. As a general rule, you should not correct AP transactions during the

journal import process. Instead, they should be corrected in the AP subledger before they are transferred

to GL.

Deletion from the GL Interface

If you have deleted any AP batches or lines from AP batches out of the GL Interface,

this will cause AP and GL to be out of balance. If you have done this, please open a

service request and, if possible, provide details about what was deleted.

AP data should never be deleted from the GL Interface except as directed by AP support

in a service request.

AP batches not imported from the GL Interface

If the AP batch is still in the GL Interface, it will not be reflected in the GL reports and this will

cause a difference between AP and GL.

See step 1 below for a query to check the GL Interface. This will show the payables source group ids

that need to be imported into the GL and posted before the reconciliation process can be performed.

AP batches not posted.

GL information is not included in the reports until it is posted. Any AP batches that are unposted in

GL will cause a difference between AP and GL.

Please run the diagnostic in Note:205624.1 to determine whether unposted AP batches exist.

Consider known bugs that can cause this problem:

Bug:4449844 AP Trial Balance does not honor as of date, as detailed in Note:315147.1

Bug:3385847 Failure In Gl Transfer Program Did Not Rollback All, as detailed in Note:282520.1



Opening a Service Request

If you cannot resolve the reconciliation problem after completing the steps above, you can open a service

request by performing the following steps and uploading the requested information.

********************************

Note the following steps will create some tables in your database. Please be sure you

understand the effects of these steps and have appropriate permissions before proceeding.

********************************

1. First verify that there is no data in the GL Interface that has yet to be processed.

If the following query returns any rows this data must be imported and posted into

the GL before following through with the rest of the action plan.

select x.group_id

, x.batch_name

, x.gl_transfer_run_id

, g.status

, USER_JE_SOURCE_NAME

, sum(g.accounted_dr)

, sum(g.accounted_cr)

from XLA_GL_TRANSFER_BATCHES_ALL x

, GL_INTERFACE g

where x.group_id = g.group_id

and x.application_id = 200

and g.gl_sl_link_table = 'APECL'

group by x.group_id

, x.batch_name

, x.gl_transfer_run_id

, g.status

, USER_JE_SOURCE_NAME;

2. Please upload two Posted Invoice Registers

One for the period that is out of balance.

One for the period prior to the period that is out of balance.

3. Please upload two Posted Payment Registers



4. Please upload three Accounts Payable Trial Balances

One for the last day of the period that is out of balance.

One for the last day of the period prior to the period that is out of balance.

One for the last day of the period two periods prior to the period that is out of balance.

5. Please upload three Payables Account Analysis Reports



One for the period two periods prior to the period that is out of balance.

6. Create a couple tables to work with to compare AP and GL. When prompted for a period name,

enter the name of the earliest period that is out of balance.

create table Note_344367_1_gl_t as

select l.accounted_dr

, l.accounted_cr

, l.code_combination_id

, nvl(l.gl_sl_link_id, ir.gl_sl_link_id) gl_sl_link_id

, l.set_of_books_id

, h.status

, h.je_batch_id

from gl_je_lines l

, gl_je_headers h

, gl_import_references ir

where l.period_name = '&&Period_Name'

and l.je_header_id = h.je_header_id

and l.je_header_id = ir.je_header_id

and l.je_line_num = ir.je_line_num

and nvl(l.gl_sl_link_table, ir.gl_sl_link_table) = 'APECL';

create table Note_344367_1_ap_t as



select sum(l.accounted_dr) accounted_dr

, sum(l.accounted_cr) accounted_cr

, h.gl_transfer_run_id


, l.gl_sl_link_id

, h.set_of_books_id

, l.org_id

, l.ae_line_type_code

from ap_ae_lines_all l

, ap_ae_headers_all h

where l.ae_header_id = h.ae_header_id

and h.period_name = '&&Period_Name'

and h.gl_transfer_flag = 'Y'

group by l.gl_sl_link_id

, h.set_of_books_id


, l.org_id

, l.ae_line_type_code

, h.gl_transfer_run_id;

create table Note_344367_1_ap_ccids as

select distinct l.code_combination_id

, l.org_id

, h.set_of_books_id




and h.period_name = '&&Period_Name'


and l.ae_line_type_code = 'LIABILITY';

7. Create some indexes to get the rest of the queries to go a bit faster

create index Note_344367_1_gl_t_idx on Note_344367_1_gl_t (gl_sl_link_id);

analyze table Note_344367_1_gl_t estimate statistics;

create index Note_344367_1_AP_t_idx on Note_344367_1_AP_t (gl_sl_link_id);

analyze table Note_344367_1_AP_t estimate statistics;

create index Note_344367_1_ap_ccids_idx on Note_344367_1_ap_ccids (set_of_books_id, code_combination_id);

analyze table Note_344367_1_ap_ccids estimate statistics;

create index Note_344367_1_alb1 on AP_LIABILITY_BALANCE (ae_line_id);

create index Note_344367_1_alb2 on AP_LIABILITY_BALANCE (ae_header_id);

analyze table ap.ap_liability_balance estimate statistics;

8. This query will check all those entries in AP that do not seem to be in GL.

If this query returns rows it may indicate that data was deleted out of the

GL interface prior to importing into GL.

Upload output in Excel format if possible.

select l.gl_sl_link_id

, l.accounted_dr

, l.accounted_cr

, l.org_id

, l.set_of_books_id

, l.gl_transfer_run_id

from Note_344367_1_ap_t l

where not exists (

select a.gl_sl_link_id

from Note_344367_1_gl_t a

where a.gl_sl_link_id = l.gl_sl_link_id);

9. This query will compare all those entries that are in both AP and GL

yet have different CCIDs or different amounts.

If this query returns rows it indicates that data was changed in the

GL interface prior to import.



, l.accounted_dr

, a.accounted_dr

, l.accounted_cr



, a.accounted_cr

, l.org_id

, l.set_of_books_id


, a.code_combination_id

from (

select sum(l1.accounted_dr) accounted_dr

, sum(l1.accounted_cr) accounted_cr

, l1.gl_sl_link_id

, l1.org_id

, l1.set_of_books_id

, l1.code_combination_id

from Note_344367_1_ap_t l1

group by l1.gl_sl_link_id

, l1.gl_sl_link_id

, l1.org_id

, l1.set_of_books_id

, l1.code_combination_id) l

, Note_344367_1_gl_t a

where l.gl_sl_link_id = a.gl_sl_link_id

and ( nvl(l.accounted_dr,0) != nvl(a.accounted_dr,0)

or nvl(l.accounted_cr,0) != nvl(a.accounted_cr,0)

or l.code_combination_id != a.code_combination_id);

10. This query will check for all those batches that have been transfered

to the GL and imported but not posted. If this query returns rows

then the reason these are not posted should be investigated.


select b.name

, b.status

, sum(t.accounted_dr)

, sum(t.accounted_cr)

, count(*)

from gl_je_batches b

, Note_344367_1_gl_t t

where b.je_batch_id = t.je_batch_id

and t.status != 'P'

group by b.name

, b.status;

11. This query will check the AP side for any entries that do not have

GL_SL_LINK_IDS. If rows are returned from this query it may indicate

that the standard AP to GL transfer program was not run or some

type of datafix was performed.



, l.accounted_dr

, l.accounted_cr

, l.org_id

, l.set_of_books_id



where l.gl_sl_link_id is null;

12. This query will check GL for any entries that are not in AP.

If this query returns rows it indicates that somehow the AP side was reset,

probably due to a datafix.


select a.accounted_dr

, a.accounted_cr

, a.gl_sl_link_id

, a.set_of_books_id

, a.code_combination_id

from Note_344367_1_gl_t a

where not exists (



where l.gl_sl_link_id = a.gl_sl_link_id);

13. This query will check all the liability accounts in GL that were

used in AP for this period for data that did not come from AP.



If this query returns rows it means that either two products are using

the same liability account or that entries have been manually entered into

the GL. Please explain why these were entered into the GL.


select jl.code_combination_id

, jl.je_header_id

, jl.je_line_num

, jl.accounted_dr

, jl.accounted_cr

, jl.creation_date

, jl.created_by

, u.user_name

from gl_je_lines jl

, gl_import_references ir

, fnd_user u

where jl.period_name = '&&Period_Name'

and jl.je_header_id = ir.je_header_id (+)

and jl.je_line_num = ir.je_line_num (+)

and jl.created_by = u.user_id (+)

and nvl(jl.gl_sl_link_id, ir.gl_sl_link_id) is null

and (jl.set_of_books_id, jl.code_combination_id) in (

select t.set_of_books_id

, t.code_combination_id

from Note_344367_1_ap_ccids t);

14. This query will validate that the accounts used as Liability Accounts

do not have other line types. If this query returns rows it most likely

indicates:

a) A setup problem;

b) The Update Accounting Entries screen was used and the wrong account was entered; or

c) User entered distribution line as CHARGE type but against liability code combination

select ae_line_type_code

, accounted_cr

, accounted_dr

, gl_sl_link_id

from Note_344367_1_ap_t

where ae_line_type_code != 'LIABILITY'

and code_combination_id in (

select a.code_combination_id

from Note_344367_1_ap_ccids a);

15. This query will validate that all LIABILITY lines in AP_AE_LINES_ALL

that have been marked as transferred are in the AP_LIABILITY_BALANCE

table. If this query returns rows the AP to GL transfer program may

have aborted mid run.


select h.gl_transfer_flag

, h.period_name

, l.accounted_dr

, l.accounted_cr

, h.set_of_books_id

, l.ae_header_id

, l.ae_line_id




and h.period_name = '&&period_name'

and l.ae_line_type_code = 'LIABILITY'


and not exists (

select a.ae_line_id

from ap_liability_balance a

where a.ae_line_id = l.ae_line_id);

16. This query will validate that all rows in AP_LIABILITY_BALANCE

exist in AP_AE_LINES_ALL. If this query returns rows it indicates

that some type of datafix has been done and the AP_LIABILITY_BALANCE

table needs to be rebuilt.

select b.invoice_id

, b.org_id



, b.ae_line_id

, b.ae_header_id

, b.accounted_dr

, b.accounted_cr

, b.set_of_books_id

, b.accounting_date

from ap_liability_balance b

where b.accounting_date between '&&Period_Start' and '&&Period_End'

and not exists (

select a.ae_line_id

from ap_ae_lines_all a


where a.ae_line_id = b.ae_line_id

and a.ae_header_id = h.ae_header_id

and h.gl_transfer_flag = 'Y');

17. This query will check that the values in AP_AE_LINES_ALL are the

same as the values in AP_LIABILITY_BALANCE. If this query returns

rows it indicates some type of datafix has been done.


select h.gl_transfer_flag

, h.period_name

, l.accounted_dr

, l.accounted_cr

, h.set_of_books_id

, l.ae_header_id

, l.ae_line_id



, ap_liability_balance b


and h.period_name = '&&period_name'


and l.ae_line_id = b.ae_line_id

and (nvl(l.accounted_dr,0) != nvl(b.accounted_dr,0)

or nvl(l.accounted_cr,0) != nvl(b.accounted_cr,0)

or h.accounting_date != b.accounting_date);

18. Drop the indexes created.

drop index Note_344367_1_alb1;

drop index Note_344367_1_alb2;

drop index Note_344367_1_gl_t_idx;

drop index Note_344367_1_AP_t_idx;

drop index Note_344367_1_ap_ccids_idx;

19. Drop the tables created

drop table Note_344367_1_ap_ccids;

drop table Note_344367_1_gl_t;

drop table Note_344367_1_AP_t;

20. Run Gather Schema Statistics for the AP schema to reset the statistics modified.

21. Check the backup and TAR tables on your system to see what datafixes may have been run.

select created

, object_name

, owner

from dba_objects

where object_type = 'TABLE'

and (object_name like 'BAK%'

or object_name like 'TAR%'

or object_name like 'SR%');

22. Run AP_Patches_Applied.sql from note 416696.1 and upload the output.

23. Please Run the script from Note 205624.1 - General Ledger (GL) Period End Reconciliation

24. Please download ftp://ftp.oracle.com/support/hidden/patchsets/AP/scripts/Check_Trial_Balance_h.zip



25. Please run Check_Trial_Balance_h.zip, a read only diagnostic script to show all invoices that have

0 remaining on the payment schedule but the liability lines do not balance to 0. This script

will show any invoices that may be accounted incorrectly.

This script is best run after accounting has been run for all eligible transactions

and all transactions have been accounted transferred to the GL.

This report can take VERY LONG time to run on larger systems. Please upload any other data

requested while this is running.

26. Please run APAtgHealthCheck.sql from note 416699.1 which is a read only diagnostic script.

This script will show any orphan data or transactions that have inconsistent dates.

Both Orphans and Inconsistent dates can throw off balancing.

Use 01-JAN-1995 as the start date (type out all 4 digits of year).

This script requires SQL*Plus 8.1.7 or greater.

Recommended method is to run sqlplus from the server.

Answer Y when prompted for detail.

The run will create two reports. Upload both reports (summary & detail).

This takes a while to run on larger systems. Please upload any other data requested while this is running.

27. Please run the ACT script for short_name=AP or SQLAP (whichever is on the list)

If data corruption exists, this will help support determine the root cause.

This script will list all versions of all the AP forms, reports, packages, etc.

Instructions on how to download and install the ACT script are in Note 183274.1.

The note contains more detailed instructions, however, once the

diagnostics are installed and you have logged in, you can use

the following steps to get the report we need:

a. Select Applications DBA from the Application drop down box.

b. Click the Advanced tab in the upper right hand corner.

c. Select the Application Collection Tool (ACT) on the left-hand side.

d. Enter your username and password. Select the responsibility id you were

using when the problem ocurred from the list of values and enter the Application

Shortname: AP or SQLAP (whichever is on the list)

References

BUG:4449844 - Apxtrbal Ap Trial Balance Does Not Honor The As Of Date

NOTE:106740.1 - Accounts Payable Trial Balance Frequently Asked Questions

NOTE:205624.1 - General Ledger (GL) Period End Reconciliation

Keywords

APXTRBAL.RDF ; MANUAL~ADJUSTMENT ; RECONCILE ; RECONCILIATION ;

Help us improve our service. Please email us your comments for this document.

Related

Products

Oracle E-Business Suite > Financial Management > Procure to Pay > Oracle Payables

Keywords

APXTRBAL.RDF; MANUAL ADJUSTMENT; RECONCILE; RECONCILIATION

Back to top



ap to gl reco

Documents