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 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" 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 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 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 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 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 BSEG."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" 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 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 AND BSEG.MANDT = RBKP.MANDT AND SUBSTRING(BKPF.AWKEY,1,14) = RBKP.BELNR || RBKP.GJAHR )