IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ILC_GetBillList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[ILC_GetBillList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ILC_GetBillList] (
	@AnchorItem ItemType=NULL
)
RETURNS TABLE
RETURN (
   WITH BOM (
		 job
		,suffix
		,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)
  )
  AS (
  		SELECT 
			 i.job--job
			,i.suffix--suffix
			,jr.oper_num--opernum
			,jm.[Sequence]--sequence
			,i.item--anchoritem
  			,i.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
  		FROM dbo.item i
  		JOIN dbo.jobmatl jm ON jm.job = i.job
  			AND jm.suffix = i.suffix
  		JOIN dbo.job j ON j.job = jm.job
  			AND j.suffix = jm.suffix
  			AND j.[type] = 'S'
  		JOIN dbo.jobroute jr ON jr.job = j.job
  			AND jr.suffix = j.suffix
  			AND jr.oper_num = jm.oper_num

  		WHERE i.item=ISNULL(@AnchorItem,i.item)

  		UNION ALL
  	
  		SELECT 
			 jm.job--job
			,jm.suffix--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
  		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.job j ON j.job = jm.job
  			AND j.suffix = jm.suffix
  			AND j.[type] = 'S'
  		JOIN dbo.jobroute jr ON jr.job = j.job
  			AND jr.suffix = j.suffix
  			AND jr.oper_num = jm.oper_num	
  	)
	SELECT
		 b.job AS component_job
		,b.suffix AS component_suffix
		,b.opernum AS component_opernum
		,b.[sequence] AS component_sequence
  		,b.anchoritem
  		,b.item
  		,b.component  	
  		,b.[level]
		,b.SortPath
		,b.total_matl_qty
		,b.total_matl_qty_conv
		,b.matl_qty
		,b.u_m
		,b.units
	FROM 
		BOM b
);

GO