USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMRECURRINGPRICES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMRECURRINGPRICES
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @ANNUALOPTION bit = null output,
  @SEMIANNUALOPTION bit = null output,
  @QUARTERLYOPTION bit = null output,
  @MONTHLYOPTION bit = null output,
    @RECURRINGPRICES xml = null output
)
as
    set nocount on;

    set @DATALOADED = 1;

  select top 1
        @ANNUALOPTION = case when MLTA.AMOUNT is null then 0 else 1 end,
        @SEMIANNUALOPTION = case when MLTSA.AMOUNT is null then 0 else 1 end,
        @QUARTERLYOPTION = case when MLTQ.AMOUNT is null then 0 else 1 end,
        @MONTHLYOPTION = case when MLTM.AMOUNT is null then 0 else 1 end
  from MEMBERSHIPLEVEL ML 
        INNER JOIN MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
        LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTA on ML.ID = MLTA.LEVELID and MLTA.RECURRINGPAYMENTOPTION = 'Annually' and MLTA.ISACTIVE = 1
        LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTSA on ML.ID = MLTSA.LEVELID and MLTSA.RECURRINGPAYMENTOPTION = 'Semi-annually' and MLTSA.ISACTIVE = 1
        LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTQ on ML.ID = MLTQ.LEVELID and MLTQ.RECURRINGPAYMENTOPTION = 'Quarterly' and MLTQ.ISACTIVE = 1
        LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTM on ML.ID = MLTM.LEVELID and MLTM.RECURRINGPAYMENTOPTION = 'Monthly' and MLTM.ISACTIVE = 1
  where 
        ML.MEMBERSHIPPROGRAMID = @ID
        and ML.ISACTIVE = 1

    set @RECURRINGPRICES = (select distinct
                        ML.MEMBERSHIPPROGRAMID,
                          ML.NAME as LEVEL,
                          MLTA.AMOUNT as ANNUALPRICE,
                          MLTSA.AMOUNT as SEMIANNUALPRICE,
                          MLTSA.AMOUNT * 2 as SEMIANNUALTOTAL,
                          MLTQ.AMOUNT as QUARTERLYPRICE,
                          MLTQ.AMOUNT * 4 as QUARTERLYTOTAL,
                          MLTM.AMOUNT as MONTHLYPRICE,
                          MLTM.AMOUNT * 12 as MONTHLYTOTAL,
                        ML.SEQUENCE,
                        ML.BASECURRENCYID
                      from 
                          MEMBERSHIPLEVEL ML 
                          INNER JOIN MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
                          LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTA on ML.ID = MLTA.LEVELID and MLTA.RECURRINGPAYMENTOPTION = 'Annually' and MLTA.ISACTIVE = 1
                          LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTSA on ML.ID = MLTSA.LEVELID and MLTSA.RECURRINGPAYMENTOPTION = 'Semi-annually' and MLTSA.ISACTIVE = 1
                          LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTQ on ML.ID = MLTQ.LEVELID and MLTQ.RECURRINGPAYMENTOPTION = 'Quarterly' and MLTQ.ISACTIVE = 1
                          LEFT OUTER JOIN MEMBERSHIPLEVELTERM MLTM on ML.ID = MLTM.LEVELID and MLTM.RECURRINGPAYMENTOPTION = 'Monthly' and MLTM.ISACTIVE = 1
                      where 
                          ML.MEMBERSHIPPROGRAMID = @ID and ML.ISACTIVE = 1
                      order by ML.SEQUENCE
                      for xml raw('ITEM'),type,elements,root('RECURRINGPRICES'),BINARY BASE64)

    return 0;