How to have to AND NOT EXIST() in one statement?
This is the statement but I think has an error or
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@diffBetweenFoundAndExpSeq
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
--AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND ((SPI.Position = SP.Position AND (SPI.StorageBlocked <> 0 OR SPI.StorageDisabled <> 0))
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty or booked for stockunit in stack. ---
--HERE IS SEYING THAT THERE IS AN INCORRECT SYNTAX NEAR THE KEYWORD 'AND'
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE SUPE.Id_StoragePosition = sp.Id
AND (
ISNULL( SUPE.Booking, 0) = 0
OR
(
(
SL.Id <> @IdStorageLocationToWhereL
OR
(
(
SL.Id = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Position, SP.Clearence, SL.Clearance
ORDER BY SP.Position ASC
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@diffBetweenFoundAndExpSeq
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
--AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND ((SPI.Position = SP.Position AND (SPI.StorageBlocked <> 0 OR SPI.StorageDisabled <> 0))
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty or booked for stockunit in stack. ---
--HERE IS SEYING THAT THERE IS AN INCORRECT SYNTAX NEAR THE KEYWORD 'AND'
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE SUPE.Id_StoragePosition = sp.Id
AND (
ISNULL( SUPE.Booking, 0) = 0
OR
(
(
SL.Id <> @IdStorageLocationToWhereL
OR
(
(
SL.Id = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Position, SP.Clearence, SL.Clearance
ORDER BY SP.Position ASC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your code is fine. What error message/problem are you having?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am still not getting any compile error. Can you please post all the relevant code including all DECLAREs
ASKER
This is the entire code: when I remove the ) I found out that there is other one on the top somewhere that open this one.
USE [WMS_KRAFT749_DEV]
GO
/****** Object: StoredProcedure [WMS.Storage].[LookForPosi
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WMS.Storage].[LookForPosi
@IdStockUnit bigint = null,
@ActualLevel int = 0,
@IdTransportOrder bigint = null,
@FoundPosForProcessedStock
@StockUnitsInStack [WMS_Kraft].[StockUnitStac
@IdStorageLocationFrom bigint = NULL,
@IdStorageLocationToWhereL
@ExcludeStorageLocationFro
@StockUnitsOnLGV bit = 0,
@ConsiderOnlyPhysicallyBus
@BookFoundPosition bit = 1,
@CheckLTOBlockStorageBlock
@CheckClearence bit = 1,
@ForceEmptyStoragePosition
-------- OTHER INPUT PARAMETERS.
@EnableSavePoint bit = 1,
-------- OUTPUT PARAMETERS ---------------
@FoundStoragePositionID bigint = null output,
@FoundStorageLocationID bigint = null output,
@FoundInternalLevel int = null output,
@DepositHeight float = null output,
@NoPosFoundBecauseOfMissin
@NoPosFoundBecauseOfLogica
-------- OTHER OUTPUT PARAMETERS ---------------
@ResultCode int = null output,
@ResultMsg nvarchar(255) = null output,
@ResultTimeStamp datetime = null output
AS
BEGIN
DECLARE @preTranCount int,
@error int,
@counter int,
@savePointDefined bit = 0
DECLARE @CurrentTimeStamp DateTime
DECLARE @actProcName nvarchar(255) = null
SET NOCOUNT ON;
BEGIN TRY
SET @ResultCode = 0; -- Default value = 0 --> NO RECORD MODIFIED.
SET @Resultmsg = NULL;
SET @counter = 0;
SET @error = 0;
set @CurrentTimeStamp = GETDATE();
SET @ResultTimeStamp = @CurrentTimeStamp;
set @actProcName = object_name( @@PROCID )
if ( XACT_STATE() < 0 )
begin
SET @RESULTCODE = -2;
SET @RESULTMSG = 'XACT_STATE() < 0, unable to execute the SP. ';
return @ResultCode;
end
SET @preTranCount = @@trancount;
IF @preTranCount = 0
BEGIN
BEGIN TRANSACTION @actProcName
END
ELSE
BEGIN
if ( @EnableSavePoint <> 0 )
BEGIN
SAVE TRANSACTION @actProcName
SET @savePointDefined = 1
END;
END;
--------------------- OPERATIVE STEPS --------------------------
DECLARE @defaultLoadAidHeight float = null
DECLARE @defaultSKUHeight float = null
DECLARE @stockUnitHeight float = null
DECLARE @loadAidHeight float = null
DECLARE @skuHeight float = null
DECLARE @strictSequencingRequest bit = 0
DECLARE @dynamicStagingOptionON bit = 0
DECLARE @hotLoadStagingOptionON bit = 0
DECLARE @idShipment bigint = null
DECLARE @actLogicalOrder int = 0
DECLARE @idPrevTransportOrder bigint = null
DECLARE @actDockLoadSeq int = null
DECLARE @actTierNumber NVARCHAR(1) = NULL
DECLARE @idPrevStoragePosition bigint = null
DECLARE @prevDockLoadSeq int = NULL
DECLARE @prevLogicalOrder int
DECLARE @prevTierNumber NVARCHAR(1) = NULL
DECLARE @prevInternalLevel int
DECLARE @prevPosition int = null
DECLARE @prevLevel int = null
DECLARE @prevIdStockUnit bigint = null
DECLARE @prevStockUnitAlreadyStage
DECLARE @idPrevStorageLocation bigint = null
DECLARE @InSequencePosForProcessed
DECLARE @OutOfSequencePosForProces
DECLARE @shipmentNr nvarchar(50) = null
DECLARE @prevAssignmentUTCTimeStam
DECLARE @tempClearance float = null
DECLARE @tempI int = null
DECLARE @tempStackHeight float = null
DECLARE @maxLogicalOrderInShipment
DECLARE @minLogicalOrderInStagingL
DECLARE @maxLogicalOrderInStagingL
DECLARE @diffBetweenFoundAndExpSeq
DECLARE @StorageLocationToExclude table
(
Id_Location bigint
)
IF ( @ExcludeStorageLocationFro
AND ( NOT @IdStorageLocationFrom IS NULL )
BEGIN
INSERT INTO @StorageLocationToExclude ( Id_Location )
VALUES ( @IdStorageLocationFrom )
END
IF ( @CheckLTOBlockStorageBlock
BEGIN
INSERT INTO @StorageLocationToExclude(
SELECT BSSL.Id_StorageLocation
FROM WMS_Kraft.GetBlockStorageS
END
-- We reset the output parameter.
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfMissin
SET @NoPosFoundBecauseOfLogica
-- We have to check if the shipment has some assigned stagin lanes.
-- we check if all the transport Orer with lowet logical order are already in.
SELECT TOP(1) @idShipment = SH.Id,
@shipmentNr = SH.ShipmentNr,
@strictSequencingRequest = SH.strictSequencingRequest
@dynamicStagingOptionON = SH.DynamicStagingOption,
@hotLoadStagingOptionON = SH.HotLoadStagingOption,
@actLogicalOrder = TOR.LogicalOrder,
@actDockLoadSeq = TOR.E80_DockLoadSeqNum,
@actTierNumber = TOR.TierNumber
FROM [WMS.Handling].TransportOr
JOIN [WMS.Shipping].Shipment SH
ON TOR.ShipmentNr = SH.ShipmentNr
WHERE TOR.Id = @IdTransportOrder
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( @idShipment is null )
BEGIN
SET @ResultCode = 0;
SET @ResultMsg = 'No Shipment found, unable to proceed';
Goto FinalSteps;
END
-- We load config parameter.
SELECT @defaultLoadAidHeight = CASE
WHEN A.Name = 'LoadAidDefaultHeight' AND ( ISNUMERIC(A.VALUE) = 1 ) THEN CAST ( A.VALUE AS FLOAT )
ELSE @defaultLoadAidHeight
END,
@defaultSKUHeight = CASE
WHEN A.Name = 'SKUDefaultHeight' AND ( ISNUMERIC(A.VALUE) = 1 ) THEN CAST ( A.VALUE AS FLOAT )
ELSE @defaultSKUHeight
END
FROM [WMS.Common].ApplicationSe
WHERE A.[Group] = 'WMSCONFIG'
AND A.NAME IN ( 'LoadAidDefaultHeight', 'SKUDefaultHeight')
-- We Load data about stockunit.
-- We Load Info about StockUnit: SKU, Loadid, Dispostition code, Sales Velocity Code, etc.c.c.
SELECT @loadAidHeight = LA.Height,
@skuHeight = SKU.Height
FROM [WMS.Inventory].StockUnit S
LEFT JOIN [WMS.Inventory].LoadAid LA
ON S.Id_LoadAid = LA.Id
LEFT JOIN [WMS.Inventory].SKU SKU
ON S.Id_Sku = SKU.Id
WHERE S.Id = @IdStockUnit
-- We compute the stockUnit height.
IF ( ISNULL( @loadAidHeight, 0 ) <= 0 )
BEGIN
SET @loadAidHeight = ISNULL( @defaultLoadAidHeight, 0)
END
IF ( ISNULL( @skuHeight, 0 ) <= 0 )
BEGIN
SET @skuHeight = ISNULL( @defaultSKUHeight, 0 )
END
SET @stockUnitHeight = @skuHeight + @loadAidHeight
SELECT @maxLogicalOrderInShipment
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
--first check the staging lane type connected to the shipment
declare @stagingLaneType nvarchar(150)
select @stagingLaneType = t4.Name from [WMS.Shipping].Shipment t1
inner join [WMS.Shipping].DockStaging
inner join [WMS.Storage].StorageLocat
inner join [WMS.Storage].StorageLocat
where t1.Id = @idShipment
if (@stagingLaneType = 'Conveyor')
begin
--Lowville situation
--When a conveyor is used for staging we need not to look and book the position
--as the staging lane is always available (from WMS point of view)
set @NoPosFoundBecauseOfLogica
set @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'No needs for position searching because the staging lane are Conveyor';
Goto FinalSteps;
end
else if (@stagingLaneType = 'BlockStorage')
begin
IF NOT EXISTS (
SELECT SH.Id
FROM [WMS.Shipping].Shipment SH
WHERE SH.Id = @idShipment
AND SH.MaxLogicalOrder >= @actLogicalOrder
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need more space in staging lanes based on MaxLogicalOrder of Shipment';
Goto FinalSteps;
END
DELETE FROM @InSequencePosForProcessed
DELETE FROM @OutOfSequencePosForProces
INSERT INTO @InSequencePosForProcessed
SELECT FPS.*
FROM @FoundPosForProcessedStock
JOIN [WMS.Handling].TransportOr
ON FPS.IdTransportOrder = TOR.Id
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actLogicalOrder
AND TOR.Id <> @IdTransportOrder
-- We don't put OTHER constraint about found internal level.
-- So we can have, here, an inversion between tier and booked internal level
-- but this inversion can be fixed outside in GetUnique....
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We fill the @OutOfSequencePosForProces
INSERT INTO @OutOfSequencePosForProces
SELECT FPS.*
FROM @FoundPosForProcessedStock
LEFT OUTER JOIN @InSequencePosForProcessed
ON FPS.IdStockUnit = ISFP.IdStockUnit
WHERE ISFP.IdStockUnit IS NULL
AND FPS.IdTransportOrder <> @IdTransportOrder
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We try to look for position already booked/occupied by other transportOrder
-- with same @actDockLoadSeq.
SET @idPrevStoragePosition = NULL
SET @idPrevStorageLocation = NULL
SET @prevPosition = NULL
SET @prevLevel = NULL
SET @prevInternalLevel = NULL
SET @prevDockLoadSeq = NULL
SET @prevTierNumber = NULL
SET @prevIdStockUnit = NULL
SET @idPrevTransportOrder = NULL
SET @prevLogicalOrder = NULL
SET @prevStockUnitAlreadyStage
SET @prevAssignmentUTCTimeStam
SELECT TOP(1) @idPrevStoragePosition = SP.Id,
@idPrevStorageLocation = SP.Id_StorageLocation,
@prevPosition = SP.POSITION,
@prevLevel = SP.level,
@prevInternalLevel = SUP.InternalLevel,
@prevDockLoadSeq = TOR.E80_DockLoadSeqNum,
@prevTierNumber = TOR.TierNumber,
@prevIdStockUnit = SUP.Id_StockUnit,
@idPrevTransportOrder = TOR.Id,
@prevLogicalOrder = TOR.LogicalOrder,
@prevStockUnitAlreadyStage
WHEN SUP.Booking <> 0 THEN 0
ELSE 1
END,
@prevAssignmentUTCTimeStam
FROM [WMS.Handling].TransportOr
LEFT JOIN [WMS.Handling].StockUnitAl
ON SUA.Id_TransportOrder = TOR.Id
LEFT JOIN [WMS.Storage].StockUnitPos
ON (
TOR.Id_ConnectedStockUnit = SUP.ID_STOCKUNIT
OR
SUA.Id_StockUnit = SUP.Id_StockUnit
)
LEFT JOIN [WMS.Storage].StoragePosit
ON SUP.Id_StoragePosition = SP.ID
LEFT JOIN [WMS.Shipping].ShipmentSta
ON SP.Id_StorageLocation = SSGL.Id_StorageLocation
WHERE TOR.Id_TransportOrderType IN ( SELECT TOT.ID FROM [WMS.Handling].TransportOr
AND TOR.Id_TransportOrderStatu
AND TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actDockLoadSeq
AND TOR.TierNumber < @actTierNumber
AND ( NOT SUP.Id_StockUnit IS NULL )
AND ( NOT SP.Id IS NULL )
AND SSGL.Id_Shipment = @idShipment
ORDER BY TOR.TierNumber DESC
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We have to wait for previous transportOrder?
-- For now we consider the wait condition only when we could stack but we didn't find
-- a good transportOrder with same DockLoadSeqNumber.
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actDockLoadSeq
AND TOR.Id_TransportOrderStatu
AND (
( @counter = 0
AND
TOR.TierNumber < @actTierNumber
)
OR
(
@counter > 0
AND
TOR.TierNumber > @prevTierNumber
AND
TOR.TierNumber < @actTierNumber
)
)
)
--
OR (
( @actDockLoadSeq = @prevDockLoadSeq )
AND
( @ConsiderOnlyPhysicallyBus
AND
( @prevStockUnitAlreadyStage
AND NOT EXISTS (
SELECT SUS.IdStockUnit
FROM @StockUnitsInStack SUS
WHERE SUS.IdStockUnit = @prevIdStockUnit
)
)
-- We wait for other transportOrder with same @actDockLoadSeq.
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder with same DockLoadSeqNum';
Goto FinalSteps;
END
-- Ok, we can proceed.
IF ( NOT @idPrevStoragePosition IS NULL )
-- Ok, we have found a precise landmark having same DockLoadSeq.
BEGIN
-- We can try to stack in same position where we have landmark if:
-- - actual tier allows it ( we have in storageposition a number of stockunit less than max tier number ).
-- and
-- - the storageposition is not blocked, disabled, etc.c.
SET @NoPosFoundBecauseOfLogica
SET @tempClearance = null;
-- We don't consider the condition on physically status of @idPrevStoragePosition
-- because we trust completely in whant @idPrevTransportOrder did before.
-- In addition, sice we have multiple tiernumbers for a given DockLoad we must not
-- overstage.
IF ( @ConsiderOnlyPhysicallyBus
BEGIN
SELECT TOP(1) @tempClearance = ISNULL( sp.clearence, sl.clearance ),
@FoundInternalLevel = COUNT( DISTINCT CASE
WHEN SUP.Id_StockUnit = @IdStockUnit THEN NULL
ELSE SUP.Id_StockUnit
END ) + 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT JOIN [WMS.Storage].StockUnitPos
ON SP.ID = SUP.Id_StoragePosition
AND (
ISNULL( SUP.BOOKING, 0 ) = 0
OR
(
-- Even if we have the @ConsiderOnlyPhysicallyBus
-- we consider the booked position for stockunits in actual stack.
SUP.Booking <> 0
AND
( NOT ISFP.IdStoragePosition IS NULL )
AND
( ISFP.IdStockUnit = SUP.Id_StockUnit )
AND
( ISFP.InternalLevel = SUP.InternalLevel )
)
)
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE SLTE.Id_Location IS NULL
AND SP.Id_StorageLocation = @idPrevStorageLocation
AND SP.Id = @idPrevStoragePosition
AND NOT EXISTS (
SELECT OSFP.IdStockUnit
FROM @OutOfSequencePosForProces
WHERE OSFP.IdStoragePosition = SP.Id
)
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
-- The Destination position must not be blocked by other blocked of busy position.
-- We use directly the BlockStorage conditions for disabled, blocked, etc.c
-- because we are in case of @stoargelocationType = 'BlockStorage' staging lane.
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND (
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
GROUP BY sp.clearence, sl.clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
ELSE
-- @ConsiderOnlyPhysicallyBus
BEGIN
SELECT TOP(1) @tempClearance = ISNULL( sp.clearence, sl.clearance ),
@FoundInternalLevel = COUNT( DISTINCT CASE
WHEN SUP.Id_StockUnit = @IdStockUnit THEN NULL
ELSE SUP.Id_StockUnit
END ) + 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
LEFT JOIN [WMS.Storage].StockUnitPos
ON SP.ID = SUP.Id_StoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE SLTE.Id_Location IS NULL
AND SP.Id_StorageLocation = @idPrevStorageLocation
AND SP.Id = @idPrevStoragePosition
AND NOT EXISTS (
SELECT OSFP.IdStockUnit
FROM @OutOfSequencePosForProces
WHERE OSFP.IdStoragePosition = SP.Id
)
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
-- The Destination position must not be blocked by other blocked of busy position.
-- We use directly the BlockStorage conditions for disabled, blocked, etc.c
-- because we are in case of @stoargelocationType = 'BlockStorage' staging lane.
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND (
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
GROUP BY sp.clearence, sl.clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
IF ( @counter > 0 )
BEGIN
SET @tempI = NULL
-- We need to check some other conditions.
IF ( ISNUMERIC ( @actTierNumber ) = 1 )
BEGIN
SET @tempI = CAST ( @actTierNumber as int )
END
SET @tempStackHeight = WMS_KRAFT.GetStackOfPallet
IF (
( @tempI IS NULL )
OR
( @tempI >= @FoundInternalLevel )
)
AND
(
(
ISNULL( @CheckClearence, 0 ) = 0
OR
( ( @tempStackHeight + @stockUnitHeight ) <= @tempClearance ) )
)
AND
( NOT @FoundInternalLevel IS NULL )
BEGIN
-- Ok, we have found a good position. The @FoundInternalLevel output variable
-- is already set.
SET @FoundStoragePositionID = @idPrevStoragePosition
SET @FoundStorageLocationID = @idPrevStorageLocation
SET @DepositHeight = @tempStackHeight
SET @ResultCode = 1;
SET @ResultMsg = 'Found stack position on previous stockunit already staged';
Goto BookingAlignmentAndExit;
END
END
-- If we arrive here it means we didn't find a good position even if
-- ( @actDockLoadSeq = @prevDockLoadSeq ).
-- This is an error condition so we look for
-- a position in very far storageposition .
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SGL.Id_StorageLocation
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.ID = SLTE.Id_Location
WHERE ( SGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND SGL.MINLOGICALORDER > @maxLogicalOrderInShipment
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and not booked for stockunit not in stack.
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE supe.Id_StoragePosition = sp.Id
AND (
ISNULL(SUPE.Booking, 0 ) = 0
OR
(
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
ORDER BY SGL.AssignmentUTCTimeStamp
SP.Position ASC,
SP.Id
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Delayed StoragePosition found for transportOrder with stack.';
Goto BookingAlignmentAndExit;
END
ELSE
BEGIN
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need other space in staging lane for delay booking purpose.';
Goto FinalSteps;
END
END
-- If we arrive here it means we didn't find a transportOrder with same DockLoadSeqNumber
-- and lower TierNumber because it doesn't exit:
-- - we are working with the unique transportOrder having such DockLoadSeqNumber.
-- or
-- - we are working with first tier number of such DockLoadSeqNumber.
-- or
-- - we are a cloned transportOrder.
-- ( in case of different original transportOrder with same dockloadseq and different tier
-- number we don't want cloned ).
--
-- -> In all of case we work with theoretically pallet distribution in ShipmentStagingLane table.
-- We compute the flag @firstPalletOfAStack
DECLARE @firstPalletOfAMovingStack
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder = @actLogicalOrder
AND TOR.Id <> @IdTransportOrder
AND TOR.Id_TransportOrderStatu
AND TOR.ClonedFromTransportOrd
)
BEGIN
SET @firstPalletOfAMovingStack
END
IF ( @strictSequencingRequest <> 0 )
BEGIN
-- SET @firstPositionInStagingLan
SET @maxLogicalOrderInStagingL
SET @minLogicalOrderInStagingL
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @tempClearance = NULL
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and booked for stockunit in stack
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPI.Id_StockUnit = SUS.IdStockUnit
WHERE SUPI.Id_StoragePosition = SP.Id
AND (
( ISNULL( SUPI.Booking, 0 ) = 0 )
OR
(
SUPI.Booking <> 0
AND
(
( SP.Id_StorageLocation <> @IdStorageLocationToWhereL
OR
(
(
SP.Id_StorageLocation = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUS.IdStockUnit IS NULL
)
)
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Clearence, SL.Clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder >= @minLogicalOrderInStagingL
AND TOR.LogicalOrder < @actLogicalOrder
AND TOR.Id_TransportOrderStatu
)
-- We have to wait due to logicalorder.
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder with different DockLoadSeqNum';
Goto FinalSteps;
END
IF ( ( WMS_KRAFT.GetStackOfPallet
OR
( ISNULL( @CheckClearence, 0 ) = 0 )
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'StoragePosition found for transportOrder - Strictsequencing';
Goto BookingAlignmentAndExit;
END
END
-- We don't go to FinalSteps because, in this case, we have an escape solution after.
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
END
ELSE
-- @StrictSequencingRequest = 0
BEGIN
SET @diffBetweenFoundAndExpSeq
SET @minLogicalOrderInStagingL
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @tempClearance = NULL
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@diffBetweenFoundAndExpSeq
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
--AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty or booked for stockunit in stack. ---
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE SUPE.Id_StoragePosition = sp.Id
AND (
ISNULL( SUPE.Booking, 0) = 0
OR
(
(
SL.Id <> @IdStorageLocationToWhereL
OR
(
(
SL.Id = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Position, SP.Clearence, SL.Clearance
ORDER BY SP.Position ASC
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( ( @counter > 0 )
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
-- Even if we have strictSequening = 0, if we have a stack of pallet with lower logicalorder
-- directed to the staging lane we wait.
IF EXISTS (
SELECT AD.LogicalOrder
FROM (SELECT TOR.LogicalOrder,
COUNT( CASE
WHEN TOS.Name = 'Closed' THEN TOR.Id
ELSE NULL
END ) AS Completed,
COUNT( TOR.Id) AS NrPalletInStack
FROM [WMS.Handling].TransportOr
JOIN [WMS.Handling].TransportOr
ON TOR.Id_TransportOrderStatu
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOS.NAME NOT IN ( 'Aborted', 'Deleted' )
GROUP BY TOR.LogicalOrder ) AS AD
WHERE AD.NrPalletInStack > 1
AND AD.Completed < AD.NrPalletInStack
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder due to stack to complete.';
Goto FinalSteps;
END
IF ( ( WMS_KRAFT.GetStackOfPallet
OR
( ISNULL( @CheckClearence, 0 ) = 0 )
BEGIN
-- We evaluate the status of @diffBetweenFoundAndExpSeq
-- - 0 --> It means we have found exactly the match position.
-- - < 0 --> It means we have found a position deeper than the expected one.
-- - > 0 --> It means we have found a position more external than the expected one.
IF ( @diffBetweenFoundAndExpSeq
BEGIN
-- We can go.
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Best StoragePosition found for transportOrder - Delta Seq = 0';
Goto BookingAlignmentAndExit;
END
-- We compute some other info about the situation of the staging lane.
DECLARE @minDelta int = 0
DECLARE @maxDelta int = 0
DECLARE @countPalletStaged int = 0
SELECT @minDelta = MIN ( CASE
WHEN StagingLaneInfo.Delta < 0 THEN StagingLaneInfo.Delta
ELSE 0
END ),
@maxDelta = MAX ( CASE
WHEN StagingLaneInfo.Delta > 0 THEN StagingLaneInfo.Delta
ELSE 0
END ),
@countPalletStaged = SUM ( StagingLaneInfo.NrPallet )
FROM
(SELECT SP.Position,
TOR.LogicalOrder,
( sp.Position - 1 + @minLogicalOrderInStagingL
COUNT( DISTINCT SUP.Id_StockUnit) AS NrPallet
FROM [WMS.Handling].TransportOr
JOIN [WMS.Storage].StockUnitPos
ON TOR.Id_ConnectedStockUnit = SUP.Id_StockUnit
JOIN [WMS.Storage].StoragePosit
ON SUP.Id_StoragePosition = SP.Id
WHERE SP.Id_StorageLocation = @FoundStorageLocationID
AND ISNULL( SUP.BOOKING, 0 ) = 0
AND TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
GROUP BY SP.Position, TOR.LogicalOrder ) AS StagingLaneInfo
SET @countPalletStaged = ISNULL(@countPalletStaged,
IF ( @countPalletStaged = 0 )
BEGIN
IF (
( @diffBetweenFoundAndExpSeq
AND
(
( @diffBetweenFoundAndExpSeq
OR
( @firstPalletOfAMovingStack
)
)
OR
( @diffBetweenFoundAndExpSeq
BEGIN
-- We have to check if there is no other executing transportOrder with lower logical order in
-- in staging lane.
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOR.Id_TransportOrderStatu
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait other transportOrder because we are moving first pallet Of Stack or deeper position.';
Goto FinalSteps;
END
END
-- If we arrive here it means we can continue.
END
ELSE
-- We have already some pallets in the staging lane.
BEGIN
IF NOT(
( @minDelta = -1 )
AND
( @diffBetweenFoundAndExpSeq
AND
( @maxDelta = 0 )
)
-- We have to check if there is other transportOrder with lower dockloadquenumber
BEGIN
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOR.Id_TransportOrderStatu
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait other transportOrder because we have some pallets in staging.';
Goto FinalSteps;
END
END
-- If we arrive here it means we can continue.
END
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Best StoragePosition found for transportOrder - Delta Seq not 0';
Goto BookingAlignmentAndExit;
END
END
-- This is an error condition due to:
-- - some storagePosition blocked or disabled.
-- - some storagePosition busy but expected free.
--
-- -> We continue to find an escape solution.
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
END
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHSL.Id_StorageLocation
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE ( SHSL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND SHSL.MINLOGICALORDER > @maxLogicalOrderInShipment
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and not booked for stockunit not in stack.
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE supe.Id_StoragePosition = sp.Id
AND (
ISNULL(SUPE.Booking, 0 ) = 0
OR
(
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
ORDER BY SHSL.AssignmentUTCTimeStam
SP.Position ASC,
SP.Id
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Delayed StoragePosition found for transportOrder.';
Goto BookingAlignmentAndExit;
END
ELSE
BEGIN
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need other space in staging lane for delay booking purpose.';
Goto FinalSteps;
END
END -- End of (@stagingLaneType = 'BlockStorage')
BookingAlignmentAndExit:
IF ( NOT @FoundStoragePositionID IS NULL )
AND ( NOT @FoundStorageLocationID IS NULL )
AND ( NOT @FoundInternalLevel IS NULL )
AND ( NOT @DepositHeight IS NULL )
BEGIN
if ( @BookFoundPosition <> 0 )
BEGIN
INSERT INTO [WMS.Storage].StockUnitPos
SELECT TOP(1) 1, @DepositHeight, @IdStockUnit, @FoundStoragePositionID, @FoundInternalLevel, @CurrentTimeStamp
WHERE NOT EXISTS(
SELECT SUP.Id
FROM [WMS.Storage].StockUnitPos
WHERE SUP.Id_StockUnit = @IdStockUnit
AND SUP.Booking <> 0
AND SUP.Id_StoragePosition = @FoundStoragePositionID
)
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( @counter = 0)
BEGIN
UPDATE [WMS.Storage].StockUnitPos
SET Heigth = @DepositHeight,
InternalLevel = @FoundInternalLevel,
PositioningTime = @CurrentTimeStamp
WHERE StockUnitPosition.Id_Stock
AND StockUnitPosition.Booking <> 0
AND StockUnitPosition.Id_Stora
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
IF ( @counter > 0 )
BEGIN
SET @ResultCode = @counter
END
ELSE
BEGIN
SET @ResultCode = -13;
SET @ResultMsg = 'Critical SW error during position reservation';
Raiserror( @ResultMsg, 16, 1)
Goto FinalSteps;
END
Goto FinalSteps;
END
ELSE
BEGIN
SET @ResultCode = 1
Goto FinalSteps;
END
END
ELSE
BEGIN
SET @FoundStoragePositionID = NULL
SET @FoundStorageLocationID = NULL
SET @FoundInternalLevel = NULL
SET @DepositHeight = NULL
SET @ResultCode = 0;
SET @ResultMsg = 'No Position found';
Goto FinalSteps;
END
FinalSteps:
--------------------- FINAL STEPS --------------------------
IF ( @preTranCount = 0 )
begin
if ( @@TRANCOUNT > 0) and (XACT_STATE() > 0)
BEGIN
COMMIT TRAN @actProcName
END;
if ( @@TRANCOUNT > 0) and (XACT_STATE() < 0)
BEGIN
ROLLBACK TRAN @actProcName;
SET @ResultCode = -1;
SET @ResultMsg = ''
END;
end;
END TRY
BEGIN CATCH
declare @Error_number int, @Error_severity int
declare @Error_procedure nvarchar(126), @Error_message nvarchar(2048)
declare @Error_line int, @Error_state int
declare @xstate smallint, @actTranCount integer
select @Error_number = ERROR_NUMBER(),
@Error_severity = ERROR_SEVERITY(),
@Error_state = ERROR_STATE(),
@Error_procedure = ERROR_PROCEDURE(),
@Error_line = ERROR_LINE(),
@Error_message = ERROR_MESSAGE(),
@xstate = XACT_STATE(),
@actTranCount = @@TRANCOUNT
SET @ResultCode = -1;
if (( @xstate = -1) AND ( @preTranCount = 0))
ROLLBACK TRAN @actProcName; -- ROLLBACK COMPLETELY TRANSACTION .
if (( @xstate = 1) AND ( @preTranCount = 0))
ROLLBACK TRAN @actProcName; -- ROLLBACK COMPLETELY TRANSACTION .
if (( @xstate = 1) AND ( @preTranCount > 0))
BEGIN
IF ( @savePointDefined <> 0 )
BEGIN
ROLLBACK TRAN @actProcName; -- ROLLBACK ONLY LOCAL SAVEPOINT ( IF DEFINED ).
END;
END;
EXECUTE [WMS.Common].[LogProcedure
raiserror ('%s: %d: %s', 16, 1, @Error_Procedure, @Error_number, @Error_Message) ;
SET @Resultmsg = SUBSTRING(@Error_message,1
END CATCH
RETURN @ResultCode;
END
USE [WMS_KRAFT749_DEV]
GO
/****** Object: StoredProcedure [WMS.Storage].[LookForPosi
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WMS.Storage].[LookForPosi
@IdStockUnit bigint = null,
@ActualLevel int = 0,
@IdTransportOrder bigint = null,
@FoundPosForProcessedStock
@StockUnitsInStack [WMS_Kraft].[StockUnitStac
@IdStorageLocationFrom bigint = NULL,
@IdStorageLocationToWhereL
@ExcludeStorageLocationFro
@StockUnitsOnLGV bit = 0,
@ConsiderOnlyPhysicallyBus
@BookFoundPosition bit = 1,
@CheckLTOBlockStorageBlock
@CheckClearence bit = 1,
@ForceEmptyStoragePosition
-------- OTHER INPUT PARAMETERS.
@EnableSavePoint bit = 1,
-------- OUTPUT PARAMETERS ---------------
@FoundStoragePositionID bigint = null output,
@FoundStorageLocationID bigint = null output,
@FoundInternalLevel int = null output,
@DepositHeight float = null output,
@NoPosFoundBecauseOfMissin
@NoPosFoundBecauseOfLogica
-------- OTHER OUTPUT PARAMETERS ---------------
@ResultCode int = null output,
@ResultMsg nvarchar(255) = null output,
@ResultTimeStamp datetime = null output
AS
BEGIN
DECLARE @preTranCount int,
@error int,
@counter int,
@savePointDefined bit = 0
DECLARE @CurrentTimeStamp DateTime
DECLARE @actProcName nvarchar(255) = null
SET NOCOUNT ON;
BEGIN TRY
SET @ResultCode = 0; -- Default value = 0 --> NO RECORD MODIFIED.
SET @Resultmsg = NULL;
SET @counter = 0;
SET @error = 0;
set @CurrentTimeStamp = GETDATE();
SET @ResultTimeStamp = @CurrentTimeStamp;
set @actProcName = object_name( @@PROCID )
if ( XACT_STATE() < 0 )
begin
SET @RESULTCODE = -2;
SET @RESULTMSG = 'XACT_STATE() < 0, unable to execute the SP. ';
return @ResultCode;
end
SET @preTranCount = @@trancount;
IF @preTranCount = 0
BEGIN
BEGIN TRANSACTION @actProcName
END
ELSE
BEGIN
if ( @EnableSavePoint <> 0 )
BEGIN
SAVE TRANSACTION @actProcName
SET @savePointDefined = 1
END;
END;
--------------------- OPERATIVE STEPS --------------------------
DECLARE @defaultLoadAidHeight float = null
DECLARE @defaultSKUHeight float = null
DECLARE @stockUnitHeight float = null
DECLARE @loadAidHeight float = null
DECLARE @skuHeight float = null
DECLARE @strictSequencingRequest bit = 0
DECLARE @dynamicStagingOptionON bit = 0
DECLARE @hotLoadStagingOptionON bit = 0
DECLARE @idShipment bigint = null
DECLARE @actLogicalOrder int = 0
DECLARE @idPrevTransportOrder bigint = null
DECLARE @actDockLoadSeq int = null
DECLARE @actTierNumber NVARCHAR(1) = NULL
DECLARE @idPrevStoragePosition bigint = null
DECLARE @prevDockLoadSeq int = NULL
DECLARE @prevLogicalOrder int
DECLARE @prevTierNumber NVARCHAR(1) = NULL
DECLARE @prevInternalLevel int
DECLARE @prevPosition int = null
DECLARE @prevLevel int = null
DECLARE @prevIdStockUnit bigint = null
DECLARE @prevStockUnitAlreadyStage
DECLARE @idPrevStorageLocation bigint = null
DECLARE @InSequencePosForProcessed
DECLARE @OutOfSequencePosForProces
DECLARE @shipmentNr nvarchar(50) = null
DECLARE @prevAssignmentUTCTimeStam
DECLARE @tempClearance float = null
DECLARE @tempI int = null
DECLARE @tempStackHeight float = null
DECLARE @maxLogicalOrderInShipment
DECLARE @minLogicalOrderInStagingL
DECLARE @maxLogicalOrderInStagingL
DECLARE @diffBetweenFoundAndExpSeq
DECLARE @StorageLocationToExclude table
(
Id_Location bigint
)
IF ( @ExcludeStorageLocationFro
AND ( NOT @IdStorageLocationFrom IS NULL )
BEGIN
INSERT INTO @StorageLocationToExclude ( Id_Location )
VALUES ( @IdStorageLocationFrom )
END
IF ( @CheckLTOBlockStorageBlock
BEGIN
INSERT INTO @StorageLocationToExclude(
SELECT BSSL.Id_StorageLocation
FROM WMS_Kraft.GetBlockStorageS
END
-- We reset the output parameter.
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfMissin
SET @NoPosFoundBecauseOfLogica
-- We have to check if the shipment has some assigned stagin lanes.
-- we check if all the transport Orer with lowet logical order are already in.
SELECT TOP(1) @idShipment = SH.Id,
@shipmentNr = SH.ShipmentNr,
@strictSequencingRequest = SH.strictSequencingRequest
@dynamicStagingOptionON = SH.DynamicStagingOption,
@hotLoadStagingOptionON = SH.HotLoadStagingOption,
@actLogicalOrder = TOR.LogicalOrder,
@actDockLoadSeq = TOR.E80_DockLoadSeqNum,
@actTierNumber = TOR.TierNumber
FROM [WMS.Handling].TransportOr
JOIN [WMS.Shipping].Shipment SH
ON TOR.ShipmentNr = SH.ShipmentNr
WHERE TOR.Id = @IdTransportOrder
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( @idShipment is null )
BEGIN
SET @ResultCode = 0;
SET @ResultMsg = 'No Shipment found, unable to proceed';
Goto FinalSteps;
END
-- We load config parameter.
SELECT @defaultLoadAidHeight = CASE
WHEN A.Name = 'LoadAidDefaultHeight' AND ( ISNUMERIC(A.VALUE) = 1 ) THEN CAST ( A.VALUE AS FLOAT )
ELSE @defaultLoadAidHeight
END,
@defaultSKUHeight = CASE
WHEN A.Name = 'SKUDefaultHeight' AND ( ISNUMERIC(A.VALUE) = 1 ) THEN CAST ( A.VALUE AS FLOAT )
ELSE @defaultSKUHeight
END
FROM [WMS.Common].ApplicationSe
WHERE A.[Group] = 'WMSCONFIG'
AND A.NAME IN ( 'LoadAidDefaultHeight', 'SKUDefaultHeight')
-- We Load data about stockunit.
-- We Load Info about StockUnit: SKU, Loadid, Dispostition code, Sales Velocity Code, etc.c.c.
SELECT @loadAidHeight = LA.Height,
@skuHeight = SKU.Height
FROM [WMS.Inventory].StockUnit S
LEFT JOIN [WMS.Inventory].LoadAid LA
ON S.Id_LoadAid = LA.Id
LEFT JOIN [WMS.Inventory].SKU SKU
ON S.Id_Sku = SKU.Id
WHERE S.Id = @IdStockUnit
-- We compute the stockUnit height.
IF ( ISNULL( @loadAidHeight, 0 ) <= 0 )
BEGIN
SET @loadAidHeight = ISNULL( @defaultLoadAidHeight, 0)
END
IF ( ISNULL( @skuHeight, 0 ) <= 0 )
BEGIN
SET @skuHeight = ISNULL( @defaultSKUHeight, 0 )
END
SET @stockUnitHeight = @skuHeight + @loadAidHeight
SELECT @maxLogicalOrderInShipment
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
--first check the staging lane type connected to the shipment
declare @stagingLaneType nvarchar(150)
select @stagingLaneType = t4.Name from [WMS.Shipping].Shipment t1
inner join [WMS.Shipping].DockStaging
inner join [WMS.Storage].StorageLocat
inner join [WMS.Storage].StorageLocat
where t1.Id = @idShipment
if (@stagingLaneType = 'Conveyor')
begin
--Lowville situation
--When a conveyor is used for staging we need not to look and book the position
--as the staging lane is always available (from WMS point of view)
set @NoPosFoundBecauseOfLogica
set @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'No needs for position searching because the staging lane are Conveyor';
Goto FinalSteps;
end
else if (@stagingLaneType = 'BlockStorage')
begin
IF NOT EXISTS (
SELECT SH.Id
FROM [WMS.Shipping].Shipment SH
WHERE SH.Id = @idShipment
AND SH.MaxLogicalOrder >= @actLogicalOrder
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need more space in staging lanes based on MaxLogicalOrder of Shipment';
Goto FinalSteps;
END
DELETE FROM @InSequencePosForProcessed
DELETE FROM @OutOfSequencePosForProces
INSERT INTO @InSequencePosForProcessed
SELECT FPS.*
FROM @FoundPosForProcessedStock
JOIN [WMS.Handling].TransportOr
ON FPS.IdTransportOrder = TOR.Id
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actLogicalOrder
AND TOR.Id <> @IdTransportOrder
-- We don't put OTHER constraint about found internal level.
-- So we can have, here, an inversion between tier and booked internal level
-- but this inversion can be fixed outside in GetUnique....
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We fill the @OutOfSequencePosForProces
INSERT INTO @OutOfSequencePosForProces
SELECT FPS.*
FROM @FoundPosForProcessedStock
LEFT OUTER JOIN @InSequencePosForProcessed
ON FPS.IdStockUnit = ISFP.IdStockUnit
WHERE ISFP.IdStockUnit IS NULL
AND FPS.IdTransportOrder <> @IdTransportOrder
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We try to look for position already booked/occupied by other transportOrder
-- with same @actDockLoadSeq.
SET @idPrevStoragePosition = NULL
SET @idPrevStorageLocation = NULL
SET @prevPosition = NULL
SET @prevLevel = NULL
SET @prevInternalLevel = NULL
SET @prevDockLoadSeq = NULL
SET @prevTierNumber = NULL
SET @prevIdStockUnit = NULL
SET @idPrevTransportOrder = NULL
SET @prevLogicalOrder = NULL
SET @prevStockUnitAlreadyStage
SET @prevAssignmentUTCTimeStam
SELECT TOP(1) @idPrevStoragePosition = SP.Id,
@idPrevStorageLocation = SP.Id_StorageLocation,
@prevPosition = SP.POSITION,
@prevLevel = SP.level,
@prevInternalLevel = SUP.InternalLevel,
@prevDockLoadSeq = TOR.E80_DockLoadSeqNum,
@prevTierNumber = TOR.TierNumber,
@prevIdStockUnit = SUP.Id_StockUnit,
@idPrevTransportOrder = TOR.Id,
@prevLogicalOrder = TOR.LogicalOrder,
@prevStockUnitAlreadyStage
WHEN SUP.Booking <> 0 THEN 0
ELSE 1
END,
@prevAssignmentUTCTimeStam
FROM [WMS.Handling].TransportOr
LEFT JOIN [WMS.Handling].StockUnitAl
ON SUA.Id_TransportOrder = TOR.Id
LEFT JOIN [WMS.Storage].StockUnitPos
ON (
TOR.Id_ConnectedStockUnit = SUP.ID_STOCKUNIT
OR
SUA.Id_StockUnit = SUP.Id_StockUnit
)
LEFT JOIN [WMS.Storage].StoragePosit
ON SUP.Id_StoragePosition = SP.ID
LEFT JOIN [WMS.Shipping].ShipmentSta
ON SP.Id_StorageLocation = SSGL.Id_StorageLocation
WHERE TOR.Id_TransportOrderType IN ( SELECT TOT.ID FROM [WMS.Handling].TransportOr
AND TOR.Id_TransportOrderStatu
AND TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actDockLoadSeq
AND TOR.TierNumber < @actTierNumber
AND ( NOT SUP.Id_StockUnit IS NULL )
AND ( NOT SP.Id IS NULL )
AND SSGL.Id_Shipment = @idShipment
ORDER BY TOR.TierNumber DESC
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
-- We have to wait for previous transportOrder?
-- For now we consider the wait condition only when we could stack but we didn't find
-- a good transportOrder with same DockLoadSeqNumber.
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.E80_DockLoadSeqNum = @actDockLoadSeq
AND TOR.Id_TransportOrderStatu
AND (
( @counter = 0
AND
TOR.TierNumber < @actTierNumber
)
OR
(
@counter > 0
AND
TOR.TierNumber > @prevTierNumber
AND
TOR.TierNumber < @actTierNumber
)
)
)
--
OR (
( @actDockLoadSeq = @prevDockLoadSeq )
AND
( @ConsiderOnlyPhysicallyBus
AND
( @prevStockUnitAlreadyStage
AND NOT EXISTS (
SELECT SUS.IdStockUnit
FROM @StockUnitsInStack SUS
WHERE SUS.IdStockUnit = @prevIdStockUnit
)
)
-- We wait for other transportOrder with same @actDockLoadSeq.
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder with same DockLoadSeqNum';
Goto FinalSteps;
END
-- Ok, we can proceed.
IF ( NOT @idPrevStoragePosition IS NULL )
-- Ok, we have found a precise landmark having same DockLoadSeq.
BEGIN
-- We can try to stack in same position where we have landmark if:
-- - actual tier allows it ( we have in storageposition a number of stockunit less than max tier number ).
-- and
-- - the storageposition is not blocked, disabled, etc.c.
SET @NoPosFoundBecauseOfLogica
SET @tempClearance = null;
-- We don't consider the condition on physically status of @idPrevStoragePosition
-- because we trust completely in whant @idPrevTransportOrder did before.
-- In addition, sice we have multiple tiernumbers for a given DockLoad we must not
-- overstage.
IF ( @ConsiderOnlyPhysicallyBus
BEGIN
SELECT TOP(1) @tempClearance = ISNULL( sp.clearence, sl.clearance ),
@FoundInternalLevel = COUNT( DISTINCT CASE
WHEN SUP.Id_StockUnit = @IdStockUnit THEN NULL
ELSE SUP.Id_StockUnit
END ) + 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT JOIN [WMS.Storage].StockUnitPos
ON SP.ID = SUP.Id_StoragePosition
AND (
ISNULL( SUP.BOOKING, 0 ) = 0
OR
(
-- Even if we have the @ConsiderOnlyPhysicallyBus
-- we consider the booked position for stockunits in actual stack.
SUP.Booking <> 0
AND
( NOT ISFP.IdStoragePosition IS NULL )
AND
( ISFP.IdStockUnit = SUP.Id_StockUnit )
AND
( ISFP.InternalLevel = SUP.InternalLevel )
)
)
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE SLTE.Id_Location IS NULL
AND SP.Id_StorageLocation = @idPrevStorageLocation
AND SP.Id = @idPrevStoragePosition
AND NOT EXISTS (
SELECT OSFP.IdStockUnit
FROM @OutOfSequencePosForProces
WHERE OSFP.IdStoragePosition = SP.Id
)
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
-- The Destination position must not be blocked by other blocked of busy position.
-- We use directly the BlockStorage conditions for disabled, blocked, etc.c
-- because we are in case of @stoargelocationType = 'BlockStorage' staging lane.
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND (
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
GROUP BY sp.clearence, sl.clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
ELSE
-- @ConsiderOnlyPhysicallyBus
BEGIN
SELECT TOP(1) @tempClearance = ISNULL( sp.clearence, sl.clearance ),
@FoundInternalLevel = COUNT( DISTINCT CASE
WHEN SUP.Id_StockUnit = @IdStockUnit THEN NULL
ELSE SUP.Id_StockUnit
END ) + 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
LEFT JOIN [WMS.Storage].StockUnitPos
ON SP.ID = SUP.Id_StoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE SLTE.Id_Location IS NULL
AND SP.Id_StorageLocation = @idPrevStorageLocation
AND SP.Id = @idPrevStoragePosition
AND NOT EXISTS (
SELECT OSFP.IdStockUnit
FROM @OutOfSequencePosForProces
WHERE OSFP.IdStoragePosition = SP.Id
)
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
-- The Destination position must not be blocked by other blocked of busy position.
-- We use directly the BlockStorage conditions for disabled, blocked, etc.c
-- because we are in case of @stoargelocationType = 'BlockStorage' staging lane.
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND (
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
GROUP BY sp.clearence, sl.clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
IF ( @counter > 0 )
BEGIN
SET @tempI = NULL
-- We need to check some other conditions.
IF ( ISNUMERIC ( @actTierNumber ) = 1 )
BEGIN
SET @tempI = CAST ( @actTierNumber as int )
END
SET @tempStackHeight = WMS_KRAFT.GetStackOfPallet
IF (
( @tempI IS NULL )
OR
( @tempI >= @FoundInternalLevel )
)
AND
(
(
ISNULL( @CheckClearence, 0 ) = 0
OR
( ( @tempStackHeight + @stockUnitHeight ) <= @tempClearance ) )
)
AND
( NOT @FoundInternalLevel IS NULL )
BEGIN
-- Ok, we have found a good position. The @FoundInternalLevel output variable
-- is already set.
SET @FoundStoragePositionID = @idPrevStoragePosition
SET @FoundStorageLocationID = @idPrevStorageLocation
SET @DepositHeight = @tempStackHeight
SET @ResultCode = 1;
SET @ResultMsg = 'Found stack position on previous stockunit already staged';
Goto BookingAlignmentAndExit;
END
END
-- If we arrive here it means we didn't find a good position even if
-- ( @actDockLoadSeq = @prevDockLoadSeq ).
-- This is an error condition so we look for
-- a position in very far storageposition .
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SGL.Id_StorageLocation
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.ID = SLTE.Id_Location
WHERE ( SGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND SGL.MINLOGICALORDER > @maxLogicalOrderInShipment
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and not booked for stockunit not in stack.
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE supe.Id_StoragePosition = sp.Id
AND (
ISNULL(SUPE.Booking, 0 ) = 0
OR
(
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
ORDER BY SGL.AssignmentUTCTimeStamp
SP.Position ASC,
SP.Id
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Delayed StoragePosition found for transportOrder with stack.';
Goto BookingAlignmentAndExit;
END
ELSE
BEGIN
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need other space in staging lane for delay booking purpose.';
Goto FinalSteps;
END
END
-- If we arrive here it means we didn't find a transportOrder with same DockLoadSeqNumber
-- and lower TierNumber because it doesn't exit:
-- - we are working with the unique transportOrder having such DockLoadSeqNumber.
-- or
-- - we are working with first tier number of such DockLoadSeqNumber.
-- or
-- - we are a cloned transportOrder.
-- ( in case of different original transportOrder with same dockloadseq and different tier
-- number we don't want cloned ).
--
-- -> In all of case we work with theoretically pallet distribution in ShipmentStagingLane table.
-- We compute the flag @firstPalletOfAStack
DECLARE @firstPalletOfAMovingStack
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder = @actLogicalOrder
AND TOR.Id <> @IdTransportOrder
AND TOR.Id_TransportOrderStatu
AND TOR.ClonedFromTransportOrd
)
BEGIN
SET @firstPalletOfAMovingStack
END
IF ( @strictSequencingRequest <> 0 )
BEGIN
-- SET @firstPositionInStagingLan
SET @maxLogicalOrderInStagingL
SET @minLogicalOrderInStagingL
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @tempClearance = NULL
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and booked for stockunit in stack
AND NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPI.Id_StockUnit = SUS.IdStockUnit
WHERE SUPI.Id_StoragePosition = SP.Id
AND (
( ISNULL( SUPI.Booking, 0 ) = 0 )
OR
(
SUPI.Booking <> 0
AND
(
( SP.Id_StorageLocation <> @IdStorageLocationToWhereL
OR
(
(
SP.Id_StorageLocation = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUS.IdStockUnit IS NULL
)
)
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Clearence, SL.Clearance
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder >= @minLogicalOrderInStagingL
AND TOR.LogicalOrder < @actLogicalOrder
AND TOR.Id_TransportOrderStatu
)
-- We have to wait due to logicalorder.
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder with different DockLoadSeqNum';
Goto FinalSteps;
END
IF ( ( WMS_KRAFT.GetStackOfPallet
OR
( ISNULL( @CheckClearence, 0 ) = 0 )
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'StoragePosition found for transportOrder - Strictsequencing';
Goto BookingAlignmentAndExit;
END
END
-- We don't go to FinalSteps because, in this case, we have an escape solution after.
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
END
ELSE
-- @StrictSequencingRequest = 0
BEGIN
SET @diffBetweenFoundAndExpSeq
SET @minLogicalOrderInStagingL
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @tempClearance = NULL
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
@minLogicalOrderInStagingL
@maxLogicalOrderInStagingL
@diffBetweenFoundAndExpSeq
@tempClearance = ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
--AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty or booked for stockunit in stack. ---
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE SUPE.Id_StoragePosition = sp.Id
AND (
ISNULL( SUPE.Booking, 0) = 0
OR
(
(
SL.Id <> @IdStorageLocationToWhereL
OR
(
(
SL.Id = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Position, SP.Clearence, SL.Clearance
ORDER BY SP.Position ASC
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( ( @counter > 0 )
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
-- Even if we have strictSequening = 0, if we have a stack of pallet with lower logicalorder
-- directed to the staging lane we wait.
IF EXISTS (
SELECT AD.LogicalOrder
FROM (SELECT TOR.LogicalOrder,
COUNT( CASE
WHEN TOS.Name = 'Closed' THEN TOR.Id
ELSE NULL
END ) AS Completed,
COUNT( TOR.Id) AS NrPalletInStack
FROM [WMS.Handling].TransportOr
JOIN [WMS.Handling].TransportOr
ON TOR.Id_TransportOrderStatu
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOS.NAME NOT IN ( 'Aborted', 'Deleted' )
GROUP BY TOR.LogicalOrder ) AS AD
WHERE AD.NrPalletInStack > 1
AND AD.Completed < AD.NrPalletInStack
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait for other transportOrder due to stack to complete.';
Goto FinalSteps;
END
IF ( ( WMS_KRAFT.GetStackOfPallet
OR
( ISNULL( @CheckClearence, 0 ) = 0 )
BEGIN
-- We evaluate the status of @diffBetweenFoundAndExpSeq
-- - 0 --> It means we have found exactly the match position.
-- - < 0 --> It means we have found a position deeper than the expected one.
-- - > 0 --> It means we have found a position more external than the expected one.
IF ( @diffBetweenFoundAndExpSeq
BEGIN
-- We can go.
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Best StoragePosition found for transportOrder - Delta Seq = 0';
Goto BookingAlignmentAndExit;
END
-- We compute some other info about the situation of the staging lane.
DECLARE @minDelta int = 0
DECLARE @maxDelta int = 0
DECLARE @countPalletStaged int = 0
SELECT @minDelta = MIN ( CASE
WHEN StagingLaneInfo.Delta < 0 THEN StagingLaneInfo.Delta
ELSE 0
END ),
@maxDelta = MAX ( CASE
WHEN StagingLaneInfo.Delta > 0 THEN StagingLaneInfo.Delta
ELSE 0
END ),
@countPalletStaged = SUM ( StagingLaneInfo.NrPallet )
FROM
(SELECT SP.Position,
TOR.LogicalOrder,
( sp.Position - 1 + @minLogicalOrderInStagingL
COUNT( DISTINCT SUP.Id_StockUnit) AS NrPallet
FROM [WMS.Handling].TransportOr
JOIN [WMS.Storage].StockUnitPos
ON TOR.Id_ConnectedStockUnit = SUP.Id_StockUnit
JOIN [WMS.Storage].StoragePosit
ON SUP.Id_StoragePosition = SP.Id
WHERE SP.Id_StorageLocation = @FoundStorageLocationID
AND ISNULL( SUP.BOOKING, 0 ) = 0
AND TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
GROUP BY SP.Position, TOR.LogicalOrder ) AS StagingLaneInfo
SET @countPalletStaged = ISNULL(@countPalletStaged,
IF ( @countPalletStaged = 0 )
BEGIN
IF (
( @diffBetweenFoundAndExpSeq
AND
(
( @diffBetweenFoundAndExpSeq
OR
( @firstPalletOfAMovingStack
)
)
OR
( @diffBetweenFoundAndExpSeq
BEGIN
-- We have to check if there is no other executing transportOrder with lower logical order in
-- in staging lane.
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOR.Id_TransportOrderStatu
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait other transportOrder because we are moving first pallet Of Stack or deeper position.';
Goto FinalSteps;
END
END
-- If we arrive here it means we can continue.
END
ELSE
-- We have already some pallets in the staging lane.
BEGIN
IF NOT(
( @minDelta = -1 )
AND
( @diffBetweenFoundAndExpSeq
AND
( @maxDelta = 0 )
)
-- We have to check if there is other transportOrder with lower dockloadquenumber
BEGIN
IF EXISTS (
SELECT TOR.Id
FROM [WMS.Handling].TransportOr
WHERE TOR.ShipmentNr = @shipmentNr
AND TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingL
AND TOR.Id_TransportOrderStatu
)
BEGIN
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SET @NoPosFoundBecauseOfLogica
SET @ResultCode = 0;
SET @ResultMsg = 'We wait other transportOrder because we have some pallets in staging.';
Goto FinalSteps;
END
END
-- If we arrive here it means we can continue.
END
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Best StoragePosition found for transportOrder - Delta Seq not 0';
Goto BookingAlignmentAndExit;
END
END
-- This is an error condition due to:
-- - some storagePosition blocked or disabled.
-- - some storagePosition busy but expected free.
--
-- -> We continue to find an escape solution.
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
END
SET @FoundStoragePositionID = null
SET @FoundStorageLocationID = null
SET @FoundInternalLevel = null
SET @DepositHeight = null
SELECT TOP 1
@FoundStoragePositionID = SP.Id,
@FoundStorageLocationID = SP.Id_StorageLocation,
@FoundInternalLevel = 1
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHSL.Id_StorageLocation
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
WHERE ( SHSL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND SHSL.MINLOGICALORDER > @maxLogicalOrderInShipment
-- THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND (
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND (
(
SPI.Position = SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
SPI.StorageDisabled <> 0
)
)
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
)
--- The Position must be physically empty and not booked for stockunit not in stack.
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE supe.Id_StoragePosition = sp.Id
AND (
ISNULL(SUPE.Booking, 0 ) = 0
OR
(
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
ORDER BY SHSL.AssignmentUTCTimeStam
SP.Position ASC,
SP.Id
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
if ( ( @counter > 0)
and ( @FoundStoragePositionID is not null )
and ( @FoundStorageLocationID is not null )
and ( @FoundInternalLevel is not null ))
BEGIN
SET @DepositHeight = 0;
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 1;
SET @ResultMsg = 'Delayed StoragePosition found for transportOrder.';
Goto BookingAlignmentAndExit;
END
ELSE
BEGIN
SET @DepositHeight = NULL
SET @FoundInternalLevel = NULL
SET @FoundStorageLocationID = NULL
SET @FoundStoragePositionID = NULL
SET @NoPosFoundBecauseOfMissin
SET @ResultCode = 0;
SET @ResultMsg = 'We need other space in staging lane for delay booking purpose.';
Goto FinalSteps;
END
END -- End of (@stagingLaneType = 'BlockStorage')
BookingAlignmentAndExit:
IF ( NOT @FoundStoragePositionID IS NULL )
AND ( NOT @FoundStorageLocationID IS NULL )
AND ( NOT @FoundInternalLevel IS NULL )
AND ( NOT @DepositHeight IS NULL )
BEGIN
if ( @BookFoundPosition <> 0 )
BEGIN
INSERT INTO [WMS.Storage].StockUnitPos
SELECT TOP(1) 1, @DepositHeight, @IdStockUnit, @FoundStoragePositionID, @FoundInternalLevel, @CurrentTimeStamp
WHERE NOT EXISTS(
SELECT SUP.Id
FROM [WMS.Storage].StockUnitPos
WHERE SUP.Id_StockUnit = @IdStockUnit
AND SUP.Booking <> 0
AND SUP.Id_StoragePosition = @FoundStoragePositionID
)
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
IF ( @counter = 0)
BEGIN
UPDATE [WMS.Storage].StockUnitPos
SET Heigth = @DepositHeight,
InternalLevel = @FoundInternalLevel,
PositioningTime = @CurrentTimeStamp
WHERE StockUnitPosition.Id_Stock
AND StockUnitPosition.Booking <> 0
AND StockUnitPosition.Id_Stora
SELECT @counter = @@ROWCOUNT, @error = @@ERROR
END
IF ( @counter > 0 )
BEGIN
SET @ResultCode = @counter
END
ELSE
BEGIN
SET @ResultCode = -13;
SET @ResultMsg = 'Critical SW error during position reservation';
Raiserror( @ResultMsg, 16, 1)
Goto FinalSteps;
END
Goto FinalSteps;
END
ELSE
BEGIN
SET @ResultCode = 1
Goto FinalSteps;
END
END
ELSE
BEGIN
SET @FoundStoragePositionID = NULL
SET @FoundStorageLocationID = NULL
SET @FoundInternalLevel = NULL
SET @DepositHeight = NULL
SET @ResultCode = 0;
SET @ResultMsg = 'No Position found';
Goto FinalSteps;
END
FinalSteps:
--------------------- FINAL STEPS --------------------------
IF ( @preTranCount = 0 )
begin
if ( @@TRANCOUNT > 0) and (XACT_STATE() > 0)
BEGIN
COMMIT TRAN @actProcName
END;
if ( @@TRANCOUNT > 0) and (XACT_STATE() < 0)
BEGIN
ROLLBACK TRAN @actProcName;
SET @ResultCode = -1;
SET @ResultMsg = ''
END;
end;
END TRY
BEGIN CATCH
declare @Error_number int, @Error_severity int
declare @Error_procedure nvarchar(126), @Error_message nvarchar(2048)
declare @Error_line int, @Error_state int
declare @xstate smallint, @actTranCount integer
select @Error_number = ERROR_NUMBER(),
@Error_severity = ERROR_SEVERITY(),
@Error_state = ERROR_STATE(),
@Error_procedure = ERROR_PROCEDURE(),
@Error_line = ERROR_LINE(),
@Error_message = ERROR_MESSAGE(),
@xstate = XACT_STATE(),
@actTranCount = @@TRANCOUNT
SET @ResultCode = -1;
if (( @xstate = -1) AND ( @preTranCount = 0))
ROLLBACK TRAN @actProcName; -- ROLLBACK COMPLETELY TRANSACTION .
if (( @xstate = 1) AND ( @preTranCount = 0))
ROLLBACK TRAN @actProcName; -- ROLLBACK COMPLETELY TRANSACTION .
if (( @xstate = 1) AND ( @preTranCount > 0))
BEGIN
IF ( @savePointDefined <> 0 )
BEGIN
ROLLBACK TRAN @actProcName; -- ROLLBACK ONLY LOCAL SAVEPOINT ( IF DEFINED ).
END;
END;
EXECUTE [WMS.Common].[LogProcedure
raiserror ('%s: %d: %s', 16, 1, @Error_Procedure, @Error_number, @Error_Message) ;
SET @Resultmsg = SUBSTRING(@Error_message,1
END CATCH
RETURN @ResultCode;
END
Two points:
1. Please post code in a Style code. It is difficult to cut and paste when you don't do that.
2. I cannot find any compile error in that Stored Procedure. So you need to tell us, what type of error message you are getting:
A. When you try and CREATE/ALTER that Stored Procedure or
B. When you try and EXECUTE that Stored Procedure.
What exact error message do you get?
1. Please post code in a Style code. It is difficult to cut and paste when you don't do that.
2. I cannot find any compile error in that Stored Procedure. So you need to tell us, what type of error message you are getting:
A. When you try and CREATE/ALTER that Stored Procedure or
B. When you try and EXECUTE that Stored Procedure.
What exact error message do you get?
ASKER
I found several errors on exists with parenthesis before it.
Thank you
Thank you
ASKER
This is the new:
SELECT
SP.Id,
SP.Id_StorageLocation,
ISNULL( MAX( ISFP.InternalLevel ), 0 ) + 1,
SHGL.MinLogicalOrder,
SHGL.MaxLogicalOrder,
( sp.Position - 1 + SHGL.MinLogicalOrder - @actLogicalOrder ),
ISNULL( sp.clearence, sl.clearance)
FROM [WMS.Storage].StorageLocat
JOIN [WMS.Storage].StoragePosit
ON SP.Id_StorageLocation = SL.Id
JOIN [WMS.Shipping].ShipmentSta
ON SL.Id = SHGL.Id_StorageLocation
LEFT JOIN @InSequencePosForProcessed
ON SP.Id = ISFP.IdStoragePosition
LEFT OUTER JOIN @StorageLocationToExclude SLTE
ON SL.Id = SLTE.Id_Location
LEFT OUTER JOIN @OutOfSequencePosForProces
ON SP.Id = OSFP.IdStoragePosition
WHERE ( SHGL.Id_Shipment = @idShipment)
AND SLTE.Id_Location IS NULL
AND OSFP.IdStoragePosition IS NULL
AND SHGL.MinLogicalOrder <= @actLogicalOrder
AND SHGL.MaxLogicalOrder >= @actLogicalOrder
--AND ( SP.Position = ( @actLogicalOrder - SHGL.MinLogicalOrder + 1 ) )
AND ISNULL(SL.StorageDisabled_
AND ISNULL(SP.StorageBlocked,0
AND ISNULL(SP.StorageDisabled,
AND
NOT EXISTS (
SELECT SUPI.ID
FROM [WMS.Storage].StoragePosit
LEFT JOIN [WMS.Storage].StockUnitPos
ON SUPI.Id_StoragePosition = SPI.Id
WHERE SPI.Id_StorageLocation = SP.Id_StorageLocation
AND ((SPI.Position = SP.Position AND (SPI.StorageBlocked <> 0 OR SPI.StorageDisabled <> 0))
OR
(
SPI.Position > SP.Position
AND
(
SPI.StorageBlocked <> 0
OR
( ( NOT SUPI.Id IS NULL ) AND ISNULL( SUPI.BOOKING, 0) = 0 )
)
)
)
)
--- The Position must be physically empty or booked for stockunit in stack. ---
AND NOT EXISTS (
SELECT SUPE.ID
FROM [WMS.Storage].StockUnitPos
LEFT JOIN @StockUnitsInStack SUS
ON SUPE.Id_StockUnit = SUS.IdStockUnit
WHERE SUPE.Id_StoragePosition = sp.Id
AND (
ISNULL( SUPE.Booking, 0) = 0
OR
(
(
SL.Id <> @IdStorageLocationToWhereL
OR
(
(
SL.Id = @IdStorageLocationToWhereL
OR
@IdStorageLocationToWhereL
)
AND
ISNULL( @ConsiderOnlyPhysicallyBus
)
)
AND
SUPE.Booking <> 0
AND
SUS.IdStockUnit IS NULL
)
)
)
AND (
@stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
OR
ISNULL( @CheckClearence, 0 ) = 0
)
GROUP BY SP.Id, SP.Id_StorageLocation, SHGL.MinLogicalOrder, SHGL.MaxLogicalOrder, SP.Position, SP.Clearence, SL.Clearance
ORDER BY SP.Position ASC