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