eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PER_PQH_SHR

Source


1 Package Body per_pqh_shr as
2 /* $Header: pepqhshr.pkb 120.3 2006/02/21 15:22:44 hsajja noship $ */
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 -- |------------------------------< per_asg_wf_sync>-----------------------------|
545 -- ----------------------------------------------------------------------------
546 --
547 Procedure per_asg_wf_sync(p_event varchar2, p_rec  per_asg_shd.g_rec_type
548       ,p_old_position_id       in number default null
549       ,p_effective_date        in date
550       ,p_validation_start_date in date
551       ,p_validation_end_date    in date
552       ,p_datetrack_mode        in varchar2) is
553 --
554        myparms  wf_parameter_list_t;
555        l_future_change  boolean;
556        l_position_id    number;
557        l_assignment_id  varchar2(15);
558        l_old_position_id number;
559        l_old_pos_id     number;
560        l_future_date    date;
561        l_proc           varchar2(30) := 'per_asg_wf_sync';
562        l_start_date	date;
563        l_expiration_date date;
564        l_effective_start_date date;
565        l_assg_start_date date;
566        l_assg_end_date date;
567        cnt number;
568        l_cnt number;
569 
570        cursor get_eff_st_date(c_pos_id number) is
571        select min(effective_start_date) into l_effective_start_date
572        from per_all_assignments_f
573        where assignment_id = l_assignment_id
574        and assignment_type = 'E'
575        and position_id = c_pos_id;
576   begin
577        hr_utility.set_location('Entering:'||l_proc, 5);
578        --
579        l_position_id := p_rec.position_id;
580        l_assignment_id := p_rec.assignment_id;
581        l_old_position_id := per_asg_shd.g_old_rec.position_id;
582        l_assg_start_date := p_rec.effective_start_date;
583        l_assg_end_date := p_rec.effective_end_date;
584        l_old_pos_id  :=  p_old_position_id;
585        --
586        -- Check position budgeted amount
587        --
588        if (p_event in ('POST_INSERT', 'POST_UPDATE')) then
589          if (p_rec.assignment_type = 'E') then
590            if (l_position_id <> nvl(l_old_position_id,-1)) then
591              pqh_psf_bus.chk_position_budget(
592                            p_assignment_id  => l_assignment_id,
593                            p_effective_date => p_effective_date,
594                            p_called_from    => 'ASG',
595                            p_old_position_id => l_old_position_id,
596                            p_new_position_id => l_position_id);
597            end if;
598          end if;
599        end if;
600        --
601        -- Assignment WF Synchronize
602        --
603        if l_old_pos_id is not null then
604 
605             if p_datetrack_mode = 'CORRECTION' then
606                WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system  => 'PER',
607                               p_user_orig_system_id   => p_rec.person_id,
608                               p_role_orig_system      => 'POS',
609                               p_role_orig_system_id   => l_old_pos_id,
610                               p_start_date            => hr_general.end_of_time,
611                               p_expiration_date       => hr_general.end_of_time);
612             else
613                open get_eff_st_date(l_old_pos_id);
614                fetch get_eff_st_date into l_effective_start_date;
615                close get_eff_st_date;
616                WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system  => 'PER',
617                               p_user_orig_system_id   => p_rec.person_id,
618                               p_role_orig_system      => 'POS',
619                               p_role_orig_system_id   => l_old_pos_id,
620                               p_start_date            => l_effective_start_date,
621                               p_expiration_date       => l_assg_start_date-1);
622 
623             end if;
624         end if;
625        if l_position_id is not null then
626          WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system      => 'PER',
627                               p_user_orig_system_id   => p_rec.person_id,
628                               p_role_orig_system      => 'POS',
629                               p_role_orig_system_id   => l_position_id,
630                               p_start_date	      => l_assg_start_date,
631                               p_expiration_date	      => l_assg_end_date);
632        end if;
633        --
634        hr_utility.set_location('Leaving:'||l_proc, 5);
635 
636   end;
637 --
638 -- ----------------------------------------------------------------------------
639 -- |---------------------------< my_synch_routine >---------------------------|
640 -- ----------------------------------------------------------------------------
641 --
642 procedure my_synch_routine(mykey in varchar2) is
643 --
644 cursor c_person(p_assignment_id varchar2) is
645        SELECT USR.USER_NAME          USER_NAME,
646        'PER'                         USER_ORIG_SYSTEM,
647        PER.PERSON_ID                 USER_ORIG_SYSTEM_ID,
648        'POS'||':'||POS.POSITION_ID   ROLE_NAME,
649        'POS'                         ROLE_ORIG_SYSTEM,
650        POS.POSITION_ID               ROLE_ORIG_SYSTEM_ID,
651        PER.FULL_NAME                 USER_DISPLAY_NAME,
652        'QUERY'                       NOTIFICATION_PREFERENCE,
653        FNDL.NLS_LANGUAGE             LANGUAGE,
654        FNDT.NLS_TERRITORY            TERRITORY,
655        PER.EMAIL_ADDRESS             EMAIL_ADDRESS,
656        NULL                          FAX,
657        'ACTIVE'                      STATUS
658 from   PER_ALL_ASSIGNMENTS_F ASS,
659        PER_ALL_POSITIONS  POS,
660        FND_USER USR,
661        PER_ALL_PEOPLE_F PER,
662        FND_TERRITORIES FNDT,
663        FND_LANGUAGES FNDL,
664        HR_LOCATIONS HRL
665 where  ASS.ASSIGNMENT_ID = P_ASSIGNMENT_ID
666 and    ASS.POSITION_ID = POS.POSITION_ID
667 and    ASS.PERSON_ID   = USR.EMPLOYEE_ID
668 and    ASS.PERSON_ID   = PER.PERSON_ID
669 and    trunc(sysdate) between PER.EFFECTIVE_START_DATE
670                           and PER.EFFECTIVE_END_DATE
671 and    trunc(sysdate) between ASS.EFFECTIVE_START_DATE
672                           and ASS.EFFECTIVE_END_DATE
673 and    trunc(sysdate) between USR.START_DATE
674                           and nvl(USR.END_DATE, sysdate+1)
675 and    PER.EMPLOYEE_NUMBER is not null
676 and    ASS.ASSIGNMENT_TYPE = 'E'
677 and     POS.LOCATION_ID         = HRL.LOCATION_ID(+)
678 and     HRL.COUNTRY             = FNDT.TERRITORY_CODE(+)
679 and     FNDT.NLS_TERRITORY      = FNDL.NLS_TERRITORY(+);
680 --
681         cursor c_pos_exists(p_position_id number) is
682         select 'x'
683         from wf_roles -- wf_local_roles --(bug 2897533)
684         where orig_system_id = p_position_id
685         and   orig_system = 'POS';
686 --
687         cursor c_user_exists(p_user_id varchar2) is
688         select 'x'
689         from wf_users --wf_local_users --(bug 2897533)
690         where orig_system_id = p_user_id
691         and   orig_system = 'PER';
692 --
693 /*
694         cursor c_per_pos_del(p_person_id varchar2) is
695         select 'x'
696         from wf_user_roles --wf_local_user_roles --(bug 2897533)
697         where user_orig_system_id = p_person_id
698         and   user_orig_system = 'PER'
699         and   role_orig_system = 'POS';
700 */
701 --
702         r_person     c_person%rowtype;
703         l_assignment_id varchar2(15) := mykey;
704         l_plist        wf_parameter_list_t;
705         l_proc         varchar2(30) := 'MY_SYNC_ROUTINE';
706         l_dummy        varchar2(10);
707         l_dummy_pos    varchar2(10);
708         l_dummy_user   varchar2(10);
709 --
710       begin
711         --
712         if l_assignment_id is not null then
713           open c_person(l_assignment_id);
714           fetch c_person into r_person;
715 
716           hr_utility.set_location('ORIG SYSTEM ID: '|| r_person.USER_ORIG_SYSTEM_ID||l_proc, 5);
717           hr_utility.set_location('ORIG SYSTEM:'|| r_person.USER_ORIG_SYSTEM|| l_proc, 5);
718           hr_utility.set_location('ROLE ORIG SYSTEM:'|| r_person.ROLE_ORIG_SYSTEM|| l_proc, 5);
719 
720           if c_person%notfound then
721             /*
722             open c_per_pos_del(r_person.USER_ORIG_SYSTEM_ID);
723             fetch c_per_pos_del into l_dummy;
724             if c_per_pos_del%found then
725               hr_utility.set_location('WF_SYNC set DELETE parameter true: '||l_proc, 10);
726               wf_event.AddParameterToList('USER_NAME', r_person.ROLE_NAME,l_plist);
727               wf_event.AddParameterToList('DELETE', 'TRUE',l_plist);
728               wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
729               -- synch the wf_local_user table --
730               hr_utility.set_location('Before deleting WF_SYNC package role_user: '
731                                           || r_person.USER_NAME, 15);
732               wf_local_synch.propagate_user_role(p_user_orig_system   => r_person.USER_ORIG_SYSTEM,
733                                                  p_user_orig_system_id  => r_person.USER_ORIG_SYSTEM_ID,
734                                                  p_role_orig_system  => r_person.ROLE_ORIG_SYSTEM,
735                                                  p_role_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID);
736               hr_utility.set_location('After deleting WF_SYNC role_user: '||l_proc, 20);
737               --
738             end if;
739             */
740             --
741             close c_person;
742             --
743             return;
744           end if;
745           close c_person;
746         end if;
747 
748         --
749         -- construct the list of attributes using standard OID att names --
750         --
751         hr_utility.set_location('Before calling add parameters: '||l_proc, 20);
752          wf_event.AddParameterToList( 'orclWFOrigSystem',r_person.ROLE_ORIG_SYSTEM,l_plist);
753          wf_event.AddParameterToList( 'orclWFOrigSystemID',r_person.ROLE_ORIG_SYSTEM_ID,l_plist);
754          wf_event.AddParameterToList( 'orclWorkFlowNotificationPref', r_person.NOTIFICATION_PREFERENCE, l_plist);
755         wf_event.AddParameterToList('preferredLanguage',r_person.LANGUAGE,l_plist);
756          wf_event.AddParameterToList( 'orclNLSTerritory', r_person.TERRITORY, l_plist);
757          wf_event.AddParameterToList( 'orclIsEnabled', r_person.STATUS, l_plist);
758          wf_event.AddParameterToList( 'WFSYNCH_OVERWRITE','TRUE',l_plist);
759          wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
760 
761         open c_pos_exists(r_person.ROLE_ORIG_SYSTEM_ID);
762         fetch c_pos_exists into l_dummy_pos;
763 
764         if c_pos_exists%notfound then
765            wf_event.AddParameterToList(
766              p_name => 'USER_NAME',
767              p_value => r_person.ROLE_NAME,
768              p_parameterlist => l_plist);
769 
770        hr_utility.set_location('In Insert WF_SYNC role: '
771                                           || r_person.ROLE_NAME, 25);
772 
773 
774            WF_LOCAL_SYNCH.propagate_role(p_orig_system => r_person.ROLE_ORIG_SYSTEM,
775                          p_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID,
776                          p_attributes => l_plist);
777        end if;
778 
779         open c_user_exists(r_person.USER_ORIG_SYSTEM_ID);
780         fetch c_user_exists into l_dummy_user;
781 
782         if c_user_exists%notfound then
783         hr_utility.set_location('In Insert WF_SYNC user: '
784                                           || r_person.USER_NAME, 25);
785            wf_event.AddParameterToList(
786              p_name => 'USER_NAME',
787              p_value => r_person.USER_NAME,
788              p_parameterlist => l_plist);
789 
790            WF_LOCAL_SYNCH.propagate_user(p_orig_system => r_person.USER_ORIG_SYSTEM,
791                          p_orig_system_id => r_person.USER_ORIG_SYSTEM_ID,
792                          p_attributes => l_plist);
793         end if;
794 
795         -- synch the wf_local_user table --
796         hr_utility.set_location('Before calling WF_SYNC propagate user_role: '
797                                           || r_person.USER_ORIG_SYSTEM_ID, 30);
798               WF_LOCAL_SYNCH.propagate_user_role(
799                               p_user_orig_system   => r_person.USER_ORIG_SYSTEM,
800                               p_user_orig_system_id  => r_person.USER_ORIG_SYSTEM_ID,
801                               p_role_orig_system  => r_person.ROLE_ORIG_SYSTEM,
802                               p_role_orig_system_id => r_person.ROLE_ORIG_SYSTEM_ID);
803 
804         hr_utility.set_location('After calling WF_SYNC propagate user_role: '||l_proc, 35);
805         --
806       end my_synch_routine;
807 --
808 end per_pqh_shr;