USP_DATAFORMTEMPLATE_VIEW_CREDITREFUND_MEMBERSHIPCREDITITEM2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITREFUND_MEMBERSHIPCREDITITEM2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @AMOUNTREFUNDED 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,
    @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,
            @AMOUNTREFUNDED = LI.BASEAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join dbo.CREDITITEM_EXT 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.ORGAMOUNT as AMOUNTREFUNDED
            from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
            inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
            where MEMBERSHIPADDON.MEMBERSHIPID = @MEMBERSHIPID
                and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @CREDITID
            for xml raw('ITEM'),type,elements,root('ADDONS'),binary base64
        )

    return 0;