2023052_数据库过程_区县业务稽核过程语句

/*---2023--0522 稽核监控 
受理后回退率  
该指标预警营销不规范致回退问题。地市各项重点业务当月办理用户当月累计回退量/当月办理量。  
归属区县、业务名称、受理回退区县、回退号码、回退工号、开户时间、状态、状态时间、回退受理工号、回退时间、回退受理渠道、归属网格

协议补收款受理率  
该指标预警低质发展行为。当月在上月有协议补收款且≥10元用户上受理业务量/当月受理业务量。  
归属区县、业务名称、受理业务区县、补收号码、受理业务名称、受理工号、受理时间、受理渠道、开户时间、状态、状态时间、归属网格

受理后停机/销户率  
该指标预警受理后号码留存情况。地市各项重点业务当月办理用户截止到次月停机或销户量/当月办理量。  
归属区县、号码、业务名称、受理业务区县、回退工号、受理渠道、开户时间、停机/销户时间、开户时间、状态、归属网格、办理重点业务数量

生效后取消率  
该指标预警业务流失问题。地市各项重点业务当月生效客户到当月底的累计取消量/当月累计生效量。  ---确定为 当月生效在当月取消/当月生效量 
归属区县、号码、业务名称、受理业务区县、受理渠道、受理工号、受理时间、取消时间、取消工号、取消渠道、开户时间、办理重点业务数量、开户时间、状态、状态时间、归属网格

 05-18 16:34:04
这几个指标计算的时候，回退是按什么口径，取消是按什么口径，最后的统计是按照号码归属还是按受理地市？
 05-18 16:37:53
报表描述里有，按受理
 05-18 16:38:49
意思是号码 a区县给客户受理的业务，如果其他区县给回退的也是汇总到 受理区县？
 05-18 16:42:28
是的*/

----字典表  ：业务类别 优惠编码 回退指标项 协议补收款指标项 受理后停机销户指标项 生效后取消率指标项

--create table zhyw.yhh_slyc_priv_prod_type
--(业务类别 varchar2(40),优惠编码 varchar2(50),回退指标项 varchar2(8),
--协议补收款指标项 varchar2(8),受理后停机销户指标项 varchar2(8),生效后取消率指标项 varchar2(8));

--select * from zhyw.yhh_slyc_priv_prod_type for update ;


zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_a'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_a as
select * from 
(select distinct a.*,nvl(nvl(b.privname,c.prodname),d.VC_BIZNAME) id_name,
nvl(case when b.privname is not null then ''priv'' 
         when c.prodname is not null then ''prod''
         when d.VC_BIZNAME is not null then ''spid'' end,''不详'') id_type,
row_number() over (partition by a.优惠编码,a.业务类别  order by 1 desc ) 排名 
from zhyw.yhh_slyc_priv_prod_type a,
TBCS.privilege_SCHEME@BCV b,
tbcs.product@bcv c,
tbcs.iboss_spbizinfo@bcv d
where a.优惠编码=b.privid(+)
and a.优惠编码=c.prodid(+)
and a.优惠编码=d.BIZCODE(+)) a
where 排名=1';
EXECUTE IMMEDIATE (SQL_STRING);

--1.C：新入网（20%）、终端合约（20%）、流量包（10%）、模组升档合约（20%）
--2.H：宽带（15%）、移动高清（15%）、千兆宽带（10%）、家庭安防、智能组网、大屏点播（各5%）
--3.N：权益业务（10%）、视频彩铃、咪咕视频、咪咕音乐、咪咕阅读、移动云盘、云游戏、5G视频秘书、惠玩包（各5%）

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_b'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_b as
select a.*,nvl(case 
when a.业务类别 like ''%新入网%'' then ''新入网''
when a.业务类别 like ''%终端合约%'' then ''终端合约''
when a.业务类别 like ''%流量包%'' then ''流量包''
when a.业务类别 like ''%模组升档合约%'' then ''模组升档合约''
  
when a.业务类别 like ''%千兆宽带%'' then ''千兆宽带''
when a.业务类别 like ''%宽带%'' then ''宽带''
when a.业务类别 like ''%移动高清%'' then ''移动高清''
when a.业务类别 like ''%千兆宽带%'' then ''千兆宽带''
when a.业务类别 like ''%家庭安防%'' then ''家庭安防''
when a.业务类别 like ''%智能组网%'' then ''智能组网''
when a.业务类别 like ''%大屏点播%'' then ''大屏点播''
  
when a.业务类别 like ''%权益业务%'' then ''权益业务''
when a.业务类别 like ''%视频彩铃%'' then ''视频彩铃''
when a.业务类别 like ''%咪咕视频%'' then ''咪咕视频''
when a.业务类别 like ''%咪咕音乐%'' then ''咪咕音乐''
when a.业务类别 like ''%咪咕阅读%'' then ''咪咕阅读''
when a.业务类别 like ''%移动云盘%'' then ''移动云盘''
when a.业务类别 like ''%云游戏%'' then ''云游戏''
when a.业务类别 like ''%5G视频秘书%'' then ''5G视频秘书''
when a.业务类别 like ''%惠玩包%'' then ''惠玩包'' end,''不详'') 业务类型,
  
nvl(case 
when a.业务类别 like ''%新入网%'' then ''C''
when a.业务类别 like ''%终端合约%'' then ''C''
when a.业务类别 like ''%流量包%'' then ''C''
when a.业务类别 like ''%模组升档合约%'' then ''C''
  
when a.业务类别 like ''%千兆宽带%'' then ''H''
when a.业务类别 like ''%宽带%'' then ''H''
when a.业务类别 like ''%移动高清%'' then ''H''
when a.业务类别 like ''%家庭安防%'' then ''H''
when a.业务类别 like ''%智能组网%'' then ''H''
when a.业务类别 like ''%大屏点播%'' then ''H''
  
when a.业务类别 like ''%权益业务%'' then ''N''
when a.业务类别 like ''%视频彩铃%'' then ''N''
when a.业务类别 like ''%咪咕视频%'' then ''N''
when a.业务类别 like ''%咪咕音乐%'' then ''N''
when a.业务类别 like ''%咪咕阅读%'' then ''N''
when a.业务类别 like ''%移动云盘%'' then ''N''
when a.业务类别 like ''%云游戏%'' then ''N''
when a.业务类别 like ''%5G视频秘书%'' then ''N''
when a.业务类别 like ''%惠玩包%'' then ''N'' end,''0'') 业务大类
from zhyw.yhh_slyc_priv_prod_type_a a ';
EXECUTE IMMEDIATE (SQL_STRING);

-----业务组合查找

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_spid'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_spid as
select a.OID, a.SUBSID, a.SPID, a.SPBIZID, a.APPLYOID, 
a.STARTDATE, a.CANCELOID, a.ENDDATE, a.APPLYACCESSTYPE, a.APPLYOPERID, a.CANCELACCESSTYPE, a.CANCELOPERID,
b.业务类别,b.优惠编码,b.回退指标项,b.协议补收款指标项,b.受理后停机销户指标项,b.生效后取消率指标项,b.id_type,b.业务类型,b.业务大类,b.id_name
from tbcs.subs_spservice@bcv a,
 (select * from zhyw.yhh_slyc_priv_prod_type_b b where b.id_type =''spid'' ) b
 where a.SPBIZID=b.优惠编码
 and a.STARTDATE>=to_date('''||v_monsrq||''',''yyyymm'')  ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_priv'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_priv as
select a.OID, a.SUBSID, a.prodid, a.privid, a.APPLYOID, 
a.STARTDATE, a.CANCELOID, a.ENDDATE, a.APPLYACCESSTYPE, a.APPLYOPERID, a.CANCELACCESSTYPE, a.CANCELOPERID,
b.业务类别,b.优惠编码,b.回退指标项,b.协议补收款指标项,b.受理后停机销户指标项,b.生效后取消率指标项,b.id_type,b.业务类型,b.业务大类,b.id_name
from zhyw.subs_privilege a,
 (select * from zhyw.yhh_slyc_priv_prod_type_b b where b.id_type =''priv'' ) b
 where a.privid=b.优惠编码
 and a.STARTDATE>=to_date('''||v_monsrq||''',''yyyymm'') ';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_prod'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_prod as
select a.OID, a.SUBSID, a.prodid, a.privid, a.APPLYOID, 
a.STARTDATE, a.CANCELOID, a.ENDDATE, a.APPLYACCESSTYPE, a.APPLYOPERID, a.CANCELACCESSTYPE, a.CANCELOPERID,
b.业务类别,b.优惠编码,b.回退指标项,b.协议补收款指标项,b.受理后停机销户指标项,b.生效后取消率指标项,b.id_type,b.业务类型,b.业务大类,b.id_name
from zhyw.subs_privilege a,
 (select * from zhyw.yhh_slyc_priv_prod_type_b b where b.id_type =''prod'' ) b
 where a.prodid=b.优惠编码
 and a.STARTDATE>=to_date('''||v_monsrq||''',''yyyymm'') ';
EXECUTE IMMEDIATE (SQL_STRING);

--------------合并优惠表

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_all'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_all as
 select * from zhyw.yhh_slyc_priv_prod_type_spid a
 union all
 select * from zhyw.yhh_slyc_priv_prod_type_priv a
 union all
 select * from zhyw.yhh_slyc_priv_prod_type_prod a ';
EXECUTE IMMEDIATE (SQL_STRING);

---归属区县、号码、业务名称、受理业务区县、受理渠道、受理工号、受理时间、取消时间、取消工号、取消渠道、开户时间、办理重点业务数量、开户时间、状态、状态时间、归属网格

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slmx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slmx as
 select b.ownerorgid,b.servnumber,a.oid,a.subsid,b.createdate,b.status,b.statusdate,
 a.spid,a.spbizid,a.applyoid,a.startdate,a.canceloid,a.enddate,a.applyoperid,a.canceloperid,
 a.业务类别, a.优惠编码,a.id_name, a.回退指标项, a.协议补收款指标项, a.受理后停机销户指标项, a.生效后取消率指标项, a.ID_TYPE, a.业务类型, a.业务大类,
 c.recdefid recdefid_kt,c.recorgid recorgid_kt,c.recopid recopid_kt,c.recdate recdate_kt,
 d.recdefid recdefid_qx,d.recorgid recorgid_qx,d.recopid recopid_qx,d.recdate recdate_qx
 from zhyw.yhh_slyc_priv_prod_type_all a,
 zhyw.subscriber b,
 zhyw.shc_reception c,
 zhyw.shc_reception d
 where a.subsid=b.subsid
 and a.applyoid=c.oid(+)
 and a.canceloid=d.oid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

---专用区县表
 
  /*create table zhyw.yhh_slyc_priv_prod_county as
  select a.受理区县id county_id,a.受理区县 name
  from zhyw.yhh_slyc_priv_prod_type_slhht_mx a
  group by a.受理区县id,a.受理区县;
  
  select * from zhyw.yhh_slyc_priv_prod_county for update;*/
  
---受理后回退率  
--该指标预警营销不规范致回退问题。地市各项重点业务当月办理用户当月累计回退量/当月办理量。  
--归属区县、业务名称、受理回退区县、回退号码、回退工号、开户时间、状态、状态时间、回退受理工号、回退时间、回退受理渠道、归属网格


zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht as
 select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.回退指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
  and nvl(a.ENDDATE,sysdate+9999)=a.STARTDATE
  and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsr||'''
 and a.recdate_kt -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
 
 ---业务受理区县、业务名称、受理量、回退量、回退率  ----业务名称 是指 业务类型


zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht_mx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht_mx as
   select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.回退指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsr||'''
 and a.recdate_kt -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
EXECUTE IMMEDIATE (SQL_STRING);

 -------受理业务量

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht_hza'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht_hza as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht_mx a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht_mx a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht_mx a
 group by a.业务大类,a.业务类型 ';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht_hzb'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht_hzb as
 select ''区县分业务'' type,a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
  union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理回退区县id,''全市'' 受理回退区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhht a
 group by a.业务大类,a.业务类型 ';
EXECUTE IMMEDIATE (SQL_STRING);


zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht_hzc'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht_hzc as
 select distinct a.* from 
 (select a.type,a.受理区县ID, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhht_hza a
 union all
 select b.type,b.受理区县ID, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhht_hzb b ) a ';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhht_hzd'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhht_hzd as
 select '''||P_DAY||''' cycle, a.*,
 nvl(b.受理量,0) 受理量,nvl(b.applyoids,0) sl_applyoids ,
 nvl(c.受理量,0) 回退量,nvl(c.applyoids,0) ht_applyoids ,
 round(nvl(c.受理量,0)/decode(nvl(b.受理量,0),0,1,nvl(b.受理量,0))*100,2) 回退受理业务率,
 round(nvl(c.applyoids,0)/decode(nvl(b.applyoids,0),0,1,nvl(b.applyoids,0))*100,2) 回退受理流水率
 from zhyw.yhh_slyc_priv_prod_type_slhht_hzc a,
 zhyw.yhh_slyc_priv_prod_type_slhht_hza b,
 zhyw.yhh_slyc_priv_prod_type_slhht_hzb c
 where a.type=b.type(+)
 and a.受理区县id=b.受理区县id(+)
 and a.业务类型=b.业务类型(+)
 and a.type=c.type(+)
 and a.受理区县id=c.受理区县ID(+)
 and a.业务类型=c.业务类型(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

 ---------------------------------------------------------------------------------
 --受理后停机/销户率  ---上月受理本月停机
--该指标预警受理后号码留存情况。地市各项重点业务当月办理用户截止到次月停机或销户量/当月办理量。  
--归属区县、号码、业务名称、受理业务区县、回退工号、受理渠道、开户时间、停机/销户时间、开户时间、状态、归属网格、办理重点业务数量

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj as
 select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and zt.dictname<>''正使用''
  and a.受理后停机销户指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsrq||'''
 and a.recdate_kt -1 <to_date('''||v_last_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj_mx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj_mx as
   select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.受理后停机销户指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsrq||'''
 and a.recdate_kt -1 <to_date('''||v_last_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj_hza'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj_hza as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj_mx a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj_mx a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj_mx a
 group by a.业务大类,a.业务类型 ';
EXECUTE IMMEDIATE (SQL_STRING);
  
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj_hzb'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj_hzb as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhtj a
 group by a.业务大类,a.业务类型 ';
EXECUTE IMMEDIATE (SQL_STRING);
 
 
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj_hzc'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj_hzc as
 select distinct a.* from 
 (select a.type,a.受理区县ID, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhtj_hza a
 union all
 select b.type,b.受理区县id, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhtj_hzb b ) a';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhtj_hzd'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhtj_hzd as
 select '''||P_DAY||'''  cycle, a.*,
 nvl(b.受理量,0) 受理量,nvl(b.applyoids,0) sl_applyoids ,
 nvl(c.受理量,0) 回退量,nvl(c.applyoids,0) ht_applyoids ,
 round(nvl(c.受理量,0)/decode(nvl(b.受理量,0),0,1,nvl(b.受理量,0))*100,2) 回退受理业务率,
 round(nvl(c.applyoids,0)/decode(nvl(b.applyoids,0),0,1,nvl(b.applyoids,0))*100,2) 回退受理流水率
 from zhyw.yhh_slyc_priv_prod_type_slhtj_hzc a,
 zhyw.yhh_slyc_priv_prod_type_slhtj_hza b,
 zhyw.yhh_slyc_priv_prod_type_slhtj_hzb c
 where a.type=b.type(+)
 and a.受理区县id=b.受理区县id(+)
 and a.业务类型=b.业务类型(+)
 and a.type=c.type(+)
 and a.受理区县id=c.受理区县ID(+)
 and a.业务类型=c.业务类型(+)';
EXECUTE IMMEDIATE (SQL_STRING);

 ---------------------------------------------------------------------------------
 --生效后取消率  
--该指标预警业务流失问题。地市各项重点业务当月生效客户到当月底的累计取消量/当月累计生效量。  ---确定为 当月生效在当月取消/当月生效量 
--归属区县、号码、业务名称、受理业务区县、受理渠道、受理工号、受理时间、取消时间、取消工号、取消渠道、开户时间、办理重点业务数量、开户时间、状态、状态时间、归属网格

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx as
 select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and nvl(a.enddate,sysdate+9999)<add_months(to_date('''||v_monsr||''',''yyyymm''),1)
  and a.生效后取消率指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.startdate,sysdate-999),''yyyymm'')='''||v_monsr||'''
 and a.startdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx_mx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx_mx as
   select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.生效后取消率指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.startdate,sysdate-999),''yyyymm'')='''||v_monsr||'''
 and a.startdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx_hza'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx_hza as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx_mx a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx_mx a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx_mx a
 group by a.业务大类,a.业务类型';
EXECUTE IMMEDIATE (SQL_STRING);
 
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx_hzb'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx_hzb as
 select ''区县分业务'' type, a.受理区县id 受理区县id,a.受理区县 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id 受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_slhqx a
 group by a.业务大类,a.业务类型';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx_hzc'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx_hzc as
 select distinct a.* from 
 (select a.type,a.受理区县ID, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhqx_hza a
 union all
 select b.type,b.受理区县id, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_slhqx_hzb b ) a';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_slhqx_hzd'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_slhqx_hzd as
 select '''||P_DAY||''' cycle, a.*,
 nvl(b.受理量,0) 受理量,nvl(b.applyoids,0) sl_applyoids ,
 nvl(c.受理量,0) 回退量,nvl(c.applyoids,0) ht_applyoids ,
 round(nvl(c.受理量,0)/decode(nvl(b.受理量,0),0,1,nvl(b.受理量,0))*100,2) 回退受理业务率,
 round(nvl(c.applyoids,0)/decode(nvl(b.applyoids,0),0,1,nvl(b.applyoids,0))*100,2) 回退受理流水率
 from zhyw.yhh_slyc_priv_prod_type_slhqx_hzc a,
 zhyw.yhh_slyc_priv_prod_type_slhqx_hza b,
 zhyw.yhh_slyc_priv_prod_type_slhqx_hzb c
 where a.type=b.type(+)
 and a.受理区县id=b.受理区县id(+)
 and a.业务类型=b.业务类型(+)
 and a.type=c.type(+)
 and a.受理区县id=c.受理区县ID(+)
 and a.业务类型=c.业务类型(+)';
EXECUTE IMMEDIATE (SQL_STRING);

---------------------------------------------------------------------------------
 --协议补收款受理率  
--该指标预警低质发展行为。当月在上月有协议补收款且≥10元用户上受理业务量/当月受理业务量。  
--归属区县、业务名称、受理业务区县、补收号码、受理业务名称、受理工号、受理时间、受理渠道、开户时间、状态、状态时间、归属网格
--202304月数据看 202303账期费用

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_bs'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_bs as
 select REGION, ACCTID, BILLCYCLE, SUBSID, ITEMTYPE, ITEMCODE, ITEMADDCODE, PARTIALITEMSEQ, FEE  
 from account.billitem533@zwbcv a 
 where substr(BILLCYCLE,1,6)='''||v_monsrq||'''
 and nvl(ITEMTYPE,4)<4
 and ITEMCODE like ''25%''
 and REGION=''533'' ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_bsh'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_bsh as
 select a.subsid,substr(a.billcycle,1,6) billcycle,sum(a.fee)/100  补收款元
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_bs a
 group by a.subsid,substr(a.billcycle,1,6)
 having sum(a.fee)>=1000  ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl as
 select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.协议补收款指标项=''是''
  and a.subsid in (select t.subsid from zhyw.yhh_slyc_priv_prod_type_bsfhsl_bsh t )
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsrq||'''
 and a.recdate_kt -1 <to_date('''||v_last_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_mx'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_mx as
   select distinct substr(a.ownerorgid,1,8) 归属区县id,b.name 归属区县, a.业务大类,a.业务类型,a.id_type,a.业务类别, a.优惠编码, a.ID_NAME,
 substr(a.recorgid_kt,1,8) 受理区县id,d.name 受理区县,a.subsid,a.servnumber 回退号码,a.applyoid,a.recdate_kt,a.startdate,a.enddate,
 a.recopid_kt 受理工号,a.createdate 开户时间,zt.dictname 状态,a.statusdate 状态时间,a.canceloid,a.recopid_qx 回退受理工号,
 a.recdate_qx 回退时间,substr(a.recorgid_qx,1,8) 受理回退区县id,c.name 受理回退区县,a.recorgid_qx 回退受理渠道,e.unit_id,e.unit_name
 from zhyw.yhh_slyc_priv_prod_type_slmx a,
 zhyw.yhh_slyc_priv_prod_county b, zhyw.yhh_slyc_priv_prod_county c, zhyw.yhh_slyc_priv_prod_county d, zhyw.shc_organization e,
 (select * from tbcs.dict_item@bcv where groupid=''US'') zt
  where substr(a.recorgid_qx,1,8)=c.county_id(+)
  and substr(a.recorgid_kt,1,8)=d.county_id(+)
  and substr(a.ownerorgid,1,8)=b.county_id(+)
  and a.recorgid_qx=e.orgid(+)
  and a.status=zt.dictid(+)
  and a.协议补收款指标项=''是''
  and substr(a.recorgid_kt,1,5) =''SD.LC'' 
 and to_char(nvl(a.recdate_kt,sysdate-999),''yyyymm'')='''||v_monsrq||'''
 and a.recdate_kt -1 <to_date('''||v_last_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);

 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_hza'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_hza as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_mx a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_mx a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_mx a
 group by a.业务大类,a.业务类型';
EXECUTE IMMEDIATE (SQL_STRING);
  
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_hzb'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzb as
 select ''区县分业务'' type, a.受理区县id,a.受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl a
 group by a.受理区县id,a.受理区县,a.业务大类,a.业务类型
 union all ---区县全业务
 select ''区县全业务'' type,a.受理区县id,a.受理区县,''all'' 业务大类,''全部'' 业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl a
 group by a.受理区县id,a.受理区县
 union all ---全市分业务
 select ''全市分业务'' type,''SD.LC'' 受理区县id,''全市'' 受理区县,a.业务大类,a.业务类型,count(distinct a.applyoid||a.优惠编码) 受理量,count(distinct a.applyoid) applyoids
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl a
 group by a.业务大类,a.业务类型 ';
EXECUTE IMMEDIATE (SQL_STRING);
 
 
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_hzc'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzc as
 select distinct a.* from 
 (select a.type,a.受理区县ID, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_hza a
 union all
 select b.type,b.受理区县id, 受理区县, 业务大类, 业务类型 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzb b ) a ';
EXECUTE IMMEDIATE (SQL_STRING);
 
zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_bsfhsl_hzd'),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzd as
 select '''||P_DAY||'''  cycle, a.*,
 nvl(b.受理量,0) 受理量,nvl(b.applyoids,0) sl_applyoids ,
 nvl(c.受理量,0) 回退量,nvl(c.applyoids,0) ht_applyoids ,
 round(nvl(c.受理量,0)/decode(nvl(b.受理量,0),0,1,nvl(b.受理量,0))*100,2) 回退受理业务率,
 round(nvl(c.applyoids,0)/decode(nvl(b.applyoids,0),0,1,nvl(b.applyoids,0))*100,2) 回退受理流水率
 from zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzc a,
 zhyw.yhh_slyc_priv_prod_type_bsfhsl_hza b,
 zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzb c
 where a.type=b.type(+)
 and a.受理区县id=b.受理区县id(+)
 and a.业务类型=b.业务类型(+)
 and a.type=c.type(+)
 and a.受理区县id=c.受理区县ID(+)
 and a.业务类型=c.业务类型(+) ';
EXECUTE IMMEDIATE (SQL_STRING);


----异常数据明细汇总
 
 zhyw.shc_drop_retable(upper('yhh_slyc_priv_prod_type_'||v_monsr),'ZHYW');
SQL_STRING:='create table zhyw.yhh_slyc_priv_prod_type_'||v_monsr||' as
 select ''受理后回退'' 类型,'''||v_monsr||''' cycle,a.*,'''||v_monsr||''' billcycle,0 补收款元
 from zhyw.yhh_slyc_priv_prod_type_slhht a
 union all
 select ''受理后停机'' 类型,'''||v_monsr||''' cycle,a.*,'''||v_monsr||''' billcycle,0 补收款元
 from zhyw.yhh_slyc_priv_prod_type_slhtj a 
 union all
 select ''生效后取消'' 类型,'''||v_monsr||''' cycle,a.*,'''||v_monsr||''' billcycle,0 补收款元
 from zhyw.yhh_slyc_priv_prod_type_slhqx a 
 union all
  select ''补收后受理'' 类型,'''||v_monsr||''' cycle,a.*,b.billcycle,b.补收款元 
  from zhyw.yhh_slyc_priv_prod_type_bsfhsl a,
  zhyw.yhh_slyc_priv_prod_type_bsfhsl_bsh b
  where a.subsid=b.subsid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);




---异常数据明细汇总

--将数据重的删了再导入，免得业务回退不体现
 SQL_STRING:='delete zhyw.yhh_slyc_priv_prod_type_all_bdhz a where cycle= '''||P_DAY||'''  ';
 EXECUTE IMMEDIATE (SQL_STRING);
 commit;

SQL_STRING:='insert into zhyw.yhh_slyc_priv_prod_type_all_bdhz 
select ''受理后回退'' 类型,a.* from zhyw.yhh_slyc_priv_prod_type_slhht_hzd a 
union all
select ''受理后停机'' 类型,a.* from zhyw.yhh_slyc_priv_prod_type_slhtj_hzd a
union all
select ''生效后取消'' 类型,a.* from zhyw.yhh_slyc_priv_prod_type_slhqx_hzd a
union all
select ''补收后受理'' 类型,a.* from zhyw.yhh_slyc_priv_prod_type_bsfhsl_hzd a  ';
 EXECUTE IMMEDIATE (SQL_STRING);
 commit;