USP_DISCOUNT_MERCHANDISEDEPARTMENTS_UPDATEFROMXML

CREATE procedure dbo.USP_DISCOUNT_MERCHANDISEDEPARTMENTS_UPDATEFROMXML
(
    @DISCOUNTID uniqueidentifier,
    @XML xml,
    @CHANGEAGENTID uniqueidentifier,
    @CHANGEDATE datetime,
    @DISCOUNTEDITEM tinyint = 1
)
as
begin
    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 (
       [DISCOUNTGROUPID] uniqueidentifier,
       [ID] uniqueidentifier,
       [MERCHANDISEDEPARTMENTID] uniqueidentifier,
       [SEQUENCE] integer)

    insert into @TempTbl 
    select
        T.c.value('(DISCOUNTGROUPID)[1]', 'uniqueidentifier') as DISCOUNTGROUPID,
        T.c.value('(ID)[1]','uniqueidentifier') as ID,            
        T.c.value('(MERCHANDISEDEPARTMENTID)[1]','uniqueidentifier') as MERCHANDISEDEPARTMENTID,
        T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
    from @XML.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(c)

    update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
    update @TempTbl set DISCOUNTGROUPID = newid() where (DISCOUNTGROUPID is null) or (DISCOUNTGROUPID = '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 from dbo.[DISCOUNTGROUPDETAIL] where [DISCOUNTGROUPDETAIL].ID in 
        (select DISCOUNTGROUPDETAIL.ID 
        from dbo.DISCOUNTGROUPDETAIL
        inner join dbo.DISCOUNTGROUP 
            on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
        inner join dbo.DISCOUNT
            on DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
        where DISCOUNT.ID = @DISCOUNTID
            and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = @DISCOUNTEDITEM
            and DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE <> 3
        EXCEPT select ID from @TempTbl)

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;

    update dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
        set DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID = TEMP.MERCHANDISEDEPARTMENTID,
            DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.SEQUENCE = TEMP.SEQUENCE
        from dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
        inner join @TempTbl as TEMP on TEMP.ID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID

    insert into dbo.[DISCOUNTGROUP] 
        ([DISCOUNTID], 
        [ID],    
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
    select 
        @DISCOUNTID, 
        [DISCOUNTGROUPID],
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE                
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT] as data where data.ID = [temp].ID)

    insert into dbo.[DISCOUNTGROUPDETAIL]
        ([DISCOUNTGROUPID], 
        [ID],
        DISCOUNTEDITEM,
        DISCOUNTGROUPDETAILAPPLICATIONCODE,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
    select 
        [DISCOUNTGROUPID], 
        [ID],
        @DISCOUNTEDITEM,
        1, -- Merchandise department

        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT] as data where data.ID = [temp].ID)

    if @@Error <> 0
        return 2;

    insert into dbo.[DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT]
        ([ID],
        MERCHANDISEDEPARTMENTID,
        SEQUENCE,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
    select 
        [ID],
        MERCHANDISEDEPARTMENTID,
        SEQUENCE,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT] as data where data.ID = [temp].ID)

end

if @@Error <> 0
    return 4;

return 0;