系統(tǒng)中所有跟訂單相關的業(yè)務都需要添加OAID的賦值和存儲威鹿，為了后期對用戶信息進行解密民效。
- 數(shù)據(jù)庫結構改動
- 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);
- 數(shù)據(jù)庫存儲過程改動
- 1)訂單轉單改動顶籽，自動任務轉換驻子，以及手工轉換
sp_oms_cvttbordallcustomer // 添加 OAID賦值
line435 oaid
line614 t.oaid
- 自動合單和手工合單
sp_oms_automergerorder
line 245 添加OAID,TID
--NewOrderId ,
--AllStoreSite ,
--IsToDrp ,
--ScanTime ,
--IsGiveLogistic ,
--PickingCode
, companyid, codamount, oms_distributorid, ieswarehouseid, haspresalesku --發(fā)貨倉庫谭羔，默認為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 優(yōu)化執(zhí)行速度
line 326 添加 max(OAID),MAX(TID)
,max(oaid),max(tid)
FROM mergerorders
- 手工拆單和自動拆單
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,
- 對接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 := '該單據(jù)正被其他人操作，請稍后再試陶衅！';
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);
- 檢查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;
