1 PACKAGE BODY pay_pyucslis_pkg AS
2 /* $Header: pyucslis.pkb 120.26.12010000.5 2008/09/16 10:27:09 rnemani ship $ */
3 --
4 --
5 g_package varchar2(20) := 'pay_pyucslis_pkg.';
6 g_debug boolean := hr_utility.debug_enabled;
7 --
8 --
9 -- Start changes for the Bug 5438641
10 -- The procedure modified for Bulk Collect.
11
12 procedure add_contacts_for_person(
13 p_person_id number,
14 p_business_group_id number,
15 p_generation_scope varchar2,
16 p_effective_date date
17 ) is
18 --
19 l_proc varchar2(72):= g_package||'add_contacts_for_person';
20 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
21 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
22 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
23 l_upd_date date := trunc(sysdate);
24 --
25 begin
26 --
27 hr_utility.set_location('Entering: '||l_proc, 10);
28 -- Insert a record for each contact of the person. Only process those
29 -- profiles which are in the generation scope but include ALL contacts
30 -- for this person_id.
31
32 -- Added DISTINCT so that it handles multiple contact relationships
33 -- between the same two people. Do not insert if using user-based
34 -- security as this is assessed dynamically.
35 insert into per_person_list(security_profile_id, person_id, request_id,
36 program_application_id, program_id,
37 program_update_date)
38 select /*+ USE_NL(PSP) */
39 distinct ppl.security_profile_id, pcr.contact_person_id,
40 l_req_id, l_appl_id, l_prog_id, l_upd_date
41 from per_contact_relationships pcr,
42 per_person_list ppl,
43 per_security_profiles psp
44 where ppl.person_id = p_person_id
45 and ppl.security_profile_id = psp.security_profile_id
46 and (psp.view_all_contacts_flag = 'N' or
47 (psp.view_all_contacts_flag = 'Y' and
48 psp.view_all_candidates_flag = 'X'))
49 and (nvl(psp.top_organization_method, 'S') <> 'U' and
50 nvl(psp.top_position_method, 'S') <> 'U' and
51 nvl(psp.custom_restriction_flag, 'N') <> 'U')
52 and ((psp.business_group_id = p_business_group_id and
53 p_generation_scope = 'ALL_BUS_GRP') or
54 (psp.business_group_id is null and
55 p_generation_scope = 'ALL_GLOBAL') or
56 p_generation_scope = 'ALL_PROFILES')
57 and pcr.person_id = ppl.person_id
58 and not exists
59 (select /*+ NO_MERGE */ null
60 from per_all_assignments_f asg
61 where asg.person_id = pcr.contact_person_id
62 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
63 and not exists
64 (select /*+ NO_MERGE */ null
65 from per_person_list ppl1
66 where ppl1.person_id = pcr.contact_person_id
67 and ppl1.granted_user_id is null
68 and ppl1.security_profile_id = ppl.security_profile_id);
69 --
70 hr_utility.set_location('Leaving: '||l_proc, 99);
71 --
72 end add_contacts_for_person;
73
74 --
75 procedure add_unrelated_contacts(
76 p_business_group_id number
77 ,p_generation_scope varchar2
78 ,p_effective_date date
79 ) is
80 --
81 type l_number_t is table of number index by binary_integer;
82 --
83 l_per_tbl l_number_t;
84 l_per_bg_tbl l_number_t;
85 l_sp_tbl l_number_t;
86 l_sp_bg_tbl l_number_t;
87 --
88 l_proc varchar2(72) := g_package||'.add_unrelated_contacts';
89 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
90 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
91 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
92 l_upd_date date := trunc(sysdate);
93 t_varchar varchar2(1);
94 -- Fetch the unrelated contacts, excluding the Candidates (iRecruitment
95 -- registered user's) that are in scope.
96 cursor csr_get_unrelated_contacts is
97 select distinct
98 papf.person_id,
99 papf.business_group_id
100 from per_all_people_f papf
101 where not exists
102 (select null
103 from per_all_assignments_f asg
104 where asg.person_id = papf.person_id)
105 and not exists
106 (select null
107 from per_contact_relationships pcr
108 where pcr.contact_person_id = papf.person_id)
109 and ((p_generation_scope = 'ALL_BUS_GRP' and
110 papf.business_group_id = p_business_group_id) or
111 p_generation_scope <> 'ALL_BUS_GRP')
112 and not exists
113 (select null
114 from per_person_type_usages_f ptuf,
115 per_person_types ppt
116 where ppt.system_person_type = 'IRC_REG_USER'
117 and ptuf.person_type_id = ppt.person_type_id
118 and ptuf.person_id = papf.person_id);
119 -- Fetch the security profiles that restrict by contacts, are
120 -- in profile scope and are not user-based.
121 --
122 -- Cursor for p_generation_scope = 'ALL_BUS_GRP'
123 cursor csr_get_sec_profs_bg is
124 select psp.security_profile_id,
125 psp.business_group_id
126 from per_security_profiles psp
127 where (psp.view_all_contacts_flag = 'N' or
128 (psp.view_all_contacts_flag = 'Y' and
129 psp.view_all_candidates_flag = 'X'))
130 and (nvl(psp.top_organization_method, 'S') <> 'U' and
131 nvl(psp.top_position_method, 'S') <> 'U' and
132 nvl(psp.custom_restriction_flag, 'N') <> 'U')
133 and psp.business_group_id = p_business_group_id;
134 --
135 -- Cursor for p_generation_scope <> 'ALL_BUS_GRP'
136 cursor csr_get_sec_profs is
137 select psp.security_profile_id,
138 psp.business_group_id
139 from per_security_profiles psp
140 where (psp.view_all_contacts_flag = 'N' or
141 (psp.view_all_contacts_flag = 'Y' and
142 psp.view_all_candidates_flag = 'X'))
143 and (nvl(psp.top_organization_method, 'S') <> 'U' and
144 nvl(psp.top_position_method, 'S') <> 'U' and
145 nvl(psp.custom_restriction_flag, 'N') <> 'U')
146 and ((p_generation_scope = 'ALL_GLOBAL' and
147 psp.business_group_id is null) or
148 p_generation_scope = 'ALL_PROFILES');
149 --
150
151 begin
152 --
153 hr_utility.set_location('Entering: '||l_proc, 1);
154 hr_utility.set_location('Request ID '||p_request_id, 15);
155 --
156 -- Bulk collect the unrelated contacts into PL/SQL tables.
157 open csr_get_unrelated_contacts;
158 fetch csr_get_unrelated_contacts bulk collect into l_per_tbl, l_per_bg_tbl;
159 close csr_get_unrelated_contacts;
160 --
161 hr_utility.set_location(l_proc, 10);
162 --
163 if l_per_tbl.count > 0 then
164 -- When there are unrelated contacts, bulk collect the security
165 -- profiles that restrict by contacts.
166 hr_utility.set_location(l_proc, 20);
167 --
168 if p_generation_scope = 'ALL_BUS_GRP' then
169 --
170 open csr_get_sec_profs_bg;
171 fetch csr_get_sec_profs_bg bulk collect into l_sp_tbl, l_sp_bg_tbl;
172 close csr_get_sec_profs_bg;
173 --
174 else
175 --
176 open csr_get_sec_profs;
177 fetch csr_get_sec_profs bulk collect into l_sp_tbl, l_sp_bg_tbl;
178 close csr_get_sec_profs;
179 --
180 end if;
181 --
182 hr_utility.set_location(l_proc, 30);
183 --
184 if l_sp_tbl.count > 0 then
185 --
186 hr_utility.set_location(l_proc, 40);
187 --
188 for i in l_sp_tbl.first..l_sp_tbl.last loop
189 -- Insert the unrelated contacts for each security profile.
190 -- Enforce the business group restriction when restricting
191 -- by all profiles.
192 for j in l_per_tbl.first..l_per_tbl.last
193 --
194 loop
195
196 if (p_generation_scope <> 'ALL_PROFILES' or
197 (p_generation_scope = 'ALL_PROFILES' and
198 nvl(l_sp_bg_tbl(i), l_per_bg_tbl(j)) =
199 l_per_bg_tbl(j)))
200
201 then
202 begin
203
204 select 'X' into t_varchar
205 from per_person_list p2
206 where p2.person_id = l_per_tbl(j)
207 and p2.security_profile_id = l_sp_tbl(i);
208
209 exception
210 when no_Data_found then
211
212 insert into per_person_list(security_profile_id, person_id,
213 request_id, program_application_id,
214 program_id, program_update_date)
215 values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
216 l_appl_id, l_prog_id, l_upd_date);
217 --
218 end;
219
220 --
221 end if;
222 end loop;
223 end loop;
224 --
225 end if;
226 --
227 end if;
228 --
229 hr_utility.set_location('Leaving: '||l_proc, 999);
230 --
231 end add_unrelated_contacts;
232 --
233
234 /* =======================================================================
235 NAME
236 delete_old_person_list_changes
237 DESCRIPTION
238 Delete entries in the person list changes table which are no longer
239 required because they are currently employed.
240 PARAMETERS
241 l_effective_date - date at which we are running.
242 ========================================================================== */
243 --
244 PROCEDURE delete_old_person_list_changes (l_effective_date DATE)
245 IS
246 BEGIN
247 --
248 hr_utility.set_location('hr_listgen.delete_old_person_list_changes',10);
249 --
250 -- Stubb out as part of ex-person security enhancements.
251 --
252 hr_utility.set_location('hr_listgen.delete_old_person_list_changes',20);
253 --
254 END delete_old_person_list_changes;
255 --
256 --
257 --
258 --
259 /* =======================================================================
260 NAME
261 build_payroll_list
262 DESCRIPTION
263 Insert payroll list entries for the current security profile based on
264 the secured payroll table per_security_payrolls generated by the define
265 security profile form. If the include_exclude option in the security
266 profile is set to 'I' then the specified payrolls are copied to the payroll
267 list. If the include_exclude flag is 'E' then all other payrolls for
268 the business group are inserted into the list.
269 PARAMETERS
270 l_security_profile_id - identifier of the current security profile
271 l_business_group_id - business group of the security profile.
272 l_include_exclude_payroll_flag - include/exclude option of security profile
273 l_effective_date - date at which the lists are generated
274 l_update_date - today's date.
275
276 ========================================================================== */
277 PROCEDURE build_payroll_list (l_security_profile_id NUMBER,
278 l_business_group_id NUMBER,
279 l_include_exclude_payroll_flag VARCHAR2,
280 l_effective_date DATE,
281 l_update_date DATE)
282 IS
283 BEGIN
284 --
285 IF (l_include_exclude_payroll_flag = 'I') THEN
286 --
287 hr_utility.set_location('hr_listgen.build_payroll_list', 10);
288 --
289 INSERT INTO pay_payroll_list
290 (payroll_id,
291 security_profile_id,
292 request_id,
293 program_application_id,
294 program_id,
295 program_update_date)
296 SELECT pay.payroll_id,
297 l_security_profile_id,
298 p_request_id,
299 p_program_application_id,
300 p_program_id,
301 l_update_date
302 FROM pay_all_payrolls_f pay,
303 pay_security_payrolls sec
304 WHERE sec.security_profile_id = l_security_profile_id
305 AND sec.payroll_id = pay.payroll_id
306 AND l_effective_date
307 BETWEEN pay.effective_start_date
308 AND pay.effective_end_date;
309 --
310 ELSE -- exclude payrolls
311 --
312 hr_utility.set_location('hr_listgen.build_payroll_list', 20);
313 --
314 INSERT INTO pay_payroll_list
315 (payroll_id,
316 security_profile_id,
317 request_id,
318 program_application_id,
319 program_id,
320 program_update_date)
321 SELECT pay.payroll_id,
322 l_security_profile_id,
323 p_request_id,
324 p_program_application_id,
325 p_program_id,
326 l_update_date
327 FROM pay_all_payrolls_f pay
328 WHERE l_effective_date
329 BETWEEN pay.effective_start_date
330 AND pay.effective_end_date
331 AND pay.business_group_id + 0 = l_business_group_id
332 AND NOT EXISTS
333 (SELECT NULL
334 FROM pay_security_payrolls sec
335 WHERE sec.security_profile_id = l_security_profile_id
336 AND sec.payroll_id = pay.payroll_id) ;
337 --
338 END IF; -- include payrolls
339 --
340 END build_payroll_list;
341 --
342 --
343 /* =======================================================================
344 NAME
345 build_organization_list
346 DESCRIPTION
347 Insert values into the organization list for the security profile.
348 Starting with the organization specified a tree walk of the organization
349 structure element table per_org_structure_elements takes place and
350 all organization below that specified are inserted into the organization
351 list. If the include_top_org option is specified then that organisation
352 is explicitly inserted into the list. The business group is
353 inserted into the organisation list if not previously inserted.
354 PARAMETERS
355 l_security_profile_id - identifier of the current security profile
356 l_include_top_org_flag - include/exclude top organization option
357 l_organization_structure_id - identifier of the organization structure
358 to be used.
359 l_organization_id - top organization to consider within the
360 organization structure
361 l_exclude_business_groups_flag - include/exclude all business groups when
362 running in global mode
363 l_effective_date - effective date of the run to pick the
364 structure version.
365 l_update_date - todays date.
366 p_business_group_mode - LOCAL/GLOBAL depends on type of security
367 profile.
368 ========================================================================== */
369 --
370 PROCEDURE build_organization_list (
371 l_security_profile_id NUMBER,
372 l_include_top_org_flag VARCHAR2,
373 l_organization_structure_id NUMBER,
374 l_organization_id NUMBER,
375 l_exclude_business_groups_flag VARCHAR2,
376 l_effective_date DATE,
377 l_update_date DATE,
378 p_business_group_mode VARCHAR2) IS
379 --
380 l_proc varchar2(100) := 'pay_pyucslis_pkg.build_organization_list';
381 --
382 begin
383 --
384 hr_utility.set_location(l_proc, 10);
385 --
386 -- Insert all organizations in the hierarchy (excluding the top organization).
387 --
388 INSERT INTO per_organization_list
389 (security_profile_id,
390 organization_id,
391 request_id,
392 program_application_id,
393 program_id,
394 program_update_date )
395 SELECT l_security_profile_id,
396 o.organization_id_child,
397 p_request_id,
398 p_program_application_id,
399 p_program_id,
400 l_update_date
401 FROM per_org_structure_elements o
402 CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
403 AND o.org_structure_version_id = PRIOR o.org_structure_version_id
404 START WITH o.organization_id_parent = l_organization_id
405 AND o.org_structure_version_id =
406 (SELECT v.org_structure_version_id
407 FROM per_org_structure_versions v
408 WHERE v.organization_structure_id = l_organization_structure_id
409 AND l_effective_date BETWEEN v.date_from
410 AND NVL(v.date_to, TO_DATE('31-12-4712','dd-mm-yyyy')));
411 --
412 hr_utility.set_location(l_proc, 20);
413 --
414 -- Insert all organizations in the organization list that have their
415 -- include / exclude flag set to 'I'.
416 --
417 INSERT INTO per_organization_list
418 (security_profile_id,
419 request_id,
420 program_id,
421 program_application_id,
422 program_update_date,
423 organization_id)
424 SELECT l_security_profile_id,
425 p_request_id,
426 p_program_id,
427 p_program_application_id,
428 l_update_date,
429 pso.organization_id
430 FROM per_security_organizations pso
431 WHERE pso.entry_type = 'I'
432 AND pso.security_profile_id = l_security_profile_id
433 AND NOT EXISTS
434 (SELECT NULL
435 FROM per_organization_list pol
436 WHERE pol.security_profile_id = l_security_profile_id
437 AND pol.organization_id = pso.organization_id);
438 --
439 hr_utility.set_location(l_proc, 30);
440 --
441 -- Include the Top Organization if the security profile permits.
442 --
443 IF (l_include_top_org_flag = 'Y') THEN
444 --
445 IF l_organization_id IS NOT NULL THEN
446 --
447 hr_utility.set_location(l_proc, 40);
448 --
449 INSERT INTO per_organization_list
450 (security_profile_id,
451 organization_id,
452 request_id,
453 program_application_id,
454 program_id,
455 program_update_date )
456 SELECT l_security_profile_id,
457 l_organization_id,
458 p_request_id,
459 p_program_application_id,
460 p_program_id,
461 l_update_date
462 FROM DUAL
463 /* Duplicate check. Required because of organization list Includes */
464 WHERE NOT EXISTS
465 (SELECT NULL
466 FROM per_organization_list pol
467 WHERE pol.security_profile_id = l_security_profile_id
468 AND pol.user_id IS NULL
469 AND pol.organization_id = l_organization_id);
470 --
471 END IF;
472 --
473 END IF;
474 --
475 hr_utility.set_location(l_proc, 50);
476 --
477 IF p_business_group_mode='GLOBAL' AND
478 NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
479 --
480 -- Include all business groups in the hierarchy for the GLOBAL sec prof.
481 --
482 INSERT INTO per_organization_list
483 (security_profile_id,
484 organization_id,
485 request_id,
486 program_application_id,
487 program_id,
488 program_update_date )
489 SELECT DISTINCT
490 l_security_profile_id,
491 org.business_group_id,
492 p_request_id,
493 p_program_application_id,
494 p_program_id,
495 l_update_date
496 FROM hr_all_organization_units org
497 , per_organization_list lst
498 WHERE lst.security_profile_id = l_security_profile_id
499 AND lst.organization_id=org.organization_id
500 AND NOT EXISTS
501 (SELECT 1
502 FROM per_organization_list lst2
503 WHERE lst2.organization_id = org.business_group_id
504 AND lst2.user_id IS NULL
505 AND lst2.security_profile_id = l_security_profile_id);
506 --
507 ELSIF p_business_group_mode = 'LOCAL' AND
508 NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
509 --
510 -- Include the business group for a LOCAL security profile.
511 --
512 INSERT INTO per_organization_list
513 (security_profile_id,
514 organization_id,
515 request_id,
516 program_application_id,
517 program_id,
518 program_update_date )
519 SELECT l_security_profile_id,
520 s.business_group_id,
521 p_request_id,
522 p_program_application_id,
523 p_program_id,
524 l_update_date
525 FROM per_security_profiles s
526 WHERE s.security_profile_id = l_security_profile_id
527 AND NOT EXISTS
528 (SELECT NULL
529 FROM per_organization_list b
530 WHERE b.organization_id = s.business_group_id
531 AND b.user_id IS NULL
532 AND b.security_profile_id = l_security_profile_id);
533 --
534 END IF;
535 --
536 -- Remove the organizations listed as 'Exclude' in the organization list.
537 --
538 DELETE
539 FROM per_organization_list
540 WHERE security_profile_id = l_security_profile_id
541 AND user_id IS NULL
542 AND organization_id IN
543 (SELECT organization_id
544 FROM per_security_organizations
545 WHERE security_profile_id = l_security_profile_id
546 AND entry_type = 'E');
547 --
548 -- BUSINESS_GROUP_ID's should be excluded from PER_ORGANIZATION_LIST for a
549 -- global security profile with EXCLUDE_BUSINESS_GROUPS_FLAG is set as 'Y'.
550 --
551 -- Here an exclusive DELETE command is used, because in global security
552 -- profile a business group can be a child of child of another business
553 -- group (ie: more than one hierarchy below). In such cases a NOT EXISTS
554 -- clause will not identify these business groups, when we use CONNECT BY.
555 -- ie: While using NOT EXISTS clause along with CONNECT BY, then NOT EXISTS
556 -- will scan only the first level of hierarchy and not the subsequent levels
557 -- below.
558 --
559 IF p_business_group_mode = 'GLOBAL' AND
560 NVL(l_exclude_business_groups_flag, 'N') = 'Y' THEN
561 --
562 DELETE
563 FROM per_organization_list pol
564 WHERE pol.security_profile_id = l_security_profile_id
565 AND pol.user_id IS NULL
566 AND pol.organization_id IN
567 (SELECT org.business_group_id
568 FROM hr_all_organization_units org
569 WHERE org.organization_id = pol.organization_id
570 AND org.organization_id = org.business_group_id);
571 --
572 END IF;
573 --
574 END build_organization_list;
575 --
576 /* =======================================================================
577 NAME
578 build_position_list
579 DESCRIPTION
580 Insert values into the position list for the security profile.
581 A tree walk of the position structure table takes place starting with
582 the top position specified. If the 'all_organisations' option is
583 specified then a row is inserted for each position in the structure
584 below the top position. If 'all_organizations' is not specified then
585 rows are only inserted if the position encountered exists in an
586 organization in the organization list for the security profile. If
587 the 'include top position' option is specified then the position is
588 explictly inserted into the position list.
589 PARAMETERS
590 l_security_profile_id - identifier of the current security profile.
591 l_view_all_organizations_flag - all organizations option
592 l_include_top_position_flag - include/exclude top position option
593 l_position_structure_id - position structure to be used.
594 l_position_id - top position in the position structure
595 to be used.
596 l_effective_date - effective_date of the run at which to
597 pick the version.
598 l_update_date - today's date.
599 ========================================================================== */
600 --
601 PROCEDURE build_position_list (l_security_profile_id NUMBER,
602 l_view_all_organizations_flag VARCHAR2,
603 l_include_top_position_flag VARCHAR2,
604 l_position_structure_id NUMBER,
605 l_position_id NUMBER,
606 l_effective_date DATE,
607 l_update_date DATE)
608 IS
609 BEGIN
610 --
611 IF (l_view_all_organizations_flag = 'N') THEN
612 --
613 hr_utility.set_location('hr_listgen.build_position_list', 10);
614 --
615 INSERT INTO per_position_list
616 (security_profile_id,
617 position_id,
618 request_id,
619 program_application_id,
620 program_id,
621 program_update_date )
622 SELECT l_security_profile_id,
623 p.subordinate_position_id,
624 p_request_id,
625 p_program_application_id,
626 p_program_id,
627 l_update_date
628 FROM per_pos_structure_elements p
629 WHERE EXISTS
630 (SELECT NULL
631 FROM hr_all_positions_f pp,
632 per_organization_list ol
633 WHERE ol.organization_id = pp.organization_id
634 AND pp.position_id = p.subordinate_position_id
635 AND ol.security_profile_id= l_security_profile_id)
636 START WITH p.parent_position_id = l_position_id
637 AND p.pos_structure_version_id =
638 (SELECT v.pos_structure_version_id
639 FROM per_pos_structure_versions v
640 WHERE v.position_structure_id = l_position_structure_id
641 AND l_effective_date
642 BETWEEN v.date_from
643 AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
644 CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
645 AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
646 --
647 -- Include the top position.
648 --
649 IF ( l_include_top_position_flag = 'Y') THEN
650 --
651 hr_utility.set_location('hr_listgen.build_position_list', 20);
652 --
653 INSERT INTO per_position_list
654 (security_profile_id,
655 position_id,
656 request_id,
657 program_application_id,
658 program_id,
659 program_update_date )
660 SELECT l_security_profile_id,
661 l_position_id,
662 p_request_id,
663 p_program_application_id,
664 p_program_id,
665 l_update_date
666 FROM sys.dual
667 WHERE EXISTS
668 (SELECT NULL
669 FROM hr_all_positions_f pp,
670 per_organization_list ol
671 WHERE ol.organization_id = pp.organization_id
672 AND pp.position_id = l_position_id
673 AND ol.security_profile_id= l_security_profile_id);
674 --
675 END IF; -- Include the top position.
676 --
677 ELSE -- l_view_all_organizations_flag
678 --
679 hr_utility.set_location('hr_listgen.build_position_list', 30);
680 --
681 INSERT INTO per_position_list
682 (security_profile_id,
683 position_id,
684 request_id,
685 program_application_id,
686 program_id,
687 program_update_date )
688 SELECT l_security_profile_id,
689 p.subordinate_position_id,
690 p_request_id,
691 p_program_application_id,
692 p_program_id,
693 l_update_date
694 FROM per_pos_structure_elements p
695 START WITH p.parent_position_id = l_position_id
696 AND p.pos_structure_version_id =
697 (SELECT v.pos_structure_version_id
698 FROM per_pos_structure_versions v
699 WHERE v.position_structure_id = l_position_structure_id
700 AND l_effective_date
701 BETWEEN v.date_from
702 AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
703 CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
704 AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
705 --
706 -- Include top position.
707 --
708 IF ( l_include_top_position_flag = 'Y') THEN
709 --
710 hr_utility.set_location('hr_listgen.build_position_list', 40);
711 --
712 INSERT INTO per_position_list
713 (security_profile_id,
714 position_id,
715 request_id,
716 program_application_id,
717 program_id,
718 program_update_date )
719 VALUES (l_security_profile_id,
720 l_position_id,
721 p_request_id,
722 p_program_application_id,
723 p_program_id,
724 l_update_date);
725 --
726 END IF; -- Include the top position.
727 --
728 END IF; -- View all organizations.
729 --
730 END build_position_list;
731 --
732 --
733 /* =======================================================================
734 NAME
735 build_contact_list
736 DESCRIPTION
737 Insert contacts into the person list for the security profile.
738 PARAMETERS
739 p_security_profile_id - security profile identifier
740 p_effective_date - date at which the lists are generated
741 p_business_group_id - business group ID from the security profile.
742 If it's null(global profile) include contacts
743 from all BGs. Otherwise just for the profiles
744 business group.
745 ========================================================================== */
746 --
747 procedure build_contact_list(
748 p_security_profile_id number,
749 p_view_all_contacts_flag varchar2, -- Added for bug (6376000/4774264)
750 p_effective_date date,
751 p_business_group_id number
752 ) is
753 --
754 l_proc varchar2(72):= g_package||'build_contact_list';
755 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
756 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
757 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
758 l_upd_date date := trunc(sysdate);
759 --
760 begin
761 --
762 hr_utility.set_location('Entering: ' || l_proc, 10);
763
764 IF p_view_all_contacts_flag = 'Y' then ---- Added for bug (6376000/4774264)
765 -- If the Security profile set as -- View All contacts = Yes
766 -- In this case we need to Insert the contact records for,
767 -- 1) Related contacts - If the person/Employee is visible then Only Insert
768 -- contacts related to the Person/Employee.
769 -- --> Query #2 will populate these records.
770 -- 2) Unrelated Contacts - Insert all Unrelated i.e which is not belong to any
771 -- Person/Record in system.
772 -- --> Query #3 will populate these records.
773 -- 3) View All contacts = Yes --> Insert all reacords which are related to the
774 -- Person/Employee in the system but not populated because of the Security Profile setup like
775 -- Employee = Restricted.
776 -- --> Query #1 will populate these records.
777
778
779 -- Query #1
780 insert into per_person_list(security_profile_id, request_id, program_id
781 ,program_application_id, program_update_date
782 ,person_id)
783 select distinct p_security_profile_id, l_req_id, l_prog_id,
784 l_appl_id, l_upd_date, pcr.contact_person_id
785 from per_contact_relationships pcr,
786 per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
787 where ppl.person_id = pcr.person_id
788 and (pcr.business_group_id = p_business_group_id or
789 p_business_group_id is null)
790 -- and ppl.security_profile_id = p_security_profile_id for bug (6376000/4774264)
791 and not exists
792 (select null
793 from per_all_assignments_f asg
794 where asg.person_id = pcr.contact_person_id
795 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
796 and not exists
797 (select null
798 from per_person_list ppl1
799 where ppl1.person_id = pcr.contact_person_id
800 and ppl1.granted_user_id is null
801 and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
802 --
803 hr_utility.set_location(l_proc, 20);
804 Else
805 -- Insert into person list, all people with a contact relationship to
806 -- someone already in the person list as long as their system person type
807 -- is 'other'
808 -- Added DISTINCT to handle two or more contact relationships
809 -- for the same two people (e.g., the same person is a brother and
810 -- emergency contact).
811
812 -- Instead of using using the worker numbers to evaluate whether the
813 -- current record is a contact another AND NOT EXISTS can check for
814 -- assignments.
815 -- This way, all contacts who are also another type will be ignored,
816 -- it should be assumed that their assignments will be processed by an
817 -- earlier part of LISTGEN.
818 -- Query #2
819 insert into per_person_list(security_profile_id, request_id, program_id
820 ,program_application_id, program_update_date
821 ,person_id)
822 select distinct ppl.security_profile_id, l_req_id, l_prog_id,
823 l_appl_id, l_upd_date, pcr.contact_person_id
824 from per_contact_relationships pcr,
825 per_person_list ppl
826 where ppl.person_id = pcr.person_id
827 and (pcr.business_group_id = p_business_group_id or
828 p_business_group_id is null)
829 and ppl.security_profile_id = p_security_profile_id
830 and not exists
831 (select null
832 from per_all_assignments_f asg
833 where asg.person_id = pcr.contact_person_id
834 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
835 and not exists
836 (select null
837 from per_person_list ppl1
838 where ppl1.person_id = pcr.contact_person_id
839 and ppl1.granted_user_id is null
840 and ppl1.security_profile_id = ppl.security_profile_id);
841 --
842 hr_utility.set_location(l_proc, 30);
843 End if;
844 -- Inserts all unrelated contacts(excluding the candidates, those
845 -- registered from iRecruitment) who do not have any other assignments.
846 -- If there are additional assignments these will be excluded from here
847 -- on the assumption that they would have been evaluated in the previous
848 -- stages of listgen.
849 -- Query #3
850 insert into per_person_list(security_profile_id, request_id,
851 program_application_id, program_id,
852 program_update_date, person_id)
853 select distinct psp.security_profile_id, l_req_id, l_appl_id,
854 l_prog_id, l_upd_date, papf.person_id
855 from per_all_people_f papf,
856 per_security_profiles psp
857 where psp.security_profile_id = p_security_profile_id
858 and (psp.business_group_id = papf.business_group_id or
859 psp.business_group_id is null)
860 and not exists
861 (select null
862 from per_all_assignments_f asg
863 where asg.person_id = papf.person_id)
864 and not exists ---- Rever Commneted for for bug 4774264
865 (select null
866 from per_contact_relationships pcr
867 where pcr.contact_person_id = papf.person_id)
868 and not exists
869 (select null
870 from per_person_type_usages_f ptuf,
871 per_person_types ppt
872 where ppt.system_person_type = 'IRC_REG_USER'
873 and ptuf.person_type_id = ppt.person_type_id
874 and ptuf.person_id = papf.person_id)
875 and not exists
876 (select null
877 from per_person_list ppl
878 where ppl.person_id = papf.person_id
879 and ppl.granted_user_id is null
880 and ppl.security_profile_id = psp.security_profile_id);
881 --
882 hr_utility.set_location('Leaving: ' || l_proc, 99);
883 --
884 end build_contact_list;
885 --
886 /* =======================================================================
887 NAME
888 add_person_list_changes
889 DESCRIPTION
890 Insert additional person list entries for persons in the person list
891 changes table. If an entry exists for the security profile in the
892 person list changes table and there is not an entry already for that
893 person in the person list then a row is inserted. Only persons who
894 have a termination date before the effective date and who do
895 not have a current period of service (at effective date) are added. As
896 'B' assignments are created on termination we need to exclude these
897 assignments from consideration.
898 PARAMETERS
899 l_security_profile_id - identifier of the current security profile.
900 l_effective_date - date for which the secure lists are generated.
901 l_update_date - today's date.
902 ========================================================================= */
903 --
904 PROCEDURE add_person_list_changes (l_security_profile_id NUMBER,
905 l_effective_date DATE,
906 l_update_date DATE)
907 IS
908 BEGIN
909 --
910 hr_utility.set_location('hr_listgen.add_person_list_changes',10);
911 --
912 INSERT INTO per_person_list
913 (security_profile_id,
914 person_id,
915 request_id,
916 program_application_id,
917 program_id,
918 program_update_date )
919 SELECT DISTINCT l_security_profile_id,
920 plc.person_id,
921 p_request_id,
922 p_program_application_id,
923 p_program_id,
924 l_update_date
925 FROM per_person_list_changes plc
926 WHERE plc.security_profile_id = l_security_profile_id
927 AND NOT EXISTS
928 (SELECT NULL
929 FROM per_all_assignments_f pos
930 WHERE pos.person_id = plc.person_id
931 AND pos.assignment_type <> 'B'
932 AND l_effective_date
933 BETWEEN pos.effective_start_date
934 AND pos.effective_end_date)
935 AND EXISTS
936 (SELECT NULL
937 FROM per_all_assignments_f pos
938 WHERE pos.person_id = plc.person_id
939 AND l_effective_date > pos.effective_start_date)
940 AND NOT EXISTS
941 (SELECT NULL
942 FROM per_person_list ppl
943 WHERE ppl.person_id = plc.person_id
944 AND ppl.granted_user_Id IS NULL
945 AND ppl.security_profile_id = plc.security_profile_id);
946 --
947 hr_utility.set_location('hr_listgen.add_person_list_changes',20);
948 --
949 END add_person_list_changes;
950
951
952 /* =======================================================================
953 NAME
954 create_person_list
955 DESCRIPTION
956 populates the per_person_list using dynamic sql
957 PARAMETERS
958 Few parameters are needed due it inheriting from the parent function
959 sec_rec - Row in per_security_profiles for current profile
960 ========================================================================= */
961 --
962
963 PROCEDURE create_person_list(sec_rec PER_SECURITY_PROFILES%ROWTYPE,
964 p_effective_date date,
965 p_update_date date,
966 p_who_to_process varchar2)
967 IS
968 l_select_text varchar2(500);
969 l_where_clause varchar2(3000);
970 l_restriction_flags varchar2(1000);
971 l_execution_stmt varchar2(8500);
972 l_execution_stmt2 varchar2(8500);
973 l_exclude_flags varchar2(1000);
974
975 l_sec_rec_security_profile_id varchar2(2000);
976
977 -- for the bug 5214715
978
979 PROCEDURE execute_statement
980 AS
981 l_cursor_id NUMBER;
982 l_dsql_text VARCHAR2(32767);
983 l_num_of_rows NUMBER;
984 l_num number(10);
985 j number(10):=1;
986 i number(10):=50;
987 BEGIN
988 hr_utility.set_location('Entering execute_statement',10);
989 l_cursor_id := dbms_sql.open_cursor;
990 fnd_dsql.set_cursor(l_cursor_id);
991
992 l_dsql_text := fnd_dsql.get_text(FALSE);
993 l_num := length(l_dsql_text);
994
995 /* -- use to print the final sql query
996 while j < l_num +50
997 loop
998 hr_utility.set_location(substr(l_dsql_text,j,i),10);
999 -- hr_utility.set_location('-------------------------',20);
1000 -- dbms_output.put_line(substr(l_dsql_text,i));
1001 j := j+50;
1002 end loop;
1003 */
1004 dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
1005 hr_utility.set_location('after parse',10);
1006 fnd_dsql.do_binds;
1007 hr_utility.set_location('after bind',10);
1008 l_num_of_rows := dbms_sql.execute(l_cursor_id);
1009 hr_utility.set_location('after execuate ',10);
1010 dbms_sql.close_cursor(l_cursor_id);
1011 hr_utility.set_location('Leaveing execute_statement',10);
1012 END execute_statement;
1013
1014 /*-- for the bug 5214715 --*/
1015 PROCEDURE add_comm_str ( p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE ) as
1016 l_restriction_flags varchar2(2000);
1017 l_exclude_flags varchar2(2000);
1018 BEGIN
1019 hr_utility.set_location('Entering add_comm_str',10);
1020 l_restriction_flags :='';
1021 if (p_sec_rec.view_all_cwk_flag = 'N') then
1022 if length(l_restriction_flags)>0 then
1023 l_restriction_flags:=l_restriction_flags||' OR ';
1024 end if;
1025 l_restriction_flags:=l_restriction_flags||' ASSIGNMENT.assignment_type=''C''';
1026 end if;
1027
1028 if (p_sec_rec.view_all_employees_flag = 'N') then
1029 if length(l_restriction_flags)>0 then
1030 l_restriction_flags:=l_restriction_flags||' OR ';
1031 end if;
1032 l_restriction_flags:=l_restriction_flags||' ASSIGNMENT.assignment_type=''E''';
1033 end if;
1034
1035 if (p_sec_rec.view_all_applicants_flag = 'N') then
1036 /*
1037 ** Change in logic due to bug 3024532. Process Applicant assignments
1038 ** regardless of the employee/Cwk restriction. This means that EMP-APL
1039 ** are visible to a profile by virtue of Appl assignment even if Emp
1040 ** assignment is not visible.
1041 */
1042 if length(l_restriction_flags)>0 then
1043 l_restriction_flags:=l_restriction_flags||' OR ';
1044 end if;
1045 l_restriction_flags:=l_restriction_flags||
1046 '( ASSIGNMENT.assignment_type=''A'' )';
1047 end if;
1048
1049 if l_restriction_flags is not null or
1050 length(l_restriction_flags) = 0
1051 then
1052 fnd_dsql.add_text(' and ( ');
1053 fnd_dsql.add_text(l_restriction_flags);
1054 fnd_dsql.add_text(' ) ');
1055 end if;
1056
1057 l_exclude_flags :='';
1058 if (p_sec_rec.view_all_cwk_flag = 'X') then
1059 if length(l_exclude_flags)>0 then
1060 l_exclude_flags:=l_exclude_flags||' AND ';
1061 end if;
1062 l_exclude_flags:=l_exclude_flags||'ASSIGNMENT.assignment_type<>''C''';
1063 end if;
1064 --
1065 if (p_sec_rec.view_all_employees_flag = 'X') then
1066 if length(l_exclude_flags)>0 then
1067 l_exclude_flags:=l_exclude_flags||' AND ';
1068 end if;
1069 l_exclude_flags:=l_exclude_flags||'ASSIGNMENT.assignment_type<>''E''';
1070 end if;
1071 --
1072 if (p_sec_rec.view_all_applicants_flag = 'X') then
1073 if length(l_exclude_flags)>0 then
1074 l_exclude_flags:=l_exclude_flags||' AND ';
1075 end if;
1076 l_exclude_flags:=l_exclude_flags||
1077 '(ASSIGNMENT.assignment_type<>''A'' )';
1078 end if;
1079 --
1080 if l_exclude_flags is not null or
1081 length(l_exclude_flags) = 0
1082 then
1083 fnd_dsql.add_text(' and ( ');
1084 fnd_dsql.add_text(l_exclude_flags);
1085 fnd_dsql.add_text(' ) ');
1086 end if;
1087 hr_utility.set_location('Leaveing add_comm_str',10);
1088 END add_comm_str;
1089
1090 /*-- for the bug 5214715 --*/
1091 -- XXCUSTOM - procedure to add static insert statement text
1092 PROCEDURE init_statement
1093 (
1094 -- p_person_id IN NUMBER
1095 p_request_id IN NUMBER
1096 ,p_prog_appl_id IN NUMBER
1097 ,p_program_id IN NUMBER
1098 ,p_update_date IN DATE
1099 -- ,p_from_clause IN VARCHAR2
1100 --- ,p_generation_scope IN VARCHAR2
1101 -- ,p_business_group_id IN NUMBER
1102 -- ,p_assignment_type IN VARCHAR2
1103 ,p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE
1104 )
1105 AS
1106 BEGIN
1107 hr_utility.set_location('Entering init_statement',10);
1108 fnd_dsql.init;
1109
1110 fnd_dsql.add_text(
1111 'INSERT into per_person_list
1112 (security_profile_id,
1113 person_id,
1114 request_id,
1115 program_application_id,
1116 program_id,
1117 program_update_date ) ');
1118
1119 -- Add the insert statement and binds
1120 fnd_dsql.add_text( ' SELECT DISTINCT ');
1121 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122 fnd_dsql.add_text(' , assignment.person_id ');
1123 fnd_dsql.add_text(' ,');
1124 fnd_dsql.add_bind(nvl(p_request_id,''));
1125 fnd_dsql.add_text(' , ');
1126 fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127 fnd_dsql.add_text(' , ');
1128 fnd_dsql.add_bind(nvl(p_program_id,''));
1129 fnd_dsql.add_text(' , ');
1130 fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131 fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1132
1133 /*-------- additional select clause ---------------*/
1134
1135 if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1136 (p_sec_rec.view_all_applicants_flag = 'N'
1137 and (p_sec_rec.view_all_employees_flag <>'Y'
1138 or p_sec_rec.view_all_cwk_flag <>'Y')) then
1139 fnd_dsql.add_text(', per_all_people_f PERSON ');
1140 End if;
1141
1142 if p_sec_rec.view_all_organizations_flag='N' then
1143 Fnd_dsql.add_text(', per_organization_list ol ');
1144 end if;
1145
1146 if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1147 Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1148 end if;
1149
1150 if p_sec_rec.view_all_positions_flag='N' then
1151 fnd_dsql.add_text(', per_position_list pl ');
1152 end if;
1153
1154 if p_sec_rec.view_all_payrolls_flag='N' then
1155 fnd_dsql.add_text( ' , pay_payroll_list ppl ');
1156 end if;
1157
1158 if p_sec_rec.view_all_organizations_flag='Y' then
1159 null;
1160 end if;
1161 /*------------------ end additional select clause -----------------*/
1162
1163 /*-------------- start where clause -------------------*/
1164 fnd_dsql.add_text(' Where ');
1165 fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1166
1167 if p_sec_rec.business_group_id is null then
1168 fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1169 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1170 else
1171 fnd_dsql.add_bind(p_sec_rec.business_group_id);
1172 end if;
1173
1174 if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1175 (p_sec_rec.view_all_applicants_flag = 'N'
1176 and (p_sec_rec.view_all_employees_flag <>'Y'
1177 or p_sec_rec.view_all_cwk_flag <>'Y')) then
1178
1179 fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1180 fnd_dsql.add_text(' ( '); -- 5214715
1181 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182 fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183 /* Got a fresh future person */
1184 fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1186 fnd_dsql.add_text(' AND NOT EXISTS
1187 (SELECT NULL
1188 FROM per_all_people_f papf1
1189 WHERE papf1.person_id = PERSON.person_id
1190 AND papf1.effective_start_date < PERSON.effective_start_date)) ');
1191
1192 fnd_dsql.add_text(' ) '); -- 5214715
1193 End if;
1194
1195 if p_sec_rec.view_all_organizations_flag='N' then
1196 fnd_dsql.add_text(' and ol.security_profile_id = ');
1197 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1198 fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');
1199 end if;
1200
1201 if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1202 fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1203 fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1204 fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1205 end if;
1206
1207 if p_sec_rec.view_all_positions_flag='N' then
1208 fnd_dsql.add_text(' and pl.security_profile_id = ');
1209 fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1210 fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');
1211 end if;
1212
1213 if p_sec_rec.view_all_payrolls_flag='N' then
1214 fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1215 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1216 fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1217 fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');
1218 end if;
1219
1220 if p_sec_rec.view_all_organizations_flag='Y' then
1221 null;
1222 end if;
1223 /*-------------- End where clause -------------------*/
1224 hr_utility.set_location('Leaveing init_statement',10);
1225 end init_statement;
1226
1227 BEGIN
1228 hr_utility.set_location('Entering Craete_person_list',10);
1229 init_statement
1230 (
1231 --p_person_id => p_person_id
1232 p_request_id => p_request_id
1233 ,p_prog_appl_id => p_program_application_id
1234 ,p_program_id => p_program_id
1235 ,p_update_date => p_update_date
1236 -- ,p_from_clause => p_from_clause
1237 -- ,p_generation_scope => p_generation_scope
1238 -- ,p_business_group_id => p_business_group_id
1239 -- ,p_assignment_type => p_assignment_type
1240 ,p_sec_rec => sec_rec
1241 );
1242
1243
1244 -- Selects what type of assignment records we are interested in
1245 -- (Only ones where the relevant flag is N (Restricted))
1246 --
1247 -- Also adds check to make sure the relevant number for the assignment is not
1248 -- null ie for assignment_type of E, PERSON.employee_number must not be null
1249 --
1250
1251 /*--------- Start for l_restriction_flags and l_exclude_flags ----------------*/
1252
1253 add_comm_str( p_sec_rec => sec_rec );
1254
1255 /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1256
1257 fnd_dsql.add_text(' and ( ');
1258 fnd_dsql.add_text(' ( '); -- 5214715
1259 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260 fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261 or ( ASSIGNMENT.effective_start_date>= ');
1262 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263 fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264 FROM per_all_assignments_f pos1
1265 WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1267 fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1268 fnd_dsql.add_text('
1269 AND NOT EXISTS
1270 (SELECT NULL
1271 FROM per_all_assignments_f pos1
1272 WHERE pos1.person_id = ASSIGNMENT.person_id
1273 AND ((pos1.assignment_type=''E'' and
1274 pos1.period_of_service_id=ASSIGNMENT.period_of_service_id) or
1275 (pos1.assignment_type=''A'' and
1276 pos1.application_id=ASSIGNMENT.application_id) or
1277 (pos1.assignment_type=''C'' and
1278 pos1.period_of_placement_date_start =
1279 ASSIGNMENT.period_of_placement_date_start))
1280 AND pos1.effective_start_date< ASSIGNMENT.effective_start_date)
1281 or (ASSIGNMENT.effective_end_date < ');
1282
1283 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1284 -- added and clause for bug 5168364
1285 fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1286 from per_all_assignments_f asg
1287 where asg.person_id = assignment.person_id
1288 and asg.assignment_type in(''A'',''C'',''E'')
1289 ) ');
1290 fnd_dsql.add_text(' AND NOT EXISTS
1291 (SELECT NULL
1292 FROM per_all_assignments_f papf
1293 WHERE papf.person_Id = ASSIGNMENT.person_id
1294 AND papf.assignment_type in(''A'',''C'',''E'')
1295 AND papf.effective_end_date >= ');
1296 fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1297 fnd_dsql.add_text(' ))))'); -- 5214715
1298
1299 fnd_dsql.add_text(' and not exists(select 1
1300 from per_person_list ppl
1301 where ppl.security_profile_id = ');
1302 fnd_dsql.add_bind(sec_rec.security_profile_id);
1303 fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1304 and ppl.granted_user_id is null)');
1305
1306 -- if the custom sql flag is set and the restriction text is not empty
1307 -- (>2 chars) then append the custom sql to the end of the statement
1308
1309 if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1310 fnd_dsql.add_text(' and ');
1311 fnd_dsql.add_text(sec_rec.restriction_text);
1312 end if;
1313
1314 if g_debug then
1315 hr_utility.trace('select '||to_char(length(l_select_text)));
1316 hr_utility.trace('where '||to_char(length(l_where_clause)));
1317 l_execution_stmt2:=l_execution_stmt;
1318 while length(l_execution_stmt2)>0 loop
1319 hr_utility.trace(substr(l_execution_stmt2,1,70));
1320 l_execution_stmt2:=substr(l_execution_stmt2,71);
1321 end loop;
1322 end if;
1323
1324 Execute_statement;
1325 hr_utility.set_location('Leaveing Craete_person_list',10);
1326 END create_person_list;
1327
1328
1329 --
1330 -- ----------------------------------------------------------------------------
1331 -- |---------------------< clear_per_list_table >-----------------------------|
1332 -- ----------------------------------------------------------------------------
1333 --
1334 procedure clear_per_list_table (
1335 p_person_id number,
1336 p_generation_scope varchar2,
1337 p_business_group_id number,
1338 p_effective_date date) is
1339 --
1340 l_proc varchar2(72) := g_package||'clear_per_list_table';
1341 --
1342 l_sub_str varchar2(1000);
1343 l_del_str varchar2(8000);
1344 l_exe_str varchar2(9000);
1345
1346 --
1347 begin
1348 --
1349 hr_utility.set_location('Entering : '||l_proc,10);
1350 --
1351 -- Clear the records for this person.
1352
1353 -- Bug6809753 - start
1354 l_del_str := ' delete from per_person_list ppl where
1355 ppl.person_id = '||p_person_id||'
1356 and ppl.granted_user_id is null and exists
1357 (select ''X'' from per_security_profiles pspf
1358 where pspf.security_profile_id = ppl.security_profile_id ';
1359 -- Bug6809753 - end
1360
1361 if p_generation_scope = 'ALL_BUS_GRP' then
1362
1363 /* Commented Bug6809753 - start
1364 delete from per_person_list ppl where
1365 ppl.person_id = p_person_id
1366 and ppl.granted_user_id is null and exists
1367 (select 'X' from per_security_profiles pspf
1368 where pspf.security_profile_id = ppl.security_profile_id
1369 and pspf.business_group_id = p_business_group_id);
1370 Commented Bug6809753 - end */
1371
1372 -- Bug6809753 - start
1373 l_sub_str := ' and pspf.business_group_id = '||p_business_group_id||' ';
1374 -- Bug6809753 - end
1375
1376 end if;
1377 if p_generation_scope = 'ALL_GLOBAL' then
1378
1379 /* Commented Bug6809753 - start
1380 delete from per_person_list ppl where
1381 ppl.person_id = p_person_id
1382 and ppl.granted_user_id is null and exists
1383 (select 'X' from per_security_profiles pspf
1384 where pspf.security_profile_id = ppl.security_profile_id
1385 and pspf.business_group_id is null);
1386 Commented Bug6809753 - end */
1387
1388 -- Bug6809753 - start
1389 l_sub_str := ' and pspf.business_group_id is null ';
1390 -- Bug6809753 - end
1391
1392 end if;
1393 if p_generation_scope = 'ALL_PROFILES' then
1394
1395 /* Commented Bug6809753 - start
1396 delete from per_person_list ppl where
1397 ppl.person_id = p_person_id
1398 and ppl.granted_user_id is null and exists
1399 (select 'X' from per_security_profiles pspf
1400 where pspf.security_profile_id = ppl.security_profile_id);
1401 Commented Bug6809753 - end */
1402
1403 -- Bug6809753 - start
1404 l_sub_str := '';
1405 -- Bug6809753 - end
1406
1407 end if;
1408 -- un commanted the below line - Bug6809753
1409 l_exe_str := l_del_str||l_sub_str||')';
1410
1411 execute immediate l_exe_str; -- Bug6809753
1412
1413 -- Clear records for the contacts of this person
1414 -- 2906862 - dkerr 2003-05-01
1415 -- I've restricted the scan of PER_SECURITY_PROFILES to restricted
1416 -- contact profiles - which can drastically reduce the amount of I/O
1417 -- performed.
1418 -- Analysis of major customer data suggests the following :
1419 -- 1. Security profiles with "view_all_contacts_flag='N'"
1420 -- are usually a minority.
1421 -- 2. A typical installation may have hundreds of security
1422 -- profiles.
1423 --
1424
1425 /* Commented Bug6809753 - start
1426
1427 if p_generation_scope = 'ALL_BUS_GRP' then
1428
1429 delete from per_person_list ppl
1430 where ppl.security_profile_id in
1431 (select pspf.security_profile_id
1432 from per_security_profiles pspf
1433 where (pspf.view_all_contacts_flag = 'N' or
1434 (pspf.view_all_contacts_flag = 'Y' and
1435 pspf.view_all_candidates_flag = 'X'))
1436 and pspf.business_group_id = p_business_group_id
1437 )
1438 and ppl.person_id in (
1439 select pcr.contact_person_id
1440 from per_contact_relationships pcr,
1441 per_person_type_usages_f ptu,
1442 per_person_types ppt
1443 where pcr.person_id = p_person_id
1444 and pcr.contact_person_id = ptu.person_id
1445 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1446 between ptu.effective_start_date
1447 and ptu.effective_end_date
1448 and ptu.person_type_id = ppt.person_type_id
1449 and ppt.system_person_type = 'OTHER')
1450 and ppl.granted_user_id is null;
1451
1452
1453 End if;
1454 if p_generation_scope = 'ALL_GLOBAL' then
1455 delete from per_person_list ppl
1456 where ppl.security_profile_id in
1457 (select pspf.security_profile_id
1458 from per_security_profiles pspf
1459 where (pspf.view_all_contacts_flag = 'N' or
1460 (pspf.view_all_contacts_flag = 'Y' and
1461 pspf.view_all_candidates_flag = 'X'))
1462 and pspf.business_group_id is null
1463 )
1464 and ppl.person_id in (
1465 select pcr.contact_person_id
1466 from per_contact_relationships pcr,
1467 per_person_type_usages_f ptu,
1468 per_person_types ppt
1469 where pcr.person_id = p_person_id
1470 and pcr.contact_person_id = ptu.person_id
1471 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1472 between ptu.effective_start_date
1473 and ptu.effective_end_date
1474 and ptu.person_type_id = ppt.person_type_id
1475 and ppt.system_person_type = 'OTHER')
1476 and ppl.granted_user_id is null;
1477 End if;
1478 if p_generation_scope = 'ALL_PROFILES' then
1479 delete from per_person_list ppl
1480 where ppl.security_profile_id in
1481 (select pspf.security_profile_id
1482 from per_security_profiles pspf
1483 where (pspf.view_all_contacts_flag = 'N' or
1484 (pspf.view_all_contacts_flag = 'Y' and
1485 pspf.view_all_candidates_flag = 'X'))
1486 )
1487 and ppl.person_id in (
1488 select pcr.contact_person_id
1489 from per_contact_relationships pcr,
1490 per_person_type_usages_f ptu,
1491 per_person_types ppt
1492 where pcr.person_id = p_person_id
1493 and pcr.contact_person_id = ptu.person_id
1494 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1495
1496 between ptu.effective_start_date
1497 and ptu.effective_end_date
1498 and ptu.person_type_id = ppt.person_type_id
1499 and ppt.system_person_type = 'OTHER')
1500 and ppl.granted_user_id is null;
1501 End if;
1502
1503 Commented Bug6809753 - end */
1504 --
1505 -- Bug6809753 -start
1506 l_del_str := ' delete from per_person_list ppl
1507 where ppl.security_profile_id in
1508 (select pspf.security_profile_id
1509 from per_security_profiles pspf
1510 where (pspf.view_all_contacts_flag = ''N'' or
1511 (pspf.view_all_contacts_flag = ''Y'' and
1512 pspf.view_all_candidates_flag = ''X'')) '
1513 ||l_sub_str||')
1514 and ppl.person_id in (
1515 select pcr.contact_person_id
1516 from per_contact_relationships pcr,
1517 per_person_type_usages_f ptu,
1518 per_person_types ppt
1519 where pcr.person_id = '||p_person_id||
1520 ' and pcr.contact_person_id = ptu.person_id
1521 and to_date('''||to_char(p_effective_date,
1522 'dd/mm/yyyy')||''', ''dd/mm/yyyy'')
1523 between ptu.effective_start_date
1524 and ptu.effective_end_date
1525 and ptu.person_type_id = ppt.person_type_id
1526 and ppt.system_person_type = ''OTHER'')
1527 and ppl.granted_user_id is null';
1528 --
1529 l_exe_str := l_del_str;
1530 --
1531 execute immediate l_exe_str;
1532 -- Bug6809753 -end
1533
1534 hr_utility.set_location('Leaving : '||l_proc,20);
1535 --
1536 end clear_per_list_table;
1537 --
1538 --
1539 -- ----------------------------------------------------------------------------
1540 -- |----------------------< clear_unrelated_contacts >------------------------|
1541 -- ----------------------------------------------------------------------------
1542 --
1543 procedure clear_unrelated_contacts (
1544 p_generation_scope in varchar2,
1545 p_business_group_id in number
1546 ) is
1547 --
1548 l_proc varchar2(72) := g_package||'clear_unrelated_contacts';
1549 --
1550 begin
1551 --
1552 hr_utility.set_location('Entering : '||l_proc,10);
1553 --
1554 if p_generation_scope <> 'ALL_BUS_GRP' then
1555
1556 delete from per_person_list ppl
1557 where ppl.security_profile_id in
1558 (select pspf.security_profile_id
1559 from per_security_profiles pspf
1560 where (pspf.view_all_contacts_flag = 'N' or
1561 (pspf.view_all_contacts_flag = 'Y' and
1562 pspf.view_all_candidates_flag = 'X')))
1563 /* and ppl.person_id in
1564 (select papf.person_id
1565 from per_all_people_f papf
1566 where papf.person_id = ppl.person_id
1567 and ((p_generation_scope = 'ALL_BUS_GRP' and
1568 papf.business_group_id = p_business_group_id) or
1569 p_generation_scope <> 'ALL_BUS_GRP')) */
1570 and not exists
1571 (select null
1572 from per_all_assignments_f asg
1573 where asg.person_id = ppl.person_id)
1574 and not exists
1575 (select null
1576 from per_contact_relationships pcr
1577 where pcr.contact_person_id = ppl.person_id);
1578
1579 else
1580 delete from per_person_list ppl
1581 where ppl.security_profile_id in
1582 (select pspf.security_profile_id
1583 from per_security_profiles pspf
1584 where (pspf.view_all_contacts_flag = 'N' or
1585 (pspf.view_all_contacts_flag = 'Y' and
1586 pspf.view_all_candidates_flag = 'X')))
1587 and ppl.person_id in
1588 (select papf.person_id
1589 from per_all_people_f papf
1590 where papf.person_id = ppl.person_id
1591 and papf.business_group_id = p_business_group_id)
1592 and not exists
1593 (select null
1594 from per_all_assignments_f asg
1595 where asg.person_id = ppl.person_id)
1596 and not exists
1597 (select null
1598 from per_contact_relationships pcr
1599 where pcr.contact_person_id = ppl.person_id);
1600 end if;
1601 --
1602 hr_utility.set_location('Leaving : '||l_proc,20);
1603 --
1604 end clear_unrelated_contacts;
1605 --
1606 -- ----------------------------------------------------------------------------
1607 -- |---------------------< clear_sp_list_table >------------------------------|
1608 -- ----------------------------------------------------------------------------
1609 --
1610 procedure clear_sp_list_table (
1611 p_generation_scope varchar2,
1612 p_business_group_id number,
1613 p_security_profile_id number,
1614 p_clear_people_flag boolean
1615 ) is
1616 --
1617 l_proc varchar2(72) := g_package||'clear_sp_list_table';
1618 --
1619 begin
1620 --
1621 hr_utility.set_location('Entering : '||l_proc, 10);
1622 -- Clearing Organization static list
1623 delete
1624 from per_organization_list
1625 where security_profile_id = p_security_profile_id
1626 and user_id is null;
1627 --
1628 hr_utility.set_location(l_proc, 20);
1629 -- Clearing Position static list
1630 delete
1631 from per_position_list
1632 where security_profile_id = p_security_profile_id
1633 and user_id is null;
1634 --
1635 hr_utility.set_location(l_proc, 30);
1636 -- Clearing the Person static list
1637 if p_clear_people_flag then
1638 --
1639 delete
1640 from per_person_list
1641 where security_profile_id = p_security_profile_id
1642 and granted_user_id is null;
1643 --
1644 end if;
1645 --
1646 hr_utility.set_location(l_proc, 40);
1647 -- Clearing Payroll static list
1648 delete
1649 from pay_payroll_list
1650 where security_profile_id = p_security_profile_id;
1651 --
1652 hr_utility.set_location('Leaving : '||l_proc, 99);
1653 --
1654 end clear_sp_list_table;
1655 --
1656 /* =======================================================================
1657 NAME
1658 process_person
1659 DESCRIPTION
1660
1661 PARAMETERS
1662 l_effective_date - date for which the secure lists are generated.
1663 ========================================================================= */
1664 --
1665 PROCEDURE process_person (p_person_id per_all_people_f.person_id%TYPE,
1666 p_effective_date date,
1667 p_business_group_id number,
1668 p_generation_scope varchar2,
1669 p_who_to_process varchar2)
1670 IS
1671 l_proc varchar2(50) := g_package||'process_person';
1672 l_effective_date date;
1673
1674 /*
1675 ** Notes on this cursor:
1676 **
1677 ** Returns people who are :
1678 ** Current on effective date.
1679 ** Current at somepoint in the future and either don't exist today
1680 ** or are 'ex' people today.
1681 **
1682 ** The date returned is the greatest out of the calculated ESD and the
1683 ** effective date.
1684 **
1685 ** For a current applicant who is hired in the future then that person
1686 ** will have PTU data which causes them to qualify against both these
1687 ** conditions. The min(esd) will result in only one of those PTU records
1688 ** to be taken into account and so the correct date is calculated.
1689 */
1690 /* Commented Bug6809753 - start
1691 cursor c_current_person is
1692 select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
1693 from per_person_type_usages_f ppf,
1694 per_person_types ppt
1695 where ppf.person_id = p_person_id
1696 and ppf.person_type_id = ppt.person_type_id --taken out for Performance bug.
1697 -- Current person today
1698 and (( -- ppf.person_type_id = ppt.person_type_id and
1699 ppt.business_group_id =
1700 nvl(p_business_group_id,ppt.business_group_id)
1701 and p_effective_date between ppf.effective_start_date
1702 and ppf.effective_end_date
1703 and ppt.system_person_type in ('EMP','APL','CWK'))
1704 OR
1705 -- Future person
1706 ( -- ppf.person_type_id = ppt.person_type_id and
1707 ppt.business_group_id =
1708 nvl(p_business_group_id,ppt.business_group_id)
1709 and p_effective_date < ppf.effective_start_date
1710 and ppt.system_person_type in ('EMP','APL','CWK')))
1711 group by ppf.person_id;
1712 Commented Bug6809753 - end */
1713
1714 -- Bug6809753 - start
1715 cursor c_current_person is
1716 select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
1717 from per_person_type_usages_f ppf,
1718 per_person_types ppt
1719 where ppf.person_id = p_person_id
1720 /*
1721 ** Current person today
1722 */
1723 and (( ppf.person_type_id = ppt.person_type_id
1724 and ppt.business_group_id =
1725 nvl(p_business_group_id,ppt.business_group_id)
1726 and p_effective_date between ppf.effective_start_date
1727 and ppf.effective_end_date
1728 and ppt.system_person_type in ('EMP','APL','CWK'))
1729 OR
1730 /*
1731 ** Future person
1732 */
1733 ( ppf.person_type_id = ppt.person_type_id
1734 and ppt.business_group_id =
1735 nvl(p_business_group_id,ppt.business_group_id)
1736 and p_effective_date < ppf.effective_start_date
1737 and ppt.system_person_type in ('EMP','APL','CWK')))
1738 group by ppf.person_id;
1739
1740 /*-- Start changes made for the bug 5252738 - Bug6809753 ---*/
1741 /* ** To exclude Current person today */
1742
1743 cursor c_exclude_person is
1744 select ppf.person_id
1745 from per_person_type_usages_f ppf,
1746 per_person_types ppt
1747 where ppf.person_id = p_person_id
1748
1749 and ( ppf.person_type_id = ppt.person_type_id
1750 and ppt.business_group_id =
1751 nvl(p_business_group_id,ppt.business_group_id)
1752 and p_effective_date between ppf.effective_start_date
1753 and ppf.effective_end_date
1754 and ppt.system_person_type in ('EMP','APL','CWK'));
1755 /*-- End changes made for the bug 5252738 -Bug6809753 ---*/
1756 -- Bug6809753 - End
1757
1758 cursor c_former_person is
1759 select ppf.person_id, paf.assignment_type,
1760 least(max(paf.effective_end_date), p_effective_date) effective_date
1761 from per_person_type_usages_f ppf,
1762 per_person_types ppt,
1763 per_all_assignments_f paf
1764 where ppf.person_id = p_person_id
1765 and ppf.person_id = paf.person_id
1766 and paf.assignment_type in ('A','C','E')
1767 and paf.effective_start_date < p_effective_date
1768 /*
1769 ** Existed as a current person at somepoint in history
1770 */
1771 and ( ppf.person_type_id = ppt.person_type_id
1772 and p_effective_date > ppf.effective_start_date
1773 and ppt.system_person_type in ('EMP','APL','CWK'))
1774 /*
1775 ** ...as an ex person on the effective date
1776 */
1777 and exists (select null
1778 from per_person_type_usages_f ppf1,
1779 per_person_types ppt1
1780 where ppf1.person_id = ppf.person_id
1781 and p_effective_date between ppf1.effective_start_date
1782 and ppf1.effective_end_date
1783 and ppf1.person_type_id = ppt1.person_type_id
1784 and ppt1.business_group_id = nvl(p_business_group_id,
1785 ppt1.business_group_id)
1786 and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
1787 /*
1788 ** ...and not a current person on effective date or in the future.
1789 **
1790 ** (Due to the implementation of PTU I can be both EMP and EX-APL
1791 ** today. i.e. I'm an employee who was successfully hired after
1792 ** some application process. In this case the person should be
1793 ** processed as a current and not an ex person. Note the
1794 ** exception for APLs who are former EMPs/CWKs - in this
1795 ** case an APL who is also term'd should be visible as both an
1796 ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
1797 ** who are EX-EMP/EX-CWK but who are also APL
1798 */
1799 and not exists (select null
1800 from per_person_type_usages_f ppf2,
1801 per_person_types ppt2
1802 where ppf2.person_id = ppf.person_id
1803 and p_effective_date < ppf2.effective_end_date
1804 and ppf2.person_type_id = ppt2.person_type_id
1805 and ppt2.business_group_id = nvl(p_business_group_id,
1806 ppt2.business_group_id)
1807 and ppt2.system_person_type in ('EMP','CWK'))
1808 group by ppf.person_id, paf.assignment_type
1809 order by effective_date desc;
1810
1811 cursor c_get_asg(p_person_id number,
1812 p_effective_date date) is
1813 select paf.assignment_id, paf.effective_start_date
1814 from per_all_assignments_f paf
1815 where paf.person_id = p_person_id
1816 and paf.assignment_type not in ('B','O') -- added from bug 4352765, Bug 7412855
1817 and ( (p_effective_date between paf.effective_start_date
1818 and paf.effective_end_date)
1819 or
1820 (paf.effective_start_date > p_effective_date and
1821 not exists (select null
1822 from per_all_assignments_f paf1
1823 where paf1.assignment_id = paf.assignment_id
1824 and paf1.effective_start_date <=
1825 paf.effective_start_date)));
1826
1827 cursor c_is_current_apl is
1828 select 'Y'
1829 from per_person_type_usages_f ptu,
1830 per_person_types ppt
1831 where ptu.person_id = p_person_id
1832 and p_effective_date < ptu.effective_end_date
1833 and ptu.person_type_Id = ppt.person_type_id
1834 and ppt.system_person_type = 'APL';
1835 cursor c_is_former is
1836 select 'Y'
1837 from per_person_type_usages_f ptu,
1838 per_person_types ppt
1839 where ptu.person_id = p_person_id
1840 and p_effective_date between ptu.effective_start_date
1841 and ptu.effective_end_date
1842 and ptu.person_type_id = ppt.person_type_id
1843 and ppt.system_person_type in ('EX_EMP','EX_CWK');
1844
1845 l_is_current_apl varchar2(1) := 'N';
1846 l_is_former varchar2(1) := 'N';
1847
1848 l_person_id number;
1849 l_assignment_type PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE%TYPE;
1850 l_person_proc_date date;
1851 lc_person_id number(10); -- Bug6809753
1852 l_cleared_ppl boolean := FALSE;
1853
1854 BEGIN
1855
1856 hr_utility.set_location('Entering : '||l_proc,10);
1857 hr_utility.set_location('p_person_id '||p_person_id,15);
1858 hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
1859 'DD-MON-YYYY'),20);
1860 hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
1861 hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
1862
1863 /*
1864 ** We need to find out quickly if the person is an APL today or in the
1865 ** future so we can control processing later.
1866 open c_is_current_apl;
1867 fetch c_is_current_apl into l_is_current_apl;
1868 close c_is_current_apl;
1869 */
1870 /*
1871 ** We need to find out if the person is current any 'EX' type
1872 open c_is_former;
1873 fetch c_is_former into l_is_former;
1874 close c_is_former;
1875 */
1876
1877 hr_utility.set_location(l_proc,40);
1878
1879 if p_who_to_process in ('CURRENT','ALL')
1880 -- or ( p_who_to_process = 'TERM'
1881 -- and l_is_current_apl = 'Y'
1882 -- and l_is_former = 'Y')
1883 then
1884
1885 /*
1886 ** The current implementation coded below has one flaw when processing a former
1887 ** EMP/CWK who as applied for a job and is an APL with who to process as ALL. In
1888 ** this case the person will be processed three times.
1889 ** 1) As an APL by the current_person cursor.
1890 ** 2) As an APL by the former_person cursor.
1891 ** 3) As an EX-EMP/EX-CWK by the former_person cursor.
1892 **
1893 ** Optimizations for this issue will be considered at a later date. If this comment
1894 ** is still in the file then you know the optimization has not yet been completed.
1895 */
1896
1897 hr_utility.set_location(l_proc,50);
1898 /*
1899 ** We are processing current EMP/APL/CWK or
1900 ** we are processing former EMP/APL/CWK but the person we are dealing
1901 ** with is both a current APL and a former EMP/CWK in which case we
1902 ** also need to process them here. This is because an APL should also
1903 ** be visible to the profiles who could see them when EMP/CWK.
1904 **
1905 ** In this section will determine whether the person is EMP/APL/CWK now or
1906 ** in the future and process them accordingly on the relevant date.
1907 */
1908 open c_current_person;
1909 fetch c_current_person into l_person_id, l_person_proc_date;
1910
1911 if c_current_person%found then
1912 close c_current_person;
1913 /*
1914 ** Clear out the person list for this person_id
1915 */
1916 clear_per_list_table(p_person_id => p_person_id,
1917 p_business_group_id => p_business_group_id,
1918 p_generation_scope => p_generation_scope,
1919 p_effective_date => p_effective_date);
1920 l_cleared_ppl := TRUE;
1921
1922 /*
1923 ** Populate the person list for each assignment that this person has.
1924 */
1925 for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
1926 hr_utility.set_location(l_proc,60);
1927 hr_utility.trace('processing assignment :'||
1928 to_char(l_asgrec.assignment_id));
1929 hr_security_internal.add_to_person_list(
1930 p_effective_date => l_person_proc_date,
1931 p_assignment_id => l_asgrec.assignment_id,
1932 p_business_group_id => p_business_group_id,
1933 p_generation_scope => p_generation_scope);
1934
1935 end loop;
1936 else
1937 close c_current_person;
1938 end if;
1939 end if;
1940
1941 if p_who_to_process in ('TERM','ALL')
1942 -- or ( p_who_to_process = 'CURRENT'
1943 -- and l_is_current_apl = 'Y'
1944 -- and l_is_former = 'Y')
1945 then
1946 hr_utility.set_location(l_proc,70);
1947 /*
1948 ** We are processing former EMP/APL/CWK or
1949 ** we are processing current EMP/APL/CWK but the person we are dealing
1950 ** with is both a current APL and a former EMP/CWK in which case we
1951 ** also need to process them here. This is because an APL should also
1952 ** be visible to the people who could see them when EMP/CWK.
1953 **
1954 ** In this section will determine whether the person is EX-EMP/EX-APL/EX-CWK
1955 ** now and process them accordingly on the relevant date. In this case the
1956 ** relevant date is the effective_end_date of the last assignment they had.
1957 ** Note that we process a person twice if they are EX-APL and EX-EMP/EX-CWK.
1958 */
1959 -- Bug6809753 -start
1960 /*-- Start changes made for the bug 5252738 - Bug6809753 ---*/
1961 open c_exclude_person;
1962 fetch c_exclude_person into lc_person_id;
1963
1964 if c_exclude_person%notfound then
1965 /*-- End changes made for the bug 5252738 - Bug6809753 ---*/
1966 -- Bug6809753 -end
1967
1968 for l_former_person in c_former_person loop
1969 hr_utility.set_location(l_proc,80);
1970 if l_cleared_ppl <> TRUE then
1971 /*
1972 ** Clear out the person list for this person_id if it's not been cleared
1973 ** already for this person.
1974 */
1975 clear_per_list_table(p_person_id => p_person_id,
1976 p_business_group_id => p_business_group_id,
1977 p_generation_scope => p_generation_scope,
1978 p_effective_date => p_effective_date);
1979 l_cleared_ppl := TRUE;
1980 end if;
1981 /*
1982 ** Populate the person list for each assignment that this person has.
1983 */
1984 for l_asgrec in c_get_asg(l_former_person.person_id,
1985 l_former_person.effective_date) loop
1986
1987 hr_utility.set_location(l_proc,90);
1988 hr_utility.trace('processing assignment :'||
1989 to_char(l_asgrec.assignment_id));
1990
1991 hr_security_internal.add_to_person_list(
1992 p_effective_date => l_former_person.effective_date,
1993 p_assignment_id => l_asgrec.assignment_id,
1994 p_business_group_id => p_business_group_id,
1995 p_generation_scope => p_generation_scope);
1996
1997 end loop;
1998 if l_former_person.assignment_type in ('E','C') then
1999 /*
2000 ** Exit if we've processed either an Emp or CWK. For an APL or EX-APL
2001 ** who is also EX-EMP/EX-CWK we will process the APL/EX_APL record then
2002 ** the EX-EMP/EX-CWK record then exit.
2003 ** For someone who is both EX-EMP and EX-CWK we only want to process the
2004 ** person for the EX-type they were last. i.e. an EMP leaves and returns
2005 ** as CWK then leaves they will be both EX-EMP and EX-CWK in the PTU
2006 ** table. We only want to process them as EX-CWK.
2007 */
2008 hr_utility.set_location(l_proc,100);
2009 exit;
2010 end if;
2011 end loop;
2012 -- Bug6809753 -start
2013 end if;
2014 close c_exclude_person; /*-- End changes made for the bug 5252738 - Bug6809753 ---*/
2015 -- Bug6809753 -end
2016 end if;
2017 /*
2018 ** Load up the contacts for this person
2019 */
2020 add_contacts_for_person(p_person_id => p_person_id,
2021 p_effective_date => p_effective_date,
2022 p_generation_scope => p_generation_scope,
2023 p_business_group_id => p_business_group_id);
2024
2025 hr_utility.set_location('Leaving : '||l_proc,110);
2026
2027 END process_person;
2028 --
2029 -- ----------------------------------------------------------------------------
2030 -- |---------------------< generate_opp_lists >-------------------------------|
2031 -- ----------------------------------------------------------------------------
2032 --
2033 procedure generate_opp_lists(
2034 p_effective_date in date
2035 ,p_generation_scope in varchar2
2036 ,p_business_group_id in number
2037 ) is
2038 --
2039 l_effective_date date;
2040 l_update_date date;
2041 l_found boolean default false;
2042 l_business_group_mode varchar2(20);
2043 l_proc varchar2(72) := g_package||'.generate_opp_lists';
2044 --
2045 cursor security_profiles is
2046 select *
2047 from per_security_profiles
2048 where ((business_group_id = p_business_group_id and
2049 p_generation_scope = 'ALL_BUS_GRP')
2050 or (business_group_id is null and
2051 p_generation_scope = 'ALL_GLOBAL')
2052 or (p_generation_scope = 'ALL_PROFILES')
2053 and org_security_mode in ('NONE','HIER'));
2054 --
2055 begin
2056 --
2057 -- Get the session date and the current date to avoid multiple selects.
2058 hr_utility.set_location('Entering '||l_proc, 10);
2059 hr_utility.set_location('Request ID '||p_request_id, 15);
2060 --
2061 l_effective_date := trunc(p_effective_date);
2062 l_update_date := trunc(sysdate);
2063 --
2064 hr_utility.set_location(l_proc, 20);
2065 --
2066 for sec_rec in security_profiles loop
2067 --
2068 -- Delete previous entries for the profile. By using this function
2069 -- we will be using the old generation mechanism so we need to clear
2070 -- the person list data upfront.
2071 clear_sp_list_table(p_generation_scope => p_generation_scope,
2072 p_business_group_id => sec_rec.business_group_id,
2073 p_security_profile_id => sec_rec.security_profile_id,
2074 p_clear_people_flag => false);
2075 --
2076 hr_utility.set_location(l_proc, 30);
2077 --
2078 if (sec_rec.view_all_flag = 'N') then
2079 --
2080 hr_utility.set_location(l_proc, 40);
2081 --
2082 if (sec_rec.view_all_payrolls_flag = 'N') then
2083 --
2084 hr_utility.set_location(l_proc, 50);
2085 --
2086 -- Build the payroll list.
2087 build_payroll_list(sec_rec.security_profile_id,
2088 sec_rec.business_group_id,
2089 sec_rec.include_exclude_payroll_flag,
2090 l_effective_date,
2091 l_update_date);
2092 --
2093 end if; -- view_all_payrolls_flag
2094 --
2095 -- Do not insert the orgs if using user-based security.
2096 if (sec_rec.view_all_organizations_flag = 'N' and
2097 nvl(sec_rec.top_organization_method, 'S') <> 'U') then
2098 --
2099 hr_utility.set_location(l_proc, 60);
2100 --
2101 -- Determine business_group mode for the current security profile
2102 if sec_rec.business_group_id is null then
2103 l_business_group_mode := 'GLOBAL';
2104 else
2105 l_business_group_mode := 'LOCAL';
2106 end if;
2107 --
2108 -- Build organization list
2109 build_organization_list(sec_rec.security_profile_id,
2110 sec_rec.include_top_organization_flag,
2111 sec_rec.organization_structure_id,
2112 sec_rec.organization_id,
2113 sec_rec.exclude_business_groups_flag,
2114 l_effective_date,
2115 l_update_date,
2116 l_business_group_mode);
2117 --
2118 end if;
2119 --
2120 -- Do not insert the positions if using user-based security.
2121 if (sec_rec.view_all_positions_flag = 'N' and
2122 nvl(sec_rec.top_position_method, 'S') <> 'U') then
2123 --
2124 hr_utility.set_location(l_proc, 70);
2125 --
2126 -- Build position list
2127 build_position_list(sec_rec.security_profile_id,
2128 sec_rec.view_all_organizations_flag,
2129 sec_rec.include_top_position_flag,
2130 sec_rec.position_structure_id,
2131 sec_rec.position_id,
2132 l_effective_date,
2133 l_update_date);
2134 --
2135 end if;
2136 --
2137 hr_utility.set_location(l_proc, 80);
2138 --
2139 end if;
2140 --
2141 l_found:=true;
2142 --
2143 hr_utility.set_location('Request ID '||p_request_id, 15);
2144 --
2145 end loop;
2146 --
2147 hr_utility.set_location(l_proc, 80);
2148 -- Clearing all unrelated contacts.
2149 clear_unrelated_contacts(p_generation_scope => p_generation_scope,
2150 p_business_group_id => p_business_group_id);
2151 --
2152 -- Add unrelated contacts for the profiles we've processed.
2153 hr_utility.set_location(l_proc, 90);
2154 hr_utility.set_location('Request ID '||p_request_id, 15);
2155 --
2156 add_unrelated_contacts(p_business_group_id => p_business_group_id,
2157 p_generation_scope => p_generation_scope,
2158 p_effective_date => l_effective_date);
2159 --
2160 hr_utility.set_location('Leaving '||l_proc, 130);
2161 --
2162 end generate_opp_lists;
2163
2164 --
2165 -- ----------------------------------------------------------------------------
2166 -- |---------------------< build_lists_for_users >----------------------------|
2167 -- ----------------------------------------------------------------------------
2168 --
2169 PROCEDURE build_lists_for_users
2170 (p_sec_prof_rec IN hr_security_internal.g_sec_prof_r
2171 ,p_effective_date IN DATE
2172 ,p_debug IN BOOLEAN DEFAULT FALSE
2173 ,p_user_id IN NUMBER default null
2174 ,p_process_all_users IN BOOLEAN DEFAULT TRUE)
2175
2176 IS
2177
2178 --
2179 -- Local variables
2180 --
2181 l_proc varchar2(72):= g_package||'build_lists_for_users';
2182 l_debug_type NUMBER := hr_security_internal.g_NO_DEBUG;
2183 l_person_id NUMBER;
2184 l_employee_id NUMBER;
2185 l_user_id NUMBER;
2186 l_all_static_users VARCHAR2(1) := 'Y';
2187 --
2188 l_api_ovn NUMBER := 3;
2189 l_del_static_lists_warning BOOLEAN;
2190
2191 --
2192 l_resp_id NUMBER;
2193 l_resp_app_id NUMBER;
2194 l_sec_grp_id NUMBER;
2195
2196 --
2197 -- Fetch the users in the static user list, ignoring users that do
2198 -- not have a person attached. If p_user_id has a value then just that
2199 -- single user will be fetched.
2200 --
2201 CURSOR csr_get_users IS
2202 SELECT seu.user_id
2203 ,usr.employee_id person_id
2204 ,seu.security_user_id
2205 ,seu.object_version_number
2206 FROM per_security_users seu
2207 ,fnd_user usr
2208 WHERE seu.security_profile_id = p_sec_prof_rec.security_profile_id
2209 AND (seu.process_in_next_run_flag = 'Y' OR l_all_static_users = 'Y')
2210 AND seu.user_id = usr.user_id
2211 AND usr.employee_id IS NOT NULL
2212 AND nvl(p_user_id,usr.user_id) = seu.user_id;
2213
2214 --
2215 -- Retrieve details of first responsibility available
2216 -- to the user to perform the apps_initialize
2217 --
2218 CURSOR csr_get_resp (p_user_id number)
2219 IS
2220 SELECT fusg.responsibility_id,
2221 fusg.responsibility_application_id,
2222 fusg.security_group_id
2223 FROM FND_USER_RESP_GROUPS fusg
2224 WHERE fusg.user_id = p_user_id
2225 AND p_effective_date BETWEEN fusg.start_date
2226 AND NVL(fusg.end_date,p_effective_date)
2227 AND rownum = 1;
2228
2229 BEGIN
2230 hr_utility.set_location(l_proc||' sec prof id '||
2231 to_char(p_sec_prof_rec.security_profile_id),13);
2232 hr_utility.set_location(l_proc||' p_effective_date '||
2233 p_effective_date,13);
2234 hr_utility.set_location(l_proc||' p_user_id '||
2235 p_user_id,13);
2236
2237 --
2238 -- Check that the mandatory parameters have been entered.
2239 --
2240 IF p_sec_prof_rec.security_profile_id IS NOT NULL
2241 AND p_effective_date IS NOT NULL THEN
2242
2243 --
2244 -- If debug output is required, set the debug type.
2245 --
2246 IF p_debug THEN
2247 l_debug_type := hr_security_internal.G_FND_LOG;
2248 END IF;
2249 --
2250 -- make sure that single users who are unchecked in the security
2251 -- profiles form are picked up.
2252 --
2253 -- if p_process_all_user is true then run for everybody, regardless of
2254 -- process flag status.
2255 --
2256 IF (p_user_id is not null OR p_process_all_users)
2257 THEN
2258 l_all_static_users := 'Y';
2259 ELSE
2260 l_all_static_users := 'N';
2261 END IF;
2262
2263 FOR user_rec IN csr_get_users LOOP
2264
2265 -- Bug 3598627
2266 -- If user-based custom security is used then
2267 -- retrieve the first valid responsibility for
2268 -- user and call 'assp_initialize' procedure
2269 -- to set the REQUIRED user level context.
2270 --
2271 -- Note: The first valid responsibility may not
2272 -- set the correct responsibility level
2273 -- context, but it is user level context
2274 -- required here will be correct.
2275 --
2276 -- If valid responsibility is not found for user
2277 -- security permisions will not be stored in the
2278 -- static list.
2279
2280 l_resp_id := NULL;
2281 l_resp_app_id := NULL;
2282 l_sec_grp_id := NULL;
2283
2284
2285 IF (p_sec_prof_rec.custom_restriction_flag = 'U')
2286 THEN
2287 -- Get the first valid responsibility details
2288 OPEN csr_get_resp(user_rec.user_id);
2289 FETCH csr_get_resp INTO l_resp_id,
2290 l_resp_app_id,
2291 l_sec_grp_id;
2292 CLOSE csr_get_resp;
2293
2294 fnd_global.apps_initialize(user_rec.user_id,
2295 l_resp_id,
2296 l_resp_app_id,
2297 l_sec_grp_id);
2298 END IF;
2299
2300 -- If user-based custom security is not used or
2301 -- valid responsibility is found when used.
2302 --
2303 IF (NVL(p_sec_prof_rec.custom_restriction_flag, 'N') <> 'U')
2304 OR (l_resp_id IS NOT NULL) THEN
2305 --
2306 -- For each user in the static list, assess
2307 -- permissions and store in the static tables.
2308 --
2309 -- Set the person to be the named person on the
2310 -- security profile if set, otherwise use the
2311 -- person on the user.
2312 --
2313 l_person_id := NVL(p_sec_prof_rec.named_person_id
2314 ,user_rec.person_id);
2315
2316 hr_security_internal.evaluate_access
2317 (p_person_id => l_person_id
2318 ,p_user_id => user_rec.user_id
2319 ,p_effective_date => p_effective_date
2320 ,p_sec_prof_rec => p_sec_prof_rec
2321 ,p_what_to_evaluate => hr_security_internal.g_ALL
2322 ,p_use_static_lists => FALSE
2323 ,p_update_static_lists => TRUE
2324 ,p_debug => l_debug_type);
2325 --
2326 --Bug 4742108 set everyone back to N if they have been processed
2327 -- regardless of whether processing all or marked static users.
2328 l_api_ovn := user_rec.object_version_number;
2329 -- Bug 4338667
2330 -- now record has been processed need to set
2331 -- process_in_next_run_flag from 'Y' to 'N'.
2332 --
2333 hr_security_user_api.update_security_user
2334 (p_effective_date => p_effective_date
2335 ,p_security_user_id => user_rec.security_user_id
2336 ,p_user_id => user_rec.user_id
2337 ,p_security_profile_id => p_sec_prof_rec.security_profile_id
2338 ,p_process_in_next_run_flag => 'N'
2339 ,p_object_version_number => l_api_ovn
2340 ,p_del_static_lists_warning => l_del_static_lists_warning);
2341 END IF;
2342 END LOOP;
2343 END IF; -- p_sec_prof_rec.security_profile_id has value
2344 hr_utility.set_location('Leaving : '||l_proc,50);
2345 END build_lists_for_users;
2346 --
2347 -- ----------------------------------------------------------------------------
2348 -- |---------------------< build_lists_for_user >------------------------------|
2349 -- ----------------------------------------------------------------------------
2350 -- built for gsi enhancement bug 4634655 and 4338667.
2351 -- this is not a public api but customers have been granted permission to call
2352 -- this procedure directly so DO NOT change parameters as this will invalidate
2353 -- customer code.
2354 --
2355 PROCEDURE build_lists_for_user
2356 (p_security_profile_id number,
2357 p_user_id number,
2358 p_effective_date date default trunc(sysdate)) IS
2359 --
2360 -- Local Variables
2361 --
2362 l_proc varchar2(72):= g_package||'build_lists_for_user';
2363 l_debug boolean default FALSE;
2364 l_process_all_users boolean := TRUE; -- as just running for one user.
2365 l_user_id number;
2366 l_security_profile_id number;
2367 l_sec_prof_rec hr_security_internal.g_sec_prof_r; -- per_security_profiles
2368 -- %ROWTYPE;
2369 -- Cursors:
2370 --
2371 -- check that user and profile exist in per_security_users
2372 --
2373 CURSOR csr_check_user_exists(p_user_id number, p_security_profile_id number)
2374 IS
2375 SELECT seu.user_id,
2376 seu.security_profile_id
2377 FROM per_security_users seu
2378 WHERE seu.user_id = p_user_id
2379 AND seu.security_profile_id = p_security_profile_id;
2380 --
2381 -- get security profile
2382 --
2383 CURSOR csr_security_profile_record(l_security_profile_id number)
2384 IS
2385 SELECT *
2386 from per_security_profiles
2387 where security_profile_id = l_security_profile_id;
2388 --
2389 BEGIN
2390 --
2391 hr_utility.trace('Processing for Single User');
2392 hr_utility.set_location('Entering : '||l_proc,10);
2393 --
2394 -- Check that the mandatory parameters have been entered.
2395 --
2396 IF p_security_profile_id IS NOT NULL
2397 AND p_user_id IS NOT NULL
2398 THEN
2399 -- if they exist continue processing, if they don't then do nothing.
2400 -- check that user and security profile exist
2401 OPEN csr_check_user_exists(p_user_id, p_security_profile_id);
2402 FETCH csr_check_user_exists
2403 INTO l_user_id,
2404 l_security_profile_id;
2405 CLOSE csr_check_user_exists;
2406 hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
2407 hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
2408 hr_utility.set_location(l_proc||'p_security_profile_id : '||
2409 p_security_profile_id,23);
2410 hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
2411 hr_utility.set_location(l_proc||'l_security_profile_id : '||
2412 l_security_profile_id,27);
2413 hr_utility.set_location('IF l_user_id or l_security profile id is null '
2414 ,30);
2415 hr_utility.set_location('then it has not been found in per_security_users'
2416 ,30);
2417 --
2418 -- if they exist then get security profile for user
2419 --
2420 OPEN csr_security_profile_record(l_security_profile_id);
2421 FETCH csr_security_profile_record
2422 INTO l_sec_prof_rec;
2423 CLOSE csr_security_profile_record;
2424 -- call build lists for users for a single user
2425 hr_utility.set_location(l_proc||'call build_lists_for_users '
2426 ||p_effective_date,40);
2427 build_lists_for_users
2428 (p_sec_prof_rec => l_sec_prof_rec
2429 ,p_effective_date => trunc(p_effective_date)
2430 ,p_debug => l_debug
2431 ,p_user_id => l_user_id
2432 ,p_process_all_users => l_process_all_users);
2433 END IF;
2434 hr_utility.set_location('Leaving : '||l_proc,69);
2435 END build_lists_for_user;
2436 --
2437 -- ----------------------------------------------------------------------------
2438 -- |---------------------< generate_list_control >-----------------------------|
2439 -- ----------------------------------------------------------------------------
2440 --
2441 procedure generate_list_control(
2442 p_effective_date date,
2443 p_generation_scope varchar2,
2444 p_business_group_id varchar2 default null,
2445 p_security_profile_id varchar2 default null,
2446 p_who_to_process varchar2 default null,
2447 p_action_parameter_group_id varchar2 default null,
2448 p_user_id varchar2 default null,
2449 p_static_user_processing varchar2 default 'ALL_STATIC',
2450 -- Bug fix 3816741
2451 errbuf out NOCOPY varchar2,
2452 retcode out NOCOPY number) is
2453
2454 l_proc varchar2(60) := g_package||'generate_list_control';
2455
2456 l_business_group_id number;
2457 l_security_profile_id number;
2458 l_action_parameter_group_id number;
2459 l_user_id number;
2460 l_security_profile_name per_security_profiles.security_profile_name%TYPE;
2461 l_logging pay_action_parameters.parameter_value%TYPE;
2462 l_request_id number;
2463 l_effective_date varchar2(50);
2464 l_update_date date;
2465 l_debug boolean := FALSE;
2466 l_success boolean;
2467 l_status varchar2(100);
2468 l_phase varchar2(100);
2469 l_dev_status varchar2(100);
2470 l_dev_phase varchar2(100);
2471 l_message varchar2(100);
2472 l_request_data varchar2(100);
2473 c_wait number := 60;
2474 c_timeout number := 300;
2475 l_process_all_users boolean := TRUE;
2476
2477 -- bug fix 3816741 starts here
2478
2479 l_call_status boolean;
2480
2481 -- bug fix 3816741 ends here
2482
2483 --
2484 -- Fetch the action parameter to determine if logging
2485 -- should be switched on.
2486 --
2487 CURSOR csr_get_action_param IS
2488 SELECT pap.parameter_value
2489 FROM pay_action_parameters pap
2490 WHERE pap.parameter_name = 'LOGGING';
2491
2492 CURSOR security_profiles
2493 IS
2494 SELECT *
2495 FROM per_security_profiles
2496 WHERE ( (business_group_id = p_business_group_id and
2497 p_generation_scope = 'ALL_BUS_GRP')
2498 OR
2499 (business_group_id is null and
2500 p_generation_scope = 'ALL_GLOBAL')
2501 OR
2502 (p_generation_scope = 'ALL_PROFILES')
2503 AND org_security_mode IN ('NONE','HIER')
2504 );
2505 --
2506 begin
2507 hr_utility.set_location('Entering : '||l_proc,10);
2508
2509 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
2510 l_security_profile_id := to_number(p_security_profile_id);
2511 l_business_group_id := to_number(p_business_group_id);
2512 l_user_id := to_number(p_user_id);
2513 l_action_parameter_group_id := to_number(p_action_parameter_group_id);
2514 l_update_date := trunc(sysdate);
2515 --
2516 /*
2517 ** Perform restart checking....
2518 */
2519 l_request_data := fnd_conc_global.request_data;
2520 hr_utility.set_location(l_request_data,12);
2521 if l_request_data is not null then
2522
2523 OPEN csr_get_action_param;
2524 FETCH csr_get_action_param INTO l_logging;
2525 CLOSE csr_get_action_param;
2526
2527 --
2528 -- If logging is set to General, enable debugging.
2529 --
2530 IF instr(NVL(l_logging, 'N'), 'G') <> 0 THEN
2531 l_debug := TRUE;
2532 END IF;
2533 -- Bug 4338667. If user has been specified just run for that user.
2534 -- calling build_list for user rather than buld lists for users to avoid
2535 -- having to dup code by calling sec rec cursor for just this users
2536 -- security profile id. If this is considerably less efficient then we can
2537 -- revise.
2538 /*
2539 IF p_user_id IS NOT NULL
2540 THEN
2541 hr_utility.trace('Processing for Single User - 1');
2542 --
2543 build_lists_for_user
2544 (p_security_profile_id => p_security_profile_id
2545 ,p_user_id => l_user_id
2546 ,p_effective_date => p_effective_date);
2547 END IF;
2548 */
2549 -- Bug 4338667 process all static users unless process only flagged users has
2550 -- been choosen
2551 IF p_static_user_processing = 'FLAGGED_STATIC'
2552 THEN
2553 l_process_all_users := FALSE;
2554 ELSE
2555 l_process_all_users := TRUE;
2556 END IF;
2557
2558 /*
2559 ** On restart we need to finish off by processing the
2560 ** ex-emps.
2561 */
2562 FOR sec_rec in security_profiles LOOP
2563 hr_utility.set_location(l_proc||' SP id '||
2564 to_char(sec_rec.security_profile_id),13);
2565 add_person_list_changes (sec_rec.security_profile_id,
2566 p_effective_date,
2567 l_update_date);
2568
2569 --
2570 -- Build static lists for any users in the list of people to
2571 -- build static lists for. At present, this is outside of the
2572 -- multithreaded PYUGEN process.
2573 --
2574 build_lists_for_users
2575 (p_sec_prof_rec => sec_rec
2576 ,p_effective_date => p_effective_date
2577 ,p_process_all_users => l_process_all_users
2578 ,p_user_id => l_user_id
2579 ,p_debug => l_debug);
2580
2581 END LOOP;
2582
2583 -- Bug fix 3816741 starts here
2584 -- code to check the status of child request. PERSLM will error out
2585 -- if any of the parallel process for MSL_PERSON_LIST concurrent
2586 -- program errors out.
2587
2588 l_call_status := fnd_concurrent.get_request_status(
2589 request_id => l_request_data,
2590 phase => l_phase,
2591 status => l_status,
2592 dev_phase => l_dev_phase,
2593 dev_status => l_dev_status,
2594 message => l_message);
2595
2596 hr_utility.set_location(l_proc||' Dev phase:'||l_dev_phase,14);
2597 hr_utility.set_location(l_proc||' Dev status:'||l_dev_status,15);
2598
2599 if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
2600 errbuf := l_message;
2601 retcode := 2;
2602 else
2603 retcode := 0;
2604 end if;
2605
2606 -- Bug fix 3816741 ends here
2607 return;
2608
2609 end if;
2610 /*
2611 ** Validate the input parameters where appropriate.
2612 **
2613 ** For ALL_PROFILES and ALL_GLOBAL if any value has been provided for BG ID or
2614 ** SP ID we'll just ignore them.
2615 */
2616 if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') and
2617 p_security_profile_id is null then
2618 /*
2619 ** No security profile has been specified.
2620 */
2621 hr_utility.set_message(800,'PER_289776_NO_PROF_ID');
2622 hr_utility.raise_error;
2623 elsif p_generation_scope = 'ALL_BUS_GRP' and
2624 p_business_group_id is null then
2625 /*
2626 ** No business group has been specified.
2627 */
2628 hr_utility.set_message(800,'PER_289777_NO_BG_ID');
2629 hr_utility.raise_error;
2630 elsif p_generation_scope = 'SINGLE_USER' and
2631 p_user_id is null then
2632 /*
2633 ** No user has been specified. Bug 4338667.
2634 */
2635 hr_utility.set_message(800,'PER_50293_NO_USER_ID');
2636 hr_utility.raise_error;
2637 end if;
2638 -- Bug 4338667 call build_lists_for_user directly if single user option
2639 -- specified.
2640 --
2641 /* IF p_generation_scope = 'SINGLE_USER'
2642 THEN
2643 hr_utility.trace('Processing for Single User');
2644 build_lists_for_user
2645 (p_security_profile_id => p_security_profile_id
2646 ,p_user_id => l_user_id
2647 ,p_effective_date => p_effective_date);
2648 ELSE */
2649 if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
2650 hr_utility.trace('Processing for Single Profile or Single User');
2651 hr_utility.set_location(l_proc||'Single Profile or Single User ',19);
2652 hr_utility.set_location(l_proc||'call generate_lists ',20);
2653 generate_lists(p_effective_date => p_effective_date,
2654 p_generation_scope => p_generation_scope,
2655 p_security_profile_id => l_security_profile_id,
2656 p_who_to_process => p_who_to_process,
2657 p_user_id => l_user_id,
2658 p_static_user_processing => p_static_user_processing);
2659 else
2660 /*
2661 ** We are doing all profiles, all profiles in BG or all global profiles.
2662 ** In this case we can process by assignment using PYUGEN if HR is installed.
2663 ** If HR is shared then use the old sequential mechanism.
2664 */
2665 if hr_general.chk_product_installed(800) = 'TRUE' then
2666 hr_utility.set_location(l_proc,30);
2667 /*
2668 ** HR is fully installed so we will use PYUGEN for the person list. First
2669 ** we must generate the Org, Pos and Payroll list information...
2670 */
2671 generate_opp_lists(p_effective_date => p_effective_date,
2672 p_generation_scope => p_generation_scope,
2673 p_business_group_id => l_business_group_id);
2674 /*
2675 ** ...now submit PYUGEN to do the people bit...
2676 */
2677 hr_utility.set_location(l_proc,40);
2678
2679 l_request_id := fnd_request.submit_request(application => 'PER',
2680 program => 'MSL_PERSON_LIST',
2681 sub_request => TRUE,
2682 argument1 => 'ARCHIVE',
2683 argument2 => 'PESLM',
2684 argument3 => 'HR_PROCESS',
2685 argument4 => l_effective_date,
2686 argument5 => l_effective_date,
2687 argument6 => 'PROCESS',
2688 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
2689 argument8 => null,
2690 argument9 => null,
2691 argument10 => l_action_parameter_group_id,
2692 argument11 => 'BUSINESS_GROUP_ID='||l_business_group_id,
2693 argument12 => 'GENERATION_SCOPE='||p_generation_scope,
2694 argument13 => 'WHO_TO_PROCESS='||p_who_to_process,
2695 argument14 => chr(0));
2696
2697 /*
2698 ** Set the status of the process and then exit until the sub-requests
2699 ** have completed.
2700 */
2701 -- Bug fix 3816741
2702 -- l_request_id passed as request_data to check the status of
2703 -- MSL_PERSON_LIST concurrent program.
2704
2705 if l_request_id = 0 then
2706 errbuf := fnd_message.get;
2707 retcode := 2;
2708 else
2709 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
2710 request_data=> l_request_id );
2711 retcode := 0;
2712 end if;
2713 else
2714 /*
2715 ** HR is shared so use the old mechanism but using the new submission
2716 ** mechanism.
2717 */
2718 hr_utility.set_location(l_proc,50);
2719 generate_lists(p_effective_date => p_effective_date,
2720 p_generation_scope => p_generation_scope,
2721 p_business_group_id => l_business_group_id,
2722 p_who_to_process => p_who_to_process,
2723 p_user_id => l_user_id,
2724 p_static_user_processing => p_static_user_processing);
2725 end if;
2726 --end if;
2727 end if;
2728 hr_utility.set_location('Leaving : '||l_proc,100);
2729
2730 end generate_list_control;
2731 --
2732 -- ----------------------------------------------------------------------------
2733 -- |---------------------< generate_list_control >-----------------------------|
2734 -- ----------------------------------------------------------------------------
2735 --
2736 -- Bug fix 3816741
2737 -- Calls the overloaded generate_list_control which has errbuf and retcode
2738 -- parameter added to return the concurrent program status.
2739
2740 procedure generate_list_control(
2741 p_effective_date date,
2742 p_generation_scope varchar2,
2743 p_business_group_id varchar2 default null,
2744 p_security_profile_id varchar2 default null,
2745 p_who_to_process varchar2,
2746 p_action_parameter_group_id varchar2,
2747 p_user_id varchar2 default null,
2748 p_static_user_processing varchar2 default 'ALL_STATIC') is
2749
2750 l_errbuf varchar2(32000);
2751 l_retcode number;
2752 begin
2753 generate_list_control( p_effective_date => p_effective_date,
2754 p_generation_scope => p_generation_scope,
2755 p_business_group_id => p_business_group_id,
2756 p_security_profile_id => p_security_profile_id,
2757 p_who_to_process => p_who_to_process,
2758 p_action_parameter_group_id => p_action_parameter_group_id,
2759 p_user_id => p_user_id,
2760 p_static_user_processing => p_static_user_processing,
2761 errbuf => l_errbuf,
2762 retcode => l_retcode);
2763 end generate_list_control;
2764
2765 --
2766 -- ----------------------------------------------------------------------------
2767 -- |-------------------------< submit_security >------------------------------|
2768 -- ----------------------------------------------------------------------------
2769 --
2770 procedure submit_security(errbuf out NOCOPY varchar2,
2771 retcode out NOCOPY number,
2772 p_effective_date varchar2,
2773 p_generation_scope varchar2,
2774 p_business_group_id varchar2,
2775 p_security_profile_id varchar2,
2776 p_who_to_process varchar2,
2777 p_action_parameter_group_id varchar2,
2778 p_user_name varchar2 default null,
2779 p_static_user_processing varchar2 default 'ALL_STATIC') is
2780
2781 l_proc varchar2(100) := g_package||'submit_security';
2782
2783 begin
2784
2785 -- hr_utility.trace_on('F','LISTGEN');
2786
2787 hr_utility.set_location('Entering '||l_proc,10);
2788
2789 --
2790 -- Set variables used for WHO columns
2791 --
2792 p_program_id := fnd_profile.value('CONC_PROGRAM_ID');
2793 p_request_id := fnd_profile.value('CONC_REQUEST_ID');
2794 p_program_application_id := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
2795 p_update_date := trunc(sysdate);
2796
2797 generate_list_control
2798 (p_effective_date => nvl(fnd_date.canonical_to_date(p_effective_date)
2799 ,sysdate),
2800 p_generation_scope => p_generation_scope,
2801 p_business_group_id => p_business_group_id,
2802 p_security_profile_id => p_security_profile_id,
2803 p_who_to_process => p_who_to_process,
2804 p_action_parameter_group_id => p_action_parameter_group_id,
2805 p_user_id => p_user_name,
2806 -- p_user_name is a misnomer;user name shows on param but passes user id
2807 p_static_user_processing => p_static_user_processing,
2808 -- Bug 3816741. Parameters passed to get the concurrent program status.
2809 errbuf => errbuf,
2810 retcode => retcode);
2811
2812 hr_utility.set_location('Leaving '||l_proc,20);
2813 end;
2814 --
2815 -- ----------------------------------------------------------------------------
2816 -- |----------------------< generate_lists >----------------------------------|
2817 -- ----------------------------------------------------------------------------
2818 --
2819 procedure generate_lists(
2820 p_effective_date in date
2821 ,p_security_profile_name in varchar2 default 'ALL_SECURITY_PROFILES'
2822 ,p_business_group_mode in varchar2 default 'LOCAL'
2823 ) is
2824 --
2825 l_generation_scope varchar2(30);
2826 l_proc varchar2(72) := g_package||'generate_lists';
2827 --
2828 begin
2829 --
2830 if p_security_profile_name = 'ALL_SECURITY_PROFILES' then
2831 --
2832 l_generation_scope := 'ALL_PROFILES';
2833 --
2834 else
2835 l_generation_scope := 'SINGLE_PROF';
2836 end if;
2837 --
2838 generate_lists(p_effective_date => p_effective_date,
2839 p_generation_scope => l_generation_scope,
2840 p_security_profile_name => p_security_profile_name,
2841 p_who_to_process => 'ALL');
2842 --
2843 end generate_lists;
2844 --
2845 -- ----------------------------------------------------------------------------
2846 -- |----------------------< generate_lists >----------------------------------|
2847 -- ----------------------------------------------------------------------------
2848 --
2849 procedure generate_lists(
2850 p_effective_date in date
2851 ,p_generation_scope in varchar2
2852 ,p_business_group_id in number default null
2853 ,p_security_profile_id in number default null
2854 ,p_security_profile_name in varchar2 default null
2855 ,p_who_to_process in varchar2 default null
2856 ,p_user_id in number default null
2857 ,p_static_user_processing in varchar2 default 'ALL_STATIC'
2858 ) is
2859 --
2860 l_effective_date date;
2861 l_update_date date;
2862 l_found boolean default false;
2863 l_debug boolean := false;
2864 l_business_group_mode varchar2(30);
2865 l_proc varchar2(72) := g_package||'generate_lists';
2866 l_logging pay_action_parameters.parameter_value%type;
2867 l_process_all_users boolean default true;
2868 l_user_id number;
2869 --
2870 -- Fetch the action parameter to determine if logging
2871 -- should be switched on.
2872 cursor csr_get_action_param is
2873 select pap.parameter_value
2874 from pay_action_parameters pap
2875 where pap.parameter_name = 'LOGGING';
2876 --
2877 cursor security_profiles is
2878 select *
2879 from per_security_profiles
2880 where (((security_profile_id = p_security_profile_id or
2881 security_profile_name = p_security_profile_name)
2882 and p_generation_scope in ('SINGLE_PROF','SINGLE_USER'))
2883 or (business_group_id = p_business_group_id and
2884 p_generation_scope = 'ALL_BUS_GRP')
2885 or (business_group_id is null and
2886 p_generation_scope='ALL_GLOBAL')
2887 or (p_generation_scope = 'ALL_PROFILES'))
2888 and org_security_mode in ('NONE', 'HIER');
2889 --
2890 begin
2891 --
2892 -- Get the session date and the current date to avoid multiple selects.
2893 hr_utility.set_location('Entering '||l_proc, 10);
2894 --
2895 l_effective_date := trunc(p_effective_date);
2896 l_update_date := trunc(sysdate);
2897 l_user_id := p_user_id;
2898 -- Get the debug paramater
2899 open csr_get_action_param;
2900 fetch csr_get_action_param into l_logging;
2901 close csr_get_action_param;
2902 --
2903 -- If logging is set to General, enable debugging.
2904 if instr(nvl(l_logging, 'N'), 'G') <> 0 then
2905 l_debug := true;
2906 end if;
2907 -- Bug 4338667: if user name is passed then this must be running for single
2908 -- user in single security profile, so just call build_list_for_user directly.
2909 /* IF p_user_id IS NOT NULL
2910 THEN
2911 hr_utility.trace('Processing for Single User - 2');
2912 build_lists_for_user
2913 (p_security_profile_id => p_security_profile_id
2914 ,p_user_id => l_user_id
2915 ,p_effective_date => p_effective_date);
2916 END IF; */
2917 --
2918 -- Bug 4338667: if not explicitly processing for certain static users then
2919 -- process for all of them
2920 IF p_static_user_processing = 'FLAGGED_STATIC'
2921 THEN
2922 l_process_all_users := FALSE;
2923 ELSE
2924 l_process_all_users := TRUE;
2925 END IF;
2926 --
2927 hr_utility.set_location(l_proc, 20);
2928 --
2929 for sec_rec in security_profiles loop
2930 --
2931 -- Delete previous entries for the profile. By using this function
2932 -- we will be using the old generation mechanism so we need to clear
2933 -- the person list data upfront.
2934 clear_sp_list_table(p_generation_scope => p_generation_scope,
2935 p_business_group_id => sec_rec.business_group_id,
2936 p_security_profile_id => sec_rec.security_profile_id,
2937 p_clear_people_flag => true);
2938 --
2939 hr_utility.set_location(l_proc, 30);
2940 --
2941 -- If there are no restrictions or this profile uses user-based
2942 -- security, do not execute the inserts.
2943 if(sec_rec.view_all_employees_flag = 'N' or
2944 sec_rec.view_all_applicants_flag = 'N' or
2945 sec_rec.view_all_cwk_flag = 'N' or
2946 (sec_rec.view_all_contacts_flag = 'N' or
2947 (sec_rec.view_all_contacts_flag = 'Y' and
2948 sec_rec.view_all_candidates_flag = 'X')) or
2949 sec_rec.view_all_organizations_flag = 'N' or
2950 sec_rec.view_all_positions_flag = 'N' or
2951 sec_rec.view_all_payrolls_flag = 'N' or
2952 sec_rec.custom_restriction_flag = 'Y') then
2953 --
2954 hr_utility.set_location(l_proc, 40);
2955 --
2956 if (sec_rec.view_all_payrolls_flag = 'N') then
2957 --
2958 hr_utility.set_location(l_proc, 50);
2959 --
2960 -- Build the payroll list.
2961 build_payroll_list(sec_rec.security_profile_id,
2962 sec_rec.business_group_id,
2963 sec_rec.include_exclude_payroll_flag,
2964 l_effective_date,
2965 l_update_date);
2966 --
2967 end if; -- view_all_payrolls_flag
2968 --
2969 -- Do not insert if using user-based security.
2970 if(sec_rec.view_all_organizations_flag = 'N' and
2971 nvl(sec_rec.top_organization_method, 'S') <> 'U') then
2972 --
2973 hr_utility.set_location(l_proc, 60);
2974 -- Determine business_group mode for the current security profile
2975 if sec_rec.business_group_id is null then
2976 l_business_group_mode := 'GLOBAL';
2977 else
2978 l_business_group_mode := 'LOCAL';
2979 end if;
2980 -- Build organization list
2981 build_organization_list(sec_rec.security_profile_id,
2982 sec_rec.include_top_organization_flag,
2983 sec_rec.organization_structure_id,
2984 sec_rec.organization_id,
2985 sec_rec.exclude_business_groups_flag,
2986 l_effective_date,
2987 l_update_date,
2988 l_business_group_mode);
2989 --
2990 end if;
2991 --
2992 -- Do not insert if using user-based security.
2993 if(sec_rec.view_all_positions_flag = 'N' and
2994 nvl(sec_rec.top_organization_method, 'S') <> 'U' and
2995 nvl(sec_rec.top_position_method, 'S') <> 'U') then
2996 --
2997 hr_utility.set_location(l_proc, 70);
2998 -- Build position list
2999 build_position_list(sec_rec.security_profile_id,
3000 sec_rec.view_all_organizations_flag,
3001 sec_rec.include_top_position_flag,
3002 sec_rec.position_structure_id,
3003 sec_rec.position_id,
3004 l_effective_date,
3005 l_update_date);
3006 --
3007 end if;
3008 --
3009 -- Build person list if we have any person level restriction.
3010 if(sec_rec.view_all_employees_flag = 'N' or
3011 sec_rec.view_all_applicants_flag = 'N' or
3012 sec_rec.view_all_cwk_flag = 'N' or
3013 (sec_rec.view_all_contacts_flag = 'N' or
3014 (sec_rec.view_all_contacts_flag = 'Y' and
3015 sec_rec.view_all_candidates_flag = 'X'))) and
3016 (nvl(sec_rec.top_organization_method, 'S') <> 'U' and
3017 nvl(sec_rec.top_position_method, 'S') <> 'U' and
3018 nvl(sec_rec.custom_restriction_flag, 'N') <> 'U') then
3019 --
3020 create_person_list(sec_rec,
3021 l_effective_date,
3022 l_update_date,
3023 p_who_to_process);
3024 --
3025 end if;
3026 --
3027 -- Add person list changes.
3028 add_person_list_changes(sec_rec.security_profile_id,
3029 l_effective_date,
3030 l_update_date);
3031 --
3032 -- Build static lists for any users in the list of people to
3033 -- build static lists for.
3034 build_lists_for_users(p_sec_prof_rec => sec_rec
3035 ,p_effective_date => l_effective_date
3036 ,p_process_all_users => l_process_all_users
3037 ,p_user_id => l_user_id
3038 ,p_debug => l_debug);
3039 --
3040 end if;
3041 --
3042 -- We only populate build_contact_list if restricting by contacts.
3043 -- Otherwise there is no point in populating the lists because
3044 -- show_person handles view_all_contacts = Yes profiles.
3045 -- The contact list is also only built when user-based restrictions
3046 -- are not in use.
3047 --
3048 -- A condition with view_all_contacts_flag = All and
3049 -- view_all_candidates_flag = None, needs caching (ie: similar to
3050 -- record existing in per_person_list). The additional OR condition
3051 -- is included as part of Candidate Security enchancements.
3052 if(sec_rec.view_all_contacts_flag = 'N' or
3053 (sec_rec.view_all_contacts_flag = 'Y' and
3054 sec_rec.view_all_candidates_flag = 'X')) and
3055 (nvl(sec_rec.top_organization_method, 'S') <> 'U' and
3056 nvl(sec_rec.top_position_method, 'S') <> 'U' and
3057 nvl(sec_rec.custom_restriction_flag, 'N') <> 'U') then
3058 --
3059 build_contact_list(p_security_profile_id => sec_rec.security_profile_id,
3060 p_view_all_contacts_flag => sec_rec.view_all_contacts_flag, -- Added for bug (6376000/4774264)
3061 p_effective_date => l_effective_date,
3062 p_business_group_id => sec_rec.business_group_id);
3063 --
3064 end if;
3065 --
3066 l_found := true;
3067 --
3068 end loop;
3069 --
3070 hr_utility.set_location(l_proc, 130);
3071 --
3072 if not l_found then
3073 --
3074 hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
3075 hr_utility.set_message_token ('PROFILE_NAME', p_security_profile_name);
3076 hr_utility.raise_error;
3077 --
3078 end if;
3079 --
3080 hr_utility.set_location('Leaving '||l_proc, 140);
3081 --
3082 end generate_lists;
3083 --
3084 -- ----------------------------------------------------------------------------
3085 -- |--------------------------< range_cursor >--------------------------------|
3086 -- ----------------------------------------------------------------------------
3087 --
3088 procedure range_cursor (pactid in number,
3089 sqlstr out NOCOPY varchar2) is
3090
3091 l_proc varchar2(100) := g_package||'range_curosr';
3092 l_generation_scope varchar2(20);
3093 begin
3094 --hr_utility.trace_on('F','LISTGEN');
3095 hr_utility.set_location('Entering : '||l_proc,10);
3096 select pay_core_utils.get_parameter('GENERATION_SCOPE',
3097 pa1.legislative_parameters)
3098 into l_generation_scope
3099 from pay_payroll_actions pa1
3100 where payroll_action_id = pactid;
3101
3102 /*
3103 ** Define the SQL statement to get the people we want to process. Provide
3104 ** initial filtering based on business group if appropriate.
3105 */
3106 if l_generation_scope = 'ALL_PROFILES'
3107 or l_generation_scope = 'ALL_GLOBAL'
3108 then
3109 hr_utility.set_location(l_proc,20);
3110 sqlstr := 'select distinct per.person_id
3111 from per_all_people_f per
3112 ,pay_payroll_actions ppa
3113 where ppa.payroll_action_id = :payroll_action_id
3114 order by per.person_id';
3115 -- and ppa.effective_date between per.effective_start_date
3116 -- and per.effective_end_date
3117 else
3118 /*
3119 **scope is ALL_BUS_GRP
3120 */
3121 hr_utility.set_location(l_proc,30);
3122 sqlstr := 'select distinct per.person_id
3123 from per_all_people_f per
3124 ,pay_payroll_actions ppa
3125 where ppa.payroll_action_id = :payroll_action_id
3126 and pay_core_utils.get_parameter(''BUSINESS_GROUP_ID'',
3127 ppa.legislative_parameters) =
3128 per.business_group_id
3129 order by per.person_id';
3130 -- and ppa.effective_date between per.effective_start_date
3131 -- and per.effective_end_date
3132 end if;
3133 hr_utility.set_location('Leaving : '||l_proc,40);
3134 end range_cursor;
3135 --
3136 -- ----------------------------------------------------------------------------
3137 -- |--------------------------< action_creation >-----------------------------|
3138 -- ----------------------------------------------------------------------------
3139 --
3140 -- Purpose : This routine creates assignment actions for a specific chunk.
3141 -- Only one action is created for a single person ID. If a person
3142 -- has multiple assignments then we ignore all but the first one.
3143 -- This is so that we can process all the assignment records within
3144 -- the same chunk(and therefore thread). Later in the process we
3145 -- will get a list of all assignment IDs for a person and process
3146 -- each one of them.
3147 --
3148 -- Notes :
3149 --
3150 procedure action_creation (pactid in number,
3151 stperson in number,
3152 endperson in number,
3153 chunk in number) is
3154
3155 l_temp_person_id per_all_people_f.person_id%TYPE;
3156 l_lockingactid pay_assignment_actions.assignment_action_id%TYPE;
3157 l_business_group_id number;
3158 l_generation_scope varchar2(20);
3159 l_who_to_proc varchar2(20);
3160
3161 l_proc varchar2(100) := g_package||'action_creation';
3162
3163 /*
3164 ** Cursor to select the individual person ID's for each person in the range
3165 ** between stperson and endperson.
3166 **
3167 ** Use the emp/apl/cwk number columns to filter out contact only people
3168 ** unless they become a Emp/Apl/Cwl in the future.
3169 */
3170 cursor c_actions(pactid number,
3171 stperson number,
3172 endperson number) is
3173 select distinct ppf.person_id
3174 from per_person_type_usages_f ppf
3175 ,pay_payroll_actions ppa
3176 ,per_person_types ppt
3177 where ppf.person_id between stperson and endperson
3178 and ppa.payroll_action_id = pactid
3179 -- and ppf.person_type_id = ppt.person_type_id --commented Bug6809753
3180 and (( l_business_group_id = ppt.business_group_id
3181 and l_generation_scope = 'ALL_BUS_GRP')
3182 OR
3183 ( l_generation_scope = 'ALL_GLOBAL')
3184 OR
3185 ( l_generation_scope = 'ALL_PROFILES'))
3186 and (( l_who_to_proc in ('CURRENT','ALL')
3187 /*
3188 ** Current person today
3189 */
3190 and (( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
3191 ppa.effective_date between ppf.effective_start_date
3192 and ppf.effective_end_date
3193 and ppt.system_person_type in ('EMP','APL','CWK'))
3194 OR
3195 /*
3196 ** Future person
3197 */
3198 ( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
3199 ppa.effective_date < ppf.effective_start_date
3200 and ppt.system_person_type in ('EMP','APL','CWK'))))
3201 OR
3202 ( l_who_to_proc in ('TERM','ALL')
3203 /*
3204 ** Existed as a current person at somepoint in history
3205 */
3206 and ( ppf.person_type_id = ppt.person_type_id
3207 and ppa.effective_date > ppf.effective_start_date
3208 and ppt.system_person_type in ('EMP','APL','CWK'))
3209 /*
3210 ** ...as an ex person on the effective date
3211 */
3212 and exists (select null
3213 from per_person_type_usages_f ppf1,
3214 per_person_types ppt1
3215 where ppf1.person_id = ppf.person_id
3216 and ppa.effective_date between ppf1.effective_start_date
3217 and ppf1.effective_end_date
3218 and ppf1.person_type_id = ppt1.person_type_id
3219 and ppt1.business_group_id = ppt.business_group_id
3220 and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
3221 /*
3222 ** ...and not a current person on effective date or in
3223 ** the future.
3224 **
3225 ** Due to the implementation of PTU I can be both EMP and EX-APL
3226 ** today. i.e. I'm an employee who was successfully hired after
3227 ** some application process. In this case the person should be
3228 ** processed as a current and not an ex person. Note the
3229 ** exception for APLs who are either former EMPs/CWKs - in this
3230 ** case an APL who is also term'd should be visible as both an
3231 ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
3232 ** who are EX-EMP/EX-CWK but who are also APL
3233 */
3234 and not exists (select null
3235 from per_person_type_usages_f ppf2,
3236 per_person_types ppt2
3237 where ppf2.person_id = ppf.person_id
3238 and ppa.effective_date < ppf2.effective_end_date
3239 and ppf2.person_type_id = ppt2.person_type_id
3240 and ppt2.business_group_id = ppt.business_group_id
3241 and ppt2.system_person_type in ('EMP','CWK'))));
3242
3243
3244 /*********************************************************************************************
3245 ** Bug 3464720.
3246 ** The cursor was used prior to the termination enhancement. For performance reason this will
3247 ** be used if terminated people are not selected.
3248 *********************************************************************************************/
3249 /*
3250 ** Cursor to select the assignment ID's for each person in the range
3251 ** between stperson and endperson.
3252 */
3253 cursor c_actions_prev(pactid number,
3254 stperson number,
3255 endperson number) is
3256 select distinct asg.assignment_id,
3257 asg.person_id
3258 from per_all_assignments_f asg
3259 ,pay_payroll_actions ppa
3260 where asg.assignment_type in ('E','A','C')
3261 and asg.person_id between stperson and endperson
3262 and ppa.payroll_action_id = pactid
3263 and ( pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
3264 ppa.legislative_parameters) =
3265 asg.business_group_id
3266 and pay_core_utils.get_parameter('GENERATION_SCOPE',
3267 ppa.legislative_parameters) =
3268 'ALL_BUS_GRP'
3269 OR
3270 pay_core_utils.get_parameter('GENERATION_SCOPE',
3271 ppa.legislative_parameters) =
3272 'ALL_GLOBAL'
3273 OR
3274 pay_core_utils.get_parameter('GENERATION_SCOPE',
3275 ppa.legislative_parameters) =
3276 'ALL_PROFILES')
3277 and ((ppa.effective_date between asg.effective_start_date
3278 and asg.effective_end_date)
3279 or
3280 (asg.effective_start_date > ppa.effective_date and
3281 not exists (select null
3282 from per_all_assignments_f paf1
3283 where paf1.assignment_id = asg.assignment_id
3284 and paf1.effective_start_date <
3285 ppa.effective_date)));
3286
3287
3288
3289 begin
3290
3291 l_temp_person_id := null;
3292
3293 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
3294 ppa.legislative_parameters),
3295 pay_core_utils.get_parameter('GENERATION_SCOPE',
3296 ppa.legislative_parameters),
3297 pay_core_utils.get_parameter('WHO_TO_PROCESS',
3298 ppa.legislative_parameters)
3299 into l_business_group_id, l_generation_scope, l_who_to_proc
3300 from pay_payroll_actions ppa
3301 where ppa.payroll_action_id = pactid;
3302
3303 /*********************************
3304 ** If terminated people selected
3305 **********************************/
3306 IF (l_who_to_proc in ('TERM', 'ALL'))
3307 THEN
3308
3309 for perrec in c_actions(pactid, stperson, endperson) loop
3310
3311 select pay_assignment_actions_s.nextval
3312 into l_lockingactid
3313 from dual;
3314
3315 if l_temp_person_id is null then
3316 /*
3317 ** This is the first iteration so set the temp variable
3318 ** and insert the first action record.
3319 */
3320 l_temp_person_id := perrec.person_id;
3321 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
3322 assignid => -1,
3323 pactid => pactid,
3324 chunk => chunk,
3325 greid => null,
3326 object_id => perrec.person_id,
3327 object_type => 'PER_ALL_PEOPLE_F');
3328 end if;
3329
3330 if l_temp_person_id <> perrec.person_id then
3331 /*
3332 ** The person ID has changed since last time and so we need to
3333 ** insert an action record for this assignment
3334 */
3335 l_temp_person_id := perrec.person_id;
3336 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
3337 assignid => -1,
3338 pactid => pactid,
3339 chunk => chunk,
3340 greid => null,
3341 object_id => perrec.person_id,
3342 object_type => 'PER_ALL_PEOPLE_F');
3343 end if;
3344 end loop;
3345
3346 ELSE
3347 /******************************************
3348 ** If terminated people are not selected
3349 ******************************************/
3350 for asgrec in c_actions_prev(pactid, stperson, endperson) loop
3351
3352 select pay_assignment_actions_s.nextval
3353 into l_lockingactid
3354 from dual;
3355
3356 if l_temp_person_id is null then
3357 /*
3358 ** This is the first iteration so set the temp variable
3359 ** and insert the first action record.
3360 */
3361 l_temp_person_id := asgrec.person_id;
3362
3363 -- Bug 3630537
3364 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
3365 -- archive_data needs it to process a person.
3366 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
3367 assignid => -1,
3368 pactid => pactid,
3369 chunk => chunk,
3370 greid => null,
3371 object_id => asgrec.person_id,
3372 object_type => 'PER_ALL_PEOPLE_F');
3373 end if;
3374
3375 if l_temp_person_id <> asgrec.person_id then
3376 /*
3377 ** The person ID has changed since last time and so we need to
3378 ** insert an action record for this assignment
3379 */
3380 l_temp_person_id := asgrec.person_id;
3381
3382 -- Bug 3630537
3383 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
3384 -- archive_data needs it to process a person.
3385 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
3386 assignid => -1,
3387 pactid => pactid,
3388 chunk => chunk,
3389 greid => null,
3390 object_id => asgrec.person_id,
3391 object_type => 'PER_ALL_PEOPLE_F');
3392 end if;
3393 end loop;
3394
3395 END IF;
3396
3397 end action_creation;
3398
3399 --
3400 -- ----------------------------------------------------------------------------
3401 -- |---------------------------< initialization >-----------------------------|
3402 -- ----------------------------------------------------------------------------
3403 --
3404 -- Purpose : This process is called for each slave process to perform
3405 -- standard initialization.
3406 --
3407 -- Notes :
3408 --
3409 procedure initialization(p_payroll_action_id in number)
3410 is
3411 begin
3412 --
3413 -- Set WHO column globals...
3414 --
3415 p_program_id := fnd_profile.value('CONC_PROGRAM_ID');
3416 p_request_id := fnd_profile.value('CONC_REQUEST_ID');
3417 p_program_application_id := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
3418 p_update_date := trunc(sysdate);
3419
3420 end initialization;
3421 --
3422 -- ----------------------------------------------------------------------------
3423 -- |----------------------------< archive_data >------------------------------|
3424 -- ----------------------------------------------------------------------------
3425 --
3426 -- Purpose : This process is called for each assignment action and performs the
3427 -- processing required for each individual person. We have access
3428 -- to an assignment ID but need to determine security for a person
3429 -- so convert the assignment ID into a person ID and then kickoff
3430 -- the processing for that person.
3431 --
3432 -- Notes :
3433 --
3434 procedure archive_data(p_assactid in number,
3435 p_effective_date in date) is
3436
3437 cursor c_person is
3438 select ass.object_id
3439 from pay_assignment_actions ass
3440 where ass.assignment_action_id = p_assactid;
3441
3442 l_person_id per_all_people_f.person_id%TYPE;
3443 l_business_group_id number;
3444 l_generation_scope varchar2(20);
3445 l_who_to_process varchar2(30);
3446
3447 begin
3448 --hr_utility.trace_on('F','PERSLM');
3449
3450
3451 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
3452 ppa.legislative_parameters),
3453 pay_core_utils.get_parameter('GENERATION_SCOPE',
3454 ppa.legislative_parameters),
3455 pay_core_utils.get_parameter('WHO_TO_PROCESS',
3456 ppa.legislative_parameters)
3457 into l_business_group_id, l_generation_scope, l_who_to_process
3458 from pay_payroll_actions ppa,
3459 pay_assignment_actions paa
3460 where ppa.payroll_action_id = paa.payroll_action_id
3461 and paa.assignment_action_id = p_assactid;
3462
3463 /*
3464 ** Get the person ID from the assignment action.
3465 */
3466 open c_person;
3467 fetch c_person into l_person_id;
3468 close c_person;
3469
3470 pay_pyucslis_pkg.process_person(l_person_id,
3471 p_effective_date,
3472 l_business_group_id,
3473 l_generation_scope,
3474 l_who_to_process);
3475
3476 end archive_data;
3477
3478 function chk_person_in_profile (p_person_id in number,
3479 p_security_profile_id number)
3480 return varchar2 is
3481
3482 l_dummy number;
3483 cursor c_per_in_profile is
3484 select 1
3485 from per_person_list
3486 where person_id = p_person_id
3487 and granted_user_id is null
3488 and security_profile_id = p_security_profile_id;
3489
3490 begin
3491
3492 open c_per_in_profile;
3493 fetch c_per_in_profile into l_dummy;
3494 if c_per_in_profile%FOUND then
3495 close c_per_in_profile;
3496 return 'Y';
3497 else
3498 close c_per_in_profile;
3499 return 'X';
3500 end if;
3501
3502 end;
3503 --
3504 -- --------------------------------------------------------------------------
3505 -- |------------------------< submit_cand_sec_opt >-------------------------|
3506 -- --------------------------------------------------------------------------
3507 --
3508 procedure submit_cand_sec_opt(
3509 errbuf out nocopy varchar2,
3510 retcode out nocopy number,
3511 p_profile_option in varchar2
3512 ) is
3513 --
3514 -- Local variables
3515 l_proc varchar2(72):= g_package||'submit_cand_sec_opt';
3516 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
3517 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
3518 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
3519 l_upd_date date := trunc(sysdate);
3520 l_sec_cnt number := 1;
3521 --
3522 -- Exception variables
3523 e_irec_not_installed exception;
3524 --
3525 -- Record Type declaration
3526 type sec_rec is record (
3527 security_profile_id varchar2(15),
3528 security_profile_name varchar2(240)
3529 );
3530 --
3531 -- Table type declaration
3532 type report_rec is table of sec_rec index by binary_integer;
3533 --
3534 -- Security profile records not processed due to the
3535 -- unavailability of lock.
3536 sec_not_processed report_rec;
3537 --
3538 -- Get all security profiles excluding the view_all profiles.
3539 cursor csr_security_prof is
3540 select pspv.*
3541 from per_security_profiles_v pspv
3542 where pspv.view_all_flag = 'N'
3543 and pspv.view_all_candidates_flag <> p_profile_option;
3544 --
3545 begin
3546 --
3547 hr_utility.set_location('Entering '||l_proc, 10);
3548 --
3549 -- Checking that whether iRecruitment is installed.
3550 if nvl(fnd_profile.value('IRC_INSTALLED_FLAG'), 'N') = 'N' then
3551 raise e_irec_not_installed;
3552 end if;
3553 --
3554 hr_utility.set_location(l_proc, 20);
3555 -- Assume that, iRecruitment is installed, hence continuing the process.
3556 for r_sec in csr_security_prof loop
3557 --
3558 begin
3559 --
3560 hr_utility.set_location(l_proc, 30);
3561 -- Get the lock of the respective security profile record.
3562 per_security_profiles_pkg.lock_row(
3563 x_rowid => r_sec.row_id,
3564 x_security_profile_id => r_sec.security_profile_id,
3565 x_business_group_id => r_sec.business_group_id,
3566 x_position_id => r_sec.position_id,
3567 x_organization_id => r_sec.organization_id,
3568 x_position_structure_id => r_sec.position_structure_id,
3569 x_organization_structure_id => r_sec.organization_structure_id,
3570 x_include_top_org_flag => rtrim(r_sec.include_top_organization_flag),
3571 x_include_top_position_flag => rtrim(r_sec.include_top_position_flag),
3572 x_security_profile_name => rtrim(r_sec.security_profile_name),
3573 x_view_all_applicants_flag => rtrim(r_sec.view_all_applicants_flag),
3574 x_view_all_employees_flag => rtrim(r_sec.view_all_employees_flag),
3575 x_view_all_flag => rtrim(r_sec.view_all_flag),
3576 x_view_all_organizations_flag => rtrim(r_sec.view_all_organizations_flag),
3577 x_view_all_payrolls_flag => rtrim(r_sec.view_all_payrolls_flag),
3578 x_view_all_positions_flag => rtrim(r_sec.view_all_positions_flag),
3579 x_view_all_cwk_flag => rtrim(r_sec.view_all_cwk_flag),
3580 x_view_all_contacts_flag => rtrim(r_sec.view_all_contacts_flag),
3581 x_view_all_candidates_flag => rtrim(r_sec.view_all_candidates_flag),
3582 x_include_exclude_payroll_flag => rtrim(r_sec.include_exclude_payroll_flag),
3583 x_reporting_oracle_username => rtrim(r_sec.reporting_oracle_username),
3584 x_allow_granted_users_flag => rtrim(r_sec.allow_granted_users_flag),
3585 x_restrict_by_supervisor_flag => rtrim(r_sec.restrict_by_supervisor_flag),
3586 x_supervisor_levels => r_sec.supervisor_levels,
3587 x_exclude_secondary_asgs_flag => rtrim(r_sec.exclude_secondary_asgs_flag),
3588 x_exclude_person_flag => rtrim(r_sec.exclude_person_flag),
3589 x_named_person_id => r_sec.named_person_id,
3590 x_custom_restriction_flag => rtrim(r_sec.custom_restriction_flag),
3591 x_restriction_text => rtrim(r_sec.restriction_text),
3592 x_exclude_business_groups_flag => rtrim(r_sec.exclude_business_groups_flag),
3593 x_org_security_mode => rtrim(r_sec.org_security_mode),
3594 x_restrict_on_individual_asg => rtrim(r_sec.restrict_on_individual_asg),
3595 x_top_organization_method => rtrim(r_sec.top_organization_method),
3596 x_top_position_method => rtrim(r_sec.top_position_method)
3597 );
3598 --
3599 hr_utility.set_location(l_proc, 40);
3600 -- Sucessfully locked the row, now updating the
3601 -- view_all_candidates_flag with the given value through parameter
3602 -- p_profile_option
3603 per_security_profiles_pkg.update_row(
3604 x_rowid => r_sec.row_id,
3605 x_security_profile_id => r_sec.security_profile_id,
3606 x_business_group_id => r_sec.business_group_id,
3607 x_position_id => r_sec.position_id,
3608 x_organization_id => r_sec.organization_id,
3609 x_position_structure_id => r_sec.position_structure_id,
3610 x_organization_structure_id => r_sec.organization_structure_id,
3611 x_include_top_org_flag => r_sec.include_top_organization_flag,
3612 x_include_top_position_flag => r_sec.include_top_position_flag,
3613 x_security_profile_name => r_sec.security_profile_name,
3614 x_view_all_applicants_flag => r_sec.view_all_applicants_flag,
3615 x_view_all_employees_flag => r_sec.view_all_employees_flag,
3616 x_view_all_flag => r_sec.view_all_flag,
3617 x_view_all_organizations_flag => r_sec.view_all_organizations_flag,
3618 x_view_all_payrolls_flag => r_sec.view_all_payrolls_flag,
3619 x_view_all_positions_flag => r_sec.view_all_positions_flag,
3620 x_view_all_cwk_flag => r_sec.view_all_cwk_flag,
3621 x_view_all_contacts_flag => r_sec.view_all_contacts_flag,
3622 x_view_all_candidates_flag => p_profile_option,
3623 x_include_exclude_payroll_flag => r_sec.include_exclude_payroll_flag,
3624 x_reporting_oracle_username => r_sec.reporting_oracle_username,
3625 x_allow_granted_users_flag => r_sec.allow_granted_users_flag,
3626 x_restrict_by_supervisor_flag => r_sec.restrict_by_supervisor_flag,
3627 x_supervisor_levels => r_sec.supervisor_levels,
3628 x_exclude_secondary_asgs_flag => r_sec.exclude_secondary_asgs_flag,
3629 x_exclude_person_flag => r_sec.exclude_person_flag,
3630 x_named_person_id => r_sec.named_person_id,
3631 x_custom_restriction_flag => r_sec.custom_restriction_flag,
3632 x_restriction_text => r_sec.restriction_text,
3633 x_exclude_business_groups_flag => r_sec.exclude_business_groups_flag,
3634 x_org_security_mode => r_sec.org_security_mode,
3635 x_restrict_on_individual_asg => r_sec.restrict_on_individual_asg,
3636 x_top_organization_method => r_sec.top_organization_method,
3637 x_top_position_method => r_sec.top_position_method,
3638 x_request_id => l_req_id,
3639 x_program_application_id => l_appl_id,
3640 x_program_id => l_prog_id,
3641 x_program_update_date => l_upd_date
3642 );
3643 --
3644 hr_utility.set_location('Sec Prof Id:'||r_sec.security_profile_id, 50);
3645 hr_utility.set_location('Sec Name:'||r_sec.security_profile_name, 55);
3646 hr_utility.set_location('BG Id:'||r_sec.business_group_id, 60);
3647 --
3648 exception
3649 --
3650 -- Could not obtain the lock.
3651 when others then
3652 --
3653 -- Keeping the failed record details into a PL/SQL cache. This will
3654 -- be shown to customer in a report format in concurrent log after
3655 -- the warning (translated) message.
3656 sec_not_processed(l_sec_cnt).security_profile_id
3657 := r_sec.security_profile_id;
3658 sec_not_processed(l_sec_cnt).security_profile_name
3659 := r_sec.security_profile_name;
3660 l_sec_cnt := l_sec_cnt + 1;
3661 --
3662 -- Keeping the information traced.
3663 hr_utility.trace('Cannot process security profile :');
3664 hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
3665 hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
3666 hr_utility.trace('BG Id: '||r_sec.business_group_id);
3667 hr_utility.trace('BG Name: '||r_sec.business_group_name);
3668 --
3669 --
3670 end;
3671 --
3672 end loop;
3673 --
3674 hr_utility.set_location(l_proc, 70);
3675 -- Needs to format the report of failed records (if any) after the
3676 -- warning message.
3677 if sec_not_processed.count > 0 then
3678 --
3679 hr_utility.set_location(l_proc, 80);
3680 -- Setting the message to get the translated message text
3681 fnd_message.set_name('PER', 'PER_449705_SEC_UPDATE_FAILED');
3682 --
3683 errbuf := null;
3684 errbuf := nvl(fnd_message.get, 'PER_449705_SEC_UPDATE_FAILED');
3685 retcode := 1; -- Concurrent process finished with a warning.
3686 --
3687 -- Looping through the PL/SQL cache and writing to the concurrent
3688 -- log file.
3689 for i in sec_not_processed.first..sec_not_processed.last loop
3690 --
3691 fnd_file.put_line(fnd_file.log,
3692 sec_not_processed(i).security_profile_name||'('||
3693 sec_not_processed(i).security_profile_id||')');
3694 --
3695 end loop;
3696 --
3697 end if;
3698 --
3699 hr_utility.set_location('Leaving '||l_proc, 99);
3700 --
3701 exception
3702 --
3703 when e_irec_not_installed then
3704 --
3705 -- Setting the message to get the translated message text
3706 fnd_message.set_name('PER', 'PER_449706_IRC_NOT_INSTALLED');
3707 --
3708 errbuf := null;
3709 errbuf := nvl(fnd_message.get, 'PER_449706_IRC_NOT_INSTALLED');
3710 retcode := 1; -- Concurrent process finished with a warning.
3711 --
3712 hr_utility.set_location('Leaving '||l_proc, 99);
3713 --
3714 end submit_cand_sec_opt;
3715 --
3716 END pay_pyucslis_pkg;
[Home] [Help]