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 )