UFN_PDACCOUNT_GETCONSTITUENCY_3

            CREATE function [dbo].[UFN_PDACCOUNT_GETCONSTITUENCY_3]
            (
                @REVENUEID uniqueidentifier, 
                @INFORMATIONSOURCECODE tinyint, -- 0 revenue tables, 1 revenue batch tables

                @REVENUEINFORMATION xml
            )
            returns uniqueidentifier
            As 
            begin
                declare @CONSTITID as uniqueidentifier;
                declare @RESULT as uniqueidentifier;
                declare @DATE UDT_FUZZYDATE = convert(nchar(8), GETDATE(), 112);

                if @INFORMATIONSOURCECODE = 0
                    select @CONSTITID = FINANCIALTRANSACTION.CONSTITUENTID
                    from dbo.FINANCIALTRANSACTION
                    where ID = @REVENUEID
                else
                    select top 1 @CONSTITID = REVENUEINFORMATION.CONSTITUENTID 
                    from dbo.UFN_REVENUEINFORMATION_FROMITEMLISTXML(@REVENUEINFORMATION) as REVENUEINFORMATION;

                select top 1 @RESULT = V1.ID
                from (select distinct isnull(t2.ID, t3.ID) as ID, 
                                        t1.DESCRIPTION, t1.DATEFROM, t1.DATETO
                        from dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(@CONSTITID) t1 
                        left join dbo.CONSTITUENCYCODE t2 
                            on t1.DESCRIPTION = t2.DESCRIPTION and t1.CONSTITUENCYTYPECODE = 99
                        left join dbo.CONSTITUENCYSYSTEMNAME t3 
                            on t1.DESCRIPTION = T3.DESCRIPTION and t1.CONSTITUENCYTYPECODE != 99) V1
                left join dbo.PDCONSTITUENCYORDER t4 
                    on V1.ID = t4.CONSTITUENCYCODEID
                where
                    (coalesce(V1.DATEFROM,'00000000') = '00000000' or V1.DATEFROM <= @DATE)
                    and (coalesce(V1.DATETO,'00000000') = '00000000' or V1.DATETO >= @DATE)
                order by isnull(t4.SEQUENCE,99999), V1.DESCRIPTION;

                return @RESULT
            end