----20230801 渠道评估体系数据
------微企经理直销渠道对应关系
---工号 微企经理归属 直销渠道 类型
--- create table zhyw.xc_qdpgtx_wqjl_zxqd_bdcd
---(operid varchar2(18),opername varchar2(80),zxqd_id varchar2(80),zxqd_name varchar2(80),type varchar2(80),in_time date);
--- select * from zhyw.xc_qdpgtx_wqjl_zxqd_bdcd for update ;
--- update zhyw.xc_qdpgtx_wqjl_zxqd_bdcd a set a.in_time=sysdate
--- where a.in_time is null;
---直销员与微企经理
zhyw.shc_drop_retable(upper('xc_qdpgtx_wqjl_jfzxy_sjxf'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_wqjl_jfzxy_sjxf as
select TO_CHAR(OP_TIME,''YYYYMMDD'') 日期,
ID 主键 ,
APP_ID 应用编码 ,
APP_CODE 平台编码 ,
APP_NAME 平台名称 ,
PARTNER_NAME 合伙人姓名 ,
PARTNER_MOBILE 合伙人手机号 ,
PARTNER_AUDIT_STATUS 合伙人审核状态 ,
PARTNER_TYPE 合伙人类型 ,
PARTNER_ROLE 合伙人角色 ,
PARTNER_ID 合伙人身份证号 ,
PARTNER_ADDRESS 合伙人地址 ,
PARTNER_AUTH_STATUS 合伙人认证状态 ,
PARTNER_RESOURCE 合伙人来源 ,
PARTNER_DEVELOP_STATUS 合伙人是否可以发展下线 ,
REC_PARTNER_MOBILE 合伙人推荐手机号 ,
REC_PARTNER_NAME 合伙人推荐姓名 ,
PARTNER_STATUS 合伙人状态 ,
PARTNER_SHOP_CODE 合伙店团队编码 ,
PARTNER_SHOP_NAME 合伙店团队名称 ,
PARTNER_SHOP_IMAGE 合伙店团队图片 ,
PARTNER_SHOP_STATUS 合伙店团队状态 ,
EMPLOYEE_NUMBER BOSS工号 ,
EMPLOYEE_MOBILE BOSS手机号 ,
EMPLOYEE_NAME BOSS姓名 ,
PROVINCE_CODE 省份编码 ,
CITY_CODE 城市编码 ,
DISTRICT_CODE 地区编码 ,
PROVINCE 省份 ,
CITY 城市 ,
DISTRICT 地区 ,
CREATED_BY 创建人 ,
CREATED_TIME 创建时间 ,
UPDATED_BY 更新人 ,
UPDATED_TIME 更新时间 ,
DELETED 是否删除1可用2删除 ,
PHY_STORE_NAME 实体店名称 ,
PHY_STORE_STATUS 是否有实体店1有2没有 ,
PHY_STORE_REGISTRATION_NUMBER 实体店营业执照注册号 ,
PHY_STORE_LONGITUDE 实体店经度 ,
PHY_STORE_LATITUDE 实体店纬度 ,
PHY_STORE_LOCATION 实体店位置地图获取 ,
PHY_STORE_DISTRICT 实体店区域地图获取 ,
PHY_STORE_ADDRESS 实体店详细地址 ,
PHY_STORE_CONTACTS 实体店联系人 ,
PHY_STORE_CONTACTS_NUM 实体店联系电话 ,
PHY_STORE_LICENSE 实体店营业执照 ,
PHY_STORE_IMAGE 实体店照片 ,
SHOPPER_ORG_ID 工号对应店长用户信息所属机构id ,
SELF_EMPLOYEE_NUMBER 自营工号 ,
FOUR_ORG_CODE 四级机构编码 ,
FOUR_ORG_NAME 四级机构名称 ,
FIVE_ORG_CODE 五级机构编码 ,
FIVE_ORG_NAME 五级机构名称 ,
STATUS_UPDATED_TIME 状态更新时间 ,
FIRST_INDUSTRY_CODE 行业形态编码一级编码 ,
FIRST_INDUSTRY_NAME 行业形态名称一级名称 ,
SECOND_INDUSTRY_CODE 行业形态属性编码二级编码 ,
SECOND_INDUSTRY_NAME 行业形态属性名称二级名称
from zibo.ods_partner_info_ds
where city like ''淄博%''
and partner_role in (84,85) ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_wqjl_jfzxy_sjxfa'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfa as
select a.* from
(select a.*,row_number() over (partition by a.合伙人手机号 order by a.创建时间 desc ) 排名
from zhyw.xc_qdpgtx_wqjl_jfzxy_sjxf a
where to_date(a.创建时间,''yyyy-mm-dd hh24:mi:ss'')-1 < to_date('''||P_DAY||''',''yyyymmdd'') ) a
where a.排名 = 1 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_wqjl_jfzxy_sjxfb'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfb as
select a.合伙人姓名,a.合伙人手机号,a.合伙人角色,a.合伙人审核状态,a.合伙人认证状态,a.合伙人状态,a.boss工号,a.boss手机号,a.boss姓名,b.orgid,c.orgname
from zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfa a,
tbcs.operator@bcv b,
tbcs.organization@bcv c
where a.boss工号=b.operid(+)
and b.orgid=c.orgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_wqjl_jfzxy_sjxfc'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfc as
select a.*,b.status,b.渠道类别,nvl(case when nvl(b.是否核心渠道,''0'')=''1'' then ''是'' end,''否'') 是否核心渠道
from zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfb a,
zhyw.shc_organization_'||v_monsr||' b
where a.orgid=b.orgid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);
---微企经理直销员
zhyw.shc_drop_retable(upper('xc_qdpgtx_wqjl_zxqd_zxsj'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_wqjl_zxqd_zxsj as
select * from
(select b.county_id,b.jf_county_id,b.note,b.name,a.*,c.合伙人手机号,c.合伙人姓名,c.合伙人状态,
row_number() over (partition by a.zxqd_id,c.合伙人手机号 order by a.in_time desc ) 排名
from zhyw.xc_qdpgtx_wqjl_zxqd_bdcd a,
(select * from zhyw.rpt_county b where b.county_id<>''0'') b,
zhyw.xc_qdpgtx_wqjl_jfzxy_sjxfc c
where substr(a.operid,2,1)=b.county_id(+)
and a.zxqd_id=c.orgid(+)
and a.OPERID<>''#N/A''
and a.TYPE=''微企经理直销渠道'') a
where a.in_time in (select max(a.in_time) from zhyw.xc_qdpgtx_wqjl_zxqd_bdcd a
where a.TYPE=''微企经理直销渠道'' )
and a.合伙人状态=''1'' ';
EXECUTE IMMEDIATE (SQL_STRING);
-------------20230824 校园渠道与统管渠道
--create table zhyw.xc_qdpgtx_xiaoyuan_tongguan
--(orgid varchar2(50),type varchar2(18));
--select * from zhyw.xc_qdpgtx_xiaoyuan_tongguan for update ;
----渠道经理对应渠道关系
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdjl_dyqd_zxsj'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdjl_dyqd_zxsj as
select a.* from
(select a.片区归属区县,a.orgid,a.orgname,a.status,a.statusdate,a.unit_id,a.unit_name,
a.渠道类别,nvl(case when a.渠道类别 like ''%泛渠道%'' then ''否''end,''是'') 是否核心渠道,a.渠道经理工号,a.渠道经理,b.CREATDATE,
row_number() over (partition by a.orgid order by b.CREATDATE desc ) 排名
from zhyw.shc_organization_'||v_monsr||' a,
(select * from tbcs.ch_manager_allot@bcv b where b.status=''1'' ) b
where (a.status=1 or (a.status<>1 and a.statusdate>to_date('''||v_monsr||''',''yyyymm'')))
and a.orgid=b.orgid(+)
and a.渠道经理工号=b.EMPLOYEEID(+)
and a.是否自办渠道=''社会渠道''
and a.渠道类别 not in (''社会直销渠道'')
and a.渠道经理工号 is not null) a
where 排名=1
----20230824 评估体系内包含校园渠道及市公司统管渠道，不应计入发展量，麻烦沙工全量剔除
and a.orgid not in (select orgid from zhyw.xc_qdpgtx_xiaoyuan_tongguan ) ';
EXECUTE IMMEDIATE (SQL_STRING);
----渠道经理对应渠道关系
zhyw.shc_drop_retable(upper('xc_qdpgtx_allqd_dyqd_zxsj'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_allqd_dyqd_zxsj as
select a.* from
(select a.片区归属区县,a.orgid,a.orgname,a.status,a.statusdate,a.unit_id,a.unit_name,
a.渠道类别,nvl(case when a.渠道类别 like ''%泛渠道%'' then ''否''end,''是'') 是否核心渠道,a.渠道经理工号,a.渠道经理,b.CREATDATE,
row_number() over (partition by a.orgid order by b.CREATDATE desc ) 排名
from zhyw.shc_organization_'||v_monsr||' a,
(select * from tbcs.ch_manager_allot@bcv b where b.status=''1'' ) b
where a.orgid=b.orgid(+)
and a.渠道经理工号=b.EMPLOYEEID(+)
and a.是否自办渠道=''社会渠道''
and a.渠道经理工号 is not null) a
where 排名=1
----20230824 评估体系内包含校园渠道及市公司统管渠道，不应计入发展量，麻烦沙工全量剔除
and a.orgid not in (select orgid from zhyw.xc_qdpgtx_xiaoyuan_tongguan ) ';
EXECUTE IMMEDIATE (SQL_STRING);
-------------------------------------------各业务情况
---新入网
--（1）剔除当月停机、未活跃明细，未活跃客户定义：当月无语音或流量等通信业务。
--（2）拉新客户按照1:1.2计入,拉新口径为身份证名下6个月内未办理山东移动号码，融合拉新客户全部按照拉新客户计入。
--（3）新入网的号码需划归到C2D类集团内，未划归集团的不计入。---------分两部分，有的不限制
---折后49+
--（1）折后49元及以上套餐均计入折后49+，校园卡不计入。
--（2）折前129+按照1.5户折算。
--（3）剔除首月预存费用低于折后套餐费客户，后付费套餐不稽核该条件。
--（4）剔除当月停机、未活跃客户。
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_fanghao'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_fanghao as
select a.servnumber,a.subsid,a.qx_rg,a.registerorgid,a.createdate,a.status,a.prodid,a.prodname,b.PREPAYTYPE,
nvl(case when b.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,
a.unit_id,a.unit_name,shc.shc_func_stopkey(a.stopkey) stopkey,a.recopid,a.opername,a.oid,a.formnum,a.recdate,a.recorgid,
a.直销员电话,a.集团编号, 集团类型,nvl(case when nvl(upper(集团类型),''0'') like ''%C2%''
or nvl(upper(集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
携入类型, 是否活跃, 是否拉新,
nvl(case when a.携入类型 is null then ''否'' end,''是'') 融合拉新,
产品归档,nvl(a.预存款价值,0) 预存款价值, nvl(a.当月充值金额,0)+nvl(a.预存款价值,0) 当月缴费金额,nvl(a.jiazhi,0) jiazhi,折后价值
from zhyw.shc_fanghao_'||v_monsr||' a,
tbcs.product_define@bcv b
where a.prodid=b.prodid(+)
and a.STATUS in (''US10'')
and a.是否活跃=1
----2023年8月1日 (周二) 13:43 徐策邮件 ：电渠不算
and a.registerorgid not like ''SD.LC.DQ%''
and a.registerorgid not like ''SD.LC.YJDQ%''
and to_char(a.createdate,''yyyymm'')='''||v_monsr||'''
and a.createdate-1 <to_date('''||p_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
---新入网
--（1）剔除当月停机、未活跃明细，未活跃客户定义：当月无语音或流量等通信业务。 ---开始已删除不用考虑
--（2）拉新客户按照1:1.2计入,拉新口径为身份证名下6个月内未办理山东移动号码，融合拉新客户全部按照拉新客户计入。
--（3）新入网的号码需划归到C2D类集团内，未划归集团的不计入。---------分两部分，有的不限制
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xrw'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_xrw as
select a.SERVNUMBER, SUBSID, QX_RG, REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME,
PREPAYTYPE, 付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME, OID,FORMNUM, RECDATE,
RECORGID, 直销员电话, 集团编号, 集团类型, 划归C2D类集团,携入类型, 是否活跃, 是否拉新, 融合拉新,
产品归档, 预存款价值, 当月缴费金额, JIAZHI, 折后价值,''新入网'' type,
nvl(case when a.是否拉新=''是'' or a.融合拉新=''是'' then 1.2 end,1) 渠道社区_核算数,
nvl(case when a.是否拉新=''是'' or a.融合拉新=''是'' then 1.2 end,1)*a.划归c2d类集团 政企_核算数
from zhyw.xc_qdpgtx_gywmx_fanghao a ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xrw_zba'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_xrw_zba as
select a.*,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_xrw a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.registerorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xrw_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_xrw_hzjg as
select '''||p_day||''' cycle, a.渠道经理工号,a.渠道经理,a.registerorgid,a.渠道类别,a.是否核心渠道,count(*) 条数, sum(a.渠道社区_核算数) 总结算数
from zhyw.xc_qdpgtx_gywmx_xrw_zba a
group by a.渠道经理工号,a.渠道经理,a.registerorgid,a.渠道类别,a.是否核心渠道 ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_xrw_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_xrw_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_xrw_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
---折后49+
--（1）折后49元及以上套餐均计入折后49+，校园卡不计入。---限制条件排除
--（2）折前129+按照1.5户折算。
--（3）剔除首月预存费用低于折后套餐费客户，后付费套餐不稽核该条件。
--（4）剔除当月停机、未活跃客户。---开始已删除不用考虑
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_zh49'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_zh49 as
select a.SERVNUMBER, SUBSID, QX_RG, REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME,
PREPAYTYPE, 付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME, OID,FORMNUM, RECDATE,
RECORGID, 直销员电话, 集团编号, 集团类型, 划归C2D类集团,携入类型, 是否活跃, 是否拉新, 融合拉新,
产品归档, 预存款价值, 当月缴费金额, JIAZHI, 折后价值,''折后49'' type,
nvl(case when a.jiazhi>=129 then 1.5 end,1) 渠道社区_核算数,
nvl(case when a.jiazhi>=129 then 1.5 end,1)*a.划归c2d类集团 政企_核算数
from zhyw.xc_qdpgtx_gywmx_fanghao a
where nvl(a.折后价值,0)>=48
and a.产品归档 not in (''校园卡'')
--（3）剔除首月预存费用低于折后套餐费客户，后付费套餐不稽核该条件。
and (a.付费类型=''后付费'' or (a.付费类型=''预付费'' and a.当月缴费金额>=a.折后价值)) ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_zh49_zba'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_zh49_zba as
select a.*,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_zh49 a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.registerorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_zh49_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_zh49_hzjg as
select '''||p_day||''' cycle, a.渠道经理工号,a.渠道经理,a.registerorgid,a.渠道类别,a.是否核心渠道,count(*) 条数, sum(a.渠道社区_核算数) 总结算数
from zhyw.xc_qdpgtx_gywmx_zh49_zba a
group by a.渠道经理工号,a.渠道经理,a.registerorgid,a.渠道类别,a.是否核心渠道 ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_zh49_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_zh49_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_zh49_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----5G终端
--（1）终端合约：5G终端合约为泛全联盟统一平台供货的5G手机销量（包含直营、直供），具体指泛全联盟5G终端合约销量，包含信用购、顺差让利、5G金币等合约形式。
--（2）质量管控：终端合约顺差让利机卡分离不计入，数据取自《手机IMEI出库口径销售明细》 。
--（3）2G客户迁转：考核2G终端拍照客户完成迁转，2G客户迁转进度按照2G迁转5G完成率进行系数折算。
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_5gzd_zb'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_5gzd_zb as
select distinct a.imei,a.luru_subsid user_id,substr(a.sale_channel_id,8,1) QX_RG,
a.sale_channel_id,a.recdate CREATEDATE,b.status,b.prodid,pp.prodname,pd.PREPAYTYPE,
nvl(case when pd.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,u.town_id UNIT_ID,ut.unit_name,
shc.shc_func_stopkey(b.stopkey) stopkey,a.recopid,op.opername,a.recoid,''0'' FORMNUM,a.recdate,
a.sale_channel_id RECORGID,b5.zhixiao_hm 直销员电话,a.集团编号,a.集团类型,
nvl(case when nvl(upper(a.集团类型),''0'') like ''%C2%'' or nvl(upper(a.集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
b6.入网类型 携入类型,nvl(b6.是否活跃,0) 是否活跃,''否''是否拉新,
nvl(case when nvl(upper(b6.入网类型),''0'') like ''%转入%'' then ''是'' end,''否'') 融合拉新,
a.term_brand||a.term_code 产品归档,a.录入号码套餐价值, 录入号码前三月平均ARPU, ZUIDIXIAOFEI,a.录入号码上月套餐价值,''5G终端'' type,
a.activity_id,a.activity_lvl_id,a.active_prodname,a.xieyi_privname,a.机卡分离
from (select a.*,row_number() over (partition by a.imei,a.recoid order by a.recdate desc ) 排名
from zhyw.LSHT_zibo_term_sale'||v_monsr||' a
where imei in (select imei from zibo.zb_term_fanquan_develop_mx )) a,
zhyw.subscriber b,
tbcs.product@bcv pp,
tbcs.product_define@bcv pd,
zibo.dw_user_town_ds_'||p_day||' u,
zibo.DIM_PUB_UNIT ut,
tbcs.operator@bcv op,
zhyw.cs_order_contact b5,
zhyw.shc_all_label_mx b6
----2023年8月1日 (周二) 13:43 徐策邮件 ：电渠不算
where a.luru_subsid=b.subsid(+)
and b.prodid=pp.prodid(+)
and b.prodid=pd.prodid(+)
and a.luru_subsid=u.user_id(+)
and u.town_id=ut.unit_id(+)
and a.recopid=op.operid(+)
and a.recoid=b5.oid(+)
and a.luru_subsid=b6.subsid(+)
and a.SALE_CHANNEL_ID not like ''SD.LC.DQ%''
and a.SALE_CHANNEL_ID not like ''SD.LC.YJDQ%''
and a.排名=1
--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_qdpgtx_gywmx_5gzd_zbyl'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_5gzd_zbyl as
select * from zhyw.xc_qdpgtx_gywmx_5gzd_zb ';
EXECUTE IMMEDIATE (SQL_STRING);
---终端合约顺差让利机卡分离不计入 直接剔除
---2023年8月15日 (周二) 11:35
---5G终端口径不再将终端合约顺差让利机卡分离作为判定条件，请协助修改
--SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_5gzd_zb a where a.xieyi_privname like ''%顺差让利%'' and a.机卡分离=''机卡分离'' ';
--EXECUTE IMMEDIATE (SQL_STRING);
--commit;
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_5gzd_zba'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_5gzd_zba as
select a.*,1 结算数,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_5gzd_zb a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.sale_channel_id=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_5gzd_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_5gzd_hzjg as
select '''||p_day||''' cycle, a.渠道经理工号,a.渠道经理,a.sale_channel_id,a.渠道类别,a.是否核心渠道,count(*) 条数, sum(a.结算数) 总结算数
from zhyw.xc_qdpgtx_gywmx_5gzd_zba a
group by a.渠道经理工号,a.渠道经理,a.sale_channel_id,a.渠道类别,a.是否核心渠道 ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_5gzd_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_5gzd_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_5gzd_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
---------------2G迁转
--zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_zb'),'ZHYW');
--SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_2gqz_zb as
-- select * from
--(select a.*,row_number() over (partition by a.user_id order by TYPE_NAME desc ) 排名
--from zibo.tmp_cjie_0706_2gwl_info a
--where a.op_time=''202308'') a
--where 排名=1 ';
-- EXECUTE IMMEDIATE (SQL_STRING);
---202308 使用 zhyw.gjb_23g_qianzhuan_mx
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_2gqz_zb as
select * from zhyw.gjb_23g_qianzhuan_mx a
where to_char(op_time,''yyyymm'')=''202308''
and (qz_4g_mon+qz_5g_mon+qz_lw_mon)>=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_zba'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_2gqz_zba as
select * from
(select a.product_no,a.imei14,a.cycle,row_number() over (partition by a.product_no order by a.cycle desc,count(*) desc) 排名
from shzc.lw_wzjk_zhsy_bdcd a where a.imei14 is not null
and exists （select 1 from ZHYW.xc_qdpgtx_gywmx_2gqz_zb b where b.product_no=a.product_no )
group by a.product_no,a.imei14,a.cycle) a
where 排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_zbb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_2gqz_zbb as
select a.product_no,a.user_id,a.qx_gs county_id,a.unit_id_gs unit_id,zs flag,''0''is_jt_tac,''0''kacao,
a.type_final type_name,''0''open_volte_flag,''0''use_volte_flag,''0'' volte_flag, b.imei14,b.cycle,c.term_brand,c.term_code,c.band_4g,c.term_5g,
d.sale_channel_id,d.res_type_id,d.工号,d.终端类型,d.recoid,d.recdate,d.luru_servnumber,d.luru_subsid,d.集团编号,d.集团类型,
d.activity_id,d.active_prodname,d.activity_lvl_id,d.录入号码套餐价值,d.zuidixiaofei,d.xieyi_privname,d.机卡分离,d.录入号码上月套餐价值,
录入号码前三月平均ARPU,d.recopid
from ZHYW.xc_qdpgtx_gywmx_2gqz_zb a,
ZHYW.xc_qdpgtx_gywmx_2gqz_zba b,
ZIBO.DW_IMEI_TERMINFO_NEW_MS c,
zhyw.LSHT_zibo_term_sale'||v_monsr||' d
where a.product_no=b.product_no(+)
and substr(b.imei14,1,8)=c.tac(+)
and b.imei14=substr(d.imei,1,14) ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_zbc'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_2gqz_zbc as
select distinct a.imei14,a.user_id,substr(a.sale_channel_id,8,1) QX_RG,
a.sale_channel_id,a.recdate CREATEDATE,b.status,b.prodid,pp.prodname,pd.PREPAYTYPE,
nvl(case when pd.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,u.town_id UNIT_ID,ut.unit_name,
shc.shc_func_stopkey(b.stopkey) stopkey,a.recopid,op.opername,a.recoid,''0'' FORMNUM,a.recdate,
a.sale_channel_id RECORGID,b5.zhixiao_hm 直销员电话,a.集团编号,a.集团类型,
nvl(case when nvl(upper(a.集团类型),''0'') like ''%C2%'' or nvl(upper(a.集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
b6.入网类型 携入类型,nvl(b6.是否活跃,0) 是否活跃,''否''是否拉新,
nvl(case when nvl(upper(b6.入网类型),''0'') like ''%转入%'' then ''是'' end,''否'') 融合拉新,
a.term_brand||a.term_code 产品归档,a.录入号码套餐价值, 录入号码前三月平均ARPU, ZUIDIXIAOFEI,a.录入号码上月套餐价值,''2G迁转'' type,
a.activity_id,a.activity_lvl_id,a.active_prodname,a.xieyi_privname,a.机卡分离,a.flag,a.band_4g,a.term_5g
from ZHYW.xc_qdpgtx_gywmx_2gqz_zbb a,
zhyw.subscriber b,
tbcs.product@bcv pp,
tbcs.product_define@bcv pd,
zibo.dw_user_town_ds_'||P_DAY||' u,
zibo.DIM_PUB_UNIT ut,
tbcs.operator@bcv op,
zhyw.cs_order_contact b5,
zhyw.shc_all_label_mx b6
----2023年8月1日 (周二) 13:43 徐策邮件 ：电渠不算
where a.luru_subsid=b.subsid(+)
and b.prodid=pp.prodid(+)
and b.prodid=pd.prodid(+)
and a.luru_subsid=u.user_id(+)
and u.town_id=ut.unit_id(+)
and a.工号=op.operid(+)
and a.recoid=b5.oid(+)
and a.luru_subsid=b6.subsid(+)
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_qdpgtx_gywmx_2gqz_zbd'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_2gqz_zbd as
select a.*,1 结算数,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_2gqz_zbc a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.sale_channel_id=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_2gqz_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_2gqz_hzjg as
select '''||p_day||''' cycle, a.渠道经理工号,a.渠道经理,a.sale_channel_id,a.渠道类别,a.是否核心渠道,count(*) 条数,
sum(a.结算数) 结算数,sum(case when a.term_5g=1 then a.结算数 end) g5结算数
from zhyw.xc_qdpgtx_gywmx_2gqz_zbd a
group by a.渠道经理工号,a.渠道经理,a.sale_channel_id,a.渠道类别,a.是否核心渠道 ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_2gqz_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_2gqz_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_2gqz_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
----升档
--（1）升档政策按照10元等效计入发展量；其中非一体化套餐迁转为一体化套餐的额外增加1户，
--2023年3月-2023年5月受理升档合约在考核月取消的，按照10元等效从对应业务受理升档区县进行扣减（二次升档的不扣减）。
--（2）仅计入线下升档完成值，不包含统管渠道业务量。
--（3）强鉴权等效完成量目标值为渠道经理升档目标值的25%，完不成弱鉴权升档完成量*50%。
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zb'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zb as
select a.servnumber,a.subsid,substr(a.orgid,8,1) qx_rg,a.orgid,to_date(a.startdate,''yyyymmdd hh24:mi:ss'') CREATEDATE,
b.status,b.prodid,pp.prodname,pd.PREPAYTYPE,nvl(case when pd.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,
u.town_id UNIT_ID,ut.unit_name,shc.shc_func_stopkey(b.stopkey) stopkey,a.recopid,op.opername,a.oid recoid,''0'' FORMNUM,a.recdate,
a.orgid RECORGID,nvl(a.直销员电话,b5.zhixiao_hm) 直销员电话,a.集团编号,a.集团类型,
nvl(case when nvl(upper(a.集团类型),''0'') like ''%C2%'' or nvl(upper(a.集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
b6.入网类型 携入类型,nvl(b6.是否活跃,0) 是否活跃,''否''是否拉新,
nvl(case when nvl(upper(b6.入网类型),''0'') like ''%转入%'' then ''是'' end,''否'') 融合拉新,a.prod_type,
b6.上月arpu,b6.档次_jiazhi,a.备用字段1,a.备用字段2,''升档'' type,a.prodid activity_id,a.privid activity_lvl_id,
a.prodname active_prodname,a.privname xieyi_privname,a.鉴权
from zhyw.REPORT_MX_MONTH_'||v_monsr||' a,
zhyw.subscriber b,
tbcs.product@bcv pp,
tbcs.product_define@bcv pd,
zibo.dw_user_town_ds_'||p_day||' u,
zibo.DIM_PUB_UNIT ut,
tbcs.operator@bcv op,
zhyw.cs_order_contact b5,
zhyw.shc_all_label_mx b6
where (prod_type in (''模组升档合约新年包'',''跨升档融权益礼包'',''特殊产品专项升档'',''低端阶梯打折'')
or (prod_type like ''%新模组升级%'' and 个性化=''计入模组升级'') )
and a.subsid=b.subsid(+)
and b.prodid=pp.prodid(+)
and b.prodid=pd.prodid(+)
and a.subsid=u.user_id(+)
and u.town_id=ut.unit_id(+)
and a.recopid=op.operid(+)
and a.oid=b5.oid(+)
and a.subsid=b6.subsid(+)
and a.orgid not like ''SD.LC.DQ%''
and a.orgid not like ''SD.LC.YJDQ%''
and to_char(a.recdate,''yyyymm'')='''||v_monsr||'''
and a.recdate-1 <to_date('''||p_day||''',''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQL_STRING);
---其中非一体化套餐迁转为一体化套餐的额外增加1户，
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zbaq'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zbaq as
select e.subsid,count(*) counts from zhyw.SHC_TYTAOCAN_'||v_monsrq||' e
where nvl(e.enddate,sysdate+9999)>to_date('''||v_monsr||''',''yyyymm'')
and nvl(e.startdate,sysdate+9999)<to_date('''||v_monsr||''',''yyyymm'')
group by e.subsid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zbab'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zbab as
select e.subsid,count(*) counts from zhyw.SHC_TYTAOCAN_'||v_monsr||' e
where nvl(e.enddate,sysdate+9999)>to_date('''||v_monsrh||''',''yyyymm'')
and nvl(e.startdate,sysdate+9999)<to_date('''||v_monsrh||''',''yyyymm'')
group by e.subsid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zbb'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zbb as
select a.*,nvl(b.counts,0) 前月一体化,nvl(c.counts,0) 本月一体化,nvl(d.xl,0) 折算系数
from zhyw.xc_qdpgtx_gywmx_sheng_zb a,
zhyw.xc_qdpgtx_gywmx_sheng_zbaq b,
zhyw.xc_qdpgtx_gywmx_sheng_zbab c,
zhyw.xc_qdpgtx_gywmx_sheng_type d
where a.subsid=to_char(b.subsid(+))
and a.subsid=to_char(c.subsid(+))
and a.activity_id=d.prodid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);
----张旭冉工整理的强鉴权
-----select nvl(VERFITYNAME,VERFITYTYPE) from zhyw.zxr_dim_verfity where strong_flag=1
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zbc'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zbc as
select a.*,折算系数 + nvl(case when a.前月一体化=0 and a.本月一体化=1 then 1 end,0) 结算数,
nvl(case when nvl(a.鉴权,''0'') in (select nvl(VERFITYNAME,VERFITYTYPE) from zhyw.zxr_dim_verfity where strong_flag=1) then 1 end,0) 是否强鉴权
from zhyw.xc_qdpgtx_gywmx_sheng_zbb a ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_zbd'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_zbd as
select a.*,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_sheng_zbc a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.orgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_sheng_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_sheng_hzjg as
select '''||p_day||''' cycle, a.渠道经理工号,a.渠道经理,a.recorgid,a.渠道类别,a.是否核心渠道,count(*) 条数, sum(a.结算数) 总结算数,
sum(nvl(case when a.是否强鉴权=1 then a.结算数 end,0))强鉴权结算数,
sum(nvl(case when a.是否强鉴权=0 then a.结算数 end,0))弱鉴权结算数
from zhyw.xc_qdpgtx_gywmx_sheng_zbd a
group by a.渠道经理工号,a.渠道经理,a.recorgid,a.渠道类别,a.是否核心渠道 ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_sheng_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_sheng_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_sheng_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
---线盒新增
--（1）按照竣工口径移动高清及宽带各按照1户进行计入。
--（2）考核线盒同装率，移动高清与宽带新增比目标0.7，低于目标部分从本考核值中扣除。
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_qdpgtx_gywmx_xhxz_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zb 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_qdpgtx_gywmx_xhxz_zba'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zba as
select distinct a.servnumber,a.subsid,substr(a.recorgid,8,1) QX_RG,a.recorgid orgid,a.startdate CREATEDATE,b.status,b.prodid,pp.prodname,pd.PREPAYTYPE,
nvl(case when pd.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,u.town_id UNIT_ID,ut.unit_name,
shc.shc_func_stopkey(b.stopkey) stopkey,a.applyoperidfw recopid,op.opername,a.applyoid,''0'' FORMNUM,a.recdate,
a.recorgid RECORGID,nvl(a.zhixiao_hm,b5.zhixiao_hm) 直销员电话,b6.集团编号,b6.集团类型,
nvl(case when nvl(upper(b6.集团类型),''0'') like ''%C2%'' or nvl(upper(b6.集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
b6.入网类型 携入类型,nvl(b6.是否活跃,0) 是否活跃,''否''是否拉新,
nvl(case when nvl(upper(b6.入网类型),''0'') like ''%转入%'' then ''是'' end,''否'') 融合拉新,
a.宽带资费,a.jiazhi,a.dc,a.带宽限制,a.flag_htv,''线盒新增'' type,
a.prodid_fw,a.privname_priv,a.prodnamezt,a.宽带资费 privname,''宽带'' flag_fam
from ZHYW.xc_qdpgtx_gywmx_xhxz_zb a,
zhyw.subscriber b,
tbcs.product@bcv pp,
tbcs.product_define@bcv pd,
zibo.dw_user_town_ds_'||P_DAY||' u,
zibo.DIM_PUB_UNIT ut,
tbcs.operator@bcv op,
zhyw.cs_order_contact b5,
zhyw.shc_all_label_mx b6
where a.subsid=b.subsid(+)
and b.prodid=pp.prodid(+)
and b.prodid=pd.prodid(+)
and a.subsid=u.user_id(+)
and u.town_id=ut.unit_id(+)
and a.applyoperidfw=op.operid(+)
and a.applyoid=b5.oid(+)
and a.subsid=b6.subsid(+)';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz_zbb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zbb 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.接入费, ''0'' 赠送2个月, sysdate 赠送2个月STARTDATE, sysdate 赠送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_qdpgtx_gywmx_xhxz_zbc'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zbc as
select distinct a.servnumber,a.subsid,substr(a.recorgid,8,1) QX_RG,a.recorgid orgid,a.startdate CREATEDATE,b.status,b.prodid,pp.prodname,pd.PREPAYTYPE,
nvl(case when pd.PREPAYTYPE=''pptpPre'' then ''预付费'' end,''后付费'') 付费类型,u.town_id UNIT_ID,ut.unit_name,
shc.shc_func_stopkey(b.stopkey) stopkey,a.applyoperid recopid,op.opername,a.applyoid,''0'' FORMNUM,a.recdate,
a.recorgid RECORGID,nvl(a.zhixiao_hm,b5.zhixiao_hm) 直销员电话,b6.集团编号,b6.集团类型,
nvl(case when nvl(upper(b6.集团类型),''0'') like ''%C2%'' or nvl(upper(b6.集团类型),''0'') like ''%D%'' then 1 end,0) 划归C2D类集团,
b6.入网类型 携入类型,nvl(b6.是否活跃,0) 是否活跃,''否''是否拉新,
nvl(case when nvl(upper(b6.入网类型),''0'') like ''%转入%'' then ''是'' end,''否'') 融合拉新,
''0'' 宽带资费,0 jiazhi,''0'' dc,''0'' 带宽限制,''1'' flag_htv,''线盒新增'' type,
to_char(a.applyoid) prodid_fw,to_char(a.resid) privname_priv,a.privname prodnamezt,a.预存 privname,''高清'' flag_fam
from ZHYW.xc_qdpgtx_gywmx_xhxz_zbb a,
zhyw.subscriber b,
zhyw.product pp,
zhyw.product_define pd,
zibo.dw_user_town_ds_'||P_DAY||' u,
zibo.DIM_PUB_UNIT ut,
zhyw.operator op,
zhyw.cs_order_contact b5,
zhyw.shc_all_label_mx b6
where a.subsid=b.subsid(+)
and b.prodid=pp.prodid(+)
and b.prodid=pd.prodid(+)
and a.subsid=u.user_id(+)
and u.town_id=ut.unit_id(+)
and a.applyoperid=op.operid(+)
and a.applyoid=b5.oid(+)
and a.subsid=b6.subsid(+)';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz_zbd'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zbd as
select a.orgid,
count(distinct case when a.flag_fam=''宽带''then a.applyoid||a.subsid end) 宽带量,
count(distinct case when a.flag_fam=''高清''then a.applyoid||a.subsid end) 高清量,
round(count(distinct case when a.flag_fam=''高清''then a.applyoid||a.subsid end)/
decode(count(distinct case when a.flag_fam=''宽带''then a.applyoid||a.subsid end),0,1,
count(distinct case when a.flag_fam=''宽带''then a.applyoid||a.subsid end))*100,2) 高清宽带率
from
(select * from zhyw.xc_qdpgtx_gywmx_xhxz_zba a
union all
select * from zhyw.xc_qdpgtx_gywmx_xhxz_zbc a) a
group by a.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz_zbe'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz_zbe as
select b.*,
nvl(case when b.高清宽带率<70 then b.宽带量+ (b.高清量-ceil(b.宽带量/100*70)) end ,b.宽带量) 宽带上限
from ZHYW.xc_qdpgtx_gywmx_xhxz_zbd b ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_gywmx_xhxz as
select a.*,nvl(case when a.排名<=a.宽带上限 then 1 end,0) 结算量
from
(select a.*,
row_number() over (partition by a.orgid,a.flag_fam order by a.status,a.recdate desc ) 排名,
nvl(case when a.flag_fam=''宽带'' then b.宽带上限 end,b.高清量 ) 宽带上限
from
(select * from zhyw.xc_qdpgtx_gywmx_xhxz_zba a
union all
select * from zhyw.xc_qdpgtx_gywmx_xhxz_zbc a ) a,
ZHYW.xc_qdpgtx_gywmx_xhxz_zbe b
where a.orgid=b.orgid(+)) a ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz_zbf'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_xhxz_zbf as
select a.*,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_xhxz a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.orgid=b.orgid
and a.结算量<>0 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_gywmx_xhxz_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_gywmx_xhxz_hzjg as
select '''||P_DAY||''' cycle, a.渠道经理工号,a.渠道经理,a.recorgid,a.渠道类别,a.是否核心渠道,count(*) 条数, sum(a.结算量) 总结算数,
sum(nvl(case when a.FLAG_FAM=''宽带'' then a.结算量 end,0))宽带结算数,
sum(nvl(case when a.FLAG_FAM=''高清'' then a.结算量 end,0))高清结算数
from zhyw.xc_qdpgtx_gywmx_xhxz_zbf a
group by a.渠道经理工号,a.渠道经理,a.recorgid,a.渠道类别,a.是否核心渠道';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_gywmx_xhxz_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_gywmx_xhxz_hzjg_bdcd
select * from zhyw.xc_qdpgtx_gywmx_xhxz_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----业务汇总
--重点产能
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyw_zdcn_mx'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyw_zdcn_mx as
----新入网
select '''||v_monsr||''' cycle,SERVNUMBER, SUBSID, QX_RG, REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME, OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新,a.prodid prodid_yw,to_char(a.jiazhi) jiazhi,
to_char(a.折后价值) 折后价值, 产品归档,''新入网'' 类型, TYPE, 渠道社区_核算数 结算数,渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_xrw_zba a
union all
----折后49
select '''||v_monsr||''' cycle,SERVNUMBER, SUBSID, QX_RG, REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME, OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新,a.prodid,to_char(a.jiazhi) jiazhi,to_char(a.折后价值) 折后价值,
产品归档,''折后49'' 类型, TYPE, 渠道社区_核算数 结算数,渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_zh49_zba a
union all
----5G终端
select '''||v_monsr||''' cycle,a.imei SERVNUMBER,a.user_id SUBSID, QX_RG,a.sale_channel_id REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME,a.recoid OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新, 产品归档,a.activity_id,a.activity_lvl_id,
a.产品归档,''5G终端'' 类型, TYPE, a.结算数, 渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_5gzd_zba a
union all
----2G迁转
select '''||v_monsr||''' cycle,a.imei14 SERVNUMBER,to_number(a.user_id) SUBSID, QX_RG,a.sale_channel_id REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME,a.recoid OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新, 产品归档,a.activity_id,a.activity_lvl_id,
a.产品归档,''2G迁转'' 类型, TYPE, a.结算数, 渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_2gqz_zbd a
union all
----升档
select '''||v_monsr||''' cycle,SERVNUMBER, SUBSID, QX_RG, a.recorgid REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME,a.recoid OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新,a.鉴权 产品归档,a.activity_id,a.activity_lvl_id,
to_char(a.折算系数) dc ,a.prod_type 类型, TYPE,a.结算数 ,渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_sheng_zbd a
union all
---线盒新增
select '''||v_monsr||''' cycle,SERVNUMBER, SUBSID, QX_RG, a.recorgid REGISTERORGID, CREATEDATE, STATUS, PRODID, PRODNAME, PREPAYTYPE,
付费类型, UNIT_ID, UNIT_NAME, STOPKEY, RECOPID, OPERNAME,a.applyoid OID, FORMNUM, RECDATE, RECORGID, 直销员电话,
集团编号, 集团类型, 划归C2D类集团, 携入类型, 是否活跃, 是否拉新, 融合拉新,a.带宽限制 产品归档,a.prodid_fw,a.privname_priv,a.dc,
a.flag_fam 类型, TYPE,a.结算量 结算数 ,渠道类别, 是否核心渠道, 渠道经理工号, 渠道经理
from zhyw.xc_qdpgtx_gywmx_xhxz_zbf a ';
EXECUTE IMMEDIATE (SQL_STRING);
------渠道业务-重点产能-明细本地存档
SQL_STRING:='delete zhyw.xc_qdpgtx_qdyw_zdcn_mxbdcd a where a.cycle='''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_qdyw_zdcn_mxbdcd
select * from zhyw.xc_qdpgtx_qdyw_zdcn_mx a ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----20230824 ************************
----5G终端使用郭工过程，这个只有到区县汇总，没有号码明细无法进行清算
----渠道汇总
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyw_zdcn_qdhz'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyw_zdcn_qdhz as
select '''||P_DAY||''' cycle,a.渠道经理工号,a.渠道经理,REGISTERORGID ,渠道类别, 是否核心渠道,
count(distinct a.类型||a.type||a.oid||a.subsid) 总_条数, sum(a.结算数) 总_结算数,
count(distinct case when a.type=''新入网'' then a.类型||a.type||a.oid||a.subsid end) 新入网_条数,
sum(nvl(case when a.type=''新入网'' then a.结算数 end,0)) 新入网_结算数,
count(distinct case when a.type=''折后49'' then a.类型||a.type||a.oid||a.subsid end) 折后49_条数,
sum(nvl(case when a.type=''折后49'' then a.结算数 end,0)) 折后49_结算数,
count(distinct case when a.type=''升档'' then a.类型||a.type||a.oid||a.subsid end) 升档_条数,
sum(nvl(case when a.type=''升档'' then a.结算数 end,0)) 升档_结算数,
count(distinct case when a.type=''5G终端'' then a.类型||a.type||a.oid||a.subsid end) G5终端_条数,
sum(nvl(case when a.type=''5G终端'' then a.结算数 end,0)) G5终端_结算数,
count(distinct case when a.type=''2G迁转'' then a.类型||a.type||a.oid||a.subsid end) G2迁转_条数,
sum(nvl(case when a.type=''2G迁转'' then a.结算数 end,0)) G2迁转_结算数,
count(distinct case when a.type=''2G迁转'' and 类型=''5G终端'' then a.类型||a.type||a.oid||a.subsid end) G2转G5_条数,
sum(nvl(case when a.type=''2G迁转'' and 类型=''5G终端'' then a.结算数 end,0)) G2转G5_结算数,
count(distinct case when a.type=''线盒新增'' then a.类型||a.type||a.oid||a.subsid end) 线盒新增_条数,
sum(nvl(case when a.type=''线盒新增'' then a.结算数 end,0)) 线盒新增_结算数
from zhyw.xc_qdpgtx_qdyw_zdcn_mx a
group by a.渠道经理工号,a.渠道经理,REGISTERORGID ,渠道类别, 是否核心渠道';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_qdyw_zdcn_qdhz_bdcd a where a.cycle='''||v_monsr||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into ZHYW.xc_qdpgtx_qdyw_zdcn_qdhz_bdcd
select * from zhyw.xc_qdpgtx_qdyw_zdcn_qdhz ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----渠道经理汇总
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyw_zdcn_hz'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyw_zdcn_hz as
select '''||P_DAY||''' cycle,a.渠道经理工号,a.渠道经理,
count(distinct a.类型||a.type||a.oid||a.subsid) 总_条数, sum(a.结算数) 总_结算数,
count(distinct case when a.type=''新入网'' then a.类型||a.type||a.oid||a.subsid end) 新入网_条数,
sum(nvl(case when a.type=''新入网'' then a.结算数 end,0)) 新入网_结算数,
count(distinct case when a.type=''折后49'' then a.类型||a.type||a.oid||a.subsid end) 折后49_条数,
sum(nvl(case when a.type=''折后49'' then a.结算数 end,0)) 折后49_结算数,
count(distinct case when a.type=''升档'' then a.类型||a.type||a.oid||a.subsid end) 升档_条数,
sum(nvl(case when a.type=''升档'' then a.结算数 end,0)) 升档_结算数,
count(distinct case when a.type=''5G终端'' then a.类型||a.type||a.oid||a.subsid end) G5终端_条数,
sum(nvl(case when a.type=''5G终端'' then a.结算数 end,0)) G5终端_结算数,
count(distinct case when a.type=''2G迁转'' then a.类型||a.type||a.oid||a.subsid end) G2迁转_条数,
sum(nvl(case when a.type=''2G迁转'' then a.结算数 end,0)) G2迁转_结算数,
count(distinct case when a.type=''2G迁转'' then a.类型||a.type||a.oid||a.subsid end) G2转G5_条数,
sum(nvl(case when a.type=''2G迁转'' then a.结算数 end,0)) G2转G5_结算数,
count(distinct case when a.type=''线盒新增'' then a.类型||a.type||a.oid||a.subsid end) 线盒新增_条数,
sum(nvl(case when a.type=''线盒新增'' then a.结算数 end,0)) 线盒新增_结算数
from zhyw.xc_qdpgtx_qdyw_zdcn_mx a
group by a.渠道经理工号,a.渠道经理';
EXECUTE IMMEDIATE (SQL_STRING);
--------------渠道能力部分
-------保证金清退 2 完成当月清退目标得满分，未完成不得分。
---考核省公司拍照存量渠道保证金清退进度，按照各区县整体清退进度量化清退目标进行考核，区县财务提交报账后计入有效清退完成量。
--- 经分一期有 “保证金清退明细报表”，里面是全省拍照渠道，保证金金额及执行中保证金金额同时为0，才计入有效清退。
---统计所有淄博渠道数量，再判定保证金金额列和执行中保证金金额是否都为0，是的话记为该渠道有效清退。
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyj_qtjl_zbc'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyj_qtjl_zbc as
select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,a.bj_type
from zibo.xc_2022_photo_channel_bzj_dm a where a.bj_type=''开局'' --and a.flg=1
union all
select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,a.bj_type from
(select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,''前情0'' bj_type,
row_number() over (partition by a.channel_id order by a.op_time desc ) 排名
from zibo.xc_2022_photo_channel_bzj_dm a where --a.flg=1 and
substr(a.op_time,1,6) ='''||v_monsrq2||''') a
where 排名=1
union all
select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,a.bj_type from
(select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,''前情1'' bj_type,
row_number() over (partition by a.channel_id order by a.op_time desc ) 排名
from zibo.xc_2022_photo_channel_bzj_dm a where --a.flg=1 and
substr(a.op_time,1,6) ='''||v_monsrq||''') a
where 排名=1
union all
select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,a.bj_type from
(select a.op_time,a.CITY_ID, CITY_NAME, COUNTY_NAME, CHANNEL_ID, TOTALVAL, LOCKEDAMOUNT, DEPOSITVAL,a.flg,''本月'' bj_type,
row_number() over (partition by a.channel_id order by a.op_time desc ) 排名
from zibo.xc_2022_photo_channel_bzj_dm a where --a.flg=1 and
substr(a.op_time,1,6) ='''||v_monsr||'''
and a.op_time <= '''||p_day||''' ) a
where 排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyj_qtjl_zbd'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyj_qtjl_zbd as
select a.city_id,a.channel_id,
max(nvl(case when a.bj_type=''开局'' then a.totalval end,0)) totalval_kj,
max(nvl(case when a.bj_type=''开局'' then a.depositval end,0)) depositval_kj,
max(nvl(case when a.bj_type=''开局'' then a.op_time end,0)) op_time_kj,
max(nvl(case when a.bj_type=''前情0'' then a.totalval end,0)) totalval_qq0,
max(nvl(case when a.bj_type=''前情0'' then a.depositval end,0)) depositval_qq0,
max(nvl(case when a.bj_type=''前情0'' then a.op_time end,0)) op_time_qq0,
max(nvl(case when a.bj_type=''前情1'' then a.totalval end,0)) totalval_qq,
max(nvl(case when a.bj_type=''前情1'' then a.depositval end,0)) depositval_qq,
max(nvl(case when a.bj_type=''前情1'' then a.op_time end,0)) op_time_qq,
max(nvl(case when a.bj_type=''本月'' then a.totalval end,0)) totalval_by,
max(nvl(case when a.bj_type=''本月'' then a.depositval end,0)) depositval_by,
max(nvl(case when a.bj_type=''本月'' then a.op_time end,0)) op_time_by
from zhyw.xc_qdpgtx_qdyj_qtjl_zbc a
where flg=1
group by a.city_id,a.channel_id ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyj_qtjl_zbe'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyj_qtjl_zbe as
select * from zhyw.xc_qdpgtx_qdyj_qtjl_zbd a where a.totalval_by+a.depositval_by=0
and a.totalval_kj+a.depositval_kj<>0
and a.totalval_qq+a.depositval_qq<>0 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyj_qtjl_zbf'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyj_qtjl_zbf as
select * from
(select b.*,row_number() over (partition by b.orgid order by nvl(b.CREATDATE,sysdate-9999) desc ) 排名
from tbcs.ch_manager_allot@bcv b where b.status=''1'') b
where 排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdyj_qtjl_hzjg'),'ZHYW');
SQL_STRING:='create table zhyw.xc_qdpgtx_qdyj_qtjl_hzjg as
select '''||P_DAY||''' cycle, a.employeeid,count(distinct CHANNEL_ID) CHANNELs,max(a.totalval_kj) totalval,max(a.depositval_kj) depositval
from
(select a.*,b.employeeid
from zhyw.xc_qdpgtx_qdyj_qtjl_zbe a,
zhyw.xc_qdpgtx_qdyj_qtjl_zbf b
where a.channel_id=b.orgid) a
group by a.employeeid ';
EXECUTE IMMEDIATE (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_qdyj_qtjl_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into zhyw.xc_qdpgtx_qdyj_qtjl_hzjg_bdcd
select * from zhyw.xc_qdpgtx_qdyj_qtjl_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----渠道走访
--走访率达到100%得满分，未完成不得分
--考核核心渠道及泛渠道码店走访情况，核心渠道≥8次；泛渠道码店≥1次计入有效走访，有效走访率低于100%不计入。
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_zb as
select a.res_type,a.view_type,a.interview_id,a.res_id,a.res_name,a.res_address,a.distance_to_res,
a.view_content,a.clockin_longitude,a.clockin_latitude,a.user_account,a.user_name,a.create_date,a.modify_date,a.sign_in_time,a.sign_out_time,a.zzsc
from zibo.hwgrid_interview_record a
where to_char(a.create_date,''yyyymm'')='''||v_monsr||'''
and a.create_date-1<to_date('''||p_day||''',''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_zba'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_zba as
select a.*,b.unit_id,b.是否核心渠道,b.渠道类别,b.渠道经理工号,b.渠道经理
from ZHYW.xc_qdpgtx_qdzf_hxfqd_zb a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.res_id=b.orgid ' ;
execute immediate (SQL_STRING);
---2023年8月9日 (周三) 09:35 以该渠道归属渠道经理判定有效走访次数，一天最多计入2次。
--走访签到签退时间，间隔核心渠道必须大于30分钟；泛渠道10分钟。
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_zbb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_zbb as
select a.res_id,a.res_name,a.渠道经理工号,a.渠道经理,a.是否核心渠道,a.渠道类别,
count(distinct a.interview_id||to_char(a.create_date,''yyyymmdd'')) 走访次数,
count(distinct to_char(a.create_date,''yyyymmdd'')) 走访天数
from (select a.*,row_number() over (partition by a.res_id,a.渠道经理工号,to_char(a.create_date,''yyyymmdd'') order by a.create_date desc ) 排名
from ZHYW.xc_qdpgtx_qdzf_hxfqd_zba a
where a.user_account=a.渠道经理工号
and nvl(case when a.是否核心渠道=''是'' and to_number(nvl(regexp_substr(a.zzsc,''+[0-9]+''),0))>=30 then 1
when a.是否核心渠道=''否'' and to_number(nvl(regexp_substr(a.zzsc,''+[0-9]+''),0))>=10 then 1 end,0)=1 ) a
where 排名<3
group by a.res_id,a.res_name,a.渠道经理工号,a.渠道经理,a.是否核心渠道,a.渠道类别 ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_zbc'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_zbc as
select a.*,nvl(b.走访次数,0) 走访次数,nvl(b.走访天数,0) 走访天数
from zhyw.xc_qdpgtx_qdjl_dyqd_zxsj a,
ZHYW.xc_qdpgtx_qdzf_hxfqd_zbb b
where a.orgid=b.res_id(+) ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_zbd'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_zbd as
select a.渠道经理工号,a.渠道经理,count(distinct a.orgid) 渠道总量,
count(distinct case when a.是否核心渠道=''是'' then a.orgid end) 核心渠道数,
count(distinct case when a.是否核心渠道=''是'' and a.走访次数>0 then a.orgid end) 核心渠道走访数,
count(distinct case when a.是否核心渠道=''是'' and a.走访次数>=8 then a.orgid end) 核心渠道有效走访数,
round(count(distinct case when a.是否核心渠道=''是'' and a.走访次数>=8 then a.orgid end)/
decode(count(distinct case when a.是否核心渠道=''是'' then a.orgid end),0,1,count(distinct case when a.是否核心渠道=''是'' then a.orgid end))*100,2) 核心渠道有效走访率,
count(distinct case when a.是否核心渠道=''否'' then a.orgid end) 泛渠道数,
count(distinct case when a.是否核心渠道=''否'' and a.走访次数>=1 then a.orgid end) 泛渠道有效走访数,
round(count(distinct case when a.是否核心渠道=''否'' and a.走访次数>=1 then a.orgid end)/
decode(count(distinct case when a.是否核心渠道=''否'' then a.orgid end),0,1,count(distinct case when a.是否核心渠道=''否'' then a.orgid end))*100,2) 泛渠道有效走访率
from ZHYW.xc_qdpgtx_qdzf_hxfqd_zbc a
group by a.渠道经理工号,a.渠道经理 ' ;
execute immediate (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzf_hxfqd_hzjg'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzf_hxfqd_hzjg as
select '''||p_day||''' cycle, a.*,round((a.核心渠道有效走访数+a.泛渠道有效走访数)/a.渠道总量*100,2) 渠道总量有效走访率
from ZHYW.xc_qdpgtx_qdzf_hxfqd_zbd a ' ;
execute immediate (SQL_STRING);
SQL_STRING:='delete zhyw.xc_qdpgtx_qdzf_hxfqd_hzjg_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into ZHYW.xc_qdpgtx_qdzf_hxfqd_hzjg_bdcd
select * from ZHYW.xc_qdpgtx_qdzf_hxfqd_hzjg ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
---核心渠道高销率 商渠泛渠道活跃率
--考核核心渠道有效业务20笔及以上达标率，有效业务包含放号、存量、家庭、5G终端合约月销量≥20笔。
--考核商渠泛渠道有效业务活跃率，有效业务包含有效业务包含放号、存量、家庭、5G终端合约等重点业务。
--家庭业务 指 宽带、htv、家庭安防、路由器（智能组网）、点播年包
---魔百和_点播明细
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_qdpgtx_qdhyl_mbhdb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_mbhdb 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, ''否'' 可兑换,
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_qdpgtx_qdhyl_mbhdb_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_mbhdb_zb as
select a.servnumber,a.subsid,a.bizcode,a.applyoid,a.applyoperid,a.recorgid,a.startdate,a.enddate,
a.flag_nb,''点播年包'' type,''家庭业务'' dalei,a.price,a.jiazhi,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from shzc.xc_qdpgtx_qdhyl_mbhdb a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.recorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_aflyq'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_aflyq as
select * from zhyw.zb_yxkd_sanjiantao_subs a
where a.item in (''家庭安防'',''路由器'')
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_qdpgtx_qdhyl_aflyq_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_aflyq_zb as
select a.servnumber,a.subsid,a.privid,a.applyoid,a.applyoperid,a.recorgid,a.startdate,a.enddate,
a.flag_num,a.item type,''家庭业务'' dalei,a.rn,a.jiazhi,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from ZHYW.xc_qdpgtx_qdhyl_aflyq a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.recorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_kdgq'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_kdgq as
select * from ZHYW.xc_qdpgtx_gywmx_xhxz a
where 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('xc_qdpgtx_qdhyl_kdgq_zb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_kdgq_zb as
select a.servnumber,a.subsid,a.prodid_fw,a.applyoid,a.recopid,a.recorgid,a.createdate startdate,sysdate + 9999 enddate,
a.flag_fam,a.flag_fam type,''家庭业务''dalei,to_number(a.带宽限制) 带宽限制,a.jiazhi,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from shzc.xc_qdpgtx_qdhyl_kdgq a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.recorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
----家庭业务 五项汇总数据 ************************************************
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_jtyw'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_jtyw as
SELECT * from ZHYW.xc_qdpgtx_qdhyl_mbhdb_zb
union all
SELECT * from ZHYW.xc_qdpgtx_qdhyl_aflyq_zb
union all
SELECT * from ZHYW.xc_qdpgtx_qdhyl_kdgq_zb a ';
EXECUTE IMMEDIATE (SQL_STRING);
----放号业务 **************************************************
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_fhyw'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_fhyw as
select a.servnumber,a.subsid,a.prodid,a.oid,a.recopid,a.registerorgid,a.createdate,sysdate + 9999 enddate,
a.产品归档,''放号'' type,''放号'' dalei,a.折后价值,a.jiazhi,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_fanghao a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.registerorgid=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
----5G终端数据预留
----zhyw.xc_qdpgtx_gywmx_5gzd_zbyl
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_zdyw'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_zdyw as
select a.imei,a.user_id,a.activity_lvl_id,a.recoid,a.recopid,a.sale_channel_id,a.recdate,sysdate + 9999 enddate,
a.产品归档,''5G终端'' type,''5G终端'' dalei,a.zuidixiaofei 折后价值,a.录入号码套餐价值 jiazhi,b.渠道类别,b.是否核心渠道,b.渠道经理工号,b.渠道经理
from zhyw.xc_qdpgtx_gywmx_5gzd_zbyl a,
zhyw.xc_qdpgtx_qdjl_dyqd_zxsj b
where a.sale_channel_id=b.orgid ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_allhz'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_allhz as
select a.*,nvl(case when a.是否核心渠道=''是'' and 业务量>=20 then 1
when a.是否核心渠道=''否'' and 业务量>=1 then 1 end,0) 是否达标
from
(select a.registerorgid,a.渠道类别,a.是否核心渠道,a.渠道经理工号,a.渠道经理,
count(distinct a.subsid||a.oid||a.type||a.dalei) 业务量
from
(select * from ZHYW.xc_qdpgtx_qdhyl_fhyw a
union all
select * from ZHYW.xc_qdpgtx_qdhyl_zdyw a
union all
select * from ZHYW.xc_qdpgtx_qdhyl_jtyw a) a
group by a.registerorgid,a.渠道类别,a.是否核心渠道,a.渠道经理工号,a.渠道经理) a ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_qdjlgl'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_qdjlgl as
select a.渠道经理工号,a.渠道经理,
count(distinct case when a.是否核心渠道=''是'' then a.orgid end) 核心渠道数,
count(distinct case when a.是否核心渠道=''否'' then a.orgid end) 泛渠道数
from zhyw.xc_qdpgtx_qdjl_dyqd_zxsj a
group by a.渠道经理工号,a.渠道经理 ';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_qdjldb'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_qdjldb as
select a.渠道经理工号,a.渠道经理,
count(distinct case when a.是否核心渠道=''是'' and a.是否达标=1 then a.registerorgid end) 核心渠道达标数,
count(distinct case when a.是否核心渠道=''否'' and a.是否达标=1 then a.registerorgid end) 泛渠道达标数
from ZHYW.xc_qdpgtx_qdhyl_allhz a
group by a.渠道经理工号,a.渠道经理';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdhyl_jlhzjg'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdhyl_jlhzjg as
select a.渠道经理工号,a.渠道经理,
a.核心渠道数,nvl(b.核心渠道达标数,0) 核心渠道达标数,
round(nvl(b.核心渠道达标数,0)/decode(a.核心渠道数,0,1,a.核心渠道数)*100,2) 核心渠道达标率,
a.泛渠道数,nvl(b.泛渠道达标数,0) 泛渠道达标数,
round(nvl(b.泛渠道达标数,0)/decode(a.泛渠道数,0,1,a.泛渠道数)*100,2) 泛渠道达标率
from ZHYW.xc_qdpgtx_qdhyl_qdjlgl a,
ZHYW.xc_qdpgtx_qdhyl_qdjldb b
where a.渠道经理工号=b.渠道经理工号(+)';
EXECUTE IMMEDIATE (SQL_STRING);
---数据组合
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdjl_zdcn_rhz'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdjl_zdcn_rhz as
select a.cycle,
g.orgid,q.orgname,q.unit_id,q.unit_name,
a.渠道经理工号,
a.渠道经理,
b.核心渠道数,
b.泛渠道数,
a.总_条数,
a.总_结算数,
a.新入网_条数,
a.新入网_结算数,
a.折后49_条数,
a.折后49_结算数,
a.升档_条数,
a.升档_结算数,
a.g5终端_条数,
a.g5终端_结算数,
a.g2迁转_条数,
a.g2迁转_结算数,
a.g2转g5_条数,
a.g2转g5_结算数,
a.线盒新增_条数,
a.线盒新增_结算数,
c.核心渠道达标数,c.核心渠道达标率,
c.泛渠道达标数,c.泛渠道达标率,
nvl(y.channels,0) 清退渠道数,nvl((y.totalval+y.depositval),0)/100 清退押金,
z.核心渠道走访数, z.核心渠道有效走访数, z.核心渠道有效走访率,
z.泛渠道有效走访数, z.泛渠道有效走访率,
z.渠道总量有效走访率
from zhyw.xc_qdpgtx_qdyw_zdcn_hz a,
ZHYW.xc_qdpgtx_qdhyl_qdjlgl b,
ZHYW.xc_qdpgtx_qdhyl_qdjldba c,
zhyw.xc_qdpgtx_qdyj_qtjl_hzjg y,
ZHYW.xc_qdpgtx_qdzf_hxfqd_hzjg z,
tbcs.operator@Bcv g,
zhyw.shc_organization_'||v_monsr||' q
where a.渠道经理工号=b.渠道经理工号(+)
and a.渠道经理工号=c.渠道经理工号(+)
and a.渠道经理工号=y.employeeid(+)
and a.渠道经理工号=z.渠道经理工号(+)
and a.渠道经理工号=g.operid(+)
and g.orgid=q.orgid(+)';
EXECUTE IMMEDIATE (SQL_STRING);
--渠道经理目标
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdjl_zdcn_rhzjg'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdjl_zdcn_rhzjg as
select a.cycle,
a.orgid,
a.orgname,
a.unit_id,
a.unit_name,
a.渠道经理工号,
a.渠道经理,
a.核心渠道数,
a.泛渠道数,
a.总_条数,
a.总_结算数,
a.新入网_条数,
a.新入网_结算数,nvl(b.hx_xrw,0) 新入网_目标,
a.折后49_条数,
a.折后49_结算数,nvl(b.hx_zh49,0) 折后49_目标,
a.升档_条数,
a.升档_结算数,nvl(b.hx_sd,0) 升档_目标,
a.g5终端_条数,
a.g5终端_结算数,nvl(b.hx_5gzd,0) g5终端_目标,
a.g2迁转_条数,
a.g2迁转_结算数,nvl(b.hx_2gqz,0) g2迁转_目标,
a.g2转g5_条数,
a.g2转g5_结算数,nvl(b.hx_2gq5g,0) g2转g5_目标,
a.线盒新增_条数,
a.线盒新增_结算数,nvl(b.hx_xhxz,0) 线盒新增_目标,
a.核心渠道达标数,
a.核心渠道达标率,nvl(b.hx_hxqdgxl,0) 核心渠道高销率_目标,
a.泛渠道达标数,
a.泛渠道达标率,nvl(b.hx_sqfqdhyl,0) 商渠泛渠道活跃率_目标,
a.清退渠道数,
a.清退押金,nvl(b.hx_bzjqt,0) 保证金清退_目标,
a.核心渠道走访数,
a.核心渠道有效走访数,
a.核心渠道有效走访率,
a.泛渠道有效走访数,
a.泛渠道有效走访率,
a.渠道总量有效走访率,nvl(b.hx_qdzf,0)*100 渠道走访_目标
from ZHYW.xc_qdpgtx_qdjl_zdcn_rhz a,
zhyw.xc_qdpgtx_qdjl_mbsj_zxsj b
where a.渠道经理=b.qdjl(+)';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzg_zdcn_rhz'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzg_zdcn_rhz as
select a.cycle,substr(a.orgid,1,8) qx_id,
sum(nvl(核心渠道数,0)) 核心渠道数,
sum(nvl(泛渠道数,0)) 泛渠道数,
sum(nvl(总_条数,0)) 总_条数,
sum(nvl(总_结算数,0)) 总_结算数,
sum(nvl(新入网_条数,0)) 新入网_条数,
sum(nvl(新入网_结算数,0)) 新入网_结算数,
sum(nvl(折后49_条数,0)) 折后49_条数,
sum(nvl(折后49_结算数,0)) 折后49_结算数,
sum(nvl(升档_条数,0)) 升档_条数,
sum(nvl(升档_结算数,0)) 升档_结算数,
sum(nvl(G5终端_条数,0)) G5终端_条数,
sum(nvl(G5终端_结算数,0)) G5终端_结算数,
sum(nvl(G2迁转_条数,0)) G2迁转_条数,
sum(nvl(G2迁转_结算数,0)) G2迁转_结算数,
sum(nvl(G2转G5_条数,0)) G2转G5_条数,
sum(nvl(G2转G5_结算数,0)) G2转G5_结算数,
sum(nvl(线盒新增_条数,0)) 线盒新增_条数,
sum(nvl(线盒新增_结算数,0)) 线盒新增_结算数,
sum(nvl(核心渠道达标数,0)) 核心渠道达标数,
round(sum(nvl(核心渠道达标数,0))/ decode(sum(nvl(核心渠道数,0)),0,1,sum(nvl(核心渠道数,0))) *100,2) 核心渠道达标率,
sum(nvl(泛渠道达标数,0)) 泛渠道达标数,
round(sum(nvl(泛渠道达标数,0))/ decode(sum(nvl(泛渠道数,0)),0,1,sum(nvl(泛渠道数,0))) *100,2) 泛渠道达标率,
sum(nvl(清退渠道数,0)) 清退渠道数,
sum(nvl(清退押金,0)) 清退押金,
sum(nvl(核心渠道走访数,0)) 核心渠道走访数,
sum(nvl(核心渠道有效走访数,0)) 核心渠道有效走访数,
sum(nvl(核心渠道有效走访率,0)) 核心渠道有效走访率,
sum(nvl(泛渠道有效走访数,0)) 泛渠道有效走访数,
sum(nvl(泛渠道有效走访率,0)) 泛渠道有效走访率,
sum(nvl(渠道总量有效走访率,0)) 渠道总量有效走访率
from ZHYW.xc_qdpgtx_qdjl_zdcn_rhz a
group by a.cycle,substr(a.orgid,1,8)
union all
select a.cycle,''SD.LC'' qx_id,
sum(nvl(核心渠道数,0)) 核心渠道数,
sum(nvl(泛渠道数,0)) 泛渠道数,
sum(nvl(总_条数,0)) 总_条数,
sum(nvl(总_结算数,0)) 总_结算数,
sum(nvl(新入网_条数,0)) 新入网_条数,
sum(nvl(新入网_结算数,0)) 新入网_结算数,
sum(nvl(折后49_条数,0)) 折后49_条数,
sum(nvl(折后49_结算数,0)) 折后49_结算数,
sum(nvl(升档_条数,0)) 升档_条数,
sum(nvl(升档_结算数,0)) 升档_结算数,
sum(nvl(G5终端_条数,0)) G5终端_条数,
sum(nvl(G5终端_结算数,0)) G5终端_结算数,
sum(nvl(G2迁转_条数,0)) G2迁转_条数,
sum(nvl(G2迁转_结算数,0)) G2迁转_结算数,
sum(nvl(G2转G5_条数,0)) G2转G5_条数,
sum(nvl(G2转G5_结算数,0)) G2转G5_结算数,
sum(nvl(线盒新增_条数,0)) 线盒新增_条数,
sum(nvl(线盒新增_结算数,0)) 线盒新增_结算数,
sum(nvl(核心渠道达标数,0)) 核心渠道达标数,
round(sum(nvl(核心渠道达标数,0))/ decode(sum(nvl(核心渠道数,0)),0,1,sum(nvl(核心渠道数,0))) *100,2) 核心渠道达标率,
sum(nvl(泛渠道达标数,0)) 泛渠道达标数,
round(sum(nvl(泛渠道达标数,0))/ decode(sum(nvl(泛渠道数,0)),0,1,sum(nvl(泛渠道数,0))) *100,2) 泛渠道达标率,
sum(nvl(清退渠道数,0)) 清退渠道数,
sum(nvl(清退押金,0)) 清退押金,
sum(nvl(核心渠道走访数,0)) 核心渠道走访数,
sum(nvl(核心渠道有效走访数,0)) 核心渠道有效走访数,
sum(nvl(核心渠道有效走访率,0)) 核心渠道有效走访率,
sum(nvl(泛渠道有效走访数,0)) 泛渠道有效走访数,
sum(nvl(泛渠道有效走访率,0)) 泛渠道有效走访率,
sum(nvl(渠道总量有效走访率,0)) 渠道总量有效走访率
from ZHYW.xc_qdpgtx_qdjl_zdcn_rhz a
group by a.cycle';
EXECUTE IMMEDIATE (SQL_STRING);
zhyw.shc_drop_retable(upper('xc_qdpgtx_qdzg_zdcn_rhzjg'),'ZHYW');
SQL_STRING:='create table ZHYW.xc_qdpgtx_qdzg_zdcn_rhzjg as
SELECT A.CYCLE,b.county_id,
A.QX_ID,b.qx,
A.核心渠道数,
A.泛渠道数,
A.总_条数,
A.总_结算数,
A.新入网_条数,
a.新入网_结算数,nvl(b.hx_xrw,0) 新入网_目标,
a.折后49_条数,
a.折后49_结算数,nvl(b.hx_zq69,0) 折后49_目标,
a.升档_条数,
a.升档_结算数,nvl(b.hx_sd,0) 升档_目标,
a.g5终端_条数,
a.g5终端_结算数,nvl(b.hx_5gzd,0) g5终端_目标,
a.g2迁转_条数,
a.g2迁转_结算数,nvl(b.hx_2gqz,0) g2迁转_目标,
a.g2转g5_条数,
a.g2转g5_结算数,nvl(b.hx_2gq5g,0) g2转g5_目标,
a.线盒新增_条数,
a.线盒新增_结算数,nvl(b.hx_xhxz,0) 线盒新增_目标,
a.核心渠道达标数,
a.核心渠道达标率,nvl(b.hx_hxqdgxl,0) 核心渠道高销率_目标,
a.泛渠道达标数,
a.泛渠道达标率,nvl(b.hx_sqfqdhyl,0) 商渠泛渠道活跃率_目标,
a.清退渠道数,
a.清退押金,nvl(b.hx_bzjqt,0) 保证金清退_目标,
a.核心渠道走访数,
a.核心渠道有效走访数,
round(a.核心渠道有效走访数/核心渠道数*100,2) 核心渠道有效走访率,
a.泛渠道有效走访数,
round(泛渠道有效走访数/泛渠道数*100,2) 泛渠道有效走访率,
round((a.核心渠道有效走访数+泛渠道有效走访数)/(核心渠道数+泛渠道数)*100,2) 渠道总量有效走访率
FROM ZHYW.xc_qdpgtx_qdzg_zdcn_rhz A,
zhyw.xc_qdpgtx_qdzg_mbsj_zxsj B
WHERE A.QX_ID=B.NOTE(+)';
EXECUTE IMMEDIATE (SQL_STRING);
----渠道经理
SQL_STRING:='delete zhyw.xc_qdpgtx_qdjl_zdcn_rhz_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into ZHYW.xc_qdpgtx_qdjl_zdcn_rhz_bdcd
select * from ZHYW.xc_qdpgtx_qdjl_zdcn_rhzjg a ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
----渠道主管
SQL_STRING:='delete zhyw.xc_qdpgtx_qdzg_zdcn_rhz_bdcd a where a.cycle='''||P_DAY||''' ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
SQL_STRING:='insert into ZHYW.xc_qdpgtx_qdzg_zdcn_rhz_bdcd
select * from ZHYW.xc_qdpgtx_qdzg_zdcn_rhzjg a ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
