--------充值---------- drop SEQUENCE CU_MEMBER_RECHARGE_SEQ; -- Create sequence create sequence CU_MEMBER_RECHARGE_SEQ minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 10; insert into hzs_cloud.CU_MEMBER_RECHARGE (PK_ID, PK_MEMBER, RECHARGE_AMOUNT, PK_ACCOUNT, RECHARGE_TYPE, RECHARGE_CODE, APPROVE_STATE, APPROVE_TIME, APPROVER, REMARKS, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, PK_RATE, PK_CREATOR, PK_MODIFIED, APPROVE_REMARKS, BATCH, RECHARGE_METHOD, RECHARGE_SOURCE) select CU_MEMBER_RECHARGE_SEQ.NEXTVAL PK_ID, (select m.pk_id from cu_member m where m.member_code = c.yhbh) PK_MEMBER, c.czje RECHARGE_AMOUNT, (select a.pk_id from bd_account a where a.field_value = decode(c.bzlx,12,8,c.bzlx ) and a.pk_country = c.pk_country and a.enable_state = 0) PK_ACCOUNT, 4 RECHARGE_TYPE, c.czbh RECHARGE_CODE, c.shzt + 1 APPROVE_STATE, c.shsj APPROVE_TIME, (select m.pk_id from cu_member m where m.member_code = c.shr) APPROVER, c.czbz REMARKS, c.del_flag, c.pk_country, c.create_time CREATION_TIME, c.update_time MODIFIED_TIME, (select bc.pk_id from bd_currency bc where bc.pk_country = c.pk_country ) PK_RATE, nvl((select m.pk_id from cu_member m where m.member_code = c.create_by ),100000000) PK_CREATOR, (select m.pk_id from cu_member m where m.member_code = c.update_by) PK_MODIFIED, c.shbz APPROVE_REMARKS, c.czbh BATCH, case when c.czje > 0 then 1 else 2 end RECHARGE_METHOD, case when c.czlx = 7 then 1 when c.czlx = 8 then 1 when c.czlx = 9 then 1 when c.czlx = 10 then 1 when c.czlx = 20 then 1 else 0 end RECHARGE_SOURCE from hzs_cn.bs_charge c where (select m.pk_id from cu_member m where m.member_code = c.yhbh) is not null; commit; ----查询对比---- select count(1) from hzs_cn.bs_charge c where (select m.pk_id from cu_member m where m.member_code = c.yhbh) is not null; -----备注---- -------17 马来、105 尼印有政策币 insert into BD_ACCOUNT (PK_ID, ACCOUNT_NAME, PK_TRANSACTION_KEY, SORT, FIELD_VALUE, ENABLE_STATE, IS_TRANSFER, IS_WITHDRAWAL, EFFECTIVE_DATE, BACKGROUND_IMG, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, IS_RECHARGE, IS_REGULAR, IS_POSTAGE, PK_CREATOR, PK_MODIFIED, PK_BD_ACCOUNT, ON_LINE_RECHARGE, SHOW_STATUS) values (bd_account_seq.nextval , '政策币账户', null, 3, 3, 0, 0, 0, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/20230420/40e0478d-d01c-4db1-b916-dadb369b287c.jpg', 0, 17, to_date('15-06-2023 14:35:46', 'dd-mm-yyyy hh24:mi:ss'), null, 0, 0, 0, 100000000, null, null, 1, null); insert into BD_ACCOUNT (PK_ID, ACCOUNT_NAME, PK_TRANSACTION_KEY, SORT, FIELD_VALUE, ENABLE_STATE, IS_TRANSFER, IS_WITHDRAWAL, EFFECTIVE_DATE, BACKGROUND_IMG, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, IS_RECHARGE, IS_REGULAR, IS_POSTAGE, PK_CREATOR, PK_MODIFIED, PK_BD_ACCOUNT, ON_LINE_RECHARGE, SHOW_STATUS) values (BD_ACCOUNT_seq.Nextval, '政策币账户', null, 3, 3, 0, 0, 0, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/20230420/40e0478d-d01c-4db1-b916-dadb369b287c.jpg', 0, 105, to_date('15-06-2023 14:37:09', 'dd-mm-yyyy hh24:mi:ss'), null, 0, 0, 0, 100000000, null, null, 1, null); commit; -----充值详情------- drop SEQUENCE cu_member_recharge_detail_seq; -- Create sequence create sequence cu_member_recharge_detail_seq minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 10; insert into CU_MEMBER_RECHARGE_DETAIL (PK_ID, RECHARGE_CODE, PAYMENT_METHOD, PAYMENT_AMOUNT, CREATION_TIME, DEL_FLAG, PK_CREATOR) select cu_member_recharge_detail_seq.nextval PK_ID, r.recharge_code, r.recharge_type PAYMENT_METHOD, r.recharge_amount PAYMENT_AMOUNT, r.creation_time, r.del_flag, r.pk_creator from cu_member_recharge r where r.recharge_code is not null and r.del_flag=0; commit; ------提现------ drop SEQUENCE CU_MEMBER_WITHDRAW_SEQ; -- Create sequence create sequence CU_MEMBER_WITHDRAW_SEQ minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 10; insert into CU_MEMBER_WITHDRAW (PK_ID, PK_MEMBER, WITHDRAW_CODE, PK_ACCOUNT, PK_RATE, CASH_AMOUNT, SERVICE_CHARGE, INCOME_TAX, ISSUED_AMOUNT, APPROVE_STATE, PAYMENT_TIME, PAYER, REMARKS, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, APPROVER, APPROVE_TIME, PK_CREATOR, PK_MODIFIED, STATUS, PAYER_REMARKS, APPROVE_REMARKS, BATCH) select CU_MEMBER_WITHDRAW_SEQ.Nextval, (select m.pk_id from cu_member m where m.member_code = c.yhbh and m.del_flag = 0) PK_MEMBER, c.sqdh WITHDRAW_CODE, (select a.pk_id from bd_account a where a.field_value = 4 and a.pk_country = c.pk_country and a.enable_state = 0) PK_ACCOUNT, (select bc.pk_id from bd_currency bc where bc.pk_country = c.pk_country and bc.del_flag = 0) PK_RATE, c.txje CASH_AMOUNT, c.sxf SERVICE_CHARGE, c.grsds INCOME_TAX, c.sfje ISSUED_AMOUNT, case when c.zt = 1 then 1 when c.zt = 2 then 5 when c.zt = 3 then 4 when c.zt = 4 then 5 when c.zt = 5 then 1 when c.zt = 6 then 4 end APPROVE_STATE, c.dkrq PAYMENT_TIME, (select m.pk_id from cu_member m where m.member_code = c.dkr and m.del_flag = 0) PAYER, c.bz REMARKS, c.del_flag DEL_FLAG, c.pk_country, c.create_time CREATION_TIME, c.update_time MODIFIED_TIME, (select m.pk_id from cu_member m where m.member_code = c.shr and m.del_flag = 0) APPROVER, c.shrq APPROVE_TIME, nvl((select m.pk_id from cu_member m where m.member_code = c.create_by and m.del_flag = 0),100000000) PK_CREATOR, (select m.pk_id from cu_member m where m.member_code = c.update_by and m.del_flag = 0) PK_MODIFIED, case when c.zt = 4 then 1 when c.zt = 5 then 3 when c.zt = 6 then 2 else 0 end STATUS, c.dkbz PAYER_REMARKS, c.shbz APPROVE_REMARKS, c.sqdh BATCH from hzs_cn.bs_cash c; commit; ----转账--------- drop SEQUENCE cu_member_transfer_seq; -- Create sequence create sequence cu_member_transfer_seq minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 10; insert into cu_member_transfer (PK_ID, PK_MEMBER, TRANSFER_CODE, PK_ACCOUNT, TO_PK_MEMBER, PK_TO_ACCOUNT, PK_RATE, TRANSFER_MONEY, REMARKS, DEL_FLAG, CREATION_TIME, MODIFIED_TIME, PK_COUNTRY, PK_CREATOR, PK_MODIFIED, ISSUED_AMOUNT) select cu_member_transfer_seq.nextval, (select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) PK_MEMBER, t.zzdh TRANSFER_CODE, (select a.pk_id from bd_account a where a.field_value = t.zzlx and a.pk_country = t.pk_country and a.enable_state = 0) PK_ACCOUNT, (select m.pk_id from cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) TO_PK_MEMBER, (select a.pk_id from bd_account a where a.field_value = (case when t.zzlx = '4' then '1' when t.zzlx = '7' then '1' else t.zzlx end ) and a.pk_country = t.pk_country and a.enable_state = 0) PK_TO_ACCOUNT, (select bc.pk_id from bd_currency bc where bc.pk_country = t.pk_country and bc.del_flag = 0) PK_RATE, t.zzje TRANSFER_MONEY, t.bz REMARKS, t.del_flag, t.create_time CREATION_TIME, t.update_time MODIFIED_TIME, t.pk_country, nvl((select m.pk_id from cu_member m where m.member_code = t.create_by and m.del_flag = 0),100000000) PK_CREATOR, (select m.pk_id from cu_member m where m.member_code = t.update_by and m.del_flag = 0) PK_MODIFIED, t.zzje ISSUED_AMOUNT from hzs_cn.bs_trans t where (select m.pk_id from hzs_cloud.cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) is not null and (select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) is not null; commit; --------验证------ select count(1) from cu_member_transfer; select count(1) from hzs_cn.bs_trans t where (select m.pk_id from hzs_cloud.cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) is not null and (select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) is not null; -----账户详情----- drop SEQUENCE cu_member_account_detail_seq; -- Create sequence create sequence cu_member_account_detail_seq minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 10; ---------- 可以调用importData 存储过程--------------------------- insert into cu_member_account_detail (PK_ID, PK_MEMBER, ACCOUNT1, ACCOUNT2, ACCOUNT3, ACCOUNT4, ACCOUNT5, ACCOUNT6, ACCOUNT7, ACCOUNT8, ACCOUNT9, ACCOUNT10, ACCOUNT11, ACCOUNT12, CREATE_TIME, PK_RATE, PK_COUNTRY) select cu_member_account_detail_seq.nextval PK_ID, (select m.pk_id from cu_member m where m.member_code = d.user_name) PK_MEMBER, d.account1, d.account2, d.account3, d.account4, d.account5, d.account6, d.account7, d.account8, d.account9, d.account10, d.account11, d.account12, d.create_time CREATE_TIME, (select bc.pk_id from bd_currency bc where bc.pk_country = u.pk_country and bc.del_flag = 0) PK_RATE, u.pk_country from hzs_cn.sys_user_account_detail d, hzs_cn.sys_user u where u.user_name=d.user_name and (select m.pk_id from cu_member m where m.member_code = d.user_name) is not null and d.create_time>=to_date('2023-06-01','yyyy-mm-dd');