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, VBUK, MAKT, MARA, KNA1, T001W, TVKO
-- Notes  : VBUK carries the overall document status (GBSTK/LFSTK/FKSTK). 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.LFIMG   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
  st.GBSTK  AS "Overall Processing Status",
  st.LFSTK  AS "Delivery Status",
  st.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 vbak h
INNER JOIN vbap l
  ON  l.MANDT = h.MANDT
  AND l.VBELN = h.VBELN
LEFT JOIN vbep s
  ON  s.MANDT = l.MANDT
  AND s.VBELN = l.VBELN
  AND s.POSNR = l.POSNR
LEFT JOIN vbkd k
  ON  k.MANDT = h.MANDT
  AND k.VBELN = h.VBELN
  AND k.POSNR = '000000'
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 tvko so
  ON  so.MANDT = h.MANDT
  AND so.VKORG = h.VKORG
WHERE
  h.MANDT = '100'
  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;