iVend Database Database : Integration_GoodsIssue_SerialBatch Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@InventoryTransactionKey

In

The transaction key 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

InvInventoryTransactionBatch

Table

This gets affected in case the product on the goods issue note is batch tracked.

1

InvInventoryTransactionDetail

Table

Defines the details of the product on the goods issue note

1

InvInventoryTransactionSerial

Table

This gets affected in case the product on the goods issue note is serial tracked.

1

InvSerial

Table

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

1

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsIssue_SerialBatch]

@InventoryTransactionKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON;

SELECT   InvInventoryTransactionSerial.InventoryTransactionDetailKey AS CXS_TDKY,

    'Serial' AS BSType,

    1 AS Quantity,

    InvSerial.SerialNumber AS SerialBatchNumber,

    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   InvInventoryTransactionDetail

    INNER JOIN InvInventoryTransactionSerial ON

    InvInventoryTransactionDetail.InvInventoryTransactionDetailKey = InvInventoryTransactionSerial.InventoryTransactionDetailKey

    INNER JOIN InvSerial ON

    InvInventoryTransactionSerial.SerialKey = InvSerial.SerialKey

WHERE   (InvInventoryTransactionDetail.Type = 0)

AND    (InvInventoryTransactionDetail.InventoryTransactionKey = @InventoryTransactionKey)

UNION

SELECT   InvInventoryTransactionDetail.InvInventoryTransactionDetailKey AS CXS_TDKY,

    'Batch' AS BSType,

    SUM(InvInventoryTransactionBatch.Quantity) AS Quantity,

    InvBatch.BatchNumber AS SerialBatchNumber, 0,

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

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

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

FROM   InvInventoryTransactionDetail

    INNER JOIN InvInventoryTransactionBatch ON

    InvInventoryTransactionDetail.InvInventoryTransactionDetailKey = InvInventoryTransactionBatch.InventoryTransactionDetailKey

    INNER JOIN InvBatch ON

    InvInventoryTransactionBatch.BatchKey = InvBatch.BatchKey

WHERE   (InvInventoryTransactionDetail.Type = 0)

AND    (InvInventoryTransactionDetail.InventoryTransactionKey = @InventoryTransactionKey)

group by  InvInventoryTransactionDetail.InvInventoryTransactionDetailKey, InvBatch.BatchNumber, InvBatch.AdmissionDate, InvBatch.ExpirationDate, InvBatch.ManufacturingDate

END