USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMBENEFIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMBENEFIT (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CARDFORMAT nvarchar(255),
  @NAMEFORMAT uniqueidentifier,
  @LEVELS xml,
  @BENEFITS xml,
  @DEDUCTIBILITYCODE tinyint,
  @PROGRAMBASEDONCODE tinyint,
  @PROGRAMTYPECODE tinyint,
  @TERMCOUNT integer,
  @REPORTCATALOGID uniqueidentifier,
  @LETTERTEMPLATEID uniqueidentifier
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  declare @CARDFORMATTEXT nvarchar(255)
  set @CARDFORMATTEXT = (select NAME from dbo.REPORTCATALOG where ID=@REPORTCATALOGID)

  begin try
        -- handle updating the data


        -- If not tax deductible, delete all associated contribution designations

        if @DEDUCTIBILITYCODE = 2
        begin
            update dbo.MEMBERSHIPPROGRAM
            set
                DUESTREATEDASCONTRIBUTION = 0
            where
                ID = @ID

            delete from
                dbo.MEMBERSHIPPROGRAMDESIGNATION
            where
                MEMBERSHIPPROGRAMID = @ID

            delete from dbo.MEMBERSHIPLEVELDESIGNATION
                from dbo.MEMBERSHIPLEVELDESIGNATION
                inner join MEMBERSHIPLEVEL on MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            where
                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID
        end

        update dbo.MEMBERSHIPPROGRAM set 
      DEDUCTIBILITYCODE = @DEDUCTIBILITYCODE,
      CARDFORMAT = ISNULL(@CARDFORMATTEXT, ''),
      NAMEFORMATID = @NAMEFORMAT,
      REPORTCATALOGID = @REPORTCATALOGID,
      LETTERTEMPLATEID = @LETTERTEMPLATEID
    where
      ID = @ID

    update dbo.MEMBERSHIPLEVEL set
      RECEIPTAMOUNT = T.c.value('(PRICE)[1]','money')
    from
      @LEVELS.nodes('/LEVELS/ITEM') T(c)
    where
      MEMBERSHIPLEVEL.ID = T.c.value('(ID)[1]','uniqueidentifier')      


    delete from 
      dbo.MEMBERSHIPLEVELBENEFIT 
    where 
      ID in (    
              select 
                MLB.ID
              from
                dbo.MEMBERSHIPLEVELBENEFIT MLB
                inner join dbo.MEMBERSHIPLEVEL ML on MLB.MEMBERSHIPLEVELID = ML.ID
              where 
                ML.MEMBERSHIPPROGRAMID = @ID
                and MLB.ID not in (
                                    select 
                                      T.c.value('(ID)[1]','uniqueidentifier')             
                                    from
                                      @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
                                    where
                                      T.c.value('(ID)[1]','uniqueidentifier')  is not null
                                  )
             )

    --Save benefits

    insert into dbo.MEMBERSHIPLEVELBENEFIT
    (
        ID, MEMBERSHIPLEVELID, BENEFITID,
        QUANTITY, UNITVALUE, DETAILS,
        ADDEDBYID, CHANGEDBYID, DATEADDED,
        DATECHANGED, USEPERCENT, VALUEPERCENT,
        BASECURRENCYID, FREQUENCYCODE, NUMBERTOOFFERCODE
    )
    select
        newid(), T.c.value('(MEMBERSHIPLEVELID)[1]','uniqueidentifier'), T.c.value('(BENEFITID)[1]','uniqueidentifier'),
        T.c.value('(QUANTITY)[1]','int'), T.c.value('(UNITVALUE)[1]','money'), T.c.value('(DETAILS)[1]','nvarchar(255)'),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE,
        @CURRENTDATE, T.c.value('(USEPERCENT)[1]','bit'), T.c.value('(VALUEPERCENT)[1]','decimal'),
        BENEFIT.BASECURRENCYID, T.c.value('(FREQUENCY)[1]','tinyint'), T.c.value('(NUMBERTOOFFER)[1]','tinyint')
    from
        @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
        inner join dbo.BENEFIT on BENEFIT.ID = T.c.value('(BENEFITID)[1]','uniqueidentifier')
    where
        T.c.value('(ID)[1]','uniqueidentifier') is null



    update dbo.MEMBERSHIPLEVELBENEFIT
    set
      BENEFITID = T.c.value('(BENEFITID)[1]','uniqueidentifier'),
      QUANTITY = T.c.value('(QUANTITY)[1]','int'),
      UNITVALUE = T.c.value('(UNITVALUE)[1]','money'), 
      DETAILS = T.c.value('(DETAILS)[1]','nvarchar(255)'),
      DATECHANGED = @CURRENTDATE, 
      USEPERCENT =  T.c.value('(USEPERCENT)[1]','bit'),
      VALUEPERCENT = T.c.value('(VALUEPERCENT)[1]','decimal'), 
      BASECURRENCYID = BENEFIT.BASECURRENCYID,
      FREQUENCYCODE = T.c.value('(FREQUENCY)[1]','tinyint'),
      NUMBERTOOFFERCODE = T.c.value('(NUMBERTOOFFER)[1]','tinyint')
    from
      @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
      inner join dbo.BENEFIT on BENEFIT.ID = T.c.value('(BENEFITID)[1]','uniqueidentifier')
    where
      MEMBERSHIPLEVELBENEFIT.ID = T.c.value('(ID)[1]','uniqueidentifier')      


    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch



return 0;