USP_DISCOUNT_QUALIFYINGMERCHANDISEDEPARTMENTS_ADDFROMXML

create procedure dbo.USP_DISCOUNT_QUALIFYINGMERCHANDISEDEPARTMENTS_ADDFROMXML
(
    @DISCOUNTID uniqueidentifier,
    @XML xml,            
    @DISCOUNTTYPECODE tinyint = 0,
    @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 (
       [DISCOUNTITEMID] uniqueidentifier,
       [DISCOUNTGROUPID] uniqueidentifier,
       [MERCHANDISEDEPARTMENTID] uniqueidentifier,
       [SEQUENCE] integer)

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

    update @TempTbl set DISCOUNTITEMID = newid() where (DISCOUNTITEMID is null) or (DISCOUNTITEMID = '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;

    -- insert new items            

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

    if @@Error <> 0
        return 2;

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

        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as [temp]

    if @@Error <> 0
        return 2;

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

    if @@Error <> 0
        return 2;
    return 0;