iVend Database Database : TrxInventoryUpdateCheckNegativeInventory Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

VarChar

1

@pTransactionKey

In

Reference key of the Transaction

VarWChar

50

@pSiteId

In

Reference key of the Site

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

InvBatch

Table

Batch details gets affected when doing any transaction with item which has been batch tracked.

1

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtGiftCertificate

Table

Used to store the values for the Gift Certificate issued

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdateCheckNegativeInventory]

(

@pDebug     CHAR(1),

@pTransactionKey NVARCHAR(50),

@pSiteId   INT

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255),

  @lAllowNegativeInventory BIT,

  @lWarehouseKey NVARCHAR(50),

  @lProductList NVARCHAR(3000),

  @lStoreKey NVARCHAR(50)

--Select  @lAllowNegativeInventory = AllowNegativeInventory From CfgEnterprise

Select @lWarehouseKey = WarehouseKey,

  @lStoreKey = StoreKey

From RtlStore Where SiteId = @pSiteId AND IsDeleted = 0

Select @lAllowNegativeInventory = AllowNegativeInventory From InvWarehouse Where WarehouseKey = @lWarehouseKey

DECLARE @DuplicateGC NVARCHAR(50)

SELECT TOP 1 @DuplicateGC = SerialNumber

FROM PmtGiftCertificate Where TransactionKey = @pTransactionKey

GROUP BY ProductKey , SerialNumber

HAVING COUNT(ProductKey) > 1

IF ISNULL(@DuplicateGC,'') != ''

BEGIN

SET @ErrorDesc = 'Same serial number used more than once for GC: ' + @DuplicateGC

GOTO ERRORHANDLER

END

SELECT @lProductList = COALESCE(@lProductList + ', ', '') + product.Id + ':' + batch.BatchNumber

FROM InvProduct product, InvBatch batch

Where product.ProductKey = batch.ProductKey

AND product.IsBatchTracked = 'TRUE'

AND batch.BatchKey In

(

Select Distinct A.ProductDetailKey

From TrxTransactionSaleItem A, InvProduct B

Where A.ProductKey = B.ProductKey

AND  B.IsBatchTracked = 'TRUE'

AND  A.ProductDetailKey != '0'

And  TransactionKey = @pTransactionKey

AND  A.Type in (0, 3, 4, 5)

UNION

SELECT Distinct i.ProductDetailKey

FROM TrxTransaction h, TrxTransactionLayaway i, InvProduct p

WHERE h.TransactionKey = i.TransactionKey

AND  i.ProductKey = p.ProductKey

AND  h.TransactionKey = @pTransactionKey

And  p.IsBatchTracked = 'TRUE'

AND  i.ProductDetailKey != '0'

AND  i.Type   = 0     -- [0] CREATION OF LAYAWAY

AND  i.InventoryAllocationMethod = 0     -- [0] Sale allocation method

)

And batch.AvailableQuantity < 0

IF (LEN(@lProductList) > 0)

BEGIN

SET @ErrorDesc = 'Not enough inventory in warehouse for batches ' + @lProductList

GOTO ERRORHANDLER

END

If @lAllowNegativeInventory = 'TRUE'

BEGIN

  RETURN

END

ELSE

BEGIN

  Select Distinct A.ProductKey, CONVERT(NVARCHAR(50), ISNULL(A.DeliveryLocationKey,'0')) As LocationKey

  INTO #TempProductList

  From TrxTransactionSaleItem A, InvProduct B

  Where A.ProductKey = B.ProductKey

  And B.IsAssembly = 'FALSE'

  And B.IsNonStock = 'FALSE'

  And TransactionKey = @pTransactionKey

  AND A.Type   = 0

  And isnull(A.IsDeliveryPackage,0) = 0

  AND A.DeliveryWarehouseKey IN ('0', @lWarehouseKey) --THIS WILL ENSURE THAT I TAKE CARE OF ONLY THOSE PRODUCT WHICH ARE DELIVERED FROM MY CURRENT STORE

  INSERT INTO #TempProductList

  Select Distinct A.ProductKey, CONVERT(NVARCHAR(50), ISNULL(A.DeliveryLocationKey,'0')) As LocationKey

  From TrxTransactionSaleItem A, InvProduct B

  Where A.ProductKey = B.ProductKey

  And B.IsAssembly = 'FALSE'

  And B.IsNonStock = 'FALSE'

  And isnull(A.IsDeliveryPackage,0) = 0

  And TransactionKey = @pTransactionKey

  AND  A.Type in (3, 4, 5)

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

    as

    (

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

      CONVERT(DECIMAL(20,5), Assembly.Quantity * Detail.Quantity), CONVERT(NVARCHAR(50), ISNULL(Detail.DeliveryLocationKey,'0'))

      FROM     InvAssemblyComponent Assembly, TrxTransaction Sale, TrxTransactionSaleItem Detail,

      InvProduct Product, InvInventoryItem Item

      Where Assembly.ParentProductKey   = Detail.ProductKey

    AND  Detail.ProductKey     = Product.ProductKey

    AND  Sale.TransactionKey     = Detail.TransactionKey

    AND  Item.WarehouseKey     = Detail.WarehouseKey

    AND  Assembly.ProductKey     = Item.ProductKey

    AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

    AND  Detail.Type       = 0         --INDICATES THAT THIS IS SALE ITEM

    And isnull(Detail.IsDeliveryPackage,0) = 0

    AND  Sale.TransactionKey     = @pTransactionKey

    AND  Detail.DeliveryWarehouseKey IN   ('0', @lWarehouseKey)       --THIS WILL ENSURE THAT I TAKE CARE OF ONLY THOSE PRODUCT WHICH ARE DELIVERED FROM MY CURRENT STORE

      UNION ALL

      SELECT R1.AssemblyComponentKey,

       R1.ParentProductKey,

       R1.ProductKey,

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

       , LocationKey

    FROM InvAssemblyComponent AS R1

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

     )

  INSERT INTO #TempProductList

  SELECT B.ProductKey, LocationKey

  FROM RecursionCTE A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

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

    as

    (

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

      CONVERT(DECIMAL(20,5), Assembly.Quantity * Detail.Quantity), CONVERT(NVARCHAR(50), ISNULL(Detail.DeliveryLocationKey,'0'))

      FROM     InvAssemblyComponent Assembly, TrxTransaction Sale, TrxTransactionSaleItem Detail,

      InvProduct Product, InvInventoryItem Item

      Where Assembly.ParentProductKey   = Detail.ProductKey

    AND  Detail.ProductKey     = Product.ProductKey

    AND  Sale.TransactionKey     = Detail.TransactionKey

    AND  Item.WarehouseKey     = Detail.WarehouseKey

    AND  Assembly.ProductKey     = Item.ProductKey

    AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

    AND  Detail.Type       IN (3, 4, 5)       --INDICATES THAT THIS IS DELIVERY

    AND isnull(Detail.IsDeliveryPackage,0) = 0

    AND  Sale.TransactionKey     = @pTransactionKey

      UNION ALL

      SELECT R1.AssemblyComponentKey,

       R1.ParentProductKey,

       R1.ProductKey,

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

       , LocationKey

    FROM InvAssemblyComponent AS R1

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

     )

  INSERT INTO #TempProductList

  SELECT B.ProductKey, LocationKey

  FROM RecursionCTE A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

  INSERT INTO #TempProductList

  SELECT Distinct p.ProductKey, i.DeliveryLocationKey

  FROM TrxTransaction h, TrxTransactionLayaway i, InvProduct p

  WHERE h.TransactionKey = i.TransactionKey

  AND  i.ProductKey = p.ProductKey

  AND  h.TransactionKey = @pTransactionKey

  And  p.IsAssembly = 'FALSE'

  AND  p.IsNonStock = 'FALSE'

  AND  i.Type   = 0     -- [0] CREATION OF LAYAWAY

  AND  i.InventoryAllocationMethod = 0     -- [0] Sale allocation method

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

    as

    (

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

      CONVERT(DECIMAL(20,5), Assembly.Quantity * Detail.Quantity), Detail.InventoryAllocationMethod, CONVERT(NVARCHAR(50), ISNULL(Detail.DeliveryLocationKey,'0'))

      FROM     InvAssemblyComponent Assembly, TrxTransaction Sale, TrxTransactionLayaway Detail,

      InvProduct Product, InvInventoryItem Item

      Where Assembly.ParentProductKey   = Detail.ProductKey

    AND  Detail.ProductKey     = Product.ProductKey

    AND  Sale.TransactionKey     = Detail.TransactionKey

    AND  Item.WarehouseKey     = Detail.WarehouseKey

    AND  Assembly.ProductKey     = Item.ProductKey

    AND  Product.IsAssembly     = 'TRUE'         --INDICATES THAT THE PARENT PRODUCT IS ASSEMBLY ITEM

    AND  Detail.Type       = 0         -- [0] CREATION OF LAYAWAY

    AND  Sale.TransactionKey     = @pTransactionKey

    AND  Detail.DeliveryWarehouseKey IN   ('0', @lWarehouseKey)       --THIS WILL ENSURE THAT I TAKE CARE OF ONLY THOSE PRODUCT WHICH ARE DELIVERED FROM MY CURRENT STORE

      UNION ALL

      SELECT R1.AssemblyComponentKey,

       R1.ParentProductKey,

       R1.ProductKey,

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

       R2.InventoryAllocationMethod

       , LocationKey

    FROM InvAssemblyComponent AS R1

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

     )

  INSERT INTO #TempProductList

  SELECT B.ProductKey, LocationKey

  FROM RecursionCTE A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  AND  B.IsAssembly = 'FALSE'

  AND  B.IsNonStock = 'FALSE'

  AND  A.InventoryAllocationMethod = 0             -- [0] Sale allocation method

  --CHECK FOR THE WAREHOUSELEVEL

  SELECT @lProductList = COALESCE(@lProductList + ', ', '') + T1.Id

  FROM InvInventoryItem T0, InvProduct T1,

  ( Select Distinct ProductKey, LocationKey

  From #TempProductList

  ) T2

  Where T0.ProductKey = T1.ProductKey

  And  T0.ProductKey = T2.ProductKey

  And  T0.AvailableQuantity < 0

  And  T0.WarehouseKey = @lWarehouseKey

  IF (LEN(@lProductList) > 0)

  BEGIN

  SET @ErrorDesc = 'Not enough inventory in warehouse for: ' + @lProductList

  GOTO ERRORHANDLER

  END

  SELECT @lProductList = COALESCE(@lProductList + ', ', '') + T1.Id

  FROM InvInventoryLocation T0, InvProduct T1,

  ( Select Distinct ProductKey, LocationKey

  From #TempProductList

  ) T2

  Where T0.ProductKey = T1.ProductKey

  And  T0.ProductKey = T2.ProductKey

  And  T0.AvailableQuantity < 0

  And  T0.WarehouseKey = @lWarehouseKey

  AND  T0.LocationKey = T2.LocationKey

  AND  T2.LocationKey != '0'

  IF (LEN(@lProductList) > 0)

  BEGIN

  SET @ErrorDesc = 'Not enough inventory in location for: ' + @lProductList

  GOTO ERRORHANDLER

  END

END

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN

END