[Home] [Help]
PACKAGE BODY: APPS.PER_HRWF_SYNCH
Source
1 PACKAGE BODY PER_HRWF_SYNCH AS
2 /* $Header: perhrwfs.pkb 120.6.12020000.6 2013/05/24 06:50:04 pathota ship $ */
3 --
4 g_package varchar2(30) := 'per_hrwf_synch.';
5 g_count number := 0;
6 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< chk_date_status >-----------------------------|
13 p_end_date in date) return varchar2 is
10 -- ----------------------------------------------------------------------------
11 function chk_date_status
12 (p_start_date in date,
14 --
15 l_proc varchar2(80) := g_package||'chk_date_status';
16 l_status varchar2(10);
17 --
18 begin
19 --
20 -- This routine is used to check where start date and end date fall
21 -- in the time frames of CURRENT, FUTURE or PAST.
22 --
23 -- If sysdate between p_start_date and p_end_date then
24 -- return 'CURRENT'
25 -- elsif sysdate < p_start_date then
26 -- return 'FUTURE'
27 -- elsif sysdate > p_start_date then
28 -- return 'PAST'
29 -- end if;
30 --
31 hr_utility.set_location('Entering '||l_proc,10);
32 --
33 -- bug 4635241 Modified the following If condition to consider the Past date as well
34 --
35 if sysdate between p_start_date and p_end_date then
36 -- or (p_start_date < sysdate and p_end_date < sysdate) then --commented for 9735750
37 --
38 l_status := 'CURRENT';
39 --
40 elsif sysdate < p_start_date then
41 --
42 l_status := 'FUTURE';
43 --
44 elsif sysdate > p_start_date then
45 --
46 l_status := 'PAST';
47 --
48 end if;
49 --
50 hr_utility.set_location('Leaving '||l_proc,10);
51 return(l_status);
52 --
53 end chk_date_status;
54 --
55 --
56 --
57 -- ----------------------------------------------------------------------------
58 -- |------------------------------< call_back >--------------------------------|
59 -- ----------------------------------------------------------------------------
60 procedure call_back(p_parameters in wf_parameter_list_t default null) is
61 --
62 l_counter number;
63 l_proc varchar2(80) := g_package||'call_back';
64 l_entity varchar2(10);
65 l_person_rec per_all_people_f%rowtype;
66 l_assignment_rec per_all_assignments_f%rowtype;
67 l_pds_rec per_periods_of_service%rowtype;
68 l_person_id number;
69 l_assignment_id number;
70 l_person_id_canonical varchar2(20);
71 l_assignment_id_canonical varchar2(20);
72 l_start_date date;
73 l_end_date date;
74 l_start_date_canonical varchar2(30);
75 l_end_date_canonical varchar2(30);
76 -- 3297591 these not really needed here
77 l_person_party_id number;
78 l_person_party_id_canonical varchar2(20);
79 --
80 --per_all_people_f%rowtype cursor.
81 --
82 cursor l_person_cur is
83 select *
84 from per_all_people_f
85 where person_id = l_person_id
86 and trunc(effective_start_date) = l_start_date
87 and trunc(effective_end_date) = l_end_date;
88 --
89 --per_periods_of_service%rowtype cursor
90 cursor l_pds_cur is
91 Select *
92 from per_periods_of_service
93 where person_id = l_person_id
94 and trunc(date_start) = l_start_date
95 and trunc(actual_termination_date) = l_end_date;
96 --per_all_assignments_f%rowtype cursor.
97 --
98 cursor l_assignment_cur is
99 select *
100 from per_all_assignments_f
101 where assignment_id = l_assignment_id
102 and trunc(effective_start_date) = l_start_date
103 and trunc(effective_end_date) = l_end_date;
104 --
105 begin
106 --
107 hr_utility.set_location('Entering '||l_proc,10);
108 --
109 -- Check that the records exist in the parameter list.
110 -- if p_parametrs.count = 0 then .... return .....end if
111 --
112 if p_parameters.count = 0 then
113 --
114 hr_utility.set_location('Not a single parameter exists '||l_proc,15);
115 hr_utility.set_location('Leaving '||l_proc,16);
116 return;
117 --
118 end if;
119 --
120 --get the attribute CONTEXT value from the parameter list.
121 --
122 hr_utility.set_location('Start assigning values from param_list '||l_proc,25);
123
124 l_entity := wf_event.getvalueforparameter(
125 p_name => 'CONTEXT',
126 p_parameterlist => p_parameters);
127 --
128 l_start_date_canonical := wf_event.getvalueforparameter(
129 p_name => 'STARTDATE',
130 p_parameterlist => p_parameters);
131
132 l_end_date_canonical := wf_event.getvalueforparameter(
133 p_name => 'ENDDATE',
134 p_parameterlist => p_parameters);
135
136 l_start_date := FND_DATE.canonical_to_date(l_start_date_canonical);
137 l_end_date := FND_DATE.canonical_to_date(l_end_date_canonical);
138 hr_utility.set_location('End assign Values from param_list '||l_proc,30);
139 --
140 if l_entity = 'PERSON' then
141 --
142 hr_utility.set_location('Person Record '||l_proc,35);
143 -- get attribute values of peson from parameter list.
144 --
145 l_person_id_canonical := wf_event.getvalueforparameter(
146 p_name => 'PERSONID',
147 p_parameterlist => p_parameters);
148 l_person_id := FND_NUMBER.canonical_to_number(l_person_id_canonical);
149 --
150 -- 3297591
151 --/*
152 l_person_party_id_canonical := wf_event.getvalueforparameter(
153 p_name => 'PERSONPARTYID',
154 p_parameterlist => p_parameters);
155 l_person_party_id := FND_NUMBER.canonical_to_number(l_person_party_id_canonical);
156 --*/
157 --
158 -- get the appropriate person record.
159 --
160 open l_person_cur;
161 fetch l_person_cur into l_person_rec;
162 close l_person_cur;
163 --
164 hr_utility.set_location('Before call to person routine '||l_proc,40);
165 --
166 PER_HRWF_SYNCH.per_per_wf(p_rec => l_person_rec,
167 p_action => null);
168 hr_utility.set_location('After call to person routine '||l_proc,45);
169 --
170 end if; -- l_entity = 'PERSON'
171 --
172 if l_entity = 'PERSON:TERMINATION' then
173
174 hr_utility.set_location('Person Termination Record '||l_proc,46);
175
176 l_person_id_canonical := wf_event.getvalueforparameter( p_name => 'PERSONID',
177 p_parameterlist => p_parameters);
178 l_person_id := FND_NUMBER.canonical_to_number(l_person_id_canonical);
179
180 open l_pds_cur;
181 fetch l_pds_cur into l_pds_rec;
182 close l_pds_cur;
183
184 hr_utility.set_location('Before call to pds routine '||l_proc,47);
185
186 PER_HRWF_SYNCH.per_pds_wf(p_rec => l_pds_rec,
187 p_date => l_pds_rec.actual_termination_date,
188 p_action => 'TERMINATION');
189
190 end if;
191 --
192 if l_entity = 'POSITION' then
193 --
194 hr_utility.set_location('Position Record '||l_proc,35);
195 l_assignment_id_canonical := wf_event.getvalueforparameter(
196 p_name => 'ASSIGNMENTID',
197 p_parameterlist => p_parameters);
198 --
199 l_assignment_id := FND_NUMBER.canonical_to_number(l_assignment_id_canonical);
200 --
201 -- get the appropriate assignment record
202 --
203 open l_assignment_cur;
204 fetch l_assignment_cur into l_assignment_rec;
205 close l_assignment_cur;
206 hr_utility.set_location('Before call to Assignment routine '||l_proc,35);
207 --
208 --call the routine
209 --
210 PER_HRWF_SYNCH.per_asg_wf(p_rec => l_assignment_rec,
211 p_action => null);
212 hr_utility.set_location('After call to Assignment routine '||l_proc,35);
213 --
214 end if; --l_entity = 'POSITION'
215 --
216 hr_utility.set_location('Leaving '||l_proc,10);
217 --
218 end call_back;
219 --
220 --
221 --
222 -- ----------------------------------------------------------------------------
223 -- |-----------------------------< per_per_wf >-------------------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 --
227 procedure per_per_wf(
228 p_rec in per_all_people_f%rowtype,
229 p_action in varchar2) is
230 --
231 -- local variables
232 --
233 l_proc varchar2(80) := g_package||'per_per_wf';
234 l_date_chk varchar2(10);
235 l_emp_num_canonical varchar2(30);
236 l_start_date_canonical varchar2(30);
237 l_end_date_canonical varchar2(30);
238 l_person_id_canonical varchar2(20);
239 l_person_party_id_canonical varchar2(20); -- 3297591
240 l_parameters wf_parameter_list_t;
241 --
242 --
243 l_employee_type per_person_types.user_person_type%type;
244 l_birth_date varchar2(30);
245 l_hire_date varchar2(30);
246 l_date_created varchar2(30);
247 l_update_date varchar2(30);
248 l_updated_by varchar2(20);
249 l_created_by varchar2(20);
250 -- l_user_name wf_local_roles.name%type;
251 -- l_user_name varchar2(60); -- 3297591
252 l_user_name fnd_user.user_name%type; -- 5340008
253 l_per_role varchar2(200);
254 l_expiration_date varchar2(30);
255 --l_display_name wf_local_roles.display_name%TYPE;;
256 l_display_name varchar2(310); -- 3297591,4149356
257 l_update varchar(1) default 'Y'; -- Bug 4597033
258 l_check_person_id per_all_people_f.person_id%type;
259 --
260 -- cursors
261 --
262 cursor role_exists(p_person_id in number) is
263 select name
264 from wf_local_roles
265 where orig_system = 'PER'
266 and orig_system_id = p_person_id
267 and rownum = 1; -- This extra WHERE condition is added as per the request of "tpapired"
268 -- fix for bug 7455694
269
270 -- below cursor is added for fix for bug#16743491
271 cursor per_role_role_exists(p_person_id in number) is
272 select name
273 from wf_local_roles
274 where orig_system = 'PER_ROLE'
275 and orig_system_id = p_person_id
276 and rownum = 1;
277
278 l_description wf_local_roles.description%TYPE;
279 cursor role_description(p_person_id in number) is
280 select description
281 from wf_local_roles
282 where orig_system = 'PER'
283 and orig_system_id = p_person_id
284 and rownum = 1;
285
286 --
287 Begin
288 --
289 hr_utility.set_location('Entering '||l_proc,10);
290 --
291 -- check for the current employee flag
292 -- if current employee flag <> 'Y' then
293 -- return
294 -- end if
295 --
296 -- the following if condition was changed to include a check for
297 -- contingent worker to be taken care.
298 --
299 -- Bug fix 3883910
300 -- If condition to check whether the person is an employee or cwk
301 -- is removed, as wf_local_roles as to be updated for all persons.
302
303 /* if (nvl(p_rec.current_employee_flag,'N') <>'Y'
304 and
305 nvl(p_rec.current_npw_flag,'N') <>'Y') then
306 --
307 hr_utility.set_location('Not a current employee '||l_proc,15);
308 hr_utility.set_location('Leaving ... '||l_proc,15);
309 return;
310 --
311 end if;*/
312 --
313 l_person_id_canonical := FND_NUMBER.number_to_canonical(p_rec.person_id);
314 l_person_party_id_canonical := FND_NUMBER.number_to_canonical(p_rec.party_id); -- 3297591
315 l_start_date_canonical := FND_DATE.date_to_canonical(p_rec.effective_start_date);
316 l_end_date_canonical := FND_DATE.date_to_canonical(p_rec.effective_end_date);
317 -- need to set up display name here - 3297591...
318 begin
319 l_display_name := HR_PERSON_NAME.get_person_name(p_rec.person_id,
320 p_rec.effective_start_date,
324 End;
321 null); -- 3297591
322 exception
323 when others then null;
325 -- Check if this is DELETE
326 --
327 if nvl(p_action,'NO_DELETE') = 'DELETE' then
328 --
329 hr_utility.set_location('Delete Person '||l_proc,20);
330 wf_event.addparametertolist(
331 p_name => 'DELETE',
332 p_value => 'TRUE',
333 p_parameterlist => l_parameters);
334 --
335 -- if ROLE is already created, then assign USER_NAME to l_user_name.
336 --
337 open role_exists(p_rec.person_id);
338 fetch role_exists into l_user_name;
339 --
340 if role_exists%notfound then
341 l_user_name := 'PER:'||p_rec.person_id;
342 end if;
343 close role_exists;
344 --
345 wf_event.addparametertolist('orclWFOrigSystem','PER',l_parameters);
346 wf_event.addparametertolist('orclWFOrigSystemID',p_rec.person_id,l_parameters);
347 wf_event.addparametertolist('PERSON_PARTY_ID',p_rec.party_id,l_parameters); -- 3297591
348 wf_event.addparametertolist('USER_NAME',l_user_name,l_parameters);
349 -- wf_event.addparametertolist('DisplayName',p_rec.full_name,l_parameters); -- 3297591
350 wf_event.addparametertolist('DisplayName',l_display_name,l_parameters);
351 --
352 wf_local_synch.propagate_role(
353 p_orig_system => 'PER',
354 p_orig_system_id => p_rec.person_id,
355 p_attributes => l_parameters,
356 p_start_date => p_rec.effective_start_date,
357 p_expiration_date => null);
358 --
359 wf_directory.DeleteRole ( p_name => l_user_name,
360 p_origSystem => 'PER' ,
361 p_origSystemID =>p_rec.person_id );
362
363 open per_role_role_exists (p_rec.person_id);
364 fetch per_role_role_exists into l_per_role;
365 hr_utility.set_location('PER_HRWF_SYNCH.per_per_wf - l_check_person_id'||p_rec.person_id,70);
366 if per_role_role_exists%notfound then
367 l_per_role := 'PER_ROLE:'||p_rec.person_id;
368 end if;
369 close per_role_role_exists;
370
371 wf_local_synch.propagate_role(
372 p_orig_system => 'PER_ROLE',
373 p_orig_system_id => p_rec.person_id,
374 p_attributes => l_parameters,
375 p_start_date => p_rec.effective_start_date,
376 p_expiration_date => p_rec.effective_end_date);
377
378 wf_directory.DeleteRole ( p_name => l_per_role,
379 p_origSystem => 'PER_ROLE' ,
380 p_origSystemID =>p_rec.person_id );
381
382
383 return;
384 --
385 end if;
386 --
387 l_date_chk := PER_HRWF_SYNCH.chk_date_status(
388 p_rec.effective_start_date,
389 p_rec.effective_end_date);
390 --
391 -- If the transaction is in CURRENT time frame
392 --
393 if l_date_chk = 'CURRENT' then
394 --
395 hr_utility.set_location('Current Person '||l_proc,20);
396 -- call propagate user as of now
397 --
398 -- assign attributes and values(not null values only) to l_parameters.
399 --
400 -- NEW CODE START
401 --
402 --
403 -- if ROLE is already created, then assign USER_NAME to l_user_name.
404 --
405 open role_exists(p_rec.person_id);
406 fetch role_exists into l_user_name;
407 --
408 if role_exists%notfound then
409 l_user_name := 'PER:'||p_rec.person_id;
410 l_update := 'N'; -- Bug 4597033
411 end if;
412 --
413 close role_exists;
414 --
415 hr_utility.set_location('start add params',63);
416 wf_event.addparametertolist(
417 p_name => 'USER_NAME',
418 p_value => l_user_name,
419 p_parameterlist => l_parameters);
420 /*
421 wf_event.addparametertolist(
422 p_name => 'DisplayName',
423 p_value => p_rec.full_name,
424 p_parameterlist => l_parameters);
425 */ -- 3297591
426
427 wf_event.addparametertolist(
428 p_name => 'DisplayName',
429 p_value => l_display_name,
430 p_parameterlist => l_parameters); -- 3297591
431
432 wf_event.addparametertolist(
433 p_name => 'PERSON_PARTY_ID',
434 p_value => p_rec.party_id,
435 p_parameterlist => l_parameters); -- 3297591
436
437 /* -- For now these are commented
438 wf_event.addparametertolist(
439 p_name => 'orclWorkFlowNotificationPref',
440 p_value => 'QUERY',
441 p_parameterlist => l_parameters);
442
443 wf_event.addparametertolist(
444 p_name => 'preferredLanguage',
445 p_value => p_rec.correspondence_language,
446 p_parameterlist => l_parameters);
447
448 wf_event.addparametertolist(
449 p_name => 'FascimileTelephoneNumber',
450 p_value => FAX,
451 p_parameterlist => l_parameters);
452 wf_event.addparametertolist(
453 p_name => 'orclNLSTerritory',
454 p_value => TERRITORY,
455 p_parameterlist => l_parameters);
456
457 */
458 wf_event.addparametertolist(
459 p_name => 'mail',
460 p_value => p_rec.email_address,
461 p_parameterlist => l_parameters);
462
463 wf_event.addparametertolist(
464 p_name => 'orclIsEnabled',
465 p_value => 'ACTIVE',
466 p_parameterlist => l_parameters);
467 -- bug 4635241 commented out the following line and set the value to Null.
468 -- l_expiration_date := FND_DATE.date_to_canonical(p_rec.effective_end_date);
469 l_expiration_date := NULL;
470 wf_event.addparametertolist(
471 p_name => 'ExpirationDate',
472 p_value => l_expiration_date,
473 p_parameterlist => l_parameters);
474
475 wf_event.addparametertolist(
476 p_name => 'orclWFOrigSystem',
477 p_value => 'PER',
478 p_parameterlist => l_parameters);
479
480 wf_event.addparametertolist(
481 p_name => 'orclWFOrigSystemID',
482 p_value => p_rec.person_id,
483 p_parameterlist => l_parameters);
484
485 --Added below paramter description for bug#14767779
486
487 wf_event.addparametertolist(p_name => 'description',
488 p_value => p_rec.full_name,
489 p_parameterlist => l_parameters);
490
491 -- Bug 4597033
492 -- If the transaction is an update then passing the overwrite parameter with value TRUE
493 if l_update = 'Y' then
494 wf_event.addparametertolist(
495 p_name => 'WFSYNCH_OVERWRITE',
496 p_value => 'TRUE',
497 p_parameterlist => l_parameters);
498
499 open role_description (p_rec.person_id);
500 fetch role_description into l_description ;
501 close role_description;
502 hr_utility.set_location('l_description '|| l_description,20);
503
504 wf_event.addparametertolist(
505 p_name => 'description',
506 p_value => l_description,
507 p_parameterlist => l_parameters);
508
509
510 end if;
511 hr_utility.set_location('end add params',64);
512 --
513 -- NEW CODE END
514 --
515 wf_local_synch.propagate_role(
516 p_orig_system => 'PER',
517 p_orig_system_id => p_rec.person_id,
518 p_attributes => l_parameters,
519 p_start_date => p_rec.effective_start_date,
520 -- p_expiration_date => p_rec.effective_end_date);
521 p_expiration_date => NULL);
522 -- Bug 4635241 Modified the call to wf_local_synch.propagate_role
523 -- by passing NULL as value for p_expiration_date
524 --
525 hr_utility.set_location('After calling propagate_role ',65);
526 end if; --l_date_chk = CURRENT
527 --
528 -- if this is a future dated transaction then defer calling propagate user
529 -- until the future date equals sysdate
530 --
531 if l_date_chk = 'FUTURE' then
532 --
533 -- This is effective in the future date, Call_back routine must be called.
534 --
535 -- assign attributes and values for l_parameters.
536 --
537 hr_utility.set_location('Future Person '||l_proc,50);
538 --
539 wf_event.addparametertolist(
540 p_name => 'CONTEXT',
541 p_value => 'PERSON',
542 p_parameterlist => l_parameters);
543
544 wf_event.addparametertolist(
545 p_name => 'PERSONID',
546 p_value => l_person_id_canonical,
547 p_parameterlist => l_parameters);
548 wf_event.addparametertolist(
549 p_name => 'STARTDATE',
550 p_value => l_start_date_canonical,
551 p_parameterlist => l_parameters);
552 wf_event.addparametertolist(
553 p_name => 'ENDDATE',
554 p_value => l_end_date_canonical,
555 p_parameterlist => l_parameters);
556
557 --
558 hr_utility.set_location('Before calling call_me_later '||l_proc,50);
559 -- Call the routine wf_util.call_me_later ()
560 --
561 wf_util.call_me_later(p_callback => 'per_hrwf_synch.call_back',
562 p_when => trunc(p_rec.effective_start_date),
563 p_parameters => l_parameters);
564 --
565 end if;
566 --
567 hr_utility.set_location('Leaving '||l_proc,10);
568 --
569 end per_per_wf;
570 --
571 --
572 --
573 -- ----------------------------------------------------------------------------
574 -- |-----------------------------< per_asg_wf >-------------------------------|
575 -- ----------------------------------------------------------------------------
576 --
577 --
578 --
579 procedure per_asg_wf(
580 p_rec in per_all_assignments_f%rowtype,
581 p_action in varchar2) is
582 --
583 -- local variables
584 --
585 l_proc varchar2(80) := g_package||'per_asg_wf';
586 l_date_chk varchar2(10);
587 l_start_date_canonical varchar2(30);
588 l_end_date_canonical varchar2(30);
589 l_person_id_canonical varchar2(20);
590 l_assignment_id_canonical varchar2(20);
591 l_parameters wf_parameter_list_t;
592 --
593 l_position_id per_all_assignments_f.position_id%TYPE;
594 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
595 l_max_date date;
596 l_start_date date;
597 l_end_date date;
598 --
599 cursor start_date is
600 select min(effective_start_date)
601 from per_all_assignments_f
602 where assignment_id = l_assignment_id
603 and position_id = l_position_id
604 and nvl(assignment_type,'Z') = 'E'
605 and nvl(primary_flag,'Z') = 'Y';
606 --
607 cursor maxpos_date is
608 select max(nvl(date_end, hr_api.g_eot))
609 from per_all_positions
610 where position_id = l_position_id;
611 --
612 Begin
613 --
614 l_position_id := p_rec.position_id;
615 l_assignment_id := p_rec.assignment_id;
616 --
617 hr_utility.set_location('Entering '||l_proc,10);
618 --
619 -- check for the primary assignment flag=Y and assignmrnt type = E
620 -- if primary flag <> 'Y' then
621 -- return
622 -- end if
623 --
624 if p_rec.assignment_type <> 'E' and p_rec.primary_flag <>'Y' then
625 --
626 hr_utility.set_location('Assignment neither Primary nor type = E '||l_proc,15);
627 hr_utility.set_location('Leaving '||l_proc,16);
628 return;
629 --
630 end if;
631 --
632 -- No need to process where position_id is null
633 --
634 if p_rec.position_id is null then
635 --
636 hr_utility.set_location('Position id is null .. '||l_proc,20);
637 hr_utility.set_location('Leaving .. '||l_proc,21);
638 return;
639 --
640 end if;
641 --
645 hr_utility.set_location('Delete Assignment ',25);
642 -- Check if this is DELETE
643 --
644 if nvl(p_action,'NO_DELETE') = 'DELETE' then
646 --
647 WF_LOCAL_SYNCH.propagate_user_role(
648 p_user_orig_system => 'PER',
649 p_user_orig_system_id => p_rec.person_id,
650 p_role_orig_system => 'POS',
651 p_role_orig_system_id => 'POS'||':'||p_rec.position_id,
652 p_start_date => p_rec.effective_start_date,
653 p_expiration_date => p_rec.effective_end_date);
654 return;
655 --
656 end if;
657 --
658 l_date_chk := PER_HRWF_SYNCH.chk_date_status(
659 p_rec.effective_start_date,
660 p_rec.effective_end_date);
661 --
662 -- If the transaction is in CURRENT time frame
663 --
664 if l_date_chk = 'CURRENT' then
665 --
666 hr_utility.set_location('Current Assignment ',30);
667 -- call propagate user as of now
668 --
669 -- we are not passing any attributes to propagate_user_role procedure....
670 open start_date;
671 fetch start_date into l_start_date;
672 close start_date;
673 --
674 open maxpos_date;
675 fetch maxpos_date into l_max_date;
676 close maxpos_date;
677 --
678 if l_max_date <= p_rec.effective_end_date then
679 l_end_date := l_max_date;
680 else
681 l_end_date := p_rec.effective_end_date;
682 end if;
683 --
684 -- Call wf_local_synch.propogate_user_role()
685 hr_utility.set_location('Before Calling propagate_user_role ',35);
686 WF_LOCAL_SYNCH.propagate_user_role(
687 p_user_orig_system => 'PER',
688 p_user_orig_system_id => p_rec.person_id,
689 p_role_orig_system => 'POS',
690 p_role_orig_system_id => p_rec.position_id,
691 p_start_date => l_start_date,
692 p_expiration_date => l_end_date);
693 --
694 end if; --l_date_chk = CURRENT
695 --
696 -- if this is effective in a future date then defer calling propagate user
697 -- until the future date equals sysdate
698 --
699 --
700 if l_date_chk = 'FUTURE' then
701 --
702 hr_utility.set_location('Future Assignment ',40);
703 -- This is effective in the future date, Call_back routine must be called.
704 --
705 --
706 l_person_id_canonical := FND_NUMBER.number_to_canonical(p_rec.person_id);
707 l_assignment_id_canonical := FND_NUMBER.number_to_canonical(p_rec.assignment_id);
708 l_start_date_canonical := FND_DATE.date_to_canonical(p_rec.effective_start_date);
709 l_end_date_canonical := FND_DATE.date_to_canonical(p_rec.effective_end_date);
710 --
711 -- assign attributes and values for l_parameters.
712 --
713 --
714 wf_event.addparametertolist(
715 p_name => 'CONTEXT',
716 p_value => 'POSITION',
717 p_parameterlist => l_parameters);
718 wf_event.addparametertolist(
719 p_name => 'ASSIGNMENTID',
720 p_value => l_assignment_id_canonical,
721 p_parameterlist => l_parameters);
722 wf_event.addparametertolist(
723 p_name => 'STARTDATE',
724 p_value => l_start_date_canonical,
725 p_parameterlist => l_parameters);
726 wf_event.addparametertolist(
727 p_name => 'ENDDATE',
728 p_value => l_end_date_canonical,
729 p_parameterlist => l_parameters);
730
731 -- Call the routine wf_util.call_me_later ()
732 --
733 hr_utility.set_location('Calling WF routine call_me_later.... ',45);
734 WF_UTIL.call_me_later(p_callback => 'PER_HRWF_SYNCH.call_back',
735 p_when => trunc(p_rec.effective_start_date),
736 p_parameters => l_parameters);
737 --
738 end if;
739 --
740 hr_utility.set_location('Leaving '||l_proc,100);
741 --
742 end per_asg_wf;
743 --
744 --
745 -- ----------------------------------------------------------------------------
746 -- |-----------------------------< per_pds_wf >-------------------------------|
747 -- ----------------------------------------------------------------------------
748 --
749 procedure per_pds_wf(
750 p_rec in per_periods_of_service%rowtype,
751 p_date in date default null,
752 p_action in varchar2) is
753 --
754 l_proc varchar2(80) := g_package||'per_pds_wf';
755 l_date date;
756 l_perrole_date date;
757 l_parameters wf_parameter_list_t;
758 l_user_name wf_local_roles.name%type;
759 --
760 l_perrole_user_name wf_local_roles.name%type;
761 l_start_date_canonical varchar2(30);
762 l_end_date_canonical varchar2(30);
763 l_person_id_canonical varchar2(20);
764 l_description VARCHAR2(1000);
765 l_notificationPref VARCHAR2(8);
766 l_parentOrigSys VARCHAR2(240);
767 l_parentOrigSysID NUMBER;
768 --
769 -- cursors
770 --
771 cursor role_exists(p_person_id in number) is
772 select name
773 from wf_local_roles
774 where orig_system = 'PER'
775 and orig_system_id = p_person_id
776 and rownum = 1; -- This extra WHERE condition is added as per the request of "tpapired"
777 --
778 cursor perrole_role_exists (p_person_id in number) is
779 select name
780 from wf_local_roles
781 where orig_system = 'PER_ROLE'
782 and orig_system_id = p_person_id
783 and rownum = 1;
784 --
785 begin
786 --
787 hr_utility.set_location('Entering '||l_proc,10);
788 --
789 if p_action = 'TERMINATION' then
790 -- l_date := p_date;
791 l_date := p_date +(1-1/86400); -- bug 4773768
792 l_perrole_date := to_date('31-12-4712', 'DD-MM-YYYY');
793 hr_utility.set_location('Termination.. '||l_proc,20);
794 else
795 hr_utility.set_location('Reverse Termination.. '||l_proc,20);
796 --set the end of time as ATD
797 l_date := to_date('31-12-4712', 'DD-MM-YYYY');
798 -- set the status a ACTIVE
799 wf_event.addparametertolist('orclIsEnabled','ACTIVE',l_parameters); -- 4133057
800 end if;
801 --
802 --
803 -- if ROLE is already created, then assign USER_NAME to l_user_name.
804 --
805 open role_exists(p_rec.person_id);
806 fetch role_exists into l_user_name;
807 --
808 if role_exists%notfound then
809 l_user_name := 'PER:'||p_rec.person_id;
810 end if;
811 --
812 close role_exists;
813 --
814 wf_event.addparametertolist('orclWFOrigSystem','PER',l_parameters);
815 wf_event.addparametertolist('orclWFOrigSystemID',p_rec.person_id,l_parameters);
816 --l_user_name := 'PER:'||p_rec.person_id;
817 wf_event.addparametertolist('USER_NAME',l_user_name,l_parameters);
818 --
819 hr_utility.set_location('Calling Propagate_role '||l_proc,30);
820 wf_local_synch.propagate_role(
821 p_orig_system => 'PER',
822 p_orig_system_id => p_rec.person_id,
823 p_attributes => l_parameters,
824 p_start_date => p_rec.date_start,
825 p_expiration_date => l_date);
826
827 --
828 open perrole_role_exists(p_rec.person_id);
829 fetch perrole_role_exists into l_perrole_user_name;
830
831 if perrole_role_exists%notfound then
832 l_user_name := 'PER_ROLE:'||p_rec.person_id;
833 end if;
834 --
835 close perrole_role_exists;
836
837 if p_action = 'TERMINATION' then
838
839 if (trunc(nvl(p_date,p_rec.actual_termination_date)) < trunc(sysdate)) then
840
841 wf_event.addparametertolist('orclWFOrigSystem','PER_ROLE',l_parameters);
842 wf_event.addparametertolist('orclWFOrigSystemID',p_rec.person_id,l_parameters);
843 wf_event.addparametertolist('USER_NAME',l_user_name,l_parameters);
844
845 Begin
846 select description,
847 notification_preference,
848 orig_system,
849 orig_system_id
850 Into l_description,
851 l_notificationPref,
852 l_parentOrigSys,
853 l_parentOrigSysID
854 from per_role_roles_v
855 where name = 'PER_ROLE:'||p_rec.person_id;
856 Exception
857 When others then
858 l_description := null;
859 l_notificationPref := null;
860 l_parentOrigSys := null;
861 l_parentOrigSysID := null;
862 End;
863
864 wf_event.addparametertolist('DESCRIPTION',l_user_name,l_parameters);
865 wf_event.addparametertolist('ORCLWORKFLOWNOTIFICATIONPREF',l_notificationPref,l_parameters);
866 wf_event.addparametertolist('ORCLWFPARENTORIGSYS',l_parentOrigSys,l_parameters);
867 wf_event.addparametertolist('ORCLWFPARENTORIGSYSID',l_parentOrigSysID,l_parameters);
868
869
870 hr_utility.set_location('Calling Propagate_role for PER_ROLE role'||l_proc,50);
871 wf_local_synch.propagate_role( p_orig_system => 'PER',
872 p_orig_system_id => p_rec.person_id,
873 p_attributes => l_parameters,
874 p_start_date => p_rec.actual_termination_date+1,
875 p_expiration_date => l_perrole_date);
876 else
877 l_start_date_canonical := FND_DATE.date_to_canonical(p_rec.date_start);
878 l_end_date_canonical := FND_DATE.date_to_canonical(l_perrole_date);
879 l_person_id_canonical := FND_NUMBER.number_to_canonical(p_rec.person_id);
880
881 wf_event.addparametertolist(p_name => 'CONTEXT',
882 p_value => 'PERSON:TERMINATION',
883 p_parameterlist => l_parameters);
884
885 wf_event.addparametertolist(p_name => 'PERSON_ID',
886 p_value => l_person_id_canonical,
887 p_parameterlist => l_parameters);
888
889 wf_event.addparametertolist(p_name => 'STARTDATE',
890 p_value => l_start_date_canonical,
891 p_parameterlist => l_parameters);
892
893 wf_event.addparametertolist(p_name => 'ENDDATE',
894 p_value => l_end_date_canonical,
895 p_parameterlist => l_parameters);
896
897 hr_utility.set_location('Before calling call_me_later for PER_ROLE '||l_proc,50);
898 hr_utility.set_location('Before calling later:trunc(p_rec.actual_termination_date+1)='||trunc(p_rec.actual_termination_date+1)||l_proc,50);
899
900
901 wf_util.call_me_later(p_callback => 'per_hrwf_synch.call_back',
902 p_when => trunc(p_rec.actual_termination_date+1),
903 p_parameters => l_parameters);
904
905 End if;
906
907 End if;
908
909 hr_utility.set_location('Leaving '||l_proc,40);
910 --
911 end per_pds_wf;
912 --
913 -- --------------------------------------------------------------------------
914 -- |-----------------------------< per_per_wf >------------------------------|
915 -- --------------------------------------------------------------------------
916 procedure per_per_wf(
920 l_rec per_all_people_f%rowtype;
917 p_rec in per_per_shd.g_rec_type,
918 p_action in varchar2) is
919 --
921 --
922 l_party_id number(15,0);
923 --
924 cursor get_party_id(p_person_id in number) is
925 select party_id
926 from per_all_people_f
927 where person_id = p_rec.person_id;
928 begin
929 -- 3297591 - in case party_id has since been filled... is this needed?
930 if p_rec.party_id is null
931 then
932 open get_party_id(p_rec.person_id);
933 fetch get_party_id into l_party_id;
934 --
935 if get_party_id%notfound then
936 l_party_id := NULL;
937 end if;
938 else
939 l_party_id := p_rec.party_id;
940 end if;
941 -- 3297591 was that needed?
942 --
943 -- Transfering the argument values from per_per_shd.g_rec_type to
944 -- per_all_people_f%rowtype
945 --
946 l_rec.current_employee_flag := p_rec.current_employee_flag;
947 l_rec.current_npw_flag := p_rec.current_npw_flag;
948 l_rec.person_id := p_rec.person_id;
949 l_rec.party_id := l_party_id; -- 3297591
950 l_rec.effective_start_date := p_rec.effective_start_date;
951 l_rec.effective_end_date := p_rec.effective_end_date;
952 l_rec.full_name := p_rec.full_name;
953 l_rec.correspondence_language := p_rec.correspondence_language;
954 l_rec.email_address := p_rec.email_address;
955 --
956 -- Calling the actual procedure
957 per_per_wf(p_rec => l_rec,
958 p_action => p_action);
959
960 --
961 end per_per_wf;
962 --
963 -- --------------------------------------------------------------------------
964 -- |-----------------------------< per_pds_wf >------------------------------|
965 -- --------------------------------------------------------------------------
966 procedure per_pds_wf(
967 p_person_id in number,
968 p_date in date default null,
969 p_date_start in date,
970 p_action in varchar2) is
971 --
972 l_rec per_periods_of_service%rowtype;
973 --
974 begin
975 --
976 -- Transfering the argument values from local variables to
977 -- per_periods_of_service%rowtype
978 --
979 l_rec.person_id := p_person_id;
980 l_rec.date_start := p_date_start;
981 --
982 -- Calling the actual procedure
983 per_pds_wf(p_rec => l_rec,
984 p_date => p_date,
985 p_action => p_action);
986 --
987 end per_pds_wf;
988 --
989 procedure sych_user_pos_role (p_in_user_name in fnd_user.user_name%type,
990 p_in_person_id in per_all_people_f.person_id%type,
991 p_in_start_date in fnd_user.start_date%type,
992 p_in_exp_date in fnd_user.end_date%type)
993 is
994
995 l_user_name fnd_user.user_name%type;
996 srch_criteria_table srch_criteria;
997 srch_criteria_table2 srch_criteria;
998 l_fetch_rec_count number :=0;
999 l_fetch_loop_iter number :=0;
1000 l_filter_loop_iter number :=0;
1001 l_filter_row_count number :=0;
1002 j number :=0;
1003
1004 l_proc varchar2(200) := 'per_hrwf_synch.synch_user_pos_role.';
1005 Cursor Csr_assignment_data (p_in_person_id in number,p_in_start_date in date,p_in_exp_date in date)
1006 is
1007 Select *
1008 from per_all_assignments_f paaf
1009 where paaf.person_id = p_in_person_id
1010 and trunc(effective_end_date) >= trunc(p_in_start_date)
1011 and trunc(effective_start_date) <= trunc(nvl(p_in_exp_date,effective_start_date))
1012 order by effective_start_date asc;
1013
1014 Begin
1015
1016 hr_utility.set_location('Entering '||l_proc,10);
1017
1018 select count(*)
1019 into l_fetch_rec_count
1020 from per_all_assignments_f paaf
1021 where paaf.person_id = p_in_person_id
1022 and trunc(effective_end_date) >= greatest(trunc(p_in_start_date),trunc(sysdate) )
1023 and trunc(effective_start_date) <= trunc(nvl(p_in_exp_date,effective_start_date))
1024 order by effective_start_date asc;
1025
1026 l_fetch_loop_iter := 0 ;
1027
1028 For csr_ptr in Csr_assignment_data(p_in_person_id,p_in_start_date,p_in_exp_date)
1029 Loop
1030
1031 srch_criteria_table(l_fetch_loop_iter).p_person_id := csr_ptr.person_id;
1032 srch_criteria_table(l_fetch_loop_iter).p_assignment_id := csr_ptr.assignment_id;
1033 srch_criteria_table(l_fetch_loop_iter).p_effective_start_date := csr_ptr.effective_start_date;
1034 srch_criteria_table(l_fetch_loop_iter).p_effective_end_date := csr_ptr.effective_end_date;
1035 srch_criteria_table(l_fetch_loop_iter).p_position_id := csr_ptr.position_id;
1036
1037 l_fetch_loop_iter := l_fetch_loop_iter + 1;
1038
1039 End Loop;
1040
1041 --For (i=0 ; i<l_fetch_loop_iter-1 ; i++)
1042 For i in 0 .. l_fetch_loop_iter-1
1043 loop
1044 if (i=0)
1045 then
1046 srch_criteria_table2(i) := srch_criteria_table(i);
1047 j := i;
1048 else
1049 if ((srch_criteria_table(i).p_position_id = srch_criteria_table(i-1).p_position_id) and (srch_criteria_table(i).p_effective_start_date = srch_criteria_table(i-1).p_effective_end_date + 1))
1050 then
1051
1052 srch_criteria_table2(j).p_effective_end_date := srch_criteria_table(i).p_effective_end_date;
1053
1054 else
1055 j := j+1;
1056 srch_criteria_table2(j) := srch_criteria_table(i);
1057
1058 end if;
1059 end if;
1060
1061 l_filter_loop_iter := j;
1062
1063 end loop;
1064
1065 l_filter_row_count := srch_criteria_table2.count;
1066 --dbms_output.put_line('Starting of rows');
1067 for k in 0 .. l_filter_row_count-1
1068 loop
1069 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
1070 p_user_orig_system_id => srch_criteria_table2(k).p_person_id ,
1071 p_role_orig_system => 'POS',
1072 p_role_orig_system_id => srch_criteria_table2(k).p_position_id,
1073 p_start_date => greatest(srch_criteria_table2(k).p_effective_start_date,p_in_start_date) ,
1074 p_expiration_date => least(srch_criteria_table2(k).p_effective_end_date, nvl(p_in_exp_date,hr_general.end_of_time)) );
1075
1076 end loop;
1077 hr_utility.set_location('Entering '||l_proc,200);
1078 exception
1079 when others then
1080 --dbms_output.put_line(sqlerrm);
1081 raise;
1082 end sych_user_pos_role;
1083 ----------
1084 end per_hrwf_synch;