PROPERTYDETAIL
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| PROPERTYSUBTYPECODEID | uniqueidentifier | yes | |
| SALEDATE | datetime | yes | |
| SALEAMOUNT | money | ||
| BROKERFEE | money | ||
| SALEPOSTDATE | datetime | yes | |
| SALEPOSTSTATUSCODE | tinyint | ||
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| SALEPOSTSTATUS | nvarchar(11) | yes | |
| ORGANIZATIONSALEAMOUNT | money | ||
| ORGANIZATIONBROKERFEE | money | ||
| TRANSACTIONSALEAMOUNT | money | ||
| TRANSACTIONBROKERFEE | money | ||
| BASECURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| ISSOLD | int |
Definition
Copy
CREATE view dbo.PROPERTYDETAIL with schemabinding as
select
FINANCIALTRANSACTION.ID
,PROPERTYDETAIL_EXT.PROPERTYSUBTYPECODEID
,PROPERTYDETAIL_EXT.SALEDATE
,isnull(case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FINANCIALTRANSACTION.BASEAMOUNT else -1 * FINANCIALTRANSACTION.BASEAMOUNT end,0) as SALEAMOUNT
,PROPERTYDETAIL_EXT.TRANSACTIONBROKERFEE as BROKERFEE
,cast(FINANCIALTRANSACTION.POSTDATE as datetime) as SALEPOSTDATE
,isnull(cast(case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) as SALEPOSTSTATUSCODE
,FINANCIALTRANSACTION.ADDEDBYID
,FINANCIALTRANSACTION.CHANGEDBYID
,FINANCIALTRANSACTION.DATEADDED
,FINANCIALTRANSACTION.DATECHANGED
,FINANCIALTRANSACTION.TS
,FINANCIALTRANSACTION.TSLONG
,cast(case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 'Posted' when 1 then 'Not Posted' when 3 then 'Do Not Post' end as nvarchar(11))as SALEPOSTSTATUS
,isnull(case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FINANCIALTRANSACTION.ORGAMOUNT else -1 * FINANCIALTRANSACTION.ORGAMOUNT end,0) as ORGANIZATIONSALEAMOUNT
,PROPERTYDETAIL_EXT.ORGANIZATIONBROKERFEE
,isnull(case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FINANCIALTRANSACTION.TRANSACTIONAMOUNT else -1 * FINANCIALTRANSACTION.TRANSACTIONAMOUNT end,0) as TRANSACTIONSALEAMOUNT
,PROPERTYDETAIL_EXT.BROKERFEE as TRANSACTIONBROKERFEE
,case when FINANCIALTRANSACTION.DELETEDON is null then V.BASECURRENCYID else null end as BASECURRENCYID
,FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
,case when FINANCIALTRANSACTION.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID
,FINANCIALTRANSACTION.BASEEXCHANGERATEID
,case when SALEDATE is null then 0 else 1 end as ISSOLD
from dbo.FINANCIALTRANSACTION
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.PARENTID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROPERTYDETAIL_EXT on FINANCIALTRANSACTION.ID = PROPERTYDETAIL_EXT.ID
where TYPECODE = 22 and FINANCIALTRANSACTION.DELETEDON is null