I have the following Stored Procedure that returns an Indented BOM that could possibly be 11 levels deep. What I'm trying to do is calculate the cost but I am having trouble getting cost of sub assemblies.
Parent item Component Qty Cost
ABC 123A 2 $2
123A XYZ 1 $1
So in the example above 123A is a component of item ABC but 123A also has a component.
If I were to just go through and add up all the cost, they will be inflated. If I was able to multiple the qty per of a sub assembly by the qty per of its components I think I could get to where I need to be.
Is that possible?
ALTER PROCEDURE [dbo].[IndentedBOM]
-- Add the parameters for the stored procedure here
@itemno varchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select bom.[Level],bom.item_no,bom.seq_no, bom.Comp,bom.item_desc_1,bom.loc,bom.low_lvl_cd as LLC,bom.attaching_oper_no as OperNo,bom.mfg_uom as UOM,
bom.scrap_factor as Scrap,bom.activity_cd as Active,bom.stocked_fg as STK, bom.bulk_issue_fg as BI, bom.pur_or_mfg as PorM,bom.controlled_fg as CTL,
bom.backflush_fg as BF, bom.Qty, bom.country_origin, bom.std_cost, bom.extra_1
from
(
select distinct bom.*
from QIVIndentedWork _0
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVIndentedWork) _1 on _0.comp_item_no = _1.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1, loc,low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVIndentedWork) _2 on _1.Comp = _2.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _3 on _2.Comp = _3.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _4 on _3.Comp = _4.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _5 on _4.Comp = _5.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _6 on _5.Comp = _6.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _7 on _6.Comp = _7.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _8 on _7.Comp = _8.item_no
left join (select item_no, comp_item_no as Comp, item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg,qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _9 on _8.Comp = _9.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _10 on _9.Comp = _10.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _11 on _10.Comp = _11.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _12 on _11.Comp = _12.item_no
cross apply (
select right('000' + cast(_0.seq_no as varchar(3)), 3) As _0
, right('000' + cast(_1.seq_no as varchar(3)), 3) As _1
, right('000' + cast(_2.seq_no as varchar(3)), 3) As _2
, right('000' + cast(_3.seq_no as varchar(3)), 3) As _3
, right('000' + cast(_4.seq_no as varchar(3)), 3) As _4
, right('000' + cast(_5.seq_no as varchar(3)), 3) As _5
, right('000' + cast(_6.seq_no as varchar(3)), 3) As _6
, right('000' + cast(_7.seq_no as varchar(3)), 3) As _7
, right('000' + cast(_8.seq_no as varchar(3)), 3) As _8
, right('000' + cast(_9.seq_no as varchar(3)), 3) As _9
, right('000' + cast(_10.seq_no as varchar(3)), 3) As _10
, right('000' + cast(_11.seq_no as varchar(3)), 3) As _11
) seq
cross apply (
select distinct * from
(
select 1 as [Level],_0.item_no, _0.comp_item_no as Comp,_0.item_desc_1,_0.loc, _0.low_lvl_cd, _0.attaching_oper_no,_0.mfg_uom,_0.scrap_factor,_0.activity_cd,_0.stocked_fg,_0.bulk_issue_fg,_0.pur_or_mfg, _0.controlled_fg,_0.backflush_fg, _0.qty_per_par as Qty,_0.country_origin,_0.std_cost,_0.extra_1, _0.seq_no, seq._0 + _0.comp_item_no as OrderBY
union all
select 2,_1.item_no, _1.Comp,_1.item_desc_1,_1.loc, _1.low_lvl_cd, _1.attaching_oper_no,_1.mfg_uom,_1.scrap_factor,_1.activity_cd,_1.stocked_fg,_1.bulk_issue_fg,_1.pur_or_mfg, _1.controlled_fg,_1.backflush_fg, _1.Qty,_1.country_origin,_1.std_cost,_1.extra_1, _1.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp
union all
select 3,_2.item_no, _2.Comp,_2.item_desc_1,_2.loc, _2.low_lvl_cd, _2.attaching_oper_no,_2.mfg_uom,_2.scrap_factor,_2.activity_cd,_2.stocked_fg,_2.bulk_issue_fg,_2.pur_or_mfg, _2.controlled_fg,_2.backflush_fg, _2.Qty,_2.country_origin,_2.std_cost, _2.extra_1, _2.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp
union all
select 4,_3.item_no, _3.Comp,_3.item_desc_1,_3.loc, _3.low_lvl_cd, _3.attaching_oper_no,_3.mfg_uom,_3.scrap_factor,_3.activity_cd,_3.stocked_fg,_3.bulk_issue_fg,_3.pur_or_mfg, _3.controlled_fg,_3.backflush_fg, _3.Qty,_3.country_origin,_3.std_cost,_3.extra_1, _3.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp
union all
select 5,_4.item_no, _4.Comp,_4.item_desc_1,_4.loc, _4.low_lvl_cd, _4.attaching_oper_no,_4.mfg_uom,_4.scrap_factor,_4.activity_cd,_4.stocked_fg,_4.bulk_issue_fg,_4.pur_or_mfg, _4.controlled_fg,_4.backflush_fg, _4.Qty,_4.country_origin,_4.std_cost,_4.extra_1, _4.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp
union all
select 6,_5.item_no, _5.Comp,_5.item_desc_1,_5.loc, _5.low_lvl_cd, _5.attaching_oper_no,_5.mfg_uom,_5.scrap_factor,_5.activity_cd,_5.stocked_fg,_5.bulk_issue_fg,_5.pur_or_mfg, _5.controlled_fg,_5.backflush_fg, _5.Qty,_5.country_origin,_5.std_cost,_5.extra_1, _5.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp
union all
select 7,_6.item_no, _6.Comp,_6.item_desc_1,_6.loc, _6.low_lvl_cd, _6.attaching_oper_no,_6.mfg_uom,_6.scrap_factor,_6.activity_cd,_6.stocked_fg,_6.bulk_issue_fg,_6.pur_or_mfg, _6.controlled_fg,_6.backflush_fg, _6.Qty,_6.country_origin,_6.std_cost,_6.extra_1, _6.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp
union all
select 8,_7.item_no, _7.Comp,_7.item_desc_1,_7.loc, _7.low_lvl_cd, _7.attaching_oper_no,_7.mfg_uom,_7.scrap_factor,_7.activity_cd,_7.stocked_fg,_7.bulk_issue_fg,_7.pur_or_mfg, _7.controlled_fg,_7.backflush_fg, _7.Qty,_7.country_origin,_7.std_cost,_7.extra_1, _7.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp
union all
select 9, _8.item_no,_8.Comp,_8.item_desc_1,_8.loc, _8.low_lvl_cd, _8.attaching_oper_no,_8.mfg_uom,_8.scrap_factor,_8.activity_cd,_8.stocked_fg,_8.bulk_issue_fg,_8.pur_or_mfg, _8.controlled_fg,_8.backflush_fg, _8.Qty,_8.country_origin,_8.std_cost,_8.extra_1, _8.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp
union all
select 10,_9.item_no, _9.Comp,_9.item_desc_1,_9.loc, _9.low_lvl_cd, _9.attaching_oper_no,_9.mfg_uom,_9.scrap_factor,_9.activity_cd,_9.stocked_fg,_9.bulk_issue_fg,_9.pur_or_mfg, _9.controlled_fg,_9.backflush_fg, _9.Qty,_9.country_origin,_9.std_cost,_9.extra_1, _9.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp
union all
select 11, _10.item_no,_10.Comp,_10.item_desc_1,_10.loc, _10.low_lvl_cd, _10.attaching_oper_no,_10.mfg_uom,_10.scrap_factor,_10.activity_cd,_10.stocked_fg,_10.bulk_issue_fg,_10.pur_or_mfg, _10.controlled_fg,_10.backflush_fg, _10.Qty,_10.country_origin,_10.std_cost,_10.extra_1, _10.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp
union all
select 12, _11.item_no,_11.Comp,_11.item_desc_1,_11.loc, _11.low_lvl_cd, _11.attaching_oper_no,_11.mfg_uom,_11.scrap_factor,_11.activity_cd,_11.stocked_fg,_11.bulk_issue_fg,_11.pur_or_mfg, _11.controlled_fg,_11.backflush_fg, _11.Qty,_11.country_origin,_11.std_cost,_11.extra_1, _11.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp + seq._11 + _11.Comp
) bom
) bom
where _0.item_no = @itemno and bom.Comp is not null
) bom
order By bom.OrderBY
END