USE [NFGC_Workflow] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Ben.Jiang -- Create date:2011/9/13 -- Description:添加库存警告 -- ============================================= ALTER PROCEDURE [dbo].[Insert_AlltblStockLimit] @Company NVARCHAR(50), @MaxEnable bit, @MinEnable bit, @Max NVARCHAR(50), @Min NVARCHAR(50), @MaxAlertAheadDays int, @MinAlertAheadDays int AS BEGIN DECLARE @CustomerCur CURSOR;--声明外层游标 DECLARE @Id NVARCHAR(50),@ItemCode NVARCHAR(50),@customer NVARCHAR(50); --获取所有客户记录 SET @CustomerCur=CURSOR FOR select distinct [cust-num] from(SELECT A.[cust-num], ISNULL(B.name,A.[name]) as name from dbo.Erp_custaddr a LEFT JOIN dbo.Erp_custaddr_CHS B ON A.[cust-num]=B.[cust-num] AND A.[cust-seq]=B.[cust-seq] where A.[credit-hold]='False' and A.[cust-seq]=0) as cust
OPEN @CustomerCur --打开外层游标 FETCH NEXT FROM @CustomerCur INTO @customer--提取外层游标行 WHILE(@@FETCH_STATUS=0) BEGIN
DECLARE @varCur CURSOR;--声明内层游标 --获取每一个客户的商品记录 SET @varCur = CURSOR FOR SELECT DISTINCT item FROM dbo.Erp_itemcust AS A INNER JOIN [Erp_ux-customer] AS B ON A.[cust-num]=B.[cust-num] AND B.[cust-num] IS NOT NULL AND LEN(A.[cust-num])>0 AND B.[cust-seq]=0 INNER JOIN Erp_imsAs AS C ON C.sItem=A.item AND C.sCust=A.[cust-num] AND C.cActFlg = '0' WHERE A.item IS NOT NULL AND LEN(A.item)>0 AND A.[cust-num]=@customer;
OPEN @varCur --打开内层游标 FETCH NEXT FROM @varCur INTO @ItemCode WHILE(@@FETCH_STATUS=0) BEGIN
IF (exists(SELECT Id from tblStockLimit where Company=@Company and Custnum=@customer and ItemCode=@ItemCode)) --存在该记录更新记录 BEGIN SET @Id=(SELECT Id from tblStockLimit where Company=@Company and Custnum=@customer and ItemCode=@ItemCode) update tblStockLimit set MaxEnable=@MaxEnable,Max=case when @Max<>'' then @Max else null end, MaxAlertAheadDays=case when @MaxAlertAheadDays<>'' then @MaxAlertAheadDays else null end, MinEnable=@MinEnable,Min=case when @Min<>'' then @Min else null end, MinAlertAheadDays=case when @MinAlertAheadDays<>'' then @MinAlertAheadDays else null end where Id=@Id; END
ELSE --不存在该记录新增记录 BEGIN insert into tblStockLimit(Company,Custnum,ItemCode,MaxEnable,Max,MaxAlertAheadDays,MinEnable,Min,MinAlertAheadDays) values('NFGS',@customer,@ItemCode,@MaxEnable,case when @Max<>'' then @Max else null end, case when @MaxAlertAheadDays<>'' then @MaxAlertAheadDays else null end,@MinEnable, case when @Min<>'' then @Min else null end,case when @MinAlertAheadDays<>'' then @MinAlertAheadDays else null end); END
FETCH NEXT FROM @varCur INTO @ItemCode--内层游标向下移动一行 END CLOSE @varCur DEALLOCATE @varCur FETCH NEXT FROM @CustomerCur INTO @customer--内层游标结束后,外层游标继续向下移动一行 END