/*8 设置清单(预出库) 更新临时表中库存、在单量、当前订单量的数量(所有商品目录)*/ /*先取出销售订单清单中的订单数量,库存中的数量,预出库单中的数量,更新临时表中的数量*/ ALTER PROCEDURE SMSP_RefreshTempSaleOrderLog @OrderOutOdd01 varchar(20) ASDECLARE @Goods01 varchar(30), /*商品编号*/ @Num numeric(11,2), /*当前某一个订单清单的订货数量*/ @ResNum numeric(11,2), /*当前某一个订单清单的可发数量*/ @SaveNum Numeric(11,2), /*某一个商品的库存数量*/ @OrderNum Numeric(11,2), /*当前所有预出库清单的某一个预出库数量*/ @Goods02 varchar(30), /*商品名称*/ @Goods03 varchar(15), /*图(型)号*/ @Goods04 varchar(15), /*原厂编号*/ @Goods05 varchar(20), /*适用对象*/ @Goods11 varchar(4), /*计量单位*/ @OutPrice numeric(15,6) /*调拨价*/ /*用商品目录创建游标*/ DECLARE Goods CURSOR LOCAL FOR SELECT Goods01,Goods02,Goods03,Goods04,Goods05,Goods11,Goods23 FROM Goods OPEN Goods FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice WHILE @@FETCH_STATUS = 0 BEGIN
/*汇总库存中的结存数*/ SELECT @SaveNum=SUM(Storage12) FROM Storage WHERE Storage12>0 AND Goods01 = @Goods01 /*子查询 对应于汇总库存中的商品编号,汇总预出库清单中的商品数量,销售订单经过预出库,转化为预出库单,锁定库存数量 SELECT @OrderNum=SUM(OrderOutLog02) FROM OrderOutLog WHERE Goods01 = @Goods01 AND OrderOutOdd01 IN (SELECT OrderOutOdd01 FROM OrderOutOdd WHERE OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检'))*/
/*连接的性能比子查询要好*/ SELECT @OrderNum=SUM(OrderOutLog.OrderOutLog02) FROM OrderOutLog JOIN OrderOutOdd ON OrderOutLog.OrderOutOdd01=OrderOutOdd.OrderOutOdd01 WHERE OrderOutOdd.OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检') AND OrderOutLog.Goods01=@Goods01
/*对应于销售订单清单中的商品数量*/ SELECT @Num=SUM(OrderOutLog02) FROM OrderOutLog WHERE OrderOutOdd01= @OrderOutOdd01 AND Goods01 = @Goods01 AND OrderOutLog02>0 /*对应于销售订单清单中的可发数量*/ SET @ResNum =ISNUll(@SaveNum,0)-ISNULL(@OrderNum,0) IF @ResNum=0 SET @ResNum = NULL
/*更新*/ UPDATE #TempSaleOrderLog SET SaveNum= @SaveNum, OrderNum= @OrderNum, Num=@Num,ResNum=@ResNum WHERE Goods01 = @Goods01 FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice END CLOSE Goods DEALLOCATE Goods/*---------------*/ GO
/*用创建游标*/ DECLARE mycursor CURSOR LOCAL FOR SELECT c1 FROM T1 OPEN mycursor FETCH NEXT FROM mycursor INTO @var WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM mycursor INTO @var END CLOSE mycursor DEALLOCATE mycursor
这是一用品入库的游标 Declare @ThingCode Varchar(8),@Count Numeric(8),@amount Numeric(8,2),@BuyPrice Numeric(8,2),@BillNum Varchar(30) Declare @Wbm Varchar(4),@Pym Varchar(4),@ThingSmallCode Varchar(5),@Result Varchar(30),@Unit Varchar(10),@WarehouseCode Varchar(4) DECLARE CurSor1 CURSOR FOR Select BillNum,ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,BuyPrice,WarehouseCode From dbo.T_yprkxxk Where SelectSign='√' And JoinSign is Null OPEN CurSor1 Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode WHILE @@FETCH_STATUS=0 BEGIN Select @Count=Count(*) From T_lbypk Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode If @Count>0 Begin Update T_lbypk Set amount=amount+@amount,Price=Price+Round(@BuyPrice*@amount,2) Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode
End Else Begin Insert Into T_lbypk(ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,Price,WarehouseCode) Values(@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,Round(@BuyPrice*@amount,2) ,@WarehouseCode) End Select @BillNum=Null,@ThingCode=Null,@Wbm=Null,@Pym=Null,@ThingSmallCode=Null,@Result=Null,@Unit=Null,@amount=Null,@BuyPrice=Null,@WarehouseCode=Null Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode END Close CurSor1 Deallocate CurSor1
/*先取出销售订单清单中的订单数量,库存中的数量,预出库单中的数量,更新临时表中的数量*/
ALTER PROCEDURE SMSP_RefreshTempSaleOrderLog
@OrderOutOdd01 varchar(20)
ASDECLARE
@Goods01 varchar(30), /*商品编号*/
@Num numeric(11,2), /*当前某一个订单清单的订货数量*/
@ResNum numeric(11,2), /*当前某一个订单清单的可发数量*/
@SaveNum Numeric(11,2), /*某一个商品的库存数量*/
@OrderNum Numeric(11,2), /*当前所有预出库清单的某一个预出库数量*/
@Goods02 varchar(30), /*商品名称*/
@Goods03 varchar(15), /*图(型)号*/
@Goods04 varchar(15), /*原厂编号*/
@Goods05 varchar(20), /*适用对象*/
@Goods11 varchar(4), /*计量单位*/
@OutPrice numeric(15,6) /*调拨价*/ /*用商品目录创建游标*/
DECLARE Goods CURSOR LOCAL FOR
SELECT Goods01,Goods02,Goods03,Goods04,Goods05,Goods11,Goods23
FROM Goods OPEN Goods
FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice
WHILE @@FETCH_STATUS = 0
BEGIN
/*汇总库存中的结存数*/
SELECT @SaveNum=SUM(Storage12)
FROM Storage
WHERE Storage12>0
AND Goods01 = @Goods01
/*子查询 对应于汇总库存中的商品编号,汇总预出库清单中的商品数量,销售订单经过预出库,转化为预出库单,锁定库存数量
SELECT @OrderNum=SUM(OrderOutLog02)
FROM OrderOutLog
WHERE Goods01 = @Goods01
AND OrderOutOdd01 IN
(SELECT OrderOutOdd01 FROM OrderOutOdd WHERE OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检'))*/
/*连接的性能比子查询要好*/
SELECT @OrderNum=SUM(OrderOutLog.OrderOutLog02)
FROM OrderOutLog
JOIN OrderOutOdd ON OrderOutLog.OrderOutOdd01=OrderOutOdd.OrderOutOdd01
WHERE OrderOutOdd.OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检')
AND OrderOutLog.Goods01=@Goods01
/*对应于销售订单清单中的商品数量*/
SELECT @Num=SUM(OrderOutLog02)
FROM OrderOutLog
WHERE OrderOutOdd01= @OrderOutOdd01
AND Goods01 = @Goods01
AND OrderOutLog02>0 /*对应于销售订单清单中的可发数量*/
SET @ResNum =ISNUll(@SaveNum,0)-ISNULL(@OrderNum,0)
IF @ResNum=0
SET @ResNum = NULL
/*更新*/
UPDATE #TempSaleOrderLog SET SaveNum= @SaveNum, OrderNum= @OrderNum, Num=@Num,ResNum=@ResNum
WHERE Goods01 = @Goods01 FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice
END
CLOSE Goods
DEALLOCATE Goods/*---------------*/
GO
DECLARE mycursor CURSOR LOCAL FOR
SELECT c1
FROM T1 OPEN mycursor
FETCH NEXT FROM mycursor INTO @var
WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM mycursor INTO @var END
CLOSE mycursor
DEALLOCATE mycursor
这是一用品入库的游标
Declare @ThingCode Varchar(8),@Count Numeric(8),@amount Numeric(8,2),@BuyPrice Numeric(8,2),@BillNum Varchar(30)
Declare @Wbm Varchar(4),@Pym Varchar(4),@ThingSmallCode Varchar(5),@Result Varchar(30),@Unit Varchar(10),@WarehouseCode Varchar(4)
DECLARE CurSor1 CURSOR
FOR Select BillNum,ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,BuyPrice,WarehouseCode From dbo.T_yprkxxk Where SelectSign='√' And JoinSign is Null
OPEN CurSor1
Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode
WHILE @@FETCH_STATUS=0
BEGIN
Select @Count=Count(*) From T_lbypk Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode
If @Count>0
Begin
Update T_lbypk Set amount=amount+@amount,Price=Price+Round(@BuyPrice*@amount,2) Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode
End Else
Begin
Insert Into T_lbypk(ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,Price,WarehouseCode)
Values(@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,Round(@BuyPrice*@amount,2) ,@WarehouseCode)
End Select @BillNum=Null,@ThingCode=Null,@Wbm=Null,@Pym=Null,@ThingSmallCode=Null,@Result=Null,@Unit=Null,@amount=Null,@BuyPrice=Null,@WarehouseCode=Null
Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode
END
Close CurSor1
Deallocate CurSor1