Skip to main content

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');

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;