USP_EXCHANGEFIELDMAPPING_VALIDATEMAPPINGS

            create procedure dbo.USP_EXCHANGEFIELDMAPPING_VALIDATEMAPPINGS
            (
                @PHONES xml, 
                @ADDRESSES xml,
                @EMAILADDRESSES xml
            )
            with execute as caller
            as            
                declare @PHONETYPECODEID uniqueidentifier;
                declare @ADDRESSTYPECODEID uniqueidentifier;
                declare @EMAILADDRESSTYPECODEID uniqueidentifier;
                declare @OUTLOOKTYPECODE int;

                declare @ERROR nvarchar(500);

                declare @PHONESTABLE table
                    (
                        ID int,
                        PHONETYPECODEID uniqueidentifier,
                        OUTLOOKTYPECODE int
                    );

                declare @ADDRESSESTABLE table
                    (
                        ID int,                        
                        ADDRESSTYPECODEID uniqueidentifier,
                        OUTLOOKTYPECODE int
                    );

                declare @EMAILADDRESSESTABLE table
                    (
                        ID int,                        
                        EMAILADDRESSTYPECODEID uniqueidentifier,
                        OUTLOOKTYPECODE int
                    );

                insert into @PHONESTABLE(ID, PHONETYPECODEID, OUTLOOKTYPECODE) select ROW_NUMBER() OVER (Order by ID), PHONETYPECODEID, OUTLOOKPHONETYPECODE from dbo.UFN_EXCHANGEPHONEMAPPING_GETMAPPINGS_FROMITEMLISTXML(@PHONES);
                insert into @ADDRESSESTABLE(ID, ADDRESSTYPECODEID, OUTLOOKTYPECODE) select ROW_NUMBER() OVER (Order by ID), ADDRESSTYPECODEID, OUTLOOKADDRESSTYPECODE from dbo.UFN_EXCHANGEADDRESSMAPPING_GETMAPPINGS_FROMITEMLISTXML(@ADDRESSES);
                insert into @EMAILADDRESSESTABLE(ID, EMAILADDRESSTYPECODEID, OUTLOOKTYPECODE) select ROW_NUMBER() OVER (Order by ID), EMAILADDRESSTYPECODEID, OUTLOOKEMAILADDRESSTYPECODE from dbo.UFN_EXCHANGEEMAILADDRESSMAPPING_GETMAPPINGS_FROMITEMLISTXML(@EMAILADDRESSES);            

                --Verify that the phone type is only listed once in this group list

                set @PHONETYPECODEID = null;

                select top 1 @PHONETYPECODEID = PHONETYPECODEID from @PHONESTABLE as PHONES group by PHONES.PHONETYPECODEID having count(ID) > 1;

                if @PHONETYPECODEID is not null begin
                    set @ERROR = 'The phone type ''' + dbo.UFN_PHONETYPECODE_GETDESCRIPTION(@PHONETYPECODEID) + ''' is mapped multiple times.';
                    raiserror(@ERROR, 13, 1);
                end

                --Verify that the address type is only listed once in this group list

                set @ADDRESSTYPECODEID = null;

                select top 1 @ADDRESSTYPECODEID = ADDRESSTYPECODEID from @ADDRESSESTABLE as ADDRESSES group by ADDRESSES.ADDRESSTYPECODEID having count(ID) > 1;

                if @ADDRESSTYPECODEID is not null begin
                    set @ERROR = 'The address type ''' + dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(@ADDRESSTYPECODEID) + ''' is mapped multiple times.';
                    raiserror(@ERROR, 13, 1);
                end

                --Verify that the email address type is only listed once in this group list

                set @EMAILADDRESSTYPECODEID = null;

                select top 1 @EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID from @EMAILADDRESSESTABLE as EMAILADDRESSES group by EMAILADDRESSES.EMAILADDRESSTYPECODEID having count(ID) > 1;

                if @EMAILADDRESSTYPECODEID is not null begin
                    set @ERROR = 'The email address type ''' + dbo.UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION(@EMAILADDRESSTYPECODEID) + ''' is mapped multiple times.';
                    raiserror(@ERROR, 13, 1);
                end




                --Verify that the phone type is is uniquely mapped in this group list

                set @OUTLOOKTYPECODE = null;

                select top 1 @OUTLOOKTYPECODE = OUTLOOKTYPECODE from @PHONESTABLE as PHONES group by PHONES.OUTLOOKTYPECODE having count(ID) > 1;

                if @OUTLOOKTYPECODE is not null begin
                    select top 1 @PHONETYPECODEID = PHONETYPECODEID from @PHONESTABLE as PHONES where PHONES.OUTLOOKTYPECODE = @OUTLOOKTYPECODE
                    set @ERROR = 'The phone type ''' + dbo.UFN_PHONETYPECODE_GETDESCRIPTION(@PHONETYPECODEID) + ''' is mapped to an exchange phone type that is already mapped.';
                    raiserror(@ERROR, 13, 1);
                end                

                --Verify that the address type is is uniquely mapped in this group list

                set @OUTLOOKTYPECODE = null;

                select top 1 @OUTLOOKTYPECODE = OUTLOOKTYPECODE from @ADDRESSESTABLE as ADDRESSES group by ADDRESSES.OUTLOOKTYPECODE having count(ID) > 1;

                if @OUTLOOKTYPECODE is not null begin
                    select top 1 @ADDRESSTYPECODEID = ADDRESSTYPECODEID from @ADDRESSESTABLE as ADDRESS where ADDRESS.OUTLOOKTYPECODE = @OUTLOOKTYPECODE
                    set @ERROR = 'The address type ''' + dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(@ADDRESSTYPECODEID) + ''' is mapped to an exchange address type that is already mapped.';
                    raiserror(@ERROR, 13, 1);
                end

                --Verify that the email address type is is uniquely mapped in this group list

                set @OUTLOOKTYPECODE = null;

                select top 1 @OUTLOOKTYPECODE = OUTLOOKTYPECODE from @EMAILADDRESSESTABLE as EMAILADDRESSES group by EMAILADDRESSES.OUTLOOKTYPECODE having count(ID) > 1;

                if @OUTLOOKTYPECODE is not null begin
                    select top 1 @EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID from @EMAILADDRESSESTABLE as EMAILADDRESS where EMAILADDRESS.OUTLOOKTYPECODE = @OUTLOOKTYPECODE
                    set @ERROR = 'The email address type ''' + dbo.UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION(@EMAILADDRESSTYPECODEID) + ''' is mapped to an exchange email address type that is already mapped.';
                    raiserror(@ERROR, 13, 1);
                end