UFN_ASSIGNPLANNEDGIFTLETTERCODESPROCESS_GETLETTERCODESANDSELECTIONS_SITES

            create function dbo.UFN_ASSIGNPLANNEDGIFTLETTERCODESPROCESS_GETLETTERCODESANDSELECTIONS_SITES
            (
                @ASSIGNPLANNEDGIFTLETTERCODESPROCESSID uniqueidentifier = null,
                @OWNERID uniqueidentifier = null
            )
            returns @RESULT table
            (
                PLANNEDGIFTLETTERCODEID uniqueidentifier,
                IDSETREGISTERID uniqueidentifier
            )
            as
            begin

                declare @BUSINESSPROCESSCATALOGID uniqueidentifier = '37910A93-C849-4B83-8664-BB22BD12EAFC';

                if @OWNERID is null and @ASSIGNPLANNEDGIFTLETTERCODESPROCESSID is not null
                    set @OWNERID = (select OWNERID from dbo.BUSINESSPROCESSINSTANCE where BUSINESSPROCESSPARAMETERSETID = @ASSIGNPLANNEDGIFTLETTERCODESPROCESSID);

                declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);

                declare @SITESFORUSER table (SITEID uniqueidentifier);
                -- Performance: don't bother filling sites table if user is admin

                if @ISSYSADMIN = 0
                    insert into @SITESFORUSER
                    select SITEID from dbo.UFN_SITESFORUSERONFEATURE(@OWNERID, @BUSINESSPROCESSCATALOGID, 4);

                if @ASSIGNPLANNEDGIFTLETTERCODESPROCESSID is null
                begin

                    insert into @RESULT(PLANNEDGIFTLETTERCODEID,IDSETREGISTERID)
                    select 
                        PLANNEDGIFTLETTERCODE.ID,
                        IDSETREGISTER.ID
                    from
                        dbo.PLANNEDGIFTLETTERCODE
                    inner join
                        dbo.IDSETREGISTER on PLANNEDGIFTLETTERCODE.IDSETREGISTERID = IDSETREGISTER.ID
                    where
                        ISACTIVE = 1
                        and
                        (
                            @ISSYSADMIN = 1
                            or

                            -- Letter Security

                            exists 
                            (
                                select 1
                                from dbo.[UFN_SITEID_MAPFROM_PLANNEDGIFTLETTERCODEID]([PLANNEDGIFTLETTERCODE].[ID]) [PLANNEDGIFTLETTERCODESITES]
                                where 
                                    PLANNEDGIFTLETTERCODESITES.SITEID is null
                                    or
                                    exists
                                    (
                                        select 1 
                                        from @SITESFORUSER as [SFU]
                                        where 
                                            SFU.SITEID = [PLANNEDGIFTLETTERCODESITES].[SITEID]
                                    )
                            )

                            -- Selection Security

                            and (
                                IDSETREGISTER.SITEID is null 
                                or
                                exists 
                                (
                                    select 1 
                                    from @SITESFORUSER as [SFU]
                                    where 
                                        SFU.SITEID = [IDSETREGISTER].[SITEID]
                                )
                            )
                        );
                end
                else
                begin

                    insert into @RESULT(PLANNEDGIFTLETTERCODEID,IDSETREGISTERID)
                    select
                        ASSIGNPLANNEDGIFTLETTERCODESPROCESSLETTERCODES.PLANNEDGIFTLETTERCODEID,
                        IDSETREGISTER.ID
                    from
                        dbo.ASSIGNPLANNEDGIFTLETTERCODESPROCESSLETTERCODES
                    inner join
                        dbo.PLANNEDGIFTLETTERCODE on ASSIGNPLANNEDGIFTLETTERCODESPROCESSLETTERCODES.PLANNEDGIFTLETTERCODEID = PLANNEDGIFTLETTERCODE.ID
                    inner join
                        dbo.IDSETREGISTER on PLANNEDGIFTLETTERCODE.IDSETREGISTERID = IDSETREGISTER.ID
                    where
                        ASSIGNPLANNEDGIFTLETTERCODESPROCESSID = @ASSIGNPLANNEDGIFTLETTERCODESPROCESSID
                        and PLANNEDGIFTLETTERCODE.ISACTIVE = 1
                        and 
                        (
                            @ISSYSADMIN = 1
                            or

                            -- Letter Security

                            exists (
                                select 1
                                from dbo.[UFN_SITEID_MAPFROM_PLANNEDGIFTLETTERCODEID]([PLANNEDGIFTLETTERCODE].[ID]) [PLANNEDGIFTLETTERCODESITES]
                                where 
                                    PLANNEDGIFTLETTERCODESITES.SITEID is null
                                    or
                                    exists
                                    (
                                        select 1 
                                        from @SITESFORUSER as [SFU]
                                        where 
                                            SFU.SITEID=[PLANNEDGIFTLETTERCODESITES].[SITEID]
                                    )
                            )

                            -- Selection Security

                            and (
                                IDSETREGISTER.SITEID is null 
                                or
                                exists 
                                (
                                    select 1 
                                    from @SITESFORUSER as [SFU]
                                    where 
                                        SFU.SITEID=[IDSETREGISTER].[SITEID]
                                )
                            )
                        );
                    end

                return;

            end