Data Pool Customizations

Celonis Product Documentation

Data Pool Customizations

While the original data scope of the AP connector is based on SAP’s parked MM invoices and posted FI invoices, the ETL pipeline can be adjusted to reduce/extend the invoice scope or integrate third-party pre-processing systems (e.g. OpenText (VIM), ReadSoft). In most cases, the data pipeline only needs to be adjusted in a few places to keep the data model structure stable and ensure that the app can be installed without major changes.

The sections below guide you through this customization process and contain code snippets that can be used as a starting point in your implementation.

SAP FI - only blocked invoices

If you don’t park invoices in SAP and just want to run the app on blocked invoices, it makes sense to use the op. DM and add customizations that were made for the existing DM (e.g. additional activities, tables, filters) to the transformations related to the op. DM. The DM will then not contain open parked invoices but only the blocked invoices.

Using the op. DM has the advantage that the Parked & Blocked Invoices App doesn’t have to be customized to another DM structure. Generally, the implementation and customization of the op. DM is less effort than updating multiple PQL definitions in the Knowledge Model. Please keep also in mind that it’s not required to re-extract existing data even when using a second data model so that the APC implications are negligible.

If the addition of the op. DM is not an option, the Parked & Blocked App can be adjusted to show only posted FI invoices.

If you need help, go to the Celonis Support Center at Celopeers. You can search for answers, ask a new question, or create a support ticket.

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:

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.
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.
Create Table: _CEL_AP_CASES - Adds the third-party system to the case table instead of the parked SAP invoices (second union).
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.
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.

Create Table: TMP_FI_MM_UNION

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 ON 1=1
       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 ON 1=1
       AND BKPF.MANDT = RBKP.MANDT
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || RBKP.GJAHR
WHERE 1=1
   AND 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
WHERE 1=1
   AND (H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL)
);

OT Create Table: TMP_AP_RSEG_RBDRSEG

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 ON 1=1
       AND VIM_ITEM."MANDT" = H."MANDT"
       AND VIM_ITEM."DOCID" = H."DOCID"
WHERE 1=1
   AND H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL
;

Create Table: _CEL_AP_CASES

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 ON 1=1
       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 ON 1=1
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || CAST(RBKP.GJAHR AS VARCHAR(4))
   LEFT JOIN MD_PAYMENT_TERMS AS T052PT ON 1=1
       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 ON 1=1
       AND T003T."SPRAS" = '<%=primaryLanguageKey%>'
       AND BKPF."MANDT" = T003T."MANDT"
       AND BKPF."BLART" = T003T."BLART"
   LEFT JOIN "T008T" AS T008T ON 1=1
       AND T008T.SPRAS='<%=primaryLanguageKey%>'
       AND BSEG."MANDT" = T008T."MANDT"
       AND BSEG."ZLSPR" = T008T."ZAHLS"
   LEFT JOIN "T001" AS T001 ON 1=1
       AND BKPF."MANDT" = T001."MANDT"
       AND BKPF."BUKRS" = T001."BUKRS"
WHERE 1=1
   AND 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 ON 1=1
       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 ON 1=1
       AND T003T."SPRAS" = '<%=primaryLanguageKey%>'
       AND H."MANDT" = T003T."MANDT"
       AND H."BLART" = T003T."BLART"
   LEFT JOIN "T001" AS T001 ON 1=1
       AND H."MANDT" = T001."MANDT"
       AND H."BUKRS" = T001."BUKRS"
WHERE 1=1
   AND H."BUKRS" IS NULL
   OR H."BELNR" IS NULL
   OR H."GJAHR" IS NULL
;

Create Table: AP_RSEG_OPEN

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 ON 1=1
       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 ON 1=1
       AND B.MANDT = RSEG.MANDT
       AND B.MANDT || B.BELNR = RSEG.MANDT || RSEG.BELNR
WHERE CASE_TYPE = 'PARKED'
);

Integration of Parked FI Invoices

In the current version of the standard AP connector, only parked MM invoices are included (RSEG, RBKP). In case you are also parking non-PO invoices, the data pipeline needs to be adjusted.

The tables VBKPF and VBSEGK need to be extracted.
Create Table: TMP_FI_MM_UNION - Add a third join to include parked FI invoices:
Create Table: _CEL_AP_CASES - Add third join to include parked FI invoices:

Code Snippets for OpenText (VIM)

Create Table: TMP_FI_MM_UNION

-- Add the below statement to the existing code

UNION ALL -- Parked FI Documents

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"
   ,NULL AS "MM_KEY"
   ,BKPF."TCODE"
   ,NULL AS "STJAH"
   ,NULL AS "STBLG"
   ,NULL AS "XREVERSAL"
   ,NULL AS "CPUDT_MM"
   ,NULL AS "CPUTM_MM"
   ,NULL AS "ERFNAM"
   ,NULL AS "USNAM_MM"
   ,BSEG."LIFNR"
   ,BKPF."AWKEY" AS "AWKEY"-- BKPF."AWKEY"
FROM
   "VBKPF" AS BKPF
   INNER JOIN "VBSEGK" AS BSEG ON 1=1
       AND BKPF."MANDT" = BSEG."MANDT"
       AND BKPF."BUKRS" = BSEG."BUKRS"
       AND BKPF."BELNR" = BSEG."BELNR"
       AND BKPF."GJAHR" = BSEG."GJAHR"
       AND BKPF."AUSBK" = BSEG."AUSBK"WHERE 1=1
   AND BSEG.BSCHL IN <%=postingKeysCaseTable%>
   AND BKPF.BSTAT = 'V'
   AND NOT EXISTS ( -- documents that were not yet already posted
  SELECT 1 FROM "BSIK"
 AS BSIK  WHERE 1=1
       AND BSEG.MANDT = BSIK.MANDT
       AND BSEG.BELNR = BSIK.BELNR
       AND BSEG."GJAHR" = BSIK."GJAHR"
       AND BSEG."BUKRS" = BSIK."BUKRS"
       AND BSEG."BUZEI" = BSIK."BUZEI"
       )
   AND NOT EXISTS ( -- documents that were not yet already posted
  SELECT 1 FROM "RBKP" AS RBKP
  WHERE 1=1
       AND BSEG.MANDT = RBKP.MANDT
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || RBKP.GJAHR
       )

Create Table: _CEL_AP_CASES

-- Add the below statement to the existing code

UNION ALL -- Parked FI Documents
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"
   ,NULL AS "MM_KEY"
   ,NULL AS "MM_DOCUMENT_NUMBER" -- Need to be reviewed
   ,'FI_PARKED' AS "CASE_TYPE"
   ,COALESCE(T001."BUTXT",'') AS "BUKRS_TEXT"
   ,T001.WAERS AS "BUKRS_WAERS"
   --,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 "DUPLICATE"
   --,CASE WHEN BKPF.AWTYP = 'RMRP' THEN RBKP."BELNR" ELSE BSEG."BELNR" END AS "BCB_BELNR" -- Original Query
   ,BSEG.BELNR AS "BCB_BELNR"
FROM "VBSEGK" AS BSEG
   INNER JOIN "VBKPF" AS BKPF ON 1=1
       AND BKPF."MANDT" = BSEG."MANDT"
       AND BKPF."BUKRS" = BSEG."BUKRS"
       AND BKPF."BELNR" = BSEG."BELNR"
       AND BKPF."GJAHR" = BSEG."GJAHR"
   LEFT JOIN MD_PAYMENT_TERMS_OPEN AS T052PT ON 1=1
       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 ON 1=1
       AND T003T."SPRAS" = '<%=primaryLanguageKey%>'
       AND BSEG."MANDT" = T003T."MANDT"
       AND BKPF."BLART" = T003T."BLART"
--  LEFT JOIN "T008T" AS T008T ON 1=1
--      AND T008T.SPRAS='<%=primaryLanguageKey%>'
--      AND BSEG."MANDT" = T008T."MANDT"
--      AND BSEG."ZLSPR" = T008T."ZAHLS"
   LEFT JOIN "T001" AS T001 ON 1=1
       AND BKPF."MANDT" = T001."MANDT"
       AND BKPF."BUKRS" = T001."BUKRS"WHERE 1=1
   AND BSEG.BSCHL IN <%=postingKeysCaseTable%>
   AND BKPF.BSTAT = 'V'
   AND NOT EXISTS ( -- documents that were not yet already posted
  SELECT 1 FROM "BSIK" AS BSIK
  WHERE 1=1
       AND BSEG.MANDT = BSIK.MANDT
       AND BSEG.BELNR = BSIK.BELNR
       AND BSEG."GJAHR" = BSIK."GJAHR"
       AND BSEG."BUKRS" = BSIK."BUKRS"
       AND BSEG."BUZEI" = BSIK."BUZEI"
       )
     AND NOT EXISTS ( -- documents that were not yet already posted
  SELECT 1 FROM "RBKP" AS RBKP  WHERE 1=1
       AND BSEG.MANDT = RBKP.MANDT
       AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || RBKP.GJAHR
       )

Was this helpful?

Would you like to provide feedback? Just click here to suggest edits.

Celonis Product Documentation

Data Pool Customizations

SAP FI - only blocked invoices

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

Code Snippets for OpenText (VIM)

Important

Create Table: TMP_FI_MM_UNION

OT Create Table: TMP_AP_RSEG_RBDRSEG

Create Table: _CEL_AP_CASES

Create Table: AP_RSEG_OPEN

Integration of Parked FI Invoices

Code Snippets for OpenText (VIM)

Create Table: TMP_FI_MM_UNION

Create Table: _CEL_AP_CASES

Search results