create or replace procedure zhyw.SHA_GZGC_YSYK_CS(P_DAY VARCHAR2 DEFAULT TO_CHAR(SYSDATE - 1,'yyyymmdd')) as
SQL_STRING varchar2(30000);
v_day varchar2(8);
v_day1 varchar2(8);
v_day2 varchar2(8);
v_day5 varchar2(8);
v_day_qy1 varchar2(8);
v_month varchar2(6);
v_month_1 varchar2(6);
v_month_5 varchar2(6);
v_month1 varchar2(6);
v_monthq varchar2(6);
v_monthq_1 varchar2(6);
v_last_day varchar2(8);
v_last_day_2 varchar2(8);
v_last_day2 varchar2(8);
v_last_day3 varchar2(8);
v_monthS varchar2(6);
v_monthS3 varchar2(6);
v_monsr varchar2(6);
v_monsrq varchar2(6);
v_monsrh varchar2(6);
v_monsrq2 varchar2(6);
v_monsrq3 varchar2(6);
v_monsrq4 varchar2(6);
v_monsrq5 varchar2(6);
v_monsrq6 varchar2(6);
v_monsrq12 varchar2(6);
v_P_DAY varchar2(8);
V_TAB1 varchar2(900);
zdyf varchar2(10);
begin
v_day :=to_char(sysdate-1,'yyyymmdd');
v_day1 :=to_char(sysdate-2,'yyyymmdd');
v_day2 :=to_char(sysdate-3,'yyyymmdd');
v_day_qy1 :=to_char(add_months(sysdate-2,-1),'yyyymmdd');
v_day5 :=to_char(sysdate-5,'yyyymmdd');
v_month :=to_char(add_months(sysdate-1,-0),'yyyymm');
v_month_1 :=to_char(add_months(sysdate-2,-0),'yyyymm');
v_month_5 :=to_char(add_months(sysdate-5,-1),'yyyymm');
v_month1 :=to_char(add_months(sysdate-1,1),'yyyymm');
v_monthq :=to_char(add_months(sysdate-1,-1),'yyyymm');
v_monthq_1 :=to_char(add_months(sysdate-2,-1),'yyyymm');
v_monthS :=to_char(add_months(sysdate-1,-2),'yyyymm');
v_monthS3 :=to_char(add_months(sysdate-1,-3),'yyyymm');
v_last_day :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymmdd') ;
v_last_day_2 :=to_char(last_day(add_months(sysdate-2,-1)),'yyyymmdd') ;
v_last_day2 :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymmdd') ;
v_last_day3 :=to_char(last_day(add_months(sysdate-1,-3)),'yyyymmdd') ;
v_monsr :=substr(P_DAY,1,6);
v_monsrq :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-1),'yyyymm') ;
v_monsrh :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),1),'yyyymm') ;
v_monsrq2 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-2),'yyyymm') ;
v_monsrq3 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-3),'yyyymm') ;
v_monsrq4 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-4),'yyyymm') ;
v_monsrq5 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-5),'yyyymm') ;
v_monsrq6 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-6),'yyyymm') ;
v_monsrq12 :=to_char(add_months(to_date(substr(P_DAY,1,6),'yyyymm'),-12),'yyyymm') ;
v_P_DAY :=to_char(to_date(P_DAY,'yyyymmdd')-1,'yyyymmdd') ;
zdyf :=to_char(trunc(to_date(P_DAY,'yyyymmdd'),'mm'),'yyyymm');--统计月
/*支撑集团酬金关联关系维护表,按天更新。
报表字段:集团归属区县、集团编号、集团名称、客户经理编号、客户经理名称、集团产品编码、集团产品编号,产品开户时间、
酬金比例、渠道编号、渠道名称、酬金开始时间、酬金结束时间、结算月数、审批时间*/
SQL_STRING:='delete from xxx.szc_201709_jtcjwh a where a.日期=to_char(sysdate-1,''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
commit;
---最大月份
select max(a.OBJECT_NAME) OBJECT_NAME into V_TAB1 From all_objects a
where objEct_name like UPPER('%qcy_group_subscriber_2%')
and a.OWNER='ZHYW' ;
SQL_STRING:='insert into xxx.szc_201709_jtcjwh
select to_char(sysdate-1,''yyyymmdd'') 日期,
a.oid,a.orderid,q.name1 区县,a.SUBSID,h.servnumber,a.SUBSNAME,a.SAID,a.SANAME,a.GRPPRODTYPE,
a.REWARDACCSCALE 酬金比例,a.BEGINDATE 创建时间,a.ENDDATE,a.ACCOUNTCYCLE ,
a.status,B.DICTNAME 状态,a.statusdate 状态时间,
a1.集团编号,a1.集团名称,a1.区域编码,a1.服务区县,
a1.集团产品编号,a1.产品,a1.集团产品名称,a1.客户经理,c1.opername
from tbcs.grp_subs_reward_info@bcv a,
zhyw.rpt_county q,zhyw.subscriber h,
(select * from tbcs.dict_item@bcv WHERE GROUPID=''stcm'') B,
zhyw.'||V_TAB1||' a1,
tbcs.operator@bcv c1
where substr(a.said,8,1)=q.county_id(+)
and A.STATUS=B.DICTID(+)
and a.subsid=h.subsid(+)
and a.subsid=a1.subsid(+)
and a1.客户经理=c1.operid(+)' ;
execute immediate (SQL_STRING);
commit;
---------------集团sa关联到期提醒的信息推送过程开始-----------------
fan_drop_retable(upper('jtcj_sagl_mx'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mx as
select a.日期,a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.状态,a.集团编号
from xxx.szc_201709_jtcjwh a ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_mxa'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxa as
select a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.集团编号,min(a.日期) 日期,
row_number() over (partition by a.oid order by min(a.日期) desc ) 排名
from xxx.jtcj_sagl_mx a
group by a.oid,a.subsid,a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.集团编号 ' ;
execute immediate (SQL_STRING);
---最大月份
select max(a.OBJECT_NAME) OBJECT_NAME into V_TAB1 From all_objects a
where objEct_name like UPPER('%qcy_group_subscriber_%')
and a.OWNER='ZHYW' ;
fan_drop_retable(upper('jtcj_sagl_mxb'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxb as
select a.oid,a.subsid,b.acctid,b.集团名称,b.集团产品名称,a.said,a.grpprodtype,
a.酬金比例,a.日期 最早关联日期,to_char(a.创建时间,''yyyymm'') 关联时选择开始月,
a.accountcycle 有效月数,
nvl(case when to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') >=to_char(a.创建时间,''yyyymm'') then
to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') end,to_char(a.创建时间,''yyyymm'')) 实际开始月,
nvl(case when to_char(add_months(to_date(a.日期,''yyyymmdd''),-1),''yyyymm'') =to_char(a.创建时间,''yyyymm'') then
to_char(add_months(to_date(a.日期,''yyyymmdd''),-1+a.accountcycle-1),''yyyymm'') end,
to_char(add_months(a.创建时间,a.accountcycle-1),''yyyymm'')) 实际结束月,b.产品编码
from xxx.jtcj_sagl_mxa a,
zhyw.'||V_TAB1||' b
where a.subsid=b.subsid(+)
and a.排名=1 ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_mxc'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxc as
select distinct a.*,b.RULETYPE,b.VALUEDESC ATTR
from xxx.jtcj_sagl_mxb a ,
(select * from settle.ch_ngsettle_ruleconstvalue@choujin_new b where RULETYPE <>''P_GROUP_PRIV'') b
where a.产品编码=b.REMUNERAT(+) ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_mxd'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxd as
select distinct b.RULETYPE,b.REMUNERAT,b.VALUEDESC,c.itemname
from settle.ch_ngsettle_ruleconstvalue@choujin_new b,
(select distinct a.ruletype,a.ruletype,a.attr from xxx.jtcj_sagl_mxc a where a.ruletype is not null ) a,
account.acctitem_def@zwbcv c
where b.REMUNERAT=a.attr
and b.VALUEDESC=c.itemcode(+)
and b.RULETYPE=''P_GROUP_BILLID'' ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_mxe'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxe as
select * from xxx.szc_201709_jtcjwh a
where a.日期=(select max(a.日期) from xxx.szc_201709_jtcjwh a ) ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_mxf'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxf as
select a.*,b.status,b.状态,b.状态时间,b.产品,b.客户经理,b.opername,c.CONTACTPHONE
from xxx.jtcj_sagl_mxc a ,
xxx.jtcj_sagl_mxe b ,
tbcs.operator@bcv c
where a.oid=b.oid(+)
and b.客户经理=c.operid(+)
and 状态=''在网''
and a.实际结束月 in (to_char(sysdate,''yyyymm''),to_char(add_months(sysdate,1),''yyyymm'')) ' ;
execute immediate (SQL_STRING);
--比如客户经理a负责一个集团产品酬金,当计算到结束月份为次月时,就会给客户经理发一条微信和短信通知。
--内容:设置流水+集团名称+subsid +sa渠道编码+业务的类型+有效月数+实际结束月+酬金比例,即将到期,请核实是否需要重新关联,谢谢合作
fan_drop_retable(upper('jtcj_sagl_mxfs'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_mxfs as
select to_char(sysdate,''yyyymm'') cycle,
a.oid,a.subsid,a.said,a.酬金比例,a.有效月数,a.实际结束月,a.产品编码,a.产品,a.客户经理,a.opername,a.contactphone,
''您好!您有一条集团SA酬金:''||a.oid||'',''||a.集团名称||''_''||a.subsid||'' 对应渠道:''||a.said||'' 业务类型:''||
a.产品||'' 有效月数:''||a.有效月数||'' 结束月份:''||a.实际结束月||'' 酬金比例:''||a.酬金比例*100||''%,即将到期,请核实是否需要重新关联,谢谢合作'' 发送内容,0 发送状态
from xxx.jtcj_sagl_mxf a
where a.contactphone is not null ' ;
execute immediate (SQL_STRING);
SQL_STRING:='INSERT INTO xxx.jtcj_sagl_mx_fsh
select * from xxx.jtcj_sagl_mxfs a
where not exists (select * from xxx.jtcj_sagl_mx_fsh b where a.cycle=b.cycle
and a.oid=b.oid ) ' ;
execute immediate (SQL_STRING);
SQL_STRING:='INSERT INTO zhyw.shc_sendmessage_weixin (SERVNUMBER ,INSERTTIME ,SENDSTAFF, STATUS, CONTENT1)
select a.contactphone,sysdate,a.客户经理,1,a.发送内容
from xxx.jtcj_sagl_mx_fsh a where a.发送状态=0 ' ;
execute immediate (SQL_STRING);
SQL_STRING:='update xxx.jtcj_sagl_mx_fsh a set a.发送状态=1 where a.发送状态=0 ' ;
execute immediate (SQL_STRING);
commit;
---20200529
--集团酬金有查询,到期提醒,我看看再加一个欠费月份提醒吧,比如一个集团酬金关联的账单欠费三个月到五个月的时候提醒
---集团产品sa关联
fan_drop_retable(upper('jtcj_sagl_qlsj'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_qlsj as
select a.*,b.status,b.状态,b.状态时间,b.产品,b.客户经理,b.opername,c.CONTACTPHONE
from xxx.jtcj_sagl_mxc a ,
xxx.jtcj_sagl_mxe b ,
tbcs.operator@bcv c
where a.oid=b.oid(+)
and b.客户经理=c.operid(+)
and (b.状态=''在网''
or (b.状态<>''在网'' and b.状态时间>sysdate-360 )) ' ;
execute immediate (SQL_STRING);
---最大月份
select max(a.OBJECT_NAME) OBJECT_NAME into V_TAB1 From all_objects a
where objEct_name like UPPER('%qcy_group_subs_member_2%')
and a.OWNER='ZHYW' ;
fan_drop_retable(upper('jtcj_sagl_qlsjhm'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm as
select distinct a.oid,a.subsid,c.servnumber,a.acctid,a.said,
a.有效月数,
a.酬金比例,
nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月) 实际开始月,
a.实际结束月,
a.集团名称,
a.集团产品名称,
a.产品编码,
a.attr,
nvl(b.subsoid,a.subsid) subsoid,
nvl(b.memservnumber,c.servnumber) memservnumber,
nvl(b.acctoid,a.acctid) acctoid,
nvl(b.加入集团时间, to_date(nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月),''yyyymm'') ) 加入集团时间,
row_number() over (partition by a.oid, nvl(b.subsoid,a.subsid)
order by nvl(b.加入集团时间, to_date(nvl(case when a.最早关联日期=''2017''||''0929'' then a.关联时选择开始月 end, a.实际开始月),''yyyymm'') ) desc ) 排名
from xxx.jtcj_sagl_qlsj a,
zhyw.'||V_TAB1||' b,
zhyw.subscriber c
where a.subsid=b.subsid(+)
and a.subsid=c.subsid(+)
---结束时间选择6个月内
and a.实际结束月>=to_char(sysdate-31*6,''yyyymm'') ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_qlsjhm_qf'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm_qf as
select b.oid,b.实际开始月,b.实际结束月,b.加入集团时间,a.ACCTID,a.SUBSID,
substr(a.BILLCYCLE,1,6) BILLCYCLE ,a.ITEMTYPE ,a.ITEMCODE,c.itemname,a.FEE
from account.v_billitem_owe533@zwbcv a,
xxx.jtcj_sagl_qlsjhm b ,
xxx.jtcj_sagl_mxd c
where a.subsid=b.subsoid
and substr(a.BILLCYCLE,1,6) >=b.实际开始月
and substr(a.BILLCYCLE,1,6) <=b.实际结束月
and b.attr=c.remunerat(+)
and a.ITEMCODE=c.valuedesc(+)
and a.ITEMTYPE<4 ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_sagl_qlsjhm_qfhz'),'xxx');
SQL_STRING:='create table xxx.jtcj_sagl_qlsjhm_qfhz as
select a.oid,
count(distinct a.subsid) 欠费号码数,
count(distinct a.billcycle) 欠费账期数,
min(a.billcycle) 最早欠费账期,
max(a.billcycle) 最近欠费账期,
sum(a.fee)/100 欠费金额元,
count(distinct (case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end)) 近六月欠费账期数,
nvl(min(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end),''无'') 近六月最早欠费账期,
nvl(max(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.billcycle end),''无'') 近六月最近欠费账期,
sum(nvl(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'') then a.fee end,0))/100 近六月欠费金额元,
sum(nvl(case when a.billcycle>=to_char(add_months(sysdate,-5),''yyyymm'')
and a.itemname is not null and to_char(a.加入集团时间,''yyyymm'')<=a.billcycle then a.fee end,0))/100 近六月可返酬欠费金额元
from xxx.jtcj_sagl_qlsjhm_qf a
group by a.oid ' ;
execute immediate (SQL_STRING);
SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzm a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzm
select TO_CHAR(SYSDATE - 1,''yyyymmdd'') 日期, a.*,b.欠费号码数,b.欠费账期数,b.最早欠费账期,b.最近欠费账期,b.欠费金额元,
b.近六月欠费账期数,b.近六月最早欠费账期,b.近六月最近欠费账期,b.近六月欠费金额元,b.近六月可返酬欠费金额元
from xxx.jtcj_sagl_qlsj a,
xxx.jtcj_sagl_qlsjhm_qfhz b
where a.oid=b.oid ' ;
execute immediate (SQL_STRING);
SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzh a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzh
select a.日期,a.said,a.客户经理,a.opername,a.contactphone,a.集团名称,
count(distinct a.subsid) 欠费产品编码,
sum(a.近六月欠费账期数) 近六月欠费账期数,
min(a.近六月最早欠费账期) 近六月最早欠费账期,
max(a.近六月最近欠费账期) 近六月最近欠费账期,
sum(a.近六月欠费金额元) 近六月欠费金额元,
sum(a.近六月可返酬欠费金额元) 近六月可返酬欠费金额元
from xxx.jtcj_sagl_qlsjhm_qfhzm a
where a.近六月可返酬欠费金额元>0
and a.日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'')
group by a.日期,a.said,a.客户经理,a.opername,a.contactphone,a.集团名称 ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=' delete xxx.jtcj_sagl_qlsjhm_qfhzh_dx a where 日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'') ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=' insert into xxx.jtcj_sagl_qlsjhm_qfhzh_dx
select a.日期,a.said,a.集团名称,
a.客户经理,a.opername,a.contactphone,
''您好!截止:''||a.日期||'',集团名称:''||a.集团名称||'' 关联SA编码:''||a.said
||'' 欠费产品数:''||a.欠费产品编码||''个,近六个月内欠费月数:''
||a.近六月欠费账期数||''个月,最早欠费账期:''
||a.近六月最早欠费账期||''元,欠费金额:''
||a.近六月欠费金额元||''元,欠费将影响系统酬金正常核发,请及时关注处理。'' 发送内容,0 发送状态
from xxx.jtcj_sagl_qlsjhm_qfhzh a
where a.近六月可返酬欠费金额元>0
and a.日期=TO_CHAR(SYSDATE - 1,''yyyymmdd'')
and substr(a.日期,7,2) in (''05'',''20'') ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:='INSERT INTO zhyw.shc_sendmessage_weixin (SERVNUMBER ,INSERTTIME ,SENDSTAFF, STATUS, CONTENT1)
select a.contactphone,sysdate,a.客户经理,1,a.发送内容
from xxx.jtcj_sagl_qlsjhm_qfhzh_dx a where a.发送状态=0 ' ;
execute immediate (SQL_STRING);
SQL_STRING:='update xxx.jtcj_sagl_qlsjhm_qfhzh_dx a set a.发送状态=1 where a.发送状态=0 ' ;
execute immediate (SQL_STRING);
commit;
end;