iVend Database Database : GoodsIssueReport Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsIssueKey

In

Reference key of the Goods Issue document thats need to be integrated with the ERP

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgReasonCode

Table

Stores the various reason codes created in the system.

1

InvBatch

Table

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

1

InvInventoryTransaction

Table

This gets affected when the inventory of a particular item is consumed at the store itself for any reason.

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

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

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 GoodsIssue Report is generated in iVend.

--##SUMMARY_END

--Goods Issue

CREATE PROCEDURE [dbo].[GoodsIssueReport](

@GoodsIssueKey nvarchar(50)

)

As

Begin

--Exec GoodsIssueReport 10000000000001

Set NoCount On

Select --Header Info

  'GoodsIssueNo' = GoodsIssueHed.ReferenceNumber

  ,'GoodsIssueDate' = GoodsIssueHed.Created

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

  ,'Remarks' = Reason.Description

  --Line Info

  ,'ItemCode' = Product.Id

  ,'Description' = GoodsIssueDet.Description

  ,'Quantity' = GoodsIssueDet.Quantity

  --Serail No. Information

  ,'SerialNo' = InvSerial.SerialNumber

  ,'SerialAdmissionDate' = InvSerial.AdmissionDate

  ,'SerialManufacturingDate' = InvSerial.ManufacturingDate

  ,'SerialExpirationDate' = InvSerial.ExpirationDate

  --Batch No. Information

  ,'BatchNo' = InvBatch.BatchNumber

  ,'BatchQuantity' = GoodsIssueBatch.Quantity

  ,'BatchStore' = Store.Id

  ,'BatchAdmissionDate' = InvBatch.AdmissionDate

  ,'BatchManufacturingDate' = InvBatch.ManufacturingDate

  ,'BatchExpirationDate' = InvBatch.ExpirationDate

Into #temp

From InvInventoryTransaction As GoodsIssueHed

  Inner Join InvInventoryTransactionDetail As GoodsIssueDet On GoodsIssueHed.InventoryTransactionKey = GoodsIssueDet.InventoryTransactionKey

  And GoodsIssueHed.SourceType = 0

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

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

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

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

  --Serial No.

  Left Outer Join InvInventoryTransactionSerial As GoodsIssueSerail On GoodsIssueDet.InvInventoryTransactionDetailKey = GoodsIssueSerail.InventoryTransactionDetailKey

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

  --Batch No.

  Left Outer Join InvInventoryTransactionBatch As GoodsIssueBatch On GoodsIssueDet.InvInventoryTransactionDetailKey = GoodsIssueBatch.InventoryTransactionDetailKey

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

  Left Outer Join CfgReasonCode As Reason On GoodsIssueHed.ReasonKey = Reason.ReasonCodeKey

Where GoodsIssueHed.InventoryTransactionKey = @GoodsIssueKey

Select * From #temp

Set NoCount Off

End