Amount Mismatch Between AP Trial Balance And GL Account Analysis Report (Doc ID 1308301.1)

Oracle Payables - Version: 12.1.2 and later   [Release: 12.1 and later ]
Information in this document applies to any platform.
AP Trial Balance Report, GL Account Analysis Report

Issue 
The total of amounts in the AP Trial Balance doesnt match with the Total of amounts in the GL Account Analysis Report.
ie Trial Balance +(Posted Invoices-Posted Payments) is not equal to GL Balance

There is a mismatch between the AP Trial Balance report and GL Account Analysis report amount.

Because of this customers are not able to reconcile the data between AP and GL.

Customers need to know why there is a mismatch between GLand AP amounts. This would help them in auditing the transactions and reconciliation.

SOLUTION

Manual entries created though undo accounting do not appear on Trial balance because Manual entries(Reversal and Original) are supposed to net off each other.

So if you run till trial balance till sysdate,entries would anyways net off and would have no net effect.

Sometimes Reversal Manual is created in separate period from Original Manual thats why entries might not be netting off in that period but till sydate all Manuals should net off.

Please run the APTB report till date and check if the invoices are still showing up.

This is the reason why the reports APTB and GL account analysis would not exactly match.

If the data in xla_ae_lines and gl is reconciled, that means there is no issue with the reconciliation.

As per the << Document 553484.1>> - Troubleshooting Accounts Payable Trial Balance (Open Account AP Balance Listing) issues in R12, the following queries can be used to reconcile the data.

Query 17: Run the following query to get the account ccid 

SELECT * 

FROM gl_code_combinations 

WHERE 1=1 

and segment1 = '&segment1' 

and segment2 = '&segment2' 

and segment3 = '&segment3' 

and segment4 = '&segment4' 

and segment5 = '&segment5';

Query 18: Please run the following query to show the Liability account totals in GL (gl_je_lines):

select gjh.period_name, gps.start_date, gjh.je_source, gjh.ledger_id, gjh.status,

gjl.code_combination_id, sum(accounted_dr), sum(accounted_cr), count(*)

from gl_je_lines gjl, gl_je_headers gjh, gl_je_batches gjb, gl_period_statuses gps

where gjl.je_header_id = gjh.je_header_id

and gps.period_name(+) = gjh.period_name

and gps.ledger_id(+) = gjh.ledger_id

and gps.application_id = 200

and gjb.je_batch_id = gjh.je_batch_id

and code_combination_id in (&ccid)

and gjh.ledger_id = &ledger_id

group by gjh.period_name,gps.start_date, gjh.je_source, gjh.ledger_id, gjh.status,

gjl.code_combination_id

order by gjh.je_source, gps.start_date;

Note: Verify the entries are posted, Status = P.

Query 19: Please run the following query to show the Liability account totals in GL (gl_balances):

select *

from gl_balances

where 1=1

and code_combination_id in (&ccid)

and ledger_id = &ledger_id

order by period_year, period_num;

Query 20: Please run the following query to show the Liability account totals in AP (xla_ae_lines):

select xeh.application_id, xeh.ledger_id, xeh.period_name, gps.start_date,

xeh.gl_transfer_status_code, xel.accounting_class_code,

xel.code_combination_id, sum(xel.accounted_dr), sum(xel.accounted_cr), count(*)

from xla_ae_lines xel, xla_ae_headers xeh, gl_period_statuses gps

where 1=1

and xel.application_id = 200

and xeh.ae_header_id = xel.ae_header_id

and xeh.gl_transfer_status_code = 'Y'

and gps.period_name(+) = xeh.period_name

and gps.ledger_id(+) = xeh.ledger_id

and gps.application_id(+) = 200

and code_combination_id in (&ccid)

and xeh.ledger_id = &ledger_id

group by xeh.application_id, xeh.ledger_id, xeh.period_name, xel.accounting_class_code,

gps.start_date, xeh.gl_transfer_status_code, xel.code_combination_id

order by xel.accounting_class_code, gps.start_date;

Note: Only accounting_class_code = Liability will be included in the Trial Balance Report totals.

Query 21: Please run the following query to show the Liability account totals in AP (xla_trial_balances):

select xeh.application_id, xeh.ledger_id, xeh.period_name, gps.start_date,

xeh.gl_transfer_status_code, xtb.event_class_code,

xtb.source_application_id, xtb.definition_code, code_combination_id, to_char(gl_date, 'MON-YY'),

sum(acctd_unrounded_dr), sum(acctd_unrounded_cr), count(*)

from xla_trial_balances xtb, xla_ae_headers xeh, gl_period_statuses gps

where 1=1

and xeh.application_id = 200

and xeh.ae_header_id = xtb.ae_header_id

and xeh.gl_transfer_status_code = 'Y'

and gps.period_name(+) = xeh.period_name

and gps.ledger_id(+) = xeh.ledger_id

and gps.application_id(+) = 200

and code_combination_id in (&ccid)

and xeh.ledger_id = &ledger_id

group by xeh.application_id, xeh.ledger_id, xeh.period_name, gps.start_date,

xeh.gl_transfer_status_code, xtb.event_class_code,

xtb.source_application_id, xtb.definition_code, code_combination_id, to_char(gl_date, 'MON-YY')

order by xtb.definition_code, gps.start_date, xtb.event_class_code;

Query 18 and Query 20 will give the period wise balances in GL and XLA_AE_LINES respectively. If the balance is matching between these two queries, then that means the invoice data is accounted and pushed to GL. Now check the results of Query 21, if Query 20 and Query 21 does not match, then the issue is with the data in XLA_TRAIL_BALANCE from where the APTB report pulls the data.

The mismatch might be due to the manual event type in XLA_AE_LINES which is generally posted due to datafixes. This data would not be present in XLA_TRIAL_BALANCE causing the mismatch.

The below query shows the manual transactions in the month of January and the amount exactly matches the AP-GL difference amount in the Trial Balance report.

select distinct xe.event_id, xe.application_id, xe.event_type_code, xe.event_date, xe.entity_id, xe.event_status_code,  

xe.process_status_code, xe.upg_batch_id, xe.budgetary_control_flag,  

xeh.ledger_id, xeh.ae_header_id, xeh.accounting_date, xeh.description, xeh.gl_transfer_status_code, xeh.accounting_entry_status_code,  

xel.gl_sl_link_id, xel.accounting_class_code, xel.party_id, xel.party_site_id, xel.accounted_dr, xel.accounted_cr  

from xla_ae_lines xel, xla_ae_headers xeh, xla_events xe  

where xe.application_id = 200  

and xe.event_type_code = 'MANUAL'  

and xel.accounting_class_code = 'LIABILITY' 

and xel.code_combination_id= &code_combination_id

and xeh.event_id = xe.event_id  

and xeh.ae_header_id = xel.ae_header_id 

and xe.event_date LIKE &period_name.

order by xeh.ledger_id, xe.event_id, xeh.ae_header_id, xeh.accounting_date;

Please decide on a single period with mismatch to run the queries which would make the analysis easier.