任务需求

每月按16日中午的时间点发布,16日早上可以调度起来了,不用考虑前置接口具备情况,发布完在群里发下通知即可。

手动采集

接口机采集

## 数据位置
水滴安全 203接口机

# 采集接口文件到HDFS的Shell指令
# 切换到指定目录
cd /data1/hx_data
# 却换到Hadoop用户获取操作权限
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
# 删除目录下已上传的接口文件
hadoop fs -rm /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/*202110*
export HADOOP_USER_NAME=hh_process_cjzh_dw_cb
/tenants/hh_process_cjzh_dw_cb/work/hx_data

# 将所有接口文件全部重新推送
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
hadoop fs -put *  /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/

行云采集

ALTER TABLE SRC.SRC_M_BCA03014 ADD PARTITION P202103 (MONTH_ID='202103'); --添加分区
ALTER TABLE SRC.SRC_M_BCA03014 TRUNCATE PARTITION P202103 ; --清空分区

INSERT INTO /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03014(
LOCAL_NET,GRID_ID,GRID_NAME,GRID_ACCT_TOTAL,GRID_INCOME_2G,GRID_INCOME_3G,GRID_INCOME_4G,GRID_INCOME_5G,GRID_INCOME_BB,GRID_INCOME_FX,GRID_INCOME_OTHER,GRID_MARKET_COST,CHAN_FEE,SOSIAL_CHAN_FEE,OWN_CHAN_FEE,CUST_SERV_COST,CUST_HOLD_COST,AD_FEE,GRID_SETTLE_PAY,USER_GET_COST,OUT_SERV_FEE,CLOSE_OUT,BUSINESS_OUT,SERV_OUT,BAD_RESERVES,ICT_COST,PORT_SUBSIDY,SALE_LOSS,OTHER_COST,LABOR_COST,GRID_MARGIN_RATE,FULL_WORKERS_NUM,CONTRACT_WORKERS_NUM,DISPATCH_WORKERS_NUM,CLOSE_WORKERS_NUM,GRID_SALE_NUM,GRID_AVG_CAPACITY,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
) 
PARTITION ON(MONTH_ID='202103') 
'HDFS:/TENANTS/HH_PROCESS_CJZH_SRC_REALTIME/WORK/HH_PROCESS_CJZH_SRC_REALTIME_WG/HX_DATA/CMBBIDWAL03014A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

数据加工

DM层脚本开发

网格人均产能

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_GRID_AVG_CAPACITY_M(V_DATE  IN VARCHAR,V_RETCODE OUT VARCHAR,V_RETINFO OUT VARCHAR) AS
    /*@
    
    ****************************************************************
    *名称 --%@NAME:                        划小-1.网格人均产能
    *功能描述 --%@COMMENT:            1.网格人均产能
    *执行周期 --%@PERIOD:               M
    *参数 --%@PARAM:              
    *参数 --%@PARAM:V_RETCODE           过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO           过程运行结束成功与否描述
    *创建人 --%@CREATOR:                FX
    *创建时间 --%@CREATED_TIME:         2022.01.18
    *备注 --%@REMARK:
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:               
    *目标表 --%@TO:                 
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_PKG       VARCHAR(40);
    V_PROCNAME  VARCHAR(40);
    V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
    V_TABLENAME  VARCHAR(60);
    V_STARTTIME DATE; --脚本开始执行时间
    V_SQL       VARCHAR(4000);
    V_CNT       NUMBER := 0;
    V_CNT2     NUMBER := 0;
    V_PART_NAME VARCHAR(100);

BEGIN
    V_STARTTIME := SYSDATE; --脚本开始执行时间
    V_PKG       := 'DM_M_DEV';
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_AVG_CAPACITY_M'; --过程名
    V_TABLENAME  :='DM_KPI_DATA_GRID_AVG_CAPACITY_M';
    V_PART_NAME:='PART'||V_DATE||'';

  
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
     --
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
    EXECUTE IMMEDIATE V_SQL; 
        --
        
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
               NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
               NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
               NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
               NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
          FROM SRC.SRC_M_BCA03014
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';

    EXECUTE IMMEDIATE V_SQL;
    
   
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
               NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
               NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
               NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
               NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
          FROM SRC.SRC_M_BCA03014
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';

    EXECUTE IMMEDIATE V_SQL;
   
   
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M''
AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
     
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
        
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       DECODE(NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0),
              0,
              0,
              NVL(SUM(GRID_ACCT_TOTAL), 0) /
              (NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0)))
  FROM (
       SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME
         UNION ALL
        --地市汇总
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --全国
 V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       DECODE(NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0),
              0,
              0,
              NVL(SUM(GRID_ACCT_TOTAL), 0) /
              (NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0)))
  FROM (SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
      
   --北10
   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       DECODE(NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0),
              0,
              0,
              NVL(SUM(GRID_ACCT_TOTAL), 0) /
              (NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
      
   --南21
   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       DECODE(NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0),
              0,
              0,
              NVL(SUM(GRID_ACCT_TOTAL), 0) /
              (NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID,GRID_ID,GRID_NAME
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
     
   
    --网格汇总
   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
              AREA_ORD,
              AREA_NAME,
               ''-1''   GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
               NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
               NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
               NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
               NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
               DECODE(NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0),
              0,
              0,
              NVL(SUM(GRID_ACCT_TOTAL), 0) /
              (NVL(SUM(FULL_WORKERS_NUM), 0) + NVL(SUM(GRID_SALE_NUM), 0)))
          FROM DM.DM_KPI_DATA_GRID_AVG_CAPACITY_M
         WHERE MONTH_ID = '''||V_DATE||''' AND PROV_ID NOT IN (''111'',''112'',''113'')
         GROUP BY MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
              AREA_ORD,
              AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
       

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

网格毛利率

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_GRID_MARGIN_RATE_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-2.网格毛利率
  *功能描述 --%@COMMENT:    2.网格毛利率
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            lz
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(10000);
  V_CNT       NUMBER := 0;
  V_CNT2     NUMBER := 0;
  V_PART_NAME VARCHAR(100);

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_DM_KPI_DATA_GRID_MARGIN_RATE_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_GRID_MARGIN_RATE_M';
  V_PART_NAME:='PART'||V_DATE||'';
  
    
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M_MID2''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID2 ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID2 TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M_MID2 partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
               CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0)  END GRID_MARKET_COST,
               NVL(SUM(TO_NUMBER(CHAN_FEE)), 0) CHAN_FEE,
               NVL(SUM(TO_NUMBER(SOSIAL_CHAN_FEE)), 0) SOSIAL_CHAN_FEE,
               NVL(SUM(TO_NUMBER(OWN_CHAN_FEE)), 0) OWN_CHAN_FEE,
               NVL(SUM(TO_NUMBER(CUST_SERV_COST)), 0) CUST_SERV_COST,
               NVL(SUM(TO_NUMBER(CUST_HOLD_COST)), 0) CUST_HOLD_COST,
               NVL(SUM(TO_NUMBER(AD_FEE)), 0) AD_FEE,
               NVL(SUM(TO_NUMBER(GRID_SETTLE_PAY)), 0) GRID_SETTLE_PAY,
               NVL(SUM(TO_NUMBER(USER_GET_COST)), 0) USER_GET_COST,
               CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0)  END OUT_SERV_FEE,
               CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE  NVL(TO_NUMBER(CLOSE_OUT), 0) END CLOSE_OUT,
               NVL(SUM(TO_NUMBER(BUSINESS_OUT)), 0) BUSINESS_OUT,
               NVL(SUM(TO_NUMBER(SERV_OUT)), 0) SERV_OUT,
               NVL(SUM(TO_NUMBER(BAD_RESERVES)), 0) BAD_RESERVES,
               NVL(SUM(TO_NUMBER(ICT_COST)), 0) ICT_COST,
               NVL(SUM(TO_NUMBER(PORT_SUBSIDY)), 0) PORT_SUBSIDY,
               NVL(SUM(TO_NUMBER(SALE_LOSS)), 0) SALE_LOSS,
               NVL(SUM(TO_NUMBER(OTHER_COST)), 0) OTHER_COST,
               CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0)  END LABOR_COST
          FROM SRC.SRC_M_BCA03014 
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME,
                           CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0)  END,
                           CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0)  END,
                           CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0)  END,
                           CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE  NVL(TO_NUMBER(CLOSE_OUT), 0) END';

EXECUTE IMMEDIATE V_SQL;
    

 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1 ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1 TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1 partition on(MONTH_ID='''||V_DATE||''')
SELECT T.MONTH_ID MONTH_ID,
               T.PROV_ID PROV_ID,
               T.AREA_ID AREA_ID,
               T.GRID_ID GRID_ID,
               T.GRID_NAME GRID_NAME,
               NVL(SUM(T.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(T.GRID_MARKET_COST), 0) GRID_MARKET_COST,
               NVL(SUM(T.CHAN_FEE), 0) CHAN_FEE,
               NVL(SUM(T.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
               NVL(SUM(T.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
               NVL(SUM(T.CUST_SERV_COST), 0) CUST_SERV_COST,
               NVL(SUM(T.CUST_HOLD_COST), 0) CUST_HOLD_COST,
               NVL(SUM(T.AD_FEE), 0) AD_FEE,
               NVL(SUM(T.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
               NVL(SUM(T.USER_GET_COST), 0) USER_GET_COST,
               NVL(SUM(T.OUT_SERV_FEE), 0)  OUT_SERV_FEE,
               NVL(SUM(T.CLOSE_OUT), 0) CLOSE_OUT,
               NVL(SUM(T.BUSINESS_OUT), 0) BUSINESS_OUT,
               NVL(SUM(T.SERV_OUT), 0) SERV_OUT,
               NVL(SUM(T.BAD_RESERVES), 0) BAD_RESERVES,
               NVL(SUM(T.ICT_COST), 0) ICT_COST,
               NVL(SUM(T.PORT_SUBSIDY), 0) PORT_SUBSIDY,
               NVL(SUM(T.SALE_LOSS), 0) SALE_LOSS,
               NVL(SUM(T.OTHER_COST), 0) OTHER_COST,
               NVL(SUM(T.LABOR_COST),0) LABOR_COST,
               NVL(SUM(T.SMART_COST_FEE), 0) SMART_COST_FEE,
               NVL(SUM(T.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
               NVL(SUM(T.NET_HOLD_COST), 0) NET_HOLD_COST,
               NVL(SUM(T.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
               NVL(SUM(T.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(T.SMART_OTHER_COST), 0) SMART_OTHER_COST
 FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               AREA_ID AREA_ID,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
               NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
               NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
               NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
               NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
               NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
               NVL(SUM(AD_FEE), 0) AD_FEE,
               NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
               NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
               NVL(SUM(OUT_SERV_FEE), 0)  OUT_SERV_FEE,
               NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
               NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
               NVL(SUM(SERV_OUT), 0) SERV_OUT,
               NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
               NVL(SUM(ICT_COST), 0) ICT_COST,
               NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
               NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
               NVL(SUM(OTHER_COST), 0) OTHER_COST,
               NVL(SUM(LABOR_COST),0) LABOR_COST,
               NULL SMART_COST_FEE,
               NULL CUST_ACCESS_COST,
               NULL NET_HOLD_COST,
               NULL ACCT_LABOR_COST,
               NULL CLOSE_OUT_FEE,
               NULL SMART_OTHER_COST 
               FROM DM_KPI_DATA_GRID_MARGIN_RATE_M_MID2 WHERE MONTH_ID = '''||V_DATE||'''
               GROUP BY MONTH_ID,
               PROV_ID,
               AREA_ID,
               GRID_ID,
               GRID_NAME
    UNION ALL
     SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET AREA_ID,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NULL GRID_ACCT_TOTAL,
               NULL GRID_MARKET_COST,
               NULL CHAN_FEE,
               NULL SOSIAL_CHAN_FEE,
               NULL OWN_CHAN_FEE,
               NULL CUST_SERV_COST,
               NULL CUST_HOLD_COST,
               NULL AD_FEE,
               NULL GRID_SETTLE_PAY,
               NULL USER_GET_COST,
               NULL OUT_SERV_FEE,
               NULL CLOSE_OUT,
               NULL BUSINESS_OUT,
               NULL SERV_OUT,
               NULL BAD_RESERVES,
               NULL ICT_COST,
               NULL PORT_SUBSIDY,
               NULL SALE_LOSS,
               NULL OTHER_COST,
               NULL LABOR_COST,
               NVL(SUM(TO_NUMBER(SMART_COST_FEE)), 0) SMART_COST_FEE,
               NVL(SUM(TO_NUMBER(CUST_ACCESS_COST)), 0) CUST_ACCESS_COST,
               NVL(SUM(TO_NUMBER(NET_HOLD_COST)), 0) NET_HOLD_COST,
               NVL(SUM(TO_NUMBER(ACCT_LABOR_COST)), 0) ACCT_LABOR_COST,
               NVL(SUM(TO_NUMBER(CLOSE_OUT_FEE)), 0) CLOSE_OUT_FEE,
               NVL(SUM(TO_NUMBER(OTHER_COST)), 0) SMART_OTHER_COST
          FROM SRC.SRC_M_BCA03016
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME) T 
 GROUP BY T.MONTH_ID,
               T.PROV_ID,
               T.AREA_ID,
               T.GRID_ID,
               T.GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
  
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M_MID''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT A.MONTH_ID MONTH_ID,
       A.PROV_ID PROV_ID,
       B.PROV_ORD PROV_ORD,
       B.PROV_NAME PROV_NAME,
       A.AREA_ID LOCAL_NET,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
       NVL(B.AREA_NAME,''其他'') AREA_NAME,
       TRIM(A.GRID_ID) GRID_ID,
       TRIM(A.GRID_NAME) GRID_NAME,
       NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(A.AD_FEE), 0) AD_FEE,
       NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
       NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(A.ICT_COST), 0) ICT_COST,
       NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
       NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
       NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
       NULL
  FROM DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1 A JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) B ON A.AREA_ID = B.LOCAL_NET
 WHERE A.MONTH_ID = '''||V_DATE||'''
 GROUP BY A.MONTH_ID,
       A.PROV_ID,
       B.PROV_ORD,
       B.PROV_NAME,
       A.AREA_ID,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')),
       NVL(B.AREA_NAME,''其他''),
       TRIM(A.GRID_ID),
       TRIM(A.GRID_NAME)';

EXECUTE IMMEDIATE V_SQL;
    
V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT A.MONTH_ID MONTH_ID,
       A.PROV_ID PROV_ID,
       B.PROV_ORD PROV_ORD,
       B.PROV_NAME PROV_NAME,
       A.AREA_ID LOCAL_NET,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
       NVL(B.AREA_NAME,''其他'') AREA_NAME,
       TRIM(A.GRID_ID) GRID_ID,
       TRIM(A.GRID_NAME) GRID_NAME,
       NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(A.AD_FEE), 0) AD_FEE,
       NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
       NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(A.ICT_COST), 0) ICT_COST,
       NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
       NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
       NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
       NULL
  FROM DM_KPI_DATA_GRID_MARGIN_RATE_M_MID1 A JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) B ON A.AREA_ID = B.AREA_ID
 WHERE A.MONTH_ID = '''||V_DATE||'''
 GROUP BY A.MONTH_ID,
       A.PROV_ID,
       B.PROV_ORD,
       B.PROV_NAME,
       A.AREA_ID,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')),
       NVL(B.AREA_NAME,''其他''),
       TRIM(A.GRID_ID),
       TRIM(A.GRID_NAME)';

EXECUTE IMMEDIATE V_SQL;
   
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
--地市汇总
   V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   
   --全国
 V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID';

EXECUTE IMMEDIATE V_SQL;
    
   --北10
   V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID ';

EXECUTE IMMEDIATE V_SQL;
    
   --南21
   V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID';

EXECUTE IMMEDIATE V_SQL;
    

       --网格汇总
   V_SQL:='insert into DM_KPI_DATA_GRID_MARGIN_RATE_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            ''-1''  GRID_ID,
             ''汇总''  GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(AD_FEE), 0) AD_FEE,
       NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(SERV_OUT), 0) SERV_OUT,
       NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(ICT_COST), 0) ICT_COST,
       NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(OTHER_COST), 0) OTHER_COST,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              (NVL(SUM(GRID_ACCT_TOTAL), 0)-NVL(SUM(GRID_MARKET_COST), 0)) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_MARGIN_RATE_M
         WHERE MONTH_ID = '''||V_DATE||''' and prov_id not in(''111'',''112'',''113'')
         GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

智家工程师装维产能

CREATE  OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_SMART_CAPACITY_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-3.智家工程师装维产能
  *功能描述 --%@COMMENT:        3.智家工程师装维产能
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            lz
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(4000);
  V_CNT       NUMBER := 0;
  V_CNT2     NUMBER := 0;
  V_PART_NAME VARCHAR(100);

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_DM_KPI_DATA_SMART_CAPACITY_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_SMART_CAPACITY_M';
  V_PART_NAME:='PART'||V_DATE||'';
  
    

  
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M_MID''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
               NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
               NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
               NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
               NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
               NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
               NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
               NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
               NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
          FROM SRC.SRC_M_BCA03016
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID,LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL;
    
   
V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
               NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
               NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
               NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
               NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
               NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
               NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
               NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
               NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
          FROM SRC.SRC_M_BCA03016
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID,LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL;
   
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(TOTAL_ORDER_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM (SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME
        UNION ALL
        --地市汇总
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME)
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --全国
 V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(TOTAL_ORDER_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM (SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --北10
   V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(TOTAL_ORDER_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --南21
   V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
       NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
       NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
       NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(TOTAL_ORDER_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   V_SQL:='insert into DM_KPI_DATA_SMART_CAPACITY_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
            PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
              AREA_ORD,
              AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总''  GRID_NAME,
               NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
               NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
               NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
               NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM,
               DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(TOTAL_ORDER_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
          FROM DM.DM_KPI_DATA_SMART_CAPACITY_M
         WHERE MONTH_ID = '''||V_DATE||''' and prov_id not in (''111'',''112'',''113'')
         GROUP BY MONTH_ID,
            PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
              AREA_ORD,
              AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

智家工程师人均百元等效发展量

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_SMART_AVG_NUM_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-4.智家工程师人均百元等效发展量
  *功能描述 --%@COMMENT:    4.智家工程师人均百元等效发展量
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            lz
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(4000);
  V_CNT       NUMBER := 0;
  V_CNT2     NUMBER := 0;
  V_PART_NAME VARCHAR(100);

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_DM_KPI_DATA_SMART_AVG_NUM_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_SMART_AVG_NUM_M';
  V_PART_NAME:='PART'||V_DATE||'';
  
    

  
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M_MID''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0)/100 SMART_DEV_NUM,
               NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
               NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
               NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
               NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
               NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
          FROM SRC.SRC_M_BCA03016
         WHERE MONTH_ID = '''||V_DATE||'''
           AND EQU_DEV_NUM NOT IN (''#N/A'')
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL;
    
   
V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M_MID partition on(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0)/100 SMART_DEV_NUM,
               NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
               NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
               NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
               NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
               NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
               NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
               NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
          FROM SRC.SRC_M_BCA03016
         WHERE MONTH_ID = '''||V_DATE||'''
           AND EQU_DEV_NUM NOT IN (''#N/A'')
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL; 
   
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(SMART_DEV_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM (SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME
        UNION ALL
        --地市汇总
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME)
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --全国
 V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(SMART_DEV_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM (SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --北10
   V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(SMART_DEV_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --南21
   V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
       NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
       NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
       NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
       NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
       NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
       NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
       NVL(SUM(COOP_NUM), 0) COOP_NUM,
       DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(SMART_DEV_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
  FROM ( 
        SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   

   V_SQL:='insert into DM_KPI_DATA_SMART_AVG_NUM_M partition on(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            ''-1'' GRID_ID,
             ''汇总'' GRID_NAME,
               NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
               NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
               NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
               NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
               NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
               NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
               NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
               NVL(SUM(COOP_NUM), 0) COOP_NUM,
               DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0),
              0,
              0,
              NVL(SUM(SMART_DEV_NUM), 0) /
              (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
          FROM DM.DM_KPI_DATA_SMART_AVG_NUM_M
         WHERE MONTH_ID = '''||V_DATE||''' and prov_id not in (''111'',''112'',''113'')
         GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

宽带资源利用率

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_KD_USE_RATE_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-5.宽带资源利用率
  *功能描述 --%@COMMENT:    5.宽带资源利用率
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            LZ
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(4000);
  V_CNT       NUMBER := 0;
  V_CNT2     NUMBER := 0;
  V_PART_NAME VARCHAR(100);

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_DM_KPI_DATA_KD_USE_RATE_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_KD_USE_RATE_M';
  V_PART_NAME:='PART'||V_DATE||'';
  
    

  
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M_MID''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NULL,
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
               NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
          FROM SRC.SRC_M_BCA03017
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID,LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL;
    
   
V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NULL,
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
               NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
          FROM SRC.SRC_M_BCA03017
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID,LOCAL_NET, GRID_ID, GRID_NAME) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';
EXECUTE IMMEDIATE V_SQL;
   
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       DECODE(NVL(SUM(BB_PORT_NUM), 0),
              0,
              0,
              NVL(SUM(BB_USER_NUM), 0) /
              NVL(SUM(BB_PORT_NUM), 0)),
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM (
       SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME
         UNION ALL
        --地市汇总
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --全国
 V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       DECODE(NVL(SUM(BB_PORT_NUM), 0),
              0,
              0,
              NVL(SUM(BB_USER_NUM), 0) /
              NVL(SUM(BB_PORT_NUM), 0)),
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM (SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --北10
   V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       DECODE(NVL(SUM(BB_PORT_NUM), 0),
              0,
              0,
              NVL(SUM(BB_USER_NUM), 0) /
              NVL(SUM(BB_PORT_NUM), 0)),
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM ( 
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --南21
   V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       DECODE(NVL(SUM(BB_PORT_NUM), 0),
              0,
              0,
              NVL(SUM(BB_USER_NUM), 0) /
              NVL(SUM(BB_PORT_NUM), 0)),
       NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
       NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
  FROM ( 
        SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   
   V_SQL:='INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
 SELECT MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
             AREA_ORD,
             AREA_NAME,
             ''-1'' GRID_ID,
              ''汇总'' GRID_NAME,
             DECODE(NVL(SUM(BB_PORT_NUM), 0),
              0,
              0,
              NVL(SUM(BB_USER_NUM), 0) /
              NVL(SUM(BB_PORT_NUM), 0)),
               NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
               NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
          FROM DM.DM_KPI_DATA_KD_USE_RATE_M
         WHERE MONTH_ID = '''||V_DATE||''' AND PROV_ID NOT IN (''111'',''112'',''113'')
         GROUP BY MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
             AREA_ORD,
             AREA_NAME';
EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

网格人力资源总投入占收比

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_GRID_PAYPRO_RATE_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-6.网格人力资源总投入占収比
  *功能描述 --%@COMMENT:    6.网格人力资源总投入占収比
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            LZ
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(4000);
  V_CNT       NUMBER := 0;
  V_CNT2     NUMBER := 0;
  V_PART_NAME VARCHAR(100);

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_DM_KPI_DATA_GRID_PAYPRO_RATE_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_GRID_PAYPRO_RATE_M';
  V_PART_NAME:='PART'||V_DATE||'';
  
    

  
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
               NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
               NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
               NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
               NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
               CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0)  END LABOR_COST,
               CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
               TO_NUMBER(''0'') SALE_OUT_FEE
          FROM SRC.SRC_M_BCA03018
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME,
                           CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0) END,
                           CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
           ) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';

EXECUTE IMMEDIATE V_SQL;
    
   
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       NULL
  FROM (SELECT MONTH_ID MONTH_ID,
               PROV_ID PROV_ID,
               LOCAL_NET LOCAL_NET,
               GRID_ID GRID_ID,
               GRID_NAME GRID_NAME,
               NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
               NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
               NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
               NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
               NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
               NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
               CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0)  END LABOR_COST,
               CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
               TO_NUMBER(''0'') SALE_OUT_FEE
          FROM SRC.SRC_M_BCA03018
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,PROV_ID, LOCAL_NET, GRID_ID, GRID_NAME,
                           CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(LABOR_COST),0) END,
                           CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'')  ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
           ) T1 JOIN 
       (SELECT * FROM DM.DMCODE_GRID_PROV) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
       T1.PROV_ID,
       T2.PROV_ORD,
       T2.PROV_NAME,
       T1.LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
       NVL(T2.AREA_NAME,''其他''),
       TRIM(T1.GRID_ID),
       TRIM(T1.GRID_NAME)';

EXECUTE IMMEDIATE V_SQL;
   
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M''
AND PART_NAME='''||V_PART_NAME||'''';

 EXECUTE IMMEDIATE V_SQL INTO V_CNT2;
    
 IF V_CNT2 < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              NVL(SUM(GRID_RES_PAY), 0) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
  FROM (
       SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               AREA_ID,
               AREA_ORD,
               AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID,
                  PROV_ID,
                  PROV_ORD,
                  PROV_NAME,
                  AREA_ID,
                  AREA_ORD,
                  AREA_NAME,
                  GRID_ID,
                  GRID_NAME
         UNION ALL
        --地市汇总
        SELECT MONTH_ID,
               PROV_ID,
               PROV_ORD,
               PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               GRID_ID,
               GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID, PROV_ID, PROV_ORD, PROV_NAME, GRID_ID, GRID_NAME
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --全国
 V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              NVL(SUM(GRID_RES_PAY), 0) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
  FROM (SELECT MONTH_ID,
               ''111'' PROV_ID,
               TO_NUMBER(''-3'') PROV_ORD,
               ''全国'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --北10
   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              NVL(SUM(GRID_RES_PAY), 0) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
  FROM ( 
        SELECT MONTH_ID,
               ''112'' PROV_ID,
               TO_NUMBER(''-2'') PROV_ORD,
               ''北10'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   --南21
   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
       PROV_ID,
       PROV_ORD,
       PROV_NAME,
       AREA_ID,
       AREA_ORD,
       AREA_NAME,
       GRID_ID,
       GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
       NVL(SUM(LABOR_COST), 0) LABOR_COST,
       NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
       DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              NVL(SUM(GRID_RES_PAY), 0) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
  FROM ( 
        SELECT MONTH_ID,
               ''113'' PROV_ID,
               TO_NUMBER(''-1'') PROV_ORD,
               ''南21'' PROV_NAME,
               ''-1'' AREA_ID,
               TO_NUMBER(''-1'') AREA_ORD,
               ''汇总'' AREA_NAME,
               ''-1'' GRID_ID,
               ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
         WHERE MONTH_ID = '''||V_DATE||'''
           AND PROV_ID IN (SELECT PROV_ID FROM DM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
         GROUP BY MONTH_ID
         )
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME';
EXECUTE IMMEDIATE V_SQL;
    

   V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
             AREA_ORD,
             AREA_NAME,
             ''-1'' GRID_ID,
              ''汇总'' GRID_NAME,
               NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
               NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
               NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
               NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
               NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
               NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
               NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
               NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
               NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
               NVL(SUM(LABOR_COST), 0) LABOR_COST,
               NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
               NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
               DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0),
              0,
              0,
              NVL(SUM(GRID_RES_PAY), 0) /
              NVL(SUM(GRID_ACCT_TOTAL), 0))
          FROM DM.DM_KPI_DATA_GRID_PAYPRO_RATE_M
         WHERE MONTH_ID = '''||V_DATE||''' AND PROV_ID NOT IN (''111'',''112'',''113'')
         GROUP BY MONTH_ID,
             PROV_ID,
             PROV_ORD,
             PROV_NAME,
             AREA_ID,
             AREA_ORD,
             AREA_NAME';
EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

总览1_综合网格效能评价总体情况

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_GRID_ALL_M (
    V_DATE IN VARCHAR,
    V_RETCODE OUTPUT VARCHAR,
    V_RETINFO OUTPUT VARCHAR
    ) AS

    /*@
    
    ****************************************************************
    *名称 --%@NAME:          划小-总览1_综合网格效能评价总体情况
    *功能描述 --%@COMMENT:    总览1_综合网格效能评价总体情况
    *执行周期 --%@PERIOD:           M
    *参数 --%@PARAM:              
    *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
    *创建人 --%@CREATOR:            FX
    *创建时间 --%@CREATED_TIME:     2022.01.18
    *备注 --%@REMARK:
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:               
    *目标表 --%@TO:                 
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    V_PKG       VARCHAR(40);
    V_PROCNAME  VARCHAR(40);
    V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
    V_TABLENAME  VARCHAR(60);
    V_STARTTIME DATE; --脚本开始执行时间
    V_SQL       VARCHAR(4000);
    V_CNT       NUMBER := 0;
    V_PART_NAME VARCHAR(100);
    V_LMONTH     VARCHAR(6); --上月

BEGIN
    V_STARTTIME := SYSDATE; --脚本开始执行时间
    V_PKG       := 'DM_M_DEV';
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_ALL_M'; --过程名
    V_TABLENAME  :='DM_KPI_DATA_GRID_ALL_M';
    V_PART_NAME:='PART'||V_DATE||'';
    V_LMONTH  := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
  
    
   
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME = '' DM_KPI_DATA_GRID_ALL_M '' AND PART_NAME = '''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
        EXECUTE IMMEDIATE V_SQL;
    
    END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION PART'|| V_DATE ||'';
    EXECUTE IMMEDIATE V_SQL; 
    
        
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 综合网格效能评价概况 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';

    EXECUTE IMMEDIATE V_SQL;
    
    
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 网格毛利率 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';
    EXECUTE IMMEDIATE V_SQL;
    
   
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 智家工程师装维产能 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_SMART_CAPACITY_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';

    EXECUTE IMMEDIATE V_SQL;
    
   
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 智家工程师人均百元等效发展量 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_SMART_AVG_NUM_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';
    EXECUTE IMMEDIATE V_SQL;
    
   
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 宽带资源利用率 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_KD_USE_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';
    EXECUTE IMMEDIATE V_SQL;
    

    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 '') RPT_NAME,
        '' 人力资源投入占收比 '' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''', '''||V_LMONTH||''') AND GRID_ID = '' - 1 ''
        ) T1
    LEFT JOIN DM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, '' 汇总 ''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';
    EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

总览2_数据分省总览

CREATE OR REPLACE PROCEDURE P_BDI_DM_KPI_DATA_GRID_PROV_M(V_DATE  IN VARCHAR,
                                                   V_RETCODE OUT VARCHAR,
                                                   V_RETINFO OUT VARCHAR) AS
  /*@
  
  ****************************************************************
  *名称 --%@NAME:          划小-总览2_数据分省总览
  *功能描述 --%@COMMENT:    总览2_数据分省总览
  *执行周期 --%@PERIOD:           M
  *参数 --%@PARAM:              
  *参数 --%@PARAM:V_RETCODE       过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO       过程运行结束成功与否描述
  *创建人 --%@CREATOR:            LZ
  *创建时间 --%@CREATED_TIME:     20210428
  *备注 --%@REMARK:
  *修改记录 --%@MODIFY:
  *来源表 --%@FROM:               
  *目标表 --%@TO:                 
  *修改记录 --%@MODIFY:           
  ******************************************************************
  @*/
  V_PKG       VARCHAR(40);
  V_PROCNAME  VARCHAR(40);
  V_ROWLINE   NUMBER := 0; --记录目标表数据变化记录数
  V_TABLENAME  VARCHAR(60);
  V_STARTTIME DATE; --脚本开始执行时间
  V_SQL       VARCHAR(4000);
  V_CNT       NUMBER := 0;
  V_PART_NAME VARCHAR(100);
  V_LMONTH     VARCHAR(6); --上月

BEGIN
  V_STARTTIME := SYSDATE; --脚本开始执行时间
  V_PKG       := 'DM_M_DEV';
  V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_PROV_M'; --过程名
  V_TABLENAME  :='DM_KPI_DATA_GRID_PROV_M';
  V_PART_NAME:='PART'||V_DATE||'';
  V_LMONTH  := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
    
  
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PROV_M_MID1''
AND PART_NAME='''||V_PART_NAME||'''';
 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    

 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID1 ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID1 TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M_MID1 PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
                SUM(GRID_SALE_NUM) GRID_SALE_NUM,
                SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
                SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
                SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
                SUM(GRID_ACCT_TOTAL1) GRID_ACCT_TOTAL1,
                SUM(GRID_MARKET_COST) GRID_MARKET_COST,
                SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
                SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
                SUM(SMART_ALL_NUM) SMART_ALL_NUM,
                SUM(SMART_OUT_NUM) SMART_OUT_NUM,
                SUM(SMART_CAPACITY) SMART_CAPACITY
FROM(
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
                SUM(GRID_SALE_NUM) GRID_SALE_NUM,
                SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
                SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
                SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
                NULL GRID_ACCT_TOTAL1,
                NULL GRID_MARKET_COST,
                NULL GRID_MARGIN_RATE,
                NULL TOTAL_ORDER_NUM,
             NULL SMART_ALL_NUM,
             NULL SMART_OUT_NUM,
             NULL SMART_CAPACITY
  FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME
UNION ALL
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
             NULL GRID_SALE_NUM,
                NULL FULL_WORKERS_NUM,
                NULL GRID_AVG_CAPACITY,
                NULL GRID_ACCT_TOTAL,
                SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL1,
                SUM(GRID_MARKET_COST) GRID_MARKET_COST,
                SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
                NULL TOTAL_ORDER_NUM,
             NULL SMART_ALL_NUM,
             NULL SMART_OUT_NUM,
             NULL SMART_CAPACITY
  FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME
UNION ALL
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
             NULL GRID_SALE_NUM,
                NULL FULL_WORKERS_NUM,
                NULL GRID_AVG_CAPACITY,
                NULL GRID_ACCT_TOTAL,
                NULL GRID_ACCT_TOTAL1,
                NULL GRID_MARKET_COST,
                NULL GRID_MARGIN_RATE,
                SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
                SUM(SMART_ALL_NUM) SMART_ALL_NUM,
                SUM(SMART_OUT_NUM) SMART_OUT_NUM,
                SUM(SMART_CAPACITY) SMART_CAPACITY
  FROM DM_KPI_DATA_SMART_CAPACITY_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME)
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    

V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PROV_M_MID2''
AND PART_NAME='''||V_PART_NAME||'''';
 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    

 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID2 ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID2 TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M_MID2 PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
                SUM(SMART_DEV_NUM) SMART_DEV_NUM,
                SUM(SMART_ALL_NUM) SMART_ALL_NUM,
                SUM(SMART_OUT_NUM) SMART_OUT_NUM,
                SUM(SMART_AVG_NUM) SMART_AVG_NUM,
                SUM(BB_PORT_NUM) BB_PORT_NUM,
                SUM(BB_USER_NUM) BB_USER_NUM,
                SUM(BB_USE_RATE) BB_USE_RATE,
                SUM(TOTAL_ORDER_NUM2) TOTAL_ORDER_NUM2,
                SUM(GRID_RES_PAY) GRID_RES_PAY,
                SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
FROM(
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
                SUM(SMART_DEV_NUM) SMART_DEV_NUM,
                SUM(SMART_ALL_NUM) SMART_ALL_NUM,
                SUM(SMART_OUT_NUM) SMART_OUT_NUM,
                SUM(SMART_AVG_NUM) SMART_AVG_NUM,
                NULL BB_PORT_NUM,
                NULL BB_USER_NUM,
                NULL BB_USE_RATE,
                NULL TOTAL_ORDER_NUM2,
             NULL GRID_RES_PAY,
             NULL GRID_PAYPRO_RATE
  FROM DM_KPI_DATA_SMART_AVG_NUM_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME
UNION ALL
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
             NULL SMART_DEV_NUM,
                NULL SMART_ALL_NUM,
                NULL SMART_OUT_NUM,
                NULL SMART_AVG_NUM,
                SUM(BB_PORT_NUM) BB_PORT_NUM,
                SUM(BB_USER_NUM) BB_USER_NUM,
                SUM(BB_USE_RATE) BB_USE_RATE,
                NULL TOTAL_ORDER_NUM2,
             NULL GRID_RES_PAY,
             NULL GRID_PAYPRO_RATE
  FROM DM_KPI_DATA_KD_USE_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME
UNION ALL
SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
             NULL SMART_DEV_NUM,
                NULL SMART_ALL_NUM,
                NULL SMART_OUT_NUM,
                NULL SMART_AVG_NUM,
                NULL BB_PORT_NUM,
                NULL BB_USER_NUM,
                NULL BB_USE_RATE,
                SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL2,
                SUM(GRID_RES_PAY) GRID_RES_PAY,
                SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
  FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
    AND GRID_ID = ''-1''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME)
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   
 V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PROV_M_MID3''
AND PART_NAME='''||V_PART_NAME||'''';
 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    

 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID3 ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M_MID3 TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M_MID3 PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT  MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
                SUM(GRID_SALE_NUM) GRID_SALE_NUM,SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
                SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,SUM(GRID_ACCT_TOTAL1) GRID_ACCT_TOTAL1,SUM(GRID_MARKET_COST) GRID_MARKET_COST,
                SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,SUM(SMART_ALL_NUM) SMART_ALL_NUM,
                SUM(SMART_OUT_NUM) SMART_OUT_NUM,SUM(SMART_CAPACITY) SMART_CAPACITY,SUM(SMART_DEV_NUM) SMART_DEV_NUM,
                SUM(SMART_ALL_NUM1) SMART_ALL_NUM1,SUM(SMART_OUT_NUM1) SMART_OUT_NUM1,SUM(SMART_AVG_NUM) SMART_AVG_NUM,
                SUM(BB_PORT_NUM) BB_PORT_NUM,SUM(BB_USER_NUM) BB_USER_NUM,SUM(BB_USE_RATE) BB_USE_RATE,
                SUM(GRID_ACCT_TOTAL2) GRID_ACCT_TOTAL2,SUM(GRID_RES_PAY) GRID_RES_PAY,SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
 FROM(
SELECT MONTH_ID MONTH_ID,PROV_ID PROV_ID,PROV_ORD PROV_ORD,PROV_NAME PROV_NAME,AREA_ID AREA_ID,AREA_ORD AREA_ORD,AREA_NAME AREA_NAME,
                NVL(SUM(GRID_SALE_NUM),0) GRID_SALE_NUM,
                NVL(SUM(FULL_WORKERS_NUM),0) FULL_WORKERS_NUM,
                NVL(SUM(GRID_AVG_CAPACITY),0) GRID_AVG_CAPACITY,
                NVL(SUM(GRID_ACCT_TOTAL),0) GRID_ACCT_TOTAL,
                NVL(SUM(GRID_ACCT_TOTAL1),0) GRID_ACCT_TOTAL1,
                NVL(SUM(GRID_MARKET_COST),0) GRID_MARKET_COST,
                NVL(SUM(GRID_MARGIN_RATE),0) GRID_MARGIN_RATE,
                NVL(SUM(TOTAL_ORDER_NUM),0) TOTAL_ORDER_NUM,
                NVL(SUM(SMART_ALL_NUM),0) SMART_ALL_NUM,
                NVL(SUM(SMART_OUT_NUM),0) SMART_OUT_NUM,
                NVL(SUM(SMART_CAPACITY),0) SMART_CAPACITY,
                NULL  SMART_DEV_NUM,NULL  SMART_ALL_NUM1,NULL  SMART_OUT_NUM1,NULL  SMART_AVG_NUM,NULL  BB_PORT_NUM,
                NULL  BB_USER_NUM,NULL  BB_USE_RATE,NULL  GRID_ACCT_TOTAL2,NULL  GRID_RES_PAY,NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_GRID_PROV_M_MID1
WHERE MONTH_ID = '''||V_DATE||'''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME
UNION ALL
SELECT MONTH_ID MONTH_ID,PROV_ID PROV_ID,PROV_ORD PROV_ORD,PROV_NAME PROV_NAME,AREA_ID AREA_ID,AREA_ORD AREA_ORD,AREA_NAME AREA_NAME,
                NULL GRID_SALE_NUM,NULL FULL_WORKERS_NUM,NULL GRID_AVG_CAPACITY,NULL GRID_ACCT_TOTAL,NULL GRID_ACCT_TOTAL1,
                NULL GRID_MARKET_COST,NULL GRID_MARGIN_RATE,NULL TOTAL_ORDER_NUM,NULL SMART_ALL_NUM,NULL SMART_OUT_NUM,NULL SMART_CAPACITY,
                NVL(SUM(SMART_DEV_NUM),0) SMART_DEV_NUM,
                NVL(SUM(SMART_ALL_NUM1),0) SMART_ALL_NUM1,
                NVL(SUM(SMART_OUT_NUM1),0) SMART_OUT_NUM1,
                NVL(SUM(SMART_AVG_NUM),0) SMART_AVG_NUM,
                NVL(SUM(BB_PORT_NUM),0) BB_PORT_NUM,
                NVL(SUM(BB_USER_NUM),0) BB_USER_NUM,
                NVL(SUM(BB_USE_RATE),0) BB_USE_RATE,
                NVL(SUM(GRID_ACCT_TOTAL2),0) GRID_ACCT_TOTAL2,
                NVL(SUM(GRID_RES_PAY),0) GRID_RES_PAY,
                NVL(SUM(GRID_PAYPRO_RATE),0) GRID_PAYPRO_RATE
FROM DM_KPI_DATA_GRID_PROV_M_MID2
WHERE MONTH_ID = '''||V_DATE||'''
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME)
GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    
   
  V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS
WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PROV_M''
AND PART_NAME='''||V_PART_NAME||'''';
 EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    

 IF V_CNT < 1 THEN
      V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M ADD PARTITION PART'||V_DATE||'(MONTH_ID='''||V_DATE||''')';
     EXECUTE IMMEDIATE V_SQL;
    
   END IF;  
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION PART'|| V_DATE ||'';
        EXECUTE IMMEDIATE V_SQL; 
    
        
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,T1.PROV_ID PROV_ID,T1.PROV_ORD PROV_ORD,
T1.PROV_NAME PROV_NAME,T1.AREA_ID AREA_ID,T1.AREA_ORD AREA_ORD,T1.AREA_NAME AREA_NAME,
NVL(SUM(T1.GRID_SALE_NUM),0) GRID_SALE_NUM,
NVL(SUM(T1.FULL_WORKERS_NUM),0) FULL_WORKERS_NUM,
NVL(SUM(T1.GRID_AVG_CAPACITY),0) GRID_AVG_CAPACITY,
NVL(SUM(T1.GRID_ACCT_TOTAL),0) GRID_ACCT_TOTAL,
NVL(SUM(T2.GRID_AVG_CAP_HB),0) GRID_AVG_CAP_HB,
NVL(SUM(T1.GRID_ACCT_TOTAL1),0) GRID_ACCT_TOTAL1,
NVL(SUM(T1.GRID_MARKET_COST),0) GRID_MARKET_COST,
NVL(SUM(T1.GRID_MARGIN_RATE),0) GRID_MARGIN_RATE,
NVL(SUM(T2.GRID_MAR_RATE_HB),0) GRID_MAR_RATE_HB,
NVL(SUM(T1.TOTAL_ORDER_NUM),0) TOTAL_ORDER_NUM,
NVL(SUM(T1.SMART_ALL_NUM),0) SMART_ALL_NUM,
NVL(SUM(T1.SMART_OUT_NUM),0) SMART_OUT_NUM,
NVL(SUM(T1.SMART_CAPACITY),0) SMART_CAPACITY,
NVL(SUM(T2.SMART_CAPACITY_HB),0) SMART_CAPACITY_HB,
NVL(SUM(T1.SMART_DEV_NUM),0) SMART_DEV_NUM,
NVL(SUM(T1.SMART_ALL_NUM1),0) SMART_ALL_NUM1,
NVL(SUM(T1.SMART_OUT_NUM1),0) SMART_OUT_NUM1,
NVL(SUM(T1.SMART_AVG_NUM),0) SMART_AVG_NUM,
NVL(SUM(T2.SMART_AVG_NUM_HB),0) SMART_AVG_NUM_HB,
NVL(SUM(T1.BB_PORT_NUM),0) BB_PORT_NUM,
NVL(SUM(T1.BB_USER_NUM),0) BB_USER_NUM,
NVL(SUM(T1.BB_USE_RATE),0) BB_USE_RATE,
NVL(SUM(T2.BB_USE_RATE_HB),0) BB_USE_RATE_HB,
NVL(SUM(T1.GRID_ACCT_TOTAL2),0) GRID_ACCT_TOTAL2,
NVL(SUM(T1.GRID_RES_PAY),0) GRID_RES_PAY,
NVL(SUM(T1.GRID_PAYPRO_RATE),0) GRID_PAYPRO_RATE,
NVL(SUM(T2.GRID_PAYPRO_RATE_HB) ,0) GRID_PAYPRO_RATE_HB 
FROM
(SELECT MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME,
SUM(GRID_SALE_NUM) GRID_SALE_NUM,SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,SUM(GRID_ACCT_TOTAL1) GRID_ACCT_TOTAL1,SUM(GRID_MARKET_COST) GRID_MARKET_COST,
SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,SUM(SMART_ALL_NUM) SMART_ALL_NUM,
SUM(SMART_OUT_NUM) SMART_OUT_NUM,SUM(SMART_CAPACITY) SMART_CAPACITY,SUM(SMART_DEV_NUM) SMART_DEV_NUM,
SUM(SMART_ALL_NUM1) SMART_ALL_NUM1,SUM(SMART_OUT_NUM1) SMART_OUT_NUM1,SUM(SMART_AVG_NUM) SMART_AVG_NUM,
SUM(BB_PORT_NUM) BB_PORT_NUM,SUM(BB_USER_NUM) BB_USER_NUM,SUM(BB_USE_RATE) BB_USE_RATE,
SUM(GRID_ACCT_TOTAL2) GRID_ACCT_TOTAL2,SUM(GRID_RES_PAY) GRID_RES_PAY,SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
 FROM DM_KPI_DATA_GRID_PROV_M_MID3
 WHERE MONTH_ID = '''||V_DATE||'''
 GROUP BY MONTH_ID,PROV_ID,PROV_ORD,PROV_NAME,AREA_ID,AREA_ORD,AREA_NAME) T1,
(SELECT  MONTH_ID, PROV_ID,PROV_NAME,AREA_ID,AREA_NAME,
               NVL(SUM(GRID_AVG_CAP_HB),0) GRID_AVG_CAP_HB,
              NVL(SUM(GRID_MAR_RATE_HB),0) GRID_MAR_RATE_HB,
               NVL(SUM(SMART_CAPACITY_HB),0) SMART_CAPACITY_HB,
               NVL(SUM(SMART_AVG_NUM_HB),0) SMART_AVG_NUM_HB,
               NVL(SUM(BB_USE_RATE_HB),0) BB_USE_RATE_HB,
               NVL(SUM(GRID_PAYPRO_RATE_HB) ,0) GRID_PAYPRO_RATE_HB
 FROM
 (SELECT  MONTH_ID,PROV_ID,PROV_NAME,AREA_ID,AREA_NAME,KPI_NAME,
 CASE WHEN KPI_NAME = ''综合网格效能评价概况'' THEN SUM(RATIO) ELSE NULL END GRID_AVG_CAP_HB,
 CASE WHEN KPI_NAME = ''网格毛利率'' THEN SUM(RATIO) ELSE NULL END GRID_MAR_RATE_HB, 
 CASE WHEN KPI_NAME = ''智家工程师装维产能'' THEN SUM(RATIO) ELSE NULL END SMART_CAPACITY_HB, 
 CASE WHEN KPI_NAME = ''智家工程师人均百元等效发展量'' THEN SUM(RATIO) ELSE NULL END SMART_AVG_NUM_HB,
 CASE WHEN KPI_NAME = ''宽带资源利用率'' THEN SUM(RATIO) ELSE NULL END BB_USE_RATE_HB,
 CASE WHEN KPI_NAME = ''人力资源投入占收比'' THEN SUM(RATIO) ELSE NULL END GRID_PAYPRO_RATE_HB
 FROM DM_KPI_DATA_GRID_ALL_M
  WHERE MONTH_ID = '''||V_DATE||'''
 GROUP BY MONTH_ID,PROV_ID,PROV_NAME,AREA_ID,AREA_NAME,KPI_NAME) 
 GROUP BY MONTH_ID, PROV_ID,PROV_NAME,AREA_ID,AREA_NAME) T2
 WHERE T1.AREA_ID = T2.AREA_ID
   AND T1.PROV_ID = T2.PROV_ID
 GROUP BY T1.MONTH_ID,T1.PROV_ID,T1.PROV_ORD,T1.PROV_NAME,T1.AREA_ID,T1.AREA_ORD,T1.AREA_NAME';

EXECUTE IMMEDIATE V_SQL;
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

整体存储过程调用

由于数据云资源注册错误,当前无法删除错误注册的资源,所以无法使用BDI直接调度以上六个存储过程,并且基于每期数据量并不大的实际情况,所以可以在行云创建一个存储过程调用以上六个存储过程,在BDI调用以下存储过程即可。

CREATE OR REPLACE PROCEDURE P_BDI_HX_INVOKE_PROCESS(V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR,V_RETINFO OUT VARCHAR) AS
    /*@
    
    ****************************************************************
    *名称 --%@NAME:                      P_BDI_HX_INVOKE_PROCESS
    *功能描述 --%@COMMENT:            整体调用划小加工过程
    *执行周期 --%@PERIOD:               月
    *参数 --%@PARAM:              
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO           过程运行结束成功与否描述
    *创建人 --%@CREATOR:                FX
    *创建时间 --%@CREATED_TIME:        2022.01.18
    *备注 --%@REMARK:
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:               
    *目标表 --%@TO:                 
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_PROCNAME  VARCHAR(40);
    V_START_TIME DOUBLE; --脚本开始执行时间
    V_END_TIME DOUBLE; --脚本结束执行时间
    TIME_OUT DOUBLE;
    V_RETCODE_TEMP VARCHAR(300);
    V_RETINFO_TEMP VARCHAR(300);
    TIME_CONSUMING VARCHAR(300); --总耗费时长
BEGIN

    -- 1.网格人均产能:P_BDI_DM_KPI_DATA_GRID_AVG_CAPACITY_M

    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_AVG_CAPACITY_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_GRID_AVG_CAPACITY_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 2.网格毛利率:P_BDI_DM_KPI_DATA_GRID_MARGIN_RATE_M
    
    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_MARGIN_RATE_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_GRID_MARGIN_RATE_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 3.智家工程师装维产能:P_BDI_DM_KPI_DATA_SMART_CAPACITY_M
    
    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_SMART_CAPACITY_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_SMART_CAPACITY_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 4.智家工程师人均百元等效发展量: P_BDI_DM_KPI_DATA_SMART_AVG_NUM_M

    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_SMART_AVG_NUM_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_SMART_AVG_NUM_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 5.宽带资源利用率:P_BDI_DM_KPI_DATA_KD_USE_RATE_M

    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_KD_USE_RATE_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_KD_USE_RATE_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 6.网格人力资源总投入占収比:P_BDI_DM_KPI_DATA_GRID_PAYPRO_RATE_M
    
    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_PAYPRO_RATE_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_GRID_PAYPRO_RATE_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');

    -- 总览1_综合网格效能评价总体情况:P_BDI_DM_KPI_DATA_GRID_ALL_M
    
    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_ALL_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_GRID_ALL_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');
    
    -- 总览2_数据分省总览:P_BDI_DM_KPI_DATA_GRID_PROV_M
    
    V_START_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    V_PROCNAME  := 'P_BDI_DM_KPI_DATA_GRID_PROV_M'; --过程名
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行开始'||'==============');
    P_BDI_DM_KPI_DATA_GRID_PROV_M(V_MONTH,V_RETCODE_TEMP,V_RETINFO_TEMP);
    V_END_TIME := (SYSDATE - DATE'1970-01-01')*86400; --脚本开始执行时间
    TIME_OUT := V_END_TIME - V_START_TIME;
    TIME_CONSUMING := CAST(TIME_OUT AS VARCHAR(300));
    DBMS_OUTPUT.PUT_LINE('执行结果:'||V_RETCODE_TEMP); 
    DBMS_OUTPUT.PUT_LINE('耗费时长:'||TIME_CONSUMING||' 秒'); 
    DBMS_OUTPUT.PUT_LINE('=============='||V_PROCNAME||'执行结束'||'==============');
    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

导数到应用层

存储过程开发


CREATE OR REPLACE PROCEDURE P_DS_HX_REPORT_FORM(V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR,V_RETINFO OUT VARCHAR) AS
    /*@
    
    ****************************************************************
    *名称 --%@NAME:                      P_DS_HX_REPORT_FORM
    *功能描述 --%@COMMENT:                老划小整体导数到应用层
    *执行周期 --%@PERIOD:               月
    *参数 --%@PARAM:                      V_MONTH 202110
    *参数 --%@PARAM:V_RETCODE              过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO           过程运行结束成功与否描述
    *创建人 --%@CREATOR:                FX
    *创建时间 --%@CREATED_TIME:            2022.01.18
    *备注 --%@REMARK:
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:               
    *目标表 --%@TO:                 
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_PROCNAME  VARCHAR(40);
    V_START_TIME DOUBLE; --脚本开始执行时间
    V_END_TIME DOUBLE; --脚本结束执行时间
    TIME_OUT DOUBLE;
    TIME_CONSUMING VARCHAR(300); --总耗费时长
    V_PART_NAME VARCHAR(50);
    V_CNT NUMBER;
    V_SQL VARCHAR(10000);
BEGIN


    --  1.网格人均产能:    DM_KPI_DATA_GRID_AVG_CAPACITY_M

    DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_AVG_CAPACITY_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;
    -- DBMS_OUTPUT.PUT_LINE(V_PART_NAME);

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 结束 =========');




    --    2.网格毛利率:    DM_KPI_DATA_GRID_MARGIN_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 网格毛利率 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_MARGIN_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格毛利率: 结束 =========');
    
    
    

    -- 3.智家工程师装维产能:    DM_KPI_DATA_SMART_CAPACITY_M

    DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_CAPACITY_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能: 结束 =========');
    
    
    

    -- 4.智家工程师人均百元等效发展量:     DM_KPI_DATA_SMART_AVG_NUM_M

    DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_AVG_NUM_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量: 结束 =========');
    
    
    

    -- 5.宽带资源利用率:    DM_KPI_DATA_KD_USE_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_KD_USE_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率: 结束 =========');
    
    
    


    -- 6.网格人力资源总投入占収比:    DM_KPI_DATA_GRID_PAYPRO_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PAYPRO_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比: 结束 =========');
    
    
    

    -- 总览1_综合网格效能评价总体情况:    DM_KPI_DATA_GRID_ALL_M

    DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_ALL_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_ALL_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_ALL_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况: 结束 =========');


    -- 总览2_数据分省总览:    DM_KPI_DATA_GRID_PROV_M

    DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PROV_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PROV_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_PROV_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览: 结束 =========');

    

    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

手动调用

DM层调用

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_BDI_HX_INVOKE_PROCESS('202201',V1,V2);
END;

应用层调用

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DS_HX_REPORT_FORM('202201',V1,V2);
END;

以下是手动调用存储过程抽疯报错拆解出来的手动执行脚本……

ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_ALL_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PROV_M ADD PARTITION PART_202201 (MONTH_ID='202201');


--插入语句

INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
WHERE MONTH_ID = '202201';

INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
WHERE MONTH_ID = '202201';

INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_ALL_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PROV_M@DM
WHERE MONTH_ID = '202201';

数据云流程开发

flowchart TB
    subgraph 采集流程
        start(数据采集) --> log[日志查询]    
    end
    subgraph 加工流程
        start2(注册资源)-->|扫描存储过程|proc[P_BDI_HX_INVOKE_PROCESS\n P_DS_HX_REPORT_FORM] -->|定时触发|run[加工流程]
    end
    采集流程 --> 加工流程
 

采集流程

采集调度流程

采集流程

备用方案

由于账号权限问题,飞哥的账号后续不能随便登录,然而只有飞哥的账号才有对应的接口机、行云等资源权限,所以若在自动调度之后仍需要手动重新采集部分数据,则手动调用龙哥账号下的流程: F_DS_HX_OLD_MP_GATHER ,可以输入账期进行整体文件重传,或输入账期和省份进行精确重传。

当采集完成后,即可在行云SRC库下手动调用以下脚本,将接口机文件采集到行云。(其中需要将 202103 批量替换成对应账期)

-- 手动录入SRC_M_BCA03014

alter TABLE SRC.SRC_M_BCA03014 add partition P202103 (month_id='202103')

alter TABLE SRC.SRC_M_BCA03014 TRUNCATE partition P202103 

insert into /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03014(
LOCAL_NET,GRID_ID,GRID_NAME,GRID_ACCT_TOTAL,GRID_INCOME_2G,GRID_INCOME_3G,GRID_INCOME_4G,GRID_INCOME_5G,GRID_INCOME_BB,GRID_INCOME_FX,GRID_INCOME_OTHER,GRID_MARKET_COST,CHAN_FEE,SOSIAL_CHAN_FEE,OWN_CHAN_FEE,CUST_SERV_COST,CUST_HOLD_COST,AD_FEE,GRID_SETTLE_PAY,USER_GET_COST,OUT_SERV_FEE,CLOSE_OUT,BUSINESS_OUT,SERV_OUT,BAD_RESERVES,ICT_COST,PORT_SUBSIDY,SALE_LOSS,OTHER_COST,LABOR_COST,GRID_MARGIN_RATE,FULL_WORKERS_NUM,CONTRACT_WORKERS_NUM,DISPATCH_WORKERS_NUM,CLOSE_WORKERS_NUM,GRID_SALE_NUM,GRID_AVG_CAPACITY,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
) 
partition on(month_id='202103') 
'hdfs:/tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/CMBBIDWAL03014A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

select * from SRC.SRC_M_BCA03014;

-- 手动录入SRC_M_BCA03016

alter TABLE SRC.SRC_M_BCA03016 add partition P202103 (month_id='202103')

alter TABLE SRC.SRC_M_BCA03016 TRUNCATE partition P202103 

insert into /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03016(
LOCAL_NET,GRID_ID,GRID_NAME,EQU_DEV_NUM,SMART_ALL_NUM,CONTRACT_NUM,DISPATCHED_NUM,CLOSE_NUM,SMART_OUT_NUM,AGENT_NUM,COOP_NUM,TOTAL_ORDER_NUM,INSTALL_ORDER_NUM,MOVE_ORDER_NUM,REPAIR_ORDER_NUM,SMART_COST_FEE,CUST_ACCESS_COST,NET_HOLD_COST,ACCT_LABOR_COST,CLOSE_OUT_FEE,OTHER_COST,MONTH_ID,PROV_ID
) 
partition on(month_id='202103') 
'hdfs:/tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/CMBBIDWAL03016A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

select * from SRC.SRC_M_BCA03016;

-- 手动录入SRC_M_BCA03017

alter TABLE SRC.SRC_M_BCA03017 add partition P202103 (month_id='202103')

alter TABLE SRC.SRC_M_BCA03017 TRUNCATE partition P202103 

insert into /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03017(
LOCAL_NET,GRID_ID,GRID_NAME,BB_USE_RATE,BB_PORT_NUM,BB_USER_NUM,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
) 
partition on(month_id='202103') 
'hdfs:/tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/CMBBIDWAL03017A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

select * from SRC.SRC_M_BCA03017;

-- 手动录入SRC_M_BCA03018

alter TABLE SRC.SRC_M_BCA03018 add partition P202103 (month_id='202103')

alter TABLE SRC.SRC_M_BCA03018 TRUNCATE partition P202103 

insert into /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03018(
LOCAL_NET,GRID_ID,GRID_NAME,GRID_ACCT_TOTAL,GRID_INCOME_2G,GRID_INCOME_3G,GRID_INCOME_4G,GRID_INCOME_5G,GRID_INCOME_BB,GRID_INCOME_FX,GRID_INCOME_OTHER,GRID_RES_PAY,LABOR_COST,CLOSE_OUT_FEE,SALE_OUT_FEE,GRID_PAYPRO_RATE,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
) 
partition on(month_id='202103') 
'hdfs:/tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/CMBBIDWAL03018A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

select * from SRC.SRC_M_BCA03018;

加工流程

流程名

F_DP_HX_SURVEILLANCE

资源注册

xcloud_dm_10_177_66_109

待注册资源汇总:

P_BDI_HX_INVOKE_PROCESS
P_DS_HX_REPORT_FORM

image-20220118164501243

image-20220118110057890

流程开发

按顺序执行上面注册的存储过程即可。

image-20220221104159807

定时调度

image-20220221103258268

采集流程定时调度

根据需求,每月到时间不管前置是否具备,优先加工一版数据进行展示,所以采集流程设置为每月16号早上8点开始第一次采集,之后连续四天同一时间重新启动采集流程。通过查看采集日志信息,汇总缺失的省份文件,要求省份上传对应文件。

加工流程定时调度

定于每月16号13:50开始加工数据,之后连续四天同一时间进行加工。

自动采集和加工流程只配置到当月的28号,在这之后接口文件若是重新上传,则需要手动重新采集。

附录

接口文件是否具备查询

select
'3014:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3014%'
union all
select
'3016:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3016%'
union all
select
'3017:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3017%'
union all
select
'3018:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3018%'

image-20220117184759013

Last modification:March 25th, 2022 at 04:03 pm
如果觉得我的文章对你有用,请随意赞赏