iVend Database Database : ForeCast_UpdateMerchandiseHierarchy Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@HierarchyDetailKey

In

VarWChar

50

@ForecastPlanningKey

In

VarWChar

50

@ForeCastMethod

In

SmallInt

2

@ForecastSubMethod

In

SmallInt

2

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

Forecast

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

InvMerchandiseHierarchyDetail

Table

Detail table for saving the merchandise Hierarch tree details.

1

CREATE PROCEDURE [dbo].[ForeCast_UpdateMerchandiseHierarchy] (@HierarchyDetailKey NVARCHAR(50), @ForecastPlanningKey NVARCHAR(50), @ForeCastMethod smallint, @ForecastSubMethod smallint)

As

Begin

/*

Exec ForeCast_UpdateMerchandiseHierarchy 10000000000002,10000000000001,0,0

Select * From InvForeCast

Select * From InvHistoricalSales

*/

Declare @HierarchyKey NVARCHAR(50),

@HierarchyLevel int

Select @HierarchyKey = HierarchyKey

From InvMerchandiseHierarchyDetail

Where HierarchyDetailKey=@HierarchyDetailKey

--Declaring temporary table to analysis

Create Table #TreeHierarchy(HierarchyDetailKey NVARCHAR(50),ParentNodeKey NVARCHAR(50),HierarchyLevel int)

Create Table #LeafHierarchy(LeafDetailKey NVARCHAR(50),ParentNodeKey NVARCHAR(50),HierarchyLevel int)

Create Table #HierarchyDetail(

  ID Int Identity(1,1)

 , HierarchyDetailKey NVARCHAR(50)

 , Level1 nvarchar(100)

 , Level2 nvarchar(100)

 , Level3 nvarchar(100)

 , Level4 nvarchar(100)

 , Level5 nvarchar(100)

 , Level6 nvarchar(100)

 , Level7 nvarchar(100)

 , Level8 nvarchar(100)

 , Level9 nvarchar(100)

 , Level10 nvarchar(100)

 , Level11 nvarchar(100)

 , Level12 nvarchar(100)

 , Level13 nvarchar(100)

 , Level14 nvarchar(100)

 , Level15 nvarchar(100)

   )

--Fetching hierarchy

;With TreeHierarchy As(

Select  MHD.HierarchyDetailKey, ParentNodeKey =Cast('0' as NVARCHAR(50))

  ,HierarchyLevel=1

From InvMerchandiseHierarchyDetail AS MHD

Where MHD.HierarchyDetailKey=@HierarchyDetailKey

And  MHD.IsEnabled = 1

And  MHD.IsDeleted = 0

Union All

Select MHD.HierarchyDetailKey, ParentNodeKey=MHD.ParentNodeKey,

 HierarchyLevel=TreeHierarchy.HierarchyLevel + 1

From InvMerchandiseHierarchyDetail AS MHD

Inner Join TreeHierarchy On TreeHierarchy.HierarchyDetailKey=MHD.ParentNodeKey

Where MHD.IsEnabled = 1

And  MHD.IsDeleted = 0

)

Insert #TreeHierarchy Select * From TreeHierarchy

--Calaculating Leaf level node

SET @HierarchyLevel=(Select MAX(HierarchyLevel) from #TreeHierarchy)

Insert Into #HierarchyDetail(HierarchyDetailKey) Select HierarchyDetailKey From #TreeHierarchy Where HierarchyLevel=@HierarchyLevel

--Fetching Child Hierarchy

;With LeafDetailHierarchy As(

Select LeafDetailKey= MHD.HierarchyDetailKey,ParentNodeKey=HierarchyDetailKey,HierarchyLevel=@HierarchyLevel From #TreeHierarchy AS MHD

Where MHD.HierarchyDetailKey in(Select HierarchyDetailKey From #TreeHierarchy Where HierarchyLevel=@HierarchyLevel)

Union All

Select LeafDetailKey=LeafDetailHierarchy.LeafDetailKey, ParentNodeKey=MHD.ParentNodeKey, HierarchyLevel=LeafDetailHierarchy.HierarchyLevel-1

From #TreeHierarchy AS MHD

Inner Join LeafDetailHierarchy On LeafDetailHierarchy.ParentNodeKey=MHD.HierarchyDetailKey

)

Insert #LeafHierarchy Select * From LeafDetailHierarchy Where HierarchyLevel > 0

Declare @SQL Varchar(MAX)

Declare @LoopCounter int

Set @LoopCounter = @HierarchyLevel

While(@LoopCounter>0)

Begin

SET @SQL='Update HD SET  Level' + CAST(@LoopCounter as Varchar(2)) + '=(Select (Select Name From InvMerchandiseHierarchyDetail as IQ Where IQ.HierarchyDetailKey=LH.ParentNodeKey) From #LeafHierarchy as LH Where LH.HierarchyLevel=' + + CAST(@LoopCounter

as Varchar(2)) + ' And HD.HierarchyDetailKey=LH.LeafDetailKey) From #HierarchyDetail as HD'

--print @SQL

Exec(@SQL)

SET @LoopCounter= @LoopCounter-1

End

Update InvForeCast

SET Level1=HD.Level1

 ,Level2=HD.Level2

 ,Level3=HD.Level3

 ,Level4=HD.Level4

 ,Level5=HD.Level5

 , Level6=HD.Level6

 , Level7=HD.Level7

 , Level8=HD.Level8

 , Level9=HD.Level9

 , Level10=HD.Level10

 , Level11=HD.Level11

 , Level12=HD.Level12

 , Level13=HD.Level13

 , Level14=HD.Level14

 , Level15=HD.Level15

From InvProductMerchandiseHierarchy as PMH

Inner Join InvForeCast as InvForeCast On InvForeCast.ProductKey=PMH.ProductKey

Inner Join #HierarchyDetail as HD On HD.HierarchyDetailKey=PMH.HierarchyDetailKey

Where ForecastPlanningKey =@ForecastPlanningKey And ForeCastMethod =@ForeCastMethod And ForecastSubMethod =@ForecastSubMethod

And   PMH.IsActive = 1

Update InvHistoricalSales SET Level1=HD.Level1,Level2=HD.Level2,Level3=HD.Level3,Level4=HD.Level4,Level5=HD.Level5,

        Level6=HD.Level6,Level7=HD.Level7,Level8=HD.Level8,Level9=HD.Level9,Level10=HD.Level10,

        Level11=HD.Level11,Level12=HD.Level12,Level13=HD.Level13,Level14=HD.Level14,Level15=HD.Level15

From InvProductMerchandiseHierarchy as PMH

Inner Join InvHistoricalSales as InvHistoricalSales On InvHistoricalSales.ProductKey=PMH.ProductKey

Inner Join #HierarchyDetail as HD On HD.HierarchyDetailKey=PMH.HierarchyDetailKey

Where ForecastPlanningKey = @ForecastPlanningKey

And ForeCastMethod =@ForeCastMethod

And ForecastSubMethod =@ForecastSubMethod

And   PMH.IsActive = 1

End