1 Package Body hr_taskflow_workflow as
2 /* $Header: hrtskwkf.pkb 120.1.12000000.2 2007/04/09 14:13:57 agolechh ship $ */
3 --
4 -- WF Used Private Globals
5 --
6 g_process_activity_rec wf_process_activities%rowtype;
7 type g_usage_rec is record
8 (nav_node_usage_id hr_navigation_node_usages.nav_node_usage_id%type
9 ,instance_id wf_process_activities.instance_id%type
10 ,sqlform hr_navigation_units.form_name%type
11 ,override_label hr_navigation_paths.override_label%type);
12 type g_wf_transition_rec is record
13 (to_process_activity wf_activity_transitions.to_process_activity%type
14 ,result_code wf_activity_transitions.result_code%type);
15 type g_usage_tab is table of g_usage_rec index by binary_integer;
16 type g_trans_tab is table of g_wf_transition_rec index by binary_integer;
17 g_node_usage_tab g_usage_tab;
18 g_stack_tab g_trans_tab;
19 g_hr_app varchar2(30) := 'PER';
20 g_root_activity_name varchar2(30) := 'ROOT';
21 g_activity_type_function varchar2(30) := 'FUNCTION';
22 g_activity_type_process varchar2(30) := 'PROCESS';
23 g_activity_type_start varchar2(30) := 'START';
24 g_activity_type_end varchar2(30) := 'END';
25 g_default_transition_value varchar2(30) := '*';
26 g_parent_transition varchar2(18) := 'BUTTON_PARENT_FORM';
27 g_taskflow_activity_type varchar2(22) := 'TASKFLOW_ACTIVITY_TYPE';
28 g_hrms_sqlform varchar2(12) := 'HRMS_SQLFORM';
29 g_root_taskflow_form_sel varchar2(27) := 'ROOT_TASKFLOW_FORM_SELECTOR';
30 g_process_name varchar2(12) := 'PROCESS_NAME';
31 g_item_type varchar2(9) := 'ITEM_TYPE';
32 g_from_form_activity varchar2(18) := 'FROM_FORM_ACTIVITY';
33 g_to_form_activity varchar2(16) := 'TO_FORM_ACTIVITY';
34 g_max_number_of_buttons number := 5;
35 g_max_button_sequence_value number := 99999;
36 g_root_form_activity_id number;
37 g_root_process_id wf_process_activities.instance_id%type;
38 g_business_group_id pay_customized_restrictions.business_group_id%type;
39 g_legislation_code pay_customized_restrictions.legislation_code%type;
40 g_legislation_subgroup pay_customized_restrictions.legislation_subgroup%type;
41 g_activity_display_name wf_activities_tl.display_name%type;
42 g_process_display_name wf_activities_tl.display_name%type;
43 g_item_type_display_name wf_item_types_tl.display_name%type;
44 g_language varchar2(30);
45 g_converted_processes number;
46 --
47 -- Private Package Cursors
48 --
49 --
50 cursor g_get_process_activity(c_instance_id number) is
51 select *
52 from wf_process_activities wpa
53 where wpa.instance_id = c_instance_id;
54 --
55 cursor g_csr_root_runnable_process
56 (c_item_type varchar2, c_process_name varchar2) is
57 select wpa.activity_name
58 from wf_process_activities wpa
59 where wpa.process_version =
60 (select max(wpa1.process_version)
61 from wf_process_activities wpa1
62 where wpa1.process_name = wpa.process_name
63 and wpa1.process_item_type = wpa.process_item_type)
64 and wpa.process_item_type = c_item_type
65 and wpa.activity_name = nvl(c_process_name, wpa.activity_name)
66 and wpa.process_name = g_root_activity_name
67 order by 1;
68 --
69 -- Package Variables
70 --
71 g_package varchar2(33) := 'hr_taskflow_workflow.';
72 -- global internal workflow item attribute names
73 g_root_form_name_attr varchar2(14) := 'ROOT_FORM_NAME';
74 g_workflow_duration_attr varchar2(17) := 'WORKFLOW_DURATION';
75 g_oracle_session_id_attr varchar2(17) := 'ORACLE_SESSION_ID';
76 g_workflow_id hr_workflows.workflow_id%type;
77 g_workflow_process_mode varchar2(10);
78 -- --------------------------------------------------------------------------
79 -- |--------------------< get_item_act_display_names >----------------------|
80 -- --------------------------------------------------------------------------
81 procedure get_item_act_display_names
82 (p_instance_id in number
83 ,p_item_type_display_name out nocopy varchar2
84 ,p_activity_display_name out nocopy varchar2
85 ,p_process_display_name out nocopy varchar2) is
86 --
87 l_proc varchar2(72) := g_package||'get_item_act_display_names';
88 --
89 cursor csr_sel_names is
90 select wat1.display_name activity_display_name
91 ,witt.display_name item_display_name
92 ,wat2.display_name process_display_name
93 from wf_activities_tl wat1
97 ,wf_item_types_tl witt
94 ,wf_activities_tl wat2
95 ,wf_activities wa1
96 ,wf_activities wa2
98 ,wf_process_activities wpa
99 where wpa.instance_id = p_instance_id
100 and witt.name = wpa.activity_item_type
101 and witt.language = g_language
102 and wa1.name = wpa.activity_name
103 and wa1.item_type = wpa.activity_item_type
104 and wa1.end_date is null
105 and wat1.item_type = wa1.item_type
106 and wat1.name = wa1.name
107 and wat1.version = wa1.version
108 and wat1.language = g_language
109 and wa2.name = wpa.process_name
110 and wa2.item_type = wpa.process_item_type
111 and wa2.end_date is null
112 and wat2.item_type = wa2.item_type
113 and wat2.name = wa2.name
114 and wat2.version = wa2.version
115 and wat2.language = g_language;
116 --
117 begin
118 hr_utility.set_location('Entering:'||l_proc, 10);
119 open csr_sel_names;
120 fetch csr_sel_names
121 into p_activity_display_name, p_item_type_display_name, p_process_display_name;
122 if csr_sel_names%notfound then
123 p_item_type_display_name := null;
124 p_activity_display_name := null;
125 p_process_display_name := null;
126 end if;
127 close csr_sel_names;
128 hr_utility.set_location('Leaving:'||l_proc, 20);
129 end get_item_act_display_names;
130 -- --------------------------------------------------------------------------
131 -- |-------------------------< get_nav_node_usage_id >----------------------|
132 -- --------------------------------------------------------------------------
133 function get_nav_node_usage_id(p_instance_id in number) return number is
134 l_nav_node_usage_id number;
135 l_proc varchar2(72) := g_package||'get_nav_node_usage_id';
136 begin
137 hr_utility.set_location('Entering:'||l_proc, 10);
138 for i in g_node_usage_tab.first..g_node_usage_tab.last loop
139 if g_node_usage_tab(i).instance_id = p_instance_id then
140 hr_utility.set_location(l_proc, 15);
141 l_nav_node_usage_id := g_node_usage_tab(i).nav_node_usage_id;
142 exit;
143 end if;
144 end loop;
145 hr_utility.set_location('Leaving:'||l_proc, 20);
146 return (l_nav_node_usage_id);
147 end get_nav_node_usage_id;
148 -- --------------------------------------------------------------------------
149 -- |-------------------------< get_override_label >-------------------------|
150 -- --------------------------------------------------------------------------
151 function get_override_label(p_instance_id in number) return varchar2 is
152 l_override_label hr_navigation_paths.override_label%type := null;
153 l_proc varchar2(72) := g_package||'get_override_label';
154 begin
155 hr_utility.set_location('Entering:'||l_proc, 10);
156 for i in g_node_usage_tab.first..g_node_usage_tab.last loop
157 if g_node_usage_tab(i).instance_id = p_instance_id then
158 hr_utility.set_location(l_proc, 15);
159 l_override_label := g_node_usage_tab(i).override_label;
160 exit;
161 end if;
162 end loop;
163 hr_utility.set_location('Leaving:'||l_proc, 20);
164 return (l_override_label);
165 end get_override_label;
166 -- --------------------------------------------------------------------------
167 -- |-------------------------------< get_sqlform >-------------------------|
168 -- --------------------------------------------------------------------------
169 function get_sqlform(p_instance_id in number) return varchar2 is
170 l_sqlform hr_navigation_units.form_name%type := null;
171 l_proc varchar2(72) := g_package||'get_sqlform';
172 begin
173 hr_utility.set_location('Entering:'||l_proc, 10);
174 for i in g_node_usage_tab.first..g_node_usage_tab.last loop
175 if g_node_usage_tab(i).instance_id = p_instance_id then
176 hr_utility.set_location(l_proc, 15);
177 l_sqlform := g_node_usage_tab(i).sqlform;
178 exit;
179 end if;
180 end loop;
181 hr_utility.set_location('Leaving:'||l_proc, 20);
182 return (l_sqlform);
183 end get_sqlform;
184 -- ----------------------------------------------------------------------------
185 -- |-----------------< set_root_process_activity_id >-------------------------|
186 -- ----------------------------------------------------------------------------
187 procedure set_root_process_activity_id
188 (p_process_item_type in varchar2
189 ,p_root_process_name in varchar2) is
190 -- cursor select the Process Activity ID of the root process
191 cursor l_get_instance_id is
192 select wpa.instance_id
193 from wf_process_activities wpa
194 where wpa.process_version =
195 (select max(wpa1.process_version)
196 from wf_process_activities wpa1
197 where wpa1.process_name = wpa.process_name
198 and wpa1.process_item_type = wpa.process_item_type)
199 and wpa.process_item_type = p_process_item_type
200 and wpa.activity_name = p_root_process_name
201 and wpa.process_name = g_root_activity_name;
202 --
203 l_proc varchar2(72) := g_package||'set_root_process_activity_id';
204 --
205 begin
206 hr_utility.set_location('Entering:'||l_proc, 10);
207 open l_get_instance_id;
208 fetch l_get_instance_id into g_root_process_id;
209 if l_get_instance_id%notfound then
210 -- error the root process specified does not exist
211 close l_get_instance_id;
212 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
213 fnd_message.set_token('PROCEDURE', 'internal error');
214 fnd_message.set_token('STEP','10');
215 fnd_message.raise_error;
216 end if;
217 close l_get_instance_id;
218 hr_utility.set_location('Leaving:'||l_proc, 20);
219 end set_root_process_activity_id;
220 -- ----------------------------------------------------------------------------
221 -- |--------------------< set_root_form_activity_id >-------------------------|
222 -- ----------------------------------------------------------------------------
223 procedure set_root_form_activity_id
224 (p_process_item_type in varchar2
225 ,p_root_process_name in varchar2) is
226 -- cursor select the Process Activity ID of the activity
227 -- ROOT_TASKFLOW_FORM_SELECTOR
228 cursor l_get_instance_id is
229 select wpa.instance_id
230 from wf_process_activities wpa
231 where wpa.process_version =
232 (select max(wpa1.process_version)
233 from wf_process_activities wpa1
234 where wpa1.process_name = wpa.process_name
235 and wpa1.process_item_type = wpa.process_item_type)
236 and wpa.process_item_type = p_process_item_type
237 and wpa.process_name = p_root_process_name
238 and wpa.activity_name = g_root_taskflow_form_sel;
239 -- retrieve the transitions for the given instance id and where the
240 -- transition is not a default value
241 cursor l_get_root_instance_id(c_instance_id number) is
242 select wat.to_process_activity
243 ,wat.result_code
244 from wf_activity_transitions wat
245 where wat.result_code <> g_default_transition_value
246 and wat.from_process_activity = c_instance_id;
247 --
248 l_proc varchar2(72) := g_package||'set_root_form_activity_id';
249 l_instance_id number;
250 l_index number := 0;
251 l_result_code wf_activity_transitions.result_code%type;
252 --
253 begin
254 hr_utility.set_location('Entering:'||l_proc, 10);
255 -- get the instance_id of the ROOT_TASKFLOW_FORM_SELECTOR
256 -- the reason a for loop is used is to check that one and only
257 -- only ROOT_TASKFLOW_FORM_SELECTOR exists
258 for l_csr in l_get_instance_id loop
259 -- incrment the counter
260 l_index := l_index + 1;
261 if l_index > 1 then
262 hr_utility.set_location(l_proc, 15);
263 -- exit out of the loop if more than one row is returned
264 exit;
265 else
266 -- set the instance id
267 l_instance_id := l_csr.instance_id;
268 end if;
269 end loop;
270 hr_utility.set_location(l_proc, 20);
271 --
272 if l_index = 0 then
273 -- a root selector does not exist for the process
274 -- select the root process details that is in error
275 get_item_act_display_names
276 (p_instance_id => g_root_process_id
277 ,p_item_type_display_name => g_item_type_display_name
278 ,p_activity_display_name => g_activity_display_name
279 ,p_process_display_name => g_process_display_name);
280 --
281 fnd_message.set_name(g_hr_app, 'HR_52950_WKF2TSK_NO_ROOT_SEL');
282 fnd_message.set_token(g_process_name, g_activity_display_name);
283 fnd_message.set_token(g_item_type, g_item_type_display_name);
284 fnd_message.raise_error;
285 elsif l_index > 1 then
286 -- more than one root selector exists error
287 -- select the root process details that is in error
288 get_item_act_display_names
289 (p_instance_id => g_root_process_id
290 ,p_item_type_display_name => g_item_type_display_name
291 ,p_activity_display_name => g_activity_display_name
292 ,p_process_display_name => g_process_display_name);
293 --
294 fnd_message.set_name(g_hr_app, 'HR_52951_WKF2TSK_ROOT_SELS');
295 fnd_message.set_token(g_process_name, g_activity_display_name);
296 fnd_message.set_token(g_item_type, g_item_type_display_name);
297 fnd_message.raise_error;
298 end if;
299 hr_utility.set_location(l_proc, 25);
300 -- now that we have found the selector activity we must determine the
301 -- root form
302 l_index := 0;
303 for l_csr in l_get_root_instance_id(l_instance_id) loop
304 -- incrment the counter
305 l_index := l_index + 1;
306 if l_index > 1 then
307 hr_utility.set_location(l_proc, 30);
308 exit;
309 else
310 -- ensure that the activity selected is a form
311 g_root_form_activity_id := l_csr.to_process_activity;
312 l_result_code := l_csr.result_code;
313 end if;
314 end loop;
315 hr_utility.set_location(l_proc, 35);
316 --
317 if l_index = 0 then
318 -- the root form does not actually transition to a form so error
319 -- select the root process details that is in error
320 get_item_act_display_names
321 (p_instance_id => g_root_process_id
322 ,p_item_type_display_name => g_item_type_display_name
323 ,p_activity_display_name => g_activity_display_name
324 ,p_process_display_name => g_process_display_name);
325 --
326 fnd_message.set_name(g_hr_app, 'HR_52952_WKF2TSK_ROOT_SEL_TRAN');
327 fnd_message.set_token(g_process_name, g_activity_display_name);
328 fnd_message.set_token(g_item_type, g_item_type_display_name);
329 fnd_message.raise_error;
330 elsif l_index > 1 then
331 -- the root selector transitions to more than one form so error
332 -- select the root process details that is in error
333 get_item_act_display_names
334 (p_instance_id => g_root_process_id
335 ,p_item_type_display_name => g_item_type_display_name
336 ,p_activity_display_name => g_activity_display_name
337 ,p_process_display_name => g_process_display_name);
338 --
339 fnd_message.set_name(g_hr_app, 'HR_52953_WKF2TSK_ROOT_SEL_TRAS');
340 fnd_message.set_token(g_process_name, g_activity_display_name);
341 fnd_message.set_token(g_item_type, g_item_type_display_name);
342 fnd_message.raise_error;
343 end if;
344 hr_utility.set_location(l_proc, 40);
345 -- ensure that the activity selected is a form and the transition matches the activity name
346 open g_get_process_activity(g_root_form_activity_id);
347 fetch g_get_process_activity into g_process_activity_rec;
348 if g_get_process_activity%notfound then
349 close g_get_process_activity;
350 -- the instance_id does not exist this is a serious internal error
351 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
352 fnd_message.set_token('PROCEDURE', 'internal error');
353 fnd_message.set_token('STEP','40');
354 fnd_message.raise_error;
355 end if;
356 close g_get_process_activity;
357 hr_utility.set_location(l_proc, 45);
358 if g_process_activity_rec.activity_name <> l_result_code then
359 -- the root transition does not match a sql form activity
360 -- select the root process details that is in error
361 get_item_act_display_names
362 (p_instance_id => g_root_process_id
363 ,p_item_type_display_name => g_item_type_display_name
364 ,p_activity_display_name => g_activity_display_name
365 ,p_process_display_name => g_process_display_name);
366 --
367 fnd_message.set_name(g_hr_app, 'HR_52954_WKF2TSK_ROOT_WRN_SEL');
368 fnd_message.set_token(g_process_name, g_activity_display_name);
369 fnd_message.set_token(g_item_type, g_item_type_display_name);
370 fnd_message.raise_error;
371 end if;
372 hr_utility.set_location('Leaving:'||l_proc, 50);
373 end set_root_form_activity_id;
374 -- ----------------------------------------------------------------------------
375 -- |---------------------< set_business_legislation >-------------------------|
376 -- ----------------------------------------------------------------------------
377 procedure set_business_legislation
378 (p_business_group_id in pay_customized_restrictions.business_group_id%type
379 ,p_legislation_code in pay_customized_restrictions.legislation_code%type
380 ,p_legislation_subgroup in pay_customized_restrictions.legislation_subgroup%type) is
381 --
382 l_proc varchar2(72) := g_package||'set_business_legislation';
383 --
384 begin
385 hr_utility.set_location('Entering:'||l_proc, 10);
386 g_business_group_id := p_business_group_id;
387 g_legislation_code := p_legislation_code;
388 g_legislation_subgroup := p_legislation_subgroup;
389 hr_utility.set_location('Leaving:'||l_proc, 20);
390 end set_business_legislation;
391 -- ----------------------------------------------------------------------------
392 -- |------------------------------< insert_workflow >-------------------------|
393 -- ----------------------------------------------------------------------------
394 procedure insert_workflow
395 (p_process_name in varchar2) is
396 --
397 cursor l_csr_workflow_id is
398 select hw.workflow_id
399 from hr_workflows hw
400 where hw.workflow_name = p_process_name;
401 --
402 cursor l_csr_nav_usages(c_workflow_id number) is
403 select hnnu.nav_node_usage_id
404 from hr_navigation_node_usages hnnu
405 where hnnu.workflow_id = c_workflow_id;
406 --
407 l_proc varchar2(72) := g_package||'insert_workflow';
408 l_nav_path_id number;
409
410 -- start of Bug 4506198
411
412 l_nav_node_usage_id hr_navigation_node_usages.nav_node_usage_id%type;
413 cursor l_csr_nav_paths(p_nav_id hr_navigation_node_usages.nav_node_usage_id%type) is
414 select nav_path_id
415 from hr_navigation_paths hnp
416 where hnp.from_nav_node_usage_id = p_nav_id
417 or hnp.to_nav_node_usage_id = p_nav_id;
418
419 -- end of bug 4506198
420 --
421 begin
422 hr_utility.set_location('Entering:'||l_proc, 10);
423 -- check to see if the workflow exists for the process name
424 open l_csr_workflow_id;
425 fetch l_csr_workflow_id into g_workflow_id;
426 if l_csr_workflow_id%notfound then
427 hr_utility.set_location(l_proc, 15);
428 -- select the sequence
429 begin
430 select hr_workflows_s.nextval
431 into g_workflow_id
432 from sys.dual;
433 exception
434 when others then
435 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
436 fnd_message.set_token('PROCEDURE', l_proc);
437 fnd_message.set_token('STEP','10');
438 fnd_message.raise_error;
439 end;
440 hr_utility.set_location(l_proc, 20);
441 -- the workflow does not exist so create it
442 insert into hr_workflows (workflow_id, workflow_name) values (g_workflow_id, p_process_name);
443 g_workflow_process_mode := 'INSERT';
444 else
445 hr_utility.set_location(l_proc, 25);
446 -- set the workflow process mode to UPDATE
447 g_workflow_process_mode := 'UPDATE';
448 -- as we are updating the workflow we must delete the navigation paths
449 -- and current workflow node usages
450 for csr_csr in l_csr_nav_usages(g_workflow_id) loop
451 -- delete the paths
452
453 -- start of bug 4506198
454 /*select nav_path_id
455 into l_nav_path_id
456 from hr_navigation_paths hnp
457 where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
458 or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id; */
459
460 l_nav_node_usage_id:=csr_csr.nav_node_usage_id;
461 for csr_paths in l_csr_nav_paths(l_nav_node_usage_id) loop
462
463 delete from hr_navigation_paths_tl hnp
464 where nav_path_id = csr_paths.nav_path_id;
465
466 delete from hr_navigation_paths hnp
467 where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
468 or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id;
469
470 end loop;
471
472 delete from hr_navigation_paths_tl hnp
473 where nav_path_id = l_nav_path_id;
474
475 delete from hr_navigation_paths hnp
476 where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
477 or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id;
478 -- delete the usage
479 delete from hr_navigation_node_usages hnnu
480 where hnnu.nav_node_usage_id = csr_csr.nav_node_usage_id;
481 end loop;
482 end if;
483 hr_utility.set_location(l_proc, 30);
484 close l_csr_workflow_id;
485 hr_utility.set_location('Leaving:'||l_proc, 35);
486 end insert_workflow;
487 -- ----------------------------------------------------------------------------
488 -- |----------------------< insert_navigation_paths >-------------------------|
489 -- ----------------------------------------------------------------------------
490 procedure insert_navigation_paths is
491 -- --------------------------------------------------------------------------
492 -- |-------------------------< private cursors >----------------------------|
493 -- --------------------------------------------------------------------------
494 cursor csr_process_start_transitions
495 (c_instance_id wf_process_activities.instance_id%type) is
496 -- select the START result codes
497 select wat.from_process_activity
498 ,wat.to_process_activity
499 ,wat.result_code
500 from wf_process_activities wpa1
501 ,wf_process_activities wpa2
502 ,wf_activity_transitions wat
503 where wpa1.instance_id = c_instance_id
504 and wpa2.process_name = wpa1.activity_name
505 and wpa2.process_item_type = wpa1.activity_item_type
506 and wpa2.start_end = g_activity_type_start
507 and wpa2.process_version =
508 (select max(wpa3.process_version)
509 from wf_process_activities wpa3
510 where wpa3.process_name = wpa2.process_name
511 and wpa3.process_item_type = wpa2.process_item_type)
512 and wat.from_process_activity = wpa2.instance_id;
513 --
514 cursor csr_process_end_transitions
515 (c_instance_id wf_process_activities.instance_id%type
516 ,c_result_code wf_activity_transitions.result_code%type) is
517 -- select the END result codes
518 select wat.from_process_activity
519 ,wat.to_process_activity
520 ,wat.result_code
521 from wf_process_activities wpa1
522 ,wf_process_activities wpa2
523 ,wf_activity_transitions wat
524 where wpa1.instance_id = c_instance_id
525 and wpa1.start_end = g_activity_type_end
526 and wpa2.activity_name = wpa1.process_name
527 and wpa2.activity_item_type = wpa1.process_item_type
528 and wpa2.process_version =
529 (select max(wpa3.process_version)
530 from wf_process_activities wpa3
531 where wpa3.process_name = wpa2.process_name
532 and wpa3.process_item_type = wpa2.process_item_type)
533 and wat.from_process_activity = wpa2.instance_id
534 and wat.result_code = c_result_code;
535 --
536 cursor csr_transitions(c_instance_id number) is
537 -- selects the transition information for the specified activity
538 -- instance
539 select wat.from_process_activity
540 ,wat.to_process_activity
541 ,wat.result_code
542 from wf_activity_transitions wat
543 where wat.from_process_activity = c_instance_id;
544 --
545 cursor csr_transitions1(c_instance_id number) is
546 -- selects the transition information for the specified activity
547 -- instance
548 select wat.from_process_activity
549 ,wat.to_process_activity
550 ,wat.result_code
551 from wf_activity_transitions wat
552 where wat.from_process_activity = c_instance_id;
553 -- selects an activity type and start_end value for a specified
554 -- activity instance
555 cursor csr_attivity_type(c_instance_id number) is
556 select wa.type
557 ,wpa.start_end
558 from wf_activities wa
559 ,wf_process_activities wpa
560 where wpa.instance_id = c_instance_id
561 and wa.item_type = wpa.activity_item_type
562 and wa.name = wpa.activity_name
563 and wa.end_date is null;
564
565 /*-- Chages start for the bug 5702720 ---*/
566 Cursor csr_language_code(T_NAV_PATH_ID number) is
567 select L.language_code l_language_code
568 from FND_LANGUAGES L
569 where L.INSTALLED_FLAG in ('I', 'B')
570 and not exists
571 (select NULL
572 from HR_NAVIGATION_PATHS_TL T
573 where T.NAV_PATH_ID = T_NAV_PATH_ID
574 and T.LANGUAGE = L.LANGUAGE_CODE);
575 /*-- Chages End for the bug 5702720 ---*/
576 --
577 -- --------------------------------------------------------------------------
578 -- |-----------------------< private variables >----------------------------|
579 -- --------------------------------------------------------------------------
580 l_proc varchar2(72) := g_package||'insert_navigation_paths';
581 l_expected_sqlform varchar2(30);
582 l_activity_type varchar2(30);
583 l_found_form boolean;
584 l_return_to_root boolean;
585 l_dummy boolean;
586 l_current_language varchar2(3);
587 l_nav_path_id number;
588 l_pop_from_process_activity wf_activity_transitions.from_process_activity%type;
589 l_pop_to_process_activity wf_activity_transitions.to_process_activity%type;
590 l_pop_result_code wf_activity_transitions.result_code%type;
591 -- define structure types
592 type l_result_stack_rec is record
593 (from_process_activity wf_activity_transitions.from_process_activity%type
594 ,to_process_activity wf_activity_transitions.to_process_activity%type
595 ,result_code wf_activity_transitions.result_code%type);
596 type l_insert_path_rec is record
597 (from_nav_node_usage_id hr_navigation_paths.from_nav_node_usage_id%type
598 ,to_nav_node_usage_id hr_navigation_paths.to_nav_node_usage_id%type
599 ,nav_button_required hr_navigation_paths.nav_button_required%type
600 ,sequence hr_navigation_paths.sequence%type
601 ,override_label hr_navigation_paths.override_label%type
602 ,result_code wf_activity_transitions.result_code%type
603 ,insert_path boolean
604 );
605 type l_parent_rec is record
606 (from_nav_node_usage_id hr_navigation_paths.from_nav_node_usage_id%type
607 ,to_nav_node_usage_id hr_navigation_paths.to_nav_node_usage_id%type
608 ,sequence hr_navigation_paths.sequence%type
609 ,parent_index number);
610 type l_visit_list_tab is table of
611 wf_activity_transitions.from_process_activity%type
612 index by binary_integer;
613 type l_result_stack_tab is table of l_result_stack_rec
614 index by binary_integer;
615 type l_insert_path_tab is table of l_insert_path_rec
616 index by binary_integer;
617 type l_parent_tab is table of l_parent_rec
618 index by binary_integer;
619 -- define the structures
620 l_result_stack_struct l_result_stack_tab;
621 l_visit_list_struct l_visit_list_tab;
622 l_insert_path_struct l_insert_path_tab;
623 l_parent_struct l_parent_tab;
624
625 -- --------------------------------------------------------------------------
626 -- PRIVATE PROCEDURES AND FUNCTIONS --
627 -- --------------------------------------------------------------------------
628 -- |-----------------------< push_result_stack >----------------------------|
629 -- --------------------------------------------------------------------------
630 procedure push_result_stack
631 (p_from_process_activity in wf_activity_transitions.from_process_activity%type
632 ,p_to_process_activity in wf_activity_transitions.to_process_activity%type
633 ,p_result_code in wf_activity_transitions.result_code%type) is
634 --
635 l_index binary_integer := l_result_stack_struct.count + 1;
636 l_proc varchar2(72) := g_package||'push_result_stack';
637 --
638 begin
639 hr_utility.set_location('Entering:'||l_proc, 10);
640 l_result_stack_struct(l_index).from_process_activity := p_from_process_activity;
641 l_result_stack_struct(l_index).to_process_activity := p_to_process_activity;
642 l_result_stack_struct(l_index).result_code := p_result_code;
643 hr_utility.set_location('Leaving:'||l_proc, 20);
644 end push_result_stack;
645 -- --------------------------------------------------------------------------
646 -- |--------------------------< pop_result_stack >--------------------------|
647 -- --------------------------------------------------------------------------
648 procedure pop_result_stack
649 (p_from_process_activity out nocopy wf_activity_transitions.from_process_activity%type
650 ,p_to_process_activity out nocopy wf_activity_transitions.to_process_activity%type
651 ,p_result_code out nocopy wf_activity_transitions.result_code%type) is
652 --
653 l_proc varchar2(72) := g_package||'pop_result_stack';
654 --
655 begin
656 hr_utility.set_location('Entering:'||l_proc, 10);
657 if l_result_stack_struct.count < 1 then
658 hr_utility.set_location(l_proc, 15);
659 -- the stack is empty so return nulls
660 p_from_process_activity := null;
661 p_to_process_activity := null;
662 p_result_code := null;
663 else
664 hr_utility.set_location(l_proc, 20);
665 -- pop the last entry
666 p_from_process_activity :=
667 l_result_stack_struct(l_result_stack_struct.last).from_process_activity;
668 p_to_process_activity :=
669 l_result_stack_struct(l_result_stack_struct.last).to_process_activity;
670 p_result_code :=
671 l_result_stack_struct(l_result_stack_struct.last).result_code;
672 -- delete the last stack entry
673 l_result_stack_struct.delete(l_result_stack_struct.last);
674 end if;
675 hr_utility.set_location('Leaving:'||l_proc, 25);
676 end pop_result_stack;
677 -- --------------------------------------------------------------------------
678 -- |---------------------------< zap_result_stack >-------------------------|
679 -- --------------------------------------------------------------------------
680 procedure zap_result_stack is
681 l_proc varchar2(72) := g_package||'zap_result_stack';
682 begin
683 hr_utility.set_location('Entering:'||l_proc, 10);
684 l_result_stack_struct.delete;
685 hr_utility.set_location('Leaving:'||l_proc, 20);
686 end zap_result_stack;
687 -- --------------------------------------------------------------------------
688 -- |---------------------------< exist_in_visit_list >----------------------|
689 -- --------------------------------------------------------------------------
690 function exist_in_visit_list
691 (p_from_process_activity in wf_activity_transitions.from_process_activity%type)
692 return boolean is
693 l_proc varchar2(72) := g_package||'exist_in_visit_list';
694 l_return boolean := false;
695 begin
696 hr_utility.set_location('Entering:'||l_proc, 10);
697 if l_visit_list_struct.count > 0 then
698 hr_utility.set_location('Entering:'||l_proc, 15);
699 for i in l_visit_list_struct.first..l_visit_list_struct.last loop
700 if l_visit_list_struct(i) = p_from_process_activity then
701 hr_utility.set_location(l_proc, 20);
702 l_return := true;
703 exit;
704 end if;
705 end loop;
706 end if;
707 hr_utility.set_location('Leaving:'||l_proc, 25);
708 return(l_return);
709 end exist_in_visit_list;
710 -- --------------------------------------------------------------------------
711 -- |---------------------------< set_visit_activity >----------------------|
712 -- --------------------------------------------------------------------------
713 function set_visit_activity
714 (p_from_process_activity in wf_activity_transitions.from_process_activity%type)
715 return boolean is
716 l_proc varchar2(72) := g_package||'set_visit_activity';
717 l_return boolean := false;
718 begin
719 hr_utility.set_location('Entering:'||l_proc, 10);
720 -- if the activity doesn't already exist in the visit list the
721 -- add it
722 if not exist_in_visit_list(p_from_process_activity) then
723 hr_utility.set_location(l_proc, 15);
724 l_visit_list_struct(l_visit_list_struct.count + 1) := p_from_process_activity;
725 l_return := true;
726 end if;
727 hr_utility.set_location('Leaving:'||l_proc, 20);
728 return(l_return);
729 end set_visit_activity;
730 -- --------------------------------------------------------------------------
731 -- |--------------------------< zap_visit_list >----------------------------|
732 -- --------------------------------------------------------------------------
733 procedure zap_visit_list is
734 l_proc varchar2(72) := g_package||'zap_visit_list';
735 begin
736 hr_utility.set_location('Entering:'||l_proc, 10);
737 l_visit_list_struct.delete;
738 hr_utility.set_location('Leaving:'||l_proc, 20);
739 end zap_visit_list;
740 -- --------------------------------------------------------------------------
741 -- |-------------------------< get_button_details >-------------------------|
742 -- --------------------------------------------------------------------------
743 procedure get_button_details
744 (p_from_process_activity in number
745 ,p_to_process_activity in number
746 ,p_from_nav_node_usage_id in number
747 ,p_to_nav_node_usage_id in number
748 ,p_index in number
749 ,p_override_label out nocopy varchar2
750 ,p_sequence out nocopy number
751 ,p_nav_button_required out nocopy varchar2) is
752 --
753 l_proc varchar2(72) := g_package||'get_button_details';
754 --
755 cursor csr_button_values(c_form_name varchar2) is
756 -- selects DISPLAY_BUTTONx name for the specified process activity
757 -- and where the form_name is the same or is a form_name does not
758 -- exist check to see if a parent has been specified
759 select waav.name
760 ,waav.text_value
761 from wf_activity_attr_values waav
762 where waav.process_activity_id = p_from_process_activity
763 and waav.name like 'DISPLAY_BUTTON_'
764 and (waav.text_value = c_form_name
765 or waav.text_value = g_parent_transition);
766 --
767 l_nav_button_required hr_navigation_paths.nav_button_required%type := 'N';
768 l_name wf_activity_attr_values.name%type;
769 l_sqlform hr_navigation_units.form_name%type;
770 l_parent boolean := false;
771 l_index binary_integer;
772 --
773 begin
774 hr_utility.set_location('Entering:'||l_proc, 10);
775 -- get the sqlform we are going to
776 l_sqlform := get_sqlform(p_to_process_activity);
777 -- determine the location of the button in the from process activity
778 -- attributes
779 for csr_csr in csr_button_values(l_sqlform) loop
780 l_name := csr_csr.name;
781 if csr_csr.text_value = l_sqlform then
782 -- we have found the button so we must display it
783 l_parent := false;
784 l_nav_button_required := 'Y';
785 exit;
786 else
787 -- we have found a parent
788 l_parent := true;
789 end if;
790 end loop;
791 -- check to see if only a parent was found
792 if l_parent then
793 l_index := l_parent_struct.count + 1;
794 -- because the button could be a parent we must add to the parent list
795 -- for further processing
796 l_parent_struct(l_index).from_nav_node_usage_id :=
797 p_from_nav_node_usage_id;
798 l_parent_struct(l_index).to_nav_node_usage_id :=
799 p_to_nav_node_usage_id;
800 l_parent_struct(l_index).sequence :=
801 to_number(replace(l_name,'DISPLAY_BUTTON',null));
802 l_parent_struct(l_index).parent_index := p_index;
803 end if;
804 -- set the p_nav_button_required out var
805 p_nav_button_required := l_nav_button_required;
806 -- set the p_sequence var
807 if l_nav_button_required = 'Y' then
808 p_sequence := to_number(replace(l_name,'DISPLAY_BUTTON',null));
809 else
810 p_sequence := 5;
811 end if;
812 -- set the override label
813 p_override_label := get_override_label(p_to_process_activity);
814 hr_utility.set_location('Leaving:'||l_proc, 20);
815 end get_button_details;
816 -- --------------------------------------------------------------------------
817 -- |---------------------< correct_parent_buttons >-------------------------|
818 -- --------------------------------------------------------------------------
819 procedure correct_parent_buttons is
820 l_proc varchar2(72) := g_package||'correct_parent_buttons';
821 begin
822 hr_utility.set_location('Entering:'||l_proc, 10);
823 if l_parent_struct.count > 0 then
824 for i in l_parent_struct.first..l_parent_struct.last loop
825 for j in l_insert_path_struct.first..l_insert_path_struct.last loop
826 if l_insert_path_struct(j).from_nav_node_usage_id =
827 l_parent_struct(i).to_nav_node_usage_id and
828 l_insert_path_struct(j).to_nav_node_usage_id =
829 l_parent_struct(i).from_nav_node_usage_id then
830 -- parent does exist
831 l_insert_path_struct(l_parent_struct(i).parent_index).sequence
832 := l_parent_struct(i).sequence;
833 if l_parent_struct(i).sequence < 5 then
834 l_insert_path_struct(l_parent_struct(i).parent_index).nav_button_required
835 := 'Y';
836 exit;
837 else
838 l_insert_path_struct(l_parent_struct(i).parent_index).nav_button_required
839 := 'N';
840 end if;
841 end if;
842 end loop;
843 end loop;
844 end if;
845 hr_utility.set_location('Leaving:'||l_proc, 20);
846 end correct_parent_buttons;
847 -- --------------------------------------------------------------------------
848 -- |----------------------------< set_insert_path >-------------------------|
849 -- --------------------------------------------------------------------------
850 procedure set_insert_path
851 (p_from_nav_node_usage_id in number
852 ,p_from_process_activity in number
853 ,p_to_process_activity in number
854 ,p_result_code in varchar2) is
855 --
856 l_proc varchar2(72) := g_package||'set_insert_path';
857 l_button_text varchar2(40);
858 l_button_sequence number := g_max_button_sequence_value;
859 l_max_number_of_buttons number := g_max_number_of_buttons;
860 l_nav_button_required varchar2(1);
861 l_number_of_paths number := 0;
862 l_max_display_sequence number := 0;
863 l_max_display_seq_index number := 0;
864 l_index binary_integer;
865 l_insert_path boolean;
866 l_to_nav_node_usage_id number;
867 begin
868 hr_utility.set_location('Entering:'||l_proc, 10);
869 -- determine if the path is navigating back to the root form
870 l_to_nav_node_usage_id := get_nav_node_usage_id(p_to_process_activity);
871 if p_to_process_activity = g_root_form_activity_id then
872 -- ok navigating back to the root so create the path
873 l_insert_path := false;
874 else
875 l_insert_path := true;
876 end if;
877 -- the path is not going 2 the root activity so determine the path
878 if l_insert_path_struct.count > 0 then
879 -- ensure that an entry does not already exist for this form and
880 -- result combination. also count the number of navigation paths
881 for i in l_insert_path_struct.first..l_insert_path_struct.last loop
882 if l_insert_path_struct(i).from_nav_node_usage_id =
883 p_from_nav_node_usage_id then
884 hr_utility.set_location(l_proc, 15);
885 if l_insert_path_struct(i).result_code = p_result_code or
886 l_insert_path_struct(i).to_nav_node_usage_id = l_to_nav_node_usage_id then
887 -- a serious error has occurred, you cannot have a 'FROM' form activity
888 -- have more than one path to the same form.
889 -- select the process details that is in error
890 get_item_act_display_names
891 (p_instance_id => p_from_process_activity
892 ,p_item_type_display_name => g_item_type_display_name
893 ,p_activity_display_name => g_activity_display_name
894 ,p_process_display_name => g_process_display_name);
895 -- get the TO_FORM_ACTIVITY details
896 fnd_message.set_name(g_hr_app, 'HR_52955_WKF2TSK_SAME_SQLFORM');
897 fnd_message.set_token(g_process_name, g_process_display_name);
898 fnd_message.set_token(g_item_type, g_item_type_display_name);
899 fnd_message.set_token(g_from_form_activity, g_activity_display_name);
900 -- get the TO_FORM_ACTIVITY details
901 get_item_act_display_names
902 (p_instance_id => p_to_process_activity
903 ,p_item_type_display_name => g_item_type_display_name
904 ,p_activity_display_name => g_activity_display_name
905 ,p_process_display_name => g_process_display_name);
906 --
907 fnd_message.set_token(g_to_form_activity, g_item_type_display_name);
908 fnd_message.raise_error;
909 end if;
910 end if;
911 end loop;
912 end if;
913 l_index := l_insert_path_struct.count + 1;
914 -- get the button details
915 get_button_details
916 (p_from_process_activity => p_from_process_activity
917 ,p_to_process_activity => p_to_process_activity
918 ,p_from_nav_node_usage_id => p_from_nav_node_usage_id
919 ,p_to_nav_node_usage_id => get_nav_node_usage_id(p_to_process_activity)
920 ,p_index => l_index
921 ,p_override_label => l_button_text
922 ,p_sequence => l_button_sequence
923 ,p_nav_button_required => l_nav_button_required);
924 --
925 hr_utility.set_location(l_proc, 85);
926 -- insert the path
927 l_insert_path_struct(l_index).from_nav_node_usage_id := p_from_nav_node_usage_id;
928 l_insert_path_struct(l_index).to_nav_node_usage_id := l_to_nav_node_usage_id;
929 l_insert_path_struct(l_index).nav_button_required := l_nav_button_required;
930 l_insert_path_struct(l_index).sequence := l_button_sequence;
931 l_insert_path_struct(l_index).override_label := l_button_text;
932 l_insert_path_struct(l_index).result_code := p_result_code;
933 l_insert_path_struct(l_index).insert_path := l_insert_path;
934 hr_utility.set_location('Leaving:'||l_proc, 90);
935 end set_insert_path;
936 -- --------------------------------------------------------------------------
937 -- |---------------------------< get_activity_type >------------------------|
938 -- --------------------------------------------------------------------------
939 function get_activity_type
940 (p_to_process_activity in number
941 ,p_expected_sqlform in varchar2) return varchar2 is
942 -- return code: description:
943 -- CORRECT_SQLFORM
944 -- INCORRECT_SQLFORM
945 -- PROCESS
946 -- END
947 -- OTHER
948 l_proc varchar2(72) := g_package||'get_activity_type';
952 hr_utility.set_location('Entering:'||l_proc, 10);
949 l_activity_type varchar2(30) := null;
950 l_start_end varchar2(30) := null;
951 begin
953 -- determine if the activity is in the corresponding usages table
954 for i in g_node_usage_tab.first..g_node_usage_tab.last loop
955 if g_node_usage_tab(i).instance_id = p_to_process_activity then
956 hr_utility.set_location(l_proc, 15);
957 -- the activity instance is a sql*form but is it the one we
958 -- are looking for?
959 if g_node_usage_tab(i).sqlform = p_expected_sqlform or
960 p_expected_sqlform = g_default_transition_value or
961 p_expected_sqlform = g_parent_transition then
962 hr_utility.set_location(l_proc, 20);
963 l_activity_type := 'CORRECT_SQLFORM';
964 else
965 hr_utility.set_location(l_proc, 25);
966 l_activity_type := 'INCORRECT_SQLFORM';
967 end if;
968 --
969 exit;
970 end if;
971 end loop;
972 --
973 hr_utility.set_location(l_proc, 30);
974 if l_activity_type is null then
975 hr_utility.set_location(l_proc, 35);
976 -- the activity is not a sqlform so we must determine if it
977 -- is a process, end activity or any other type of activity
978 open csr_attivity_type(p_to_process_activity);
979 fetch csr_attivity_type into l_activity_type, l_start_end;
980 if csr_attivity_type%notfound then
981 -- the activity does not exist. this is a serious internal
982 -- error which we must report
983 close csr_attivity_type;
984 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
985 fnd_message.set_token('PROCEDURE', l_proc);
986 fnd_message.set_token('STEP','10');
987 fnd_message.raise_error;
988 end if;
989 close csr_attivity_type;
990 hr_utility.set_location(l_proc, 40);
991 -- check to see if the activity is not a PROCESS
992 if l_activity_type <> g_activity_type_process then
993 hr_utility.set_location(l_proc, 45);
994 -- determine if the process is a function
995 if l_activity_type = g_activity_type_function then
996 -- as the activity type is a FUNCTION we must determine if
997 -- it is an END function
998 hr_utility.set_location(l_proc, 50);
999 if l_start_end = g_activity_type_end then
1000 hr_utility.set_location(l_proc, 55);
1001 l_activity_type := l_start_end;
1002 end if;
1003 else
1004 hr_utility.set_location(l_proc, 60);
1005 -- set the activity to 'OTHER'
1006 l_activity_type := 'OTHER';
1007 end if;
1008 end if;
1009 end if;
1010 return(l_activity_type);
1011 hr_utility.set_location('Leaving:'||l_proc, 65);
1012 end get_activity_type;
1013 -- ----------------------------------------------------------------------------
1014 -- |------------------------------< MAIN BODY >-------------------------------|
1015 -- ----------------------------------------------------------------------------
1016 begin
1017 hr_utility.set_location('Entering:'||l_proc, 10);
1018 -- delete the parents table
1019 l_parent_struct.delete;
1020 -- loop through each inserted navigation node usage
1021 for l_usage_index in g_node_usage_tab.first..g_node_usage_tab.last loop
1022 -- determine if we are on the root node
1023 if g_node_usage_tab(l_usage_index).instance_id = g_root_form_activity_id then
1024 -- we are on the root node so we don't need to have a transition back
1025 -- to itself
1026 l_return_to_root := true;
1027 else
1028 -- we are NOT on the root node so we must have a transition back
1029 -- to the root
1030 l_return_to_root := false;
1031 end if;
1032 -- for each usage get the corresponding transition information
1033 for l_result_csr in csr_transitions
1034 (g_node_usage_tab(l_usage_index).instance_id) loop
1035 -- set the expect sqlform
1036 l_expected_sqlform := l_result_csr.result_code;
1037 -- determine if the transition goes to the expected sql*form
1038 l_activity_type := get_activity_type
1039 (l_result_csr.to_process_activity
1040 ,l_expected_sqlform);
1041 -- branch on the activity type
1042 if l_activity_type = 'CORRECT_SQLFORM' then
1043 hr_utility.set_location(l_proc, 15);
1044 -- determine if the form we are navigation to is the root form
1045 if l_result_csr.to_process_activity = g_root_form_activity_id then
1046 -- ok we transition back to the root form so we must
1047 -- indicate this
1048 l_return_to_root := true;
1049 end if;
1050 -- the correct sql*form was found so we must insert it into the
1051 -- insert navigation path stack
1052 set_insert_path
1053 (p_from_nav_node_usage_id =>
1054 g_node_usage_tab(l_usage_index).nav_node_usage_id
1055 ,p_from_process_activity =>
1056 g_node_usage_tab(l_usage_index).instance_id
1057 ,p_to_process_activity => l_result_csr.to_process_activity
1058 ,p_result_code => l_expected_sqlform);
1059 --
1060 elsif l_activity_type = 'INCORRECT_SQLFORM' then
1061 -- an incorrect sql*form was found so we must error
1062 -- select the process details that is in error
1063 get_item_act_display_names
1064 (p_instance_id => g_node_usage_tab(l_usage_index).instance_id
1065 ,p_item_type_display_name => g_item_type_display_name
1066 ,p_activity_display_name => g_activity_display_name
1067 ,p_process_display_name => g_process_display_name);
1068 -- get the TO_FORM_ACTIVITY details
1072 fnd_message.set_token(g_from_form_activity, g_activity_display_name);
1069 fnd_message.set_name(g_hr_app, 'HR_52956_WKF2TSK_WRONG_SQLFORM');
1070 fnd_message.set_token(g_process_name, g_process_display_name);
1071 fnd_message.set_token(g_item_type, g_item_type_display_name);
1073 -- get the TO_FORM_ACTIVITY details
1074 get_item_act_display_names
1075 (p_instance_id => l_result_csr.to_process_activity
1076 ,p_item_type_display_name => g_item_type_display_name
1077 ,p_activity_display_name => g_activity_display_name
1078 ,p_process_display_name => g_process_display_name);
1079 --
1080 fnd_message.set_token(g_to_form_activity, g_item_type_display_name);
1081 fnd_message.raise_error;
1082 else
1083 hr_utility.set_location(l_proc, 20);
1084 -- delete the results stack
1085 zap_result_stack;
1086 hr_utility.set_location(l_proc, 25);
1087 -- delete the visit list
1088 zap_visit_list;
1089 -- set found form to false
1090 l_found_form := false;
1091 -- add activity to visit list
1092 l_dummy := set_visit_activity(g_node_usage_tab(l_usage_index).instance_id);
1093 hr_utility.set_location(l_proc, 30);
1094 -- push onto results stack
1095 push_result_stack
1096 (g_node_usage_tab(l_usage_index).instance_id
1097 ,l_result_csr.to_process_activity
1098 ,l_result_csr.result_code);
1099 hr_utility.set_location(l_proc, 35);
1100 -- while the stack is not empty loop
1101 while l_result_stack_struct.count > 0 loop
1102 -- pop the result stack
1103 pop_result_stack
1104 (l_pop_from_process_activity
1105 ,l_pop_to_process_activity
1106 ,l_pop_result_code);
1107 -- does the activty goto the expect form?
1108 l_activity_type := get_activity_type
1109 (l_pop_to_process_activity
1110 ,l_expected_sqlform);
1111 if l_activity_type = 'CORRECT_SQLFORM' then
1112 hr_utility.set_location(l_proc, 40);
1113 -- ok we transition back to the root form so we must
1114 -- indicate this
1115 if l_pop_to_process_activity = g_root_form_activity_id then
1116 l_return_to_root := true;
1117 end if;
1118 -- the correct sql*form was found so we must insert it into the
1119 -- insert navigation path stack
1120 set_insert_path
1121 (g_node_usage_tab(l_usage_index).nav_node_usage_id
1122 ,g_node_usage_tab(l_usage_index).instance_id
1123 ,l_pop_to_process_activity
1124 ,l_expected_sqlform);
1125 l_found_form := true;
1126 elsif l_activity_type = 'INCORRECT_SQLFORM' then
1127 -- an incorrect sql*form was found so we must error
1128 -- select the process details that is in error
1129 get_item_act_display_names
1130 (p_instance_id => g_node_usage_tab(l_usage_index).instance_id
1131 ,p_item_type_display_name => g_item_type_display_name
1132 ,p_activity_display_name => g_activity_display_name
1133 ,p_process_display_name => g_process_display_name);
1134 -- get the TO_FORM_ACTIVITY details
1135 fnd_message.set_name(g_hr_app, 'HR_52956_WKF2TSK_WRONG_SQLFORM');
1136 fnd_message.set_token(g_process_name, g_process_display_name);
1137 fnd_message.set_token(g_item_type, g_item_type_display_name);
1138 fnd_message.set_token(g_from_form_activity, g_activity_display_name);
1139 -- get the TO_FORM_ACTIVITY details
1140 get_item_act_display_names
1141 (p_instance_id => l_pop_to_process_activity
1142 ,p_item_type_display_name => g_item_type_display_name
1143 ,p_activity_display_name => g_activity_display_name
1144 ,p_process_display_name => g_process_display_name);
1145 --
1146 fnd_message.set_token(g_to_form_activity, g_item_type_display_name);
1147 fnd_message.raise_error;
1148 elsif l_activity_type = 'PROCESS' then
1149 hr_utility.set_location(l_proc, 45);
1150 l_dummy := set_visit_activity(l_pop_to_process_activity);
1151 -- push each start activity results
1152 for csr_start in csr_process_start_transitions
1153 (l_pop_to_process_activity) loop
1154 if not exist_in_visit_list(csr_start.to_process_activity) then
1155 hr_utility.set_location(l_proc, 50);
1156 push_result_stack
1157 (csr_start.from_process_activity
1158 ,csr_start.to_process_activity
1159 ,csr_start.result_code);
1160 end if;
1161 end loop;
1162 elsif l_activity_type = g_activity_type_end then
1163 hr_utility.set_location(l_proc, 55);
1164 l_dummy := set_visit_activity(l_pop_to_process_activity);
1165 for csr_end in csr_process_end_transitions
1166 (l_pop_to_process_activity
1167 ,l_pop_result_code) loop
1168 --
1169 push_result_stack
1170 (csr_end.from_process_activity
1171 ,csr_end.to_process_activity
1172 ,csr_end.result_code);
1173 --
1174 end loop;
1175 else
1176 hr_utility.set_location(l_proc, 60);
1177 if set_visit_activity(l_pop_to_process_activity) then
1178 hr_utility.set_location(l_proc, 65);
1179 for csr_results in csr_transitions1(l_pop_to_process_activity) loop
1183 (csr_results.from_process_activity
1180 if not exist_in_visit_list(csr_results.to_process_activity) then
1181 hr_utility.set_location(l_proc, 70);
1182 push_result_stack
1184 ,csr_results.to_process_activity
1185 ,csr_results.result_code);
1186 end if;
1187 end loop;
1188 end if;
1189 end if;
1190 end loop;
1191 end if;
1192 end loop;
1193 -- determine if the form transitions back to the root form
1194 -- if it doesn't then error
1195 if not l_return_to_root then
1196 -- a corresponding transition back to the root form for the usage
1197 -- does not exist
1198 -- so we must report this
1199 get_item_act_display_names
1200 (p_instance_id => g_node_usage_tab(l_usage_index).instance_id
1201 ,p_item_type_display_name => g_item_type_display_name
1202 ,p_activity_display_name => g_activity_display_name
1203 ,p_process_display_name => g_process_display_name);
1204 --
1205 fnd_message.set_name(g_hr_app, 'HR_52959_WKF2TSK_NO_ROOT');
1206 fnd_message.set_token(g_process_name, g_process_display_name);
1207 fnd_message.set_token(g_item_type, g_item_type_display_name);
1208 fnd_message.set_token(g_from_form_activity, g_activity_display_name);
1209 get_item_act_display_names
1210 (p_instance_id => g_root_form_activity_id
1211 ,p_item_type_display_name => g_item_type_display_name
1212 ,p_activity_display_name => g_activity_display_name
1213 ,p_process_display_name => g_process_display_name);
1214 fnd_message.set_token(g_to_form_activity, g_activity_display_name);
1215 fnd_message.raise_error;
1216 end if;
1217 end loop;
1218 -- correct the parent buttons
1219 correct_parent_buttons;
1220 -- check to see if any paths exist
1221 if l_insert_path_struct.count > 0 then
1222 -- insert all the paths
1223 for i in l_insert_path_struct.first..l_insert_path_struct.last loop
1224 -- only insert paths where the boolean insert_path is true
1225 if l_insert_path_struct(i).insert_path then
1226 insert into hr_navigation_paths
1227 (nav_path_id
1228 ,from_nav_node_usage_id
1229 ,to_nav_node_usage_id
1230 ,nav_button_required
1231 ,sequence
1232 ,override_label)
1233 values
1234 (hr_navigation_paths_s.nextval
1235 ,l_insert_path_struct(i).from_nav_node_usage_id
1236 ,l_insert_path_struct(i).to_nav_node_usage_id
1237 ,l_insert_path_struct(i).nav_button_required
1238 ,l_insert_path_struct(i).sequence
1239 ,l_insert_path_struct(i).override_label
1240 );
1241
1242 select nav_path_id
1243 into l_nav_path_id
1244 from hr_navigation_paths
1245 where from_nav_node_usage_id = l_insert_path_struct(i).from_nav_node_usage_id
1246 and to_nav_node_usage_id = l_insert_path_struct(i).to_nav_node_usage_id;
1247
1248 /*-- Chages start for the bug 5702720 ---*/
1249 hr_utility.set_location(l_proc, 6253);
1250
1251 for I in csr_language_code(l_nav_path_id)
1252 loop
1253 hr_utility.set_location('In the Cursor csr_language_code= '||i.l_language_code, 6254);
1254 insert into hr_navigation_paths_tl (
1255 nav_path_id
1256 ,language
1257 ,source_lang
1258 ,override_label)
1259 select b.nav_path_id
1260 ,i.l_language_code
1261 ,userenv('LANG')
1262 ,b.override_label
1263 from hr_navigation_paths b
1264 where not exists
1265 (select '1'
1266 from hr_navigation_paths_tl t
1267 where t.nav_path_id = b.nav_path_id
1268 and t.language = i.l_language_code);
1269 end loop;
1270 hr_utility.set_location(l_proc, 6255);
1271 /*-- Chages End for the bug 5702720 ---*/
1272
1273 /* --- original code before fix 5702720 ---
1274 select L.language_code
1275 into l_current_language
1276 from FND_LANGUAGES L
1277 where L.INSTALLED_FLAG in ('I', 'B')
1278 and not exists
1279 (select NULL
1280 from HR_NAVIGATION_PATHS_TL T
1281 where T.NAV_PATH_ID = L_NAV_PATH_ID
1282 and T.LANGUAGE = L.LANGUAGE_CODE);
1283
1284 insert into hr_navigation_paths_tl (
1285 nav_path_id
1286 ,language
1287 ,source_lang
1288 ,override_label)
1289 select b.nav_path_id
1290 ,l_current_language
1291 ,userenv('LANG')
1292 ,b.override_label
1293 from hr_navigation_paths b
1294 where not exists
1295 (select '1'
1296 from hr_navigation_paths_tl t
1297 where t.nav_path_id = b.nav_path_id
1298 and t.language = l_current_language);
1299 -- End Original code before fix 5702720 -- */
1300 end if;
1301 end loop;
1302 end if;
1303 hr_utility.set_location('Leaving:'||l_proc, 75);
1304 end insert_navigation_paths;
1305 -- ----------------------------------------------------------------------------
1306 -- |-----------------< insert_navigation_node_usage >-------------------------|
1307 -- ----------------------------------------------------------------------------
1308 procedure insert_navigation_node_usage
1309 (p_nav_node_id in number
1310 ,p_instance_id in number
1311 ,p_sqlform in varchar2
1312 ,p_override_label in varchar2) is
1313 --
1314 l_top_node varchar2(1) := 'N';
1315 l_nav_node_usage_id hr_navigation_node_usages.nav_node_usage_id%type;
1316 l_proc varchar2(72) := g_package||'insert_navigation_node_usage';
1317 l_index binary_integer;
1318 --
1319 begin
1323 hr_utility.set_location(l_proc, 15);
1320 hr_utility.set_location('Entering:'||l_proc, 10);
1321 -- is the instance the root form activity?
1322 if p_instance_id = g_root_form_activity_id then
1324 l_top_node := 'Y';
1325 end if;
1326 -- select the sequence
1327 begin
1328 select hr_navigation_node_usages_s.nextval
1329 into l_nav_node_usage_id
1330 from sys.dual;
1331 exception
1332 when others then
1333 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
1334 fnd_message.set_token('PROCEDURE', l_proc);
1335 fnd_message.set_token('STEP','10');
1336 fnd_message.raise_error;
1337 end;
1338 hr_utility.set_location(l_proc, 20);
1339 -- insert the node usage
1340 insert into hr_navigation_node_usages
1341 (nav_node_usage_id,
1342 workflow_id,
1343 nav_node_id,
1344 top_node)
1345 values
1346 (l_nav_node_usage_id
1347 ,g_workflow_id
1348 ,p_nav_node_id
1349 ,l_top_node);
1350 -- insert the row into the pl/sql table
1351 l_index := g_node_usage_tab.count + 1;
1352 g_node_usage_tab(l_index).nav_node_usage_id := l_nav_node_usage_id;
1353 g_node_usage_tab(l_index).instance_id := p_instance_id;
1354 g_node_usage_tab(l_index).sqlform := p_sqlform;
1355 g_node_usage_tab(l_index).override_label := p_override_label;
1356 hr_utility.set_location('Leaving:'||l_proc, 25);
1357 --
1358 end insert_navigation_node_usage;
1359 -- ----------------------------------------------------------------------------
1360 -- |----------------------< insert_navigation_nodes >-------------------------|
1361 -- ----------------------------------------------------------------------------
1362 procedure insert_navigation_nodes
1363 (p_process_item_type in varchar2
1364 ,p_process_name in varchar2) is
1365 --
1366 cursor l_csr_processes(c_instance_id wf_process_activities.instance_id%type) is
1367 select wpa1.instance_id
1368 from wf_activities wa
1369 ,wf_process_activities wpa1
1370 ,wf_process_activities wpa2
1371 where wpa2.instance_id = c_instance_id
1372 and wpa1.process_name = wpa2.activity_name
1373 and wpa1.process_item_type = wpa2.activity_item_type
1374 and wpa1.process_version =
1375 (select max(wpa3.process_version)
1376 from wf_process_activities wpa3
1377 where wpa3.process_name = wpa1.process_name
1378 and wpa3.process_item_type = wpa1.process_item_type)
1379 and wa.name = wpa1.activity_name
1380 and wa.item_type = wpa1.activity_item_type
1381 and wa.type = g_activity_type_process
1382 and wa.end_date is null;
1383 --
1384 cursor l_csr_tf_form_activity
1385 (c_instance_id wf_process_activities.instance_id%type) is
1386 select wpa1.instance_id
1387 from wf_activities wa
1388 ,wf_process_activities wpa1
1389 ,wf_process_activities wpa2
1390 ,wf_activity_attr_values waav
1391 ,wf_activity_attributes waa
1392 where wpa2.instance_id = c_instance_id
1393 and wpa1.process_name = wpa2.activity_name
1394 and wpa1.process_item_type = wpa2.activity_item_type
1395 and wpa1.process_version =
1396 (select max(wpa3.process_version)
1397 from wf_process_activities wpa3
1398 where wpa3.process_name = wpa1.process_name
1399 and wpa3.process_item_type = wpa1.process_item_type)
1400 and wa.name = wpa1.activity_name
1401 and wa.item_type = wpa1.activity_item_type
1402 and wa.type = g_activity_type_function
1403 and wa.end_date is null
1404 and waav.process_activity_id = wpa1.instance_id
1405 and waav.name = waa.name
1406 and waav.text_value = g_hrms_sqlform
1407 and waa.activity_item_type = wa.item_type
1408 and waa.activity_name = wa.name
1409 and waa.activity_version = wa.version
1410 and waa.name = g_taskflow_activity_type;
1411 --
1412 cursor l_csr_tf_form_attributes
1413 (c_instance_id wf_process_activities.instance_id%type) is
1414 select waa.name
1415 ,waav.text_value
1416 from wf_activities wa
1417 ,wf_process_activities wpa1
1418 ,wf_activity_attr_values waav
1419 ,wf_activity_attributes waa
1420 where wpa1.instance_id = c_instance_id
1421 and wa.name = wpa1.activity_name
1422 and wa.item_type = wpa1.activity_item_type
1423 and wa.type = g_activity_type_function
1424 and wa.end_date is null
1425 and waav.process_activity_id = wpa1.instance_id
1426 and waav.name = waa.name
1427 and waav.name in ('TASKFLOW_ACTIVITY_NAME'
1428 ,'CUSTOMIZATION_NAME'
1429 ,'BUTTON_TEXT'
1430 ,'HRMS_FORM_BLOCK_NAME')
1431 and waa.activity_item_type = wa.item_type
1432 and waa.activity_name = wa.name
1433 and waa.activity_version = wa.version
1434 and exists
1435 (select 1
1436 from wf_activity_attr_values waav1
1437 where waav1.name = g_taskflow_activity_type
1438 and waav1.text_value = g_hrms_sqlform
1439 and waav1.process_activity_id = waav.process_activity_id);
1440 --
1441 cursor l_csr_nav_unit_id(c_form_name varchar2, c_block_name varchar2) is
1442 select hnu.nav_unit_id
1443 ,hnul.default_label
1444 from hr_navigation_units hnu,
1445 hr_navigation_units_tl hnul
1446 where hnu.form_name = c_form_name
1450 --
1447 and nvl(hnu.block_name, hr_api.g_varchar2) = nvl(c_block_name, hr_api.g_varchar2)
1448 and hnu.nav_unit_id = hnul.nav_unit_id
1449 and hnul.language=userenv('LANG');
1451 cursor l_csr_cust_restrict_id(c_form_name varchar2, c_customization_name varchar2) is
1452 select pcr.customized_restriction_id
1453 from pay_customized_restrictions pcr
1454 where pcr.form_name = c_form_name
1455 and pcr.enabled_flag = 'Y'
1456 and nvl(pcr.business_group_id, nvl(g_business_group_id, hr_api.g_number)) =
1457 nvl(g_business_group_id, hr_api.g_number)
1458 and nvl(pcr.legislation_code, nvl(g_legislation_code, hr_api.g_varchar2)) =
1459 nvl(g_legislation_code, hr_api.g_varchar2)
1460 and nvl(pcr.legislation_subgroup, nvl(g_legislation_subgroup, hr_api.g_varchar2)) =
1461 nvl(g_legislation_subgroup, hr_api.g_varchar2)
1462 and pcr.application_id between 800 and 899
1463 and pcr.name = c_customization_name;
1464 --
1465 cursor l_csr_nav_node_id(c_nav_unit_id number, c_customized_restriction_id number) is
1466 select hnn.nav_node_id
1467 ,hnn.name
1468 from hr_navigation_nodes hnn
1469 where hnn.nav_unit_id = c_nav_unit_id
1470 and nvl(hnn.customized_restriction_id, hr_api.g_number) =
1471 nvl(c_customized_restriction_id, hr_api.g_number);
1472 --
1473 l_taskflow_actvity_name wf_activity_attr_values.text_value%type;
1474 l_customization_name wf_activity_attr_values.text_value%type;
1475 l_taskflow_block_name wf_activity_attr_values.text_value%type;
1476 l_nav_unit_id hr_navigation_units.nav_unit_id%type;
1477 l_customized_restriction_id pay_customized_restrictions.customized_restriction_id%type;
1478 l_navigation_node_name hr_navigation_nodes.name%type;
1479 l_nextval number;
1480 l_nav_node_id hr_navigation_nodes.nav_node_id%type;
1481 l_root_nav_node_id hr_navigation_nodes.nav_node_id%type;
1482 l_proc varchar2(72) := g_package||'insert_navigation_nodes';
1483 l_override_label hr_navigation_paths.override_label%type;
1484 l_default_label hr_navigation_units.default_label%type;
1485 l_found_nav_node boolean;
1486 --
1487 type l_process_stack_tab is table of
1488 wf_process_activities.instance_id%type
1489 index by binary_integer;
1490 l_process_stack_struct l_process_stack_tab;
1491 l_current_working_process_id wf_process_activities.instance_id%type;
1492 --
1493 begin
1494 hr_utility.set_location('Entering:'||l_proc, 10);
1495 --
1496 -- we need to select all the form activities within a process where the
1497 -- process is located within the specified root process.
1498 -- originally this was perceived as being a network/hierachy query but
1499 -- due to performance this mechasim has been abandoned (ideally this
1500 -- would the best solution, however, a new index would be required
1501 -- from workflow on the table wf_process_activities). so due to
1502 -- time constraints, a work around has been put in place which
1503 -- describes the processing logic in detail.
1504 --
1505 -- a stack is used to maintain a 'working' list of processes. the intial
1506 -- push value of the stack is the ROOT process itself. as a process
1507 -- is popped off the stack this process becomes the current working
1508 -- process. for the current working process a query selects any sub
1509 -- processes for the current process and pushes them onto the stack.
1510 -- next, all taskflow form activities are identified for the current
1511 -- working process and subsequent processing occurs. this process
1512 -- repeats itself until the stack is empty.
1513 --
1514 -- pseudo logic:
1515 --
1516 -- clear the stack
1517 -- push the root process onto the stack
1518 -- while the stack is not empty loop
1519 -- pop the stack into the current working process identifier
1520 -- select all sub processes for the current working process and push
1521 -- onto the stack
1522 -- for the current working process select all taskflow form actvities
1523 -- perform further processing
1524 -- end loop
1525 -- end loop
1526 --
1527 --
1528 l_process_stack_struct.delete; -- clear the process stack
1529 -- push the root process on the stack
1530 l_process_stack_struct(1) := g_root_process_id;
1531 while l_process_stack_struct.count > 0 loop
1532 -- pop the last element of the stack
1533 l_current_working_process_id :=
1534 l_process_stack_struct(l_process_stack_struct.last);
1535 l_process_stack_struct.delete(l_process_stack_struct.last);
1536 -- get all the subprocesses for the current working process
1537 for l_csr_sub_processes in
1538 l_csr_processes(l_current_working_process_id) loop
1539 -- push the selected sub process onto the stack
1540 l_process_stack_struct(l_process_stack_struct.count + 1) :=
1541 l_csr_sub_processes.instance_id;
1542 end loop;
1543 -- get all the taskflow form activities for the
1544 -- current working process and select the process activity attributes
1545 for l_csr_tf_function_activities in
1546 l_csr_tf_form_activity(l_current_working_process_id) loop
1547 -- clear the activity attribute values
1548 l_taskflow_actvity_name := null;
1549 l_customization_name := null;
1550 l_taskflow_block_name := null;
1551 l_nav_unit_id := null;
1552 l_customized_restriction_id := null;
1553 l_navigation_node_name := null;
1554 l_nav_node_id := null;
1555 for l_csr_attrs in
1556 l_csr_tf_form_attributes(l_csr_tf_function_activities.instance_id) loop
1557 -- we are interested in 4 attributes; TASKFLOW_ACTIVITY_NAME,
1558 -- CUSTOMIZATION_NAME, HRMS_FORM_BLOCK_NAME and BUTTON_TEXT
1559 if l_csr_attrs.name = 'TASKFLOW_ACTIVITY_NAME' then
1560 hr_utility.set_location(l_proc, 20);
1564 l_taskflow_block_name := upper(l_csr_attrs.text_value);
1561 l_taskflow_actvity_name := l_csr_attrs.text_value;
1562 elsif l_csr_attrs.name = 'HRMS_FORM_BLOCK_NAME' then
1563 hr_utility.set_location(l_proc, 25);
1565 elsif l_csr_attrs.name = 'BUTTON_TEXT' then
1566 l_override_label := substr(l_csr_attrs.text_value,1,40);
1567 else
1568 hr_utility.set_location(l_proc, 30);
1569 -- the attribute must be CUSTOMIZATION_NAME
1570 l_customization_name := l_csr_attrs.text_value;
1571 end if;
1572 end loop;
1573 hr_utility.set_location(l_proc, 40);
1574 -- ensure that a corresponding row exists within the HR_NAVIGATION_UNITS
1575 -- table.
1576 open l_csr_nav_unit_id(l_taskflow_actvity_name, l_taskflow_block_name);
1577 fetch l_csr_nav_unit_id into l_nav_unit_id, l_default_label;
1578 if l_csr_nav_unit_id%notfound then
1579 close l_csr_nav_unit_id;
1580 -- a corresponding navigation unit does not exist
1581 -- this is a serious error which we must raise and report
1582 get_item_act_display_names
1583 (p_instance_id => l_csr_tf_function_activities.instance_id
1584 ,p_item_type_display_name => g_item_type_display_name
1585 ,p_activity_display_name => g_activity_display_name
1586 ,p_process_display_name => g_process_display_name);
1587 --
1588 fnd_message.set_name(g_hr_app, 'HR_52957_WKF2TSK_INC_SQLFORM');
1589 fnd_message.set_token('ACTIVITY_NAME', g_activity_display_name);
1590 fnd_message.set_token(g_process_name, g_process_display_name);
1591 fnd_message.set_token(g_item_type, g_item_type_display_name);
1592 fnd_message.raise_error;
1593 end if;
1594 close l_csr_nav_unit_id;
1595 -- the activity was a TF function activity so we must now determine
1596 -- if the customization is valid
1597 if l_customization_name is not null then
1598 hr_utility.set_location(l_proc, 45);
1599 open l_csr_cust_restrict_id(l_taskflow_actvity_name, l_customization_name);
1600 fetch l_csr_cust_restrict_id into l_customized_restriction_id;
1601 if l_csr_cust_restrict_id%notfound then
1602 -- the customized name specified was not found so we must provide a warning
1603 -- [warning]
1604 l_customized_restriction_id := null;
1605 end if;
1606 close l_csr_cust_restrict_id;
1607 end if;
1608 hr_utility.set_location(l_proc, 50);
1609 -- set the navigation node name
1610 l_navigation_node_name := l_taskflow_actvity_name;
1611 --
1612 l_found_nav_node := false;
1613 for csr_nodes in l_csr_nav_node_id(l_nav_unit_id, l_customized_restriction_id) loop
1614 l_found_nav_node := true;
1615 -- set the local nav node id and navigation node name
1616 l_nav_node_id := csr_nodes.nav_node_id;
1617 l_navigation_node_name := csr_nodes.name;
1618 -- check to see if the root form activity is being processed
1619 if g_root_form_activity_id = l_csr_tf_function_activities.instance_id then
1620 -- set the root nav node
1621 l_root_nav_node_id := csr_nodes.nav_node_id;
1622 end if;
1623 -- check to see if we are using the root node but not in the context of
1624 -- the root activity
1625 if csr_nodes.nav_node_id = l_root_nav_node_id and
1626 g_root_form_activity_id <> l_csr_tf_function_activities.instance_id then
1627 -- we cannot use this navigation node as it is already being used
1628 -- so loop again
1629 l_found_nav_node := false;
1630 end if;
1631 -- exit the loop if row found
1632 if l_found_nav_node then
1633 exit;
1634 end if;
1635 end loop;
1636 -- was a nav node found?
1637 if not l_found_nav_node then
1638 hr_utility.set_location(l_proc, 55);
1639 -- derive a new name
1640 begin
1641 select hr_navigation_nodes_s.nextval
1642 into l_nextval
1643 from sys.dual;
1644 exception
1645 when others then
1646 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
1647 fnd_message.set_token('PROCEDURE', l_proc);
1648 fnd_message.set_token('STEP','10');
1649 fnd_message.raise_error;
1650 end;
1651 hr_utility.set_location(l_proc, 60);
1652 l_navigation_node_name := l_taskflow_actvity_name||l_nextval;
1653 -- the navigation node does not exist so we need to insert it
1654 insert into hr_navigation_nodes
1655 (nav_node_id,
1656 nav_unit_id,
1657 name,
1658 customized_restriction_id)
1659 values
1660 (l_nextval
1661 ,l_nav_unit_id
1662 ,l_navigation_node_name
1663 ,l_customized_restriction_id);
1664 end if;
1665 hr_utility.set_location(l_proc, 65);
1666 -- insert a node usage
1667 insert_navigation_node_usage
1668 (p_nav_node_id => l_nav_node_id
1669 ,p_instance_id => l_csr_tf_function_activities.instance_id
1670 ,p_sqlform => l_taskflow_actvity_name
1671 ,p_override_label => nvl(l_override_label, l_default_label));
1672 end loop;
1673 end loop;
1674 hr_utility.set_location('Leaving:'||l_proc, 70);
1675 end insert_navigation_nodes;
1676 -- ----------------------------------------------------------------------------
1677 -- |----------------------------< transfer_workflow >-------------------------|
1678 -- ----------------------------------------------------------------------------
1682 ,p_business_group_id in number default null
1679 procedure transfer_workflow
1680 (p_process_item_type in varchar2
1681 ,p_root_process_name in varchar2 default null
1683 ,p_legislation_code in varchar2 default null
1684 ,p_legislation_subgroup in varchar2 default null) is
1685 --
1686 l_proc varchar2(72) := g_package||'transfer_workflow';
1687 l_item_type wf_items.item_type%type := upper(p_process_item_type);
1688 l_process_name wf_items.root_activity%type := upper(p_root_process_name);
1689 l_found boolean := false;
1690 --
1691 begin
1692 hr_utility.set_location('Entering:'||l_proc, 10);
1693 -- ensure that the item type has been set
1694 hr_api.mandatory_arg_error
1695 (p_api_name => l_proc
1696 ,p_argument => 'process_item_type'
1697 ,p_argument_value => p_process_item_type);
1698 -- set the language code
1699 begin
1700 select userenv('LANG')
1701 into g_language
1702 from sys.dual;
1703 exception
1704 when others then
1705 -- error lang does not exist
1706 fnd_message.set_name(g_hr_app, 'HR_6153_ALL_PROCEDURE_FAIL');
1707 fnd_message.set_token('PROCEDURE', l_proc);
1708 fnd_message.set_token('STEP','10');
1709 fnd_message.raise_error;
1710 end;
1711 -- set the business legislation
1712 set_business_legislation
1713 (p_business_group_id => p_business_group_id
1714 ,p_legislation_code => p_legislation_code
1715 ,p_legislation_subgroup => p_legislation_subgroup);
1716 -- reset the number of converted processes
1717 g_converted_processes := 0;
1718 --
1719 for l_csr_root in g_csr_root_runnable_process
1720 (l_item_type, l_process_name) loop
1721 l_found := true;
1722 -- clear the usages table
1723 g_node_usage_tab.delete;
1724 hr_utility.set_location(l_proc, 20);
1725 -- check and set the root process specified is valid
1726 set_root_process_activity_id
1727 (p_process_item_type => l_item_type
1728 ,p_root_process_name => l_csr_root.activity_name);
1729 -- set the root form activity
1730 set_root_form_activity_id
1731 (p_process_item_type => l_item_type
1732 ,p_root_process_name => l_csr_root.activity_name);
1733 hr_utility.set_location(l_proc, 25);
1734 -- insert the workflow
1735 insert_workflow
1736 (p_process_name => l_csr_root.activity_name);
1737 hr_utility.set_location(l_proc, 30);
1738 -- insert the navigation nodes
1739 insert_navigation_nodes
1740 (p_process_item_type => l_item_type
1741 ,p_process_name => l_csr_root.activity_name);
1742 hr_utility.set_location(l_proc, 35);
1743 -- insert the navigation paths
1744 insert_navigation_paths;
1745 -- increment the converted processes counter
1746 g_converted_processes := g_converted_processes + 1;
1747 end loop;
1748 -- check to see if process name was specified and if it was found
1749 if NOT l_found and p_root_process_name is not null then
1750 -- error the root process specified does not exist
1751 fnd_message.set_name(g_hr_app, 'HR_52958_WKF2TSK_INC_PROCESS');
1752 fnd_message.set_token(g_item_type, g_item_type_display_name);
1753 fnd_message.set_token(g_process_name, p_root_process_name);
1754 fnd_message.raise_error;
1755 end if;
1756 hr_utility.set_location('Leaving:'||l_proc, 40);
1757 end transfer_workflow;
1758 -- ----------------------------------------------------------------------------
1759 -- |--------------------------< call_taskflow_form >--------------------------|
1760 -- ----------------------------------------------------------------------------
1761 procedure call_taskflow_form
1762 (itemtype in varchar2
1763 ,itemkey in varchar2
1764 ,actid in number
1765 ,funmode in varchar2
1766 ,result out nocopy varchar2) is
1767 --
1768 cursor l_csr_tf_form is
1769 select 1
1770 from wf_process_activities wpa
1771 ,wf_activity_attr_values waav
1772 where wpa.instance_id = actid
1773 and waav.process_activity_id = wpa.instance_id
1774 and waav.name = g_taskflow_activity_type
1775 and waav.text_value = g_hrms_sqlform;
1776 --
1777 l_proc varchar2(72) := g_package||'call_taskflow_form';
1778 l_dummy number;
1779 --
1780 begin
1781 hr_utility.set_location('Entering:'||l_proc, 10);
1782 -- check the workflow funmode value
1783 if funmode = 'RUN' then
1784 hr_utility.set_location(l_proc, 20);
1785 -- workflow is RUNing this procedure so ensure that the current
1786 -- process activity is defined as a taskflow SQL*Form and
1787 -- return a NOTIFIED value
1788 open l_csr_tf_form;
1789 fetch l_csr_tf_form into l_dummy;
1790 if l_csr_tf_form%notfound then
1791 hr_utility.set_location(l_proc, 30);
1792 close l_csr_tf_form;
1793 -- raise the error and let the outer exception handle the error
1794 raise no_data_found;
1795 end if;
1796 close l_csr_tf_form;
1797 --
1798 result := 'NOTIFIED:';
1799 hr_utility.set_location(l_proc, 40);
1800 elsif funmode = 'CANCEL' then
1801 hr_utility.set_location(l_proc, 50);
1802 -- workflow is calling in cancel mode (performing a loop reset) so ignore
1803 null;
1804 end if;
1805 hr_utility.set_location('Leaving:'||l_proc, 60);
1806 exception
1807 when others then
1808 -- because we are being directly called from workflow return an ERROR
1809 -- result
1810 result := 'ERROR:';
1811 hr_utility.set_location('Leaving:'||l_proc, 70);
1812 end call_taskflow_form;
1813 -- ----------------------------------------------------------------------------
1814 -- |----------------------------< chk_process_name >--------------------------|
1815 -- ----------------------------------------------------------------------------
1816 function chk_process_name
1817 (p_item_type in varchar2
1818 ,p_process_name in varchar2)
1819 return boolean is
1820 --
1821 l_proc varchar2(72) := g_package||'chk_process_name';
1822 l_dummy wf_process_activities.activity_name%type;
1823 l_return boolean;
1824 --
1825 begin
1826 hr_utility.set_location('Entering:'||l_proc, 10);
1827 -- ensure that the item type and process names are specified
1828 hr_api.mandatory_arg_error
1829 (p_api_name => l_proc
1830 ,p_argument => 'item_type'
1831 ,p_argument_value => p_item_type);
1832 hr_api.mandatory_arg_error
1833 (p_api_name => l_proc
1834 ,p_argument => 'process_name'
1835 ,p_argument_value => p_process_name);
1836 -- open the cursor
1837 open g_csr_root_runnable_process(upper(p_item_type), upper(p_process_name));
1838 fetch g_csr_root_runnable_process into l_dummy;
1839 if g_csr_root_runnable_process%notfound then
1840 hr_utility.set_location(l_proc, 20);
1841 l_return := false;
1842 else
1843 hr_utility.set_location(l_proc, 30);
1844 l_return := true;
1845 end if;
1846 close g_csr_root_runnable_process;
1847 hr_utility.set_location('Leaving:'||l_proc, 40);
1848 return(l_return);
1849 end chk_process_name;
1850 -- ----------------------------------------------------------------------------
1851 -- |---------------------< get_converted_processes >--------------------------|
1852 -- ----------------------------------------------------------------------------
1853 function get_converted_processes return number is
1854 begin
1855 return(g_converted_processes);
1856 end get_converted_processes;
1857 --
1858 end hr_taskflow_workflow;
[Home] [Help]