iVend Database Database : GetInventoryItemForStockCount Stored Procedure

Creation Date

5/3/2019 8:56 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductGroupId

In

VarWChar

20

@pFromProductId

In

VarWChar

20

@pToProductId

In

VarWChar

20

@pWarehouseKey

In

VarWChar

50

@pAutoAddProducts

In

Boolean

1

@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

InvProduct

Table

Stores the product related details.

1

InvProductGroup

Table

Defined the various groups under which the products can be categorised.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

SubSubsidiaryItem

Table

Stores Subsidiary information for each master, which can have multiple Subsidiaries assigned

1

CREATE PROCEDURE [dbo].[GetInventoryItemForStockCount]

(

@pProductGroupId NVARCHAR(20),

@pFromProductId   NVARCHAR(20),

@pToProductId   NVARCHAR(20),

@pWarehouseKey   NVARCHAR(50),

@pAutoAddProducts BIT--,

--@pSubsidiaryKey   nvarchar(50)

)

AS

BEGIN

Declare @lSubsidiaryKey nvarchar(50)

Select @lSubsidiaryKey = ISNULL(SubsidiaryKey, '0') From InvWarehouse Where WarehouseKey = @pWarehouseKey

IF @pAutoAddProducts = 'FALSE'

BEGIN

Select InvProduct.ProductKey, InvInventoryItem.WarehouseKey, InvInventoryItem.InventoryItemKey, InvProduct.Id, InvProduct.Description, InvInventoryItem.InStockQuantity, InvProduct.AllowFractionalQuantity

From InvProduct INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

INNER JOIN InvInventoryItem ON InvInventoryItem.ProductKey = InvProduct.ProductKey And InvInventoryItem.WarehouseKey = @pWarehouseKey

LEFT OUTER JOIN SubSubsidiaryItem ON InvProduct.ProductKey = SubSubsidiaryItem.SourceKey And SubSubsidiaryItem.SourceType = 46

Where InvProduct.Id >= CASE WHEN @pFromProductId ='' THEN  InvProduct.Id ELSE @pFromProductId END

And  InvProduct.Id <= CASE WHEN @pToProductId ='' THEN  InvProduct.Id ELSE @pToProductId END

And  InvProductGroup.Id = CASE WHEN @pProductGroupId = '' THEN InvProductGroup.Id ELSE @pProductGroupId END

AND  InvInventoryItem.IsLocked   = 'FALSE'

--And  InvProduct.IsOnHold   = 'FALSE'

And  (CASE WHEN @lSubsidiaryKey = '0' THEN InvProduct.IsOnHold ELSE InvInventoryItem.IsOnHold END) = 'FALSE'

And  InvProduct.IsDeleted   = 'FALSE'

And  InvProduct.IsGiftCertificate = 'FALSE'

And  InvProduct.IsNonStock = 'FALSE'

And  InvProduct.IsAssembly = 'FALSE'

And  InvProduct.IsMatrixItem = 'FALSE'

And ISNULL(SubSubsidiaryItem.SubsidiaryKey, '0') = @lSubsidiaryKey

END

ELSE

BEGIN

Select InvProduct.ProductKey, InvInventoryItem.WarehouseKey, InvInventoryItem.InventoryItemKey, InvProduct.Id, InvProduct.Description,

 InvInventoryItem.InStockQuantity, InvProduct.AllowFractionalQuantity

From InvProduct INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

INNER JOIN InvInventoryItem ON InvInventoryItem.ProductKey = InvProduct.ProductKey And InvInventoryItem.WarehouseKey = @pWarehouseKey

LEFT OUTER JOIN SubSubsidiaryItem ON InvProduct.ProductKey = SubSubsidiaryItem.SourceKey And SubSubsidiaryItem.SourceType = 46

Where ISNULL(InvInventoryItem.NextCountDate,dbo.GetCompanyDateTime()) <= dbo.GetCompanyDateTime()

AND  InvInventoryItem.IsLocked   = 'FALSE'

--And  InvProduct.IsOnHold   = 'FALSE'

And  (CASE WHEN @lSubsidiaryKey = '0' THEN InvProduct.IsOnHold ELSE InvInventoryItem.IsOnHold END) = 'FALSE'

And  InvProduct.IsDeleted   = 'FALSE'

And  InvProduct.IsGiftCertificate = 'FALSE'

And  InvProduct.IsNonStock = 'FALSE'

And  InvProduct.IsAssembly = 'FALSE'

And  InvProduct.IsMatrixItem = 'FALSE'

And ISNULL(SubSubsidiaryItem.SubsidiaryKey, '0') = @lSubsidiaryKey

END

END