iVend Database Database : Integration_GoodsReceiptSurcharge Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReceiptKey

In

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_GoodsReceiptSurcharge]

@GoodsReceiptKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #out

(

[SourceType]   [int]   NOT NULL,

[GoodsReceiptDetailKey] 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 GoodsReceiptDetailKey, Prc.SurchargeKey, isNull(Tax.AccountingID, '0') As TaxCode, (Isnull(Sur.Amount,0.00) - Isnull(Sur.DiscountAmount,0.00)) As LineTotal

From InvGoodReceipt Hed Inner Join InvTransactionSurcharge Sur On Hed.GoodsReceiptKey = 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 = 5 --Indicates Header Surcharge

And  Sur.IsBaseDocumentSurcharge = 0

And  Hed.GoodsReceiptKey = @GoodsReceiptKey

--Line Surcharge

Insert Into #Out

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

From InvGoodReceipt Hed Inner Join InvGoodReceiptDetail Det On Hed.GoodsReceiptKey = Det.GoodsReceiptKey

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

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

Where Sur.SourceType IN (6) --Indicates line Surcharge

And  Sur.IsBaseDocumentSurcharge = 0

And  Hed.GoodsReceiptKey = @GoodsReceiptKey

Declare Out_Cursor Cursor FOR

SELECT GoodsReceiptDetailKey, 3 - count(*) as ExtraRows

FROM #out

WHERE SourceType=6

GROUP BY GoodsReceiptDetailKey

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

OPEN Out_Cursor

Fetch Next From Out_Cursor Into @GoodsReceiptDetailKey, @ExtraRows

WHILE @@FETCH_STATUS = 0

BEGIN

Select @Counter = 0

While @Counter < @ExtraRows

Begin

  -- Add Extra Rows to #out

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

  Values (6, @GoodsReceiptDetailKey, '0', 0, 0)

  Select @Counter = @Counter + 1

End

FETCH NEXT FROM Out_cursor

INTO @GoodsReceiptDetailKey, @ExtraRows

END

CLOSE Out_cursor

DEALLOCATE Out_cursor

Select * from #Out order by SourceType, GoodsReceiptDetailKey

SET NOCOUNT OFF

END