USP_PROSPECTPLAN_GETOPPORTUNITYSUMMARY

Parameters

Parameter	Parameter Type	Mode
@PROSPECTPLANID	uniqueidentifier	IN
@UNQUALIFIEDCOUNT	int	INOUT
@UNQUALIFIEDAMOUNT	money	INOUT
@QUALIFIEDCOUNT	int	INOUT
@QUALIFIEDAMOUNT	money	INOUT
@RESPONSEPENDINGCOUNT	int	INOUT
@RESPONSEPENDINGAMOUNT	money	INOUT
@ACCEPTEDCOUNT	int	INOUT
@ACCEPTEDAMOUNT	money	INOUT
@REJECTEDCOUNT	int	INOUT
@REJECTEDAMOUNT	money	INOUT
@CANCELEDCOUNT	int	INOUT
@CANCELEDAMOUNT	money	INOUT
@UNQUALIFIEDID	uniqueidentifier	INOUT
@QUALIFIEDID	uniqueidentifier	INOUT
@RESPONSEPENDINGID	uniqueidentifier	INOUT
@ACCEPTEDID	uniqueidentifier	INOUT
@REJECTEDID	uniqueidentifier	INOUT
@CANCELEDID	uniqueidentifier	INOUT
Definition

Copy


            create procedure dbo.USP_PROSPECTPLAN_GETOPPORTUNITYSUMMARY(
            @PROSPECTPLANID uniqueidentifier,
            @UNQUALIFIEDCOUNT int = 0 output, 
            @UNQUALIFIEDAMOUNT money = 0 output, 
            @QUALIFIEDCOUNT int = 0 output, 
            @QUALIFIEDAMOUNT money = 0 output, 
            @RESPONSEPENDINGCOUNT int = 0 output,
            @RESPONSEPENDINGAMOUNT money = 0 output, 
            @ACCEPTEDCOUNT int = 0 output,
            @ACCEPTEDAMOUNT money = 0 output, 
            @REJECTEDCOUNT int = 0 output,
            @REJECTEDAMOUNT money = 0 output, 
            @CANCELEDCOUNT int = 0 output,
            @CANCELEDAMOUNT money = 0 output, 
            @UNQUALIFIEDID uniqueidentifier = null output,
            @QUALIFIEDID uniqueidentifier = null output,
            @RESPONSEPENDINGID uniqueidentifier = null output,
            @ACCEPTEDID uniqueidentifier = null output,
            @REJECTEDID uniqueidentifier = null output,
            @CANCELEDID uniqueidentifier = null output
        )
        as
        begin
            set nocount on;
            -- Get unqualified opportunity ask

            select 
                @UNQUALIFIEDCOUNT = count(*),
                @UNQUALIFIEDAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 0

            -- Get qualified opportunity ask

            select
                @QUALIFIEDCOUNT = count(*),
                @QUALIFIEDAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 1

            -- Get response pending opportunity ask

            select
                @RESPONSEPENDINGCOUNT = count(*),
                @RESPONSEPENDINGAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 2

            -- Get accepted opportunity ask

            select
                @ACCEPTEDCOUNT = count(ID),
                @ACCEPTEDAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 3

            -- Get rejected opportunity ask

            select
                @REJECTEDCOUNT = count(ID),
                @REJECTEDAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 4

            -- Get canceled opportunity ask

            select
                @CANCELEDCOUNT = count(*),
                @CANCELEDAMOUNT = coalesce(sum(AMOUNT), 0)
            from OPPORTUNITY
            where PROSPECTPLANID = @PROSPECTPLANID 
                and STATUSCODE = 5

            -- Get opportunity ids only if single opportunity exists of that status


            if(@UNQUALIFIEDCOUNT = 1)
                select 
                    @UNQUALIFIEDID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 0

            if(@QUALIFIEDCOUNT = 1)
                select 
                    @QUALIFIEDID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 1

            if(@RESPONSEPENDINGCOUNT = 1)
                select 
                    @RESPONSEPENDINGID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 2

            if(@ACCEPTEDCOUNT = 1)
                select 
                    @ACCEPTEDID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 3

            if(@REJECTEDCOUNT = 1)
                select 
                    @REJECTEDID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 4

            if(@CANCELEDCOUNT = 1)
                select 
                    @CANCELEDID = ID 
                from OPPORTUNITY
                where PROSPECTPLANID = @PROSPECTPLANID 
                    and STATUSCODE = 5
            return 0;
        end