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;