V_QUERY_BBDW_RECOGNITIONCREDIT

Fields

Field	Field Type	Null
REVENUERECOGNITIONSYSTEMID	uniqueidentifier	yes
CONSTITUENTDIMID	int	yes
FINANCIALTRANSACTIONFACTID	int	yes
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes
REVENUERECOGNITIONAMOUNT	money	yes
EFFECTIVEDATEDIMID	int	yes
EFFECTIVEDATE	datetime	yes
REVENUERECOGNITIONTYPE	nvarchar(100)	yes
REVENUECONSTITUENTDIMID	int	yes
REVENUETRANSACTIONTYPE	nvarchar(50)	yes
BASEREVENUERECOGNITIONAMOUNT	money	yes
BASECURRENCYSYSTEMID	uniqueidentifier	yes
CURRENCYSYSTEMID	uniqueidentifier	yes

Definition

Copy

create view BBDW.[V_QUERY_BBDW_RECOGNITIONCREDIT] as 

select
  rr.[REVENUERECOGNITIONSYSTEMID],
  rr.[CONSTITUENTDIMID],
  rr.[FINANCIALTRANSACTIONFACTID],
  rr.[FINANCIALTRANSACTIONLINEITEMFACTID],
  rr.[REVENUERECOGNITIONAMOUNT],
  rr.[EFFECTIVEDATEDIMID],
  rr.[EFFECTIVEDATE],
  rt.[REVENUERECOGNITIONTYPE],
  li.[CONSTITUENTDIMID] [REVENUECONSTITUENTDIMID],
  rc.[REVENUETRANSACTIONTYPE]  ,
  rr.[BASEREVENUERECOGNITIONAMOUNT],
  bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID],
  c.[CURRENCYSYSTEMID]
from BBDW.[FACT_REVENUERECOGNITION] rr
left join BBDW.[DIM_REVENUERECOGNITIONTYPE] rt on rr.[REVENUERECOGNITIONTYPEDIMID] = rt.[REVENUERECOGNITIONTYPEDIMID] and rt.[REVENUERECOGNITIONTYPEDIMID] > 0
left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] li on rr.[FINANCIALTRANSACTIONLINEITEMFACTID] = li.[FINANCIALTRANSACTIONLINEITEMFACTID]
left join BBDW.[DIM_REVENUECODE] rc on li.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
left join BBDW.[DIM_REVENUEFLAG] rf on li.[REVENUEFLAGDIMID] = rf.[REVENUEFLAGDIMID]
left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = rr.[BASECURRENCYDIMID]
outer apply BBDW.[DIM_CURRENCY] c 
where c.[ISORGANIZATIONCURRENCY] = 1
and rf.[ISDELETEDFINANCIALTRANSACTIONLINEITEM] = 0
and rf.[ISDELETEDFINANCIALTRANSACTION] = 0
and rc.[FINANCIALTRANSACTIONLINEITEMTYPECODE] <> 1
and rc.[REVENUETRANSACTIONTYPECODE] in (0,1,2,3,4,5,6,7,8,9)