/*用到三张表,库存账,单据主表,单据体表*/
/* 库存帐*/CREATE TABLE InvBalAccount
(
FiscalPeriod char(2) not null /* 会计期间 */,
WareHouseID int not null /* 仓库OID */,
MaterialID int not null /* 货品ID */,
DebQuanAmount decimal(18,8) default 0 not null /* 借方数量发生 */,
CredQuanAmount decimal(18,8) default 0 not null /* 贷方数量发生 */,
CurQuanBalance decimal(18,8) default 0 not null /* 当前数量余额 */,
BatchID int default 0 not null /* 批次ID */,
InitOutQuantity decimal(18,8) default 0 not null /* 期初出库数量*/,
constraint InvBalAccount_Key_1 unique (FiscalPeriod, MaterialID, BatchID, WareHouseID) /* Key_1 */
)/* 数据表 货物移动*/CREATE TABLE GoodsMovement
(
GoodsMovementID int not null /* 单据ID */,
BillDate date /* 单据日期 */,
BillCode varchar(30) default '' not null /* 单据编号 */,
MoveTypeID int default 0 not null /* 移动类型 */,
FiscalPeriod char(2) /* 会计期间 */,
RecWHID int default 0 not null /* 接收仓库 */,
IssWHID int default 0 not null /* 发出仓库 */,
IMCheck char(1) default '0' not null /* 库存审核状态 0 未审 1 已审 */,
/* 是否初始 0 否 1 是 采购时,使用暂估单据,初始业务后 可以做红冲,重新做单。 */ constraint GoodsMovement_Key_1 primary key (GoodsMovementID) /* Key_1 */
)
;
/* 数据表 货物移动行 */CREATE TABLE GoodsMovementItem
(
GoodsMovementID int not null /* 货物移动ID */,
GoodsMovementItemID int not null /* 货物移动行ID */,
MaterialID int /* 货品ID */,
BatchID int default 0 not null /* 批次OID */,
InpQuantity decimal(18,8) default 0 not null /* 收货数量 */,
OutQuantity decimal(18,8) default 0 not null /* 发货数量 */,
OutTypeQuantity decimal(18,8) default '0.00000000' not null /* 出库方式记录数量*/,
constraint GoodsMovementItem_Key_1 primary key (GoodsMovementID, GoodsMovementItemID) /* Key_1 */
)
;
INSERT INTO `goodsmovement` (`GoodsMovementID`, `BillDate`, `BillCode`, `MoveTypeID`, `FiscalPeriod`, `RecWHID`, `IssWHID`, `IMCheck`) VALUES
(17,'2009-03-13','RK20090313050',101,'03',1,0,'1'),
(18,'2009-03-13','CK20090313024',201,'03',0,1,'');
INSERT INTO `goodsmovementitem` (`GoodsMovementID`, `GoodsMovementItemID`, `MaterialID`, `BatchID`, `InpQuantity`, `OutQuantity`, `OutTypeQuantity`) VALUES
(17,1,6,0,10,0,0),
(17,2,6,0,10,0,0),
(18,1,6,0,0,30,0);
[/code]
做了库存初始
INSERT INTO `invbalaccount` (`FiscalPeriod`, `WareHouseID`, `MaterialID`, `DebQuanAmount`, `CredQuanAmount`, `CurQuanBalance`, `BatchID`, `InitOutQuantity`) VALUES
('03',1,6,20,0,30,0,0)
做了入库单
[code=SQL]INSERT INTO `goodsmovement` (`GoodsMovementID`, `BillDate`, `BillCode`, `MoveTypeID`, `FiscalPeriod`, `RecWHID`, `IssWHID`, `IMCheck`) VALUES
(17,'2009-03-13','RK20090313050',101,'03',1,0,'1');
INSERT INTO `goodsmovementitem` (`GoodsMovementID`, `GoodsMovementItemID`, `MaterialID`, `BatchID`, `InpQuantity`, `OutQuantity`, `OutTypeQuantity`) VALUES
(17,1,6,0,10,0,0),
(17,2,6,0,10,0,0);
再做出库单
INSERT INTO `goodsmovement` (`GoodsMovementID`, `BillDate`, `BillCode`, `MoveTypeID`, `FiscalPeriod`, `RecWHID`, `IssWHID`, `IMCheck`) VALUES
(18,'2009-03-13','CK20090313024',201,'03',0,1,'');
INSERT INTO `goodsmovementitem` (`GoodsMovementID`, `GoodsMovementItemID`, `MaterialID`, `BatchID`, `InpQuantity`, `OutQuantity`, `OutTypeQuantity`) VALUES
(18,1,6,0,0,30,0);
code]
现在要将出库单上的出库数量回写的库存账上已出库的数量InitOutQuantity
与入库单上已出库的数量OutTypeQuantity
我写一过程
调用GoodsMovementID 我提供的记录中是18
要求先更新InvBalAccount表中的InitOutQuantity 数量
如果更新完了,再更新goodsmovementitem表中入库单(RecWHID记录与本记录IssWHID相同)的OutTypeQuantity数量
goodsmovementitem表中的物料可能重复如提供数据。
更外考虑真正业务中出库单可能更新N多入库单据所以写的循环结果出问题了。
考虑用游标但没有回写上。
先给100分,不够再加
INSERT INTO T_invvalbalaccount(T_FiscalPeriod,T_WareHouseID,T_MaterialID,T_BatchID,T_QuanAmount)
SELECT FiscalPeriod,IssWHID,MaterialID,BatchID,sum(OutQuantity),
FROM GoodsMovement INNER JOIN GoodsMovementItem ON GoodsMovement.GoodsMovementID=GoodsMovementItem.GoodsMovementID
WHERE GoodsMovement.GoodsMovementID=p_BillID
GROUP BY FiscalPeriod,IssWHID,MaterialID,BatchID;
--删除临时表中非先进先出,后进先出的物料
DELETE FROM T_invvalbalaccount
WHERE OutType='0' OR OutType='3';
SELECT count(1) INTO v_count FROM T_invvalbalaccount;
IF v_count>0 THEN --处理先进先出,后进先出的货品 --先处理先进先出的货品
SELECT FiscalPeriod INTO v_FiscalPeriod FROM FiscalPeriod WHERE Kjqj_first='y'; UPDATE InvBalAccount,T_invvalbalaccount
SET InitOutQuantity=InitOutQuantity+T_QuanAmount,T_QuanAmount=0
WHERE InvBalAccount.FiscalPeriod=v_FiscalPeriod
AND InvBalAccount.WareHouseID=T_WareHouseID
AND InvBalAccount.MaterialID=T_MaterialID
AND InvBalAccount.BatchID=T_BatchID
AND T_invvalbalaccount.OutType='1'
AND CurQuanBalance+CredQuanAmount-DebQuanAmount-InitOutQuantity>=T_QuanAmount;
--去了更新的内容
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0;
--更新完全部初始的
UPDATE InvBalAccount,T_invvalbalaccount
SET InitOutQuantity=CurQuanBalance+CredQuanAmount-DebQuanAmount,
T_QuanAmount=T_QuanAmount-(CurQuanBalance+CredQuanAmount-DebQuanAmount-InitOutQuantity)
WHERE InvBalAccount.FiscalPeriod=v_FiscalPeriod
AND InvBalAccount.WareHouseID=T_WareHouseID
AND InvBalAccount.MaterialID=T_MaterialID
AND InvBalAccount.BatchID=T_BatchID
AND T_invvalbalaccount.OutType='1'
AND CurQuanBalance+CredQuanAmount-DebQuanAmount-InitOutQuantity<T_QuanAmount;
--处理单据从最初没有出完库的开始每一单据进行更新,循环处理
SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
WHILE v_count>0 do
--更新最早单据,可能有重复的物料,这样一条一条处理
SELECT min(GoodsMovement.GoodsMovementID),min(GoodsMovementItemID) INTO v_minBillID ,v_minBillItemID
FROM GoodsMovement INNER JOIN GoodsMovementItem ON GoodsMovement.GoodsMovementID=GoodsMovementItem.GoodsMovementID
AND MoveTypeID IN (101,102,103,104,105) AND GoodsMovement.IMCheck='1'
INNER JOIN T_invvalbalaccount ON GoodsMovement.RecWHID=T_WareHouseID
AND GoodsMovementItem.MaterialID=T_MaterialID
AND GoodsMovementItem.BatchID=T_BatchID
AND T_invvalbalaccount.OutType='1'
AND InpQuantity>OutTypeQuantity;
--更新入库单上的出库金额
UPDATE GoodsMovementItem,T_invvalbalaccount
SET OutTypeQuantity=OutTypeQuantity+T_QuanAmount,T_QuanAmount=0
WHERE GoodsMovementID=v_minBillID
AND GoodsMovementItemID=v_minBillItemID
AND GoodsMovementItem.MaterialID=T_MaterialID
AND GoodsMovementItem.BatchID=T_BatchID
AND T_invvalbalaccount.OutType='1'
AND InpQuantity-OutTypeQuantity>=T_QuanAmount;
--去了更新的内容
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0;
--更新入库单内容
UPDATE GoodsMovementItem,T_invvalbalaccount
SET OutTypeQuantity=InpQuantity,T_QuanAmount=T_QuanAmount-(InpQuantity-OutTypeQuantity)
WHERE GoodsMovementID=v_minBillID
AND GoodsMovementItemID=v_minBillItemID
AND GoodsMovementItem.MaterialID=T_MaterialID
AND GoodsMovementItem.BatchID=T_BatchID
AND T_invvalbalaccount.OutType='1'
AND InpQuantity-OutTypeQuantity<T_QuanAmount;
SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
END WHILE ;
这样做跑死了。
SELECT count(1) INTO v_count FROM T_invvalbalaccount;
IF v_count>0 THEN --处理先进先出,后进先出的货品
--处理了部分业务
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0; SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
END WHILE ;
看IF WHILE间的内容,怎么保证它停下。
不好意思,前面贴的太多了。
IF v_count>0 THEN --处理先进先出,后进先出的货品
--处理了部分业务
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0; SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
--v_count=0 时怎么让WHILE退出呢
END WHILE ;
跳出WHILE还是其它?
LEAVE Statement
LEAVE labelThis statement is used to exit any labeled flow control construct. It can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE). 我还没有测试过
LEAVE WHILE v_count>0 do --处理先进先出,后进先出的货品
--处理了部分业务
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0; SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
--v_count=0 时怎么让WHILE退出呢
if v_count=0 then
LEAVE label
END WHILE ;
这样吗?
drop procedure if exists rr$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`rr`(dd varchar(10))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare v1 integer;
set v1=10;
zz:WHILE v1 > 0 DO
SET v1 = v1 - 1;
if v1=2 then
begin
select 'dddd';
leave zz;
end;
end if;
END WHILE; END$$DELIMITER ;
zz:WHILE v_count>0 do --处理先进先出,后进先出的货品
--处理了部分业务
DELETE FROM T_invvalbalaccount WHERE T_QuanAmount=0; SELECT count(1) INTO v_count FROM T_invvalbalaccount WHERE T_invvalbalaccount.OutType='1';
--v_count=0 时怎么让WHILE退出呢
if v_count=0 then
LEAVE zz;
END WHILE ;
WHILE @v_count>0 do
--处理业务,删除不合理的记录
SELECT count(1) INTO @v_count FROM T_invvalbalaccount ;
END WHILE
记录的值,如为0时,应该不会循环