WMS2.0 获取库存

背景简介
货主出库订单重复传了,或者开单客户选错了等，占用了库存，需要删除

涉及系统

SCM    英克WMS（2.0）

SCM

一，入库订单6301 6401 ，订单数据

-- 货主入库订单 6301
SELECT A.*,A.rowid FROM Tpl_purchase_order A WHERE srcno = 'GI1094498';
SELECT A.*,A.rowid FROM Tpl_Purchase_Order_Dtl A,Tpl_purchase_order B WHERE A.PORDERID=B.PORDERID AND  B.srcno = 'GI1094498';

-- 货主入库订单 6401
SELECT A.*,A.rowid FROM tpl_in_order A WHERE srcno = 'GI1094498';
SELECT A.*,A.rowid FROM tpl_in_order_Dtl A,tpl_in_order B WHERE A.inid=B.inid AND  B.srcno = 'GI1094498';

二,货主出库6302 6402订单

--6302 总单,细单状态改为 0
SELECT a.usestatus , A.*,A.rowid FROM tpl_export_order A WHERE srcexpno = 'GIPS18009638' ;
SELECT a.usestatus ,a.goodsownid, a.lotno, A.*,A.rowid FROM tpl_export_order_dtl A ,tpl_export_order b WHERE 1=1 
and a.exporderid = b.exporderid
and b.srcexpno = 'GIPS18009638' ;

--6402 总单,细单状态改为 0
SELECT  a.usestatus , A.*,A.rowid FROM tpl_out_order_v A WHERE  srcexpno = 'GIPS18009638' ;
SELECT  a.usestatus ,a.goodsownid, a.lotno,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 = 'GIPS18009638' 
;
--6508 删除占用库存  goodsqty 为占用库存
select t.goodsqty, t.*,t.rowid from tpl_st_io_doc_tmp t ,tpl_st_qty_lst a, tpl_goods h, wms_goods_lot  l where 1=1
and a.ownergoodsid = h.ownergoodsid(+)
and t.goodsid= a.goodsid
and a.lotid = l.lotid(+)
and h.mdmgoodscode='GI310816'
and l.lotno='20221115'
and t.orderid in (SELECT outid FROM tpl_out_order WHERE  srcexpno = 'GIPS18009638')

WMS

一，删除订单数据

-- WMS 6402
SELECT A.*,A.rowid FROM tpl_out_order A WHERE outid=15655060;
SELECT A.*,A.rowid FROM tpl_out_order_dtl A WHERE outid=15655060;

-- wms 4402   物流出库订单ID outid
SELECT A.*,A.rowid FROM wms_out_order A WHERE outid = 25290;
SELECT A.*,A.rowid FROM wms_out_order_dtl A WHERE  outid=25290;

二，恢复WMS中的库存平台库存占用，注意：此订单下的所有库存占用需要逐一恢复

• 查询6508， 查询条件 是 SKU和 批号，获得货品主档ID和批号ID
  

-- WMS 6508 ，修改busiqty的值
SELECT A. goodsqty,A.busiqty,A.rowid from tpl_st_qty_lst a where 1=1
   and goodsownerid = 1021
   and goodsid = 24588
   and lotid= 41034;

方法二 调用出库接口 POSTMAN 抽单

1.获取 从货主出库订单管理6302 ，获取 EXTERNALSOID ，EXTERNALDTSOID 号，备用

SELECT A.EXTERNALSOID FROM TPL_EXPORT_ORDER A WHERE 1=1
AND SRCEXPNO='SJ1JSF22090016';


SELECT B.EXTERNALDTSOID FROM TPL_EXPORT_ORDER A ,TPL_EXPORT_ORDER_DTL B WHERE 
A.EXPORDERID=B.EXPORDERID 
AND A.SRCEXPNO='SJ1JSF22090016';

地址 ：http://scm-uat.cq-p.com.cn:8060/inca/otherwms/thirdErp/cancelOutorder

替换入正确的

{
    "REQUEST": {
        "HEAD": {
            "PAGENUM": null,
            "ACCOUNT": "SJ02",
            "MODULE": null,
            "PASSWORD": "HMmX5i5En5UngMFD",
            "CONSUMER": '88888',
            "COUNT": null,
            "BIZTRANSACTIONID": "SF_CKCD_2022092800001",
            "SRVLEVEL": null,
            "PAGESIZE": null
        },
        "LIST": [{
            "OWNERID": "SJ02",
            "STOCKID": "BQ",
            "EXTERNALSOID": "2303XS046",
            "CHILD_TABLE_LIST":[{
                "EXTERNALDTSOID": "5"
            }]
        }]
    }
}