iVend Database Database : GetChildInventoryItemList Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@parentProductKey

In

VarWChar

50

@inventoryStoreGroupKey

In

VarWChar

50

@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

InvProductAttribute

Table

Defines the attribute information assigned to a product

1

CREATE PROCEDURE [dbo].[GetChildInventoryItemList]

(

@parentProductKey nvarchar(50),

@inventoryStoreGroupKey nvarchar(50)

)

As

BEGIN

declare @attributeIdList varchar(max)

SELECT @attributeIdList = COALESCE(@attributeIdList + ', ', '') + '['+ CONVERT(nvarchar(50),AttributeId) +']'

FROM

(

Select distinct(m.Id) AttributeId from InvProductAttribute pa

Inner Join InvAttributeDetail d on

 pa.AttributeDetailKey = d.AttributeDetailKey

Inner Join InvAttributeMaster m on

 m.attributeKey = d.attributeKey

Where pa.ProductKey = @parentProductKey

) t1

declare @dynamicPivotQuery nvarchar(max)

set @dynamicPivotQuery =

'Select * from

(

 select p.ProductKey,p.Id ProductId ,p.Description ProductDescription ,Sum(InStockQuantity) InStockQuantity,sum(AvailableQuantity) AvailableQuantity,am.Id as              Master,ad.description Detail

 from InvInventoryItem i

 Inner join InvWarehouse w on

 i.WarehouseKey = w.WarehouseKey and

 w.InventoryStoreGroupKey IN (''0'', CASE WHEN '''+ CONVERT(nvarchar(50),@inventoryStoreGroupKey) +''' !=  ''0'' THEN + '''+

CONVERT(nvarchar(50),@inventoryStoreGroupKey) +''' ELSE w.InventoryStoreGroupKey END)

 Inner join InvProduct p on

 i.ProductKey = p.ProductKey

 Inner join InvProductAttribute ia on

 i.ProductKey = ia.productkey

 Inner join InvAttributeDetail ad on

 ad.AttributeDetailKey = ia.AttributeDetailKey

 Inner join InvAttributeMaster am on

 am.AttributeKey = ad.AttributeKey

 Where p.ParentMartixProductKey = ''' + CONVERT(nvarchar(50), @parentProductKey) +

''' group by p.productKey,p.Id,p.Description,am.Id,ad.description

)a

pivot

(

max(detail)

 FOR [Master] IN ('+ @attributeIdList +')

)as pivotTable'

EXEC sp_executesql @dynamicPivotQuery

END