v_FACT_FINANCIALTRANSACTIONLINEITEM

Contains information about financialtransactionlineitems

Fields

Field	Field Type	Null	Description
FINANCIALTRANSACTIONLINEITEMFACTID	int		BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID]
FINANCIALTRANSACTIONLINEITEMSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMSYSTEMID]
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONSYSTEMID]
FINANCIALTRANSACTIONSEQUENCEID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONSEQUENCEID]
FINANCIALTRANSACTIONLOOKUPID	nvarchar(100)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLOOKUPID]
REVENUECODEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECHANNELDIMID]
FINANCIALTRANSACTIONDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATEDIMID]
FINANCIALTRANSACTIONDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]
FINANCIALTRANSACTIONPOSTDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONPOSTDATEDIMID]
FINANCIALTRANSACTIONPOSTDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONPOSTDATE]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTSYSTEMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGCONSTITUENTFACTID]
FINANCIALTRANSACTIONAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONAMOUNT]
FINANCIALTRANSACTIONRECEIPTAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONRECEIPTAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[BASECURRENCYDIMID]
FINANCIALTRANSACTIONBASEAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONBASEAMOUNT]
FINANCIALTRANSACTIONDATETIMEOFFSET	datetimeoffset	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATETIMEOFFSET]
ACCOUNTSYSTEMDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[ACCOUNTSYSTEMDIMID]
APPUSERDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[APPUSERDIMID]
CHECKNUMBER	nvarchar(50)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CHECKNUMBER]
CREDITCARDTYPEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CREDITCARDTYPEDIMID]
OTHERPAYMENTMETHODDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[OTHERPAYMENTMETHODDIMID]
OTHERREVENUETYPEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[OTHERREVENUETYPEDIMID]
CHECKFUZZYDATE	char(8)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATEDIMID]
GLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[GLREVENUECATEGORYDIMID]
FINANCIALTRANSACTIONLINEITEMPOSTDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMPOSTDATEDIMID]
FINANCIALTRANSACTIONLINEITEMPOSTDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMPOSTDATE]
DESIGNATIONDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[DESIGNATIONDIMID]
PURPOSELEVEL1DIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL1DIMID]
PURPOSELEVEL2DIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL2DIMID]
PURPOSELEVEL3DIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL3DIMID]
PURPOSELEVEL4DIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL4DIMID]
PURPOSELEVEL5DIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL5DIMID]
EVENTREGISTRANTFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[EVENTREGISTRANTFACTID]
FINANCIALTRANSACTIONLINEITEMAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMAMOUNT]
FINANCIALTRANSACTIONLINEITEMBASEAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT]
CHANGEDDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATEDIMID]
CHANGEDDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATE]
BATCHNUMBER	nvarchar(100)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[BATCHNUMBER]
SOURCELINEITEMFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[SOURCELINEITEMFACTID]
FINANCIALTRANSACTIONLINEITEMADJUSTMENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMADJUSTMENTSYSTEMID]
ADJUSTMENTCONSTITUENTDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTCONSTITUENTDIMID]
ADJUSTMENTDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTDATEDIMID]
ADJUSTMENTDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTDATE]
ADJUSTMENTREASONDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTREASONDIMID]
TRANSACTIONBALANCE	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[[TRANSACTIONBALANCE]]
EVENTDIMID	int		BBDW.[FACT_EVENTREGISTRANT].[EVENTDIMID]
EVENTSTARTDATEDIMID	int		BBDW.[FACT_EVENTREGISTRANT].[EVENTSTARTDATEDIMID]
EVENTENDDATEDIMID	int		BBDW.[FACT_EVENTREGISTRANT].[EVENTENDDATEDIMID]
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+.
LINEITEMBALANCE	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[LINEITEMBALANCE]
MARKETINGSEGMENTATIONDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTATIONDIMID]
FINANCIALTRANSACTIONLINEITEMTRANSACTIONAMOUNT	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMTRANSACTIONAMOUNT]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUESCHEDULEDIMID]
ADDEDDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTION].[ADDEDDATE]
ADDEDDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTION].[ADDEDDATEDIMID]

Definition

Copy


  CREATE view [BBDW].[v_FACT_FINANCIALTRANSACTIONLINEITEM] as
  select 
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMSYSTEMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONSYSTEMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONSEQUENCEID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLOOKUPID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECODEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUEFLAGDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUECHANNELDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONPOSTDATEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONPOSTDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTSYSTEMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSOURCECODEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGCONSTITUENTFACTID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONRECEIPTAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[BASECURRENCYDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONBASEAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATETIMEOFFSET],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ACCOUNTSYSTEMDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[APPUSERDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CHECKNUMBER],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CREDITCARDTYPEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[OTHERPAYMENTMETHODDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[OTHERREVENUETYPEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CHECKFUZZYDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[GLREVENUECATEGORYDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMPOSTDATEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMPOSTDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[DESIGNATIONDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL1DIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL2DIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL3DIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL4DIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[PURPOSELEVEL5DIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[EVENTREGISTRANTFACTID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[CHANGEDDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[BATCHNUMBER],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[SOURCELINEITEMFACTID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMADJUSTMENTSYSTEMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTCONSTITUENTDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTDATEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADJUSTMENTREASONDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONBALANCE],
    isnull(er.[EVENTDIMID], 0) as [EVENTDIMID], 
    isnull(er.[EVENTSTARTDATEDIMID], 0) as [EVENTSTARTDATEDIMID], 
    isnull(er.[EVENTENDDATEDIMID], 0) as [EVENTENDDATEDIMID], 
    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_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, a.[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) > 104 then 05 
      else datediff(ww, a.[APPEALSTARTDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) + 1 
    end as [APPEALWEEKDIMID], 
    case 
      when ms.[SEGMENTATIONDATE] is null then 0 
      when ms.[SEGMENTATIONDATE] > [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE] then 0 
      when datediff(ww, ms.[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) > 104 then 105 
      else datediff(ww, ms.[SEGMENTATIONDATE], [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONDATE]) + 1 
    end as [MARKETINGRESPONSEWEEKDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[LINEITEMBALANCE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTATIONDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMTRANSACTIONAMOUNT],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[REVENUESCHEDULEDIMID],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADDEDDATE],
    [FACT_FINANCIALTRANSACTIONLINEITEM].[ADDEDDATEDIMID]
  from 
    [BBDW].[FACT_FINANCIALTRANSACTIONLINEITEM]
    left join BBDW.[DIM_CONSTITUENT] c on [FACT_FINANCIALTRANSACTIONLINEITEM].[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
    left join BBDW.[DIM_MARKETINGSEGMENT] ms on [FACT_FINANCIALTRANSACTIONLINEITEM].[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
    left join BBDW.[DIM_APPEAL] a on [FACT_FINANCIALTRANSACTIONLINEITEM].[APPEALDIMID] = a.[APPEALDIMID]
    left join BBDW.[FACT_EVENTREGISTRANT] er on [FACT_FINANCIALTRANSACTIONLINEITEM].[EVENTREGISTRANTFACTID] = er.[EVENTREGISTRANTFACTID]