CREATE OR REPLACE FUNCTION dbo.fgetschoolhierarchy(
	par_schoolid integer DEFAULT NULL::integer)
    RETURNS table(entityid1 integer, entityname1 varchar, entityid2 integer, entityname2 varchar, entityid3 integer, entityname3 varchar, entityid4 integer, entityname4 varchar, entityid5 integer, entityname5 varchar, entityid6 integer, entityname6 varchar, entityid7 integer, entityname7 varchar, schoolentityid integer, schoolid integer, emiscode varchar, schoolname varchar, reportentityid1 integer, reportentityname1 varchar, reportentityid2 integer, reportentityname2 varchar)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

BEGIN
    /* DROP TABLE IF EXISTS fGetSchoolHierarchy$TMPTBL;
    /* SET NOCOUNT ON */
    CREATE TEMPORARY TABLE fGetSchoolHierarchy$TMPTBL
    AS*/
	
	RETURN QUERY
    SELECT
        eh.entityid1, 
		eh.entityname1, 
		eh.entityid2, 
		eh.entityname2, 
		eh.entityid3, 
		eh.entityname3, 
		eh.entityid4, 
		eh.entityname4, 
		eh.entityid5, 
		eh.entityname5, 
		eh.entityid6, 
		eh.entityname6, 
		eh.entityid7, 
		eh.entityname7, 
		eh.schoolentityid, 
		eh.schoolid, 
		eh.emiscode, 
		eh.schoolname, 
		rh.entityid1 AS reportentityid1, 
		rh.entityname1 AS reportentityname1, 
		rh.entityid2 AS reportentityid2, 
		rh.entityname2 AS reportentityname2
        FROM dbo.vwentityhierarchyname AS eh
        JOIN dbo.entity AS e1
            ON eh.entityid1 = e1.entityid AND e1.entitytypeid = 1
        CROSS JOIN (SELECT
            hd.rootentityid
            FROM dbo.hierarchydefinition AS hd
            JOIN dbo.entity AS e
                ON hd.rootentityid = e.entityid
            WHERE hd.relatedrootentityid = e1.entityid AND hd.entitytypeid = 7 AND LOWER(e.name) SIMILAR TO LOWER('%Report Regions%')) AS rx
        LEFT OUTER JOIN dbo.vwentityhierarchyname AS rh
            ON rx.rootentityid = rh.entityid1 AND rh.schoolid = eh.schoolid
        WHERE eh.schoolid = par_SchoolID
        ORDER BY eh.entityid1 DESC NULLS FIRST
        LIMIT 1;
END;
$BODY$;

ALTER FUNCTION dbo.fgetschoolhierarchy(integer)
    OWNER TO postgres;