Items with all Inventory Dimensions

Submitted by Nathan Clouse on 09/20/15.

A view of all items with their product, tracking and storage dimensions plus additional info about what is enabled for each inventory dimension group.

AX Version:

AX 2012

Module:

Product Information Management

CREATE VIEW [dbo].[vw_inventItemsWithDimensions] 
AS 
  SELECT it.*, 
         erp.productmaster, 
         erp.retaitotalweight, 
         erp.variantconfigurationtechnology, 
         erp.retailcolorgroupid, 
         erp.retailsizegroupid, 
         erp.retailstylegroupid, 
         erp.instancerelationtype, 
         erp.displayproductnumber, 
         erp.searchname, 
         erp.producttype, 
         erp.pdscwproduct, 
         'storageName' = sd.NAME, 
         'storageDescription' = sd.description, 
         sd.iswarehousemandatoryenabled, 
         sd.iswarehouseprimarystockingenabled, 
         sd.iswarehousewhsenabled, 
         sd.wmslocationenabled, 
         sd.wmspalletidenabled, 
         sd.inventwarehouseenabled, 
         sd.inventsiteenabled, 
         sd.licenseplateenabled, 
         sd.inventstatusenabled, 
         'trackingName' = td.NAME, 
         'trackingDescription' = td.description, 
         td.isserialnumbercontrolenabled, 
         td.captureserial, 
         td.inventbatchenabled, 
         td.inventserialenabled, 
         td.inventgtdenabled, 
         td.inventprofileenabled, 
         td.inventownerenabled, 
         'ProductName' = pd.NAME, 
         'productDescription' = pd.description, 
         pd.configenabled, 
         pd.inventsizeenabled, 
         pd.inventcolorenabled, 
         pd.inventstyleenabled 
  FROM   inventtable it 
         INNER JOIN ecoresproduct erp 
                 ON erp.recid = it.product 
                    AND erp.partition = it.partition 
         LEFT JOIN ecoresstoragedimensiongroupitem erStorage 
                ON erStorage.itemid = it.itemid 
                   AND erStorage.itemdataareaid = 
                       it.dataareaid 
                   AND erStorage.partition = it.partition 
         LEFT JOIN ecorestrackingdimensiongroupitem 
                   erTracking 
                ON erTracking.itemid = it.itemid 
                   AND erTracking.itemdataareaid = 
                       it.dataareaid 
                   AND erTracking.partition = it.partition 
         LEFT JOIN ecoresproductdimensiongroupproduct 
                   erProduct 
                ON erProduct.product = it.product 
                   AND erProduct.partition = it.partition 
         LEFT JOIN vw_pimstoragedimensions sd 
                ON sd.recid = 
                   erStorage.storagedimensiongroup 
                   AND sd.partition = it.partition 
         LEFT JOIN vw_pimtrackingdimensions td 
                ON td.recid = 
                   erTracking.trackingdimensiongroup 
                   AND td.partition = it.partition 
         LEFT JOIN vw_pimproductdimensions pd 
                ON pd.recid = 
                   erProduct.productdimensiongroup 
                   AND pd.partition = it.partition 

go

quicktab