iVend Database Database : TrxGenerateLayawayInstallmentsForSaleLevel Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTransactionKey

In

Reference key of the Transaction

VarWChar

50

@pDebug

In

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

VarChar

1

@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

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

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

1

PmtLayawayPlan

Table

Defines the plan through which the items can be put on layaways to the customers.

1

RepReplicationTransaction

Table

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

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

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionStatus

Table

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

1

CREATE PROCEDURE [dbo].[TrxGenerateLayawayInstallmentsForSaleLevel]

(

@pTransactionKey NVARCHAR(50),

@pDebug     CHAR(1),

@pSiteId   NVARCHAR(50),

@pBatchKey   NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @NextInstallmentDetailKey VARCHAR(50),

  @layawayTotal NUMERIC(20,5),

  @surchargeTotal NUMERIC(20,5),

  @numberOfInstallments INT,

  @pInstallmentdate DATETIME,

  @durationType INT,

  @depositType INT,

  @deposit NUMERIC(20, 5),

  @balanaceAmount  NUMERIC(20, 5),

  @lastInstallmentDate DATETIME,

  @daysInstallmentDuration INT,

  @lCount   INT ,

  @lPerInstallmentAmount NUMERIC(20, 5),

  @lDateofInstallment DATETIME,

  @InstallmentAmount NUMERIC(20, 5),

  @lErrorDesc   NVARCHAR(255),

  @pDepositAmount  NUMERIC(20, 5),

  @Error   INT,

  @RecordsUpdated INT,

  @POSSiteId   INT

Select @POSSiteId = ISNULL(POSSiteId, 0) from CfgSiteInformation

--Get the details of the installments

Select @durationType = min(A.DurationType),

  @depositType = min(B.DepositType),

  @daysInstallmentDuration = min(A.InstallmentDuration),

  @numberOfInstallments = min(B.InstallmentCount),

  @pInstallmentdate = min(InstallmentStartDate)

From PmtLayawayPlan A, TrxTransactionLayaway B

Where A.LayawayPlanKey = B.LayawayPlanKey

AND B.TransactionKey = @pTransactionKey

GROUP BY B.TransactionKey

Select @layawayTotal = B.LayawayPayableAmount,

  @pDepositAmount = B.LayawayDepositAmount

From TrxTransaction A, TrxTransactionStatus B

Where A.TransactionKey = B.TransactionKey

AND A.TransactionKey = @pTransactionKey

SET @balanaceAmount = @layawayTotal - @pDepositAmount

--Divide the Balanace amount in equal EMI's

SET @lCount = 1

SET @lPerInstallmentAmount = @balanaceAmount/ case when @numberOfInstallments=0 then 1 else @numberOfInstallments end

CREATE TABLE #LayawayInstallmentDetails

(

 InstallmentNo int,

 InstallmentDate datetime,

 InstallmentAmount decimal(20, 5),

 PaidAmount decimal(20, 5),

 BalanceAmount decimal(20, 5)

)

INSERT INTO #LayawayInstallmentDetails

SELECT 0 'Installment No',dbo.GetCompanyDateTime() 'Date', @pDepositAmount 'Amount', @pDepositAmount 'Paid', 0 'Balance Due'

Set @lastInstallmentDate = @pInstallmentdate

WHILE @lCount <= @numberOfInstallments

BEGIN

  If @lCount = 1

  BEGIN

    SET @lDateofInstallment = @lastInstallmentDate

  END

  ELSE

  BEGIN

    IF @durationType = 0 ---weekly

    BEGIN

      SELECT @lDateofInstallment = DATEADD(Week, 1, @lastInstallmentDate)

    END

    ELSE IF @durationType = 1 ---monthly

    BEGIN

                SELECT @lDateofInstallment = DATEADD(MONTH, 1, @lastInstallmentDate)

            END

    ELSE IF @durationType = 2 ---Quaterly

            BEGIN

                SELECT @lDateofInstallment = DATEADD(quarter, 1, @lastInstallmentDate)

            END

    ELSE IF @durationType = 3 ---Half Yearly

            BEGIN

                SELECT @lDateofInstallment = DATEADD(MONTH, 6, @lastInstallmentDate)

            END

    ELSE IF @durationType = 4 ---Yearly

            BEGIN

                SELECT @lDateofInstallment = DATEADD(YEAR, 1, @lastInstallmentDate)

            END

    ELSE IF @durationType = 5 ---Duration

            BEGIN

                SELECT @lDateofInstallment = DATEADD(DAY, @daysInstallmentDuration, @lastInstallmentDate)

            END

  END

SET @lastInstallmentDate = @lDateofInstallment

If @lCount <> @numberOfInstallments

  BEGIN

  SET @InstallmentAmount = @lPerInstallmentAmount

  END

Else

  BEGIN

  Select @InstallmentAmount = @layawayTotal - (ISNULL(SUM(InstallmentAmount),0) + @pDepositAmount)

  From #LayawayInstallmentDetails

  Where InstallmentNo > 0

  END

INSERT INTO #LayawayInstallmentDetails(

  InstallmentNo, InstallmentDate,

  InstallmentAmount, PaidAmount, BalanceAmount)

        VALUES  ( @lCount, @lDateofInstallment,

  ROUND(@InstallmentAmount,2), 0, ROUND(@InstallmentAmount ,2) )

IF(@@ERROR <>0)

BEGIN

  SET @lErrorDesc = 'Error when inserting into table #LayawayInstallmentDetails.'

  GOTO ERRORHANDLER

END

        SET @lCount = @lCount +1

    END

--Bulk Insert Into TrxTransactionInstallmentDetail table

Insert into TrxTransactionInstallmentDetail

  (

   InstallmentDetailKey,

   TransactionKey,

   SourceType,

   SourceKey,

   InstallmentNumber,

   InstallmentDate,

   Amount,

   PaidAmount,

   PaidDate,

   Balance,

   Status

  )

SELECT NEWID(),

  @pTransactionKey,

  1, --INDICATES THAT THE INSTALLMENTS IS CREATED ON THE BASIS OF THE SALE AND NOT THE INDIVIDUAL LAYAWAY ITEMS.

  @pTransactionKey,

  InstallmentNo, --Installment Number

  InstallmentDate, -- Dateof Installment

  InstallmentAmount, --Installment Amount

  PaidAmount, --Amount that is paid

  Null, --amount is not paid

  BalanceAmount, --stores the balance amount

  0 --Status 0 indicates it is not cancelled

FROM  #LayawayInstallmentDetails

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

     SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 171, InstallmentDetailKey, @pBatchKey, 0, 'FALSE'

FROM TrxTransactionInstallmentDetail A

WHERE TransactionKey = @pTransactionKey

return(0)

ERRORHANDLER:

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

RETURN(1)

END