We help IT Professionals succeed at work.

How to have to AND NOT EXIST() in one statement?

378 Views
Last Modified: 2012-06-21
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,                                    
                              @minLogicalOrderInStagingLane = SHGL.MinLogicalOrder,
                              @maxLogicalOrderInStagingLane = SHGL.MaxLogicalOrder,
                              @diffBetweenFoundAndExpSeqNum =      ( sp.Position - 1 + SHGL.MinLogicalOrder - @actLogicalOrder ),
                              @tempClearance                              =      ISNULL( sp.clearence, sl.clearance)
                  FROM      [WMS.Storage].StorageLocation SL       
                  JOIN      [WMS.Storage].StoragePosition SP
                  ON            SP.Id_StorageLocation = SL.Id                                                                  
                  JOIN      [WMS.Shipping].ShipmentStagingLane SHGL
                  ON            SL.Id = SHGL.Id_StorageLocation
                  LEFT JOIN      @InSequencePosForProcessedStockunits ISFP
                  ON            SP.Id = ISFP.IdStoragePosition
                  LEFT OUTER JOIN @StorageLocationToExclude SLTE
                  ON            SL.Id = SLTE.Id_Location
                  LEFT OUTER JOIN @OutOfSequencePosForProcessedStockunits OSFP
                  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_LGV,0)      =      0
                  AND            ISNULL(SP.StorageBlocked,0)                  =      0
                  AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
                  AND            (
                              NOT EXISTS (
                                    SELECT      SUPI.ID
                                    FROM      [WMS.Storage].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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].StockUnitPosition SUPE
                              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 <> @IdStorageLocationToWhereLGVInFront
                                                      OR
                                                      (
                                                            (
                                                            SL.Id = @IdStorageLocationToWhereLGVInFront
                                                            OR
                                                            @IdStorageLocationToWhereLGVInFront IS NULL
                                                            )
                                                            AND
                                                            ISNULL( @ConsiderOnlyPhysicallyBusyPositions , 0 ) = 0
                                                      )
                                                )                                          
                                                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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I changed it but I am getting the same message:  Incorrect syntax near the keyword 'AND':

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].StorageLocation SL       
                  JOIN      [WMS.Storage].StoragePosition SP
                  ON            SP.Id_StorageLocation = SL.Id                                                                  
                  JOIN      [WMS.Shipping].ShipmentStagingLane SHGL
                  ON            SL.Id = SHGL.Id_StorageLocation
                  LEFT JOIN      @InSequencePosForProcessedStockunits ISFP
                  ON            SP.Id = ISFP.IdStoragePosition
                  LEFT OUTER JOIN @StorageLocationToExclude SLTE
                  ON            SL.Id = SLTE.Id_Location
                  LEFT OUTER JOIN @OutOfSequencePosForProcessedStockunits OSFP
                  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_LGV,0)      =      0
                  AND            ISNULL(SP.StorageBlocked,0)                  =      0
                  AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
                  AND            
                              NOT EXISTS (
                                    SELECT      SUPI.ID
                                    FROM      [WMS.Storage].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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].StockUnitPosition SUPE
                              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 <> @IdStorageLocationToWhereLGVInFront
                                                      OR
                                                      (
                                                            (
                                                            SL.Id = @IdStorageLocationToWhereLGVInFront
                                                            OR
                                                            @IdStorageLocationToWhereLGVInFront IS NULL
                                                            )
                                                            AND
                                                            ISNULL( @ConsiderOnlyPhysicallyBusyPositions , 0 ) = 0
                                                      )
                                                )                                          
                                                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
CERTIFIED EXPERT
Top Expert 2012

Commented:
Your code is fine.  What error message/problem are you having?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
I am still not getting any compile error.  Can you please post all the relevant code including all DECLAREs

Author

Commented:
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].[LookForPositionS_StagingLane]    Script Date: 02/08/2012 13:47:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WMS.Storage].[LookForPositionS_StagingLane]
      @IdStockUnit                                          bigint                  =      null,
      @ActualLevel                                          int                        =      0,
      @IdTransportOrder                                    bigint                  =      null,
      @FoundPosForProcessedStockunits                  [WMS_Kraft].[StockUnitStackInfo] readonly,
      @StockUnitsInStack                                    [WMS_Kraft].[StockUnitStackInfo] readonly,
      @IdStorageLocationFrom                              bigint                  =      NULL,            
      @IdStorageLocationToWhereLGVInFront            bigint                  =      null,            
      @ExcludeStorageLocationFrom                        bit                        =      1,
      @StockUnitsOnLGV                                    bit                        =      0,            
      @ConsiderOnlyPhysicallyBusyPositions      bit                        =      1,            
      @BookFoundPosition                                    bit                        =      1,      
      @CheckLTOBlockStorageBlocking                  bit                        =      1,
      @CheckClearence                                          bit                        =      1,            
      @ForceEmptyStoragePositionResearch            bit                        =      0,
      --------      OTHER INPUT PARAMETERS.
      @EnableSavePoint                                    bit                        =      1,
      --------      OUTPUT PARAMETERS      ---------------            
      @FoundStoragePositionID                              bigint                  =      null      output,
      @FoundStorageLocationID                              bigint                  =      null      output,      
      @FoundInternalLevel                                    int                        =      null      output,
      @DepositHeight                                          float                  =      null      output,
      @NoPosFoundBecauseOfMissingStagingLane      bit                        =      null      output,
      @NoPosFoundBecauseOfLogicalOrderSeq            bit                        =      null      output,
      --------      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 @prevStockUnitAlreadyStaged      bit            =      0            
      DECLARE @idPrevStorageLocation            bigint      =      null
      DECLARE @InSequencePosForProcessedStockunits                  [WMS_Kraft].[StockUnitStackInfo]
      DECLARE @OutOfSequencePosForProcessedStockunits                  [WMS_Kraft].[StockUnitStackInfo]
      DECLARE @shipmentNr                              nvarchar(50)      =      null
      DECLARE @prevAssignmentUTCTimeStamp      DATETIME      =      NULL
      DECLARE @tempClearance                        float      =      null
      DECLARE @tempI                                    int            =      null
      DECLARE @tempStackHeight                  float      =      null
      DECLARE @maxLogicalOrderInShipment      int      =      0      
      DECLARE @minLogicalOrderInStagingLane      int      =      0
      DECLARE @maxLogicalOrderInStagingLane      int      =      0
      DECLARE @diffBetweenFoundAndExpSeqNum      INT      =      NULL
      
      DECLARE @StorageLocationToExclude table
      (            
            Id_Location      bigint
      )
      
      IF ( @ExcludeStorageLocationFrom <> 0 )
            AND ( NOT @IdStorageLocationFrom IS NULL  )
      BEGIN            
            INSERT INTO @StorageLocationToExclude ( Id_Location )
            VALUES ( @IdStorageLocationFrom )
      END      
      
      IF ( @CheckLTOBlockStorageBlocking <> 0 )
      BEGIN
            INSERT INTO @StorageLocationToExclude( Id_Location )
            SELECT      BSSL.Id_StorageLocation
            FROM      WMS_Kraft.GetBlockStorageStagingLanesUnavailableDueToLTO() BSSL
      END
      
      -- We reset the output parameter.
      SET @FoundStoragePositionID                              =      null
      SET @FoundStorageLocationID                              =      null            
      SET @FoundInternalLevel                                    =      null      
      SET @DepositHeight                                          =      null      
      SET @NoPosFoundBecauseOfMissingStagingLane      =      null      
      SET @NoPosFoundBecauseOfLogicalOrderSeq            =      null      
      
      
      -- 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.strictSequencingRequested,
                              @dynamicStagingOptionON            =      SH.DynamicStagingOption,
                              @hotLoadStagingOptionON            =      SH.HotLoadStagingOption,
                              @actLogicalOrder                  =      TOR.LogicalOrder,
                              @actDockLoadSeq                        =      TOR.E80_DockLoadSeqNum,
                              @actTierNumber                        =      TOR.TierNumber      
      FROM      [WMS.Handling].TransportOrder TOR
      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].ApplicationSetting A
      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 = MAX( TOR.LogicalOrder )
      FROM      [WMS.Handling].TransportOrder TOR
      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].DockStagingLane t2 on t2.Id_Dock = t1.Id_Dock
      inner join [WMS.Storage].StorageLocation t3 on t3.Id = t2.Id_StagingLane
      inner join [WMS.Storage].StorageLocationType t4 on t4.Id = t3.Id_LocationType
      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 @NoPosFoundBecauseOfLogicalOrderSeq = 'false'
            set @NoPosFoundBecauseOfMissingStagingLane = 'false'
            
            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 @NoPosFoundBecauseOfMissingStagingLane      =      1;
                  SET @ResultCode = 0;
                  SET @ResultMsg = 'We need more space in staging lanes based on MaxLogicalOrder of Shipment';
                  Goto FinalSteps;            
            END
            
            
            DELETE FROM @InSequencePosForProcessedStockunits
            DELETE FROM @OutOfSequencePosForProcessedStockunits
            
            INSERT INTO @InSequencePosForProcessedStockunits
            SELECT      FPS.*
            FROM      @FoundPosForProcessedStockunits FPS
            JOIN      [WMS.Handling].TransportOrder TOR
            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 @OutOfSequencePosForProcessedStockunits by difference.
            INSERT INTO @OutOfSequencePosForProcessedStockunits
            SELECT      FPS.*
            FROM      @FoundPosForProcessedStockunits FPS
            LEFT OUTER JOIN      @InSequencePosForProcessedStockunits ISFP
            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 @prevStockUnitAlreadyStaged = NULL
            SET @prevAssignmentUTCTimeStamp      =      NULL
                              
            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,
                        @prevStockUnitAlreadyStaged      =      CASE
                                                                        WHEN SUP.Booking <> 0 THEN 0
                                                                        ELSE 1
                                                                        END,
                        @prevAssignmentUTCTimeStamp      =      SSGL.AssignmentUTCTimeStamp
            FROM      [WMS.Handling].TransportOrder TOR      
            LEFT JOIN [WMS.Handling].StockUnitAllocation SUA
            ON            SUA.Id_TransportOrder = TOR.Id
            LEFT JOIN [WMS.Storage].StockUnitPosition SUP
            ON            (
                        TOR.Id_ConnectedStockUnit = SUP.ID_STOCKUNIT
                        OR
                        SUA.Id_StockUnit = SUP.Id_StockUnit
                  )
            LEFT JOIN      [WMS.Storage].StoragePosition SP
            ON            SUP.Id_StoragePosition = SP.ID                              
            LEFT JOIN      [WMS.Shipping].ShipmentStagingLane SSGL
            ON            SP.Id_StorageLocation = SSGL.Id_StorageLocation
            WHERE      TOR.Id_TransportOrderType IN ( SELECT TOT.ID FROM [WMS.Handling].TransportOrderType TOT WHERE TOT.Name = 'RET')
            AND            TOR.Id_TransportOrderStatus NOT IN ( SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ('Aborted', 'Deleted'))
            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].TransportOrder TOR
                  WHERE      TOR.ShipmentNr = @shipmentNr
                  AND            TOR.E80_DockLoadSeqNum = @actDockLoadSeq
                  AND            TOR.Id_TransportOrderStatus NOT IN (SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ('Closed', 'Aborted', 'Deleted'))
                  AND            (
                                    (      @counter = 0
                                          AND
                                          TOR.TierNumber < @actTierNumber
                                    )
                                    OR
                                    (
                                          @counter > 0
                                          AND
                                          TOR.TierNumber > @prevTierNumber
                                          AND
                                          TOR.TierNumber < @actTierNumber
                                    )      
                              )                        
                  )                                                                  
                  --
                  OR      (                        
                              ( @actDockLoadSeq = @prevDockLoadSeq )
                              AND
                              ( @ConsiderOnlyPhysicallyBusyPositions <> 0 )
                              AND
                              ( @prevStockUnitAlreadyStaged = 0 )
                              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 @NoPosFoundBecauseOfLogicalOrderSeq      =      1
                  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 @NoPosFoundBecauseOfLogicalOrderSeq      =      0;                              
                  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 ( @ConsiderOnlyPhysicallyBusyPositions <> 0 )
                  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].StorageLocation SL       
                        JOIN      [WMS.Storage].StoragePosition SP
                        ON            SP.Id_StorageLocation = SL.Id      
                        LEFT JOIN      @InSequencePosForProcessedStockunits ISFP
                        ON            SP.Id = ISFP.IdStoragePosition                        
                        LEFT JOIN      [WMS.Storage].StockUnitPosition SUP
                        ON            SP.ID = SUP.Id_StoragePosition
                        AND            (
                                    ISNULL( SUP.BOOKING, 0 ) = 0
                                    OR
                                    (
                                    -- Even if we have the @ConsiderOnlyPhysicallyBusyPositions flag
                                    -- 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      @OutOfSequencePosForProcessedStockunits OSFP
                                    WHERE      OSFP.IdStoragePosition = SP.Id
                              )
                        --            THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
                        AND            ISNULL(SL.StorageDisabled_LGV,0)      =      0
                        AND            ISNULL(SP.StorageBlocked,0)                  =      0
                        AND            ISNULL(SP.StorageDisabled,0)            =      0                                          
                        --            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].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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
                  -- @ConsiderOnlyPhysicallyBusyPositions = 0
                  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].StorageLocation SL       
                        JOIN      [WMS.Storage].StoragePosition SP
                        ON            SP.Id_StorageLocation = SL.Id                                                                              
                        LEFT JOIN      [WMS.Storage].StockUnitPosition SUP
                        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      @OutOfSequencePosForProcessedStockunits OSFP
                                    WHERE      OSFP.IdStoragePosition = SP.Id
                              )
                        --            THE DESTINATION POSITION MUST NOT BE BLOCKED OR DISABLED FOR STORAGING.
                        AND            ISNULL(SL.StorageDisabled_LGV,0)      =      0
                        AND            ISNULL(SP.StorageBlocked,0)                  =      0
                        AND            ISNULL(SP.StorageDisabled,0)            =      0                                          
                        --            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].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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.GetStackOfPalletActualHeightInStoragePosition2( @idPrevStoragePosition, @idStockUnit, (~@ConsiderOnlyPhysicallyBusyPositions), @stockunitsInStack, @defaultSKUHeight, @defaultLoadAidHeight );
                        
                        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].StorageLocation SL       
                  JOIN      [WMS.Storage].StoragePosition SP
                  ON            SP.Id_StorageLocation = SL.Id                                                                  
                  JOIN      [WMS.Shipping].ShipmentStagingLane SGL
                  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_LGV,0)      =      0
                  AND            ISNULL(SP.StorageBlocked,0)                  =      0
                  AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
                  AND            (
                              NOT EXISTS (
                                    SELECT      SUPI.ID
                                    FROM      [WMS.Storage].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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].StockUnitPosition SUPE
                              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( @ConsiderOnlyPhysicallyBusyPositions, 0 ) = 0
                                          )
                                    )
                        )
                  AND      (
                              @stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
                              OR
                              ISNULL( @CheckClearence, 0 ) = 0
                        )
                  ORDER BY      SGL.AssignmentUTCTimeStamp ASC,
                                    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 @NoPosFoundBecauseOfMissingStagingLane = 0;
                        
                        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 @NoPosFoundBecauseOfMissingStagingLane = 1;
                        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 BIT = 0
            
            IF EXISTS (
                  SELECT      TOR.Id
                  FROM      [WMS.Handling].TransportOrder TOR
                  WHERE      TOR.ShipmentNr = @shipmentNr
                  AND            TOR.LogicalOrder = @actLogicalOrder
                  AND            TOR.Id <> @IdTransportOrder
                  AND            TOR.Id_TransportOrderStatus NOT IN (SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ('Closed', 'Aborted', 'Deleted') )
                  AND            TOR.ClonedFromTransportOrder IS NULL
            )
            BEGIN
                  SET @firstPalletOfAMovingStack = 1;
            END
            
            
            IF ( @strictSequencingRequest <> 0 )
            BEGIN
                  -- SET @firstPositionInStagingLane      =      0            
                  SET @maxLogicalOrderInStagingLane = NULL
                  SET @minLogicalOrderInStagingLane = NULL            
                  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,                              
                              @minLogicalOrderInStagingLane      =      SHGL.MinLogicalOrder,
                              @maxLogicalOrderInStagingLane      =      SHGL.MaxLogicalOrder,
                              @tempClearance                              =      ISNULL( sp.clearence, sl.clearance)
                  FROM      [WMS.Storage].StorageLocation SL       
                  JOIN      [WMS.Storage].StoragePosition SP
                  ON            SP.Id_StorageLocation = SL.Id                                                                  
                  JOIN      [WMS.Shipping].ShipmentStagingLane SHGL
                  ON            SL.Id = SHGL.Id_StorageLocation
                  LEFT JOIN      @InSequencePosForProcessedStockunits ISFP
                  ON            SP.Id = ISFP.IdStoragePosition
                  LEFT OUTER JOIN @StorageLocationToExclude SLTE
                  ON            SL.Id = SLTE.Id_Location
                  LEFT OUTER JOIN @OutOfSequencePosForProcessedStockunits OSFP
                  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_LGV,0)      =      0
                  AND            ISNULL(SP.StorageBlocked,0)                  =      0
                  AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
                  AND            (
                              NOT EXISTS (
                                    SELECT      SUPI.ID
                                    FROM      [WMS.Storage].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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].StockUnitPosition SUPI                        
                              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 <> @IdStorageLocationToWhereLGVInFront )
                                                      OR
                                                      (                                                                                                            
                                                            (
                                                                  SP.Id_StorageLocation = @IdStorageLocationToWhereLGVInFront
                                                                  OR
                                                                  @IdStorageLocationToWhereLGVInFront IS NULL
                                                            )
                                                            AND
                                                            ISNULL( @ConsiderOnlyPhysicallyBusyPositions, 0 ) = 0
                                                      )
                                                )
                                                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].TransportOrder TOR
                                    WHERE      TOR.ShipmentNr = @shipmentNr
                                    AND            TOR.LogicalOrder >= @minLogicalOrderInStagingLane
                                    AND            TOR.LogicalOrder < @actLogicalOrder
                                    AND            TOR.Id_TransportOrderStatus NOT IN ( SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ( 'Closed', 'Aborted', 'Deleted' ) )
                              )
                        -- We have to wait due to logicalorder.
                        BEGIN
                              SET @FoundStoragePositionID                        =      null
                              SET @FoundStorageLocationID                        =      null            
                              SET @FoundInternalLevel                              =      null      
                              SET @DepositHeight                                    =      null                                    
                              SET @NoPosFoundBecauseOfLogicalOrderSeq      =      1
                              SET @ResultCode = 0;
                              SET @ResultMsg = 'We wait for other transportOrder with different DockLoadSeqNum';
                              Goto FinalSteps;
                        END            
                        
                        IF      ( ( WMS_KRAFT.GetStackOfPalletActualHeightInStoragePosition2( @FoundStoragePositionID, @idStockUnit, (~@ConsiderOnlyPhysicallyBusyPositions), @stockunitsInStack, @defaultSKUHeight, @defaultLoadAidHeight ) + @stockUnitHeight ) <= @tempClearance )
                              OR
                              ( ISNULL( @CheckClearence, 0 ) = 0 )
                        BEGIN
                                                      
                              SET @DepositHeight = 0;
                              SET @NoPosFoundBecauseOfMissingStagingLane = 0;
                        
                              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 @diffBetweenFoundAndExpSeqNum      =      NULL            
                  SET @minLogicalOrderInStagingLane      =      NULL            
                  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,                                    
                              @minLogicalOrderInStagingLane = SHGL.MinLogicalOrder,
                              @maxLogicalOrderInStagingLane = SHGL.MaxLogicalOrder,
                              @diffBetweenFoundAndExpSeqNum =      ( sp.Position - 1 + SHGL.MinLogicalOrder - @actLogicalOrder ),
                              @tempClearance                              =      ISNULL( sp.clearence, sl.clearance)
                  FROM      [WMS.Storage].StorageLocation SL       
                  JOIN      [WMS.Storage].StoragePosition SP
                  ON            SP.Id_StorageLocation = SL.Id                                                                  
                  JOIN      [WMS.Shipping].ShipmentStagingLane SHGL
                  ON            SL.Id = SHGL.Id_StorageLocation
                  LEFT JOIN      @InSequencePosForProcessedStockunits ISFP
                  ON            SP.Id = ISFP.IdStoragePosition
                  LEFT OUTER JOIN @StorageLocationToExclude SLTE
                  ON            SL.Id = SLTE.Id_Location
                  LEFT OUTER JOIN @OutOfSequencePosForProcessedStockunits OSFP
                  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_LGV,0)      =      0
                  AND            ISNULL(SP.StorageBlocked,0)                  =      0
                  AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
                  AND            (
                              NOT EXISTS (
                                    SELECT      SUPI.ID
                                    FROM      [WMS.Storage].StoragePosition  SPI
                                    LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                                    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].StockUnitPosition SUPE
                              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 <> @IdStorageLocationToWhereLGVInFront
                                                      OR
                                                      (
                                                            (
                                                            SL.Id = @IdStorageLocationToWhereLGVInFront
                                                            OR
                                                            @IdStorageLocationToWhereLGVInFront IS NULL
                                                            )
                                                            AND
                                                            ISNULL( @ConsiderOnlyPhysicallyBusyPositions , 0 ) = 0
                                                      )
                                                )                                          
                                                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].TransportOrder TOR
                                                JOIN      [WMS.Handling].TransportOrderStatus TOS
                                                ON            TOR.Id_TransportOrderStatus = TOS.Id
                                                WHERE      TOR.ShipmentNr = @shipmentNr
                                                AND            TOR.LogicalOrder BETWEEN @minLogicalOrderInStagingLane AND ( @actLogicalOrder - 1 )
                                                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 @NoPosFoundBecauseOfLogicalOrderSeq      =      1
                              SET @ResultCode = 0;
                              SET @ResultMsg = 'We wait for other transportOrder due to stack to complete.';
                              Goto FinalSteps;      
                        END
                  
                  
                        IF      ( ( WMS_KRAFT.GetStackOfPalletActualHeightInStoragePosition2( @FoundStoragePositionID, @idStockUnit, (~@ConsiderOnlyPhysicallyBusyPositions), @stockunitsInStack, @defaultSKUHeight, @defaultLoadAidHeight ) + @stockUnitHeight ) <= @tempClearance )
                              OR
                              ( ISNULL( @CheckClearence, 0 ) = 0 )
                        BEGIN
                  
                              -- We evaluate the status of @diffBetweenFoundAndExpSeqNum.
                              -- - 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 ( @diffBetweenFoundAndExpSeqNum = 0)
                              BEGIN
                                    -- We can go.
                                    SET @DepositHeight = 0;
                                    SET @NoPosFoundBecauseOfMissingStagingLane = 0;
                              
                                    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 + @minLogicalOrderInStagingLane - TOR.LogicalOrder ) AS Delta,
                                                COUNT( DISTINCT SUP.Id_StockUnit) AS NrPallet                                                                        
                                    FROM      [WMS.Handling].TransportOrder TOR
                                    JOIN      [WMS.Storage].StockUnitPosition SUP
                                    ON            TOR.Id_ConnectedStockUnit = SUP.Id_StockUnit
                                    JOIN      [WMS.Storage].StoragePosition SP
                                    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 @minLogicalOrderInStagingLane AND @maxLogicalOrderInShipment
                                    GROUP BY SP.Position, TOR.LogicalOrder ) AS StagingLaneInfo
                              
                              SET @countPalletStaged = ISNULL(@countPalletStaged, 0)
                              
                              IF ( @countPalletStaged = 0 )
                              BEGIN
                              
                                    IF      (
                                                ( @diffBetweenFoundAndExpSeqNum < 0 )      
                                                AND
                                                (
                                                      ( @diffBetweenFoundAndExpSeqNum < -1 )
                                                      OR
                                                      ( @firstPalletOfAMovingStack <> 0 )      
                                                )
                                          )
                                          OR
                                          ( @diffBetweenFoundAndExpSeqNum > 0 )
                                    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].TransportOrder TOR
                                                WHERE      TOR.ShipmentNr = @shipmentNr
                                                AND            TOR.LogicalOrder BETWEEN  @minLogicalOrderInStagingLane AND ( @actLogicalOrder - 1 )                                                
                                                AND            TOR.Id_TransportOrderStatus NOT IN (SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ('Closed', 'Aborted', 'Deleted') )
                                          )      
                                          BEGIN
                                                SET @FoundStoragePositionID                        =      null
                                                SET @FoundStorageLocationID                        =      null            
                                                SET @FoundInternalLevel                              =      null      
                                                SET @DepositHeight                                    =      null                                    
                                                SET @NoPosFoundBecauseOfLogicalOrderSeq      =      1
                                                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
                                          ( @diffBetweenFoundAndExpSeqNum = 1 )
                                          AND
                                          ( @maxDelta = 0 )
                                          )
                                    -- We have to check if there is other transportOrder with lower dockloadquenumber
                                    BEGIN
                                          IF EXISTS (
                                                SELECT      TOR.Id
                                                FROM      [WMS.Handling].TransportOrder TOR
                                                WHERE      TOR.ShipmentNr = @shipmentNr
                                                AND            TOR.LogicalOrder BETWEEN  @minLogicalOrderInStagingLane AND ( @actLogicalOrder - 1 )                                                
                                                AND            TOR.Id_TransportOrderStatus NOT IN (SELECT TOS.ID FROM [WMS.Handling].TransportOrderStatus TOS WHERE TOS.Name IN ('Closed', 'Aborted', 'Deleted') )
                                          )      
                                          BEGIN
                                                SET @FoundStoragePositionID                        =      null
                                                SET @FoundStorageLocationID                        =      null            
                                                SET @FoundInternalLevel                              =      null      
                                                SET @DepositHeight                                    =      null                                    
                                                SET @NoPosFoundBecauseOfLogicalOrderSeq      =      1
                                                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 @NoPosFoundBecauseOfMissingStagingLane = 0;
                              
                              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].StorageLocation SL       
            JOIN      [WMS.Storage].StoragePosition SP
            ON            SP.Id_StorageLocation = SL.Id                                                                  
            JOIN      [WMS.Shipping].ShipmentStagingLane SHSL
            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_LGV,0)      =      0
            AND            ISNULL(SP.StorageBlocked,0)                  =      0
            AND            ISNULL(SP.StorageDisabled,0)            =      0                                                      
            AND            (
                        NOT EXISTS (
                              SELECT      SUPI.ID
                              FROM      [WMS.Storage].StoragePosition  SPI
                              LEFT JOIN      [WMS.Storage].StockUnitPosition SUPI
                              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].StockUnitPosition SUPE
                        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( @ConsiderOnlyPhysicallyBusyPositions, 0 ) = 0
                                    )
                              )
                        )
            AND      (
                        @stockUnitHeight <= ISNULL( sp.clearence, sl.clearance)
                        OR
                        ISNULL( @CheckClearence, 0 ) = 0
                  )
            ORDER BY      SHSL.AssignmentUTCTimeStamp ASC,
                              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 @NoPosFoundBecauseOfMissingStagingLane = 0;
                  
                  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 @NoPosFoundBecauseOfMissingStagingLane = 1;
                  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].StockUnitPosition (Booking, Heigth, Id_StockUnit, Id_StoragePosition, InternalLevel, PositioningTime )
                  SELECT TOP(1)      1, @DepositHeight, @IdStockUnit, @FoundStoragePositionID, @FoundInternalLevel, @CurrentTimeStamp
                  WHERE  NOT EXISTS(
                        SELECT  SUP.Id
                        FROM      [WMS.Storage].StockUnitPosition SUP
                        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].StockUnitPosition
                        SET            Heigth                  =      @DepositHeight,
                                    InternalLevel      =      @FoundInternalLevel,
                                    PositioningTime      =      @CurrentTimeStamp
                        WHERE      StockUnitPosition.Id_StockUnit = @IdStockUnit
                        AND            StockUnitPosition.Booking <> 0
                        AND            StockUnitPosition.Id_StoragePosition = @FoundStoragePositionID
                        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].[LogProcedureError]
      
      raiserror ('%s: %d: %s', 16, 1, @Error_Procedure, @Error_number, @Error_Message) ;                               
      SET @Resultmsg = SUBSTRING(@Error_message,1,255)                  
END CATCH      

RETURN @ResultCode;

END
CERTIFIED EXPERT
Top Expert 2012

Commented:
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?

Author

Commented:
I found several errors on exists with parenthesis before it.

Thank you

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.