Microsoft SQL code example to pull Syteline job material information.
USE [testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_Excel_Addin_VJM] Script Date: 06/24/2013 23:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_Excel_Addin_VJM]
@Site nvarchar(20)
,@WC nvarchar(6)
AS
BEGIN
SET NOCOUNT ON;
IF @site = 'testsite'
BEGIN
SELECT jbr.job
,jbr.suffix
,MAX(job.qty_released)
,MAX(itm.item)
,MAX(job.description)
,MAX(itm.product_code)
,jbr.oper_num
,jm.sequence
,MAX(jbr.wc)
,MAX(ISNULL(jm.item, job.item))
,MAX(ISNULL(MYSCHEMA._chad_Excel_Addin_VJM_item_description('testsite', jm.job, jm.suffix, jm.oper_num, jm.sequence), job.description))
,MAX(jm.matl_qty_conv)
,MAX(jm.u_m)
,SUM(COALESCE(jobt.qty_complete,0))
,MAX(convert(char,jobsch.start_date,101))
,CASE
WHEN Udf.UDFVCSD1 = 1
THEN 'YES'
ELSE 'NO'
END AS part_off
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_machine_color('testsite', jbr.job) AS machine_color
,MYSCHEMA._chad_Excel_Addin_VJM_next_work_station('testsite', jbr.job, jbr.suffix, jbr.oper_num) AS next_wc
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_notes('testsite', jbr.job) AS notes
FROM [testsite_Live_App].[dbo].jobroute AS jbr
INNER JOIN [testsite_Live_App].[dbo].job AS job
ON jbr.job = job.job
AND jbr.suffix = job.suffix
LEFT OUTER JOIN [testsite_Live_App].[dbo].jobmatl AS jm
ON jbr.job = jm.job
AND jbr.suffix = jm.suffix
AND jbr.oper_num = jm.oper_num
LEFT OUTER JOIN [testsite_Live_App].[dbo].item AS itm
ON job.item = itm.item
AND (itm.job = job.job or job.type <> 'S')
LEFT OUTER JOIN [testsite_Live_App].[dbo].item AS Matl
ON Matl.item = jm.item
LEFT OUTER JOIN [testsite_Live_App].[dbo].job_sch AS jobsch
ON job.job = jobsch.job
AND job.suffix = jobsch.suffix
AND job.type = 'J' AND job.job <> ''
LEFT OUTER JOIN [testsite_Live_App].[dbo].jobtran AS jobt
ON job.job = jobt.job
AND job.suffix = jobt.suffix
LEFT OUTER JOIN [testsite_Live_App].[dbo].UserDefinedFields AS Udf
ON itm.RowPointer = Udf.RowId
AND Udf.TableName = 'item'
WHERE job.stat = 'R'
AND jbr.wc = @WC
AND jbr.complete = 0
AND MYSCHEMA._chad_Excel_Addin_VJM_completed(@Site, jbr.job, jbr.suffix, jbr.oper_num) = 0
GROUP BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
ORDER BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
END
ELSE IF @site = 'testsite2'
BEGIN
SELECT jbr.job
,jbr.suffix
,MAX(job.qty_released)
,MAX(itm.item)
,MAX(job.description)
,MAX(itm.product_code)
,jbr.oper_num
,jm.sequence
,MAX(jbr.wc)
,MAX(ISNULL(jm.item, job.item))
,MAX(ISNULL(MYSCHEMA._chad_Excel_Addin_VJM_item_description('testsite2', jm.job, jm.suffix, jm.oper_num, jm.sequence), job.description))
,MAX(jm.matl_qty_conv)
,MAX(jm.u_m)
,SUM(COALESCE(jobt.qty_complete,0))
,MAX(convert(char,jobsch.start_date,101))
,CASE
WHEN Udf.UDFVCSD1 = 1
THEN 'YES'
ELSE 'NO'
END AS part_off
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_machine_color('testsite2', jbr.job) AS machine_color
,MYSCHEMA._chad_Excel_Addin_VJM_next_work_station('testsite2', jbr.job, jbr.suffix, jbr.oper_num) AS next_wc
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_notes('testsite2', jbr.job) AS notes
FROM [testsite2_Live_App].[dbo].jobroute AS jbr
INNER JOIN [testsite2_Live_App].[dbo].job AS job
ON jbr.job = job.job
AND jbr.suffix = job.suffix
LEFT OUTER JOIN [testsite2_Live_App].[dbo].jobmatl AS jm
ON jbr.job = jm.job
AND jbr.suffix = jm.suffix
AND jbr.oper_num = jm.oper_num
LEFT OUTER JOIN [testsite2_Live_App].[dbo].item AS itm
ON job.item = itm.item
AND (itm.job = job.job or job.type <> 'S')
LEFT OUTER JOIN [testsite2_Live_App].[dbo].item AS Matl
ON Matl.item = jm.item
LEFT OUTER JOIN [testsite2_Live_App].[dbo].job_sch AS jobsch
ON job.job = jobsch.job
AND job.suffix = jobsch.suffix
AND job.type = 'J' AND job.job <> ''
LEFT OUTER JOIN [testsite2_Live_App].[dbo].jobtran AS jobt
ON job.job = jobt.job
AND job.suffix = jobt.suffix
LEFT OUTER JOIN [testsite2_Live_App].[dbo].UserDefinedFields AS Udf
ON itm.RowPointer = Udf.RowId
AND Udf.TableName = 'item'
WHERE job.stat = 'R'
AND jbr.wc = @WC
AND jbr.complete = 0
AND MYSCHEMA._chad_Excel_Addin_VJM_completed(@Site, jbr.job, jbr.suffix, jbr.oper_num) = 0
GROUP BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
ORDER BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
END
ELSE IF @site = 'testsite3'
BEGIN
SELECT jbr.job
,jbr.suffix
,MAX(job.qty_released)
,MAX(itm.item)
,MAX(job.description)
,MAX(itm.product_code)
,jbr.oper_num
,jm.sequence
,MAX(jbr.wc)
,MAX(ISNULL(jm.item, job.item))
,MAX(ISNULL(MYSCHEMA._chad_Excel_Addin_VJM_item_description('testsite3', jm.job, jm.suffix, jm.oper_num, jm.sequence), job.description))
,MAX(jm.matl_qty_conv)
,MAX(jm.u_m)
,SUM(COALESCE(jobt.qty_complete,0))
,MAX(convert(char,jobsch.start_date,101))
,CASE
WHEN Udf.UDFVCSD1 = 1
THEN 'YES'
ELSE 'NO'
END AS part_off
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_machine_color('testsite3', jbr.job) AS machine_color
,MYSCHEMA._chad_Excel_Addin_VJM_next_work_station('testsite3', jbr.job, jbr.suffix, jbr.oper_num) AS next_wc
,MYSCHEMA._chad_Excel_Addin_VJM_job_order_notes('testsite3', jbr.job) AS notes
FROM [testsite3_Live_App].[dbo].jobroute AS jbr
INNER JOIN [testsite3_Live_App].[dbo].job AS job
ON jbr.job = job.job
AND jbr.suffix = job.suffix
LEFT OUTER JOIN [testsite3_Live_App].[dbo].jobmatl AS jm
ON jbr.job = jm.job
AND jbr.suffix = jm.suffix
AND jbr.oper_num = jm.oper_num
LEFT OUTER JOIN [testsite3_Live_App].[dbo].item AS itm
ON job.item = itm.item
AND (itm.job = job.job or job.type <> 'S')
LEFT OUTER JOIN [testsite3_Live_App].[dbo].item AS Matl
ON Matl.item = jm.item
LEFT OUTER JOIN [testsite3_Live_App].[dbo].job_sch AS jobsch
ON job.job = jobsch.job
AND job.suffix = jobsch.suffix
AND job.type = 'J' AND job.job <> ''
LEFT OUTER JOIN [testsite3_Live_App].[dbo].jobtran AS jobt
ON job.job = jobt.job
AND job.suffix = jobt.suffix
LEFT OUTER JOIN [testsite3_Live_App].[dbo].UserDefinedFields AS Udf
ON itm.RowPointer = Udf.RowId
AND Udf.TableName = 'item'
WHERE job.stat = 'R'
AND jbr.wc = @WC
AND jbr.complete = 0
AND MYSCHEMA._chad_Excel_Addin_VJM_completed(@Site, jbr.job, jbr.suffix, jbr.oper_num) = 0
GROUP BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
ORDER BY jbr.job, jbr.suffix, jbr.oper_num, jm.sequence, Udf.UDFVCSD1
END
END