UFN_REVENUERECEIPT_REMOVEGAPSFROMSTARTINGNUMBER

Either returns the supplied starting receipt number or a new number that eliminates starting gaps

Return

Return Type
int

Parameters

Parameter	Parameter Type	Mode	Description
@STARTINGNUMBER	int	IN
@RECEIPTSTACKINFOID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_REVENUERECEIPT_REMOVEGAPSFROMSTARTINGNUMBER
(
@STARTINGNUMBER int = null,
@RECEIPTSTACKINFOID uniqueidentifier = null
)
returns int
with execute as caller
as begin
    declare @RESULT int = @STARTINGNUMBER

    if not exists 
    (select * from dbo.REVENUERECEIPT RR where RR.RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID and RR.RECEIPTNUMBER = @STARTINGNUMBER - 1)
        begin
            --select @RESULT = max(RECEIPTNUMBER)+1 from dbo.REVENUERECEIPT where RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID

            select @RESULT = min(RECEIPTNUMBER)+1 from dbo.REVENUERECEIPT rr
            where RECEIPTSTACKINFOID = @RECEIPTSTACKINFOID and RECEIPTNUMBER >= @RESULT
            and not exists (select * from dbo.REVENUERECEIPT where RECEIPTNUMBER=rr.RECEIPTNUMBER+1)
        end
    return isnull(@RESULT,1)
end