-- 结算期间 需要修改 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