Mastering Customer Master Data Tables in SAP

Understanding the structure and nuances of SAP’s customer master data tables is crucial for any data analyst looking to leverage SAP data within SQL Server. This detailed exploration will cover the primary tables involved in managing customer information—KNA1, KNB1, KNVV, and ADRC—highlighting their key attributes and explaining how they interact with one another.

KNA1 – General Data

The KNB1 table in SAP is essentially the financial master data table for customers. It stores information specific to each company code, which is necessary for managing financial transactions related to customers. Here’s a simplified breakdown of what the KNB1 table includes:

1. Customer Number: Links this financial data to the general customer data in the KNA1 table.
2. Company Code: Identifies the specific company or subsidiary within a larger corporation that this data pertains to.
3. Reconciliation Account: A crucial element for financial accounting, this account is used to reconcile transactions in sub-ledgers with the general ledger.
4. Payment Terms: Describes the conditions under which payments should be made by the customer, including deadlines and potential discounts for early payment.
5. Credit Limit: The maximum amount of credit that the company is willing to extend to the customer.
6. Dunning Data: Related to the process of reminding customers of their due payments.

In essence, KNB1 holds the financial aspects of customer data that help the financial department manage credit, payments, and overall financial relationships with customers within each specific company code in a multi-company environment. This table is vital for maintaining the financial integrity and smooth operation of business transactions in SAP’s financial accounting module

• KUNNR: Customer Number, a unique identifier.
• NAME1: Name of the customer.
• ORT01: City where the customer is located.
• LAND1: Country of the customer.
• REGIO: Region within the country.
• PSTLZ: Postal Code.
• TELNR: Telephone number.
• ADRNR: Address number linking to the ADRC table.
• KTOKD: Account group which categorizes customers into groups like sold-to, shipped-to.

KNB1 – Company Code Data

The KNB1 table in SAP stores financial information related to customers, but it’s specific to each company code. This means it helps manage details that are unique to each part of a larger company, especially when a company has multiple subsidiaries or operates in various regions.

Here’s what the KNB1 table typically includes:

• Customer Number: This connects the financial records in KNB1 to the general customer details in another table called KNA1.
• Company Code: Identifies which part of the company the financial data is relevant to.
• Reconciliation Account: Important for financial reporting, this is the general ledger account where all transactions from a customer are consolidated.
• Payment Terms: These are the agreed conditions under which a customer makes payments, like deadlines or discounts for early payments.
• Credit Limit: The maximum amount of credit the company is willing to extend to this customer.

Essentially, KNB1 is crucial for managing how money flows between a company and its customers within the specific frameworks set by different parts of the company.

• KUNNR: Customer Number, linking back to KNA1.
• BUKRS: Company Code where the customer is registered.
• AKONT: Main Ledger Account used for reconciliation.
• ZTERM: Payment terms defining how and when payments are made.
• FDGRV: Credit limit for the customer.
• VMONB: Dunning procedure indicating the action to follow for overdue payments.

KNVV – Sales Data

The KNVV table in SAP is dedicated to storing sales-related data for customers. It’s specific to each combination of sales organization, distribution channel, and division. This segmentation allows companies to manage customer information in a detailed and structured manner according to different sales areas.

Here’s a simple breakdown of what you can find in the KNVV table:

• Customer Number (KUNNR): Links sales data to the general customer profile found in the KNA1 table.
• Sales Organization (VKORG): Identifies the sales unit responsible for the transaction.
• Distribution Channel (VTWEG): Specifies the channel through which the products or services are sold (e.g., wholesale, retail, or direct sales).
• Division (SPART): Indicates the product line or category.
• Sales Office (VKBUR): Optional field that can specify the local office responsible for the customer.
• Sales Group (VKGRP): Optional field that refers to a group within a sales office, which can further segment customer management.
• Shipping Conditions (VSBED): Specifies how products are delivered to the customer.
• Billing Conditions (FKARA): Defines how billing is handled for the customer.

Overall, the KNVV table is critical for managing sales-specific details, allowing businesses to tailor their sales strategies and operations to the needs of different customer segments effectively.

• KUNNR: Customer Number, for linking to KNA1.
• VKORG: Sales Organization managing the customer.
• VTWEG: Distribution Channel through which the sales occur.
• SPART: Division representing a product line.
• ERDAT: Date when the record was created.
• VWERK: Plant responsible for delivery.
• KDGRP: Customer group for segmentation and analysis.

ADRC – Address Data

The ADRC table in SAP holds detailed address information for various entities like customers, vendors, and employees. It is a central address table that can be referenced by other SAP modules and master data tables to ensure consistency and accuracy in address data across the system.

Here’s a simple overview of what the ADRC table includes:

• ADDRNUMBER: A unique identifier for each address record.
• DATE_FROM: The validity start date of the address.
• NATION: Country code.
• STREET: Street name and number.
• POST_CODE1: Primary postal code.
• CITY1: City name.
• COUNTRY: Country name.
• REGION: Region code or state.
• TIME_ZONE: The applicable time zone for the address.
• LANGU: Language in which the address is maintained.

This table is crucial for managing comprehensive address details in a centralized way, allowing SAP to use this information across various transactions and communications effectively. It supports the accuracy and consistency of address data in communications, billing, shipping, and legal documentation.

How These Tables Interact

The beauty of SAP’s design is in its integration. KNA1 serves as the hub, connecting with KNB1 and KNVV via the customer number (KUNNR). It links to ADRC through the address number (ADRNR), ensuring all customer-related data—from general information to specific financial details and address information—are interconnected. This setup allows for comprehensive data analysis and efficient data management.

Interactions and Relationships

• KNA1 is the central node linking to other customer master tables. The customer number (KUNNR) from KNA1 is used as a foreign key in both KNB1 and KNVV, enabling the connection of general, company-specific, and sales-specific data.
• KNB1 and KNVV both link back to KNA1 using the customer number, allowing detailed financial and sales data to be accessed alongside general customer data.
• ADRC is linked through address numbers (ADRNR) referenced in KNA1, integrating detailed address information with the general customer profile.

How to fetch data using SQL

For SQL Server analysts, these tables provide a rich dataset for complex queries and deep analytics. You can join these tables to form comprehensive views of customer data, aiding in financial analysis, sales forecasting, and strategic planning.

For example, to view customer financial and sales details in one query, you might use:

SELECT 
    a.KUNNR AS Customer_Number,
    a.NAME1 AS Customer_Name,
    b.ORT01 AS City,
    c.VKORG AS Sales_Organization,
    c.VTWEG AS Distribution_Channel
FROM 
    KNA1 a
JOIN 
    ADRC b ON a.ADRNR = b.ADRNR
JOIN 
    KNVV c ON a.KUNNR = c.KUNNR
WHERE 
    a.LAND1 = 'DE';

This query fetches the customer number, name, city, sales organization, and distribution channel for all customers in Germany (‘DE’).

As a data analyst new to SAP, exploring these tables with SQL queries can provide deep insights into customer dynamics and operational efficiencies. It’s also advisable to look into additional tables like KNBK for bank details or KNVP for partner functions to extend the analysis further.

By understanding these interactions and mastering the SQL queries to fetch and analyze the data, you can significantly contribute to strategic business decisions in an SAP environment.

You may also learn How to Join BSEG and BKPF Tables in SAP?