Overview of Account Balance to Transaction Integrity Report (R09705)

The Account Balance to Transaction Integrity Report (R09705) assists in locating out-of-balance conditions in the system by identifying imbalances between the Account Balance F0902 and Account Ledger F0911 tables on a period-by-period basis.

Scope

This document provides an overview of the Account Balance to Transaction Integrity Report (R09705) including the overview, program functionality, setup, information on reviewing and resolving the integrity report, reasons for integrity and frequently asked questions.

Details

Functionality

The Account Balance to Transactions report reviews each account balance in the Account Balance (F0902) table, verifies the amounts against the total of all posted transactions in the Account Ledger (F0911) table, and prints any out-of-balance conditions by period. It summarizes F0911 totals by ledger type, account number, century, fiscal year and period number.

It reads the F0902 first and if an F0902 record exists, it compares the balance to the total of the corresponding posted F0911 transactions. This report also identifies Account Balance (F0902) amounts that do not have supporting Account Ledger (F0911) detail.

If no F0902 record exists for an account, the program will not check the F0911 for that account.

Enhancement Note: In EnterpriseOne release 8.10 and later, Enhancement Bug 10815197 has redesigned the R09705 Integrity Report with the following additions:

Maximize Performance
Clears Invalid Period Balance in F0902
Remove unwanted processing of Inactive accounts
Enhanced data selection criteria

Releases Xe, ERP 8.0 and 8.9:

This integrity only prints the imbalances found between the F0902 and F0911 tables and does not perform any updates.

Release 8.10 and later:

If this integrity report does not locate F0911 records with the same account number, period number, fiscal year, and ledger type as the F0902 records, it deletes the invalid period balance from the F0902 table, if the following conditions apply:

Processing option of R09705 is set to clear invalid period balances.
The affected ledger type has the Prevent Direct Balance Update check box selected in the Ledger Type Master Setup program (P0025). For more information on setting up Ledger Types, refer to Overview of Ledger Types Setup (P0025).

If the Account Balance to Transactions report locates F0911 records with corresponding F0902 records, the system does not delete invalid period balances from the F0902 table, regardless of the processing option and ledger type master setup.

Setup

Processing Options

Releases Xe, ERP 8.0 and 8.9:

Prior to 8.10, the only processing option available is for the selection of a specific ledger type (if not specified in the data selection).

Ledger Type Tab

Ledger Type: If comparing a units ledger, enter the amounts ledger to use to retrieve the transaction records.

Release 8.10 and later:

Select Tab

Exclusive Data Selection Values:

1. Company

Specify the company number to select records from the F0902 table.
A default of blank will select all companies.

2. Ledger Type

Specify the ledger type (UDC table 09/LT) to select records from the F0902 table.
A default of blank will select all amount ledger types in the F0902 table and excludes all unit ledger types in the same table.

NOTE: To process records for unit ledger types, submit the report for each unit ledger type individually. Enter a specific unit ledger type in this processing option and enter the associated amount ledger type in the Amount Ledger Type processing option. For example, if you want to process unit ledger type records from the AU ledger and the associated amount ledger is AA, specify AU for the Ledger Type processing option and AA for the Amount Ledger Type processing option under the Units tab.

3. Fiscal Year

Specify the fiscal year to select records from the F0902 table. Enter the last two digits of the fiscal year. For example, enter 06 for 2006.
A default of blank will select all fiscal years.

4. Beginning Period Number and 5. Ending Period Number

Specify the beginning and ending periods to select records from the F0902 table.
If you leave the Beginning Period Number processing option blank, the system selects all records with a period number that is prior to the period that is specified in the Ending Period Number processing option.
If you leave the Ending Period Number processing option blank, the system selects all records with a period number after the period that is specified in the Beginning Period Number processing option.
If you leave both processing options, the system selects all period numbers.

6. Business Unit

Specify the business unit to select records from the F0902 table.
A default of blank will select all business units for the company that is specified in the Company processing option.

7. Beginning Object Account and 8. Ending Object Account

Specify the beginning and ending object account numbers in a range of object accounts to select records from the F0902 table.
If you leave the Beginning Object Account processing option blank, the system selects all records with an object account that precedes the object account that is specified in the Ending Object Account processing option.
If you leave the Ending Object Account processing option blank, the system selects all records with an object account after the account that is specified in the Beginning Object Account processing option
If you leave both processing options blank, the system selects all object accounts.

Options Tab

1. Clear invalid period balances

Enter '1' to delete period amounts for invalid records in the F0902 table and print the invalid records on the report. The system deletes period amounts for invalid records only if no records exist in the F0911 table and the corresponding ledger type has the Prevent Direct Balance Update option selected in the Ledger Type Master Setup program (P0025).
A default of Blank will not delete period amounts for invalid records in the F0902 table and prints the invalid records on the report.

NOTE:

Example 1: If an account in the F0902 table for ledger type AA has a balance of 600 in period 6 of fiscal year 2007 and no records for the account exist in the F0911 table for the same period, fiscal year and ledger type, the system deletes the F0902 amount only if the AA ledger type has the Prevent Direct Balance Update check box checked. In this situation, the system deletes the period balance from the F0902 and prints the out-of-balance amount and the message Balance Cleared on the report.
Example 2: If an account in the F0902 table for ledger type AA has a balance of 600 in period 6 of fiscal year 2007 and the same account has a balance of 400 in the same period, fiscal year, and ledger type in the F0911 table, the system does not delete the amount from the F0902 table because the account has a balance in the F0911 table for the same period number, fiscal year and ledger type, regardless of whether the Prevent Direct Balance Update option is selected.

2. Exclude inactive accounts

Enter '1' to exclude inactive accounts when comparing period balances in the F0902 table to corresponding records in the F00911 table.
A default of Blank would include all the inactive accounts for comparison of F0902 with F0911.

NOTE: The Exclude inactive accounts processing option will also assist in decreasing the processing time by excluding the processing of inactive F0901 account records with PEC = 'I'.

Units Tab

1. Amount Ledger Type

Use this processing option in conjunction with the Ledger Type processing option to specify the amount ledger type to select records from the F0902 table. Specify the amount ledger type (UDC table 09/LT) that is associated with the unit ledger type identified in the Ledger Type processing option.
A default of Blank will select ledger type AA.

Data Selection

Releases Xe, ERP 8.0 and 8.9:

In releases prior to 8.10, Compare Account Balance to Transactions (R09705) allows data selection only for limited criteria:

Ledger Type
Company
Fiscal Year

All data selection for these prior releases are based on the Account Balances (F0902) table.

NOTE: While the report allows data selection on other items, such as a specific period or Account ID, data selection on any item other than Ledger Type, Company or Fiscal Year is not recommended as the results of the Integrity Report may be misleading.

Release 8.10 and later:

Beginning with the 8.10 release, a new workfile Account Balance Compare Exceptions (F09UI016) has been created which is used by the UBE report to assist with more efficient processing. The data selection is changed to be based on the F09UI016 instead of the F0902. However, any defined data selection is completely ignored. All items that can be selected in order to improve performance or narrow selection criteria must now be chosen via the R09705 processing options only:

Select Tab

Company
Ledger Type
Fiscal Year
Beginning and Ending Period Number
Business Unit
Beginning and Ending Object Accounts

The first three processing options on the Select tab would be the standard data selection in prior releases (Company/Ledger Type/Fiscal Year). The remaining items enhance a user's ability to narrow selection criteria in order to decrease processing time of this Integrity Report.

With performance 9.1 Bug 17533200 applied or at release 9.2 and later:

Additional setup is required to reset cache as the R09705 is running otherwise ERROR: Memory allocation failed could occur.

1. In EnterpriseOne System Control (P99410) verify data item TBLREFR with the radio button is set to Yes otherwise this row needs to be added.
2. In UDC 00/RF Add the following UDC: Code R09705, Description 01 of Compare Account Balances To Transactions, Description 02 of F0901, Special Handling Code of 11.

NOTE: Do not include any ledger types for which Account Ledger (F0911) detail does not exist (such as BA) or every Account Balance (F0902) record will be listed on the report for that ledger type.

Reviewing the Report

1* The integrity report does not find supporting F0911 records with the same account number, period number, fiscal year, and ledger type as the F0902 records, hence it shows the Account Ledger F0911 as Blank whereas F0902 Account Balances exist.

2* The integrity report verifies and prints a mismatch between balances of the F0902 and corresponding F0911 records for the following criteria:

If F0902 Amount > F0911 Amount
If F0902 Amount < F0911 Amount

3* The report does find a F0902 record for the account 91009.1110.BEAR but with F0902 amounts updated as zero for multiple periods, whereas the corresponding F0911 transactions have accumulated balances as non-zero, hence the report prints the discrepancy. If no F0902 record existed for this account, the program would not have checked the F0911 for the same account, hence it would not print a discrepancy in this case.

Resolving the Integrity

The first step in resolving the issues on the Integrity Report would be to export and review the data to compare the amounts from the F0911 and F0902 tables as follows:

Filter the F0911 table on the following fields:

Posted Code (GLPOST), Company (GLCO), Account ID (GLAID), Ledger Type (GLLT), Century (GLCTRY), Fiscal Year (GLFY), Period Number (GLPN)

Append Subledger Value (GLSBL), Subledger Type (GLSBLT) to the above selection, if applicable.

Filter the F0902 table on the following fields:

Company (GBCO), Account ID (GBAID), Ledger Type (GBLT), Century (GBCTRY), Fiscal Year (GBFY)

Append Subledger Value (GBSBL), Subledger Type (GBSBLT) to the above selection, if applicable.

1* To correct this error, select the Prevent Direct Balance Update option in the Ledger Type Master Setup program (P0025) for any ledger type that should not allow data entries directly to the F0902 table. Then run the Account Balance to Transactions report (R09705) with the Clear Invalid Period Balances processing option set to '1' to delete invalid balances from the F0902 table.

2* and 3* Run the R099102 Repost Account Ledger program Overview Repost Account Ledger Integrity Report (R099102) to update existing F0902 amounts with correct F0911 accumulated amounts.

Some typical discrepancies, causes, and possible resolutions of similar scenarios are listed as follows:

Discrepancies	Reasons	Resolutions
Account Balance and Detail Columns are Out of Balance	Data entries were damaged by improper void or revision processes.	If data entries are damaged in the F0911 table, run a data utility program. If data entries are damaged in the F0902 table, run the R099102 Repost Account Ledger program.
Account Balance and Detail Columns are Out of Balance	An entry was not properly created by the conversion/interface programs.	Research the error and add the missing entries. Verify the validity of Account Ledger F0911 records and correct the conversion/interface programs.
Balance Column Has an Amount and Detail Column Does Not Have an Amount	This error appears if entries were made directly to the F0902 table.	To correct this error, select the Prevent Direct Balance Update option in the Ledger Type Master Setup program (P0025) for any ledger type that should not allow data entries directly to the F0902 table. Then run the Account Balance to Transactions report with the Clear Invalid Period Balances processing option set to 1 to delete invalid balances from the F0902 table.
Date Pattern is Incorrect	This error appears if changes were made to the fiscal date pattern after journal entries were entered in the system.	If the date pattern of the prior company is different from the new company, run the R099102 Repost Account Ledger program with the processing option set to print only the report. After reviewing the report, run the R099102 program with the processing option set to recalculate the amounts to correct the fiscal period and year balances.

To prevent future out-of-balance conditions, consider:

Placing security on the Work With Batches form to prevent improper changes.
Assigning responsibility for out-of-balance posting to one individual.
Allowing only one user to void, change, or delete entries.

NOTE: To prevent inaccurately deleting records from the F0902 table, ensure that you select the 'Prevent Direct Balance Update' check box in the Ledger Type Master Setup program (P0025) only for those ledger types that should not allow data entries directly to the F0902 table. Certain ledger types might allow direct updates to the F0902 table, such as the BA ledger or other budgeting ledger types.

Frequently Asked Questions

Question 1: The R09705 integrity report is showing out of balance conditions in a multi-currency environment. After exporting F0911/F0902 data as per above instructions and during analysis found that the F0902 table has duplicate records for each fiscal year, one with CRCD=USD and one with CRCD blank. How did this happen and how can it be fixed?

Answer 1: This scenario may happen because of following reasons:

One of the possibilities is that an account which was defined as a monetary account earlier (in order to lock that account so that it can only accept transactions in that currency) was later on changed to non-monetary account (in order to book a foreign transaction to the account) by clearing the currency code at F0901 Account Master level. Please refer to the Common Issues Question 1 section in Frequently Asked Questions on the Monetary Account Valuation Report (R09415) to verify and rectify if the currency code for an account has been modified since inception.
Alternatively, the same may happen in case "Post Balance by Currency" flag in Company Constants has been turned on and off at least once. Please refer to Currency Fields F0911/F0902 Tables section in Overview of Post Balances by Currency in order to make sure that the corresponding tables have the correct currency codes populated in F0901, F0911 and F0902 tables. Further, refer to Changing from Summarized Balances to Balances by Currency section in Overview of Post Balances by Currency on how to fix the erroneous records.

Question 2: Why were the processing options changed for the R09705 report beginning in 8.10?

Answer 2: In releases prior to 8.10, Compare Account Balance to Transactions ( R09705) allowed data selection only for limited criteria. This could be by Ledger Type, Company, and, depending on the software level of R09705, may also include data selection by Fiscal Year ( a performance enhancement change so that R09705 would not have to query every fiscal year). All data selection for these prior releases was also based on the Account Balances ( F0902) table. While the report allowed data selection on other items, such as a specific period or Account ID, data selection on any item other than Ledger Type, Company or Fiscal Year was not recommended as the results of the UBE would be misleading. Also prior to 8.10, the only processing option available was for the selection of a specific ledger type (if not entered in the data selection).
Beginning with 8.10, data selection for R09705 was changed to be based on the Account Balance Compare Exceptions table ( F09UI016) table instead of the F0902, and is completely ignored. All items that can be selected in order to improve performance or narrow selection criteria must now be chosen via the R09705 processing options.

Question 3: What is the difference in functionality between the Account Balance to Transaction Report (R09705) and Repost Account Ledger Report (R099102)?

Answer 3: Both R09705 and R099102 read and compare the Account Ledger (F0911) and Account Balances (F0902) files for accumulated ledger amounts but have following differences:

The R09705 reads the F0902 file first and then F0911. If no F0902 record exists for an account, it goes on to the next account. If it finds the F0902 record, then it looks for corresponding F0911 record, whereas R099102 reads the F0911 file first. If no F0911 record exists for an account, it goes on to the next account. If its finds the F0911 record, then it looks for corresponding F0902 record.
The R09705 in prior releases (Xe, ERP8.0 and 8.9) had no ability to update amounts. Beginning in release 8.10 and subsequent, it can clear F0902 invalid balances, which do not have a corresponding F0911 records. The R099102 can be run in final mode to update F0902 Balances with posted F0911.

Example: If account 1.1110.BEAR has an AA ledger amount in the F0911 of $100 and an amount in the F0902 for $200 for period 8 of 2008, this condition would appear as an exception on both the R09705 and R099102 reports. But to correct this, only the Repost Account Ledger would update the F0902 amount with the F0911 amount ($100).

For more information on R009102, refer to Overview Repost Account Ledger Integrity Report Overview of Repost Account Ledger Integrity Report (R099102).

Question 4: Why is the Account Balance to Transaction Report (R09705) printing "No Data Selected" when no integrity issue is found rather than just a blank page?

Answer 4: Beginning with 8.10 release, the data selection has been changed to be based on the Account Balance Compare Exceptions table (F09UI016) table instead of the Accounts Balances (F0902), but is still completely ignored. All items that can be selected in order to improve performance or narrow selection criteria must now be chosen via the R09705 processing options.

The R09705 report throws "No Data Selected" only when the report does not find any data in the F09UI016 work file which is written only when there are discrepancies between F0902 and F0911. Hence if no discrepancies exist between F0902 and F0911, the system does not create any records in the F09UI016 work file, hence report prints the error message.

For more information on the same, you may refer to Bug 10998040 which has been returned by Development as working as designed.

Question 5: Why is R09705 failing with ERROR: Memory allocation failed?

Answer 5: Refer to Setup and required performance setup.