Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ;WITH
- BOM (
- job_a
- ,suffix_a
- ,job_c
- ,suffix_c
- ,opernum
- ,[SEQUENCE]
- ,anchoritem
- ,item
- ,component
- ,[level]
- ,SortPath
- ,total_matl_qty
- ,total_matl_qty_conv
- ,matl_qty
- ,u_m
- ,units --U = per unit L = per lot (Basically if U then you need that matl_qty for every 1 unit you make. If L then it doesn't matter how many you make it will always cost the same on time qty of matl_qty)
- ,item_path
- )
- AS (
- SELECT
- j.job AS job_a--job
- ,j.suffix AS suffix_a --suffix
- ,j.job AS job_c--job
- ,j.suffix AS suffix_c--suffix
- ,jr.oper_num--opernum
- ,jm.[SEQUENCE]--sequence
- ,j.item--anchoritem
- ,j.item--item
- ,jm.item--component
- ,0 AS [level]--level
- ,CAST('\' + RIGHT('000'+CONVERT(VARCHAR(3),jm.[sequence]),3) AS NVARCHAR(254)) --sortpath
- ,jm.matl_qty as total_matl_qty
- ,jm.matl_qty_conv as total_matl_qty_conv
- ,jm.matl_qty
- ,jm.u_m
- ,jm.units
- ,cast(j.item as varchar) as item_path
- FROM dbo.job j
- JOIN dbo.jobmatl jm ON jm.job = j.job
- AND jm.suffix = j.suffix
- JOIN dbo.jobroute jr ON jr.job = j.job
- AND jr.suffix = j.suffix
- AND jr.oper_num = jm.oper_num
- WHERE j.stat = 'R'
- UNION ALL
- SELECT
- b.job_a
- ,b.suffix_a
- ,jm.job as job_c--job
- ,jm.suffix as suffix_c --suffix
- ,jr.oper_num--opernum
- ,jm.[Sequence]--sequence
- ,b.anchoritem--anchoritem
- ,i.item--item
- ,jm.item--component
- ,(b.[level] + 1) AS [level]--level
- ,CAST(b.SortPath + '\' + RIGHT('000'+CONVERT(VARCHAR(3),jm.[sequence]),3) AS NVARCHAR(254))--sortpath
- ,CAST((b.total_matl_qty * jm.matl_qty) as decimal(18,8)) as total_matl_qty
- ,CAST((b.total_matl_qty_conv * jm.matl_qty) as decimal(18,8)) as total_matl_qty_conv
- ,jm.matl_qty
- ,jm.u_m
- ,jm.units
- ,Cast((ISNULL(b.item_path,'') + '>' + i.item) as varchar) as item_path
- FROM BOM b
- JOIN dbo.item i ON i.item = b.component
- JOIN dbo.jobmatl jm ON jm.job = i.job
- AND jm.suffix = i.suffix
- JOIN dbo.jobroute jr ON jr.job = jm.job
- AND jr.suffix = jm.suffix
- AND jr.oper_num = jm.oper_num
- )
- SELECT
- b.job_a AS job
- ,b.suffix_a AS suffix
- ,b.anchoritem as top_level_item
- ,b.item
- ,b.[level]
- ,b.item_path
- FROM
- BOM b
- left join
- job j
- on
- b.job_a = j.job
- and
- b.suffix_a = j.suffix
- where
- b.anchoritem = b.component and (j.rework = 1 and level > 0 or j.rework = 0)
- ;WITH
- BOM (
- job_c
- ,suffix_c
- ,opernum
- ,[sequence]
- ,anchoritem
- ,item
- ,component
- ,[level]
- ,total_matl_qty
- ,total_matl_qty_conv
- ,matl_qty
- ,u_m
- ,units --U = per unit L = per lot (Basically if U then you need that matl_qty for every 1 unit you make. If L then it doesn't matter how many you make it will always cost the same ON TIME qty OF matl_qty)
- ,item_path
- )
- AS (
- SELECT
- i.job AS job_c--job
- ,i.suffix AS suffix_c--suffix
- ,jr.oper_num--opernum
- ,jm.[SEQUENCE]--sequence
- ,i.item--anchoritem
- ,i.item--item
- ,jm.item--component
- ,0 AS [level]--level
- ,jm.matl_qty AS total_matl_qty
- ,jm.matl_qty_conv AS total_matl_qty_conv
- ,jm.matl_qty
- ,jm.u_m
- ,jm.units
- ,CAST(i.item AS VARCHAR) AS item_path
- FROM dbo.item i
- JOIN dbo.jobmatl jm ON jm.job = i.job
- AND jm.suffix = i.suffix
- JOIN dbo.jobroute jr ON jr.job = i.job
- AND jr.suffix = i.suffix
- AND jr.oper_num = jm.oper_num
- WHERE i.stat IN ('A','S')
- UNION ALL
- SELECT
- jm.job AS job_c--job
- ,jm.suffix AS suffix_c --suffix
- ,jr.oper_num--opernum
- ,jm.[SEQUENCE]--sequence
- ,b.anchoritem--anchoritem
- ,i.item--item
- ,jm.item--component
- ,(b.[level] + 1) AS [level]--level
- ,CAST((b.total_matl_qty * jm.matl_qty) AS DECIMAL(18,8)) AS total_matl_qty
- ,CAST((b.total_matl_qty_conv * jm.matl_qty) AS DECIMAL(18,8)) AS total_matl_qty_conv
- ,jm.matl_qty
- ,jm.u_m
- ,jm.units
- ,CAST((ISNULL(b.item_path,'') + '>' + i.item) AS VARCHAR) AS item_path
- FROM BOM b
- JOIN dbo.item i ON i.item = b.component
- JOIN dbo.jobmatl jm ON jm.job = i.job
- AND jm.suffix = i.suffix
- JOIN dbo.jobroute jr ON jr.job = jm.job
- AND jr.suffix = jm.suffix
- AND jr.oper_num = jm.oper_num
- )
- SELECT
- b.anchoritem AS top_level_item
- ,b.item
- ,b.[level]
- ,b.item_path
- FROM
- BOM b
- WHERE
- b.anchoritem = b.component
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement