UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY

Returns the reference number for a standing order, formatted for display.

Return

Return Type
nvarchar(18)

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY
            (
                @REVENUEID uniqueidentifier
            ) 
            returns nvarchar(18)
            as
            begin
                declare @FORMATTEDREFERENCENUMBER nvarchar(18);
                declare @USESYSTEMGENERATEDREFERENCENUMBER bit = 0;

                select
                    @FORMATTEDREFERENCENUMBER = case USESYSTEMGENERATEDREFERENCENUMBER when 0 then CUSTOMREFERENCENUMBER else cast(REFERENCENUMBER as nvarchar(18)) end,
                    @USESYSTEMGENERATEDREFERENCENUMBER = USESYSTEMGENERATEDREFERENCENUMBER
                from 
                    dbo.REVENUESTANDINGORDER 
                where 
                    ID = @REVENUEID;

                if @USESYSTEMGENERATEDREFERENCENUMBER = 1
                begin
                    declare @OFFSET int;
                    set @OFFSET = 1;
                    select top 1 @OFFSET = coalesce(OFFSET, 1)
                    from dbo.STANDINGORDERREFERENCENUMBEROFFSET;

                    select
                        @FORMATTEDREFERENCENUMBER = right('000000000000000000' + cast(@FORMATTEDREFERENCENUMBER + @OFFSET as nvarchar(18)), 18) 
                end

                return @FORMATTEDREFERENCENUMBER;
            end