V_QUERY_BBDW_SPONSORSHIP

create view BBDW.[V_QUERY_BBDW_SPONSORSHIP] as 

select 
  s.[SPONSORSHIPDIMID],
  s.[SPONSORSHIPSYSTEMID],
  s.[SPONSORSHIPCOMMITMENTLOOKUPID],  
  s.[SPONSORSHIPPROGRAMNAME],
  s.[SPONSORSHIPPROGRAMAMOUNT],
  s.[SPONSORSHIPPROGRAMISACTIVE],
  s.[ISAFFILIATESPONSORSHIPPROGRAM],
  s.[AFFILIATECONSTITUENTDIMID],
  s.[OPPORTUNITYSPONSORSHIPLOCATIONDIMID],
  s.[SPONSORSHIPOPPORTUNITYGROUPNAME],
  s.[SPONSORSHIPOPPORTUNITYGROUPISACTIVE],
  s.[SPONSORSPEROPPORTUNITY],
  s.[SPONSORSHIPOPPORTUNITY],
  s.[SPONSORSHIPOPPORTUNITYTYPE],
  s.[SPONSORSHIPOPPORTUNITYELIGIBILITY],
  s.[SPONSORSHIPOPPORTUNITYAVAILABILITY],
  s.[SPONSORSHIPOPPORTUNITYLOOKUPID],
  s.[SPONSORSHIPOPPORTUNITYSTATUS],
  s.[LASTDOCUMENTATIONDATEDIMID],
  s.[LASTDOCUMENTATIONDATE],
  s.[SPONSORSHIPOPPORTUNITYPROJECTCATEGORY],
  s.[SPONSORSHIPOPPORTUNITYPROJECTCATEGORYCODESYSTEMID],

  s.[SPONSORSHIPSTATUS],
  s.[SPONSORSHIPSTARTDATEDIMID],
  s.[SPONSORSHIPSTARTDATE],
  s.[SPONSORSHIPENDDATEDIMID],
  s.[SPONSORSHIPENDDATE],
  s.[SPONSORSHIPPLANNEDENDDATEDIMID],
  s.[SPONSORSHIPPLANNEDENDDATE],
  s.[EXPIRATIONSPONSORSHIPREASON],
  s.[CHILDGENDER],
  s.[ISHIVPOSITIVE],
  s.[HASCONDITION],
  s.[ISORPHANED],
  s.[AGERANGE],
  s.[ISSOLESPONSORSHIP],
  s.[ISGIFTSPONSORSHIP],  
  case s.[SPONSORSHIPSTATUSCODE] 
    when 1 then
      case st.[SPONSORSHIPTRANSACTIONACTIONCODE]
        when 6 then 'Active (Transfer pending)'
        when 5 then 'Active (Reassigned)'
        else 'Active' end
    when 2 then
      case st.[SPONSORSHIPTRANSACTIONACTIONCODE]
        when 1 then 'Transferred'
        when 2 then
          case s.[SPONSORSHIPDIMID]
            when st.[CONTEXTSPONSORSHIPDIMID] then
              case sr.[SPONSORSHIPREASONTYPECODE] 
                when 11 then 'Expired' 
                else 'Cancelled' 
              end
            when st.[DECLINEDSPONSORSHIPDIMID] then 'Declined' end
        when 3 then 'Terminated'
        when 5 then 'Reassigned'
        when 7 then 
          case s.[SPONSORSHIPDIMID]
            when st.[CONTEXTSPONSORSHIPDIMID] then 'Transferred'
            when st.[DECLINEDSPONSORSHIPDIMID] then 'Declined' end
        when 8 then 'Transferred' 
      end
    else s.[SPONSORSHIPSTATUS] 
  end [STATUS],
  case when s.[SPONSORSHIPSTATUSCODE] = 2 or (s.[SPONSORSHIPSTATUSCODE] = 1 and st.[SPONSORSHIPTRANSACTIONACTIONCODE] = 6) then sr.[SPONSORSHIPREASON] end [LASTTRANSACTIONREASON],
  st.[SPONSORSHIPTRANSACTIONACTION] [LASTTRANSACTIONACTION],
  s.[SPONSORCONSTITUENTDIMID],
  s.[SPONSORSHIPOPPORTUNITYCHILDDIMID],
  s.[SPONSORSHIPOPPORTUNITYPROJECTDIMID],
  s.[SPONSORSHIPLOCATIONDIMID],
  s.[FINANCIALSPONSORCONSTITUENTDIMID], 
  s.[FINANCIALTRANSACTIONFACTID],
  s.[ACTIVESPONSORS],
  c.[CURRENCYSYSTEMID]
from [BBDW].[DIM_SPONSORSHIP] s
left join [BBDW].[FACT_SPONSORSHIPTRANSACTION] st on s.[SPONSORSHIPCOMMITMENTSYSTEMID] = st.[SPONSORSHIPCOMMITMENTSYSTEMID] and st.[ISLASTSPONSORSHIPTRANSACTION] = 1
left join [BBDW].[DIM_SPONSORSHIPREASON] sr on st.[SPONSORSHIPREASONDIMID] = sr.[SPONSORSHIPREASONDIMID] and st.[SPONSORSHIPREASONDIMID] <> 0
outer apply BBDW.[DIM_CURRENCY] c 
where c.[ISORGANIZATIONCURRENCY] = 1 and s.[SPONSORSHIPDIMID] > 0