V_CONSTITUENT_INTERACTION

CREATE view dbo.V_CONSTITUENT_INTERACTION
as

  select
    INTERACTION.ID,
    INTERACTION.CONSTITUENTID,
    INTERACTION.INTERACTIONTYPECODEID,
    INTERACTION.OBJECTIVE,
    INTERACTION.FUNDRAISERID,
    INTERACTION.DATE,
    INTERACTION.EXPECTEDDATE,
    INTERACTION.ACTUALDATE,
    INTERACTION.COMPLETED,
    INTERACTION.DATEADDED,
    INTERACTION.DATECHANGED,
    INTERACTION.ADDEDBYID,
    INTERACTION.CHANGEDBYID,
    INTERACTION.TSLONG,
    INTERACTION.COMMENT,
    INTERACTION.EVENTID,
    INTERACTION.INTERACTIONSUBCATEGORYID,
    INTERACTION.LOOKUPID,
    INTERACTION.STATUS,
    INTERACTION.PROSPECTPLANID,
    INTERACTION.STARTTIME,
    INTERACTION.ENDTIME,
    INTERACTION.TIMEZONEENTRYID,
    case
      when INTERACTION.PROSPECTPLANID is null then 0
      else 1
    end as INTERACTIONBASETYPE,
    null as STEWARDSHIPSTEPCATEGORYCODEID,
    null as CONTACTPERSONID,
    null as STEWARDSHIPPLANID,
    INTERACTION.STATUSCODE,
    INTERACTION.EXPECTEDSTARTTIME,
    INTERACTION.ACTUALSTARTTIME,
    INTERACTION.EXPECTEDENDTIME,
    INTERACTION.ACTUALENDTIME,
    INTERACTION.LOCATION
  from
    dbo.INTERACTION
  left join
    dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
  left join
    dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
  where
    INTERACTION.ISINTERACTION = 1 and
    INTERACTION.STATUSCODE <> 0

  union all

  select
    STEWARDSHIPPLANSTEP.ID,
    STEWARDSHIPPLAN.CONSTITUENTID,
    STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID,
    STEWARDSHIPPLANSTEP.OBJECTIVE,
    STEWARDSHIPPLANSTEP.CONSTITUENTID as FUNDRAISERID,
    STEWARDSHIPPLANSTEP.DATE,
    STEWARDSHIPPLANSTEP.TARGETDATE as EXPECTEDDATE,
    STEWARDSHIPPLANSTEP.ACTUALDATE,
    STEWARDSHIPPLANSTEP.COMPLETED,
    STEWARDSHIPPLANSTEP.DATEADDED,
    STEWARDSHIPPLANSTEP.DATECHANGED,
    STEWARDSHIPPLANSTEP.ADDEDBYID,
    STEWARDSHIPPLANSTEP.CHANGEDBYID,
    STEWARDSHIPPLANSTEP.TSLONG,
    null as COMMENT,
    STEWARDSHIPPLANSTEP.EVENTID,
    null as INTERACTIONSUBCATEGORYID,
    null as LOOKUPID,
    STEWARDSHIPPLANSTEP.STATUS,
    null as PROSPECTPLANID,
    STEWARDSHIPPLANSTEP.STARTTIME,
    STEWARDSHIPPLANSTEP.ENDTIME,
    STEWARDSHIPPLANSTEP.TIMEZONEENTRYID,
    2 as INTERACTIONBASETYPE,
    STEWARDSHIPPLANSTEP.CATEGORYCODEID as STEWARDSHIPSTEPCATEGORYCODEID,
    STEWARDSHIPPLANSTEP.CONTACTPERSONID,
    STEWARDSHIPPLANSTEP.PLANID as STEWARDSHIPPLANID,
    case STEWARDSHIPPLANSTEP.STATUSCODE
      when 0 then 1
      when 1 then 2
      when 2 then 4
      when 3 then 5
    end as STATUSCODE,
    STEWARDSHIPPLANSTEP.TARGETSTARTTIME as EXPECTEDSTARTTIME,
    STEWARDSHIPPLANSTEP.ACTUALSTARTTIME,
    STEWARDSHIPPLANSTEP.TARGETENDTIME as EXPECTEDENDTIME,
    STEWARDSHIPPLANSTEP.ACTUALENDTIME,
    null as LOCATION
  from
    dbo.STEWARDSHIPPLANSTEP
  inner join
    dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
  where
    STEWARDSHIPPLANSTEP.ISINTERACTION = 1