iVend Database Database : InvGetMatrixItems Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pPOSKey

In

Reference key of the POS where the transaction is carried out

VarWChar

50

@pWarehouseKey

In

Reference key for which the details is required

VarWChar

50

@pProductKey

In

parent matrix item

VarWChar

50

@pTransactionType

In

signifies type of transcation for which the list is required

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvAttributeDetail

Table

Stores the details of attrbutes defined for a matrix product

1

InvAttributeMaster

Table

Stores the attrbutes information for a matrix product

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvProductAttribute

Table

Defines the attribute information assigned to a product

1

InvTempMatrixProductList

Table

1

--EXEC InvGetMatrixItems 10000000000005, 10000000000003, 10000000000024, 8

CREATE PROCEDURE [dbo].[InvGetMatrixItems]

@pPOSKey as nvarchar(50),

@pWarehouseKey as nvarchar(50),

@pProductKey as nvarchar(50),

@pTransactionType as Int

AS

BEGIN

SET NOCOUNT ON

DECLARE @lCurrentAttributeKey nvarchar(50),

  @lCurrentAttributeDetailKey nvarchar(50)

/*TRANSACTION TYPES

  Sale = 0,

  SaleRefund = 1,

  SaleExchange = 2,

  SpecialOrder = 8,

  Layaway = 9,

  Quotation = 16

 */

--THIS WILL ENSURE THAT FOR THE ATTRIBUTES FOR WHICH NO VALUE IS SELECTED FROM UI ARE STILL ACCOMODATED IN THE SEARCH RESULT

INSERT INTO InvTempMatrixProductList (POSKey, AttributeKey, AttributeDetailKey)

SELECT @pPOSKey, A.AttributeKey, C.AttributeDetailKey

FROM InvAttributeMaster A, InvAttributeDetail B, InvProductAttribute C

WHERE A.AttributeKey = B.AttributeKey

AND B.AttributeDetailKey =  C.AttributeDetailKey

AND C.ProductKey = @pProductKey

AND A.AttributeKey NOT IN

 (

  SELECT DISTINCT ATTRIBUTEKEY

  FROM InvTempMatrixProductList

 )

--THIS IS TO GET THE PRODUCT LIST

SELECT A.ProductKey, A.Id, A.Description, B.InventoryItemKey, B.Price, B.InStockQuantity, B.AvailableQuantity, E.AttributeDetailKey

INTO #TempProductList

FROM InvProduct A, InvInventoryItem B, InvAttributeMaster C,  InvAttributeDetail D, InvProductAttribute E

Where A.ProductKey = B.ProductKey

And B.WarehouseKey = @pWarehouseKey

And A.ProductKey = E.ProductKey

And E.AttributeDetailKey = D.AttributeDetailKey

And D.AttributeKey = C.AttributeKey

And A.ParentMartixProductKey = @pProductKey

And A.IsOnHold = 0

And A.IsMatrixChildItem = 1

And A.IsSaleable = CASE WHEN @pTransactionType IN(0, 16) THEN 'TRUE' ELSE A.IsSaleable END

And A.IsExchangable = CASE WHEN @pTransactionType IN(2) THEN 'TRUE' ELSE A.IsExchangable END

And A.IsRefundable = CASE WHEN @pTransactionType IN(1) THEN 'TRUE' ELSE A.IsRefundable END

And A.CanLayaway = CASE WHEN @pTransactionType IN(9) THEN 'TRUE' ELSE A.CanLayaway END

And A.CanOrder = CASE WHEN @pTransactionType IN(8) THEN 'TRUE' ELSE A.CanOrder END

SELECT TOP 1 AttributeDetailKey INTO #TempAttributeList

FROM InvProductAttribute

--SELECT * FROM #TempProductList

DECLARE ProductListCursor CURSOR FOR

SELECT DISTINCT AttributeKey FROM InvTempMatrixProductList

WHERE  POSKey = @pPOSKey

GROUP BY AttributeKey

HAVING COUNT(AttributeKey) = 1

OPEN ProductListCursor

FETCH NEXT FROM  ProductListCursor INTO @lCurrentAttributeKey

WHILE @@FETCH_STATUS=0

BEGIN

  Select @lCurrentAttributeDetailKey = AttributeDetailKey

  FROM InvTempMatrixProductList

  WHERE POSKey = @pPOSKey

  AND   AttributeKey = @lCurrentAttributeKey

  DELETE FROM #TempProductList

  WHERE InventoryItemKey NOT IN

  (

  SELECT DISTINCT InventoryItemKey FROM #TempProductList

  WHERE AttributeDetailKey = @lCurrentAttributeDetailKey

  )

FETCH NEXT FROM ProductListCursor INTO @lCurrentAttributeKey

END

CLOSE ProductListCursor

DEALLOCATE ProductListCursor

DECLARE AttributeCursor CURSOR FOR

SELECT DISTINCT AttributeKey FROM InvTempMatrixProductList

WHERE  POSKey = @pPOSKey

GROUP BY AttributeKey

HAVING COUNT(AttributeKey) > 1

OPEN AttributeCursor

FETCH NEXT FROM  AttributeCursor INTO @lCurrentAttributeKey

WHILE @@FETCH_STATUS=0

BEGIN

  Delete FROM #TempAttributeList

  INSERT INTO #TempAttributeList

  Select AttributeDetailKey

  FROM InvTempMatrixProductList

  WHERE POSKey = @pPOSKey

  AND AttributeKey = @lCurrentAttributeKey

  DELETE FROM #TempProductList

  WHERE InventoryItemKey NOT IN

  (

  SELECT DISTINCT InventoryItemKey FROM #TempProductList A

      WHERE A.AttributeDetailKey IN

   (SELECT DISTINCT AttributeDetailKey FROM #TempAttributeList)

  )

FETCH NEXT FROM AttributeCursor INTO @lCurrentAttributeKey

END

CLOSE AttributeCursor

DEALLOCATE AttributeCursor

SELECT DISTINCT

  A.ProductKey, A.Id, A.Description,

  A.InventoryItemKey, A.Price, A.InStockQuantity,

  A.AvailableQuantity

FROM #TempProductList A, InvProduct B

WHERE A.ProductKey = B.ProductKey

AND B.IsMatrixItem = 'FALSE'

AND B.IsMatrixChildItem = 'TRUE'

RETURN

lErrHandler:

CLOSE AttributeCursor

DEALLOCATE AttributeCursor

CLOSE ProductListCursor

DEALLOCATE ProductListCursor

--ROLLBACK TRANSACTION

RETURN

END