USP_DISCOUNT_MERCHANDISEITEMS_ADDFROMXML

CREATE procedure [dbo].[USP_DISCOUNT_MERCHANDISEITEMS_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,
       [QUALIFYINGITEMID] uniqueidentifier,
       [DISCOUNTGROUPID] uniqueidentifier,
       [MERCHANDISEITEMID] uniqueidentifier,
       [SEQUENCE] integer)

    insert into @TempTbl 
    select            
        T.c.value('(DISCOUNTITEMID)[1]','uniqueidentifier') as 'DISCOUNTITEMID',            
        T.c.value('(QUALIFYINGITEMID)[1]','uniqueidentifier') as 'QUALIFYINGITEMID',
        T.c.value('(DISCOUNTGROUPID)[1]','uniqueidentifier') as 'DISCOUNTGROUPID',
        T.c.value('(MERCHANDISEITEMID)[1]','uniqueidentifier') as 'MERCHANDISEITEMID',
        T.c.value('(SEQUENCE)[1]','integer') as 'SEQUENCE'
    from @XML.nodes('/DISCOUNTMERCHANDISEITEMS/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],
        1,
        2, -- Merchandise item

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

    if @@Error <> 0
        return 2;

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

    if @@Error <> 0
        return 2;

    return 0;