USP_REPORT_SPONSORSHIPTRANSFEREXCEPTIONS

create procedure dbo.USP_REPORT_SPONSORSHIPTRANSFEREXCEPTIONS
(
    @ID nvarchar(36)
)
with execute as owner
as
    set nocount on;

    declare @TABLENAME nvarchar(128)
    declare @SQL nvarchar(4000);

    select @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
    from dbo.BUSINESSPROCESSSTATUS 
    inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
    where BUSINESSPROCESSSTATUS.ID = @ID
    and BUSINESSPROCESSOUTPUT.TABLEKEY = 'Exception';

    if @TABLENAME is null or OBJECT_ID(@TABLENAME) is null
        raiserror('Business process exception table could not be found.  The process might not have completed successfully. ',13,1)

    begin try
        set @SQL = 'select ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) CONSTITUENTID,
               NF.NAME SPONSORNAME,
               CONSTITUENT.LOOKUPID CONSTITUENTLOOKUPID,    
               ''http://www.blackbaud.com/FROMSPONSORSHIPID?FROMSPONSORSHIPID='' + CONVERT(nvarchar(36),FROMSPONSORSHIP.ID) FROMSPONSORSHIPID,
               dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) FROMOPPORTUNITY,
                  ''http://www.blackbaud.com/FROMOPPORTUNITYID?FROMOPPORTUNITYID='' + CONVERT(nvarchar(36),FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) FROMOPPORTUNITYID,
               FROMOPPORTUNITY.LOOKUPID FROMOPPORTUNITYLOOKUPID,             
               case EXCEPTIONTAB.ERRORMESSAGE
                when ''BBERR_NOTCOMPLETINGPENDINGTRANSFERS'' then N''This sponsorship already has a transfer pending.''
                when ''BBERR_PENDINGTRANSFERFOUND'' then N''This sponsorship is pending - only active sponsorships can be transferred.''
                when ''BBERR_INACTIVESPONSORSHIPFOUND'' then N''This sponsorship is inactive - only active sponsorships can be transferred.''
                when ''BBERR_NOMATCHINGOPPORTUNITY'' then N''No matching sponsorship opportunity was found.''
                when ''BBERR_NODESIGNATIONFOUND'' then N''A designation could not be found for the new sponsorship''''s opportunity.''
                when ''BBERR_PROGRAMOPPORTUNITYNOTCONSISTENT'' then N''The selected opportunity is not consistent with the program of this sponsorship''
                when ''BBERR_AFFILIATETONONAFFILIATE'' then N''An affiliate sponsorship can not be transferred to a non-affiliate program.''
                when ''BBERR_NONAFFILIATETOAFFILIATE'' then N''A non-affiliate sponsorship can not be transferred to an affiliate program.''        
                when ''BBERR_SOLESPONSORSHIPTONONSOLE'' then N''A sole sponsorship can not be transferred to a program that does not allow sole sponsorships.''           
                when ''BBERR_CHILDOPPORTUNITYTOOYOUNG'' then N''The selected child is too young for sponsorship.''
                when ''BBERR_CHILDOPPORTUNITYTOOOLD'' then N''The selected child is too old for sponsorship.''           
                else N''Unexpected exception: ''+EXCEPTIONTAB.ERRORMESSAGE
               end ERRORMESSAGE
        from dbo.' + @TABLENAME + ' EXCEPTIONTAB 
        inner join dbo.SPONSORSHIP FROMSPONSORSHIP on FROMSPONSORSHIP.ID = EXCEPTIONTAB.FROMSPONSORSHIPID
        inner join dbo.SPONSORSHIPOPPORTUNITY FROMOPPORTUNITY on FROMOPPORTUNITY.ID = FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = FROMSPONSORSHIP.CONSTITUENTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF';

        exec sp_executesql @SQL;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

return 0;