Example Function to Look up Top Level Org

CREATE OR REPLACE FUNCTION f_getTopOrg

( orgID IN MYSCHEMA.t_organization.organizationid%TYPE ) 

RETURN MYSCHEMA.t_organization.organizationid%TYPE 
IS

    pOrgID MYSCHEMA.t_organization.organizationid%TYPE;

    cOrgID MYSCHEMA.t_organization.organizationid%TYPE;
BEGIN

    pOrgID := orgID;

    WHILE pOrgID IS NOT NULL

     LOOP

       cOrgID := pOrgID;

       SELECT A.parentorganizationid

         INTO pOrgID

         FROM MYSCHEMA.t_organization A

        WHERE A.organizationid = cOrgID;

     END LOOP;

    RETURN(cOrgID);

END;