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;----------------------------------------------東部市場(chǎng)----------------------------------------- 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) ( /* 東部市場(chǎng)創(chuàng)收月報(bào)mk_csyb_dbsc中的指標(biāo)完成 */ 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表中所有的東部創(chuàng)收年度指標(biāo) */ 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, '年度指標(biāo)' 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) ( /* 每個(gè)公司的指標(biāo)完成率 */ 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) ( /* 每個(gè)公司的指標(biāo)完成總計(jì) */ 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, '總計(jì)' 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) ( /*********************************************/ /****************開(kāi)始 小 計(jì) ****************/ /*按區(qū)塊和區(qū)域分組的 各油區(qū)指標(biāo)完成小計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 計(jì)**' 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) ( /* 按區(qū)塊劃分的 年度指標(biāo)小計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 計(jì)**' gsmc, p_ny ny, '0' qyxh, '年度指標(biāo)' 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) ( /* 按區(qū)塊分組的 指標(biāo)完成總計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 計(jì)**' gsmc, p_ny ny, '2' qyxh, '總計(jì)' 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) ( /* 按區(qū)塊分組的 小計(jì)的指標(biāo)完成率 */ 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 /*----- 按區(qū)塊劃分的 年度指標(biāo)小計(jì)*/ (select max(bm_ytdw.bkdm) bkdm,bm_ytdw.bk bkmc, 200 gsxh, '**小 計(jì)**' 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, /*----- 按區(qū)塊分組的 指標(biāo)完成總計(jì)*/ (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(+) /****************結(jié)束 小 計(jì) ****************/ ); insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw) ( /****************開(kāi)始 總 計(jì) ****************/ /* 年度指標(biāo)額的總計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm,'=====總 計(jì)=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '0' qyxh, '年度指標(biāo)' 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) ( /* 各個(gè)域區(qū)的指標(biāo)完成總計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm, '=====總 計(jì)=====' 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) ( /* 指標(biāo)完成總計(jì) */ select p_loginid loginid, p_deptno deptno, p_region region, 'zzzz' bkdm, '=====總 計(jì)=====' bkmc, 200 gsxh, ' ' gsmc, p_ny ny, '2' qyxh, '總計(jì)' 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) ( /* 總計(jì)完成率 */ 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 /* 年度指標(biāo)額的總計(jì) */ (select 'zzzz' bkdm,'=====總 計(jì)=====' 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, /* 指標(biāo)完成總計(jì) */ (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 ); /****************結(jié)束 總 計(jì) ****************/
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;
注冊(cè)會(huì)員，創(chuàng)建你的web開(kāi)發(fā)資料庫(kù)，
