iVend Database Database : Integration_GoodsReturnSurcharge Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReturnKey

In

(Primary Key of InvGoodsReturn)for which the Goods Return Information that needs to be pulled from iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

--Based on the output of this procedure the same transaction surcharges is being applied on the documents

--that is getting posted in SAP Business One.

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsReturnSurcharge]

@GoodsReturnKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #out

(

[SourceType]   [int]   NOT NULL,

[GoodsReturnDetailKey] [nvarchar](50)   NOT NULL,

[SurchargeKey]   [nvarchar](50)   NOT NULL,

[TaxCode]   [nvarchar](50) NOT NULL,

[LineTotal]   [decimal](20, 5) NOT NULL

)

--Header Surcharge

Insert Into #Out

Select Sur.SourceType, '0' As GoodsReturnDetailKey, Prc.SurchargeKey, isNull(Tax.AccountingID, '0') As TaxCode, (Isnull(Sur.Amount,0.00) - Isnull(Sur.DiscountAmount,0.00)) As LineTotal

From InvGoodsReturn Hed Inner Join InvTransactionSurcharge Sur On Hed.GoodsReturnKey = Sur.SourceKey

        Left Outer Join TaxTaxCode As Tax On Sur.TaxCodeKey = Tax.TaxCodeKey

        Right Outer Join PrcSurcharge As Prc On Sur.SurchargeKey = Prc.SurchargeKey

Where Sur.SourceType = 3 --Indicates Header Surcharge

And  Hed.GoodsReturnKey = @GoodsReturnKey

--Line Surcharge

Insert Into #Out

Select Sur.SourceType, Det.GoodsReturnDetailKey, Sur.SurchargeKey, isNull(Tax.AccountingID, '0') As TaxCode, (isNull(Sur.Amount, 0.00) - isNull(Sur.DiscountAmount, 0.00)) As LineTotal

From InvGoodsReturn Hed Inner Join InvGoodsReturnDetail Det On Hed.GoodsReturnKey = Det.GoodsReturnKey

        Inner join InvTransactionSurcharge Sur On Det.GoodsReturnDetailKey = Sur.SourceKey

        Left Outer Join TaxTaxCode Tax on Sur.TaxCodeKey = Tax.TaxCodeKey

Where Sur.SourceType = 4 --Indicates Header Surcharge

And  Hed.GoodsReturnKey = @GoodsReturnKey

Declare Out_Cursor Cursor FOR

SELECT GoodsReturnDetailKey, 3 - count(*) as ExtraRows

FROM #out

WHERE SourceType=4

GROUP BY GoodsReturnDetailKey

Declare @GoodsReturnDetailKey NVARCHAR(50), @ExtraRows int, @Counter int

OPEN Out_Cursor

Fetch Next From Out_Cursor Into @GoodsReturnDetailKey, @ExtraRows

WHILE @@FETCH_STATUS = 0

BEGIN

Select @Counter = 0

While @Counter < @ExtraRows

Begin

  -- Add Extra Rows to #out

  Insert Into #Out ( [SourceType], [GoodsReturnDetailKey], [SurchargeKey], [TaxCode], [LineTotal])

  Values (4, @GoodsReturnDetailKey, 0, 0, 0)

  Select @Counter = @Counter + 1

End

FETCH NEXT FROM Out_cursor

INTO @GoodsReturnDetailKey, @ExtraRows

END

CLOSE Out_cursor

DEALLOCATE Out_cursor

Select * from #Out order by SourceType, GoodsReturnDetailKey

SET NOCOUNT OFF

END