CREATE PROCEDURE {sp_name}
	 @bReturnCount BIT = 0, 
	 @bReturnID BIT = 0, 
	 @PageIndex INT = 1, 
	 @PageSize INT = 10
AS
BEGIN

DECLARE @FromIndex INT,@ToIndex INT
SET @FromIndex = (@PageIndex * @PageSize) - (@PageSize - 1)
SET @ToIndex = (@PageIndex * @PageSize)
CREATE TABLE #TmpQueryProspects(ProspectID INT,ObjectTypeID INT) 


INSERT INTO #TmpQueryProspects 
{select_query}


IF (@bReturnCount = 1)
BEGIN
--	SELECT COUNT(DISTINCT ProspectID) as TotalCount --Commented By Thilina
	SELECT COUNT(ProspectID) as TotalCount -- Added By Thilina
	FROM #TmpQueryProspects

	--SELECT COUNT(DISTINCT tcpl.OwnerID) as ExistingCount --Commented By Thilina
	SELECT DISTINCT tcpl.OwnerID , tcpl.ProspectTypeID -- Added By Thilina
	INTO #tmpExistingCount -- Added By Thilina
	FROM dbo.tblCampaignProspectList tcpl WITH (NOLOCK) 
	INNER JOIN 
	(SELECT ProspectID,
	(CASE WHEN ObjectTypeID = 2 THEN 1 WHEN ObjectTypeID = 30 THEN 2 END) [ObjectTypeID]
	FROM #TmpQueryProspects) tqp ON tcpl.OwnerID = tqp.ProspectID
	INNER JOIN dbo.tblCampaign tcmp WITH (NOLOCK) ON tcpl.CampaignID = tcmp.CampaignID
	AND (tcmp.CampaignStatusID = 2 OR tcmp.CampaignStatusID = 5)
	AND tcpl.ProspectTypeID = tqp.ObjectTypeID AND ISNULL(tcpl.bRemoved,0) = 0

	SELECT count(1) as ExistingCount from #tmpExistingCount-- Added By Thilina
    DROP TABLE #tmpExistingCount -- Added By Thilina
	
	DECLARE @pid INT = 0,@ptype INT = 0,@ctype INT = 0
	SELECT TOP 1 @pid = ProspectID,@ptype = ObjectTypeID FROM #TmpQueryProspects
	IF(@ptype = 2)
	BEGIN
	    SELECT @ctype = tcus.CustomerTypeID
	    FROM dbo.tblCustomer tcus WITH (NOLOCK)
	    WHERE tcus.CustomerID = @pid
	END
	ELSE IF(@ptype = 30)
     BEGIN
         SELECT @ctype = tcus.CustomerTypeID
	    FROM dbo.tblLead tcus WITH (NOLOCK)
	    WHERE tcus.LeadID = @pid
     END
	SELECT @ctype AS CustomerType

	SELECT SUM(tbl.bMarketingByPhone) [MarketingPhoneCount],SUM(bMarketingByEmail) [MarketingEmailCount]
	FROM (SELECT CASE WHEN ISNULL(tcon.bMarketingByPhone,0) = 1 THEN 1 ELSE 0 END [bMarketingByPhone],
	CASE WHEN ISNULL(tcon.bMarketingByEmail,0) = 1 THEN 1 ELSE 0 END [bMarketingByEmail]
	FROM #TmpQueryProspects tqp
	INNER JOIN dbo.tblCustomer tc WITH (NOLOCK) ON tqp.ProspectID = tc.CustomerID
	AND tqp.ObjectTypeID = 2 --customer
	INNER JOIN tblContact tcon WITH ( NOLOCK ) ON tc.CustomerID = tcon.OwnerID
		AND ISNULL(tcon.bPrimaryContact,0) = 1 AND tcon.ObjectTypeID = 2
	UNION ALL
	SELECT CASE WHEN ISNULL(tlc.bMarketingByPhone,0) = 1 THEN 1 ELSE 0 END [bMarketingByPhone],
	CASE WHEN ISNULL(tlc.bMarketingByEmail,0) = 1 THEN 1 ELSE 0 END [bMarketingByEmail]
	FROM #TmpQueryProspects tqp
	INNER JOIN dbo.tblLead tl WITH (NOLOCK) ON tqp.ProspectID = tl.LeadID
	AND tqp.ObjectTypeID = 30
    INNER JOIN dbo.tblLeadContact tlc WITH ( NOLOCK ) ON tl.LeadID = tlc.OwnerID
            AND ISNULL(tlc.bPrimaryContact,0) = 1 AND tlc.ObjectTypeID = 30) tbl
END

IF(@bReturnID = 1)
BEGIN
	SELECT DISTINCT ProspectID,ObjectTypeID 
	FROM #TmpQueryProspects
	ORDER BY ObjectTypeID,ProspectID
END

END