v_FACT_INSTALLMENTPAYMENT

The installment payment fact table contains information about pledge, matching gift claim, and planned giving installment payments.

Fields

Field	Field Type	Null	Description
INSTALLMENTPAYMENTAMOUNT	money	yes	BBDW.[FACT_INSTALLMENTPAYMENT].[INSTALLMENTPAYMENTAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_INSTALLMENTPAYMENT].[BASECURRENCYDIMID]
BASEINSTALLMENTPAYMENTAMOUNT	money	yes	BBDW.[FACT_INSTALLMENTPAYMENT].[BASEINSTALLMENTPAYMENTAMOUNT]
PAYMENTREVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
PAYMENTREVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
PAYEMENTPOSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
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.
INSTALLMENTPAYMENTLAGDAYS	int	yes	The difference in days between the installment date and the installment payment date.
ISINSTALLMENTPAYMENTLATE	int		True if the installment payment date is after the installment date.
PLEDGEREVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
PLEDGEREVENUESEQUENCEID	int	yes	BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
PLEDGEREVENUECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
PLEDGEREVENUEFLAGDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
PLEDGEREVENUECHANNELDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
PLEDGEREVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
PLEDGEPOSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
PLEDGEDESIGNATIONDIMID	int	yes	BBDW.[FACT_REVENUE].[DESIGNATIONDIMID]
PLEDGEPURPOSELEVEL1DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL1DIMID]
PLEDGEPURPOSELEVEL2DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL2DIMID]
PLEDGEPURPOSELEVEL3DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL3DIMID]
PLEDGEPURPOSELEVEL4DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL4DIMID]
PLEDGEPURPOSELEVEL5DIMID	int	yes	BBDW.[FACT_REVENUE].[PURPOSELEVEL5DIMID]
PLEDGECONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
PLEDGECONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
PLEDGEMARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
PLEDGEMARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
PLEDGEMARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
PLEDGEGLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
PLEDGECONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
PLEDGECONSTITUENTAGE	int	yes	BBDW.[DIM_CONSTITUENT].[AGE]
PLEDGECONSTITUENTADDRESSDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
PLEDGECONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
PLEDGECONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
PLEDGECONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
PLEDGECONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
PLEDGESEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
PLEDGEAPPEALDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALDIMID]
PLEDGEAPPEALSTARTDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
PLEDGEAPPEALENDDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
PAYMENTAPPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the installment payment date. Anything over 104 weeks is labeled 105+.
PAYMENTMARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the installment payment date. Anything over 104 weeks is labeled 105+.
SOURCEREVENUESEQUENCEID	int	yes	[BBDW].[FACT_INSTALLMNET].[SOURCEREVENUESEQUENCEID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_INSTALLMENTPAYMENT].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_INSTALLMENTPAYMENT].[FINANCIALTRANSACTIONLINEITEMFACTID]
ORGINSTALLMENTPAYMENTAMOUNT	money	yes	Calculated column. Transaction Amount currency converted to organisation amount.

Definition

Copy


CREATE view BBDW.v_FACT_INSTALLMENTPAYMENT as
  select ip.INSTALLMENTPAYMENTAMOUNT, 
    ip.BASECURRENCYDIMID, 
        ip.BASEINSTALLMENTPAYMENTAMOUNT, 
    payment.FINANCIALTRANSACTIONLINEITEMFACTID AS PAYMENTREVENUEFACTID, 
    payment.FINANCIALTRANSACTIONDATEDIMID AS PAYMENTREVENUEDATEDIMID, 
    payment.FINANCIALTRANSACTIONPOSTDATEDIMID AS PAYEMENTPOSTDATEDIMID, 
        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, 
            datediff(dd, i.INSTALLMENTDATE, payment.FINANCIALTRANSACTIONDATE) as INSTALLMENTPAYMENTLAGDAYS,
            case
                when datediff(dd, i.[INSTALLMENTDATE], payment.[FINANCIALTRANSACTIONDATE]) > 0 
                  then 1 
                else 0 
            end as ISINSTALLMENTPAYMENTLATE, 
      pledge.FINANCIALTRANSACTIONLINEITEMFACTID AS PLEDGEREVENUEFACTID,
            pledge.FINANCIALTRANSACTIONSEQUENCEID AS PLEDGEREVENUESEQUENCEID, 
      pledge.REVENUECODEDIMID AS PLEDGEREVENUECODEDIMID,
            pledge.REVENUEFLAGDIMID AS PLEDGEREVENUEFLAGDIMID, 
      pledge.REVENUECHANNELDIMID AS PLEDGEREVENUECHANNELDIMID, 
            pledge.FINANCIALTRANSACTIONDATEDIMID AS PLEDGEREVENUEDATEDIMID, 
      pledge.FINANCIALTRANSACTIONPOSTDATEDIMID AS PLEDGEPOSTDATEDIMID, 
            pledge.DESIGNATIONDIMID AS PLEDGEDESIGNATIONDIMID, 
      pledge.PURPOSELEVEL1DIMID AS PLEDGEPURPOSELEVEL1DIMID, 
            pledge.PURPOSELEVEL2DIMID AS PLEDGEPURPOSELEVEL2DIMID, 
      pledge.PURPOSELEVEL3DIMID AS PLEDGEPURPOSELEVEL3DIMID, 
            pledge.PURPOSELEVEL4DIMID AS PLEDGEPURPOSELEVEL4DIMID, 
      pledge.PURPOSELEVEL5DIMID AS PLEDGEPURPOSELEVEL5DIMID, 
            pledge.CONSTITUENTDIMID AS PLEDGECONSTITUENTDIMID, 
      pledge.CONSTITUENTSYSTEMID AS PLEDGECONSTITUENTSYSTEMID, 
            pledge.MARKETINGSEGMENTDIMID AS PLEDGEMARKETINGSEGMENTDIMID, 
      pledge.MARKETINGSOURCECODEDIMID AS PLEDGEMARKETINGSOURCECODEDIMID, 
      pledge.MARKETINGCONSTITUENTFACTID AS PLEDGEMARKETINGCONSTITUENTFACTID, 
      pledge.GLREVENUECATEGORYDIMID AS PLEDGEGLREVENUECATEGORYDIMID, 
      c.CONSTITUENTINFORMATIONDIMID AS PLEDGECONSTITUENTINFORMATIONDIMID, 
            c.AGE AS PLEDGECONSTITUENTAGE, 
      c.CONSTITUENTADDRESSDIMID AS PLEDGECONSTITUENTADDRESSDIMID, 
      c.CONSTITUENTADDRESSDETAILDIMID AS PLEDGECONSTITUENTADDRESSDETAILDIMID, 
      c.CONSTITUENTADDRESSFLAGDIMID AS PLEDGECONSTITUENTADDRESSFLAGDIMID, 
      c.CONSTITUENTADDRESSTYPEDIMID AS PLEDGECONSTITUENTADDRESSTYPEDIMID, 
      c.CONSTITUENTDEMOGRAPHICDIMID AS PLEDGECONSTITUENTDEMOGRAPHICDIMID, 
            ms.SEGMENTATIONDATEDIMID AS PLEDGESEGMENTATIONDATEDIMID, 
      a.APPEALDIMID AS PLEDGEAPPEALDIMID, 
            a.APPEALSTARTDATEDIMID AS PLEDGEAPPEALSTARTDATEDIMID, 
      a.APPEALENDDATEDIMID AS PLEDGEAPPEALENDDATEDIMID, 
            case 
                when a.[APPEALSTARTDATE] IS NULL 
          then 0 
                when a.[APPEALSTARTDATE] > payment.[FINANCIALTRANSACTIONDATE] 
                  then 0 
                when datediff(ww, a.[APPEALSTARTDATE], payment.[FINANCIALTRANSACTIONDATE]) > 104 
                  then 105 
                else datediff(ww, a.[APPEALSTARTDATE], payment.[FINANCIALTRANSACTIONDATE]) + 1 
            end as PAYMENTAPPEALWEEKDIMID, 
            case 
              when ms.[SEGMENTATIONDATE] IS NULL 
          then 0 
              when ms.[SEGMENTATIONDATE] > payment.[FINANCIALTRANSACTIONDATE] 
               then 0 
              when datediff(ww, ms.[SEGMENTATIONDATE], payment.[FINANCIALTRANSACTIONDATE]) > 104 
               then 105 
              else datediff(ww, ms.[SEGMENTATIONDATE], payment.[FINANCIALTRANSACTIONDATE]) + 1 
            end as PAYMENTMARKETINGRESPONSEWEEKDIMID, 
            i.SOURCEREVENUESEQUENCEID, 
      ip.FINANCIALTRANSACTIONFACTID, 
      ip.FINANCIALTRANSACTIONLINEITEMFACTID,
            ip.ORGINSTALLMENTPAYMENTAMOUNT 
  from BBDW.FACT_INSTALLMENTPAYMENT as ip 
    inner join  BBDW.DIM_DATE AS d ON d.ISCURRENTDATE = 1 
      left outer join BBDW.FACT_FINANCIALTRANSACTIONLINEITEM as payment on ip.REVENUEFACTID = payment.FINANCIALTRANSACTIONLINEITEMFACTID
      left outer join BBDW.FACT_INSTALLMENT as i on i.INSTALLMENTFACTID = ip.INSTALLMENTFACTID 
      left outer join BBDW.FACT_FINANCIALTRANSACTIONLINEITEM as pledge on i.REVENUEFACTID = pledge.FINANCIALTRANSACTIONLINEITEMFACTID 
      left outer join BBDW.DIM_CONSTITUENT as c on pledge.CONSTITUENTDIMID = c.CONSTITUENTDIMID 
      left outer join BBDW.DIM_MARKETINGSEGMENT as ms on pledge.MARKETINGSEGMENTDIMID = ms.MARKETINGSEGMENTDIMID 
      left outer join BBDW.DIM_APPEAL as a on pledge.APPEALDIMID = a.APPEALDIMID