iVend Database Database : GenerateSKU Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@productKey

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

InvItemMatrixCodeSetting

Table

Defines the item matrix product code setup values for a defined product

1

InvProduct

Table

Stores the product related details.

1

InvProductAttribute

Table

Defines the attribute information assigned to a product

1

CREATE PROCEDURE [dbo].[GenerateSKU](@productKey nvarchar(50))

AS

BEGIN

IF EXISTS (select * from tempdb.sys.objects WHERE object_id = OBJECT_ID(N'##GenerateSKUTable') AND type in (N'U'))

Begin

DROP TABLE

End

SELECT pa.ProductKey, ad.AttributeKey,ad.AttributeDetailKey,ad.Id,ad.Description INTO #temp2

FROM InvProductAttribute pa INNER JOIN InvAttributeDetail ad

ON pa.AttributeDetailKey =  ad.AttributeDetailKey

WHERE pa.ProductKey = @productKey

AND pa.IsDeleted =0

AND ad.IsDeleted = 0

Declare @AttributeKey nvarchar(100), @AttributeDetailKey nvarchar(100)

Declare curKeys CURSOR FOR

  SELECT AttributeKey

  From InvItemMatrixCodeSetting im

  where im.AttributeKey !='0'

  And im.ProductKey = @productKey

  order by im.CodeOrder

DECLARE @FinalQuery nvarchar(2000)

DECLARE @attributeKey1 nvarchar(100),@attributeDescription nvarchar(100)

set @FinalQuery = ''

OPEN curKeys

FETCH curKeys INTO @AttributeKey

WHILE @@FETCH_STATUS = 0

BEGIN

  SELECT @attributeDescription= am.Description

  FROM InvAttributeMaster am

  WHERE am.AttributeKey = @AttributeKey

  IF @FinalQuery = ''

  SET @FinalQuery = 'SELECT * into

      '] FROM #temp2 WHERE AttributeKey = '''+ @AttributeKey +''') [T' + @AttributeKey + ']'

  ELSE

   SET @FinalQuery = @FinalQuery + ' CROSS JOIN (SELECT Id [Id_' + @AttributeKey +'],Description [Description_'+ @AttributeKey +'], AttributeDetailKey [AttributeDetailKey_'+ @AttributeKey +

      '] FROM #temp2 WHERE AttributeKey = '''+ @AttributeKey +''') [T' +   @AttributeKey + ']'

 FETCH curKeys INTO @AttributeKey

 END

CLOSE curKeys

DEALLOCATE curKeys

--print @FinalQuery

EXEC (@FinalQuery)

ALTER TABLE

ALTER TABLE

ALTER TABLE

  DECLARE @FinalUpdateQueryForItemCode AS NVARCHAR(4000)

  DECLARE @lCurrentAttributeKey as NVARCHAR(100)

  DECLARE @CodeSeperator as NVARCHAR(100)

  DECLARE @UseSpaceAsCodeSeperator as bit

  DECLARE @CodeLength as NVARCHAR(100)

  Declare @productId nvarchar(20)

  Declare @productDescription nvarchar(100)

           select @productId = REPLACE(p.Id,CHAR(39),CHAR(34)) ,@productDescription = REPLACE(p.Description,CHAR(39),CHAR(34))from InvProduct p where p.ProductKey = @productKey

           DECLARE ProductListCursor CURSOR FOR

           SELECT  CONVERT(NVARCHAR (100), AttributeKey),

  UseSpaceAsCodeSeperator,

  CodeSeperator, CodeLength

  FROM InvItemMatrixCodeSetting where productkey = @productKey order by CodeOrder

           OPEN ProductListCursor

           FETCH NEXT FROM  ProductListCursor INTO @lCurrentAttributeKey,@UseSpaceAsCodeSeperator,@CodeSeperator,@CodeLength

           WHILE @@FETCH_STATUS=0

           BEGIN

                 if(@UseSpaceAsCodeSeperator = 'True')

                    set @CodeSeperator = ' '

                 else

                    set @CodeSeperator = ISNULL(@CodeSeperator,'')

                  --if (ISNULL(@CodeSeperator,'')='')

                 set @CodeSeperator =  REPLACE(@CodeSeperator,char(39),CHAR(34))

    if(@lCurrentAttributeKey = '0')

     set  @FinalUpdateQueryForItemCode = 'Update

    else

    set @FinalUpdateQueryForItemCode = 'UPDATE

                                                    ',AttributeDetailKeyCombination =ISNULL(AttributeDetailKeyCombination ,'''') + '','' + [AttributeDetailKey_' + @lCurrentAttributeKey +']'

    --print @FinalUpdateQueryForItemCode

     EXECUTE (@FinalUpdateQueryForItemCode)

    FETCH NEXT FROM ProductListCursor INTO @lCurrentAttributeKey,@UseSpaceAsCodeSeperator,@CodeSeperator,@CodeLength

           END

           CLOSE ProductListCursor

           DEALLOCATE ProductListCursor

--update query for updating Item Description Column of Test Table2

           DECLARE @FinalUpdateQueryForDesc AS NVARCHAR(4000)

           DECLARE @CurrentAttributeKey as NVARCHAR(100)

           DECLARE @DescSeperator as NVARCHAR(100)

           DECLARE @UseSpaceAsDescSeperator as bit

           DECLARE @DescLength as int

           DECLARE ProductListCursor CURSOR FOR

           SELECT  CONVERT(NVARCHAR(100), AttributeKey),DescriptionSeperator,DescriptionLength,UseSpaceAsDescriptionSeperator FROM InvItemMatrixCodeSetting where productkey = @productKey order by DescriptionOrder

           OPEN ProductListCursor

           FETCH NEXT FROM  ProductListCursor INTO @CurrentAttributeKey,@DescSeperator,@DescLength,@UseSpaceAsDescSeperator

           WHILE @@FETCH_STATUS=0

           BEGIN

             if(@UseSpaceAsDescSeperator = 'True')

                    set @DescSeperator = ' '

                 else

                    set @DescSeperator = ISNULL(@DescSeperator,'')

                set @DescSeperator =  REPLACE(@DescSeperator,char(39),CHAR(34))

                 IF(@CurrentAttributeKey = '0')

                 set  @FinalUpdateQueryForDesc = 'Update

                ELSE

                set @FinalUpdateQueryForDesc = 'UPDATE

    EXECUTE (@FinalUpdateQueryForDesc)

                 FETCH NEXT FROM ProductListCursor INTO @CurrentAttributeKey,@DescSeperator,@DescLength,@UseSpaceAsDescSeperator

           END

           CLOSE ProductListCursor

           DEALLOCATE ProductListCursor

  select REPLACE(ItemCode,CHAR(34),char(39)) 'ItemCode', REPLACE(ItemDescription,CHAR(34),char(39)) 'ItemDescription', AttributeDetailKeyCombination  from

  drop table

 End