20221118_数据库过程_渠道产能过程存档

create or replace procedure zhyw.SHA_QDCN_PGTX_CS(P_DAY VARCHAR2 DEFAULT TO_CHAR(SYSDATE - 1,'yyyymmdd')) as

  V_ZMLX   VARCHAR2(50);
  V_ZMLX_SZ   VARCHAR2(20);

SQL_STRING varchar2(30000);
v_day varchar2(8);
v_day1 varchar2(8);
v_day2 varchar2(8);
v_day5 varchar2(8);
v_day_qy1 varchar2(8);
v_month varchar2(6);
v_month_1 varchar2(6);
v_month_5 varchar2(6);
v_month1 varchar2(6);
v_monthq varchar2(6);
v_monthq_1 varchar2(6);
v_last_day varchar2(8);
v_last_day_2 varchar2(8);
v_last_day2 varchar2(8);
v_last_day3 varchar2(8);
v_monthS varchar2(6);
v_monthS3 varchar2(6);
v_yesr varchar2(4);
v_monsr varchar2(6);
v_monsrq varchar2(6);
v_monsrh varchar2(6);

---本月最后一天
v_monsr_last varchar2(8);

v_monsrq2 varchar2(6);
v_monsrq3 varchar2(6);
v_monsrq4 varchar2(6);
v_monsrq5 varchar2(6);
v_monsrq6 varchar2(6);
v_monsrq12 varchar2(6);
v_P_DAY varchar2(8);
V_TAB1 varchar2(900);
   zdyf   varchar2(10);

begin


    v_day        :=to_char(sysdate-1,'yyyymmdd');
    v_day1       :=to_char(sysdate-2,'yyyymmdd');
    v_day2       :=to_char(sysdate-3,'yyyymmdd');
    v_day_qy1       :=to_char(add_months(sysdate-2,-1),'yyyymmdd');
    v_day5       :=to_char(sysdate-5,'yyyymmdd');
    v_month      :=to_char(add_months(sysdate-1,-0),'yyyymm');
    v_month_1    :=to_char(add_months(sysdate-2,-0),'yyyymm');
    v_month_5    :=to_char(add_months(sysdate-5,-1),'yyyymm');
    v_month1     :=to_char(add_months(sysdate-1,1),'yyyymm');
    v_monthq     :=to_char(add_months(sysdate-1,-1),'yyyymm');
    v_monthq_1     :=to_char(add_months(sysdate-2,-1),'yyyymm');
    v_monthS     :=to_char(add_months(sysdate-1,-2),'yyyymm');
    v_monthS3    :=to_char(add_months(sysdate-1,-3),'yyyymm');
    v_last_day   :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-1)),'yyyymmdd') ;
    v_last_day_2   :=to_char(last_day(add_months(sysdate-2,-1)),'yyyymmdd') ;
    v_last_day2  :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-2)),'yyyymmdd') ;
    v_last_day3  :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),-3)),'yyyymmdd') ;

    v_yesr       :=substr(P_DAY,1,4);
    v_monsr      :=substr(P_DAY,1,6);
    v_monsrq      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-1),'yyyymm') ;
    v_monsrh      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),1),'yyyymm') ;
    
    v_monsr_last :=to_char(last_day(add_months(to_date(P_DAY,'yyyymmdd'),0)),'yyyymmdd') ;

    v_monsrq2      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-2),'yyyymm') ;
    v_monsrq3      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-3),'yyyymm') ;
    v_monsrq4      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-4),'yyyymm') ;
    v_monsrq5      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-5),'yyyymm') ;
    v_monsrq6      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-6),'yyyymm') ;
    v_monsrq12      :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-12),'yyyymm') ;
    v_P_DAY          :=to_char(to_date(P_DAY,'yyyymmdd')-1,'yyyymmdd') ;

    zdyf:=to_char(trunc(to_date(P_DAY,'yyyymmdd'),'mm'),'yyyymm');--统计月



---渠道产能评估体系 2022-1114
---渠道产能体系

----渠道基础目录  select * from shzc.zxb_qdcn_pgtx_qdtype for update ;

SQL_STRING:='insert into shzc.zxb_qdcn_pgtx_qdtype 
select * from shzc.zxb_qdcn_pgtx_qdtype_dr a 
where not exists ( select * from shzc.zxb_qdcn_pgtx_qdtype t where t.渠道编码=a.渠道编码
and t.userid=a.userid and t.in_time=a.in_time)
and 区县<>''区县''   ';
      EXECUTE IMMEDIATE (SQL_STRING);
      COMMIT;

----导入人员与时间字段
SQL_STRING:='update shzc.zxb_qdcn_pgtx_qdtype a set a.渠道编码=trim(a.渠道编码),a.userid=''xuce'',a.in_time=sysdate
where a.in_time is null  ';
      EXECUTE IMMEDIATE (SQL_STRING);
      COMMIT;
      
----经分报表处理
SQL_STRING:='update  shzc.zxb_qdcn_pgtx_qdtype a set a.in_time=sysdate ,a.渠道编码=trim(a.渠道编码)
where to_char(a.in_time,''hh24miss'')=''000000''  ';
      EXECUTE IMMEDIATE (SQL_STRING);
      COMMIT;

----月周日期对应表  select * from shzc.zxb_qdcn_pgtx_wwtype for update ;

-----------------------------------------------------代码开始-------------------------------------------------------
----渠道数据基础

zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sja'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sja as
select * from shzc.zxb_qdcn_pgtx_qdtype a
where a.in_time = (
select max(a.in_time) from shzc.zxb_qdcn_pgtx_qdtype a ) ';
      EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_wwtype_sja'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_wwtype_sja as
select * from 
(select a.*,row_number() over (partition by a.cycle,a.ww  order by a.start_day desc ) 排名
 from shzc.zxb_qdcn_pgtx_wwtype a
where a.cycle='''||v_monsr||''') a
where 排名=1 ';
      EXECUTE IMMEDIATE (SQL_STRING);

---拉新客户按照（等效折算+1）户计入（2017vip月汇总，放号） ---2017vip指标汇总_当月

zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_fhb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_fhb as
select a.registerorgid,a.status,a.statusdate,a.subsid,a.servnumber,a.prodid,a.prodname,
a.是否活跃,a.最后一次活跃时间,a.停机锁,a.是否新入网信用购,a.是否融合, a.是否业务融合, a.是否宽带融合,
a.jiazhi,a.关怀打折,a.折后价值,a.是否折后48,a.是否69以上,a.等效69,
to_number(to_char(a.createdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
 from  zhyw.shc_fanghao_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.registerorgid=b.渠道编码
and to_number(to_char(a.createdate,''dd'')) between c.start_day and c.end_day
and a.createdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')';
      EXECUTE IMMEDIATE (SQL_STRING);
      
zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_fhbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_fhbhz as
select a.registerorgid,count(distinct a.subsid) mon_subs,
count(distinct case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then a.subsid end) day_subs,
count(distinct case when a.本月第几周=1 then a.subsid end) ww1_subs,
count(distinct case when a.本月第几周=2 then a.subsid end) ww2_subs,
count(distinct case when a.本月第几周=3 then a.subsid end) ww3_subs,
count(distinct case when a.本月第几周=4 then a.subsid end) ww4_subs,
count(distinct case when a.本月第几周=5 then a.subsid end) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_fhb a 
where a.是否活跃=1
and a.status=''US10''
group by a.registerorgid';
      EXECUTE IMMEDIATE (SQL_STRING);


---5折算1户，原价算2户；高套餐按照69元等效折算；针对固移、信用购场景，按照1.2户折算（两个场景均符合，也按1.2户折算）。（放号明细，69等效）
---直接按照 69等效 这一列，如果为空取0

--- select   t1.*,折算  折算_69 from zhyw.shc_pk_third_fanghao_mx_month  t1 where to_char(createdate,'yyyymm')='202211'

zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dx69'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dx69 as
select a.registerorgid,a.status,a.statusdate,a.subsid,a.servnumber,a.jiazhi,a.停机锁,a.is_yxkd,a.是否拉新,a.折算 等效69,
to_number(to_char(a.createdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
 from  zhyw.shc_pk_third_fanghao_mx_month a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.registerorgid=b.渠道编码
and to_number(to_char(a.createdate,''dd'')) between c.start_day and c.end_day
and to_char(createdate,''yyyymm'')='''||v_monsr||'''
and a.createdate -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
      EXECUTE IMMEDIATE (SQL_STRING);


zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dx69hz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dx69hz as
select  a.registerorgid,
sum(nvl(a.等效69,0)) mon_dx69,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.等效69,0) end,0)) day_dx69,
sum(nvl(case when a.本月第几周=1 then nvl(a.等效69,0) end,0)) ww1_dx69,
sum(nvl(case when a.本月第几周=2 then nvl(a.等效69,0) end,0)) ww2_dx69,
sum(nvl(case when a.本月第几周=3 then nvl(a.等效69,0) end,0)) ww3_dx69,
sum(nvl(case when a.本月第几周=4 then nvl(a.等效69,0) end,0)) ww4_dx69,
sum(nvl(case when a.本月第几周=5 then nvl(a.等效69,0) end,0)) ww5_dx69
from shzc.zxb_qdcn_pgtx_qdtype_dx69 a 
group by a.registerorgid ';
      EXECUTE IMMEDIATE (SQL_STRING);

----小合约+信用购_代办（2017vip月汇总，小合约+信用购_代办）

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_zdhy'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_zdhy as
 select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,a.备用字段1,a.oid,
 to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
 from zhyw.report_mx_month_'||v_monsr||'_new a ,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
 and a.prod_type=''终端合约''
 and 备用字段1 in (''新入网信用购'',''存量换机优享'',''家庭合户信用购'',''权益信用购机'',''小合约'')
 and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
 and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);


 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_zdhyhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_zdhyhz as
select a.orgid registerorgid,count(distinct a.subsid||a.oid||a.prod_type) mon_subs,
count(distinct case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then a.subsid||a.oid||a.prod_type end) day_subs,
count(distinct case when a.本月第几周=1 then a.subsid||a.oid||a.prod_type end) ww1_subs,
count(distinct case when a.本月第几周=2 then a.subsid||a.oid||a.prod_type end) ww2_subs,
count(distinct case when a.本月第几周=3 then a.subsid||a.oid||a.prod_type end) ww3_subs,
count(distinct case when a.本月第几周=4 then a.subsid||a.oid||a.prod_type end) ww4_subs,
count(distinct case when a.本月第几周=5 then a.subsid||a.oid||a.prod_type end) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_zdhy a 
group by a.orgid ';
      EXECUTE IMMEDIATE (SQL_STRING);

---升档套包
---模组升档合约新年包 + 新模组升级 + 跨升档融权益礼包*2 + 不限量升档融权益 + 低端阶梯打折 + 小颗粒升档业务 *0.5+ 流量年包_5g （2017vip月汇总，小合约+信用购_代办）

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sdtb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sdtb as
 select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,a.个性化 ,a.prodid,a.oid,
 nvl(case when prod_type=''跨升档融权益礼包''  and prodid<> ''PROD20220715766.999'' then 2
          when prod_type=''小颗粒升档业务''   then 0.5 end,1) 折算系数,
 to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
 from zhyw.report_mx_month_'||v_monsr||'_new a ,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
 and (a.prod_type in (''模组升档合约新年包'',''跨升档融权益礼包'',''低端阶梯打折'',''小颗粒升档业务'',''流量年包_5g'')
 or (a.prod_type =''新模组升级''  and  个性化=''计入模组升级''))
 and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
 and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_sdtbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_sdtbhz as
select  a.orgid registerorgid,
sum(nvl(a.折算系数,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.折算系数,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.折算系数,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.折算系数,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.折算系数,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.折算系数,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.折算系数,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_sdtb a 
group by a.orgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);

---线盒新增 ------------


 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxz as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
 to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and (a.item like ''宽带新增%'' or a.item like ''魔百和新增%'')
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
 and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
 and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
      EXECUTE IMMEDIATE (SQL_STRING);
 

 
 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzhz as
select  a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_xhxz a 
group by a.recorgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);

----2022-1114宽带线盒不核减
 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzb as
 select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
 to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%核减%''
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
 and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
 and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
      EXECUTE IMMEDIATE (SQL_STRING);

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_xhxzbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_xhxzbhz as
select  a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_xhxzb a 
group by a.recorgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);


---智能组网
---考核智能组网新增发展量。（同竞赛口径）

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_znzw'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_znzw as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
 to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%路由器%'' 
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
 and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
 and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_znzwhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_znzwhz as
select  a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_znzw a 
group by a.recorgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);

--家庭安防  nvl(宽带权益包,0)+nvl(家庭安防,0)家庭安防
---考核家庭安防新增发展量（家庭安防客户开通宽带权益包、1元接盘包另计入1户）（同竞赛口径）

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_jtaf'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_jtaf as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
 to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and (a.item like ''%宽带权益包%'' or a.item like ''%家庭安防%'') 
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
 and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
 and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);
 
 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_jtafhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_jtafhz as
select  a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_jtaf a 
group by a.recorgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);


--点播年包
---考核点播年包新增发展量，按照10元等效（1元体验包按照0.5户计入）（同竞赛口径）

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dbnb'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dbnb as
select a.item,a.subsid,a.servnumber,a.recorgid,a.recdate,a.recoid,
a.startdate,a.enddate,a.ok_date,a.flow_m,a.flag,a.jf,a.flag_route_line,
 to_number(to_char(a.ok_date,''dd'')) create_day,nvl(c.ww,1) 本月第几周
From zhyw.zb_kd_pk_mx_subs_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.recorgid=b.渠道编码
and a.item like ''%点播年包%'' 
and to_number(to_char(a.ok_date,''dd'')) between c.start_day and c.end_day
 and to_char(a.ok_date,''yyyymm'')='''||v_monsr||'''
 and a.ok_date -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);

 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_dbnbhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_dbnbhz as
select  a.recorgid registerorgid,
sum(nvl(a.flag,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.flag,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.flag,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.flag,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.flag,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.flag,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.flag,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_dbnb a 
group by a.recorgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);

---权益+视频彩铃  ZXR_RACE_2022Q4_CEL



 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_qycl'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_qycl as
select a.prod_type,a.orgid,a.subsid,a.servnumber,a.recdate,a.prodname,a.privname,
a.prodid,a.oid,to_number(a.score) 折算系数,
 to_number(to_char(a.recdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周
from zhyw.zxr_race_2022q4_ticket_qy_'||v_monsr||' a,
shzc.zxb_qdcn_pgtx_qdtype_sja b,
 shzc.zxb_qdcn_pgtx_wwtype_sja c
where a.orgid=b.渠道编码
and to_number(to_char(a.recdate,''dd'')) between c.start_day and c.end_day
 and to_char(a.recdate,''yyyymm'')='''||v_monsr||'''
 and a.recdate -1 <to_date('''||P_DAY||''',''yyyymmdd'')  ';
      EXECUTE IMMEDIATE (SQL_STRING);
 
 
 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_qyclhz'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_qyclhz as
select  a.orgid registerorgid,
sum(nvl(a.折算系数,0)) mon_subs,
sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then nvl(a.折算系数,0) end,0)) day_subs,
sum(nvl(case when a.本月第几周=1 then nvl(a.折算系数,0) end,0)) ww1_subs,
sum(nvl(case when a.本月第几周=2 then nvl(a.折算系数,0) end,0)) ww2_subs,
sum(nvl(case when a.本月第几周=3 then nvl(a.折算系数,0) end,0)) ww3_subs,
sum(nvl(case when a.本月第几周=4 then nvl(a.折算系数,0) end,0)) ww4_subs,
sum(nvl(case when a.本月第几周=5 then nvl(a.折算系数,0) end,0)) ww5_subs
from shzc.zxb_qdcn_pgtx_qdtype_qycl a 
group by a.orgid  ';
      EXECUTE IMMEDIATE (SQL_STRING);



----数据组合
 zhyw.shc_drop_retable(upper('zxb_qdcn_pgtx_qdtype_hzbd'),'SHZC');
SQL_STRING:='create table shzc.zxb_qdcn_pgtx_qdtype_hzbd as
select '''||P_DAY||''' in_time,a.区县,a.是否改革,a.网格名称,a.渠道编码,a.渠道名称,a.大类,a.职位,a.渠道归属,
----月数据
nvl(fh.mon_subs,0) 放号_月数据,
nvl(dx.mon_dx69,0) 等效69_月数据,
nvl(zd.mon_subs,0) 信用购_月数据,
nvl(sd.mon_subs,0) 升档套包_月数据,
nvl(xh.mon_subs,0) 线盒新增_月数据,
--nvl(xhb.mon_subs,0) 线盒核减_月数据,
nvl(zn.mon_subs,0) 智能组网_月数据,
nvl(jt.mon_subs,0) 家庭安防_月数据,
nvl(db.mon_subs,0) 点播年包_月数据,
nvl(qy.mon_subs,0) 权益彩铃_月数据,

nvl(fh.mon_subs,0) +nvl(dx.mon_dx69,0) +nvl(zd.mon_subs,0) +nvl(sd.mon_subs,0) +
nvl(xh.mon_subs,0) -- -nvl(xhb.mon_subs,0) 
+nvl(zn.mon_subs,0) +nvl(jt.mon_subs,0) +
nvl(db.mon_subs,0) +nvl(qy.mon_subs,0) 合计_月数据,
----日数据
nvl(fh.day_subs,0) 放号_日数据,
nvl(dx.day_dx69,0) 等效69_日数据,
nvl(zd.day_subs,0) 信用购_日数据,
nvl(sd.day_subs,0) 升档套包_日数据,
nvl(xh.day_subs,0) 线盒新增_日数据,
--nvl(xhb.day_subs,0) 线盒核减_日数据,
nvl(zn.day_subs,0) 智能组网_日数据,
nvl(jt.day_subs,0) 家庭安防_日数据,
nvl(db.day_subs,0) 点播年包_日数据,
nvl(qy.day_subs,0) 权益彩铃_日数据,
nvl(fh.day_subs,0) +nvl(dx.day_dx69,0) +nvl(zd.day_subs,0) +nvl(sd.day_subs,0) +
nvl(xh.day_subs,0) -- -nvl(xhb.day_subs,0) 
+nvl(zn.day_subs,0) +nvl(jt.day_subs,0) +
nvl(db.day_subs,0) +nvl(qy.day_subs,0) 合计_日数据,
----第一周
nvl(fh.ww1_subs,0) 放号_第一周数据,
nvl(dx.ww1_dx69,0) 等效69_第一周数据,
nvl(zd.ww1_subs,0) 信用购_第一周数据,
nvl(sd.ww1_subs,0) 升档套包_第一周数据,
nvl(xh.ww1_subs,0) 线盒新增_第一周数据,
--nvl(xhb.ww1_subs,0) 线盒核减_第一周数据,
nvl(zn.ww1_subs,0) 智能组网_第一周数据,
nvl(jt.ww1_subs,0) 家庭安防_第一周数据,
nvl(db.ww1_subs,0) 点播年包_第一周数据,
nvl(qy.ww1_subs,0) 权益彩铃_第一周数据,
nvl(fh.ww1_subs,0) +nvl(dx.ww1_dx69,0) +nvl(zd.ww1_subs,0) +nvl(sd.ww1_subs,0) +
nvl(xh.ww1_subs,0) -- -nvl(xhb.ww1_subs,0) 
+nvl(zn.ww1_subs,0) +nvl(jt.ww1_subs,0) +
nvl(db.ww1_subs,0) +nvl(qy.ww1_subs,0) 合计_第一周数据,
----第二周
nvl(fh.ww2_subs,0) 放号_第二周数据,
nvl(dx.ww2_dx69,0) 等效69_第二周数据,
nvl(zd.ww2_subs,0) 信用购_第二周数据,
nvl(sd.ww2_subs,0) 升档套包_第二周数据,
nvl(xh.ww2_subs,0) 线盒新增_第二周数据,
--nvl(xhb.ww2_subs,0) 线盒核减_第二周数据,
nvl(zn.ww2_subs,0) 智能组网_第二周数据,
nvl(jt.ww2_subs,0) 家庭安防_第二周数据,
nvl(db.ww2_subs,0) 点播年包_第二周数据,
nvl(qy.ww2_subs,0) 权益彩铃_第二周数据,
nvl(fh.ww2_subs,0) +nvl(dx.ww2_dx69,0) +nvl(zd.ww2_subs,0) +nvl(sd.ww2_subs,0) +
nvl(xh.ww2_subs,0) -- -nvl(xhb.ww2_subs,0) 
+nvl(zn.ww2_subs,0) +nvl(jt.ww2_subs,0) +
nvl(db.ww2_subs,0) +nvl(qy.ww2_subs,0) 合计_第二周数据,
----第三周
nvl(fh.ww3_subs,0) 放号_第三周数据,
nvl(dx.ww3_dx69,0) 等效69_第三周数据,
nvl(zd.ww3_subs,0) 信用购_第三周数据,
nvl(sd.ww3_subs,0) 升档套包_第三周数据,
nvl(xh.ww3_subs,0) 线盒新增_第三周数据,
--nvl(xhb.ww3_subs,0) 线盒核减_第三周数据,
nvl(zn.ww3_subs,0) 智能组网_第三周数据,
nvl(jt.ww3_subs,0) 家庭安防_第三周数据,
nvl(db.ww3_subs,0) 点播年包_第三周数据,
nvl(qy.ww3_subs,0) 权益彩铃_第三周数据,
nvl(fh.ww3_subs,0) +nvl(dx.ww3_dx69,0) +nvl(zd.ww3_subs,0) +nvl(sd.ww3_subs,0) +
nvl(xh.ww3_subs,0) -- -nvl(xhb.ww3_subs,0) 
+nvl(zn.ww3_subs,0) +nvl(jt.ww3_subs,0) +
nvl(db.ww3_subs,0) +nvl(qy.ww3_subs,0) 合计_第三周数据,
----第四周
nvl(fh.ww4_subs,0) 放号_第四周数据,
nvl(dx.ww4_dx69,0) 等效69_第四周数据,
nvl(zd.ww4_subs,0) 信用购_第四周数据,
nvl(sd.ww4_subs,0) 升档套包_第四周数据,
nvl(xh.ww4_subs,0) 线盒新增_第四周数据,
--nvl(xhb.ww4_subs,0) 线盒核减_第四周数据,
nvl(zn.ww4_subs,0) 智能组网_第四周数据,
nvl(jt.ww4_subs,0) 家庭安防_第四周数据,
nvl(db.ww4_subs,0) 点播年包_第四周数据,
nvl(qy.ww4_subs,0) 权益彩铃_第四周数据,
nvl(fh.ww4_subs,0) +nvl(dx.ww4_dx69,0) +nvl(zd.ww4_subs,0) +nvl(sd.ww4_subs,0) +
nvl(xh.ww4_subs,0) -- -nvl(xhb.ww4_subs,0) 
+nvl(zn.ww4_subs,0) +nvl(jt.ww4_subs,0) +
nvl(db.ww4_subs,0) +nvl(qy.ww4_subs,0) 合计_第四周数据,
----第五周
nvl(fh.ww5_subs,0) 放号_第五周数据,
nvl(dx.ww5_dx69,0) 等效69_第五周数据,
nvl(zd.ww5_subs,0) 信用购_第五周数据,
nvl(sd.ww5_subs,0) 升档套包_第五周数据,
nvl(xh.ww5_subs,0) 线盒新增_第五周数据,
--nvl(xhb.ww5_subs,0) 线盒核减_第五周数据,
nvl(zn.ww5_subs,0) 智能组网_第五周数据,
nvl(jt.ww5_subs,0) 家庭安防_第五周数据,
nvl(db.ww5_subs,0) 点播年包_第五周数据,
nvl(qy.ww5_subs,0) 权益彩铃_第五周数据,
nvl(fh.ww5_subs,0) +nvl(dx.ww5_dx69,0) +nvl(zd.ww5_subs,0) +nvl(sd.ww5_subs,0) +
nvl(xh.ww5_subs,0) -- -nvl(xhb.ww5_subs,0) 
+nvl(zn.ww5_subs,0) +nvl(jt.ww5_subs,0) +
nvl(db.ww5_subs,0) +nvl(qy.ww5_subs,0) 合计_第五周数据
       
       
from shzc.zxb_qdcn_pgtx_qdtype_sja a,
shzc.zxb_qdcn_pgtx_qdtype_fhbhz fh,---放号
shzc.zxb_qdcn_pgtx_qdtype_dx69hz dx,---等效69
shzc.zxb_qdcn_pgtx_qdtype_zdhyhz zd,---信用购
shzc.zxb_qdcn_pgtx_qdtype_sdtbhz sd,---升档套包
shzc.zxb_qdcn_pgtx_qdtype_xhxzhz xh,---线盒新增
--shzc.zxb_qdcn_pgtx_qdtype_xhxzbhz xhb,---线盒新增 核减
shzc.zxb_qdcn_pgtx_qdtype_znzwhz zn,---智能组网
shzc.zxb_qdcn_pgtx_qdtype_jtafhz jt,---家庭安防
shzc.zxb_qdcn_pgtx_qdtype_dbnbhz db,---点播年包
shzc.zxb_qdcn_pgtx_qdtype_qyclhz qy  ---权益彩铃
where a.渠道编码=fh.registerorgid(+)
and a.渠道编码=dx.registerorgid(+)
and a.渠道编码=zd.registerorgid(+)

and a.渠道编码=sd.registerorgid(+)
and a.渠道编码=xh.registerorgid(+)
--and a.渠道编码=xhb.registerorgid(+)
and a.渠道编码=zn.registerorgid(+)
and a.渠道编码=jt.registerorgid(+)
and a.渠道编码=db.registerorgid(+)
and a.渠道编码=qy.registerorgid(+) ';
      EXECUTE IMMEDIATE (SQL_STRING);


---每天渠道数据存档
SQL_STRING:='delete shzc.zxb_qdcn_pgtx_qdtype_bdcd  where in_time = '''||v_monsr||''' ';
      EXECUTE IMMEDIATE (SQL_STRING);
      COMMIT;
      
SQL_STRING:=' insert into shzc.zxb_qdcn_pgtx_qdtype_bdcd 
select a.* from shzc.zxb_qdcn_pgtx_qdtype_hzbd a  ';
      EXECUTE IMMEDIATE (SQL_STRING);
      COMMIT;  

  exception

  --增加异常处理　提高代码健壮性
  when others then
    shc_add_exception('xxxxx', 'c0000972'); --异常处理
end;