USP_DATALIST_OPPORTUNITYDESIGNATIONOUTSTANDING

List of designations associated with an opportunity which have not been fulfilled.
Parameters

Parameter	Parameter Type	Mode	Description
@OPPORTUNITYID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_OPPORTUNITYDESIGNATIONOUTSTANDING
                (
                    @OPPORTUNITYID 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;