VA01

-- ============================================================
-- T-Code : VA01 Create Sales Order
-- Purpose: Create a sales order with header, items, and schedule lines
-- Grain  : One row per sales-order item with first schedule line
-- Tables : VBAK, VBAP, VBEP, VBKD, MAKT, MARA, KNA1, T001W, TVKOT
-- Notes  : On S/4HANA the overall statuses (GBSTK/LFSTK/FKSTK) live inline on VBAK; on ECC they live on VBUK — see the commented ECC variant. Filter ABGRU = '' to exclude items with a rejection reason. KUNNR on VBAK is sold-to; ship-to lives in VBPA partner role 'WE'.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.VBELN   AS "Sales Document",
  l.POSNR   AS "Item",
  s.ETENR   AS "Schedule Line",

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

  -- Quantities + UOM (paired)
  l.KWMENG  AS "Order Quantity",
  l.VRKME   AS "Sales Unit",
  l.KBMENG  AS "Confirmed Quantity",
  s.WMENG   AS "Schedule Line Qty",
  l.NETWR   AS "Net Value",
  l.NETPR   AS "Net Price",
  h.WAERK   AS "Document Currency",

  -- Dates
  h.AUDAT   AS "Document Date",
  h.ERDAT   AS "Created On",
  s.EDATU   AS "Schedule Line Date",
  h.AEDAT   AS "Last Changed On",

  -- Status / indicators (S/4HANA: inline on VBAK; ECC: on VBUK — see comment below)
  h.GBSTK   AS "Overall Processing Status",
  h.LFSTK   AS "Delivery Status",
  h.FKSTK   AS "Billing Status",
  l.ABGRU   AS "Reason for Rejection",

  -- Org fields
  l.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  l.LGORT   AS "Storage Location",
  h.VKORG   AS "Sales Organization",
  so.VTEXT  AS "Sales Organization Name",
  h.VTWEG   AS "Distribution Channel",
  h.SPART   AS "Division",
  h.VKBUR   AS "Sales Office",

  -- Master data enrichment
  h.AUART   AS "Sales Document Type",
  h.KUNNR   AS "Sold-To Party",
  c.NAME1   AS "Sold-To Name",
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  k.BSTKD   AS "Customer PO Number",
  k.INCO1   AS "Incoterms (Part 1)"
FROM <catalog>.<schema>.vbak h
INNER JOIN <catalog>.<schema>.vbap l
  ON  l.MANDT = h.MANDT
  AND l.VBELN = h.VBELN
LEFT JOIN <catalog>.<schema>.vbep s
  ON  s.MANDT = l.MANDT
  AND s.VBELN = l.VBELN
  AND s.POSNR = l.POSNR
  AND s.ETENR = '0001'
LEFT JOIN <catalog>.<schema>.vbkd k
  ON  k.MANDT = h.MANDT
  AND k.VBELN = h.VBELN
  AND k.POSNR = '000000'
-- ECC variant: statuses live on VBUK, not VBAK — add this join and read
-- st.GBSTK / st.LFSTK / st.FKSTK instead:
-- 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>.tvkot so
  ON  so.MANDT = h.MANDT
  AND so.VKORG = h.VKORG
  AND so.SPRAS = 'E'
WHERE
  h.MANDT = '<MANDT>'
  AND h.AUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  AND l.ABGRU = ''
  -- AND h.VKORG = '<VKORG>'
  -- AND h.KUNNR = '<KUNNR>'
ORDER BY h.AUDAT DESC, h.VBELN, l.POSNR;