Get released product variant in AX 2012 R3

 If you want get released product variants of AX 2012 using SQL, then SQL script can help you. Here I am fetching Item Number, Product Variant Number, Style,Color, Size, Product Name. 

Please notice the join condition of InventDimCombination with EcoResProduct table. I wrote this query while doing data migration from AX 2012 R3 to D365F&O.

SELECT inventdimcombination.itemid               [ITEMNUMBER],
       ecoresproduct.displayproductnumber        [PRODUCTVARIANTNUMBER],
       inventdim.inventstyleid                   [PRODUCTSTYLEID],
       inventdim.inventcolorid                   [PRODUCTCOLORID],
       inventdim.inventsizeid                    [PRODUCTSIZEID],
       inventdim.configid                        [PRODUCTCONFIGURATIONID],
       Isnull(ecoresproducttranslation.NAME, '') [PRODUCTNAME],
       inventitemgroupitem.itemgroupid           [ITEMGROUP]
FROM   inventdimcombination
       INNER JOIN inventdim
               ON inventdim.inventdimid = inventdimcombination.inventdimid
       INNER JOIN inventtable
               ON inventtable.itemid = inventdimcombination.itemid
       INNER JOIN ecoresproduct
               ON ecoresproduct.recid =
                  inventdimcombination.distinctproductvariant
       INNER JOIN ecoresproducttranslation
               ON ecoresproducttranslation.product = ecoresproduct.recid
       INNER JOIN inventitemgroupitem
               ON inventitemgroupitem.itemid = inventtable.itemid
                  AND inventitemgroupitem.itemdataareaid =
                      inventtable.dataareaid
WHERE  inventtable.dataareaid = '@DataAreaID'