create or replace procedure biller533.shzc_202102_gagx_szhm_gc (p_qx in varchar2,p_userid varchar2,p_lx varchar2, p_id varchar2, p_subsid varchar2,p_su_lx varchar2,p_cz_lx varchar2,p_xz_gh varchar2,p_note varchar2, p_cursor in out Results.ref_cursor_type) as v_qx VARCHAR2(10); v_sql VARCHAR2(20000); SQL_STRING VARCHAR2(20000); v_hd VARCHAR2(100); v_lx VARCHAR2(100); v_rq VARCHAR2(10); v_id VARCHAR2(15); v_subsid VARCHAR2(15); v_qx_id VARCHAR2(10); v_orgid VARCHAR2(50); v_name VARCHAR2(50); v_sjhm VARCHAR2(50); v_note VARCHAR2(500); V_TAB1 varchar2(900); begin v_id :=nvl(p_id,'0'); ---申请id null 处理 v_subsid :=nvl(p_subsid,'9999'); v_lx :=trim(p_lx); v_note :=trim(p_note); v_rq :=to_char(sysdate,'yyyymmdd'); ---经分工号获取区县 select max(qx) into v_qx_id from (select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(ORGID) into v_orgid from (select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(username) into v_name from (select a.userid,a.username from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,opername from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(mobilephone) into v_sjhm from (select a.userid,a.mobilephone from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,CONTACTPHONE from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sj_tmp'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sj_tmp as select distinct a.* from (select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end, a.证件关联) 关联类型, nvl(case when a.subsid=b.subsid then 1 end, 2) 关联类型id, a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''否'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.hd_iden_no=a.hd_iden_no union all select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end,a.渠道关联) , nvl(case when a.subsid=b.subsid then 1 end, 3) 关联类型id,a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''是'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.registerorgida=a.registerorgid union all select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end,a.证件关联), nvl(case when a.subsid=b.subsid then 1 end, 2) 关联类型id,a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''是'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.hd_iden_no=a.hd_iden_no ) a where nvl(a.关联类型,''无'')<>''无'' ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sj_id'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sj_id as select a.id ida,a.name namea,a.类型 类型a,a.开户类型 开户类型a,a.相差90天以内 相差90天以内a, to_char(count(distinct a.subsid)) subsida, to_char(count(distinct case when a.关联类型=''当事号码'' then a.subsid end)) 当事号码, to_char(count(distinct case when a.关联类型=''证件关联'' then a.subsid end)) 证件关联, to_char(count(distinct case when a.关联类型=''渠道关联'' then a.subsid end)) 渠道关联 from SHZC.jbsj_jfbbdr_gagx_szhm_sj_tmp a group by a.id,a.name,a.类型,a.开户类型,a.相差90天以内' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_dlqk'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_dlqk as select '''||p_userid||''' p_userid,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码,'''||v_qx_id||''' 归属区县,'''||v_orgid||''' 组织机构,sysdate in_time, '''||v_lx||''' 报表类型,'''||p_id||''' 选择id,'''||p_cz_lx||''' 处理类型 ,'''||p_xz_gh||''' 选择工号, '''||v_note||''' 备注信息 from dual ' ; execute immediate (SQL_STRING); SQL_STRING:='insert into SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_bd select a.* from SHZC.jbsj_jfbbdr_gagx_szhm_dlqk a ' ; execute immediate (SQL_STRING); commit; ----权限的控制 开始 select max(qx) into v_qx from zhyw.zibo_jyfx_staff where trim(userid)=p_userid; select name1 into v_qx from zhyw.rpt_county where county_id=v_qx; --1 全部涉案数据 --2 公安部涉案数据 --3 工信部举报数据 --4 工单涉及号码 ---信息安全考核指标和清单报表 if p_cz_lx='1' then ---查看 if v_lx='1' then ---如果类型全部 if v_qx='全市' then if p_qx='全市' then v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) order by a.ID desc ) a ' ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' order by a.ID desc ) a ' ; end if ; elsif v_lx='2' then if v_qx='全市' then if p_qx='全市' then v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and 类型=''公安部涉案'' order by a.ID desc ) a ' ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' and 类型=''公安部涉案'' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' and 类型=''公安部涉案'' order by a.ID desc ) a ' ; end if ; elsif v_lx='3' then if v_qx='全市' then if p_qx='全市' then v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and 类型=''工信部举报'' order by a.ID desc ) a ' ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' and 类型=''工信部举报'' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' and 类型=''工信部举报'' order by a.ID desc ) a ' ; end if ; elsif v_lx='4' then v_sql:='select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''案件类别'',''开户时间'', ''开户类型'',''发案时间'',''证件号'',''相差90天以内'',''用户编码'',''号码'', ''开户时间'',''渠道名称'',''产品名称'',''关联类型'',''状态'',''状态时间'', ''漫游地'',''风险漫游地'',''通话号码数'',''通话数'',''通话分钟数'',''离散度'',''诈骗'',''高频'',''骚扰'', ''最终得分'',''核查人'', ''核查时间'', ''关联类型F'', ''是否接通'', ''未接通原因'', ''是否本人'', ''核查使用情况'', ''渠道经理问询情况'', ''营业员问询情况'', ''号码处置意见'' from dual union all select a.* from (select to_char(ID),NAME,涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,案件类别,to_char(STARTDATE,''yyyy-mm-dd''), 开户类型,to_char(发案时间A,''yyyy-mm-dd''),substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4), 相差90天以内,SUBSID,SERVNUMBER,to_char(CREATEDATE,''yyyy-mm-dd''), ORGNAME,PRODNAME,关联类型,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), 漫游地,风险漫游地,to_char(通话号码数),to_char(通话数),to_char(通话分钟数),to_char(离散度), to_char(诈骗),to_char(高频),to_char(骚扰),to_char(最终得分), y.核查人 ,y.核查时间 ,y.关联类型F ,y.是否接通 ,y.未接通原因 ,y.是否本人 , y.核查使用情况 ,y.渠道经理问询情况 ,y.营业员问询情况 ,y.号码处置意见 from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJ_TMP a, SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y where a.id='''||v_id||''' and a.ID=y.idf(+) and a.SUBSID=y.选择用户编码f(+) order by 关联类型id,最终得分,CREATEDATE desc ) a' ; end if ; elsif p_cz_lx='2' then ----工号自处理 fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sj_gh'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh as select a.*,b.name,d.orgname from (select a.userid,a.username,a.qx,NVL(case when a.qx=''0'' then ''SD.LC'' END,''SD.LC.0''||A.QX ) ORGID, a.mobilephone,''经分'' 类型 from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,opername,NVL(SUBSTR(ORGID,8,1),''0''),ORGID,CONTACTPHONE,''工号'' 类型 from tbcs.operator@Bcv WHERE ORGID like ''SD.LC%'' and STATUS=1 and length(opername)<5 and OPERID like ''c%'' ) a, zhyw.rpt_county b, tbcs.organization@bcv d where a.qx=b.county_id and a.ORGID=d.orgid(+)' ; execute immediate (SQL_STRING); if v_id<>'0' and v_id<>'9999' then SQL_STRING:='insert into SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_bfp select a.*,substr(a.选择工号,1,2) 类型,substr(a.选择工号,4,length(a.选择工号)) 选择工号id from SHZC.jbsj_jfbbdr_gagx_szhm_dlqk a where a.选择id not in (''0'',''9999'') ' ; execute immediate (SQL_STRING); commit; fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_dlqk_cfp'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp as select a.选择id,a.申请人姓名,a.in_time in_timeb,a.类型 类型b,a.选择工号id from (select a.*,row_number() over (partition by a.选择id order by a.in_time desc ) 排名 from SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_bfp a ) a where a.排名=1 ' ; execute immediate (SQL_STRING); end if ; if v_lx='1' then ---如果类型全部 if v_qx='全市' then if p_qx='全市' then v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) order by a.ID desc ) a ' ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' order by a.ID desc ) a ' ; end if ; elsif v_lx='2' then if v_qx='全市' then if p_qx='全市' then v_sql:='select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and 类型=''公安部涉案'' order by a.ID desc ) a ' ; else v_sql:='select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' and 类型=''公安部涉案'' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' and 类型=''公安部涉案'' order by a.ID desc ) a ' ; end if ; elsif v_lx='3' then if v_qx='全市' then if p_qx='全市' then v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and 类型=''工信部举报'' order by a.ID desc ) a ' ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||p_qx||''' and 类型=''工信部举报'' order by a.ID desc ) a ' ; end if ; else v_sql:=' select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''通话地'', ''客户名称'',''证件号码'',''案件类别'',''用户编码'',''手机号码'',''开户时间'',''使用时间'', ''产品'',''状态'',''状态时间'', ''渠道编码'',''开户类型'',''发案时间A'',''HD_IDEN_NO'',''相差90天以内'' , ''涉及用户数'',''当事号码'',''证件关联'',''渠道关联'', ''分派人'',''分派时间'',''类型'',''选择工号id'', ''处理人姓名'',''处理人手机号'',''处理人组织机构'',''已反馈数量'' from dual union all select a.* from (select to_char(ID),nvl(b.name,''全市''),涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,通话地,substr(客户名称,1,1)||''**'', substr(证件号码,1,10)||''****''||substr(证件号码,15,4),案件类别,to_char(SUBSID),SERVNUMBER, to_char(CREATEDATE,''yyyy-mm-dd''),to_char(STARTDATE,''yyyy-mm-dd''), PRODNAME,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), ORGNAME,开户类型,to_char(发案时间A,''yyyy-mm-dd''), substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4),相差90天以内, d.subsida,d.当事号码,d.证件关联,d.渠道关联, e.申请人姓名,to_char(e.in_timeb ,''yyyy/mm/dd/hh24/mi/ss''),e.类型b,e.选择工号id, t.usernamet,t.mobilephonet,t.orgnamet,to_char(nvl(y.选择用户编码f,0)) 选择用户编码f from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJH_BD a, zhyw.rpt_county b, SHZC.jbsj_jfbbdr_gagx_szhm_sj_id d, SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_cfp e, (select t.类型 类型t,t.userid useridt,t.orgname orgnamet,t.username usernamet, t.mobilephone mobilephonet from SHZC.jbsj_jfbbdr_gagx_szhm_sj_gh t ) t, (select y.idf,count(distinct y.选择用户编码f) 选择用户编码f from SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y group by y.idf ) y where substr(a.registerorgida,8,1)=b.county_id(+) and a.id=y.idf(+) and a.id=d.ida(+) and e.类型b=t.类型t(+) and e.选择工号id=t.useridt(+) and a.id=e.选择id(+) and b.name='''||v_qx||''' and 类型=''工信部举报'' order by a.ID desc ) a ' ; end if ; elsif v_lx='4' then v_sql:='select ''ID'',''区县'',''涉案号码'',''邮件转派时间'',''受害人电话'',''类型'',''案件类别'',''开户时间'', ''开户类型'',''发案时间'',''证件号'',''相差90天以内'',''用户编码'',''号码'', ''开户时间'',''渠道名称'',''产品名称'',''关联类型'',''状态'',''状态时间'', ''漫游地'',''风险漫游地'',''通话号码数'',''通话数'',''通话分钟数'',''离散度'',''诈骗'',''高频'',''骚扰'', ''最终得分'',''核查人'', ''核查时间'', ''关联类型F'', ''是否接通'', ''未接通原因'', ''是否本人'', ''核查使用情况'', ''渠道经理问询情况'', ''营业员问询情况'', ''号码处置意见'' from dual union all select a.* from (select to_char(ID),NAME,涉案号码,to_char(邮件转派时间,''yyyy-mm-dd''), 受害人电话,类型,案件类别,to_char(STARTDATE,''yyyy-mm-dd''), 开户类型,to_char(发案时间A,''yyyy-mm-dd''),substr(HD_IDEN_NO,1,10)||''****''||substr(HD_IDEN_NO,15,4), 相差90天以内,SUBSID,SERVNUMBER,to_char(CREATEDATE,''yyyy-mm-dd''), ORGNAME,PRODNAME,关联类型,DICTNAME,to_char(STATUSDATE,''yyyy-mm-dd''), 漫游地,风险漫游地,to_char(通话号码数),to_char(通话数),to_char(通话分钟数),to_char(离散度), to_char(诈骗),to_char(高频),to_char(骚扰),to_char(最终得分), y.核查人 ,y.核查时间 ,y.关联类型F ,y.是否接通 ,y.未接通原因 ,y.是否本人 , y.核查使用情况 ,y.渠道经理问询情况 ,y.营业员问询情况 ,y.号码处置意见 from shzc.JBSJ_JFBBDR_GAGX_SZHM_SJ_TMP a, SHZC.jbsj_jfbbdr_gagx_qxhc_fkqk_fyj y where a.id='''||v_id||''' and a.ID=y.idf(+) and a.SUBSID=y.选择用户编码f(+) order by 关联类型id,最终得分,CREATEDATE desc ) a' ; end if ; elsif p_cz_lx='3' then ----重新生成数据 ------生产数据过程 SQL_STRING:='update shzc.jbsj_jfbbdr_gagx_szhm a set a.字段1=trim(a.字段1), a.字段2=trim(a.字段2),a.字段3=trim(a.字段3),a.字段4=trim(a.字段4),a.字段5=trim(a.字段5), a.字段6=trim(a.字段6),a.字段7=trim(a.字段7),a.字段8=trim(a.字段8),a.字段9=trim(a.字段9) ' ; execute immediate (SQL_STRING); commit; ---公安转派涉案号码 fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_gazp'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_gazp as select replace(a.字段1,'';@'','''') 邮件转派时间, a.字段2 涉案号码, a.字段3 案件类别, a.字段4 发案时间, a.字段5 受害人电话, a.字段6 通话地, a.字段7 注册单位, a.字段8 客户名称, a.字段9 证件号码 from (select a.*,row_number() over (partition by a.字段1,a.字段2 order by a.in_time ) 排名 from (select distinct replace(a.字段1,'';@'','''') 字段1,a.字段2,a.字段3,a.字段4,a.字段5,a.字段6,a.字段7,a.字段8,a.字段9,a.usid,a.in_time from shzc.jbsj_jfbbdr_gagx_szhm a ) a where length(a.字段2) in ( 11,13) and a.字段2 not in (''通话地点'',''涉案号码'') ) a where 排名=1 ' ; execute immediate (SQL_STRING); ---工信部受理举报明细 fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_gxzp'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_gxzp as select replace(a.字段1,'';@'','''') 日期, a.字段2 通话地点, a.字段3 举报号码, a.字段4 被举报号码, a.字段5 举报时间, a.字段6 举报内容, a.字段7 注册单位, a.字段8 客户名称, a.字段9 证件号码 from (select a.*,row_number() over (partition by a.字段1,a.字段3,a.字段4,a.字段5 order by a.in_time ) 排名 from (select distinct replace(a.字段1,'';@'','''') 字段1,a.字段2,a.字段3,a.字段4,a.字段5,a.字段6,a.字段7,a.字段8,a.字段9,a.usid,a.in_time from shzc.jbsj_jfbbdr_gagx_szhm a ) a where length(a.字段2) not in ( 11,13) and a.字段2 not in (''通话地点'',''涉案号码'') ) a where 排名=1 ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjh'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjh as select replace(b.涉案号码,''+86'','''') 涉案号码,to_date(b.邮件转派时间,''yyyy/mm/dd'') 邮件转派时间,b.受害人电话, ''公安部涉案'' 类型,b.发案时间,b.通话地,b.客户名称,b.证件号码,b.案件类别 from shzc.jbsj_jfbbdr_gagx_szhm_gazp b union all select replace(a.被举报号码,''+86'','''') ,to_date(a.日期,''yyyy/mm/dd''), a.举报号码,''工信部举报'' ,a.举报时间,a.通话地点,a.客户名称,a.证件号码,a.举报内容 from shzc.jbsj_jfbbdr_gagx_szhm_gxzp a ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjha'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjha as select distinct nvl(b.省名,d.省名)省名, nvl(b.地市,d.地市)地市, nvl(b.运营商,d.运营商) 运营商,a.* from shzc.jbsj_jfbbdr_gagx_szhm_sjh a, shzc.qghdb_hdq8w b, shzc.qghdb_hdq7w d where substr(a.涉案号码,1,8)=b.号段(+) and substr(a.涉案号码,1,7)=d.号段(+) ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhb'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhb as select a.* from (select a.省名,a.地市,a.运营商,a.涉案号码,a.邮件转派时间,a.受害人电话,a.类型,b.subsid,b.servnumber,b.createdate,b.prodid,b.registerorgid, b.status,b.statusdate,b.createdate startdate,b.registerorgid registerorgida, row_number() over (partition by a.涉案号码,a.邮件转派时间,a.受害人电话,a.类型 order by nvl(b.createdate,sysdate-9999) desc ) 排名 from (select * from shzc.jbsj_jfbbdr_gagx_szhm_sjha a where nvl(a.地市,''无'')=''淄博'') a, (select * from zhyw.subscriber b where b.status not in (''US26'',''US28'') ) b where a.涉案号码=b.servnumber(+) and a.邮件转派时间>b.createdate(+) ) a where 排名=1 ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhc'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhc as select a.* from tbcs.subs_serviceattr@bcv a, (select distinct b.涉案号码 from shzc.jbsj_jfbbdr_gagx_szhm_sjhb b where b.subsid is null ) b where a.attrvalue=b.涉案号码' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhd'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhd as select * from (select b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,a.subsid,a.startdate,a.enddate, row_number() over (partition by a.attrvalue,b.邮件转派时间,b.受害人电话,b.类型 order by nvl(a.startdate,sysdate-9999) desc ) 排名 from shzc.jbsj_jfbbdr_gagx_szhm_sjhc a, (select distinct b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型 from shzc.jbsj_jfbbdr_gagx_szhm_sjhb b where b.subsid is null ) b where a.attrvalue=b.涉案号码(+) and a.startdate<b.邮件转派时间 ) a where a.排名=1' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhe'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhe as select b.省名,b.地市,b.运营商,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型, a.subsid,c.servnumber,c.createdate,c.prodid,c.registerorgid,c.status,c.statusdate,a.startdate,1 排名 from shzc.jbsj_jfbbdr_gagx_szhm_sjhb b , shzc.jbsj_jfbbdr_gagx_szhm_sjhd a, zhyw.subscriber c where b.subsid is null and b.涉案号码=a.涉案号码(+) and b.邮件转派时间=a.邮件转派时间(+) and b.受害人电话=a.受害人电话(+) and b.类型=a.类型(+) and a.subsid=c.subsid(+)' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhea'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhea as select b.itemname,a.recdate sj,c.dictname fs, nvl((select distinct orgname from tbcs.organization@bcv where orgid=a.recorgid),''不详'') yyt,a.* from zhyw.shc_reception a, tbcs.reception_define@bcv b, (select * from tbcs.dict_item@bcv where groupid=''bsac'') c, shzc.jbsj_jfbbdr_gagx_szhm_sjhe d where a.region=533 and a.recdefid=b.itemid and a.contacttype =c.dictid(+) and a.entityid = d.subsid and to_char(a.recdate,''yyyymmdd'')=to_char(d.startdate,''yyyymmdd'')' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhf'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhf as select a.省名,地市,运营商,涉案号码,邮件转派时间,受害人电话,类型,SUBSID,SERVNUMBER, CREATEDATE,PRODID,REGISTERORGID,STATUS,STATUSDATE,STARTDATE,REGISTERORGIDA, ''普通开户'' 开户类型 from shzc.jbsj_jfbbdr_gagx_szhm_sjhb a where a.subsid is not null union all select b.省名,b.地市,b.运营商,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.SUBSID,b.SERVNUMBER, b.CREATEDATE,b.PRODID,b.REGISTERORGID,b.STATUS,b.STATUSDATE,b.STARTDATE,a.RECORGID,''和多号'' from shzc.jbsj_jfbbdr_gagx_szhm_sjhe b, shzc.jbsj_jfbbdr_gagx_szhm_sjhea a where b.subsid=a.entityid(+) and to_char(a.recdate,''yyyymmdd'')=to_char(b.startdate,''yyyymmdd'')' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg as select a.*,b.发案时间,to_date(nvl(case when length(b.发案时间)>19 then substr(b.发案时间,21,19) end , substr(b.发案时间,1,19)) ,''yyyy-mm-dd hh24:mi:ss'') 发案时间a, to_date(substr(b.发案时间,1,19),''yyyy-mm-dd hh24:mi:ss'') 发案时间b, c.hd_iden_no, nvl(case when to_date(nvl(case when length(b.发案时间)>19 then substr(b.发案时间,21,19) end , substr(b.发案时间,1,19)) ,''yyyy-mm-dd hh24:mi:ss'') -a.startdate <=90 then ''是'' end,''否'') 相差90天以内, b.通话地,b.客户名称,b.证件号码,b.案件类别 from shzc.jbsj_jfbbdr_gagx_szhm_sjhf a, shzc.jbsj_jfbbdr_gagx_szhm_sjha b, shzc.shzc_age_jqb c where a.涉案号码=b.涉案号码(+) and a.邮件转派时间=b.邮件转派时间(+) and a.受害人电话=b.受害人电话(+) and a.类型=b.类型(+) and a.subsid=c.subsid(+)' ; execute immediate (SQL_STRING); SQL_STRING:='insert into shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd(省名,地市,涉案号码,邮件转派时间,受害人电话, 类型,通话地,客户名称,证件号码,案件类别,SUBSID,SERVNUMBER,CREATEDATE,STARTDATE,PRODID,PRODNAME, STATUS,DICTNAME,STATUSDATE,REGISTERORGIDA,ORGNAME,开户类型,发案时间,发案时间A,HD_IDEN_NO,相差90天以内) select a.省名,a.地市,a.涉案号码,a.邮件转派时间,a.受害人电话,a.类型,a.通话地,a.客户名称,a.证件号码,a.案件类别, a.subsid,a.servnumber,a.createdate,a.startdate,a.prodid,pp.prodname,a.status,zt.dictname,a.statusdate, a.registerorgida,q.orgname,a.开户类型,a.发案时间,a.发案时间a,a.hd_iden_no,a.相差90天以内 from shzc.jbsj_jfbbdr_gagx_szhm_sjhg a , (select * from tbcs.dict_item@bcv where groupid=''US'') zt, tbcs.organization@bcv q, tbcs.product@bcv pp where a.status=zt.dictid(+) and a.registerorgida=q.orgid(+) and a.prodid=pp.prodid(+) and not exists (select 1 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd t where a.涉案号码=t.涉案号码 and a.邮件转派时间=t.邮件转派时间 and a.受害人电话=t.受害人电话 and a.类型=t.类型) order by a.邮件转派时间' ; execute immediate (SQL_STRING); commit; fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhga'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhga as select d.hd_iden_no,d.subsid,d.servnumber,b.createdate,b.registerorgid,b.status,b.statusdate,b.nettype,b.prodid from shzc.shzc_age_jqb d, (select distinct a.hd_iden_no from shzc.jbsj_jfbbdr_gagx_szhm_sjhg a ) a, zhyw.subscriber b where d.hd_iden_no=a.hd_iden_no and d.subsid=b.subsid(+)' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhgb'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhgb as select distinct a.registerorgida,b.subsid,b.servnumber,b.createdate,b.registerorgid,b.status,b.statusdate,b.nettype,b.prodid from zhyw.subscriber b, (select distinct a.registerorgida,to_date(to_char(a.startdate,''yyyymmdd''),''yyyymmdd'') startdate from shzc.jbsj_jfbbdr_gagx_szhm_sjhg a where a.相差90天以内=''是'' and a.开户类型=''普通开户'' and substr(a.registerorgida,8,1) in (''3'',''4'',''5'',''6'',''7'',''8'',''9'',''a'',''b'',''c'',''d'') ) a where b.registerorgid=a.registerorgida and b.createdate>=a.startdate-30 and b.createdate<=a.startdate+1' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhgc'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhgc as select distinct a.* from (select a.subsid,a.servnumber,a.createdate,a.registerorgid,a.status,a.statusdate,a.nettype,a.prodid from shzc.jbsj_jfbbdr_gagx_szhm_sjhga a union all select to_char(a.subsid),a.servnumber,a.createdate,a.registerorgid,a.status,a.statusdate,a.nettype,a.prodid from shzc.jbsj_jfbbdr_gagx_szhm_sjhgb a ) a' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhgd'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhgd as select distinct a.*,e.hd_iden_no,nvl(case when b.hd_iden_no is not null then ''证件关联'' end,''无'') 证件关联, nvl(case when d.registerorgida is not null then ''渠道关联'' end,''无'') 渠道关联 from shzc.jbsj_jfbbdr_gagx_szhm_sjhgc a, shzc.jbsj_jfbbdr_gagx_szhm_sjhga b, shzc.jbsj_jfbbdr_gagx_szhm_sjhgb d, shzc.shzc_age_jqb e where a.subsid=b.subsid(+) and a.subsid=d.subsid(+) and a.subsid=e.subsid(+) and a.nettype =''GSM'' and not exists (select 1 from zhyw.DEL_PROD t where t.prodid=a.prodid )' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg3a'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg3a as select a.user_id,a.roamcity_id,count(distinct a.call_tm) call_tm,sum(a.cs) cs,sum(a.call_duration_m) call_duration_m from ZHYW.ZWZX_MHH_CALL_CELL_HD a, shzc.jbsj_jfbbdr_gagx_szhm_sjhgd b where a.user_id=b.subsid group by a.user_id,a.roamcity_id' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg3b'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg3b as select a.user_id, listagg (a.PROVINCENAME||''_''||a.AREAname,'' ,'') within group (order by user_id) 漫游地, listagg (A.风险漫游地,'' ,'') within group (order by user_id) 风险漫游地 from (select B.PROVINCENAME,b.AREAname,t.areaname 风险漫游地,a.*, row_number() over (partition by a.user_id order by a.call_tm desc , a.call_duration_m desc ) 排名 from shzc.jbsj_jfbbdr_gagx_szhm_sjhg3a a , (select * from common.SA_DB_NATAREAINFO@bcv b WHERE AREATYPE=1 ) B, (select distinct t.areaname from shzc.khhmd_zhapian_my t ) t where a.ROAMCITY_ID=b.AREAID(+) and b.AREAname=t.areaname(+) and a.roamcity_id<>''533'') a group by a.user_id' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg4a'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg4a as select a.user_id,a.opposite_regular_no,a.call_tm,a.cs,a.call_duration_m,nvl(a.zj_duration_m,0) zj_duration_m from zhyw.zwzx_mhh_call_oppo_hd a, shzc.jbsj_jfbbdr_gagx_szhm_sjhgd b where a.user_id=b.subsid' ; execute immediate (SQL_STRING); ----取最大 ---最大月份 select max(a.OBJECT_NAME) OBJECT_NAME into V_TAB1 From all_objects a where objEct_name like UPPER('zwzx_mhh_call_oppo_20%') and a.OWNER='ZHYW' ; fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg4b'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg4b as select a.user_id,a.opposite_regular_no,a.cs,a.call_duration_m,nvl(a.zj_duration_m,0) zj_duration_m from zhyw.''||V_TAB1||'' a, shzc.jbsj_jfbbdr_gagx_szhm_sjhgd b where a.user_id=b.subsid' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg4h'),'SHZC'); SQL_STRING:='create table shzc.jbsj_jfbbdr_gagx_szhm_sjhg4h as select a.user_id,sum(a.cs) cs,sum(a.call_duration_m) call_duration_m, count(distinct a.opposite_regular_no) opposite_regular_no, round(count(distinct a.opposite_regular_no)/sum(a.cs) ,4) 离散度 from ( select a.user_id,a.opposite_regular_no,a.cs,a.call_duration_m from shzc.jbsj_jfbbdr_gagx_szhm_sjhg4a a union all select a.user_id,a.opposite_regular_no,a.cs,a.call_duration_m from shzc.jbsj_jfbbdr_gagx_szhm_sjhg4b a ) a group by a.user_id' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg11'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11 as select a.*,b.subsid,(case when a.recdefid in (''StopSubsQZ'',''ChangeProduct'') and (nvl(a.notes,''0'') like ''%关于紧急关停异常高频号码的通知%'' or nvl(a.notes,''0'') like ''%高频%'' or nvl(a.notes,''0'') like ''%举报%'' or nvl(a.notes,''0'') like ''%诈骗%'' or nvl(a.notes,''0'') like ''%骚扰%'' or nvl(a.notes,''0'') like ''%公安机关%关停%'' or nvl(a.notes,''0'') like ''%12321%'') then ''停机'' when a.recdefid in (''OpenSubsQZ'',''ChangeProduct'') and (nvl(a.notes,''0'') like ''%客户签署承诺书申请开机%'' or nvl(a.notes,''0'') like ''%公安机关%开机%'' or nvl(a.notes,''0'') like ''%134站点%开机%'' or nvl(a.notes,''0'') like ''%签署%'') then ''开机'' end) 停开机类型 from SHZC.tskhgt_sljl_hz a, (select b.servnumber,b.subsid,b.createdate,b.status, nvl(case when b.status in (''US10'',''US30'') then to_date(''2099'',''yyyy'') end,b.statusdate) statusdate from shzc.jbsj_jfbbdr_gagx_szhm_sjhgd b where b.createdate is not null ) b where a.servnumber=b.servnumber and a.recdate>=b.createdate and a.recdate<b.statusdate' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg11a'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11a as select a.subsid,to_char(a.recdate,''yyyymmdd'') recdate, max(nvl(case when a.notes like ''%诈骗%'' then ''诈骗'' when a.notes like ''%高频号码%'' then ''高频'' when a.notes like ''%骚扰%'' then ''骚扰'' end,''其他'')) 关停类型 from SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11 a where a.停开机类型=''停机'' and a.recdate>=sysdate-366 group by a.subsid,to_char(a.recdate,''yyyymmdd'')' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhg11b'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11b as select a.subsid,count(distinct a.recdate) oids, count(distinct case when a.关停类型 =''诈骗'' then a.recdate end) 诈骗, count(distinct case when a.关停类型 =''高频'' then a.recdate end) 高频, count(distinct case when a.关停类型 =''骚扰'' then a.recdate end) 骚扰, count(distinct case when a.关停类型 =''其他'' then a.recdate end) 其他 from SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11a a group by a.subsid' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sjhge'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sjhge as select a.subsid,a.servnumber,a.createdate,a.registerorgid,q.orgname,a.prodid,pp.prodname, a.hd_iden_no,a.证件关联,a.渠道关联,b.status,zt.dictname,b.statusdate, nvl(c.漫游地,''无'') 漫游地,nvl(c.风险漫游地,''无'') 风险漫游地, nvl(d.opposite_regular_no,0) 通话号码数,nvl(d.cs,0) 通话数, nvl(d.call_duration_m,0) 通话分钟数,nvl(d.离散度,0) 离散度, nvl(e.诈骗,0) 诈骗,nvl(e.高频,0) 高频,nvl(e.骚扰,0) 骚扰, nvl(f.最终得分,100) 最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjhgd a, zhyw.subscriber b, (select * from tbcs.dict_item@bcv where groupid=''US'') zt, tbcs.organization@bcv q, tbcs.product@bcv pp, shzc.jbsj_jfbbdr_gagx_szhm_sjhg3b c , shzc.jbsj_jfbbdr_gagx_szhm_sjhg4h d, SHZC.jbsj_jfbbdr_gagx_szhm_sjhg11b e, shzc.zjhc_lwsj_syz_yczjgm f where a.subsid=b.subsid(+) and a.prodid=pp.prodid(+) and b.status=zt.dictid(+) and a.registerorgid=q.orgid(+) and a.subsid=c.user_id(+) and a.subsid=d.user_id(+) and a.subsid=e.subsid(+) and a.hd_iden_no=f.hd_iden_no(+)' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sj_tmp'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sj_tmp as select distinct a.* from (select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end, a.证件关联) 关联类型, nvl(case when a.subsid=b.subsid then 1 end, 2) 关联类型id, a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''否'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.hd_iden_no=a.hd_iden_no union all select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end,a.渠道关联) , nvl(case when a.subsid=b.subsid then 1 end, 3) 关联类型id,a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''是'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.registerorgida=a.registerorgid union all select b.id,nvl(d.name,''全市'') name,b.涉案号码,b.邮件转派时间,b.受害人电话,b.类型,b.案件类别, b.startdate,b.开户类型,b.发案时间a,a.hd_iden_no,b.相差90天以内, a.subsid,a.servnumber,a.createdate,a.orgname,a.prodname, nvl(case when a.subsid=b.subsid then ''当事号码'' end,a.证件关联), nvl(case when a.subsid=b.subsid then 1 end, 2) 关联类型id,a.dictname,a.statusdate, a.漫游地,a.风险漫游地,a.通话号码数,a.通话数,a.通话分钟数,a.离散度,a.诈骗,a.高频,a.骚扰,a.最终得分 from shzc.jbsj_jfbbdr_gagx_szhm_sjh_bd b, shzc.jbsj_jfbbdr_gagx_szhm_sjhge a , zhyw.rpt_county d where b.相差90天以内=''是'' and substr(b.registerorgida,8,1)=d.county_id(+) and b.hd_iden_no=a.hd_iden_no ) a where nvl(a.关联类型,''无'')<>''无'' ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_sj_id'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_sj_id as select a.id ida,a.name namea,a.类型 类型a,a.开户类型 开户类型a,a.相差90天以内 相差90天以内a, to_char(count(distinct a.subsid)) subsida, to_char(count(distinct case when a.关联类型=''当事号码'' then a.subsid end)) 当事号码, to_char(count(distinct case when a.关联类型=''证件关联'' then a.subsid end)) 证件关联, to_char(count(distinct case when a.关联类型=''渠道关联'' then a.subsid end)) 渠道关联 from SHZC.jbsj_jfbbdr_gagx_szhm_sj_tmp a group by a.id,a.name,a.类型,a.开户类型,a.相差90天以内' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jbsj_jfbbdr_gagx_szhm_dlqk'),'SHZC'); SQL_STRING:='create table SHZC.jbsj_jfbbdr_gagx_szhm_dlqk as select '''||p_userid||''' p_userid,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码,'''||v_qx_id||''' 归属区县,'''||v_orgid||''' 组织机构,sysdate in_time, '''||v_lx||''' 报表类型,'''||p_id||''' 选择id,'''||p_cz_lx||''' 处理类型 ,'''||p_xz_gh||''' 选择工号, '''||v_note||''' 备注信息 from dual ' ; execute immediate (SQL_STRING); SQL_STRING:='insert into SHZC.jbsj_jfbbdr_gagx_szhm_dlqk_bd select a.* from SHZC.jbsj_jfbbdr_gagx_szhm_dlqk a ' ; execute immediate (SQL_STRING); commit; v_sql:='select ''无'' from dual ' ; end if ; open p_cursor for v_sql; end ;