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

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

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

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

CREATE Function

[dbo].[rptOpeningBalance]

(

@StartDate Datetime,

@ToDate Datetime,

@ProductKey Nvarchar(200),

@WarehouseKey Nvarchar(200)

)

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 -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 --10000000000005

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

  ) X

  Group By X.ProductKey,X.BusinessDate

--

--select  * from #table return

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

--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