create or replace procedure biller533.shzc_202103_jh_zdyw_fxyjgc (p_userid varchar2,p_lx varchar2,p_yjywlx varchar2, p_id varchar2, p_czyy varchar2,p_iswf varchar2,p_notes varchar2,p_qx varchar2, p_cursor in out Results.ref_cursor_type) as v_sql VARCHAR2(20000); SQL_STRING VARCHAR2(20000); v_lx VARCHAR2(10); v_yjywlx VARCHAR2(15); v_id VARCHAR2(15); v_yjywm VARCHAR2(50); v_qx_id VARCHAR2(10); v_orgid VARCHAR2(50); v_name VARCHAR2(50); v_sjhm VARCHAR2(50); v_czyy VARCHAR2(500); v_iswf VARCHAR2(500); v_notes VARCHAR2(500); begin v_yjywlx :=nvl(p_yjywlx,0); v_id :=nvl(p_id,'0'); ---申请id null 处理 v_lx :=trim(p_lx); v_czyy :=nvl(trim(p_czyy),'无'); v_iswf :=nvl(trim(p_iswf),'无'); v_notes :=nvl(trim(p_notes),'无'); ---经分工号获取区县 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 ; select max(预警业务类型) into v_yjywm from shzc.jh_zdyw_fxyj_yjlx where trim(xl)=v_yjywlx; fan_drop_retable(upper('jh_zdyw_fxyj_sjcda'),'SHZC'); SQL_STRING:='create table shzc.jh_zdyw_fxyj_sjcda as select a.* from (select a.区县,a.问题,nvl(b.预警业务类型,a.预警业务类型) 预警业务类型,e.xl 预警业务类型id,a.usid,a.in_time, zhyw.shzc_zfc_zftq_tscl(a.问题,''【'',''】'') 渠道名,nvl(d.区县,a.区县) 渠道区县,d.渠道编码, row_number() over (partition by a.区县,a.问题,nvl(b.预警业务类型,a.预警业务类型) order by a.in_time ) 排名 from shzc.jh_zdyw_fxyj_sjcd a , shzc.jh_zdyw_fxyj_yjlx b, (select * from (select d.*,row_number() over (partition by d.渠道编码 order by d.导入日期 desc ) 排名 from SHZC.tmp_szc_201511_dlhz d ) d where d.排名=1 ) d, shzc.jh_zdyw_fxyj_yjlx e where a.区县<>''区县'' and a.预警业务类型=to_char(b.xl(+)) and nvl(b.预警业务类型,a.预警业务类型)=e.预警业务类型(+) and zhyw.shzc_zfc_zftq_tscl(a.问题,''【'',''】'') =d.渠道名称(+)) a where 排名=1 ' ; execute immediate (SQL_STRING); ----插入数据 SQL_STRING:='insert into shzc.jh_zdyw_fxyj_sjcd_bd(区县,问题,预警业务类型,USID,IN_TIME,渠道名,渠道区县,渠道编码,预警业务类型id) select a.区县,问题,预警业务类型,USID,IN_TIME,渠道名,渠道区县,渠道编码,预警业务类型id from shzc.jh_zdyw_fxyj_sjcda a where not exists (select 1 from shzc.jh_zdyw_fxyj_sjcd_bd t where a.区县=t.区县 and a.问题=t.问题 and a.预警业务类型=t.预警业务类型 and a.in_time=t.in_time) ' ; execute immediate (SQL_STRING); commit; fan_drop_retable(upper('jh_zdyw_fxyj_sjcd_fkqk'),'SHZC'); SQL_STRING:='create table SHZC.jh_zdyw_fxyj_sjcd_fkqk as select '''||p_userid||''' p_userid,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码,'''||v_qx_id||''' 归属区县, '''||v_orgid||''' 组织机构,sysdate in_time,'''||v_lx||''' 操作类型,'''||v_yjywlx||''' 预警业务类型,'''||v_id||''' 选择id, '''||v_czyy||''' 查证原因, '''||v_iswf||''' 是否违规, '''||v_notes||''' 处罚办法, '''||v_yjywm||''' 预警业务类型名 from dual ' ; execute immediate (SQL_STRING); SQL_STRING:='insert into SHZC.jh_zdyw_fxyj_sjcd_fkqk_bd select a.* from SHZC.jh_zdyw_fxyj_sjcd_fkqk a ' ; execute immediate (SQL_STRING); commit; --1 全部涉案数据 --2 公安部涉案数据 --3 工信部举报数据 --4 工单涉及号码 ---信息安全考核指标和清单报表 if v_lx='查看' then ---查看 if v_yjywlx=0 then if v_id='0' then v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) and a.id=b.选择id(+) order by a.id desc ) a' ; else v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.id='''||v_id||''' and a.id=b.选择id(+) and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; end if ; else if v_id='0' then v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.预警业务类型='''||v_yjywm||''' and a.id=b.选择id(+) and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; else v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.预警业务类型='''||v_yjywm||''' and a.id=b.选择id(+) and a.id='''||v_id||''' and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; end if ; end if ; elsif v_lx='反馈' then if v_id<>'0' then SQL_STRING:='insert into SHZC.jh_zdyw_fxyj_sjcd_fkqk_fbd select a.* from SHZC.jh_zdyw_fxyj_sjcd_fkqk a ' ; execute immediate (SQL_STRING); commit; fan_drop_retable(upper('jh_zdyw_fxyj_sjcd_fkqk_ebd'),'SHZC'); SQL_STRING:='create table SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd as select a.*,b.orgname from (select a.*,row_number() over (partition by a.选择id order by a.in_time desc ) 排名 from SHZC.jh_zdyw_fxyj_sjcd_fkqk_fbd a ) a, tbcs.organization@bcv b where a.组织机构=b.orgid(+) and a.排名=1 ' ; execute immediate (SQL_STRING); end if; if v_yjywlx=0 then if v_id='0' then v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) and a.id=b.选择id(+) order by a.id desc ) a' ; else v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.id='''||v_id||''' and a.id=b.选择id(+) and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; end if ; else if v_id='0' then v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.预警业务类型='''||v_yjywm||''' and a.id=b.选择id(+) and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; else v_sql:='select ''ID'',''区县'',''问题'',''预警业务类型'',''发起时间'',''渠道名'', ''反馈人员'',''人员组织'',''反馈时间'',''查证原因'',''是否违规'',''处罚办法'' from dual union all select a.* from (select to_char(a.id),a.区县,a.问题,a.预警业务类型,to_char(a.in_time,''yyyymmdd hh24:mi:ss''),a.渠道名, b.申请人姓名,b.orgname,to_char(b.in_time,''yyyymmdd hh24:mi:ss''),b.查证原因,b.是否违规,b.处罚办法 from shzc.jh_zdyw_fxyj_sjcd_bd a, SHZC.jh_zdyw_fxyj_sjcd_fkqk_ebd b where a.预警业务类型='''||v_yjywm||''' and a.id=b.选择id(+) and a.id='''||v_id||''' and ( a.区县='''||p_qx||''' or ''全市'' ='''||p_qx||''' ) order by a.id desc ) a' ; end if ; end if ; end if ; open p_cursor for v_sql; end ;