Provides a list of all packages and macros in the database
including their components, accessories and alternatives
| You can execute this query using Excel Query Builder in RentalPoint..... |  |
WITH CTE AS (
SELECT
ISNULL(I.groupfld,' ') AS ParentGroup,
ISNULL(PG.group_descv6,' ') AS ParentGroupDesc,
ISNULL(I.category,' ') AS ParentCategory,
ISNULL(PC.cat_descv6,' ') AS ParentCategoryDesc,
ISNULL(I.subcategory,' ') AS ParentSubCategory,
ISNULL(PSC.cat_descv6,' ') AS ParentSubCategoryDesc,
ISNULL(B.parent_code,' ') AS ParentCode,
ISNULL(I.descriptionv6,' ') AS ParentDescription,
-- FIXED: Proper CASE + alias
ISNULL(
CASE I.product_config
WHEN 0 THEN 'Product'
WHEN 1 THEN 'PACKAGE'
WHEN 2 THEN 'MACRO'
END, ' '
) AS ParentConfig,
ISNULL(I2.groupfld,' ') AS ChildGroup,
ISNULL(CG.group_descv6,' ') AS ChildGroupDesc,
ISNULL(I2.category,' ') AS ChildCategory,
ISNULL(CC.cat_descv6,' ') AS ChildCategoryDesc,
ISNULL(I2.subcategory,' ') AS ChildSubCategory,
ISNULL(CSC.cat_descv6,' ') AS ChildSubCategoryDesc,
ISNULL(B.product_code,' ') AS ChildCode,
ISNULL(I2.descriptionv6,' ') AS ChildDescription,
CASE B.variable_part
WHEN 0 THEN 'Component'
WHEN 1 THEN 'Accessory'
WHEN 2 THEN 'Alternative'
END AS ChildDefinedAs,
B.SelectComp AS ChildSelectComp,
-- FIXED: numeric → varchar before ISNULL
ISNULL(CAST(B.qty_v5 AS varchar(20)),' ') AS ChildQty,
ISNULL(
CAST(I2.product_config AS varchar(10)),
' '
) AS ChildConfig,
ROW_NUMBER() OVER (
PARTITION BY B.parent_code
ORDER BY B.sub_seq_no
) AS rn
FROM tblbill B
LEFT JOIN tblinvmas I ON I.product_code = B.parent_code
LEFT JOIN tblgroup PG ON PG.group_code = I.groupfld
LEFT JOIN tblcategory PC ON PC.category_code = I.category
LEFT JOIN tblcategory PSC ON PSC.category_code = I.subcategory
LEFT JOIN tblinvmas I2 ON I2.product_code = B.product_code
LEFT JOIN tblgroup CG ON CG.group_code = I2.groupfld
LEFT JOIN tblcategory CC ON CC.category_code = I2.category
LEFT JOIN tblcategory CSC ON CSC.category_code = I2.subcategory
WHERE I.product_type_v41 = 0
AND I.product_config IN (1,2)
)
SELECT
CASE WHEN rn = 1 THEN ParentGroup ELSE ' ' END AS [Parent Group],
CASE WHEN rn = 1 THEN ParentGroupDesc ELSE ' ' END AS [Parent Group Description],
CASE WHEN rn = 1 THEN ParentCategory ELSE ' ' END AS [Parent Category],
CASE WHEN rn = 1 THEN ParentCategoryDesc ELSE ' ' END AS [Parent Category Description],
CASE WHEN rn = 1 THEN ParentSubCategory ELSE ' ' END AS [Parent Sub Category],
CASE WHEN rn = 1 THEN ParentSubCategoryDesc ELSE ' ' END AS [Parent Sub Category Description],
CASE WHEN rn = 1 THEN ParentCode ELSE ' ' END AS [Parent Code],
CASE WHEN rn = 1 THEN ParentDescription ELSE ' ' END AS [Parent Description],
CASE WHEN rn = 1 THEN ParentConfig ELSE ' ' END AS [Parent Config],
ChildGroup AS [Child Group],
ChildGroupDesc AS [Child Group Description],
ChildCategory AS [Child Category],
ChildCategoryDesc AS [Child Category Description],
ChildSubCategory AS [Child Sub Category],
ChildSubCategoryDesc AS [Child Sub Category Description],
ChildCode AS [Child Code],
ChildDefinedAs AS [Type],
ChildSelectComp AS [Optional],
ChildDescription AS [Child Description],
ChildQty AS [Child Qty],
ChildConfig AS [Product Config]
FROM CTE
ORDER BY ParentGroup, ParentCategory, ParentSubCategory, ParentCode;