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

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

TrxTransaction

Table

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

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

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

@TransactionKey NVARCHAR(50)

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON

Create Table #temp

 (

  TransactionKey NVARCHAR(50)

  ,TransactionPaymentKey NVARCHAR(50)

  ,TransactionId NVARCHAR(100)

  ,U_SITEID INT

  ,CustomerKey NVARCHAR(50)

  ,CustomerCode NVARCHAR(100)

  ,BusinessDate NVARCHAR(8)

  , AccountNumber NVARCHAR(100)

  , ExpirationDate NVARCHAR(8)

  ,ZipCode NVARCHAR(100)

  ,AccountType NVARCHAR(100)

  ,PaymentReferenceNumber NVARCHAR(100)

  ,Amount DECIMAL(20,5)

  ,ChangeAmount DECIMAL(20,5)

  ,AMOUNTLESSCHANGE DECIMAL(20,5)

  ,Amount_FC DECIMAL(20,5)

  ,AMOUNTLESSCHANGE_FC 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)

  --End Dicount

  ,MoreThanOneTender VARCHAR(100)

  ,PaymentTypeKey NVARCHAR(50)

  ,IsIntegrated BIT

  ,GiftCertificateKey NVARCHAR(50)

  ,Warehouse NVARCHAR(100)

  ,GiftCertificateProductKey NVARCHAR(50)

  ,BankCode NVARCHAR(100)

  ,ValidationCode NVARCHAR(100)

  ,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 TrxTransactionPayment.TransactionKey As TransactionKey

  ,MIN(TrxTransactionPayment.TransactionPaymentKey) As TransactionPaymentKey

  ,TrxTransaction.TransactionId   As TransactionId

  ,RtlStore.SiteID       AS U_SITEID

  ,TrxTransaction.CustomerKey     As CustomerKey

  ,CusCustomer.AccountingID     As CustomerCode

  ,CONVERT(VARCHAR, TrxTransaction.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(ForeignCurrencyAmount, 0)) As Amount_FC

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

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

  ,'CASH'         AS Tender

  ,0       As TotalAmount

  ,0       As TotalRounding

  --Dicount

  ,SUM(ISNULL(TrxTransactionPayment.DiscountAmount, 0) ) As DiscountAmount

  ,SUM(ISNULL(TrxTransactionPayment.NetPayableAmount, 0) ) As NetPayableAmount

  --End Dicount

  ,'N'         As MoreThanOneTender

  ,MIN(TrxTransactionPayment.PaymentTypeKey) As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated   As IsIntegrated

  ,TrxTransactionPayment.DetailKey   As GiftCertificateKey

  ,InvWarehouse.AccountingID     As Warehouse

  ,'-1'           As GiftCertificateProductKey

  ,''           As BankCode

  ,TrxTransactionPayment.ValidationCode As ValidationCode

  , 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 = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode --NEW

FROM TrxTransactionPayment

  ,TrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

  , PmtCurrency

WHERE TRXTRANSACTIONPAYMENT.TRANSACTIONKEY= @TransactionKey

AND  TRXTRANSACTIONPAYMENT.TENDERTYPE IN(0)

AND  TRXTRANSACTIONPAYMENT.IsVoided = 0

And  TrxTransaction.TRANSACTIONKEY= @TransactionKey

And  TrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  TRXTRANSACTIONPAYMENT.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

AND  TrxTransactionPayment.CurrencyKey = PmtCurrency.CurrencyKey

group BY TrxTransactionPayment.TransactionKey, TrxTransaction.TransactionId ,RtlStore.SiteID ,TrxTransaction.CustomerKey

  ,CusCustomer.AccountingID ,TrxTransaction.BusinessDate

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

  ,TrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

  ,TrxTransactionPayment.ValidationCode

  ,PmtCurrency.Id

  ,InvWarehouse.BranchCode

  ,TrxTransaction.CurrencyKey

  , TrxTransactionPayment.CheckDueDate

UNION

SELECT TrxTransactionPayment.TransactionKey As TransactionKey

  ,MIN(TrxTransactionPayment.TransactionPaymentKey) As TransactionPaymentKey

  ,TrxTransaction.TransactionId   As TransactionId

  ,RtlStore.SiteID       AS U_SITEID

  ,TrxTransaction.CustomerKey     As CustomerKey

  ,CusCustomer.AccountingID     As CustomerCode

  ,CONVERT(VARCHAR, TrxTransaction.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

  --Test

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

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

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

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

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

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

  ,'CASH'         AS Tender

  ,0     As TotalAmount

  ,0     As TotalRounding

  --Dicount

  ,SUM(ISNULL(TrxTransactionPayment.DiscountAmount, 0) ) As DiscountAmount

  ,SUM(ISNULL(TrxTransactionPayment.NetPayableAmount, 0) ) As NetPayableAmount

  --End Dicount

  ,'N'         As MoreThanOneTender

  ,MIN(TrxTransactionPayment.PaymentTypeKey) As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated   As IsIntegrated

  ,TrxTransactionPayment.DetailKey   As GiftCertificateKey

  ,InvWarehouse.AccountingID     As Warehouse

  ,'-1'           As GiftCertificateProductKey

  ,''           As BankCode

  ,TrxTransactionPayment.ValidationCode As ValidationCode

  , 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 = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode --NEW

FROM TrxTransactionPayment

  ,TrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

  , PmtCurrency

WHERE TRXTRANSACTIONPAYMENT.TRANSACTIONKEY= @TransactionKey

AND  TRXTRANSACTIONPAYMENT.TENDERTYPE IN(7)

AND  TRXTRANSACTIONPAYMENT.IsVoided = 0

And  TrxTransaction.TRANSACTIONKEY= @TransactionKey

And  TrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  TRXTRANSACTIONPAYMENT.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

AND  TrxTransactionPayment.CurrencyKey = PmtCurrency.CurrencyKey

group BY TrxTransactionPayment.TransactionKey, TrxTransaction.TransactionId ,RtlStore.SiteID ,TrxTransaction.CustomerKey

  ,CusCustomer.AccountingID ,TrxTransaction.BusinessDate

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

  ,TrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

  ,TrxTransactionPayment.ValidationCode

  ,PmtCurrency.Id

  ,InvWarehouse.BranchCode

  ,TrxTransaction.CurrencyKey

  ,TrxTransactionPayment.CheckDueDate

UNION

SELECT TrxTransactionPayment.TransactionKey   As TransactionKey

  ,TrxTransactionPayment.TransactionPaymentKey As TransactionPaymentKey

  ,TrxTransaction.TransactionId     As TransactionId

  ,RtlStore.SiteID       As U_SITEID

  ,TrxTransaction.CustomerKey     As CustomerKey

  ,CusCustomer.AccountingID     As CustomerCode

  ,Convert(varchar, TrxTransaction.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

  --,0  As Amount_FC

  --,0      AS AMOUNTLESSCHANGE_FC

  ,ISNULL(ForeignCurrencyAmount, 0)   As Amount_FC

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

  ,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

  --Dicount

  ,ISNULL(TrxTransactionPayment.DiscountAmount, 0)   As DiscountAmount

  ,ISNULL(TrxTransactionPayment.NetPayableAmount, 0) As NetPayableAmount

  --End Dicount

  ,'N'           As MoreThanOneTender

  ,TrxTransactionPayment.PaymentTypeKey   As PaymentTypeKey

  ,PmtPaymentType.IsIntegrated     As IsIntegrated

  ,TrxTransactionPayment.DetailKey   As GiftCertificateKey

  ,InvWarehouse.AccountingID     As Warehouse

  ,pmtGiftCertificate.ProductKey     As GiftCertificateProductKey

  ,ISNULL(CfgBank.Id,'')       As BankCode

  ,TrxTransactionPayment.ValidationCode As ValidationCode

  , 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

  ,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 = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode --NEW

From TrxTransactionPayment Inner Join TrxTransaction On TrxTransactionPayment.TransactionKey = TrxTransaction.TransactionKey

        Inner Join CusCustomer On TrxTransaction.CustomerKey = CusCustomer.CustomerKey

        Inner Join RtlStore On TrxTransaction.StoreKey = RtlStore.StoreKey

        Inner Join PmtPaymentType On TrxTransactionPayment.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

        Inner Join InvWarehouse On RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

        Left Outer Join pmtGiftCertificate On TrxTransactionPayment.DetailKey = pmtGiftCertificate.GiftCertificateKey

        Left Outer Join CfgBank On TrxTransactionPayment.BankKey = CfgBank.BankKey

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

Where TrxTransactionPayment.TransactionKey= @TransactionKey

AND  TrxTransactionPayment.IsVoided = 0

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

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 =

   (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')

--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 TrxTransactionPayment

  ,TrxTransaction

  ,CusCustomer

  ,RtlStore

  ,PmtPaymentType

  ,InvWarehouse

WHERE TRXTRANSACTIONPAYMENT.TRANSACTIONKEY= @TransactionKey

AND  TRXTRANSACTIONPAYMENT.TENDERTYPE IN(0, 7)

AND  TRXTRANSACTIONPAYMENT.IsVoided = 0

And  TrxTransaction.TRANSACTIONKEY= @TransactionKey

And  TrxTransaction.CustomerKey = CusCustomer.CustomerKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  TRXTRANSACTIONPAYMENT.PaymentTypeKey = PmtPaymentType.PaymentTypeKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

group BY TrxTransactionPayment.TransactionKey, TrxTransaction.TransactionId ,RtlStore.SiteID ,TrxTransaction.CustomerKey

  ,CusCustomer.AccountingID ,TrxTransaction.BusinessDate

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

  ,TrxTransactionPayment.DetailKey

  ,InvWarehouse.AccountingID

  ,TrxTransactionPayment.ValidationCode

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