SAP FICO Tables for Financial Management and Reporting

SAP Financial Accounting (FI) and Controlling (CO) are core modules in SAP ERP systems, handling all financial transactions and cost management processes. Understanding the key tables in SAP FICO is essential for retrieving data to solve business problems efficiently. This blog post explores the most crucial SAP FICO tables and provides SQL queries for common business scenarios that back-end SAP users often encounter.

Key SAP FICO Tables

1. Accounting Document Tables
   These tables store all the accounting documents, such as invoices, payments, and other financial transactions.
   • BKPF: Stores header data for accounting documents (e.g., document number, fiscal year, company code).
   • BSEG: Contains line item data for each accounting document, including debit/credit amounts and cost centers.
2. Customer and Vendor Master Data
   Customer and vendor master data tables store information regarding business partners that a company deals with.
   • KNA1: Stores general data for customer master records (e.g., customer name, address).
   • KNB1: Contains company-specific information for customers (e.g., credit control area, reconciliation accounts).
   • LFA1: Stores general data for vendor master records.
   • LFB1: Contains company-specific vendor data, such as terms of payment.
3. General Ledger (G/L) Master Data
   These tables store general ledger accounts used in accounting.
   • SKA1: Stores the chart of accounts for G/L accounts.
   • SKB1: Contains company-specific G/L account data, such as balance sheets and profit/loss indicators.
4. Cost Center and Profit Center Accounting Tables
   These tables are essential for internal controlling and profitability analysis.
   • CSKS: Stores cost center master data.
   • COSP: Contains actual cost totals posted to cost centers.
   • CEPC: Stores profit center master data.
   • GLPCA: Contains profit center accounting data, including revenue and expense totals.
5. Asset Accounting Tables
   Asset management involves tracking and managing fixed assets such as buildings, machinery, and vehicles.
   • ANLA: Stores general asset master data.
   • ANEP: Contains line item details for asset transactions, such as acquisition, depreciation, and retirement.
6. Document Parking and Recurring Entries
   Document parking allows for saving documents in an incomplete status for later completion.
   • VBKPF: Stores parked document headers.
   • BKDF: Stores recurring entry headers, used for transactions like rent and salaries that repeat periodically.

Learn about Optimizing Queries and Managing Material Data with the MARA Table in SAP

Sample SQL Queries for Common Business Problems

Here are some typical SQL queries that back-end users of SAP FICO can use to retrieve data for financial management and reporting.

1. Query to Retrieve General Ledger Account Balances

You might want to fetch the balances of G/L accounts for a specific period to analyze the financial position of the company.

SELECT SKB1.SAKNR AS Account_Number,
       SKB1.BUKRS AS Company_Code,
       SKB1.SALDO AS Account_Balance,
       T001.BUTXT AS Company_Name
FROM SKB1
JOIN T001 ON SKB1.BUKRS = T001.BUKRS
WHERE SKB1.SAKNR = '100000'  -- Example G/L account number
  AND SKB1.BUKRS = '1000'  -- Example company code
  AND SKB1.GJAHR = '2023';  -- Example fiscal year

2. Retrieve Customer Outstanding Payments

This query fetches the open items (invoices) from customers that are yet to be paid.

SELECT KNA1.KUNNR AS Customer_Number,
       KNA1.NAME1 AS Customer_Name,
       BSID.BELNR AS Invoice_Number,
       BSID.DMBTR AS Invoice_Amount,
       BSID.ZFBDT AS Due_Date
FROM BSID
JOIN KNA1 ON BSID.KUNNR = KNA1.KUNNR
WHERE BSID.BUZEI = '001'  -- Example line item number
  AND BSID.BUKRS = '1000'  -- Example company code
  AND BSID.BELNR = '4500001234';  -- Example document number

3. Retrieve Vendor Payment Details

This query retrieves details about payments made to vendors for a specific period.

SELECT LFA1.LIFNR AS Vendor_Number,
       LFA1.NAME1 AS Vendor_Name,
       BSEG.BELNR AS Document_Number,
       BSEG.WRBTR AS Payment_Amount,
       BSEG.BUDAT AS Posting_Date
FROM BSEG
JOIN LFA1 ON BSEG.LIFNR = LFA1.LIFNR
WHERE BSEG.KOART = 'K'  -- 'K' denotes vendor accounts
  AND BSEG.BUKRS = '1000'  -- Example company code
  AND BSEG.BUDAT BETWEEN '20230901' AND '20230930';  -- Example date range

4. Cost Center Expense Query

This query is useful for retrieving actual expenses posted to cost centers during a specific period.

SELECT CSKS.KOSTL AS Cost_Center,
       COSP.WRTTP AS Transaction_Type,
       COSP.WTG001 AS Total_Amount
FROM COSP
JOIN CSKS ON COSP.KOSTL = CSKS.KOSTL
WHERE CSKS.BUKRS = '1000'  -- Example company code
  AND COSP.GJAHR = '2023'  -- Example fiscal year
  AND COSP.KSTAR = '400000';  -- Example cost element (expense)

5. Asset Depreciation Query

To retrieve the depreciation details for specific assets, this query extracts data from the asset accounting tables.

SELECT ANLA.ANLNR AS Asset_Number,
       ANLA.TXT50 AS Asset_Description,
       ANEP.BELNR AS Document_Number,
       ANEP.NDABJ AS Fiscal_Year,
       ANEP.AFAKR AS Depreciation_Key,
       ANEP.BUZEI AS Line_Item,
       ANEP.WERT AS Depreciation_Amount
FROM ANLA
JOIN ANEP ON ANLA.ANLNR = ANEP.ANLNR
WHERE ANLA.BUKRS = '1000'  -- Example company code
  AND ANEP.GJAHR = '2023';  -- Example fiscal year

Conclusion

SAP FICO tables are the backbone of financial and controlling data in any SAP ERP system. Knowing which tables to query and how to retrieve the necessary data can help solve business problems effectively. The SQL queries provided above can assist SAP back-end users in retrieving critical data for financial reporting, customer/vendor analysis, and asset management. By mastering these queries, you can efficiently handle day-to-day financial management and reporting tasks.

Read more about Classification tables in SAP