DBA Data[Home] [Help]

PACKAGE BODY: APPS.OKC_DBTREE_PVT

Source


1 PACKAGE BODY okc_dbtree_pvt AS
2 /*$Header: OKCTREEB.pls 120.1 2006/02/17 06:51:34 maanand noship $*/
3 
4 	l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 
6 --===================
7 -- TYPES
8 --===================
9 -- add your type declarations here if any
10 --
11 
12 
13 --===================
14 -- CONSTANTS
15 --===================
16    G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKCTREE';
17 --
18 -- add your constants here if any
19 
20 --===================
21 -- GLOBAL VARIABLES
22 --===================
23 -- add your private global variables here if any
24 --
25       MaxDataRecs      NUMBER;
26 
27 --
28 -- ========================================================
29 -- PRIVATE PROCEDURES AND FUNCTIONS
30 -- ========================================================
31 --
32 
33 --
34 --
35 --
36 -- ---------------------------------------------------------------------------------
37 -- PROCEDURE:  SaveNodeIndex                                                      --
38 -- PURPOSE:   Updates the start/stop index of the node's children in the          --
39 --            IControltable for the tree id and node number passed                --
40 -- DEPENDENCIES: the IControlTable has been intitialized                          --
41 --                                                                                --
42 -- ---------------------------------------------------------------------------------
43 --
44 PROCEDURE SaveNodeIndex
45               (p_tree_id	IN	NUMBER
46                ,p_node_number   IN      NUMBER
47                ,p_start		IN	NUMBER
48                ,p_end           IN      NUMBER
49               )
50 IS
51       i		NUMBER := 0;
52       node_updated   BOOLEAN;
53 
54 BEGIN
55   i := 0;
56   node_updated := FALSE;
57 
58   FOR i in IControlTable.FIRST..IControlTable.LAST LOOP
59      IF IControlTable.EXISTS(i) THEN
60           IF (IControlTable(i).tree_id = p_tree_id AND
61              IControlTable(i).node_number = p_node_number) THEN
62                  IControlTable(i).start_ind := p_start;
63                  IControlTable(i).end_ind := p_end;
64                  IControlTAble(i).num_entries := p_end - p_start + 1;
65                  IControLTable(i).current_set := 1;
66                  node_updated := TRUE;
67                  EXIT;
68           END IF;
69      END IF;
70   END LOOP;
71 
72 --
73 --   if no nodes were updated, then add one to the end of the table
74 --
75   IF NOT node_updated THEN
76        IControlTable.EXTEND;
77        i := IControlTable.LAST;
78        IControlTable(i).tree_id := p_tree_id;
79        IControlTable(i).node_number := p_node_number;
80        IControlTable(i).start_ind := p_start;
81        IControlTable(i).end_ind := p_end;
82        IControlTable(i).num_entries := p_end - p_start + 1;
83        IControlTable(i).current_set := 1;
84    END IF;
85 
86 EXCEPTION
87   WHEN OTHERS THEN
88       raise_application_error(-20407,'Exception in SaveNodeIndex: ' || to_char(p_tree_id)
89                                || ':' || to_char(p_node_number)
90                                || ':' || to_char(p_start)
91                                || ':' || to_char(p_end) ,TRUE );
92 END    SaveNodeIndex;
93 --
94 --
95 --
96 -- ---------------------------------------------------------------------------------
97 -- FUNCTION:  GetNodeIndex                                                        --
98 -- PURPOSE:   Gets the requested attribute from IControlTable for the             --
99 --            tree/level/node number passed                                       --
100 --                                                                                --
101 --                                                                                --
102 -- ---------------------------------------------------------------------------------
103 --
104 FUNCTION GetNodeIndex
105               ( p_tree_id	IN	NUMBER
106                ,p_node_number	IN	NUMBER
107                ,p_type		IN	VARCHAR2
108               )
109 RETURN NUMBER IS
110     i		       NUMBER;
111 BEGIN
112   i := 0;
113   FOR i IN IControlTable.FIRST..IControlTable.LAST LOOP
114     IF (IControlTable(i).tree_id = p_tree_id
115        AND IControlTable(i).node_number = p_node_number) THEN
116        IF p_type = 'START' THEN
117           return IControlTAble(i).start_ind;
118        ELSIF p_type = 'END' THEN
119           return IControlTAble(i).end_ind;
120        ELSIF p_type = 'ENTRY' THEN
121           return IControlTAble(i).num_entries;
122        ELSIF p_type = 'SET' THEN
123 	  return IControLTable(i).current_set;
124        END IF;
125     END IF;
126   END LOOP;
127 
128   return -1;
129 EXCEPTION
130   WHEN OTHERS THEN
131        raise_application_error(-20408,'Exception in GetNodeIndex: ' || to_char(p_tree_id)
132                                 || ':' || to_char(p_node_number)
133                                 || ':' || p_type, TRUE );
134 END      GetNodeIndex;
135 --
136 --
137 --
138 -- ---------------------------------------------------------------------------------
139 -- FUNCTION:  get_data_parameter                                                  --
140 -- PURPOSE:   Returns the requested parameter from the data string passed.        --
141 --            Paramters are separated by '\'. Parameter names passed are:         --
142 --                   TYPE - parameter 1 - node type (ROOT,GRP,KHDR)               --
143 --                   LEVEL - parameter 2 - level of the node 0 = root             --
144 --                   NODEID - parameter 3 - index# for node in tDataTable         --
145 --                   CHILD - parameter 4 - #of children for this node             --
146 --                   OCCUR - parameter 5 - id value of the corresponding row      --
147 --                                                                                --
148 -- ---------------------------------------------------------------------------------
149 --
150 FUNCTION    get_data_parameter
151                 (p_data_string          IN      VARCHAR2,
152                  p_parm_name            IN      VARCHAR2
153                 )
154 RETURN VARCHAR2 IS
155       x                       NUMBER := 0;
156       plen                    NUMBER := 0;
157       parmno                  NUMBER := 0;
158 BEGIN
159   IF p_parm_name = 'TYPE' THEN
160      parmno := 1;
161   ELSIF p_parm_name = 'LEVEL' THEN
162      parmno := 2;
163   ELSIF p_parm_name = 'NODEID' THEN
164      parmno := 3;
165   ELSIF p_parm_name = 'CHILD' THEN
166      parmno := 4;
167   ELSIF p_parm_name = 'OCCUR' THEN
168      parmno := 5;
169   END IF;
170 
171   IF parmno < 1 THEN
172      raise_application_error(-20106,'Invalid Parmater Name received', TRUE);
173   END IF;
174 
175   x := INSTR(p_data_string,'\',1,parmno) + 1;
176   plen := INSTR(p_data_string,'\',1,parmno+1) - x;
177 
178   return  SUBSTR(p_data_string,x,plen);
179 
180 EXCEPTION
181    WHEN OTHERS THEN
182 	raise_application_error(-20105,'Failure in get_data_parameter: ' || p_parm_name
183 					|| ':' || substr(p_data_string,1,150), TRUE);
184 
185 END     get_data_parameter;
186 
187 --
188 --
189 --
190 -- ---------------------------------------------------------------------------------
191 -- PROCEDURE:  DeleteControlRec                                                   --
192 -- PURPOSE:    Deletes the row corresponding to the tree_id, node_number passed   --
193 --             from the IControlTable                                             --
194 -- Dependencies: None                                                             --
195 -- ---------------------------------------------------------------------------------
196 --
197 PROCEDURE DeleteControlRec
198                 (p_tree_id        IN   NUMBER
199                  ,p_node_number   IN   NUMBER)
200 IS
201     i             NUMBER := 0;
202 BEGIN
203    i := IControlTable.FIRST;
204    WHILE i <= IControlTable.LAST LOOP
205 	IF (IControlTable(i).tree_id = p_tree_id
206             AND IControlTable(i).node_number = p_node_number) THEN
207 		IControlTable.DELETE(i);
208                 EXIT;
209 	END IF;
210 	i := IControltable.NEXT(i);
211    END LOOP;
212 
213 EXCEPTION
214    WHEN OTHERS THEN
215 	raise_application_error(-20195,'Failure inDeleteControlRec: ' || to_char(p_tree_id)
216 					|| ':' || to_char(p_node_number), TRUE);
217 
218 END   DeleteControlRec;
219 
220 --
221 --
222 --
223 -- ========================================================
224 -- PUBLIC PROCEDURES AND FUNCTIONS
225 -- ========================================================
226 --
227 --
228 --
229 PROCEDURE push_error
230 		(p_package_name		IN	VARCHAR2
231 		,p_program_name		IN	VARCHAR2
232 		,p_entry_point		IN	NUMBER
233 		,p_error_type		IN	VARCHAR2
234 		,p_error_msg		IN	VARCHAR2
235 		 )
236 IS
237 BEGIN
238 	null;
239 END  push_error;
240 
241 
242 PROCEDURE pop_error
243 		(p_delete_flag		IN	BOOLEAN
244 		,p_Package_name	 OUT NOCOPY VARCHAR2
245 		,p_program_name	 OUT NOCOPY VARCHAR2
246 		,p_entry_point	 OUT NOCOPY NUMBER
247 		,p_error_type	 OUT NOCOPY VARCHAR2
248 		,p_error_msg	 OUT NOCOPY VARCHAR2
249 		)
250 IS
251 BEGIN
252 	null;
253 END  pop_error;
254 
255 PROCEDURE clear_stack
256 IS
257 BEGIN
258 	null;
259 END  clear_stack;
260 --
261 --
262 --
263 -- ------------------------------------------------------------------------------
264 --  FUNCITON:	Get_Tree_ID                                                    --
265 --  PURPOSE:	Converts the tree name passed to the id in the tree definition --
266 --		table.                                                         --
267 -- ------------------------------------------------------------------------------
268 --
269 FUNCTION  Get_Tree_ID
270 		(p_tree_name		IN	VARCHAR2
271 		)
272 	RETURN NUMBER
273 IS
274    id_out		NUMBER := 0;
275 
276 BEGIN
277 --
278    return 1;
279 
280 END     Get_Tree_id;
281 --
282 --
283 --
284 --
285 -- ---------------------------------------------------------------------------------
286 -- PROCEDURE:  ClearNodeCache                                                     --
287 -- PURPOSE:    Clears the tree formatted records cached in a PLSQL table          --
288 --             for the treeid and level number passed by deleting the rows        --
289 --             from this table and then updating the start/end pointers for the   --
290 --             node in the IControl TAble. If the IControlTable entry is not      --
291 --             found then the children were never queried and this routine        --
292 --             does nothing                                                       --
293 -- Prerequisites: None.                                                           --
294 --                                                                                --                                                                               --
295 -- ---------------------------------------------------------------------------------
296 --
297 
298 PROCEDURE ClearNodeCache
299 		(p_tree_id		IN	NUMBER
300 		 ,p_node_number		IN	NUMBER)
301 IS
302     x		NUMBER := 0;
303     y		NUMBER := 0;
304 
305 BEGIN
306 --
307 --  initialize the table records table (index by table)
308 --
309    x := GetNodeIndex(p_tree_id, p_node_number, 'START');
310    y := GetNodeIndex(p_tree_id, p_node_number, 'END');
311 
312 --
313 --  Loop thru each table entry and delete any children queried for that node
314 --  Delete the child node entry in IControlTAable when done
315 --
316 
317    WHILE x <= y LOOP
318        okc_dbtree_pvt.ClearNodeCache(p_tree_id, tDataTable(x).tree_node_id);
319        okc_dbtree_pvt.DeleteControlRec(p_tree_id, tDataTable(x).tree_node_id);
320        tDataTable.DELETE(x);
321 
322        x:= tDataTable.NEXT(x);
323 
324    END LOOP;
325 
326 --
327 --   Update the Control Table for the node_number passed to reflect the deleted rows
328 --
329    SaveNodeIndex(p_tree_id, p_node_number, 0, 0);
330 
331 END   ClearNodeCache;
332 
333 --
334 --
335 --
336 -- ---------------------------------------------------------------------------------
337 -- PROCEDURE:  ProcessRootNode                                                    --
338 -- PURPOSE:    process the root node for the Contracts group tree and put results --
339 --             in the tDataTable table.                                           --
340 --             Access flag parameter controls which groups are selected:          --
341 --                 'U' = Public only                                              --
342 --                 'P' = Private Only                                             --
343 --                 'A' = both public and private                                  --
344 -- DEPENDENCIES:  tDataTable must be initialized and empty                        --
345 -- ---------------------------------------------------------------------------------
346 --
347 PROCEDURE ProcessRootNode
348               (  p_tree_id              IN  NUMBER
349                  ,p_access_flag         IN  VARCHAR2 )
350 IS
351 /*
352   CURSOR   rt (gflag   In  VARCHAR2) IS
353         select  id,
354                 name,
355                 public_yn,
356                 created_by,
357                 creation_date,
358                 okc_query.GetChildCount(id) numchildren
359          from   okc_k_groups_v g
360         where   NOT EXISTS (select  1
361                               from  okc_k_grpings_v p
362                              where  p.included_cgp_id = g.id)
363           AND   ( (g.public_yn = decode(gflag,'U','Y',gflag,'A','Y'))
364                  OR (g.public_yn = decode(gflag,'P','N') AND created_by = 10589)
365                  OR (gflag = 'A' AND created_by = 10589)
366                 )
367         ORDER BY g.name;
368 */
369 -- The cursor has been modified by manash to take care of public/private grouping problem Bug#1159200
370 
371 /*
372   CURSOR   rt (gflag   In  VARCHAR2) IS
373         select  id,
374                 name,
375                 public_yn,
376                 created_by,
377                 creation_date,
378                 okc_query.GetChildCount(id) numchildren
379          from   okc_k_groups_v g
380         where   NOT EXISTS (select  1
381                               from  okc_k_grpings_v p
382                              where  p.included_cgp_id = g.id)
383           AND   ( (g.public_yn = decode(gflag,'U','Y','A','Y'))
384                  OR (g.public_yn = decode(gflag,'P','N') AND user_id = FND_GLOBAL.USER_ID)
385                  OR (gflag = 'A' AND user_id = FND_GLOBAL.USER_ID)
386                 )
387         ORDER BY g.name;
388 */
389 -- The cursor has been modified by manash to take care of public/private grouping problem Bug#1159200
390 
391 -- Bug Bug Fix 4958704 - Performance issue
392 
393   CURSOR   rt (gflag   In  VARCHAR2) IS
394         select  id,
395                 name,
396                 public_yn,
397                 created_by,
398                 creation_date,
399                 okc_query.GetChildCount(id) numchildren
400          from   okc_k_groups_v g
401         where   NOT EXISTS (
402 				select  1
403 				from  okc_k_grpings_v p, okc_k_groups_v g1
404 				where  p.included_cgp_id = g.id
405 				and	g1.id = p.cgp_parent_id
406 				and 	g1.public_yn = 'Y'
407 
408 				UNION
409 
410 				select  1
411 				from  okc_k_grpings_v p, okc_k_groups_v g1
412 				where  p.included_cgp_id = g.id
413 				and	g1.id = p.cgp_parent_id
414 				AND   g1.public_yn = 'N' and g1.user_id = FND_GLOBAL.USER_ID
415 			   )
419                 )
416           AND   ( (g.public_yn = decode(gflag,'U','Y','A','Y'))
417                  OR (g.public_yn = decode(gflag,'P','N') AND user_id = FND_GLOBAL.USER_ID)
418                  OR (gflag = 'A' AND user_id = FND_GLOBAL.USER_ID)
420         ORDER BY g.name;
421 
422 
423 -- Bug Bug Fix 4958704
424 
425     i                  NUMBER;
426     x                  NUMBER;
427     pname              VARCHAR2(20);
428     rows_fetched       NUMBER := 0;
429     More_records       BOOLEAN := FALSE;
430     num_children       NUMBER := 0;
431 
432 BEGIN
433     tDataTable := TreeDataTableType();
434     IControlTable := IndexControlTable();
435    --
436    --    store the root node as entry 1 in the data table....
437    --
438    TDataTable.EXTEND;
439    x := TDataTable.LAST;
440    tDataTable(x).tree_initial_state := 1;
441    tDataTable(x).tree_depth := 1;
442    tDataTable(x).tree_label := 'Contracts Group Root';
443    tDataTable(x).tree_icon_name := okc_dbtree_pvt.icon_name;
444    tDataTable(x).tree_data := '\ROOT\0\' || to_char(x) || '\3\root\';
445    tDataTable(x).tree_node_id := x;
446    tDataTable(x).tree_node_type := 'GRP';
447    tDataTable(x).tree_parent_node_id := null;
448    tDataTable(x).tree_level_number := 1;
449 
450    --
451    --   store a Controltable entry for the root so start/stop for its
452    --   children can be saved in the following query
453    --
454    IControlTable.EXTEND;
455    x := IControlTable.LAST;
456    IControlTable(x).tree_id := p_tree_id;
457    IControlTable(x).node_number := 1;
458    IControlTable(x).start_ind := 0;
459    IControlTable(x).end_ind := 0;
460    IControlTable(x).num_entries := 0;
461    IControlTable(x).current_set := 1;
462 --
463 --   execute the query and put the results into the datatable
464 --
465    rows_fetched := 0;
466    FOR rtrec IN rt(p_access_flag) LOOP
467      --
468      --   Process the record just fetched into the TDataTable
469      --
470      rows_fetched := rows_fetched + 1;
471 
472      TDataTAble.EXTEND;
473      x := TDataTable.LAST;
474 
475      tDataTable(x).tree_depth := 1;
476      tDataTable(x).tree_label := rtrec.name;
477 	if rtrec.public_yn = 'Y' then
478        tDataTable(x).tree_icon_name := 'treepubl';
479 	else
480        tDataTable(x).tree_icon_name := 'treepers';
481 	end if;
482      tDataTable(x).tree_num_children := rtrec.numchildren;
483 
484      IF rtrec.numchildren > 0 THEN
485          tDataTable(x).tree_initial_state := 1;
486      ELSE
487          tDataTable(x).tree_initial_state := 0;
488      END IF;
489      tDataTable(x).tree_data := '\GRP' || '\1' || '\' || to_char(x) || '\'
490                                 || to_char(rtrec.numchildren) || '\'
491                                 || to_char(rtrec.id) || '\';
492      tDataTable(x).tree_node_id := x;
493      tDataTable(x).tree_node_type := 'GRP';
494      tDataTable(x).tree_parent_node_id := 1;
495      tDataTable(x).tree_level_number := 1;
496 
497   END LOOP;
498 
499   --
500   --  Update the level controls into IControltable
501   --
502    SaveNodeIndex (p_tree_id ,1 ,2 ,x);
503 
504 --  for i in 1..x LOOP
505 --      jrt_message('OKCDBTREE-ProcessRootNode', to_char(i) || ':' || 'Data: ' || tDataTable(i).tree_data, sysdate);
506 --      jrt_message('OKCDBTREE-ProcessRootNode', to_char(i) || ':' || 'NodeType: ' || tDataTable(i).tree_node_type, sysdate);
507 --      jrt_message('OKCDBTREE-ProcessRootNode', to_char(i) || ':' || 'NodeId: ' || to_char(tDataTable(i).tree_node_id), sysdate);
508 --      jrt_message('OKCDBTREE-ProcessRootNode', to_char(i) || ':' || 'LevelNo: ' || to_char(tDataTable(i).tree_level_number), sysdate);
509 --  END LOOP;
510 
511 --  FOR x in IControlTable.FIRST..IControlTable.LAST LOOP
512 --      jrt_message('OKCDBTREE-ProcessRootNode', ' Control Entry: ' || to_char(x) || ' TreeID:'
513 --           || to_char(IControlTable(x).tree_id) || ' Node:'
514 --           || to_char(IControlTable(x).node_number) || ' Start:'
515 --           || to_char(IControlTable(x).start_ind) || ' End:'
516 --           || to_char(IControlTable(x).end_ind) || ' Entries:'
517 --           || to_char(IControlTable(x).num_entries), sysdate );
518 --  END LOOP;
519 
520 EXCEPTION
521    WHEN OTHERS THEN
522         raise_application_error(-20457,'Exception in Process Root Node ' ||
523                                  to_char(p_tree_id) || p_access_flag, TRUE);
524 
525 END   ProcessRootNode;
526 
527 
528 --
529 -- ---------------------------------------------------------------------------------
530 -- PROCEDURE:  ReturnChildrenNodes                                                --
531 -- PURPOSE:    retreives all cached nodes for the tree and node passed. If the    --
532 --             reload_flag = 'Y' then the cached is cleared and the rows          --
533 --             requireied and reloaded before returning to the requesting form    --
534 -- Prerequisites: Result table must be initialized in form before call            --
535 --                TreeDataTable in package spec must be initialized               --
536 --                                                                                --
537 ------------------------------------------------------------------------------------
538 --
539 
540 PROCEDURE ReturnChildrenNodes
544                        ,p_nodes_out           OUT NOCOPY NUMBER
541                       (p_tree_id              IN  NUMBER
542                        ,p_node_number         IN  NUMBER
543                        ,p_reload_flag         IN  VARCHAR2
545                        ,p_Result_table        OUT NOCOPY okc_dbtree_pvt.ResultRecTableType)
546 IS
547       x              NUMBER;
548       y              NUMBER;
549       z              NUMBER;
550 BEGIN
551  --
552  --  set the start/end indexes for the children of the node_number passed
553  --
554     x := GetNodeIndex(p_tree_id, p_node_number, 'START');
555     y := GetNodeIndex(p_tree_id, p_node_number, 'END');
556     p_nodes_out := GetNodeIndex(p_tree_id, p_node_number, 'ENTRY');
557 
558  --
559  --  move the node entries to the result set table. S separate table is used for
560  --  results as the format is different and the result is an index by table
561  --  which forms PLSQL seems to like better....
562  --
563 
564    z := 0;
565    FOR i in x..y LOOP
566       z := z + 1;
567       IF tDataTable.EXISTS(i) THEN
568          p_result_table(z).initial_state := 1;
569          p_result_table(z).tree_depth    := tDataTable(i).tree_level_number;
570          p_result_table(z).node_label    := substr(tDataTable(i).tree_label,1,255);
571          p_result_table(z).node_icon     := substr(tDataTable(i).tree_icon_name,1,255);
572          p_result_table(z).node_data     := substr(tDataTable(i).tree_data,1,255);
573          p_result_table(z).node_children  := tDataTable(i).tree_num_children;
574       END IF;
575 
576    END LOOP;
577 
578    --
579    --  check that the actual number returned = number stored in control table
580    --  question is what to do if they are not the same?????
581    --
582    IF p_nodes_out <> z THEN
583       p_nodes_out := z;
584    END IF;
585 
586 EXCEPTION
587    WHEN OTHERS THEN
588         raise_application_error(-20458,'Exception in DBTREE:ReturnChildrenNodes ' ||
589                                  to_char(p_tree_id) || ':' || to_char(p_node_number), TRUE);
590 
591 
592 END   ReturnChildrenNodes;
593 --
594 --
595 --
596 -- ---------------------------------------------------------------------------------
597 -- PROCEDURE:  LoadNodeChildren                                                   --
598 -- PURPOSE:    creates the TDataTable entries for the children nodes below the    --
599 --             specified tree and level and node passed                           --
600 --                                                                                --
601 -- Prerequisites: Tree Node defintion must be loaded into TDefTable              --
602 --                TreeDataTable in package spec must be iinitialized              --
603 --                                                                                --
604 -- ---------------------------------------------------------------------------------
605 --
606 PROCEDURE LoadNodeChildren
607 		(p_tree_id		IN    NUMBER
608                 ,p_node_number          IN    NUMBER
609                 ,p_reload_flag          IN    VARCHAR2
610                 ,p_nodes_out            OUT NOCOPY   NUMBER)
611 IS
612    CURSOR s ( p_grp_id  IN   NUMBER) IS
613           select  'GRP' rec_type,
614                   g.id node_id,
615                   g.included_cgp_id occur_id,
616                   t.name node_name,
617 			   b.public_yn,
618                   okc_query.GetChildCount(b.id) numchildren
619            from   okc_k_grpings g,
620                   okc_k_groups_b b,
621                   okc_k_groups_tl t
622           where   g.cgp_parent_id = p_grp_id
623             and   g.included_cgp_id IS NOT NULL
624             and   g.included_cgp_id = b.id
625             and   b.id = t.id
626             and   t.language = USERENV('LANG')
627 --    UNION
628 --          select  'KHDR' rec_type,
629 --                  g.id node_id,
630 --                  g.included_chr_id occur_id,
631 --                  b.contract_number node_name,
632 --                  okc_query.GetChildCount(b.id) numchildren
633 --           from   okc_k_grpings g,
634 --                  okc_k_headers_b b,
635 --                  okc_k_headers_tl t
636 --          where   g.cgp_parent_id = p_grp_id
637 --            and   g.included_chr_id IS NOT NULL
638 --            and   g.included_chr_id = b.id
639 --            and   b.id = t.id
640 --            and   t.language = USERENV('LANG')
641    order by 4;
642 
643 -- The cursor has been modified by manash (added order by) to take care of sorting problem Bug#1162535
644 -- and the column node_name (see commented out) to display K number only Bug#1160083
645 
646    start_index         NUMBER;
647    end_index           NUMBER;
648    k_id                NUMBER;
649    level_no            NUMBER;
650    num_recs            NUMBER;
651    entered_ind NUMBER := 0;
652 
653 BEGIN
654 
655   --
656   --   delete any previously loaded children nodes
657   --
658     num_recs := 0;
659     num_recs := GetNodeIndex(p_tree_id, p_node_number, 'ENTRY');
660 --    jrt_message('DBTREE_PVT: ','Start of LoadNodeChildren', sysdate);
661 
662     IF num_recs > 0 AND p_reload_flag = 'Y' THEN
663            ClearNodeCache(p_tree_id, p_node_number);
667     END IF;
664     ELSIF num_recs > 0 AND p_reload_flag = 'N' THEN
665            p_nodes_out := num_recs;
666            goto quit_proc;
668 
669   --
670   --   open the cursor and read recs into table
671   --
672       k_id := 0;
673       k_id := to_number(Get_Data_Parameter(tDataTable(p_node_number).tree_data, 'OCCUR'));
674       level_no := to_number(Get_Data_Parameter(tDataTable(p_node_number).tree_data, 'LEVEL')) + 1;
675 
676   --
677   --  set the table indexes in tDataTable, execute the query and load the results
678   --
679 
680       start_index := tDataTable.LAST + 1;
681 	 end_index := start_index;
682       FOR srec IN s(k_id) LOOP
683 		entered_ind := 1;
684           tDataTAble.EXTEND;
685           end_index := tdataTable.LAST;
686           tDataTable(end_index).tree_num_children := srec.numchildren;
687 --    jrt_message('DBTREE_PVT: ','Cursor loop' || srec.node_name || srec.numchildren, sysdate);
688           IF srec.numchildren > 0 THEN
689                tDataTable(end_index).tree_initial_state := 1;
690           ELSE
691                tDataTable(end_index).tree_initial_state := 0;
692           END IF;
693           tDataTable(end_index).tree_depth := level_no;
694 /*          tDataTable(end_index).tree_label := srec.rec_type || ': ' || srec.node_name
695                                       || ': ' || to_char(srec.numchildren);
696 							   */
697 -- The following line has been added by manash to diaplay K number only Bug#1160083
698 
699           tDataTable(end_index).tree_label := srec.node_name;
700 
701           tDataTable(end_index).tree_parent_node_id := p_node_number;
702           tDataTable(end_index).tree_level_number := level_no;
703           tDataTable(end_index).tree_node_id := end_index;
704 
705           IF srec.rec_type = 'KHDR' THEN
706                tDataTable(end_index).tree_icon_name := null;
707                tDataTable(end_index).tree_data := '\KHDR\' || to_char(level_no) || '\'
708                                 || to_char(end_index) || '\'
709                                 || to_char(srec.numchildren) || '\'
710                                 || to_char(srec.occur_id) || '\';
711                tDataTable(end_index).tree_node_type := 'KHDR';
712           ELSE
713 	        if srec.public_yn = 'Y' then
714                tDataTable(end_index).tree_icon_name := 'treepubl';
715 	        else
716                tDataTable(end_index).tree_icon_name := 'treepers';
717 	        end if;
718                tDataTable(end_index).tree_data := '\GRP\' || to_char(level_no) || '\'
719                                 || to_char(end_index) || '\'
720                                 || to_char(srec.numchildren) || '\'
721                                 || to_char(srec.occur_id) || '\';
722           END IF;
723 
724      END LOOP;
725 
726   --
727   --   store the IcontrolTable entry for the node just loaded
728   --
729     if entered_ind = 0 then
730 	 p_nodes_out := 0;
731     else
732       SaveNodeIndex( p_tree_id, p_node_number, start_index, end_index);
733       p_nodes_out := end_index - start_index + 1;
734     end if;
735 
736 
737 
738 <<quit_proc>>
739    null;
740 
741 EXCEPTION
742    WHEN OTHERS THEN
743       raise_application_error(-20458,'Exception in Get_node_children ', TRUE);
744 
745 END  LoadNodeChildren;
746 --
747 --
748 --
749 --  PROCEDURE:  Initialize_Package
750 --  PURPOSE:  reinitialize all package nestedtables and variables
751 --
752 PROCEDURE Initialize_Package
753 IS
754 BEGIN
755 --
756 --   Initialize all the nested tables for this session....
757 --
758 
759   IControlTable := IndexControlTable ();
760   tDataTable := TreeDataTableType ();
761 
762 --
763 --   initialize the datarecs table when I remember how to that for an index by table?????
764 --
765 
766 
767 
768 END   Initialize_Package;
769 
770 
771 
772 END okc_dbtree_pvt;