- 数据库结构改动
create table T_SYS_JDZSCONFIGURE
(
device_id VARCHAR2(510),
user_ip VARCHAR2(510),
appname VARCHAR2(510),
dbinstance_name VARCHAR2(510),
url VARCHAR2(4000)
);
create table T_OMSJDORDERLINK
(
appname VARCHAR2(510),
jd_id VARCHAR2(510),
user_ip VARCHAR2(510),
user_id VARCHAR2(510),
device_id VARCHAR2(510),
file_md5 VARCHAR2(510),
orderids VARCHAR2(510),
operation VARCHAR2(510),
url VARCHAR2(510),
sendto_url VARCHAR2(510),
db VARCHAR2(510),
sql VARCHAR2(510),
logtype NUMBER,
createtime DATE,
uploadstatus NUMBER,
id NUMBER not null,
shopid NUMBER,
updatetime DATE,
sellernick VARCHAR2(510),
PLATFROM NUMBER,
ATI VARCHAR2(510) default '0000000000',
OPERATIONTYPE NUMBER
)
partition by range (CREATETIME)
interval (NUMTODSINTERVAL(1, 'day'))
(partition part_t01 values less than(to_date('2019-11-01', 'yyyy-mm-dd')));
;
-- Add comments to the columns
comment on column T_OMSJDORDERLINK.jd_id
is 'sellernick';
comment on column T_OMSJDORDERLINK.file_md5
is '如对应订单操作为导出，导出的文件MD5值';
comment on column T_OMSJDORDERLINK.orderids
is '订单号列表，用英文逗号分隔，每次最多100条记录。如果超过100条，拆分成多条请求';
comment on column T_OMSJDORDERLINK.operation
is '表附录一 订单操作类型定义表';
comment on column T_OMSJDORDERLINK.url
is '客户端请求的URL，wmsurl';
comment on column T_OMSJDORDERLINK.sendto_url
is '订单推送的目的地URL，比如快递公司的API';
comment on column T_OMSJDORDERLINK.db
is '连接的数据库实例名称或IP';
comment on column T_OMSJDORDERLINK.sql
is 'sql语句';
comment on column T_OMSJDORDERLINK.logtype
is '日志类型：1登录 2订单访问 3传第三方 4数据库操作日志';
comment on column T_OMSJDORDERLINK.createtime
is '创建时间';
comment on column T_OMSJDORDERLINK.uploadstatus
is '上报状态';
comment on column T_OMSJDORDERLINK.updatetime
is '更新时间';
comment on column T_OMSJDORDERLINK.sellernick
is '店铺昵称';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_OMSJDORDERLINK
add constraint PK_T_OMSJDORDERLINK primary key (ID)
using index ;
create index i_T_OMSJDORDERLINK_01 on T_OMSJDORDERLINK (uploadstatus, platfrom, operationtype, logtype) local;
create index i_T_OMSJDORDERLINK_02 on T_OMSJDORDERLINK (shopid) local;
- 数据库存储过程改造
- 1）新增存储过程 sp_oms_addjdorderlink
- 修改订单上传云仓存储过程 （如果不启用云仓可以忽略）
oms_pt_orderso
line 54 增加4个字段
v_ordershopid NUMBER;
v_ieswarehouseid NUMBER;
v_tid NUMBER;
v_code NUMBER;
v_msg VARCHAR2(2000);
line 96 修改
platform, t.ordershopid, tid, t.ieswarehouseid
v_platform, v_ordershopid, v_tid, v_ieswarehouseid
line 258 增加逻辑
END IF;
--------------
IF nvl(v_platform, 0) IN (2, 3, 4,102)
THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid,
'', 3, 0, '传云仓宙斯', v_code, v_msg);
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid,
'UPDATE t_omsonlineorder t SET t.istodrp = 1 WHERE id =' || v_tab_order(v).id || ';',
4, 0, '传云仓宙斯', v_code, v_msg);
END IF;
------------------
COMMIT;
v_succeed_cnt := v_succeed_cnt + 1;
sp_oms_api_updateorderstatus
line 17 增加
v_platform NUMBER;
v_ieswarehouseid NUMBER;
v_tid NUMBER;
line29 增加
, tid, a.ieswarehouseid, a.platform
, v_tid, v_ieswarehouseid, v_platform
line 102 增加
<<update_success>>
sp_oms_addorderlink(v_sourcecode, v_ordershopid, 'QIMEN_CP_NOTIFY', code, msg);
-----------------------------
IF v_platform IN (2, 3, 4,102)
THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid, '',
3, 0, '传WMS宙斯', code, msg);
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid,
' UPDATE t_omsonlineorder t SET orderstatus = 4 WHERE id =' ||
orderid || ';', 4, 0, '传WMS宙斯', code, msg);
END IF;
---------------------------------
sp_oms_insertorderlog(orderid,
sp_oms_auditorder
line 18 增加
v_outcode NUMBER;
v_platform NUMBER;
v_ieswarehouseid NUMBER;
v_tid NUMBER;
line 26 增加
, a.platform, a.ieswarehouseid, a.tid
v_platform, v_ieswarehouseid, v_tid
line 46 增加
sp_oms_addorderlink(v_sourcecode, v_ordershopid, 'QIMEN_ERP_CHECK', code, v_outmsg);
-------------------------------
IF v_platform IN (2, 3, 4,102)
THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid, '',
2, 9, '审核宙斯', v_outcode, v_outmsg);
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid,
'UPDATE t_omsonlineorder SET orderstatus = 3, modifieddate = ' ||
SYSDATE || ', audittime = ' || SYSDATE ||
' WHERE id = rorderid AND orderstatus = 1;', 4, 9, '审核宙斯',
v_outcode, v_outmsg);
END IF;
-------------------------------------------
END;
sp_oms_cancelorder
line 33 增加
v_tid NUMBER;
line 44 增加
,tid
,v_tid
line 126 增加
---------------------------
IF v_platform IN (2, 3, 4,102)
THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid, '',
2, 4, '取消传宙斯', code, msg);
sp_oms_addjdorderlink(v_platform, v_tid, v_ieswarehouseid, '', '', v_ordershopid,
'UPDATE t_omsonlineorder SET orderstatus = 7, modifieddate = ' ||
SYSDATE ||
' WHERE id = v_orderid AND orderstatus IN (1, 2, 3, 4, 51, 52);',
4, 4, '取消传宙斯', code, msg);
END IF;
---------------------------------------------------
--如果不是缺货状态，需要释放库存
sp_oms_getuploadbatchlog
line 81 增加
---------------------
ELSIF v_platfrom = 102 THEN
SELECT t.id BULK COLLECT
INTO v_tab_ids
FROM t_omsjdorderlink t
INNER JOIN t_iesshop p ON t.shopid = p.id
AND p.isactive = 1
WHERE rownum <= pagesize
AND t.createtime >= SYSDATE - 30
AND nvl(uploadstatus, 0) = 0
AND p.platform = v_platfrom
AND t.logtype != 1;
--更新物流下载中状态
UPDATE t_omsjdorderlink
SET uploadstatus = 2
WHERE id IN (SELECT column_value
FROM TABLE(v_tab_ids));
--查询数据
OPEN o_cur FOR
SELECT t.id, t.appname, t.jd_id, t.user_ip, t.user_id, t.device_id,
t.file_md5, t.orderids, t.operation, t.url, t.SQL, t.db,
t.logtype, t.shopid, t.sendto_url,
nvl(t.operationtype, 0) operationtype, t.ati, p.shopsecret,
p.sellernick, t.createtime
FROM t_omsjdorderlink t
INNER JOIN t_iesshop p ON t.shopid = p.id
AND p.isactive = 1
WHERE t.id IN (SELECT column_value
FROM TABLE(v_tab_ids));
END IF;
--------------------------
sp_oms_setinvalid
line 26 增加
v_code NUMBER;
v_msg VARCHAR2(500);
line 60 增加
--开始循环订单，进行取消逻辑
--By ljz 20171101 速度优化
FOR vactive IN (SELECT b.orderstatus, b.id AS orderid,
nvl(b.pickinglistid, 0) AS pickinglistid,
--------------------------------------
b.platform, b.tid,
b.ieswarehouseid, b.ordershopid
-----------------------------------------------
line 122 增加
----------------------------
IF vactive.platform IN (2, 3, 4,102)
THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(vactive.platform, vactive.tid, vactive.ieswarehouseid, '', '',
vactive.ordershopid, '', 2, 8, '作废传宙斯', v_code, v_msg);
sp_oms_addjdorderlink(vactive.platform, vactive.tid, vactive.ieswarehouseid, '', '',
vactive.ordershopid,
'UPDATE t_omsonlineorder SET orderstatus = 8, modifieddate = ' ||
SYSDATE ||
' WHERE id = v_orderid AND orderstatus IN (1, 2, 3, 4, 51, 52);',
4, 8, '作废传宙斯', v_code, v_msg);
END IF;
------------------------------------
--插入订单作废日志
sp_otb_orderso_create_retail
line 62 增加 (ordershopid v_otb_onlineorder.ordershopid%type)
platform v_otb_onlineorder.platform%TYPE,
tid v_otb_onlineorder.tid%TYPE,
ieswarehouseid v_otb_onlineorder.ieswarehouseid%TYPE);
line 126 增加(t.ordershopid)
, t.platform, t.tid, t.ieswarehouseid BULK COLLECT
line 762 增加
GOTO endloop;
END IF;
-------------------------------------------
IF v_oms_orderso(v) . platform IN (2, 3, 4, 102) THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v_oms_orderso(v) . platform,
v_oms_orderso(v) . tid,
v_oms_orderso(v) . ieswarehouseid, '', '',
v_oms_orderso(v).ordershopid, '', 3, 0,
'传WMS宙斯', code, msg);
sp_oms_addjdorderlink(v_oms_orderso(v).platform,
v_oms_orderso(v) . tid,
v_oms_orderso(v) . ieswarehouseid, '', '',
v_oms_orderso(v) . ordershopid,
' UPDATE t_omsonlineorder t SET istodrp = 1 WHERE id =' ||
v_oms_orderso(v).id || ';', 4, 0,
'传WMS宙斯', code, msg);
END IF;
-----------------------------------
v_totalsuccount := v_totalsuccount + 1;
sp_otb_occupy_create_retail
line 39 增加
, t.ordershopid, t.tid, t.platform
line 359 增加
IF SQL%ROWCOUNT = 0 THEN
--raise_application_error(-20201, '该单据已生成零售与调拨单！');
ROLLBACK;
GOTO endloop;
END IF;
-------------------------------------
IF v.platform IN (2, 3, 4, 102) THEN
--京东日志类型：1登录 2订单访问 3传第三方 4数据库操作日志
sp_oms_addjdorderlink(v.platform, v.tid, v.ieswarehouseid, '',
'', v.ordershopid, '', 3, 0, '传WMS宙斯',
code, msg);
sp_oms_addjdorderlink(v.platform, v.tid, v.ieswarehouseid, '',
'', v.ordershopid,
' UPDATE t_omsonlineorder t SET istodrp = 1 WHERE id =' || v.id || ';',
4, 0, '传WMS宙斯', code, msg);
END IF;
----------------------------------------------------------
code := 1;
msg := v.id || '成功！';
11_INIT_DATA
12_配置自动任务
处理序号问题
select max(id) from T_SYSAUTOTASK;
WSeq_T_SYSAUTOTASK
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;
网友评论