If you want to get Product Master Size,Color,Style, ConfigID and thier names then below three SQL queries can help you get data quickly from D365FO compared to X++ development. Normally the volume of data is high when we try to export these types of data.
1) Product Master Size
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoressize.NAME [PRODUCTSIZEID],
isnull(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [SIZENAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.sizeproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoressize ecoressize
ON ecoressize.recid = ecoresproductmasterdimensionvalue.size_
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
2) Product Master Color
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecorescolor.NAME [PRODUCTCOLORID],
isnull(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [COLORNAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.colorproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecorescolor ecoResColor
ON ecorescolor.recid = ecoresproductmasterdimensionvalue.color
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
3) Product Master Style
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoresstyle.NAME [PRODUCTSTYLEID],
ecoresproducttranslation.NAME [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [STYLENAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.styleproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoresstyle ecoresstyle
ON ecoresstyle.recid = ecoresproductmasterdimensionvalue.STYLE
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
4) Product Master Config
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoresconfiguration.NAME [PRODUCTCONFIGURATIONID],
ISNULL(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [CONFIGNAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.CONFIGPRODUCTMASTER
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoresconfiguration ecoresconfiguration
ON ecoresconfiguration.recid = ecoresproductmasterdimensionvalue.configuration
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
No comments:
Post a Comment