
CREATE TABLE TestOutPlanBaselineWeeks (
RawLine char(16) DEFAULT NULL,
FinishedGood char(16) DEFAULT NULL,
plant_code char(4) DEFAULT NULL,
period varchar(6) DEFAULT NULL,
del number(11,0) DEFAULT NULL,
WW1P1 number(11,0) DEFAULT NULL,
WW1P2 number(11,0) DEFAULT NULL,
DEL_DSI number(11) DEFAULT NULL,
WW1P1_DSI number(11) DEFAULT NULL,
WW1P2_DSI number(11) DEFAULT NULL,
CLIPWW1 number(11) DEFAULT NULL,
CLIPWW1Order number(11) DEFAULT NULL,
WW2P1 number(11) DEFAULT NULL,
WW2P2 number(11) DEFAULT NULL,
WW2P1_DSI number(11) DEFAULT NULL,
WW2P2_DSI number(11) DEFAULT NULL,
CLIPWW2 number(11) DEFAULT NULL,
CLIPWW2Order number(11) DEFAULT NULL,
WW3 number(11) DEFAULT NULL,
WW4 number(11) DEFAULT NULL,
WW5 number(11) DEFAULT NULL,
WW6 number(11) DEFAULT NULL,
WW7 number(11) DEFAULT NULL,
WW8 number(11) DEFAULT NULL,
WW9 number(11) DEFAULT NULL,
WW10 number(11) DEFAULT NULL,
WW11 number(11) DEFAULT NULL,
WW12 number(11) DEFAULT NULL,
WW13 number(11) DEFAULT NULL,
OPStatus char(3) DEFAULT NULL,
WW1P1Org number(11) DEFAULT NULL,
WW1P2Org number(11) DEFAULT NULL,
WW2P1Org number(11) DEFAULT NULL,
WW2P2Org number(11) DEFAULT NULL,
descr varchar(40) DEFAULT NULL
);


select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3, WW3C,WW3O, WW4, WW4C,WW4O, WW5, WW5C,WW5O,WW6,WW6C,WW6O,WW7,WW7C,WW7O
,case when status='w2clip' then
  case 
    when WW7C <0 then case when WW8+WW7C <0 then 0 else WW8+WW7C end 
    when WW7C >= 0 then WW8 end 
  else WW8 end as 'WW8'
,case when status='w2clip' then
  case 
    when WW7C <0 then case when WW8+WW7C >=0 then 0 else WW8+WW7C end 
    when WW7C >= 0 then case when WW8-WW7C >=0 then 0 else WW8-WW7C end 
  end
  else 0 end as 'WW8C'
,WW8 as WW8O,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3, WW3C,WW3O, WW4, WW4C,WW4O, WW5, WW5C,WW5O,WW6,WW6C,WW6O
,case when status='w2clip' then
  case 
    when WW6C <0 then case when WW7+WW6C <0 then 0 else WW7+WW6C end 
    when WW6C >= 0 then WW7 end 
  else WW7 end as 'WW7'
,case when status='w2clip' then
  case 
    when WW6C <0 then case when WW7+WW6C >=0 then 0 else WW7+WW6C end 
    when WW6C >= 0 then case when WW7-WW6C >=0 then 0 else WW7-WW6C end
  end
  else 0 end as 'WW7C'
,WW7 as WW7O,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3, WW3C,WW3O, WW4, WW4C,WW4O, WW5, WW5C,WW5O
,case when status='w2clip' then
  case 
    when WW5C <0 then case when WW6+WW5C <0 then 0 else WW6+WW5C end 
    when WW5C >= 0 then WW6 end
  else WW6 end as 'WW6'
,case when status='w2clip' then
  case 
    when WW5C <0 then case when WW6+WW5C >=0 then 0 else WW6+WW5C end 
    when WW5C >= 0 then case when WW6-WW5C >=0 then 0 else WW6-WW5C end 
  end
  else 0 end as 'WW6C'
,WW6 as WW6O,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3, WW3C,WW3O, WW4, WW4C,WW4O
,case when status='w2clip' then
  case 
    when WW4C <0 then case when WW5+WW4C <0 then 0 else WW5+WW4C end 
    when WW4C >= 0 then WW5 end
  else WW5 end as 'WW5'
,case when status='w2clip' then
  case 
    when WW4C <0 then case when WW5+WW4C >=0 then 0 else WW5+WW4C end 
    when WW4C >= 0 then case when WW5-WW4C >=0 then 0 else WW5-WW4C end 
  end
  else 0 end as 'WW5C'
,WW5 as WW5O,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3, WW3C,WW3O
,case when status='w2clip' then
  case 
    when WW3C <0 then case when WW4+WW3C <0 then 0 else WW4+WW3C end 
    when WW3C >= 0 then WW4 end
  else WW4 end as 'WW4'
,case when status='w2clip' then
  case 
    when WW3C <0 then case when WW4+WW3C >=0 then 0 else (WW4+WW3C) end
    when WW3C >= 0 then case when WW4-WW3C >=0 then 0 else (WW4-WW3C) end
  end
  else 0 end as 'WW4C'
,WW4 as WW4O,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DEL_DSI,DELO,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C,WW2P2,WW2P2O,WW2P2_DSI,WW2P2C,CLIPWW2,CLIPWW2Order,CLIPWW2O
,case when status='w2clip' then
  case 
    when WW2P2C <0 then case when WW3+WW2P2C <0 then 0 else WW3+WW2P2C end 
    when WW2P2C >= 0 then WW3 end
  else WW3 end as 'WW3'
,case when status='w2clip' then
 case 
   when WW2P2C <0 then case when WW3+WW2P2C >=0 then 0 else (WW3+WW2P2C) end 
   when WW2P2C >= 0 then case when WW3-WW2P2C >=0 then 0 else (WW3-WW2P2C) end
 end
 else 0 end as 'WW3C' 
,WW3 as ww3O
,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status,w1status,DELQ,DELO,DEL_DSI,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP,WW2P1,WW2P1O,WW2P1_DSI,WW2P1C
,case 
  when WW2P1C <0 then case when WW2P2+WW2P2_DSI+WW2P1C <0 then 0 else (WW2P2+WW2P2_DSI)+WW2P1C end
  when WW2P1C >= 0 then (WW2P2+WW2P2_DSI) end 'WW2P2'
,WW2P2_DSI
,case 
  when WW2P1C <0 then case when WW2P2+WW2P2_DSI+WW2P1C >=0 then 0 else (WW2P2+WW2P2_DSI)+WW2P1C end
  when WW2P1C >= 0 then case when WW2P2+WW2P2_DSI - WW2P1C >=0 then 0 else (WW2P2+WW2P2_DSI) - WW2P1C end
 end as 'WW2P2C'
,WW2P2 as 'WW2P2O'
,CLIPWW2,CLIPWW2Order,CLIPWW2O
,WW3,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from (
select period,plant_code,rawline,finishedgood,status as w1status,DELQ,DELO,DEL_DSI
,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O,WW1P2,WW1P2_DSI,WW1P2C,WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP
,case
  when (CLIPWW2 < ((WW2P1+WW2P1_DSI+WW1P2C)+(WW2P2+WW2P2_DSI))) then 'w2top'
  when (CLIPWW2 >= ((WW2P1+WW2P1_DSI+WW1P2C)+(WW2P2+WW2P2_DSI))) then 'w2clip'
  end 'Status'
,greatest ((WW2P1+WW2P1_DSI+WW1P2C), CLIPWW2) as 'WW2P1'
, case
   when((WW2P1+WW2P1_DSI+WW1P2C) < CLIPWW2) then - (CLIPWW2 - (WW2P1+WW2P1_DSI+WW1P2C) )
   when((WW2P1+WW2P1_DSI+WW1P2C) >= CLIPWW2) then 0 end as 'WW2P1C',WW2P1 as WW2P1O,WW2P1_DSI
,WW2P2,WW2P2_DSI,CLIPWW2,CLIPWW2O,CLIPWW2Order
,WW3,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPstatus,descr
from
(
select period,plant_code,RawLine,FinishedGood,status,DELQ,DELO,DEL_DSI
,WW1P1,WW1P1_DSI,WW1P1C,WW1P1O
,case
   when WW1P1C <0 then case when WW1P2+WW1P2_DSI+WW1P1C <=0 then 0 else WW1P2+WW1P2_DSI+WW1P1C end
                       else (WW1P2+WW1P2_DSI)
  end WW1P2
,WW1P2_DSI
,case
   when WW1P1C < 0 then case when (WW1P2+WW1P2_DSI)+WW1P1C >0 then 0 else (WW1P2+WW1P2_DSI)+WW1P1C end
                         else case when (WW1P2+WW1P2_DSI) >=0 then 0 else (WW1P2+WW1P2_DSI) end
  end as 'WW1P2C'
,WW1P2 as WW1P2O,CLIPWW1,CLIPWW1Order,W1TOPGAP
,WW2P1,WW2P1_DSI,WW2P2,WW2P2_DSI
,case 
   when status ='w1top' and (W1TOPGAP >0) then (CLIPWW2-W1TOPGAP) else CLIPWW2 end as 'CLIPWW2'
,CLIPWW2 as CLIPWW2O,CLIPWW2Order
,WW3,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPStatus,descr
from
(
select period,plant_code,RawLine,FinishedGood,status,DELQ,DEL as DELO,DEL_DSI
,case
  when ((DEL+DEL_DSI)= DELQ) then (WW1P1+WW1P1_DSI)
  when (CLIPWW1 > (DEL+DEL_DSI)) then 
    case when (WW1P1+WW1P1_DSI) -(CLIPWW1-(DEL+DEL_DSI)) <0 then 0 else (WW1P1+WW1P1_DSI) -(CLIPWW1-(DEL+DEL_DSI)) end
end WW1P1
,case
  when ((DEL+DEL_DSI)= DELQ) then (WW1P1+WW1P1_DSI)
  when (CLIPWW1 > (DEL+DEL_DSI)) then (WW1P1+WW1P1_DSI) -(CLIPWW1-(DEL+DEL_DSI))
end WW1P1C
,WW1P1 as WW1P1O,WW1P1_DSI
,WW1P2,WW1P2_DSI
,CLIPWW1,CLIPWW1Order,W1TOPGAP
,WW2P1,WW2P1_DSI
,WW2P2,WW2P2_DSI
,CLIPWW2,CLIPWW2Order
,WW3,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPStatus,descr
from
(
select period,plant_code,RawLine,FinishedGood,DEL
,case
  when (CLIPWW1 < ((DEL+DEL_DSI)+(WW1P1+WW1P1_DSI)+(WW1P2+WW1P2_DSI))) then 'w1top'
  when (CLIPWW1 >= ((DEL+DEL_DSI)+(WW1P1+WW1P1_DSI)+(WW1P2+WW1P2_DSI))) then 'w1clip'
end 'Status'
,(((DEL+DEL_DSI)+(WW1P1+WW1P1_DSI)+(WW1P2+WW1P2_DSI)) - CLIPWW1 ) as 'W1TOPGAP'
,case
  when (CLIPWW1 <= (DEL+DEL_DSI)) then (DEL+DEL_DSI)
  when (CLIPWW1 > (DEL+DEL_DSI)) then CLIPWW1
  when (CLIPWW1 > ((DEL+DEL_DSI)+(WW1P1+WW1P2_DSI))) then CLIPWW1
  when (CLIPWW1 > ((DEL+DEL_DSI)+(WW1P1+WW1P1_DSI)+(WW1P2+WW1P2_DSI))) then CLIPWW1
else 0
end DELQ
,DEL_DSI
,WW1P1Org as WW1P1, WW1P1_DSI
,WW1P2Org as WW1P2,WW1P2_DSI
,CLIPWW1,CLIPWW1Order
,WW2P1Org as WW2P1,WW2P1_DSI
,WW2P2Org as WW2P2,WW2P2_DSI
,CLIPWW2,CLIPWW2Order
,WW3,WW4,WW5,WW6,WW7,WW8,WW9,WW10,WW11,WW12,WW13,OPStatus,descr
from TestOutPlanBaselineWeeks
) delq) w1p1) w1p2) ww2Pp1) w3) w4) w5) w6) w7) w8;
