ProTech-Online.com

Microsoft SQL code example to pull Syteline current job materials.

USE [testsite_live_App]
GO
/****** Object:  StoredProcedure [MYSCHEMA].[_chad_Excel_Addin_BOM_Current_Materials]    Script Date: 06/24/2013 23:32:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_Excel_Addin_BOM_Current_Materials]
      --@ORDER_NUM nvarchar(12)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT job.item AS Item
            ,jitem.description AS 'Item Description'
            ,jm.oper_num AS Operation
            ,jbr.wc AS WC
            ,jm.item AS Material
            ,CASE
                  WHEN EXISTS (SELECT TOP 1 1 FROM item  WHERE (item.item = jm.item))
                  THEN Matl.description
                  ELSE jm.description
            END AS 'Material Description'
            ,jm.matl_qty_conv AS Quantity
            ,jitem.u_m AS 'U/M'
            ,jm.ref_type AS Ref
            ,jm.backflush AS Backflush
            ,jm.bflush_loc AS 'Backflush Location'
      FROM jobmatl AS jm
            INNER JOIN job AS job
                  ON jm.job = job.job
                        AND jm.suffix = job.suffix
            INNER JOIN item AS jitem
                  ON jitem.item = job.item
                        AND jitem.job = job.job
            INNER JOIN jobroute AS jbr
                  ON jm.job = jbr.job
                        AND jm.suffix = jbr.suffix
                        AND jm.oper_num = jbr.oper_num
            LEFT OUTER JOIN item AS Matl
                  ON Matl.item = jm.item
      WHERE jm.suffix = '0' AND JOB.type = 'S'
      ORDER BY job.item

END