USP_REVENUE_GETINSTALLMENTSTOPAY

Returns the split information for the first installment with non zero balance, and the next one.

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



      CREATE procedure [dbo].[USP_REVENUE_GETINSTALLMENTSTOPAY] 
                (
                    @REVENUEID uniqueidentifier
                )
                as 
                begin
          Declare @DesignationCount integer
          Declare @TopInstallmentRows as integer

          set @DesignationCount = (select COUNT(distinct ISplt.DESIGNATIONID) from 
              dbo.INSTALLMENT I
              INNER JOIN dbo.INSTALLMENTSPLIT ISplt
                  ON ISplt.INSTALLMENTID = I.ID
              where
                  I.REVENUEID = @REVENUEID)

          set @TopInstallmentRows = @DesignationCount * 2 --we want the next two complete installments


          select top (@TopInstallmentRows)
              I.SEQUENCE,
              I.TRANSACTIONAMOUNT,
              I.[DATE],
              coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(ISplt.ID),0) as 'SPLITBALANCE',
              coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(I.ID),0) as 'BALANCE',
              ISplt.DESIGNATIONID as 'DESIGNATIONID',
              D.VANITYNAME,
              I.ID as 'INSTALLMENTID',
              @DesignationCount as 'DESIGNATIONCOUNT'     
          from 
              dbo.INSTALLMENT I
              INNER JOIN dbo.INSTALLMENTSPLIT ISplt
                  ON ISplt.INSTALLMENTID = I.ID
              LEFT JOIN dbo.DESIGNATION D
                  ON D.ID = ISplt.DESIGNATIONID 
          where
              I.REVENUEID = @REVENUEID
              and
              coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(ISplt.ID),0) > 0
          order by
            I.SEQUENCE
        end