/****** Object: StoredProcedure [dbo].[Tecul_UpdateICInventory_Kanban] Script Date: 06/26/2013 09:25:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Tecul_UpdateICInventory_Kanban]
@FInterID int, --单据体ＩＤ
@ClassID INT, --单据类型 (24,21,29,41,28,10,5,2,1)
@UpdateResult int output -- 1更新成功，0更新失败或没有更新
--,@UpdateResult2 nvarchar(50) output -- 提示信息
as
SET NOCOUNT ON
t @UpdateResult=0
-- ClassID判断 指定更新类型
if @ClassID in(24,21,29,41,28,10,5,2,1)
begin
--FInterID判断
if exists (lect FinterID from icstockbill where FInterID=@FInterID and FTranType=@classID)
BEGIN
--开始更新即时库存
CREATE TABLE #TempBill
(FBrNo VARCHAR(10) NOT NULL DEFAULT(''),
pybk
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 (@ClassID =24) --领料单-
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,24 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FSCStockID,0) AS FSCStockID,ISNULL(u1.FDCSPID,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 and @ClassID =24
end
if( @ClassID =21) --销售出库-
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,21 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FSCStockID,ISNULL(u1.FDCSPID,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 and @ClassID =21
end
if (@ClassID =29) --其它出库-
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.F
InterID,u1.FEntryID,29 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FSCStockID,ISNULL(u1.FDCSPID,0) AS FSCSPID,I
SNULL(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 and @ClassID =29
end
if( @ClassID =41) --调拨单
begin
--插入接收仓库的物料数据 +
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,41 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 and @ClassID =41
--插入发出仓库的物料数据 -
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,41 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 and @ClassID =41
end
if (@ClassID =28) --20100907-28委外出库-
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStoc
亲爱的英文单词kID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
suchSELECT '',u1.FInterID,u1.FEntryID,28 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,
u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FSCStockID,ISNULL(u1.FDCSPID,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 and @ClassID =28
英语星期一到星期天end
if (@ClassID =10) --20100907-10其它入库+
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,10 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 and @ClassID =10
end
if (@ClassID =5) --20100907-5委外入库+
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,5 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 and @ClassID =5
end
if (@ClassID =2) --20100907-2产品入库+
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',@FInterID,u1.FEntryID,2 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 and @ClassID =2
end
if (@ClassID =1) --20100907-1外购入库+
begin
INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FQty,FSecQty)
SELECT '',u1.FInterID,u1.FEntryID,1 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 and @ClassID =1
end
--负库存判断 (原即时库存+待更新库存>=0 或允许负库存)
if not exists
(lect sum(t1.FQty ) as fqty,u1.FQty as ufqty
FROM ICInventory t1
right 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
right join (lect fitemID,funderstock from T_stock) t on t.fitemID=u1.FStockID
where isnull(t1.FQty,0)+isnull(u1.FQty,0)<0
and t.funderstock=0 --
GROUP BY t1.FItemID,t1.FBatchNo,t1.FAuxPropID,t1.FStockID,t1.FStockPlaceID,t1.FKFPeriod,t1.FKFDate,u1.FQty
)
begin
begin tran
--更新即时库存
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
if(@@error>0)
艾米纳姆好听的歌begin
rollback tran
t @UpdateResult=0
--t @UpdateResult2='更新失败，'
end
el --更新即时库存成功
begin
commit tran
t @UpdateResult=1
--t @UpdateResult2='更新成功，'
end
end
el
北大青鸟上海
begin
t @UpdateResult=0
--t @UpdateResult2='负库存或不够出库 且不允许负库存时，更新没有执行...'
end
END
el
begin
t @UpdateResult=0
--t @UpdateResult2='窗休ID不存在，更新没有执行...'
end
end
el
begin
t @UpdateResult=0
--t @UpdateResult2='非指定类型，即时库存不更新...'
end
GO
