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
                 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,
                 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_Haan AS INT), 0)                   AS haan,
                 coalesce(CAST(b.Bestand_Leverkusen AS INT), 0)          AS leverkusen
               FROM (
                      SELECT
                        t1.LBFIRM  AS firma,
                        t1.LBARTE  AS ordernumber,
                        t1.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 = 410 OR LBLAGN = 450) AND LBSTAT = 'A'
                             GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                             HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE LBFIRM = 3 AND (LBLAGN = 810 OR LBLAGN = 850) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2
                          ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE LBFIRM = 3 AND (LBLAGN = 600 OR LBLAGN = 615 OR LBLAGN = 610) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t3
                          ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE LBFIRM = 3 AND (LBLAGN = 661 OR LBLAGN = 665 OR LBLAGN = 667) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t4
                          ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR
                        LEFT 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 = 510) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t5
                          ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR
                      UNION
                      SELECT
                        t1.LBFIRM  AS firma,
                        t1.LBARTE  AS ordernumber,
                        t1.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 = 110 OR LBLAGN = 150) AND LBSTAT = 'A'
                             GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                             HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE LBFIRM = 1 AND (LBLAGN = 700 OR LBLAGN = 710 OR LBLAGN = 750) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2
                          ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE LBFIRM = 1 AND (LBLAGN = 600 OR LBLAGN = 310 OR LBLAGN = 315) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t3
                          ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE
                                      LBFIRM = 1 AND (LBLAGN = 360 OR LBLAGN = 361 OR LBLAGN = 950 OR LBLAGN = 365) AND
                                      LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t4
                          ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR
                        LEFT JOIN (
                                    SELECT
                                      LBFIRM,
                                      LBARTE,
                                      LBAPNR,
                                      COALESCE(SUM(LBLMNG), 0) AS Bestand
                                    FROM IWMV2R1DTA.LBST00
                                    WHERE
                                      LBFIRM = 1 AND (LBLAGN = 250 OR LBLAGN = 210 OR LBLAGN = 200) AND LBSTAT = 'A'
                                    GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
                                    HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
                                  ) t5
                          ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR) b
                      WHERE CONCAT(CONCAT(b.firma, '-'), b.ordernumber) NOT IN (
                        SELECT CONCAT(CONCAT(ar.ARFIRM, '-'), ar.ARARTE) AS ordernumber
                        FROM IWMV2R1DTA.ARTS00 ar
                        WHERE ar.ARATAR = 'D'
                      )
                    ) 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_exhibition_dynsets

Validation

Validierung

Keine Validierungsfehler gespeichert.

Metadata

Metadaten

mode	-
name	iwm_articles_stock_exhibition_dynsets
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.162534+00	0