spGetApplicationFieldsByType

CREATE procedure [dbo].[spGetApplicationFieldsByType] (
                @ClientsID int,
                @FieldType int
            )
            AS
            BEGIN
                with FIELDS_CTE as (

                    SELECT 
                        f.CountryMask,
                        c.FieldName "Category",
                         f.FieldID, 
                         f.AttribTypeID, 
                         f.FieldName, 
                         f.PrivateShowByDefault, 
                         c.Sequence as 'ParentSequence', 
                         f.Sequence as 'FieldSequence'
                    FROM dbo.ApplicationFields f 
                    INNER JOIN dbo.Clients ON 1 = 1
                    LEFT OUTER JOIN dbo.ApplicationFields c ON f.ParentFieldID = c.FieldID
                    WHERE 
                        f.CountryMask & Clients.RECountryMask = Clients.RECountryMask
                        AND Clients.ID = @ClientsID
                        AND f.FieldType = @FieldType
                        AND f.ParentFieldID IS NOT NULL
                        AND c.HasMultiples = 0
                        AND f.ParentFieldID <> 134 and c.FieldID <> 134 -- exclude Notes fields

                        AND f.ParentFieldID <> 160 and c.FieldID <> 160 -- exclude Volunteer fields



                    union all
                    -- Need to include the Primary Alumni field so default privacy can be set for it.

                    SELECT 
                        f.CountryMask, 
                        c.FieldName "Category", 
                        f.FieldID, 
                        f.AttribTypeID, 
                        f.FieldName, 
                        f.PrivateShowByDefault, 
                        c.Sequence as 'ParentSequence', 
                        f.Sequence as 'FieldSequence'
                    FROM 
                        dbo.ApplicationFields f 
                        INNER JOIN dbo.Clients ON 1 = 1
                        LEFT OUTER JOIN dbo.ApplicationFields c ON f.ParentFieldID = c.FieldID
                    where 
                        f.CountryMask & Clients.RECountryMask = Clients.RECountryMask
                        AND Clients.ID = @ClientsID
                        AND f.FieldType = @FieldType
                        AND f.FieldID = 132
                    ) 

                    SELECT 
                        CountryMask, 
                        Category, 
                        FieldID, 
                        AttribTypeID, 
                        FieldName, 
                        PrivateShowByDefault
                    from FIELDS_CTE
                    order by FIELDS_CTE.ParentSequence, FIELDS_CTE.FieldSequence
            END