spGetClientUserPrivacy

      CREATE PROCEDURE [dbo].[spGetClientUserPrivacy]
                    (
                        @ClientsID int,
                        @RaisersEdgeRecordID int,
                        @ItIsMe bit
                    )
                AS
            BEGIN
                set nocount on

                DECLARE @BackOfficeSystemPeopleID int
                exec dbo.spGetBackOfficeSystemPeopleID @RaisersEdgeRecordID, 0, @BackOfficeSystemPeopleID OUTPUT

                DECLARE @RECountryMask INT
                SELECT @RECountryMask = RECountryMask
                FROM dbo.Clients
                WHERE [id] = @ClientsID


                if @ItIsMe = 1 And @BackOfficeSystemPeopleID > 0
                    INSERT INTO ClientUserFieldPrivacy(BackOfficeSystemPeopleID, FieldID, FieldAttribID, Private)
                    SELECT @BackOfficeSystemPeopleID, fields.fieldID, fields.AttribTypeID, isnull(fp.private,case when fields.privateShowbydefault=1 then 0  else 1 end) Private
                    FROM dbo.ApplicationFields Fields
                    LEFT OUTER JOIN dbo.ApplicationFields Cats ON Fields.ParentFieldID = Cats.FieldID
                    LEFT OUTER JOIN dbo.ClientUserFieldPrivacy FP ON FP.FieldID = fields.FieldID and FP.FieldAttribID = fields.AttribTypeID AND FP.BackOfficeSystemPeopleID = @BackOfficeSystemPeopleID
                    WHERE 
                    Fields.CountryMask & @RECountryMask = @RECountryMask
                    and fields.fieldtype=1
                    and fields.parentFieldID is not null
                    and fp.Private is null

                SELECT fields.fieldID, fields.AttribTypeID FieldAttribID, isnull(fp.private,case when fields.privateShowbydefault=1 then 0  else 1 end) Private
                FROM dbo.ApplicationFields Fields
                LEFT OUTER JOIN dbo.ApplicationFields Cats ON Fields.ParentFieldID = Cats.FieldID
                LEFT OUTER JOIN dbo.ClientUserFieldPrivacy FP ON FP.FieldID = fields.FieldID and FP.FieldAttribID = fields.AttribTypeID    and FP.BackOfficeSystemPeopleID = @BackOfficeSystemPeopleID
                WHERE 
                Fields.CountryMask & @RECountryMask = @RECountryMask
                and fields.fieldtype=1
                and fields.parentFieldID is not null
            END