USP_DATAFORMTEMPLATE_ADD_DISCOUNTBOGO

The save procedure used by the add dataform template "Discount With Required Purchase Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@APPLIESTOCODE	tinyint	IN	Applies to
@APPLICATIONTYPECODE	tinyint	IN	Selected for use
@DISCOUNTTYPECODE	tinyint	IN	Add a discount requiring a purchase
@CALCULATIONTYPECODE	tinyint	IN	Calculation type
@PERCENT	decimal(5, 2)	IN	Value
@AMOUNT	money	IN	Value
@NUMBERTOPURCHASE	int	IN	Quantity at full price
@NUMBERTODISCOUNTTYPECODE	tinyint	IN	Quantity discounted
@NUMBERTODISCOUNT	int	IN	Number to discount
@LIMITDISCOUNTSPERORDER	bit	IN	Limit the number of times this discount may be applied in an order
@NUMBEROFDISCOUNTSPERORDER	int	IN	Uses allowed
@BOGOCALCULATIONTYPECODE	tinyint	IN	Calculation type
@BOGOLIMITDISCOUNTSPERORDER	bit	IN	Limit the number of times this discount may be applied
@BOGONUMBEROFDISCOUNTSPERORDER	int	IN	Uses allowed
@DISCOUNTTICKETSFORCODE	tinyint	IN	Limit discount to
@ALLOWWALKUPSALES	bit	IN	Walk up
@WALKUPID	uniqueidentifier	IN	Walk Up Sales ID
@WALKUPACTIVE	bit	IN	Walk Up Sales Active
@ALLOWADVANCEDSALES	bit	IN	Advance sales
@ADVANCEDID	uniqueidentifier	IN	Advanced Sales ID
@ADVANCEDACTIVE	bit	IN	Advanced Sales Active
@ALLOWONLINESALES	bit	IN	Online
@ONLINEID	uniqueidentifier	IN	Online Sales ID
@ONLINEACTIVE	bit	IN	Online Sales Active
@ALLOWMONDAY	bit	IN	Monday
@ALLOWTUESDAY	bit	IN	Tuesday
@ALLOWWEDNESDAY	bit	IN	Wednesday
@ALLOWTHURSDAY	bit	IN	Thursday
@ALLOWFRIDAY	bit	IN	Friday
@ALLOWSATURDAY	bit	IN	Saturday
@ALLOWSUNDAY	bit	IN	Sunday
@ALLOWDISCOUNTDATEFROM	datetime	IN	From
@ALLOWDISCOUNTDATETO	datetime	IN	To
@ALLOWDISCOUNTTIMEFROM	UDT_HOURMINUTE	IN	From
@ALLOWDISCOUNTTIMETO	UDT_HOURMINUTE	IN	To
@ITEMAMOUNT	money	IN	Value
@ITEMPERCENT	decimal(5, 2)	IN	Value
@PROMOTIONALCODES	xml	IN	Promotion codes
@DISCOUNTQUALIFYINGPRICETYPES	xml	IN	Buy these price types
@DISCOUNTPRICETYPES	xml	IN	Apply discounts to these price types
@DISCOUNTPROGRAMS	xml	IN	Select programs for discount
@DISCOUNTMEMBERS	xml	IN	Restrict discount ot these memberships
@DISCOUNTCONSTITUENCYCODES	xml	IN	Restrict discount to these constituencies
@ADDRESSSELECTIONID	uniqueidentifier	IN	Include
@GROUPSIZES	xml	IN	Group size details
@GROUPSALESID	uniqueidentifier	IN
@GROUPSALESACTIVE	bit	IN
@ALLOWGROUPSALES	bit	IN	Group sales
@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

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DISCOUNTBOGO
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255) = '',
    @APPLIESTOCODE tinyint = 1,
    @APPLICATIONTYPECODE tinyint = 0,
    @DISCOUNTTYPECODE tinyint = 1,
    @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,
    @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,
    @APPLIESTOMERCHANDISECODE tinyint = 5,
    @QUALIFYINGAPPLIESTOMERCHANDISECODE tinyint = 4

)
as
    set nocount on;

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

    begin try
        -- 0 tickets  1 merchandise

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

        begin
            select @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS = null,
                @QUALIFYINGDISCOUNTMERCHANDISEITEMS = null
        end
        else
        begin
            select @DISCOUNTQUALIFYINGPRICETYPES = null
            if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 1 and not exists
                (select 1 
                    from @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS.nodes('/QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(merch)
                )
                raiserror('BBERR_MISSINGQUALIFYINGDEPARTMENTS', 13, 1);            

            if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 2 and not exists
                (select 1 
                    from @QUALIFYINGDISCOUNTMERCHANDISEITEMS.nodes('/QUALIFYINGDISCOUNTMERCHANDISEITEMS/ITEM') T(merch)
                )
                raiserror('BBERR_MISSINGQUALIFYINGITEMS', 13, 1);                        
        end

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

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

        declare @APPLIESTOMERCHANDISE bit = 0
        declare @APPLIESTOTICKETS bit = 0

        if @DISCOUNTITEMTYPECODE = 1
        begin
            set @APPLIESTOMERCHANDISE = 1
            set @APPLIESTOTICKETS = 0
        end
        else
        begin
            set @APPLIESTOMERCHANDISE = 0
            set @APPLIESTOTICKETS = 1
        end

        if (@APPLIESTOMERCHANDISE = 1)
        begin
            if @APPLIESTOMERCHANDISECODE = 1 and not exists 
                (select 1 
                    from @DISCOUNTMERCHANDISEDEPARTMENTS.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(merch)
                )

                raiserror('BBERR_MISSINGDEPARTMENTS', 13, 1);

            if @APPLIESTOMERCHANDISECODE = 2 and not exists
                (select 1 
                    from @DISCOUNTMERCHANDISEITEMS.nodes('/DISCOUNTMERCHANDISEITEMS/ITEM') T(merch)
                )
                raiserror('BBERR_MISSINGITEMS', 13, 1);                
        end

        if (@APPLIESTOTICKETS = 1) and not exists 
                (
                    select 1 
                    from @DISCOUNTPRICETYPES.nodes('/DISCOUNTPRICETYPES/ITEM') T(pricetypes)
                )
                raiserror('BBERR_MISSINGPRICETYPES', 13, 1);                

        exec dbo.USP_DISCOUNT_ADD
                @ID,
                @CHANGEAGENTID,
                @NAME,
                @DESCRIPTION,
                @APPLIESTOCODE,
                @APPLICATIONTYPECODE,
                @DISCOUNTTYPECODE,
                @CALCULATIONTYPECODE,
                @PERCENT,
                @AMOUNT,
                @NUMBERTOPURCHASE,
                @NUMBERTODISCOUNTTYPECODE,
                @NUMBERTODISCOUNT,
                @LIMITDISCOUNTSPERORDER,
                @NUMBEROFDISCOUNTSPERORDER,
                @BOGOCALCULATIONTYPECODE,
                @BOGOLIMITDISCOUNTSPERORDER,
                @BOGONUMBEROFDISCOUNTSPERORDER,                        
                @DISCOUNTTICKETSFORCODE,
                @ALLOWWALKUPSALES,
                @WALKUPID,
                @WALKUPACTIVE,
                @ALLOWADVANCEDSALES,
                @ADVANCEDID,
                @ADVANCEDACTIVE,                        
                @ALLOWONLINESALES,
                @ONLINEID,
                @ONLINEACTIVE,                        
                @ALLOWMONDAY,
                @ALLOWTUESDAY,
                @ALLOWWEDNESDAY,
                @ALLOWTHURSDAY,
                @ALLOWFRIDAY,
                @ALLOWSATURDAY,
                @ALLOWSUNDAY,
                @ALLOWDISCOUNTDATEFROM,
                @ALLOWDISCOUNTDATETO,
                @ALLOWDISCOUNTTIMEFROM,
                @ALLOWDISCOUNTTIMETO,
                @ITEMAMOUNT,
                @ITEMPERCENT,                        
                @PROMOTIONALCODES,
                @DISCOUNTQUALIFYINGPRICETYPES,
                @DISCOUNTPRICETYPES,
                @DISCOUNTPROGRAMS,
                @DISCOUNTMEMBERS,
                @DISCOUNTCONSTITUENCYCODES,
                @ADDRESSSELECTIONID,
                @GROUPSIZES,
                @GROUPSALESID,
                @GROUPSALESACTIVE,
                @ALLOWGROUPSALES,
                @APPLIESTOTICKETS, -- tickets

                @APPLIESTOMERCHANDISE, -- merchandise

                @APPLIESTOMERCHANDISECODE,
                @DISCOUNTMERCHANDISEDEPARTMENTS,
                @DISCOUNTMERCHANDISEITEMS,
                @ITEMMERCHANDISEPERCENT,
                @ITEMMERCHANDISEAMOUNT,
                @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS,
                @QUALIFYINGDISCOUNTMERCHANDISEITEMS,
                @QUALIFYINGITEMTYPECODE,
                @DISCOUNTITEMTYPECODE,
                @DISCOUNTMERCHANDISEFORCODE,
                @QUALIFYINGAPPLIESTOMERCHANDISECODE

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


    return 0;