iVend Database Database : Integration_Consolidated_TransactionPayment 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 transactions 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

ConTrxTransactionPayment

Table

Stores the consolidated values for the Transaction payments

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtPaymentType

Table

Defines a list of payment types specified in the system. These payment types are used to take payment against a transaction at POS.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

--Based on the output of this procedure the incoming/outgoing payment transaction is getting posted in SAP Business One.

--This procedure does not returns any AR Payment made in iVend.

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_Consolidated_TransactionPayment]

@TransactionKey NVARCHAR(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

--Declare @lPaymentId nvarchar(20)

--Select @lPaymentId = Id from PmtCurrency Where IsBaseCurrency = 1

Create Table #temp

 (

  TransactionKey NVARCHAR(50)

  ,TransactionPaymentKey NVARCHAR(50)

  ,TransactionId NVARCHAR(100)

  ,U_SiteId INT

  ,CustomerKey NVARCHAR(50)

  ,CustomerCode NVARCHAR(100)

  , BusinessDate VARCHAR(8)

  ,AccountNumber NVARCHAR(100)

  , ExpirationDate VARCHAR(8)

  ,ZipCode NVARCHAR(30)

  ,AccountType NVARCHAR(100)

  ,PaymentReferenceNumber NVARCHAR(100)

  ,Amount DECIMAL(20,5)

  ,ChangeAmount DECIMAL(20,5)

  ,AMOUNTLESSCHANGE DECIMAL(20,5)

  ,RoundingAmount DECIMAL(20,5)

  ,Tender NVARCHAR(100)

  ,TotalAmount DECIMAL(20,5)

  ,TotalRounding DECIMAL(20,5)

  ,DiscountAmount DECIMAL(20,5)

  ,NetPayableAmount DECIMAL(20,5)

  ,MoreThanOneTender NVARCHAR(100)

  ,PaymentTypeKey NVARCHAR(50)

  ,IsIntegrated BIT

  ,GiftCertificateKey NVARCHAR(50)

  ,Warehouse NVARCHAR(100)

  ,GiftCertificateProductKey NVARCHAR(50)

  ,BankCode NVARCHAR(100)

  ,ValidationCode NVARCHAR(100)

  ,Amount_FC DECIMAL(20,5)

  ,AMOUNTLESSCHANGE_FC DECIMAL(20,5)

  ,CurrencyCode NVARCHAR(100)

  ,Amount_CASH DECIMAL(20,5)

  ,ChangeAmount_CASH DECIMAL(20,5)

  ,AMOUNTLESSCHANGE_CASH DECIMAL(20,5)

  ,RoundingAmount_CASH DECIMAL(20,5)

  ,BranchKey NVARCHAR(100)

  ,TransactionCurrencyCode NVARCHAR(100)

 )

Insert Into #Temp

SELECT ConTrxTransactionPayment.TransactionKey     As TransactionKey

  ,MIN(ConTrxTransactionPayment.TransactionPaymentKey) As TransactionPaymentKey

  ,ConTrxTransaction.TransactionId       As TransactionId

  ,RtlStore.SiteID           AS U_SiteId

  ,ConTrxTransaction.CustomerKey       AS CustomerKey

  ,CusCustomer.AccountingID         As CustomerCode

  ,Convert(varchar, ConTrxTransaction.BusinessDate, 112) As BusinessDate

  ,Case When LTRIM(RTRIM(IsNull(CardNumber, ''))) = '' Then '0' Else REPLACE(CardNumber,'X','') End As AccountNumber

  , CASE WHEN CheckDueDate IS NULL THEN CONVERT(VARCHAR, ISNULL(ExpirationDate, dbo.GetCompanyDateTime()), 112) ELSE CONVERT(VARCHAR, CheckDueDate, 112) END As ExpirationDate

  ,ZipCode             As ZipCode

  ,''               As AccountType

  ,PaymentReferenceNumber         As PaymentReferenceNumber

  ,SUM(AMOUNT) As Amount

  ,SUM(ISNULL(CHANGEAMOUNT,0))       As ChangeAmount

  ,SUM(ISNULL(AMOUNT,0)) - SUM(Isnull(CHANGEAMOUNT,0)) AS AMOUNTLESSCHANGE

  ,SUM(Isnull(ROUNDINGAMOUNT,0)) As RoundingAmount

  ,'CASH'             AS Tender

  ,0         As TotalAmount

  ,0         As TotalRounding

  ,SUM(Isnull(ConTrxTransactionPayment.DiscountAmount,0)) As DiscountAmount

  ,SUM(Isnull(ConTrxTransactionPayment.NetPayableAmount,0)) As NetPayableAmount

  ,'N'             As MoreThanOneTender

  ,MIN(ConTrxTransactionPayment.PaymentTypeKey)   As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated       As IsIntegrated

  ,ConTrxTransactionPayment.DetailKey       As GiftCertificateKey

  ,InvWarehouse.AccountingID         As Warehouse

  ,'-1'               As GiftCertificateProductKey

  ,''               As BankCode

  ,''               As ValidationCode

  ,SUM(ISNULL(ForeignCurrencyAmount, 0)) As Amount_FC

  ,SUM(ISNULL(ForeignCurrencyAmount,0)) - SUM(ISNULL(CHANGEAMOUNT,0)) AS AMOUNTLESSCHANGE_FC

  , PmtCurrency.Id           As  CurrencyCode

  ,0     As Amount_CASH

  ,0     As ChangeAmount_CASH

  ,0     As AMOUNTLESSCHANGE_CASH

  ,0     As RoundingAmount_CASH

  ,CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END AS BranchKey,

  (SELECT Id FROM PmtCurrency WHERE CurrencyKey = RtlStore.BaseCurrencyKey) AS TransactionCurrencyCode

FROM ConTrxTransactionPayment

  ,ConTrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

  , PmtCurrency

WHERE ConTrxTransactionPayment.TRANSACTIONKEY= @TransactionKey

AND  ConTrxTransactionPayment.TENDERTYPE = 0

AND  ConTrxTransactionPayment.IsVoided = 0

And  ConTrxTransaction.TRANSACTIONKEY= @TransactionKey

And  ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  ConTrxTransaction.StoreKey = RtlStore.StoreKey

And  ConTrxTransactionPayment.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

AND  ConTrxTransactionPayment.CurrencyKey = PmtCurrency.CurrencyKey

group BY ConTrxTransactionPayment.TransactionKey, ConTrxTransaction.TransactionId ,RtlStore.SiteID ,ConTrxTransaction.CustomerKey

  ,CusCustomer.AccountingID ,ConTrxTransaction.BusinessDate

  ,CardNumber ,ExpirationDate ,ZipCode ,PaymentReferenceNumber, PmtPaymentType.IsIntegrated

  ,ConTrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

  ,PmtCurrency.Id

  ,InvWarehouse.BranchCode

  ,RtlStore.BaseCurrencyKey

  ,ConTrxTransactionPayment.CheckDueDate

UNION

SELECT ConTrxTransactionPayment.TransactionKey     As TransactionKey

  ,MIN(ConTrxTransactionPayment.TransactionPaymentKey) As TransactionPaymentKey

  ,ConTrxTransaction.TransactionId       As TransactionId

  ,RtlStore.SiteID           AS U_SiteId

  ,ConTrxTransaction.CustomerKey       AS CustomerKey

  ,CusCustomer.AccountingID         As CustomerCode

  ,Convert(varchar, ConTrxTransaction.BusinessDate, 112) As BusinessDate

  ,Case When LTRIM(RTRIM(IsNull(CardNumber, ''))) = '' Then '0' Else REPLACE(CardNumber,'X','') End As AccountNumber

  , CASE WHEN CheckDueDate IS NULL THEN CONVERT(VARCHAR, ISNULL(ExpirationDate, dbo.GetCompanyDateTime()), 112) ELSE CONVERT(VARCHAR, CheckDueDate, 112) END As ExpirationDate

  ,ZipCode             As ZipCode

  ,''               As AccountType

  ,PaymentReferenceNumber         As PaymentReferenceNumber

  ,SUM(ISNULL(AMOUNT, 0))     As Amount

  ,SUM(Isnull(CHANGEAMOUNT,0))       As ChangeAmount

  ,SUM(Isnull(AMOUNT,0)) - SUM(Isnull(CHANGEAMOUNT,0)) AS AMOUNTLESSCHANGE

  ,SUM(Isnull(ROUNDINGAMOUNT,0))       As RoundingAmount

  ,'CASH'             AS Tender

  ,0         As TotalAmount

  ,0         As TotalRounding

  ,SUM(Isnull(ConTrxTransactionPayment.DiscountAmount,0))         As DiscountAmount

  ,SUM(Isnull(ConTrxTransactionPayment.NetPayableAmount,0))       As NetPayableAmount

  ,'N'             As MoreThanOneTender

  ,MIN(ConTrxTransactionPayment.PaymentTypeKey)   As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated       As IsIntegrated

  ,ConTrxTransactionPayment.DetailKey       As GiftCertificateKey

  ,InvWarehouse.AccountingID         As Warehouse

  ,'-1'               As GiftCertificateProductKey

  ,''               As BankCode

  ,''               As ValidationCode

  ,SUM(ISNULL(ForeignCurrencyAmount, 0))   As Amount_FC

  ,SUM(ISNULL(ForeignCurrencyAmount,0)) - SUM(ISNULL(CHANGEAMOUNT,0)) AS AMOUNTLESSCHANGE_FC

  , PmtCurrency.Id           As  CurrencyCode

  ,0         As Amount_CASH

  ,0         As ChangeAmount_CASH

  ,0         As AMOUNTLESSCHANGE_CASH

  ,0         As RoundingAmount_CASH

  ,CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END AS BranchKey,

  (SELECT Id FROM PmtCurrency WHERE CurrencyKey = RtlStore.BaseCurrencyKey) AS TransactionCurrencyCode

FROM ConTrxTransactionPayment

  ,ConTrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

  , PmtCurrency

WHERE ConTrxTransactionPayment.TRANSACTIONKEY= @TransactionKey

AND  ConTrxTransactionPayment.TENDERTYPE =7

AND  ConTrxTransactionPayment.IsVoided = 0

And  ConTrxTransaction.TRANSACTIONKEY= @TransactionKey

And  ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  ConTrxTransaction.StoreKey = RtlStore.StoreKey

And  ConTrxTransactionPayment.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

AND  ConTrxTransactionPayment.CurrencyKey = PmtCurrency.CurrencyKey

group BY ConTrxTransactionPayment.TransactionKey, ConTrxTransaction.TransactionId ,RtlStore.SiteID ,ConTrxTransaction.CustomerKey

  ,CusCustomer.AccountingID ,ConTrxTransaction.BusinessDate

  ,CardNumber ,ExpirationDate ,ZipCode ,PaymentReferenceNumber, PmtPaymentType.IsIntegrated

  ,ConTrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

  ,PmtCurrency.Id

  ,InvWarehouse.BranchCode

  ,RtlStore.BaseCurrencyKey

  ,ConTrxTransactionPayment.CheckDueDate

UNION

SELECT ConTrxTransactionPayment.TransactionKey     As TransactionKey

  ,ConTrxTransactionPayment.TransactionPaymentKey   As TransactionPaymentKey

  ,ConTrxTransaction.TransactionKey     As TransactionId

  ,RtlStore.SiteID         AS U_SITEID

  ,ConTrxTransaction.CustomerKey       As CustomerKey

  ,CusCustomer.AccountingID       As CustomerCode

  ,Convert(varchar, ConTrxTransaction.BusinessDate, 112) As BusinessDate

  ,Case When LTRIM(RTRIM(IsNull(CardNumber, ''))) = '' Then '0' Else REPLACE(CardNumber,'X','') End As AccountNumber

  , CASE WHEN CheckDueDate IS NULL THEN CONVERT(VARCHAR, ISNULL(ExpirationDate, dbo.GetCompanyDateTime()), 112) ELSE CONVERT(VARCHAR, CheckDueDate, 112) END As ExpirationDate

  ,ZipCode           As ZipCode

  ,Case

  When AccountType = 0 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-OTHERS' WHEN TENDERTYPE = 2 THEN 'DEBIT-OTHERS' ELSE '' END)

  When AccountType = 1 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-MASTERCARD' WHEN TENDERTYPE = 2 THEN 'DEBIT-MASTERCARD' ELSE '' END)

  When AccountType = 2 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-VISA' WHEN TENDERTYPE = 2 THEN 'DEBIT-VISA' ELSE '' END)

  When AccountType = 3 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-AMEX' WHEN TENDERTYPE = 2 THEN 'DEBIT-AMEX' ELSE '' END)

  When AccountType = 4 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-DINERS CLUB' WHEN TENDERTYPE = 2 THEN 'DEBIT-DINERS CLUB' ELSE '' END)

  When AccountType = 5 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-DISCOVER' WHEN TENDERTYPE = 2 THEN 'DEBIT-DISCOVER' ELSE '' END)

  When AccountType = 6 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-JCB' WHEN TENDERTYPE = 2 THEN 'DEBIT-JCB' ELSE '' END)

  When AccountType = 7 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-CARTBLANCE' WHEN TENDERTYPE = 2 THEN 'DEBIT-CARTBLANCE' ELSE '' END)

  When AccountType = 8 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-MAESTRO' WHEN TENDERTYPE = 2 THEN 'DEBIT-MAESTRO' ELSE '' END)

  When AccountType = 9 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-VISAELECTRON' WHEN TENDERTYPE = 2 THEN 'DEBIT-VISAELECTRON' ELSE '' END)

  When AccountType = 10 Then (CASE WHEN TENDERTYPE = 1 THEN 'CREDIT-CHINAUNIONPAY' WHEN TENDERTYPE = 2 THEN 'DEBIT-CHINAUNIONPAY' ELSE '' END)

  End             As AccountType

  ,PaymentReferenceNumber         As PaymentReferenceNumber

  ,Isnull(AMOUNT,0)     As Amount

  ,Isnull(CHANGEAMOUNT,0)       As ChangeAmount

  ,Isnull(AMOUNT,0) - Isnull(CHANGEAMOUNT,0) AS AmountLessChange

  ,Isnull(ROUNDINGAMOUNT,0)       As RoundingAmount

  , CASE

  WHEN TENDERTYPE = 1 THEN 'CREDIT CARD'

  WHEN TENDERTYPE = 2 THEN 'DEBIT CARD'

  WHEN TENDERTYPE = 3 THEN 'GIFT CERTIFICATE'

  WHEN TENDERTYPE = 4 THEN 'ON ACCOUNT'

  WHEN TENDERTYPE = 5 THEN 'CHECK'

  WHEN TENDERTYPE = 6 THEN 'TRAVELERS CHECK'

  WHEN TENDERTYPE = 8 THEN 'VOUCHER'

  WHEN TENDERTYPE = 9 THEN 'LOYALTY REDEMPTION'

  WHEN TENDERTYPE = 10 THEN 'CUSTOM'

  WHEN TENDERTYPE = 12 THEN 'CLUB-PAYMENT'

  WHEN TENDERTYPE = 13 THEN 'CLUB-REDEMPTION'

  WHEN TENDERTYPE = 14 THEN 'EBT'

  END             AS Tender

  ,0         As TotalAmount

  ,0         As TotalRounding

  ,Isnull(ConTrxTransactionPayment.DiscountAmount,0)         As DiscountAmount

  ,Isnull(ConTrxTransactionPayment.NetPayableAmount,0)         As NetPayableAmount

  ,'N'             As MoreThanOneTender

  ,ConTrxTransactionPayment.PaymentTypeKey   As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated       As IsIntegrated

  ,ConTrxTransactionPayment.DetailKey     As GiftCertificateKey

  , InvWarehouse.AccountingID       As Warehouse

  , PmtGiftCertificate.ProductKey       As GiftCertificateProductKey

  ,''             As BankCode

  ,''             As ValidationCode

  ,ISNULL(ForeignCurrencyAmount, 0) As Amount_FC

  ,ISNULL(ForeignCurrencyAmount,0) - ISNULL(CHANGEAMOUNT,0) AS AMOUNTLESSCHANGE_FC

  , CASE WHEN TenderType = 10 THEN  PmtCurrency.Id ELSE (SELECT Id FROM PmtCurrency WHERE IsBaseCurrency = 1) END As  CurrencyCode--In case of other than 'CASH' Tender , the basecurrency is required

  --, (SELECT Id FROM PmtCurrency WHERE IsBaseCurrency = 1) As  CurrencyCode

  ,0         As Amount_CASH

  ,0         As ChangeAmount_CASH

  ,0         As AMOUNTLESSCHANGE_CASH

  ,0         As RoundingAmount_CASH

  ,CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END AS BranchKey,

  (SELECT Id FROM PmtCurrency WHERE CurrencyKey = RtlStore.BaseCurrencyKey) AS TransactionCurrencyCode

FROM ConTrxTransactionPayment INNER JOIN ConTrxTransaction

  ON ConTrxTransactionPayment.TransactionKey = ConTrxTransaction.TransactionKey

  INNER JOIN CusCustomer

  ON ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

  INNER JOIN RtlStore

  ON ConTrxTransaction.StoreKey = RtlStore.StoreKey

  INNER JOIN PmtPaymentType

  ON ConTrxTransactionPayment.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

  INNER JOIN InvWarehouse

  ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

  LEFT OUTER JOIN PmtGiftCertificate

  ON ConTrxTransactionPayment.DetailKey = PmtGiftCertificate.GiftCertificateKey

  left outer join PmtCurrency on ConTrxTransactionPayment.CurrencyKey = PmtCurrency.CurrencyKey

WHERE ConTrxTransactionPayment.TransactionKey = @TransactionKey

AND  ConTrxTransactionPayment.IsVoided = 0

AND  TenderType IN (1,2,3,4,5,6,8,9,10,12,13, 14)

And  ConTrxTransaction.TransactionKey = @TransactionKey

If (Select Count(Distinct TENDER) From #Temp Where TENDER <> 'ON ACCOUNT') > 1

Begin

Update #Temp

Set  MoreThanOneTender = 'Y'

End

Update #Temp

Set  TotalRounding = (Select Sum(RoundingAmount) From #Temp),

  TotalAmount =

  ISNULL((Select

    Sum(case

    when TENDER = 'CUSTOM' AND IsIntegrated = 0

      then 0

    when TENDER <> 'ON ACCOUNT' AND TENDER <> 'CASH'

      then Amount

    when TENDER <> 'ON ACCOUNT' AND TENDER = 'CASH' AND (ChangeAmount > 0 and Amount = 0)

      then AMOUNTLESSCHANGE

    when TENDER <> 'ON ACCOUNT' AND TENDER = 'CASH' AND (ChangeAmount > 0 and ChangeAmount > Amount)

      then Amount

    when TENDER <> 'ON ACCOUNT' AND TENDER = 'CASH' AND (ChangeAmount > 0 and ChangeAmount < Amount)

      then AMOUNTLESSCHANGE

    when TENDER <> 'ON ACCOUNT' AND TENDER = 'CASH' AND (ChangeAmount = 0)

      then Amount

    else 0

    end)

From #Temp

Where TENDER <> 'ON ACCOUNT'),0)

--THE BELOW QUERY GETS THE TOTAL SUM FOR THE 'CASH' TENDER IRRESPECTIVE OF THE CURRENCY

--AND UPDATES THE COLUMNS 'Amount_CASH' , 'ChangeAmount_CASH' ,'AMOUNTLESSCHANGE_CASH' AND 'RoundingAmount_CASH'

--THESE COLUMNS ARE USED BY THE INTEGRATION SERVICE ( IN CASE OF 'CASH' TENDER ONLY ) WHEN THE 'Split Payment By Foreign Currency' IS OFF IN SAP

BEGIN---------------------------------------------------------------------------------------------------------------

DECLARE @Amount_CASH decimal(20,5),

  @ChangeAmount_CASH decimal(20,5),

  @AMOUNTLESSCHANGE_CASH decimal(20,5),

  @RoundingAmount_CASH decimal(20,5)

SELECT @Amount_CASH = SUM(ISNULL(AMOUNT, 0))

  ,@ChangeAmount_CASH = SUM(ISNULL(CHANGEAMOUNT,0))

  ,@AMOUNTLESSCHANGE_CASH = SUM(ISNULL(AMOUNT,0)) - SUM(ISNULL(CHANGEAMOUNT,0))

  ,@RoundingAmount_CASH = SUM(ISNULL(ROUNDINGAMOUNT,0))

FROM  ConTrxTransactionPayment

  ,ConTrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

WHERE ConTrxTransactionPayment.TransactionKey = @TransactionKey

AND  ConTrxTransactionPayment.TENDERTYPE IN(0, 7)

AND  ConTrxTransactionPayment.IsVoided = 0

And  ConTrxTransaction.TransactionKey = @TransactionKey

And  ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  ConTrxTransaction.StoreKey = RtlStore.StoreKey

And  ConTrxTransactionPayment.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

group BY ConTrxTransactionPayment.TransactionKey,

   ConTrxTransaction.TransactionId ,RtlStore.SiteID , ConTrxTransaction.CustomerKey

  ,CusCustomer.AccountingID , ConTrxTransaction.BusinessDate

  ,CardNumber ,ExpirationDate ,ZipCode ,PaymentReferenceNumber, PmtPaymentType.IsIntegrated

  ,ConTrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

UPDATE #Temp SET Amount_CASH = @Amount_CASH ,

     ChangeAmount_CASH = @ChangeAmount_CASH,

     AMOUNTLESSCHANGE_CASH = @AMOUNTLESSCHANGE_CASH,

     RoundingAmount_CASH = @RoundingAmount_CASH

END--------------------------------------------------------------------------------------------------------------------

Delete from #Temp where IsIntegrated = 0 AND TENDER = 'CUSTOM'

Select * From #Temp

END

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON