Uploading An Item Again

The following SQL script can be run on a tenant / customer and output the results in the same format as the Microsoft Excel Upload spreadsheet.

Note: The SQL script does not take into account pallet, additionals or attributes.

Copy

SELECT ItemCode, ItemName, ItemGroup, UnitDepth, UnitWidth, UnitHeight, UnitWeight, UnitBarcode, InnerQuantity, 
InnerDepth, InnerWidth, InnerHeight, InnerWeight, InnerBarcode, OuterQuantity, OuterDepth, OuterWidth, OuterHeight, 
OuterWeight, OuterBarcode
FROM (SELECT ItemCode, ItemName, ItemGroup, UnitDepth, UnitWidth, UnitHeight, UnitWeight, UnitBarcode, InnerQuantity, 
InnerDepth, InnerWidth, InnerHeight, InnerWeight, InnerBarcode, OuterQuantity, OuterDepth, OuterWidth, OuterHeight, 
OuterWeight, OuterBarcode FROM ITM
      INNER JOIN (SELECT ITM.ItemID, TradeItemDepth as UnitDepth, TradeItemWidth as UnitWidth, TradeItemHeight as 
      UnitHeight, TradeItemWeight as UnitWeight, TradeItemGTIN as UnitBarcode FROM ITM1
                 INNER JOIN ITM ON ITM.ItemID = ITM1.ItemID
                 WHERE InstanceID = :InstanceID AND TenantID = :TenantID AND TradeItemType = 'Unit') as Unit on 
                 Unit.ItemID = ITM.ItemID
      LEFT JOIN (SELECT ITM.ItemID, TradeItemQuantity as InnerQuantity, TradeItemDepth as InnerDepth, TradeItemWidth 
      as InnerWidth, TradeItemHeight as InnerHeight, TradeItemWeight as InnerWeight, TradeItemGTIN as InnerBarcode FROM ITM1
                 INNER JOIN ITM ON ITM.ItemID = ITM1.ItemID
                 WHERE InstanceID = :InstanceID AND TenantID = :TenantID AND TradeItemType = 'Inner') as INNS on 
                 INNS.ItemID = ITM.ItemID
      LEFT JOIN (SELECT ITM.ItemID, TradeItemQuantity as OuterQuantity, TradeItemDepth as OuterDepth, TradeItemWidth as 
      OuterWidth, TradeItemHeight as OuterHeight, TradeItemWeight as OuterWeight, TradeItemGTIN as OuterBarcode FROM ITM1
                 INNER JOIN ITM ON ITM.ItemID = ITM1.ItemID
                 WHERE InstanceID = :InstanceID AND TenantID = :TenantID AND TradeItemType = 'Outer') as OUTS on 
                 OUTS.ItemID = ITM.ItemID
      WHERE ITM.InstanceID = :InstanceID AND TenantID = :TenantID) as Items