querying summarized bill of materials

Posted By wishingforsql over 5 years ago

For some reporting purposes I need to normalize the bill of materials exactly how it shows in the BM_SummarizedReportWRK report into a SQL table. How do I do this? I've been attempting to backwards engineer from the regular and accessible Bill Of Materials tables and the queries are getting a pretty certain shade of ugly since our BOM has things like

Product A is made up of:

Component A

Component B

Product B

Component A

Component D

Product C

Component E

Product B

etc

So, if I good just work with the data in table format / query off of the WRK table, my life would be a lot easier! Obviously Sage 100 isn't designed to operate that way and right now I'm failing to find a workaround.

Sage 4.5 ProvideX -- I'll be using SSIS to get the data out of Sage and into my SQL server (we'll be upgrading to 100c SQL later this year, but I suspect I'll still have the same problem).

0 Sage100Reports over 5 years ago

You will need to bring the BM_BillHeader and BM_BillDetail tables into SQL and then create a stored procedure with a cursor to go through the Bill of Material tables and find all the components. Your cursor will need to be nested based upon the maximum number of levels. The stored procedure will then populate the Summary WRK table you have in SQL. This will be the easiest way to get the data into the format you want for SSIS.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 5 years ago

I've done this type of thing with SQL Views and UNION ALL statements to bring the nested components together... (pulling the data into SQL Express when working with a ProvideX system).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 100

querying summarized bill of materials

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING