20230215_数据库过程_渠道重点业务过程

–五福红包报表支撑需求 2022-1227

–本月省公司上线了个社会渠道达量奖励活动叫做五福红包。分别涉及五项业务。聚焦放号、升档、线盒、千兆宽带、终端信用购五项重点业务发展。业务统计口径均为12月起受理且生效。
– 达量完成口径为超出基本任务之后部分进行奖励。基本任务值省公司统一下达。已经在表内注明。
– 具体业务统计口径我已经在表内详细标注。您看下有问题再沟通。整个报表建议按照我得框架表出来最好。

---- 任务量
---- 账期 渠道 放号红包 升档红包 宽带红包 千兆红包 终端信用购红包
– create table shzc.zxb_qd_wfhb_qdtype
–(账期 varchar2(18),渠道编码 varchar2(80),放号红包 number,升档红包 number,宽带红包 number,千兆红包 number,终端信用购红包 number,userid varchar2(18),in_time date );

–select * from shzc.zxb_qd_wfhb_qdtype for update ;

SQL_STRING:=‘update shzc.zxb_qd_wfhb_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_qd_wfhb_qdtype a set a.in_time=sysdate ,a.渠道编码=trim(a.渠道编码)
where to_char(a.in_time,’‘hh24miss’‘)=’‘000000’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;

—提取月任务的最新导入数据
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_sja’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_sja as
select * from shzc.zxb_qd_wfhb_qdtype a
where a.in_time = (
select max(a.in_time) from shzc.zxb_qd_wfhb_qdtype a where 账期=’‘’||v_monsr||‘’’ )
and a.账期=‘’‘||v_monsr||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);

----放号情况
----取12月放号发展量，剔除副卡、动感地带校园卡及非正使用状态发展量
----只取折后48+套餐，且需按照放号时间排列出超出底线值后的发展量

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_fhb’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_fhb as
select a.servnumber,a.subsid,a.registerorgid,a.createdate,to_char(createdate,’‘yyyymm’‘) cycle,a.status,a.statusdate,
a.prodid,a.prodname,a.jiazhi,a.五大渠道,a.是否活跃,a.是否拉新,
nvl(a.是否折后48,’‘0’‘) 是否折后48,a.最后一次活跃时间,a.停机锁,a.是否新入网信用购,a.是否融合, a.是否业务融合, a.是否宽带融合,
a.关怀打折,a.折后价值,a.预存合约,a.是否开通宽带,a.直销员电话,a.recopid,a.oid,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,b.放号红包
from zhyw.shc_fanghao_’||v_monsr||’ a,
shzc.zxb_qd_wfhb_qdtype_sja b
where a.registerorgid=b.渠道编码
and to_char(a.createdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.createdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_fhc’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_fhc as
select a.*,row_number() over (partition by a.registerorgid order by a.createdate ) 排名
from shzc.zxb_qd_wfhb_qdtype_fhb a
where a.status=’‘US10’’
and a.prodname not like ‘’%副卡%‘’ and a.prodname not like ‘’%动感地带校园卡%‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);

  ----每月留存
  zhyw.shc_drop_retable(upper('zxb_qd_wfhb_qdtype_fhc_'||v_monsr),'SHZC');
  SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_fhc_'||v_monsr||' as
  select * from  shzc.zxb_qd_wfhb_qdtype_fhc ';
  EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_fhjg’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_fhjg as
select a.registerorgid,max(a.放号红包) 放号红包任务,count(distinct a.subsid) 放号数量,
count(distinct case when a.排名>a.放号红包 and a.是否折后48=’‘1’’ then a.subsid end ) 超额完成且48放号
from shzc.zxb_qd_wfhb_qdtype_fhc a
group by a.registerorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

----升档红包
----取12月升档受理且生效的升档业务量

—受理时间

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_sda’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_sda as
select a.prod_type,a.servnumber,a.subsid,a.recdate,a.prodname,a.privname,
to_date(a.startdate,’‘yyyymmdd hh24:mi:ss’‘) startdate,to_date(a.enddate,’‘yyyymmdd hh24:mi:ss’‘) enddate,
a.业务类型,a.orgid,a.recopid,a.oid
from zhyw.REPORT_MX_MONTH_’||v_monsr||’ a ,
shzc.zxb_qd_wfhb_qdtype_sja b
where (prod_type in (‘‘模组升档合约新年包’’,‘‘跨升档融权益礼包’’,‘‘特殊产品专项升档’’,
‘‘低端阶梯打折’’)or (prod_type like ‘’%新模组升级%‘’ and 个性化=‘‘计入模组升级’’) )
and a.orgid=b.渠道编码
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

----本地升档存档，前期数据补充
SQL_STRING:='insert into shzc.zxb_qd_wfhb_qdtype_sdb
select * from shzc.zxb_qd_wfhb_qdtype_sda a
where a.oid not in ( select b.oid from shzc.zxb_qd_wfhb_qdtype_sdb b) ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;

----按照生效时间展示业务量
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_sdc’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_sdc as
select a.*,b.升档红包,row_number() over (partition by a.orgid order by a.startdate||a.oid ) 排名
from (select distinct a.prod_type,a.subsid,a.servnumber,a.recdate,a.startdate,a.orgid,a.oid
from shzc.zxb_qd_wfhb_qdtype_sdb a ) a,
shzc.zxb_qd_wfhb_qdtype_sja b
where a.orgid=b.渠道编码
and to_char(a.startdate,’‘yyyymm’‘)=’‘’||v_monsr||‘’’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

  ----每月留存
  zhyw.shc_drop_retable(upper('zxb_qd_wfhb_qdtype_sdc_'||v_monsr),'SHZC');
  SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_sdc_'||v_monsr||' as
  select * from  shzc.zxb_qd_wfhb_qdtype_sdc ';
  EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_sdjg’),‘SHZC’);
SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_sdjg as
select a.orgid registerorgid,max(a.升档红包) 升档红包任务,count(distinct a.prod_type||a.oid) 升档数量,
count(distinct case when a.排名>a.升档红包 then a.startdate||a.oid end ) 超额完成数量
from shzc.zxb_qd_wfhb_qdtype_sdc a
group by a.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);

— 宽带红包
— 取12月受理且生效的线盒同装发展量
— 只取搭载智家或生态产品的线盒同装
select max(TABLE_NAME) into V_TAB1 from all_tables a where a.TABLE_NAME like upper(‘zhai_yxkd_priv_subs%’) and length(a.TABLE_NAME)=26;
select max(table_name) into V_TAB2 from all_tables where table_name like upper(‘%zb_fam_subs%’);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kda’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_kda as
select distinct a.所属区县名称,a1.unit_id,a1.unit_name,a.PRODID_FW,
a.grid_name, a.subsid,a.servnumber,zhyw.getprodname(a.prodid_fw) prodid_fwm, a.leixing, a.prodnamezt,
a.startdate,a.enddate,a.所属乡镇_街道名称, a.所属小区_学校_自然村,
a.代理商,a.带宽限制,a.网络设备情况,replace(a.联系人姓名,’‘1’‘,’‘I’') 联系人姓名,
a.lx_priv, a.PRIVNAME_PRIV, a.PRIV_START, a.PRIV_END,
a.lx_rh, a.PRIVNAME_RH, a.RH_START, a.RH_END,
a.PRIVNAME_YC, a.YC_START, a.YC_END,
a.applyoperidfw, a.recorgid, k12.orgname,a.代理商a, a.recdate , a.canceloperidfw,
c.privname, c.privname_startdate, c.privname_enddate,
a.FEE FEEa, a.RECEIVEDATE RECEIVEDATEa, a.dictname dictnamea, a.statusdate statusdatea,
c.FEE FEEc, c.RECEIVEDATE RECEIVEDATEc, c.dictname dictnamec, c.statusdate statusdatec,
a.ont类型,
a.ont_privname, f1.dictname, g.反馈营销片区, a.客户经理,

case when k1.f_servnumber is not null then ‘‘搭载副卡’’ end 搭载副卡,k1.f_subsid,k1.recdate recdatek1,k1.recopid,
k6.privname privnamek6,k6.jiazhi,
k7.privname privnamek7, k7.xf_privname,k7.xf_start xf_start,k7.xf_end xf_end,k7.xf_operid ,
case when k8.subsid is not null then ‘‘可开副卡’’ end 可开副卡,
k9.item,k9.dc,
k10.flow_m,
case when k11.status=‘‘stcmNml’’ then ‘‘开通家庭网’’ end flag_fam,
k13.cancel_reason,
k14.privname 宽带资费,
k15.privname 宽带优惠,
case when c.subsid is not null and nvl(c.enddate,sysdate+10000)>sysdate then ‘‘1’’ end flag_htv,
case when k16.in_subsid is not null then ‘‘同址换号’’ end flag_huanhao ,
case when k17.user_id is not null then ‘‘c类宽带’’ end c类 ,
k12.unit_name unit_namek12,
case when k18.AFFECTTYPE=2 then ‘‘当月生效’’ when k18.AFFECTTYPE=3 then ‘‘次月生效’’ else to_char(k18.affecttype,0) end 生效月份,
zhixiao_hm ,k19.zhixiao_name,a.applyoid

from zhyw.‘||V_TAB1||’ a,
zhyw.qcy_tmp_unit_grid a1,
zhyw.zb_yxkd_county b ,
zhyw.zhai_htv_subsxin c ,
ZHYW.zhht_kd_om_order1 F, (select * from zhyw.dict_item m where groupid=‘‘US’’) f1, ZHYW.ZHAI_PQ_KD_DIM g,
zhyw.zb_wnfk_subs k1,
zhyw.zb_subs_tc_jiazhi k6,
zhyw.zb_yxkd_xufei_subs k7,
zhyw.wanneng_fk_mb_mx k8 ,
zhyw.zb_kd_aijia_qunzu_subs k9,
zhyw.zb_yxkd_flow_‘||v_monsr||’ k10,
zhyw.‘||V_TAB2||’ k11,
zhyw.shc_organization k12,
zhyw.zb_yxkd_cancel_reason_subs k13,
(select item,subsid,privid,privname,a.startdate,a.enddate
from zhyw.qcy_tmp_kd_prv_2 a
where item=‘‘产品资费’’ and
nvl(enddate,sysdate+10000)>sysdate-1
) k14,
(select subsid,privid,privname,startdate,enddate
from zhyw.qcy_tmp_kd_photo_2
where nvl(enddate,sysdate+10000)>sysdate-1
) k15, —宽带优惠到期
zhyw.ZB_YXKD_HUANHAO_SUBS k16,
zibo.d_notbroadband_user_ds k17,
zhyw.SHC_SUBS_PRODUCT k18,
zhyw.cs_order_contact k19,
shzc.zxb_qd_wfhb_qdtype_sja t

where a.所属区县=b.所属区县(+)
and a.grid_id=a1.grid_id(+)
and a.subsid=c.subsid(+)
and a.servnumber=F.acc_num(+)
AND a.status=f1.dictid(+)
and nvl(a.订单状态,‘‘0’’)<>‘‘已撤单’’
and nvl(a.lx_priv,‘‘0’’)<>‘‘铁通天’’
and a.所属小区_学校_自然村=g.boss小区编码(+)
and a.subsid=k1.main_subsid(+) and
a.subsid=k6.subsid(+) and
a.subsid=k7.subsid(+) and
a.subsid=k8.subsid(+) and
a.subsid=k9.subsid(+) and
a.subsid=k10.user_id(+) and
a.subsid=k11.subsid(+) and
a.recorgid=k12.orgid(+) and
a.subsid=k13.subsid(+) and
a.subsid=k14.subsid(+) and
a.subsid=k15.subsid(+) and
a.subsid=k16.in_subsid(+) and
a.subsid=k17.user_id(+) and
a.subsid=k18.subsid(+) and
a.prodid_fw=k18.prodid(+) and
a.applyoid=k19.oid(+)
and a.recorgid=t.渠道编码
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:='insert into shzc.zxb_qd_wfhb_qdtype_kdb
select a.prodid_fw,a.subsid,a.servnumber,a.recdate,a.leixing,a.startdate,a.enddate,a.带宽限制,
a.applyoperidfw,a.applyoid,a.recorgid,a.ont类型,a.jiazhi,a.item,a.dc,a.flag_fam,a.宽带资费,a.flag_htv
from shzc.zxb_qd_wfhb_qdtype_kda a
where a.applyoid not in (select b.applyoid from shzc.zxb_qd_wfhb_qdtype_kdb b ) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

– 路由器 379
– 家庭安防 25
–点播 zhyw.zb_htv_spservice_subs

----生态业务
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kdc’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_kdc as
select a.* from zhyw.zb_yxkd_sanjiantao_subs a,
(select distinct b.subsid from shzc.zxb_qd_wfhb_qdtype_kdb b ) b
where a.subsid=b.subsid
and a.item in (’‘路由器’‘,’‘家庭安防’') ';
EXECUTE IMMEDIATE (SQL_STRING);

—点播业务
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kdd’),‘SHZC’);
SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_kdd as
select a.* from zhyw.zb_htv_spservice_subs a,
(select distinct b.subsid from shzc.zxb_qd_wfhb_qdtype_kdb b ) b
where a.subsid=b.subsid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kde’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_kde as
select a.subsid,a.item,count() cs
from shzc.zxb_qd_wfhb_qdtype_kdc a
where a.recdate<sysdate
and nvl(a.enddate,sysdate+9999)>to_date(‘’‘||v_monsr||’‘’,‘‘yyyymm’’)
group by a.subsid,a.item
union all
select a.subsid,‘‘点播’’ item,count() cs
from shzc.zxb_qd_wfhb_qdtype_kdd a
where a.recdate<sysdate
and nvl(a.enddate,sysdate+9999)>to_date(’‘’||v_monsr||‘’‘,’‘yyyymm’')
group by a.subsid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kdf’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_kdf as
select a.subsid,
max(nvl(case when a.item=’‘路由器’’ then a.cs end,0)) 路由器,
max(nvl(case when a.item=‘‘家庭安防’’ then a.cs end,0)) 家庭安防,
max(nvl(case when a.item=‘‘点播’’ then a.cs end,0)) 点播
from shzc.zxb_qd_wfhb_qdtype_kde a
group by a.subsid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kdg’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_kdg as
select a.*,
nvl(b.路由器,’‘0’‘)路由器,nvl(b.家庭安防,’‘0’‘) 家庭安防,nvl(b.点播,’‘0’‘) 点播,t.宽带红包,
row_number() over (partition by a.recorgid order by a.startdate ) 排名
from (select distinct a.subsid,a.servnumber,a.recdate,a.startdate,a.enddate,a.applyoid,a.recorgid,nvl(a.flag_htv,’‘0’‘) flag_htv
from shzc.zxb_qd_wfhb_qdtype_kdb a ) a,
shzc.zxb_qd_wfhb_qdtype_kdf b,
shzc.zxb_qd_wfhb_qdtype_sja t
where a.subsid=b.subsid(+)
and a.recorgid=t.渠道编码
and nvl(a.flag_htv,’‘0’‘)=’‘1’’
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

  ----每月留存
  zhyw.shc_drop_retable(upper('zxb_qd_wfhb_qdtype_kdg_'||v_monsr),'SHZC');
  SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_kdg_'||v_monsr||' as
  select * from  shzc.zxb_qd_wfhb_qdtype_kdg ';
  EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_kdjg’),‘SHZC’);
SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_kdjg as
select a.recorgid registerorgid,max(a.宽带红包) 宽带红包任务,count(distinct a.applyoid) 宽带数量,
count(distinct case when a.排名>a.宽带红包 and a.路由器+a.家庭安防+a.点播>0 then a.applyoid end ) 超额完成数量
from shzc.zxb_qd_wfhb_qdtype_kdg a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

---- 千兆红包
— 取12月受理且生效的千兆提速包发展量

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_qza’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_qza as
select distinct rec_qx,a.item,b.grid_id,b.grid_name, a.servnumber,a.subsid, a.unit_name,
a.pkg_prod,a.pkg_name, a.privid,a.privname, b3.dc,b3.note, a.recdate,
a.startdate,a.enddate,c.orgname,a.applyoperid, b1.notes,
a.是否自办渠道,a.渠道经理,a.recorgid,
tsbxy_priv,tsbxy_start,tsbxy_end,
kd_start,kd_end,
a.createdate ,a.statusname, a.statusdate,a.tc_privname,a.jiazhi,
a.zone_id,a.zone_name,
case when b2.subsid is not null then ‘‘有优惠’’ end flag,
c.unit_name unit_namec,a.flag_midhigh,zhixiao_hm,b4.zhixiao_name ,a.applyoid
from zhyw.zb_yxkd_sanjiantao_subs a,
zhyw.zb_yyy_unit b,
zhyw.shc_organization c,
zhyw.shc_reception b1,
( select *from zhyw.zb_yxkd_priv_monitor_subs
where send_priv=’‘PRIV20210608985.999’’ and
nvl(send_end,sysdate+36)>send_start and
nvl(send_end,sysdate+36)>sysdate-1) b2,
qcy.qcy_yxkd_sanjiantao_priv b3,
zhyw.cs_order_contact b4 ,
shzc.zxb_qd_wfhb_qdtype_sja t

      where a.applyoperid=b.operid(+) and
      a.recorgid=c.orgid(+) and
        a.applyoid=b1.oid(+) and
         a.subsid=b2.subsid(+) and
         a.privid=b3.privid(+) and
         a.applyoid=b4.oid(+) 
        and a.recorgid=t.渠道编码
        and a.item=''提速包''
        and to_char(a.recdate,''yyyymm'')='''||v_monsr||'''
        and a.recdate-1 <to_date('''||P_DAY||''',''yyyymmdd'') ';
  EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:='insert into shzc.zxb_qd_wfhb_qdtype_qzb
select * from shzc.zxb_qd_wfhb_qdtype_qza a
where a.applyoid not in (select b.applyoid from shzc.zxb_qd_wfhb_qdtype_qzb b ) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_qzc’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_qzc as
select a.*,t.千兆红包,row_number() over (partition by a.recorgid order by a.startdate ) 排名
from (select distinct a.recdate,a.startdate,a.enddate,a.subsid,a.servnumber,a.recorgid,a.applyoid
from shzc.zxb_qd_wfhb_qdtype_qzb a ) a,
shzc.zxb_qd_wfhb_qdtype_sja t
where a.recorgid=t.渠道编码
and to_char(a.startdate,’‘yyyymm’‘)=’‘’||v_monsr||‘’’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

  ----每月留存
  zhyw.shc_drop_retable(upper('zxb_qd_wfhb_qdtype_qzc_'||v_monsr),'SHZC');
  SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_qzc_'||v_monsr||' as
  select * from  shzc.zxb_qd_wfhb_qdtype_qzc ';
  EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_qzjg’),‘SHZC’);
SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_qzjg as
select a.recorgid registerorgid,max(a.千兆红包) 千兆红包任务,count(distinct a.applyoid) 千兆数量,
count(distinct case when a.排名>a.千兆红包 then a.applyoid end ) 超额完成数量
from shzc.zxb_qd_wfhb_qdtype_qzc a
group by a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

—终端信用购红包
–取12月受理且生效的终端和分期发展量
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_zda’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_zda as
select a.imei,a.op_time,a.sale_channel_id,a.res_type_id,a.term_brand,a.term_code,
a.prodname,a.privname,a.终端类型,a.imei_tel,a.imei_subsid,a.recoid,a.recdate,a.prod_startdate,a.prod_enddate,
a.active_prodname,a.dc_privname,a.imei首次激活时间,a.机卡分离,a.是否信用购,’‘0’’ 直销员电话,a.工号
from zhyw.LSHT_zibo_term_sale’||v_monsr||’ a,
shzc.zxb_qd_wfhb_qdtype_sja b
where a.sale_channel_id=b.渠道编码
and a.是否信用购=‘‘1’’
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:='insert into shzc.zxb_qd_wfhb_qdtype_zdb
select * from shzc.zxb_qd_wfhb_qdtype_zda a
where a.recoid not in (select b.recoid from shzc.zxb_qd_wfhb_qdtype_zdb b ) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_zdc’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_zdc as
select a.*,t.终端信用购红包,row_number() over (partition by a.sale_channel_id order by a.prod_startdate ) 排名
from
shzc.zxb_qd_wfhb_qdtype_zdb a,
shzc.zxb_qd_wfhb_qdtype_sja t
where a.sale_channel_id=t.渠道编码
and to_char(a.prod_startdate,’‘yyyymm’‘)=’‘’||v_monsr||‘’’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)
and a.prod_startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

----每月留存
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_zdc_’||v_monsr),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_zdc_’||v_monsr||’ as
select * from shzc.zxb_qd_wfhb_qdtype_zdc ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_zdjg’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_zdjg as
select a.sale_channel_id registerorgid,max(a.终端信用购红包) 终端红包任务,count(distinct a.recoid) 终端数量,
count(distinct case when a.排名>a.终端信用购红包 then a.recoid end ) 超额完成数量
from shzc.zxb_qd_wfhb_qdtype_zdc a
group by a.sale_channel_id’;
EXECUTE IMMEDIATE (SQL_STRING);

--------组合
zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_lszh’),‘SHZC’);
SQL_STRING:='create table shzc.zxb_qd_wfhb_qdtype_lszh as
select a.账期,a.渠道编码,nvl(b.orgname,c.orgname) orgname,b.片区归属区县,b.status,b.unit_id,b.unit_name,b.微网格编码,b.微网格名称,
b.是否自办渠道, b.单位类型, b.渠道类型, b.渠道类别,b.渠道经理工号,b.渠道经理,
—底线目标进度 超出完成 完成档次 符合奖励条件发展量 奖励预估
a.放号红包,nvl(f1.放号数量,0) 放号数量,round(nvl(f1.放号数量,0)/a.放号红包100,2) 放号目标进度,nvl(f1.放号数量,0)-a.放号红包 放号超出完成,nvl(f1.超额完成且48放号,0) 放号符合奖励条件发展量,
a.升档红包,nvl(s1.升档数量,0) 升档数量,round(nvl(s1.升档数量,0)/a.升档红包100,2) 升档目标进度,nvl(s1.升档数量,0)-a.升档红包 升档超出完成,nvl(s1.超额完成数量,0) 升档符合奖励条件发展量,
a.宽带红包,nvl(k1.宽带数量,0) 宽带数量,round(nvl(k1.宽带数量,0)/a.宽带红包100,2) 宽带目标进度,nvl(k1.宽带数量,0)-a.宽带红包 宽带超出完成,nvl(k1.超额完成数量,0) 宽带符合奖励条件发展量,
a.千兆红包,nvl(q1.千兆数量,0) 千兆数量,round(nvl(q1.千兆数量,0)/a.千兆红包100,2) 千兆目标进度,nvl(q1.千兆数量,0)-a.千兆红包 千兆超出完成,nvl(q1.超额完成数量,0) 千兆符合奖励条件发展量,
a.终端信用购红包,nvl(z1.终端数量,0) 终端数量,round(nvl(z1.终端数量,0)/a.终端信用购红包*100,2) 终端目标进度,nvl(z1.终端数量,0)-a.终端信用购红包 终端超出完成,nvl(z1.超额完成数量,0) 终端符合奖励条件发展量

from shzc.zxb_qd_wfhb_qdtype_sja a,
zhyw.shc_organization_‘||v_monsr||’ b,
tbcs.organization@bcv c,
shzc.zxb_qd_wfhb_qdtype_fhjg f1,
shzc.zxb_qd_wfhb_qdtype_sdjg s1,
shzc.zxb_qd_wfhb_qdtype_kdjg k1,
shzc.zxb_qd_wfhb_qdtype_qzjg q1,
shzc.zxb_qd_wfhb_qdtype_zdjg z1
where a.渠道编码=b.orgid(+)
and a.渠道编码=c.orgid(+)
and a.渠道编码=f1.registerorgid(+)
and a.渠道编码=s1.registerorgid(+)
and a.渠道编码=k1.registerorgid(+)
and a.渠道编码=q1.registerorgid(+)
and a.渠道编码=z1.registerorgid(+)';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘zxb_qd_wfhb_qdtype_lszha’),‘SHZC’);
SQL_STRING:=‘create table shzc.zxb_qd_wfhb_qdtype_lszha as
select
a.账期,’‘’||P_DAY||‘’’ in_time,
a.渠道编码,
a.orgname,
a.片区归属区县,
a.status,
a.unit_id,
a.unit_name,
a.微网格编码,
a.微网格名称,
a.是否自办渠道,
a.单位类型,
a.渠道类型,
a.渠道类别,
a.渠道经理工号,
a.渠道经理,
-----数据情况
a.放号红包,
a.放号数量,
a.放号目标进度,
nvl(case when a.放号超出完成 <0 then 0 end,a.放号超出完成) 放号超出完成,
nvl(case when a.放号超出完成 between 1 and 5 then 1
when a.放号超出完成 between 6 and 10 then 2
when a.放号超出完成 between 11 and 9999 then 3 end,0) 放号完成档次,
a.放号符合奖励条件发展量,
a.放号符合奖励条件发展量 *
nvl(case when a.放号超出完成 between 1 and 5 then 30
when a.放号超出完成 between 6 and 10 then 40
when a.放号超出完成 between 11 and 9999 then 50 end,0) 放号奖励预估,

a.升档红包,
a.升档数量,
a.升档目标进度,
nvl(case when a.升档超出完成 <0 then 0 end,a.升档超出完成) 升档超出完成,
nvl(case when a.升档超出完成 between 1 and 5 then 1
when a.升档超出完成 between 6 and 10 then 2
when a.升档超出完成 between 11 and 9999 then 3 end,0) 升档完成档次,
a.升档符合奖励条件发展量,
a.升档符合奖励条件发展量 *
nvl(case when a.升档超出完成 between 1 and 5 then 15
when a.升档超出完成 between 6 and 10 then 20
when a.升档超出完成 between 11 and 9999 then 25 end,0) 升档奖励预估,

a.宽带红包,
a.宽带数量,
a.宽带目标进度,
nvl(case when a.宽带超出完成 <0 then 0 end,a.宽带超出完成) 宽带超出完成,
nvl(case when a.宽带超出完成 between 1 and 5 then 1
when a.宽带超出完成 between 6 and 10 then 2
when a.宽带超出完成 between 11 and 9999 then 3 end,0) 宽带完成档次,
a.宽带符合奖励条件发展量,
a.宽带符合奖励条件发展量 *
nvl(case when a.宽带超出完成 between 1 and 5 then 50
when a.宽带超出完成 between 6 and 10 then 60
when a.宽带超出完成 between 11 and 9999 then 70 end,0) 宽带奖励预估,
a.千兆红包,
a.千兆数量,
a.千兆目标进度,
nvl(case when a.千兆超出完成 <0 then 0 end,a.千兆超出完成) 千兆超出完成,
nvl(case when a.千兆超出完成 between 1 and 5 then 1
when a.千兆超出完成 between 6 and 10 then 2
when a.千兆超出完成 between 11 and 9999 then 3 end,0) 千兆完成档次,
a.千兆符合奖励条件发展量,
a.千兆符合奖励条件发展量 *
nvl(case when a.千兆超出完成 between 1 and 5 then 15
when a.千兆超出完成 between 6 and 10 then 20
when a.千兆超出完成 between 11 and 9999 then 25 end,0) 千兆奖励预估,
a.终端信用购红包,
a.终端数量,
a.终端目标进度,
nvl(case when a.终端超出完成 <0 then 0 end,a.终端超出完成) 终端超出完成,
nvl(case when a.终端超出完成 between 1 and 5 then 1
when a.终端超出完成 between 6 and 10 then 2
when a.终端超出完成 between 11 and 9999 then 3 end,0) 终端完成档次,
a.终端符合奖励条件发展量,
a.终端符合奖励条件发展量 *
nvl(case when a.终端超出完成 between 1 and 5 then 40
when a.终端超出完成 between 6 and 10 then 50
when a.终端超出完成 between 11 and 9999 then 60 end,0) 终端奖励预估

from shzc.zxb_qd_wfhb_qdtype_lszh a’;
EXECUTE IMMEDIATE (SQL_STRING);

–create table shzc.zxb_qd_wfhb_qdtype_qdhzbd as
–select * from shzc.zxb_qd_wfhb_qdtype_lszha

—每天渠道数据存档
SQL_STRING:=‘delete shzc.zxb_qd_wfhb_qdtype_qdhzbd where in_time = ‘’’||P_DAY||‘’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;

SQL_STRING:=’ insert into shzc.zxb_qd_wfhb_qdtype_qdhzbd
select * from shzc.zxb_qd_wfhb_qdtype_lszha ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;