USP_DATAFORMTEMPLATE_VIEW_SALESORDERITEM2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@DESCRIPTION	nvarchar(100)	INOUT
@QUANTITY	int	INOUT
@PRICE	money	INOUT
@TYPECODE	tinyint	INOUT
@TOTAL	money	INOUT
@FEES	money	INOUT
@NAME	nvarchar(100)	INOUT
@PRICETYPE	nvarchar(100)	INOUT
@STARTDATE	datetime	INOUT
@STARTTIME	nvarchar(4)	INOUT
@LOCATION	nvarchar(100)	INOUT
@MEMBERSHIPEXPIRATIONDATE	datetime	INOUT
@MEMBERSHIPLEVELTYPECODE	nvarchar(100)	INOUT
@MEMBERSHIPNUMBEROFCHILDREN	smallint	INOUT
@MEMBERSHIPGIVENBY	nvarchar(154)	INOUT
@MEMBERSHIPMEMBERS	xml	INOUT
@PROMOTIONS	money	INOUT
@MEMBERSHIPADDONS	xml	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERITEM2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @DESCRIPTION nvarchar(100) = null output,
    @QUANTITY int = null output,
    @PRICE money = null output,
    @TYPECODE tinyint = null output,
    @TOTAL money = null output,
    @FEES money = null output,
    @NAME nvarchar(100) = null output,
    @PRICETYPE nvarchar(100) = null output,
    @STARTDATE datetime = null output,
    @STARTTIME nvarchar(4) = null output,
    @LOCATION nvarchar(100) = null output,
    @MEMBERSHIPEXPIRATIONDATE datetime = null output,
    @MEMBERSHIPLEVELTYPECODE nvarchar(100) = null output,
    @MEMBERSHIPNUMBEROFCHILDREN smallint = null output,
    @MEMBERSHIPGIVENBY nvarchar(154) = null output,
    @MEMBERSHIPMEMBERS xml = null output,
    @PROMOTIONS money = null output,
    @MEMBERSHIPADDONS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @DESCRIPTION = DESCRIPTION,
        @QUANTITY = QUANTITY,
        @PRICE = PRICE,
        @TYPECODE = TYPECODE,
        @TOTAL = TOTAL
    from dbo.SALESORDERITEM
    where ID = @ID

    if @TYPECODE = 0  -- Tickets

    begin
        select @NAME = coalesce(EVENT.NAME, PROGRAM.NAME, ''),
            @PRICETYPE = PRICETYPECODE.DESCRIPTION,
            @STARTDATE = EVENT.STARTDATE,
            @STARTTIME = EVENT.STARTTIME,
            @LOCATION = dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID)
        from dbo.SALESORDERITEMTICKET
        inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
        left join dbo.EVENT on SALESORDERITEMTICKET.EVENTID = EVENT.ID
        left join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
        where SALESORDERITEMTICKET.ID = @ID;

        declare @FEEDOLLARAMOUNT money = 0;

        select
            @FEEDOLLARAMOUNT = sum(TOTAL)
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        where SALESORDERITEMFEE.SALESORDERITEMID = @ID;

        set @FEES = coalesce(@FEEDOLLARAMOUNT, 0.0);

        set @TOTAL = @TOTAL + @FEES;
    end
    else if @TYPECODE = 1  -- Membership

    begin
        select @PROMOTIONS = sum(AMOUNT)
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
        where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = @ID;

        select
            @MEMBERSHIPEXPIRATIONDATE = SALESORDERITEMMEMBERSHIP.EXPIRATIONDATE,
            @MEMBERSHIPLEVELTYPECODE = dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION(SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID),
            @MEMBERSHIPNUMBEROFCHILDREN = SALESORDERITEMMEMBERSHIP.NUMBEROFCHILDREN,
            @MEMBERSHIPGIVENBY = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDERITEMMEMBERSHIP.GIVENBYID),
            @MEMBERSHIPMEMBERS = (
                select dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDERITEMMEMBER.CONSTITUENTID) [MEMBERNAME]
                from dbo.SALESORDERITEMMEMBER
                where SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
                for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
            )
        from dbo.SALESORDERITEMMEMBERSHIP
        where ID = @ID;

        -- Add-ons

        select
            @MEMBERSHIPADDONS = (
                select
                    ADDON.NAME as ADDONNAME,
                    SALESORDERITEM.QUANTITY,
                    SALESORDERITEM.PRICE,
                    SALESORDERITEM.TOTAL
                from dbo.SALESORDERITEM
                inner join dbo.SALESORDERITEMMEMBERSHIPADDON SOIMA on SALESORDERITEM.ID = SOIMA.ID
                inner join dbo.ADDON on SOIMA.ADDONID = ADDON.ID
                where SALESORDERITEM.TYPECODE = 16
                    and SOIMA.SALESORDERITEMMEMBERSHIPID = @ID
                for xml raw('ITEM'),type,elements,root('MEMBERSHIPADDONS'),binary base64
            );
    end

    return 0;