Engine / Editor
iwm_articles_stock
Mandant default
Pipelines
Engines
Include Mappings
Filter
Überblick
Editor
Validierung
Metadaten
Revisionen
Zurück
Altbestand
Engine bearbeiten
Bestand öffnen
Altbestand
Zum Editor
Run erst mit Status active
DRAFT
Status
engine
Typ
unitize
Runner
1
Revisionen
Editor
Engine bearbeiten
Definitionstyp
Runner-Typ
Name
Titel
Status
draft
active
archived
YAML-Inhalt
Engine: Unitize Source_Config: importEngine Target_Config: unitizeEngine Mysql_Engine: InnoDB Default_Charset: latin1 Source_Connection_Type: database Source_Database_Connection_Service: DatabaseConnectionService Source_Datapool: iwm_articles_stock Source_Primary_Key: [ORDERNUMBER, FIRMA, LBAPNR] Target_Primary_Key: [ordernumber, firma] #always primary key for target datapool. primary key of source pool can be read from the pool itself. Add_Indices: - Add_Index: Datapool: iwm_articles_stock Index_Name: idx_iwm_articles_stock Fields: FIRMA, ORDERNUMBER, LBAPNR - Add_Index: Datapool: iwm_articles_stock_inventory Index_Name: idx_iwm_articles_stock_inventory Fields: IRFIRM, IRARTE, IRAPNR Datapool_Definition: ordernumber: VARCHAR(255) NOT NULL mainnumber: VARCHAR(255) DEFAULT NULL firma: INT(11) NOT NULL anlieferart: VARCHAR(10) DEFAULT NULL stock: VARCHAR(40) DEFAULT NULL stock_inventory: VARCHAR(40) DEFAULT NULL witten: VARCHAR(40) DEFAULT NULL bottrop: VARCHAR(40) DEFAULT NULL haan: VARCHAR(40) DEFAULT NULL recklinghausen: VARCHAR(40) DEFAULT NULL leverkusen: VARCHAR(40) DEFAULT NULL lieferzeitinwochen: VARCHAR(40) DEFAULT NULL stock_local: VARCHAR(40) DEFAULT NULL #Sql: SELECT t1.firma, t1.ORDERNUMBER, t1.MAINNUMBER, t1.IRBMNG, t1.WITTEN, t1.BOTTROP, t1.RECKLINGHAUSEN, t1.HAAN, t1.LEVERKUSEN, t1.LIEFERZEITINWOCHEN FROM ( # SELECT # IF ( # IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)) IS NOT NULL, # IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)), # IF(iwm_articles_stock_inventory.IRAPNR = 0, iwm_articles_stock_inventory.IRARTE, CONCAT(iwm_articles_stock_inventory.IRARTE,'-',iwm_articles_stock_inventory.IRAPNR)) # ) as ORDERNUMBER, # IF (iwm_articles_stock.ORDERNUMBER IS NOT NULL, iwm_articles_stock.ORDERNUMBER, iwm_articles_stock_inventory.IRARTE) AS MAINNUMBER, # IF (iwm_articles_stock.FIRMA IS NOT NULL,iwm_articles_stock.FIRMA, iwm_articles_stock_inventory.IRFIRM) as firma , IF(iwm_articles_stock_inventory.IRBMNG IS NULL, 0, iwm_articles_stock_inventory.IRBMNG) as IRBMNG, # IF (iwm_articles_stock.WITTEN IS NOT NULL, iwm_articles_stock.WITTEN, 0) as WITTEN, # IF (iwm_articles_stock.BOTTROP IS NOT NULL, iwm_articles_stock.BOTTROP, 0) as BOTTROP, # IF (iwm_articles_stock.RECKLINGHAUSEN IS NOT NULL, iwm_articles_stock.RECKLINGHAUSEN, 0) as RECKLINGHAUSEN, # IF (iwm_articles_stock.HAAN IS NOT NULL, iwm_articles_stock.HAAN, 0) as HAAN, # IF (iwm_articles_stock.LEVERKUSEN IS NOT NULL, iwm_articles_stock.LEVERKUSEN, 0) as LEVERKUSEN, # IF (iwm_articles_stock_inventory.IRLIZW IS NOT NULL, iwm_articles_stock_inventory.IRLIZW DIV 7, 0) as LIEFERZEITINWOCHEN # from iwm_articles_stock # LEFT JOIN iwm_articles_stock_inventory # ON iwm_articles_stock.firma = iwm_articles_stock_inventory.IRFIRM AND iwm_articles_stock.ordernumber = iwm_articles_stock_inventory.IRARTE # UNION # SELECT IF ( # IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)) IS NOT NULL, # IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)), # IF(iwm_articles_stock_inventory.IRAPNR = 0, iwm_articles_stock_inventory.IRARTE, CONCAT(iwm_articles_stock_inventory.IRARTE,'-',iwm_articles_stock_inventory.IRAPNR)) # ) as ORDERNUMBER, # IF (iwm_articles_stock.ORDERNUMBER IS NOT NULL, iwm_articles_stock.ORDERNUMBER, iwm_articles_stock_inventory.IRARTE) AS MAINNUMBER, # IF (iwm_articles_stock.FIRMA IS NOT NULL,iwm_articles_stock.FIRMA, iwm_articles_stock_inventory.IRFIRM), IF(iwm_articles_stock_inventory.IRBMNG IS NULL, 0, iwm_articles_stock_inventory.IRBMNG) as IRBMNG, # IF (iwm_articles_stock.WITTEN IS NOT NULL, iwm_articles_stock.WITTEN, 0) as WITTEN, # IF (iwm_articles_stock.BOTTROP IS NOT NULL, iwm_articles_stock.BOTTROP, 0) as BOTTROP, # IF (iwm_articles_stock.RECKLINGHAUSEN IS NOT NULL, iwm_articles_stock.RECKLINGHAUSEN, 0) as RECKLINGHAUSEN, # IF (iwm_articles_stock.HAAN IS NOT NULL, iwm_articles_stock.HAAN, 0) as HAAN, # IF (iwm_articles_stock.LEVERKUSEN IS NOT NULL, iwm_articles_stock.LEVERKUSEN, 0) as LEVERKUSEN, # IF (iwm_articles_stock_inventory.IRLIZW IS NOT NULL, iwm_articles_stock_inventory.IRLIZW DIV 7, 0) as LIEFERZEITINWOCHEN # from iwm_articles_stock # RIGHT JOIN iwm_articles_stock_inventory # ON iwm_articles_stock.firma = iwm_articles_stock_inventory.IRFIRM AND iwm_articles_stock.ordernumber = iwm_articles_stock_inventory.IRARTE # UNION # SELECT iwm_articles_stock_dynsets.ORDERNUMBER, iwm_articles_stock_dynsets.ORDERNUMBER as MAINNUMBER, iwm_articles_stock_dynsets.FIRMA, 0 as IRBMNG, iwm_articles_stock_dynsets.WITTEN, iwm_articles_stock_dynsets.BOTTROP, iwm_articles_stock_dynsets.RECKLINGHAUSEN, iwm_articles_stock_dynsets.HAAN, iwm_articles_stock_dynsets.LEVERKUSEN, 0 AS LIEFERZEITINWOCHEN FROM iwm_articles_stock_dynsets # ORDER BY `ORDERNUMBER` ASC # ) as t1 Sql: SELECT t1.firma, t1.ORDERNUMBER, t1.MAINNUMBER, t1.IRBMNG, t1.WITTEN, t1.BOTTROP, t1.RECKLINGHAUSEN, t1.HAAN, t1.LEVERKUSEN, t1.LIEFERZEITINWOCHEN FROM ( SELECT COALESCE(iwm_articles_stock.FIRMA, iwm_articles_stock_inventory.IRFIRM) AS firma, IF ( IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)) IS NOT NULL, IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)), IF(iwm_articles_stock_inventory.IRAPNR = 0, iwm_articles_stock_inventory.IRARTE, CONCAT(iwm_articles_stock_inventory.IRARTE,'-',iwm_articles_stock_inventory.IRAPNR)) ) as ORDERNUMBER, COALESCE(iwm_articles_stock.ORDERNUMBER, iwm_articles_stock_inventory.IRARTE) AS MAINNUMBER, COALESCE(iwm_articles_stock_inventory.IRBMNG, 0) AS IRBMNG, COALESCE(iwm_articles_stock.WITTEN, 0) AS WITTEN, COALESCE(iwm_articles_stock.BOTTROP, 0) AS BOTTROP, COALESCE(iwm_articles_stock.RECKLINGHAUSEN, 0) AS RECKLINGHAUSEN, COALESCE(iwm_articles_stock.HAAN, 0) AS HAAN, COALESCE(iwm_articles_stock.LEVERKUSEN, 0) AS LEVERKUSEN, COALESCE(iwm_articles_stock_inventory.IRLIZW DIV 7, 0) AS LIEFERZEITINWOCHEN FROM iwm_articles_stock LEFT JOIN iwm_articles_stock_inventory ON iwm_articles_stock.firma = iwm_articles_stock_inventory.IRFIRM AND iwm_articles_stock.ordernumber = iwm_articles_stock_inventory.IRARTE AND iwm_articles_stock.LBAPNR = iwm_articles_stock_inventory.IRAPNR UNION DISTINCT SELECT COALESCE(iwm_articles_stock.FIRMA, iwm_articles_stock_inventory.IRFIRM) AS firma, IF ( IF(iwm_articles_stock_inventory.IRAPNR = 0, iwm_articles_stock_inventory.IRARTE, CONCAT(iwm_articles_stock_inventory.IRARTE,'-',iwm_articles_stock_inventory.IRAPNR)) IS NOT NULL, IF(iwm_articles_stock_inventory.IRAPNR = 0, iwm_articles_stock_inventory.IRARTE, CONCAT(iwm_articles_stock_inventory.IRARTE,'-',iwm_articles_stock_inventory.IRAPNR)), IF(iwm_articles_stock.LBAPNR = 0, iwm_articles_stock.ORDERNUMBER, CONCAT(iwm_articles_stock.ORDERNUMBER,'-',iwm_articles_stock.LBAPNR)) ) as ORDERNUMBER, COALESCE(iwm_articles_stock.ORDERNUMBER, iwm_articles_stock_inventory.IRARTE) AS MAINNUMBER, COALESCE(iwm_articles_stock_inventory.IRBMNG, 0) AS IRBMNG, COALESCE(iwm_articles_stock.WITTEN, 0) AS WITTEN, COALESCE(iwm_articles_stock.BOTTROP, 0) AS BOTTROP, COALESCE(iwm_articles_stock.RECKLINGHAUSEN, 0) AS RECKLINGHAUSEN, COALESCE(iwm_articles_stock.HAAN, 0) AS HAAN, COALESCE(iwm_articles_stock.LEVERKUSEN, 0) AS LEVERKUSEN, COALESCE(iwm_articles_stock_inventory.IRLIZW DIV 7, 0) AS LIEFERZEITINWOCHEN FROM iwm_articles_stock_inventory LEFT JOIN iwm_articles_stock ON iwm_articles_stock_inventory.IRFIRM = iwm_articles_stock.firma AND iwm_articles_stock_inventory.IRARTE = iwm_articles_stock.ordernumber AND iwm_articles_stock_inventory.IRAPNR = iwm_articles_stock.LBAPNR UNION DISTINCT SELECT iwm_articles_stock_dynsets.FIRMA, iwm_articles_stock_dynsets.ORDERNUMBER, iwm_articles_stock_dynsets.ORDERNUMBER AS MAINNUMBER, 0 AS IRBMNG, iwm_articles_stock_dynsets.WITTEN, iwm_articles_stock_dynsets.BOTTROP, iwm_articles_stock_dynsets.RECKLINGHAUSEN, iwm_articles_stock_dynsets.HAAN, iwm_articles_stock_dynsets.LEVERKUSEN, 0 AS LIEFERZEITINWOCHEN FROM iwm_articles_stock_dynsets ) AS t1 ORDER BY ORDERNUMBER ASC; Mapping: ordernumber: Source: t1.ORDERNUMBER mainnumber: Source: t1.MAINNUMBER firma: t1.firma anlieferart: () stock: (0) stock_inventory: Source: t1.IRBMNG Field_Adapter: - replace_empty: 0 witten: Source: t1.WITTEN bottrop: Source: t1.BOTTROP recklinghausen: Source: t1.RECKLINGHAUSEN haan: Source: t1.HAAN leverkusen: Source: t1.LEVERKUSEN lieferzeitinwochen: Source: t1.LIEFERZEITINWOCHEN stock_local: () Datapool: iwm_articles_stock
Definition aktualisieren