cn_hierarchy_edges_t2
AFTER INSERT ON CN_HIERARCHY_EDGES_ALL
REFERENCING NEW as new
OLD as old
FOR EACH ROW
Type
AFTER EACH ROW
Event
INSERT
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
DECLARE
child_exist varchar2(1);
X_header_id NUMBER(15);
X_period_id NUMBER(15);
-- for marking event
CURSOR l_dim_hier_csr IS
SELECT head.name, head.head_hierarchy_id,
head.org_id, -- MOAC changes
dim.start_date, dim.end_date
FROM cn_dim_hierarchies dim,
cn_head_hierarchies head
WHERE dim.dim_hierarchy_id = :new.dim_hierarchy_id
AND head.head_hierarchy_id = dim.header_dim_hierarchy_id
AND head.org_id = dim.org_id;
BEGIN
child_exist := cn_dim_hierarchy_utilities.node_exist
(:new.dim_hierarchy_id,
:new.value_id);
IF ( child_exist = 'N' ) THEN
cn_debug.print_msg('child not exist',1);
INSERT INTO cn_dim_explosion
(dim_hierarchy_id,
value_id,
ancestor_id,
hierarchy_level,
ancestor_external_id,
value_external_id,
org_id
)
(SELECT :new.dim_hierarchy_id,
:new.value_id,
:new.value_id,
0,
val.external_id,
val.external_id,
:new.org_id
FROM cn_hierarchy_nodes val
WHERE val.value_id = :new.value_id
AND val.dim_hierarchy_id = :new.dim_hierarchy_id
AND val.org_id = :new.org_id);
END IF;
IF :new.parent_value_id IS NOT NULL THEN
child_exist := cn_dim_hierarchy_utilities.node_exist
(:new.dim_hierarchy_id,
:new.parent_value_id);
IF ( child_exist = 'N' ) THEN
cn_debug.print_msg('child not exist',1);
INSERT INTO cn_dim_explosion
(dim_hierarchy_id,
value_id,
ancestor_id,
hierarchy_level,
ancestor_external_id,
value_external_id,
org_id
)
(SELECT :new.dim_hierarchy_id,
:new.parent_value_id,
:new.parent_value_id,
0,
val.external_id,
val.external_id,
:new.org_id
FROM cn_hierarchy_nodes val
WHERE val.value_id = :new.parent_value_id
AND val.dim_hierarchy_id = :new.dim_hierarchy_id
AND val.org_id = :new.org_id);
END IF;
END IF;
IF ( :new.parent_value_id IS NOT NULL ) THEN
-- for a new edge connecting two not null nodes, insert all
-- the ancestors for the new value
cn_debug.print_msg('inserting edges',1);
cn_debug.print_msg('dim_hierarchy_id = '||:new.dim_hierarchy_id,1);
INSERT INTO cn_dim_explosion
(dim_hierarchy_id,
value_id,
ancestor_id,
hierarchy_level,
value_external_id,
ancestor_external_id,
org_id)
SELECT :new.dim_hierarchy_id,
exp1.value_id,
exp2.ancestor_id,
exp2.hierarchy_level + 1,
exp1.value_external_id,
exp2.ancestor_external_id,
:new.org_id
FROM cn_dim_explosion exp1,
cn_dim_explosion exp2
WHERE exp1.ancestor_id = :new.value_id
AND exp2.value_id = :new.parent_value_id
AND exp1.dim_hierarchy_id = :new.dim_hierarchy_id
AND exp2.dim_hierarchy_id = :new.dim_hierarchy_id
AND exp1.org_id = :new.org_id
AND exp2.org_id = :new.org_id;
END IF;
-- marking event here
FOR l_dim IN l_dim_hier_csr LOOP
IF cn_mark_events_pkg.check_cls_hier(l_dim.head_hierarchy_id,l_dim.org_id) = 1 THEN
cn_mark_events_pkg.mark_event_cls_hier
('CHANGE_CLS_HIER', l_dim.name,
:old.dim_hierarchy_id, l_dim.head_hierarchy_id,
NULL, l_dim.start_date, NULL, l_dim.end_date,l_dim.org_id);
ELSIF cn_mark_events_pkg.check_rev_hier(l_dim.head_hierarchy_id,l_dim.org_id) = 1 THEN
cn_mark_events_pkg.mark_event_rc_hier
('CHANGE_RC_HIER', l_dim.name,
:old.dim_hierarchy_id, l_dim.head_hierarchy_id,
NULL, l_dim.start_date, NULL, l_dim.end_date,l_dim.org_id);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
cn_debug.print_msg(sqlerrm,1);
RAISE;
END cn_hierarchy_edges_t2;