SELECT
warenKorb.WARKBNTZ,
warenKorb.WARKWARK,
warenKorb.WARKWKAR,
warenKorb.WARKVADR,
warenKorb.WARKVSAR,
warenKorb.WARKANGX,
warenKorb.WARKSTAT,
warenKorb.WARKWAEH,
kontierung.KDKOKONT,
kontierung.KDKOKOTX,
kostenStelle.KDKSKOST,
kostenStelle.KDKSTEXT,
warenKorbData.WKDTKRVX,
benutzer.BNTNNAM1,
benutzer.BNTNNAM2,
benutzerGruppe.GPSBGRPN,
lieferant.LFNTNAM1,
supplier_ociSupplier_table.lfsswert as supplier_ociSupplier,
supplier_ocidublette_table.lfsswert as supplier_ocidublette,
supplier_ocidulette_table.lfsswert as supplier_ocidulette,
warenKorb.WARKSTAT,
warenKorb.WARKWAEH,
wkdtartx,
wkdtbmng,
wkdtccty,
wkdtccid,
round( warenKorbData.wkdtposw/sswaek00.waekumef,2) as article_value_line_in_euros,
round( warenKorbData.wkdtppeh/sswaek00.waekumef,2) as article_value_per_einheit_in_euros,
eclass_v51.ecdeklax as eclass_v51,
eclass_v51.ecdename as eclass_v51_name,
eclass_v51_1.ecdeklax as eclass_v51_1,
eclass_v51_1.ecdename as eclass_v51_1_name,
eclass_v51_2.ecdeklax as eclass_v51_2,
eclass_v51_2.ecdename as eclass_v51_2_name,
eclass_v51_3.ecdeklax as eclass_v51_3,
eclass_v51_3.ecdename as eclass_v51_3_name,
eclass_v71.ecdeklax as eclass_v71,
eclass_v71.ecdename as eclass_v71_name,
eclass_v71_1.ecdeklax as eclass_v71_1,
eclass_v71_1.ecdename as eclass_v71_1_name,
eclass_v71_2.ecdeklax as eclass_v71_2,
eclass_v71_2.ecdename as eclass_v71_2_name,
eclass_v71_3.ecdeklax as eclass_v71_3,
eclass_v71_3.ecdename as eclass_v71_3_name,
wkdtkutx,
kunde.*,
DUNS_Nummer_tbl.protwert as client_DUNS_Nummer,
Handelsregisternummer_tbl.protwert as client_duns_Handelsregisternummer,
Position_tbl.protwert as client_duns_position,
Branche_tbl.protwert as client_Branche_code,
Beschaeftigte_tbl.protwert as client_duns_Beschaeftigte,
duns_description.duns_description as client_branch,
supplier_LAND_table.lfsswert as supplier_LAND,
supplier_Name_table.lfsswert as supplier_Name,
supplier_intern_table.lfsswert as supplier_intern,
supplier_language_table.lfsswert as supplier_language,
supplier_lfntgroup_table.lfsswert as supplier_lfntgroup,
supplier_mroSupplierNew_table.lfsswert as supplier_mroSupplierNew,
supplier_schnittstelle_table.lfsswert as supplier_schnittstelle,
supplier_systemlieferant_table.lfsswert as supplier_systemlieferant,
supplier_ustID_table.lfsswert as supplier_ustID,
lieferant.LFNTLFNT,
lieferant.LFNTKUTX,
lieferant.LFNTNAM1,
lieferant.LFNTNAM2,
lieferant.LFNTPLZ,
lieferant.LFNTORT,
lieferant.LFNTPSTF,
lieferant.LFNTMBWR,
lieferant.LFNTWAEH,
CASE lieferant.LFNTLAND WHEN 'D' THEN 'DE' ELSE lieferant.LFNTLAND END as lfntland,
warenKorbData.*,
bestellungAdresse.*,
betreuer.prottext as betreuer,
konzname,
plz.lat,
plz.lon,
CASE WHEN plz.lat IS NULL THEN 0 ELSE plz.lat END AS lat,
CASE WHEN plz.lon IS NULL THEN 0 ELSE plz.lon END AS lon,
STR_TO_DATE(
       CONCAT('',WARKDBST, lpad(WARKZBST,6,0)),
    '%Y%m%d%H%i%s'
 ) as order_timestamp
FROM utf8.sswark00 AS warenKorb
INNER JOIN utf8.sswkdt00 AS warenKorbData ON warenKorb.WARKWARK = warenKorbData.WKDTWARK AND warenKorbData.WKDTLFNT = warenKorb.WARKLFNT
INNER JOIN utf8.sskund00 AS kunde ON warkkund=kundkund AND kundadra=3
INNER JOIN utf8.sswkla00 AS bestellungAdresse ON WKLAWARK = WARKWARK
left join utf8.ssprot00 as betreuer on protwert=kundkund and protkeyx='Y' and kundadra=3 and kundlfnt=0
left join utf8.sskonz00 on konzkund=kundkund and kundadra=3 and kundlfnt=0
LEFT JOIN utf8.s4_plz_geo_code AS plz ON plz.country_code = bestellungAdresse.WKLALAND AND plz.zip_code = bestellungAdresse.WKLAPLZ
LEFT JOIN utf8.sslfnt00 AS lieferant ON warenKorbData.WKDTLFNT = lieferant.LFNTLFNT
LEFT JOIN utf8.sskdks00 AS kostenStelle ON warenKorbData.WKDTCCID = kostenStelle.KDKSKOST AND kostenStelle.KDKSKUND = warenKorb.WARKKUND
LEFT JOIN utf8.sskdko00 AS kontierung ON warenKorbData.WKDTCCTY = kontierung.KDKOKONT AND kontierung.KDKOBNTZ = 0 AND kontierung.KDKOKUND = warenKorb.WARKKUND
LEFT JOIN utf8.ssbntn00 AS benutzer ON benutzer.BNTNBNTZ = warenKorb.WARKBNTZ
LEFT JOIN utf8.ssgpsb00 AS benutzerGruppe ON benutzerGruppe.GPSBGRPN = benutzer.BNTNBNTZ AND benutzerGruppe.GPSBKUND = benutzer.BNTNKUND
left join ssklkl00 as v51cls on v51cls.klklkund=0 and v51cls.klkllfnt=lfntlfnt and v51cls.klklartx=wkdtartx and v51cls.klklvers='5.1'
left join ssecde00 as eclass_v51 on eclass_v51.ecdevers=v51cls.klklvers and eclass_v51.ecdeklax=v51cls.klklklax and eclass_v51.ecdespra='de' and eclass_v51.ecdeland='DE'
left join ssecde00 as eclass_v51_1 on eclass_v51_1.ecdevers=v51cls.klklvers and eclass_v51_1.ecdeklax=concat(substring(v51cls.klklklax,1,2),'000000') and eclass_v51_1.ecdespra='de' and eclass_v51_1.ecdeland='DE'
left join ssecde00 as eclass_v51_2 on eclass_v51_2.ecdevers=v51cls.klklvers and eclass_v51_2.ecdeklax=concat(substring(v51cls.klklklax,1,4),'0000') and eclass_v51_2.ecdespra='de' and eclass_v51_2.ecdeland='DE'
left join ssecde00 as eclass_v51_3 on eclass_v51_3.ecdevers=v51cls.klklvers and eclass_v51_3.ecdeklax=concat(substring(v51cls.klklklax,1,4),'0000') and eclass_v51_3.ecdespra='de' and eclass_v51_3.ecdeland='DE'
left join ssecde00 as eclass_v51_4 on eclass_v51_4.ecdevers=v51cls.klklvers and eclass_v51_4.ecdeklax=concat(substring(v51cls.klklklax,1,6),'00') and eclass_v51_4.ecdespra='de' and eclass_v51_4.ecdeland='DE'
left join ssklkl00 as v71cls on v71cls.klklkund=0 and v71cls.klkllfnt=lfntlfnt and v71cls.klklartx=wkdtartx and v71cls.klklvers='7.1'
left join ssecde00 as eclass_v71 on eclass_v71.ecdevers=v71cls.klklvers and eclass_v71.ecdeklax=v71cls.klklklax and eclass_v71.ecdespra='de' and eclass_v71.ecdeland='DE'
left join ssecde00 as eclass_v71_1 on eclass_v71_1.ecdevers=v71cls.klklvers and eclass_v71_1.ecdeklax=concat(substring(v71cls.klklklax,1,2),'000000') and eclass_v71_1.ecdespra='de' and eclass_v71_1.ecdeland='DE'
left join ssecde00 as eclass_v71_2 on eclass_v71_2.ecdevers=v71cls.klklvers and eclass_v71_2.ecdeklax=concat(substring(v71cls.klklklax,1,4),'0000') and eclass_v71_2.ecdespra='de' and eclass_v71_2.ecdeland='DE'
left join ssecde00 as eclass_v71_3 on eclass_v71_3.ecdevers=v71cls.klklvers and eclass_v71_3.ecdeklax=concat(substring(v71cls.klklklax,1,6),'00') and eclass_v71_3.ecdespra='de' and eclass_v71_3.ecdeland='DE'
left join ssprot00 as DUNS_Nummer_tbl  on DUNS_Nummer_tbl.protdatu=kunde.kundkund and DUNS_Nummer_tbl.protkeyx='DU' and DUNS_Nummer_tbl.prottext='DUNS-Nummer'
left join ssprot00 as Handelsregisternummer_tbl on Handelsregisternummer_tbl.protdatu=kunde.kundkund and Handelsregisternummer_tbl.protkeyx='DU' and Handelsregisternummer_tbl.prottext='Handelsregisternummer'
left join ssprot00 as Position_tbl on Position_tbl.protdatu=kunde.kundkund and Position_tbl.protkeyx='DU' and Position_tbl.prottext='Position'
left join ssprot00 as Branche_tbl on Branche_tbl.protdatu=kunde.kundkund and Branche_tbl.protkeyx='DU' and Branche_tbl.prottext='Branche'
left join ssprot00 as Beschaeftigte_tbl on Beschaeftigte_tbl.protdatu=kunde.kundkund and Beschaeftigte_tbl.protkeyx='DU' and Beschaeftigte_tbl.prottext='Beschaeftigte'
left join duns_description on duns_id = Branche_tbl.protwert
left join ssbnsp00 on bnspbntz = warkbntz
left join sskdsp00 on kdspkund = kundkund
left join sswaek00 on waekwaeh=WARKWAEH and waekabdt=substring(warkdbst,1,6)
left join sslfss00 as supplier_LAND_table on supplier_LAND_table.lfsslfnt=lfntlfnt and supplier_LAND_table.lfsskeyx='LAND'
left join sslfss00 as supplier_Name_table on supplier_Name_table.lfsslfnt=lfntlfnt and supplier_Name_table.lfsskeyx='Name'
left join sslfss00 as supplier_intern_table on supplier_intern_table.lfsslfnt=lfntlfnt and supplier_intern_table.lfsskeyx='intern'
left join sslfss00 as supplier_language_table on supplier_language_table.lfsslfnt=lfntlfnt and supplier_language_table.lfsskeyx='language'
left join sslfss00 as supplier_lfntgroup_table on supplier_lfntgroup_table.lfsslfnt=lfntlfnt and supplier_lfntgroup_table.lfsskeyx='lfntgroup'
left join sslfss00 as supplier_mroSupplierNew_table on supplier_mroSupplierNew_table.lfsslfnt=lfntlfnt and supplier_mroSupplierNew_table.lfsskeyx='mroSupplierNew'
left join sslfss00 as supplier_schnittstelle_table on supplier_schnittstelle_table.lfsslfnt=lfntlfnt and supplier_schnittstelle_table.lfsskeyx='schnittstelle'
left join sslfss00 as supplier_systemlieferant_table on supplier_systemlieferant_table.lfsslfnt=lfntlfnt and supplier_systemlieferant_table.lfsskeyx='systemlieferant'
left join sslfss00 as supplier_ustID_table on supplier_ustID_table.lfsslfnt=lfntlfnt and supplier_ustID_table.lfsskeyx='ustID'
left join sslfss00 as supplier_geloescht_table on supplier_geloescht_table.lfsslfnt=lfntlfnt and supplier_geloescht_table.lfsskeyx='geloescht'
left join sslfss00 as supplier_ociSupplier_table on supplier_ociSupplier_table.lfsslfnt=lfntlfnt and supplier_ociSupplier_table.lfsskeyx='ociSupplier'
left join sslfss00 as supplier_ocidublette_table on supplier_ocidublette_table.lfsslfnt=lfntlfnt and supplier_ocidublette_table.lfsskeyx='ocidublette'
left join sslfss00 as supplier_ocidulette_table on supplier_ocidulette_table.lfsslfnt=lfntlfnt and supplier_ocidulette_table.lfsskeyx='ocidulette'
WHERE warenKorb.WARKWKAR IN (2,3)
AND bnspbntz IS NULL
AND kdspkund IS NULL
warenKorb.warkdbst > 20191000
warenKorb.warkdbst < 20191100