For instance, if the user selects top most organization in hierarchy, the pay slips of all the children and sub children must be displayed in the same way if user selects a child organization then pay slips of that child and all of its sub children must be displayed.
I just need to write the query for getting this parent/child relationship on SQL, according to user input.
Let's say you have an organization hierarchy table like this:
ORGANIZATION_HIEARARCHIES (ORG_HIER_ID NUMBER NOT NULL, CHILD_ORGANIZATION_ID NUMBER NOT NULL, PARENT_ORGANIZATION_ID NUMBER)
You also have an ORGANIZATIONS table that is something like this:
ORGANIZATION (ORGANIZATION_ID NUMBER NOT NULL, ORGANIZATION_NAME VARCHAR2(100) NOT NULL)
ORGANIZATION_ID is a foreign key to both CHILD_ORGANIZATION_ID column and PARENT_ORGANIZAITON_ID column.
Anyway, to provide a hierarchy that starts with a child_organization_id and displays all of its children and grand children you can use the following SQL statement.
SELECT t1.organization_id, RPAD (' ', 3*(level-1))||t1.organization_name displayed_organization FROM organizations t1, organization_hierarchies t2 WHERE t2.child_organization_id = t1.organization_id CONNECT BY t2.parent_organization_id = prior t2.child_organization_id START WITH t2.child_organization_id = :PARAMETER_ORG_ID ORDER SIBLINGS BY SORT_ORDER
Please note that the ORDER SIBLINGS BY SORT_ORDER clause is a new feature in Oracle9i SQL that enables you to order siblings in a hierarchical tree that was not available in earlier version of Oracle.
This was first published in March 2004