Microsoft SQL code example to pull Syteline invoice data.
USE [Testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_Excel_Addin_Executive_Invoices_Export] Script Date: 06/17/2013 21:06:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_Excel_Addin_Executive_Invoices_Export]
AS
BEGIN
DECLARE
@StartDate DateTime
,@EndDate DateTime
SET NOCOUNT ON;
SET @EndDate = DATEADD(d,1,(CONVERT(DATE,GETDATE())))
SET @StartDate = DATEADD(yy,-02,@EndDate)
CREATE TABLE #chad_inv_all(
inv_site nvarchar(20)
,inv_type nvarchar(20)
,item nvarchar(30)
,description nvarchar(40)
,inv_qty decimal(19,3)
,unit_price decimal(18,3)
,ext_price decimal(21,3)
,state nvarchar(5)
,country nvarchar(30)
,billto_name nvarchar(60)
,inv_date nvarchar(20)
,inv_num nvarchar(12)
,slsman nvarchar(8))
--SRO TESTSITE
INSERT INTO #chad_inv_all
SELECT 'Testsite'
,'SRO'
,itm.item
,itm.description
,itm.qty_to_bill
,itm.price
,Case When itm.bill_type = 'P' then itm.price else( itm.qty_to_bill * itm.price) End
,addr.state
,addr.country
,addrb.name
,convert(char,hdr.inv_date,101)
,itm.inv_num
,hdr.slsman
FROM [Testsite_Live_App].[dbo].fs_sro_inv_item AS itm
LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_inv_hdr AS hdr
ON itm.inv_num = hdr.inv_num
LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_matl AS mtl
ON itm.inv_num= mtl.inv_num
AND itm.inv_line = mtl.inv_line
AND itm.sro_oper = mtl.sro_oper
AND itm.trans_num = mtl.trans_num
AND mtl.type = 'A'
AND itm.trans_type = 'M'
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addr
ON hdr.cust_num = addr.cust_num
AND hdr.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addrb
ON hdr.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [Testsite_Live_App].[dbo].item AS item
ON itm.item = item.item
LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro AS sro
ON hdr.sro_num = sro.sro_num
LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_labor AS SROLabor
ON itm.inv_num = SROLabor.inv_num
AND itm.inv_line = SROLabor.inv_line
AND itm.sro_oper = SROLabor.sro_oper
AND itm.trans_num = SROLabor.trans_num
AND SROLabor.type = 'A'
AND itm.trans_type = 'L'
LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_misc AS SROMisc
ON itm.inv_num = SROMisc.inv_num
AND itm.inv_line = SROMisc.inv_line
AND itm.sro_oper = SROMisc.sro_oper
AND itm.trans_num = SROMisc.trans_num
AND SROMisc.type = 'A'
AND itm.trans_type = 'C'
--WHERE itm.inv_num = '300000022'
--itm.qty <> itm.qty_to_bill
ORDER BY itm.sro_num, itm.sro_line, itm.sro_oper, itm.trans_num
--SRO TESTSITE2
INSERT INTO #chad_inv_all
SELECT 'Testsite2'
,'SRO'
,itm.item
,itm.description
,itm.qty_to_bill
,itm.price
,Case When itm.bill_type = 'P' then itm.price else( itm.qty_to_bill * itm.price) End
,addr.state
,addr.country
,addrb.name
,convert(char,hdr.inv_date,101)
,itm.inv_num
,hdr.slsman
FROM [Testsite2_Live_App].[dbo].fs_sro_inv_item AS itm
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_hdr AS hdr
ON hdr.inv_num = itm.inv_num
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_matl AS mtl
ON itm.inv_num= mtl.inv_num
AND itm.inv_line = mtl.inv_line
AND itm.sro_oper = mtl.sro_oper
AND itm.trans_num = mtl.trans_num
AND mtl.type = 'A'
AND itm.trans_type = 'M'
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addr
ON hdr.cust_num = addr.cust_num
AND hdr.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addrb
ON hdr.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].item AS item
ON itm.item = item.item
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro AS sro
ON hdr.sro_num = sro.sro_num
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_labor AS SROLabor
ON itm.inv_num = SROLabor.inv_num
AND itm.inv_line = SROLabor.inv_line
AND itm.sro_oper = SROLabor.sro_oper
AND itm.trans_num = SROLabor.trans_num
AND SROLabor.type = 'A'
AND itm.trans_type = 'L'
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_misc AS SROMisc
ON itm.inv_num = SROMisc.inv_num
AND itm.inv_line = SROMisc.inv_line
AND itm.sro_oper = SROMisc.sro_oper
AND itm.trans_num = SROMisc.trans_num
AND SROMisc.type = 'A'
AND itm.trans_type = 'C'
--WHERE itm.inv_num = '300000022'
--itm.qty <> itm.qty_to_bill
ORDER BY itm.sro_num, itm.sro_line, itm.sro_oper, itm.trans_num
--SRO TESTSITE3
INSERT INTO #chad_inv_all
SELECT 'TESTSITE3'
,'SRO'
,itm.item
,itm.description
,itm.qty_to_bill
,itm.price
,Case When itm.bill_type = 'P' then itm.price else( itm.qty_to_bill * itm.price) End
,addr.state
,addr.country
,addrb.name
,convert(char,hdr.inv_date,101)
,itm.inv_num
,hdr.slsman
FROM [TESTSITE3_Live_App].[dbo].fs_sro_inv_item AS itm
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_hdr AS hdr
ON hdr.inv_num = itm.inv_num
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_matl AS mtl
ON itm.inv_num= mtl.inv_num
AND itm.inv_line = mtl.inv_line
AND itm.sro_oper = mtl.sro_oper
AND itm.trans_num = mtl.trans_num
AND mtl.type = 'A'
AND itm.trans_type = 'M'
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addr
ON hdr.cust_num = addr.cust_num
AND hdr.cust_seq = addr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addrb
ON hdr.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].item AS item
ON itm.item = item.item
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro AS sro
ON hdr.sro_num = sro.sro_num
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_labor AS SROLabor
ON itm.inv_num = SROLabor.inv_num
AND itm.inv_line = SROLabor.inv_line
AND itm.sro_oper = SROLabor.sro_oper
AND itm.trans_num = SROLabor.trans_num
AND SROLabor.type = 'A'
AND itm.trans_type = 'L'
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_misc AS SROMisc
ON itm.inv_num = SROMisc.inv_num
AND itm.inv_line = SROMisc.inv_line
AND itm.sro_oper = SROMisc.sro_oper
AND itm.trans_num = SROMisc.trans_num
AND SROMisc.type = 'A'
AND itm.trans_type = 'C'
--WHERE itm.inv_num = '300000022'
--itm.qty <> itm.qty_to_bill
ORDER BY itm.sro_num, itm.sro_line, itm.sro_oper, itm.trans_num
--CO Testsite
INSERT INTO #chad_inv_all
SELECT invhdr.site_ref
,'CO'
,Case
WHEN invhdr.co_num is null then 'Manual Invoice'
ELSE invitm.item
End
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,invitm.qty_invoiced
,invitm.price
,Case
WHEN invhdr.bill_type = 'P' then invitm.price
WHEN invhdr.co_num is null then invhdr.price
ELSE( invitm.qty_invoiced * invitm.price)
End
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.state
ELSE dsaddr.state
END
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.country
ELSE dsaddr.country
END
,custaddr.name
,convert(char,invhdr.inv_date,101)
,invhdr.inv_num
,invhdr.slsman
FROM [Testsite_Live_App].[dbo].inv_hdr_all AS invhdr
LEFT OUTER JOIN [Testsite_Live_App].[dbo].inv_item_all AS invitm
ON invhdr.inv_num = invitm.inv_num
AND invhdr.site_ref = invitm.site_ref
AND invhdr.inv_seq = invitm.inv_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS custaddr
ON invhdr.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [Testsite_Live_App].[dbo].item AS item
ON invitm.item = item.item
LEFT OUTER JOIN [Testsite_Live_App].[dbo].coitem AS coi
ON invitm.co_num = coi.co_num
AND invitm.co_line = coi.co_line
AND invitm.co_release = coi.co_release
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS dsaddr
ON coi.cust_num = dsaddr.cust_num
AND coi.cust_seq = dsaddr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS staddr
ON invhdr.cust_num = staddr.cust_num
AND invhdr.cust_seq = staddr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].co AS co
ON invhdr.co_num = co.co_num
WHERE invhdr.site_ref = 'Testsite'
AND invhdr.inv_date > '2012-06-01 00:00:00'
AND (invitm.co_num NOT IN(SELECT sro_hdr.sro_num
FROM [Testsite_Live_App].[dbo].fs_sro_inv_hdr AS sro_hdr)
OR invhdr.co_num is null)
ORDER BY invhdr.site_ref, invitm.co_num, invitm.co_line, invitm.co_release
--CO Testsite2
INSERT INTO #chad_inv_all
SELECT invhdr.site_ref
,'CO'
,Case
WHEN invhdr.co_num is null then 'Manual Invoice'
ELSE invitm.item
End
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,invitm.qty_invoiced
,invitm.price
,Case
WHEN invhdr.bill_type = 'P' then invitm.price
WHEN invhdr.co_num is null then invhdr.price
ELSE( invitm.qty_invoiced * invitm.price)
End
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.state
ELSE dsaddr.state
END
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.country
ELSE dsaddr.country
END
,custaddr.name
,convert(char,invhdr.inv_date,101)
,invhdr.inv_num
,invhdr.slsman
FROM [Testsite2_Live_App].[dbo].inv_hdr_all AS invhdr
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].inv_item_all AS invitm
ON invhdr.inv_num = invitm.inv_num
AND invhdr.site_ref = invitm.site_ref
AND invhdr.inv_seq = invitm.inv_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS custaddr
ON invhdr.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].item AS item
ON invitm.item = item.item
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].coitem AS coi
ON invitm.co_num = coi.co_num
AND invitm.co_line = coi.co_line
AND invitm.co_release = coi.co_release
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS dsaddr
ON coi.cust_num = dsaddr.cust_num
AND coi.cust_seq = dsaddr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS staddr
ON invhdr.cust_num = staddr.cust_num
AND invhdr.cust_seq = staddr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].co AS co
ON invhdr.co_num = co.co_num
WHERE invhdr.site_ref = 'Testsite2'
AND invhdr.inv_date > '2012-06-01 00:00:00'
AND (invitm.co_num NOT IN(SELECT sro_hdr.sro_num
FROM [Testsite2_Live_App].[dbo].fs_sro_inv_hdr AS sro_hdr)
OR invhdr.co_num is null)
ORDER BY invhdr.site_ref, invitm.co_num, invitm.co_line, invitm.co_release
--CO TESTSITE3
INSERT INTO #chad_inv_all
SELECT invhdr.site_ref
,'CO'
,Case
WHEN invhdr.co_num is null then 'Manual Invoice'
ELSE invitm.item
End
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,invitm.qty_invoiced
,invitm.price
,Case
WHEN invhdr.bill_type = 'P' then invitm.price
WHEN invhdr.co_num is null then invhdr.price
ELSE( invitm.qty_invoiced * invitm.price)
End
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.state
ELSE dsaddr.state
END
,CASE
WHEN coi.cust_num IS NULL
THEN staddr.country
ELSE dsaddr.country
END
,custaddr.name
,convert(char,invhdr.inv_date,101)
,invhdr.inv_num
,invhdr.slsman
FROM [TESTSITE3_Live_App].[dbo].inv_hdr_all AS invhdr
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].inv_item_all AS invitm
ON invhdr.inv_num = invitm.inv_num
AND invhdr.site_ref = invitm.site_ref
AND invhdr.inv_seq = invitm.inv_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS custaddr
ON invhdr.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].item AS item
ON invitm.item = item.item
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].coitem AS coi
ON invitm.co_num = coi.co_num
AND invitm.co_line = coi.co_line
AND invitm.co_release = coi.co_release
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS dsaddr
ON coi.cust_num = dsaddr.cust_num
AND coi.cust_seq = dsaddr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS staddr
ON invhdr.cust_num = staddr.cust_num
AND invhdr.cust_seq = staddr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].co AS co
ON invhdr.co_num = co.co_num
WHERE invhdr.site_ref = 'TESTSITE3'
AND invhdr.inv_date > '2012-06-01 00:00:00'
AND (invitm.co_num NOT IN(SELECT sro_hdr.sro_num
FROM [TESTSITE3_Live_App].[dbo].fs_sro_inv_hdr AS sro_hdr)
OR invhdr.co_num is null)
ORDER BY invhdr.site_ref, invitm.co_num, invitm.co_line, invitm.co_release
IF OBJECT_ID('[MYSCHEMA].chad_inv_all_export') IS NOT NULL
BEGIN
DROP TABLE [MYSCHEMA].chad_inv_all_export
END
SELECT * INTO [MYSCHEMA].chad_inv_all_export FROM #chad_inv_all
WHERE inv_date >= @StartDate AND inv_date < @EndDate
--ORDER BY order_num, order_line, order_rel, oper, trans_num
END