1 PACKAGE BODY HR_SUPERVISOR_SS
2 /* $Header: hrsupwrs.pkb 120.14.12020000.2 2012/07/04 23:59:30 amnaraya ship $*/
3 AS
4 gv_activity_name constant varchar2(50) := 'HR_CHANGE_MANAGER';
5 gv_date_format constant varchar2(10) :='RRRR-MM-DD';
6 gv_package constant varchar2(30) := 'HR_SUPERVISOR_SS';
7 g_package constant varchar2(30) := 'HR_SUPERVISOR_SS';
8 g_applicant_hire boolean := false;
9
10 /*
11 ||===========================================================================
12 || FUNCTION: update_object_version
13 || DESCRIPTION: Update the object version number in the transaction step
14 || to pass the invalid object api error for Save for Later.
15 ||=======================================================================
16 */
17 PROCEDURE update_object_version
18 (p_transaction_step_id in number
19 ,p_login_person_id in number) IS
20
21 l_proc constant varchar2(100) := g_package || ' update_object_version';
22 BEGIN
23 hr_utility.set_location('Entering'|| l_proc,5);
24 -- We don't need to have specific code to update the object version
25 -- number for SAVE_FOR_LATER because in update_supervisor procedure, it
26 -- always gets the object version number at run time every time. Therefore,
27 -- no specific code is required here. We just need to have dummy stub
28 -- for SAVE_FOR_LATER to resolve the call.
29 null;
30 hr_utility.set_location('Leaving'|| l_proc,10);
31 END update_object_version;
32
33 /*
34 ||===========================================================================
35 || PROCEDURE: branch_on_cost_center_mgr
36 || DESCRIPTION:
37 || This procedure will read the CURRENT_PERSON_ID item level
38 || attribute value and then find out nocopy if the employee to be terminated
39 || is a cost center manager or not. If yes, it will set the WF item
40 || attribute HR_TERM_COST_CENTER_MGR_FLAG to 'Y' and the WF result code
41 || will be set to "Y". In doing so, workflow will transition to the
42 || Cost Center page accordingly.
43 || This procedure will set the wf transition code as follows:
44 || (Y/N)
45 || For 'Y' => branch to Cost Center page
46 || 'N' => do not branch to Cost Center page
47 ||=======================================================================
48 */
49 PROCEDURE branch_on_cost_center_mgr
50 (itemtype in varchar2
51 ,itemkey in varchar2
52 ,actid in number
53 ,funcmode in varchar2
54 ,resultout out nocopy varchar2)
55 IS
56
57 ld_effective_date date default trunc(sysdate);
58 ln_person_id number default null;
59 ln_creator_person_id number default null;
60 lv_high_end_date constant varchar2(12) := '4712/12/31';
61 lv_term_sup_flag varchar2(30) default null;
62 l_proc varchar2(100) :=g_package || ' branch_on_cost_center_mgr';
63 ln_assignment_id number default null;
64 dummy varchar2(2);
65 l_term_sec_asg varchar2(2);
66 l_vol_term varchar2(6);
67 dummy_2 varchar2(2);
68 -- This cursor is copied from the Cost Center Manager Relationship Module,
69 -- see $PER_TOP/java/selfservice/ccmgr/server/HRCCMgrRelationshipsVO.java.
70 -- However, it has removed security check in the where clause because we want
71 -- to issue a notification when the login person does not have access to both
72 -- the organziation and the cost center manager. So, only this removal of
73 -- security check is different from the java HRCCMgrRelationshipsVO.java
74 -- VO.
75 -- For any problem, please consult the owner of that module.
76 -- A given person (the selected person) can be assigned as a cost center
77 -- manager in more than one cost centers.
78 -- The has_update_access (='Y') column signifies that the person has access
79 -- to both the Organization and the Manager in the cost center
80 -- relationship(s)
81 -- Security Check:
82 -- Where clause: fetch all relationships that are current or future dated
83 -- that the person has access to either Organization Or Manager.
84
85 CURSOR csr_update_access_check IS
86 SELECT hao.organization_id
87 ,fnd_date.canonical_to_date(cost_center.ORG_INFORMATION3) start_date
88 ,fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4) end_date
89 ,decode(
90 (decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE'
91 ,HR_SECURITY.SHOW_RECORD
92 ('HR_ALL_ORGANIZATION_UNITS'
93 ,HAO.ORGANIZATION_ID
94 )
95 ),'TRUE',0,1
96 ) +
97 decode(decode(hr_general.get_xbg_profile
98 ,'Y', hao.business_group_id
99 ,hr_general.get_business_group_id
100 )
101 ,hao.business_group_id,0,1
102 ) +
103 decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE'
104 ,HR_SECURITY.SHOW_RECORD
105 ('PER_ALL_PEOPLE_F'
106 ,PAP.PERSON_ID
107 ,PAP.PERSON_TYPE_ID
108 ,PAP.EMPLOYEE_NUMBER
109 ,PAP.APPLICANT_NUMBER
110 )
111 )
112 ,'TRUE',0,1
113 ) +
114 decode(decode(hr_general.get_xbg_profile
115 ,'Y',pap.business_group_id
116 ,hr_general.get_business_group_id
117 )
118 ,pap.business_group_id,0,1
119 )
120 ),0,'Y','N'
121 ) has_update_access
122 FROM hr_organization_information cost_center
123 ,per_all_people_f pap, hr_all_organization_units hao
124 WHERE cost_center.ORG_INFORMATION2 = to_char(pap.person_id)
125 AND cost_center.org_information_context = 'Organization Name Alias'
126 AND pap.person_id = ln_person_id
127 AND (pap.current_employee_flag = 'Y' or pap.current_npw_flag = 'Y')
128 AND hao.organization_id = cost_center.organization_id
129 AND trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
130 AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
131 /* Excluding pending approvals */
132 AND not exists (select 'e' from hr_api_transaction_steps s, hr_api_transactions t
133 where s.api_name = 'HR_CCMGR_SS.PROCESS_API'
134 --Bug 3034218: Exclude current process, include v5 pending status RO, ROS and YS
135 and s.transaction_id = t.transaction_id and status IN ('YS', 'Y','RO','ROS')
136 and t.item_key <> itemKey
137 --BUG 3648732
138 and exists
139 (
140 SELECT NULL FROM hr_api_transaction_values v
141 WHERE s.transaction_step_id+0 = v.transaction_step_id
142 AND v.name = 'P_ORGANIZATION_ID'
143 AND v.number_value = hao.organization_id
144 )
145 and rownum < 2)
146 AND exists (select 'e' from hr_organization_information class, hr_org_info_types_by_class ctype
147 where ctype.org_information_type = 'Organization Name Alias'
148 and ctype.org_classification = class.org_information1
149 and class.org_information_context = 'CLASS'
150 and class.org_information2 = 'Y'
151 and class.organization_id = cost_center.organization_id)
152 AND (nvl(fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4),ld_effective_date) >= ld_effective_date
153 Or (fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4) <= ld_effective_date
154 and fnd_date.canonical_to_date(cost_center.ORG_INFORMATION3)
155 = (select max(fnd_date.canonical_to_date(oi.ORG_INFORMATION3))
156 from hr_organization_information oi
157 where oi.org_information_context = 'Organization Name Alias'
158 and oi.organization_id = cost_center.organization_id))); -- 2476134
159
160 -- NOTE: We need to use the primary assignment because we're trying to
161 -- derive the manager id of the login person, not the action employee.
162 CURSOR csr_get_mgr_id_of_login IS
163 SELECT paf.supervisor_id
164 FROM per_all_assignments_f paf
165 ,per_all_people_f ppf
166 WHERE ppf.person_id = ln_creator_person_id
167 and paf.person_id = ppf.person_id
168 and ld_effective_date between ppf.effective_start_date
169 and ppf.effective_end_date
170 and ld_effective_date between paf.effective_start_date
171 and paf.effective_end_date
172 and (paf.assignment_type = 'E' and ppf.current_employee_flag = 'Y'
173 or paf.assignment_type = 'C' and ppf.current_npw_flag = 'Y')
174 and paf.primary_flag = 'Y';
175
176 ln_manager_id number default null;
177 lv_no_access_to_some_cc varchar2(1) default null;
178
179 cursor csr_attr_value(actid in number, name in varchar2) is
180 SELECT WAAV.TEXT_VALUE Value
181 FROM WF_ACTIVITY_ATTR_VALUES WAAV
182 WHERE WAAV.PROCESS_ACTIVITY_ID = actid
183 AND WAAV.NAME = name;
184
185 BEGIN
186 --
187 -- Get the action person id, ie. selected person id
188 hr_utility.set_location('Entering '|| l_proc,5);
189 ln_person_id := wf_engine.GetItemAttrNumber
190 (itemtype => itemtype
191 ,itemkey => itemkey
192 ,aname => 'CURRENT_PERSON_ID');
193
194 ln_assignment_id := wf_engine.GetItemAttrNumber (
195 itemtype => itemtype,
196 itemkey => itemkey,
197 aname => 'CURRENT_ASSIGNMENT_ID');
198
199 -- Get the login person id
200 ln_creator_person_id := wf_engine.GetItemAttrNumber
201 (itemtype => itemtype
202 ,itemkey => itemkey
203 ,aname => 'CREATOR_PERSON_ID');
204
205
206 -- The termination effective date was stored in the wf item attribute
207 -- CURRENT_EFFECTIVE_DATE as a date data type.
208 ld_effective_date := wf_engine.GetItemAttrDate
209 (itemtype => itemtype
210 ,itemkey => itemkey
211 ,aname => 'CURRENT_EFFECTIVE_DATE');
212
213 -- Need to get the wf item attribute HR_TERM_SUP_FLAG so that the Supervisor
214 -- page knows whether the caller is from Termination or not.
215 lv_term_sup_flag := wf_engine.GetItemAttrText
216 (itemtype => itemtype
217 ,itemkey => itemkey
218 ,aname => 'HR_TERM_SUP_FLAG');
219
220 -- We will set the result code to 'Y' only if the item attribute
221 -- HR_TERM_SUP_FLAG is set to 'Y', that means the caller is from Termination.
222 -- Otherwise, we don't need to branch to the Cost Center Manager module.
223 IF lv_term_sup_flag = 'Y' THEN
224 hr_utility.trace('In (if lv_term_sup_flag = Y)'|| l_proc);
225 resultout := 'COMPLETE:'|| 'N';
226 lv_no_access_to_some_cc := 'N';
227
228 select primary_flag into dummy from per_all_assignments_f where
229 assignment_id=ln_assignment_id and ld_effective_date between
230 effective_start_date and effective_end_date;
231
232 l_term_sec_asg := wf_engine.getitemattrtext(itemtype, itemkey,
233 'HR_TERM_SEC_ASG',true);
234
235 -- We need to run the update_access_check cursor to see if the
236 -- login person has the security access to select another manager
237 -- for the cost centers that the terminated employee is responsible
238 -- for. If the login person has access to some cost centers and not
239 -- all of them, we need to construct the WF item_attribute
240 -- hr_mgr_id_of_login_person to contain the login person's manager's
241 -- person_id. Later on after the Confirmation page, a notification
242 -- will be sent to the login person's supervisor to explain to them
243 -- him that the manager of a cost center has been terminated and a new
244 -- one needs to be assigned. If the login person does not have a
245 -- supervisor, per CM Beach on 02/18/2002, a notification will be sent
246 -- to the login person himself so that he can forward the email to
247 -- whomever appropriate to reassign a new cost center manager.
248 --
249 if (l_term_sec_asg is null OR l_term_sec_asg <> 'Y' OR dummy = 'Y') then
250 FOR get_update_access in csr_update_access_check LOOP
251 IF get_update_access.has_update_access = 'Y' THEN
252 resultout := 'COMPLETE:'|| 'Y';
253 ELSE
254 lv_no_access_to_some_cc := 'Y';
255 END IF;
256 END LOOP;
257 end if;
258
259 if (ln_person_id = fnd_global.employee_id) then
260 l_vol_term := wf_engine.getitemattrtext(itemtype, itemkey,
261 'HR_VOL_TERM_SS',true);
262 if (l_vol_term is not null) then
263 open csr_attr_value(actid,'BYPASS_ORG_MGR');
264 fetch csr_attr_value into dummy_2;
265 close csr_attr_value;
266 end if;
267 end if;
268
269 if (dummy_2 = 'Y' and resultout = 'COMPLETE:Y') then
270 resultout := 'COMPLETE:'|| 'N';
271 if (l_vol_term = 'SUP') then
272 wf_engine.setitemattrtext(itemtype,itemkey,'HR_VOL_TERM_SS','BOTH');
273 elsif (l_vol_term = 'Y') then
274 wf_engine.setitemattrtext(itemtype,itemkey,'HR_VOL_TERM_SS','CCMGR');
275 end if;
276 end if;
277
278 -- We need to get the login person's manager id and set the WF item
279 -- attribute hr_mgr_id_of_login_person if the resultout is 'Y' and
280 -- the lv_no_access_to_some_cc is 'Y' also. That means there
281 -- is some cost centers that the login person cannot access and the
282 -- person terminated is a cost center manager.
283
284 IF lv_no_access_to_some_cc = 'Y' THEN
285 hr_utility.trace('In (if lv_no_access_to_some_cc = Y)'|| l_proc);
286 OPEN csr_get_mgr_id_of_login;
287 FETCH csr_get_mgr_id_of_login into ln_manager_id;
288 IF csr_get_mgr_id_of_login%NOTFOUND THEN
289 -- set the WF item attribute hr_mgr_id_of_login_person to the
290 -- login person himself if he does not have a manager to report
291 -- to. This is concurred by C. Beach on 02/18/2002. The reason
292 -- is that the login person can forward the notification to
293 -- someone else.
294
295 wf_engine.SetItemAttrNumber
296 (itemtype => itemtype
297 ,itemkey => itemkey
298 ,aname => 'HR_MGR_ID_OF_LOGIN_PERSON'
299 ,avalue => ln_creator_person_id);
300 ELSE
301 wf_engine.SetItemAttrNumber
302 (itemtype => itemtype
303 ,itemkey => itemkey
304 ,aname => 'HR_MGR_ID_OF_LOGIN_PERSON'
305 ,avalue => ln_manager_id);
306 END IF;
307
308 CLOSE csr_get_mgr_id_of_login;
309 END IF;
310 END IF; -- lv_term_sup_flag = 'Y'
311 hr_utility.set_location('Leaving'|| l_proc,20);
312 EXCEPTION
313 WHEN OTHERS THEN
314 hr_utility.set_location('EXCEPTION'|| l_proc,555);
315 WF_CORE.CONTEXT(gv_package
316 ,'BRANCH_ON_COST_CENTER_MGR'
317 ,itemtype
318 ,itemkey
319 ,to_char(actid)
320 ,funcmode);
321 RAISE;
322
323 END branch_on_cost_center_mgr;
324
325 /*
326 ||===========================================================================
327 || PROCEDURE: create_transaction
328 || DESCRIPTION: Create transaction and transaction steps.
329 ||===========================================================================
330 */
331
332 PROCEDURE Create_transaction(
333 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
334 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
335 p_act_id IN NUMBER ,
336 p_transaction_id IN OUT NOCOPY NUMBER ,
337 p_transaction_step_id IN OUT NOCOPY NUMBER,
338 p_login_person_id IN NUMBER ,
339 p_review_proc_call IN VARCHAR2 ,
340 p_no_of_direct_reports IN NUMBER DEFAULT 0,
341 p_no_of_emps IN NUMBER DEFAULT 0 ,
342 p_selected_emp_name IN VARCHAR2 DEFAULT NULL,
343 p_single_supervisor_name IN VARCHAR2 DEFAULT NULL ,
344 p_single_effective_date IN DATE DEFAULT NULL,
345 p_term_flag IN VARCHAR2,
346 p_selected_emp_id IN NUMBER,
347 p_rptg_grp_id IN VARCHAR2 DEFAULT NULL,
348 p_plan_id IN VARCHAR2 DEFAULT NULL,
349 p_effective_date_option IN VARCHAR2 DEFAULT NULL ) IS
350 --p_selected_emp_id is the new attribute added on jan 8th raj
351
352 ln_transaction_id NUMBER ;
353 ln_transaction_step_id NUMBER ;
354 lv_result VARCHAR2(100) ;
355 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
356 lv_activity_name wf_item_activity_statuses_v.activity_name%TYPE;
357 ln_trans_step_rows number default 0;
358 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
359 ln_ovn hr_api_transaction_steps.object_version_number%TYPE;
360 ln_term_flag BOOLEAN DEFAULT FALSE;
361 l_proc constant varchar2(1000) := g_package || ' Create_transaction';
362 BEGIN
363 hr_utility.set_location('Entering'|| l_proc,5);
364 ln_transaction_id := hr_transaction_ss.get_transaction_id
365 (p_Item_Type => p_item_type
366 ,p_Item_Key => p_item_key);
367
368 IF ln_transaction_id IS NULL
369
370 THEN
371 hr_utility.trace('In ( IF ln_transaction_id IS NULL)'|| l_proc);
372 hr_transaction_ss.start_transaction
373 ( itemtype => p_item_type
374 ,itemkey => p_item_key
375 ,actid => p_act_id
376 ,funmode => 'RUN'
377 ,p_login_person_id => p_login_person_id
378 ,result => lv_result
379 ,p_plan_id => p_plan_id
380 ,p_rptg_grp_id => p_rptg_grp_id
381 ,p_effective_date_option => p_effective_date_option );
382
383 ln_transaction_id := hr_transaction_ss.get_transaction_id
384 (p_item_type => p_item_type
385 ,p_item_key => p_item_key);
386
387 END IF; -- now we have a valid txn id , let's find out txn steps
388
389 hr_transaction_api.get_transaction_step_info
390 (p_Item_Type => p_item_type
391 ,p_Item_Key => p_item_key
392 ,p_activity_id => to_number(p_act_id)
393 ,p_transaction_step_id => ltt_trans_step_ids
394 ,p_object_version_number => ltt_trans_obj_vers_num
395 ,p_rows => ln_trans_step_rows);
396
397 IF ln_trans_step_rows < 1 THEN
398 hr_utility.trace('In (if ln_trans_step_rows < 1)'|| l_proc);
399 --There is no transaction step for this transaction.
400 --Create a step within this new transaction
401
402 hr_transaction_api.create_transaction_step(
403 p_validate => false
404 ,p_creator_person_id => p_login_person_id
405 ,p_transaction_id => ln_transaction_id
406 ,p_api_name => 'HR_SUPERVISOR_SS.PROCESS_API'
407 ,p_Item_Type => p_item_type
408 ,p_Item_Key => p_item_key
409 ,p_activity_id => p_act_id
410 ,p_transaction_step_id => ln_transaction_step_id
411 ,p_object_version_number =>ln_ovn ) ;
412
413
414 ELSE
415 hr_utility.trace('In else of (If ln_trans_step_rows < 1)'|| l_proc);
416 --There are transaction steps for this transaction.
417 --Get the Transaction Step ID for this activity.
418 ln_transaction_step_id :=
419 hr_transaction_ss.get_activity_trans_step_id(
420 p_activity_name => gv_activity_name
421 ,p_trans_step_id_tbl => ltt_trans_step_ids);
422
423 END IF;
424
425 -- write activity name to txn table
426 hr_transaction_api.set_varchar2_value (
427 p_transaction_step_id =>ln_transaction_step_id,
428 p_person_id => p_login_person_id ,
429 p_name => 'p_activity_name' ,
430 p_value => gv_activity_name ) ;
431
432 hr_transaction_api.set_varchar2_value (
433 p_transaction_step_id =>ln_transaction_step_id,
434 p_person_id => p_login_person_id ,
435 p_name => 'P_REVIEW_PROC_CALL' ,
436 p_value => p_review_proc_call ) ;
437
438 hr_transaction_api.set_varchar2_value (
439 p_transaction_step_id =>ln_transaction_step_id,
440 p_person_id => p_login_person_id ,
441 p_name => 'P_REVIEW_ACTID' ,
442 p_value => p_act_id ) ;
443
444 hr_transaction_api.set_number_value (
445 p_transaction_step_id =>ln_transaction_step_id,
446 p_person_id => p_login_person_id ,
447 p_name => 'p_no_of_reports' ,
448 p_value => p_no_of_direct_reports ) ;
449
450 hr_transaction_api.set_varchar2_value (
451 p_transaction_step_id =>ln_transaction_step_id,
452 p_person_id => p_login_person_id ,
453 p_name => 'p_selected_emp_name' ,
454 p_value =>p_selected_emp_name ) ;
455
456 hr_transaction_api.set_varchar2_value (
457 p_transaction_step_id =>ln_transaction_step_id,
458 p_person_id => p_login_person_id ,
459 p_name => 'p_single_supervisor_name' ,
460 p_value =>p_single_supervisor_name ) ;
461
462 hr_transaction_api.set_date_value (
463 p_transaction_step_id =>ln_transaction_step_id,
464 p_person_id => p_login_person_id ,
465 p_name => 'p_single_effective_date' ,
466 p_value => p_single_effective_date ) ;
467
468 hr_transaction_api.set_number_value (
469 p_transaction_step_id =>ln_transaction_step_id,
470 p_person_id => p_login_person_id ,
471 p_name => 'p_no_of_emp' ,
472 p_value => p_no_of_emps ) ;
473
474 hr_transaction_api.set_varchar2_value (
475 p_transaction_step_id =>ln_transaction_step_id,
476 p_person_id => p_login_person_id ,
477 p_name => 'p_term_flag' ,
478 p_value => p_term_flag ) ;
479
480 --this is the new thing which is added to
481 hr_transaction_api.set_number_value (
482 p_transaction_step_id =>ln_transaction_step_id,
483 p_person_id => p_login_person_id ,
484 p_name => 'p_selected_emp_id' ,
485 p_value => p_selected_emp_id) ;
486
487
488
489 p_transaction_id := ln_transaction_id ;
490 p_transaction_step_id := ln_transaction_step_id ;
491 hr_utility.set_location('Leaving'|| l_proc,25);
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 hr_utility.set_location('EXCEPTION'|| l_proc,555);
496 raise ;
497
498 END create_transaction ;
499
500 /*
501 ||===========================================================================
502 || PROCEDURE: update_supervisor
503 || DESCRIPTION: This changes the per_all_assignments_f.supervisor_id attribute
504 || to the value of the passed in parm p_supervisor_id.
505 ||===========================================================================
506 */
507
508 PROCEDURE update_supervisor (
509 p_effective_date DATE ,
510 p_attribute_update_mode VARCHAR2,
511 p_assignment_id NUMBER,
512 p_supervisor_id NUMBER,
513 --Assignment Security
514 p_supervisor_assignment_id NUMBER,
515
516 p_validate BOOLEAN )
517 IS
518
519 -- Bug 2130066 Fix Begins: 01/11/2002
520 -- Changed from per_assignments_f to per_all_assignments_f. This is
521 -- necessary because when a Supervisor Security profile restrict to 1 level,
522 -- we cannot get data for 2 levels down or beyond. This will happen when
523 -- the 1st level employee is terminated, we need to change all his direct
524 -- reports to another employee. In this case, the direct reports of the
525 -- terminating employee will not be returned because the Supervisor Security
526 -- profile is restricted to 1 level.
527 CURSOR lc_object_version_no IS
528 SELECT object_version_number,
529 assignment_type
530 FROM per_all_assignments_f -- Bug 2130066 fix
531 WHERE assignment_id = p_assignment_id
532 AND (p_effective_date BETWEEN
533 NVL ( effective_start_date , p_effective_date)
534 AND NVL ( effective_end_date , p_effective_date )) ;
535
536 ln_object_version_no NUMBER ;
537 ln_assignment_type per_all_assignments_f.assignment_type%type;
538 ln_comment_id NUMBER ;
539 ld_effective_start_date DATE;
540 ld_effective_end_date DATE;
541 lb_no_managers_warning BOOLEAN ;
542 lb_other_manager_warning BOOLEAN ;
543 lv_message_number VARCHAR2(80);
544
545 -- Assignment Security
546 l_supervisor_assignment_id number ;
547 l_assignment_security_profile varchar(30) := hr_general2.supervisor_assignments_in_use ;
548 l_proc constant varchar2(100) := g_package || ' update_supervisor';
549 BEGIN
550 hr_utility.set_location('Entering'|| l_proc,5);
551 OPEN lc_object_version_no ;
552 FETCH lc_object_version_no into ln_object_version_no, ln_assignment_type ;
553 CLOSE lc_object_version_no ;
554
555 /*
556 hr_java_script_web.alert ( 'asg id ' || p_assignment_id ) ;
557 hr_java_script_web.alert ( 'ovn is ' || ln_object_version_no ) ;
558 */
559
560 -- Assignment Security
561 IF(l_assignment_security_profile <> 'TRUE') then
562 l_supervisor_assignment_id := hr_api.g_number;
563 ELSE
564 l_supervisor_assignment_id
565 := p_supervisor_assignment_id;
566 END IF;
567 -- End of Assignment Security code
568
569
570
571 -- call api here
572 hr_assignment_att.update_asg(
573 p_validate=>p_validate,
574 p_effective_date=>p_effective_date ,
575 p_attribute_update_mode=>p_attribute_update_mode ,
576 p_assignment_id=>p_assignment_id ,
577 p_assignment_type => ln_assignment_type,
578 p_object_version_number =>ln_object_version_no ,
579 p_supervisor_id => p_supervisor_id ,
580 -- Assignment Security
581 p_supervisor_assignment_id => l_supervisor_assignment_id ,
582
583 p_comment_id => ln_comment_id ,
584 p_effective_start_date=>ld_effective_start_date,
585 p_effective_end_date=>ld_effective_end_date,
586 p_no_managers_warning=>lb_no_managers_warning,
587 p_other_manager_warning=>lb_other_manager_warning ) ;
588 hr_utility.set_location('Leaving'|| l_proc,10);
589
590 EXCEPTION
591 /*WHEN hr_utility.hr_error THEN
592 hr_message.provide_error;
593 lv_message_number := hr_message.last_message_number;
594 hr_errors_api.addErrorToTable(
595 p_errormsg => hr_message.get_message_text,
596 p_errorcode => lv_message_number
597 );*/
598
599 WHEN OTHERS THEN
600 hr_utility.set_location('EXCEPTION'|| l_proc,555);
601 raise ;
602
603 END update_supervisor ;
604
605
606 /*
607 ||===========================================================================
608 || PROCEDURE: validate_api
609 || DESCRIPTION:
610 ||
611 ||===========================================================================
612 */
613
614 PROCEDURE validate_api (
615 p_selected_emp_id NUMBER ,
616 p_selected_person_sup_id NUMBER ,
617 p_selected_person_old_sup_id NUMBER ,
618
619 -- Assignment Security
620 p_selected_person_sup_asg_id NUMBER ,
621 p_sel_person_old_sup_asg_id NUMBER ,
622
623 p_passed_effective_date DATE ,
624 p_passed_assignment_id NUMBER ,
625 p_direct_reports ltt_direct_reports ,
626 p_validate BOOLEAN ,
627 p_from_term BOOLEAN DEFAULT FALSE ,
628 p_emp_asg_id hr_util_misc_web.g_varchar2_tab_type,
629 p_emp_effective_date hr_util_misc_web.g_varchar2_tab_type,
630 -- Assignment Security
631 p_emp_sup_asg_id hr_util_misc_web.g_varchar2_tab_type,
632
633 p_term_flag VARCHAR2)
634
635 IS
636 lv_message_number VARCHAR2(80);
637 ln_count number ;
638 l_proc constant varchar2(100) := g_package || 'validate_api';
639
640
641 BEGIN
642 hr_utility.set_location('Entering'|| l_proc,5);
643 SAVEPOINT update_supervisor ;
644 -- if not invoked from termination , update the
645 -- selected employee's supervisor
646 -- we need to check here if the supervisor is changed and
647 -- then only do the update
648
649
650
651
652
653
654 -- remove the comments and the update_supervisor
655 -- RAJ just to test IF p_from_term = FALSE TO p_term_flag="N"
656 IF p_term_flag = 'N'
657 THEN
658 hr_utility.trace('In (if p_term_flag = N)'|| l_proc);
659 IF (
660 ( p_selected_person_sup_id IS NOT NULL AND
661 p_selected_person_old_sup_id IS NULL ) OR
662 ( p_selected_person_sup_id IS NULL AND
663 p_selected_person_old_sup_id IS NOT NULL) OR
664 (p_selected_person_sup_id <> p_selected_person_old_sup_id) OR
665 ( hr_general2.supervisor_assignments_in_use = 'TRUE'
666 AND (p_selected_person_sup_asg_id is not NULL and
667 p_sel_person_old_sup_asg_id is NULL)
668 ) OR
669 ( hr_general2.supervisor_assignments_in_use = 'TRUE'
670 AND (p_selected_person_sup_asg_id is NULL and
671 p_sel_person_old_sup_asg_id is not NULL)
672 ) OR
673 ( hr_general2.supervisor_assignments_in_use = 'TRUE'
674 AND p_selected_person_sup_asg_id <> p_sel_person_old_sup_asg_id)
675 )
676 THEN
677
678
679 update_supervisor(
680 p_passed_effective_date ,
681 'ATTRIBUTE_UPDATE' ,
682 p_passed_assignment_id ,
683 p_selected_person_sup_id,
684 -- Assignment Security
685 p_selected_person_sup_asg_id,
686
687 p_validate) ;
688 END IF ;
689
690
691 END IF ;
692
693
694
695
696 -- if direct reports exists , update their supervisor
697 -- update direct reports' supervisor
698
699 ln_count := p_direct_reports.count ;
700
701
702
703
704 FOR i in 1..p_direct_reports.count
705 LOOP
706 -- check if supervisor is changed for this direct report
707 -- if so update the supervisor
708
709 IF ((p_direct_reports(i).supervisor_id <> p_selected_emp_id) or
710 (p_direct_reports(i).supervisor_id is Null) or
711 (hr_general2.supervisor_assignments_in_use = 'TRUE' AND
712 p_direct_reports(i).supervisor_assignment_id <>
713 p_passed_assignment_id)
714 or (hr_general2.supervisor_assignments_in_use = 'TRUE' AND
715 p_direct_reports(i).supervisor_assignment_id is NULL)
716 )
717 THEN
718 update_supervisor(
719 p_direct_reports(i).effective_date ,
720 'ATTRIBUTE_UPDATE' ,
721 p_direct_reports(i).assignment_id ,
722 p_direct_reports(i).supervisor_id ,
723 -- Assignment Security
724 p_direct_reports(i).supervisor_assignment_id ,
725
726 p_validate);
727
728 END IF ;
729
730
731 END LOOP ;
732 --comented out this part fort testing the Direct Reports Raj
733
734 -- add new direct reports
735 FOR i in 1..p_emp_asg_id.count
736 LOOP
737 update_supervisor(
738 p_emp_effective_date(i) ,
739 'ATTRIBUTE_UPDATE' ,
740 p_emp_asg_id(i) ,
741 p_selected_emp_id,
742
743 p_emp_sup_asg_id(i) ,
744
745 p_validate);
746
747 END LOOP ;
748 IF p_validate
749 THEN
750 ROLLBACK to update_supervisor ;
751 END IF ;
752 hr_utility.set_location('Leaving'|| l_proc,15);
753 EXCEPTION
754 -- add api error
755 /*WHEN hr_utility.hr_error THEN
756 hr_message.provide_error;
757 lv_message_number := hr_message.last_message_number;
758 hr_errors_api.addErrorToTable(
759 p_errormsg => hr_message.get_message_text,
760 p_errorcode => lv_message_number
761 );*/
762
763 WHEN OTHERS THEN
764 hr_utility.set_location('EXCEPTION'|| l_proc,555);
765 raise ;
766
767 END validate_api;
768
769 /*
770 ||===========================================================================
771 || PROCEDURE: validate_emp_assignments
772 || DESCRIPTION:
773 ||
774 ||===========================================================================
775 */
776
777 PROCEDURE validate_emp_assignments (
778 p_emp_id hr_util_misc_web.g_varchar2_tab_type DEFAULT
779 hr_util_misc_web.g_varchar2_tab_default ,
780 p_emp_asg_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type ,
781 p_emp_effective_date hr_util_misc_web.g_varchar2_tab_type DEFAULT
782 hr_util_misc_web.g_varchar2_tab_default,
783 p_error_flag IN OUT NOCOPY BOOLEAN )
784
785 IS
786
787 -- Bug 2130066 Fix Begins: 01/11/2002
788 -- Changed from per_assignments_f to per_all_assignments_f. This is
789 -- necessary because when a Supervisor Security profile restrict to 1 level
790 -- ,we cannot get data for 2 levels down or beyond. This will happen when
791 -- the 1st level employee is terminated, we need to change all his direct
792 -- reports to another employee. In this case, the direct reports of the
793 -- terminating employee will not be returned because the Supervisor
794 -- Security profile is restricted to 1 level.
795 --
796 cursor lc_assignment_id ( p_person_id NUMBER, p_effective_date date) is
797 SELECT distinct paf.assignment_id
798 FROM per_all_assignments_f paf, -- Bug 2130066 fix
799 per_all_people_f ppf
800 WHERE ppf.person_id = p_person_id
801 AND p_effective_date BETWEEN
802 ppf.effective_start_date AND ppf.effective_end_date
803 AND paf.person_id = ppf.person_id
804 AND paf.primary_flag = 'Y'
805 AND p_effective_date BETWEEN
806 paf.effective_start_date AND paf.effective_end_date
807 AND ((paf.assignment_type = 'E' and ppf.current_employee_flag = 'Y')
808 OR (paf.assignment_type = 'C' and ppf.current_npw_flag = 'Y'));
809
810 ln_assignment_id NUMBER ;
811 l_proc constant varchar2(100) := g_package || 'validate_emp_assignments';
812
813 BEGIN
814 hr_utility.set_location('Entering'|| l_proc,5);
815 p_error_flag := FALSE ;
816
817 FOR i in 1..p_emp_id.count
818 LOOP
819
820
821
822 open lc_assignment_id ( p_emp_id(i), p_emp_effective_date(i));
823 FETCH lc_assignment_id INTO ln_assignment_id ;
824
825 IF lc_assignment_id%NOTFOUND THEN
826
827 p_error_flag := TRUE ;
828 END IF ;
829
830 p_emp_asg_id(i):= ln_assignment_id ;
831
832
833
834 close lc_assignment_id ;
835
836 END LOOP ;
837 hr_utility.set_location('Leaving'|| l_proc,10);
838
839 EXCEPTION
840 WHEN OTHERS THEN
841
842 hr_utility.set_location('EXCEPTION'|| l_proc,555);
843 raise ;
844
845
846 END ;
847
848
849 /*
850 ||===========================================================================
851 || PROCEDURE: get_txn_details
852 || DESCRIPTION:
853 ||
854 ||===========================================================================
855 */
856
857 PROCEDURE get_txn_details (
858 p_item_type IN wf_items.item_type%type ,
859 p_item_key IN wf_items.item_key%TYPE ,
860 p_act_id IN NUMBER,
861 p_selected_emp_id IN OUT NOCOPY NUMBER ,
862 p_passed_assignment_id IN OUT NOCOPY NUMBER ,
863 p_sup_id OUT NOCOPY NUMBER,
864 p_old_sup_id OUT NOCOPY NUMBER ,
865 -- Assignment Security
866 p_sup_asg_id OUT NOCOPY NUMBER,
867 p_old_sup_asg_id OUT NOCOPY NUMBER ,
868
869 p_sup_name OUT NOCOPY VARCHAR2 ,
870 p_old_sup_name OUT NOCOPY VARCHAR2,
871 p_passed_effective_date OUT NOCOPY DATE ,
872 p_direct_reports IN OUT NOCOPY ltt_direct_reports,
873 p_emp_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
874 p_emp_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
875 p_emp_asg_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
876 p_emp_date IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type ,
877 p_emp_sup_asg_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
878 p_single_supervisor_name IN OUT NOCOPY VARCHAR2,
879 p_single_effective_date IN OUT NOCOPY DATE,
880 p_term_flag IN OUT NOCOPY VARCHAR2)
881
882 IS
883
884 ln_transaction_step_id NUMBER;
885 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
886 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
887 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
888 ln_trans_step_rows NUMBER ;
889 lv_activity_name wf_item_activity_statuses_v.activity_name%type ;
890 ln_no_of_reports NUMBER ;
891 ln_selected_person_id NUMBER ;
892 ln_selected_emp_id NUMBER ;
893 ln_passed_assignment_id NUMBER ;
894 lv_selected_emp_name VARCHAR2(250);
895 ln_no_of_emp NUMBER ;
896 lv_selected_emp BOOLEAN;
897 lv_direct_reports BOOLEAN;
898 lv_new_reports BOOLEAN;
899 l_proc constant varchar2(100) := g_package || 'get_txn_details';
900 -- ld_passed_effective_date DATE ;
901
902 BEGIN
903 hr_utility.set_location('Entering'|| l_proc,5);
904 ln_transaction_id := hr_transaction_ss.get_transaction_id
905 (p_Item_Type => p_item_type,
906 p_Item_Key => p_item_key);
907
908
909 IF ln_transaction_id IS NOT NULL
910 THEN
911 hr_utility.trace('In if ln_transaction_id IS NOT NULL '|| l_proc);
912 hr_transaction_api.get_transaction_step_info
913 (p_Item_Type => p_item_type,
914 p_Item_Key => p_item_key,
915 p_activity_id =>p_act_id,
916 p_transaction_step_id => ltt_trans_step_ids,
917 p_object_version_number => ltt_trans_obj_vers_num,
918 p_rows => ln_trans_step_rows);
919
920
921 -- if no transaction steps are found , return
922 IF ln_trans_step_rows < 1
923 THEN
924 hr_utility.set_location('Leaving'|| l_proc,15);
925 RETURN ;
926
927 ELSE
928
929 ln_transaction_step_id :=
930 hr_transaction_ss.get_activity_trans_step_id
931 (p_activity_name => gv_activity_name
932 ,p_trans_step_id_tbl => ltt_trans_step_ids);
933
934 -- now get the which region is changed
935 -- catch the exception as one or more regions may be changed
936 BEGIN
937 lv_selected_emp :=
938 hr_transaction_api.get_boolean_value
939 (p_transaction_step_id => ln_transaction_step_id,
940 p_name =>'p_selected_emp');
941 EXCEPTION
942 WHEN OTHERS THEN
943 hr_utility.set_location('EXCEPTION'|| l_proc,555);
944 lv_selected_emp := false;
945 END;
946
947 BEGIN
948 lv_direct_reports :=
949 hr_transaction_api.get_boolean_value
950 (p_transaction_step_id => ln_transaction_step_id,
951 p_name =>'p_direct_reports');
952 EXCEPTION
953 WHEN OTHERS THEN
954 hr_utility.set_location('EXCEPTION'|| l_proc,560);
955 lv_direct_reports := false;
956 END;
957
958 BEGIN
959 lv_new_reports :=
960 hr_transaction_api.get_boolean_value
961 (p_transaction_step_id => ln_transaction_step_id,
962 p_name =>'p_new_reports');
963 EXCEPTION
964 WHEN OTHERS THEN
965 hr_utility.set_location('EXCEPTION'|| l_proc,565);
966 lv_new_reports := false;
967 END;
968
969 -- now get the individual txn data
970
971 p_term_flag :=
972 hr_transaction_api.get_varchar2_value
973 (p_transaction_step_id => ln_transaction_step_id,
974 p_name =>'p_term_flag');
975
976
977 p_selected_emp_id :=
978 hr_transaction_api.get_number_value
979 (p_transaction_step_id => ln_transaction_step_id,
980 p_name =>'p_selected_emp_id');
981 ln_no_of_emp :=
982 hr_transaction_api.get_number_value
983 (p_transaction_step_id => ln_transaction_step_id,
984 p_name =>'p_no_of_emp');
985
986 p_single_supervisor_name :=
987 hr_transaction_api.get_VARCHAR2_value
988 (p_transaction_step_id => ln_transaction_step_id,
989 p_name =>'p_single_supervisor_name');
990
991 p_single_effective_date :=
992 hr_transaction_api.get_date_value
993 (p_transaction_step_id => ln_transaction_step_id,
994 p_name =>'p_single_effective_date');
995
996 if p_term_flag='N' Then
997 hr_utility.trace('In (if p_term_flag=N) '|| l_proc);
998 if lv_selected_emp then
999 hr_utility.trace('In (if lv_selected_emp) '|| l_proc);
1000 p_passed_assignment_id :=
1001 hr_transaction_api.get_number_value
1002 (p_transaction_step_id => ln_transaction_step_id,
1003 p_name =>'p_passed_assignment_id');
1004
1005 p_sup_id :=
1006 hr_transaction_api.get_number_value
1007 (p_transaction_step_id => ln_transaction_step_id,
1008 p_name =>'p_selected_person_sup_id');
1009
1010
1011 p_old_sup_id :=
1012 hr_transaction_api.get_number_value
1013 (p_transaction_step_id => ln_transaction_step_id,
1014 p_name =>'p_selected_person_old_sup_id');
1015
1016 -- Assignment Security
1017 p_sup_asg_id :=
1018 hr_transaction_api.get_number_value
1019 (p_transaction_step_id => ln_transaction_step_id,
1020 p_name =>'p_selected_person_sup_asg_id');
1021
1022
1023 p_old_sup_asg_id :=
1024 hr_transaction_api.get_number_value
1025 (p_transaction_step_id => ln_transaction_step_id,
1026 p_name =>'p_sel_person_old_sup_asg_id');
1027
1028
1029 lv_selected_emp_name :=
1030 hr_transaction_api.get_varchar2_value
1031 (p_transaction_step_id => ln_transaction_step_id,
1032 p_name =>'p_selected_emp_name');
1033
1034 p_sup_name :=
1035 hr_transaction_api.get_varchar2_value
1036 (p_transaction_step_id => ln_transaction_step_id,
1037 p_name =>'p_selected_person_sup_name');
1038
1039
1040 p_old_sup_name :=
1041 hr_transaction_api.get_varchar2_value
1042 (p_transaction_step_id => ln_transaction_step_id,
1043 p_name =>'p_selected_person_old_sup_name');
1044
1045 p_passed_effective_date :=
1046 hr_transaction_api.get_date_value
1047 (p_transaction_step_id => ln_transaction_step_id,
1048 p_name =>'p_passed_effective_date');
1049 end if; -- end selected emp
1050
1051 IF lv_new_reports THEN
1052 hr_utility.trace('In (if lv_new_reports) '|| l_proc);
1053
1054 FOR i in 1 ..ln_no_of_emp
1055 LOOP
1056
1057 p_emp_name(i) :=
1058 hr_transaction_api.get_varchar2_value
1059 (p_transaction_step_id => ln_transaction_step_id,
1060 p_name =>'p_emp_name'||i);
1061
1062 p_emp_id(i) :=
1063 hr_transaction_api.get_number_value
1064 (p_transaction_step_id => ln_transaction_step_id,
1065 p_name =>'p_emp_id'||i);
1066
1067 p_emp_asg_id(i) :=
1068 hr_transaction_api.get_number_value
1069 (p_transaction_step_id => ln_transaction_step_id,
1070 p_name =>'p_emp_asg_id'||i);
1071
1072 p_emp_date(i):=
1073 hr_transaction_api.get_date_value
1074 (p_transaction_step_id => ln_transaction_step_id,
1075 p_name =>'p_emp_date'||i);
1076
1077 p_emp_sup_asg_id(i):=
1078 hr_transaction_api.get_number_value
1079 (p_transaction_step_id => ln_transaction_step_id,
1080 p_name =>'p_emp_sup_asg_id'||i);
1081
1082
1083 END LOOP ;
1084 END IF; -- end new emp
1085 end If; -- end termination flag
1086
1087 ln_no_of_reports :=
1088 hr_transaction_api.get_number_value
1089 (p_transaction_step_id => ln_transaction_step_id,
1090 p_name =>'p_no_of_reports');
1091
1092 IF lv_direct_reports THEN
1093 hr_utility.trace('In (if lv_direct_reports) '|| l_proc);
1094 -- now get all the direct reports info
1095 FOR i in 1..ln_no_of_reports
1096 LOOP
1097
1098 p_direct_reports(i).full_name :=
1099 hr_transaction_api.get_varchar2_value
1100 (p_transaction_step_id => ln_transaction_step_id,
1101 p_name =>'p_full_name'||i);
1102
1103 p_direct_reports(i).supervisor_id :=
1104 hr_transaction_api.get_number_value
1105 (p_transaction_step_id => ln_transaction_step_id,
1106 p_name =>'p_supervisor_id'||i);
1107
1108 -- Assignment Security
1109 p_direct_reports(i).supervisor_assignment_id :=
1110 hr_transaction_api.get_number_value
1111 (p_transaction_step_id => ln_transaction_step_id,
1112 p_name =>'p_supervisor_assignment_id'||i);
1113
1114
1115 p_direct_reports(i).supervisor_name :=
1116 hr_transaction_api.get_varchar2_value
1117 (p_transaction_step_id => ln_transaction_step_id,
1118 p_name =>'p_supervisor_name'||i);
1119
1120
1121
1122 p_direct_reports(i).effective_date:=
1123 hr_transaction_api.get_date_value
1124 (p_transaction_step_id => ln_transaction_step_id,
1125 p_name =>'p_effective_date'||i);
1126
1127 p_direct_reports(i).assignment_id:=
1128 hr_transaction_api.get_number_value
1129 (p_transaction_step_id => ln_transaction_step_id,
1130 p_name =>'p_assignment_id'||i);
1131
1132 END LOOP ;
1133 END IF;
1134 END IF; -- end Direct reports
1135 END IF ;
1136 hr_utility.set_location('Leaving'|| l_proc,40);
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 hr_utility.set_location('EXCEPTION'|| l_proc,570);
1140 raise ;
1141
1142 END ;
1143
1144
1145 /*
1146 ||===========================================================================
1147 || PROCEDURE: process_api
1148 || DESCRIPTION:
1149 ||
1150 ||===========================================================================
1151 */
1152
1153 PROCEDURE process_api (
1154 p_transaction_step_id IN
1155 hr_api_transaction_steps.transaction_step_id%type,
1156 p_validate BOOLEAN default FALSE,
1157 p_effective_date IN varchar2 default null) IS
1158
1159 lv_item_type VARCHAR2(100);
1160 lv_item_key VARCHAR2(100);
1161 ln_act_id NUMBER ;
1162 ln_sup_id NUMBER ;
1163 ln_old_sup_id NUMBER ;
1164 -- Assignment Security
1165 ln_sup_asg_id NUMBER ;
1166 ln_old_sup_asg_id NUMBER ;
1167
1168 lv_sup_name VARCHAR2(250);
1169 lv_old_sup_name VARCHAR2(250);
1170 ld_passed_effective_date DATE ;
1171 ltt_reports ltt_direct_reports;
1172 ltt_emp_name hr_util_misc_web.g_varchar2_tab_type ;
1173 ltt_emp_id hr_util_misc_web.g_varchar2_tab_type ;
1174 ltt_emp_asg_id hr_util_misc_web.g_varchar2_tab_type ;
1175 ltt_emp_date hr_util_misc_web.g_varchar2_tab_type ;
1176 -- Assignment Security
1177 ltt_emp_sup_asg_id hr_util_misc_web.g_varchar2_tab_type ;
1178
1179 ltt_reports_supervisor_id hr_util_misc_web.g_varchar2_tab_type;
1180 ltt_reports_supervisor_name hr_util_misc_web.g_varchar2_tab_type ;
1181 ltt_reports_effective_date hr_util_misc_web.g_varchar2_tab_type ;
1182 lv_result_code VARCHAR2(250);
1183 lv_single_supervisor_name VARCHAR2(250);
1184 ld_single_effective_date DATE ;
1185 ln_selected_emp_id NUMBER ;
1186 ln_passed_assignment_id NUMBER ;
1187 lb_emp_asg_flag BOOLEAN ;
1188 ld_term_flag varchar2(1) ;
1189
1190 -- For SAVE_FOR_LATER
1191 ld_effective_date date default null;
1192 l_proc constant varchar2(100) := g_package || 'process_api';
1193 BEGIN
1194
1195 hr_utility.set_location('Entering'|| l_proc,5);
1196 hr_transaction_api.get_transaction_step_info(
1197 p_transaction_step_id => p_transaction_step_id
1198 ,p_item_type => lv_item_type
1199 ,p_item_key => lv_item_key
1200 ,p_activity_id => ln_act_id);
1201
1202
1203 -- get supervisor data from txn tables
1204 get_txn_details (
1205 p_item_type=>lv_item_type,
1206 p_item_key=>lv_item_key,
1207 p_act_id=>ln_act_id,
1208 p_selected_emp_id=>ln_selected_emp_id ,
1209 p_passed_assignment_id=>ln_passed_assignment_id,
1210 p_sup_id=>ln_sup_id ,
1211 p_old_sup_id=>ln_old_sup_id ,
1212
1213 -- Assignment Security
1214 p_sup_asg_id=>ln_sup_asg_id ,
1215 p_old_sup_asg_id=>ln_old_sup_asg_id ,
1216
1217
1218 p_sup_name=>lv_sup_name ,
1219 p_old_sup_name=>lv_old_sup_name ,
1220 p_passed_effective_date=>ld_passed_effective_date,
1221 p_direct_reports=>ltt_reports,
1222 p_emp_name=>ltt_emp_name ,
1223 p_emp_id=>ltt_emp_id ,
1224 p_emp_asg_id=>ltt_emp_asg_id ,
1225 p_emp_date=>ltt_emp_date ,
1226 -- Assignment Security
1227 p_emp_sup_asg_id =>ltt_emp_sup_asg_id ,
1228 p_single_supervisor_name=>lv_single_supervisor_name,
1229 p_single_effective_date=>ld_single_effective_date,
1230 p_term_flag=>ld_term_flag) ;
1231
1232 -- SAVE_FOR_LATER Changes Begin:
1233 -- The following is for SAVE_FOR_LATER code change.
1234 -- 1)When the Action page re-launch a suspended workflow process, it does
1235 -- a validation by calling the process_api with the new user entered
1236 -- effective date. Added a new parameter p_effective_date to this proc.
1237 -- If p_effective_date is not null, then use it as the effective date for
1238 -- api validation.
1239 -- 2)When the Action page re-launch a suspended workflow process, it
1240 -- allows user to enter a new effective date. We should not use the
1241 -- effective date that we saved in the transaction table.
1242 -- In process_api, if the p_effective_date parameter is null then use
1243 -- the workflow attribute P_EFFECTIVE_DATE as the effective date for api
1244 -- validation.
1245 --
1246 IF (p_effective_date is not null) THEN
1247 ld_effective_date:= to_date(p_effective_date, gv_date_format);
1248 ELSE
1249 ld_effective_date:= to_date(
1250 hr_transaction_ss.get_wf_effective_date
1251 (p_transaction_step_id => p_transaction_step_id), gv_date_format);
1252 END IF;
1253
1254 -- SAVE_FOR_LATER Changes End
1255
1256 -- start newhire
1257 -- If its a new hire flow than the assignmentId which is coming from transaction table
1258 -- will not be valid because the person has just been created by the process_api of the
1259 -- hr_process_person_ss.process_api we can get that person Id and assignment id by making a call
1260 -- to the global parameters but we need to branch out the code.
1261 -- We also need the latest Object version Number not the one on transaction tables
1262
1263 -- adding the session id check to avoid connection pooling problems.
1264 -- if (hr_process_person_ss.g_assignment_id is not null) then
1265 if (( hr_process_person_ss.g_assignment_id is not null) and
1266 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
1267 ln_selected_emp_id := hr_process_person_ss.g_person_id;
1268 ln_passed_assignment_id := hr_process_person_ss.g_assignment_id;
1269 end if;
1270
1271 -- end newhire
1272
1273 /*
1274 if ld_term_flag='N' then
1275
1276 validate_emp_assignments (
1277 p_emp_id=>ltt_emp_id ,
1278 p_emp_asg_id =>ltt_emp_asg_id,
1279 p_emp_effective_date => ltt_emp_date,
1280 p_error_flag =>lb_emp_asg_flag ) ;
1281
1282 end if;
1283 */
1284
1285 -- call api here
1286 validate_api(
1287 p_selected_emp_id=>ln_selected_emp_id ,
1288 p_selected_person_sup_id=>ln_sup_id,
1289 p_selected_person_old_sup_id=>ln_old_sup_id ,
1290 -- Assignment Security
1291 p_selected_person_sup_asg_id=>ln_sup_asg_id,
1292 p_sel_person_old_sup_asg_id=>ln_old_sup_asg_id ,
1293
1294
1295 p_passed_effective_date=>ld_passed_effective_date ,
1296 p_passed_assignment_id=>ln_passed_assignment_id,
1297 p_direct_reports=>ltt_reports,
1298 p_validate=>p_validate ,
1299 p_emp_asg_id=>ltt_emp_asg_id ,
1300 p_emp_effective_date=>ltt_emp_date,
1301 -- Assignment Security
1302 p_emp_sup_asg_id=>ltt_emp_sup_asg_id ,
1303
1304 p_term_flag=>ld_term_flag ) ;
1305
1306
1307 -- end if;
1308
1309 hr_utility.set_location('Leaving'|| l_proc,10);
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 hr_utility.set_location('EXCEPTION'|| l_proc,555);
1314 raise ;
1315
1316 END PROCESS_API;
1317
1318 /*
1319 ||===========================================================================
1320 || PROCEDURE: write_transaction
1321 || DESCRIPTION:
1322 ||
1323 ||===========================================================================
1324 */
1325
1326 PROCEDURE write_transaction (
1327 p_old_sup_id NUMBER default NULL,
1328 p_old_sup_asg_id NUMBER default NULL,
1329 p_new_sup_id NUMBER default NULL,
1330 p_new_sup_asg_id NUMBER default NULL,
1331 p_old_sup_name per_people_f.full_name%type default NULL,
1332 p_new_sup_name per_people_f.full_name%type,
1333 p_emp_name per_people_f.full_name%type,
1334 p_emp_id per_people_f.person_id%type default NULL,
1335 p_effective_date Date ,
1336 p_assignment_id NUMBER ,
1337 p_section_code IN VARCHAR2,
1338 p_row_num NUMBER DEFAULT 0,
1339 p_transaction_step_id NUMBER,
1340 p_login_person_id IN NUMBER) IS
1341 l_proc constant varchar2(100) := g_package || ' write_transaction';
1342
1343 BEGIN
1344
1345 hr_utility.set_location('Entering'|| l_proc,5);
1346
1347 -- write data for selected employee
1348 IF p_section_code = 'NEW_MANAGER'
1349 THEN
1350
1351 hr_utility.trace('In (IF p_section_code = NEW_MANAGER)'|| l_proc);
1352
1353 -- Store the p_selected_emp to be used in process_Api
1354 hr_transaction_api.set_boolean_value (
1355 p_transaction_step_id =>p_transaction_step_id,
1356 p_person_id => p_login_person_id ,
1357 p_name => 'p_selected_emp' ,
1358 p_value => TRUE ) ;
1359
1360 -- we are in the top region for the supervisor page
1361 -- write selected person's employee id
1362
1363
1364 hr_transaction_api.set_number_value (
1365 p_transaction_step_id =>p_transaction_step_id,
1366 p_person_id => p_login_person_id ,
1367 p_name => 'p_selected_emp_id' ,
1368 p_value =>p_emp_id ) ;
1369
1370
1371 hr_transaction_api.set_varchar2_value (
1372 p_transaction_step_id =>p_transaction_step_id,
1373 p_person_id => p_login_person_id ,
1374 p_name => 'p_selected_emp_name' ,
1375 p_value =>p_emp_name ) ;
1376
1377
1378 hr_transaction_api.set_varchar2_value (
1379 p_transaction_step_id =>p_transaction_step_id,
1380 p_person_id => p_login_person_id ,
1381 p_name => 'p_selected_person_sup_name' ,
1382 p_value =>p_new_sup_name ) ;
1383
1384 hr_transaction_api.set_varchar2_value (
1385 p_transaction_step_id =>p_transaction_step_id,
1386 p_person_id => p_login_person_id ,
1387 p_name => 'p_selected_person_old_sup_name' ,
1388 p_value =>p_old_sup_name ) ;
1389
1390
1391 hr_transaction_api.set_number_value (
1392 p_transaction_step_id =>p_transaction_step_id,
1393 p_person_id => p_login_person_id ,
1394 p_name => 'p_selected_person_old_sup_id' ,
1395 p_value =>p_old_sup_id ) ;
1396
1397
1398 -- Assignment Security
1399 hr_transaction_api.set_number_value (
1400 p_transaction_step_id =>p_transaction_step_id,
1401 p_person_id => p_login_person_id ,
1402 p_name => 'p_sel_person_old_sup_asg_id' ,
1403 p_value => p_old_sup_asg_id ) ;
1404
1405
1406 hr_transaction_api.set_number_value (
1407 p_transaction_step_id =>p_transaction_step_id,
1408 p_person_id => p_login_person_id ,
1409 p_name => 'p_selected_person_sup_id' ,
1410 p_value =>p_new_sup_id ) ;
1411
1412
1413
1414 -- Assignment Security
1415 hr_transaction_api.set_number_value (
1416 p_transaction_step_id =>p_transaction_step_id,
1417 p_person_id => p_login_person_id ,
1418 p_name => 'p_selected_person_sup_asg_id' ,
1419 p_value =>p_new_sup_asg_id ) ;
1420
1421
1422 hr_transaction_api.set_date_value (
1423 p_transaction_step_id =>p_transaction_step_id,
1424 p_person_id => p_login_person_id ,
1425 p_name => 'p_passed_effective_date' ,
1426 p_value =>p_effective_date) ;
1427
1428 hr_transaction_api.set_number_value (
1429 p_transaction_step_id =>p_transaction_step_id,
1430 p_person_id => p_login_person_id ,
1431 p_name => 'p_passed_assignment_id' ,
1432 p_value =>p_assignment_id ) ;
1433
1434
1435 END IF ;
1436
1437
1438 -- write transaction data for direct reports section
1439 IF p_section_code = 'DIR_REPORTS'
1440 THEN
1441 hr_utility.trace('In (IF p_section_code = DIR_REPORTS)'|| l_proc);
1442 -- Store the p_selected_emp to be used in process_Api
1443 hr_transaction_api.set_boolean_value (
1444 p_transaction_step_id =>p_transaction_step_id,
1445 p_person_id => p_login_person_id ,
1446 p_name => 'p_direct_reports' ,
1447 p_value => TRUE ) ;
1448
1449 -- write the name of the direct report
1450
1451 hr_transaction_api.set_varchar2_value (
1452 p_transaction_step_id =>p_transaction_step_id,
1453 p_person_id => p_login_person_id ,
1454 p_name => 'p_full_name'||p_row_num ,
1455 p_value =>p_emp_name ) ;
1456
1457
1458 -- Write the person id of the direct reports
1459 -- This is done as a part of Global Name format changes
1460 -- Bug 5130368 also raises this issue.
1461 hr_transaction_api.set_number_value (
1462 p_transaction_step_id =>p_transaction_step_id,
1463 p_person_id => p_login_person_id ,
1464 p_name => 'p_dir_rep_emp_id'||p_row_num ,
1465 p_value =>p_emp_id ) ;
1466
1467
1468 -- write the assignment id of the direct report
1469 hr_transaction_api.set_number_value (
1470 p_transaction_step_id =>p_transaction_step_id,
1471 p_person_id => p_login_person_id ,
1472 p_name => 'p_assignment_id'||p_row_num ,
1473 p_value =>p_assignment_id ) ;
1474
1475 -- write the new supervisor id and new supervisor name
1476 -- we do not write the old supervisor details as that has
1477 -- been written already as selected person's detail
1478
1479 hr_transaction_api.set_number_value (
1480 p_transaction_step_id =>p_transaction_step_id,
1481 p_person_id => p_login_person_id ,
1482 p_name => 'p_supervisor_id'||p_row_num ,
1483 p_value =>p_new_sup_id ) ;
1484
1485 -- Assignment Security
1486 hr_transaction_api.set_number_value (
1487 p_transaction_step_id =>p_transaction_step_id,
1488 p_person_id => p_login_person_id ,
1489 p_name => 'p_supervisor_assignment_id'||p_row_num ,
1490 p_value =>p_new_sup_asg_id ) ;
1491
1492
1493 hr_transaction_api.set_number_value (
1494 p_transaction_step_id =>p_transaction_step_id,
1495 p_person_id => p_login_person_id ,
1496 p_name => 'p_old_supervisor_asg_id'||p_row_num ,
1497 p_value =>p_old_sup_asg_id ) ;
1498
1499
1500 hr_transaction_api.set_varchar2_value (
1501 p_transaction_step_id =>p_transaction_step_id,
1502 p_person_id => p_login_person_id ,
1503 p_name => 'p_supervisor_name'||p_row_num ,
1504 p_value =>p_new_sup_name ) ;
1505
1506 hr_transaction_api.set_date_value (
1507 p_transaction_step_id =>p_transaction_step_id,
1508 p_person_id => p_login_person_id ,
1509 p_name => 'p_effective_date'||p_row_num ,
1510 p_value =>p_effective_date ) ;
1511
1512
1513 END IF ;
1514
1515
1516 -- write data for new direct reports
1517 IF p_section_code = 'NEW_REPORTS'
1518 THEN
1519 hr_utility.trace('In (IF p_section_code = NEW_REPORTS)'|| l_proc);
1520 -- Store the p_selected_emp to be used in process_Api
1521 hr_transaction_api.set_boolean_value (
1522 p_transaction_step_id =>p_transaction_step_id,
1523 p_person_id => p_login_person_id ,
1524 p_name => 'p_new_reports' ,
1525 p_value => TRUE ) ;
1526
1527 hr_transaction_api.set_varchar2_value (
1528 p_transaction_step_id =>p_transaction_step_id,
1529 p_person_id => p_login_person_id ,
1530 p_name => 'p_emp_name'||p_row_num ,
1531 p_value =>p_emp_name ) ;
1532
1533 hr_transaction_api.set_number_value (
1534 p_transaction_step_id =>p_transaction_step_id,
1535 p_person_id => p_login_person_id ,
1536 p_name => 'p_emp_id'||p_row_num ,
1537 p_value =>p_emp_id ) ;
1538
1539 hr_transaction_api.set_date_value (
1540 p_transaction_step_id =>p_transaction_step_id,
1541 p_person_id => p_login_person_id ,
1542 p_name => 'p_emp_date'||p_row_num ,
1543 p_value =>p_effective_date ) ;
1544
1545 -- write the assignment id of the new direct report
1546 hr_transaction_api.set_number_value (
1547 p_transaction_step_id =>p_transaction_step_id,
1548 p_person_id => p_login_person_id ,
1549 p_name => 'p_emp_asg_id'||p_row_num ,
1550 p_value =>p_assignment_id ) ;
1551
1552 -- write the supervisor assignment id of the new direct report
1553 hr_transaction_api.set_number_value (
1554 p_transaction_step_id =>p_transaction_step_id,
1555 p_person_id => p_login_person_id ,
1556 p_name => 'p_emp_sup_asg_id'||p_row_num ,
1557 p_value =>p_new_sup_asg_id ) ;
1558
1559
1560 END IF ;
1561 hr_utility.set_location('Leaving'|| l_proc,25);
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 hr_utility.trace(' HR_SUPERVISOR_SS.write_transaction: ' || SQLERRM );
1565 hr_utility.set_location('EXCEPTION'|| l_proc,555);
1566 raise ;
1567
1568 END WRITE_TRANSACTION ;
1569
1570 /*
1571 ||===========================================================================
1572 || PROCEDURE: update_asg
1573 || DESCRIPTION:
1574 ||
1575 ||===========================================================================
1576 */
1577 PROCEDURE update_asg
1578 (p_validate in number default 0
1579 ,p_attribute_update_mode in varchar2
1580 ,p_manager_details_tab in out nocopy SSHR_MANAGER_DETAILS_TAB_TYP
1581 ,p_item_type in varchar2 default null
1582 ,p_item_key in varchar2 default null
1583 ,p_actid in varchar2 default null
1584 ,p_rptg_grp_id in varchar2 default null
1585 ,p_plan_id in varchar2 default null
1586 ,p_effective_date_option in varchar2 default null
1587 ,p_num_of_direct_reports in number default 0
1588 ,p_num_of_new_direct_reports in number default 0
1589 ,p_selected_person_id in number
1590 ,p_selected_person_name in varchar2
1591 ,p_term_sup_flag in varchar2
1592 ,p_login_person_id in number
1593 ,p_save_for_later in varchar2 default 'SAVE'
1594 ,p_transaction_step_id in out nocopy number
1595 )
1596 IS
1597
1598 l_transaction_id number ;
1599 l_transaction_step_id number ;
1600 l_error_found boolean := false;
1601 -- Assignment Security
1602 l_supervisor_assignment_id number ;
1603 l_old_supervisor_assignment_id number ;
1604
1605 l_assignment_security_profile varchar(30) := hr_general2.supervisor_assignments_in_use ;
1606 l_proc constant varchar2(100) := g_package || 'update_asg';
1607
1608 BEGIN
1609 hr_utility.set_location('Entering'|| l_proc,5);
1610 -- first call update_asg if not save for later
1611 IF (p_save_for_later = 'SAVE') THEN
1612 hr_utility.trace('IN (if p_save_for_later = SAVE)'|| l_proc);
1613 FOR I IN 1 ..p_manager_details_tab.count LOOP
1614 -- Assignment Security
1615 IF(l_assignment_security_profile <> 'TRUE') then
1616 l_supervisor_assignment_id := hr_api.g_number;
1617 ELSE
1618 l_supervisor_assignment_id
1619 := p_manager_details_tab(I).supervisor_assignment_id;
1620 END IF;
1621 -- End of Assignment Security code
1622 update_asg(p_validate => p_validate,
1623 p_attribute_update_mode => p_attribute_update_mode,
1624 p_item_type => p_item_type,
1625 p_item_key => p_item_key,
1626 p_actid => p_actid,
1627 p_assignment_id => p_manager_details_tab(I).assignment_id,
1628 p_object_version_number => p_manager_details_tab(I).object_ver_number,
1629 p_supervisor_id => p_manager_details_tab(I).supervisor_id,
1630 p_supervisor_assignment_id => l_supervisor_assignment_id,
1631 p_effective_date => p_manager_details_tab(I).effective_date,
1632 p_comment_id => p_manager_details_tab(I).comment_id,
1633 p_effective_start_date => p_manager_details_tab(I).effective_start_date,
1634 p_effective_end_date => p_manager_details_tab(I).effective_end_date,
1635 p_no_managers_warning => p_manager_details_tab(I).no_managers_warning,
1636 p_other_manager_warning => p_manager_details_tab(I).other_manager_warning,
1637 p_error_message_appl => p_manager_details_tab(I).error_message_appl,
1638 p_error_message_name => p_manager_details_tab(I).error_message_name,
1639 p_error_message => p_manager_details_tab(I).error_message);
1640 IF (p_manager_details_tab(I).error_message is not null) THEN
1641 l_error_found := true;
1642 END IF;
1643 END LOOP;
1644 END IF;
1645 hr_utility.trace('end of checking in update_asg');
1646
1647 IF (NOT l_error_found) THEN
1648 hr_utility.trace('IN if (NOT l_error_found)'|| l_proc);
1649
1650 -- second call create transaction to start transaction
1651 create_transaction(p_item_type => p_item_type,
1652 p_item_key => p_item_key,
1653 p_act_id => p_actid,
1654 p_transaction_id => l_transaction_id,
1655 p_transaction_step_id => l_transaction_step_id,
1656 p_login_person_id => p_login_person_id,
1657 p_review_proc_call => wf_engine.GetActivityAttrText(itemtype => p_item_type,
1658 itemkey => p_item_key,
1659 actid => p_actid,
1660 aname => 'HR_REVIEW_REGION_ITEM'),
1661 p_selected_emp_id => p_selected_person_id,
1662 p_selected_emp_name => p_selected_person_name,
1663 p_no_of_direct_reports => p_num_of_direct_reports,
1664 p_no_of_emps => p_num_of_new_direct_reports,
1665 p_term_flag => p_term_sup_flag,
1666 p_rptg_grp_id => p_rptg_grp_id,
1667 p_plan_id => p_plan_id,
1668 p_effective_date_option => p_effective_date_option);
1669 hr_utility.trace('bdefore writing Txn in update_asg');
1670 hr_utility.trace('p_manager_details_tab.count=' || p_manager_details_tab.count);
1671 -- third call write transactions to write to transaction tables
1672 FOR I IN 1 ..p_manager_details_tab.count LOOP
1673
1674 -- Assignment Security
1675 IF(l_assignment_security_profile <> 'TRUE') then
1676
1677 l_supervisor_assignment_id := -1;
1678 l_old_supervisor_assignment_id := -1;
1679 ELSE
1680 l_supervisor_assignment_id
1681 := p_manager_details_tab(I).supervisor_assignment_id;
1682 l_old_supervisor_assignment_id
1683 := p_manager_details_tab(I).old_supervisor_assignment_id;
1684 END IF;
1685 -- End of Assignment Security code
1686
1687
1688 write_transaction(p_old_sup_id => p_manager_details_tab(I).old_supervisor_id,
1689 p_old_sup_asg_id => l_old_supervisor_assignment_id,
1690 p_new_sup_id => p_manager_details_tab(I).supervisor_id,
1691 p_new_sup_asg_id => l_supervisor_assignment_id,
1692
1693 p_old_sup_name => p_manager_details_tab(I).old_supervisor_name,
1694 p_new_sup_name => p_manager_details_tab(I).supervisor_name,
1695 p_emp_name => p_manager_details_tab(I).person_name,
1696 p_emp_id => p_manager_details_tab(I).person_id,
1697 p_effective_date => p_manager_details_tab(I).effective_date,
1698 p_assignment_id => p_manager_details_tab(I).assignment_id,
1699 p_section_code => p_manager_details_tab(I).region,
1700 p_row_num => p_manager_details_tab(I).rownumber,
1701 p_transaction_step_id => l_transaction_step_id,
1702 p_login_person_id => p_login_person_id);
1703 END LOOP;
1704 -- now commit the transaction table data
1705 commit;
1706 END IF;
1707 p_transaction_step_id := l_transaction_step_id;
1708 hr_utility.set_location('Leaving'|| l_proc,15);
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 hr_utility.set_location('EXCEPTION'|| l_proc,555);
1712
1713 hr_utility.set_message('PER', SQLERRM ||' '||to_char(SQLCODE));
1714 hr_utility.raise_error;
1715 hr_utility.set_location('Leaving'|| l_proc,10);
1716 END update_asg;
1717
1718
1719 /*
1720 ||===========================================================================
1721 || PROCEDURE: update_asg
1722 || DESCRIPTION:
1723 ||
1724 ||===========================================================================
1725 */
1726
1727 procedure update_asg
1728 (p_validate in NUMBER default 0
1729 ,p_effective_date in date
1730 ,p_attribute_update_mode in varchar2
1731 ,p_assignment_id in number
1732 ,p_object_version_number in out nocopy number
1733 ,p_supervisor_id in number default null
1734 ,p_supervisor_assignment_id in number default null
1735 ,p_assignment_number in varchar2 default null
1736 ,p_change_reason in varchar2 default null
1737 ,p_comments in varchar2 default null
1738 ,p_date_probation_end in date default null
1739 ,p_default_code_comb_id in number default null
1740 ,p_frequency in varchar2 default null
1741 ,p_internal_address_line in varchar2 default null
1742 ,p_manager_flag in varchar2 default null
1743 ,p_normal_hours in number default null
1744 ,p_perf_review_period in number default null
1745 ,p_perf_review_period_frequency in varchar2 default null
1746 ,p_probation_period in number default null
1747 ,p_probation_unit in varchar2 default null
1748 ,p_sal_review_period in number default null
1749 ,p_sal_review_period_frequency in varchar2 default null
1750 ,p_set_of_books_id in number default null
1751 ,p_source_type in varchar2 default null
1752 ,p_time_normal_finish in varchar2 default null
1753 ,p_time_normal_start in varchar2 default null
1754 ,p_ass_attribute_category in varchar2 default null
1755 ,p_title in varchar2 default null
1756 ,p_comment_id out nocopy number
1757 ,p_effective_start_date out nocopy date
1758 ,p_effective_end_date out nocopy date
1759 ,p_no_managers_warning out nocopy Number
1760 ,p_other_manager_warning out nocopy NUMBER
1761 ,p_item_type in varchar2 default null
1762 ,p_item_key in varchar2 default null
1763 ,p_actid in varchar2 default null
1764 ,p_error_message_appl out nocopy varchar2
1765 ,p_error_message_name out nocopy varchar2
1766 ,p_error_message out nocopy long
1767 )
1768 IS
1769
1770 ln_supervisor_id number ;
1771 lb_no_managers_warn boolean;
1772 lb_other_manager_warn boolean;
1773 lb_validate BOOLEAN ;
1774
1775 -- Bug 2130066 Fix Begins: 01/11/2002
1776 -- Changed from per_assignments_f to per_all_assignments_f. This is
1777 -- necessary because when a Supervisor Security profile restrict to 1 level
1778 -- ,we cannot get data for 2 levels down or beyond. This will happen when
1779 -- the 1st level employee is terminated, we need to change all his direct
1780 -- reports to another employee. In this case, the direct reports of the
1781 -- terminating employee will not be returned because the Supervisor
1782 -- Security profile is restricted to 1 level.
1783 --
1784
1785 CURSOR lc_object_version_no(l_assignment_id number) IS
1786 SELECT object_version_number, assignment_type
1787 FROM per_all_assignments_f -- Bug 2130066 fix
1788 WHERE assignment_id = l_assignment_id
1789 AND (p_effective_date BETWEEN
1790 NVL ( effective_start_date , p_effective_date)
1791 AND NVL ( effective_end_date , p_effective_date )) ;
1792
1793
1794
1795 ln_object_version_no NUMBER ;
1796
1797 -- variables and cursor for applicant_hire
1798 l_object_version_number number;
1799 l_assignment_type per_all_assignments_f.assignment_type%type;
1800 l_per_object_version_number number;
1801 l_employee_number varchar2(30);
1802 l_person_id number;
1803 l_person_name per_all_people_f.global_name%type;
1804 l_assignment_id number;
1805 l_appl_assignment_type per_all_assignments_f.assignment_type%type;
1806 l_per_effective_start_date date;
1807 l_per_effective_end_date date;
1808 l_unaccepted_asg_del_warning boolean;
1809 l_assign_payroll_warning boolean;
1810 l_attribute_update_mode varchar2(50);
1811 isApplicantSubordinate boolean := true;
1812 e_future_assignment_change EXCEPTION;
1813 l_current_person_id varchar2(25) default null;
1814 l_supervisor_old_id number;
1815 -- cursor to get the applicant object_version_number from
1816 -- per_all_people_f
1817 cursor per_applicant_rec(p_appl_person_id in number,
1818 p_appl_effective_date in date) is
1819 select object_version_number
1820 from per_all_people_f
1821 where person_id = p_appl_person_id
1822 and p_appl_effective_date between effective_start_date
1823 and effective_end_date;
1824
1825 -- cursor to get the applicant object_version_number,person_id
1826 -- assignment_type from per_all_assignments_f, for assinging
1827 -- a new manager
1828 cursor asg_appl_rec_assign_manager(p_appl_assign_id in number,
1829 p_appl_effective_date in date) is
1830 select object_version_number,
1831 assignment_type,
1832 person_id
1833 from per_all_assignments_f
1834 where assignment_id = p_appl_assign_id
1835 and p_appl_effective_date between effective_start_date
1836 and effective_end_date;
1837
1838 -- cursor to get the applicant object_version_number
1839 -- assignment_type from per_all_assignments_f, for assigning
1840 -- a new direct reports
1841 cursor asg_appl_rec_assign_directs(p_appl_person_id in number,
1842 p_appl_effective_date in date) is
1843 select object_version_number,
1844 assignment_type
1845 from per_all_assignments_f
1846 where person_id = p_appl_person_id
1847 and p_appl_effective_date between effective_start_date
1848 and effective_end_date;
1849
1850 -- added cursor for Bug 11679239
1851 cursor csr_assignment_person is
1852 select person_id,Global_name from per_all_people_f
1853 where person_id in
1854 (select person_id from per_all_assignments_f
1855 where ASSIGNMENT_ID = p_assignment_id);
1856
1857 cursor csr_assignment_future(p_appl_person_id in number,
1858 p_appl_effective_date in date) is
1859 select assignment_id
1860 from per_all_assignments_f
1861 where person_id = p_appl_person_id
1862 and effective_start_date > p_appl_effective_date;
1863
1864
1865 cursor csr_present_supervisor_id(p_appl_person_id in number,
1866 p_appl_effective_date in date) is
1867 select supervisor_id
1868 from per_all_assignments_f
1869 where person_id = p_appl_person_id
1870 and p_appl_effective_date between effective_start_date and effective_end_date;
1871 -- End of added cursor for Bug 11679239
1872
1873 cursor csr_assignment is
1874 select assignment_id
1875 from per_all_assignments_f
1876 where assignment_id = p_assignment_id;
1877
1878 -- Bug Fix 3041328
1879 cursor csr_person is
1880 select person_id
1881 from per_all_people_f
1882 where person_id = p_supervisor_id;
1883
1884 CURSOR lc_get_current_applicant_flag
1885 (p_person_id in number
1886 ,p_eff_date in date default trunc(sysdate))
1887 IS
1888 SELECT per.current_applicant_flag,
1889 per.current_employee_flag,
1890 per.current_npw_flag
1891 FROM per_all_people_f per
1892 WHERE per.person_id = p_person_id
1893 AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
1894
1895 l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1896 l_current_employee_flag per_all_people_f.current_employee_flag%type;
1897 l_current_npw_flag per_all_people_f.current_npw_flag%type;
1898 l_applicant_person_id per_all_people_f.person_id%type;
1899 l_applicant_assignment_id per_all_assignments_f.assignment_id%type;
1900 l_applicant_effective_date per_all_people_f.effective_start_date%type;
1901
1902 -- Bug Fix 3041328
1903 l_supervisor_id per_all_people_F.person_id%type;
1904 -- l_supervisor_assign_id per_all_assignments_f.assignment_id%type;
1905 l_supervisor_assignment_id per_all_assignments_f.supervisor_assignment_id%type;
1906
1907 l_re_hire_flow varchar2(25) default null;
1908 l_error_message varchar2(500);
1909 l_ex_emp varchar2(10) default null;
1910 CURSOR chk_ex_emp(l_person_id in number, l_effective_date in Date) is
1911 select ppt.SYSTEM_PERSON_TYPE from per_all_people_f paf, per_person_types ppt where person_id = l_person_id
1912 and paf.PERSON_TYPE_ID = ppt.PERSON_TYPE_ID
1913 and l_effective_date between effective_start_date and effective_end_date;
1914
1915 l_proc constant varchar2(100) := g_package || ' update_asg';
1916 BEGIN
1917 hr_utility.set_location('Entering'|| l_proc,5);
1918 SAVEPOINT sup_update_asg;
1919 l_attribute_update_mode := p_attribute_update_mode;
1920 l_assignment_id := p_assignment_id;
1921
1922 --Validate the p_assignment_id
1923 open csr_assignment;
1924 fetch csr_assignment into l_assignment_id;
1925 if csr_assignment%notfound then
1926 hr_new_user_reg_ss.processNewUserTransaction
1927 (WfItemType => p_item_type
1928 ,WfItemKey => p_item_key
1929 ,PersonId => l_person_id
1930 ,AssignmentId => l_assignment_id);
1931 end if;
1932 close csr_assignment;
1933
1934 -- Bug Fix 3041328
1935 l_supervisor_id := p_supervisor_id;
1936 l_supervisor_assignment_id := p_supervisor_assignment_id;
1937 --Validate the p_supervisor_id
1938 if l_supervisor_id is not null then
1939 hr_utility.trace('In ( if l_supervisor_id is not null )'|| l_proc);
1940 l_re_hire_flow := wf_engine.GetItemAttrText(p_item_type,p_item_key,'HR_FLOW_IDENTIFIER',true);
1941 open chk_ex_emp(l_supervisor_id, p_effective_date);
1942 fetch chk_ex_emp into l_ex_emp;
1943 close chk_ex_emp;
1944
1945 if nvl(l_re_hire_flow,'N') = 'EX_EMP' and nvl(l_ex_emp,'N') = 'EX_EMP' then
1946 hr_new_user_reg_ss.processExEmpTransaction
1947 (WfItemType => p_item_type
1948 ,WfItemKey => p_item_key
1949 ,PersonId => l_supervisor_id
1950 ,AssignmentId => l_supervisor_assignment_id
1951 ,p_error_message => l_error_message);
1952 else
1953 open csr_person;
1954 fetch csr_person into l_supervisor_id;
1955 if csr_person%notfound then
1956 hr_utility.trace('In ( if csr_person%notfound )'|| l_proc);
1957 hr_new_user_reg_ss.processNewUserTransaction
1958 (WfItemType => p_item_type
1959 ,WfItemKey => p_item_key
1960 ,PersonId => l_supervisor_id
1961 ,AssignmentId => l_supervisor_assignment_id);
1962 end if;
1963 close csr_person;
1964 -- end of Bug Fix 3041328
1965 end if;
1966 end if;
1967
1968 --Fix for Bug 11679239
1969 --check whether the current employee has future date assignment changes
1970
1971 l_current_person_id := wf_engine.GetItemAttrText(p_item_type,p_item_key,'CURRENT_PERSON_ID',true);
1972
1973 open csr_assignment_person;
1974 fetch csr_assignment_person into l_person_id,l_person_name;
1975 close csr_assignment_person;
1976
1977 open csr_present_supervisor_id(l_person_id,p_effective_date);
1978 fetch csr_present_supervisor_id into l_supervisor_old_id;
1979 close csr_present_supervisor_id;
1980
1981 if (p_supervisor_id<>l_supervisor_old_id) then
1982 hr_utility.trace('Entering if (p_supervisor_id<>l_supervisor_old_id)');
1983 --added condition to check only for direct reports
1984 if (l_current_person_id <> l_person_id) then
1985 open csr_assignment_future(l_person_id,p_effective_date);
1986 fetch csr_assignment_future into l_assignment_id;
1987 if csr_assignment_future%found then
1988 hr_utility.trace('In ( if csr_person%notfound )'|| l_proc);
1989 hr_utility.trace('Future assignemnt change found');
1990
1991 RAISE e_future_assignment_change;
1992 end if;
1993 end if; --End of added condition to check only for direct reports
1994 end if;
1995
1996 --End of check
1997 --End of Fix for Bug 11679239
1998
1999 -- To support applicant hire in New Hire process, we need to convert the applicant
2000 -- to employee then update the assignment and rollback the employee to applicant
2001
2002 -- If we are assigning a new manager to the applicant record, then we need
2003 -- hire the applicant and assign a manager
2004
2005 -- If we are assigning new direct reports to the applicant record, then we
2006 -- need to hire the applicant and assign new directs
2007 -- First get the assignment_type using the assingmet id, if its an applicant
2008 -- then we are assigning a new manager to the applicant
2009 /*
2010
2011 Check the mode we are entering to update
2012
2013 case 1: update the current employee assignment record with applicant person id
2014 Create a employee record with applicant person id
2015 case 2: update the applicant assignment record with existing employee person id.
2016 Create a employee record with applicant assignment record details.
2017 case 3: update the current employee assignment record with existing employee person id
2018 No need to create a dummy employee record.
2019
2020 */
2021
2022
2023 -- Checking case 1:
2024 open lc_get_current_applicant_flag(p_supervisor_id, p_effective_date);
2025 fetch lc_get_current_applicant_flag into l_current_applicant_flag, l_current_employee_flag, l_current_npw_flag;
2026 close lc_get_current_applicant_flag;
2027
2028 if (l_current_applicant_flag = 'Y' AND
2029 nvl(l_current_employee_flag, 'N') <> 'Y' AND
2030 nvl(l_current_npw_flag, 'N') <> 'Y') then
2031 hr_utility.trace('In ( if of checking Case1 mode )'|| l_proc);
2032 g_applicant_hire := true;
2033 isApplicantSubordinate := false;
2034 l_applicant_person_id := p_supervisor_id;
2035 -- Get the assignment_id for the applicant from workflow
2036 l_applicant_assignment_id := wf_engine.getItemAttrText(
2037 itemtype => p_item_type,
2038 itemkey => p_item_key,
2039 aname => 'CURRENT_ASSIGNMENT_ID');
2040 l_applicant_effective_date := wf_engine.getItemAttrDate(
2041 itemtype => p_item_type,
2042 itemkey => p_item_key,
2043 aname => 'CURRENT_EFFECTIVE_DATE');
2044 -- Get the object version number
2045 open asg_appl_rec_assign_directs(p_supervisor_id, p_effective_date);
2046 fetch asg_appl_rec_assign_directs into l_object_version_number
2047 ,l_appl_assignment_type;
2048 close asg_appl_rec_assign_directs;
2049
2050 -- call the API to hire this person.
2051 -- reset the g_applicant_hire to false.
2052 end if;
2053
2054 -- Checking case 2 :
2055 open asg_appl_rec_assign_manager(p_assignment_id, p_effective_date);
2056 fetch asg_appl_rec_assign_manager into l_object_version_number
2057 ,l_appl_assignment_type
2058 ,l_person_id;
2059 close asg_appl_rec_assign_manager;
2060
2061 if(l_appl_assignment_type = 'A') then
2062 hr_utility.trace('In ( if (l_appl_assignment_type = A )'|| l_proc);
2063 g_applicant_hire := true;
2064 isApplicantSubordinate := false;
2065 -- Get the person_id from assignment record.
2066 l_applicant_person_id := l_person_id;
2067 l_applicant_assignment_id := p_assignment_id;
2068 l_applicant_effective_date := wf_engine.getItemAttrDate(
2069 itemtype => p_item_type,
2070 itemkey => p_item_key,
2071 aname => 'CURRENT_EFFECTIVE_DATE');
2072 -- first get the object_version_number for the applicant from
2073 -- per_all_people_f
2074
2075 open per_applicant_rec(l_person_id, l_applicant_effective_date);
2076 fetch per_applicant_rec into l_per_object_version_number;
2077 close per_applicant_rec;
2078 end if;
2079
2080 if (g_applicant_hire) then
2081 hr_utility.trace('In ( if of g_applicant_hire )'|| l_proc);
2082 -- SAVEPOINT applicant_hire;
2083
2084 -- get the employee number from Basic Details Step
2085 /*hr_person_info_util_ss.get_trns_employee_number(
2086 p_item_type => p_item_type
2087 ,p_item_key => p_item_key
2088 ,p_employee_number => l_employee_number);
2089 -- first get the object_version_number for the applicant from
2090 -- per_all_people_f
2091
2092 open per_applicant_rec(l_applicant_person_id, l_applicant_effective_date);
2093 fetch per_applicant_rec into l_per_object_version_number;
2094 close per_applicant_rec;
2095
2096 --call the hr_applicant_api.hire_applicant
2097 hr_applicant_api.hire_applicant(
2098 p_validate => false
2099 ,p_hire_date => l_applicant_effective_date
2100 ,p_person_id => l_applicant_person_id
2101 ,p_per_object_version_number => l_per_object_version_number
2102 ,p_assignment_id => l_applicant_assignment_id
2103 ,p_employee_number => l_employee_number
2104 ,p_per_effective_start_date => l_per_effective_start_date
2105 ,p_per_effective_end_date => l_per_effective_end_date
2106 ,p_unaccepted_asg_del_warning=> l_unaccepted_asg_del_warning
2107 ,p_assign_payroll_warning => l_assign_payroll_warning
2108 ,p_source => true); */
2109
2110 hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
2111 p_item_key => p_item_key
2112 ,p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
2113
2114 end if;
2115
2116 if p_validate = 1 THEN
2117 lb_validate := true ;
2118 else
2119 lb_validate := false ;
2120 end if ;
2121
2122
2123 OPEN lc_object_version_no(l_assignment_id) ;
2124 FETCH lc_object_version_no into ln_object_version_no,l_assignment_type;
2125 CLOSE lc_object_version_no ;
2126
2127 -- Call the actual API.
2128 hr_assignment_att.update_asg(
2129 p_validate =>lb_validate,
2130 p_effective_date =>p_effective_date ,
2131 p_attribute_update_mode =>p_attribute_update_mode ,
2132 p_assignment_id =>l_assignment_id ,
2133 p_assignment_type =>l_assignment_type,
2134 p_object_version_number =>ln_object_version_no ,
2135 p_supervisor_id => l_supervisor_id ,
2136 -- Assignment Security
2137 p_supervisor_assignment_id =>l_supervisor_assignment_id,
2138 p_comment_id => p_comment_id ,
2139 p_effective_start_date =>p_effective_start_date,
2140 p_effective_end_date =>p_effective_end_date,
2141 p_no_managers_warning =>lb_no_managers_warn,
2142 p_other_manager_warning =>lb_other_manager_warn ) ;
2143
2144 -- applicant_hire
2145 if (g_applicant_hire) then
2146 g_applicant_hire := false;
2147 --rollback to applicant_hire;
2148 end if;
2149 hr_utility.set_location('Leaving'|| l_proc,40);
2150 ROLLBACK TO sup_update_asg;
2151
2152 EXCEPTION
2153 --added for Bug 11679239
2154 WHEN e_future_assignment_change THEN
2155 hr_utility.set_location('EXCEPTION'|| l_proc,555);
2156 hr_utility.set_message ('800','HR_50438_FUTUR_CHNG_EXST_ERR');
2157 hr_utility.set_message_token ('PERSON_NAME', l_person_name);
2158 p_error_message := hr_utility.get_message;
2159 --End of added for Bug 11679239
2160
2161 WHEN hr_utility.hr_error THEN
2162 hr_utility.set_location('EXCEPTION'|| l_proc,555);
2163 -- -------------------------------------------
2164 -- an application error has been raised so we must
2165 -- redisplay the web form to display the error
2166 -- --------------------------------------------
2167 ROLLBACK TO sup_update_asg;
2168 hr_message.provide_error;
2169 p_error_message_appl := hr_message.last_message_app;
2170 p_error_message_name := hr_message.last_message_name;
2171 p_error_message := null;
2172
2173 WHEN OTHERS THEN
2174 hr_utility.set_location('EXCEPTION'|| l_proc,555);
2175 -- applicant_hire
2176 if (g_applicant_hire) then
2177 g_applicant_hire := false;
2178 -- rollback to applicant_hire;
2179 end if;
2180 IF (hr_utility.get_message IS NOT NULL) THEN
2181 p_error_message := 'ORA' || hr_utility.hr_error_number || ' '||
2182 hr_utility.get_message;
2183 ELSE
2184 p_error_message := hr_message.get_message_text;
2185 END IF;
2186 p_error_message_appl := null;
2187 p_error_message_name := null;
2188 ROLLBACK TO sup_update_asg;
2189
2190
2191 END update_asg;
2192
2193
2194 END HR_SUPERVISOR_SS;
[Home] [Help]