[Home] [Help]
PACKAGE BODY: APPS.PER_PQH_SHR
Source
1 Package Body per_pqh_shr as
2 /* $Header: pepqhshr.pkb 120.4.12020000.3 2013/04/19 02:43:38 pathota ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pqh_shr.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< position_control_enabled >----------------------|
12 -- ----------------------------------------------------------------------------
13 function POSITION_CONTROL_ENABLED(P_ORGANIZATION_ID NUMBER default null,
14 p_effective_date in date default sysdate,
15 p_assignment_id number default null) RETURN VARCHAR2 IS
16 --
17 l_business_group_id number;
18 l_org_structure_version_id number;
19 l_organization_id number;
20 l_pc_flag varchar2(10);
21 l_effective_date date := nvl(p_effective_date, sysdate);
22 --
23 cursor c2(p_business_group_id number, p_effective_date date) is
24 select ver.org_structure_version_id
25 from
26 per_org_structure_versions ver,
27 per_organization_structures str
28 where
29 ver.organization_structure_id = str.organization_structure_id
30 and str.business_group_id = p_business_group_id
31 and str.position_control_structure_flg='Y'
32 and nvl(p_effective_date, sysdate) between ver.date_from
33 and nvl(ver.date_to, hr_general.end_of_time);
34 --
35 --
36 cursor c_assignment(p_assignment_id number, p_effective_date date) is
37 select organization_id, business_group_id
38 from
39 per_all_assignments_f
40 where
41 assignment_id = p_assignment_id
42 and p_effective_date between effective_start_date and effective_end_date;
43 --
44 cursor c_organization(p_organization_id number) is
45 select business_group_id
46 from
47 hr_all_organization_units
48 where
49 organization_id = p_organization_id;
50 --
51 BEGIN
52 if p_organization_id is not null then
53 l_organization_id := p_organization_id;
54 open c_organization(p_organization_id);
55 fetch c_organization into l_business_group_id;
56 close c_organization;
57 elsif p_assignment_id is not null then
58 open c_assignment(p_assignment_id, l_effective_date);
59 fetch c_assignment into l_organization_id, l_business_group_id;
60 close c_assignment;
61 end if;
62 --
63 if l_organization_id is not null then
64 open c2(l_business_group_id, l_effective_date);
65 fetch c2 into l_org_structure_version_id;
66 close c2;
67 --
68 l_pc_flag := per_pqh_shr.POSITION_CONTROL_ENABLED(
69 p_org_structure_version_id => l_org_structure_version_id,
70 p_organization_id => l_organization_id,
71 p_business_group_id => l_business_group_id);
72 return l_pc_flag;
73 end if;
74 RETURN 'N';
75 END;
76 --
77 --
78 -- ---------------------------------------------------------------------------
79 -- |------------------------< open_status >-----------------------------|
80 -- ---------------------------------------------------------------------------
81 -- Description:
82 -- Retrieves the funded_status of the position.
83 --
84 function open_status
85 (p_position_id in number, p_effective_date in date) return varchar2 is
86 l_proc varchar2(72) := g_package||'open_status';
87 l_open_state varchar2(100):= 'OPEN';
88 begin
89 hr_utility.set_location('Entering:'||l_proc, 5);
90 --
91 -- fetch open state
92 --
93 l_open_state := pqh_psf_bus.open_status(p_position_id, p_effective_date);
94 --
95 hr_utility.set_location(' Leaving:'||l_proc, 10);
96 --
97 return l_open_state;
98 end;
99 --
100 -- ---------------------------------------------------------------------------
101 -- |------------------------< future approved actions >-----------------|
102 -- ---------------------------------------------------------------------------
103 -- Description:
104 -- Retrieves the future approved actions of the position.
105 --
106 function future_approved_actions
107 (p_position_id in number) return varchar2 is
108 l_proc varchar2(72) := g_package||'future_approved_actions';
109 l_pending_action_flag varchar2(100):= 'N';
110 begin
111 hr_utility.set_location('Entering:'||l_proc, 5);
112 --
113 -- fetch future approved actions flag
114 --
115 l_pending_action_flag := pqh_psf_bus.future_approved_actions(p_position_id);
116 --
117 hr_utility.set_location(' Leaving:'||l_proc, 10);
118 --
119 return l_pending_action_flag;
120 end;
121 --
122 --
123 -- ---------------------------------------------------------------------------
124 -- |----------------------< per_abv_insert_validate >-------------------|
125 -- ---------------------------------------------------------------------------
126 --
127 PROCEDURE per_abv_insert_validate(
128 p_assignment_id number,
129 p_value number,
130 p_unit varchar2,
131 p_effective_date date) is
132 --
133 l_proc varchar2(72) := g_package||'per_abv_insert_validate';
134 --
135 begin
136 hr_utility.set_location('Entering:'||l_proc, 5);
137 --
138 pqh_psf_bus.per_abv_insert_validate(
139 p_assignment_id =>p_assignment_id,
140 p_value =>p_value,
141 p_unit =>p_unit,
142 p_effective_date =>p_effective_date);
143 --
144 hr_utility.set_location(' Leaving:'||l_proc, 10);
145 end;
146 --
147 -- ---------------------------------------------------------------------------
148 -- |----------------------< per_abv_update_validate >-------------------|
149 -- ---------------------------------------------------------------------------
150 --
151 PROCEDURE per_abv_update_validate(
152 p_abv_id number,
153 p_assignment_id number,
154 p_value number,
155 p_unit varchar2,
156 p_effective_date date,
157 p_validation_start_date date,
158 p_validation_end_date date,
159 p_datetrack_mode varchar2) is
160 --
161 l_proc varchar2(72) := g_package||'per_abv_update_validate';
162 --
163 begin
164 hr_utility.set_location('Entering:'||l_proc, 5);
165 --
166 pqh_psf_bus.per_abv_update_validate(
167 p_abv_id =>p_abv_id,
168 p_assignment_id =>p_assignment_id,
169 p_value =>p_value,
170 p_unit =>p_unit,
171 p_effective_date =>p_effective_date,
172 p_validation_start_date =>p_validation_start_date,
173 p_validation_end_date =>p_validation_end_date,
174 p_datetrack_mode =>p_datetrack_mode);
175 --
176 hr_utility.set_location(' Leaving:'||l_proc, 10);
177 end;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |------------------------< hr_psf_bus >--------------------|
181 -- ----------------------------------------------------------------------------
182 --
183 Procedure hr_psf_bus(p_event varchar2, p_rec hr_psf_shd.g_rec_type
184 , p_effective_date date
185 , p_validation_start_date date
186 , p_validation_end_date date
187 , p_datetrack_mode varchar2) is
188 --
189 l_proc varchar2(72) := g_package||'hr_psf_bus';
190 --
191 begin
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 --
194 if p_event = 'INSERT_VALIDATE' then
195 pqh_psf_bus.hr_psf_bus_insert_validate(p_rec, p_effective_date);
196 elsif p_event = 'UPDATE_VALIDATE' then
197 pqh_psf_bus.hr_psf_bus_update_validate(p_rec, p_effective_date, p_validation_start_date,p_validation_end_date, p_datetrack_mode );
198 end if;
199 --
200 hr_utility.set_location(' Leaving:'||l_proc, 10);
201 end;
202 --
203 --
204 -- ----------------------------------------------------------------------------
205 -- |------------------------------< per_asg_bus >-----------------------------|
206 -- ----------------------------------------------------------------------------
207 --
208 Procedure per_asg_bus(p_event varchar2, p_rec per_asg_shd.g_rec_type
209 ,p_effective_date in date
210 ,p_validation_start_date in date
211 ,p_validation_end_date in date
212 ,p_datetrack_mode in varchar2) is
213 --
214 l_proc varchar2(72) := g_package||'per_asg_bus';
215 --
216 begin
217 hr_utility.set_location('Entering:'||l_proc, 5);
218 --
219 if p_event = 'INSERT_VALIDATE' then
220 pqh_psf_bus.per_asg_bus_insert_validate(p_rec, p_effective_date);
221 elsif p_event = 'UPDATE_VALIDATE' then
222 pqh_psf_bus.per_asg_bus_update_validate(p_rec,
223 p_effective_date,
224 p_validation_start_date,
225 p_validation_end_date,
226 p_datetrack_mode );
227 elsif p_event = 'DELETE_VALIDATE' then
228 pqh_psf_bus.per_asg_bus_delete_validate(
229 p_rec => p_rec
230 ,p_effective_date => p_effective_date
231 ,p_validation_start_date => p_validation_start_date
232 ,p_validation_end_date => p_validation_end_date
233 ,p_datetrack_mode => p_datetrack_mode);
234 end if;
235 --
236 hr_utility.set_location(' Leaving:'||l_proc, 10);
237 end;
238 --
239 --
240 -- ----------------------------------------------------------------------------
241 -- |------------------------------< hr_lei_bus >------------------------------|
242 -- ----------------------------------------------------------------------------
243 --
244 Procedure hr_lei_bus(p_event varchar2, p_rec hr_lei_shd.g_rec_type) is
245 --
246 l_proc varchar2(72) := g_package||'hr_psf_bus';
247 --
248 begin
249 hr_utility.set_location('Entering:'||l_proc, 5);
250 --
251 --
252 hr_utility.set_location(' Leaving:'||l_proc, 10);
253 end;
254 --
255 --
256 -- ----------------------------------------------------------------------------
257 -- |-----------------------------< hr_loc_bus >------------------------------|
258 -- ----------------------------------------------------------------------------
259 --
260 Procedure hr_loc_bus(p_event varchar2, p_rec hr_loc_shd.g_rec_type) is
261 --
262 l_proc varchar2(72) := g_package||'hr_psf_bus';
263 --
264 begin
265 hr_utility.set_location('Entering:'||l_proc, 5);
266 --
267 --
268 hr_utility.set_location(' Leaving:'||l_proc, 10);
269 end;
270 --
271 /*
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------------< hr_org_bus >------------------------------|
275 -- ----------------------------------------------------------------------------
276 --
277 Procedure hr_oru_bus(p_event varchar2, p_rec hr_oru_shd.g_rec_type) is
278 --
279 l_proc varchar2(72) := g_package||'hr_psf_bus';
280 --
281 begin
282 hr_utility.set_location('Entering:'||l_proc, 5);
283 --
284 --
285 hr_utility.set_location(' Leaving:'||l_proc, 10);
286 end;
287 --
288 */
289 --
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------------< pe_aei_bus >------------------------------|
292 -- ----------------------------------------------------------------------------
293 --
294 Procedure pe_aei_bus(p_event varchar2, p_rec pe_aei_shd.g_rec_type) is
295 --
296 l_proc varchar2(72) := g_package||'hr_psf_bus';
297 --
298 begin
299 hr_utility.set_location('Entering:'||l_proc, 5);
300 --
301 --
302 hr_utility.set_location(' Leaving:'||l_proc, 10);
303 end;
304 --
305 --
306 -- ----------------------------------------------------------------------------
307 -- |-----------------------------< pe_pei_bus >------------------------------|
308 -- ----------------------------------------------------------------------------
309 --
310 Procedure pe_pei_bus(p_event varchar2, p_rec pe_pei_shd.g_rec_type) is
311 --
312 l_proc varchar2(72) := g_package||'hr_psf_bus';
313 --
314 begin
315 hr_utility.set_location('Entering:'||l_proc, 5);
316 --
317 --
318 hr_utility.set_location(' Leaving:'||l_proc, 10);
319 end;
320 --
321 --
322 -- ----------------------------------------------------------------------------
323 -- |-----------------------------< pe_per_bus >------------------------------|
324 -- ----------------------------------------------------------------------------
325 --
326 Procedure per_per_bus(p_event varchar2, p_rec per_per_shd.g_rec_type) is
327 --
328 l_proc varchar2(72) := g_package||'hr_psf_bus';
329 --
330 begin
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 10);
335 end;
336 --
337 --
338 -- ----------------------------------------------------------------------------
339 -- |-----------------------------< per_apl_bus >------------------------------|
340 -- ----------------------------------------------------------------------------
341 --
342 Procedure per_apl_bus(p_event varchar2, p_rec per_apl_shd.g_rec_type) is
343 --
344 l_proc varchar2(72) := g_package||'hr_psf_bus';
345 --
346 begin
347 hr_utility.set_location('Entering:'||l_proc, 5);
348 --
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 end;
352 --
353 --
354 -- ----------------------------------------------------------------------------
355 -- |-----------------------------< pe_jei_bus >------------------------------|
356 -- ----------------------------------------------------------------------------
357 --
358 Procedure pe_jei_bus(p_event varchar2, p_rec pe_jei_shd.g_rec_type) is
359 --
360 l_proc varchar2(72) := g_package||'hr_psf_bus';
361 --
362 begin
363 hr_utility.set_location('Entering:'||l_proc, 5);
364 --
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 10);
367 end;
368 --
369 --
370 -- ----------------------------------------------------------------------------
371 -- |-----------------------------< pe_poi_bus >------------------------------|
372 -- ----------------------------------------------------------------------------
373 --
374 Procedure pe_poi_bus(p_event varchar2, p_rec pe_poi_shd.g_rec_type) is
375 --
376 l_proc varchar2(72) := g_package||'hr_psf_bus';
377 --
378 begin
379 hr_utility.set_location('Entering:'||l_proc, 5);
380 --
381 --
382 hr_utility.set_location(' Leaving:'||l_proc, 10);
383 end;
384 --
385 --
386 -- ----------------------------------------------------------------------------
387 -- |-----------------------------< per_job_bus >------------------------------|
388 -- ----------------------------------------------------------------------------
389 --
390 Procedure per_job_bus(p_event varchar2, p_rec per_job_shd.g_rec_type) is
391 --
392 l_proc varchar2(72) := g_package||'hr_psf_bus';
393 --
394 begin
395 hr_utility.set_location('Entering:'||l_proc, 5);
396 --
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 10);
399 end;
400 --
401 --
402 -- ----------------------------------------------------------------------------
403 -- |-----------------------------< per_dpf_bus >------------------------------|
404 -- ----------------------------------------------------------------------------
405 --
406 Procedure per_dpf_bus(p_event varchar2, p_rec per_dpf_shd.g_rec_type) is
407 --
408 l_proc varchar2(72) := g_package||'hr_psf_bus';
409 --
410 begin
411 hr_utility.set_location('Entering:'||l_proc, 5);
412 --
413 --
414 hr_utility.set_location(' Leaving:'||l_proc, 10);
415 end;
416 --
417 --
418 -- ----------------------------------------------------------------------------
419 -- |-----------------------------< per_pse_bus >------------------------------|
420 -- ----------------------------------------------------------------------------
421 --
422 Procedure per_pse_bus(p_event varchar2, p_rec per_pse_shd.g_rec_type) is
423 --
424 l_proc varchar2(72) := g_package||'hr_psf_bus';
425 --
426 begin
427 hr_utility.set_location('Entering:'||l_proc, 5);
428 --
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 10);
431 end;
432 --
433 --
434 -- ----------------------------------------------------------------------------
435 -- |-----------------------------< per_jbr_bus >------------------------------|
436 -- ----------------------------------------------------------------------------
437 --
438 Procedure per_jbr_bus(p_event varchar2, p_rec per_jbr_shd.g_rec_type) is
439 --
440 l_proc varchar2(72) := g_package||'hr_psf_bus';
441 --
442 begin
443 hr_utility.set_location('Entering:'||l_proc, 5);
444 --
445 --
446 hr_utility.set_location(' Leaving:'||l_proc, 10);
447 end;
448 --
449 --
450 -- ----------------------------------------------------------------------------
451 -- |-----------------------------< per_vgr_bus >------------------------------|
452 -- ----------------------------------------------------------------------------
453 --
454 Procedure per_vgr_bus(p_event varchar2, p_rec per_vgr_shd.g_rec_type) is
455 --
456 l_proc varchar2(72) := g_package||'hr_psf_bus';
457 --
458 begin
459 hr_utility.set_location('Entering:'||l_proc, 5);
460 --
461 --
462 hr_utility.set_location(' Leaving:'||l_proc, 10);
463 end;
464 --
465 --
466 -- ----------------------------------------------------------------------------
467 -- |-----------------------------< per_ose_bus >------------------------------|
468 -- ----------------------------------------------------------------------------
469 --
470 Procedure per_ose_bus(p_event varchar2, p_rec per_ose_shd.g_rec_type) is
471 --
472 l_proc varchar2(72) := g_package||'hr_psf_bus';
473 --
474 begin
475 hr_utility.set_location('Entering:'||l_proc, 5);
476 --
477 --
478 hr_utility.set_location(' Leaving:'||l_proc, 10);
479 end;
480 --
481 --
482 --
483 function POSITION_CONTROL_ENABLED(p_org_structure_version_id number,
484 p_organization_id number,
485 p_business_group_id number) RETURN VARCHAR2 IS
486 --
487 CURSOR C1(p_org_structure_version_id number, P_ORGANIZATION_ID NUMBER, p_business_group_id number) IS
488 SELECT level, POSITION_CONTROL_ENABLED_FLAG
489 FROM
490 PER_ORG_STRUCTURE_ELEMENTS A
491 where
492 a.business_group_id = p_business_group_id
493 start with organization_id_child = P_ORGANIZATION_ID
494 and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
495 connect by organization_id_child = prior organization_id_parent
496 and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
497 --
498 CURSOR C2(p_org_structure_version_id number) IS
499 select organization_id_parent organization_id
500 from PER_ORG_STRUCTURE_ELEMENTS
501 where ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
502 minus
503 select organization_id_child organization_id
504 from PER_ORG_STRUCTURE_ELEMENTS
505 where ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
506 --
507 cursor c3(p_org_structure_version_id number) is
508 select nvl(osv.topnode_pos_ctrl_enabled_flag,'N')
509 from per_org_structure_versions osv
510 where osv.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
511 --
512 l_org_pc_enabled_null boolean := false;
513 l_top_org_id number;
514 l_pc_enabled_flag varchar2(10);
515 --
516 BEGIN
517 if p_organization_id is not null and p_org_structure_version_id is not null
518 and p_business_group_id is not null then
519 --
520 FOR R_C1 IN C1(p_org_structure_version_id, P_ORGANIZATION_ID, p_business_group_id) LOOP
521 IF R_C1.POSITION_CONTROL_ENABLED_FLAG IS NOT NULL THEN
522 RETURN R_C1.POSITION_CONTROL_ENABLED_FLAG;
523 else
524 l_org_pc_enabled_null := true;
525 END IF;
526 END LOOP;
527 open c3(p_org_structure_version_id);
528 fetch c3 into l_pc_enabled_flag;
529 close c3;
530 if l_org_pc_enabled_null then
531 return l_pc_enabled_flag;
532 end if;
533 open C2(p_org_structure_version_id);
534 fetch c2 into l_top_org_id;
535 close c2;
536 if l_top_org_id = p_organization_id then
537 return l_pc_enabled_flag;
538 end if;
539 end if;
540 RETURN 'N';
541 END;
542
543 -- ----------------------------------------------------------------------------
544 -- |------------------------------< mysynch_routine>-----------------------------|
545 -- ----------------------------------------------------------------------------
546 --
547
548 Procedure mysynch_routine(my_parms in wf_parameter_list_t) is
549
550 l_user_orig_system Varchar2(40);
551 l_user_orig_system_id Number;
552 l_user_orig_system_id_can Varchar2(40);
553 l_role_orig_system Varchar2(40);
554 l_role_orig_system_id Number;
555 l_role_orig_system_id_can Varchar2(40);
556 l_start_date Date;
557 l_start_date_can Varchar2(40);
558 l_expiration_date Date;
559 l_expiration_date_can Varchar2(40);
560 l_entity Varchar2(40);
561 l_pos_id Number;
562 l_event Varchar2(40);
563 l_datetrack_mode Varchar2(40);
564 l_assignment_id Number;
565 l_assignment_id_can Varchar2(40);
566 l_user_name fnd_user.user_name%type;
567 l_old_position_id_can Varchar2(40);
568 l_old_position_id hr_all_positions_f.position_id%type;
569
570
571 l_proc Varchar2(100) := g_package||'.mysynch_routine';
572
573 Cursor C_Pos_Per_Exist(p_position_id in number,p_start_date in date, p_end_date in date)
574 is
575 Select assignment_id
576 from per_all_assignments_f
577 where position_id = p_position_id
578 and effective_start_date = p_start_date
579 and effective_end_date = p_end_date;
580
581 Begin
582 hr_utility.set_location('Entered '||l_proc,10);
583
584 l_entity := wf_event.getvalueforparameter( p_name => 'CONTEXT',
585 p_parameterlist => my_parms);
586
587 l_event := wf_event.getvalueforparameter( p_name => 'EVENT',
588 p_parameterlist => my_parms);
589
590 l_datetrack_mode := wf_event.getvalueforparameter( p_name => 'DATETRACK_MODE',
591 p_parameterlist => my_parms);
592
593 l_assignment_id_can := wf_event.getvalueforparameter( p_name => 'ASSIGNMENT_ID',
594 p_parameterlist => my_parms);
595
596 l_assignment_id := fnd_number.canonical_to_number(l_assignment_id_can);
597
598 l_user_orig_system := wf_event.getvalueforparameter( p_name => 'USER_ORIG_SYSTEM',
599 p_parameterlist => my_parms);
600
601 l_user_orig_system_id_can := wf_event.getvalueforparameter( p_name => 'USER_ORIG_SYSTEM_ID',
602 p_parameterlist => my_parms);
603
604 l_user_orig_system_id := fnd_number.canonical_to_number(l_user_orig_system_id_can);
605
606 l_role_orig_system := wf_event.getvalueforparameter( p_name => 'ROLE_ORIG_SYSTEM',
607 p_parameterlist => my_parms);
608
609 l_role_orig_system_id_can := wf_event.getvalueforparameter( p_name => 'ROLE_ORIG_SYSTEM_ID',
610 p_parameterlist => my_parms);
611
612 l_old_position_id_can := wf_event.getvalueforparameter( p_name => 'OLD_POSITION_ID',
613 p_parameterlist => my_parms);
614
615 l_old_position_id := fnd_number.canonical_to_number(l_old_position_id_can);
616
617 l_user_name := wf_event.getvalueforparameter( p_name => 'USER_NAME',
618 p_parameterlist => my_parms);
619
620 l_role_orig_system_id := fnd_number.canonical_to_number(l_role_orig_system_id_can);
621
622 l_start_date_can := wf_event.getvalueforparameter( p_name => 'START_DATE',
623 p_parameterlist => my_parms);
624
625 l_start_date := fnd_date.canonical_to_date(l_start_date_can);
626
627 l_expiration_date_can := wf_event.getvalueforparameter( p_name => 'EXPIRATION_DATE',
628 p_parameterlist => my_parms);
629
630 l_expiration_date := fnd_date.canonical_to_date(l_expiration_date_can);
631
632
633 hr_utility.set_location(l_proc||'l_entity ='||l_entity,40);
634 hr_utility.set_location(l_proc||'l_event ='||l_event,50);
635 hr_utility.set_location(l_proc||'l_datetrack_mode ='||l_datetrack_mode,60);
636 hr_utility.set_location(l_proc||'l_assignment_id ='||l_assignment_id,70);
637 hr_utility.set_location(l_proc||'l_user_orig_system ='||l_user_orig_system,80);
638 hr_utility.set_location(l_proc||'l_user_orig_system_id ='||l_user_orig_system_id,90);
639 hr_utility.set_location(l_proc||'l_role_orig_system ='||l_role_orig_system,100);
640 hr_utility.set_location(l_proc||'l_role_orig_system_id ='||l_role_orig_system_id,110);
641 hr_utility.set_location(l_proc||'l_old_position_id ='||l_old_position_id,120);
642 hr_utility.set_location(l_proc||'l_user_name ='||l_user_name,130);
643 hr_utility.set_location(l_proc||'l_start_date ='||l_start_date,140);
644 hr_utility.set_location(l_proc||'l_expiration_date ='||l_expiration_date,150);
645
646 open C_Pos_Per_Exist(l_role_orig_system_id,l_start_date, l_expiration_date);
647 fetch C_Pos_Per_Exist into l_pos_id;
648
649 if C_Pos_Per_Exist%notfound then
650 close C_Pos_Per_Exist;
651 else
652 hr_utility.set_location(l_proc,160);
653 if l_datetrack_mode = 'CORRECTION' then
654
655 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => l_user_orig_system,
656 p_user_orig_system_id => l_user_orig_system_id,
657 p_role_orig_system => l_role_orig_system,
658 p_role_orig_system_id => l_role_orig_system_id,
659 p_start_date => hr_general.end_of_time,
660 p_expiration_date => hr_general.end_of_time);
661 hr_utility.set_location(l_proc,170);
662 else
663
664 /* Below call to Wf_Directory.DeleteUserRole is to deleted already end-dated old POS - PER relation.
665 After deleting the old one, we can create the new relation with new POS */
666 hr_utility.set_location(l_proc,180);
667 /*Wf_Directory.DeleteUserRole ( p_username => l_user_name,
668 p_rolename => 'POS'|| ':' || l_old_position_id,
669 p_userOrigSystem => 'PER',
670 p_userOrigSystemID => l_user_orig_system_id,
671 p_roleOrigSystem => 'POS',
672 p_roleOrigSystemID => l_old_position_id);*/
673
674 /* Wf_Directory.RemoveUsersFromAdHocRole(role_name => 'POS'|| ':' || l_old_position_id,
675 role_users => l_user_name);*/
676 hr_utility.set_location(l_proc,190);
677 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => l_user_orig_system,
678 p_user_orig_system_id => l_user_orig_system_id,
679 p_role_orig_system => l_role_orig_system,
680 p_role_orig_system_id => l_role_orig_system_id,
681 p_start_date => l_start_date,
682 p_expiration_date => l_expiration_date);
683 hr_utility.set_location(l_proc,200);
684 end if;
685 Close C_Pos_Per_Exist;
686 end if;
687 hr_utility.set_location('Leaving - '||l_proc,160);
688 End mysynch_routine;
689
690 --
691 --
692 -- ----------------------------------------------------------------------------
693 -- |------------------------------< per_asg_wf_sync>-----------------------------|
694 -- ----------------------------------------------------------------------------
695 --
696 Procedure per_asg_wf_sync(p_event varchar2, p_rec per_asg_shd.g_rec_type
697 ,p_old_position_id in number default null
698 ,p_effective_date in date
699 ,p_validation_start_date in date
700 ,p_validation_end_date in date
701 ,p_datetrack_mode in varchar2) is
702 --
703 myparms wf_parameter_list_t;
704 l_future_change boolean;
705 l_position_id number;
706 l_assignment_id varchar2(15);
707 l_old_position_id number;
708 l_old_pos_id number;
709 l_future_date date;
710 l_proc varchar2(30) := g_package||'per_asg_wf_sync';
711 l_start_date date;
712 l_expiration_date date;
713 l_effective_start_date date;
714 l_assg_start_date date;
715 l_assg_end_date date;
716 cnt number;
717 l_cnt number;
718
719 l_current_date date := trunc(sysdate);
720 l_position_id_canonical varchar2(40);
721 l_assignment_id_canonical varchar2(40);
722 l_assg_start_date_canonical varchar2(40);
723 l_assg_end_date_canonical varchar2(40);
724 l_user_name varchar2(40);
725 l_old_position_id_can varchar2(40);
726 l_per_id_can varchar2(40);
727 l_plist wf_parameter_list_t;
728 l_usr_strt_date date;
729 l_usr_end_date date;
730 l_eff_strt_date date;
731 l_eff_end_date date;
732
733 /*cursor get_eff_st_date(c_pos_id number) is
734 select min(effective_start_date) into l_effective_start_date
735 from per_all_assignments_f
736 where assignment_id = l_assignment_id
737 and assignment_type = 'E'
738 and position_id = c_pos_id;*/
739
740 cursor get_eff_st_date(c_pos_id in number ,c_curr_start_date in date ) is
741 select min(effective_start_date) into l_effective_start_date
742 from per_all_assignments_f
743 where assignment_id = l_assignment_id
744 and assignment_type = 'E'
745 and position_id = c_pos_id
746 and effective_start_date <= c_curr_start_date
747 and effective_start_date > nvl((select max(effective_start_date)
748 from per_all_assignments_f
749 where assignment_id = l_assignment_id
750 and position_id not in (c_pos_id)
751 and effective_start_date < c_curr_start_date),(effective_start_date-1));
752
753 cursor get_eff_st_date2(c_pos_id in number , c_assg_start_date in date) is
754 select min(effective_start_date) into l_effective_start_date
755 from per_all_assignments_f
756 where assignment_id = l_assignment_id
757 and assignment_type = 'E'
758 and position_id = c_pos_id
759 and effective_start_date > nvl((select max(effective_start_date)
760 from per_all_assignments_f
761 where assignment_id = l_assignment_id
762 and position_id not in (c_pos_id)
763 and effective_start_date < c_assg_start_date),(effective_start_date-1));
764
765 cursor get_user_name (p_person_id in number,p_effective_start_date in date,p_old_position_id in number)
766 is
767 SELECT usr.user_name,usr.start_date,end_date
768 FROM per_all_assignments_f ass
769 , fnd_user usr
770 WHERE ass.person_id = usr.employee_id
771 and ass.person_id = p_person_id
772 --AND p_effective_start_date BETWEEN usr.start_date AND nvl (usr.end_date, p_effective_start_date)
773 AND ass.position_id IS NOT NULL
774 AND p_effective_start_date BETWEEN ass.effective_start_date AND ass.effective_end_date
775 AND ass.assignment_type IN ('E', 'C')
776 AND ass.position_id = p_old_position_id;
777
778
779 begin
780 hr_utility.set_location('Entering:'||l_proc, 5);
781 --
782 l_position_id := p_rec.position_id;
783 l_assignment_id := p_rec.assignment_id;
784 l_old_position_id := nvl(per_asg_shd.g_old_rec.position_id,p_old_position_id);
785 l_assg_start_date := p_rec.effective_start_date;
786 l_assg_end_date := p_rec.effective_end_date;
787 l_old_pos_id := p_old_position_id;
788 --
789 -- Check position budgeted amount
790 --
791 if (p_event in ('POST_INSERT', 'POST_UPDATE')) then
792 if (p_rec.assignment_type = 'E') then
793 if (l_position_id <> nvl(l_old_position_id,-1)) then
794 pqh_psf_bus.chk_position_budget(
795 p_assignment_id => l_assignment_id,
796 p_effective_date => p_effective_date,
797 p_called_from => 'ASG',
798 p_old_position_id => l_old_position_id,
799 p_new_position_id => l_position_id);
800 end if;
801 end if;
802 end if;
803 --
804 -- Assignment WF Synchronize
805 --
806 -- fix for bug 8945335 starts here
807 -- if condn added to do wf synch only for E or C assignments (not for A)
808 if (p_rec.assignment_type = 'E') or (p_rec.assignment_type = 'C')
809 then
810 -- fix for bug 8945335 ends here
811 if l_old_pos_id is not null then
812
813 if p_datetrack_mode = 'CORRECTION' then
814
815 open get_eff_st_date(l_position_id ,l_assg_start_date);
816 fetch get_eff_st_date into l_effective_start_date;
817 if get_eff_st_date%notfound then
818 close get_eff_st_date;
819 open get_eff_st_date2(l_position_id,l_assg_start_date);
820 fetch get_eff_st_date into l_effective_start_date;
821 close get_eff_st_date2;
822 end if;
823
824 Open get_user_name (p_rec.person_id,l_effective_start_date,l_position_id);
825 fetch get_user_name into l_user_name,l_usr_strt_date,l_usr_end_date;
826 Close get_user_name;
827
828 l_effective_start_date := greatest(l_effective_start_date,l_usr_strt_date);
829 If ( trunc(p_rec.effective_start_date) < trunc(sysdate) and trunc(p_rec.effective_end_date) < trunc(sysdate) )
830 then null;
831 elsif (trunc(p_rec.effective_start_date) <= trunc(sysdate) and trunc(p_rec.effective_end_date) >= trunc(sysdate))
832 then
833 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
834 p_user_orig_system_id => p_rec.person_id,
835 p_role_orig_system => 'POS',
836 p_role_orig_system_id => l_old_pos_id,
837 p_start_date => greatest(p_rec.effective_start_date,l_usr_strt_date),
838 p_expiration_date => least(p_rec.effective_end_date,nvl(l_usr_end_date,hr_general.end_of_time)));
839
840 elsif (trunc(p_rec.effective_start_date) > trunc(sysdate) and trunc(p_rec.effective_end_date) >= trunc(sysdate))
841 then
842
843 l_per_id_can := fnd_number.number_to_canonical(p_rec.person_id);
844 l_assignment_id_canonical := fnd_number.number_to_canonical(l_assignment_id);
845 l_position_id_canonical := fnd_number.number_to_canonical(l_position_id);
846 l_assg_start_date_canonical := fnd_date.date_to_canonical(greatest(p_rec.effective_start_date,l_usr_strt_date));
847 l_assg_end_date_canonical := fnd_date.date_to_canonical(least(p_rec.effective_end_date,nvl(l_usr_end_date,hr_general.end_of_time)));
848
849
850 wf_event.addparametertolist(p_name => 'CONTEXT',
851 p_value => 'POSITION',
852 p_parameterlist => l_plist);
853
854 wf_event.addparametertolist(p_name => 'EVENT',
855 p_value => p_event,
856 p_parameterlist => l_plist);
857
858 wf_event.addparametertolist(p_name => 'DATETRACK_MODE',
859 p_value => p_datetrack_mode,
860 p_parameterlist => l_plist);
861
862 wf_event.addparametertolist(p_name => 'ASSIGNMENT_ID',
863 p_value => l_assignment_id_canonical,
864 p_parameterlist => l_plist);
865
866 wf_event.addparametertolist(p_name => 'USER_ORIG_SYSTEM',
867 p_value => 'PER',
868 p_parameterlist => l_plist);
869
870 wf_event.addparametertolist(p_name => 'USER_ORIG_SYSTEM_ID',
871 p_value => l_per_id_can,
872 p_parameterlist => l_plist);
873
874 wf_event.addparametertolist(p_name => 'ROLE_ORIG_SYSTEM',
875 p_value => 'POS',
876 p_parameterlist => l_plist);
877
878 wf_event.addparametertolist(p_name => 'ROLE_ORIG_SYSTEM_ID',
879 p_value => l_position_id_canonical,
880 p_parameterlist => l_plist);
881
882 wf_event.addparametertolist(p_name => 'OLD_POSITION_ID',
883 p_value => l_old_position_id_can,
884 p_parameterlist => l_plist);
885
886 wf_event.addparametertolist(p_name => 'USER_NAME',
887 p_value => l_user_name,
888 p_parameterlist => l_plist);
889
890 wf_event.addparametertolist(p_name => 'START_DATE',
891 p_value => l_assg_start_date_canonical,
892 p_parameterlist => l_plist);
893
894 wf_event.addparametertolist(p_name => 'EXPIRATION_DATE',
895 p_value => l_assg_end_date_canonical,
896 p_parameterlist => l_plist);
897 hr_utility.set_location(l_proc,96);
898 wf_util.call_me_later(p_callback => 'per_pqh_shr.mysynch_routine',
899 p_when => trunc(l_assg_start_date),
900 p_parameters => l_plist);
901
902 end if;
903 else
904 open get_eff_st_date(l_old_pos_id, l_assg_start_date);
905 fetch get_eff_st_date into l_effective_start_date;
906 if get_eff_st_date%notfound then
907 close get_eff_st_date;
908 open get_eff_st_date2(l_old_pos_id,l_assg_start_date);
909 fetch get_eff_st_date into l_effective_start_date;
910 close get_eff_st_date2;
911 end if;
912
913 Open get_user_name (p_rec.person_id,l_effective_start_date,l_old_pos_id);
914 fetch get_user_name into l_user_name,l_usr_strt_date,l_usr_end_date;
915 Close get_user_name;
916
917 l_effective_start_date := greatest(l_effective_start_date,l_usr_strt_date);
918
919 --close get_eff_st_date;
920 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
921 p_user_orig_system_id => p_rec.person_id,
922 p_role_orig_system => 'POS',
923 p_role_orig_system_id => l_old_pos_id,
924 p_start_date => l_effective_start_date,
925 p_expiration_date => l_assg_start_date-1);
926
927 if l_assg_start_date <= l_current_date -- to check if the start date of the new relation is in the past or future
928 then -- below call is to synch user role as the start date is in the past
929
930 hr_utility.set_location(l_proc||'To Delete previous user role',86);
931 /* Wf_Directory.DeleteUserRole ( p_username => l_user_name,
932 p_rolename => 'POS'|| ':' || l_old_pos_id,
933 p_userOrigSystem => 'PER',
934 p_userOrigSystemID => p_rec.person_id,
935 p_roleOrigSystem => 'POS',
936 p_roleOrigSystemID => l_old_pos_id);*/
937
938 /*Wf_Directory.RemoveUsersFromAdHocRole(role_name => 'POS'|| ':' || l_old_pos_id,
939 role_users => l_user_name);*/
940
941 hr_utility.set_location(l_proc||'Deleted pervious user role',87);
942 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
943 p_user_orig_system_id => p_rec.person_id,
944 p_role_orig_system => 'POS',
945 p_role_orig_system_id => l_position_id,
946 p_start_date => l_assg_start_date,
947 p_expiration_date => least(l_assg_end_date,nvl(l_usr_end_date,hr_general.end_of_time)));
948 else -- below call is to queue and call back later on appropriate date as the start date is in the future
949 hr_utility.set_location(l_proc,95);
950 l_per_id_can := fnd_number.number_to_canonical(p_rec.person_id);
951 l_assignment_id_canonical := fnd_number.number_to_canonical(l_assignment_id);
952 l_position_id_canonical := fnd_number.number_to_canonical(l_position_id);
953 l_assg_start_date_canonical := fnd_date.date_to_canonical(greatest(l_effective_start_date,l_usr_strt_date));
954 l_assg_end_date_canonical := fnd_date.date_to_canonical(least(l_assg_end_date,l_usr_end_date));
955 l_old_position_id_can := fnd_number.number_to_canonical(l_old_pos_id);
956
957 wf_event.addparametertolist(p_name => 'CONTEXT',
958 p_value => 'POSITION',
959 p_parameterlist => l_plist);
960
961 wf_event.addparametertolist(p_name => 'EVENT',
962 p_value => p_event,
963 p_parameterlist => l_plist);
964
965 wf_event.addparametertolist(p_name => 'DATETRACK_MODE',
966 p_value => p_datetrack_mode,
967 p_parameterlist => l_plist);
968
969 wf_event.addparametertolist(p_name => 'ASSIGNMENT_ID',
970 p_value => l_assignment_id_canonical,
971 p_parameterlist => l_plist);
972
973 wf_event.addparametertolist(p_name => 'USER_ORIG_SYSTEM',
974 p_value => 'PER',
975 p_parameterlist => l_plist);
976
977 wf_event.addparametertolist(p_name => 'USER_ORIG_SYSTEM_ID',
978 p_value => l_per_id_can,
979 p_parameterlist => l_plist);
980
981 wf_event.addparametertolist(p_name => 'ROLE_ORIG_SYSTEM',
982 p_value => 'POS',
983 p_parameterlist => l_plist);
984
985 wf_event.addparametertolist(p_name => 'ROLE_ORIG_SYSTEM_ID',
986 p_value => l_position_id_canonical,
987 p_parameterlist => l_plist);
988
989 wf_event.addparametertolist(p_name => 'OLD_POSITION_ID',
990 p_value => l_old_position_id_can,
991 p_parameterlist => l_plist);
992
993 wf_event.addparametertolist(p_name => 'USER_NAME',
994 p_value => l_user_name,
995 p_parameterlist => l_plist);
996
997 wf_event.addparametertolist(p_name => 'START_DATE',
998 p_value => l_assg_start_date_canonical,
999 p_parameterlist => l_plist);
1000
1001 wf_event.addparametertolist(p_name => 'EXPIRATION_DATE',
1002 p_value => l_assg_end_date_canonical,
1003 p_parameterlist => l_plist);
1004 hr_utility.set_location(l_proc,96);
1005 wf_util.call_me_later(p_callback => 'per_pqh_shr.mysynch_routine',
1006 p_when => trunc(l_assg_start_date),
1007 p_parameters => l_plist);
1008
1009 end if;
1010
1011 end if;
1012 else
1013 if l_position_id is not null then
1014 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => 'PER',
1015 p_user_orig_system_id => p_rec.person_id,
1016 p_role_orig_system => 'POS',
1017 p_role_orig_system_id => l_position_id,
1018 p_start_date => l_assg_start_date,
1019 p_expiration_date => l_assg_end_date);
1020 end if;
1021 -- fix for bug 8945335 starts here
1022 end if;
1023 end if;
1024 -- fix for bug 8945335 ends here
1025 --
1026 hr_utility.set_location('Leaving:'||l_proc, 5);
1027
1028 end per_asg_wf_sync;
1029 --
1030 -- ----------------------------------------------------------------------------
1031 -- |---------------------------< my_synch_routine >---------------------------|
1032 -- ----------------------------------------------------------------------------
1033 --
1034 procedure my_synch_routine(mykey in varchar2) is
1035 --
1036 cursor c_person(p_assignment_id varchar2) is
1037 SELECT USR.USER_NAME USER_NAME,
1038 'PER' USER_ORIG_SYSTEM,
1039 PER.PERSON_ID USER_ORIG_SYSTEM_ID,
1040 'POS'||':'||POS.POSITION_ID ROLE_NAME,
1041 'POS' ROLE_ORIG_SYSTEM,
1042 POS.POSITION_ID ROLE_ORIG_SYSTEM_ID,
1043 PER.FULL_NAME USER_DISPLAY_NAME,
1044 'QUERY' NOTIFICATION_PREFERENCE,
1045 FNDL.NLS_LANGUAGE LANGUAGE,
1046 FNDT.NLS_TERRITORY TERRITORY,
1047 PER.EMAIL_ADDRESS EMAIL_ADDRESS,
1048 NULL FAX,
1049 'ACTIVE' STATUS
1050 from PER_ALL_ASSIGNMENTS_F ASS,
1051 PER_ALL_POSITIONS POS,
1052 FND_USER USR,
1053 PER_ALL_PEOPLE_F PER,
1054 FND_TERRITORIES FNDT,
1055 FND_LANGUAGES FNDL,
1056 HR_LOCATIONS HRL
1057 where ASS.ASSIGNMENT_ID = P_ASSIGNMENT_ID
1058 and ASS.POSITION_ID = POS.POSITION_ID
1059 and ASS.PERSON_ID = USR.EMPLOYEE_ID
1060 and ASS.PERSON_ID = PER.PERSON_ID
1061 and trunc(sysdate) between PER.EFFECTIVE_START_DATE
1062 and PER.EFFECTIVE_END_DATE
1063 and trunc(sysdate) between ASS.EFFECTIVE_START_DATE
1064 and ASS.EFFECTIVE_END_DATE
1065 and trunc(sysdate) between USR.START_DATE
1066 and nvl(USR.END_DATE, sysdate+1)
1067 and PER.EMPLOYEE_NUMBER is not null
1068 and ASS.ASSIGNMENT_TYPE = 'E'
1069 and POS.LOCATION_ID = HRL.LOCATION_ID(+)
1070 and HRL.COUNTRY = FNDT.TERRITORY_CODE(+)
1071 and FNDT.NLS_TERRITORY = FNDL.NLS_TERRITORY(+);
1072 --
1073 cursor c_pos_exists(p_position_id number) is
1074 select 'x'
1075 from wf_roles -- wf_local_roles --(bug 2897533)
1076 where orig_system_id = p_position_id
1077 and orig_system = 'POS';
1078 --
1079 cursor c_user_exists(p_user_id varchar2) is
1080 select 'x'
1081 from wf_users --wf_local_users --(bug 2897533)
1082 where orig_system_id = p_user_id
1083 and orig_system = 'PER';
1084 --
1085 /*
1086 cursor c_per_pos_del(p_person_id varchar2) is
1087 select 'x'
1088 from wf_user_roles --wf_local_user_roles --(bug 2897533)
1089 where user_orig_system_id = p_person_id
1090 and user_orig_system = 'PER'
1091 and role_orig_system = 'POS';
1092 */
1093 --
1094 r_person c_person%rowtype;
1095 l_assignment_id varchar2(15) := mykey;
1096 l_plist wf_parameter_list_t;
1097 l_proc varchar2(30) := 'MY_SYNC_ROUTINE';
1098 l_dummy varchar2(10);
1099 l_dummy_pos varchar2(10);
1100 l_dummy_user varchar2(10);
1101 --
1102 begin
1103 --
1104 if l_assignment_id is not null then
1105 open c_person(l_assignment_id);
1106 fetch c_person into r_person;
1107
1108 hr_utility.set_location('ORIG SYSTEM ID: '|| r_person.USER_ORIG_SYSTEM_ID||l_proc, 5);
1109 hr_utility.set_location('ORIG SYSTEM:'|| r_person.USER_ORIG_SYSTEM|| l_proc, 5);
1110 hr_utility.set_location('ROLE ORIG SYSTEM:'|| r_person.ROLE_ORIG_SYSTEM|| l_proc, 5);
1111
1112 if c_person%notfound then
1113 /*
1114 open c_per_pos_del(r_person.USER_ORIG_SYSTEM_ID);
1115 fetch c_per_pos_del into l_dummy;
1116 if c_per_pos_del%found then
1117 hr_utility.set_location('WF_SYNC set DELETE parameter true: '||l_proc, 10);
1118 wf_event.AddParameterToList('USER_NAME', r_person.ROLE_NAME,l_plist);
1119 wf_event.AddParameterToList('DELETE', 'TRUE',l_plist);
1120 wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
1121 -- synch the wf_local_user table --
1122 hr_utility.set_location('Before deleting WF_SYNC package role_user: '
1123 || r_person.USER_NAME, 15);
1124 wf_local_synch.propagate_user_role(p_user_orig_system => r_person.USER_ORIG_SYSTEM,
1125 p_user_orig_system_id => r_person.USER_ORIG_SYSTEM_ID,
1126 p_role_orig_system => r_person.ROLE_ORIG_SYSTEM,
1127 p_role_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID);
1128 hr_utility.set_location('After deleting WF_SYNC role_user: '||l_proc, 20);
1129 --
1130 end if;
1131 */
1132 --
1133 close c_person;
1134 --
1135 return;
1136 end if;
1137 close c_person;
1138 end if;
1139
1140 --
1141 -- construct the list of attributes using standard OID att names --
1142 --
1143 hr_utility.set_location('Before calling add parameters: '||l_proc, 20);
1144 wf_event.AddParameterToList( 'orclWFOrigSystem',r_person.ROLE_ORIG_SYSTEM,l_plist);
1145 wf_event.AddParameterToList( 'orclWFOrigSystemID',r_person.ROLE_ORIG_SYSTEM_ID,l_plist);
1146 wf_event.AddParameterToList( 'orclWorkFlowNotificationPref', r_person.NOTIFICATION_PREFERENCE, l_plist);
1147 wf_event.AddParameterToList('preferredLanguage',r_person.LANGUAGE,l_plist);
1148 wf_event.AddParameterToList( 'orclNLSTerritory', r_person.TERRITORY, l_plist);
1149 wf_event.AddParameterToList( 'orclIsEnabled', r_person.STATUS, l_plist);
1150 wf_event.AddParameterToList( 'WFSYNCH_OVERWRITE','TRUE',l_plist);
1151 wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
1152
1153 open c_pos_exists(r_person.ROLE_ORIG_SYSTEM_ID);
1154 fetch c_pos_exists into l_dummy_pos;
1155
1156 if c_pos_exists%notfound then
1157 wf_event.AddParameterToList(
1158 p_name => 'USER_NAME',
1159 p_value => r_person.ROLE_NAME,
1160 p_parameterlist => l_plist);
1161
1162 hr_utility.set_location('In Insert WF_SYNC role: '
1163 || r_person.ROLE_NAME, 25);
1164
1165
1166 WF_LOCAL_SYNCH.propagate_role(p_orig_system => r_person.ROLE_ORIG_SYSTEM,
1167 p_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID,
1168 p_attributes => l_plist);
1169 end if;
1170
1171 open c_user_exists(r_person.USER_ORIG_SYSTEM_ID);
1172 fetch c_user_exists into l_dummy_user;
1173
1174 if c_user_exists%notfound then
1175 hr_utility.set_location('In Insert WF_SYNC user: '
1176 || r_person.USER_NAME, 25);
1177 wf_event.AddParameterToList(
1178 p_name => 'USER_NAME',
1179 p_value => r_person.USER_NAME,
1180 p_parameterlist => l_plist);
1181
1182 WF_LOCAL_SYNCH.propagate_user(p_orig_system => r_person.USER_ORIG_SYSTEM,
1183 p_orig_system_id => r_person.USER_ORIG_SYSTEM_ID,
1184 p_attributes => l_plist);
1185 end if;
1186
1187 -- synch the wf_local_user table --
1188 hr_utility.set_location('Before calling WF_SYNC propagate user_role: '
1189 || r_person.USER_ORIG_SYSTEM_ID, 30);
1190 WF_LOCAL_SYNCH.propagate_user_role(
1191 p_user_orig_system => r_person.USER_ORIG_SYSTEM,
1192 p_user_orig_system_id => r_person.USER_ORIG_SYSTEM_ID,
1193 p_role_orig_system => r_person.ROLE_ORIG_SYSTEM,
1194 p_role_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID);
1195
1196 hr_utility.set_location('After calling WF_SYNC propagate user_role: '||l_proc, 35);
1197 --
1198 end my_synch_routine;
1199 --
1200 end per_pqh_shr;