-- 商城订单迁移（缺少用户顶点）（已删除 或者 不存在的会员 订单未进行迁移） insert into sa_order (pk_id, pk_member, order_code, order_type, pk_rate, order_amount, order_achieve, order_ass_achieve, postage, rec_name, rec_phone, rec_province, rec_city, rec_county, rec_address, remark, order_status, pk_reference, pk_placement, tran_type, delivery_way, pay_time, pay_type, del_flag, creation_time, modified_time, pk_creator, pk_modified, pk_country) select 600000 + bmo.pkid pk_id, suyh.user_id pk_member, bmo.ddbh order_code, case when bmo.ddlx = 6 then 13 else 10 end order_type, bc.pk_id pk_rate, bmo.zjg order_amount, trunc(nvl(bmo.pv, 0) / 6.4, 6) order_achieve, nvl(bmo.bv, 0) order_ass_achieve, nvl(bmo.yf, 0) postage, bmo.shrm REC_NAME, bmo.lxdh REC_PHONE, (select ba.pk_id from bd_area ba where ba.pk_country = 1 and ba.code = nvl(basheng.bh, bmo.sheng)) REC_PROVINCE, (select ba.pk_id from bd_area ba where ba.pk_country = 1 and ba.code = nvl((select bashi.bh from hzs_cn.bs_area_info bashi where bashi.mc = bmo.shi and bashi.parent_bh = basheng.bh), bmo.shi)) REC_CITY, (select ba.pk_id from bd_area ba where ba.pk_country = 1 and ba.code = nvl((select baxian.bh from hzs_cn.bs_area_info baxian where baxian.mc = bmo.xian and baxian.parent_bh = (select bashi.bh from hzs_cn.bs_area_info bashi where bashi.mc = bmo.shi and bashi.parent_bh = basheng.bh)), bmo.xian)) REC_COUNTY, bmo.DIZHI rec_address, bmo.bz remark, nvl(bmo.STATUS, 1) ORDER_STATUS, cm.PK_PARENT pk_reference, cm.PK_PLACE_PARENT pk_placement, 1 tran_type, 1 delivery_way, nvl(bmo.zfsj, bmo.create_time) pay_time, case when bmo.ZFLX = 0 then 1 else 0 end PAY_TYPE, bmo.del_flag del_flag, bmo.create_time creation_time, bmo.update_time modified_time, sucr.user_id pk_creator, suup.user_id pk_modified, 1 pk_country from hzs_cn.BS_MALL_ORDER bmo inner join hzs_cn.sys_user suyh on suyh.user_name = bmo.yhbh left join hzs_cn.sys_user sucr on sucr.user_name = bmo.create_by left join hzs_cn.sys_user suup on suup.user_name = bmo.update_by left join hzs_cn.bs_area_info basheng on basheng.mc = bmo.sheng left join bd_currency bc on bc.del_flag = 0 and bc.pk_country = 1 left join cu_member cm on cm.member_code = bmo.YHBH where bmo.del_flag = 0 and bmo.create_time >= to_date('2023-01-01 00:00:00', 'yyyy--mm-dd hh24:mi:ss') and to_char(bmo.xdsj, 'YYYY-MM-DD') < '2021-12-01' and suyh.user_type != 0 union select 600000 + bmo.pkid pk_id, suyh.user_id pk_member, bmo.ddbh order_code, case when bmo.ddlx = 6 then 13 else 10 end order_type, bc.pk_id pk_rate, bmo.zjg order_amount, trunc(nvl(bmo.pv, 0) / 6.4, 6) order_achieve, nvl(bmo.bv, 0) order_ass_achieve, nvl(bmo.yf, 0) postage, bmo.shrm REC_NAME, bmo.lxdh REC_PHONE, basheng.pk_id rec_province, bashi.pk_id rec_city, baxian.pk_id rec_county, bmo.DIZHI rec_address, bmo.bz remark, nvl(bmo.STATUS, 1) ORDER_STATUS, cm.PK_PARENT pk_reference, cm.PK_PLACE_PARENT pk_placement, 1 tran_type, 1 delivery_way, nvl(bmo.zfsj, bmo.create_time) pay_time, case when bmo.ZFLX = 0 then 1 else 0 end PAY_TYPE, bmo.del_flag del_flag, bmo.create_time creation_time, bmo.update_time modified_time, sucr.user_id pk_creator, suup.user_id pk_modified, 1 pk_country from hzs_cn.BS_MALL_ORDER bmo inner join hzs_cn.sys_user suyh on suyh.user_name = bmo.yhbh left join hzs_cn.sys_user sucr on sucr.user_name = bmo.create_by left join hzs_cn.sys_user suup on suup.user_name = bmo.update_by left join bd_area basheng on basheng.pk_country = 1 and basheng.code = bmo.SHENG left join bd_area bashi on bashi.pk_country = 1 and bashi.code = bmo.SHI left join bd_area baxian on baxian.pk_country = 1 and baxian.code = bmo.XIAN left join bd_currency bc on bc.del_flag = 0 and bc.pk_country = 1 left join cu_member cm on cm.member_code = bmo.create_by where bmo.del_flag = 0 and bmo.create_time >= to_date('2023-01-01 00:00:00', 'yyyy--mm-dd hh24:mi:ss') and to_char(bmo.xdsj, 'YYYY-MM-DD') >= '2021-12-01' and suyh.user_type != 0; -- 商城订单明细迁移 insert into sa_order_items (pk_id, pk_order, pk_product, price, quantity, pk_rate, achievement, ass_achievement, pk_grade, pk_awards, order_status, del_flag, creation_time, modified_time, pk_country, pk_creator, pk_modified, specs_name, wares_quantity, logistics_code, logistics_company) select 1000000 + bmoi.pkid pk_id, 600000 + bmo.pkid pk_order, bmoi.cpid pk_product, bmoi.jg price, bmoi.cpsl quantity, bc.pk_id pk_rate, nvl(bmoi.pv, 0) achievement, nvl(bmoi.bv, 0) ass_achievement, cm.pk_settle_grade pk_grade, cm.pk_awards pk_awards, case when bmoi.DDZT = 1 then 3 when bmo.status = 1 then 1 when bmo.status = 0 then 0 else 3 end order_status, bmoi.del_flag del_flag, bmo.create_time creation_time, bmo.update_time modified_time, 1 pk_country, sucr.user_id pk_creator, suup.user_id pk_modified, nvl(bp.cpgg, bmoi.SPECIFICATIONS) specs_name, bmoi.CPSL wares_quantity, bmoi.kddh logistics_code, bmoi.kdgs logistics_company from hzs_cn.bs_mall_order_items bmoi inner join hzs_cn.bs_mall_order bmo on bmo.ddbh = bmoi.ddbh inner join hzs_cn.sys_user suyh on suyh.user_name = bmo.yhbh inner join cu_member cm on cm.pk_id = suyh.user_id left join hzs_cn.sys_user sucr on sucr.user_name = bmo.create_by left join hzs_cn.sys_user suup on suup.user_name = bmo.update_by left join hzs_cn.bs_product bp on bp.pkid = bmoi.cpid left join bd_currency bc on bc.del_flag = 0 and bc.pk_country = 1 where bmoi.del_flag = 0 and bmo.del_flag = 0 and bmo.create_time >= to_date('2023-01-01 00:00:00', 'yyyy--mm-dd hh24:mi:ss') and suyh.user_type != 0 order by bmoi.pkid desc;