iVend Database Database : rptClosingBalanceQuantity User Defined Function

Creation Date

5/30/2016 7:01 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@ToDate

In

DBTimeStamp

4

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

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

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 Function [dbo].[rptClosingBalanceQuantity]

(@ToDate Datetime)

--DECLARE @ToDate Datetime='20160810', @ProductKey Bigint='', @WarehouseKey Bigint=''

--DECLARE @table1 TABLE(Qty Decimal(20,5), ProductKey BIGINT, Warehousekey Bigint, BusinessDate DateTime)

RETURNS @table1 TABLE(Qty Decimal(20,5), ProductKey BIGINT, Warehousekey Bigint)

AS

BEGIN

Declare @table TABLE(Qty Decimal(20,5), ProductKey BIGINT, BusinessDate DateTime, Warehousekey Bigint)

INSERT INTO @TABLE

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

FROM

 (

    SELECT

   (T1.InQty - T1.OutQty) AS QuantityReceived, T3.ProductKey, Convert(nvarchar, T1.Created, 111) AS BusinessDate, T1.WarehouseKey

  FROM

   InvInventoryItemLog T1 WITH (NOLOCK)

  INNER JOIN InvProduct T3 WITH (NOLOCK) ON T1.ProductKey=T3.ProductKey

  --INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey

  WHERE

   T1.SourceType=-1 And Convert(nVarchar, T1.Created,112)<= @ToDate

  --AND T3.ProductKey=ISNULL(@ProductKey,0) AND T1.WarehouseKey=ISNULL(@WarehouseKey, 0)

UNION ALL

  SELECT

   T1.InQty - T1.OutQty, T3.ProductKey ,Convert(nvarchar, T1.Created, 111) AS BusinessDate, T1.WarehouseKey

  FROM

   InvInventoryItemLog T1 WITH (NOLOCK)

  INNER JOIN InvProduct T3 WITH (NOLOCK) ON T1.ProductKey=T3.ProductKey

  --INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey

  WHERE

   T1.SourceType=-99 And Convert(nVarchar, T1.Created,112)<= @ToDate

  --AND T3.ProductKey=ISNULL(@ProductKey,0) AND T1.WarehouseKey=ISNULL(@WarehouseKey, 0)

UNION ALL

  SELECT

   T1.InQty - T1.OutQty, T3.ProductKey, Convert(nvarchar, T1.Created, 111) AS BusinessDate, T1.WarehouseKey

  FROM

   InvInventoryItemLog T1 WITH (NOLOCK)

  INNER JOIN InvProduct T3 WITH (NOLOCK) ON T1.ProductKey=T3.ProductKey

  --INNER JOIN InvProductGroup T5 on T3.ProductGroupKey=T5.ProductGroupKey

  WHERE

   T1.SourceType=0 And Convert(nVarchar, T1.Created,112)<= @ToDate

  --AND T3.ProductKey = ISNULL(@ProductKey,0) AND T1.WarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

  SELECT

   T2.QuantityReceived ,T2.ProductKey , Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T1.ToWarehouseKey AS WarehouseKey

  FROM

   InvGoodReceipt T1 WITH (NOLOCK)

  INNER JOIN InvGoodReceiptDetail T2 WITH (NOLOCK) ON T2.GoodsReceiptKey=T1.GoodsReceiptKey

  WHERE

  Convert(nVarchar,T1.BusinessDate,112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) AND T1.ToWarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

  SELECT

  -1*T2.Quantity, T2.ProductKey, Convert(nvarchar, T1.Created, 111) AS BusinessDate, T1.WarehouseKey

  FROM

   InvGoodsReturn T1 WITH (NOLOCK)

  INNER JOIN InvGoodsReturnDetail T2 WITH (NOLOCK) ON T2.GoodsReturnKey=T1.GoodsReturnKey

  WHERE

  Convert(nVarchar, T1.Created, 112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) AND T1.WarehouseKey = ISNULL(@WarehouseKey, 0)

  UNION ALL

    SELECT

  -1*T2.Quantity, T2.ProductKey, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T1.WarehouseKey

    FROM

   InvInventoryTransaction T1 WITH (NOLOCK)

  INNER JOIN InvInventoryTransactionDetail T2 WITH (NOLOCK) ON T1.InventoryTransactionKey=T2.InventoryTransactionKey

    WHERE

  Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) AND T1.WarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

    SELECT

  -1*T2.Quantity, T2.ProductKey, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T2.WarehouseKey

    FROM

   TrxTransaction T1 WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem T2 WITH (NOLOCK) ON T2.TransactionKey=T1.TransactionKey

  and T2.Type !=4 and T2.Type !=1 AND T1.IsSuspended = 0 AND T1.IsVoided = 0

    WHERE

  Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) AND T2.WarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

    SELECT

   T2.Quantity, T2.ProductKey, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T2.WarehouseKey

    FROM

   TrxTransaction T1 WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem T2 WITH (NOLOCK) ON T2.TransactionKey=T1.TransactionKey

  AND T2.Type=1 AND T1.IsSuspended = 0 AND T1.IsVoided = 0

    WHERE

  Convert(nVarchar,T1.BusinessDate,112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) And T2.WarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

    SELECT

  Case When T1.Type=0 then -1 else 1 end * T2.Quantity, T2.ProductKey, Convert(nvarchar, T1.BusinessDate, 111) As BusinessDate,

   T1.WarehouseKey

    FROM

   InvProductBuildBreak T1 WITH (NOLOCK)

  INNER JOIN InvProductBuildBreakDetail T2 WITH (NOLOCK) ON T2.ProductBuildBreakKey = T1.ProductBuildBreakKey and T2.Quantity>0

    WHERE

  Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  --AND T2.ProductKey = ISNULL(@ProductKey,0) And T1.WarehouseKey = ISNULL(@WarehouseKey, 0)

  UNION ALL

    SELECT

  Case When T1.Type=0 then 1 else -1 end * T1.Quantity, T1.ProductKey, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T1.WarehouseKey

    FROM

   InvProductBuildBreak T1 WITH (NOLOCK)

    WHERE

  Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  --AND T1.ProductKey = ISNULL(@ProductKey,0) and T1.Quantity>0  And T1.WarehouseKey = ISNULL(@WarehouseKey, 0)

UNION ALL

  SELECT

  -(T1.OutQty) AS QuantityReceived, T3.ProductKey, Convert(nvarchar, T1.Created, 111) AS BusinessDate, T1.WarehouseKey

  FROM

   InvInventoryItemLog T1 WITH (NOLOCK)

  INNER JOIN InvProduct T3 WITH (NOLOCK) ON T1.ProductKey=T3.ProductKey

  WHERE

   T1.SourceType=4 And Convert(nVarchar, T1.Created,112)<= @ToDate

  --AND T3.ProductKey=ISNULL(@ProductKey,0) AND T1.WarehouseKey=ISNULL(@WarehouseKey, 0)

  ) X

  Group By X.ProductKey, X.Warehousekey, X.BusinessDate

--SELECT  * FROM @TABLE  Order BY ProductKey, Warehousekey

INSERT INTO @table1(Qty, ProductKey, WarehouseKey)

SELECT Sum(Qty) AS Qty, ProductKey, Warehousekey

FROM @table

Where BusinessDate<=@ToDate

Group BY ProductKey, Warehousekey

Order BY ProductKey, Warehousekey

--Select * from @Table1 --Where ProductKey=@ProductKey

RETURN

END