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