# JD Edwards F4801 Work Order SQL Query Generator

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

## Short answer

Build SQL for open JDE work orders by branch plant, status and date, plus the parts list for one work order.

## Category

developer

## Erp

Oracle JD Edwards EnterpriseOne

## Industry

Manufacturing

## Audience

- JDE developer
- Shop floor planner
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Data schema or library

**Key:** schema

**Type:** text

**Default:** PRODDTA

#### Branch plant (WAMCU)

**Key:** branch

**Type:** text

**Default:** M30

#### Order type (WADCTO)

**Key:** wo_type

**Type:** text

**Default:** WO

#### Status from (WASRST)

**Key:** status_from

**Type:** text

**Default:** 10

#### Status to (WASRST)

**Key:** status_to

**Type:** text

**Default:** 40

#### Request date before (Julian)

**Key:** due_before

**Type:** number

**Default:** 126365

#### Work order number (for parts list)

**Key:** wo_number

**Type:** number

**Default:** 412345

**Blocks:**

#### Open work orders by branch plant and status

**Body:** SELECT WADOCO AS wo_number,
       WADCTO AS order_type,
       LTRIM(WAMCU) AS branch_plant,
       WALITM AS item_number,
       WADL01 AS description,
       WASRST AS status,
       WAUORG AS qty_ordered_raw,
       WASOQS AS qty_completed_raw,
       DATEADD(DAY, (WADRQJ % 1000) - 1, DATEFROMPARTS(1900 + WADRQJ / 1000, 1, 1)) AS request_date,
       WAANPA AS supervisor
FROM {{schema}}.F4801
WHERE LTRIM(WAMCU) = '{{branch}}'
  AND WADCTO = '{{wo_type}}'
  AND WASRST BETWEEN '{{status_from}}' AND '{{status_to}}'
  AND WADRQJ > 0
  AND WADRQJ <= {{due_before}}
ORDER BY WADRQJ, WADOCO;

#### Parts list for one work order

**Body:** SELECT WMDOCO AS wo_number,
       WMCPIT AS component_short_item,
       WMLITM AS component_item,
       WMUORG AS qty_required_raw,
       WMTRQT AS qty_issued_raw
FROM {{schema}}.F3111
WHERE WMDOCO = {{wo_number}}
ORDER BY WMCPIT;

#### Operations (routing) for the same order

**Body:** SELECT WLDOCO AS wo_number,
       WLOPSQ AS operation_seq,
       WLOPHD AS description,
       WLMCU AS work_center
FROM {{schema}}.F3112
WHERE WLDOCO = {{wo_number}}
ORDER BY WLOPSQ;

**Notes:** Written for SQL Server. Quantity columns carry implied decimals defined by the data dictionary, so the _raw aliases are left unscaled; check the display decimals for UORG, SOQS and TRQT in your release. Status values such as 10 and 40 are examples, because work order status codes come from UDC 00/SS and are set per site. Column names are standard but verify them in your release, then test on a copy.

## Content

**Intro:** The F4801 work order master is where JD Edwards manufacturing and maintenance orders live. This generator builds queries for open orders, the parts list and the routing, with Julian date conversion included.

**Sections:**

#### How to use it

**Body:** Enter your branch plant, order type and the status range you consider open, along with the request date cutoff in Julian form. The first block lists work orders; the second and third show components and operations for one order number. Paste into your SQL client against a reporting copy of the database.

#### Tables behind a work order

**Body:** F4801 is the header with order number WADOCO, type WADCTO, branch WAMCU, item WAITM and status WASRST. F3111 is the parts list, keyed by order number and component, and F3112 is the routing with operation sequence. Related tables hold the order text and the hours and quantities posted by shop floor reporting.

**Bullets:**

- WASRST is the status; the codes are site defined
- WATRDJ is the order date and WASTRT the start date
- WADRQJ is the requested completion date
- WAANPA is the supervisor address number

#### Using the results

**Body:** Late orders show a request date earlier than today with a status short of complete. Compare the quantity ordered against completed for scrap and short shipment trends, and join the parts list to inventory in F41021 to see shortages. Keep joins on the short item number WAITM and not the 25 character item number.

#### Common mistakes

**Body:** Treating status as numeric when it is a character field, ignoring zero dates (WADRQJ of 0 means none), mixing manufacturing and maintenance order types, and assuming quantity columns already contain decimals.

**Cta:** If planners ask for custom work order lists every week, we can build a read-only assistant over your JDE shop floor data with approvals on any write-back.

**Faq:**

#### Where are work order status codes defined?

**Answer:** They come from a user defined code list (system 00, type SS) and are customised per company. Look at your own UDC values, not a generic list, before you decide which codes are open.

#### Why do I get zero rows for a branch plant?

**Answer:** Business unit values are right justified and padded, so an equality test with an untrimmed column fails. Apply LTRIM on the column as in the generated SQL.

#### Can I use item number WALITM for joins?

**Answer:** You can, but the short item number WAITM is the numeric key used across inventory tables and is faster and safer. WALITM is the user facing second item number.

#### Does the query change any data?

**Answer:** No. All blocks are SELECT only. Use JDE applications or business functions to change orders so status history, inventory commitments and costing stay consistent.

## Search terms

jde f4801 sql, jd edwards work order query, jde open work orders sql, f3111 work order parts list, jde shop floor sql report, jd edwards wo table, jde work order dates julian

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