Comprehensive Guide to Vendor Master Table in SAP for SQL Users

Vendor master table in SAP plays a critical role in procurement and financial transactions, as it stores all the key details about vendors. For those who work on the backend, especially new users, understanding the structure of vendor master tables and how to fetch this data using SQL queries is essential. This blog post will guide you through the primary vendor master tables, explain how they relate to one another, and provide examples of SQL queries to retrieve vendor information efficiently.

What is Vendor Master Data?

Vendor master data refers to the repository of all information related to vendors who provide goods and services to a business. It includes general information, company code-specific data, purchasing details, payment terms, bank details, and more. Each of these categories of vendor data is stored in separate tables within SAP.

Key Vendor Master Table in SAP

When working with vendor master data, it is important to understand which tables store which pieces of information. Below are the key tables related to vendor master data:

1. LFA1 (Vendor Master – General Data)
   This table stores the general data for each vendor, such as the vendor name, address, and contact details. It is linked to the vendor’s account number (LIFNR), which is the primary key.
2. LFB1 (Vendor Master – Company Code Data)
   The LFB1 table contains information specific to the vendor’s relationship with each company code, such as payment terms, reconciliation accounts, and accounting information.
3. LFM1 (Vendor Master – Purchasing Organization Data)
   This table stores data at the purchasing organization level, including purchasing-specific information such as ordering currency and terms of delivery.
4. LFBK (Vendor Master – Bank Details)
   Bank-related information for vendors is stored in the LFBK table. It includes fields such as bank account numbers and bank country codes.
5. LFB5 (Vendor Master – Dunning Data)
   This table contains data related to the vendor’s dunning process, which tracks overdue payments and generates reminders.
6. LFM2 (Vendor Master – Purchasing Data)
   The LFM2 table stores more detailed purchasing data, such as the validity of conditions and vendor evaluation data.

Learn about Material Master table in sap

SQL Queries for Vendor Master Data

Understanding the relationships between these tables allows you to construct SQL queries to fetch vendor data from SAP. Below are some commonly used SQL queries to retrieve vendor master data.

1. Fetching General Vendor Information

To retrieve general information about vendors, you can query the LFA1 table:

SELECT 
    LFA1.LIFNR AS Vendor_Number,
    LFA1.NAME1 AS Vendor_Name,
    LFA1.ORT01 AS City,
    LFA1.PSTLZ AS Postal_Code
FROM 
    LFA1
WHERE 
    LFA1.LAND1 = 'US';  -- Filter by country (optional)

This query will provide you with basic vendor details such as vendor number, name, city, and postal code.

2. Retrieving Vendor Details by Company Code

To get vendor data that is specific to a company code, you can use the LFB1 table. For instance, if you want to see the payment terms and reconciliation accounts for vendors:

SELECT 
    LFB1.LIFNR AS Vendor_Number,
    LFB1.BUKRS AS Company_Code,
    LFB1.ZTERM AS Payment_Terms,
    LFB1.KNRZE AS Reconciliation_Account
FROM 
    LFB1
WHERE 
    LFB1.BUKRS = '1000';  -- Company Code ID

3. Retrieving Bank Information for Vendors

To fetch the bank details associated with each vendor, query the LFBK table:

SELECT 
    LFBK.LIFNR AS Vendor_Number,
    LFBK.BANKS AS Bank_Country,
    LFBK.BANKL AS Bank_Key,
    LFBK.BANKN AS Bank_Account
FROM 
    LFBK
WHERE 
    LFBK.LIFNR = '200000';  -- Vendor ID

4. Retrieving Vendor Purchasing Data

To get data related to purchasing organizations, such as currency and terms of delivery, use the LFM1 table:

SELECT 
    LFM1.LIFNR AS Vendor_Number,
    LFM1.EKORG AS Purchasing_Organization,
    LFM1.WAERS AS Order_Currency,
    LFM1.INCO1 AS Incoterms
FROM 
    LFM1
WHERE 
    LFM1.EKORG = '2000';  -- Purchasing Organization ID

Commonly Asked Questions

1. How Do I Check Which Vendors Were Recently Added?

You can use the ERDAT (created-on date) field in the LFA1 table to find out which vendors were recently created:

SELECT 
    LFA1.LIFNR AS Vendor_Number,
    LFA1.NAME1 AS Vendor_Name,
    LFA1.ERDAT AS Created_On
FROM 
    LFA1
WHERE 
    LFA1.ERDAT >= '20231001';  -- Vendors created after a specific date

2. How Do I Retrieve Vendor Bank Information?

The LFBK table is your go-to for vendor bank details. For each vendor, you can query bank account numbers and associated countries.

3. How Can I Track Vendor Changes?

You can track changes made to vendor master data using the change document functionality in SAP. By calling the CHANGEDOCUMENT_READ_HEADERS function, you can view historical changes for a particular vendor.

Conclusion

Vendor master data is a fundamental part of SAP’s Material Management and Financial Accounting modules. Understanding how to extract this data using SQL queries is crucial for backend users who need to manage vendors, purchasing, and payments. Whether you’re pulling general data, company-specific information, or purchasing-related details, mastering SQL queries for vendor master tables allows you to maintain efficient and accurate data reporting.

By querying key tables such as LFA1, LFB1, LFM1, and LFBK, you can retrieve comprehensive vendor data that supports procurement, finance, and compliance operations.

Learn about Storage Location Tables in SAP