iVend Database Database : Integration_PurchaseOrderSurcharge Stored Procedure

Creation Date

4/13/2015 12:00 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

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[Integration_PurchaseOrderSurcharge]

@PurchaseOrderKey NVARCHAR(50)

AS

BEGIN

CREATE TABLE #out(

[SourceType] [int] NOT NULL,

[PurchaseOrderDetailKey] NVARCHAR(50) NOT NULL,

[SurchargeKey] NVARCHAR(50) NOT NULL,

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

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

)

SET NOCOUNT ON;

--Header Surcharge

/*

Insert Into #Out

Select Isnull(Sur.SourceType,1) as SourceType, 0 As PurchaseOrderDetailKey, Prc.SurchargeKey, isNull(Tax.AccountingID, ''0'') As TaxCode, Isnull(Sur.Amount,0) As LineTotal

From PurPurchaseOrder Hed, InvTransactionSurcharge Sur, TaxTaxCode As Tax, PrcSurcharge As Prc

Where Hed.PurchaseOrderKey = Sur.SourceKey

And  Hed.TaxCodeKey *= Tax.TaxCodeKey

And  Sur.SurchargeKey =* Prc.SurchargeKey

And  Sur.SourceType = 1  --Indicates Header Surcharge

And  Hed.PurchaseOrderKey = @PurchaseOrderKey

*/

Insert Into #Out

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

From PurPurchaseOrder Hed Inner Join InvTransactionSurcharge Sur On Hed.PurchaseOrderKey = 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 = 1 --Indicates Header Surcharge

And  Hed.PurchaseOrderKey = @PurchaseOrderKey

--Line Surcharge (Maximum 3 surcharges)

/*

Insert Into #Out

Select Sur.SourceType, Det.PurchaseOrderDetailKey, Sur.SurchargeKey, isNull(Tax.AccountingID, ''0'') As TaxCode, Sur.Amount As LineTotal

From PurPurchaseOrder Hed, PurPurchaseOrderDetail Det, InvTransactionSurcharge Sur, TaxTaxCode As Tax

Where Hed.PurchaseOrderKey = Det.PurchaseOrderKey

And  Det.PurchaseOrderDetailKey = Sur.SourceKey

And  Sur.TaxCodeKey *= Tax.TaxCodeKey

And  Sur.SourceType = 2  --Indicates Header Surcharge

And  Hed.PurchaseOrderKey = @PurchaseOrderKey

*/

Insert Into #Out

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

From PurPurchaseOrder Hed Inner Join PurPurchaseOrderDetail Det On Hed.PurchaseOrderKey = Det.PurchaseOrderKey

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

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

Where Sur.SourceType = 2 --Indicates Header Surcharge

And  Hed.PurchaseOrderKey = @PurchaseOrderKey

-- SELECT PurchaseOrderDetailKey, 3-count(*) as ExtraRows

-- FROM #out

-- WHERE sourcetype=2

-- GROUP BY PurchaseOrderDetailKey

Declare Out_Cursor Cursor FOR

SELECT PurchaseOrderDetailKey, 3-count(*) as ExtraRows

FROM #out

WHERE sourcetype=2

GROUP BY PurchaseOrderDetailKey

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

OPEN Out_Cursor

Fetch Next From Out_Cursor Into @PurchaseOrderDetailKey, @ExtraRows

WHILE @@FETCH_STATUS = 0

BEGIN

Select @Counter = 0

While @Counter < @ExtraRows

Begin

  -- Add Extra Rows to #out

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

  Values (2, @PurchaseOrderDetailKey, '0', 0, 0)

  Select @Counter = @Counter + 1

End

FETCH NEXT FROM Out_cursor

INTO @PurchaseOrderDetailKey, @ExtraRows

END

CLOSE Out_cursor

DEALLOCATE Out_cursor

Select * from #Out order by SourceType, PurchaseOrderDetailKey, SurchargeKey Desc

SET NOCOUNT OFF;

END