These queries will provide you with a list of active products that still utilize XSL based product layouts. XSL product layouts were the original format used for creating product layouts for your products. In order to use them and have this information display on your printed report you had to enable the option under Setup | System Setup | Orders, Estimates, and Service Tickets labeled “Always save bitmap images of Product Layouts”. Saving these bitmap images can dramatically increase database size and can slow down the actual save time for any transactions.

Low - These are select queries only, your actual data is not altered in any way.

-- SQL that provides a list of all active products with XSL Product Layouts
SELECT ProductID, Product, ProductLayout FROM (
	SELECT 
	CG.ID AS ProductID, CG.ItemName AS Product
	,TemplateName AS ProductLayout
	FROM CUSTOMERGOODSITEM CG WITH(NOLOCK)
	LEFT JOIN PRICINGPLAN PP WITH(NOLOCK) ON PP.GoodsItemID = CG.ID
	LEFT JOIN IOTemplate IO WITH(NOLOCK) ON IO.ID = PP.InvoiceTemplateID
	WHERE CG.ClassTypeID = 12000 AND IO.TextFormat = 1
	AND CG.IsActive = 1
UNION
	SELECT 
	CG.ID AS ProductID, CG.ItemName AS Product
	,TemplateName AS ProductLayout
	FROM CUSTOMERGOODSITEM CG WITH(NOLOCK)
	LEFT JOIN PRICINGPLAN PP WITH(NOLOCK) ON PP.GoodsItemID = CG.ID
	LEFT JOIN IOTemplate IO WITH(NOLOCK) ON IO.ID = PP.WorkOrderTemplateID
	WHERE CG.ClassTypeID = 12000 AND IO.TextFormat = 1
	AND CG.IsActive = 1
) TMP1
GROUP BY ProductID, Product, ProductLayout
-- SQL that checks for any open orders using products with XSL Product Layouts
SELECT OrderNumber FROM TRANSHEADER T WITH(NOLOCK)
LEFT JOIN TransDetail TD WITH(NOLOCK) ON TD.TransHeaderID = T.ID
WHERE T.StatusID IN (1,2,3) AND TD.GoodsItemID IN (
SELECT ProductID FROM (
	SELECT 
	CG.ID AS ProductID, CG.ItemName AS Product
	,TemplateName AS ProductLayout
	FROM CUSTOMERGOODSITEM CG WITH(NOLOCK)
	LEFT JOIN PRICINGPLAN PP WITH(NOLOCK) ON PP.GoodsItemID = CG.ID
	LEFT JOIN IOTemplate IO WITH(NOLOCK) ON IO.ID = PP.InvoiceTemplateID
	WHERE CG.ClassTypeID = 12000 AND IO.TextFormat = 1
	AND CG.IsActive = 1
UNION
	SELECT 
	CG.ID AS ProductID, CG.ItemName AS Product
	,TemplateName AS ProductLayout
	FROM CUSTOMERGOODSITEM CG WITH(NOLOCK)
	LEFT JOIN PRICINGPLAN PP WITH(NOLOCK) ON PP.GoodsItemID = CG.ID
	LEFT JOIN IOTemplate IO WITH(NOLOCK) ON IO.ID = PP.WorkOrderTemplateID
	WHERE CG.ClassTypeID = 12000 AND IO.TextFormat = 1
	AND CG.IsActive = 1
) TMP1
GROUP BY ProductID
)

Contributor: Brandon R., Cyrious

Date: 11/7/2011

You could leave a comment if you were logged in.