iVend Database Database : MRP_CalculateInOut Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@TfrLevel

In

SmallInt

2

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

MRP

Stored Procedure

1

CREATE PROCEDURE [dbo].[MRP_CalculateInOut] @TfrLevel smallint = 0

As

Begin

Set NoCount On

Truncate Table #StockInOut

--If @TfrLevel = 0

-- Return

Insert Into #StockInOut(ProductKey, WarehouseKey, DueDate)

Select Distinct ProductKey, WarehouseKey, DueDate

From #ReceiptsDemands

Where TfrLevel = @TfrLevel

Order By ProductKey, WarehouseKey, DueDate

Update StockInOut

Set  StockInOut.QtyOnHand = ItemWarehouse.InStockQuantity

From (Select RID=Row_Number()Over(Partition By ProductKey,WarehouseKey Order By DueDate) ,* From #StockInOut) As StockInOut Inner Join #ItemWarehouse As ItemWarehouse On StockInOut.ProductKey = ItemWarehouse.ProductKey

                  And StockInOut.WarehouseKey = ItemWarehouse.WarehouseKey

                  And RID=1

Update StockInOut

Set --QtyOnHand  = ISNULL(temp.OnHandQuantity,0),

  QtyIn = ISNULL(temp.InQuantity,0),

  QtyOut =ISNULL(temp.OutQuantity,0)

From #StockInOut As StockInOut Inner Join

           (

            Select DueDate, ProductKey, WarehouseKey,

              Sum(Case When StockType = 'R' And ObjectId = 38 Then Quantity Else 0 End) As OnHandQuantity,

              Sum(Case When StockType = 'R' And ObjectId <> 38 Then Quantity Else 0 End) As InQuantity,

              --Sum(Case When StockType = 'R' And ObjectId <> 38 Then Case When OrderQty > Quantity Then OrderQty Else Quantity End Else 0 End) As InQuantity,

              Sum(Case When StockType = 'I' Then Quantity Else 0 End) As OutQuantity

            From #ReceiptsDemands

            Group By ProductKey, WarehouseKey, DueDate

           ) As temp On StockInOut.DueDate = temp.DueDate And StockInOut.ProductKey = temp.ProductKey And StockInOut.WarehouseKey = temp.WarehouseKey

Declare @OB      numeric(20, 5),

  @CB      numeric(20, 5),

  @LastItemWarehouseCode nvarchar(100)

  Set @OB = 0

  Set @CB = 0

  Set @LastItemWarehouseCode =''

UPDATE #StockInOut

SET @OB   = ISNULL((Case When @LastItemWarehouseCode <> Cast(ProductKey as varchar(20)) + ':' + Cast(WarehouseKey as varchar(20)) Then ISNULL(QtyOB,0)

          ELSE (CASE WHEN @CB < 0 THEN 0 ELSE @CB END) END),0),

  @CB     = ISNULL(@OB,0) + QtyOnHand + QtyIn - QtyOut,

  @LastItemWarehouseCode = Cast(ProductKey as varchar(20)) + ':' + Cast(WarehouseKey as varchar(20)),

   QtyOB     = @OB,

   QtyCB     = @CB

Set NoCount Off

End