SCM 传单据是 提示运输地址为空

背景简介

出库单下传时,可能出 主数据地址 未下传，导致订单无法下达

涉及系统

SCM    英克WMS（2.0）

出库单下传时,可能出 主数据地址 未下传

货主匹配地址信息 使用的是 6302 tpl_export_order.shippingaddress(ERP运输地址)

与 8104 wms_transport_address.psdzyckdzgx (对应的仓库地址) 获得 transid

需要注意 出库类型 进退出库匹配的是 供应商单位 ,普通出库匹配 客户单位

10036 报错

— 问题一,出库单下传物流出错: 物流返回信息-地址主数据不存在！ERP出库订单号:SL0E23030270020232

--按出库单查询 地址 是否错误
--按出库单查询 地址 是否错误
SELECT a.inceptaddr 运输地址,a.GOODSOWNERID 货主id, a.gocompanyid 货主单位ID,a.transid,d.transid 接口tranid,a.psdzyckdzgx,b.shippingaddress 出库单ERP运输地址,b.actualaddress 出库单实际送货地址,a.rowid
FROM 
     WMS_TRANSPORT_ADDRESS a,
     tpl_export_order      b,
     Tpl_go_company_v      c,
     tpl2wms_out_doc       d
WHERE 1=1
  AND C.companyid = a.gocompanyid
  and b.reccompanyid = C.companyid
  and a.usestatus = 1
  and b.srcexpno = d.srcexpno(+)
 and b.srcexpno in (
'SL0E23051270020085'
);

--1.将运输地址复制到 配送地址 确保地址有值
SELECT a.inceptaddr 运输地址,a.psdzyckdzgx ,A.*,A.rowid 
FROM 
     WMS_TRANSPORT_ADDRESS a
     where transid in ('21047189')
--2.传单位
INSERT INTO TPL_COMPANY_SYNC(SEQID,COMPANYID, WAREHID)
       SELECT TPL_COMPANY_SYNC_SEQ.NEXTVAL, A.WARECID, 301
       FROM TPL_GO_COMPANY A WHERE A.GOODSOWNERID = 1850
       AND A.COMPANYID IN (909295);
--3.下发配送地址到物流
INSERT INTO TPL_TRANS_SYNC  (SEQID, TRANSID, WAREHID)
       SELECT TPL_TRANS_SYNC_SEQ.NEXTVAL, A.TRANSID, 301
       FROM WMS_TRANSPORT_ADDRESS A WHERE A.transid in (
'21047189')









  --第一步 按出库单获取的客户编码
  SELECT  a.gcompanyid ,A.*,A.rowid FROM tpl_out_order_v A WHERE 1=1 and srcexpno in (
'SL0E23030370020002'
  );


-- 第二步 按客户编码 下发 地址
INSERT INTO TPL_COMPANY_SYNC(SEQID,COMPANYID, WAREHID)
       SELECT TPL_COMPANY_SYNC_SEQ.NEXTVAL, A.WARECID, 301
       FROM TPL_GO_COMPANY A WHERE A.GOODSOWNERID = 1850
       AND A.COMPANYID IN (
SELECT a.companyid FROM TPL_GO_COMPANY A WHERE 1=1 and goodsownerid = 1850 and Gcompanyid in (
      'SL0E12588'
)
)

--如果还报错,先下发一下单位,再发地址
INSERT INTO TPL_COMPANY_SYNC(SEQID,COMPANYID, WAREHID)
       SELECT TPL_COMPANY_SYNC_SEQ.NEXTVAL, A.WARECID, 301
       FROM TPL_GO_COMPANY A WHERE A.GOODSOWNERID = 1850
       AND A.COMPANYID IN (  SELECT  a.reccompanyid FROM tpl_out_order_v A WHERE 1=1 and srcexpno in (
'SL0E23030370020002'
 ));

--如果还报错,检查下传单据中间表transid是否一致
SELECT a.transid, A.*,A.rowid FROM tpl_out_order A WHERE srcexpno in ('SL0E23030970020011');
SELECT a.transid,A.*,A.rowid FROM tpl_export_order A WHERE srcexpno  in ('SL0E23030970020011');
SELECT a.transid,a.RECEIVEPHONE ,A.*,A.rowid FROM tpl2wms_out_doc A WHERE A.Erporderid in ('SL0E23030970020011');

问题二 WMS_TPL_OUTDTL, 细单必要字段：’货主原始单号’为空！

– 问题2 出库单下传物流出错: 物流返回信息-WMS_TPL_OUTDTL,SEQID=5739528,细单必要字段：’货主原始单号’为空！

--平台出库单表 给1 
SELECT a.srcexpdtlno, A.*,A.rowid FROM tpl_out_order_dtl_v A,tpl_out_order_v B  WHERE 1=1
and a.outid= b.outid
and b.srcexpno in (
'SL0E23030270020241'
);
-- 接口表 srcexpdtlno 给 1
SELECT A.srcexpdtlno,A.*,A.rowid FROM  tpl2wms_out_dtl A ,tpl2wms_out_doc B  WHERE
A.docid=B.DOCID
AND B.Erporderid  in (
'SL0E23030270020241'
);

问题三 出库单下传物流出错: 物流返回信息-非自提出库单的收货电话不能为空!！

– 问题2 出库单下传物流出错: 物流返回信息-WMS_TPL_OUTDTL,SEQID=5739528,细单必要字段：’货主原始单号’为空！

--receivephone  收货人电话不为空
SELECT a.receivephone, A.*,A.rowid FROM tpl_out_order_v A,tpl_out_order_v B  WHERE 1=1
and a.outid= b.outid
and b.srcexpno in (
'SL0E2303217002007'
);
-- receivephone 收货人电话不为空
SELECT a.receivephone,A.*,A.rowid FROM  tpl2wms_out_doc A ,tpl2wms_out_doc B  WHERE
A.docid=B.DOCID
AND B.Erporderid  in (
'SL0E2303217002007'
);

问题三 出库单下传物流出错: 物流返回信息-平台下发订单的运输地址为空,ERP出库订号:6007316944！

– 问题2 出库单下传物流出错: 物流返回信息-WMS_TPL_OUTDTL,SEQID=5739528,细单必要字段：’货主原始单号’为空！

--transid  运输ID不为空
SELECT a.shippingaddress,a.actualaddress, a.transid ,a.inceptaddr,a.realrecaddr,a.receiveaddr, a.receivephone, A.*,A.rowid FROM tpl_out_order_v A,tpl_out_order_v B  WHERE 1=1
and a.outid= b.outid
and b.srcexpno in (
'6007316944'
);

-- transid 运输ID不为空
SELECT a.shippingaddress, a.receivephone,a.transid, A.*,A.rowid FROM  tpl2wms_out_doc A ,tpl2wms_out_doc B  WHERE
A.docid=B.DOCID
AND B.Erporderid  in (
'6007316944'
);

地址中间表

SELECT c.companyname,b.psdzyckdzgx FROM TPL_TRANS_SYNC A ,wms_transport_address_v B ,Tpl_go_company_v c WHERE 1=1
and b.gocompanyid = c.companyid
and a.transid = b.transid 
and c.GOODSOWNERID=1850  
AND a.WAREHID=301 


--删除地址接口表的
delete FROM TPL_TRANS_SYNC A WHERE transid in 
(
  SELECT a.transid FROM TPL_TRANS_SYNC A ,wms_transport_address_v B ,Tpl_go_company_v c WHERE 1=1

  and b.gocompanyid = c. companyid
  and a.transid = b.transid 
   and  c.GOODSOWNERID=1850  
   AND a.WAREHID=301 
);

--已传地址
SELECT A.*,A.rowid FROM WMS_TPL_COMPANYADDR_his A ,wms_transport_address B ,Tpl_go_company_v c WHERE 1=1
and b.gocompanyid = c. companyid
and a.transid = b.transid 
and c.GOODSOWNERID=1850  
AND a.WAREHID=301
and a.transid=21189731


--统计中间表
SELECT count(a.transid) FROM wms_tpl_companyaddr A ,WMS_TRANSPORT_ADDRESS B WHERE a.transid = b.transid  and  a.GOODSOWNERID=1850  AND a.WAREHID=301 


--删除8104所有停用的地址
SELECT count(transid) from  wms_transport_address where TRANSID in (

 SELECT B.TRANSID FROM  wms_transport_address B ,Tpl_go_company_v c WHERE 1=1

  AND B.GOCOMPANYID = C.COMPANYID
  AND C.GOODSOWNERID=1850
  AND B.USESTATUS = 2
)