SCM 提示多条对码信息

背景简介

由于WMS故障，或者数据库未加限制，导致了数据重复情况，会导致SCM无法下达 单据

涉及系统

SCM    英克WMS（2.0）

违反唯一约束条件 (WMS_BJ_PROD.TPL_INDTL_PK)

在100036 下 查出来

情况一

首先去查订单明细，获得SKU，是否在WMS有重复的数据。有的话清理


--10036获取入库单号后，到6401中查询所有的明细，逐条查询WMS，看是否有两个以上的

SELECT A.GOODSID, A.REGISTNO,A.REGISTDOCNO,A.PRODLICENSENO, A.*,A.ROWID FROM TPL_PUB_GOODS A WHERE MDMGOODSCODE='SJ023274';

SELECT A.WAREGOODSID, A.*,A.ROWID FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNID='SJ023274';
--包装
SELECT A.*,A.ROWID FROM TPL_PUB_GOODS_PACKS A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

--养护
SELECT A.*,A.ROWID FROM GSP_GOODSMAIN A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';




SELECT A.*,A.ROWID FROM WMS_GOODS_TO_POSTYPE A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND  B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.ROWID FROM WMS_GOODS_FEATURE A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND  B.MDMGOODSCODE='SJ023274';

--证照
SELECT A.WAREGOODSID, A.*,A.ROWID FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNID='SJ023274';


SELECT A.*,A.rowid FROM GSP_ST_IO_RECORD A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_OUT_ORDER_DTL A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

SELECT A.*,A.rowid FROM WMS_RECEIVE_DTL A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_ST_QTY_EVERYDAY A ,TPL_PUB_GOODS_PACKS B, TPL_PUB_GOODS C WHERE 1=1
 AND A.GOODSPACKID= B.GOODSPACKID 
 AND B.GOODSID= C.GOODSID 
AND C.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_WAVE_GOODS_DTL A ,TPL_PUB_GOODS_PACKS B, TPL_PUB_GOODS C WHERE 1=1
 AND A.GOODSPACKID= B.GOODSPACKID 
 AND B.GOODSID= C.GOODSID 
AND C.MDMGOODSCODE='SJ023274';

SELECT A.*,A.rowid FROM WMS_IN_ORDER_DTL A , TPL_PUB_GOODS B WHERE 1=1
 AND A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

以下查询出来的，都需要进行清理

--以下SKU都需要清理
select count(mdmgoodscode),mdmgoodscode from tpl_pub_goods a group by a.mdmgoodscode having count(mdmgoodscode)>1

--清理完毕后，加上唯一约束
alter table tpl_pub_goods add constraint TPL_PUB_GOODS_MDMCODE_UNI unique(MDMGOODSCODE);

情况二，写入的值，在数据库中有值了

PO订单下传物流出错：物流返回信息-部分成功:ORA-00001: 违反唯一约束条件 (WMS_BJ_PROD.TPL_INDTL_PK):ERROR insert into tpl_in_order_dtl(INID,INDTLID,GOODSID,GOODSPACKID,PORDERSTATUS,PRINTNO,PRINTLINE,BATCHNO

原因是WMS 表 TPL_INDTL_PK 的索引重复。 根据tpl_in_order_dtl 表得知是 物流入库订单查询 6401 功能，查询

违反唯一约束条件 (WMS_BJ_PROD.TPL_INDTL_PK)

在100036 下 查出来

情况一

首先去查订单明细，获得SKU，是否在WMS有重复的数据。有的话清理


--10036获取入库单号后，到6401中查询所有的明细，逐条查询WMS，看是否有两个以上的

SELECT A.GOODSID, A.REGISTNO,A.REGISTDOCNO,A.PRODLICENSENO, A.*,A.ROWID FROM TPL_PUB_GOODS A WHERE MDMGOODSCODE='SJ023274';

SELECT A.WAREGOODSID, A.*,A.ROWID FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNID='SJ023274';
--包装
SELECT A.*,A.ROWID FROM TPL_PUB_GOODS_PACKS A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

--养护
SELECT A.*,A.ROWID FROM GSP_GOODSMAIN A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';




SELECT A.*,A.ROWID FROM WMS_GOODS_TO_POSTYPE A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND  B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.ROWID FROM WMS_GOODS_FEATURE A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND  B.MDMGOODSCODE='SJ023274';

--证照
SELECT A.WAREGOODSID, A.*,A.ROWID FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNID='SJ023274';


SELECT A.*,A.rowid FROM GSP_ST_IO_RECORD A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_OUT_ORDER_DTL A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

SELECT A.*,A.rowid FROM WMS_RECEIVE_DTL A ,TPL_PUB_GOODS B WHERE A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_ST_QTY_EVERYDAY A ,TPL_PUB_GOODS_PACKS B, TPL_PUB_GOODS C WHERE 1=1
 AND A.GOODSPACKID= B.GOODSPACKID 
 AND B.GOODSID= C.GOODSID 
AND C.MDMGOODSCODE='SJ023274';


SELECT A.*,A.rowid FROM WMS_WAVE_GOODS_DTL A ,TPL_PUB_GOODS_PACKS B, TPL_PUB_GOODS C WHERE 1=1
 AND A.GOODSPACKID= B.GOODSPACKID 
 AND B.GOODSID= C.GOODSID 
AND C.MDMGOODSCODE='SJ023274';

SELECT A.*,A.rowid FROM WMS_IN_ORDER_DTL A , TPL_PUB_GOODS B WHERE 1=1
 AND A.GOODSID= B.GOODSID 
AND B.MDMGOODSCODE='SJ023274';

以下查询出来的，都需要进行清理

--以下SKU都需要清理
select count(mdmgoodscode),mdmgoodscode from tpl_pub_goods a group by a.mdmgoodscode having count(mdmgoodscode)>1

--清理完毕后，加上唯一约束
alter table tpl_pub_goods add constraint TPL_PUB_GOODS_MDMCODE_UNI unique(MDMGOODSCODE);

情况二，写入的值，在数据库中有值了

PO订单下传物流出错：物流返回信息-部分成功:ORA-00001: 违反唯一约束条件 (WMS_BJ_PROD.TPL_INDTL_PK):ERROR insert into tpl_in_order_dtl(INID,INDTLID,GOODSID,GOODSPACKID,PORDERSTATUS,PRINTNO,PRINTLINE,BATCHNO

原因是WMS 表 TPL_INDTL_PK 的索引重复。 根据tpl_in_order_dtl 表得知是 物流入库订单查询 6401 功能，查询

得知是WMS_INDTL_SEQ 的最大值出问题，产生了重复。

然后去PLSQL中，查询这个 SEQ，的最大值，然后把最大值给SEQ

SELECT max(indtlid) FROM tpl_in_order_dtl_v A

修改最大值

再重置10036

得知是WMS_INDTL_SEQ 的最大值出问题，产生了重复。

然后去PLSQL中，查询这个 SEQ，的最大值，然后把最大值给SEQ

SELECT max(indtlid) FROM tpl_in_order_dtl_v A


修改最大值

再重置10036

文档更新时间: 2023-09-01 14:52   作者：周骏