truncate table AC_PICK;

-- 提货迁移（新人礼包共34条2022年历史数据未迁移，提货氢氧机因产品已经删除未迁移）
insert into AC_PICK
(pk_id,
 pk_member,
 pick_type,
 pk_data_id,
 specs_name,
 usable_quantity,
 used_quantity,
 pick_cover,
 receive_time,
 is_free_mail,
 pk_postage,
 creation_time,
 pk_creator,
 modified_time,
 pk_modified,
 pk_country,
 base_quantity)
select bpp.pkid                                               pk_id,
       cm.pk_id                                               pk_member,
       bpp.pick_type                                          pick_type,
       bpp.product_id                                         pk_data_id,
       bp.cpgg                                                specs_name,
       bpp.usable_quantity                                    usable_quantity,
       bpp.used_quantity                                      used_quantity,
       nvl(bp.PICK_GOODS_IMG, bp.cpfm)                        pick_cover,
       bpp.receive_time                                       receive_time,
       case
           when bpp.pick_type in (5, 7) then
               1
           when bpp.pick_type = 0 and bpp.product_id in (12531) then
               1
           else
               0
           end                                                is_free_mail,
       case
           when bpp.pick_type in (5, 7) then
               0
           when bpp.product_id = 12531 then
               329 -- 红酒邮费模板ID
           else
               null
           end                                                pk_postage,
       bpp.create_time                                        creation_time,
       nvl(cmcr.pk_id, cm.pk_id)                              pk_creator,
       bpp.update_time                                        modified_time,
       nvl(cmup.pk_id, nvl2(bpp.update_time, cm.pk_id, null)) pk_modified,
       1                                                      pk_country,
       case
           when bpp.pick_type in (5, 7) then
               3
           else
               1
           end                                                base_quantity
from hzs_cn.bs_product_pick bpp
         left join cu_member cm
                   on cm.member_code = bpp.user_name
         left join hzs_cn.bs_product bp
                   on bp.pkid = bpp.product_id
         left join cu_member cmcr
                   on cmcr.member_code = bpp.create_by
         left join cu_member cmup
                   on cmup.member_code = bpp.update_by
where bpp.del_flag = 0
  and bpp.usable_quantity > 0
  and bpp.pick_type in (0, 1, 5, 7, 8)
  and bp.del_flag = 0;

-- 删除序列
drop SEQUENCE AC_PICK_SEQ;

-- 创建序列
create sequence AC_PICK_SEQ
    minvalue 1
    maxvalue 999999999999
    start with 100000
    increment by 1 cache 10;