WMS & SMC 对库存事务脚本

才华有限青年_

已于 2024-04-26 16:48:05 修改

阅读量370

点赞数

文章标签：安全

于 2018-11-22 10:37:35 首次发布

本文链接：https://blog.csdn.net/yx183/article/details/84335039

版权

前端ERP系统和仓库系统分别管了2套库存，每隔一段时间就需要比对一下，看是否存在差异，避免超卖现象。

首先把wms的库存数据导入到smc中，再通过SQL语句来比对。

/*************************************************准备工作*************************************************************/
--导入WMS库存至SMC系统脚本temp_wms_smc_inventory
/*
	drop table temp_wms_smc_inventory
	
	select WarehouseID,CustomerID,SKU,LotAtt08,sum(Qty) Qty,sum(QtyAllocated) QtyAllocated
	from V_SelectINV_LOT_LOC_ID
	where locationid not like '%STAGE' 
	and WarehouseID in('BB1','FLC','HKBT','HJC')
	group by WarehouseID,CustomerID,SKU,LotAtt08

	select * from temp_wms_smc_inventory
	
*/

--SMC实时库存备份表

--备份SMC库存脚本

-- drop table wh_inventory_20181120

select *
into wh_inventory_20181120
from wh_inventory


select * from wh_inventory_20181120

/**************************************************************************************************************/



--WMS库存明细
-- drop table temp_hjz_wms_smc_inventory_bak2

select * from temp_hjz_wms_smc_inventory_bak2

select WarehouseID,CustomerID,SKU,LotAtt08,SUM(Qty) qty,SUM(QtyAllocated) QtyAllocated 
into temp_hjz_wms_smc_inventory_bak2
from temp_wms_smc_inventory
where 1 = 1
group by WarehouseID,CustomerID,SKU,LotAtt08



--SMC库存明细
-- drop table temp_hjz_smc_wms_inventory_bak2

select * from temp_hjz_smc_wms_inventory_bak2


select k.warehousecode,k.compcode,k.outprodcode,k.invtype,sum(k.actualtotal) actualtotal,sum(k.blockqty) blockqty
into temp_hjz_smc_wms_inventory_bak2
from wh_inventory_20181120 k
left join cmp_product c on k.compcode = c.compcode and k.prodid = c.prodid and k.outprodcode = c.outprodcode
where 1 = 1
and k.outprodcode = c.outprodcode
group by k.warehousecode,k.compcode,k.outprodcode,k.invtype




--steop1:SMC与WMS总库存对比
select t2.WarehouseID,t2.WmsQty,t1.SmcQty,t2.WmsQty-t1.SmcQty as ZyQty from 
(
	select warehousecode,cast(SUM(actualtotal) as int) SmcQty from temp_hjz_smc_wms_inventory_bak2
	where 1 = 1
	and  warehousecode IN ('HKBT','FLC','BB1','HJC')
	group by warehousecode
)t1 inner join
(
	select WarehouseID,SUM(Qty) WmsQty
	from temp_hjz_wms_smc_inventory_bak2
	where 1 = 1
	and WarehouseID IN ('HKBT','FLC','BB1','HJC')
	and CustomerID <> 'WS'
	group by WarehouseID
)t2
on t1.warehousecode = t2.WarehouseID 


--steop2:WMS独有库存
select t.WarehouseID,SUM(qty) wmsQty,SUM(actualtotal) smcQty,SUM(qty)-isnull(SUM(actualtotal),0) as ZyQty from 
(   
   select  WarehouseID,CustomerID,SUM(qty) qty,SUM(actualtotal) actualtotal from
   (
		select WarehouseID,CustomerID,SKU,LotAtt08,SUM(qty) qty,SUM(actualtotal) actualtotal from 
		(
			select a.WarehouseID,a.CustomerID,a.SKU,a.LotAtt08,a.Qty,a.QtyAllocated
			,b.warehousecode,b.compcode,b.outprodcode,b.invtype,b.actualtotal,b.blockqty 
			,b.actualtotal - a.Qty as ZyQty
			from temp_hjz_wms_smc_inventory_bak2 a
			left join temp_hjz_smc_wms_inventory_bak2 b 
			on a.WarehouseID = b.warehousecode and a.CustomerID = b.compcode and a.SKU = b.outprodcode 
			and case when a.LotAtt08 = 'N' then 0 when a.LotAtt08 = 'ZKBL' then 2 when a.LotAtt08 = 'DHBL' then 3
			when a.LotAtt08 = 'CQBL' then 4 when a.LotAtt08 = 'YCSJ' then 5 when a.LotAtt08 = 'Y' then 1
			else 6 end = b.invtype
			where 1 = 1
			and  a.WarehouseID  IN ('HKBT','FLC','BB1','HJC')
			and  b.warehousecode is null --仓库多出库存
			--and b.invtype <>6
			and a.CustomerID <> 'WS' --踢除耗材
			
		) k group by WarehouseID,CustomerID,SKU,LotAtt08
	) m group by WarehouseID,CustomerID
) t group by t.WarehouseID

	

--steop3:SMC独有库存
select warehousecode,cast(SUM(wmsQty) as int) wmsQty,cast(SUM(smcQty) as int) smcQty,SUM(ZyQty) ZyQty from 
(
	select t1.warehousecode,t1.compcode,t1.outprodcode,t1.invtype,SUM(t1.qty) wmsQty,SUM(isnull(t1.actualtotal,0)) smcQty,SUM(t1.qty) - SUM(isnull(t1.actualtotal,0)) as ZyQty from 
	(
		select a.WarehouseID,a.CustomerID,a.SKU,a.LotAtt08,a.Qty,a.QtyAllocated
		,b.warehousecode,b.compcode,b.outprodcode,b.invtype,b.actualtotal,b.blockqty 
		,b.actualtotal - a.Qty as ZyQty
		from temp_hjz_wms_smc_inventory_bak2 a
		right join temp_hjz_smc_wms_inventory_bak2 b 
		on a.WarehouseID = b.warehousecode and a.CustomerID = b.compcode and a.SKU = b.outprodcode
		and case when a.LotAtt08 = 'N' then 0 when a.LotAtt08 = 'ZKBL' then 2 when a.LotAtt08 = 'DHBL' then 3
		when a.LotAtt08 = 'CQBL' then 4 when a.LotAtt08 = 'YCSJ' then 5 when a.LotAtt08 = 'Y' then 1
		else 6 end = b.invtype
		where 1 = 1
		and a.WarehouseID is null 
		and b.warehousecode IN ('HKBT','FLC','BB1','HJC')
		and b.invtype<>6
		and isnull(b.actualtotal,0) > 0
		
	) t1
	where 1 = 1
	group by t1.warehousecode,t1.compcode,t1.outprodcode,t1.invtype
) k group by  warehousecode



--steop4:wms,smc产品能匹配上，库存数量不对
select t2.warehousecode,SUM(wmsQty) wmsQty,cast(Sum(smcQty) as float) smcQty,SUM(ZyQty) ZyQty from 
(
	select t1.warehousecode,t1.compcode,t1.outprodcode,t1.invtype,sum(t1.Qty) wmsQty,sum(t1.actualtotal) smcQty,SUM(ZyQty) ZyQty from 
	(		
		select a.WarehouseID,a.CustomerID,a.SKU,a.LotAtt08,a.Qty,a.QtyAllocated
		,b.warehousecode,b.compcode,b.outprodcode,b.invtype,b.actualtotal,b.blockqty 
		,a.Qty - b.actualtotal as ZyQty
		from temp_hjz_wms_smc_inventory_bak2 a
		inner join temp_hjz_smc_wms_inventory_bak2 b 
		on a.WarehouseID = b.warehousecode and a.CustomerID = b.compcode and a.SKU = b.outprodcode
		and case when a.LotAtt08 = 'N' then 0 when a.LotAtt08 = 'ZKBL' then 2 when a.LotAtt08 = 'DHBL' then 3
		when a.LotAtt08 = 'CQBL' then 4 when a.LotAtt08 = 'YCSJ' then 5 when a.LotAtt08 = 'Y' then 1
		else 6 end = b.invtype
		where 1 = 1
		and a.WarehouseID IN('HKBT','FLC','BB1','HJC')
		and a.Qty - b.actualtotal < 0
		and b.invtype<>6
	)t1 group by t1.warehousecode,t1.compcode,t1.outprodcode,t1.invtype
)t2 group by t2.warehousecode


/*********************************************************************************************/

WMS查询库存事务脚本。

--库存余量－历史库存事务
declare @WarehouseID varchar(20),@CustomerID varchar(20),@Sku varchar(20),
		@LotNum varchar(20),@Location varchar(20),@TraceId varchar(20)
	set @WarehouseID = 'HJC'
	set @CustomerID = 'LSK'
	set @Sku = '4562163837450'
--set @LotNum = '01319904'
--set @Location = 'H2-YMX-C23'
--set @TraceId = '4540790613722'

drop table tmp_wspy_inventory_table

select * into tmp_wspy_inventory_table from 
( 

--入库、上架、移入、转移入、拣入、补入、加工入
Select a.WarehouseID,a.TOCustomerID,a.TOSKU,a.TransactionID,
	   a.TransactionType+'-'+b.codename_c as TRANSACTIONTYPE_NAME,
	   a.DOCNO,a.addwho,isnull(a.toqty_each,0) as TOQTY,
	   CONVERT(varchar(16),a.transactionTime,120) as TRANSACTIONTIME  
From ACT_Transaction_log  a  with(nolock)  
Left Join BAS_codes b on a.TransactionType=b.COde and b.COdeID='TRN_TYP'  
where a.WarehouseID = @WarehouseID
and a.TOCustomerID=@CustomerID and a.TOSKU=@Sku  
--and a.TOLotnum=@LotNum  
--and a.TOLocation=@Location  
--and a.TOID=@TraceId  
and TransactionType in ('IN','PA','MV','TR','PK','RP','KT')  

UNION  

--移出、转移出、捡出、补出、发运
Select  a.WarehouseID,a.TOCustomerID,a.TOSKU,a.TransactionID,
		a.TransactionType+'-'+b.codename_c as TRANSACTIONTYPE_NAME,
		a.DOCNO,a.addwho,isnull(-a.toqty_each,0) as TOQTY,
		CONVERT(varchar(16),a.transactionTime,120) as TRANSACTIONTIME  
From ACT_Transaction_log  a  with(nolock)  
Left Join BAS_codes b on a.TransactionType=b.COde and b.COdeID='TRN_TYP'  
where a.WarehouseID = @WarehouseID
and a.FMCustomerID=@CustomerID and a.FMSKU=@Sku  
--and a.FMLotnum=@LotNum  
--and a.FMLocation=@Location  
--and a.FMID=@TraceId  
and TransactionType in ('MV','TR','PK','RP','SO')  


UNION  

--加工单
Select  a.WarehouseID,a.TOCustomerID,a.TOSKU,a.TransactionID,
		a.TransactionType+'-'+b.codename_c as TRANSACTIONTYPE_NAME,
		a.DOCNO,a.addwho,isnull(a.toqty_each,0) as TOQTY,
		CONVERT(varchar(16),a.transactionTime,120) as TRANSACTIONTIME  
From ACT_Transaction_log  a  with(nolock)  
Left Join BAS_codes b on a.TransactionType=b.COde and b.COdeID='TRN_TYP'  
where a.WarehouseID = @WarehouseID
and a.FMCustomerID=@CustomerID and a.FMSKU=@Sku  
--and a.FMLotnum=@LotNum  
--and a.FMLocation=@Location  
--and a.FMID=@TraceId  
and TransactionType in ('KT')  

UNION  

--调整单
Select  a.WarehouseID,a.TOCustomerID,a.TOSKU,a.TransactionID,
	    a.TransactionType+'-'+b.codename_c as TRANSACTIONTYPE_NAME,
	    a.DOCNO,a.addwho,isnull(a.toqty_each-a.Fmqty_Each,0) as TOQTY,
	    CONVERT(varchar(16),a.transactionTime,120) as TRANSACTIONTIME  
From ACT_Transaction_log  a  with(nolock)  
Left Join BAS_codes b on a.TransactionType=b.COde and b.COdeID='TRN_TYP'  
where a.WarehouseID = @WarehouseID
and a.FMCustomerID=@CustomerID  and a.FMSKU=@Sku  
--and a.FMLotnum=@LotNum  
--and a.FMLocation=@Location  
--and a.FMID=@TraceId  
and TransactionType in ('AD')  
) x  
Order By TransactionID


--库存流水明细
select WarehouseID,ToCustomerID,ToSku,TransactionID,
	   TRANSACTIONTYPE_NAME,DocNo,cast(TOQTY as int) TOQTY,AddWho,
	   cast(TOQTY as int) TOQTY,TRANSACTIONTIME 
from tmp_wspy_inventory_table
where 1 = 1
and TRANSACTIONTYPE_NAME <> 'PA-上架'

--库存明细按类型汇总
select WarehouseID,TRANSACTIONTYPE_NAME,cast(sum(TOQTY) as int) Qty 
from tmp_wspy_inventory_table
where 1 = 1
and TRANSACTIONTYPE_NAME <> 'PA-上架'
group by WarehouseID,TRANSACTIONTYPE_NAME
order by Qty desc

--库存流水明细汇总
select WarehouseID,cast(SUM(TOQTY) as int) as '库存余量' 
from tmp_wspy_inventory_table
where 1 = 1
and TRANSACTIONTYPE_NAME <> 'PA-上架'
group by WarehouseID

SMC查询库存事务脚本。

declare @outprodcode varchar(20) 
declare @warehouse varchar(20)
declare @customer varchar(20)

set @outprodcode = '023923900028'
set @warehouse = 'FLC'
set @customer = 'SUN'

--入库  
;with asn_detail as  (
SELECT s.id 进仓单号,s.corrdoccode 报关单,s.ladingno 提单号,b.name 企业名称,g.outprodcode 商品编号,g.prodname 商品名称,g.applyqty 申报数量,g.actualqty 入库实际数量,fineqty 良品数,brokenqty 破损数,
netwt 总净重,g.createdate 时间,s.createdate,s.warehousecode 仓库 ,s.status 状态 ,CASE 
WHEN s.status=-1 THEN '已删除' 
WHEN s.status =0 THEN '已入库' 
WHEN s.status=1 THEN '待确认' 
WHEN s.status=3 THEN '已通知wms' 
WHEN s.status=4 THEN '仓库待确认' 
END  状态描述  
FROM wh_store s
INNER JOIN wh_storegoods g ON s.id=g.storeid
INNER JOIN cmp_baseinfo b ON b.compcode=s.compcode 
  WHERE    isbill='N'
 AND  s.status=0   --and cpc.srcoutprodcode   in ('B01KXL8XF4','B01KXL8O3U')
 and g.outprodcode  = @outprodcode and s.warehousecode = @warehouse
 --and s.warehousecode   in( 'hjc' )
AND s.compcode= @customer
--AND s.createdate>='2017-01-01 00:00:00' AND s.createdate<'2018-07-28 00:00:00'
) select SUM(申报数量) 申报数量,SUM(入库实际数量) 实际入库数量 from asn_detail
;
--出库总数
SELECT	  sum(od.qty) 出库总数
FROM  ord_order  o  
LEFT JOIN ord_orderitem od ON od.orderid=o.orderid  
LEFT JOIN wh_orderstatus s on s.orderid=o.orderid  and s.status=0 
WHERe o.compcode=@customer and od.outprodcode = @outprodcode
 and outwarehousecode=@warehouse 
and o.status>-1 ;

SELECT	  sum(od.qty) 出库总数
FROM  ord_order  o  
LEFT JOIN ord_orderitem od ON od.orderid=o.orderid  
LEFT JOIN wh_orderstatus s on s.orderid=o.orderid  and s.status=0 
WHERe o.compcode=@customer and od.outprodcode = @outprodcode
 and outwarehousecode=@warehouse 
and o.status>-1 ;



---------- 手工出库单
SELECT w.id, CASE  
WHEN w.changestype=0 THEN '修改SMC'
WHEN w.changestype=1 THEN '修改WMS'
WHEN w.changestype=2 THEN '修改海关' 
END  调整类型 ,CASE  
WHEN w.changestype in (0,1) THEN '出仓单' 
WHEN w.changestype=2 THEN '退运单' 
END  单据类型 , wg.outprodcode 商品编号,wg.prodname 商品名称,sum(wg.stock) 数量,w.warehousecode 仓库,cpc.srcoutprodcode ASIN,
w.createdate,w.notes,w.status ,CASE 
WHEN w.status =-1 THEN '已经删除'  
WHEN w.status =9 THEN 'SMC已出库' 
WHEN w.status=1 THEN 'SMC未审核' 
WHEN w.status=5 THEN '海关出库异常' 
WHEN w.status=6 THEN '已发送WMS' 
END  状态  from  wh_goodsout w
LEFT JOIN wh_goodsoutitems wg on w.id=wg.whgoodsoutid
LEFT JOIN cmp_product_compare cpc on cpc.wtdoutprodcode =wg.outprodcode AND w.compcode=cpc.compcode
where    wg.outprodcode = @outprodcode
and w.compcode=@customer  and w.warehousecode=@warehouse 
GROUP BY w.id,outprodcode, prodname,warehousecode,changestype,cpc.srcoutprodcode,w.createdate,w.notes,w.status
ORDER BY outprodcode;


-----手工入库单
SELECT s.id,s.ladingno,b.name 企业名称,g.outprodcode 商品编号,g.prodname 商品名称,g.applyqty 申报数量,g.actualqty 入库实际数量,fineqty 良品数,brokenqty 破损数,
netwt 总净重,g.createdate 时间,s.warehousecode 仓库,s.notes ,CASE  
WHEN s.operations=0 THEN '修改SMC'
WHEN s.operations=1 THEN '修改WMS' 
END  调整系统 ,s.status 状态 ,CASE
WHEN s.status=-1 THEN '已删除'   
WHEN s.status=0 THEN '已入库' 
WHEN s.status=1 THEN '待确认' 
WHEN s.status=3 THEN '已通知wms' 
WHEN s.status=4 THEN '仓库待确认' 
END  状态 
 FROM wh_store s
INNER JOIN wh_storegoods g ON s.id=g.storeid
INNER JOIN cmp_baseinfo b ON b.compcode=s.compcode 
--LEFT JOIN cmp_product_compare cpc on cpc.wtdoutprodcode =g.outprodcode AND cpc.compcode=s.compcode
 WHERE     isbill!='N' and s.warehousecode = @warehouse
AND  s.status>=0--AND  s.status=0  --
and g.outprodcode = @outprodcode-- and cpc.srcoutprodcode   in ('B019VSCJJ6','B0191CIS9W')
and s.compcode = @customer




--出库明细;  

SELECT	 od.itemid, wd.ladingno 总运单号, c.fullname'企业名称',c.name '企业简称',e.waybillcode '快递单号',o.outorderid '企业订单号' ,o.orderid '海外通订单号'--, o.expresscode,o.outwarehousecode
 ,o.busimode'业务类型'  ,o.outorderdate'下单时间',o.createdate '转正时间',o.inorderdate '录入时间', o.stockupflag 是否备货--,co.entryoptime,co.*
  ,u.name'收货人' , outwarehousecode 仓库  --,,o.outwarehousecode 仓库 
 ,od.outprodcode'商品编码',od.prodname '商品名称'  ,od.price '商品单价',od.qty '商品数量',od.total'商品总价' 
 ,o.total '订单总价',o.tax 总税金,e.freight 总快递费,noncashpartamount 优惠价格--,e.grosswt 商品毛重
 ,o.status 
,CASE  WHEN o.status=-1 THEN '删除' WHEN o.status in (1,2,3,4,5,6,7,8,9,10) THEN ''  END  是否删除状态 
,co.loadoptime 装载时间 ,j.optime 推送wms时间 ,s.updatetime WMS出库时间 
FROM  ord_order  o 
LEFT JOIN  ord_express e ON o.orderid=e.orderid
LEFT JOIN cmp_baseinfo c ON c.compcode=o.compcode 
LEFT JOIN ord_orderitem od ON od.orderid=o.orderid 
LEFT JOIN  user_consumer u ON o.orderid=u.consumerid 
LEFT JOIN cus_order co ON co.orderid=o.orderid
LEFT JOIN wh_deliveritem w ON w.orderid=o.orderid
LEFT JOIN wh_deliver wd on wd.id=w.deliverid
LEFT JOIN wh_orderstatus s on s.orderid=o.orderid  --and s.status=0 
LEFT JOIN job_insr_wms j on j.referencepk=o.orderid  
WHERe  od.outprodcode = @outprodcode-- and s.status=0 
 AND outwarehousecode = @warehouse
 --o.customscode='5141'
 AND o.compcode= @customer
--AND o.busimode='BBC'
and o.status>-1 --and od.qty = 1