CREATE OR REPLACE TRIGGER cu_member_assess2023_TRIG BEFORE INSERT OR UPDATE ON cu_member_assess2023 FOR EACH ROW DECLARE v_newVal NUMBER(12) := 0; v_incval NUMBER(12) := 0; BEGIN IF INSERTING AND :new.pk_id IS NULL THEN SELECT cu_member_assess_seq.NEXTVAL INTO v_newVal FROM DUAL; IF v_newVal = 1 THEN SELECT NVL(max(pk_id), 0) INTO v_newVal FROM cu_member_assess2023; v_newVal := v_newVal + 1; LOOP EXIT WHEN v_incval >= v_newVal; SELECT cu_member_assess_seq.nextval INTO v_incval FROM dual; END LOOP; END IF; :new.pk_id := v_newVal; END IF; END; CREATE OR REPLACE TRIGGER "CU_MEMBER_ASSESS_DETAIL_TRIG" BEFORE INSERT OR UPDATE ON cu_member_assess_detail FOR EACH ROW DECLARE v_newVal NUMBER(12) := 0; v_incval NUMBER(12) := 0; BEGIN IF INSERTING AND :new.pk_id IS NULL THEN SELECT cu_member_assess_detail_SEQ.NEXTVAL INTO v_newVal FROM DUAL; -- If this is the first time this table have been inserted into (sequence == 1) IF v_newVal = 1 THEN --get the max indentity value from the table SELECT NVL(max(pk_id),0) INTO v_newVal FROM cu_member_assess_detail; v_newVal := v_newVal + 1; --set the sequence to that value LOOP EXIT WHEN v_incval>=v_newVal; SELECT cu_member_assess_detail_SEQ.nextval INTO v_incval FROM dual; END LOOP; END IF; -- assign the value from the sequence to emulate the identity column :new.pk_id := v_newVal; END IF; END; drop sequence CU_MEMBER_ASSESS_SEQ; create sequence CU_MEMBER_ASSESS_SEQ minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20; -- 2021年复购考核 insert into cu_member_assess2021(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_HONOUR@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period <= 364 and su.user_type !='0' union select su.user_id PK_MEMBER, gd.period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, 0 PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from g_Hischeck_Vip@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period <= 364 and su.user_type !='0' order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; --2021年分红考核记录 insert into cu_member_assess2021(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period +1 period, for_month SETTLE_MONTH, 1 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_BV MONTH_BALANCE, CURR_MONTH_REPUR_BV MONTH_ADD, USED_BV MONTH_USED, SURPLUS_BV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_SHARE@link_test gh inner join g_bonus_period_day@link_test gd on gh.for_month = gd.start_date inner join sys_user@link_test su on gh.user_name = su.user_name where gh.period <= 364 order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; --2022年复购考核记录 insert into cu_member_assess2022(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_HONOUR@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period > 364 and su.user_type !='0' and gh.period <= 729 union select su.user_id PK_MEMBER, gd.period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, 0 PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from g_Hischeck_Vip@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period > 364 and su.user_type !='0' and gh.period <= 729 order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; --2022年分红考核记录 insert into cu_member_assess2022(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period +1 period, for_month SETTLE_MONTH, 1 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_BV MONTH_BALANCE, CURR_MONTH_REPUR_BV MONTH_ADD, USED_BV MONTH_USED, SURPLUS_BV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_SHARE@link_test gh inner join g_bonus_period_day@link_test gd on gh.for_month = gd.start_date inner join sys_user@link_test su on gh.user_name = su.user_name where gh.period > 364 and gh.period <= 729 order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; --2023年复购考核记录 insert into cu_member_assess2023(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_HONOUR@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period > 729 and su.user_type !='0' union select su.user_id PK_MEMBER, gd.period, for_month SETTLE_MONTH, 0 ASSESS_TYPE, 0 PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_PV MONTH_BALANCE, CURR_MONTH_REPUR_PV MONTH_ADD, USED_PV MONTH_USED, SURPLUS_PV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from g_Hischeck_Vip@LINK_TEST gh inner join g_bonus_period_day@LINK_TEST gd on gh.for_month = gd.start_date inner join sys_user@LINK_TEST su on gh.user_name = su.user_name where gh.period > 729 and su.user_type !='0' order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; --2023年分红考核记录 insert into cu_member_assess2023(pk_id, PK_MEMBER, period, SETTLE_MONTH, ASSESS_TYPE, PK_AWARDS, ASSESS_TARGET, IS_EXAMINE, ASSESS_STATUS, MONTH_BALANCE, MONTH_ADD, MONTH_USED, MONTH_SURPLUS, CREATION_TIME, PK_COUNTRY, PK_CREATOR) select cu_member_assess_seq.nextval pk_id, a.PK_MEMBER, a.period, to_char(a.SETTLE_MONTH, 'yyyy-mm') SETTLE_MONTH, a.ASSESS_TYPE, ba.pk_id PK_AWARDS, a.ASSESS_TARGET, a.IS_EXAMINE, a.ASSESS_STATUS, a.MONTH_BALANCE, a.MONTH_ADD, a.MONTH_USED, a.MONTH_SURPLUS, a.CREATION_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select su.user_id PK_MEMBER, gd.period + 1, for_month SETTLE_MONTH, 1 ASSESS_TYPE, (nvl(honour_level, 0) * 5) PK_AWARDS, ckval ASSESS_TARGET, (case EXEMPT_STATE when 1 then 0 else 1 end) IS_EXAMINE, (case CHECK_STATE when 1 then 0 else 2 end) ASSESS_STATUS, LAST_MONTH_SURPLUS_BV MONTH_BALANCE, CURR_MONTH_REPUR_BV MONTH_ADD, USED_BV MONTH_USED, SURPLUS_BV MONTH_SURPLUS, CLAC_TIME CREATION_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_SHARE@link_test gh inner join g_bonus_period_day@link_test gd on gh.for_month = gd.start_date inner join sys_user@link_test su on gh.user_name = su.user_name where gh.period > 729 order by SETTLE_MONTH, pk_member) a left join bd_awards ba on ba.awards_value = a.PK_AWARDS and ba.pk_country = a.pk_country; update cu_member_assess2021 set MONTH_BALANCE = MONTH_BALANCE/6.4,MONTH_ADD=MONTH_ADD/6.4, CUR_MONTH_ADD=CUR_MONTH_ADD/6.4,MONTH_USED=MONTH_USED/6.4,MONTH_SURPLUS=MONTH_SURPLUS/6.4; update cu_member_assess2021 set ASSESS_TARGET = ASSESS_TARGET/6.4 where ASSESS_TYPE=0; update cu_member_assess2022 set MONTH_BALANCE = MONTH_BALANCE/6.4,MONTH_ADD=MONTH_ADD/6.4, CUR_MONTH_ADD=CUR_MONTH_ADD/6.4,MONTH_USED=MONTH_USED/6.4,MONTH_SURPLUS=MONTH_SURPLUS/6.4; update cu_member_assess2022 set ASSESS_TARGET = ASSESS_TARGET/6.4 where ASSESS_TYPE=0; update cu_member_assess2023 set MONTH_BALANCE = MONTH_BALANCE/6.4,MONTH_ADD=MONTH_ADD/6.4, CUR_MONTH_ADD=CUR_MONTH_ADD/6.4,MONTH_USED=MONTH_USED/6.4,MONTH_SURPLUS=MONTH_SURPLUS/6.4; update cu_member_assess2023 set ASSESS_TARGET = ASSESS_TARGET/6.4 where ASSESS_TYPE=0; drop sequence Cu_Member_Assess_Detail_Seq; create sequence Cu_Member_Assess_Detail_Seq minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20; -- bv pv交易明细 insert into CU_MEMBER_ASSESS_DETAIL(PK_ID, PK_ASSESS, PK_MEMBER, CHANGE_TYPE, TRADE_ACHIEVE, TRADE_TIME, PK_COUNTRY, PK_CREATOR) select Cu_Member_Assess_Detail_Seq.Nextval PK_ID, ca.pk_id pk_assess, a.pk_member, a.CHANGE_TYPE, a.TRADE_ACHIEVE, a.TRADE_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select (PERIOD + 1) period, su.user_id pk_member, 1 CHANGE_TYPE, (case when EXEMPT_STATE = 1 then 0 when CHECK_STATE = 1 then USED_PV else 0 end) TRADE_ACHIEVE, CLAC_TIME TRADE_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from G_HISCHECK_HONOUR@LINK_TEST gh left join sys_user@LINK_TEST su on gh.user_name = su.user_name order by trade_time) a inner join cu_member_assess2023 ca on ca.pk_member = a.pk_member and ca.period = a.period where a.trade_achieve > 0 and ca.assess_type=0; --2021 --2022 --2023 insert into CU_MEMBER_ASSESS_DETAIL(PK_ID, PK_ASSESS, PK_MEMBER, CHANGE_TYPE, TRADE_ACHIEVE, TRADE_TIME, PK_COUNTRY, PK_CREATOR) select Cu_Member_Assess_Detail_Seq.Nextval PK_ID, ca.pk_id pk_assess, a.pk_member, a.CHANGE_TYPE, a.TRADE_ACHIEVE, a.TRADE_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select (PERIOD + 1) period, su.user_id pk_member, 1 CHANGE_TYPE, (case when EXEMPT_STATE = 1 then 0 when CHECK_STATE = 1 then USED_PV else 0 end) TRADE_ACHIEVE, CLAC_TIME TRADE_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from g_Hischeck_Vip@LINK_TEST gh left join sys_user@LINK_TEST su on gh.user_name = su.user_name order by trade_time) a inner join cu_member_assess2023 ca on ca.pk_member = a.pk_member and ca.period = a.period where a.trade_achieve > 0 and ca.assess_type=0; --2021 --2022 --2023 insert into CU_MEMBER_ASSESS_DETAIL(PK_ID, PK_ASSESS, PK_MEMBER, CHANGE_TYPE, TRADE_ACHIEVE, TRADE_TIME, PK_COUNTRY, PK_CREATOR) select Cu_Member_Assess_Detail_Seq.Nextval PK_ID, ca.pk_id pk_assess, a.pk_member, a.CHANGE_TYPE, a.TRADE_ACHIEVE, a.TRADE_TIME, a.PK_COUNTRY, a.PK_CREATOR from (select (PERIOD + 1) period, su.user_id pk_member, 3 CHANGE_TYPE, (case when EXEMPT_STATE = 1 then 0 when CHECK_STATE = 1 then USED_BV else 0 end) TRADE_ACHIEVE, CLAC_TIME TRADE_TIME, CLAC_COUNTRY PK_COUNTRY, 100000000 PK_CREATOR from g_Hischeck_Share@LINK_TEST gh left join sys_user@LINK_TEST su on gh.user_name = su.user_name order by trade_time) a inner join cu_member_assess2023 ca on ca.pk_member = a.pk_member and ca.period = a.period where a.trade_achieve > 0 and ca.assess_type=1;