USP_DATALIST_PROSPECT_HOUSEHOLDMEMBERPLANNEDGIFTS

                CREATE procedure dbo.USP_DATALIST_PROSPECT_HOUSEHOLDMEMBERPLANNEDGIFTS
                (
                    @PROSPECTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @CURRENCYCODE tinyint = 2
                )
                as begin
                    set nocount on;

                    declare @CURRENCYID uniqueidentifier;

                    declare @MULTICURRENCYENABLED bit;
                    set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
                    if @MULTICURRENCYENABLED = 0 
                        set @CURRENCYCODE = 1;

                    if @CURRENCYCODE = 1
                        set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    if @CURRENCYCODE = 3
                    begin
                        set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
                        if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                        begin     
                            set @CURRENCYCODE = 1
                        end
                    end;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());    

                    declare @DATALISTID uniqueidentifier;
                    set @DATALISTID = '41d3cf65-cce0-4aca-96aa-58df585213f6';

                    select
                        C.ID PROSPECTID,
                        NF_C.NAME PROSPECTNAME,
                        PG.ID,
                        PG.VEHICLE,
                        PG.GIFTDATE,
                        case @CURRENCYCODE
                          when 2 then PG.TRANSACTIONGIFTAMOUNT
                          when 1 then PG.ORGANIZATIONGIFTAMOUNT
                          when 3 then  dbo.UFN_PLANNEDGIFTDESIGNATION_AMOUNTINCURRENCY(PG.ID, @CURRENCYID)
                          else PG.GIFTAMOUNT
                        end as AMOUNT,
                        PG.STATUS,
                        PG.ISANONYMOUS,
                        PG.ISREVOCABLE,
                        case
                            when (select ISPRIMARY from dbo.GROUPMEMBER where GROUPID=@PROSPECTID and MEMBERID=PG.CONSTITUENTID) = 1 then '1'
                            else '2' + C.KEYNAME + ' ' + C.FIRSTNAME + ' ' + cast(C.ID as nvarchar(36))
                        end GROUPORPRIMARYSORT,
                        case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 else 
                            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, C.ID)
                        end as 'HASPERMISSIONS',
                        dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
                        case @CURRENCYCODE
                          when 2 then PG.TRANSACTIONCURRENCYID
                          when 0 then PG.BASECURRENCYID
                          else @CURRENCYID
                        end as DISPLAYCURRENCY             
                    from
                        dbo.GROUPMEMBER GM
                    inner join
                        dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
                    left outer join
                        dbo.CONSTITUENT C on C.ID = PG.CONSTITUENTID
                    left outer join 
                        dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
                    where
                        GM.GROUPID = @PROSPECTID
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE))
                    and 
                    (
                        select count(*) 
                        from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE 
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
                    ) > 0
                    and 
                    (
                        @SITEFILTERMODE = 0
                        or 
                            PG.ID in 
                            (
                                select PLANNEDGIFTSITE.PLANNEDGIFTID
                                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                inner join dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
                            )
                    )
                    order by
                        C.KEYNAME, C.FIRSTNAME, PG.GIFTDATE
                  end