Monday, December 28, 2009

How to create the Hierarchical Tree in oracle forms?


There are 2 ways to populate a hierarchical tree (1. Record Group, 2. Query Text)

Step1 - Create Record group
Step2 - Create hierarchical tree item (Control Block)
                         - WHEN-TREE-NODE-SELECTED
                         - WHEN-TREE-NODE-ACTIVATED
Step3 – Create data block

Step1 - Create Record group

The record Group query should be in the specified structure., which requires 5 Columns.

SELECT STATUS, LEVEL, LABEL, ICON, VALUE FROM TABLE;

STATUS - Indicates the initiate status of the Node (Normally Value is 1).
LEVEL - This is a specific pseudo-column Derived from “CONNECT BY”.
LABEL -This is the visible label of the Node.
ICON - That contains the icon name of the Node (can be NULL).
VALUE -That contains the value of the Node.



Step2 - Create hierarchical tree item (Control Block)



Add the trigger code @ WHEN-NEW-FORM-INSTANCE and WHEN-TREE-NODE-ACTIVATED


DECLARE
   vi_tree      item;
   vn_tree_rg   NUMBER;
BEGIN
   vi_tree      := FIND_ITEM ('TREE_BLK.EMP_TREE');
   vn_tree_rg   := POPULATE_GROUP ('EMP_TREE_RG');
   ftree.SET_TREE_PROPERTY ( vi_tree, ftree.record_group, 'EMP_TREE_RG');
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_message.debug (SQLERRM);
END;



Add the trigger code @ WHEN-TREE-NODE-SELECTED

DECLARE
   vi_htree        item;
   vc_node_value   VARCHAR2 (100);
BEGIN
   vi_htree        := FIND_ITEM ('TREE_BLK.EMP_TREE');
   vc_node_value   := ftree.GET_TREE_NODE_PROPERTY ( vi_htree, :SYSTEM.trigger_node, ftree.node_value);
   SET_BLOCK_PROPERTY ( 'emp', default_where, 'EMPNO = ' || vc_node_value);
   GO_BLOCK ('EMP');
   EXECUTE_QUERY;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_message.debug (SQLERRM);
END;

Step3 – Create data block





@ Runtime



16 comments:

  1. Helpful Topic. Thanks the author for share.

    ReplyDelete
  2. Thats Really help ful keep it up
    Thank You.

    ReplyDelete
  3. for your kind attention sir, if you describe this process with screenshot, i think, may be that was very helpful for us...

    ReplyDelete
  4. SELECT 1, level, ename, null , to_char(empno)
    FROM emp
    START WITH mgr IS NULL
    CONNECT BY PRIOR empno = mgr

    ReplyDelete
  5. Good Simple explanation with example

    ReplyDelete
  6. very helpful..... I followed the steps I don't know where I might have gone wrong the tree cannot show. only a form with an empty tree is displayed...where do u think I might have gone wrong ?

    regards

    Gladys

    ReplyDelete
  7. Respected SANKAR B

    Excellent method you show in your blog

    You explained a very Complex structure in a very easy mode.

    God bless you .....

    Regards
    Harsh shah

    ReplyDelete
  8. i got an error ..
    identifier 'find_message.debug' must be declare...
    plz sort me out

    ReplyDelete
  9. If You give the Table Srtucture then it will be more attractive for beginners.....Thanks

    ReplyDelete
  10. it is really simple steps and great

    ReplyDelete
  11. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete