USP_DISCOUNT_ADD

Adds a discount and child records.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@APPLIESTOCODE	tinyint	IN
@APPLICATIONTYPECODE	tinyint	IN
@DISCOUNTTYPECODE	tinyint	IN
@CALCULATIONTYPECODE	tinyint	IN
@PERCENT	decimal(5, 2)	IN
@AMOUNT	money	IN
@NUMBERTOPURCHASE	int	IN
@NUMBERTODISCOUNTTYPECODE	tinyint	IN
@NUMBERTODISCOUNT	int	IN
@LIMITDISCOUNTSPERORDER	bit	IN
@NUMBEROFDISCOUNTSPERORDER	int	IN
@BOGOCALCULATIONTYPECODE	tinyint	IN
@BOGOLIMITDISCOUNTSPERORDER	bit	IN
@BOGONUMBEROFDISCOUNTSPERORDER	int	IN
@DISCOUNTTICKETSFORCODE	tinyint	IN
@ALLOWWALKUPSALES	bit	IN
@WALKUPID	uniqueidentifier	IN
@WALKUPACTIVE	bit	IN
@ALLOWADVANCEDSALES	bit	IN
@ADVANCEDID	uniqueidentifier	IN
@ADVANCEDACTIVE	bit	IN
@ALLOWONLINESALES	bit	IN
@ONLINEID	uniqueidentifier	IN
@ONLINEACTIVE	bit	IN
@ALLOWMONDAY	bit	IN
@ALLOWTUESDAY	bit	IN
@ALLOWWEDNESDAY	bit	IN
@ALLOWTHURSDAY	bit	IN
@ALLOWFRIDAY	bit	IN
@ALLOWSATURDAY	bit	IN
@ALLOWSUNDAY	bit	IN
@ALLOWDISCOUNTDATEFROM	datetime	IN
@ALLOWDISCOUNTDATETO	datetime	IN
@ALLOWDISCOUNTTIMEFROM	UDT_HOURMINUTE	IN
@ALLOWDISCOUNTTIMETO	UDT_HOURMINUTE	IN
@ITEMAMOUNT	money	IN
@ITEMPERCENT	decimal(5, 2)	IN
@PROMOTIONALCODES	xml	IN
@DISCOUNTQUALIFYINGPRICETYPES	xml	IN
@DISCOUNTPRICETYPES	xml	IN
@DISCOUNTPROGRAMS	xml	IN
@DISCOUNTMEMBERS	xml	IN
@DISCOUNTCONSTITUENCYCODES	xml	IN
@ADDRESSSELECTIONID	uniqueidentifier	IN
@GROUPSIZES	xml	IN
@GROUPSALESID	uniqueidentifier	IN
@GROUPSALESACTIVE	bit	IN
@ALLOWGROUPSALES	bit	IN
@APPLIESTOTICKETS	bit	IN
@APPLIESTOMERCHANDISE	bit	IN
@APPLIESTOMERCHANDISECODE	tinyint	IN
@DISCOUNTMERCHANDISEDEPARTMENTS	xml	IN
@DISCOUNTMERCHANDISEITEMS	xml	IN
@ITEMMERCHANDISEPERCENT	decimal(5, 2)	IN
@ITEMMERCHANDISEAMOUNT	money	IN
@QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS	xml	IN
@QUALIFYINGDISCOUNTMERCHANDISEITEMS	xml	IN
@QUALIFYINGITEMTYPECODE	tinyint	IN
@DISCOUNTITEMTYPECODE	tinyint	IN
@DISCOUNTMERCHANDISEFORCODE	tinyint	IN
@QUALIFYINGAPPLIESTOMERCHANDISECODE	tinyint	IN
Definition

Copy


CREATE procedure dbo.USP_DISCOUNT_ADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255) = '',
    @APPLIESTOCODE tinyint = 0,
    @APPLICATIONTYPECODE tinyint = 0,
    @DISCOUNTTYPECODE tinyint = 0,
    @CALCULATIONTYPECODE tinyint = 0,
    @PERCENT decimal(5,2) = 0.0,
    @AMOUNT money = 0,
    @NUMBERTOPURCHASE int = 1,
    @NUMBERTODISCOUNTTYPECODE tinyint = 0,
    @NUMBERTODISCOUNT int = 1,
    @LIMITDISCOUNTSPERORDER bit = 0,
    @NUMBEROFDISCOUNTSPERORDER int = 1,
    @BOGOCALCULATIONTYPECODE tinyint = 0,
    @BOGOLIMITDISCOUNTSPERORDER bit = 0,
    @BOGONUMBEROFDISCOUNTSPERORDER int = 1,
    @DISCOUNTTICKETSFORCODE tinyint = 0,
    @ALLOWWALKUPSALES bit = 1,
    @WALKUPID uniqueidentifier = null,
    @WALKUPACTIVE bit = 1,
    @ALLOWADVANCEDSALES bit = 1,
    @ADVANCEDID uniqueidentifier = null,
    @ADVANCEDACTIVE bit = 1,
    @ALLOWONLINESALES bit = 1,
    @ONLINEID uniqueidentifier = null,
    @ONLINEACTIVE bit = 1,
    @ALLOWMONDAY bit=1,
    @ALLOWTUESDAY bit=1,
    @ALLOWWEDNESDAY bit=1,
    @ALLOWTHURSDAY bit=1,
    @ALLOWFRIDAY bit=1,
    @ALLOWSATURDAY bit=1,
    @ALLOWSUNDAY bit=1,
    @ALLOWDISCOUNTDATEFROM datetime = null,
    @ALLOWDISCOUNTDATETO datetime = null,
    @ALLOWDISCOUNTTIMEFROM dbo.UDT_HOURMINUTE = null,
    @ALLOWDISCOUNTTIMETO dbo.UDT_HOURMINUTE = null,
    @ITEMAMOUNT money = 0,
    @ITEMPERCENT decimal (5,2) = 0.0,
    @PROMOTIONALCODES xml = null,
    @DISCOUNTQUALIFYINGPRICETYPES xml = null,
    @DISCOUNTPRICETYPES xml = null,
    @DISCOUNTPROGRAMS xml = null,
    @DISCOUNTMEMBERS xml = null,
    @DISCOUNTCONSTITUENCYCODES xml = null,
    @ADDRESSSELECTIONID uniqueidentifier = null,
    @GROUPSIZES xml = null,
    @GROUPSALESID uniqueidentifier = null,
    @GROUPSALESACTIVE bit = null,
    @ALLOWGROUPSALES bit = 0,
    @APPLIESTOTICKETS bit = 1,
    @APPLIESTOMERCHANDISE bit = 0,
    @APPLIESTOMERCHANDISECODE tinyint = 0,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml = null,
    @DISCOUNTMERCHANDISEITEMS xml = null,
    @ITEMMERCHANDISEPERCENT decimal (5, 2) = 0.0,
    @ITEMMERCHANDISEAMOUNT money = 0,
    @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS xml = null,
    @QUALIFYINGDISCOUNTMERCHANDISEITEMS xml = null,
    @QUALIFYINGITEMTYPECODE tinyint = 0,
    @DISCOUNTITEMTYPECODE tinyint = 0,
    @DISCOUNTMERCHANDISEFORCODE tinyint = 0,
    @QUALIFYINGAPPLIESTOMERCHANDISECODE tinyint= 0
)
as
    set nocount on;

    if @ID is null
        set @ID = newid();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    if @DISCOUNTTYPECODE = 0  --if standard discount

        begin
            set @NUMBERTODISCOUNTTYPECODE = 1
            set @NUMBERTODISCOUNT = 1
            set @NUMBERTOPURCHASE = 1
            set @DISCOUNTTICKETSFORCODE = 0
            set @BOGOCALCULATIONTYPECODE = 0
            set @BOGOLIMITDISCOUNTSPERORDER = 0
            set @BOGONUMBEROFDISCOUNTSPERORDER = 0

            if @APPLIESTOCODE = 0 -- if Order

                begin
                    set @ITEMPERCENT = 0
                    set @ITEMAMOUNT = 0

                    if @CALCULATIONTYPECODE = 0
                        set @PERCENT = 0
                    else
                        set @AMOUNT = 0

                    set @ITEMMERCHANDISEPERCENT = 0
                    set @ITEMMERCHANDISEAMOUNT = 0

                    set @LIMITDISCOUNTSPERORDER = 0
                    set @NUMBEROFDISCOUNTSPERORDER = 1

                    --Blank out the programs

                    set @DISCOUNTPROGRAMS = null
                    set @DISCOUNTMERCHANDISEDEPARTMENTS = null
                    set @DISCOUNTMERCHANDISEITEMS = null
                end
            else  -- Item

                begin
                    set @PERCENT = 0
                    set @AMOUNT = 0

                    if @CALCULATIONTYPECODE = 0
                    begin
                        set @ITEMMERCHANDISEPERCENT = 0
                        if @APPLIESTOMERCHANDISE = 1 and @ITEMMERCHANDISEAMOUNT = 0
                            raiserror('BBERR_INVALIDMERCHANDISEAMOUNT', 13, 1)
                    end
                    else
                    begin
                        set @ITEMMERCHANDISEAMOUNT = 0
                        if @APPLIESTOMERCHANDISE = 1 and @ITEMMERCHANDISEPERCENT = 0
                            raiserror('BBERR_INVALIDMERCHANDISEPERCENT', 13, 1)
                    end

                    if @LIMITDISCOUNTSPERORDER = 0
                    begin
                        set @NUMBEROFDISCOUNTSPERORDER = 1
                    end
                end
        end
    else if @DISCOUNTTYPECODE = 1  -- if with required purchase discount

        begin
            set @PERCENT = 0
            set @AMOUNT = 0
            set @ITEMPERCENT = 0
            set @ITEMAMOUNT = 0

            set @CALCULATIONTYPECODE = @BOGOCALCULATIONTYPECODE
            set @LIMITDISCOUNTSPERORDER = @BOGOLIMITDISCOUNTSPERORDER

            if @DISCOUNTITEMTYPECODE = 0
            begin
                set @ITEMMERCHANDISEPERCENT = 0
                set @ITEMMERCHANDISEAMOUNT = 0
            end
            else
            begin
                if @BOGOCALCULATIONTYPECODE = 0
                begin
                    set @ITEMMERCHANDISEPERCENT = 0
                    if @ITEMMERCHANDISEAMOUNT <= 0 and @APPLIESTOMERCHANDISE = 1
                        raiserror('BBERR_INVALIDMERCHANDISEAMOUNT', 13, 1);
                end
                else
                begin
                    set @ITEMMERCHANDISEAMOUNT = 0
                    if @ITEMMERCHANDISEPERCENT <= 0 and @APPLIESTOMERCHANDISE = 1
                        raiserror('BBERR_INVALIDMERCHANDISEPERCENT', 13, 1);
                end
            end

            if @NUMBERTODISCOUNTTYPECODE = 1 -- Unlimited

                begin
                    set @NUMBERTODISCOUNT = 1
                    set @LIMITDISCOUNTSPERORDER = 0
                    set @NUMBEROFDISCOUNTSPERORDER = 1
                end

            if @LIMITDISCOUNTSPERORDER = 0
                set @NUMBEROFDISCOUNTSPERORDER = 1
            else
                set @NUMBEROFDISCOUNTSPERORDER = @BOGONUMBEROFDISCOUNTSPERORDER

        end
    else  -- if group size discount

        begin
            set @NUMBERTODISCOUNTTYPECODE = 1
            set @NUMBERTODISCOUNT = 1
            set @NUMBERTOPURCHASE = 1
            set @DISCOUNTTICKETSFORCODE = 0
            set @BOGOCALCULATIONTYPECODE = 0
            set @BOGOLIMITDISCOUNTSPERORDER = 0
            set @BOGONUMBEROFDISCOUNTSPERORDER = 0

            set @PERCENT = 0
            set @AMOUNT = 0
            set @ITEMPERCENT = 0
            set @ITEMAMOUNT = 0

            set @LIMITDISCOUNTSPERORDER = 0
            set @NUMBEROFDISCOUNTSPERORDER = 1

        end

    begin try
        insert into dbo.DISCOUNT
        (
            ID,
            ORIGINALDISCOUNTID,
            NAME,
            [DESCRIPTION],
            APPLIESTOCODE,
            APPLICATIONTYPECODE,
            DISCOUNTTYPECODE,
            CALCULATIONTYPECODE,
            [PERCENT],
            AMOUNT,
            NUMBERTOPURCHASE,
            NUMBERTODISCOUNTTYPECODE,
            NUMBERTODISCOUNT,
            LIMITDISCOUNTSPERORDER,
            NUMBEROFDISCOUNTSPERORDER,
            DISCOUNTTICKETSFORCODE,
            APPLIESTOMERCHANDISE,
            APPLIESTOTICKETS,
            MERCHANDISEAMOUNT,
            MERCHANDISEPERCENT,
            QUALIFYINGITEMTYPECODE,
            DISCOUNTITEMTYPECODE,
            DISCOUNTMERCHANDISEFORCODE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @ID,
            @ID,
            @NAME,
            @DESCRIPTION,
            @APPLIESTOCODE,
            @APPLICATIONTYPECODE,
            @DISCOUNTTYPECODE,
            @CALCULATIONTYPECODE,
            @PERCENT,
            @AMOUNT,
            @NUMBERTOPURCHASE,
            @NUMBERTODISCOUNTTYPECODE,
            @NUMBERTODISCOUNT,
            @LIMITDISCOUNTSPERORDER,
            @NUMBEROFDISCOUNTSPERORDER,
            @DISCOUNTTICKETSFORCODE,
            @APPLIESTOMERCHANDISE,
            @APPLIESTOTICKETS,
            @ITEMMERCHANDISEAMOUNT,
            @ITEMMERCHANDISEPERCENT,
            @QUALIFYINGITEMTYPECODE,
            @DISCOUNTITEMTYPECODE,
            @DISCOUNTMERCHANDISEFORCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );


    declare @DISCOUNTAVAILABILITYID uniqueidentifier;
    set @DISCOUNTAVAILABILITYID = newid();

    insert into dbo.DISCOUNTAVAILABILITY
    (
        ID,
        DISCOUNTID,
        ALLOWMONDAY,
        ALLOWTUESDAY,
        ALLOWWEDNESDAY,
        ALLOWTHURSDAY,
        ALLOWFRIDAY,
        ALLOWSATURDAY,
        ALLOWSUNDAY,
        ALLOWDISCOUNTDATEFROM,
        ALLOWDISCOUNTDATETO,
        ALLOWDISCOUNTTIMEFROM,
        ALLOWDISCOUNTTIMETO,
        SEQUENCE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    values
    (
        @DISCOUNTAVAILABILITYID,
        @ID,
        @ALLOWMONDAY,
        @ALLOWTUESDAY,
        @ALLOWWEDNESDAY,
        @ALLOWTHURSDAY,
        @ALLOWFRIDAY,
        @ALLOWSATURDAY,
        @ALLOWSUNDAY,
        @ALLOWDISCOUNTDATEFROM,
        @ALLOWDISCOUNTDATETO,
        @ALLOWDISCOUNTTIMEFROM,
        @ALLOWDISCOUNTTIMETO,
        1,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    );


    If @WALKUPACTIVE=1
        begin
            If @ALLOWWALKUPSALES=1
                begin
                    if @WALKUPID is null
                    begin
                        select @WALKUPID = ID from dbo.SALESMETHOD where TYPECODE = 0;
                    end

                    insert into dbo.DISCOUNTAVAILABILITYSALESMETHOD
                    (
                        ID,
                        DISCOUNTAVAILABILITYID,
                        SALESMETHODID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values
                    (
                        newid(),
                        @DISCOUNTAVAILABILITYID,
                        @WALKUPID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end
        end

    If @ADVANCEDACTIVE=1
        begin
            If @ALLOWADVANCEDSALES=1
                begin
                    if @ADVANCEDID is null
                    begin
                        select @ADVANCEDID = ID from dbo.SALESMETHOD where TYPECODE = 1;
                    end

                    insert into dbo.DISCOUNTAVAILABILITYSALESMETHOD
                    (
                        ID,
                        DISCOUNTAVAILABILITYID,
                        SALESMETHODID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values
                    (
                        newid(),
                        @DISCOUNTAVAILABILITYID,
                        @ADVANCEDID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end
        end

    If @ONLINEACTIVE=1
        begin
            If @ALLOWONLINESALES=1
                begin
                    if @ONLINEID is null
                    begin
                        select @ONLINEID = ID from dbo.SALESMETHOD where TYPECODE = 2;
                    end

                    insert into dbo.DISCOUNTAVAILABILITYSALESMETHOD
                    (
                        ID,
                        DISCOUNTAVAILABILITYID,
                        SALESMETHODID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values
                    (
                        newid(),
                        @DISCOUNTAVAILABILITYID,
                        @ONLINEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end
        end

If @GROUPSALESACTIVE=1
        begin
            If @ALLOWGROUPSALES=1
                begin
                    if @GROUPSALESID is null
                    begin
                        select @GROUPSALESID = ID from dbo.SALESMETHOD where TYPECODE = 2;
                    end

                    insert into dbo.DISCOUNTAVAILABILITYSALESMETHOD
                    (
                        ID,
                        DISCOUNTAVAILABILITYID,
                        SALESMETHODID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values
                    (
                        newid(),
                        @DISCOUNTAVAILABILITYID,
                        @GROUPSALESID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end
        end

    if @APPLICATIONTYPECODE=2 -- With Code

        exec dbo.USP_DISCOUNT_GETPROMOTIONALCODES_ADDFROMXML @ID, @PROMOTIONALCODES, @CHANGEAGENTID;

    if @DISCOUNTTYPECODE=1 -- With required purchase

        begin
            if @DISCOUNTQUALIFYINGPRICETYPES is not null and @QUALIFYINGITEMTYPECODE = 0
                exec dbo.USP_DISCOUNT_GETQUALIFYINGPRICETYPES_ADDFROMXML @ID, @DISCOUNTQUALIFYINGPRICETYPES, @CHANGEAGENTID;
            if @DISCOUNTPRICETYPES is not null and @DISCOUNTITEMTYPECODE = 0
                exec dbo.USP_DISCOUNT_GETPRICETYPES_ADDFROMXML @ID, @DISCOUNTPRICETYPES, @CHANGEAGENTID;
        end

    if @DISCOUNTTYPECODE=0 and @APPLIESTOCODE=1 -- Standard Discounts applying to items.

    begin
        exec dbo.USP_DISCOUNT_GETPRICETYPES_ADDFROMXML @ID, @DISCOUNTPRICETYPES, @CHANGEAGENTID;
    end

    if @DISCOUNTTYPECODE = 2 -- Group Size Discounts

        exec dbo.USP_DISCOUNT_GETGROUPSIZES_UPDATEFROMXML @ID, @GROUPSIZES, @CHANGEAGENTID, @CURRENTDATE;

    if (@DISCOUNTPROGRAMS is not null) and (@APPLIESTOTICKETS = 1 or @QUALIFYINGITEMTYPECODE = 0)
    begin
        exec dbo.USP_DISCOUNT_ADDDISCOUNTPROGRAMSFROMXML @ID, @DISCOUNTPROGRAMS, @DISCOUNTTYPECODE, @CHANGEAGENTID, @CURRENTDATE, @DISCOUNTITEMTYPECODE, @QUALIFYINGITEMTYPECODE;
    end

    if @DISCOUNTMEMBERS is not null
        exec dbo.USP_DISCOUNT_GETMEMBERSHIPPROGRAMS_ADDFROMXML @ID, @DISCOUNTMEMBERS, @CHANGEAGENTID;

    if @APPLIESTOMERCHANDISE = 1
    begin
        declare @DISCOUNTGROUPID uniqueidentifier;

        if (@DISCOUNTITEMTYPECODE = 1) or (@DISCOUNTTYPECODE = 0)
        begin

            if @APPLIESTOMERCHANDISECODE = 1     -- merchandise departments

                exec dbo.USP_DISCOUNT_MERCHANDISEDEPARTMENTS_ADDFROMXML @ID, @DISCOUNTMERCHANDISEDEPARTMENTS, @DISCOUNTTYPECODE, @CHANGEAGENTID, @CURRENTDATE;
            else if @APPLIESTOMERCHANDISECODE = 2     -- merchandise items

                exec dbo.USP_DISCOUNT_MERCHANDISEITEMS_ADDFROMXML @ID, @DISCOUNTMERCHANDISEITEMS, @DISCOUNTTYPECODE, @CHANGEAGENTID, @CURRENTDATE;
            else if @APPLIESTOMERCHANDISECODE in (0, 5, 6)
            begin
                set @DISCOUNTGROUPID = newid();
                -- insert new items

                insert into dbo.[DISCOUNTGROUP]
                (
                    DISCOUNTID,
                    ID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values
                (
                    @ID,
                    @DISCOUNTGROUPID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )

                insert into dbo.[DISCOUNTGROUPDETAIL]
                (
                    DISCOUNTGROUPID,
                    ID,
                    DISCOUNTEDITEM,
                    DISCOUNTGROUPDETAILAPPLICATIONCODE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values
                (
                    @DISCOUNTGROUPID,
                    newid(),
                    1,
                    @APPLIESTOMERCHANDISECODE, -- all discountable merchandise

                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )
            end
        end
    end
    if ((@DISCOUNTTYPECODE = 1) and (@QUALIFYINGITEMTYPECODE = 1))
    begin
        if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 1     -- merchandise departments

            exec dbo.USP_DISCOUNT_QUALIFYINGMERCHANDISEDEPARTMENTS_ADDFROMXML @ID, @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS, @DISCOUNTTYPECODE, @CHANGEAGENTID, @CURRENTDATE;
        else if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 2     -- merchandise items

            exec dbo.USP_DISCOUNT_QUALIFYINGMERCHANDISEITEMS_ADDFROMXML @ID, @QUALIFYINGDISCOUNTMERCHANDISEITEMS, @CHANGEAGENTID, @CURRENTDATE;
        else if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 4
        begin

            set @DISCOUNTGROUPID = newid();
            -- insert new items

            insert into dbo.[DISCOUNTGROUP]
            (
                DISCOUNTID,
                ID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @ID,
                @DISCOUNTGROUPID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

            insert into dbo.[DISCOUNTGROUPDETAIL]
            (
                DISCOUNTGROUPID,
                ID,
                DISCOUNTEDITEM,
                DISCOUNTGROUPDETAILAPPLICATIONCODE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @DISCOUNTGROUPID,
                newid(),
                0,
                4, -- all discountable merchandise

                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )
        end
    end



    -- LeeCh, 07/19/2009

    -- Add the following code to address the discount constituency change

    declare @CONSTITUENCYCODESXML xml;
    set @CONSTITUENCYCODESXML = (
        select 
            USERDEFINEDCONSTITUENCYDEFINITION.ID as CONSTITUENCYCODEID,
            SYSTEMCONSTITUENCYDEFINITION.ID as CONSTITUENCYSYSTEMNAMEID,
            DISCOUNTCONSTITUENCYCODETABLE.ID,
            DISCOUNTCONSTITUENCYCODETABLE.SEQUENCE
        from
            (select
                T.c.value('(CONSTITUENCYCODEID)[1]','uniqueidentifier') as 'CONSTITUENCYCODEID',
                T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
                T.c.value('(SEQUENCE)[1]','int') as 'SEQUENCE'
            from @DISCOUNTCONSTITUENCYCODES.nodes('/DISCOUNTCONSTITUENCYCODES/ITEM') T(c)
            ) as DISCOUNTCONSTITUENCYCODETABLE

        left join dbo.CONSTITUENCYDEFINITION as USERDEFINEDCONSTITUENCYDEFINITION on USERDEFINEDCONSTITUENCYDEFINITION.ID = DISCOUNTCONSTITUENCYCODETABLE.CONSTITUENCYCODEID and USERDEFINEDCONSTITUENCYDEFINITION.ISSYSTEM = 0
        left join dbo.CONSTITUENCYDEFINITION as SYSTEMCONSTITUENCYDEFINITION on SYSTEMCONSTITUENCYDEFINITION.ID = DISCOUNTCONSTITUENCYCODETABLE.CONSTITUENCYCODEID and SYSTEMCONSTITUENCYDEFINITION.ISSYSTEM = 1

        for xml raw('ITEM'),type,elements,root('DISCOUNTCONSTITUENCYCODES'),BINARY BASE64
    );

    if @CONSTITUENCYCODESXML is not null
        exec dbo.USP_DISCOUNT_GETCONSTITUENCYCODES_ADDFROMXML @ID, @CONSTITUENCYCODESXML, @CHANGEAGENTID;

    -- LeeCh, 07/21/2009

    -- Add the address constraint to discount

    if @ADDRESSSELECTIONID is not null
        begin
            insert into dbo.DISCOUNTADDRESS (
                ID,
                IDSETREGISTERID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            ) values (
                @ID,
                @ADDRESSSELECTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;