iVend Database Database : rptOpeningBalance_dailyClosingBal User Defined Function

Creation Date

12/9/2015 4:29 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@StartDate

In

DBTimeStamp

4

@ToDate

In

DBTimeStamp

4

@ProductKey

In

BigInt

8

@WarehouseKey

In

BigInt

8

@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

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  * From [dbo].[rptOpeningBalance_dailyClosingBal] ( '2015/08/31', '2015/09/11', '10000000001040', '10000000000005')

CREATE Function [dbo].[rptOpeningBalance_dailyClosingBal]

(

@StartDate Datetime,

@ToDate Datetime,

@ProductKey Bigint,

@WarehouseKey Bigint

)

Returns @table1 TABLE(Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint, RN Int)

As

Begin

--Declare @table1 TABLE(Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint, RN INT)

-- Declare @StartDate Datetime ='2015/06/24',  @ToDate Datetime ='2015/06/24',  @ProductKey Bigint =10000000003168, @WarehouseKey Bigint =10000000000004

Declare @DateTable TABLE (Rn int, [Date] Datetime)

Declare @table TABLE (Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint)

Declare @Opentable TABLE(Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint)

Declare @UpdateNextQty TABLE(Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint, RN INT)

Declare @UpdatePreQty TABLE(Qty Decimal(20,5), ProductKey bigint, CDate Datetime, WarehouseKey Bigint, RN INT)

Declare @temp Decimal(20,5), @DayCount int

Set @DayCount = DATEDIFF(D, @StartDate,@ToDate)+1

INSERT INTO @table

SELECT SUM(X.QuantityReceived) QuantityReceive, 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 WITH (NOLOCK) 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 ,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 WITH (NOLOCK) 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, 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 WITH (NOLOCK) 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 , 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

 T2.ProductKey = @ProductKey AND T1.ToWarehouseKey = @WarehouseKey And Convert(nVarchar,T1.BusinessDate,112)<= @ToDate

  UNION ALL

  SELECT

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

  FROM

 InvGoodsReturn T1 WITH (NOLOCK)

INNER JOIN InvGoodsReturnDetail T2 WITH (NOLOCK) 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, 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

 T2.ProductKey = @ProductKey AND T1.WarehouseKey = @WarehouseKey And Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  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

 T2.ProductKey = @ProductKey AND T2.WarehouseKey = @WarehouseKey And Convert(nVarchar, T1.BusinessDate, 112)<= @ToDate

  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

 T2.ProductKey = @ProductKey And T2.WarehouseKey = @WarehouseKey And Convert(nVarchar,T1.BusinessDate,112)<= @ToDate

  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

 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, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T1.WarehouseKey

  FROM

 InvProductBuildBreak T1 WITH (NOLOCK)

  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, Convert(nvarchar, T1.BusinessDate, 111) AS BusinessDate, T1.ToWarehouseKey

  FROM

 InvStockTransfer T1 WITH (NOLOCK)

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

WHERE

 PRODUCTKEY=@ProductKey AND T1.fromWarehouseKey = @WarehouseKey AND Convert(nvarchar, BusinessDate, 112) <=@ToDate

  ) X

  Group By X.ProductKey, X.BusinessDate, X.WarehouseKey

  INSERT INTO @Opentable (ProductKey, cDate, Qty, WarehouseKey)

SELECT T1.ProductKey, convert(nvarchar, T1.CDate, 111) CDate, (SELECT SUM(Qty)

FROM @table WHERE CDate<=T1.CDate) Qty

, T1.WarehouseKey

FROM

@table T1

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

ORDER BY T1.CDate

INSERT INTO @DateTable(RN, [Date])

Select RN, DATEADD(DAY, RN-1, @StartDate) AS [Date]

FROM

(

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

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

FROM sys.all_objects

 ) AS N

Insert Into @table1 (Qty, ProductKey, WarehouseKey, cDate, RN)

Select O.Qty,

ISNULL(O.ProductKey, @ProductKey) AS ProductKey, ISNULL(O.WarehouseKey, @WarehouseKey) AS WarehouseKey, R.Date, R.RN

from @DateTable R Left OUTER JOIN @Opentable O ON R.Date = O.CDate

--  Select * From @table1 Order by cDate

IF Exists (Select 1 from @table1 where Qty is NULL )

BEGIN

INSERT INTo @UpdateNextQty (Qty, ProductKey, CDate, WarehouseKey, RN)

Select (SELECT Min(A.Qty)

                FROM @table1 A

                WHERE A.Qty IS NOT NULL

    AND A.ProductKey = C.ProductKey AND A.WarehouseKey = C.WarehouseKey

    AND A.Rn < C.Rn

) AS Qty, C.ProductKey, C.CDate, C.WarehouseKey, C.RN

From @table1 C

WHERE Qty Is NULL

END

IF Exists (Select 1 from @table1 where Qty is NULL AND RN=1 )

BEGIN

INSERT INTo @UpdatePreQty (Qty, ProductKey, CDate, WarehouseKey, RN)

Select ( Select ISNULL(Abs(SUM(InQty)-Sum(OutQty)),0) From InvInventoryItemLog L

Where L.ProductKey = @ProductKey AND L.WarehouseKey =@WarehouseKey AND CONVERT(Varchar,Created,112)<@StartDate

 ) QTy, C.ProductKey, C.CDate, C.WarehouseKey, C.RN

From @table1 C

WHERE (Qty IS NULL)

END

IF Exists (Select 1 From @table1 Where (Qty IS NULL OR Qty = 0) AND RN<>1)

BEGIN

UPDATE T

Set T.Qty = (Select Qty From @UpdateNextQty N Where N.ProductKey = T.ProductKey AND N.WarehouseKey = T.WarehouseKey AND N.RN = T.RN)

FROM @table1 T WHERE T.QTY IS NULL

END

IF Exists (Select 1 From @table1 Where (Qty IS NULL OR Qty = 0) AND RN=1)

BEGIN

UPDATE T

Set T.Qty = (Select Qty From @UpdatePreQty N Where N.ProductKey = T.ProductKey AND N.WarehouseKey = T.WarehouseKey AND N.RN = T.RN)

FROM @table1 T WHERE T.QTY IS NULL

END

--Select * From @table1 Order by cDate

Return

END