[易飞]七、邮件提醒-订单与销货单提醒

1.未结束订单(>6天)

SELECT T.TD001+'-'+rtrim(T.TD002)+'-'+T.TD003 单号,K.TC012 客户单号, T.TD004 品号, T.TD005 品名, T.TD006 规格, convert( decimal(15,2),T.TD008) 订单, convert( decimal(15,2),T.TD009) 已交, convert( decimal(15,2),(TD008-TD009)) 欠交, T.TD013 预交货日期, DATEDIFF(DAY,TD013,GETDATE()) AS 交货日超7天,A.MA002 客户简称,convert( decimal(15,2),B.MB064) 现有库存, case when (TD008-TD009)-B.MB064>0.00 then convert( decimal(15,2),(TD008-TD009)-B.MB064) else 0.00 end as 欠数 FROM ZE.dbo.COPTC K, ZE.dbo.COPTD T, ZE.dbo.COPMA A, ZE.dbo.INVMB B WHERE (K.TC004=A.MA001) AND (T.TD021='Y') AND (T.TD008<>0) AND (T.TD016='N') AND (K.TC001=T.TD001) AND (K.TC002=T.TD002) AND B.MB001=T.TD004 and TD013<=convert(char(8),getdate(),112) and DATEDIFF(DAY,TD013,GETDATE()) >6

2.超期订单

SELECT K.TC004 客户编码,MA002 客户简称,T.TD001+'-'+rtrim(T.TD002)+'-'+T.TD003 单据号, T.TD004 品号, T.TD005 品名, convert(decimal(15,2),T.TD008) 订单数量, convert(decimal(15,2),T.TD009) 已交数量, convert(decimal(15,2),(TD008-TD009)) 欠交数量, ZE.dbo.Date10(T.TD013) 欠交日期, DATEDIFF(DAY,TD013,GETDATE()) 超期天数 FROM ZE.dbo.COPTC K,ZE.dbo.COPTD T,ZE.dbo.COPMA A WHERE (K.TC004=A.MA001) AND (T.TD021='Y') AND (T.TD008<>0) AND (T.TD016='N') AND (K.TC001=T.TD001) AND (K.TC002=T.TD002) AND (T.TD013 <CONVERT(CHAR(8),GETDATE(),112))

3.销货单已下单未审核超期2天

select datediff(day,TG003,GETDATE()) 开单日期超今天,TG001+'-'+TG002 as 销货单,ZE.dbo.Date10(TG003) as 开单日期,MA003 客户名称,MV002 AS 业务员,TG008 as 送货地址,TG013 原币销货金额 ,TG011 as 币种,TG012 as 汇率,cast(Convert(decimal(18,2),TG044*100) as varchar)+'%' 税率 from ZE.dbo.COPTG inner join ZE.dbo.COPMA on TG004=MA001 inner JOIN CMSMV ON TG006=MV001 where TG023='N' and datediff(day,TG003,GETDATE())>2

4.客户订单变更提醒（看似简单）

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /* *用途:客户订单变更提醒 *作者:龚德辉 2011-11-24 */ ALTER Procedure [dbo].[UP_OrderChgNotice] ( @0 as nvarchar(15)=null ) as begin select TE001+'-'+rtrim(TE002)+'-'+isnull(TF104,'') as 订单号,TE003 变更版本,dbo.Date10(TE004) AS 变更日期,TE006 变更原因,CASE WHEN TE005='Y' THEN '是'else '否' end as 整张结束,TE007 客户编码,TD004 品号,TD006 规格,convert(decimal(10,2),TF109) 原订单数量,convert(decimal(10,2),TF009) as 变更后订单数量, dbo.Date10(TF115) as 原预计交货日, dbo.Date10(TF015) as 变更后计交货日,TE039 AS 审核者 from COPTE INNER join COPTF on TE001=TF001 AND TE002=TF002 AND TE003=TF003 left JOIN COPTD ON TE001=TD001 AND TE002=TD002 AND TF104=TD003 WHERE TE004=convert(char(8),convert(char(8),dateadd(day,-1,getdate()),112),112) --and (TF109<>TF009 OR TF115<>TF015) and TE029='Y' union all select TE001+'-'+rtrim(TE002)+'-'+TD003 as 订单号,TE003 变更版本,dbo.Date10(TE004) AS 变更日期,TE006 变更原因,CASE WHEN TE005='Y' THEN '是'else '否' end as 整张结束,TE007 客户编码,TD004 品号,TD006 规格,TD008-TD009 原订单数量,0 变更后订单数量,dbo.Date10(TD013) AS 原预计交货日,dbo.Date10(TD013) 变更后计交货日,TE039 AS 审核者 from COPTE left JOIN COPTD ON TE001=TD001 AND TE002=TD002 where TE004=convert(char(8),convert(char(8),dateadd(day,-1,getdate()),112),112) and TE005='Y' end