iVend Database Database : TrxGetLaybyInstallmentPlan Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pLaybyPlanKey

In

Reference key of the Layaway Plan

VarWChar

50

@pLayawayTotal

In

Total amount for the Layaway Item

Numeric

9

@pDepositAmount

In

Deposit amount for the Layaway Item

Numeric

9

@pNumberOfInstallments

In

Number of Installments to be paid

Integer

4

@pInstallmentdate

In

Date when the Installment would Start or First Installment due date

DBTimeStamp

4

@pTransactionLayawayKey

In

Reference key of the Transaction Layaway

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

PmtLayawayPlan

Table

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

1

TrxTransactionInstallmentDetail

Table

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

1

--Procedure Name:uspGetLaybyInstallmentPlan

--Author : Abinesh Agarwal

--Created on: 12-Nov-2007

--Purpose:For Calculate the EMI's based on the passed vales

CREATE PROCEDURE [dbo].[TrxGetLaybyInstallmentPlan]

(

@pLaybyPlanKey   NVARCHAR(50) = 0,

@pLayawayTotal   NUMERIC(20,5) = NULL,

@pDepositAmount   NUMERIC(20,5) = NULL,

@pNumberOfInstallments INT = 0,

@pInstallmentdate DATETIME = NULL,

@pTransactionLayawayKey NVARCHAR(50) = '0'

)

AS

BEGIN

SET NOCOUNT ON

Declare @durationType int,

  @depositType int,

  @deposit Numeric(20, 5),

  --@InitialDepositAmount 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)

IF @pTransactionLayawayKey = '0'

BEGIN

  --Get the details of the installments

  Select @durationType = DurationType,

    @depositType = DepositType,

    @deposit = Deposit,

    @daysInstallmentDuration = InstallmentDuration

  From PmtLayawayPlan

  where LayawayPlanKey = @pLaybyPlanKey

  SET @balanaceAmount = @pLayawayTotal - @pDepositAmount --@InitialDepositAmount

  --Divide the Balanace amount in equal EMI's

  SET @lCount = 1

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

  CREATE TABLE #LayawayInstallmentDetails

  (  InitialDeposit nvarchar(50),

   InstallmentNo int,

   InstallmentDate datetime,

   InstallmentAmount decimal(20,4),

   PaidAmount decimal(20,4),

   BalanceAmount decimal(18,4)

  )

  INSERT INTO #LayawayInstallmentDetails

  SELECT 'Initial Deposit' ,0 'Installment No',dbo.GetCompanyDateTime() 'Date', Round(@pDepositAmount, 5) 'Amount',

  Round(@pDepositAmount, 5) 'Paid', 0 'Balance Due'

  Set @lastInstallmentDate = @pInstallmentdate

  WHILE @lCount <= @pNumberOfInstallments

  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(quarter, 1, @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 <> @pNumberOfInstallments

    BEGIN

    SET @InstallmentAmount = @lPerInstallmentAmount

    END

  Else

    BEGIN

    Select @InstallmentAmount = @pLayawayTotal - (ISNULL(Sum(InstallmentAmount),0) + @pDepositAmount)

    From #LayawayInstallmentDetails

    Where InstallmentNo > 0

    END

  INSERT INTO #LayawayInstallmentDetails(

    InitialDeposit, InstallmentNo, InstallmentDate,

    InstallmentAmount, PaidAmount, BalanceAmount)

          VALUES  ( @lCount, @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

  SELECT  InitialDeposit 'InitialDeposit',

    InstallmentNo 'InstallmentNo',

    InstallmentDate 'InstallmentDate',

    Round(InstallmentAmount,2) 'InstallmentAmount' ,

    Round(PaidAmount,2)'PaidAmount' ,

    Round(BalanceAmount,2) 'BalanceAmount'

  FROM  #LayawayInstallmentDetails

END

ELSE IF @pTransactionLayawayKey != '0' AND EXISTS(SELECT 1 FROM CfgEnterprise WHERE ApplyLayawayOnSale = 'FALSE')

BEGIN

  SELECT  InstallmentNumber 'InitialDeposit',

    InstallmentNumber 'InstallmentNo',

    InstallmentDate 'InstallmentDate',

    Round(Amount,2) 'InstallmentAmount' ,

    Round(PaidAmount,2)'PaidAmount' ,

    Round(Balance,2) 'BalanceAmount'

  FROM  TrxTransactionInstallmentDetail

  WHERE SourceKey = @pTransactionLayawayKey

  AND  SourceType = 0

END

ELSE IF @pTransactionLayawayKey != '0' AND EXISTS(SELECT 1 FROM CfgEnterprise WHERE ApplyLayawayOnSale = 'TRUE')

BEGIN

  SELECT  InstallmentNumber 'InitialDeposit',

    InstallmentNumber 'InstallmentNo',

    InstallmentDate 'InstallmentDate',

    Round(Amount,2) 'InstallmentAmount' ,

    Round(PaidAmount,2)'PaidAmount' ,

    Round(Balance,2) 'BalanceAmount'

  FROM  TrxTransactionInstallmentDetail

  WHERE SourceKey = @pTransactionLayawayKey

  AND  SourceType = 1

END

return(0)

ERRORHANDLER:

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

RETURN(1)

END