USE [ChangHongWMS612]
GO/****** Object: StoredProcedure [dbo].[st_WMS_RptMaterialInOutDaily] Script Date: 05/12/2015 17:24:27 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
/*create by :zhussDate:2014-12-19
Remark:原料投入产出报表原存储过程名称:WMS_RptMaterialInOutDaily*/ALTER Proc [dbo].[st_WMS_RptMaterialInOutDaily]( @StartDate varchar(10)= '', @EndDate varchar(10)='', @MaterialNO varchar(50) = '')as if @EndDate='' select @EndDate=CONVERT(varchar(10),getdate(),121) select @StartDate = CONVERT(varchar(10),convert(datetime,@StartDate),121) select @EndDate = CONVERT(varchar(10),convert(datetime,@EndDate),121)exec st_WMS_JobReDoMaterialInOut 'RptMaterialInOutDaily' select t1.* , BillAges = datediff(day, (select top 1 m.CreateTime from KIT_MaterialStockIn m where m.MQty>0 and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO order by m.CreateTime desc) ,getdate()) from WMS_RptMaterialInOutDaily t1 where CurrDate between @StartDate and @EndDate and (@MaterialNO = '' or (MaterialNO like '%' +@MaterialNO+'%' or MaterialName like '%' +@MaterialNO+'%')) order by CurrDate,MaterialNOGO
---------------------------------------
USE [ChangHongWMS612]
GO/****** Object: StoredProcedure [dbo].[st_WMS_JobReDoMaterialInOut] Script Date: 05/12/2015 17:33:05 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
GO/*
create by :hzcreate time:2014-12-19Remark:执行原料出入库原存储过程名:JOB_ReDoMaterialInOut*/ALTER proc [dbo].[st_WMS_JobReDoMaterialInOut]( --WMS_RptMaterialInOutDaily @JobName varchar(50) = 'RptMaterialInOutDaily'--@r_DoJob varchar(50) = '')asdeclare @CurrDate varchar(10), @UpdateDate datetime, @Feeding_Time varchar(19), @CurrTime varchar(19)
declare CursorD cursor local For select distinct convert(varchar(10),FeedTime,121), ApprovedDate = CONVERT(varchar(14),CreateTime,121)+'00:00',convert(varchar(19),FeedTime,121) from KIT_FeedSupply where CreateTime>= CONVERT(varchar(10),GETDATE(),121) union select ChgDate = convert(varchar(10),HopperTime,121), ApprovedDate = CONVERT(varchar(14),CreateTime,121)+'00:00',convert(varchar(19),HopperTime,121) from KIT_HopperRecord Open CursorD Fetch next From CursorD Into @CurrDate, @UpdateDate, @Feeding_Time While(@@Fetch_Status = 0) Begin if(@JobName = '' or @JobName = 'RptMaterialInOutDaily') begin if(exists(select * from WMS_JobDoRpt where JobName = 'RptMaterialInOutDaily' and CurrDate = @CurrDate and UpdateTime<=@UpdateDate) or not exists(select * from WMS_JobDoRpt where JobName = 'RptMaterialInOutDaily' and CurrDate = @CurrDate)) begin select @CurrTime = @CurrDate + ' 23:59:59'exec [dbo].[st_WMS_JobGenRptMaterialInOut] @CurrTime end end Fetch next From CursorD Into @CurrDate, @UpdateDate, @Feeding_Time End Close CursorD Deallocate CursorDGO------------------------------------------------
USE [ChangHongWMS612]
GO/****** Object: StoredProcedure [dbo].[st_WMS_JobGenRptMaterialInOut] Script Date: 05/12/2015 17:42:39 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
GO/*
Create by:hz Create time:2014-12-18 Remark:原料投入产出日报表 原存储过程名:Job_GenRptMaterialInOut --2014-12-18 hz ChiefMESNew 修改为ChangHong_612 exec st_WMS_JobGenRptMaterialInOut '2015-01-27 08:30:00'*/ALTER Proc [dbo].[st_WMS_JobGenRptMaterialInOut]( @CurrDate varchar(19) = '2014-11-30 08:30:00'--@r_Date varchar(19) = '2014-11-30 08:30:00')as declare @Date varchar(10), @NextDate varchar(10) declare @BeginDate varchar(19), @EndDate varchar(19) if(@CurrDate = '') begin set @Date = convert(varchar(10),dateadd(day,-1,GETDATE()),121) end else begin select @Date=convert(varchar(10), @CurrDate,120) end select @NextDate = CONVERT(varchar(10),dateadd(day,1,@Date),121) declare @MonthFrom varchar(10) select @MonthFrom = convert(varchar(10),DATEADD(day,-DATEPART(day, @CurrDate)+1,@CurrDate ),121) --月初select @BeginDate=@Date+' 08:30:00', @EndDate=@NextDate+' 08:30:00'
delete from WMS_RptMaterialInOutDaily where CurrDate = @Date if object_id('tempdb..#t_RptMaterialInOutDaily') is not null drop table #t_RptMaterialInOutDaily select * into #t_RptMaterialInOutDaily from WMS_RptMaterialInOutDaily where 1 = 0 create table #t_Material(MaterialNO varchar(50),MaterialName varchar(100), SupplyCode varchar(50), FeedGrade varchar(50), MaterialClass varchar(100), Unit varchar(50)) insert into #t_Material(MaterialNO,MaterialName, SupplyCode,FeedGrade,MaterialClass, Unit) select distinct MaterialNO, MaterialName, SupNO, FeedGrade, MaterialsClassification, Units from ChangHong_612.dbo.MES_MaterialPacketinsert into #t_RptMaterialInOutDaily(CurrDate, MaterialNO, MaterialName, SupplyNO,MaterialClass, FeedGrade, Unit, Process, CostCenter)
select @Date, MaterialNO, MaterialName, SupplyCode,MaterialClass, FeedGrade, Unit, '612','8002107' from #t_Material--仓库本日领入
update t1 set TodayLinInQty =(select SUM(m.MQty) from KIT_MaterialStockIn m where m.MQty>0 and charindex('转入',isnull(m.StateDesc,''))=0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1--仓库月累计领入
update t1 set MonthLinInQty =(select SUM(m.MQty) from KIT_MaterialStockIn m where m.MQty>0 and charindex('转入',isnull(m.StateDesc,''))=0 and isnull(m.CheckTime,m.CreateTime) between @MonthFrom and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --仓库本日转入 update t1 set TodayZhuanInQty =(select SUM(m.MQty) from KIT_MaterialStockIn m where m.MQty>0 and charindex('转入',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --仓库月累计转入 update t1 set MonthZhuanInQty =(select SUM(m.MQty) from KIT_MaterialStockIn m where m.MQty>0 and charindex('转入',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @MonthFrom and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --转入单位 update t1 set ZhunInUnit =(select max(Unit) from KIT_MaterialStockIn m where m.MQty>0 and charindex('转入',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --select * from WMS_RptMaterialInOutDaily where CurrDate = '2014-11-19' --select * from KIT_MaterialStockIn --select * from WMS_RptMaterialInOutDaily where isnull(TodayZhuanOutQty,0)<>isnull(BZTodayLinIn,0) and CurrDate='2014-11-20' --select * from KIT_MaterialStockIn where MATERIAL='810498396' and C_DATE>='2014-11-20' --仓库本日发出 update t1 set TodayOutQty =(select SUM(isnull(m.GQty,0)-ISNULL(m.RQty,0)) from KIT_MaterialStockIn m where (m.GQty>0 or m.RQty>0) and charindex('转移',isnull(m.StateDesc,''))=0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --仓库月累计发出 update t1 set MonthOutQty =(select SUM(isnull(m.GQty,0)-ISNULL(m.RQty,0)) from KIT_MaterialStockIn m where (m.GQty>0 or m.RQty>0) and charindex('转移',isnull(m.StateDesc,''))=0 and isnull(m.CheckTime,m.CreateTime) between @MonthFrom and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1--仓库本日转出 update t1 set TodayZhuanOutQty =(select SUM(m.GQty) from KIT_MaterialStockIn m where m.GQty>0 and charindex('转移',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --仓库月累计转出 update t1 set MonthZhuanOutQty =(select SUM(m.GQty) from KIT_MaterialStockIn m where m.GQty>0 and charindex('转移',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @MonthFrom and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --转出单位 update t1 set ZhunOutUnit =(select max(m.Unit) from KIT_MaterialStockIn m where m.GQty>0 and charindex('转移',isnull(m.StateDesc,''))>0 and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --昨日结存 declare @LastDate varchar(10), @LastBeginDate varchar(19), @LastEndDate varchar(19) select @LastDate = CONVERT(varchar(10),dateadd(day,-1,convert(datetime,@date)),121) select @LastBeginDate = @LastDate+' 08:30:00', @LastEndDate = @Date+' 08:30:00' update t set LastStockQty =( select top 1 r.MQty from KIT_StockBatchCheck r where State =5 and isnull(r.CheckTime,r.CreateTime) between @LastBeginDate and @LastEndDate and r.RawNo = t.MaterialNO and r.SupplyNo = t.SupplyNO order by r.CreateTime desc) from #t_RptMaterialInOutDaily t update t set LastStockQty =(select top 1 TodayStockQty from WMS_RptMaterialInOutDaily r where r.SupplyNO = t.SupplyNO and r.MaterialNO = t.MaterialNO and r.CurrDate = @LastDate) from #t_RptMaterialInOutDaily t where t.LastStockQty is null --班组本日领入 update t1 set BZTodayLinIn =(select SUM(isnull(m.MQty,0)-ISNULL(m.RQty,0)) from KIT_StockDetail m where (m.MQty>0 or m.RQty>0) and isnull(m.CheckTime,m.CreateTime) between @BeginDate and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --班组月累计领入 update t1 set BZMonthLinIn =(select SUM(isnull(m.MQty,0)-ISNULL(m.RQty,0)) from KIT_StockDetail m where (m.MQty>0 or m.RQty>0) and isnull(m.CheckTime,m.CreateTime)between @MonthFrom and @EndDate and m.RawNO = t1.MaterialNO and m.SupplyNO = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1
--班组本日加料
update t1 set BZTodayPutQty =(select SUM(m.feedingQty) from ChangHong_612.dbo.MES_Material m where m.feedingTime between @BeginDate and @EndDate and m.MaterialNO = t1.MaterialNO and m.supplier = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1--班组月累计加料
update t1 set BZMonthPutQty =(select SUM(m.feedingQty) from ChangHong_612.dbo.MES_Material m where m.feedingTime between @MonthFrom and @EndDate and m.MaterialNO = t1.MaterialNO and m.supplier = t1.SupplyNO ) from #t_RptMaterialInOutDaily t1 --班组昨日结存 update t1 set BZLastStockQty =(select top 1 MQty from KIT_StockCheck r where State =6 and isnull(r.CheckTime,r.CreateTime) between @LastBeginDate and @LastEndDate and r.RawNo = t1.MaterialNO and r.SupplyNo = t1.SupplyNO order by r.CreateTime desc) from #t_RptMaterialInOutDaily t1 update t set BZLastStockQty =(select top 1 BZTodayStock from WMS_RptMaterialInOutDaily r where r.SupplyNO = t.SupplyNO and r.MaterialNO = t.MaterialNO and r.CurrDate = @LastDate) from #t_RptMaterialInOutDaily t where BZLastStockQty is null --班组本日结存 update t set BZTodayStock = isnull(BZLastStockQty,0)+ISNULL(BZTodayLinIn,0)-ISNULL(BZTodayPutQty,0) from #t_RptMaterialInOutDaily t --料斗期初余料 update t1 set HopperTermBeginQty =isnull((select top 1 HopperTermEndQty from WMS_RptMaterialInOutDaily r where r.SupplyNO = t1.SupplyNO and r.MaterialNO = t1.MaterialNO and r.CurrDate = @LastDate ),0) from #t_RptMaterialInOutDaily t1--料斗本日加料 update t1 set HopperTodayPutQty = BZTodayPutQty from #t_RptMaterialInOutDaily t1
--仓库本日结存:仓库昨日结存+仓库本日领入+仓库本日转入-仓库本日发出-仓库本日转出
update t set TodayStockQty =isnull(LastStockQty,0)+isnull(TodayLinInQty,0)+isnull(TodayZhuanInQty,0)-isnull(TodayOutQty,0)-isnull(TodayZhuanOutQty,0) from #t_RptMaterialInOutDaily t --update t1 set HopperMonthPutQty =(select SUM(m.T_NUM) -- from Kitting.dbo.kitting_h_p_no_num m -- where m.C_DATE between @MonthFrom and @EndDate -- and m.HH_GNO = t1.MaterialNO and m.SUP_NO = t1.SupplyNO -- ) --from #t_RptMaterialInOutDaily t1 --本日消耗 update t1 set TodayConsumQty =isnull(TodayConsumQty,0)+ isnull((select SUM(r.ActualQty*(MaterialQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo1 = t1.MaterialNO and r.SupplyCode = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1update t1 set TodayConsumQty =isnull(TodayConsumQty,0)+ isnull((select SUM(r.ActualQty*(MaterialQty2/r.MaterialSumQty))
from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo2 = t1.MaterialNO and r.SupplyCode2 = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty =isnull(TodayConsumQty,0)+ isnull((select SUM(r.ActualQty*(MaterialQty3/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo3 = t1.MaterialNO and r.SupplyCode3 = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty = isnull(TodayConsumQty,0)+isnull((select SUM(r.ActualQty*(r.SheMuQty/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.SheMuCode = t1.MaterialNO and r.SheMuSupply = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty = isnull(TodayConsumQty,0)+isnull((select SUM(r.ActualQty*(r.SheMuQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.SheMuCode1 = t1.MaterialNO and r.SheMu1Supply = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty = isnull(TodayConsumQty,0)+isnull((select SUM(r.ActualQty*(r.CycleQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo1 = t1.MaterialNO and r.Cycle1Supply = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty = isnull(TodayConsumQty,0)+isnull((select SUM(r.ActualQty*(r.CycleQty2/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo2 = t1.MaterialNO and r.Cycle2Supply = t1.SupplyNO ),0) from #t_RptMaterialInOutDaily t1 update t1 set TodayConsumQty = isnull(TodayConsumQty,0)+isnull((select SUM(r.ActualQty*(r.CycleQty3/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo3 = t1.MaterialNO and r.Cycle3Supply = t1.SupplyNO and r.MaterialSumQty>0 ),0) from #t_RptMaterialInOutDaily t1 --料斗期末余料:取当日投料报表班组余料 update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.MaterialQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo1 = t1.MaterialNO and r.MaterialSumQty>0 and r.SupplyCode = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1 update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.MaterialQty2/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo2 = t1.MaterialNO and r.MaterialSumQty>0 and r.SupplyCode2 = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.MaterialQty3/r.MaterialSumQty))
from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.MaterialNo3 = t1.MaterialNO and r.MaterialSumQty>0 and r.SupplyCode3 = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1 update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.SheMuQty/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.SheMuCode = t1.MaterialNO and r.MaterialSumQty>0 and r.SheMuSupply = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1 update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.SheMuQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.SheMuCode1 = t1.MaterialNO and r.MaterialSumQty>0 and r.SheMu1Supply = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1 update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.CycleQty1/r.MaterialSumQty)) from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo1 = t1.MaterialNO and r.MaterialSumQty>0 and r.Cycle1Supply = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.CycleQty2/r.MaterialSumQty))
from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo1 = t1.MaterialNO and r.MaterialSumQty>0 and r.Cycle2Supply = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1update t1 set HopperTermEndQty = isnull(HopperTermEndQty,0)+ isnull((select SUM(r.JiaoPlusQty*(r.CycleQty3/r.MaterialSumQty))
from WMS_RptMaterialPutDaily r where r.CurrDate = t1.CurrDate and r.CycleNo1 = t1.MaterialNO and r.MaterialSumQty>0 and r.Cycle3Supply = t1.SupplyNO),0) from #t_RptMaterialInOutDaily t1 --混料结存(暂存) update t1 set TodayMixQty = (select SUM(r.OverQty) from KIT_MixOver r where r.RawNO = t1.MaterialNO and STATE = 0 and r.SupplyNO = t1.SupplyNO and r.MixTime <=@EndDate ) from #t_RptMaterialInOutDaily t1 --合计库存:仓库本日结存+班组本日结存+料斗本日结存+暂存库存 update t1 set SumStockQty = isnull(TodayStockQty,0)+isnull(BZTodayStock,0)+isnull(HopperTermEndQty,0)+ISNULL(TodayMixQty,0) from #t_RptMaterialInOutDaily t1 --合计期初库存 update t1 set SumTermBeginQty = (select top 1 SumStockQty from WMS_RptMaterialInOutDaily r where r.SupplyNO = t1.SupplyNO and r.MaterialNO = t1.MaterialNO and r.CurrDate <@MonthFrom) from #t_RptMaterialInOutDaily t1 --期初库存 update t1 set TermBeginQty = (select top 1 TodayStockQty from WMS_RptMaterialInOutDaily r where r.SupplyNO = t1.SupplyNO and r.MaterialNO = t1.MaterialNO and r.CurrDate <@MonthFrom) from #t_RptMaterialInOutDaily t1 delete from #t_RptMaterialInOutDaily where isnull(TodayStockQty,0) = 0 and isnull(TodayOutQty,0) = 0 and isnull(LastStockQty,0) = 0 and isnull(ZhunInUnit,0)= 0 and ISNULL(SumStockQty,0) = 0 and isnull(BZTodayStock,0) = 0 and ISNULL(TodayZhuanInQty,0)=0 and ISNULL(TodayZhuanOutQty,0)= 0 and ISNULL(BZTodayLinIn,0)=0 and ISNULL(HopperTodayPutQty,0) = 0 and ISNULL(BZTodayPutQty,0)=0declare @TodayAffactRows int
insert into WMS_RptMaterialInOutDaily([CurrDate],[SupplyNO] ,[MaterialNO],[MaterialName],[MaterialClass] ,[FeedGrade],[Unit],[SumStockQty],[SumTermBeginQty] ,[TermBeginQty],[LastStockQty],[MonthLinInQty] ,[TodayZhuanInQty],[MonthZhuanInQty],[ZhunInUnit] ,[TodayOutQty],[MonthOutQty],[TodayZhuanOutQty] ,[MonthZhuanOutQty],[ZhunOutUnit],[TodayStockQty] ,[BZSumStockQty],[BZLastStockQty] ,[BZTodayLinIn],[BZMonthLinIn] ,[BZTodayPutQty],[BZMonthPutQty] ,[BZTodayStock],[HopperTermBeginQty] ,[HopperTodayPutQty],[HopperMonthPutQty],[TodayConsumQty] ,[HopperTermEndQty],[AdjustQty] ,[Remark],[Process],[CostCenter],[TodayLinInQty],TodayMixQty ) select [CurrDate],[SupplyNO] ,[MaterialNO],[MaterialName],[MaterialClass] ,[FeedGrade],[Unit],[SumStockQty],[SumTermBeginQty] ,[TermBeginQty],[LastStockQty],[MonthLinInQty] ,[TodayZhuanInQty],[MonthZhuanInQty],[ZhunInUnit] ,[TodayOutQty],[MonthOutQty],[TodayZhuanOutQty] ,[MonthZhuanOutQty],[ZhunOutUnit],[TodayStockQty] ,[BZSumStockQty],[BZLastStockQty] ,[BZTodayLinIn],[BZMonthLinIn] ,[BZTodayPutQty],[BZMonthPutQty] ,[BZTodayStock],[HopperTermBeginQty] ,[HopperTodayPutQty],[HopperMonthPutQty],[TodayConsumQty] ,[HopperTermEndQty],[AdjustQty] ,[Remark],[Process],[CostCenter],[TodayLinInQty],TodayMixQty from #t_RptMaterialInOutDailyselect @TodayAffactRows = @@ROWCOUNT
if(@CurrDate = '') set @CurrDate =@CurrDate if(not exists(select * from WMS_JobDoRpt where JobName = 'RptMaterialInOutDaily' and CurrDate = @CurrDate)) begin insert into WMS_JobDoRpt(JobName,CurrDate,UpdateTime) select 'RptMaterialInOutDaily',@CurrDate ,GETDATE() end else begin update WMS_JobDoRpt set Updatetime = getdate() ,LastAffactRows =@TodayAffactRows where JobName = 'RptMaterialInOutDaily' and CurrDate = @CurrDate end -- select * from RptMaterialInOutDaily drop table #t_RptMaterialInOutDailyGO