1 PACKAGE BODY per_maintain_taskflow AS
2 /* $Header: petkflow.pkb 115.2 2004/06/25 02:27:09 adudekul noship $ */
3
4 --
5 -- Procedure to delete a taskflow and related data.
6 --
7 PROCEDURE delete_taskflow (
8 p_taskflow_name IN VARCHAR2
9 ) IS
10 l_workflow_id NUMBER;
11 l_nav_node_usage_id NUMBER;
12 --
13 -- Cursor to bring back all of the node usages for a node in a tasfklow.
14 --
15 CURSOR csr_node_usages IS
16 SELECT nav_node_usage_id
17 FROM hr_navigation_node_usages
18 WHERE workflow_id = l_workflow_id;
19 BEGIN
20 --
21 -- Find the workflow_id that corresponds to p_taskflow_name (workflow name).
22 --
23 SELECT workflow_id
24 INTO l_workflow_id
25 FROM hr_workflows
26 WHERE workflow_name = p_taskflow_name;
27 --
28 -- For each node usage attached to a taskflow delete the navigation paths
29 -- then delete the node usage record.
30 --
31 FOR node_usage_record IN csr_node_usages LOOP
32 l_nav_node_usage_id := node_usage_record.nav_node_usage_id;
33 DELETE FROM hr_navigation_paths
34 WHERE from_nav_node_usage_id = l_nav_node_usage_id
35 OR to_nav_node_usage_id = l_nav_node_usage_id;
36 DELETE FROM hr_navigation_node_usages
37 WHERE nav_node_usage_id = l_nav_node_usage_id;
38 END LOOP;
39 --
40 -- Delete the taskflow.
41 --
42 DELETE FROM hr_workflows
43 WHERE workflow_id = l_workflow_id;
44 END delete_taskflow;
45
46 --
47 -- Procedure to delete a navigation unit and related data.
48 --
49 PROCEDURE delete_navigation_unit (
50 p_form_name IN VARCHAR2,
51 p_block_name IN VARCHAR2
52 ) IS
53 l_nav_unit_id NUMBER;
54 l_node_name VARCHAR2(80);
55 l_global_usage_id NUMBER;
56 --
57 -- Cursor to bring back all of the navigation nodes for a unit.
58 --
59 CURSOR csr_nav_nodes IS
60 SELECT nav_node_id, name
61 FROM hr_navigation_nodes
62 WHERE nav_unit_id = l_nav_unit_id;
63 --
64 -- Cursor to bring back all of the global usages for a unit.
65 --
66 CURSOR csr_global_usages IS
67 SELECT global_usage_id
68 FROM hr_nav_unit_global_usages
69 WHERE nav_unit_id = l_nav_unit_id;
70 BEGIN
71 --
72 -- Find the nav_unit_id for the form name/block name combination.
73 --
74 SELECT nav_unit_id
75 INTO l_nav_unit_id
76 FROM hr_navigation_units
77 WHERE form_name = p_form_name
78 AND nvl(block_name,hr_api.g_varchar2) = nvl(p_block_name,hr_api.g_varchar2);
79 --
80 -- For each navigation node associated with a navigation unit call
81 -- the delete_navigation_nodes procedure to delete the navigation
82 -- node data. This includes node usages and navigation paths.
83 --
84 FOR node_record IN csr_nav_nodes LOOP
85 l_node_name := node_record.name;
86 delete_navigation_node(l_node_name);
87 END LOOP;
88 --
89 -- For each global usage associaged with a navigation unit, delete
90 -- all of the navigation context rules, then delete the global
91 -- usage record.
92 --
93 FOR global_usage_record IN csr_global_usages LOOP
94 l_global_usage_id := global_usage_record.global_usage_id;
95 DELETE FROM hr_navigation_context_rules
96 WHERE global_usage_id = l_global_usage_id;
97 DELETE FROM hr_nav_unit_global_usages
98 WHERE global_usage_id = l_global_usage_id;
99 END LOOP;
100 --
101 -- Delete all of the incompatibility rules associated with a
102 -- navigation unit.
103 --
104 DELETE FROM hr_incompatibility_rules
105 WHERE from_nav_unit_id = l_nav_unit_id
106 OR to_nav_unit_id = l_nav_unit_id;
107 --
108 -- Delete the navigation unit record.
109 --
110
111 DELETE FROM hr_navigation_units_tl
112 WHERE nav_unit_id = l_nav_unit_id;
113
114 DELETE FROM hr_navigation_units
115 WHERE nav_unit_id = l_nav_unit_id;
116
117 END delete_navigation_unit;
118
119 --
120 -- Procedure to delete a navigation node and related data.
121 --
122 PROCEDURE delete_navigation_node (
123 p_nav_node_name IN VARCHAR2
124 ) IS
125 l_nav_node_id NUMBER;
126 l_nav_node_usage_id NUMBER;
127 l_nav_path_id number;
128 --
129 -- Cursor to bring back all of the node usages for a node.
130 --
131 -- Bug 3648687. Modified the following cursor.
132 --
133 CURSOR csr_node_usages IS
134 SELECT nnu.nav_node_usage_id, hnp.nav_path_id
135 FROM hr_navigation_node_usages nnu,
136 hr_navigation_paths hnp
137 WHERE nav_node_id = l_nav_node_id
138 AND (nnu.nav_node_usage_id = hnp.from_nav_node_usage_id
139 OR nnu.nav_node_usage_id = hnp.to_nav_node_usage_id);
140 BEGIN
141 --
142 -- Find the nav_node_id that correspond to p_nav_node_name.
143 --
144 SELECT nav_node_id
145 INTO l_nav_node_id
146 FROM hr_navigation_nodes
147 WHERE name = p_nav_node_name;
148 --
149 -- For each node usage attached to a node delete the navigation paths
150 -- then delete the node usage record.
151 --
152 FOR node_usage_record IN csr_node_usages LOOP
153 l_nav_node_usage_id := node_usage_record.nav_node_usage_id;
154 l_nav_path_id := node_usage_record.nav_path_id;
155
156 DELETE FROM hr_navigation_paths_tl
157 WHERE nav_path_id = l_nav_path_id;
158
159 DELETE FROM hr_navigation_paths
160 WHERE from_nav_node_usage_id = l_nav_node_usage_id
161 OR to_nav_node_usage_id = l_nav_node_usage_id
162 OR nav_path_id = l_nav_path_id;
163
164 DELETE FROM hr_navigation_node_usages
165 WHERE nav_node_usage_id = l_nav_node_usage_id;
166 END LOOP;
167 --
168 -- Delete the navigation node.
169 --
170 DELETE FROM hr_navigation_nodes
171 WHERE nav_node_id = l_nav_node_id;
172 END delete_navigation_node;
173
174 END per_maintain_taskflow;
175