iVend Database Database : rptOpeningBalance_Location 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

@LocationKey

In

VarWChar

200

@RETURN_VALUE

Return Value

Numeric

9

Database Object

Object Type

Description

Dep Level

Rpt_ItemWithoutSale

User Defined Function

1

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

InvInventoryLocationLog

Table

Stores the logs or Audit information of location for the Inventory available at different locations

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

InvLocation

Table

Save the location infomation.

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

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

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

----   Select [dbo].[rptOpeningBalance_Location] ( '2016/01/01', '2016/01/31', 10000000000000001, 10000000000000002, 10000000000000001)

CREATE Function [dbo].[rptOpeningBalance_Location]

(

@StartDate Datetime,

@ToDate Datetime,

@ProductKey NVARCHAR(200),

@WarehouseKey NVARCHAR(200),

@LocationKey NVARCHAR(200)

)

RETURNS DECIMAL(20,5)

AS

BEGIN

----Declare @StartDate Datetime='2016/01/01', @ToDate Datetime='2016/01/31',@ProductKey NVARCHAR(200) =10000000000000003, @WarehouseKey NVARCHAR(200)=10000000000000002,

--@LocationKey NVARCHAR(200)=10000000000000001

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(ISNULL(X.QuantityReceived,0)) As Qty, X.ProductKey,X.BusinessDate

FROM

(

SELECT (ISNULL(T1.InQty,0) - ISNULL(T1.OutQty,0)) 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 ISNULL(T1.LocationKey,0)=@LocationKey And Convert(nVarchar, T1.Created,112)<= @ToDate

UNION ALL

SELECT (ISNULL(T1.InQty,0) - ISNULL(T1.OutQty,0)), 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 ISNULL(T1.LocationKey,0)=@LocationKey And Convert(nVarchar, T1.Created,112)<= @ToDate

UNION ALL

SELECT (ISNULL(T1.InQty,0) - ISNULL(T1.OutQty,0)), 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 ISNULL(T1.LocationKey,0)=@LocationKey And Convert(nVarchar, T1.Created,112)<= @ToDate

UNION ALL

SELECT ISNULL(T2.QuantityReceived,0), 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 @LocationKey= CASE WHEN ISNULL(T1.ToLocationKey,0)=0 THEN (Select  LocationKey From InvLocation Where IsDefault=1 and WarehouseKey=@WarehouseKey) ELSE ISNULL(T1.ToLocationKey,0) END

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

UNION ALL

SELECT -1*ISNULL(T2.Quantity,0), 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 ISNULL(T1.LocationKey,0)=@LocationKey And Convert(nVarchar,T1.Created,112)<= @ToDate

UNION ALL

SELECT -1*ISNULL(T2.Quantity,0), 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 ISNULL(T1.LocationKey,0)=@LocationKey And Convert(nVarchar,T1.Created,112)<= @ToDate

UNION ALL

SELECT -1*ISNULL(T2.Quantity,0), T2.ProductKey, T1.BusinessDate

FROM TrxTransaction T1

  INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.TransactionKey and T2.Type !=4 and T2.Type !=1 AND T1.IsSuspended = 0 AND T1.IsVoided = 0

Where T2.ProductKey=@ProductKey AND T2.WarehouseKey=@WarehouseKey AND ISNULL(T2.DeliveryLocationKey,0)=@LocationKey And Convert(nVarchar,T1.ActualDate,112)<= @ToDate and HasFulFillments=0

UNION ALL

SELECT ISNULL(T2.Quantity,0), T2.ProductKey, T1.BusinessDate

FROM TrxTransaction T1

  INNER JOIN TrxTransactionSaleItem T2 on T2.TransactionKey=T1.TransactionKey and T2.Type=1   AND T1.IsSuspended = 0 AND T1.IsVoided = 0

WHERE T2.ProductKey=@ProductKey And T2.WarehouseKey=@WarehouseKey AND ISNULL(T2.DeliveryLocationKey,0)=@LocationKey And Convert(nVarchar,T1.ActualDate,112)<= @ToDate

UNION ALL

SELECT CASE WHEN T1.TYPE=0 THEN -1 ELSE 1 END * ISNULL(T2.Quantity,0), 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 ISNULL(T1.KitLocationKey,0)=@LocationKey And Convert(nVarchar,T1.Created,112)<= @ToDate

UNION ALL

SELECT CASE WHEN T1.TYPE=0 THEN 1 ELSE -1 END * ISNULL(T1.Quantity,0), T1.ProductKey, T1.BusinessDate

FROM InvProductBuildBreak T1

WHERE T1.ProductKey=@ProductKey and T1.Quantity>0 And T1.WarehouseKey=@WarehouseKey AND ISNULL(T1.KitLocationKey,0)=@LocationKey And Convert(nVarchar,T1.Created,112)<= @ToDate

UNION ALL

SELECT -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 ISNULL(T1.FromLocationKey,0)=@LocationKey AND Convert(nvarchar,T1.Created, 112) <=@ToDate

UNION ALL

SELECT ISnull(T1.InQty,0)-Isnull(T1.OutQty,0), T2.ProductKey, T1.Created

FROM InvInventoryLocationLog T1

  INNER JOIN InvProduct T2 WITH (NOLOCK) on T1.ProductKey = T2.ProductKey

  INNER JOIN InvLocation T3 WITH (NOLOCK) on T1.LocationKey = T3.LocationKey

WHERE SourceType=17 AND T1.ProductKey=@ProductKey AND T1.WarehouseKey=@WarehouseKey AND ISNULL(T1.LocationKey,0)=@LocationKey AND Convert(nvarchar,T1.Created, 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

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

WHERE T1.SourceType=8 and OutQty>0

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

SELECT @Temp=Sum(ISNULL(Qty,0)) FROM @Table --ORDER BY CDate Desc

--Select @Temp

RETURN @Temp

END