Understanding Purchase Order Tables in SAP

Understanding how SAP handles purchase orders (PO) is critical for anyone working on the backend of SAP systems. Purchase orders are essential components in procurement processes, and SAP organizes this data across multiple interconnected tables. For newcomers to SAP, fetching and analyzing this data using SQL can seem daunting. This guide simplifies the process by explaining key SAP PO tables, their relationships, and common SQL queries.

Whether you’re troubleshooting issues, generating reports, or integrating SAP with external systems, this article will equip you with the knowledge to retrieve purchase order data efficiently.

What Are SAP Purchase Orders?

A purchase order in SAP is a formal request issued to vendors to provide goods or services. It acts as a binding contract that includes details like item descriptions, quantities, and agreed prices. Purchase orders streamline procurement and help maintain accurate records.

SAP stores purchase order information in a modular structure. Different tables are dedicated to various aspects of the PO lifecycle, such as header information, item-level details, and transaction history. Understanding how these tables interconnect will allow you to fetch the exact data you need.

Key SAP Purchase Order Tables and Their Roles

SAP uses several tables to manage purchase order data. Below are the most important ones and their purposes.

EKKO – Purchasing Document Header

The EKKO table contains high-level details about purchase orders. It is the central table for all purchasing documents.

Key fields include:

• EBELN: The unique number identifying each purchasing document.
• LIFNR: Vendor number, linking to the supplier details in vendor master tables.
• AEDAT: Date when the document was created.
• BSTYP: Document category (e.g., purchase order, framework agreement).

This table acts as the entry point for all PO-related data.

EKPO – Purchasing Document Items

EKPO provides line-item-level details for purchase orders. Each record in EKPO corresponds to a specific item in the PO.

Key fields include:

• EBELN: Links to the header record in EKKO.
• EBELP: Line-item number within the purchase order.
• MATNR: Material number representing the goods or services ordered.
• MENGE: Quantity ordered for the item.
• NETPR: Net price of the item.

If you’re looking to analyze or report on individual items within a PO, EKPO is the table to query.

EKBE – Purchasing Document History

EKBE captures the transaction history of purchase orders. It logs events like goods receipts, invoice receipts, and order cancellations.

Key fields include:

• EBELN: Links to the corresponding purchase order.
• BWART: Movement type, such as goods receipt (GR) or invoice receipt (IR).
• MENGE: Quantity of goods received or processed.

This table is essential for tracking the lifecycle and fulfillment of POs.

EKKN – Account Assignment in Purchasing

EKKN contains accounting details for purchase orders, especially when cost allocation is involved.

Key fields include:

• EBELN: Purchase order number.
• KOSTL: Cost center linked to the PO item.
• AUFNR: Order number, used for internal orders or projects.

If your POs include cost distribution, EKKN will provide the required details.

EKET – Scheduling Agreement Schedule Lines

EKET stores delivery schedules for POs. It is commonly used for orders with multiple deliveries spread over time.

Key fields include:

• EBELN: Purchase order number.
• EINDT: Delivery date for the scheduled item.
• MENGE: Quantity scheduled for delivery.

This table ensures visibility into when and how items will be delivered.

How SAP Purchase Order Tables Are Connected

Understanding the relationships between purchase order tables is crucial for building meaningful queries. The following are the primary relationships:

• EKKO to EKPO: Use the field EBELN to join header-level data with item-level data.
• EKPO to EKBE: Use the fields EBELN and EBELP to link item-level data with historical movements.
• EKPO to EKKN: Use the fields EBELN and EBELP to link items with account assignments.
• EKPO to EKET: Use the fields EBELN and EBELP to fetch delivery schedules for specific items.

Common SQL Queries for Purchase Order Data Retrieval

Retrieve All Purchase Orders with Items

This query fetches header and item-level data for all purchase orders.

SELECT
    EKKO.EBELN AS "PO Number",
    EKKO.LIFNR AS "Vendor",
    EKPO.EBELP AS "Item Number",
    EKPO.MATNR AS "Material",
    EKPO.MENGE AS "Quantity",
    EKPO.NETPR AS "Net Price"
FROM
    EKKO
INNER JOIN
    EKPO ON EKKO.EBELN = EKPO.EBELN
ORDER BY
    EKKO.EBELN, EKPO.EBELP;

Fetch Open Purchase Orders

This query identifies open purchase orders by calculating the difference between ordered and received quantities.

SELECT
    EKKO.EBELN AS "PO Number",
    EKKO.LIFNR AS "Vendor",
    EKPO.EBELP AS "Item Number",
    EKPO.MATNR AS "Material",
    EKPO.MENGE AS "Ordered Quantity",
    COALESCE(SUM(EKBE.MENGE), 0) AS "Received Quantity",
    (EKPO.MENGE - COALESCE(SUM(EKBE.MENGE), 0)) AS "Open Quantity"
FROM
    EKKO
INNER JOIN
    EKPO ON EKKO.EBELN = EKPO.EBELN
LEFT JOIN
    EKBE ON EKPO.EBELN = EKBE.EBELN AND EKPO.EBELP = EKBE.EBELP
WHERE
    (EKPO.MENGE - COALESCE(SUM(EKBE.MENGE), 0)) > 0
GROUP BY
    EKKO.EBELN, EKKO.LIFNR, EKPO.EBELP, EKPO.MATNR, EKPO.MENGE;

Get Purchase Orders by Vendor

This query retrieves all purchase orders for a specific vendor.

SELECT
    EKKO.EBELN AS "PO Number",
    EKKO.LIFNR AS "Vendor",
    EKPO.EBELP AS "Item Number",
    EKPO.MATNR AS "Material",
    EKPO.MENGE AS "Quantity",
    EKPO.NETPR AS "Net Price"
FROM
    EKKO
INNER JOIN
    EKPO ON EKKO.EBELN = EKPO.EBELN
WHERE
    EKKO.LIFNR = 'Vendor_Code'
ORDER BY
    EKKO.EBELN;

Replace 'Vendor_Code' with the vendor’s unique identifier.

Learn about ACDOCA Table In SAP

Understanding Purchase Order Status in SAP

SAP tracks purchase order status at both the document and item levels, making it easier to monitor progress and fulfillment.

• Document-Level Status: The EKKO.STATUS field reflects the overall status of the PO, such as Open or Closed.
• Item-Level Status: Fields like EKPO.ELIKZ indicate delivery status, while EKPO.INSMK shows the invoice status.
• Historical Status: The EKBE.BWART field identifies movement types, such as Goods Receipt (GR) or Invoice Receipt (IR).

Best Practices for Querying SAP PO Data

• Optimize Joins: Use indexed fields for efficient joins and avoid unnecessary table combinations.
• Filter Early: Apply conditions in the WHERE clause to limit the dataset before performing joins.
• Handle Null Values: Use functions like COALESCE to replace nulls with default values.
• Understand Business Logic: Familiarize yourself with movement types and document categories to build meaningful queries.

Frequently Asked Questions

How can I check open purchase orders?

Use the query in the “Fetch Open Purchase Orders” section to identify POs where the ordered quantity exceeds the received quantity.

What is the difference between EKPO and EKKO?

The EKKO table contains header-level data for purchase orders, while EKPO stores item-level details. Each record in EKKO may correspond to multiple records in EKPO.

Can I join purchase order tables with material master data?

Yes, you can join the EKPO table with the material master table (MARA) using the MATNR field to fetch additional material details.

Additional Resources

For more insights, visit:

• SAP Community
• SAP Help Portal
• Your SQL Guide to SAP Tables

This guide serves as a comprehensive starting point for anyone looking to understand SAP purchase order tables and retrieve data using SQL. Use these tips and queries to improve your SAP backend workflows and reporting.

Learn about Production Planning Tables in SAP