bluebunny72

ILC_GetBillList table valued function

Sep 25th, 2020
2,049
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 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'))
  2.     DROP FUNCTION [dbo].[ILC_GetBillList]
  3. GO
  4.  
  5. SET ANSI_NULLS ON
  6. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9.  
  10. CREATE FUNCTION [dbo].[ILC_GetBillList] (
  11.     @AnchorItem ItemType=NULL
  12. )
  13. RETURNS TABLE
  14. RETURN (
  15.    WITH BOM (
  16.          job
  17.         ,suffix
  18.         ,opernum
  19.         ,[sequence]
  20.         ,anchoritem
  21.         ,item
  22.         ,component     
  23.         ,[level]
  24.         ,SortPath
  25.         ,total_matl_qty
  26.         ,total_matl_qty_conv
  27.         ,matl_qty
  28.         ,u_m
  29.         ,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)
  30.   )
  31.   AS (
  32.         SELECT
  33.              i.job--job
  34.             ,i.suffix--suffix
  35.             ,jr.oper_num--opernum
  36.             ,jm.[Sequence]--sequence
  37.             ,i.item--anchoritem
  38.             ,i.item--item
  39.             ,jm.item--component    
  40.             ,0 AS [level]--level
  41.             ,CAST('\' + RIGHT('000'+CONVERT(VARCHAR(3),jm.[sequence]),3) AS NVARCHAR(254)) --sortpath
  42.             ,jm.matl_qty as total_matl_qty
  43.             ,jm.matl_qty_conv as total_matl_qty_conv
  44.             ,jm.matl_qty
  45.             ,jm.u_m
  46.             ,jm.units
  47.         FROM dbo.item i
  48.         JOIN dbo.jobmatl jm ON jm.job = i.job
  49.             AND jm.suffix = i.suffix
  50.         JOIN dbo.job j ON j.job = jm.job
  51.             AND j.suffix = jm.suffix
  52.             AND j.[type] = 'S'
  53.         JOIN dbo.jobroute jr ON jr.job = j.job
  54.             AND jr.suffix = j.suffix
  55.             AND jr.oper_num = jm.oper_num
  56.  
  57.         WHERE i.item=ISNULL(@AnchorItem,i.item)
  58.  
  59.         UNION ALL
  60.    
  61.         SELECT
  62.              jm.job--job
  63.             ,jm.suffix--suffix
  64.             ,jr.oper_num--opernum
  65.             ,jm.[Sequence]--sequence
  66.             ,b.anchoritem--anchoritem
  67.             ,i.item--item
  68.             ,jm.item--component
  69.             ,(b.[level] + 1) AS [level]--level
  70.             ,CAST(b.SortPath + '\' + RIGHT('000'+CONVERT(VARCHAR(3),jm.[sequence]),3) AS NVARCHAR(254))--sortpath
  71.             ,CAST((b.total_matl_qty * jm.matl_qty) as decimal(18,8)) as total_matl_qty
  72.             ,CAST((b.total_matl_qty_conv * jm.matl_qty) as decimal(18,8)) as total_matl_qty_conv
  73.             ,jm.matl_qty
  74.             ,jm.u_m
  75.             ,jm.units
  76.         FROM BOM b
  77.         JOIN dbo.item i ON i.item = b.component
  78.         JOIN dbo.jobmatl jm ON jm.job = i.job
  79.             AND jm.suffix = i.suffix
  80.         JOIN dbo.job j ON j.job = jm.job
  81.             AND j.suffix = jm.suffix
  82.             AND j.[type] = 'S'
  83.         JOIN dbo.jobroute jr ON jr.job = j.job
  84.             AND jr.suffix = j.suffix
  85.             AND jr.oper_num = jm.oper_num  
  86.     )
  87.     SELECT
  88.          b.job AS component_job
  89.         ,b.suffix AS component_suffix
  90.         ,b.opernum AS component_opernum
  91.         ,b.[sequence] AS component_sequence
  92.         ,b.anchoritem
  93.         ,b.item
  94.         ,b.component   
  95.         ,b.[level]
  96.         ,b.SortPath
  97.         ,b.total_matl_qty
  98.         ,b.total_matl_qty_conv
  99.         ,b.matl_qty
  100.         ,b.u_m
  101.         ,b.units
  102.     FROM
  103.         BOM b
  104. );
  105.  
  106. GO
RAW Paste Data