Recursive Bill of Materials

   ;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