This SQL query/report provides a simple listing of

• Product Category
• Product
• Pricing Plan
• Income Account

Add this as a SQL report for easiest access.

None. This is a selection query and no data is modified in the running of it.

SELECT 
    (SELECT ElementName FROM PricingElement 
     WHERE ID = Product.CategoryID AND ClassTypeID = Product.CategoryClassTypeID) 
    AS [Product Category],
    ItemName AS [Product Name],
    PP.PricingPlanTypeName AS [Pricing Plan],
    (SELECT AccountName FROM GLAccount WHERE ID = PP.AccountCodeID) AS [Income Account],
    (SELECT ExportAccountName FROM GLAccount WHERE ID = PP.AccountCodeID) AS [Export Name],
    Product.ID AS ProductID,
    PP.AccountCodeID AS IncomeAccountID
FROM CustomerGoodsItem Product
LEFT JOIN PricingPlan PP ON PP.GoodsItemID = Product.ID
WHERE Product.ID > 0 AND Product.ClassTypeID = 12000
ORDER BY [Product Category], [Product Name], [Pricing Plan]

• Entered : 06/2010
• Version : 3.5+