forked from angelo/java-retail-app
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 |