Original query:

--Func
CREATE OR REPLACE FUNCTION public.ecs_get_eprescribing_entities_with_latti_longi(eprescribing_entitiesid integer, entity_name character varying, no_of_rec integer, no_of_page integer, latti character varying, longi character varying)
 RETURNS SETOF ecs_get_eprescribing_entities_with_latti_longi_type
 LANGUAGE plpgsql
AS $function$------================================================
 ------< Author	      :	Sudeep                      >
 ------< Create date  :                       >
 ------< Description  :	Procedure to get eprescribing entities   >
 ------< modified by  : 
 ------< modified on  : 
 ------====================================================
--null,null,100,0,'13.080785518106978','77.55965096707715'
--null,null,100,0,'33.5116061','-84.2220886'
--13.080785518106978,77.55965096707715
---5,null,10,0,'12.9719208','77.5946105'
--1,null,10,0,null,null
DECLARE
total_count BIGINT;
total_count1 BIGINT;
BEGIN
if (eprescribing_entitiesid=2) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;

ELSIF(eprescribing_entitiesid=3) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;


ELSIF(eprescribing_entitiesid=1) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	SELECT count(cs.customer_site_id)
	from ecs_customer_site cs
	where cs.product_id=4 AND	
			--	(to_number(cs.lattittude,'99D999999999') between to_number(COALESCE(latti,cs.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,cs.lattittude),'99D999999999')+1 )
--	and (to_number(cs.longittude,'999D999999999') between to_number(COALESCE(longi,cs.longittude),'999D999999999')-1 and to_number(COALESCE(longi,cs.longittude),'999D999999999')+1 )and
	cs.site_name ILIKE ('%'||COALESCE(entity_name,cs.site_name)||'%') into total_count1;
	total_count=total_count+total_count1;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit 
	

	UNION

		SELECT mt.name,cs.customer_site_id,cs.site_name,cs.address,cs.phone,
	cs.email,null::character varying(100),total_count,cs.lattittude::character varying(50),cs.longittude::character varying(50),(coalesce(cs.lattittude,'')||' '||coalesce(cs.longittude,''))::character varying(50),((select * from ecs_get_distance(longi,latti,cs.longittude,cs.lattittude)) ||' KM') ::varchar
	from ecs_customer_site cs
	LEFT JOIN ecs_master_types mt on mt.type_id=1
	WHERE 	--(to_number(cs.lattittude,'99D999999999') between to_number(COALESCE(latti,cs.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,cs.lattittude),'99D999999999')+1 )
	--and (to_number(cs.longittude,'999D999999999') between to_number(COALESCE(longi,cs.longittude),'999D999999999')-1 and to_number(COALESCE(longi,cs.longittude),'999D999999999')+1 )and
		cs.site_name ILIKE ('%'||COALESCE(entity_name,cs.site_name)||'%')  and  cs.is_active=1::bit and cs.product_id=4--order by  cs.customer_site_id ASC
	LIMIT no_of_rec OFFSET no_of_page;





ELSE
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id=eprescribing_entitiesid and h.is_active=1::bit into total_count; 

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM')::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id=eprescribing_entitiesid and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;
END IF;
END;
$function$
;

Prettified:

CREATE OR REPLACE FUNCTION "public"."ecs_get_eprescribing_entities_with_latti_longi"(int, varchar, int, int, varchar, varchar) RETURNS SETOF ecs_get_eprescribing_entities_with_latti_longi_type language "plpgsql" AS $$------================================================
 ------< Author	      :	Sudeep                      >
 ------< Create date  :                       >
 ------< Description  :	Procedure to get eprescribing entities   >
 ------< modified by  : 
 ------< modified on  : 
 ------====================================================
--null,null,100,0,'13.080785518106978','77.55965096707715'
--null,null,100,0,'33.5116061','-84.2220886'
--13.080785518106978,77.55965096707715
---5,null,10,0,'12.9719208','77.5946105'
--1,null,10,0,null,null
DECLARE
total_count BIGINT;
total_count1 BIGINT;
BEGIN
if (eprescribing_entitiesid=2) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;

ELSIF(eprescribing_entitiesid=3) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;


ELSIF(eprescribing_entitiesid=1) Then
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit into total_count;

	SELECT count(cs.customer_site_id)
	from ecs_customer_site cs
	where cs.product_id=4 AND	
			--	(to_number(cs.lattittude,'99D999999999') between to_number(COALESCE(latti,cs.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,cs.lattittude),'99D999999999')+1 )
--	and (to_number(cs.longittude,'999D999999999') between to_number(COALESCE(longi,cs.longittude),'999D999999999')-1 and to_number(COALESCE(longi,cs.longittude),'999D999999999')+1 )and
	cs.site_name ILIKE ('%'||COALESCE(entity_name,cs.site_name)||'%') into total_count1;
	total_count=total_count+total_count1;

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM') ::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id IN (eprescribing_entitiesid,4) and h.is_active=1::bit 
	

	UNION

		SELECT mt.name,cs.customer_site_id,cs.site_name,cs.address,cs.phone,
	cs.email,null::character varying(100),total_count,cs.lattittude::character varying(50),cs.longittude::character varying(50),(coalesce(cs.lattittude,'')||' '||coalesce(cs.longittude,''))::character varying(50),((select * from ecs_get_distance(longi,latti,cs.longittude,cs.lattittude)) ||' KM') ::varchar
	from ecs_customer_site cs
	LEFT JOIN ecs_master_types mt on mt.type_id=1
	WHERE 	--(to_number(cs.lattittude,'99D999999999') between to_number(COALESCE(latti,cs.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,cs.lattittude),'99D999999999')+1 )
	--and (to_number(cs.longittude,'999D999999999') between to_number(COALESCE(longi,cs.longittude),'999D999999999')-1 and to_number(COALESCE(longi,cs.longittude),'999D999999999')+1 )and
		cs.site_name ILIKE ('%'||COALESCE(entity_name,cs.site_name)||'%')  and  cs.is_active=1::bit and cs.product_id=4--order by  cs.customer_site_id ASC
	LIMIT no_of_rec OFFSET no_of_page;





ELSE
	SELECT count(h.type_id)
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id=eprescribing_entitiesid and h.is_active=1::bit into total_count; 

	RETURN QUERY
	SELECT mt.name,h.eprescribing_entities_id,h.name,h.address,h.land_phone1,
	h.email,h.website,total_count,h.lattittude,h.longittude,h.location,((select * from ecs_get_distance(longi,latti,h.longittude,h.lattittude)) ||' KM')::varchar
	from ecs_eprescribing_entities h
	LEFT JOIN ecs_master_types mt on mt.type_id=h.type_id
	WHERE (to_number(h.lattittude,'99D999999999') between to_number(COALESCE(latti,h.lattittude),'99D999999999')-1 and to_number(COALESCE(latti,h.lattittude),'99D999999999')+1 )
	and (to_number(h.longittude,'999D999999999') between to_number(COALESCE(longi,h.longittude),'999D999999999')-1 and to_number(COALESCE(longi,h.longittude),'999D999999999')+1 )and
	h.name ILIKE ('%'||COALESCE(entity_name,h.name)||'%') and h.type_id=eprescribing_entitiesid and h.is_active=1::bit order by  h.type_id ASC
	LIMIT no_of_rec OFFSET no_of_page;
END IF;
END;
$$