USP_DATAFORMTEMPLATE_VIEW_CREDITREFUND_MEMBERSHIPCREDITITEM

The load procedure used by the view dataform template "Credit Refund Membership Credit Item View 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.
@PRICE	money	INOUT	Price
@MEMBERSHIPEXPIRATIONDATE	datetime	INOUT	Expires on
@MEMBERSHIPLEVELTYPECODE	nvarchar(100)	INOUT	Type
@MEMBERSHIPNUMBEROFCHILDREN	smallint	INOUT	Children
@MEMBERSHIPGIVENBY	nvarchar(154)	INOUT	Given by
@MEMBERSHIPMEMBERS	xml	INOUT	Members
@PROMOTIONS	money	INOUT	Promotions
@ADDONS	xml	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITREFUND_MEMBERSHIPCREDITITEM
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PRICE money = 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,
    @ADDONS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @MEMBERSHIPID uniqueidentifier = null;

    declare @CREDITID uniqueidentifier;
    select @CREDITID = CREDITID from dbo.CREDITITEM_EXT where CREDITITEM_EXT.ID = @ID;

    declare @TYPECODE integer;
    select @TYPECODE = TYPECODE from dbo.CREDITITEM_EXT where ID = @ID;

    if @TYPECODE = 1 begin
        select
            @DATALOADED = 1,
            @PRICE = LI.QUANTITY * LI.UNITVALUE,
            @PROMOTIONS = EXT.DISCOUNTS
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        where LI.ID = @ID;

        select
            @MEMBERSHIPID = CREDITITEMMEMBERSHIP.MEMBERSHIPID,
            @MEMBERSHIPEXPIRATIONDATE = [CREDITITEMMEMBERSHIP].[EXPIRATIONDATE],
            @MEMBERSHIPLEVELTYPECODE = dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION([CREDITITEMMEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID]),
            @MEMBERSHIPNUMBEROFCHILDREN = [CREDITITEMMEMBERSHIP].[NUMBEROFCHILDREN],
            @MEMBERSHIPGIVENBY = dbo.UFN_CONSTITUENT_BUILDNAME([CREDITITEMMEMBERSHIP].[GIVENBYID]),
            @MEMBERSHIPMEMBERS = [CREDITITEMMEMBERSHIP].[MEMBERS]
        from dbo.[CREDITITEMMEMBERSHIP]
        inner join dbo.[MEMBERSHIP]
            on [CREDITITEMMEMBERSHIP].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
        where [CREDITITEMMEMBERSHIP].[ID] = @ID;
    end
    else if @TYPECODE = 16 begin
        select
            @MEMBERSHIPID = MEMBERSHIPADDON.MEMBERSHIPID,
            @DATALOADED = 1
        from dbo.FINANCIALTRANSACTIONLINEITEM
        inner join MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
        where FINANCIALTRANSACTIONLINEITEM.ID = @ID
    end

    select
        @ADDONS = (
            select
                ADDON.NAME,
                FINANCIALTRANSACTIONLINEITEM.QUANTITY,
                FINANCIALTRANSACTIONLINEITEM.UNITVALUE as PRICE,
                FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as TOTAL
            from dbo.CREDITITEM_EXT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
            inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
            inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
            where MEMBERSHIPADDON.MEMBERSHIPID = @MEMBERSHIPID
                and CREDITITEM_EXT.CREDITID = @CREDITID
            for xml raw('ITEM'),type,elements,root('ADDONS'),binary base64
        )

    return 0;