iVend Database Database : Integration_GoodsReturnSerialBatch Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReturnKey

In

The Goods Return Key (Primary Key of InvGoodsReturn) for which the serial and batch information needs to be pulled from iVend.

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvBatch

Table

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

1

InvGoodsReturn

Table

Stores the data related to Inventory Goods Return

1

InvGoodsReturnBatch

Table

Stores the data related to inventory goods return for batch

1

InvGoodsReturnDetail

Table

Stores the details of inventory goods return

1

InvGoodsReturnSerial

Table

Stores the data related to serial inventory goods return

1

InvSerial

Table

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

1

-- Goods Return transactions in iVend

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsReturnSerialBatch]

    @GoodsReturnKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON

SELECT InvGoodsReturn.GoodsReturnKey,

  InvGoodsReturnDetail.GoodsReturnDetailKey AS CXS_TDKY,

          'Serial' AS BSType,

          1 AS Quantity,

           InvSerial.SerialNumber AS InternalSerialBatchNumber,

           InvGoodsReturnDetail.LineNumber, isNull(InvSerial.SystemNumber, 0) AS SystemNumber,

  CONVERT(NVARCHAR, InvSerial.AdmissionDate, 112) As InDate,

  CONVERT(NVARCHAR, InvSerial.ExpirationDate, 112) As ExpDate,

  CONVERT(NVARCHAR, InvSerial.ManufacturingDate, 112) As PrdDate

FROM InvGoodsReturn INNER JOIN InvGoodsReturnDetail ON InvGoodsReturn.GoodsReturnKey = InvGoodsReturnDetail.GoodsReturnKey

      INNER JOIN InvGoodsReturnSerial ON InvGoodsReturnDetail.GoodsReturnDetailKey = InvGoodsReturnSerial.GoodsReturnDetailKey

      INNER JOIN InvSerial ON InvGoodsReturnSerial.SerialKey = InvSerial.SerialKey

WHERE InvGoodsReturn.GoodsReturnKey = @GoodsReturnKey

  UNION

SELECT InvGoodsReturn.GoodsReturnKey,

  InvGoodsReturnDetail.GoodsReturnDetailKey AS CXS_TDKY,

  'Batch' AS BSType,

  InvGoodsReturnBatch.Quantity AS Quantity,

  InvBatch.BatchNumber AS InternalSerialBatchNumber,

  InvGoodsReturnDetail.LineNumber,0 as SystemNumber,

  CONVERT(NVARCHAR, InvBatch.AdmissionDate, 112) As InDate,

  CONVERT(NVARCHAR, InvBatch.ExpirationDate, 112) As ExpDate,

  CONVERT(NVARCHAR, InvBatch.ManufacturingDate, 112) As PrdDate

FROM InvGoodsReturn INNER JOIN InvGoodsReturnDetail ON InvGoodsReturn.GoodsReturnKey = InvGoodsReturnDetail.GoodsReturnKey

      INNER JOIN InvGoodsReturnBatch ON InvGoodsReturnDetail.GoodsReturnDetailKey = InvGoodsReturnBatch.GoodsReturnDetailKey

      INNER JOIN InvBatch ON InvGoodsReturnBatch.BatchKey = InvBatch.BatchKey

WHERE   InvGoodsReturn.GoodsReturnKey = @GoodsReturnKey

ORDER BY CXS_TDKY, BSType, InternalSerialBatchNumber

END