USP_DATAFORMTEMPLATE_EDITLOAD_DISCOUNTDETAIL

The load 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 used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@NUMBERTOPURCHASE	int	INOUT	Quantity at full price
@NUMBERTODISCOUNTTYPECODE	tinyint	INOUT	Quantity discounted
@NUMBERTODISCOUNT	int	INOUT	Number to discount
@CALCULATIONTYPECODE	tinyint	INOUT	Calculation type
@DISCOUNTTICKETSFORCODE	tinyint	INOUT	Limit discount to
@LIMITDISCOUNTSPERORDER	bit	INOUT	Limit the number of times this discount may be applied
@NUMBEROFDISCOUNTSPERORDER	int	INOUT	Uses allowed
@DISCOUNTQUALIFYINGPRICETYPES	xml	INOUT	Buy these price types
@DISCOUNTPRICETYPES	xml	INOUT	Apply discounts to these price types
@DISCOUNTMERCHANDISEDEPARTMENTS	xml	INOUT	Departments
@DISCOUNTMERCHANDISEITEMS	xml	INOUT	Items
@ITEMMERCHANDISEPERCENT	decimal(5, 2)	INOUT	Value
@ITEMMERCHANDISEAMOUNT	money	INOUT	Value
@QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS	xml	INOUT	Departments
@QUALIFYINGDISCOUNTMERCHANDISEITEMS	xml	INOUT	Items
@QUALIFYINGITEMTYPECODE	tinyint	INOUT	Item type
@DISCOUNTITEMTYPECODE	tinyint	INOUT	Item type
@DISCOUNTMERCHANDISEFORCODE	tinyint	INOUT	Limit discount to
@APPLIESTOMERCHANDISECODE	tinyint	INOUT	Applies to
@QUALIFYINGAPPLIESTOMERCHANDISECODE	tinyint	INOUT	Triggered by
@DISCOUNTPROGRAMS	xml	INOUT	Select programs for discount

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DISCOUNTDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @NUMBERTOPURCHASE int = null output,
    @NUMBERTODISCOUNTTYPECODE tinyint = null output,
    @NUMBERTODISCOUNT int = null output,
    @CALCULATIONTYPECODE tinyint = null output,
    @DISCOUNTTICKETSFORCODE tinyint = null output,
    @LIMITDISCOUNTSPERORDER bit = null output,
    @NUMBEROFDISCOUNTSPERORDER int = null output,
    @DISCOUNTQUALIFYINGPRICETYPES xml = null output,
    @DISCOUNTPRICETYPES xml = null output,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml = null output,
    @DISCOUNTMERCHANDISEITEMS xml = null output,
    @ITEMMERCHANDISEPERCENT decimal(5, 2) = null output,
    @ITEMMERCHANDISEAMOUNT money = null output,
    @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS xml = null output,
    @QUALIFYINGDISCOUNTMERCHANDISEITEMS xml = null output,
    @QUALIFYINGITEMTYPECODE tinyint = null output,
    @DISCOUNTITEMTYPECODE tinyint = null output,
    @DISCOUNTMERCHANDISEFORCODE tinyint = null output,
    @APPLIESTOMERCHANDISECODE tinyint = null output,
    @QUALIFYINGAPPLIESTOMERCHANDISECODE tinyint = null output,
    @DISCOUNTPROGRAMS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @APPLIESTOCODE tinyint;

    select
        @DATALOADED = 1,
        @NUMBERTOPURCHASE = NUMBERTOPURCHASE,
        @NUMBERTODISCOUNTTYPECODE = NUMBERTODISCOUNTTYPECODE,
        @NUMBERTODISCOUNT = NUMBERTODISCOUNT,
        @CALCULATIONTYPECODE = CALCULATIONTYPECODE,
        @DISCOUNTTICKETSFORCODE = DISCOUNTTICKETSFORCODE,
        @LIMITDISCOUNTSPERORDER = LIMITDISCOUNTSPERORDER,
        @NUMBEROFDISCOUNTSPERORDER = NUMBEROFDISCOUNTSPERORDER,
        @DISCOUNTITEMTYPECODE = DISCOUNTITEMTYPECODE,
        @QUALIFYINGITEMTYPECODE = QUALIFYINGITEMTYPECODE,
        @ITEMMERCHANDISEPERCENT = MERCHANDISEPERCENT,
        @ITEMMERCHANDISEAMOUNT = MERCHANDISEAMOUNT,
        @DISCOUNTMERCHANDISEFORCODE = DISCOUNTMERCHANDISEFORCODE
    from
        dbo.DISCOUNT

    where
        ID = @ID;

    if @DATALOADED = 1
    begin
        if @QUALIFYINGITEMTYPECODE = 0 -- tickets qualifying

        begin
            set @DISCOUNTQUALIFYINGPRICETYPES = dbo.UFN_DISCOUNT_GETQUALIFYINGPRICETYPES_TOITEMLISTXML(@ID);
            set @QUALIFYINGAPPLIESTOMERCHANDISECODE = 4;
        end
        else -- merchandise qualifying

        begin
            select
                @QUALIFYINGAPPLIESTOMERCHANDISECODE = DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE
            from dbo.DISCOUNTGROUPDETAIL
            inner join dbo.DISCOUNTGROUP
                on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
            where DISCOUNTGROUP.DISCOUNTID = @ID
                and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
                and DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE <> 3

            if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 1
            begin
                set @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS = (
                    select
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID,
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID,
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.SEQUENCE
                    from dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
                    inner join dbo.DISCOUNTGROUPDETAIL
                        on dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID = DISCOUNTGROUPDETAIL.ID
                    inner join dbo.DISCOUNTGROUP
                        on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                    where DISCOUNTGROUP.DISCOUNTID = @ID
                        and DISCOUNTEDITEM = 0
                    for xml raw('ITEM'),type,elements,root('QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS'),binary base64
                );
            end
            else if @QUALIFYINGAPPLIESTOMERCHANDISECODE = 2
            begin
                set @QUALIFYINGDISCOUNTMERCHANDISEITEMS = (
                    select
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.ID,
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID,
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.SEQUENCE
                    from dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM
                    inner join dbo.DISCOUNTGROUPDETAIL
                        on dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM.ID = DISCOUNTGROUPDETAIL.ID
                    inner join dbo.DISCOUNTGROUP
                        on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                    where DISCOUNTGROUP.DISCOUNTID = @ID
                        and DISCOUNTEDITEM = 0
                    for xml raw('ITEM'),type,elements,root('QUALIFYINGDISCOUNTMERCHANDISEITEMS'),binary base64
                );

            end

        end

        if @DISCOUNTITEMTYPECODE = 0
        begin
            set @DISCOUNTPRICETYPES = dbo.UFN_DISCOUNT_GETPRICETYPES_TOITEMLISTXML(@ID);
            set @APPLIESTOMERCHANDISECODE = 5
        end
        else -- merchandise is discounted

        begin
            select
                @APPLIESTOMERCHANDISECODE = DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE
            from dbo.DISCOUNTGROUPDETAIL
            inner join dbo.DISCOUNTGROUP
                on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
            where DISCOUNTGROUP.DISCOUNTID = @ID
                and DISCOUNTEDITEM = 1
                and DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE <> 3

            if @APPLIESTOMERCHANDISECODE = 1
            begin
                set @DISCOUNTMERCHANDISEDEPARTMENTS = (
                    select
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID,
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID,
                        DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.SEQUENCE
                    from dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
                    inner join dbo.DISCOUNTGROUPDETAIL
                        on dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID = DISCOUNTGROUPDETAIL.ID
                    inner join dbo.DISCOUNTGROUP
                        on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                    where DISCOUNTGROUP.DISCOUNTID = @ID
                        and DISCOUNTEDITEM = 1
                    for xml raw('ITEM'),type,elements,root('DISCOUNTMERCHANDISEDEPARTMENTS'),binary base64
                );
            end
            else if @APPLIESTOMERCHANDISECODE = 2
            begin
                set @DISCOUNTMERCHANDISEITEMS = (
                    select
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.ID,
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID,
                        DISCOUNTGROUPDETAILMERCHANDISEITEM.SEQUENCE
                    from dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM
                    inner join dbo.DISCOUNTGROUPDETAIL
                        on dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM.ID = DISCOUNTGROUPDETAIL.ID
                    inner join dbo.DISCOUNTGROUP
                        on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                    where DISCOUNTGROUP.DISCOUNTID = @ID
                        and DISCOUNTEDITEM = 1
                    for xml raw('ITEM'),type,elements,root('DISCOUNTMERCHANDISEITEMS'),binary base64
                );
            end
        end

        if @DISCOUNTITEMTYPECODE = 0 or @QUALIFYINGITEMTYPECODE = 0
            set @DISCOUNTPROGRAMS =
                (
                    select distinct
                        DISCOUNTGROUPID,
                        PROGRAMID
                    from dbo.UFN_DISCOUNT_GETDISCOUNTEDPROGRAMS(@ID)
                    for xml raw('ITEM'),type,elements,root('DISCOUNTPROGRAMS'),binary base64
                );
    end

    return 0;