"set ANSI_NULLS ON set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spu_PO2]
AS
DECLARE @ETD1 datetime, @ETD2 datetime, @ETD3 datetime, @ETD4 datetime,
@ShipAir1 varchar(1), @ShipAir2 varchar(1), @ShipAir3 varchar(1), @ShipAir4 varchar(1)
-- SELECT * FROM ZZPOt DROP TABLE #PO
SELECT TOP 100 PERCENT Style, Color, [Size], Pono, OrdQty, Style + Color + [Size] AS Item
INTO #PO
FROM dbo.zzPO
GROUP BY Style, Color, [Size], Style + Color + [Size], Pono, OrdQty
ORDER BY Style, Color, [Size], Pono
SELECT TOP 100 PERCENT Style, Color, [Size], MIN(DISTINCT Pono) AS MinPono,
Style + Color + [Size] AS Item, @ETD1 AS ETD1
INTO #PO1
FROM dbo.zzPO
GROUP BY Style, Color, [Size]
ORDER BY Style, Color, [Size]
--UPDATE #PO1
--SET ETD1 = ETD
--FROM #PO1 INNER JOIN tPO
--ON tPO.Pono = #PO1.MinPono
delete #PO
from #PO, #PO1
where #PO.item = #PO1.Item and #PO.Pono = #PO1.MinPono
SELECT TOP 100 PERCENT Style, Color, [Size], MIN(DISTINCT Pono) AS MinPono,
Style + Color + [Size] AS Item, @ETD2 AS ETD2
INTO #PO2
FROM #PO
GROUP BY Style, Color, [Size]
ORDER BY Style, Color, [Size]
--UPDATE #PO2
--SET ETD2 = ETD
--FROM #PO2 INNER JOIN tPO
--ON tPO.Pono = #PO2.MinPono
delete #PO
from #PO, #PO2
where #PO.item = #PO2.Item and #PO.Pono = #PO2.MinPono
SELECT TOP 100 PERCENT Style, Color, [Size], MIN(DISTINCT Pono) AS MinPono,
Style + Color + [Size] AS Item, @ETD3 AS ETD3
INTO #PO3
FROM #PO
GROUP BY Style, Color, [Size]
ORDER BY Style, Color, [Size]
--UPDATE #PO3
--SET ETD3 = ETD
--FROM #PO3 INNER JOIN tPO
--ON tPO.Pono = #PO3.MinPono
delete #PO
from #PO, #PO3
where #PO.item = #PO3.Item and #PO.Pono = #PO3.MinPono
SELECT TOP 100 PERCENT Style, Color, [Size], MIN(DISTINCT Pono) AS MinPono,
Style + Color + [Size] AS Item, @ETD4 AS ETD4
INTO #PO4
FROM #PO
GROUP BY Style, Color, [Size]
ORDER BY Style, Color, [Size]
--UPDATE #PO4
--SET ETD4 = ETD
--FROM #PO4 INNER JOIN tPO
--ON tPO.Pono = #PO4.MinPono
UPDATE zzPoT
SET zzPoT.Pono1 = #PO1.MinPono
FROM zzPoT
inner join #PO1
ON #PO1.Style = zzPoT.Style and #PO1.Color = zzPoT.Color and #PO1.[Size] = zzPoT.[Size]
UPDATE zzPoT
SET zzPoT.Pono2 = #PO2.MinPono
FROM zzPoT
inner join #PO2
ON #PO2.Style = zzPoT.Style and #PO2.Color = zzPoT.Color and #PO2.[Size] = zzPoT.[Size]
UPDATE zzPoT
SET zzPoT.Pono3 = #PO3.MinPono
FROM zzPoT
inner join #PO3
ON #PO3.Style = zzPoT.Style and #PO3.Color = zzPoT.Color and #PO3.[Size] = zzPoT.[Size]
UPDATE zzPoT
SET zzPoT.Pono4 = #PO4.MinPono
FROM zzPoT
inner join #PO4
ON #PO4.Style = zzPoT.Style and #PO4.Color = zzPoT.Color and #PO4.[Size] = zzPoT.[Size]
UPDATE zzPoT
SET zzPoT.PO1 = tLinePO.OrdQty
FROM zzPoT
inner join tLinePO
ON tLinePO.Style = zzPoT.Style and tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size] and zzPoT.Pono1 = tLinePO.Pono
UPDATE zzPoT
SET zzPoT.PO2 = tLinePO.OrdQty
FROM zzPoT
inner join tLinePO
ON tLinePO.Style = zzPoT.Style and tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size] and zzPoT.Pono2 = tLinePO.Pono
UPDATE zzPoT
SET zzPoT.PO3 = tLinePO.OrdQty
FROM zzPoT
inner join tLinePO
ON tLinePO.Style = zzPoT.Style and tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size] and zzPoT.Pono3 = tLinePO.Pono
UPDATE zzPoT
SET zzPoT.PO4 = tLinePO.OrdQty
FROM zzPoT inner join tLinePO
ON tLinePO.Style = zzPoT.Style and tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size] and zzPoT.Pono4 = tLinePO.Pono
UPDATE zzPoT
SET PO1 = isnull(PO1,0) /12, PO2 = isnull(PO2,0) /12, PO3 = isnull(PO3,0)/12, PO4 = isnull(PO4,0)/12
FROM zzPoT
UPDATE zzPoT
SET ETD1 = ETD, zzPoT.ShipAir1 = tLinePO.ShipAir
FROM zzPoT INNER JOIN tLinePO
ON tLinePO.Pono = zzPoT.Pono1 and tLinePO.Style = zzPoT.Style and
tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size]
WHERE PO1 > 0
UPDATE zzPoT
SET ETD2 = ETD, ShipAir2 = tLinePO.ShipAir
FROM zzPoT INNER JOIN tLinePO
ON tLinePO.Pono = zzPoT.Pono2 and tLinePO.Style = zzPoT.Style and
tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size]
WHERE PO2 > 0
UPDATE zzPoT
SET ETD3 = ETD, ShipAir3 = tLinePO.ShipAir
FROM zzPoT INNER JOIN tLinePO
ON tLinePO.Pono = zzPoT.Pono3 and tLinePO.Style = zzPoT.Style and
tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size]
WHERE PO3 > 0
UPDATE zzPoT
SET ETD4 = ETD, ShipAir4 = tLinePO.ShipAir
FROM zzPoT INNER JOIN tLinePO
ON tLinePO.Pono = zzPoT.Pono4 and tLinePO.Style = zzPoT.Style and
tLinePO.Color = zzPoT.Color and tLinePO.[Size] = zzPoT.[Size]
WHERE PO4 > 0
-- SELECT * from zzpot where style='ab831'
RETURN
"
