V_QUERY_CONSTITUENTACCOUNT
This provides the ability to query constituent financial account information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System Record ID |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| ACCOUNTTYPE | nvarchar(8) | yes | Account type |
| EFTSTATUS | nvarchar(10) | yes | EFT status |
| FINANCIALINSTITUTIONID | uniqueidentifier | yes | Financial institution ID |
| 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 |
| DATEADDED | datetime | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| ACCOUNTNAME | nvarchar(100) | yes | Name on account |
| ACCOUNTNUMBER | nvarchar(50) | yes | Account number |
| ACCOUNTNUMBER_MASKED | nvarchar(4000) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/21/2016 5:07:44 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.154.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTACCOUNT AS
select
CONSTITUENTACCOUNT.ID,
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENTACCOUNT.ACCOUNTTYPE,
CONSTITUENTACCOUNT.EFTSTATUS,
CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID,
coalesce([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
coalesce([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
coalesce([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
coalesce([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
CONSTITUENTACCOUNT.DATEADDED,
CONSTITUENTACCOUNT.DATECHANGED,
CONSTITUENTACCOUNT.TSLONG,
CONSTITUENTACCOUNT.ACCOUNTNAME,
coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'') as ACCOUNTNUMBER,
case
when len(coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'')) = 0 then ''
else replicate('*', len(coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'')) -
len(right(coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),''), 4))) +
right(coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),''), 4)
end as ACCOUNTNUMBER_MASKED
from
dbo.CONSTITUENT
left join
dbo.CONSTITUENTACCOUNT
on
CONSTITUENTACCOUNT.CONSTITUENTID = CONSTITUENT.ID
left join
dbo.CHANGEAGENT as [ADDEDBY]
on
[ADDEDBY].ID = CONSTITUENTACCOUNT.ADDEDBYID
left join
dbo.CHANGEAGENT as [CHANGEDBY]
on
[CHANGEDBY].ID = CONSTITUENTACCOUNT.CHANGEDBYID