Legacy-Definition sicher vergleichen
Diese Detailansicht zeigt den unveränderten Legacy-Dateistand. Von hier aus lässt sich die Definition als Draft in die Workbench übernehmen und dort weiter bearbeiten, ohne das Original im Dateisystem anzutasten.
draft
Raw YAML
Dateiinhalt
Der Inhalt wird read-only aus dem Legacy-Dateisystem geladen. Bearbeitbar wird er erst nach dem Import in die Workbench.
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
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
TargetDatapool: iwm_articles_stock
Metadata
Erkannte Konfiguration
Diese Sicht basiert auf der aktuell lesbaren Legacy-Datei und hilft beim Vergleichen vor dem Import.
| 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 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", "TargetDatapool": "iwm_articles_stock" } } ] |