iVend Database Database : UpdateDeliveryInformationFromSAPBusinessOne Stored Procedure

Creation Date

6/28/2017 11:56 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@WarehouseId

In

VarWChar

20

@ProductId

In

VarWChar

50

@Quantity

In

Numeric

9

@SourceType

In

VarWChar

20

@SourceKey

In

VarWChar

20

@SourceDetailKey

In

VarWChar

20

@UpdateType

In

VarChar

20

@DeliveryType

In

Integer

4

@OrderDetailKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

CREATE PROCEDURE [dbo].[UpdateDeliveryInformationFromSAPBusinessOne]

(

  @WarehouseId nvarchar(20),

  @ProductId nvarchar(50),

  @Quantity decimal(20,5)

, @SourceType nvarchar(20)

, @SourceKey nvarchar(20)

, @SourceDetailKey nvarchar(20)

, @UpdateType varchar(20)

, @DeliveryType INT

, @OrderDetailKey nvarchar(50)

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @ReplicationKey nvarchar(50)

Declare @InventoryItemLogKey nvarchar(50) ,

@IsAssembly bit,

@productKey nvarchar(50)

, @warehouseKey nvarchar(50)

, @UpdateQuantity bit

, @UpdateFulfillmentQuanity BIT

, @UpdateReservedQuanity decimal(20,5)

, @rowNumber int

Select @IsAssembly = IsAssembly, @productKey = ProductKey from InvProduct Where AccountingID = @ProductId And IsDeleted = 0

Select @warehouseKey = WarehouseKey from InvWarehouse Where AccountingID = @WarehouseId And IsDeleted = 0

Create table #AssemblyComponents

(

  RowNumber int,

  AssemblyComponentKey nvarchar(50)

 , ParentProductKey nvarchar(50),

   ProductKey nvarchar(50)

 , Quantity Decimal(20,5)

 )

IF ISNULL(@IsAssembly,0) = 0

    BEGIN

  insert into #AssemblyComponents

  SELECT 1, 0 AssemblyComponentKey, 0 ParentProductKey, @productKey As ProductKey, @Quantity

    END

ELSE

  begin

 ;WITH RecursionCTE (AssemblyComponentKey,ParentProductKey,ProductKey, Quantity)

  AS

    (

  SELECT   Assembly.AssemblyComponentKey, Assembly.ParentProductKey, Assembly.ProductKey,

  CONVERT(DECIMAL(20,5), Assembly.Quantity * @Quantity)

  FROM     InvAssemblyComponent Assembly With(nolock) , InvProduct Product With(nolock)

  Where     Assembly.ParentProductKey   = Product.ProductKey

  AND  Product.IsAssembly     = 'TRUE'

  And  Product.ProductKey = @productKey

  UNION ALL

  SELECT R1.AssemblyComponentKey,

       R1.ParentProductKey,

       R1.ProductKey,

      CONVERT(DECIMAL(20,5), R1.Quantity * R2.Quantity)

  FROM InvAssemblyComponent AS R1 With(nolock)

  JOIN RecursionCTE AS R2 ON R1.ParentProductKey = R2.ProductKey

   )

  insert into #AssemblyComponents

  SELECT ROW_NUMBER() OVER (ORDER BY A.ProductKey) as RowNumber, A.*

  FROM RecursionCTE A, InvProduct B With(nolock)

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

END

Set @UpdateQuantity = 0

IF (@DeliveryType = 2) --if the delivery is done against AR Reserve Invoice

  begin

  set @UpdateFulfillmentQuanity = 1

  set @UpdateReservedQuanity = 1

  set @UpdateQuantity = 1

  set @UpdateType = 0

  end

  else IF (@DeliveryType = 3) --if the delivery is done against layaway

  begin

  set @UpdateFulfillmentQuanity = 1

  set @UpdateReservedQuanity = 1

  set @UpdateQuantity = 0

  set @UpdateType = 0

  end

  else IF (@DeliveryType = 0) --if the delivery is done against order

  begin

  set @UpdateFulfillmentQuanity = 1

  set @UpdateReservedQuanity = 0

  set @UpdateQuantity = 0

  set @UpdateType = 0

  end

else IF (@DeliveryType = 1) --if the ar invoice is created against order

  begin

  set @UpdateFulfillmentQuanity = 1

  set @UpdateQuantity = 0

  set @UpdateType = 0

  end

While EXISTS (Select 1 from #AssemblyComponents)

  BEGIN

  SELECT TOP 1 @rowNumber = RowNumber From #AssemblyComponents

  SELECT @InventoryItemLogKey = NEWID()

  INSERT INTO InvInventoryItemLog (InventoryItemLogKey, WarehouseKey, ProductKey, InQty, OutQty, SourceType, SourceKey, SourceDetailKey, UpdateType, Created, OnFulfillmentQuanity, ReservedQuantity)

  SELECT @InventoryItemLogKey ,

      @WarehouseKey

       , ProductKey

       , 0

       , case when @UpdateQuantity = 1 then Quantity else 0 end

       , -1

       , 0

       , 0

       , @UpdateType

       , GetDate()

   , case when @UpdateFulfillmentQuanity = 1 then -1 * Quantity else 0 end

   , case when @UpdateReservedQuanity = 1 then -1 * Quantity else 0 end

  From #AssemblyComponents Where RowNumber = @rowNumber

  EXEC DataNotification @pSourceType = 172, @pSourceKey = @InventoryItemLogKey, @pBatchKey = NEWID, @pOperationType = 0, @pUserKey = '-1', @pDebug= 'false'

  EXEC InventoryItemQuantityUpdates @pInventoryItemLogKey = @InventoryItemLogKey

  Delete from #AssemblyComponents Where RowNumber = @rowNumber

  END

END