iVend Database Database : InvWarehouseInventoryLocation Stored Procedure

Creation Date

8/11/2019 8:38 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@WarehouseKey

In

The new warehouse that has been added in InvWarehouse

VarWChar

50

@LocationKey

In

The new Location that has been added in InvLocation

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

GetCompanyDateTime

User Defined Function

1

InvBatch

Table

Batch details gets affected when doing any transaction with item which has been batch tracked.

1

InvBatchLocation

Table

Describes the batch information with Location, Warehouse, Product and Expiry date

1

InvBatchLocationLog

Table

Stores each record processed for the batch with location deifned.

1

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvLocation

Table

Save the location infomation.

1

InvSerial

Table

Serial details gets affected when doing any transaction with the item which has been serial tracked.

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

1

CREATE PROCEDURE [dbo].[InvWarehouseInventoryLocation]

(

@WarehouseKey nvarchar(50),

@LocationKey   nvarchar(50)

)

AS

BEGIN

DECLARE @SiteID       INT,

  @POSSiteId     INT,

  @IsDefaultLocation   BIT,

  @SiteIdLength     INT

BEGIN TRANSACTION

SELECT @SiteID = SiteId, @POSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

SELECT @IsDefaultLocation = ISNULL(IsDefault, 0) FROM InvLocation WHERE LocationKey = @LocationKey

SELECT @SiteIdLength = LEN(@SiteID)

SELECT DISTINCT ProductKey, @WarehouseKey AS WarehouseKey, @LocationKey AS LocationKey

INTO #temp

FROM InvInventoryLocation

WHERE WarehouseKey = @WarehouseKey

AND LocationKey = @LocationKey

--GET THE

IF (SELECT COUNT(*) FROM #temp) = 0

BEGIN

  INSERT INTO #temp

  SELECT DISTINCT ProductKey, @WarehouseKey As WarehouseKey, @LocationKey AS LocationKey

  From InvProduct

  Where IsDeleted = 0

  And IsNonStock = 0

END

IF (SELECT COUNT(1) FROM #temp) = 0

BEGIN

    commit

  RETURN

END

IF(@IsDefaultLocation = 0)

BEGIN

  INSERT INTO InvInventoryLocation

  (InventoryLocationKey, ProductKey, WarehouseKey, LocationKey , InStockQuantity, InReturnQuantity, OnLayawayQuantity, OnOrderQuantity,

  OnFulFillmentQuantity, AvailableQuantity,AllocatedQuantity, Created, Modified, CreatedBy, ModifiedBy)

  SELECT NEWID(), ProductKey, @WarehouseKey , @LocationKey , 0, 0, 0,0, 0, 0,0, dbo.GetCompanyDateTime(),

  dbo.GetCompanyDateTime(), '-1', '-1'

  FROM #temp

  INSERT INTO RepReplicationTransaction(

    ReplicationTransactionKey, SourceType, SourceKey

    , BatchKey, OperationType, Flag

    , StoreId, FromSBO, Sender, Receiver)

  SELECT NEWID(), 393, A.InventoryLocationKey,

    0 AS BatchKey, 0, 0

    , NULL, NULL, NULL, NULL

  FROM InvInventoryLocation A, #temp B

  Where A.ProductKey = B.ProductKey

  And A.WarehouseKey = @WarehouseKey

  And A.LocationKey = @LocationKey

END

ELSE

BEGIN

  INSERT INTO InvInventoryLocation

   (

    InventoryLocationKey, ProductKey, WarehouseKey

    ,LocationKey , InStockQuantity, InReturnQuantity

    , OnLayawayQuantity, OnOrderQuantity, OnFulFillmentQuantity

    , AvailableQuantity,AllocatedQuantity, Created

    , Modified, CreatedBy, ModifiedBy

   )

  SELECT NEWID(), A.ProductKey, @WarehouseKey

    ,@LocationKey, B.InStockQuantity, B.InReturnQuantity

    , B.OnLayawayQuantity, B.OnOrderQuantity, B.OnFulFillmentQuantity

    , B.AvailableQuantity, B.AllocatedQuantity, dbo.GetCompanyDateTime()

    , dbo.GetCompanyDateTime(), '-1', '-1'

  FROM #temp A

  INNER JOIN InvInventoryItem B

  ON A.WarehouseKey = B.WarehouseKey

  AND A.ProductKey = B.ProductKey

  INSERT INTO RepReplicationTransaction

   (

    ReplicationTransactionKey, SourceType, SourceKey

    , BatchKey, OperationType, Flag

    , StoreId, FromSBO, Sender

    , Receiver

   )

  SELECT NEWID(), 393, A.InventoryLocationKey,

  0 AS BatchKey,

  0, 0, NULL, NULL, NULL, NULL

  FROM InvInventoryLocation A, #temp B

  Where A.ProductKey = b.ProductKey

  And A.WarehouseKey = @WarehouseKey

  And A.LocationKey = @LocationKey

END

/*For Default Location Entery for Batch and Serial*/

IF(@IsDefaultLocation = 1)

BEGIN

  --Delta for Inventory Location

  INSERT INTO InvInventoryLocationLog(InventoryLocationLogKey,WarehouseKey,ProductKey,LocationKey,InQty,OutQty,SourceType,SourceKey,SourceDetailKey,

  UpdateType,Created,OnFulfillmentQuanity,AllocatedQuantity)

  SELECT NEWID(),A.WarehouseKey,A.ProductKey,A.LocationKey,

  C.AvailableQuantity,0,'-1',0,0,0,GETDATE(),0,0

  FROM InvInventoryLocation A

  INNER JOIN #temp B ON B.LocationKey = A.LocationKey AND B.WarehouseKey = A.WarehouseKey AND A.ProductKey = B.ProductKey

  INNER JOIN InvInventoryItem C ON C.ProductKey = A.ProductKey AND C.WarehouseKey = A.WarehouseKey

  WHERE A.WarehouseKey = @WarehouseKey AND A.LocationKey = @LocationKey

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType,

  Flag, StoreId, FromSBO, Sender, Receiver)

  SELECT NEWID(), 394, A.InventoryLocationLogKey,

  0 AS BatchKey, 1, 0

   , NULL, NULL, NULL, NULL

  FROM InvInventoryLocationLog A

  INNER JOIN #temp B ON B.LocationKey = A.LocationKey AND B.WarehouseKey = A.WarehouseKey AND A.ProductKey = B.ProductKey

  WHERE A.WarehouseKey = @WarehouseKey AND A.LocationKey = @LocationKey

  IF EXISTS(SELECT 1 FROM InvBatch Where WarehouseKey = @WarehouseKey)

  BEGIN

  INSERT INTO InvBatchLocation(BatchLocationKey,BatchKey,BatchNumber,WarehouseKey,ProductKey,LocationKey,CommentKey,

   AdmissionDate,ExpirationDate,ManufacturingDate,InStockQuantity,CommittedQuantity,OnOrderQuantity,

   OnRentQuantity,LostQuantity,InReturnQuantity,AvailableQuantity,OnLayawayQuantity,ReleasedQuantity,

   Status,Price,Details,AllocatedQuantity,Created,Modified,CreatedBy,ModifiedBy,IsDeleted)

  SELECT NEWID(),

   B.BatchKey, B.BatchNumber, B.WarehouseKey, B.ProductKey, @LocationKey,B.CommentKey,

   B.AdmissionDate,B.ExpirationDate,B.ManufacturingDate,B.InStockQuantity,B.CommittedQuantity,B.OnOrderQuantity,

   B.OnRentQuantity,B.LostQuantity,B.InReturnQuantity,B.AvailableQuantity,B.OnLayawayQuantity,B.ReleasedQuantity,

   B.Status,B.Price,B.Details,B.AllocatedQuantity, dbo.GetCompanyDateTime(), dbo.GetCompanyDateTime(), '-1', '-1',0

  FROM InvBatch B

  Where B.WarehouseKey = @WarehouseKey

  AND  B.IsDeleted = 0

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType,

   Flag, StoreId, FromSBO, Sender, Receiver)

  SELECT NEWID(), 395,

   InvBatchLocation.BatchLocationKey,

  0, 0, 0, NULL, NULL, NULL, NULL

  FROM InvBatchLocation

  Where WarehouseKey = @WarehouseKey

  AND  IsDeleted = 0

  INSERT INTO InvBatchLocationLog(BatchLocationLogKey,BatchKey,WarehouseKey,ProductKey,LocationKey, InQty, OutQty, SourceType, SourceKey, SourceDetailKey, AllocatedQuantity)

  SELECT NEWID(),B.BatchKey,B.WarehouseKey,B.ProductKey,@LocationKey,

   C.AvailableQuantity,0,'-1',0,0,0

  FROM InvBatchLocation B

  --INNER JOIN #tempBatch A ON B.WarehouseKey = A.WarehouseKey  AND A.ProductKey = B.ProductKey AND A.BatchKey = B.BatchKey

  --INNER JOIN #tempBatch A ON B.WarehouseKey = A.WarehouseKey AND B.LocationKey = A.LocationKey AND A.ProductKey = B.ProductKey AND A.BatchKey = B.BatchKey

  INNER JOIN InvBatch C ON B.WarehouseKey = C.WarehouseKey AND B.ProductKey = C.ProductKey AND B.BatchKey = C.BatchKey

  WHERE B.WarehouseKey = @WarehouseKey AND B.LocationKey = @LocationKey

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType,

   Flag, StoreId, FromSBO, Sender, Receiver)

  SELECT NEWID(), 396, b.BatchLocationLogKey,

  0 AS BatchKey, 1, 0, NULL, NULL, NULL, NULL

  FROM InvBatch A

  INNER JOIN InvBatchLocationLog B ON B.WarehouseKey = A.WarehouseKey AND B.ProductKey = A.ProductKey AND B.BatchKey = A.BatchKey

  WHERE A.WarehouseKey = @WarehouseKey AND B.LocationKey = @LocationKey

  END ---end for batch update

  /*For Serial*/

  IF EXISTS(SELECT 1 FROM InvSerial Where WarehouseKey = @WarehouseKey)

  BEGIN

  UPDATE InvSerial

  SET LocationKey = @LocationKey

  Where IsDeleted = 0 And WarehouseKey = @WarehouseKey

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType,

   Flag, StoreId, FromSBO, Sender, Receiver)

  SELECT NEWID(), 52, SerialKey AS SourceKey,

  0 AS BatchKey,

  1, 0, NULL, NULL, NULL, NULL

  FROM InvSerial

  Where IsDeleted = 0

  And WarehouseKey = @WarehouseKey

  END

END

COMMIT

End