V_QUERY_SPONSORSHIPOPPORTUNITYCHILD
This provides the ability to query all sponsorship opportunity child fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| FIRSTNAME | nvarchar(50) | First name | |
| MIDDLENAME | nvarchar(50) | Middle name | |
| LASTNAME | nvarchar(100) | Last name | |
| BIRTHDATE | UDT_FUZZYDATE | Birth date | |
| ISHIVPOSITIVE | bit | HIV positive | |
| ISORPHANED | bit | Orphaned | |
| AGE | int | yes | Age |
| GENDER | nvarchar(7) | yes | Gender |
| NAME | nvarchar(154) | yes | Name |
| ELIGIBILITY | nvarchar(10) | yes | Eligibility |
| DESCRIPTION | nvarchar(100) | yes | Disability/Illness |
| 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 |
| REASON | nvarchar(100) | yes | Ineligible reason |
| PICTURE | varbinary | yes | PICTURE |
| PICTURETHUMBNAIL | varbinary | yes | PICTURETHUMBNAIL |
| RESERVATIONKEY | nvarchar(100) | yes | Reservation key |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| GROUPID | uniqueidentifier | yes | Group ID |
| SPONSORSHIPREASONID | uniqueidentifier | yes | |
| SPROPPCHILDCONDITIONCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/21/2016 5:07:41 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.154.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPOPPORTUNITYCHILD AS
select
SPONSORSHIPOPPORTUNITYCHILD.ID,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.KEYNAME as LASTNAME,
CONSTITUENT.BIRTHDATE,
SPONSORSHIPOPPORTUNITYCHILD.ISHIVPOSITIVE,
SPONSORSHIPOPPORTUNITYCHILD.ISORPHANED,
CONSTITUENT.AGE,
CONSTITUENT.GENDER,
NF.NAME,
SPONSORSHIPOPPORTUNITY.ELIGIBILITY,
SPROPPCHILDCONDITIONCODE.DESCRIPTION,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SPONSORSHIPOPPORTUNITYCHILD.DATEADDED,
SPONSORSHIPOPPORTUNITYCHILD.DATECHANGED,
SPONSORSHIPOPPORTUNITYCHILD.TSLONG,
SPONSORSHIPREASON.REASON,
CONSTITUENT.PICTURE,
CONSTITUENT.PICTURETHUMBNAIL,
SPONSORSHIPOPPORTUNITYRESERVEPROCESS.NAME as RESERVATIONKEY,
SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID,
GROUPMEMBER.GROUPID,
SPONSORSHIPREASON.ID as SPONSORSHIPREASONID,
SPROPPCHILDCONDITIONCODE.ID as SPROPPCHILDCONDITIONCODEID
from dbo.SPONSORSHIPOPPORTUNITYCHILD
inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIPOPPORTUNITYCHILD.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID
left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
left join dbo.SPROPPCHILDCONDITIONCODE on SPROPPCHILDCONDITIONCODE.ID = SPONSORSHIPOPPORTUNITYCHILD.SPROPPCHILDCONDITIONCODEID
left join dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPREASONID
left join dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS on SPONSORSHIPOPPORTUNITYRESERVEPROCESS.ID = SPONSORSHIPOPPORTUNITY.RESERVATIONKEYID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIPOPPORTUNITYCHILD.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIPOPPORTUNITYCHILD.CHANGEDBYID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF