jd edwards R89F0911G UBE

Using the JD Edwards R89F0911G UBE for Extracting Data from the General Ledger (F0911)

During auditing, companies are requested to provide data from their General Ledger (F0911) so auditors can complete their annual review. This file can hold up to millions of records, based on the number of transactions processed by the business.

Businesses want a fast and accurate process to get this data.

Most companies will create a SQL Query to extract the data; however, starting with 9.1 Tools Release, JDE UBE R89F0911G now has data selection that allows you to extract data based your audit requirements.

The benefit of using this program is that you can output all the records in your General Ledger Table (F0911) with additional information such as Document Description, Business Unit Descriptions, Explanations, etc. (See Figure 9)

Another benefit is the accuracy of the data. When you run the extraction program, the system generates a .txt file that writes to the Export folder of the installation directory. The system does not generate a report when you run the program. The text files are semi-colon (; ) delimited with double quote (“) encapsulation.

A query may slow down your database performance and often requires wildcard expressions to avoid null values, joins, etc. and additional manipulation to ensure the data is accurate.

Running the R89F0911G

Unless you have access to G74 EMEA Localization menu, you will need access to Fastpath. If you have the EMEA menu, you can select the German Localization, then GDPdU Data Extraction, then F0911 Data Extraction for GDPdU/. If you are using Fastpath enter BV and then enter.

Step 1

In the Batch Application field enter R89F0911G and click Find. You will only see 1 version, ZJDE0001.  (see Figure 1)

Figure 1

Step 2

To submit the job, select the line and then click OK (green checkmark ).

Step 3

On the “Batch Versions – Table Conversion Prompting” screen (see Figure2), check the Data Selection box. There are no processing options. Then click submit.

Figure 2

Step 4

In the Data Selection (see Figure 3) you can add the G/L date (commonly used) as follows:

  • In the Left Operand select from the drop-down list DGJ (Date – For G/L (and Voucher) – Julian (F0911) (IC))
  • In the Comparison field, select from the drop-down list – is equal to
  • In the Right Operand, select from the drop-down box – Literal
  • Then in the Select Literal Value screen, select the Range of Values Tab (see figure 3)
  • Enter the data range
  • Click OK (green checkmark ) to accept the dates

Figure 3

Step 5

Once the data selection is completed, click OK (green checkmark ) to complete the process. (see Figure 2)

Figure 2

Retrieving the .txt File

The folder should reside on the enterprise server by pathcode. Example: for the development server, you would retrieve from \\E920\DV920\Export on the enterprise server and for production would be \\E920\PD920\Export.

Figure 4

Converting the .txt file to Excel

Step 1

Once you have accessed the folder and file, save the .txt file.

Step 2

Open a new spreadsheet in Excel and then access “Data” and then ”From Text/CSV”. (see Figure 5)

Figure 5

Step 3

Depending where you saved the file, navigate to and select the .txt file. (see Figure 6)

Figure 6

Step 4

Click on Import. You may get a screen like this (see Figure 7).

Figure 7

Step 5

Click on Load. Now you have the data in Excel format (see Figure 8).

Figure 8

Columns and Headings

When you run the R89F0911G UBE, the system writes data for the selected records for the data items listed in this table, in the order listed.

Field Description of Value
DCT Document Type
DOC Document Number
KCO Document Company
DGJ G/L Date
JELN Journal entry line number
LT Ledger Type
EXTL Line extension code
DCT Description Document type description
KCO Description Document company description
LT Description Ledger type description
POST G/L posted code
CO Company
CO Description Company Description
MCU Business Unit
MCU Description Business Unit description
OBJ Object
SUB Subsidiary
PN Period
CTRY Century
FY Fiscal Year
CRCD Currency Code – from
CRR Currency Conversion Rate
AA Amount
EXA Name – alpha explanation
EXR Name – remark explanation
AN8 Address number
AN8 Description Address number value description
CN Payment Number
ALTX Consumption Tax cross reference
DSVJ Date Service tax
BRCR Currency Code – Base
BCRC Description Currency code – base value description
TXA1 Tax Rate/Area 1
TXA1 Description Tax Rate/ Area 1 description
EXR1 Tax Explanation
EXR1 Description Tax explanation value description
TXITM Tax Short item Number
TXITM Description Tax short item number value description
ACR Amount Currency