VL01N

-- ============================================================
-- T-Code : VL01N Create Outbound Delivery with Order Reference
-- Purpose: Create an outbound delivery referencing a sales order
-- Grain  : One row per outbound-delivery item
-- Tables : LIKP, LIPS, MAKT, MARA, KNA1, T001W, T001L
-- Notes  : WADAT_IST = actual goods-issue date (null until PGI posted). On S/4HANA the delivery statuses live inline on LIKP — WBSTK (goods movement) is canonical for filtering 'open' deliveries; on ECC they live on VBUK — see the commented ECC variant.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.VBELN   AS "Delivery",
  l.POSNR   AS "Delivery Item",

  -- Descriptive text
  l.ARKTX   AS "Item Short Text",
  mt.MAKTX  AS "Material Description",

  -- Quantities + UOM (paired)
  l.LFIMG   AS "Delivery Quantity",
  l.VRKME   AS "Sales Unit",
  l.LGMNG   AS "Actual Qty (Stock Unit)",
  l.MEINS   AS "Base Unit of Measure",
  l.NTGEW   AS "Net Weight",
  l.GEWEI   AS "Weight Unit",

  -- Dates
  h.LFDAT   AS "Delivery Date",
  h.WADAT   AS "Planned Goods-Issue Date",
  h.WADAT_IST AS "Actual Goods-Issue Date",
  h.KODAT   AS "Picking Date",
  h.ERDAT   AS "Created On",

  -- Status / indicators
  h.WBSTK   AS "Goods Movement Status",
  h.KOSTK   AS "Picking Status",
  h.PKSTK   AS "Packing Status",
  h.FKSTK   AS "Billing Status",

  -- Org fields
  l.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  l.LGORT   AS "Storage Location",
  sl.LGOBE  AS "Storage Location Name",
  h.VSTEL   AS "Shipping Point",
  h.VKORG   AS "Sales Organization",

  -- Master data enrichment
  h.LFART   AS "Delivery Type",
  h.KUNAG   AS "Sold-To Party",
  h.KUNNR   AS "Ship-To Party",
  c.NAME1   AS "Customer Name",
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  l.VGBEL   AS "Reference Document (Sales Order)",
  l.VGPOS   AS "Reference Item"
FROM <catalog>.<schema>.likp h
INNER JOIN <catalog>.<schema>.lips l
  ON  l.MANDT = h.MANDT
  AND l.VBELN = h.VBELN
-- ECC variant: statuses live on VBUK, not LIKP — add this join and read
-- st.WBSTK / st.KOSTK / st.PKSTK / st.FKSTK instead (VBUK also carries
-- LFSTK, the overall delivery status, which has no LIKP inline equivalent):
-- LEFT JOIN <catalog>.<schema>.vbuk st
--   ON  st.MANDT = h.MANDT
--   AND st.VBELN = h.VBELN
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN <catalog>.<schema>.kna1 c
  ON  c.MANDT = h.MANDT
  AND c.KUNNR = h.KUNNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = l.MANDT
  AND sl.WERKS = l.WERKS
  AND sl.LGORT = l.LGORT
WHERE
  h.MANDT = '<MANDT>'
  AND h.LFDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND h.VSTEL = '<VSTEL>'
  -- AND h.WBSTK <> 'C'  -- excludes completed deliveries
ORDER BY h.LFDAT DESC, h.VBELN, l.POSNR;