import / iwm_articles_stock_exhibition

Engine: Import
Source_Config: oster400
Target_Config: importEngine
Mysql_Engine: InnoDB
Default_Charset: latin1
Source_Connection_Type: database
Source_Database_Connection_Service: DatabaseConnectionService

Queries:
  - Query:
      GetQuery: WITH Bestaende AS (
       SELECT
       lb.LBFIRM AS firma,
       lb.LBARTE AS ordernumber,
       lb.LBAPNR,
       COALESCE(SUM(CASE WHEN LBLAGN IN (410, 450) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Witten,
       COALESCE(SUM(CASE WHEN LBLAGN IN (810, 850) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Bottrop,
       COALESCE(SUM(CASE WHEN LBLAGN IN (600, 610, 615) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Recklinghausen,
       COALESCE(SUM(CASE WHEN LBLAGN IN (661, 665, 667) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Leverkusen,
       COALESCE(SUM(CASE WHEN LBLAGN IN (500, 510, 550) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Haan
       FROM
       IWMV2R1DTA.LBST00 lb
       LEFT JOIN
       IWMV2R1DTA.ARTS00 ar ON lb.LBARTE = ar.ARARTE
       WHERE
       lb.LBFIRM = 3
       AND lb.LBSTAT = 'A'
       AND (
       ((LBLAGN = 410 OR LBLAGN = 450) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN = 810 OR LBLAGN = 850) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (600, 610, 615)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (661, 665, 667)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (500, 510, 550)) AND (LBDISP = 'K' OR ARHWGR > 90))
        )
        GROUP BY lb.LBFIRM, lb.LBARTE, lb.LBAPNR
       UNION ALL
       SELECT
       lb.LBFIRM AS firma,
        lb.LBARTE AS ordernumber,
       lb.LBAPNR,
       COALESCE(SUM(CASE WHEN LBLAGN IN (100, 110, 150) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Witten,
       COALESCE(SUM(CASE WHEN LBLAGN IN (700, 710, 750) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Bottrop,
       COALESCE(SUM(CASE WHEN LBLAGN IN (600, 310, 315) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Recklinghausen,
       COALESCE(SUM(CASE WHEN LBLAGN IN (360, 361, 950, 365) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Leverkusen,
       COALESCE(SUM(CASE WHEN LBLAGN IN (250, 210, 200) THEN LBLMNG ELSE 0 END), 0) AS Bestand_Haan
       FROM
       IWMV2R1DTA.LBST00 lb
       LEFT JOIN
       IWMV2R1DTA.ARTS00 ar ON lb.LBARTE = ar.ARARTE
       WHERE
       lb.LBFIRM = 1
       AND lb.LBSTAT = 'A'
       AND (
       ((LBLAGN IN (100, 110, 150)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (700, 710, 750)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR 
       ((LBLAGN IN (600, 310, 315)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (360, 361, 950, 365)) AND (LBDISP = 'K' OR ARHWGR > 90))
       OR
       ((LBLAGN IN (250, 210, 200)) AND (LBDISP = 'K' OR ARHWGR > 90))
       )
       GROUP BY lb.LBFIRM, lb.LBARTE, lb.LBAPNR
       )
       SELECT
       b.firma,
       b.ordernumber,
       b.LBAPNR,
       COALESCE(CAST(b.Bestand_Witten AS INT), 0) AS witten,
       COALESCE(CAST(b.Bestand_Bottrop AS INT), 0) AS bottrop,
       COALESCE(CAST(b.Bestand_Recklinghausen AS INT), 0) AS recklinghausen,
       COALESCE(CAST(b.Bestand_Leverkusen AS INT), 0) AS leverkusen,
       COALESCE(CAST(b.Bestand_Haan AS INT), 0) AS haan
       FROM Bestaende b
       WHERE b.ordernumber NOT IN (
       SELECT ar.ARARTE
       FROM IWMV2R1DTA.ARTS00 ar
       WHERE ar.ARATAR = 'D'
       )





      TargetDatapool: iwm_articles_stock_exhibition