USP_DATAFORMTEMPLATE_EDIT_OPPORTUNITYREASONS

The save procedure used by the edit dataform template "Sponsorship Opportunity Pending Reasons Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@REASONLIST	xml	IN	Reason
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
Definition

Copy


    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_OPPORTUNITYREASONS (
        @ID uniqueidentifier,
        @REASONLIST xml,
        @CHANGEAGENTID uniqueidentifier
    )
    as 
    begin
        set nocount on;

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

        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();    

        -- build a temporary table containing the values from the XML

        declare @TempTbl table (
         ID uniqueidentifier,
         SPONSORSHIPREASONID uniqueidentifier)

        begin try
          insert into @TempTbl select 
              ID,
              SPONSORSHIPREASONID
          from dbo.UFN_SPONOSRSHIPOPPORTUNITY_REASONS_FROMITEMLISTXML(@REASONLIST)

          update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

        -- delete the items that don't exist in the XML

          delete from dbo.SPONSORSHIPOPPORTUNITYREASON
                where SPONSORSHIPOPPORTUNITYREASON.ID not in (select ID from @TempTbl)
                and SPONSORSHIPOPPORTUNITYREASON.SPONSORSHIPOPPORTUNITYID = @ID      

          -- update the items that exist in the XML table and the db

          update dbo.SPONSORSHIPOPPORTUNITYREASON
                set SPONSORSHIPOPPORTUNITYREASON.ID=temp.ID,
                SPONSORSHIPOPPORTUNITYREASON.SPONSORSHIPREASONID=temp.SPONSORSHIPREASONID,
                SPONSORSHIPOPPORTUNITYREASON.CHANGEDBYID = @CHANGEAGENTID,
                SPONSORSHIPOPPORTUNITYREASON.DATECHANGED = @CURRENTDATE

            from dbo.SPONSORSHIPOPPORTUNITYREASON inner join @TempTbl as temp on SPONSORSHIPOPPORTUNITYREASON.ID = temp.ID
            where SPONSORSHIPOPPORTUNITYREASON.SPONSORSHIPREASONID<>temp.SPONSORSHIPREASONID

            -- insert new items

            insert into dbo.SPONSORSHIPOPPORTUNITYREASON 
                (ID,
          SPONSORSHIPOPPORTUNITYID,
                SPONSORSHIPREASONID,                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select ID,
          @ID,
                SPONSORSHIPREASONID, 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @TempTbl as temp
            where not exists (select ID from dbo.SPONSORSHIPOPPORTUNITYREASON as SPR where SPR.ID = temp.ID)

            --- update child eligibility  

            declare @REASONCOUNT tinyint
            select @REASONCOUNT = count(*) from @TempTbl

            if @REASONCOUNT = 0 
              begin
                exec.USP_RECORDOPERATION_SPONSORSHIPOPPORTUNITYMAKEELIGIBLE @ID, @CHANGEAGENTID
              end
        else
          begin
          exec.USP_RECORDOPERATION_SPONSORSHIPOPPORTUNITYMAKEPENDING @ID, @CHANGEAGENTID
          end

        exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @ID, 0, @CHANGEAGENTID
        end try

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

        return 0;    

    end