Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement