iVend Database Database : GetBOMQuantity Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductKey

In

VarWChar

50

@pWarehouseKey

In

VarWChar

50

@pLocationKey

In

VarWChar

50

@pQuantity

Out

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvAssemblyComponent

Table

Defines the Assembly set up at the console together with the parent as well as the associated products.

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvKitComponent

Table

Defines the Kit set up at the console together with the parent as well as the associated products.

1

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

-- =============================================

-- Author:  ABINESH AGARWAL

-- Create date: 11/25/2014

-- Description: GETS THE QUANTITY OF Assembly and KIT components

-- =============================================

CREATE PROCEDURE [dbo].[GetBOMQuantity]

-- Add the parameters for the stored procedure here

@pProductKey nvarchar(50),

@pWarehouseKey nvarchar(50),

@pLocationKey nvarchar(50),

@pQuantity INT OUTPUT

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @lIsLOcationEnabled BIT

SET @pQuantity = 0

SELECT @lIsLOcationEnabled = ISNULL(IsLocationEnabled,0) FROM InvWarehouse WHERE WarehouseKey = @pWarehouseKey

IF EXISTS(select 1 from InvProduct Where ProductKey = @pProductKey and IsKit = 1)

BEGIN

IF(@lIsLOcationEnabled = 0 AND @pLocationKey = 0)

BEGIN

  ;WITH RecursionCTE (KitComponentKey,ParentProductKey,ProductKey, Quantity, InventoryItemKey, AvailableQuantity)

  as

   (

    SELECT   A.KitComponentKey, A.ParentProductKey, A.ProductKey,

    CONVERT(DECIMAL(20,5), A.Quantity * 1),E.InventoryItemKey, Convert(Decimal(20,5),  E.AvailableQuantity) - Convert(Decimal(20,5),  E.ReservedQuantity) As AvailableQuantity

    FROM     InvKitComponent A, InvProduct D, InvInventoryItem E

    Where A.ParentProductKey = D.ProductKey

  --And  D.IsAssembly   = 'TRUE'  --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  E.WarehouseKey   = @pWarehouseKey

  AND  A.ProductKey   = E.ProductKey

  And  A.ParentProductKey     = @pProductKey

    UNION ALL

    SELECT R1.AssemblyComponentKey,

      R1.ParentProductKey,

      R1.ProductKey,

      convert(decimal(20,5), R1.Quantity * R2.Quantity),

      R3.InventoryItemKey,

      R3.AvailableQuantity - R3.ReservedQuantity

  FROM InvAssemblyComponent AS R1

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

  JOIN InvInventoryItem AS R3 ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = @pWarehouseKey

    )

  SELECT  A.ProductKey, B.ID, MIN(AvailableQuantity)/ SUM(QUANTITY) LeastQty INTO #ProductUOM

  FROM RecursionCTE A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  --And  B.IsKit = 'FALSE'

  And  B.IsNonStock = 'FALSE'

  group by a.ProductKey, B.ID

  SELECT @pQuantity = CONVERT(INT, MIN(LeastQty)) from #ProductUOM

END

ELSE IF(@lIsLOcationEnabled = 1 AND @pLocationKey != '0')

BEGIN

  ;WITH RecursionCTE (KitComponentKey,ParentProductKey,ProductKey, Quantity, InventoryLocationKey, AvailableQuantity)

  as

   (

    SELECT   A.KitComponentKey, A.ParentProductKey, A.ProductKey,

    CONVERT(DECIMAL(20,5), A.Quantity * 1), E.InventoryLocationKey, Convert(Decimal(20,5),  E.AvailableQuantity) As AvailableQuantity

    FROM     InvKitComponent A, InvProduct D, InvInventoryLocation E

    Where A.ParentProductKey = D.ProductKey

  --And  D.IsAssembly   = 'TRUE'  --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  E.WarehouseKey   = @pWarehouseKey

  AND  E.LocationKey   =   @pLocationKey

  AND  A.ProductKey   = E.ProductKey

  And  A.ParentProductKey     = @pProductKey

    UNION ALL

    SELECT R1.AssemblyComponentKey,

      R1.ParentProductKey,

      R1.ProductKey,

      convert(decimal(20,5), R1.Quantity * R2.Quantity),

      R3.InventoryLocationKey,

      R3.AvailableQuantity

  FROM InvAssemblyComponent AS R1

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

  JOIN InvInventoryLocation AS R3 ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = @pWarehouseKey AND R3.LocationKey = @pLocationKey

    )

  SELECT  A.ProductKey, B.ID, MIN(AvailableQuantity)/ SUM(QUANTITY) LeastQty INTO #LocationProductUOM

  FROM RecursionCTE A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  --And  B.IsKit = 'FALSE'

  And  B.IsNonStock = 'FALSE'

  group by a.ProductKey, B.ID

  SELECT @pQuantity = CONVERT(INT, MIN(LeastQty)) from #LocationProductUOM

END

END

ELSE IF EXISTS(select 1 from InvProduct Where ProductKey = @pProductKey and IsAssembly = 1)

BEGIN

 ;WITH RecursionCTE (assem,ParentProductKey,ProductKey, Quantity, InventoryItemKey, AvailableQuantity)

  as

  (

    SELECT   A.AssemblyComponentKey, A.ParentProductKey, A.ProductKey,

    CONVERT(DECIMAL(20,5), A.Quantity * 1), E.InventoryItemKey,

    Convert(Decimal(20,5),  E.AvailableQuantity) - Convert(Decimal(20,5),  E.ReservedQuantity) As AvailableQuantity

    FROM     InvAssemblyComponent A, InvProduct D, InvInventoryItem E

    Where A.ParentProductKey = D.ProductKey

  And  D.IsAssembly   = 'TRUE' --INDICATES THAT THE PARENTPRODUCT IS ASSEMBLY ITEM

  AND  E.WarehouseKey   = @pWarehouseKey

  AND  A.ProductKey   = E.ProductKey

  And  A.ParentProductKey     = @pProductKey

    UNION ALL

    SELECT R1.AssemblyComponentKey,

     R1.ParentProductKey,

     R1.ProductKey,

    convert(decimal(20,5), R1.Quantity * R2.Quantity),

     R3.InventoryItemKey,

     R3.AvailableQuantity - R3.ReservedQuantity

  FROM InvAssemblyComponent AS R1

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

  JOIN InvInventoryItem AS R3 ON R1.ProductKey = R3.ProductKey

  AND R3.WareHouseKey = @pWarehouseKey

   )

SELECT  A.ProductKey, B.ID, MIN(AvailableQuantity)/ SUM(QUANTITY) LeastQty INTO #ProductUOMAssembly

FROM RecursionCTE A, InvProduct B

WHERE A.ProductKey = B.ProductKey

--And  B.IsKit = 'FALSE'

And  B.IsNonStock = 'FALSE'

group by a.ProductKey, B.ID

SELECT @pQuantity = CONVERT(INT, MIN(LeastQty)) from #ProductUOMAssembly

END

END