iVend Database Database : Integration_PurchaseOrder Stored Procedure

Creation Date

8/20/2019 8:44 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@POKey

In

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

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

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtCurrency

Table

Define a list of all currecies defined 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

TaxTaxCode

Table

TaxCode is defined at the SBO and it's replicated from SBO to iVend. We can only view the TaxCodes at MC and associate them with the different objects, like Product, Customer, Enterprise, etc.

1

--Based on the output of this procedure the same Purchase Order is getting posted in SAP Business One.

--Neither this procedure returns the various surcharges applicable on the transaction posted in iVend

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_PurchaseOrder]

-- Add the parameters for the stored procedure here

@POKey nvarchar(50)

AS

BEGIN

DECLARE @COUNT INT, @COUNTER INT, @UserDefinedFields NVARCHAR(MAX), @FieldName NVARCHAR(100)

SET @UserDefinedFields = ''

CREATE TABLE #tmp_UserDefinedFields (RowNum INT, FieldName NVARCHAR(100));

WITH UserDefinedFields(FieldName)

AS

(

SELECT ', PurPurchaseOrder.' + B.FieldName + ' AS H_' + B.FieldName

FROM CfgUserDefinedTable A

INNER JOIN CfgUserDefinedFieldDetail B ON A.UserDefinedTableKey = B.UserDefinedTableKey

WHERE A.TableName = 'PurPurchaseOrder'

UNION ALL

SELECT ', PurPurchaseOrderDetail.' + B.FieldName + ' AS L_' + B.FieldName

FROM CfgUserDefinedTable A

INNER JOIN CfgUserDefinedFieldDetail B ON A.UserDefinedTableKey = B.UserDefinedTableKey

WHERE A.TableName = 'PurPurchaseOrderDetail'

) INSERT INTO #tmp_UserDefinedFields SELECT ROW_NUMBER() OVER (ORDER BY FieldName), FieldName FROM UserDefinedFields

SET @COUNT = @@ROWCOUNT

SET @COUNTER = 1

WHILE @COUNTER <= @COUNT

BEGIN

SELECT @FieldName = FieldName FROM #tmp_UserDefinedFields WHERE RowNum = @COUNTER

SET @UserDefinedFields = @UserDefinedFields + @FieldName

SET @COUNTER = @COUNTER + 1

END

DECLARE @SQL NVARCHAR(MAX)

SELECT  PurPurchaseOrder.PurchaseOrderKey AS U_TransactionKey

 , PurPurchaseOrder.Id AS U_TransactionId

 , PurPurchaseOrderDetail.LineNumber AS L_LineNumber

 , PurPurchaseOrder.SiteId As U_StoreKey

 , PurVendor.AccountingID AS H_CardCode

 , Convert(varchar,PurPurchaseOrder.DeliveryDate,112) AS H_DocDueDate

 , Convert(varchar,PurPurchaseOrder.Created,112) AS H_DocDate

 , Convert(varchar,PurPurchaseOrder.Created,112) AS H_TaxDate

 , PurPurchaseOrder.IsDeleted AS H_IsDeleted

 , PurPurchaseOrder.DiscountPercentage AS H_DiscountPercent

 , PurPurchaseOrder.AccountingId AS U_AccountingID

 , PurPurchaseOrderDetail.PurchaseOrderDetailKey AS L_PurchaseOrderDetailKey

 , InvWarehouse.AccountingID AS L_WarehouseCode

 , InvProduct.AccountingID AS L_ItemCode

 , PurPurchaseOrderDetail.Description AS L_ItemDescription

 , CASE WHEN PurPurchaseOrderDetail.TaxKey = '0' THEN '0' ELSE TaxTaxCode.AccountingID END AS L_TaxCode

 , PurPurchaseOrderDetail.Price AS L_BasePrice

 , PurPurchaseOrderDetail.Quantity AS L_Quantity

 , ISNULL(PurPurchaseOrderDetail.UOMQuantity ,0) AS L_UOM_Quantity

 , ISNULL(PurPurchaseOrderDetail.UOMKey,0) AS L_UOMKey

 , PurPurchaseOrderDetail.TaxRate AS L_TaxRate

 , isNull(PurPurchaseOrderDetail.QuantityReceived, 0) As L_QuantityReceived

 , PurPurchaseOrderDetail.IsDeleted AS L_IsDeleted

 , PurPurchaseOrderDetail.DiscountPercentage AS L_DiscountPercent

 , 0 As QtyPartaillyDelivered

 , cast(isNull(PurPurchaseOrder.Comments, '') as nvarchar(254)) As H_Comment

 , cast(isNull(PurPurchaseOrderDetail.Comments, '') As nvarchar(100)) As L_Comment

 , isNull(PurPurchaseOrder.ReferenceNumber, '') As H_ReferenceNumber

 , ISNULL(PurPurchaseOrder.IsAuthorized, 'TRUE') As H_Authorized

 , ISNULL(HeaderReasonCode.Id,'') As H_ReasonCode

 , ISNULL(LineReasonCode.Id,'') As L_ReasonCode

 , CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END AS H_BranchKey

--, PurPurchaseOrderDetail.Total As L_PriceAfterVat

 ,ISNULL(PmtCurrency.Id, '') AS H_DOCCURRENCY

 ,PurPurchaseOrder.ExchangeRate

 ,ISNULL(PurPurchaseOrderDetail.IsTaxOnly  , 0) AS IsTaxOnly

Into #temp

FROM   PurPurchaseOrder INNER JOIN PurPurchaseOrderDetail ON PurPurchaseOrder.PurchaseOrderKey = PurPurchaseOrderDetail.PurchaseOrderKey

      INNER JOIN PurVendor ON PurPurchaseOrder.VendorKey = PurVendor.VendorKey

      INNER JOIN InvWarehouse ON PurPurchaseOrderDetail.WarehouseKey = InvWarehouse.WarehouseKey

      INNER JOIN InvProduct ON PurPurchaseOrderDetail.ProductKey = InvProduct.ProductKey

      LEFT OUTER JOIN TaxTaxCode ON PurPurchaseOrderDetail.TaxKey = TaxTaxCode.TaxCodeKey

      LEFT OUTER JOIN CfgReasonCode As HeaderReasonCode On PurPurchaseOrder.ReasonCodeKey = HeaderReasonCode.ReasonCodeKey

      LEFT OUTER JOIN CfgReasonCode As LineReasonCode On PurPurchaseOrderDetail.ReasonCodeKey = LineReasonCode.ReasonCodeKey

      LEFT OUTER JOIN PmtCurrency On PurPurchaseOrder.DocumentCurrencyKey = PmtCurrency.CurrencyKey

WHERE     PurPurchaseOrder.PurchaseOrderKey = @POKey

Order By PurPurchaseOrderDetail.LineNumber

If Exists(Select 1 From #temp Where L_QuantityReceived > 0)

Update #temp

Set  QtyPartaillyDelivered = 1

SELECT * FROM #temp

--GET ALL THE UDF Data

SET @SQL = 'SELECT PurPurchaseOrder.PurchaseOrderKey, PurPurchaseOrderDetail.PurchaseOrderDetailKey  ' + @UserDefinedFields + '

  FROM PurPurchaseOrder

  INNER JOIN PurPurchaseOrderDetail ON PurPurchaseOrder.PurchaseOrderKey = PurPurchaseOrderDetail.PurchaseOrderKey

  WHERE     PurPurchaseOrder.PurchaseOrderKey = ''' + CAST(@POKey AS NVARCHAR(50)) + '''

  ORDER BY PurPurchaseOrderDetail.LineNumber'

EXEC (@SQL)

END