USP_DISCOUNT_ADDDISCOUNTPROGRAMSFROMXML

Adds to the discounted programs table from the xml passed to it from the grid.
Parameters

Parameter	Parameter Type	Mode
@DISCOUNTID	uniqueidentifier	IN
@XML	xml	IN
@DISCOUNTTYPECODE	tinyint	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@DISCOUNTITEMTYPECODE	tinyint	IN
@QUALIFYINGITEMTYPECODE	tinyint	IN
Definition

Copy


            CREATE procedure [dbo].[USP_DISCOUNT_ADDDISCOUNTPROGRAMSFROMXML] 
            (
                @DISCOUNTID uniqueidentifier,
                @XML xml,            
                @DISCOUNTTYPECODE tinyint = 0,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @DISCOUNTITEMTYPECODE tinyint = 0,
                @QUALIFYINGITEMTYPECODE tinyint = 0
            )

            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,
               [PROGRAMID] uniqueidentifier)

            insert into @TempTbl select 
                [DISCOUNTITEMID], [DISCOUNTGROUPID], [QUALIFYINGITEMID], [PROGRAMID] 
            from dbo.UFN_DISCOUNT_GETDISCOUNTPROGRAMS_FROMITEMLISTXML(@XML)

            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 [DISCOUNTGROUP] 
                ([DISCOUNTID], 
                [ID],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select @DISCOUNTID, 
                [DISCOUNTGROUPID],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE                
            from @TempTbl as [temp]

            if @@Error <> 0
                return 2;

            if @DISCOUNTITEMTYPECODE = 0
            begin


                insert into [DISCOUNTGROUPDETAIL]
                    ([DISCOUNTGROUPID], 
                    [ID],
                    DISCOUNTEDITEM,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select [DISCOUNTGROUPID], 
                    [DISCOUNTITEMID],
                    1,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]

                if @@Error <> 0
                    return 2;

                insert into [DISCOUNTGROUPDETAILPROGRAM]
                    (PROGRAMID, 
                    [ID],                
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select [PROGRAMID], 
                    [DISCOUNTITEMID],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]

                if @@Error <> 0
                    return 2;
            end

            if @DISCOUNTTYPECODE = 1 and @QUALIFYINGITEMTYPECODE = 0
                begin        
                    update @TempTbl set QUALIFYINGITEMID = newid() where (QUALIFYINGITEMID is null) or (QUALIFYINGITEMID = '00000000-0000-0000-0000-000000000000');

                    insert into [DISCOUNTGROUPDETAIL]
                        ([DISCOUNTGROUPID], 
                        [ID],
                        DISCOUNTEDITEM,
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED)
                    select [DISCOUNTGROUPID], 
                        [QUALIFYINGITEMID],
                        0,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CHANGEDATE, 
                        @CHANGEDATE
                    from @TempTbl as [temp]

                    if @@Error <> 0
                        return 2;

                    insert into [DISCOUNTGROUPDETAILPROGRAM]
                        (PROGRAMID, 
                        [ID],                
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED)
                    select [PROGRAMID], 
                        [QUALIFYINGITEMID],
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CHANGEDATE, 
                        @CHANGEDATE
                    from @TempTbl as [temp]

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