[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cn_head_hierarchies_s.nextval
INTO X_hierarchy_id FROM dual;
SELECT cn_dim_hierarchies_s.nextval
INTO X_hierarchy_id FROM dual;
SELECT period_name
INTO X_start_name
FROM cn_periods
WHERE period_id = X_start_period;
SELECT period_name
INTO X_end_name
FROM cn_periods
WHERE period_id = X_end_period;
SELECT name INTO X_name FROM cn_hierarchy_nodes
WHERE dim_hierarchy_id = X_dim_hierarchy_id
AND value_id = X_value_id;
X_select_clause IN OUT varchar2) IS
BEGIN
--
-- This creates a dynamic SQL select clause, based on the dimension and
-- Hierarchy being worked on. This select clause is, in turn, used to
-- help the user choose appropriate data values for filling in the
-- Hierarchy values in the form.
--
IF X_dimension_id is not null THEN
IF X_hierarchy_id IS NOT NULL THEN
SELECT '(SELECT '||
decode(col.data_type,
'VARCHAR2', col.name,
'to_char('||col.name||')')||
' NAME, -'||pk.name||
' VALUE_ID FROM '||tab.name||' MINUS '||
'SELECT name NAME, -external_id VALUE_ID FROM cn_hierarchy_nodes '||
'WHERE dim_hierarchy_id = :periods.dim_hierarchy_id '||
') UNION '||
'SELECT name NAME, value_id VALUE_ID FROM cn_hierarchy_nodes '||
'WHERE dim_hierarchy_id = :periods.dim_hierarchy_id '||
'ORDER BY 1'
INTO X_select_clause
FROM cn_obj_tables_v tab,
cn_obj_columns_v col,
cn_obj_columns_v pk
WHERE tab.table_id = col.table_id
AND tab.table_id = pk.table_id
AND pk.dimension_id = X_dimension_id
AND pk.primary_key = 'Y'
AND col.user_column_name = 'Y';
SELECT 'SELECT '||
decode(col.data_type,
'VARCHAR2', col.name,
'to_char('||col.name||') ')||
' NAME, '||pk.name||
' VALUE_ID FROM '||tab.name
INTO X_select_clause
FROM cn_obj_tables_v tab,
cn_obj_columns_v col,
cn_obj_columns_v pk
WHERE tab.table_id = col.table_id
AND tab.table_id = pk.table_id
AND pk.dimension_id = X_dimension_id
AND pk.primary_key = 'Y'
AND col.user_column_name = 'Y';
SELECT root_node
INTO X_value_id
FROM cn_dim_hierarchies
WHERE dim_hierarchy_id = X_hierarchy_id;
SELECT value_id
INTO X_value_id
FROM cn_hierarchy_edges
WHERE dim_hierarchy_id = X_hierarchy_id
AND parent_value_id is NULL;
SELECT cn_hierarchy_nodes_s.nextval INTO X_value_id FROM dual;
INSERT INTO cn_hierarchy_nodes (dim_hierarchy_id, value_id, name)
VALUES (X_hierarchy_id, X_value_id, 'ALL');
INSERT INTO cn_hierarchy_edges (dim_hierarchy_id, value_id)
VALUES (X_hierarchy_id, X_value_id);
SELECT count(*) INTO Temp FROM cn_hierarchy_nodes
WHERE external_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
INSERT INTO cn_hierarchy_nodes (value_id, external_id, name,
dim_hierarchy_id)
VALUES (cn_hierarchy_nodes_s.nextval,
X_value_id,
X_name,
X_dim_hierarchy_id);
SELECT cn_hierarchy_nodes_s.currval INTO X_value_id FROM dual;
SELECT parent_value_id
INTO X_central_parent_id
FROM cndihy_parents_v
WHERE value_id = X_parent_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id
AND root_node = X_central_root_node;
SELECT count(*) INTO X_cascade_number FROM cn_dim_explosion
WHERE ancestor_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
PROCEDURE Cascade_Delete(X_value_id number,
X_parent_value_id number,
X_dim_hierarchy_id number) IS
Cursor Children IS SELECT *
FROM cn_hierarchy_edges
WHERE parent_value_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
DELETE cn_hierarchy_edges WHERE value_id = X_value_id
AND parent_value_id IS NULL
AND dim_hierarchy_id = X_dim_hierarchy_id;
DELETE cn_hierarchy_edges WHERE value_id = X_value_id
AND parent_value_id = X_parent_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
SELECT ref_count INTO X_refcount FROM cn_hierarchy_nodes
WHERE value_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
Cascade_Delete(c.value_id, X_value_id,
X_dim_hierarchy_id);
END Cascade_Delete;
PROCEDURE Insert_Row(X_value_id number,
X_parent_Value_id number,
X_dim_hierarchy_id number) IS
BEGIN
--
-- This procedure inserts an edge into the Hierarchy, making sure
-- to delete any edges to NULL (which would be made obsolete by the
-- occurence of an active edge).
--
DELETE cn_hierarchy_edges WHERE
parent_value_id IS NULL
AND value_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
INSERT INTO cn_hierarchy_edges (value_id, parent_value_id,
dim_hierarchy_id)
VALUES (X_value_id, X_parent_Value_id,
X_dim_hierarchy_id);
END Insert_Row;
PROCEDURE Insert_Root(X_value_id number,
X_dim_hierarchy_id number) IS
BEGIN
INSERT INTO cn_hierarchy_edges (value_id, parent_value_id,
dim_hierarchy_id)
(SELECT X_value_id, NULL, X_dim_hierarchy_id
FROM dual
WHERE NOT EXISTS (SELECT * FROM cn_hierarchy_edges
WHERE value_id = X_value_id
AND dim_hierarchy_id =
X_dim_hierarchy_id));
END Insert_Root;
SELECT count(*) INTO X_parent_rows
FROM cn_hierarchy_edges
WHERE parent_value_id = X_parent_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
SELECT count(*) INTO X_child_rows
FROM cn_hierarchy_edges
WHERE parent_value_id = X_value_id
AND dim_hierarchy_id = X_dim_hierarchy_id;
SELECT cn_hierarchy_nodes_s.nextval
INTO X_value_id
FROM dual;
INSERT INTO cn_hierarchy_nodes
(dim_hierarchy_id, name, value_id)
VALUES (X_dim_hierarchy_id, X_name, X_value_id);