Example to Extract Data from SAP: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 1: Line 1:
This page describes an example how to extract data from SAP and load into QPR ProcessAnalyzer. This example uses the [[SQL_Scripting_for_ETL||SQL scripting]]. As a result, the extracted data is stored into QPR ProcessAnalyzer to tables that can be called ''staging tables''. The next step is to [[Transform_SAP_Data_into_Eventlogs_in_Snowflake|transform the staged data into the eventlogs]] used by the QPR ProcessAnalyzer models.
This page describes an example how to extract data from SAP and load into QPR ProcessAnalyzer. This example uses the [[SQL_Scripting_for_ETL|SQL scripting]]. As a result, the extracted data is stored into QPR ProcessAnalyzer to tables that can be called ''staging tables''. The next step is to [[Transform_SAP_Data_into_Eventlogs_in_Snowflake|transform the staged data into the eventlogs]] used by the QPR ProcessAnalyzer models.


Sometimes, when using the RFC interface, it's not possible to get all required data in one query due to the limitations of the RCF interface. As a workaround, multiple queries need to be used and in each query set a filter to get only a slice of the data. For example, dates can be used limit how much data one query returns.
Sometimes, when using the RFC interface, it's not possible to get all required data in one query due to the limitations of the RCF interface. As a workaround, multiple queries need to be used and in each query set a filter to get only a slice of the data. For example, dates can be used limit how much data one query returns.

Revision as of 23:27, 18 May 2023

This page describes an example how to extract data from SAP and load into QPR ProcessAnalyzer. This example uses the SQL scripting. As a result, the extracted data is stored into QPR ProcessAnalyzer to tables that can be called staging tables. The next step is to transform the staged data into the eventlogs used by the QPR ProcessAnalyzer models.

Sometimes, when using the RFC interface, it's not possible to get all required data in one query due to the limitations of the RCF interface. As a workaround, multiple queries need to be used and in each query set a filter to get only a slice of the data. For example, dates can be used limit how much data one query returns.

The script defines a temporary table #SAP_Parameters that contains the common SAP query related parameters used in all the queries. In addition, is query add its own query specific parameters.

There is also a temporary table #SapWhereClauseTable containing parameters for the SAP query where clause.

The actual extractions are done with the--#ImportSapQuery command. 

-----------------------------------
-- Adjust SAP extraction parameters
-----------------------------------
IF OBJECT_ID ('tempdb..#SAP_Parameters') IS NULL
BEGIN
 CREATE TABLE #SAP_Parameters (par_name varchar(255), par_value varchar(255))
END
TRUNCATE TABLE #SAP_Parameters;
INSERT INTO #SAP_Parameters
SELECT 'SapSystemID', 'PR1' UNION ALL
SELECT 'SapMessageServerHost', '<hostname>' UNION ALL
SELECT 'SapLogonGroup', '<logongroup>' UNION ALL
SELECT 'SapAppServerHost', '<hostname>' UNION ALL
SELECT 'SapSystemNumber', '02' UNION ALL
SELECT 'SapClient', '100' UNION ALL
SELECT 'SapRouter', '' UNION ALL
SELECT 'SapFunction', 'RFC_READ_TABLE' UNION ALL
SELECT 'SapUser', '<username>' UNION ALL
SELECT 'SapPW', '<password>' UNION ALL
SELECT 'SapLanguage', 'EN' UNION ALL
SELECT 'Start', '20210901' UNION ALL
SELECT 'End', '20230501' UNION ALL
SELECT 'SapPoolSize', '5' UNION ALL
SELECT 'SapIdleTimeout', '600' UNION ALL
SELECT 'ProjectId', '1' UNION ALL
SELECT 'ExecuteInClientSide', '1'
SELECT 'CatchOperationExceptions', '1' -- do not stop for errors, so that we can get all the tables

CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255),order_number int);
-----------
--T001: keeping this here as a first small test, even if it has been already extracted
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T001' UNION ALL
SELECT 'DataTableName', 'T001' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,BUTXT,LAND1' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T163F
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T163F' UNION ALL
SELECT 'DataTableName', 'T163F' UNION ALL
SELECT 'SapFieldNames', 'EBTYP,EBTXT,SPRAS' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T008T 
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T008T' UNION ALL
SELECT 'DataTableName', 'T008T' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL
SELECT 'SapFieldNames', 'SPRAS,ZAHLS,TEXTL'
--#ImportSapQuery

-----------
--T077Y
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T077Y' UNION ALL
SELECT 'DataTableName', 'T077Y' UNION ALL
SELECT 'SapFieldNames', 'SPRAS,KTOKK,TXT30' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T024E
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T024E' UNION ALL
SELECT 'DataTableName', 'T024E' UNION ALL
SELECT 'SapFieldNames', 'EKORG,EKOTX' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T161T
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T161T' UNION ALL
SELECT 'DataTableName', 'T161T' UNION ALL
SELECT 'SapFieldNames', 'BSART,BSTYP,BATXT,SPRAS' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

---------------
--T052
---------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T052' UNION ALL
SELECT 'DataTableName', 'T052' UNION ALL
SELECT 'SapFieldNames', 'ZTERM,ZTAGG,ZTAG1,ZPRZ1,ZTAG2,ZPRZ2,ZTAG3,ZSTG1,ZSMN1'
--#ImportSapQuery

---------------
--T052U
---------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T052U' UNION ALL
SELECT 'DataTableName', 'T052U' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`'  UNION ALL
SELECT 'SapFieldNames', 'SPRAS,ZTERM,ZTAGG,TEXT1'
--#ImportSapQuery

-----------
--T003T
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T003T' UNION ALL
SELECT 'DataTableName', 'T003T' UNION ALL
SELECT 'SapFieldNames', 'BLART,LTEXT,SPRAS' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`'  UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T005T 
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T005T' UNION ALL
SELECT 'DataTableName', 'T005T' UNION ALL
SELECT 'SapFieldNames', 'SPRAS,LAND1,LANDX' UNION ALL
SELECT 'SapWhereClause', 'SPRAS EQ `EN`'  UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--T024
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'T024' UNION ALL
SELECT 'DataTableName', 'T024' UNION ALL
SELECT 'SapFieldNames', 'EKGRP,EKNAM' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--USR02
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'USR02' UNION ALL
SELECT 'DataTableName', 'USR02' UNION ALL
SELECT 'SapFieldNames', 'BNAME,USTYP' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--V_USR_NAME
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'V_USR_NAME' UNION ALL
SELECT 'DataTableName', 'V_USR_NAME' UNION ALL
SELECT 'SapFieldNames', 'BNAME,NAME_FIRST,NAME_LAST,NAME_TEXT' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

-----------
--LFA1
-----------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'LFA1' UNION ALL
SELECT 'DataTableName', 'LFA1' UNION ALL
SELECT 'SapFieldNames', 'LIFNR,NAME1,SPRAS,LAND1,ORT01,BRSCH,KTOKK' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'SapRowcount', '0'
--#ImportSapQuery

--------------------------
--DD04T
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'DD04T' UNION ALL
SELECT 'DataTableName', 'DD04T' UNION ALL
SELECT 'SapFieldNames', 'ROLLNAME,SCRTEXT_L' UNION ALL
SELECT 'SapWhereClause', '(DDLANGUAGE EQ `EN`) AND (AS4LOCAL EQ `A`)'
--#ImportSapQuery

-------------------------------
-- Main tables
-------------------------------
-----------------------------
-- PO Tables
------------------------------

--------------------------
--EKKO
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'EKKO' UNION ALL
SELECT 'DataTableName', 'EKKO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,BUKRS,BSTYP,BSART,STATU,LOEKZ,AEDAT,ERNAM,LIFNR,EKORG,EKGRP,WAERS,KUNNR,RESWK,LIFRE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,INCO1,INCO2,FRGZU,FRGKE,FRGGR,FRGSX,IHREZ,UNSEZ' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20210101` AND AEDAT LE `20211231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKKO' UNION ALL
SELECT 'DataTableName', 'EKKO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,BUKRS,BSTYP,BSART,STATU,LOEKZ,AEDAT,ERNAM,LIFNR,EKORG,EKGRP,WAERS,KUNNR,RESWK,LIFRE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,INCO1,INCO2,FRGZU,FRGKE,FRGGR,FRGSX,IHREZ,UNSEZ' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20220101` AND AEDAT LE `20221231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKKO' UNION ALL
SELECT 'DataTableName', 'EKKO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,BUKRS,BSTYP,BSART,STATU,LOEKZ,AEDAT,ERNAM,LIFNR,EKORG,EKGRP,WAERS,KUNNR,RESWK,LIFRE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,INCO1,INCO2,FRGZU,FRGKE,FRGGR,FRGSX,IHREZ,UNSEZ' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20230101`'
--#ImportSapQuery

--------------------------
--EKPO
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'EKPO' UNION ALL
SELECT 'DataTableName', 'EKPO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,LOEKZ,TXZ01,AEDAT,MATNR,BUKRS,WERKS,LGORT,MATKL,MENGE,MEINS,NETPR,NETWR,PSTYP,KNTTP,BANFN,BNFPO,MTART,BSTYP,ELIKZ,EREKZ,BSTAE,BEDNR' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20210101` AND AEDAT LE `20211231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKPO' UNION ALL
SELECT 'DataTableName', 'EKPO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,LOEKZ,TXZ01,AEDAT,MATNR,BUKRS,WERKS,LGORT,MATKL,MENGE,MEINS,NETPR,NETWR,PSTYP,KNTTP,BANFN,BNFPO,MTART,BSTYP,ELIKZ,EREKZ,BSTAE,BEDNR' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20220101` AND AEDAT LE `20221231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKPO' UNION ALL
SELECT 'DataTableName', 'EKPO' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,LOEKZ,TXZ01,AEDAT,MATNR,BUKRS,WERKS,LGORT,MATKL,MENGE,MEINS,NETPR,NETWR,PSTYP,KNTTP,BANFN,BNFPO,MTART,BSTYP,ELIKZ,EREKZ,BSTAE,BEDNR' UNION ALL
SELECT 'SapWhereClause', 'AEDAT GE `20230101`'
--#ImportSapQuery

--------------------------
--EBAN
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'EBAN' UNION ALL
SELECT 'DataTableName', 'EBAN' UNION ALL
SELECT 'SapFieldNames', 'BANFN,BNFPO,BSART,LOEKZ,STATU,ESTKZ,ERNAM,ERDAT,BADAT,EBELN,EBELP,AFNAM,FRGKZ' UNION ALL
SELECT 'SapWhereClause', 'BADAT GE `20210101` AND BADAT LE `20211231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EBAN' UNION ALL
SELECT 'DataTableName', 'EBAN' UNION ALL
SELECT 'SapFieldNames', 'BANFN,BNFPO,BSART,LOEKZ,STATU,ESTKZ,ERNAM,ERDAT,BADAT,EBELN,EBELP,AFNAM,FRGKZ' UNION ALL
SELECT 'SapWhereClause', 'BADAT GE `20220101` AND BADAT LE `20221231`'

--#ImportSapQuery
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EBAN' UNION ALL
SELECT 'DataTableName', 'EBAN' UNION ALL
SELECT 'SapFieldNames', 'BANFN,BNFPO,BSART,LOEKZ,STATU,ESTKZ,ERNAM,ERDAT,BADAT,EBELN,EBELP,AFNAM,FRGKZ' UNION ALL
SELECT 'SapWhereClause', 'BADAT GE `20230101`'
--#ImportSapQuery

--------------------------
--EKES
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'EKES' UNION ALL
SELECT 'DataTableName', 'EKES' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,ETENS,EBTYP,EINDT,LPEIN,UZEIT,ERDAT,EZEIT,MENGE,ESTKZ,LOEKZ,XBLNR,VBELN,VBELP' UNION ALL
SELECT 'SapWhereClause', 'ERDAT GE `20210101` AND ERDAT LE `20211231`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKES' UNION ALL
SELECT 'DataTableName', 'EKES' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,ETENS,EBTYP,EINDT,LPEIN,UZEIT,ERDAT,EZEIT,MENGE,ESTKZ,LOEKZ,XBLNR,VBELN,VBELP' UNION ALL
SELECT 'SapWhereClause', 'ERDAT GE `20220101` AND ERDAT LE `20221231`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKES' UNION ALL
SELECT 'DataTableName', 'EKES' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,ETENS,EBTYP,EINDT,LPEIN,UZEIT,ERDAT,EZEIT,MENGE,ESTKZ,LOEKZ,XBLNR,VBELN,VBELP' UNION ALL
SELECT 'SapWhereClause', 'ERDAT GE `20220101`' 
--#ImportSapQuery

--------------------------
--EKBE == PO HISTORY
--------------------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'EKBE' UNION ALL
SELECT 'DataTableName', 'EKBE' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,VGABE,GJAHR,BELNR,CPUDT,CPUTM,ERNAM,BUDAT,SHKZG,WRBTR,WAERS,MENGE,BWART,BEWTP,XBLNR' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20210601`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKBE' UNION ALL
SELECT 'DataTableName', 'EKBE' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,VGABE,GJAHR,BELNR,CPUDT,CPUTM,ERNAM,BUDAT,SHKZG,WRBTR,WAERS,MENGE,BWART,BEWTP,XBLNR' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20210601` and CPUDT LE `20211231`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKBE' UNION ALL
SELECT 'DataTableName', 'EKBE' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,VGABE,GJAHR,BELNR,CPUDT,CPUTM,ERNAM,BUDAT,SHKZG,WRBTR,WAERS,MENGE,BWART,BEWTP,XBLNR' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20220601`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKBE' UNION ALL
SELECT 'DataTableName', 'EKBE' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,VGABE,GJAHR,BELNR,CPUDT,CPUTM,ERNAM,BUDAT,SHKZG,WRBTR,WAERS,MENGE,BWART,BEWTP,XBLNR' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20220601` and CPUDT LE `20221231`' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'EKBE' UNION ALL
SELECT 'DataTableName', 'EKBE' UNION ALL
SELECT 'SapFieldNames', 'EBELN,EBELP,VGABE,GJAHR,BELNR,CPUDT,CPUTM,ERNAM,BUDAT,SHKZG,WRBTR,WAERS,MENGE,BWART,BEWTP,XBLNR' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20230101`' 
--#ImportSapQuery

-----------------------
-- Billing docs
---------------------
---------------
--RBKP
---------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'RBKP' UNION ALL
SELECT 'DataTableName', 'RBKP' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,BUKRS,USNAM,TCODE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,ZBD3T,ZFBDT,WSKTO,XBLNR,RBSTAT' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20211231`'  
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'RBKP' UNION ALL
SELECT 'DataTableName', 'RBKP' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,BUKRS,USNAM,TCODE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,ZBD3T,ZFBDT,WSKTO,XBLNR,RBSTAT' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20221231`'  
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'RBKP' UNION ALL
SELECT 'DataTableName', 'RBKP' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,BUKRS,USNAM,TCODE,ZTERM,ZBD1T,ZBD1P,ZBD2T,ZBD2P,ZBD3T,ZFBDT,WSKTO,XBLNR,RBSTAT' UNION ALL
SELECT 'SapWhereClause', 'CPUDT GE `20230101` '  
--#ImportSapQuery

---------------
--RSEG
---------------
SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'RSEG' UNION ALL
SELECT 'DataTableName', 'RSEG' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BUKRS,EBELN,EBELP,XBLNR,WRBTR' UNION ALL
SELECT 'SapWhereClause', '(GJAHR EQ `2021`)' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'RSEG' UNION ALL
SELECT 'DataTableName', 'RSEG' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BUKRS,EBELN,EBELP,XBLNR,WRBTR' UNION ALL
SELECT 'SapWhereClause', '(GJAHR EQ `2022`)' 
--#ImportSapQuery

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'RSEG' UNION ALL
SELECT 'DataTableName', 'RSEG' UNION ALL
SELECT 'SapFieldNames', 'BELNR,GJAHR,BUKRS,EBELN,EBELP,XBLNR,WRBTR' UNION ALL
SELECT 'SapWhereClause', '(GJAHR EQ `2023`)' 
--#ImportSapQuery

-------------------------
-- Accounting docs
-------------------------
---------------
--BKPF
---------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2021`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'BKPF' UNION ALL
SELECT 'DataTableName', 'BKPF' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,XBLNR,BKTXT,TCODE,BSTAT,AWTYP,AWKEY,PENRC,USNAM,WAERS,GLVOR,FIKRS,STGRD' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2022`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'BKPF' UNION ALL
SELECT 'DataTableName', 'BKPF' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,XBLNR,BKTXT,TCODE,BSTAT,AWTYP,AWKEY,PENRC,USNAM,WAERS,GLVOR,FIKRS,STGRD' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2023`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'BKPF' UNION ALL
SELECT 'DataTableName', 'BKPF' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,BELNR,GJAHR,BLART,BLDAT,BUDAT,CPUDT,CPUTM,XBLNR,BKTXT,TCODE,BSTAT,AWTYP,AWKEY,PENRC,USNAM,WAERS,GLVOR,FIKRS,STGRD' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

---------------
--BSAK
---------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2021`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', 'FALSE' UNION ALL
SELECT 'SapQueryTable', 'BSAK' UNION ALL
SELECT 'DataTableName', 'BSAK' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,AUGDT,AUGBL,GJAHR,BELNR,BUZEI,BUDAT,BLART,UMSKZ,XBLNR,KIDNO,BSTAT,WRBTR,ZTERM,ZLSCH,ZLSPR,ZBD1P,ZBD1T,ZFBDT,ZBD2P,ZBD2T,ZBD3T,SKNTO,SKFBT,WAERS,LIFNR,GSBER,EBELN,EBELP,BSCHL,KOSTL,SAKNR,AUGGJ,SHKZG' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2022`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', 'TRUE' UNION ALL
SELECT 'SapQueryTable', 'BSAK' UNION ALL
SELECT 'DataTableName', 'BSAK' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,AUGDT,AUGBL,GJAHR,BELNR,BUZEI,BUDAT,BLART,UMSKZ,XBLNR,KIDNO,BSTAT,WRBTR,ZTERM,ZLSCH,ZLSPR,ZBD1P,ZBD1T,ZFBDT,ZBD2P,ZBD2T,ZBD3T,SKNTO,SKFBT,WAERS,LIFNR,GSBER,EBELN,EBELP,BSCHL,KOSTL,SAKNR,AUGGJ,SHKZG' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'GJAHR EQ `2023`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND BUKRS IN (`CZ10`,`DK10`,`FI00`,`FI10`,`FI11`,`FI12`,`FI17`,',2
INSERT INTO #SapWhereClauseTable SELECT '`PL10`,`PL71`,`SE10`,`SE75`,`SE77`,`SE78`,`SE79`,`SK30`,`TR20`)', 3 ;

SELECT * from #SAP_parameters UNION ALL
SELECT 'Append', 'TRUE' UNION ALL
SELECT 'SapQueryTable', 'BSAK' UNION ALL
SELECT 'DataTableName', 'BSAK' UNION ALL
SELECT 'SapFieldNames', 'BUKRS,AUGDT,AUGBL,GJAHR,BELNR,BUZEI,BUDAT,BLART,UMSKZ,XBLNR,KIDNO,BSTAT,WRBTR,ZTERM,ZLSCH,ZLSPR,ZBD1P,ZBD1T,ZFBDT,ZBD2P,ZBD2T,ZBD3T,SKNTO,SKFBT,WAERS,LIFNR,GSBER,EBELN,EBELP,BSCHL,KOSTL,SAKNR,AUGGJ,SHKZG' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

----------------------------------------
-- Change Docs
----------------------------------------

-- Get headers
----------------------------------------
-- Change Docs
----------------------------------------
---------------
--CDHDR_EINKBELEG
---------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210101` AND UDATE LE `20210331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210401` AND UDATE LE `20210630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210701` AND UDATE LE `20210930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20211001` AND UDATE LE `20211231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220101` AND UDATE LE `20220331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220701` AND UDATE LE `20220930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20221001` AND UDATE LE `20221231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230101` AND UDATE LE `20230331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

---------------
--CDHDR_BANF
---------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210101` AND UDATE LE `20210331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210401` AND UDATE LE `20210630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210701` AND UDATE LE `20210930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20211001` AND UDATE LE `20211231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220101` AND UDATE LE `20220331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220701` AND UDATE LE `20220930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

-- Get headers
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20221001` AND UDATE LE `20221231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230101` AND UDATE LE `20230331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

----------------
--CDHDR_INCOMINGINVOICE
----------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210101` AND UDATE LE `20210331`', 2;

SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'

--#ImportSapQuery
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210401` AND UDATE LE `20210630`', 2;

SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210701` AND UDATE LE `20210930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20211001` AND UDATE LE `20211231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220101` AND UDATE LE `20220331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220701` AND UDATE LE `20220930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20221001` AND UDATE LE `20221231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230101` AND UDATE LE `20230331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

---------------
--CDHDR_BELEG
---------------
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210101` AND UDATE LE `20210331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210401` AND UDATE LE `20210630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20210701` AND UDATE LE `20210930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20211001` AND UDATE LE `20211231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220101` AND UDATE LE `20220331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20220701` AND UDATE LE `20220930`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery
DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20221001` AND UDATE LE `20221231`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230101` AND UDATE LE `20230331`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND UDATE GE `20230401` AND UDATE LE `20220630`', 2 ;
SELECT * from #SAP_Parameters UNION ALL
SELECT 'Append', '1' UNION ALL
SELECT 'SapQueryTable', 'CDHDR' UNION ALL
SELECT 'DataTableName', 'CDHDR_BELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTCLAS,OBJECTID,CHANGENR,USERNAME,UDATE,UTIME,TCODE,CHANGE_IND' UNION ALL
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

---------------------------
--Get Line items
--------------------
----------------
--CDPOS_EINKBELEG
----------------
(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectId', (select par_value from #SAP_Parameters where par_name = 'ProjectId')) UNION ALL  
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'CDHDR_EINKBELEG') UNION ALL 
(SELECT 'TargetTable', '#CDHDR') 
--#GetAnalysis

CREATE TABLE #CDPOS_Parameters (par_name varchar(255), par_value varchar(255))
INSERT INTO #CDPOS_Parameters 
SELECT * from #SAP_Parameters UNION ALL
SELECT 'SapQueryTable', 'CDPOS' UNION ALL
SELECT 'SapRowcount', '0' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'DataTableName', 'CDPOS_EINKBELEG' UNION ALL
SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW'

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `EINKBELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND CHANGENR GE `' + convert(nvarchar,min(changenr)) + '`', 2 from #CDHDR
SELECT * from #CDPOS_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DROP TABLE #SapWhereClauseTable;
DROP TABLE #CDPOS_Parameters;
DROP TABLE #CDHDR;

----------------
--CDPOS_BANF
----------------
(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectId', (select par_value from #SAP_Parameters where par_name = 'ProjectId')) UNION ALL  
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'CDHDR_BANF') UNION ALL 
(SELECT 'TargetTable', '#CDHDR') 
--#GetAnalysis

CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int) 

CREATE TABLE #CDPOS_Parameters (par_name varchar(255), par_value varchar(255))
INSERT INTO #CDPOS_Parameters 
SELECT * from #SAP_Parameters UNION ALL
SELECT 'SapQueryTable', 'CDPOS' UNION ALL
SELECT 'SapRowcount', '0' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'DataTableName', 'CDPOS_BANF' UNION ALL
SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW'


DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BANF`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND CHANGENR GE `' + convert(nvarchar,min(changenr)) + '`', 2 from #CDHDR
SELECT * from #CDPOS_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DROP TABLE #SapWhereClauseTable;
DROP TABLE #CDPOS_Parameters;
DROP TABLE #CDHDR;

----------------
--CDPOS_INCOMINGINVOICE
----------------
(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectId', (select par_value from #SAP_Parameters where par_name = 'ProjectId')) UNION ALL  
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'CDHDR_INCOMINGINVOICE') UNION ALL 
(SELECT 'TargetTable', '#CDHDR') 
--#GetAnalysis

CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255),order_number int) 

CREATE TABLE #CDPOS_Parameters (par_name varchar(255), par_value varchar(255))
INSERT INTO #CDPOS_Parameters 
SELECT * from #SAP_Parameters UNION ALL
SELECT 'SapQueryTable', 'CDPOS' UNION ALL
SELECT 'SapRowcount', '0' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'DataTableName', 'CDPOS_INCOMINGINVOICE' UNION ALL
SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW'

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `INCOMINGINVOICE`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND CHANGENR GE `' + convert(nvarchar,min(changenr)) + '`', 2 from #CDHDR
SELECT * from #CDPOS_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DROP TABLE #SapWhereClauseTable;
DROP TABLE #CDPOS_Parameters;
DROP TABLE #CDHDR;

----------------
--CDPOS_BELEG
----------------
(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectId', (select par_value from #SAP_Parameters where par_name = 'ProjectId')) UNION ALL  
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'CDHDR_BELEG') UNION ALL 
(SELECT 'TargetTable', '#CDHDR') 
--#GetAnalysis

CREATE TABLE #CDPOS_Parameters (par_name varchar(255), par_value varchar(255))
INSERT INTO #CDPOS_Parameters 
SELECT * from #SAP_Parameters UNION ALL
SELECT 'SapQueryTable', 'CDPOS' UNION ALL
SELECT 'SapRowcount', '0' UNION ALL
SELECT 'SapRowskips', '0' UNION ALL
SELECT 'DataTableName', 'CDPOS_BELEG_ALL' UNION ALL
SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW'

DELETE FROM #SapWhereClauseTable 
INSERT INTO #SapWhereClauseTable SELECT 'OBJECTCLAS EQ `BELEG`', 1
INSERT INTO #SapWhereClauseTable SELECT 'AND CHANGENR GE `' + convert(nvarchar,min(changenr)) + '`', 2 from #CDHDR
--INSERT INTO #SapWhereClauseTable SELECT 'AND FNAME EQ `ZLSPR`', 1

SELECT * from #CDPOS_Parameters UNION ALL
SELECT 'Append', '0' UNION ALL SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number'
--#ImportSapQuery

DROP TABLE #SapWhereClauseTable;
DROP TABLE #CDPOS_Parameters;
DROP TABLE #CDHDR;

--#Exit
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Example_to_Extract_Data_from_SAP&oldid=23018"