USP_SPONSORSHIP_UPDATEDESIGNATION

create procedure dbo.USP_SPONSORSHIP_UPDATEDESIGNATION (
    @REVENUESPLITID uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    -- determine new designation

    declare @NEWDESIGNATIONID uniqueidentifier = null

    begin try
        set @NEWDESIGNATIONID = dbo.UFN_SPONSORSHIPOPPORTUNITY_GETDESIGNATION(@SPONSORSHIPOPPORTUNITYID)

        if @NEWDESIGNATIONID is null
          raiserror('BBERR_NODESIGNATIONFOUND',13,1)

        declare @REVENUEID uniqueidentifier
        declare @REVENUEAMOUNT money
        declare @SPLITS xml
        declare @ORIGINALDESIGNATIONID uniqueidentifier

        select @REVENUEID = REVENUEID
        from dbo.REVENUESPLIT
        where ID = @REVENUESPLITID;

        exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUE_SPLIT
            @ID = @REVENUEID,
            @REVENUEAMOUNT = @REVENUEAMOUNT output,
            @SPLITS = @SPLITS output

        set @ORIGINALDESIGNATIONID = @SPLITS.value('data(SPLITS/ITEM[ID=sql:variable("@REVENUESPLITID")]/DESIGNATIONID[1]/text())[1]','uniqueidentifier')

        if @ORIGINALDESIGNATIONID <> @NEWDESIGNATIONID
        begin
            set @SPLITS.modify('replace value of (SPLITS/ITEM/DESIGNATIONID[1]/text())[1] with sql:variable("@NEWDESIGNATIONID")')

            exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUE_SPLIT
                @REVENUEID,
                @CHANGEAGENTID,
                @REVENUEAMOUNT,
                @SPLITS
        end
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end