Engine bearbeiten

Bestand öffnen Altbestand Zum Editor Run erst mit Status active

DRAFT Status

engine Typ

import Runner

1 Revisionen

Editor

Engine bearbeiten

Definitionstyp Runner-Typ

Name Titel

Status YAML-Inhalt

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

Validation

Validierung

Keine Validierungsfehler gespeichert.

Metadata

Metadaten

mode	-
name	iwm_articles_stock
datapool	-
runner_type	import
feature_flags	Queries
source_config	oster400
target_config	importEngine
definition_kind	engine
source_datapool	-
target_datapool	-
source_files_count	0
mapping_entry_count	0
connection_references	oster400
source_connection_type	database
target_connection_type	-

History

Revisionen

Rev	Erstellt	Fehler
1	2026-03-17 13:42:17.098231+00	0