forked from angelo/java-retail-app
95 lines
2.5 KiB
SQL
95 lines
2.5 KiB
SQL
|
|
truncate table AC_REPURCHASE_PICK;
|
|
truncate table AC_REPURCHASE_PICK_LOG;
|
|
|
|
-- 复购提货（代言礼包）
|
|
insert into AC_REPURCHASE_PICK
|
|
(pk_id,
|
|
pk_order,
|
|
pk_member,
|
|
pk_product,
|
|
pk_wares,
|
|
base_quantity,
|
|
buy_quantity,
|
|
usable_quantity,
|
|
used_quantity,
|
|
end_time,
|
|
creation_time,
|
|
pk_creator,
|
|
modified_time,
|
|
pk_modified,
|
|
pk_country)
|
|
select brp.pk_id PK_ID,
|
|
bo.pkid PK_ORDER,
|
|
su.user_id PK_MEMBER,
|
|
brp.product_id PK_PRODUCT,
|
|
0 PK_WARES,
|
|
brp.base_quantity base_quantity,
|
|
brp.buy_quantity buy_quantity,
|
|
brp.usable_quantity usable_quantity,
|
|
brp.used_quantity used_quantity,
|
|
brp.end_time end_time,
|
|
brp.create_time creation_time,
|
|
sucr.user_id pk_creator,
|
|
brp.update_time modified_time,
|
|
suup.user_id pk_modified,
|
|
brp.pk_country pk_country
|
|
from hzs_cn.BS_REPURCHASE_PICK brp
|
|
inner join hzs_cn.bs_order bo
|
|
on bo.ddbh = brp.order_code
|
|
inner join hzs_cn.sys_user su
|
|
on su.user_name = brp.user_name
|
|
left join hzs_cn.sys_user sucr
|
|
on sucr.user_name = brp.create_by
|
|
left join hzs_cn.sys_user suup
|
|
on suup.user_name = brp.update_by;
|
|
|
|
-- 删除序列
|
|
drop SEQUENCE AC_REPURCHASE_PICK_SEQ;
|
|
|
|
-- 重建序列
|
|
create sequence AC_REPURCHASE_PICK_SEQ
|
|
minvalue 1
|
|
maxvalue 9999999999
|
|
start with 200
|
|
increment by 1
|
|
cache 10;
|
|
|
|
|
|
-- 复购提货日志（代言礼包）
|
|
insert into AC_REPURCHASE_PICK_LOG
|
|
(pk_id,
|
|
pk_repurchase_pick,
|
|
receive_month,
|
|
receive_quantity,
|
|
pk_receive,
|
|
receive_time,
|
|
receive_flag,
|
|
creation_time,
|
|
pk_creator)
|
|
select brpl.pk_id pk_id,
|
|
brpl.pk_repurchase_pick pk_repurchase_pick,
|
|
brpl.receive_month receive_month,
|
|
brpl.receive_quantity receive_quantity,
|
|
su.user_id pk_receive,
|
|
brpl.receive_time receive_time,
|
|
brpl.receive_flag receive_flag,
|
|
brpl.create_time creation_time,
|
|
sucr.user_id pk_creator
|
|
from hzs_cn.BS_REPURCHASE_PICK_LOG brpl
|
|
left join hzs_cn.sys_user su
|
|
on su.user_name = brpl.receive_by
|
|
left join hzs_cn.sys_user sucr
|
|
on sucr.user_name = brpl.create_by;
|
|
|
|
-- 删除序列
|
|
drop SEQUENCE AC_REPURCHASE_PICK_LOG_SEQ;
|
|
|
|
-- 重建序列
|
|
create sequence AC_REPURCHASE_PICK_LOG_SEQ
|
|
minvalue 1
|
|
maxvalue 9999999999
|
|
start with 1000
|
|
increment by 1
|
|
cache 10;
|