Microsoft SQL code example to pull Syteline customer order data.
USE [Testsite_live_App]GO
/****** Object: StoredProcedure [MYSCHEMA].[_chad_Excel_Addin_Executive_Orders_Export] Script Date: 06/17/2013 21:37:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_Excel_Addin_Executive_Orders_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)
--Sales Orders equivalent to SLInvItemAlls and FSSROInvItems
CREATE TABLE #chad_co_all(
co_site nvarchar(20)
,co_type nvarchar(20)
,order_num nvarchar(10)
,order_date nvarchar(20)
,item nvarchar(30)
,description nvarchar(40)
,order_qty decimal(19,3)
,shipped_qty decimal(19,3)
,price decimal(18,3)
,ext_price decimal(21,3)
,state nvarchar(5)
,country nvarchar(30)
,billto_name nvarchar(60)
,slsman nvarchar(8)
)
--SRO Matl_trans TESTSITE
INSERT INTO #chad_co_all
SELECT 'Testsite'
,'SRO'
,ln.sro_num
,convert(char,mtl.trans_date,101)
,mtl.item
,item.description
,mtl.matl_qty_conv
,ln.qty_shipped_conv
,mtl.price_conv
,mtl.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_matl AS mtl
ON ln.sro_num = mtl.sro_num
AND ln.sro_line = mtl.sro_line
AND mtl.type = 'A'
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [Testsite_Live_App].[dbo].item AS item
ON mtl.item = item.item
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON mtl.sro_num = itm.sro_num
-- AND mtl.sro_line = itm.sro_line
-- AND mtl.sro_oper = itm.sro_oper
-- AND mtl.trans_num = itm.trans_num
-- AND itm.trans_type = 'M'
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, mtl.sro_oper, mtl.trans_num
--SRO Matl_trans TESTSITE2
INSERT INTO #chad_co_all
SELECT 'Testsite2'
,'SRO'
,ln.sro_num
,convert(char,mtl.trans_date,101)
,mtl.item
,item.description
,mtl.matl_qty_conv
,ln.qty_shipped_conv
,mtl.price_conv
,mtl.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite2_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_matl AS mtl
ON ln.sro_num = mtl.sro_num
AND ln.sro_line = mtl.sro_line
AND mtl.type = 'A'
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].item AS item
ON mtl.item = item.item
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON mtl.sro_num = itm.sro_num
-- AND mtl.sro_line = itm.sro_line
-- AND mtl.sro_oper = itm.sro_oper
-- AND mtl.trans_num = itm.trans_num
-- AND itm.trans_type = 'M'
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, mtl.sro_oper, mtl.trans_num
--SRO Matl_trans TESTSITE3
INSERT INTO #chad_co_all
SELECT 'TESTSITE3'
,'SRO'
,ln.sro_num
,convert(char,mtl.trans_date,101)
,mtl.item
,item.description
,mtl.matl_qty_conv
,ln.qty_shipped_conv
,mtl.price_conv
,mtl.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [TESTSITE3_Live_App].[dbo].fs_sro AS sro
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_matl AS mtl
ON ln.sro_num = mtl.sro_num
AND ln.sro_line = mtl.sro_line
AND mtl.type = 'A'
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].item AS item
ON mtl.item = item.item
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON mtl.sro_num = itm.sro_num
-- AND mtl.sro_line = itm.sro_line
-- AND mtl.sro_oper = itm.sro_oper
-- AND mtl.trans_num = itm.trans_num
-- AND itm.trans_type = 'M'
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, mtl.sro_oper, mtl.trans_num
--SRO Lbr_trans Testsite
INSERT INTO #chad_co_all
SELECT 'Testsite'
,'SRO'
,ln.sro_num
,convert(char,SROLabor.trans_date,101)
,NULL --,mtl.item
,WorkCode.description --,item.description
,SROLabor.hrs_to_bill --,mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROLabor.rate --,mtl.price_conv
,SROLabor.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_labor AS SROLabor
ON ln.sro_num = SROLabor.sro_num
AND ln.sro_line = SROLabor.sro_line
AND SROLabor.type = 'A'
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROLabor.sro_num = itm.sro_num
-- AND SROLabor.sro_line = itm.sro_line
-- AND SROLabor.sro_oper = itm.sro_oper
-- AND SROLabor.trans_num = itm.trans_num
-- AND itm.trans_type = 'L'
--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_work_code as WorkCode
ON SROLabor.work_code = WorkCode.work_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROLabor.sro_oper, SROLabor.trans_num
--SRO Lbr_trans Testsite2
INSERT INTO #chad_co_all
SELECT 'Testsite2'
,'SRO'
,ln.sro_num
,convert(char,SROLabor.trans_date,101)
,NULL --,mtl.item
,WorkCode.description --,item.description
,SROLabor.hrs_to_bill --,mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROLabor.rate --,mtl.price_conv
,SROLabor.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite2_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_labor AS SROLabor
ON ln.sro_num = SROLabor.sro_num
AND ln.sro_line = SROLabor.sro_line
AND SROLabor.type = 'A'
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROLabor.sro_num = itm.sro_num
-- AND SROLabor.sro_line = itm.sro_line
-- AND SROLabor.sro_oper = itm.sro_oper
-- AND SROLabor.trans_num = itm.trans_num
-- AND itm.trans_type = 'L'
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_work_code as WorkCode
ON SROLabor.work_code = WorkCode.work_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROLabor.sro_oper, SROLabor.trans_num
--SRO Lbr_trans TESTSITE3
INSERT INTO #chad_co_all
SELECT 'TESTSITE3'
,'SRO'
,ln.sro_num
,convert(char,SROLabor.trans_date,101)
,NULL --,mtl.item
,WorkCode.description --,item.description
,SROLabor.hrs_to_bill --,mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROLabor.rate --,mtl.price_conv
,SROLabor.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [TESTSITE3_Live_App].[dbo].fs_sro AS sro
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_labor AS SROLabor
ON ln.sro_num = SROLabor.sro_num
AND ln.sro_line = SROLabor.sro_line
AND SROLabor.type = 'A'
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROLabor.sro_num = itm.sro_num
-- AND SROLabor.sro_line = itm.sro_line
-- AND SROLabor.sro_oper = itm.sro_oper
-- AND SROLabor.trans_num = itm.trans_num
-- AND itm.trans_type = 'L'
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_work_code as WorkCode
ON SROLabor.work_code = WorkCode.work_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROLabor.sro_oper, SROLabor.trans_num
--SRO Misc_trans Testsite
INSERT INTO #chad_co_all
SELECT 'Testsite'
,'SRO'
,ln.sro_num
,convert(char,SROMisc.trans_date,101)
,NULL --,mtl.item
,MiscCode.description --item.description
,SROMisc.qty --mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROMisc.price --mtl.price_conv
,SROMisc.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite_Live_App].[dbo].fs_sro_misc AS SROMisc
ON ln.sro_num = SROMisc.sro_num
AND ln.sro_line = SROMisc.sro_line
AND SROMisc.type = 'A'
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROMisc.sro_num = itm.sro_num
-- AND SROMisc.sro_line = itm.sro_line
-- AND SROMisc.sro_oper = itm.sro_oper
-- AND SROMisc.trans_num = itm.trans_num
-- AND itm.trans_type = 'C'
--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_misc_code as MiscCode
ON SROMisc.misc_code = MiscCode.misc_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROMisc.sro_oper, SROMisc.trans_num
--SRO Misc_trans Testsite2
INSERT INTO #chad_co_all
SELECT 'Testsite2'
,'SRO'
,ln.sro_num
,convert(char,SROMisc.trans_date,101)
,NULL --,mtl.item
,MiscCode.description --item.description
,SROMisc.qty --mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROMisc.price --mtl.price_conv
,SROMisc.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [Testsite2_Live_App].[dbo].fs_sro AS sro
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [Testsite2_Live_App].[dbo].fs_sro_misc AS SROMisc
ON ln.sro_num = SROMisc.sro_num
AND ln.sro_line = SROMisc.sro_line
AND SROMisc.type = 'A'
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROMisc.sro_num = itm.sro_num
-- AND SROMisc.sro_line = itm.sro_line
-- AND SROMisc.sro_oper = itm.sro_oper
-- AND SROMisc.trans_num = itm.trans_num
-- AND itm.trans_type = 'C'
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].fs_misc_code as MiscCode
ON SROMisc.misc_code = MiscCode.misc_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROMisc.sro_oper, SROMisc.trans_num
--SRO Misc_trans TESTSITE3
INSERT INTO #chad_co_all
SELECT 'TESTSITE3'
,'SRO'
,ln.sro_num
,convert(char,SROMisc.trans_date,101)
,NULL --,mtl.item
,MiscCode.description --item.description
,SROMisc.qty --mtl.matl_qty_conv
,ln.qty_shipped_conv
,SROMisc.price --mtl.price_conv
,SROMisc.extprice
,addr.state
,addr.country
,addrb.name
,sro.slsman
FROM [TESTSITE3_Live_App].[dbo].fs_sro AS sro
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_line AS ln
ON sro.sro_num = ln.sro_num
INNER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_misc AS SROMisc
ON ln.sro_num = SROMisc.sro_num
AND ln.sro_line = SROMisc.sro_line
AND SROMisc.type = 'A'
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addr
ON sro.cust_num = addr.cust_num
AND sro.cust_seq = addr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS addrb
ON sro.cust_num = addrb.cust_num
AND addrb.cust_seq = 0
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_item AS itm
-- ON SROMisc.sro_num = itm.sro_num
-- AND SROMisc.sro_line = itm.sro_line
-- AND SROMisc.sro_oper = itm.sro_oper
-- AND SROMisc.trans_num = itm.trans_num
-- AND itm.trans_type = 'C'
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_sro_inv_hdr AS hdr
-- ON itm.inv_num = hdr.inv_num
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].fs_misc_code as MiscCode
ON SROMisc.misc_code = MiscCode.misc_code
WHERE sro.sro_stat <> 'T'
ORDER BY sro.sro_num, ln.sro_line, SROMisc.sro_oper, SROMisc.trans_num
--CO Testsite
INSERT INTO #chad_co_all
SELECT 'Testsite'
,'CO'
,coi.co_num
,convert(char,co.order_date,101)
,coi.item
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,coi.qty_ordered_conv
,ISNULL(dbo.UomConvQty(coi.qty_shipped, ISNULL(dbo.Getumcf(coi.u_m, coi.item, '', 'C'), 1),'From Base'), 0)
,coi.price_conv
,ROUND((ISNULL(coi.qty_ordered_conv,0)) * (1 - (ISNULL(coi.disc,0)) / 100) * (ISNULL(coi.price_conv,0)),ISNULL(currency.places,
(SELECT cur1.places FROM [Testsite_Live_App].[dbo].currparms, [Testsite_Live_App].[dbo].currency cur1 WHERE cur1.curr_code = currparms.curr_code)))
,adr.state
,adr.country
,custaddr.name
,co.slsman
FROM [Testsite_Live_App].[dbo].co AS co
LEFT OUTER JOIN [Testsite_Live_App].[dbo].coitem AS coi
ON co.co_num = coi.co_num
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS adr
ON co.cust_num = adr.cust_num
AND co.cust_seq = adr.cust_seq
LEFT OUTER JOIN [Testsite_Live_App].[dbo].custaddr AS custaddr
ON co.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [Testsite_Live_App].[dbo].item AS item
ON coi.item = item.item
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].currency AS currency
ON adr.curr_code = currency.curr_code
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].inv_item_all AS invitm
-- ON coi.co_orig_site = invitm.site_ref
-- AND coi.co_num = invitm.co_num
-- AND coi.co_line = invitm.co_line
-- AND coi.co_release = invitm.co_release
--LEFT OUTER JOIN [Testsite_Live_App].[dbo].inv_hdr_all AS invhdr
-- ON invitm.site_ref = invhdr.site_ref
-- AND invitm.inv_num = invhdr.inv_num
WHERE co.type <> 'E'
ORDER BY coi.co_num, coi.co_line, coi.co_release
--CO Testsite2
INSERT INTO #chad_co_all
SELECT 'Testsite2'
,'CO'
,coi.co_num
,convert(char,co.order_date,101)
,coi.item
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,coi.qty_ordered_conv
,ISNULL(dbo.UomConvQty(coi.qty_shipped, ISNULL(dbo.Getumcf(coi.u_m, coi.item, '', 'C'), 1),'From Base'), 0)
,coi.price_conv
,ROUND((ISNULL(coi.qty_ordered_conv,0)) * (1 - (ISNULL(coi.disc,0)) / 100) * (ISNULL(coi.price_conv,0)),ISNULL(currency.places,
(SELECT cur1.places FROM [Testsite2_Live_App].[dbo].currparms, [Testsite2_Live_App].[dbo].currency cur1 WHERE cur1.curr_code = currparms.curr_code)))
,adr.state
,adr.country
,custaddr.name
,co.slsman
FROM [Testsite2_Live_App].[dbo].co AS co
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].coitem AS coi
ON co.co_num = coi.co_num
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS adr
ON co.cust_num = adr.cust_num
AND co.cust_seq = adr.cust_seq
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].custaddr AS custaddr
ON co.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [Testsite2_Live_App].[dbo].item AS item
ON coi.item = item.item
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].currency AS currency
ON adr.curr_code = currency.curr_code
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].inv_item_all AS invitm
-- ON coi.co_orig_site = invitm.site_ref
-- AND coi.co_num = invitm.co_num
-- AND coi.co_line = invitm.co_line
-- AND coi.co_release = invitm.co_release
--LEFT OUTER JOIN [Testsite2_Live_App].[dbo].inv_hdr_all AS invhdr
-- ON invitm.site_ref = invhdr.site_ref
-- AND invitm.inv_num = invhdr.inv_num
WHERE co.type <> 'E'
ORDER BY coi.co_num, coi.co_line, coi.co_release
--CO TESTSITE3
INSERT INTO #chad_co_all
SELECT 'TESTSITE3'
,'CO'
,coi.co_num
,convert(char,co.order_date,101)
,coi.item
,CASE
WHEN coi.item = 'MI' THEN coi.description
ELSE item.description
END
,coi.qty_ordered_conv
,ISNULL(dbo.UomConvQty(coi.qty_shipped, ISNULL(dbo.Getumcf(coi.u_m, coi.item, '', 'C'), 1),'From Base'), 0)
,coi.price_conv
,ROUND((ISNULL(coi.qty_ordered_conv,0)) * (1 - (ISNULL(coi.disc,0)) / 100) * (ISNULL(coi.price_conv,0)),ISNULL(currency.places,
(SELECT cur1.places FROM [TESTSITE3_Live_App].[dbo].currparms, [TESTSITE3_Live_App].[dbo].currency cur1 WHERE cur1.curr_code = currparms.curr_code)))
,adr.state
,adr.country
,custaddr.name
,co.slsman
FROM [TESTSITE3_Live_App].[dbo].co AS co
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].coitem AS coi
ON co.co_num = coi.co_num
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS adr
ON co.cust_num = adr.cust_num
AND co.cust_seq = adr.cust_seq
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].custaddr AS custaddr
ON co.cust_num = custaddr.cust_num
AND custaddr.cust_seq = 0
LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].item AS item
ON coi.item = item.item
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].currency AS currency
ON adr.curr_code = currency.curr_code
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].inv_item_all AS invitm
-- ON coi.co_orig_site = invitm.site_ref
-- AND coi.co_num = invitm.co_num
-- AND coi.co_line = invitm.co_line
-- AND coi.co_release = invitm.co_release
--LEFT OUTER JOIN [TESTSITE3_Live_App].[dbo].inv_hdr_all AS invhdr
-- ON invitm.site_ref = invhdr.site_ref
-- AND invitm.inv_num = invhdr.inv_num
WHERE co.type <> 'E'
ORDER BY coi.co_num, coi.co_line, coi.co_release
IF OBJECT_ID('[MYSCHEMA].chad_co_all_export') IS NOT NULL
BEGIN
DROP TABLE [MYSCHEMA].chad_co_all_export
END
SELECT * INTO [MYSCHEMA].chad_co_all_export FROM #chad_co_all
WHERE order_date >= @StartDate AND order_date < @EndDate
--ORDER BY order_num, order_line, order_rel, oper, trans_num
END