仓库计算仓租是按照sku在库时间 - 减免天数 * 体积 * 价格 算出来的。
亚马逊这个客户要求sku完全按照先进先出，仓库计算也需要遵守这个基本规则。
首先构造一些基本表，如下：
--挑出需要计算仓库的sku基础表
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_inventoryList] Script Date: 11/22/2018 09:45:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_inventoryList](
[id] [bigint] NULL,
[WarehouseID] [varchar](50) NULL,
[CustomerID] [varchar](50) NULL,
[sku] [varchar](50) NULL,
[operSatus] [char](6) NULL,
[operTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO--sku当天没有出库记录的，用于左连接的基础表
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_rq_list] Script Date: 11/22/2018 09:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_rq_list](
[id] [int] IDENTITY(1,1) NOT NULL,
[rq] [date] NULL,
[oper] [varchar](10) NULL,
[operTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--入库记录流水表
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_details2] Script Date: 11/22/2018 10:06:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_details2](
[id] [int] NULL,
[WarehouseID] [varchar](100) NULL,
[ToCustomerID] [varchar](100) NULL,
[ToSku] [varchar](100) NULL,
[TRANSACTIONTYPE_NAME] [varchar](100) NULL,
[LotAtt03] [varchar](100) NULL,
[rq] [date] NULL,
[ToQty] [int] NULL,
[usedqty] [int] NULL,
[para1] [varchar](100) NULL,
[para2] [varchar](100) NULL,
[editor] [varchar](100) NULL,
[editortime] [datetime] NULL,
[notest] [varchar](1000) NULL,
[DocNo] [varchar](1000) NULL,
[DocNoCount] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ((0)) FOR [usedqty]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ('') FOR [para1]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ('') FOR [para2]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ('') FOR [editor]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ('') FOR [notest]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ('') FOR [DocNo]
GO
ALTER TABLE [dbo].[t_hz_ymx_details2] ADD DEFAULT ((0)) FOR [DocNoCount]
GO
--出库记录流水表
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_details3] Script Date: 11/22/2018 10:08:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_details3](
[id] [int] NULL,
[WarehouseID] [varchar](100) NULL,
[ToCustomerID] [varchar](100) NULL,
[ToSku] [varchar](100) NULL,
[TRANSACTIONTYPE_NAME] [varchar](100) NULL,
[LotAtt03] [varchar](100) NULL,
[rq] [date] NULL,
[ToQty] [int] NULL,
[usedqty] [int] NULL,
[para1] [varchar](100) NULL,
[para2] [varchar](100) NULL,
[editor] [varchar](100) NULL,
[editortime] [datetime] NULL,
[notest] [varchar](1000) NULL,
[AdjOutQty] [int] NULL,
[TranferOutQty] [int] NULL,
[ModDataQty] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t_hz_ymx_details3] ADD DEFAULT ((0)) FOR [usedqty]
GO
ALTER TABLE [dbo].[t_hz_ymx_details3] ADD DEFAULT ('') FOR [para1]
GO
ALTER TABLE [dbo].[t_hz_ymx_details3] ADD DEFAULT ('') FOR [para2]
GO
ALTER TABLE [dbo].[t_hz_ymx_details3] ADD DEFAULT ('') FOR [editor]
GO
ALTER TABLE [dbo].[t_hz_ymx_details3] ADD DEFAULT ('') FOR [notest]
GO
--计算仓库基础数据表（主要是sku进出库的流水记录）
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_header] Script Date: 11/22/2018 10:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_header](
[WarehouseID] [varchar](50) NULL,
[CustomerID] [varchar](50) NULL,
[InventoryDate] [date] NULL,
[Sku] [varchar](50) NULL,
[LotNum] [varchar](50) NULL,
[Qty1] [int] NULL,
[inQty] [int] NULL,
[outQty] [int] NULL,
[Qty2] [int] NULL,
[LotAtt02] [varchar](50) NULL,
[LotAtt03] [varchar](50) NULL,
[InventoryDays] [int] NULL,
[CalcChargeDays] [int] NULL,
[ChargeFlag] [int] NULL,
[operater] [varchar](50) NULL,
[operateTime] [datetime] NULL,
[Notes] [varchar](1000) NULL,
[DocNO] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--仓租辅助日期
USE [wmsa]
GO
/****** Object: Table [dbo].[t_hz_ymx_rq_list] Script Date: 03/26/2019 10:11:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_hz_ymx_rq_list](
[id] [int] IDENTITY(1,1) NOT NULL,
[rq] [date] NULL,
[oper] [varchar](10) NULL,
[operTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
构造基础表后，由2个存储过程来完成需要的逻辑，如下：
--往入库表，出库表写记录，构造出入库流水
USE [wmsa]
GO
/****** Object: StoredProcedure [dbo].[SPUDF_YMX_InventList_71] Script Date: 11/22/2018 10:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SPUDF_YMX_InventList_71]
@WarehouseID varchar(20),
@CustomerID varchar(20),
@Sku varchar(20),
@r varchar(1000) output
as
begin try
/*
drop table t_hz_ymx_details2
drop table t_hz_ymx_details3
create table t_hz_ymx_details2
(
id int,
WarehouseID varchar(100),
ToCustomerID varchar(100),
ToSku varchar(100),
TRANSACTIONTYPE_NAME varchar(100),
LotAtt03 varchar(100),
rq date,
ToQty int,
usedqty int default 0,
para1 varchar(100) default '',
para2 varchar(100) default '',
editor varchar(100) default '',
editortime datetime,
notest varchar(1000) default '',
DocNo varchar(1000) default '',
DocNoCount int default 0
)
create table t_hz_ymx_details3
(
id int,
WarehouseID varchar(100),
ToCustomerID varchar(100),
ToSku varchar(100),
TRANSACTIONTYPE_NAME varchar(100),
LotAtt03 varchar(100),
rq date,
ToQty int,
usedqty int default 0,
para1 varchar(100) default '',
para2 varchar(100) default '',
editor varchar(100) default '',
editortime datetime,
notest varchar(1000) default ''
)
*/
-- select * from t_hz_ymx_details3_cache
--入库
/*
insert into t_hz_ymx_details2(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,LotAtt03,rq,ToQty,usedqty,DocNo,DocNoCount,notest,editortime)
Select ROW_NUMBER() over(order by CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23)) as id,a.WarehouseID,a.TOCustomerID,a.TOSKU, a.TransactionType as TRANSACTIONTYPE_NAME,i.LotAtt03
,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23) Rq, SUM(isnull(a.toqty_each,0)) as TOQTY ,SUM(isnull(a.toqty_each,0)) as usedQty,max(a.DocNo),COUNT(distinct a.DocNo)
,case a.TransactionType when 'IN' then '正常入库' else '转移入库' end as 'inBound',GETDATE()
From ACT_Transaction_log a
left join INV_LOT_ATT i on a.ToLotnum = i.LotNum
where a.WarehouseID = @WarehouseID
and a.TOCustomerID=@CustomerID
and a.TOSKU=@Sku
and a.TransactionType in ('IN','TR')
and a.ToQty > 0
and CONVERT(varchar(100),a.TransactionTime,23)< CONVERT(varchar(100),GETDATE(),23)
and not exists(select 1 from t_hz_ymx_details2 b where a.WarehouseID = b.WarehouseID and a.ToCustomerID = b.ToCustomerID and a.ToSku = b.ToSku and a.DocNo = b.DocNo )
group by WarehouseID,ToCustomerID,ToSku,TransactionType,LotAtt03,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23)
*/
declare @a_WarehouseID varchar(10),@a_TOCustomerID varchar(10),@a_TOSKU varchar(20),@a_TransactionType varchar(5),@a_LotAtt03 varchar(20),@a_Rq varchar(20)
,@a_ToQty int,@a_usedQty int,@a_DocNo varchar(20),@a_DocCount int,@a_inBound varchar(10),@id int = 0,@max_InboundDate date
,@min_inboundDate date,@tiqianDays int = 0
--取出当前产品最大入库日期，用于抓取此入库记录之后的入库记录
select @max_InboundDate = isnull(MAX(rq),'2015-01-01') from t_hz_ymx_details2
where WarehouseID = @WarehouseID
and ToCustomerID = @CustomerID
and ToSku = @Sku
declare cur_list1 cursor
for
--正常入库
Select a.WarehouseID,a.TOCustomerID,a.TOSKU, a.TransactionType as TRANSACTIONTYPE_NAME,i.LotAtt03,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23) Rq
,SUM(isnull(a.toqty_each,0)) as TOQTY ,SUM(isnull(a.toqty_each,0)) as usedQty,max(a.DocNo) as DocNo,0 as DocCount,'正常入库' as inBound
From ACT_Transaction_log a
left join INV_LOT_ATT i on a.ToLotnum = i.LotNum
where 1 = 1
and a.Status = 99
and a.WarehouseID = @WarehouseID
and a.TOCustomerID = @CustomerID
and a.TOSKU = @Sku
and a.TransactionType in ('IN')
and a.ToQty > 0
and CONVERT(varchar(100),a.TransactionTime,23) > @max_InboundDate
and CONVERT(varchar(100),a.TransactionTime,23) < CONVERT(varchar(100),GETDATE()- @tiqianDays,23)
group by WarehouseID,ToCustomerID,ToSku,TransactionType,LotAtt03,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23)
order by CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23) asc
open cur_list1
fetch next from cur_list1 into @a_WarehouseID,@a_TOCustomerID,@a_TOSKU,@a_TransactionType,@a_LotAtt03,@a_Rq,@a_ToQty,@a_usedQty,@a_DocNo,@a_DocCount,@a_inBound
--取出入库记录当前产品最大ID,作业后续递加的基数
select @id = isnull(MAX(id),0) from t_hz_ymx_details2
where WarehouseID = @a_WarehouseID and ToCustomerID = @a_TOCustomerID and ToSku =@a_TOSKU
while(@@FETCH_STATUS = 0)
begin
set @id = @id + 1
insert into t_hz_ymx_details2(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,LotAtt03,rq,ToQty,usedqty,DocNo,DocNoCount,notest,editortime)
values(@id,@a_WarehouseID,@a_TOCustomerID,@a_TOSKU,@a_TransactionType,@a_LotAtt03,@a_Rq,@a_ToQty,@a_usedQty,@a_DocNo,@a_DocCount,@a_inBound,GETDATE())
fetch next from cur_list1 into @a_WarehouseID,@a_TOCustomerID,@a_TOSKU,@a_TransactionType,@a_LotAtt03,@a_Rq,@a_ToQty,@a_usedQty,@a_DocNo,@a_DocCount,@a_inBound
end
close cur_list1
deallocate cur_list1
--发运
if not exists(select 1 from t_hz_ymx_header
where WarehouseID = @WarehouseID
and CustomerID = @CustomerID
and Sku = @Sku )
begin
select @min_inboundDate = DATEADD(D,-1,min(rq))
from t_hz_ymx_details2
where WarehouseID = @WarehouseID
and ToCustomerID = @CustomerID
and ToSku = @Sku
and usedqty > 0
end
else
begin
select @min_inboundDate = MAX(InventoryDate) from t_hz_ymx_header
where WarehouseID = @WarehouseID
and CustomerID = @CustomerID
and Sku = @Sku
end
insert into t_hz_ymx_details3(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,LotAtt03,rq,ToQty,notest,editortime,AdjOutQty,TranferOutQty,ModDataQty)
select ROW_NUMBER() over(order by t.rq asc) as id,@WarehouseID,@CustomerID,@Sku,'SO','',t.rq,isnull(k.TOQTY,0),'',GETDATE()
,dbo.fn_GetAdjOutQty(@WarehouseID,@CustomerID,@Sku,t.rq)
,dbo.fn_GetTranferOutQty(@WarehouseID,@CustomerID,@Sku,t.rq)
,dbo.fn_GetModDataQty(@WarehouseID,@CustomerID,@Sku,t.rq)
from t_hz_ymx_rq_list t
left join (
Select a.WarehouseID,a.TOCustomerID
,a.TOSKU, a.TransactionType as TRANSACTIONTYPE_NAME,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23) as rq,cast(sum(isnull(-a.toqty_each,0)) as int) as TOQTY
from ACT_Transaction_log a with(nolock)
where 1 = 1
and a.Status = 99
and a.TransactionType in ('SO')
and a.WarehouseID = @WarehouseID
and a.FMCustomerID = @CustomerID
and a.FMSKU = @Sku
and -a.ToQty < 0
and CONVERT(varchar(100),a.TransactionTime,23)< CONVERT(varchar(100),GETDATE(),23)
group by WarehouseID,ToCustomerID,ToSku,TransactionType,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23)
) k on t.rq = k.rq
where 1 = 1
and t.rq > @min_inboundDate
and t.rq < CONVERT(varchar(100),GETDATE()- @tiqianDays,23)
order by t.rq
/*
insert into t_hz_ymx_details3_cache(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,rq,ToQty,notest,editortime,AdjOutQty,TranferOutQty,ModDataQty)
Select id,t.WarehouseID,t.TOCustomerID,t.TOSKU, t.TRANSACTIONTYPE_NAME,t.rq,t.TOQTY,t.outBound,editortime
,dbo.fn_GetAdjOutQty(t.WarehouseID,t.ToCustomerID,t.ToSku,t.rq)
,dbo.fn_GetTranferOutQty(t.WarehouseID,t.ToCustomerID,t.ToSku,t.rq)
,dbo.fn_GetModDataQty(t.WarehouseID,t.ToCustomerID,t.ToSku,t.rq)
from
(
Select 1 as id,a.WarehouseID,a.TOCustomerID
,a.TOSKU, a.TransactionType as TRANSACTIONTYPE_NAME,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23) as rq,cast(sum(isnull(-a.toqty_each,0)) as int) as TOQTY
,'正常出库' as outBound,GETDATE() as editortime
From ACT_Transaction_log a with(nolock)
where a.WarehouseID = @WarehouseID
and a.FMCustomerID=@CustomerID
and a.FMSKU=@Sku
and TransactionType in ('SO')
and -a.ToQty < 0
--and a.TransactionTime between DATEADD(d,-3,getdate()) and GETDATE()
and CONVERT(varchar(100),a.TransactionTime,23)< CONVERT(varchar(100),GETDATE()- @tiqianDays,23)
--and not exists(select 1 from t_hz_ymx_details3 b where a.WarehouseID = b.WarehouseID and a.ToCustomerID = b.ToCustomerID and a.ToSku = b.ToSku and CONVERT(varchar(20),cast(a.TRANSACTIONTIME as date),23) = b.rq )
group by WarehouseID,ToCustomerID,ToSku,TransactionType,CONVERT(varchar(20),cast(TRANSACTIONTIME as date),23)
) t
if exists(select 1 from t_hz_ymx_details3
where WarehouseID = @WarehouseID and ToCustomerID = @CustomerID and ToSku = @Sku )
begin
--获取出库正式表最大日期
select @min_invenotryDate = max(rq)
from t_hz_ymx_details3
where WarehouseID = @WarehouseID and ToCustomerID = @CustomerID and ToSku = @Sku --and TRANSACTIONTYPE_NAME = 'SO'
end
else
begin
--获取出库临时表最小日期
select @min_invenotryDate = min(rq)
from t_hz_ymx_details3_cache
where WarehouseID = @WarehouseID and ToCustomerID = @CustomerID and ToSku = @Sku --and TRANSACTIONTYPE_NAME = 'SO'
end
--获取入库表可用库存的最小入库日期
select top 1 @min_inboundDate = rq from t_hz_ymx_details2
where WarehouseID = @WarehouseID
and ToCustomerID = @CustomerID
and ToSku = @Sku
and usedqty > 0
order by rq asc
--if(@min_invenotryDate > @min_inboundDate)
if(isnull(@min_invenotryDate,'')='' or @min_invenotryDate > @min_inboundDate)
set @min_invenotryDate = @min_inboundDate
--select top 1 @out_maxid = id from t_hz_ymx_details3_cache order by id desc
while(DATEDIFF(D,@min_invenotryDate,CONVERT(varchar(20),getdate()- @tiqianDays,23)) >= 1)
begin
if not exists(select 1 from t_hz_ymx_details3_cache
where WarehouseID = @WarehouseID and ToCustomerID = @CustomerID and ToSku = @Sku and rq = @min_invenotryDate
and TRANSACTIONTYPE_NAME = 'SO' )
begin
--set @out_maxid = @out_maxid + 1
insert into t_hz_ymx_details3_cache(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,rq,ToQty,notest,editortime,AdjOutQty,TranferOutQty,ModDataQty)
values(1,@WarehouseID,@CustomerID,@Sku,'SO',@min_invenotryDate,0,'当天未变动',GETDATE()
,dbo.fn_GetAdjOutQty(@WarehouseID,@CustomerID,@Sku,@min_invenotryDate)
,dbo.fn_GetTranferOutQty(@WarehouseID,@CustomerID,@Sku,@min_invenotryDate)
,dbo.fn_GetModDataQty(@WarehouseID,@CustomerID,@Sku,@min_invenotryDate) )
end
set @min_invenotryDate = DATEADD(D,1,@min_invenotryDate)
end
insert into t_hz_ymx_details3(id,WarehouseID,ToCustomerID,ToSku,TRANSACTIONTYPE_NAME,LotAtt03,rq,ToQty,notest,editortime,AdjOutQty,TranferOutQty,ModDataQty)
select ROW_NUMBER() over(order by rq asc) as id,a.WarehouseID,a.TOCustomerID,a.TOSKU, a.TRANSACTIONTYPE_NAME,'',a.rq,a.ToQty,notest,GETDATE(),AdjOutQty,TranferOutQty,ModDataQty
from t_hz_ymx_details3_cache a
where TRANSACTIONTYPE_NAME = 'SO'
and WarehouseID = @WarehouseID
and ToCustomerID = @CustomerID
and ToSku = @Sku
and not exists(select 1 from t_hz_ymx_details3 b
where a.WarehouseID = b.WarehouseID and a.ToCustomerID = b.ToCustomerID and a.ToSku = b.ToSku and a.rq = b.rq )
delete from t_hz_ymx_details3_cache
*/
end try
begin catch
set @r =ERROR_MESSAGE()
+'('+ERROR_PROCEDURE()+')'
+',ERROR_NUMBER='+cast(ERROR_NUMBER()as varchar(10))
+',行号='+cast(ERROR_LINE() as varchar(10))
+',ERROR_STATE='+cast(ERROR_STATE() as varchar(10))
+',ERROR_SEVERITY='+cast(ERROR_SEVERITY() as varchar(10))
end catch
--往header表（仓库计算基础表）里面构造出入库流水记录，当天没有出库的，补一条0
USE [wmsa]
GO
/****** Object: StoredProcedure [dbo].[SPUDF_YMX_t_hz_ymx_details_71] Script Date: 11/22/2018 10:18:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SPUDF_YMX_t_hz_ymx_details_71]
@in_WarehouseID varchar(20),
@in_CustomerID varchar(20),
@in_Sku varchar(20),
@return_code varchar(1000) output
as
begin try
declare @out_id int = 0,@warehouseid varchar(50),@customerid varchar(50),@sku varchar(50),@TRANSACTIONTYPE_NAME varchar(10),@ToQty int,@LotAtt03 varchar(50),@InventoryDate date,@availQty int = 0
,@nowQty int = 0,@in_id int = 1,@rq date,@rows int = 0,@usedqty int = 0,@DocNO varchar(100),@AdjOutQty int,@TranferOutQty int,@ModDataQty int,@Last_InventoryDate date,@id int = 0
--补入库初始记录
insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNO)
select WarehouseID,ToCustomerID,rq,ToSku,'',ToQty,0,0,ToQty,'',LotAtt03,0,0,0,'',GETDATE(),notest,DocNO
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and ToQty = usedqty --入库数等于可用余数，初始入库数量
and not exists(select 1 from t_hz_ymx_header t where a.WarehouseID = t.WarehouseID and a.ToCustomerID = t.CustomerID and a.ToSku = t.Sku and a.LotAtt03 = t.LotAtt03)
order by id
declare Cur_OrderList cursor
for
--批量按当日出库量汇总出库量
select a.id,a.warehouseid,a.tocustomerid,a.tosku,a.TRANSACTIONTYPE_NAME,a.Rq,a.toQty,Rq,AdjOutQty,TranferOutQty,ModDataQty
from t_hz_ymx_details3 a
where 1 = 1
and TRANSACTIONTYPE_NAME = 'SO'
and isnull(LotAtt03,'') = ''
and isnull(para2,'') <> '1'
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and a.tosku = @in_Sku
order by id
select top 1 @in_id = id from t_hz_ymx_details2
where WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and usedqty > 0
order by rq asc
--取入库单初始余量
select top 1 @availQty = usedqty,@LotAtt03 = LotAtt03,@in_id = id,@DocNO = DocNo
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
and usedqty > 0
open Cur_OrderList
Fetch Next From Cur_OrderList Into @out_id,@warehouseid,@customerid,@sku,@TRANSACTIONTYPE_NAME,@InventoryDate,@ToQty,@rq,@AdjOutQty,@TranferOutQty,@modDataQty
While @@FETCH_STATUS=0
Begin
--调整出入库逻辑
if(isnull(@AdjOutQty,0) <> 0)
begin
/*
insert into t_wms_ymx_mid(warehouseid,customerid,sku,rq,Qty,operTime,oper)
select warehouseid,FMCustomerID,FMSKU,i.LotAtt03,sum(ToQty-FMQty),GETDATE(),'system'
from ACT_Transaction_Log l
left join INV_LOT_ATT i on l.FMLotNum = i.LotNum
where TransactionType = 'AD'
and WarehouseID = @warehouseid
and FMCustomerID = @customerid
and FMSKU = @sku
--and DocNo = 'AD16120014'
and CONVERT(varchar(100),TransactionTime,23) = @InventoryDate
group by warehouseid,FMCustomerID,FMSKU,i.LotAtt03
*/
update a
set usedqty = usedqty + b.qty
from t_hz_ymx_details2 a
inner join (
select h.WarehouseID,h.CustomerID,d.SKU,i.LotAtt03,cast(sum(ToQty-Qty) as int) as qty from DOC_ADJ_Details d
left join DOC_ADJ_Header h on d.ADJNo = h.ADJNo
left join INV_LOT_ATT i on d.LotNum = i.LotNum
where 1 =1
--and h.ADJNo = 'AD16060007'
and h.WarehouseID = @WarehouseID
and h.CustomerID = @CustomerID
and d.SKU = @Sku
and CONVERT(varchar(100),h.ADJTime,23) = @Rq
group by h.WarehouseID,h.CustomerID,d.SKU,i.LotAtt03
) b on a.WarehouseID = b.WarehouseID and a.ToCustomerID = b.CustomerID
and a.ToSku = b.SKU and a.rq = b.LotAtt03
select top 1 @availQty = usedqty
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
and usedqty > 0
end
--转移出入库逻辑
if(isnull(@TranferOutQty,0) <> 0 )
begin
update a
set usedqty = usedqty + b.qty
from t_hz_ymx_details2 a
inner join (
select h.WarehouseID,h.CustomerID,d.FMSKU,i.LotAtt03,cast(-sum(d.toqty) as int) as qty
from DOC_Transfer_Details d
left join DOC_Transfer_Header h on d.TDOCNo = h.TDOCNo
inner join INV_LOT_ATT i on d.FMLotNum = i.LotNum
where 1 = 1
--and h.TDOCNo = 'TF151216000030'
and h.WarehouseID = @WarehouseID
and h.CustomerID = @CustomerID
and d.FMCustomerID <> d.TOCustomerID
and d.FMSKU = @Sku
and d.FMSKU = d.TOSKU
and CONVERT(varchar(100),h.TransferTime,23) = @Rq
group by h.WarehouseID,h.CustomerID,d.FMSKU,i.LotAtt03
) b on a.WarehouseID = b.WarehouseID
and a.ToCustomerID = b.customerid and a.ToSku = b.FMSKU and a.rq = b.LotAtt03
select top 1 @availQty = usedqty
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
and usedqty > 0
end
/*
--入库日期调整逻辑
if(isnull(@ModDataQty,0) <> 0)
begin
select 1
end
*/
if(@ToQty = 0)
goto labl02
labl01:
if(@availQty = 0)
begin
set @in_id = @in_id + 1
select top 1 @availQty = usedqty,@LotAtt03 = LotAtt03,@in_id = id,@DocNO = DocNo
from t_hz_ymx_details2 a
where 1 = 1
--and TRANSACTIONTYPE_NAME = 'IN'
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
end
if(@availQty >= ABS(@ToQty))
begin
set @availQty = @availQty - ABS(@ToQty)
--insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNo)
--select WarehouseID,ToCustomerID,rq,ToSku,'',@ToQty,0,0,@availQty,'',@LotAtt03,0,0,'','',GETDATE(),'完全满足',@DocNO
--from t_hz_ymx_details3
--where TRANSACTIONTYPE_NAME = 'SO'
--and WarehouseID = @in_WarehouseID
--and ToCustomerID = @in_CustomerID
--and ToSku = @in_Sku
--and id= @out_id
insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNo)
values(@warehouseid,@customerid,@rq,@sku,'',@ToQty,0,0,@availQty,'',@LotAtt03,0,0,'','',GETDATE(),'完全满足',@DocNO)
update t_hz_ymx_details2
set usedqty = @availQty
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
end
else
begin
--insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNO)
--select WarehouseID,ToCustomerID,rq,ToSku,'',-@availQty,0,0,0,'',@LotAtt03,0,0,'','',GETDATE(),'部分满足',@DocNO
--from t_hz_ymx_details3
--where TRANSACTIONTYPE_NAME = 'SO'
--and WarehouseID = @in_WarehouseID
--and ToCustomerID = @in_CustomerID
--and ToSku = @in_Sku
--and id= @out_id
insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNo)
values(@warehouseid,@customerid,@rq,@sku,'',-@availQty,0,0,0,'',@LotAtt03,0,0,'','',GETDATE(),'部分满足',@DocNO)
update t_hz_ymx_details2
set usedqty = 0
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
set @ToQty = @ToQty + @availQty
set @in_id = @in_id + 1
--出库数大于入库数时结束当前产品计算，进入下一个产品计算逻辑
if not exists(select 1
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id)
begin
update t_hz_ymx_details3
set para2 = 1
where WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and rq = @InventoryDate
and para2 <> 1
Fetch Next From Cur_OrderList Into @out_id,@warehouseid,@customerid,@sku,@TRANSACTIONTYPE_NAME,@InventoryDate,@ToQty,@rq,@AdjOutQty,@TranferOutQty,@modDataQty
continue
end
else
select top 1 @availQty = usedqty,@LotAtt03 = LotAtt03,@in_id = id,@DocNO = DocNo
from t_hz_ymx_details2 a
where 1 = 1
--and TRANSACTIONTYPE_NAME = 'IN'
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and id = @in_id
goto labl01
end
labl02:
--补系统记录
insert into t_hz_ymx_header(WarehouseID, CustomerID, InventoryDate, Sku, LotNum, Qty1, inQty, outQty, Qty2, LotAtt02, LotAtt03, InventoryDays, CalcChargeDays, ChargeFlag, operater, operateTime, Notes,DocNo)
select WarehouseID,ToCustomerID,@InventoryDate,ToSku,'',0,0,0,usedqty,'',LotAtt03,0,0,'','',GETDATE(),'补录01',DocNO
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and usedqty > 0
and rq < @InventoryDate
and id > @in_id
--and not exists
--(
-- select 1 from t_hz_ymx_header b
-- where Sku = @sku and WarehouseID = @warehouseid and CustomerID = @customerid
-- and InventoryDate = @InventoryDate and a.LotAtt03 = b.LotAtt03
--)
union
select WarehouseID,ToCustomerID,@InventoryDate,ToSku,'',0,0,0,usedqty,'',LotAtt03,0,0,'','',GETDATE(),'补录02',DocNO
from t_hz_ymx_details2 a
where 1 = 1
and WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and usedqty > 0
and rq < @InventoryDate
and id = @in_id and @ToQty = 0
update t_hz_ymx_details3
set para2 = 1
where WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and rq = @InventoryDate
and para2 <> 1
Fetch Next From Cur_OrderList Into @out_id,@warehouseid,@customerid,@sku,@TRANSACTIONTYPE_NAME,@InventoryDate,@ToQty,@rq,@AdjOutQty,@TranferOutQty,@modDataQty
end
Close Cur_OrderList
DeAllocate Cur_OrderList
select @Last_InventoryDate = MAX(InventoryDate)
from t_hz_ymx_header
where WarehouseID = @in_WarehouseID
and CustomerID = @in_CustomerID
and Sku = @in_Sku
--select * from t_hz_ymx_details2_log
select @id = isnull(MAX(id),0)+1 from t_hz_ymx_details2_log
insert into t_hz_ymx_details2_log
select @id, WarehouseID, ToCustomerID, ToSku, TRANSACTIONTYPE_NAME, LotAtt03, rq, ToQty, usedqty, @Last_InventoryDate as para1, para2, editor, GETDATE(), '', '', 0
from t_hz_ymx_details2
where WarehouseID = @in_WarehouseID
and ToCustomerID = @in_CustomerID
and ToSku = @in_Sku
and usedqty > 0
end try
begin catch
Close Cur_OrderList
DeAllocate Cur_OrderList
set @return_code=ERROR_MESSAGE()
+'('+ERROR_PROCEDURE()+')'
+',ERROR_NUMBER='+cast(ERROR_NUMBER()as varchar(10))
+',行号='+cast(ERROR_LINE() as varchar(10))
+',ERROR_STATE='+cast(ERROR_STATE() as varchar(10))
+',ERROR_SEVERITY='+cast(ERROR_SEVERITY() as varchar(10))
end catch
--通过定时器，每天凌晨自动跑这个逻辑挑出需要计算的sku计算到截止前一天的仓租
USE [wmsa]
GO
/****** Object: StoredProcedure [dbo].[SPUDF_YMX_CangZu] Script Date: 11/22/2018 10:23:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SPUDF_YMX_CangZu]
as
begin
drop table t_hz_ymx_inventoryList
--剔除没有用的sku，只算有库存和当月有出入库的sku
select ROW_NUMBER() over(order by WarehouseID,CustomerID,SKU) as id,WarehouseID,CustomerID,
sku,CAST('' as CHAR(6)) as operSatus,CAST('' as datetime) as operTime
into t_hz_ymx_inventoryList
from
(
select distinct WarehouseID,CustomerID,Sku from t_hz_ymx_header
where 1 = 1
and WarehouseID in('HKBT','FLC')
and CustomerID = 'YMX'
and InventoryDate between DATEADD(d,-2,CONVERT(varchar(20),GETDATE(),23)) and CONVERT(varchar(20),GETDATE(),23)
and Qty2 > 0
union
select distinct WarehouseID,FMCustomerID as CustomerID,FMSKU as sku
from ACT_Transaction_Log
where 1 = 1
and EditTime between DATEADD(d,-2,CONVERT(varchar(20),GETDATE(),23)) and CONVERT(varchar(20),GETDATE(),23)
and FMCustomerID = 'YMX'
and TransactionType = 'SO' and Status = '99'
and WarehouseID in('FLC','HKBT')
and FMSKU <> '*'
group by WarehouseID,FMCustomerID,FMSKU
union
select distinct WarehouseID,FMCustomerID as CustomerID,FMSKU as sku
from ACT_Transaction_Log
where 1 = 1
and EditTime between DATEADD(d,-2,CONVERT(varchar(20),GETDATE(),23)) and CONVERT(varchar(20),GETDATE(),23)
and FMCustomerID = 'YMX'
and TransactionType = 'IN' and Status = '99'
and WarehouseID in('FLC','HKBT')
and FMSKU <> '*'
group by WarehouseID,FMCustomerID,FMSKU
) t
--下面脚本用于仓库仓租计算（富力仓和香港仓都可以用）
declare @id int, @WarehouseID varchar(100),@CustomerID varchar(100),@SKU varchar(100)
declare Cur_inventoryList cursor
for
select id, WarehouseID,CustomerID,SKU from t_hz_ymx_inventoryList
where isnull(operSatus,0) = 0 --1:为已处理
open Cur_inventoryList
Fetch Next From Cur_inventoryList Into @id,@warehouseid,@customerid,@sku
While @@FETCH_STATUS=0
Begin
--select @id,@warehouseid,@customerid,@sku
--构造进出库存记录
EXEC SPUDF_YMX_InventList_71 @warehouseid,@customerid,@sku,''
----写入租仓库存流水
EXEC SPUDF_YMX_t_hz_ymx_details_71 @warehouseid,@customerid,@sku,''
update t_hz_ymx_inventoryList set opersatus = 1,opertime = GETDATE() where id = @id
Fetch Next From Cur_inventoryList Into @id,@warehouseid,@customerid,@sku
end
Close Cur_inventoryList
DeAllocate Cur_inventoryList
end
跑完逻辑通通过一个视图可以计算出仓库数据，如下：
--富力仓仓租
select row_number() over(order by t.InventoryDate,t.Sku,t.LotAtt03) 行号,t.WarehouseID 仓库编号,
t.CustomerID 客户编号,t.InventoryDate 库存日期,t.category ASIN码,t.Sku 商品条码,t.prodname 商品名称,
t.Qty1 当日库存变动数,t.Qty2 当日库存结余数,t.InventoryDays 累计在库天数,
t.CalcChargeDays_weight '计费天数(重量)',t.grosswt 产品毛重,
case when t.CalcChargeDays_weight > 0 then 1 else t.CalcChargeDays_weight end *t.grosswt*0.1*t.Qty2 '当日仓租(重量计费)',
t.CalcChargeDays_tiji '计费天数(体积)',t.pic 产品体积,
case when (t.CalcChargeDays_tiji between 1 and 150) then 1 *t.pic*3*t.Qty2
when t.CalcChargeDays_tiji>150 then 1 *t.pic*5*t.Qty2
else 0 end '当日仓租(体积计费)',
t.LotAtt03 入库日期,t.ASNReference1 SMC入库单号,t.DocNO WMS入库单号
from (
select a.WarehouseID,a.CustomerID,InventoryDate,category,SKU,Qty1,Qty2,LotAtt03,datediff(D,CAST(LotAtt03 as date),InventoryDate) as InventoryDays
,case when datediff(D,CAST(LotAtt03 as date),InventoryDate) > 45 then datediff(D,CAST(LotAtt03 as date),InventoryDate) -45 else 0 end CalcChargeDays_weight
,p.grosswt
,case when datediff(D,CAST(LotAtt03 as date),InventoryDate) > 30 then datediff(D,CAST(LotAtt03 as date),InventoryDate) -30 else 0 end CalcChargeDays_tiji
,p.pic
,DocNO,h.ASNReference1,p.prodname
from t_hz_ymx_header a
left join DOC_ASN_Header h on a.docNo = h.asnno and a.WarehouseID = h.WarehouseID and a.CustomerID = h.CustomerID
left join cmp_product_180124 p on a.CustomerID = p.compcode and a.Sku = p.outprodcode
where 1 = 1
and a.WarehouseID ='FLC'
and InventoryDate >= '2018-10-01' and InventoryDate <= '2018-10-31'
) t
order by t.InventoryDate,t.Sku,t.LotAtt03
--邦泰仓仓租
select row_number() over(order by t.InventoryDate,t.Sku,t.LotAtt03) 行号,t.WarehouseID 仓库编号,
t.CustomerID 客户编号,t.InventoryDate 库存日期,t.category ASIN码,t.Sku 商品条码,t.prodname 商品名称,
t.Qty1 当日库存变动数,t.Qty2 当日库存结余数,t.InventoryDays 累计在库天数,
t.CalcChargeDays_weight '计费天数(重量)',t.grosswt 产品毛重,
case when t.CalcChargeDays_weight > 0 then 1 else t.CalcChargeDays_weight end *t.grosswt*0.3*t.Qty2 '当日仓租(重量计费)',
t.CalcChargeDays_tiji '计费天数(体积)',t.pic 产品体积,
case when (t.CalcChargeDays_tiji between 1 and 160) then 1 *t.pic*10*t.Qty2
when t.CalcChargeDays_tiji>160 then 1 *t.pic*12*t.Qty2
else 0 end '当日仓租(体积计费)',
t.LotAtt03 入库日期,t.ASNReference1 SMC入库单号,t.DocNO WMS入库单号
from (
select a.WarehouseID,a.CustomerID,InventoryDate,category,SKU,Qty1,Qty2,LotAtt03,datediff(D,CAST(LotAtt03 as date),InventoryDate) as InventoryDays
,case when datediff(D,CAST(LotAtt03 as date),InventoryDate) > 45 then datediff(D,CAST(LotAtt03 as date),InventoryDate) -45 else 0 end CalcChargeDays_weight
,p.grosswt
,case when datediff(D,CAST(LotAtt03 as date),InventoryDate) > 20 then datediff(D,CAST(LotAtt03 as date),InventoryDate) -20 else 0 end CalcChargeDays_tiji
,p.pic
,DocNO,h.ASNReference1,p.prodname
from t_hz_ymx_header a
left join DOC_ASN_Header h on a.docNo = h.asnno and a.WarehouseID = h.WarehouseID and a.CustomerID = h.CustomerID
left join cmp_product_180124 p on a.CustomerID = p.compcode and a.Sku = p.outprodcode
where 1 = 1
and a.WarehouseID='HKBT'
and InventoryDate >= '2018-10-01' and InventoryDate <= '2018-10-31'
) t
order by t.InventoryDate,t.Sku,t.LotAtt03
--构造仓租辅助日期脚本
declare @day int
declare @rq varchar(100)
begin
set @day=0
while( @day<1000)
begin
select @rq= CONVERT(varchar(100),DATEADD(DAY,@day,GETDATE()-31),23)
--select @rq
insert into t_hz_ymx_rq_list(rq,oper,operTime)
select @rq,'system',GETDATE()
set @day=@day+1
end
end
--select * from t_hz_ymx_rq_list
扫一扫
2527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
