[Home] [Help]
PACKAGE BODY: APPS.HR_EMP_ERROR_UTILITY
Source
4 -- private package global declarations
1 package body hr_emp_error_utility as
2 /* $Header: hreruweb.pkb 120.2 2005/09/23 14:51:17 svittal noship $ */
3 -- ---------------------------------------------------------------------------
5 -- ---------------------------------------------------------------------------
6 g_package constant varchar2(31) := 'hr_emp_error_utility.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- private package procedure and function declarations
10 -- ---------------------------------------------------------------------------
11 --
12 -- ---------------------------------------------------------------------------
13 -- |---------------------< set_activity_status >-----------------------------|
14 -- ---------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- Procedure to set the status of the specified activity.
19 --
20 -- Pre-Requisities:
21 -- None
22 --
23 -- In Parameters:
24 -- p_item_type -> The internal name for the item type.
25 -- p_item_key -> A string that represents a primary key generated by
26 -- the application for the item type. The string uniquely
27 -- identifies the item within an item type.
28 -- p_actid -> Unique identifier for the activity.
29 -- p_status -> String to define the status of the activity.
30 --
31 -- Post Success:
32 --
33 -- Post Failure:
34 -- None.
35 --
36 -- Developer Implementation Notes:
37 -- None
38 --
39 -- Access Status:
40 -- Private
41 --
42 -- {End Of Comments}
43 -- ---------------------------------------------------------------------------
44 procedure set_activity_status
45 (p_item_type in varchar2
46 ,p_item_key in varchar2
47 ,p_actid in number
48 ,p_status in varchar2) is
49 --
50 l_textbuf varchar(2000);
51 l_numberbuf number;
52 l_datebuf date;
53 l_context varchar2(80);
54 --
55 begin
56 l_context := p_item_type||':'||p_item_key||':'||to_char(p_actid);
57 wf_engine.cb
58 (command => p_status
59 ,context => l_context
60 ,attr_name => null
61 ,attr_type => null
62 ,text_value => l_textbuf
63 ,number_value => l_numberbuf
64 ,date_value => l_datebuf);
65 end set_activity_status;
66 -- ---------------------------------------------------------------------------
67 -- |------------------< set_transact_step_messages >------------------------|
68 -- ---------------------------------------------------------------------------
69 -- {Start Of Comments}
70 --
71 -- Description:
72 -- Item attributes can not be deleted. If none exist, then creates one.
73 -- Otherwise at least one attribute is defined for the transaction step
74 -- so need to determine the first attribute which is defined to be null
75 --
76 -- Pre-Requisities:
77 -- None
78 --
79 -- In Parameters:
80 -- p_item_type -> The internal name for the item type.
81 -- p_item_key -> A string that represents a primary key generated by
82 -- the application for the item type. The string uniquely
83 -- identifies the item within an item type.
84 -- p_transaction_step_id -> transaction identifier for transaction
85 -- p_actid -> Unique identifier for the activity.
86 -- p_error_text -> String defining the contents of the error/warning messages.
87 -- p_type -> A string which accepts 'ERROR_TEXT' or 'WARNING_TEXT'
88 -- which specifies errors or warnings respectively.
89 --
90 -- Post Success:
91 -- Creates or updates error/warning text messages.
92 --
93 -- Post Failure:
94 -- None
95 --
96 -- Developer Implementation Notes:
97 -- None
98 --
99 -- Access Status:
100 -- Private
101 --
102 -- {End Of Comments}
103 -- ---------------------------------------------------------------------------
104 PROCEDURE set_transact_step_messages
105 (p_item_type IN wf_items.item_type%TYPE
106 ,p_item_key IN wf_items.item_key%TYPE
107 ,p_transaction_step_id IN NUMBER
108 ,p_error_text IN VARCHAR2
109 ,p_type IN varchar2) IS
110 -- --------------------------------------------------------------------------
111 -- local cursor definitions
112 -- --------------------------------------------------------------------------
113 -- csr_min_error_instance -> selects the minimum value of the error
114 -- /warning instance for the error
115 -- text item attributes for a given
116 -- transaction step id which is set to NULL
117 --
118 CURSOR csr_wf_count IS
119 select count(*)
120 from wf_item_attribute_values iav
121 where iav.item_type = p_item_type
122 and iav.item_key = p_item_key
123 and iav.name like p_type||':%';
124 CURSOR csr_wf_min_error_instance IS
125 select min(to_number(substr(iav.name
126 ,instr(iav.name,':',1,2) + 1)))
127 from wf_item_attribute_values iav
128 where iav.item_type = p_item_type
129 and iav.item_key = p_item_key
130 and iav.name like p_type||':%'
131 and to_number(substr(iav.name
132 ,instr(iav.name,':') + 1
133 ,instr(iav.name,':',1,2)
134 - instr(iav.name,':') - 1))
135 = p_transaction_step_id
136 and iav.text_value IS NULL;
137 -- ---------------------------------------------------------------------------
138 -- Local variable declerations
139 -- ---------------------------------------------------------------------------
140 l_proc_name varchar2(71) := g_package||'set_transact_step_messages';
141 l_error_instance number;
142 l_count number;
143 l_aname wf_item_attribute_values.name%TYPE :=
144 p_type||':'||to_char(p_transaction_step_id)||':';
145 BEGIN
146 -- -------------------------------------------------------------------------
147 -- Test to see if any item attributes exist
148 -- -------------------------------------------------------------------------
149 OPEN csr_wf_count;
150 FETCH csr_wf_count INTO l_count;
151 CLOSE csr_wf_count;
152 -- -------------------------------------------------------------------------
153 -- If no item attributes exist then create a new one
154 -- -------------------------------------------------------------------------
155 IF l_count = 0 THEN
156 -- Set l_transact_step_error_instance to 0
157 l_error_instance := 0;
158 --
159 l_aname := l_aname||to_char(l_error_instance);
160 -- Add the new item attribute
161 wf_engine.AddItemAttr
162 (itemtype => p_item_type
163 ,itemkey => p_item_key
164 ,aname => l_aname);
165 ELSE
166 -- -------------------------------------------------------------------------
167 -- At least one attribute is defined for the transaction step so
168 -- need to determine the first attribute which is defined to be null
169 -- If none exists we need to create a new one at count
170 -- -------------------------------------------------------------------------
171 OPEN csr_wf_min_error_instance;
172 FETCH csr_wf_min_error_instance INTO l_error_instance;
173 CLOSE csr_wf_min_error_instance;
174
175 IF l_error_instance IS NULL THEN
176 -- Create attribute with an instance of l_count
177 l_aname := l_aname||to_char(l_count);
178 -- Add the new item attribute
179 wf_engine.AddItemAttr
180 (itemtype => p_item_type
181 ,itemkey => p_item_key
182 ,aname => l_aname);
183 ELSE
184 l_aname := l_aname||to_char(l_error_instance);
185 END IF;
186
187 END IF;
188 -- -------------------------------------------------------------------------
189 -- Set the error text item attribute to p_error_text
190 -- -------------------------------------------------------------------------
191 wf_engine.SetItemAttrText
192 (itemtype => p_item_type
193 ,itemkey => p_item_key
194 ,aname => l_aname
195 ,avalue => p_error_text);
196 END set_transact_step_messages;
197 -- ---------------------------------------------------------------------------
198 -- |-----------------------< exists_messages >-----------------------------|
199 -- ---------------------------------------------------------------------------
200 -- {Start Of Comments}
201 --
202 -- Description:
203 -- Tests to see whether any errors or warnings exist.
204 --
205 -- Pre-Requisities:
206 -- None
207 --
208 -- In Parameters:
209 -- p_item_type -> The internal name for the item type.
210 -- p_item_key -> A string that represents a primary key generated by
211 -- the application for the item type. The string uniquely
212 -- identifies the item within an item type.
213 -- p_actid -> Unique identifier for the activity.
214 -- p_type -> A string which accepts 'ERROR_TEXT' or 'WARNING_TEXT'
215 -- which specifies errors or warnings respectively.
216 --
217 -- Post Success:
218 -- Returns a boolean of true if a message does exist and false if
219 -- does not.
220 --
221 -- Post Failure:
222 -- None.
223 --
224 -- Developer Implementation Notes:
225 -- None
226 --
227 -- Access Status:
228 -- Private.
229 --
230 -- {End Of Comments}
231 -- ---------------------------------------------------------------------------
232 FUNCTION exists_messages
233 (p_item_type IN wf_items.item_type%TYPE
234 ,p_item_key IN wf_items.item_key%TYPE
235 ,p_actid IN wf_process_activities.instance_id%TYPE
236 ,p_type IN varchar2) RETURN BOOLEAN IS
237 -- --------------------------------------------------------------------------
238 -- local cursor definitions
239 -- --------------------------------------------------------------------------
240 -- csr_wf_text -> selects the text for all transactions of an activity
241 --
242 CURSOR csr_wf_text is
243 -- fix for bug # 1577987
244 /*
245 Modified the cursor to use the PK of the table wf_item_attribute_values and
246 avoid a full table scan so as to enhance performance.
247 */
248 select iav.text_value
249 from hr_api_transaction_steps hats
250 ,wf_item_attribute_values iav
251 where iav.item_type = p_item_type
252 and iav.item_key = p_item_key
253 and iav.name like p_type||':%'
254 and hats.activity_id = p_actid
255 and hats.transaction_step_id =
256 to_number(substr(iav.name
257 ,instr(iav.name,':') + 1
258 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1));
259
260
261 /* select iav.text_value
262 from hr_api_transaction_steps hats
263 ,wf_item_attribute_values iav
264 where hats.activity_id = p_actid
265 and hats.item_type = p_item_type
266 and hats.item_key = p_item_key
267 and iav.name like p_type||':%'
268 and hats.transaction_step_id =
269 to_number(substr(iav.name
270 ,instr(iav.name,':') + 1
271 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1));
272 */
273 -- ---------------------------------------------------------------------------
274 -- Local variable declarations
275 -- ---------------------------------------------------------------------------
276 l_proc_name varchar2(61) := g_package||'exists_text';
280 -- otherwise return TRUE
277 l_exist_message BOOLEAN := FALSE;
278 BEGIN
279 -- Loop through all attributes. If any are null or none exist then return FALSE
281 FOR lcsr IN csr_wf_text LOOP
282 -- If the text_value is not NULL then set l_exist_text to TRUE
283 -- and leave the loop
284 IF lcsr.text_value IS NOT NULL THEN
285 l_exist_message := TRUE;
286 exit;
287 END IF;
288 END LOOP;
289 RETURN l_exist_message;
290 END exists_messages;
291 --
292 -- ---------------------------------------------------------------------------
293 -- |-----------------------< exists_messages >-----------------------------|
294 -- ---------------------------------------------------------------------------
295 -- {Start Of Comments}
296 --
297 -- Description:
298 -- Tests to see whether any errors or warnings exist.
299 --
300 -- Pre-Requisities:
301 -- None
302 --
303 -- In Parameters:
304 -- p_item_type -> The internal name for the item type.
305 -- p_item_key -> A string that represents a primary key generated by
306 -- the application for the item type. The string uniquely
307 -- identifies the item within an item type.
308 -- p_actid -> Unique identifier for the activity.
309 -- p_type -> A string which accepts 'ERROR_TEXT' or 'WARNING_TEXT'
310 -- which specifies errors or warnings respectively.
311 -- p_transaction_step_id -> transaction step id with in the activity
312 --
313 -- Post Success:
314 -- Returns a boolean of true if a message does exist and false if
315 -- does not.
316 --
317 -- Post Failure:
318 -- None.
319 --
320 -- Developer Implementation Notes:
321 -- None
322 --
323 -- Access Status:
324 -- Private.
325 --
326 -- {End Of Comments}
327 -- ---------------------------------------------------------------------------
328 FUNCTION exists_messages
329 (p_item_type IN wf_items.item_type%TYPE
330 ,p_item_key IN wf_items.item_key%TYPE
331 ,p_actid IN wf_process_activities.instance_id%TYPE
332 ,p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE
333 ,p_type IN varchar2) RETURN BOOLEAN IS
334 -- --------------------------------------------------------------------------
335 -- local cursor definitions
336 -- --------------------------------------------------------------------------
337 -- csr_wf_text -> selects the text for all transactions of an activity
338 --
339 CURSOR csr_wf_text is
340 -- fix for bug # 1577987
341 select iav.text_value
342 from hr_api_transaction_steps hats
343 ,wf_item_attribute_values iav
344 where iav.item_type = p_item_type
345 and iav.item_key = p_item_key
346 and iav.name like p_type||':%'
347 and hats.activity_id = p_actid
348 and hats.transaction_step_id = p_transaction_step_id
349 and hats.transaction_step_id =
350 to_number(substr(iav.name
351 ,instr(iav.name,':') + 1
352 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1));
353
354
355 /*select iav.text_value
356 from hr_api_transaction_steps hats
357 ,wf_item_attribute_values iav
358 where hats.activity_id = p_actid
359 and hats.item_type = p_item_type
360 and hats.item_key = p_item_key
361 and iav.name like p_type||':%'
362 and hats.transaction_step_id = p_transaction_step_id
363 and hats.transaction_step_id =
364 to_number(substr(iav.name
365 ,instr(iav.name,':') + 1
366 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1));
367 */
368 -- ---------------------------------------------------------------------------
369 -- Local variable declarations
370 -- ---------------------------------------------------------------------------
371 l_proc_name varchar2(61) := g_package||'exists_text';
372 l_exist_message BOOLEAN := FALSE;
373 BEGIN
374 -- Loop through all attributes. If any are null or none exist then return FALSE
375 -- otherwise return TRUE
376 FOR lcsr IN csr_wf_text LOOP
377 -- If the text_value is not NULL then set l_exist_text to TRUE
378 -- and leave the loop
379 IF lcsr.text_value IS NOT NULL THEN
380 l_exist_message := TRUE;
381 exit;
382 END IF;
383 END LOOP;
384 RETURN l_exist_message;
385 END exists_messages;
386 --
387 -- ---------------------------------------------------------------------------
388 -- public package procedure and function declarations
389 -- ---------------------------------------------------------------------------
390 --
391 -- ---------------------------------------------------------------------------
392 -- |-----------------------< raise_system_error >----------------------------|
393 -- ---------------------------------------------------------------------------
394 procedure raise_system_error
395 (p_item_type in varchar2
396 ,p_item_key in varchar2
397 ,p_actid in number
398 ,p_set_error_status in boolean default true) is
399 --
400 begin
401 if p_set_error_status then
402 -- set the activity to an ERROR status
403 set_activity_status
404 (p_item_type => p_item_type
405 ,p_item_key => p_item_key
406 ,p_actid => p_actid
407 ,p_status => 'ERROR');
408 end if;
409 end raise_system_error;
410 -- ---------------------------------------------------------------------------
411 -- |---------------------< abort_process_in_error >--------------------------|
412 -- ---------------------------------------------------------------------------
413 procedure abort_process_in_error
414 (itemtype in varchar2
415 ,itemkey in varchar2
416 ,actid in number
417 ,funmode in varchar2
418 ,result out nocopy varchar2) is
419 --
420 l_error_item_type varchar2(8);
421 l_error_item_key varchar2(240);
422 l_result varchar2(1);
423 --
424 begin
425 if funmode = 'RUN' then
426 -- get the error item type and key
427 l_error_item_type := wf_engine.getitemattrtext
428 (itemtype => itemtype
429 ,itemkey => itemkey
430 ,aname => 'ERROR_ITEM_TYPE');
431 l_error_item_key := wf_engine.getitemattrtext
432 (itemtype => itemtype
433 ,itemkey => itemkey
434 ,aname => 'ERROR_ITEM_KEY');
435 -- abort the process which is in error
436 wf_engine.abortprocess
437 (itemtype => l_error_item_type
438 ,itemkey => l_error_item_key
439 ,result => wf_engine.eng_exception);
440 --
441 result := 'SUCCESS';
442 elsif funmode = 'CANCEL' then
443 null;
444 end if;
445 exception
446 when others then
447 result := 'COMPLETE:ERROR';
448 end abort_process_in_error;
449 -- ---------------------------------------------------------------------------
450 -- |------------------------< reset workflow >-------------------------------|
451 -- ---------------------------------------------------------------------------
452 procedure reset_workflow
453 (itemtype in varchar2
454 ,itemkey in varchar2
455 ,actid in number
456 ,funmode in varchar2
457 ,result out nocopy varchar2) is
458 -- ---------------------------------------------------------------------------
459 -- Local variable declarations
460 -- ---------------------------------------------------------------------------
461 l_error_item_type varchar2(8) :=
462 wf_engine.getitemattrtext
463 (itemtype => itemtype
464 ,itemkey => itemkey
465 ,aname => 'ERROR_ITEM_TYPE') ;
466 l_error_item_key varchar2(240) :=
467 wf_engine.getitemattrtext
468 (itemtype => itemtype
469 ,itemkey => itemkey
470 ,aname => 'ERROR_ITEM_KEY');
471 l_error_actid number :=
472 wf_engine.getitemattrnumber
473 (itemtype => itemtype
474 ,itemkey => itemkey
475 ,aname => 'ERROR_ACTIVITY_ID');
476 --
477 l_activity_status varchar2(80);
478 l_error_instance_label wf_process_activities.instance_label%TYPE;
479 --
480 begin
481 if funmode = 'RUN' then
482 l_error_instance_label := hr_workflow_utility.get_activity_instance_label
483 (p_actid => l_error_actid);
484 wf_engine.handleerror
485 (itemtype => l_error_item_type
486 ,itemkey => l_error_item_key
487 ,activity => l_error_instance_label
488 ,command => 'RETRY');
489 -- we need to determine if the activity is still in
490 -- error
491 wf_item_activity_status.status
492 (itemtype => l_error_item_type
493 ,itemkey => l_error_item_key
494 ,actid => l_error_actid
495 ,status => l_activity_status);
496 --
497 if l_activity_status <> 'ERROR' then
498 -- as the error has been handled we need to reset the
499 -- SYSTEM_ERROR_ACTID to null.
500 wf_engine.setitemattrnumber
501 (itemtype => l_error_item_type
502 ,itemkey => l_error_item_key
503 ,aname => 'SYSTEM_ERROR_ACTID'
504 ,avalue => null);
505 end if;
506 elsif funmode = 'CANCEL' then
507 null;
508 end if;
512 end reset_workflow;
509 exception
510 when others then
511 raise;
513 -- ---------------------------------------------------------------------------
514 -- |--------------------< initialise_system_error >--------------------------|
515 -- ---------------------------------------------------------------------------
516 procedure initialise_system_error
517 (itemtype in varchar2
518 ,itemkey in varchar2
519 ,actid in number
520 ,funmode in varchar2
521 ,result out nocopy varchar2) is
522 --
523 l_error_person_display_name wf_users.display_name%type;
524 l_error_person_username wf_users.name%type;
525 --
526 l_error_item_type varchar2(8) :=
527 wf_engine.getitemattrtext
528 (itemtype => itemtype
529 ,itemkey => itemkey
530 ,aname => 'ERROR_ITEM_TYPE') ;
531 l_error_item_key varchar2(240) :=
532 wf_engine.getitemattrtext
533 (itemtype => itemtype
534 ,itemkey => itemkey
535 ,aname => 'ERROR_ITEM_KEY');
536 l_error_actid number :=
537 wf_engine.getitemattrnumber
538 (itemtype => itemtype
539 ,itemkey => itemkey
540 ,aname => 'ERROR_ACTIVITY_ID');
541 l_person_id per_people_f.person_id%type :=
542 wf_engine.getitemattrnumber
543 (itemtype => l_error_item_type
544 ,itemkey => l_error_item_key
545 ,aname => 'CURRENT_PERSON_ID');
546 --
547 begin
548 if funmode = 'RUN' then
549 -- ------------------------------------------------------------------------
550 -- set the ERROR_PERSON_ID item attribute
551 -- ------------------------------------------------------------------------
552 -- check to see if the item attribute exists
553 if not hr_workflow_utility.item_attribute_exists
554 (p_item_type => itemtype
555 ,p_item_key => itemkey
556 ,p_name => 'ERROR_PERSON_ID') then
557 -- the ERROR_PERSON_ID does not exist so create it
558 wf_engine.additemattr
559 (itemtype => itemtype
560 ,itemkey => itemkey
561 ,aname => 'ERROR_PERSON_ID');
562 end if;
563 -- set the ERROR_PERSON_ID attribute value
564 wf_engine.setitemattrnumber
565 (itemtype => itemtype
566 ,itemkey => itemkey
567 ,aname => 'ERROR_PERSON_ID'
568 ,avalue => l_person_id);
569 -- ------------------------------------------------------------------------
570 -- set the ERROR_PERSON_USERNAME item attribute
571 -- ------------------------------------------------------------------------
572 -- get the username from workflow by calling the directory services
573 wf_directory.getusername
574 (p_orig_system => 'PER'
575 ,p_orig_system_id => l_person_id
576 ,p_name => l_error_person_username
577 ,p_display_name => l_error_person_display_name);
578 -- check to see if the item attribute exists
579 if not hr_workflow_utility.item_attribute_exists
580 (p_item_type => itemtype
581 ,p_item_key => itemkey
582 ,p_name => 'ERROR_PERSON_USERNAME') then
583 -- the ERROR_PERSON_USERNAME does not exist so create it
584 wf_engine.additemattr
585 (itemtype => itemtype
586 ,itemkey => itemkey
587 ,aname => 'ERROR_PERSON_USERNAME');
588 end if;
589 -- set the ERROR_PERSON_USERNAME attribute value
590 wf_engine.setitemattrtext
591 (itemtype => itemtype
592 ,itemkey => itemkey
593 ,aname => 'ERROR_PERSON_USERNAME'
594 ,avalue => l_error_person_username);
595 -- ------------------------------------------------------------------------
596 -- set the TRANSACTION_ID item attribute
597 -- ------------------------------------------------------------------------
598 -- check to see if the item attribute exists
599 if not hr_workflow_utility.item_attribute_exists
600 (p_item_type => itemtype
601 ,p_item_key => itemkey
602 ,p_name => 'TRANSACTION_ID') then
603 -- the TRANSACTION_ID does not exist so create it
604 wf_engine.additemattr
605 (itemtype => itemtype
606 ,itemkey => itemkey
607 ,aname => 'TRANSACTION_ID');
608 end if;
609 -- set the TRANSACTION_ID item attribute value
610 wf_engine.setitemattrnumber
611 (itemtype => itemtype
612 ,itemkey => itemkey
613 ,aname => 'TRANSACTION_ID'
614 ,avalue => hr_transaction_ss.get_transaction_id
615 (p_item_type => l_error_item_type
616 ,p_item_key => l_error_item_key));
617 -- ------------------------------------------------------------------------
618 -- set the SYSTEM_ERROR_ACTID item attribute
619 -- ------------------------------------------------------------------------
620 -- set the item attribute SYSTEM_ERROR_ACTID to the
621 -- current activity
622 -- check to see if the item attribute exists
623 if not hr_workflow_utility.item_attribute_exists
624 (p_item_type => l_error_item_type
625 ,p_item_key => l_error_item_key
626 ,p_name => 'SYSTEM_ERROR_ACTID') then
627 -- the SYSTEM_ERROR_ACTID does not exist so create it
628 wf_engine.additemattr
629 (itemtype => l_error_item_type
630 ,itemkey => l_error_item_key
631 ,aname => 'SYSTEM_ERROR_ACTID');
632 end if;
633 -- set the SYSTEM_ERROR_ACTID attribute value
634 wf_engine.setitemattrnumber
635 (itemtype => l_error_item_type
636 ,itemkey => l_error_item_key
637 ,aname => 'SYSTEM_ERROR_ACTID'
638 ,avalue => l_error_actid);
639 --
640 result := 'COMPLETE';
641 --
642 elsif funmode = 'CANCEL' then
643 null;
644 end if;
645 --
646 end initialise_system_error;
647 -- ---------------------------------------------------------------------------
648 -- |------------------< clr_activity_messages >----------------------|
649 -- ---------------------------------------------------------------------------
650 PROCEDURE clr_activity_messages
651 (p_item_type IN wf_items.item_type%TYPE
652 ,p_item_key IN wf_items.item_key%TYPE
653 ,p_actid IN wf_process_activities.instance_id%TYPE) IS
654 -- --------------------------------------------------------------------------
655 -- local cursor definitions
656 -- --------------------------------------------------------------------------
657 -- csr_wf_attribute_name -> selects the name of all the
658 -- error/warning text item attributes
659 -- for a given activity
660 --
661 CURSOR csr_wf_attribute_name IS
662 SELECT iav.name
663 FROM wf_item_attribute_values iav,
664 hr_api_transaction_steps hats
665 WHERE iav.item_type = hats.item_type
666 AND iav.item_key = hats.item_key
667 AND (iav.name LIKE 'ERROR_TEXT:%'
668 OR iav.name LIKE 'WARNING_TEXT:%')
669 AND TO_NUMBER(SUBSTR(iav.name,
670 INSTR (iav.name, ':') + 1,
671 INSTR (iav.name, ':', 1, 2) - INSTR (iav.name, ':') - 1)) =
672 hats.transaction_step_id
673 AND hats.item_type = p_item_type
674 AND hats.item_key = p_item_key
675 AND hats.activity_id = p_actid;
676
677 /*CURSOR csr_wf_attribute_name IS
678 select iav.name
679 from wf_item_attribute_values iav
680 where iav.item_type = p_item_type
681 and iav.item_key = p_item_key
682 and iav.name like 'ERROR_TEXT'||':%'
683 or iav.name like 'WARNING_TEXT'||':%'
684 and to_number(
685 substr(iav.name
686 ,instr(iav.name,':') + 1
687 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1
688 )
689 ) in
690 (select hats.transaction_step_id
691 from hr_api_transaction_steps hats
692 where hats.item_type = p_item_type
693 and hats.item_key = p_item_key
694 and hats.activity_id = p_actid);*/
695 -- ---------------------------------------------------------------------------
696 -- Local variable declerations
697 -- ---------------------------------------------------------------------------
698 l_proc_name varchar2(61) := g_package||'clr_activity_messages';
699 BEGIN
700 -- ---------------------------------------------------------------------------
701 -- Clear all error text for this activity by setting each to NULL
702 -- ---------------------------------------------------------------------------
703 FOR lcsr IN csr_wf_attribute_name LOOP
704 -- Clear current error_text attribute
705 wf_engine.SetItemAttrText
706 (itemtype => p_item_type
707 ,itemkey => p_item_key
708 ,aname => lcsr.name
709 ,avalue => null);
710 END LOOP;
711 END clr_activity_messages;
712 --
713 -- ---------------------------------------------------------------------------
714 -- |------------------< clr_transact_step_messages >----------------------|
715 -- ---------------------------------------------------------------------------
716 PROCEDURE clr_transact_step_messages
717 (p_item_type IN wf_items.item_type%TYPE
718 ,p_item_key IN wf_items.item_key%TYPE
719 ,p_transaction_step_id IN NUMBER) IS
720 -- --------------------------------------------------------------------------
721 -- local cursor definitions
722 -- --------------------------------------------------------------------------
723
724 -- csr_wf_attribute_name -> selects the name of the
725 -- error/warning text item attribute
726 -- for a given transaction step
727 --
728 CURSOR csr_wf_attribute_name IS
729 SELECT iav.name
730 FROM wf_item_attribute_values iav
731 WHERE iav.item_type = p_item_type
732 AND iav.item_key = p_item_key
733 AND (iav.name LIKE 'ERROR_TEXT:%'
734 OR iav.name LIKE 'WARNING_TEXT:%')
735 AND TO_NUMBER(SUBSTR(iav.name,
736 INSTR(iav.name, ':') + 1,
737 INSTR(iav.name, ':', 1, 2) - INSTR(iav.name, ':') - 1)) =
738 p_transaction_step_id;
739
740 /*CURSOR csr_wf_attribute_name IS
741 select iav.name
742 from wf_item_attribute_values iav
743 where iav.item_type = p_item_type
744 and iav.item_key = p_item_key
745 and iav.name like 'ERROR_TEXT'||':%'
746 or iav.name like 'WARNING_TEXT'||':%'
747 and to_number(
748 substr(iav.name
749 ,instr(iav.name,':') + 1
750 ,instr(iav.name,':',1,2) - instr(iav.name,':') - 1
751 )
752 ) = p_transaction_step_id; */
753 -- ---------------------------------------------------------------------------
754 -- Local variable declerations
755 -- ---------------------------------------------------------------------------
756 l_proc_name varchar2(61) := g_package||'clr_transact_step_messages';
757 BEGIN
758 -- ---------------------------------------------------------------------------
759 -- Clear all error text for this transaction step by setting each to NULL
760 -- ---------------------------------------------------------------------------
761 FOR lcsr IN csr_wf_attribute_name LOOP
762 -- Clear current error_text attribute
763 wf_engine.SetItemAttrText
764 (itemtype => p_item_type
765 ,itemkey => p_item_key
766 ,aname => lcsr.name
767 ,avalue => null);
768 END LOOP;
769 END clr_transact_step_messages;
770 -- ---------------------------------------------------------------------------
771 -- |------------------< set_transact_step_error_text >------------------------|
772 -- ---------------------------------------------------------------------------
773 PROCEDURE set_transact_step_error_text
774 (p_item_type IN wf_items.item_type%TYPE
775 ,p_item_key IN wf_items.item_key%TYPE
776 ,p_transaction_step_id IN NUMBER
777 ,p_error_text IN VARCHAR2) IS
778 -- ---------------------------------------------------------------------------
779 -- Local variable declerations
780 -- ---------------------------------------------------------------------------
781 l_proc_name varchar2(71) := g_package||'set_transact_step_error_text';
782 BEGIN
783 set_transact_step_messages
784 (p_item_type => p_item_type
785 ,p_item_key => p_item_key
786 ,p_transaction_step_id => p_transaction_step_id
787 ,p_error_text => p_error_text
788 ,p_type => 'ERROR_TEXT');
789 END set_transact_step_error_text;
790 --
791 -- ---------------------------------------------------------------------------
792 -- |-----------------------< exists_error_text >-----------------------------|
793 -- ---------------------------------------------------------------------------
794 FUNCTION exists_error_text
795 (p_item_type IN wf_items.item_type%TYPE
796 ,p_item_key IN wf_items.item_key%TYPE
797 ,p_actid IN wf_process_activities.instance_id%TYPE) RETURN BOOLEAN IS
798 -- ---------------------------------------------------------------------------
799 -- Local variable declarations
800 -- ---------------------------------------------------------------------------
801 l_proc_name varchar2(61) := g_package||'exists_error_text';
802 BEGIN
803 RETURN exists_messages
804 (p_item_type => p_item_type
805 ,p_item_key => p_item_key
806 ,p_actid => p_actid
807 ,p_type => 'ERROR_TEXT');
808 END exists_error_text;
809 --
810 FUNCTION exists_error_text
811 (p_item_type IN wf_items.item_type%TYPE
812 ,p_item_key IN wf_items.item_key%TYPE
813 ,p_actid IN wf_process_activities.instance_id%TYPE
814 ,p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE) RETURN BOOLEAN IS
815 -- ---------------------------------------------------------------------------
816 -- Local variable declarations
817 -- ---------------------------------------------------------------------------
818 l_proc_name varchar2(61) := g_package||'exists_error_text';
819 BEGIN
820 RETURN exists_messages
821 (p_item_type => p_item_type
822 ,p_item_key => p_item_key
823 ,p_actid => p_actid
824 ,p_transaction_step_id => p_transaction_step_id
825 ,p_type => 'ERROR_TEXT');
826 END exists_error_text;
827 --
828 -- ---------------------------------------------------------------------------
829 -- |------------------< set_transact_step_warning_text >------------------------|
830 -- ---------------------------------------------------------------------------
831 PROCEDURE set_transact_step_warning_text
832 (p_item_type IN wf_items.item_type%TYPE
833 ,p_item_key IN wf_items.item_key%TYPE
834 ,p_transaction_step_id IN NUMBER
835 ,p_error_text IN VARCHAR2) IS
836 -- ---------------------------------------------------------------------------
837 -- Local variable declerations
838 -- ---------------------------------------------------------------------------
839 l_proc_name varchar2(71) := g_package||'set_transact_step_warning_text';
840 BEGIN
841 set_transact_step_messages
842 (p_item_type => p_item_type
843 ,p_item_key => p_item_key
844 ,p_transaction_step_id => p_transaction_step_id
845 ,p_error_text => p_error_text
846 ,p_type => 'WARNING_TEXT');
847 END set_transact_step_warning_text;
848 --
849 -- ---------------------------------------------------------------------------
850 -- |-----------------------< exists_warning_text >----------------------------|
851 -- ---------------------------------------------------------------------------
852 FUNCTION exists_warning_text
853 (p_item_type IN wf_items.item_type%TYPE
854 ,p_item_key IN wf_items.item_key%TYPE
855 ,p_actid IN wf_process_activities.instance_id%TYPE) RETURN BOOLEAN IS
856 -- ---------------------------------------------------------------------------
857 -- Local variable declarations
858 -- ---------------------------------------------------------------------------
859 l_proc_name varchar2(61) := g_package||'exists_warning_text';
860 BEGIN
861 RETURN exists_messages
862 (p_item_type => p_item_type
863 ,p_item_key => p_item_key
864 ,p_actid => p_actid
865 ,p_type => 'WARNING_TEXT');
866 END exists_warning_text;
867 --
868 FUNCTION exists_warning_text
869 (p_item_type IN wf_items.item_type%TYPE
870 ,p_item_key IN wf_items.item_key%TYPE
871 ,p_actid IN wf_process_activities.instance_id%TYPE
872 ,p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE) RETURN BOOLEAN IS
873 -- ---------------------------------------------------------------------------
874 -- Local variable declarations
875 -- ---------------------------------------------------------------------------
876 l_proc_name varchar2(61) := g_package||'exists_warning_text';
877 BEGIN
878 RETURN exists_messages
879 (p_item_type => p_item_type
880 ,p_item_key => p_item_key
881 ,p_actid => p_actid
882 ,p_transaction_step_id => p_transaction_step_id
883 ,p_type => 'WARNING_TEXT');
884 END exists_warning_text;
885 --
886 end hr_emp_error_utility;