iVend Database Database : Integration_Consolidated_TransactionSurcharge Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@TransactionKey

In

transaction key for which the various transaction surcharges that needs to be pulled from iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ConTrxTransaction

Table

Stores the Consolidated Transaction detail values

1

ConTrxTransactionSaleItem

Table

Stores the consolidated values for the Transaction Sale Item

1

ConTrxTransactionSurcharge

Table

Stores the consolidated information for the Transaction Surcharges

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 transaction surcharges is being applied on the documents

--that is getting posted in SAP Business One.

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_Consolidated_TransactionSurcharge]

    @TransactionKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON;

-- PRINT '###########################################################################################'

-- PRINT '####

-- PRINT '###########################################################################################'

Select TransactionKey, DocType, ExpenseCode, ISNULL(TaxCode,0 ) As TaxCode, Sum(LineTotal) As LineTotal, Sum(SaleLevelSurchargeTotal) As SaleLevelSurchargeTotal, convert(NVARCHAR(50), OriginalDocumentKey) OriginalDocumentKey, convert(NVARCHAR(50), LAYAWAYKEY) LAYAWAYKEY, SUM(Tax) As Tax , TaxRate, IsPaymentDiscountReversalSurcharge

From

(

Select C.TransactionKey, 'FRT' As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN ISNULL(C.TaxCodeKey, '0') = '0' THEN '0'

    ELSE (SELECT AccountingID FROM TaxTaxCode WHERE TaxCodeKey = C.TaxCodeKey)

    END AS TaxCode

   ,(C.Amount - C.DiscountAmount) As LineTotal

   ,Convert(Decimal(20,5), 0) As SaleLevelSurchargeTotal

   ,convert(NVARCHAR(50), 0) OriginalDocumentKey

   , -1 as LAYAWAYKEY

   , Convert(Decimal(20,5), ISNULL(C.Tax,0)) As Tax

   , Convert(Decimal(20,5), ISNULL(C.TaxRate,0)) As TaxRate

   , IsPaymentDiscountReversalSurcharge

From ConTrxTransaction A, ConTrxTransactionSurcharge C

Where A.TransactionKey = C.TransactionKey

And  C.SourceType   IN(0, 6)   --SHOWS THIS SURCHARGE IS FOR SALE TRANSACTION

And  C.Status   = 0   --SHOWS THAT THE SURCHARGE IS NOT DELETED

And  C.TransactionKey = @TransactionKey

UNION ALL

Select C.TransactionKey, 'ARI' As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN ISNULL(C.TaxCodeKey, '0') = '0' THEN '0'

    ELSE (SELECT AccountingID FROM TaxTaxCode WHERE TaxCodeKey = C.TaxCodeKey)

  END AS TaxCode

   ,(C.Amount - C.DiscountAmount) As LineTotal

   ,Convert(Decimal(20,5), 0) As SaleLevelSurchargeTotal

   ,0 As OriginalDocumentKey

   , -1 as LAYAWAYKEY

   , Convert(Decimal(20,5), ISNULL(C.Tax,0)) As Tax

   , Convert(Decimal(20,5), ISNULL(C.TaxRate,0)) As TaxRate

   , IsPaymentDiscountReversalSurcharge

From ConTrxTransaction A, ConTrxTransactionSaleItem B, ConTrxTransactionSurcharge C

Where A.TransactionKey=B.TransactionKey

And  B.TransactionItemKey = C.SourceKey

And  C.SourceType   = 4   --SHOWS THIS SURCHARGE IS FOR SALE ITEM

And  B.HasFulfillment = 0

And  B.[Type]   = 0   -- SHOWS IT IS A SALE ITEM???

And  C.Status   = 0   --SHOWS THAT THE SURCHARGE IS NOT DELETED

And  C.TransactionKey = @TransactionKey

-- PRINT '##############################################################'

-- PRINT '####= Get the list of the surchage against Credit Memo ######='

-- PRINT '##############################################################'

UNION ALL

Select C.TransactionKey, 'CRM' As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN ISNULL(C.TaxCodeKey, '0') = '0' THEN '0'

    ELSE (SELECT AccountingID FROM TaxTaxCode WHERE TaxCodeKey = C.TaxCodeKey)

  END AS TaxCode

  --,(C.Amount - C.DiscountAmount) As LineTotal

   , CASE WHEN C.IsRefund = 'FALSE' THEN (C.Amount - C.DiscountAmount) * -1

    ELSE (C.Amount - C.DiscountAmount) END As LineTotal

    ,Convert(Decimal(20,5), 0) As SaleLevelSurchargeTotal

   ,0 As OriginalDocumentKey

   , -1 as LAYAWAYKEY

   , Convert(Decimal(20,5), ISNULL(C.Tax,0)) As Tax

   , Convert(Decimal(20,5), ISNULL(C.TaxRate,0)) As TaxRate

   , IsPaymentDiscountReversalSurcharge

From ConTrxTransaction A, ConTrxTransactionSaleItem B, ConTrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionItemKey   = C.SourceKey

And  C.SourceType           = 4

And  B.Type                 = 1         -- SHOWS IT IS A REFUND ITEM

And  C.Status               = 0         --SHOWS THAT THE SURCHARGE IS NOT DELETED

And  C.TransactionKey = @TransactionKey

) As temp

Group By TransactionKey, DocType, ExpenseCode, TaxCode, SaleLevelSurchargeTotal, OriginalDocumentKey, LAYAWAYKEY, TaxRate, IsPaymentDiscountReversalSurcharge

Order By 3

END