1 PACKAGE BODY psp_wf_adj_pkg AS
2 /* $Header: PSPWFAJB.pls 120.1 2008/04/29 12:41:56 amakrish noship $ */
3
4 /***********************************************************************
5 ** Function GET_SUPERVISOR returns the employee's supervisor person ID for
6 ** the given assignment. The employee
7 ** person ID and assignment number are passed in. If there is no supervisor
8 ** for the given assignment, -1 will be returned.
9 ***************************************************************************/
10 FUNCTION get_supervisor(p_person_id IN NUMBER,
11 p_assignment_number IN VARCHAR2)
12 RETURN NUMBER IS
13 l_supervisor_id NUMBER;
14 /*
15 -- Old Cursor
16 CURSOR get_supervisor_id_csr IS
17 SELECT supervisor_id
18 FROM per_assignments_f
19 WHERE person_id = p_person_id
20 AND assignment_number = p_assignment_number
21 AND sysdate between effective_start_date and effective_end_date;
22 */
23 -- Bug : 1994421. Cursor get_supervisor_id_csr rewritten for Enhancement Employee Assignment for Zero Work days.
24
25 CURSOR get_supervisor_id_csr IS
26 SELECT paf1.supervisor_id
27 FROM per_assignments_f paf1
28 WHERE paf1.person_id = p_person_id
29 AND paf1.assignment_number = p_assignment_number
30 AND paf1.effective_end_date = (SELECT max(paf2.effective_end_date)
31 FROM per_assignments_f paf2
32 WHERE paf2.assignment_id = paf1.assignment_id);
33
34 BEGIN
35 OPEN get_supervisor_id_csr;
36 FETCH get_supervisor_id_csr INTO l_supervisor_id;
37 IF get_supervisor_id_csr%NOTFOUND THEN
38 l_supervisor_id := -1;
39 END IF;
40 CLOSE get_supervisor_id_csr;
41
42 RETURN l_supervisor_id;
43 END;
44
45 /***************************************************************************
46 ** This Function is added for Enhancement- Employee assignments with Zero work days.
47 ** Bug 1994421
48 **************************************************************************/
49
50 FUNCTION get_approver_status (p_supervisor_id IN NUMBER)
51 RETURN NUMBER IS
52 l_approver_status NUMBER;
53 l_valid_supervisor NUMBER;
54 BEGIN
55 SELECT count(*)
56 INTO l_valid_supervisor
57 -- FROM per_people_f ppf Commented for Bug 3741272
58 FROM per_all_people_f ppf
59 WHERE ppf.person_id = p_supervisor_id
60 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
61 AND current_employee_flag = 'Y';
62
63 IF l_valid_supervisor > 0 THEN
64 l_approver_status:=1;
65 ELSE
66 l_approver_status:=-1;
67 END IF;
68 RETURN l_approver_status;
69 END get_approver_status;
70
71 /**************************************************************************/
72 /*Procedure UPDATE_ADJUSTMENT_CONTROL_TABLE updated the table
73 PSP_ADJUSTMENT_CONTROL_TABLE with the */
74 /*approver's UserID. */
75 /**************************************************************************/
76 PROCEDURE update_adj_ctrl_table(p_batch_name IN VARCHAR2,
77 p_approver_userID IN NUMBER,
78 p_comments IN VARCHAR2,
79 return_code OUT NOCOPY NUMBER) IS
80 BEGIN
81
82 UPDATE psp_adjustment_control_table
83 SET approver_id = p_approver_userID,
84 approval_date = SYSDATE, -- Added to fix bug 1661405. approval_date is a new column added to table
85 comments = p_comments
86 WHERE adjustment_batch_name = p_batch_name;
87
88 IF (SQL%NOTFOUND) THEN
89 return_code := -1;
90 return;
91 END IF;
92
93 update psp_payroll_controls
94 set status_code = 'N'
95 where batch_name = p_batch_name
96 and source_type = 'A'
97 and status_code = 'C';
98
99 IF (SQL%NOTFOUND) THEN
100 return_code := -1;
101 return;
102 END IF;
103
104 return_code := 0;
105
106 END update_adj_ctrl_table;
107
108 /*****************************************************************************
109 *This procedure is called from generate_lines procedure in PSP_ADJ_DRIVER
110 *when user submits the Adjustment Batch.
111 ****************************************************************************/
112 PROCEDURE init_workflow(p_batch_name IN VARCHAR2,
113 p_person_id IN NUMBER,
114 p_display_name IN VARCHAR2,
115 p_assignment IN VARCHAR2,
116 ---p_earnings_element IN VARCHAR2,
117 p_begin_date IN DATE,
118 p_end_date IN DATE,
119 p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
120 p_comments IN VARCHAR2,
121 p_time_out IN NUMBER,
122 return_code OUT NOCOPY NUMBER)
123 IS
124 ItemType VARCHAR2(30) := 'PSPADJWF';
125 ItemKey VARCHAR2(30);
126
127 l_creator_username VARCHAR2(100);
128
129 BEGIN
130
131 /*---------------------------------------------------------------*/
132 /*1. Created the workflow process "psp_distribution_adjustment" */
133 /*---------------------------------------------------------------*/
134 ItemKey := p_batch_name;
135
136 -- dbms_output.put_line('batch Name '||p_batch_name);
137
138 l_creator_username := FND_GLOBAL.user_name;
139
140 wf_engine.CreateProcess( itemtype => ItemType,
141 itemkey => ItemKey,
142 process => 'PSP_DISTRIBUTION_ADJUSTMENT');
143
144 /*Added for bug 7004679 */
145 wf_engine.setitemowner(itemtype => ItemType,
146 itemkey => ItemKey,
147 owner => l_creator_username);
148
149
150 /*---------------------------------------------------------------*/
151 /*2. Initialize the item attributes: */
152 /* (1) BATCH */
153 /* (2) EMPLOYEE_PERSON_ID */
154 /* (3) EMPLOYEE_DISPLAY_NAME */
155 /* (4) ASSIGNMENT */
156 /* (5) EARNINGS_ELEMENT */
157 /* (6) BEGIN_DATE */
158 /* (7) END_DATE */
159 /* (8) CREATOR_USERNAME */
160 /* (9) CREATOR_DISPLAY_NAME */
161 /* (10) Comments */
162 /* (11) Time Out */
163 /*---------------------------------------------------------------*/
164 wf_engine.SetItemAttrText(itemtype => ItemType,
165 itemkey => ItemKey,
166 aname => 'BATCH',
167 avalue => p_batch_name);
168
169 wf_engine.SetItemAttrNUMBER(itemtype => ItemType,
170 itemkey => ItemKey,
171 aname => 'EMPLOYEE_PERSON_ID',
172 avalue => p_person_id);
173
174 wf_engine.SetItemAttrText(itemtype => ItemType,
175 itemkey => ItemKey,
176 aname => 'EMPLOYEE_DISPLAY_NAME',
177 avalue => p_display_name);
178
179 wf_engine.SetItemAttrText(itemtype => ItemType,
180 itemkey => ItemKey,
181 aname => 'ASSIGNMENT',
182 avalue => p_assignment);
183 /*
184 wf_engine.SetItemAttrText(itemtype => ItemType,
185 itemkey => ItemKey,
186 aname => 'EARNINGS_ELEMENT',
187 avalue => p_earnings_element); */
188
189 wf_engine.SetItemAttrDATE(itemtype => ItemType,
190 itemkey => ItemKey,
191 aname => 'BEGIN_DATE',
192 avalue => p_begin_date);
193
194 wf_engine.SetItemAttrDATE(itemtype => ItemType,
195 itemkey => ItemKey,
196 aname => 'END_DATE',
197 avalue => p_end_date);
198
199 -- Introduced the following for bug fix 2916848
200 wf_engine.SetItemAttrText(itemtype => ItemType,
201 itemkey => ItemKey,
202 aname => 'CURRENCY_CODE',
203 avalue => p_currency_code);
204 -- End of bug fix 2916848
205
206
207 wf_engine.SetItemAttrText(itemtype => ItemType,
208 itemkey => ItemKey,
209 aname => 'CREATOR_USERNAME',
210 avalue => l_creator_username);
211
212 wf_engine.SetItemAttrText(itemtype => ItemType,
213 itemkey => ItemKey,
214 aname => 'CREATOR_DISPLAY_NAME',
215 avalue => wf_directory.GetRoleDisplayName(l_creator_username));
216
217 wf_engine.SetItemAttrText(itemtype => ItemType,
218 itemkey => ItemKey,
219 aname => 'NOTE',
220 avalue => p_comments);
221
222
223 wf_engine.SetItemAttrNUMBER(itemtype => ItemType,
224 itemkey => ItemKey,
225 aname => 'TIME_OUT',
226 avalue => p_time_out);
227
228 -- dbms_output.put_line('After setting the attribute');
229 /*---------------------------------------------------------------*/
230 /*3. Start the workflow process "psp_distribution_adjustment" */
231 /*---------------------------------------------------------------*/
232 wf_engine.StartProcess(itemtype => ItemType,
233 itemkey => ItemKey);
234 -- dbms_output.put_line('After starting process');
235
236 return_code := 0;
237
238 END init_workflow;
239
240 /***************************************************************************
241 ** Procedure SELECT_APPROVER is called by "Select Approver" activity in the
242 ** distribution adjustment workflow process.
243 ** By default, the supervisor is the approver.
244 ** If customization is needed, enter your code in
245 ** PSP_WF_ADJ_CUSTOM.select_approver_custom.
246 ****************************************************************************/
247 PROCEDURE select_approver(itemtype IN VARCHAR2,
248 itemkey IN VARCHAR2,
249 actid IN NUMBER,
250 funcmode IN VARCHAR2,
251 result OUT NOCOPY VARCHAR2)
252 IS
253
254 l_person_id NUMBER;
255 l_custom_supervisor_id NUMBER;
256 l_assignment_number VARCHAR2(30);
257 l_supervisor_id NUMBER;
258 l_approver_status NUMBER;
259 l_approver_username VARCHAR2(100);
260 l_approver_display_name VARCHAR2(240);
261 BEGIN
262
263 IF (funcmode = 'RUN') THEN
264 l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'EMPLOYEE_PERSON_ID');
267 l_assignment_number := wf_engine.GetItemAttrText(itemtype => itemtype,
268 itemkey => itemkey,
269 aname => 'ASSIGNMENT');
270
271 /*------------------------------------------------------------------
272 **By default, the supervisor is the approver. To get the supervisor's
273 ** person ID call get_supervisor():
274 ** l_supervisor_id := get_supervisor(l_person_id, assignment_number);
275 ** However, if the approver is not the employee's supervisor, we need
276 ** to customize the program to select the approver.
277 ** Customize code can be entered in psp_wf_adj_custom.select_approver_custom
278 ** procedure and returns the appropriate supervisior id.
279 **---------------------------------------------------------------------*/
280
281 l_supervisor_id := get_supervisor(l_person_id, l_assignment_number);
282
283 -- Bug : 1994421 Code added for Enhancement Employee Assignment with Zero work days.
284 IF l_supervisor_id <> -1 THEN
285 l_approver_status := get_approver_status(l_supervisor_id);
286 END IF;
287 -- Bug : 1994421 Code ended for Enhancement Employee Assignment with Zero work days.
288
289 --for customization purpose
290 -- two standard value person_id and assignment_number is passed
291 -- If more values are required user can get from workflow .
292 -- All internal names are given in the custom package.
293 psp_wf_adj_custom.select_approver_custom(itemtype,
294 itemkey,
295 actid,
296 funcmode,
297 l_person_id,
298 l_assignment_number,
299 l_custom_supervisor_id);
300 if l_custom_supervisor_id is not null
301 then
302 l_supervisor_id := l_custom_supervisor_id;
303 l_approver_status := 0; -- Introduced for bug fix 3443921
304 end if;
305
306 wf_directory.GetUserName('PER', l_supervisor_id, l_approver_username, l_approver_display_name);
307
308 /*Added for bug 7004679 */
309 wf_engine.setitemowner(itemtype => ItemType,
310 itemkey => ItemKey,
311 owner => l_approver_username);
312
313 -- Bug : 1994421
314 -- Added an IF condition to check Terminated approver or Employee (irrespective of Valid or terminated) with No approver.
315
316 IF l_approver_status=-1 or l_supervisor_id=-1 THEN
317 result := 'COMPLETE:NOT_FOUND';
318 ELSE
319 IF (wf_directory.UserActive(l_approver_username)) THEN
320 wf_engine.SetItemAttrText(itemtype => itemtype,
321 itemkey => itemkey,
322 aname => 'APPROVER_USERNAME',
323 avalue => l_approver_username);
324 wf_engine.SetItemAttrText(itemtype => itemtype,
325 itemkey => itemkey,
326 aname => 'APPROVER_DISPLAY_NAME',
327 avalue => l_approver_display_name);
328 result := 'COMPLETE:FOUND';
329 ELSE
330 result := 'COMPLETE:NOT_FOUND';
331 END IF;
332 END IF;
333 END IF; --end of IF (funcmode = 'RUN') THEN--
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 wf_core.context('PSP_WF_ADJ_PKG', 'SELECT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
338 raise;
339 END select_approver;
340
341 /**************************************************************************
342 ** Procedure UNDO_DISTRIBUTION_ADJUSTMENT is called by "Undo Distribution
343 ** Adjustment" activity in the distribution adjustment workflow process.
344 ** If the adjustment batch is cancelled by the creator or rejected by
345 ** the approver, the database will be returned to the state that is before
346 ** the batch is created.
347 ***************************************************************************/
348 PROCEDURE undo_distribution_adjustment(itemtype IN VARCHAR2,
349 itemkey IN VARCHAR2,
350 actid IN NUMBER,
351 funcmode IN VARCHAR2,
352 result OUT NOCOPY VARCHAR2)
353 IS
354 l_batch_name VARCHAR2(30);
355 l_comments VARCHAR2(2000);
356 l_errbuf VARCHAR2(2000);
357 l_return_code NUMBER;
358 l_business_group_id NUMBER;
359 l_set_of_books_id NUMBER;
360 BEGIN
361
362 IF (funcmode = 'RUN') THEN
363 l_batch_name := wf_engine.GetItemAttrText(itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'BATCH');
366
367 l_comments := wf_engine.GetItemAttrText(itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'NOTE');
370
371 l_business_group_id := to_number(psp_general.get_specific_profile('PER_BUSINESS_GROUP_ID'));
372
373 l_set_of_books_id := to_number(psp_general.get_specific_profile('GL_SET_OF_BKS_ID'));
374
375 psp_adj_driver.undo_adjustment(l_batch_name, l_business_group_id,
376 l_set_of_books_id,l_comments,
377 l_errbuf, l_return_code);
378 IF (l_return_code = 0) THEN
379 result := 'COMPLETE';
380 ELSE
381 result := 'ERROR';
382 END IF;
383 END IF;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 wf_core.context('PSP_WF_ADJ_PKG', 'UNDO_DISTRIBUTION_ADJUSTMENT', itemtype, itemkey, to_char(actid), funcmode);
388 raise;
389 END undo_distribution_adjustment;
390
391
392 /***********************************************************************
393 ** Procedure GET_APPROVAL_RESPONDER is called by workflow activity
394 ** Get Final Approver" to figure out who is the final approver in the
395 ** forwarding path.
396 ************************************************************************/
397 PROCEDURE get_approval_responder(itemtype in varchar2,
398 itemkey in varchar2,
399 actid in number,
400 funcmode in varchar2,
401 result out NOCOPY varchar2)
402 IS
403 l_responder_username VARCHAR2(100);
404 l_responder_display_name VARCHAR2(240);
405
406 CURSOR get_approval_responder_csr IS
407 SELECT responder
408 FROM wf_notifications
409 WHERE notification_id =
410 -- Introduced the following for bug fix 3263333
411 (SELECT ias.notification_id
412 FROM wf_lookups l_at,
413 wf_lookups l_as,
414 wf_activities_vl a,
415 wf_process_activities pa,
416 wf_item_types_vl it,
417 wf_items i,
418 wf_item_activity_statuses ias
419 WHERE ias.item_type = itemtype
420 AND ias.item_key = itemkey
421 AND i.item_type = itemtype
422 AND i.item_key = itemkey
423 AND i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
424 AND i.item_type = it.name
425 AND ias.process_activity = pa.instance_id
426 AND pa.activity_name = a.name
427 AND pa.activity_item_type = a.item_type
428 AND l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
429 AND l_at.lookup_code = a.type
430 AND l_as.lookup_type = 'WFENG_STATUS'
431 AND l_as.lookup_code = ias.activity_status
432 AND a.name = 'NOT_APPROVAL_REQUIRED');
433 /***** Commented the following for bug fix 3263333
434 --(SELECT MAX(notification_id) Commented for bug fix 3263333
435 (SELECT notification_id -- Introduced for bug fix 3263333
436 FROM wf_item_activity_statuses -- changed to base table for bug fix 3263333
437 WHERE item_type = 'PSPADJWF' AND
438 item_key = itemkey AND
439 process_activity = actid); -- Introduced for bug fix 3263333
440 --activity_name = 'NOT_APPROVAL_REQUIRED'); Commented for bug fox 3263333
441 End of comment for bug fix 3263333 *****/
442 BEGIN
443
444 IF (funcmode = 'RUN') THEN
445 OPEN get_approval_responder_csr;
446 FETCH get_approval_responder_csr INTO l_responder_username;
447 CLOSE get_approval_responder_csr;
448
449 l_responder_display_name := wf_directory.GetRoleDisplayName(l_responder_username);
450
451 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_USERNAME', l_responder_username);
452 wf_engine.SetItemAttrText(itemtype, itemkey, 'APPROVER_DISPLAY_NAME', l_responder_display_name);
453
454 result := 'COMPLETE';
455 END IF;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 WF_CORE.CONTEXT('PSP_WF_ADJ_PKG', 'GET_APPROVAL_RESPONDER', itemtype, itemkey, to_char(actid), funcmode);
460 raise;
461 END get_approval_responder;
462
463
464 /************************************************************************
465 ** Procedure RECORD_APPROVER is called by workflow activity "Record Approver".
466 ** When the distribution adjustment batch is approved, the approver's
467 ** user ID is recorded in table PSP_ADJUSTMENT_CONTROL_TABLE.
468 ************************************************************************/
469 PROCEDURE record_approver(itemtype IN VARCHAR2,
470 itemkey IN VARCHAR2,
471 actid IN NUMBER,
472 funcmode IN VARCHAR2,
473 result OUT NOCOPY VARCHAR2)
474 IS
475 l_approver_username VARCHAR2(100);
476 l_approver_userID NUMBER(15);
477 l_batch_name VARCHAR2(30);
478 l_return_code NUMBER;
479 l_comments VARCHAR2(2000);
480
481 CURSOR get_user_id_csr IS
482 SELECT user_id
483 FROM fnd_user
484 WHERE user_name = l_approver_username;
485 BEGIN
486
487 IF (funcmode = 'RUN') THEN
488 l_approver_username := wf_engine.GetItemAttrText(itemtype => itemtype,
489 itemkey => itemkey,
490 aname => 'APPROVER_USERNAME');
491
492 OPEN get_user_id_csr;
493 FETCH get_user_id_csr INTO l_approver_userID;
494 CLOSE get_user_id_csr;
495
496 IF l_approver_userID IS NULL THEN
497 result := 'ERROR';
498 ELSE
499 l_batch_name := wf_engine.GetItemAttrText(itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'BATCH');
502 l_comments := wf_engine.GetItemAttrText(itemtype => itemtype,
503 itemkey => itemkey,
504 aname => 'NOTE');
505 update_adj_ctrl_table(l_batch_name, l_approver_userID,l_comments, l_return_code);
506 IF l_return_code = -1 THEN
507 result := 'ERROR';
508 ELSE
509 result := 'COMPLETE';
510 END IF;
511 END IF;
512 END IF;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 WF_CORE.CONTEXT('PSP_WF_ADJ_PKG', 'GET_APPROVAL_RESPONDER', itemtype, itemkey, to_char(actid), funcmode);
517 raise;
518 END record_approver;
519
520 /***************************************************************************
521 ** This procedure can be used to omit the approval step. The valid return
522 ** values are COMPLETE:N or COMPLETE:Y. Present Code is assumed approval
523 ** required and the value is set to N.
524 ** If the value is set to 'Y' then adjustment will directly be approved
525 ** and ready for S and T.
526 ** Customization code can be put in
527 ** psp_wf_adj_custom.omit_approval_custom.
528 **************************************************************************/
529 PROCEDURE omit_approval (itemtype IN VARCHAR2,
530 itemkey IN VARCHAR2,
531 actid IN NUMBER,
532 funcmode IN VARCHAR2,
533 result OUT NOCOPY VARCHAR2)
534 IS
535 p_omit_approval VARCHAR2(1);
536 BEGIN
537 psp_wf_adj_custom.omit_approval_custom
538 (itemtype,
539 itemkey,
540 actid,
541 funcmode,
542 p_omit_approval);
543 if p_omit_approval = 'N' -- preferred value
544 then
545 result := 'COMPLETE:N';
546 elsif p_omit_approval = 'Y'
547 then
548 result := 'COMPLETE:Y';
549 end if;
550 END omit_approval;
551
552 /****************************************************************************
553 ** This procedure record creator as approver in case of OMIT_APPROVAL returns Y
554 ** means approval is not required from approver. At present approver is creator.
555 ** If customization is required please enter your code in
556 ** psp_wf_adj_custom.record_creator_custom package.
557 ******************************************************************************/
558 PROCEDURE record_creator (itemtype IN VARCHAR2,
559 itemkey IN VARCHAR2,
560 actid IN NUMBER,
561 funcmode IN VARCHAR2,
562 result OUT NOCOPY VARCHAR2)
563 IS
564 l_creator_username VARCHAR2(100);
565 l_creator_user_id NUMBER;
566 l_custom_approver_id NUMBER;
567 l_batch_name VARCHAR2(100);
568 l_comments VARCHAR2(2000);
569 l_return_code NUMBER;
570 BEGIN
571 IF (funcmode = 'RUN') THEN
572 l_creator_username := wf_engine.GetItemAttrText(itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'CREATOR_USERNAME');
575 BEGIN
576 SELECT user_id
577 into l_creator_user_id
578 FROM fnd_user
579 WHERE user_name = l_creator_username;
580 EXCEPTION
581 WHEN OTHERS THEN
582 result := 'ERROR';
583 end;
584
585 IF l_creator_user_id IS NULL THEN
586 result := 'ERROR';
587 ELSE
588 l_batch_name := wf_engine.GetItemAttrText(itemtype => itemtype,
589 itemkey => itemkey,
590 aname => 'BATCH');
591 l_comments := wf_engine.GetItemAttrText(itemtype => itemtype,
592 itemkey => itemkey,
593 aname => 'NOTE');
594
595 psp_wf_adj_custom.record_creator_custom(
596 itemtype,
597 itemkey,
598 actid,
599 funcmode,
600 l_custom_approver_id);
601 if l_custom_approver_id is not null
602 then
603 l_creator_user_id := l_custom_approver_id;
604 end if;
605
606 update_adj_ctrl_table(l_batch_name, l_creator_user_id,l_comments, l_return_code);
607 IF l_return_code = -1 THEN
608 result := 'ERROR';
609 ELSE
610 result := 'COMPLETE';
611 END IF;
612 END IF;
613 END IF;
614
615 EXCEPTION
616 WHEN OTHERS THEN
617 WF_CORE.CONTEXT('PSP_WF_ADJ_PKG', 'RECORD_CREATOR', itemtype, itemkey, to_char(actid), funcmode);
618 raise;
619 END record_creator;
620
621 END psp_wf_adj_pkg;
[Home] [Help]