v_QUERY_FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE

Contains information from financial transaction line item attributes.

Fields

Field	Field Type	Null	Description
ATTRIBUTESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTESYSTEMID]
FINANCIALTRANSACTIONLINEITEMATTRIBUTESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[FINANCIALTRANSACTIONLINEITEMATTRIBUTESYSTEMID]
ATTRIBUTECATEGORYSYSTEMID	uniqueidentifier	yes
FINANCIALTRANSACTIONLINEITEMSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[FINANCIALTRANSACTIONLINEITEMSYSTEMID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[FINANCIALTRANSACTIONLINEITEMFACTID]
ATTRIBUTESTARTDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTESTARTDATEDIMID]
ATTRIBUTESTARTDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTESTARTDATE]
ATTRIBUTEENDDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTEENDDATEDIMID]
ATTRIBUTEENDDATE	datetime	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTEENDDATE]
ATTRIBUTEVALUE	nvarchar(255)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTEVALUE]
ATTRIBUTECOMMENT	nvarchar(255)	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[ATTRIBUTECOMMENT]
ATTRIBUTEVALUECONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUECONSTITUENTSYSTEMID]
ATTRIBUTEVALUECONSTITUENTFULLNAME	nvarchar(154)	yes	BBDW.[DIM_CONSTITUENT].[FULLNAME]
VALUEDATE	date	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUEDATE]
VALUEDATEDIMID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUEDATEDIMID]
VALUEYESNO	bit	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUEYESNO]
VALUECURRENCY	money	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUECURRENCY]
VALUEHOURMINUTE	UDT_HOURMINUTE	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUEHOURMINUTE]
VALUEFUZZYDATE	UDT_FUZZYDATE	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUEFUZZYDATE]
VALUENUMBER	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE].[VALUENUMBER]

Definition

Copy


create view [BBDW].[v_QUERY_FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE] as

  select
    ba.[ATTRIBUTESYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMATTRIBUTESYSTEMID],
    a.[ATTRIBUTECATEGORYSYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMSYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMFACTID],
    ba.[ATTRIBUTESTARTDATEDIMID],
    ba.[ATTRIBUTESTARTDATE],
    ba.[ATTRIBUTEENDDATEDIMID],
    ba.[ATTRIBUTEENDDATE],
    ba.[ATTRIBUTEVALUE], 
    ba.[ATTRIBUTECOMMENT],
    null as [ATTRIBUTEVALUECONSTITUENTSYSTEMID],
    null as [ATTRIBUTEVALUECONSTITUENTFULLNAME],
    ba.[VALUEDATE],
    ba.[VALUEDATEDIMID],
    ba.[VALUEYESNO],
    ba.[VALUECURRENCY],
    ba.[VALUEHOURMINUTE],
    ba.[VALUEFUZZYDATE],
    ba.[VALUENUMBER]
  from 
    BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE] ba
    inner join BBDW.[DIM_ATTRIBUTE] a on ba.[ATTRIBUTEDIMID]  = a.[ATTRIBUTEDIMID] and a.[ATTRIBUTEDATATYPE] <> 'Constituent Record'

union all

  select
    ba.[ATTRIBUTESYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMATTRIBUTESYSTEMID],
    a.[ATTRIBUTECATEGORYSYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMSYSTEMID],
    ba.[FINANCIALTRANSACTIONLINEITEMFACTID],
    ba.[ATTRIBUTESTARTDATEDIMID],
    ba.[ATTRIBUTESTARTDATE],
    ba.[ATTRIBUTEENDDATEDIMID],
    ba.[ATTRIBUTEENDDATE],
    ba.[ATTRIBUTEVALUE], 
    ba.[ATTRIBUTECOMMENT],
    c.[CONSTITUENTSYSTEMID] as [ATTRIBUTEVALUECONSTITUENTSYSTEMID],
    c.[FULLNAME] as [ATTRIBUTEVALUECONSTITUENTFULLNAME],
    ba.[VALUEDATE],
    ba.[VALUEDATEDIMID],
    ba.[VALUEYESNO],
    ba.[VALUECURRENCY],
    ba.[VALUEHOURMINUTE],
    ba.[VALUEFUZZYDATE],
    ba.[VALUENUMBER]
  from 
    BBDW.[FACT_FINANCIALTRANSACTIONLINEITEMATTRIBUTE] ba 
    inner join BBDW.[DIM_ATTRIBUTE] a on ba.[ATTRIBUTEDIMID] = a.[ATTRIBUTEDIMID] and a.[ATTRIBUTEDATATYPE] = 'Constituent Record'
    left join BBDW.[DIM_CONSTITUENT] c on ba.[ATTRIBUTEVALUE] = c.[CONSTITUENTSYSTEMID];