计算累计电费回收率,的存储过程。
create or replace procedure p_report_p001(repYm in varchar2) is
itmName varchar2(200); --项目
stratDate varchar2(100); --统计时段起
endDate varchar2(100); --统计时段止
dateCount number(4); --统计天数
feePq number(15,4); --费用/量
consNos varchar2(2000); --欢乐海岸关联用户
orderNo number(4); --排序
itemLev number(2); --层级关系
buyPq number(15,4);
buyPq1 number(15,4);
buyPq2 number(15,4);
buyPq3 number(15,4);
salePq number(15,4);
salePq1 number(15,4);
salePq2 number(15,4);
salePq3 number(15,4);
inSalePq number(15,4);
saleFee number(15,4);
buyFee number(15,4);
mThisDate date;
mLastDate date;
thisRece number(16,4);
thisRcved number(16,4);
totalRcved number(16,4);
totalRece number(16,4);
begin
delete from oct_p_report_p001 p where p.rep_ym = repYm and p.wp_flag = '1' ;
commit;
................省略..................................................repYm是传入的参数
--30 本月应收 thisRece 已经算了
--31 本月实收 thisRcved 已经算了
--33 累计回收率
itmName := '累计回收率';
feePq := 0;
orderNo := orderNo+1;
totalRcved := 0; --累计实收
totalRece := 0; --累计应收
Select sum(fee_Pq)+ thisRcved into totalRcved from oct_p_report_p001 oprp where
to_char( to_date(oprp.rep_Ym,'yyyy-mm') ,'YYYY')=
to_char( to_date(repYm,'yyyy-mm') ,'YYYY')
and
to_char( to_date(oprp.rep_Ym,'yyyy-mm') ,'MM')=
to_char( to_date(repYm,'yyyy-mm') ,'MM')
and ITM_NAME='本月实收电费'; ----------是年份等于同一年,月份是最小的,(考虑到可能从1月开始,也可能不是。)
Select sum(fee_Pq)+ thisRcved into totalRece from oct_p_report_p001 oprp where
to_char( to_date(oprp.rep_Ym,'yyyy-mm') ,'YYYY')=
to_char( to_date(repYm,'yyyy-mm') ,'YYYY')
and
to_char( to_date(oprp.rep_Ym,'yyyy-mm') ,'MM')=
to_char( to_date(repYm,'yyyy-mm') ,'MM')
and ITM_NAME='本月应收电费';
feePq := ( totalRcved/ totalRece) * 100 ;
insert into oct_p_report_p001(
report_id,itm_name,rep_ym,strat_date,end_date,date_count,fee_pq,cons_nos,order_no,item_lev,wp_flag,lead_no
)values(
sys_guid(),itmName,repYm,stratDate,endDate,dateCount,feePq,'',orderNo,'0','1',null
);
commit;
end p_report_p001;