V_QUERY_TAXDECLARATION
Provides the ability to query tax declarations.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | Constituent | |
| DECLARATIONMADE | datetime | yes | Declaration made |
| DECLARATIONSTARTS | datetime | Declaration starts | |
| DECLARATIONENDS | datetime | yes | Declaration ends |
| DECLARATIONINDICATOR | nvarchar(8) | yes | Declaration indicator |
| DECLARATIONSOURCECODE | nvarchar(100) | yes | Declaration source |
| SCANNEDDOCSEXIST | bit | Scanned documents exist | |
| CONFIRMATIONSENT | datetime | yes | Confirmation sent |
| CONFIRMATIONRETURNED | datetime | yes | Confirmation returned |
| PAYSTAX | nvarchar(7) | yes | Pays tax |
| TAXSTATUSCODE | nvarchar(100) | yes | Tax status |
| COMMENTS | nvarchar(255) | Comments | |
| SITENAME | varchar(1) | Site | |
| ID | uniqueidentifier | System record 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 | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| REFERENCENUMBER | nvarchar(20) | yes | Reference number |
| DESCRIPTION | nvarchar(50) | yes | Description |
| NAMEOPTIONCODE | int | ||
| ALIASID | uniqueidentifier | yes | |
| ADDRESSOPTIONCODE | int | ||
| ADDRESSID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:53 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_TAXDECLARATION AS
select
TAXDECLARATION.CONSTITUENTID,
DECLARATIONMADE,
DECLARATIONSTARTS,
DECLARATIONENDS,
DECLARATIONINDICATOR,
coalesce((select DESCRIPTION from dbo.DECLARATIONSOURCECODE where ID = DECLARATIONSOURCECODEID), '') as DECLARATIONSOURCECODE,
SCANNEDDOCSEXIST,
CONFIRMATIONSENT,
CONFIRMATIONRETURNED,
PAYSTAX,
coalesce((select DESCRIPTION from dbo.TAXSTATUSCODE where ID = TAXSTATUSCODEID), '') as TAXSTATUSCODE,
COMMENTS,
-- Site is no longer referenced from TAXDECLARATION but keeping the output field for
-- backwards compatibility
'' as SITENAME,
TAXDECLARATION.ID,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
TAXDECLARATION.DATEADDED,
TAXDECLARATION.DATECHANGED,
TAXDECLARATION.TSLONG,
coalesce((select REFERENCENUMBER from dbo.CHARITYCLAIMREFERENCENUMBER where ID = TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID), '') as REFERENCENUMBER,
coalesce((select DESCRIPTION from dbo.CHARITYCLAIMREFERENCENUMBER where ID = TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID), '') as DESCRIPTION,
case when ALIASID is null then 0 else 1 end as [NAMEOPTIONCODE],
TAXDECLARATION.ALIASID as [ALIASID],
case when ADDRESSID is null then 0 else 1 end as [ADDRESSOPTIONCODE],
case when TAXDECLARATION.ADDRESSID is null then ADDRESS.ID else TAXDECLARATION.ADDRESSID end as [ADDRESSID]
/*#EXTENSION*/
from
dbo.TAXDECLARATION
inner join
dbo.CONSTITUENT on CONSTITUENT.ID = TAXDECLARATION.CONSTITUENTID
left join
dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
left join
dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = TAXDECLARATION.ADDEDBYID
left join
dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = TAXDECLARATION.CHANGEDBYID;