Hi
Looking a wee bit of help if possible please.
I'm trying to price products based on their bill of materials.
I've managed to explode the BOM ok but I'm stuck as to how to roll-up Component prices through the BOM levels.
This is where I've got to:
with cte_lcur as
(select a.Currency as cur,
a.Rate as fx
from [dbo].[tblcurrency] a
join
(select Currency as Cur,
max(CDate) as CDate
from [dbo].[tblcurrency]
group by Currency) b on a.Currency=b.Cur and a.CDate=b.CDate
),
cte_lpo as
(select a.[ItemCode] as ItemCode,
c.Currency as Cur,
a.PONumber as PONum,
a.Price as SuppPrice,
e.fx as FX,
cast(a.Price/iif(d.PCurrency='GBP',1,e.fx) as decimal(15,5)) as Price,
row_number() over (partition by a.ItemCode order by a.[PONumber] desc) as rnum
from [Company001].[dbo].[PODetails] a
join [Company001].[dbo].[POHeader] b on a.[PONumber]=b.[PONumber]
join [Company001].[dbo].[Supplier] c on b.Supplier=c.SageCode
join [dbo].[tblcurxref] d on c.[Currency]=d.[PCurrency]
left join cte_lcur e on d.Currency=e.Cur
where a.[ItemCode] is not null
),
BOMCost
AS
(SELECT a.Parent_Code as SKU,
a.Parent_Code as ItemCode,
a.Component_Code as ComponentCode,
a.[QuantityPer] as QtyPer,
c.Price as Price,
0 as BOMLevel
FROM [Company001].[dbo].[BoM_Detail] AS a
left join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1
WHERE a.parent_code = '662056' and a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR'
UNION ALL
SELECT
b.SKU,
a.Parent_Code,
a.Component_Code,
a.[QuantityPer],
c.Price as Price,
(BOMLevel + 1) as BOMLevel
FROM [Company001].[dbo].[BoM_Detail] AS a
join BOMCost b on a.Parent_Code=b.ComponentCode
join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1
where a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR'
)
SELECT SKU, ItemCode,ComponentCode, QtyPer, Price,BOMLevel
FROM BOMCost
order by 1,6,2
The first 2 CTEs grab the last purchase order prices and converts to GBP.
The output from the above is:
In the above example the product has 3 BOM levels.
I need to roll cost of ItemCode 020001 from BOM Level 2 into ComponentCode 020001 in BOM Level 1, calculate the total cost of BOM Level 1 and then roll that up to BOM Level 0 for ComponentCode 040006. Hope you follow that. The end result should look like this.
Any help most appreciated.
Thanks
Jake
