Building Finance Open Item Reports: A SQL Stored Procedure Approach

Building Finance Open Item Reports: A SQL Stored Procedure Approach

Introduction

Managing open items—such as accounts receivable and accounts payable—is fundamental to accurate financial reporting. Yet producing reliable open item reports for historical periods presents a significant technical challenge due to how SAP systems store this data.

The Core Problem: Real-Time vs. Historical Data

SAP's open item tables (BSIK for vendor open items, BSID for customer open items, and ACDOCA in S/4HANA) maintain current status in real-time. This means they always reflect open item status as of today, not as it existed at any historical point. When finance teams need to recreate what was open at month-end three months ago, the current table structure provides no direct path to that information.

Why Manual Snapshots Fall Short

Many organizations attempt to solve this by taking manual snapshots during period-end closing, but this approach is fundamentally flawed:

Timing Issues: During closing, clearing entries are often backdated to the previous month. If a snapshot is captured before these backdated clearings are processed, it will show items as open when they should be cleared—creating discrepancies in the historical record.

Multiple Snapshot Dilemma: Taking additional snapshots after backdated entries requires ensuring no new open items are created between snapshots. This is nearly impossible since business operations—sales transactions, purchase orders, payments—continue regardless of the finance closing calendar.

Manual Process Risks: The coordination required across teams, the potential for human error, and the time-consuming nature of manual snapshot management make this approach unreliable at scale.

The Solution: Automated Historical Reconstruction

This blog presents a robust alternative: a SQL stored procedure that programmatically reconstructs open item status for any historical period. By analyzing both current open items and cleared items with their clearing dates, we can accurately determine which items were open at any point in the past.

This approach eliminates manual snapshot processes while providing a reliable foundation for trend analysis, aging reports, and comprehensive financial analytics across multiple historical periods.

The Challenge: Historical Open Item Analysis

Financial teams frequently need to answer questions like:

  • What invoices were outstanding at month-end three months ago?
  • How has our aging profile changed over the past year?
  • Which vendors had open items during a specific period?

The complexity arises because an item's "open" status is dynamic:

  1. Currently Open Items: Items that are still uncleared as of today
  2. Previously Open Items: Items that were open during a historical period but have since been cleared
  3. Time-Point Analysis: Determining the exact open status at any specific date in the past

Consider this timeline example:

Invoice Posting Date Clearing Date Status at 2024-01-31 Status at 2024-03-31
INV001 2024-01-15 Still Open Open Open
INV002 2024-01-20 2024-02-15 Open Cleared
INV003 2024-02-10 2024-03-20 Not Posted Yet Cleared
INV004 2024-03-05 Still Open Not Posted Yet Open

This complexity multiplies when analyzing multiple periods, vendors, and company codes across an enterprise environment.

Understanding the Data Structure

Our solution works with SAP's Financial Accounting data structure, including the Accounts Payables Line Items table, the Accounts Receivable Line Items table, and ACDOCA table for General Ledger Open Items.

In this blog post we take accounts payable table as example. Key fields include:

  • RECONCILIATION_ACCOUNT: G/L account codes (e.g., '22410000' for trade payables)
  • DOCUMENT_TYPE: Various document types (RX, RF, KJ, etc.) representing different transaction types
  • ITEM_STATUS: 'C' for cleared items, other values for open items
  • POSTING_DATE_IN_THE_DOCUMENT: When the document was originally posted
  • CLEARING_DATE: When the item was cleared (null for open items)

The business logic requires filtering for:

  • Specific reconciliation accounts for trade payables
  • Relevant document types for AP processing
  • Proper handling of cleared vs. open status

Solution: The buildoiindex Stored Procedure

Our approach uses a SQL stored procedure that systematically builds an Open Item Index across multiple periods. The procedure takes a single parameter—the number of historical periods to process—and constructs a comprehensive view of open items for each period.

Core Logic: Two Categories of Open Items

For any given cutoff date, an item can be considered "open" in two scenarios:

1. Still Open Items

with stillopen_ap AS (
    SELECT * FROM BSIK_ACCOUNTS_PAYABLES_LINE_ITEMS 
    WHERE RECONCILIATION_ACCOUNT IN ('22410000','22410200')
    AND DOCUMENT_TYPE IN ('RX', 'RF', 'KJ','KG','RE','KR','RN','KL','KX','X1','RA','X2')
    AND ITEM_STATUS NOT IN ('C')
    AND POSTING_DATE_IN_THE_DOCUMENT <= :dt
)

These are items that:

  • Were posted on or before the cutoff date
  • Are still not cleared (ITEM_STATUS ≠ 'C')
  • Meet all the business filtering criteria

2. Was Open Items

wasopen_ap AS (
    SELECT * FROM BSIK_ACCOUNTS_PAYABLES_LINE_ITEMS 
    WHERE RECONCILIATION_ACCOUNT IN ('22410000','22410200')
    AND DOCUMENT_TYPE IN ('RX', 'RF', 'KJ','KG','RE','KR','RN','KL','KX','X1','RA','X2')
    AND ITEM_STATUS IN ('C')
    AND POSTING_DATE_IN_THE_DOCUMENT <= :dt
    AND CLEARING_DATE > :dt
)

These are items that:

  • Were posted on or before the cutoff date
  • Are now cleared (ITEM_STATUS = 'C')
  • But were cleared AFTER the cutoff date (so they were open during that period)

The Complete Stored Procedure

create or replace procedure buildoiindex(noofperiods integer)
  returns varchar
  language sql
  as
  $$
    declare
      dt date;
      
    begin
      
      -- create a permanent table to hold the result, open item index
      create or replace table oiindex (
        acctype varchar(2), 
        compcode varchar(4), 
        docno varchar(16), 
        bp varchar(16), 
        cutoffperiod date
      );
      
      -- create a loop
      for i in 1 to noofperiods do
        -- this will return last day of a month, i.e. 2024-12-31
        dt := last_day(add_months(current_date(), -1*i));
        
        -- now building AP open items
        with stillopen_ap AS (
            SELECT * FROM BSIK_ACCOUNTS_PAYABLES_LINE_ITEMS 
            WHERE RECONCILIATION_ACCOUNT IN ('22410000','22410200')
            AND DOCUMENT_TYPE IN ('RX', 'RF', 'KJ','KG','RE','KR','RN','KL','KX','X1','RA','X2')
            AND ITEM_STATUS NOT IN ('C')
            AND POSTING_DATE_IN_THE_DOCUMENT <= :dt
            ),
        wasopen_ap AS (
            SELECT * FROM BSIK_ACCOUNTS_PAYABLES_LINE_ITEMS 
            WHERE RECONCILIATION_ACCOUNT IN ('22410000','22410200')
            AND DOCUMENT_TYPE IN ('RX', 'RF', 'KJ','KG','RE','KR','RN','KL','KX','X1','RA','X2')
            AND ITEM_STATUS IN ('C')
            AND POSTING_DATE_IN_THE_DOCUMENT <= :dt
            AND CLEARING_DATE > :dt
            ),
        open_ap AS (
            SELECT * FROM stillopen_ap UNION ALL SELECT * FROM wasopen_ap)
        SELECT COMPANY_CODE, ACCOUNTING_DOCUMENT_NUMBER, ACCOUNT_NUMBER_OF_SUPPLIER_VENDOR 
        FROM open_ap
        ORDER BY COMPANY_CODE, ACCOUNTING_DOCUMENT_NUMBER, ACCOUNT_NUMBER_OF_SUPPLIER_VENDOR;
        
        -- write the result to the table        
        INSERT INTO oiindex 
        SELECT 'AP', *, :dt FROM table(result_scan(last_query_id()));
        
      end for;
     
      return 'completed insert into table';
      
    end;
  $$
  ;

Key Technical Features

1. Dynamic Date Calculation

dt := last_day(add_months(current_date(), -1*i));

This calculates the last day of each historical month, providing consistent month-end snapshots. For example, if run in September 2025:

  • i=1: August 31, 2025
  • i=2: July 31, 2025
  • i=3: June 30, 2025

2. Result Scan Pattern

INSERT INTO oiindex 
SELECT 'AP', *, :dt FROM table(result_scan(last_query_id()));

The result_scan(last_query_id()) function captures the result of the previous CTE query, allowing us to insert the results along with additional metadata (account type 'AP' and cutoff period).

3. Parameterized Execution

The procedure accepts noofperiods as a parameter, making it flexible for different analysis requirements:

  • Monthly financial close: call buildoiindex(1);
  • Quarterly analysis: call buildoiindex(3);
  • Annual trending: call buildoiindex(12);

Output Structure and Usage

The procedure creates an oiindex table with the following structure:

Column Description
acctype Account type ('AP' for Accounts Payable)
compcode Company code
docno Document number
bp Business partner (vendor) code
cutoffperiod The month-end date for which this was an open item

Sample Output

-- build the index for past twelve periods
call buildoiindex(12);

-- view the result  
select * from oiindex order by cutoffperiod desc, compcode, bp;
acctype compcode docno bp cutoffperiod
AP 1000 INV001 VEN001 2024-03-31
AP 1000 INV004 VEN003 2024-03-31
AP 1000 INV001 VEN001 2024-01-31
AP 1000 INV002 VEN002 2024-01-31

Automating with Snowflake Tasks and CRON Scheduling

To make this open item analysis truly production-ready, we can automate the stored procedure execution using Snowflake's native task scheduling capabilities. This ensures consistent, timely updates to your open item index without manual intervention.

Creating a Basic Task for Monthly Execution

Here's how to create a task that runs our open item procedure based on schedule:

-- Create a task to run on the first five working days of each month at 2 AM
-- Note: This runs on days 1-7 but only on weekdays (Mon-Fri)
-- Effectively captures the first 5 business days of each month
CREATE OR REPLACE TASK monthly_open_item_refresh
  WAREHOUSE = 'FINANCE_WH'
  SCHEDULE = 'USING CRON 0 0 2 1-7 * 1-5 UTC'
  COMMENT = 'Refresh of open item index on first 5 working days of each month'
AS
  CALL buildoiindex(12);

CRON Expression Deep Dive

As a refresher, CRON expressions in Snowflake follow the standard format with six fields:

┌───────────── second (0-59)
│ ┌─────────── minute (0-59)
│ │ ┌───────── hour (0-23)
│ │ │ ┌─────── day of month (1-31)
│ │ │ │ ┌───── month (1-12)
│ │ │ │ │ ┌─── day of week (0-6, Sunday=0)
│ │ │ │ │ │
* * * * * *

Activating the Task

After creating a task, it must be explicitly started before it will execute:

-- Start the task (required after creation)
ALTER TASK monthly_open_item_refresh RESUME;

This automated approach ensures your open item analysis runs consistently and reliably, providing finance teams with up-to-date historical snapshots without manual intervention.

Conclusion

This stored procedure approach provides a robust foundation for financial open item analysis by:

  • Automating the complex logic of determining historical open item status
  • Standardizing the data structure for consistent reporting
  • Scaling efficiently across multiple periods and company codes
  • Enabling advanced analytics through a clean, time-series data structure

The solution transforms "snapshot" financial data into an accessible format for business intelligence tools, enabling finance teams to perform sophisticated aging analysis, trend identification, and other management reporting.

The procedure can be easily adapted for other snapshot data such as accounts receivable, or general ledger open items data and make them easy to consume for modern financial analytics.

This technical guide demonstrates a production-ready solution for building comprehensive open item indexes in Snowflake, optimized for financial reporting and business intelligence applications.

Comments

Post a Comment

Popular posts from this blog

Building dbt Model Lineage in Python

Image
Building DBT Lineage: A Technical Deep Dive Introduction In the world of data engineering, dbt has become the de facto standard for transforming data in the warehouse. As a project grows from a handful of models to hundreds or even thousands, understanding the web of dependencies—the lineage—becomes critically important. While dbt docs provides a powerful visual graph, there are many scenarios where you need programmatic access to this lineage for advanced automation and analysis. This article will guide you through building a Python script to parse a dbt project, extract model dependencies from ref() functions, and construct a complete lineage file that you can use for powerful, automated analysis. The "Why": The Power of Programmatic Lineage Why do we need to go beyond the visual graph? Automated Impact Analysis : When you modify a core model, you n...
Read more