Verifying Data Integrity for Accounts Receivable

This chapter provides an overview of accounts receivable integrity reports and discusses how to:

Run the A/R to G/L by Batch report.
Run the A/R to Account Balance by Account ID report.
Run the A/R to G/L Receipts report.

Understanding Accounts Receivable Integrity Reports

Integrity reports help identify any problems and inconsistencies with JD Edwards EnterpriseOne data so that you can resolve them and ensure that the systems remain in balance. Use these reports to verify that the accounts receivable data matches the general ledger data and that the accounts receivable transactions have the correct pay status.

Integrity reports do not have as of capability. That is, you cannot run the reports as of a certain date, such as the last day of the month, to ensure that the data was in balance at that time. You can run the report only as of the current date.

When you run Accounts Receivable integrity reports, you should run the reports:

When other users are not accessing the system.
Frequently, so that you do not risk compromising accounting data.
As needed during the conversion process at a new software installation site or during an initial learning period so that you can correct any errors promptly. Any integrity issues that exist prior to conversion will exist after conversion.

To reduce and resolve integrity issues:

Ensure that the network is stable and that communication failures are not causing problems.
Review software custom modifications because these can cause integrity problems.
When researching integrity problems, collect as much supporting documentation as possible. Such documentation is valuable if you need additional assistance from technical support to resolve an issue.
Use JD Edwards EnterpriseOne software to resolve integrity problems. In some cases, using other database utilities can worsen the problem.

Schedule integrity reports to run during off-peak hours, because no changes can be made to the data when integrity tests are running.

Running the A/R to G/L by Batch Report

This section provides an overview of the A/R to G/L by Batch report and discusses how to:

Print the A/R to G/L by Batch report
Set processing options for the A/R to GlL by Batch report

Understanding the A/R to G/L by Batch Report

The A/R to G/L by Batch integrity report (R03B701) compares the batch amount of the transactions in the F03B11 table with the batch amount of the corresponding records in the F0911 table. This integrity test:

Adds all gross amounts (posted) to the F03B11 table for each batch and accounts for variations based on tax types and tax amounts.
Accumulates all pay items that are posted to the F0911 table for each batch.
Compares the F03B11 amount with the F0911 amount. The net difference should be zero.
Compares the F03B11 amount with the automatic offset amount. The net difference should be zero.

Discrepancies are printed on the report.

The A/R to G/L by Batch Integrity report also verifies that the pay status is correct for each F03B11 transaction. The report validates the relationship between the pay status and open amount for these conditions:

The invoice does not have an open amount, but the pay status is not P (paid).
The invoice has an open amount, but the pay status is P (paid).

If any discrepancies exist, the invoice records print on the integrity report.

Before you run this program, post all invoice batches (batch type IB). The report does not process unposted or excluded batches. You can force the system to exclude a batch from processing by turning on the option on the Batch Overrides form.

Resolving Out-of-Balance Conditions

When the system detects that an invoice batch is not in balance, it prints the batch amounts that need to be researched on the report. These conditions might be reported:

The calculated amount, including taxes, of the invoice (F03B11) does not equal the distribution amount (F0911).

To determine which invoice is not in balance, run the Print Invoice Journal report and General Journal report for the batch, or review each invoice online and compare the invoice amount with the G/L distribution amount.

A discrepancy between these two amounts can occur when:
- The invoice was intentionally entered out of balance.
- The G/L distribution amount for an invoice was changed using the Journal Entries program (P0911).
- An invoice pay item was voided, but the void was not posted.
  
  Note. This condition occurs only when the invoice has multiple pay items. If the invoice has only one pay item and it is voided, but not posted, this report will not produce an out-of-balance condition. Because the system removes the posted code from the entire invoice when it is voided, the invoice will not be considered by the report. However, if only one of several pay items is voided, but not posted, the report will produce an out-of-balance condition.
  
  To correct this problem, verify that all transactions in the batch are posted. If all transactions are posted, locate the invoice that is out of balance and change the journal entry amount to correspond to the invoice amount. If the invoice amount is not correct, put the transaction in balance first, and then void it.
The distribution amount (F0911) does not equal the automatic offset amount (F0911).

This condition occurs only when the post program does not finish processing.

To correct this problem, determine the missing offset entry amount, create the balancing one-sided journal entry, and post the batch out of balance.

Either condition listed on the report can also occur if data records are damaged. Data records can be damaged for a variety of reasons, including hardware failure. To identify damaged data records, run queries over key fields (company, G/L date, document number, fiscal year, century, and so forth) for the F03B11 and F0911 tables to identify any null, blank, or special characters that should not be part of the value.

To correct this problem, repair the key field or remove the record from the table. Contact the database administrator for assistance in identifying and repairing damaged records.

Invalid Pay Status Conditions

If the system detects an invalid pay status, it prints the information on the right side of the report with the error message Invalid Pay Status. You must research the invoice to determine whether the pay status or open amount is incorrect. You use a data file utility to update the field with the correct value. Contact the database administrator for assistance.

Printing the A/R to G/L by Batch Report

Period End Processing (G03B21), A/R to G/L by Batch

Setting Processing Options for A/R to G/L by Batch (R03B701)

Processing options enable you to specify the default processing for programs and reports.

Dates

These processing options are used to specify either the number of days or the dates for invoice selection.

1. Number of days

Specify the number of days prior to, but not including, the system date to select posted invoice batches (IB). The system processes all posted invoice batches with a batch date on or after the number of days specified. For example, if today's date is June 15, 2008 and you specify 45, the system processes all posted invoice batches from May 01, 2008 to June 15, 2008. If you leave this processing option blank and do not specify values in the Date Range processing options, the system process all posted invoice batches.

Do not enter a value for this processing option if you specify a date range.

Beginning date and Ending date

Specify the beginning and ending dates of the date range to use to select invoice batches (IB) for processing. The system selects invoice batches with a date between the dates specified, including the dates that you enter.

Do not specify a date range if you entered a value in the Number of days processing option.

Running the A/R to Account Balance by Account ID Report

This section provides an overview of the A/R to Account Balance by Account ID report and discusses how to:

Print the A/R to Account Balance by Account ID report.
Set processing options for the A/R to Account Balance by Account ID report.
Set data selection and sequence for the A/R to Account Balance by Account ID report.

Understanding the A/R to Account Balance by Account ID

For each offset account by account ID, the A/R to Account Balance by Account ID report (R03B707) compares amounts updated in the Account Balances table (F0902) with posted amounts in these transaction tables:

F03B11
F03B112
F03B13
F03B14

The report compensates for unposted transactions in the Invoice Revisions table.

The report considers these Accounts Receivable accounts:

A/R offset accounts (AAI item RC).
Write-off accounts (AAI item RA).
Deduction accounts (AAI item RN).
Discount taken accounts (AAI item RKD).
Bank accounts (AAI item RB).
Delinquency fee accounts (AAI items RFC and RFD).
Gain/loss accounts (AAI items RG and RL).
Alternate currency gain/loss accounts (RY and RZ).
Rounding account (AAI item R8).

The system updates each accounts receivable transaction (F03B11, F03B13, and F03B14) with the appropriate account ID for the transaction at the time it is generated.

When you run the report, the system accumulates amounts from the appropriate accounts receivable transaction tables by:

Account ID
Company
Fiscal year
Century
G/L period number
Base currency code

The system prints the result with the corresponding F0902 account balance.

Note. The system uses different tables to compute the amounts for the various A/R accounts. For example, the system uses amounts from the F03B11, F03B112, and F03B14 tables to compute the amount for the A/R trade account, but uses only the amount from the F03B13 table to compute the amount for the bank account.

This report does not consider these document types in the F03B11 table:

R1 (draft invoices)
RU (unapplied receipts)
RB (chargeback invoices)
R5 (deduction)

Resolving Out-of-Balance Conditions

When the system detects discrepancies between the amounts in the transaction tables (F03B11, F03B112, F03B13, and F03B14) and the amount in the Account Balances table (F0902), it prints a difference amount. Differences might indicate an out-of-balance condition that should be researched. Run this integrity reports and resolve any discrepancies.

A/R Invoices to G/L by Batch Integrity report (R03B701).
A/R to G/L Receipts Integrity by Batch report (R890911BI).
Repost Account Ledger (R099102).

Typically, if the other integrity reports are clean, differences occur for these circumstances only:

A journal entry was posted to an accounts receivable account.

If you post a journal entry to an account that is also designated in the account receivable AAIs, the system updates the Account Balances table (F0902). Because the journal entry was created outside of the JD Edwards EnterpriseOne Accounts Receivable system, no corresponding transaction exists in the accounts receivable transaction tables, and an out-of-balance condition is reported by the system for the account.

To correct the problem, void the journal entry and re-enter it for the correct account.
An account is shared by more than one system.

If an accounts receivable account, such as the bank account, is also updated by the JD Edwards EnterpriseOne Accounts Payable system, the F0902 account balance will not equal the accounts receivable transaction balance for this account.

To correct this problem, use processing options to exclude the account from the report.
The post program did not finish processing and differences were not adjusted.

If the post program does not finish processing, transaction records might reflect a posted status even though the corresponding F0902 account balance table was never updated. You can research account differences created in this situation by running the Repost Account Ledger program (R099102).
The fiscal date pattern was changed.

If you change the date pattern associated with the fiscal year, the period numbers on accounts receivable records will no longer be in synchronization with the period number on the Account Balances records (F0902), and an out-of-balance condition will be reported.

To correct the problem, you must update the period number in the accounts receivable tables (F03B11, F03B13, F03B14) to match the new fiscal date pattern. Contact the database administrator for assistance.
An invoice was voided in a previous period, but the report was run for the current period.

Consider this example:
You enter two invoices for 100 each that have G/L dates in July (period 7). When you post the invoices, the F0902 table has a balance of 200 in period 7.
You determine that only one invoice should have been entered, so you void one of the invoices. However, instead of voiding the invoice in July, you void it using a G/L date in June (period 6). The invoice record in the F03B11 table now has a G/L date of June instead of July due to the void.
You run the report with the processing option completed for period 7.

The report shows a discrepancy of 100, because the F0902 table has a balance of 200 for July, but only one invoice record exists in the F03B11 table with a G/L date in July for 100. The -100 (credit) in June is not considered, because you selected period 7 in the processing options.

Under these circumstances, although the report shows an out-of-balance condition, the records are actually in balance. To correct the report, either change the processing option to run the report for the previous period (6), or leave that option blank to run the report across all periods.

The same problem could occur between fiscal years if an invoice is voided in a prior fiscal year, but the report is run for the current fiscal year.
VAT adjustments for discounts taken or write-offs occurred when the invoice was paid.

Depending on how tax rules and processing options in the General Ledger Post Report program (R09801) are set up, the system might make adjustments to the discounts taken or write-off accounts for VAT adjustments when receipts are posted.

If you make VAT adjustments, you must manually reconcile the accounts for discounts taken and write-offs, or exclude these accounts from printing on the report. VAT adjustments do not cause an out-of-balance condition for any other accounts listed on the report.
Data records are damaged.

This error can occur with any A/R integrity report for a variety of reasons, including hardware failure. To identify damaged data records, run queries over key fields (company, G/L date, document number, fiscal year, century, and so forth) for each table to identify any null, blank, or special characters that should not be part of the value.

To correct this problem, repair the key field or remove the record from the table. If the record is in the F0902 table, you might need to repost the F0911 table.

Contact the database administrator for assistance in identifying and repairing damaged records.
Data records were not converted properly.

If you do not convert all the records for the JD Edwards EnterpriseOne Accounts Receivable system (for example, you convert the general ledger and invoice records, but not receipts), the report will produce an out-of-balance condition.

To assist you in identifying true integrity issues, run this integrity report after conversion to use as a basis of comparison later if you are not able to convert all accounts receivable records.

Printing the A/R to Account Balance by Account ID Report

Period End Processing (G03B21), A/R to Account Balance by Account ID.

Setting Processing Options for A/R to Account Balance by Account ID (R03B707)

Processing options enable you to specify the default processing for programs and reports.

Select

These processing options specify data selection from the F03B11 table.

Account ID	Use this processing option to limit the report to the account specified. You designate the account by entering the system-assigned short account ID. The short account ID is eight digits; therefore, you must include any leading zeros for the account to be valid. For example, if the short account ID for the Trade Receivables Account 1.1210 is 6251, you enter 00006251. If you leave this field blank, the system prints all applicable accounts as determined by other processing option values.
Century	Use this processing option to limit the report to records with the specified century. The system includes records with a century equal to and greater than the value that you specify. For example, if you enter 19, the system includes records from 1900 through the current year. If you enter 20, the system includes records from 2000 through the current year.
Fiscal Year	Use this processing option to limit the report to records with the specified fiscal year. The system includes records with a fiscal year equal to and greater than the value that you specify. To use this processing option, you must additionally specify a value for the Century processing option, or the system ignores the value entered. For example, if you specify 20 for the Century and 05 for the Fiscal Year, the system includes all records from 2008, as defined by the fiscal date pattern.
Period Number	Use this processing option to limit the report to records with the specified period number. The system includes records with a period equal to and greater than the value you specify. To use this processing option, you must additionally specify values in for Fiscal Year and Century processing options, or the system ignores the value entered. For example, if you specify 20 for the Century, 05 for the Fiscal Year, and 6 for the Period Number, the system includes all records from the sixth and subsequent periods in 2008 through any date after 2008, as defined by the fiscal date pattern.

Process

These processing options specify the accounts to verify for integrity purposes and to include on the report.

Include Trade Account from F03B11 and F03B14	Specify whether to include the trade accounts from the F03B11 and F03B14 tables, as designated by the values in the RPAID and RZAID fields, respectively, on the report. The system also includes the chargeback trade account (RZAIDC) from the F03B14 table on the report. Blank: Do not include the trade accounts. 1: Include the trade accounts.
Include Discount Account	Specify whether to include the discount taken account (RZAIDD) from the F03B14 table on the report. Values are: Blank: Do not include the discount taken account. 1: Include the discount taken account.
Include Deduction Account	Specify whether to include the deduction account (RZDAID) from the F03B14 table on the report. Values are: Blank: Do not include the deduction account. 1: Include the deduction account.
Include Write-Off Account	Specify whether to include the write-off account (RZAIDW) from the F03B14 table on the report. Values are: Blank: Do not include the write-off account. 1: Include the write-off account.
Include Gain/Loss Account	Specify whether to include the gain/loss account (RZAIDT) from the F03B14 table on the report. Values are: Blank: Do not include the gain/loss account. 1: Include the gain/loss account.
Include Alternate Currency Gain/Loss Account	Specify whether to include the alternate currency gain/loss account (RZAIDA) from the F03B14 table on the report. Values are: Blank: Do not include the alternate currency gain/loss account. 1: Include the alternate currency gain/loss account.
Include Bank Account	Specify whether to include the bank account (RYGLBA) from the F03B13 table on the report. Values are: Blank: Do not include the bank account. 1: Include the bank account.
Include Rounding Account	Specify whether to include the rounding account (RZRASI) from the F03B14 table on the report. Blank: Do not include the rounding account. 1: Include the rounding account.

Display

This processing option specifies the level of detail to include on the report.

Report Mode

Specify whether to include the bank account (RYGLBA) from the F03B13 table on the report. Values are:

Blank: Do not include the bank account.

1: Include the bank account.

Setting Data Selection and Sequence for A/R to Account Balance by Account ID

Do not change the data selection or data sequence for this report. Use the processing options to limit or specify report criteria.

Running the A/R to G/L Receipts Report

This section provides an overview of the A/R to G/L Receipts report and discusses how to:

Print the A/R to G/L Receipts report.
Set processing options for A/R to G/L Receipts report.

Understanding the A/R to G/L Receipts Report

The A/R to G/L Receipts integrity report (R890911BI) verifies that each batch of posted records in the F03B14 table is in balance with the corresponding G/L receipt amounts in the F0911 table, as well as with the automatic entry (AE record) to the A/R trade account. The system reports discrepancies only.

Note. Some instances occur in which you cannot programmatically confirm with certainty that an integrity issue does not exist. The report might print an out-of-balance condition that does not exist. It is the responsibility to investigate the reported issue and, if warranted, correct it. If the program did not report these uncertain situations, an integrity issue could potentially be unreported.

Important! This program includes a processing option to create a balancing entry in the F0911 table if a discrepancy occurs. However, it is recommended that you use this option only for currency conversion. Prerequisites exist for using this option.

See Running Preconversion Integrity Reports.

Before you run this program, post all receipt batches (batch type RB). The program does not process unposted or excluded batches. You can cause the system to exclude a batch from processing by turning on the option on the Batch Overrides form.

Resolving Out-of-Balance Conditions

When the system detects that an A/R receipt batch is not in balance, it prints the batch amounts that need to be researched on the report. These conditions might be reported:

A/R amount (F03B14) does not equal G/L amount (F0911)

This condition occurs only when the post program does not finish processing.

To correct this problem, run the post program again to remove all system-generated entries. When the system detects that the batch is not in balance, it deletes all entries made to the F0911 table. After the entries are removed, run the post program again (a third time) to create entries correctly.
A/R amount (F03B14) does not equal the automatic offset amount (F0911).

This condition occurs only when the post program does not finish processing.

To correct this problem, run the post program again to remove all system generated entries. When the system detects that the batch is not in balance, it deletes all entries made to the F0911 table. After the entries are removed, run the post program again (a third time) to create entries correctly.

Either condition listed on the report can also occur if data records are damaged. Data records can be damaged for a variety of reasons, including hardware failure. To identify damaged data records, run queries over key fields (company, G/L date, document number, fiscal year, century, and so forth) for the F03B14 and F0911 tables to identify any null, blank, or special characters that should not be part of the value.

To correct this problem, repair the key field or remove the record from the table. Contact the database administrator for assistance in identifying and repairing damaged records.

Printing the A/R to G/L Receipts Report

Period End Processing (G03B21), A/R to G/L Receipts.

Setting Processing Options for A/R to G/L Receipts (R890911BI)

Processing options enable you to specify the default processing for programs and reports.

Process

This processing options specifies the mode in which the system runs the program.

1. Update

Specify the mode in which the system runs the program. Values are:

Blank: Proof Mode. The system does not update the F0911 table.

1: Final Mode. The system updates the F0911 table.