v_FACT_FINANCIALTRANSACTION

Contains information about financialtransactions

Fields

Field	Field Type	Null	Description
FINANCIALTRANSACTIONFACTID	int		BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONSYSTEMID]
FINANCIALTRANSACTIONSEQUENCEID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONSEQUENCEID]
FINANCIALTRANSACTIONLOOKUPID	nvarchar(100)	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONLOOKUPID]
REVENUECODEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[REVENUECHANNELDIMID]
FINANCIALTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATEDIMID]
FINANCIALTRANSACTIONDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE]
FINANCIALTRANSACTIONDATETIMEOFFSET	datetimeoffset	yes	BBDW.[FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATETIMEOFFSET]
POSTDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[POSTDATEDIMID]
POSTDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTION].[POSTDATE]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CONSTITUENTSYSTEMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[MARKETINGSEGMENTDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_FINANCIALTRANSACTION].[MARKETINGCONSTITUENTFACTID]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[BASECURRENCYDIMID]
AMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[AMOUNT]
BASEAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[BASEAMOUNT]
RECEIPTAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[RECEIPTAMOUNT]
CHANGEDDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CHANGEDDATE]
CHANGEDDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CHANGEDDATEDIMID]
APPUSERDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[APPUSERDIMID]
CHECKNUMBER	nvarchar(50)	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CHECKNUMBER]
CHECKFUZZYDATE	char(8)	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CHECKFUZZYDATE]
CREDITCARDTYPEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CREDITCARDTYPEDIMID]
OTHERPAYMENTMETHODDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[OTHERPAYMENTMETHODDIMID]
ACCOUNTSYSTEMDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[ACCOUNTSYSTEMDIMID]
BATCHNUMBER	nvarchar(100)	yes	BBDW.[FACT_FINANCIALTRANSACTION].[BATCHNUMBER]
TRANSACTIONBALANCE	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[[TRANSACTIONBALANCE]]
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 revenue date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the revenue date. Anything over 104 weeks is labeled 105+.
MARKETINGSEGMENTATIONDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[MARKETINGSEGMENTATIONDIMID]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[REVENUESCHEDULEDIMID]
ADDEDDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTION].[ADDEDDATE]
ADDEDDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[ADDEDDATEDIMID]
DESIGNATIONLIST	nvarchar(4000)	yes	BBDW.[FACT_FINANCIALTRANSACTION].[DESIGNATIONLIST]
CREDITCARDEXPIRESON	UDT_FUZZYDATE	yes	BBDW.[FACT_FINANCIALTRANSACTION].[CREDITCARDEXPIRESON]
TOTALINSTALLMENTCOUNT	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[TOTALINSTALLMENTCOUNT]
TOTALPAIDAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[TOTALPAIDAMOUNT]
PASTDUEAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTION].[PASTDUEAMOUNT]

Definition

Copy


CREATE view [BBDW].[v_FACT_FINANCIALTRANSACTION] as
  select 
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONFACTID],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONSYSTEMID],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONSEQUENCEID],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONLOOKUPID],
    [FACT_FINANCIALTRANSACTION].[REVENUECODEDIMID],
    [FACT_FINANCIALTRANSACTION].[REVENUEFLAGDIMID],
    [FACT_FINANCIALTRANSACTION].[REVENUECHANNELDIMID],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATEDIMID],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE],
    [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATETIMEOFFSET],
    [FACT_FINANCIALTRANSACTION].[POSTDATEDIMID],
    [FACT_FINANCIALTRANSACTION].[POSTDATE],
    [FACT_FINANCIALTRANSACTION].[CONSTITUENTDIMID],
    [FACT_FINANCIALTRANSACTION].[CONSTITUENTSYSTEMID],    
    [FACT_FINANCIALTRANSACTION].[MARKETINGSEGMENTDIMID],
    [FACT_FINANCIALTRANSACTION].[MARKETINGSOURCECODEDIMID],
    [FACT_FINANCIALTRANSACTION].[MARKETINGCONSTITUENTFACTID],
    [FACT_FINANCIALTRANSACTION].[BASECURRENCYDIMID],
    [FACT_FINANCIALTRANSACTION].[AMOUNT],
    [FACT_FINANCIALTRANSACTION].[BASEAMOUNT],
    [FACT_FINANCIALTRANSACTION].[RECEIPTAMOUNT],
    [FACT_FINANCIALTRANSACTION].[CHANGEDDATE],
    [FACT_FINANCIALTRANSACTION].[CHANGEDDATEDIMID],
    [FACT_FINANCIALTRANSACTION].[APPUSERDIMID],
    [FACT_FINANCIALTRANSACTION].[CHECKNUMBER],
    [FACT_FINANCIALTRANSACTION].[CHECKFUZZYDATE],
    [FACT_FINANCIALTRANSACTION].[CREDITCARDTYPEDIMID],
    [FACT_FINANCIALTRANSACTION].[OTHERPAYMENTMETHODDIMID],
    [FACT_FINANCIALTRANSACTION].[ACCOUNTSYSTEMDIMID],
    [FACT_FINANCIALTRANSACTION].[BATCHNUMBER],
    [FACT_FINANCIALTRANSACTION].[TRANSACTIONBALANCE],
    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] > [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, a.[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE]) > 104 then 05 
      else datediff(ww, a.[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE]) + 1 
      end as [APPEALWEEKDIMID], 
    case 
      when ms.[SEGMENTATIONDATE] is null then 0 
      when ms.[SEGMENTATIONDATE] > [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, ms.[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE]) > 104 then 105 
      else datediff(ww, ms.[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTION].[FINANCIALTRANSACTIONDATE]) + 1 
      end as [MARKETINGRESPONSEWEEKDIMID],
    [FACT_FINANCIALTRANSACTION].[MARKETINGSEGMENTATIONDIMID],
    [FACT_FINANCIALTRANSACTION].[REVENUESCHEDULEDIMID],
    [FACT_FINANCIALTRANSACTION].[ADDEDDATE],
    [FACT_FINANCIALTRANSACTION].[ADDEDDATEDIMID],
    [FACT_FINANCIALTRANSACTION].[DESIGNATIONLIST],
    [FACT_FINANCIALTRANSACTION].[CREDITCARDEXPIRESON],
    [FACT_FINANCIALTRANSACTION].[TOTALINSTALLMENTCOUNT],
    [FACT_FINANCIALTRANSACTION].[TOTALPAIDAMOUNT],
    [FACT_FINANCIALTRANSACTION].[PASTDUEAMOUNT]
  from 
  [BBDW].[FACT_FINANCIALTRANSACTION]      
  left join BBDW.[DIM_CONSTITUENT] c on [FACT_FINANCIALTRANSACTION].[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
  left join BBDW.[DIM_MARKETINGSEGMENT] ms on [FACT_FINANCIALTRANSACTION].[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
  left join BBDW.[DIM_APPEAL] a on [FACT_FINANCIALTRANSACTION].[APPEALDIMID] = a.[APPEALDIMID];