# JD Edwards World F0011 Batch Status SQL Generator

> Source: https://netray.co/tools/jde-world-f0011-batch-status-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate Db2 for i SQL that lists F0011 batch headers by type, status and date, decodes status through UDC 98/IC and counts unposted F0911 lines per batch.

## Category

developer

## Erp

JD Edwards World

## Audience

- JDE World developer
- IBM i administrator
- Financial systems analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Data library (F0011, F0911)

**Key:** datalib

**Type:** text

**Default:** JDFDATA

**Help:** Library that holds your World business data files.

#### Library holding F0005 (UDC)

**Key:** udclib

**Type:** text

**Default:** JDFDATA

**Help:** Some sites keep F0005 in a separate common library; change it if so.

#### Batch type (ICICUT)

**Key:** batch_type

**Type:** text

**Default:** G

**Help:** For example G journal entries, V vouchers, IB invoices.

#### Exclude batch status (ICIST)

**Key:** exclude_status

**Type:** text

**Default:** D

**Help:** D is posted; excluding it shows batches not yet posted.

#### User ID pattern (ICUSER)

**Key:** user

**Type:** text

**Default:** %

**Help:** Use % for all users or a user ID such as JSMITH.

#### From batch date (CYYDDD)

**Key:** from_date

**Type:** text

**Default:** 126001

**Help:** 126001 is 1 January 2026.

**Blocks:**

#### 1. Batch headers with status and date

**Body:** -- JD Edwards World, Db2 for i: F0011 batch control headers
SELECT B.ICICUT AS BATCH_TYPE,
       B.ICICU  AS BATCH_NUMBER,
       B.ICIST  AS BATCH_STATUS,
       COALESCE(U.DRDL01, 'Not found in UDC 98/IC') AS STATUS_DESC,
       B.ICUSER AS USER_ID,
       B.ICDICJ AS BATCH_DATE_CYYDDD,
       CASE WHEN B.ICDICJ > 0
            THEN DATE(DIGITS(DECIMAL(B.ICDICJ + 1900000, 7, 0)))
       END AS BATCH_DATE,
       B.ICPOB  AS POSTED_OUT_OF_BALANCE
FROM {{datalib}}.F0011 B
LEFT JOIN {{udclib}}.F0005 U
       ON U.DRSY = '98'
      AND U.DRRT = 'IC'
      AND TRIM(U.DRKY) = TRIM(B.ICIST)
WHERE B.ICICUT = '{{batch_type}}'
  AND B.ICIST <> '{{exclude_status}}'
  AND B.ICUSER LIKE '{{user}}'
  AND B.ICDICJ >= {{from_date}}
ORDER BY B.ICDICJ, B.ICICU;

#### 2. Unposted F0911 lines per batch

**Body:** -- Count F0911 lines not yet posted (GLPOST <> 'P') for each batch
SELECT B.ICICUT AS BATCH_TYPE,
       B.ICICU  AS BATCH_NUMBER,
       B.ICIST  AS BATCH_STATUS,
       B.ICUSER AS USER_ID,
       G.GLLT   AS LEDGER_TYPE,
       COUNT(*) AS UNPOSTED_LINES,
       SUM(G.GLAA) AS NET_AMOUNT_STORED
FROM {{datalib}}.F0011 B
JOIN {{datalib}}.F0911 G
  ON G.GLICU  = B.ICICU
 AND G.GLICUT = B.ICICUT
WHERE G.GLPOST <> 'P'
  AND B.ICICUT = '{{batch_type}}'
  AND B.ICDICJ >= {{from_date}}
GROUP BY B.ICICUT, B.ICICU, B.ICIST, B.ICUSER, G.GLLT
ORDER BY UNPOSTED_LINES DESC;

#### 3. Running the same query on EnterpriseOne

**Body:** -- EnterpriseOne uses the same F0011 and F0911 column names.
-- On E1 with Db2 for i, change the libraries, for example:
--   {{datalib}} -> PRODDTA (business data)
--   {{udclib}}  -> PRODCTL (control tables, where F0005 lives in E1)
-- On E1 with SQL Server or Oracle, keep the column names and filters
-- but replace the DIGITS/DATE conversion with that database's date functions.

**Notes:** Verify for your release: the batch status values in UDC 98/IC on your system (A approved and D posted are standard; check the rest with a quick SELECT on F0005), the library that holds F0005, and that GLAA is stored without decimals (implied decimal places from the data dictionary), so divide by 100 for a 2-decimal currency. Run first in a test library or with FETCH FIRST 100 ROWS ONLY added.

## Content

**Intro:** This generator builds Db2 for i SQL for JD Edwards World that lists F0011 batch control headers with their status, user and date, and counts unposted F0911 lines per batch. The same column names work on EnterpriseOne with a library or schema change.

**Sections:**

#### How to use it

**Body:** Enter your data library, the library that holds the UDC file F0005, the batch type, the status to exclude, a user pattern and a from date in CYYDDD. Copy the SQL into ACS Run SQL Scripts, STRSQL or any ODBC client connected to the IBM i. Query 1 is the batch list you would otherwise page through on the batch review screens. Query 2 finds batches that still have unposted journal lines, the usual question before month-end close. Both are read-only SELECT statements.

**Bullets:**

- Use % in the user field for all users
- Exclude status D to see everything not yet posted
- Add FETCH FIRST 100 ROWS ONLY while testing

#### Where this lives in JD Edwards

**Body:** F0011 is the batch control file: one record per batch, keyed by batch number (ICICU) and batch type (ICICUT). ICIST holds the batch status, decoded by UDC 98/IC in F0005, ICUSER the user, ICDICJ the batch date in JDE Julian CYYDDD and ICPOB the posted out of balance flag. Detail lines live in the transaction files, F0911 for the general ledger, joined on GLICU and GLICUT. A posted F0911 line has GLPOST = 'P'. Batch approval is set by the general accounting constants: when manager approval is required, new batches wait for approval before they can post.

#### Worked example: month-end unposted check

**Body:** A controller asks which G batches since 1 January 2026 are not posted. Use batch type G, exclude status D, user %, from date 126001. Query 1 returns twelve batches, three still awaiting approval. Query 2 shows batch 48211 has 214 unposted AA ledger lines and batch 48230 has 6. The CYYDDD conversion adds 1900000 to the Julian value to make a 7-digit yyyyddd string, which Db2 for i DATE() accepts, so 126032 becomes 2026032, which is 1 February 2026.

#### Gotchas and how to check the result

**Body:** Zero or blank dates break the conversion, which is why the CASE skips ICDICJ of 0. TRIM on both sides of the UDC join handles UDC keys stored with padding. GLAA is stored without the decimal point, so a raw sum of 1250000 in a 2-decimal currency means 12,500.00. Cross-check a sample batch against the batch review screen: the status, user and number of lines should match. If the UDC description is missing, the status value is not set up in 98/IC on your system.

**Bullets:**

- Divide GLAA by the currency's decimal factor
- Check that F0005 is in the library you entered
- Compare one batch with the batch review screen

**Cta:** If you want your finance team to ask JD Edwards World questions like this in plain English, answered from your IBM i on your own servers, we can set that up.

**Faq:**

#### What is the F0011 table in JD Edwards?

**Answer:** F0011 is the batch control file. It stores one header per batch with the batch number (ICICU), batch type (ICICUT), batch status (ICIST), user (ICUSER), batch date (ICDICJ) and posting flags. Transaction lines in files like F0911 point back to it through their own batch number and batch type fields.

#### How do batch approvals work in JDE World?

**Answer:** When the general accounting constants require manager approval, new batches are created in a pending state and an approver must approve them before posting. The status is stored in F0011 ICIST and decoded by UDC 98/IC. Query 1 with the posted status excluded shows which batches are still waiting.

#### How do I convert a JDE Julian date in Db2 for i?

**Answer:** Add 1900000 to the CYYDDD value to get yyyyddd, format it as seven digits and pass it to DATE(), for example DATE(DIGITS(DECIMAL(ICDICJ + 1900000, 7, 0))). Db2 for i accepts a 7-character yyyyddd string. Guard against zero dates with a CASE expression.

#### Does this SQL work on JD Edwards EnterpriseOne?

**Answer:** The F0011 and F0911 column names are the same in EnterpriseOne. On E1 with Db2 for i, change the libraries to your business data and control table libraries. On SQL Server or Oracle, keep the columns and filters but replace the date conversion with the database's own date functions.

## Search terms

jde f0011 table, jde world batch approver, jde world tables, jde world system, jde unposted batches query, f0011 batch status, jde world f0911 unposted, db2 for i jde julian date

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json