易飞存储过程没有录工单工时的非委外工单

最新推荐文章于 2019-07-04 09:26:06 发布

sadleaflzh

最新推荐文章于 2019-07-04 09:26:06 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/sadleaflzh/article/details/16938329

本文介绍了一种通过存储过程查询特定时间段内的生产工单信息的方法，包括工单名称、工单单别、工单单号等详细内容，并通过排除委外工单来精确筛选厂内工单及返工工单。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--因为可以重复录入工单工时，所以生产中状态也要录工时
--通过单据性质将委外工单排除，原来用工单类别(51厂内工单，52 返工工单)

create proc sp_gdgs_all(@starttime varchar(8),@endtime varchar(8))
as
begin
declare @lblb varchar(10)
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempt'))
drop table #tempt
create table #tempt
(gdmc varchar(10),
gddb varchar(10),
gddh varchar(20),
kdrq varchar(20),
pm varchar(80),
gg varchar(150),
dw varchar(4),
yjcl float,
sjcl float,
wgrq varchar(20)
)
declare gdlb cursor for select MQ001 from CMSMQ WHERE MQ003 in('51','52') and MQ017='1'
open gdlb
fetch next from gdlb into @lblb
while @@fetch_status=0
begin
insert into #tempt
select MQ002 AS '工单名称', TA001 AS '工单单别', TA002 AS '工单单号',TA003 AS '开单日期',TA034 AS '品名',TA035 '规格',TA007 AS '单位',TA015 AS '预计产量',TA017 AS '实际产量',TA014 AS '实际完工'
from MOCTA,CMSMQ
WHERE
TA011 IN ('Y','3')
--因为可以重复录入工单工时，所以生产中状态也要录工时
AND TA014 between @starttime and @endtime
AND MOCTA.TA001=CMSMQ.MQ001
AND MOCTA.TA001=@lblb
and @lblb+TA002 NOT IN(SELECT MB003+MB004 FROM CSTMB WHERE MB003=@lblb)
fetch next from gdlb into @lblb
end
close gdlb
deallocate gdlb
select gdmc AS '工单名称', gddb AS '工单单别', gddh AS '工单单号',kdrq AS '开单日期',pm AS '品名',gg '规格',dw AS '单位',yjcl AS '预计产量',sjcl AS '实际产量',wgrq AS '实际完工' from #tempt
end

endexec sp_gdgs_all '20130801', '20130830'

drop proc sp_gdgs_all