create or replace procedure MIS_UpdateOutPrice(ibilldate in date,iGoodsid in varchar2) is
aprices Number;
begin
--1操作领料单,更改当前单据之后的单据中的货品平均价及总金额
declare
cursor mycursor is
Select billcode,billdate,itemno,Row_id from MIS_RPT_OUTSTORELL_VIEW
where goodsid=''||iGoodsid||'' and billdate>=ibilldate;
cursorrecord mycursor%rowtype;
begin
open mycursor;
if mycursor%isopen then
loop
fetch mycursor into cursorrecord;
--统计平均价//下面的sql 语句有问题,提示:嵌套分组函数没有group by
select DECODE(sum(count),0,0,round(sum(total)/sum(count),4)) as price into aprices
from (select sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount)) as count
,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY) as total
from MIS_rpt_outin_VIEW where GOODSID=iGoodsid and (billdate<cursorrecord.billdate or INITFLAG=0)
union
select sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount)) as count
,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY) as total
from MIS_rpt_outin_VIEW where GOODSID=iGoodsid
and (billdate=cursorrecord.billdate
and billcode<''||cursorrecord.billcode||''));
--end
if aprices<>0 then
--更新明细表平均价
update MIS_OUTSTORE_LLDETAIL set UNITPRICE=aprices where ITEMNO=cursorrecord.itemno;
--更新主表总金额
update MIS_OUTSTORE_LL set TOTALAMT=(select sum(unitcount*unitprice) as total from MIS_OUTSTORE_LLDETAIL where STORELLID=cursorrecord.row_id) where STORELLID=cursorrecord.row_id;
end if;
exit when mycursor %notfound;
end loop;
else
dbms_output.put_line('1操作领料单游标没有打开!');
end if;
end;
end MIS_UpdateOutPrice;
aprices Number;
begin
--1操作领料单,更改当前单据之后的单据中的货品平均价及总金额
declare
cursor mycursor is
Select billcode,billdate,itemno,Row_id from MIS_RPT_OUTSTORELL_VIEW
where goodsid=''||iGoodsid||'' and billdate>=ibilldate;
cursorrecord mycursor%rowtype;
begin
open mycursor;
if mycursor%isopen then
loop
fetch mycursor into cursorrecord;
--统计平均价//下面的sql 语句有问题,提示:嵌套分组函数没有group by
select DECODE(sum(count),0,0,round(sum(total)/sum(count),4)) as price into aprices
from (select sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount)) as count
,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY) as total
from MIS_rpt_outin_VIEW where GOODSID=iGoodsid and (billdate<cursorrecord.billdate or INITFLAG=0)
union
select sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount)) as count
,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY) as total
from MIS_rpt_outin_VIEW where GOODSID=iGoodsid
and (billdate=cursorrecord.billdate
and billcode<''||cursorrecord.billcode||''));
--end
if aprices<>0 then
--更新明细表平均价
update MIS_OUTSTORE_LLDETAIL set UNITPRICE=aprices where ITEMNO=cursorrecord.itemno;
--更新主表总金额
update MIS_OUTSTORE_LL set TOTALAMT=(select sum(unitcount*unitprice) as total from MIS_OUTSTORE_LLDETAIL where STORELLID=cursorrecord.row_id) where STORELLID=cursorrecord.row_id;
end if;
exit when mycursor %notfound;
end loop;
else
dbms_output.put_line('1操作领料单游标没有打开!');
end if;
end;
end MIS_UpdateOutPrice;
,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY) as total
from MIS_rpt_outin_VIEW
count为系统关键字,