USP_DATALIST_OPPORTUNITIESTOBERECONCILED

A datalist of accepted opportunities with an accepted amount that is zero or that doesn't match the revenue committed.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



  CREATE procedure dbo.USP_DATALIST_OPPORTUNITIESTOBERECONCILED
  (
      @CURRENTAPPUSERID uniqueidentifier,
      @SITEFILTERMODE tinyint = 0,
      @SITESSELECTED xml = null,
      @SECURITYFEATUREID uniqueidentifier = null,
      @SECURITYFEATURETYPE tinyint = null
  )
  as 
      set nocount on;

  with CTP_OPPURTUNITY as
  (
   select  
    OPPORTUNITY.ID,  
    NF.NAME as PROSPECTNAME,  
    PROSPECTPLAN.NAME as PLANNAME,  
    OPPORTUNITY.AMOUNT as ACCEPTEDAMOUNT,  
    OPPORTUNITY.RESPONSEDATE as ACCEPTEDDATE,
    dbo.UFN_OPPORTUNITY_REVENUECOMMITTED(OPPORTUNITY.ID) as REVENUECOMMITTED,  
    dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as SITES,  
                         OPPORTUNITY.BASECURRENCYID  
   from  
    dbo.OPPORTUNITY  
    inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID  
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF  
   where  
    PROSPECTPLAN.ISACTIVE = 1 and  
    OPPORTUNITY.STATUSCODE = 3 and     -- Accepted  

    OPPORTUNITY.AMOUNT = 0 
   and (  
     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  
   and 
   (  
     @SITEFILTERMODE = 0  
       or PROSPECTPLAN.ID in (  
       select PROSPECTPLANSITE.PROSPECTPLANID  
       from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER  
       inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID  
        )  
    ) 
  )
  select * from  CTP_OPPURTUNITY
  where REVENUECOMMITTED <> 0
  order by  
  CTP_OPPURTUNITY.PROSPECTNAME asc;