17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace package body pk_erp_w
as
procedure ElectricityAmountPoor(v_date1 varchar2,v_date2 varchar2,cur_return out cur_result)
as
begin
open cur_return for
select sum(t3.vamount) as vamount,
t3.position,
t3.elename,
t3.devicename,
t3.opertypename
from (select t1.instrumentid,
t1.vamount,
t2.instrumentname,
t2.opertypename,
t2.devicename,
t2.elename,
t2.position
from (select t.instrumentid, sum(t.vamount) as vamount
from t_erp_realinsdata_day t
where t.netid = 'N:E001'
and (t.insdate between to_date(v_date1, 'yyyy-mm-dd') and
to_date(v_date2, 'yyyy-mm-dd'))
group by t.instrumentid
order by t.instrumentid) t1,
t_erp_instrumentdefine t2
where t1.instrumentid = t2.instrumentid
and t2.opertypename in ('1', '4', '9')
and t2.elename not in ('Elec')
and t2.instrumentid not in ('I:p0300', 'I:p0890', 'I:p0891')
order by t2.elename, t2.position, t2.opertypename) t3
group by t3.position, t3.devicename, t3.elename, t3.opertypename
order by t3.elename, t3.position, t3.devicename, t3.opertypename;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line(SQLERRM);
return;
end ElectricityAmountPoor;