iVend Database Database : Integration_GoodsReturn Stored Procedure

Creation Date

7/4/2019 5:45 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReturnKey

In

The Goods Return key (Primary Key of InvGoodsReturn) for which the Goods Return information 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

InvGoodsReturn

Table

Stores the data related to Inventory Goods Return

1

InvGoodsReturnBatch

Table

Stores the data related to inventory goods return for batch

1

InvGoodsReturnDetail

Table

Stores the details of inventory goods return

1

InvGoodsReturnSerial

Table

Stores the data related to serial inventory goods return

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

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 SourceType the following documents gets posted in SAP Business One.

--Case1 SourceType = 0, Independent Goods Return

--Case2 SourceType = 1, Goods Return Based On GRPO

--If the item is serial or batch managed then this procedure does not return the serial and batch information for the item.

--Instead the 'Integration_GoodsReturnSerialBatch' prcedure gives the serail and batch information for the item.

--the output of both the procedure is joined based on 'CXS_TDKY' field of both the procedure's output

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsReturn]

          @GoodsReturnKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON

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 ', InvGoodsReturn.' + B.FieldName + ' AS H_' + B.FieldName

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvGoodsReturn'

  UNION ALL

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

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvGoodsReturnDetail'

 ) 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 InvGoodsReturn.SourceType,

   InvGoodsReturnDetail.SourceKey,

   InvGoodsReturnDetail.SourceDetailKey,

   InvGoodsReturn.GoodsReturnKey AS H_CXS_TRKY,

   InvGoodsReturn.SiteId AS H_CXS_FRST,

  Convert(Varchar, InvGoodsReturn.ReturnDate, 112) AS H_DocDate,

  Convert(Varchar, InvGoodsReturn.ReturnDate, 112) AS H_DocDueDate,

  Convert(Varchar, InvGoodsReturn.ReturnDate, 112) AS H_TaxDate,

   InvGoodsReturn.DiscountPercentage As H_DiscountPercent,

   InvGoodsReturn.Id AS H_CXS_TRID,

   InvGoodsReturn.AccountingId AS U_AccountingID,

   InvGoodsReturnDetail.GoodsReturnDetailKey AS CXS_TDKY,

   InvProduct.AccountingID AS L_ItemCode,

   InvProduct.Description AS L_ItemDescription,

   InvGoodsReturnDetail.Quantity * InvGoodsReturnDetail.ItemsPerUnit AS L_Quantity,

  ISNULL(InvGoodsReturnDetail.UOMQuantity ,0) AS L_UOM_Quantity,

  ISNULL(InvGoodsReturnDetail.UOMKey,0) AS L_UOMKey,

   InvGoodsReturnDetail.Price / InvGoodsReturnDetail.ItemsPerUnit As L_UnitPrice,

   InvGoodsReturnDetail.DiscountPercentage As L_DiscountPercent,

   InvWarehouse.Id AS L_WarehouseCode,

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

   (Select Count(*) From InvGoodsReturnSerial Where GoodsReturnDetailKey = InvGoodsReturnDetail.GoodsReturnDetailKey ) AS SerialRows,

   (Select Count(*) From InvGoodsReturnBatch Where GoodsReturnDetailKey = InvGoodsReturnDetail.GoodsReturnDetailKey ) AS BatchRows,

   PurVendor.AccountingID As VendorCode,

   InvGoodsReturnDetail.LineNumber,

  --(Select PurPurchaseOrderDetail.LineNumber From  PurPurchaseOrderDetail Where PurPurchaseOrderDetail.PurchaseOrderDetailKey = InvGoodReceiptDetail.SourceDetailKey ) As P_L_LineNumber,

  --isNull(Nullif(InvGoodReceipt.SalesPersonKey, 0), -1) As SalesPersonKey,

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

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

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

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

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

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

  CASE ISNULL(InvGoodsReturn.ExchangeRate, 0) WHEN 0 THEN 1 ELSE InvGoodsReturn.ExchangeRate END AS ExchangeRate

FROM        InvGoodsReturn INNER JOIN InvGoodsReturnDetail ON InvGoodsReturn.GoodsReturnKey = InvGoodsReturnDetail.GoodsReturnKey

                                  INNER JOIN InvWarehouse ON InvGoodsReturn.WarehouseKey = InvWarehouse.WarehouseKey

        INNER JOIN PurVendor ON InvGoodsReturn.VendorKey = PurVendor.VendorKey

                                  INNER JOIN InvProduct ON InvGoodsReturnDetail.ProductKey = InvProduct.ProductKey

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

                                  --LEFT OUTER JOIN (PurPurchaseorder INNER JOIN PurVendor ON PurPurchaseorder.VendorKey = PurVendor.VendorKey) ON PurPurchaseorder.PurchaseOrderKey = InvGoodReceiptDetail.SourceKey

        --LEFT OUTER JOIN InvWarehouse H ON InvWarehouse.IntransitWarehouseKey = H.WarehouseKey

        --LEFT OUTER JOIN CfgComment As HeaderComment On InvGoodsReturn.CommentKey = HeaderComment.CommentKey

        --LEFT OUTER JOIN CfgComment As LineComment On InvGoodsReturnDetail.CommentKey = LineComment.CommentKey

        LEFT OUTER JOIN CfgReasonCode As LineReasonCode On InvGoodsReturnDetail.ReasonKey = LineReasonCode.ReasonCodeKey

        LEFT OUTER JOIN CfgReasonCode As HeaderReasonCode On InvGoodsReturn.ReasonKey = HeaderReasonCode.ReasonCodeKey

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

WHERE  (InvGoodsReturn.GoodsReturnKey = @GoodsReturnKey  )

ORDER BY SerialRows, BatchRows

SET @SQL = '

  SELECT InvGoodsReturn.GoodsReturnKey, InvGoodsReturnDetail.GoodsReturnDetailKey ' + @UserDefinedFields + '

  FROM InvGoodsReturn INNER JOIN InvGoodsReturnDetail ON InvGoodsReturn.GoodsReturnKey = InvGoodsReturnDetail.GoodsReturnKey

  WHERE InvGoodsReturn.GoodsReturnKey = ''' + CAST( @GoodsReturnKey AS NVARCHAR(50))+ ''''

EXEC (@SQL)

END