USP_DATAFORMTEMPLATE_EDIT_DISCOUNTDETAIL_2

The save procedure used by the edit dataform template "Discount Details Edit Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NUMBERTOPURCHASE	int	IN	Quantity at full price
@NUMBERTODISCOUNTTYPECODE	tinyint	IN	Quantity discounted
@NUMBERTODISCOUNT	int	IN	Number to discount
@CALCULATIONTYPECODE	tinyint	IN	Calculation type
@DISCOUNTTICKETSFORCODE	tinyint	IN	Limit discount to
@LIMITDISCOUNTSPERORDER	bit	IN	Limit the number of times this discount may be applied
@NUMBEROFDISCOUNTSPERORDER	int	IN	Uses allowed
@DISCOUNTQUALIFYINGPRICETYPES	xml	IN	Buy these price types
@DISCOUNTPRICETYPES	xml	IN	Apply discounts to these price types
@DISCOUNTMERCHANDISEDEPARTMENTS	xml	IN	Departments
@DISCOUNTMERCHANDISEITEMS	xml	IN	Items
@ITEMMERCHANDISEPERCENT	decimal(5, 2)	IN	Value
@ITEMMERCHANDISEAMOUNT	money	IN	Value
@QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS	xml	IN	Departments
@QUALIFYINGDISCOUNTMERCHANDISEITEMS	xml	IN	Items
@QUALIFYINGITEMTYPECODE	tinyint	IN	Item type
@DISCOUNTITEMTYPECODE	tinyint	IN	Item type
@DISCOUNTMERCHANDISEFORCODE	tinyint	IN	Limit discount to
@APPLIESTOMERCHANDISECODE	tinyint	IN	Applies to
@QUALIFYINGAPPLIESTOMERCHANDISECODE	tinyint	IN	Triggered by
@DISCOUNTPROGRAMS	xml	IN	Select programs for discount
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DISCOUNTDETAIL_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NUMBERTOPURCHASE int,
    @NUMBERTODISCOUNTTYPECODE tinyint,
    @NUMBERTODISCOUNT int,
    @CALCULATIONTYPECODE tinyint,
    @DISCOUNTTICKETSFORCODE tinyint,
    @LIMITDISCOUNTSPERORDER bit,
    @NUMBEROFDISCOUNTSPERORDER int,
    @DISCOUNTQUALIFYINGPRICETYPES xml,
    @DISCOUNTPRICETYPES xml,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml,
    @DISCOUNTMERCHANDISEITEMS xml,
    @ITEMMERCHANDISEPERCENT decimal(5, 2),
    @ITEMMERCHANDISEAMOUNT money,
    @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS xml,
    @QUALIFYINGDISCOUNTMERCHANDISEITEMS xml,
    @QUALIFYINGITEMTYPECODE tinyint,
    @DISCOUNTITEMTYPECODE tinyint, -- 0 = tickets, 1 = merchandise

    @DISCOUNTMERCHANDISEFORCODE tinyint,
    @APPLIESTOMERCHANDISECODE tinyint,
    @QUALIFYINGAPPLIESTOMERCHANDISECODE tinyint,
    @DISCOUNTPROGRAMS xml
)
as
    set nocount on;

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

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

    begin try
        if @QUALIFYINGITEMTYPECODE = 0
        -- clear out qualifying merchandise discount xmls

        begin
            select @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS = null,
                @QUALIFYINGDISCOUNTMERCHANDISEITEMS = null
        end
        else
            select @DISCOUNTQUALIFYINGPRICETYPES = null

        if @DISCOUNTITEMTYPECODE = 0
        -- clear out discount merchandise xmls

        begin
            select @DISCOUNTMERCHANDISEDEPARTMENTS = null,
                @DISCOUNTMERCHANDISEITEMS = null
        end
        else
            select @DISCOUNTPRICETYPES = null

        declare @APPLIESTOMERCHANDISE bit = case @DISCOUNTITEMTYPECODE when 1 then 1 else 0 end;
        declare @APPLIESTOTICKETS bit = case @DISCOUNTITEMTYPECODE when 0 then 1 else 0 end;

        if @LIMITDISCOUNTSPERORDER = 0
            set @NUMBEROFDISCOUNTSPERORDER = 1

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

        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


        -- now that we passed validation, clone the discount and update only the new version.

        declare @CLONEID uniqueidentifier;
        exec dbo.USP_DISCOUNT_CLONE @ID, @CLONEID output;

        -- Update all xml with new ID's so the originals do not get overwritten (and the changes lost).


        if @DISCOUNTQUALIFYINGPRICETYPES is not null
        begin
            set @DISCOUNTQUALIFYINGPRICETYPES = (
                select newid() as ID, PRICETYPECODEID, SEQUENCE
                from dbo.UFN_DISCOUNT_GETQUALIFYINGPRICETYPES_FROMITEMLISTXML(@DISCOUNTQUALIFYINGPRICETYPES)
                for xml path('ITEM'),type,elements,root('DISCOUNTQUALIFYINGPRICETYPES'),BINARY BASE64
            )
        end

        if @DISCOUNTPRICETYPES is not null
        begin
            set @DISCOUNTPRICETYPES = (
                select newid() as ID, PRICETYPECODEID, SEQUENCE, AMOUNT, [PERCENT]
                from dbo.UFN_DISCOUNT_GETPRICETYPES_FROMITEMLISTXML(@DISCOUNTPRICETYPES)
                order by SEQUENCE
                for xml path('ITEM'),type,elements,root('DISCOUNTPRICETYPES'),BINARY BASE64
            )
        end

        if @DISCOUNTPROGRAMS is not null
        begin
            set @DISCOUNTPROGRAMS = (
                select newid() as DISCOUNTGROUPID, DISCOUNTITEMID, QUALIFYINGITEMID, PROGRAMID
                from dbo.UFN_DISCOUNT_GETDISCOUNTPROGRAMS_FROMITEMLISTXML(@DISCOUNTPROGRAMS)
                for xml path('ITEM'),type,elements,root('DISCOUNTPROGRAMS'),BINARY BASE64
            )
        end

        if @DISCOUNTMERCHANDISEDEPARTMENTS is not null
        begin
            set @DISCOUNTMERCHANDISEDEPARTMENTS = (
                select
                    newid() as ID,
                    newid() as DISCOUNTGROUPID,
                    T.c.value('(MERCHANDISEDEPARTMENTID)[1]','uniqueidentifier') as MERCHANDISEDEPARTMENTID,
                    T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
                from @DISCOUNTMERCHANDISEDEPARTMENTS.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(c)
                for xml path('ITEM'),type,elements,root('DISCOUNTMERCHANDISEDEPARTMENTS'),BINARY BASE64
            )
        end

        if @DISCOUNTMERCHANDISEITEMS is not null
        begin
            set @DISCOUNTMERCHANDISEITEMS = (
                select
                    newid() as ID,
                    newid() as DISCOUNTGROUPID,
                    T.c.value('(MERCHANDISEITEMID)[1]','uniqueidentifier') as MERCHANDISEITEMID,
                    T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
                from @DISCOUNTMERCHANDISEITEMS.nodes('/DISCOUNTMERCHANDISEITEMS/ITEM') T(c)
                for xml path('ITEM'),type,elements,root('DISCOUNTMERCHANDISEITEMS'),BINARY BASE64
            )
        end

        if @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS is not null
        begin
            set @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS = (
                select
                    newid() as ID,
                    newid() as DISCOUNTGROUPID,
                    T.c.value('(MERCHANDISEDEPARTMENTID)[1]','uniqueidentifier') as MERCHANDISEDEPARTMENTID,
                    T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
                from @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS.nodes('/QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(c)
                for xml path('ITEM'),type,elements,root('QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS'),BINARY BASE64
            )
        end

        if @QUALIFYINGDISCOUNTMERCHANDISEITEMS is not null
        begin
            set @QUALIFYINGDISCOUNTMERCHANDISEITEMS = (
                select
                    newid() as ID,
                    newid() as DISCOUNTGROUPID,
                    T.c.value('(MERCHANDISEITEMID)[1]','uniqueidentifier') as MERCHANDISEITEMID,
                    T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
                from @QUALIFYINGDISCOUNTMERCHANDISEITEMS.nodes('/QUALIFYINGDISCOUNTMERCHANDISEITEMS/ITEM') T(c)
                for xml path('ITEM'),type,elements,root('QUALIFYINGDISCOUNTMERCHANDISEITEMS'),BINARY BASE64
            )
        end


        update dbo.DISCOUNT set
            NUMBERTOPURCHASE = @NUMBERTOPURCHASE,
            NUMBERTODISCOUNTTYPECODE = @NUMBERTODISCOUNTTYPECODE,
            NUMBERTODISCOUNT = @NUMBERTODISCOUNT,
            CALCULATIONTYPECODE = @CALCULATIONTYPECODE,
            DISCOUNTTICKETSFORCODE = @DISCOUNTTICKETSFORCODE,
            LIMITDISCOUNTSPERORDER = @LIMITDISCOUNTSPERORDER,
            NUMBEROFDISCOUNTSPERORDER = @NUMBEROFDISCOUNTSPERORDER,
            DISCOUNTITEMTYPECODE = @DISCOUNTITEMTYPECODE,
            QUALIFYINGITEMTYPECODE = @QUALIFYINGITEMTYPECODE,
            MERCHANDISEPERCENT = @ITEMMERCHANDISEPERCENT,
            MERCHANDISEAMOUNT = @ITEMMERCHANDISEAMOUNT,
            DISCOUNTMERCHANDISEFORCODE = @DISCOUNTMERCHANDISEFORCODE,
            APPLIESTOTICKETS = @APPLIESTOTICKETS,
            APPLIESTOMERCHANDISE = @APPLIESTOMERCHANDISE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @CLONEID;


        exec dbo.USP_DISCOUNT_GETQUALIFYINGPRICETYPES_UPDATEFROMXML @CLONEID, @DISCOUNTQUALIFYINGPRICETYPES, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_DISCOUNT_GETPRICETYPES_UPDATEFROMXML @CLONEID, @DISCOUNTPRICETYPES, @CHANGEAGENTID, @CURRENTDATE;

        declare @contextCache varbinary(128);
        /* 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.DISCOUNTGROUP
        where DISCOUNTGROUP.ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUPDETAIL
                inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 3
                    and DISCOUNTGROUP.DISCOUNTID = @CLONEID
            )

        exec dbo.USP_DISCOUNT_ADDDISCOUNTPROGRAMSFROMXML @CLONEID, @DISCOUNTPROGRAMS, 1, @CHANGEAGENTID, @CURRENTDATE, @DISCOUNTITEMTYPECODE, @QUALIFYINGITEMTYPECODE

        if @DISCOUNTITEMTYPECODE = 0
            -- delete all merchandise discounted items

            delete from dbo.DISCOUNTGROUP
            where ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUP
                inner join dbo.DISCOUNTGROUPDETAIL
                    on DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = DISCOUNTGROUP.ID
                where DISCOUNTGROUP.DISCOUNTID = @CLONEID
                    and DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (0, 1, 2, 5, 6)
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1
            )
        else
            exec dbo.USP_DISCOUNT_UPDATEMERCHANDISE @CLONEID, @CHANGEAGENTID, @CURRENTDATE, @APPLIESTOMERCHANDISECODE, @DISCOUNTMERCHANDISEDEPARTMENTS, @DISCOUNTMERCHANDISEITEMS

        if @QUALIFYINGITEMTYPECODE = 0
            -- delete all merchandise qualifying items

            delete from dbo.DISCOUNTGROUP
            where ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUP
                inner join dbo.DISCOUNTGROUPDETAIL
                    on DISCOUNTGROUPDETAIL.DISCOUNTGROUPID = DISCOUNTGROUP.ID
                where DISCOUNTGROUP.DISCOUNTID = @CLONEID
                    and DISCOUNTGROUPDETAILAPPLICATIONCODE in (1, 2, 4)
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
            )
        else
            exec dbo.USP_DISCOUNT_UPDATEQUALIFYINGMERCHANDISE @CLONEID, @CHANGEAGENTID, @CURRENTDATE, @QUALIFYINGAPPLIESTOMERCHANDISECODE, @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS, @QUALIFYINGDISCOUNTMERCHANDISEITEMS

        /* reset CONTEXT_INFO to previous value */
        if not @contextCache is null
            set CONTEXT_INFO @contextCache

    end try

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

    return 0;