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;