V_QUERY_BANKACCOUNTADJUSTMENT
This provides the ability to query for bank account adjustments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| ADJUSTMENTCATEGORY | nvarchar(60) | yes | Category |
| TRANSFERADJUSTMENTID | uniqueidentifier | yes | Transfer adjustment |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| AMOUNT | money | Amount | |
| TRANSACTIONTYPE | nvarchar(23) | yes | Type |
| POSTSTATUS | nvarchar(11) | yes | Post status |
| POSTDATE | datetime | yes | Post date |
| TRANSACTIONDATE | datetime | yes | Adjustment date |
| REFERENCE | nvarchar(100) | Reference | |
| BANKACCOUNTID | uniqueidentifier | Account ID | |
| ACCOUNTNAME | nvarchar(100) | yes | Transfer account |
| BANKACCOUNTNAME | nvarchar(100) | Account name | |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| EXCHANGERATEID | uniqueidentifier | yes | Transfer exchange rate |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency |
| BANKACCOUNTTRANSACTIONID | uniqueidentifier | Bank account transaction ID | |
| TRANSACTIONAMOUNT | money | Amount (transaction currency) | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 10/27/2018 10:42:52 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.186.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BANKACCOUNTADJUSTMENT AS
select
[BANKACCOUNTADJUSTMENT].[ID],
--[BANKACCOUNTADJUSTMENT].[BANKACCOUNTADJUSTMENTCATEGORYID],
--dbo.UFN_BANKACCOUNTADJUSTMENTCATEGORY_GETNAME([BANKACCOUNTADJUSTMENT].[BANKACCOUNTADJUSTMENTCATEGORYID]) as [ADJUSTMENTCATEGORY],
BANKACCOUNTADJUSTMENTCATEGORY.CATEGORY as [ADJUSTMENTCATEGORY],
[BANKACCOUNTADJUSTMENT].[TRANSFERADJUSTMENTID],
[BANKACCOUNTADJUSTMENT].[DATEADDED],
[BANKACCOUNTADJUSTMENT].[DATECHANGED],
[BANKACCOUNTADJUSTMENT].[TSLONG],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
FT.BASEAMOUNT as AMOUNT,
cast(BATX.TRANSACTIONTYPE as nvarchar(23)) as TRANSACTIONTYPE,
FT.POSTSTATUS,
cast(FT.POSTDATE as datetime) as POSTDATE,
cast(FT.[DATE] as datetime) as TRANSACTIONDATE,
isnull(cast(left(FT.DESCRIPTION,100) as nvarchar(100)),'') as REFERENCE,
[BATX].[BANKACCOUNTID],
TRANSFER.ACCOUNTNAME,
--dbo.UFN_BANKACCOUNT_GETACCOUNTNAME([BATX].[BANKACCOUNTID]) as BANKACCOUNTNAME,
BANKACCOUNT.ACCOUNTNAME as BANKACCOUNTNAME,
PDACCOUNTSYSTEM.NAME as [ACCOUNTSYSTEM]
,BANKACCOUNTADJUSTMENT.EXCHANGERATEID
,BANKACCOUNT.TRANSACTIONCURRENCYID
,FT.ID as BANKACCOUNTTRANSACTIONID
,FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
,FT.BASEEXCHANGERATEID as BASEEXCHANGERATEID
,case when FT.DELETEDON is null then V.BASECURRENCYID else null end as BASECURRENCYID
,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
,FT.ORGAMOUNT as ORGANIZATIONAMOUNT
from
dbo.[BANKACCOUNTADJUSTMENT]
inner join dbo.[FINANCIALTRANSACTION] FT on [BANKACCOUNTADJUSTMENT].ID = FT.ID
inner join dbo.BANKACCOUNTTRANSACTION_EXT BATX on BATX.ID = FT.ID
inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BATX.BANKACCOUNTID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
left outer join dbo.BANKACCOUNTADJUSTMENTCATEGORY on BANKACCOUNTADJUSTMENTCATEGORY.ID = [BANKACCOUNTADJUSTMENT].[BANKACCOUNTADJUSTMENTCATEGORYID]
left outer join dbo.BANKACCOUNT TRANSFER on BATX.TRANSFERBANKACCOUNTID = TRANSFER.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = BANKACCOUNTADJUSTMENT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = BANKACCOUNTADJUSTMENT.CHANGEDBYID
left outer join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID