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

CfgEnterprise

Table

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

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

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

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

TrxTransactionStatus

Table

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

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdatePaymentDiscount]

(

@pDebug     CHAR(1),

@pTransactionKey nvarchar(50),

@pSiteId   nvarchar(50),

@pBatchKey   nvarchar(50),

@pTransactionStoreKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255),

  @modifiedBy nvarchar(50),

  @modified DateTime,

  @lDiscountAmount DECIMAL(20, 5),

  @lSaleAmount DECIMAL(20, 5),

  @lARPaymentAmount DECIMAL(20, 5),

  @lNetPayableAmount DECIMAL(20, 5),

  @lDetailKey nvarchar(50),

  @lCurrentARPaymentAmount DECIMAL(20, 5),

  @lProportionateAmount DECIMAL(20, 5),

  @lSpecialOrderAmount DECIMAL(20, 5),

  @lPaymentType int

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

  --IF NOT EXISTS (SELECT * FROM TrxTransactionPayment Where DiscountAmount > 0)

SELECT @lDiscountAmount = SUM(DiscountAmount)

FROM TrxTransactionPayment

Where DiscountAmount > 0

and TransactionKey = @pTransactionKey

Select

  @lSaleAmount =SaleAmount

From TrxTransactionStatus With (nolock)

Where TransactionKey = @pTransactionKey

SELECT @lARPaymentAmount = ISNULL(SUM(Amount),0)

From TrxARPayment With (nolock)

Where TransactionKey = @pTransactionKey

SELECT @lNetPayableAmount = PayableAmount

From TrxTransaction With (nolock)

Where TransactionKey = @pTransactionKey

--THIS WILL PROPORTINATE THE DISCOUNT AMOUNT ON THE LINE

IF (@lSaleAmount - ISNULL(@lARPaymentAmount,0)) <> 0

BEGIN

  UPDATE TrxTransactionSaleItem

  SET PaymentDiscount = @lDiscountAmount * TotalPostSaleDiscount / (@lSaleAmount - ISNULL(@lARPaymentAmount,0))

  WHERE TransactionKey = @pTransactionKey

  AND Type = 0

END

  DECLARE ProductListCursor CURSOR FOR

  SELECT DetailKey, Amount, PaymentType

  FROM TrxARPayment

  Where TransactionKey = @pTransactionKey

  AND  PaymentType IN (1,2) --ORDER, SALE

  OPEN ProductListCursor

  FETCH NEXT FROM  ProductListCursor INTO @lDetailKey, @lCurrentARPaymentAmount, @lPaymentType

  WHILE @@FETCH_STATUS=0

  BEGIN

    SET @lProportionateAmount = @lCurrentARPaymentAmount * @lDiscountAmount/ @lNetPayableAmount

    IF @lPaymentType = 1

    BEGIN

      SELECT @lSpecialOrderAmount = SUM(TotalPostSaleDiscount)

      From TrxTransactionOrder With (nolock)

      WHERE TransactionKey = @lDetailKey

      UPDATE TrxTransactionOrder

      SET PaymentDiscount = ISNULL(PaymentDiscount,0) + (@lProportionateAmount * TotalPostSaleDiscount / @lSpecialOrderAmount)

      WHERE TransactionKey = @lDetailKey

      --UPDATE THE DISCOUNT AMOUNT ON THE DELIVERY RECORDS OF THE SPECIAL ORDER

      UPDATE TrxTransactionSaleItem

      SET PaymentDiscount = ISNULL(PaymentDiscount,0) + (@lProportionateAmount * TotalPostSaleDiscount / @lSpecialOrderAmount)

      WHERE Type = 3

      AND OriginalDetailKey = @lDetailKey

    END

    IF @lPaymentType = 2

    BEGIN

      SELECT @lSpecialOrderAmount = SUM(TotalPostSaleDiscount)

      From TrxTransactionSaleItem

      WHERE TransactionKey = @lDetailKey

      UPDATE TrxTransactionSaleItem

      SET PaymentDiscount = ISNULL(PaymentDiscount,0) + (@lProportionateAmount * TotalPostSaleDiscount / @lSpecialOrderAmount)

      WHERE TransactionKey = @lDetailKey

    END

    FETCH NEXT FROM ProductListCursor INTO @lDetailKey, @lCurrentARPaymentAmount,@lPaymentType

  END

  CLOSE ProductListCursor

  DEALLOCATE ProductListCursor

RETURN

ERRORHANDLER:

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

RETURN

END