In ERP solutions, a "BOM" is a bill of material where the BOM is made up of other inventory items. And, those inventory items are made up of other items. So, each of these additional inventory items represent an additional "level" of the BOM.
I, therefore, need to have T-SQL conduct "iterative" calculations, where the programming tracks the costs at each level of the BOM--no matter how many levels the BOM has.
The first attachment shows the results of the following query:
select BM_View_SL_BOMS.Assembly_Item_Key as [Assembly Item Key], IV00101.ITEMDESC as [Item Description],
BM_View_SL_BOMS.Assembly_Location as [Assembly Location], BM_View_SL_BOMS.Entry_Date as [Entry Date],
BM_View_SL_BOMS.Status as [Status], BM_View_SL_BOMS.BOMUOM as [BOMUOM], BM_View_SL_BOMS.Material_Cost as [Total Material Cost],
BM_View_SL_BOMS.Labor_Cost as [Total Labor Cost], BM_View_SL_BOMS.Overhead_Cost as [Total Overhead Cost],
IV00101.STNDCOST as [Standard Cost], IV00101.CURRCOST as [Current Cost]
from BM_View_SL_BOMS INNER JOIN
BM_View_SL_BOMS.Assembly_Item_Key = IV00101.ITEMNMBR
where BM_View_SL_BOMS.Assembly_Item_Key like '70-%'
The three fields called "Total Material Cost", "Total Labor Cost", and "Total Overhead Cost" are wrong. Each of those fields needs to conduct the calculations shown in cells B34, B32, and B33 found within the attached Excel spreadsheet called "Cost Breakdown".
Each of the six attached result sets that are shown in this case are derived from the following six "select" scripts:
--material costs and quantity of each "make" item
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '70-1010-BRX0014'
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '1001311'
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '1001258'
--labor and overhead costs
select * from BM_View_SL_BOMS where Assembly_Item_Key = '70-1010-BRX0014'
select * from BM_View_SL_BOMS where Assembly_Item_Key = '1001311'
select * from BM_View_SL_BOMS where Assembly_Item_Key = '1001258'
The "Make_Buy" column of the first row of the first script tells you that item 1001311 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001311 has a Quantity of 1 for BOM 70-1010-BRX0014.
Likewise, The "Make_Buy" column of the first row of the second script tells you that item 1001258 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001258 has a Quantity of 667.63 for 1001311.
The final three scripts show the Labor and Overhead Costs of 70-1010-BRX0014, 1001311, 1001258.
Rows 2 - 5 and column Item_Key of the first script show the "Buy" items that make up 70-1010-BRX0014. These items are seen, as well, in cells A5 - A8 of the attached spreadsheet.
Rows 2 - 3 and column Item_Key of the second script show the "Buy" items that make up 1001311. These items are seen, too, in cells B17 - B18 of the attached spreadsheet.
Rows 1 - 4 and column Item_Key of the third script show the "Buy" items that make up 1001258. These items are seen, additionally, in cells C27 - C30 of the attached spreadsheet.
These first three scripts, then, show Material Costs. ("Buy" items make up Material Costs.)
Again, I need for T-SQL to conduct the calculations that are shown within cells B32, B33, and B34 of the attached Excel spreadsheet and place those results into "Total Labor Costs", "Total Overhead Costs", and "Total Material Costs", respectively, for the first script (query) that I mentioned in this case.
How do I go about producing such syntax?
Thank you! Much appreciated!