USP_REPORT_SPONSORSHIPGIFTSTATUS

create procedure dbo.USP_REPORT_SPONSORSHIPGIFTSTATUS(
@LOCATIONID uniqueidentifier = null,
@PROGRAMID uniqueidentifier = null,
@GROUPBY nvarchar(20)=null)
with execute as owner
as
    set nocount on;
    declare @SQL nvarchar(4000);
    declare @PARAMDEF nvarchar(200);
    begin try
        set @PARAMDEF = N'@LOCATIONID uniqueidentifier,@PROGRAMID uniqueidentifier'
        set @SQL = N'select  ''http://www.blackbaud.com?CONSTITUENTID=''+CONVERT(nvarchar(36),c.ID) CONSTITUENTID,c.NAME,
        ''http://www.blackbaud.com?SPONSORSHIPOPPORTUNITYID=''+CONVERT(nvarchar(36),so.ID) SPONSORSHIPOPPORTUNITYID,
    case sg.SPONSORSHIPOPPORTUNITYTYPECODE 
        when 1 then (select NAME from dbo.SPONSORSHIPOPPORTUNITYCHILD soc where soc.ID = so.ID)
        when 2 then (select NAME from dbo.SPONSORSHIPOPPORTUNITYPROJECT soj where soj.ID = so.ID)
        end OPPORTUNITY,
       (select NAME from dbo.SPONSORSHIPPROGRAM p where p.ID = s.SPONSORSHIPPROGRAMID) PROGRAM,
        dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT(r.id,null) PASTDUE,
        sl2.NAME LOCATION,sl.HIERARCHYPATH,sl2.HIERARCHYPATH
        from SPONSORSHIPLOCATION sl 
        inner join dbo.SPONSORSHIPLOCATION sl2 on sl2.HIERARCHYPATH.IsDescendantOf(sl.HIERARCHYPATH) = 1
        inner join dbo.SPONSORSHIPOPPORTUNITY so on so.SPONSORSHIPLOCATIONID = sl2.ID
        inner join dbo.SPONSORSHIP s on s.SPONSORSHIPOPPORTUNITYID = so.ID
        inner join dbo.CONSTITUENT c on s.CONSTITUENTID = c.ID
        inner join dbo.SPONSORSHIPOPPORTUNITYGROUP sg on sg.ID = so.SPONSORSHIPOPPORTUNITYGROUPID
        inner join dbo.REVENUESPLIT rs on rs.ID = s.REVENUESPLITID
        inner join dbo.REVENUE r on r.ID = rs.REVENUEID
        where dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT(r.id,null) > 0.00 and 
        ((@LOCATIONID is null and sl.HIERARCHYPATH = sl2.HIERARCHYPATH) or  
        sl.ID =@LOCATIONID) and
        (@PROGRAMID is null or s.SPONSORSHIPPROGRAMID = @PROGRAMID)'

        exec sp_executesql @SQL,@PARAMDEF,@LOCATIONID=@LOCATIONID,@PROGRAMID=@PROGRAMID
    end try
        begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;