USP_SIMPLEDATALIST_SELECTIONGROUPNAME

CREATE procedure dbo.USP_SIMPLEDATALIST_SELECTIONGROUPNAME 
(
    @RECORDTYPEID uniqueidentifier,
    @COMMUNICATIONTYPECODE tinyint = 0
)
as

    -- @COMMUNICATIONTYPECODE

    -- All              = 0

    -- AppealMailing    = 1

    -- EventInvitation  = 2

    -- Acknowledgement  = 3

    -- Reminder         = 4


    -- Temp Table for TypeCode filtering

    declare @T table
    (
        ID uniqueidentifier,
        GROUPNAME nvarchar(200)
    )

    insert into @T
    select 
        I.ID,
        I.GROUPNAME
    from dbo.IDSETREGISTER I
        left join dbo.SQLFUNCTIONCATALOG S on S.FUNCTIONNAME = I.DBOBJECTNAME -- Filter by installed products

    where
        I.RECORDTYPEID = @RECORDTYPEID and 
        I.GROUPNAME is not null and I.GROUPNAME <> '' and
        I.ACTIVE = 1 and
        (
            (S.ID is null)
            or dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
                S.SQLFUNCTIONSPECXML.query (
                    'declare namespace common="bb_appfx_commontypes";
                    /*/common:InstalledProductList'
                )
            ) = 1
        );

    if @COMMUNICATIONTYPECODE = 3 
        begin
            delete from @T
            where ID in (
                'FA233787-5AC0-4BD9-AB7F-4B3179642987', -- UFN_SELECTION_COMMUNICATION_ALLRECURRINGGIFTS

                'FF4AADC4-7EB8-4578-B82F-E84B6D4F4710'  -- UFN_SELECTION_COMMUNICATIONS_ALLPLEDGES

            );
        end

    if @COMMUNICATIONTYPECODE = 4
        begin
            delete from @T
            where ID in (
                'BB980BBA-EB2A-41D5-9B19-2337279E8B7E', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_GREATER_THAN_1000

                '6ACE7DDD-1510-4C8F-9D2A-964BE965AFF2', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_BEWTEEN_750_1000

                '4F776DF4-409B-4EF4-9983-3A0020079780', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_BEWTEEN_500_750

                '22FC164D-0B98-4ADE-BC99-A93C5C315451', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_BEWTEEN_250_500

                '0ACE796F-8543-4070-AC08-1BFF7156B645', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_BEWTEEN_100_250

                'CEC11E2B-5B3B-4638-99E4-C9B83AFAC3AA', -- UFN_ALL_UNACKNOWLEDGED_DONATIONS_BEWTEEN_0_100

                '8149F2B5-BA2C-4481-94A5-E5EFEFBA922F', -- UFN_ALL_UNACKNOWLEDGED_MEMBERSHIPS

                'CAA315CA-0087-43A7-B967-29561756189D', -- UFN_ALL_UNACKNOWLEDGED_REGISTRATIONS

                '178B46A8-3AD3-4D69-92A8-0CCC384F8D78'  -- UFN_ALL_UNACKNOWLEDGED_DONATIONS

            );
        end

    select distinct
        NULL as VALUE,
        GROUPNAME as LABEL
    from @T