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