Customer/Supplier Balance Report (R7404003, R7403B001, R7404002)

Purpose

This document details the set up and use of the Customer/Supplier Balance report in JD Edwards EnterpriseOne Accounts Receivable and Accounts Payable.

Scope

This document is intended for users who are setting up and using the Customer/Supplier Balance report.

Details

Overview of the Customer/Supplier Balance Report

The Customer/Supplier Balance report (R7404002) is created from a worktable, Customer/Supplier Balance Worktable (A/P and A/R) (F74412). The table is used for both Accounts Payable and Accounts Receivable reporting and contains information about customer and supplier transactions. The F74412 table is built by running the Generate Supplier Balance (R7404003) and the Generate Customer Balance (R7403B001) programs.

The Customer/Supplier Balance Worktable (A/P and A/R) (F74412) table contains information collated from the following tables when running the Generate Supplier Balance (R7404003) and the Generate Customer Balance (R7403B001):

Two demo versions of the Customer Balance/Supplier report (R7404002) are then provided to run over the F74412 worktable data, one to provide a Customer Balance report (XJDE0001) and one to provide a Supplier Balance report (XJDE0002), depending on the R7404002 processing option set up.

Generate Supplier Balance (R7404003) and Generate Customer Balance (R7403B001)

The way you select dates and group information in the Customer/Supplier Balance Worktable (A/P and A/R) (F74412) table determines how information is displayed on the Customer Balance Report and the Supplier Balance Report. You can group information in the F74412 table as follows:

• By G/L offset and customer or supplier number.
• By G/L offset and customer parent or supplier parent number.
• By trade account and customer or supplier number.
• By trade account and customer parent or supplier parent number.
• By customer or supplier number only.
• By customer parent or supplier parent number only.

To specify how information is grouped in the F74412 table, you set the processing options for the Generate Supplier Balance (R7404003) and the Generate Customer Balance (R7403B001) programs. If you group information in the F74412 table by G/L offset, drafts and deductions are excluded from the customer and supplier balance reports. For other groupings, drafts are included.

The system rebuilds the F74412 table each time that you run the Generate Supplier Balance and Generate Customer Balance programs. The Generate Supplier Balance program rebuilds only Accounts Payable records, while the Generate Customer Balance program rebuilds only Accounts Receivable records.

Setting Processing Options for Generate Supplier Balance (R7404003)

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

Select Tab - Use these processing options to specify the dates to use to select transactions.

1. Date From and 2. Thru Date - Enter the beginning and ending dates of the range of G/L dates for which you want to select detail transactions. If you leave these processing options blank, the system date is used.
   NOTE: The initial balance is calculated based on one day prior to the date you enter.

Process Tab - Use these processing options to specify how to group transactions and to specify whether withholding should be displayed on a separate line.

1. Group Mode - Specify whether the process should group information by G/L offset, by trade account, or leave the information ungrouped. Values are:
   
   • Blank: No G/L grouping.
   • 1: Group by G/L Offset.
     NOTE: Grouping by G/L Offset excludes drafts.
   • 2: Group by Trade Account.
2. Process Mode - Specify whether the system groups information by Parent Supplier Number or Supplier Number. Values are:
   • Blank: Group by Supplier Number.
   • 1: Group by Parent Supplier Number.
3. Withholding Tax - Specify whether the system creates a separate line for withholding tax for each payment document. Values are:
   • Blank: Single line per document.
   • 1: Separate line for Withholding Tax.

There is no report output.

Setting Processing Options for Generate Customer Balance (R7403B001)

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

Select Tab - Use these processing options to specify the dates to use to select transactions.

1. Date From and 2. Thru Date - Enter the beginning and ending dates of the range of G/L dates for which you want to select detail transactions. If you leave these processing options blank, the system date is used.
   NOTE: The initial balance is calculated based on one day prior to the date you enter.

Process Tab - Use these processing options to specify how to group transactions.

1. Group Mode - Specify whether the process should group information by G/L offset, by trade account, or leave the information ungrouped. Values are:
   • Blank: No G/L grouping.
   • 1: Group by G/L Offset.
   • 2: Group by Trade Account.
2. Process Mode - Specify whether the system groups information by parent customer number or customer number. Values are:
   • Blank: Group by Customer Number.
   • 1: Group by Parent Customer Number.

There is no report output.

Understanding Customer and Supplier Balance Reports (R7404002)

The Supplier Balance Report and Customer Balance Report are different versions of the same program (R7404002). Whether a specific version of the program produces a Supplier Balance Report or Customer Balance Report depends on the processing options. You print the supplier and customer balance reports to review the detail of the transactions between the company and the customers or suppliers. The reports also show this information for each customer or supplier for a given date range and selected companies:

• Initial balance
• Debit and credit balance
• Closing balance

The supplier and customer balance reports have these additional features:

• Transactions are printed with debit and credit amounts in separate columns.
• You can print the report with transaction amounts, including draft amounts, displayed in euro (EUR) or another currency.
• You can print the report in summary or detail mode.
• You can use the G/L Class (GLC) for data selection, sequencing, and subtotaling if you group by G/L Offset when you build the Customer/Supplier Balance Worktable (A/P and A/R) (F74412).
• You can determine customer or supplier balances as of a certain date, based on the "from" and "thru" dates specified in the processing options of the Generate Supplier Balance program (R7404003) and Generate Customer Balance program (R7403B001).
• Transactions are printed within the period specified in the processing options of the Generate Supplier Balance and Generate Customer Balance programs.
• You can compare customer and supplier balances by G/L offset or trade account with the account balances.

The supplier and customer balance reports are based on information in the Customer/Supplier Balance Worktable (A/P and A/R) table (F74412). You must build the table using the Generate Supplier Balance program (R7404003) and the Generate Customer Balance program (R7403B001) before you run the customer and supplier balance reports.

The initial balance is calculated as of one day prior to the date specified in the Date From processing options for the Generate Supplier Balance and Generate Customer Balance programs.

Setting Processing Options for Customer/Supplier Balance Report (R7404002)

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

Select Tab

1. Balance from - Specify whether to print an Accounts Payable (supplier) balance report or an Accounts Receivable (customer) balance report. Values are:
   • Blank: Print an Accounts Payable report.
   • 1: Print an Accounts Receivable report.

Print Tab

1. Print Mode - Specify the print mode. Values are:
   • Blank: Print detail including initial balance, closing balance, and all of the documents for the selected period.
   • 1: Print only the closing balance.

Currency Tab

1. As-If Currency - Use this processing option to show amounts in a currency other than the currency in which the amounts are stored in the system. The system converts and shows domestic amounts in this As If currency. For example, an amount in GBP (British Pound) can appear as if it is in EUR (Euro). If you leave this processing option blank, the system prints the amounts in the domestic currency.
   NOTE: This processing option enables you to view amounts in a different currency as a hypothetical scenario only. The amounts that appear in the different currency are not saved to the system.
2. As-If Currency Date - Specify an As-Of date if you enter a currency code for the As-If Currency processing option. This option processes the exchange rate as of the date you specify. If you leave this processing option blank, the system uses the system date.
   NOTE: A valid exchange rate must exist in the exchange rate table between the two currencies based on the As-Of date.

How to compare the A/R trade account to G/L

For each A/R trade account, to compare it to G/L, do the following steps separately:

1. Open the AAI application (P0012) by putting AAI in the fastpath
2. Inquire on the A/R trade account by inputting its business unit (BU) and object account (OBJ) (and, if applicable, its subsidiary (SUB)) in the QBE line; do not put in any other selection criteria!
3. Make a note of the various RC AAIs and their offsets (there can potentially be more than one), that point to this trade account
4. Run R7403B001 and put the offsets found in step 3 in the data selection, as well as the company number; make sure you put in the correct period in the From and Thru values in the processing options. Per Bug 10845833 which has been Returned Not Planned, final disposition states not to use data selection on this UBE. This report needs to read all POSTED transactions to get the balance correctly. If you set data selection in step 4 to company and export the F74412 records, the Excel file could contain records belonging to other companies, and those needs to be manually removed by the user, and keep only those made on the desired company to do the SUM on.
5. Step 4 will create the F74412; export all records in this table where RPSY=03B to Excel (F74412 is a worktable, and therefore gets re-created each time R7403B001 is run
6. In the excel spreadsheet do a SUM for the gross amount and open amount columns
7. Open Account Balances by Month (P0902P1)
8. Inquire on the trade account and compare the Cumulative Amount (AMTA) to the open amount sum from step 6

Frequently Asked Questions

Question 1: Is it possible to capture a customer's historical data (all open and closed invoices and related receipts) for a given period?

Answer 1: To view historical data for a customer, you can run UBE R7403B001 (Generate Customer Balance) and then R7404002 (Customer/Supplier Balance Report). R7403B001 will create a temporary worktable F74412. UBE R7404002 will show the data in the temp table.

Note: The AP equivalent, to capture and show a supplier's historical data is R7404003 (Generate Supplier Balance), see below section.

Question 2: How can the invoice records in the F74412 table be linked with corresponding F03B11 records?

Answer 2: The fields - Document Number (DOC), Document Type (DCT) and Document Company (KCO) from the invoice in the F03b11 will only get updated onto F74412, if the transactions are created within the date range setup in the processing options under the Select tab.

Question 3:Why are AP Prepayment Vouchers not printing on the Supplier Balance Report (R7404002)?

Answer 3: AP Prepayment functionality creates one voucher with a document type of PM, and with two pay items, one negative and one positive. The Generate Supplier Balance Report (R7404003) uses the GL Offset to differentiate vouchers. Because the two pay items have the same document number, they won't print.

Prepayment vouchers will only be inserted into the F74412 by R7404003, and therefore subsequently print on R7404002, if the Group Mode processing option on the Process Tab of the R7404003 is set to “1” (Group by G/L Offset).

Question 4: When running the Customer/Supplier Balance Report (R7404002) should the initial balance from this year be equal to the total amount from the previous year?

Answer 4: The Total Balance Amount from one year should be exactly the same as the Initial Balance from the next year.

Question 5: How can a receipt that was matched to an incorrect customer and subsequently voided be eliminated from the customer balance report (R7404002)?

Answer 5: In the data selection of the Customer Balance Report (R7404002) use the Receipt Number (CKNU) not equal to the incorrect receipt number.

Question 6: How to exclude A/P Drafts from Supplier Balance Report (R7404002)?

Answer 6: In order to exclude A/P Drafts from being included in Supplier Balance Report (R7404002), Generate Supplier Balance program (R7404003) should be run with Processing Option 4. Drafts set to 1 (Exclude Drafts) in Process tab.

Question 7: Is there a project for the redesign of the R7403B001 A/R Generate Customer Balance A/R and/or R7404003 A/P Generate Supplier Balance?

Answer 7: There are several Bugs and Enhancements entered for R7403B001 A/R Generate Customer Balance and R7404003 A/P Generate Supplier Balance. Enhancement Request Bug 26417673was entered to redesign R7403B001 and R7404003. To receive updates regarding this project, monitor the progress of Enh 26417673" title="Enh 26417673.