V_QUERY_BBDW_REVENUE_NORECOGNITION

Fields

Field	Field Type	Null
FINANCIALTRANSACTIONFACTID	int
FINANCIALTRANSACTIONSYSTEMID	uniqueidentifier	yes
FINANCIALTRANSACTIONLOOKUPID	nvarchar(100)	yes
AMOUNT	money	yes
RECEIPTAMOUNT	money	yes
FINANCIALTRANSACTIONDATE	datetime	yes
FINANCIALTRANSACTIONDATEDIMID	int	yes
POSTDATE	datetime	yes
POSTDATEDIMID	int	yes
CONSTITUENTDIMID	int	yes
REVENUECHANNELDESCRIPTION	nvarchar(100)	yes
REVENUEPOSTSTATUS	nvarchar(50)	yes
REVENUEPAYMENTMETHOD	nvarchar(50)	yes
REVENUETRANSACTIONTYPE	nvarchar(50)	yes
DONOTRECEIPT	int	yes
DONOTACKNOWLEDGE	int	yes
APPEALNAME	nvarchar(100)	yes
SOURCECODE	nvarchar(50)	yes
BATCHNUMBER	nvarchar(100)	yes
LETTER	nvarchar(100)	yes
TRANSACTIONBALANCE	money	yes
CURRENCYSYSTEMID	uniqueidentifier	yes
GIVENANONYMOUSLY	int	yes
NEEDSRERECEIPT	int	yes
DATEADDED	datetime	yes
DATEADDEDDIMID	int	yes
FREQUENCY	nvarchar(50)	yes
PLEDGESUBTYPE	nvarchar(100)	yes
OTHERPAYMENTMETHOD	nvarchar(100)	yes
APPEALDIMID	int	yes
DESIGNATIONLIST	nvarchar(4000)	yes
CREDITCARDEXPIRESON	UDT_FUZZYDATE	yes
DATECHANGED	datetime	yes
DATECHANGEDDIMID	int	yes
BASEAMOUNT	money	yes
BASECURRENCYSYSTEMID	uniqueidentifier	yes

Definition

Copy

create view BBDW.[V_QUERY_BBDW_REVENUE_NORECOGNITION] as 

select
  ft.[FINANCIALTRANSACTIONFACTID],
  ft.[FINANCIALTRANSACTIONSYSTEMID],
  ft.[FINANCIALTRANSACTIONLOOKUPID],
  ft.[AMOUNT],
  ft.[RECEIPTAMOUNT],
  ft.[FINANCIALTRANSACTIONDATE],
  ft.[FINANCIALTRANSACTIONDATEDIMID],
  ft.[POSTDATE],
  ft.[POSTDATEDIMID],
  ft.[CONSTITUENTDIMID],
  rch.[REVENUECHANNELDESCRIPTION],
  rc.[REVENUEPOSTSTATUS],
  rc.[REVENUEPAYMENTMETHOD],
  rc.[REVENUETRANSACTIONTYPE],
  rf.[DONOTRECEIPT],
  rf.[DONOTACKNOWLEDGE],
  a.[APPEALNAME],
  sc.[SOURCECODE],
  ft.[BATCHNUMBER],
  ms.[LETTERCODE] as [LETTER],
  ft.[TRANSACTIONBALANCE],
  c.[CURRENCYSYSTEMID],
  rf.[GIVENANONYMOUSLY],
  rf.[NEEDSRERECEIPT],
  ft.[ADDEDDATE] as [DATEADDED],
  ft.[ADDEDDATEDIMID] as [DATEADDEDDIMID],
  rsch.[FREQUENCY],
  rsch.[PLEDGESUBTYPE],
  opm.[OTHERPAYMENTMETHOD],
  ft.[APPEALDIMID],
  ft.[DESIGNATIONLIST],
  ft.[CREDITCARDEXPIRESON],  
  ft.[CHANGEDDATE] as [DATECHANGED],
  ft.[CHANGEDDATEDIMID] as [DATECHANGEDDIMID],
  ft.[BASEAMOUNT],
  bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID]
from BBDW.[FACT_FINANCIALTRANSACTION] ft
left join BBDW.[DIM_REVENUECODE] rc on rc.[REVENUECODEDIMID] = ft.[REVENUECODEDIMID]
left join BBDW.[DIM_REVENUEFLAG] rf on rf.[REVENUEFLAGDIMID] = ft.[REVENUEFLAGDIMID]
left join BBDW.[DIM_REVENUECHANNEL] rch on rch.[REVENUECHANNELDIMID] = ft.[REVENUECHANNELDIMID] and ft.[REVENUECHANNELDIMID] <> 0
left join BBDW.[DIM_APPEAL] a on ft.[APPEALDIMID] = a.[APPEALDIMID] and ft.[APPEALDIMID] <> 0
left join BBDW.[DIM_MARKETINGSOURCECODE] sc on sc.[MARKETINGSOURCECODEDIMID] = ft.[MARKETINGSOURCECODEDIMID] and ft.[MARKETINGSOURCECODEDIMID] <> 0
left join BBDW.[DIM_MARKETINGSEGMENT] ms on ms.[MARKETINGSEGMENTDIMID] = ft.[MARKETINGSEGMENTDIMID] and ft.[MARKETINGSEGMENTDIMID] <> 0
left join BBDW.[DIM_REVENUESCHEDULE] rsch on ft.[REVENUESCHEDULEDIMID] = rsch.[REVENUESCHEDULEDIMID] and ft.[REVENUESCHEDULEDIMID] <> 0
left join BBDW.[DIM_OTHERPAYMENTMETHOD] opm on ft.[OTHERPAYMENTMETHODDIMID] = opm.[OTHERPAYMENTMETHODDIMID] and ft.[OTHERPAYMENTMETHODDIMID] <> 0
left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = ft.[BASECURRENCYDIMID]
outer apply BBDW.[DIM_CURRENCY] c 

where rc.[REVENUETRANSACTIONTYPECODE] in (0,1,2,3,4,5,6,7,8,9,15)
and c.[ISORGANIZATIONCURRENCY] = 1
and rf.[ISDELETEDFINANCIALTRANSACTION] = 0