CREATE PROCEDURE UpDateStorage
(
@iGoodsSn varchar(20), --物品Sn
@Kind smallint, --类别
@iLib smallint, --仓库Sn
@cColor varchar(20), --颜色
@cDetailNo varchar(10), --明细编号
@cCorckNo varchar(20), --批号缸号
@cGrade varchar(20), --等级
@fQuantity float, --数量
@iRelationSn varchar(20),
@iCompanySn varchar(20)
)
AS
BEGIN
BEGIN TRAN
declare @fNowTotalStock float, --现有存量
@fTransferNum float, --未领用量
@fAlreadyNum float, --已购未入数
@iCentStorageSn varchar(20), --分仓资料Sn
@siColorWaterSn int, --颜色表Sn
@iStorageColorSn varchar(20), --颜色分仓Sn
@iStorageListSn varchar(20), --清单分仓Sn
@eve varchar(20) --帐套
set @eve = SubString(@iGoodsSn, 1, 3)
--////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--88 订单结案未领用量更新 99 采购单结案已购未入数更新 100 委外进货退货无清单 101生产领料退料无明细 102委外领料退料无明细 103日常领料退料无明细
--TGoods物品表更新
select @fNowTotalStock=fNowTotalStock,@fTransferNum=fTransferNum,@fAlreadyNum=fAlreadyNum from TGoods where iGoodsSn=@iGoodsSn
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=10 or @Kind=100 or @Kind= 12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=101 or @Kind=102 or @Kind=103 or @Kind=19)
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
--减未领用量
if(@Kind=3 or @Kind=6 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=20 or @Kind=101 or @Kind=102 or @Kind=19 or @Kind=20 or @Kind=88)
begin
set @fTransferNum=@fTransferNum-@fQuantity
end
--加未领用量
if(@Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=22 or @Kind=23 or @Kind=26 or @Kind=27 or @Kind=29)
begin
set @fTransferNum=@fTransferNum+@fQuantity
end
--减已购未入量
if(@Kind=1 or @Kind=99)
begin
set @fAlreadyNum=@fAlreadyNum-@fQuantity
end
--加已购未入量
if(@Kind=2)
begin
set @fAlreadyNum=@fAlreadyNum+@fQuantity
end
update TGoods set fNowTotalStock=@fNowTotalStock,fTransferNum=@fTransferNum, fAlreadyNum=@fAlreadyNum where iGoodsSn=@iGoodsSn
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--初始化
set @fNowTotalStock =0
set @fTransferNum =0
set @fAlreadyNum =0
--TCentStorage分仓资料表更新
select @fNowTotalStock=fNowStock,@iCentStorageSn=iCentStorageSn from TCentStorage where iGoodsSn=@iGoodsSn and siLibNameSn=@iLib
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=101 or @Kind=102 or @Kind=103 or @Kind=19)
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
if(@iCentStorageSn is not null) --分仓有资料,更新
begin
update TCentStorage set fNowStock=@fNowTotalStock where iCentStorageSn=@iCentStorageSn
end
else
--分仓无资料,新增
begin
exec GetNewSn 'TCentStorage', @eve, @iCentStorageSn output
declare @date datetime
set @date=getdate()
exec TCentStorageInsProc @iCentStorageSn,@iGoodsSn,@iLib,@fNowTotalStock,@date
end
--//////////////////////////////////////////////////////////////////////////////////
(
@iGoodsSn varchar(20), --物品Sn
@Kind smallint, --类别
@iLib smallint, --仓库Sn
@cColor varchar(20), --颜色
@cDetailNo varchar(10), --明细编号
@cCorckNo varchar(20), --批号缸号
@cGrade varchar(20), --等级
@fQuantity float, --数量
@iRelationSn varchar(20),
@iCompanySn varchar(20)
)
AS
BEGIN
BEGIN TRAN
declare @fNowTotalStock float, --现有存量
@fTransferNum float, --未领用量
@fAlreadyNum float, --已购未入数
@iCentStorageSn varchar(20), --分仓资料Sn
@siColorWaterSn int, --颜色表Sn
@iStorageColorSn varchar(20), --颜色分仓Sn
@iStorageListSn varchar(20), --清单分仓Sn
@eve varchar(20) --帐套
set @eve = SubString(@iGoodsSn, 1, 3)
--////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--88 订单结案未领用量更新 99 采购单结案已购未入数更新 100 委外进货退货无清单 101生产领料退料无明细 102委外领料退料无明细 103日常领料退料无明细
--TGoods物品表更新
select @fNowTotalStock=fNowTotalStock,@fTransferNum=fTransferNum,@fAlreadyNum=fAlreadyNum from TGoods where iGoodsSn=@iGoodsSn
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=10 or @Kind=100 or @Kind= 12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=101 or @Kind=102 or @Kind=103 or @Kind=19)
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
--减未领用量
if(@Kind=3 or @Kind=6 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=20 or @Kind=101 or @Kind=102 or @Kind=19 or @Kind=20 or @Kind=88)
begin
set @fTransferNum=@fTransferNum-@fQuantity
end
--加未领用量
if(@Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=22 or @Kind=23 or @Kind=26 or @Kind=27 or @Kind=29)
begin
set @fTransferNum=@fTransferNum+@fQuantity
end
--减已购未入量
if(@Kind=1 or @Kind=99)
begin
set @fAlreadyNum=@fAlreadyNum-@fQuantity
end
--加已购未入量
if(@Kind=2)
begin
set @fAlreadyNum=@fAlreadyNum+@fQuantity
end
update TGoods set fNowTotalStock=@fNowTotalStock,fTransferNum=@fTransferNum, fAlreadyNum=@fAlreadyNum where iGoodsSn=@iGoodsSn
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--初始化
set @fNowTotalStock =0
set @fTransferNum =0
set @fAlreadyNum =0
--TCentStorage分仓资料表更新
select @fNowTotalStock=fNowStock,@iCentStorageSn=iCentStorageSn from TCentStorage where iGoodsSn=@iGoodsSn and siLibNameSn=@iLib
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=101 or @Kind=102 or @Kind=103 or @Kind=19)
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
if(@iCentStorageSn is not null) --分仓有资料,更新
begin
update TCentStorage set fNowStock=@fNowTotalStock where iCentStorageSn=@iCentStorageSn
end
else
--分仓无资料,新增
begin
exec GetNewSn 'TCentStorage', @eve, @iCentStorageSn output
declare @date datetime
set @date=getdate()
exec TCentStorageInsProc @iCentStorageSn,@iGoodsSn,@iLib,@fNowTotalStock,@date
end
--//////////////////////////////////////////////////////////////////////////////////
--初始化
set @fNowTotalStock =0
set @fTransferNum =0
set @fAlreadyNum =0
--TStorageColor物品分仓颜色表更新
if(@cColor='')
begin
COMMIT TRAN
return(0)
end
select @siColorWaterSn=siColorWaterSn from TColorWater where cColorWater=@cColor
select @fNowTotalStock=fNowStock,@fTransferNum=fTransferNum,@fAlreadyNum=fAlreadyNum,@iStorageColorSn=iStorageColorSn from TStorageColor where iCentStorageSn=@iCentStorageSn and siColorWaterSn=@siColorWaterSn
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=101 or @Kind=102 or @Kind=103 or @Kind=19)
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
--减未领用量
if(@Kind=3 or @Kind=6 or @Kind=10 or @Kind=100 or @Kind=12 or @Kind=101 or @Kind=102 or @Kind=19 or @Kind=20 or @Kind=88)
begin
set @fTransferNum=@fTransferNum-@fQuantity
end
--加未领用量
if(@Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=22 or @Kind=23 or @Kind=26 or @Kind=27 or @Kind=29)
begin
set @fTransferNum=@fTransferNum+@fQuantity
end
--减已购未入量
if(@Kind=1 or @Kind=99)
begin
set @fAlreadyNum=@fAlreadyNum-@fQuantity
end
--加已购未入量
if(@Kind=2)
begin
set @fAlreadyNum=@fAlreadyNum+@fQuantity
end
if(@iStorageColorSn is not null) --颜色分仓有资料,更新
begin
update TStorageColor set fNowStock=@fNowTotalStock, fTransferNum=@fTransferNum, fAlreadyNum=@fAlreadyNum where iStorageColorSn=@iStorageColorSn
end
else
--颜色分仓无资料,新增
begin
exec GetNewSn 'TStorageColor', @eve, @iStorageColorSn output
exec TStorageColorInsProc @iStorageColorSn,@iCentStorageSn,@siColorWaterSn,@fNowTotalStock,@fTransferNum,@fAlreadyNum
end
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--初始化
set @fNowTotalStock =0
set @fTransferNum =0
set @fAlreadyNum =0
--TStorageList物品仓库清单资料表更新
if(@cDetailNo='')
begin
COMMIT TRAN
return(0)
end
if (@Kind=100 or @Kind=101 or @Kind=102 or @Kind=103) --委外退货
begin
declare @DetNo varchar(20)
select @fNowTotalStock=fQuantity, @iStorageListSn=iStorageListSn , @DetNo=cDetailNo from TStorageList where iStorageColorSn=@iStorageColorSn and cCorckNo=@cCorckNo and cGrade=@cGrade and iRelationSn=@iRelationSn and iCompanySn=@iCompanySn
set @fNowTotalStock=@fNowTotalStock-@fQuantity
set @DetNo=cast((cast(@DetNo as int) - cast(@cDetailNo as int)) as varchar(20))
if(@iStorageListSn is not null) --清单分仓有资料,更新
begin
update TStorageList set fQuantity=@fNowTotalStock, cDetailNo=@DetNo where iStorageListSn=@iStorageListSn
end
else --清单分仓无资料,新增
begin
exec GetNewSn 'TStorageList', @eve, @iStorageListSn output
exec TStorageListInsProc @iStorageListSn,@iStorageColorSn,@cDetailNo,@cCorckNo,@cGrade,@fNowTotalStock,@iRelationSn,@iCompanySn
end
end
else
begin
select @fNowTotalStock=fQuantity, @iStorageListSn=iStorageListSn from TStorageList where iStorageColorSn=@iStorageColorSn and cDetailNo=@cDetailNo and cCorckNo=@cCorckNo and cGrade=@cGrade and iRelationSn=@iRelationSn and iCompanySn=@iCompanySn
--减现有存量
if(@Kind=2 or @Kind=3 or @Kind=5 or @Kind=6 or @Kind=8 or @Kind=12 or @Kind=13 or @Kind=20 or @Kind=16 or @Kind=17 or @Kind=19 or @Kind=10 )
begin
set @fNowTotalStock=@fNowTotalStock-@fQuantity
end
--加现有存量
if(@Kind=1 or @Kind=4 or @Kind=7 or @Kind=9 or @Kind=11 or @Kind=14 or @Kind=15 or @Kind=18 or @Kind=22 or @Kind=23 or @Kind=25 or @Kind=26 or @Kind=27 or @Kind=28 or @Kind=29)
begin
set @fNowTotalStock=@fNowTotalStock+@fQuantity
end
if(@iStorageListSn is not null) --清单分仓有资料,更新
begin
update TStorageList set fQuantity=@fNowTotalStock where iStorageListSn=@iStorageListSn
end
else --清单分仓无资料,新增
begin
exec GetNewSn 'TStorageList', @eve, @iStorageListSn output
exec TStorageListInsProc @iStorageListSn,@iStorageColorSn,@cDetailNo,@cCorckNo,@cGrade,@fNowTotalStock,@iRelationSn,@iCompanySn
end
end
IF (@@error!=0)
BEGIN
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
return(0)
END
GO
To leeboyan(宝宝)
谢谢
FROM (SELECT TShipStockInfo.iShipInfoSn, TShipStockInfo.dFactDate,
TShipStockInfo.iOrderStockSn, TShipStockInfo.fTaxRate,
TShipStockInfo.fTotMoney, TShipStockInfo.fTotalQuantity,
TShipStockCont.iGoodsSn, TShipStockCont.cColorWater,
TShipStockCont.mUnitPrice, TShipStockCont.fQuantity,
TShipStockCont.fMoney, List.cBatchVatNo, List.fMeter, List.listCount
FROM TShipStockInfo INNER JOIN
TShipStockCont ON
TShipStockInfo.iShipInfoSn = TShipStockCont.iShipInfoSn INNER JOIN
(SELECT iShipContSn, cBatchVatNo, SUM(fQuantity) AS fQuantity,
SUM(fMeter) AS fMeter, COUNT(*) AS listCount
FROM TShipStockList
GROUP BY iShipContSn, cBatchVatNo) List ON
TShipStockCont.iShipContSn = List.iShipContSn
WHERE (TShipStockInfo.siKind IN (12, 13, 19))) SaleOut INNER JOIN
(SELECT TOrderInfo.iOrderInfoSn, TOrderInfo.siOrderKind, TOrderInfo.cOrderNo,
TOrderInfo.cBargainNo, TOrderInfo.iCompanySn, TOrderInfo.iGoodsSn,
TOrderInfo.cShippingMark, TCompany.cCompanyNo,
TCompany.cCompanyName, TOrderCont.cColorWater,
TOrderCont.fQuantity
FROM TOrderInfo INNER JOIN
TCompany ON
TOrderInfo.iCompanySn = TCompany.iCompanySn INNER JOIN
TOrderCont ON TOrderInfo.iOrderInfoSn = TOrderCont.iOrderInfoSn
WHERE (TOrderInfo.siOrderKind = '0')
UNION
SELECT TOrderInfo.iOrderInfoSn, TOrderInfo.siOrderKind, TOrderInfo.cOrderNo,
TOrderInfo.cBargainNo, TOrderInfo.iCompanySn, TOrderInfo.iGoodsSn,
TOrderInfo.cShippingMark, TCompany.cCompanyNo,
TCompany.cCompanyName, TProcessOut.cGoodsColor AS cColorWater,
TProcessOut.fQuantity
FROM TOrderInfo INNER JOIN
TCompany ON
TOrderInfo.iCompanySn = TCompany.iCompanySn INNER JOIN
TProcessOut ON TOrderInfo.iOrderInfoSn = TProcessOut.iOrderInfoSn
WHERE (TOrderInfo.siOrderKind = '1')) OrderInfo ON
SaleOut.iOrderStockSn = OrderInfo.iOrderInfoSn AND
SaleOut.cColorWater = OrderInfo.cColorWater
查询我已经做出, 对数据库的设计还是有些迷惑。请教 1.ERP数据库中的表结构 一定要这样设计么?
2.有没有简单其它的设计可以避免这种麻烦的查询和很多其他的嵌套式的操作?
3.还是 所有的 ERP数据库中的表结构 都是这样?
4.及其所做的查询都这样麻烦? 请高手帮助回答... 谢!
不好意思,如果簡單的說還可以看懂
我也是做ERP 的
2:ERP 一般都不會有這種復雜的結構,如果表設計好了,寫代碼的難度會少很多,速度也會快很多,
3: 查詢是根據實際情況而定,有復雜的,也有容易的,這是根據要求的情況來的,
4: 在ERP 中的主從表設計是最常見的,表與表之間的關聯性是必不可少的,
设计数据库时,表Employee,表Employee中肯定是要有人员部门这个字段,这个字段可以是 直接 用部门的名称好,还是用另一个表 Department(部门表)中的主键值。部门表其他的地方用到的并不多。为什么?还有诸如商品表 和 商品的分类等等
----------------------------------