USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLIFETIMEPRICES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLIFETIMEPRICES
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @OPTION1 nvarchar(25) = null output,
  @PAYMENTS1 integer = null output,
  @OPTION2 nvarchar(25) = null output,
  @PAYMENTS2 integer = null output,
  @OPTION3 nvarchar(25) = null output,
  @PAYMENTS3 integer = null output,
  @OPTION4 nvarchar(25) = null output,
  @PAYMENTS4 integer = null output,
  @OPTION5 nvarchar(25) = null output,
  @PAYMENTS5 integer = null output,
  @OPTION6 nvarchar(25) = null output,
  @PAYMENTS6 integer = null output,
  @OPTION7 nvarchar(25) = null output,
  @PAYMENTS7 integer = null output,
  @OPTION8 nvarchar(25) = null output,
  @PAYMENTS8 integer = null output,
  @OPTION9 nvarchar(25) = null output,
  @PAYMENTS9 integer = null output,
  @OPTION10 nvarchar(25) = null output,
  @PAYMENTS10 integer = null output,
    @LIFETIMEPRICES xml = null output
)
as
    set nocount on;

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

    -- 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.

  select 
    @OPTION1 = MAX(CASE 
      WHEN MLT1.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT1.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT1.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT1.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS1 = MAX(MLT1.LIFETIMENUMBEROFPAYMENTS),
    @OPTION2 = MAX(CASE
      WHEN MLT2.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT2.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT2.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT2.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS2 = MAX(MLT2.LIFETIMENUMBEROFPAYMENTS),
    @OPTION3 = MAX(CASE
      WHEN MLT3.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT3.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT3.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT3.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS3 = MAX(MLT3.LIFETIMENUMBEROFPAYMENTS),
    @OPTION4 = MAX(CASE 
      WHEN MLT4.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT4.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT4.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT4.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS4 = MAX(MLT4.LIFETIMENUMBEROFPAYMENTS),
    @OPTION5 = MAX(CASE
      WHEN MLT5.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT5.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT5.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT5.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS5 = MAX(MLT5.LIFETIMENUMBEROFPAYMENTS),
    @OPTION6 = MAX(CASE
      WHEN MLT6.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT6.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT6.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT6.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS6 = MAX(MLT6.LIFETIMENUMBEROFPAYMENTS),
    @OPTION7 = MAX(CASE
      WHEN MLT7.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT7.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT7.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT7.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS7 = MAX(MLT7.LIFETIMENUMBEROFPAYMENTS),
    @OPTION8 = MAX(CASE
      WHEN MLT8.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT8.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT8.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT8.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS8 = MAX(MLT8.LIFETIMENUMBEROFPAYMENTS),
    @OPTION9 = MAX(CASE
      WHEN MLT9.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT9.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT9.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT9.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS9 = MAX(MLT9.LIFETIMENUMBEROFPAYMENTS),
    @OPTION10 = MAX(CASE
      WHEN MLT10.LIFETIMEPAYMENTOPTIONCODE = 1 THEN 'month' 
      WHEN MLT10.LIFETIMEPAYMENTOPTIONCODE = 2 THEN 'quarter'
      WHEN MLT10.LIFETIMEPAYMENTOPTIONCODE = 3 THEN 'year'
      ELSE MLT10.LIFETIMEPAYMENTOPTION
    END),
    @PAYMENTS10 = MAX(MLT10.LIFETIMENUMBEROFPAYMENTS)
  from 
    MEMBERSHIPLEVEL ML
    left outer join MEMBERSHIPLEVELTERM MLT1 on ML.ID = MLT1.LEVELID and MLT1.SEQUENCE = 0 and MLT1.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT2 on ML.ID = MLT2.LEVELID and MLT2.SEQUENCE = 1 and MLT2.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT3 on ML.ID = MLT3.LEVELID and MLT3.SEQUENCE = 2 and MLT3.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT4 on ML.ID = MLT4.LEVELID and MLT4.SEQUENCE = 3 and MLT4.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT5 on ML.ID = MLT5.LEVELID and MLT5.SEQUENCE = 4 and MLT5.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT6 on ML.ID = MLT6.LEVELID and MLT6.SEQUENCE = 5 and MLT6.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT7 on ML.ID = MLT7.LEVELID and MLT7.SEQUENCE = 6 and MLT7.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT8 on ML.ID = MLT8.LEVELID and MLT8.SEQUENCE = 7 and MLT8.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT9 on ML.ID = MLT9.LEVELID and MLT9.SEQUENCE = 8 and MLT9.ISACTIVE = 1
    left outer join MEMBERSHIPLEVELTERM MLT10 on ML.ID = MLT10.LEVELID and MLT10.SEQUENCE = 9 and MLT10.ISACTIVE = 1
  where
    ML.MEMBERSHIPPROGRAMID = @ID
    and ML.ISACTIVE = 1

    set @LIFETIMEPRICES = (                          
                      select distinct 
                        ML.MEMBERSHIPPROGRAMID,
                        ML.NAME as LEVEL,
                        MLT1.AMOUNT as PRICE1,
                        MLT1.AMOUNT * MLT1.LIFETIMENUMBEROFPAYMENTS as TOTAL1,
                        MLT2.AMOUNT as PRICE2,
                        MLT2.AMOUNT * MLT2.LIFETIMENUMBEROFPAYMENTS as TOTAL2,
                        MLT3.AMOUNT as PRICE3,
                        MLT3.AMOUNT * MLT3.LIFETIMENUMBEROFPAYMENTS as TOTAL3,
                        MLT4.AMOUNT as PRICE4,
                        MLT4.AMOUNT * MLT4.LIFETIMENUMBEROFPAYMENTS as TOTAL4,
                        MLT5.AMOUNT as PRICE5,
                        MLT5.AMOUNT * MLT5.LIFETIMENUMBEROFPAYMENTS as TOTAL5,
                        MLT6.AMOUNT as PRICE6,
                        MLT6.AMOUNT * MLT6.LIFETIMENUMBEROFPAYMENTS as TOTAL6,
                        MLT7.AMOUNT as PRICE7,
                        MLT7.AMOUNT * MLT7.LIFETIMENUMBEROFPAYMENTS as TOTAL7,
                        MLT8.AMOUNT as PRICE8,
                        MLT8.AMOUNT * MLT8.LIFETIMENUMBEROFPAYMENTS as TOTAL8,
                        MLT9.AMOUNT as PRICE9,
                        MLT9.AMOUNT * MLT9.LIFETIMENUMBEROFPAYMENTS as TOTAL9,
                        MLT10.AMOUNT as PRICE10,
                        MLT10.AMOUNT * MLT10.LIFETIMENUMBEROFPAYMENTS as TOTAL10,
                        ML.SEQUENCE,
                        ML.BASECURRENCYID
                      from 
                        MEMBERSHIPLEVEL ML
                          left outer join MEMBERSHIPLEVELTERM MLT1 on ML.ID = MLT1.LEVELID and MLT1.SEQUENCE = 0 and MLT1.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT2 on ML.ID = MLT2.LEVELID and MLT2.SEQUENCE = 1 and MLT2.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT3 on ML.ID = MLT3.LEVELID and MLT3.SEQUENCE = 2 and MLT3.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT4 on ML.ID = MLT4.LEVELID and MLT4.SEQUENCE = 3 and MLT4.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT5 on ML.ID = MLT5.LEVELID and MLT5.SEQUENCE = 4 and MLT5.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT6 on ML.ID = MLT6.LEVELID and MLT6.SEQUENCE = 5 and MLT6.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT7 on ML.ID = MLT7.LEVELID and MLT7.SEQUENCE = 6 and MLT7.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT8 on ML.ID = MLT8.LEVELID and MLT8.SEQUENCE = 7 and MLT8.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT9 on ML.ID = MLT9.LEVELID and MLT9.SEQUENCE = 8 and MLT9.ISACTIVE = 1
                          left outer join MEMBERSHIPLEVELTERM MLT10 on ML.ID = MLT10.LEVELID and MLT10.SEQUENCE = 9 and MLT10.ISACTIVE = 1
                      where
                        ML.MEMBERSHIPPROGRAMID = @ID and ML.ISACTIVE = 1
                      order by
                        ML.SEQUENCE
                      for xml raw('ITEM'),type,elements,root('LIFETIMEPRICES'),BINARY BASE64)

    return 0;