[大数据]20220923_数据库过程_直销员计算语句

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘gh’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_gh 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(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘ghp’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘ghp as
select a.合伙人姓名,a.合伙人手机号,a.合伙人身份证号,a.合伙人状态,to_date(a.创建时间,‘‘yyyy-mm-dd hh24:mi:ss’’) 创建时间,
to_char(to_date(a.创建时间,‘‘yyyy-mm-dd hh24:mi:ss’’),‘‘yyyymm’’) 创建月,BOSS工号 ,
a.BOSS手机号 ,a.BOSS姓名 ,a.地区编码 ,a.省份 ,a.城市 ,a.地区 ,a.状态更新时间
from
(select a.*,row_number() over (partition by a.合伙人手机号 order by a.创建时间 desc ) 排名
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_gh a
where to_date(a.创建时间,’‘yyyy-mm-dd hh24:mi:ss’‘)<to_date(’‘’||v_monsr||‘’‘,’‘yyyymm’') ) a
where a.排名 = 1 ’ ;
execute immediate (SQL_STRING);

----直销工号类型

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘ghg’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘ghg as
select a.*,nvl(case when a.创建月 in (‘’‘||v_monsrq2||’‘’,‘’‘||v_monsrq||’‘’) then ‘‘见习’’ end,‘‘正常’’) 类型
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_ghp a ’ ;
execute immediate (SQL_STRING);

—酬金数据
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘CJ’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_CJ as
select a.*,b.rulename,b.comscls rewardid_x,a.RULEID opnid,c.targetval privid,c.cycle privid_cycle
from settle.RD_ST_SUBDETAIL@choujin_new a,
zhyw.rd_cf_ruledef’||SUBSTR(v_monsrq,3,4)||‘_a b,
zhyw.rd_st_subdetailext’||SUBSTR(v_monsrq,3,4)||’ c
where a.RULEID=b.ruleid(+)
and a.oid=c.oid(+)
and a.RULEID=‘‘99920210813986’’
and SERVNUMBER is null
and POLICYTYPE=‘‘1’’
and a.REGION=‘‘533’’
and a.cycle=‘’‘||v_monsrq||’‘’
and a.ORGCYCLE=‘’‘||v_monsrq||’‘’ ’ ;
execute immediate (SQL_STRING);

 ---202207积分业务明细

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MX’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MX as
select a.*,b.rulename,b.comscls rewardid_x,a.RULEID opnid,c.targetval privid,c.cycle privid_cycle
from settle.RD_ST_SUBDETAIL@choujin_new a,
zhyw.rd_cf_ruledef’||SUBSTR(v_monsrq,3,4)||‘_a b,
zhyw.rd_st_subdetailext’||SUBSTR(v_monsrq,3,4)||’ c
where a.RULEID=b.ruleid(+)
and a.oid=c.oid(+)
and a.status=1
and POLICYTYPE=‘‘0’’
and a.REGION=‘‘533’’
and a.cycle=‘’‘||v_monsrq||’‘’ ’ ;
execute immediate (SQL_STRING);

—铁通直销员 类
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXa’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXa as
select * from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MX a
where a.rulename like ‘‘铁通直销员%’’ ’ ;
execute immediate (SQL_STRING);

—其中 新入网积分
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXb’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXb as
select * from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXa a
where a.rulename like ‘’%新入网积分%’’ ’ ;
execute immediate (SQL_STRING);

—副卡号码 --huanjing(淄博郇静) 2022 - 09-07 14:22:59
—只减0.5的，1和2的都不需要核减
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXc’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXc as
select * from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXb a
where upper(a.productid) like upper(’‘%Fuka%’')
and nvl(a.rewardval,0)=50 ’ ;
execute immediate (SQL_STRING);

—新入网免费副卡（不包含高质量携入副卡）
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXct’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXct as
select * from
(select a.TELNUM ,a.STARTTIME ,
decode(substr(a.OUTCARRIER,1,3),‘‘001’’,‘‘电信’’,‘‘002’’,‘‘移动’’,‘‘003’’,‘‘联通’’) 携出 ,
decode(substr(a.INCARRIER,1,3),‘‘001’’,‘‘电信’’,‘‘002’’,‘‘移动’’,‘‘003’’,‘‘联通’’) 携入,
a.ENDTIME ,a.TRANSTYPE ,a.STATUS ,a.ISNEW,
row_number() over (partition by a.TELNUM order by a.STARTTIME desc ) 排名
from tbcs.np_rec_lnpdb@bcv a,
(select distinct b.servnumber from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXc b ) b
where a.telnum=b.servnumber
and a.STARTTIME<to_date(’‘’||v_monsr||‘’‘,’‘yyyymm’‘)
and a.STATUS=’‘1’') a
where 排名=1 ’ ;
execute immediate (SQL_STRING);

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXcjg’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXcjg as
select * from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXct a where a.携入=’‘移动’’ ’ ;
execute immediate (SQL_STRING);

----副卡优惠情况
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXd’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXd as
select h.entityoid,h.servnumber,b.prodid,pp.prodname,b.privid,c.privname,
b.startdate,b.enddate,b.applyoperid,b.APPLYOID,
b.CANCELOID,b.CANCELOPERID
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXc h,
zhyw.subs_privilege b,
TBCS.privilege_SCHEME@BCV c,
tbcs.product@bcv pp
where h.entityoid=b.subsid(+)
and b.privid=c.privid(+)
and b.prodid=pp.prodid(+) ’ ;
execute immediate (SQL_STRING);

—新入网副卡合约–新融合副卡打折合约

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXe’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXe as
select a.entityoid,a.servnumber,a.prodid,a.prodname,a.privid,a.privname,min(a.startdate) startdate,max(a.enddate) enddate,max(a.排名) 排名
from
(select a.*,row_number() over (partition by a.entityoid order by a.startdate desc ) 排名
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXd a where a.prodname in (’‘新融合副卡打折合约’‘,’‘新入网副卡合约’')
and nvl(a.enddate,sysdate+9999)>a.startdate) a
group by a.entityoid,a.servnumber,a.prodid,a.prodname,a.privid,a.privname ’ ;
execute immediate (SQL_STRING);

----号码最早日期与最后日期，算使用月数 标记携入
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXf’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXf as
select a.entityoid,a.servnumber,max(a.prodname) prodname,
max(a.privname) privname,min(a.startdate) startdate,max(a.enddate) enddate,
nvl(t.携出||t.携入,‘‘无’’) 携出_携入
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXe a,zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXcjg t
where a.servnumber =t.telnum(+)
group by a.entityoid,a.servnumber,nvl(t.携出||t.携入,’‘无’') ’ ;
execute immediate (SQL_STRING);

—优惠使用月数
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXg’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXg as
select a.*,nvl(case when ceil(months_between(a.enddate,to_date(to_char(a.startdate,‘‘yyyymm’’)||‘‘01’’,‘‘yyyymmdd’’)))=0 then 1 end,
ceil(months_between(a.enddate,to_date(to_char(a.startdate,‘‘yyyymm’’)||‘‘01’’,‘‘yyyymmdd’’)))) 月份
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXf a ’ ;
execute immediate (SQL_STRING);

—直销员副卡优惠号码再按照酬金从高到低排序
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXh’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXh as
select a.oid,a.cycle,a.ruleid,a.rulename,a.entitytype,a.entityoid,a.servnumber,a.productid,a.recorgid,a.recdate,
a.recopid,a.recoid,a.totalperiods,a.currentperiod,a.rewardval,a.policytype,b.prodname,b.privname,b.startdate,b.enddate,b.月份,
row_number() over (partition by a.recorgid order by a.rewardval desc ) 排名
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXc a,
zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXg b
where a.entityoid=b.entityoid
and b.携出_携入=’‘无’’ ’ ;
execute immediate (SQL_STRING);

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXhz’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXhz as
select a.cycle,a.recorgid,count(distinct a.entityoid) fk_entitys,sum(a.rewardval) fk_rewardval
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXh a group by a.cycle,a.recorgid ’ ;
execute immediate (SQL_STRING);

----GRADE 省公司计算档次

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXi’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXi as
select * from settle.loc_settle_dirctseller_detail@choujin_new a
where REGION=’‘533’’ and CYCLE=‘’‘||v_monsrq||’‘’ ’ ;
execute immediate (SQL_STRING);

—t.hxyw_score>=15 and t.jdx_score>=11 and t.xqyze>=0
—免费副卡数量 与 直销员业务量 与 省公司核发酬金情况

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXj’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXj as
select a.*,b.hxyw_score,b.bzdz_score,b.DLFLAG,b.qwhkd,b.xqyze,b.grade,b.xhtz,b.jdx_score,
b.total_reward,b.first_reward,b.second_reward,nvl(c.oid,‘‘0’’) rewardval_oid,nvl(c.rewardval,0) rewardval_yuan
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXhz a,
zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXi b,
(select * from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_CJ c where c.orgcycle=’‘’||v_monsrq||‘’’ ) c
where a.recorgid=b.recorgid(+)
and a.recorgid=c.recopid(+) ’ ;
execute immediate (SQL_STRING);

/* 2022-0907
新入网免费副卡积分上限（分）
1
3
4
6
8*/

—t.hxyw_score>=15 and t.jdx_score>=11 and t.xqyze>=0

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXk’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXk as
select a.*,b.合伙人姓名,b.合伙人状态,b.创建时间,b.创建月,b.类型,
nvl(case when a.fk_entitys>=16 then a.fk_entitys-16 end,0) 五档核减数量，a.hxyw_score- nvl(case when a.fk_entitys>=16 then a.fk_rewardval/100-8 end,0) 五档核心剩余分,
nvl(case when a.fk_entitys>=12 then a.fk_entitys-12 end,0) 四档核减数量，a.hxyw_score- nvl(case when a.fk_entitys>=12 then a.fk_rewardval/100-6 end,0) 四档核心剩余分,
nvl(case when a.fk_entitys>=8 then a.fk_entitys-8 end,0) 三档核减数量，a.hxyw_score- nvl(case when a.fk_entitys>=8 then a.fk_rewardval/100-4 end,0) 三档核心剩余分,
nvl(case when a.fk_entitys>=6 then a.fk_entitys-6 end,0) 二档核减数量，a.hxyw_score- nvl(case when a.fk_entitys>=6 then a.fk_rewardval/100-3 end,0) 二档核心剩余分,
nvl(case when a.fk_entitys>=2 then a.fk_entitys-2 end,0) 见习档核减数量，a.hxyw_score- nvl(case when a.fk_entitys>=2 then a.fk_rewardval/100-1 end,0) 见习档核心剩余分
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXj a ,zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_ghg b
where a.total_reward>0
and a.recorgid=b.合伙人手机号(+) ’ ;
execute immediate (SQL_STRING);

/*update loc_settle_dirctseller_detail t set grade=1
where t.region=v_region and t.cycle=v_cycle and t.hxyw_score>=5 and t.jdx_score>=5 and t.xqyze>=15 and t.recorgid=v1.recorgid ;

         update loc_settle_dirctseller_detail t set grade=3
         where t.region=v_region and t.cycle=v_cycle and t.hxyw_score>=15 and t.jdx_score>=11 and t.xqyze>=15 and t.recorgid=v1.recorgid;
         
         update loc_settle_dirctseller_detail t set grade=4
         where t.region=v_region and t.cycle=v_cycle and t.hxyw_score>=20 and t.jdx_score>=14 and t.xqyze>=15 and t.recorgid=v1.recorgid;
         
         update loc_settle_dirctseller_detail t set grade=5
         where t.region=v_region and t.cycle=v_cycle and t.hxyw_score>=30 and t.jdx_score>=21 and t.xqyze>=15 and t.recorgid=v1.recorgid;
         
         update loc_settle_dirctseller_detail t set grade=6
         where t.region=v_region and t.cycle=v_cycle and t.hxyw_score>=40 and t.jdx_score>=28 and t.xqyze>=15 and t.recorgid=v1.recorgid;*/

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXl’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘_MXl as
select a.*,nvl(
case when a.五档核心剩余分>=40 and a.jdx_score>=28 and a.xqyze>=15 then ‘‘五档’’
when a.四档核心剩余分>=30 and a.jdx_score>=21 and a.xqyze>=15 then ‘‘四档’’
when a.三档核心剩余分>=20 and a.jdx_score>=14 and a.xqyze>=15 then ‘‘三档’’
when a.二档核心剩余分>=15 and a.jdx_score>=11 and a.xqyze>=15 then ‘‘二档’’
when a.见习档核心剩余分>=5 and a.jdx_score>=5 and a.xqyze>=15 and a.类型=’‘见习’’ then ‘‘见习’‘end,’‘不符合’’) 地市核算档次

from zhyw.RD_ST_ZXYDX_‘||SUBSTR(v_monsrq,3,4)||’_MXk a ’ ;
execute immediate (SQL_STRING);

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXm’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXm as
select a.*,nvl(case
when a.地市核算档次=‘‘五档’’ then 450000
when a.地市核算档次=‘‘四档’’ then 350000
when a.地市核算档次=‘‘三档’’ then 250000
when a.地市核算档次=‘‘二档’’ then 160000
when a.地市核算档次=‘‘见习’’ then 100000 end,0) 地市核算酬金分
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXl a ’ ;
execute immediate (SQL_STRING);

zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘MXn’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘MXn as
select a.,nvl(case when a.地市核算酬金分<a.total_reward then ‘‘需核减’’ end,‘‘无变动’’) 操作,
nvl(case when a.地市核算酬金分<a.total_reward then a.total_reward-a.地市核算酬金分 end,0) 核减酬金分,
FLOOR(nvl(case when a.地市核算酬金分<a.total_reward then a.total_reward-a.地市核算酬金分 end,0)/32) 首月核减酬金分,
nvl(case when a.地市核算酬金分<a.total_reward then a.total_reward-a.地市核算酬金分 end,0)-
FLOOR(nvl(case when a.地市核算酬金分<a.total_reward then a.total_reward-a.地市核算酬金分 end,0)/3*2) 次月核减酬金分
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXm a ’ ;
execute immediate (SQL_STRING);

—月数据存档
zhyw.shc_drop_retable(upper(‘RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||‘HJJG’),‘ZHYW’);
SQL_STRING:='create table zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||‘HJJG as
select a.*,a.first_reward-a.首月核减酬金分 首月剩余酬金分,a.second_reward-a.次月核减酬金分 次月剩余酬金分
from zhyw.RD_ST_ZXYDX’||SUBSTR(v_monsrq,3,4)||'_MXn a ’ ;
execute immediate (SQL_STRING);

—本地数据留档 核减情况
–create table zhyw.RD_ST_ZXYDX_ALL_HJJG_BD as

SQL_STRING:=‘delete zhyw.RD_ST_ZXYDX_ALL_HJJG_BD A WHERE A.CYCLE=’‘’||v_monsrq||‘’’ ’ ;
execute immediate (SQL_STRING);
COMMIT;

SQL_STRING:=‘insert into zhyw.RD_ST_ZXYDX_ALL_HJJG_BD
SELECT a.*,sysdate FROM zhyw.RD_ST_ZXYDX_’||SUBSTR(v_monsrq,3,4)||'_HJJG A
where a.total_reward-a.地市核算酬金分>0 ’ ;
execute immediate (SQL_STRING);
COMMIT;