declare
p_curmoneySum number(18, 4);
p_placepointid number(10);
p_rsaid number(10);
p_goodsqtysum number(18, 6);
p_realmoneysum number(18, 4);
cursor p_goods_sale_c IS
select placepointid,
rsaid,
sum(goodsqty) as goodsqtysum,
sum(realmoney) as realmoneysum
from GRESA_SA_DETAIL_V
where rsaid not in (select rsaid from ZX_RSAID_ALAN_TEMP)
and sellDate between to_date('2019-01-01', 'yyyy-mm-dd') and
to_date('2019-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by placepointid, rsaid
order by realmoneysum asc;
begin
p_curmoneySum := 300000.0;
open p_goods_sale_c;
fetch p_goods_sale_c
into p_placepointid, p_rsaid, p_goodsqtysum, p_realmoneysum;
while p_goods_sale_c%found loop
p_curmoneySum := p_curmoneySum - p_realmoneysum;
if p_curmoneySum > 0 then
insert into ZX_REDUCE_RSAID_TEMP (rsaid) values (p_rsaid);
else
exit;
end if;
fetch p_goods_sale_c
into p_placepointid, p_rsaid, p_goodsqtysum, p_realmoneysum;
end loop;
close p_goods_sale_c;
end;
