Contact time rate

No details no CREATEORREPLACEPROCEDUREP_MDJ_1001066_TO_ZW (I _SPANINVARCHAR2, -- useless parameter can be input any value O_SQLOUTVARCHAR2, O_FILEOUTVARCHAR2) * broadband ticket 1001066 external call data upload and Maintenance System * mengdong Region * requirement process: (DocID: 1989216) about customer

No details no create or replace procedure P_MDJ_1001066_TO_ZW (I _SPAN IN VARCHAR2, -- useless parameter can be input any value O_ SQL OUT VARCHAR2, O_FILE OUT VARCHAR2) /* broadband ticket 1001066 external call data upload and Maintenance System * mengdong Region * requirement process: (DocID: 1989216) about customer

<无详细内容> <无>

Create or replace procedure P_MDJ_1001066_TO_ZW (I _SPAN IN VARCHAR2, -- you can enter any value for useless parameters: O_ SQL OUT VARCHAR2, O_FILE OUT VARCHAR2) /* broadband ticket 1001066 external call data upload and Maintenance System * mengdong Region * requirement process: (DocID: 1989216) requirements for customer service system development and delivery of broadband reservation time rate reports */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 serial number, CI. name of the city where ORGANAME is located, B. AREAID cell code, B. FIXMAN maintainer,. CALLERNO,. CALLEDNO called number, case when. isprocessed is null then' does not call 'when. ISPROCESSED = '0' then' outbound call failed 'else' outbound call successful 'end first outbound call successful,. reason for REMARK's first outbound call failure,. FIRSTCONTACTSTARTTIME,. LASTCONTACTSTARTTIME last contact time,. CONTACTDURATION first call duration, case when. firstcontactstarttime is null then 'no' WHEN (. FIRSTCONTACTSTARTTIME-B. ARRIVETIME1) * 24> 1 THEN 'no' else' is 'end whether to contact the user within 1 hour, case when. firstcontactstarttime is null then 'no' WHEN (. FIRSTCONTACTSTARTTIME-B. ARRIVETIME2) * 24> 3 then' no 'else' is 'end whether to contact the user within 3 hours,. CALLCOUNT cumulative number of contacts for this serial number 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 -- a ticket has multiple calls, which are grouped by the flow of the ticket, taking the records of the first call to the user FROM (select cd. SUBSNUMBER, -- the serial number CD. ISPROCESSED, -- whether the call is successful CD. CONTACTSTARTTIME, -- contact time CD. CALLERNO, -- the main call number CD. CALLEDNO, -- called number CD. CONTACTDURATION, -- Call duration CD. REMARK, -- call remarks (cause of failure) 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, -- table A, obtain call information from the Call User table (select serialno, -- Ticket serial number case when TO_CHAR (CREATIONTIME, 'hh24 ') <9 then trunc (CREATIONTIME) + 9/24 -- the next work order before, the next time is counted as WHEN TO_CHAR (CREATIONTIME, 'hh24')> = 18 then trunc (CREATIONTIME + 1) + 9/24 -- for a ticket issued after, the next time is counted as WHEN TO_CHAR (CREATIONTIME, 'hh24')> 17 AND TO_CHAR (CREATIONTIME, 'hh24 ') <18 then trunc (CREATIONTIME + 1) + 9/24-(CREATIONTIME-TRUNC (CREATIONTIME)-17/24) -- A ticket between and, the next dispatch time is on the next day minus the time when the ticket was run between and. else creationtime -- the end as ARRIVETIME1 is the time range recorded by the system, -- 1-hour contact time-out time of the ticket (process the system record's delivery time as normal start time) 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, -- three-hour dispatch time for the timely contact ticket (the system records the delivery time as normal start time) CREATIONTIME, -- dispatch time for the ticket SERVICECITY, -- city id fixman, -- installation personnel AREAID -- cell code 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 '200' and pp. ARCHIVEDATE> = TRUNC (SYSDATE)-1 and pp. ARCHIVEDATE <TRUNC (SYSDATE) and pp. SERIALNO = PW. serialno and pw. NODENAME = 'assembly dimension process' and sr. SERVICEID = PP. srid group by pp. SERIALNO) -- obtains information about the assembly dimension system from the ticket record table.) B, -- table B, obtains the ticket information ICDPUB from the ticket table. t_UCP_CITYINFO ci where B. SERIALNO =. SUBSNUMBER (+) and ci. ORGAID = B. SERVICECITY; COMMIT; O_RET: = 2; -- get the file name 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;