[易飞]八、邮件提醒-质量部集合

1.客户品号检查

select MG001 客户编码,MG002 品号,MG003 客户品号,MB002 品名,MB003 规格,ZE.dbo.Date10(LEFT(COPMG.CREATE_DATE,8)) 日期 from ZE.dbo.COPMG LEFT JOIN ZE.dbo.INVMB ON MG002=MB001

2.超期两天未建立进货检验单

select '未建立检验单' 状态,TH001+'-'+rtrim(TH002)+'-'+TH003 进货单,TH004 品号,TH005 品名, TH006 规格,ZE.dbo.Date10(TH014) 验收日期 ,TH007 进货数量,TH008 单位,TH009 仓库,TH010 批号,TH011+'-'+rtrim(TH012)+'-'+TH013 采购单 from PURTH left join QMSTA ON TH001=TA001 AND TH002=TA002 AND TH003=TA003 WHERE TH028='1' and (TA001 is null and TA002 IS NULL AND TA003 IS NULL) and datediff(day,TH014,getdate())>3

3.待处理进货检验单

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /* *用途:待处理发送同时更新进货单身标志 *作者:龚德辉 2011-05-10 * */ ALTER Procedure [dbo].[UP_InvCheck] (@0 as nvarchar(15)=null) as Begin ----判断待处理检验单 if exists(select * from QMSTA left join PURTH ON TH001=TA001 AND TH002=TA002 AND TH003=TA003 where TA014='N' AND QMSTA.TA015<>''and PURTH.UDF02<>'已发送' ) --检验单未审核 备注不为空 进货单单身不等于已发送 begin UPDATE PURTH set PURTH.UDF02='已发送' from QMSTA,PURTH where TH001=TA001 AND TH002=TA002 AND TH003=TA003 and TA014='N' AND QMSTA.TA015<>''and PURTH.UDF02<>'已发送' --检验单未审核 备注不为空 进货单单身不等于已发送 end select TA015 处理状态,TH001+'-'+rtrim(TH002)+'-'+TH003 进货单,TH004 品号,TH005 品名, TH006 规格,ZE.dbo.Date10(TH014) 验收日期 ,TH007 进货数量,TH008 单位,TH009 仓库,TH010 批号,TH011+'-'+rtrim(TH012)+'-'+TH013 采购单 from QMSTA left join PURTH ON TH001=TA001 AND TH002=TA002 AND TH003=TA003 where TA014='N' AND QMSTA.TA015<>''and PURTH.UDF02<>'已发送' --检验单未审核 备注不为空 进货单单身不等于已发送 End