UFN_SPONSORSHIPMAILING_SPONSORSHIPEXISTS

CREATE function dbo.UFN_SPONSORSHIPMAILING_SPONSORSHIPEXISTS
(
@SEGMENTATIONID uniqueidentifier,
@SPONSORSHIPID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier;         
     select 
         @SPONSORSHIPMAILINGTEMPLATEID =[MKTSPONSORSHIPMAILINGPROCESS] .[SPONSORSHIPMAILINGTEMPLATEID]
     from dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION]
     inner join dbo.[MKTSPONSORSHIPMAILINGPROCESSSTATUS] on [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SPONSORSHIPMAILINGPROCESSSTATUSID]
     inner join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID]
     where [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = @SEGMENTATIONID;

     if exists(select SPONSORSHIPID  from dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED where MKTSPONSORSHIPMAILINGTEMPLATEID = @SPONSORSHIPMAILINGTEMPLATEID and SPONSORSHIPID = @SPONSORSHIPID)
     begin
          declare @LASTTRANSACTIONDATE datetime;
          declare @DATESENT datetime;

          select
             @LASTTRANSACTIONDATE = LASTTRANSACTION.DATECHANGED
          from dbo.SPONSORSHIP 
          inner join dbo.[SPONSORSHIPCOMMITMENT] on [SPONSORSHIPCOMMITMENT].[ID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID]
          inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID))
          where SPONSORSHIP.ID = @SPONSORSHIPID

          select 
            @DATESENT = DATESENT
          from dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED where MKTSPONSORSHIPMAILINGTEMPLATEID = @SPONSORSHIPMAILINGTEMPLATEID

         if @DATESENT>@LASTTRANSACTIONDATE
          return 1;
     end
   return 0;
end