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; -------------------------------------------- Eastern Market ----------------------------------------- if p_region = 'DB' THEN DBMS_OUTPUT. PUT_LINE ('East Before !!!' || p_ny || '|| p_loginid ||' '|| p_deptno); Insert Into MK_CSYB_TEMP (Loginid, Deptno, Region, BKDM, BKMC, GSXH , GSMC, NY, QYXH, QY, ZBE, ZBDW) (/ * Eastern Market Breakfast "MK_CSYB_DBSC Indicators Complete * / 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 the table all the eastern revenue annual index * / 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, 'Annual Indicators' 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) (/ * Each company's index completion rate * / 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, 'Completion Rate' Newqy, Decode (Max (MAX (Mk_CSZB_DB.DB.DB), 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) (/ * Each company's index completed total * / 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, 'total' 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) (/ ************************************************************************* / / **** ************* Start small account **************** / * complete small tabs in various oil area indicators of blocks and area groups / Select p_loginid loginid, p_deptno deptno, p_region region, max (bm_ytdw.bkdm) BKDM, BM_YTDW.BK BKMC, 200 GSXH, '** small meter **' 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) (/ * Divided annual indicator subtitles * / select p_loginid loginid, p_deptno deptno, p_region region, max (B m_ytdw.bkdm) BKDM, BM_YTDW.BK BKMC, 200 GSXH, '** small meter **' GSMC, P_NY NY, '0' QYXH, 'Annual Indicators' 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) (/ * Complete the indicator of blocks * / select p_loginid loginid, p_deptno deptno, p_region region, max (bm_ytdw.bkdm) BKDM, BM_YTDW.BK BKMC, 200 GSXH, '** small meter **' GSMC , p_ny ny, '2' qyxh, 'total'
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) (/ * Subtituetic completion rate * / 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 / * ----- Annual indicator small count * / (SELECT MAX (BM_YTDW.BKDM) BKDM, BM_YTDW.BK BKMC, 200 GSXH, '* * Small Meter ** 'GSMC, P_NY NY,' 3 'qyxh,' Completion Rate '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, / * ----- Index block grouping is completed by a total * / (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) (/ *************** Start Total **************** / * annual indicator total * / select p_loginid loginid, p_deptno deptno, p_region region, 'zzz' bkdm, '===== Total meter = ==
== 'BKMC, 200 GSXH,' 'GSMC, P_NY NY,' 0 'qyxh,' annual indicator 'newqy, sum (mk_cszb_db.db) 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) (/ * Indicators of the various domains completed total * / SELECT P_Loginid Loginid , p_deptno deptno, p_region region, 'zzzz' bkdm, '===== total =====' 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) (/ * Indicator Complete total * / select p_loginid loginid, p_deptno deptno, p_region region, 'zzz' bkdm, '===== total =====' bkmc 200 GSXH , '' GSMC, p_ny NY, '2' QYXH, 'total' 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) (/ * total completion rate * / 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 / * The total number of annual indicators * / (Select 'zzz' BKDM, '===== Total meter =====' BKMC, 200 GSXH, '' GSMC , p_ny ny, '3' qyxh, 'completion rate' qy, sum (mk_cszb_db.db) zbe, '' zbdw from bm_ytdw, mk_cszb_db where bm_ytdw.dm = mk_cszb_db.dw and mk_cszb_db.nf = p_nf) a, / * indicator Complete total * / (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); / ********* ******* End total **************** / dBMS_OUTPUT.PUT_LINE ('East Success !!!' || p_regio N || '' || p_ny || '' || p_loginid || '' || p_deptno; endiff;
COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END MK_CSYB_XXSC;
