20230215_数据库过程_渠道业务计算过程

—20221209 渠道产能

—自有人员工号表 shzc.xc_qdcn_pgtx_opertype
—select * from shzc.xc_qdcn_pgtx_opertype for update ;

—渠道基础目录 shzc.xc_qdcn_pgtx_qdtype
—select * from shzc.xc_qdcn_pgtx_qdtype for update ;

SQL_STRING:=‘update shzc.xc_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.xc_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;

—渠道编码取最后一次导入数据 作为基础目录
zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_sja’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_sja as
select * from shzc.xc_qdcn_pgtx_qdtype a
where a.in_time = (
select max(a.in_time) from shzc.xc_qdcn_pgtx_qdtype a where in_time<to_date(’‘’||v_monsrh||‘’‘,’‘yyyymm’‘) )’;
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);

-----新入网基础数据

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_fhb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_fhb as
select a.servnumber,a.subsid,a.registerorgid,b.大类,a.createdate,to_char(createdate,’‘yyyymm’‘) cycle,a.status,a.statusdate,
a.prodid,a.prodname,a.jiazhi,a.五大渠道,a.是否活跃,a.是否拉新,
a.是否折后48,a.最后一次活跃时间,a.停机锁,a.是否新入网信用购,a.是否融合, a.是否业务融合, a.是否宽带融合,
a.关怀打折,a.折后价值,a.是否参与终端合约,a.话费合约_12月竞赛 话费合约竞赛,a.预存合约,a.是否开通宽带,
a.是否搭载收费权益产品,a.权益10元_12月竞赛 权益10元竞赛,a.直销员电话,a.recopid,a.oid,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.shc_fanghao_’||v_monsr||’ a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.registerorgid=b.渠道编码(+)
and a.recopid=t.operid(+)
and to_char(a.createdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.createdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

-----新入网
– “拉新客户按照（等效折算+1）户计入，拉新客户按照2户计入。
–剔除次月停机、销户、不活跃等异常情况；
–计算方式：放号明细，表头最新停机锁，取正常在用、强制半停字段；
– 表头是否拉新，“是”按照2户计入；“否”按照1户计入。
–表头五大渠道，“代办”字段”

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_fhs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_fhs as
select ‘‘新入网’’ item,a.subsid,a.servnumber,a.createdate recdate,a.createdate startdate,a.registerorgid recorgid,a.recopid,prodname,a.大类,a.直销员电话,’‘新入网’’ type,
nvl(case when nvl(a.是否拉新,‘‘否’’)=‘‘是’’ and nvl(a.prodname,‘‘0’’) not like ‘’%副卡%‘’ then 2 end,1) 结算数,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_fhb a
where a.status in (‘‘US10’’,‘‘US30’’)
and a.停机锁 in (‘‘正常在用’’,‘‘强制半停’’)
and a.是否活跃=1
and a.限定渠道+a.限定工号>0’;
EXECUTE IMMEDIATE (SQL_STRING);

-----折后48+
–"折后48+新增考核口径
–考核12月折后48+套餐放号激活量，剔除12月底非正常在用客户，折后48+等效新增口径为：
–（1）限折后48+套餐参与，按照48元等效折算；
–（2）针对固移、信用购场景，按照1.2户折算（两个场景均符合，也按1.2户折算）。
–（3）需满足预存话费合约、搭载宽带（宽带为新增口径）、独立铂金会员系列权益包、终端合约四项业务中一项方能计入发展量。
–计算方式：放号明细，表头折后48+，发哥找聪哥支撑。
– 表头五大渠道。“代办”字段

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_zh48s’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_zh48s as
select ‘‘折后48’’ item,a.subsid,a.servnumber,a.createdate recdate,a.createdate startdate,a.registerorgid recorgid,a.recopid,prodname,a.大类,a.直销员电话,’‘折后48’’ type,
nvl(case when nvl(a.是否折后48,0)=1 then round(a.折后价值/48,1) end,0)*
nvl(case when nvl(a.是否宽带融合,‘‘0’’)=‘‘1’’ or nvl(a.是否新入网信用购,‘‘0’’)=‘‘1’’ then 1.2 end,1)*
nvl(case when a.预存合约 is not null or nvl(a.话费合约竞赛,‘‘0’’)=‘‘1’’ or nvl(a.是否开通宽带,‘‘0’’)=‘‘1’’ or nvl(a.是否搭载收费权益产品,‘‘0’’)=‘‘1’’
or nvl(a.权益10元竞赛,‘‘0’’)=‘‘1’’ or nvl(a.是否参与终端合约,‘‘0’’)=‘‘1’’ then 1 end,0 ) 结算数,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_fhb a
where a.status in (‘‘US10’’,‘‘US30’’)
and a.停机锁 in (‘‘正常在用’’,‘‘强制半停’’)
and a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

—泛全5G手机销量
–“泛全5G手机直销量：全联盟5G终端合约销量，包含信用购、套餐打折、顺差让利、5G金币、泛全联盟5G终端优惠购机合约，IMEI未启用双倍扣除。
–计算方式：手机IMEI出库口径销售明细，表头终端类型，取“5GMoile”字段。
–表头是否自办渠道，取“社会渠道”字段。
–表头渠道类型，剔除“大众直销”字段。
–表头参与活动名称，剔除“-”；“【政企】路由器…”；“2022中小学信用购…”。”

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_zdb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_zdb as
select a.imei,a.op_time,a.sale_channel_id,b.大类,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.active_prodname,a.dc_privname,a.imei首次激活时间,a.机卡分离,’‘0’’ 直销员电话,a.工号,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.LSHT_zibo_term_sale’||v_monsr||’ a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.sale_channel_id=b.渠道编码(+)
and a.工号=t.operid(+)
and nvl(a.prodname,‘‘0’’) not like ‘’%2022中小学信用购%‘’
and nvl(a.prodname,‘‘0’’) not like ‘’%【政企】路由器%‘’
and nvl(a.prodname,‘’-‘’) <>‘’-‘’
and a.终端类型=‘‘5GMoile’’
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(‘xc_qdcn_pgtx_qdtype_zds’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_zds as
select ‘‘5G手机’’ item,a.imei,a.imei_tel,a.recdate recdate,a.recdate startdate,a.sale_channel_id recorgid,a.工号 recopid,
prodname,a.大类,a.直销员电话,’‘5G手机’’ type,1 结算数,a.限定渠道,a.限定工号,a.RECOID
from shzc.xc_qdcn_pgtx_qdtype_zdb a
where a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

-----线下强鉴权升档
–“线下强鉴权升档受理量
–（1）新模组升级、模组升档合约、低端阶梯打折（不包含三季度新上线低端升档合约）按照1:1计入，
–跨升档融权益礼包按照1:2计入，
– 小颗粒升档按照1:0.5计入；
– 客户为非一体化套餐成功迁转为一体化套餐额外计入1户。
–（2）流量升档的同时变动语音模组按照1户扣减。
–（3）强鉴权类型：本人身份证件认证、密码+证件认证、人像比对认证、人像鉴权认证（无证）
–计算方式：存量升档运营情况报表，表头是否自办渠道，取“社会渠道”字段。
–取表头其中强鉴权等效升挡量。”

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qjqb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qjqb as
select a.subsid,a.servnumber,a.orgid,b.大类,a.recdate,a.rq cycle,’‘0’’ 直销员电话,a.recopid,
a.prod_type,a.prodid,a.prodname,a.privid,a.privname,a.oid,a.鉴权,a.折算系数,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.gjb_yunying_clsd_01 a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码(+)
and a.recopid=t.operid(+)
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(‘xc_qdcn_pgtx_qdtype_qjqc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qjqc as
select a.,nvl(b2.counts,0) 上月一体化,nvl(b1.counts,0) 本月一体化,
nvl(case when nvl(b2.counts,0)=0 and nvl(b1.counts,0)>0 then 1 end,0) 非一体转一体增加,
nvl(case when a.鉴权 in (‘‘本人身份证件认证’’,‘‘密码+证件认证’’,‘‘人像比对认证’’,‘‘人像鉴权认证（无证）’’) then 1 end,0) 强鉴权类型,
nvl(case when prod_type=‘‘小颗粒升档业务’’ then 0.5
when prod_type=‘‘跨升档融权益礼包’’ then 2
when prod_type in (‘‘低端阶梯打折’’,‘‘新模组升级’’,‘‘模组升档合约新年包’’) then 1 end,0) 计入系数
from shzc.xc_qdcn_pgtx_qdtype_qjqb a,
(select b1.subsid,count() counts from zhyw.SHC_TYTAOCAN_’||v_monsr||’ b1
where nvl(b1.enddate,sysdate+9999)>to_date(‘’‘||v_monsr||’‘’,‘‘yyyymm’’)
and nvl(b1.startdate,sysdate+9999)<=add_months(to_date(‘’‘||v_monsr||’‘’,‘‘yyyymm’’),1) group by b1.subsid ) b1,
(select b2.subsid,count(*) counts from zhyw.SHC_TYTAOCAN_‘||v_monsrq||’ b2
where nvl(b2.enddate,sysdate+9999)>to_date(‘’‘||v_monsrq||’‘’,‘‘yyyymm’’)
and nvl(b2.startdate,sysdate+9999)<=add_months(to_date(‘’‘||v_monsrq||’‘’,‘‘yyyymm’’),1) group by b2.subsid ) b2
where a.subsid=b1.subsid(+)
and a.subsid=b2.subsid(+)
and a.限定渠道+a.限定工号>0’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qjqs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qjqs as
select prod_type item,a.subsid,a.servnumber,a.recdate recdate,a.recdate startdate,a.orgid recorgid,a.recopid,a.privname,a.大类,a.直销员电话,’‘线下强鉴权升档’’ type,
(a.非一体转一体增加+a.计入系数)*a.强鉴权类型 结算数,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_qjqc a’;
EXECUTE IMMEDIATE (SQL_STRING);

----5G套包
–"考核5G流量包10元等效受理情况，流量包计入范围包括流量年包、5G特惠包、5G定向包（合约产品）、不限量提速包、夜间流量包（合约产品）。
–统计12月受理，1月核算时仍在用的客户数。计算方式：2017vip受理明细_当月，指标下拉框“流量包聚合明细”
–表头是否自办渠道，取“社会渠道”字段。
–表头渠道类型，剔除“大众直销”字段。
–表头优惠，提供流量包折算表。 select * from shzc.xc_qdcn_pgtx_qdtype_privnum for update ;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_5gb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_5gb as
select a.prod_type,a.orgid,b.大类,a.servnumber,a.subsid,a.recdate,a.prodid,a.prodname,
a.privid,a.privname,a.oid,a.受理方式,a.鉴权,a.业务类型,
to_date(a.startdate,’‘yyyymmdd hh24:mi:ss’‘) startdate,
to_date(a.enddate,’‘yyyymmdd hh24:mi:ss’‘) enddate,
当前最新状态,’‘0’’ 直销员电话,a.recopid,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.report_mx_month_‘||v_monsr||’_new a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码(+)
and a.recopid=t.operid(+)
and a.prod_type in (select keywords from zhyw.shc_shishi_report_keywords
where 是否聚合明细=‘‘1’’
and 聚合明细展示名称=‘‘流量包聚合明细’’)
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(‘xc_qdcn_pgtx_qdtype_5gs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_5gs as
select prod_type item,a.subsid,a.servnumber,a.recdate recdate,a.recdate startdate,a.orgid recorgid,recopid,a.privname,a.大类,a.直销员电话,’‘5G套包’’ type,
nvl(case when a.当前最新状态=‘‘正使用’’ then nvl(b.num,0) end,0) 结算数,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_5gb a,
shzc.xc_qdcn_pgtx_qdtype_privnum b
where a.privid=b.privid(+)
and a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

----线盒月新增
–“1.线盒新增：
–按照竣工口径移动高清及宽带各按照1户进行计入。
–2.考核线盒同装率，移动高清与宽带新增比目标0.75，低于目标部分从本考核值中扣除。
–计算方式：有线宽带统计与明细(新)，表头号码状态剔除停机，表头是否有htv，取1按照2户计入；非1按照1户计入。”
–①有线宽带统计与明细(新)，表头号码状态剔除停机，剔除直销随销渠道。
–②魔百和明细报表，按开始时间，剔除随销渠道，剔除AN列有数据（直销渠道）。
– ①+②

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(‘xc_qdcn_pgtx_qdtype_xhb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhb 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

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 to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhc as
select a.subsid,a.servnumber,a.prodid_fw,a.startdate,a.enddate,a.带宽限制,a.recorgid,b.大类,
a.recdate,a.dictname,a.jiazhi,a.可开副卡,a.item,a.dc,a.flag_htv,’‘宽带’’ type,a.zhixiao_hm,a.applyoperidfw,a.APPLYOID,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_xhb a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperidfw=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhd’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhd as
select A.所属区县名称, a.servnumber,a.subsid, a.startdate,a.enddate,a.STARTDATE_FW, a.ENDDATE_FW, a.ORGNAME, a.APPLYOPERID, d.UNIT_NAME, a.CANCELOPERID, a.RECDATE,
a.所属小区_学校_自然村, a.recorgid, a.代理商, a.联系电话, a.联系人姓名, a.PRIVNAME, a.PRIVNAME_STARTDATE, a.PRIVNAME_ENDDATE,a.applyoid,
a.接入费, a.赠送2个月, a.赠送2个月STARTDATE, a.赠送2个月ENDDATE, a.预存, a.预存STARTDATE, a.预存ENDDATE, a.FEE, a.RECEIVEDATE, a.dictname, a.statusdate,
a.所属区县名称宽带, a.宽带开通时间, a.宽带结束时间, a.代理商宽带, a.机顶盒厂家, a.是否4k, a.resid, settleday,a.notes,
c.canceldate,c.canceloperid canceloperidc,
b1.zhixiao_hm,b1.zhixiao_name ,a.oid
from zhyw.zhai_htv_subsxin a,
zhyw.zb_yxkd_county b,
zhyw.zb_htv_service_cancel c,
zibo.dw_grid_zone_ds_old d ,
zhyw.cs_order_contact b1
where a.所属区县名称=b.所属区县名称(+)
and a.subsid=c.subsid (+)
and a.applyoid=b1.oid(+)
and a.所属小区_学校_自然村=d.zone_id(+)
and to_char(a.startdate,’‘yyyymm’‘)=’‘’||v_monsr||‘’’
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhe’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhe as
select a.subsid,a.servnumber,a.privname,a.startdate,a.enddate,’‘0’’ 带宽限制,a.recorgid,b.大类,
a.recdate,a.dictname,0 jiazhi,‘‘0’’ 可开副卡,‘‘0’’ item,‘‘0’’ dc,‘‘0’‘flag_htv,’‘高清’’ type,a.zhixiao_hm,a.applyoperid,a.oid,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_xhd a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

—组合起来
zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhce’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_xhce as
select distinct a.* from shzc.xc_qdcn_pgtx_qdtype_xhc a where a.限定渠道+a.限定工号>0
union all
select distinct a.* from shzc.xc_qdcn_pgtx_qdtype_xhe a where a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhcea’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhcea as
select a.*,b.status,zt.dictname statusm,b.statusdate
from shzc.xc_qdcn_pgtx_qdtype_xhce a,
zhyw.subscriber b,
(select * from tbcs.dict_item@bcv where groupid=’‘US’') zt
where a.subsid=b.subsid(+)
and b.status=zt.dictid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhca’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhca as
select a.大类,a.recorgid,count(distinct a.subsid) 宽带,
count(distinct case when nvl(a.flag_htv,’‘0’‘)=’‘1’’ then a.subsid end ) 宽带高清同装,
round(count(distinct case when nvl(a.flag_htv,‘‘0’’)=‘‘1’’ then a.subsid end )/count(distinct a.subsid)*100,2) 宽带高清同装率
from shzc.xc_qdcn_pgtx_qdtype_xhc a
where a.限定渠道+a.限定工号>0
group by a.大类,a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhea’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_xhea as
select a.大类,a.recorgid,count(distinct a.subsid) 高清
from shzc.xc_qdcn_pgtx_qdtype_xhe a
where a.限定渠道+a.限定工号>0
group by a.大类,a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhf’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_xhf as
select distinct a.* from
(select a.大类,a.recorgid from shzc.xc_qdcn_pgtx_qdtype_xhca a
union all
select a.大类,a.recorgid from shzc.xc_qdcn_pgtx_qdtype_xhea a ) a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhg’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_xhg as
select a.大类, a.recorgid,
nvl(b1.宽带,0) 宽带,
nvl(b1.宽带高清同装,0) 宽带高清同装,
nvl(b1.宽带高清同装率,0) 宽带高清同装率,
nvl(b2.高清,0) 高清,
round(nvl(b2.高清,0)/ decode(nvl(b1.宽带,0),0,1,nvl(b1.宽带,0))*100,2) 高清宽带比例
from shzc.xc_qdcn_pgtx_qdtype_xhf a,
shzc.xc_qdcn_pgtx_qdtype_xhca b1,
shzc.xc_qdcn_pgtx_qdtype_xhea b2
where a.recorgid=b1.recorgid(+)
and a.recorgid=b2.recorgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhh’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_xhh as
select a.,row_number() over (partition by a.recorgid,a.type order by a.status,a.recdate desc ) 排名,
b.宽带,b.高清,b.高清宽带比例,
nvl(case when b.高清宽带比例<75 then b.宽带+ (b.高清-ceil(b.宽带/10075)) end ,b.宽带) 宽带上限
from
shzc.xc_qdcn_pgtx_qdtype_xhcea a,
shzc.xc_qdcn_pgtx_qdtype_xhg b
where a.recorgid=b.recorgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_xhs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_xhs as
select a.type item,a.subsid,a.servnumber,a.recdate,a.startdate,a.recorgid,a.applyoperidfw recopid,prodid_fw,a.大类,a.zhixiao_hm 直销员电话,’‘线盒新增’‘type,
nvl(case when a.大类<>’‘直销’’ and a.type=‘‘宽带’’ and a.排名<=a.宽带上限 and a.status=‘‘US10’’ then 1
when a.大类<>‘‘直销’’ and a.type=‘‘高清’’ and a.status=‘‘US10’’ then 1 end,0) 结算数,a.限定渠道,a.限定工号,a.APPLYOID
from shzc.xc_qdcn_pgtx_qdtype_xhh a
where a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

----------------生态业务新增
–“生态业务新增
–（1）质差设备更换按1户计入。
–（2）家庭安防按2户计入；宽带权益包（含云存储接盘包）按1户计入。
–（3）路由器：WiFi5路由器按照199计入，WiFi6路由器按照269元计入。
–（4）提速包：统计收费类提速包，10元等效1户；及129以上的免费千兆。
–（5）点播年包、扩展坞、智慧家庭（包括全屋智能+全光WIFI）：按照价值/120元折算1户；其中月包按照1户计入；点播1元包按照0.3户计入。《魔百和_点播明细》-生效明细，通过月价值字段看，-5为1元点播包。
–计算方式：①《有线宽带_光猫机顶盒更换》-第2个下拉选项“光猫升级_所有受理客户”
– 表头业务类型，取“家庭业务”字段；
– 表头单位，剔除带有随销、直销字段。
– 按1户计入。
– ②《有线宽带_三件套》-家庭安防项目，按开始时间明细；表头是否自办渠道，取“社会渠道”字段。表头受理单位，剔除带有“随销”“直销”字段。按2户计入
– ③《有线宽带_三件套》-宽带权益包项目，按开始时间明细；取表头设备串号，有串号的量；表头是否开通家庭安防，取“家庭安防”字段。表头是否自办渠道，取“社会渠道”字段。表头受理单位，剔除带有“随销”“直销”字段。按1户计入。
– ④《有线宽带_三件套》-路由器，按开始时间明细；表头是否自办渠道，取“社会渠道”字段；表头受理单位，剔除带有“随销”“直销”字段；表头wifi，取“wifi5”、“wifi6”字段；表头售价，取售价/120计入。
– ⑤《有线宽带_三件套》-提速包项目，按受理时间明细；表头是否自办渠道，取“社会渠道”字段。表头受理单位，剔除带有“随销”“直销”字段。表头“优惠名称”，提供折算表，折算后/10计入。
– 以上5条号码状态均剔除停机。
– ⑥2017vip受理明细_当月，指标下拉框“千兆宽带”
– 表头是否自办渠道，取“社会渠道”字段。
– 表头渠道类型，剔除“大众直销”字段。
– 表头优惠，提供千兆宽带折算表。
– ⑦《魔百和_点播明细》-按生效时间明细，表头价格取10、20、19.9、-5，-5统一替换为-7。渠道量求和/10
–生态业务量=①+②+③+④+⑤+⑥+⑦”

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stb as
select distinct a.qx,a.item,a.subsid,a.servnumber,a.recdate,a.recopid,a.recorgid,a.orgname,
case when substr( a.notes,instr(a.notes,’‘1’‘,1),11) in (select servnumber from zhyw.subscriber ) then ‘’***’’ else a.notes end notes,
case when a.recdefid=‘‘WiredDevup’’ then ‘‘家庭业务设备升级’’ else a.recdefid end 家庭业务设备升级, case when b.user_id is not null then ‘‘目标客户’’ end 目标客户,
a.privname ,a.rec_type,b.user_flag,a.zone_id,a.grid_name ,a.oid
From zhyw.zb_chg_equ_subs a,
zhyw.zb_chg_equ_photo b
where a.subsid=b.user_id(+)
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(‘xc_qdcn_pgtx_qdtype_stc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stc as
select a.*,b.大类,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_stb a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.recopid=t.operid(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

—有线宽带_三件套: qcy_yxkd_sanjiantao

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_std’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_std as
select distinct a.rec_qx,a.item,b.grid_id,b.grid_name,a.subsid, a.servnumber, a.unit_name,a.pkg_prod,a.pkg_name, a.privid,a.privname,
b3.attrvalue,b3.res_type_name, b3.flag,a.recorgid,
a.recdate,a.startdate,a.enddate,c.orgname,a.applyoperid, b1.notes,
a.是否自办渠道,a.渠道经理,
a.kd_start,a.kd_end,
a.createdate ,a.statusname, a.statusdate,a.tc_privname,a.jiazhi,
a.zone_id,a.zone_name,
b4.item 权益,c.unit_name unit_namec,a.flag_midhigh,
zhixiao_hm,b5.zhixiao_name ,a.applyoid
from zhyw.zb_yxkd_sanjiantao_subs a,
zhyw.zb_yyy_unit b,
zhyw.shc_organization c,
zhyw.shc_reception b1,
zhyw.zb_yxkd_anfang_mac b3,
(select * from zhyw.zb_yxkd_sanjiantao_subs
where nvl(enddate,sysdate+10000)>startdate and
nvl(enddate,sysdate+10000)>sysdate-1 and item like upper(’‘%宽带权益%’‘) )b4,
zhyw.cs_order_contact b5
where a.applyoperid=b.operid(+) and
a.recorgid=c.orgid(+) and
a.applyoid=b1.oid(+) and
a.applyoid=b3.applyoid(+) and
a.privid=b3.privid(+) and
a.subsid=b4.subsid(+) and
a.applyoid=b5.oid(+) and
a.item like ‘’%安防%’’
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_ste’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_ste as
select a.*,b.大类,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_std a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

—路由器

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stf’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stf as
select distinct a.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,
　　　 a1.note , a.recdate,a.startdate,a.enddate,c.orgname,a.applyoperid, b1.notes,
a.是否自办渠道,a.渠道经理,a.recorgid,
a.kd_start,a.kd_end,
a.createdate ,a.statusname, a.statusdate,a.tc_privname,a.jiazhi,
a.zone_id,a.zone_name,
case when b2.recoid is not null then ‘‘上门收费’’ end 上门收费,
to_char(b2.fee/100) fee,
to_char(b2.paydate,’‘yyyymmdd’') paydate,
c.unit_name unit_namec,
b3.res_type_name, b3.attrvalue ,
b4. lastonline,
to_char(a1.dc) dc,a1.售价,
case when b5.AFFECTTYPE=2 then ‘‘当月生效’’ when b5.AFFECTTYPE=3 then ‘‘次月生效’’ else to_char(b5.affecttype,0) end 生效月份,
a.flag_midhigh ,zhixiao_hm,b6.zhixiao_name ,a.applyoid

     from zhyw.zb_yxkd_sanjiantao_subs a,(select *from  qcy.qcy_yxkd_sanjiantao_priv  where   item  like ''%路由器%''   and  note  like ''%W%'') a1,zhyw.zb_yyy_unit   b,zhyw.shc_organization c,zhyw.shc_reception b1,zhyw.zb_door_fee_rec B2,(select * from zhyw.zb_yxkd_anfang_mac where item=''路由器'') b3,(select   product_no, to_char(lastonline_date,''yyyymmdd'') lastonlineFrom   zibo.d_luyou_equipment_naguan_dt@zibo) b4,zhyw.SHC_SUBS_PRODUCT b5,zhyw.cs_order_contact  b6 where a.applyoperid=b.operid(+) anda.recorgid=c.orgid(+) anda.applyoid=b1.oid(+) anda.privid=a1.privid(+) anda.applyoid=b2.recoid(+) anda.applyoid=b3.applyoid(+) anda.servnumber=b4.product_no(+) anda.subsid=b5.subsid(+) anda.pkg_prod=b5.prodid(+) anda.applyoid=b6.oid(+) and a.item=''路由器'' and to_char(a.startdate,''yyyymm'')='''||v_monsr||'''and a.startdate-1 <to_date('''||P_DAY||''',''yyyymmdd'')';EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stg’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_stg as
select a.*,b.大类 ,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_stf a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

— 提速包

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_sth’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_sth 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
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.item=‘‘提速包’’
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(‘xc_qdcn_pgtx_qdtype_sti’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_sti as
select a.*,b.大类,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_sth a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

—宽带权益包

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stj’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stj as
select distinct a.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, a.recdate,a.startdate,a.enddate,c.orgname,a.applyoperid, b1.notes,
a.是否自办渠道,a.渠道经理,a.recorgid,
a.kd_start,a.kd_end,
a.createdate ,a.statusname, a.statusdate,a.tc_privname,a.jiazhi,
a.zone_id,a.zone_name,
b2.item item2,c.unit_name unit_namec,a.flag_midhigh,zhixiao_hm,b3.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_sanjiantao_subs where nvl(enddate,sysdate+10000)>startdate
and nvl(enddate,sysdate+10000)>sysdate-1 and item like upper(’‘%家庭安防%’‘) )b2,
zhyw.cs_order_contact b3
where a.applyoperid=b.operid(+) and
a.recorgid=c.orgid(+) and
a.applyoid=b1.oid(+) and
a.subsid=b2.subsid(+) and
a.applyoid=b3.oid(+)
and a.item=’‘宽带权益包’’
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stk’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_stk as
select a.*,b.大类,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_stj a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

----千兆宽带 表头优惠，提供千兆宽带折算表。

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stl’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stl as
select a.prod_type,a.orgid,b.大类,a.RECOPID,a.servnumber,a.subsid,a.recdate,a.prodid,a.prodname,
a.privid,a.privname,a.oid,a.受理方式,a.鉴权,a.业务类型,
to_date(a.startdate,’‘yyyymmdd hh24:mi:ss’‘) startdate,
to_date(a.enddate,’‘yyyymmdd hh24:mi:ss’‘) enddate,
当前最新状态,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.report_mx_month_’||v_monsr||‘_new a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码(+)
and a.RECOPID=t.operid(+)
and a.prod_type =’‘千兆宽带’’
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(‘xc_qdcn_pgtx_qdtype_stm’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_stm as
select a.*, nvl(b.num,0) 结算数
from shzc.xc_qdcn_pgtx_qdtype_stl a,
shzc.xc_qdcn_pgtx_qdtype_privnum b
where a.privid=b.privid(+)
and a.限定渠道+a.限定工号>0 ';
EXECUTE IMMEDIATE (SQL_STRING);

—魔百和_点播明细

select max(table_name) into V_TAB1 FROM all_tables where table_name like upper(‘%DW_MBH_USER_HY_FLAG_20%’) and owner=‘ZIBO’;
select to_char(max(op_time),‘yyyymmdd’) into V_TAB2 from zibo.DW_MBH_USER_ANDTV_SPLX_DM;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stn’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stn as
select distinct a.county_id,b2.unit_name,rec_qx,k.grid_name, a.servnumber,a.subsid,bizcode,vc_bizname,applyoperid,dictname,
a.recdate,b1.orgname,flag_nb,price,a.recorgid,a.orgname orgnamea,
startdate,enddate ,hm_status,所属区县名称,flag_red,flag_app, case when JF>=1600 then ‘‘可兑换’’ else ‘‘否’’ end 可兑换,
sp_name,sp_start,sp_end,sp_canceloperid ,
htv_start ,htv_end,
kd_start ,kd_end,
KD_QX, FLAG_XF, XF_START, XF_END, XF_PRIV ,xf_price,XF_OPERID, XF_QX, XF_ORG,
db_time 点播播放时长，
tz_time 赛特斯探针原始收视时长，
zmdj_cs 桌面点击次数,
hy_flag 用户当月是否活跃,
b3.playtime_xd_m 有效播放时长_分钟,
b4.note,a.tc_priv,a.jiazhi,FLAG_MIDHIGH ,
b5.zhixiao_hm,b5.zhixiao_name ,a.applyoid
from zhyw.zb_htv_spservice_subs a,
zhyw.zb_yyy_unit k,
zibo.’||V_TAB1||’ a1 ,
( select b.orgname,a.oid from zhyw.shc_reception a, zhyw.organization b
where a.recorgid=b.orgid(+)
) b1,
zhyw.zb_grid_subs b2 ,
( select product_no,sum(playtime_xd_m ) playtime_xd_m
from zibo.DW_MBH_USER_ANDTV_SPLX_DM
where playtime_xd_m>0.05 and to_char(op_time,‘‘yyyymmdd’’)=‘’‘||V_TAB2||’‘’
group by product_no ) b3,
(select *From qcy.qcy_htv_nb_priv where note=‘‘接盘优惠’’)b4,
zhyw.cs_order_contact b5
where a.applyoperid=k.operid(+) and
a.subsid=a1.user_id(+) and
a.applyoid=b1.oid(+) and
a.subsid=b2.subsid(+) and
a.servnumber=b3.product_No(+) and
a.bizcode=b4.privid(+) and
a.applyoid=b5.oid(+)
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.startdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_sto’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_sto as
select a.*,b.大类,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from shzc.xc_qdcn_pgtx_qdtype_stn a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码(+)
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

-----生态组合
--------提速包字典表 select * from shzc.xc_qdcn_pgtx_qdtype_tsbnum for update ;
—路由器部分取表头wifi字段，只要是wifi5就按折算199/120；wifi6折算269/120.不需要看售价了。

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_stp’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_stp as
select a.item,a.subsid,a.servnumber,a.recdate,a.recdate startdate,a.recorgid,a.recopid,a.家庭业务设备升级,a.大类,’‘0’’ 直销员电话,
‘‘光猫升级’’ type,1 预结算数,1 考核停机,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_stc a where a.限定渠道+a.限定工号>0
union all
select a.item,a.subsid,a.servnumber,a.recdate,a.startdate,a.recorgid,a.applyoperid,a.pkg_name,a.大类,a.zhixiao_hm,
‘‘家庭安防’’ type,2 预结算数,1 考核停机,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_ste a where a.限定渠道+a.限定工号>0
union all
–宽带权益包
select a.item,a.subsid,a.servnumber,a.recdate,a.startdate,a.recorgid,a.applyoperid,a.pkg_name,a.大类,a.zhixiao_hm,
‘‘宽带权益包’’ type,1 预结算数,1 考核停机,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_stk a where a.限定渠道+a.限定工号>0
union all
–路由器
select a.item,a.subsid,a.servnumber,a.recdate,a.startdate,a.recorgid,a.applyoperid,a.note||‘’‘’||a.售价,a.大类,a.zhixiao_hm,‘‘路由器’’ type,
round(nvl(case when upper(a.note) like ‘’%WIFI5%‘’ then 199/120 end,269/120),1) 预结算数,1 考核停机,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_stg a where a.限定渠道+a.限定工号>0 and (upper(a.note) like ‘’%WIFI6%‘’ or upper(a.note) like ‘’%WIFI5%‘’)
union all
–提速包
select a.item,a.subsid,a.servnumber,a.recdate,a.recdate startdate,a.recorgid,a.APPLYOPERID,a.privname||‘’‘’||to_char(nvl(b.num,0)) pkg_name,
a.大类,a.zhixiao_hm,‘‘提速包’’ type,round(nvl(b.num,0)/10,1) 预结算数 ,1 考核停机,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_sti a,
shzc.xc_qdcn_pgtx_qdtype_tsbnum b
where a.限定渠道+a.限定工号>0 and a.privid=b.privid(+)
union all
–千兆宽带
select a.prod_type,a.subsid,a.servnumber,a.recdate,a.recdate,a.orgid,a.recopid,a.prodname,a.大类,‘‘0’’,
‘‘千兆宽带’’ type,a.结算数 预结算数,0 考核停机,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_stm a where a.限定渠道+a.限定工号>0
union all
–魔百和点播明细 表头价格取10、20、19.9、-5，-5统一替换为-7。渠道量求和/10
select ‘‘魔百和点播’’ prod_type,a.subsid,a.servnumber,a.recdate,a.startdate,a.recorgid,a.applyoperid,a.vc_bizname,a.大类,a.zhixiao_hm,‘‘魔百和点播’’ type,
round(a.price/10,1) 预结算数,0 考核停机,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_sto a where a.限定渠道+a.限定工号>0
and a.price in (10,20,19.9,-5,-7) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_sts’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_sts as
select a.ITEM, a.SUBSID, a.SERVNUMBER, a.RECDATE, a.startdate, a.RECORGID, a.RECOPID, a.家庭业务设备升级, a.大类, a.直销员电话, a.TYPE,
nvl(case when a.考核停机=1 and nvl(b.status,’‘0’‘)<>’‘US10’’ then 0 end,a.预结算数) 结算数, 限定渠道, 限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_stp a,
zhyw.subscriber b,
(select * from tbcs.dict_item@bcv where groupid=‘‘US’’) zt
where a.subsid=b.subsid(+)
and b.status=zt.dictid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

—权益及收费类彩铃用户受理 zjf_zxhy
—"权益及收费彩铃用户受理
—（1）按受理单位统计，仅统计线下工号办理量。总完成量=权益完成量+收费视频彩铃完成量
—（2）权益产品发展：仅考核收费产品，黄金会员月包协议包折算0.4户、黄金会员年包折算0.6户、
—铂金会员协议包月包折算1.2户，铂金会员年包折算2.4户，铂金会员车主版、生活版协议包月包折算1户，
—铂金会员车主版/生活版年包折算2户，权益PLUS会员协议包月包折算2户，权益PLUS会员年包折算4户，权益融合升档礼包折算1.5户。

—计算方式：权益产品运营情况，表头是否自办渠道，取“社会渠道”字段。
— 表头折算，剔除“0.3”以下的折算量，不包含“0.3”。
— 表头渠道名称，剔除带有“铁通大众直销”字段。"

—（3）收费视频彩铃发展：半年包/协议包按10元等效折算、年包按1：2计入。
—视频彩铃需取2017vip指标汇总_当月“视频彩铃”字段。

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qyb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qyb as
select a.*,b.大类,’‘0’’ 直销员电话,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.zjf_zxhy_mmx_‘||v_monsr||’ a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.发展_渠道编码=b.渠道编码(+)
and a.APPLYOPERID=t.operid(+)
and to_char(a.发展_考核时间,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.发展_考核时间-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qyc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qyc as
select a.prod_type,a.orgid,b.大类,a.RECOPID,a.servnumber,a.subsid,a.recdate,a.prodid,a.prodname,
a.privid,a.privname,a.oid,a.受理方式,a.鉴权,a.业务类型,
to_date(a.startdate,’‘yyyymmdd hh24:mi:ss’‘) startdate,
to_date(a.enddate,’‘yyyymmdd hh24:mi:ss’‘) enddate,
当前最新状态,’‘0’’ 直销员电话,
nvl(case when b.渠道编码 is not null then 1 end,0) 限定渠道,
nvl(case when t.operid is not null then 1 end,0) 限定工号
from zhyw.report_mx_month_‘||v_monsr||’_new a,
shzc.xc_qdcn_pgtx_qdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码(+)
and a.RECOPID=t.operid(+)
and a.prod_type =‘‘视频彩铃’’
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsr||’‘’
and a.recdate-1 <to_date(‘’‘||P_DAY||’‘’,‘‘yyyymmdd’’)';
EXECUTE IMMEDIATE (SQL_STRING);

--------视频彩铃字典表 select * from shzc.xc_qdcn_pgtx_qdtype_spclnum for update ;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qyd’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qyd as
select a.*,round(nvl(b.num,0),1) 结算数
from shzc.xc_qdcn_pgtx_qdtype_qyc a,
shzc.xc_qdcn_pgtx_qdtype_spclnum b
where a.prodid=b.privid(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_qys’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_qys as
select a.主指标 prod_type,a.subsid,a.servnumber,a.发展_受理时间 recdate,a.发展_考核时间 startdate,
a.发展_渠道编码 recorgid,a.applyoperid,a.privname,a.大类,a.直销员电话,’‘权益产品’’ type,a.折算 结算数,a.限定渠道,a.限定工号,a.applyoid
from shzc.xc_qdcn_pgtx_qdtype_qyb a where a.限定渠道+a.限定工号>0 and a.折算 >= 0.3
union all
select a.prod_type,a.subsid,a.servnumber,a.recdate,a.startdate,a.orgid,a.recopid,a.prodname,a.大类,a.直销员电话,‘‘视频彩铃’’ type,
a.结算数,a.限定渠道,a.限定工号,a.oid
from shzc.xc_qdcn_pgtx_qdtype_qyd a where a.限定渠道+a.限定工号>0’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_mxb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_mxb as
select a.item,
to_char(a.subsid) subsid,
a.servnumber,
a.recdate,
a.startdate,
a.recorgid,
a.recopid,
a.prodname,
a.大类,
a.直销员电话,
a.type,
a.结算数,
a.限定渠道,
a.限定工号,
a.oid
from shzc.xc_qdcn_pgtx_qdtype_fhs a
union all
select b.item,
to_char(b.subsid),
b.servnumber,
b.recdate,
b.startdate,
b.recorgid,
b.recopid,
b.prodname,
b.大类,
b.直销员电话,
b.type,
b.结算数,
b.限定渠道,
b.限定工号,
b.oid from shzc.xc_qdcn_pgtx_qdtype_zh48s b
union all
select c.item,
c.imei,
c.imei_tel,
c.recdate,
c.startdate,
c.recorgid,
c.recopid,
c.prodname,
c.大类,
c.直销员电话,
c.type,
c.结算数,
c.限定渠道,
c.限定工号,
c.recoid from shzc.xc_qdcn_pgtx_qdtype_zds c
union all
select d.item,
to_char(d.subsid),
d.servnumber,
d.recdate,
d.startdate,
d.recorgid,
d.recopid,
d.privname,
d.大类,
d.直销员电话,
d.type,
d.结算数,
d.限定渠道,
d.限定工号,
d.oid from shzc.xc_qdcn_pgtx_qdtype_qjqs d
union all
select e.item,
to_char(e.subsid),
e.servnumber,
e.recdate,
e.startdate,
e.recorgid,
e.recopid,
e.privname,
e.大类,
e.直销员电话,
e.type,
e.结算数,
e.限定渠道,
e.限定工号,
e.oid from shzc.xc_qdcn_pgtx_qdtype_5gs e
union all
select f.item,
to_char(f.subsid),
f.servnumber,
f.recdate,
f.startdate,
f.recorgid,
f.recopid,
f.prodid_fw,
f.大类,
f.直销员电话,
f.type,
f.结算数,
f.限定渠道,
f.限定工号,
f.applyoid from shzc.xc_qdcn_pgtx_qdtype_xhs f
union all
select g.item,
to_char(g.subsid),
g.servnumber,
g.recdate,
g.startdate,
g.recorgid,
g.recopid,
g.家庭业务设备升级,
g.大类,
g.直销员电话,
g.type,
g.结算数,
g.限定渠道,
g.限定工号,
g.oid from shzc.xc_qdcn_pgtx_qdtype_sts g
union all
select h.prod_type,
to_char(h.subsid),
h.servnumber,
h.recdate,
h.startdate,
h.recorgid,
h.applyoperid,
h.privname,
h.大类,
h.直销员电话,
h.type,
h.结算数,
h.限定渠道,
h.限定工号,
h.applyoid from shzc.xc_qdcn_pgtx_qdtype_qys h’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qdtype_mxs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qdtype_mxs as
select distinct a.item,
a.subsid,
a.servnumber,
a.recdate,
a.startdate,
a.recorgid,
a.recopid,
a.prodname,
a.大类,
a.type,
a.结算数,
a.限定渠道,
a.限定工号,
a.oid,
nvl(b5.zhixiao_hm,a.直销员电话) 直销员电话
from shzc.xc_qdcn_pgtx_qdtype_mxb a,
zhyw.cs_order_contact b5
where a.oid=b5.oid(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

  ----- '核心','泛渠道' 单独提取汇总zhyw.shc_drop_retable(upper('xc_qdcn_pgtx_qdtype_qdmxa'),'SHZC');SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_qdmxa asselect a.*,to_number(to_char(a.startdate,''dd'')) create_day,nvl(c.ww,1) 本月第几周 from shzc.xc_qdcn_pgtx_qdtype_mxs a, shzc.zxb_qdcn_pgtx_wwtype_sja cwhere a.大类 in (''核心'',''泛渠道'')and to_number(to_char(a.startdate,''dd'')) between c.start_day and c.end_day  ';EXECUTE IMMEDIATE (SQL_STRING);----每月留存zhyw.shc_drop_retable(upper('xc_qdcn_pgtx_qdtype_mxb_'||v_monsr),'SHZC');SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_mxb_'||v_monsr||' asselect * from  shzc.xc_qdcn_pgtx_qdtype_qdmxa ';EXECUTE IMMEDIATE (SQL_STRING);--各分项汇总zhyw.shc_drop_retable(upper('xc_qdcn_pgtx_qdtype_qdmxb'),'SHZC');SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_qdmxb asselect '''||P_DAY||''' cycle,a.大类,a.recorgid,a.type,sum(nvl(case when a.create_day=to_number(substr('''||P_DAY||''',7,2)) then a.结算数 end,0)) 当日结算数,sum(a.结算数) 当月结算数,sum(nvl(case when a.本月第几周=''1'' then a.结算数 end,0)) 第一周结算数,sum(nvl(case when a.本月第几周=''2'' then a.结算数 end,0)) 第二周结算数,sum(nvl(case when a.本月第几周=''3'' then a.结算数 end,0)) 第三周结算数,sum(nvl(case when a.本月第几周=''4'' then a.结算数 end,0)) 第四周结算数from shzc.xc_qdcn_pgtx_qdtype_qdmxa a group by a.大类,a.recorgid,a.type  ';EXECUTE IMMEDIATE (SQL_STRING);--新入网 折后48+ 泛全5G手机销量 5G套包 线下强鉴权升档 线盒月新增 生态业务新增 权益及收费类彩铃zhyw.shc_drop_retable(upper('xc_qdcn_pgtx_qdtype_qdhz'),'SHZC');SQL_STRING:='create table shzc.xc_qdcn_pgtx_qdtype_qdhz asselect a.cycle,a.大类,a.recorgid,sum(nvl(case when a.type=''新入网'' then a.当日结算数 end ,0)) 当日新入网,sum(nvl(case when a.type=''新入网'' then a.当月结算数 end ,0)) 当月新入网,sum(nvl(case when a.type=''新入网'' then a.第一周结算数 end ,0)) 第一周新入网,sum(nvl(case when a.type=''新入网'' then a.第二周结算数 end ,0)) 第二周新入网,sum(nvl(case when a.type=''新入网'' then a.第三周结算数 end ,0)) 第三周新入网,sum(nvl(case when a.type=''新入网'' then a.第四周结算数 end ,0)) 第四周新入网,sum(nvl(case when a.type=''折后48'' then a.当日结算数 end ,0)) 当日折后48,sum(nvl(case when a.type=''折后48'' then a.当月结算数 end ,0)) 当月折后48,sum(nvl(case when a.type=''折后48'' then a.第一周结算数 end ,0)) 第一周折后48,sum(nvl(case when a.type=''折后48'' then a.第二周结算数 end ,0)) 第二周折后48,sum(nvl(case when a.type=''折后48'' then a.第三周结算数 end ,0)) 第三周折后48,sum(nvl(case when a.type=''折后48'' then a.第四周结算数 end ,0)) 第四周折后48,sum(nvl(case when a.type=''5G手机'' then a.当日结算数 end ,0)) 当日5G手机,sum(nvl(case when a.type=''5G手机'' then a.当月结算数 end ,0)) 当月5G手机,sum(nvl(case when a.type=''5G手机'' then a.第一周结算数 end ,0)) 第一周5G手机,sum(nvl(case when a.type=''5G手机'' then a.第二周结算数 end ,0)) 第二周5G手机,sum(nvl(case when a.type=''5G手机'' then a.第三周结算数 end ,0)) 第三周5G手机,sum(nvl(case when a.type=''5G手机'' then a.第四周结算数 end ,0)) 第四周5G手机,sum(nvl(case when a.type=''5G套包'' then a.当日结算数 end ,0)) 当日5G套包,sum(nvl(case when a.type=''5G套包'' then a.当月结算数 end ,0)) 当月5G套包,sum(nvl(case when a.type=''5G套包'' then a.第一周结算数 end ,0)) 第一周5G套包,sum(nvl(case when a.type=''5G套包'' then a.第二周结算数 end ,0)) 第二周5G套包,sum(nvl(case when a.type=''5G套包'' then a.第三周结算数 end ,0)) 第三周5G套包,sum(nvl(case when a.type=''5G套包'' then a.第四周结算数 end ,0)) 第四周5G套包,sum(nvl(case when a.type=''线下强鉴权升档'' then a.当日结算数 end ,0)) 当日强鉴权,sum(nvl(case when a.type=''线下强鉴权升档'' then a.当月结算数 end ,0)) 当月强鉴权,sum(nvl(case when a.type=''线下强鉴权升档'' then a.第一周结算数 end ,0)) 第一周强鉴权,sum(nvl(case when a.type=''线下强鉴权升档'' then a.第二周结算数 end ,0)) 第二周强鉴权,sum(nvl(case when a.type=''线下强鉴权升档'' then a.第三周结算数 end ,0)) 第三周强鉴权,sum(nvl(case when a.type=''线下强鉴权升档'' then a.第四周结算数 end ,0)) 第四周强鉴权,sum(nvl(case when a.type=''线盒新增'' then a.当日结算数 end ,0)) 当日线盒新增,sum(nvl(case when a.type=''线盒新增'' then a.当月结算数 end ,0)) 当月线盒新增,sum(nvl(case when a.type=''线盒新增'' then a.第一周结算数 end ,0)) 第一周线盒新增,sum(nvl(case when a.type=''线盒新增'' then a.第二周结算数 end ,0)) 第二周线盒新增,sum(nvl(case when a.type=''线盒新增'' then a.第三周结算数 end ,0)) 第三周线盒新增,sum(nvl(case when a.type=''线盒新增'' then a.第四周结算数 end ,0)) 第四周线盒新增,--光猫升级 家庭安防 宽带权益包  路由器  提速包 千兆宽带 魔百和点播sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.当日结算数 end ,0)) 当日生态业务,sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.当月结算数 end ,0)) 当月生态业务,sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.第一周结算数 end ,0)) 第一周生态业务,sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.第二周结算数 end ,0)) 第二周生态业务,sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.第三周结算数 end ,0)) 第三周生态业务,sum(nvl(case when a.type in (''千兆宽带'',''提速包'',''光猫升级'',''路由器'',''宽带权益包'',''家庭安防'',''魔百和点播'') then a.第四周结算数 end ,0)) 第四周生态业务,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.当日结算数 end ,0)) 当日权益彩铃,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.当月结算数 end ,0)) 当月权益彩铃,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.第一周结算数 end ,0)) 第一周权益彩铃,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.第二周结算数 end ,0)) 第二周权益彩铃,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.第三周结算数 end ,0)) 第三周权益彩铃,sum(nvl(case when a.type in (''权益产品'',''视频彩铃'') then a.第四周结算数 end ,0)) 第四周权益彩铃from shzc.xc_qdcn_pgtx_qdtype_qdmxb agroup by a.cycle,a.大类,a.recorgid  ';EXECUTE IMMEDIATE (SQL_STRING);

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

SQL_STRING:=’ insert into shzc.xc_qdcn_pgtx_qdtype_qdbd
select ‘’‘||P_DAY||’‘’ in_time,a.区县,a.网格名称,a.渠道编码,
a.渠道名称,a.渠道类型,a.大类,a.职位,a.渠道归属,
nvl(b.当日新入网,0) 当日新入网,
nvl(b.当月新入网,0) 当月新入网,
nvl(b.第一周新入网,0) 第一周新入网,
nvl(b.第二周新入网,0) 第二周新入网,
nvl(b.第三周新入网,0) 第三周新入网,
nvl(b.第四周新入网,0) 第四周新入网,
nvl(b.当日折后48,0) 当日折后48,
nvl(b.当月折后48,0) 当月折后48,
nvl(b.第一周折后48,0) 第一周折后48,
nvl(b.第二周折后48,0) 第二周折后48,
nvl(b.第三周折后48,0) 第三周折后48,
nvl(b.第四周折后48,0) 第四周折后48,
nvl(b.当日5g手机,0) 当日5g手机,
nvl(b.当月5g手机,0) 当月5g手机,
nvl(b.第一周5g手机,0) 第一周5g手机,
nvl(b.第二周5g手机,0) 第二周5g手机,
nvl(b.第三周5g手机,0) 第三周5g手机,
nvl(b.第四周5g手机,0) 第四周5g手机,
nvl(b.当日5g套包,0) 当日5g套包,
nvl(b.当月5g套包,0) 当月5g套包,
nvl(b.第一周5g套包,0) 第一周5g套包,
nvl(b.第二周5g套包,0) 第二周5g套包,
nvl(b.第三周5g套包,0) 第三周5g套包,
nvl(b.第四周5g套包,0) 第四周5g套包,
nvl(b.当日强鉴权,0) 当日强鉴权,
nvl(b.当月强鉴权,0) 当月强鉴权,
nvl(b.第一周强鉴权,0) 第一周强鉴权,
nvl(b.第二周强鉴权,0) 第二周强鉴权,
nvl(b.第三周强鉴权,0) 第三周强鉴权,
nvl(b.第四周强鉴权,0) 第四周强鉴权,
nvl(b.当日线盒新增,0) 当日线盒新增,
nvl(b.当月线盒新增,0) 当月线盒新增,
nvl(b.第一周线盒新增,0) 第一周线盒新增,
nvl(b.第二周线盒新增,0) 第二周线盒新增,
nvl(b.第三周线盒新增,0) 第三周线盒新增,
nvl(b.第四周线盒新增,0) 第四周线盒新增,
nvl(b.当日生态业务,0) 当日生态业务,
nvl(b.当月生态业务,0) 当月生态业务,
nvl(b.第一周生态业务,0) 第一周生态业务,
nvl(b.第二周生态业务,0) 第二周生态业务,
nvl(b.第三周生态业务,0) 第三周生态业务,
nvl(b.第四周生态业务,0) 第四周生态业务,
nvl(b.当日权益彩铃,0) 当日权益彩铃,
nvl(b.当月权益彩铃,0) 当月权益彩铃,
nvl(b.第一周权益彩铃,0) 第一周权益彩铃,
nvl(b.第二周权益彩铃,0) 第二周权益彩铃,
nvl(b.第三周权益彩铃,0) 第三周权益彩铃,
nvl(b.第四周权益彩铃,0) 第四周权益彩铃

from shzc.xc_qdcn_pgtx_qdtype_sja a,
shzc.xc_qdcn_pgtx_qdtype_qdhz b
where a.渠道编码=b.recorgid(+)
and a.大类 in (‘‘核心’’,‘‘泛渠道’’) ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;