iVend Database Database : Integration_GoodsReceiptSerialBatch Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReceiptKey

In

The Goods Receipt Key (Primary Key of InvGoodReceipt) 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

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptBatch

Table

This is affected in case the goods receipt is done for a batch product.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvGoodReceiptSerial

Table

This is affected in case the goods receipt is done for a serial tracked product.

1

InvSerial

Table

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

1

-- Goods Receipt transactions in iVend

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsReceiptSerialBatch]

    @GoodsReceiptKey NVARCHAR(50)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT            InvGoodReceipt.GoodsReceiptKey,

                       GoodsReceiptDetailKey AS CXS_TDKY,

                      'Serial' AS BSType,

                      1 AS Quantity,

                       InvSerial.SerialNumber AS InternalSerialBatchNumber,

                       InvGoodReceiptDetail.LineNumber,

    isNull(InvSerial.SystemNumber, 0) As SystemNumber,

    ISNULL(InvSerial.Price, 0) As Price,

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

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

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

     InvGoodReceiptSerial.SourceType

FROM  InvGoodReceipt INNER JOIN InvGoodReceiptDetail

  ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey

  INNER JOIN InvGoodReceiptSerial

  ON InvGoodReceiptDetail.GoodsReceiptDetailKey = InvGoodReceiptSerial.GoodReceiptDetailKey

  INNER JOIN InvSerial

  ON InvSerial.SerialKey = InvGoodReceiptSerial.SerialKey

    Where InvGoodReceipt.GoodsReceiptKey = @GoodsReceiptKey

  UNION

    SELECT     InvGoodReceipt.GoodsReceiptKey, InvGoodReceiptDetail.GoodsReceiptDetailKey AS CXS_TDKY, 'Batch' AS BSType,

              SUM(InvGoodReceiptBatch.QuantityReceived) AS Quantity, InvBatch.BatchNumber AS InternalSerialBatchNumber,

               InvGoodReceiptDetail.LineNumber,0 as SystemNumber,

  ISNULL(InvBatch.Price, 0) As Price,

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

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

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

   InvGoodReceiptBatch.SourceType

FROM  InvGoodReceipt INNER JOIN  InvGoodReceiptDetail

  ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey

  INNER JOIN InvGoodReceiptBatch

  ON InvGoodReceiptDetail.GoodsReceiptDetailKey = InvGoodReceiptBatch.GoodReceiptDetailKey

  INNER JOIN InvBatch

  ON InvGoodReceiptBatch.BatchKey = InvBatch.BatchKey

WHERE   InvGoodReceipt.GoodsReceiptKey = @GoodsReceiptKey

GROUP BY InvGoodReceipt.GoodsReceiptKey, InvGoodReceiptDetail.GoodsReceiptDetailKey,

               InvBatch.BatchNumber ,

               InvGoodReceiptDetail.LineNumber, InvBatch.Price,

   InvBatch.AdmissionDate, InvBatch.ExpirationDate, InvBatch.ManufacturingDate,

   InvGoodReceiptBatch.SourceType

Order By CXS_TDKY, BSType, InternalSerialBatchNumber

          --SELECT GoodsReceiptKey

END