Example to Extract Data from SAP: Difference between revisions
Jump to navigation
Jump to search
m (Ollvihe moved page Extract Data from SAP to Example Script to Extract Data from SAP without leaving a redirect) |
No edit summary |
||
| Line 1: | Line 1: | ||
This page describes an example how to extract data from SAP and load into QPR ProcessAnalyzer. | 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. | ||
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 the . | |||
The actual extractions are done with the[[SQL_Scripting_Commands#--.23ImportSapQuery|--#ImportSapQuery]] command. | |||
<pre lang="typescript" line> | <pre lang="typescript" line> | ||
----------------------------------- | ----------------------------------- | ||
-- Adjust SAP extraction parameters | -- Adjust SAP extraction parameters | ||
----------------------------------- | ----------------------------------- | ||
IF OBJECT_ID ('tempdb..#SAP_Parameters') IS NULL | IF OBJECT_ID ('tempdb..#SAP_Parameters') IS NULL | ||
BEGIN | BEGIN | ||
| Line 13: | Line 17: | ||
END | END | ||
TRUNCATE TABLE #SAP_Parameters; | TRUNCATE TABLE #SAP_Parameters; | ||
INSERT INTO #SAP_Parameters | INSERT INTO #SAP_Parameters | ||
SELECT 'SapSystemID', 'PR1' UNION ALL | SELECT 'SapSystemID', 'PR1' UNION ALL | ||
SELECT 'SapMessageServerHost', '<hostname>' UNION ALL | SELECT 'SapMessageServerHost', '<hostname>' UNION ALL | ||
| Line 48: | Line 49: | ||
SELECT 'SapRowcount', '0' | SELECT 'SapRowcount', '0' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
----------- | ----------- | ||
| Line 59: | Line 58: | ||
SELECT 'DataTableName', 'T163F' UNION ALL | SELECT 'DataTableName', 'T163F' UNION ALL | ||
SELECT 'SapFieldNames', 'EBTYP,EBTXT,SPRAS' UNION ALL | SELECT 'SapFieldNames', 'EBTYP,EBTXT,SPRAS' UNION ALL | ||
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' | SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL | ||
SELECT 'SapRowskips', '0' UNION ALL | SELECT 'SapRowskips', '0' UNION ALL | ||
SELECT 'SapRowcount', '0' | SELECT 'SapRowcount', '0' | ||
| Line 71: | Line 70: | ||
SELECT 'SapQueryTable', 'T008T' UNION ALL | SELECT 'SapQueryTable', 'T008T' UNION ALL | ||
SELECT 'DataTableName', 'T008T' UNION ALL | SELECT 'DataTableName', 'T008T' UNION ALL | ||
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' | SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL | ||
SELECT 'SapFieldNames', 'SPRAS,ZAHLS,TEXTL' | SELECT 'SapFieldNames', 'SPRAS,ZAHLS,TEXTL' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
| Line 83: | Line 82: | ||
SELECT 'DataTableName', 'T077Y' UNION ALL | SELECT 'DataTableName', 'T077Y' UNION ALL | ||
SELECT 'SapFieldNames', 'SPRAS,KTOKK,TXT30' UNION ALL | SELECT 'SapFieldNames', 'SPRAS,KTOKK,TXT30' UNION ALL | ||
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' | SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL | ||
SELECT 'SapRowskips', '0' UNION ALL | SELECT 'SapRowskips', '0' UNION ALL | ||
SELECT 'SapRowcount', '0' | SELECT 'SapRowcount', '0' | ||
| Line 108: | Line 107: | ||
SELECT 'DataTableName', 'T161T' UNION ALL | SELECT 'DataTableName', 'T161T' UNION ALL | ||
SELECT 'SapFieldNames', 'BSART,BSTYP,BATXT,SPRAS' UNION ALL | SELECT 'SapFieldNames', 'BSART,BSTYP,BATXT,SPRAS' UNION ALL | ||
SELECT 'SapWhereClause', 'SPRAS EQ `EN`' | SELECT 'SapWhereClause', 'SPRAS EQ `EN`' UNION ALL | ||
SELECT 'SapRowskips', '0' UNION ALL | SELECT 'SapRowskips', '0' UNION ALL | ||
SELECT 'SapRowcount', '0' | SELECT 'SapRowcount', '0' | ||
| Line 222: | Line 221: | ||
-- Main tables | -- Main tables | ||
------------------------------- | ------------------------------- | ||
----------------------------- | ----------------------------- | ||
-- PO Tables | -- PO Tables | ||
| Line 239: | Line 234: | ||
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 '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`' | SELECT 'SapWhereClause', 'AEDAT GE `20210101` AND AEDAT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 246: | Line 242: | ||
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 '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`' | SELECT 'SapWhereClause', 'AEDAT GE `20220101` AND AEDAT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 254: | Line 251: | ||
SELECT 'SapWhereClause', 'AEDAT GE `20230101`' | SELECT 'SapWhereClause', 'AEDAT GE `20230101`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
-------------------------- | -------------------------- | ||
--EKPO | --EKPO | ||
| Line 263: | Line 261: | ||
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 '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`' | SELECT 'SapWhereClause', 'AEDAT GE `20210101` AND AEDAT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 270: | Line 269: | ||
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 '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`' | SELECT 'SapWhereClause', 'AEDAT GE `20220101` AND AEDAT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 278: | Line 278: | ||
SELECT 'SapWhereClause', 'AEDAT GE `20230101`' | SELECT 'SapWhereClause', 'AEDAT GE `20230101`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
-------------------------- | -------------------------- | ||
--EBAN | --EBAN | ||
| Line 287: | Line 288: | ||
SELECT 'SapFieldNames', 'BANFN,BNFPO,BSART,LOEKZ,STATU,ESTKZ,ERNAM,ERDAT,BADAT,EBELN,EBELP,AFNAM,FRGKZ' 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`' | SELECT 'SapWhereClause', 'BADAT GE `20210101` AND BADAT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 294: | Line 296: | ||
SELECT 'SapFieldNames', 'BANFN,BNFPO,BSART,LOEKZ,STATU,ESTKZ,ERNAM,ERDAT,BADAT,EBELN,EBELP,AFNAM,FRGKZ' 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`' | SELECT 'SapWhereClause', 'BADAT GE `20220101` AND BADAT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
| Line 302: | Line 305: | ||
SELECT 'SapWhereClause', 'BADAT GE `20230101`' | SELECT 'SapWhereClause', 'BADAT GE `20230101`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
-------------------------- | -------------------------- | ||
--EKES | --EKES | ||
| Line 312: | Line 316: | ||
SELECT 'SapWhereClause', 'ERDAT GE `20210101` AND ERDAT LE `20211231`' | SELECT 'SapWhereClause', 'ERDAT GE `20210101` AND ERDAT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 319: | Line 324: | ||
SELECT 'SapWhereClause', 'ERDAT GE `20220101` AND ERDAT LE `20221231`' | SELECT 'SapWhereClause', 'ERDAT GE `20220101` AND ERDAT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 326: | Line 332: | ||
SELECT 'SapWhereClause', 'ERDAT GE `20220101`' | SELECT 'SapWhereClause', 'ERDAT GE `20220101`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
-------------------------- | -------------------------- | ||
--EKBE == PO HISTORY | --EKBE == PO HISTORY | ||
| Line 336: | Line 343: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20210601`' | SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20210601`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 343: | Line 351: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20210601` and CPUDT LE `20211231`' | SELECT 'SapWhereClause', 'CPUDT GE `20210601` and CPUDT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 350: | Line 359: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20220601`' | SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20220601`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 357: | Line 367: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20220601` and CPUDT LE `20221231`' | SELECT 'SapWhereClause', 'CPUDT GE `20220601` and CPUDT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 368: | Line 379: | ||
-- Billing docs | -- Billing docs | ||
--------------------- | --------------------- | ||
--------------- | --------------- | ||
--RBKP | --RBKP | ||
--------------- | --------------- | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '0' UNION ALL | SELECT 'Append', '0' UNION ALL | ||
| Line 381: | Line 389: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20211231`' | SELECT 'SapWhereClause', 'CPUDT GE `20210101` and CPUDT LE `20211231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 388: | Line 397: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20221231`' | SELECT 'SapWhereClause', 'CPUDT GE `20220101` and CPUDT LE `20221231`' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 395: | Line 405: | ||
SELECT 'SapWhereClause', 'CPUDT GE `20230101` ' | SELECT 'SapWhereClause', 'CPUDT GE `20230101` ' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
--------------- | --------------- | ||
--RSEG | --RSEG | ||
| Line 405: | Line 416: | ||
SELECT 'SapWhereClause', '(GJAHR EQ `2021`)' | SELECT 'SapWhereClause', '(GJAHR EQ `2021`)' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 412: | Line 424: | ||
SELECT 'SapWhereClause', '(GJAHR EQ `2022`)' | SELECT 'SapWhereClause', '(GJAHR EQ `2022`)' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
SELECT * from #SAP_parameters UNION ALL | SELECT * from #SAP_parameters UNION ALL | ||
SELECT 'Append', '1' UNION ALL | SELECT 'Append', '1' UNION ALL | ||
| Line 464: | Line 477: | ||
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number' | SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
--------------- | --------------- | ||
| Line 606: | Line 618: | ||
SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number' | SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number' | ||
--#ImportSapQuery | --#ImportSapQuery | ||
DELETE FROM #SapWhereClauseTable | DELETE FROM #SapWhereClauseTable | ||
| Line 1,003: | Line 1,014: | ||
--#ImportSapQuery | --#ImportSapQuery | ||
DROP TABLE #SapWhereClauseTable; | |||
DROP TABLE #CDPOS_Parameters; | |||
DROP TABLE #CDHDR; | |||
---------------- | ---------------- | ||
| Line 1,037: | Line 1,047: | ||
--#ImportSapQuery | --#ImportSapQuery | ||
DROP TABLE #SapWhereClauseTable; | |||
DROP TABLE #CDPOS_Parameters; | |||
DROP TABLE #CDHDR; | |||
---------------- | ---------------- | ||
| Line 1,069: | Line 1,079: | ||
--#ImportSapQuery | --#ImportSapQuery | ||
DROP TABLE #SapWhereClauseTable; | |||
DROP TABLE #CDPOS_Parameters; | |||
DROP TABLE #CDHDR; | |||
---------------- | ---------------- | ||
| Line 1,091: | Line 1,101: | ||
SELECT 'DataTableName', 'CDPOS_BELEG_ALL' UNION ALL | SELECT 'DataTableName', 'CDPOS_BELEG_ALL' UNION ALL | ||
SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW' | SELECT 'SapFieldNames', 'OBJECTID,CHANGENR,TABNAME,TABKEY,FNAME,CHNGIND,VALUE_NEW' | ||
DELETE FROM #SapWhereClauseTable | DELETE FROM #SapWhereClauseTable | ||
Revision as of 23:22, 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 the .
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