任务需求
每月按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
流程开发
按顺序执行上面注册的存储过程即可。
定时调度
采集流程定时调度
根据需求，每月到时间不管前置是否具备，优先加工一版数据进行展示，所以采集流程设置为每月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%'
