V_QUERY_BBDW_RECURRINGGIFTSUMMARY

Fields

Field	Field Type	Null
FINANCIALTRANSACTIONFACTID	int
FINANCIALTRANSACTIONSYSTEMID	uniqueidentifier	yes
AMOUNT	money	yes
TOTALPAIDAMOUNT	money	yes
PASTDUEAMOUNT	money	yes
FREQUENCY	nvarchar(50)	yes
STATUS	nvarchar(50)	yes
FINANCIALTRANSACTIONDATE	datetime	yes
FINANCIALTRANSACTIONDATEDIMID	int	yes
REVENUESCHEDULESTARTDATE	datetime	yes
REVENUESCHEDULESTARTDATEDIMID	int	yes
REVENUESCHEDULEENDDATE	datetime	yes
REVENUESCHEDULEENDDATEDIMID	int	yes
FIRSTINSTALLMENTFACTID	int	yes
LASTINSTALLMENTFACTID	int	yes
CURRENCYSYSTEMID	uniqueidentifier	yes
BASEAMOUNT	money	yes
BASECURRENCYSYSTEMID	uniqueidentifier	yes

Definition

Copy

create view BBDW.[V_QUERY_BBDW_RECURRINGGIFTSUMMARY] as 

select
  ft.[FINANCIALTRANSACTIONFACTID],
  ft.[FINANCIALTRANSACTIONSYSTEMID],
  ft.[AMOUNT],
  ft.[TOTALPAIDAMOUNT],
  ft.[PASTDUEAMOUNT],
  rs.[FREQUENCY],
  rs.[RECURRINGGIFTSTATUS] as [STATUS],
  [LASTPAYMENT].[FINANCIALTRANSACTIONDATE],
  [LASTPAYMENT].[FINANCIALTRANSACTIONDATEDIMID],
  fi.[REVENUESCHEDULESTARTDATE],
  fi.[REVENUESCHEDULESTARTDATEDIMID],
  li.[REVENUESCHEDULEENDDATE],
  li.[REVENUESCHEDULEENDDATEDIMID],
  fi.[RECURRINGGIFTINSTALLMENTFACTID] as [FIRSTINSTALLMENTFACTID],
  li.[RECURRINGGIFTINSTALLMENTFACTID] as [LASTINSTALLMENTFACTID],
  c.[CURRENCYSYSTEMID],
  ft.[BASEAMOUNT],
  bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID]
from BBDW.[FACT_FINANCIALTRANSACTION] ft
inner join BBDW.[DIM_REVENUECODE] rc on ft.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
left join BBDW.[DIM_REVENUESCHEDULE] rs on ft.[REVENUESCHEDULEDIMID] = rs.[REVENUESCHEDULEDIMID]
left join BBDW.[FACT_RECURRINGGIFTINSTALLMENT] fi on fi.[FINANCIALTRANSACTIONFACTID] = ft.[FINANCIALTRANSACTIONFACTID] and fi.[ISFIRSTINSTALLMENT] = 1
left join BBDW.[FACT_RECURRINGGIFTINSTALLMENT] li on li.[FINANCIALTRANSACTIONFACTID] = ft.[FINANCIALTRANSACTIONFACTID] 
left join 
(
  select
    rgi.[FINANCIALTRANSACTIONFACTID],
    ft2.[FINANCIALTRANSACTIONDATE],
    ft2.[FINANCIALTRANSACTIONDATEDIMID]
  from BBDW.[FACT_RECURRINGGIFTINSTALLMENT] rgi
  inner join BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT] rgip on rgi.[RECURRINGGIFTINSTALLMENTFACTID] = rgip.[RECURRINGGIFTINSTALLMENTFACTID] and rgip.[ISLASTPAYMENT] = 1
  inner join BBDW.[FACT_FINANCIALTRANSACTION] ft2 on ft2.[FINANCIALTRANSACTIONFACTID] = rgip.[FINANCIALTRANSACTIONFACTID]
) [LASTPAYMENT] on [LASTPAYMENT].FINANCIALTRANSACTIONFACTID = ft.[FINANCIALTRANSACTIONFACTID]
left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = ft.[BASECURRENCYDIMID]
outer apply BBDW.[DIM_CURRENCY] c 
where [REVENUETRANSACTIONTYPECODE] = 2    
and c.[ISORGANIZATIONCURRENCY] = 1