UFN_SELECTION_CONSTITUENT_PLANNEDGIVERS

CREATE function dbo.UFN_SELECTION_CONSTITUENT_PLANNEDGIVERS()
returns table as
return

  select
    REVENUELIFECYCLE.CONSTITUENTID as ID
  from    
    dbo.REVENUELIFECYCLE
  inner join
    dbo.LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
  where
    LIFECYCLETYPE.NAME = 'Planned giver' and
    --0: Donor Lifecycle

    LIFECYCLETYPE.LIFECYCLEGROUPCODE = 0

  union

  select
    PLANNEDGIFT.CONSTITUENTID as ID
  from
    dbo.PLANNEDGIFT
  where 
    --2: Accepted; 5: Matured

    STATUSCODE in (2, 5) and
    exists
    (
      select
        ID
      from
        dbo.PLANNEDGIFTDESIGNATION
      where
        PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
    );