BENEFITGLDISTRIBUTION
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| REVENUEBENEFITID | uniqueidentifier | yes | |
| GLPAYMENTMETHODREVENUETYPEMAPPINGID | uniqueidentifier | ||
| PROJECT | nvarchar(100) | ||
| REFERENCE | nvarchar(255) | ||
| AMOUNT | money | ||
| ACCOUNT | nvarchar(100) | ||
| TRANSACTIONTYPECODE | tinyint | ||
| TRANSACTIONTYPE | nvarchar(6) | yes | |
| GLTRANSACTIONID | uniqueidentifier | ||
| OUTDATED | bit | ||
| REVENUEID | uniqueidentifier | yes | |
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| FULLYPAIDSTATUS | tinyint | ||
| BASECURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONAMOUNT | money | ||
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| TRANSACTIONAMOUNT | money | ||
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| BENEFITTYPECODE | tinyint | ||
| BENEFITTYPE | nvarchar(9) | yes |
Definition
Copy
CREATE view [dbo].[BENEFITGLDISTRIBUTION] with schemabinding as
select
isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID, '00000000-0000-0000-0000-000000000000') as ID,
case when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then FINANCIALTRANSACTIONLINEITEM.ID else null end as REVENUEBENEFITID,
isnull(JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID, '00000000-0000-0000-0000-000000000000') as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
JOURNALENTRY_EXT.PROJECT,
JOURNALENTRY.COMMENT as REFERENCE,
JOURNALENTRY.BASEAMOUNT as AMOUNT,
GLACCOUNT.ACCOUNTNUMBER as ACCOUNT,
JOURNALENTRY.TRANSACTIONTYPECODE,
case JOURNALENTRY.TRANSACTIONTYPECODE
when 0 then N'Debit'
when 1 then N'Credit'
end as TRANSACTIONTYPE,
JOURNALENTRY.ID as GLTRANSACTIONID,
JOURNALENTRY_EXT.OUTDATED,
JOURNALENTRY_EXT.LOGICALREVENUEID as REVENUEID,
JOURNALENTRY_EXT.ADDEDBYID,
JOURNALENTRY_EXT.CHANGEDBYID,
JOURNALENTRY_EXT.DATEADDED,
JOURNALENTRY_EXT.DATECHANGED,
JOURNALENTRY_EXT.TS,
JOURNALENTRY_EXT.TSLONG,
JOURNALENTRY_EXT.FULLYPAIDSTATUSCODE as FULLYPAIDSTATUS,
case CURRENCYSET.BASECURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else CURRENCYSET.BASECURRENCYID end as BASECURRENCYID,
JOURNALENTRY.ORGAMOUNT as ORGANIZATIONAMOUNT,
FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
JOURNALENTRY.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
case FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else FINANCIALTRANSACTION.TRANSACTIONCURRENCYID end as TRANSACTIONCURRENCYID,
FINANCIALTRANSACTION.BASEEXCHANGERATEID,
JOURNALENTRY_EXT.BENEFITTYPECODE as BENEFITTYPECODE,
JOURNALENTRY_EXT.BENEFITTYPE as BENEFITTYPE
from dbo.JOURNALENTRY
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 5
inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID