USP_SEARCHLIST_SPONSORSHIP

CREATE procedure dbo.USP_SEARCHLIST_SPONSORSHIP
(
    --@ID uniqueidentifier ,

    @CHILDSPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @PROJECTSPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @SPONSORID uniqueidentifier = null,
    @LOCATIONID uniqueidentifier = null,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @STARTDATE dbo.UDT_FUZZYDATE = null,
    @ENDDATE dbo.UDT_FUZZYDATE = null,
    @INCLUDEINACTIVE bit = null,
    @INCLUDEPENDING bit = null,
    @MAXROWS smallint = 500,
    @LOOKUPID uniqueidentifier = null,
    @COMMITMENTID nvarchar(100) = null
)
as
    set nocount on

    if @COMMITMENTID is not null 
       set @COMMITMENTID = replace(@COMMITMENTID,'*','%') + '%';


    select top(@MAXROWS)
        S.ID,
        NF.NAME SPONSOR,
        (select NAME from dbo.SPONSORSHIPPROGRAM SPR WHERE SPR.ID = S.SPONSORSHIPPROGRAMID) PROGRAM,
        dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SO.ID) as NAME,
        SO.LOOKUPID,
        dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SO.SPONSORSHIPLOCATIONID) LOCATION,
        S.STARTDATE,S.ENDDATE,
    S.STATUS +
            case when S.STATUSCODE = 2 then
              case when LASTTRANSACTION.ACTIONCODE in(2,3) then
                     ' (' + case LASTTRANSACTION.ACTIONCODE
                                    when 2 then
                                      case S.ID
                                        when LASTTRANSACTION.CONTEXTSPONSORSHIPID then
                                          case LASTREASON.REASONTYPECODE when 11 then 'Expired' else 'Cancelled' end
                                        when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
                                    when 3 then 'Terminated'
                                    end + ')'
                    when LASTTRANSACTION.ACTIONCODE = 7 and S.ID = LASTTRANSACTION.DECLINEDSPONSORSHIPID then
                     ' (Declined)'
                    else '' end
            when S.STATUSCODE = 1 and LASTTRANSACTION.ACTIONCODE = 6 then
             ' (Transfer pending)'
            else '' end,
        SC.LOOKUPID COMMITMENTID
        --convert(varchar(11),S.STARTDATE),convert(varchar(11),S.ENDDATE),S.STATUS

    from 
        dbo.SPONSORSHIP S
        inner join dbo.SPONSORSHIPOPPORTUNITY SO on S.SPONSORSHIPOPPORTUNITYID = SO.ID
        inner join dbo.SPONSORSHIPCOMMITMENT SC ON S.SPONSORSHIPCOMMITMENTID = SC.ID
        left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYL on OPPORTUNITYL.ID = SO.SPONSORSHIPLOCATIONID
        left outer join dbo.SPONSORSHIPLOCATION SELECTEDLOCATION on SELECTEDLOCATION.ID = @LOCATIONID
    left join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = S.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = S.SPONSORSHIPCOMMITMENTID and S.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
    left outer join dbo.SPONSORSHIPREASON as LASTREASON on LASTREASON.ID = LASTTRANSACTION.SPONSORSHIPREASONID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(S.CONSTITUENTID) NF
    where
         (@CHILDSPONSORSHIPOPPORTUNITYID is null or S.SPONSORSHIPOPPORTUNITYID = @CHILDSPONSORSHIPOPPORTUNITYID) 
         and (@PROJECTSPONSORSHIPOPPORTUNITYID is null or S.SPONSORSHIPOPPORTUNITYID = @PROJECTSPONSORSHIPOPPORTUNITYID) 
         and (@SPONSORID is null or S.CONSTITUENTID = @SPONSORID)
         and (@LOCATIONID is null or OPPORTUNITYL.HIERARCHYPATH.IsDescendantOf(SELECTEDLOCATION.HIERARCHYPATH) = 1) 
         and (S.STATUSCODE = 1 
              or (S.STATUSCODE in (1,2) and @INCLUDEINACTIVE=1)
                    or (S.STATUSCODE in (0,1) and @INCLUDEPENDING=1))
         and (@STARTDATE is null or S.STARTDATE between dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@STARTDATE) and dbo.UFN_DATE_LATESTFROMFUZZYDATE(@STARTDATE))
         and (@ENDDATE is null or S.ENDDATE between dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@ENDDATE) and dbo.UFN_DATE_LATESTFROMFUZZYDATE(@ENDDATE))
         and (@SPONSORSHIPPROGRAMID is null or S.SPONSORSHIPPROGRAMID = @SPONSORSHIPPROGRAMID)
         and (@COMMITMENTID is null or SC.LOOKUPID like @COMMITMENTID)
    order by NAME