create or replace function f_get_fjkywtce(fcd in varchar, flag in number)
--非集客业务提成额(不参与考核分配)
return number
is
prekpi number;
p1 number;
p2 number;
p3 number;
begin
if flag = 0 --住宅电话揽装
then
select sum(oc.commision) into p1 from o_canvass oc
where oc.fixednet_cd = fcd group by oc.fixednet_cd;
return p1;
end if;
if flag = 1 --住宅宽带新装提成
then
select sum(h.commision) into p2 from o_new_home_broad h
where h.fixednet_cd = fcd group by h.fixednet_cd;
return p2;
end if;
if flag = 2 --住宅宽带续费提成
then
select sum(rh.commision) into p3 from o_renew_home_broad rh
where rh.fixednet_cd = fcd group by rh.fixednet_cd;
return p3;
end if;
if flag = 3 --非集客业务提成小计
then
select sum(oc.commision) into p1 from o_canvass oc
where oc.fixednet_cd = fcd group by oc.fixednet_cd;
select sum(h.commision) into p2 from o_new_home_broad h
where h.fixednet_cd = fcd group by h.fixednet_cd;
select sum(rh.commision) into p3 from o_renew_home_broad rh
where rh.fixednet_cd = fcd group by rh.fixednet_cd;
prekpi := p1+p2+p3;
return prekpi;
end if;
end f_get_fjkywtce;
oracle 函数应用实践一
最新推荐文章于 2024-08-14 15:10:21 发布