请问如何查询出not pick reports

yanwei82123300

请问如何查询出not pick reports（看看那些MO订单没有pick 干净）MO 4状态 5状态，6状态里面的原材料和工时未pick 或者数量不准确
谢谢

cw1632000

我写了个存储过程，是查询OVAR关订单后，哪些料没领的结果。
ALTER Proc [dbo].[SP_REP_MO_PICK]
(@DATE1 AS DATETIME,
@DATE2 AS DATETIME)

AS

--DECLARE @DATE datetime
--set @DATE='2012/5/22'

--插入PICK--
Select OrderNumber,LineNumber,ComponentItemNumber,
sum(Case When IssueType='I' Then IssuedQuantity Else -1*IssuedQuantity End) As PICKQTY Into #PICK1
From dbo.FS_HistoryPick
Where OrderType='M' And IssuedQuantity>0 And ComponentItemNumber Not Like 'WC%' AND RTRIM(OrderNumber)+'-'+Convert(char(3),LineNumber) IN
(Select OrderNumber+'-'+Convert(char(3),OrderLineNumber) From dbo.FS_ClosedOrderLineVariance a
inner join dbo.FS_ClosedOrderAnalysisRun b on a.ClosedOrderAnalysisRunKey=b.ClosedOrderAnalysisRunKey
where OrderType='M' AND dateadd(day,datediff(day,0,StartDateTime),0) between @DATE1 and @DATE2)
Group by OrderNumber,LineNumber,ComponentItemNumber
Order by OrderNumber,LineNumber,ComponentItemNumber

--插入BK-FL--
Insert Into #PICK1(OrderNumber,LineNumber,ComponentItemNumber,PICKQTY)
Select OrderNumber,LineNumber,ComponentItemNumber,
sum(Case When IssueType='I' Then IssuedQuantity Else -1*IssuedQuantity End) As PICKQTY
From dbo.FS_HistoryBackflush Where OrderType='M' And IssuedQuantity>0 And ComponentItemNumber Not Like 'WC%' AND RTRIM(OrderNumber)+'-'+Convert(char(3),LineNumber) IN
(Select OrderNumber+'-'+Convert(char(3),OrderLineNumber) From dbo.FS_ClosedOrderLineVariance a
inner join dbo.FS_ClosedOrderAnalysisRun b on a.ClosedOrderAnalysisRunKey=b.ClosedOrderAnalysisRunKey
where OrderType='M' AND dateadd(day,datediff(day,0,StartDateTime),0) between @DATE1 and @DATE2)
Group by OrderNumber,LineNumber,ParentItemNumber,ComponentItemNumber
Order by OrderNumber,LineNumber,ComponentItemNumber

--入库数--
select b.MONumber,a.MOLineNumber,c.ItemNumber,c.ItemDescription,ReceiptQuantity,ComponentItemNumber,d.ItemDescription as ItemDe,sum(PICKQTY) As QTY from dbo.FS_MOLine a
inner join dbo.FS_MOHeader b on a.MOHeaderKey=b.MOHeaderKey
inner join dbo.FS_Item c on a.ItemKey=c.ItemKey
left join (Select a.*,b.ItemDescription from #PICK1 a inner join FS_Item b on a.ComponentItemNumber=b.ItemNumber) d on b.MONumber=d.OrderNumber and a.MOLineNumber=d.LineNumber
where RTRIM(b.MONumber)+'-'+Convert(char(3),a.MOLineNumber) in (Select OrderNumber+'-'+Convert(char(3),OrderLineNumber) From dbo.FS_ClosedOrderLineVariance a
inner join dbo.FS_ClosedOrderAnalysisRun b on a.ClosedOrderAnalysisRunKey=b.ClosedOrderAnalysisRunKey
where OrderType='M' AND dateadd(day,datediff(day,0,StartDateTime),0)between @DATE1 and @DATE2)
Group by b.MONumber,a.MOLineNumber,c.ItemNumber,c.ItemDescription,ReceiptQuantity,ComponentItemNumber,d.ItemDescription
Order by b.MONumber,a.MOLineNumber

yanwei82123300

cw1632000谢谢帮助可否使用ACCESS做个代码，谢谢

cw1632000

你把下面下句截下来，将参数格式改一下，就是Access语句呀，只是ACCESS不支持存储过程

yanwei82123300

cw1632000老师谢谢帮助，我将您写的数据给IT部门看看他们是否可以帮助，谢谢

cw1632000

IT一定能看懂，而且COPY到数据库中就能用的，我是在7.5c环境写的，应该7.5版本都能支持。

chinzys

cw1632000  进步很大{:soso_e179:}！为你高兴！

wenlongtj

太高深了，兄弟刚接触四班，能否指点一下应该从哪学呢。

cw1632000

wenlongtj 发表于 2013/1/6 20:46
太高深了，兄弟刚接触四班，能否指点一下应该从哪学呢。

首先熟悉前台屏幕操作，明白各屏幕之间的流程逻辑关系。然后再去看后台，就会四班系统到底是怎么回事了。这样遇到问题也容易找到解决办法。