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 ;
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
