Home » Developer & Programmer » Forms » Populate Nth level of Hierarchical tree
Populate Nth level of Hierarchical tree [message #257765] Thu, 09 August 2007 02:32 Go to next message
habib.khan
Messages: 20
Registered: March 2007
Location: Islamabad
Junior Member
Hi all,
I am using oracle 10g form developer and i want to populate Nth level or node of hierarchical tree.i mean 4 or 5 or any level that a user want.
To populate 2 level is solved but i want a generic structure or syntex to populate nth level tree.
Any one,who can solve this problem,Please send me a record group query to populate tree.
With a lot of thanks.
Regards
Muhammad Habib
Re: Populate Nth level of Hierarchical tree [message #259064 is a reply to message #257765] Tue, 14 August 2007 05:55 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
Do you need to poplation to be

on adding a new record?
or
at startup of the form?
Re: Populate Nth level of Hierarchical tree [message #259700 is a reply to message #259064] Thu, 16 August 2007 05:01 Go to previous messageGo to next message
habib.khan
Messages: 20
Registered: March 2007
Location: Islamabad
Junior Member
Hi
Actually i want to populate tree for five,six level.and i want a general code to do this for users requirement.Please give me instruction or code to do so.
thanks in advance.
Regards
Muhammad Habib
Re: Populate Nth level of Hierarchical tree [message #259796 is a reply to message #259700] Thu, 16 August 2007 09:23 Go to previous message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
This one works for 5 Levels. It creates Parent Nodes only when required.
Here is the code.

PROCEDURE POPULATE_TREE IS
   CURSOR v_level1 IS
      SELECT DISTINCT ctrl_acc_name, ctrl_acc
        FROM t911_fa_ctrl_acc
       WHERE acc_level = '00'
    ORDER BY ctrl_acc;

   CURSOR v_level2(v_acc_lev1  VARCHAR2) IS
      SELECT DISTINCT ctrl_acc_name, ctrl_acc
        FROM t911_fa_ctrl_acc
       WHERE acc_level  = '0000'
         AND acc_level1 = v_acc_lev1
       ORDER BY ctrl_acc_name;

   CURSOR v_level3(v_acc_lev1  VARCHAR2, v_acc_lev2  VARCHAR2) IS
      SELECT DISTINCT ctrl_acc_name, ctrl_acc
        FROM t911_fa_ctrl_acc
       WHERE acc_level = '000000'
         AND acc_level1||acc_level2 = v_acc_lev1||v_acc_lev2
    ORDER BY ctrl_acc_name;

   CURSOR v_level4(v_ctrl_acc  VARCHAR2) IS
      SELECT DISTINCT acc_name, acc_code
        FROM t915_fa_heads
       WHERE t911_ctrl_acc = v_ctrl_acc
    ORDER BY acc_name;

   CURSOR c_ast_ref(v_ctrl_acc  VARCHAR2, v_acc_head VARCHAR2) IS
      SELECT DISTINCT asset_ref_desc, asset_ref_no
        FROM t931_fixed_assets
       WHERE asset_ctrl_acc = v_ctrl_acc
         AND asset_acc_code = v_acc_head
    ORDER BY asset_ref_desc;
   v_count   NUMBER;
 
   v_i            number;
   v_ignore       number;

   rg_sa        RECORDGROUP;

   v_init_state   GROUPCOLUMN;
   v_level        GROUPCOLUMN;
   v_label        GROUPCOLUMN;
   v_icon         GROUPCOLUMN;
   v_value        GROUPCOLUMN;
   
   v_node      FTREE.NODE;
begin
   rg_sa := FIND_GROUP('acc_chart');
   IF NOT ID_NULL(rg_sa) THEN
   	  DELETE_GROUP(rg_sa);
   END IF;
     
   rg_sa := CREATE_GROUP('acc_chart');
    
   v_init_state := ADD_GROUP_COLUMN(rg_sa, 'init_state', NUMBER_COLUMN); 
   v_level      := ADD_GROUP_COLUMN(rg_sa, 'level',      NUMBER_COLUMN);
   v_label      := ADD_GROUP_COLUMN(rg_sa, 'label',      CHAR_COLUMN, 80);
   v_icon       := ADD_GROUP_COLUMN(rg_sa, 'icon',       CHAR_COLUMN, 20);
   v_value      := ADD_GROUP_COLUMN(rg_sa, 'value',      CHAR_COLUMN, 15);
   
   v_i := 1;
   ADD_GROUP_ROW(rg_sa, v_i);
   SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
   SET_GROUP_NUMBER_CELL(v_level     , v_i, 1);
   SET_GROUP_CHAR_CELL  (v_label     , v_i, ' CHART OF ACCOUNTS');
   SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
   SET_GROUP_CHAR_CELL  (v_value     , v_i, 'XXX');
   v_i := v_i + 1;
   FOR clev1 in v_level1 loop
   	  ADD_GROUP_ROW(rg_sa, v_i);
      SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
      SET_GROUP_NUMBER_CELL(v_level     , v_i, 2);
      SET_GROUP_CHAR_CELL  (v_label     , v_i, ' '||clev1.ctrl_acc_name);
      SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
      SET_GROUP_CHAR_CELL  (v_value     , v_i, clev1.ctrl_acc);
      v_i := v_i + 1;
      
      SELECT COUNT(*)
        INTO v_count
        FROM t911_fa_ctrl_acc
       WHERE acc_level1 = SUBSTR(clev1.ctrl_acc, 1, 2)
         AND acc_level3 = '00'
         AND acc_level2 != '00';
      IF v_count > 0 THEN
         FOR clev2 in v_level2(SUBSTR(clev1.ctrl_acc, 1, 2)) loop
       	    ADD_GROUP_ROW(rg_sa, v_i);
       	    SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
            SET_GROUP_NUMBER_CELL(v_level     , v_i, 3);
            SET_GROUP_CHAR_CELL  (v_label     , v_i, ' '||clev2.ctrl_acc_name);
            SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
            SET_GROUP_CHAR_CELL  (v_value     , v_i, clev2.ctrl_acc);
            v_i := v_i + 1;
         
            SELECT COUNT(*)
              INTO v_count
              FROM t911_fa_ctrl_acc
             WHERE acc_level1  = SUBSTR(clev2.ctrl_acc, 1, 2)
               AND acc_level2  = SUBSTR(clev2.ctrl_acc, 3, 2)
               AND acc_level3 != '00';
            IF v_count > 0 THEN
               FOR clev3 in v_level3(SUBSTR(clev2.ctrl_acc, 1, 2), SUBSTR(clev2.ctrl_acc, 3, 2)) loop
          	      ADD_GROUP_ROW(rg_sa, v_i);
       	          SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
                  SET_GROUP_NUMBER_CELL(v_level     , v_i, 4);
                  SET_GROUP_CHAR_CELL  (v_label     , v_i, ' '||clev3.ctrl_acc_name);
                  SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
                  SET_GROUP_CHAR_CELL  (v_value     , v_i, clev3.ctrl_acc);
                  v_i := v_i + 1;
            
                  SELECT COUNT(*)
                    INTO v_count
                    FROM t915_fa_heads
                   WHERE t911_ctrl_acc = clev3.ctrl_acc;
                  IF v_count > 0 THEN
                     FOR clev4 in v_level4(clev3.ctrl_acc) loop
          	            ADD_GROUP_ROW(rg_sa, v_i);
       	                SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
                        SET_GROUP_NUMBER_CELL(v_level     , v_i, 5);
                        SET_GROUP_CHAR_CELL  (v_label     , v_i, ' '||clev4.acc_name);
                        SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
                        SET_GROUP_CHAR_CELL  (v_value     , v_i, clev4.acc_code);
                        v_i := v_i + 1;
                       	 FOR car IN c_ast_ref(clev3.ctrl_acc, clev4.acc_code) LOOP
          	              ADD_GROUP_ROW(rg_sa, v_i);
       	                      SET_GROUP_NUMBER_CELL(v_init_state, v_i, 1);
                              SET_GROUP_NUMBER_CELL(v_level     , v_i, 6);
                              SET_GROUP_CHAR_CELL  (v_label     , v_i, ' '||car.asset_ref_desc);
                              SET_GROUP_CHAR_CELL  (v_icon      , v_i, NULL);
                              SET_GROUP_CHAR_CELL  (v_value     , v_i, car.asset_ref_no);
                              v_i := v_i + 1;
                           END LOOP;
                        END IF;
                     END LOOP;
                  END IF;
               END LOOP;
               
               
            END IF;
         END LOOP;
      END IF;
   END LOOP; 
   FTREE.SET_TREE_PROPERTY('acc_tree.tree', FTREE.RECORD_GROUP, rg_sa);
   
END;



Excuse the alignment it gets a little "out of line" in the middle.

- Hemangi
Previous Topic: Java applet runs within Oracle form
Next Topic: how to hide currnt window when new form opens(want to see only one form at a time,no other window)
Goto Forum:
  


Current Time: Fri Mar 29 01:20:11 CDT 2024