V_QUERY_BANKACCOUNT
This provides the ability to query for bank accounts.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| ACCOUNTNAME | nvarchar(100) | Account name | |
| ACCOUNTNUMBER | nvarchar(50) | yes | Account number |
| ROUTINGNUMBER | nvarchar(4000) | Routing number | |
| STATUS | nvarchar(6) | yes | Status |
| ACCOUNTTYPE | nvarchar(8) | yes | Account type |
| MINIMUMBALANCE | decimal(19, 4) | Minimum balance | |
| DEFAULTROUTINGNUMBER | nvarchar(4000) | Default routing number | |
| DEFAULTSORTCODE | nvarchar(8) | Default sort code | |
| BANKNAME | nvarchar(154) | yes | Bank |
| BANKID | uniqueidentifier | Bank ID | |
| CASHACCOUNT | uniqueidentifier | yes | Account |
| ACCOUNTCODE | nvarchar(100) | yes | Account code |
| USERNUMBER | nvarchar(24) | User number | |
| CLIENTNAME | nvarchar(100) | Client name | |
| SORTCODE | nvarchar(8) | Sort code | |
| 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 |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Currency |
| IMMEDIATEDESTINATIONNUMBER | nvarchar(10) | yes | Immediate destination |
| IMMEDIATEDESTINATIONNAME | nvarchar(23) | yes | Immediate destination name |
| IMMEDIATEORIGINNUMBER | nvarchar(4000) | yes | Immediate origin |
| IMMEDIATEORIGINNAME | nvarchar(23) | yes | Immediate origin name |
| REFERENCECODE | nvarchar(8) | yes | Reference code |
| COMPANYNAME | nvarchar(16) | yes | Company name |
| COMPANYIDPREFIX | tinyint | yes | Company ID prefix |
| COMPANYID | nvarchar(9) | yes | Company ID |
| CREATEOFFSETTINGTRANSACTIONS | bit | yes | Create offsetting transactions |
| INCLUDECARRIAGERETURN | bit | yes | Include carriage return/line feed |
| DEFAULTBIC | nvarchar(11) | ||
| DEFAULTBANKCODE | nvarchar(25) | ||
| DEFAULTBANKINGSYSTEMID | uniqueidentifier | yes | |
| DEFAULTBANKINGSYSTEMNAME | nvarchar(50) | yes | |
| BIC | nvarchar(11) | ||
| BANKCODE | nvarchar(25) | ||
| BANKINGSYSTEMID | uniqueidentifier | yes | |
| BANKINGSYSTEMNAME | nvarchar(50) | yes |
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_BANKACCOUNT AS
select
BANKACCOUNT.ID,
BANKACCOUNT.ACCOUNTNAME,
convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)) [ACCOUNTNUMBER],
BANKACCOUNT.ROUTINGNUMBER,
BANKACCOUNT.STATUS,
BANKACCOUNT.ACCOUNTTYPE,
BANKACCOUNT.MINIMUMBALANCE,
BANK.DEFAULTROUTINGNUMBER,
BANK.DEFAULTSORTCODE,
(SELECT CONSTITUENT.NAME FROM DBO.CONSTITUENT WHERE ID = BANKACCOUNT.BANKID) BANKNAME,
BANKACCOUNT.BANKID,
BANKACCOUNT.GLACCOUNTID as CASHACCOUNT,
PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as ACCOUNTCODE,
BANKACCOUNT.USERNUMBER,
BANKACCOUNT.CLIENTNAME,
BANKACCOUNT.SORTCODE,
BANKACCOUNT.DATEADDED,
BANKACCOUNT.DATECHANGED,
BANKACCOUNT.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
(select PDACCOUNTSYSTEM.NAME from dbo.PDACCOUNTSYSTEM where PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID) as [ACCOUNTSYSTEM],
BANKACCOUNT.TRANSACTIONCURRENCYID,
EFT.IMMEDIATEDESTINATIONNUMBER,
EFT.IMMEDIATEDESTINATIONNAME,
case EFT.IMMEDIATEORIGINNUMBEROPTIONCODE when 0 then BANKACCOUNT.ROUTINGNUMBER when 1 then LEFT((select O.EIN from dbo.ORGANIZATIONINFORMATION O), 9) else EFT.IMMEDIATEORIGINNUMBER end [IMMEDIATEORIGINNUMBER],
case EFT.IMMEDIATEORIGINNAMEOPTIONCODE when 0 then LEFT((select O.NAME from dbo.ORGANIZATIONINFORMATION O), 23) else EFT.IMMEDIATEORIGINNAME end [IMMEDIATEORIGINNAME],
EFT.REFERENCECODE,
case EFT.COMPANYNAMEOPTIONCODE when 0 then LEFT((select O.NAME from dbo.ORGANIZATIONINFORMATION O), 16) else EFT.COMPANYNAME end [COMPANYNAME],
EFT.COMPANYIDPREFIX,
EFT.COMPANYID,
EFT.CREATEOFFSETTINGTRANSACTIONS,
EFT.INCLUDECARRIAGERETURN,
BANK.DEFAULTBIC,
BANK.DEFAULTBANKCODE,
BANK.DEFAULTBANKINGSYSTEMID,
DEFAULTBANKINGSYSTEM.NAME as DEFAULTBANKINGSYSTEMNAME,
BANKACCOUNT.BIC,
BANKACCOUNT.BANKCODE,
BANKACCOUNT.BANKINGSYSTEMID,
BANKINGSYSTEM.NAME as BANKINGSYSTEMNAME
from dbo.BANKACCOUNT
inner join dbo.BANK on BANK.ID = BANKACCOUNT.BANKID
left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BANKACCOUNT.BANKINGSYSTEMID
left join dbo.BANKINGSYSTEM as DEFAULTBANKINGSYSTEM on DEFAULTBANKINGSYSTEM.ID = BANK.DEFAULTBANKINGSYSTEMID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = BANKACCOUNT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = BANKACCOUNT.CHANGEDBYID
left join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID
left join dbo.BANKACCOUNTEFTINFO EFT on BANKACCOUNT.ID = EFT.ID