iVend Database Database : BreakBuildKit Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductKey

In

Reference key of the Product

VarWChar

50

@pWarehouseKey

In

Reference key of the Warehouser

VarWChar

50

@pChangeQuantity

In

Numeric

9

@pModified

In

Date of last modification of record

DBTimeStamp

4

@pModifiedBy

In

Reference of Security User

VarWChar

50

@pAverageCost

Out

Numeric

9

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

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

InvProductCost

Table

Store the historical cost of the product.

1

CREATE PROCEDURE [dbo].[BreakBuildKit]

(

@pProductKey nvarchar(50),

@pWarehouseKey nvarchar(50),

@pChangeQuantity DECIMAL(20, 5),

          -- If quantity is negative, number of kits to be broken ##PARAM_END

@pModified   DATETIME,

@pModifiedBy nvarchar(50),

@pAverageCost DECIMAL(20,5) OUT

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @ErrorDesc VARCHAR(255)

SET @pAverageCost = 0

IF(@pChangeQuantity > 0)

BEGIN

  IF EXISTS(SELECT (1)

  FROM InvKitComponent a, InvProduct ap, InvInventoryItem Inv

  WHERE a.ProductKey = ap.ProductKey

  AND  ap.IsNonStock = 'FALSE'

  AND  Inv.ProductKey = ap.ProductKey

  AND  Inv.WarehouseKey = @pWarehouseKey

  AND  a.ParentProductKey = @pProductKey

  AND  Inv.InStockQuantity < (a.Quantity * @pChangeQuantity)

   )

  BEGIN

  SET @ErrorDesc = 'The quantity of the child item is not enough.'

  GOTO ERRORHANDLER

  END

END

IF (@pChangeQuantity > 0)

BEGIN

BEGIN

  SELECT @pAverageCost = SUM(a.Quantity * ISNULL(InvInventoryItem.AverageCost,0))

  FROM InvKitComponent a, InvProduct ap, InvInventoryItem

  WHERE a.ProductKey = ap.ProductKey

  AND  InvInventoryItem.ProductKey = ap.ProductKey

  AND  InvInventoryItem.WarehouseKey = @pWarehouseKey

  AND  a.ParentProductKey = @pProductKey

  AND  ap.CostingMethod = 1

  SELECT @pAverageCost = ISNULL(@pAverageCost,0) + SUM(Quantity * AverageCost)

  FROM (

  SELECT a.Quantity ,

    isnull((SELECT TOP 1 Cost FROM InvProductCost

        WHERE ProductKey = a.ProductKey

        AND WarehouseKey = @pWarehouseKey

        AND CONVERT(VARCHAR,EffectiveDate,112) <= dbo.GetCompanyDateTime()

        AND IsDeleted = 0

        ORDER BY EffectiveDate DESC),0) AverageCost

  FROM InvKitComponent a, InvProduct ap, InvInventoryItem C

  WHERE a.ProductKey = ap.ProductKey

  AND  C.ProductKey = ap.ProductKey

  AND  C.WarehouseKey = @pWarehouseKey

  AND  a.ParentProductKey = @pProductKey

  AND  ap.CostingMethod = 2

  ) S

END

END

RETURN

ERRORHANDLER:

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

RETURN

END