USP_DATALIST_PROSPECTPLANANALYSIS
Fetches prospect plan analysis information.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | |
| @DATETOUSE | tinyint | IN | Date to use |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @PROSPECTPLANTYPECODEID | uniqueidentifier | IN | |
| @PROSPECTPLANSTATUSCODEID | uniqueidentifier | IN | |
| @INCLUDEINACTIVE | bit | IN | |
| @PROSPECTSTATUSCODEID | uniqueidentifier | IN | |
| @OPPORTUNITYSTATUSCODE | tinyint | IN | Opportunity status |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CURRENCYCODE | tinyint | IN | Currency |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PROSPECTPLANANALYSIS(
@SELECTIONID uniqueidentifier,
@DATETOUSE tinyint,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@PROSPECTPLANTYPECODEID uniqueidentifier = null,
@PROSPECTPLANSTATUSCODEID uniqueidentifier = null,
@INCLUDEINACTIVE bit = null,
@PROSPECTSTATUSCODEID uniqueidentifier = null,
@OPPORTUNITYSTATUSCODE tinyint = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@CURRENCYCODE tinyint = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as begin
set nocount on;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
declare @APPUSER_IN_NONSITEROLE bit;
declare @APPUSER_IN_NOSITEROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
if @SELECTIONID is not null
begin
select PROSPECT.ID as [PROSPECT_ID],
NF.NAME as [CONSTITUENT_NAME],
CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.FIRSTNAME as [CONSTITUENT_KEYNAME],
PROSPECTPLANTYPECODE.DESCRIPTION as [PROSPECTPLAN_TYPE],
NF_PROSPECTMANAGER.NAME as [PROSPECTMANAGER_NAME],
PROSPECTMANAGER.KEYNAME + ', ' + PROSPECTMANAGER.FIRSTNAME as [PROSPECTMANAGER_KEYNAME],
PROSPECTSTATUSCODE.DESCRIPTION as [PROSPECT_STATUS],
case @CURRENCYCODE
when 0 then OPPORTUNITY.EXPECTEDASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONEXPECTEDASKAMOUNT
else OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT
end as [EXPECTEDASKAMOUNT],
OPPORTUNITY.EXPECTEDASKDATE,
case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end as [ASKAMOUNT],
OPPORTUNITY.ASKDATE,
case OPPORTUNITY.STATUSCODE
when 3 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.AMOUNT
when 2 then OPPORTUNITY.TRANSACTIONAMOUNT
else OPPORTUNITY.ORGANIZATIONAMOUNT
end
else 0
end as [RESPONSEAMOUNT],
OPPORTUNITY.RESPONSEDATE,
(case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end -
case OPPORTUNITY.STATUSCODE
when 3 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.AMOUNT
when 2 then OPPORTUNITY.TRANSACTIONAMOUNT
else OPPORTUNITY.ORGANIZATIONAMOUNT
end
else 0
end) as [AMOUNT_VARIANCE],
datediff(dd, OPPORTUNITY.ASKDATE, OPPORTUNITY.RESPONSEDATE) as [DAYSOPEN],
OPPORTUNITY.STATUS as [OPPORTUNITY_STATUS],
PROSPECTPLANSTATUSCODE.DESCRIPTION as [PROSPECTPLAN_STATUS],
NF_PRIMARYMANAGER.NAME as [PRIMARYMANAGER_NAME],
PRIMARYMANAGER.KEYNAME + ', ' + PRIMARYMANAGER.FIRSTNAME as [PRIMARYMANAGER_SORT],
NF_SECONDARYMANAGER.NAME as [SECONDARYMANAGER_NAME],
SECONDARYMANAGER.KEYNAME + ', ' + SECONDARYMANAGER.FIRSTNAME as [SECONDARYMANAGER_SORT],
PROSPECTPLAN.ID as [PROSPECTPLAN_ID],
OPPORTUNITY.STATUSCODE,
case OPPORTUNITY.STATUSCODE
when 0 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.EXPECTEDASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONEXPECTEDASKAMOUNT
else OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT
end
else
case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end
end as [EXPECTEDASKAMOUNTORASKAMOUNT],
case @CURRENCYCODE
when 0 then OPPORTUNITY.BASECURRENCYID
when 2 then OPPORTUNITY.TRANSACTIONCURRENCYID
else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
end as CURRENCYID,
CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
CURRENCYPROPERTIES.CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
CURRENCYPROPERTIES.DECIMALDIGITS
from
dbo.PROSPECTPLAN
inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on PROSPECT.ID = SELECTION.ID
inner join dbo.CONSTITUENT on PROSPECT.ID = CONSTITUENT.ID
inner join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
left outer join dbo.CONSTITUENT as PROSPECTMANAGER on PROSPECT.PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGER.ID
left outer join dbo.PROSPECTSTATUSCODE on PROSPECT.PROSPECTSTATUSCODEID = PROSPECTSTATUSCODE.ID
left outer join dbo.PROSPECTPLANSTATUSCODE on PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = PROSPECTPLANSTATUSCODE.ID
left outer join dbo.CONSTITUENT as [PRIMARYMANAGER] on PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID = PRIMARYMANAGER.ID
left outer join dbo.CONSTITUENT as [SECONDARYMANAGER] on PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID = SECONDARYMANAGER.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE
when 0 then OPPORTUNITY.BASECURRENCYID
when 2 then OPPORTUNITY.TRANSACTIONCURRENCYID
else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
end) CURRENCYPROPERTIES
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTMANAGER.ID) NF_PROSPECTMANAGER
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PRIMARYMANAGER.ID) NF_PRIMARYMANAGER
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SECONDARYMANAGER.ID) NF_SECONDARYMANAGER
where
(@STARTDATE <= (case when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE <> 0 then OPPORTUNITY.ASKDATE
when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE = 0 then OPPORTUNITY.EXPECTEDASKDATE
else OPPORTUNITY.RESPONSEDATE end) and
@ENDDATE >= (case when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE <> 0 then OPPORTUNITY.ASKDATE
when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE = 0 then OPPORTUNITY.EXPECTEDASKDATE
else OPPORTUNITY.RESPONSEDATE end)
) and
(PROSPECTPLANTYPECODE.ID = @PROSPECTPLANTYPECODEID or @PROSPECTPLANTYPECODEID is null) and
(PROSPECTPLANSTATUSCODE.ID = @PROSPECTPLANSTATUSCODEID or @PROSPECTPLANSTATUSCODEID is null) and
(PROSPECTPLAN.ISACTIVE = 1 or @INCLUDEINACTIVE = 1) and
(PROSPECTSTATUSCODE.ID = @PROSPECTSTATUSCODEID or @PROSPECTSTATUSCODEID is null) and
(OPPORTUNITY.STATUSCODE = @OPPORTUNITYSTATUSCODE or @OPPORTUNITYSTATUSCODE is null) and
(@ISADMIN = 1 or
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
) and
(
(@SECURITYFEATUREID is null and @SECURITYFEATURETYPE is null) or
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
) > 0
)
end
else
begin
select PROSPECT.ID as [PROSPECT_ID],
NF.NAME as [CONSTITUENT_NAME],
CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.FIRSTNAME as [CONSTITUENT_KEYNAME],
PROSPECTPLANTYPECODE.DESCRIPTION as [PROSPECTPLAN_TYPE],
NF_PROSPECTMANAGER.NAME as [PROSPECTMANAGER_NAME],
PROSPECTMANAGER.KEYNAME + ', ' + PROSPECTMANAGER.FIRSTNAME as [PROSPECTMANAGER_KEYNAME],
PROSPECTSTATUSCODE.DESCRIPTION as [PROSPECT_STATUS],
case @CURRENCYCODE
when 0 then OPPORTUNITY.EXPECTEDASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONEXPECTEDASKAMOUNT
else OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT
end as [EXPECTEDASKAMOUNT],
OPPORTUNITY.EXPECTEDASKDATE,
case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end as [ASKAMOUNT],
OPPORTUNITY.ASKDATE,
case OPPORTUNITY.STATUSCODE
when 3 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.AMOUNT
when 2 then OPPORTUNITY.TRANSACTIONAMOUNT
else OPPORTUNITY.ORGANIZATIONAMOUNT
end
else 0
end as [RESPONSEAMOUNT],
OPPORTUNITY.RESPONSEDATE,
(case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end -
case OPPORTUNITY.STATUSCODE
when 3 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.AMOUNT
when 2 then OPPORTUNITY.TRANSACTIONAMOUNT
else OPPORTUNITY.ORGANIZATIONAMOUNT
end
else 0
end) as [AMOUNT_VARIANCE],
datediff(dd, OPPORTUNITY.ASKDATE, OPPORTUNITY.RESPONSEDATE) as [DAYSOPEN],
OPPORTUNITY.STATUS as [OPPORTUNITY_STATUS],
PROSPECTPLANSTATUSCODE.DESCRIPTION as [PROSPECTPLAN_STATUS],
NF_PRIMARYMANAGER.NAME as [PRIMARYMANAGER_NAME],
PRIMARYMANAGER.KEYNAME + ', ' + PRIMARYMANAGER.FIRSTNAME as [PRIMARYMANAGER_SORT],
NF_SECONDARYMANAGER.NAME as [SECONDARYMANAGER_NAME],
SECONDARYMANAGER.KEYNAME + ', ' + SECONDARYMANAGER.FIRSTNAME as [SECONDARYMANAGER_SORT],
PROSPECTPLAN.ID as [PROSPECTPLAN_ID],
OPPORTUNITY.STATUSCODE,
case OPPORTUNITY.STATUSCODE
when 0 then
case @CURRENCYCODE
when 0 then OPPORTUNITY.EXPECTEDASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONEXPECTEDASKAMOUNT
else OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT
end
else
case @CURRENCYCODE
when 0 then OPPORTUNITY.ASKAMOUNT
when 2 then OPPORTUNITY.TRANSACTIONASKAMOUNT
else OPPORTUNITY.ORGANIZATIONASKAMOUNT
end
end as [EXPECTEDASKAMOUNTORASKAMOUNT],
case @CURRENCYCODE
when 0 then OPPORTUNITY.BASECURRENCYID
when 2 then OPPORTUNITY.TRANSACTIONCURRENCYID
else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
end as CURRENCYID,
CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
CURRENCYPROPERTIES.CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
CURRENCYPROPERTIES.DECIMALDIGITS
from
dbo.PROSPECTPLAN
inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
inner join dbo.CONSTITUENT on PROSPECT.ID = CONSTITUENT.ID
inner join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
left outer join dbo.CONSTITUENT as PROSPECTMANAGER on PROSPECT.PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGER.ID
left outer join dbo.PROSPECTSTATUSCODE on PROSPECT.PROSPECTSTATUSCODEID = PROSPECTSTATUSCODE.ID
left outer join dbo.PROSPECTPLANSTATUSCODE on PROSPECTPLAN.PROSPECTPLANSTATUSCODEID = PROSPECTPLANSTATUSCODE.ID
left outer join dbo.CONSTITUENT as [PRIMARYMANAGER] on PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID = PRIMARYMANAGER.ID
left outer join dbo.CONSTITUENT as [SECONDARYMANAGER] on PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID = SECONDARYMANAGER.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE
when 0 then OPPORTUNITY.BASECURRENCYID
when 2 then OPPORTUNITY.TRANSACTIONCURRENCYID
else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
end) CURRENCYPROPERTIES
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTMANAGER.ID) NF_PROSPECTMANAGER
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PRIMARYMANAGER.ID) NF_PRIMARYMANAGER
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SECONDARYMANAGER.ID) NF_SECONDARYMANAGER
where
(@STARTDATE <= (case when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE <> 0 then OPPORTUNITY.ASKDATE
when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE = 0 then OPPORTUNITY.EXPECTEDASKDATE
else OPPORTUNITY.RESPONSEDATE end) and
@ENDDATE >= (case when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE <> 0 then OPPORTUNITY.ASKDATE
when @DATETOUSE = 0 and OPPORTUNITY.STATUSCODE = 0 then OPPORTUNITY.EXPECTEDASKDATE
else OPPORTUNITY.RESPONSEDATE end)
) and
(PROSPECTPLANTYPECODE.ID = @PROSPECTPLANTYPECODEID or @PROSPECTPLANTYPECODEID is null) and
(PROSPECTPLANSTATUSCODE.ID = @PROSPECTPLANSTATUSCODEID or @PROSPECTPLANSTATUSCODEID is null) and
(PROSPECTPLAN.ISACTIVE = 1 or @INCLUDEINACTIVE = 1) and
(PROSPECTSTATUSCODE.ID = @PROSPECTSTATUSCODEID or @PROSPECTSTATUSCODEID is null) and
(OPPORTUNITY.STATUSCODE = @OPPORTUNITYSTATUSCODE or @OPPORTUNITYSTATUSCODE is null) and
(@ISADMIN = 1 or
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
) and
(
(@SECURITYFEATUREID is null and @SECURITYFEATURETYPE is null) or
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
) > 0
)
end
return 0;
end