iVend Database Database : GoodsReceiptPOReport 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 thats need to be integrated with the ERP

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgAddress

Table

Defines the address details for each Address.

1

CfgCountry

Table

The country details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

1

CfgState

Table

The state details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

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

InvUOM

Table

Master table for the Inv UOM

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

PurPurchaseOrder

Table

Stores information related to Purchase order transactions done from Console or done from ERP on behalf of this store.

1

PurPurchaseOrderDetail

Table

Stores the line details of the purchase order.

1

PurVendor

Table

Defines the vendor details of an enterprise from which the products are purchased.

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

--GRPO

CREATE PROCEDURE [dbo].[GoodsReceiptPOReport](

@GoodsReceiptKey nvarchar(50)

)

As

Begin

--Exec GoodsReceiptPOReport 10000000000001

Set NoCount On

Select --Header Info

  'GRPONo' = GRPOHed.GoodsReceiptNumber

  ,'GoodsReceiptDetailKey' =  GRPODet.GoodsReceiptDetailKey

  ,'GRPODate' = GRPOHed.Created

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

  ,'VendorCode' = Vendor.Id

  ,'VendorName' = PurHed.VendorName

  ,'VendorBillingAddress' = isNull(BAdd.Address1, '') + ' ' + isNull(BAdd.Address2, '') + ' ' + isNull(BAdd.Address3, '') + ' ' + isNull(BAdd.City, '') + ' ' + isNull(BAdd.ZipCode, '') + ' ' + isNull(BState.Description, '') + ' ' + isNull(BCountry.Description, '')

  ,'StoreAddress'   = isNull(StoreAdd.Address1, '') + ' ' + isNull(StoreAdd.Address2, '') + ' ' + isNull(StoreAdd.Address3, '') + ' ' + isNull(StoreAdd.City, '') + ' ' + isNull(StoreAdd.ZipCode, '') + ' ' + isNull(StoreState.Description, '') + ' ' + isNull(StoreCountry.Description, '')

  ,'BasedOn' = 'Based On PO #' + PurHed.PurchaseOrderId

  ,'UOM'=InvUOM.Description

  --Line Info

  ,'ItemCode' = Product.Id

  ,'Description' = Product.Description

  ,'Quantity' = GRPODet.UOMQuantityReceived--GRPODet.QuantityReceived

  --Serail No. Information

  ,'SerialNo' = InvSerial.SerialNumber

  ,'SerialAdmissionDate' = InvSerial.AdmissionDate

  ,'SerialManufacturingDate' = InvSerial.ManufacturingDate

  ,'SerialExpirationDate' = InvSerial.ExpirationDate

  --Batch No. Information

  ,'BatchNo' = InvBatch.BatchNumber

  ,'BatchQuantity'= GRPOBatch.QuantityReceived

  ,'BatchStore' = Store.Id

  ,'BatchAdmissionDate' = InvBatch.AdmissionDate

  ,'BatchManufacturingDate' = InvBatch.ManufacturingDate

  ,'BatchExpirationDate' = InvBatch.ExpirationDate

Into #temp

From InvGoodReceipt As GRPOHed

  INNER JOIN InvGoodReceiptDetail As GRPODet On GRPOHed.GoodsReceiptKey = GRPODet.GoodsReceiptKey

  INNER JOIN InvProduct As Product On GRPODet.ProductKey = Product.ProductKey

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

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

  LEFT Outer Join InvUOM on GRPODet.UOMKey=InvUOM.UOMKey

  LEFT OUTER JOIN PurPurchaseOrderDetail As PurDet On GRPODet.SourceDetailKey = PurDet.PurchaseOrderDetailKey

  LEFT OUTER JOIN PurPurchaseOrder As PurHed On GRPODet.SourceKey = PurHed.PurchaseOrderKey

  LEFT OUTER JOIN PurVendor As Vendor On PurHed.VendorKey = Vendor.VendorKey

  --Billing Address

  Left Outer Join CfgAddress As BAdd On Vendor.BillingAddressKey = BAdd.AddressKey

  Left Outer Join CfgState As BState On BAdd.StateKey = BState.StateKey

  Left Outer Join CfgCountry As BCountry On BState.CountryKey = BCountry.CountryKey

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

  Inner Join InvWarehouse As WH On Store.WarehouseKey = WH.WarehouseKey

  --Store Address

  Left Outer Join CfgAddress As StoreAdd On WH.AddressKey = StoreAdd.AddressKey

  Left Outer Join CfgState As StoreState On StoreAdd.StateKey = StoreState.StateKey

  Left Outer Join CfgCountry As StoreCountry On StoreAdd.CountryKey = StoreCountry.CountryKey

  --Serial No.

  Left Outer Join InvGoodReceiptSerial As GRPOSerail On GRPODet.GoodsReceiptDetailKey = GRPOSerail.GoodReceiptDetailKey

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

  --Batch No.

  Left Outer Join InvGoodReceiptBatch As GRPOBatch On GRPODet.GoodsReceiptDetailKey = GRPOBatch.GoodReceiptDetailKey

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

Where GRPOHed.GoodsReceiptKey = @GoodsReceiptKey

Select * From #temp

Set NoCount Off

End