MK01

-- ============================================================
-- T-Code : MK01 Create Vendor (Purchasing)
-- Purpose: Create vendor master from a purchasing perspective
-- Grain  : One row per vendor / company code (one if LFB1 not joined)
-- Tables : LFA1, LFB1, T001
-- Notes  : LFA1 is client-level (general data). LFB1 adds company-code-specific accounting data. Outer join to LFB1 so client-only vendors still appear.
-- ============================================================
SELECT
  -- Keys
  v.MANDT   AS "Client",
  v.LIFNR   AS "Vendor",
  fb.BUKRS  AS "Company Code",

  -- Descriptive text
  v.NAME1   AS "Vendor Name",
  v.NAME2   AS "Vendor Name 2",

  -- Dates
  v.ERDAT   AS "Created On",
  v.UPDAT   AS "Changed On",

  -- Status / indicators
  v.LOEVM   AS "Central Deletion Flag",
  v.SPERR   AS "Central Posting Block",
  fb.LOEVM  AS "Deletion Flag (Co.Code)",
  fb.SPERR  AS "Posting Block (Co.Code)",

  -- Org fields
  v.ORT01   AS "City",
  v.PSTLZ   AS "Postal Code",
  v.LAND1   AS "Country",
  v.REGIO   AS "Region",
  v.TELF1   AS "Telephone",
  v.STCEG   AS "VAT Registration Number",
  cc.BUTXT  AS "Company Code Name",

  -- Master data enrichment
  v.KTOKK   AS "Vendor Account Group",
  fb.AKONT  AS "Reconciliation Account",
  fb.ZTERM  AS "Terms of Payment",
  fb.ZWELS  AS "Payment Methods",
  fb.ZAHLS  AS "Payment Block"
FROM <catalog>.<schema>.lfa1 v
LEFT JOIN <catalog>.<schema>.lfb1 fb
  ON  fb.MANDT = v.MANDT
  AND fb.LIFNR = v.LIFNR
LEFT JOIN <catalog>.<schema>.t001 cc
  ON  cc.MANDT = fb.MANDT
  AND cc.BUKRS = fb.BUKRS
WHERE
  v.MANDT = '<MANDT>'
  AND v.LIFNR = '<LIFNR>'
  -- AND fb.BUKRS = '<BUKRS>'
  -- AND v.LOEVM <> 'X'
ORDER BY v.LIFNR, fb.BUKRS;