iVend Database Database : GoodsReceiptReport Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReceiptKey

In

Reference key of the Goods Receipt document

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

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptBatch

Table

This is affected in case the goods receipt is done for a batch product.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvGoodReceiptSerial

Table

This is affected in case the goods receipt is done for a serial tracked product.

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

--##SUMMARY_END

--Goods Receipt

CREATE PROCEDURE [dbo].[GoodsReceiptReport](

@GoodsReceiptKey nvarchar(50)

)

As

Begin

--Exec GoodsReceiptReport 10000000000001

Set NoCount On

Select --Header Info

  'GoodsReceiptNo' = GoodsReceiptHed.GoodsReceiptNumber

  ,'GoodReceiptDetail' = GoodsReceiptDet.GoodsReceiptDetailKey

  ,'GoodsReceiptDate' = GoodsReceiptHed.Created

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

  ,'Remarks' = Reason.Description

  --Line Info

  ,'ItemCode' = Product.Id

  ,'Description' = Product.Description

  ,'Quantity' = GoodsReceiptDet.QuantityReceived

  --Serail No. Information

  ,'SerialNo' = InvSerial.SerialNumber

  ,'SerialAdmissionDate' = InvSerial.AdmissionDate

  ,'SerialManufacturingDate' = InvSerial.ManufacturingDate

  ,'SerialExpirationDate' = InvSerial.ExpirationDate

  --Batch No. Information

  ,'BatchNo' = InvBatch.BatchNumber

  ,'BatchQuantity' = GoodReceiptBatch.QuantityReceived

  ,'BatchStore' = Store.Id

  ,'BatchAdmissionDate' = InvBatch.AdmissionDate

  ,'BatchManufacturingDate' = InvBatch.ManufacturingDate

  ,'BatchExpirationDate' = InvBatch.ExpirationDate

Into #temp

From InvGoodReceipt As GoodsReceiptHed

  Inner Join InvGoodReceiptDetail As GoodsReceiptDet On GoodsReceiptHed.GoodsReceiptKey = GoodsReceiptDet.GoodsReceiptKey

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

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

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

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

  --Serial No.

  Left Outer Join InvGoodReceiptSerial As GoodReceiptSerial On GoodsReceiptDet.GoodsReceiptDetailKey = GoodReceiptSerial.GoodReceiptDetailKey

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

  --Batch No.

  Left Outer Join InvGoodReceiptBatch As GoodReceiptBatch On GoodsReceiptDet.GoodsReceiptDetailKey = GoodReceiptBatch.GoodReceiptDetailKey

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

  Left Outer Join CfgReasonCode As Reason On GoodsReceiptHed.ReasonCodeKey = Reason.ReasonCodeKey

Where GoodsReceiptHed.GoodsReceiptKey = @GoodsReceiptKey

Select * From #temp

Set NoCount Off

End