iVend Database Database : PurchaseOrderReport Stored Procedure

Creation Date

8/12/2019 6:09 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@PurchaseOrderKey

In

(Primary Key of PurPurchaseOrder)for which the Purchase Order Information that needs to be pulled from iVend

VarWChar

200

@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

InvProduct

Table

Stores the product related details.

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

--Exec PurchaseOrderReport 20000000000007

--PO

CREATE PROCEDURE [dbo].[PurchaseOrderReport](@PurchaseOrderKey nvarchar(200))

As

Begin

--Exec PurchaseOrderReport 10000000000127

Set NoCount On

--Declare @PurchaseOrderKey Nvarchar(200)='1000000000000006'

Select --Header Info

  'PONo' = PurHed.PurchaseOrderId

  ,'POHeaderKey' = PurHed.PurchaseOrderKey

  ,'PODate' = PurHed.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, '')

  ,'SubTotal' = PurHed.SubTotal

  ,'Tax' = PurHed.Tax

  ,'Discount' = PurHed.DiscountAmount

  ,'Total' = PurHed.Total

  ,'HeaderSurcharge' = PurHed.SurchargesTotal

  ,'POLineKey' = PurDet.PurchaseOrderDetailKey

  ,'ItemCode' = Product.Id

  ,'Description' = PurDet.Description

  ,'Quantity' = PurDet.UOMQuantity

  ,'LineItemQty' = PurDet.Quantity

  ,'UnitPrice' = PurDet.Price

  ,'TaxPer' = PurDet.TaxRate

  ,'TaxAmt' = PurDet.Tax

  ,'DiscPer' = PurDet.DiscountPercentage

  ,'DiscAmt' = PurDet.DiscountAmount

  ,'LineTotal' = PurDet.Total

  ,'LINESURCHARGE' = convert(numeric(20,5), 0)

  ,'UOM'=InvUOM.Description

  , 'IsAuthorized' = PurHed.IsAuthorized

  , 'PurchaseorderId' = PurHed.Id

Into #temp

From PurPurchaseOrder As PurHed

  Inner Join PurPurchaseOrderDetail As PurDet On PurHed.PurchaseOrderKey = PurDet.PurchaseOrderKey

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

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

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

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

  Inner 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

  LEFT OUTER JOIN RtlStore As Store On PurHed.SiteId = Store.SiteId

  LEFT OUTER JOIN InvWarehouse As WH On PurDet.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

Where PurHed.PurchaseOrderKey = @PurchaseOrderKey

And  PurDet.IsDeleted = 0 AND PurDet.[Status]=0

UPDATE A

SET LINESURCHARGE = S.SURCHARGE

FROM #temp A,

(

SELECT SUM(A.Amount) 'SURCHARGE', B.POLineKey

FROM InvTransactionSurcharge A, #temp B

Where A.SourceType = 2

AND  A.SourceKey = B.POLineKey

GROUP BY POLineKey

)S

WHERE A.POLineKey = S.POLineKey

Select * From  #temp

--Drop Table #Temp

Set NoCount Off

End