iVend Database Database : Forecast Stored Procedure

Creation Date

5/13/2019 3:57 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@ForecastPlanningKey

In

VarWChar

50

@ForeCastMethod

In

SmallInt

2

@ForecastSubMethod

In

SmallInt

2

@ErrMsg

Out

VarWChar

500

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ForeCast_UpdateMerchandiseHierarchy

Stored Procedure

1

HisHistoricalSales

Table

Stores the historic value for the sales of product

1

InvForecast

Table

1

InvForecastPlanning

Table

Stores the data based on which the inventory forecast needs to plan

1

InvForecastPlanningDetails

Table

Stores the detailed data for the forecast planning

1

InvHistoricalSales

Table

Stores the Historic sales records

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvMerchandiseHierarchyDetail

Table

Detail table for saving the merchandise Hierarch tree details.

1

InvProduct

Table

Stores the product related details.

1

InvProductCategory

Table

Defines the categories under which the products can be put for logical classification.

1

InvProductCategoryProduct

Table

Defines the list of products that have been put under each category.

1

InvProductGroup

Table

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

1

InvProductMerchandiseHierarchy

Table

Stores the values related to a defined Merchandise hirarchy for a product

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

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[Forecast] (@ForecastPlanningKey NVARCHAR(50), @ForeCastMethod smallint, @ForecastSubMethod smallint, @ErrMsg nvarchar(500) out)

As

Begin

Set NoCount On

Set ANSI_WARNINGS Off

Set @ErrMsg = ''

/*

Select * From InvInventoryItem Where ProductKey = 10000000000000002

 Declare @ErrMsg nvarchar(500)

 Set @ErrMsg = ''

 Exec Forecast '38e9b9d2-8ee9-4fb6-99db-9141966a464c', 0, 0, @ErrMsg out

 Select @ErrMsg

 Truncate Table   InvHistoricalSales

 Truncate Table   InvForecast

 Select * From InvHistoricalSales Order By WarehouseKey, ProductKey, ActualDate

 Select * From InvForecast Order By ProductKey, WarehouseKey, ForecastDate

 CREATE NONCLUSTERED INDEX [_dta_index_InvProduct_5_368720366__K1] ON [dbo].[InvProduct]

(

[ProductKey] ASC

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_TrxTransaction_5_1506104406__K1_5] ON [dbo].[TrxTransaction]

(

[TransactionKey] ASC

)

INCLUDE ( [ActualDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--CREATE STATISTICS [_dta_stat_1312723729_2_5] ON [dbo].[TrxTransactionSaleItem]([TransactionKey], [ProductKey])

CREATE NONCLUSTERED INDEX [_dta_index_TrxTransactionSaleItem_5_1312723729__K4_K5_K2_3_20] ON [dbo].[TrxTransactionSaleItem]

(

[WarehouseKey] ASC,

[ProductKey] ASC,

[TransactionKey] ASC

)

INCLUDE ( [Type],

[Quantity]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

*/

BEGIN --Variable Declarations

Declare @BasedOn     smallint,

  @FromID     nvarchar(40),

  @ToID     nvarchar(40),

  @HistoricalSalesStartMonth smallint,

  @HistoricalSalesYear smallint,

  @HistoricalSalesEndMonth smallint,

  @HistoricalSalesEndYear smallint,

  @ForecastedSalesStartMonth smallint,

  @ForecastingYear   smallint,

  @ForecastedSalesEndMonth smallint,

  @ForecastedSalesEndYear smallint,

  @ConsiderRefundInForecast Bit    ,

@lSubsidiaryKey NVARCHAR(50)

END

BEGIN --Get the Basic Details of Forecast Template

Select @BasedOn     = BasedOn,

  @HistoricalSalesStartMonth = 1,

  @HistoricalSalesYear   = HistoricalSalesYear,

  @HistoricalSalesEndMonth = 12,

  @HistoricalSalesEndYear   = ForecastingYear - 1,

  @ForecastedSalesStartMonth = 1,

  @ForecastingYear   = ForecastingYear,

  @ForecastedSalesEndMonth = 12,

  @ForecastedSalesEndYear   = ForecastingYear,

  @ConsiderRefundInForecast = ConsiderRefundInForecast   ,

@lSubsidiaryKey = SubsidiaryKey

From InvForecastPlanning

Where ForecastPlanningKey   = @ForecastPlanningKey

End

BEGIN --Get All the warehouses for which the Forecast needs to run.

Declare @Warehouses Table(WarehouseKey NVARCHAR(50))

Insert Into @Warehouses

Select WarehouseKey From InvForecastPlanningDetails Where ForecastPlanningKey = @ForecastPlanningKey And IsSelected = 1

END

BEGIN --Get All the items for which Forecast needs to run.

Declare @Items Table(ProductKey NVARCHAR(50), ProductGroupKey NVARCHAR(50), Created Datetime)

If @BasedOn = 1

Begin

  Select @FromID = InvProduct.Id,

    @ToID = InvProduct1.Id

  From InvForecastPlanning Inner Join InvProduct On  InvForecastPlanning.FromKey = InvProduct.ProductKey     And    InvProduct.IsGiftCertificate = 0

        Inner Join InvProduct As InvProduct1 On InvForecastPlanning.ToKey = InvProduct1.ProductKey And    InvProduct1.IsGiftCertificate = 0

  Where ForecastPlanningKey = @ForecastPlanningKey

  Insert Into @Items(ProductKey,  ProductGroupKey, Created)

  Select ProductKey, ProductGroupKey, Created

  From InvProduct

  Where ((Id Between @FromID And @ToID) OR (Id Between @ToID And @FromID))

  And InvProduct.IsOnHold = 0

End

Else If @BasedOn = 2

Begin

  Select @FromID = InvProductCategory.Id,

  @ToID = InvProductCategory1.Id

  From InvForecastPlanning Inner Join InvProductCategory On  InvForecastPlanning.FromKey = InvProductCategory.ProductCategoryKey

        Inner Join InvProductCategory As InvProductCategory1 On  InvForecastPlanning.ToKey = InvProductCategory1.ProductCategoryKey

  Where ForecastPlanningKey = @ForecastPlanningKey

  Insert Into @Items(ProductKey,  ProductGroupKey)

  Select InvProduct.ProductKey, InvProduct.ProductGroupKey

  From InvProductCategoryProduct Inner Join InvProductCategory On InvProductCategoryProduct.ProductCategoryKey = InvProductCategory.ProductCategoryKey

        Inner Join InvProduct On InvProductCategoryProduct.ProductKey = InvProduct.ProductKey   And    InvProduct.IsGiftCertificate = 0

  Where ((InvProductCategory.Id Between @FromID And @ToID) OR (InvProductCategory.Id Between @ToID And @FromID))

  And InvProduct.IsOnHold = 0

End

Else If @BasedOn = 3

Begin

  Select @FromID = InvProductGroup.Id,

    @ToID = InvProductGroup1.Id

  From InvForecastPlanning Inner Join InvProductGroup On  InvForecastPlanning.FromKey = InvProductGroup.ProductGroupKey

        Inner Join InvProductGroup As InvProductGroup1 On  InvForecastPlanning.ToKey = InvProductGroup1.ProductGroupKey

  Where ForecastPlanningKey = @ForecastPlanningKey

  Insert Into @Items(ProductKey,  ProductGroupKey)

  Select InvProduct.ProductKey, InvProduct.ProductGroupKey

  From InvProduct Inner Join InvProductGroup On InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey     And   InvProduct.IsGiftCertificate = 0

  Where ((InvProductGroup.Id Between @FromID And @ToID) OR (InvProductGroup.Id Between @ToID And @FromID))

  And InvProduct.IsOnHold = 0

End

Else If @BasedOn = 4

Begin

  ;with CTE AS

  (

    Select HierarchyDetailKey, ParentNodeKey

    From  InvMerchandiseHierarchyDetail

    Where  HierarchyKey = (Select FromKey From InvForecastPlanning Where ForecastPlanningKey = @ForecastPlanningKey)

    And  HierarchyDetailKey = (Select ToKey From InvForecastPlanning Where ForecastPlanningKey = @ForecastPlanningKey)

    Union All

    Select InvMerchandiseHierarchyDetail.HierarchyDetailKey, InvMerchandiseHierarchyDetail.ParentNodeKey

    From  CTE Join InvMerchandiseHierarchyDetail

    On  CTE.HierarchyDetailKey = InvMerchandiseHierarchyDetail.ParentNodeKey

  )

  Insert Into @Items(ProductKey,  ProductGroupKey)

  Select InvProduct.ProductKey, InvProduct.ProductGroupKey

  from CTE Inner Join InvProductMerchandiseHierarchy On CTE.HierarchyDetailKey = InvProductMerchandiseHierarchy.HierarchyDetailKey And InvProductMerchandiseHierarchy.HierarchyKey = ((Select FromKey From InvForecastPlanning Where ForecastPlanningKey = @ForecastPlanningKey))

    Inner Join InvProduct On InvProductMerchandiseHierarchy.ProductKey = InvProduct.ProductKey AND InvProductMerchandiseHierarchy.IsActive = 1

  Where Not Exists(Select 1 From InvMerchandiseHierarchyDetail Where ParentNodeKey = CTE.HierarchyDetailKey)

  OPTION (MAXRECURSION 32767);

End

END

if ISNULL(@lSubsidiaryKey, '0') != '0'

BEGIN

Delete From @Items Where ProductKey Not in

(

Select ProductKey From @Items A , SubSubsidiaryItem B

Where A.ProductKey = B.SourceKey

And B.SourceType = 46

And B.SubSidiaryKey = @lSubsidiaryKey

)

END

Delete From @Items Where ProductKey in ( Select ProductKey From InvProduct Where IsGiftCertificate = 1)

BEGIN --Create Each month's dummy record of historical period

Declare @HistoricalPeriods Table (Period Datetime)

 ;WITH CTE AS

 (

  SELECT Cast(CAST(@HistoricalSalesYear As varchar) + '-' + CAST(@HistoricalSalesStartMonth As varchar) + '-1' As Datetime) AS Period

  UNION ALL

  SELECT DATEADD(MONTH, 1, Period)

  FROM CTE

  WHERE DATEADD(MONTH, 1, Period) <= Cast(CAST(@HistoricalSalesEndYear As varchar) + '-' + CAST(@HistoricalSalesEndMonth As varchar) + '-1' As Datetime)

 )

Insert Into @HistoricalPeriods(Period)

SELECT CTE.Period

FROM CTE

OPTION (MAXRECURSION 32767);

END

BEGIN --Restrict the forecast to be executed if the historical sales data is too large

Declare @TotalItemCount int,

  @TotalWarehouseCount int,

  @TotalPeriodCount int

Select @TotalItemCount = COUNT(*) From @Items

Select @TotalWarehouseCount = COUNT(*) From @Warehouses

Select @TotalPeriodCount = COUNT(*) From @HistoricalPeriods

If (@TotalItemCount * @TotalWarehouseCount * @TotalPeriodCount) > 1000000

Begin

  Set @ErrMsg = 'Please Narrow your filter'

  Return

End

Select Items.ProductKey, Warehouses.WarehouseKey, HistoricalPeriods.Period

Into #temp

From @Items As Items Cross Join @Warehouses As Warehouses Cross Join @HistoricalPeriods As HistoricalPeriods

END

BEGIN --Create Each month's dummy record of forecasted period

Declare @ForecastedPeriods Table (Period Datetime)

 ;WITH CTE AS

 (

  SELECT Cast(CAST(@ForecastingYear As varchar) + '-1-1' As Datetime) AS Period

  UNION ALL

  SELECT DATEADD(MONTH, 1, Period)

  FROM CTE

  WHERE DATEADD(MONTH, 1, Period) <= Cast(CAST(@ForecastingYear As varchar) + '-12-1' As Datetime)

 )

Insert Into @ForecastedPeriods(Period)

SELECT CTE.Period

FROM CTE

OPTION (MAXRECURSION 32767);

--select  A.*, b.Id from @Items A, InvProduct B Where A.ProductKey = B.ProductKey

Delete From InvForecast Where ForecastPlanningKey = @ForecastPlanningKey --And ForecastMethod = @ForeCastMethod And ForecastSubMethod = @ForecastSubMethod

Insert Into InvForecast(ForecastPlanningKey, ForecastMethod, ForecastSubMethod, ForecastDate, WarehouseKey, ProductKey, Quantity, OverrideQuantity, OverrideRemarks, ForecastKey)

Select @ForecastPlanningKey, @ForeCastMethod, @ForecastSubMethod, Month(ForecastedPeriods.Period), Warehouses.WarehouseKey, Items.ProductKey, 0, 0, '', NEWID()

From @Items As Items Cross Join @Warehouses As Warehouses Cross Join @ForecastedPeriods As ForecastedPeriods

END

BEGIN --Get the RawData

Delete From InvHistoricalSales Where ForecastPlanningKey = @ForecastPlanningKey --And ForecastMethod = @ForeCastMethod And ForecastSubMethod = @ForecastSubMethod

Insert Into InvHistoricalSales(ForecastPlanningKey, ForecastMethod, ForecastSubMethod, ActualDate, WarehouseKey, ProductKey, Quantity)

Select ForecastPlanningKey, ForeCastMethod, ForecastSubMethod, ActualDate, WarehouseKey, ProductKey, SUM(Quantity)

From (

    Select @ForecastPlanningKey As ForecastPlanningKey, @ForeCastMethod As ForeCastMethod, @ForecastSubMethod As ForecastSubMethod,

      Cast(Cast(Year(TrxTransaction.ActualDate) As Varchar) + '-' + Cast(Month(TrxTransaction.ActualDate) As Varchar) + Cast('-1' As Varchar) As Datetime) As ActualDate,

      TrxTransactionSaleItem.WarehouseKey, TrxTransactionSaleItem.ProductKey,

      Sum(Case When TrxTransactionSaleItem.Type = 1 Then Case When @ConsiderRefundInForecast = 1 Then TrxTransactionSaleItem.Quantity * -1 Else 0 End Else TrxTransactionSaleItem.Quantity End) As Quantity

    From TrxTransactionSaleItem Inner Join TrxTransaction On TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

            Inner Join @Items As Items On TrxTransactionSaleItem.ProductKey = Items.ProductKey

            Inner Join @Warehouses As Warehouses On TrxTransactionSaleItem.WarehouseKey = Warehouses.WarehouseKey

    Where YEAR(TrxTransaction.ActualDate) >= @HistoricalSalesYear

    And YEAR(TrxTransaction.ActualDate) <= @HistoricalSalesEndYear

    Group By Year(TrxTransaction.ActualDate), Month(TrxTransaction.ActualDate), TrxTransactionSaleItem.WarehouseKey, TrxTransactionSaleItem.ProductKey

    Union All

Select @ForecastPlanningKey As ForecastPlanningKey, @ForeCastMethod As ForeCastMethod, @ForecastSubMethod As ForecastSubMethod,

      Cast(Cast(Year(HisHistoricalSales.TransactionDate) As Varchar) + '-' + Cast(Month(HisHistoricalSales.TransactionDate) As Varchar) + Cast('-1' As Varchar) As Datetime) As ActualDate,

      InvWarehouse.WarehouseKey, InvProduct.ProductKey,

      SUM(HisHistoricalSales.Quantity) As Quantity

    From HisHistoricalSales Inner Join InvWarehouse On HisHistoricalSales.WarehouseId = InvWarehouse.Id

            Inner Join @Warehouses As Warehouses On InvWarehouse.WarehouseKey = Warehouses.WarehouseKey

            Inner Join InvProduct On HisHistoricalSales.ProductId = Invproduct.Id

            Inner Join @Items As Items On InvProduct.ProductKey = Items.ProductKey

    Where YEAR(HisHistoricalSales.TransactionDate) >= @HistoricalSalesYear

    And YEAR(HisHistoricalSales.TransactionDate) <= @HistoricalSalesEndYear

    Group By Year(HisHistoricalSales.TransactionDate), Month(HisHistoricalSales.TransactionDate), InvWarehouse.WarehouseKey, InvProduct.ProductKey

   ) As temp

Group By ForecastPlanningKey, ForeCastMethod, ForecastSubMethod, ActualDate, WarehouseKey, ProductKey

END

BEGIN --Final Output

Insert Into InvHistoricalSales(ForecastPlanningKey, ForeCastMethod, ForecastSubMethod, ActualDate, WarehouseKey, ProductKey, Quantity)

Select @ForecastPlanningKey, @ForeCastMethod, @ForecastSubMethod, temp.Period, temp.WarehouseKey, temp.ProductKey, 0

From #temp As temp Left Outer Join InvHistoricalSales

On temp.Period = InvHistoricalSales.ActualDate

And temp.ProductKey = InvHistoricalSales.ProductKey

And temp.WarehouseKey = InvHistoricalSales.WarehouseKey

Where InvHistoricalSales.ForecastPlanningKey is Null

  BEGIN --Delete all those products that have been Locked or OnHold at the warehouse Level

Delete InvHistoricalSales

From InvHistoricalSales Inner Join InvInventoryItem On InvHistoricalSales.ProductKey = InvInventoryItem.ProductKey

              And InvHistoricalSales.WarehouseKey = InvInventoryItem.WarehouseKey

              And (InvInventoryItem.IsLocked = 1 Or InvInventoryItem.IsOnHold = 1)

  END

--Update the merchandising hierarchy here

If (@BasedOn = 4)

Begin

  Declare @HierarchyDetailKey Bigint

  Select @HierarchyDetailKey = ToKey From InvForecastPlanning Where ForecastPlanningKey = @ForecastPlanningKey

  EXEC ForeCast_UpdateMerchandiseHierarchy @HierarchyDetailKey, @ForecastPlanningKey, @ForeCastMethod,@ForecastSubMethod

End

--Select ForecastPlanningKey, ForecastMethod, ForecastSubMethod, ActualDate, WarehouseKey, ProductKey, Quantity

--From InvHistoricalSales

--Where ForecastPlanningKey = @ForecastPlanningKey

--And  ForecastMethod = @ForeCastMethod

--And  ForecastSubMethod = @ForecastSubMethod

--Order By ProductKey, WarehouseKey, ActualDate

END

Set NoCount Off

Set ANSI_WARNINGS ON

End