iVend Database Database : InventoryValuation Stored Procedure

Creation Date

10/8/2018 5:58 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@Subsidiary

In

VarWChar

200

@FromWarehouse

In

VarWChar

200

@ToWarehouse

In

VarWChar

200

@ProductGroup

In

VarWChar

200

@FromProduct

In

VarWChar

200

@ToProduct

In

VarWChar

200

@HOCurrency

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

CREATE Proc InventoryValuation (

  @FromDate Varchar(8)

, @Subsidiary NVarchar(200)

, @FromWarehouse NVarchar(200)

, @ToWarehouse NVarchar(200)

, @ProductGroup NVarchar(200)

, @FromProduct NVarchar(200)

, @ToProduct NVarchar(200)

, @HOCurrency Bit =0

)

AS

BEGIN

/*  Created By: Jyotsna Tyagi

Created On: June 18, 2018

Report generate data based on inventory and cost table and its generate all the products cycle

*/

--Declare @FromDate Varchar(8)='20180101', @ToDate Varchar(8)='20180131', @Subsidiary NVarchar(200)='', @FromWarehouse NVarchar(200)='', @ToWarehouse NVarchar(200)='',

-- @ProductGroup NVarchar(200)='', @FromProduct NVarchar(200)='', @ToProduct NVarchar(200)='', @HOCurrency Bit =0

--EXEC  InventoryValuation @FromDate,  @Subsidiary, @FromWarehouse, @ToWarehouse, @ProductGroup, @FromProduct, @ToProduct, @HOCurrency

/* Insert data into temp table for create index on Warehouse and product */

IF OBJECT_ID('tempdb..#InventoryItemLog') IS NOT NULL

  DROP TABLE #InventoryItemLog

CREATE TABLE [dbo].[#InventoryItemLog](

[InventoryItemLogKey] [bigint] NOT NULL,

[WarehouseKey] [bigint] NOT NULL,

[ProductKey] [bigint] NOT NULL,

[Quantity] [decimal](20, 5) NOT NULL,

[SourceType] [int] NOT NULL,

[Created] [datetime] NOT NULL)

CREATE CLUSTERED INDEX IDX_InventoryLog_WHProd ON #InventoryItemLog(WarehouseKey, ProductKey, Created)

INSERT INTO #InventoryItemLog (InventoryItemLogKey, WarehouseKey, ProductKey, Quantity, SourceType, Created)

Select InventoryItemLogKey, WarehouseKey, ProductKey, (ISNULL(InQty,0)-ISNULL(OutQty,0)), SourceType, Created From InvInventoryItemLog

/* Generate all the transaction data based on filter selection */

;WITH CostValue AS(

Select Row_NUMBER() OVER(Partition BY IL.WarehouseKey, IL.ProductKey ORDER BY IL.WarehouseKey, IL.ProductKey, IL.Created) AS RN,

ISNULL(Sub.SubsidiaryKey, 0) SubsidiaryKey, ISNULL(Sub.Id,'') AS Subsidiary, ISNULL(Sub.Description,'') AS SubsidiaryDesc,

IL.WarehouseKey, WH.Id AS Warehouse, WH.Description AS WarehouseDesc, PG.ProductGroupKey, PG.Id AS ProductGroup, PG.Description AS ProductGroupDesc,

IL.ProductKey, P.Id AS Product, P.Description AS ProductDesc, IL.SourceType, Quantity AS Quantity, IL.Created,

ISNULL(ER.SellRate,1) AS ExchangeRate, P.CostingMethod,

(CASE WHEN (Sub.SubsidiaryKey=0 or Sub.SubsidiaryKey IS NULL) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

(Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo,

CASE WHEN P.CostingMethod=1 THEN

ISNULL((

SELECT TOP 1 UpdatedAverageCost

  FROM InvInventoryItemLog WITH (NOLOCK)

  WHERE ProductKey IN (IL.ProductKey, 0)

  AND WarehouseKey IN (IL.WarehouseKey, 0)

  AND Created<= IL.Created AND ISNULL(UpdatedAverageCost, 0) <> 0

  ORDER BY Created DESC

),0)

WHEN P.CostingMethod=2 THEN ISNULL((Select Top 1 ISNULL(Cost,0) From InvProductCost PC Where IL.ProductKey=PC.ProductKey

AND IL.WarehouseKey IN (ISNULL(PC.WarehouseKey,0)) AND ISNULL(Sub.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

AND PC.EffectiveDate <=IL.Created),0) ELSE 0 END AS Cost

From #InventoryItemLog IL WITH (NOLOCK)

INNER JOIN InvWarehouse WH WITH (NOLOCK) ON IL.WarehouseKey=WH.WarehouseKey

INNER JOIN InvProduct P WITH (NOLOCK) ON IL.ProductKey=P.ProductKey

INNER JOIN InvProductGroup PG WITH (NOLOCK) ON P.ProductGroupKey=PG.ProductGroupKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON ISNULL(WH.SubsidiaryKey,0)=ISNULL(Sub.SubsidiaryKey,0)

LEFT OUTER JOIN PmtExchangeRate ER WITH (NOLOCK) ON Sub.LocalCurrencyKey= Er.CurrencyKey AND CONVERT(Nvarchar(8),IL.Created,112) = CONVERT(NVARCHAR(8), ER.EffectiveDate,112)

Where Convert(Varchar(8), IL.Created, 112)<=@FromDate

AND ISNULL(Sub.Id,'')=CASE WHEN Sub.Id<>'' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

AND (WH.Id BETWEEN CASE WHEN @FromWarehouse<>'' THEN @FromWarehouse ELSE WH.Id END AND CASE WHEN @ToWarehouse<>'' THEN @ToWarehouse ELSE WH.Id END

          OR

WH.Id BETWEEN CASE WHEN @ToWarehouse<>'' THEN @ToWarehouse ELSE WH.Id END AND CASE WHEN @FromWarehouse<>'' THEN @Fromwarehouse ELSE WH.Id END)

AND CHARINDEX(PG.Id, @ProductGroup)> CASE WHEN @ProductGroup<>'' THEN 0 ELSE -1 END

AND (P.Id BETWEEN CASE WHEN @FromProduct<>'' THEN @FromProduct ELSE P.Id END AND CASE WHEN @ToProduct<>'' THEN @ToProduct ELSE P.Id END

            OR

P.Id BETWEEN CASE WHEN @ToProduct<>'' THEN @ToProduct ELSE P.Id END AND CASE WHEN @FromProduct<>'' THEN @FromProduct ELSE P.Id END)

)

Select RN, SubsidiaryKey, Subsidiary, SubsidiaryDesc, WarehouseKey, Warehouse, WarehouseDesc, ProductGroupKey, ProductGroup, ProductGroupDesc,

ProductKey, Product, ProductDesc, SourceType, Created, Quantity,

(CASE WHEN @HOCurrency='true' THEN BaseCultureInfo ELSE CultureInfo END) AS CultureInfo, BaseCultureInfo, ExchangeRate, CostingMethod,

(CASE WHEN @HOCurrency='true' THEN (Cost*ExchangeRate) ELSE Cost END) AS Cost,

(CASE WHEN @HOCurrency='true' THEN (Quantity*(Cost*ExchangeRate)) ELSE (Quantity* Cost) END) AS TotalAmount,

(Cost*ExchangeRate) AS CostEC,(Quantity* (Cost*ExchangeRate)) AS TotalAmountEC

FROM CostValue

Order BY SubsidiaryKey, WarehouseKey, ProductGroupKey, ProductKey

END