ProTech's home page

ProTech-Online.com

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


Copyright © 2013 ProTechs-Online.com; All rights reserved.