USP_DATAFORMTEMPLATE_EDIT_DESIGNATIONANDAPPEAL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DESIGNATIONANDAPPEAL (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @DESIGNATIONID uniqueidentifier,
    @APPEALID uniqueidentifier,
  @APPEALID_OLD uniqueidentifier,
  @APPEALDESIGNATIONS xml
)
as

    set nocount on;

  declare @NEW_APPEALDESIGATION xml

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
    update dbo.EVENT 
    set APPEALID = @APPEALID
    where ID = @ID

    if @APPEALID_OLD <> @APPEALID
    begin
      set @APPEALDESIGNATIONS = (select [DESIGNATIONID], [ID], [ISDEFAULT]
                                    from (
                                          select [DESIGNATIONID], [ID], case [ISDEFAULT] when 1 then 0 else [ISDEFAULT] end as [ISDEFAULT]
                                          from dbo.[UFN_APPEAL_GETDESIGNATIONS](@APPEALID) where DESIGNATIONID <> @DESIGNATIONID
                                          union all      
                                          select @DESIGNATIONID as DESIGNATIONID, NEWID() AS ID, 1 AS [ISDEFAULT]
                                      ) A
                                    for xml raw('ITEM'),type,elements,root('APPEALDESIGNATIONS'),BINARY BASE64
                                )    
    end

        exec dbo.USP_APPEAL_GETDESIGNATIONS_UPDATEFROMXML @APPEALID, @APPEALDESIGNATIONS, @CHANGEAGENTID

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

return 0;