[易飞]六、邮件提醒-采购提醒集

1.超期(14天)未交货采购单SELECT K.TD001+'-'+rtrim(K.TD002)+'-'+K.TD003 单据号, K.TD004 品号, K.TD005 品名, K.TD006 规格,convert(decimal(15,2),K.TD008) 采购数量, convert(decimal(15,2),K.TD015) 已交数量, ZE.dbo.Date10(K.TD012) 预交日期,ZE.dbo.Date10(T.TC003) 采购日期, DATEDIFF(DAY,TD012,GETDATE()) 超期天数, T.TC025 审核者, A.MA002 供应商简称 FROM ZE.dbo.PURTD K, ZE.dbo.PURTC T, ZE.dbo.PURMA A WHERE T.TC004=A.MA001 AND K.TD001 = T.TC001 AND K.TD002 = T.TC002 AND ((K.TD016='N') AND (K.TD012<CONVERT(CHAR(8),GETDATE(),112)) AND (T.TC014<>'V') AND (K.TD008<>0) AND (SUBSTRING(K.CREATE_DATE,1,8)>='20070501') AND (DATEDIFF(DAY,TC003,GETDATE())>1)) AND DATEDIFF(DAY,TD012,GETDATE())>14

2.未结束采购单-超期14天

SELECT K.TD001+'-'+rtrim(K.TD002)+'-'+K.TD003 单据号, K.TD004 品号, K.TD005 品名, K.TD006 规格, convert(decimal(15,2),K.TD008) 数量, convert(decimal(15,2),K.TD015) 已交数量, ZE.dbo.Date10(K.TD012) 预交日期,ZE.dbo.Date10(T.TC003) 采购日期, DATEDIFF(DAY,TD012,GETDATE()) 预交日超今天数, K.TD024 来源单号,T.TC025 审核者 , A.MA002 供应商简称 FROM ZE.dbo.PURTD K, ZE.dbo.PURTC T, ZE.dbo.PURMA A WHERE TC014='Y' AND T.TC004=A.MA001 AND K.TD001 = T.TC001 AND K.TD002 = T.TC002 AND ((K.TD016='N') AND (T.TC014<>'V') AND (K.TD008<>0)) and DATEDIFF(DAY,TD012,GETDATE())>14

3.未审核采购单明细表(超两天未审核)

SELECT ZE.dbo.Date10(TC003) 单据日期, K.TD001+'-'+ rtrim(K.TD002)+'-'+K.TD003 单据号, K.TD004 品号, K.TD005 品名, K.TD006 规格, Convert(decimal(18,2),K.TD008) 采购单数量, SUBSTRING(K.CREATE_DATE,1,8) 录入日期, DATEDIFF(DAY,SUBSTRING(K.CREATE_DATE,1,8),GETDATE()) 录入日期距今天数, K.TD024 来源单号 FROM ZE.dbo.PURTD K left join ZE.dbo.PURTC C on K.TD001=C.TC001 AND K.TD002=C.TC002 WHERE (K.TD018='N') AND (K.TD008<>0) AND datediff(day,TC003,getdate())>2

4.已请购未采购明细-审核超7天（90天）

SELECT B.TB001+'-'+rtrim(B.TB002)+'-'+B.TB003 单据日期, B.TB004 品号, B.TB005 品名, B.TB006 规格, Convert(decimal(18,2),B.TB009) 清购数量, ZE.dbo.Date10(B.TB011) 需求日期, ZE.dbo.Date10(SUBSTRING(B.CREATE_DATE,1,8)) 审核日期, DATEDIFF(DAY,SUBSTRING(B.CREATE_DATE,1,8),GETDATE()) 审核日期距今天, C.MF002 审核者 FROM ZE.dbo.PURTA A, ZE.dbo.PURTB B, ZE.dbo.ADMMF C WHERE (B.TB001=A.TA001) AND (B.TB002=A.TA002) AND (B.TB009>0) AND (B.TB025='Y') AND (B.TB039='N') AND (A.TA014=C.MF001) and DATEDIFF(DAY,SUBSTRING(B.CREATE_DATE,1,8),GETDATE())>7 and DATEDIFF(DAY,GETDATE(),B.TB011)<60 AND left(B.TB004,3) not in ('308','309')

5.供应商涨价年度汇总表

select TL001+'-'+rtrim(TL002)+'-'+TM003 as 核价单,dbo.Date10(TL003) 单据日期,MA003 供应商,TL005 币别,TM004 品号,TM005 品名,TM006 规格,TM007 供应商编号,TM009 计价单位,TM010 单价,TM018 原单价,dbo.Date10(TM014) 生效日,dbo.Date10(TM015) 失效日 from PURTL left join PURTM on TL001=TM001 AND TL002=TM002 left join PURMA ON TL004=MA001 WHERE TM011='Y' and left(TL003,4)=DATEPART(year, GETDATE()) and TM018<>0 and TM005 not like '%继电器%'