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