create or replace procedure billerxxx.xxx_202005_jtcj_jfdr_gc
(p_userid varchar2,p_cycle varchar2,p_lx varchar2,p_cursor in out Results.ref_cursor_type)
as
v_sql VARCHAR2(6000);
SQL_STRING VARCHAR2(6000);
v_cycle_1 VARCHAR2(10); ----起月
v_cycle_2 VARCHAR2(10);
v_cycle_3 VARCHAR2(10);
v_cycle_4 VARCHAR2(10);
v_cycle_5 VARCHAR2(10);
v_cycle_6 VARCHAR2(10);
v_cycle_d VARCHAR2(10);
v_lx VARCHAR2(20);
begin
v_cycle_1 :=substr(p_cycle,1,6);
v_cycle_2 :=to_char(add_months(to_date(substr(p_cycle,1,6),'yyyymm'),1),'yyyymm') ; ----起月1
v_cycle_3 :=to_char(add_months(to_date(substr(p_cycle,1,6),'yyyymm'),2),'yyyymm') ; ----起月2
v_cycle_4 :=to_char(add_months(to_date(substr(p_cycle,1,6),'yyyymm'),3),'yyyymm') ; ----起月3
v_cycle_5 :=to_char(add_months(to_date(substr(p_cycle,1,6),'yyyymm'),4),'yyyymm') ; ----起月4
v_cycle_6 :=to_char(add_months(to_date(substr(p_cycle,1,6),'yyyymm'),5),'yyyymm') ; ----起月5
v_cycle_d :=to_char(sysdate-10,'yyyymm');
v_lx :=trim(p_lx);
-----------------------------过程开始-----------------
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程1--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
fan_drop_retable(upper('jtcj_jfdr_subsid_mx'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mx as
select distinct b.id,b.subsid subsid_sr,b.user_id,b.in_time,a.日期,a.oid,a.subsid,
a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.状态,a.集团编号
from xxx.jtcj_jfdr_subsid b ,
(select distinct b.id,b.subsid subsid_sr,b.user_id,b.in_time,a.日期,a.oid,a.subsid,
a.said,a.grpprodtype,a.酬金比例,a.创建时间,a.accountcycle,a.状态,a.集团编号
from xxx.jtcj_jfdr_subsid b,
xxx.szc_201709_jtcjwh a
where nvl(case when instr(b.subsid,''.'',1,1)>1 then a.said end,a.subsid)= b.subsid) a
where b.id=a.id(+) ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_jfdr_subsid_mxa'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mxa as
select a.id,a.user_id,a.in_time,a.oid,a.subsid,a.said,a.grpprodtype,
a.酬金比例,a.创建时间,a.accountcycle,a.集团编号,min(a.日期) 日期,
row_number() over (partition by a.id,a.oid,a.subsid order by min(a.日期) desc ) 排名
from xxx.jtcj_jfdr_subsid_mx a
group by a.id,a.user_id,a.in_time,a.oid,a.subsid,a.said,a.grpprodtype,
a.酬金比例,a.创建时间,a.accountcycle,a.集团编号 ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_jfdr_subsid_mxb'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mxb as
select a.id,a.user_id,a.in_time,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_jfdr_subsid_mxa a,
zhyw.qcy_group_subscriber_'||v_cycle_d||' b
where a.subsid=b.subsid(+)
and a.排名=1 ' ;
execute immediate (SQL_STRING);
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程2--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
fan_drop_retable(upper('jtcj_jfdr_subsid_mxc'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mxc as
select distinct a.*,b.RULETYPE,b.VALUEDESC ATTR
from xxx.jtcj_jfdr_subsid_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_jfdr_subsid_mxd'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mxd as
select distinct b.RULETYPE,b.REMUNERAT,b.VALUEDESC
from settle.ch_ngsettle_ruleconstvalue@choujin_new b,
(select distinct a.ruletype,a.ruletype,a.attr from xxx.jtcj_jfdr_subsid_mxc a where a.ruletype is not null ) a
where b.REMUNERAT=a.attr
and b.RULETYPE=''P_GROUP_BILLID'' ' ;
execute immediate (SQL_STRING);
------------------集团成员关联-----------物联网卡涉及太多,暂时先考虑本号码的。
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程3--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
--销账表 ----关联的号码
fan_drop_retable(upper('jtcj_jfdr_subsid_xzjl'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_xzjl as
select t.acctid,substr(t.billcycle,1,6) billcycle,t.subsid,t.itemcode,t.fee,t.wrtoffdate,t.WRTOFFSUBJECT
from account.am_billitem533@zwbcv t,
xxx.jtcj_jfdr_subsid_mxc b,
xxx.jtcj_jfdr_subsid_mxd d
where t.subsid =b.subsid
and substr(t.billcycle,1,6)>=b.实际开始月
and substr(t.billcycle,1,6)<=b.实际结束月
and b.attr=d.remunerat
and t.ITEMCODE=d.valuedesc ---取集团产品对应的销账科目
and t.wrtoffsubject not in (''DSLATE'',''acsbGft'',''acsbNopay'',''acsbCompFee'',''acsbRemitPrepay'',''acsbSjzfPresent'') --去除赠费
and t.wrtoffsubject not like ''z%''--去除赠费
and t.itemflag = ''1''
and t.itemtype in (''1'', ''2'', ''3'') ' ;
execute immediate (SQL_STRING);
fan_drop_retable(upper('jtcj_jfdr_subsid_xzjlh'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_xzjlh as
select a.subsid,a.billcycle,sum(a.fee) fee,
sum(nvl(case when substr(a.billcycle,1,6)>=to_char(add_months(a.wrtoffdate,-5),''yyyymm'') then a.fee end,0)) fee_6yxz,
row_number() over (partition by a.subsid order by a.billcycle ) 排名
from xxx.jtcj_jfdr_subsid_xzjl a
group by a.subsid,a.billcycle ' ;
execute immediate (SQL_STRING);
--销账表 ----关联的acctid
fan_drop_retable(upper('jtcj_jfdr_subsid_xzzjl'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_xzzjl as
select t.acctid,substr(t.billcycle,1,6) billcycle,t.subsid,t.itemcode,t.fee,t.wrtoffdate,t.WRTOFFSUBJECT
from account.am_billitem533@zwbcv t,
xxx.jtcj_jfdr_subsid_mxc b,
xxx.jtcj_jfdr_subsid_mxd d
where t.acctid =b.acctid
and substr(t.billcycle,1,6)>=b.实际开始月
and substr(t.billcycle,1,6)<=b.实际结束月
and b.attr=d.remunerat
and t.ITEMCODE=d.valuedesc ---取集团产品对应的销账科目
and t.wrtoffsubject not in (''DSLATE'',''acsbGft'',''acsbNopay'',''acsbCompFee'',''acsbRemitPrepay'',''acsbSjzfPresent'') --去除赠费
and t.wrtoffsubject not like ''z%''--去除赠费
and t.itemflag = ''1''
and t.itemtype in (''1'', ''2'', ''3'') ' ;
execute immediate (SQL_STRING);
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程4--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
fan_drop_retable(upper('jtcj_jfdr_subsid_xzzjlh'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_xzzjlh as
select a.acctid,a.billcycle,max(a.subsid) subsid,sum(a.fee) fee,
sum(nvl(case when substr(a.billcycle,1,6)>=to_char(add_months(a.wrtoffdate,-5),''yyyymm'') then a.fee end,0)) fee_6yxz,
row_number() over (partition by a.acctid order by a.billcycle ) 排名
from xxx.jtcj_jfdr_subsid_xzzjl a
group by a.acctid,a.billcycle ' ;
execute immediate (SQL_STRING);
------------------
fan_drop_retable(upper('jtcj_jfdr_subsid_mxe'),'xxx');
SQL_STRING:='create table xxx.jtcj_jfdr_subsid_mxe as
select distinct a.id,a.user_id,a.in_time,a.OID ,a.SUBSID,a.集团名称,a.集团产品名称 ,a.ACCTID ,
a.SAID ,q.渠道名称,q.区县,a.GRPPRODTYPE ,a.酬金比例 ,a.最早关联日期 ,a.关联时选择开始月 ,
a.有效月数 ,a.实际开始月 ,a.实际结束月 ,a.产品编码 ,pp.prodname,a.RULETYPE ,a.ATTR,
'''||v_cycle_1||''' billcycle_1 ,
nvl(b1.fee,0)/100 subsid_fee_1,
nvl(b1.fee_6yxz,0)/100 subsid_fee_6yxz_1,
nvl(c1.fee,0)/100 acctid_fee_1,
nvl(c1.fee_6yxz,0)/100 acctid_fee_6yxz_1,
c1.subsid acctid_subsid_1,
'''||v_cycle_2||''' billcycle_2 ,
nvl(b2.fee,0)/100 subsid_fee_2,
nvl(b2.fee_6yxz,0)/100 subsid_fee_6yxz_2,
nvl(c2.fee,0)/100 acctid_fee_2,
nvl(c2.fee_6yxz,0)/100 acctid_fee_6yxz_2,
c2.subsid acctid_subsid_2,
'''||v_cycle_3||''' billcycle_3 ,
nvl(b3.fee,0)/100 subsid_fee_3,
nvl(b3.fee_6yxz,0)/100 subsid_fee_6yxz_3,
nvl(c3.fee,0)/100 acctid_fee_3,
nvl(c3.fee_6yxz,0)/100 acctid_fee_6yxz_3,
c3.subsid acctid_subsid_3,
'''||v_cycle_4||''' billcycle_4 ,
nvl(b4.fee,0)/100 subsid_fee_4,
nvl(b4.fee_6yxz,0)/100 subsid_fee_6yxz_4,
nvl(c4.fee,0)/100 acctid_fee_4,
nvl(c4.fee_6yxz,0)/100 acctid_fee_6yxz_4,
c4.subsid acctid_subsid_4,
'''||v_cycle_5||''' billcycle_5 ,
nvl(b5.fee,0)/100 subsid_fee_5,
nvl(b5.fee_6yxz,0)/100 subsid_fee_6yxz_5,
nvl(c5.fee,0)/100 acctid_fee_5,
nvl(c5.fee_6yxz,0)/100 acctid_fee_6yxz_5,
c5.subsid acctid_subsid_5,
'''||v_cycle_6||''' billcycle_6 ,
nvl(b6.fee,0)/100 subsid_fee_6,
nvl(b6.fee_6yxz,0)/100 subsid_fee_6yxz_6,
nvl(c6.fee,0)/100 acctid_fee_6,
nvl(c6.fee_6yxz,0)/100 acctid_fee_6yxz_6,
c6.subsid acctid_subsid_6
from xxx.jtcj_jfdr_subsid_mxc a,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_1||''') b1,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_2||''') b2,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_3||''') b3,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_4||''') b4,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_5||''') b5,
(select * from xxx.jtcj_jfdr_subsid_xzjlh b where b.billcycle='''||v_cycle_6||''') b6,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_1||''') c1,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_2||''') c2,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_3||''') c3,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_4||''') c4,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_5||''') c5,
(select * from xxx.jtcj_jfdr_subsid_xzzjlh c where c.billcycle='''||v_cycle_6||''') c6,
tbcs.product@bcv pp,
xxx.tmp_szc_201511_dlhz q
where a.subsid=b1.subsid(+)
and a.subsid=b2.subsid(+)
and a.subsid=b3.subsid(+)
and a.subsid=b4.subsid(+)
and a.subsid=b5.subsid(+)
and a.subsid=b6.subsid(+)
and a.acctid=c1.acctid(+)
and a.acctid=c2.acctid(+)
and a.acctid=c3.acctid(+)
and a.acctid=c4.acctid(+)
and a.acctid=c5.acctid(+)
and a.acctid=c6.acctid(+)
and a.产品编码=pp.prodid(+)
and a.said=q.渠道编码(+) ' ;
execute immediate (SQL_STRING);
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程5--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
------汇总展示
SQL_STRING:='insert into xxx.jtcj_jfdr_subsid_mx_g
select * from xxx.jtcj_jfdr_subsid_mxe a
where not exists (select 1 from xxx.jtcj_jfdr_subsid_mx_g b where a.id=b.id ) ' ;
execute immediate (SQL_STRING);
commit;
-----查询执行后将导入数据清除
SQL_STRING:='delete from xxx.jtcj_jfdr_subsid a where a.id in (
select id from xxx.jtcj_jfdr_subsid_mx_g ) ' ;
execute immediate (SQL_STRING);
commit;
SQL_STRING:=' insert into xxx.xxx_ssgc_zxqk
select sysdate in_date,''biller533.xxx_202005_jtcj_jfdr_gc--过程6--'' 过程进展 from dual ' ;
execute immediate (SQL_STRING);
commit;
if v_lx='当前查询' then
---前台展示生成的数据
v_sql:=' select * from xxx.jtcj_jfdr_subsid_mxe order by id desc ' ;
else
v_sql:=' select * from xxx.jtcj_jfdr_subsid_mx_g a where a.user_id='''||p_userid||''' order by id desc ' ;
end if ;
open p_cursor for v_sql;
end ;