V_QUERY_SPONSORSHIPOPPORTUNITYCMS

/*
Generated by Blackbaud AppFx Platform
Date:  4/13/2016 4:58:50 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.158.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPOPPORTUNITYCMS AS



select
  SPONSORSHIPOPPORTUNITY.ID,
  case
    when SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = 1 then
    (
      select NF.NAME
      from SPONSORSHIPOPPORTUNITYCHILD
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID) NF
      where SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIPOPPORTUNITY.ID
    )
    else (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = SPONSORSHIPOPPORTUNITY.ID)
  end NAME,
  SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID, 
  dbo.UFN_SPONSORSHIPOPPORTUNITY_GETLOCATIONFORCMS(SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID) SPONSORSHIPLOCATIONID, 
  'Available' AVAILABILITY,
  SPONSORSHIPOPPORTUNITY.SPONSORSHIPREASONID,
  [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
  [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
  [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
  [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
  SPONSORSHIPOPPORTUNITY.DATEADDED,
  SPONSORSHIPOPPORTUNITY.DATECHANGED,
  SPONSORSHIPOPPORTUNITY.TSLONG,
  case when SPONSORSHIPOPPORTUNITYCHILD.ID is not null then 'Child' else 'Project' end [TYPE],
  SPONSORSHIPOPPORTUNITY.LOOKUPID,
  case when SPONSORSHIPOPPORTUNITYLOCK.SPONSORCOUNT is not null then SPONSORSHIPOPPORTUNITYLOCK.SPONSORCOUNT else 0 end SPONSORCOUNT
from dbo.SPONSORSHIPOPPORTUNITY
  inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
  inner join dbo.SPONSORSHIPOPPORTUNITYLOCK on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIPOPPORTUNITYLOCK.ID
  left join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIPOPPORTUNITYCHILD.ID
  left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
  left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIPOPPORTUNITY.ADDEDBYID
  left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIPOPPORTUNITY.CHANGEDBYID
-- Conditions to restrict to opportunities actually OK to sponsor

where
  SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 1 and
  SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0 and
  SPONSORSHIPOPPORTUNITYLOCK.LOCKED = 0 and
  -- The opportunity should be a part of a group that is part of a program, otherwise child/project cannot be sponsored.

  exists
  (
    select top 1 1
    from dbo.SPONSORSHIPPROGRAM SP
      inner join dbo.SPONSORSHIPOPPORTUNITYGROUP SG on SG.ID = SP.SPONSORSHIPOPPORTUNITYGROUPID
    where
      SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID = SG.ID and
      (
        SP.FILTERLOCATIONCODE = 0 or
        (
          SP.FILTERLOCATIONCODE = 1 and
          exists
          (
            select top 1 1
            from (select T.c.value('(SPONSORSHIPLOCATIONID)[1]','uniqueidentifier') AS 'SPONSORSHIPLOCATIONID' from SP.FILTERLOCATIONS.nodes('/FILTERLOCATIONS/ITEM') T(c)) X
              inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
            where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1
          )
        ) or
        (
          SP.FILTERLOCATIONCODE = 2 and
          not exists
          (
            select top 1 1
            from (select T.c.value('(SPONSORSHIPLOCATIONID)[1]','uniqueidentifier') AS 'SPONSORSHIPLOCATIONID' from SP.FILTERLOCATIONS.nodes('/FILTERLOCATIONS/ITEM') T(c)) X
              inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
            where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1
          )
        )
  )
  );