29 Verifying Data Integrity for Accounts Receivable

This chapter contains the following topics:

29.1 Understanding Accounts Receivable Integrity Reports and Programs (Release 9.2 Update)

Integrity reports and programs 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 and programs 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.

Follow these guidelines when running the Accounts Receivable integrity reports:

  • Run the reports when other users are not accessing the system.

  • Run the reports frequently, so that you do not risk compromising accounting data.

  • Run the reports 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.

29.2 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 G/L by Batch report

29.2.1 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.

29.2.1.1 Resolving Out-of-Balance Conditions

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

  1. 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 does not produce an out-of-balance condition. Because the system removes the posted code from the entire invoice when it is voided, the invoice is not considered by the report. However, if only one of several pay items is voided, but not posted, the report produces 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.

  2. 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.

29.2.1.2 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.

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

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

29.2.3 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.

29.2.3.1 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.

29.3 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.

29.3.1 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 when 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)

29.3.1.1 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 does 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 are no longer in synchronization with the period number on the Account Balances records (F0902), and an out-of-balance condition is 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 produces 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.

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

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

29.3.3 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.

29.3.3.1 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.

29.3.3.2 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.

29.3.3.3 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.

29.3.4 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.

29.4 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.

29.4.1 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 and 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.

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.

29.4.1.1 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.

See

29.4.2 Printing the A/R to G/L Receipts Report

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

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

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

29.4.3.1 Process

This processing option 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.

29.5 Working with the Revenue Recognition Invoice To Account Ledger Program (Release 9.2 Update)

This section provides an overview of the Revenue Recognition Invoice to Account Ledger program (P03B7116) and discusses how to:

  • Set processing options for the Revenue Recognition Invoice to Account Ledger program (P03B7116).

  • Review integrity issues between revenue recognition invoices and the account ledger.

29.5.1 Understanding the Revenue Recognition Invoice to Account Ledger Program (P03B7116)

Use the Revenue Recognition Invoice to Account Ledger program (P03B7116) to identify integrity issues for invoices in the revenue recognition process. The system searches for invoices with single and multiple pay items, domestic and foreign invoices, invoices with cost of goods sold (COGS), credit memos, and invoices with VAT (value-added tax).

From the P03B7116 program, you can access the Revenue Recognition program (P03B116) to immediately correct the out-of-balance revenue recognition invoice issues. From the P03B7116 program, you can also access the Batches Out of Balance program (P007032) to search any batch in error that contains revenue recognition invoices.

The P03B7116 program searches for the following integrity errors:

  • For invoices that are ready to be recognized: Invoice amounts are not equal to or are missing Performance Liability Account (PLA) entries in the Account Ledger table (F0911).

  • For invoices with a COGS value that is ready to be recognized: COGS amounts are not equal to or are missing COGS PLA entries in the F0911 table.

  • Recognized revenue amounts are not equal to the G/L entries for the revenue accounts for the invoice, or the entries are missing from the F0911 table.

  • Recognized COGS amounts are not equal to the G/L entries for the COGS accounts for the invoice, or the entries are missing from the F0911 table.

  • Recognized revenue amounts are not equal to the offsetting PLA and PLA adjustment accounts for the invoice in the F0911 table, excluding AE entries.

  • Recognized COGS amounts are not equal to the offsetting COGS PLA and COGS PLA adjustment accounts for the invoice in the F0911 table, excluding AE entries.

  • For invoices with COGS entries: Any invoice where the percent of revenue recognized is not equal to the percent of COGS recognized.

  • Invoices that exist in the Customer Ledger Tag table (F03B11T) with the Revenue Flag set to 1 are missing records in the Revenue Recognition Invoice table (F03B116).

29.5.2 Forms Used to Review Integrity Errors Between Revenue Recognition Invoices and the Account Ledger

Form Name Form ID Navigation Usage
Revenue Recognition to Account Ledger W03B7116A Revenue Recognition Processing (G03B18), Revenue Recognition Invoice to Account Ledger Review integrity issues between revenue recognition invoices and the account ledger.
Work with Invoice Recognition W03B116B Select a record on the Revenue Recognition to Account Ledger form and click Select; or select the Document Number hyper link for the record. Review G/L distribution information to identify integrity issues for revenue recognition invoices.
Batches Out of Balance W007032A Select a record on the Revenue Recognition to Account Ledger form and select Batch Out of Balance from the Form menu. Search for out-of-balance batches that contain revenue recognition invoices.

29.5.3 Setting Processing Options for the Revenue Recognition Invoice To Account Ledger Program (P03B7116)

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

29.5.3.1 Defaults

These processing options specify the default values that the system displays on the Revenue Recognition Invoice to Account Ledger form. You can change any of these values when you access the form.

1. Document Company

Use this processing option to specify the default document company the system displays on the Revenue Recognition Invoice to Account Ledger form.

2. From Date

Use this processing option to specify the default from date the system displays on the Revenue Recognition Invoice to Account Ledger form.

3. Thru Date

Use this processing option to specify the default through date the system displays on the Revenue Recognition Invoice to Account Ledger form.

4. Date Type

Use this processing option to specify the default date type (invoice date or GL date) the system selects on the Revenue Recognition Invoice to Account Ledger form.

5. Prior Days

Use this processing option to specify the default prior days value the system displays on the Revenue Recognition Invoice to Account Ledger form.

29.5.3.2 Versions

1. Batches Out of Balance (P007032)

Use this processing option to specify the version of the Batches Out of Balance program (P007032) that the system uses when you select Batch Out of Balance from the Form menu on the Revenue Recognition Invoice to Account Ledger form.

If you leave this processing option blank, the system uses version ZJDE0001 as the default.

2. Revenue Recognition (P03B116)

Use this processing option to specify the version of the Revenue Recognition program (P03B116) that the system uses when you select a record on the Revenue Recognition Invoice to Account Ledger form.

If you leave this processing option blank, the system uses version ZJDE0001 as the default.

29.5.4 Reviewing Integrity Issues Between Revenue Recognition Invoices and the Account Ledger

Access the Revenue Recognition Invoice to Account Ledger form.

29.5.4.1 Header

Use the fields in the header to search for integrity issues.

Prior Days

Use this field to specify the number of days prior to the current date that the system uses to determine the date range of records to include in the integrity check.

If you specify the number of days, the system selects records beginning with the date calculated when you subtract the number of days from the current date, and uses the current date as the ending date for the record selection. If you leave this field blank, the system uses the dates specified in the From Date and Thru Date fields.

29.5.4.2 Grid

For each error description, the system displays the document number, document company, and document type, summarized revenue recognition amounts (recognized amount, remaining amount, and amount not recognized), ledger amount, and variance amount.

Error Description

The system displays a detailed error message. Values are:

  • The amounts on invoices that are ready to be recognized are not equal to the PLA entries in the F0911 table, or the PLA entries are missing.

  • The COGS amounts on invoices that are ready to be recognized are not equal to the COGS entries in the F0911 table, or the COGS entries are missing.

  • Recognized revenue amounts are not equal to the G/L entries for the revenue accounts for the invoice.

  • Recognized COGS amounts are not equal to the G/L entries for the revenue accounts for the invoice.

  • Revenue amounts that have been recognized to not match the offsetting Performance Liability amounts.

  • COGS amounts that have been recognized to not match the offsetting Performance Liability amounts.

  • Percent of revenue recognized is not equal to the percent of COGS recognized.

  • Records that exist in F03B11T are missing in F03B116.

Variance Amount

The system displays a value that is the difference between the invoice amount or COGS amount and the ledger amount of the invoice.