v_FACT_RECURRINGGIFTINSTALLMENT

-- Added recurring gift installment balance
CREATE view [BBDW].[v_FACT_RECURRINGGIFTINSTALLMENT] as
  select 
    rgi.[RECURRINGGIFTINSTALLMENTFACTID], 
    rgi.[REVENUESCHEDULEDIMID], 
    rgi.[REVENUESCHEDULESTARTDATEDIMID], 
    rgi.[REVENUESCHEDULEENDDATEDIMID], 
    rgi.[RECURRINGGIFTINSTALLMENTDATEDIMID], 
    rgi.[RECURRINGGIFTINSTALLMENTAMOUNT], 
    rgi.[RECURRINGGIFTINSTALLMENTBALANCE], 
    rgi.[BASECURRENCYDIMID],
    rgi.[BASERECURRINGGIFTINSTALLMENTAMOUNT],     
    rgi.[PASTDUEDATEDIMID],
    rgi.[PASTDUEDATE],
    rgi.[RECURRINGGIFTINSTALLMENTSTATUSCODE],
    rgi.[RECURRINGGIFTINSTALLMENTSTATUS],    
    rgi.[RECURRINGGIFTFLAGDIMID], 
    cast(rgi.[ISFIRSTINSTALLMENT] as int) as [ISFIRSTINSTALLMENT], 
    cast(rgi.[ISLASTINSTALLMENT] as int) as [ISLASTINSTALLMENT], 
    cast(rgi.[ISMISSEDINSTALLMENT] as int) as [ISMISSEDINSTALLMENT], 
    cast(rgi.[ISFUTUREINSTALLMENT] as int) as [ISFUTUREINSTALLMENT],     
    r.[FINANCIALTRANSACTIONLINEITEMFACTID] as [REVENUEFACTID], 
    r.[FINANCIALTRANSACTIONSEQUENCEID] as [REVENUESEQUENCEID], 
    r.[REVENUECODEDIMID],
    r.[REVENUEFLAGDIMID], 
    r.[REVENUECHANNELDIMID], 
    r.[FINANCIALTRANSACTIONDATEDIMID] as [REVENUEDATEDIMID], 
    r.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [POSTDATEDIMID], 
    r.[CONSTITUENTDIMID], 
    r.[CONSTITUENTSYSTEMID], 
    r.[MARKETINGSEGMENTDIMID], 
    r.[MARKETINGSOURCECODEDIMID],    
    r.[MARKETINGCONSTITUENTFACTID], 
    r.[GLREVENUECATEGORYDIMID], 
    c.[CONSTITUENTINFORMATIONDIMID], 
    c.[AGE] as [CONSTITUENTAGE], 
    c.[CONSTITUENTADDRESSDIMID], 
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],    
    c.[CONSTITUENTDEMOGRAPHICDIMID], 
    ms.[SEGMENTATIONDATEDIMID], 
    a.[APPEALDIMID], 
    a.[APPEALSTARTDATEDIMID], 
    a.[APPEALENDDATEDIMID],     
    case 
      when a.[APPEALSTARTDATE] is null then 0 
      when a.[APPEALSTARTDATE] > r.[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
      else datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
    end as [APPEALWEEKDIMID], 
    case 
      when ms.[SEGMENTATIONDATE] IS NULL then 0 
      when ms.[SEGMENTATIONDATE] > r.[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
      else datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
    end as [MARKETINGRESPONSEWEEKDIMID],
    rgi.[FINANCIALTRANSACTIONFACTID],
    rgi.[FINANCIALTRANSACTIONLINEITEMFACTID]
  from  
    BBDW.[FACT_RECURRINGGIFTINSTALLMENT] rgi
    left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] r
      on rgi.[REVENUEFACTID] = r.[FINANCIALTRANSACTIONLINEITEMFACTID]
    left join BBDW.[DIM_CONSTITUENT] c
      on r.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
    left join BBDW.[DIM_MARKETINGSEGMENT] ms
      on r.[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
    left join BBDW.[DIM_APPEAL] a
      on r.[APPEALDIMID] = a.[APPEALDIMID]