USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMMULTITERMPRICES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMMULTITERMPRICES
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @PROGRAMBASEDONCODE tinyint = null output,
  @PROGRAMTYPECODE tinyint = null output,
  @TERM1 nvarchar(50) = null output,
  @TERM2 nvarchar(50) = null output,
  @TERM3 nvarchar(50) = null output,
  @TERM4 nvarchar(50) = null output,
  @TERM5 nvarchar(50) = null output,
  @TERM6 nvarchar(50) = null output,
  @TERM7 nvarchar(50) = null output,
  @TERM8 nvarchar(50) = null output,
  @TERM9 nvarchar(50) = null output,
  @TERM10 nvarchar(50) = null output,
    @MULTITERMPRICES xml = null output
)
as
    set nocount on;

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

    with CTE_MULTIPLETERMS
    as
    (
    select MEMBERSHIPLEVELTERM.LEVELID, MEMBERSHIPLEVELTERM.TERMLENGTHCODE,MEMBERSHIPLEVELTERM.TERMTIMELENGTH, ROW_NUMBER() over (partition by MEMBERSHIPLEVELTERM.LEVELID order by MEMBERSHIPLEVELTERM.SEQUENCE) as SEQUENCE
    from MEMBERSHIPLEVELTERM
    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID and MEMBERSHIPLEVEL.ISACTIVE = 1
    )
    select top 1
    @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE,
    @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
      @TERM1 = case when MLT1.TERMLENGTHCODE = 1 then cast(MLT1.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT1.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM2 = case when MLT2.TERMLENGTHCODE = 1 then cast(MLT2.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT2.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM3 = case when MLT3.TERMLENGTHCODE = 1 then cast(MLT3.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT3.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM4 = case when MLT4.TERMLENGTHCODE = 1 then cast(MLT4.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT4.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM5 = case when MLT5.TERMLENGTHCODE = 1 then cast(MLT5.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT5.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM6 = case when MLT6.TERMLENGTHCODE = 1 then cast(MLT6.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT6.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM7 = case when MLT7.TERMLENGTHCODE = 1 then cast(MLT7.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT7.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM8 = case when MLT8.TERMLENGTHCODE = 1 then cast(MLT8.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT8.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM9 = case when MLT9.TERMLENGTHCODE = 1 then cast(MLT9.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT9.TERMTIMELENGTH as nvarchar(5)) + ' month' end,
      @TERM10 = case when MLT10.TERMLENGTHCODE = 1 then cast(MLT10.TERMTIMELENGTH as nvarchar(5)) + ' year' else cast(MLT10.TERMTIMELENGTH as nvarchar(5)) + ' month' end
  from 
  MEMBERSHIPPROGRAM MP
  inner join MEMBERSHIPLEVEL ML ON MP.ID = ML.MEMBERSHIPPROGRAMID
        left outer join CTE_MULTIPLETERMS MLT1 on ML.ID = MLT1.LEVELID and MLT1.SEQUENCE = 1
      left outer join CTE_MULTIPLETERMS MLT2 on ML.ID = MLT2.LEVELID and MLT2.SEQUENCE = 2
      left outer join CTE_MULTIPLETERMS MLT3 on ML.ID = MLT3.LEVELID and MLT3.SEQUENCE = 3
      left outer join CTE_MULTIPLETERMS MLT4 on ML.ID = MLT4.LEVELID and MLT4.SEQUENCE = 4
      left outer join CTE_MULTIPLETERMS MLT5 on ML.ID = MLT5.LEVELID and MLT5.SEQUENCE = 5
      left outer join CTE_MULTIPLETERMS MLT6 on ML.ID = MLT6.LEVELID and MLT6.SEQUENCE = 6
      left outer join CTE_MULTIPLETERMS MLT7 on ML.ID = MLT7.LEVELID and MLT7.SEQUENCE = 7
      left outer join CTE_MULTIPLETERMS MLT8 on ML.ID = MLT8.LEVELID and MLT8.SEQUENCE = 8
      left outer join CTE_MULTIPLETERMS MLT9 on ML.ID = MLT9.LEVELID and MLT9.SEQUENCE = 9
      left outer join CTE_MULTIPLETERMS MLT10 on ML.ID = MLT10.LEVELID and MLT10.SEQUENCE = 10
  where ML.MEMBERSHIPPROGRAMID = @ID
        and ML.ISACTIVE = 1
  order by ML.SEQUENCE;

    -- 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.
    with CTE_MULTIPLEPRICES
    as
    (
    select MEMBERSHIPLEVELTERM.LEVELID, MEMBERSHIPLEVELTERM.ID, ROW_NUMBER() over (partition by MEMBERSHIPLEVELTERM.LEVELID order by MEMBERSHIPLEVELTERM.SEQUENCE) as SEQUENCE
    from MEMBERSHIPLEVELTERM
    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID and MEMBERSHIPLEVEL.ISACTIVE = 1
    )
    select @MULTITERMPRICES = (
        select distinct
          MP.ID,
            ML.NAME as LEVEL,
          ML.OBTAINLEVELCODE,
            MLT1.LOWAMOUNT as LOWAMOUNT1,
          MLT1.AMOUNT as AMOUNT1,
            MLT2.LOWAMOUNT as LOWAMOUNT2,
            MLT2.AMOUNT as AMOUNT2,
            MLT3.LOWAMOUNT as LOWAMOUNT3,
            MLT3.AMOUNT as AMOUNT3,
            MLT4.LOWAMOUNT as LOWAMOUNT4,
          MLT4.AMOUNT as AMOUNT4,
            MLT5.LOWAMOUNT as LOWAMOUNT5,
          MLT5.AMOUNT as AMOUNT5,
            MLT6.LOWAMOUNT as LOWAMOUNT6,
            MLT6.AMOUNT as AMOUNT6,
            MLT7.LOWAMOUNT as LOWAMOUNT7,
            MLT7.AMOUNT as AMOUNT7,
            MLT8.LOWAMOUNT as LOWAMOUNT8,
            MLT8.AMOUNT as AMOUNT8,
            MLT9.LOWAMOUNT as LOWAMOUNT9,
            MLT9.AMOUNT as AMOUNT9,
            MLT10.LOWAMOUNT as LOWAMOUNT10,
            MLT10.AMOUNT as AMOUNT10,
          ML.SEQUENCE,
          MP.BASECURRENCYID
        from 
            MEMBERSHIPPROGRAM MP
            inner join MEMBERSHIPLEVEL ML ON MP.ID = ML.MEMBERSHIPPROGRAMID
            left outer join CTE_MULTIPLEPRICES CTE_MP1 on CTE_MP1.LEVELID = ML.ID and CTE_MP1.SEQUENCE = 1
            left outer join MEMBERSHIPLEVELTERM MLT1 on CTE_MP1.ID = MLT1.ID and MLT1.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP2 on CTE_MP2.LEVELID = ML.ID and CTE_MP2.SEQUENCE = 2
            left outer join MEMBERSHIPLEVELTERM MLT2 on CTE_MP2.ID = MLT2.ID and MLT2.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP3 on CTE_MP3.LEVELID = ML.ID and CTE_MP3.SEQUENCE = 3
            left outer join MEMBERSHIPLEVELTERM MLT3 on CTE_MP3.ID = MLT3.ID and MLT3.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP4 on CTE_MP4.LEVELID = ML.ID and CTE_MP4.SEQUENCE = 4
            left outer join MEMBERSHIPLEVELTERM MLT4 on CTE_MP4.ID = MLT4.ID and MLT4.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP5 on CTE_MP5.LEVELID = ML.ID and CTE_MP5.SEQUENCE = 5
            left outer join MEMBERSHIPLEVELTERM MLT5 on CTE_MP5.ID = MLT5.ID and MLT5.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP6 on CTE_MP6.LEVELID = ML.ID and CTE_MP6.SEQUENCE = 6
            left outer join MEMBERSHIPLEVELTERM MLT6 on CTE_MP6.ID = MLT6.ID and MLT6.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP7 on CTE_MP7.LEVELID = ML.ID and CTE_MP7.SEQUENCE = 7
            left outer join MEMBERSHIPLEVELTERM MLT7 on CTE_MP7.ID = MLT7.ID and MLT7.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP8 on CTE_MP8.LEVELID = ML.ID and CTE_MP8.SEQUENCE = 8
            left outer join MEMBERSHIPLEVELTERM MLT8 on CTE_MP8.ID = MLT8.ID and MLT8.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP9 on CTE_MP9.LEVELID = ML.ID and CTE_MP9.SEQUENCE = 9
            left outer join MEMBERSHIPLEVELTERM MLT9 on CTE_MP9.ID = MLT9.ID and MLT9.ISACTIVE = 1
            left outer join CTE_MULTIPLEPRICES CTE_MP10 on CTE_MP10.LEVELID = ML.ID and CTE_MP10.SEQUENCE = 10
            left outer join MEMBERSHIPLEVELTERM MLT10 on CTE_MP10.ID = MLT10.ID and MLT10.ISACTIVE = 1
        where ML.MEMBERSHIPPROGRAMID = @ID and ML.ISACTIVE = 1
        order by ML.SEQUENCE
          for xml raw('ITEM'),type,elements,root('MULTITERMPRICES'),BINARY BASE64
    )
    return 0;