List of Products and Variants

Submitted by Nathan Clouse on 09/09/15.

Provides a list of all products and variants for all companies.

AX Version:

AX 2012

Module:

Product Information Management

CREATE VIEW [dbo].[vw_pimProductVariants] 
AS 
  SELECT it.itemid, 
         it.product, 
         erp.displayproductnumber, 
         idc.inventdimid, 
         idc.retailvariantid, 
         id.configid, 
         id.inventsizeid, 
         id.inventcolorid, 
         id.inventstyleid, 
         id.dataareaid, 
         id.partition 
  FROM   inventdimcombination idc 
         INNER JOIN inventtable it 
                 ON it.itemid = idc.itemid 
                    AND it.dataareaid = idc.dataareaid 
                    AND it.partition = idc.partition 
         INNER JOIN inventdim id 
                 ON id.inventdimid = idc.inventdimid 
                    AND id.dataareaid = idc.dataareaid 
                    AND id.partition = idc.partition 
         INNER JOIN ecoresproduct erp 
                 ON erp.recid = idc.distinctproductvariant 
                    AND erp.partition = idc.partition 
                    AND erp.instancerelationtype = 3266 
  -- ecoResDistinctProductVariant tableID  
  UNION ALL 
  SELECT it.itemid, 
         it.product, 
         erp.displayproductnumber, 
         '', 
         '', 
         '', 
         '', 
         '', 
         '', 
         it.dataareaid, 
         it.partition 
  FROM   inventtable it 
         INNER JOIN ecoresproduct erp 
                 ON erp.recid = it.product 
                    AND erp.partition = it.partition 
         LEFT JOIN inventdimcombination idc 
                ON idc.itemid = it.itemid 
                   AND idc.dataareaid = it.dataareaid 
                   AND idc.partition = it.partition 
  WHERE  idc.itemid IS NULL 

go

quicktab