USE [mandb101]
GO
/****** Object: StoredProcedure [dbo].[workOrderScheduleCalculate] Script Date: 03/15/2009 18:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[workOrderScheduleCalculate]
as
/*
MANDB100 = MFG
MANDB101 = OMAR
*/
begin
-- create a dummy routing for anything that doesn't have one.
insert into routing (prtno, wkcntr, seqno)
(select distinct a.prtno, 0, 1 from mandb100.envymgr.wohrec a
where not exists (select * from routing b where a.prtno = b.prtno))
-- create a 'actual' routing for each work order that doesn't have one.
insert into workOrderRouting (wohno, wkcntr, seqno)
(select a.wohno, b.wkcntr, b.seqno
from workOrder a, routing b, mandb100.envymgr.wohrec c
where a.wohno = c.wohno and b.prtno = c.prtno and
not exists (select * from workOrderRouting d
where d.wohno = a.wohno and b.wkcntr = d.wkcntr and b.seqno = d.seqno)
)
-- open work orders in some kind of order
DECLARE WOCUR cursor for
select a.wkcntr, a.wohno, (b.wohoqty - b.wohcqty) * prtwgt / 2000
from workOrderRouting a, mandb100.envymgr.wohrec b, mandb100.envymgr.prtrec c, workorder d
where a.wohno = b.wohno and
b.wohcldat = 99999999 and
b.prtno = c.prtno AND
b.wohoqty > b.wohcqty and
d.wohno = a.wohno
order by a.wkcntr, wopriority desc, (b.wohoqty - b.wohcqty) * prtwgt / 2000 DESC, a.wohno
declare @last_wkcntr char (10),
@wkcntr char(10),
@wohno char(10),
@tonnage REAL,
@daily_capacity numeric(9),
@newstartdate datetime,
@days int,
@dayshours real,
@HOURS REAL,
@extradays real,
@s char(30),
@d datetime,
@calcstartdate datetime;
set @last_wkcntr = 'zzzz'
open wocur
FETCH NEXT FROM wocur into @wkcntr, @wohno, @tonnage
while (@@FETCH_STATUS = 0 )
begin
if @wkcntr <> @last_wkcntr
begin
set @last_wkcntr = @wkcntr
-- get current daily capacity
select @daily_capacity = dailycap
from workCentre
where wkcntr = @wkcntr
set @calcstartdate = (convert(datetime, (convert(char,getdate(),112)),112))
end
update workOrder
set calcstartdate = @calcstartdate
where wohno = @wohno
-- convert tonnage into days
set @dayshours = @tonnage / (case @daily_capacity when 0 then .0001 else @daily_capacity end)
SET @HOURS = (@DAYShours - (CAST (@DAYShours AS INT)) ) * 24
SET @DAYS = CAST (@DAYShours AS INT)
set @newStartDate = @calcStartDate
set @newStartDate = DATEADD(day, @days, @newStartDate)
set @newStartDate = DATEADD(hour, @HOURS, @newStartDate)
set @d = @newstartdate
-- ok now determine if we shouldadd a few days because of holidays
set @extraDays = (select count(*) from standens.nonworkdays where workdate between
(convert(datetime, (convert(char,@calcstartdate,112)),112))
and @newstartdate)
set @newstartdate = DATEADD(day, @extradays, @newStartDate)
-- now the new start date might be on a non work day, so....
while ( select count(*)
from standens.nonworkdays
where workdate = (convert(datetime, (convert(char,@newstartdate,112)),112))
) > 0
begin
set @newstartdate = DATEADD(day, 1, @newStartDate)
end
PRINT
--@WKCNTR + @WOHNO + cast(@TONNAGE as char) + CAST(@DAILY_CAPACITY AS CHAR) +
cast(@dayshours as char) +
CONVERT(CHAR, @CALCSTARTDATE, 113) +
CONVERT(CHAR, @d, 113) +
cast(@extradays as char) +
CONVERT(CHAR, @newSTARTDATE, 113)
set @calcstartdate = @newstartdate
fETCH NEXT FROM wocur into @wkcntr, @wohno, @tonnage
end
close wocur
deallocate wocur
end