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 220.127.116.11, 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.
In the Batch Application field enter R89F0911G and click Find. You will only see 1 version, ZJDE0001. (see Figure 1)
To submit the job, select the line and then click OK (green checkmark ).
On the “Batch Versions – Table Conversion Prompting” screen (see Figure2), check the Data Selection box. There are no processing options. Then click submit.
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
Once the data selection is completed, click OK (green checkmark ) to complete the process. (see 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.
Converting the .txt file to Excel
Once you have accessed the folder and file, save the .txt file.
Open a new spreadsheet in Excel and then access “Data” and then ”From Text/CSV”. (see Figure 5)
Depending where you saved the file, navigate to and select the .txt file. (see Figure 6)
Click on Import. You may get a screen like this (see Figure 7).
Click on Load. Now you have the data in Excel format (see 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|
|JELN||Journal entry line number|
|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 Description||Company Description|
|MCU Description||Business Unit description|
|CRCD||Currency Code – from|
|CRR||Currency Conversion Rate|
|EXA||Name – alpha explanation|
|EXR||Name – remark explanation|
|AN8 Description||Address number value description|
|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 Description||Tax explanation value description|
|TXITM||Tax Short item Number|
|TXITM Description||Tax short item number value description|