V_QUERY_BBDW_MEMBERSHIPINSTALLMENTPLANSUMMARY
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| FINANCIALTRANSACTIONFACTID | int | ||
| FINANCIALTRANSACTIONSYSTEMID | uniqueidentifier | yes | |
| AMOUNT | money | yes | |
| TRANSACTIONBALANCE | money | yes | |
| TOTALPAIDAMOUNT | money | yes | |
| PASTDUEAMOUNT | money | yes | |
| TOTALINSTALLMENTCOUNT | int | yes | |
| FREQUENCY | nvarchar(50) | yes | |
| STARTDATE | datetime | yes | |
| STARTDATEDIMID | int | yes | |
| ENDDATE | datetime | yes | |
| ENDDATEDIMID | int | yes | |
| FIRSTINSTALLMENTFACTID | int | yes | |
| LASTINSTALLMENTFACTID | int | yes | |
| NEXTINSTALLMENTFACTID | int | yes | |
| CURRENCYSYSTEMID | uniqueidentifier | yes | |
| BASEAMOUNT | money | yes | |
| BASECURRENCYSYSTEMID | uniqueidentifier | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_MEMBERSHIPINSTALLMENTPLANSUMMARY] as
select
ft.[FINANCIALTRANSACTIONFACTID],
ft.[FINANCIALTRANSACTIONSYSTEMID],
ft.[AMOUNT],
ft.[TRANSACTIONBALANCE],
ft.[TOTALPAIDAMOUNT],
ft.[PASTDUEAMOUNT],
ft.[TOTALINSTALLMENTCOUNT],
rs.[FREQUENCY],
fi.[INSTALLMENTDATE] as [STARTDATE],
fi.[INSTALLMENTDATEDIMID] as [STARTDATEDIMID],
li.[INSTALLMENTDATE] as [ENDDATE],
li.[INSTALLMENTDATEDIMID] as [ENDDATEDIMID],
fi.[INSTALLMENTFACTID] as [FIRSTINSTALLMENTFACTID],
li.[INSTALLMENTFACTID] as [LASTINSTALLMENTFACTID],
ni.[INSTALLMENTFACTID] as [NEXTINSTALLMENTFACTID],
c.[CURRENCYSYSTEMID],
ft.[BASEAMOUNT],
bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID]
from BBDW.[FACT_FINANCIALTRANSACTION] ft
inner join BBDW.[DIM_REVENUECODE] rc on ft.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
left join BBDW.[DIM_REVENUESCHEDULE] rs on ft.[REVENUESCHEDULEDIMID] = rs.[REVENUESCHEDULEDIMID]
left join BBDW.[FACT_INSTALLMENT] fi on ft.[FINANCIALTRANSACTIONFACTID] = fi.[FINANCIALTRANSACTIONFACTID] and fi.[ISFIRSTINSTALLMENT] = 1
left join BBDW.[FACT_INSTALLMENT] li on ft.[FINANCIALTRANSACTIONFACTID] = li.[FINANCIALTRANSACTIONFACTID] and li.[ISLASTINSTALLMENT] = 1
left join BBDW.[FACT_INSTALLMENT] ni on ft.[FINANCIALTRANSACTIONFACTID] = ni.[FINANCIALTRANSACTIONFACTID] and ni.[ISNEXTINSTALLMENT] = 1
left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = ft.[BASECURRENCYDIMID]
outer apply BBDW.[DIM_CURRENCY] c
where [REVENUETRANSACTIONTYPECODE] = 15
and c.[ISORGANIZATIONCURRENCY] = 1