November 14, 2014 at 8:53 am
Hi all,
Got a bit of a challenge in working with a SAGE ERP system (MAS500). I need to navigate a routing and determine all the material items a given routing contains. Is there anyone out there that has done this before, or is familiar with the nature of the relationship between the dbo.tmfRoutHead_HAI and dbo.tmfRoutDtl_HAI tables? I have a recursive CTE that appears to be missing some items, and I need to know if I've just mucked up the query or it's the data.
;WITH ROUTING_BASE AS (
SELECT VP.LocalText AS RoutTypeAsText, RHP.ParentRoutingKey, RHP.RoutingKey, CAST(0 AS decimal(16,8)) AS MatReqPc, RHP.RoutingId, RHP.ItemKey,
I.ItemID, ID.ShortDesc, CAST(NULL AS int) AS MatRoutKey, 1 AS MatLevel
FROM dbo.tmfRoutHead_HAI AS RHP
INNER JOIN dbo.vListValidationString AS VP
ON RHP.RoutType = VP.DBValue
AND VP.ColumnName = 'RoutType'
AND VP.TableName = 'tmfRoutHead_HAI'
AND VP.LocalText = 'Production'
INNER JOIN dbo.timItem AS I
ON RHP.ItemKey = I.ItemKey
INNER JOIN dbo.timItemDescription AS ID
ON I.ItemKey = ID.ItemKey
WHERE I.ItemID = 'B5012A-100'
AND RHP.ParentRoutingKey IS NULL
),
ROUTING_EXPANSION_1 AS (
SELECT *
FROM ROUTING_BASE AS RB
UNION ALL
SELECT VP.LocalText AS RoutTypeAsText, ISNULL(RHC.ParentRoutingKey, DTL.MatRoutKey) AS ParentRoutingKey, RHC.RoutingKey,
DTL.MatReqPc, RHC.RoutingId, DTL.MatItemKey, I.ItemID, ID.ShortDesc, DTL.MatRoutKey, RX.MatLevel + 1 AS MatLevel
FROM dbo.tmfRoutHead_HAI AS RHC
INNER JOIN dbo.vListValidationString AS VP
ON RHC.RoutType = VP.DBValue
AND VP.ColumnName = 'RoutType'
AND VP.TableName = 'tmfRoutHead_HAI'
INNER JOIN dbo.tmfRoutDetl_HAI AS DTL
ON RHC.RoutingKey = DTL.RoutingKey
INNER JOIN dbo.timItem AS I
ON DTL.MatItemKey = I.ItemKey
INNER JOIN dbo.timItemDescription AS ID
ON I.ItemKey = ID.ItemKey
INNER JOIN ROUTING_EXPANSION_1 AS RX
ON RHC.ParentRoutingKey = RX.RoutingKey
--OR DTL.RoutingKey = RX.MatRoutKey
WHERE RHC.ParentRoutingKey IN (SELECT RoutingKey FROM ROUTING_BASE)
),
ROUTING_EXPANSION_2 AS (
SELECT *
FROM ROUTING_EXPANSION_1
UNION ALL
SELECT VP.LocalText AS RoutTypeAsText, ISNULL(RHC.ParentRoutingKey, DTL.MatRoutKey) AS ParentRoutingKey, RHC.RoutingKey,
DTL.MatReqPc, RHC.RoutingId, DTL.MatItemKey, I.ItemID, ID.ShortDesc, DTL.MatRoutKey, RX.MatLevel + 1 AS MatLevel
FROM dbo.tmfRoutHead_HAI AS RHC
INNER JOIN dbo.vListValidationString AS VP
ON RHC.RoutType = VP.DBValue
AND VP.ColumnName = 'RoutType'
AND VP.TableName = 'tmfRoutHead_HAI'
INNER JOIN dbo.tmfRoutDetl_HAI AS DTL
ON RHC.RoutingKey = DTL.RoutingKey
INNER JOIN dbo.timItem AS I
ON DTL.MatItemKey = I.ItemKey
INNER JOIN dbo.timItemDescription AS ID
ON I.ItemKey = ID.ItemKey
INNER JOIN ROUTING_EXPANSION_2 AS RX
ON DTL.RoutingKey = RX.MatRoutKey
)
SELECT REX.*
FROM ROUTING_EXPANSION_2 AS REX
ORDER BY MatLevel
OPTION (MAXRECURSION 0)
This code uses recursion to find the immediate children of the parent routing, and then expands to the details of the children, as the labor items are the details of the parent routing. I suspect I really need a LEFT OUTER JOIN to the recursive reference in that last CTE element, but that's not allowed. Any/all help appreciated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2014 at 11:53 am
Further investigation by looking at the missing items has determined that it appears to be a data issue, as a rather larger "sub" routing is intentionally not part of the master one I was testing with, as there is too much customization that has to take place for it. My query does appear to be capturing everything else correctly. I'm still not sure if the way the routings are being used is consistent with the MAS500 design or not, but at least I now know what the cause of the problem is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply