ALTER PROCEDURE [dbo].[GetOrdersWithFlags](@DepartmentCode NVARCHAR(5))

AS

 CREATE TABLE #towf(DepartmentCode NVARCHAR(3),

                            MoStatus NVARCHAR(1),

                              OrderNumber NVARCHAR(6),

                              RequiredShipDate DECIMAL(8, 0),

                              HoldCode NVARCHAR(1),

                              CreditExceptionFlag NVARCHAR(1),

                              AllowBackorderFlag NVARCHAR(1),

                              ShipToCustomer NVARCHAR(11),

                              QueueSeq DECIMAL(5,0))

 

 CREATE CLUSTERED INDEX i1 ON #towf(OrderNumber)                              

 

 INSERT INTO #towf(DepartmentCode,

                           MoStatus,

                           OrderNumber,

                           RequiredShipDate,

                           HoldCode,

                           CreditExceptionFlag,

                           AllowBackorderFlag,

                           ShipToCustomer,

                           QueueSeq) 

 SELECT     DepartmentCode,

        MoStatus,

        OrderNumber,

        RequiredShipDate,

        HoldCode,

        CreditExceptionFlag,

        AllowBackorderFlag,

        ShipToCustomer,

        QueueSeq

 FROM [tblOrders]

 WHERE QueueFlag = 'P' 

 AND   DepartmentCode = @DepartmentCode 

 OPTION (FAST 1)

 

 DECLARE @ColName  NVARCHAR(5)

 DECLARE @AttnCode DECIMAL(2, 0)

 

 DECLARE AllFlagsCursor CURSOR FAST_FORWARD FOR

  SELECT ShortDesc,AttnCode FROM [tblMasterFlags] ORDER BY DisplaySeq ASC

  OPTION (FAST 1)

  

 OPEN AllFlagsCursor

 

 BEGIN TRANSACTION

 

 FETCH NEXT FROM AllFlagsCursor INTO @ColName,@AttnCode

 WHILE @@FETCH_STATUS <> -1

  BEGIN

   EXEC('ALTER TABLE #towf ADD ' + @ColName + ' NVARCHAR(6);')

   

   DECLARE @TmpOrderNum NVARCHAR(6)

   

   DECLARE OrderNumsCur CURSOR FAST_FORWARD  FOR

    SELECT OrderNumber FROM #towf

    

   OPEN OrderNumsCur

   

   FETCH NEXT FROM OrderNumsCur INTO @TmpOrderNum

   WHILE @@FETCH_STATUS <> -1

    BEGIN

     IF ((SELECT COUNT(*) FROM [tblOrderFlags] WHERE OrderNumber = @TmpOrderNum AND AttnCode = @AttnCode) <> 0)

      BEGIN

         EXEC('UPDATE #towf SET ' + @ColName + ' = (SELECT PlannerFlag FROM tblOrderFlags WHERE OrderNumber = ' + @TmpOrdernum + ' AND AttnCode = ' + @AttnCode + ') WHERE OrderNumber = ' + @TmpOrderNum + ';')

        END

       ELSE

        BEGIN

         EXEC('UPDATE #towf SET ' + @ColName + ' = ''N'' WHERE OrderNumber = ' + @TmpOrderNum + ';')

        END

     

     FETCH NEXT FROM OrderNumsCur INTO @TmpOrderNum

    END

    

   CLOSE OrderNumsCur

   DEALLOCATE OrderNumsCur

   

   FETCH NEXT FROM AllFlagsCursor INTO @ColName,@AttnCode

  END

  

 COMMIT TRANSACTION

  

 CLOSE AllFlagsCursor

 DEALLOCATE AllFlagsCursor

 

 SELECT * FROM #towf ORDER BY MoStatus,QueueSeq ASC

 

 DROP TABLE #towf