In 2.4 & 2.5 is there a way to see a list of inventory & stock on hand? I can see stock in an excel export of Products if stock is maintained only on the variant but most our stock is in the Attribute Combinations (either Sizes, Colors or a combination of both). I've reviewed the tables as well thinking a simple SQL select would work but it looks like the attribute combinations are stored in the tables as XML. Any help would be great. Thanks!
Somone else may be able to use this. It'll do for now.
SELECT Product.Name, ProductVariant.Sku, ProductVariant.Name AS Variant, ProductVariant_ProductAttribute_Mapping.TextPrompt, ProductVariantAttributeValue.Name AS Attr, a.StockQuantity AS [Attribute Stock], ProductVariant.StockQuantity AS [Regular Stock] FROM (SELECT Id, ProductVariantId, AttributesXml, StockQuantity, AllowOutOfStockOrders, CONVERT(xml, AttributesXml) AS converted_col1 FROM ProductVariantAttributeCombination) AS a INNER JOIN ProductVariantAttributeValue ON a.converted_col1.value('(//Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value)[1] ', 'int') = ProductVariantAttributeValue.Id INNER JOIN ProductVariant_ProductAttribute_Mapping ON ProductVariantAttributeValue.ProductVariantAttributeId = ProductVariant_ProductAttribute_Mapping.Id FULL OUTER JOIN ProductVariant ON ProductVariant_ProductAttribute_Mapping.ProductVariantId = ProductVariant.Id INNER JOIN Product ON ProductVariant.ProductId = Product.Id WHERE (Product.Deleted = 0) AND (ProductVariant.Deleted = 0) ORDER BY Product.Name, ProductVariant.Sku, Variant
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.