iVend Database Database : RPT_DailyClosingBalance Stored Procedure

Creation Date

9/17/2019 9:19 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

VarChar

8

@ProductGroup

In

VarWChar

200

@FromProduct

In

VarWChar

400

@ToProduct

In

VarWChar

400

@Warehouse

In

VarWChar

400

@Subsidiary

In

VarWChar

200

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[RPT_DailyClosingBalance]

(

@FromDate Varchar(8),

@ToDate Varchar(8),

@ProductGroup NVarchar(200),

@FromProduct nVarchar(400),

@ToProduct nVarchar(400),

@Warehouse Nvarchar(400),

@Subsidiary NVarchar(200)

)

AS

BEGIN

--EXEC [RPT_DailyClosingBalance] '20170709','20170709', '', '','', 'PeterOdili', ''

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL

  DROP TABLE #DateTable

IF OBJECT_ID('tempdb..#UpdatePreQty') IS NOT NULL

  DROP TABLE #UpdatePreQty

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

  DROP TABLE #Temp

Declare @DayCount int

--,@FromDate Varchar(8)= '20170628', @ToDate Varchar(8) ='20170709', @FromProduct nVarchar(400) = '001092', @ToProduct nVarchar(400) = '001100',

-- @Warehouse Nvarchar(400) ='PeterOdili', @Subsidiary NVARCHAR(1000)=''

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

-- Select @DayCount

-----*********** Declare Table Variable for Insert data!!!

CREATE TABLE #DateTable (RN int Identity(1,1), [Date] Datetime)

CREATE TABLE #Temp (

RowLine Int, RN Int, BusinessDate DATETIME,

ProductKey NVARCHAR(200), ProductId Nvarchar(100), ProductName NVARCHAR(200),

WarehouseKey NVARCHAR(200), WarehouseId NVARCHAR(100), WarehouseName NVARCHAR(200), SubsidiaryId NVARCHAR(100), SubsidiaryName NVARCHAR(200),

ClosingBalance Decimal(10,2), QtySold Decimal(10,2), CostItemSold Decimal(10,2), NosOfDays INT

)

INSERT INTO #DateTable([Date])

SELECT DATEADD(DAY, RN-1, @FromDate) AS [Date] FROM (

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

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

FROM sys.all_objects

 ) AS N

;WITH S AS

(

Select X.ProductKey, X.ProductId, X.ProductName, X.WarehouseKey, X.WarehouseId, X.WarehouseName, X.SubsidiaryId, X.SubsidiaryName, X.BusinessDate,

X.ClosingBalance as ClosingBalance, SUM(X.QtySold) AS QtySold, Sum(X.CostItemSold) as ItemSold

FROM

(

SELECT distinct B.ProductKey, B.WarehouseKey, (case when B.TYPE=1 then B.Quantity*-1 else B.Quantity end) AS QtySold,

(Closing.Qty) AS ClosingBalance,

((case when B.TYPE=1 then B.Quantity*-1 else B.Quantity end)*

(CASE C.CostingMethod

WHEN 1 THEN B.AverageCost

WHEN 2 THEN (Select Top 1 Cost From InvProductCost PC Where PC.ProductKey=C.ProductKey AND PC.WarehouseKey IN (D.WarehouseKey,0) AND ISNULL(PC.SubsidiaryKey,0)=ISNULL(E.SubsidiaryKey,0)AND PC.EffectiveDate<= A.BusinessDate Order BY EffectiveDate DESC) END

)) As CostItemSold,

C.Id as ProductId, C.Description as ProductName, D.Id as WarehouseId, D.Description as WarehouseName, E.Id As SubsidiaryId, E.Description AS SubsidiaryName,

Convert(Varchar, A.Created, 111) AS BusinessDate

FROM TrxTransaction A WITH (NOLOCK)

JOIN TrxTransactionSaleItem B WITH (NOLOCK) ON A.TransactionKey = B.TransactionKey

JOIN InvProduct C WITH (NOLOCK) ON B.ProductKey = C.ProductKey

JOIN InvProductGroup PG WITH (NOLOCK) ON C.ProductGroupKey = PG.ProductGroupKey

JOIN InvWarehouse D WITH (NOLOCK) ON B.WarehouseKey = D.WarehouseKey

LEFT OUTER JOIN RtlSubsidiary E WITH (NOLOCK) ON Isnull(D.SubsidiaryKey,0) = ISNULL(E.SubsidiaryKey,0)

OUTER APPLY dbo.rptClosingBalanceQuantity(@ToDate) AS Closing

WHERE

B.TYPE != 4 --THIS IS DELIVERY AGAINST SALE

AND A.IsVoided = 0 AND A.IsSuspended = 0

AND B.ProductKey=Closing.ProductKey AND B.WarehouseKey=Closing.WarehouseKey

AND CHARINDEX (PG.Id , @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

AND ISNULL (E.Id ,'') = CASE WHEN @Subsidiary <> '' THEN @Subsidiary ELSE ISNULL(E.Id,'') END

AND CHARINDEX (D.Id ,@Warehouse) > CASE WHEN @Warehouse <> '' THEN 0 ELSE -1 END

AND C.Id BETWEEN CASE WHEN @FromProduct = '' THEN C.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN C.Id ELSE @ToProduct END

And Convert(Varchar, A.Created, 112)>= CASE WHEN @FromDate <>'' THEN @FromDate ELSE '' END

And Convert(Varchar, A.Created, 112)<= CASE WHEN @ToDate <>'' THEN @ToDate ELSE '' END

) X

Group By X.ProductKey, X.ProductId, X.ProductName, X.WarehouseKey, X.WarehouseId, X.WarehouseName, X.SubsidiaryId, X.SubsidiaryName, X.BusinessDate, X.ClosingBalance--, X.CostItemSold

)

INSERT INTO #Temp

(

RowLine, RN, BusinessDate, ProductKey, ProductId, ProductName, WarehouseKey, WarehouseId, WarehouseName, SubsidiaryId, SubsidiaryName, ClosingBalance, QtySold, CostItemSold, NosOfDays

)

Select

----Sum(1) OVER(PARTITION BY Date ORDER BY Date ROWs UNBOUNDED PRECEDING) AS RowLine,

NTILE(CASE WHEN @DayCount<=3 THEN 4 ELSE @DayCount END) Over(Partition By Rn Order By Rn) RowLine,

T.RN, T.Date, S.ProductKey, S.ProductId, S.ProductName, S.WarehouseKey, S.WarehouseId, S.WarehouseName, S.SubsidiaryId, S.SubsidiaryName, S.ClosingBalance,

ISNULL(S.QtySold,0) AS QtySold, ISNULL(S.ItemSold,0) AS ItemSold, NosOfDays

--into #t

From

(

Select S1.ProductID, S1.WarehouseId, S1.SubsidiaryId, R.Date, R.RN, (DATEDIFF(DAY, CONVERT(DateTime,@FromDate,112),CONVERT(DateTime,@ToDate,112))+1) [NosOfDays]

From #DateTable R LEFT OUTER JOIN

(

Select distinct ProductID, WarehouseId, SubsidiaryId From S

 ) As S1 On 1=1

) T

Left Outer Join S On T.ProductId = S.ProductID AND T.WarehouseID= S.WarehouseId And T.Date = S.BusinessDate AND ISNULL(T.SubsidiaryId,'')=ISNULL(S.SubsidiaryId,'')

Order By T.ProductId, T.WarehouseId, T.Date, T.SubsidiaryId

UPDATE T1 Set T1.ProductKey = T2.ProductKey, T1.ProductId = T2.ProductId, T1.ProductName = T2.ProductName

From #Temp T1 Inner Join (Select Distinct RowLine, ProductKey, ProductId, ProductName From #Temp Where ProductKey Is not Null)

AS T2

ON T1.RowLine=T2.RowLine Where T1.ProductKey Is NUll

UPDATE T1 Set T1.WarehouseKey = T2.WarehouseKey, T1.WarehouseId = T2.WarehouseId, T1.WarehouseName = T2.WarehouseName

From #Temp T1 Inner Join (Select Distinct RowLine, WarehouseKey, WarehouseId, WarehouseName From #Temp Where WarehouseKey Is not Null) AS T2

ON T1.RowLine=T2.RowLine Where T1.WarehouseKey Is NUll

UPDATE T1 Set T1.SubsidiaryId = T2.SubsidiaryId, T1.SubsidiaryName = T2.SubsidiaryName

From #Temp T1 Inner Join (

Select Distinct RowLine, Isnull(SubsidiaryId,'') SubsidiaryId, Isnull(SubsidiaryName,'') SubsidiaryName From #Temp Where Isnull(SubsidiaryId,'')='') AS T2

ON T1.RowLine=T2.RowLine Where Isnull(T1.SubsidiaryId,'')=''

Select * Into #UpdatePreQty From #Temp Order BY RowLine, RN

IF Exists (Select 1 From #UpdatePreQty Where (ClosingBalance IS NULL OR ClosingBalance = 0) )

BEGIN

UPDATE T1 SET T1.ClosingBalance = T2.ClosingBalance From #UpdatePreQty T1 JOIN

(Select (SELECT ISNULL(Min(A.ClosingBalance),0) FROM #Temp A

  WHERE A.ClosingBalance IS NOT NULL AND A.ProductKey = C.ProductKey AND A.WarehouseKey = C.WarehouseKey AND ISNULL(A.SubsidiaryId,'')=ISNULL(C.SubsidiaryId,'')

AND A.Rn < C.Rn) AS ClosingBalance, C.ProductKey, C.WarehouseKey, Isnull(C.SubsidiaryId,'') AS SubsidiaryId, C.RN

From #Temp C

WHERE ClosingBalance Is NULL

Group BY C.ProductKey, C.WarehouseKey, Isnull(C.SubsidiaryId,''), C.Rn)

T2 ON T1.ProductKey=T2.ProductKey AND T1.WarehouseKey=T2.WarehouseKey AND Isnull(T1.SubsidiaryId,'') = T2.SubsidiaryId AND T1.Rn=T2.RN

END

IF Exists (Select 1 From #Temp Where (ClosingBalance IS NULL OR ClosingBalance = 0) )

BEGIN

UPDATE T Set T.ClosingBalance = ( Select Max(ClosingBalance) From #UpdatePreQty N

Where N.ProductKey = T.ProductKey AND N.WarehouseKey = T.WarehouseKey AND N.RN = T.RN AND N.RowLine = T.RowLine)

FROM #Temp T

WHERE (ClosingBalance IS NULL OR ClosingBalance = 0)

END

--Select * From #Temp Order BY RowLine, RN

SELECT T2.*, Q.Qty AS DailyStock

FROM #Temp T2

OUTER APPLY dbo.rptOpeningBalance_dailyClosingBal(@FromDate, @ToDate, T2.ProductKey, T2.WarehouseKey) AS Q

WHERE T2.BusinessDate = Q.CDate AND T2.ProductKey = Q.ProductKey AND T2.WarehouseKey = Q.WarehouseKey

ORDER BY RowLine, T2.RN, ProductKey, WarehouseKey

END