create or replace procedure mk_csyb_xxsc(p_region varchar2, p_ny varchar2, p_loginid varchar2, p_deptno varchar2) as p_nf varchar2(4) := substr(p_ny,0,4);begin delete from mk_csyb_temp where loginid = p_loginid and deptno = p_deptno and region = p_region;----------------------------------------------东部市场----------------------------------------- if p_region = 'db' then dbms_output.put_line('东部before!!!'|| p_region ||' '|| p_ny ||' '|| p_loginid ||' '|| p_deptno); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 东部市场创收月报mk_csyb_dbsc中的指标完成 */ select p_loginid loginid, p_deptno deptno, p_region region, bm_ytdw.bkdm bkdm, bm_ytdw.bk bkmc, nvl(bm_ytdw.xh,199) gsxh, bm_ytdw.mc gsmc, p_ny ny, '1' qyxh, a.qy newqy, mk_csyb_dbsc.zbe zbe, mk_csyb_dbsc.zbdw zbdw from bm_ytdw,mk_csyb_dbsc, (select * from (select distinct(qy) qy from mk_csyb_dbsc where ny = p_ny) qy, (select distinct(dwdm) dwdm from mk_csyb_dbsc where ny = p_ny) dwdm) a where bm_ytdw.dm = a.dwdm and mk_csyb_dbsc.ny(+) = p_ny and mk_csyb_dbsc.qy(+) = a.qy and mk_csyb_dbsc.dwdm(+) = a.dwdm );
insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* mk_cszb_db表中所有的东部创收年度指标 */ select p_loginid loginid, p_deptno deptno, p_region region, bm_ytdw.bkdm bkdm, bm_ytdw.bk bkmc, nvl(bm_ytdw.xh,199) gsxh, bm_ytdw.mc gsmc, p_ny ny, '0' qyxh, '年度指标' newqy, mk_cszb_db.dbzb zbe, '' zbdw from bm_ytdw,mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 每个公司的指标完成率 */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm, bm_ytdw.bk bkmc, nvl(max(bm_ytdw.xh),199) gsxh, bm_ytdw.mc gsmc, p_ny ny, '3' qyxh, '完成率' newqy, decode(max(mk_cszb_db.dbzb),0,0,(sum(mk_csyb_dbsc.zbe)/max(mk_cszb_db.dbzb))*100) zbe, max(mk_csyb_dbsc.zbdw) zbdw from bm_ytdw,mk_csyb_dbsc,mk_cszb_db where bm_ytdw.dm = mk_csyb_dbsc.dwdm and bm_ytdw.dm = mk_cszb_db.dw and mk_csyb_dbsc.ny = p_ny and mk_cszb_db.nf = p_nf group by bm_ytdw.bk,bm_ytdw.mc ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 每个公司的指标完成总计 */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, nvl(max(bm_ytdw.xh),199) gsxh, bm_ytdw.mc gsmc, p_ny ny, '2' qyxh, '总计' newqy, sum(mk_csyb_dbsc.zbe) zbe, max(mk_csyb_dbsc.zbdw) zbdw from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny group by bm_ytdw.bk,bm_ytdw.mc ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /*********************************************/ /****************开始 小 计 ****************/ /*按区块和区域分组的 各油区指标完成小计 */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 计**' gsmc, p_ny ny, '1' qyxh, mk_csyb_dbsc.qy newqy, sum(mk_csyb_dbsc.zbe) zbe, max(mk_csyb_dbsc.zbdw) zbdw from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny group by bm_ytdw.bk,mk_csyb_dbsc.qy ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 按区块划分的 年度指标小计 */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 计**' gsmc, p_ny ny, '0' qyxh, '年度指标' newqy, sum(mk_cszb_db.dbzb) zbe, '' zbdw from bm_ytdw,mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf group by bm_ytdw.bk ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 按区块分组的 指标完成总计 */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 计**' gsmc, p_ny ny, '2' qyxh, '总计' newqy, sum(mk_csyb_dbsc.zbe) zbe, max(mk_csyb_dbsc.zbdw) zbdw from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny group by bm_ytdw.bk ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 按区块分组的 小计的指标完成率 */ select p_loginid loginid, p_deptno deptno, p_region region, a.bkdm bkdm, a.bkmc bkmc, a.gsxh gsxh, a.gsmc gsmc, a.ny ny, a.qyxh qyxh, a.qy newqy, decode(a.zbe,0,0,nvl(b.zbe,0)/a.zbe*100) zbe, '' zbdw from /*----- 按区块划分的 年度指标小计*/ (select max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 计**' gsmc, p_ny ny, '3' qyxh, '完成率' qy, sum(mk_cszb_db.dbzb) zbe, '' zbdw from bm_ytdw,mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf group by bm_ytdw.bk) a, /*----- 按区块分组的 指标完成总计*/ (select max(bm_ytdw.bkdm) bkdm, sum(mk_csyb_dbsc.zbe) zbe from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny group by bm_ytdw.bk) b where a.bkdm = b.bkdm(+) /****************结束 小 计 ****************/ ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /****************开始 总 计 ****************/ /* 年度指标额的总计 */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm,'=====总 计=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '0' qyxh, '年度指标' newqy, sum(mk_cszb_db.dbzb) zbe, '' zbdw from bm_ytdw,mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 各个域区的指标完成总计 */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm, '=====总 计=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '1' qyxh, mk_csyb_dbsc.qy newqy, sum(mk_csyb_dbsc.zbe) zbe, '' zbdw from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny group by mk_csyb_dbsc.qy ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 指标完成总计 */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm, '=====总 计=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '2' qyxh, '总计' newqy, sum(mk_csyb_dbsc.zbe) zbe, '' zbdw from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /* 总计完成率 */ select p_loginid loginid, p_deptno deptno, p_region region, a.bkdm bkdm, a.bkmc bkmc, a.gsxh gsxh, a.gsmc gsmc, a.ny ny, a.qyxh qyxh, a.qy newqy, decode(a.zbe,0,0,nvl(b.zbe,0)/a.zbe*100) zbe, '' zbdw from /* 年度指标额的总计 */ (select 'zzzz' bkdm,'=====总 计=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '3' qyxh, '完成率' qy, sum(mk_cszb_db.dbzb) zbe, '' zbdw from bm_ytdw,mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf) a, /* 指标完成总计 */ (select 'zzzz' bkdm, sum(mk_csyb_dbsc.zbe) zbe from bm_ytdw,mk_csyb_dbsc where bm_ytdw.dm = mk_csyb_dbsc.dwdm and mk_csyb_dbsc.ny = p_ny) b ); /****************结束 总 计 ****************/
dbms_output.put_line('东部success!!!'|| p_region ||' '|| p_ny ||' '|| p_loginid ||' '|| p_deptno); end if;
commit; exception when others then rollback;end mk_csyb_xxsc;
注册会员，创建你的web开发资料库，
