UFN_PDACCOUNT_GETCONSTITUENCY

CREATE function [dbo].[UFN_PDACCOUNT_GETCONSTITUENCY]
(@REVENUESPLITID as uniqueidentifier,
 @REVENUETRANSACTIONTYPECODE as tinyint,
 @APPLICATIONCODE tinyint,
 @PAYMENTMETHODCODE as tinyint,
 @DESIGNATIONID as uniqueidentifier) returns uniqueidentifier
 As 
 begin
     declare @CONSTITID as uniqueidentifier
    declare @RESULT as uniqueidentifier

    select @CONSTITID = T3.CONSTITUENTID from REVENUE T3
    inner join REVENUESPLIT T4 on T4.REVENUEID = T3.ID
    Where T4.ID = @REVENUESPLITID;

  select top 1 @RESULT = V1.ID
  from
  (select distinct isnull(t2.ID, t3.ID) as ID, t1.DESCRIPTION
  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
  order by isnull(t4.SEQUENCE,99999), V1.DESCRIPTION

    return @RESULT
end