USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLIFETIMEPAYMENTOPTIONS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLIFETIMEPAYMENTOPTIONS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PAYMENTOPTIONS xml = null output,
    @INSTALLMENTPOSTSTATUSCODE tinyint = null output
)
as
    set nocount on;

    -- Form has always hard-coded this. Maintaining behavior backward compatibility

    set @DATALOADED = 1;

    select @INSTALLMENTPOSTSTATUSCODE = MP.INSTALLMENTPOSTSTATUSCODE
    from dbo.MEMBERSHIPPROGRAM MP
    where MP.ID = @ID

    set @PAYMENTOPTIONS = (
        select distinct
            ML.MEMBERSHIPPROGRAMID,
            MLT.LIFETIMEPAYMENTOPTION,
            case when MLT.LIFETIMEPAYMENTOPTIONCODE = 0 then 1 else MLT.LIFETIMENUMBEROFPAYMENTS end as LIFETIMENUMBEROFPAYMENTS,
            MLT.LIFETIMEPAYMENTOPTIONCODE
        from 
            dbo.MEMBERSHIPLEVELTERM MLT
            inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
        where ML.MEMBERSHIPPROGRAMID = @ID and MLT.ISACTIVE = 1
        for xml raw('ITEM'),type,elements,root('PAYMENTOPTIONS'),BINARY BASE64
    );

    return 0;