UFN_REVENUE_BUILDSITELIST

Returns a comma delimited list of sites associated with the given revenue.

Return

Return Type
nvarchar(1024)

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_REVENUE_BUILDSITELIST(
                @REVENUEID uniqueidentifier
            )
            returns nvarchar(1024)
            as
            begin
                declare @SITES nvarchar(1024) = (
                    select dbo.UDA_BUILDLIST(NAME)
                    from dbo.SITE
                    where ID in(
                        select REVENUESPLITSITE.SITEID
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVENUESPLITSITE
                        where FINANCIALTRANSACTION.ID = @REVENUEID
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    )
                );

                return @SITES;            
            end