/*
Listing of Labour Products, the first three rates for location zero and the technicians assigned to them
*/
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
,
Isnull(CASE I.product_config
WHEN 0 THEN 'Product'
WHEN 1 THEN 'PACKAGE'
WHEN 2 THEN 'MACRO'
END, ' ') AS
ParentConfig,
Isnull(Cast(LR1.labour_rate AS VARCHAR(20)), ' ') AS
ParentLabourRate1
,
Isnull(Cast(LR2.labour_rate AS VARCHAR(20)), ' ') AS
ParentLabourRate2,
Isnull(Cast(LR3.labour_rate AS VARCHAR(20)), ' ') AS
ParentLabourRate3
,
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,
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
LEFT JOIN tblinvmas_labour_rates LR1
ON LR1.tblinvmasid = I.id
AND LR1.rate_no = 1
AND lr1.locn = 0
LEFT JOIN tblinvmas_labour_rates LR2
ON LR2.tblinvmasid = I.id
AND LR2.rate_no = 2
AND lr2.locn = 0
LEFT JOIN tblinvmas_labour_rates LR3
ON LR3.tblinvmasid = I.id
AND LR3.rate_no = 3
AND lr3.locn = 0
WHERE I.product_type_v41 = 1)
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],
CASE
WHEN rn = 1 THEN parentlabourrate1
ELSE ' '
END AS [Parent Labour Rate 1],
CASE
WHEN rn = 1 THEN parentlabourrate2
ELSE ' '
END AS [Parent Labour Rate 2],
CASE
WHEN rn = 1 THEN parentlabourrate3
ELSE ' '
END AS [Parent Labour Rate 3],
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;