URGENT Help needed in extracting BOM report through SQL in Visual 10. We went live 4 days ago and

Hello - Greetings from Chicago - We went live on Visual ERP 10 4 days ago and urgently need help with a report:

I am trying to generate a multilevel BOM through SQL query - similar to the standard Bill of Material report that comes out of Visual ERP 10.0 - but needs added
fields that is not in the standard BOM report (which can be ran through menu Eng/Mfg --> Bill of Material Report --> Engineering Master)

When I ran the SQL query, I am missing 191 rows in the BOM exported through SQL query when compared to BOM exported through Visual interface.
BILL exported from Visual Menu to CSV 1862 rows BILL exported through SQL query to CSV 1671 rows

I would appreciate if someone can assist by reviewing the sql code below. We went live 4 days ago and urgently require this report and I can pay for this help if it involves taking too much time to assist. (I am new to this forum, so don't know if it is appropriate to offer compensation for the help, so forgive me if it is not appropriate). Also, if you don't have the experience, but know someone else who can, I would appreciate a referral, you can email to shah.jahan@sternpinball.com

SQL code and Screen shots are attached

[View:/cfs-file/__key/communityserver-discussions-components-files/5/SQL_2D00_Query_2D00_for_2D00_MBIL_2D00_7_2D00_2_2D00_2022.txt:320:240]

Thanks for your time!

Find more posts tagged with

VISUAL - Enterprise General Discussions

Accepted answers

All comments

d-catalano

Have you posted this in the VMIUG-TEC google group yet?

Here's a query I've been working on for an exploded BOM. I don't think it's exactly right for your situation, but it might shed some light on the issue. FYI, the calc only works for EMs set to qty 1. Also, I do not believe this includes the leg headers, which I'll add in next chance I get.

As far as WORKORDER_SUB_ID and SUBORD_WO_SUB_ID. In the requirement table, the Sub ID is the leg number that the requirement part id is in, and the subord_wo_sub_id is the leg number that the requirement IS, if it is in fact a leg of its own. You'll see in the query below that I only join when the subord wo sub id is null, because for the purposes of this query, I did not want to see the leg headers, I only wanted to get a list of materials used in the entire BOM to set safety stock quantities on them.

***Also I should mention, this query also explodes the subassemblies found within the EM, so if you only want to see what's actually listed in this specific EM, this will give you too much info.

--Exploded BOM for Legs and Subs-Details

DECLARE @EMID nvarchar(30) = 'xxx', @DESIRED_SS NVARCHAR(10) = '#'

WITH RECURS

AS
(
SELECT
1 LVL,
R.WORKORDER_BASE_ID 'Parent',
R.PART_ID,
PSV.DESCRIPTION,
PSV.ENGINEERING_MSTR,
PSV.SAFETY_STOCK_QTY 'SSQ',
PSV.QTY_ON_HAND 'OHQ',
PSV.PLANNING_LEADTIME 'LT',
R.CALC_QTY,
cast(R.CALC_QTY as decimal(10,2)) 'ExtendedCalcQty',
(PSV.UNIT_MATERIAL_COST + PSV.UNIT_LABOR_COST + PSV.UNIT_BURDEN_COST + PSV.UNIT_SERVICE_COST) 'PartCost'
--PSV.PRODUCT_CODE,

FROM
REQUIREMENT R, PART_SITE_VIEW PSV, PART_SITE_VIEW PSVM
WHERE
R.WORKORDER_TYPE='M'
AND R.WORKORDER_BASE_ID=@EMID
AND R.WORKORDER_BASE_ID=PSVM.PART_ID AND PSVM.SITE_ID= 'HVC' AND PSVM.ENGINEERING_MSTR=R.WORKORDER_LOT_ID AND R.WORKORDER_SPLIT_ID='0' AND R.SUBORD_WO_SUB_ID IS NULL AND R.PART_ID=PSV.PART_ID AND PSV.SITE_ID= 'HVC'
UNION ALL
SELECT
RE.LVL + 1 LVL,
R.WORKORDER_BASE_ID 'Parent',
R.PART_ID,
PSV.DESCRIPTION,
PSV.ENGINEERING_MSTR,
PSV.SAFETY_STOCK_QTY 'SSQ',
PSV.QTY_ON_HAND 'OHQ',
PSV.PLANNING_LEADTIME 'LT',
R.CALC_QTY,
cast(R.CALC_QTY * RE.ExtendedCalcQty as decimal(10,2))'ExtendedCalcQty',
(PSV.UNIT_MATERIAL_COST + PSV.UNIT_LABOR_COST + PSV.UNIT_BURDEN_COST + PSV.UNIT_SERVICE_COST) 'PartCost'
--PSV.PRODUCT_CODE,
FROM
RECURS RE, REQUIREMENT R, PART_SITE_VIEW PSV
WHERE
R.WORKORDER_TYPE= 'M' AND RE.PART_ID=R.WORKORDER_BASE_ID AND RE.ENGINEERING_MSTR=R.WORKORDER_LOT_ID AND R.WORKORDER_SPLIT_ID= '0' AND R.SUBORD_WO_SUB_ID IS NULL AND R.PART_ID=PSV.PART_ID AND PSV.SITE_ID= 'HVC'
)
SELECT
LVL,
RECURS.Parent,
RECURS.PART_ID,
RECURS.DESCRIPTION,
ENGINEERING_MSTR,
RECURS.SSQ,
RECURS.OHQ,
RECURS.LT,
RECURS.CALC_QTY,
RECURS.ExtendedCalcQty,
RECURS.PartCost
--RECURS.PRODUCT_CODE,
FROM
RECURS
--WHERE RECURS.ENGINEERING_MSTR IS NULL

unknown

Hi Daniel,

Thanks, I didn't see this until today. I had posted it in the developers community and was monitoring the response only there - so I missed your post. Anyway, I was able to resolve this by following:

>>>

I was able to get the query to work - and I am posting it below so the next person struggling with this has an easy reference:

Now the quantities match (BOM exported through SQL query match with the BOM exported through Visual interface). The cost doesn't match and I will post another question in the forum for the cost mismatch.

>>>>>>>>>>>>>

Declare @Base_ID Nvarchar(20) = '500-55M1-01'
Drop TABLE IF EXISTS #TempData
;WITH CTE as (
SELECT
1 as ItemLevel,
Right('00000'+CAST(ROW_NUMBER() OVER(Partition by WORKORDER_BASE_ID ORDER BY PIECE_NO) as VARCHAR(MAX)),5) as OrderItem,
--CAST(RIGHT(REPLICATE('0',5)+CAST(PIECE_NO as VARCHAR(20)),5) as VARCHAR(MAX)) as orderByField,
ROWID, WORKORDER_BASE_ID, WORKORDER_SUB_ID, SUBORD_WO_SUB_ID, PIECE_NO, PART_ID, STATUS, QTY_PER, WorkOrder_Type, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, USER_7, DIMENSIONS, USER_2
FROM REQUIREMENT R WHERE WORKORDER_BASE_ID = @Base_ID AND WORKORDER_SUB_ID=0
UNION ALL
SELECT
C.ItemLevel + 1 as ItemLevel,
C.OrderItem + '.'+Right('00000'+CAST(ROW_NUMBER() OVER(Partition By R.WORKORDER_BASE_ID ORDER BY R.PIECE_NO) as VARCHAR(MAX)),5) as OrderItem,
--C.orderByField + CAST(RIGHT(REPLICATE('0',5)+CAST(R.PIECE_NO as VARCHAR(20)),5) as VARCHAR(MAX)) as orderByField,
R.ROWID, R.WORKORDER_BASE_ID, R.WORKORDER_SUB_ID, R.SUBORD_WO_SUB_ID, R.PIECE_NO, R.PART_ID, R.STATUS, R.QTY_PER, R.WorkOrder_Type, R.WORKORDER_LOT_ID, R.WORKORDER_SPLIT_ID, R.USER_7, R.DIMENSIONS, R.USER_2
FROM REQUIREMENT R INNER JOIN CTE C ON
R.WORKORDER_SUB_ID = C.SUBORD_WO_SUB_ID
WHERE R.WORKORDER_BASE_ID = @Base_ID AND R.WORKORDER_SUB_ID<>'0'
)
SELECT *
Into #TempData
FROM CTE
--Select * from #TempData Order By OrderItem
--Drop TABLE IF EXISTS #TempData
DECLARE @COUNT BIGINT = 1
WHILE(@COUNT<=(SELECT MAX(ItemLevel) FROM #TempData))
BEGIN
;WITH CTE as (
SELECT
T.ItemLevel + 1 as ItemLevel,
T.OrderItem + '.' +Right('00000'+CAST(ROW_NUMBER() OVER(Partition by D.WORKORDER_BASE_ID ORDER BY D.PIECE_NO) as VARCHAR(MAX)),5) as OrderItem,
--CAST(RIGHT(REPLICATE('0',5)+CAST(D.PIECE_NO as VARCHAR(20)),5) as VARCHAR(MAX)) as orderByField,
D.ROWID, T.WORKORDER_BASE_ID, D.WORKORDER_SUB_ID, D.SUBORD_WO_SUB_ID, D.PIECE_NO, D.PART_ID , D.STATUS, D.QTY_PER, D.WorkOrder_Type, D.WORKORDER_LOT_ID, D.WORKORDER_SPLIT_ID, D.USER_7, D.DIMENSIONS, D.USER_2
FROM REQUIREMENT D INNER JOIN #TempData T ON D.WORKORDER_BASE_ID=T.PART_ID WHERE T.ItemLevel = @COUNT AND D.WORKORDER_SUB_ID=0
UNION ALL
SELECT
C.ItemLevel + 1 as ItemLevel,
C.OrderItem + '.'+Right('00000'+CAST(ROW_NUMBER() OVER(Partition by R.WORKORDER_BASE_ID ORDER BY R.PIECE_NO) as VARCHAR(MAX)),5) as OrderItem,
--C.orderByField + CAST(RIGHT(REPLICATE('0',5)+CAST(R.PIECE_NO as VARCHAR(20)),5) as VARCHAR(MAX)) as orderByField,
R.ROWID, T.WORKORDER_BASE_ID, R.WORKORDER_SUB_ID, R.SUBORD_WO_SUB_ID, R.PIECE_NO, R.PART_ID, R.STATUS, R.QTY_PER, R.WorkOrder_Type, R.WORKORDER_LOT_ID, R.WORKORDER_SPLIT_ID, R.USER_7, R.DIMENSIONS, R.USER_2
FROM REQUIREMENT R INNER JOIN #TempData T ON R.WORKORDER_BASE_ID=T.PART_ID INNER JOIN CTE C ON
R.WORKORDER_SUB_ID = C.SUBORD_WO_SUB_ID
WHERE R.WORKORDER_SUB_ID<>'0'
)
INSERT INTO #TempData(ItemLevel--,orderByField
, OrderItem, RowID, WORKORDER_SUB_ID, WORKORDER_BASE_ID, SUBORD_WO_SUB_ID, PART_ID, PIECE_NO, STATUS, QTY_PER, WorkOrder_Type, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, USER_7, DIMENSIONS, USER_2)
SELECT ItemLevel--,orderByField
, OrderItem, RowID, WORKORDER_SUB_ID, WORKORDER_BASE_ID, SUBORD_WO_SUB_ID, PART_ID, PIECE_NO, STATUS, QTY_PER, WorkOrder_Type, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, USER_7, DIMENSIONS, USER_2
FROM CTE --Order By OrderItem
SET @COUNT = @COUNT+1
END
Select Distinct ItemLevel, OrderItem, WORKORDER_BASE_ID, D.PART_ID, QTY_PER, PS.UNIT_MATERIAL_COST, CASE when ItemLevel = 1 THEN PS.UNIT_MATERIAL_COST * QTY_PER else NULL END as Total_Cost, P.DESCRIPTION, P.STOCK_UM
from #TempData D INNER JOIN PART P on P.ID = D.PART_ID
LEFT JOIN PART_SITE PS ON PS.PART_ID = D.PART_ID
Order By OrderItem
Drop TABLE IF EXISTS #TempData

--Select * from REQUIREMENT WHERE RowID in (57216,73646)

Copyright © 2025 Infor. All rights reserved.