USP_ORGANIZATIONHIERARCHY_EDIT_POSITIONHOLDERS

            CREATE PROCEDURE [dbo].[USP_ORGANIZATIONHIERARCHY_EDIT_POSITIONHOLDERS] 
            (
            @XML xml,
            @CHANGEAGENTID uniqueidentifier = null,
            @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
               [ID] uniqueidentifier,
               [POSITIONID] uniqueidentifier,
               [CONSTITUENTID] uniqueidentifier,
               [DATEFROM] datetime,
               [DATETO] datetime
            )

            insert into @TempTbl select 
                [ID],
                [POSITIONID],
                [CONSTITUENTID],
                [DATEFROM],
                [DATETO]
            from dbo.UFN_ORGANIZATIONHIERARCHY_POSITIONHOLDERS_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            declare @contextCache varbinary(128);
            declare @e int;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete any items that no longer exist in the XML table

            delete from dbo.ORGANIZATIONPOSITIONHOLDER 
            where ORGANIZATIONPOSITIONHOLDER.ID not in 
                (select ID from @TempTbl)

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @@Error <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            -- we must update items based on how the dates are changed

            -- decreases in [DATETO] first

            -- changes to [DATEFROM] next

            -- increases in [DATETO] last

            -- changes to position and constituent are not allowed

            declare @UPDATEID uniqueidentifier;
            declare @DATEFROM datetime;
            declare @DATETO datetime;

            declare CUR_UPDATES cursor local fast_forward for

            SELECT    temp.ID,
                    temp.[DATEFROM],
                    temp.[DATETO]
            from dbo.ORGANIZATIONPOSITIONHOLDER inner join @TempTbl as [temp] on ORGANIZATIONPOSITIONHOLDER.ID = [temp].ID
            where (ORGANIZATIONPOSITIONHOLDER.[DATEFROM]<>temp.[DATEFROM]) or 
                (ORGANIZATIONPOSITIONHOLDER.[DATEFROM] is null and temp.[DATEFROM] is not null) or 
                (ORGANIZATIONPOSITIONHOLDER.[DATEFROM] is not null and temp.[DATEFROM] is null) or
                (ORGANIZATIONPOSITIONHOLDER.[DATETO]<>temp.[DATETO]) or 
                (ORGANIZATIONPOSITIONHOLDER.[DATETO] is null and temp.[DATETO] is not null) or 
                (ORGANIZATIONPOSITIONHOLDER.[DATETO] is not null and temp.[DATETO] is null)
            order by 
                case when temp.[DATETO] < ORGANIZATIONPOSITIONHOLDER.[DATETO] then 0
                    else case when temp.[DATETO] > ORGANIZATIONPOSITIONHOLDER.[DATETO] then 2 else 1 end 
                end,
                temp.[DATEFROM];

                open CUR_UPDATES;

                fetch next from CUR_UPDATES into @UPDATEID, @DATEFROM, @DATETO;
                while @@FETCH_STATUS = 0
                    begin

                        update dbo.ORGANIZATIONPOSITIONHOLDER
                        set DATEFROM = @DATEFROM,
                            DATETO = @DATETO,
                            CHANGEDBYID = @CHANGEAGENTID, 
                            DATECHANGED = @CHANGEDATE
                        where ORGANIZATIONPOSITIONHOLDER.ID = @UPDATEID;

                        fetch next from CUR_UPDATES into @UPDATEID, @DATEFROM, @DATETO;
                    end

                close CUR_UPDATES;
                deallocate CUR_UPDATES;

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into dbo.ORGANIZATIONPOSITIONHOLDER(
                [ID],
                [POSITIONID],
                [CONSTITUENTID],
                [DATEFROM],
                [DATETO],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select  
                [ID],
                [POSITIONID],
                [CONSTITUENTID],
                [DATEFROM],
                [DATETO],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.ORGANIZATIONPOSITIONHOLDER as data where data.ID = [temp].ID)

            if @e <> 0
                return 4;

            return 0;