USP_SPONSORSHIPSETTINGS_GETNEWDEFAULTS

CREATE procedure dbo.USP_SPONSORSHIPSETTINGS_GETNEWDEFAULTS (
@TYPE nvarchar(7),
@SPONSORSHIPOPPORTUNITYID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null
)
as
--only called when there are pending reasons.

begin
    set nocount on;

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

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    declare @REASONLIST xml = null
    select
        @REASONLIST = 
        case @TYPE
        when 'PROJECT' then PROJECTPENDINGREASONS
        when 'CHILD' then CHILDPENDINGREASONS
        end
        from
           SPONSORSHIPINFO
        order by DATEADDED

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

        declare @TempTbl table (
         ID uniqueidentifier,
         SPONSORSHIPREASONID uniqueidentifier)

        begin try
              if @TYPE  = 'CHILD'
                begin
                    insert into @TempTbl(SPONSORSHIPREASONID) 
                        select 
                            SPONSORSHIPREASONID
                        from 
                            dbo.UFN_SPONSORSHIPSETTINGS_CHILDPENDINGREASONS_FROMITEMLISTXML(@REASONLIST)
                end
                if @TYPE = 'PROJECT'
                begin
                    insert into @TempTbl(SPONSORSHIPREASONID) 
                        select 
                            SPONSORSHIPREASONID
                        from 
                            dbo.UFN_SPONSORSHIPSETTINGS_PROJECTPENDINGREASONS_FROMITEMLISTXML(@REASONLIST)
                end
          update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

                    -- insert new items

            insert into dbo.SPONSORSHIPOPPORTUNITYREASON 
                (ID,
          SPONSORSHIPOPPORTUNITYID,
                SPONSORSHIPREASONID,                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select ID,
          @SPONSORSHIPOPPORTUNITYID,
                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 SO pending status 

            declare @REASONCOUNT tinyint
            select @REASONCOUNT = count(*) from @TempTbl
        exec.USP_RECORDOPERATION_SPONSORSHIPOPPORTUNITYMAKEPENDING @SPONSORSHIPOPPORTUNITYID, @CHANGEAGENTID
        end try

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

        return 0;    
end