oracle日常开发-TQ改造加密升级

系统中所有跟订单相关的业务都需要添加OAID的赋值和存储，为了后期对用户信息进行解密。

1. 数据库结构改动

• 1）TB订单表(T_OMSTaobaoOrder)添加 OAID 字段
  语句：alter table t_omstaobaoorder add OAID varchar2(200);
• 2）线上订单表(T_OMSOnlineOrder)添加OAID字段
  语句：alter table t_omsonlineorder add OAID varchar2(200);

2. 数据库存储过程改动

• 1)订单转单改动，自动任务转换，以及手工转换

sp_oms_cvttbordallcustomer // 添加 OAID赋值
line435     oaid
line614     t.oaid

• 2. 自动合单和手工合单

sp_oms_automergerorder

line 245 添加OAID,TID
--NewOrderId ,
 --AllStoreSite ,
--IsToDrp ,
--ScanTime ,
--IsGiveLogistic ,
--PickingCode
, companyid, codamount, oms_distributorid, ieswarehouseid, haspresalesku --发货仓库，默认为0 ，20150922 chenmin
,oaid, tid

line 282  添加OAID,TID
 , a.receivmobile, a.companyid, a.codamount, a.oms_distributorid, a.haspresalesku
,oaid, tid
FROM t_omsonlineorder a
--By ljz 20171211 优化执行速度

line 326 添加 max(OAID),MAX(TID)
 ,max(oaid),max(tid)
 FROM mergerorders

• 3. 手工拆单和自动拆单

sp_oms_newsplitorder

line 384 添加oaid
 audittime, tid, haspresalesku
,oaid)
SELECT v_neworderid,

line 424 添加oaid
 END, presalestatus, iesweight, audittime, tid, haspresalesku
 ,oaid
FROM t_omsonlineorder
WHERE id = orderid;

• 4） 作废原单生成新单修改存储过程

sp_oms_insertcopyorder

line 152 添加OAID
--本地商品重量
 , splitorigorder
 ,oaid)
 SELECT v_orderid,
--订单ID

line 277 添加OAID
splitorigorder, oaid
FROM t_omsonlineorder

• 5）修改获取订单存储过程

Sp_OMS_GetOrdersByIds

line49 添加 a.OAID,a.TID
b.EWayBillPassword,
b.EWayBillUserName,
a.Id,a.oaid,a.tid,

• 6. 对接wms 修改存储过程

sp_oms_api_onliordsgetbysts

line184 添加OAID,TID
 --物流编码
 logisticsnumber,
'' as PRODID
,oaid , tid

line 280 添加oaid，tid
--来源平台名称,
--3pl订单
a.threepltiming,
a.oaid,a.tid

• 7） 对手工新增订单修改存储过程

sp_oms_insertorder

line41   
v_oriorderid       number;

line74 新增 oriorderid
 t.shipperid,t.oriorderid

line78 新增 v_oriorderid
v_expressid,v_oriorderid

line 252 
END IF;
--------------------------------------------------------  
  --说明是复制订单，存在原单id
  IF nvl(v_oriorderid, 0) <> 0
  THEN
   --20210708 增加赋值OAID
     UPDATE T_OMSONLINEORDER T
        SET T.OAID = (select oaid from T_OMSONLINEORDER O
                        WHERE O.ID = v_oriorderid)
      WHERE T.ID =  v_orderid
        AND EXISTS(SELECT 1 FROM T_OMSONLINEORDER O
                    WHERE O.ID = v_oriorderid
                      AND ((NVL(O.RECEIVADDRESS,'A') = NVL(T.RECEIVADDRESS,'A')) AND
                           (NVL(O.RECEIVNAME,'A') = NVL(T.RECEIVNAME,'A')) AND
                           (NVL(O.RECEIVMOBILE,'A') = NVL(T.RECEIVMOBILE,'A')) AND
                           (NVL(O.RECEIVPHONE,'A') = NVL(T.RECEIVPHONE,'A'))
                            )
                      );
  END IF;
  ---------------------------------
  --更新订单明细国标码
  UPDATE t_omsonlineorderproduct a

• 8)对手工修改地址修改存储过程

sp_oms_upaddressforinvadord

line 99 增加
      msg  := '该单据正被其他人操作，请稍后再试！';
      RETURN;
  END;
------------------------------------  
  update t_omsonlineorder
  set oaid = '' -- 20210707 清除oaid
  where id = v_omsonlineorderid; 
----------------------------------------  
  --  chenmin 2017-03-31 已审核订单不允许操作
  sp_oms_updateorderforjudge(v_omsonlineorderid, '修改地址', operator, code, msg);

• 9. 检查T_OMSREFUNDORDER表的字段ORIGINALRECEIVMOBILE1与t_omsonlineorder表receivmobile字段类型是否一致

select * from all_tab_columns 
where owner=upper('bosnds3')  --注意用户
and table_name in (upper('T_OMSREFUNDORDER'),upper('t_omsonlineorder'))
and column_name in (upper('ORIGINALRECEIVMOBILE1'),upper('receivmobile'))

-- 如果不一致
alter table T_OMSREFUNDORDER add  ORIGINALRECEIVMOBILE1 varchar2(510);
update T_OMSREFUNDORDER set ORIGINALRECEIVMOBILE1 =ORIGINALRECEIVMOBILE;
alter table T_OMSREFUNDORDER drop column ORIGINALRECEIVMOBILE;
alter table T_OMSREFUNDORDER rename column ORIGINALRECEIVMOBILE1 to ORIGINALRECEIVMOBILE;