v_FACT_INSTALLMENTWRITEOFF

The installment write-off fact table contains information about pledge, matching gift claim, and planned giving installment write-offs.

Fields

Field	Field Type	Null	Description
INSTALLMENTWRITEOFFFACTID	int		BBDW.[FACT_INSTALLMENTWRITEOFF].[INSTALLMENTWRITEOFFFACTID]
WRITEOFFDIMID	int	yes	BBDW.[FACT_INSTALLMENTWRITEOFF].[WRITEOFFDIMID]
WRITEOFFDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENTWRITEOFF].[WRITEOFFDATEDIMID]
WRITEOFFPOSTDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENTWRITEOFF].[WRITEOFFPOSTDATEDIMID]
INSTALLMENTWRITEOFFAMOUNT	money		BBDW.[FACT_INSTALLMENTWRITEOFF].[INSTALLMENTWRITEOFFAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_INSTALLMENTWRITEOFF].[BASECURRENCYDIMID]
BASEINSTALLMENTWRITEOFFAMOUNT	money	yes	BBDW.[FACT_INSTALLMENTWRITEOFF].[BASEINSTALLMENTWRITEOFFAMOUNT]
INSTALLMENTDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTDIMID]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULEDIMID]
REVENUESCHEDULESTARTDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULESTARTDATEDIMID]
REVENUESCHEDULEENDDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[REVENUESCHEDULEENDDATEDIMID]
INSTALLMENTDATEDIMID	int	yes	BBDW.[FACT_INSTALLMENT].[INSTALLMENTDATEDIMID]
INSTALLMENTSTATUSDIMID	int		Calculation that determines when the installment will occur relative to the current date. 0 = Past Due, 1 = Up to 1 year, 2 = 1 - 5 years, 3 = 5+ years.
REVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
REVENUESEQUENCEID	int	yes	BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
REVENUECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
REVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
POSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
DESIGNATIONDIMID	int	yes	BBDW.[FACT_REVENUE].[DESIGNATIONDIMID]
PURPOSELEVEL1DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL1DIMID]
PURPOSELEVEL2DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL2DIMID]
PURPOSELEVEL3DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL3DIMID]
PURPOSELEVEL4DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL4DIMID]
PURPOSELEVEL5DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL5DIMID]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
GLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
CONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
CONSTITUENTAGE	int	yes	BBDW.[DIM_CONSTITUENT].[AGE]
CONSTITUENTADDRESSDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
CONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
CONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
CONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
CONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
SEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
APPEALDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALDIMID]
APPEALSTARTDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
APPEALENDDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
APPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the installment date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the installment date. Anything over 104 weeks is labeled 105+.
SOURCEREVENUESEQUENCEID	int	yes	[BBDW].[FACT_INSTALLMNET].[SOURCEREVENUESEQUENCEID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_INSTALLMENTWRITEOFF] as
        select 
          iw.[INSTALLMENTWRITEOFFFACTID], 
          iw.[WRITEOFFDIMID], 
          iw.[WRITEOFFDATEDIMID], 
          iw.[WRITEOFFPOSTDATEDIMID], 
          iw.[INSTALLMENTWRITEOFFAMOUNT], 
          iw.[BASECURRENCYDIMID],   
          iw.[BASEINSTALLMENTWRITEOFFAMOUNT],
          i.[INSTALLMENTDIMID], 
          i.[REVENUESCHEDULEDIMID], 
          i.[REVENUESCHEDULESTARTDATEDIMID], 
          i.[REVENUESCHEDULEENDDATEDIMID], 
          i.[INSTALLMENTDATEDIMID], 
          case
            when i.[INSTALLMENTDATE] < d.[ACTUALDATE] then 0 
            when dateadd(yy, -1, d.[ACTUALDATE]) < current_timestamp then 1 
            when dateadd(yy, -5, d.[ACTUALDATE]) < current_timestamp then 2 
            else 3 
          end as [INSTALLMENTSTATUSDIMID], 
          r.[FINANCIALTRANSACTIONLINEITEMFACTID] as [REVENUEFACTID], 
          r.[FINANCIALTRANSACTIONSEQUENCEID] as [REVENUESEQUENCEID], 
          r.[REVENUECODEDIMID],
          r.[REVENUEFLAGDIMID], 
          r.[REVENUECHANNELDIMID], 
          r.[FINANCIALTRANSACTIONDATEDIMID] as [REVENUEDATEDIMID], 
          r.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [POSTDATEDIMID], 
          r.[DESIGNATIONDIMID], 
          r.[PURPOSELEVEL1DIMID], 
          r.[PURPOSELEVEL2DIMID], 
          r.[PURPOSELEVEL3DIMID],   
          r.[PURPOSELEVEL4DIMID], 
          r.[PURPOSELEVEL5DIMID], 
          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],
           i.[SOURCEREVENUESEQUENCEID] as [SOURCEREVENUESEQUENCEID],
          r.[FINANCIALTRANSACTIONFACTID],
          r.[FINANCIALTRANSACTIONLINEITEMFACTID]
        from
          BBDW.[FACT_INSTALLMENTWRITEOFF] iw  
          inner join BBDW.[DIM_DATE] d on d.[ISCURRENTDATE] = 1
          left join BBDW.[FACT_INSTALLMENT] i 
            on iw.[INSTALLMENTFACTID] = i.[INSTALLMENTFACTID]
          left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] r
            on i.[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]