JD Edwards SQL Query Generator for Open Sales Order Lines

-- Open sales order lines for branch 30 requested 2026-10-01 to 2026-12-31
-- Read only. Run on a reporting copy or with a read only login.
DECLARE @from_date date = '2026-10-01';
DECLARE @to_date date = '2026-12-31';
DECLARE @from_jde int = (YEAR(@from_date) - 1900) * 1000 + DATEPART(dayofyear, @from_date);
DECLARE @to_jde int = (YEAR(@to_date) - 1900) * 1000 + DATEPART(dayofyear, @to_date);

SELECT
    d.SDDOCO AS order_no,
    d.SDDCTO AS order_type,
    d.SDKCOO AS order_company,
    d.SDLNID AS line_id,
    LTRIM(RTRIM(d.SDMCU)) AS branch_plant,
    d.SDAN8 AS sold_to,
    d.SDITM AS short_item,
    LTRIM(RTRIM(d.SDLITM)) AS item_no,
    LTRIM(RTRIM(d.SDDSC1)) AS description,
    d.SDUORG / 1.0 AS qty_ordered,
    d.SDAEXP / 100.0 AS extended_price,
    d.SDNXTR AS next_status,
    d.SDLTTR AS last_status,
    DATEADD(day, (d.SDDRQJ % 1000) - 1, DATEFROMPARTS(1900 + d.SDDRQJ / 1000, 1, 1)) AS requested_date
FROM PRODDTA.F4211 d
WHERE LTRIM(RTRIM(d.SDMCU)) = '30'
  AND LTRIM(RTRIM(d.SDNXTR)) = '540'
  AND d.SDDRQJ BETWEEN @from_jde AND @to_jde
ORDER BY d.SDDRQJ, d.SDDOCO, d.SDLNID;

SELECT
    h.SHDOCO AS order_no,
    h.SHDCTO AS order_type,
    a.ABALPH AS customer_name,
    d.SDLNID AS line_id,
    LTRIM(RTRIM(d.SDLITM)) AS item_no,
    d.SDUORG / 1.0 AS qty_ordered
FROM PRODDTA.F4211 d
JOIN PRODDTA.F4201 h
  ON h.SHDOCO = d.SDDOCO AND h.SHDCTO = d.SDDCTO AND h.SHKCOO = d.SDKCOO
JOIN PRODDTA.F0101 a
  ON a.ABAN8 = d.SDAN8
WHERE LTRIM(RTRIM(d.SDMCU)) = '30'
  AND LTRIM(RTRIM(d.SDNXTR)) = '540'
ORDER BY h.SHDOCO, d.SDLNID;