USP_ORGANIZATIONHIERARCHY_POSITIONS_UPDATEFROMXML

            CREATE PROCEDURE [dbo].[USP_ORGANIZATIONHIERARCHY_POSITIONS_UPDATEFROMXML] 
            (
            @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,
               [NAME] nvarchar(50),
               [SITEID] uniqueidentifier,
               [BUSINESSUNITCODEID] uniqueidentifier
            )

            insert into @TempTbl select 
                [ID],
                [NAME],
                [SITEID],
                [BUSINESSUNITCODEID]
            from dbo.UFN_ORGANIZATIONHIERARCHY_POSITIONS_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.ORGANIZATIONPOSITION 
            where ORGANIZATIONPOSITION.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

            update ORGANIZATIONPOSITION
                    set ORGANIZATIONPOSITION.[NAME]=temp.[NAME],
                    ORGANIZATIONPOSITION.[SITEID]=temp.[SITEID],
                    ORGANIZATIONPOSITION.[BUSINESSUNITCODEID]=temp.[BUSINESSUNITCODEID],
                    ORGANIZATIONPOSITION.CHANGEDBYID = @CHANGEAGENTID,
                    ORGANIZATIONPOSITION.DATECHANGED = @CHANGEDATE
                from dbo.ORGANIZATIONPOSITION inner join @TempTbl as [temp] on ORGANIZATIONPOSITION.ID = [temp].ID
                where (ORGANIZATIONPOSITION.[NAME]<>temp.[NAME]) or 
                    (ORGANIZATIONPOSITION.[NAME] is null and temp.[NAME] is not null) or 
                    (ORGANIZATIONPOSITION.[NAME] is not null and temp.[NAME] is null) or 
                    (ORGANIZATIONPOSITION.[SITEID]<>temp.[SITEID]) or 
                    (ORGANIZATIONPOSITION.[SITEID] is null and temp.[SITEID] is not null) or 
                    (ORGANIZATIONPOSITION.[SITEID] is not null and temp.[SITEID] is null) or
                    (ORGANIZATIONPOSITION.[BUSINESSUNITCODEID]<>temp.[BUSINESSUNITCODEID]) or 
                    (ORGANIZATIONPOSITION.[BUSINESSUNITCODEID] is null and temp.[BUSINESSUNITCODEID] is not null) or 
                    (ORGANIZATIONPOSITION.[BUSINESSUNITCODEID] is not null and temp.[BUSINESSUNITCODEID] is null)

            if @@Error <> 0
                return 3;    

/*
            -- Report groups to update.
            -- build a temporary table containing the values from the XML
            declare @rpgPositions table (
               [REPORTGROUPID] uniqueidentifier,
               [POSITIONID] uniqueidentifier
            );

            insert into @rpgPositions (
                REPORTGROUPID,
                POSITIONID)
            (select A.REPORTGROUPID, B.ID
            from (
                select RGP.REPORTGROUPID, PARENTID
                from REPORTGROUPPOSITION RGP
                inner join dbo.ORGANIZATIONHIERARCHY OH
                    on RGP.POSITIONID = OH.ID
                group by RGP.REPORTGROUPID, OH.PARENTID
                having count(*) = 
                    (select count(*) from dbo.ORGANIZATIONPOSITION OPsub
                        inner join dbo.ORGANIZATIONHIERARCHY OHsub
                            on OPsub.ID = OHsub.ID
                         where OHsub.PARENTID = OH.PARENTID)
            ) A
            inner join 
            (
                select  
                    OH.[ID],
                    OH.[PARENTID]
                from @TempTbl as [temp]
                inner join dbo.ORGANIZATIONHIERARCHY OH
                    on temp.ID = OH.ID
                where not exists (select ID from dbo.ORGANIZATIONPOSITION as data where data.ID = [temp].ID)
            ) B
                on A.PARENTID = B.PARENTID);

            if @@Error <> 0
                return 4;    
*/

            -- insert new items

            insert into dbo.ORGANIZATIONPOSITION(
                [ID],
                [NAME],
                [SITEID],
                [BUSINESSUNITCODEID],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select  
                [ID],
                [NAME],
                [SITEID],
                [BUSINESSUNITCODEID],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.ORGANIZATIONPOSITION as data where data.ID = [temp].ID)

            if @e <> 0
                return 5;
/*
            -- insert new items into report groups that have all previous selected
            insert into dbo.REPORTGROUPPOSITION(
                [REPORTGROUPID],
                [POSITIONID],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select  
                [REPORTGROUPID],
                [POSITIONID],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @rpgPositions;

            if @@Error <> 0
                return 6;
*/                            
            return 0;