1108 lines
62 KiB
MySQL
1108 lines
62 KiB
MySQL
|
-- 结算期间 需要修改 cu_member_settle_period_seq
|
|||
|
|
insert into cu_member_settle_period(pk_id, settle_date, is_settle, PUBLISH_DATE, IS_PUBLISH, GRANT_DATE, IS_GRANT,
|
|||
|
|
PK_CREATOR)
|
|||
|
|
select PERIOD pk_id,
|
|||
|
|
START_DATE settle_date,
|
|||
|
|
(case STATUS when 1 then 0 else 1 end) is_settle,
|
|||
|
|
PUBLISH_DATE,
|
|||
|
|
(case IS_PUBLISH when 1 then 0 else 1 end) IS_PUBLISH,
|
|||
|
|
PAY_DATE GRANT_DATE,
|
|||
|
|
(case IS_PAY when 1 then 0 else 1 end) IS_GRANT,
|
|||
|
|
100000000 PK_CREATOR
|
|||
|
|
from g_bonus_period_day@link_test;
|
|||
|
|
|
|||
|
|
-- 明细初始化 /member/manage/settle/init-period
|
|||
|
|
--select max(pk_id) from cu_member_settle_period_seq;
|
|||
|
|
|
|||
|
|
|
|||
|
|
--奖金结算表 cu_member_settle881
|
|||
|
|
insert into cu_member_settle881 (PERIOD, pk_member, PK_PARENT, PK_PLACE_PARENT, PLACE_DEPT, MEMBER_CODE, MEMBER_NAME,
|
|||
|
|
CUSTOMER_TYPE,
|
|||
|
|
PHONE, PK_GRADE, PK_AWARDS, ACCOUNT_STATUS, PAY_STATUS, REGISTER_AUTHORITY, CATEGORY,
|
|||
|
|
PURCHASE_TYPE, PURCHASE_STATUS, SHARE_STATUS,
|
|||
|
|
INCOME_STATUS, PK_TEAM_CODE, PK_CENTER_CODE, PK_VERTEX, PK_SETTLE_COUNTRY, PK_COUNTRY,
|
|||
|
|
PK_RATE, A_BALANCE, b_BALANCE,
|
|||
|
|
A_SUM_AMOUNT, b_SUM_AMOUNT, A_SUM_PV, B_SUM_PV, A_NEW_AMOUNT, B_NEW_AMOUNT, A_NEW_PV,
|
|||
|
|
b_NEW_PV, A_SUM_REAL_AMOUNT, B_SUM_REAL_AMOUNT,
|
|||
|
|
A_SUM_REAL_PV, b_SUM_REAL_PV, REP_A_BALANCE, REP_B_BALANCE, REP_A_NEW_AMOUNT,
|
|||
|
|
REP_B_NEW_AMOUNT,
|
|||
|
|
REP_A_NEW_PV, REP_B_NEW_PV,
|
|||
|
|
REP_A_SUM_AMOUNT, REP_B_SUM_AMOUNT,
|
|||
|
|
REP_A_SUM_PV, REP_B_SUM_PV,
|
|||
|
|
REGISTER_AMOUNT, REGISTER_PV,
|
|||
|
|
REGISTER_NEW_AMOUNT, REGISTER_NEW_PV,
|
|||
|
|
UPGRADE_AMOUNT, UPGRADE_PV,
|
|||
|
|
UPGRADE_NEW_AMOUNT, UPGRADE_NEW_PV,
|
|||
|
|
REPURCHASE_AMOUNT, REPURCHASE_PV,
|
|||
|
|
REPURCHASE_NEW_AMOUNT, REPURCHASE_NEW_PV,
|
|||
|
|
HI_FUN_AMOUNT, HI_FUN_PV,
|
|||
|
|
HI_FUN_NEW_AMOUNT, HI_FUN_NEW_PV,
|
|||
|
|
MALL_AMOUNT, MALL_PV,
|
|||
|
|
MALL_NEW_AMOUNT, MALL_NEW_PV,
|
|||
|
|
MIN_ACHIEVE, ROUND, SECOND,
|
|||
|
|
R_A_BALANCE, R_b_BALANCE,
|
|||
|
|
R_A_SUM_AMOUNT, R_b_SUM_AMOUNT,
|
|||
|
|
R_A_SUM_PV, R_B_SUM_PV,
|
|||
|
|
R_A_SUM_REAL_AMOUNT, R_B_SUM_REAL_AMOUNT,
|
|||
|
|
R_A_SUM_REAL_PV, R_b_SUM_REAL_PV,
|
|||
|
|
R_REP_A_BALANCE, R_REP_B_BALANCE,
|
|||
|
|
R_REP_A_SUM_AMOUNT, R_REP_B_SUM_AMOUNT,
|
|||
|
|
R_REP_A_SUM_PV, R_REP_B_SUM_PV)
|
|||
|
|
select gd.PERIOD,
|
|||
|
|
gd.USER_ID pk_member,
|
|||
|
|
nvl(tj.user_id, 0) PK_PARENT,
|
|||
|
|
az.user_id PK_PLACE_PARENT,
|
|||
|
|
gd.PARENT_AREA PLACE_DEPT,
|
|||
|
|
gd.USER_NAME MEMBER_CODE,
|
|||
|
|
gd.REAL_NAME MEMBER_NAME,
|
|||
|
|
gi.CUSTOMER_TYPE CUSTOMER_TYPE,
|
|||
|
|
nvl(su.phonenumber, '~') PHONE,
|
|||
|
|
(case gd.CLAC_JOIN_LEVEL
|
|||
|
|
when -1 then
|
|||
|
|
20
|
|||
|
|
when 0 then
|
|||
|
|
30
|
|||
|
|
when 1 then
|
|||
|
|
40
|
|||
|
|
when 2 then
|
|||
|
|
50
|
|||
|
|
when 3 then
|
|||
|
|
60
|
|||
|
|
else
|
|||
|
|
70
|
|||
|
|
end) pk_settle_grade,
|
|||
|
|
nvl(gd.REAL_HONOUR_LEVEL, 0) * 5 pk_awards,
|
|||
|
|
su.status ACCOUNT_STATUS,
|
|||
|
|
nvl(su.ZFZT,0) PAY_STATUS,
|
|||
|
|
(case sr.role_id
|
|||
|
|
when 104 then
|
|||
|
|
1
|
|||
|
|
when 105 then
|
|||
|
|
2
|
|||
|
|
else
|
|||
|
|
0
|
|||
|
|
end) REGISTER_AUTHORITY,
|
|||
|
|
(case
|
|||
|
|
when gi.CUSTOMER_TYPE = 2 then
|
|||
|
|
1
|
|||
|
|
else
|
|||
|
|
0
|
|||
|
|
end) CATEGORY,
|
|||
|
|
0 PURCHASE_TYPE,
|
|||
|
|
(case
|
|||
|
|
when gd.REPURCHASE_CHECK_STATE = 1 then
|
|||
|
|
0
|
|||
|
|
else
|
|||
|
|
1
|
|||
|
|
end) PURCHASE_STATUS,
|
|||
|
|
(case
|
|||
|
|
when gd.MALL_CHECK_STATE = 1 then
|
|||
|
|
0
|
|||
|
|
else
|
|||
|
|
1
|
|||
|
|
end) SHARE_STATUS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
tm.user_id PK_TEAM_CODE,
|
|||
|
|
bao.user_id PK_CENTER_CODE,
|
|||
|
|
0 PK_VERTEX,
|
|||
|
|
gd.CLAC_COUNTRY PK_SETTLE_COUNTRY,
|
|||
|
|
gd.clac_country PK_COUNTRY,
|
|||
|
|
gd.EXCHANGE_RATE PK_RATE,
|
|||
|
|
A_ORG_MAIN_KEEP/ 6.4 A_BALANCE,
|
|||
|
|
B_ORG_MAIN_KEEP/ 6.4 b_BALANCE,
|
|||
|
|
A_TOTAL_MAIN_AMOUNT / 6.4 A_SUM_AMOUNT,
|
|||
|
|
B_TOTAL_MAIN_AMOUNT / 6.4 b_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_MAIN_PV / 6.4 A_SUM_PV,
|
|||
|
|
B_TOTAL_MAIN_PV / 6.4 B_SUM_PV,
|
|||
|
|
A_DAY_NEW_MAIN_AMOUNT / 6.4 A_NEW_AMOUNT,
|
|||
|
|
B_DAY_NEW_MAIN_AMOUNT / 6.4 B_NEW_AMOUNT,
|
|||
|
|
A_DAY_NEW_MAIN_PV / 6.4 A_NEW_PV,
|
|||
|
|
b_DAY_NEW_MAIN_PV / 6.4 b_NEW_PV,
|
|||
|
|
(A_TOTAL_MAIN_AMOUNT + A_TOTAL_REPURCHASE_AMOUNT)/ 6.4 A_SUM_REAL_AMOUNT,
|
|||
|
|
(b_TOTAL_MAIN_AMOUNT + b_TOTAL_REPURCHASE_AMOUNT)/ 6.4 B_SUM_REAL_AMOUNT,
|
|||
|
|
A_TOTAL_REAL_REPUR_PV / 6.4 A_SUM_REAL_PV,
|
|||
|
|
B_TOTAL_REAL_REPUR_PV / 6.4 b_SUM_REAL_PV,
|
|||
|
|
A_ORG_REPURCHASE_KEEP / 6.4 REP_A_BALANCE,
|
|||
|
|
b_ORG_REPURCHASE_KEEP / 6.4 REP_B_BALANCE,
|
|||
|
|
A_DAY_NEW_REPURCHASE_AMOUNT / 6.4 REP_A_NEW_AMOUNT,
|
|||
|
|
b_DAY_NEW_REPURCHASE_AMOUNT / 6.4 REP_B_NEW_AMOUNT,
|
|||
|
|
A_DAY_NEW_REPUR_MALL_PV / 6.4 REP_A_NEW_PV,
|
|||
|
|
b_DAY_NEW_REPUR_MALL_PV / 6.4 REP_B_NEW_PV,
|
|||
|
|
A_TOTAL_REPURCHASE_AMOUNT / 6.4 REP_A_SUM_AMOUNT,
|
|||
|
|
b_TOTAL_REPURCHASE_AMOUNT / 6.4 REP_B_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_REPUR_MALL_PV / 6.4 REP_A_SUM_PV,
|
|||
|
|
b_TOTAL_REPUR_MALL_PV / 6.4 REP_B_SUM_PV,
|
|||
|
|
nvl(gs.REGISTER_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.REGISTER_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REG_AMOUNT, 0)/ 6.4 REGISTER_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REG_PV, 0) / 6.4 REGISTER_NEW_PV,
|
|||
|
|
nvl(gs.UPGRADE_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.UPGRADE_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_UP_AMOUNT, 0) / 6.4 UPGRADE_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_UP_PV, 0) / 6.4 UPGRADE_NEW_PV,
|
|||
|
|
nvl(gs.REPURCHASE_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.REPURCHASE_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REPUR_AMOUNT, 0) / 6.4 REPURCHASE_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REPURCHASE_PV, 0) / 6.4 REPURCHASE_NEW_PV,
|
|||
|
|
nvl(gs.HI_FUN_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.HI_FUN_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_SEA2_REG_AMOUNT, 0) / 6.4 HI_FUN_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_SEA2_REG_PV, 0) / 6.4 HI_FUN_NEW_PV,
|
|||
|
|
nvl(gs.MALL_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.MALL_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MALL_AMOUNT, 0) / 6.4 MALL_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MALL_PV, 0) / 6.4 MALL_NEW_PV,
|
|||
|
|
nvl(lun.SOURCE_PV, 0) / 6.4 MIN_ACHIEVE,
|
|||
|
|
nvl(lun.times, 0) ROUND,
|
|||
|
|
nvl(lun.orders, 0)+1 SECOND,
|
|||
|
|
A_ORG_MAIN_KEEP / 6.4 R_A_BALANCE,
|
|||
|
|
B_ORG_MAIN_KEEP / 6.4 R_b_BALANCE,
|
|||
|
|
A_TOTAL_MAIN_AMOUNT / 6.4 R_A_SUM_AMOUNT,
|
|||
|
|
B_TOTAL_MAIN_AMOUNT / 6.4 R_b_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_MAIN_PV / 6.4 R_A_SUM_PV,
|
|||
|
|
B_TOTAL_MAIN_PV / 6.4 R_B_SUM_PV,
|
|||
|
|
(A_TOTAL_MAIN_AMOUNT + A_TOTAL_REPURCHASE_AMOUNT)/ 6.4 R_A_SUM_REAL_AMOUNT,
|
|||
|
|
(b_TOTAL_MAIN_AMOUNT + b_TOTAL_REPURCHASE_AMOUNT)/ 6.4 R_B_SUM_REAL_AMOUNT,
|
|||
|
|
A_TOTAL_REAL_REPUR_PV / 6.4 R_A_SUM_REAL_PV,
|
|||
|
|
B_TOTAL_REAL_REPUR_PV / 6.4 R_b_SUM_REAL_PV,
|
|||
|
|
A_ORG_REPURCHASE_KEEP / 6.4 R_REP_A_BALANCE,
|
|||
|
|
b_ORG_REPURCHASE_KEEP / 6.4 R_REP_B_BALANCE,
|
|||
|
|
A_TOTAL_REPURCHASE_AMOUNT / 6.4 R_REP_A_SUM_AMOUNT,
|
|||
|
|
b_TOTAL_REPURCHASE_AMOUNT / 6.4 R_REP_B_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_REPUR_MALL_PV / 6.4 R_REP_A_SUM_PV,
|
|||
|
|
b_TOTAL_REPUR_MALL_PV / 6.4 R_REP_B_SUM_PV
|
|||
|
|
from g_bonus_day@link_test gd
|
|||
|
|
left join g_bonus_day_info@link_test gi
|
|||
|
|
on gd.user_name = gi.user_name
|
|||
|
|
and gd.period = gi.period
|
|||
|
|
left join G_MEMBER_ACHIV@link_test ga
|
|||
|
|
on gd.user_name = ga.user_name
|
|||
|
|
and gd.period = ga.period
|
|||
|
|
left join (select user_name,
|
|||
|
|
sum(CURR_DAY_MAIN_REG_AMOUNT) REGISTER_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_REG_PV) REGISTER_PV,
|
|||
|
|
sum(CURR_DAY_MAIN_UP_AMOUNT) UPGRADE_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_UP_PV) UPGRADE_PV,
|
|||
|
|
sum(CURR_DAY_MAIN_REPUR_AMOUNT) REPURCHASE_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_REPURCHASE_PV) REPURCHASE_PV,
|
|||
|
|
sum(CURR_DAY_SEA2_REG_AMOUNT) HI_FUN_AMOUNT,
|
|||
|
|
sum(CURR_DAY_SEA2_REG_PV) HI_FUN_PV,
|
|||
|
|
sum(CURR_DAY_MALL_AMOUNT) MALL_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MALL_PV) MALL_PV
|
|||
|
|
from G_MEMBER_ACHIV@link_test
|
|||
|
|
where period <= 881
|
|||
|
|
group by user_name) gs
|
|||
|
|
on gd.user_name = gs.user_name
|
|||
|
|
left join sys_user@link_test su
|
|||
|
|
on gd.user_name = su.user_name
|
|||
|
|
left join sys_user@link_test tj
|
|||
|
|
on gd.INTRODUCER_CODE = tj.user_name
|
|||
|
|
left join sys_user@link_test az
|
|||
|
|
on gd.PARENT_CODE = az.user_name
|
|||
|
|
left join sys_user@link_test tm
|
|||
|
|
on gd.TEAM_NAME = tm.user_name
|
|||
|
|
left join sys_user@link_test bao
|
|||
|
|
on su.bdzx = bao.user_name
|
|||
|
|
left join (select user_id, max(role_id) role_id
|
|||
|
|
from sys_user_role@link_test
|
|||
|
|
where role_id in (102, 104, 105)
|
|||
|
|
group by user_id) sr
|
|||
|
|
on su.user_id = sr.user_id
|
|||
|
|
left join (select user_name,
|
|||
|
|
times,
|
|||
|
|
max(ORDERS) orders,
|
|||
|
|
min(SOURCE_PV) SOURCE_PV
|
|||
|
|
from g_detail_intro@link_test
|
|||
|
|
where (user_name, times) in
|
|||
|
|
(select user_name, max(times)
|
|||
|
|
from g_detail_intro@link_test
|
|||
|
|
where period <= 881
|
|||
|
|
group by user_name)
|
|||
|
|
group by user_name, times) lun
|
|||
|
|
on lun.user_name = gd.user_name
|
|||
|
|
where gd.period = 881
|
|||
|
|
and gi.period = 881 and gd.ACTIVE_STATE in(0,1);
|
|||
|
|
|
|||
|
|
update cu_member_settle881 ct set pk_rate =(select bc.pk_id from bd_currency bc
|
|||
|
|
where ct.pk_country =bc.pk_country) where pk_rate >0 and pk_country >0;
|
|||
|
|
merge into cu_member_settle881 a
|
|||
|
|
using (select ct.pk_member, js.pk_id pk_grade, jx.pk_id pk_awards
|
|||
|
|
from cu_member_settle881 ct
|
|||
|
|
left join bd_grade js
|
|||
|
|
on ct.pk_grade = js.grade_value
|
|||
|
|
and ct.pk_settle_country = js.pk_country
|
|||
|
|
left join bd_awards jx
|
|||
|
|
on ct.pk_awards = jx.awards_value
|
|||
|
|
and ct.pk_settle_country = jx.pk_country) b
|
|||
|
|
on (a.pk_member = b.pk_member)
|
|||
|
|
WHEN MATCHED THEN
|
|||
|
|
update set a.pk_grade = b.pk_grade,a.pk_awards = b.pk_awards;
|
|||
|
|
|
|||
|
|
--1的代表3单循环，2的代表4单循环
|
|||
|
|
select * from bd_system_config where key='RECOMMEND_1' and value=1;
|
|||
|
|
select * from cu_member_settle925 where second>3 and pk_country=1;
|
|||
|
|
|
|||
|
|
update cu_member_settle941 set round=round+1,second=1 where second>3 and pk_country in
|
|||
|
|
(select pk_country from bd_system_config where key='RECOMMEND_1' and value=1);
|
|||
|
|
update cu_member_settle941 set round=round+1,second=1 where second>4 and pk_country in
|
|||
|
|
(select pk_country from bd_system_config where key='RECOMMEND_1' and value=2);
|
|||
|
|
|
|||
|
|
--会员网体记录 -历史记录表 CU_MEMBER_TREE_SEQ 1月
|
|||
|
|
insert into CU_MEMBER_TREE202201(pk_id,PERIOD,pk_member,PK_PARENT,PK_PLACE_PARENT,PLACE_DEPT,MEMBER_CODE,
|
|||
|
|
MEMBER_NAME,CUSTOMER_TYPE,PHONE,pk_grade,pk_awards,ACCOUNT_STATUS,PAY_STATUS,REGISTER_AUTHORITY,CATEGORY,PURCHASE_TYPE,
|
|||
|
|
PURCHASE_STATUS,SHARE_STATUS,INCOME_STATUS,PK_TEAM_CODE,PK_CENTER_CODE,PK_VERTEX,PK_SETTLE_COUNTRY,PK_COUNTRY)
|
|||
|
|
select Cu_Member_Tree_Seq.Nextval pk_id, a.*
|
|||
|
|
from (select gd.PERIOD,
|
|||
|
|
gd.USER_ID pk_member,
|
|||
|
|
nvl(tj.user_id, 0) PK_PARENT,
|
|||
|
|
az.user_id PK_PLACE_PARENT,
|
|||
|
|
gd.PARENT_AREA PLACE_DEPT,
|
|||
|
|
gd.USER_NAME MEMBER_CODE,
|
|||
|
|
nvl(gd.REAL_NAME, '~') MEMBER_NAME,
|
|||
|
|
gi.CUSTOMER_TYPE CUSTOMER_TYPE,
|
|||
|
|
nvl(su.phonenumber, '~') PHONE,
|
|||
|
|
(case gd.CLAC_JOIN_LEVEL
|
|||
|
|
when -1 then
|
|||
|
|
20
|
|||
|
|
when 0 then
|
|||
|
|
30
|
|||
|
|
when 1 then
|
|||
|
|
40
|
|||
|
|
when 2 then
|
|||
|
|
50
|
|||
|
|
when 3 then
|
|||
|
|
60
|
|||
|
|
else
|
|||
|
|
70
|
|||
|
|
end) pk_grade,
|
|||
|
|
nvl(gd.REAL_HONOUR_LEVEL, 0) * 5 pk_awards,
|
|||
|
|
nvl(su.status,0) ACCOUNT_STATUS,
|
|||
|
|
nvl(su.ZFZT, 0) PAY_STATUS,
|
|||
|
|
(case sr.role_id
|
|||
|
|
when 104 then
|
|||
|
|
1
|
|||
|
|
when 105 then
|
|||
|
|
2
|
|||
|
|
else
|
|||
|
|
0
|
|||
|
|
end) REGISTER_AUTHORITY,
|
|||
|
|
(case
|
|||
|
|
when gi.CUSTOMER_TYPE = 2 then
|
|||
|
|
1
|
|||
|
|
else
|
|||
|
|
0
|
|||
|
|
end) CATEGORY,
|
|||
|
|
0 PURCHASE_TYPE,
|
|||
|
|
(case
|
|||
|
|
when gd.REPURCHASE_CHECK_STATE = 1 then
|
|||
|
|
0
|
|||
|
|
else
|
|||
|
|
1
|
|||
|
|
end) PURCHASE_STATUS,
|
|||
|
|
(case
|
|||
|
|
when gd.MALL_CHECK_STATE = 1 then
|
|||
|
|
0
|
|||
|
|
else
|
|||
|
|
1
|
|||
|
|
end) SHARE_STATUS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
tm.user_id PK_TEAM_CODE,
|
|||
|
|
bao.user_id PK_CENTER_CODE,
|
|||
|
|
0 PK_VERTEX,
|
|||
|
|
gd.CLAC_COUNTRY PK_SETTLE_COUNTRY,
|
|||
|
|
gd.clac_country PK_COUNTRY
|
|||
|
|
from g_bonus_day@link_test gd
|
|||
|
|
left join g_bonus_day_info@link_test gi
|
|||
|
|
on gd.user_name = gi.user_name
|
|||
|
|
and gd.period = gi.period
|
|||
|
|
left join sys_user@link_test su
|
|||
|
|
on gd.user_name = su.user_name
|
|||
|
|
left join sys_user@link_test tj
|
|||
|
|
on gd.INTRODUCER_CODE = tj.user_name
|
|||
|
|
left join sys_user@link_test az
|
|||
|
|
on gd.PARENT_CODE = az.user_name
|
|||
|
|
left join sys_user@link_test tm
|
|||
|
|
on gd.TEAM_NAME = tm.user_name
|
|||
|
|
left join sys_user@link_test bao
|
|||
|
|
on su.bdzx = bao.user_name
|
|||
|
|
left join (select user_id, max(role_id) role_id
|
|||
|
|
from sys_user_role@link_test
|
|||
|
|
where role_id in (102, 104, 105)
|
|||
|
|
group by user_id) sr
|
|||
|
|
on su.user_id = sr.user_id
|
|||
|
|
where gd.period between 365 and 395
|
|||
|
|
and gi.period between 365 and 395 and gd.ACTIVE_STATE in(0,1)) a;
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into CU_MEMBER_TREE202201 a
|
|||
|
|
using (select ct.pk_member, period, js.pk_id pk_grade, jx.pk_id pk_awards
|
|||
|
|
from CU_MEMBER_TREE202201 ct
|
|||
|
|
left join bd_grade js
|
|||
|
|
on ct.pk_grade = js.grade_value
|
|||
|
|
and ct.pk_settle_country = js.pk_country
|
|||
|
|
left join bd_awards jx
|
|||
|
|
on ct.pk_awards = jx.awards_value
|
|||
|
|
and ct.pk_settle_country = jx.pk_country) b
|
|||
|
|
on (a.pk_member = b.pk_member and a.period = b.period)
|
|||
|
|
WHEN MATCHED THEN
|
|||
|
|
update set a.pk_grade = b.pk_grade,a.pk_awards = b.pk_awards;
|
|||
|
|
|
|||
|
|
--CU_MEMBER_TREE202201 365 395
|
|||
|
|
--CU_MEMBER_TREE202202 396 423
|
|||
|
|
--CU_MEMBER_TREE202203 424 454
|
|||
|
|
--CU_MEMBER_TREE202204 455 484
|
|||
|
|
--CU_MEMBER_TREE202205 485 515
|
|||
|
|
--CU_MEMBER_TREE202206 516 545
|
|||
|
|
--CU_MEMBER_TREE202207 546 576
|
|||
|
|
--CU_MEMBER_TREE202208 577 607
|
|||
|
|
--CU_MEMBER_TREE202209 608 637
|
|||
|
|
--CU_MEMBER_TREE202210 638 668
|
|||
|
|
--CU_MEMBER_TREE202211 669 698
|
|||
|
|
--CU_MEMBER_TREE202212 699 729
|
|||
|
|
--CU_MEMBER_TREE202301 730 760
|
|||
|
|
--CU_MEMBER_TREE202302 761 788
|
|||
|
|
--CU_MEMBER_TREE202303 789 819
|
|||
|
|
--CU_MEMBER_TREE202304 820 849
|
|||
|
|
--CU_MEMBER_TREE202305 850 880
|
|||
|
|
--CU_MEMBER_TREE202306 881 910
|
|||
|
|
--CU_MEMBER_TREE202307 911 941
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 历史业绩
|
|||
|
|
insert into CU_MEMBER_ACHIEVE202308 (pk_id,PERIOD, pk_member, PK_RATE,PK_COUNTRY,
|
|||
|
|
A_BALANCE, b_BALANCE,
|
|||
|
|
A_SUM_AMOUNT, b_SUM_AMOUNT, A_SUM_PV, B_SUM_PV, A_NEW_AMOUNT, B_NEW_AMOUNT, A_NEW_PV,
|
|||
|
|
b_NEW_PV, A_SUM_REAL_AMOUNT, B_SUM_REAL_AMOUNT,
|
|||
|
|
A_SUM_REAL_PV, b_SUM_REAL_PV, REP_A_BALANCE, REP_B_BALANCE, REP_A_NEW_AMOUNT,
|
|||
|
|
REP_B_NEW_AMOUNT,
|
|||
|
|
REP_A_NEW_PV, REP_B_NEW_PV,
|
|||
|
|
REP_A_SUM_AMOUNT, REP_B_SUM_AMOUNT,
|
|||
|
|
REP_A_SUM_PV, REP_B_SUM_PV,
|
|||
|
|
REGISTER_AMOUNT, REGISTER_PV,
|
|||
|
|
REGISTER_NEW_AMOUNT, REGISTER_NEW_PV,
|
|||
|
|
UPGRADE_AMOUNT, UPGRADE_PV,
|
|||
|
|
UPGRADE_NEW_AMOUNT, UPGRADE_NEW_PV,
|
|||
|
|
REPURCHASE_AMOUNT, REPURCHASE_PV,
|
|||
|
|
REPURCHASE_NEW_AMOUNT, REPURCHASE_NEW_PV,
|
|||
|
|
HI_FUN_AMOUNT, HI_FUN_PV,
|
|||
|
|
HI_FUN_NEW_AMOUNT, HI_FUN_NEW_PV,
|
|||
|
|
MALL_AMOUNT, MALL_PV,
|
|||
|
|
MALL_NEW_AMOUNT, MALL_NEW_PV,
|
|||
|
|
REGISTER_Blo_NEW_PV,REPURCHASE_Blo_NEW_PV,
|
|||
|
|
R_A_BALANCE, R_b_BALANCE,
|
|||
|
|
R_A_SUM_AMOUNT, R_b_SUM_AMOUNT,
|
|||
|
|
R_A_SUM_PV, R_B_SUM_PV,
|
|||
|
|
R_A_SUM_REAL_AMOUNT, R_B_SUM_REAL_AMOUNT,
|
|||
|
|
R_A_SUM_REAL_PV, R_b_SUM_REAL_PV,
|
|||
|
|
R_REP_A_BALANCE, R_REP_B_BALANCE,
|
|||
|
|
R_REP_A_SUM_AMOUNT, R_REP_B_SUM_AMOUNT,
|
|||
|
|
R_REP_A_SUM_PV, R_REP_B_SUM_PV)
|
|||
|
|
select cu_member_achieve_seq.nextval pk_id,
|
|||
|
|
gd.PERIOD,
|
|||
|
|
gd.USER_ID pk_member,
|
|||
|
|
gd.EXCHANGE_RATE PK_RATE,
|
|||
|
|
gd.clac_country PK_COUNTRY,
|
|||
|
|
A_ORG_MAIN_KEEP/ 6.4 A_BALANCE,
|
|||
|
|
B_ORG_MAIN_KEEP/ 6.4 b_BALANCE,
|
|||
|
|
A_TOTAL_MAIN_AMOUNT/ 6.4 A_SUM_AMOUNT,
|
|||
|
|
B_TOTAL_MAIN_AMOUNT/ 6.4 b_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_MAIN_PV/ 6.4 A_SUM_PV,
|
|||
|
|
B_TOTAL_MAIN_PV/ 6.4 B_SUM_PV,
|
|||
|
|
A_DAY_NEW_MAIN_AMOUNT/ 6.4 A_NEW_AMOUNT,
|
|||
|
|
B_DAY_NEW_MAIN_AMOUNT/ 6.4 B_NEW_AMOUNT,
|
|||
|
|
A_DAY_NEW_MAIN_PV/ 6.4 A_NEW_PV,
|
|||
|
|
b_DAY_NEW_MAIN_PV/ 6.4 b_NEW_PV,
|
|||
|
|
(A_TOTAL_MAIN_AMOUNT + A_TOTAL_REPURCHASE_AMOUNT)/ 6.4 A_SUM_REAL_AMOUNT,
|
|||
|
|
(b_TOTAL_MAIN_AMOUNT + b_TOTAL_REPURCHASE_AMOUNT)/ 6.4 B_SUM_REAL_AMOUNT,
|
|||
|
|
A_TOTAL_REAL_REPUR_PV/ 6.4 A_SUM_REAL_PV,
|
|||
|
|
B_TOTAL_REAL_REPUR_PV/ 6.4 b_SUM_REAL_PV,
|
|||
|
|
A_ORG_REPURCHASE_KEEP/ 6.4 REP_A_BALANCE,
|
|||
|
|
b_ORG_REPURCHASE_KEEP/ 6.4 REP_B_BALANCE,
|
|||
|
|
A_DAY_NEW_REPURCHASE_AMOUNT/ 6.4 REP_A_NEW_AMOUNT,
|
|||
|
|
b_DAY_NEW_REPURCHASE_AMOUNT/ 6.4 REP_B_NEW_AMOUNT,
|
|||
|
|
A_DAY_NEW_REPUR_MALL_PV/ 6.4 REP_A_NEW_PV,
|
|||
|
|
b_DAY_NEW_REPUR_MALL_PV/ 6.4 REP_B_NEW_PV,
|
|||
|
|
A_TOTAL_REPURCHASE_AMOUNT/ 6.4 REP_A_SUM_AMOUNT,
|
|||
|
|
b_TOTAL_REPURCHASE_AMOUNT/ 6.4 REP_B_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_REPUR_MALL_PV/ 6.4 REP_A_SUM_PV,
|
|||
|
|
b_TOTAL_REPUR_MALL_PV/ 6.4 REP_B_SUM_PV,
|
|||
|
|
nvl(gs.REGISTER_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.REGISTER_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REG_AMOUNT, 0)/ 6.4 REGISTER_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REG_PV, 0)/ 6.4 REGISTER_NEW_PV,
|
|||
|
|
nvl(gs.UPGRADE_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.UPGRADE_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_UP_AMOUNT, 0)/ 6.4 UPGRADE_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_UP_PV, 0)/ 6.4 UPGRADE_NEW_PV,
|
|||
|
|
nvl(gs.REPURCHASE_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.REPURCHASE_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REPUR_AMOUNT, 0)/ 6.4 REPURCHASE_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MAIN_REPURCHASE_PV, 0)/ 6.4 REPURCHASE_NEW_PV,
|
|||
|
|
nvl(gs.HI_FUN_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.HI_FUN_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_SEA2_REG_AMOUNT, 0)/ 6.4 HI_FUN_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_SEA2_REG_PV, 0)/ 6.4 HI_FUN_NEW_PV,
|
|||
|
|
nvl(gs.MALL_AMOUNT, 0)/ 6.4,
|
|||
|
|
nvl(gs.MALL_PV, 0)/ 6.4,
|
|||
|
|
nvl(ga.CURR_DAY_MALL_AMOUNT, 0)/ 6.4 MALL_NEW_AMOUNT,
|
|||
|
|
nvl(ga.CURR_DAY_MALL_PV, 0) / 6.4 MALL_NEW_PV,
|
|||
|
|
nvl(gc.DAY_SYSTEM_INTRO12456_PV,0) REGISTER_Blo_NEW_PV,
|
|||
|
|
nvl(gc.DAY_SYSTEM_INTRO37_PV,0) REPURCHASE_Blo_NEW_PV,
|
|||
|
|
A_ORG_MAIN_KEEP / 6.4 R_A_BALANCE,
|
|||
|
|
B_ORG_MAIN_KEEP / 6.4 R_b_BALANCE,
|
|||
|
|
A_TOTAL_MAIN_AMOUNT / 6.4 R_A_SUM_AMOUNT,
|
|||
|
|
B_TOTAL_MAIN_AMOUNT / 6.4 R_b_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_MAIN_PV / 6.4 R_A_SUM_PV,
|
|||
|
|
B_TOTAL_MAIN_PV / 6.4 R_B_SUM_PV,
|
|||
|
|
(A_TOTAL_MAIN_AMOUNT + A_TOTAL_REPURCHASE_AMOUNT)/ 6.4 R_A_SUM_REAL_AMOUNT,
|
|||
|
|
(b_TOTAL_MAIN_AMOUNT + b_TOTAL_REPURCHASE_AMOUNT)/ 6.4 R_B_SUM_REAL_AMOUNT,
|
|||
|
|
A_TOTAL_REAL_REPUR_PV / 6.4 R_A_SUM_REAL_PV,
|
|||
|
|
B_TOTAL_REAL_REPUR_PV/ 6.4 R_b_SUM_REAL_PV,
|
|||
|
|
A_ORG_REPURCHASE_KEEP / 6.4 R_REP_A_BALANCE,
|
|||
|
|
b_ORG_REPURCHASE_KEEP / 6.4 R_REP_B_BALANCE,
|
|||
|
|
A_TOTAL_REPURCHASE_AMOUNT / 6.4 R_REP_A_SUM_AMOUNT,
|
|||
|
|
b_TOTAL_REPURCHASE_AMOUNT / 6.4 R_REP_B_SUM_AMOUNT,
|
|||
|
|
A_TOTAL_REPUR_MALL_PV / 6.4 R_REP_A_SUM_PV,
|
|||
|
|
b_TOTAL_REPUR_MALL_PV / 6.4 R_REP_B_SUM_PV
|
|||
|
|
from hzs_cn.g_bonus_day gd
|
|||
|
|
left join hzs_cn.G_MEMBER_ACHIV ga
|
|||
|
|
on gd.user_name = ga.user_name
|
|||
|
|
and gd.period = ga.period
|
|||
|
|
left join hzs_cn.g_statis_day_achiv gc
|
|||
|
|
on gd.user_name = gc.user_name and gd.period = gc.period
|
|||
|
|
left join (select user_name,
|
|||
|
|
sum(CURR_DAY_MAIN_REG_AMOUNT) REGISTER_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_REG_PV) REGISTER_PV,
|
|||
|
|
sum(CURR_DAY_MAIN_UP_AMOUNT) UPGRADE_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_UP_PV) UPGRADE_PV,
|
|||
|
|
sum(CURR_DAY_MAIN_REPUR_AMOUNT) REPURCHASE_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MAIN_REPURCHASE_PV) REPURCHASE_PV,
|
|||
|
|
sum(CURR_DAY_SEA2_REG_AMOUNT) HI_FUN_AMOUNT,
|
|||
|
|
sum(CURR_DAY_SEA2_REG_PV) HI_FUN_PV,
|
|||
|
|
sum(CURR_DAY_MALL_AMOUNT) MALL_AMOUNT,
|
|||
|
|
sum(CURR_DAY_MALL_PV) MALL_PV
|
|||
|
|
from hzs_cn.G_MEMBER_ACHIV
|
|||
|
|
where period between -729 and 941
|
|||
|
|
group by user_name) gs
|
|||
|
|
on gd.user_name = gs.user_name
|
|||
|
|
where gd.period between 911 and 941 and gd.ACTIVE_STATE in(0,1);
|
|||
|
|
|
|||
|
|
|
|||
|
|
update CU_MEMBER_ACHIEVE202201 ct set pk_rate =(select bc.pk_id from bd_currency bc
|
|||
|
|
where ct.pk_country =bc.pk_country) where pk_rate >0 and pk_country >0;
|
|||
|
|
|
|||
|
|
-- 更新业绩 /member/manage/settle/update-achieve
|
|||
|
|
--CU_MEMBER_ACHIEVE202201 365 395
|
|||
|
|
--CU_MEMBER_ACHIEVE202202 396 423
|
|||
|
|
--CU_MEMBER_ACHIEVE202203 424 454
|
|||
|
|
--CU_MEMBER_ACHIEVE202204 455 484
|
|||
|
|
--CU_MEMBER_ACHIEVE202205 485 515
|
|||
|
|
--CU_MEMBER_ACHIEVE202206 516 545
|
|||
|
|
--CU_MEMBER_ACHIEVE202207 546 576
|
|||
|
|
--CU_MEMBER_ACHIEVE202208 577 607
|
|||
|
|
--CU_MEMBER_ACHIEVE202209 608 637
|
|||
|
|
--CU_MEMBER_ACHIEVE202210 638 668
|
|||
|
|
--CU_MEMBER_ACHIEVE202211 669 698
|
|||
|
|
--CU_MEMBER_ACHIEVE202212 699 729
|
|||
|
|
--CU_MEMBER_ACHIEVE202301 730 760
|
|||
|
|
--CU_MEMBER_ACHIEVE202302 761 788
|
|||
|
|
--CU_MEMBER_ACHIEVE202303 789 819
|
|||
|
|
--CU_MEMBER_ACHIEVE202304 820 849
|
|||
|
|
--CU_MEMBER_ACHIEVE202305 850 880
|
|||
|
|
--CU_MEMBER_ACHIEVE202306 881 910
|
|||
|
|
--CU_MEMBER_ACHIEVE202307 911 941
|
|||
|
|
|
|||
|
|
--cu_member_bonus 奖金表
|
|||
|
|
--select max(pk_id) from cu_member_bonus
|
|||
|
|
insert into cu_member_bonus(pk_id,pk_member,period,PK_RATE,DIRECT_INCOME,EXPAND_INCOME,COACH_INCOME,SHARE_INCOME,
|
|||
|
|
SERVICE_INCOME,BACK_POINTS,SERVICE_SPEND,PUR_REAL_SUBTOTAL,REPUR_RANGE_INCOME,REPUR_EXPAND_INCOME,REPUR_REAL_SUBTOTAL,
|
|||
|
|
CLOUD_DIRECT_INCOME,CLOUD_PUR_INCOME,CLOUD_REPUR_INCOME,CLOUD_REAL_SUBTOTAL,GLOBAL_POINTS,CAR_AWARD_POINTS,HI_FUN_INCOME,
|
|||
|
|
MAKER_DIRECT_INCOME,MAKER_SHARE_INCOME,MAKER_REAL_SUBTOTAL,STORE_INCOME,REAL_INCOME_TOTAL,PK_COUNTRY,del_flag,PK_CREATOR,
|
|||
|
|
GRANT_STATUS,INCOME_STATUS)
|
|||
|
|
select id pk_id,
|
|||
|
|
user_id pk_member,
|
|||
|
|
period,
|
|||
|
|
exchange_rate PK_RATE,
|
|||
|
|
INTRODUCE_BONUS / 6.4 DIRECT_INCOME,
|
|||
|
|
ORG_BONUS / 6.4 EXPAND_INCOME,
|
|||
|
|
LEADER_BONUS / 6.4 COACH_INCOME,
|
|||
|
|
SHARE_BONUS / 6.4 SHARE_INCOME,
|
|||
|
|
SERVICE_BONUS / 6.4 SERVICE_INCOME,
|
|||
|
|
REPEATED_CONSUMPTION / 6.4 BACK_POINTS,
|
|||
|
|
MANAGEMENT_EXPENSE / 6.4 SERVICE_SPEND,
|
|||
|
|
(INTRODUCE_BONUS + ORG_BONUS + LEADER_BONUS + SHARE_BONUS + SERVICE_BONUS) / 6.4 PUR_REAL_SUBTOTAL,
|
|||
|
|
REPUR_RANGE_BONUS / 6.4 REPUR_RANGE_INCOME,
|
|||
|
|
REPUR_ORG_BONUS / 6.4 REPUR_EXPAND_INCOME,
|
|||
|
|
(REPUR_RANGE_BONUS + REPUR_ORG_BONUS) / 6.4 REPUR_REAL_SUBTOTAL,
|
|||
|
|
AGENT_INTRODUCE_BONUS / 6.4 CLOUD_DIRECT_INCOME,
|
|||
|
|
AGENT_AREA_BONUS / 6.4 CLOUD_PUR_INCOME,
|
|||
|
|
AGENT_REPUR_BONUS / 6.4 CLOUD_REPUR_INCOME,
|
|||
|
|
(AGENT_INTRODUCE_BONUS + AGENT_AREA_BONUS + AGENT_REPUR_BONUS) / 6.4 CLOUD_REAL_SUBTOTAL,
|
|||
|
|
BONUS_T2 / 6.4 GLOBAL_POINTS,
|
|||
|
|
BONUS_T3 / 6.4 CAR_AWARD_POINTS,
|
|||
|
|
bonus_t6 / 6.4 HI_FUN_INCOME,
|
|||
|
|
(MS_INTRODUCE_BONUS + MSPACK_INTRODUCE_BONUS) / 6.4 MAKER_DIRECT_INCOME,
|
|||
|
|
MS_INITIATORPACK_BONUS / 6.4 MAKER_SHARE_INCOME,
|
|||
|
|
(MS_INTRODUCE_BONUS + MSPACK_INTRODUCE_BONUS + MS_INITIATORPACK_BONUS) / 6.4 MAKER_REAL_SUBTOTAL,
|
|||
|
|
bonus_t7 / 6.4 STORE_INCOME,
|
|||
|
|
BONUS_TOTAL / 6.4 REAL_INCOME_TOTAL,
|
|||
|
|
CLAC_COUNTRY PK_COUNTRY,
|
|||
|
|
STORE_DEL_FLAG del_flag,
|
|||
|
|
100000000 PK_CREATOR,
|
|||
|
|
4 GRANT_STATUS,
|
|||
|
|
0 INCOME_STATUS
|
|||
|
|
from g_bonus_day@link_test
|
|||
|
|
where BONUS_TOTAL > 0 ;
|
|||
|
|
|
|||
|
|
update cu_member_bonus ct set pk_rate =(select bc.pk_id from bd_currency bc
|
|||
|
|
where ct.pk_country =bc.pk_country) where pk_rate >0 and pk_country >0
|
|||
|
|
|
|||
|
|
--推荐明细表
|
|||
|
|
--select max(pk_id) from cu_member_bonus_push;
|
|||
|
|
insert into cu_member_bonus_push(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, round, second, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_push_seq.nextval pk_id,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
round, second, round((case ddje when 0 then 0 else (PRETAX_INCOME-PRETAX_INCOME*0.2)/ddje end), 6) INCOME_RATIO, DEL_FLAG, nvl(PK_COUNTRY, 1) PK_COUNTRY, CREATION_TIME, 100000000 pk_creator
|
|||
|
|
from (
|
|||
|
|
select nvl(nvl(gd.id, gy.id), 0) PK_BONUS, nvl(bo.pkid, 0) PK_ORDER, 3 PK_BONUS_ITEMS, 0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL >0 then 2 else 1 end) CAL_TYPE, SOURCE_PV/6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL >0 then CLAC_VAL/6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL >0 then CLAC_VAL else SOURCE_PV*CLAC_RATE end)/6.4 PRETAX_INCOME,
|
|||
|
|
(case when TIMES-1 < 0 then 0 else TIMES-1 end) round, ORDERS SECOND, nvl(bo.ddje, 0)/6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG, nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME from g_detail_intro@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name=gd.user_name and gi.period=gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name=gi.user_name and gy.period=352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_push a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_push cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
-- 奖金明细表-拓展收益明细
|
|||
|
|
--select max(pk_id)from cu_member_bonus_expand;
|
|||
|
|
insert
|
|||
|
|
into cu_member_bonus_expand(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, CURRENT_TOUCH,
|
|||
|
|
EXPAND_TOUCH, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_expand_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
CURRENT_TOUCH,
|
|||
|
|
0 EXPAND_TOUCH,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
4 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_PV / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_PV * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
(case when TIMES < 0 then 0 else TIMES end) CURRENT_TOUCH,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_org@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_expand a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_expand cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
-- 奖金明细表-消费拓展收益明细表
|
|||
|
|
--select max(pk_id) from cu_member_bonus_expand;
|
|||
|
|
insert into cu_member_bonus_expand(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, CURRENT_TOUCH,
|
|||
|
|
EXPAND_TOUCH, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_expand_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
CURRENT_TOUCH,
|
|||
|
|
0 EXPAND_TOUCH,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
9 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_PV / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_PV * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
(case when TIMES < 0 then 0 else TIMES end) CURRENT_TOUCH,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_org@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_expand a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_expand cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 9) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
-- 奖金明细表-辅导收益明细
|
|||
|
|
--select max(pk_id) from cu_member_bonus_coach;
|
|||
|
|
insert into cu_member_bonus_coach(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, CURRENT_GENERATION,
|
|||
|
|
COACH_GENERATION, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_coach_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
CURRENT_GENERATION,
|
|||
|
|
COACH_GENERATION,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
5 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_ORG / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_ORG * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
CAN_LEVEL CURRENT_GENERATION,
|
|||
|
|
LEVELS COACH_GENERATION,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_leader@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_coach a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_coach cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 奖金明细表-报单服务费收益明细表
|
|||
|
|
insert into cu_member_bonus_detail(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_detail_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
7 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_PV / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_PV * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_service@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_detail a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_detail cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 7) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 云代直推
|
|||
|
|
insert into cu_member_bonus_detail(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_detail_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
0 INCOME_TAX,
|
|||
|
|
PRETAX_INCOME REAL_INCOME,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
14 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_AMONT / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_AMONT * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from G_DETAIL_AGENT_INTRO@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_detail a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_detail cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 14) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
--云代区域
|
|||
|
|
insert into cu_member_bonus_detail(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_detail_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
0 INCOME_TAX,
|
|||
|
|
PRETAX_INCOME REAL_INCOME,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
16 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_ACHIEVEMENT / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_ACHIEVEMENT * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_agent_area@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_detail a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_detail cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 16) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
--创客空间直推、共享
|
|||
|
|
insert into cu_member_bonus_detail(pk_id, pk_bonus, pk_order, PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PRETAX_INCOME, INCOME_TAX, REAL_INCOME, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_detail_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
0 INCOME_TAX,
|
|||
|
|
PRETAX_INCOME REAL_INCOME,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
(case
|
|||
|
|
when BONUS_TYPE = '创客空间礼包推荐奖金' then 20
|
|||
|
|
when BONUS_TYPE = '创客空间礼包发起人奖金' then 21
|
|||
|
|
else 21 end) PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
(case when CLAC_VAL > 0 then 2 else 1 end) CAL_TYPE,
|
|||
|
|
SOURCE_AMONT / 6.4 CAL_ACHIEVE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL / 6.4 else CLAC_RATE end) CAL_VALUE,
|
|||
|
|
(case when CLAC_VAL > 0 then CLAC_VAL else SOURCE_AMONT * CLAC_RATE end) / 6.4 PRETAX_INCOME,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from G_DETAIL_pack_INTRO@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_detail a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_detail cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 20) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_detail a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_detail cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 21) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
|
|||
|
|
--奖金明细表-复购极差收益
|
|||
|
|
insert into cu_member_bonus_range(pk_id, pk_bonus, pk_order, pk_order_items,PK_BONUS_ITEMS, INCOME_STATUS, CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE, CAL_VALUE, PK_RANGE,PK_BEFORE_RANGE,PRETAX_INCOME, INCOME_TAX,
|
|||
|
|
REAL_INCOME, INCOME_RATIO,
|
|||
|
|
DEL_FLAG, PK_COUNTRY, CREATION_TIME, pk_creator)
|
|||
|
|
select cu_member_bonus_range_seq.nextval,
|
|||
|
|
pk_bonus,
|
|||
|
|
pk_order,
|
|||
|
|
nvl(pk_order_items,0),
|
|||
|
|
PK_BONUS_ITEMS,
|
|||
|
|
INCOME_STATUS,
|
|||
|
|
CAL_TYPE,
|
|||
|
|
CAL_ACHIEVE,
|
|||
|
|
CAL_VALUE,
|
|||
|
|
0 PK_RANGE,
|
|||
|
|
0 PK_BEFORE_RANGE,
|
|||
|
|
PRETAX_INCOME,
|
|||
|
|
(PRETAX_INCOME * 0.2) INCOME_TAX,
|
|||
|
|
(PRETAX_INCOME - PRETAX_INCOME * 0.2) REAL_INCOME,
|
|||
|
|
round((case ddje when 0 then 0 else (PRETAX_INCOME - PRETAX_INCOME * 0.2) / ddje end), 6) INCOME_RATIO,
|
|||
|
|
DEL_FLAG,
|
|||
|
|
nvl(PK_COUNTRY, 1) PK_COUNTRY,
|
|||
|
|
CREATION_TIME,
|
|||
|
|
100000000 pk_creator
|
|||
|
|
from (select gi.id pk_id,
|
|||
|
|
nvl(nvl(gd.id, gy.id), 0) PK_BONUS,
|
|||
|
|
nvl(bo.pkid, 0) PK_ORDER,
|
|||
|
|
bi.PKID pk_order_items,
|
|||
|
|
8 PK_BONUS_ITEMS,
|
|||
|
|
0 INCOME_STATUS,
|
|||
|
|
2 CAL_TYPE,
|
|||
|
|
(SOURCE_PRICE*SOURCE_NUM) / 6.4 CAL_ACHIEVE,
|
|||
|
|
CLAC_VAL CAL_VALUE,
|
|||
|
|
CLAC_VAL / 6.4 PRETAX_INCOME,
|
|||
|
|
nvl(bo.ddje, 0) / 6.4 ddje,
|
|||
|
|
(case STATE when 0 then 1 else 0 end) DEL_FLAG,
|
|||
|
|
nvl(CLAC_COUNTRY, bo.pk_country) PK_COUNTRY,
|
|||
|
|
CLAC_TIME CREATION_TIME
|
|||
|
|
from g_detail_repur_range@link_test gi
|
|||
|
|
left join g_bonus_day@link_test gd
|
|||
|
|
on gi.user_name = gd.user_name and gi.period = gd.period
|
|||
|
|
left join g_bonus_day@link_test gy
|
|||
|
|
on gy.user_name = gi.user_name and gy.period = 352
|
|||
|
|
left join bs_order@link_test bo
|
|||
|
|
on bo.ddbh = gi.source_order_code
|
|||
|
|
left join bs_order_items@link_test bi
|
|||
|
|
on bo.ddbh = bi.ddbh and gi.SOURCE_PRODUCT=bi.CPID
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
|
|||
|
|
merge into cu_member_bonus_range a
|
|||
|
|
using (select cp.pk_id, bi.pk_id pk_bonus_items
|
|||
|
|
from cu_member_bonus_range cp
|
|||
|
|
left join bd_bonus_items bi
|
|||
|
|
on cp.pk_bonus_items = bi.bonus_value and cp.pk_country = bi.pk_country
|
|||
|
|
where bi.bonus_value = 8) b
|
|||
|
|
on (a.pk_id = b.pk_id)
|
|||
|
|
when matched then
|
|||
|
|
update set a.pk_bonus_items = b.pk_bonus_items;
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
insert into cu_member_month2023
|
|||
|
|
select a.*,
|
|||
|
|
b.A_SUM_PV,
|
|||
|
|
b.b_SUM_PV,
|
|||
|
|
b.REP_A_SUM_PV,
|
|||
|
|
b.REP_b_SUM_PV,
|
|||
|
|
b.REGISTER_PV,
|
|||
|
|
b.UPGRADE_PV,
|
|||
|
|
b.REPURCHASE_PV,
|
|||
|
|
b.HI_FUN_PV,
|
|||
|
|
b.MALL_PV
|
|||
|
|
from (select 941 period,
|
|||
|
|
ct.pk_member,
|
|||
|
|
ct.pk_parent,
|
|||
|
|
ct.pk_place_parent,
|
|||
|
|
ct.place_dept,
|
|||
|
|
ct.member_code,
|
|||
|
|
ct.member_name,
|
|||
|
|
ct.customer_type,
|
|||
|
|
ct.phone,
|
|||
|
|
ct.pk_grade,
|
|||
|
|
ct.pk_awards,
|
|||
|
|
ct.pk_team_code,
|
|||
|
|
ct.pk_center_code,
|
|||
|
|
ct.pk_vertex,
|
|||
|
|
ct.pk_settle_country
|
|||
|
|
from cu_member_tree202307 ct
|
|||
|
|
where period = 941) a
|
|||
|
|
inner join (select pk_member,
|
|||
|
|
sum(A_NEW_PV) A_SUM_PV,
|
|||
|
|
sum(b_NEW_PV) b_SUM_PV,
|
|||
|
|
sum(REP_A_NEW_PV) REP_A_SUM_PV,
|
|||
|
|
sum(REP_b_NEW_PV) REP_b_SUM_PV,
|
|||
|
|
sum(register_new_pv) REGISTER_PV,
|
|||
|
|
sum(upgrade_new_pv) UPGRADE_PV,
|
|||
|
|
sum(repurchase_new_pv) REPURCHASE_PV,
|
|||
|
|
sum(hi_fun_new_pv) HI_FUN_PV,
|
|||
|
|
sum(mall_new_pv) MALL_PV
|
|||
|
|
from cu_member_achieve202307
|
|||
|
|
group by pk_member) b
|
|||
|
|
on a.pk_member = b.pk_member
|
|||
|
|
|
|||
|
|
941 cu_member_achieve202307
|
|||
|
|
910 cu_member_achieve202306
|
|||
|
|
880 cu_member_achieve202305
|
|||
|
|
849 cu_member_achieve202304
|
|||
|
|
819 cu_member_achieve202303
|
|||
|
|
788 cu_member_achieve202302
|
|||
|
|
760 cu_member_achieve202301
|