1 PACKAGE BODY hr_workflow_service as
2 /* $Header: hrwfserv.pkb 120.7.12010000.2 2008/08/06 08:50:55 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 -- ---------------------------------------------------------------------------
7 -- private package global declarations
8 -- ---------------------------------------------------------------------------
9 g_package VARCHAR2(31) := 'hr_workflow_service.';
10 --
11 -- Private Package Procedures/Functions
12 --
13 -- ---------------------------------------------------------------------------
14 -- Private Package Procedures/Functions Declarations
15 -- ---------------------------------------------------------------------------
16 --
17 -- ----------------------------------------------------------------------------
18 -- |-------------------------< item_attribute_exists >------------------------|
19 -- ----------------------------------------------------------------------------
20 function item_attribute_exists
21 (p_item_type in wf_items.item_type%type
22 ,p_item_key in wf_items.item_key%type
23 ,p_name in wf_item_attribute_values.name%type)
24 return boolean is
25 -- --------------------------------------------------------------------------
26 -- declare local variables
27 -- --------------------------------------------------------------------------
28 l_dummy number(1);
29 l_return boolean := TRUE;
30 -- cursor determines if an attribute exists
31 cursor csr_wiav is
32 select 1
33 from wf_item_attribute_values wiav
34 where wiav.item_type = p_item_type
35 and wiav.item_key = p_item_key
36 and wiav.name = p_name;
37 --
38 begin
39 -- open the cursor
40 open csr_wiav;
41 fetch csr_wiav into l_dummy;
42 if csr_wiav%notfound then
43 -- item attribute does not exist so return false
44 l_return := FALSE;
45 end if;
46 close csr_wiav;
47 return(l_return);
48 end item_attribute_exists;
49 --
50 -- ----------------------------------------------------------------------------
51 -- |--------------------< check_usernm_exists_subj_aprv >---------------------|
52 -- ----------------------------------------------------------------------------
53 -- Bug #788954 Fix: This procedure is used in workflow Approved Process. This
54 -- function is invoked to check that if the subject-of-approval
55 -- person has an apps login username. If not, then workflow
56 -- will not send out a notification to the subject person.
57 -- ----------------------------------------------------------------------------
58 PROCEDURE check_usernm_exists_subj_aprv
59 (itemtype IN VARCHAR2
60 ,itemkey IN VARCHAR2
61 ,actid IN NUMBER
62 ,funcmode IN VARCHAR2
63 ,resultout OUT NOCOPY VARCHAR2)
64 IS
65 --
66 l_current_person_username VARCHAR2(2000) default null;
67 --
68 BEGIN
69 IF funcmode = 'RUN' THEN
70 l_current_person_username := wf_engine.GetItemAttrText
71 (itemtype => itemtype
72 ,itemkey => itemkey
73 ,aname => 'CURRENT_PERSON_USERNAME');
74 --
75 ELSIF funcmode = 'CANCEL' THEN
76 null;
77 END IF;
78 --
79 IF l_current_person_username is null THEN
80 resultout := 'COMPLETE:'|| 'N'; --no display name found for current person
81 ELSE
82 resultout := 'COMPLETE:'|| 'Y'; -- display name found for current person
83 END IF;
84 --
85 EXCEPTION
86 WHEN OTHERS THEN
87 WF_CORE.CONTEXT('HR_UTILITY_WORKFLOW'
88 ,'CHECK_USERNM_EXISTS_SUBJ_APRV'
89 ,itemtype
90 ,itemkey
91 ,to_char(actid)
92 ,funcmode);
93 RAISE;
94 END check_usernm_exists_subj_aprv;
95 --
96 --
97 -- ----------------------------------------------------------------------------
98 -- |------------------------< get_activity_name >-----------------------------|
99 -- ----------------------------------------------------------------------------
100 procedure get_activity_name
101 (p_item_type in wf_items.item_type%type
102 ,p_item_key in wf_items.item_key%type
103 ,p_actid in number
104 ,p_activity_name out nocopy varchar2
105 ,p_activity_display_name out nocopy varchar2) is
106 --
107 cursor l_csr is
108 --BUG 3636429
109 SELECT distinct activity_name,activity_display_name
110 FROM
111 (
112 SELECT a.name activity_name,
113 a.display_name activity_display_name
114 FROM wf_activities_vl a
115 ,wf_item_activity_statuses ias
116 ,wf_process_activities pa
117 WHERE ias.item_type = p_item_type
118 AND ias.item_key = p_item_key
119 AND ias.process_activity = p_actid
120 AND ias.process_activity = pa.instance_id
121 AND pa.activity_name = a.name
122 AND pa.activity_item_type = a.item_type
123 UNION ALL
124 SELECT a.name activity_name,
125 a.display_name activity_display_name
126 FROM wf_activities_vl a
127 ,wf_item_activity_statuses_h iash
128 ,wf_process_activities pa
129 WHERE iash.item_type = p_item_type
130 AND iash.item_key = p_item_key
131 AND iash.process_activity = p_actid
132 AND iash.process_activity = pa.instance_id
133 AND pa.activity_name = a.name
134 AND pa.activity_item_type = a.item_type
135 );
136 --
137 begin
138 open l_csr;
139 fetch l_csr into p_activity_name, p_activity_display_name;
140 if l_csr%notfound then
141 p_activity_name := null;
142 p_activity_display_name := null;
143 end if;
144 close l_csr;
145 end get_activity_name;
146 -- ----------------------------------------------------------------------------
147 -- |------------------------< create_hr_directory_services >------------------|
148 -- ----------------------------------------------------------------------------
149 procedure create_hr_directory_services
150 (p_item_type in wf_items.item_type%type
151 ,p_item_key in wf_items.item_key%type
152 ,p_service_name in varchar2
153 ,p_service_person_id in per_all_people_f.person_id%type) is
154 --
155 l_item_type_attribute_name varchar2(30);
156 type l_suffix_tab is table of varchar2(30) index by binary_integer;
157 l_suffix l_suffix_tab;
158 l_username wf_users.name%type;
159 l_display_name wf_users.display_name%type;
160 --
161 begin
162 if p_service_person_id is not null then
163 l_suffix(1) := 'ID';
164 l_suffix(2) := 'USERNAME';
165 l_suffix(3) := 'DISPLAY_NAME';
166 -- get the USERNAME and DISPLAY_NAME from workflow
167 begin
168 wf_directory.getusername
169 (p_orig_system => 'PER'
170 ,p_orig_system_id => p_service_person_id
171 ,p_name => l_username
172 ,p_display_name => l_display_name);
173 exception
174 when others then
175 null;
176 end;
177 for i in 1..3 loop
178 l_item_type_attribute_name := p_service_name||'_'||l_suffix(i);
179 -- check to see if the item attribute has been created
180 if not item_attribute_exists
181 (p_item_type => p_item_type
182 ,p_item_key => p_item_key
183 ,p_name => l_item_type_attribute_name) then
184 -- the item attribute does not exist so create it
185 wf_engine.additemattr
186 (itemtype => p_item_type
187 ,itemkey => p_item_key
188 ,aname => l_item_type_attribute_name);
189 end if;
190 -- set the item attribue value
191 if i = 1 then
192 -- set the ID value
193 wf_engine.setitemattrnumber
194 (itemtype => p_item_type
195 ,itemkey => p_item_key
196 ,aname => l_item_type_attribute_name
197 ,avalue => p_service_person_id);
198 elsif i = 2 then
199 -- set the USERNAME value
200 wf_engine.setitemattrtext
201 (itemtype => p_item_type
202 ,itemkey => p_item_key
203 ,aname => l_item_type_attribute_name
204 ,avalue => l_username);
205 else
206 -- set the DISPLAY_NAME value
207 wf_engine.setitemattrtext
208 (itemtype => p_item_type
209 ,itemkey => p_item_key
210 ,aname => l_item_type_attribute_name
211 ,avalue => l_display_name);
212 end if;
213 end loop;
214 end if;
215 end create_hr_directory_services;
216 -- ----------------------------------------------------------------------------
217 -- |-------------------------< get_item_attr_expanded_info >------------------|
218 -- ----------------------------------------------------------------------------
219 procedure get_item_attr_expanded_info
220 (p_item_type in wf_items.item_type%type
221 ,p_item_key in wf_items.item_key%type
222 ,p_name in wf_item_attributes.name%type
223 ,p_exists out nocopy boolean
224 ,p_subtype out nocopy wf_item_attributes.subtype%type
225 ,p_type out nocopy wf_item_attributes.type%type
226 ,p_format out nocopy wf_item_attributes.format%type
227 ,p_date_value out nocopy wf_item_attribute_values.date_value%type
228 ,p_number_value out nocopy wf_item_attribute_values.number_value%type
229 ,p_text_value out nocopy wf_item_attribute_values.text_value%type) is
230 -- --------------------------------------------------------------------------
231 -- declare local variables
232 -- --------------------------------------------------------------------------
233 l_type wf_item_attributes.type%type;
234 l_name wf_item_attributes.name%type := upper(p_name);
235 --
236 begin
237 -- initialise the OUT parameters
238 p_exists := true;
239 p_subtype := null;
240 p_type := null;
241 p_format := null;
242 p_date_value := null;
243 p_number_value := null;
244 p_text_value := null;
245 -- get the item attribute information
246 wf_engine.GetItemAttrInfo
247 (itemtype => p_item_type
248 ,aname => l_name
249 ,atype => l_type
250 ,subtype => p_subtype
251 ,format => p_format);
252 --
253 p_type := l_type;
254 -- branch on the type
255 if l_type = 'NUMBER' then
256 p_number_value :=
257 wf_engine.GetItemAttrNumber
258 (itemtype => p_item_type
259 ,itemkey => p_item_key
260 ,aname => l_name);
261 elsif l_type = 'DATE' then
262 p_date_value :=
263 wf_engine.GetItemAttrDate
264 (itemtype => p_item_type
265 ,itemkey => p_item_key
266 ,aname => l_name);
267 else
268 p_text_value :=
269 wf_engine.GetItemAttrText
270 (itemtype => p_item_type
271 ,itemkey => p_item_key
272 ,aname => l_name);
273 end if;
274 exception
275 -- an error has occurred because the item attribute does not exists
276 -- reset all of the OUT parameters ensuring
277 when others then
278 p_exists := false;
279 p_subtype := null;
280 p_type := null;
281 p_format := null;
282 p_date_value := null;
283 p_number_value := null;
284 p_text_value := null;
285 end get_item_attr_expanded_info;
286 -- ----------------------------------------------------------------------------
287 -- |-------------------------< get_act_attr_expanded_info >------------------|
288 -- ----------------------------------------------------------------------------
289 procedure get_act_attr_expanded_info
290 (p_item_type in wf_items.item_type%type
291 ,p_item_key in wf_items.item_key%type
292 ,p_actid in wf_activity_attr_values.process_activity_id%type
293 ,p_name in wf_activity_attributes.name%type
294 ,p_exists out nocopy boolean
295 ,p_subtype out nocopy wf_activity_attributes.subtype%type
296 ,p_type out nocopy wf_activity_attributes.type%type
297 ,p_format out nocopy wf_activity_attributes.format%type
298 ,p_date_value out nocopy wf_activity_attr_values.date_value%type
299 ,p_number_value out nocopy wf_activity_attr_values.number_value%type
300 ,p_text_value out nocopy wf_activity_attr_values.text_value%type) is
301 -- --------------------------------------------------------------------------
302 -- declare local variables
303 -- --------------------------------------------------------------------------
304 l_type wf_activity_attributes.type%type;
305 l_name wf_activity_attributes.name%type := upper(p_name);
306 --
307 begin
308 -- initialise the OUT parameters
309 p_exists := true;
310 p_subtype := null;
311 p_type := null;
312 p_format := null;
313 p_date_value := null;
314 p_number_value := null;
315 p_text_value := null;
316 -- get the item attribute information
317 wf_engine.GetActivityAttrInfo
318 (itemtype => p_item_type
319 ,itemkey => p_item_key
320 ,actid => p_actid
321 ,aname => l_name
322 ,atype => l_type
323 ,subtype => p_subtype
324 ,format => p_format);
325 --
326 p_type := l_type;
327 -- branch on the type
328 if l_type = 'NUMBER' then
329 p_number_value :=
330 wf_engine.GetActivityAttrNumber
331 (itemtype => p_item_type
332 ,itemkey => p_item_key
333 ,actid => p_actid
334 ,aname => l_name);
335 elsif l_type = 'DATE' then
336 p_date_value :=
337 wf_engine.GetActivityAttrDate
338 (itemtype => p_item_type
339 ,itemkey => p_item_key
340 ,actid => p_actid
341 ,aname => l_name);
342 else
343 p_text_value :=
344 wf_engine.GetActivityAttrText
345 (itemtype => p_item_type
346 ,itemkey => p_item_key
347 ,actid => p_actid
348 ,aname => l_name);
349 end if;
350 exception
351 -- an error has occurred because the item attribute does not exists
352 -- reset all of the OUT parameters ensuring
353 when others then
354 p_exists := false;
355 p_subtype := null;
356 p_type := null;
357 p_format := null;
358 p_date_value := null;
359 p_number_value := null;
360 p_text_value := null;
361 end get_act_attr_expanded_info;
362 -- ----------------------------------------------------------------------------
363 -- |-------------------------< check_activity_type_attrs >--------------------|
364 -- ----------------------------------------------------------------------------
365 procedure check_activity_type_attrs
366 (p_item_type in wf_items.item_type%type
367 ,p_item_key in wf_items.item_key%type
368 ,p_actid in wf_activity_attr_values.process_activity_id%type) is
369 --
370 l_exists boolean;
371 l_subtype wf_activity_attributes.subtype%type;
372 l_type wf_activity_attributes.type%type;
373 l_format wf_activity_attributes.format%type;
374 l_date_value wf_activity_attr_values.date_value%type;
375 l_number_value wf_activity_attr_values.number_value%type;
376 l_text_value wf_activity_attr_values.text_value%type;
377 l_activity_name wf_item_activity_statuses_v.activity_name%type;
378 l_activity_display_name wf_item_activity_statuses_v.activity_display_name%type;
379 --
380 begin
381 -- check to see if the HR_ACTIVITY_TYPE activity attribute exists
382 get_act_attr_expanded_info
383 (p_item_type => p_item_type
384 ,p_item_key => p_item_key
385 ,p_actid => p_actid
386 ,p_name => g_hr_activity_type
387 ,p_exists => l_exists
388 ,p_subtype => l_subtype
389 ,p_type => l_type
390 ,p_format => l_format
391 ,p_date_value => l_date_value
392 ,p_number_value => l_number_value
393 ,p_text_value => l_text_value);
394 --
395 if NOT l_exists then
396 -- supply HR error message, HR_ACTIVITY_TYPE does not exist
397 hr_utility.set_message(800,'HR_52468_NO_ACTIVITY_ATTR');
398 hr_utility.set_message_token('ACTIVITY_ATTRIBUTE', g_hr_activity_type);
399 -- get the activity name
400 get_activity_name
401 (p_item_type => p_item_type
402 ,p_item_key => p_item_key
403 ,p_actid => p_actid
404 ,p_activity_name => l_activity_name
405 ,p_activity_display_name => l_activity_display_name);
406 hr_utility.set_message_token('ACTIVITY_NAME', l_activity_display_name);
407 hr_utility.set_message_token('ITEM_TYPE', p_item_type);
408 hr_utility.raise_error;
409 end if;
410 -- check to see if the HR_ACTIVITY_TYPE is NULL
411 -- note: we are assuming that the value must be of text type
412 if l_text_value is NULL then
413 -- supply HR error message, HR_ACTIVITY_TYPE cannot be NULL
414 hr_utility.set_message(800,'HR_52469_ACTIVITY_ATTR_NULL');
415 hr_utility.set_message_token('ACTIVITY_ATTRIBUTE', g_hr_activity_type);
416 -- get the activity name
417 get_activity_name
418 (p_item_type => p_item_type
419 ,p_item_key => p_item_key
420 ,p_actid => p_actid
421 ,p_activity_name => l_activity_name
422 ,p_activity_display_name => l_activity_display_name);
423 hr_utility.set_message_token('ACTIVITY_NAME', l_activity_display_name);
424 hr_utility.set_message_token('ITEM_TYPE', p_item_type);
425 hr_utility.raise_error;
426 end if;
427 -- check to see if the HR_ACTIVITY_TYPE_VALUE activity attribute exists
428 get_act_attr_expanded_info
429 (p_item_type => p_item_type
430 ,p_item_key => p_item_key
431 ,p_actid => p_actid
432 ,p_name => g_hr_activity_type_value
433 ,p_exists => l_exists
434 ,p_subtype => l_subtype
435 ,p_type => l_type
436 ,p_format => l_format
437 ,p_date_value => l_date_value
438 ,p_number_value => l_number_value
439 ,p_text_value => l_text_value);
440 --
441 if NOT l_exists then
442 -- supply HR error message
443 -- supply HR error message, HR_ACTIVITY_TYPE does not exist
444 hr_utility.set_message(800,'HR_52468_NO_ACTIVITY_ATTR');
445 hr_utility.set_message_token('ACTIVITY_ATTRIBUTE', g_hr_activity_type_value);
446 -- get the activity name
447 get_activity_name
448 (p_item_type => p_item_type
449 ,p_item_key => p_item_key
450 ,p_actid => p_actid
451 ,p_activity_name => l_activity_name
452 ,p_activity_display_name => l_activity_display_name);
453 hr_utility.set_message_token('ACTIVITY_NAME', l_activity_display_name);
454 hr_utility.set_message_token('ITEM_TYPE', p_item_type);
455 hr_utility.raise_error;
456 end if;
457 -- check to see if the HR_ACTIVITY_TYPE_VALUE is NULL
458 -- note: we are assuming that the value must be of text type
459 if l_text_value is NULL then
460 -- supply HR error message, HR_ACTIVITY_TYPE_VALUE cannot be NULL
461 hr_utility.set_message(800,'HR_52469_ACTIVITY_ATTR_NULL');
462 hr_utility.set_message_token('ACTIVITY_ATTRIBUTE', g_hr_activity_type_value);
463 -- get the activity name
464 get_activity_name
465 (p_item_type => p_item_type
466 ,p_item_key => p_item_key
467 ,p_actid => p_actid
468 ,p_activity_name => l_activity_name
469 ,p_activity_display_name => l_activity_display_name);
470 hr_utility.set_message_token('ACTIVITY_NAME', l_activity_display_name);
471 hr_utility.set_message_token('ITEM_TYPE', p_item_type);
472 hr_utility.raise_error;
473 end if;
474 end check_activity_type_attrs;
475 -- ----------------------------------------------------------------------------
476 -- |-----------------------------< hr_web_page >------------------------------|
477 -- ----------------------------------------------------------------------------
478 procedure hr_web_page
479 (itemtype in varchar2
480 ,itemkey in varchar2
481 ,actid in number
482 ,funcmode in varchar2
483 ,resultout out nocopy varchar2) is
484 --
485 begin
486 if funcmode = 'RUN' then
487 -- check to ensure the HR_ACTIVITY_TYPE/VALUE activity attributes exist
488 check_activity_type_attrs
489 (p_item_type => itemtype
490 ,p_item_key => itemkey
491 ,p_actid => actid);
492 -- set the result to NOTIFIED: to stall the workflow engine
493 resultout := 'NOTIFIED:';
494 return;
495 end if;
496 if funcmode = 'CANCEL' then
497 resultout := 'COMPLETE:';
498 return;
499 end if;
500 exception
501 when others then
502 wf_core.Context
503 (g_package, 'hr_web_page', itemtype, itemkey, to_char(actid), funcmode);
504 raise;
505 end hr_web_page;
506 -- ----------------------------------------------------------------------------
507 -- |-------------------------< check_hr_window_title >------------------------|
508 -- ----------------------------------------------------------------------------
509 function check_hr_window_title
510 (p_item_type in wf_items.item_type%type
511 ,p_item_key in wf_items.item_key%type
512 ,p_actid in wf_activity_attr_values.process_activity_id%type)
513 return boolean is
514 --
515 l_exists boolean;
516 l_subtype wf_activity_attributes.subtype%type;
517 l_type wf_activity_attributes.type%type;
518 l_format wf_activity_attributes.format%type;
519 l_date_value wf_activity_attr_values.date_value%type;
520 l_number_value wf_activity_attr_values.number_value%type;
521 l_text_value wf_activity_attr_values.text_value%type;
522 --
523 begin
524 -- check to see if the WINDOW_TITLE activity attribute exists
525 get_act_attr_expanded_info
526 (p_item_type => p_item_type
527 ,p_item_key => p_item_key
528 ,p_actid => p_actid
529 ,p_name => g_window_title
530 ,p_exists => l_exists
531 ,p_subtype => l_subtype
532 ,p_type => l_type
533 ,p_format => l_format
534 ,p_date_value => l_date_value
535 ,p_number_value => l_number_value
536 ,p_text_value => l_text_value);
537 --
538 return(l_exists);
539 end check_hr_window_title;
540 -- ----------------------------------------------------------------------------
541 -- |---------------------------< get_hr_window_title >------------------------|
542 -- ----------------------------------------------------------------------------
543 function get_hr_window_title
544 (p_item_type in wf_items.item_type%type
545 ,p_item_key in wf_items.item_key%type
546 ,p_actid in wf_activity_attr_values.process_activity_id%type)
547 return varchar2 is
548 --
549 l_exists boolean;
550 l_subtype wf_activity_attributes.subtype%type;
551 l_type wf_activity_attributes.type%type;
552 l_format wf_activity_attributes.format%type;
553 l_date_value wf_activity_attr_values.date_value%type;
554 l_number_value wf_activity_attr_values.number_value%type;
555 l_text_value wf_activity_attr_values.text_value%type;
556 --
557 begin
558 -- check to see if the WINDOW_TITLE activity attribute exists
559 get_act_attr_expanded_info
560 (p_item_type => p_item_type
561 ,p_item_key => p_item_key
562 ,p_actid => p_actid
563 ,p_name => g_window_title
564 ,p_exists => l_exists
565 ,p_subtype => l_subtype
566 ,p_type => l_type
567 ,p_format => l_format
568 ,p_date_value => l_date_value
569 ,p_number_value => l_number_value
570 ,p_text_value => l_text_value);
571 --
572 if l_exists then
573 return(l_text_value);
574 else
575 return(NULL);
576 end if;
577 end get_hr_window_title;
578 -- ----------------------------------------------------------------------------
579 -- |-------------------------< check_web_page_code >--------------------------|
580 -- ----------------------------------------------------------------------------
581 function check_web_page_code
582 (p_item_type in wf_items.item_type%type
583 ,p_item_key in wf_items.item_key%type
584 ,p_actid in wf_activity_attr_values.process_activity_id%type
585 ,p_web_page_section_code in wf_activity_attributes.name%type)
586 return boolean is
587 --
588 l_exists boolean;
589 l_subtype wf_activity_attributes.subtype%type;
590 l_type wf_activity_attributes.type%type;
591 l_format wf_activity_attributes.format%type;
592 l_date_value wf_activity_attr_values.date_value%type;
593 l_number_value wf_activity_attr_values.number_value%type;
594 l_text_value wf_activity_attr_values.text_value%type;
595 --
596 begin
597 -- check to see if the specified p_web_page_section_code activity
598 -- attribute exists
599 get_act_attr_expanded_info
600 (p_item_type => p_item_type
601 ,p_item_key => p_item_key
602 ,p_actid => p_actid
603 ,p_name => p_web_page_section_code
604 ,p_exists => l_exists
605 ,p_subtype => l_subtype
606 ,p_type => l_type
607 ,p_format => l_format
608 ,p_date_value => l_date_value
609 ,p_number_value => l_number_value
610 ,p_text_value => l_text_value);
611 --
612 if l_exists and l_text_value is not null then
613 return(TRUE);
614 else
615 return(FALSE);
616 end if;
617 end check_web_page_code;
618 -- ----------------------------------------------------------------------------
619 -- |-----------------------------< get_web_page_code >------------------------|
620 -- ----------------------------------------------------------------------------
621 function get_web_page_code
622 (p_item_type in wf_items.item_type%type
623 ,p_item_key in wf_items.item_key%type
624 ,p_actid in wf_activity_attr_values.process_activity_id%type
625 ,p_web_page_section_code in wf_activity_attributes.name%type)
626 return varchar2 is
627 --
628 l_exists boolean;
629 l_subtype wf_activity_attributes.subtype%type;
630 l_type wf_activity_attributes.type%type;
631 l_format wf_activity_attributes.format%type;
632 l_date_value wf_activity_attr_values.date_value%type;
633 l_number_value wf_activity_attr_values.number_value%type;
634 l_text_value wf_activity_attr_values.text_value%type;
635 --
636 begin
637 -- get the p_web_page_section_code activity attribute details
638 get_act_attr_expanded_info
639 (p_item_type => p_item_type
640 ,p_item_key => p_item_key
641 ,p_actid => p_actid
642 ,p_name => p_web_page_section_code
643 ,p_exists => l_exists
644 ,p_subtype => l_subtype
645 ,p_type => l_type
646 ,p_format => l_format
647 ,p_date_value => l_date_value
648 ,p_number_value => l_number_value
649 ,p_text_value => l_text_value);
650 --
651 if l_exists then
652 return(l_text_value);
653 else
654 return(NULL);
655 end if;
656 end get_web_page_code;
657 -- ----------------------------------------------------------------------------
658 -- |-------------------------< get_act_reentry_value_info >-------------------|
659 -- ----------------------------------------------------------------------------
660 procedure get_act_reentry_value_info
661 (p_item_type in wf_items.item_type%type
662 ,p_item_key in wf_items.item_key%type
663 ,p_actid in wf_activity_attr_values.process_activity_id%type
664 ,p_exists out nocopy boolean
665 ,p_result_code out nocopy wf_item_activity_statuses_v.activity_result_code%type) is
666 --
667 cursor l_csr_wiasv is
668 select wiasv.activity_result_code
669 from wf_item_activity_statuses wiasv
670 where wiasv.process_activity = p_actid
671 and wiasv.item_type = p_item_type
672 and wiasv.item_key = p_item_key
673 order by wiasv.end_date desc;
674
675 /*select wiasv.activity_result_code
676 from wf_item_activity_statuses_v wiasv
677 where wiasv.activity_id = p_actid
678 and wiasv.item_type = p_item_type
679 and wiasv.item_key = p_item_key
680 order by wiasv.activity_end_date desc;*/
681 --
682 l_counter integer := 0;
683 --
684 begin
685 for I in l_csr_wiasv loop
686 -- increment the counter
687 l_counter := l_counter + 1;
688 -- get the current activity result code
689 p_result_code := I.activity_result_code;
690 if l_counter = 2 then
691 -- exit the loop on the second iteration
692 exit;
693 end if;
694 end loop;
695 --
696 -- Changed July 08, 1998 vtakru
697 -- if l_counter <= 1 then
698 if l_counter < 1 then
699 --
700 p_exists := FALSE;
701 p_result_code := NULL;
702 else
703 p_exists := TRUE;
704 end if;
705 end get_act_reentry_value_info;
706 -- ----------------------------------------------------------------------------
707 -- |-------------------------< check_activity_reentry >-----------------------|
708 -- ----------------------------------------------------------------------------
709 function check_activity_reentry
710 (p_item_type in wf_items.item_type%type
711 ,p_item_key in wf_items.item_key%type
712 ,p_actid in wf_activity_attr_values.process_activity_id%type)
713 return boolean is
714 --
715 l_exists boolean;
716 l_activity_result_code wf_item_activity_statuses_v.activity_result_code%type;
717 --
718 begin
719 get_act_reentry_value_info
720 (p_item_type => p_item_type
721 ,p_item_key => p_item_key
722 ,p_actid => p_actid
723 ,p_exists => l_exists
724 ,p_result_code => l_activity_result_code);
725 return(l_exists);
726 end check_activity_reentry;
727 -- ----------------------------------------------------------------------------
728 -- |-------------------------< get_activity_reentry_value >-------------------|
729 -- ----------------------------------------------------------------------------
730 function get_activity_reentry_value
731 (p_item_type in wf_items.item_type%type
732 ,p_item_key in wf_items.item_key%type
733 ,p_actid in wf_activity_attr_values.process_activity_id%type)
734 return wf_item_activity_statuses_v.activity_result_code%type is
735 --
736 l_exists boolean;
737 l_activity_result_code wf_item_activity_statuses_v.activity_result_code%type;
738 --
739 begin
740 get_act_reentry_value_info
741 (p_item_type => p_item_type
742 ,p_item_key => p_item_key
743 ,p_actid => p_actid
744 ,p_exists => l_exists
745 ,p_result_code => l_activity_result_code);
746 return(l_activity_result_code);
747 end get_activity_reentry_value;
748 -- ----------------------------------------------------------------------------
749 -- |--------------------< wf_get_runnable_process_name >----------------------|
750 -- ----------------------------------------------------------------------------
751 function wf_get_runnable_process_name
752 (p_item_type in wf_items.item_type%type
753 ,p_process_name in wf_process_activities.process_name%type)
754 return wf_runnable_processes_v.display_name%type is
755 -- cursor determines is the specified process is runnable
756 cursor csr_wrpv is
757 select wrpv.display_name
758 from wf_runnable_processes_v wrpv
759 where wrpv.item_type = p_item_type
760 and wrpv.process_name = p_process_name;
761 --
762 l_display_name wf_runnable_processes_v.display_name%type;
763 --
764 begin
765 -- Determine if the specified process is runnable
766 open csr_wrpv;
767 fetch csr_wrpv into l_display_name;
768 if csr_wrpv%notfound then
769 close csr_wrpv;
770 return(NULL);
771 end if;
772 close csr_wrpv;
773 return(l_display_name);
774 end wf_get_runnable_process_name;
775 -- ----------------------------------------------------------------------------
776 -- |-------------------------< wf_process_runnable >--------------------------|
777 -- ----------------------------------------------------------------------------
778 function wf_process_runnable
779 (p_item_type in wf_items.item_type%type
780 ,p_process_name in wf_process_activities.process_name%type)
781 return boolean is
782 --
783 begin
784 if wf_get_runnable_process_name
785 (p_item_type => p_item_type
786 ,p_process_name => p_process_name) is NULL then
787 return(FALSE);
788 else
789 return(TRUE);
790 end if;
791 end wf_process_runnable;
792 -- ----------------------------------------------------------------------------
793 -- |------------------------------< create_process >--------------------------|
794 -- ----------------------------------------------------------------------------
795 procedure create_process
796 (p_process_name in wf_process_activities.process_name%type
797 ,p_item_type in wf_items.item_type%type
798 ,p_person_id in varchar2 default null
799 ,p_called_from in varchar2 default null
800 ,p_item_attribute in g_varchar2_tab_type
801 default g_varchar2_tab_default
802 ,p_item_attribute_value in g_varchar2_tab_type
803 default g_varchar2_tab_default
804 ,p_number_of_attributes_in in number default 0) is
805 --
806 l_process_name wf_process_activities.process_name%type :=
807 upper(p_process_name);
808
809 l_item_type wf_items.item_type%type := upper(p_item_type);
810 l_item_key wf_items.item_key%type;
811 --
812 l_creator_person_id per_people_f.person_id%type;
813 l_index binary_integer;
814 l_temp_item_attribute varchar2(2000);
815 l_temp_item_attribute_value varchar2(2000);
816 --
817 l_role_name varchar2(320);
818 l_role_displayname varchar2(360);
819
820 l_item_attribute g_varchar2_tab_type := p_item_attribute;
821 l_item_attribute_value g_varchar2_tab_type := p_item_attribute_value;
822 l_number_of_attributes_in number := p_number_of_attributes_in;
823
824 begin
825 -- --------------------------------------------------------------------------
826 -- Validate the session and get the person id
827 -- --------------------------------------------------------------------------
828 if p_person_id is not null then
829 l_number_of_attributes_in := l_number_of_attributes_in + 1;
830 l_item_attribute(l_number_of_attributes_in) := 'P_PERSON_ID';
831 l_item_attribute_value(l_number_of_attributes_in) := p_person_id;
832 l_number_of_attributes_in := l_number_of_attributes_in + 1;
833 l_item_attribute(l_number_of_attributes_in) := 'CURRENT_PERSON_ID';
834 l_item_attribute_value(l_number_of_attributes_in) := p_person_id;
835 end if;
836 if p_called_from is not null then
837 l_number_of_attributes_in := l_number_of_attributes_in + 1;
838 l_item_attribute(l_number_of_attributes_in) := 'P_CALLED_FROM';
839 l_item_attribute_value(l_number_of_attributes_in) := p_called_from;
840 end if;
841
842 hr_util_misc_web.validate_session(p_person_id => l_creator_person_id);
843 -- Determine if the specified process is runnable
844 if NOT wf_process_runnable
845 (p_item_type => l_item_type
846 ,p_process_name => l_process_name) then
847 -- supply HR error message, p_process_name either does not exist or
848 -- is NOT a runnable process
849 hr_utility.set_message(800,'HR_52958_WKF2TSK_INC_PROCESS');
850 hr_utility.set_message_token('ITEM_TYPE', l_item_type);
851 hr_utility.set_message_token('PROCESS_NAME', l_process_name);
852 hr_utility.raise_error;
853 end if;
854 -- Get the next item key from the sequence
855 select hr_workflow_item_key_s.nextval
856 into l_item_key
857 from sys.dual;
858 -- Create the Workflow Process
859 wf_engine.CreateProcess
860 (itemtype => l_item_type
861 ,itemkey => l_item_key
862 ,process => l_process_name);
863 --
864 -- Create the standard set of item attributes
865 -- CURRENT_PERSON_ID and CREATOR_PERSON_ID
866 --
867 create_hr_directory_services
868 (p_item_type => l_item_type
869 ,p_item_key => l_item_key
870 ,p_service_name => 'CREATOR_PERSON'
871 ,p_service_person_id => l_creator_person_id);
872 create_hr_directory_services
873 (p_item_type => l_item_type
874 ,p_item_key => l_item_key
875 ,p_service_name => 'CURRENT_PERSON'
876 ,p_service_person_id => l_creator_person_id);
877 -- check to see if the SESSION_ID attribute has been created
878 if not item_attribute_exists
879 (p_item_type => l_item_type
880 ,p_item_key => l_item_key
881 ,p_name => 'SESSION_ID') then
882 -- the SESSION_ID does not exist so create it
883 wf_engine.additemattr
884 (itemtype => l_item_type
885 ,itemkey => l_item_key
886 ,aname => 'SESSION_ID');
887 end if;
888 -- set the SESSION_ID to the person who is creating the process
889 wf_engine.setitemattrnumber
890 (itemtype => l_item_type
891 ,itemkey => l_item_key
892 ,aname => 'SESSION_ID'
893 ,avalue => to_number(icx_sec.getID(icx_sec.PV_SESSION_ID)));
894 -- check to see if the PROCESS_NAME attribute has been created
895 if not item_attribute_exists
896 (p_item_type => l_item_type
897 ,p_item_key => l_item_key
898 ,p_name => 'PROCESS_NAME') then
899 -- the PROCESS_NAME does not exist so create it
900 wf_engine.additemattr
901 (itemtype => l_item_type
902 ,itemkey => l_item_key
903 ,aname => 'PROCESS_NAME');
904 end if;
905 -- set the PROCESS_NAME
906 wf_engine.setitemattrtext
907 (itemtype => l_item_type
908 ,itemkey => l_item_key
909 ,aname => 'PROCESS_NAME'
910 ,avalue => l_process_name);
911 -- check to see if the PROCESS_DISPLAY_NAME attribute has been created
912 if not item_attribute_exists
913 (p_item_type => l_item_type
914 ,p_item_key => l_item_key
915 ,p_name => 'PROCESS_DISPLAY_NAME') then
916 -- the PROCESS_DISPLAY_NAME does not exist so create it
917 wf_engine.additemattr
918 (itemtype => l_item_type
919 ,itemkey => l_item_key
920 ,aname => 'PROCESS_DISPLAY_NAME');
921 end if;
922 -- set the PROCESS_DISPLAY_NAME to the person who is creating the process
923 wf_engine.setitemattrtext
924 (itemtype => l_item_type
925 ,itemkey => l_item_key
926 ,aname => 'PROCESS_DISPLAY_NAME'
927 ,avalue => wf_get_runnable_process_name
928 (p_item_type => l_item_type
929 ,p_process_name => l_process_name));
930 -- Fix for bug 2619178 begins
931 if not item_attribute_exists
932 (p_item_type => l_item_type
933 ,p_item_key => l_item_key
934 ,p_name => 'HR_EDA_MODE') then
935 wf_engine.additemattr
936 (itemtype => l_item_type
937 ,itemkey => l_item_key
938 ,aname => 'HR_EDA_MODE');
939 end if;
940 if p_person_id is null then
941 -- comming from Employee SS
942 wf_engine.setitemattrtext
943 (itemtype => l_item_type
944 ,itemkey => l_item_key
945 ,aname => 'HR_EDA_MODE'
946 ,avalue => 'Y');
947 else
948 -- comming from Manager SS
949 wf_engine.setitemattrtext
950 (itemtype => l_item_type
951 ,itemkey => l_item_key
952 ,aname => 'HR_EDA_MODE'
953 ,avalue => 'N');
954 end if;
955 -- Fix for bug 2619178 ends.
956 --
957 -- Create Item Attributes for those passed in
958 --
959 l_index := 1;
960 --
961 WHILE l_index <= l_number_of_attributes_in LOOP
962 begin
963 -- upper the item attribute name
964 -- if a NO_DATA_FOUND exception occurs, the exception is
965 -- handled and the item is skipped
966 l_temp_item_attribute := upper(l_item_attribute(l_index));
967 begin
968 l_temp_item_attribute_value := l_item_attribute_value(l_index);
969 exception
970 when NO_DATA_FOUND then
971 -- The array element at the index position has not been set
972 -- handle the exception and set the value to NULL
973 l_temp_item_attribute_value := NULL;
974 end;
975 if not item_attribute_exists
976 (p_item_type => l_item_type
977 ,p_item_key => l_item_key
978 ,p_name => l_temp_item_attribute) then
979 wf_engine.additemattr
980 (itemtype => l_item_type
981 ,itemkey => l_item_key
982 ,aname => l_temp_item_attribute);
983 end if;
984 --
985 if (l_temp_item_attribute = 'CREATOR_PERSON_ID' or
986 l_temp_item_attribute = 'CURRENT_PERSON_ID') then
987 --
988 create_hr_directory_services
989 (p_item_type => l_item_type
990 ,p_item_key => l_item_key
991 ,p_service_name => substr(l_temp_item_attribute,
992 1, length(l_temp_item_attribute) - 3)
993 ,p_service_person_id => nvl(l_temp_item_attribute_value,
994 l_creator_person_id));
995 else
996 wf_engine.setitemattrtext
997 (itemtype => l_item_type
998 ,itemkey => l_item_key
999 ,aname => l_temp_item_attribute
1000 ,avalue => l_temp_item_attribute_value);
1001 end if;
1002 l_index := l_index + 1;
1003 exception
1004 when NO_DATA_FOUND then
1005 -- The array element at the index position has not been set
1006 -- Ignore, but increment the counter and continue with the LOOP
1007 l_index := l_index + 1;
1008 end;
1009 END LOOP;
1010
1011 -- ---------------------------------
1012 -- Get the Role for the Owner
1013 -- ---------------------------------
1014 wf_directory.getRoleName
1015 (p_orig_system => 'PER'
1016 ,p_orig_system_id => l_creator_person_id
1017 ,p_name => l_role_name
1018 ,p_display_name => l_role_displayname);
1019
1020 IF l_role_name = '' OR l_role_name IS NULL THEN
1021 RAISE g_invalid_responsibility;
1022 END IF;
1023 -- ---------------------------------------------------
1024 -- Set the Item Owner (Fix for Bug # 758351)
1025 -- ---------------------------------------------------
1026 wf_engine.setItemOwner
1027 (itemtype => l_item_type
1028 ,itemkey => l_item_key
1029 ,owner => l_role_name);
1030
1031 -- check if the attribute exists if not create
1032
1033 if not item_attribute_exists
1034 (p_item_type => l_item_type
1035 ,p_item_key => l_item_key
1036 ,p_name => 'CURRENT_EFFECTIVE_DATE') then
1037 wf_engine.additemattr
1038 (itemtype => l_item_type
1039 ,itemkey => l_item_key
1040 ,aname => 'CURRENT_EFFECTIVE_DATE');
1041 end if;
1042
1043 -- set the item attribute for effective date
1044 --CURRENT_EFFECTIVE_DATE
1045 wf_engine.setitemattrdate
1046 (itemtype => l_item_type
1047 ,itemkey => l_item_key
1048 ,aname => 'CURRENT_EFFECTIVE_DATE'
1049 ,avalue => trunc(sysdate));
1050
1051
1052 -- Start the WF runtime process
1053 wf_engine.startprocess
1054 (itemtype => l_item_type
1055 ,itemkey => l_item_key);
1056 -- Continue the process
1057 continue_process
1058 (p_item_type => l_item_type
1059 ,p_item_key => l_item_key);
1060 --
1061 EXCEPTION
1062 WHEN g_invalid_responsibility THEN
1063 fnd_message.set_name('PER','HR_SSA_INVALID_RESPONSIBILITY');
1064 icx_util.add_error(fnd_message.get);
1065 icx_admin_sig.error_screen('HRSSA');
1066 end create_process;
1067 -- ----------------------------------------------------------------------------
1068 -- |------------------------------< display_html >----------------------------|
1069 -- ----------------------------------------------------------------------------
1070 procedure display_html
1071 (p_procedure_name in varchar2
1072 ,p_item_type in wf_items.item_type%type
1073 ,p_item_key in wf_items.item_key%type
1074 ,p_actid in wf_activity_attr_values.process_activity_id%type) is
1075 --
1076 l_cursor_name integer;
1077 l_sqlbuf varchar2(2000);
1078 l_row_processed integer;
1079 --
1080 begin
1081 l_sqlbuf := 'begin ' || p_procedure_name || ' (:v1,:v2,:v3); end;';
1082 l_cursor_name := dbms_sql.open_cursor;
1083 dbms_sql.parse(l_cursor_name, l_sqlbuf, dbms_sql.v7);
1084 dbms_sql.bind_variable(l_cursor_name, ':v1', p_item_type);
1085 dbms_sql.bind_variable(l_cursor_name, ':v2', p_item_key);
1086 dbms_sql.bind_variable(l_cursor_name, ':v3', p_actid);
1087 l_row_processed := dbms_sql.execute(l_cursor_name);
1088 dbms_sql.close_cursor(l_cursor_name);
1089 exception
1090 when OTHERS then
1091 -- supply HR error message
1092 -- an error has occurred when attempting to call the stored procedure
1093 -- to generate the web page.
1094 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1095 hr_utility.set_message_token('PROCEDURE', 'hr_workflow_service.display_html');
1096 hr_utility.set_message_token('STEP','10');
1097 hr_utility.raise_error;
1098 end display_html;
1099 -- ----------------------------------------------------------------------------
1100 -- |---------------------------< continue_process >---------------------------|
1101 -- ----------------------------------------------------------------------------
1102 procedure continue_process
1103 (p_item_type in wf_items.item_type%type
1104 ,p_item_key in wf_items.item_key%type) is
1105 --
1106 l_activity_type wf_activity_attr_values.text_value%type;
1107 l_activity_type_value wf_activity_attr_values.text_value%type;
1108 l_dummy number(1);
1109 --
1110 cursor csr_prc_active is
1111 select 1
1112 from wf_item_activity_statuses wias
1113 ,wf_process_activities wpa1
1114 where wpa1.process_item_type = p_item_type
1115 and wpa1.process_name = g_wf_root_process
1116 and wpa1.process_version =
1117 (select max(wpa2.process_version)
1118 from wf_process_activities wpa2
1119 where wpa2.process_item_type = p_item_type
1120 and wpa2.process_name = g_wf_root_process)
1121 and wias.process_activity = wpa1.instance_id
1122 and wias.item_type = p_item_type
1123 and wias.item_key = p_item_key
1124 and wias.activity_status = g_wf_root_process_active;
1125 -- select all ACTIVITIES for the item type/key which are FUNCTION's and
1126 -- have a status of NOTIFIED and have either the HR_ACTIVITY_TYPE or
1127 -- HR_ACTIVITY_TYPE_VALUE activity attributes defined
1128 cursor csr_wiasv is
1129 /*select wiasv.activity_id
1130 from wf_item_activity_statuses_v wiasv
1131 where wiasv.item_type = p_item_type
1132 and wiasv.item_key = p_item_key
1133 and wiasv.activity_type_code = g_wf_function
1134 and wiasv.activity_status_code = g_wf_activity_notified
1135 and exists
1136 (select 1
1137 from wf_activity_attr_values waav
1138 where waav.process_activity_id = wiasv.activity_id
1139 and waav.name in (g_hr_activity_type, g_hr_activity_type_value));*/
1140 SELECT process_activity activity_id
1141 FROM WF_ITEM_ACTIVITY_STATUSES IAS
1142 WHERE ias.item_type = p_item_type
1143 and ias.item_key = p_item_key
1144 and ias.activity_status = g_wf_activity_notified
1145 and exists
1146 (select 1
1147 from wf_activity_attr_values waav
1148 where waav.process_activity_id = ias.process_activity
1149 and waav.name in (g_hr_activity_type, g_hr_activity_type_value));
1150
1151 -- select the activity attribute NAME and TEXT_VALUE for the specified
1152 -- ACTIVITY where the activity attribute name is either; HR_ACTIVITY_TYPE
1153 -- and HR_ACTIVITY_TYPE_VALUE
1154 cursor csr_waav
1155 (c_process_activity_id wf_activity_attr_values.process_activity_id%type) is
1156 select waav.name
1157 ,waav.text_value
1158 from wf_activity_attr_values waav
1159 where waav.process_activity_id = c_process_activity_id
1160 and waav.name in (g_hr_activity_type, g_hr_activity_type_value);
1161 begin
1162 -- check to see if the process is still ACTIVE
1163 open csr_prc_active;
1164 fetch csr_prc_active into l_dummy;
1165 if csr_prc_active%notfound then
1166 -- the process is not ACTIVE anymore there no further processing
1167 -- is required
1168 close csr_prc_active;
1169 else
1170 close csr_prc_active;
1171 -- select each ACTIVITY which is a FUNCTION in a NOTIFIED state
1172 for l_csr1 in csr_wiasv loop
1173 -- select the HR_ACTIVITY_TYPE and HR_ACTIVITY_TYPE_VALUE attribute values
1174 for l_csr2 in csr_waav(l_csr1.activity_id) loop
1175 if l_csr2.name = 'HR_ACTIVITY_TYPE' then
1176 l_activity_type := l_csr2.text_value;
1177 else
1178 l_activity_type_value := l_csr2.text_value;
1179 end if;
1180 end loop;
1181 -- check to see if we have a web page
1182 if l_activity_type = g_hr_web_page_code then
1183 -- display the web/HTML page
1184 display_html
1185 (p_procedure_name => l_activity_type_value
1186 ,p_item_type => p_item_type
1187 ,p_item_key => p_item_key
1188 ,p_actid => l_csr1.activity_id);
1189 -- only display one web page so exit loop
1190 exit;
1191 end if;
1192 end loop;
1193 --
1194 end if;
1195 end continue_process;
1196 -- ----------------------------------------------------------------------------
1197 -- |------------------------< transition_activity >---------------------------|
1198 -- ----------------------------------------------------------------------------
1199 procedure transition_activity
1200 (p_item_type in wf_items.item_type%type
1201 ,p_item_key in wf_items.item_key%type
1202 ,p_actid in wf_activity_attr_values.process_activity_id%type
1203 ,p_result_code in wf_item_activity_statuses_v.activity_result_code%type) is
1204 --
1205 begin
1206 -- transition the wf engine
1207 wf_engine.CompleteActivity
1208 (itemtype => p_item_type
1209 ,itemkey => p_item_key
1210 ,activity => wf_engine.GetActivityLabel(actid => p_actid)
1211 ,result => p_result_code);
1212 -- continue the process after it has been transitioned
1213 continue_process
1214 (p_item_type => p_item_type
1215 ,p_item_key => p_item_key);
1216 --
1217 end transition_activity;
1218 --
1219 -- ----------------------------------------------------------------------------
1220 -- |-------------------------< check_active_wf_items >-- ---------------------|
1221 -- ----------------------------------------------------------------------------
1222 -- Purpose: This function is overloaded. It will only return the pending
1223 -- approval workflow items for a section of a page, ie. by
1224 -- activity_result_code to a table.
1225 -- ----------------------------------------------------------------------------
1226 function check_active_wf_items
1227 (p_item_type in wf_items.item_type%type
1228 ,p_process_name in wf_process_activities.process_name%type
1229 ,p_current_person_id in per_people_f.person_id%type
1230 ,p_activity_name in wf_item_activity_statuses_v.activity_name%type
1231 ,p_activity_result_code in varchar2
1232 )
1233 return active_wf_items_list is
1234 --
1235 l_process_name wf_process_activities.process_name%type;
1236 l_active_item_key wf_items.item_key%type;
1237 l_dummy number(1);
1238 l_count integer;
1239 l_active_wf_items_list hr_workflow_service.active_wf_items_list;
1240 l_activity_id wf_item_activity_statuses_v.activity_id%type;
1241 --
1242 -- Local cursor definations
1243 -- csr_wf_active_item Returns the item key of any process which
1244 -- is currently active with the name of p_process and belonging to
1245 -- the given person id
1246 cursor csr_wfactitms (p_current_person_id in per_people_f.person_id%type
1247 ,p_process_name in wf_process_activities.process_name%type
1248 ,p_item_type in wf_items.item_type%type
1249 ) is
1250
1251 -- fix for the bug1835437
1252 SELECT /*+ ordered */
1253 process.item_key
1254 FROM wf_process_activities activity,
1255 wf_item_activity_statuses process,
1256 wf_item_activity_statuses result,
1257 wf_item_attribute_values attribute
1258 WHERE activity.activity_name = p_process_name
1259 AND activity.activity_item_type = p_item_type
1260 AND activity.process_item_type = p_item_type
1261 AND activity.instance_id = process.process_activity
1262 AND process.activity_status = 'ACTIVE'
1263 AND process.item_type = p_item_type
1264 AND process.item_key = attribute.item_key
1265 AND attribute.item_type = p_item_type
1266 AND attribute.name = 'CURRENT_PERSON_ID'
1267 AND attribute.number_value = p_current_person_id
1268 and result.item_type = p_item_type
1269 and result.item_key = process.item_key
1270 and result.activity_result_code = p_activity_result_code;
1271
1272 /*
1273
1274 -- fix for bug # 1632855 also refer bug # 1577987
1275 -- removed the view wf_item_activity_statuses_v
1276 -- using activity_item_type||'' to disable non unique index
1277
1278 select process.item_key
1279 from wf_item_activity_statuses process
1280 ,wf_item_attribute_values attribute
1281 ,wf_process_activities activity
1282 where activity.activity_name = p_process_name
1283 and activity.process_item_type = p_item_type
1284 and activity.activity_item_type||'' = p_item_type
1285 and activity.instance_id = process.process_activity
1286 and process.activity_status||'' = 'ACTIVE'
1287 and process.item_type = p_item_type
1288 and process.item_key = attribute.item_key
1289 and attribute.item_type = p_item_type
1290 and attribute.name = 'CURRENT_PERSON_ID'
1291 and attribute.number_value = p_current_person_id;
1292
1293 */
1294
1295 /*
1296 select process.item_key
1297 from wf_item_activity_statuses process
1298 ,wf_item_attribute_values attribute
1299 ,wf_process_activities activity
1300 ,wf_item_activity_statuses result
1301 where activity.activity_name = p_process_name
1302 and activity.process_item_type = p_item_type
1303 and activity.activity_item_type = p_item_type
1304 and activity.instance_id = process.process_activity
1305 and process.activity_status = 'ACTIVE'
1306 and process.item_type = p_item_type
1307 and process.item_key = attribute.item_key
1308 and attribute.item_type = process.item_type
1309 and attribute.name = 'CURRENT_PERSON_ID'
1310 and attribute.number_value = p_current_person_id
1311 and result.item_type = p_item_type
1312 and result.item_key = process.item_key
1313 and result.activity_result_code = p_activity_result_code;
1314 */
1315
1316 /*select process.item_key
1317 from wf_item_activity_statuses_v process
1318 where process.activity_name = p_process_name
1319 and process.activity_status_code = 'ACTIVE'
1320 and process.item_type = p_item_type
1321 and process.item_key in (select attribute.item_key
1322 from wf_item_attribute_values attribute
1323 where attribute.item_type = p_item_type
1324 and attribute.name = 'CURRENT_PERSON_ID'
1325 and attribute.number_value = p_current_person_id);*/
1326
1327 cursor csr_hats is
1328 select 1
1329 from hr_api_transaction_steps
1330 where item_type = p_item_type
1331 and item_key = l_active_item_key;
1332
1333 cursor csr_wfactname is
1334 select activity.instance_id activity_id
1335 from wf_process_activities activity,
1336 wf_item_activity_statuses process
1337 where activity.activity_name = p_activity_name
1338 and activity.process_item_type = p_item_type
1339 and activity.activity_item_type = p_item_type
1340 and activity.instance_id = process.process_activity
1341 and process.item_type = p_item_type
1342 and process.item_key = l_active_item_key
1343 and process.activity_status = 'COMPLETE';
1344
1345 /* select distinct process.activity_id
1346 from wf_item_activity_statuses_v process
1347 where process.item_type = p_item_type
1348 and process.item_key = l_active_item_key
1349 and process.activity_name = p_activity_name; */
1350
1351 --
1352 l_activity_result_code wf_item_activity_statuses.activity_result_code%type;
1353 --
1354 begin
1355 -- There can be mulitiple Itemkeys each corresponding to a
1356 -- section of a worksheet. Loop through all of them and validate
1357 -- that the records exist in the transaction table.
1358 l_count := 0;
1359 -- get each active process for the person in the given itemtype
1360 <<main_loop>>
1361 for I in csr_wfactitms (p_current_person_id => p_current_person_id
1362 ,p_process_name => p_process_name
1363 ,p_item_type => p_item_type
1364 ) loop
1365 l_active_item_key := I.item_key;
1366 if l_active_item_key Is Not Null then
1367 -- open the cursor
1368 open csr_hats;
1369 fetch csr_hats into l_dummy;
1370 if csr_hats%notfound then
1371 l_active_item_key := null;
1372 else
1373 -- Open cursor and get the activity name
1374 open csr_wfactname;
1375 fetch csr_wfactname into l_activity_id;
1376 if csr_wfactname%notfound then
1377 l_activity_id := null;
1378 else
1379 l_activity_result_code := p_activity_result_code;
1380 -- hr_workflow_service.get_activity_reentry_value
1381 -- (p_item_type => p_item_type
1382 -- ,p_item_key => l_active_item_key
1383 -- ,p_actid => l_activity_id);
1384 --IF upper(p_activity_result_code) =
1385 -- upper(l_activity_result_code) THEN
1386 -------------------------------------------------------
1387 -- NOTE: The count increment statement must be at
1388 -- the place where a row is to be written
1389 -- to the l_active_wf_items_list table. Otherwise,
1390 -- we'll get index mismatched problem with the
1391 -- NO_DATA_FOUND error when accessing the table.
1392 -------------------------------------------------------
1393 l_count := l_count + 1;
1394 l_active_wf_items_list(l_count).active_item_key
1395 := l_active_item_key;
1396 l_active_wf_items_list(l_count).activity_id
1397 := l_activity_id;
1398 l_active_wf_items_list(l_count).activity_result_code
1399 := l_activity_result_code;
1400 --END IF;
1401 end if;
1402 close csr_wfactname;
1403 end if;
1404 close csr_hats;
1405 end if;
1406 end loop;
1407 return l_active_wf_items_list;
1408 --
1409 end check_active_wf_items;
1410 -- ----------------------------------------------------------------------------
1411 -- |-------------------------< check_active_wf_items >-------------------------|
1412 -- ----------------------------------------------------------------------------
1413 -- Purpose: This function will return all the pending approval workflow items
1414 -- for a page. If a page has many sections, the caller need to filter
1415 -- the pending approval workflow items to find out if a particular
1416 -- section has active pending approval items by comparing the
1417 -- activity result code.
1418 -- ----------------------------------------------------------------------------
1419 function check_active_wf_items
1420 (p_item_type in wf_items.item_type%type
1421 ,p_process_name in wf_process_activities.process_name%type
1422 ,p_current_person_id in per_people_f.person_id%type
1423 ,p_activity_name in wf_item_activity_statuses_v.activity_name%type
1424 )
1425 return active_wf_items_list is
1426 --
1427 l_process_name wf_process_activities.process_name%type;
1428 l_active_item_key wf_items.item_key%type;
1429 l_dummy number(1);
1430 l_count integer;
1431 l_active_wf_items_list hr_workflow_service.active_wf_items_list;
1432 l_activity_id wf_item_activity_statuses_v.activity_id%type;
1433 --
1434 -- Local cursor definations
1435 -- csr_wf_active_item Returns the item key of any process which
1436 -- is currently active with the name of p_process and belonging to
1437 -- the given person id
1438 cursor csr_wfactitms (p_current_person_id in per_people_f.person_id%type
1439 ,p_process_name in wf_process_activities.process_name%type
1440 ,p_item_type in wf_items.item_type%type
1441 ) is
1442
1443
1444 -- fix for the bug1835437
1445 SELECT /*+ ordered */
1446 process.item_key
1447 FROM wf_process_activities activity,
1448 wf_item_activity_statuses process,
1449 wf_item_attribute_values attribute
1450 WHERE activity.activity_name = p_process_name
1451 AND activity.activity_item_type = p_item_type
1452 AND activity.instance_id = process.process_activity
1453 AND process.activity_status = 'ACTIVE'
1454 AND process.item_type = p_item_type
1455 AND process.item_key = attribute.item_key
1456 AND attribute.item_type = p_item_type
1457 AND attribute.name = 'CURRENT_PERSON_ID'
1458 AND attribute.number_value = p_current_person_id;
1459
1460 /*
1461 -- fix for bug # 1632855 and also refer bug # 1577987
1462 -- removed the view wf_item_activity_statuses_v
1463 -- using activity_item_type||'' to disable non unique index
1464 -- removed the redundant AND conditions
1465
1466 select process.item_key
1467 from wf_item_attribute_values attribute,
1468 wf_process_activities activity,
1469 wf_item_activity_statuses process
1470 where activity.activity_name = p_process_name
1471 -- and activity.process_item_type = p_item_type
1472 -- and activity.activity_item_type||'' = p_item_type
1473 and activity.instance_id = process.process_activity
1474 and process.activity_status = 'ACTIVE'
1475 and process.item_type = p_item_type
1476 and process.item_key = attribute.item_key
1477 and attribute.item_type = p_item_type
1478 and attribute.name = 'CURRENT_PERSON_ID'
1479 and attribute.number_value = p_current_person_id;
1480
1481 */
1482
1483 /*
1484 select process.item_key
1485 from wf_item_activity_statuses process
1486 ,wf_item_attribute_values attribute
1487 ,wf_process_activities activity
1488 where activity.activity_name = p_process_name
1489 and activity.process_item_type = p_item_type
1490 and activity.activity_item_type = p_item_type
1491 and activity.instance_id = process.process_activity
1492 and process.activity_status = 'ACTIVE'
1493 and process.item_type = p_item_type
1494 and process.item_key = attribute.item_key
1495 and attribute.item_type = p_item_type
1496 and attribute.name = 'CURRENT_PERSON_ID'
1497 and attribute.number_value = p_current_person_id;
1498 */
1499
1500 /*select process.item_key
1501 from wf_item_activity_statuses_v process
1502 where process.activity_name = p_process_name
1503 and process.activity_status_code = 'ACTIVE'
1504 and process.item_type = p_item_type
1505 and process.item_key in (select attribute.item_key
1506 from wf_item_attribute_values attribute
1507 where attribute.item_type = p_item_type
1508 and attribute.name = 'CURRENT_PERSON_ID'
1509 and attribute.number_value = p_current_person_id);*/
1510
1511 cursor csr_hats is
1512 select 1
1513 from hr_api_transaction_steps
1514 where item_type = p_item_type
1515 and item_key = l_active_item_key;
1516
1517 cursor csr_wfactname is
1518 select activity.instance_id activity_id
1519 from wf_process_activities activity,
1520 wf_item_activity_statuses process
1521 where activity.activity_name = p_activity_name
1522 and activity.process_item_type = p_item_type
1523 and activity.activity_item_type = p_item_type
1524 and activity.instance_id = process.process_activity
1525 and process.item_type = p_item_type
1526 and process.item_key = l_active_item_key
1527 and process.activity_status = 'COMPLETE';
1528
1529 /*select distinct process.activity_id
1530 from wf_item_activity_statuses_v process
1531 where process.item_type = p_item_type
1532 and process.item_key = l_active_item_key
1533 and process.activity_name = p_activity_name; */
1534
1535 begin
1536 -- There can be mulitiple Itemkeys each corresponding to a
1537 -- section of a worksheet. Loop through all of them and validate
1538 -- that the records exist in the transaction table.
1539 l_count := 0;
1540 -- get each active process for the person in the given itemtype
1541 <<main_loop>>
1542 for I in csr_wfactitms (p_current_person_id => p_current_person_id
1543 ,p_process_name => p_process_name
1544 ,p_item_type => p_item_type
1545 ) loop
1546 l_active_item_key := I.item_key;
1547 if l_active_item_key Is Not Null then
1548 -- open the cursor
1549 open csr_hats;
1550 fetch csr_hats into l_dummy;
1551 if csr_hats%notfound then
1552 l_active_item_key := null;
1553 else
1554 l_count := l_count + 1;
1555 l_active_wf_items_list(l_count).active_item_key
1556 := l_active_item_key;
1557 -- Open cursor and get the activity name
1558 open csr_wfactname;
1559 fetch csr_wfactname into l_activity_id;
1560 if csr_wfactname%notfound then
1561 l_activity_id := null;
1562 else
1563 l_active_wf_items_list(l_count).activity_id
1564 := l_activity_id;
1565 l_active_wf_items_list(l_count).activity_result_code
1566 := hr_workflow_service.get_activity_reentry_value
1567 (p_item_type => p_item_type
1568 ,p_item_key => l_active_item_key
1569 ,p_actid => l_activity_id);
1570 end if;
1571 close csr_wfactname;
1572 end if;
1573 close csr_hats;
1574 end if;
1575 end loop;
1576 return l_active_wf_items_list;
1577 --
1578 end check_active_wf_items;
1579 --
1580 -- ----------------------------------------------------------------------------
1581 -- |---------------------------< get_active_wf_items >-------------------------|
1582 -- ----------------------------------------------------------------------------
1583 -- Purpose: This function will return all the pending approval workflow items
1584 -- for a page. If a page has many sections, the caller can pass
1585 -- the result code to find out if a particular section has active
1586 -- pending approval items by comparing the activity result code.
1587 -- This function is designed specifically for use in Personal
1588 -- Information to look for pending approval items which contain an
1589 -- acitivity with a specific result code equals to the input parameter.
1590 -- For Address Section in Personal Information, it needs to pass
1591 -- a value of either "PRIMARY" or "SECONDARY" in the parameter
1592 -- p_address_context because Primary Address and Secondary Address
1593 -- share the same api name, which is hr_process_address_ss.
1594 -- ----------------------------------------------------------------------------
1595 FUNCTION get_active_wf_items
1596 (p_item_type in wf_items.item_type%type
1597 ,p_process_name in wf_process_activities.process_name%type
1598 ,p_current_person_id in per_people_f.person_id%type
1599 ,p_api_name in hr_api_transaction_steps.api_name%type
1600 ,p_activity_result_code in varchar2 default null
1601 ,p_address_context in varchar2 default null
1602 )
1603 return active_wf_trans_items_list is
1604 --
1605 l_process_name wf_process_activities.process_name%type;
1606 l_active_item_key wf_items.item_key%type;
1607 l_count integer;
1608 l_active_wf_items_list hr_workflow_service.active_wf_trans_items_list;
1609 l_activity_id wf_item_activity_statuses_v.activity_id%type;
1610 --
1611 -- Local cursor definitions
1612 -----------------------------------------------------------------------------
1613 -- csr_wf_active_item Returns the item key of any process which
1614 -- is currently active with the name of p_process and belonging to
1615 -- the given person id
1616 --
1617 -- For a given item_type and item_key, we want to find out if that item_key
1618 -- contains a wf activity with a specific result code. This way, we can
1619 -- determine if the item_key has gone through a specific path, such as
1620 -- submit for approval.
1621 -----------------------------------------------------------------------------
1622 cursor csr_wfactitms (p_current_person_id in per_people_f.person_id%type
1623 ,p_process_name in wf_process_activities.process_name%type
1624 ,p_item_type in wf_items.item_type%type
1625 ,p_result_code in varchar2
1626 ) is
1627
1628 select process.item_key
1629 from wf_item_activity_statuses process
1630 ,wf_item_attribute_values attribute
1631 ,wf_process_activities activity
1632 ,wf_item_activity_statuses result
1633 where activity.activity_name = p_process_name
1634 and activity.process_item_type = p_item_type
1635 and activity.activity_item_type = p_item_type
1636 and activity.instance_id = process.process_activity
1637 and process.activity_status = 'ACTIVE'
1638 and process.item_type = p_item_type
1639 and process.item_key = attribute.item_key
1640 and attribute.item_type = process.item_type
1641 and attribute.name = 'CURRENT_PERSON_ID'
1642 and attribute.number_value = p_current_person_id
1643 and result.item_type = process.item_type
1644 and result.item_key = process.item_key
1645 and result.activity_result_code = p_result_code;
1646
1647 -----------------------------------------------------------------------------
1648 -- csr_wf_active_item Returns the item key of any process which
1649 -- is currently active with the name of p_process and belonging to
1650 -- the given person id
1651 --
1652 -- The following cursor is for no result code passed in.
1653 -----------------------------------------------------------------------------
1654 cursor csr_wfactitms2 (p_current_person_id in per_people_f.person_id%type
1655 ,p_process_name in wf_process_activities.process_name%type
1656 ,p_item_type in wf_items.item_type%type
1657 ) is
1658
1659 select process.item_key
1660 from wf_item_activity_statuses process
1661 ,wf_item_attribute_values attribute
1662 ,wf_process_activities activity
1663 where activity.activity_name = p_process_name
1664 and activity.process_item_type = p_item_type
1665 and activity.activity_item_type = p_item_type
1666 and activity.instance_id = process.process_activity
1667 and process.activity_status = 'ACTIVE'
1668 and process.item_type = p_item_type
1669 and process.item_key = attribute.item_key
1670 and attribute.item_type = process.item_type
1671 and attribute.name = 'CURRENT_PERSON_ID'
1672 and attribute.number_value = p_current_person_id;
1673
1674
1675 ------------------------------------------------------------------------------
1676 -- We use api_name to filter the transaction records to improve performance so
1677 -- that we don't need to loop through the wf_item_activity_statuses_v to
1678 -- derive the activity_id for a given activity_name. The view
1679 -- wf_item_activity_statuses_v has a lot of records to process.
1680 ------------------------------------------------------------------------------
1681 cursor csr_hats (csr_p_api_name in varchar2
1682 ,csr_p_item_key in varchar2) is
1683 select transaction_step_id, activity_id
1684 from hr_api_transaction_steps
1685 where item_type = p_item_type
1686 and item_key = csr_p_item_key
1687 and upper(api_name) = csr_p_api_name;
1688
1689 ------------------------------------------------------------------------------
1690 -- The following cursor is similar to csr_hats except that it further filters
1691 -- by a transaction value with a content of "P_PRIMARY_FLAG" equals to "Y" or
1692 -- "N". This cursor is used by addresses because both primary and
1693 -- secondary address use the same api name, which is hr_process_address_ss.
1694 -- Hence, we cannot differentiate a transaction step is either for primary
1695 -- or secondary address if we don't look at the transaction value.
1696 ------------------------------------------------------------------------------
1697 ------------------------------------------------------------------------------
1698 -- These changes are made to accomodate a second secondary address. since there
1699 -- is no way to identify between the two secondary addresses, we will call this
1700 -- a tertiary address and IN TRANSACTION TABLES the "P_PRIMARY_FLAG" will be
1701 -- set to "T" IMPORTANT -- only in transaction table
1702 ------------------------------------------------------------------------------
1703 cursor csr_addr_hats (csr_p_api_name in varchar2
1704 ,csr_p_item_key in varchar2
1705 ,csr_p_primary_flag in varchar2
1706 ) is
1707 select step.transaction_step_id, step.activity_id
1708 from hr_api_transaction_steps step
1709 ,hr_api_transaction_values value
1710 where item_type = p_item_type
1711 and item_key = csr_p_item_key
1712 and upper(api_name) = csr_p_api_name
1713 and step.transaction_step_id = value.transaction_step_id
1714 and value.name = 'P_PRIMARY_FLAG'
1715 and value.varchar2_value = csr_p_primary_flag;
1716
1717
1718 l_activity_result_code wf_item_activity_statuses.activity_result_code%type;
1719 l_activity_result_code_in wf_item_activity_statuses.activity_result_code%type
1720 default null;
1721 api_name_in hr_api_transaction_steps.api_name%type default null;
1722 l_trans_step_id hr_api_transaction_steps.transaction_step_id%type
1723 default null;
1724 l_address_primary_flag per_addresses.primary_flag%type default null;
1725 l_use_csr_addr_hats varchar2(1) default null;
1726
1727 BEGIN
1728 -- There can be mulitiple Itemkeys each corresponding to a
1729 -- section of a page. Loop through all of them and validate
1730 -- that the records exist in the transaction table because there be
1731 -- defunct wf processes but no transaction records exist.
1732
1733 l_activity_result_code_in := upper(p_activity_result_code);
1734 api_name_in := upper(p_api_name);
1735
1736 ----------------------------------------------------------------------------
1737 -- Check if we are getting pending approval items for primary or secondary
1738 -- address. If yes, we need to use a different cursor in retrieving trans
1739 -- data because primary and secondary address use the same api name, which
1740 -- is hr_process_address_ss.
1741 ----------------------------------------------------------------------------
1742 IF p_address_context IS NOT NULL
1743 THEN
1744 IF upper(p_address_context) = 'PRIMARY'
1745 THEN
1746 l_use_csr_addr_hats := 'Y';
1747 l_address_primary_flag := 'Y';
1748 ELSIF upper(p_address_context) = 'SECONDARY'
1749 THEN
1750 l_use_csr_addr_hats := 'Y';
1751 l_address_primary_flag := 'N';
1752 -- startregistration
1753 ELSIF upper(p_address_context) = 'TERTIARY'
1754 THEN
1755 l_use_csr_addr_hats := 'Y';
1756 l_address_primary_flag := 'T';
1757 -- endregistration
1758 END IF;
1759 ELSE
1760 l_use_csr_addr_hats := 'N';
1761 END IF;
1762
1763 l_count := 0;
1764 -- get each active process for the person in the given itemtype
1765 IF l_activity_result_code_in IS NOT NULL
1766 THEN
1767 -- filter by result code
1768 -- main loop
1769 FOR I in csr_wfactitms (p_current_person_id => p_current_person_id
1770 ,p_process_name => p_process_name
1771 ,p_item_type => p_item_type
1772 ,p_result_code => l_activity_result_code_in
1773 )
1774 LOOP
1775 l_active_item_key := I.item_key;
1776
1777 -- inner loop
1778 IF l_use_csr_addr_hats = 'Y'
1779 THEN
1780 FOR csr_trans in csr_addr_hats
1781 (csr_p_api_name => api_name_in
1782 ,csr_p_item_key => l_active_item_key
1783 ,csr_p_primary_flag => l_address_primary_flag
1784 )
1785 LOOP
1786 -------------------------------------------------------
1787 -- NOTE: The count increment statement must be at
1788 -- the place where a row is to be written
1789 -- to the l_active_wf_items_list table. Otherwise,
1790 -- we'll get index mismatched problem with the
1791 -- NO_DATA_FOUND error when accessing the table.
1792 -------------------------------------------------------
1793 l_count := l_count + 1;
1794 l_active_wf_items_list(l_count).active_item_key
1795 := l_active_item_key;
1796 l_active_wf_items_list(l_count).activity_id :=
1797 csr_trans.activity_id;
1798 l_active_wf_items_list(l_count).trans_step_id :=
1799 csr_trans.transaction_step_id;
1800 l_active_wf_items_list(l_count).activity_result_code
1801 := l_activity_result_code;
1802 END LOOP; -- end inner loop for address
1803 -- ELSE Please remove comment if registration removed
1804 -- startregistration
1805 ELSIF l_use_csr_addr_hats = 'N'
1806 THEN
1807 -- endregistration
1808 FOR csr_trans in csr_hats (csr_p_api_name => api_name_in
1809 ,csr_p_item_key => l_active_item_key)
1810 LOOP
1811 -------------------------------------------------------
1812 -- NOTE: The count increment statement must be at
1813 -- the place where a row is to be written
1814 -- to the l_active_wf_items_list table. Otherwise,
1815 -- we'll get index mismatched problem with the
1816 -- NO_DATA_FOUND error when accessing the table.
1817 -------------------------------------------------------
1818 l_count := l_count + 1;
1819 l_active_wf_items_list(l_count).active_item_key
1820 := l_active_item_key;
1821 l_active_wf_items_list(l_count).activity_id :=
1822 csr_trans.activity_id;
1823 l_active_wf_items_list(l_count).trans_step_id :=
1824 csr_trans.transaction_step_id;
1825 l_active_wf_items_list(l_count).activity_result_code
1826 := l_activity_result_code;
1827 END LOOP; -- end inner loop
1828 -- startregistration
1829 ELSIF l_use_csr_addr_hats = 'T'
1830 THEN
1831 FOR csr_trans in csr_hats (csr_p_api_name => api_name_in
1832 ,csr_p_item_key => l_active_item_key)
1833 LOOP
1834 -------------------------------------------------------
1835 -- NOTE: The count increment statement must be at
1836 -- the place where a row is to be written
1837 -- to the l_active_wf_items_list table. Otherwise,
1838 -- we'll get index mismatched problem with the
1839 -- NO_DATA_FOUND error when accessing the table.
1840 -------------------------------------------------------
1841 l_count := l_count + 1;
1842 l_active_wf_items_list(l_count).active_item_key
1843 := l_active_item_key;
1844 l_active_wf_items_list(l_count).activity_id :=
1845 csr_trans.activity_id;
1846 l_active_wf_items_list(l_count).trans_step_id :=
1847 csr_trans.transaction_step_id;
1848 l_active_wf_items_list(l_count).activity_result_code
1849 := l_activity_result_code;
1850 END LOOP; -- end inner loop
1851 -- endregistration
1852 END IF;
1853 END LOOP; -- end main loop
1854 ELSE
1855 -- no result code filter
1856 -- main loop
1857 FOR I in csr_wfactitms2 (p_current_person_id => p_current_person_id
1858 ,p_process_name => p_process_name
1859 ,p_item_type => p_item_type
1860 )
1861 LOOP
1862 l_active_item_key := I.item_key;
1863
1864 -- inner loop
1865 IF l_use_csr_addr_hats = 'Y'
1866 THEN
1867 FOR csr_trans in csr_addr_hats
1868 (csr_p_api_name => api_name_in
1869 ,csr_p_item_key => l_active_item_key
1870 ,csr_p_primary_flag => l_address_primary_flag
1871 )
1872 LOOP
1873 -------------------------------------------------------
1874 -- NOTE: The count increment statement must be at
1875 -- the place where a row is to be written
1876 -- to the l_active_wf_items_list table. Otherwise,
1877 -- we'll get index mismatched problem with the
1878 -- NO_DATA_FOUND error when accessing the table.
1879 -------------------------------------------------------
1880 l_count := l_count + 1;
1881 l_active_wf_items_list(l_count).active_item_key
1882 := l_active_item_key;
1883 l_active_wf_items_list(l_count).activity_id :=
1884 csr_trans.activity_id;
1885 l_active_wf_items_list(l_count).trans_step_id :=
1886 csr_trans.transaction_step_id;
1887 l_active_wf_items_list(l_count).activity_result_code
1888 := l_activity_result_code;
1889 END LOOP; -- end inner loop for address
1890 -- ELSE Please remove comment if registration removed
1891 --startregistration
1892 ELSIF l_use_csr_addr_hats = 'N'
1893 THEN
1894 --endregistration
1895 FOR csr_trans in csr_hats (csr_p_api_name => api_name_in
1896 ,csr_p_item_key => l_active_item_key)
1897 LOOP
1898 -------------------------------------------------------
1899 -- NOTE: The count increment statement must be at
1900 -- the place where a row is to be written
1901 -- to the l_active_wf_items_list table. Otherwise,
1902 -- we'll get index mismatched problem with the
1903 -- NO_DATA_FOUND error when accessing the table.
1904 -------------------------------------------------------
1905 -- no result code passed in for filtering
1906 l_count := l_count + 1;
1907 l_active_wf_items_list(l_count).active_item_key
1908 := l_active_item_key;
1909 l_active_wf_items_list(l_count).activity_id :=
1910 csr_trans.activity_id;
1911 l_active_wf_items_list(l_count).trans_step_id :=
1912 csr_trans.transaction_step_id;
1913 l_active_wf_items_list(l_count).activity_result_code
1914 := l_activity_result_code;
1915 END LOOP; -- inner loop
1916 --startregistration
1917 ELSIF l_use_csr_addr_hats = 'T'
1918 THEN
1919 FOR csr_trans in csr_hats (csr_p_api_name => api_name_in
1920 ,csr_p_item_key => l_active_item_key)
1921 LOOP
1922 -------------------------------------------------------
1923 -- NOTE: The count increment statement must be at
1924 -- the place where a row is to be written
1925 -- to the l_active_wf_items_list table. Otherwise,
1926 -- we'll get index mismatched problem with the
1927 -- NO_DATA_FOUND error when accessing the table.
1928 -------------------------------------------------------
1929 -- no result code passed in for filtering
1930 l_count := l_count + 1;
1931 l_active_wf_items_list(l_count).active_item_key
1932 := l_active_item_key;
1933 l_active_wf_items_list(l_count).activity_id :=
1934 csr_trans.activity_id;
1935 l_active_wf_items_list(l_count).trans_step_id :=
1936 csr_trans.transaction_step_id;
1937 l_active_wf_items_list(l_count).activity_result_code
1938 := l_activity_result_code;
1939 END LOOP; -- inner loop
1940 -- endregistration
1941 END IF;
1942 END LOOP; -- main loop
1943 END IF;
1944
1945 return l_active_wf_items_list;
1946 --
1947 END get_active_wf_items;
1948 -- ----------------------------------------------------------------------------
1949 -- |-------------------------< remove_defunct_process >--------------------------|
1950 -- ----------------------------------------------------------------------------
1951 procedure remove_defunct_process
1952 (itemtype in varchar2
1953 ,itemkey in varchar2
1954 ,actid in number
1955 ,funcmode in varchar2
1956 ,resultout out nocopy varchar2) is
1957 --
1958 l_item_key wf_items.item_key%type;
1959 l_dummy INTEGER;
1960 l_transaction_id hr_api_transactions.transaction_id%type;
1961 l_transaction_age number default 30;
1962 l_transaction_status varchar2(5) ;
1963 l_transaction_status_to_delete varchar2(10);
1964 c_status varchar2(10);
1965 l_delete_transaction boolean default false;
1966 errbuf varchar2(4000) default '';
1967
1968 --
1969 -- Local cursor definations
1970 -- csr_wfdfctitms Returns the item keys of any process activites which
1971 -- are in NOTFIED state and whose session ID are either disabled or
1972 -- which do exists.
1973 -- This cursor is now defunt as we are using the transaction age as the criteria instead of
1974 -- ICX session id. Please check the LLD for details.
1975 -- The changes to the program are tracked through bug # 2380121
1976 /*cursor csr_wfdfctitms is
1977 select wias.item_key
1978 from wf_item_activity_statuses wias,
1979 wf_activity_attr_values waav,
1980 wf_process_activities wpa,
1981 wf_item_attribute_values wiav
1982 where wias.item_type = itemtype
1983 and wias.activity_status = 'NOTIFIED'
1984 and wpa.instance_id = wias.process_activity
1985 and wpa.instance_id = waav.process_activity_id
1986 and waav.name = 'HR_ACTIVITY_TYPE'
1987 and wiav.item_key = wias.item_key
1988 and wiav.item_type = wias.item_type
1989 and wiav.name = 'SESSION_ID'
1990 and not exists
1991 (select 1
1992 from icx_sessions s
1993 where s.session_id = wiav.number_value
1994 and s.disabled_flag = 'N');
1995 */
1996
1997 cursor csr_wfdfctitms (c_transaction_age in number, c_status in varchar2) is
1998 select * from (select a.transaction_id transaction_id,
1999 s.text_value status,
2000 a.item_type item_type,
2001 a.item_key item_key
2002 from (select transaction_id,
2003 status,
2004 nvl(item_type,hr_workflow_service.getItemType(t.transaction_id)) item_type,
2005 nvl(item_key,hr_workflow_service.getItemKey(t.transaction_id)) item_key
2006 from hr_api_transactions t
2007 where t.last_update_date <= sysdate - c_transaction_age
2008 and t.status not in ('Y', 'YS','W')
2009 and t.transaction_ref_table <> 'PER_APPRAISALS'
2010 ) a, -- bug 3635925 , bug 5357274, bug 5990955
2011 wf_item_attribute_values s
2012 where a.item_type = itemtype
2013 and a.item_type = s.item_type
2014 and a.item_key = s.item_key
2015 and s.name = 'TRAN_SUBMIT')
2016 where status = nvl(c_status,status) ;
2017
2018 -- start bug 5990955
2019
2020 --Only the appraisals with a System status (appraisal_system_status in
2021 -- PER_APPRAISALS) as "COMPLETED" or "DELETED", irrespective of status in
2022 -- HR_API_TRANSACTIONS table will be deleted. So looking for the
2023 -- APPRAISAL_SYSTEM_STATUS in the cursor
2024
2025 CURSOR csr_wfdfctitmsapprs (c_transaction_age in number) is
2026 SELECT t.item_type, t.item_key, t.transaction_id
2027 FROM hr_api_transactions t, per_appraisals a
2028 WHERE t.transaction_ref_table = 'PER_APPRAISALS'
2029 AND t.last_update_date <= sysdate - c_transaction_age
2030 AND t.transaction_ref_id = a.appraisal_id
2031 AND a.appraisal_system_status IN ('DELETED','COMPLETED');
2032
2033 -- end bug 5990955
2034
2035 -- csr_wfdfctrans check if the Itemkey is present in the
2036 -- hr_api_transaction_steps table
2037 cursor csr_wfdfctrans is
2038 select hats.transaction_id
2039 from hr_api_transaction_steps hats
2040 where hats.item_type = itemtype
2041 and hats.item_key = l_item_key;
2042
2043 --
2044 -- to fetch orphan records caused by wf tables getting purged before
2045 -- executing this defunct process
2046 --
2047 CURSOR defunct_tx_ids IS
2048 /* SELECT distinct hats.transaction_id
2049 FROM hr_api_transaction_steps hats
2050 WHERE NOT EXISTS (SELECT 'Y'
2051 FROM wf_items wi
2052 WHERE wi.item_type = hats.item_type
2053 AND wi.item_key = hats.item_key);
2054 */
2055 -- Fix for bug#3322644
2056 SELECT hat.transaction_id
2057 FROM hr_api_transactions hat
2058 WHERE NOT EXISTS (SELECT 'Y'
2059 FROM wf_items wi
2060 WHERE wi.item_type = nvl(hat.item_type,hr_workflow_service.getItemType(hat.transaction_id))
2061 AND wi.item_key = nvl(hat.item_key,hr_workflow_service.getItemKey(hat.transaction_id))
2062 )
2063 AND Not Exists( select 'Y' from wf_items w --fix for bug 6121860
2064 where w.item_type = 'HRSFL'
2065 and w.user_key = hat.transaction_id)
2066 and hat.item_key is not null;
2067 --
2068 -- to fetch orphan records from wf tables which do not have an entry
2069 -- in the hr_api_transactions. This could cause from SSHR transaction
2070 -- not using hr_api_transactions and for WF process started without
2071 -- an entry to the hr_api_transactions. Possible causes, user started
2072 -- new transaction and closed the web browser.
2073
2074 -- fix for bug#2838117
2075 -- fetch all the records from wf_items for given item type which
2076 -- not in hr_api_transactions and icx session is disabled or does
2077 -- not exist.
2078
2079 -- 4287117
2080 --5076290
2081 CURSOR defunct_wf_ids (c_transaction_age in number) IS
2082 select wi.item_key
2083 from wf_items wi , wf_item_attribute_values av, icx_sessions s
2084 where wi.item_type= itemtype
2085 and trunc(wi.begin_date) <= trunc(sysdate) --fix for bug 6642996
2086 and wi.end_date is null
2087 and av.item_type = wi.item_type
2088 and av.item_key = wi.item_key
2089 and av.name = 'SESSION_ID'
2090 and av.number_value = s.session_id(+)
2091 and s.disabled_flag(+) = 'Y'
2092 and (
2093 (wi.item_key) not in (
2094 select t1.item_key
2095 from hr_api_transactions t1
2096 where wi.item_type = t1.item_type
2097 and wi.item_key = t1.item_key
2098 and t1.item_type = itemtype
2099 )
2100 and (wi.item_key) not in (
2101 select ts.item_key
2102 from hr_api_transaction_steps ts
2103 where ts.item_type = wi.item_type
2104 and ts.item_key = wi.item_key
2105 and ts.item_type = itemtype
2106 )
2107 );
2108 --5076290
2109 /* CURSOR defunct_wf_ids IS
2110 select wi.item_key
2111 from wf_items wi
2112 where wi.item_type = itemtype
2113 and wi.begin_date <= trunc(sysdate)
2114 and wi.end_date is null
2115 and not exists (select 'e' from hr_api_transactions t
2116 where
2117 t.item_type is not null
2118 and t.item_key is not null
2119 and wi.item_type = t.item_type
2120 and wi.item_key = t.item_key
2121 )
2122 and not exists (select 'e' from hr_api_transactions t, hr_api_transaction_steps ts
2123 where
2124 t.item_type is null
2125 and t.item_key is null
2126 and t.transaction_id = ts.transaction_id
2127 and wi.item_type = ts.item_type
2128 and wi.item_key = ts.item_key
2129 and ts.item_type is not null
2130 and ts.item_key is not null
2131 )
2132 and exists (select 'e' from wf_item_attribute_values av, icx_sessions s
2133 where av.item_type = wi.item_type
2134 and av.item_key = wi.item_key
2135 and av.name = 'SESSION_ID'
2136 and av.number_value = s.session_id(+)
2137 and s.disabled_flag(+) = 'Y');
2138 */
2139
2140 /* Cursor to identify if there is an offer to be closed*/
2141 cursor csrOfferDetails(c_transaction_id in number) is
2142 select hat.transaction_ref_id,
2143 hat.assignment_id,
2144 iof.offer_status
2145 from hr_api_transactions hat,
2146 irc_offers iof
2147 where hat.transaction_ref_table='IRC_OFFERS' and
2148 hat.transaction_ref_id = iof.offer_id and
2149 hat.transaction_id = c_transaction_id and
2150 iof.offer_status in ('PENDING','CORRECTION');
2151
2152 -- Fix for bug 6501341
2153 CURSOR defunct_wfsfl_ids IS
2154 select wi.item_key
2155 from wf_items wi
2156 where wi.item_type = 'HRSFL'
2157 and not exists (select transaction_id from hr_api_transactions
2158 where transaction_id = wi.user_key);
2159 --
2160
2161 l_offer_id number;
2162 l_applicant_assignment_id number;
2163 l_offer_status varchar2(100);
2164 -- 4287117
2165
2166 --
2167 begin
2168 --
2169 -- Get the transaction status value of items to be deleted
2170 -- This is normally passed through the Concurrent program
2171 -- and populated into item attribute HR_TRANS_STATUS_FOR_DEL_ATTR
2172 l_transaction_status_to_delete := wf_engine.getitemattrText
2173 (itemtype => itemtype
2174 ,itemkey => itemkey
2175 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR');
2176 -- Get the transaction age value of items to be deleted
2177 -- This is normally passed through the Concurrent program
2178 -- and populated into item attribute 'HR_TRANS_AGE_FOR_DEL_ATTR'
2179 l_transaction_age:= wf_engine.getitemattrNumber
2180 (itemtype => itemtype
2181 ,itemkey => itemkey
2182 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR');
2183
2184 -- c_status
2185 if(l_transaction_status_to_delete='ALL') then
2186 c_status := null;
2187 else
2188 c_status := l_transaction_status_to_delete;
2189 end if;
2190
2191 if funcmode = 'RUN' then
2192 -- first purge all the orphan transaction records if any
2193 BEGIN
2194 for rec in defunct_tx_ids loop
2195 begin
2196 hr_utility.set_location('START : Processing defunct transaction ID : '||rec.transaction_id,400);
2197
2198 l_offer_id := null;
2199 l_applicant_assignment_id := null;
2200 l_offer_status := null;
2201
2202 open csrOfferDetails(rec.transaction_id);
2203 fetch csrOfferDetails into l_offer_id, l_applicant_assignment_id,l_offer_status;
2204 close csrOfferDetails;
2205
2206 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id);
2207
2208 begin
2209 /* To check if it is the approval process for an offer. If yes, close the offer*/
2210 if l_offer_id is not null then
2211
2212 hr_utility.set_location ('Closing Offer...',415);
2213 hr_utility.set_location ('Offer ID : '||l_offer_id,430);
2214 hr_utility.set_location ('Applicant Assignment ID : '||l_applicant_assignment_id,445);
2215 hr_utility.set_location ('Offer Status : '||l_offer_status,460);
2216
2217 irc_offers_api.close_offer(p_effective_date => sysdate
2218 , p_applicant_assignment_id => l_applicant_assignment_id
2219 , p_offer_id => l_offer_id
2220 , p_change_reason => 'MANUAL_CLOSURE'
2221 , p_note_text => 'Closed from Complete Defunct Workflow Process');
2222 end if;
2223 --
2224 hr_utility.set_location ('Offer successfully closed ',480);
2225 exception
2226 when others then
2227 hr_utility.set_location ('Error occurred while closing offer : '||substr(SQLERRM,1,2000),500);
2228 end;
2229 exception
2230 when others then
2231 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_tx_ids' );
2232 end;
2233 hr_utility.set_location('END : Processing defunct transaction ID : '||rec.transaction_id,400);
2234 end loop;
2235 exception
2236 when others then
2237 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_tx_ids loop' );
2238 end;
2239
2240 -- Fix for bug 6501341
2241 BEGIN
2242 for rec in defunct_wfsfl_ids loop
2243 BEGIN -- Block to 'abort' the WF process
2244 wf_engine.abortprocess(itemtype => 'HRSFL'
2245 ,itemkey => rec.item_key
2246 ,result => 'eng_force');
2247 EXCEPTION
2248 when others then
2249 wf_core.Context(g_package, 'remove_defunct_process',
2250 'HRSFL', itemkey, to_char(actid),
2251 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2252 END;-- end of Block to 'abort' the WF process
2253 end loop;
2254 END;
2255
2256 -- fix for bug#2838117
2257 BEGIN
2258 for rec in defunct_wf_ids (l_transaction_age) loop
2259 BEGIN
2260 begin
2261 select TEXT_VALUE
2262 into l_transaction_status
2263 from WF_ITEM_ATTRIBUTE_VALUES
2264 where ITEM_TYPE = itemtype
2265 and ITEM_KEY = rec.item_key
2266 and NAME = 'TRAN_SUBMIT';
2267 exception
2268 when no_data_found then
2269 l_transaction_status := null;
2270 end;
2271 -- reset the l_delete_transaction status false by default
2272 l_delete_transaction:= false;
2273 -- check if the current transaction can deleted
2274 if(l_transaction_status='Y' or l_transaction_status='YS') then -- bug 3635925
2275 l_delete_transaction := false;
2276 else
2277 l_delete_transaction := true;
2278 end if;
2279
2280 if(l_delete_transaction) then -- delete transaction which explicitly
2281 -- identified for delete.
2282 -- First abort the WF process for this transaction record.
2283 BEGIN -- Block to 'abort' the WF process
2284 wf_engine.abortprocess(itemtype => itemtype
2285 ,itemkey => rec.item_key
2286 ,result => 'eng_force');
2287 EXCEPTION
2288 when others then
2289 wf_core.Context(g_package, 'remove_defunct_process',
2290 itemtype, itemkey, to_char(actid),
2291 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2292 END;-- end of Block to 'abort' the WF process
2293 end if;
2294 EXCEPTION
2295 when others then
2296 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_wf_ids' );
2297 END; -- end of block for loop
2298 end loop;
2299 EXCEPTION
2300 when others then
2301 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_wf_ids loop' );
2302 END; -- end block for defunct_wf_ids
2303
2304
2305
2306 for rec in csr_wfdfctitms(l_transaction_age,c_status) loop
2307 BEGIN -- inner block to catch exception for the transaction being processed.
2308 -- check the transaction status.
2309 -- we will delete the transaction data related to status
2310 -- l_transaction_status_to_delete
2311 -- All transactions with status 'Y' will not be touched by this program
2312
2313 if rec.item_key IS NOT NULL then
2314 -- Get the transaction status from the WF item attribute TRAN_SUBMIT
2315 -- need to remove this call once the SSHR V4.1 functionality is implemented.
2316 l_transaction_status :=wf_engine.GetItemAttrText(itemtype => itemtype
2317 ,itemkey => rec.item_key
2318 ,aname => 'TRAN_SUBMIT');
2319
2320 -- reset the l_delete_transaction status false by default
2321 l_delete_transaction:= false;
2322 -- check if the current transaction can deleted
2323 if(l_transaction_status='Y' or l_transaction_status='YS') then -- bug 3635925
2324 l_delete_transaction := false;
2325 elsif(l_transaction_status_to_delete='ALL') then
2326 l_delete_transaction := true;
2327 elsif (l_transaction_status_to_delete=l_transaction_status) then
2328 l_delete_transaction := true;
2329 end if;
2330
2331
2332
2333 if(l_delete_transaction) then -- delete transaction which explicitly
2334 -- identified for delete.
2335 -- First abort the WF process for this transaction record.
2336 BEGIN -- Block to 'abort' the WF process
2337 wf_engine.abortprocess(itemtype => itemtype
2338 ,itemkey => rec.item_key
2339 ,result => 'eng_force');
2340 exception
2341 when others then
2342 wf_core.Context(g_package, 'remove_defunct_process',
2343 itemtype, itemkey, to_char(actid),
2344 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2345 END;
2346
2347 BEGIN -- Block to 'purge' the data in the SSHR transaction tables.
2348 -- get the transaction for this transaction from workflow
2349 -- TRANSACTION_ID
2350 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id );
2351 exception
2352 when others then
2353 wf_core.Context(g_package, 'remove_defunct_process',
2354 itemtype, itemkey, to_char(actid),
2355 funcmode,'Record item key being processed was :'||nvl(rec.item_key,''));
2356 END;
2357
2358
2359 end if; -- end of status check
2360 end if; -- end of check for item key.
2361 exception
2362 when others then
2363 wf_core.Context(g_package, 'remove_defunct_process',
2364 itemtype, itemkey, to_char(actid), funcmode,'Record item key being processed was :'||rec.item_key );
2365 END ;
2366
2367 end loop;
2368
2369 -- start bug 5990955
2370
2371 for rec in csr_wfdfctitmsapprs(l_transaction_age) loop
2372 BEGIN
2373 wf_engine.abortprocess(itemtype => rec.item_type
2374 ,itemkey => rec.item_key
2375 ,result => 'eng_force');
2376
2377 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id );
2378
2379 exception
2380 when others then
2381 wf_core.Context(g_package, 'remove_defunct_process',
2382 itemtype, itemkey, to_char(actid),
2383 funcmode,'Record item key being processed was :'||nvl(rec.item_key,''));
2384 END;
2385 end loop;
2386
2387 -- end bug 5990955
2388 --
2389 resultout := 'COMPLETE:';
2390 return;
2391 end if;
2392 --
2393 if funcmode = 'CANCEL' then
2394 resultout := 'COMPLETE:';
2395 return;
2396 end if;
2397
2398 exception
2399 when others then
2400 errbuf := sqlerrm;
2401 wf_core.Context
2402 (g_package, 'remove_defunct_process',
2403 itemtype, itemkey, to_char(actid), funcmode);
2404 raise;
2405 --
2406 --
2407 end remove_defunct_process;
2408
2409 --
2410 -- ----------------------------------------------------------------------------
2411 -- |------------------------------<start_cleanup_process>--------------------------|
2412 -- ----------------------------------------------------------------------------
2413 procedure start_cleanup_process
2414 (p_item_type in wf_items.item_type%type
2415 ,p_transaction_age in wf_item_attribute_values.number_value%type
2416 ,p_process_name in wf_process_activities.process_name%type default 'HR_BACKGROUND_CLEANUP_PRC',
2417 p_transaction_status in varchar2 default 'ALL'
2418 ) is
2419 --
2420 l_process_name wf_process_activities.process_name%type := upper(p_process_name);
2421 l_item_type wf_items.item_type%type := upper(p_item_type);
2422 l_item_key wf_items.item_key%type;
2423 --
2424 --
2425 begin
2426 -- --------------------------------------------------------------------------
2427 -- check if the p_transaction_age has value
2428 --
2429 if (p_transaction_age is NULL) then
2430 hr_utility.set_message(800,'HR_NULL_TRANSACTION_AGE');
2431 hr_utility.raise_error;
2432 return;
2433 end if;
2434
2435
2436
2437 -- Determine if the specified process is runnable
2438 if NOT wf_process_runnable
2439 (p_item_type => l_item_type
2440 ,p_process_name => l_process_name) then
2441 -- supply HR error message, p_process_name either does not exist or
2442 -- is NOT a runnable process
2443 hr_utility.set_message(800,'HR_52958_WKF2TSK_INC_PROCESS');
2444 hr_utility.set_message_token('ITEM_TYPE', l_item_type);
2445 hr_utility.set_message_token('PROCESS_NAME', p_process_name);
2446 hr_utility.raise_error;
2447 end if;
2448 -- Get the next item key from the sequence
2449 select hr_workflow_item_key_s.nextval
2450 into l_item_key
2451 from sys.dual;
2452
2453
2454
2455 -- Create the Workflow Process
2456 wf_engine.CreateProcess
2457 (itemtype => l_item_type
2458 ,itemkey => l_item_key
2459 ,process => l_process_name);
2460 -- set the user key
2461 wf_engine.SetItemUserKey(itemtype=> l_item_type,
2462 itemkey => l_item_key,
2463 userkey => l_item_type);
2464
2465 -- add run time attribute for storing the transaction age.
2466 -- check if the attribute exists.
2467 if not item_attribute_exists
2468 (p_item_type => l_item_type
2469 ,p_item_key => l_item_key
2470 ,p_name => 'HR_TRANS_AGE_FOR_DEL_ATTR') then
2471 wf_engine.additemattr
2472 (itemtype => l_item_type
2473 ,itemkey => l_item_key
2474 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR');
2475 end if;
2476
2477 wf_engine.setitemattrnumber
2478 (itemtype => l_item_type
2479 ,itemkey => l_item_key
2480 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR'
2481 ,avalue => p_transaction_age);
2482
2483 -- add run time attribute for storing the transaction status.
2484 -- check if the attribute exists.
2485 if not item_attribute_exists
2486 (p_item_type => l_item_type
2487 ,p_item_key => l_item_key
2488 ,p_name => 'HR_TRAN_STAT_FOR_DEL_ATTR') then
2489 wf_engine.additemattr
2490 (itemtype => l_item_type
2491 ,itemkey => l_item_key
2492 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR');
2493 end if;
2494
2495 wf_engine.setitemattrText
2496 (itemtype => l_item_type
2497 ,itemkey => l_item_key
2498 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR'
2499 ,avalue => p_transaction_status);
2500
2501
2502 -- Start the WF runtime process
2503 wf_engine.startprocess
2504 (itemtype => l_item_type
2505 ,itemkey => l_item_key);
2506
2507
2508
2509 exception
2510 when others then
2511 Wf_Core.Context('hr_workflow_service', 'start_cleanup_process', l_item_type, l_item_key, p_transaction_age);
2512 raise;
2513 --
2514 end start_cleanup_process;
2515
2516 --
2517
2518 -- Block
2519 -- Stop and wait for external completion
2520 -- OUT
2521 -- result - NOTIFIED
2522 procedure Block(itemtype in varchar2,
2523 itemkey in varchar2,
2524 actid in number,
2525 funcmode in varchar2,
2526 resultout in out nocopy varchar2)
2527 is
2528 begin
2529 -- Do nothing in cancel or timeout mode
2530 if (funcmode <> wf_engine.eng_run) then
2531 resultout := wf_engine.eng_null;
2532 return;
2533 end if;
2534
2535 hr_transaction_api.Set_Process_Order_String(p_item_type => itemtype
2536 ,p_item_key => itemkey
2537 ,p_actid => actid);
2538
2539 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
2540 ':'||wf_engine.eng_null;
2541 exception
2542 when others then
2543 Wf_Core.Context('hr_workflow_service', 'Block', itemtype,
2544 itemkey, to_char(actid), funcmode);
2545 raise;
2546 end Block;
2547
2548 --
2549 -- TotalConcurrent
2550 -- Concurrent Program version
2551 -- IN:
2552 -- errbuf - CPM error message
2553 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
2554 -- itemtype - Item type to delete, or null for all itemtypes
2555 -- age - Minimum age of data to purge (in days)
2556 -- p_process_name default cleanup process name.
2557 -- transaction_status , the status of all the Transactions to be cleaned.
2558
2559 procedure TotalConcurrent(
2560 errbuf out nocopy varchar2,
2561 retcode out nocopy varchar2,
2562 itemtype in varchar2 default null,
2563 age in varchar2 default '0',
2564 p_process_name in varchar2 default 'HR_BACKGROUND_CLEANUP_PRC',
2565 transaction_status in varchar2 default 'ALL')
2566
2567 is
2568 errname varchar2(30);
2569 errmsg varchar2(2000);
2570 errstack varchar2(2000);
2571
2572 begin
2573 start_cleanup_process(p_item_type =>itemtype
2574 ,p_transaction_age =>age
2575 ,p_process_name => p_process_name
2576 ,p_transaction_status=>transaction_status);
2577
2578 errbuf := '';
2579 retcode := '0';
2580 exception
2581 when others then
2582 -- Retrieve error message into errbuf
2583 wf_core.get_error(errname, errmsg, errstack);
2584 if (errmsg is not null) then
2585 errbuf := errmsg;
2586 else
2587 errbuf := sqlerrm;
2588 end if;
2589 -- Return 2 for error.
2590 retcode := '2';
2591 end TotalConcurrent;
2592
2593
2594 function getItemType(p_transaction_id in hr_api_transactions.transaction_id%type)
2595 return wf_items.item_type%type is
2596 l_item_type wf_items.item_type%type;
2597 begin
2598 select ts.item_type
2599 into getItemType.l_item_type
2600 from hr_api_transaction_steps ts
2601 where ts.transaction_id=getItemType.p_transaction_id
2602 and ts.item_type is not null and rownum <=1;
2603 return getItemType.l_item_type;
2604 end getItemType;
2605
2606 function getItemKey(p_transaction_id in hr_api_transactions.transaction_id%type)
2607 return wf_items.item_key%type is
2608 l_item_key wf_items.item_key%type;
2609 begin
2610 select ts.item_key
2611 into getItemkey.l_item_key
2612 from hr_api_transaction_steps ts
2613 where getItemkey.p_transaction_id = ts.transaction_id
2614 and ts.item_key is not null and rownum <=1;
2615 return getItemkey.l_item_key;
2616 end getItemKey;
2617
2618 --
2619 END hr_workflow_service;
[Home] [Help]