V_QUERY_OPPORTUNITY
Opportunities
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| PROSPECTPLANID | uniqueidentifier | Plan ID | |
| EXPECTEDASKAMOUNT | money | Expected ask amount | |
| ASKAMOUNT | money | Ask amount | |
| AMOUNT | money | yes | Amount |
| STATUS | nvarchar(16) | yes | Status |
| EXPECTEDASKDATE | datetime | yes | Expected ask date |
| ASKDATE | datetime | yes | Ask date |
| RESPONSEDATE | datetime | yes | Response date |
| COMMENT | nvarchar(max) | Comments | |
| LIKELIHOODTYPECODEID | uniqueidentifier | yes | Likelihood ID |
| LIKELIHOODTYPECODE | nvarchar(100) | yes | Likelihood |
| OPPORTUNITYTYPECODEID | uniqueidentifier | yes | Opportunity type ID |
| OPPORTUNITYTYPE | nvarchar(100) | yes | Opportunity type |
| 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 |
| REVENUECOMMITTED | money | yes | Revenue committed |
| ACCEPTEDAMOUNT | money | yes | Accepted ask amount |
| LIKELIHOODPERCENT | tinyint | yes | Likelihood percent |
| BASECURRENCYID | uniqueidentifier | yes | BASECURRENCYID |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | TRANSACTIONCURRENCYID |
| ORGANIZATIONEXPECTEDASKAMOUNT | money | Expected ask amount (organization currency) | |
| TRANSACTIONEXPECTEDASKAMOUNT | money | Expected ask amount (transaction currency) | |
| ORGANIZATIONASKAMOUNT | money | Ask amount (organization currency) | |
| TRANSACTIONASKAMOUNT | money | Ask amount (transaction currency) | |
| TRANSACTIONAMOUNT | money | yes | Amount (transaction currency) |
| BASEEXCHANGERATEID | uniqueidentifier | yes | BASEEXCHANGERATEID |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | ORGANIZATIONEXCHANGERATEID |
| ORGANIZATIONAMOUNT | money | yes | Amount (organization currency) |
| TRANSACTIONACCEPTEDASKAMOUNT | money | Accepted ask amount (transaction currency) | |
| ORGANIZATIONACCEPTEDASKAMOUNT | money | Accepted ask amount (organization currency) | |
| TOTALPAIDAMOUNT | money | yes | |
| ADDEDBYID | uniqueidentifier | yes | |
| CHANGEDBYID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 12/15/2016 8:40:59 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.165.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_OPPORTUNITY AS
select
OPPORTUNITY.ID,
OPPORTUNITY.PROSPECTPLANID,
OPPORTUNITY.EXPECTEDASKAMOUNT,
OPPORTUNITY.ASKAMOUNT,
OPPORTUNITY.AMOUNT,
OPPORTUNITY.STATUS,
OPPORTUNITY.EXPECTEDASKDATE,
OPPORTUNITY.ASKDATE,
OPPORTUNITY.RESPONSEDATE,
OPPORTUNITY.COMMENT,
OPPORTUNITY.LIKELIHOODTYPECODEID,
(select LIKELIHOODTYPECODE.DESCRIPTION from dbo.LIKELIHOODTYPECODE where OPPORTUNITY.LIKELIHOODTYPECODEID = LIKELIHOODTYPECODE.ID) [LIKELIHOODTYPECODE],
OPPORTUNITY.OPPORTUNITYTYPECODEID,
(select DESCRIPTION from dbo.OPPORTUNITYTYPECODE where ID = OPPORTUNITY.OPPORTUNITYTYPECODEID) as OPPORTUNITYTYPE,
OPPORTUNITY.DATEADDED,
OPPORTUNITY.DATECHANGED,
OPPORTUNITY.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
dbo.UFN_OPPORTUNITY_REVENUECOMMITTEDINCURRENCY(OPPORTUNITY.ID, OPPORTUNITY.BASECURRENCYID) as REVENUECOMMITTED,
case OPPORTUNITY.STATUSCODE when 3 then OPPORTUNITY.AMOUNT else 0 end as ACCEPTEDAMOUNT,
(select LIKELIHOODPERCENT.[PERCENT] from dbo.LIKELIHOODPERCENT where OPPORTUNITY.LIKELIHOODTYPECODEID = LIKELIHOODPERCENT.ID) [LIKELIHOODPERCENT],
OPPORTUNITY.BASECURRENCYID,
OPPORTUNITY.TRANSACTIONCURRENCYID,
OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT,
OPPORTUNITY.TRANSACTIONEXPECTEDASKAMOUNT,
OPPORTUNITY.ORGANIZATIONASKAMOUNT,
OPPORTUNITY.TRANSACTIONASKAMOUNT,
OPPORTUNITY.TRANSACTIONAMOUNT,
OPPORTUNITY.BASEEXCHANGERATEID,
OPPORTUNITY.ORGANIZATIONEXCHANGERATEID,
OPPORTUNITY.ORGANIZATIONAMOUNT,
OPPORTUNITY.TRANSACTIONACCEPTEDASKAMOUNT,
OPPORTUNITY.ORGANIZATIONACCEPTEDASKAMOUNT,
dbo.UFN_OPPORTUNITY_GETAMOUNTPAID_INCURRENCY(OPPORTUNITY.ID, OPPORTUNITY.BASECURRENCYID) as TOTALPAIDAMOUNT,
ADDEDBY.ID as ADDEDBYID,
CHANGEDBY.ID as CHANGEDBYID
/*#EXTENSION*/
from dbo.OPPORTUNITY
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = OPPORTUNITY.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = OPPORTUNITY.CHANGEDBYID