Private Function WorkOrdersRecordSet() As Recordset
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.usp_GetWorkOrdersForWeek"
        .Parameters.Append .CreateParameter("@BranchNum", adChar, adParamInput, 2, asOpenArgs(0))
        .Parameters.Append .CreateParameter("CommodityID", adInteger, adParamInput, , asOpenArgs(1))
        .Parameters.Append .CreateParameter("@StartShipDate", adDate, adParamInput, , asOpenArgs(2))
        .Parameters.Append .CreateParameter("@EndShipDate", adDate, adParamInput, , asOpenArgs(3))
        .CommandTimeout = 0
    End With
    
    Set rs = cmd.Execute
    
    
    Set WorkOrdersRecordSet = rs
    
    Set rs = Nothing
    Set cmd = Nothing
    
End Function

IF OBJECT_ID('dbo.usp_GetWorkOrdersForWeek') IS NOT NULL
  DROP PROC dbo.usp_GetWorkOrdersForWeek

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC  dbo.usp_GetWorkOrdersForWeek
(
@BranchNum CHAR(4),
@CommodityID INT,
@StartShipDate DATETIME,
@EndShipDate DATETIME
)
AS
/*
-- =============================================
-- Object Name: usp_GetWorkOrdersForWeek
-- Author:	-- Create date: 3/26/2009
-- Description:	
-- History		Date		Comments
-- =============================================

*/
BEGIN
   DECLARE @Date AS DATETIME
   --DECLARE @BranchNum AS CHAR(4)
   DECLARE @MaterialType NVARCHAR(100)
   DECLARE @MaterialForm NVARCHAR(64)       
    
   SELECT @MaterialType = MaterialType
         ,@MaterialForm = MaterialForm
     FROM dbo.Commodities AS c
    WHERE c.CommodityID = @CommodityID

   SET  @Date = DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)

  
   CREATE TABLE #WorkOrdersLoad(Wo# Char(6), OrdQty FLOAT, Part# CHAR(6), ShipDate DATETIME, Customer CHAR(35),                                  MaterialType NVARCHAR(100), MaterialForm NVARCHAR(64), Branch CHAR(2))
                                  
   CREATE INDEX #WorkOrdersLoad_Branch_NU_Nidx ON #WorkOrdersLoad (Branch)
   CREATE INDEX #WorkOrdersLoad_ShipDate_NU_Nidx ON #WorkOrdersLoad (ShipDate)
   CREATE INDEX #WorkOrdersLoad_MaterialType_NU_Nidx ON #WorkOrdersLoad (MaterialType)
   CREATE INDEX #WorkOrdersLoad_MaterialForm_NU_Nidx ON #WorkOrdersLoad (MaterialForm)
   
   INSERT INTO #WorkOrdersLoad
   SELECT DISTINCT WONumber AS Wo#
          ,FB_OPNWO_WAQTYO OrdQty
          ,FB_OPNWO_PART AS Part#
          ,CAST(CONVERT(CHAR(10),FB_OPNWO_SHIPDT,112) AS DATETIME) AS ShipDate
          ,FB_OPNWO_CUNAME AS Customer
          ,MaterialType
          ,MaterialForm
          ,FB_OPNWO_SHIPBR AS Branch
     FROM dbo.VWorkOrdersBackLog AS vwobl
   UNION ALL
   SELECT DISTINCT WONumber AS Wo#
          ,FB_OPNWO_WAQTYO OrdQty
          ,FB_OPNWO_PART AS Part#
          ,CAST(CONVERT(CHAR(10),FB_OPNWO_SHIPDT,112) AS DATETIME) AS ShipDate
          ,FB_OPNWO_CUNAME AS Customer
          ,MaterialType
          ,MaterialForm
          ,FB_OPNWO_SHIPBR AS Branch
     FROM dbo.VWorkOrdersUnscheduled AS vwou
   UNION ALL
   SELECT DISTINCT WONumber AS Wo#
          ,FB_OPNWO_WAQTYO OrdQty
          ,FB_OPNWO_PART AS Part#
          ,CAST(CONVERT(CHAR(10),FB_OPNWO_SHIPDT,112) AS DATETIME) AS ShipDate
          ,FB_OPNWO_CUNAME AS Customer
          ,MaterialType
          ,MaterialForm
          ,FB_OPNWO_SHIPBR AS Branch
     FROM dbo.VWorkOrdersScheduled AS vwos


   SELECT DISTINCT Wo#
          ,OrdQty
          ,Part#
          ,ShipDate
          ,Customer
          ,MaterialType
          ,MaterialForm
     FROM #WorkOrdersLoad
    WHERE ShipDate >= @StartShipDate
      AND ShipDate <= @EndShipDate
      AND Branch = @BranchNum
      AND MaterialType = @MaterialType
      AND MaterialForm = @MaterialForm  
    ORDER BY ShipDate  ASC
      
      DROP TABLE #WorkOrdersLoad
      
GO