IDC资讯中心
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;
