1 PACKAGE BODY CNDIHY_Hierarchy_PKG as
2 -- $Header: cndihyab.pls 115.1 99/07/16 07:06:17 porting ship $
3
4
5 --
6 -- Procedure Name
7 -- default_row
8 -- History
9 -- 12/28/93 Tony Lower Created
10 --
11 PROCEDURE Default_Row (X_hierarchy_id IN OUT number) IS
12
13 BEGIN
14
15 --
16 -- This procedure is used to fill in the Head_Hierarchy_ID in
17 -- cn_head_hierarchies with an arbitrarily generated sequence.
18 -- This maintains the uniqueness of primary keys.
19 --
20
21 IF X_hierarchy_id IS NULL THEN
22
23 -- X_Hierarchy_ID := cn_head_hierarchies_s.nextval;
24
25 SELECT cn_head_hierarchies_s.nextval
26 INTO X_hierarchy_id FROM dual;
27
28 END IF;
29
30 END Default_Row;
31
32
33 --
34 -- Procedure Name
35 -- default_period_row
36 -- History
37 -- 7/18/94 Tony Lower Created
38 --
39 PROCEDURE Default_Period_Row (X_hierarchy_id IN OUT number) IS
40
41 BEGIN
42
43 --
44 -- This procedure is used to fill in the Dim_Hierarchy_ID in
45 -- cn_dim_hierarchies with an arbitrarily generated sequence.
46 -- This maintains the uniqueness of primary keys.
47 --
48
49 IF X_hierarchy_id IS NULL THEN
50
51 -- X_Hierarchy_ID := cn_dim_hierarchies_s.nextval;
52
53 SELECT cn_dim_hierarchies_s.nextval
54 INTO X_hierarchy_id FROM dual;
55
56 END IF;
57
58 END Default_Period_Row;
59
60
61 --
62 -- Procedure Name
63 -- Populate_Period_Fields
64 -- History
65 -- 12/28/93 Tony Lower Created
66 --
67 PROCEDURE Populate_Period_Fields (X_start_period number,
68 X_start_name IN OUT varchar2,
69 X_end_period number,
70 X_end_name IN OUT varchar2) IS
71 BEGIN
72
73 --
74 -- This matches Period names to the period IDs that are stored in
75 -- the cn_dim_hierarchies table.
76 --
77
78 IF X_start_period IS NOT NULL THEN
79 SELECT period_name
80 INTO X_start_name
81 FROM cn_periods
82 WHERE period_id = X_start_period;
83 END IF;
84
85 IF X_end_period IS NOT NULL THEN
86 SELECT period_name
87 INTO X_end_name
88 FROM cn_periods
89 WHERE period_id = X_end_period;
90 END IF;
91
92 END Populate_Period_Fields;
93
94 --
95 -- Procedure Name
96 -- Populate_Value_Fields
97 -- History
98 -- 12/28/93 Tony Lower Created
99 --
100 PROCEDURE Populate_Value_Fields (X_value_id number,
101 X_dim_hierarchy_id number,
102 X_name IN OUT varchar2) IS
103 BEGIN
104
105 --
106 -- This procedure fills in the name for a value ID in a particular
107 -- dimension hierarchy. This data is now held denormalized in the
108 -- CN_HIERARCHY_NODES translation table, making obselete our previous
109 -- (slow) method of searching through the base source tables for the
110 -- dimension.
111 --
112
113 IF X_value_id IS NOT NULL THEN
114
115 SELECT name INTO X_name FROM cn_hierarchy_nodes
116 WHERE dim_hierarchy_id = X_dim_hierarchy_id
117 AND value_id = X_value_id;
118
119 END IF;
120
121 END Populate_Value_Fields;
122
123 --
124 -- Procedure Name
125 -- Populate_Fields
126 -- History
127 -- 12/28/93 Tony Lower Created
128 --
129 PROCEDURE Populate_Fields (X_dimension_id number,
130 X_hierarchy_id number,
131 X_select_clause IN OUT varchar2) IS
132
133 BEGIN
134
135 --
136 -- This creates a dynamic SQL select clause, based on the dimension and
137 -- Hierarchy being worked on. This select clause is, in turn, used to
138 -- help the user choose appropriate data values for filling in the
139 -- Hierarchy values in the form.
140 --
141
142 IF X_dimension_id is not null THEN
143
144 IF X_hierarchy_id IS NOT NULL THEN
145
146 SELECT '(SELECT '||
147 decode(col.data_type,
148 'VARCHAR2', col.name,
149 'to_char('||col.name||')')||
150 ' NAME, -'||pk.name||
151 ' VALUE_ID FROM '||tab.name||' MINUS '||
152 'SELECT name NAME, -external_id VALUE_ID FROM cn_hierarchy_nodes '||
153 'WHERE dim_hierarchy_id = :periods.dim_hierarchy_id '||
154 ') UNION '||
155 'SELECT name NAME, value_id VALUE_ID FROM cn_hierarchy_nodes '||
156 'WHERE dim_hierarchy_id = :periods.dim_hierarchy_id '||
157 'ORDER BY 1'
158 INTO X_select_clause
159 FROM cn_obj_tables_v tab,
160 cn_obj_columns_v col,
161 cn_obj_columns_v pk
162 WHERE tab.table_id = col.table_id
163 AND tab.table_id = pk.table_id
164 AND pk.dimension_id = X_dimension_id
165 AND pk.primary_key = 'Y'
166 AND col.user_column_name = 'Y';
167
168 ELSE
169
170 SELECT 'SELECT '||
171 decode(col.data_type,
172 'VARCHAR2', col.name,
173 'to_char('||col.name||') ')||
174 ' NAME, '||pk.name||
175 ' VALUE_ID FROM '||tab.name
176 INTO X_select_clause
177 FROM cn_obj_tables_v tab,
178 cn_obj_columns_v col,
179 cn_obj_columns_v pk
180 WHERE tab.table_id = col.table_id
181 AND tab.table_id = pk.table_id
182 AND pk.dimension_id = X_dimension_id
183 AND pk.primary_key = 'Y'
184 AND col.user_column_name = 'Y';
185
186 END IF;
187
188 END IF;
189
190 END Populate_Fields;
191
192
193
194 --
195 -- Procedure Name
196 -- root_node
197 -- History
198 -- 12/28/93 Tony Lower Created
199 --
200 PROCEDURE Root_Node ( X_hierarchy_id number,
201 X_value_id IN OUT number) IS
202
203 BEGIN
204
205 --
206 -- This procedure returns the Root node of the hierarchy in question.
207 -- If no root node exists, it searches to see whether the hierarchy
208 -- is a rooted tree, and if so returns that root, otherwise, it will
209 -- check whether the hierarchy is empty, and if so return a newly
210 -- created root.
211 --
212 -- In the case where the hierarchy is already a multiply rooted forest,
213 -- and no root node is specified in the Dim_Hierarchy record, the
214 -- procedure returns NULL.
215 --
216
217 SELECT root_node
218 INTO X_value_id
219 FROM cn_dim_hierarchies
220 WHERE dim_hierarchy_id = X_hierarchy_id;
221
222 IF X_value_id IS NULL THEN
223
224 SELECT value_id
225 INTO X_value_id
226 FROM cn_hierarchy_edges
227 WHERE dim_hierarchy_id = X_hierarchy_id
228 AND parent_value_id is NULL;
229
230 END IF;
231
232 EXCEPTION
233
234 WHEN TOO_MANY_ROWS THEN
235 X_value_id := NULL;
236
237 WHEN NO_DATA_FOUND THEN
238
239 -- X_value_id := cn_hierarchy_nodes_s.nextval;
240
241 SELECT cn_hierarchy_nodes_s.nextval INTO X_value_id FROM dual;
242
243 INSERT INTO cn_hierarchy_nodes (dim_hierarchy_id, value_id, name)
244 VALUES (X_hierarchy_id, X_value_id, 'ALL');
245
246 INSERT INTO cn_hierarchy_edges (dim_hierarchy_id, value_id)
247 VALUES (X_hierarchy_id, X_value_id);
248
249 END Root_Node;
250
251 --
252 -- Procedure Name
253 -- Synchronize_Node
254 -- History
255 -- 12/28/93 Tony Lower Created
256 --
257 PROCEDURE Synchronize_Node (X_value_id IN OUT number,
258 X_name IN varchar2,
259 X_dim_hierarchy_id IN number) IS
260 Temp number(15);
261 BEGIN
262
263 --
264 -- This procedure creates a hierarchy node for the hierarchy value in
265 -- question, if one does not exist already.
266 --
267
268 SELECT count(*) INTO Temp FROM cn_hierarchy_nodes
269 WHERE external_id = X_value_id
270 AND dim_hierarchy_id = X_dim_hierarchy_id;
271
272 IF Temp = 0 THEN
273
274 INSERT INTO cn_hierarchy_nodes (value_id, external_id, name,
275 dim_hierarchy_id)
276 VALUES (cn_hierarchy_nodes_s.nextval,
277 X_value_id,
278 X_name,
279 X_dim_hierarchy_id);
280
281 -- X_value_id := cn_hierarchy_nodes_s.currval;
282
283 SELECT cn_hierarchy_nodes_s.currval INTO X_value_id FROM dual;
284
285 END IF;
286
287 END Synchronize_Node;
288
289 --
290 -- Procedure Name
291 -- Shift_Parent
292 -- History
293 -- 12/28/93 Tony Lower Created
294 --
295 PROCEDURE Shift_Parent( X_parent_value_id number,
296 X_dim_hierarchy_id number,
297 X_central_root_node number,
298 X_central_value_id IN OUT number,
299 X_central_parent_id IN OUT number) IS
300 BEGIN
301
302 --
303 -- This procedure handles the mechanics behind shifting the focus of
304 -- the hierarchy viewer one record _up_. It feeds back into the
305 -- control fields of the hierarchy viewer, and is followed by a
306 -- programmatic query which pulls in the appropriate data.
307 --
308
309 SELECT parent_value_id
310 INTO X_central_parent_id
311 FROM cndihy_parents_v
312 WHERE value_id = X_parent_value_id
313 AND dim_hierarchy_id = X_dim_hierarchy_id
314 AND root_node = X_central_root_node;
315
316 X_central_value_id := NULL;
317
318 IF X_central_parent_id IS NULL THEN
319
320 X_central_value_id := X_parent_value_id;
321 X_central_parent_id := NULL;
322
323 END IF;
324
325 EXCEPTION
326
327 WHEN NO_DATA_FOUND THEN
328
329 X_central_value_id := X_parent_value_id;
330 X_central_parent_id := NULL;
331
332 END Shift_Parent;
333
334 --
335 -- Procedure Name
336 -- Cascade_Number
337 -- History
338 -- 7/19/93 Tony Lower Created
339 --
340 PROCEDURE Cascade_Number(X_value_id number,
341 X_dim_hierarchy_id number,
342 X_cascade_number IN OUT number) IS
343 BEGIN
344
345 --
346 -- This procedure calculates how many nodes are in danger of deletion
347 -- from a cascade delete. It is used to give the user information in
348 -- making the decision whether to cascade delete.
349 --
350
351 SELECT count(*) INTO X_cascade_number FROM cn_dim_explosion
352 WHERE ancestor_id = X_value_id
353 AND dim_hierarchy_id = X_dim_hierarchy_id;
354
355 END Cascade_Number;
356
357 --
358 -- Procedure Name
359 -- Cascade_Delete
360 -- History
361 -- 7/19/93 Tony Lower Created
362 --
363 PROCEDURE Cascade_Delete(X_value_id number,
364 X_parent_value_id number,
365 X_dim_hierarchy_id number) IS
366
367 Cursor Children IS SELECT *
368 FROM cn_hierarchy_edges
369 WHERE parent_value_id = X_value_id
370 AND dim_hierarchy_id = X_dim_hierarchy_id;
371
372 X_refcount number(15);
373
374 BEGIN
375
376 --
377 -- This deletes a node from the graph, and also deletes all nodes
378 -- beneath it (if they are not part of the graph by way of some other
379 -- parent).
380 --
381
382 IF X_parent_value_id IS NULL THEN
383
384 DELETE cn_hierarchy_edges WHERE value_id = X_value_id
385 AND parent_value_id IS NULL
386 AND dim_hierarchy_id = X_dim_hierarchy_id;
387
388 ELSE
389
390 DELETE cn_hierarchy_edges WHERE value_id = X_value_id
391 AND parent_value_id = X_parent_value_id
392 AND dim_hierarchy_id = X_dim_hierarchy_id;
393
394 END IF;
395
396 SELECT ref_count INTO X_refcount FROM cn_hierarchy_nodes
397 WHERE value_id = X_value_id
398 AND dim_hierarchy_id = X_dim_hierarchy_id;
399
400 IF X_refcount = 0 THEN
401
402 FOR c IN Children LOOP
403
404 Cascade_Delete(c.value_id, X_value_id,
405 X_dim_hierarchy_id);
406
407 END LOOP;
408
409 END IF;
410
411 END Cascade_Delete;
412
413 --
414 -- Procedure Name
415 -- Insert_Row
416 -- History
417 -- 7/19/93 Tony Lower Created
418 --
422 BEGIN
419 PROCEDURE Insert_Row(X_value_id number,
420 X_parent_Value_id number,
421 X_dim_hierarchy_id number) IS
423
424 --
425 -- This procedure inserts an edge into the Hierarchy, making sure
426 -- to delete any edges to NULL (which would be made obsolete by the
427 -- occurence of an active edge).
428 --
429
430 DELETE cn_hierarchy_edges WHERE
431 parent_value_id IS NULL
432 AND value_id = X_value_id
433 AND dim_hierarchy_id = X_dim_hierarchy_id;
434
435 INSERT INTO cn_hierarchy_edges (value_id, parent_value_id,
436 dim_hierarchy_id)
437 VALUES (X_value_id, X_parent_Value_id,
438 X_dim_hierarchy_id);
439
440 END Insert_Row;
441
442 --
443 -- Procedure Name
444 -- Insert_Root
445 -- History
446 -- 7/25/93 Tony Lower Created
447 --
448 PROCEDURE Insert_Root(X_value_id number,
449 X_dim_hierarchy_id number) IS
450 BEGIN
451
452 INSERT INTO cn_hierarchy_edges (value_id, parent_value_id,
453 dim_hierarchy_id)
454 (SELECT X_value_id, NULL, X_dim_hierarchy_id
455 FROM dual
456 WHERE NOT EXISTS (SELECT * FROM cn_hierarchy_edges
457 WHERE value_id = X_value_id
458 AND dim_hierarchy_id =
459 X_dim_hierarchy_id));
460
461 END Insert_Root;
462
463 --
464 -- Procedure Name
465 -- Fetch_Row_counts
466 -- History
467 -- 7/25/93 Tony Lower Created
468 --
469 PROCEDURE Fetch_Row_Counts (X_parent_value_id number,
470 X_value_id number,
471 X_dim_hierarchy_id number,
472 X_parent_rows IN OUT number,
473 X_child_rows IN OUT number) IS
474 BEGIN
475
476 SELECT count(*) INTO X_parent_rows
477 FROM cn_hierarchy_edges
478 WHERE parent_value_id = X_parent_value_id
479 AND dim_hierarchy_id = X_dim_hierarchy_id;
480
481 SELECT count(*) INTO X_child_rows
482 FROM cn_hierarchy_edges
483 WHERE parent_value_id = X_value_id
484 AND dim_hierarchy_id = X_dim_hierarchy_id;
485
486 END Fetch_Row_counts;
487
488 --
489 -- Procedure Name
490 -- Fetch_Row_counts
491 -- History
492 -- 7/26/93 Tony Lower Created
493 --
494 PROCEDURE Create_Dummy_Node (X_value_id IN OUT number,
495 X_name varchar2,
496 X_dim_hierarchy_id number) IS
497 BEGIN
498
499 SELECT cn_hierarchy_nodes_s.nextval
500 INTO X_value_id
501 FROM dual;
502
503 INSERT INTO cn_hierarchy_nodes
504 (dim_hierarchy_id, name, value_id)
505 VALUES (X_dim_hierarchy_id, X_name, X_value_id);
506
507 END Create_Dummy_Node;
508
509 END CNDIHY_Hierarchy_PKG;
[Home] [Help]