iVend Database Database : StockTransferReport 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 that needs to be pulled from iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgComment

Table

Defines the details of all the comments that have been stored for various objects like Customer, Vendor etc.

1

InvBatch

Table

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

1

InvProduct

Table

Stores the product related details.

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

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

LbrUser

Table

Defines a list of all employees/ labor users created in the system

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

SecSecurityUser

Table

We can make any employee of the system as a security user and specify details like login id and password and electronic id etc in this table , who may be doing POS transactions. It is mandatory to make an employee a security user before he/she can do POS transactions.

1

--Based on the output of this procedure the Stock Transfer Report is generated in iVend.

--##SUMMARY_END

--Stock Transfer

CREATE PROCEDURE [dbo].[StockTransferReport](@StockTransferKey nvarchar(50))

As

Begin

--Exec StockTransferReport 10000000000001

Set NoCount On

Select --Header Info

  'StockTransferNo' = StockTransferHed.RequestNumber

  ,'StockTransferDate' = StockTransferHed.Created

  ,'CreatedBy' = isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '')

  ,'Remarks' = Comment.Value

  ,'FromWarehouse' = FromWH.Id

  ,'ToWarehouse' = ToWh.Id

  --Line Info

  ,'ItemCode' = Product.Id

  ,'Description' = Product.Description

  ,'Quantity' = StockTransferDet.Quantity

  --Serail No. Information

  ,'SerialNo' = InvSerial.SerialNumber

  ,'SerialAdmissionDate' = InvSerial.AdmissionDate

  ,'SerialManufacturingDate' = InvSerial.ManufacturingDate

  ,'SerialExpirationDate' = InvSerial.ExpirationDate

  --Batch No. Information

  ,'BatchNo' = InvBatch.BatchNumber

  ,'BatchQuantity' = StockTransferBatch.Quantity

  ,'BatchStore' = Store.Id

  ,'BatchAdmissionDate' = InvBatch.AdmissionDate

  ,'BatchManufacturingDate' = InvBatch.ManufacturingDate

  ,'BatchExpirationDate' = InvBatch.ExpirationDate

  ,'StockTransferDetailKey' = StockTransferDet.StockTransferDetailKey

Into #temp

From InvStockTransfer As StockTransferHed

  Inner Join InvStockTransferDetail As StockTransferDet On StockTransferHed.StockTransferKey = StockTransferDet.StockTransferKey

  Inner Join InvProduct As Product On StockTransferDet.ProductKey = Product.ProductKey

  Inner Join InvWarehouse As FromWH On StockTransferHed.FromWarehouseKey = FromWH.WarehouseKey

  Inner Join InvWarehouse As ToWH On StockTransferHed.ToWarehouseKey = ToWH.WarehouseKey

  Left Outer Join SecSecurityUser On StockTransferHed.CreatedBy = SecSecurityUser.SecurityUserKey

  Left Outer Join LbrUser ON SecSecurityUser.UserKey = LbrUser.UserKey

  Inner Join RtlStore As Store On StockTransferHed.SiteId = Store.SiteId

  --Serial No.

  Left Outer Join InvStockTransferSerial As StockTransferSerial On StockTransferDet.StockTransferDetailKey = StockTransferSerial.StockTransferDetailKey

  Left Outer Join InvSerial On StockTransferSerial.SerialKey = InvSerial.SerialKey

  --Batch No.

  Left Outer Join InvStockTransferBatch As StockTransferBatch On StockTransferDet.StockTransferDetailKey = StockTransferBatch.StockTransferDetailKey

  Left Outer Join InvBatch On StockTransferBatch.BatchKey = InvBatch.BatchKey

  Left Outer Join CfgComment As Comment On StockTransferHed.CommentKey = Comment.CommentKey

Where StockTransferHed.StockTransferKey = @StockTransferKey

Select * From #temp

Set NoCount Off

End