存储过程

上一篇 / 下一篇 2014-05-27 17:34:47 / 个人分类：甲骨文

create or replace procedure p_sag_rpt_departmentstatistics(
    p_montime         in     varchar2,        -- query start time51Testing软件测试网@ xY~i;v
    p_departmentid    in     varchar2,        -- query departmentname
PF0l6gG;A0    p_cur             out    sys_refcursor    -- Cursor for output
b/KcN&t+k0)51Testing软件测试网o,Dc N8Y2B
/**********************************************************************************51Testing软件测试网 vj%vtV5@ m
NAME:       p_sag_rpt_departmentstatistics
j?'RmEje2M0REVISIONS: Business settlement of blank51Testing软件测试网 DK/UUoXqn
Ver         Date         Author                 Description
T~8^|4w;^7}0---------   ---------- ---------------------- ------------------------------------
a/]?4cdW aK0C00         2014-01-24              1.Create the procedure
NOTES:      NA51Testing软件测试网n/W~;gb8?
reportname: Business settlement of blank51Testing软件测试网5Z$_6QXE5H
**********************************************************************************/
zRHB5^'D:O7dN0is
`8tVZq*i{&ka0    v_filter              varchar2(500);
?v%qh0cjj0    v_payfilter           varchar2(500);
b1x"er5~]!@vz*N0    v_prmfilter           varchar2(500);
iYi2L/GDN~2i"})X0    v_sql                 varchar2(4000);51Testing软件测试网)j%YbB cD2U

begin
    -- judge profileid

E\;g ppi5DLV:_0    if p_departmentid <> 'ALL' then
*iD0n.};^'a5~/E0       v_filter := ' where t1.DEPARTMENTID = '''|| p_departmentid ||'''';
.MH,xL?)L L@8`xT]b0    end if;

,`@"oM!k;d0    if p_montime is not null then
h`w-k6CXh0       v_payfilter:=' to_char(dtime,''yyyy-mm'') = '''|| p_montime ||'''';
S+[F4|!Ki"lm0       v_prmfilter:=' to_char(dtime,''yyyy-mm'') = '''|| p_montime ||'''';
&pG]^K!VPHV0      else51Testing软件测试网]D v~Ig-a
         v_payfilter:=' 1=1 ';
H@DGj v4o0         v_prmfilter:=' 1=1 ';
b|v,sEJ0    end if;51Testing软件测试网3i1v#p ?\S

$it{W0n6f q/b0 v_sql :='select dtime,DEPARTMENTID,DEPARTMENTNAME,payfee,prmfee,errorRatio from (';51Testing软件测试网8uC4A"x,b

    -- set querying sql
9mJAKgW0    51Testing软件测试网7^,O\:b~#~ny
    v_sql := v_sql ||'select nvl('''||p_montime||''','''') as dtime,t2.DEPARTMENTID as DEPARTMENTID,51Testing软件测试网8{{[9q+r;z
       t2.DEPARTMENTNAME as DEPARTMENTNAME,51Testing软件测试网o0Wg:D+p_F
       sum(t2.payfee) as payfee,51Testing软件测试网ev2e/v'{H
       sum(t2.prmfee) as prmfee,51Testing软件测试网4cs"GA1Kb(]
       (round(decode(nvl(sum(t2.payfee), 0),
                     0,
                     0,
W0APY-G/KH7z0                     nvl((sum(t2.payfee) - sum(t2.prmfee)), 0) /
EIZ1Zv q(l0                     nvl(sum(t2.payfee), 0)), 4) * 100)'||'||''%'' as errorRatio
"j-ii3m%x/LH0 from (select DEPARTMENTID,
               DEPARTMENTNAME,51Testing软件测试网D3k[.\w
               APPLICATIONID,51Testing软件测试网:W}C.YM^{0S
               payfee,
wq9v ['JDQCJM0               (C * decode((1 - to_number(nvl(jtsettleratio, 0)) / 100),0,0,(decode(B, 0, 0, plat / B)) /
f6NK-i7S0               (1 - to_number(nvl(jtsettleratio, 0)) / 100)) *
3e)qN;@ e0               to_number(nvl(bpsettleratio, 0)) / 100) as prmfee
q'?6KK,~\Z0          from (select DEPARTMENTID,
@_1a"j z7V;\}ZZ"b0                       DEPARTMENTNAME,
                       APPLICATIONID,
2TlOT"MR``D0                       (select nvl(sum(fee), 0) *
(f6ZXF Jw4^%F,f0                               to_number(nvl(t1.bpsettleratio, 0))
'piFlOc0                         from t_aep_parmonth_paymentcharge
                         where appkey = t1.APPLICATIONID and '||v_payfilter||') as payfee,
                       (select nvl(sum(fee), 0)
:La#yX,{0                          from t_sep_prm_balancedetail51Testing软件测试网uv8C!OQqo
                          where '||v_prmfilter||' and servicename =51Testing软件测试网/e D)~dz
                               (select max(servicename)
                                  from T_SEP_CucVacConfig t4,
                                       (select distinct prmserviceid, appkey51Testing软件测试网 @6k$yd}sk
                                        from t_aep_parmonth_paymentcharge where '||v_payfilter||') t551Testing软件测试网6YK {u4S%p`
                                  where t4.ServiceId = t5.prmserviceid
IRj[q0                                       and t5.appkey = t1.APPLICATIONID)) as C,
0sGk? r.m0                                   51Testing软件测试网 @.`,ZJ-`6Rkl
                       (select nvl(sum(fee), 0)
                          from t_aep_parmonth_paymentcharge
wz^3c]0                          where '||v_payfilter||'and prmserviceid = f_sag_rpt_getServiceId('''||p_montime||''',t1.APPLICATIONID)
                       ) as B,51Testing软件测试网F&GN_^QB
                       51Testing软件测试网'`(q7psnJG7h
                       (select nvl(sum(fee), 0)
"b5D0@U xyI0                          from t_aep_parmonth_paymentcharge51Testing软件测试网WG6HhX a6r
                          where appkey = t1.APPLICATIONID and '||v_payfilter||') as plat,
A;iF`)x4C0                          t1.jtsettleratio as jtsettleratio,t1.bpsettleratio as bpsettleratio
                          from T_SEP_CUC_SETTLERATIO t1 '||v_filter||')) t2
yemTRD f0                    group by t2.DEPARTMENTID, t2.DEPARTMENTNAME';

{/ND6z,t&[*k0
+V iPpR*?1S\0E:LJg c V0        v_sql :=v_sql||' ) t6 where t6.payfee <> 0 and t6.prmfee <> 0 ';
cj7xq0Z]mH@/NY ~o0        insert into debug_msg values ('p_sag_rpt_departmentstatistics:' || v_sql,sysdate);
!Vjfs[`O0        commit;

`+s~ ag/HDC9C5l0 open p_cur for v_sql ;

exception
    when others then
)H/H3|!]$_-J9b#l0                open p_cur for
                select 'err' dtime,
"A*gi9i;B/{0                       'err' DEPARTMENTID,
                       'err' DEPARTMENTNAME,
-@wlvy.W4z {0                       'err' payfee,51Testing软件测试网 _ud+R*C
                       'err' prmfee,
*@w]b$S0                       'err' errorRatio
                from dual;51Testing软件测试网/]#ode]1C
end p_sag_rpt_departmentstatistics;
5G5Jz7PMq2n'E0/
DN.z W1ER"f2nf0