USP_DATALIST_OPPORTUNITYDESIGNATIONOUTSTANDINGREVENUE
List of designations associated with an opportunity which have not been fulfilled to be used for revenue form requests.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @OPPORTUNITYID | uniqueidentifier | IN | Opportunity ID |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_OPPORTUNITYDESIGNATIONOUTSTANDINGREVENUE
(
@OPPORTUNITYID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier
) as
set nocount on;
with OUTSTANDINGDESIGNATION(ID, DESIGNATIONID, NAME, AMOUNT) as
(
select
OD.ID,
OD.DESIGNATIONID,
dbo.UFN_DESIGNATION_GETLOOKUPID(OD.DESIGNATIONID) NAME,
OD.AMOUNT - coalesce((
select
sum(RS.AMOUNT)
from
dbo.REVENUEOPPORTUNITY RO
inner join
dbo.REVENUESPLIT RS on RS.ID = RO.ID
where
RO.OPPORTUNITYID = OD.OPPORTUNITYID
and
RS.DESIGNATIONID = OD.DESIGNATIONID
), 0)
from
dbo.OPPORTUNITYDESIGNATION OD
where
OD.OPPORTUNITYID = @OPPORTUNITYID
)
select
ID,
DESIGNATIONID,
NAME,
AMOUNT
from
OUTSTANDINGDESIGNATION
where
AMOUNT > 0
and
(
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
or
(dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATIONID)) = 1)
);