VA05

-- ============================================================
-- T-Code : VA05 List of Sales Orders
-- Purpose: List sales orders with status and rejection reason, filtered by date / sales org / customer
-- Grain  : One row per sales-order item with first schedule line
-- Tables : VBAK, VBAP, VBEP, VBKD, VBUK, MAKT, MARA, KNA1, T001W, TVKOT
-- 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.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
  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 <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'
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;