USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMBENEFITS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMBENEFITS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BENEFITS xml = null output,
  @TAXDEDUCTIBLEAMOUNT money = null output,
  @TAXDEDUCTIBILITYCODE tinyint = null output,
  @PROGRAMTYPECODE tinyint = null output,
  @MEMBERSHIPLEVELAMOUNT money = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;


      set @BENEFITS = (select
                          B.NAME as BENEFIT,
                          NUMBERTOOFFER as QUANTITYDETERMINATION,
                          case
                            when B.USEPERCENT = 1 then 0
                            else MLB.QUANTITY
                          end as QUANTITY,
                          MLB.FREQUENCY,
                          case 
                              when MP.PROGRAMTYPECODE = 0 and MP.PROGRAMBASEDONCODE = 0 and MLT.AMOUNT is not null and B.USEPERCENT = 1 then (MLB.VALUEPERCENT / 100) * MLT.AMOUNT 
                              else MLB.QUANTITY * MLB.UNITVALUE
                          end as FAIRMARKETVALUE,
                        MP.BASECURRENCYID,
                        B.USEPERCENT,
                        B.VALUEPERCENT,
                        MLB.DETAILS
                      FROM dbo.MEMBERSHIPPROGRAM MP
                          inner join dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
                          inner join dbo.MEMBERSHIPLEVELBENEFIT MLB on ML.ID = MLB.MEMBERSHIPLEVELID
                          inner join dbo.BENEFIT B on MLB.BENEFITID = B.ID
                          left outer join (select 
                                            LEVELID, 
                                            AMOUNT 
                                          from MEMBERSHIPLEVELTERM 
                                                      where LEVELID = @ID
                                                      and LEVELID not in (select LEVELID from MEMBERSHIPLEVELTERM where SEQUENCE > 0)
                                                      ) MLT on MLT.LEVELID = ML.ID
                      where 
                          ML.ID = @ID
                        for xml raw ('ITEM'), type, elements, root('BENEFITS'), BINARY BASE64);

  set @TAXDEDUCTIBLEAMOUNT = (SELECT
                                RECEIPTAMOUNT
                              FROM dbo.MEMBERSHIPLEVEL ML
                                WHERE ML.ID = @ID);

  set @MEMBERSHIPLEVELAMOUNT = (select AMOUNT 
                                from MEMBERSHIPLEVELTERM 
                                            where LEVELID = @ID
                                            and LEVELID not in (select LEVELID from MEMBERSHIPLEVELTERM where SEQUENCE > 0))

  select 
    @TAXDEDUCTIBILITYCODE = DEDUCTIBILITYCODE,
    @PROGRAMTYPECODE = PROGRAMTYPECODE,
    @BASECURRENCYID = MP.BASECURRENCYID
  from dbo.MEMBERSHIPPROGRAM MP
  inner join dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
  where ML.ID = @ID