USE [P2WMS_WH43]
GO
/****** Object: StoredProcedure [dbo].[sp_fru_CalcAllocatePickData] Script Date: 05/04/2018 11:41:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Evan
-- Create date: 2018年4月17日 14点53分
-- Description: 自动分配待拣数据,寄宿在SQL server的job任务[FRU自动按照库位分配拣货任务],每5分钟跑一次
-- Logic: 1：汇总待分配的DN（DN,PartNO,Location,BOL）,同一个DN可能会对应多个不同库位。#temp_DNList
-- 2: 汇总需要分配的库位（distinct）。#temp_AllocationLocation
-- 3：遍历#temp_AllocationLocation.LocationCode,匹配此LocationCode对应表FRU_PickLocationNew.Account手上DN量最少的账户，
-- 将此次循环下的LocationCode的DN（包括这个DN下对应的另外的库位）全部给这个人，最后将已分配的数据从#temp_DNList删除。
-- =============================================
CreatePROCEDURE [dbo].[sp_fru_CalcAllocatePickData]
AS
BEGIN
----------------------------------------找出未拣货的DN
IF EXISTS ( SELECT *
FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#temp_DNList') )
BEGIN
DROP TABLE #temp_box
END
CREATE TABLE #temp_DNList--汇总未分配账户未拣货的DN
(
DN NVARCHAR(MAX) ,
PartNO NVARCHAR(250) ,
LocationCode NVARCHAR(250) ,
BOL NVARCHAR(250)
)
INSERT INTO #temp_DNList
( DN ,
PartNO ,
LocationCode ,
BOL
)
SELECT wo.UserDefineAttribute01 ,
wop.PartNO ,
wopp.LocationCode ,
--SUM(CAST( wopp.PickEAQty AS DECIMAL(18,0))) PickEAQty,
wo.UserDefineAttribute02
FROM dbo.WMS_Outbound wo
INNER JOIN dbo.WMS_OutboundPart wop ON wop.OutboundID = wo.OutboundID
INNER JOIN dbo.WMS_OutboundPick wopp ON wopp.OutboundPartID = wop.OutboundPartID
LEFT JOIN dbo.WMS_FRU_DNAllocationToAccount dta ON dta.DN = wo.UserDefineAttribute01
WHERE ISNULL(wopp.TargetLocationCode, '') = ''
AND ISNULL(wo.UserDefineAttribute02, '') <> ''
AND ISNULL(wop.AllocationEAQty, 0) > 0
AND ISNULL(dta.DN, '') = ''
GROUP BY wo.UserDefineAttribute01 ,
wop.PartNO ,
wopp.LocationCode ,
wo.UserDefineAttribute02
ORDER BY wopp.LocationCode SELECT *
FROM #temp_DNList
ORDER BY LocationCode;
----------------------------------------------------------计算启用的账户手上的已分配的DN数,一个DN对应一个料
WITH a AS ( SELECT fpln.Account ,
ISNULL(COUNT(fdt.DN), 0) OrderNum
FROM ( SELECT DISTINCT
Account
FROM dbo.FRU_PickLocationNew
WHERE IsUse = 1
) fpln
LEFT JOIN WMS_FRU_DNAllocationToAccount fdt ON fdt.Picker = fpln.Account
INNER JOIN dbo.WMS_Outbound wo ON wo.UserDefineAttribute01 = fdt.DN
AND WO.PickState <> 2
AND wo.ShipmentState <> 3
GROUP BY fpln.Account
),
b AS ( SELECT DISTINCT
Account
FROM dbo.FRU_PickLocationNew
WHERE IsUse = 1
)
SELECT b.Account ,
CASE WHEN ISNULL(a.Account, '') <> '' THEN a.OrderNum
ELSE 0
END OrderNum
FROM b
LEFT JOIN a ON a.Account = b.Account
---------------------------------------------------------汇总本次需要系统自动分配库位，按照库位分配至账号，优先挑手上DN量最少的账户
IF EXISTS ( SELECT *
FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#temp_AllocationLocation') )
BEGIN
DROP TABLE #temp_AllocationLocation
END
CREATE TABLE #temp_AllocationLocation
(
LocationCode NVARCHAR(MAX) ,
RN INT
)
INSERT INTO #temp_AllocationLocation
( LocationCode ,
RN
)
SELECT sss.LocationCode ,
ROW_NUMBER() OVER ( ORDER BY sss.LocationCode ) RN
FROM ( SELECT DISTINCT
LocationCode
FROM #temp_DNList
) sss
SELECT *
FROM #temp_AllocationLocation
ORDER BY LocationCode;
DECLARE @i INT= 1;
DECLARE @de_Location NVARCHAR(250);--当前遍历处理的库位
DECLARE @de_Account NVARCHAR(250);--当前要分配任务至的账户，挑选目前手上DN量最少的账户
WHILE @i <= ( SELECT MAX(RN)
FROM #temp_AllocationLocation
)
BEGIN SELECT @de_Location = LocationCode
FROM #temp_AllocationLocation
WHERE RN = @i;
IF EXISTS ( SELECT TOP 1
1
FROM #temp_DNList
WHERE LocationCode = @de_Location )
BEGIN
WITH a AS ( SELECT fpln.Account ,
ISNULL(COUNT(fdt.DN), 0) OrderNum
FROM ( SELECT DISTINCT
Account
FROM dbo.FRU_PickLocationNew
WHERE IsUse = 1
) fpln
LEFT JOIN WMS_FRU_DNAllocationToAccount fdt ON fdt.Picker = fpln.Account
INNER JOIN dbo.WMS_Outbound wo ON wo.UserDefineAttribute01 = fdt.DN
AND WO.PickState <> 2
AND wo.ShipmentState <> 3
GROUP BY fpln.Account
),
b AS ( SELECT DISTINCT
Account
FROM dbo.FRU_PickLocationNew
WHERE IsUse = 1
AND Location = SUBSTRING(@de_Location,
1, 2)
)
SELECT TOP 1
@de_Account = sss.Account
FROM ( SELECT b.Account ,
CASE WHEN ISNULL(a.Account, '') <> ''
THEN a.OrderNum
ELSE 0
END OrderNum
FROM b
LEFT JOIN a ON a.Account = b.Account
) sss
ORDER BY sss.OrderNum ,
sss.Account
--此次遍历的库位未在表FRU_PickLocationNew维护账户，因此未获取到可分配的账户
IF ( ISNULL(@de_Account, '') <> '' )
BEGIN
--发现该Location下存在DN要去不同库位上拣货，那么该DN全部分配给这个人
DECLARE @IsRepeatDN INT= 0;--标识，该Location下是否存在DN要去不同库位拣货，1表示存在这些DN
IF EXISTS ( SELECT *
FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#temp_RepeatDN') )
BEGIN
DROP TABLE #temp_RepeatDN
END
CREATE TABLE #temp_RepeatDN ( DN NVARCHAR(MAX) )
IF EXISTS ( SELECT DN
FROM #temp_DNList
WHERE dn IN (
SELECT dn
FROM #temp_DNList
WHERE LocationCode = @de_Location )
GROUP BY DN
HAVING COUNT(DN) > 1 )
BEGIN
INSERT INTO #temp_RepeatDN
( DN
)
SELECT DISTINCT
SSS.DN
FROM ( SELECT
DN
FROM
#temp_DNList
WHERE
dn IN (
SELECT
dn
FROM
#temp_DNList
WHERE
LocationCode = @de_Location )
GROUP BY DN
HAVING
COUNT(DN) > 1
) SSS
SELECT @IsRepeatDN = 1;
END
---------------------------
IF ( @IsRepeatDN <> 1 )
BEGIN
INSERT INTO dbo.WMS_FRU_DNAllocationToAccount
( DN ,
LocationCode ,
BOL ,
Picker ,
CreateBy ,
CreateDate
)
SELECT td.DN ,
td.LocationCode ,
td.BOL ,
@de_Account ,
'Evan' ,
GETDATE()
FROM #temp_DNList td
WHERE td.LocationCode = @de_Location
END
ELSE
BEGIN
INSERT INTO dbo.WMS_FRU_DNAllocationToAccount
( DN ,
LocationCode ,
BOL ,
Picker ,
CreateBy ,
CreateDate
)
SELECT td.DN ,
td.LocationCode ,
td.BOL ,
@de_Account ,
'Evan' ,
GETDATE()
FROM #temp_DNList td
WHERE td.LocationCode = @de_Location
AND TD.DN NOT IN (
SELECT
dn
FROM #temp_RepeatDN ) INSERT INTO dbo.WMS_FRU_DNAllocationToAccount
( DN ,
LocationCode ,
BOL ,
Picker ,
CreateBy ,
CreateDate
)
SELECT td.DN ,
td.LocationCode ,
td.BOL ,
@de_Account ,
'Evan' ,
GETDATE()
FROM #temp_DNList td
WHERE TD.DN IN ( SELECT
dn
FROM
#temp_RepeatDN )
END DELETE FROM #temp_DNList
WHERE LocationCode = @de_Location;
DELETE FROM #temp_DNList
WHERE dn IN ( SELECT dn
FROM #temp_RepeatDN );
DROP TABLE #temp_RepeatDN;
END
END
SET @i = @i + 1; END
SELECT *
FROM #temp_DNList;
----------------------------------------------------- END