Example to Transform SAP Data into Eventlog in Snowflake

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

This page describes how Snowflake can be used to transform earlier extracted SAP data into the eventlog format used by QPR ProcessAnalyzer models. These scripts run in Snowflake as procedures (i.e., these scripts are not stored into QPR ProcessAnalyzer). There are two scripts: one for creating the case attributes data (one row for each case) and one for creating the events data (one row for each event).

Defining procedures

SQL code in Snowflake is placed into procedure definitions where the code can be run either manually or scheduled using Snowflake tasks (https://docs.snowflake.com/en/user-guide/tasks-intro). Procedure can be created with following snippet: 

CREATE OR REPLACE PROCEDURE CREATE_CASE_ATTRIBUTES()
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS
BEGIN
  <place sql here>
END;

Procedure can be called as follows: 

CALL CREATE_CASE_ATTRIBUTES();

Create case attributes

The example script below creates the case attributes data. First, the script creates a table containing the case attribute data (or if the table pre-exists, recreates it). This way if model is changed, the CaseAttribute table structure does not need to be changed all the time.

Amount in local currency (and Cost) attributes are calculated by summing up table BSAK entries with SHKZG ='S' (Debit entry). Table BSAK contains Cleared Accounting entries for Vendor items. Currency conversions are made with hard coded values. They should be taken regularly using existing dataset available in the Snowflake.

The example script creates following columns (case attributes):

  • CaseId
  • Creation Month
  • Company Code
  • Payment Terms
  • Vendor
  • Payment Method
  • Vendor: Group
  • Vendor: Country
  • Document Type
  • Currency
  • Amount in doc currency
  • Cost
  • Currency Rate
  • PO Number
  • Invoice Type
  • PO Type

In the script, replace the <CasesTableName> with the actual name of the cases table. 

// Set BSAK DATA AS oneliner
CREATE OR REPLACE TEMPORARY VIEW BSAK_TEMP AS
SELECT BUKRS, BELNR, GJAHR, WAERS, MIN(ZLSCH) ZLSCH, MIN(ZTERM) ZTERM, MIN(LIFNR) LIFNR,
  SUM(CASE WHEN SHKZG ='S' THEN WRBTR else 0 end) SSUM
FROM BSAK_PROD
  GROUP BY BUKRS,BELNR,GJAHR,WAERS;

CREATE OR REPLACE TEMPORARY TABLE TEMP_CURRENCIES (CURR_CODE String, CURR_RATE Float);
INSERT INTO TEMP_CURRENCIES (CURR_CODE,CURR_RATE) VALUES
('ZMW', 21.1079),
('UAH', 39.06615),
('TZS', 2475.255),
('NOK', 10.98025),
('KHR', 4288.5),
('KGS', 92.505),
('KES', 134.29),
('JPY', 144.095),
('GEL', 2.75295),
('GBP', 0.8799);

CREATE OR REPLACE TABLE "<CasesTableName>" AS
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
SUBSTRING(BKPF.BUDAT, 1, 4) || '/' || SUBSTRING(BKPF.BUDAT, 5, 2) AS "Creation Month",
BKPF.BUKRS || ' - ' || COALESCE(T001.BUTXT, '*') AS "Company Code",
BSAK.ZTERM || ' - ' || COALESCE(T052U.TEXT1, '*') AS "Payment Terms",
BSAK.LIFNR || ' - ' || COALESCE(LFA1.NAME1, '*') AS "Vendor",
BSAK.ZLSCH AS "Payment Method",
LFA1.KTOKK || ' - ' || COALESCE(T077Y.TXT30, '*') AS "Vendor: Group",
LFA1.LAND1 || ' - ' || COALESCE(T005T.LANDX, '*') AS "Vendor: Country",
BKPF.BLART || ' - ' || T003T.LTEXT AS "Document Type",
BSAK.WAERS AS "Currency",
BSAK.SSUM :: Float AS "Amount in doc currency",
COALESCE(BSAK.SSUM :: Float / CURR.CURR_RATE :: Float, 0) AS "Cost",
CURR.CURR_RATE AS "Currency Rate",
EKPO.EBELN AS "PO Number",
CASE WHEN EKPO.EBELN IS NULL THEN 'No PO' ELSE 'PO' END AS "Invoice Type",
EKKO.BSART || ' - ' || COALESCE(T161T.BATXT, '*') AS "PO Type"

FROM BKPF_PROD AS BKPF
JOIN BSAK_TEMP AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
LEFT OUTER JOIN T052U_PROD AS T052U 
  ON BSAK.ZTERM = T052U.ZTERM AND T052U.SPRAS = 'EN'
LEFT OUTER JOIN LFA1_PROD AS LFA1 
  ON BSAK.LIFNR = LFA1.LIFNR
LEFT OUTER JOIN T005T_PROD AS T005T 
  ON LFA1.LAND1 = T005T.LAND1
LEFT OUTER JOIN T077Y_PROD AS T077Y
  ON LFA1.KTOKK = T077Y.KTOKK
LEFT OUTER JOIN T001_PROD AS T001
  ON BKPF.BUKRS = T001.BUKRS //and T001.SPRAS = 'EN'
LEFT OUTER JOIN T003T_PROD AS T003T 
  ON BKPF.BLART = T003T.BLART
LEFT OUTER JOIN TEMP_CURRENCIES CURR
  ON TRIM(BSAK.WAERS) = TRIM(CURR.CURR_CODE)
LEFT OUTER JOIN RBKP_PROD AS RBKP --link to PO
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
LEFT OUTER JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
LEFT OUTER JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
LEFT OUTER JOIN EKKO_PROD EKKO
  ON EKPO.EBELN = EKKO.EBELN
LEFT OUTER JOIN T161T_PROD T161T
  ON EKKO.BSART = T161T.BSART AND EKKO.BSTYP = T161T.BSTYP
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3');

DROP VIEW BSAK_TEMP;
DROP TABLE TEMP_CURRENCIES;

Create events

Following script creates the events datatable contents (i.e.,eventlog data). If the table doesn't already exist, it's created. If the table pre-exists, its contents is fully replaced.

Events are created with one large select where each event type are constructed as sub-selects which are combined into the final select using the union all. Note that each sub-select need to have exactly the same columns for the select to work.

The example script creates the following columns (event attributes) to the events table:

  • CaseId
  • ProcessStep
  • TimeStamp
  • User
  • Automation (values: Automatic, Robotic, Manual)

In this example, events are limited to CompanyCode and document type level with where clause for BKPF.BUKRS and BKPF.BLART. All the events are getting their CaseId from table BKPF, so all the events are restricted to the wanted companies and document.

In the script, replace the <EventsTableName> with the actual name of the events table. 

CREATE OR REPLACE TABLE "<EventsTableName>" AS
-----------------------------
--AP Document Created
-----------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'AP Document Created' AS ProcessStep,
TO_TIMESTAMP(BKPF.CPUDT || ' ' || BKPF.CPUTM, 'YYYYMMDD HH24MISS') AS TimeStamp,
BKPF.USNAM AS User,
CASE WHEN BKPF.USNAM LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation
FROM BKPF_PROD AS BKPF 
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
LEFT OUTER JOIN T003T_PROD AS T003T 
  ON T003T.BLART = BKPF.BLART
LEFT OUTER JOIN USR02_PROD AS USR02 
  ON USR02.BNAME = BKPF.USNAM
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

-----------------------------
--PO Created
-----------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'PO Created' AS ProcessStep,
CASE WHEN CH2.UDATE IS NOT NULL THEN TO_TIMESTAMP(CH2.UDATE || ' ' || CH2.UTIME, 'YYYYMMDD HH24MISS') ELSE TO_TIMESTAMP(CH1.UDATE || ' ' || CH1.UTIME, 'YYYYMMDD HH24MISS') END AS TimeStamp,
CASE WHEN CH2.UDATE IS NOT NULL THEN CH2.USERNAME ELSE EKKO.ERNAM END AS User,
CASE WHEN CH2.UDATE IS NOT NULL THEN
  CASE WHEN CH2.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN U2.USTYP = 'A' THEN 'Manual' 
  ELSE 'Automatic' END
ELSE 	
  CASE WHEN EKKO.ERNAM LIKE '%ROBOT%' THEN 'Robotic' WHEN U1.USTYP = 'A' THEN 'Manual' 
  ELSE 'Automatic' END
END AS Automation

FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN CDPOS_EINKBELEG_PROD AS CP1
  ON TRIM(CP1.OBJECTID) = EKKO.EBELN AND TRIM(CP1.TABNAME) = 'EKKO' AND TRIM(CP1.FNAME) = 'KEY'
JOIN CDHDR_EINKBELEG_PROD AS CH1
  ON TRIM(CH1.OBJECTID) = TRIM(CP1.OBJECTID) AND TRIM(CH1.CHANGENR) = TRIM(CP1.CHANGENR)
LEFT OUTER JOIN CDPOS_EINKBELEG_PROD AS CP2
  ON EKPO.EBELN || EKPO.EBELP = SUBSTR(CP2.tabkey, 4, 15) AND CP2.TABNAME = 'EKPO' AND CP2.FNAME = 'KEY'
LEFT OUTER JOIN CDHDR_EINKBELEG_PROD AS CH2
  ON CH2.OBJECTID = CP2.OBJECTID AND CH2.CHANGENR = CP2.CHANGENR
LEFT OUTER JOIN T161T_PROD AS T161T 
  ON T161T.BSART = EKKO.BSART AND T161T.BSTYP = EKKO.BSTYP
LEFT OUTER JOIN USR02_PROD AS U1
  ON U1.BNAME = EKKO.ERNAM
LEFT OUTER JOIN USR02_PROD AS U2
  ON U2.BNAME = CH2.USERNAME
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

---------------------------------------------
--PR Created
---------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'PR Created' AS ProcessStep,
TO_TIMESTAMP(EBAN.BADAT, 'YYYYMMDD') AS TimeStamp,
EBAN.ERNAM AS User,
CASE WHEN EBAN.ERNAM LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN EBAN_PROD AS EBAN
  ON EKPO.BANFN = EBAN.BANFN AND EKPO.BNFPO = EBAN.BNFPO 
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = EBAN.ERNAM
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

---------------------------------------------
--PO History
---------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
CASE WHEN EKBE.VGABE = '1' AND EKBE.SHKZG = 'H' THEN 'Goods Receipt Reversal'
  WHEN EKBE.VGABE = '1' AND EKBE.SHKZG = 'S' THEN 'Goods Receipt'		
  WHEN EKBE.VGABE = '2' THEN 'Invoice Receipt'
  WHEN EKBE.VGABE = '3' THEN 'Subseq. Debit/Credit'
  WHEN EKBE.VGABE = '4' THEN 'Down Payment'
  WHEN EKBE.VGABE = '5' THEN 'Payment'
  WHEN EKBE.VGABE = '6' THEN 'Goods Issue for Stock Transfer'
  WHEN EKBE.VGABE = '7' THEN 'Consumption (Subcontracting)'
  WHEN EKBE.VGABE = '8' THEN 'Delivery (Stock Transfer)'
  WHEN EKBE.VGABE = '9' THEN 'Service Entry Sheet'
  WHEN EKBE.VGABE = 'A' THEN 'Down Payment Request'
  WHEN EKBE.VGABE = 'C' THEN 'Down Payment Clearing'
  WHEN EKBE.VGABE = 'Q' THEN 'Comparison for Internally Posted Material'
  WHEN EKBE.VGABE = 'R' THEN 'Return Delivery via Delivery Note'
  WHEN EKBE.VGABE = 'P' THEN 'Invoice Parking'
  WHEN EKBE.VGABE = 'V' THEN 'Down Payment Request Clearing'
  WHEN EKBE.VGABE = 'M' THEN 'Posted Subsequent Debit with Reference to a Contract'
  WHEN EKBE.VGABE = 'K' THEN 'Posted Invoice with Reference to a Contract'
  WHEN EKBE.VGABE = 'L' THEN 'Parked Invoice with Reference to a Contract'
  WHEN EKBE.VGABE = 'N' THEN 'Posted Subsequent Debit for Contract in Blanket PO'
  WHEN EKBE.VGABE = 'O' THEN 'Posted Invoice for Contract in Blanket Purchase Order'
  WHEN EKBE.VGABE = 'G' THEN 'Parked Invoice for Contract in Blanket Purchase Order'
END AS ProcessStep,
TO_TIMESTAMP(EKBE.CPUDT || ' ' || EKBE.CPUTM, 'YYYYMMDD HH24MISS') AS TimeStamp,
EKBE.ERNAM AS User,
CASE WHEN EKBE.ERNAM LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN EKBE_PROD AS EKBE
  ON EKPO.EBELN = EKBE.EBELN AND EKPO.EBELP = EKBE.EBELP 
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = EKBE.ERNAM
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

---------------------------------------------
--Vendor Confirmation
---------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Vendor Confirmation: ' || T163F.EBTXT AS ProcessStep,
TO_TIMESTAMP(EKES.ERDAT || ' ' || EKES.EZEIT, 'YYYYMMDD HH24MISS') AS TimeStamp,
'N/A' AS User,
CASE WHEN EKES.ESTKZ = '1' THEN 'Manual' WHEN EKES.ESTKZ = '3' THEN 'Automatic' ELSE 'N/A' END 
AS Automation // 2=shipping notification 4=return delivery (https://www.sapdatasheet.org/abap/doma/bbest.html)
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN EKES_PROD AS EKES
  ON EKPO.EBELN = EKES.EBELN AND EKPO.EBELP = EKES.EBELP
JOIN T163F_PROD AS T163F
  ON EKES.EBTYP = T163F.EBTYP
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

---------------------------------------------
--Invoice Clearing
---------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Invoice Clearing' AS ProcessStep,
TO_TIMESTAMP(BSAK.AUGDT || ' 235957', 'YYYYMMDD HH24MISS') AS TimeStamp,
COALESCE(BKPF_CLE.USNAM, 'N/A') AS User,
CASE WHEN BKPF_CLE.USNAM LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
LEFT OUTER JOIN BKPF_PROD AS BKPF_CLE
  ON BKPF_CLE.BELNR = BSAK.AUGBL AND BKPF_CLE.GJAHR = BSAK.GJAHR AND BKPF_CLE.BUKRS = BSAK.BUKRS
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = BKPF_CLE.USNAM
WHERE BKPF_CLE.BUDAT > 20190000
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

------------------------------------------------------
--Invoice Due Date
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Invoice Due Date' AS ProcessStep,
CASE WHEN BSAK.ZBD1P = 0 THEN DATEADD(d,TO_NUMBER(BSAK.ZBD1T),TO_TIMESTAMP(BSAK.ZFBDT || ' 235959', 'YYYYMMDD HH24MISS'))
ELSE CASE WHEN BSAK.ZBD2P = 0 THEN DATEADD(d,TO_NUMBER(BSAK.ZBD2T),TO_TIMESTAMP(BSAK.ZFBDT || ' 235959', 'YYYYMMDD         HH24MISS'))
ELSE DATEADD(d,TO_NUMBER(BSAK.ZBD3T),TO_TIMESTAMP(BSAK.ZFBDT || ' 235959', 'YYYYMMDD HH24MISS'))
END END AS "TimeStamp", 
'N/A' AS User,
'N/A' AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
WHERE BSAK.ZFBDT < 20300001
  AND BSAK.ZFBDT > 20200101
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

------------------------------------------------------
--Invoice Document Date
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Invoice Document Date' AS ProcessStep,
DATEADD(second,1,TO_TIMESTAMP(BKPF.BLDAT, 'YYYYMMDD')) AS TimeStamp, 
'N/A' AS User,
'N/A' AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
WHERE BKPF.BLDAT < 20300001
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

------------------------------------------------------
--Invoice Posting Date
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Invoice Posting Date' AS ProcessStep,
DATEADD(second,1,TO_TIMESTAMP(BKPF.BUDAT, 'YYYYMMDD')) AS TimeStamp, 
'N/A' AS User,
'N/A' AS Automation
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
WHERE BKPF.BUDAT < 20300001
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--PO Item Changes
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'PO Changed: ' || COALESCE(DD04T.SCRTEXT_L,REPLACE(CDPOS_EINKBELEG.FNAME, ' ', '')) AS ProcessStep,
TO_TIMESTAMP(CDHDR_EINKBELEG.UDATE || ' ' || CDHDR_EINKBELEG.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_EINKBELEG.USERNAME AS User,
CASE WHEN CDHDR_EINKBELEG.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO
  ON EKKO.EBELN = EKPO.EBELN
JOIN CDPOS_EINKBELEG_PROD AS CDPOS_EINKBELEG 
  ON EKPO.EBELN || EKPO.EBELP = SUBSTR(CDPOS_EINKBELEG.tabkey, 4, 15) AND TRIM(CDPOS_EINKBELEG.TABNAME) = 'EKPO' AND TRIM(CDPOS_EINKBELEG.CHNGIND) = 'U'
JOIN CDHDR_EINKBELEG_PROD AS CDHDR_EINKBELEG
  ON CDHDR_EINKBELEG.OBJECTID = CDPOS_EINKBELEG.OBJECTID AND CDHDR_EINKBELEG.CHANGENR = CDPOS_EINKBELEG.CHANGENR
LEFT OUTER JOIN DD04T_PROD AS DD04T
  ON DD04T.ROLLNAME = CDPOS_EINKBELEG.FNAME
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_EINKBELEG.USERNAME
WHERE TRIM(CDPOS_EINKBELEG.FNAME) NOT IN ('STAPO', 'AEDAT', 'LOEKZ', 'PROCSTAT') 
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--PO Approval
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'PO ' || CASE WHEN CDPOS_EINKBELEG.VALUE_NEW LIKE '%X%' THEN 'Approved' else 'Approval Revoked' end AS ProcessStep,
DATEADD(ms,200,TO_TIMESTAMP(CDHDR_EINKBELEG.UDATE || ' ' || CDHDR_EINKBELEG.UTIME, 'YYYYMMDD HH24MISS')) AS TimeStamp,
CDHDR_EINKBELEG.USERNAME AS User,
CASE WHEN CDHDR_EINKBELEG.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN CDPOS_EINKBELEG_PROD AS CDPOS_EINKBELEG
  ON TRIM(CDPOS_EINKBELEG.OBJECTID) = EKKO.EBELN AND TRIM(CDPOS_EINKBELEG.TABNAME) = 'EKKO' AND TRIM(CDPOS_EINKBELEG.CHNGIND) = 'U' AND TRIM(CDPOS_EINKBELEG.FNAME) = 'FRGZU'
JOIN CDHDR_EINKBELEG_PROD AS CDHDR_EINKBELEG 
  ON CDHDR_EINKBELEG.OBJECTID = CDPOS_EINKBELEG.OBJECTID AND CDHDR_EINKBELEG.CHANGENR = CDPOS_EINKBELEG.CHANGENR
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_EINKBELEG.USERNAME
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--PO Deletion indicator
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
CASE WHEN TRIM(CDPOS_EINKBELEG.VALUE_NEW) = 'L' THEN 'PO Deletion Indicator: Activated' 
  WHEN TRIM(CDPOS_EINKBELEG.VALUE_NEW) = 'S' THEN 'PO Block Indicator: Activated' 
ELSE 'PO Deletion Indicator: Deactivated' end AS ProcessStep,
TO_TIMESTAMP(CDHDR_EINKBELEG.UDATE || ' ' || CDHDR_EINKBELEG.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_EINKBELEG.USERNAME AS User,
CASE WHEN CDHDR_EINKBELEG.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO
  ON EKKO.EBELN = EKPO.EBELN
JOIN CDPOS_EINKBELEG_PROD AS CDPOS_EINKBELEG 
  ON EKPO.EBELN || EKPO.EBELP = SUBSTR(CDPOS_EINKBELEG.tabkey, 4, 15) AND TRIM(CDPOS_EINKBELEG.FNAME) = 'LOEKZ' AND TRIM(CDPOS_EINKBELEG.CHNGIND) = 'U'
JOIN CDHDR_EINKBELEG_PROD AS CDHDR_EINKBELEG 
  ON CDHDR_EINKBELEG.OBJECTID = CDPOS_EINKBELEG.OBJECTID AND CDHDR_EINKBELEG.CHANGENR = CDPOS_EINKBELEG.CHANGENR
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_EINKBELEG.USERNAME
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--PR Approval
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
CASE WHEN TRIM(CDPOS_BANF.VALUE_NEW) != '' THEN 'PR Approved' else 'PR Approval Revoked' END AS ProcessStep,
TO_TIMESTAMP(CDHDR_BANF.UDATE || ' ' || CDHDR_BANF.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_BANF.USERNAME AS User,
CASE WHEN CDHDR_BANF.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN EKPO_PROD AS EKPO
  ON RSEG.EBELN = EKPO.EBELN AND RSEG.EBELP = EKPO.EBELP
JOIN EKKO_PROD AS EKKO 
  ON EKKO.EBELN = EKPO.EBELN
JOIN EBAN_PROD AS EBAN
  ON EKPO.BANFN = EBAN.BANFN AND EKPO.BNFPO = EBAN.BNFPO 
JOIN CDPOS_BANF_PROD AS CDPOS_BANF
  ON EBAN.BANFN || EBAN.BNFPO = SUBSTR(CDPOS_BANF.tabkey, 4, 15) AND TRIM(CDPOS_BANF.FNAME) = 'FRGDT' AND TRIM(CDPOS_BANF.CHNGIND) = 'U'
JOIN CDHDR_BANF_PROD AS CDHDR_BANF
  ON CDHDR_BANF.OBJECTID = CDPOS_BANF.OBJECTID AND CDHDR_BANF.CHANGENR = CDPOS_BANF.CHANGENR
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_BANF.USERNAME
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--Invoice Blocks
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
CASE WHEN CDPOS_INCOMINGINVOICE.TABNAME = 'RBKP_BLOCKED' THEN 'Invoice Released' ELSE 'Invoice Block:' || CASE WHEN CDPOS_INCOMINGINVOICE.VALUE_NEW = '' THEN ' Deactivated' ELSE ' Activated' END END AS ProcessStep,
TO_TIMESTAMP(CDHDR_INCOMINGINVOICE.UDATE || ' ' || CDHDR_INCOMINGINVOICE.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_INCOMINGINVOICE.USERNAME AS User,
CASE WHEN CDHDR_INCOMINGINVOICE.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN CDHDR_INCOMINGINVOICE_PROD AS CDHDR_INCOMINGINVOICE 
  ON RBKP.BELNR || RBKP.GJAHR = CDHDR_INCOMINGINVOICE.OBJECTID
JOIN CDPOS_INCOMINGINVOICE_PROD AS CDPOS_INCOMINGINVOICE
  ON CDHDR_INCOMINGINVOICE.OBJECTID = CDPOS_INCOMINGINVOICE.OBJECTID AND CDHDR_INCOMINGINVOICE.CHANGENR = CDPOS_INCOMINGINVOICE.CHANGENR
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_INCOMINGINVOICE.USERNAME
LEFT OUTER JOIN DD04T_PROD AS DD04T
  ON DD04T.ROLLNAME = CDPOS_INCOMINGINVOICE.FNAME
WHERE CDPOS_INCOMINGINVOICE.TABNAME in ('RSEG', 'RBKP_BLOCKED') AND (CDPOS_INCOMINGINVOICE.FNAME LIKE 'SPGR%' or CDPOS_INCOMINGINVOICE.FNAME = 'KEY')
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL

------------------------------------------------------
--Invoice Changes
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
'Invoice Changed: ' || COALESCE(DD04T.SCRTEXT_L, REPLACE(CDPOS_INCOMINGINVOICE.FNAME, ' ', '')) AS ProcessStep,
TO_TIMESTAMP(CDHDR_INCOMINGINVOICE.UDATE || ' ' || CDHDR_INCOMINGINVOICE.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_INCOMINGINVOICE.USERNAME AS User,
CASE WHEN CDHDR_INCOMINGINVOICE.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN RBKP_PROD AS RBKP
  ON TRIM(BKPF.AWKEY) = RBKP.BELNR || RBKP.GJAHR AND BKPF.BUKRS = RBKP.BUKRS
JOIN RSEG_PROD AS RSEG
  ON RBKP.BELNR = RSEG.BELNR AND RBKP.GJAHR = RSEG.GJAHR AND RBKP.BUKRS = RSEG.BUKRS
JOIN CDHDR_INCOMINGINVOICE_PROD AS CDHDR_INCOMINGINVOICE 
  ON RBKP.BELNR || RBKP.GJAHR = CDHDR_INCOMINGINVOICE.OBJECTID
JOIN CDPOS_INCOMINGINVOICE_PROD AS CDPOS_INCOMINGINVOICE 
  ON CDHDR_INCOMINGINVOICE.OBJECTID = CDPOS_INCOMINGINVOICE.OBJECTID AND CDHDR_INCOMINGINVOICE.CHANGENR = CDPOS_INCOMINGINVOICE.CHANGENR
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_INCOMINGINVOICE.USERNAME
LEFT OUTER JOIN DD04T_PROD AS DD04T
  ON DD04T.ROLLNAME = CDPOS_INCOMINGINVOICE.FNAME
WHERE CDPOS_INCOMINGINVOICE.TABNAME in ('RSEG', 'RBKP') AND CDPOS_INCOMINGINVOICE.FNAME NOT LIKE 'SPGR%'
  AND CDPOS_INCOMINGINVOICE.CHNGIND = 'U'
  AND BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3')

UNION ALL 

------------------------------------------------------
--Payment Blocks
------------------------------------------------------
SELECT DISTINCT BKPF.BELNR || BKPF.BUKRS || BKPF.GJAHR AS CaseId,
CASE WHEN TRIM(CDPOS_BELEG.VALUE_NEW) = '' or TRIM(CDPOS_BELEG.VALUE_NEW) IS NULL THEN 'Payment Block Deactivated' ELSE 'Payment Block Activated: ' || TRIM(T008T.TEXTL) END AS ProcessStep,
TO_TIMESTAMP(CDHDR_BELEG.UDATE || ' ' || CDHDR_BELEG.UTIME, 'YYYYMMDD HH24MISS') AS TimeStamp,
CDHDR_BELEG.USERNAME AS User,
CASE WHEN CDHDR_BELEG.USERNAME LIKE '%ROBOT%' THEN 'Robotic' WHEN USR02.USTYP = 'A' THEN 'Manual' ELSE 'Automatic' END AS Automation 
FROM BKPF_PROD AS BKPF
JOIN BSAK_PROD AS BSAK
  ON BKPF.BELNR = BSAK.BELNR AND BKPF.GJAHR = BSAK.GJAHR AND BKPF.BUKRS = BSAK.BUKRS
JOIN CDHDR_BELEG_PROD AS CDHDR_BELEG 
  ON BKPF.BUKRS || BKPF.BELNR || BKPF.GJAHR = SUBSTR(CDHDR_BELEG.OBJECTID, 4, 18)
JOIN CDPOS_BELEG_ALL_PROD AS CDPOS_BELEG 
  ON CDHDR_BELEG.OBJECTID = CDPOS_BELEG.OBJECTID AND CDHDR_BELEG.CHANGENR = CDPOS_BELEG.CHANGENR AND TRIM(CDPOS_BELEG.FNAME) = 'ZLSPR' AND TRIM(CDPOS_BELEG.CHNGIND) = 'U'
LEFT OUTER JOIN T008T_PROD AS T008T
  ON TRIM(CDPOS_BELEG.VALUE_NEW) = T008T.ZAHLS 
LEFT OUTER JOIN USR02_PROD AS USR02
  ON USR02.BNAME = CDHDR_BELEG.USERNAME
WHERE BKPF.BUKRS IN ('CC01', 'CC02', 'CC03')
  AND BKPF.BLART IN ('A1', 'A2', 'A3');
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Example_to_Transform_SAP_Data_into_Eventlog_in_Snowflake&oldid=23232"