iVend Database Database : Daily_ProductCost User Defined Function

Creation Date

9/17/2019 9:19 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

VarChar

8

@ProductKey

In

VarWChar

400

@WarehouseKey

In

VarWChar

400

@Subsidiarykey

In

VarWChar

400

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

InvInventoryItemLog

Table

Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated.

1

InvProduct

Table

Stores the product related details.

1

InvProductCost

Table

Store the historical cost of the product.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RtlSubsidiary

Table

Defines all the Subsidiaries defined in the application

1

CREATE FUNCTION [dbo].[Daily_ProductCost](

@FromDate Varchar(8),

@ToDate Varchar(8),

@ProductKey NVARCHAR(400),

@WarehouseKey NVARCHAR(400),

@Subsidiarykey NVARCHAR(400)

)

Returns

@UpdateCost Table(Rn int, DR Int, Date DateTime, ProductKey NVARCHAR(400), Product NVARCHAR(100), ProductDesc Nvarchar(400), WarehouseKey NVARCHAR(400),

Warehouse NVARCHAR(100), WarehouseDesc NVARCHAR(400), CostingMethod Int, CostingSubMethod Int, Cost Decimal(20,4),SubsidiaryKey NVARCHAR(400))

AS

BEGIN

--  Select * From dbo.Daily_ProductCost('20160101', '20160630', 10000000000000001, 10000000000000002,0)

--Declare @FromDate Varchar(8)='20160601', @ToDate Varchar(8)='20160630', @ProductKey Bigint=10000000000000003,

-- @WarehouseKey Bigint=10000000000000004, @SubsidiaryKey BIGINT=0

DECLARE @DayCount INT, @CostingMethod INT

SET @DayCount = DATEDIFF(D, @FromDate,@ToDate)+1

Declare @InventoryData Table(Rn int, DR Int, Date DateTime, ProductKey NVARCHAR(400), Product nVarchar(100), ProductDesc Nvarchar(400), WarehouseKey NVARCHAR(400),

 Warehouse NVARCHAR(100), WarehouseDesc NVARCHAR(400), CostingMethod Int, CostingSubMethod Int,Subsidiarykey NVARCHAR(400))

Declare @DateTable TABLE (RN int Identity(1,1), DR Int, [Date] Datetime)

--Declare @UpdateCost Table(Rn int, DR Int, Date DateTime, ProductKey Bigint, Product nVarchar(100), ProductDesc Nvarchar(400), WarehouseKey Bigint,

--Warehouse NVARCHAR(100), WarehouseDesc NVARCHAR(400), CostingMethod Int, CostingSubMethod Int, Cost Decimal(20,4),SubsidiaryKey Bigint)

INSERT INTO @DateTable(DR, [Date])

Select DENSE_RANK() OVER(ORDER BY [Date]) AS DR, [Date] FROM(

SELECT DATEADD(DAY, RN-1, @FromDate) AS [Date] FROM (

SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate) + 1)

   RN = ROW_NUMBER() OVER (ORDER BY [object_id])

FROM sys.all_objects

 ) AS N)Q

;WITH Inv AS

 (

SELECT P.ProductKey, P.Id Product, P.Description As ProductDesc, Wh.WarehouseKey, Wh.Id As Warehouse, Wh.Description AS WarehouseDesc,

  ISNULL(P.CostingMethod,0) AS CostingMethod, ISNULL(P.CostingSubMethod,0) AS CostingSubMethod, ISNULL(RS.SubsidiaryKey, '0') AS SubsidiaryKey

FROM InvProduct P

CROSS JOIN InvWarehouse Wh

LEFT OUTER JOIN RtlSubsidiary RS on Wh.SubsidiaryKey=RS.SubsidiaryKey

Where ProductKey=@ProductKey AND (@WarehouseKey= WarehouseKey OR @WarehouseKey=0) and (@SubsidiaryKey= RS.SubsidiaryKey OR @SubsidiaryKey=0)

 )

INSERT INTO @InventoryData (Rn, DR, Date, ProductKey, Product, ProductDesc, WarehouseKey, Warehouse, WarehouseDesc, CostingMethod, CostingSubMethod, Subsidiarykey)

SELECT R.Rn, R.DR, R.Date, Inv.* From @DateTable AS R, Inv

SELECT Top 1 @CostingMethod=CostingMethod FROM @InventoryData Where ProductKey=@ProductKey and ISNULL(WarehouseKey, '0') IN (@WarehouseKey, '0')

OR (ISNULL(SubsidiaryKey, '0') IN ( @SubsidiaryKey, '0'))

-- SELECT 'INV', * FROM @InventoryData

--Select * from InvProductCost Where ProductKey=10000000000000003

IF @CostingMethod=2

BEGIN

    INSERT INTO @UpdateCost (Rn, DR, Date, ProductKey, Product, ProductDesc, WarehouseKey, Warehouse, WarehouseDesc, CostingMethod, CostingSubMethod, Cost,Subsidiarykey)

  SELECT Q.Rn, Q.DR, Q.Date, Q.ProductKey, Q.Product, Q.ProductDesc, Q.WarehouseKey, Q.Warehouse, Q.WarehouseDesc, Q.CostingMethod, Q.CostingSubMethod, ISNULL(PC.Cost,0)AS Cost,

  ISNULL(Q.Subsidiarykey, '0') AS Subsidiarykey

  FROM @InventoryData Q

  LEFT OUTER JOIN InvProductCost PC ON PC.ProductKey = Q.ProductKey

  AND PC.WarehouseKey IN (Q.WarehouseKey, 0)

  AND Convert(Varchar(10),PC.EffectiveDate,120)= Convert(Varchar(10),Q.Date,120)

  AND ISNULL(PC.Subsidiarykey, '0')= ISNULL(Q.SubsidiaryKey, '0')

  IF Exists (Select 1 From @UpdateCost Where (Cost IS NULL OR Cost = 0) )

  BEGIN

  UPDATE T Set T.Cost =

   (Select Max(Cost) From @UpdateCost N Where N.ProductKey = T.ProductKey

    AND N.WarehouseKey IN  (T.WarehouseKey,0) AND ISNULL(N.SubsidiaryKey, '0')=ISNULL(T.SubsidiaryKey, '0') AND N.RN < T.RN )

  FROM @UpdateCost T WHERE (Cost IS NULL OR Cost = 0)

  END

-- ----------------**********UPDate First values if comes null************-----------------

  IF Exists (Select 1 From @UpdateCost Where (Cost IS NULL OR Cost = 0) )

  BEGIN

  UPDATE T Set T.Cost = (SELECT MAX(ISNULL(Cost,0)) FROM InvProductCost T1 WHERE (T1.EffectiveDate < @FromDate

        AND (Cost IS not NULL OR Cost=0))AND T1.ProductKey = @ProductKey AND T1.WarehouseKey In (@WarehouseKey, 0)

        AND ISNULL(T1.SubsidiaryKey, '0')=ISNULL(@SubsidiaryKey, '0'))

  FROM @UpdateCost T WHERE (Cost IS NULL OR Cost = 0)

  END

END

-- SELECT 'Costmethod2', * FROM @UpdateCost

----Select 'Costmethod2',* From @UpdateCost Where ProductKey =@ProductKey AND WarehouseKey IN (@WarehouseKey, 0)

IF @CostingMethod=1

BEGIN

    INSERT INTO @UpdateCost (Rn, DR, Date, ProductKey, Product, ProductDesc, WarehouseKey, Warehouse, WarehouseDesc, CostingMethod, CostingSubMethod, Cost, Subsidiarykey)

  SELECT Q.Rn, Q.DR, Q.Date, Q.ProductKey, Q.Product, Q.ProductDesc, Q.WarehouseKey, Q.Warehouse, Q.WarehouseDesc, Q.CostingMethod, Q.CostingSubMethod,

  ISNULL(MAX(ISNULL(IL.UpdatedAverageCost, 0)),0) AS Cost, ISNULL(Q.SubsidiaryKey, '0') AS Subsidiarykey

  FROM @InventoryData Q

  LEFT OUTER JOIN InvInventoryItemLog IL ON Q.ProductKey = IL.ProductKey AND Q.WarehouseKey=IL.WarehouseKey AND Q.Date=Convert(Varchar(10), IL.Created,120)

  WHERE Q.ProductKey = @ProductKey AND Q.WarehouseKey IN (@WarehouseKey, 0)

  Group BY Q.Rn, Q.DR, Q.Date, Q.ProductKey, Q.Product, Q.ProductDesc, Q.WarehouseKey, Q.Warehouse, Q.WarehouseDesc, Q.CostingMethod, Q.CostingSubMethod,ISNULL(Q.Subsidiarykey, '0')

  IF Exists (Select 1 From @UpdateCost Where (Cost IS NULL OR Cost = 0) )

  BEGIN

  UPDATE T Set T.Cost = (Select Max(Cost) From @UpdateCost N Where N.ProductKey = T.ProductKey AND N.WarehouseKey =T.WarehouseKey AND N.RN <= T.RN )

  FROM @UpdateCost T WHERE (Cost IS NULL OR Cost = 0)

  END

-- --------------**********UPDate First values if comes null************-----------------

  IF Exists (Select 1 From @UpdateCost Where (Cost IS NULL OR Cost = 0) )

  BEGIN

  UPDATE T Set T.Cost = (SELECT MAX(ISNULL(T1.UpdatedAverageCost, 0)) FROM InvInventoryItemLog T1 WHERE

        Convert(Varchar(10), T1.Created,112)< @FromDate AND (Cost IS not NULL OR Cost=0) AND T1.ProductKey = @ProductKey

        AND T1.WarehouseKey =@WarehouseKey)

  FROM @UpdateCost T WHERE (Cost IS NULL OR Cost = 0)

  END

END

IF @CostingMethod=0

BEGIN

    INSERT INTO @UpdateCost (Rn, DR, Date, ProductKey, Product, ProductDesc, WarehouseKey, Warehouse, WarehouseDesc, CostingMethod, CostingSubMethod, Cost,Subsidiarykey)

  SELECT Q.Rn, Q.DR, Q.Date, Q.ProductKey, Q.Product, Q.ProductDesc, Q.WarehouseKey, Q.Warehouse, Q.WarehouseDesc, Q.CostingMethod, Q.CostingSubMethod,

  ISNULL(MAX(ISNULL(IL.UpdatedAverageCost, 0)),0) AS Cost ,ISNULL(Q.Subsidiarykey, '0') AS Subsidiarykey

  FROM @InventoryData Q

  LEFT OUTER JOIN InvInventoryItemLog IL ON Q.ProductKey = IL.ProductKey AND Q.Date=Convert(Varchar(10), IL.Created,120)

  WHERE Q.ProductKey = @ProductKey AND Q.WarehouseKey IN (@WarehouseKey, 0)

  Group BY Q.Rn, Q.DR, Q.Date, Q.ProductKey, Q.Product, Q.ProductDesc, Q.WarehouseKey, Q.Warehouse, Q.WarehouseDesc, Q.CostingMethod, Q.CostingSubMethod

  ,ISNULL(Q.Subsidiarykey, '0')

END

--Select 'SAURABH3',* From @UpdateCost Where ProductKey =@ProductKey AND WarehouseKey IN (@WarehouseKey, 0)

Return

END