Building a treetable-like form
I made myself familiar with ADF two years ago. Doing so i also had a look at the different components provided by the ADF-faces-UI. I found one component named TreeTable very nice and useful. A TreeTable component shows hierarchical data in a tabular way, where the first column of the table "contains" the hierarchy and can be used to expand, collapse and drill into the data. The big difference against a simple tree is that the hierarchical part of the data is "synchronized" with the tabular data. So, when scrolling through the data, the tree-part moves along with the table-part.
So, as an "old" forms-developer, i tried to create a similar functionality in forms.
The HR-schema is a good example, for the EMPLOYEES-table provided with the schema contains a hierarchical structure. Each employee has assigned a manager, which in turn also is an employee again. The "top-managers", which are the topmost-level in the hierarchy do not have a manager assigned. So as an example we will build a form which shows all the employees in a tabular view with the possibility to expand and collapse each row so that the "detail"-employees of an employee are shown/hidden in the same block.
The hierarchical query is not a problem to implement, but how deal with the dynamic loading and hiding of details to an already queried record. I played a while trying to create missing records "by hand" and integrate them into the already queried data in the block, but if found it to be to complicated (at least, when the block should be updateable). So i tried a different approach:
Whenever a record should be expanded or collapsed, the whole block gets requeried and the cursor gets navigated to the previously select record.
The query has to be adjusted every time the block is requeried so that it contains only the data which should be shown in the block. With each requery, there is either one master expanded, which in turn leads to all detail-employees have to be included in the query, or one master is collapsed, which leads to all detail-employees to be excluded from the query.
To do so, i decided to use an table-type-object containing all id's of the "expanded" employees, and this table-type-object then can be included in the query. To store the instance of the object i use a database-package.
Last part is to include or exclude a master-id to this object from the form.
First part is to create an object-type to hold the id's:
CREATE OR REPLACE TYPE T_IDLIST AS TABLE OF NUMBER;
Second part is to create a package to "store" an instance of this object-type, which also includes methods to add an id to the type and to remove it again.
CREATE OR REPLACE PACKAGE PK_TREETABLE IS -- Method to reset the list PROCEDURE PR_RESET; -- Method to retrieve the object FUNCTION FK_GET_EXPANDED RETURN T_IDLIST; -- Method to toggle the given id -- If the id is not in the list, include it, else exclude it PROCEDURE PR_TOGGLE(i_nId IN NUMBER); END;
And the body
CREATE OR REPLACE PACKAGE BODY PK_TREETABLE IS
oIds T_IDLIST:=T_IDLIST(-1);
PROCEDURE PR_RESET IS
BEGIN
-- re-Instantiate the type, given only -1 for the root
oIds:=T_IDLIST(-1);
END;
FUNCTION FK_GET_EXPANDED
RETURN T_IDLIST IS
BEGIN
-- return the internal instance
RETURN oIds;
END;
PROCEDURE PR_TOGGLE(i_nId IN NUMBER) IS
bFound BOOLEAN:=FALSE;
iPos PLS_INTEGER;
BEGIN
-- check if, the given id is part of the list
iPos:=oIds.FIRST;
LOOP
EXIT WHEN iPos IS NULL;
IF oIds(iPos)=i_nId THEN
-- found, delete it
oIds.DELETE(iPos);
-- and remember
bFound:=TRUE;
END IF;
iPos:=oIds.NEXT(iPos);
END LOOP;
-- If not found
IF NOT bFound THEN
-- add it to the list
oIds.EXTEND(1);
oIds(oids.COUNT):=i_nId;
END IF;
END;
END;
Now we need the view to base our forms-block on
CREATE OR REPLACE VIEW V_EMP
(
STATUS,
MASTER,
EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
EMAIL,
HIRE_DATE
) AS
SELECT CAST('?' AS VARCHAR2(200)) STATUS,
NVL(MANAGER_ID,-1) MASTER,
EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
EMAIL,
HIRE_DATE
FROM EMPLOYEES
WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
)
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME
Now, we create a simple tabular form based on that view and implement a WHEN-MOUSE-DOUBLECLICK-trigger which does the toggling of the id in the package and the requery-part:
PK_TREETABLE.PR_TOGGLE(:V_EMP.EMPLOYEE_ID); EXECUTE_QUERY;
For now, there is a status-field showing just a question-mark. Lets make it a little nicer by showing a + when a node is collapsed and a minus when it is expanded. We have to adjust the view for that:
CREATE OR REPLACE VIEW V_EMP
(
STATUS,
MASTER,
EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
EMAIL,
HIRE_DATE
) AS
SELECT RPAD(' ', LEVEL-1, ' ') ||
CASE WHEN SELECTED_EMP IS NULL THEN
'+'
ELSE
'-'
END STATUS,
NVL(MANAGER_ID,-1) MASTER,
EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
EMAIL,
HIRE_DATE
FROM EMPLOYEES,
(SELECT COLUMN_VALUE SELECTED_EMP
FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
)
WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
)
AND EMPLOYEE_ID=SELECTED_EMP(+)
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME
The GUI for the tree is not satisfying by now, we have to deal with some additional requirements:
-
Show the expand and collapse nodes in a graphical way
-
Show tree-lines
-
Show icons for each node
I'm curently working on building a java-bean for this, i'll show the results in my next post.
/image%2F1176854%2F20161229%2Fob_af6d94_pl-jrxml2pdf-barcodes.png)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/idata%2F2654307%2Fjrxml_variables.png)
