V_QUERY_BBDW_PAYMENT

create view BBDW.[V_QUERY_BBDW_PAYMENT] as 

select
  li.[FINANCIALTRANSACTIONLINEITEMFACTID],
  li.[FINANCIALTRANSACTIONLINEITEMSYSTEMID],
  li.[FINANCIALTRANSACTIONFACTID],
  li.[DESIGNATIONDIMID],
  li.[FINANCIALTRANSACTIONLINEITEMAMOUNT],
  rc.[REVENUEAPPLICATION],
  rc.[REVENUEAPPLICATIONTYPE],
  gl.[GLREVENUECATEGORYNAME],
  c.[CURRENCYSYSTEMID],
  li.[EVENTREGISTRANTFACTID],
  [COMMITMENT].[COMMITMENTFINANCIALTRANSACTIONFACTID],
  li.[FINANCIALTRANSACTIONLINEITEMBASEAMOUNT],
  bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID]
from 
  BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] li
  left join BBDW.[DIM_REVENUECODE] rc on li.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
  left join BBDW.[DIM_REVENUEFLAG] rf on rf.[REVENUEFLAGDIMID] = li.[REVENUEFLAGDIMID]
  left join BBDW.[DIM_GLREVENUECATEGORY] gl on gl.[GLREVENUECATEGORYDIMID] = li.[GLREVENUECATEGORYDIMID] and li.[GLREVENUECATEGORYDIMID] <> 0
  left join 
  (
    select
    distinct
    ip.[FINANCIALTRANSACTIONLINEITEMFACTID] as [PAYMENTFINANCIALTRANSACTIONLINEITEMFACTID],
    i.[FINANCIALTRANSACTIONFACTID] as [COMMITMENTFINANCIALTRANSACTIONFACTID]
  from BBDW.[FACT_INSTALLMENTPAYMENT] ip
  inner join BBDW.[FACT_INSTALLMENT] i on ip.[INSTALLMENTFACTID] = i.[INSTALLMENTFACTID]
  ) as [COMMITMENT] on li.[FINANCIALTRANSACTIONLINEITEMFACTID] = [COMMITMENT].[PAYMENTFINANCIALTRANSACTIONLINEITEMFACTID]
  left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = li.[BASECURRENCYDIMID]

  outer apply BBDW.[DIM_CURRENCY] c 
where 
  rc.[REVENUETRANSACTIONTYPECODE] in (0,1,2,3,4,5,6,7,8,9, 15)
  and rc.[REVENUEAPPLICATIONTYPECODE] > -1    
  and rc.[FINANCIALTRANSACTIONLINEITEMTYPECODE] != 1
  and rf.[ISDELETEDFINANCIALTRANSACTIONLINEITEM] = 0  
  and c.[ISORGANIZATIONCURRENCY] = 1