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

CfgEnterprise

Table

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

1

ConTrxTransactionMapping

Table

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtCurrency

Table

Define a list of all currecies 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

TrxARPayment

Table

Defines the details of the on account payments or any other settlements being done by the customer and the amount.

1

TrxTransaction

Table

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

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

--Based on the output of this procedure the same incoming or outgoing payments are posted in SAP Business One.

--This procedure returns only those payments that has been made against some previous transactions

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_ARPayment]

@TransactionKey nvarchar(50)

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON

DECLARE @AppliedAmount NUMERIC(20,5),

  @ApplyLayawayOnSale BIT

SELECT @ApplyLayawayOnSale = ApplyLayawayOnSale FROM CfgEnterprise

SET @AppliedAmount = 0

IF EXISTS (

  SELECT *

  From TrxTransaction A, TrxTransactionPayment B, PmtPaymentType C

  Where A.TransactionKey = @TransactionKey

  And  HasSurcharges   = 'FALSE'

  And  HasLayaways     = 'FALSE'

  And  HasLayawaysCancellations = 'FALSE'

  And  HasSales     = 'FALSE'

  And  HasSaleExchanges   = 'FALSE'

  And  HasDeliveries   = 'FALSE'

  And  HasRefunds     = 'FALSE'

  And  HasOrders     = 'FALSE'

  And  HasFulFillments   = 'FALSE'

  And  HasARPayments   = 'TRUE'

  And  HasQuotations   = 'FALSE'

  And  HasGiftCertificates   = 'FALSE'

  And  A.TransactionKey   = B.TransactionKey

  And  B.PaymentTypeKey   = C.PaymentTypeKey

  And  C.IsIntegrated   = 'FALSE'

  And  C.Type     = 10 --THIS INDICATES THAT THIS IS A CUSTOM PAYMENT

  )

BEGIN

  SELECT @AppliedAmount = SUM(Amount)

  From TrxTransaction A, TrxTransactionPayment B, PmtPaymentType C

  Where A.TransactionKey   = @TransactionKey

  And  HasSurcharges   = 'FALSE'

  And  HasLayaways     = 'FALSE'

  And  HasLayawaysCancellations = 'FALSE'

  And  HasSales     = 'FALSE'

  And  HasSaleExchanges   = 'FALSE'

  And  HasDeliveries   = 'FALSE'

  And  HasRefunds     = 'FALSE'

  And  HasOrders     = 'FALSE'

  And  HasFulFillments   = 'FALSE'

  And  HasARPayments   = 'TRUE'

  And  HasQuotations   = 'FALSE'

  And  HasGiftCertificates   = 'FALSE'

  And  A.TransactionKey   = B.TransactionKey

  And  B.PaymentTypeKey   = C.PaymentTypeKey

  And  C.IsIntegrated   = 'TRUE'

  And  C.Type     != 10 --THIS INDICATES THAT THIS IS A CUSTOM PAYMENT

END

SELECT CASE WHEN ISNULL(

    (

    SELECT IsConsolidated

    FROM ConTrxTransactionMapping

    Where TransactionKey = TrxARPayment.DetailKey

    ), '0') = '0'

  THEN

   DetailKey

  ELSE

   (

    SELECT ConsolidatedTransactionKey

    FROM ConTrxTransactionMapping

    Where TransactionKey = TrxARPayment.DetailKey

   )

  END

  As DetailKey

  ,CASE WHEN @AppliedAmount >0 THEN @AppliedAmount ELSE Amount END 'Amount', InvWarehouse.AccountingID, TrxARPayment.PaymentType

  ,(SELECT Id FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode

FROM TrxARPayment, TrxTransaction, RtlStore, InvWarehouse

WHERE TrxARPayment.DetailKey = TrxTransaction.TransactionKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

And  TrxARPayment.TransactionKey = @TransactionKey

AND  TrxARPayment.PaymentType in (2) --THIS IS FOR THE PAYMENT DONE AGAINST THE PREVIOUS SALE

UNION All

SELECT

  '-1' as DetailKey

  ,CASE WHEN @AppliedAmount >0 THEN @AppliedAmount ELSE Amount END 'Amount', InvWarehouse.AccountingID, TrxARPayment.PaymentType

  ,(SELECT Id FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode

FROM TrxARPayment, RtlStore, InvWarehouse, TrxTransaction

WHERE TrxARPayment.TransactionKey = TrxTransaction.TransactionKey

AND  TrxTransaction.StoreKey = RtlStore.StoreKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

And  TrxARPayment.TransactionKey = @TransactionKey

AND  TrxARPayment.PaymentType in (0, 1)

UNION All

SELECT

  DetailKey As DetailKey

  ,CASE WHEN @AppliedAmount >0 THEN @AppliedAmount ELSE Amount END 'Amount', InvWarehouse.AccountingID, TrxARPayment.PaymentType

  ,(SELECT Id FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode

FROM TrxARPayment, TrxTransaction, RtlStore, InvWarehouse, TrxTransactionLayAway

WHERE TrxARPayment.DetailKey = TrxTransactionLayAway.TransactionLayawayKey

And  TrxTransaction.TransactionKey = TrxTransactionLayAway.TransactionKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

And  TrxARPayment.TransactionKey = @TransactionKey

AND  TrxARPayment.PaymentType in (3)

AND @ApplyLayawayOnSale = 'FALSE'

UNION ALL

SELECT

  DetailKey As DetailKey

  ,CASE WHEN @AppliedAmount >0 THEN @AppliedAmount ELSE Amount END 'Amount', InvWarehouse.AccountingID, TrxARPayment.PaymentType

  ,(SELECT Id FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS TransactionCurrencyCode

FROM TrxARPayment, TrxTransaction, RtlStore, InvWarehouse

WHERE TrxARPayment.DetailKey = TrxTransaction.TransactionKey

And  TrxTransaction.StoreKey = RtlStore.StoreKey

And  RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

And  TrxARPayment.TransactionKey = @TransactionKey

AND  TrxARPayment.PaymentType in (3)

AND @ApplyLayawayOnSale = 'TRUE'

END

-- Drop stored procedure if it already exists

SET ANSI_NULLS ON