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

@pTransactionLayawayKey

In

Reference key of the Layaway 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

2

TrxInventoryUpdateLaybyItem

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

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

TrxTransactionSurcharge

Table

Provides a reference of all the surcharges & other details attached with any transaction.

1

CREATE PROCEDURE [dbo].[TrxGenerateLayawayInstallments]

(

@pTransactionKey NVARCHAR(50),

@pTransactionLayawayKey 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 = A.DurationType,

  @depositType = B.DepositType,

  @deposit = B.Deposit,

  @daysInstallmentDuration = A.InstallmentDuration,

  @layawayTotal = B.TotalPostSaleDiscount,

  @numberOfInstallments = B.InstallmentCount,

  @pInstallmentdate = InstallmentStartDate,

  @pDepositAmount = DepositAmount

From PmtLayawayPlan A, TrxTransactionLayaway B

Where A.LayawayPlanKey = B.LayawayPlanKey

AND TransactionLayawayKey = @pTransactionLayawayKey

Select @surchargeTotal = ISNULL(Sum(Total),0)

From TrxTransactionSurcharge

Where SourceKey = @pTransactionLayawayKey

And  SourceType = 2 --INDICATES THE SURCHARGE IS FOR THE LAYAWAY ITEM

SET @layawayTotal = IsNull(@layawayTotal, 0) + IsNull(@surchargeTotal, 0)

SET @balanaceAmount = @layawayTotal - @pDepositAmount--@InitialDepositAmount

--Divide the Balanace amount in equal EMI's

SET @lCount = 1

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

--select @balanaceAmount

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,

  0,

  @pTransactionLayawayKey,

  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

SELECT @RecordsUpdated = @@RowCount, @Error = @@ERROR

  IF(@Error <>0)

BEGIN

  SET @lErrorDesc = 'Error when inserting into table TrxTransactionInstallmentDetail.'

  GOTO ERRORHANDLER

END

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

     SourceKey, BatchKey,OperationType,Flag)

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

FROM TrxTransactionInstallmentDetail A

WHERE TransactionKey = @pTransactionKey

AND  SourceKey = @pTransactionLayawayKey

return(0)

ERRORHANDLER:

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

RETURN(1)

END