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
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎