iVend Database Database : Integration_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

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

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

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionFulfillment

Table

Defines a list of all those transactions which have a fulfillment plan attached to it.

1

TrxTransactionFulfillmentDetail

Table

Defines the details of all the transactions which have a fulfillment plan attached to them.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

TrxTransactionSurcharge

Table

Provides a reference of all the surcharges & other details attached with any transaction.

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

    @TransactionKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON;

  DECLARE @lAllowMultipleTransactionMode BIT

  SELECT @lAllowMultipleTransactionMode = AllowMultipleTransactionMode FROM CfgEnterprise

SELECT @lAllowMultipleTransactionMode =

CASE WHEN HasSaleExchanges = 1 Then 1 ELSE

CASE WHEN CONVERT(int, HasSales) + CONVERT(int, HasRefunds) + CONVERT(int, HasLayaways) + CONVERT(int, HasLayawaysCancellations) + CONVERT(int, HasOrders) > 1 THEN 1 ELSE 0 END

END

FROM TrxTransaction

Where TransactionKey = @TransactionKey

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

-- PRINT '####

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

Select TransactionKey, DocType, ExpenseCode, 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 --INTO #TEMPSURCHARGES

From

(

Select C.TransactionKey, 'FRT' As DocType

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '0' THEN '0'

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

    END AS TaxCode

   ,CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' AND HasRefunds = 'TRUE' THEN -1 * (C.Amount - C.DiscountAmount) ELSE (C.Amount - C.DiscountAmount) END 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 TrxTransaction A, TrxTransactionSurcharge 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

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, 'ORD' As DocType--CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'ORD' END

  ,C.SurchargeKey As ExpenseCode

  ,CASE

  WHEN C.TaxCodeKey = '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 TrxTransaction A, TrxTransactionOrder B, TrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionOrderKey   = C.SourceKey

And  C.SourceType                 = 1               --SHOWS THIS SURCHARGE IS FOR ORDER ITEM

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

And  C.TransactionKey = @TransactionKey

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

-- PRINT '####

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

UNION All

SELECT TransactionKey, 'ORD' As DocType---CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'ORD' END

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

  ,SurchargeKey As ExpenseCode

  ,CASE

  WHEN TaxCodeKey = '0' THEN '0'

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

  END AS TaxCode

  ,(Amount - 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 TrxTransactionSurcharge C

WHERE  SourceType = 7

And  C.Status       = 0

AND  sourcekey in (

      SELECT distinct f.FulfillmentKey

      FROM TrxTransaction A, TrxTransactionOrder B, TrxTransactionFulfillmentDetail FD, TrxTransactionFulfillment F

      Where A.TransactionKey   = B.TransactionKey

      And  F.TransactionKey   = A.TransactionKey

      And  F.FulfillmentKey   = FD.FulFillmentKey

      And  FD.SourceDetailKey   = b.TransactionOrderKey

      And  FD.SourceType   = 1

      And  B.HasFulfillment   = 'TRUE'

      And  A.TransactionKey   = @TransactionKey

     )

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'ARI' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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 TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionSurcharge 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 '####

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

UNION All

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'ARI' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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 TrxTransactionSurcharge C

WHERE  SourceType = 7

And  C.Status       = 0

AND  sourcekey in (

      SELECT distinct f.FulfillmentKey

      FROM TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionFulfillmentDetail FD, TrxTransactionFulfillment F

      Where A.TransactionKey   = B.TransactionKey

      And  F.TransactionKey   = A.TransactionKey

      And  f.FulfillmentKey   = FD.FulFillmentKey

      And  FD.SourceDetailKey   = b.TransactionItemKey

      And  FD.SourceType   = 0

      And  B.HasFulfillment   = 'TRUE'

      AND  B.Type                     = 0

      And  F.InventoryAllocationMethod = 0

      And  A.TransactionKey   = @TransactionKey

      )

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, 'RES' As DocType--CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'RES' END

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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 TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionSurcharge C,

   TrxTransactionFulfillment D, TrxTransactionFulfillmentDetail FD

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionItemKey   = C.SourceKey

And  C.SourceType                 = 4

And  B.HasFulfillment         = 'TRUE'

And  D.FulFillmentKey       = FD.FulFillmentKey

And  FD.SourceDetailKey       = B.TransactionItemKey

And  D.InventoryAllocationMethod = 1 -- ON FULFILLMENT REDUCE INVENTORY I.E U WILL MAKE RESERVE INVOICE OF IT

And  FD.SourceType                 = 0         -- SHOWS IT IS A FULFILLMENT AGAINST SALE

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 '####

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

UNION ALL

Select C.TransactionKey, 'RES' As DocType--CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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 TrxTransactionSurcharge C

WHERE  SourceType = 7

And  C.Status       = 0

AND  sourcekey in (

      SELECT distinct f.FulfillmentKey

      FROM TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionFulfillmentDetail FD, TrxTransactionFulfillment F

      Where A.TransactionKey   = B.TransactionKey

      And  F.TransactionKey   = A.TransactionKey

      And  f.FulfillmentKey   = FD.FulFillmentKey

      And  FD.SourceDetailKey   = b.TransactionItemKey

      And  FD.SourceType   = 0

      And  B.HasFulfillment   = 'TRUE'

      And  F.InventoryAllocationMethod = 1

      AND  B.Type                     = 0

      And  A.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 C.TaxCodeKey = '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 TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionSurcharge 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

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

-- PRINT '# Get the list of the surchage against delivery of Sale Item #'

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'DEL' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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 TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionItemKey   = C.SourceKey

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

And  B.Type                             = 4               -- SHOWS IT IS A DELIVERY AGAINST SALE ITEM

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

And  C.TransactionKey = @TransactionKey

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'LARI' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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

   ,B.TransactionLayawayKey as LAYAWAYKEY

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

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

   , IsPaymentDiscountReversalSurcharge

From TrxTransaction A, TrxTransactionLayaway B, TrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionLayawayKey   = C.SourceKey

And  C.SourceType           = 2

And  B.Type     = 0         -- SHOWS IT IS A LAYAWAY SALE ITEM

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

And  B.InventoryAllocationMethod = 0 --SHOWS THAT IT IS A LARI

And  C.TransactionKey = @TransactionKey

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

-- PRINT '####

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

UNION All

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'LARI' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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

   , B.TransactionLayawayKey as LAYAWAYKEY

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

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

   , IsPaymentDiscountReversalSurcharge

FROM TrxTransactionSurcharge C, TrxTransactionLayaway B

WHERE  SourceType = 7

And  C.Status       = 0

AND  sourcekey in (

      SELECT distinct f.FulfillmentKey

      FROM TrxTransaction A, /*TrxTransactionLayaway B,*/ TrxTransactionFulfillmentDetail FD, TrxTransactionFulfillment F

      Where A.TransactionKey   = B.TransactionKey

      And  F.TransactionKey   = A.TransactionKey

      And  f.FulfillmentKey   = FD.FulFillmentKey

      And  FD.SourceDetailKey   = b.TransactionLayawayKey

      And  FD.SourceType   = 2

      And  B.HasFulfillment   = 'TRUE'

      And  F.InventoryAllocationMethod = 0

      AND  B.Type                     = 0

      And  A.TransactionKey   = @TransactionKey

      )

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'LRES' END As DocType

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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

   ,B.TransactionLayawayKey as LAYAWAYKEY

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

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

   , IsPaymentDiscountReversalSurcharge

From TrxTransaction A, TrxTransactionLayaway B, TrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionLayawayKey   = C.SourceKey

And  C.SourceType           = 2

And  B.Type     = 0         -- SHOWS IT IS A LAYAWAY SALE ITEM

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

And  B.InventoryAllocationMethod = 1 --SHOWS THAT IT IS A LARI

And  C.TransactionKey = @TransactionKey

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

-- PRINT '####

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'LRES' END As DocType

  --,SourceKey

  --,C.TransactionSurchargeKey As U_TransactionSurchargeKey

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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

   , B.TransactionLayawayKey as LAYAWAYKEY

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

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

   , IsPaymentDiscountReversalSurcharge

FROM TrxTransactionSurcharge C, TrxTransactionLayaway B

WHERE  SourceType = 7

And  C.Status       = 0

AND  sourcekey in (

      SELECT distinct f.FulfillmentKey

      FROM TrxTransaction A, /*TrxTransactionLayaway B,*/ TrxTransactionFulfillmentDetail FD, TrxTransactionFulfillment F

      Where A.TransactionKey   = B.TransactionKey

      And  F.TransactionKey   = A.TransactionKey

      And  f.FulfillmentKey   = FD.FulFillmentKey

      And  FD.SourceDetailKey   = b.TransactionLayawayKey

      And  FD.SourceType   = 2

      And  B.HasFulfillment   = 'TRUE'

      And  F.InventoryAllocationMethod = 1

      AND  B.Type                     = 0

      And  A.TransactionKey   = @TransactionKey

      )

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

-- PRINT '####= Get the list of the surchage against Layaway(LCRM) ######='

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

UNION ALL

Select C.TransactionKey, CASE WHEN @lAllowMultipleTransactionMode = 'FALSE' THEN 'FRT' ELSE 'LCRM' END As DocType

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '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

   ,B.TransactionLayawayKey as LAYAWAYKEY

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

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

   , IsPaymentDiscountReversalSurcharge

From TrxTransaction A, TrxTransactionLayaway B, TrxTransactionSurcharge C

Where A.TransactionKey       = B.TransactionKey

And  B.TransactionLayawayKey   = C.SourceKey

And  C.SourceType           = 2

And  B.Type     = 1         -- SHOWS IT IS A LAYAWAY CANCELLATION

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

And  C.TransactionKey = @TransactionKey

UNION ALL

Select C.TransactionKey, 'ARISO'

   ,C.SurchargeKey As ExpenseCode

   ,CASE

    WHEN C.TaxCodeKey = '0' THEN '0'

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

  END AS TaxCode

   ,CASE WHEN  SurchargeSourceType IN( 0, 2) THEN (C.Amount - C.DiscountAmount) ELSE 0 END As LineTotal

   ,CASE WHEN  SurchargeSourceType = 1 THEN (C.Amount - C.DiscountAmount) ELSE 0 END As SaleLevelSurchargeTotal

   ,b.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 TrxTransaction A, TrxTransactionSaleItem B, TrxTransactionSurcharge 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]   = 3   -- DELIVERY AGAINST SALES ORDER

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