THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Bill of Materials – Computing Component Totals

Previous posts have shown how to do a parts explosion and a where used query. Another common query needed for Bill of Materials is a list of quantities required for all components in an assembly. The indented parts explosion is easily modified to produce grand totals for all parts in an assembly.

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ProductAssemblyID)

AS

(

    SELECT b.ComponentID,

           p.Name,

           b.PerAssemblyQty,

           b.ProductAssemblyID

    FROM Production.BillOfMaterials AS b

    INNER JOIN Production.Product p

    on b.ComponentID = p.ProductID

    WHERE b.EndDate IS NULL  -- only retrieve components still being used

    and b.ComponentID = 775  -- specify a component to explode

    UNION ALL

    SELECT b.ComponentID,

           p.Name,

           b.PerAssemblyQty,

           b.ProductAssemblyID

    FROM Production.BillOfMaterials as b

    INNER JOIN Production.Product p

    on b.ComponentID = p.ProductID

    INNER JOIN BOMcte AS cte

    ON b.ProductAssemblyID = cte.ComponentID

    WHERE b.EndDate IS NULL  -- only retrieve components still being used

)

SELECT Name, sum(PerAssemblyQty) as Total

FROM BOMcte

group by Name;

Name                             Total
-----------------------------    -----
Adjustable Race                  1.00
BB Ball Bearing                  10.00
Bearing Ball                     10.00
Blade                            2.00
Chain                            1.00
Chain Stays                      2.00
Chainring                        3.00
Chainring Bolts                  3.00
Chainring Nut                    3.00
Cone-Shaped Race                 2.00
Crown Race                       1.00
Cup-Shaped Race                  2.00
Decal 1                          2.00
Decal 2                          1.00
Down Tube                        1.00
Fork Crown                       1.00
Fork End                         2.00
Freewheel                        1.00
Front Brakes                     1.00
Front Derailleur                 1.00
Front Derailleur Cage            1.00
Front Derailleur Linkage         1.00
Guide Pulley                     1.00
Handlebar Tube                   1.00
Head Tube                        1.00
Headset Ball Bearings            8.00
HL Bottom Bracket                1.00
HL Crankarm                      2.00
HL Crankset                      1.00
HL Fork                          1.00
HL Grip Tape                     40.00
HL Headset                       1.00
HL Hub                           2.00
HL Mountain Frame - Black, 38    1.00
HL Mountain Front Wheel          1.00
HL Mountain Handlebars           1.00
HL Mountain Pedal                1.00
HL Mountain Rear Wheel           1.00
HL Mountain Rim                  2.00
HL Mountain Seat Assembly        1.00
HL Mountain Seat/Saddle          1.00
HL Mountain Tire                 2.00
HL Nipple                        72.00
HL Shell                         3.00
HL Spindle/Axle                  2.00
Keyed Washer                     1.00
Lock Nut 19                      1.00
Lock Ring                        1.00
Lower Head Race                  1.00
Metal Bar 1                      1.00
Metal Bar 2                      1.00
Metal Sheet 2                    3.00
Metal Sheet 3                    1.00
Metal Sheet 4                    1.00
Metal Sheet 5                    3.00
Metal Sheet 6                    2.00
Metal Sheet 7                    1.00
Mountain End Caps                2.00
Mountain Tire Tube               2.00
Mountain-100 Black, 38           1.00
Paint - Black                    8.00
Pinch Bolt                       4.00
Rear Brakes                      1.00
Rear Derailleur                  1.00
Rear Derailleur Cage             1.00
Reflector                        4.00
Seat Lug                         1.00
Seat Post                        1.00
Seat Stays                       4.00
Seat Tube                        1.00
Spokes                           72.00
Steerer                          1.00
Stem                             1.00
Tension Pulley                   1.00
Top Tube                         1.00

Published Saturday, November 07, 2009 4:48 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

LoriD said:

Thanx bunches for the BOM explosion code. I've modified your example and put this code into Crystal Reports. It reports all the correct parts, but now I need to add costs. My code looks something like the below, but I've been unable to figure out

1) how to show current assembly material costs based on the lower levels current material costs and

2)how to accept multiple values for a filter (I'm using multiple value parameter within Crystal Reports, but I get a blank report when I use the code below with 2+ part numbers. If I use only 1, my report is fine.)

Do you have any ideas how I can do this?

;WITH BOMcte(ULPartNum, Description, QtyPer, BOMLevel, MtlSeq, Revision, Approved, LLPartNum, ClassID, TypeCode, LastLaborCost,LastBurdenCost, LastMaterialCost, LastSubContCost, LastMtlBurCost, TotalCost, VendorID, VendorName, PullAsAsm, ViewAsAsm, Sort, Company,PartToExplode, Buyer)

AS

(

SELECT CAST(PM.PartNum as varchar(100)), P.PartDescription, PM.QtyPer,0, PM.MtlSeq, PM.RevisionNum, PR.Approved, PM.MtlPartNum, P.ClassID, P.TypeCode, PC.LastLaborCost,PC.LastBurdenCost, PC.LastMaterialCost, PC.LastSubContCost, PC.LastMtlBurCost, (PM.QtyPer * (PC.LastLaborCost + PC.LastBurdenCost + PC.LastMaterialCost +  PC.LastSubContCost + PC.LastMtlBurCost)) as TotalCost, V.VendorID, V.Name, PM.PullAsAsm, PM.ViewAsAsm, CAST('\' + PM.MtlPartNum + '-' + str(PM.MtlSeq) as nvarchar(620)), PR.Company, PR.PartNum, PP.BuyerID

   FROM PartRev as PR

INNER JOIN PartMtl as PM

on PR.Company = PM.Company and PR.PartNum = PM.PartNum and  PR.RevisionNum = PM.RevisionNum

INNER JOIN Part as P

   on PM.Company = P.Company and PM.MtlPartNum = P.PartNum

INNER Join PartPlant as PP

on P.Company = PP.Company and P.PartNum = PP.PartNum

INNER Join PartCost as PC

on PC.Company = P.Company and PC.PartNum = P.PartNum

Inner Join Vendor as V

on PP.Company = V.Company and PP.VendorNum = V.VendorNum

WHERE PR.PartNum = '{?PartNum}' and PR.Approved = '1'

   UNION ALL

   SELECT CAST(PM.PartNum as varchar(100)), P.PartDescription, PM.QtyPer, BOMLevel + 1, PM.MtlSeq, PM.RevisionNum,PR.Approved, PM.MtlPartNum, P.ClassID, P.TypeCode, PC.LastLaborCost, PC.LastBurdenCost, PC.LastMaterialCost, PC.LastSubContCost, PC.LastMtlBurCost, (PM.QtyPer * (PC.LastLaborCost + PC.LastBurdenCost + PC.LastMaterialCost + PC.LastSubContCost + PC.LastMtlBurCost)) as TotalCost, V.VendorID, V.Name, PM.PullAsAsm, PM.ViewAsAsm, CAST(cte.Sort + '\' + P.PartNum + '-' + str(PM.MtlSeq) as nvarchar(620)), PR.Company,PR.PartNum, PP.BuyerID

FROM PartMtl as PM

INNER JOIN Part as P

on PM.Company = P.Company and PM.MtlPartNum = P.PartNum

   INNER JOIN BOMcte as cte

ON PM.Company = cte.Company and PM.PartNum = cte.LLPartNum

INNER Join PartPlant as PP

on PP.Company = P.Company and PP.PartNum = P.PartNum

INNER Join PartCost as PC

on PC.Company = P.Company and PC.PartNum = P.PartNum Inner Join Vendor as V

on PP.Company = V.Company and PP.VendorNum = V.VendorNum

INNER JOIN PartRev as PR

on PM.Company = PR.Company and PM.PartNum = PR.PartNum and PM.RevisionNum = PR.RevisionNum

WHERE PR.Approved = '1'

)

SELECT BOMLevel, CAST(REPLICATE(' .', BOMLevel) + LLPartNum as nvarchar(100)) as Material, Description, QtyPer, LastLaborCost,LastBurdenCost, LastMaterialCost, LastSubContCost, TotalCost, VendorID, VendorName, ClassID, TypeCode, PullAsAsm, ViewAsAsm, Revision, Sort, Company, ULPartNum, Buyer

FROM BOMcte

ORDER BY Sort;

April 26, 2010 5:29 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement