SCM改库存

改6508 库存

SELECT a.goodsstatusid,A.*,A.rowid FROM 
tpl_st_qty_lst A,   
tpl_goods       h,
wms_goods_lot   l
WHERE 1=1  
and a.ownergoodsid = h.ownergoodsid(+)
and a.lotid = l.lotid(+) 
and h.goodsownid='1110242604' 
AND l.lotno='20220901';

--查编码
SELECT A.MDMCODE 货主编码 ,A.SUPPLY_MDCODE, A.SA_OUTSYSCODE 系统编码, A.* FROM CQYYPROD.PUB_ENTRY@INCAPF A WHERE 1=1 
--AND A.MDMCODE IN ('PC', 'PB', 'PD', 'PA', 'PG', '69', 'PL', 'DQ')
AND A.ENTRYNAME like ('重庆医药和平医药批发有限公司巫溪分公司');

改6302 6402 5512 单据状态

--改6302单据状态
--改6302单据状态
SELECT a.goodsstatusid,A.*,A.rowid FROM tpl_export_order_dtl_v A WHERE exporderdtlid IN 
(
'45331906','45331905'  --出库细单ID
);

--改6402单据状态
SELECT a.goodsstatusid,A.*,A.rowid FROM tpl_out_order_dtl_v A WHERE outdtlid in 
(
'108480958','108480957' --订单细单ID
);



--改下传数据接口表状态 ，然后重新传
SELECT a.outdtlid, a.goodsstatusid, A.*,A.rowid FROM tpl2wms_out_dtl_his A WHERE a.docid in (SELECT A.docid FROM tpl2wms_out_doc_his A WHERE 
a.srcexpno='87847'
and outdtlid in --订单细单ID
(
'108480958','108480957'
) );


-- 销毁只需要改 5512 ，和 tpl2wms_out_dtl 接口表
--改5512 销毁记录单据状态
SELECT a.exporderdtlid,  A.*,A.rowid FROM tpl_export_destroy_dtl_v A WHERE  soid = '49897719' and goodsownid='1000468970' and lotno ='P20231161-3' ;

如果MID1 还匹配不起，可以尝试将分公司表的MID1 给 通知单和指示单

--通知单
UPDATE OUTSTOCKINFORMDTTBL C
   SET C.MID1 =
       (SELECT DISTINCT A.MID1
                  FROM STOCKGOODSTBL_COMPANY A
                 WHERE A.GOODSID = C.GOODSID
                    AND A.GOODSBATCH = C.GOODSBATCH
                    AND A.QUALITYFLG = C.QUALITYFLG
                   AND A.COMPANYID = C.COMPANYID
               )
 WHERE EXISTS
 (SELECT   1   FROM STOCKGOODSTBL_COMPANY A
                 WHERE A.GOODSID = C.GOODSID
                    AND A.GOODSBATCH = C.GOODSBATCH
                    AND A.QUALITYFLG = C.QUALITYFLG
                    AND A.COMPANYID = C.COMPANYID
           )
 AND C.OUTSTOCKINFORMID='15949588014' AND C.GOODSID='1000468282';


--指示单
UPDATE OUTSTOCKINDICATIONDTTBL C
   SET C.MID1 =
       (SELECT DISTINCT A.MID1
                  FROM STOCKGOODSTBL_COMPANY A
                 WHERE A.GOODSID = C.GOODSID
                    AND A.GOODSBATCH = C.GOODSBATCH
                    AND A.QUALITYFLG = C.QUALITYFLG
                   AND A.COMPANYID = C.COMPANYID
               )
 WHERE EXISTS
 (SELECT   1   FROM STOCKGOODSTBL_COMPANY A
                 WHERE A.GOODSID = C.GOODSID
                    AND A.GOODSBATCH = C.GOODSBATCH
                    AND A.QUALITYFLG = C.QUALITYFLG
                    AND A.COMPANYID = C.COMPANYID
           )
 AND C.OUTSTOCKINDICATIONID='15949588014' AND C.GOODSID='1000468282';

 SELECT A.*,A.rowid FROM OUTSTOCKINDICATIONDTTBL A WHERE 1=1 AND A.OUTSTOCKINDICATIONID='15949588014' AND GOODSID='1000468282';