Troubleshooting Accounts Payable Trial Balance (Open Account AP Balance Listing) issues in R12

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Troubleshooting Accounts Payable Trial Balance (Open Account AP Balance Listing) issues in R12

Symptoms:

How to troubleshoot Accounts Payable Trial Balance (Open Account AP Balance Listing) issues in R12

Solution:

Troubleshooting Details

Apply the latest code for the Trial Balance

It is important to have the latest Trial Balance code and files to avoid encountering any of the known issues. Please apply the following patches to get the latest Trial Balance Code and any dependent/related files (As of 14-Jul-10):

R12.0.x

• 9162536:R12.AP.A (Includes Summary/Detail Report Mode change and Trial Balance Remodel Phase 4 patch 8291101.)
• 9602525:R12.XLA.A

R12.1.x

• 9162536:R12.AP.B (Summary/Detail Report Mode change and Trial Balance Remodel Phase 4)
• 9023641:R12.XLA.B
• 9133956:R12.XLA.B
• 9451385:R12.XLA.B

Note: Please see R12 Oracle Financials XLA Patching Dependency Analysis Note 833986.1 for additional patching that may be required.

The Summary/Detail Report Mode change includes:

<!--[if !supportLists]-->· <!--[endif]-->Changes towards APTB remodelling initiated in XLA bug 8773522, the change is that there will be a new parameter REPORT STYLE that user can choose while running the Concurrent program. The value is either Summary or Detail.

The remodeling of Trial Balance phase 4 add/includes the following features:

• Addresses the issue where Open Account AP balances Listing
  Report shows no data if new Journal source other than payables is added
  to the Definition code.
• Added the following columns to the "Group by Account, Summary" template:
• Other Sources : GL Balance of the journal entries from non Payables sources.
• Subledger Manuals: GL Balance of the manual journal entries created in Subledger.
• Parameters added/changed:
• Start Date: This parameter is now hidden and defaults to 01-Jan-1950.
• Include SLA Manuals/Other Sources: This parameter controls whether SLA Manual entries and Non Payables sources amounts are displayed in the "Group by Account, Summary" template report output.
• The Report by default runs with Year To Date parameter. The start date
  and Account Balance parameter are not displayed on the report.
  The Remaining amount is calculated by default from the start
  period of a transaction till As Of Date entered by the user.
• If the user enters a future as of date that does not exists in
  GL, the latest open period GL Amount will be shown on the
  report.
• Added legends in the report for the following columns:
  GL Balance, Subledger Manuals, Other Sources, GL Account Balance
  and Payment Status.

The Trial Balance Phase 4 remodel patch includes the previous remodel additions/updates e.g.

• AP Invoice amount and payment status fields are now included on the report template for better diagnostics.
• Improved performance in the Open Accounts Data Manager.
• It only reports on Liability accounting line information.
• The report filters out the Liability account data in AP, even if the same account is used for non-Liability accounts.
• No manual entries are inserted into the Trial Balance table (xla_trial_balances)

Accounts Payable Trial Balance report name

In R12, there are 4 Concurrent Programs related to the trial balance.

1. Report Name = "Accounts Payable Trial Balance (Old) "

Short Name = APXTRBAL

This is the R11i Accounts Payable Trial Balance, it should be disabled in R12.

2. Report Name = "Accounts Payable Trial Balance"

Short Name = APTBRPT

This is the R12 Accounts Payable Trial Balance, this is the correct report name to run if Trial Balance Remodel Phase 4 or higher has been applied. This report is a modified version of the Open Account Balance Listing report.

3. Report Name = "Open Account Balance Listing"

Short Name = XLATBRPT

This is a Subledger Accounting report. This report should NOT be used for Payables. Instead, use the applicable Payables modified version of the report.

4. Report Name = "Open Account AP Balance Listing"

Short Name = XLAAPRPT

This is the subledger Open Account Balance Listing report modified for Payables and should be used with Trial Balance Remodel Phases 1 - 3.

Note: Currently, the Trial Balance documentation and notes refer to the Accounts Payable Trial Balance and Open Account AP Balance Listing report interchangeably. The name change was due to a change in Phase 1 - 3, going forward all Payables Trial Balance patches should retain the name "Accounts Payable Trial Balance". Please make sure you run the correct report based on your Trial Balance remodel phase and do NOT run the Open Account Balance Listing report for Payables.

Complete Post-Install Steps for Trial Balance Remodel patch

Note: Steps 1 - 2 can be skipped if these steps have already been completed for a previous Trial Balance remodel patch that was applied.

1. Please run the xlaapinvpostupg.sql script manually to populate the applied_to attributes in
xla_distribution_links table in apps schema. i.e.

>cd $XLA_TOP/patch/115/sql
> sqlplus apps/****
> @xlaapinvpostupg.sql

IMPORTANT: MAKE SURE THIS STEP IS COMPLETED AFTER APPLYING THE LATEST PATCH AND BEFORE REBUILDING THE DATA.

2. Check that the following indexes should be present in AP_LIABILITY_BALANCE table:

Note: This step is for R11i Customers upgraded to R12 ONLY. NEW installs of R12 and customers upgraded to R12.1.x should NOT complete this step.

2.1. Index on VENDOR_ID VENDOR_SITE_ID ( as _N1)
2.2. Index on INVOICE_ID (as _N2 )
2.3. Combination index on AE_LINE_ID,AE_HEADER_ID,CODE_COMBINATION_ID, SET_OF_BOOKS_ID in the same order (as _N3 from aplbupgind.sql script in
the patch ).
2.4. Combination index on SLE_LINE_NUM,SLE_HEADER_ID,CODE_COMBINATION_ID, SET_OF_BOOKS_ID in the same order (as _N4 from aplbupgind.sql script in
the patch).

Query 1: Use the following query to find the indexes that exist:

select owner, substr(c.table_name,1,25)
,c.index_name,substr(c.column_position,1,2)
,substr(c.column_name,1,20),i.status
from
dba_ind_columns c, dba_indexes i
where i.table_name in ('AP_LIABILITY_BALANCE')
and c.index_name = i.index_name
order by index_name,column_position;

2.5 If not, manually run the script aplbupgind.sql (120.0.12000000.3) to create the
AP_LIABILITY_BALANCE_N3 and AP_LIABILITY_BALANCE_N4 index.

2.6. Gather Stats for the ap_liability_balance table.

exec dbms_stats.GATHER_TABLE_STATS('AP','AP_LIABILITY_BALANCE');

2.7. Verify ap_liability_balance table was analyzed/gathered stats:

Query 2: Run the following query to verify ap_liability_balance table was analyzed/gathered stats:
SELECT table_name, num_rows, blocks, avg_row_len,
to_char(last_analyzed ,'MM/DD/YYYY HH24:MI:SS')
FROM dba_tables
WHERE table_name IN
('AP_LIABILITY_BALANCE');

Make sure the ap_liability_balance table has a current last analyzed date and shows a row count under num_rows column.

3. Rebuild the Open Account AP Balances Listing (Trial Balance) Data

How to Rebuild the Open Account AP Balances Listing (Trial Balance) Data

1. Navigate: Payables Responsibility > Setup > Accounting Setups > Subledger Accounting
Setups > Open Account Balances Listing Definitions

Note: For better performance of Data Manager run, increase the number of processors to 16,32 or 64 by
navigating to Sub Ledger Accounting Setup -> Open Account Balances Listing Definitions
-> Process Options button and for the ledger update the number of processors to
16,32 or 64.

2. Query the Report Definition Code

Note: To search for Report Definitions created by the upgrade, select Defined By = Accounting Flexfield.
And upgraded report definitions are created by the apintbal.sql script during the upgrade with a definition code in the following format: AP_200_' || T.ledger_id

3. Click on Duplicate to Copy the definition.

4. Enter name and code for Copy and Apply the changes.

IMPORTANT: View requests and wait for Data Manageer processes submitted to complete before
proceeding to the next steps. When you modify an existing or create a new report definition the following data manager processes are submitted:

USER_CONCURRENT_PROGRAM_NAME CONCURRENT_PROGRAM_NAME
Open Account Balances Data Manager Worker Process (XLATBDMW)
Open Account Balances Data Manager (XLATBDMG)

5. Reset the number of processors (If you increased the number of processors in step 1)

Navigate: Sub Ledger Accounting Setup -> Open Account Balances Listing Definitions ->
Process Options button and reset the number of processors back to 1 for the ledger.

Note: The original/upgraded report definition can be rebuilt also. However, for the purposes of testing and comparison, this note rebuilds a new/copied definition. If the rebuild of the new/copied report definition worked, the rebuild of the original/upgraded report definition should work also. Make sure whatever option is selected to be used for PROD, that option is tested first!

A report definition is rebuilt anytime the application thinks a change occurred. The following methods can be used to rebuild a report definition:

1)Enable/disable report definition.
2)Open report definition and merely click on apply button. This will repopulate xla_trial_balance table.

6. Verify the data was rebuilt

Query 3: Run the following query to verify the data was rebuilt

select xtb.definition_code, xeh.ledger_id, decode(xeh.upg_batch_id,
NULL,'NOT UPGRADE', 'UPGRADE') Upgraded, min(xtb.creation_date),
count(*)
from xla_trial_balances xtb, xla_ae_headers xeh
where xtb.ae_header_id = xeh.ae_header_id(+)
group by xtb.definition_code, xeh.ledger_id, decode(xeh.upg_batch_id, NULL,
'NOT UPGRADE',
'UPGRADE')
order by 2,1;

The rebuilt definition code data should have a minimum creation date equal to the date the data was rebuilt.

Clarify/Define the issue

1. Run a new Accounts Payables Trial Balance report for the report definition with the rebuilt data and the following details:

<!--[if !supportLists]-->· <!--[endif]-->Start Date = Begin Date for first period you started using Oracle (This parameter is hidden as of Trial Balance Remodel phase 4, with default date 01-Jan-1950)

<!--[if !supportLists]-->· <!--[endif]-->As of Date = End Date for Period to be reconciled

<!--[if !supportLists]-->· <!--[endif]-->Show Transaction Detail = Yes

<!--[if !supportLists]-->· <!--[endif]-->Include Write Offs = No

<!--[if !supportLists]-->· <!--[endif]-->Include SLA Manuals/Other Sources = Yes (This parameter is only applicable for the "Group by Account, Summary" template.

<!--[if !supportLists]-->· <!--[endif]-->For troubleshooting reconciliation issues, run report for the following templates:

<!--[if !supportLists]-->· <!--[endif]-->Accounts Payables Trial Balance - Group by Account, Detail

<!--[if !supportLists]-->· <!--[endif]-->Accounts Payables Trial Balance - Group by Account, Summary

Note: See Note 444044.1 for details on how to change the template.

Review the Open Account AP Balances Listing (Trial Balance) Data. Reconcile the data with GL. The correct method for reconciling AP to GL is as follows:

Last Months Accounts Payable Trial Balance

+ This months Payables Posted Invoice Register

- This months Payables Posted Payment Register

= This months Accounts Payable Trial Balance

Note: The Accounts Payable Trial Balance report output shows a GL total. That GL total includes manual and non Liability class entries from the Payables subledger, and non-Payables source entries in GL. Use the given method to reconcile, and the Trial Balance amount remaining total should equal the GL total excluding the manual and non Liability class entries from the Payables subledger, and non-Payables source entries in GL.

After patching, rebuilding and reconciling, if there is still an issue, define your issue. Most trial balance issues are of the following type:

Type 1: Invoice incorrectly reported/missing from trial balance.
Type 2: AP Trial Balance total does not reconcile with GL
Type 3: R12 Trial Balance does not reconcile with R11.5 Trial Balance after the upgrade.

See the instructions below for troubleshooting each type of issue.

Type 1: Invoice incorrectly reported/missing from trial balance

Steps to investigate/fix "Invoices incorrectly reported/missing from trial balance":

1. Review the Common Problems section in this note.

2. If you are unable to resolve this issue after reviewing the steps in this section, please log an SR and include the following:

a. What is the invoice problem?

<!--[if !supportLists]-->o <!--[endif]-->R12 invoices reported on the Trial Balance for a specific supplier that are paid and should not be reported.

<!--[if !supportLists]-->o <!--[endif]-->R12 invoices reported on the Trial Balance for a specific account that are paid and should not be reported.

<!--[if !supportLists]-->o <!--[endif]-->Invoices created in R11i and reported on Trial Balance in R11i, but NOT report on the R12 trial balance.

<!--[if !supportLists]-->o <!--[endif]-->Invoices created in R11i and NOT reported on Trial Balance in R11i, but reported on the R12 trial balance.

Note: Use the new Payment Status and Cancelled Dates column to identify Fully Paid and Cancelled invoices reported incorrectly.

b. Provide the data from the Get the example problem invoice data section from this note.

c. Upload the Accounts Payable Trial Balance report and request log output.

d. Upload the query results from the Get the Trial Balance file/package versions section of this note

Note: An invoice incorrectly reported on the Trial Balance does not necessarily mean AP and GL will not reconcile. An invoice can be incorrectly reported on the Trial Balance due to incorrect accounting. That incorrect accounting will likely exist in AP and GL, so they will be in balance.

Type 2: AP Trial Balance total does not reconcile with GL

Steps to investigate/fix "AP Trial Balance total does not reconcile with GL":

1. Review the Common Problems section in this note.

2. If you are unable to resolve this issue after reviewing the steps in this section, please log an SR and include the following:

a. How much is the out of balance?

b. When was the last time the Trial balance was in balance?

c. If any problem invoices have been identified as causing the out of balance, provide the data from the Get the example problem invoice data section from this note.

d. Upload the Accounts Payable Trial Balance report and request log output. Upload for both the "Group by Account, Detail" and "Group by Account, Summary" templates. Upload for the last month in balance and the current month out of balance..

e. How was the out of balance calculated? Upload an Excel spreadsheet or other documentation showing the last month in balance and the current month out of balance.

Note: A Reconciliation Spreadsheet is available to assist with reconciling. If possible, please download the spreadsheet, populate it with the requested information and upload it to the SR. Click here to download the spreadsheet.

f. Upload the Payables Posted Invoice Register and Payables Posted Payment Register for the last month in balance and the current month out of balance.

g. Upload the query results from the Get the Trial Balance file/package versions section of this note

h. Upload the query results from the Get the Report Definition Data section of this note.

i. Upload the query results from the Queries to Check for Known Issues section of this note.

j. Upload the query results from the Queries to compare XLA and GL data section of this note.

Type 3: R12 Trial Balance does not reconcile with R11.5 Trial Balance after the upgrade.

1. Review the Common Problems section in this note.

2. If you are unable to resolve this issue after reviewing the steps in this section, please log an SR and include the following:

a. What is the R11i Trial Balance Amount Remaining?

b. What is the R12 Open Account AP Balance Listing Amount Remaining?

c. What is the difference?

d. If any problem invoices have been identified as causing the out of balance, provide the data from the Get the example problem invoice datasection from this note.

e. Upload the R11i Accounts Payable Trial Balance with As of Date = Last day before your upgrade

f. Upload the R12 Accounts Payable Trial Balance with As of Date = Last day before your upgrade
e. Upload the results of Query 3 to show if upgraded data was populated in the xla_trial_balances table.

f. The R12 trial balance uses data from the R11i ap_liability_balance table to report upgraded data. Run the following query to verify the data existed and was correct in R11i:

Query 4: Check the data in the ap_liability_balance table

select set_of_books_id, org_id, code_combination_id, sum(nvl(accounted_cr,0)) cr, sum(nvl(accounted_dr,0)) dr,
sum(nvl(accounted_cr,0)) - sum(nvl(accounted_dr,0)) diff, count(*)
from ap_liability_balance
group by set_of_books_id, org_id, code_combination_id
order by set_of_books_id, org_id, code_combination_id;

Get the example problem invoice data

1. Upload APList output for some example problem invoices.

Note: Download and apply Oracle Diagnostic patch 8599943 to get the latest R12 Invoice Data Collection (APList) code.

2. Get the data from the XLA_TRIAL_BALANCES table

The Trial Balance report uses the data from the XLA_TRIAL_BALANCES table. Make sure the data is populated in this table.

(Get the Invoice_id from the APList)

Query 5: Get the xla_trial_balances data for an invoice

select *
from xla_trial_balances
where source_entity_id in (
select entity_id
from xla.xla_transaction_entities xte
where application_id = 200
and (
(entity_code = 'AP_INVOICES' and source_id_int_1 in (&invoice_id))
)
)
OR
applied_to_entity_id in (
select entity_id
from xla.xla_transaction_entities xte
where application_id = 200
and (
(entity_code = 'AP_INVOICES' and source_id_int_1 in (&invoice_id))
)
);

3. Get the ap_liability_balance data for an upgraded invoice

Query 6: Get the ap_liability_balance data

select *
from ap_liability_balance
where invoice_id = &invoice_id;

4. Get the Trial Balance Diagnostic for the problem invoice.

Download and apply Oracle Diagnostic patch 8517685*** to get the latest R12 Trial Balance Diagnostic.

Follow the instructions in Note 829180.1 to run the diagnostic.

***This patch is available through controlled release. Please contact support for assistance with obtaining this patch.

Get the Report Definition Data

18. Upload the Report Definition Data

Query 7: Get the Report Definition Data

SELECT *
FROM XLA_TB_DEFINITIONS_B;

SELECT *
FROM XLA_TB_DEFINITIONS_TL;

SELECT *
FROM XLA_TB_DEFN_DETAILS;

Common Problems

• Subledger Accounting created, but not transferred to GL.
• Subledger Accounting transferred to GL, but not posted in GL.
• The trial balance Start Date and As of Date do not include the invoice and payment accounting.
• The Trial Balance was ran for a specific operating unit and the Ledger has multiple operating units. The ledger total will only include the amounts for the operating unit it was ran for.
• Is the problem with upgraded data? (Check the APList upg_batch_id column in xla_events and xla_ae_headers)
• Do the APList accounting dates for the invoice and payment fall within the range of the Start Date and As of Date Trial Balance parameters?
• Are there rows in the xla_trial_balances tables? Do they look correct?
• (For non-upgraded data) If data is missing from the xla_trial_balances table, does the data in xla_ae_lines and xla_distribution_links look OK?
• (For upgraded data) If data is missing from the xla_trial_balances table, does the data exist in the ap_liability_balance table?
• Review the definition data, does it look like the Invoice/Payment should be included for a report definition? Does it has the same account/segment as defined for the definition?
• Verify the xlaapinvpostupg.sql script was ran. See the Complete Post-Install Steps for Trial Balance Remodel patch section of this note for instructions on how to run this script.
• The Accounts Payable Trial Balance GL total includes manual and non Liability class entries from the Payables subledger, and non-Payables source entries in GL. Make sure you exclude those from the GL Balance if you are trying to reconcile to that total.
• (For upgraded data) Were the R11i and R12 reports ran with the same parameters? Same Start Date? Same As of Date? Same account?
• Make sure the data is rebuilt. See the How to Rebuild the Open Account AP Balances Listing (Trial Balance) Data section of this note for instructions on how to rebuild the data and verify it has been rebuilt.

Queries to Check for Known Issues

Query 8: Find how many transfer runs do not have data in the gl_import_references table.
drop table sub_gir;
create table sub_gir as
select distinct gl_sl_link_id, je_batch_id, je_header_id, je_line_num
from gl_import_references
where gl_sl_link_table = 'XLAJEL'
order by 1;
CREATE INDEX sub_gir_r1 ON sub_gir (gl_sl_link_id);
CREATE INDEX sub_gir_r2 ON sub_gir (je_batch_id);
CREATE INDEX sub_gir_r3 ON sub_gir (je_header_id);
CREATE INDEX sub_gir_r4 ON sub_gir (je_line_num);
analyze table apps.sub_gir compute statistics;
select count(*) from sub_gir;
select XEH.ledger_id, xeh.group_id, xeh.gl_transfer_status_code, xeh.period_name,
xel.*
from xla_ae_lines xel, sub_gir gir, xla_ae_headers xeh
where xeh.ae_header_id = xel.ae_header_id
--and xel.gl_sl_link_id is not null
and xel.gl_sl_link_id = gir.gl_sl_link_id(+)
and xel.gl_sl_link_table = 'XLAJEL'
and xeh.gl_transfer_status_code = 'Y'
and gir.gl_sl_link_id is NULL
and xel.accounting_class_code = 'LIABILITY'
and xeh.application_id = 200
order by xeh.ledger_id asc, xeh.group_id desc;
And Query to find AP data in the gl_import_references table that no longer exists in xla_ae_lines table
select substr(gjb.name,1,35) name, gjh.period_name, gjh.ledger_id,
gir.*
from xla_ae_lines xel, sub_gir gir, gl_je_headers gjh , gl_je_batches gjb
where xel.gl_sl_link_id(+) = gir.gl_sl_link_id
and gjb.je_batch_id = gir.je_batch_id
and xel.application_id(+) = 200
and gjh.je_header_id = gir.je_header_id
and xel.gl_sl_link_id(+) = gir.gl_sl_link_id
and xel.gl_sl_link_table(+) = 'XLAJEL'
and gjh.je_source = 'Payables'
and xel.gl_sl_link_id is NULL
order by gir.je_batch_id desc;
Query 9. Check for data in the xla_trial_balances table that does match/exist in xla_ae_lines
select xtb.*
from
(select xeh.ledger_id, xeh.ae_header_id, xeh.accounting_date,
xeli.code_combination_id, sum(accounted_dr) xel_dr, sum(accounted_cr) xel_cr, count(*)
from xla_ae_lines xeli, xla_ae_headers xeh
where xeli.ae_header_id = xeh.ae_header_id
and xeh.application_id = 200
and xeli.accounting_class_code = 'LIABILITY'
and xeh.event_type_code != 'MANUAL'
and xeh.gl_transfer_status_code = 'Y'
group by xeh.ledger_id, xeh.ae_header_id, xeh.accounting_date, xeli.code_combination_id) xel,
(select definition_code, ledger_id, code_combination_id, ae_header_id, gl_date,
sum(acctd_unrounded_dr) xtb_dr, sum(acctd_unrounded_cr) xtb_cr, count(*)
from xla_trial_balances
group by definition_code, ledger_id, code_combination_id, ae_header_id, gl_date) xtb
where 1=1
and xtb.ae_header_id = xel.ae_header_id(+)
and nvl(xtb.xtb_dr, 0) - nvl(xtb.xtb_cr, 0)= nvl(xel.xel_dr(+),0)- nvl(xel.xel_cr(+),0)
and xtb.ledger_id = xel.ledger_id(+)
and xtb.code_combination_id = xel.code_combination_id(+)
and trunc(xtb.gl_date) = trunc(xel.accounting_date(+))
and xel.ae_header_id is NULL
order by xtb.definition_code, gl_date desc;
And Check for data in the xla_ae_lines table that does match/exist in xla_trial_balances table
select xel.*
from
(select xeh.ledger_id, xeh.ae_header_id, xeh.accounting_date,
xeli.code_combination_id, sum(accounted_dr) xel_dr, sum(accounted_cr) xel_cr, count(*)
from xla_ae_lines xeli, xla_ae_headers xeh
where xeli.ae_header_id = xeh.ae_header_id
and xeh.application_id = 200
and xeli.accounting_class_code = 'LIABILITY'
and xeh.event_type_code != 'MANUAL'
and xeh.gl_transfer_status_code = 'Y'
group by xeh.ledger_id, xeh.ae_header_id, xeh.accounting_date, xeli.code_combination_id) xel,
(select definition_code, ledger_id, code_combination_id, ae_header_id, gl_date,
sum(acctd_unrounded_dr) xtb_dr, sum(acctd_unrounded_cr) xtb_cr, count(*)
from xla_trial_balances
group by definition_code, ledger_id, code_combination_id, ae_header_id, gl_date) xtb
where 1=1
and xtb.ae_header_id(+) = xel.ae_header_id
and nvl(xtb.xtb_dr(+), 0) - nvl(xtb.xtb_cr(+), 0)= nvl(xel.xel_dr,0)- nvl(xel.xel_cr,0)
and xtb.ledger_id(+) = xel.ledger_id
and xtb.code_combination_id(+) = xel.code_combination_id
and trunc(xtb.gl_date(+)) = trunc(xel.accounting_date)
and xtb.ae_header_id is NULL
order by accounting_date desc;
Query 10: Check for data in xla_trial_balances and NOT in AP_SLA_INVOICES_TRANSACTION_V
select *
from xla_trial_balances tb
where NOT exists (
select 1
from AP_SLA_INVOICES_TRANSACTION_V tiv, xla.xla_transaction_entities xte
where nvl(tb.applied_to_entity_id,tb.source_entity_id)=xte.entity_id
and xte.entity_code='AP_INVOICES'
and nvl(xte.source_id_int_1,-99)=tiv.invoice_id
and xte.application_id=tb.source_application_id
)
and tb.source_application_id=200
and tb.code_combination_id = &ccid
and tb.ledger_id =& ledger_id
order by tb.definition_code, tb.ledger_id;
Query 11: Check for invoices with bug 7871425 (party_site_id is NULL for non-employees)
select ai.*
from ap_invoices_all ai, hz_parties HZP , hz_party_sites HZPS,ap_suppliers as1
where ai.party_site_id is NULL
and nvl(ai.historical_flag, 'N') = 'N'
and AI.vendor_id = AS1.vendor_id(+)
and AI.party_id = HZP.party_id
and AI.party_site_id = HZPS.party_site_id(+)
and AS1.employee_id is null
and HZPS.party_site_id is null;
Query 12: Check for invoices with bug 7019211 (Payment Request Invoices)
select xte.entity_id, xe.event_id, xe.event_date,
ai.invoice_id, ai.vendor_id, ai.vendor_site_id,
ai.party_id, ai.party_site_id
from ap_invoices_all ai, xla_events xe,
xla.xla_transaction_entities xte
where ai.invoice_type_lookup_code = 'PAYMENT REQUEST'
and ai.vendor_id < 0
and xte.entity_code = 'AP_INVOICES'
and xte.source_id_int_1 = ai.invoice_id
and xe.entity_id = xte.entity_id
order by ai.invoice_id, xe.event_id;
Query 13: Check for MANUAL subledger journal entries
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.code_combination_id = &ccid
and xeh.ledger_id =& ledger_id
and xel.accounting_class_code = 'LIABILITY'
and xeh.event_id = xe.event_id
and xeh.ae_header_id = xel.ae_header_id
order by xeh.ledger_id, xe.event_id, xeh.ae_header_id, xeh.accounting_date;
See Note 605707.1 for explanation of how manual entries can affect reconciliation
Query 14: Check for all data in the xla_trial_balances table with event_class_code = PAYMENTS and the applied_to_entity_id is NULL.
select *
from xla_trial_balances
where applied_to_entity_id is NULL
and event_class_code = 'PAYMENTS'
order by definition_code;
select definition_code, ledger_id, event_class_code, source_application_id,
decode(source_entity_id, NULL, 'NULL', 'NOT NULL'),
decode(applied_to_entity_id, NULL, 'NULL', 'NOT NULL'),
count(*)
from xla_trial_balances
group by definition_code, ledger_id, event_class_code, source_application_id,
decode(source_entity_id, NULL, 'NULL', 'NOT NULL'),
decode(applied_to_entity_id, NULL, 'NULL', 'NOT NULL')
order by definition_code, ledger_id;
Query 15: Check for Orphan rows in the xla_ae_lines table
select *
from xla_ae_lines xel
where NOT exists (
select 1
from xla_ae_headers xeh
where xel.ae_header_id = xeh.ae_header_id)
and xel.application_id = 200
and xel.code_combination_id in (&ccid)
and xel.ledger_id =& ledger_id;
Query 16: Check for journal entries with Non Liability accounting entry class codes:
select xeh.application_id, xeh.ledger_id, xeh.period_name, gps.start_date,
xeh.gl_transfer_status_code, xel.accounting_class_code,
xel.*
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
and xel.accounting_class_code != 'LIABILITY'
order by 2,4;
Query 17: Query to check for upgraded accounting transferred after upgrade (Note 1092913.1)
select distinct aeh.accounting_event_id, aeh.ae_header_id, aeh.org_id, aeh.set_of_books_id, aeh.period_name, aeh.gl_transfer_flag,
h.ae_header_id, h.ledger_id, h.period_name, h.accounting_entry_status_code, h.gl_transfer_status_code, h.group_id,
xte.entity_id, xte.entity_code, xte.source_id_int_1, xte.security_id_int_1, xte.transaction_number
from xla_ae_lines l
, xla_ae_headers h
, ap_ae_headers_all aeh
, xla_transaction_entities_upg xte
where h.application_id = 200
and xte.entity_id = h.entity_id
and h.event_id(+) = aeh.accounting_event_id
and nvl(aeh.gl_transfer_flag,'X') != 'Y'
and l.ae_header_id = h.ae_header_id
and h.upg_batch_id is NOT NULL
and nvl(h.description,'X') != 'R11.5 Upgrade';

Queries to compare XLA and GL data

Run queries to check data in AP and GL

Query 18: 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 19: 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 20: 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 21: 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 22: 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;

Additional Troubleshooting files/data:

If the steps above do not resolve the issue, the following additional information may be needed:

1. Get the trace for the Trial Balance process

The Trial Balance report does not directly use the data from the XLA_TRIAL_BALANCES tables. It takes the data from the XLA_TRIAL_BALANCES tables and inserts it into
the Global Temporary XLA_TRIAL_BALANCES_GT table. If there is a problem with that part of the code, a trace file may be needed to troubleshoot.

a. Submit the Trial Balance for the problem supplier to limit the size of the trace and use the trace instructions from:

Note 458371.1 - How To Get The R12 Trace/Debug File?

b. Create a copy of the XLA_TRIAL_BALANCES_GT table

create table copy_XLA_TRIAL_BALANCES_GT as
select *
from XLA_TRIAL_BALANCES_GT
where 1 =2;

c. Insert the data

Get the insert from the trace and modify the insert to insert into copy_XLA_TRIAL_BALANCES_GT

and replace the bind variable in the script with the actual values.

d. Query the data. Please upload the output formatted in an Excel file:

select *
from copy_xla_trial_balances_gt;

2. Review the RTF file

Upload the RTF file that corresponds to template i.e. Template = Accounts Payable Trial Balance - Group by Account, Detail corresponds to APTBRPT02.rtf. Open the file in MS WORD.
Do the following to find the column name that maps to the template field name:

a. Open the RTF file in MS WORD.

b. Right click on the field name > Click on Properties > Add Help Text

3. and XML output

Find the report output in the View Request form, Click on the Diagnostics button
and select View XML.

Get the Trial Balance file/package versions

Upload Trial balance file/package versions:

Log in to server, set environment, and do the following:

cd $XLA_TOP/patch/115/publisher/templates/US/

strings -a XLAAPRPT01.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a XLAAPRPT02.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a XLAAPRPT03.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a XLAAPRPT04.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a XLAAPRPT05.rtf | grep Header -->Applicable after Report Mode patch 9162536
strings -a XLAAPRPT06.rtf | grep Header -->Applicable after Report Mode patch 9162536

cd $AP_TOP/patch/115/publisher/templates/US/

strings -a APTBRPT01.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a APTBRPT02.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a APTBRPT03.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a APTBRPT04.rtf | grep Header -->Not Applicable after Report Mode patch 9162536
strings -a APTBRPT05.rtf | grep Header -->Applicable after Report Mode patch 9162536
strings -a APTBRPT06.rtf | grep Header -->Applicable after Report Mode patch 9162536

cd $XLA_TOP/patch/115/publisher/defs/
strings -a XLATBRPT.xml | grep Header

strings -a XLAAPRPT.xml | grep Header -->Applicable after Report Mode patch 9162536

cd $XLA_TOP/patch/115/xdf

strings -a xla_trial_balances_gt.xdf | grep Header

cd $AP_TOP/patch/115/xdf

strings -a ap_sla_invoices_transaction_v.xdf | grep Header

Query 23: Get the Trial Balance package versions

select *
from dba_source
where line =2
and name in ('XLA_TB_DATA_MANAGER_PVT','XLA_TB_REPORT_PVT',
'XLA_TB_AP_REPORT_PVT','XLA_TB_BALANCE_PKG');

< !--[if !supportLineBreakNewLine]-->
< !--[endif]-->

ATTENTION: Oracle Payables Community Join our growing Payables Community and learn from your peers and Oracle on how to address your unique issues in AP.

References:

Troubleshooting Accounts Payable Trial Balance (Open Account AP Balance Listing) issues in R12 [ID 553484.1]

Get Oracle Certifications for all Exams
Free Online Exams.com