ERPL Examples

Real-world examples using ERPL for on-premise SAP integration.

RFC Examples

Read Customer Data

SELECT * FROM sap_read_table('KNA1', MAX_ROWS => 100);

Read Sales Orders

SELECT * FROM sap_read_table('VBAK', MAX_ROWS => 100);

Call BAPI Function

SELECT * FROM sap_rfc_invoke(
    'BAPI_FLIGHT_GETLIST',
    path => '/FLIGHT_LIST'
);

Get Function Metadata

SELECT * FROM sap_rfc_describe_function('BAPI_FLIGHT_GETLIST');

Get Table Structure

SELECT * FROM sap_describe_fields('KNA1');

BICS Examples

Execute BW Query

-- Open a state, place a characteristic on rows, filter, then read by id
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'q1');
SELECT state_id FROM sap_bics_rows('q1', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('q1', '0CALMONTH', '202401', op => 'SET');
SELECT * FROM sap_bics_result('q1');

List Available Cubes

SELECT * FROM sap_bics_show(obj_type => 'CUBE');

Get Query Lineage

-- All lineage edges touching one BEx query
SELECT *
FROM sap_bics_lineage_edges()
WHERE tgt_name = '0D_FC_NW_C01_Q0008' OR src_name = '0D_FC_NW_C01_Q0008';

ODP Examples

Delta Replication

-- First call: auto-DELTAINIT (full snapshot + delta-pointer registered).
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

-- Subsequent calls: only the changes since the previous call.
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

-- Release the cursor when the pipeline finishes (resumable on next run).
PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', 'VBAK$F');

Full Replication

-- One-shot: opens a FULL cursor, streams, auto-closes.
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

Check Subscriptions

-- ERPL-owned subscriptions
SELECT * FROM sap_odp_show_subscriptions();

-- Every subscriber on a given source (cross-team visibility)
SELECT * FROM sap_odp_get_subscriptions('BW', 'VBAK$F');

Probe Before Extract

-- Cheap last-modified probe (no cursor side effects)
SELECT * FROM sap_odp_get_last_modified('BW', 'VBAK$F');

Preview Data

SELECT * FROM sap_odp_preview('BW', 'VBAK$F');

APE Examples

Discover CDS Entities

-- Search rather than browsing the whole tree
SELECT object_name, object_path, released
FROM sap_ape_show(search => 'SALESORDER');

-- Look before extracting: no graph, no subscription
SELECT * FROM sap_ape_preview('I_SALESORDER', max_rows => 20);

CDS Delta With Deletes

-- First call: initial load, and SAP generates DHCDC triggers (slow, asynchronous)
SELECT count(*) FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL');

-- Subsequent calls: only changes. 'U' = insert-or-update after-image, 'D' = delete
SELECT "/1DH/OPERATION" AS op, count(*)
FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL')
GROUP BY 1;

Apply a Delta Idempotently

BEGIN;
  CREATE TEMP TABLE chg AS
    SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL');

  DELETE FROM salesorder
   WHERE SalesOrder IN (SELECT SalesOrder FROM chg WHERE "/1DH/OPERATION" = 'D');

  DELETE FROM salesorder
   WHERE SalesOrder IN (SELECT SalesOrder FROM chg WHERE "/1DH/OPERATION" = 'U');
  INSERT INTO salesorder
    SELECT * EXCLUDE ("/1DH/OPERATION") FROM chg WHERE "/1DH/OPERATION" = 'U';
COMMIT;

Recover an Interrupted Read

-- SAP commits each package on handover and cannot re-send it, so replay
-- the local spill. Touches no SAP state; works with SAP unreachable.
SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL', recover => true);

Manage Subscriptions

-- What exists, and what SAP calls it
SELECT cds_name, subscriber_process, subscription_name, status
FROM sap_ape_show_subscriptions();

-- Drop with the UNPREFIXED subscriber name — triggers persist otherwise
PRAGMA sap_ape_drop('I_SALESORDER', 'NIGHTLY_ETL');

Check Authorizations Before You Start

SELECT auth_object, checked_values, verdict, note
FROM sap_ape_check_authorizations('I_SALESORDER')
WHERE verdict <> 'ok';

Advanced Examples

Cross-Protocol Data Integration

-- Combine RFC and BICS data
WITH erp_customers AS (
  SELECT KUNNR AS customer_id, NAME1 AS customer_name
  FROM sap_read_table('KNA1', MAX_ROWS => 1000)
),
bw_sales AS (
  -- State 'q1' built above with sap_bics_begin/rows/filter
  SELECT
    "0D_NW_PROD" AS product,
    "0D_NW_NETV" AS net_value
  FROM sap_bics_result('q1')
)
SELECT
  e.customer_id,
  e.customer_name,
  b.net_value
FROM erp_customers e
JOIN bw_sales b ON e.customer_name = b.product;  -- illustrative join

ODP Delta Pipeline

-- Daily delta replication pipeline (run on schedule from your orchestrator)
WITH delta_data AS (
  SELECT
    *,
    ODQ_CHANGEMODE,
    CASE ODQ_CHANGEMODE
      WHEN 'C' THEN 'INSERT'
      WHEN 'U' THEN 'UPDATE'
      WHEN 'D' THEN 'DELETE'
      ELSE 'UNKNOWN'
    END AS change_type
  FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL')
)
SELECT
  change_type,
  COUNT(*) AS record_count,
  MIN(ERDAT) AS earliest_date,
  MAX(ERDAT) AS latest_date
FROM delta_data
GROUP BY change_type
ORDER BY change_type;

BICS Lineage Analysis

-- Aggregate edges related to one BEx query by source/target kind
WITH query_edges AS (
  SELECT src_kind, tgt_kind
  FROM sap_bics_lineage_edges()
  WHERE tgt_name = '0D_FC_NW_C01_Q0008' OR src_name = '0D_FC_NW_C01_Q0008'
),
lineage_analysis AS (
  SELECT src_kind, tgt_kind, COUNT(*) AS flow_count
  FROM query_edges
  GROUP BY src_kind, tgt_kind
)
SELECT
  src_kind,
  tgt_kind,
  flow_count,
  ROUND(flow_count * 100.0 / SUM(flow_count) OVER(), 2) AS percentage
FROM lineage_analysis
ORDER BY flow_count DESC;

RFC Function Discovery

-- Discover BAPI functions (columns: FUNCNAME, GROUPNAME, APPL, HOST, STEXT)
SELECT FUNCNAME, GROUPNAME, STEXT
FROM sap_rfc_show_function(FUNCNAME => 'BAPI*')
ORDER BY FUNCNAME;

ODP Subscription Management

-- Reconcile ERPL-owned subscriptions against their cursors
WITH subs AS (
  SELECT queue_name, subscriber_name, subscriber_proc
  FROM sap_odp_show_subscriptions()
),
cursors AS (
  SELECT subscriber_proc, pointer, is_closed, is_delta_extension, request_date
  FROM sap_odp_show_cursors(subscriber_name => 'ERPL')
)
SELECT
  s.queue_name,
  s.subscriber_proc,
  c.is_delta_extension,
  c.is_closed,
  c.request_date,
  CURRENT_DATE - CAST(c.request_date AS DATE) AS days_since_last_use,
  CASE
    WHEN c.is_closed IS NULL                                       THEN 'NO_CURSOR'
    WHEN c.is_closed                                                THEN 'CLOSED'
    WHEN CURRENT_DATE - CAST(c.request_date AS DATE) > 7            THEN 'STALE'
    WHEN CURRENT_DATE - CAST(c.request_date AS DATE) > 1            THEN 'RECENT'
    ELSE                                                                  'CURRENT'
  END AS usage_status
FROM subs s
LEFT JOIN cursors c USING (subscriber_proc)
ORDER BY days_since_last_use DESC NULLS LAST;