Example to Transform SAP Data into Eventlog in Snowflake
Jump to navigation
Jump to search
This page describes how Snowflake can transform earlier extracted SAP data into eventlog format used by QPR ProcessAnalyzer models. These script run in Snowflake as procedures (i.e., these scripts are not put 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).
Creating procedures
SQL code in Snowflake is placed into procedures where the can be easily run either manually or scheduled using Snowflake tasks. Procedure can be created with following snippet:
CREATE OR REPLACE PROCEDURE CREATE_CASE_ATTRIBUTES()
RETURNS VARCHAR(16777216)
LANGUAGE SQL
LANGUAGE SQL
AS
BEGIN
<place code here>
END;
Procedure can be called as follows:
CALL CREATE_CASE_ATTRIBUTES();
Create case attributes
Following script creates the case attributes data. The script creates the table containing the case attribute data (or if the table pre-exists, recreates it). This way if model is changes the CaseAttribute table structure does not 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.
In the following 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 NOT NULL THEN 'PO'
WHEN BKPF.BLART = 'K3' THEN 'IFS/K3'
ELSE 'No 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 then combined into the final select using 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 to the events table:
- CaseId
- ProcessStep
- TimeStamp
- User
- Automation
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 following 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');