Skip to main content

Celonis Product Documentation

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.

  1. The tables VBKPF and VBSEGK need to be extracted.

  2. Create Table: TMP_FI_MM_UNION - Add a third join to include parked FI invoices:

    Create_Table_TMP_FI_MM_UNION_Parked.png
  3. Create Table: _CEL_AP_CASES - Add third join to include parked FI invoices:

    Create_Table_CEL_AP_CASES_Parked.png
Code Snippets for OpenText (VIM)
-- 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
       )

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