REVENUEBENEFIT
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| REVENUEID | uniqueidentifier | ||
| BENEFITID | uniqueidentifier | ||
| QUANTITY | int | ||
| UNITVALUE | money | ||
| DETAILS | nvarchar(255) | ||
| SEQUENCE | int | ||
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| SENDBENEFIT | tinyint | ||
| PERCENTAPPLICABLEAMOUNT | money | ||
| VALUEPERCENT | decimal(20, 4) | ||
| TRANSACTIONTOTALVALUE | money | ||
| ORGANIZATIONTOTALVALUE | money | ||
| BASECURRENCYID | uniqueidentifier | yes | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| TOTALVALUE | money | yes | |
| REVENUESPLITID | uniqueidentifier | yes |
Definition
Copy
CREATE view [dbo].[REVENUEBENEFIT] with schemabinding
as
select
LINEITEM.ID,
LINEITEM.FINANCIALTRANSACTIONID as REVENUEID,
EXT.BENEFITID,
isnull(cast(LINEITEM.QUANTITY as Int), 0) as QUANTITY,
LINEITEM.UNITVALUE,
isnull(Left(LINEITEM.DESCRIPTION,255),'') as DETAILS,
LINEITEM.SEQUENCE,
LINEITEM.ADDEDBYID,
LINEITEM.CHANGEDBYID,
LINEITEM.DATEADDED,
LINEITEM.DATECHANGED,
LINEITEM.TS,
LINEITEM.TSLONG,
EXT.SENDBENEFIT,
EXT.PERCENTAPPLICABLEAMOUNT,
EXT.VALUEPERCENT,
LINEITEM.TRANSACTIONAMOUNT as TRANSACTIONTOTALVALUE,
LINEITEM.ORGAMOUNT as ORGANIZATIONTOTALVALUE,
case CURRENCYSET.BASECURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else CURRENCYSET.BASECURRENCYID end as BASECURRENCYID,
case FT.TRANSACTIONCURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else FT.TRANSACTIONCURRENCYID end as TRANSACTIONCURRENCYID,
FT.BASEEXCHANGERATEID,
FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
case when LINEITEM.DELETEDON is null then LINEITEM.BASEAMOUNT else null end as TOTALVALUE,
EXT.REVENUESPLITID
from dbo.FINANCIALTRANSACTIONLINEITEM as LINEITEM
inner join dbo.FINANCIALTRANSACTION as FT on LINEITEM.FINANCIALTRANSACTIONID=FT.ID
inner join dbo.REVENUEBENEFIT_EXT as EXT on LINEITEM.ID = EXT.ID
inner join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
where LINEITEM.DELETEDON is null