iVend Database Database : rptOpeningBalance_Stock User Defined Function

Creation Date

9/17/2019 9:19 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@StartDate

In

DBTimeStamp

4

@ToDate

In

DBTimeStamp

4

@ProductKey

In

VarWChar

200

@WarehouseKey

In

VarWChar

200

@RETURN_VALUE

Return Value

Numeric

9

Database Object

Object Type

Description

Dep Level

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvGoodsReturn

Table

Stores the data related to Inventory Goods Return

1

InvGoodsReturnDetail

Table

Stores the details of inventory goods return

1

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

InvInventoryTransaction

Table

This gets affected when the inventory of a particular item is consumed at the store itself for any reason.

1

InvInventoryTransactionDetail

Table

Defines the details of the product on the goods issue note

1

InvProduct

Table

Stores the product related details.

1

InvProductBuildBreak

Table

Stores the information when the product is Build or Broken into its components

1

InvProductBuildBreakDetail

Table

Defines the product details which is being built or break.

1

InvProductGroup

Table

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

1

-- SELECT * FROM dbo.InvInventoryItemLog WHERE ProductKey =10000000000000002 AND WarehouseKey=10000000000000003

-- SELECT  dbo.rptOpeningBalance_Stock('20160426', '20160426', 30000000000000020, 10000000000000003)

CREATE FUNCTION

[dbo].[rptOpeningBalance_Stock]

(

@StartDate DATETIME,

@ToDate DATETIME,

@ProductKey NVARCHAR(200),

@WarehouseKey NVARCHAR(200)

)

--DECLARE @StartDate DATETIME='20160626', @ToDate DATETIME='20160626',  @ProductKey NVARCHAR(200), @WarehouseKey NVARCHAR(200)

--SELECT @ProductKey =10000000000000006, @WarehouseKey=10000000000000004

RETURNS DECIMAL(20,5)

AS

BEGIN

DECLARE @table TABLE(Qty DECIMAL(20,5),ProductKey NVARCHAR(200),CDate DATETIME)

DECLARE @table1 TABLE(Qty DECIMAL(20,5),ProductKey NVARCHAR(200),CDate DATETIME)

DECLARE @temp DECIMAL(20,5)

INSERT INTO @table

SELECT SUM(X.QuantityReceived), X.ProductKey,X.BusinessDate

FROM

 (

SELECT (T1.InQty - T1.OutQty) AS QuantityReceived, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=-1 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT T1.InQty - T1.OutQty, T3.ProductKey, T1.Created

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=-99 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT T1.InQty - T1.OutQty, T3.ProductKey, T1.Created

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=0 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT T2.QuantityReceived, T2.ProductKey, T1.BusinessDate

FROM InvGoodReceipt T1

INNER JOIN InvGoodReceiptDetail T2 ON T2.GoodsReceiptKey=T1.GoodsReceiptKey

WHERE T2.ProductKey=@ProductKey AND T1.ToWarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,T1.BusinessDate,112)<= @ToDate

UNION ALL

SELECT -1*T2.Quantity, T2.ProductKey, T1.ReturnDate

FROM InvGoodsReturn T1

INNER JOIN InvGoodsReturnDetail T2 ON T2.GoodsReturnKey=T1.GoodsReturnKey

WHERE T2.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,T1.ReturnDate,112)<= @ToDate

UNION ALL

SELECT -1*T2.Quantity, T2.ProductKey, T1.BusinessDate

FROM InvInventoryTransaction T1

INNER JOIN InvInventoryTransactionDetail T2 ON T1.InventoryTransactionKey=T2.InventoryTransactionKey

WHERE T2.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,T1.BusinessDate,112)<= @ToDate

UNION ALL

  SELECT (- T1.OutQty) AS QuantityReceived, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

WHERE T1.SourceType=7 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT (T1.InQty) AS QuantityReceived, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

WHERE T1.SourceType=7 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT CASE WHEN T1.Type=0 THEN -1 ELSE 1 END * T2.Quantity, T2.ProductKey, T1.BusinessDate

FROM  InvProductBuildBreak T1

INNER JOIN InvProductBuildBreakDetail T2 ON T2.ProductBuildBreakKey =T1.ProductBuildBreakKey AND T2.Quantity>0

WHERE T2.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,T1.BusinessDate,112)<= @ToDate

UNION ALL

SELECT CASE WHEN T1.Type=0 THEN 1 ELSE -1 END * T1.Quantity, T1.ProductKey, T1.BusinessDate

FROM  InvProductBuildBreak T1

WHERE T1.ProductKey=@ProductKey AND T1.Quantity>0 AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,T1.BusinessDate,112)<= @ToDate

--UNION ALL

-- SELECT 11, -ISNULL(T2.QuantityReceived,0),T2.ProductKey,T1.BusinessDate

-- FROM InvStockTransfer T1

--  INNER JOIN InvStockTransferDetail T2 WITH (NOLOCK) ON T1.StockTransferKey = T2.StockTransferKey

-- WHERE PRODUCTKEY=@ProductKey AND T1.fromWarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR,BusinessDate, 112) <=@ToDate

UNION ALL

SELECT (- T1.OutQty) AS QuantityReceived, T3.ProductKey, T1.Created AS BusinessDate --- edit by Saurabh sharma on 19-3-2016

FROM

 InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=8

AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

  SELECT ( T1.INQty) AS QuantityReceived, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=8 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT -(T1.OutQty) AS Quantity, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=4 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

UNION ALL

SELECT T1.InQty AS Quantity, T3.ProductKey, T1.Created AS BusinessDate

FROM InvInventoryItemLog T1

INNER JOIN InvProduct T3 ON T1.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T5 ON T3.ProductGroupKey=T5.ProductGroupKey

WHERE T1.SourceType=4 AND T3.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND CONVERT(NVARCHAR, T1.Created,112)<= @ToDate

  ) X

  GROUP BY X.ProductKey,X.BusinessDate

--

--select  * from @table

INSERT INTO @table1(ProductKey,cDate,Qty)

SELECT T1.ProductKey,CONVERT(NVARCHAR, T1.CDate, 112),(SELECT SUM(Qty) FROM @table WHERE CDate<=T1.CDate)

FROM @table T1

WHERE CONVERT(NVARCHAR, T1.CDate ,112) >= @StartDate AND CONVERT(NVARCHAR,T1.CDate, 112) <= @ToDate

ORDER BY T1.CDate

--SELECT T1.ProductKey,convert(nvarchar, T1.CDate ,112),(select SUM(Qty) from @table where CDate<=T1.CDate)

--FROM @table T1

--Where convert(nvarchar, T1.CDate ,112) >=   @StartDate and CONVERT(NVARCHAR,T1.CDate, 112) <= @ToDate

--Order by T1.CDate

-- SELECT * FROM @table

----if exists (select 1 from @table1 Group by ProductKey)

---- select @temp= AVG(Qty) from @table1 Group by ProductKey

----else

SELECT @temp=SUM(Qty) FROM @table --order by CDate Desc

RETURN @temp

END