Posted on Leave a comment

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;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.