LT02

-- ============================================================
-- T-Code : LT02 Create Transfer Order for Inventory Difference
-- Purpose: Create a transfer order to clear an inventory difference
-- Grain  : One row per transfer-order item (LTAK header + LTAP item)
-- Tables : LTAK, LTAP, MAKT, MARA, T001W
-- Notes  : LTAK.PQUIT = 'C' indicates a confirmed TO. VLPLA/NLPLA are the source/destination bins. For EWM, see /SCWM/* tables instead.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.LGNUM   AS "Warehouse Number",
  h.TANUM   AS "Transfer Order",
  l.TAPOS   AS "TO Item",

  -- Descriptive text
  mt.MAKTX  AS "Material Description",

  -- Quantities + UOM (paired)
  l.VSOLM   AS "Source Quantity",
  l.VISTM   AS "Actual Quantity",
  l.NSOLM   AS "Destination Quantity",
  l.MEINS   AS "Unit of Measure",

  -- Dates
  h.BDATU   AS "Confirmation Date",
  h.BZEIT   AS "Confirmation Time",
  h.DATAU   AS "Creation Date",
  h.UZEIT   AS "Creation Time",

  -- Status / indicators
  h.BWLVS   AS "Movement Type (WM)",
  h.PQUIT   AS "Confirmation Status",
  l.KZQUIT  AS "Item Confirmation",
  h.BETYP   AS "Reference Document Type",
  h.BENUM   AS "Reference Number",

  -- Org fields
  l.VLTYP   AS "Source Storage Type",
  l.VLPLA   AS "Source Bin",
  l.NLTYP   AS "Destination Storage Type",
  l.NLPLA   AS "Destination Bin",
  l.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  l.LGORT   AS "Storage Location",

  -- Master data enrichment
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  l.CHARG   AS "Batch",
  h.ERNAM   AS "Created By"
FROM <catalog>.<schema>.ltak h
INNER JOIN <catalog>.<schema>.ltap l
  ON  l.MANDT = h.MANDT
  AND l.LGNUM = h.LGNUM
  AND l.TANUM = h.TANUM
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>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
WHERE
  h.MANDT = '<MANDT>'
  AND h.LGNUM = '<LGNUM>'
  AND h.DATAU BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND h.TANUM = '<TANUM>'
  -- AND h.PQUIT = 'C'   -- only confirmed
ORDER BY h.BDATU DESC, h.TANUM, l.TAPOS;