Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- средстоимость = (текущий остаток*текущая себестомость+new.колво+new.цена)/(текущий остаток+new.колво)
- 1)Приход ItemA - 10units - 10$ = GOODSREMAINS qty=10 TGOODS GDSAVGCOST=10
- 2)Продажа ItemA - 4units - 25$ = GOODSREMAINS qty=6 TGOODS GDSAVGCOST=10
- 3)Приход ItemA - 10units - 15$ = GOODSREMAINS qty=16 TGOODS GDSAVGCOST=13,13
- 4)Приход ItemA - 10units - 20$ = GOODSREMAINS qty=26 TGOODS GDSAVGCOST=15,77
- declare variable CURRENT_QTY D_QTY;
- declare variable CURRENT_COST D_AMT;
- declare variable NEW_COST D_AMT;
- begin
- IF (INSERTING) THEN
- begin
- CURRENT_QTY =0;
- CURRENT_COST =0;
- NEW_COST =0;
- select sum(coalesce(qty,0))
- from GOODSREMAINS
- where GOODSID=:NEW.GDSID
- INTO :CURRENT_QTY;
- select coalesce(GDSAVGCOST,0)
- from TGOODS
- where GDSID=:NEW.GDSID
- INTO :CURRENT_COST;
- if (exists(select goodsid from goodsremains where goodsid = :NEW.GDSID and storeid = :NEW.STOREID and boxnumber = :NEW.GDSCOD)) then
- begin
- UPDATE GOODSREMAINS
- SET GOODSREMAINS.QTY = GOODSREMAINS.QTY + :NEW.RGSTRITEMQTY * :NEW.TRANSTYPE
- WHERE GOODSREMAINS.GOODSID = :NEW.GDSID
- AND GOODSREMAINS.BOXNUMBER = :NEW.GDSCOD
- AND GOODSREMAINS.STOREID = :NEW.STOREID;
- end
- else
- begin
- insert into goodsremains (
- goodsid,
- storeid,
- boxnumber,
- qty)
- VALUES (:NEW.GDSID,:NEW.STOREID,:NEW.GDSCOD,:NEW.RGSTRITEMQTY * :NEW.TRANSTYPE);
- end
- NEW_COST = (:CURRENT_QTY*:CURRENT_COST+:NEW.RGSTRITEMQTY*:NEW.RGTRITEMPURCHAMT)/(:NEW.RGSTRITEMQTY+:CURRENT_QTY);
- update TGOODS
- set GDSAVGCOST=:NEW_COST
- where GDSID=:NEW.GDSID;
- end
- ELSE IF (DELETING) THEN
- BEGIN
- CURRENT_QTY =0;
- CURRENT_COST =0;
- NEW_COST =0;
- select sum(coalesce(qty,0))
- from GOODSREMAINS
- where GOODSID=:OLD.GDSID
- INTO :CURRENT_QTY;
- select coalesce(GDSAVGCOST,0)
- from TGOODS
- where GDSID=:OLD.GDSID
- INTO :CURRENT_COST;
- UPDATE GOODSREMAINS
- SET GOODSREMAINS.QTY = GOODSREMAINS.QTY - :OLD.RGSTRITEMQTY * :OLD.TRANSTYPE
- WHERE GOODSREMAINS.GOODSID = :OLD.GDSID
- AND GOODSREMAINS.BOXNUMBER = :OLD.GDSCOD
- AND GOODSREMAINS.STOREID = :OLD.STOREID;
- NEW_COST = ((:CURRENT_QTY-:OLD.RGSTRITEMQTY * :OLD.TRANSTYPE)*:CURRENT_COST-:OLD.RGSTRITEMQTY*:OLD.RGTRITEMPURCHAMT)/:CURRENT_QTY+:OLD.RGSTRITEMQTY;
- update TGOODS
- set GDSAVGCOST=:NEW_COST
- where GDSID=:OLD.GDSID;
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement