iVend Database Database : Integration_GoodsReceiptSerialBatchShortReceived Stored Procedure

Creation Date

9/18/2015 10:23 AM

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

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvSerial

Table

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

1

InvStockTransferBatch

Table

This gets affected in case the stock transfer is done for a batch product

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvStockTransferSerial

Table

This gets affected in case the stock transfer is done for a serially tracked product

1

CREATE PROCEDURE [dbo].[Integration_GoodsReceiptSerialBatchShortReceived]

    @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

FROM InvGoodReceipt

INNER JOIN InvGoodReceiptDetail ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey AND InvGoodReceipt.SourceType = 2 /*1 -> PO and 2 -> StockTransfer*/

INNER JOIN InvStockTransferDetail ON InvGoodReceiptDetail.SourceDetailKey = InvStockTransferDetail.StockTransferDetailKey

INNER JOIN InvStockTransferSerial ON InvStockTransferDetail.StockTransferDetailKey = InvStockTransferSerial.StockTransferDetailKey AND InvStockTransferSerial.IsShortReceived = 1

INNER JOIN InvSerial ON InvStockTransferSerial.SerialKey = InvSerial.SerialKey

WHERE InvGoodReceipt.GoodsReceiptKey = @GoodsReceiptKey

UNION

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

               InvStockTransferBatch.ShortQuantityReceived 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

FROM InvGoodReceipt

  INNER JOIN InvGoodReceiptDetail ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey AND InvGoodReceipt.SourceType = 2 /*1 -> PO and 2 -> StockTransfer*/

  INNER JOIN InvStockTransferDetail ON InvGoodReceiptDetail.SourceDetailKey = InvStockTransferDetail.StockTransferDetailKey

  INNER JOIN InvStockTransferBatch ON InvStockTransferDetail.StockTransferDetailKey = InvStockTransferBatch.StockTransferDetailKey AND InvStockTransferBatch.ShortQuantityReceived > 0

  INNER JOIN InvBatch ON InvStockTransferBatch.BatchKey = InvBatch.BatchKey

WHERE   InvGoodReceipt.GoodsReceiptKey = @GoodsReceiptKey

END