import / iwm_articles_stock_dynsets

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: SELECT
        dyn_articles.firma,
        dyn_articles.set_ordernumber as ordernumber,
        MIN(coalesce(CAST(stock.witten AS INT), 0))         AS witten,
        MIN(coalesce(CAST(stock.bottrop AS INT), 0))        AS bottrop,
        MIN(coalesce(CAST(stock.recklinghausen AS INT), 0)) AS recklinghausen,
        MIN(coalesce(CAST(stock.haan AS INT), 0))           AS haan,
        MIN(coalesce(CAST(stock.leverkusen AS INT), 0))     AS leverkusen
        FROM (
        SELECT
        b.firma,
        b.ordernumber,
        b.LBAPNR,
        SUM(coalesce(CAST(b.Bestand_Witten AS INT), 0) - coalesce(CAST(vr.VR_Witten AS INT), 0))                 AS witten,
        SUM(coalesce(CAST(b.Bestand_Bottrop AS INT), 0) - coalesce(CAST(vr.VR_Bottrop AS INT), 0) )              AS bottrop,
        SUM(coalesce(CAST(b.Bestand_Recklinghausen AS INT), 0) - coalesce(CAST(vr.VR_Recklinghausen AS INT), 0)) AS recklinghausen,
        SUM(coalesce(CAST(b.Bestand_Haan AS INT), 0) - coalesce(CAST(vr.VR_Haan AS INT), 0) )                    AS haan,
        SUM(coalesce(CAST(b.Bestand_Leverkusen AS INT), 0) - coalesce(CAST(vr.Vr_Leverkusen AS INT), 0))         AS leverkusen
        FROM (
        SELECT
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBFIRM, t2.LBFIRM), t3.LBFIRM),t4.LBFIRM),t5.LBFIRM) AS firma,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBARTE, t2.LBARTE), t3.LBARTE),t4.LBARTE),t5.LBARTE) AS ordernumber,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBAPNR, t2.LBAPNR), t3.LBAPNR),t4.LBAPNR),t5.LBAPNR) AS LBAPNR,
        t1.Bestand AS Bestand_Witten,
        t2.Bestand AS Bestand_Bottrop,
        t3.Bestand AS Bestand_Recklinghausen,
        t4.Bestand AS Bestand_Leverkusen,
        t5.Bestand AS Bestand_Haan
        FROM (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 3 AND (LBLAGN = 400 OR LBLAGN = 450 OR LBLAGN = 900 OR LBLAGN = 945) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))) t1
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 3 AND (LBLAGN = 800 OR LBLAGN = 850 OR LBLAGN = 700) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))) t2
        ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 3 AND (LBLAGN = 600 OR LBLAGN = 615) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t3
        ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 3 AND (LBLAGN = 660 OR LBLAGN = 665 OR LBLAGN = 950) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t4
        ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 3 AND (LBLAGN = 500 OR LBLAGN = 550 OR LBLAGN = 501) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t5
        ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR
        UNION
        SELECT
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBFIRM, t2.LBFIRM), t3.LBFIRM),t4.LBFIRM),t5.LBFIRM) AS firma,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBARTE, t2.LBARTE), t3.LBARTE),t4.LBARTE),t5.LBARTE) AS ordernumber,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.LBAPNR, t2.LBAPNR), t3.LBAPNR),t4.LBAPNR),t5.LBAPNR) AS LBAPNR,
        t1.Bestand AS Bestand_Witten,
        t2.Bestand AS Bestand_Bottrop,
        t3.Bestand AS Bestand_Recklinghausen,
        t4.Bestand AS Bestand_Leverkusen,
        t5.Bestand AS Bestand_Haan
        FROM (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 1 AND (LBLAGN = 100 OR LBLAGN = 150 OR LBLAGN = 900 OR LBLAGN = 945) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))) t1
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 1 AND (LBLAGN = 700 OR LBLAGN = 750) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))) t2
        ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE LBFIRM = 1 AND (LBLAGN = 600 OR LBLAGN = 615 OR LBLAGN = 315) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t3
        ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR or t2.LBARTE = t3.LBARTE AND t2.LBAPNR = t3.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE
        LBFIRM = 1 AND (LBLAGN = 660 OR LBLAGN = 665 OR LBLAGN = 950 OR LBLAGN = 365 OR LBLAGN = 360) AND
        LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t4
        ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR or t2.LBARTE = t4.LBARTE AND t2.LBAPNR = t4.LBAPNR or t3.LBARTE = t4.LBARTE AND t3.LBAPNR = t4.LBAPNR
        FULL OUTER JOIN (
        SELECT
        LBFIRM,
        LBARTE,
        LBAPNR,
        COALESCE(SUM(LBLMNG), 0) AS Bestand
        FROM IWMV2R1DTA.LBST00
        WHERE
        LBFIRM = 1 AND (LBLAGN = 500 OR LBLAGN = 501 OR LBLAGN = 200 OR LBLAGN = 250) AND LBSTAT = 'A'
        GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
        HAVING ((LBARTE > 0) AND (LBDISP = 'L'))
        ) t5
        ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR or t2.LBARTE = t5.LBARTE AND t2.LBAPNR = t5.LBAPNR or t3.LBARTE = t5.LBARTE AND t3.LBAPNR = t5.LBAPNR or t4.LBARTE = t5.LBARTE AND t4.LBAPNR = t5.LBAPNR) b
        LEFT JOIN (
        SELECT
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRFIRM, t2.VRFIRM), t3.VRFIRM),t4.VRFIRM),t5.VRFIRM) AS firma,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRARTE, t2.VRARTE), t3.VRARTE),t4.VRARTE),t5.VRARTE) AS ordernumber,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRAPNR, t2.VRAPNR), t3.VRAPNR),t4.VRAPNR),t5.VRAPNR) AS VRAPNR,
        t1.Bestand AS VR_Witten,
        t2.Bestand AS VR_Bottrop,
        t3.Bestand AS VR_Recklinghausen,
        t4.Bestand AS Vr_Leverkusen,
        t5.Bestand AS VR_Haan
        FROM (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE
        VRFIRM = 3 AND (VRLAGN = 400 OR VRLAGN = 450 OR VRLAGN = 900 OR VRLAGN = 945) AND VRSTAT = 'A'
        AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)) t1
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 3 AND (VRLAGN = 800 OR VRLAGN = 850 OR VRLAGN = 700) AND VRSTAT = 'A' AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)) t2
        ON t1.VRARTE = t2.VRARTE AND t1.VRAPNR = t2.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 3 AND (VRLAGN = 600 OR VRLAGN = 615) AND VRSTAT = 'A' AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t3
        ON t1.VRARTE = t3.VRARTE AND t1.VRAPNR = t3.VRAPNR or t2.VRARTE = t3.VRARTE AND t2.VRAPNR = t3.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 3 AND (VRLAGN = 660 OR VRLAGN = 665 OR VRLAGN = 950) AND VRSTAT = 'A' AND
        VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t4
        ON t1.VRARTE = t4.VRARTE AND t1.VRAPNR = t4.VRAPNR or t2.VRARTE = t4.VRARTE AND t2.VRAPNR = t4.VRAPNR or t3.VRARTE = t4.VRARTE AND t3.VRAPNR = t4.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 3 AND (VRLAGN = 500 OR VRLAGN = 550 OR VRLAGN = 501) AND VRSTAT = 'A' AND
        VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t5
        ON t1.VRARTE = t5.VRARTE AND t1.VRAPNR = t5.VRAPNR or t2.VRARTE = t5.VRARTE AND t2.VRAPNR = t5.VRAPNR or t3.VRARTE = t5.VRARTE AND t3.VRAPNR = t5.VRAPNR or t4.VRARTE = t5.VRARTE AND t4.VRAPNR = t5.VRAPNR
        UNION
        SELECT
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRFIRM, t2.VRFIRM), t3.VRFIRM),t4.VRFIRM),t5.VRFIRM) AS firma,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRARTE, t2.VRARTE), t3.VRARTE),t4.VRARTE),t5.VRARTE) AS ordernumber,
        IFNULL(IFNULL(IFNULL(IFNULL(t1.VRAPNR, t2.VRAPNR), t3.VRAPNR),t4.VRAPNR),t5.VRAPNR) AS VRAPNR,
        t1.Bestand AS VR_Witten,
        t2.Bestand AS VR_Bottrop,
        t3.Bestand AS VR_Recklinghausen,
        t4.Bestand AS Vr_Leverkusen,
        t5.Bestand AS VR_Haan
        FROM (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE
        VRFIRM = 1 AND (VRLAGN = 100 OR VRLAGN = 150 OR VRLAGN = 900 OR VRLAGN = 945) AND VRSTAT = 'A'
        AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)) t1
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 1 AND (VRLAGN = 700 OR VRLAGN = 750) AND VRSTAT = 'A' AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)) t2
        ON t1.VRARTE = t2.VRARTE AND t1.VRAPNR = t2.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 1 AND (VRLAGN = 600 OR VRLAGN = 615 OR VRLAGN = 315) AND VRSTAT = 'A' AND
        VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t3
        ON t1.VRARTE = t3.VRARTE AND t1.VRAPNR = t3.VRAPNR or t2.VRARTE = t3.VRARTE AND t2.VRAPNR = t3.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 1 AND
        (VRLAGN = 660 OR VRLAGN = 665 OR VRLAGN = 950 OR VRLAGN = 365 OR VRLAGN = 360) AND
        VRSTAT = 'A'
        AND VRRSTT = 1
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t4
        ON t1.VRARTE = t4.VRARTE AND t1.VRAPNR = t4.VRAPNR or t2.VRARTE = t4.VRARTE AND t2.VRAPNR = t4.VRAPNR or t3.VRARTE = t4.VRARTE AND t3.VRAPNR = t4.VRAPNR
        FULL OUTER JOIN (
        SELECT
        VRFIRM,
        VRARTE,
        VRAPNR,
        COALESCE(SUM(VRBMNG), 0) AS Bestand
        FROM IWMV2R1DTA.VRES01
        WHERE VRFIRM = 1 AND (VRLAGN = 500 OR VRLAGN = 501 OR VRLAGN = 200 OR VRLAGN = 250) AND
        VRRSTT = 1
        AND VRSTAT = 'A'
        GROUP BY VRFIRM, VRARTE, VRAPNR
        HAVING (VRARTE > 0)
        ) t5
        ON t1.VRARTE = t5.VRARTE AND t1.VRAPNR = t5.VRAPNR or t2.VRARTE = t5.VRARTE AND t2.VRAPNR = t5.VRAPNR or t3.VRARTE = t5.VRARTE AND t3.VRAPNR = t5.VRAPNR or t4.VRARTE = t5.VRARTE AND t4.VRAPNR = t5.VRAPNR) vr
        ON vr.firma = b.firma AND vr.ordernumber = b.ordernumber AND vr.VRAPNR = b.LBAPNR
        WHERE b.ordernumber NOT IN (
        SELECT ar.ARARTE AS ordernumber
        FROM IWMV2R1DTA.ARTS00 ar
        WHERE ar.ARATAR = 'D'
        )
        GROUP BY   b.firma,   b.ordernumber,   b.LBAPNR
        ) stock
        RIGHT JOIN (
        SELECT
        ARARTE AS set_ordernumber,
        VSARTK AS ordernumber,
        VSFIRM AS firma
        FROM IWMV2R1DTA.VSET00 vset
        LEFT JOIN IWMV2R1DTA.ARTS00 ar
        ON vset.VSARTE = ar.ARARTE
        WHERE ARATAR = 'D') dyn_articles
        ON stock.ordernumber = dyn_articles.ordernumber AND stock.firma = dyn_articles.firma
        GROUP BY dyn_articles.firma, dyn_articles.set_ordernumber





      TargetDatapool: iwm_articles_stock_dynsets