iVend Database Database : TrxHandleARPayments Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

VarChar

1

@pTransactionKey

In

Reference key of the Transaction

VarWChar

50

@pSiteId

In

Reference key of the Site

VarWChar

50

@pBatchKey

In

Reference key of the batch in which the transaction is getting saved, to maintain concurrency

VarWChar

50

@pTransactionStoreKey

In

Reference key of Store

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

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

TrxTransactionInstallmentDetail

Table

Defines the installments details of all those transactions which have a layaway plan attached to it.

1

TrxTransactionStatus

Table

Defines the transaction status with respect to various amounts that are affected by it.

1

--EXEC HandleARPayments 10000000000011, 'Y'

CREATE PROCEDURE [dbo].[TrxHandleARPayments]

(

@pDebug     CHAR(1),

@pTransactionKey NVARCHAR(50),

@pSiteId   NVARCHAR(50),

@pBatchKey   NVARCHAR(50),

@pTransactionStoreKey NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc VARCHAR(255),

  @Error INT,

  @transactionLayawayKey NVARCHAR(50),

  @paymentAmount  NUMERIC(20, 5),

  @balanceAmount  NUMERIC(20, 5),

  @installmentDetailKey NVARCHAR(50),

  @balance   NUMERIC(20, 5),

  @OnAccountPaymentAmount NUMERIC(20, 5)

/****** Checking AR Payments****/

If @pDebug = 'Y'

Select 'Update the paid amount of the Sales Order'

If EXISTS ( SELECT 1

  FROM TrxTransaction t, TrxARPayment s

  WHERE t.TransactionKey = s.TransactionKey

  AND  t.TransactionKey = @pTransactionKey

  AND  s.PaymentType = 1

    )

BEGIN

If @pDebug = 'Y'

  Select 'Updating the payments collected till date for sales order'

  UPDATE o

  SET SpecialOrderBalanceAmount = SpecialOrderBalanceAmount - ISNULL(b.Amount, 0),

   SpecialOrderPaidAmount = ISNULL(SpecialOrderPaidAmount, 0) + ISNULL(b.Amount, 0)

  FROM TrxTransactionStatus o ,

  (

  SELECT sum(Amount) 'Amount', p.DetailKey

  FROM TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 1

  AND  h.TransactionKey = @pTransactionKey

  GROUP BY p.DetailKey

  ) b

  WHERE b.DetailKey = o.TransactionKey

  ---for generating the replication entry for the transaction status of the earlier Sales Order-------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

     SourceKey, BatchKey,OperationType,Flag)

    SELECT NEWID(),

  178, TransactionStatusKey,

  @pBatchKey,

  1, 'FALSE'

  FROM TrxTransactionStatus o , TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 1

  AND  h.TransactionKey = @pTransactionKey

  AND  p.DetailKey = o.TransactionKey

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'Error while generating the replication entry for the transaction status update in case of AR payments against previous sales order.'

  GOTO ERRORHANDLER

  END

  --------------------------------------------------------------------------------------------------------------------

END

/****** Checking AR Payments****/

If @pDebug = 'Y'

Select 'Update the paid amount of the Sales transaction'

If EXISTS ( SELECT 1

  FROM TrxTransaction t, TrxARPayment s

  WHERE t.TransactionKey = s.TransactionKey

  AND  t.TransactionKey = @pTransactionKey

  AND  s.PaymentType = 2

    )

BEGIN

If @pDebug = 'Y'

  Select 'Updating the payments collected till date for SALES'

  update o

  set PaidAmount = ISNULL(PaidAmount, 0) + ISNULL(b.Amount, 0),

   BalanceAmount = BalanceAmount - + ISNULL(b.Amount, 0)

  from TrxTransactionStatus o ,

  (

  Select sum(Amount) 'Amount', p.DetailKey

  FROM TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 2

  AND  h.TransactionKey = @pTransactionKey

  group by p.DetailKey

  ) b

  where b.DetailKey = o.TransactionKey

  ---for generating the replication entry for the transcationstatus of the earlier transaction---------------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

     SourceKey, BatchKey,OperationType,Flag)

    SELECT NEWID() AS theRow,

  178, TransactionStatusKey,

  @pBatchKey,

  1, 'FALSE'

  FROM TrxTransactionStatus o , TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 2

  AND  h.TransactionKey = @pTransactionKey

  AND  p.DetailKey = o.TransactionKey

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'Error while generating the replication entry for the transaction status update in case of AR payments against previous sale.'

  GOTO ERRORHANDLER

  END

  --------------------------------------------------------------------------------------------------------------------

END

/****** Checking AR Payments in case the system is setup for the sale level layaway****/

IF EXISTS (SELECT 1 FROM TrxARPayment WHERE TransactionKey = @pTransactionKey AND PaymentType = 3)

BEGIN

  UPDATE T0

  SET    LayawayPaidAmount = ISNULL(LayawayPaidAmount, 0) + ISNULL(T1.Amount, 0),

     LayawayBalanceAmount = LayawayBalanceAmount - ISNULL(T1.Amount, 0)

  FROM TrxTransactionStatus T0 ,

  (

  Select sum(Amount) 'Amount', p.DetailKey

  FROM TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 3

  AND  h.TransactionKey = @pTransactionKey

  group by p.DetailKey

  ) T1

  where T0.TransactionKey = T1.DetailKey

  ---for generating the replication entry for the transcationstatus of the earlier transaction---------------------

  INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

     SourceKey, BatchKey,OperationType,Flag)

    SELECT NEWID(),

  178, TransactionStatusKey,

  @pBatchKey,

  1, 'FALSE'

  FROM TrxTransactionStatus o , TrxTransaction h, TrxARPayment p

  WHERE h.TransactionKey = p.TransactionKey

  AND  p.PaymentType   = 3

  AND  h.TransactionKey = @pTransactionKey

  AND  p.DetailKey   = o.TransactionKey

  IF(@Error <>0)

  BEGIN

  SET @ErrorDesc = 'Error while generating the replication entry for the transaction status update in case of AR payments against previous sale.'

  GOTO ERRORHANDLER

  END

  --------------------------------------------------------------------------------------------------------------------

END

------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM TrxARPayment WHERE TransactionKey = @pTransactionKey AND PaymentType = 3)

BEGIN

  DECLARE layAwayPayment CURSOR FOR

  SELECT DetailKey, Amount

  FROM TrxARPayment

  WHERE PaymentType = 3

  AND  TransactionKey = @pTransactionKey

  OPEN layAwayPayment

  FETCH NEXT FROM  layAwayPayment INTO @transactionLayawayKey, @paymentAmount

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @balanceAmount = ISNULL(@paymentAmount, 0)

  DECLARE layAwayInstallments CURSOR FOR

  SELECT InstallmentDetailKey, Balance

  FROM TrxTransactionInstallmentDetail

  WHERE SourceType = 1

  AND  SourceKey = @transactionLayawayKey

  AND  Balance > 0

  ORDER BY InstallmentNumber

  OPEN layAwayInstallments

  FETCH NEXT FROM  layAwayInstallments INTO @installmentDetailKey , @balance

  WHILE @@FETCH_STATUS = 0

  BEGIN

    UPDATE TrxTransactionInstallmentDetail

    SET PaidAmount = Case when Balance < @balanceAmount Then ISNULL(PaidAmount, 0) + ISNULL(Balance, 0) Else ISNULL(PaidAmount, 0) + ISNULL(@balanceAmount, 0) END

    , Balance = Case when Balance < @balanceAmount Then 0 Else Balance - @balanceAmount END

    WHERE InstallmentDetailKey = @installmentDetailKey

    ---for generating the replication entry for the transcationstatus of the earlier transaction---------------------

    INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType, Flag)

    Values(NEWID(), 171, @installmentDetailKey, @pBatchKey, 1, 'FALSE')

    IF(@Error <>0)

    BEGIN

    SET @ErrorDesc = 'Error while generating the replication entry for updating the installment status aginst the layaway AR payment.'

    GOTO ERRORHANDLER

    END

    IF @balanceAmount <= @balance

    SET @balanceAmount = 0

    ELSE

    SET @balanceAmount = ISNULL(@balanceAmount, 0) - ISNULL(@balance, 0)

    IF( ISNULL(@balanceAmount, 0) = 0) -- if the installment balance is more than the amount paid then

    BEGIN

      GOTO SETTLENEXTPAYMENT

    END

  FETCH NEXT FROM  layAwayInstallments INTO @installmentDetailKey , @balance

  END

SETTLENEXTPAYMENT:

  CLOSE layAwayInstallments

  DEALLOCATE layAwayInstallments

  FETCH NEXT FROM  layAwayPayment INTO @transactionLayawayKey, @paymentAmount

  END

  CLOSE layAwayPayment

  DEALLOCATE layAwayPayment

END

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN

END