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 9.1.5.3, 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.

FieldDescription of Value
DCTDocument Type
DOCDocument Number
KCODocument Company
DGJG/L Date
JELNJournal entry line number
LTLedger Type
EXTLLine extension code
DCT DescriptionDocument type description
KCO DescriptionDocument company description
LT DescriptionLedger type description
POSTG/L posted code
COCompany
CO DescriptionCompany Description
MCUBusiness Unit
MCU DescriptionBusiness Unit description
OBJObject
SUBSubsidiary
PNPeriod
CTRYCentury
FYFiscal Year
CRCDCurrency Code – from
CRRCurrency Conversion Rate
AAAmount
EXAName – alpha explanation
EXRName – remark explanation
AN8Address number
AN8 DescriptionAddress number value description
CNPayment Number
ALTXConsumption Tax cross reference
DSVJDate Service tax
BRCRCurrency Code – Base
BCRC DescriptionCurrency code – base value description
TXA1Tax Rate/Area 1
TXA1 DescriptionTax Rate/ Area 1 description
EXR1Tax Explanation
EXR1 DescriptionTax explanation value description
TXITMTax Short item Number
TXITM DescriptionTax short item number value description
ACRAmount Currency