1 PACKAGE BODY hr_workflow_service as
2 /* $Header: hrwfserv.pkb 120.10.12020000.2 2012/07/05 00:15:12 amnaraya 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')
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
2019 --Bug fix 8358911
2020 --For new architecture
2021 cursor csr_wfdfctitms_newarch (c_transaction_age in number, c_status in varchar2) is
2022 select * from (select a.transaction_id transaction_id,
2023 status
2024 from (select transaction_id,
2025 status,
2026 nvl(item_type,hr_workflow_service.getItemType(t.transaction_id)) item_type,
2027 nvl(item_key,hr_workflow_service.getItemKey(t.transaction_id)) item_key
2028 from hr_api_transactions t
2029 where t.last_update_date <= sysdate - c_transaction_age
2030 and t.status not in ('Y', 'YS')
2031 and t.transaction_ref_table <> 'PER_APPRAISALS'
2032 and t.item_key is null
2033 ) a
2034 where a.item_type = itemtype)
2035 where status = nvl(c_status,status);
2036
2037 --Bug fix 8358911 ends
2038
2039
2040 -- start bug 5990955
2041
2042 --Only the appraisals with a System status (appraisal_system_status in
2043 -- PER_APPRAISALS) as "COMPLETED" or "DELETED", irrespective of status in
2044 -- HR_API_TRANSACTIONS table will be deleted. So looking for the
2045 -- APPRAISAL_SYSTEM_STATUS in the cursor
2046
2047 CURSOR csr_wfdfctitmsapprs (c_transaction_age in number) is
2048 SELECT t.item_type, t.item_key, t.transaction_id
2049 FROM hr_api_transactions t, per_appraisals a
2050 WHERE t.transaction_ref_table = 'PER_APPRAISALS'
2051 AND t.last_update_date <= sysdate - c_transaction_age
2052 AND t.transaction_ref_id = a.appraisal_id
2053 AND a.appraisal_system_status IN ('DELETED','COMPLETED');
2054
2055 -- end bug 5990955
2056
2057 -- csr_wfdfctrans check if the Itemkey is present in the
2058 -- hr_api_transaction_steps table
2059 cursor csr_wfdfctrans is
2060 select hats.transaction_id
2061 from hr_api_transaction_steps hats
2062 where hats.item_type = itemtype
2063 and hats.item_key = l_item_key;
2064
2065 --
2066 -- to fetch orphan records caused by wf tables getting purged before
2067 -- executing this defunct process
2068 --
2069 CURSOR defunct_tx_ids IS
2070 /* SELECT distinct hats.transaction_id
2071 FROM hr_api_transaction_steps hats
2072 WHERE NOT EXISTS (SELECT 'Y'
2073 FROM wf_items wi
2074 WHERE wi.item_type = hats.item_type
2075 AND wi.item_key = hats.item_key);
2076 */
2077 -- Fix for bug#3322644
2078 SELECT hat.transaction_id
2079 FROM hr_api_transactions hat
2080 WHERE NOT EXISTS (SELECT 'Y'
2081 FROM wf_items wi
2082 WHERE wi.item_type = nvl(hat.item_type,hr_workflow_service.getItemType(hat.transaction_id))
2083 AND wi.item_key = nvl(hat.item_key,hr_workflow_service.getItemKey(hat.transaction_id))
2084 )
2085 AND Not Exists( select 'Y' from wf_items w --fix for bug 6121860
2086 where w.item_type = 'HRSFL'
2087 and w.user_key = hat.transaction_id)
2088 and hat.item_key is not null;
2089 --
2090 -- to fetch orphan records from wf tables which do not have an entry
2091 -- in the hr_api_transactions. This could cause from SSHR transaction
2092 -- not using hr_api_transactions and for WF process started without
2093 -- an entry to the hr_api_transactions. Possible causes, user started
2094 -- new transaction and closed the web browser.
2095
2096 -- fix for bug#2838117
2097 -- fetch all the records from wf_items for given item type which
2098 -- not in hr_api_transactions and icx session is disabled or does
2099 -- not exist.
2100
2101 -- 4287117
2102 --5076290
2103 CURSOR defunct_wf_ids (c_transaction_age in number) IS
2104 select wi.item_key
2105 from wf_items wi , wf_item_attribute_values av, icx_sessions s
2106 where wi.item_type= itemtype
2107 and trunc(wi.begin_date) <= trunc(sysdate) --fix for bug 6642996
2108 and wi.end_date is null
2109 and av.item_type = wi.item_type
2110 and av.item_key = wi.item_key
2111 and av.name = 'SESSION_ID'
2112 and av.number_value = s.session_id(+)
2113 and s.disabled_flag(+) = 'Y'
2114 and (
2115 (wi.item_key) not in (
2116 select t1.item_key
2117 from hr_api_transactions t1
2118 where wi.item_type = t1.item_type
2119 and wi.item_key = t1.item_key
2120 and t1.item_type = itemtype
2121 )
2122 and (wi.item_key) not in (
2123 select ts.item_key
2124 from hr_api_transaction_steps ts
2125 where ts.item_type = wi.item_type
2126 and ts.item_key = wi.item_key
2127 and ts.item_type = itemtype
2128 )
2129 );
2130 --5076290
2131 /* CURSOR defunct_wf_ids IS
2132 select wi.item_key
2133 from wf_items wi
2134 where wi.item_type = itemtype
2135 and wi.begin_date <= trunc(sysdate)
2136 and wi.end_date is null
2137 and not exists (select 'e' from hr_api_transactions t
2138 where
2139 t.item_type is not null
2140 and t.item_key is not null
2141 and wi.item_type = t.item_type
2142 and wi.item_key = t.item_key
2143 )
2144 and not exists (select 'e' from hr_api_transactions t, hr_api_transaction_steps ts
2145 where
2146 t.item_type is null
2147 and t.item_key is null
2148 and t.transaction_id = ts.transaction_id
2149 and wi.item_type = ts.item_type
2150 and wi.item_key = ts.item_key
2151 and ts.item_type is not null
2152 and ts.item_key is not null
2153 )
2154 and exists (select 'e' from wf_item_attribute_values av, icx_sessions s
2155 where av.item_type = wi.item_type
2156 and av.item_key = wi.item_key
2157 and av.name = 'SESSION_ID'
2158 and av.number_value = s.session_id(+)
2159 and s.disabled_flag(+) = 'Y');
2160 */
2161
2162 /* Cursor to identify if there is an offer to be closed*/
2163 cursor csrOfferDetails(c_transaction_id in number) is
2164 select hat.transaction_ref_id,
2165 hat.assignment_id,
2166 iof.offer_status
2167 from hr_api_transactions hat,
2168 irc_offers iof
2169 where hat.transaction_ref_table='IRC_OFFERS' and
2170 hat.transaction_ref_id = iof.offer_id and
2171 hat.transaction_id = c_transaction_id and
2172 iof.offer_status in ('PENDING','CORRECTION');
2173
2174 -- Fix for bug 6501341
2175 CURSOR defunct_wfsfl_ids IS
2176 select wi.item_key
2177 from wf_items wi
2178 where wi.item_type = 'HRSFL'
2179 and not exists (select transaction_id from hr_api_transactions
2180 where transaction_id = wi.user_key);
2181 --
2182
2183 l_offer_id number;
2184 l_applicant_assignment_id number;
2185 l_offer_status varchar2(100);
2186 -- 4287117
2187
2188 --
2189 begin
2190 --
2191 -- Get the transaction status value of items to be deleted
2192 -- This is normally passed through the Concurrent program
2193 -- and populated into item attribute HR_TRANS_STATUS_FOR_DEL_ATTR
2194 l_transaction_status_to_delete := wf_engine.getitemattrText
2195 (itemtype => itemtype
2196 ,itemkey => itemkey
2197 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR');
2198 -- Get the transaction age value of items to be deleted
2199 -- This is normally passed through the Concurrent program
2200 -- and populated into item attribute 'HR_TRANS_AGE_FOR_DEL_ATTR'
2201 l_transaction_age:= wf_engine.getitemattrNumber
2202 (itemtype => itemtype
2203 ,itemkey => itemkey
2204 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR');
2205
2206 -- c_status
2207 if(l_transaction_status_to_delete='ALL') then
2208 c_status := null;
2209 else
2210 c_status := l_transaction_status_to_delete;
2211 end if;
2212
2213 if funcmode = 'RUN' then
2214 -- first purge all the orphan transaction records if any
2215 BEGIN
2216 for rec in defunct_tx_ids loop
2217 begin
2218 hr_utility.set_location('START : Processing defunct transaction ID : '||rec.transaction_id,400);
2219
2220 l_offer_id := null;
2221 l_applicant_assignment_id := null;
2222 l_offer_status := null;
2223
2224 open csrOfferDetails(rec.transaction_id);
2225 fetch csrOfferDetails into l_offer_id, l_applicant_assignment_id,l_offer_status;
2226 close csrOfferDetails;
2227
2228 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id);
2229
2230 begin
2231 /* To check if it is the approval process for an offer. If yes, close the offer*/
2232 if l_offer_id is not null then
2233
2234 hr_utility.set_location ('Closing Offer...',415);
2235 hr_utility.set_location ('Offer ID : '||l_offer_id,430);
2236 hr_utility.set_location ('Applicant Assignment ID : '||l_applicant_assignment_id,445);
2237 hr_utility.set_location ('Offer Status : '||l_offer_status,460);
2238
2239 irc_offers_api.close_offer(p_effective_date => sysdate
2240 , p_applicant_assignment_id => l_applicant_assignment_id
2241 , p_offer_id => l_offer_id
2242 , p_change_reason => 'MANUAL_CLOSURE'
2243 , p_note_text => 'Closed from Complete Defunct Workflow Process');
2244 end if;
2245 --
2246 hr_utility.set_location ('Offer successfully closed ',480);
2247 exception
2248 when others then
2249 hr_utility.set_location ('Error occurred while closing offer : '||substr(SQLERRM,1,2000),500);
2250 end;
2251 exception
2252 when others then
2253 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_tx_ids' );
2254 end;
2255 hr_utility.set_location('END : Processing defunct transaction ID : '||rec.transaction_id,400);
2256 end loop;
2257 exception
2258 when others then
2259 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_tx_ids loop' );
2260 end;
2261
2262
2263 -- fix for bug#2838117
2264 BEGIN
2265 for rec in defunct_wf_ids (l_transaction_age) loop
2266 BEGIN
2267 begin
2268 select TEXT_VALUE
2269 into l_transaction_status
2270 from WF_ITEM_ATTRIBUTE_VALUES
2271 where ITEM_TYPE = itemtype
2272 and ITEM_KEY = rec.item_key
2273 and NAME = 'TRAN_SUBMIT';
2274 exception
2275 when no_data_found then
2276 l_transaction_status := null;
2277 end;
2278 -- reset the l_delete_transaction status false by default
2279 l_delete_transaction:= false;
2280 -- check if the current transaction can deleted
2281 if(l_transaction_status='Y' or l_transaction_status='YS') then -- bug 3635925
2282 l_delete_transaction := false;
2283 else
2284 l_delete_transaction := true;
2285 end if;
2286
2287 if(l_delete_transaction) then -- delete transaction which explicitly
2288 -- identified for delete.
2289 -- First abort the WF process for this transaction record.
2290 BEGIN -- Block to 'abort' the WF process
2291 wf_engine.abortprocess(itemtype => itemtype
2292 ,itemkey => rec.item_key
2293 ,result => 'eng_force');
2294 EXCEPTION
2295 when others then
2296 wf_core.Context(g_package, 'remove_defunct_process',
2297 itemtype, itemkey, to_char(actid),
2298 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2299 END;-- end of Block to 'abort' the WF process
2300 end if;
2301 EXCEPTION
2302 when others then
2303 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_wf_ids' );
2304 END; -- end of block for loop
2305 end loop;
2306 EXCEPTION
2307 when others then
2308 wf_core.Context(g_package, 'remove_defunct_process : Error Running defunct_wf_ids loop' );
2309 END; -- end block for defunct_wf_ids
2310
2311
2312
2313 for rec in csr_wfdfctitms(l_transaction_age,c_status) loop
2314 BEGIN -- inner block to catch exception for the transaction being processed.
2315 -- check the transaction status.
2316 -- we will delete the transaction data related to status
2317 -- l_transaction_status_to_delete
2318 -- All transactions with status 'Y' will not be touched by this program
2319
2320 if rec.item_key IS NOT NULL then
2321 -- Get the transaction status from the WF item attribute TRAN_SUBMIT
2322 -- need to remove this call once the SSHR V4.1 functionality is implemented.
2323 l_transaction_status :=wf_engine.GetItemAttrText(itemtype => itemtype
2324 ,itemkey => rec.item_key
2325 ,aname => 'TRAN_SUBMIT');
2326
2327 -- reset the l_delete_transaction status false by default
2328 l_delete_transaction:= false;
2329 -- check if the current transaction can deleted
2330 if(l_transaction_status='Y' or l_transaction_status='YS') then -- bug 3635925
2331 l_delete_transaction := false;
2332 elsif(l_transaction_status_to_delete='ALL') then
2333 l_delete_transaction := true;
2334 elsif (l_transaction_status_to_delete=l_transaction_status) then
2335 l_delete_transaction := true;
2336 end if;
2337
2338
2339
2340 if(l_delete_transaction) then -- delete transaction which explicitly
2341 -- identified for delete.
2342 -- First abort the WF process for this transaction record.
2343 BEGIN -- Block to 'abort' the WF process
2344 wf_engine.abortprocess(itemtype => itemtype
2345 ,itemkey => rec.item_key
2346 ,result => 'eng_force');
2347 exception
2348 when others then
2349 wf_core.Context(g_package, 'remove_defunct_process',
2350 itemtype, itemkey, to_char(actid),
2351 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2352 END;
2353
2354 BEGIN -- Block to 'purge' the data in the SSHR transaction tables.
2355 -- get the transaction for this transaction from workflow
2356 -- TRANSACTION_ID
2357 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id );
2358 exception
2359 when others then
2360 wf_core.Context(g_package, 'remove_defunct_process',
2361 itemtype, itemkey, to_char(actid),
2362 funcmode,'Record item key being processed was :'||nvl(rec.item_key,''));
2363 END;
2364
2365
2366 end if; -- end of status check
2367 end if; -- end of check for item key.
2368 exception
2369 when others then
2370 wf_core.Context(g_package, 'remove_defunct_process',
2371 itemtype, itemkey, to_char(actid), funcmode,'Record item key being processed was :'||rec.item_key );
2372 END ;
2373
2374 end loop;
2375
2376
2377 --Bug fix 8358911
2378 for rec in csr_wfdfctitms_newarch(l_transaction_age,c_status) loop
2379 begin
2380 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id );
2381 exception
2382 when others then
2383 wf_core.Context(g_package, 'remove_defunct_process',
2384 itemtype, itemkey, to_char(actid),
2385 funcmode,'Record transaction id being processed was :'||nvl(rec.transaction_id,''));
2386 end;
2387 end loop;
2388 --Bug fix 8358911 Ends
2389
2390
2391 -- start bug 5990955
2392
2393 for rec in csr_wfdfctitmsapprs(l_transaction_age) loop
2394 BEGIN
2395 wf_engine.abortprocess(itemtype => rec.item_type
2396 ,itemkey => rec.item_key
2397 ,result => 'eng_force');
2398
2399 hr_transaction_api.rollback_transaction(p_transaction_id => rec.transaction_id );
2400
2401 exception
2402 when others then
2403 wf_core.Context(g_package, 'remove_defunct_process',
2404 itemtype, itemkey, to_char(actid),
2405 funcmode,'Record item key being processed was :'||nvl(rec.item_key,''));
2406 END;
2407 end loop;
2408
2409 -- end bug 5990955
2410
2411 -- Fix for bug 6501341
2412 BEGIN
2413 for rec in defunct_wfsfl_ids loop
2414 BEGIN -- Block to 'abort' the WF process
2415 wf_engine.abortprocess(itemtype => 'HRSFL'
2416 ,itemkey => rec.item_key
2417 ,result => 'eng_force');
2418 EXCEPTION
2419 when others then
2420 wf_core.Context(g_package, 'remove_defunct_process',
2421 'HRSFL', itemkey, to_char(actid),
2422 funcmode,'Record item key being processed was :'||nvl(rec.item_key,'') );
2423 END;-- end of Block to 'abort' the WF process
2424 end loop;
2425 END;
2426
2427 --
2428 resultout := 'COMPLETE:';
2429 return;
2430 end if;
2431 --
2432 if funcmode = 'CANCEL' then
2433 resultout := 'COMPLETE:';
2434 return;
2435 end if;
2436
2437 exception
2438 when others then
2439 errbuf := sqlerrm;
2440 wf_core.Context
2441 (g_package, 'remove_defunct_process',
2442 itemtype, itemkey, to_char(actid), funcmode);
2443 raise;
2444 --
2445 --
2446 end remove_defunct_process;
2447
2448 --
2449 -- ----------------------------------------------------------------------------
2450 -- |------------------------------<start_cleanup_process>--------------------------|
2451 -- ----------------------------------------------------------------------------
2452 procedure start_cleanup_process
2453 (p_item_type in wf_items.item_type%type
2454 ,p_transaction_age in wf_item_attribute_values.number_value%type
2455 ,p_process_name in wf_process_activities.process_name%type default 'HR_BACKGROUND_CLEANUP_PRC',
2456 p_transaction_status in varchar2 default 'ALL'
2457 ) is
2458 --
2459 l_process_name wf_process_activities.process_name%type := upper(p_process_name);
2460 l_item_type wf_items.item_type%type := upper(p_item_type);
2461 l_item_key wf_items.item_key%type;
2462 --
2463 --
2464 begin
2465 -- --------------------------------------------------------------------------
2466 -- check if the p_transaction_age has value
2467 --
2468 if (p_transaction_age is NULL) then
2469 hr_utility.set_message(800,'HR_NULL_TRANSACTION_AGE');
2470 hr_utility.raise_error;
2471 return;
2472 end if;
2473
2474
2475
2476 -- Determine if the specified process is runnable
2477 if NOT wf_process_runnable
2478 (p_item_type => l_item_type
2479 ,p_process_name => l_process_name) then
2480 -- supply HR error message, p_process_name either does not exist or
2481 -- is NOT a runnable process
2482 hr_utility.set_message(800,'HR_52958_WKF2TSK_INC_PROCESS');
2483 hr_utility.set_message_token('ITEM_TYPE', l_item_type);
2484 hr_utility.set_message_token('PROCESS_NAME', p_process_name);
2485 hr_utility.raise_error;
2486 end if;
2487 -- Get the next item key from the sequence
2488 select hr_workflow_item_key_s.nextval
2489 into l_item_key
2490 from sys.dual;
2491
2492
2493
2494 -- Create the Workflow Process
2495 wf_engine.CreateProcess
2496 (itemtype => l_item_type
2497 ,itemkey => l_item_key
2498 ,process => l_process_name);
2499 -- set the user key
2500 wf_engine.SetItemUserKey(itemtype=> l_item_type,
2501 itemkey => l_item_key,
2502 userkey => l_item_type);
2503
2504 -- add run time attribute for storing the transaction age.
2505 -- check if the attribute exists.
2506 if not item_attribute_exists
2507 (p_item_type => l_item_type
2508 ,p_item_key => l_item_key
2509 ,p_name => 'HR_TRANS_AGE_FOR_DEL_ATTR') then
2510 wf_engine.additemattr
2511 (itemtype => l_item_type
2512 ,itemkey => l_item_key
2513 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR');
2514 end if;
2515
2516 wf_engine.setitemattrnumber
2517 (itemtype => l_item_type
2518 ,itemkey => l_item_key
2519 ,aname => 'HR_TRANS_AGE_FOR_DEL_ATTR'
2520 ,avalue => p_transaction_age);
2521
2522 -- add run time attribute for storing the transaction status.
2523 -- check if the attribute exists.
2524 if not item_attribute_exists
2525 (p_item_type => l_item_type
2526 ,p_item_key => l_item_key
2527 ,p_name => 'HR_TRAN_STAT_FOR_DEL_ATTR') then
2528 wf_engine.additemattr
2529 (itemtype => l_item_type
2530 ,itemkey => l_item_key
2531 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR');
2532 end if;
2533
2534 wf_engine.setitemattrText
2535 (itemtype => l_item_type
2536 ,itemkey => l_item_key
2537 ,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR'
2538 ,avalue => p_transaction_status);
2539
2540
2541 -- Start the WF runtime process
2542 wf_engine.startprocess
2543 (itemtype => l_item_type
2544 ,itemkey => l_item_key);
2545
2546
2547
2548 exception
2549 when others then
2550 Wf_Core.Context('hr_workflow_service', 'start_cleanup_process', l_item_type, l_item_key, p_transaction_age);
2551 raise;
2552 --
2553 end start_cleanup_process;
2554
2555 --
2556
2557 -- Block
2558 -- Stop and wait for external completion
2559 -- OUT
2560 -- result - NOTIFIED
2561 procedure Block(itemtype in varchar2,
2562 itemkey in varchar2,
2563 actid in number,
2564 funcmode in varchar2,
2565 resultout in out nocopy varchar2)
2566 is
2567 begin
2568 -- Do nothing in cancel or timeout mode
2569 if (funcmode <> wf_engine.eng_run) then
2570 resultout := wf_engine.eng_null;
2571 return;
2572 end if;
2573
2574 hr_transaction_api.Set_Process_Order_String(p_item_type => itemtype
2575 ,p_item_key => itemkey
2576 ,p_actid => actid);
2577
2578 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
2579 ':'||wf_engine.eng_null;
2580 exception
2581 when others then
2582 Wf_Core.Context('hr_workflow_service', 'Block', itemtype,
2583 itemkey, to_char(actid), funcmode);
2584 raise;
2585 end Block;
2586
2587 --
2588 -- TotalConcurrent
2589 -- Concurrent Program version
2590 -- IN:
2591 -- errbuf - CPM error message
2592 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
2593 -- itemtype - Item type to delete, or null for all itemtypes
2594 -- age - Minimum age of data to purge (in days)
2595 -- p_process_name default cleanup process name.
2596 -- transaction_status , the status of all the Transactions to be cleaned.
2597
2598 procedure TotalConcurrent(
2599 errbuf out nocopy varchar2,
2600 retcode out nocopy varchar2,
2601 itemtype in varchar2 default null,
2602 age in varchar2 default '0',
2603 p_process_name in varchar2 default 'HR_BACKGROUND_CLEANUP_PRC',
2604 transaction_status in varchar2 default 'ALL')
2605
2606 is
2607 errname varchar2(30);
2608 errmsg varchar2(2000);
2609 errstack varchar2(2000);
2610
2611 begin
2612 start_cleanup_process(p_item_type =>itemtype
2613 ,p_transaction_age =>age
2614 ,p_process_name => p_process_name
2615 ,p_transaction_status=>transaction_status);
2616
2617 errbuf := '';
2618 retcode := '0';
2619 exception
2620 when others then
2621 -- Retrieve error message into errbuf
2622 wf_core.get_error(errname, errmsg, errstack);
2623 if (errmsg is not null) then
2624 errbuf := errmsg;
2625 else
2626 errbuf := sqlerrm;
2627 end if;
2628 -- Return 2 for error.
2629 retcode := '2';
2630 end TotalConcurrent;
2631
2632
2633 function getItemType(p_transaction_id in hr_api_transactions.transaction_id%type)
2634 return wf_items.item_type%type is
2635 l_item_type wf_items.item_type%type;
2636 begin
2637 select ts.item_type
2638 into getItemType.l_item_type
2639 from hr_api_transaction_steps ts
2640 where ts.transaction_id=getItemType.p_transaction_id
2641 and ts.item_type is not null and rownum <=1;
2642 return getItemType.l_item_type;
2643 end getItemType;
2644
2645 function getItemKey(p_transaction_id in hr_api_transactions.transaction_id%type)
2646 return wf_items.item_key%type is
2647 l_item_key wf_items.item_key%type;
2648 begin
2649 select ts.item_key
2650 into getItemkey.l_item_key
2651 from hr_api_transaction_steps ts
2652 where getItemkey.p_transaction_id = ts.transaction_id
2653 and ts.item_key is not null and rownum <=1;
2654 return getItemkey.l_item_key;
2655 end getItemKey;
2656
2657 --
2658 END hr_workflow_service;
[Home] [Help]