iVend Database Database : Integration_StockTransferSerialBatch Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@StockTransferKey

In

(Primary Key of InvStockTransfer) for which the Stock Transfer 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

InvSerial

Table

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

1

InvStockTransfer

Table

Stores stock transfer details

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

-- Stock Transfer transactions in iVend

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_StockTransferSerialBatch]

-- Add the parameters for the stored procedure here

@StockTransferKey NVARCHAR(50)

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT C.StockTransferKey,

 B.StockTransferDetailKey ,

'Serial' AS BSType,

 A.SerialNumber AS InternalSerialBatchNumber,0 as Quantity,0 as QuantityOpened,

0 as QuantityShipped,0 as QuantityReceived, isNull(A.SystemNumber, 0) As SystemNumber,

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

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

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

FROM  InvSerial A INNER JOIN

                     InvStockTransferSerial B ON A.SerialKey = B.SerialKey INNER JOIN

                     InvStockTransfer C INNER JOIN

                     InvStockTransferDetail D ON C.StockTransferKey = D.StockTransferKey ON

                     B.StockTransferDetailKey = D.StockTransferDetailKey

Where (C.StockTransferKey =@StockTransferKey)

union

SELECT C.StockTransferKey,

 B.StockTransferDetailKey ,

'Batch' AS BSType,

 A.BatchNumber AS InternalSerialBatchNumber,B.Quantity,B.QuantityOpen,

 B.QuantityShipped,B.QuantityReceived,0 as SystemNumber,

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

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

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

FROM  InvBatch A INNER JOIN

                     InvStockTransferBatch B ON A.BatchKey = B.BatchKey INNER JOIN

                     InvStockTransfer C INNER JOIN

                     InvStockTransferDetail D ON C.StockTransferKey = D.StockTransferKey ON

                     B.StockTransferDetailKey = D.StockTransferDetailKey

Where (C.StockTransferKey =@StockTransferKey)

Order By B.StockTransferDetailKey, BSType, InternalSerialBatchNumber

--SELECT StockTransferKey

END

----------------------------------------------------------------------------------------------------------------------------------------------------------