1 Package body pqh_ss_workflow as
2 /* $Header: pqwftswi.pkb 120.27.12020000.4 2013/01/22 15:24:42 ckondapi ship $*/
3
4
5 --
6 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
7 -- directory of SQL Navigator
8 --
9 -- Purpose: Briefly explain the functionality of the package body
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- --------- ------ ------------------------------------------
14 -- Enter procedure, function bodies as shown below
15 /*
16 PRF_DT_RULE_FUTUR_CHANGE_FOUND VARCHAR2(30) := 'PQH_DT_RULE_FUTUR_CHANGE_FOUND';
17 PRF_ALLOW_TRANSACTION_REFRESH VARCHAR2(30) := 'PQH_ALLOW_TRANSACTION_REFRESH';
18 PRF_ALLOW_CONCURRENT_TXN VARCHAR2(30) := 'PQH_ALLOW_CONCURRENT_TXN';
19 PRF_ALLOW_INELIGIBLE_ACTIONS VARCHAR2(30) := 'PQH_ENABLE_INELIGIBLE_ACTIONS';
20 */
21
22 -- Global variables
23 g_date_format constant varchar2(12) DEFAULT 'RRRR-MM-DD';
24 g_package constant varchar2(100) DEFAULT 'pqh_ss_workflow';
25 g_OA_HTML constant varchar2(100) DEFAULT fnd_web_config.jsp_agent;
26 g_OA_MEDIA constant varchar2(100) DEFAULT fnd_web_config.web_server||'OA_MEDIA/';
27 --
28 --
29 /* **************************************************************
30 UPDATE_TXN_CURRENT_VALUES
31 Private procedure to update current values to previous values in
32 hr_api_transaction_values table
33 *************************************************************** */
34 PROCEDURE update_txn_current_values ( p_transactionId NUMBER) IS
35 BEGIN
36 IF ( p_transactionId IS NULL ) THEN
37 RETURN;
38 END IF;
39 --
40 UPDATE hr_api_transaction_values
41 SET varchar2_value = previous_varchar2_value,
42 number_value = previous_number_value,
43 date_value = previous_date_value
44 WHERE transaction_step_id IN (
45 SELECT transaction_step_id
46 FROM hr_api_transaction_steps
47 WHERE transaction_id = p_transactionId
48 --AND api_name <> 'HR_SUPERVISOR_SS.PROCESS_API');
49 AND api_name NOT in ('HR_SUPERVISOR_SS.PROCESS_API'
50 ,'HR_PROCESS_SIT_SS.PROCESS_API'
51 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
52 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
53 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
54 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
55 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
56 ,'HR_COMP_PROFILE_SS.PROCESS_API')
57 );
58
59
60 -- ns 08-May-2003: Bug 2927679: On Cancel the changes and the review activity ids were mismatching
61 -- causing the issue
62 -- AND name NOT IN ('P_REVIEW_PROC_CALL','P_REVIEW_ACTID');
63 -- end 08-May-2003
64
65 -- For supervisor step only:
66 -- Copy the previously saved values from history to
67 -- the current transaction
68 PQH_SS_HISTORY.copy_value_from_history(p_transactionId);
69 --
70 EXCEPTION
71 WHEN OTHERS THEN
72 -- raise it so that calling procedure can track it
73 raise;
74 --
75 END;
76 /* **************************************************************
77 UPDATE_TXN_PREVIOUS_VALUES
78 Private procedure to update previous values to current values in
79 hr_api_transaction_values table
80 *************************************************************** */
81 --
82 PROCEDURE update_txn_previous_values ( p_transactionId NUMBER) IS
83 --
84 BEGIN
85 --
86 IF ( p_transactionId IS NULL ) THEN
87 RETURN;
88 END IF;
89 --
90 UPDATE hr_api_transaction_values
91 SET previous_varchar2_value = varchar2_value,
92 previous_date_value = date_value,
93 previous_number_value = number_value
94 WHERE transaction_step_id IN (
95 SELECT transaction_step_id
96 FROM hr_api_transaction_steps
97 WHERE transaction_id = p_transactionId
98 --AND api_name <> 'HR_SUPERVISOR_SS.PROCESS_API');
99 AND api_name NOT in ('HR_SUPERVISOR_SS.PROCESS_API'
100 ,'HR_PROCESS_SIT_SS.PROCESS_API'
101 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
102 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
103 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
104 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
105 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
106 ,'HR_COMP_PROFILE_SS.PROCESS_API')
107 );
108
109
110 -- For supervisor step only:
111 -- Store the saved/submitted values to history tables
112 PQH_SS_HISTORY.copy_value_to_history ( p_transactionId);
113 --
114 EXCEPTION
115 WHEN OTHERS THEN
116 -- raise it so that calling procedure can track it
117 raise;
118 --
119 END;
120
121 /* *****************************************
122 -- Private Procedure to
123 -- Update the transaction with original values from history
124 -- pick the 0th record and below from approval history
125 -- which maintains the original values that was saved (SFL) or submitted (APPROVE_EDIT & SUBMIT)
126 -- ******************************************/
127 PROCEDURE reset_original_values (
128 p_itemType IN VARCHAR2
129 ,p_itemKey IN VARCHAR2
130 ,p_txnId IN NUMBER ) IS
131 --
132 CURSOR cur_orig IS
133 SELECT step_history_id, datatype, name, value
134 FROM pqh_ss_value_history
135 WHERE (step_history_id,approval_history_id) IN (
136 SELECT sh.step_history_id, ah.approval_history_id
137 FROM pqh_ss_step_history sh,
138 pqh_ss_approval_history ah
139 WHERE ah.transaction_history_id = sh.transaction_history_id
140 AND sh.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
141 AND ah.transaction_history_id = p_txnId
142 AND ah.approval_history_id = 0);
143 --
144 --
145 l_step_history_id NUMBER(15);
146 l_datatype VARCHAR(20);
147 l_name VARCHAR(35);
148 l_originalValue VARCHAR2(2000);
149 l_dateOption VARCHAR2(10);
150 l_effectiveDate DATE;
151 l_dt_update_mode VARCHAR2(50);
152 --
153 BEGIN
154 --
155 IF ( cur_orig%ISOPEN ) THEN
156 CLOSE cur_orig;
157 END IF;
158 --
159 OPEN cur_orig;
160 LOOP
161
162 FETCH cur_orig INTO l_step_history_id, l_datatype, l_name, l_originalValue;
163 EXIT WHEN cur_orig%NOTFOUND;
164 --
165 IF ( l_datatype = 'NUMBER') THEN
166 HR_TRANSACTION_API.set_number_value (
167 p_transaction_step_id => l_step_history_id
168 ,p_person_id => null
169 ,p_name => l_name
170 ,p_value => hr_api.g_number
171 ,p_original_value => l_originalValue
172 );
173 ELSIF ( l_datatype = 'DATE') THEN
174 HR_TRANSACTION_API.set_date_value (
175 p_transaction_step_id => l_step_history_id
176 ,p_person_id => null
177 ,p_name => l_name
178 ,p_value => hr_api.g_date
179 ,p_original_value => fnd_date.canonical_to_date(l_originalValue)
180 );
181 ELSE
182 HR_TRANSACTION_API.set_varchar2_value (
183 p_transaction_step_id => l_step_history_id
184 ,p_person_id => null
185 ,p_name => l_name
186 ,p_value => hr_api.g_varchar2
187 ,p_original_value => l_originalValue
188 );
189 END IF;
190 END LOOP;
191 CLOSE cur_orig;
192
193 IF ( l_step_history_id IS NOT NULL) THEN
194 -- Fetch the effective date and date option from transaction Values
195 -- table and set it to the appropriate columns in transaction (header) table
196 l_dateOption := HR_TRANSACTION_API.get_varchar2_value (
197 p_transaction_step_id => l_step_history_id
198 ,p_name => 'P_EFFECTIVE_DATE_OPTION' );
199 --
200 IF (NVL(l_dateOption,'X') = 'E') THEN
201 l_effectiveDate := HR_TRANSACTION_API.get_date_value (
202 p_transaction_step_id => l_step_history_id
203 ,p_name => 'P_EFFECTIVE_DATE' );
204 --
205 ELSE
206 l_effectiveDate := sysdate;
207 END IF;
208 --
209 HR_TRANSACTION_API.update_transaction (
210 p_transaction_id => p_txnId
211 ,p_transaction_effective_date => l_effectiveDate
212 ,p_effective_date_option => NVL(l_dateOption,'E') );
213 --
214 wf_engine.setItemAttrText (
215 itemtype => p_itemType
216 ,itemkey => p_itemKey
217 ,aname => 'P_EFFECTIVE_DATE'
218 ,avalue => TO_CHAR(l_effectiveDate,g_date_format) );
219 --
220 --
221 END IF;
222 --
223 --COMMIT;
224 --
225 --
226 EXCEPTION
227 WHEN OTHERS THEN
228 raise;
229 --
230 END reset_original_values;
231
232 PROCEDURE get_item_type_and_key (
233 p_ntfId IN NUMBER
234 ,p_itemType OUT NOCOPY VARCHAR2
235 ,p_itemKey OUT NOCOPY VARCHAR2 ) IS
236
237 CURSOR cur_ias IS
238 SELECT item_type, item_key
239 FROM wf_item_activity_statuses
240 WHERE notification_id = p_ntfId;
241
242 CURSOR cur_not IS
243 SELECT SUBSTR(context,1,INSTR(context,':',1)-1)
244 ,SUBSTR(context,INSTR(context,':')+1, ( INSTR(context,':',INSTR(context,':')+1 ) - INSTR(context,':')-1) )
245 FROM wf_notifications
246 WHERE notification_id = p_ntfId;
247 BEGIN
248 IF ( cur_ias%ISOPEN ) THEN
249 CLOSE cur_ias;
250 END IF;
251 --
252
253 OPEN cur_ias;
254 FETCH cur_ias INTO p_itemType, p_itemKey;
255
256 IF ( cur_ias%NOTFOUND ) THEN
257 IF ( cur_not%ISOPEN ) THEN
258 CLOSE cur_not;
259 END IF;
260 --
261 OPEN cur_not;
262 FETCH cur_not INTO p_itemType, p_itemKey;
263 CLOSE cur_not;
264 END IF;
265
266 CLOSE cur_ias;
267 --
268 EXCEPTION
269 WHEN OTHERS THEN
270 raise;
271 --
272 END;
273 /* **************************************************************
274 -- Public function to Get Transaction Id from transaction table
275 -- using Item Type and Item Key info
276 --
277 *************************************************************** */
278 FUNCTION get_transaction_id (
279 p_itemType IN VARCHAR2
280 ,p_itemKey IN VARCHAR2 ) RETURN NUMBER IS
281 CURSOR cur_txn IS
282 SELECT transaction_id
283 FROM hr_api_transactions
284 WHERE item_type = p_itemType
285 AND item_key = p_itemKey;
286 l_transactionId NUMBER;
287 BEGIN
288 IF ( cur_txn%ISOPEN ) THEN
289 CLOSE cur_txn;
290 END IF;
291 --
292 OPEN cur_txn;
293 FETCH cur_txn INTO l_transactionId;
294 CLOSE cur_txn;
295 RETURN l_transactionId;
296 --
297 EXCEPTION
298 WHEN OTHERS THEN
299 raise;
300 --
301 END get_transaction_id;
302 --
303 FUNCTION get_notification_id (
304 p_itemType IN VARCHAR2
305 ,p_itemKey IN VARCHAR2
306 ) RETURN NUMBER IS
307 --
308 l_ntfId NUMBER;
309 --
310 CURSOR cur_ntf IS
311 SELECT ias.notification_id
312 FROM WF_ITEM_ACTIVITY_STATUSES IAS
313 WHERE ias.item_type = p_itemType
314 and ias.item_key = p_itemKey
315 and IAS.ACTIVITY_STATUS = 'NOTIFIED'
316 and notification_id is not null
317 and rownum < 2;
318 --
319 BEGIN
320 --
321 IF ( cur_ntf%ISOPEN ) THEN
322 CLOSE cur_ntf;
323 END IF;
324 --
325 OPEN cur_ntf;
326 FETCH cur_ntf INTO l_ntfId;
327 CLOSE cur_ntf;
328 RETURN l_ntfId;
329 --
330 EXCEPTION
331 WHEN OTHERS THEN
332 raise;
333 --
334 END;
335 --
336
337 FUNCTION isHrRepNtf(
338 p_itemType IN VARCHAR2,
339 p_itemKey IN VARCHAR2) RETURN Boolean IS
340
341 l_ntf_name VARCHAR2(100);
342 l_ntfId NUMBER;
343
344 BEGIN
345 l_ntfId := get_notification_id(p_itemType,p_itemKey);
346 l_ntf_name := WF_NOTIFICATION.getattrtext(l_ntfId,'HR_NTF_IDENTIFIER');
347
348 IF (l_ntf_name = 'HR_EMBED_ON_APPR_NTFY_HR_REP') THEN
349 RETURN true;
350 END IF;
351
352 RETURN false;
353
354 EXCEPTION WHEN OTHERS THEN
355 RETURN false;
356 END isHrRepNtf;
357
358 --
359 FUNCTION get_notified_activity (
360 p_itemType IN VARCHAR2
361 ,p_itemKey IN VARCHAR2
362 ,p_ntfId IN VARCHAR2
363 ) RETURN NUMBER IS
364 --
365 l_activityId NUMBER;
366 --
367 CURSOR cur_wf IS
368 SELECT process_activity
369 FROM WF_ITEM_ACTIVITY_STATUSES IAS
370 WHERE ias.item_type = p_itemType
371 and ias.item_key = p_itemKey
372 AND ias.notification_id IS NULL
373 and IAS.ACTIVITY_STATUS = 'NOTIFIED'
374 and rownum < 2;
375 --
376 CURSOR cur_wf_ntfId(p_ntfId VARCHAR2) IS
377 SELECT process_activity
378 FROM WF_ITEM_ACTIVITY_STATUSES IAS
379 WHERE ias.item_type = p_itemType
380 and ias.item_key = p_itemKey
381 and ias.notification_id = p_ntfId
382 and IAS.ACTIVITY_STATUS = 'NOTIFIED'
383 and rownum < 2;
384 --
385 BEGIN
386 IF (p_ntfId is null) THEN
387 IF ( cur_wf%ISOPEN ) THEN
388 CLOSE cur_wf;
389 END IF;
390 --
391 OPEN cur_wf;
392 FETCH cur_wf INTO l_activityId;
393 CLOSE cur_wf;
394 --
395 ELSE
396 IF ( cur_wf_ntfId%ISOPEN ) THEN
397 CLOSE cur_wf_ntfId;
398 END IF;
399 --
400 OPEN cur_wf_ntfId(p_ntfId);
401 FETCH cur_wf_ntfId INTO l_activityId;
402 CLOSE cur_wf_ntfId;
403 --
404 END IF;
405 --
406 return l_activityId;
407 --
408 --
409 EXCEPTION
410 WHEN OTHERS THEN
411 raise;
412 --
413 END get_notified_activity;
414 --
415 --
416 FUNCTION get_notified_activity (
417 p_itemType IN VARCHAR2
418 ,p_itemKey IN VARCHAR2
419 ) RETURN NUMBER IS
420 --
421 l_activityId NUMBER;
422 --
423 CURSOR cur_wf IS
424 -- Fix for bug 3719338
425 /*SELECT ias.process_activity
426 FROM wf_item_activity_statuses ias
427 WHERE ias.item_type = p_itemType
428 and ias.item_key = p_itemKey
429 and ias.activity_status = 'NOTIFIED'
430 and ias.process_activity in (
431 select pa.instance_id
432 FROM wf_process_activities PA,
433 wf_activity_attributes AA,
434 wf_activities WA,
435 wf_items WI
436 WHERE pa.process_item_type = ias.item_type
437 and wa.item_type = pa.process_item_type
438 and wa.name = pa.activity_name
439 and wi.item_type = ias.item_type
440 and wi.item_key = ias.item_key
441 and wi.begin_date >= wa.begin_date
442 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
443 and aa.activity_item_type = wa.item_type
444 and aa.activity_name = wa.name
445 and aa.activity_version = wa.version
446 and aa.type = 'FORM'
447 )
448 order by Decode(ias.activity_result_code,'#NULL',1,2);
449 */
450 SELECT process_activity
451 from
452 (select process_activity
453 FROM WF_ITEM_ACTIVITY_STATUSES IAS
454 WHERE ias.item_type = p_itemType
455 and ias.item_key = p_itemKey
456 and ias.activity_status = 'NOTIFIED'
457 and ias.process_activity in (
458 select wpa.instance_id
459 FROM WF_PROCESS_ACTIVITIES WPA,
460 WF_ACTIVITY_ATTRIBUTES WAA,
461 WF_ACTIVITIES WA,
462 WF_ITEMS WI
463 WHERE wpa.process_item_type = ias.item_type
464 and wa.item_type = wpa.process_item_type
465 and wa.name = wpa.activity_name
466 and wi.item_type = ias.item_type
467 and wi.item_key = ias.item_key
468 and wi.begin_date >= wa.begin_date
469 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
470 and waa.activity_item_type = wa.item_type
471 and waa.activity_name = wa.name
472 and waa.activity_version = wa.version
473 and waa.type = 'FORM'
474 )
475 order by begin_date desc)
476 where rownum<=1;
477
478 --
479 BEGIN
480 IF ( cur_wf%ISOPEN ) THEN
481 CLOSE cur_wf;
482 END IF;
483 --
484 OPEN cur_wf;
485 FETCH cur_wf INTO l_activityId;
486 CLOSE cur_wf;
487 return l_activityId;
488 --
489 EXCEPTION
490 WHEN OTHERS THEN
491 raise;
492 --
493 END get_notified_activity;
494
495 /* ******************************************************
496 -- Complete Workflow Activity
497 --
498 ******************************************************* */
499 PROCEDURE complete_wf_activity (
500 p_itemType IN VARCHAR2,
501 p_itemKey IN VARCHAR2,
502 p_activity IN NUMBER,
503 p_otherAct IN VARCHAR2,
504 p_resultCode IN VARCHAR2,
505 p_commitFlag IN VARCHAR2 DEFAULT 'N' ) IS
506 --
507 -- PRAGMA AUTONOMOUS_TRANSACTION;
508 l_activity Number;
509
510 actdate date; -- Active date
511 acttype varchar2(8); -- Activity type
512 notid pls_integer; -- Notification group id
513 user varchar2(320); -- Notification assigned user
514
515
516 CURSOR c_wf IS
517 SELECT instance_label,ias.process_activity actvityId, ias.notification_id ntfId
518 FROM WF_ITEM_ACTIVITY_STATUSES IAS,
519 WF_PROCESS_ACTIVITIES PA
520 WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
521 AND ias.item_type = pa.process_item_type
522 and ias.item_type = p_itemType
523 and ias.item_key = p_itemKey
524 and ias.activity_status = 'NOTIFIED'
525 and ias.process_activity <> p_activity
526 and not exists (select 'e'
527 from WF_ACTIVITIES wa, WF_ACTIVITY_ATTRIBUTES waa, WF_ITEMS wi
528 where wa.item_type = pa.process_item_type
529 and wa.name = pa.activity_name
530 and wi.item_type = ias.item_type
531 and wi.item_key = ias.item_key
532 and wi.begin_date between wa.begin_date and nvl(wa.end_date,wi.begin_date)
533 and waa.activity_item_type = wa.item_type
534 and waa.activity_name = wa.name
535 and waa.activity_version = wa.version
536 and waa.type = 'FORM');
537
538 /*
539 and ( (p_otherAct = 'OTHER' AND ias.process_activity <> p_activity) OR -- Activity other than the passed one
540 (p_otherAct = 'CURR' AND ias.process_activity = l_activity ) OR -- Fetch current first and complete that
541 (p_otherAct = 'THIS' AND ias.process_activity = p_activity ) ) ; -- Notification to be completed.
542 --
543 p_OtherAct = yes'OTHER' - Complete the activity other than the passed one
544 p_OtherAct = no 'CURR' - Activity not available, fetch current activity firs
545 t, and then complete that.
546 p_OtherAct = NTF 'THIS' - Complete the passed activity
547 */
548 l_activity_name VARCHAR2(100);
549 --
550 --
551 BEGIN
552 --
553 IF ( p_otherAct IN ('CURR','THIS')) THEN
554 IF ( p_otherAct = 'CURR') THEN
555 l_activity := get_notified_activity(p_itemType,p_itemKey);
556 if(l_activity is null ) then
557 return;
558 end if;
559 ELSIF ( p_otherAct = 'THIS') THEN
560 l_activity := p_activity;
561 END IF;
562 --
563 l_activity_name := wf_engine.getActivityLabel(l_activity);
564
565 begin
566 actdate := Wf_Item.Active_Date(p_itemType, p_itemKey);
567 acttype := Wf_Activity.Instance_Type(l_activity, actdate);
568
569 if (acttype = wf_engine.eng_notification) then
570 -- Get notification id
571 Wf_Item_Activity_Status.Notification_Status(p_itemType, p_itemKey, l_activity,
572 notid, user);
573 end if;
574 exception
575 when others then
576 null;
577 end;
578 --
579 ELSIF ( p_otherAct = 'OTHER') THEN
580 --
581 IF ( c_wf%ISOPEN ) THEN
582 CLOSE c_wf;
583 END IF;
584 --
585 OPEN c_wf;
586 FETCH c_wf into l_activity_name,l_activity,notid;
587 CLOSE c_wf;
588 END IF;
589 --
590 --
591 if l_activity_name is not null then
592 if (notid is not null) then
593 wf_notification.setattrtext(
594 notid
595 ,'RESULT'
596 ,p_resultCode);
597 wf_notification.respond(
598 notid
599 ,null
600 ,fnd_global.user_name
601 ,null);
602 else
603 wf_engine.CompleteActivity(
604 p_itemType
605 , p_itemKey
606 , l_activity_name
607 , p_resultCode) ;
608 end if;
609 end if;
610 -- Removing commit as Pragma Autonomous_Transaction is added;
611 -- if ( p_commitFlag = 'Y' ) then
612 commit;
613 -- end if;
614 --
615 --
616 --
617 EXCEPTION
618 WHEN OTHERS THEN
619 hr_utility.trace(' exception in '||g_package||'.complete_wf_activity : ' || sqlerrm);
620 Wf_Core.Context(g_package, 'complete_wf_activity', p_itemType, p_itemKey);
621 raise;
622 --
623 END complete_wf_activity;
624 --
625 PROCEDURE start_approval_wf (
626 itemtype IN VARCHAR2,
627 itemkey IN VARCHAR2,
628 actid IN NUMBER,
629 funcmode IN VARCHAR2,
630 result OUT NOCOPY VARCHAR2 ) IS
631 --
632 l_initialSFL VARCHAR2(30);
633 l_NtfId NUMBER;
634 l_result VARCHAR2(30) := 'RESUBMIT'; -- Default result
635
636 CURSOR cur_sfl IS
637 SELECT nvl(decode(wav.text_value, null, hat.status,
638 decode(hat.status,'S','SUBMIT',hat.status)),'N')
639 FROM hr_api_transactions hat,
640 wf_item_attribute_values wav
641 WHERE hat.item_type = wav.item_Type
642 AND hat.item_key = wav.item_Key
643 AND wav.item_type = itemType
644 AND wav.item_key = itemKey
645 AND wav.name = 'SAVED_ACTIVITY_ID';
646
647
648 BEGIN
649 --
650 IF (funcmode = wf_engine.eng_run) THEN
651
652 -- fix for bug 4454439
653 begin
654 -- re-intialize the performer roles
655 hr_approval_ss.reinitperformerroles(p_notification_id=>null
656 ,p_transaction_id=>null
657 ,p_item_type=>itemtype
658 ,p_item_key=>itemKey);
659 exception
660 when others then
661 null;
662 end;
663
664
665 IF ( cur_sfl%ISOPEN ) THEN
666 CLOSE cur_sfl;
667 END IF;
668 --
669 OPEN cur_sfl;
670 FETCH cur_sfl into l_initialSFL;
671 CLOSE cur_sfl;
672
673 --If initial save for later then result is APPROVED, else it is RESUBMIT
674 --This is used to ignore two history records for initial SFL & Submit
675 IF ( l_initialSFL = 'SUBMIT') THEN
676 l_result := 'APPROVED';
677 ELSIF (l_initialSFL = 'Y') THEN
678
679 l_NtfId := get_notification_id(itemtype, itemKey);
680
681 wf_notification.setattrtext(
682 l_NtfId
683 ,'WF_NOTE'
684 ,wf_engine.GetItemAttrText(
685 itemtype
686 ,itemkey
687 ,'APPROVAL_COMMENT_COPY'));
688 wf_engine.setitemattrtext(
689 itemtype
690 ,itemkey
691 ,'APPROVAL_COMMENT_COPY'
692 ,null);
693 ELSE
694 wf_notification.propagatehistory(
695 itemtype
696 ,itemkey
697 ,'APPROVAL_NOTIFICATION'
698 ,fnd_global.user_name
699 ,'WF_SYSTEM'
700 --,hr_workflow_ss.getNextApproverForHist(itemtype, itemkey)
701 ,'RESUBMIT'
702 ,null
703 ,wf_engine.GetItemAttrText(
704 itemtype
705 ,itemkey
706 ,'APPROVAL_COMMENT_COPY'));
707 END IF;
708
709 --Set transaction status to pending
710 --Bug 3018784: This call is moved from set_txn_submit_status
711 set_transaction_status (
712 p_itemType => itemType
713 ,p_itemKey => itemKey
714 ,p_action => 'SUBMIT'
715 ,p_result => result );
716
717 --Complete the Activity in approval flow with Approved (if new or SFL txn)
718 --Or Resubmit if pending or RFC transactions.
719 complete_wf_activity (
720 p_itemType => itemtype,
721 p_itemKey => itemkey,
722 p_activity => actid,
723 p_otherAct => 'OTHER',
724 p_commitFlag => 'Y',
725 p_resultCode => l_result ) ;
726 END IF;
727 --
728 result := 'COMPLETE:NEXT';
729 --
730 --
731 EXCEPTION
732 WHEN OTHERS THEN
733 hr_utility.trace(' exception in '||g_package||'.start_approval_wf : ' || sqlerrm);
734 Wf_Core.Context(g_package, 'start_approval_wf', itemType, itemKey);
735 raise;
736 --
737 END start_approval_wf;
738 --
739 --
740 PROCEDURE get_transaction_info (
741 p_itemType IN VARCHAR2
742 ,p_itemKey IN VARCHAR2
743 ,p_loginPerson IN VARCHAR2
744 ,p_whatchecks IN VARCHAR2 DEFAULT 'EPIG'
745 ,p_calledFrom IN VARCHAR2 DEFAULT 'REQUEST'
746 ,p_personId OUT NOCOPY VARCHAR2
747 ,p_assignmentId OUT NOCOPY VARCHAR2
748 ,p_state OUT NOCOPY VARCHAR2
749 ,p_status OUT NOCOPY VARCHAR2
750 ,p_txnId OUT NOCOPY VARCHAR2
751 ,p_businessGrpId OUT NOCOPY VARCHAR2
752 ,p_editAllowed OUT NOCOPY VARCHAR2
753 ,p_futureChange OUT NOCOPY VARCHAR2
754 ,p_pendingTxn OUT NOCOPY VARCHAR2
755 ,p_interAction OUT NOCOPY VARCHAR2
756 ,p_effDateOption IN OUT NOCOPY VARCHAR2
757 ,p_effectiveDate IN OUT NOCOPY VARCHAR2
758 ,p_isPersonElig OUT NOCOPY VARCHAR2
759 ,p_rptgGrpId OUT NOCOPY VARCHAR2
760 ,p_planId OUT NOCOPY VARCHAR2
761 ,p_processName OUT NOCOPY VARCHAR2
762 ,p_dateParmExist OUT NOCOPY VARCHAR2
763 ,p_rateParmExist OUT NOCOPY VARCHAR2
764 ,p_slryParmExist OUT NOCOPY VARCHAR2
765 ,p_rateMessage OUT NOCOPY VARCHAR2
766 ,p_terminateFlag OUT NOCOPY VARCHAR2 ) IS
767 --
768 l_effDate DATE;
769 l_changeDate DATE;
770 l_terminationDate VARCHAR2(30);
771 l_effDateOption VARCHAR2(10);
772 l_functionId NUMBER(15);
773 l_parameter fnd_form_functions.parameters%TYPE;
774 dummy VARCHAR2(10);
775 l_flowName VARCHAR2(100);
776 l_version VARCHAR2(10);
777 l_asg_tx VARCHAR2(1) := 'N';
778
779 -- Fetch transaction information
780 CURSOR cur_txn IS
781 SELECT transaction_id, status, transaction_state, NVL(transaction_effective_date,sysdate),
782 assignment_id, effective_date_option, plan_id, rptg_grp_id,
783 NVL(selected_person_id,-1), process_name,function_id
784 FROM hr_api_transactions
785 WHERE item_type = p_itemType
786 AND item_key = p_itemKey;
787 --
788 -- Bug 2969312: 21-May-2003: ns
789 -- check to see if assignment is terminated
790 -- if so flag is set
791 -- Check if the transaction is on an assignment
792 CURSOR cur_chk_asg (c_txnId NUMBER) IS
793 SELECT 'X'
794 FROM hr_api_transaction_steps
795 WHERE transaction_id = c_txnId
796 AND api_name IN (
797 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
798 'HR_SUPERVISOR_SS.PROCESS_API',
799 'HR_TERMINATION_SS.PROCESS_API',
800 'HR_PAY_RATE_SS.PROCESS_API',
801 'PER_SSHR_CHANGE_PAY.PROCESS_API' );
802 --
803 -- Check if the assignment is terminated
804 CURSOR cur_term_asg (c_asgnId NUMBER, c_effective_date DATE) IS
805 SELECT 'X'
806 FROM per_all_assignments_f
807 WHERE assignment_id = c_asgnId
808 AND effective_end_date <= trunc(c_effective_date)
809 AND assignment_status_type_id in (
810 SELECT assignment_status_type_id
811 FROM per_assignment_status_types
812 WHERE per_system_status in ('TERM_ASSIGN', 'END'));
813 --
814 -- Check if assignment is terminated in future
815 CURSOR cur_fut_term_asg (c_asgnId NUMBER, c_effective_date DATE) IS
816 SELECT to_char(ser.actual_termination_date,g_date_format)
817 FROM per_periods_of_service ser,
818 per_all_assignments_f ass
819 where ass.period_of_service_id = ser.period_of_service_id
820 AND ass.assignment_id = c_asgnId
821 AND TRUNC(c_effective_date) between ass.effective_start_date AND ass.effective_end_date ;
822
823 -- For Employee transactions check if the person is terminated
824 CURSOR cur_term_emp (c_personId NUMBER, c_effective_date DATE) IS
825 SELECT 'X'
826 FROM per_all_people_f
827 WHERE nvl(current_employee_flag,'N') <> 'Y'
828 AND nvl(current_applicant_flag,'N') <> 'Y'
829 AND nvl(current_npw_flag,'N') <> 'Y'
830 AND TRUNC(c_effective_date) BETWEEN effective_start_date AND effective_end_date
831 AND person_id = c_personId;
832 --
833 CURSOR cur_fn (c_functionId NUMBER) IS
834 SELECT parameters
835 FROM fnd_form_functions
836 WHERE function_id = c_functionId;
837 --
838 -- Bug 3003754: check to see if pay rate changes exist on the same date as the transaction effective date
839 CURSOR cur_pay ( c_assignmentId NUMBER, c_bgId NUMBER) IS
840 SELECT change_date
841 FROM per_pay_proposals
842 WHERE assignment_id = c_assignmentId
843 AND business_group_id = c_bgId
844 ORDER BY change_date desc ;
845
846 l_rehire_flow varchar2(25) default null;
847
848 BEGIN
849 --
850 /* p_WhatChecks can be
851 E- Edit privilege
852 F- Future dated check alone
853 P- Pending Transaction Check
854 I- Intervening Action and future dated checks
855 G- Eligibility
856 */
857 IF ( p_whatChecks IS NULL ) THEN RETURN; END IF;
858 IF ( cur_txn%ISOPEN ) THEN
859 CLOSE cur_txn;
860 END IF;
861 --
862 OPEN cur_txn ;
863 FETCH cur_txn INTO p_txnId, p_status, p_state, l_effDate , p_assignmentId, l_effDateOption,
864 p_planId, p_rptgGrpId, p_personId, p_processName, l_functionId;
865 CLOSE cur_txn;
866
867 -- If effective date is passed, validate information as of the
868 -- passed effective date
869 -- else validate against the transaction effective date.
870 IF ( p_effectiveDate IS NOT NULL ) THEN
871 l_effDate := TO_DATE(p_effectiveDate, g_date_format );
872 ELSE
873 p_effectiveDate := TO_CHAR(l_effDate, g_date_format);
874 END IF;
875 --
876 IF (p_effDateOption IS NOT NULL ) THEN
877 l_effDateOption := p_effDateOption;
878 ELSE
879 p_effDateOption := l_effDateOption;
880 END IF;
881 --
882 --
883 -- Bug 2969312: 21-May-2003: ns
884 -- Check if the transaction is assignment related
885 -- if so, check if assignment is terminated, else
886 -- check if person is terminated.
887 -- if either is terminated, set appropriate flag
888 p_terminateFlag := 'NO'; -- reset flag before use.
889
890 IF ( cur_fut_term_asg%ISOPEN ) THEN
891 CLOSE cur_fut_term_asg;
892 END IF;
893 --
894 OPEN cur_chk_asg(p_txnId );
895 FETCH cur_chk_asg INTO dummy;
896
897 IF cur_chk_asg%FOUND THEN -- Assignment related transaction
898 l_asg_tx := 'Y';
899 END IF;
900 CLOSE cur_chk_asg;
901
902 -- do not perform termination check for non assignment
903 -- related transaction.
904 IF l_asg_tx = 'Y' THEN
905
906 --
907 OPEN cur_fut_term_asg(p_assignmentId, TO_DATE(p_effectiveDate, g_date_format ));
908 FETCH cur_fut_term_asg INTO l_terminationDate;
909 CLOSE cur_fut_term_asg;
910
911 IF ( l_terminationDate IS NOT NULL ) THEN
912
913 if p_itemType is not null and p_itemKey is not null then
914 l_rehire_flow := wf_engine.GetItemAttrText(p_itemType,p_itemKey,'HR_FLOW_IDENTIFIER',true);
915
916 end if;
917
918 --
919 IF nvl(l_rehire_flow,'N') <> 'EX_EMP' AND nvl(l_rehire_flow,'N') <> 'REVERSE_TERMINATION' then
920 IF ( to_date(l_terminationDate,g_date_format) <= trunc(sysdate)) THEN
921 p_terminateFlag := 'ASGN'; -- set flag
922 ELSE
923 p_terminateFlag := l_terminationDate;
924 END IF;
925 END IF;
926
927 --
928 ELSE
929 --
930 IF ( cur_term_emp%ISOPEN ) THEN
931 CLOSE cur_term_emp;
932 END IF;
933 --
934 OPEN cur_term_emp(p_personId, TO_DATE(p_effectiveDate, g_date_format ));
935 FETCH cur_term_emp INTO dummy;
936 --
937 if p_itemType is not null and p_itemKey is not null then
938 l_rehire_flow := wf_engine.GetItemAttrText(p_itemType,p_itemKey,'HR_FLOW_IDENTIFIER',true);
939 end if;
940 If nvl(l_rehire_flow,'N') <> 'EX_EMP' AND nvl(l_rehire_flow,'N') <> 'REVERSE_TERMINATION' then
941 IF cur_term_emp%FOUND THEN -- Person is found to be terminated
942 --
943 p_terminateFlag := 'PRSN'; -- set flag for person termination
944 --
945 ELSE
946 --
947 IF ( cur_term_asg%ISOPEN ) THEN
948 CLOSE cur_term_asg;
949 END IF;
950 --
951 OPEN cur_term_asg (p_assignmentId, TO_DATE(p_effectiveDate, g_date_format ));
952 FETCH cur_term_asg INTO dummy;
953 --
954 IF cur_term_asg%FOUND THEN -- Assignment is found to be terminated
955 p_terminateFlag := 'ASGN'; -- set flag
956 END IF;
957 --
958 CLOSE cur_term_asg;
959 --
960 END IF;
961 End if;
962 --
963 CLOSE cur_term_emp;
964 --
965 END IF;
966 END IF;
967 --
968 --
969 -- Check if effective Date parameter exist for the function
970 IF ( cur_fn%ISOPEN ) THEN
971 CLOSE cur_fn;
972 END IF;
973 --
974 OPEN cur_fn (l_functionId);
975 FETCH cur_fn INTO l_parameter;
976 CLOSE cur_fn;
977
978 IF ( INSTR(l_parameter,'pEffectiveDate') > 0 ) THEN
979 p_dateParmExist := 'Y';
980 END IF;
981
982 IF ( INSTR(l_parameter,'pPayRate') > 0 ) THEN
983 p_rateParmExist := 'Y';
984 END IF;
985
986 IF ( INSTR(l_parameter,'pSalChange') > 0 ) THEN
987 p_slryParmExist := 'Y';
988 END IF;
989
990 -- if date option is as of approval, take current date for processing.
991 IF (l_effDateOption = 'A' OR p_dateParmExist = 'N') THEN
992 l_effDate := TRUNC( Sysdate);
993 END IF;
994
995 -- ========================================
996 -- if termination flag is set, then return
997 -- without any further checks.
998 IF ( NVL(p_terminateFlag,'NO') <> 'NO') THEN
999 return;
1000 END IF;
1001 -- ========================================
1002
1003 p_businessGrpId := PQH_SS_UTILITY.get_business_group_id (
1004 p_personId => p_personId
1005 ,p_effectiveDate => l_effDate );
1006 --
1007 -- check salary changes
1008 IF ( p_assignmentId IS NOT NULL AND p_businessGrpId IS NOT NULL
1009 AND p_rateParmExist = 'Y') THEN
1010 IF ( cur_pay%ISOPEN ) THEN
1011 CLOSE cur_pay;
1012 END IF;
1013 --
1014 OPEN cur_pay (p_assignmentId, p_businessGrpId);
1015 FETCH cur_pay INTO l_changeDate;
1016 --
1017 IF cur_pay%FOUND THEN
1018 IF l_changeDate = l_effDate THEN
1019 p_rateMessage := 'PQH_SS_PAYRATE_SAMEDAY_ERR';
1020 ELSIF l_changeDate > l_effDate THEN
1021 p_rateMessage := 'PQH_SS_PAYRATE_FUTURE_ERR';
1022 END IF;
1023 END IF;
1024 --
1025 CLOSE cur_pay;
1026 END IF;
1027
1028 IF ( p_processName IS NULL ) THEN
1029 p_processName := wf_engine.GetItemAttrText(
1030 itemtype => p_itemType
1031 ,itemkey => p_itemKey
1032 ,aname => 'PROCESS_NAME');
1033 END IF;
1034 --
1035 BEGIN
1036 l_flowName := wf_engine.GetItemAttrText(
1037 itemtype => p_itemType,
1038 itemkey => p_itemKey,
1039 aname => 'HR_FLOW_NAME_ATTR');
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 NULL; -- if attribute not found then continue w/out error
1043 END;
1044
1045 p_editAllowed := 'N';
1046
1047 -- Bug 3025523: Check access to selected employee's record via secured view
1048 -- Secure access is not checked in case of New Hire as there
1049 -- will be no record in hr tables.
1050 -- added (NVL(p_calledFrom,'X') = 'FINAL_VALIDATION') for bug 13416339
1051 IF ( NVL(l_flowName,'x') <> 'HrCommonInsertOab' AND
1052 NVL(l_flowName, 'x') <> 'CWKPlacement' AND
1053 p_personid <> -1 AND
1054 NVL(p_calledFrom,'X') <> 'FINAL_VALIDATION') THEN
1055 BEGIN
1056 select 'x'
1057 into dummy
1058 from per_people_f
1059 where person_id = p_personId
1060 and l_effDate between effective_start_date and effective_end_date;
1061 --
1062 dummy := null;
1063 --
1064 EXCEPTION
1065 WHEN NO_DATA_FOUND THEN
1066 p_editAllowed := 'NS'; --No access to emp record
1067 -- ========================================================
1068 -- No need to perform all the validations, if manager does not have
1069 -- secure access to the selected person's record.
1070 -- ========================================================
1071 Return;
1072 END;
1073 END IF;
1074
1075 IF ( INSTR(p_whatChecks,'E') > 0) THEN
1076 --
1077 IF ( INSTR(p_status,'S') >0 OR p_status = 'RI' ) THEN
1078 p_editAllowed := 'Y';
1079 ELSE
1080 --
1081 PQH_SS_UTILITY.check_edit_privilege (
1082 p_personId => p_loginPerson
1083 ,p_businessGroupId => p_businessGrpId
1084 ,p_editAllowed => p_editAllowed );
1085 --
1086 END IF;
1087 END IF;
1088
1089 -- ========================================================
1090 -- After evaluating the edit privilege, there is no need
1091 -- to perform all the validations, if in new hire flow
1092 -- ========================================================
1093 IF ( NVL(l_flowName,'x') = 'HrCommonInsertOab' OR
1094 NVL(l_flowName, 'x') = 'CWKPlacement' ) THEN
1095 if p_effDateOption IS NULL then
1096 p_effDateOption := 'E';
1097 end if;
1098
1099 RETURN;
1100 END IF;
1101
1102 -- ========================================================
1103 -- Perform other validations only if the user has Edit privilege
1104 -- these checks are irrelevant if user cannot edit.
1105 -- Either Edit priv was not checked or checked and found eligible
1106 -- ========================================================
1107 IF ( INSTR(p_whatChecks,'E')=0 OR p_editAllowed = 'Y' ) THEN
1108
1109 IF ( INSTR(p_whatChecks,'P') > 0) THEN
1110 p_pendingTxn := PQH_SS_UTILITY.check_pending_Transaction (
1111 p_txnId => p_txnId
1112 ,p_itemType => p_itemType
1113 ,p_personId => p_personId
1114 ,p_assignId => p_assignmentId ) ;
1115 END IF;
1116 --
1117 p_futureChange := 'N';
1118 p_interAction := 'N';
1119 --Perform date track validations only if the transaction has
1120 --date associated with it.
1121 IF ( p_dateParmExist <> 'N') THEN
1122 --
1123 l_version := PQH_SS_UTILITY.get_approval_process_version(
1124 p_itemType => p_itemType,
1125 p_itemKey => p_itemKey );
1126 --
1127 -- ========================================================
1128 -- For V4 approval process, compare the OVN of assignment and the transaction
1129 -- If they do not match return appropriate flag
1130 -- ========================================================
1131 IF ( l_version IS NULL OR l_version <> 'V5') THEN
1132 Declare
1133 --
1134 CURSOR cur_asg_step IS
1135 SELECT transaction_step_id
1136 FROM hr_api_transaction_steps
1137 WHERE transaction_id = p_txnId
1138 AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
1139 --
1140 --
1141 CURSOR cur_match_ovn (c_txn_step_id NUMBER, c_effDate DATE) IS
1142 SELECT 'X'
1143 FROM hr_api_transaction_values tv,
1144 per_assignments_f af
1145 WHERE af.assignment_id = p_assignmentId
1146 AND tv.transaction_step_id = c_txn_step_id
1147 AND tv.name = 'P_OBJECT_VERSION_NUMBER'
1148 AND c_effDate BETWEEN af.effective_start_date AND af.effective_end_date
1149 AND NVL(original_number_value,number_value) = af.object_version_number;
1150 --
1151 l_txn_step_id NUMBER;
1152 --
1153 Begin
1154 --
1155 Open cur_asg_step;
1156 Fetch cur_asg_step INTO l_txn_step_id;
1157
1158 -- ==================================================
1159 -- Assignment step exists, check if the ovns match
1160 -- If assginment step does not exist then do nothing
1161 -- ==================================================
1162 IF cur_asg_step%FOUND THEN
1163 --
1164 Open cur_match_ovn (l_txn_step_id, l_effDate);
1165 Fetch cur_match_ovn INTO Dummy;
1166 -- If matching ovns are not found -Intervening action -Set flag to throw error
1167 IF cur_match_ovn%NOTFOUND THEN
1168 p_interAction := 'YV4';
1169 END IF;
1170 Close cur_match_ovn;
1171 --
1172 END IF;
1173 --
1174 Close cur_asg_step;
1175 --
1176 End;
1177 --
1178 ELSE
1179 --
1180 IF ( INSTR(p_whatChecks,'I') > 0 OR INSTR(p_whatChecks,'F') > 0) THEN
1181 --
1182 p_futureChange := PQH_SS_UTILITY.check_future_Change (
1183 p_txnId => p_txnId
1184 ,p_assignmentId => p_assignmentId
1185 ,p_effectiveDate => l_effDate
1186 ,p_calledFrom => p_calledFrom );
1187 --
1188 END IF; -- future/intervening
1189
1190 IF ( INSTR(p_whatChecks,'I') > 0) THEN
1191 --
1192 p_interAction := PQH_SS_UTILITY.check_intervening_Action (
1193 p_txnId => p_txnId
1194 ,p_assignmentId => p_assignmentId
1195 ,p_effectiveDate => l_effDate
1196 ,p_futureChange => p_futureChange ) ;
1197 --
1198 END IF; -- intervening
1199 --
1200 END IF; -- End if Approval version
1201 --
1202 END IF; --dateParamExist
1203 --
1204 IF ( INSTR(p_whatChecks,'G') > 0 ) THEN
1205 --
1206 p_isPersonElig := PQH_SS_UTILITY.check_eligibility (
1207 p_planId => p_planId
1208 ,p_personId => p_personId
1209 ,p_effectiveDate => l_effDate ) ;
1210 --
1211 END IF;
1212 END IF;
1213 --
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 hr_utility.trace(' exception in '||g_package||'.get_transaction_info : ' || sqlerrm);
1217 raise;
1218 --
1219 END get_transaction_info;
1220 --
1221
1222
1223 PROCEDURE revert_to_last_save (
1224 p_txnId IN NUMBER
1225 ,p_itemType IN VARCHAR2
1226 ,p_itemKey IN VARCHAR2
1227 ,p_status IN VARCHAR2
1228 ,p_state IN VARCHAR2
1229 ,p_revertFlag IN VARCHAR2 ) IS
1230 --
1231 l_applicationId NUMBER(15);
1232 l_regionCode VARCHAR2(30);
1233 l_newState VARCHAR2(5);
1234 l_activityLabel VARCHAR2(240);
1235 l_savedStatus BOOLEAN := INSTR(p_status,'S') > 0;
1236 l_activityId NUMBER(15);
1237 l_currentActivityId NUMBER(15);
1238 l_savedActivityId NUMBER(15);
1239 --
1240 BEGIN
1241
1242
1243 -- Handle WF Transitions
1244 -- if the txn is in sfl status AND if either unsaved changes found that user wanted to revert or txn is in transient state
1245 -- transition back to saved activity
1246 IF ( l_savedStatus AND ( (p_state='T') OR (p_state = 'W' AND p_revertFlag = 'Y') ) )THEN
1247 --
1248 l_savedActivityId := wf_engine.GetItemAttrText(
1249 itemtype => p_itemType,
1250 itemkey => p_itemKey,
1251 aname => 'SAVED_ACTIVITY_ID');
1252 --
1253 --
1254 l_currentActivityId := get_notified_activity(
1255 p_itemType => p_itemType
1256 ,p_itemKey => p_itemKey);
1257
1258 IF (l_savedActivityId <> l_currentActivityId ) THEN
1259
1260 -- handle error to go back to the saved activity
1261 l_activityLabel := WF_ENGINE.GetActivityLabel(l_savedActivityId);
1262 --
1263 --possibility of the method closing the notification
1264 -- instead of the form activity.
1265 if(l_activityLabel is not null) then
1266 WF_ENGINE.handleError(
1267 itemType => p_itemType
1268 ,itemKey => p_itemKey
1269 ,activity => l_activityLabel
1270 ,command => 'RETRY' ) ;
1271 end if;
1272
1273 -- Bug 2962973:ns complete the current activity
1274 -- so that only SFL activity is left notified
1275 -- NO DML Operations directly on FND schema.
1276 -- Fix for bug#3578646
1277 /*
1278 UPDATE wf_item_activity_statuses
1279 SET activity_status = 'COMPLETE',
1280 activity_result_code ='#NULL'
1281 WHERE item_type = p_itemType
1282 AND item_key = p_itemKey
1283 AND process_activity = l_currentActivityId;
1284 */
1285
1286 END IF;
1287 -- if unsave changes found and user does not want to revert
1288 -- if (savedStatus and (p_state IS NULL OR p_state='W' and revert=N)) OR
1289 -- ( otherThanSavedStatus AND p_state='W' and revert=N) ) THEN do nothing
1290 ELSIF ( l_savedStatus OR (p_state ='W' and p_revertFlag='N') or (p_status = 'W' and (p_state is null or p_state = 'T' or p_state = 'W')) ) THEN
1291 NULL;
1292 -- if txn not in sfl status OR if state is null
1293 ELSE --i.e IF p_state is NULL (redo needed in this cause coz it could be the blocked activity)
1294 -- if (p_state = 'W' AND p_revertFlag='Y' and status = NOT SAVED)
1295 -- Find the current activity
1296 -- if p_state is not null, Complete WF with RESULT=REDO to go to the first page of the transaction,
1297 -- Since this method is called when Edit is clicked from a notification,
1298 -- Notification is always one of the notified activity,
1299 -- Fetching activity other than that of Ntf will give the notified activity of the txn thread.
1300 l_activityId := get_notified_activity(
1301 p_itemType => p_itemType
1302 ,p_itemKey => p_itemKey);
1303 --
1304
1305 -- In case of a txn send to approval and try to update we get null from
1306 -- get_notified_activity(p_itemType,p_itemKey), as it is a HR Block Activity
1307 -- and we r searching for Page Activities with FORM as value.
1308 -- We are calling overloaded call to get the blocked activity.
1309 if(l_activityId is null ) then
1310 l_activityId := get_notified_activity(
1311 p_itemType => p_itemType,
1312 p_itemKey => p_itemKey,
1313 p_ntfId => null);
1314 end if;
1315
1316
1317 -- Notified activity which must be completed to
1318 -- go the first page of the transaction and fetch it's activity id.
1319 if(l_activityId is not null) then
1320 complete_wf_activity (
1321 p_itemType => p_itemType,
1322 p_itemKey => p_itemKey,
1323 p_activity => l_activityId,
1324 p_otherAct => 'THIS',
1325 p_resultCode => 'REDO' );
1326 end if;
1327 --
1328 -- END IF;
1329 --
1330 END IF;
1331
1332 -- **********************************************
1333 -- Update transaction state : START
1334 IF (p_state = 'W' ) THEN
1335 if (p_revertFlag = 'Y' ) then
1336 l_newState := 'T' ; -- not null because on edit state is transient.
1337 else
1338 l_newState := p_state;
1339 end if;
1340 ELSE -- (if p_state is null or p_state = 'T')
1341 l_newState := 'T';
1342 END IF;
1343 --
1344 IF (p_state = 'W' AND p_revertFlag = 'Y' ) THEN
1345 --
1346 -- update_txn_current_values(p_txnId );
1347 -- cancel the user previous action and revert the data to
1348 -- previous good state
1349 hr_trans_history_api.cancel_action(p_txnId);
1350 --
1351 elsIF ( l_newState <> NVL(p_state,'**') ) THEN
1352 --
1353 -- update only if no revert is made
1354 hr_transaction_api.update_transaction(
1355 p_transaction_id => p_txnId,
1356 p_status => p_status,
1357 p_transaction_state => l_newState );
1358 END IF;
1359
1360
1361 -- Update transaction status : END
1362 -- **********************************************
1363 --
1364 --COMMIT;
1365
1366 --
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 hr_utility.trace(' exception in '||g_package||'.revert_to_last_save : ' || sqlerrm);
1370 raise;
1371 --
1372 END revert_to_last_save;
1373
1374
1375 /* ******************************************************
1376 -- Get Transaction Information
1377 -- This procedure is called from CompleteWorkflowCO, called when Edit
1378 -- link/button is pressed on the notification
1379 -- It should set appropriate Transient transaction status.
1380 ******************************************************* */
1381 PROCEDURE get_url_for_edit (
1382 p_itemType IN VARCHAR2
1383 ,p_itemKey IN VARCHAR2
1384 ,p_activityId OUT NOCOPY VARCHAR2
1385 ,p_functionName OUT NOCOPY VARCHAR2
1386 ,p_url OUT NOCOPY VARCHAR2 ) IS
1387 --
1388 l_applicationId NUMBER;
1389 l_regionCode VARCHAR2(30);
1390 l_newState VARCHAR2(5);
1391 l_activityLabel VARCHAR2(240);
1392 --l_savedStatus BOOLEAN := INSTR(p_status,'S') > 0;
1393 --
1394 BEGIN
1395 --
1396 -- fetch the current activity, from which the url will be built.
1397 /*
1398 p_activityId := get_notified_activity(
1399 p_itemType => p_itemType
1400 ,p_itemKey => p_itemKey
1401 ,p_ntfId => null);
1402 */
1403 p_activityId := get_notified_activity(
1404 p_itemType => p_itemType
1405 ,p_itemKey => p_itemKey);
1406 -- If attribute is not found, means the transaction was submitted and thus current activity is BLOCK
1407 -- Complete this activity with RESULT=REDO and fetch the attribute value from the current activity.
1408 l_regionCode := wf_engine.GetActivityAttrText(
1409 itemtype => p_itemType
1410 ,itemkey => p_itemKey
1411 ,actid => p_activityId
1412 ,aname => 'HR_ACTIVITY_TYPE_VALUE' );
1413 --
1414 DECLARE
1415 activity_attr_doesnot_exist exception;
1416 pragma exception_init(activity_attr_doesnot_exist, -20002);
1417 BEGIN
1418 l_applicationId := wf_engine.GetActivityAttrText(
1419 itemtype => p_itemType
1420 ,itemkey => p_itemKey
1421 ,actid => p_activityId
1422 ,aname => 'APPLICATION_ID' );
1423 IF l_applicationId IS NULL THEN
1424 l_applicationId := '800';
1425 END IF;
1426 EXCEPTION
1427 WHEN activity_attr_doesnot_exist THEN -- if APPLICATION_ID is not defined for the jsp page, set it to 800 by default
1428 l_applicationId := '800';
1429 WHEN OTHERS THEN
1430 raise;
1431 END;
1432 --
1433 p_functionName := wf_engine.GetItemAttrText(
1434 itemtype => p_itemType
1435 ,itemkey => p_itemKey
1436 ,aname => 'P_CALLED_FROM');
1437 --
1438 p_url := 'OA.jsp?akRegionApplicationId='||l_applicationId||'&akRegionCode='||l_regionCode||'&retainAM=Y&OAFunc='||p_functionName;
1439 --
1440 --
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 hr_utility.trace(' exception in '||g_package||'.get_url_for_edit : ' || sqlerrm);
1444 raise;
1445 --
1446 END get_url_for_edit;
1447 --
1448 --
1449 /* **************************************************************
1450 -- Set transaction Status
1451 --
1452 *************************************************************** */
1453
1454 PROCEDURE set_transaction_status (
1455 p_itemtype IN VARCHAR2,
1456 p_itemkey IN VARCHAR2,
1457 p_activityId IN VARCHAR2 DEFAULT NULL,
1458 p_action IN VARCHAR2,
1459 p_result OUT NOCOPY VARCHAR2 ) IS
1460 --
1461 --Pragma Autonomous_Transaction;
1462
1463 l_ReturnToUser VARCHAR2(340);
1464 l_CreatorUser VARCHAR2(340);
1465 l_transactionId NUMBER;
1466 l_status VARCHAR2(10);
1467 l_state VARCHAR2(5);
1468 l_newState VARCHAR2(5);
1469 l_newStatus VARCHAR2(10);
1470 --
1471 l_activity_id NUMBER; -- -- added for bug 12561019
1472 CURSOR cur_txn_status IS
1473 SELECT transaction_id, status, transaction_state
1474 FROM hr_api_transactions
1475 WHERE item_type = p_itemType
1476 AND item_key = p_itemKey;
1477 --
1478 BEGIN
1479 --
1480 l_activity_id := to_number(p_activityId); -- added for bug 12561019
1481 IF ( cur_txn_status%ISOPEN ) THEN
1482 CLOSE cur_txn_status;
1483 END IF;
1484 --
1485 OPEN cur_txn_status;
1486 FETCH cur_txn_status INTO l_transactionId, l_status, l_state;
1487 CLOSE cur_txn_status;
1488 --
1489 -- fix for bug 4886788
1490 -- need to close sfl ntf with new SFL model by default.
1491 IF l_transactionId is not null THEN
1492 hr_sflutil_ss.closeopensflnotification(l_transactionId);
1493 END IF;
1494
1495 IF (p_action = 'SFL') THEN
1496 -- If saving Supervisor page, then put add value history with step_id =0, no approval_history record
1497
1498 IF l_transactionId is null THEN
1499 p_result := 'COMPLETE:SUCCESS';
1500 RETURN;
1501 END IF;
1502
1503 -- Copy current values to previous values so they can be used to revert later (if needed)
1504 /* update_txn_previous_values (l_transactionId); */ -- ##history
1505 --
1506 -- Update original value in history table (LATEST_ORIGINAL_VALUE) -- ##history
1507 /* PQH_SS_HISTORY.track_original_value
1508 ( p_ItemType => p_itemType
1509 , p_itemKey => p_itemKey
1510 , p_action => 'SFL'
1511 , p_username => null
1512 , p_transactionId => l_transactionId); */
1513 --
1514 l_newState := null; -- New state will be null.
1515 IF ( l_status IN ('W','C','N','S')) THEN
1516 l_newStatus := 'S';
1517 ELSIF ( INSTR(l_status,'S') > 0 ) THEN -- If already saved txn then no change in status
1518 l_newStatus := l_status;
1519 ELSE
1520 l_newStatus := l_status||'S';
1521 END IF;
1522
1523
1524
1525 hr_transaction_api.update_transaction(
1526 p_transaction_id => l_transactionId,
1527 p_status => l_newStatus,
1528 p_transaction_state => l_newState );
1529
1530 hr_trans_history_api.archive_sfl(l_transactionId
1531 ,null
1532 ,FND_GLOBAL.user_name);
1533
1534
1535 --
1536 IF (l_status in ('Y','RI','RO')) THEN
1537 l_newStatus := wf_engine.eng_null;
1538 ELSE
1539 l_newStatus := 'SFL';
1540 END IF;
1541 complete_wf_activity (
1542 p_itemType => p_itemType
1543 ,p_itemKey => p_itemKey
1544 ,p_activity => l_activity_id -- added for bug 12561019
1545 ,p_otherAct => 'OTHER'
1546 ,p_resultCode => l_newStatus);
1547 --
1548 ELSE
1549 -- Get new Transaction Status
1550 IF (p_action = 'SUBMIT' ) THEN
1551 -- Copy current values to previous values so they can be used to revert later (if needed)
1552 -- update_txn_previous_values (l_transactionId); -- ##history
1553
1554
1555
1556 --
1557 l_newState := null; -- New state will be null.
1558 -- If transaction submitted or Edited and Submitted then set to Y
1559 l_newStatus := 'Y';
1560 hr_transaction_api.update_transaction(
1561 p_transaction_id => l_transactionId,
1562 p_status => l_newStatus,
1563 p_transaction_state => l_newState );
1564
1565 -- Note:- Initial submit archive is handled in the flow
1566 -- need to handle archive of resubmit
1567 if(l_status not in ('W','S')) then
1568 -- approver edit or rfc edit submit case
1569 hr_trans_history_api.archive_resubmit(l_transactionId,
1570 null,
1571 fnd_global.user_name,
1572 wf_engine.getitemattrtext(p_itemType,
1573 p_itemKey,
1574 'APPROVAL_COMMENT_COPY')
1575 );
1576
1577 end if;
1578
1579 ELSIF (p_action = 'APPROVE') THEN
1580 l_newStatus := 'Y';
1581 l_newState := null;
1582
1583 hr_transaction_api.update_transaction(
1584 p_transaction_id => l_transactionId,
1585 p_status => l_newStatus,
1586 p_transaction_state => l_newState );
1587
1588 --
1589 --
1590 hr_trans_history_api.archive_approve(l_transactionId,
1591 null,
1592 fnd_global.user_name,
1593 null); -- comments ??
1594
1595
1596 IF ( l_state IN ('T','W') ) THEN -- REDO only if transient or WIP
1597 IF (l_state = 'W') THEN
1598 -- ##history
1599 /*update_txn_current_values( l_transactionId );
1600 --
1601 reset_original_values (
1602 p_itemType => p_itemType
1603 ,p_itemKey => p_itemKey
1604 ,p_txnId => l_transactionId);*/
1605 null;
1606 --
1607 END IF;
1608 complete_wf_activity (
1609 p_itemType => p_itemType
1610 ,p_itemKey => p_itemKey
1611 ,p_activity => l_activity_id -- added for bug 12561019 -- not used because current act is fetched
1612 ,p_otherAct => 'CURR'
1613 ,p_commitFlag => 'Y'
1614 ,p_resultCode => 'REDO' );
1615 --
1616 END IF;
1617 --
1618 ELSIF (p_action = 'RFC' ) THEN
1619 -- Fetch the Return to Username and Creator User Name from WF
1620 BEGIN
1621 l_ReturnToUser :=
1622 wf_engine.GetItemAttrText(
1623 itemtype => p_itemType,
1624 itemkey => p_itemKey,
1625 aname => 'RETURN_TO_USERNAME');
1626 EXCEPTION
1627 WHEN OTHERS THEN -- if attributes are not found then don't do anything
1628 null;
1629 END;
1630
1631 IF (l_ReturnToUSer IS NULL ) THEN
1632 --For customized processes still using the old approval process
1633 --the status is set to C, so that they are listed in
1634 --in pending actions table.
1635 l_newStatus := 'C';
1636 -- Bug 3031918: TRAN_SUBMIT need to be set to C for v4 processes
1637 -- on Return for Correction
1638 wf_engine.SetItemAttrText(
1639 itemtype => p_itemType,
1640 itemkey => p_itemKey,
1641 aname => 'TRAN_SUBMIT',
1642 avalue => l_newStatus );
1643
1644 ELSE
1645 l_CreatorUser :=
1646 wf_engine.GetItemAttrText(
1647 itemtype => p_itemType,
1648 itemkey => p_itemKey,
1649 aname => 'CREATOR_PERSON_USERNAME');
1650 --
1651 IF (l_ReturnToUSer = l_CreatorUser ) THEN -- Return for correction to initiator
1652 l_newStatus := 'RI';
1653 ELSE -- Returned for Correction to other manager
1654 l_newStatus := 'RO';
1655 END IF;
1656 END IF;
1657 l_newState := null; -- no change to state.
1658 hr_transaction_api.update_transaction(
1659 p_transaction_id => l_transactionId,
1660 p_status => l_newStatus,
1661 p_transaction_state => l_newState );
1662 -- update the transaction state table too
1663 pqh_tsh_upd.upd(p_transaction_history_id=>l_transactionId,
1664 p_approval_history_id=>hr_trans_history_api.gettransstatesequence(l_transactionId),
1665 p_status=>l_newStatus,
1666 p_transaction_state=>l_newState);
1667
1668 END IF;
1669 --
1670 /*hr_transaction_api.update_transaction(
1671 p_transaction_id => l_transactionId,
1672 p_status => l_newStatus,
1673 p_transaction_state => l_newState );*/
1674 wf_engine.SetItemAttrText(
1675 itemtype => p_itemType,
1676 itemkey => p_itemKey,
1677 aname => 'TRAN_SUBMIT',
1678 avalue => l_newStatus );
1679 --
1680 END IF;
1681 p_result := 'COMPLETE:SUCCESS';
1682 --
1683 --
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686 hr_utility.trace(' exception in '||g_package||'.set_transaction_status : ' || sqlerrm);
1687 raise;
1688 --
1689 END set_transaction_status;
1690
1691 --
1692 --
1693 -- Bug 3018784: This procedure is nullified in ver115.34 (hrssa.wft must be 115.196 and above)
1694 -- as it is no longer called from the workflow process.
1695 PROCEDURE set_txn_submit_status (
1696 itemtype IN VARCHAR2,
1697 itemkey IN VARCHAR2,
1698 actid IN NUMBER,
1699 funcmode IN VARCHAR2,
1700 result OUT NOCOPY VARCHAR2 ) IS
1701 --
1702 BEGIN
1703 --
1704 result := 'COMPLETE:SUCCESS';
1705 --
1706 END set_txn_submit_status;
1707 --
1708 --
1709 PROCEDURE set_txn_approve_status (
1710 itemtype IN VARCHAR2,
1711 itemkey IN VARCHAR2,
1712 actid IN NUMBER,
1713 funcmode IN VARCHAR2,
1714 result OUT NOCOPY VARCHAR2 ) IS
1715 --
1716 BEGIN
1717 --
1718 IF (funcmode = wf_engine.eng_run) THEN
1719 --
1720 set_transaction_status (
1721 p_itemType => itemType
1722 ,p_itemKey => itemKey
1723 ,p_action => 'APPROVE'
1724 ,p_result => result );
1725 --
1726 END IF;
1727 --
1728 --
1729 EXCEPTION
1730 WHEN OTHERS THEN
1731 hr_utility.trace(' exception in '||g_package||'.set_txn_approve_status : ' || sqlerrm);
1732 Wf_Core.Context(g_package, 'set_txn_approve_status', itemType, itemKey);
1733 raise;
1734 --
1735 END set_txn_approve_status;
1736 --
1737 --
1738 PROCEDURE set_txn_rfc_status (
1739 itemtype IN VARCHAR2,
1740 itemkey IN VARCHAR2,
1741 actid IN NUMBER,
1742 funcmode IN VARCHAR2,
1743 result OUT NOCOPY VARCHAR2 ) IS
1744 --
1745 BEGIN
1746 --
1747 IF (funcmode = wf_engine.eng_run) THEN
1748 --
1749 set_transaction_status (
1750 p_itemType => itemType
1751 ,p_itemKey => itemKey
1752 ,p_action => 'RFC'
1753 ,p_result => result );
1754 --
1755 END IF;
1756
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 hr_utility.trace(' exception in '||g_package||'.set_txn_rfc_status : ' || sqlerrm);
1760 Wf_Core.Context(g_package, 'set_txn_rfc_status', itemType, itemKey);
1761 raise;
1762 --
1763 END set_txn_rfc_status;
1764 --
1765 PROCEDURE set_txn_sfl_status (
1766 itemtype IN VARCHAR2,
1767 itemkey IN VARCHAR2,
1768 actid IN NUMBER,
1769 funcmode IN VARCHAR2,
1770 result OUT NOCOPY VARCHAR2 ) IS
1771 --
1772 BEGIN
1773 --
1774 IF (funcmode = wf_engine.eng_run) THEN
1775 --
1776 /* Fix for bug# 3389563
1777 The calls will be part of Java calls.
1778 This procedure should not be invoked from with in any Workflow process.
1779 set_transaction_status (
1780 p_itemType => itemType
1781 ,p_itemKey => itemKey
1782 ,p_activityId=> actId
1783 ,p_action => 'SFL'
1784 ,p_result => result );
1785 */
1786 result:= wf_engine.eng_trans_default;
1787 --
1788 END IF;
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 hr_utility.trace(' exception in '||g_package||'.set_txn_sfl_status : ' || sqlerrm);
1793 Wf_Core.Context(g_package, 'set_txn_sfl_status', itemType, itemKey);
1794 raise;
1795 END set_txn_sfl_status;
1796 --
1797
1798 --
1799 -- This procedure sets the effective date and date option in transaction table
1800 -- as well as the transaction_values table.
1801 PROCEDURE set_effective_date_and_option (
1802 p_txnId IN VARCHAR2
1803 ,p_effectiveDate IN DATE
1804 ,p_effectiveDateOption IN VARCHAR2 ) IS
1805 --
1806 CURSOR cur_asgn IS
1807 SELECT api_name,transaction_step_id
1808 FROM hr_api_transaction_steps
1809 WHERE transaction_id = p_txnId;
1810 --
1811 -- AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
1812 --
1813 l_txnStepId NUMBER(15);
1814 l_apiName VARCHAR2(300);
1815 l_effectiveDate DATE := p_effectiveDate;
1816 BEGIN
1817 --
1818 IF ( NVL(p_effectiveDateOption,'E') = 'A') THEN
1819 l_effectiveDate := sysdate;
1820 END IF;
1821
1822 HR_TRANSACTION_API.update_transaction (
1823 p_transaction_id => p_txnId
1824 ,p_transaction_effective_date => l_effectiveDate
1825 ,p_effective_date_option => NVL(p_effectiveDateOption,'E') );
1826 --
1827
1828 FOR I IN cur_asgn
1829 LOOP
1830 IF (I.api_name = 'HR_SUPERVISOR_SS.PROCESS_API' ) THEN
1831 HR_TRANSACTION_API.set_date_value (
1832 p_transaction_step_id => I.transaction_step_id
1833 ,p_person_id => null
1834 ,p_name => 'P_PASSED_EFFECTIVE_DATE'
1835 ,p_value => l_effectiveDate );
1836
1837 /*
1838 -- ====================================================================
1839 -- Also set the effective dates to system date if date option is
1840 -- as of approval for following attributes
1841 -- P_PASSED_EFFECTIVE_DATE : for selected person's reassign date
1842 -- P_EFFECTIVE_DATE_<n> : for reassigned employees (n is 1,2,3..)
1843 -- P_EMP_DATE_<n> : for newly assigned employees(n is 1,2,3..)
1844 -- P_SINGLE_EFFECTIVE_DATE : ????
1845 -- Note: Termination step may also have to be added in case the
1846 -- Notification date is to be set to as of approval date
1847 -- ====================================================================
1848 IF ( NVL(p_effectiveDateOption,'E') = 'A' ) THEN
1849 UPDATE hr_api_transaction_values
1850 SET date_value = l_effectiveDate
1851 WHERE transaction_step_id = I.transaction_step_id
1852 AND name like 'P%DATE%';
1853 END IF;
1854 --
1855 */
1856 ELSE
1857 --
1858 --
1859 HR_TRANSACTION_API.set_date_value (
1860 p_transaction_step_id => I.transaction_step_id
1861 ,p_person_id => null -- used to validate, no other usage
1862 ,p_name => 'P_EFFECTIVE_DATE'
1863 ,p_value => p_effectiveDate );
1864 --
1865 HR_TRANSACTION_API.set_varchar2_value (
1866 p_transaction_step_id => I.transaction_step_id
1867 ,p_person_id => null -- used to validate, no other usage
1868 ,p_name => 'P_EFFECTIVE_DATE_OPTION'
1869 ,p_value => NVL(p_effectiveDateOption,'E') );
1870
1871 END IF;
1872 END LOOP;
1873 --
1874 --
1875 EXCEPTION
1876 WHEN OTHERS THEN
1877 hr_utility.trace(' exception in '||g_package||'.set_effective_date_and_option : ' || sqlerrm);
1878 raise;
1879 --
1880 END set_effective_date_and_option;
1881 --
1882 --
1883 -- Called from Workflow Cancel Activity
1884 PROCEDURE reset_txn_current_values (
1885 itemtype IN VARCHAR2,
1886 itemkey IN VARCHAR2,
1887 actid IN NUMBER,
1888 funcmode IN VARCHAR2,
1889 result OUT NOCOPY VARCHAR2 ) IS
1890 --
1891 l_transactionId NUMBER(15);
1892 l_ntfId NUMBER(15);
1893 l_activityLabel VARCHAR2(240);
1894 l_status VARCHAR2(10);
1895 l_step_id NUMBER(15);
1896 --
1897 -- CURSOR cur_status IS
1898 -- SELECT status
1899 -- FROM hr_api_transactions
1900 -- WHERE transaction_id = l_transactionId;
1901 BEGIN
1902 IF (funcmode <> wf_engine.eng_run) THEN
1903 result := wf_engine.eng_null;
1904 RETURN;
1905 END IF;
1906
1907 l_transactionId := get_transaction_id (
1908 p_itemType => itemType
1909 ,p_itemKey => itemKey );
1910
1911 -- if transactionid is null, nothing is saved yet
1912 -- no action needed, just complete the current workflow activity.
1913 IF (l_transactionId IS NOT null ) THEN
1914 l_ntfId := get_notification_id(
1915 p_itemType => itemType
1916 ,p_itemKey => itemKey);
1917
1918
1919 -- need to check status too
1920 begin
1921 select status into l_status
1922 from hr_api_transactions
1923 where transaction_id=l_transactionId;
1924 exception
1925 when others then
1926 null;
1927 end;
1928 --
1929
1930 -- Remove transaction if it's a new one, else revert back to previous values
1931 IF ( l_ntfId IS NULL and l_status='W' ) THEN
1932 hr_transaction_api.rollback_transaction
1933 (p_transaction_id => l_transactionId );
1934
1935 --3099089 change starts
1936 --need to remove transaction_id from wf_item_attribute_values also
1937 wf_engine.setitemattrnumber
1938 (itemtype => itemtype
1939 ,itemkey => itemkey
1940 ,aname => 'TRANSACTION_ID'
1941 ,avalue => null);
1942 --3099089 change ends
1943
1944 result := 'COMPLETE:CANCELLED';
1945 RETURN;
1946 ELSE
1947 -- IF ( cur_status%ISOPEN ) THEN
1948 -- CLOSE cur_status;
1949 -- END IF;
1950 --
1951 -- OPEN cur_status;
1952 -- FETCH cur_status INTO l_status;
1953 -- CLOSE cur_status;
1954
1955
1956 -- add new call to archive api to revert to last save
1957 hr_trans_history_api.cancel_action(l_transactionId);
1958 --
1959 --
1960 --vegopala fix for bug 5436747.Commenting as it is not
1961 -- reqd to set to T.
1962 -- Set the state to T once the activities are completed.
1963 --
1964 /* hr_transaction_api.update_transaction (
1965 p_transaction_id => l_transactionId
1966 ,p_transaction_state => 'T' );
1967 */
1968 --
1969 result := 'COMPLETE:ACCEPTED';
1970 RETURN;
1971 END IF; -- notification not null
1972 END IF; -- txn not null
1973 -- default result
1974 result := 'COMPLETE:CANCELLED';
1975 --
1976 --
1977 --
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 hr_utility.trace(' exception in '||g_package||'.reset_txn_current_values : ' || sqlerrm);
1981 Wf_Core.Context(g_package, 'reset_txn_current_values', itemType, itemKey);
1982 raise;
1983 --
1984 END reset_txn_current_values;
1985
1986
1987 --
1988 --
1989 PROCEDURE check_initial_save_for_later (
1990 itemtype IN VARCHAR2,
1991 itemkey IN VARCHAR2,
1992 actid IN NUMBER,
1993 funcmode IN VARCHAR2,
1994 result OUT NOCOPY VARCHAR2 ) IS
1995 --
1996 -- ItemType aNd ItemKey are for the approval wf process
1997 CURSOR cur_txn IS
1998 SELECT NVL(status,'N')
1999 FROM hr_api_transactions
2000 WHERE item_type = itemType
2001 AND item_key = itemKey;
2002 l_status VARCHAR2(10);
2003 --
2004 BEGIN
2005 --
2006 IF (funcmode = wf_engine.eng_run ) THEN
2007
2008 /*
2009 07-May-2003 ns moved to approval_block
2010 -- Set the Approval process version to distinguish it
2011 -- it from old approval process.
2012 wf_engine.SetItemAttrText(
2013 itemtype => itemType
2014 , itemkey => itemKey
2015 , aname => 'HR_APPROVAL_PRC_VERSION'
2016 , avalue => 'V5' );
2017 --
2018 */
2019 IF ( cur_txn%ISOPEN ) THEN
2020 CLOSE cur_txn;
2021 END IF;
2022 --
2023 OPEN cur_txn;
2024 FETCH cur_txn INTO l_status;
2025 CLOSE cur_txn;
2026 --
2027 IF l_status in ('S') THEN -- Saved for Later (not yet Submitted)
2028 result := 'COMPLETE:T';
2029 ELSE
2030 result := 'COMPLETE:F';
2031 END IF;
2032 --
2033 END IF;
2034 --
2035 --
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 hr_utility.trace(' exception in '||g_package||'.check_initial_save_for_later : ' || sqlerrm);
2039 Wf_Core.Context(g_package, 'check_initial_save_for_later', itemType, itemKey);
2040 raise;
2041 --
2042 END check_initial_save_for_later;
2043 --
2044 --
2045 /* Return_For_Correction to the user passed by the calling method returnForCorrection
2046 * in ReturnForCorrectionAMImpl
2047 * Parameters:
2048 * p_userName - Used to set FROM_ROLE attribute of the notification
2049 * p_userDisplayName - Used in FYI notification send to initiator when RFC to
2050 * other than Initiator
2051 * p_approverIndex - Selected users index in the approval chain, to set wf attribute
2052 * p_txnId - To fetch last default approver before the approver who is performing RFC
2053 */
2054 PROCEDURE return_for_correction (
2055 p_itemType IN VARCHAR2
2056 , p_itemKey IN VARCHAR2
2057 , p_userId IN VARCHAR2 -- NOTE: not really userid, it is the personId
2058 , p_userName IN VARCHAR2
2059 , p_userDisplayName IN VARCHAR2
2060 , p_ntfId IN VARCHAR2
2061 , p_note IN VARCHAR2
2062 , p_approverIndex IN NUMBER
2063 , p_txnId IN VARCHAR2) IS
2064 --
2065 l_activity NUMBER;
2066 l_itemType VARCHAR2(30);
2067 l_itemKey VARCHAR2(30);
2068 l_userName FND_USER.user_name%Type;
2069 l_ntfId NUMBER;
2070 l_lastDefaultApprover NUMBER;
2071 dummy varchar2(10);
2072
2073 -- Cursor to find if the person (selected for RFC) is an additional approver
2074 CURSOR cur_add_appr IS
2075 SELECT 'X'
2076 FROM wf_item_attribute_values
2077 WHERE item_type = p_itemType
2078 AND item_key = p_itemKey
2079 AND name like 'ADDITIONAL_APPROVER_%'
2080 AND number_value = p_userId;
2081 --
2082 -- Cursor to fetch the last default approver below the person performing
2083 -- RFC. It is used only in case of NON-AME approvals.
2084 --
2085 CURSOR cur_appr IS
2086 SELECT pth.employee_id
2087 FROM pqh_ss_approval_history pah,
2088 fnd_user pth
2089 WHERE pah.user_name = pth.user_name
2090 AND pah.transaction_history_id = p_txnId
2091 AND approval_history_id = (
2092 SELECT MAX(approval_history_id)
2093 FROM pqh_ss_approval_history pah1,
2094 fnd_user pth1
2095 WHERE pah1.user_name = pth1.user_name
2096 AND pah1.transaction_history_id = pah.transaction_history_id
2097 AND pth1.employee_id IN (
2098 SELECT pth2.employee_id --, pth2.user_name, approval_history_id
2099 FROM pqh_ss_approval_history pah2,
2100 fnd_user pth2
2101 WHERE pah2.user_name = pth2.user_name
2102 AND pah2.transaction_history_id = pah.transaction_history_id
2103 AND approval_history_id < (
2104 SELECT MIN(approval_history_id)
2105 FROM pqh_ss_approval_history
2106 WHERE transaction_history_id = pah.transaction_history_id
2107 AND user_name = p_userName
2108 AND approval_history_id > 0
2109 )
2110 and approval_history_id > 0
2111 MINUS
2112 SELECT number_value
2113 FROM wf_item_attribute_values
2114 WHERE item_type = p_itemType
2115 AND item_key = p_itemKey
2116 AND name like 'ADDITIONAL_APPROVER_%'
2117 )
2118 );
2119 --
2120 BEGIN
2121 --
2122 Rollback; -- Needed in case of New Hire and CWK to that the changes are not committed.
2123
2124 -- fix for bug 4454439
2125 begin
2126 -- re-intialize the performer roles
2127 hr_approval_ss.reinitperformerroles(p_notification_id=>null
2128 ,p_transaction_id=>null
2129 ,p_item_type=>p_itemType
2130 ,p_item_key=>p_itemKey);
2131 exception
2132 when others then
2133 null;
2134 end;
2135
2136 IF ( p_itemType IS NULL OR p_itemKey IS NULL OR p_userName IS NULL ) THEN
2137 get_item_type_and_key (
2138 p_ntfId => p_ntfId
2139 ,p_itemType => l_itemType
2140 ,p_itemKey => l_itemKey );
2141
2142 -- fix for bug 4454439
2143 begin
2144 -- re-intialize the performer roles
2145 hr_approval_ss.reinitperformerroles(p_notification_id=>null
2146 ,p_transaction_id=>null
2147 ,p_item_type=>l_itemType
2148 ,p_item_key=>l_itemKey);
2149 exception
2150 when others then
2151 null;
2152 end;
2153
2154
2155 l_userName := wf_engine.GetItemAttrText(
2156 itemtype => l_itemType
2157 , itemkey => l_itemKey
2158 , aname => 'CREATOR_PERSON_USERNAME');
2159 ELSE
2160 l_itemType := p_itemType;
2161 l_itemKey := p_itemKey;
2162 --l_userName := p_userName; -- fix for bug 4481775
2163 if(p_userId is not null) then
2164 select user_name
2165 into l_userName
2166 from fnd_user
2167 where employee_id=p_userId;
2168 end if;
2169
2170 END IF;
2171
2172 -- Set the item attribute for the return to user name.
2173 wf_engine.SetItemAttrText(
2174 itemtype => l_itemType
2175 , itemkey => l_itemKey
2176 , aname => 'RETURN_TO_USERNAME'
2177 , avalue => l_userName );
2178 --
2179 hr_approval_wf.create_item_attrib_if_notexist(
2180 p_item_type => l_itemtype
2181 ,p_item_key => l_itemkey
2182 ,p_name => 'RETURN_TO_PERSON_DISPLAY_NAME');
2183
2184 wf_engine.SetItemAttrText(
2185 itemtype => l_itemType
2186 , itemkey => l_itemKey
2187 , aname => 'RETURN_TO_PERSON_DISPLAY_NAME'
2188 , avalue => p_userDisplayName );
2189 --
2190 -- Fetch the activity id of notification to be completed
2191 l_activity := get_notified_activity(
2192 p_itemType => l_itemType
2193 ,p_itemKey => l_itemKey
2194 ,p_ntfId => p_ntfId );
2195 --
2196 -- Set the notes for RFC notification.
2197 wf_engine.setItemAttrText (
2198 itemtype => l_itemType
2199 ,itemkey => l_itemKey
2200 ,aname => 'NOTE_FROM_APPR'
2201 ,avalue => p_note );
2202
2203 wf_engine.setItemAttrText (
2204 itemtype => l_itemType
2205 ,itemkey => l_itemKey
2206 ,aname => 'WF_NOTE'
2207 ,avalue => NULL );
2208
2209
2210 wf_notification.setattrtext(
2211 p_ntfId
2212 ,'RESULT'
2213 ,'RETURNEDFORCORRECTION');
2214 BEGIN
2215 wf_notification.setattrtext(
2216 p_ntfId
2217 ,'WF_NOTE'
2218 ,p_note);
2219 EXCEPTION WHEN OTHERS THEN
2220 -- RFC from SFL Other
2221 wf_notification.propagatehistory(
2222 l_itemType
2223 ,l_itemKey
2224 ,'APPROVAL_NOTIFICATION'
2225 ,fnd_global.user_name
2226 ,l_userName
2227 ,'RETURNEDFORCORRECTION'
2228 ,null
2229 ,p_note);
2230 END;
2231
2232 -- Send Notification
2233
2234 wf_notification.respond(
2235 p_ntfId
2236 ,null
2237 ,fnd_global.user_name
2238 ,null);
2239
2240 -- Fetch the id for RFC notification.
2241 l_ntfId := get_notification_id(
2242 p_itemType => l_itemType
2243 ,p_itemKey => l_itemKey);
2244
2245
2246 -- Set the from attribute for RFC notification.
2247 wf_notification.setAttrText(
2248 nid => l_ntfId
2249 ,aname => '#FROM_ROLE'
2250 ,avalue => fnd_global.user_name );
2251
2252 -- Set the two workflow attributes for Non-AME approval process
2253 IF (wf_engine.GetItemAttrText(itemtype => l_itemType ,
2254 itemkey => l_itemKey,
2255 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NULL) THEN
2256 -- CURRENT_APPROVER_INDEX
2257 begin
2258 -- set the attribute value to null
2259 wf_engine.SetItemAttrNumber(
2260 itemtype => l_itemType ,
2261 itemkey => l_itemKey,
2262 aname => 'CURRENT_APPROVER_INDEX',
2263 avalue => p_approverIndex);
2264 exception
2265 when others then null;
2266 end;
2267
2268 -- Set LAST_DEFAULT_APPROVER
2269 begin
2270
2271 --
2272 -- If the selected person (for RFC) is additional approver
2273 -- then fetch the last default approver from history
2274 -- else selected person is the last default approver
2275 IF ( cur_add_appr%ISOPEN ) THEN
2276 CLOSE cur_add_appr;
2277 END IF;
2278 --
2279 OPEN cur_add_appr;
2280 FETCH cur_add_appr INTO dummy;
2281 if cur_add_appr%found then
2282 --
2283 IF ( cur_appr%ISOPEN ) THEN
2284 CLOSE cur_appr;
2285 END IF;
2286 --
2287 OPEN cur_appr;
2288 FETCH cur_appr INTO l_lastDefaultApprover;
2289 CLOSE cur_appr;
2290 --
2291 else
2292 l_lastDefaultApprover := p_userId;
2293 end if;
2294
2295 CLOSE cur_add_appr;
2296
2297 IF ( l_lastDefaultApprover IS NOT NULL ) THEN
2298 wf_engine.SetItemAttrNumber(
2299 itemtype => l_itemType ,
2300 itemkey => l_itemKey,
2301 aname => 'LAST_DEFAULT_APPROVER',
2302 avalue => l_lastDefaultApprover);
2303 END IF;
2304 exception
2305 when others then null;
2306 end;
2307 END IF; -- Non-AME approval
2308
2309 --Commit is needed otherwise from role and notes will not appear for the notification.
2310 COMMIT;
2311 --
2312 EXCEPTION
2313 WHEN OTHERS THEN
2314 hr_utility.trace(' exception in '||g_package||'.return_for_correction : ' || sqlerrm);
2315 raise;
2316 --
2317 END return_for_correction;
2318 --
2319 -- Local function to return the message_name for the notification
2320 FUNCTION get_message_attr_name(
2321 p_ntfId in number
2322 ,p_itemType in varchar2) return varchar2 IS
2323 --
2324 CURSOR cur_ntf IS
2325 SELECT wma.display_name
2326 FROM wf_notifications wn, wf_message_attributes_vl wma
2327 WHERE wn.notification_id = p_ntfId
2328 AND wn.message_name = wma.message_name
2329 AND wma.message_type = p_itemType
2330 AND wma.name = 'EDIT_TXN_URL';
2331 --
2332 l_messageAttr varchar2(4000);
2333 BEGIN
2334 --
2335 IF ( cur_ntf%ISOPEN ) THEN
2336 CLOSE cur_ntf;
2337 END IF;
2338 --
2339 OPEN cur_ntf;
2340 FETCH cur_ntf INTO l_messageAttr;
2341 CLOSE cur_ntf ;
2342 --
2343 RETURN l_messageAttr;
2344 --
2345 END get_message_attr_name;
2346 --
2347 --
2348 PROCEDURE get_edit_link(
2349 document_id in varchar2,
2350 display_type in varchar2,
2351 document in out nocopy varchar2,
2352 document_type in out nocopy varchar2) IS
2353 --
2354 --
2355 l_profileValue VARCHAR2(1);
2356 -- l_profileName VARCHAR2(30);
2357 l_ntfId NUMBER(15);
2358 l_itemType VARCHAR2(30);
2359 l_itemKey VARCHAR2(30);
2360 -- l_effectiveDate VARCHAR2(20);
2361 -- l_personId VARCHAR2(15);
2362 -- l_assignmentId VARCHAR2(15);
2363 l_editUrl VARCHAR2(400);
2364 l_urlLabel VARCHAR2(80);
2365 l_checkProfile VARCHAR2(10);
2366 l_status VARCHAR2(10);
2367 --
2368 CURSOR cur_txn (c_itemType VARCHAR2, c_itemKey VARCHAR2) IS
2369 SELECT -- to_char(NVL(transaction_effective_date,sysdate),g_date_format),
2370 -- assignment_id, selected_person_id,
2371 NVL(status,'N'), NVL(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N')
2372 FROM hr_api_transactions
2373 WHERE item_type = c_itemType
2374 AND item_key = c_itemKey;
2375 --
2376 BEGIN
2377 document_type := wf_notification.doc_html;
2378 l_ntfId := document_id;
2379
2380 -- l_ntfId := SUBSTR(document_id,1,INSTR(document_id,':')-1);
2381 -- l_checkProfile := SUBSTR(document_id,INSTR(document_id,':')+1);
2382
2383 -- IF (l_checkProfile = 'Y' ) THEN
2384 -- l_profileValue := NVL(fnd_profile.value(l_profileName),'N');
2385 -- END IF;
2386
2387 --dbms_output.put_line('NtfId: '||l_ntfId);
2388
2389 get_item_type_and_key (
2390 p_ntfId => l_ntfId
2391 ,p_itemType => l_itemType
2392 ,p_itemKey => l_itemKey );
2393 --
2394 --dbms_output.put_line('itemType: '||l_itemType||' itemKey: '||l_itemKey);
2395 IF ( cur_txn%ISOPEN ) THEN
2396 CLOSE cur_txn;
2397 END IF;
2398 --
2399 OPEN cur_txn (l_itemType, l_itemKey);
2400 FETCH cur_txn INTO l_status, l_profileValue;
2401 -- FETCH cur_txn INTO l_effectiveDate, l_assignmentId, l_personId, l_status, l_profileValue;
2402 CLOSE cur_txn;
2403 --
2404 --
2405 -- No need to check profile option (i.e. must render edit link)
2406 -- in following cases
2407 IF (INSTR(l_status,'S') > 0 OR l_status IN ('RI','N','C','W') ) THEN
2408 l_checkProfile := 'N';
2409 END IF;
2410
2411 IF (l_checkProfile = 'N' OR l_profileValue ='Y' ) THEN
2412 l_urlLabel := get_message_attr_name (
2413 p_ntfId => l_ntfId
2414 ,p_itemType => l_itemType );
2415
2416
2417 l_editUrl := '<a href='||g_OA_HTML||
2418 'OA.jsp?page=/oracle/apps/pqh/selfservice/common/webui/EffectiveDatePG&retainAM=Y&NtfId=&#NID>'||
2419 l_urlLabel||'</a>';
2420 document :=
2421 '<tr><td> '||
2422 '<IMG SRC="'||g_OA_MEDIA||'afedit.gif"/>'||
2423 '</td><td>'||
2424 l_editUrl||
2425 '</td></tr> ';
2426
2427 ELSE
2428 document := null;
2429 END IF;
2430 --
2431 EXCEPTION
2432 WHEN OTHERS THEN
2433 hr_utility.trace(' exception in '||g_package||'.get_edit_link : ' || sqlerrm);
2434 raise;
2435 --
2436 END get_edit_link;
2437 --
2438 --
2439 -- This procedure will check if the date option is A - As of final approval
2440 -- it will set the effective date to the system date.
2441 PROCEDURE set_date_if_as_of_approval (
2442 itemtype IN VARCHAR2,
2443 itemkey IN VARCHAR2,
2444 actid IN NUMBER,
2445 funcmode IN VARCHAR2,
2446 result OUT NOCOPY VARCHAR2 ) IS
2447 --
2448 CURSOR cur_txn IS
2449 SELECT transaction_id, NVL(effective_date_option,'X')
2450 FROM hr_api_transactions
2451 WHERE item_type = itemType
2452 AND item_key = itemKey;
2453 --
2454 l_txnId NUMBER(15);
2455 l_effDateOption VARCHAR2(10);
2456 l_effectiveDate DATE;
2457 --
2458 BEGIN
2459 --
2460 IF ( cur_txn%ISOPEN ) THEN
2461 CLOSE cur_txn;
2462 END IF;
2463 --
2464 OPEN cur_txn;
2465 FETCH cur_txn INTO l_txnId, l_effDateOption;
2466 CLOSE cur_txn;
2467
2468 IF ( l_effDateOption = 'A' ) THEN
2469 l_effectiveDate := sysdate;
2470
2471 HR_TRANSACTION_API.update_transaction (
2472 p_transaction_id => l_txnId
2473 ,p_transaction_effective_date => l_effectiveDate );
2474 --
2475 wf_engine.setItemAttrText
2476 (itemtype => itemType
2477 ,itemkey => itemKey
2478 ,aname => 'P_EFFECTIVE_DATE'
2479 ,avalue => TO_CHAR(l_effectiveDate,g_date_format) );
2480 -- Bug 4243314 starts.
2481 wf_engine.setItemAttrDate
2482 (itemtype => itemType
2483 ,itemkey => itemKey
2484 ,aname => 'CURRENT_EFFECTIVE_DATE'
2485 ,avalue =>l_effectiveDate);
2486 -- Bug 4243314 ends.
2487 --
2488 --
2489 -- ==================================================================
2490 -- Bug 3044048: On final approval set sysdate as, As Of Approval Date
2491 -- If it is decided that reassigned and newly assigned reports are to
2492 -- have current date if as of approval is chosen, then use the where
2493 -- clause name like 'P%DATE%'.
2494 -- If it is decided that notified date must be as of approval then
2495 -- then add the where clause api_name = 'HR_TERMINATION_SS.PROCESS_API'
2496 -- ==================================================================
2497 UPDATE hr_api_transaction_values
2498 SET date_value = l_effectiveDate
2499 WHERE datatype = 'DATE'
2500 AND name = 'P_PASSED_EFFECTIVE_DATE'
2501 AND transaction_step_id = (
2502 SELECT transaction_step_id
2503 FROM hr_api_transaction_steps
2504 WHERE transaction_id = l_txnId
2505 AND api_name = 'HR_SUPERVISOR_SS.PROCESS_API' );
2506 --
2507 END IF;
2508 --
2509 --
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 hr_utility.trace(' exception in '||g_package||'.set_date_if_as_of_approval : ' || sqlerrm);
2513 Wf_Core.Context(g_package, 'set_date_if_as_of_approval', itemType, itemKey);
2514 raise;
2515 --
2516 END set_date_if_as_of_approval;
2517 --
2518 FUNCTION add_message (
2519 p_message_type IN VARCHAR2
2520 ,p_apps_short_name IN VARCHAR2
2521 ,p_message_name IN VARCHAR2
2522 ,p_called_from IN VARCHAR2
2523 ,p_addToPub IN VARCHAR2 DEFAULT 'NO'
2524 ,p_token_name IN VARCHAR2 DEFAULT NULL
2525 ,p_token_value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
2526 --
2527 l_message VARCHAR2(4000);
2528 l_text VARCHAR2(8000);
2529 l_icon VARCHAR2(40);
2530 l_css VARCHAR2(40);
2531 ln_app_id fnd_application.application_id%type;
2532 cursor c1 is
2533 select fa.application_id
2534 from fnd_application fa
2535 where fa.application_short_name = p_apps_short_name ;
2536
2537 BEGIN
2538 --
2539 fnd_message.set_name(p_apps_short_name,p_message_name);
2540 -- l_message := fnd_message.get_string(p_apps_short_name,p_message_name);
2541 IF (p_token_name IS NOT NULL AND p_token_value IS NOT NULL ) THEN
2542 fnd_message.set_token(p_token_name,p_token_value);
2543 END IF;
2544 l_message := fnd_message.get;
2545 --
2546 -- No need to format the message if called from final validation
2547 IF (NVL(p_called_from,'X') = 'FINAL_VALIDATION') THEN
2548 return l_message;
2549 END IF;
2550
2551 IF (p_message_type = 'ERR') THEN
2552 l_icon := 'erroricon_active.gif';
2553 l_css := 'OraErrorText';
2554 ELSE
2555 l_icon := 'warningicons_active.gif';
2556 l_css := 'OraTipText';
2557 END IF;
2558
2559 l_text := '<tr><td> '||
2560 '<IMG SRC="'||g_OA_MEDIA||l_icon||'"/>'||
2561 '</td><td>'||
2562 '<a class='||l_css||'>'||l_message||'</a>'||
2563 '</td></tr> ';
2564
2565 IF (p_addToPub <> 'NO') THEN
2566 open c1 ;
2567 fetch c1 into ln_app_id ;
2568 close c1 ;
2569 hr_utility.set_message(ln_app_id,p_message_name);
2570 IF (p_token_name IS NOT NULL AND p_token_value IS NOT NULL ) THEN
2571 hr_utility.set_message_token(p_token_name,p_token_value);
2572 END IF;
2573 IF ( p_message_type = 'ERR') THEN
2574 hr_multi_message.add( p_message_type => hr_multi_message.G_ERROR_MSG);
2575 ELSE
2576 hr_multi_message.add( p_message_type => hr_multi_message.G_WARNING_MSG);
2577 END IF;
2578 END IF;
2579
2580 return l_text;
2581 --
2582 EXCEPTION
2583 WHEN OTHERS THEN
2584 hr_utility.trace(' exception in '||g_package||'.add_message : ' || sqlerrm);
2585 raise;
2586 --
2587 END add_message;
2588 --
2589
2590 FUNCTION get_errors_and_warnings (
2591 p_itemType IN VARCHAR2,
2592 p_itemKey IN VARCHAR2,
2593 p_calledFrom IN VARCHAR2 DEFAULT NULL,
2594 p_addToPub IN VARCHAR2 DEFAULT 'NO',
2595 p_sendToHr OUT NOCOPY VARCHAR2,
2596 p_hasErrors OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2597 --
2598 l_state VARCHAR2(10);
2599 l_status VARCHAR2(10);
2600 l_terminationDate DATE;
2601 --
2602 l_stepId NUMBER(15);
2603 l_txnId NUMBER(15);
2604 l_bgId NUMBER(15);
2605 l_rptgGrpId NUMBER(15);
2606 l_planId NUMBER(15);
2607 l_personId VARCHAR2(15);
2608 l_assignmentId VARCHAR2(15);
2609 l_cpersonId VARCHAR2(15);
2610 --
2611 l_editAllowed VARCHAR2(10);
2612 l_futureChange VARCHAR2(10);
2613 l_pendingTxn VARCHAR2(10);
2614 l_interAction VARCHAR2(10);
2615 l_isPersonElig VARCHAR2(10);
2616 l_dtParmFound VARCHAR2(10);
2617 l_rtParmFound VARCHAR2(10);
2618 l_slParmFound VARCHAR2(10);
2619 l_terminateFlag VARCHAR2(30);
2620 --
2621 l_effDateOption VARCHAR2(20);
2622 l_effectiveDate VARCHAR2(20);
2623 --
2624 l_profileValue VARCHAR2(100);
2625 l_processName VARCHAR2(200);
2626 --
2627 l_rateMsg VARCHAR2(100) ;
2628 l_errorWarnings VARCHAR2(20000);
2629 l_errorMsg VARCHAR2(10000) := '';
2630 l_warningMsg VARCHAR2(10000) := '';
2631 --
2632 l_whatChecks VARCHAR2(10);
2633 lb_istxnowner boolean;
2634
2635 --
2636 BEGIN
2637 --
2638 -- By default check Intervening/Future, Pending and Eligibility
2639 l_whatChecks := 'IPG';
2640 p_sendToHr := 'N';
2641 p_hasErrors := 'N';
2642
2643 IF (p_addToPub = 'YES') THEN
2644 hr_multi_message.enable_message_list;
2645 p_hasErrors := set_developer_ntf_msg(p_itemType, p_itemKey);
2646 END IF;
2647
2648 -- If non-approval process and final_validation is being performed,
2649 -- then only check for future dated changes.
2650 IF (NVL(p_calledFrom,'X') = 'FINAL_VALIDATION' ) THEN
2651 BEGIN
2652 IF wf_engine.GetItemAttrText(
2653 itemtype => p_itemType,
2654 itemkey => p_itemKey,
2655 aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG')
2656 NOT IN ('YES','YES_DYNAMIC') THEN
2657 l_whatChecks := 'F';
2658 END IF;
2659 EXCEPTION
2660 WHEN OTHERS THEN -- if wf attribute is not found
2661 l_whatChecks := 'F';
2662 END;
2663 END IF;
2664 --
2665
2666 l_effectiveDate := NVL( wf_engine.getItemAttrText (
2667 itemtype => p_itemType
2668 ,itemkey => p_itemKey
2669 ,aname => 'P_EFFECTIVE_DATE'),to_char(sysdate,g_date_format));
2670
2671 get_transaction_info (
2672 p_itemType => p_itemType
2673 ,p_itemKey => p_itemKey
2674 ,p_loginPerson => l_personId
2675 ,p_whatchecks => l_whatChecks
2676 ,p_calledFrom => p_calledFrom --added for bug 13416339
2677 ,p_personId => l_personId
2678 ,p_assignmentId => l_assignmentId
2679 ,p_state => l_state
2680 ,p_status => l_status
2681 ,p_txnId => l_txnId
2682 ,p_businessGrpId => l_bgId
2683 ,p_editAllowed => l_editAllowed
2684 ,p_futureChange => l_futureChange
2685 ,p_pendingTxn => l_pendingTxn
2686 ,p_interAction => l_interAction
2687 ,p_effDateOption => l_effDateOption
2688 ,p_effectiveDate => l_effectiveDate
2689 ,p_isPersonElig => l_isPersonElig
2690 ,p_rptgGrpId => l_rptgGrpId
2691 ,p_planId => l_planId
2692 ,p_processName => l_processName
2693 ,p_dateParmExist => l_dtParmFound
2694 ,p_rateParmExist => l_rtParmFound
2695 ,p_slryParmExist => l_slParmFound
2696 ,p_rateMessage => l_rateMsg
2697 ,p_terminateFlag => l_terminateFlag);
2698
2699 --fix for the bug 7640649
2700 l_cpersonId := NVL( wf_engine.getItemAttrText (
2701 itemtype => p_itemType
2702 ,itemkey => p_itemKey
2703 ,aname => 'CREATOR_PERSON_ID'),-1);
2704
2705 if hr_multi_tenancy_pkg.is_multi_tenant_system then
2706 hr_multi_tenancy_pkg.set_context_for_person(l_cpersonId);
2707 end if;
2708
2709 begin
2710 lb_istxnowner := nvl(hr_transaction_swi.istxnowner(l_txnId,null),false);
2711 exception
2712 when others then
2713 lb_istxnowner := false;
2714 end;
2715
2716 -- Bug 3025523: If the logged in manager does not secured access to the selected person
2717 -- then display appropriate message, no other message will be displayed in this
2718 -- case
2719 IF ( l_editAllowed = 'NS' ) THEN
2720 --
2721 p_hasErrors := 'Y';
2722 l_errorMsg := l_errorMsg||add_message('ERR','PQH','PQH_SS_ACCESS_TO_EMP_REC_ERR',p_calledFrom,p_addToPub);
2723 --
2724 END IF;
2725 --
2726 IF ( l_futureChange = 'Y' OR
2727 l_interAction = 'Y' OR
2728 l_interAction = 'YC' OR
2729 l_pendingTxn = 'Y' OR
2730 l_editAllowed = 'NS' OR
2731 l_terminateFlag <> 'NO' OR
2732 l_rateMsg IS NOT NULL OR
2733 l_isPersonElig = 'N' ) THEN
2734
2735 -- Termination Check
2736 IF ( l_terminateFlag IN ('ASGN','PRSN') ) THEN
2737 --
2738 p_hasErrors := 'Y';
2739 -- l_errorMsg := l_errorMsg||add_message('ERR','PER','HR_HR_MESG38_WEB',p_calledFrom);
2740 l_errorMsg := l_errorMsg||add_message('ERR','PQH','PQH_SS_PRSN_TERMINATED_NTF_ERR',p_calledFrom,p_addToPub);
2741 --
2742 --If termination date is returned then, check if current or future termination
2743 ELSIF ( NVL(l_terminateFlag,'NO')<> 'NO' ) THEN
2744 --
2745 BEGIN
2746 l_terminationDate := to_date(l_terminateFlag,g_date_format);
2747 --
2748 -- if future termination then check profile and set error or warning
2749 IF ( l_terminationDate > trunc(sysdate) ) THEN
2750 p_sendToHr := 'Y'; -- so that it is send to hr rep
2751 l_profileValue := NVL(fnd_profile.value('PQH_DT_RULE_FUTUR_CHANGE_FOUND'),'ERROR');
2752 --
2753 IF ( l_profileValue = 'ERROR' OR l_slParmFound= 'Y' ) THEN
2754 p_hasErrors := 'Y'; -- To send error notification
2755 l_errorMsg := l_errorMsg||add_message('ERR','PQH','PQH_SS_FUT_TERM_EXISTS_ERR',
2756 p_calledFrom,p_addToPub,'TERM_DATE',l_terminationDate);
2757 ELSE
2758 l_errorMsg := l_errorMsg||add_message('WRN','PQH','PQH_SS_FUT_TERM_EXISTS_WRN',
2759 p_calledFrom,p_addToPub,'TERM_DATE',l_terminationDate);
2760 END IF;
2761 --
2762 END IF;
2763 --
2764 EXCEPTION
2765 -- In case a termination date is not returned, some value other than
2766 -- NO, ASGN or PRSN is returned
2767 WHEN OTHERS THEN Null;
2768 END;
2769 END IF;
2770
2771 --Bug 3003754 Salary Change
2772 IF ( l_rtParmFound = 'Y' AND l_rateMsg IS NOT NULL) THEN
2773 --
2774 l_stepId := pqh_ss_utility.get_transaction_step_id (
2775 p_itemType => p_itemType
2776 , p_itemKey => p_itemKey
2777 , p_apiName => 'HR_PAY_RATE_SS.PROCESS_API' );
2778 --
2779 -- Display message only if the payrate step exists
2780 if ( l_stepId IS NULL ) THEN
2781 l_rateMsg := null;
2782 else
2783 l_errorMsg := l_errorMsg||add_message('ERR','PQH',l_rateMsg,p_calledFrom,p_addToPub);
2784 end if;
2785 --
2786 END IF;
2787
2788 IF ( l_interAction in ('YC', 'Y') ) THEN
2789 -- In case of approving, an intervening action is always an error.
2790 -- so no need to check the profile in that case
2791 p_hasErrors := 'Y';
2792
2793 IF (p_calledFrom = 'FINAL_VALIDATION') THEN
2794 l_errorMsg := l_errorMsg||add_message('ERR','PQH','PQH_SS_INTRVN_ACTN_NTF_ERR',p_calledFrom,p_addToPub);
2795 ELSE
2796 l_profileValue := NVL(fnd_profile.value('PQH_ALLOW_TRANSACTION_REFRESH'),'N');
2797 --
2798 IF (l_profileValue = 'N') THEN
2799
2800 l_errorMsg := l_errorMsg||
2801 add_message('ERR','PQH','PQH_SS_INTRVN_ACTN_NTF_ERR',p_calledFrom,p_addToPub);
2802 ELSE
2803 -- check if the login user is initiator or approver
2804 if(lb_istxnowner) then
2805 l_warningMsg := l_warningMsg||
2806 add_message('WRN','PQH','PQH_SS_INTRVN_ACTN_NTF_WRN',p_calledFrom,p_addToPub);
2807 else
2808 -- check the profile if the system is configured for approvers editing
2809 --IF ( nvl(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N') = 'Y' AND l_editAllowed='Y') THEN
2810 IF ( nvl(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N') = 'Y' ) THEN
2811 l_warningMsg := l_warningMsg||
2812 add_message('WRN','PER','HR_INTRVN_ACTN_NTF_WRN_AE',p_calledFrom,p_addToPub);
2813 null;
2814 else
2815 l_warningMsg := l_warningMsg||
2816 add_message('WRN','PER','HR_INTRVN_ACTN_NTF_WRN_ANE',p_calledFrom,p_addToPub);
2817 null;
2818 end if;-- edit profile check.
2819 end if;
2820 END IF;
2821 END IF;
2822 --
2823 END IF;
2824 --
2825 IF ( l_futureChange = 'Y' ) THEN
2826 l_profileValue := NVL(fnd_profile.value('PQH_DT_RULE_FUTUR_CHANGE_FOUND'),'ERROR');
2827 if (l_profileValue = 'APPROVE_ONLY' AND NVL(l_slParmFound,'X') <> 'Y' ) THEN
2828 -- if it's final validation and intervening changes are found, then the txn will
2829 -- not be routed to HR Rep, intead Transaction error notification will be sent
2830 -- When that happens no need to show the warning "Future change exist, txn will
2831 -- be routed to HR Rep".
2832 IF ( p_calledFrom = 'FINAL_VALIDATION' AND l_interAction in ('YC','Y')) THEN
2833 p_hasErrors := 'Y';
2834 ELSIF (NOT isHrRepNtf(p_itemType, p_itemKey)) THEN
2835 p_sendToHr := 'Y';
2836 if(lb_istxnowner) then
2837 l_warningMsg := l_warningMsg||
2838 add_message('WRN','PQH','PQH_SS_FUTURE_CHNG_EXIST_WRN',p_calledFrom,p_addToPub);
2839 else
2840 l_warningMsg := l_warningMsg||
2841 add_message('WRN','PER','HR_FUTURE_CHNG_EXIST_WRN_APR',p_calledFrom,p_addToPub);
2842 end if;
2843 END IF;
2844 ELSE
2845 p_hasErrors := 'Y';
2846 if(lb_istxnowner) then
2847 l_errorMsg := l_errorMsg||
2848 add_message('ERR','PQH','PQH_SS_FUTURE_CHNG_EXIST_ERR',p_calledFrom,p_addToPub);
2849 else
2850 l_errorMsg := l_errorMsg||
2851 add_message('ERR','PER','HR_FUTURE_CHNG_EXIST_ERR_APR',p_calledFrom,p_addToPub);
2852 end if;
2853 END IF;
2854 --
2855 END IF;
2856 -- Pending Transaction Found
2857 IF ( l_pendingTxn = 'Y' ) THEN
2858 l_profileValue := NVL(fnd_profile.value('PQH_ALLOW_CONCURRENT_TXN'),'N');
2859 IF (l_profileValue = 'N') THEN
2860 p_hasErrors := 'Y';
2861 l_errorMsg := l_errorMsg||
2862 add_message('ERR','PQH','PQH_SS_PENDING_TXN_NTF_ERR',p_calledFrom,p_addToPub);
2863 ELSE
2864 l_warningMsg := l_warningMsg ||
2865 add_message('WRN','PQH','PQH_SS_PENDING_TXN_NTF_WRN',p_calledFrom,p_addToPub);
2866 END IF;
2867 END IF;
2868 -- Person is Ineligible
2869 IF ( l_isPersonElig = 'N' ) THEN -- Person Ineligible
2870 l_profileValue := NVL(fnd_profile.value('PQH_ENABLE_INELIGIBLE_ACTIONS'),'N');
2871 IF (l_profileValue = 'N') THEN
2872 p_hasErrors := 'Y';
2873 l_errorMsg := l_errorMsg||
2874 add_message('ERR','PQH','PQH_SS_PERSON_INELIG_NTF_ERR',p_calledFrom,p_addToPub);
2875 ELSE
2876 l_warningMsg := l_warningMsg ||
2877 add_message('WRN','PQH','PQH_SS_PERSON_INELIG_NTF_WRN',p_calledFrom,p_addToPub);
2878 END IF;
2879 END IF;
2880 --
2881 -- Concatinate Errors first and then Warnings
2882 -- They are kept in separate variable, for future use.
2883 l_errorWarnings := l_errorWarnings||l_errorMsg||l_warningMsg;
2884
2885 --No formatting if called from final validation
2886 IF (l_errorWarnings IS NOT NULL AND NVL(p_calledFrom,'X') <> 'FINAL_VALIDATION') THEN
2887 l_errorWarnings := '<TABLE border=0> '||l_errorWarnings||'</TABLE>';
2888 END IF;
2889
2890 ELSE
2891 l_errorWarnings := NULL;
2892 END IF;
2893 IF (p_addToPub = 'YES' AND l_errorWarnings is NOT NULL AND length(l_errorWarnings) >0) THEN
2894 p_hasErrors := 'Y';
2895 END IF;
2896 IF (p_addToPub = 'YES') THEN
2897 hr_multi_message.disable_message_list;
2898 END IF;
2899 RETURN l_errorWarnings;
2900 --
2901 EXCEPTION
2902 WHEN OTHERS THEN
2903 hr_utility.trace(' exception in '||g_package||'.get_errors_and_warnings : ' || sqlerrm);
2904 raise;
2905 --
2906 END get_errors_and_warnings;
2907
2908 --
2909 --
2910 PROCEDURE validation_on_final_approval (
2911 itemtype IN VARCHAR2,
2912 itemkey IN VARCHAR2,
2913 actid IN NUMBER,
2914 funcmode IN VARCHAR2,
2915 result OUT NOCOPY VARCHAR2 ) IS
2916 --
2917 l_errorWarnings VARCHAR2(20000);
2918 l_hasErrors VARCHAR2(5);
2919 l_sendToHr VARCHAR2(5);
2920 l_txnId NUMBER;
2921 BEGIN
2922 --
2923 /*
2924 * 1. Get another flag which tells whethar intervening action has taken place
2925 * 2. Based on the flag see if the error flag is not set (no change if set)
2926 * 3. Sent out a different result if InterveningFlag=Y and ErrorFlag = N
2927 */
2928 l_errorWarnings :=
2929 get_errors_and_warnings (
2930 p_itemType => itemType
2931 ,p_itemKey => itemKey
2932 ,p_calledFrom=> 'FINAL_VALIDATION'
2933 ,p_hasErrors => l_hasErrors
2934 ,p_sendToHr => l_sendToHr );
2935 --
2936 IF ( l_errorWarnings IS NULL OR l_hasErrors <> 'Y') THEN -- If no messages or only warnings
2937 result := 'COMPLETE:SUCCESS';
2938 ELSE -- if errors are found
2939
2940 -- Bug 3035702: Allow starting new transaction if existing transaction is in error
2941 -- Set transaction status to 'E - Error' so that it does not block any other transaction
2942 -- and can be picked up for cleanup.
2943 hr_transaction_api.update_transaction(
2944 p_transaction_id => get_transaction_id(itemType,itemKey),
2945 p_status => 'E');
2946
2947 -- Fix for bug 8334527 :
2948
2949 wf_engine.setitemattrtext
2950 (itemtype => itemtype
2951 ,itemkey => itemkey
2952 ,aname => 'TRAN_SUBMIT'
2953 ,avalue => 'E');
2954
2955 result := 'COMPLETE:FAILURE';
2956 wf_engine.SetItemAttrText(
2957 itemtype => itemType
2958 , itemkey => itemKey
2959 , aname => 'ERROR_MESSAGE' -- Bug 2962967: changed from ERROR_MESSAGE_TEXT
2960 , avalue => l_errorWarnings );
2961 END IF;
2962 --
2963 -- Set send to HR rep attribute
2964 wf_engine.SetItemAttrText(
2965 itemtype => itemType
2966 , itemkey => itemKey
2967 , aname => 'SEND_TO_HR_REP'
2968 , avalue => l_sendToHr );
2969 --
2970 --
2971 EXCEPTION
2972 WHEN OTHERS THEN
2973 hr_utility.trace(' exception in '||g_package||'.validation_on_final_approval : ' || sqlerrm);
2974 Wf_Core.Context(g_package, 'validation_on_final_approval', itemType, itemKey);
2975 raise;
2976 --
2977 END validation_on_final_approval;
2978 --
2979 --
2980 PROCEDURE check_for_warning_error (
2981 document_id in varchar2,
2982 display_type in varchar2,
2983 document in out nocopy varchar2,
2984 document_type in out nocopy varchar2) IS
2985 --
2986 l_itemType VARCHAR2(30);
2987 l_itemKey VARCHAR2(30);
2988 --
2989 l_errorWarnings VARCHAR2(20000);
2990 l_hasErrors VARCHAR2(5);
2991 l_sendToHr VARCHAR2(5);
2992 BEGIN
2993 --
2994 -- No need to check for errors if notification is closed.
2995 if ( is_notification_closed(document_id) = 'Y') then
2996 return;
2997 end if;
2998 --
2999 document_type := wf_notification.doc_html;
3000
3001 -- Document will only have the notification id
3002 -- fetch the wf itemType and key from notification
3003 get_item_type_and_key (
3004 p_ntfId => document_id
3005 ,p_itemType => l_itemType
3006 ,p_itemKey => l_itemKey );
3007 --
3008 l_errorWarnings :=
3009 get_errors_and_warnings (
3010 p_itemType => l_itemType
3011 ,p_itemKey => l_itemKey
3012 ,p_hasErrors => l_hasErrors
3013 ,p_sendToHr => l_sendToHr );
3014 --
3015 document := l_errorWarnings;
3016 --
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 hr_utility.trace(' exception in '||g_package||'.check_for_warning_error : ' || sqlerrm);
3020 document := '<a class=OraErrorText>Exception in '||g_package||'.check_for_warning_error : '||
3021 sqlerrm||'</a>';
3022 --
3023 END check_for_warning_error ;
3024 --
3025 --
3026 FUNCTION is_notification_closed (
3027 p_ntfId IN VARCHAR2 ) RETURN VARCHAR2 IS
3028 --
3029 l_isClosed VARCHAR2(10) := 'N';
3030 lv_ntf_role WF_NOTIFICATIONS.RECIPIENT_ROLE%type;
3031 lv_ntf_msg_typ WF_NOTIFICATIONS.MESSAGE_TYPE%type;
3032 lv_ntf_msg_name WF_NOTIFICATIONS.MESSAGE_NAME%type;
3033 lv_ntf_prior WF_NOTIFICATIONS.PRIORITY%type;
3034 lv_ntf_due WF_NOTIFICATIONS.DUE_DATE%type;
3035 lv_ntf_status WF_NOTIFICATIONS.STATUS%type;
3036 --
3037 BEGIN
3038 --
3039 -- Get notification recipient and status
3040 Wf_Notification.GetInfo(p_ntfId, lv_ntf_role, lv_ntf_msg_typ, lv_ntf_msg_name, lv_ntf_prior, lv_ntf_due, lv_ntf_status);
3041 if (lv_ntf_status <> 'OPEN') then
3042 l_isClosed := 'Y';
3043 else
3044 l_isClosed := 'N';
3045 end if;
3046
3047 return l_isClosed;
3048 --
3049 --
3050 EXCEPTION
3051 WHEN OTHERS THEN
3052 hr_utility.trace(' exception in '||g_package||'.is_notification_closed : ' || sqlerrm);
3053 raise;
3054 --
3055 END is_notification_closed;
3056 --
3057
3058 FUNCTION complete_custom_rfc (
3059 p_ntfId IN VARCHAR2 ) RETURN VARCHAR2 IS
3060 --
3061 l_isCustomRFC VARCHAR2(5);
3062 l_itemType VARCHAR2(30);
3063 l_itemKey VARCHAR2(240);
3064 l_activityId NUMBER;
3065 BEGIN
3066 get_item_type_and_key (
3067 p_ntfId => p_ntfId
3068 ,p_itemType => l_itemType
3069 ,p_itemKey => l_itemKey );
3070
3071 l_isCustomRFC :=
3072 wf_engine.GetItemAttrText(
3073 itemtype => l_itemType
3074 ,itemkey => l_itemKey
3075 ,aname => 'HR_CUSTOM_RETURN_FOR_CORR');
3076
3077 IF (l_isCustomRFC = 'Y' ) THEN
3078 l_activityId :=
3079 get_notified_activity(
3080 p_itemType => l_itemType
3081 ,p_itemKey => l_itemKey
3082 ,p_ntfId => p_ntfId );
3083
3084 complete_wf_activity (
3085 p_itemType => l_itemtype,
3086 p_itemKey => l_itemkey,
3087 p_activity => l_activityId,
3088 p_otherAct => 'THIS',
3089 p_resultCode => 'RETURNEDFORCORRECTION' ) ;
3090 END IF;
3091
3092 RETURN l_isCustomRFC;
3093 --
3094 EXCEPTION
3095 WHEN OTHERS THEN
3096 hr_utility.trace(' exception in '||g_package||'.complete_custom_rfc : ' || sqlerrm);
3097 raise;
3098 --
3099 END complete_custom_rfc;
3100 --
3101 --
3102 procedure delete_txn_notification(
3103 p_itemType IN VARCHAR2
3104 ,p_itemKey IN VARCHAR2
3105 ,p_transactionId IN VARCHAR2
3106 ) is
3107 l_activity_id number;
3108 l_notificationId number;
3109 begin
3110 --
3111 l_notificationId := get_notification_id (
3112 p_itemType => p_itemType
3113 ,p_itemKey => p_itemKey
3114 );
3115 --
3116 if l_notificationId is not null then
3117 l_activity_id := get_notified_activity (
3118 p_itemType => p_itemType
3119 ,p_itemKey => p_itemKey
3120 ,p_ntfId => l_notificationId
3121 );
3122 --
3123 complete_wf_activity (
3124 p_itemType => p_itemType,
3125 p_itemKey => p_itemKey,
3126 p_activity => l_activity_id,
3127 p_otherAct => 'THIS',
3128 p_resultCode => 'DEL' );
3129 elsif p_transactionId is not null then
3130 hr_transaction_api.rollback_transaction(p_transactionId);
3131 end if;
3132 --
3133 /*
3134 exception
3135 when others then
3136 hr_transaction_api.rollback_transaction
3137 (p_transaction_id => p_transactionid);
3138 */
3139 end;
3140 --
3141 PROCEDURE set_hr_rep_role (
3142 itemtype IN VARCHAR2,
3143 itemkey IN VARCHAR2,
3144 actid IN NUMBER,
3145 funcmode IN VARCHAR2,
3146 result OUT NOCOPY VARCHAR2 ) IS
3147
3148 l_roleType PQH_ROLES.role_type_cd%TYPE := 'HR_REP';
3149 l_PersonId NUMBER(15);
3150 l_bgId NUMBER(15);
3151 l_flag VARCHAR2(1);
3152 l_roleId NUMBER(15);
3153 l_roleName PQH_ROLES.role_name%TYPE;
3154
3155 CURSOR cur_wfrole (p_role_id NUMBER) IS
3156 SELECT name
3157 FROM wf_roles
3158 WHERE orig_system = 'PQH_ROLE'
3159 AND orig_system_id = p_role_id ;
3160
3161 BEGIN
3162
3163 l_personId := wf_engine.GetItemAttrText(
3164 itemtype => itemtype,
3165 itemkey => itemkey,
3166 aname => 'CURRENT_PERSON_ID');
3167
3168 l_bgId := PQH_SS_UTILITY.get_business_group_id ( l_PersonId, sysdate);
3169
3170 PQH_SS_UTILITY.get_Role_Info (
3171 p_roleTypeCd => l_roleType
3172 ,p_businessGroupId => l_bgId
3173 ,p_globalRoleFlag => l_flag
3174 ,p_roleName => l_roleName
3175 ,p_roleId => l_roleId );
3176
3177 /* if l_roleName is null then
3178 Wf_Core.Token('TYPE', itemtype);
3179 Wf_Core.Token('ACTID', to_char(actid));
3180 Wf_Core.Raise('WFENG_NOTIFICATION_PERFORMER');
3181 end if;
3182 */
3183 IF ( l_roleId IS NULL ) THEN
3184 --
3185 result := 'COMPLETE:FAILURE';
3186 --
3187 ELSE -- Role id is not null
3188 --
3189 IF ( cur_wfrole%ISOPEN ) THEN
3190 CLOSE cur_wfrole;
3191 END IF;
3192 --
3193 OPEN cur_wfrole (l_roleId) ;
3194 FETCH cur_wfrole INTO l_roleName ;
3195 CLOSE cur_wfrole;
3196 --
3197 IF ( l_roleName IS NULL ) THEN
3198 --
3199 result := 'COMPLETE:FAILURE';
3200 --
3201 ELSE
3202 --
3203 wf_engine.SetItemAttrText(
3204 itemtype => itemtype,
3205 itemkey => itemkey,
3206 aname => 'HR_REP_ROLE',
3207 avalue => l_roleName);
3208 --
3209 result := 'COMPLETE:SUCCESS';
3210 --
3211 END IF;
3212 --
3213 END IF;
3214 --
3215 IF ( result = 'COMPLETE:FAILURE') THEN
3216 --
3217 -- Bug 3035702: Allow starting new transaction if existing transaction is in error
3218 -- Set transaction status to 'E - Error' so that it does not block any other transaction
3219 -- and can be picked up for cleanup.
3220 hr_transaction_api.update_transaction(
3221 p_transaction_id => get_transaction_id(itemType,itemKey),
3222 p_status => 'E');
3223 END IF;
3224 --
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 hr_utility.trace(' exception in '||g_package||'.set_hr_rep_role : ' || sqlerrm);
3228 Wf_Core.Context(g_package, 'set_hr_rep_role', itemType, itemKey);
3229 raise;
3230
3231 END set_hr_rep_role;
3232
3233 procedure approval_block(itemtype in varchar2,
3234 itemkey in varchar2,
3235 actid in number,
3236 funcmode in varchar2,
3237 resultout in out nocopy varchar2)
3238 is
3239 begin
3240 -- Do nothing in cancel or timeout mode
3241 if (funcmode <> wf_engine.eng_run) then
3242 resultout := wf_engine.eng_null;
3243 return;
3244 end if;
3245
3246 -- Set the Approval process version to distinguish it
3247 -- it from old approval process.
3248 wf_engine.SetItemAttrText(
3249 itemtype => itemType
3250 , itemkey => itemKey
3251 , aname => 'HR_APPROVAL_PRC_VERSION'
3252 , avalue => 'V5' );
3253 --
3254 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
3255 ':'||wf_engine.eng_null;
3256 exception
3257 when others then
3258 hr_utility.trace(' exception in '||g_package||'.approval_block : ' || sqlerrm);
3259 Wf_Core.Context(g_package, 'approval_block', itemType, itemKey);
3260 raise;
3261 end approval_block;
3262 --
3263
3264
3265 /* ============== APPROVAL HISTORY ==========================
3266 * Procedure to build the notification history, that is added
3267 * to the bottom of approval notifications. It also considers
3268 * RFC notifications and includes the record in building the
3269 * Workflow History.
3270 * This procedure internally uses two procedures copied from
3271 * WF_notifications package with some modifications to cater
3272 * to our specific need
3273 */
3274 PROCEDURE approval_history (
3275 document_id in varchar2,
3276 display_type in varchar2,
3277 document in out nocopy varchar2,
3278 document_type in out nocopy varchar2) IS
3279
3280 --
3281 -- Wf_Ntf_History
3282 -- Construct a history table for a notification activity.
3283 -- NOTE
3284 -- Consist of three sections:
3285 -- 1. Current Notification
3286 -- 2. Past Notifications in the history table
3287 -- 3. The owner role as the submitter and begin date for such item
3288 --
3289
3290
3291 l_x varchar2(32000);
3292 type tdType is table of varchar2(4005) index by binary_integer;
3293
3294 table_width varchar2(8) := '100%';
3295 table_border varchar2(2) := '0';
3296 table_cellpadding varchar2(2) := '3';
3297 table_cellspacing varchar2(2) := '1';
3298 table_bgcolor varchar2(7) := 'white';
3299 th_bgcolor varchar2(7) := '#cccc99';
3300 th_fontcolor varchar2(7) := '#336699';
3301 th_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif';
3302 th_fontsize varchar2(2) := '2';
3303 td_bgcolor varchar2(7) := '#f7f7e7';
3304 td_fontcolor varchar2(7) := 'black';
3305 td_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif';
3306 td_fontsize varchar2(2) := '2';
3307
3308
3309
3310 procedure NTF_Table(cells in tdType,
3311 col in pls_integer,
3312 type in varchar2, -- 'V'ertical or 'H'orizontal
3313 rs in out nocopy varchar2)
3314 is
3315 i pls_integer;
3316 colon pls_integer;
3317 modv pls_integer;
3318 alignv varchar2(1);
3319 l_align varchar2(8);
3320 l_width varchar2(3);
3321 l_text varchar2(4000);
3322 l_type varchar2(1);
3323 l_dir varchar2(1);
3324 l_dirAttr varchar2(10);
3325
3326 -- Define a local set and initialize with the default
3327 l_table_width varchar2(8) := table_width;
3328 l_table_border varchar2(2) := table_border;
3329 l_table_cellpadding varchar2(2) := table_cellpadding;
3330 l_table_cellspacing varchar2(2) := table_cellspacing;
3331 l_table_bgcolor varchar2(7) := table_bgcolor;
3332 l_th_bgcolor varchar2(7) := th_bgcolor;
3333 l_th_fontcolor varchar2(7) := th_fontcolor;
3334 l_th_fontface varchar2(80) := th_fontface;
3335 l_th_fontsize varchar2(2) := th_fontsize;
3336 l_td_bgcolor varchar2(7) := td_bgcolor;
3337 l_td_fontcolor varchar2(7) := td_fontcolor;
3338 l_td_fontface varchar2(80) := td_fontface;
3339 l_td_fontsize varchar2(2) := td_fontsize;
3340
3341 begin
3342 if length(type) > 1 then
3343 l_type := substrb(type, 1, 1);
3344 l_dir := substrb(type,2, 1);
3345 else
3346 l_type := type;
3347 l_dir := 'L';
3348 end if;
3349
3350 if l_dir = 'L' then
3351 l_dirAttr := NULL;
3352 else
3353 l_dirAttr := 'dir="RTL"';
3354 end if;
3355
3356 if (l_type = 'N') then
3357 -- Notification format. Alter the default colors.
3358 l_table_bgcolor := '#FFFFFF';
3359 l_th_bgcolor := '#FFFFFF';
3360 l_th_fontcolor := '#000000';
3361 l_td_bgcolor := '#FFFFFF';
3362 l_td_fontcolor := '#000000';
3363 l_table_cellpadding := '1';
3364 l_table_cellspacing := '1';
3365 end if;
3366
3367 if (cells.COUNT = 0) then
3368 rs := null;
3369 return;
3370 end if;
3371 rs := '<table width=100% border=0 cellpadding=0 cellspacing=0 '||l_dirAttr||
3372 '><tr><td>';
3373 rs := rs||wf_core.newline||'<table sumarry="" width='||l_table_width||
3374 ' border='||l_table_border||
3375 ' cellpadding='||l_table_cellpadding||
3376 ' cellspacing='||l_table_cellspacing||
3377 ' bgcolor='||l_table_bgcolor||' '||l_dirAttr||'>';
3378
3379 -- ### implement as generic log in the future
3380 -- if (wf_notification.debug) then
3381 -- dbms_output.put_line(to_char(cells.LAST));
3382 -- end if;
3383
3384 for i in 1..cells.LAST loop
3385 -- if (wf_notification.debug) then
3386 -- dbms_output.put_line(substrb('('||to_char(i)||')='||cells(i),1,254));
3387 -- end if;
3388 modv := mod(i, col);
3389 if (modv = 1) then
3390 rs := rs||wf_core.newline||'<tr>';
3391 end if;
3392
3393 alignv := substrb(cells(i), 1, 1);
3394 if (alignv = 'R') then
3395 l_align := 'RIGHT';
3396 elsif (alignv = 'L') then
3397 l_align := 'LEFT';
3398 elsif (alignv = 'S') then
3399 if (l_dir = 'L') then
3400 l_align := 'LEFT';
3401 else
3402 l_align := 'RIGHT';
3403 end if;
3404 elsif (alignv = 'E') then
3405 if (l_dir = 'L') then
3406 l_align := 'RIGHT';
3407 else
3408 l_align := 'LEFT';
3409 end if;
3410 else
3411 l_align := 'CENTER';
3412 end if;
3413
3414 -- if (wf_notification.debug) then
3415 -- dbms_output.put_line('modv = '||to_char(modv));
3416 -- end if;
3417
3418 colon := instrb(cells(i),':');
3419 l_width := substrb(cells(i), 2, colon-2);
3420 l_text := substrb(cells(i), colon+1); -- what is after the colon
3421
3422 if ((l_type = 'V' and modv = 1) or (l_type = 'N' and modv = 1)
3423 or (l_type = 'H' and i <= col)) then
3424 if (l_type = 'N') then
3425 rs := rs||wf_core.newline||'<td';
3426 else
3427 -- this is a header
3428 rs := rs||wf_core.newline||'<th';
3429 end if;
3430 if (l_type = 'V') then
3431 rs := rs||' scope=row';
3432 else
3433 rs := rs||' scope=col';
3434 end if;
3435
3436 if (l_width is not null) then
3437 rs := rs||' width='||l_width;
3438 end if;
3439 rs := rs||' align='||l_align||' valign=baseline bgcolor='||
3440 l_th_bgcolor||'>';
3441 rs := rs||'<font color='||l_th_fontcolor||' face="'||l_th_fontface||'"'
3442 ||' size='||l_th_fontsize||'>';
3443 rs := rs||l_text||'</font>';
3444 if (l_type = 'N') then
3445 rs := rs||'</td>';
3446 else
3447 rs := rs||'</th>';
3448 end if;
3449 else
3450 -- this is regular data
3451 rs := rs||wf_core.newline||'<td';
3452 if (l_width is not null) then
3453 rs := rs||' width='||l_width;
3454 end if;
3455 rs := rs||' align='||l_align||' valign=baseline bgcolor='||
3456 l_td_bgcolor||'>';
3457 rs := rs||'<font color='||td_fontcolor||' face="'||l_td_fontface||'"'
3458 ||' size='||l_td_fontsize||'>';
3459 if (l_type = 'N') then
3460 rs := rs||'<b>'||l_text||'</b></font></td>';
3461 else
3462 rs := rs||l_text||'</font></td>';
3463 end if;
3464 end if;
3465 if (modv = 0) then
3466 rs := rs||wf_core.newline||'</tr>';
3467 end if;
3468 end loop;
3469 rs := rs||wf_core.newline||'</table>'||wf_core.newline||'</td></tr></table>';
3470
3471 exception
3472 when OTHERS then
3473 wf_core.context('Wf_Notification', 'NTF_Table',to_char(col),l_type);
3474 raise;
3475 end NTF_Table;
3476
3477
3478 function wf_ntf_history(nid in number,
3479 disptype in varchar2)
3480 return varchar2
3481 is
3482 -- current notification
3483 cursor hist0c(x_item_type varchar2, x_item_key varchar2, x_actid number) is
3484
3485 SELECT rownum h_sequence
3486 ,h_notification_id
3487 ,h_from_role
3488 ,h_action
3489 ,h_comment
3490 ,h_action_date
3491 FROM (
3492 SELECT h_notification_id
3493 ,h_from_role
3494 ,h_action
3495 ,h_comment
3496 ,h_action_date
3497 FROM (
3498 SELECT 99999999 h_sequence
3499 ,ias.notification_id h_notification_id
3500 ,ias.assigned_user h_from_role
3501 ,wf_core.activity_result (a.result_type
3502 ,ias.activity_result_code) h_action
3503 ,
3504 (
3505 SELECT text_value
3506 FROM wf_notification_attributes
3507 WHERE notification_id = ias.notification_id
3508 AND name = 'WF_NOTE'
3509 ) h_comment
3510 ,nvl (ias.end_date
3511 ,ias.begin_date) h_action_date
3512 FROM wf_item_activity_statuses ias
3513 ,wf_activities a
3514 ,wf_process_activities pa
3515 ,wf_items i
3516 ,WF_ITEM_TYPES IT
3517 where IAS.ITEM_TYPE = 'HRSSA'
3518 and IAS.ITEM_KEY = '15018'
3519 and IAS.NOTIFICATION_ID is not null
3520 and nvl(RESULT_TYPE,'*') NOT IN ( '*','HR_DONE')
3521 and IAS.ITEM_TYPE = I.ITEM_TYPE
3522 and IAS.ITEM_KEY = I.ITEM_KEY
3523 and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
3524 and I.ITEM_TYPE = IT.NAME
3525 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
3526 and PA.ACTIVITY_NAME = A.NAME
3527 and PA.ACTIVITY_ITEM_TYPE= A.ITEM_TYPE
3528 and (IAS.ACTIVITY_RESULT_CODE is null or IAS.ACTIVITY_RESULT_CODE not in ('SFL','#NULL'))
3529
3530 UNION ALL
3531 SELECT 99999999 h_sequence
3532 ,ias.notification_id h_notification_id
3533 ,ias.assigned_user h_from_role
3534 ,wf_core.activity_result (a.result_type
3535 ,ias.activity_result_code) h_action
3536 ,
3537 (
3538 SELECT text_value
3539 FROM wf_notification_attributes
3540 WHERE notification_id = ias.notification_id
3541 AND name = 'WF_NOTE'
3542 ) h_comment
3543 ,nvl (ias.end_date
3544 ,ias.begin_date) h_action_date
3545 FROM wf_item_activity_statuses_h ias
3546 ,wf_activities a
3547 ,wf_process_activities pa
3548 ,wf_items i
3549 ,WF_ITEM_TYPES IT
3550 where IAS.ITEM_TYPE = 'HRSSA'
3551 and IAS.ITEM_KEY = '15018'
3552 and IAS.NOTIFICATION_ID is not null
3553 and (IAS.ACTIVITY_RESULT_CODE is null or IAS.ACTIVITY_RESULT_CODE not in ('SFL','#NULL'))
3554 and nvl(RESULT_TYPE,'*') NOT IN ( '*','HR_DONE')
3555 and IAS.ITEM_TYPE = I.ITEM_TYPE
3556 and IAS.ITEM_KEY = I.ITEM_KEY
3557 and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
3558 and I.ITEM_TYPE = IT.NAME
3559 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
3560 and PA.ACTIVITY_NAME = A.NAME
3561 and PA.ACTIVITY_ITEM_TYPE= A.ITEM_TYPE
3562
3563 UNION ALL
3564 SELECT c.sequence h_sequence
3565 ,c.notification_id h_notification_id
3566 ,c.from_role h_from_role
3567 ,wf_core.translate (c.action) h_action
3568 ,c.user_comment h_comment
3569 ,c.comment_date h_action_date
3570 FROM wf_item_activity_statuses ias
3571 ,wf_comments c
3572 WHERE ias.item_type = x_item_type
3573 AND ias.item_key = x_item_key
3574 -- AND ias.process_activity = x_actid
3575 AND ias.notification_id = c.notification_id
3576 AND c.action NOT IN ('RESPOND','RESPOND_WA','RESPOND_RULE'
3577 ,'SEND','CANCEL')
3578 UNION ALL
3579 SELECT c.sequence h_sequence
3580 ,c.notification_id h_notification_id
3581 ,c.from_role h_from_role
3582 ,wf_core.translate (c.action) h_action
3583 ,c.user_comment h_comment
3584 ,c.comment_date h_action_date
3585 FROM wf_item_activity_statuses_h ias
3586 ,wf_comments c
3587 WHERE ias.item_type = x_item_type
3588 AND ias.item_key = x_item_key
3589 -- AND ias.process_activity = x_actid
3590 AND ias.notification_id = c.notification_id
3591 AND c.action NOT IN ('RESPOND','RESPOND_WA','RESPOND_RULE'
3592 ,'SEND','CANCEL')
3593 )
3594 ORDER BY h_action_date desc
3595 ,h_notification_id desc
3596 ,h_sequence desc
3597 );
3598
3599
3600 l_itype varchar2(30);
3601 l_ikey varchar2(240);
3602 l_actid number;
3603 l_result_type varchar2(30);
3604 l_result_code varchar2(30);
3605 l_action varchar2(80);
3606 l_owner_role varchar2(320);
3607 l_owner varchar2(320);
3608 l_begin_date date;
3609 i pls_integer;
3610 j pls_integer;
3611 role_info_tbl wf_directory.wf_local_roles_tbl_type;
3612
3613 l_delim varchar2(1) := ':';
3614 cells tdType;
3615 result varchar2(32000) := '';
3616 begin
3617 wf_notification.getNtfActInfo(nid, l_itype, l_ikey, l_actid);
3618
3619 hr_utility.set_location(' kck nid : ' || nid, 666);
3620 hr_utility.set_location(' kck l_itype : ' || l_itype, 666);
3621 hr_utility.set_location(' kck l_ikey : ' || l_ikey, 666);
3622 hr_utility.set_location(' kck l_actid : ' || l_actid, 666);
3623
3624 l_actid := null;
3625
3626
3627 j := 1;
3628 -- title
3629 cells(j) := wf_core.translate('SEQUENCE');
3630 if (disptype = wf_notification.doc_html) then
3631 cells(j) := 'L10%:'||cells(j);
3632 end if;
3633 j := j+1;
3634 cells(j) := wf_core.translate('WHO');
3635 if (disptype = wf_notification.doc_html) then
3636 cells(j) := 'L:'||cells(j);
3637 end if;
3638 j := j+1;
3639 cells(j) := wf_core.translate('ACTION');
3640 if (disptype = wf_notification.doc_html) then
3641 cells(j) := 'L:'||cells(j);
3642 end if;
3643 j := j+1;
3644 cells(j) := wf_core.translate('DATE');
3645 if (disptype = wf_notification.doc_html) then
3646 cells(j) := 'L:'||cells(j);
3647 end if;
3648 j := j+1;
3649 cells(j) := wf_core.translate('NOTE');
3650 if (disptype = wf_notification.doc_html) then
3651 cells(j) := 'L:'||cells(j);
3652 end if;
3653 j := j+1;
3654
3655 begin
3656 select OWNER_ROLE, BEGIN_DATE
3657 into l_owner_role, l_begin_date
3658 from WF_ITEMS
3659 where ITEM_TYPE = l_itype
3660 and ITEM_KEY = l_ikey;
3661 exception
3662 when OTHERS then
3663 raise;
3664 end;
3665
3666
3667 i := 0;
3668 for histr in hist0c(l_itype, l_ikey, l_actid) loop
3669
3670 hr_utility.set_location(' kck histr.h_sequence : ' || histr.h_sequence, 666);
3671 hr_utility.set_location(' kck histr.h_from_role : ' || histr.h_from_role, 666);
3672 hr_utility.set_location(' kck histr.h_action : ' || histr.h_action, 666);
3673 hr_utility.set_location(' kck histr.h_action_date : ' || histr.h_action_date, 666);
3674 hr_utility.set_location(' kck histr.h_comment : ' || histr.h_comment, 666);
3675
3676 if ((NOT (l_owner_role <> histr.h_from_role AND histr.h_action = 'Submit' )) AND
3677 (NOT ( histr.h_from_role = l_owner_role and NVL(histr.h_action,'X') = 'Approve'))) then
3678
3679 -- skip if first record was sfl and submitted
3680 cells(j) := to_char(histr.h_sequence);
3681 j := j+1;
3682 wf_directory.GetRoleInfo2(histr.h_from_role, role_info_tbl);
3683
3684 if (disptype = wf_notification.doc_html) then
3685 cells(j) := 'L:'||role_info_tbl(1).display_name;
3686 else
3687 cells(j) := role_info_tbl(1).display_name;
3688 end if;
3689 j := j+1;
3690
3691 if (disptype = wf_notification.doc_html) then
3692 if (histr.h_action is null) then
3693 cells(j) := 'L: ';
3694 else
3695 cells(j) := 'L:'||histr.h_action;
3696 end if;
3697 else
3698 cells(j) := histr.h_action;
3699 end if;
3700 j := j+1;
3701
3702 if (disptype = wf_notification.doc_html) then
3703 cells(j) := 'L:'||to_char(histr.h_action_date);
3704 else
3705 cells(j) := to_char(histr.h_action_date);
3706 end if;
3707 j := j+1;
3708
3709 begin
3710 cells(j) := histr.h_comment;
3711 exception
3712 when OTHERS then
3713 cells(j) := null;
3714 wf_core.clear;
3715 end;
3716 if (disptype = wf_notification.doc_html) then
3717 if (cells(j) is null) then
3718 cells(j) := 'L: ';
3719 else
3720 cells(j) := 'L:'||cells(j);
3721 end if;
3722 end if;
3723 j := j+1;
3724
3725 i := i+1;
3726 end if;
3727 end loop;
3728
3729 -- calculate the sequence
3730 -- Only after we know the number of rows, then we can put the squence
3731 -- number on for each row.
3732 for k in 0..i-1 loop
3733 if (disptype = wf_notification.doc_html) then
3734 cells((k+1)*5+1) := 'C:'||to_char(i-k);
3735 else
3736 cells((k+1)*5+1) := to_char(i-k);
3737 end if;
3738 end loop;
3739
3740
3741
3742 if (disptype = wf_notification.doc_html) then
3743 table_width := '100%';
3744
3745 NTF_Table(
3746 cells => cells,
3747 col => 5,
3748 type => 'H',
3749 rs => result );
3750 else
3751 for k in 1..cells.LAST loop
3752 if (mod(k, 5) <> 0) then
3753 result := result||cells(k)||' '||l_delim||' ';
3754 else
3755 result := result||cells(k)||wf_core.newline;
3756 end if;
3757 end loop;
3758 end if;
3759
3760 return(result);
3761 exception
3762 when OTHERS then
3763 wf_core.context('Wf_Notification', 'Wf_NTF_History', to_char(nid));
3764 raise;
3765 end wf_ntf_history;
3766
3767
3768 BEGIN
3769
3770
3771 hr_utility.set_location('document_id passed : ' || document_id, 666);
3772 --
3773 document_type := wf_notification.doc_html;
3774 document := wf_ntf_history(document_id, document_type);
3775 --
3776 END approval_history;
3777 --
3778 --
3779
3780 PROCEDURE reset_process_section_attr (
3781 itemtype IN VARCHAR2,
3782 itemkey IN VARCHAR2,
3783 actid IN NUMBER,
3784 funcmode IN VARCHAR2,
3785 result OUT NOCOPY VARCHAR2 ) IS
3786 BEGIN
3787 wf_engine.SetItemAttrText(
3788 itemtype => itemType
3789 , itemkey => itemKey
3790 , aname => 'HR_PERINFO_PROCESS_SECTION'
3791 , avalue => NULL );
3792 END reset_process_section_attr ;
3793 --
3794 --
3795 PROCEDURE set_image_source (
3796 itemtype IN VARCHAR2,
3797 itemkey IN VARCHAR2,
3798 actid IN NUMBER,
3799 funcmode IN VARCHAR2,
3800 result OUT NOCOPY VARCHAR2 ) IS
3801 --
3802 l_viewImage VARCHAR2(100);
3803 l_rfcImage VARCHAR2(100);
3804 --
3805 BEGIN
3806 --
3807 l_viewImage := wf_engine.GetActivityAttrText(
3808 itemtype => itemType
3809 ,itemkey => itemKey
3810 ,actid => actId
3811 ,aname => 'VIEW_IMAGE_NAME' );
3812 if ( l_viewImage IS NULL ) then
3813 l_viewImage := 'previewscreen_enabled.gif';
3814 end if;
3815 --
3816 l_rfcImage := wf_engine.GetActivityAttrText(
3817 itemtype => itemType
3818 ,itemkey => itemKey
3819 ,actid => actId
3820 ,aname => 'RFC_IMAGE_NAME' );
3821 --
3822 if ( l_rfcImage IS NULL ) then
3823 l_rfcImage := 'backarro.gif';
3824 end if;
3825 --
3826 --
3827 wf_engine.SetItemAttrText(
3828 itemtype => itemType
3829 , itemkey => itemKey
3830 , aname => 'IMG_VIEW_ACTION'
3831 , avalue => g_OA_MEDIA||l_viewImage
3832 );
3833 --
3834 --
3835 wf_engine.SetItemAttrText(
3836 itemtype => itemType
3837 , itemkey => itemKey
3838 , aname => 'IMG_RFC_ACTION'
3839 , avalue => g_OA_MEDIA||l_rfcImage
3840 );
3841 --
3842 result := 'COMPLETE:SUCCESS';
3843 --
3844 EXCEPTION
3845 WHEN OTHERS THEN
3846 null;
3847 END set_image_source;
3848
3849
3850 FUNCTION set_developer_ntf_msg(
3851 p_itemType IN VARCHAR2,
3852 p_itemKey IN VARCHAR2) RETURN VARCHAR IS
3853 l_ntfId NUMBER;
3854 l_ntf_identifier VARCHAR2(100) := 'HR_NTF_IDENTIFIER';
3855 l_ntf_name VARCHAR2(100);
3856 l_error_message VARCHAR2(2000);
3857 l_sal_basis_change_token VARCHAR2(100);
3858 l_current_person_display_name VARCHAR2(100);
3859 l_process_display_name VARCHAR2(100);
3860 l_note_from_requestor VARCHAR2(2000);
3861 l_note_from_approver VARCHAR2(2000);
3862 l_errors VARCHAR2(100);
3863 l_ntf_err_text VARCHAR2(20000);
3864 BEGIN
3865 l_ntfId := get_notification_id(p_itemType,p_itemKey);
3866 BEGIN
3867 l_ntf_name := WF_NOTIFICATION.getattrtext(l_ntfId,l_ntf_identifier);
3868 EXCEPTION
3869 WHEN OTHERS THEN
3870 NULL;
3871 END;
3872 -- For Commit system errors this item attribute gets populated
3873 BEGIN
3874 l_ntf_err_text := wf_engine.GetItemAttrText(
3875 itemtype => p_itemType,
3876 itemkey => p_itemKey,
3877 aname => 'ERROR_MESSAGE_TEXT');
3878 EXCEPTION
3879 WHEN OTHERS THEN
3880 NULL;
3881 END;
3882 l_errors := 'N';
3883 IF (l_ntf_err_text is not NULL)
3884 THEN
3885 l_errors := 'Y';
3886 hr_utility.set_message(800, 'HRSSA_TXN_ERROR_MSG');
3887 hr_utility.set_message_token('ERROR_MESSAGE', l_ntf_err_text);
3888 hr_multi_message.add( p_message_type => hr_multi_message.G_ERROR_MSG);
3889 END IF;
3890 IF (l_ntf_name = 'HR_EMBED_NTF_PAY_CONTACT_MSG')
3891 THEN
3892 l_sal_basis_change_token := wf_engine.GetItemAttrText(
3893 itemtype => p_itemType,
3894 itemkey => p_itemKey,
3895 aname => 'HR_SALARY_BASIS_CHANGE_TOKEN');
3896 l_current_person_display_name := wf_engine.GetItemAttrText(
3897 itemtype => p_itemType,
3898 itemkey => p_itemKey,
3899 aname => 'CURRENT_PERSON_DISPLAY_NAME');
3900 l_process_display_name := wf_engine.GetItemAttrText(
3901 itemtype => p_itemType,
3902 itemkey => p_itemKey,
3903 aname => 'PROCESS_DISPLAY_NAME');
3904 l_note_from_requestor := wf_engine.GetItemAttrText(
3905 itemtype => p_itemType,
3906 itemkey => p_itemKey,
3907 aname => 'APPROVAL_COMMENT_COPY');
3908
3909 hr_utility.set_message(800, 'HRSSA_MID_PAY_PERIOD_MSG');
3910 hr_utility.set_message_token('SALARY_BASIS_CHANGE_TOKEN', l_sal_basis_change_token);
3911 hr_utility.set_message_token('CURRENT_PERSON_DISPLAY_NAME', l_current_person_display_name);
3912 hr_utility.set_message_token('PROCESS_DISPLAY_NAME',l_process_display_name);
3913 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3914
3915 hr_utility.set_message(800, 'HRSSA_NOTE_FROM_REQUESTOR');
3916 hr_utility.set_message_token('NOTE_FROM_REQUESTOR', l_note_from_requestor);
3917 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3918 l_errors := 'Y';
3919 ELSIF (l_ntf_name = 'HR_EMBED_ON_APPR_NTFY_HR_REP')
3920 THEN
3921 hr_utility.set_message(800, 'HRSSA_INTRVN_ACTION_MSG');
3922 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3923 hr_utility.set_message(800, 'HRSSA_CANCEL_ACTION_MSG');
3924 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3925 l_errors := 'Y';
3926 ELSIF (l_ntf_name = 'HR_EMBED_V5_RFC_OTHER' or l_ntf_name = 'HR_EMBED_V5_RFC_INITIATOR')
3927 THEN
3928 l_note_from_approver := wf_engine.GetItemAttrText(
3929 itemtype => p_itemType,
3930 itemkey => p_itemKey,
3931 aname => 'NOTE_FROM_APPR');
3932 hr_utility.set_message(800, 'HRSSA_NOTE_FROM_APPR');
3933 hr_utility.set_message_token('NOTE_FROM_APPR', l_note_from_approver);
3934 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3935 l_errors := 'Y';
3936 ELSIF (l_ntf_name = 'HR_EMBED_TXN_ERROR_MSG')
3937 THEN
3938 l_error_message := wf_engine.GetItemAttrText(
3939 itemtype => p_itemType,
3940 itemkey => p_itemKey,
3941 aname => 'ERROR_MESSAGE');
3942 hr_utility.set_message(800, 'HRSSA_TXN_ERROR_MSG');
3943 hr_utility.set_message_token('ERROR_MESSAGE', l_error_message);
3944 hr_multi_message.add( p_message_type => hr_multi_message.G_ERROR_MSG);
3945 hr_utility.set_message(800, 'HRSSA_CANCEL_ACTION_MSG');
3946 hr_multi_message.add( p_message_type => hr_multi_message.G_INFORMATION_MSG);
3947 l_errors := 'Y';
3948 END IF;
3949
3950 hr_approval_ss.checktransactionstate(wf_engine.getitemattrnumber
3951 (itemtype => p_itemType
3952 ,itemkey => p_itemKey
3953 ,aname => 'TRANSACTION_ID'));
3954
3955 if(hr_utility.check_warning) Then
3956 l_errors := 'Y';
3957 hr_utility.clear_warning;
3958 end if;
3959
3960 RETURN l_errors;
3961 END set_developer_ntf_msg;
3962
3963 PROCEDURE get_curr_notified_activity (
3964 p_itemType IN VARCHAR2
3965 ,p_itemKey IN VARCHAR2
3966 ,p_activityId OUT NOCOPY NUMBER
3967 ) IS
3968 --
3969 l_activityId NUMBER(20);
3970
3971
3972 CURSOR cur_wf IS
3973 SELECT process_activity
3974 FROM WF_ITEM_ACTIVITY_STATUSES IAS
3975 WHERE ias.item_type = p_itemType
3976 AND ias.item_key = p_itemKey
3977 AND ias.activity_status = 'NOTIFIED'
3978 ORDER BY begin_date DESC, execution_time DESC;
3979 --
3980 BEGIN
3981 IF ( cur_wf%ISOPEN ) THEN
3982 CLOSE cur_wf;
3983 END IF;
3984 --
3985 OPEN cur_wf;
3986 FETCH cur_wf INTO l_activityId;
3987 CLOSE cur_wf;
3988
3989 p_activityId := l_activityId;
3990 --
3991 EXCEPTION
3992 WHEN OTHERS THEN
3993 raise;
3994 --
3995 END get_curr_notified_activity;
3996 END; -- Package Body PQH_SS_WORKFLOW;
[Home] [Help]