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