- CREATE OR REPLACE PROCEDURE P_MDJ_1001066_TO_ZW(I_SPAN IN VARCHAR2, --无用参数可输入任意值
- O_SQL OUT VARCHAR2,
- O_FILE OUT VARCHAR2)
- /* 宽带工单1001066外呼数据上传装维系统
- * 2014-02-20
- * 蒙冬珏
- * 需求流程:(DocID:1989216) 关于客服系统开发并传送宽带预约及时率报表的需求单
- */
- AS
- O_RET NUMBER;
- PROCEDURE_NAME VARCHAR2(200);
- V_SQLECODE VARCHAR2(50);
- V_SQLERRM VARCHAR2(500);
- V_FILENAME VARCHAR2(100);
- V_SQL VARCHAR2(4000);
- BEGIN
- PROCEDURE_NAME:=GETEXEC_PROCEDURE_NAME();
- O_RET := 1;
- execute immediate 'truncate table ICDPUB.T_MDJ_1001066_TO_ZW';
- INSERT INTO ICDPUB.T_MDJ_1001066_TO_ZW
- SELECT B.SERIALNO 流水号,
- CI.ORGANAME 地市名称,
- B.AREAID 小区编码,
- B.FIXMAN 维护员,
- A.CALLERNO 主叫号码,
- A.CALLEDNO 被叫号码,
- CASE WHEN A.ISPROCESSED IS NULL THEN '未呼出'
- WHEN A.ISPROCESSED = '0' THEN '呼出失败'
- ELSE '呼出成功' END 首次呼出是否成功,
- A.REMARK 首次呼出失败原因,
- A.FIRSTCONTACTSTARTTIME 首次联系时间,
- A.LASTCONTACTSTARTTIME 最后一次联系时间,
- A.CONTACTDURATION 首次通话时长,
- CASE WHEN A.FIRSTCONTACTSTARTTIME IS NULL THEN '否'
- WHEN (A.FIRSTCONTACTSTARTTIME - B.ARRIVETIME1) * 24 > 1 THEN '否'
- ELSE '是' END 是否1小时内联系用户,
- CASE WHEN A.FIRSTCONTACTSTARTTIME IS NULL THEN '否'
- WHEN (A.FIRSTCONTACTSTARTTIME - B.ARRIVETIME2) * 24 > 3 THEN '否'
- ELSE '是' END 是否3小时内联系用户,
- A.CALLCOUNT 本流水号累计联系用户次数
- FROM (SELECT T.SUBSNUMBER,MAX(CASE WHEN T.RN=1 THEN T.ISPROCESSED END) ISPROCESSED,MIN(T.CONTACTSTARTTIME) FIRSTCONTACTSTARTTIME,
- MAX(T.CONTACTSTARTTIME) LASTCONTACTSTARTTIME,MAX(CASE WHEN RN=1 THEN T.CALLERNO END) CALLERNO,
- MAX(CASE WHEN RN=1 THEN T.CALLEDNO END) CALLEDNO,MAX(CASE WHEN RN=1 THEN T.CONTACTDURATION END) CONTACTDURATION,
- MAX(CASE WHEN RN=1 THEN T.REMARK END) REMARK,COUNT(T.SUBSNUMBER) CALLCOUNT --一张工单有多次呼叫,按工单流水分组,取第一次呼叫用户的记录
- FROM
- (SELECT CD.SUBSNUMBER, --工单流水号
- CD.ISPROCESSED, --是否呼叫成功
- CD.CONTACTSTARTTIME, --联系时间
- CD.CALLERNO, --主叫号码
- CD.CALLEDNO, --被叫号码
- CD.CONTACTDURATION, --呼叫时长
- CD.REMARK, --呼叫备注(失败原因)
- ROW_NUMBER() OVER(PARTITION BY CD.SUBSNUMBER ORDER BY CD.CONTACTSTARTTIME) RN
- FROM ICDPUB.T_CCT_CONTACTDETAIL CD
- WHERE CD.CONTACTMODEID = '19'
- AND CD.CONTACTSTARTTIME >= TRUNC(SYSDATE)-11
- AND CD.CONTACTSTARTTIME < TRUNC(SYSDATE)
- ) T GROUP BY T.SUBSNUMBER
- ) A, --表A,从呼叫用户的表里获取呼叫信息
- (SELECT SERIALNO, --工单流水号
- CASE
- WHEN TO_CHAR(CREATIONTIME, 'hh24') < 9 THEN
- TRUNC(CREATIONTIME) + 9 / 24 --9点前下派工单,下派时间算作9点
- WHEN TO_CHAR(CREATIONTIME, 'hh24') >= 18 THEN
- TRUNC(CREATIONTIME + 1) + 9 / 24 --18点后下派的工单,下派时间算作第二天9点
- WHEN TO_CHAR(CREATIONTIME,'HH24') >17 AND TO_CHAR(CREATIONTIME,'HH24') <18 THEN
- TRUNC(CREATIONTIME + 1) + 9 / 24 - (CREATIONTIME - TRUNC(CREATIONTIME)-17/24) --17至18点间的工单,下派时间为第二天9点减去工单在17至18点之间已运行的时间
- ELSE
- CREATIONTIME --非以上时间段则为系统记录的工单下派时间
- END AS ARRIVETIME1, --1小时联系及时率的工单下派时间(将系统记录的下派时间处理为正常上班时间)
- CASE
- WHEN TO_CHAR(CREATIONTIME, 'hh24') < 8.5 THEN
- TRUNC(CREATIONTIME) + 8.5 / 24
- WHEN TO_CHAR(CREATIONTIME, 'hh24') >= 18 THEN
- TRUNC(CREATIONTIME + 1) + 8.5 / 24
- WHEN TO_CHAR(CREATIONTIME, 'hh24') >15 AND TO_CHAR(CREATIONTIME, 'hh24') <18 THEN
- TRUNC(CREATIONTIME + 1) + 8.5 / 24 - (CREATIONTIME - TRUNC(CREATIONTIME)-15/24)
- ELSE
- CREATIONTIME
- END AS ARRIVETIME2, --3小时联系及时率的工单下派时间(将系统记录的下派时间处理为正常上班时间)
- CREATIONTIME, --工单下派时间
- SERVICECITY, --地市ID
- FIXMAN, --装维人员
- AREAID --小区编码
- FROM (SELECT PP.SERIALNO,
- MIN(PW.CREATIONTIME) CREATIONTIME,
- MIN(PP.SERVICECITY) SERVICECITY,
- MIN(PP.PUBDYNACHAR108) FIXMAN,
- MIN(SR.SERVICEINFOCHAR165) AREAID
- FROM ICDPUB.T_PBH_PROBLEMPROCESS PP,
- ICDPUB.T_PBH_PROBLEMWORKITEM PW,
- ICDPUB.T_SR_SERVICEREQUEST SR
- WHERE PP.SRTYPEID LIKE '004001%'
- AND PP.ARCHIVEDATE >= TRUNC(SYSDATE)-1
- AND PP.ARCHIVEDATE < TRUNC(SYSDATE)
- AND PP.SERIALNO = PW.SERIALNO
- AND PW.NODENAME = '装维处理'
- AND SR.SERVICEID = PP.SRID
- GROUP BY PP.SERIALNO) --从工单记录表里获取装维系统环节的信息
- ) B, --表B,从工单表里获取工单信息
- ICDPUB.T_UCP_CITYINFO CI
- WHERE B.SERIALNO = A.SUBSNUMBER(+)
- AND CI.ORGAID = B.SERVICECITY;
- COMMIT;
- O_RET := 2;
- --获取文件名称
- V_FILENAME := 'order_' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
- V_SQL:='SELECT SERIALNO ,'||
- ' ORGANAME ,'||
- ' AREAID ,'||
- ' FIXMAN ,'||
- ' CALLERNO ,'||
- ' CALLEDNO ,'||
- ' ISPROCESSED ,'||
- ' REMARK ,'||
- ' FIRSTCONTACTSTARTTIME,'||
- ' LASTCONTACTSTARTTIME ,'||
- ' CONTACTDURATION ,'||
- ' CALLIN1HOUR ,'||
- ' CALLIN3HOURS ,'||
- ' CALLCOUNT FROM ICDPUB.T_MDJ_1001066_TO_ZW';
- O_RET := 3;
- O_FILE := V_FILENAME;
- O_SQL := V_SQL;
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- O_RET := 999;
- V_SQLECODE := SQLCODE;
- V_SQLERRM := SUBSTR(SQLERRM, 1, 300);
- INSERT INTO ICDPUB.T_MDJ_ERRORLOG
- VALUES
- (SYSDATE,
- '',
- O_RET,
- V_SQLECODE,
- V_SQLERRM,
- '[FAIL]['||PROCEDURE_NAME||']');
- COMMIT;
- END P_MDJ_1001066_TO_ZW;
- --该片段来自于http://www.codesnippet.cn/detail/230620149849.html
来源: http://www.codesnippet.cn/detail/230620149849.html