--exec mycyy_Stockcheck 1942,0,41
--@FInterid 单据内码
--@CheckBz 审核标志 0 反审核  1 审核
--@ftrantype 单据类型

ALTER Proc [dbo].[mycyy_Stockcheck]
(@FInterid int,@CheckBz Int,@ftrantype int)
  as Begin
CREATE TABLE #TempBill
( FBrNo VARCHAR(10) NOT NULL DEFAULT(''),
FInterID INT NOT NULL DEFAULT(0),
FEntryID INT NOT NULL DEFAULT(0),
FTranType INT NOT NULL DEFAULT(0),
FItemID INT NOT NULL DEFAULT(0),
FBatchNo NVARCHAR(255) NOT NULL DEFAULT(''),
FAuxPropID INT NOT NULL DEFAULT(0),
FStockID INT NOT NULL DEFAULT(0),
FStockPlaceID INT NOT NULL DEFAULT(0),
FKFPeriod INT NOT NULL DEFAULT(0),
FKFDate VARCHAR(20) NOT NULL DEFAULT(''),
FQty DECIMAL(28,10) NOT NULL DEFAULT(0),
FSecQty DECIMAL(28,10) NOT NULL DEFAULT(0))
IF (@CheckBz=1 and @ftrantype in (1,2,5,10,40,41))  or  (@CheckBz=0 and  @ftrantype in (21,24,28,29,43))
Begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,@ftrantype AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FDCStockID,ISNULL(u1.FDCSPID,0) AS FDCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,
LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,1*u1.FQty AS FQty,1*u1.FSecQty AS FSecQty
FROM ICStockBillEntry u1
WHERE u1.FInterID=@FInterid
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,@ftrantype AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FSCStockID,0) AS FSCStockID,ISNULL(u1.FSCSPID,0) AS FSCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,
LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,
1*(-1)*u1.FQty AS FQty,1*(-1)*u1.FSecQty AS FSecQty
FROM ICStockBillEntry u1
WHERE u1.FInterID=@FInterid
End

IF (@CheckBz=0 and @ftrantype in (1,2,5,10,40,41))  or  (@CheckBz=1 and  @ftrantype in (21,24,28,29,43))
Begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,@ftrantype AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FSCStockID,0) AS FSCStockID,ISNULL(u1.FDCSPID,0) AS FDCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,
LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,
1*u1.FQty AS FQty,1*u1.FSecQty AS FSecQty
FROM ICStockBillEntry u1
WHERE u1.FInterID=@FInterid
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,@ftrantype AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FDCStockID,ISNULL(u1.FSCSPID,0) AS FSCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,
LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,
1*(-1)*u1.FQty AS FQty,1*(-1)*u1.FSecQty AS FSecQty
FROM ICStockBillEntry u1
WHERE u1.FInterID=@FInterid
End

UPDATE t1 SET t1.FQty=t1.FQty+u1.FQty,t1.FSecQty=t1.FSecQty+u1.FSecQty
FROM ICInventory t1 INNER JOIN
(SELECT FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate
,SUM(FQty) AS FQty,SUM(FSecQty) AS FSecQty FROM #TempBill
GROUP BY FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate) u1
ON t1.FItemID=u1.FItemID AND t1.FBatchNo=u1.FBatchNo AND t1.FAuxPropID=u1.FAuxPropID
AND t1.FStockID=u1.FStockID AND t1.FStockPlaceID=u1.FStockPlaceID AND t1.FKFPeriod=u1.FKFPeriod AND t1.FKFDate=u1.FKFDate

DELETE u1 FROM ICInventory t1 INNER JOIN #TempBill u1
ON t1.FItemID=u1.FItemID AND t1.FBatchNo=u1.FBatchNo AND t1.FAuxPropID=u1.FAuxPropID
AND t1.FStockID=u1.FStockID AND t1.FStockPlaceID=u1.FStockPlaceID
AND t1.FKFPeriod=u1.FKFPeriod AND t1.FKFDate=u1.FKFDate
INSERT INTO ICInventory(FBrNo,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,
SUM(FQty) AS FQty,SUM(FSecQty) AS FSecQty FROM #TempBill
GROUP BY FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate
DROP TABLE #TempBill
End