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

----2023-0131-清算过程

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_sja’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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 a.in_time<to_date(’‘’||v_monsrh||‘’‘,’‘yyyymm’')) ';
EXECUTE IMMEDIATE (SQL_STRING);

----放号

SQL_STRING:=‘delete shzc.xc_qdcn_pgtx_qsqdtype_fhb a where a.cycle=’‘’||v_monsrq||‘’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_fhb
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_monsrq||’ a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.registerorgid=b.渠道编码
and a.recopid=t.operid(+)
and not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_fhb d where d.subsid=a.subsid and d.oid=a.oid)
and to_char(a.createdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_fhc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_fhc as
select a.*,nvl(b.status,’‘US20’') status_n
from shzc.xc_qdcn_pgtx_qsqdtype_fhb a,
(select b.subsid,b.status from zhyw.subscriber b ) b
where a.subsid=b.subsid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_fhs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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,a.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_fhc a
where a.status in (‘‘US10’’,‘‘US30’’)
and a.停机锁 in (‘‘正常在用’’,‘‘强制半停’’)
and a.是否活跃=1
and a.status_n<>‘‘US10’’ ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_zh48s’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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,a.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_fhc a
where a.status in (‘‘US10’’,‘‘US30’’)
and a.停机锁 in (‘‘正常在用’’,‘‘强制半停’’)
and a.status_n<>‘‘US10’’ ';
EXECUTE IMMEDIATE (SQL_STRING);

---

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_xhb
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.zhai_yxkd_priv_subs_‘||v_monsrq||’ 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_monsrq||’ k10,
zhyw.ZB_FAM_SUBS_‘||v_last_day||’ 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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_xhb d where d.subsid=a.subsid and d.applyoid=a.APPLYOID)
and (to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’
or to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_xhc’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_xhb a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.applyoperidfw=t.operid(+)';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_xhd
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_xhd d where d.subsid=a.subsid and d.oid=a.oid)
and (to_char(a.recdate,’‘yyyymm’‘)=’‘’||v_monsrq||‘’’
or to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’)';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_xhe’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_xhd a,
shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_xhce’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_xhce as
select distinct a.,nvl(case when to_char(a.startdate,‘‘yyyymm’’)<‘’‘||v_monsr||’‘’ then ‘‘生效’’ end,‘‘受理’’) 生效 from shzc.xc_qdcn_pgtx_qsqdtype_xhc a
union all
select distinct a.,nvl(case when to_char(a.startdate,’‘yyyymm’‘)<’‘’||v_monsr||‘’’ then ‘‘生效’’ end,‘‘受理’’) 生效 from shzc.xc_qdcn_pgtx_qsqdtype_xhe a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_xhcf’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_xhcf as
select a.*,nvl(nvl(b.status,d.status),’‘US20’‘) status_n
from shzc.xc_qdcn_pgtx_qsqdtype_xhce a,
(select b.subsid,b.status from zhyw.subscriber b) b,
zhyw.subscriber d
where a.subsid=b.subsid(+)
and a.subsid=d.subsid(+)’;
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_xhs’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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,
1 结算数,a.限定渠道,a.限定工号,a.APPLYOID,a.状态 from
(select a.,‘‘受理未生效’’ 状态 from shzc.xc_qdcn_pgtx_qsqdtype_xhcf a where a.生效=‘‘受理’’ and a.startdate>to_date(‘’‘||v_monsrh||’‘’,‘‘yyyymm’’)
union all
select a.,’‘生效后停机’’ from shzc.xc_qdcn_pgtx_qsqdtype_xhcf a where a.生效=‘‘生效’’ and a.status_n<>‘‘US10’’) a’;
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_stb
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_stb d where d.subsid=a.subsid and d.oid=a.oid)
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_stc’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_stb a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.recopid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_std
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_std d where d.subsid=a.subsid and d.applyoid=a.applyoid)
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);

      commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_ste’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_std a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_stf
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_stf d where d.subsid=a.subsid and d.applyoid=a.applyoid)and to_char(a.startdate,''yyyymm'')='''||v_monsrq||'''  ';EXECUTE IMMEDIATE (SQL_STRING);commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_stg’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_stf a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_sth
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_sth d where d.subsid=a.subsid and d.applyoid=a.applyoid)
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_sti’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_sth a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_stj
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_stj d where d.subsid=a.subsid and d.applyoid=a.applyoid)
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_stk’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_stj a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.recorgid=b.渠道编码
and a.applyoperid=t.operid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_stl
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_monsrq||‘_new a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码
and a.RECOPID=t.operid(+)
and a.prod_type =’‘千兆宽带’’
and not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_stl d where d.subsid=a.subsid and d.oid=a.oid)
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_stm’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_stm as
select a.*, nvl(b.num,0) 结算数
from shzc.xc_qdcn_pgtx_qsqdtype_stl a,
shzc.xc_qdcn_pgtx_qdtype_privnum b
where a.privid=b.privid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=’ insert into shzc.xc_qdcn_pgtx_qsqdtype_stn
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.DW_MBH_USER_HY_FLAG_‘||v_monsrq||’ 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’’)=(select to_char(max(op_time),‘‘yyyymmdd’’) from zibo.DW_MBH_USER_ANDTV_SPLX_DM a )
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 not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_stn d where d.subsid=a.subsid and d.applyoid=a.applyoid)
and to_char(a.startdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_sto’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_stn a,
shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_stp’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_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_qsqdtype_stq’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_stq as
select a.*,nvl(nvl(b.status,d.status),’‘US20’') status_n
from shzc.xc_qdcn_pgtx_qsqdtype_stp a,
(select b.subsid,b.status from zhyw.subscriber b) b,
zhyw.subscriber d
where a.subsid=b.subsid(+)
and a.subsid=d.subsid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_sts’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_sts as
select a.ITEM, a.SUBSID, a.SERVNUMBER, a.RECDATE, a.startdate, a.RECORGID, a.RECOPID, a.家庭业务设备升级, a.大类, a.直销员电话, a.TYPE,
a.预结算数 结算数, 限定渠道, 限定工号,a.oid,a.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_stq a where a.status_n<>’‘US10’’ ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_qyb
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_monsrq||’ a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.发展_渠道编码=b.渠道编码
and a.APPLYOPERID=t.operid(+)
and not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_qyb d where d.subsid=a.subsid and d.applyoid=a.applyoid)
and to_char(a.发展_考核时间,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_qyc
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_monsrq||’_new a,
shzc.xc_qdcn_pgtx_qsqdtype_sja b,
shzc.xc_qdcn_pgtx_opertype t
where a.orgid=b.渠道编码
and a.RECOPID=t.operid(+)
and a.prod_type =‘‘视频彩铃’’
and not exists ( select 1 from shzc.xc_qdcn_pgtx_qsqdtype_qyc d where d.subsid=a.subsid and d.oid=a.oid)
and to_char(a.recdate,‘‘yyyymm’’)=‘’‘||v_monsrq||’‘’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qyd’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_qyd as
select a.*,round(nvl(b.num,0),1) 结算数
from shzc.xc_qdcn_pgtx_qsqdtype_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_qsqdtype_qye’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qye 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,a.prodid,a.privid
from shzc.xc_qdcn_pgtx_qsqdtype_qyb a where 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,a.prodid,a.privid
from shzc.xc_qdcn_pgtx_qsqdtype_qyd a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qyf’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qyf as
select a.,row_number() over (partition by a.PROD_TYPE, SUBSID,APPLYOID, PRODID, PRIVID order by nvl(a.ENDDATE,sysdate+9999) desc ) 排名
from
(select distinct a.,b.enddate from shzc.xc_qdcn_pgtx_qsqdtype_qye a,
zhyw.subs_privilege b
where nvl(a.privid,’’ ‘’) <>‘’ ‘’
and a.privid=b.privid(+)
and a.applyoid=b.applyoid(+)
union all
select distinct a.*,b.enddate from shzc.xc_qdcn_pgtx_qsqdtype_qye a,
zhyw.subs_privilege b
where nvl(a.privid,‘’ ‘’) =‘’ ‘’
and a.prodid=b.prodid(+)
and a.applyoid=b.applyoid(+)) a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qyg’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_qyg as
select * from shzc.xc_qdcn_pgtx_qsqdtype_qyf a
where a.排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qyh’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qyh as
select a.*,nvl(nvl(b.status,d.status),’‘US20’') status_n
from shzc.xc_qdcn_pgtx_qsqdtype_qyg a,
(select b.subsid,b.status from zhyw.subscriber b) b,
zhyw.subscriber d
where a.subsid=b.subsid(+)
and a.subsid=d.subsid(+) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qyi’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qyi as
select * from shzc.xc_qdcn_pgtx_qsqdtype_qyh a
where a.status_n<>’‘US10’’
or nvl(a.enddate,sysdate+9999)<=to_date(‘’‘||v_monsrh||’‘’,‘‘yyyymm’’) ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qys’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qys as
select PROD_TYPE, SUBSID, SERVNUMBER, RECDATE, STARTDATE, RECORGID, APPLYOPERID, PRIVNAME, 大类, 直销员电话, TYPE, 结算数, 限定渠道, 限定工号, APPLYOID,
nvl(case when a.status_n<>’‘US10’’ then ‘‘用户停机’’ end,‘‘优惠结束’’) status_n
from shzc.xc_qdcn_pgtx_qsqdtype_qyi a ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_mxb’),‘SHZC’);
SQL_STRING:='create table shzc.xc_qdcn_pgtx_qsqdtype_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,a.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_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,b.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_zh48s b
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,f.状态
from shzc.xc_qdcn_pgtx_qsqdtype_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,g.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_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,h.status_n
from shzc.xc_qdcn_pgtx_qsqdtype_qys h ';
EXECUTE IMMEDIATE (SQL_STRING);

----每月存档

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_mxb_’||v_monsrq),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_mxb_’||v_monsrq||’ as
select * from shzc.xc_qdcn_pgtx_qsqdtype_mxb ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘delete shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd where cycle = ‘’’||v_monsrq||‘’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;

------核减数据汇总，11月已经处理的就不再处理，12月开始只核减之前计算过的。

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd
select a.*,’‘’||v_monsrq||‘’’ cycle from shzc.xc_qdcn_pgtx_qsqdtype_mxb_‘||v_monsrq||’ a
where not exists (select 1 from shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd t
where t.item=a.item
and t.subsid=a.subsid
and t.startdate=a.startdate
and t.type=a.type
and t.oid=a.oid)
and to_char(a.startdate,‘‘yyyymm’’)=‘‘2022’’||‘‘11’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
COMMIT;

----徐策计算明细，202212之后的计算范围内的。

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_jsmx_bd
select distinct a.item,a.subsid,a.startdate,a.type,a.结算数,a.oid from shzc.xc_qdcn_pgtx_qdtype_mxb_’||v_monsrq||’ a
where not exists (select 1 from shzc.xc_qdcn_pgtx_qsqdtype_jsmx_bd t
where t.item=a.item
and t.subsid=a.subsid
and t.startdate=a.startdate
and t.type=a.type
and t.oid=a.oid) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd
select a.*,’‘’||v_monsrq||‘’’ cycle from shzc.xc_qdcn_pgtx_qsqdtype_mxb_‘||v_monsrq||’ a
where not exists (select 1 from shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd t
where t.item=a.item
and t.subsid=a.subsid
and t.startdate=a.startdate
and t.type=a.type
and t.oid=a.oid)
and exists (select 1 from shzc.xc_qdcn_pgtx_qsqdtype_jsmx_bd t
where t.item=a.item
and t.subsid=a.subsid
and t.startdate=a.startdate
and t.type=a.type
and t.oid=a.oid)
and to_char(a.startdate,‘‘yyyymm’’) <> ‘‘2022’’||‘‘11’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

------2023-0130 -2022-12变动 :生态中清算量不再包括宽带权益包、千兆宽带，权益清算量不再包括视频彩铃，之前生态中的提速包项单独拿出来。

SQL_STRING:=‘delete shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd a where a.cycle >=’‘2022’‘||’‘12’’
and a.type in (‘‘宽带权益包’’,‘‘千兆宽带’’,‘‘视频彩铃’’) ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qdmxb’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qdmxb as
select ‘’’||v_monsrq||‘’’ cycle,a.大类,a.recorgid,a.type,
sum(a.结算数) 当月结算数
from shzc.xc_qdcn_pgtx_qsqdtype_mx_bdcd a where a.cycle=‘’‘||v_monsrq||’‘’
and 大类 in (‘‘核心’’,‘‘泛渠道’’)
group by a.大类,a.recorgid,a.type ';
EXECUTE IMMEDIATE (SQL_STRING);

zhyw.shc_drop_retable(upper(‘xc_qdcn_pgtx_qsqdtype_qdhz’),‘SHZC’);
SQL_STRING:=‘create table shzc.xc_qdcn_pgtx_qsqdtype_qdhz as
select ‘’’||v_monsrq||‘’’ cycle,a.大类,a.recorgid,
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=‘‘线盒新增’’ 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_qsqdtype_qdmxb a
where 大类 in (‘‘核心’’,‘‘泛渠道’’)
group by a.大类,a.recorgid ';
EXECUTE IMMEDIATE (SQL_STRING);

SQL_STRING:=‘delete shzc.xc_qdcn_pgtx_qsqdtype_qdhza a where a.in_time=’‘’||v_last_day||‘’’ ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

SQL_STRING:=‘insert into shzc.xc_qdcn_pgtx_qsqdtype_qdhza
select ‘’’||v_last_day||‘’’ in_time,a.区县,a.网格名称,a.渠道编码,
a.渠道名称,a.渠道类型,a.大类,a.职位,a.渠道归属,
nvl(b.当月新入网,0) 当月新入网,
nvl(b.当月折后48,0) 当月折后48,
nvl(b.当月线盒新增,0) 当月线盒新增,
nvl(b.当月生态业务,0) 当月生态业务,
nvl(b.当月权益彩铃,0) 当月权益彩铃,
nvl(b.当月提速包,0) 当月提速包

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