USP_DATALIST_FUNDINGREQUESTSINPIPELINE

Summary of unawarded funding requests.
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN	Show for
@TOTAL	int	INOUT
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_FUNDINGREQUESTSINPIPELINE
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
                    @TOTAL int = null output
                )
                as begin
                    set nocount on;

                    declare @SUMMARYTABLE table
                    (
                        ID uniqueidentifier,
                        DESCRIPTION nvarchar(100),
                        SEQUENCE int,
                        COUNT int
                    );

                    if @ORGPOSITIONSSELECTIONID is null
                    begin
                        insert into @SUMMARYTABLE (ID, DESCRIPTION, SEQUENCE, COUNT)
                        select 
                            coalesce(FUNDINGREQUESTSTAGECODE.ID,'00000000-0000-0000-0000-000000000001') as ID,
                            coalesce(FUNDINGREQUESTSTAGECODE.DESCRIPTION, 'No current stage') as DESCRIPTION,
                            FUNDINGREQUESTSTAGECODE.SEQUENCE,
                            count(*) as COUNT
                        from dbo.FUNDINGREQUEST
                            left join dbo.FUNDINGREQUESTSTAGECODE on FUNDINGREQUESTSTAGECODE.ID = FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID
                        where not exists
                            (
                                select ID
                                from dbo.REVENUEFUNDINGREQUEST
                                where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                            )
                        group by
                            coalesce(FUNDINGREQUESTSTAGECODE.ID,'00000000-0000-0000-0000-000000000001'),
                            coalesce(FUNDINGREQUESTSTAGECODE.DESCRIPTION, 'No current stage'),
                            FUNDINGREQUESTSTAGECODE.SEQUENCE;
                    end
                    else
                    begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

                        declare @TODAY datetime = getdate();

                        insert into @SUMMARYTABLE (ID, DESCRIPTION, SEQUENCE, COUNT)
                        select 
                            coalesce(FUNDINGREQUESTSTAGECODE.ID,'00000000-0000-0000-0000-000000000001') as ID,
                            coalesce(FUNDINGREQUESTSTAGECODE.DESCRIPTION, 'No current stage') as DESCRIPTION,
                            FUNDINGREQUESTSTAGECODE.SEQUENCE,
                            count(*) as COUNT
                        from dbo.FUNDINGREQUEST
                            inner join dbo.ORGANIZATIONPOSITIONHOLDER 
                                on ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = FUNDINGREQUEST.PRIMARYMANAGERID 
                                    and @TODAY between ORGANIZATIONPOSITIONHOLDER.DATEFROM and coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @TODAY)
                            inner join @IDS as SELECTION on ORGANIZATIONPOSITIONHOLDER.ID = SELECTION.ID
                            left join dbo.FUNDINGREQUESTSTAGECODE on FUNDINGREQUESTSTAGECODE.ID = FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID
                        where not exists
                            (
                                select ID
                                from dbo.REVENUEFUNDINGREQUEST
                                where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                            )
                        group by
                            coalesce(FUNDINGREQUESTSTAGECODE.ID,'00000000-0000-0000-0000-000000000001'),
                            coalesce(FUNDINGREQUESTSTAGECODE.DESCRIPTION, 'No current stage'),
                            FUNDINGREQUESTSTAGECODE.SEQUENCE;
                    end

                    select
                        ID,
                        DESCRIPTION,
                        COUNT
                    from @SUMMARYTABLE
                    order by SEQUENCE;

                    select @TOTAL = sum(COUNT) from @SUMMARYTABLE;
                end