VL03N

-- ============================================================
-- T-Code : VL03N Display Outbound Delivery
-- Purpose: Display an outbound delivery with items and status
-- Grain  : One row per outbound-delivery item
-- Tables : LIKP, LIPS, VBUK, MAKT, MARA, KNA1, T001W, T001L
-- Notes  : WADAT_IST = actual goods-issue date (null until PGI posted). On S/4HANA, LIKP/LIPS remain but WBSTK (goods-movement status) is canonical for filtering 'open' deliveries.
-- ============================================================
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
  st.LFSTK  AS "Delivery Status",
  st.WBSTK  AS "Goods Movement Status",
  st.KOSTK  AS "Picking Status",
  st.PKSTK  AS "Packing 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.KUNNR   AS "Sold-To Party",
  h.KUNAG   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 likp h
INNER JOIN lips l
  ON  l.MANDT = h.MANDT
  AND l.VBELN = h.VBELN
LEFT JOIN vbuk st
  ON  st.MANDT = h.MANDT
  AND st.VBELN = h.VBELN
LEFT JOIN makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN kna1 c
  ON  c.MANDT = h.MANDT
  AND c.KUNNR = h.KUNNR
LEFT JOIN t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
LEFT JOIN t001l sl
  ON  sl.MANDT = l.MANDT
  AND sl.WERKS = l.WERKS
  AND sl.LGORT = l.LGORT
WHERE
  h.MANDT = '100'
  AND h.LFDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND h.VSTEL = '<VSTEL>'
  -- AND st.WBSTK <> 'C'  -- uncomment to exclude completed deliveries
ORDER BY h.LFDAT DESC, h.VBELN, l.POSNR;