Skip to main content

Celonis Product Documentation

Integration of third-party pre-processing systems (e.g. ReadSoft, VIM Open Text)

If the invoices are not parked in SAP but in a third-party system, the recommended approach is similar to the SAP FI - only blocked invoices to use the op. Data Jobs and DM as a foundation and then add system specific additions into the Data Job and the DM.

Typically, the following data jobs have to be updated:

  1. Create Table: TMP_FI_MM_UNION - Instead of adding data from RBKB (incoming invoice header), the second union statement should be adjusted to include invoice header data of the third party system considering only the ones that are not yet posted. Eventually, columns in the SELECT column need to be adjusted or commented out.

    Create_Table_TMP_FI_MM_UNION.png

  2. OT Create Table: TMP_AP_RSEG_RBDRSEG - Instead of RBDRSEG, the invoice item table of the preprocessing system needs to be unified with RSEG considering only non-posted invoices.

    OT_Create_Table.png

  3. Create Table: _CEL_AP_CASES - Adds the third-party system to the case table instead of the parked SAP invoices (second union).

    Create_Table_CEL_AP_CASES.png

  4. Create Table: AP_RSEG_OPEN - In order to have the link to the purchase order in the DM, the RSEG table needs to be adjusted to also include the data from the invoices being not yet in SAP but only in the preprocessing system.

    Create_Table_AP_RSEG_OPEN.png

  5. Updating those three statements should be sufficient to include invoices parked in a third-party system in the app, it’s possible to further extend the data model and add additional activities such as workflows to it.

Documentation for this section will be added in the future. If you need help in the meantime, go to the Celonis Support Center at Celopeers. You can search for answers, ask a new question, or create a support ticket.

Code Snippets for OpenText (VIM)

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

DROP TABLE IF EXISTS "TMP_FI_MM_UNION";
CREATE TABLE "TMP_FI_MM_UNION" AS (
SELECT
    BSEG."MANDT"
   ,BSEG."BUKRS"
   ,BSEG."BELNR"
   ,BSEG."GJAHR"
   ,BSEG."BUZEI"
   ,CAST(BKPF."BLDAT" AS DATE) AS "BLDAT"
   ,CAST(BKPF."CPUDT" AS DATE) AS "CPUDT"
   ,CAST(BKPF."CPUTM" AS TIME) AS "CPUTM"
   ,BKPF."USNAM"
   ,CAST(BSEG."ZFBDT" AS DATE) AS "ZFBDT"
   ,BSEG."ZBD1T"
   ,BSEG."ZBD1P"
   ,BSEG."ZBD2T"
   ,BSEG."ZBD3T"
   ,BSEG."MANDT" || BSEG."BUKRS" || BSEG."BELNR" || BSEG."GJAHR" || BSEG."BUZEI" AS "_CASE_KEY"
   ,'FI' AS "CASE_TYPE"
   ,RBKP."MANDT" || RBKP."BELNR" || RBKP."GJAHR" AS "MM_KEY"
   ,BKPF."TCODE"
   ,BKPF."STJAH"
   ,BKPF."STBLG"
   ,BKPF."XREVERSAL"
   ,CAST(RBKP."CPUDT" AS DATE) AS "CPUDT_MM"
   ,CAST(RBKP."CPUTM" AS TIME) AS "CPUTM_MM"
   ,RBKP."ERFNAM"
   ,RBKP."USNAM" AS "USNAM_MM"
   ,BSEG."LIFNR"
   ,BKPF."AWKEY"
   ,NULL AS "VIM_STATUS"FROM
   "BKPF" AS BKPF
   INNER JOIN "BSIK" AS BSEG
       AND BKPF."MANDT" = BSEG."MANDT"
       AND BKPF."BUKRS" = BSEG."BUKRS"
       AND BKPF."BELNR" = BSEG."BELNR"
       AND BKPF."GJAHR" = BSEG."GJAHR"
   LEFT JOIN RBKP AS RBKP
       AND BKPF.MANDT = RBKP.MANDT
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || RBKP.GJAHR

   BSEG.BSCHL IN <%=postingKeysCaseTable%>
  
UNION ALL
SELECT
    H."MANDT"
   ,H."BUKRS"
   ,H."DOCID" AS BELNR
   ,H."GJAHR"
   ,NULL AS "BUZEI"
   ,CAST(H."BLDAT" AS DATE) AS "BLDAT"
   ,CAST(NULL AS DATE) AS "CPUDT"
   ,CAST(NULL AS TIME) AS "CPUTM"
   ,NULL AS "USNAM"
   ,H."ZFBDT"
   ,H."ZBD1T"
   ,H."ZBD1P"
   ,H."ZBD2T"
   ,H."ZBD3T"
   ,H."MANDT" || H."DOCID" AS "_CASE_KEY"
   ,'PARKED' AS "CASE_TYPE"
   ,H."MANDT" || H."DOCID" AS "MM_KEY"
   ,NULL AS TCODE
   ,NULL AS "STJAH"
   ,NULL AS "STBLG"
   ,NULL AS "XREVERSAL"
   ,CAST(NULL AS DATE) AS "CPUDT_MM"
   ,CAST(NULL AS TIME) AS "CPUTM_MM"
   ,NULL AS "ERFNAM"
   ,NULL AS "USNAM_MM"
   ,H.LIFNR
   ,NULL AS "AWKEY"
   -- ,H."BUS_OBJKEY" AS "BUS_OBJKEY"
   -- ,H."BUS_OBJTYPE" AS "BUS_OBJTYPE"
   ,H.STATUS AS "VIM_STATUS" -- Added to bring through vim status
FROM
   "/OPT/VIM_1HEAD" AS H
(H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL)
);
DROP TABLE IF EXISTS "TMP_RSEG_RBDRSEG";
CREATE TABLE "TMP_RSEG_RBDRSEG" AS
SELECT
   "MANDT"
   ,"BELNR"
   ,"GJAHR"
   ,"BUZEI"
   --,"BPMNG"
   -- ,NULL AS "BPREM"
   -- ,"BPRME"
   -- ,NULL AS "BPUMN"
   -- ,NULL AS "BPUMZ"
   -- ,NULL AS "BPWEM"
   -- ,NULL AS "BSMNG"
   ,NULL AS "BUDAT"
   -- ,"BUKRS"
   -- ,"BWTAR"
   -- ,NULL AS "DMBTR"
   ,"EBELN"
   ,"EBELP"
   -- ,NULL AS "EINDT"
   -- ,"EREKZ"
   -- ,"FRBNR"
   -- ,NULL AS "HSWAE"
   ,"LFBNR"
   ,"LFGJA"
   ,"LFPOS"
   -- ,"KNTTP"
   -- ,"KSCHL"
   -- ,"MATNR"
   ,"MEINS"
   ,"MENGE"
   -- ,NULL AS "MEPRF"
   -- ,"MWSKZ"
   -- ,NULL AS "NETPR"
   -- ,NULL AS "NETWR"
   -- ,"PSTYP"
   ,NULL AS "REFWR"
   ,NULL AS "REMNG"
   -- ,NULL AS "RETPO"
   -- ,NULL AS "SCHPR"
   -- ,NULL AS "SELKZ"
   ,"SHKZG"
    --,"SPGRC"
    --,"SPGRG"
   ,"SPGRM"
   ,"SPGRP"
   -- ,"SPGRQ"
   -- ,"SPGRS"
   -- ,"SPGRT"
   -- ,"SPGRV"
   -- ,"SPGREXT"
   -- ,"STUNR"
   ,"TBTKZ" --ADDED FOR PARKED AND BLOCKED INVOICES UPDATE
   -- ,"TXJCD"
   -- ,NULL AS "TXZ01"
   -- ,NULL AS "WAERS"
   -- ,NULL AS "WEBRE"
   ,NULL AS "WEMNG"
   -- ,NULL AS "WEPOS"
   -- ,"WERKS"
   ,"WRBTR"
   ,NULL AS "WMWST"
   -- ,"XBLNR"
   -- ,"XEKBE"
   -- ,"XEKBZ"
   -- ,"ZAEHK"
   -- ,"ZEKKN"
   -- ,NULL AS "LSMNG"
   -- ,NULL AS "LSMEH"
   -- ,NULL AS "BASME"
   -- ,NULL AS "XUBNKR"
   -- ,"GRICD"
   -- ,"GRIRG"
   -- ,"GITYP"
   ,NULL AS "MBLNR"
   ,NULL AS "MJAHR"
   ,NULL AS "MBLPO"
   -- ,NULL AS "KNUMVK"
   -- ,NULL AS "KNUMV"
   -- ,NULL AS "KNUMVR"
   -- ,NULL AS "SATNR"
   -- ,NULL AS "EAN11"
   -- ,NULL AS "IDNLF"
   -- ,NULL AS "AKTNR"
   -- ,NULL AS "UPVOR"
   -- ,NULL AS "UPTYP"
   -- ,NULL AS "UEBPO"
   -- ,NULL AS "SRVPOS"
   -- ,"PACKNO"
   -- ,"INTROW"
   -- ,NULL AS "LEBRE"
   -- ,NULL AS "BEKKN"
   -- ,NULL AS "PLN_PACKNO"
   -- ,NULL AS "PLN_INTROW"
   -- ,NULL AS "LIEFFN"
   -- ,"RBWWR"
   -- ,"RBMNG"
   ,"BPRBM"
   -- ,"KZMEK"
   -- ,NULL AS "MENGEALT"
   -- ,NULL AS "BPMNGALT"
   -- ,NULL AS "WRBTRALT"
   -- ,NULL AS "XUPDA"
   -- ,NULL AS "EFKOR"
   -- ,NULL AS "LFKOR"
   -- ,NULL AS "OK"
   -- ,"COMPLAINT_REASON"
   -- ,NULL AS "WEUNB"
   -- ,"SGTXT"
   -- ,NULL AS "REWWR"
   -- ,"WEREC"
   -- ,NULL AS "KZBWS"
   -- ,"XSKRL"
   -- ,NULL AS "VRTKZ"
   -- ,NULL AS "RETTP"
   -- ,"RETAMT_FC"
   -- ,"RETPC"
   -- ,"RETDUEDT"
   -- ,"XRETTAXNET"
   -- ,"XDINV"
   -- ,"INV_ITM_ORIGIN"
   -- ,"INVREL"
   -- ,"DIFF_AMOUNT"
   -- ,"CHARG"
   -- ,"XCPRF"
   -- ,"XHISTMA"
   ,MANDT || BELNR || GJAHR AS "MM_KEY"
   ,_CELONIS_CHANGE_DATE
FROM
   "RSEG" AS RSEG
UNION ALL
SELECT
    VIM_ITEM."MANDT"
   ,VIM_ITEM."DOCID" AS BELNR
   ,"GJAHR" AS "GJAHR"
   ,"ITEMID" AS "BUZEI"
   -- ,"BPMNG"
   -- ,"BPREM"
   -- ,"BPRME"
   -- ,"BPUMN"
   -- ,"BPUMZ"
   -- ,"BPWEM"
   -- ,"BSMNG"
   ,"BUDAT"
   -- ,"BUKRS"
   -- ,"BWTAR"
   -- ,"DMBTR"
   ,COALESCE(VIM_ITEM.EBELN,H."EBELN")
   ,"EBELP"
   -- ,"EINDT"
   -- ,"EREKZ"
   -- ,"FRBNR"
   -- ,"HSWAE"
   ,NULL AS "LFBNR"
   ,NULL AS "LFGJA"
   ,NULL AS "LFPOS"
   -- ,"KNTTP"
   -- ,"KSCHL"
   -- ,"MATNR"
   ,BSTME AS "MEINS"
   ,"MENGE"
   -- ,"MEPRF"
   -- ,"MWSKZ"
   -- ,"NETPR"
   -- ,"NETWR"
   -- ,"PSTYP"
   ,NULL AS "REFWR"
   ,NULL AS "REMNG"
   -- ,"RETPO"
   -- ,"SCHPR"
   -- ,"SELKZ"
   ,"SHKZG"
   -- ,"SPGRC"
   -- ,"SPGRG"
   ,NULL AS "SPGRM"
   ,NULL AS "SPGRP"
   -- ,"SPGRQ"
   -- ,"SPGRS"
   -- ,"SPGRT"
   -- ,"SPGRV"
   -- ,"STUNR"
   , NULL AS "TBTKZ" -- ADDED FOR PARKED AND BLOCKED UPDATED
   -- ,"TXJCD"
   -- ,"TXZ01"
   -- ,"WAERS"
   -- ,"WEBRE"
   ,"WEMNG"
   -- ,"WEPOS"
   -- ,"WERKS"
   ,NETPR AS "WRBTR" --WRBTR_PROP
   ,"WMWST"
   -- ,"XBLNR"
   -- ,"XEKBE"
   -- ,"XEKBZ"
   -- ,"ZAEHK"
   -- ,"ZEKKN"
   -- ,"LSMNG"
   -- ,"LSMEH"
   -- ,"BASME"
   -- ,"XUBNKR"
   -- ,"GRICD"
   -- ,"GRIRG"
   -- ,"GITYP"
   ,NULL AS "MBLNR"
   ,NULL AS "MJAHR"
   ,NULL AS "MBLPO"
   -- ,"KNUMVK"
   -- ,"KNUMV"
   -- ,"KNUMVR"
   -- ,"SATNR"
   -- ,"EAN11"
   -- ,"IDNLF"
   -- ,"AKTNR"
   -- ,"UPVOR"
   -- ,"UPTYP"
   -- ,"UEBPO"
   -- ,"SRVPOS"
   -- ,"PACKNO"
   -- ,"INTROW"
   -- ,"LEBRE"
   -- ,"BEKKN"
   -- ,"PLN_PACKNO"
   -- ,"PLN_INTROW"
   -- ,"LIEFFN"
   -- ,"RBWWR"
   -- ,"RBMNG"
   ,NULL AS "BPRBM"
   -- ,"KZMEK"
   -- ,"MENGEALT"
   -- ,"BPMNGALT"
   -- ,"WRBTRALT"
   -- ,"XUPDA"
   -- ,"EFKOR"
   -- ,"LFKOR"
   -- ,"OK"
   -- ,"COMPLAINT_REASON"
   -- ,"WEUNB"
   -- ,"SGTXT"
   -- ,"REWWR"
   -- ,"WEREC"
   -- ,"KZBWS"
   -- ,"XSKRL"
   -- ,"VRTKZ"
   -- ,"RETTP"
   -- ,"RETAMT_FC"
   -- ,"RETPC"
   -- ,"RETDUEDT"
   -- ,"XRETTAXNET"
   -- ,"XDINV"
   -- ,"INV_ITM_ORIGIN"
   -- ,"INVREL"
   -- ,"DIFF_AMOUNT"
   -- ,"CHARG"
   -- ,"XCPRF"
   -- ,"XHISTMA"
   ,H.MANDT || H.DOCID  AS "MM_KEY"
   ,VIM_ITEM._CELONIS_CHANGE_DATE
FROM
"/OPT/VIM_1ITEM" AS VIM_ITEM
   INNER JOIN"/OPT/VIM_1HEAD" AS H
       AND VIM_ITEM."MANDT" = H."MANDT"
       AND VIM_ITEM."DOCID" = H."DOCID"
   AND H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL
;
DROP TABLE IF EXISTS "_CEL_AP_CASES";
CREATE TABLE "_CEL_AP_CASES" AS
SELECT
   BSEG."MANDT"
   ,BSEG."BUKRS"
   ,BSEG."LIFNR"
   -- ,BSEG."UMSKS"
   -- ,BSEG."UMSKZ"
   -- ,BSEG."AUGDT"
   -- ,BSEG."AUGBL"
   -- ,BSEG."ZUONR"
   ,BSEG."GJAHR"
   ,BSEG."BELNR"
   ,BSEG."BUZEI"
   -- ,BSEG."BUDAT"
   -- ,BSEG."BLDAT"
   -- ,BSEG."CPUDT"
   -- ,BSEG."WAERS"
   -- ,BSEG."XBLNR"
   -- ,BSEG."BLART"
   -- ,BSEG."MONAT"
   ,BSEG."BSCHL"
   -- ,BSEG."ZUMSK"
   ,BSEG."SHKZG"
   -- ,BSEG."GSBER"
   ,BSEG."MWSKZ"
   -- ,BSEG."DMBTR"
   ,BSEG."WRBTR"
   -- ,BSEG."MWSTS"
   ,BSEG."WMWST"
   -- ,BSEG."BDIFF"
   -- ,BSEG."BDIF2"
   ,BSEG."SGTXT"
   -- ,BSEG."PROJN"
   -- ,BSEG."AUFNR"
   -- ,BSEG."ANLN1"
   -- ,BSEG."ANLN2"
   -- ,BSEG."EBELN"
   -- ,BSEG."EBELP"
   -- ,BSEG."SAKNR"
   -- ,BSEG."HKONT"
   -- ,BSEG."FKONT"
   -- ,BSEG."FILKD"
   ,BSEG."ZFBDT"
   ,BSEG."ZTERM"
   ,BSEG."ZBD1T"
   ,BSEG."ZBD2T"
   ,BSEG."ZBD3T"
   ,BSEG."ZBD1P"
   ,BSEG."ZBD2P"
   -- ,BSEG."SKFBT"
   -- ,BSEG."SKNTO"
   ,BSEG."WSKTO"
   ,BSEG."ZLSCH"
   ,BSEG."ZLSPR"
   -- ,BSEG."ZBFIX"
   -- ,BSEG."HBKID"
   -- ,BSEG."BVTYP"
   -- ,BSEG."REBZG"
   -- ,BSEG."REBZJ"
   -- ,BSEG."REBZZ"
   -- ,BSEG."SAMNR"
   -- ,BSEG."ZOLLT"
   -- ,BSEG."ZOLLD"
   -- ,BSEG."LZBKZ"
   -- ,BSEG."LANDL"
   -- ,BSEG."DIEKZ"
   -- ,BSEG."MANSP"
   -- ,BSEG."MSCHL"
   -- ,BSEG."MADAT"
   -- ,BSEG."MANST"
   -- ,BSEG."MABER"
   -- ,BSEG."XNETB"
   -- ,BSEG."XANET"
   -- ,BSEG."XCPDD"
   -- ,BSEG."XESRD"
   -- ,BSEG."XZAHL"
   -- ,BSEG."MWSK1"
   -- ,BSEG."DMBT1"
   -- ,BSEG."WRBT1"
   -- ,BSEG."MWSK2"
   -- ,BSEG."DMBT2"
   -- ,BSEG."WRBT2"
   -- ,BSEG."MWSK3"
   -- ,BSEG."DMBT3"
   -- ,BSEG."WRBT3"
   -- ,BSEG."QSSKZ"
   -- ,BSEG."QSSHB"
   -- ,BSEG."QBSHB"
   -- ,BSEG."BSTAT"
   -- ,BSEG."ANFBN"
   -- ,BSEG."ANFBJ"
   -- ,BSEG."ANFBU"
   -- ,BSEG."VBUND"
   -- ,BSEG."REBZT"
   ,BSEG."STCEG"
   -- ,BSEG."EGBLD"
   -- ,BSEG."EGLLD"
   -- ,BSEG."QSZNR"
   -- ,BSEG."QSFBT"
   -- ,BSEG."XINVE"
   -- ,BSEG."PROJK"
   -- ,BSEG."FIPOS"
   -- ,BSEG."NPLNR"
   -- ,BSEG."AUFPL"
   -- ,BSEG."APLZL"
   -- ,BSEG."XEGDR"
   -- ,BSEG."DMBE2"
   -- ,BSEG."DMBE3"
   -- ,BSEG."DMB21"
   -- ,BSEG."DMB22"
   -- ,BSEG."DMB23"
   -- ,BSEG."DMB31"
   -- ,BSEG."DMB32"
   -- ,BSEG."DMB33"
   -- ,BSEG."MWST2"
   -- ,BSEG."MWST3"
   -- ,BSEG."SKNT2"
   -- ,BSEG."SKNT3"
   -- ,BSEG."BDIF3"
   -- ,BSEG."XRAGL"
   -- ,BSEG."RSTGR"
   -- ,BSEG."UZAWE"
   -- ,BSEG."KOSTL"
   -- ,BSEG."LNRAN"
   -- ,BSEG."XSTOV"
   -- ,BSEG."KZBTR"
   -- ,BSEG."XREF1"
   -- ,BSEG."XREF2"
   -- ,BSEG."XARCH"
   -- ,BSEG."PSWSL"
   -- ,BSEG."PSWBT"
   -- ,BSEG."IMKEY"
   -- ,BSEG."ZEKKN"
   -- ,BSEG."FISTL"
   -- ,BSEG."GEBER"
   -- ,BSEG."DABRZ"
   -- ,BSEG."XNEGP"
   -- ,BSEG."EMPFB"
   -- ,BSEG."PRCTR"
   -- ,BSEG."XREF3"
   -- ,BSEG."DTWS1"
   -- ,BSEG."DTWS2"
   -- ,BSEG."DTWS3"
   -- ,BSEG."DTWS4"
   -- ,BSEG."XPYPR"
   -- ,BSEG."KIDNO"
   -- ,BSEG."PYCUR"
   -- ,BSEG."PYAMT"
   -- ,BSEG."BUPLA"
   -- ,BSEG."SECCO"
   -- ,BSEG."PPDIFF"
   -- ,BSEG."PPDIF2"
   -- ,BSEG."PPDIF3"
   -- ,BSEG."PENLC1"
   -- ,BSEG."PENLC2"
   -- ,BSEG."PENLC3"
   -- ,BSEG."PENFC"
   -- ,BSEG."PENDAYS"
   -- ,BSEG."PENRC"
   -- ,BSEG."VERTT"
   -- ,BSEG."VERTN"
   -- ,BSEG."VBEWA"
   -- ,BSEG."KBLNR"
   -- ,BSEG."KBLPOS"
   -- ,BSEG."GRANT_NBR"
   -- ,BSEG."GMVKZ"
   -- ,BSEG."SRTYPE"
   -- ,BSEG."LOTKZ"
   -- ,BSEG."ZINKZ"
   -- ,BSEG."FKBER"
   -- ,BSEG."INTRENO"
   -- ,BSEG."PPRCT"
   -- ,BSEG."BUZID"
   -- ,BSEG."AUGGJ"
   -- ,BSEG."HKTID"
   -- ,BSEG."BUDGET_PD"
   -- ,BSEG."KONTT"
   -- ,BSEG."KONTL"
   -- ,BSEG."UEBGDAT"
   -- ,BSEG."VNAME"
   -- ,BSEG."EGRUP"
   -- ,BSEG."BTYPE"
   -- ,BSEG."PROPMANO"
   ,BKPF."BLART"
   ,BKPF."BLDAT"
   ,BKPF."BUDAT"
   ,BKPF."CPUDT"
   ,BKPF."XBLNR"
   ,BKPF."WAERS"
   ,BKPF."REINDAT"
   -- ,NULL AS "USNAM"
   -- ,NULL AS "TCODE"
   -- ,NULL AS "CPUTM"
   -- ,NULL AS "VGART"
   -- ,NULL AS "KURSF"
   ,CAST(NULL AS FLOAT) AS "RMWWR"
   -- ,NULL AS "BEZNK"
   ,CAST(NULL AS FLOAT) AS "WMWST1"
   ,NULL AS "MWSKZ1"
   -- ,NULL AS "WMWST2"
   -- ,NULL AS "MWSKZ2"
   ,NULL AS "XRECH"
   -- ,NULL AS "BKTXT"
   -- ,NULL AS "SAPRL"
   -- ,NULL AS "LOGSYS"
   -- ,NULL AS "XMWST"
   -- ,NULL AS "STBLG"
   -- ,NULL AS "STJAH"
   -- ,NULL AS "MWSKZ_BNK"
   -- ,NULL AS "TXJCD_BNK"
   -- ,NULL AS "IVTYP"
   -- ,NULL AS "XRBTX"
   -- ,NULL AS "REPART"
   ,NULL AS "RBSTAT"
   -- ,NULL AS "KNUMVE"
   -- ,NULL AS "KNUMVL"
   -- ,NULL AS "ARKUEN"
   -- ,NULL AS "ARKUEMW"
   -- ,NULL AS "MAKZN"
   -- ,NULL AS "MAKZMW"
   -- ,NULL AS "LIEFFN"
   -- ,NULL AS "LIEFFMW"
   -- ,NULL AS "XAUTAKZ"
   -- ,NULL AS "ESRNR"
   -- ,NULL AS "ESRPZ"
   -- ,NULL AS "ESRRE"
   -- ,NULL AS "TXKRS"
   -- ,NULL AS "EGMLD"
   -- ,NULL AS "VATDATE"
   -- ,NULL AS "J_1BNFTYPE"
   -- ,NULL AS "BRNCH"
   -- ,NULL AS "ERFPR"
   -- ,NULL AS "NAME1"
   -- ,NULL AS "NAME2"
   -- ,NULL AS "NAME3"
   -- ,NULL AS "NAME4"
   -- ,NULL AS "PSTLZ"
   -- ,NULL AS "ORT01"
   -- ,NULL AS "LAND1"
   -- ,NULL AS "STRAS"
   -- ,NULL AS "PFACH"
   -- ,NULL AS "PSTL2"
   -- ,NULL AS "PSKTO"
   -- ,NULL AS "BANKN"
   -- ,NULL AS "BANKL"
   -- ,NULL AS "BANKS"
   -- ,NULL AS "STCD1"
   -- ,NULL AS "STCD2"
   -- ,NULL AS "STKZU"
   -- ,NULL AS "STKZA"
   -- ,NULL AS "REGIO"
   -- ,NULL AS "BKONT"
   -- ,NULL AS "DTAWS"
   -- ,NULL AS "DTAMS"
   -- ,NULL AS "SPRAS"
   -- ,NULL AS "XCPDK"
   -- ,NULL AS "EMPFG"
   -- ,NULL AS "FITYP"
   -- ,NULL AS "STCDT"
   -- ,NULL AS "STKZN"
   -- ,NULL AS "STCD3"
   -- ,NULL AS "STCD4"
   -- ,NULL AS "BKREF"
   -- ,NULL AS "J_1KFREPRE"
   -- ,NULL AS "J_1KFTBUS"
   -- ,NULL AS "J_1KFTIND"
   -- ,NULL AS "ANRED"
   -- ,NULL AS "ERNAME"
   -- ,NULL AS "FDLEV"
   -- ,NULL AS "FDTAG"
   -- ,NULL AS "FRGKZ"
   -- ,NULL AS "ERFNAM"
   -- ,NULL AS "INV_TRAN"
   -- ,NULL AS "PREPAY_STATUS"
   -- ,NULL AS "PREPAY_AWKEY"
   -- ,NULL AS "ASSIGN_STATUS"
   -- ,NULL AS "ASSIGN_NEXT_DATE"
   -- ,NULL AS "ASSIGN_END_DATE"
   -- ,NULL AS "COPY_BY_BELNR"
   -- ,NULL AS "COPY_BY_YEAR"
   -- ,NULL AS "COPY_TO_BELNR"
   -- ,NULL AS "COPY_TO_YEAR"
   -- ,NULL AS "COPY_USER"
   -- ,NULL AS "KURSX"
   -- ,NULL AS "WWERT"
   -- ,NULL AS "ANZRPV"
   -- ,NULL AS "REPDAT"
   -- ,NULL AS "ANZRPO"
   -- ,NULL AS "DIFFN"
   -- ,NULL AS "RPZIELN"
   -- ,NULL AS "XKORREKT"
   -- ,NULL AS "XZUORDRT"
   -- ,NULL AS "BNKSEL"
   -- ,NULL AS "XRECHL"
   -- ,NULL AS "XRECHR"
   ,BSEG."MANDT" || BSEG."BUKRS" || BSEG."BELNR" || BSEG."GJAHR" || BSEG."BUZEI" AS "_CASE_KEY"
   ,RBKP."MANDT" || RBKP."BELNR" || RBKP."GJAHR" AS "MM_KEY"
   ,CASE WHEN BKPF.AWTYP = 'RMRP' THEN 'MM_POSTED' ELSE 'FI_POSTED' END AS "CASE_TYPE"
   ,COALESCE(T001."BUTXT",'') AS "BUKRS_TEXT"
   ,T008T.TEXTL AS "ZLSPR_TEXT"
   ,COALESCE(T003T."LTEXT",'') AS "BLART_TEXT"
   ,T052PT.ZTERM AS "ZTERM_MD"
   ,T052PT.ZTAGG AS "ZTAGG_MD"
   ,T052PT.ZDART AS "ZDART_MD"
   ,T052PT.ZFAEL AS "ZFAEL_MD"
   ,T052PT.ZMONA AS "ZMONA_MD"
   ,T052PT.ZTAG1 AS "ZTAG1_MD"
   ,T052PT.ZPRZ1 AS "ZPRZ1_MD"
   ,T052PT.ZTAG2 AS "ZTAG2_MD"
   ,T052PT.ZPRZ2 AS "ZPRZ2_MD"
   ,T052PT.ZTAG3 AS "ZTAG3_MD"
   ,NULL AS "CURR_ROLE"
   ,NULL AS "VIM_STATUS" -- Added to bring through vim status
   ,NULL AS CHANGE_USER -- Added to bring though user
   ,NULL AS ASSIGNMENT -- Added to bring through credit memo
FROM "BSIK" AS BSEG
   INNER JOIN "BKPF" AS BKPF
       AND BKPF."MANDT" = BSEG."MANDT"
       AND BKPF."BUKRS" = BSEG."BUKRS"
       AND BKPF."BELNR" = BSEG."BELNR"
       AND BKPF."GJAHR" = BSEG."GJAHR"
   LEFT JOIN RBKP AS RBKP
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || CAST(RBKP.GJAHR AS VARCHAR(4))
   LEFT JOIN MD_PAYMENT_TERMS AS T052PT
       AND BSEG.MANDT = T052PT.MANDT
       AND BSEG.BUKRS = T052PT.BUKRS
       AND BSEG.BELNR = T052PT.BELNR
       AND BSEG.GJAHR = T052PT.GJAHR
       AND BSEG.BUZEI = T052PT.BUZEI
   LEFT JOIN "T003T" AS T003T
       AND T003T."SPRAS" = '<%=primaryLanguageKey%>'
       AND BKPF."MANDT" = T003T."MANDT"
       AND BKPF."BLART" = T003T."BLART"
   LEFT JOIN "T008T" AS T008T
       AND T008T.SPRAS='<%=primaryLanguageKey%>'
       AND BSEG."MANDT" = T008T."MANDT"
       AND BSEG."ZLSPR" = T008T."ZAHLS"
   LEFT JOIN "T001" AS T001
       AND BKPF."MANDT" = T001."MANDT"
       AND BKPF."BUKRS" = T001."BUKRS"
BSEG.BSCHL IN <%=postingKeysCaseTable%>
UNION ALL
SELECT
    H."MANDT"
   ,H."BUKRS"
   ,H."LIFNR"
   -- ,NULL AS "UMSKS"
   -- ,NULL AS "UMSKZ"
   -- ,NULL AS "AUGDT"
   -- ,NULL AS "AUGBL"
   -- ,H."ZUONR"
   ,H."GJAHR"
   ,H."DOCID" AS BELNR
   ,NULL AS "BUZEI"
   -- ,H."BUDAT"
   -- ,H."BLDAT"
   -- ,H."CPUDT"
   -- ,H."WAERS"
   -- ,H."XBLNR"
   -- ,H."BLART"
   -- ,NULL AS "MONAT"
   ,NULL AS "BSCHL"
   -- ,NULL AS "ZUMSK"
   ,CASE WHEN H."CREDIT_MEMO" IS NULL THEN 'H' ELSE 'S' END AS "SHKZG"
    -- ,H."GSBER"
   ,NULL AS "MWSKZ"
   -- ,NULL AS "DMBTR"
   ,CAST(NET_AMOUNT AS FLOAT) AS "WRBTR"
   -- ,NULL AS "MWSTS"
   ,CAST(NULL AS FLOAT) AS "WMWST"
   -- ,NULL AS "BDIFF"
   -- ,NULL AS "BDIF2"
   ,H."SGTXT"
   -- ,NULL AS "PROJN"
   -- ,NULL AS "AUFNR"
   -- ,NULL AS "ANLN1"
   -- ,NULL AS "ANLN2"
   -- ,NULL AS "EBELN"
   -- ,NULL AS "EBELP"
   -- ,NULL AS "SAKNR"
   -- ,H."HKONT"
   -- ,NULL AS "FKONT"
   -- ,H."FILKD"
   ,H."ZFBDT"
   ,H."PYMNT_TERMS" AS "ZTERM"
   ,H."ZBD1T"
   ,H."ZBD2T"
   ,H."ZBD3T"
   ,H."ZBD1P"
   ,H."ZBD2P"
   -- ,NULL AS "SKFBT"
   -- ,NULL AS "SKNTO"
   ,H."DISCOUNT" AS "WSKTO"
   ,NULL AS "ZLSCH"
   ,NULL AS "ZLSPR"
   -- ,H."ZBFIX"
   -- ,H."HBKID"
   -- ,H."BVTYP"
   -- ,H."REBZG"
   -- ,H."REBZJ"
   -- ,NULL AS "REBZZ"
   -- ,NULL AS "SAMNR"
   -- ,NULL AS "ZOLLT"
   -- ,NULL AS "ZOLLD"
   -- ,H."LZBKZ"
   -- ,H."LANDL"
   -- ,H."DIEKZ"
   -- ,NULL AS "MANSP"
   -- ,NULL AS "MSCHL"
   -- ,NULL AS "MADAT"
   -- ,NULL AS "MANST"
   -- ,NULL AS "MABER"
   -- ,NULL AS "XNETB"
   -- ,NULL AS "XANET"
   -- ,NULL AS "XCPDD"
   -- ,NULL AS "XESRD"
   -- ,NULL AS "XZAHL"
   -- ,NULL AS "MWSK1"
   -- ,NULL AS "DMBT1"
   -- ,NULL AS "WRBT1"
   -- ,NULL AS "MWSK2"
   -- ,NULL AS "DMBT2"
   -- ,NULL AS "WRBT2"
   -- ,NULL AS "MWSK3"
   -- ,NULL AS "DMBT3"
   -- ,NULL AS "WRBT3"
   -- ,NULL AS "QSSKZ"
   -- ,NULL AS "QSSHB"
   -- ,NULL AS "QBSHB"
   -- ,NULL AS "BSTAT"
   -- ,NULL AS "ANFBN"
   -- ,NULL AS "ANFBJ"
   -- ,NULL AS "ANFBU"
   -- ,NULL AS "VBUND"
   -- ,NULL AS "REBZT"
   ,NULL AS "STCEG"
   -- ,NULL AS "EGBLD"
   -- ,NULL AS "EGLLD"
   -- ,NULL AS "QSZNR"
   -- ,H."QSFBT"
   -- ,H."XINVE"
   -- ,NULL AS "PROJK"
   -- ,NULL AS "FIPOS"
   -- ,NULL AS "NPLNR"
   -- ,NULL AS "AUFPL"
   -- ,NULL AS "APLZL"
   -- ,H."XEGDR"
   -- ,NULL AS "DMBE2"
   -- ,NULL AS "DMBE3"
   -- ,NULL AS "DMB21"
   -- ,NULL AS "DMB22"
   -- ,NULL AS "DMB23"
   -- ,NULL AS "DMB31"
   -- ,NULL AS "DMB32"
   -- ,NULL AS "DMB33"
   -- ,NULL AS "MWST2"
   -- ,NULL AS "MWST3"
   -- ,NULL AS "SKNT2"
   -- ,NULL AS "SKNT3"
   -- ,NULL AS "BDIF3"
   -- ,NULL AS "XRAGL"
   -- ,NULL AS "RSTGR"
   -- ,H."UZAWE"
   -- ,NULL AS "KOSTL"
   -- ,NULL AS "LNRAN"
   -- ,NULL AS "XSTOV"
   -- ,NULL AS "KZBTR"
   -- ,NULL AS "XREF1"
   -- ,NULL AS "XREF2"
   -- ,NULL AS "XARCH"
   -- ,NULL AS "PSWSL"
   -- ,NULL AS "PSWBT"
   -- ,NULL AS "IMKEY"
   -- ,NULL AS "ZEKKN"
   -- ,NULL AS "FISTL"
   -- ,NULL AS "GEBER"
   -- ,NULL AS "DABRZ"
   -- ,NULL AS "XNEGP"
   -- ,H."EMPFB"
   -- ,NULL AS "PRCTR"
   -- ,H."XREF3"
   -- ,NULL AS "DTWS1"
   -- ,NULL AS "DTWS2"
   -- ,NULL AS "DTWS3"
   -- ,NULL AS "DTWS4"
   -- ,NULL AS "XPYPR"
   -- ,H."KIDNO"
   -- ,NULL AS "PYCUR"
   -- ,NULL AS "PYAMT"
   -- ,H."BUPLA"
   -- ,H."SECCO"
   -- ,NULL AS "PPDIFF"
   -- ,NULL AS "PPDIF2"
   -- ,NULL AS "PPDIF3"
   -- ,NULL AS "PENLC1"
   -- ,NULL AS "PENLC2"
   -- ,NULL AS "PENLC3"
   -- ,NULL AS "PENFC"
   -- ,NULL AS "PENDAYS"
   -- ,NULL AS "PENRC"
   -- ,NULL AS "VERTT"
   -- ,NULL AS "VERTN"
   -- ,NULL AS "VBEWA"
   -- ,NULL AS "KBLNR"
   -- ,NULL AS "KBLPOS"
   -- ,NULL AS "GRANT_NBR"
   -- ,NULL AS "GMVKZ"
   -- ,NULL AS "SRTYPE"
   -- ,H."LOTKZ"
   -- ,NULL AS "ZINKZ"
   -- ,NULL AS "FKBER"
   -- ,NULL AS "INTRENO"
   -- ,NULL AS "PPRCT"
   -- ,NULL AS "BUZID"
   -- ,NULL AS "AUGGJ"
   -- ,NULL AS "HKTID"
   -- ,NULL AS "BUDGET_PD"
   -- ,NULL AS "KONTT"
   -- ,NULL AS "KONTL"
   -- ,NULL AS "UEBGDAT"
   -- ,NULL AS "VNAME"
   -- ,NULL AS "EGRUP"
   -- ,NULL AS "BTYPE"
   -- ,NULL AS "PROPMANO"
   ,H."BLART"
   ,H."BLDAT"
   ,H."BUDAT"
   ,NULL AS "CPUDT"
   ,H."XBLNR"
   ,H."WAERS"
   ,H.SCAN_DATE AS "REINDAT"
   -- ,H."USNAM"
   -- ,H."TCODE"
   -- ,H."CPUTM"
   -- ,H."VGART"
   -- ,H."KURSF"
   ,H."GROSS_AMOUNT" AS "RMWWR"
   -- ,H."BEZNK"
   ,H.VAT_AMOUNT AS "WMWST1"
   ,H.TAX_CODE AS "MWSKZ1"
   -- ,H."WMWST2"
   -- ,H."MWSKZ2"
   ,NULL AS "XRECH"
   -- ,H."BKTXT"
   -- ,H."SAPRL"
   -- ,H."LOGSYS"
   -- ,H."XMWST"
   -- ,H."STBLG"
   -- ,H."STJAH"
   -- ,H."MWSKZ_BNK"
   -- ,H."TXJCD_BNK"
   -- ,H."IVTYP"
   -- ,H."XRBTX"
   -- ,H."REPART"
   ,NULL AS "RBSTAT"
   -- ,H."KNUMVE"
   -- ,H."KNUMVL"
   -- ,H."ARKUEN"
   -- ,H."ARKUEMW"
   -- ,H."MAKZN"
   -- ,H."MAKZMW"
   -- ,H."LIEFFN"
   -- ,H."LIEFFMW"
   -- ,H."XAUTAKZ"
   -- ,H."ESRNR"
   -- ,H."ESRPZ"
   -- ,H."ESRRE"
   -- ,H."TXKRS"
   -- ,H."EGMLD"
   -- ,H."VATDATE"
   -- ,H."J_1BNFTYPE"
   -- ,H."BRNCH"
   -- ,H."ERFPR"
   -- ,H."NAME1"
   -- ,H."NAME2"
   -- ,H."NAME3"
   -- ,H."NAME4"
   -- ,H."PSTLZ"
   -- ,H."ORT01"
   -- ,H."LAND1"
   -- ,H."STRAS"
   -- ,H."PFACH"
   -- ,H."PSTL2"
   -- ,H."PSKTO"
   -- ,H."BANKN"
   -- ,H."BANKL"
   -- ,H."BANKS"
   -- ,H."STCD1"
   -- ,H."STCD2"
   -- ,H."STKZU"
   -- ,H."STKZA"
   -- ,H."REGIO"
   -- ,H."BKONT"
   -- ,H."DTAWS"
   -- ,H."DTAMS"
   -- ,H."SPRAS"
   -- ,H."XCPDK"
   -- ,H."EMPFG"
   -- ,H."FITYP"
   -- ,H."STCDT"
   -- ,H."STKZN"
   -- ,H."STCD3"
   -- ,H."STCD4"
   -- ,H."BKREF"
   -- ,H."J_1KFREPRE"
   -- ,H."J_1KFTBUS"
   -- ,H."J_1KFTIND"
   -- ,H."ANRED"
   -- ,H."ERNAME"
   -- ,H."FDLEV"
   -- ,H."FDTAG"
   -- ,H."FRGKZ"
   -- ,H."ERFNAM"
   -- ,H."INV_TRAN"
   -- ,H."PREPAY_STATUS"
   -- ,H."PREPAY_AWKEY"
   -- ,H."ASSIGN_STATUS"
   -- ,H."ASSIGN_NEXT_DATE"
   -- ,H."ASSIGN_END_DATE"
   -- ,H."COPY_BY_BELNR"
   -- ,H."COPY_BY_YEAR"
   -- ,H."COPY_TO_BELNR"
   -- ,H."COPY_TO_YEAR"
   -- ,H."COPY_USER"
   -- ,H."KURSX"
   -- ,H."WWERT"
   -- ,HB."ANZRPV"
   -- ,HB."REPDAT"
   -- ,HB."ANZRPO"
   -- ,HB."DIFFN"
   -- ,HB."RPZIELN"
   -- ,HB."XKORREKT"
   -- ,HB."XZUORDRT"
   -- ,HB."BNKSEL"
   --  ,HB."XRECHL"
   -- ,HB."XRECHR"
   ,H."MANDT" || H."DOCID"  AS "_CASE_KEY"
   ,H."MANDT" || H."DOCID"  AS "MM_KEY"
   ,'PARKED' AS "CASE_TYPE"
   ,COALESCE(T001."BUTXT",'') AS "BUKRS_TEXT"
   ,NULL AS "ZLSPR_TEXT"
   ,COALESCE(T003T."LTEXT",'') AS "BLART_TEXT"
   ,T052PT.ZTERM AS "ZTERM_MD"
   ,T052PT.ZTAGG AS "ZTAGG_MD"
   ,T052PT.ZDART AS "ZDART_MD"
   ,T052PT.ZFAEL AS "ZFAEL_MD"
   ,T052PT.ZMONA AS "ZMONA_MD"
   ,T052PT.ZTAG1 AS "ZTAG1_MD"
   ,T052PT.ZPRZ1 AS "ZPRZ1_MD"
   ,T052PT.ZTAG2 AS "ZTAG2_MD"
   ,T052PT.ZPRZ2 AS "ZPRZ2_MD"
   ,T052PT.ZTAG3 AS "ZTAG3_MD"
   ,H."CURR_ROLE"
   ,H.STATUS AS "VIM_STATUS" -- Added to bring through vim status
   ,H.CHANGE_USER AS CHANGE_USER  -- Added to bring though user
   ,H.ZZZUONR AS ASSIGNMENT -- Added to bring through assignment
FROM
   "/OPT/VIM_1HEAD" AS H
   LEFT JOIN MD_PAYMENT_TERMS AS T052PT
       AND H.MANDT = T052PT.MANDT
       AND H.BUKRS = T052PT.BUKRS
       AND H.BELNR = T052PT.BELNR
       AND H.GJAHR = T052PT.GJAHR
   LEFT JOIN "T003T" AS T003T
       AND T003T."SPRAS" = '<%=primaryLanguageKey%>'
       AND H."MANDT" = T003T."MANDT"
       AND H."BLART" = T003T."BLART"
   LEFT JOIN "T001" AS T001
       AND H."MANDT" = T001."MANDT"
       AND H."BUKRS" = T001."BUKRS"
   AND H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL
;
DROP TABLE IF EXISTS "AP_RSEG_OPEN";
CREATE TABLE "AP_RSEG_OPEN" AS (
SELECT
    RSEG.*
   ,B."_CASE_KEY"
FROM "TMP_FI_MM_UNION" AS B
   INNER JOIN "TMP_AP_RSEG_RBDRSEG" AS RSEG
       AND B.MANDT = RSEG.MANDT
       AND SUBSTRING(B.AWKEY,1,14) = RSEG.BELNR || RSEG.GJAHR -- for posted invoices
   WHERE CASE_TYPE <> 'PARKED'

UNION

SELECT
    RSEG.*
   ,B."_CASE_KEY"
FROM "TMP_FI_MM_UNION" AS B
   INNER JOIN "TMP_AP_RSEG_RBDRSEG" AS RSEG
       AND B.MANDT = RSEG.MANDT
       AND B.MANDT || B.BELNR = RSEG.MANDT || RSEG.BELNR
WHERE CASE_TYPE = 'PARKED'
);