1 PACKAGE BODY psp_wf_eff_pkg AS
2 /* $Header: PSPWFEFB.pls 120.3 2008/04/29 12:42:10 amakrish noship $ */
3
4 /*****************************************************************
5 *Function UPDATE_STATUS updates the status of effort *
6 *report. It is called by STATUS_APPROVED, STATUS_REJECTED, *
7 *STATUS_CERTIFIED, STATUS_NEW, STATUS_SUPERSEDED. *
8 *****************************************************************/
9 function UPDATE_STATUS(itemkey in varchar2,
10 funcmode in varchar2,
11 status in varchar2) return VARCHAR2;
12
13 /**********************************************************************************
14 **Function INIT_WORKFLOW is called when an effort report is created. It is called**
15 **from package psp_efforts_pkg.crt. This creates the workflow process, populates **
16 **attributes, and starts the process. **
17 ***********************************************************************************/
18 function INIT_WORKFLOW(a_template_id IN NUMBER)
19 return NUMBER
20 IS
21 ItemType VARCHAR2(30) := 'PSPEFFWF';
22 ItemKey VARCHAR2(30);
23
24 l_omit_approval_step VARCHAR2(1);
25 l_creator_username VARCHAR2(100);
26 l_report_id NUMBER;
27 -- Fix bug 954141
28 l_report_id1 NUMBER;
29 l_max_ver NUMBER;
30 -- End fix bug 954141
31 l_person_id NUMBER;
32 l_begin_date DATE;
33 l_end_date DATE;
34 l_emp_display_name VARCHAR2(240);
35 l_emp_username VARCHAR2(100);
36
37 -- Start fix bug 954141. Added the max(version) and group by in the
38 -- cursor below. Concatenate these two and initiate WF.
39 CURSOR get_report_id_csr IS
40 SELECT effort_report_id, max(version_num)
41 FROM psp_effort_reports
42 WHERE template_id=a_template_id
43 GROUP BY effort_report_id;
44
45 CURSOR get_emp_id_csr IS
46 SELECT person_id
47 FROM psp_effort_reports
48 WHERE effort_report_id = l_report_id;
49
50 CURSOR get_period_details_csr IS
51 SELECT Begin_date,
52 End_date
53 FROM psp_effort_report_templates
54 WHERE template_id = a_template_id;
55
56 CURSOR get_emp_display_name IS
57 SELECT full_name
58 FROM per_all_people_f
59 WHERE person_id =l_person_id
60 AND effective_end_date >= l_begin_date
61 AND effective_start_date <= l_end_date
62 ORDER BY effective_start_date desc;
63
64 BEGIN
65
66 OPEN get_report_id_csr;
67 LOOP
68 FETCH get_report_id_csr INTO l_report_id, l_max_ver;
69 EXIT WHEN get_report_id_csr%NOTFOUND;
70 l_report_id1 := to_char(l_report_id) || to_char(l_max_ver);
71 -- ItemKey := to_char(l_report_id); /* Fix 954141 */
72 ItemKey := l_report_id1;
73 /*-----------------------------*/
74 /*Create a new workflow process*/
75 /*-----------------------------*/
76
77 l_creator_username := FND_GLOBAL.user_name;
78
79 wf_engine.CreateProcess(itemtype => ItemType,
80 itemkey => ItemKey,
81 process => 'EFFORT_REPORT_WF');
82
83 /*Added for bug 7004679 */
84 wf_engine.setitemowner(itemtype => ItemType,
85 itemkey => ItemKey,
86 owner => l_creator_username);
87
88 /*----------------------------------------------------------------------------------------*/
89 /*Set Item Attributes */
90 /*1. PROCESS ----- keep track which phase workflow is in(APPROVAL/CERTIFICATION) */
91 /*2. CEATOR_USERNAME */
92 /*3. CREATOR_DISPLAY_NAME */
93 /*4. EMPLOYEE_PERSON_ID */
94 /*5. EMPLOYEE_USERNAME */
95 /*6. EMPLOYEE_DISPLAY_NAME */
96 /*7. REPORT_ID ---\ */
97 /*8. BEGIN_DATE ------ effort report related info */
98 /*9. END_DATE ---/ */
99 /*10. TEMPLATE_ID */
100 /*----------------------------------------------------------------------------------------*/
101
102 l_omit_approval_step := wf_engine.GetItemAttrText(itemtype, itemkey, 'OMIT_APPROVAL_STEP');
103 IF (l_omit_approval_step = 'Y') THEN
104 wf_engine.SetItemAttrText(itemtype => ItemType,
105 itemkey => ItemKey,
106 aname => 'PROCESS',
107 avalue => 'CERTIFICATION');
108 ELSE
109 wf_engine.SetItemAttrText(itemtype => ItemType,
110 itemkey => ItemKey,
111 aname => 'PROCESS',
112 avalue => 'APPROVAL');
113 END IF;
114
115 wf_engine.SetItemAttrText(itemtype => ItemType,
116 itemkey => ItemKey,
117 aname => 'CREATOR_USERNAME',
118 avalue => l_creator_username);
119
120 wf_engine.SetItemAttrText(itemtype => ItemType,
121 itemkey => ItemKey,
122 aname => 'CREATOR_DISPLAY_NAME',
123 avalue => wf_directory.GetRoleDisplayName(l_creator_username));
124
125 wf_engine.SetItemAttrText(itemtype => ItemType,
126 itemkey => ItemKey,
127 aname => 'REPORT_ID',
128 avalue => l_report_id);
129
130 OPEN get_period_details_csr;
131 FETCH get_period_details_csr INTO l_begin_date, l_end_date;
132 CLOSE get_period_details_csr;
133
134 wf_engine.SetItemAttrText(itemtype => ItemType,
135 itemkey => ItemKey,
136 aname => 'BEGIN_DATE',
137 avalue => l_begin_date);
138
139 wf_engine.SetItemAttrText(itemtype => ItemType,
140 itemkey => ItemKey,
141 aname => 'END_DATE',
142 avalue => l_end_date);
143
144 OPEN get_emp_id_csr;
145 FETCH get_emp_id_csr INTO l_person_id;
146 CLOSE get_emp_id_csr;
147
148 wf_directory.GetUserName('PER', l_person_id, l_emp_username, l_emp_display_name);
149
150 /*Bug 5145170: person name is null when he dont have any record in fnd users
151 In this case get the name from per_all_people_f*/
152
153
154 IF (l_emp_display_name IS NULL) THEN
155 OPEN get_emp_display_name;
156 FETCH get_emp_display_name INTO l_emp_display_name;
157 CLOSE get_emp_display_name;
158 END IF;
159
160 wf_engine.SetItemAttrText(itemtype => ItemType,
161 itemkey => ItemKey,
162 aname => 'EMPLOYEE_PERSON_ID',
163 avalue => l_person_id);
164
165 wf_engine.SetItemAttrText(itemtype => ItemType,
166 itemkey => ItemKey,
167 aname => 'EMPLOYEE_USERNAME',
168 avalue => l_emp_username);
169
170 wf_engine.SetItemAttrText(itemtype => ItemType,
171 itemkey => ItemKey,
172 aname => 'EMPLOYEE_DISPLAY_NAME',
173 avalue => l_emp_display_name);
174
175 wf_engine.SetItemAttrText(itemtype => ItemType,
176 itemkey => ItemKey,
177 aname => 'TEMPLATE_ID',
178 avalue => a_template_id);
179
180 /*-----------------------------------------------------*/
181 /*Start the workflow process */
182 /*-----------------------------------------------------*/
183 wf_engine.startprocess(itemtype => ItemType,
184 itemkey => ItemKey);
185 END LOOP;
186 CLOSE get_report_id_csr;
187
188 RETURN(0);
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 RETURN(-1);
192 WHEN OTHERS THEN
193 RETURN(-2);
194 END INIT_WORKFLOW;
195
196 /*********************************************************
197 **Procedure OMIT_APPROVAL_STEP checks the value of the **
198 **attribute by the same name in the workflow process and**
199 **sends the result. This procedure is called by the **
200 **workflow activity OMIT_APPROVAL_STEP. **
201 **********************************************************/
202 procedure OMIT_APPROVAL_STEP (itemtype in varchar2,
203 itemkey in varchar2,
204 actid in number,
205 funcmode in varchar2,
206 result out NOCOPY varchar2)
207 IS
208 l_omit_approval_step VARCHAR2(1);
209 BEGIN
210
211 IF (funcmode = 'RUN') THEN
212 l_omit_approval_step := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'OMIT_APPROVAL_STEP');
213 result := 'COMPLETE:'||l_omit_approval_step;
214 END IF;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'OMIT_APPROVAL_STEP', itemtype, itemkey, to_char(actid), funcmode);
219 raise;
220
221 END OMIT_APPROVAL_STEP;
222
223 /**************************************************************
224 **Procedure CAN_EMPLOYEE_APPROVE checks the value of the item**
225 **attribute CAN_EMPLOYEE_APPROVE and sends the result to **
226 **workflow process. This procedure is called by the **
227 **workflow activity "Can Employee Approve". **
228 ***************************************************************/
229 procedure CAN_EMPLOYEE_APPROVE(itemtype in varchar2,
230 itemkey in varchar2,
231 actid in number,
232 funcmode in varchar2,
233 result out NOCOPY varchar2)
234 IS
235 l_can_employee_approve varchar2(1);
236 BEGIN
237
238 IF (funcmode = 'RUN') THEN
239 l_can_employee_approve := wf_engine.GetItemAttrText(itemtype, itemkey, 'CAN_EMPLOYEE_APPROVE');
240 result := 'COMPLETE:'||l_can_employee_approve;
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'CAN_EMPLOYEE_APPROVE', itemtype, itemkey, to_char(actid), funcmode);
246 raise;
247
248 END CAN_EMPLOYEE_APPROVE;
249
250 /************************************************************
251 **PROCEDURE CAN_SUPERVISOR_CERTIFY checks if supervisor can**
252 **certify the effort report. If yes, set the certifier's **
253 **display name and username. **
254 *************************************************************/
255 procedure CAN_SUPERVISOR_CERTIFY (itemtype in varchar2,
256 itemkey in varchar2,
257 actid in number,
258 funcmode in varchar2,
259 result out NOCOPY varchar2)
260 IS
261 l_can_supervisor_certify VARCHAR2(1);
262 BEGIN
263
264 IF (funcmode = 'RUN') THEN
265 l_can_supervisor_certify := wf_engine.GetItemAttrText(itemtype, itemkey, 'CAN_SUPERVISOR_CERTIFY');
266 result := 'COMPLETE:'||l_can_supervisor_certify;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'CAN_SUPERVISOR_CERTIFY', itemtype, itemkey, to_char(actid), funcmode);
272 raise;
273
274 END CAN_SUPERVISOR_CERTIFY;
275
276 /***********************************************************
277 **Procedure VERIFY_EMPLOYEE checks if employee username **
278 **exists and populates the item attriutes **
279 **APPROVER_USERNAME and APPROVER_DISPLAY_NAME with the **
280 **employee's. username and display name. This procedure is**
281 **called by the workflow activity VERIFY_EMPLOYEE. **
282 ************************************************************/
283 procedure VERIFY_EMPLOYEE(itemtype in varchar2,
284 itemkey in varchar2,
285 actid in number,
286 funcmode in varchar2,
287 result out NOCOPY varchar2)
288 IS
289 l_employee_username VARCHAR2(100);
290 l_employee_display_name VARCHAR2(240);
291 BEGIN
292
293 IF (funcmode = 'RUN') THEN
294 l_employee_username := wf_engine.GetItemAttrText(itemtype, itemkey, 'EMPLOYEE_USERNAME');
295 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_USERNAME', l_employee_username);
296 l_employee_display_name := wf_engine.GetItemAttrText(itemtype, itemkey, 'EMPLOYEE_DISPLAY_NAME');
297 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_DISPLAY_NAME', l_employee_display_name);
298
299 IF (wf_directory.UserActive(l_employee_username) = TRUE) THEN
300 result := 'COMPLETE:FOUND';
301 ELSE
302 result := 'COMPLETE:NOT_FOUND';
303 END IF;
304
305 END IF;
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'VERIFY_EMPLOYEE', itemtype, itemkey, to_char(actid), funcmode);
310 raise;
311
312 END VERIFY_EMPLOYEE;
313
314 /*******************************************************************************
315 *Procedure VERIFY_SUPERVISOR is called by workflow activity VERIFY SUPERVISOR.*
316 *It finds the supervisor for EMPLOYEE and populdate item attributes: *
317 *certifier_username and certifier_display_name. The procedure returns *
318 *COMPLETE:Found if the supervisor is found; returns COMPLETE:Not Found, if *
319 *the supervisor cannot be found. *
320 *******************************************************************************/
321 procedure VERIFY_SUPERVISOR(itemtype in varchar2,
322 itemkey in varchar2,
323 actid in number,
324 funcmode in varchar2,
325 result out NOCOPY varchar2)
326 IS
327 l_employee_id NUMBER;
328 l_supervisor_id NUMBER;
329
330 l_certifier_username VARCHAR2(100); --column USER_NAME in FND_USER is VARCHAR2(100)
331 l_certifier_display_name VARCHAR2(240); --column FULL_NAME in PER_PEOPLE_F is VARCHAR2(240)
332
333 CURSOR get_supervisor_id_csr IS
334 SELECT assignment.supervisor_id
335 FROM per_assignments_f assignment,
336 per_people_f people
337 WHERE assignment.person_id = l_employee_id
338 AND assignment.supervisor_id = people.person_id
339 AND assignment.assignment_type ='E' --Added for bug 2624259.
340 AND trunc(SYSDATE) BETWEEN people.effective_start_date AND people.effective_end_date
341 AND assignment.primary_flag = 'Y';
342
343 BEGIN
344
345 IF (funcmode = 'RUN') THEN ---<<OUTER IF..THEN>>
346 l_employee_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'EMPLOYEE_PERSON_ID');
347
348 OPEN get_supervisor_id_csr;
349 FETCH get_supervisor_id_csr INTO l_supervisor_id;
350 IF (get_supervisor_id_csr%NOTFOUND) THEN
351 l_supervisor_id := -1;
352 END IF;
353 CLOSE get_supervisor_id_csr;
354
355 wf_directory.GetUserName('PER', l_supervisor_id, l_certifier_username, l_certifier_display_name);
356
357 IF ((l_supervisor_id <> -1) AND
358 (l_certifier_username IS NOT NULL) AND (l_certifier_display_name IS NOT NULL) AND
359 (wf_directory.UserActive(l_certifier_username))) THEN
360 result := 'COMPLETE:FOUND';
361 ELSE
362 result := 'COMPLETE:NOT_FOUND';
363 END IF;
364
365 wf_engine.SetItemAttrText(itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'CERTIFIER_USERNAME',
368 avalue => l_certifier_username);
369
370 wf_engine.SetItemAttrText(itemType => itemtype,
371 itemKey => itemkey,
372 aname => 'CERTIFIER_DISPLAY_NAME',
373 avalue => l_certifier_display_name);
374
375
376 END IF; ---<<OUTER IF..THEN>>
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'VERIFY_SUPERVISOR', itemtype, itemkey, to_char(actid), funcmode);
381 raise;
382
383 END VERIFY_SUPERVISOR;
384
385 /*****************************************************************
386 **Function UPDATE_STATUS updates the status of effort **
387 **report. It is called by STATUS_APPROVED, STATUS_REJECTED, **
388 **STATUS_CERTIFIED, STATUS_NEW, STATUS_SUPERSEDED. **
389 ******************************************************************/
390 function UPDATE_STATUS(itemkey in varchar2,
391 funcmode in varchar2,
392 status in varchar2)
393 return VARCHAR2
394 IS
395 l_version_num NUMBER;
396 --Bug# 954141
397 l_rep_id varchar2(30);
398 --Bug# 954141
399 BEGIN
400
401 IF (funcmode = 'RUN') THEN
402
403 --Bug# 954141
404 l_rep_id := substr(itemkey, 1, length(itemkey) - 1 );
405 -- Bug# 954141
406
407 SELECT max(version_num) INTO l_version_num
408 FROM psp_effort_reports
409 -- WHERE effort_report_id = to_number(itemkey);
410 WHERE effort_report_id = to_number(l_rep_id);
411 --Bug# 954141
412
413 UPDATE psp_effort_reports
414 SET status_code = status
415 -- WHERE effort_report_id = to_number(itemkey);
416 WHERE effort_report_id = to_number(l_rep_id)
417 AND version_num = l_version_num;
418 --Bug# 954141
419
420 RETURN('COMPLETE');
421
422 END IF;
423
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 RETURN('ERROR:'||TO_CHAR(SQLCODE));
427
428 END UPDATE_STATUS;
429
430 /*****************************************************************
431 **PROCEDURE STATUS_APPROVED updates the status of effort **
432 **report to 'A'. It is called in the workflow activity UPDATE_EF**
433 **FORT_REPORT_STATUS_TO_APPROVED. **
434 ******************************************************************/
435 procedure STATUS_APPROVED(itemtype in varchar2,
436 itemkey in varchar2,
437 actid in number,
438 funcmode in varchar2,
439 result out NOCOPY varchar2) IS
440 BEGIN
441
442 IF (funcmode = 'RUN') THEN
443 result := update_status(itemkey, funcmode, 'A');
444 END IF;
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'STATUS_APPROVED', itemtype, itemkey, to_char(actid), funcmode);
449 raise;
450
451 END STATUS_APPROVED;
452
453 /************************************************************
454 **PROCEDURE STATUS_SUPERSEDED updates the status of effort **
455 **report to 'S'. It is called in workflow activity **
456 **UPDATE_EFFORT_REPORT_STATUS_TO_SUPERSEDED. **
457 *************************************************************/
458 procedure STATUS_SUPERSEDED(itemtype in varchar2,
459 itemkey in varchar2,
460 actid in number,
461 funcmode in varchar2,
462 result out NOCOPY varchar2) IS
463 BEGIN
464
465 IF (funcmode = 'RUN') THEN
466 result := update_status(itemkey, funcmode, 'S');
467 END IF;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'STATUS_SUPERSEDED', itemtype, itemkey, to_char(actid), funcmode);
472 raise;
473
474 END STATUS_SUPERSEDED;
475
476 /**********************************************************
477 **PROCEDURE STATUS_CERTIFIED updates the status of effort**
478 **report to 'C'. It is called in workflow activity **
479 **UPDATE_EFFORT_REPORT_STATUS_TO_CERTIFIED. **
480 ***********************************************************/
481 procedure STATUS_CERTIFIED(itemtype in varchar2,
482 itemkey in varchar2,
483 actid in number,
484 funcmode in varchar2,
485 result out NOCOPY varchar2) IS
486 BEGIN
487
488 IF (funcmode = 'RUN') THEN
489 result := update_status(itemkey, funcmode, 'C');
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'STATUS_CERTIFIED', itemtype, itemkey, to_char(actid), funcmode);
495 raise;
496
497 END STATUS_CERTIFIED;
498
499 /*********************************************************
500 **PROCEDURE STATUS_REJECTED updates the status of effort**
501 **report to 'R'. It is called by a workflow activity **
502 **"Update Effort Report Status to Rejected". **
503 **********************************************************/
504 procedure STATUS_REJECTED (itemtype in varchar2,
505 itemkey in varchar2,
506 actid in number,
507 funcmode in varchar2,
508 result out NOCOPY varchar2) IS
509 BEGIN
510
511 IF (funcmode = 'RUN') THEN
512 result := update_status(itemkey, funcmode, 'R');
513 END IF;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'STATUS_REJECTED', itemtype, itemkey, to_char(actid), funcmode);
518 raise;
519
520 END STATUS_REJECTED;
521
522 /***********************************************************************
523 **Procedure SELECT_CERTIFIER is called by a workflow process "Select **
524 **Certifier". It is invoked if user profile option "PSP:Can Supervisor**
525 **Certify" is set to "No". Procedure SELECT_CERTIFIER makes a call to **
526 **PSP_WF_CUSTOM.EFFORT_SELECT_CERTIFIER, where users can customize who**
527 **the certifer is. **
528 ************************************************************************/
529 procedure SELECT_CERTIFIER(itemtype in varchar2,
530 itemkey in varchar2,
531 actid in number,
532 funcmode in varchar2,
533 result out NOCOPY varchar2)
534 IS
535 l_emp_id NUMBER;
536 l_certifier_id NUMBER;
537 l_certifier_username VARCHAR2(100);
538 l_certifier_display_name VARCHAR2(240);
539 BEGIN
540
541 IF (funcmode = 'RUN') THEN
542 l_emp_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'EMPLOYEE_PERSON_ID');
543 psp_wf_custom.effort_select_certifier(l_emp_id, l_certifier_id);
544 wf_directory.GetUserName('PER', l_certifier_id, l_certifier_username, l_certifier_display_name);
545
546 IF ((l_certifier_username IS NOT NULL) AND (l_certifier_display_name IS NOT NULL) AND (wf_directory.UserActive(l_certifier_username))) THEN
547 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_USERNAME', l_certifier_username);
548 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_DISPLAY_NAME', l_certifier_display_name);
549 result := 'COMPLETE:FOUND';
550 ELSE
551 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_USERNAME', NULL);
552 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_DISPLAY_NAME', NULL);
553 result := 'COMPLETE:NOT_FOUND';
554 END IF;
555 END IF;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'SELECT_CERTIFIER', itemtype, itemkey, to_char(actid), funcmode);
560 raise;
561
562 END SELECT_CERTIFIER;
563
564 /***********************************************************************
565 **Procedure SELECT_APPROVER is called by a workflow process "Select **
566 **Approver". It is invoked if user profile option "PSP:Can Employee **
567 **Approve" is set to "No". Procedure SELECT_APPROVER makes a call to **
568 **PSP_WF_CUSTOM.EFFORT_SELECT_APPROVER, where users can customize who **
569 **the approver is. **
570 ************************************************************************/
571 procedure SELECT_APPROVER (itemtype in varchar2,
572 itemkey in varchar2,
573 actid in number,
574 funcmode in varchar2,
575 result out NOCOPY varchar2)
576 IS
577 l_emp_id NUMBER;
578 l_approver_id NUMBER;
579 l_approver_username VARCHAR2(100);
580 l_approver_display_name VARCHAR2(240);
581 BEGIN
582
583 IF (funcmode = 'RUN') THEN
584 l_emp_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'EMPLOYEE_PERSON_ID');
585 psp_wf_custom.effort_select_approver(l_emp_id, l_approver_id);
586 wf_directory.GetUserName('PER', l_approver_id, l_approver_username, l_approver_display_name);
587
588 IF ((l_approver_username IS NOT NULL) AND (l_approver_display_name IS NOT NULL) AND (wf_directory.UserActive(l_approver_username))) THEN
589 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_USERNAME', l_approver_username);
590 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_DISPLAY_NAME', l_approver_display_name);
591 result := 'COMPLETE:FOUND';
592 ELSE
593 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_USERNAME', NULL);
594 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_DISPLAY_NAME', NULL);
595 result := 'COMPLETE:NOT_FOUND';
596 END IF;
597 END IF;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'SELECT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
602 raise;
603
604 END SELECT_APPROVER;
605
606 /***********************************************************************
607 **Procedure GET_APPROVAL_RESPONDER is called by a workflow process **
608 **"Get Final Approver's Name". **
609 ************************************************************************/
610 procedure GET_APPROVAL_RESPONDER(itemtype in varchar2,
611 itemkey in varchar2,
612 actid in number,
613 funcmode in varchar2,
614 result out NOCOPY varchar2)
615 IS
616 l_responder_username VARCHAR2(100);
617 l_responder_display_name VARCHAR2(240);
618
619 CURSOR get_approval_responder_csr IS
620 SELECT responder
621 FROM wf_notifications
622 WHERE notification_id =
623 --(SELECT MAX(notification_id) Commented for bug fix 3263333
624 (SELECT notification_id -- Introduced for bug fix 3263333
625 FROM wf_item_activity_statuses -- Changed to base table for bug fix 3263333
626 WHERE item_type = 'PSPEFFWF' AND
627 item_key = itemkey AND
628 process_activity = actid); -- Introduced for bug fix 3263333
629 --activity_name = 'NOTIFY_APPROVER'); Commented for bug fix 3263333
630 BEGIN
631
632 IF (funcmode = 'RUN') THEN
633 OPEN get_approval_responder_csr;
634 FETCH get_approval_responder_csr INTO l_responder_username;
635 CLOSE get_approval_responder_csr;
636
637 l_responder_display_name := wf_directory.GetRoleDisplayName(l_responder_username);
638
639 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_USERNAME', l_responder_username);
640 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_DISPLAY_NAME', l_responder_display_name);
641
642 result := 'COMPLETE';
643 END IF;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'GET_APPROVAL_RESPONDER', itemtype, itemkey, to_char(actid), funcmode);
648 raise;
649
650 END GET_APPROVAL_RESPONDER;
651
652 /****************************************************************************
653 **Procedure GET_CERTIFICATION_RESPONDER is called by a workflow process **
654 **"Get Final Certifier's Name". **
655 *****************************************************************************/
656 procedure GET_CERTIFICATION_RESPONDER(itemtype in varchar2,
657 itemkey in varchar2,
658 actid in number,
659 funcmode in varchar2,
660 result out NOCOPY varchar2)
661 IS
662 l_responder_username VARCHAR2(100);
663 l_responder_display_name VARCHAR2(240);
664
665 CURSOR get_cert_responder_csr IS
666 SELECT responder
667 FROM wf_notifications
668 WHERE notification_id =
669 --(SELECT MAX(notification_id) Commented for bug fix 3263333
670 (SELECT notification_id -- Introduced for bug fix 3263333
671 FROM wf_item_activity_statuses -- changed to base table for bug fix 3263333
672 WHERE item_type = 'PSPEFFWF' AND
673 item_key = itemkey AND
674 process_activity = actid); -- Introduced for bug fix 3263333
675 --activity_name = 'NOTIFY_CERTIFIER'); Commented for bug fix 3263333
676 BEGIN
677
678 IF (funcmode = 'RUN') THEN
679 OPEN get_cert_responder_csr;
680 FETCH get_cert_responder_csr INTO l_responder_username;
681 CLOSE get_cert_responder_csr;
682
683 l_responder_display_name := wf_directory.GetRoleDisplayName(l_responder_username);
684
685 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_USERNAME', l_responder_username);
686 wf_engine.SetItemAttrText(itemtype, itemkey, 'CERTIFIER_DISPLAY_NAME', l_responder_display_name);
687
688 result := 'COMPLETE';
689 END IF;
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'GET_CERTIFICATION_RESPONDER', itemtype, itemkey, to_char(actid), funcmode);
694 raise;
695
696 END GET_CERTIFICATION_RESPONDER;
697
698
699 /***************************************************************************************************
700 Created By : skotwal
701
702 Date Created By : 17-SEP-2001
703
704 Purpose : For the Enhancement Zero Work Days
705
706 Know limitations, enhancements or remarks
707
708 Change History
709
710 Who When What
711 skotwal 17-SEP-2001 Creating the procedure
712 This procedure checks whether the employee has been
713 terminated or not. If terminated it returns a value 'Y'
714 which will then redirect the workflow to skip the Approval
715 Process and lead towards Certification Process
716
717 ***************************************************************************************************/
718 procedure VERIFY_TERM_EMPLOYEE(itemtype in varchar2,
719 itemkey in varchar2,
720 actid in number,
721 funcmode in varchar2,
722 result out NOCOPY varchar2)
723 IS
724
725 l_current_employee_flag VARCHAR2(1);
726 l_person_id NUMBER(9);
727
728 CURSOR get_term_employee_csr IS
729 SELECT ppf.current_employee_flag
730 FROM per_people_f ppf
731 WHERE ppf.person_id = l_person_id
732 AND trunc(SYSDATE) BETWEEN trunc(ppf.effective_start_date) AND trunc(ppf.effective_end_date);
733
734
735 BEGIN
736 IF (funcmode = 'RUN') THEN
737 l_person_id := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'EMPLOYEE_PERSON_ID');
738
739 OPEN get_term_employee_csr;
740 FETCH get_term_employee_csr INTO l_current_employee_flag;
741 CLOSE get_term_employee_csr;
742
743 IF (l_current_employee_flag IS NULL) THEN
744 wf_engine.SetItemAttrText(itemtype => ItemType,
745 itemkey => ItemKey,
746 aname => 'PROCESS',
747 avalue => 'CERTIFICATION');
748 result := 'COMPLETE:Y';
749 ELSE
750 result := 'COMPLETE:N';
751 END IF;
752
753 END IF;
754 EXCEPTION
755 WHEN OTHERS THEN
756 WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'VERIFY_TERM_EMPLOYEE', itemtype, itemkey, to_char(actid), funcmode);
757 raise;
758
759 END VERIFY_TERM_EMPLOYEE;
760
761
762
763 END psp_wf_eff_pkg;
764
[Home] [Help]