USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMBENEFIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMBENEFIT(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
      @CARDFORMAT nvarchar(255) = null output,
    @NAMEFORMAT uniqueidentifier = null output,
    @TSLONG bigint = 0 output,
      @LEVELS xml = null output,
    @BENEFITS xml = null output,
    @DEDUCTIBILITYCODE tinyint = null output,
    @PROGRAMBASEDONCODE tinyint = null output,
    @PROGRAMTYPECODE tinyint = null output,
    @TERMCOUNT integer = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @CARDSREQUIRED bit = null output,
    @REPORTCATALOGID uniqueidentifier = null output,
    @LETTERTEMPLATEID uniqueidentifier = null output,
    @LOWESTTERMVALUE money = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows

    set @DATALOADED = 0
    set @TSLONG = 0

    set @CARDSREQUIRED = 0 --MEMBERSHIPCARDS are no longer required


    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency

    -- can be considered.

    select
        @DATALOADED = 1,
        @TSLONG = MP.TSLONG,
      @CARDFORMAT = MP.CARDFORMAT,
    @DEDUCTIBILITYCODE = MP.DEDUCTIBILITYCODE,
      @NAMEFORMAT = MP.NAMEFORMATID,
    @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE,
    @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
    @TERMCOUNT = MLT.TERMCOUNT,
    @BASECURRENCYID = MP.BASECURRENCYID,
    @REPORTCATALOGID = MP.REPORTCATALOGID,
    @LETTERTEMPLATEID = MP.LETTERTEMPLATEID
  from dbo.MEMBERSHIPPROGRAM MP
  left outer join dbo.REPORTCATALOG RC on MP.CARDFORMAT = RC.NAME
  left outer join dbo.LETTERTEMPLATE lc on mp.CARDFORMAT = lc.NAME and LETTERTEMPLATETYPECODE = 8
  inner join (select MAX(MLT.SEQUENCE)TERMCOUNT, ML.MEMBERSHIPPROGRAMID 
                from 
                    dbo.MEMBERSHIPLEVELTERM MLT
                    inner join MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID 
        where ML.MEMBERSHIPPROGRAMID = @ID
                group by ML.MEMBERSHIPPROGRAMID)MLT on MLT.MEMBERSHIPPROGRAMID = MP.ID 
    where MP.ID = @ID


    set @LEVELS = (
    select top 20
      ML.ID,
      ML.NAME,
      ML.MEMBERSALLOWED,
      ML.RECEIPTAMOUNT as PRICE,
      case @PROGRAMTYPECODE
        -- for lifetime program, get the lowest total price term, lifetime term has zero for number of payments

        when 2 then
        (
          select top 1
            case LIFETIMEPAYMENTOPTIONCODE when 0 then TERM.AMOUNT else TERM.AMOUNT * TERM.LIFETIMENUMBEROFPAYMENTS end as TOTALAMOUNT
          from dbo.MEMBERSHIPLEVELTERM TERM
          where TERM.LEVELID = ML.ID
            and TERM.ISACTIVE = 1
          order by TOTALAMOUNT asc
        )
        else 
        (
          select min(TERM.AMOUNT)
          from dbo.MEMBERSHIPLEVELTERM TERM
          where TERM.LEVELID = ML.ID
            and TERM.ISACTIVE = 1
        )
      end as LEVELAMOUNT
    from 
      dbo.MEMBERSHIPLEVEL ML
    where
      ML.MEMBERSHIPPROGRAMID = @ID
      and ML.ISACTIVE = 1
    order by ML.SEQUENCE
    for xml raw ('ITEM'), type, elements, root('LEVELS'), BINARY BASE64);

  set @BENEFITS = (
      select
                MEMBERSHIPLEVELBENEFIT.ID,
                MEMBERSHIPLEVELBENEFIT.BENEFITID,
                MEMBERSHIPLEVELBENEFIT.NUMBERTOOFFERCODE as NUMBERTOOFFER,
                MEMBERSHIPLEVELBENEFIT.QUANTITY,
                MEMBERSHIPLEVELBENEFIT.FREQUENCYCODE as FREQUENCY, 
                MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID,
                MEMBERSHIPLEVELBENEFIT.USEPERCENT,
                MEMBERSHIPLEVELBENEFIT.VALUEPERCENT,
                BENEFIT.NAME BENEFITNAME,
                MEMBERSHIPLEVELBENEFIT.UNITVALUE,
                MEMBERSHIPLEVEL.BASECURRENCYID,
                MEMBERSHIPLEVELBENEFIT.DETAILS
            from
                MEMBERSHIPLEVELBENEFIT
                inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            where
                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID
            order by
                MEMBERSHIPLEVEL.SEQUENCE
            for xml raw ('ITEM'), type, elements, root('BENEFITS'), BINARY BASE64);

    select
        @LOWESTTERMVALUE = min(AMOUNT)
    from dbo.MEMBERSHIPLEVELTERM
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID

               set @DATALOADED = 1

    return 0;