A Comprehensive Guide to FI Tables in SAP

The Financial Accounting (FI) module in SAP is a core component designed to manage all financial transactions within an organization. Understanding the key FI tables, their purposes, and how to retrieve information from these tables using SQL queries is essential for anyone working with SAP’s backend database. This guide will introduce you to the primary FI tables in SAP, explain their interconnections, and provide examples of SQL queries to extract various types of information.

Key FI Tables in SAP

1. BKPF (Accounting Document Header)
   • Contains header data for accounting documents, such as document number, posting date, and document type.
2. BSEG (Accounting Document Segment)
   • Stores line item details for accounting documents, including general ledger (G/L) account numbers, amounts, and cost centers.
3. BSIS (G/L Account: Line Items)
   • Holds line items for G/L accounts that have a debit balance.
4. BSAS (G/L Account: Line Items)
   • Contains line items for G/L accounts that have a credit balance.
5. BSIK (Vendor: Line Items)
   • Stores open items for vendor accounts, including invoice numbers and payment terms.
6. BSAK (Vendor: Cleared Items)
   • Holds cleared items for vendor accounts, showing details of settled invoices.
7. BSID (Customer: Line Items)
   • Contains open items for customer accounts, including invoice details and payment due dates.
8. BSAD (Customer: Cleared Items)
   • Stores cleared items for customer accounts, indicating which invoices have been paid.

How These Tables Are Interconnected

The FI tables in SAP are interconnected through key fields that facilitate efficient data management and retrieval. Here’s how they typically relate to each other:

• Document Number (BELNR): This is the primary key that links the header table (BKPF) with the line item table (BSEG).
• Fiscal Year (GJAHR): Often used along with document number to uniquely identify accounting documents.
• Account Number (KONTO): Connects G/L accounts, vendor accounts, and customer accounts with their respective line item tables (BSIS, BSAS, BSIK, BSAK, BSID, BSAD).

You might be interested to learn CO Tables in SAP

Example SQL Queries

1. Retrieve Basic Accounting Document Information

To get basic information about accounting documents, including header and line item details, you can join BKPF and BSEG:

SELECT
    BKPF.BUKRS AS Company_Code,
    BKPF.BELNR AS Document_Number,
    BKPF.GJAHR AS Fiscal_Year,
    BKPF.BUDAT AS Posting_Date,
    BSEG.BUZEI AS Line_Item,
    BSEG.HKONT AS G_L_Account,
    BSEG.WRBTR AS Amount,
    BSEG.KOSTL AS Cost_Center
FROM
    BKPF
JOIN
    BSEG ON BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE
    BKPF.BUKRS = '1000';  -- Specific Company Code

2. Retrieve Open Vendor Items

To get a list of open items for vendor accounts, query the BSIK table:

SELECT
    BSIK.BUKRS AS Company_Code,
    BSIK.BELNR AS Document_Number,
    BSIK.GJAHR AS Fiscal_Year,
    BSIK.LIFNR AS Vendor_Number,
    BSIK.BUZEI AS Line_Item,
    BSIK.WRBTR AS Amount,
    BSIK.ZFBDT AS Payment_Due_Date
FROM
    BSIK
WHERE
    BSIK.BUKRS = '1000';  -- Specific Company Code

3. Retrieve Cleared Customer Items

To get details of cleared items for customer accounts, query the BSAD table:

SELECT
    BSAD.BUKRS AS Company_Code,
    BSAD.BELNR AS Document_Number,
    BSAD.GJAHR AS Fiscal_Year,
    BSAD.KUNNR AS Customer_Number,
    BSAD.BUZEI AS Line_Item,
    BSAD.WRBTR AS Amount,
    BSAD.AUGDT AS Clearing_Date
FROM
    BSAD
WHERE
    BSAD.BUKRS = '1000';  -- Specific Company Code

4. Retrieve G/L Account Line Items

To get line items for G/L accounts, query the BSIS and BSAS tables:

SELECT
    BSIS.BUKRS AS Company_Code,
    BSIS.BELNR AS Document_Number,
    BSIS.GJAHR AS Fiscal_Year,
    BSIS.HKONT AS G_L_Account,
    BSIS.BUZEI AS Line_Item,
    BSIS.WRBTR AS Debit_Amount
FROM
    BSIS
WHERE
    BSIS.BUKRS = '1000'  -- Specific Company Code
UNION ALL
SELECT
    BSAS.BUKRS AS Company_Code,
    BSAS.BELNR AS Document_Number,
    BSAS.GJAHR AS Fiscal_Year,
    BSAS.HKONT AS G_L_Account,
    BSAS.BUZEI AS Line_Item,
    BSAS.WRBTR AS Credit_Amount
FROM
    BSAS
WHERE
    BSAS.BUKRS = '1000';  -- Specific Company Code

Conclusion

Understanding the structure and relationships of SAP FI tables is crucial for effectively managing financial data and transactions. The tables BKPF, BSEG, BSIS, BSAS, BSIK, BSAK, BSID, and BSAD each play a significant role in the Financial Accounting module. By mastering the joins and key fields, you can write efficient SQL queries to retrieve various types of information, aiding in better data analysis and decision-making.

You are might be interested to learn MM Tables in SAP