1 PACKAGE BODY pay_pyucslis_pkg AS
2 /* $Header: pyucslis.pkb 120.36.12020000.4 2013/02/27 12:47:46 swrajapa ship $ */
3 --
4 --
5 g_package varchar2(20) := 'pay_pyucslis_pkg.';
6 g_debug boolean := hr_utility.debug_enabled;
7
8 -- Start changes for bug 13504049
9 g_use_temp_table varchar2(10) := 'N';
10 g_generation_scope varchar2(20) := 'ALL_PROFILES';
11 g_static_list_profile varchar2(10) := 'N';
12 -- End changes for bug 13504049
13 --
14 --
15 -- Start changes for the Bug 5438641
16 -- The procedure modified for Bulk Collect.
17
18 procedure add_contacts_for_person(
19 p_person_id number,
20 p_business_group_id number,
21 p_generation_scope varchar2,
22 p_effective_date date
23 ) is
24 --
25 l_proc varchar2(72):= g_package||'add_contacts_for_person';
26 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
27 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
28 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
29 l_upd_date date := trunc(sysdate);
30 --
31 begin
32 --
33 hr_utility.set_location('Entering: '||l_proc, 10);
34 -- Insert a record for each contact of the person. Only process those
35 -- profiles which are in the generation scope but include ALL contacts
36 -- for this person_id.
37
38 -- Added DISTINCT so that it handles multiple contact relationships
39 -- between the same two people. Do not insert if using user-based
40 -- security as this is assessed dynamically.
41
42 --Start changes for the code for 13504049
43 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
44 hr_utility.set_location(l_proc||' using stage table',20);
45 insert into per_person_list_stage(security_profile_id, person_id, request_id,
46 program_application_id, program_id,
47 program_update_date)
48 select /*+ USE_NL(PSP) */
49 distinct ppl.security_profile_id, pcr.contact_person_id,
50 l_req_id, l_appl_id, l_prog_id, l_upd_date
51 from per_contact_relationships pcr,
52 per_person_list_stage ppl,
53 per_security_profiles psp
54 where ppl.person_id = p_person_id
55 and ppl.security_profile_id = psp.security_profile_id
56 and (psp.view_all_contacts_flag = 'N' or
57 (psp.view_all_contacts_flag = 'Y' and
58 psp.view_all_candidates_flag = 'X'))
59 and (nvl(psp.top_organization_method, 'S') <> 'U' and
60 nvl(psp.top_position_method, 'S') <> 'U' and
61 nvl(psp.custom_restriction_flag, 'N') <> 'U')
62 and ((psp.business_group_id = p_business_group_id and
63 p_generation_scope = 'ALL_BUS_GRP') or
64 (psp.business_group_id is null and
65 p_generation_scope = 'ALL_GLOBAL') or
66 p_generation_scope = 'ALL_PROFILES')
67 and pcr.person_id = ppl.person_id
68 and not exists
69 (select /*+ NO_MERGE */ null
70 from per_all_assignments_f asg
71 where asg.person_id = pcr.contact_person_id
72 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
73 and not exists
74 (select /*+ NO_MERGE */ null
75 from per_person_list_stage ppl1
76 where ppl1.person_id = pcr.contact_person_id
77 and ppl1.granted_user_id is null
78 and ppl1.security_profile_id = ppl.security_profile_id);
79
80 else
81 hr_utility.set_location(l_proc||' using actual table',30);
82 insert into per_person_list(security_profile_id, person_id, request_id,
83 program_application_id, program_id,
84 program_update_date)
85 select /*+ USE_NL(PSP) */
86 distinct ppl.security_profile_id, pcr.contact_person_id,
87 l_req_id, l_appl_id, l_prog_id, l_upd_date
88 from per_contact_relationships pcr,
89 per_person_list ppl,
90 per_security_profiles psp
91 where ppl.person_id = p_person_id
92 and ppl.security_profile_id = psp.security_profile_id
93 and (psp.view_all_contacts_flag = 'N' or
94 (psp.view_all_contacts_flag = 'Y' and
95 psp.view_all_candidates_flag = 'X'))
96 and (nvl(psp.top_organization_method, 'S') <> 'U' and
97 nvl(psp.top_position_method, 'S') <> 'U' and
98 nvl(psp.custom_restriction_flag, 'N') <> 'U')
99 and ((psp.business_group_id = p_business_group_id and
100 p_generation_scope = 'ALL_BUS_GRP') or
101 (psp.business_group_id is null and
102 p_generation_scope = 'ALL_GLOBAL') or
103 p_generation_scope = 'ALL_PROFILES')
104 and pcr.person_id = ppl.person_id
105 and not exists
106 (select /*+ NO_MERGE */ null
107 from per_all_assignments_f asg
108 where asg.person_id = pcr.contact_person_id
109 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
110 and not exists
111 (select /*+ NO_MERGE */ null
112 from per_person_list ppl1
113 where ppl1.person_id = pcr.contact_person_id
114 and ppl1.granted_user_id is null
115 and ppl1.security_profile_id = ppl.security_profile_id);
116 end if;
117 --
118 hr_utility.set_location('Leaving: '||l_proc, 99);
119 --
120 end add_contacts_for_person;
121
122 --
123 procedure add_unrelated_contacts(
124 p_business_group_id number
125 ,p_generation_scope varchar2
126 ,p_effective_date date
127 ) is
128 --
129 type l_number_t is table of number index by binary_integer;
130 --
131 l_per_tbl l_number_t;
132 l_per_bg_tbl l_number_t;
133 l_sp_tbl l_number_t;
134 l_sp_bg_tbl l_number_t;
135 --
136 l_proc varchar2(72) := g_package||'.add_unrelated_contacts';
137 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
138 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
139 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
140 l_upd_date date := trunc(sysdate);
141 t_varchar varchar2(1);
142 -- Fetch the unrelated contacts, excluding the Candidates (iRecruitment
143 -- registered user's) that are in scope.
144 cursor csr_get_unrelated_contacts is
145 select distinct
146 papf.person_id,
147 papf.business_group_id
148 from per_all_people_f papf
149 where not exists
150 (select null
151 from per_all_assignments_f asg
152 where asg.person_id = papf.person_id)
153 and not exists
154 (select null
155 from per_contact_relationships pcr
156 where pcr.contact_person_id = papf.person_id)
157 and ((p_generation_scope = 'ALL_BUS_GRP' and
158 papf.business_group_id = p_business_group_id) or
159 p_generation_scope <> 'ALL_BUS_GRP')
160 and not exists
161 (select null
162 from per_person_type_usages_f ptuf,
163 per_person_types ppt
164 where ppt.system_person_type = 'IRC_REG_USER'
165 and ptuf.person_type_id = ppt.person_type_id
166 and ptuf.person_id = papf.person_id);
167 -- Fetch the security profiles that restrict by contacts, are
168 -- in profile scope and are not user-based.
169 --
170 -- Cursor for p_generation_scope = 'ALL_BUS_GRP'
171 cursor csr_get_sec_profs_bg is
172 select psp.security_profile_id,
173 psp.business_group_id
174 from per_security_profiles psp
175 where (psp.view_all_contacts_flag = 'N' or
176 (psp.view_all_contacts_flag = 'Y' and
177 psp.view_all_candidates_flag = 'X'))
178 and (nvl(psp.top_organization_method, 'S') <> 'U' and
179 nvl(psp.top_position_method, 'S') <> 'U' and
180 nvl(psp.custom_restriction_flag, 'N') <> 'U')
181 and psp.business_group_id = p_business_group_id;
182 --
183 -- Cursor for p_generation_scope <> 'ALL_BUS_GRP'
184 cursor csr_get_sec_profs is
185 select psp.security_profile_id,
186 psp.business_group_id
187 from per_security_profiles psp
188 where (psp.view_all_contacts_flag = 'N' or
189 (psp.view_all_contacts_flag = 'Y' and
190 psp.view_all_candidates_flag = 'X'))
191 and (nvl(psp.top_organization_method, 'S') <> 'U' and
192 nvl(psp.top_position_method, 'S') <> 'U' and
193 nvl(psp.custom_restriction_flag, 'N') <> 'U')
194 and ((p_generation_scope = 'ALL_GLOBAL' and
195 psp.business_group_id is null) or
196 p_generation_scope = 'ALL_PROFILES');
197 --
198
199 begin
200 --
201 hr_utility.set_location('Entering: '||l_proc, 1);
202 hr_utility.set_location('Request ID '||p_request_id, 15);
203 --
204 -- Bulk collect the unrelated contacts into PL/SQL tables.
205 open csr_get_unrelated_contacts;
206 fetch csr_get_unrelated_contacts bulk collect into l_per_tbl, l_per_bg_tbl;
207 close csr_get_unrelated_contacts;
208 --
209 hr_utility.set_location(l_proc, 10);
210 --
211 if l_per_tbl.count > 0 then
212 -- When there are unrelated contacts, bulk collect the security
213 -- profiles that restrict by contacts.
214 hr_utility.set_location(l_proc, 20);
215 --
216 if p_generation_scope = 'ALL_BUS_GRP' then
217 --
218 open csr_get_sec_profs_bg;
219 fetch csr_get_sec_profs_bg bulk collect into l_sp_tbl, l_sp_bg_tbl;
220 close csr_get_sec_profs_bg;
221 --
222 else
223 --
224 open csr_get_sec_profs;
225 fetch csr_get_sec_profs bulk collect into l_sp_tbl, l_sp_bg_tbl;
226 close csr_get_sec_profs;
227 --
228 end if;
229 --
230 hr_utility.set_location(l_proc, 30);
231 --
232 if l_sp_tbl.count > 0 then
233 --
234 hr_utility.set_location(l_proc, 40);
235 --
236 for i in l_sp_tbl.first..l_sp_tbl.last loop
237 -- Insert the unrelated contacts for each security profile.
238 -- Enforce the business group restriction when restricting
239 -- by all profiles.
240 for j in l_per_tbl.first..l_per_tbl.last
241 --
242 loop
243
244 if (p_generation_scope <> 'ALL_PROFILES' or
245 (p_generation_scope = 'ALL_PROFILES' and
246 nvl(l_sp_bg_tbl(i), l_per_bg_tbl(j)) =
247 l_per_bg_tbl(j)))
248
249 then
250 begin
251
252 select 'X' into t_varchar
253 from per_person_list p2
254 where p2.person_id = l_per_tbl(j)
255 and p2.security_profile_id = l_sp_tbl(i);
256
257 exception
258 when no_Data_found then
259
260 -- Start changes for bug 13504049
261 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
262 hr_utility.set_location(l_proc||' using stage table',50);
263 insert into per_person_list_stage(security_profile_id, person_id,
264 request_id, program_application_id,
265 program_id, program_update_date)
266 values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
267 l_appl_id, l_prog_id, l_upd_date);
268 else
269 hr_utility.set_location(l_proc||' using actual table',60);
270 insert into per_person_list(security_profile_id, person_id,
271 request_id, program_application_id,
272 program_id, program_update_date)
273 values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
274 l_appl_id, l_prog_id, l_upd_date);
275 end if;
276 -- End changes for bug 13504049
277 --
278 end;
279
280 --
281 end if;
282 end loop;
283 end loop;
284 --
285 end if;
286 --
287 end if;
288 --
289 hr_utility.set_location('Leaving: '||l_proc, 999);
290 --
291 end add_unrelated_contacts;
292 --
293
294 /* =======================================================================
295 NAME
296 delete_old_person_list_changes
297 DESCRIPTION
298 Delete entries in the person list changes table which are no longer
299 required because they are currently employed.
300 PARAMETERS
301 l_effective_date - date at which we are running.
302 ========================================================================== */
303 --
304 PROCEDURE delete_old_person_list_changes (l_effective_date DATE)
305 IS
306 BEGIN
307 --
308 hr_utility.set_location('hr_listgen.delete_old_person_list_changes',10);
309 --
310 -- Stubb out as part of ex-person security enhancements.
311 --
312 hr_utility.set_location('hr_listgen.delete_old_person_list_changes',20);
313 --
314 END delete_old_person_list_changes;
315 --
316 --
317 --
318 --
319 /* =======================================================================
320 NAME
321 build_payroll_list
322 DESCRIPTION
323 Insert payroll list entries for the current security profile based on
324 the secured payroll table per_security_payrolls generated by the define
325 security profile form. If the include_exclude option in the security
326 profile is set to 'I' then the specified payrolls are copied to the payroll
327 list. If the include_exclude flag is 'E' then all other payrolls for
328 the business group are inserted into the list.
329 PARAMETERS
330 l_security_profile_id - identifier of the current security profile
331 l_business_group_id - business group of the security profile.
332 l_include_exclude_payroll_flag - include/exclude option of security profile
333 l_effective_date - date at which the lists are generated
334 l_update_date - today's date.
335
336 ========================================================================== */
337 PROCEDURE build_payroll_list (l_security_profile_id NUMBER,
338 l_business_group_id NUMBER,
339 l_include_exclude_payroll_flag VARCHAR2,
340 l_effective_date DATE,
341 l_update_date DATE)
342 IS
343 BEGIN
344 --
345 IF (l_include_exclude_payroll_flag = 'I') THEN
346 --
347 hr_utility.set_location('hr_listgen.build_payroll_list', 10);
348 --
349 INSERT INTO pay_payroll_list
350 (payroll_id,
351 security_profile_id,
352 request_id,
353 program_application_id,
354 program_id,
355 program_update_date)
356 SELECT distinct pay.payroll_id,
357 l_security_profile_id,
358 p_request_id,
359 p_program_application_id,
360 p_program_id,
361 l_update_date
362 FROM pay_all_payrolls_f pay,
363 pay_security_payrolls sec
364 WHERE sec.security_profile_id = l_security_profile_id
365 AND sec.payroll_id = pay.payroll_id;
366 /* Coomented for bug 8219374
367 AND l_effective_date
368 BETWEEN pay.effective_start_date
369 AND pay.effective_end_date;*/
370 --
371 ELSE -- exclude payrolls
372 --
373 hr_utility.set_location('hr_listgen.build_payroll_list', 20);
374 --
375 INSERT INTO pay_payroll_list
376 (payroll_id,
377 security_profile_id,
378 request_id,
379 program_application_id,
380 program_id,
381 program_update_date)
382 SELECT distinct pay.payroll_id,
383 l_security_profile_id,
384 p_request_id,
385 p_program_application_id,
386 p_program_id,
387 l_update_date
388 FROM pay_all_payrolls_f pay
389 WHERE
390 /* Coomented for bug 8219374
391 l_effective_date
392 BETWEEN pay.effective_start_date
393 AND pay.effective_end_date
394 AND */
395 pay.business_group_id + 0 = l_business_group_id
396 AND NOT EXISTS
397 (SELECT NULL
398 FROM pay_security_payrolls sec
399 WHERE sec.security_profile_id = l_security_profile_id
400 AND sec.payroll_id = pay.payroll_id) ;
401 --
402 END IF; -- include payrolls
403 --
404 END build_payroll_list;
405 --
406 --
407 /* =======================================================================
408 NAME
409 build_organization_list
410 DESCRIPTION
411 Insert values into the organization list for the security profile.
412 Starting with the organization specified a tree walk of the organization
413 structure element table per_org_structure_elements takes place and
414 all organization below that specified are inserted into the organization
415 list. If the include_top_org option is specified then that organisation
416 is explicitly inserted into the list. The business group is
417 inserted into the organisation list if not previously inserted.
418 PARAMETERS
419 l_security_profile_id - identifier of the current security profile
420 l_include_top_org_flag - include/exclude top organization option
421 l_organization_structure_id - identifier of the organization structure
422 to be used.
423 l_organization_id - top organization to consider within the
424 organization structure
425 l_exclude_business_groups_flag - include/exclude all business groups when
426 running in global mode
427 l_effective_date - effective date of the run to pick the
428 structure version.
429 l_update_date - todays date.
430 p_business_group_mode - LOCAL/GLOBAL depends on type of security
431 profile.
432 ========================================================================== */
433 --
434 PROCEDURE build_organization_list (
435 l_security_profile_id NUMBER,
436 l_include_top_org_flag VARCHAR2,
437 l_organization_structure_id NUMBER,
438 l_organization_id NUMBER,
439 l_exclude_business_groups_flag VARCHAR2,
440 l_effective_date DATE,
441 l_update_date DATE,
442 p_business_group_mode VARCHAR2) IS
443 --
444 l_proc varchar2(100) := 'pay_pyucslis_pkg.build_organization_list';
445 --
446 begin
447 --
448 hr_utility.set_location(l_proc, 10);
449 --
450 -- Insert all organizations in the hierarchy (excluding the top organization).
451 --
452 INSERT INTO per_organization_list
453 (security_profile_id,
454 organization_id,
455 request_id,
456 program_application_id,
457 program_id,
458 program_update_date )
459 SELECT l_security_profile_id,
460 o.organization_id_child,
461 p_request_id,
462 p_program_application_id,
463 p_program_id,
464 l_update_date
465 FROM per_org_structure_elements o
466 CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
467 AND o.org_structure_version_id = PRIOR o.org_structure_version_id
468 START WITH o.organization_id_parent = l_organization_id
469 AND o.org_structure_version_id =
470 (SELECT v.org_structure_version_id
471 FROM per_org_structure_versions v
472 WHERE v.organization_structure_id = l_organization_structure_id
473 AND l_effective_date BETWEEN v.date_from
474 AND NVL(v.date_to, TO_DATE('31-12-4712','dd-mm-yyyy')));
475 --
476 hr_utility.set_location(l_proc, 20);
477 --
478 -- Insert all organizations in the organization list that have their
479 -- include / exclude flag set to 'I'.
480 --
481 INSERT INTO per_organization_list
482 (security_profile_id,
483 request_id,
484 program_id,
485 program_application_id,
486 program_update_date,
487 organization_id)
488 SELECT l_security_profile_id,
489 p_request_id,
490 p_program_id,
491 p_program_application_id,
492 l_update_date,
493 pso.organization_id
494 FROM per_security_organizations pso
495 WHERE pso.entry_type = 'I'
496 AND pso.security_profile_id = l_security_profile_id
497 AND NOT EXISTS
498 (SELECT NULL
499 FROM per_organization_list pol
500 WHERE pol.security_profile_id = l_security_profile_id
501 AND pol.organization_id = pso.organization_id);
502 --
503 hr_utility.set_location(l_proc, 30);
504 --
505 -- Include the Top Organization if the security profile permits.
506 --
507 IF (l_include_top_org_flag = 'Y') THEN
508 --
509 IF l_organization_id IS NOT NULL THEN
510 --
511 hr_utility.set_location(l_proc, 40);
512 --
513 INSERT INTO per_organization_list
514 (security_profile_id,
515 organization_id,
516 request_id,
517 program_application_id,
518 program_id,
519 program_update_date )
520 SELECT l_security_profile_id,
521 l_organization_id,
522 p_request_id,
523 p_program_application_id,
524 p_program_id,
525 l_update_date
526 FROM DUAL
527 /* Duplicate check. Required because of organization list Includes */
528 WHERE NOT EXISTS
529 (SELECT NULL
530 FROM per_organization_list pol
531 WHERE pol.security_profile_id = l_security_profile_id
532 AND pol.user_id IS NULL
533 AND pol.organization_id = l_organization_id);
534 --
535 END IF;
536 --
537 END IF;
538 --
539 hr_utility.set_location(l_proc, 50);
540 --
541 IF p_business_group_mode='GLOBAL' AND
542 NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
543 --
544 -- Include all business groups in the hierarchy for the GLOBAL sec prof.
545 --
546 INSERT INTO per_organization_list
547 (security_profile_id,
548 organization_id,
549 request_id,
550 program_application_id,
551 program_id,
552 program_update_date )
553 SELECT DISTINCT
554 l_security_profile_id,
555 org.business_group_id,
556 p_request_id,
557 p_program_application_id,
558 p_program_id,
559 l_update_date
560 FROM hr_all_organization_units org
561 , per_organization_list lst
562 WHERE lst.security_profile_id = l_security_profile_id
563 AND lst.organization_id=org.organization_id
564 AND NOT EXISTS
565 (SELECT 1
566 FROM per_organization_list lst2
567 WHERE lst2.organization_id = org.business_group_id
568 AND lst2.user_id IS NULL
569 AND lst2.security_profile_id = l_security_profile_id);
570 --
571 ELSIF p_business_group_mode = 'LOCAL' AND
572 NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
573 --
574 -- Include the business group for a LOCAL security profile.
575 --
576 INSERT INTO per_organization_list
577 (security_profile_id,
578 organization_id,
579 request_id,
580 program_application_id,
581 program_id,
582 program_update_date )
583 SELECT l_security_profile_id,
584 s.business_group_id,
585 p_request_id,
586 p_program_application_id,
587 p_program_id,
588 l_update_date
589 FROM per_security_profiles s
590 WHERE s.security_profile_id = l_security_profile_id
591 AND NOT EXISTS
592 (SELECT NULL
593 FROM per_organization_list b
594 WHERE b.organization_id = s.business_group_id
595 AND b.user_id IS NULL
596 AND b.security_profile_id = l_security_profile_id);
597 --
598 END IF;
599 --
600 -- Remove the organizations listed as 'Exclude' in the organization list.
601 --
602 DELETE
603 FROM per_organization_list
604 WHERE security_profile_id = l_security_profile_id
605 AND user_id IS NULL
606 AND organization_id IN
607 (SELECT organization_id
608 FROM per_security_organizations
609 WHERE security_profile_id = l_security_profile_id
610 AND entry_type = 'E');
611 --
612 -- BUSINESS_GROUP_ID's should be excluded from PER_ORGANIZATION_LIST for a
613 -- global security profile with EXCLUDE_BUSINESS_GROUPS_FLAG is set as 'Y'.
614 --
615 -- Here an exclusive DELETE command is used, because in global security
616 -- profile a business group can be a child of child of another business
617 -- group (ie: more than one hierarchy below). In such cases a NOT EXISTS
618 -- clause will not identify these business groups, when we use CONNECT BY.
619 -- ie: While using NOT EXISTS clause along with CONNECT BY, then NOT EXISTS
620 -- will scan only the first level of hierarchy and not the subsequent levels
621 -- below.
622 --
623 IF p_business_group_mode = 'GLOBAL' AND
624 NVL(l_exclude_business_groups_flag, 'N') = 'Y' THEN
625 --
626 DELETE
627 FROM per_organization_list pol
628 WHERE pol.security_profile_id = l_security_profile_id
629 AND pol.user_id IS NULL
630 AND pol.organization_id IN
631 (SELECT org.business_group_id
632 FROM hr_all_organization_units org
633 WHERE org.organization_id = pol.organization_id
634 AND org.organization_id = org.business_group_id);
635 --
636 END IF;
637 --
638 END build_organization_list;
639 --
640 /* =======================================================================
641 NAME
642 build_position_list
643 DESCRIPTION
644 Insert values into the position list for the security profile.
645 A tree walk of the position structure table takes place starting with
646 the top position specified. If the 'all_organisations' option is
647 specified then a row is inserted for each position in the structure
648 below the top position. If 'all_organizations' is not specified then
649 rows are only inserted if the position encountered exists in an
650 organization in the organization list for the security profile. If
651 the 'include top position' option is specified then the position is
652 explictly inserted into the position list.
653 PARAMETERS
654 l_security_profile_id - identifier of the current security profile.
655 l_view_all_organizations_flag - all organizations option
656 l_include_top_position_flag - include/exclude top position option
657 l_position_structure_id - position structure to be used.
658 l_position_id - top position in the position structure
659 to be used.
660 l_effective_date - effective_date of the run at which to
661 pick the version.
662 l_update_date - today's date.
663 ========================================================================== */
664 --
665 PROCEDURE build_position_list (l_security_profile_id NUMBER,
666 l_view_all_organizations_flag VARCHAR2,
667 l_include_top_position_flag VARCHAR2,
668 l_position_structure_id NUMBER,
669 l_position_id NUMBER,
670 l_effective_date DATE,
671 l_update_date DATE)
672 IS
673 BEGIN
674 --
675 IF (l_view_all_organizations_flag = 'N') THEN
676 --
677 hr_utility.set_location('hr_listgen.build_position_list', 10);
678 --
679 INSERT INTO per_position_list
680 (security_profile_id,
681 position_id,
682 request_id,
683 program_application_id,
684 program_id,
685 program_update_date )
686 SELECT l_security_profile_id,
687 p.subordinate_position_id,
688 p_request_id,
689 p_program_application_id,
690 p_program_id,
691 l_update_date
692 FROM per_pos_structure_elements p
693 WHERE EXISTS
694 (SELECT NULL
695 FROM hr_all_positions_f pp,
696 per_organization_list ol
697 WHERE ol.organization_id = pp.organization_id
698 AND pp.position_id = p.subordinate_position_id
699 AND ol.security_profile_id= l_security_profile_id)
700 START WITH p.parent_position_id = l_position_id
701 AND p.pos_structure_version_id =
702 (SELECT v.pos_structure_version_id
703 FROM per_pos_structure_versions v
704 WHERE v.position_structure_id = l_position_structure_id
705 AND l_effective_date
706 BETWEEN v.date_from
707 AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
708 CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
709 AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
710 --
711 -- Include the top position.
712 --
713 IF ( l_include_top_position_flag = 'Y') THEN
714 --
715 hr_utility.set_location('hr_listgen.build_position_list', 20);
716 --
717 INSERT INTO per_position_list
718 (security_profile_id,
719 position_id,
720 request_id,
721 program_application_id,
722 program_id,
723 program_update_date )
724 SELECT l_security_profile_id,
725 l_position_id,
726 p_request_id,
727 p_program_application_id,
728 p_program_id,
729 l_update_date
730 FROM sys.dual
731 WHERE EXISTS
732 (SELECT NULL
733 FROM hr_all_positions_f pp,
734 per_organization_list ol
735 WHERE ol.organization_id = pp.organization_id
736 AND pp.position_id = l_position_id
737 AND ol.security_profile_id= l_security_profile_id);
738 --
739 END IF; -- Include the top position.
740 --
741 ELSE -- l_view_all_organizations_flag
742 --
743 hr_utility.set_location('hr_listgen.build_position_list', 30);
744 --
745 INSERT INTO per_position_list
746 (security_profile_id,
747 position_id,
748 request_id,
749 program_application_id,
750 program_id,
751 program_update_date )
752 SELECT l_security_profile_id,
753 p.subordinate_position_id,
754 p_request_id,
755 p_program_application_id,
756 p_program_id,
757 l_update_date
758 FROM per_pos_structure_elements p
759 START WITH p.parent_position_id = l_position_id
760 AND p.pos_structure_version_id =
761 (SELECT v.pos_structure_version_id
762 FROM per_pos_structure_versions v
763 WHERE v.position_structure_id = l_position_structure_id
764 AND l_effective_date
765 BETWEEN v.date_from
766 AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
767 CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
768 AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
769 --
770 -- Include top position.
771 --
772 IF ( l_include_top_position_flag = 'Y') THEN
773 --
774 hr_utility.set_location('hr_listgen.build_position_list', 40);
775 --
776 INSERT INTO per_position_list
777 (security_profile_id,
778 position_id,
779 request_id,
780 program_application_id,
781 program_id,
782 program_update_date )
783 VALUES (l_security_profile_id,
784 l_position_id,
785 p_request_id,
786 p_program_application_id,
787 p_program_id,
788 l_update_date);
789 --
790 END IF; -- Include the top position.
791 --
792 END IF; -- View all organizations.
793 --
794 END build_position_list;
795 --
796 --
797 /* =======================================================================
798 NAME
799 build_contact_list
800 DESCRIPTION
801 Insert contacts into the person list for the security profile.
802 PARAMETERS
803 p_security_profile_id - security profile identifier
804 p_effective_date - date at which the lists are generated
805 p_business_group_id - business group ID from the security profile.
806 If it's null(global profile) include contacts
807 from all BGs. Otherwise just for the profiles
808 business group.
809 ========================================================================== */
810 --
811 procedure build_contact_list(
812 p_security_profile_id number,
813 p_view_all_contacts_flag varchar2, -- Added for bug (6376000/4774264)
814 p_effective_date date,
815 p_business_group_id number
816 ) is
817 --
818 l_proc varchar2(72):= g_package||'build_contact_list';
819 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
820 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
821 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
822 l_upd_date date := trunc(sysdate);
823 --
824 begin
825 --
826 hr_utility.set_location('Entering: ' || l_proc, 10);
827
828 IF p_view_all_contacts_flag = 'Y' then ---- Added for bug (6376000/4774264)
829 -- If the Security profile set as -- View All contacts = Yes
830 -- In this case we need to Insert the contact records for,
831 -- 1) Related contacts - If the person/Employee is visible then Only Insert
832 -- contacts related to the Person/Employee.
833 -- --> Query #2 will populate these records.
834 -- 2) Unrelated Contacts - Insert all Unrelated i.e which is not belong to any
835 -- Person/Record in system.
836 -- --> Query #3 will populate these records.
837 -- 3) View All contacts = Yes --> Insert all reacords which are related to the
838 -- Person/Employee in the system but not populated because of the Security Profile setup like
839 -- Employee = Restricted.
840 -- --> Query #1 will populate these records.
841
842
843 -- Query #1
844 -- Start changes for bug 13504049
845 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
846 hr_utility.set_location(l_proc||' using stage table', 14);
847 insert into per_person_list_stage(security_profile_id, request_id, program_id
848 ,program_application_id, program_update_date
849 ,person_id)
850 select distinct p_security_profile_id, l_req_id, l_prog_id,
851 l_appl_id, l_upd_date, pcr.contact_person_id
852 from per_contact_relationships pcr,
853 per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
854 where ppl.person_id = pcr.person_id
855 and (pcr.business_group_id = p_business_group_id or
856 p_business_group_id is null)
857 -- and ppl.security_profile_id = p_security_profile_id for bug (6376000/4774264)
858 and not exists
859 (select null
860 from per_all_assignments_f asg
861 where asg.person_id = pcr.contact_person_id
862 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
863 and not exists
864 (select null
865 from per_person_list_stage ppl1
866 where ppl1.person_id = pcr.contact_person_id
867 and ppl1.granted_user_id is null
868 and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
869 else
870 hr_utility.set_location(l_proc||' using actual table', 18);
871 insert into per_person_list(security_profile_id, request_id, program_id
872 ,program_application_id, program_update_date
873 ,person_id)
874 select distinct p_security_profile_id, l_req_id, l_prog_id,
875 l_appl_id, l_upd_date, pcr.contact_person_id
876 from per_contact_relationships pcr,
877 per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
878 where ppl.person_id = pcr.person_id
879 and (pcr.business_group_id = p_business_group_id or
880 p_business_group_id is null)
881 -- and ppl.security_profile_id = p_security_profile_id for bug (6376000/4774264)
882 and not exists
883 (select null
884 from per_all_assignments_f asg
885 where asg.person_id = pcr.contact_person_id
886 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
887 and not exists
888 (select null
889 from per_person_list ppl1
890 where ppl1.person_id = pcr.contact_person_id
891 and ppl1.granted_user_id is null
892 and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
893 end if;
894 -- End changes for bug 13504049
895 --
896 hr_utility.set_location(l_proc, 20);
897 Else
898 -- Insert into person list, all people with a contact relationship to
899 -- someone already in the person list as long as their system person type
900 -- is 'other'
901 -- Added DISTINCT to handle two or more contact relationships
902 -- for the same two people (e.g., the same person is a brother and
903 -- emergency contact).
904
905 -- Instead of using using the worker numbers to evaluate whether the
906 -- current record is a contact another AND NOT EXISTS can check for
907 -- assignments.
908 -- This way, all contacts who are also another type will be ignored,
909 -- it should be assumed that their assignments will be processed by an
910 -- earlier part of LISTGEN.
911 -- Query #2
912
913 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
914 hr_utility.set_location(l_proc||' using stage table', 24);
915 insert into per_person_list_stage(security_profile_id, request_id, program_id
916 ,program_application_id, program_update_date
917 ,person_id)
918 select distinct ppl.security_profile_id, l_req_id, l_prog_id,
919 l_appl_id, l_upd_date, pcr.contact_person_id
920 from per_contact_relationships pcr,
921 per_person_list_stage ppl
922 where ppl.person_id = pcr.person_id
923 and (pcr.business_group_id = p_business_group_id or
924 p_business_group_id is null)
925 and ppl.security_profile_id = p_security_profile_id
926 and not exists
927 (select null
928 from per_all_assignments_f asg
929 where asg.person_id = pcr.contact_person_id
930 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
931 and not exists
932 (select null
933 from per_person_list_stage ppl1
934 where ppl1.person_id = pcr.contact_person_id
935 and ppl1.granted_user_id is null
936 and ppl1.security_profile_id = ppl.security_profile_id);
937 else
938 hr_utility.set_location(l_proc||' using actual table', 28);
939 insert into per_person_list(security_profile_id, request_id, program_id
940 ,program_application_id, program_update_date
941 ,person_id)
942 select distinct ppl.security_profile_id, l_req_id, l_prog_id,
943 l_appl_id, l_upd_date, pcr.contact_person_id
944 from per_contact_relationships pcr,
945 per_person_list ppl
946 where ppl.person_id = pcr.person_id
947 and (pcr.business_group_id = p_business_group_id or
948 p_business_group_id is null)
949 and ppl.security_profile_id = p_security_profile_id
950 and not exists
951 (select null
952 from per_all_assignments_f asg
953 where asg.person_id = pcr.contact_person_id
954 and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
955 and not exists
956 (select null
957 from per_person_list ppl1
958 where ppl1.person_id = pcr.contact_person_id
959 and ppl1.granted_user_id is null
960 and ppl1.security_profile_id = ppl.security_profile_id);
961 end if;
962 --
963 hr_utility.set_location(l_proc, 30);
964 End if;
965 -- Inserts all unrelated contacts(excluding the candidates, those
966 -- registered from iRecruitment) who do not have any other assignments.
967 -- If there are additional assignments these will be excluded from here
968 -- on the assumption that they would have been evaluated in the previous
969 -- stages of listgen.
970 -- Query #3
971
972 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
973 hr_utility.set_location(l_proc||' using stage table', 34);
974 insert into per_person_list_stage(security_profile_id, request_id,
975 program_application_id, program_id,
976 program_update_date, person_id)
977 select distinct psp.security_profile_id, l_req_id, l_appl_id,
978 l_prog_id, l_upd_date, papf.person_id
979 from per_all_people_f papf,
980 per_security_profiles psp
981 where psp.security_profile_id = p_security_profile_id
982 and (psp.business_group_id = papf.business_group_id or
983 psp.business_group_id is null)
984 and not exists
985 (select null
986 from per_all_assignments_f asg
987 where asg.person_id = papf.person_id)
988 and not exists ---- Rever Commneted for for bug 4774264
989 (select null
990 from per_contact_relationships pcr
991 where pcr.contact_person_id = papf.person_id)
992 and not exists
993 (select null
994 from per_person_type_usages_f ptuf,
995 per_person_types ppt
996 where ppt.system_person_type = 'IRC_REG_USER'
997 and ptuf.person_type_id = ppt.person_type_id
998 and ptuf.person_id = papf.person_id)
999 and not exists
1000 (select null
1001 from per_person_list_stage ppl
1002 where ppl.person_id = papf.person_id
1003 and ppl.granted_user_id is null
1004 and ppl.security_profile_id = psp.security_profile_id);
1005 else
1006 hr_utility.set_location(l_proc||' using actual table', 38);
1007 insert into per_person_list(security_profile_id, request_id,
1008 program_application_id, program_id,
1009 program_update_date, person_id)
1010 select distinct psp.security_profile_id, l_req_id, l_appl_id,
1011 l_prog_id, l_upd_date, papf.person_id
1012 from per_all_people_f papf,
1013 per_security_profiles psp
1014 where psp.security_profile_id = p_security_profile_id
1015 and (psp.business_group_id = papf.business_group_id or
1016 psp.business_group_id is null)
1017 and not exists
1018 (select null
1019 from per_all_assignments_f asg
1020 where asg.person_id = papf.person_id)
1021 and not exists ---- Rever Commneted for for bug 4774264
1022 (select null
1023 from per_contact_relationships pcr
1024 where pcr.contact_person_id = papf.person_id)
1025 and not exists
1026 (select null
1027 from per_person_type_usages_f ptuf,
1028 per_person_types ppt
1029 where ppt.system_person_type = 'IRC_REG_USER'
1030 and ptuf.person_type_id = ppt.person_type_id
1031 and ptuf.person_id = papf.person_id)
1032 and not exists
1033 (select null
1034 from per_person_list ppl
1035 where ppl.person_id = papf.person_id
1036 and ppl.granted_user_id is null
1037 and ppl.security_profile_id = psp.security_profile_id);
1038 end if;
1039 --
1040 hr_utility.set_location('Leaving: ' || l_proc, 99);
1041 --
1042 end build_contact_list;
1043 --
1044 /* =======================================================================
1045 NAME
1046 add_person_list_changes
1047 DESCRIPTION
1048 Insert additional person list entries for persons in the person list
1049 changes table. If an entry exists for the security profile in the
1050 person list changes table and there is not an entry already for that
1051 person in the person list then a row is inserted. Only persons who
1052 have a termination date before the effective date and who do
1053 not have a current period of service (at effective date) are added. As
1054 'B' assignments are created on termination we need to exclude these
1055 assignments from consideration.
1056 PARAMETERS
1057 l_security_profile_id - identifier of the current security profile.
1058 l_effective_date - date for which the secure lists are generated.
1059 l_update_date - today's date.
1060 ========================================================================= */
1061 --
1062 PROCEDURE add_person_list_changes (l_security_profile_id NUMBER,
1063 l_effective_date DATE,
1064 l_update_date DATE)
1065 IS
1066 l_proc varchar2(72):= g_package||'add_person_list_changes';
1067 BEGIN
1068 --
1069 hr_utility.set_location('Entering: '||l_proc,10);
1070 --
1071 -- Start change for bug 13504049
1072 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1073 hr_utility.set_location(l_proc||' using stage table', 14);
1074 INSERT INTO per_person_list_stage
1075 (security_profile_id,
1076 person_id,
1077 request_id,
1078 program_application_id,
1079 program_id,
1080 program_update_date )
1081 SELECT DISTINCT l_security_profile_id,
1082 plc.person_id,
1083 p_request_id,
1084 p_program_application_id,
1085 p_program_id,
1086 l_update_date
1087 FROM per_person_list_changes plc
1088 WHERE plc.security_profile_id = l_security_profile_id
1089 AND NOT EXISTS
1090 (SELECT NULL
1091 FROM per_all_assignments_f pos
1092 WHERE pos.person_id = plc.person_id
1093 AND pos.assignment_type <> 'B'
1094 AND l_effective_date
1095 BETWEEN pos.effective_start_date
1096 AND pos.effective_end_date)
1097 AND EXISTS
1098 (SELECT NULL
1099 FROM per_all_assignments_f pos
1100 WHERE pos.person_id = plc.person_id
1101 AND l_effective_date > pos.effective_start_date)
1102 AND NOT EXISTS
1103 (SELECT NULL
1104 FROM per_person_list_stage ppl
1105 WHERE ppl.person_id = plc.person_id
1106 AND ppl.granted_user_Id IS NULL
1107 AND ppl.security_profile_id = plc.security_profile_id);
1108 else
1109 hr_utility.set_location(l_proc||' using actual table', 18);
1110 INSERT INTO per_person_list
1111 (security_profile_id,
1112 person_id,
1113 request_id,
1114 program_application_id,
1115 program_id,
1116 program_update_date )
1117 SELECT DISTINCT l_security_profile_id,
1118 plc.person_id,
1119 p_request_id,
1120 p_program_application_id,
1121 p_program_id,
1122 l_update_date
1123 FROM per_person_list_changes plc
1124 WHERE plc.security_profile_id = l_security_profile_id
1125 AND NOT EXISTS
1126 (SELECT NULL
1127 FROM per_all_assignments_f pos
1128 WHERE pos.person_id = plc.person_id
1129 AND pos.assignment_type <> 'B'
1130 AND l_effective_date
1131 BETWEEN pos.effective_start_date
1132 AND pos.effective_end_date)
1133 AND EXISTS
1134 (SELECT NULL
1135 FROM per_all_assignments_f pos
1136 WHERE pos.person_id = plc.person_id
1137 AND l_effective_date > pos.effective_start_date)
1138 AND NOT EXISTS
1139 (SELECT NULL
1140 FROM per_person_list ppl
1141 WHERE ppl.person_id = plc.person_id
1142 AND ppl.granted_user_Id IS NULL
1143 AND ppl.security_profile_id = plc.security_profile_id);
1144 end if;
1145 -- End change for bug 13504049
1146 --
1147 hr_utility.set_location('hr_listgen.add_person_list_changes',20);
1148 --
1149 END add_person_list_changes;
1150
1151
1152 /* =======================================================================
1153 NAME
1154 create_person_list
1155 DESCRIPTION
1156 populates the per_person_list using dynamic sql
1157 PARAMETERS
1158 Few parameters are needed due it inheriting from the parent function
1159 sec_rec - Row in per_security_profiles for current profile
1160 ========================================================================= */
1161 --
1162
1163 PROCEDURE create_person_list(sec_rec PER_SECURITY_PROFILES%ROWTYPE,
1164 p_effective_date date,
1165 p_update_date date,
1166 p_who_to_process varchar2)
1167 IS
1168 l_select_text varchar2(500);
1169 l_where_clause varchar2(3000);
1170 l_restriction_flags varchar2(1000);
1171 l_execution_stmt varchar2(8500);
1172 l_execution_stmt2 varchar2(8500);
1173 l_exclude_flags varchar2(1000);
1174
1175 l_sec_rec_security_profile_id varchar2(2000);
1176
1177 -- for the bug 5214715
1178
1179 PROCEDURE execute_statement
1180 AS
1181 l_cursor_id NUMBER;
1182 l_dsql_text VARCHAR2(32767);
1183 l_num_of_rows NUMBER;
1184 l_num number(10);
1185 j number(10):=1;
1186 i number(10):=50;
1187 BEGIN
1188 hr_utility.set_location('Entering execute_statement',10);
1189 l_cursor_id := dbms_sql.open_cursor;
1190 fnd_dsql.set_cursor(l_cursor_id);
1191
1192 l_dsql_text := fnd_dsql.get_text(FALSE);
1193 l_num := length(l_dsql_text);
1194
1195 /* -- use to print the final sql query
1196 while j < l_num +50
1197 loop
1198 hr_utility.set_location(substr(l_dsql_text,j,i),10);
1199 -- hr_utility.set_location('-------------------------',20);
1200 -- dbms_output.put_line(substr(l_dsql_text,i));
1201 j := j+50;
1202 end loop;
1203 */
1204 dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
1205 hr_utility.set_location('after parse',10);
1206 fnd_dsql.do_binds;
1207 hr_utility.set_location('after bind',10);
1208 l_num_of_rows := dbms_sql.execute(l_cursor_id);
1209 hr_utility.set_location('after execuate ',10);
1210 dbms_sql.close_cursor(l_cursor_id);
1211 hr_utility.set_location('Leaveing execute_statement',10);
1212 END execute_statement;
1213
1214 /*-- for the bug 5214715 --*/
1215 PROCEDURE add_comm_str ( p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE ) as
1216 l_restriction_flags varchar2(2000);
1217 l_exclude_flags varchar2(2000);
1218 BEGIN
1219 hr_utility.set_location('Entering add_comm_str',10);
1220 l_restriction_flags :='';
1221 if (p_sec_rec.view_all_cwk_flag = 'N') then
1222 if length(l_restriction_flags)>0 then
1223 l_restriction_flags:=l_restriction_flags||' OR ';
1224 end if;
1225 l_restriction_flags:=l_restriction_flags||' ASSIGNMENT.assignment_type=''C''';
1226 end if;
1227
1228 if (p_sec_rec.view_all_employees_flag = 'N') then
1229 if length(l_restriction_flags)>0 then
1230 l_restriction_flags:=l_restriction_flags||' OR ';
1231 end if;
1232 l_restriction_flags:=l_restriction_flags||' ASSIGNMENT.assignment_type=''E''';
1233 end if;
1234
1235 if (p_sec_rec.view_all_applicants_flag = 'N') then
1236 /*
1237 ** Change in logic due to bug 3024532. Process Applicant assignments
1238 ** regardless of the employee/Cwk restriction. This means that EMP-APL
1239 ** are visible to a profile by virtue of Appl assignment even if Emp
1240 ** assignment is not visible.
1241 */
1242 if length(l_restriction_flags)>0 then
1243 l_restriction_flags:=l_restriction_flags||' OR ';
1244 end if;
1245 l_restriction_flags:=l_restriction_flags||
1246 '( ASSIGNMENT.assignment_type=''A'' )';
1247 end if;
1248
1249 if l_restriction_flags is not null or
1250 length(l_restriction_flags) = 0
1251 then
1252 fnd_dsql.add_text(' and ( ');
1253 fnd_dsql.add_text(l_restriction_flags);
1254 fnd_dsql.add_text(' ) ');
1255 end if;
1256
1257 l_exclude_flags :='';
1258 if (p_sec_rec.view_all_cwk_flag = 'X') then
1259 if length(l_exclude_flags)>0 then
1260 l_exclude_flags:=l_exclude_flags||' AND ';
1261 end if;
1262 l_exclude_flags:=l_exclude_flags||'ASSIGNMENT.assignment_type<>''C''';
1263 end if;
1264 --
1265 if (p_sec_rec.view_all_employees_flag = 'X') then
1266 if length(l_exclude_flags)>0 then
1267 l_exclude_flags:=l_exclude_flags||' AND ';
1268 end if;
1269 l_exclude_flags:=l_exclude_flags||'ASSIGNMENT.assignment_type<>''E''';
1270 end if;
1271 --
1272 if (p_sec_rec.view_all_applicants_flag = 'X') then
1273 if length(l_exclude_flags)>0 then
1274 l_exclude_flags:=l_exclude_flags||' AND ';
1275 end if;
1276 l_exclude_flags:=l_exclude_flags||
1277 '(ASSIGNMENT.assignment_type<>''A'' )';
1278 end if;
1279 --
1280 if l_exclude_flags is not null or
1281 length(l_exclude_flags) = 0
1282 then
1283 fnd_dsql.add_text(' and ( ');
1284 fnd_dsql.add_text(l_exclude_flags);
1285 fnd_dsql.add_text(' ) ');
1286 end if;
1287 hr_utility.set_location('Leaveing add_comm_str',10);
1288 END add_comm_str;
1289
1290 /*-- for the bug 5214715 --*/
1291 -- XXCUSTOM - procedure to add static insert statement text
1292 PROCEDURE init_statement
1293 (
1294 -- p_person_id IN NUMBER
1295 p_request_id IN NUMBER
1296 ,p_prog_appl_id IN NUMBER
1297 ,p_program_id IN NUMBER
1298 ,p_update_date IN DATE
1299 -- ,p_from_clause IN VARCHAR2
1300 --- ,p_generation_scope IN VARCHAR2
1301 -- ,p_business_group_id IN NUMBER
1302 -- ,p_assignment_type IN VARCHAR2
1303 ,p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE)
1304 AS
1305
1306 BEGIN
1307 hr_utility.set_location('Entering init_statement',10);
1308 fnd_dsql.init;
1309
1310 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1311 hr_utility.set_location('init_statement using stage table',14);
1312 fnd_dsql.add_text(
1313 'INSERT into per_person_list_stage
1314 (security_profile_id,
1315 person_id,
1316 request_id,
1317 program_application_id,
1318 program_id,
1319 program_update_date ) ');
1320 else
1321 hr_utility.set_location('init_statement using actual table',18);
1322 fnd_dsql.add_text(
1323 'INSERT into per_person_list
1324 (security_profile_id,
1325 person_id,
1326 request_id,
1327 program_application_id,
1328 program_id,
1329 program_update_date ) ');
1330 end if;
1331
1332 -- Add the insert statement and binds
1333 fnd_dsql.add_text( ' SELECT DISTINCT ');
1334 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335 fnd_dsql.add_text(' , assignment.person_id ');
1336 fnd_dsql.add_text(' ,');
1337 fnd_dsql.add_bind(nvl(p_request_id,''));
1338 fnd_dsql.add_text(' , ');
1339 fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340 fnd_dsql.add_text(' , ');
1341 fnd_dsql.add_bind(nvl(p_program_id,''));
1342 fnd_dsql.add_text(' , ');
1343 fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344 fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1345
1346 /*-------- additional select clause ---------------*/
1347
1348 if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1349 (p_sec_rec.view_all_applicants_flag = 'N'
1350 and (p_sec_rec.view_all_employees_flag <>'Y'
1351 or p_sec_rec.view_all_cwk_flag <>'Y')) then
1352 fnd_dsql.add_text(', per_all_people_f PERSON ');
1353 End if;
1354 --Commented for debug- 8533491
1355 /* if p_sec_rec.view_all_organizations_flag='N' then
1356 Fnd_dsql.add_text(', per_organization_list ol ');
1357 end if;*/
1358
1359 if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1360 Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1361 end if;
1362 --Commented for debug- 8533491
1363 /* if p_sec_rec.view_all_positions_flag='N' then
1364 fnd_dsql.add_text(', per_position_list pl ');
1365 end if;
1366
1367 if p_sec_rec.view_all_payrolls_flag='N' then
1368 fnd_dsql.add_text( ' , pay_payroll_list ppl ');
1369 end if;*/
1370
1371 if p_sec_rec.view_all_organizations_flag='Y' then
1372 null;
1373 end if;
1374 /*------------------ end additional select clause -----------------*/
1375
1376 /*-------------- start where clause -------------------*/
1377 fnd_dsql.add_text(' Where ');
1378 fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1379
1380 if p_sec_rec.business_group_id is null then
1381 fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1382 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1383 else
1384 fnd_dsql.add_bind(p_sec_rec.business_group_id);
1385 end if;
1386
1387 if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1388 (p_sec_rec.view_all_applicants_flag = 'N'
1389 and (p_sec_rec.view_all_employees_flag <>'Y'
1390 or p_sec_rec.view_all_cwk_flag <>'Y')) then
1391
1392 fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1393 fnd_dsql.add_text(' ( '); -- 5214715
1394 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395 fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396 /* Got a fresh future person */
1397 fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1399 fnd_dsql.add_text(' AND NOT EXISTS
1400 (SELECT NULL
1401 FROM per_all_people_f papf1
1402 WHERE papf1.person_id = PERSON.person_id
1403 AND papf1.effective_start_date < PERSON.effective_start_date)) ');
1404
1405 fnd_dsql.add_text(' ) '); -- 5214715
1406 End if;
1407
1408 if p_sec_rec.view_all_organizations_flag='N' then
1409 --Modified for debug- 8533491
1410 /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412 fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413
1414 fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415 fnd_dsql.add_text(' WHERE security_profile_id = ');
1416 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417 fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418 end if;
1419
1420 if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1421 fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1422 fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1423 fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1424 end if;
1425
1426 if p_sec_rec.view_all_positions_flag='N' then
1427 --Modified for debug-8533491
1428 /*fnd_dsql.add_text(' and pl.security_profile_id = ');
1429 fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430 fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431
1432 fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433 fnd_dsql.add_text(' WHERE security_profile_id = ');
1434 fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1435 fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');
1436
1437 end if;
1438
1439 if p_sec_rec.view_all_payrolls_flag='N' then
1440 --Modified for debug- 8533491.
1441 /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1442 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443 fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444 fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445
1446 fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447 fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448 fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449 fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450 -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');
1451 end if;
1452
1453 if p_sec_rec.view_all_organizations_flag='Y' then
1454 null;
1455 end if;
1456 /*-------------- End where clause -------------------*/
1457 hr_utility.set_location('Leaveing init_statement',10);
1458 end init_statement;
1459
1460 BEGIN
1461 hr_utility.set_location('Entering Create_person_list',10);
1462 init_statement
1463 (
1464 --p_person_id => p_person_id
1465 p_request_id => p_request_id
1466 ,p_prog_appl_id => p_program_application_id
1467 ,p_program_id => p_program_id
1468 ,p_update_date => p_update_date
1469 -- ,p_from_clause => p_from_clause
1470 -- ,p_generation_scope => p_generation_scope
1471 -- ,p_business_group_id => p_business_group_id
1472 -- ,p_assignment_type => p_assignment_type
1473 ,p_sec_rec => sec_rec
1474 );
1475
1476
1477 -- Selects what type of assignment records we are interested in
1478 -- (Only ones where the relevant flag is N (Restricted))
1479 --
1480 -- Also adds check to make sure the relevant number for the assignment is not
1481 -- null ie for assignment_type of E, PERSON.employee_number must not be null
1482 --
1483
1484 /*--------- Start for l_restriction_flags and l_exclude_flags ----------------*/
1485
1486 add_comm_str( p_sec_rec => sec_rec );
1487
1488 /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1489
1490 fnd_dsql.add_text(' and ( ');
1491 fnd_dsql.add_text(' ( '); -- 5214715
1492 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493 fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494 or ( ASSIGNMENT.effective_start_date>= ');
1495 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496 fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497 FROM per_all_assignments_f pos1
1498 WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1500 fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1501 fnd_dsql.add_text('
1502 AND NOT EXISTS
1503 (SELECT NULL
1504 FROM per_all_assignments_f pos1
1505 WHERE pos1.person_id = ASSIGNMENT.person_id
1506 AND ((pos1.assignment_type=''E'' and
1507 pos1.period_of_service_id=ASSIGNMENT.period_of_service_id) or
1508 (pos1.assignment_type=''A'' and
1509 pos1.application_id=ASSIGNMENT.application_id) or
1510 (pos1.assignment_type=''C'' and
1511 pos1.period_of_placement_date_start =
1512 ASSIGNMENT.period_of_placement_date_start))
1513 AND pos1.effective_start_date< ASSIGNMENT.effective_start_date)
1514 or (ASSIGNMENT.effective_end_date < ');
1515
1516 fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1517 -- added and clause for bug 5168364
1518 fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1519 from per_all_assignments_f asg
1520 where asg.person_id = assignment.person_id
1521 and asg.assignment_type in(''A'',''C'',''E'')
1522 ) ');
1523 fnd_dsql.add_text(' AND NOT EXISTS
1524 (SELECT NULL
1525 FROM per_all_assignments_f papf
1526 WHERE papf.person_Id = ASSIGNMENT.person_id
1527 AND papf.assignment_type in(''A'',''C'',''E'')
1528 AND papf.effective_end_date >= ');
1529 fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1530 fnd_dsql.add_text(' ))))'); -- 5214715
1531
1532 -- Start changes for bug 13504049
1533 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1534 fnd_dsql.add_text(' and not exists(select 1
1535 from per_person_list_stage ppl
1536 where ppl.security_profile_id = ');
1537 else
1538
1539 fnd_dsql.add_text(' and not exists(select 1
1540 from per_person_list ppl
1541 where ppl.security_profile_id = ');
1542 end if;
1543 -- End changes for bug 13504049
1544 fnd_dsql.add_bind(sec_rec.security_profile_id);
1545 fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1546 and ppl.granted_user_id is null)');
1547
1548 -- if the custom sql flag is set and the restriction text is not empty
1549 -- (>2 chars) then append the custom sql to the end of the statement
1550
1551 if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1552 fnd_dsql.add_text(' and ');
1553 fnd_dsql.add_text(sec_rec.restriction_text);
1554 end if;
1555
1556 if g_debug then
1557 hr_utility.trace('select '||to_char(length(l_select_text)));
1558 hr_utility.trace('where '||to_char(length(l_where_clause)));
1559 l_execution_stmt2:=l_execution_stmt;
1560 while length(l_execution_stmt2)>0 loop
1561 hr_utility.trace(substr(l_execution_stmt2,1,70));
1562 l_execution_stmt2:=substr(l_execution_stmt2,71);
1563 end loop;
1564 end if;
1565
1566 Execute_statement;
1567 hr_utility.set_location('Leaveing Craete_person_list',10);
1568 END create_person_list;
1569
1570
1571 --
1572 -- ----------------------------------------------------------------------------
1573 -- |---------------------< clear_per_list_table >-----------------------------|
1574 -- ----------------------------------------------------------------------------
1575 --
1576 procedure clear_per_list_table (
1577 p_person_id number,
1578 p_generation_scope varchar2,
1579 p_business_group_id number,
1580 p_effective_date date) is
1581 --
1582 l_proc varchar2(72) := g_package||'clear_per_list_table';
1583 --
1584 l_sub_str varchar2(1000);
1585 l_del_str varchar2(8000);
1586 l_exe_str varchar2(9000);
1587 l_effective_date date;
1588 --
1589 begin
1590 --
1591 hr_utility.set_location('Entering : '||l_proc,10);
1592 --
1593 -- Clear the records for this person.
1594
1595 -- Bug6809753 - start
1596 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1597 l_del_str := ' delete from per_person_list_stage ppl where
1598 ppl.person_id = :p_person_id
1599 and ppl.granted_user_id is null and exists
1600 (select ''X'' from per_security_profiles pspf
1601 where pspf.security_profile_id = ppl.security_profile_id ';
1602 else
1603 l_del_str := ' delete from per_person_list ppl where
1604 ppl.person_id = :p_person_id
1605 and ppl.granted_user_id is null and exists
1606 (select ''X'' from per_security_profiles pspf
1607 where pspf.security_profile_id = ppl.security_profile_id ';
1608 end if;
1609 -- Bug6809753 - end
1610
1611 if p_generation_scope = 'ALL_BUS_GRP' then
1612
1613 /* Commented Bug6809753 - start
1614 delete from per_person_list ppl where
1615 ppl.person_id = p_person_id
1616 and ppl.granted_user_id is null and exists
1617 (select 'X' from per_security_profiles pspf
1618 where pspf.security_profile_id = ppl.security_profile_id
1619 and pspf.business_group_id = p_business_group_id);
1620 Commented Bug6809753 - end */
1621
1622 -- Bug6809753 - start
1623 l_sub_str := ' and pspf.business_group_id = :p_business_group_id ';
1624 -- Bug6809753 - end
1625
1626 end if;
1627 if p_generation_scope = 'ALL_GLOBAL' then
1628
1629 /* Commented Bug6809753 - start
1630 delete from per_person_list ppl where
1631 ppl.person_id = p_person_id
1632 and ppl.granted_user_id is null and exists
1633 (select 'X' from per_security_profiles pspf
1634 where pspf.security_profile_id = ppl.security_profile_id
1635 and pspf.business_group_id is null);
1636 Commented Bug6809753 - end */
1637
1638 -- Bug6809753 - start
1639 l_sub_str := ' and pspf.business_group_id is null ';
1640 -- Bug6809753 - end
1641
1642 end if;
1643 if p_generation_scope = 'ALL_PROFILES' then
1644
1645 /* Commented Bug6809753 - start
1646 delete from per_person_list ppl where
1647 ppl.person_id = p_person_id
1648 and ppl.granted_user_id is null and exists
1649 (select 'X' from per_security_profiles pspf
1650 where pspf.security_profile_id = ppl.security_profile_id);
1651 Commented Bug6809753 - end */
1652
1653 -- Bug6809753 - start
1654 l_sub_str := '';
1655 -- Bug6809753 - end
1656
1657 end if;
1658 -- un commanted the below line - Bug6809753
1659 l_exe_str := l_del_str||l_sub_str||')';
1660
1661 if p_generation_scope = 'ALL_BUS_GRP' then
1662 execute immediate l_exe_str
1663 using p_person_id,p_business_group_id;
1664 else
1665 execute immediate l_exe_str
1666 using p_person_id;
1667 end if;
1668
1669 -- Clear records for the contacts of this person
1670 -- 2906862 - dkerr 2003-05-01
1671 -- I've restricted the scan of PER_SECURITY_PROFILES to restricted
1672 -- contact profiles - which can drastically reduce the amount of I/O
1673 -- performed.
1674 -- Analysis of major customer data suggests the following :
1675 -- 1. Security profiles with "view_all_contacts_flag='N'"
1676 -- are usually a minority.
1677 -- 2. A typical installation may have hundreds of security
1678 -- profiles.
1679 --
1680
1681 /* Commented Bug6809753 - start
1682
1683 if p_generation_scope = 'ALL_BUS_GRP' then
1684
1685 delete from per_person_list ppl
1686 where ppl.security_profile_id in
1687 (select pspf.security_profile_id
1688 from per_security_profiles pspf
1689 where (pspf.view_all_contacts_flag = 'N' or
1690 (pspf.view_all_contacts_flag = 'Y' and
1691 pspf.view_all_candidates_flag = 'X'))
1692 and pspf.business_group_id = p_business_group_id
1693 )
1694 and ppl.person_id in (
1695 select pcr.contact_person_id
1696 from per_contact_relationships pcr,
1697 per_person_type_usages_f ptu,
1698 per_person_types ppt
1699 where pcr.person_id = p_person_id
1700 and pcr.contact_person_id = ptu.person_id
1701 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1702 between ptu.effective_start_date
1703 and ptu.effective_end_date
1704 and ptu.person_type_id = ppt.person_type_id
1705 and ppt.system_person_type = 'OTHER')
1706 and ppl.granted_user_id is null;
1707
1708
1709 End if;
1710 if p_generation_scope = 'ALL_GLOBAL' then
1711 delete from per_person_list ppl
1712 where ppl.security_profile_id in
1713 (select pspf.security_profile_id
1714 from per_security_profiles pspf
1715 where (pspf.view_all_contacts_flag = 'N' or
1716 (pspf.view_all_contacts_flag = 'Y' and
1717 pspf.view_all_candidates_flag = 'X'))
1718 and pspf.business_group_id is null
1719 )
1720 and ppl.person_id in (
1721 select pcr.contact_person_id
1722 from per_contact_relationships pcr,
1723 per_person_type_usages_f ptu,
1724 per_person_types ppt
1725 where pcr.person_id = p_person_id
1726 and pcr.contact_person_id = ptu.person_id
1727 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1728 between ptu.effective_start_date
1729 and ptu.effective_end_date
1730 and ptu.person_type_id = ppt.person_type_id
1731 and ppt.system_person_type = 'OTHER')
1732 and ppl.granted_user_id is null;
1733 End if;
1734 if p_generation_scope = 'ALL_PROFILES' then
1735 delete from per_person_list ppl
1736 where ppl.security_profile_id in
1737 (select pspf.security_profile_id
1738 from per_security_profiles pspf
1739 where (pspf.view_all_contacts_flag = 'N' or
1740 (pspf.view_all_contacts_flag = 'Y' and
1741 pspf.view_all_candidates_flag = 'X'))
1742 )
1743 and ppl.person_id in (
1744 select pcr.contact_person_id
1745 from per_contact_relationships pcr,
1746 per_person_type_usages_f ptu,
1747 per_person_types ppt
1748 where pcr.person_id = p_person_id
1749 and pcr.contact_person_id = ptu.person_id
1750 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
1751
1752 between ptu.effective_start_date
1753 and ptu.effective_end_date
1754 and ptu.person_type_id = ppt.person_type_id
1755 and ppt.system_person_type = 'OTHER')
1756 and ppl.granted_user_id is null;
1757 End if;
1758
1759 Commented Bug6809753 - end */
1760 --
1761 -- Bug6809753 -start
1762 l_effective_date := to_date(to_char(p_effective_date,'dd/mm/yyyy'),'dd/mm/yyyy');
1763
1764 if p_generation_scope = 'ALL_BUS_GRP' then
1765
1766 l_del_str := ' delete from per_person_list ppl
1767 where ppl.security_profile_id in
1768 (select pspf.security_profile_id
1769 from per_security_profiles pspf
1770 where (pspf.view_all_contacts_flag = ''N'' or
1771 (pspf.view_all_contacts_flag = ''Y'' and
1772 pspf.view_all_candidates_flag = ''X''))
1773 and pspf.business_group_id = :p_business_group_id )
1774 and ppl.person_id in (
1775 select pcr.contact_person_id
1776 from per_contact_relationships pcr,
1777 per_person_type_usages_f ptu,
1778 per_person_types ppt
1779 where pcr.person_id = :p_person_id
1780 and pcr.contact_person_id = ptu.person_id
1781 and :l_effective_date
1782 between ptu.effective_start_date
1783 and ptu.effective_end_date
1784 and ptu.person_type_id = ppt.person_type_id
1785 and ppt.system_person_type = ''OTHER'')
1786 and ppl.granted_user_id is null';
1787 --
1788 l_exe_str := l_del_str;
1789 --
1790 execute immediate l_exe_str
1791 using p_business_group_id,p_person_id,
1792 l_effective_date;
1793 end if;
1794
1795 if p_generation_scope = 'ALL_GLOBAL' then
1796 l_del_str := ' delete from per_person_list ppl
1797 where ppl.security_profile_id in
1798 (select pspf.security_profile_id
1799 from per_security_profiles pspf
1800 where (pspf.view_all_contacts_flag = ''N'' or
1801 (pspf.view_all_contacts_flag = ''Y'' and
1802 pspf.view_all_candidates_flag = ''X''))
1803 and pspf.business_group_id is null )
1804 and ppl.person_id in (
1805 select pcr.contact_person_id
1806 from per_contact_relationships pcr,
1807 per_person_type_usages_f ptu,
1808 per_person_types ppt
1809 where pcr.person_id = :p_person_id
1810 and pcr.contact_person_id = ptu.person_id
1811 and :l_effective_date
1812 between ptu.effective_start_date
1813 and ptu.effective_end_date
1814 and ptu.person_type_id = ppt.person_type_id
1815 and ppt.system_person_type = ''OTHER'')
1816 and ppl.granted_user_id is null';
1817 --
1818 l_exe_str := l_del_str;
1819 --
1820 execute immediate l_exe_str
1821 using p_person_id,
1822 l_effective_date;
1823 end if;
1824
1825 if p_generation_scope = 'ALL_PROFILES' then
1826 l_del_str := ' delete from per_person_list ppl
1827 where ppl.security_profile_id in
1828 (select pspf.security_profile_id
1829 from per_security_profiles pspf
1830 where (pspf.view_all_contacts_flag = ''N'' or
1831 (pspf.view_all_contacts_flag = ''Y'' and
1832 pspf.view_all_candidates_flag = ''X'')))
1833 and ppl.person_id in (
1834 select pcr.contact_person_id
1835 from per_contact_relationships pcr,
1836 per_person_type_usages_f ptu,
1837 per_person_types ppt
1838 where pcr.person_id = :p_person_id
1839 and pcr.contact_person_id = ptu.person_id
1840 and :l_effective_date
1841 between ptu.effective_start_date
1842 and ptu.effective_end_date
1843 and ptu.person_type_id = ppt.person_type_id
1844 and ppt.system_person_type = ''OTHER'')
1845 and ppl.granted_user_id is null';
1846 --
1847 l_exe_str := l_del_str;
1848 --
1849 execute immediate l_exe_str
1850 using p_person_id,
1851 l_effective_date;
1852 end if;
1853
1854 -- Bug6809753 -end
1855
1856 hr_utility.set_location('Leaving : '||l_proc,20);
1857 --
1858 end clear_per_list_table;
1859 --
1860 --
1861 -- ----------------------------------------------------------------------------
1862 -- |----------------------< clear_unrelated_contacts >------------------------|
1863 -- ----------------------------------------------------------------------------
1864 --
1865 procedure clear_unrelated_contacts (
1866 p_generation_scope in varchar2,
1867 p_business_group_id in number
1868 ) is
1869 --
1870 l_proc varchar2(72) := g_package||'clear_unrelated_contacts';
1871 --
1872 begin
1873 --
1874 hr_utility.set_location('Entering : '||l_proc,10);
1875 --
1876 if p_generation_scope <> 'ALL_BUS_GRP' then
1877
1878 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1879 hr_utility.set_location(l_proc||' using stage table',14);
1880 delete from per_person_list_stage ppl
1881 where ppl.security_profile_id in
1882 (select pspf.security_profile_id
1883 from per_security_profiles pspf
1884 where (pspf.view_all_contacts_flag = 'N' or
1885 (pspf.view_all_contacts_flag = 'Y' and
1886 pspf.view_all_candidates_flag = 'X')))
1887 /* and ppl.person_id in
1888 (select papf.person_id
1889 from per_all_people_f papf
1890 where papf.person_id = ppl.person_id
1891 and ((p_generation_scope = 'ALL_BUS_GRP' and
1892 papf.business_group_id = p_business_group_id) or
1893 p_generation_scope <> 'ALL_BUS_GRP')) */
1894 and not exists
1895 (select null
1896 from per_all_assignments_f asg
1897 where asg.person_id = ppl.person_id)
1898 and not exists
1899 (select null
1900 from per_contact_relationships pcr
1901 where pcr.contact_person_id = ppl.person_id);
1902 else
1903 hr_utility.set_location(l_proc||' using actual table',18);
1904 delete from per_person_list ppl
1905 where ppl.security_profile_id in
1906 (select pspf.security_profile_id
1907 from per_security_profiles pspf
1908 where (pspf.view_all_contacts_flag = 'N' or
1909 (pspf.view_all_contacts_flag = 'Y' and
1910 pspf.view_all_candidates_flag = 'X')))
1911 /* and ppl.person_id in
1912 (select papf.person_id
1913 from per_all_people_f papf
1914 where papf.person_id = ppl.person_id
1915 and ((p_generation_scope = 'ALL_BUS_GRP' and
1916 papf.business_group_id = p_business_group_id) or
1917 p_generation_scope <> 'ALL_BUS_GRP')) */
1918 and not exists
1919 (select null
1920 from per_all_assignments_f asg
1921 where asg.person_id = ppl.person_id)
1922 and not exists
1923 (select null
1924 from per_contact_relationships pcr
1925 where pcr.contact_person_id = ppl.person_id);
1926 end if;
1927
1928 else
1929 delete from per_person_list ppl
1930 where ppl.security_profile_id in
1931 (select pspf.security_profile_id
1932 from per_security_profiles pspf
1933 where (pspf.view_all_contacts_flag = 'N' or
1934 (pspf.view_all_contacts_flag = 'Y' and
1935 pspf.view_all_candidates_flag = 'X')))
1936 and ppl.person_id in
1937 (select papf.person_id
1938 from per_all_people_f papf
1939 where papf.person_id = ppl.person_id
1940 and papf.business_group_id = p_business_group_id)
1941 and not exists
1942 (select null
1943 from per_all_assignments_f asg
1944 where asg.person_id = ppl.person_id)
1945 and not exists
1946 (select null
1947 from per_contact_relationships pcr
1948 where pcr.contact_person_id = ppl.person_id);
1949 end if;
1950 --
1951 hr_utility.set_location('Leaving : '||l_proc,20);
1952 --
1953 end clear_unrelated_contacts;
1954 --
1955 -- ----------------------------------------------------------------------------
1956 -- |---------------------< clear_sp_list_table >------------------------------|
1957 -- ----------------------------------------------------------------------------
1958 --
1959 procedure clear_sp_list_table (
1960 p_generation_scope varchar2,
1961 p_business_group_id number,
1962 p_security_profile_id number,
1963 p_clear_people_flag boolean
1964 ) is
1965 --
1966 l_proc varchar2(72) := g_package||'clear_sp_list_table';
1967 --
1968 begin
1969 --
1970 hr_utility.set_location('Entering : '||l_proc, 10);
1971 -- Clearing Organization static list
1972 delete
1973 from per_organization_list
1974 where security_profile_id = p_security_profile_id
1975 and user_id is null;
1976 --
1977 hr_utility.set_location(l_proc, 20);
1978 -- Clearing Position static list
1979 delete
1980 from per_position_list
1981 where security_profile_id = p_security_profile_id
1982 and user_id is null;
1983 --
1984 hr_utility.set_location(l_proc, 30);
1985 -- Clearing the Person static list
1986 if p_clear_people_flag then
1987 --
1988 -- Start changes for bug 13504049
1989 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1990 hr_utility.set_location(l_proc||' using stage table',34);
1991 delete
1992 from per_person_list_stage
1993 where security_profile_id = p_security_profile_id
1994 and granted_user_id is null;
1995 else
1996 hr_utility.set_location(l_proc||' using actual table',38);
1997 delete
1998 from per_person_list
1999 where security_profile_id = p_security_profile_id
2000 and granted_user_id is null;
2001 end if;
2002 -- End changes for bug 13504049
2003 --
2004 end if;
2005 --
2006 hr_utility.set_location(l_proc, 40);
2007 -- Clearing Payroll static list
2008 delete
2009 from pay_payroll_list
2010 where security_profile_id = p_security_profile_id;
2011 --
2012 hr_utility.set_location('Leaving : '||l_proc, 99);
2013 --
2014 end clear_sp_list_table;
2015 --
2016 /* =======================================================================
2017 NAME
2018 process_person
2019 DESCRIPTION
2020
2021 PARAMETERS
2022 l_effective_date - date for which the secure lists are generated.
2023 ========================================================================= */
2024 --
2025 PROCEDURE process_person (p_person_id per_all_people_f.person_id%TYPE,
2026 p_effective_date date,
2027 p_business_group_id number,
2028 p_generation_scope varchar2,
2029 p_who_to_process varchar2)
2030 IS
2031 l_proc varchar2(50) := g_package||'process_person';
2032 l_effective_date date;
2033
2034 /*
2035 ** Notes on this cursor:
2036 **
2037 ** Returns people who are :
2038 ** Current on effective date.
2039 ** Current at somepoint in the future and either don't exist today
2040 ** or are 'ex' people today.
2041 **
2042 ** The date returned is the greatest out of the calculated ESD and the
2043 ** effective date.
2044 **
2045 ** For a current applicant who is hired in the future then that person
2046 ** will have PTU data which causes them to qualify against both these
2047 ** conditions. The min(esd) will result in only one of those PTU records
2048 ** to be taken into account and so the correct date is calculated.
2049 */
2050 /* Commented Bug6809753 - start
2051 cursor c_current_person is
2052 select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
2053 from per_person_type_usages_f ppf,
2054 per_person_types ppt
2055 where ppf.person_id = p_person_id
2056 and ppf.person_type_id = ppt.person_type_id --taken out for Performance bug.
2057 -- Current person today
2058 and (( -- ppf.person_type_id = ppt.person_type_id and
2059 ppt.business_group_id =
2060 nvl(p_business_group_id,ppt.business_group_id)
2061 and p_effective_date between ppf.effective_start_date
2062 and ppf.effective_end_date
2063 and ppt.system_person_type in ('EMP','APL','CWK'))
2064 OR
2065 -- Future person
2066 ( -- ppf.person_type_id = ppt.person_type_id and
2067 ppt.business_group_id =
2068 nvl(p_business_group_id,ppt.business_group_id)
2069 and p_effective_date < ppf.effective_start_date
2070 and ppt.system_person_type in ('EMP','APL','CWK')))
2071 group by ppf.person_id;
2072 Commented Bug6809753 - end */
2073
2074 -- Bug6809753 - start
2075 cursor c_current_person is
2076 select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
2077 from per_person_type_usages_f ppf,
2078 per_person_types ppt
2079 where ppf.person_id = p_person_id
2080 /*
2081 ** Current person today
2082 */
2083 and (( ppf.person_type_id = ppt.person_type_id
2084 and ppt.business_group_id =
2085 nvl(p_business_group_id,ppt.business_group_id)
2086 and p_effective_date between ppf.effective_start_date
2087 and ppf.effective_end_date
2088 and ppt.system_person_type in ('EMP','APL','CWK'))
2089 OR
2090 /*
2091 ** Future person
2092 */
2093 ( ppf.person_type_id = ppt.person_type_id
2094 and ppt.business_group_id =
2095 nvl(p_business_group_id,ppt.business_group_id)
2096 and p_effective_date < ppf.effective_start_date
2097 and ppt.system_person_type in ('EMP','APL','CWK')))
2098 group by ppf.person_id;
2099
2100 /*-- Start changes made for the bug 5252738 - Bug6809753 ---*/
2101 /* ** To exclude Current person today */
2102
2103 cursor c_exclude_person is
2104 select ppf.person_id
2105 from per_person_type_usages_f ppf,
2106 per_person_types ppt
2107 where ppf.person_id = p_person_id
2108
2109 and ( ppf.person_type_id = ppt.person_type_id
2110 and ppt.business_group_id =
2111 nvl(p_business_group_id,ppt.business_group_id)
2112 and p_effective_date between ppf.effective_start_date
2113 and ppf.effective_end_date
2114 and ppt.system_person_type in ('EMP','APL','CWK'));
2115 /*-- End changes made for the bug 5252738 -Bug6809753 ---*/
2116 -- Bug6809753 - End
2117
2118 cursor c_former_person is
2119 select ppf.person_id, paf.assignment_type,
2120 least(max(paf.effective_end_date), p_effective_date) effective_date
2121 from per_person_type_usages_f ppf,
2122 per_person_types ppt,
2123 per_all_assignments_f paf
2124 where ppf.person_id = p_person_id
2125 and ppf.person_id = paf.person_id
2126 and paf.assignment_type in ('A','C','E')
2127 and paf.effective_start_date < p_effective_date
2128 /*
2129 ** Existed as a current person at somepoint in history
2130 */
2131 and ( ppf.person_type_id = ppt.person_type_id
2132 and p_effective_date > ppf.effective_start_date
2133 and ppt.system_person_type in ('EMP','APL','CWK'))
2134 /*
2135 ** ...as an ex person on the effective date
2136 */
2137 and exists (select null
2138 from per_person_type_usages_f ppf1,
2139 per_person_types ppt1
2140 where ppf1.person_id = ppf.person_id
2141 and p_effective_date between ppf1.effective_start_date
2142 and ppf1.effective_end_date
2143 and ppf1.person_type_id = ppt1.person_type_id
2144 and ppt1.business_group_id = nvl(p_business_group_id,
2145 ppt1.business_group_id)
2146 and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
2147 /*
2148 ** ...and not a current person on effective date or in the future.
2149 **
2150 ** (Due to the implementation of PTU I can be both EMP and EX-APL
2151 ** today. i.e. I'm an employee who was successfully hired after
2152 ** some application process. In this case the person should be
2153 ** processed as a current and not an ex person. Note the
2154 ** exception for APLs who are former EMPs/CWKs - in this
2155 ** case an APL who is also term'd should be visible as both an
2156 ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
2157 ** who are EX-EMP/EX-CWK but who are also APL
2158 */
2159 and not exists (select null
2160 from per_person_type_usages_f ppf2,
2161 per_person_types ppt2
2162 where ppf2.person_id = ppf.person_id
2163 and p_effective_date < ppf2.effective_end_date
2164 and ppf2.person_type_id = ppt2.person_type_id
2165 and ppt2.business_group_id = nvl(p_business_group_id,
2166 ppt2.business_group_id)
2167 and ppt2.system_person_type in ('EMP','CWK'))
2168 group by ppf.person_id, paf.assignment_type
2169 order by effective_date desc;
2170
2171 cursor c_get_asg(p_person_id number,
2172 p_effective_date date) is
2173 select paf.assignment_id, paf.effective_start_date
2174 from per_all_assignments_f paf
2175 where paf.person_id = p_person_id
2176 and paf.assignment_type not in ('B','O') -- added from bug 4352765, Bug 7412855
2177 and ( (p_effective_date between paf.effective_start_date
2178 and paf.effective_end_date)
2179 or
2180 (paf.effective_start_date > p_effective_date and
2181 not exists (select null
2182 from per_all_assignments_f paf1
2183 where paf1.assignment_id = paf.assignment_id
2184 and paf1.effective_start_date <=
2185 paf.effective_start_date)));
2186
2187 cursor c_is_current_apl is
2188 select 'Y'
2189 from per_person_type_usages_f ptu,
2190 per_person_types ppt
2191 where ptu.person_id = p_person_id
2192 and p_effective_date < ptu.effective_end_date
2193 and ptu.person_type_Id = ppt.person_type_id
2194 and ppt.system_person_type = 'APL';
2195 cursor c_is_former is
2196 select 'Y'
2197 from per_person_type_usages_f ptu,
2198 per_person_types ppt
2199 where ptu.person_id = p_person_id
2200 and p_effective_date between ptu.effective_start_date
2201 and ptu.effective_end_date
2202 and ptu.person_type_id = ppt.person_type_id
2203 and ppt.system_person_type in ('EX_EMP','EX_CWK');
2204
2205 l_is_current_apl varchar2(1) := 'N';
2206 l_is_former varchar2(1) := 'N';
2207
2208 l_person_id number;
2209 l_assignment_type PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE%TYPE;
2210 l_person_proc_date date;
2211 lc_person_id number(10); -- Bug6809753
2212 l_cleared_ppl boolean := FALSE;
2213
2214
2215 -- Code changes for bug 8691129 Start
2216
2217 TYPE l_security_profile_type IS TABLE OF per_security_profiles.security_profile_id%TYPE INDEX BY BINARY_INTEGER;
2218 l_security_profile_table l_security_profile_type;
2219 l_c_security_profile_table l_security_profile_type;
2220 l_d_security_profile_table l_security_profile_type;
2221 l_security_profile_table_temp l_security_profile_type;
2222
2223 l_collection_index number:=0;
2224
2225
2226 procedure profile_add_to_person_list
2227 (p_effective_date in date
2228 ,p_assignment_id in number
2229 ,p_business_group_id in number default null
2230 ,p_generation_scope in varchar2 default 'ALL_PROFILES') IS
2231
2232 l_proc varchar2(72) := g_package||'process_person.profile_add_to_person_list';
2233
2234 cursor asg_details is
2235 select paf.person_id,
2236 paf.organization_id,
2237 paf.position_id,
2238 paf.payroll_id,
2239 paf.business_group_id,
2240 paf.assignment_type,
2241 ppf.current_employee_flag,
2242 ppf.current_npw_flag
2243 from per_all_assignments_f paf,
2244 per_all_people_f ppf
2245 where paf.assignment_id = p_assignment_id
2246 and p_effective_date between paf.effective_start_date
2247 and paf.effective_end_date
2248 and paf.person_id = ppf.person_id
2249 and p_effective_date between ppf.effective_start_date
2250 and ppf.effective_end_date;
2251 l_person_id per_all_assignments_f.person_id%type;
2252 l_organization_id per_all_assignments_f.organization_id%type;
2253 l_position_id per_all_assignments_f.position_id%type;
2254 l_payroll_id per_all_assignments_f.payroll_id%type;
2255 l_business_group_id per_all_assignments_f.business_group_id%type;
2256 l_assignment_type per_all_assignments_f.assignment_type%type;
2257 l_current_employee_flag per_all_people_f.current_employee_flag%type;
2258 l_current_npw_flag per_all_people_f.current_npw_flag%type;
2259
2260 l_bggr_str varchar2(1000);
2261 l_asgt_str varchar2(2000);
2262 l_from_str varchar2(2000);
2263 l_inst_str varchar2(3000);
2264 l_comm_str varchar2(5000);
2265 l_cond_str varchar2(5000);
2266 l_exec_str varchar2(18000);
2267 l_exec_str_print varchar2(18000);
2268
2269 l_all varchar2(1) := 'Y';
2270 l_Restrict varchar2(1) := 'N';
2271 l_none varchar2(1) := 'X';
2272 l_U varchar2(1) := 'U';
2273 l_S varchar2(1) := 'S';
2274 l_true varchar2(4) := 'TRUE';
2275 l_false varchar2(5) := 'FALSE';
2276
2277
2278 PROCEDURE profile_add_to_cache IS
2279 l_proc varchar2(100):= 'profile_add_to_person_list.profile_add_to_cache';
2280 Begin
2281 if g_debug then
2282 hr_utility.set_location('Entering '||l_proc, 10);
2283 End if;
2284 If l_security_profile_table_temp.count > 0 then
2285 if g_debug then
2286 hr_utility.set_location(l_proc, 20);
2287 End if;
2288 for I in l_security_profile_table_temp.first .. l_security_profile_table_temp.last
2289 loop
2290 hr_utility.set_location(l_security_profile_table_temp(i), 25);
2291 l_security_profile_table(l_collection_index + i):= l_security_profile_table_temp(i);
2292 end loop;
2293 l_collection_index := l_security_profile_table.last;
2294 End if;
2295 if g_debug then
2296 hr_utility.set_location('Leaveing '||l_proc, 30);
2297 End if;
2298 End profile_add_to_cache;
2299
2300 begin
2301
2302 if g_debug then
2303 hr_utility.set_location('Entering:'|| l_proc, 10);
2304 end if;
2305 open asg_details;
2306 fetch asg_details into
2307 l_person_id,
2308 l_organization_id,
2309 l_position_id,
2310 l_payroll_id,
2311 l_business_group_id,
2312 l_assignment_type,
2313 l_current_employee_flag,
2314 l_current_npw_flag;
2315 if g_debug then
2316 hr_utility.set_location(l_proc, 30);
2317 end if;
2318 if(asg_details%found) then
2319 close asg_details;
2320 if p_generation_scope = 'ALL_BUS_GRP' then
2321 hr_utility.set_location(l_proc, 31);
2322 l_bggr_str := ' sec.business_group_id = :l_business_group_id and ';
2323 end if;
2324 if p_generation_scope = 'ALL_GLOBAL' then
2325 hr_utility.set_location(l_proc, 32);
2326 l_bggr_str := ' sec.business_group_id is null and ';
2327 end if;
2328 if p_generation_scope = 'ALL_PROFILES' then
2329 hr_utility.set_location(l_proc, 33);
2330 l_bggr_str := ' (sec.business_group_id = :l_business_group_id
2331 or sec.business_group_id is null) and ';
2332 end if;
2333 if l_assignment_type = 'E' then
2334 hr_utility.set_location(l_proc, 34);
2335 l_asgt_str := ' (sec.view_all_employees_flag = :l_Restrict or
2336 (sec.view_all_employees_flag = :l_all and
2337 (sec.view_all_contacts_flag = :l_Restrict or
2338 (sec.view_all_contacts_flag = :l_all and
2339 sec.view_all_candidates_flag = :l_None)))) and ';
2340 end if;
2341 if l_assignment_type = 'C' then
2342 hr_utility.set_location(l_proc, 35);
2343 l_asgt_str := ' (sec.view_all_cwk_flag = :l_Restrict or
2344 (sec.view_all_cwk_flag = :l_all and
2345 (sec.view_all_contacts_flag = :l_Restrict or
2346 (sec.view_all_contacts_flag = :l_all and
2347 sec.view_all_candidates_flag = :l_None)))) and ';
2348 end if;
2349 if l_assignment_type = 'A' then
2350 hr_utility.set_location(l_proc, 36);
2351 l_asgt_str := ' (sec.view_all_applicants_flag = :l_Restrict or
2352 (sec.view_all_applicants_flag = :l_all and
2353 (sec.view_all_contacts_flag = :l_Restrict or
2354 (sec.view_all_contacts_flag = :l_all and
2355 sec.view_all_candidates_flag = :l_None)))) and ';
2356 end if;
2357 l_inst_str := ' select sec.security_profile_id '; --
2358 l_comm_str := ' decode(sec.custom_restriction_flag,:l_Restrict,:l_true,:l_U,:l_FALSE,
2359 null,:l_true,hr_security_internal.evaluate_custom(
2360 :p_assignment_id , sec.restriction_text,
2361 to_date(to_char(:p_effective_date, ''dd/mm/yyyy'')
2362 , ''dd/mm/yyyy''))) = :l_true';
2363
2364
2365 if l_position_id is null then
2366 if l_payroll_id is null then
2367 begin
2368 if g_debug then
2369 hr_utility.set_location(l_proc, 40);
2370 end if;
2371 l_from_str := ' from per_security_profiles sec,
2372 per_organization_list org ';
2373 -- Modified for bug 11737636
2374 l_cond_str := ' org.security_profile_id = sec.security_profile_id
2375 and org.organization_id = :l_organization_id
2376 and sec.view_all_organizations_flag = :l_Restrict
2377 and nvl(sec.top_organization_method, :l_S) <> :l_U
2378 and sec.VIEW_ALL_PAYROLLS_FLAG <> :l_Restrict
2379 and sec.VIEW_ALL_POSITIONS_FLAG <> :l_Restrict
2380 and ';
2381 l_exec_str := l_inst_str||l_from_str||' where '||
2382 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2383
2384 if g_debug then
2385 l_exec_str_print:= l_exec_str;
2386 while length(l_exec_str_print)>0 loop
2387 hr_utility.trace(substr(l_exec_str_print,1,70));
2388 l_exec_str_print:=substr(l_exec_str_print,71);
2389 end loop;
2390 End if;
2391 if p_generation_scope = 'ALL_GLOBAL' then
2392 execute immediate l_exec_str
2393 bulk collect into l_security_profile_table_temp
2394 using
2395 l_Restrict, --l_asgt_str
2396 l_all, --l_asgt_str
2397 l_Restrict, --l_asgt_str
2398 l_all, --l_asgt_str
2399 l_None, --l_asgt_str
2400 l_organization_id, --l_cond_str
2401 l_Restrict, --l_cond_str
2402 l_S, --l_cond_str
2403 l_U, --l_cond_str
2404 l_Restrict, --l_cond_str -- added for bug 11737636
2405 l_Restrict, --l_cond_str -- added for bug 11737636
2406 l_Restrict, --l_comm_str
2407 l_true, --l_comm_str
2408 l_U, --l_comm_str
2409 l_FALSE, --l_comm_str
2410 l_true, --l_comm_str
2411 p_assignment_id, --l_comm_str
2412 p_effective_date, --l_comm_str
2413 l_true; --l_comm_str
2414 else
2415 execute immediate l_exec_str
2416 bulk collect into l_security_profile_table_temp
2417 using
2418 l_business_group_id,--l_bggr_str
2419 l_Restrict, --l_asgt_str
2420 l_all, --l_asgt_str
2421 l_Restrict, --l_asgt_str
2422 l_all, --l_asgt_str
2423 l_None, --l_asgt_str
2424 l_organization_id, --l_cond_str
2425 l_Restrict, --l_cond_str
2426 l_S, --l_cond_str
2427 l_U, --l_cond_str
2428 l_Restrict, --l_cond_str -- added for bug 11737636
2429 l_Restrict, --l_cond_str -- added for bug 11737636
2430 l_Restrict, --l_comm_str
2431 l_true, --l_comm_str
2432 l_U, --l_comm_str
2433 l_FALSE, --l_comm_str
2434 l_true, --l_comm_str
2435 p_assignment_id, --l_comm_str
2436 p_effective_date, --l_comm_str
2437 l_true; --l_comm_str
2438 end if;
2439 profile_add_to_cache;
2440 exception
2441 when no_data_found then
2442 if g_debug then
2443 hr_utility.set_location(l_proc, 50);
2444 end if;
2445 null;
2446 when others then
2447 if g_debug then
2448 hr_utility.set_location(l_proc, 60);
2449 hr_utility.set_location(l_proc||sqlerrm, 60);
2450 end if;
2451 raise;
2452 end;
2453 begin
2454 if g_debug then
2455 hr_utility.set_location(l_proc, 70);
2456 end if;
2457 l_from_str := ' from per_security_profiles sec ';
2458 -- Modified for 11737636
2459 l_cond_str := ' sec.view_all_organizations_flag = :l_all
2460 and ((sec.view_all_positions_flag = :l_Restrict
2461 and nvl(sec.top_position_method, :l_S) <> :l_U)
2462 or sec.view_all_payrolls_flag <> :l_Restrict)
2463 and sec.view_all_flag = :l_Restrict and ';
2464 l_exec_str := l_inst_str||l_from_str||' where '||
2465 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2466 if g_debug then
2467 l_exec_str_print:= l_exec_str;
2468 while length(l_exec_str_print)>0 loop
2469 hr_utility.trace(substr(l_exec_str_print,1,70));
2470 l_exec_str_print:=substr(l_exec_str_print,71);
2471 end loop;
2472 End if;
2473 if p_generation_scope = 'ALL_GLOBAL' then
2474 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2475 using
2476 l_Restrict, --l_asgt_str
2477 l_all, --l_asgt_str
2478 l_Restrict, --l_asgt_str
2479 l_all, --l_asgt_str
2480 l_None, --l_asgt_str
2481 l_all, --l_cond_str
2482 l_Restrict, --l_cond_str
2483 l_S, --l_cond_str
2484 l_U, --l_cond_str
2485 l_Restrict, --l_cond_str
2486 l_Restrict, --l_cond_str
2487 l_Restrict, --l_comm_str
2488 l_true, --l_comm_str
2489 l_U, --l_comm_str
2490 l_FALSE, --l_comm_str
2491 l_true, --l_comm_str
2492 p_assignment_id, --l_comm_str
2493 p_effective_date, --l_comm_str
2494 l_true; --l_comm_str
2495 else
2496 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2497 using
2498 l_business_group_id,--l_bggr_str
2499 l_Restrict, --l_asgt_str
2500 l_all, --l_asgt_str
2501 l_Restrict, --l_asgt_str
2502 l_all, --l_asgt_str
2503 l_None, --l_asgt_str
2504 l_all, --l_cond_str
2505 l_Restrict, --l_cond_str
2506 l_S, --l_cond_str
2507 l_U, --l_cond_str
2508 l_Restrict, --l_cond_str
2509 l_Restrict, --l_cond_str
2510 l_Restrict, --l_comm_str
2511 l_true, --l_comm_str
2512 l_U, --l_comm_str
2513 l_FALSE, --l_comm_str
2514 l_true, --l_comm_str
2515 p_assignment_id, --l_comm_str
2516 p_effective_date, --l_comm_str
2517 l_true; --l_comm_str
2518 end if;
2519 profile_add_to_cache;
2520 exception
2521 when no_data_found then
2522 if g_debug then
2523 hr_utility.set_location(l_proc, 80);
2524 end if;
2525 null;
2526 when others then
2527 if g_debug then
2528 hr_utility.set_location(l_proc||sqlerrm, 90);
2529 hr_utility.set_location(l_proc, 90);
2530 end if;
2531 raise;
2532 end;
2533 begin
2534 if g_debug then
2535 hr_utility.set_location(l_proc, 100);
2536 end if;
2537 l_from_str := ' from per_security_profiles sec ';
2538 l_cond_str := ' sec.view_all_organizations_flag = :l_all
2539 and sec.view_all_positions_flag = :l_all
2540 and sec.view_all_payrolls_flag = :l_all
2541 and sec.custom_restriction_flag = :l_all
2542 and sec.view_all_flag = :l_Restrict and ';
2543 l_exec_str := l_inst_str||l_from_str||' where '||
2544 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2545 if g_debug then
2546 l_exec_str_print:= l_exec_str;
2547 while length(l_exec_str_print)>0 loop
2548 hr_utility.trace(substr(l_exec_str_print,1,70));
2549 l_exec_str_print:=substr(l_exec_str_print,71);
2550 end loop;
2551 End if;
2552 if p_generation_scope = 'ALL_GLOBAL' then
2553 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2554 using
2555 l_Restrict, --l_asgt_str
2556 l_all, --l_asgt_str
2557 l_Restrict, --l_asgt_str
2558 l_all, --l_asgt_str
2559 l_None, --l_asgt_str
2560 l_all, --l_cond_str
2561 l_all, --l_cond_str
2562 l_all, --l_cond_str
2563 l_all, --l_cond_str
2564 l_Restrict, --l_cond_str
2565 l_Restrict, --l_comm_str
2566 l_true, --l_comm_str
2567 l_U, --l_comm_str
2568 l_FALSE, --l_comm_str
2569 l_true, --l_comm_str
2570 p_assignment_id, --l_comm_str
2571 p_effective_date, --l_comm_str
2572 l_true; --l_comm_str
2573 else
2574 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2575 using
2576 l_business_group_id, --l_bggr_str
2577 l_Restrict, --l_asgt_str
2578 l_all, --l_asgt_str
2579 l_Restrict, --l_asgt_str
2580 l_all, --l_asgt_str
2581 l_None, --l_asgt_str
2582 l_all, --l_cond_str
2583 l_all, --l_cond_str
2584 l_all, --l_cond_str
2585 l_all, --l_cond_str
2586 l_Restrict, --l_cond_str
2587 l_Restrict, --l_comm_str
2588 l_true, --l_comm_str
2589 l_U, --l_comm_str
2590 l_FALSE, --l_comm_str
2591 l_true, --l_comm_str
2592 p_assignment_id, --l_comm_str
2593 p_effective_date, --l_comm_str
2594 l_true; --l_comm_str
2595 end if;
2596 profile_add_to_cache;
2597 exception
2598 when no_data_found then
2599 if g_debug then
2600 hr_utility.set_location(l_proc, 110);
2601 end if;
2602 null;
2603 when others then
2604 if g_debug then
2605 hr_utility.set_location(l_proc||sqlerrm, 120);
2606 hr_utility.set_location(l_proc, 120);
2607 end if;
2608 raise;
2609 end;
2610 else -- position is null but payroll is not
2611 begin
2612 if g_debug then
2613 hr_utility.set_location(l_proc, 130);
2614 end if;
2615 l_from_str := ' from per_security_profiles sec,
2616 pay_payroll_list pay,
2617 per_organization_list org ';
2618 l_cond_str := ' org.security_profile_id = sec.security_profile_id
2619 and pay.security_profile_id = sec.security_profile_id
2620 and pay.security_profile_id = org.security_profile_id
2621 and org.organization_id = :l_organization_id
2622 and pay.payroll_id = :l_payroll_id
2623 and sec.view_all_organizations_flag = :l_Restrict
2624 and nvl(sec.top_organization_method, :l_S) <> :l_U
2625 and sec.view_all_payrolls_flag = :l_Restrict and ';
2626 l_exec_str := l_inst_str||l_from_str||' where '||
2627 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2628 if g_debug then
2629 l_exec_str_print:= l_exec_str;
2630 while length(l_exec_str_print)>0 loop
2631 hr_utility.trace(substr(l_exec_str_print,1,70));
2632 l_exec_str_print:=substr(l_exec_str_print,71);
2633 end loop;
2634 End if;
2635 if p_generation_scope = 'ALL_GLOBAL' then
2636 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2637 using
2638 l_Restrict, --l_asgt_str
2639 l_all, --l_asgt_str
2640 l_Restrict, --l_asgt_str
2641 l_all, --l_asgt_str
2642 l_None, --l_asgt_str
2643 l_organization_id, --l_cond_str
2644 l_payroll_id, --l_cond_str
2645 l_Restrict, --l_cond_str
2646 l_S, --l_cond_str
2647 l_U, --l_cond_str
2648 l_Restrict, --l_cond_str
2649 l_Restrict, --l_comm_str
2650 l_true, --l_comm_str
2651 l_U, --l_comm_str
2652 l_FALSE, --l_comm_str
2653 l_true, --l_comm_str
2654 p_assignment_id, --l_comm_str
2655 p_effective_date, --l_comm_str
2656 l_true; --l_comm_str
2657 else
2658 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2659 using
2660 l_business_group_id, --l_bggr_str
2661 l_Restrict, --l_asgt_str
2662 l_all, --l_asgt_str
2663 l_Restrict, --l_asgt_str
2664 l_all, --l_asgt_str
2665 l_None, --l_asgt_str
2666 l_organization_id, --l_cond_str
2667 l_payroll_id, --l_cond_str
2668 l_Restrict, --l_cond_str
2669 l_S, --l_cond_str
2670 l_U, --l_cond_str
2671 l_Restrict, --l_cond_str
2672 l_Restrict, --l_comm_str
2673 l_true, --l_comm_str
2674 l_U, --l_comm_str
2675 l_FALSE, --l_comm_str
2676 l_true, --l_comm_str
2677 p_assignment_id, --l_comm_str
2678 p_effective_date, --l_comm_str
2679 l_true; --l_comm_str
2680 end if;
2681 profile_add_to_cache;
2682 exception
2683 when no_data_found then
2684 if g_debug then
2685 hr_utility.set_location(l_proc, 140);
2686 end if;
2687 null;
2688 when others then
2689 if g_debug then
2690 hr_utility.set_location(l_proc||sqlerrm, 150);
2691 hr_utility.set_location(l_proc, 150);
2692 end if;
2693 raise;
2694 end;
2695 begin
2696 if g_debug then
2697 hr_utility.set_location(l_proc, 160);
2698 end if;
2699 l_from_str := ' from per_security_profiles sec,
2700 per_organization_list org ';
2701 l_cond_str := ' org.security_profile_id = sec.security_profile_id
2702 and org.organization_id = :l_organization_id
2703 and sec.view_all_organizations_flag = :l_Restrict
2704 and nvl(sec.top_organization_method, :l_S) <> :l_U
2705 and sec.view_all_payrolls_flag = :l_all and ';
2706 l_exec_str := l_inst_str||l_from_str||' where '||
2707 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2708 if g_debug then
2709 l_exec_str_print:= l_exec_str;
2710 while length(l_exec_str_print)>0 loop
2711 hr_utility.trace(substr(l_exec_str_print,1,70));
2712 l_exec_str_print:=substr(l_exec_str_print,71);
2713 end loop;
2714 End if;
2715 if p_generation_scope = 'ALL_GLOBAL' then
2716 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2717 using
2718 l_Restrict, --l_asgt_str
2719 l_all, --l_asgt_str
2720 l_Restrict, --l_asgt_str
2721 l_all, --l_asgt_str
2722 l_None, --l_asgt_str
2723 l_organization_id, --l_cond_str
2724 l_Restrict, --l_cond_str
2725 l_S, --l_cond_str
2726 l_U, --l_cond_str
2727 l_all, --l_cond_str
2728 l_Restrict, --l_comm_str
2729 l_true, --l_comm_str
2730 l_U, --l_comm_str
2731 l_FALSE, --l_comm_str
2732 l_true, --l_comm_str
2733 p_assignment_id, --l_comm_str
2734 p_effective_date, --l_comm_str
2735 l_true; --l_comm_str
2736 else
2737 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2738 using
2739 l_business_group_id, --l_bggr_str
2740 l_Restrict, --l_asgt_str
2741 l_all, --l_asgt_str
2742 l_Restrict, --l_asgt_str
2743 l_all, --l_asgt_str
2744 l_None, --l_asgt_str
2745 l_organization_id, --l_cond_str
2746 l_Restrict, --l_cond_str
2747 l_S, --l_cond_str
2748 l_U, --l_cond_str
2749 l_all, --l_cond_str
2750 l_Restrict, --l_comm_str
2751 l_true, --l_comm_str
2752 l_U, --l_comm_str
2753 l_FALSE, --l_comm_str
2754 l_true, --l_comm_str
2755 p_assignment_id, --l_comm_str
2756 p_effective_date, --l_comm_str
2757 l_true; --l_comm_str
2758 end if;
2759 profile_add_to_cache;
2760 exception
2761 when no_data_found then
2762 if g_debug then
2763 hr_utility.set_location(l_proc, 170);
2764 end if;
2765 null;
2766 when others then
2767 if g_debug then
2768 hr_utility.set_location(l_proc||sqlerrm, 180);
2769 hr_utility.set_location(l_proc, 180);
2770 end if;
2771 raise;
2772 end;
2773 begin
2774 if g_debug then
2775 hr_utility.set_location(l_proc, 190);
2776 end if;
2777 l_from_str := ' from per_security_profiles sec,
2778 pay_payroll_list pay ';
2779 l_cond_str := ' pay.security_profile_id = sec.security_profile_id
2780 and pay.payroll_id = :l_payroll_id
2781 and sec.view_all_organizations_flag = :l_all
2782 and sec.view_all_payrolls_flag = :l_Restrict and ';
2783 l_exec_str := l_inst_str||l_from_str||' where '||
2784 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2785 if g_debug then
2786 l_exec_str_print:= l_exec_str;
2787 while length(l_exec_str_print)>0 loop
2788 hr_utility.trace(substr(l_exec_str_print,1,70));
2789 l_exec_str_print:=substr(l_exec_str_print,71);
2790 end loop;
2791 End if;
2792 if p_generation_scope = 'ALL_GLOBAL' then
2793 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2794 using
2795 l_Restrict, --l_asgt_str
2796 l_all, --l_asgt_str
2797 l_Restrict, --l_asgt_str
2798 l_all, --l_asgt_str
2799 l_None, --l_asgt_str
2800 l_payroll_id, --l_cond_str
2801 l_all, --l_cond_str
2802 l_Restrict, --l_cond_str
2803 l_Restrict, --l_comm_str
2804 l_true, --l_comm_str
2805 l_U, --l_comm_str
2806 l_FALSE, --l_comm_str
2807 l_true, --l_comm_str
2808 p_assignment_id, --l_comm_str
2809 p_effective_date, --l_comm_str
2810 l_true; --l_comm_str
2811 else
2812 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2813 using
2814 l_business_group_id, --l_bggr_str
2815 l_Restrict, --l_asgt_str
2816 l_all, --l_asgt_str
2817 l_Restrict, --l_asgt_str
2818 l_all, --l_asgt_str
2819 l_None, --l_asgt_str
2820 l_payroll_id, --l_cond_str
2821 l_all, --l_cond_str
2822 l_Restrict, --l_cond_str
2823 l_Restrict, --l_comm_str
2824 l_true, --l_comm_str
2825 l_U, --l_comm_str
2826 l_FALSE, --l_comm_str
2827 l_true, --l_comm_str
2828 p_assignment_id, --l_comm_str
2829 p_effective_date, --l_comm_str
2830 l_true; --l_comm_str
2831 end if;
2832 profile_add_to_cache;
2833 exception
2834 when no_data_found then
2835 if g_debug then
2836 hr_utility.set_location(l_proc, 200);
2837 end if;
2838 null;
2839 when others then
2840 if g_debug then
2841 hr_utility.set_location(l_proc||sqlerrm, 210);
2842 hr_utility.set_location(l_proc, 210);
2843 end if;
2844 raise;
2845 end;
2846 begin
2847 if g_debug then
2848 hr_utility.set_location(l_proc, 220);
2849 end if;
2850 l_from_str := ' from per_security_profiles sec ';
2851 l_cond_str := ' sec.view_all_organizations_flag = :l_all
2852 and sec.view_all_payrolls_flag = :l_all
2853 and sec.view_all_positions_flag = :l_Restrict
2854 and nvl(sec.top_position_method, :l_S) <> :l_U
2855 and sec.view_all_flag = :l_Restrict and ';
2856 l_exec_str := l_inst_str||l_from_str||' where '||
2857 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2858 if g_debug then
2859 l_exec_str_print:= l_exec_str;
2860 while length(l_exec_str_print)>0 loop
2861 hr_utility.trace(substr(l_exec_str_print,1,70));
2862 l_exec_str_print:=substr(l_exec_str_print,71);
2863 end loop;
2864 End if;
2865 if p_generation_scope = 'ALL_GLOBAL' then
2866 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2867 using
2868 l_Restrict, --l_asgt_str
2869 l_all, --l_asgt_str
2870 l_Restrict, --l_asgt_str
2871 l_all, --l_asgt_str
2872 l_None, --l_asgt_str
2873
2874 l_all, --l_cond_str
2875 l_all, --l_cond_str
2876 l_Restrict, --l_cond_str
2877 l_S, --l_cond_str
2878 l_U, --l_cond_str
2879 l_Restrict, --l_comm_str
2880
2881 l_Restrict, --l_comm_str
2882 l_true, --l_comm_str
2883 l_U, --l_comm_str
2884 l_FALSE, --l_comm_str
2885 l_true, --l_comm_str
2886 p_assignment_id, --l_comm_str
2887 p_effective_date, --l_comm_str
2888 l_true; --l_comm_str
2889 else
2890 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2891 using
2892 l_business_group_id, --l_bggr_str
2893 l_Restrict, --l_asgt_str
2894 l_all, --l_asgt_str
2895 l_Restrict, --l_asgt_str
2896 l_all, --l_asgt_str
2897 l_None, --l_asgt_str
2898
2899 l_all, --l_cond_str
2900 l_all, --l_cond_str
2901 l_Restrict, --l_cond_str
2902 l_S, --l_cond_str
2903 l_U, --l_cond_str
2904 l_Restrict, --l_comm_str
2905
2906 l_Restrict, --l_comm_str
2907 l_true, --l_comm_str
2908 l_U, --l_comm_str
2909 l_FALSE, --l_comm_str
2910 l_true, --l_comm_str
2911 p_assignment_id, --l_comm_str
2912 p_effective_date, --l_comm_str
2913 l_true; --l_comm_str
2914
2915 end if;
2916 profile_add_to_cache;
2917
2918 exception
2919 when no_data_found then
2920 if g_debug then
2921 hr_utility.set_location(l_proc, 230);
2922 end if;
2923 null;
2924 when others then
2925 if g_debug then
2926 hr_utility.set_location(l_proc||sqlerrm, 240);
2927 hr_utility.set_location(l_proc, 240);
2928 end if;
2929 raise;
2930 end;
2931 end if;
2932 begin
2933 if g_debug then
2934 hr_utility.set_location(l_proc, 250);
2935 end if;
2936 l_from_str := ' from per_security_profiles sec ';
2937 l_cond_str := ' sec.view_all_organizations_flag = :l_all
2938 and sec.view_all_positions_flag = :l_all
2939 and sec.view_all_payrolls_flag = :l_all
2940 and sec.custom_restriction_flag = :l_all
2941 and sec.view_all_flag = :l_Restrict and ';
2942 l_exec_str := l_inst_str||l_from_str||' where '||
2943 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2944 if g_debug then
2945 l_exec_str_print:= l_exec_str;
2946 while length(l_exec_str_print)>0 loop
2947 hr_utility.trace(substr(l_exec_str_print,1,70));
2948 l_exec_str_print:=substr(l_exec_str_print,71);
2949 end loop;
2950 End if;
2951 if p_generation_scope = 'ALL_GLOBAL' then
2952 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2953 using
2954 l_Restrict, --l_asgt_str
2955 l_all, --l_asgt_str
2956 l_Restrict, --l_asgt_str
2957 l_all, --l_asgt_str
2958 l_None, --l_asgt_str
2959
2960 l_all, --l_cond_str
2961 l_all, --l_cond_str
2962 l_all, --l_cond_str
2963 l_all, --l_cond_str
2964 l_Restrict, --l_cond_str
2965
2966 l_Restrict, --l_comm_str
2967 l_true, --l_comm_str
2968 l_U, --l_comm_str
2969 l_FALSE, --l_comm_str
2970 l_true, --l_comm_str
2971 p_assignment_id, --l_comm_str
2972 p_effective_date, --l_comm_str
2973 l_true; --l_comm_str
2974
2975 else
2976 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
2977 using
2978 l_business_group_id, --l_bggr_str
2979 l_Restrict, --l_asgt_str
2980 l_all, --l_asgt_str
2981 l_Restrict, --l_asgt_str
2982 l_all, --l_asgt_str
2983 l_None, --l_asgt_str
2984
2985 l_all, --l_cond_str
2986 l_all, --l_cond_str
2987 l_all, --l_cond_str
2988 l_all, --l_cond_str
2989 l_Restrict, --l_cond_str
2990
2991 l_Restrict, --l_comm_str
2992 l_true, --l_comm_str
2993 l_U, --l_comm_str
2994 l_FALSE, --l_comm_str
2995 l_true, --l_comm_str
2996 p_assignment_id, --l_comm_str
2997 p_effective_date, --l_comm_str
2998 l_true; --l_comm_str
2999
3000 end if;
3001 profile_add_to_cache;
3002 exception
3003 when no_data_found then
3004 if g_debug then
3005 hr_utility.set_location(l_proc, 260);
3006 end if;
3007 null;
3008 when others then
3009 if g_debug then
3010 hr_utility.set_location(l_proc||sqlerrm, 270);
3011 hr_utility.set_location(l_proc, 270);
3012 end if;
3013 raise;
3014 end;
3015 else -- position is not null
3016 if l_payroll_id is null then
3017 begin
3018 if g_debug then
3019 hr_utility.set_location(l_proc, 280);
3020 end if;
3021 l_from_str := ' from per_security_profiles sec,
3022 per_position_list posl,
3023 per_organization_list org ';
3024 -- Modified for bug 11737636
3025 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3026 and posl.security_profile_id = sec.security_profile_id
3027 and posl.security_profile_id = org.security_profile_id
3028 and org.organization_id = :l_organization_id
3029 and posl.position_id = :l_position_id
3030 and sec.view_all_organizations_flag = :l_Restrict
3031 and nvl(sec.top_organization_method, :l_S) <> :l_U
3032 and sec.view_all_positions_flag = :l_Restrict
3033 and nvl(sec.top_position_method, :l_S) <> :l_U
3034 and sec.VIEW_ALL_PAYROLLS_FLAG <> :l_Restrict
3035 and ';
3036 l_exec_str := l_inst_str||l_from_str||' where '||
3037 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3038 if g_debug then
3039 l_exec_str_print:= l_exec_str;
3040 while length(l_exec_str_print)>0 loop
3041 hr_utility.trace(substr(l_exec_str_print,1,70));
3042 l_exec_str_print:=substr(l_exec_str_print,71);
3043 end loop;
3044 End if;
3045 if p_generation_scope = 'ALL_GLOBAL' then
3046 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3047 using
3048 l_Restrict, --l_asgt_str
3049 l_all, --l_asgt_str
3050 l_Restrict, --l_asgt_str
3051 l_all, --l_asgt_str
3052 l_None, --l_asgt_str
3053
3054 l_organization_id, --l_cond_str
3055 l_position_id, --l_cond_str
3056 l_Restrict, --l_cond_str
3057 l_S, --l_cond_str
3058 l_U, --l_cond_str
3059 l_Restrict, --l_cond_str
3060 l_S, --l_cond_str
3061 l_U, --l_cond_str
3062 l_Restrict, -- l_cond_str --added for bug 11737636
3063
3064 l_Restrict, --l_comm_str
3065 l_true, --l_comm_str
3066 l_U, --l_comm_str
3067 l_FALSE, --l_comm_str
3068 l_true, --l_comm_str
3069 p_assignment_id, --l_comm_str
3070 p_effective_date, --l_comm_str
3071 l_true; --l_comm_str
3072
3073 else
3074 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3075 using
3076 l_business_group_id, --l_bggr_str
3077 l_Restrict, --l_asgt_str
3078 l_all, --l_asgt_str
3079 l_Restrict, --l_asgt_str
3080 l_all, --l_asgt_str
3081 l_None, --l_asgt_str
3082
3083 l_organization_id, --l_cond_str
3084 l_position_id, --l_cond_str
3085 l_Restrict, --l_cond_str
3086 l_S, --l_cond_str
3087 l_U, --l_cond_str
3088 l_Restrict, --l_cond_str
3089 l_S, --l_cond_str
3090 l_U, --l_cond_str
3091 l_Restrict, -- l_cond_str --added for bug 11737636
3092
3093 l_Restrict, --l_comm_str
3094 l_true, --l_comm_str
3095 l_U, --l_comm_str
3096 l_FALSE, --l_comm_str
3097 l_true, --l_comm_str
3098 p_assignment_id, --l_comm_str
3099 p_effective_date, --l_comm_str
3100 l_true; --l_comm_str
3101
3102 end if;
3103 profile_add_to_cache;
3104 exception
3105 when no_data_found then
3106 if g_debug then
3107 hr_utility.set_location(l_proc, 290);
3108 end if;
3109 null;
3110 when others then
3111 if g_debug then
3112 hr_utility.set_location(l_proc||sqlerrm, 300);
3113 hr_utility.set_location(l_proc, 300);
3114 end if;
3115 raise;
3116 end;
3117 begin
3118 if g_debug then
3119 hr_utility.set_location(l_proc, 310);
3120 end if;
3121 l_from_str := ' from per_security_profiles sec,
3122 per_organization_list org ';
3123 -- Changed for bug 11737636
3124 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3125 and org.organization_id = :l_organization_id
3126 and sec.view_all_organizations_flag = :l_Restrict
3127 and nvl(sec.top_organization_method, :l_S) <> :l_U
3128 and sec.view_all_positions_flag = :l_all
3129 and sec.VIEW_ALL_PAYROLLS_FLAG <> :l_Restrict
3130 and ';
3131 l_exec_str := l_inst_str||l_from_str||' where '||
3132 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3133 if g_debug then
3134 l_exec_str_print:= l_exec_str;
3135 while length(l_exec_str_print)>0 loop
3136 hr_utility.trace(substr(l_exec_str_print,1,70));
3137 l_exec_str_print:=substr(l_exec_str_print,71);
3138 end loop;
3139 End if;
3140 if p_generation_scope = 'ALL_GLOBAL' then
3141 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3142 using
3143 l_Restrict, --l_asgt_str
3144 l_all, --l_asgt_str
3145 l_Restrict, --l_asgt_str
3146 l_all, --l_asgt_str
3147 l_None, --l_asgt_str
3148
3149 l_organization_id, --l_cond_str
3150 l_Restrict, --l_cond_str
3151 l_S, --l_cond_str
3152 l_U, --l_cond_str
3153 l_all, ----l_cond_str
3154 l_Restrict, --l_cond_str -- Added for bug 11737636
3155
3156 l_Restrict, --l_comm_str
3157 l_true, --l_comm_str
3158 l_U, --l_comm_str
3159 l_FALSE, --l_comm_str
3160 l_true, --l_comm_str
3161 p_assignment_id, --l_comm_str
3162 p_effective_date, --l_comm_str
3163 l_true; --l_comm_str
3164
3165 else
3166 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3167 using
3168 l_business_group_id, --l_bggr_str
3169 l_Restrict, --l_asgt_str
3170 l_all, --l_asgt_str
3171 l_Restrict, --l_asgt_str
3172 l_all, --l_asgt_str
3173 l_None, --l_asgt_str
3174
3175 l_organization_id, --l_cond_str
3176 l_Restrict, --l_cond_str
3177 l_S, --l_cond_str
3178 l_U, --l_cond_str
3179 l_all, ----l_cond_str
3180 l_Restrict, --l_cond_str -- Added for bug 11737636
3181
3182 l_Restrict, --l_comm_str
3183 l_true, --l_comm_str
3184 l_U, --l_comm_str
3185 l_FALSE, --l_comm_str
3186 l_true, --l_comm_str
3187 p_assignment_id, --l_comm_str
3188 p_effective_date, --l_comm_str
3189 l_true; --l_comm_str
3190
3191 end if;
3192 profile_add_to_cache;
3193 exception
3194 when no_data_found then
3195 if g_debug then
3196 hr_utility.set_location(l_proc, 320);
3197 end if;
3198 null;
3199 when others then
3200 if g_debug then
3201 hr_utility.set_location(l_proc||sqlerrm, 330);
3202 hr_utility.set_location(l_proc, 330);
3203 end if;
3204 raise;
3205 end;
3206 begin
3207 if g_debug then
3208 hr_utility.set_location(l_proc, 340);
3209 end if;
3210 l_from_str := ' from per_security_profiles sec ';
3211 -- Changed for bug 11737636
3212 l_cond_str := ' sec.view_all_organizations_flag = :l_all
3213 and sec.view_all_positions_flag = :l_all
3214 and sec.view_all_payrolls_flag <> :l_Restrict and ';
3215 l_exec_str := l_inst_str||l_from_str||' where '||
3216 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3217 if g_debug then
3218 l_exec_str_print:= l_exec_str;
3219 while length(l_exec_str_print)>0 loop
3220 hr_utility.trace(substr(l_exec_str_print,1,70));
3221 l_exec_str_print:=substr(l_exec_str_print,71);
3222 end loop;
3223 End if;
3224 if p_generation_scope = 'ALL_GLOBAL' then
3225 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3226 using
3227 l_Restrict, --l_asgt_str
3228 l_all, --l_asgt_str
3229 l_Restrict, --l_asgt_str
3230 l_all, --l_asgt_str
3231 l_None, --l_asgt_str
3232
3233 l_all, ----l_cond_str
3234 l_all, ----l_cond_str
3235 l_Restrict, --l_cond_str
3236
3237 l_Restrict, --l_comm_str
3238 l_true, --l_comm_str
3239 l_U, --l_comm_str
3240 l_FALSE, --l_comm_str
3241 l_true, --l_comm_str
3242 p_assignment_id, --l_comm_str
3243 p_effective_date, --l_comm_str
3244 l_true; --l_comm_str
3245
3246 else
3247 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3248 using
3249 l_business_group_id, --l_bggr_str
3250 l_Restrict, --l_asgt_str
3251 l_all, --l_asgt_str
3252 l_Restrict, --l_asgt_str
3253 l_all, --l_asgt_str
3254 l_None, --l_asgt_str
3255
3256 l_all, ----l_cond_str
3257 l_all, ----l_cond_str
3258 l_Restrict, --l_cond_str
3259
3260 l_Restrict, --l_comm_str
3261 l_true, --l_comm_str
3262 l_U, --l_comm_str
3263 l_FALSE, --l_comm_str
3264 l_true, --l_comm_str
3265 p_assignment_id, --l_comm_str
3266 p_effective_date, --l_comm_str
3267 l_true; --l_comm_str
3268
3269 end if;
3270 profile_add_to_cache;
3271 exception
3272 when no_data_found then
3273 if g_debug then
3274 hr_utility.set_location(l_proc, 350);
3275 end if;
3276 null;
3277 when others then
3278 if g_debug then
3279 hr_utility.set_location(l_proc||sqlerrm, 360);
3280 hr_utility.set_location(l_proc, 360);
3281 end if;
3282 raise;
3283 end;
3284 begin
3285 if g_debug then
3286 hr_utility.set_location(l_proc, 370);
3287 end if;
3288 l_from_str := ' from per_security_profiles sec,
3289 per_position_list posl ';
3290 --Modified for bug 11737636
3291 l_cond_str := ' posl.security_profile_id = sec.security_profile_id
3292 and posl.position_id = :l_position_id
3293 and sec.view_all_organizations_flag = :l_all
3294 and sec.view_all_positions_flag = :l_Restrict
3295 and nvl(sec.top_position_method, :l_S) <> :l_U
3296 and sec.view_all_flag = :l_Restrict
3297 and sec.business_group_id = :l_business_group_id
3298 and sec.VIEW_ALL_PAYROLLS_FLAG <> :l_Restrict
3299 and ';
3300 l_exec_str := l_inst_str||l_from_str||' where '||
3301 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3302 if g_debug then
3303 l_exec_str_print:= l_exec_str;
3304 while length(l_exec_str_print)>0 loop
3305 hr_utility.trace(substr(l_exec_str_print,1,70));
3306 l_exec_str_print:=substr(l_exec_str_print,71);
3307 end loop;
3308 End if;
3309 if p_generation_scope = 'ALL_GLOBAL' then
3310 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3311 using
3312 l_Restrict, --l_asgt_str
3313 l_all, --l_asgt_str
3314 l_Restrict, --l_asgt_str
3315 l_all, --l_asgt_str
3316 l_None, --l_asgt_str
3317
3318 l_position_id, ----l_cond_str
3319 l_all, ----l_cond_str
3320 l_Restrict, --l_cond_str
3321 l_S, --l_cond_str
3322 l_U, --l_cond_str
3323 l_Restrict, --l_cond_str
3324 l_business_group_id, --l_cond_str
3325 l_Restrict, --l_cond_str -- Added for bug 11737636
3326
3327 l_Restrict, --l_comm_str
3328 l_true, --l_comm_str
3329 l_U, --l_comm_str
3330 l_FALSE, --l_comm_str
3331 l_true, --l_comm_str
3332 p_assignment_id, --l_comm_str
3333 p_effective_date, --l_comm_str
3334 l_true; --l_comm_str
3335
3336 else
3337 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3338 using
3339 l_business_group_id, --l_bggr_str
3340 l_Restrict, --l_asgt_str
3341 l_all, --l_asgt_str
3342 l_Restrict, --l_asgt_str
3343 l_all, --l_asgt_str
3344 l_None, --l_asgt_str
3345
3346 l_position_id, ----l_cond_str
3347 l_all, ----l_cond_str
3348 l_Restrict, --l_cond_str
3349 l_S, --l_cond_str
3350 l_U, --l_cond_str
3351 l_Restrict, --l_cond_str
3352 l_business_group_id, --l_cond_str
3353 l_Restrict, --l_cond_str -- Added for bug 11737636
3354 l_Restrict, --l_comm_str
3355 l_true, --l_comm_str
3356 l_U, --l_comm_str
3357 l_FALSE, --l_comm_str
3358 l_true, --l_comm_str
3359 p_assignment_id, --l_comm_str
3360 p_effective_date, --l_comm_str
3361 l_true; --l_comm_str
3362
3363 end if;
3364 profile_add_to_cache;
3365 exception
3366 when no_data_found then
3367 if g_debug then
3368 hr_utility.set_location(l_proc, 380);
3369 end if;
3370 null;
3371 when others then
3372 if g_debug then
3373 hr_utility.set_location(l_proc||sqlerrm, 390);
3374 hr_utility.set_location(l_proc, 390);
3375 end if;
3376 raise;
3377 end;
3378 begin
3379 if g_debug then
3380 hr_utility.set_location(l_proc, 400);
3381 end if;
3382 l_from_str := ' from per_security_profiles sec ';
3383 l_cond_str := ' sec.view_all_organizations_flag = :l_all
3384 and sec.view_all_positions_flag = :l_all
3385 and sec.view_all_payrolls_flag = :l_all
3386 and sec.custom_restriction_flag = :l_all
3387 and sec.view_all_flag = :l_Restrict and ';
3388 l_exec_str := l_inst_str||l_from_str||' where '||
3389 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3390 if g_debug then
3391 l_exec_str_print:= l_exec_str;
3392 while length(l_exec_str_print)>0 loop
3393 hr_utility.trace(substr(l_exec_str_print,1,70));
3394 l_exec_str_print:=substr(l_exec_str_print,71);
3395 end loop;
3396 End if;
3397 if p_generation_scope = 'ALL_GLOBAL' then
3398 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3399 using
3400 l_Restrict, --l_asgt_str
3401 l_all, --l_asgt_str
3402 l_Restrict, --l_asgt_str
3403 l_all, --l_asgt_str
3404 l_None, --l_asgt_str
3405
3406 l_all, ----l_cond_str
3407 l_all, ----l_cond_str
3408 l_all, ----l_cond_str
3409 l_all, ----l_cond_str
3410 l_Restrict, --l_cond_str
3411
3412 l_Restrict, --l_comm_str
3413 l_true, --l_comm_str
3414 l_U, --l_comm_str
3415 l_FALSE, --l_comm_str
3416 l_true, --l_comm_str
3417 p_assignment_id, --l_comm_str
3418 p_effective_date, --l_comm_str
3419 l_true; --l_comm_str
3420
3421 else
3422 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3423 using
3424 l_business_group_id, --l_bggr_str
3425 l_Restrict, --l_asgt_str
3426 l_all, --l_asgt_str
3427 l_Restrict, --l_asgt_str
3428 l_all, --l_asgt_str
3429 l_None, --l_asgt_str
3430
3431 l_all, ----l_cond_str
3432 l_all, ----l_cond_str
3433 l_all, ----l_cond_str
3434 l_all, ----l_cond_str
3435 l_Restrict, --l_cond_str
3436
3437 l_Restrict, --l_comm_str
3438 l_true, --l_comm_str
3439 l_U, --l_comm_str
3440 l_FALSE, --l_comm_str
3441 l_true, --l_comm_str
3442 p_assignment_id, --l_comm_str
3443 p_effective_date, --l_comm_str
3444 l_true; --l_comm_str
3445
3446 end if;
3447 profile_add_to_cache;
3448 exception
3449 when no_data_found then
3450 if g_debug then
3451 hr_utility.set_location(l_proc, 410);
3452 end if;
3453 null;
3454 when others then
3455 if g_debug then
3456 hr_utility.set_location(l_proc||sqlerrm, 420);
3457 hr_utility.set_location(l_proc, 420);
3458 end if;
3459 raise;
3460 end;
3461 else -- position and payroll are not null
3462 begin
3463 if g_debug then
3464 hr_utility.set_location(l_proc, 430);
3465 end if;
3466 l_from_str := ' from per_security_profiles sec,
3467 pay_payroll_list pay,
3468 per_position_list posl,
3469 per_organization_list org ';
3470 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3471 and posl.security_profile_id = sec.security_profile_id
3472 and pay.security_profile_id = sec.security_profile_id
3473 and pay.security_profile_id = posl.security_profile_id
3474 and pay.security_profile_id = org.security_profile_id
3475 and org.security_profile_id = posl.security_profile_id
3476 and org.organization_id = :l_organization_id
3477 and posl.position_id = :l_position_id
3478 and pay.payroll_id = :l_payroll_id
3479 and sec.view_all_organizations_flag = :l_Restrict
3480 and nvl(sec.top_organization_method, :l_S) <> :l_U
3481 and sec.view_all_positions_flag = :l_Restrict
3482 and nvl(sec.top_position_method, :l_S) <> :l_U
3483 and sec.view_all_payrolls_flag = :l_Restrict and ';
3484 l_exec_str := l_inst_str||l_from_str||' where '||
3485 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3486 if g_debug then
3487 l_exec_str_print:= l_exec_str;
3488 while length(l_exec_str_print)>0 loop
3489 hr_utility.trace(substr(l_exec_str_print,1,70));
3490 l_exec_str_print:=substr(l_exec_str_print,71);
3491 end loop;
3492 End if;
3493 if p_generation_scope = 'ALL_GLOBAL' then
3494 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3495 using
3496 l_Restrict, --l_asgt_str
3497 l_all, --l_asgt_str
3498 l_Restrict, --l_asgt_str
3499 l_all, --l_asgt_str
3500 l_None, --l_asgt_str
3501
3502 l_organization_id, ----l_cond_str
3503 l_position_id, ----l_cond_str
3504 l_payroll_id, ----l_cond_str
3505 l_Restrict, ----l_cond_str
3506 l_S, --l_cond_str
3507 l_U, --l_cond_str
3508 l_Restrict,
3509 l_S, --l_cond_str
3510 l_U, --l_cond_str
3511 l_Restrict, --l_cond_str
3512
3513 l_Restrict, --l_comm_str
3514 l_true, --l_comm_str
3515 l_U, --l_comm_str
3516 l_FALSE, --l_comm_str
3517 l_true, --l_comm_str
3518 p_assignment_id, --l_comm_str
3519 p_effective_date, --l_comm_str
3520 l_true; --l_comm_str
3521
3522 else
3523 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3524 using
3525 l_business_group_id, --l_bggr_str
3526 l_Restrict, --l_asgt_str
3527 l_all, --l_asgt_str
3528 l_Restrict, --l_asgt_str
3529 l_all, --l_asgt_str
3530 l_None, --l_asgt_str
3531
3532 l_organization_id, ----l_cond_str
3533 l_position_id, ----l_cond_str
3534 l_payroll_id, ----l_cond_str
3535 l_Restrict, ----l_cond_str
3536 l_S, --l_cond_str
3537 l_U, --l_cond_str
3538 l_Restrict,
3539 l_S, --l_cond_str
3540 l_U, --l_cond_str
3541 l_Restrict, --l_cond_str
3542
3543 l_Restrict, --l_comm_str
3544 l_true, --l_comm_str
3545 l_U, --l_comm_str
3546 l_FALSE, --l_comm_str
3547 l_true, --l_comm_str
3548 p_assignment_id, --l_comm_str
3549 p_effective_date, --l_comm_str
3550 l_true; --l_comm_str
3551
3552 end if;
3553 profile_add_to_cache;
3554 exception
3555 when no_data_found then
3556 if g_debug then
3557 hr_utility.set_location(l_proc, 440);
3558 end if;
3559 null;
3560 when others then
3561 if g_debug then
3562 hr_utility.set_location(l_proc||sqlerrm, 450);
3563 hr_utility.set_location(l_proc, 450);
3564 end if;
3565 raise;
3566 end;
3567 begin
3568 if g_debug then
3569 hr_utility.set_location(l_proc, 460);
3570 end if;
3571 l_from_str := ' from per_security_profiles sec,
3572 per_position_list posl,
3573 per_organization_list org ';
3574 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3575 and posl.security_profile_id = sec.security_profile_id
3576 and posl.security_profile_id = org.security_profile_id
3577 and org.organization_id = :l_organization_id
3578 and posl.position_id = :l_position_id
3579 and sec.view_all_organizations_flag = :l_Restrict
3580 and nvl(sec.top_organization_method, :l_S) <> :l_U
3581 and sec.view_all_positions_flag = :l_Restrict
3582 and nvl(sec.top_position_method, :l_S) <> :l_U
3583 and sec.view_all_payrolls_flag = :l_all and ';
3584 l_exec_str := l_inst_str||l_from_str||' where '||
3585 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3586 if g_debug then
3587 l_exec_str_print:= l_exec_str;
3588 while length(l_exec_str_print)>0 loop
3589 hr_utility.trace(substr(l_exec_str_print,1,70));
3590 l_exec_str_print:=substr(l_exec_str_print,71);
3591 end loop;
3592 End if;
3593 if p_generation_scope = 'ALL_GLOBAL' then
3594 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3595 using
3596 l_Restrict, --l_asgt_str
3597 l_all, --l_asgt_str
3598 l_Restrict, --l_asgt_str
3599 l_all, --l_asgt_str
3600 l_None, --l_asgt_str
3601
3602 l_organization_id, ----l_cond_str
3603 l_position_id, ----l_cond_str
3604 l_Restrict, ----l_cond_str
3605 l_S, --l_cond_str
3606 l_U, --l_cond_str
3607 l_Restrict,
3608 l_S, --l_cond_str
3609 l_U, --l_cond_str
3610 l_all, --l_cond_str
3611
3612 l_Restrict, --l_comm_str
3613 l_true, --l_comm_str
3614 l_U, --l_comm_str
3615 l_FALSE, --l_comm_str
3616 l_true, --l_comm_str
3617 p_assignment_id, --l_comm_str
3618 p_effective_date, --l_comm_str
3619 l_true; --l_comm_str
3620
3621 else
3622 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3623 using
3624 l_business_group_id, --l_bggr_str
3625 l_Restrict, --l_asgt_str
3626 l_all, --l_asgt_str
3627 l_Restrict, --l_asgt_str
3628 l_all, --l_asgt_str
3629 l_None, --l_asgt_str
3630
3631 l_organization_id, ----l_cond_str
3632 l_position_id, ----l_cond_str
3633 l_Restrict, ----l_cond_str
3634 l_S, --l_cond_str
3635 l_U, --l_cond_str
3636 l_Restrict,
3637 l_S, --l_cond_str
3638 l_U, --l_cond_str
3639 l_all, --l_cond_str
3640
3641 l_Restrict, --l_comm_str
3642 l_true, --l_comm_str
3643 l_U, --l_comm_str
3644 l_FALSE, --l_comm_str
3645 l_true, --l_comm_str
3646 p_assignment_id, --l_comm_str
3647 p_effective_date, --l_comm_str
3648 l_true; --l_comm_str
3649
3650 end if;
3651 profile_add_to_cache;
3652 exception
3653 when no_data_found then
3654 if g_debug then
3655 hr_utility.set_location(l_proc, 470);
3656 end if;
3657 null;
3658 when others then
3659 if g_debug then
3660 hr_utility.set_location(l_proc||sqlerrm, 480);
3661 hr_utility.set_location(l_proc, 480);
3662 end if;
3663 raise;
3664 end;
3665 begin
3666 if g_debug then
3667 hr_utility.set_location(l_proc, 490);
3668 end if;
3669 l_from_str := ' from per_security_profiles sec,
3670 pay_payroll_list pay,
3671 per_organization_list org ';
3672 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3673 and pay.security_profile_id = sec.security_profile_id
3674 and org.organization_id = :l_organization_id
3675 and pay.payroll_id = :l_payroll_id
3676 and sec.view_all_organizations_flag = :l_Restrict
3677 and nvl(sec.top_organization_method, :l_S) <> :l_U
3678 and sec.view_all_positions_flag = :l_all
3679 and sec.view_all_payrolls_flag = :l_Restrict and ';
3680 l_exec_str := l_inst_str||l_from_str||' where '||
3681 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3682 if g_debug then
3683 l_exec_str_print:= l_exec_str;
3684 while length(l_exec_str_print)>0 loop
3685 hr_utility.trace(substr(l_exec_str_print,1,70));
3686 l_exec_str_print:=substr(l_exec_str_print,71);
3687 end loop;
3688 End if;
3689 if p_generation_scope = 'ALL_GLOBAL' then
3690 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3691 using
3692 l_Restrict, --l_asgt_str
3693 l_all, --l_asgt_str
3694 l_Restrict, --l_asgt_str
3695 l_all, --l_asgt_str
3696 l_None, --l_asgt_str
3697
3698 l_organization_id, ----l_cond_str
3699 l_payroll_id, ----l_cond_str
3700 l_Restrict, ----l_cond_str
3701 l_S, --l_cond_str
3702 l_U, --l_cond_str
3703 l_all,
3704 l_Restrict, --l_cond_str
3705
3706 l_Restrict, --l_comm_str
3707 l_true, --l_comm_str
3708 l_U, --l_comm_str
3709 l_FALSE, --l_comm_str
3710 l_true, --l_comm_str
3711 p_assignment_id, --l_comm_str
3712 p_effective_date, --l_comm_str
3713 l_true; --l_comm_str
3714
3715 else
3716 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3717 using
3718 l_business_group_id, --l_bggr_str
3719 l_Restrict, --l_asgt_str
3720 l_all, --l_asgt_str
3721 l_Restrict, --l_asgt_str
3722 l_all, --l_asgt_str
3723 l_None, --l_asgt_str
3724
3725 l_organization_id, ----l_cond_str
3726 l_payroll_id, ----l_cond_str
3727 l_Restrict, ----l_cond_str
3728 l_S, --l_cond_str
3729 l_U, --l_cond_str
3730 l_all,
3731 l_Restrict, --l_cond_str
3732
3733 l_Restrict, --l_comm_str
3734 l_true, --l_comm_str
3735 l_U, --l_comm_str
3736 l_FALSE, --l_comm_str
3737 l_true, --l_comm_str
3738 p_assignment_id, --l_comm_str
3739 p_effective_date, --l_comm_str
3740 l_true; --l_comm_str
3741
3742 end if;
3743 profile_add_to_cache;
3744 exception
3745 when no_data_found then
3746 if g_debug then
3747 hr_utility.set_location(l_proc, 500);
3748 end if;
3749 null;
3750 when others then
3751 if g_debug then
3752 hr_utility.set_location(l_proc||sqlerrm, 510);
3753 hr_utility.set_location(l_proc, 510);
3754 end if;
3755 raise;
3756 end;
3757 begin
3758 if g_debug then
3759 hr_utility.set_location(l_proc, 520);
3760 end if;
3761 l_from_str := ' from per_security_profiles sec,
3762 pay_payroll_list pay,
3763 per_position_list posl ';
3764 l_cond_str := ' posl.security_profile_id = sec.security_profile_id
3765 and pay.security_profile_id = sec.security_profile_id
3766 and pay.security_profile_id = posl.security_profile_id
3767 and posl.position_id = :l_position_id
3768 and pay.payroll_id = :l_payroll_id
3769 and sec.view_all_organizations_flag = :l_all
3770 and sec.view_all_positions_flag = :l_Restrict
3771 and nvl(sec.top_position_method, :l_S) <> :l_U
3772 and sec.view_all_payrolls_flag = :l_Restrict and ';
3773 l_exec_str := l_inst_str||l_from_str||' where '||
3774 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3775 if g_debug then
3776 l_exec_str_print:= l_exec_str;
3777 while length(l_exec_str_print)>0 loop
3778 hr_utility.trace(substr(l_exec_str_print,1,70));
3779 l_exec_str_print:=substr(l_exec_str_print,71);
3780 end loop;
3781 End if;
3782 if p_generation_scope = 'ALL_GLOBAL' then
3783 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3784 using
3785 l_Restrict, --l_asgt_str
3786 l_all, --l_asgt_str
3787 l_Restrict, --l_asgt_str
3788 l_all, --l_asgt_str
3789 l_None, --l_asgt_str
3790
3791 l_position_id, ----l_cond_str
3792 l_payroll_id, ----l_cond_str
3793 l_all,
3794 l_Restrict, ----l_cond_str
3795 l_S, --l_cond_str
3796 l_U, --l_cond_str
3797 l_Restrict, --l_cond_str
3798
3799 l_Restrict, --l_comm_str
3800 l_true, --l_comm_str
3801 l_U, --l_comm_str
3802 l_FALSE, --l_comm_str
3803 l_true, --l_comm_str
3804 p_assignment_id, --l_comm_str
3805 p_effective_date, --l_comm_str
3806 l_true; --l_comm_str
3807
3808 else
3809 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3810 using
3811 l_business_group_id, --l_bggr_str
3812 l_Restrict, --l_asgt_str
3813 l_all, --l_asgt_str
3814 l_Restrict, --l_asgt_str
3815 l_all, --l_asgt_str
3816 l_None, --l_asgt_str
3817
3818 l_position_id, ----l_cond_str
3819 l_payroll_id, ----l_cond_str
3820 l_all,
3821 l_Restrict, ----l_cond_str
3822 l_S, --l_cond_str
3823 l_U, --l_cond_str
3824 l_Restrict, --l_cond_str
3825
3826 l_Restrict, --l_comm_str
3827 l_true, --l_comm_str
3828 l_U, --l_comm_str
3829 l_FALSE, --l_comm_str
3830 l_true, --l_comm_str
3831 p_assignment_id, --l_comm_str
3832 p_effective_date, --l_comm_str
3833 l_true; --l_comm_str
3834
3835 end if;
3836 profile_add_to_cache;
3837 exception
3838 when no_data_found then
3839 if g_debug then
3840 hr_utility.set_location(l_proc, 530);
3841 end if;
3842 null;
3843 when others then
3844 if g_debug then
3845 hr_utility.set_location(l_proc||sqlerrm, 540);
3846 hr_utility.set_location(l_proc, 540);
3847 end if;
3848 raise;
3849 end;
3850 begin
3851 if g_debug then
3852 hr_utility.set_location(l_proc, 550);
3853 end if;
3854 l_from_str := ' from per_security_profiles sec,
3855 per_organization_list org ';
3856 l_cond_str := ' org.security_profile_id = sec.security_profile_id
3857 and org.organization_id = :l_organization_id
3858 and sec.view_all_organizations_flag = :l_Restrict
3859 and nvl(sec.top_organization_method, :l_S) <> :l_U
3860 and sec.view_all_positions_flag = :l_all
3861 and sec.view_all_payrolls_flag = :l_all and ';
3862 l_exec_str := l_inst_str||l_from_str||' where '||
3863 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3864 if g_debug then
3865 l_exec_str_print:= l_exec_str;
3866 while length(l_exec_str_print)>0 loop
3867 hr_utility.trace(substr(l_exec_str_print,1,70));
3868 l_exec_str_print:=substr(l_exec_str_print,71);
3869 end loop;
3870 End if;
3871 if p_generation_scope = 'ALL_GLOBAL' then
3872 execute immediate l_exec_str bulk collect into l_security_profile_table_temp -- 6368698
3873 using
3874 l_Restrict, --l_asgt_str
3875 l_all, --l_asgt_str
3876 l_Restrict, --l_asgt_str
3877 l_all, --l_asgt_str
3878 l_None, --l_asgt_str
3879
3880 l_organization_id, ----l_cond_str
3881 l_Restrict, ----l_cond_str
3882 l_S, --l_cond_str
3883 l_U, --l_cond_str
3884 l_all, --l_cond_str
3885 l_all, --l_cond_str
3886
3887
3888 l_Restrict, --l_comm_str
3889 l_true, --l_comm_str
3890 l_U, --l_comm_str
3891 l_FALSE, --l_comm_str
3892 l_true, --l_comm_str
3893 p_assignment_id, --l_comm_str
3894 p_effective_date, --l_comm_str
3895 l_true; --l_comm_str
3896
3897 else
3898 execute immediate l_exec_str bulk collect into l_security_profile_table_temp -- 6368698
3899 using
3900 l_business_group_id, --l_bggr_str
3901 l_Restrict, --l_asgt_str
3902 l_all, --l_asgt_str
3903 l_Restrict, --l_asgt_str
3904 l_all, --l_asgt_str
3905 l_None, --l_asgt_str
3906
3907 l_organization_id, ----l_cond_str
3908 l_Restrict, ----l_cond_str
3909 l_S, --l_cond_str
3910 l_U, --l_cond_str
3911 l_all, --l_cond_str
3912 l_all, --l_cond_str
3913
3914 l_Restrict, --l_comm_str
3915 l_true, --l_comm_str
3916 l_U, --l_comm_str
3917 l_FALSE, --l_comm_str
3918 l_true, --l_comm_str
3919 p_assignment_id, --l_comm_str
3920 p_effective_date, --l_comm_str
3921 l_true; --l_comm_str
3922
3923 end if;
3924 profile_add_to_cache;
3925 exception
3926 when no_data_found then
3927 if g_debug then
3928 hr_utility.set_location(l_proc, 560);
3929 end if;
3930 null;
3931 when others then
3932 if g_debug then
3933 hr_utility.set_location(l_proc||sqlerrm, 570);
3934 hr_utility.set_location(l_proc, 570);
3935 end if;
3936 raise;
3937 end;
3938 begin
3939 if g_debug then
3940 hr_utility.set_location(l_proc, 580);
3941 end if;
3942 l_from_str := ' from per_security_profiles sec,
3943 per_position_list posl ';
3944 l_cond_str := ' posl.security_profile_id = sec.security_profile_id
3945 and posl.position_id = :l_position_id
3946 and sec.view_all_organizations_flag = :l_all
3947 and sec.view_all_positions_flag = :l_Restrict
3948 and nvl(sec.top_position_method, :l_S) <> :l_U
3949 and sec.view_all_payrolls_flag = :l_all and ';
3950 l_exec_str := l_inst_str||l_from_str||' where '||
3951 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3952 if g_debug then
3953 l_exec_str_print:= l_exec_str;
3954 while length(l_exec_str_print)>0 loop
3955 hr_utility.trace(substr(l_exec_str_print,1,70));
3956 l_exec_str_print:=substr(l_exec_str_print,71);
3957 end loop;
3958 End if;
3959 if p_generation_scope = 'ALL_GLOBAL' then
3960 execute immediate l_exec_str bulk collect into l_security_profile_table_temp -- 6368698
3961 using
3962 l_Restrict, --l_asgt_str
3963 l_all, --l_asgt_str
3964 l_Restrict, --l_asgt_str
3965 l_all, --l_asgt_str
3966 l_None, --l_asgt_str
3967
3968 l_position_id, ----l_cond_str
3969 l_all, --l_cond_str
3970 l_Restrict, ----l_cond_str
3971 l_S, --l_cond_str
3972 l_U, --l_cond_str
3973 l_all, --l_cond_str
3974
3975 l_Restrict, --l_comm_str
3976 l_true, --l_comm_str
3977 l_U, --l_comm_str
3978 l_FALSE, --l_comm_str
3979 l_true, --l_comm_str
3980 p_assignment_id, --l_comm_str
3981 p_effective_date, --l_comm_str
3982 l_true; --l_comm_str
3983
3984 else
3985 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
3986 using
3987 l_business_group_id, --l_bggr_str
3988 l_Restrict, --l_asgt_str
3989 l_all, --l_asgt_str
3990 l_Restrict, --l_asgt_str
3991 l_all, --l_asgt_str
3992 l_None, --l_asgt_str
3993
3994 l_position_id, ----l_cond_str
3995 l_all, --l_cond_str
3996 l_Restrict, ----l_cond_str
3997 l_S, --l_cond_str
3998 l_U, --l_cond_str
3999 l_all, --l_cond_str
4000
4001 l_Restrict, --l_comm_str
4002 l_true, --l_comm_str
4003 l_U, --l_comm_str
4004 l_FALSE, --l_comm_str
4005 l_true, --l_comm_str
4006 p_assignment_id, --l_comm_str
4007 p_effective_date, --l_comm_str
4008 l_true; --l_comm_str
4009
4010 end if;
4011 profile_add_to_cache;
4012 exception
4013 when no_data_found then
4014 if g_debug then
4015 hr_utility.set_location(l_proc, 590);
4016 end if;
4017 null;
4018 when others then
4019 if g_debug then
4020 hr_utility.set_location(l_proc||sqlerrm, 600);
4021 hr_utility.set_location(l_proc, 600);
4022 end if;
4023 raise;
4024 end;
4025 begin
4026 if g_debug then
4027 hr_utility.set_location(l_proc, 610);
4028 end if;
4029 l_from_str := ' from per_security_profiles sec,
4030 pay_payroll_list pay ';
4031 l_cond_str := ' pay.security_profile_id = sec.security_profile_id
4032 and pay.payroll_id = :l_payroll_id
4033 and sec.view_all_organizations_flag = :l_all
4034 and sec.view_all_positions_flag = :l_all
4035 and sec.view_all_payrolls_flag = :l_Restrict and ';
4036 l_exec_str := l_inst_str||l_from_str||' where '||
4037 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
4038 if g_debug then
4039 l_exec_str_print:= l_exec_str;
4040 while length(l_exec_str_print)>0 loop
4041 hr_utility.trace(substr(l_exec_str_print,1,70));
4042 l_exec_str_print:=substr(l_exec_str_print,71);
4043 end loop;
4044 End if;
4045 if p_generation_scope = 'ALL_GLOBAL' then
4046 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4047 using
4048 l_Restrict, --l_asgt_str
4049 l_all, --l_asgt_str
4050 l_Restrict, --l_asgt_str
4051 l_all, --l_asgt_str
4052 l_None, --l_asgt_str
4053
4054 l_payroll_id, ----l_cond_str
4055 l_all, --l_cond_str
4056 l_all, --l_cond_str
4057 l_Restrict, ----l_cond_str
4058
4059 l_Restrict, --l_comm_str
4060 l_true, --l_comm_str
4061 l_U, --l_comm_str
4062 l_FALSE, --l_comm_str
4063 l_true, --l_comm_str
4064 p_assignment_id, --l_comm_str
4065 p_effective_date, --l_comm_str
4066 l_true; --l_comm_str
4067
4068 else
4069 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4070 using
4071 l_business_group_id, --l_bggr_str
4072 l_Restrict, --l_asgt_str
4073 l_all, --l_asgt_str
4074 l_Restrict, --l_asgt_str
4075 l_all, --l_asgt_str
4076 l_None, --l_asgt_str
4077
4078 l_payroll_id, ----l_cond_str
4079 l_all, --l_cond_str
4080 l_all, --l_cond_str
4081 l_Restrict, ----l_cond_str
4082
4083 l_Restrict, --l_comm_str
4084 l_true, --l_comm_str
4085 l_U, --l_comm_str
4086 l_FALSE, --l_comm_str
4087 l_true, --l_comm_str
4088 p_assignment_id, --l_comm_str
4089 p_effective_date, --l_comm_str
4090 l_true; --l_comm_str
4091
4092 end if;
4093 profile_add_to_cache;
4094 exception
4095 when no_data_found then
4096 if g_debug then
4097 hr_utility.set_location(l_proc, 620);
4098 end if;
4099 null;
4100 when others then
4101 if g_debug then
4102 hr_utility.set_location(l_proc||sqlerrm, 630);
4103 hr_utility.set_location(l_proc, 630);
4104 end if;
4105 raise;
4106 end;
4107 begin
4108 if g_debug then
4109 hr_utility.set_location(l_proc, 640);
4110 end if;
4111 l_from_str := ' from per_security_profiles sec ';
4112 l_cond_str := ' sec.view_all_organizations_flag = :l_all
4113 and sec.view_all_positions_flag = :l_all
4114 and sec.view_all_payrolls_flag = :l_all
4115 and sec.custom_restriction_flag = :l_all
4116 and sec.view_all_flag = :l_Restrict and ';
4117 l_exec_str := l_inst_str||l_from_str||' where '||
4118 l_bggr_str||l_asgt_str||l_cond_str||
4119 ' hr_security_internal.evaluate_custom(
4120 :p_assignment_id,sec.restriction_text,
4121 to_date(to_char(:p_effective_date, ''dd/mm/yyyy'')
4122 , ''dd/mm/yyyy'')) = :l_true';
4123
4124 if g_debug then
4125 l_exec_str_print:= l_exec_str;
4126 while length(l_exec_str_print)>0 loop
4127 hr_utility.trace(substr(l_exec_str_print,1,70));
4128 l_exec_str_print:=substr(l_exec_str_print,71);
4129 end loop;
4130 End if;
4131 if p_generation_scope = 'ALL_GLOBAL' then
4132 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4133 using
4134 l_Restrict, --l_asgt_str
4135 l_all, --l_asgt_str
4136 l_Restrict, --l_asgt_str
4137 l_all, --l_asgt_str
4138 l_None, --l_asgt_str
4139
4140 l_all, --l_cond_str
4141 l_all, --l_cond_str
4142 l_all, --l_cond_str
4143 l_all, --l_cond_str
4144 l_Restrict, ----l_cond_str
4145 p_assignment_id,
4146 p_effective_date,
4147 l_true;
4148 else
4149 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4150 using
4151 l_business_group_id, --l_bggr_str
4152 l_Restrict, --l_asgt_str
4153 l_all, --l_asgt_str
4154 l_Restrict, --l_asgt_str
4155 l_all, --l_asgt_str
4156 l_None, --l_asgt_str
4157
4158 l_all, --l_cond_str
4159 l_all, --l_cond_str
4160 l_all, --l_cond_str
4161 l_all, --l_cond_str
4162 l_Restrict, ----l_cond_str
4163 p_assignment_id,
4164 p_effective_date,
4165 l_true;
4166 end if;
4167 profile_add_to_cache;
4168 exception
4169 when no_data_found then
4170 if g_debug then
4171 hr_utility.set_location(l_proc, 650);
4172 end if;
4173 null;
4174 when others then
4175 if g_debug then
4176 hr_utility.set_location(l_proc||sqlerrm, 660);
4177 hr_utility.set_location(l_proc, 660);
4178 end if;
4179 raise;
4180 end;
4181 begin
4182 if g_debug then
4183 hr_utility.set_location(l_proc, 670);
4184 end if;
4185 l_from_str := ' from per_security_profiles sec ';
4186 l_cond_str := ' sec.view_all_organizations_flag = :l_all
4187 and sec.view_all_positions_flag = :l_all
4188 and sec.view_all_payrolls_flag = :l_all
4189 and sec.custom_restriction_flag = :l_all
4190 and sec.view_all_flag = :l_Restrict and ';
4191 l_exec_str := l_inst_str||l_from_str||' where '||
4192 l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
4193 if g_debug then
4194 l_exec_str_print:= l_exec_str;
4195 while length(l_exec_str_print)>0 loop
4196 hr_utility.trace(substr(l_exec_str_print,1,70));
4197 l_exec_str_print:=substr(l_exec_str_print,71);
4198 end loop;
4199 End if;
4200 if p_generation_scope = 'ALL_GLOBAL' then
4201 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4202 using
4203 l_Restrict, --l_asgt_str
4204 l_all, --l_asgt_str
4205 l_Restrict, --l_asgt_str
4206 l_all, --l_asgt_str
4207 l_None, --l_asgt_str
4208
4209 l_all, --l_cond_str
4210 l_all, --l_cond_str
4211 l_all, --l_cond_str
4212 l_all, --l_cond_str
4213 l_Restrict, ----l_cond_str
4214
4215 l_Restrict, --l_comm_str
4216 l_true, --l_comm_str
4217 l_U, --l_comm_str
4218 l_FALSE, --l_comm_str
4219 l_true, --l_comm_str
4220 p_assignment_id, --l_comm_str
4221 p_effective_date, --l_comm_str
4222 l_true; --l_comm_str
4223
4224 else
4225 execute immediate l_exec_str bulk collect into l_security_profile_table_temp
4226 using
4227 l_business_group_id, --l_bggr_str
4228 l_Restrict, --l_asgt_str
4229 l_all, --l_asgt_str
4230 l_Restrict, --l_asgt_str
4231 l_all, --l_asgt_str
4232 l_None, --l_asgt_str
4233
4234 l_all, --l_cond_str
4235 l_all, --l_cond_str
4236 l_all, --l_cond_str
4237 l_all, --l_cond_str
4238 l_Restrict, ----l_cond_str
4239
4240 l_Restrict, --l_comm_str
4241 l_true, --l_comm_str
4242 l_U, --l_comm_str
4243 l_FALSE, --l_comm_str
4244 l_true, --l_comm_str
4245 p_assignment_id, --l_comm_str
4246 p_effective_date, --l_comm_str
4247 l_true; --l_comm_str
4248
4249 end if;
4250 profile_add_to_cache;
4251 exception
4252 when no_data_found then
4253 if g_debug then
4254 hr_utility.set_location(l_proc, 680);
4255 end if;
4256 null;
4257 when others then
4258 if g_debug then
4259 hr_utility.set_location(l_proc||sqlerrm, 690);
4260 hr_utility.set_location(l_proc, 690);
4261 end if;
4262 raise;
4263 end;
4264 end if;
4265 end if;
4266 if g_debug then
4267 hr_utility.set_location(l_proc, 700);
4268 end if;
4269 else
4270 close asg_details;
4271 end if;
4272
4273 if g_debug then
4274 hr_utility.set_location(l_proc, 705);
4275 end if;
4276 exception
4277 when no_data_found then
4278 if g_debug then
4279 hr_utility.set_location(l_proc, 730);
4280 end if;
4281 end profile_add_to_person_list;
4282
4283
4284 PROCEDURE profile_insert_cache_to_list IS
4285 errors Number;
4286 l_cnt Number;
4287 dml_errors EXCEPTION;
4288 PRAGMA exception_init(dml_errors, -24381);
4289 l_proc varchar2(100):= 'process_person.profile_insert_cache_to_list';
4290 l_program_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
4291 l_request_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
4292 l_prog_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
4293 l_update_date date := trunc(sysdate);
4294
4295 Begin
4296 if g_debug then
4297 hr_utility.set_location('Entering '||l_proc, 10);
4298 End if;
4299 If l_c_security_profile_table.count > 0 then
4300 if g_debug then
4301 hr_utility.set_location(l_proc, 20);
4302 End if;
4303 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
4304 hr_utility.set_location(l_proc||' using stage table',24);
4305 forall per_rec in l_c_security_profile_table.first .. l_c_security_profile_table.last
4306 SAVE EXCEPTIONS
4307 Insert into per_person_list_stage(security_profile_id,
4308 person_id,request_id,
4309 program_application_id,
4310 program_id,
4311 program_update_date)
4312 values(l_c_security_profile_table(per_rec),
4313 p_person_id,
4314 nvl(l_request_id, ''),
4315 nvl(l_prog_appl_id, ''),
4316 nvl(l_program_id, ''),
4317 to_date(to_char(l_update_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4318 );
4319 else
4320 hr_utility.set_location(l_proc||' using actual table',24);
4321 forall per_rec in l_c_security_profile_table.first .. l_c_security_profile_table.last
4322 SAVE EXCEPTIONS
4323 Insert into per_person_list(security_profile_id,
4324 person_id,request_id,
4325 program_application_id,
4326 program_id,
4327 program_update_date)
4328 values(l_c_security_profile_table(per_rec),
4329 p_person_id,
4330 nvl(l_request_id, ''),
4331 nvl(l_prog_appl_id, ''),
4332 nvl(l_program_id, ''),
4333 to_date(to_char(l_update_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4334 );
4335 end if;
4336 -- End changes for bug 13504049
4337 End if;
4338 if g_debug then
4339 hr_utility.set_location('Leaveing '||l_proc, 30);
4340 End if;
4341 Exception
4342 WHEN dml_errors THEN
4343 errors := SQL%BULK_EXCEPTIONS.COUNT;
4344 l_cnt := l_cnt + errors;
4345 FOR i IN 1..errors LOOP
4346 If g_debug then
4347 hr_utility.trace ('Error occurred during iteration ' ||
4348 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Oracle error is ' ||
4349 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
4350 End if;
4351 If SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 then
4352 raise;
4353 End if;
4354 End loop;
4355 WHEN OTHERS THEN
4356 raise;
4357 End profile_insert_cache_to_list;
4358
4359
4360 PROCEDURE profile_delete_cache_from_list IS
4361 errors Number;
4362 l_cnt Number;
4363 dml_errors EXCEPTION;
4364 PRAGMA exception_init(dml_errors, -24381);
4365 l_proc varchar2(100):= 'process_person.profile_delete_cache_from_list';
4366
4367 Begin
4368 if g_debug then
4369 hr_utility.set_location('Entering '||l_proc, 10);
4370 End if;
4371 If l_d_security_profile_table.count > 0 then
4372 if g_debug then
4373 hr_utility.set_location(l_proc, 20);
4374 End if;
4375
4376
4377 /* forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4378 SAVE EXCEPTIONS
4379 Delete from per_person_list ppl
4380 where ppl.security_profile_id in
4381 (select pspf.security_profile_id
4382 from per_security_profiles pspf
4383 where pspf.security_profile_id = l_d_security_profile_table(per_rec) and
4384 (pspf.view_all_contacts_flag = 'N' or
4385 (pspf.view_all_contacts_flag = 'Y' and
4386 pspf.view_all_candidates_flag = 'X'))
4387 )
4388 and ppl.person_id in (
4389 select pcr.contact_person_id
4390 from per_contact_relationships pcr,
4391 per_person_type_usages_f ptu,
4392 per_person_types ppt
4393 where pcr.person_id = p_person_id
4394 and pcr.contact_person_id = ptu.person_id
4395 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4396
4397 between ptu.effective_start_date
4398 and ptu.effective_end_date
4399 and ptu.person_type_id = ppt.person_type_id
4400 and ppt.system_person_type = 'OTHER')
4401 and ppl.granted_user_id is null;*/
4402 -- Start changes for bug 13504049
4403 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
4404 hr_utility.set_location(l_proc||'deleting stage table', 24);
4405 forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4406 SAVE EXCEPTIONS
4407 Delete from per_person_list_stage
4408 where person_id = p_person_id
4409 and security_profile_id = l_d_security_profile_table(per_rec);
4410 else
4411 hr_utility.set_location(l_proc||'deleting actual table', 28);
4412 forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4413 SAVE EXCEPTIONS
4414 Delete from per_person_list
4415 where person_id = p_person_id
4416 and security_profile_id = l_d_security_profile_table(per_rec);
4417 end if;
4418 -- End changes for bug 13504049
4419 End if;
4420 if g_debug then
4421 hr_utility.set_location('Leaving '||l_proc, 30);
4422 End if;
4423 Exception
4424 WHEN dml_errors THEN
4425 errors := SQL%BULK_EXCEPTIONS.COUNT;
4426 l_cnt := l_cnt + errors;
4427 FOR i IN 1..errors LOOP
4428 If g_debug then
4429 hr_utility.trace ('Error occurred during iteration ' ||
4430 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Oracle error is ' ||
4431 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
4432 End if;
4433 If SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 then
4434 raise;
4435 End if;
4436 End loop;
4437 WHEN OTHERS THEN
4438 raise;
4439 End profile_delete_cache_from_list;
4440
4441
4442
4443
4444
4445
4446 procedure profile_process_list is
4447
4448 l_proc varchar2(75) := 'process_person.profile_process_list';
4449 chk number;
4450 l_counter number :=1;
4451 d_counter number :=1;
4452 i number;
4453 j number;
4454 k number;
4455
4456 begin
4457 hr_utility.set_location('Entering '||l_proc,10);
4458 select security_profile_id bulk collect into l_c_security_profile_table
4459 from per_person_list ppl where
4460 ppl.person_id = p_person_id
4461 and ppl.granted_user_id is null and exists
4462 (select 'X' from per_security_profiles pspf
4463 where pspf.security_profile_id = ppl.security_profile_id);
4464
4465
4466 hr_utility.set_location('Current list',1);
4467 k := l_security_profile_table.first;
4468 while k is not null
4469 loop
4470 hr_utility.set_location(l_security_profile_table(k),2);
4471 k := l_security_profile_table.next(k);
4472 end loop;
4473
4474 i:=l_c_security_profile_table.first;
4475 while i is not null
4476 loop
4477 hr_utility.set_location('i = '||l_c_security_profile_table(i),20);
4478 chk:=0;
4479 j:=l_security_profile_table.first;
4480 while j is not null
4481 loop
4482 hr_utility.set_location('j = '||l_security_profile_table(j),30);
4483 if l_c_security_profile_table(i)= l_security_profile_table(j) then
4484 chk := 1;
4485 hr_utility.set_location('Found-'||l_c_security_profile_table(i),40);
4486 l_c_security_profile_table.delete(i);
4487 l_security_profile_table.delete(j);
4488 exit;
4489 end if;
4490 j:=l_security_profile_table.next(j);
4491 end loop;
4492 if chk = 0 then
4493 hr_utility.set_location('Not Found- Delete'||l_c_security_profile_table(i),50);
4494 l_d_security_profile_table(d_counter) := l_c_security_profile_table(i);
4495 d_counter := d_counter + 1;
4496 l_c_security_profile_table.delete(i);
4497 end if;
4498 i:=l_c_security_profile_table.next(i);
4499 end loop;
4500
4501 if l_security_profile_table.count > 0 then
4502
4503 l_c_security_profile_table.delete;
4504
4505 k := l_security_profile_table.first;
4506 while k is not null loop
4507 hr_utility.set_location('New list : '|| l_security_profile_table(k),60);
4508 l_c_security_profile_table(l_counter):= l_security_profile_table(k);
4509 l_counter := l_counter + 1;
4510 k := l_security_profile_table.next(k);
4511 end loop;
4512
4513 end if;
4514 hr_utility.set_location('List to insert',70);
4515 k := l_c_security_profile_table.first;
4516 while k is not null
4517 loop
4518 hr_utility.set_location(l_c_security_profile_table(k),80);
4519 k := l_c_security_profile_table.next(k);
4520 end loop;
4521
4522 hr_utility.set_location('List to delete',701);
4523 k := l_d_security_profile_table.first;
4524 while k is not null
4525 loop
4526 hr_utility.set_location(l_d_security_profile_table(k),801);
4527 k := l_d_security_profile_table.next(k);
4528 end loop;
4529
4530 hr_utility.set_location('Leaving '||l_proc,999);
4531 end profile_process_list;
4532
4533 -- Code changes for bug 8691129 end.
4534
4535
4536 BEGIN
4537
4538 hr_utility.set_location('Entering : '||l_proc,10);
4539 hr_utility.set_location('p_person_id '||p_person_id,15);
4540 hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541 'DD-MON-YYYY'),20);
4542 hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4543 hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
4544
4545 /*
4546 ** We need to find out quickly if the person is an APL today or in the
4547 ** future so we can control processing later.
4548 open c_is_current_apl;
4549 fetch c_is_current_apl into l_is_current_apl;
4550 close c_is_current_apl;
4551 */
4552 /*
4553 ** We need to find out if the person is current any 'EX' type
4554 open c_is_former;
4555 fetch c_is_former into l_is_former;
4556 close c_is_former;
4557 */
4558
4559 hr_utility.set_location(l_proc,40);
4560
4561
4562 -- Code change for bug 8691129 Start
4563
4564 if( p_generation_scope = 'ALL_PROFILES') then
4565
4566 -- To delete all the contacts of the person
4567
4568 delete from per_person_list ppl
4569 where ppl.security_profile_id in
4570 (select pspf.security_profile_id
4571 from per_security_profiles pspf
4572 where (pspf.view_all_contacts_flag = 'N' or
4573 (pspf.view_all_contacts_flag = 'Y' and
4574 pspf.view_all_candidates_flag = 'X'))
4575 )
4576 and ppl.person_id in (
4577 select pcr.contact_person_id
4578 from per_contact_relationships pcr,
4579 per_person_type_usages_f ptu,
4580 per_person_types ppt
4581 where pcr.person_id = p_person_id
4582 and pcr.contact_person_id = ptu.person_id
4583 and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4584 between ptu.effective_start_date
4585 and ptu.effective_end_date
4586 and ptu.person_type_id = ppt.person_type_id
4587 and ppt.system_person_type = 'OTHER')
4588 and ppl.granted_user_id is null;
4589
4590
4591 --In this section will determine whether the person is EMP/APL/CWK now or
4592 -- in the future and process them accordingly on the relevant date.
4593
4594 if p_who_to_process in ('CURRENT','ALL') then
4595 open c_current_person;
4596 fetch c_current_person into l_person_id, l_person_proc_date;
4597
4598 if c_current_person%found then
4599 close c_current_person;
4600 hr_utility.set_location(l_proc,601);
4601 for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4602 hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4603 profile_add_to_person_list(
4604 p_effective_date => l_person_proc_date,
4605 p_assignment_id => l_asgrec.assignment_id,
4606 p_business_group_id => p_business_group_id,
4607 p_generation_scope => p_generation_scope);
4608 end loop;
4609
4610 else
4611 close c_current_person;
4612 end if;
4613
4614 end if;
4615
4616 -- In this section will determine whether the person is EX-EMP/EX-APL/EX-CWK now and process them
4617 -- accordingly on the relevant date. In this case the relevant date is the effective_end_date of the
4618 -- last assignment they had.
4619
4620 if p_who_to_process in ('TERM','ALL') then
4621
4622 hr_utility.set_location(l_proc,701);
4623
4624 for l_former_person in c_former_person loop
4625 for l_asgrec in c_get_asg(l_former_person.person_id,l_former_person.effective_date) loop
4626 hr_utility.set_location(to_char(l_asgrec.assignment_id),901);
4627 hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4628 profile_add_to_person_list(
4629 p_effective_date => l_former_person.effective_date,
4630 p_assignment_id => l_asgrec.assignment_id,
4631 p_business_group_id => p_business_group_id,
4632 p_generation_scope => p_generation_scope);
4633
4634 end loop;
4635 if l_former_person.assignment_type in ('E','C') then
4636 hr_utility.set_location(l_proc,1001);
4637 exit;
4638 end if;
4639 end loop;
4640
4641 end if;
4642
4643 -- process the list prepared from the above sections.
4644
4645 profile_process_list;
4646
4647 -- insert the processed list to the per_person_list marked for insertion.
4648
4649 profile_insert_cache_to_list;
4650
4651 -- delete the processed list from the per_person_list marked for deletion.
4652
4653 profile_delete_cache_from_list;
4654
4655 -- add all contacts of the person to person list
4656
4657 add_contacts_for_person(p_person_id => p_person_id,
4658 p_effective_date => p_effective_date,
4659 p_generation_scope => p_generation_scope,
4660 p_business_group_id => p_business_group_id);
4661
4662 else
4663
4664 if p_who_to_process in ('CURRENT','ALL')
4665 -- or ( p_who_to_process = 'TERM'
4666 -- and l_is_current_apl = 'Y'
4667 -- and l_is_former = 'Y')
4668 then
4669
4670 /*
4671 ** The current implementation coded below has one flaw when processing a former
4672 ** EMP/CWK who as applied for a job and is an APL with who to process as ALL. In
4673 ** this case the person will be processed three times.
4674 ** 1) As an APL by the current_person cursor.
4675 ** 2) As an APL by the former_person cursor.
4676 ** 3) As an EX-EMP/EX-CWK by the former_person cursor.
4677 **
4678 ** Optimizations for this issue will be considered at a later date. If this comment
4679 ** is still in the file then you know the optimization has not yet been completed.
4680 */
4681
4682 hr_utility.set_location(l_proc,50);
4683 /*
4684 ** We are processing current EMP/APL/CWK or
4685 ** we are processing former EMP/APL/CWK but the person we are dealing
4686 ** with is both a current APL and a former EMP/CWK in which case we
4687 ** also need to process them here. This is because an APL should also
4688 ** be visible to the profiles who could see them when EMP/CWK.
4689 **
4690 ** In this section will determine whether the person is EMP/APL/CWK now or
4691 ** in the future and process them accordingly on the relevant date.
4692 */
4693 open c_current_person;
4694 fetch c_current_person into l_person_id, l_person_proc_date;
4695
4696 if c_current_person%found then
4697 close c_current_person;
4698 /*
4699 ** Clear out the person list for this person_id
4700 */
4701 clear_per_list_table(p_person_id => p_person_id,
4702 p_business_group_id => p_business_group_id,
4703 p_generation_scope => p_generation_scope,
4704 p_effective_date => p_effective_date);
4705 l_cleared_ppl := TRUE;
4706
4707 /*
4708 ** Populate the person list for each assignment that this person has.
4709 */
4710 for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4711 hr_utility.set_location(l_proc,60);
4712 hr_utility.trace('processing assignment :'||
4713 to_char(l_asgrec.assignment_id));
4714 hr_security_internal.add_to_person_list(
4715 p_effective_date => l_person_proc_date,
4716 p_assignment_id => l_asgrec.assignment_id,
4717 p_business_group_id => p_business_group_id,
4718 p_generation_scope => p_generation_scope);
4719
4720 end loop;
4721 else
4722 close c_current_person;
4723 end if;
4724 end if;
4725
4726 if p_who_to_process in ('TERM','ALL')
4727 -- or ( p_who_to_process = 'CURRENT'
4728 -- and l_is_current_apl = 'Y'
4729 -- and l_is_former = 'Y')
4730 then
4731 hr_utility.set_location(l_proc,70);
4732 /*
4733 ** We are processing former EMP/APL/CWK or
4734 ** we are processing current EMP/APL/CWK but the person we are dealing
4735 ** with is both a current APL and a former EMP/CWK in which case we
4736 ** also need to process them here. This is because an APL should also
4737 ** be visible to the people who could see them when EMP/CWK.
4738 **
4739 ** In this section will determine whether the person is EX-EMP/EX-APL/EX-CWK
4740 ** now and process them accordingly on the relevant date. In this case the
4741 ** relevant date is the effective_end_date of the last assignment they had.
4742 ** Note that we process a person twice if they are EX-APL and EX-EMP/EX-CWK.
4743 */
4744 -- Bug6809753 -start
4745 /*-- Start changes made for the bug 5252738 - Bug6809753 ---*/
4746 open c_exclude_person;
4747 fetch c_exclude_person into lc_person_id;
4748
4749 if c_exclude_person%notfound then
4750 /*-- End changes made for the bug 5252738 - Bug6809753 ---*/
4751 -- Bug6809753 -end
4752
4753 for l_former_person in c_former_person loop
4754 hr_utility.set_location(l_proc,80);
4755 if l_cleared_ppl <> TRUE then
4756 /*
4757 ** Clear out the person list for this person_id if it's not been cleared
4758 ** already for this person.
4759 */
4760 clear_per_list_table(p_person_id => p_person_id,
4761 p_business_group_id => p_business_group_id,
4762 p_generation_scope => p_generation_scope,
4763 p_effective_date => p_effective_date);
4764 l_cleared_ppl := TRUE;
4765 end if;
4766 /*
4767 ** Populate the person list for each assignment that this person has.
4768 */
4769 for l_asgrec in c_get_asg(l_former_person.person_id,
4770 l_former_person.effective_date) loop
4771
4772 hr_utility.set_location(l_proc,90);
4773 hr_utility.trace('processing assignment :'||
4774 to_char(l_asgrec.assignment_id));
4775
4776 hr_security_internal.add_to_person_list(
4777 p_effective_date => l_former_person.effective_date,
4778 p_assignment_id => l_asgrec.assignment_id,
4779 p_business_group_id => p_business_group_id,
4780 p_generation_scope => p_generation_scope);
4781
4782 end loop;
4783 if l_former_person.assignment_type in ('E','C') then
4784 /*
4785 ** Exit if we've processed either an Emp or CWK. For an APL or EX-APL
4786 ** who is also EX-EMP/EX-CWK we will process the APL/EX_APL record then
4787 ** the EX-EMP/EX-CWK record then exit.
4788 ** For someone who is both EX-EMP and EX-CWK we only want to process the
4789 ** person for the EX-type they were last. i.e. an EMP leaves and returns
4790 ** as CWK then leaves they will be both EX-EMP and EX-CWK in the PTU
4791 ** table. We only want to process them as EX-CWK.
4792 */
4793 hr_utility.set_location(l_proc,100);
4794 exit;
4795 end if;
4796 end loop;
4797 -- Bug6809753 -start
4798 end if;
4799 close c_exclude_person; /*-- End changes made for the bug 5252738 - Bug6809753 ---*/
4800 -- Bug6809753 -end
4801 end if;
4802 /*
4803 ** Load up the contacts for this person
4804 */
4805 add_contacts_for_person(p_person_id => p_person_id,
4806 p_effective_date => p_effective_date,
4807 p_generation_scope => p_generation_scope,
4808 p_business_group_id => p_business_group_id);
4809
4810 hr_utility.set_location('Leaving : '||l_proc,110);
4811
4812 end if;
4813 -- code changes for bug 8691129 end
4814
4815 END process_person;
4816 --
4817 -- ----------------------------------------------------------------------------
4818 -- |---------------------< generate_opp_lists >-------------------------------|
4819 -- ----------------------------------------------------------------------------
4820 --
4821 procedure generate_opp_lists(
4822 p_effective_date in date
4823 ,p_generation_scope in varchar2
4824 ,p_business_group_id in number
4825 ) is
4826 --
4827 l_effective_date date;
4828 l_update_date date;
4829 l_found boolean default false;
4830 l_business_group_mode varchar2(20);
4831 l_proc varchar2(72) := g_package||'.generate_opp_lists';
4832 --
4833 cursor security_profiles is
4834 select *
4835 from per_security_profiles
4836 where ((business_group_id = p_business_group_id and
4837 p_generation_scope = 'ALL_BUS_GRP')
4838 or (business_group_id is null and
4839 p_generation_scope = 'ALL_GLOBAL')
4840 or (p_generation_scope = 'ALL_PROFILES')
4841 and org_security_mode in ('NONE','HIER'));
4842 --
4843 begin
4844 --
4845 -- Get the session date and the current date to avoid multiple selects.
4846 hr_utility.set_location('Entering '||l_proc, 10);
4847 hr_utility.set_location('Request ID '||p_request_id, 15);
4848 --
4849 l_effective_date := trunc(p_effective_date);
4850 l_update_date := trunc(sysdate);
4851 --
4852 hr_utility.set_location(l_proc, 20);
4853 --
4854 for sec_rec in security_profiles loop
4855 --
4856 -- Delete previous entries for the profile. By using this function
4857 -- we will be using the old generation mechanism so we need to clear
4858 -- the person list data upfront.
4859 clear_sp_list_table(p_generation_scope => p_generation_scope,
4860 p_business_group_id => sec_rec.business_group_id,
4861 p_security_profile_id => sec_rec.security_profile_id,
4862 p_clear_people_flag => false);
4863 --
4864 hr_utility.set_location(l_proc, 30);
4865 --
4866 if (sec_rec.view_all_flag = 'N') then
4867 --
4868 hr_utility.set_location(l_proc, 40);
4869 --
4870 if (sec_rec.view_all_payrolls_flag = 'N') then
4871 --
4872 hr_utility.set_location(l_proc, 50);
4873 --
4874 -- Build the payroll list.
4875 build_payroll_list(sec_rec.security_profile_id,
4876 sec_rec.business_group_id,
4877 sec_rec.include_exclude_payroll_flag,
4878 l_effective_date,
4879 l_update_date);
4880 --
4881 end if; -- view_all_payrolls_flag
4882 --
4883 -- Do not insert the orgs if using user-based security.
4884 if (sec_rec.view_all_organizations_flag = 'N' and
4885 nvl(sec_rec.top_organization_method, 'S') <> 'U') then
4886 --
4887 hr_utility.set_location(l_proc, 60);
4888 --
4889 -- Determine business_group mode for the current security profile
4890 if sec_rec.business_group_id is null then
4891 l_business_group_mode := 'GLOBAL';
4892 else
4893 l_business_group_mode := 'LOCAL';
4894 end if;
4895 --
4896 -- Build organization list
4897 build_organization_list(sec_rec.security_profile_id,
4898 sec_rec.include_top_organization_flag,
4899 sec_rec.organization_structure_id,
4900 sec_rec.organization_id,
4901 sec_rec.exclude_business_groups_flag,
4902 l_effective_date,
4903 l_update_date,
4904 l_business_group_mode);
4905 --
4906 end if;
4907 --
4908 -- Do not insert the positions if using user-based security.
4909 if (sec_rec.view_all_positions_flag = 'N' and
4910 nvl(sec_rec.top_position_method, 'S') <> 'U') then
4911 --
4912 hr_utility.set_location(l_proc, 70);
4913 --
4914 -- Build position list
4915 build_position_list(sec_rec.security_profile_id,
4916 sec_rec.view_all_organizations_flag,
4917 sec_rec.include_top_position_flag,
4918 sec_rec.position_structure_id,
4919 sec_rec.position_id,
4920 l_effective_date,
4921 l_update_date);
4922 --
4923 end if;
4924 --
4925 hr_utility.set_location(l_proc, 80);
4926 --
4927 end if;
4928 --
4929 l_found:=true;
4930 --
4931 hr_utility.set_location('Request ID '||p_request_id, 15);
4932 --
4933 end loop;
4934 --
4935 hr_utility.set_location(l_proc, 80);
4936 -- Clearing all unrelated contacts.
4937 clear_unrelated_contacts(p_generation_scope => p_generation_scope,
4938 p_business_group_id => p_business_group_id);
4939 --
4940 -- Add unrelated contacts for the profiles we've processed.
4941 hr_utility.set_location(l_proc, 90);
4942 hr_utility.set_location('Request ID '||p_request_id, 15);
4943 --
4944 add_unrelated_contacts(p_business_group_id => p_business_group_id,
4945 p_generation_scope => p_generation_scope,
4946 p_effective_date => l_effective_date);
4947 --
4948 hr_utility.set_location('Leaving '||l_proc, 130);
4949 --
4950 end generate_opp_lists;
4951
4952 --
4953 -- ----------------------------------------------------------------------------
4954 -- |---------------------< build_lists_for_users >----------------------------|
4955 -- ----------------------------------------------------------------------------
4956 --
4957 PROCEDURE build_lists_for_users
4958 (p_sec_prof_rec IN hr_security_internal.g_sec_prof_r
4959 ,p_effective_date IN DATE
4960 ,p_debug IN BOOLEAN DEFAULT FALSE
4961 ,p_user_id IN NUMBER default null
4962 ,p_process_all_users IN BOOLEAN DEFAULT TRUE)
4963
4964 IS
4965
4966 --
4967 -- Local variables
4968 --
4969 l_proc varchar2(72):= g_package||'build_lists_for_users';
4970 l_debug_type NUMBER := hr_security_internal.g_NO_DEBUG;
4971 l_person_id NUMBER;
4972 l_employee_id NUMBER;
4973 l_user_id NUMBER;
4974 l_all_static_users VARCHAR2(1) := 'Y';
4975 --
4976 l_api_ovn NUMBER := 3;
4977 l_del_static_lists_warning BOOLEAN;
4978
4979 --
4980 l_resp_id NUMBER;
4981 l_resp_app_id NUMBER;
4982 l_sec_grp_id NUMBER;
4983
4984 --
4985 -- Fetch the users in the static user list, ignoring users that do
4986 -- not have a person attached. If p_user_id has a value then just that
4987 -- single user will be fetched.
4988 --
4989 CURSOR csr_get_users IS
4990 SELECT seu.user_id
4991 ,usr.employee_id person_id
4992 ,seu.security_user_id
4993 ,seu.object_version_number
4994 FROM per_security_users seu
4995 ,fnd_user usr
4996 WHERE seu.security_profile_id = p_sec_prof_rec.security_profile_id
4997 AND (seu.process_in_next_run_flag = 'Y' OR l_all_static_users = 'Y')
4998 AND seu.user_id = usr.user_id
4999 AND usr.employee_id IS NOT NULL
5000 AND nvl(p_user_id,usr.user_id) = seu.user_id;
5001
5002 --
5003 -- Retrieve details of first responsibility available
5004 -- for the user to which this security profile is
5005 -- attached to perform the apps_initialize
5006 --
5007 CURSOR csr_get_resp (p_user_id number,p_security_profile_id varchar2)
5008 IS
5009 SELECT fusg.responsibility_id,
5010 fusg.responsibility_application_id,
5011 fusg.security_group_id
5012 FROM FND_USER_RESP_GROUPS fusg
5013 WHERE fusg.user_id = p_user_id
5014 AND p_effective_date BETWEEN fusg.start_date
5015 AND NVL(fusg.end_date,p_effective_date)
5016 AND EXISTS(
5017 SELECT level_value FROM
5018 fnd_profile_option_values
5019 WHERE profile_option_id = (SELECT profile_option_id FROM fnd_profile_options_vl
5020 WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID')
5021 AND PROFILE_OPTION_VALUE = p_security_profile_id --SECURITY_PROFILE_ID
5022 AND LEVEL_ID=10003
5023 AND level_value=fusg.responsibility_id )
5024 AND rownum = 1;
5025
5026 CURSOR csr_get_resp_sge (p_user_id number,p_security_profile_id varchar2)
5027 IS
5028 SELECT fusg.responsibility_id,
5029 fusg.responsibility_application_id,
5030 fusg.security_group_id
5031 FROM FND_USER_RESP_GROUPS fusg
5032 WHERE fusg.user_id = p_user_id
5033 AND p_effective_date BETWEEN fusg.start_date
5034 AND NVL(fusg.end_date,p_effective_date)
5035 AND EXISTS(
5036 SELECT RESPONSIBILITY_ID FROM per_sec_profile_assignments_v
5037 WHERE user_id = p_user_id
5038 AND security_profile_id = p_security_profile_id
5039 AND responsibility_id = fusg.responsibility_id)
5040 AND rownum = 1;
5041
5042 BEGIN
5043 hr_utility.set_location('Entering: '||l_proc,10);
5044 hr_utility.set_location(l_proc||' sec prof id '||
5045 to_char(p_sec_prof_rec.security_profile_id),13);
5046 hr_utility.set_location(l_proc||' p_effective_date '||
5047 p_effective_date,13);
5048 hr_utility.set_location(l_proc||' p_user_id '||
5049 p_user_id,13);
5050
5051 --
5052 -- Check that the mandatory parameters have been entered.
5053 --
5054 IF p_sec_prof_rec.security_profile_id IS NOT NULL
5055 AND p_effective_date IS NOT NULL THEN
5056
5057 --
5058 -- If debug output is required, set the debug type.
5059 --
5060 IF p_debug THEN
5061 l_debug_type := hr_security_internal.G_FND_LOG;
5062 END IF;
5063 --
5064 -- make sure that single users who are unchecked in the security
5065 -- profiles form are picked up.
5066 --
5067 -- if p_process_all_user is true then run for everybody, regardless of
5068 -- process flag status.
5069 --
5070 IF (p_user_id is not null OR p_process_all_users)
5071 THEN
5072 l_all_static_users := 'Y';
5073 ELSE
5074 l_all_static_users := 'N';
5075 END IF;
5076
5077 FOR user_rec IN csr_get_users LOOP
5078
5079 -- Bug 3598627
5080 -- If user-based custom security is used then
5081 -- retrieve the first valid responsibility for
5082 -- user to which this security profile is
5083 -- attached and call 'apps_initialize' procedure
5084 -- to set the REQUIRED user level context.
5085 --
5086 -- Note: The first valid responsibility may not
5087 -- set the correct responsibility level
5088 -- context, but it is user level context
5089 -- required here will be correct.
5090 --
5091 -- If valid responsibility is not found for user
5092 -- security permisions will not be stored in the
5093 -- static list.
5094
5095 l_resp_id := NULL;
5096 l_resp_app_id := NULL;
5097 l_sec_grp_id := NULL;
5098
5099
5100 IF (p_sec_prof_rec.custom_restriction_flag = 'U')
5101 THEN
5102 if fnd_profile.value('ENABLE_SECURITY_GROUPS') = 'N' then
5103 OPEN
5104 csr_get_resp(user_rec.user_id,to_char(p_sec_prof_rec.security_profile_id));
5105 FETCH csr_get_resp INTO l_resp_id,
5106 l_resp_app_id,
5107 l_sec_grp_id;
5108 hr_utility.set_location('Security Groups No - Resp id-'||l_resp_id,555);
5109 CLOSE csr_get_resp;
5110 else
5111 OPEN
5112 csr_get_resp_sge(user_rec.user_id,to_char(p_sec_prof_rec.security_profile_id));
5113 FETCH csr_get_resp_sge INTO l_resp_id,
5114 l_resp_app_id,
5115 l_sec_grp_id;
5116 hr_utility.set_location('Security Groups Yes - Resp id '||l_resp_id,556);
5117 CLOSE csr_get_resp_sge;
5118 end if;
5119 IF l_resp_id is not null then
5120 fnd_global.apps_initialize(user_rec.user_id,
5121 l_resp_id,
5122 l_resp_app_id,
5123 l_sec_grp_id);
5124 else
5125 hr_utility.set_location('Security Profile was not attached to any responsibility of this user',557);
5126 end if;
5127 END IF;
5128
5129 -- If user-based custom security is not used or
5130 -- valid responsibility is found when used.
5131 --
5132 IF (NVL(p_sec_prof_rec.custom_restriction_flag, 'N') <> 'U')
5133 OR (l_resp_id IS NOT NULL) THEN
5134 --
5135 -- For each user in the static list, assess
5136 -- permissions and store in the static tables.
5137 --
5138 -- Set the person to be the named person on the
5139 -- security profile if set, otherwise use the
5140 -- person on the user.
5141 --
5142 l_person_id := NVL(p_sec_prof_rec.named_person_id
5143 ,user_rec.person_id);
5144
5145 hr_security_internal.evaluate_access
5146 (p_person_id => l_person_id
5147 ,p_user_id => user_rec.user_id
5148 ,p_effective_date => p_effective_date
5149 ,p_sec_prof_rec => p_sec_prof_rec
5150 ,p_what_to_evaluate => hr_security_internal.g_ALL
5151 ,p_use_static_lists => FALSE
5152 ,p_update_static_lists => TRUE
5153 ,p_debug => l_debug_type);
5154 --
5155 --Bug 4742108 set everyone back to N if they have been processed
5156 -- regardless of whether processing all or marked static users.
5157 l_api_ovn := user_rec.object_version_number;
5158 -- Bug 4338667
5159 -- now record has been processed need to set
5160 -- process_in_next_run_flag from 'Y' to 'N'.
5161 --
5162 hr_security_user_api.update_security_user
5163 (p_effective_date => p_effective_date
5164 ,p_security_user_id => user_rec.security_user_id
5165 ,p_user_id => user_rec.user_id
5166 ,p_security_profile_id => p_sec_prof_rec.security_profile_id
5167 ,p_process_in_next_run_flag => 'N'
5168 ,p_object_version_number => l_api_ovn
5169 ,p_del_static_lists_warning => l_del_static_lists_warning);
5170 END IF;
5171 END LOOP;
5172 END IF; -- p_sec_prof_rec.security_profile_id has value
5173 hr_utility.set_location('Leaving : '||l_proc,50);
5174 END build_lists_for_users;
5175 --
5176 -- ----------------------------------------------------------------------------
5177 -- |---------------------< build_lists_for_user >------------------------------|
5178 -- ----------------------------------------------------------------------------
5179 -- built for gsi enhancement bug 4634655 and 4338667.
5180 -- this is not a public api but customers have been granted permission to call
5181 -- this procedure directly so DO NOT change parameters as this will invalidate
5182 -- customer code.
5183 --
5184 PROCEDURE build_lists_for_user
5185 (p_security_profile_id number,
5186 p_user_id number,
5187 p_effective_date date default trunc(sysdate)) IS
5188 --
5189 -- Local Variables
5190 --
5191 l_proc varchar2(72):= g_package||'build_lists_for_user';
5192 l_debug boolean default FALSE;
5193 l_process_all_users boolean := TRUE; -- as just running for one user.
5194 l_user_id number;
5195 l_security_profile_id number;
5196 l_sec_prof_rec hr_security_internal.g_sec_prof_r; -- per_security_profiles
5197 -- %ROWTYPE;
5198 -- Cursors:
5199 --
5200 -- check that user and profile exist in per_security_users
5201 --
5202 CURSOR csr_check_user_exists(p_user_id number, p_security_profile_id number)
5203 IS
5204 SELECT seu.user_id,
5205 seu.security_profile_id
5206 FROM per_security_users seu
5207 WHERE seu.user_id = p_user_id
5208 AND seu.security_profile_id = p_security_profile_id;
5209 --
5210 -- get security profile
5211 --
5212 CURSOR csr_security_profile_record(l_security_profile_id number)
5213 IS
5214 SELECT *
5215 from per_security_profiles
5216 where security_profile_id = l_security_profile_id;
5217 --
5218 BEGIN
5219 --
5220 hr_utility.trace('Processing for Single User');
5221 hr_utility.set_location('Entering : '||l_proc,10);
5222 --
5223 -- Check that the mandatory parameters have been entered.
5224 --
5225 IF p_security_profile_id IS NOT NULL
5226 AND p_user_id IS NOT NULL
5227 THEN
5228 -- if they exist continue processing, if they don't then do nothing.
5229 -- check that user and security profile exist
5230 OPEN csr_check_user_exists(p_user_id, p_security_profile_id);
5231 FETCH csr_check_user_exists
5232 INTO l_user_id,
5233 l_security_profile_id;
5234 CLOSE csr_check_user_exists;
5235 hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
5236 hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237 hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238 p_security_profile_id,23);
5239 hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240 hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241 l_security_profile_id,27);
5242 hr_utility.set_location('IF l_user_id or l_security profile id is null '
5243 ,30);
5244 hr_utility.set_location('then it has not been found in per_security_users'
5245 ,30);
5246 --
5247 -- if they exist then get security profile for user
5248 --
5249 OPEN csr_security_profile_record(l_security_profile_id);
5250 FETCH csr_security_profile_record
5251 INTO l_sec_prof_rec;
5252 CLOSE csr_security_profile_record;
5253 -- call build lists for users for a single user
5254 hr_utility.set_location(l_proc||'call build_lists_for_users '
5255 ||p_effective_date,40);
5256 build_lists_for_users
5257 (p_sec_prof_rec => l_sec_prof_rec
5258 ,p_effective_date => trunc(p_effective_date)
5259 ,p_debug => l_debug
5260 ,p_user_id => l_user_id
5261 ,p_process_all_users => l_process_all_users);
5262 END IF;
5263 hr_utility.set_location('Leaving : '||l_proc,69);
5264 END build_lists_for_user;
5265 --
5266 -- ----------------------------------------------------------------------------
5267 -- |---------------------< generate_list_control >-----------------------------|
5268 -- ----------------------------------------------------------------------------
5269 --
5270 procedure generate_list_control(
5271 p_effective_date date,
5272 p_generation_scope varchar2,
5273 p_business_group_id varchar2 default null,
5274 p_security_profile_id varchar2 default null,
5275 p_who_to_process varchar2 default null,
5276 p_action_parameter_group_id varchar2 default null,
5277 p_user_id varchar2 default null,
5278 p_static_user_processing varchar2 default 'ALL_STATIC',
5279 -- Bug fix 3816741
5280 errbuf out NOCOPY varchar2,
5281 retcode out NOCOPY number,
5282 p_use_temp_tables varchar2) -- Added for bug 13504049
5283 is
5284
5285 l_proc varchar2(60) := g_package||'generate_list_control';
5286
5287 l_business_group_id number;
5288 l_security_profile_id number;
5289 l_action_parameter_group_id number;
5290 l_user_id number;
5291 l_security_profile_name per_security_profiles.security_profile_name%TYPE;
5292 l_logging pay_action_parameters.parameter_value%TYPE;
5293 l_request_id number;
5294 l_effective_date varchar2(50);
5295 l_update_date date;
5296 l_debug boolean := FALSE;
5297 l_success boolean;
5298 l_status varchar2(100);
5299 l_phase varchar2(100);
5300 l_dev_status varchar2(100);
5301 l_dev_phase varchar2(100);
5302 l_message varchar2(100);
5303 l_request_data varchar2(100);
5304 c_wait number := 60;
5305 c_timeout number := 300;
5306 l_process_all_users boolean := TRUE;
5307
5308 -- bug fix 3816741 starts here
5309
5310 l_call_status boolean;
5311
5312 -- bug fix 3816741 ends here
5313
5314 --
5315 -- Fetch the action parameter to determine if logging
5316 -- should be switched on.
5317 --
5318 CURSOR csr_get_action_param IS
5319 SELECT pap.parameter_value
5320 FROM pay_action_parameters pap
5321 WHERE pap.parameter_name = 'LOGGING';
5322
5323 CURSOR security_profiles
5324 IS
5325 SELECT *
5326 FROM per_security_profiles
5327 WHERE ( (business_group_id = p_business_group_id and
5328 p_generation_scope = 'ALL_BUS_GRP')
5329 OR
5330 (business_group_id is null and
5331 p_generation_scope = 'ALL_GLOBAL')
5332 OR
5333 (p_generation_scope = 'ALL_PROFILES')
5334 AND org_security_mode IN ('NONE','HIER')
5335 );
5336 --
5337 begin
5338 hr_utility.set_location('Entering : '||l_proc,10);
5339
5340 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
5341 l_security_profile_id := to_number(p_security_profile_id);
5342 l_business_group_id := to_number(p_business_group_id);
5343 l_user_id := to_number(p_user_id);
5344 l_action_parameter_group_id := to_number(p_action_parameter_group_id);
5345 l_update_date := trunc(sysdate);
5346 --
5347 /*
5348 ** Perform restart checking....
5349 */
5350 l_request_data := fnd_conc_global.request_data;
5351 hr_utility.set_location(l_request_data,12);
5352 if l_request_data is not null then
5353
5354 OPEN csr_get_action_param;
5355 FETCH csr_get_action_param INTO l_logging;
5356 CLOSE csr_get_action_param;
5357
5358 --
5359 -- If logging is set to General, enable debugging.
5360 --
5361 IF instr(NVL(l_logging, 'N'), 'G') <> 0 THEN
5362 l_debug := TRUE;
5363 END IF;
5364 -- Bug 4338667. If user has been specified just run for that user.
5365 -- calling build_list for user rather than buld lists for users to avoid
5366 -- having to dup code by calling sec rec cursor for just this users
5367 -- security profile id. If this is considerably less efficient then we can
5368 -- revise.
5369 /*
5370 IF p_user_id IS NOT NULL
5371 THEN
5372 hr_utility.trace('Processing for Single User - 1');
5373 --
5374 build_lists_for_user
5375 (p_security_profile_id => p_security_profile_id
5376 ,p_user_id => l_user_id
5377 ,p_effective_date => p_effective_date);
5378 END IF;
5379 */
5380 -- Bug 4338667 process all static users unless process only flagged users has
5381 -- been choosen
5382 IF p_static_user_processing = 'FLAGGED_STATIC'
5383 THEN
5384 l_process_all_users := FALSE;
5385 ELSE
5386 l_process_all_users := TRUE;
5387 END IF;
5388
5389 /*
5390 ** On restart we need to finish off by processing the
5391 ** ex-emps.
5392 */
5393 FOR sec_rec in security_profiles LOOP
5394 hr_utility.set_location(l_proc||' SP id '||
5395 to_char(sec_rec.security_profile_id),13);
5396 add_person_list_changes (sec_rec.security_profile_id,
5397 p_effective_date,
5398 l_update_date);
5399
5400 --
5401 -- Build static lists for any users in the list of people to
5402 -- build static lists for. At present, this is outside of the
5403 -- multithreaded PYUGEN process.
5404 --
5405 build_lists_for_users
5406 (p_sec_prof_rec => sec_rec
5407 ,p_effective_date => p_effective_date
5408 ,p_process_all_users => l_process_all_users
5409 ,p_user_id => l_user_id
5410 ,p_debug => l_debug);
5411
5412 END LOOP;
5413
5414 -- Bug fix 3816741 starts here
5415 -- code to check the status of child request. PERSLM will error out
5416 -- if any of the parallel process for MSL_PERSON_LIST concurrent
5417 -- program errors out.
5418
5419 l_call_status := fnd_concurrent.get_request_status(
5420 request_id => l_request_data,
5421 phase => l_phase,
5422 status => l_status,
5423 dev_phase => l_dev_phase,
5424 dev_status => l_dev_status,
5425 message => l_message);
5426
5427 hr_utility.set_location(l_proc||' Dev phase:'||l_dev_phase,14);
5428 hr_utility.set_location(l_proc||' Dev status:'||l_dev_status,15);
5429
5430 if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
5431 errbuf := l_message;
5432 retcode := 2;
5433 else
5434 retcode := 0;
5435 end if;
5436
5437 -- Bug fix 3816741 ends here
5438 return;
5439
5440 end if;
5441 /*
5442 ** Validate the input parameters where appropriate.
5443 **
5444 ** For ALL_PROFILES and ALL_GLOBAL if any value has been provided for BG ID or
5445 ** SP ID we'll just ignore them.
5446 */
5447 if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') and
5448 p_security_profile_id is null then
5449 /*
5450 ** No security profile has been specified.
5451 */
5452 hr_utility.set_message(800,'PER_289776_NO_PROF_ID');
5453 hr_utility.raise_error;
5454 elsif p_generation_scope = 'ALL_BUS_GRP' and
5455 p_business_group_id is null then
5456 /*
5457 ** No business group has been specified.
5458 */
5459 hr_utility.set_message(800,'PER_289777_NO_BG_ID');
5460 hr_utility.raise_error;
5461 elsif p_generation_scope = 'SINGLE_USER' and
5462 p_user_id is null then
5463 /*
5464 ** No user has been specified. Bug 4338667.
5465 */
5466 hr_utility.set_message(800,'PER_50293_NO_USER_ID');
5467 hr_utility.raise_error;
5468 end if;
5469 -- Bug 4338667 call build_lists_for_user directly if single user option
5470 -- specified.
5471 --
5472 /* IF p_generation_scope = 'SINGLE_USER'
5473 THEN
5474 hr_utility.trace('Processing for Single User');
5475 build_lists_for_user
5476 (p_security_profile_id => p_security_profile_id
5477 ,p_user_id => l_user_id
5478 ,p_effective_date => p_effective_date);
5479 ELSE */
5480 if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
5481 hr_utility.trace('Processing for Single Profile or Single User');
5482 hr_utility.set_location(l_proc||' Single Profile or Single User ',19);
5483 hr_utility.set_location(l_proc||' call generate_lists ',20);
5484 hr_utility.set_location(l_proc||' p_use_temp_tables '||p_use_temp_tables,20);
5485
5486 -- Start changes for bug 13504049
5487 g_use_temp_table := p_use_temp_tables;
5488 g_generation_scope := p_generation_scope;
5489
5490 generate_lists(p_effective_date => p_effective_date,
5491 p_generation_scope => p_generation_scope,
5492 p_security_profile_id => l_security_profile_id,
5493 p_who_to_process => p_who_to_process,
5494 p_user_id => l_user_id,
5495 p_static_user_processing => p_static_user_processing);
5496
5497 if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
5498 hr_utility.set_location(l_proc||'Sync stage table with actual table',21);
5499 insert into per_person_list(
5500 SECURITY_PROFILE_ID,
5501 PERSON_ID,
5502 REQUEST_ID,
5503 PROGRAM_APPLICATION_ID,
5504 PROGRAM_ID,
5505 PROGRAM_UPDATE_DATE,
5506 GRANTED_USER_ID)
5507 select
5508 SECURITY_PROFILE_ID,
5509 PERSON_ID,
5510 REQUEST_ID,
5511 PROGRAM_APPLICATION_ID,
5512 PROGRAM_ID,
5513 PROGRAM_UPDATE_DATE,
5514 GRANTED_USER_ID
5515 from per_person_list_stage a
5516 where not exists (
5517 select 1
5518 from per_person_list
5519 where security_profile_id=a.security_profile_id
5520 and person_id=a.person_Id
5521 and nvl(granted_user_id,-1) = nvl(a.granted_user_id,-1)
5522 and security_profile_id = p_security_profile_id);
5523
5524 delete from per_person_list a
5525 where security_profile_id=p_security_profile_id
5526 and not exists (
5527 select 1
5528 from per_person_list_stage
5529 where person_id=a.person_Id
5530 and nvl(granted_user_id,-1) = nvl(a.granted_user_id,-1));
5531
5532 commit;
5533
5534 hr_utility.set_location(l_proc||'Synchronization completed',21);
5535 end if;
5536
5537 g_use_temp_table := 'N';
5538 -- End changes for bug 13504049
5539 else
5540 /*
5541 ** We are doing all profiles, all profiles in BG or all global profiles.
5542 ** In this case we can process by assignment using PYUGEN if HR is installed.
5543 ** If HR is shared then use the old sequential mechanism.
5544 */
5545 if hr_general.chk_product_installed(800) = 'TRUE' then
5546 hr_utility.set_location(l_proc,30);
5547 /*
5548 ** HR is fully installed so we will use PYUGEN for the person list. First
5549 ** we must generate the Org, Pos and Payroll list information...
5550 */
5551 generate_opp_lists(p_effective_date => p_effective_date,
5552 p_generation_scope => p_generation_scope,
5553 p_business_group_id => l_business_group_id);
5554 /*
5555 ** ...now submit PYUGEN to do the people bit...
5556 */
5557 hr_utility.set_location(l_proc,40);
5558
5559 l_request_id := fnd_request.submit_request(application => 'PER',
5560 program => 'MSL_PERSON_LIST',
5561 sub_request => TRUE,
5562 argument1 => 'ARCHIVE',
5563 argument2 => 'PESLM',
5564 argument3 => 'HR_PROCESS',
5565 argument4 => l_effective_date,
5566 argument5 => l_effective_date,
5567 argument6 => 'PROCESS',
5568 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
5569 argument8 => null,
5570 argument9 => null,
5571 argument10 => l_action_parameter_group_id,
5572 argument11 => 'BUSINESS_GROUP_ID='||l_business_group_id,
5573 argument12 => 'GENERATION_SCOPE='||p_generation_scope,
5574 argument13 => 'WHO_TO_PROCESS='||p_who_to_process,
5575 argument14 => chr(0));
5576
5577 /*
5578 ** Set the status of the process and then exit until the sub-requests
5579 ** have completed.
5580 */
5581 -- Bug fix 3816741
5582 -- l_request_id passed as request_data to check the status of
5583 -- MSL_PERSON_LIST concurrent program.
5584
5585 if l_request_id = 0 then
5586 errbuf := fnd_message.get;
5587 retcode := 2;
5588 else
5589 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
5590 request_data=> l_request_id );
5591 retcode := 0;
5592 end if;
5593 else
5594 /*
5595 ** HR is shared so use the old mechanism but using the new submission
5596 ** mechanism.
5597 */
5598 hr_utility.set_location(l_proc,50);
5599
5600 generate_lists(p_effective_date => p_effective_date,
5601 p_generation_scope => p_generation_scope,
5602 p_business_group_id => l_business_group_id,
5603 p_who_to_process => p_who_to_process,
5604 p_user_id => l_user_id,
5605 p_static_user_processing => p_static_user_processing);
5606
5607 end if;
5608 --end if;
5609 end if;
5610 hr_utility.set_location('Leaving : '||l_proc,100);
5611
5612 end generate_list_control;
5613 --
5614
5615 -- ----------------------------------------------------------------------------
5616 -- |---------------------< generate_list_control >-----------------------------|
5617 -- ----------------------------------------------------------------------------
5618 --
5619 -- Overloaded the procedure for bug 13504049
5620
5621 procedure generate_list_control(p_effective_date date,
5622 p_generation_scope varchar2,
5623 p_business_group_id varchar2 default null,
5624 p_security_profile_id varchar2 default null,
5625 p_who_to_process varchar2 default null,
5626 p_action_parameter_group_id varchar2 default null,
5627 p_user_id varchar2 default null,
5628 p_static_user_processing varchar2 default 'ALL_STATIC',
5629 errbuf out NOCOPY varchar2,
5630 retcode out NOCOPY number)
5631 is
5632 begin
5633 generate_list_control(
5634 p_effective_date => p_effective_date,
5635 p_generation_scope => p_generation_scope,
5636 p_business_group_id => p_business_group_id,
5637 p_security_profile_id => p_security_profile_id,
5638 p_who_to_process => p_who_to_process,
5639 p_action_parameter_group_id => p_action_parameter_group_id,
5640 p_user_id => p_user_id,
5641 p_static_user_processing => p_static_user_processing,
5642 errbuf => errbuf,
5643 retcode => retcode,
5644 p_use_temp_tables => 'N');
5645 end generate_list_control;
5646
5647 -- ----------------------------------------------------------------------------
5648 -- |---------------------< generate_list_control >-----------------------------|
5649 -- ----------------------------------------------------------------------------
5650 --
5651 -- Bug fix 3816741
5652 -- Calls the overloaded generate_list_control which has errbuf and retcode
5653 -- parameter added to return the concurrent program status.
5654
5655 procedure generate_list_control(
5656 p_effective_date date,
5657 p_generation_scope varchar2,
5658 p_business_group_id varchar2 default null,
5659 p_security_profile_id varchar2 default null,
5660 p_who_to_process varchar2,
5661 p_action_parameter_group_id varchar2,
5662 p_user_id varchar2 default null,
5663 p_static_user_processing varchar2 default 'ALL_STATIC')
5664 is
5665
5666 l_errbuf varchar2(32000);
5667 l_retcode number;
5668 begin
5669 generate_list_control(
5670 p_effective_date => p_effective_date,
5671 p_generation_scope => p_generation_scope,
5672 p_business_group_id => p_business_group_id,
5673 p_security_profile_id => p_security_profile_id,
5674 p_who_to_process => p_who_to_process,
5675 p_action_parameter_group_id => p_action_parameter_group_id,
5676 p_user_id => p_user_id,
5677 p_static_user_processing => p_static_user_processing,
5678 errbuf => l_errbuf,
5679 retcode => l_retcode,
5680 p_use_temp_tables => 'N');
5681 end generate_list_control;
5682
5683 -- Overloaded the procedure for bug 13504049
5684 -- ----------------------------------------------------------------------------
5685 -- |-------------------------< submit_security >------------------------------|
5686 -- ----------------------------------------------------------------------------
5687 --
5688 procedure submit_security(errbuf out NOCOPY varchar2,
5689 retcode out NOCOPY number,
5690 p_effective_date varchar2,
5691 p_generation_scope varchar2,
5692 p_business_group_id varchar2,
5693 p_security_profile_id varchar2,
5694 p_who_to_process varchar2,
5695 p_action_parameter_group_id varchar2,
5696 p_user_name varchar2 default null,
5697 p_static_user_processing varchar2 default 'ALL_STATIC')
5698 is
5699 begin
5700 submit_security(errbuf => errbuf
5701 ,retcode => retcode
5702 ,p_effective_date => p_effective_date
5703 ,p_generation_scope => p_generation_scope
5704 ,p_business_group_id => p_business_group_id
5705 ,p_security_profile_id => p_security_profile_id
5706 ,p_who_to_process => p_who_to_process
5707 ,p_action_parameter_group_id => p_action_parameter_group_id
5708 ,p_user_name => p_user_name
5709 ,p_static_user_processing => p_static_user_processing
5710 ,p_use_temp_tables => 'N');
5711 end;
5712
5713 procedure submit_security(errbuf out NOCOPY varchar2,
5714 retcode out NOCOPY number,
5715 p_effective_date varchar2,
5716 p_generation_scope varchar2,
5717 p_business_group_id varchar2,
5718 p_security_profile_id varchar2,
5719 p_who_to_process varchar2,
5720 p_action_parameter_group_id varchar2,
5721 p_user_name varchar2 default null,
5722 p_static_user_processing varchar2 default 'ALL_STATIC',
5723 p_use_temp_tables varchar2) --added for bug 13504049
5724 is
5725
5726 l_proc varchar2(100) := g_package||'submit_security';
5727
5728 begin
5729
5730 -- hr_utility.trace_on('F','LISTGEN');
5731
5732 hr_utility.set_location('Entering '||l_proc,10);
5733
5734 --
5735 -- Set variables used for WHO columns
5736 --
5737 p_program_id := fnd_profile.value('CONC_PROGRAM_ID');
5738 p_request_id := fnd_profile.value('CONC_REQUEST_ID');
5739 p_program_application_id := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
5740 p_update_date := trunc(sysdate);
5741
5742 -- Bug 3813908
5743 if p_action_parameter_group_id is not null then
5744 pay_core_utils.pay_action_parameter_group_id := p_action_parameter_group_id;
5745 end if;
5746
5747 generate_list_control
5748 (p_effective_date => nvl(fnd_date.canonical_to_date(p_effective_date)
5749 ,sysdate),
5750 p_generation_scope => p_generation_scope,
5751 p_business_group_id => p_business_group_id,
5752 p_security_profile_id => p_security_profile_id,
5753 p_who_to_process => p_who_to_process,
5754 p_action_parameter_group_id => p_action_parameter_group_id,
5755 p_user_id => p_user_name,
5756 -- p_user_name is a misnomer;user name shows on param but passes user id
5757 p_static_user_processing => p_static_user_processing,
5758 -- Bug 3816741. Parameters passed to get the concurrent program status.
5759 errbuf => errbuf,
5760 retcode => retcode,
5761 p_use_temp_tables => p_use_temp_tables); --Added for bug 13504049
5762
5763 hr_utility.set_location('Leaving '||l_proc,20);
5764 end;
5765 --
5766 -- ----------------------------------------------------------------------------
5767 -- |----------------------< generate_lists >----------------------------------|
5768 -- ----------------------------------------------------------------------------
5769 --
5770 procedure generate_lists(
5771 p_effective_date in date
5772 ,p_security_profile_name in varchar2 default 'ALL_SECURITY_PROFILES'
5773 ,p_business_group_mode in varchar2 default 'LOCAL'
5774 ) is
5775 --
5776 l_generation_scope varchar2(30);
5777 l_proc varchar2(72) := g_package||'generate_lists';
5778 --
5779 begin
5780 --
5781 if p_security_profile_name = 'ALL_SECURITY_PROFILES' then
5782 --
5783 l_generation_scope := 'ALL_PROFILES';
5784 --
5785 else
5786 l_generation_scope := 'SINGLE_PROF';
5787 end if;
5788 --
5789 generate_lists(p_effective_date => p_effective_date,
5790 p_generation_scope => l_generation_scope,
5791 p_security_profile_name => p_security_profile_name,
5792 p_who_to_process => 'ALL');
5793 --
5794 end generate_lists;
5795 --
5796 -- ----------------------------------------------------------------------------
5797 -- |----------------------< generate_lists >----------------------------------|
5798 -- ----------------------------------------------------------------------------
5799 --
5800 procedure generate_lists(
5801 p_effective_date in date
5802 ,p_generation_scope in varchar2
5803 ,p_business_group_id in number default null
5804 ,p_security_profile_id in number default null
5805 ,p_security_profile_name in varchar2 default null
5806 ,p_who_to_process in varchar2 default null
5807 ,p_user_id in number default null
5808 ,p_static_user_processing in varchar2 default 'ALL_STATIC'
5809 ) is
5810 --
5811 l_effective_date date;
5812 l_update_date date;
5813 l_found boolean default false;
5814 l_debug boolean := false;
5815 l_business_group_mode varchar2(30);
5816 l_proc varchar2(72) := g_package||'generate_lists';
5817 l_logging pay_action_parameters.parameter_value%type;
5818 l_process_all_users boolean default true;
5819 l_user_id number;
5820 --
5821 -- Fetch the action parameter to determine if logging
5822 -- should be switched on.
5823 cursor csr_get_action_param is
5824 select pap.parameter_value
5825 from pay_action_parameters pap
5826 where pap.parameter_name = 'LOGGING';
5827 --
5828 cursor security_profiles is
5829 select *
5830 from per_security_profiles
5831 where (((security_profile_id = p_security_profile_id or
5832 security_profile_name = p_security_profile_name)
5833 and p_generation_scope in ('SINGLE_PROF','SINGLE_USER'))
5834 or (business_group_id = p_business_group_id and
5835 p_generation_scope = 'ALL_BUS_GRP')
5836 or (business_group_id is null and
5837 p_generation_scope='ALL_GLOBAL')
5838 or (p_generation_scope = 'ALL_PROFILES'))
5839 and org_security_mode in ('NONE', 'HIER');
5840
5841 -- Start changes for bug 13504049
5842 cursor csr_get_static_users(lp_security_profile_id number) is
5843 select security_user_id
5844 from per_security_users
5845 where security_profile_id = lp_security_profile_id;
5846
5847 l_static_user_id number;
5848 -- End changes for bug 13504049
5849 --
5850 begin
5851 --
5852 -- Get the session date and the current date to avoid multiple selects.
5853 hr_utility.set_location('Entering '||l_proc, 10);
5854 --
5855 l_effective_date := trunc(p_effective_date);
5856 l_update_date := trunc(sysdate);
5857 l_user_id := p_user_id;
5858 -- Get the debug paramater
5859 open csr_get_action_param;
5860 fetch csr_get_action_param into l_logging;
5861 close csr_get_action_param;
5862 --
5863 -- If logging is set to General, enable debugging.
5864 if instr(nvl(l_logging, 'N'), 'G') <> 0 then
5865 l_debug := true;
5866 end if;
5867 -- Bug 4338667: if user name is passed then this must be running for single
5868 -- user in single security profile, so just call build_list_for_user directly.
5869 /* IF p_user_id IS NOT NULL
5870 THEN
5871 hr_utility.trace('Processing for Single User - 2');
5872 build_lists_for_user
5873 (p_security_profile_id => p_security_profile_id
5874 ,p_user_id => l_user_id
5875 ,p_effective_date => p_effective_date);
5876 END IF; */
5877 --
5878 -- Bug 4338667: if not explicitly processing for certain static users then
5879 -- process for all of them
5880 IF p_static_user_processing = 'FLAGGED_STATIC'
5881 THEN
5882 l_process_all_users := FALSE;
5883 ELSE
5884 l_process_all_users := TRUE;
5885 END IF;
5886 --
5887 hr_utility.set_location(l_proc, 20);
5888 --
5889 for sec_rec in security_profiles loop
5890
5891 -- Start chages for bug 13504049
5892 open csr_get_static_users(sec_rec.security_profile_id);
5893 fetch csr_get_static_users into l_static_user_id;
5894 close csr_get_static_users;
5895
5896 if l_static_user_id is not null then
5897 hr_utility.set_location(l_proc||' static list in use', 10);
5898 g_static_list_profile := 'Y';
5899
5900 else
5901
5902 if g_generation_scope = 'SINGLE_PROF' and g_use_temp_table = 'Y' then
5903 g_static_list_profile := 'N';
5904 insert into per_person_list_stage(
5905 SECURITY_PROFILE_ID,
5906 PERSON_ID,
5907 REQUEST_ID,
5908 PROGRAM_APPLICATION_ID,
5909 PROGRAM_ID,
5910 PROGRAM_UPDATE_DATE,
5911 GRANTED_USER_ID)
5912 select
5913 SECURITY_PROFILE_ID,
5914 PERSON_ID,
5915 REQUEST_ID,
5916 PROGRAM_APPLICATION_ID,
5917 PROGRAM_ID,
5918 PROGRAM_UPDATE_DATE,
5919 GRANTED_USER_ID
5920 from per_person_list
5921 where security_profile_id=sec_rec.security_profile_id;
5922 end if;
5923
5924 end if;
5925 -- End chages for bug 13504049
5926
5927 --
5928 -- Delete previous entries for the profile. By using this function
5929 -- we will be using the old generation mechanism so we need to clear
5930 -- the person list data upfront.
5931 clear_sp_list_table(p_generation_scope => p_generation_scope,
5932 p_business_group_id => sec_rec.business_group_id,
5933 p_security_profile_id => sec_rec.security_profile_id,
5934 p_clear_people_flag => true);
5935 --
5936 hr_utility.set_location(l_proc, 30);
5937 --
5938 -- If there are no restrictions or this profile uses user-based
5939 -- security, do not execute the inserts.
5940 if(sec_rec.view_all_employees_flag = 'N' or
5941 sec_rec.view_all_applicants_flag = 'N' or
5942 sec_rec.view_all_cwk_flag = 'N' or
5943 (sec_rec.view_all_contacts_flag = 'N' or
5944 (sec_rec.view_all_contacts_flag = 'Y' and
5945 sec_rec.view_all_candidates_flag = 'X')) or
5946 sec_rec.view_all_organizations_flag = 'N' or
5947 sec_rec.view_all_positions_flag = 'N' or
5948 sec_rec.view_all_payrolls_flag = 'N' or
5949 sec_rec.custom_restriction_flag = 'Y') then
5950 --
5951 hr_utility.set_location(l_proc, 40);
5952 --
5953 if (sec_rec.view_all_payrolls_flag = 'N') then
5954 --
5955 hr_utility.set_location(l_proc, 50);
5956 --
5957 -- Build the payroll list.
5958 build_payroll_list(sec_rec.security_profile_id,
5959 sec_rec.business_group_id,
5960 sec_rec.include_exclude_payroll_flag,
5961 l_effective_date,
5962 l_update_date);
5963 --
5964 end if; -- view_all_payrolls_flag
5965 --
5966 -- Do not insert if using user-based security.
5967 if(sec_rec.view_all_organizations_flag = 'N' and
5968 nvl(sec_rec.top_organization_method, 'S') <> 'U') then
5969 --
5970 hr_utility.set_location(l_proc, 60);
5971 -- Determine business_group mode for the current security profile
5972 if sec_rec.business_group_id is null then
5973 l_business_group_mode := 'GLOBAL';
5974 else
5975 l_business_group_mode := 'LOCAL';
5976 end if;
5977 -- Build organization list
5978 build_organization_list(sec_rec.security_profile_id,
5979 sec_rec.include_top_organization_flag,
5980 sec_rec.organization_structure_id,
5981 sec_rec.organization_id,
5982 sec_rec.exclude_business_groups_flag,
5983 l_effective_date,
5984 l_update_date,
5985 l_business_group_mode);
5986 --
5987 end if;
5988 --
5989 -- Do not insert if using user-based security.
5990 if(sec_rec.view_all_positions_flag = 'N' and
5991 nvl(sec_rec.top_organization_method, 'S') <> 'U' and
5992 nvl(sec_rec.top_position_method, 'S') <> 'U') then
5993 --
5994 hr_utility.set_location(l_proc, 70);
5995 -- Build position list
5996 build_position_list(sec_rec.security_profile_id,
5997 sec_rec.view_all_organizations_flag,
5998 sec_rec.include_top_position_flag,
5999 sec_rec.position_structure_id,
6000 sec_rec.position_id,
6001 l_effective_date,
6002 l_update_date);
6003 --
6004 end if;
6005 --
6006 -- Build person list if we have any person level restriction.
6007 if(sec_rec.view_all_employees_flag = 'N' or
6008 sec_rec.view_all_applicants_flag = 'N' or
6009 sec_rec.view_all_cwk_flag = 'N' or
6010 (sec_rec.view_all_contacts_flag = 'N' or
6011 (sec_rec.view_all_contacts_flag = 'Y' and
6012 sec_rec.view_all_candidates_flag = 'X'))) and
6013 (nvl(sec_rec.top_organization_method, 'S') <> 'U' and
6014 nvl(sec_rec.top_position_method, 'S') <> 'U' and
6015 nvl(sec_rec.custom_restriction_flag, 'N') <> 'U') then
6016 --
6017 create_person_list(sec_rec,
6018 l_effective_date,
6019 l_update_date,
6020 p_who_to_process);
6021 --
6022 end if;
6023 --
6024 -- Add person list changes.
6025 add_person_list_changes(sec_rec.security_profile_id,
6026 l_effective_date,
6027 l_update_date);
6028 --
6029 -- Build static lists for any users in the list of people to
6030 -- build static lists for.
6031 build_lists_for_users(p_sec_prof_rec => sec_rec
6032 ,p_effective_date => l_effective_date
6033 ,p_process_all_users => l_process_all_users
6034 ,p_user_id => l_user_id
6035 ,p_debug => l_debug);
6036 --
6037 end if;
6038 --
6039 -- We only populate build_contact_list if restricting by contacts.
6040 -- Otherwise there is no point in populating the lists because
6041 -- show_person handles view_all_contacts = Yes profiles.
6042 -- The contact list is also only built when user-based restrictions
6043 -- are not in use.
6044 --
6045 -- A condition with view_all_contacts_flag = All and
6046 -- view_all_candidates_flag = None, needs caching (ie: similar to
6047 -- record existing in per_person_list). The additional OR condition
6048 -- is included as part of Candidate Security enchancements.
6049 if(sec_rec.view_all_contacts_flag = 'N' or
6050 (sec_rec.view_all_contacts_flag = 'Y' and
6051 sec_rec.view_all_candidates_flag = 'X')) and
6052 (nvl(sec_rec.top_organization_method, 'S') <> 'U' and
6053 nvl(sec_rec.top_position_method, 'S') <> 'U' and
6054 nvl(sec_rec.custom_restriction_flag, 'N') <> 'U') then
6055 --
6056 build_contact_list(p_security_profile_id => sec_rec.security_profile_id,
6057 p_view_all_contacts_flag => sec_rec.view_all_contacts_flag, -- Added for bug (6376000/4774264)
6058 p_effective_date => l_effective_date,
6059 p_business_group_id => sec_rec.business_group_id);
6060 --
6061 end if;
6062 --
6063 l_found := true;
6064 --
6065 end loop;
6066 --
6067 hr_utility.set_location(l_proc, 130);
6068 --
6069 if not l_found then
6070 --
6071 hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
6072 hr_utility.set_message_token ('PROFILE_NAME', p_security_profile_name);
6073 hr_utility.raise_error;
6074 --
6075 end if;
6076 --
6077 hr_utility.set_location('Leaving '||l_proc, 140);
6078 --
6079 end generate_lists;
6080 --
6081 -- ----------------------------------------------------------------------------
6082 -- |--------------------------< range_cursor >--------------------------------|
6083 -- ----------------------------------------------------------------------------
6084 --
6085 procedure range_cursor (pactid in number,
6086 sqlstr out NOCOPY varchar2) is
6087
6088 l_proc varchar2(100) := g_package||'range_curosr';
6089 l_generation_scope varchar2(20);
6090 begin
6091 --hr_utility.trace_on('F','LISTGEN');
6092 hr_utility.set_location('Entering : '||l_proc,10);
6093 select pay_core_utils.get_parameter('GENERATION_SCOPE',
6094 pa1.legislative_parameters)
6095 into l_generation_scope
6096 from pay_payroll_actions pa1
6097 where payroll_action_id = pactid;
6098
6099 /*
6100 ** Define the SQL statement to get the people we want to process. Provide
6101 ** initial filtering based on business group if appropriate.
6102 */
6103 if l_generation_scope = 'ALL_PROFILES'
6104 or l_generation_scope = 'ALL_GLOBAL'
6105 then
6106 hr_utility.set_location(l_proc,20);
6107 sqlstr := 'select distinct per.person_id
6108 from per_all_people_f per
6109 ,pay_payroll_actions ppa
6110 where ppa.payroll_action_id = :payroll_action_id
6111 order by per.person_id';
6112 -- and ppa.effective_date between per.effective_start_date
6113 -- and per.effective_end_date
6114 else
6115 /*
6116 **scope is ALL_BUS_GRP
6117 */
6118 hr_utility.set_location(l_proc,30);
6119 sqlstr := 'select distinct per.person_id
6120 from per_all_people_f per
6121 ,pay_payroll_actions ppa
6122 where ppa.payroll_action_id = :payroll_action_id
6123 and pay_core_utils.get_parameter(''BUSINESS_GROUP_ID'',
6124 ppa.legislative_parameters) =
6125 per.business_group_id
6126 order by per.person_id';
6127 -- and ppa.effective_date between per.effective_start_date
6128 -- and per.effective_end_date
6129 end if;
6130 hr_utility.set_location('Leaving : '||l_proc,40);
6131 end range_cursor;
6132 --
6133 -- ----------------------------------------------------------------------------
6134 -- |--------------------------< action_creation >-----------------------------|
6135 -- ----------------------------------------------------------------------------
6136 --
6137 -- Purpose : This routine creates assignment actions for a specific chunk.
6138 -- Only one action is created for a single person ID. If a person
6139 -- has multiple assignments then we ignore all but the first one.
6140 -- This is so that we can process all the assignment records within
6141 -- the same chunk(and therefore thread). Later in the process we
6142 -- will get a list of all assignment IDs for a person and process
6143 -- each one of them.
6144 --
6145 -- Notes :
6146 --
6147 procedure action_creation (pactid in number,
6148 stperson in number,
6149 endperson in number,
6150 chunk in number) is
6151
6152 l_temp_person_id per_all_people_f.person_id%TYPE;
6153 l_lockingactid pay_assignment_actions.assignment_action_id%TYPE;
6154 l_business_group_id number;
6155 l_generation_scope varchar2(20);
6156 l_who_to_proc varchar2(20);
6157
6158 l_proc varchar2(100) := g_package||'action_creation';
6159
6160 /*
6161 ** Cursor to select the individual person ID's for each person in the range
6162 ** between stperson and endperson.
6163 **
6164 ** Use the emp/apl/cwk number columns to filter out contact only people
6165 ** unless they become a Emp/Apl/Cwl in the future.
6166 */
6167 cursor c_actions(pactid number,
6168 stperson number,
6169 endperson number) is
6170 select distinct ppf.person_id
6171 from per_person_type_usages_f ppf
6172 ,pay_payroll_actions ppa
6173 ,per_person_types ppt
6174 where ppf.person_id between stperson and endperson
6175 and ppa.payroll_action_id = pactid
6176 -- and ppf.person_type_id = ppt.person_type_id --commented Bug6809753
6177 and (( l_business_group_id = ppt.business_group_id
6178 and l_generation_scope = 'ALL_BUS_GRP')
6179 OR
6180 ( l_generation_scope = 'ALL_GLOBAL')
6181 OR
6182 ( l_generation_scope = 'ALL_PROFILES'))
6183 and (( l_who_to_proc in ('CURRENT','ALL')
6184 /*
6185 ** Current person today
6186 */
6187 and (( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
6188 ppa.effective_date between ppf.effective_start_date
6189 and ppf.effective_end_date
6190 and ppt.system_person_type in ('EMP','APL','CWK'))
6191 OR
6192 /*
6193 ** Future person
6194 */
6195 ( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
6196 ppa.effective_date < ppf.effective_start_date
6197 and ppt.system_person_type in ('EMP','APL','CWK'))))
6198 OR
6199 ( l_who_to_proc in ('TERM','ALL')
6200 /*
6201 ** Existed as a current person at somepoint in history
6202 */
6203 and ( ppf.person_type_id = ppt.person_type_id
6204 and ppa.effective_date > ppf.effective_start_date
6205 and ppt.system_person_type in ('EMP','APL','CWK'))
6206 /*
6207 ** ...as an ex person on the effective date
6208 */
6209 and exists (select null
6210 from per_person_type_usages_f ppf1,
6211 per_person_types ppt1
6212 where ppf1.person_id = ppf.person_id
6213 and ppa.effective_date between ppf1.effective_start_date
6214 and ppf1.effective_end_date
6215 and ppf1.person_type_id = ppt1.person_type_id
6216 and ppt1.business_group_id = ppt.business_group_id
6217 and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
6218 /*
6219 ** ...and not a current person on effective date or in
6220 ** the future.
6221 **
6222 ** Due to the implementation of PTU I can be both EMP and EX-APL
6223 ** today. i.e. I'm an employee who was successfully hired after
6224 ** some application process. In this case the person should be
6225 ** processed as a current and not an ex person. Note the
6226 ** exception for APLs who are either former EMPs/CWKs - in this
6227 ** case an APL who is also term'd should be visible as both an
6228 ** APL and as EX-EMP/EX-CWK therefore this cursor can see people
6229 ** who are EX-EMP/EX-CWK but who are also APL
6230 */
6231 and not exists (select null
6232 from per_person_type_usages_f ppf2,
6233 per_person_types ppt2
6234 where ppf2.person_id = ppf.person_id
6235 and ppa.effective_date < ppf2.effective_end_date
6236 and ppf2.person_type_id = ppt2.person_type_id
6237 and ppt2.business_group_id = ppt.business_group_id
6238 and ppt2.system_person_type in ('EMP','CWK'))));
6239
6240
6241 /*********************************************************************************************
6242 ** Bug 3464720.
6243 ** The cursor was used prior to the termination enhancement. For performance reason this will
6244 ** be used if terminated people are not selected.
6245 *********************************************************************************************/
6246 /*
6247 ** Cursor to select the assignment ID's for each person in the range
6248 ** between stperson and endperson.
6249 */
6250 cursor c_actions_prev(pactid number,
6251 stperson number,
6252 endperson number) is
6253 select distinct asg.assignment_id,
6254 asg.person_id
6255 from per_all_assignments_f asg
6256 ,pay_payroll_actions ppa
6257 where asg.assignment_type in ('E','A','C')
6258 and asg.person_id between stperson and endperson
6259 and ppa.payroll_action_id = pactid
6260 and ( pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
6261 ppa.legislative_parameters) =
6262 asg.business_group_id
6263 and pay_core_utils.get_parameter('GENERATION_SCOPE',
6264 ppa.legislative_parameters) =
6265 'ALL_BUS_GRP'
6266 OR
6267 pay_core_utils.get_parameter('GENERATION_SCOPE',
6268 ppa.legislative_parameters) =
6269 'ALL_GLOBAL'
6270 OR
6271 pay_core_utils.get_parameter('GENERATION_SCOPE',
6272 ppa.legislative_parameters) =
6273 'ALL_PROFILES')
6274 and ((ppa.effective_date between asg.effective_start_date
6275 and asg.effective_end_date)
6276 or
6277 (asg.effective_start_date > ppa.effective_date and
6278 not exists (select null
6279 from per_all_assignments_f paf1
6280 where paf1.assignment_id = asg.assignment_id
6281 and paf1.effective_start_date <
6282 ppa.effective_date)));
6283
6284
6285
6286 begin
6287
6288 l_temp_person_id := null;
6289
6290 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
6291 ppa.legislative_parameters),
6292 pay_core_utils.get_parameter('GENERATION_SCOPE',
6293 ppa.legislative_parameters),
6294 pay_core_utils.get_parameter('WHO_TO_PROCESS',
6295 ppa.legislative_parameters)
6296 into l_business_group_id, l_generation_scope, l_who_to_proc
6297 from pay_payroll_actions ppa
6298 where ppa.payroll_action_id = pactid;
6299
6300 /*********************************
6301 ** If terminated people selected
6302 **********************************/
6303 IF (l_who_to_proc in ('TERM', 'ALL'))
6304 THEN
6305
6306 for perrec in c_actions(pactid, stperson, endperson) loop
6307
6308 select pay_assignment_actions_s.nextval
6309 into l_lockingactid
6310 from dual;
6311
6312 if l_temp_person_id is null then
6313 /*
6314 ** This is the first iteration so set the temp variable
6315 ** and insert the first action record.
6316 */
6317 l_temp_person_id := perrec.person_id;
6318 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
6319 assignid => -1,
6320 pactid => pactid,
6321 chunk => chunk,
6322 greid => null,
6323 object_id => perrec.person_id,
6324 object_type => 'PER_ALL_PEOPLE_F');
6325 end if;
6326
6327 if l_temp_person_id <> perrec.person_id then
6328 /*
6329 ** The person ID has changed since last time and so we need to
6330 ** insert an action record for this assignment
6331 */
6332 l_temp_person_id := perrec.person_id;
6333 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
6334 assignid => -1,
6335 pactid => pactid,
6336 chunk => chunk,
6337 greid => null,
6338 object_id => perrec.person_id,
6339 object_type => 'PER_ALL_PEOPLE_F');
6340 end if;
6341 end loop;
6342
6343 ELSE
6344 /******************************************
6345 ** If terminated people are not selected
6346 ******************************************/
6347 for asgrec in c_actions_prev(pactid, stperson, endperson) loop
6348
6349 select pay_assignment_actions_s.nextval
6350 into l_lockingactid
6351 from dual;
6352
6353 if l_temp_person_id is null then
6354 /*
6355 ** This is the first iteration so set the temp variable
6356 ** and insert the first action record.
6357 */
6358 l_temp_person_id := asgrec.person_id;
6359
6360 -- Bug 3630537
6361 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
6362 -- archive_data needs it to process a person.
6363 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
6364 assignid => -1,
6365 pactid => pactid,
6366 chunk => chunk,
6367 greid => null,
6368 object_id => asgrec.person_id,
6369 object_type => 'PER_ALL_PEOPLE_F');
6370 end if;
6371
6372 if l_temp_person_id <> asgrec.person_id then
6373 /*
6374 ** The person ID has changed since last time and so we need to
6375 ** insert an action record for this assignment
6376 */
6377 l_temp_person_id := asgrec.person_id;
6378
6379 -- Bug 3630537
6380 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
6381 -- archive_data needs it to process a person.
6382 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
6383 assignid => -1,
6384 pactid => pactid,
6385 chunk => chunk,
6386 greid => null,
6387 object_id => asgrec.person_id,
6388 object_type => 'PER_ALL_PEOPLE_F');
6389 end if;
6390 end loop;
6391
6392 END IF;
6393
6394 end action_creation;
6395
6396 --
6397 -- ----------------------------------------------------------------------------
6398 -- |---------------------------< initialization >-----------------------------|
6399 -- ----------------------------------------------------------------------------
6400 --
6401 -- Purpose : This process is called for each slave process to perform
6402 -- standard initialization.
6403 --
6404 -- Notes :
6405 --
6406 procedure initialization(p_payroll_action_id in number)
6407 is
6408 begin
6409 --
6410 -- Set WHO column globals...
6411 --
6412 p_program_id := fnd_profile.value('CONC_PROGRAM_ID');
6413 p_request_id := fnd_profile.value('CONC_REQUEST_ID');
6414 p_program_application_id := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
6415 p_update_date := trunc(sysdate);
6416
6417 end initialization;
6418 --
6419 -- ----------------------------------------------------------------------------
6420 -- |----------------------------< archive_data >------------------------------|
6421 -- ----------------------------------------------------------------------------
6422 --
6423 -- Purpose : This process is called for each assignment action and performs the
6424 -- processing required for each individual person. We have access
6425 -- to an assignment ID but need to determine security for a person
6426 -- so convert the assignment ID into a person ID and then kickoff
6427 -- the processing for that person.
6428 --
6429 -- Notes :
6430 --
6431 procedure archive_data(p_assactid in number,
6432 p_effective_date in date) is
6433
6434 cursor c_person is
6435 select ass.object_id
6436 from pay_assignment_actions ass
6437 where ass.assignment_action_id = p_assactid;
6438
6439 l_person_id per_all_people_f.person_id%TYPE;
6440 l_business_group_id number;
6441 l_generation_scope varchar2(20);
6442 l_who_to_process varchar2(30);
6443
6444 begin
6445 --hr_utility.trace_on('F','PERSLM');
6446
6447
6448 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
6449 ppa.legislative_parameters),
6450 pay_core_utils.get_parameter('GENERATION_SCOPE',
6451 ppa.legislative_parameters),
6452 pay_core_utils.get_parameter('WHO_TO_PROCESS',
6453 ppa.legislative_parameters)
6454 into l_business_group_id, l_generation_scope, l_who_to_process
6455 from pay_payroll_actions ppa,
6456 pay_assignment_actions paa
6457 where ppa.payroll_action_id = paa.payroll_action_id
6458 and paa.assignment_action_id = p_assactid;
6459
6460 /*
6461 ** Get the person ID from the assignment action.
6462 */
6463 open c_person;
6464 fetch c_person into l_person_id;
6465 close c_person;
6466
6467 pay_pyucslis_pkg.process_person(l_person_id,
6468 p_effective_date,
6469 l_business_group_id,
6470 l_generation_scope,
6471 l_who_to_process);
6472
6473 end archive_data;
6474
6475 function chk_person_in_profile (p_person_id in number,
6476 p_security_profile_id number)
6477 return varchar2 is
6478
6479 l_dummy number;
6480 cursor c_per_in_profile is
6481 select 1
6482 from per_person_list
6483 where person_id = p_person_id
6484 and granted_user_id is null
6485 and security_profile_id = p_security_profile_id;
6486
6487 begin
6488
6489 open c_per_in_profile;
6490 fetch c_per_in_profile into l_dummy;
6491 if c_per_in_profile%FOUND then
6492 close c_per_in_profile;
6493 return 'Y';
6494 else
6495 close c_per_in_profile;
6496 return 'X';
6497 end if;
6498
6499 end;
6500 --
6501 -- --------------------------------------------------------------------------
6502 -- |------------------------< submit_cand_sec_opt >-------------------------|
6503 -- --------------------------------------------------------------------------
6504 --
6505 procedure submit_cand_sec_opt(
6506 errbuf out nocopy varchar2,
6507 retcode out nocopy number,
6508 p_profile_option in varchar2
6509 ) is
6510 --
6511 -- Local variables
6512 l_proc varchar2(72):= g_package||'submit_cand_sec_opt';
6513 l_prog_id number(15) := fnd_profile.value('CONC_PROGRAM_ID');
6514 l_req_id number(15) := fnd_profile.value('CONC_REQUEST_ID');
6515 l_appl_id number(15) := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
6516 l_upd_date date := trunc(sysdate);
6517 l_sec_cnt number := 1;
6518 --
6519 -- Exception variables
6520 e_irec_not_installed exception;
6521 --
6522 -- Record Type declaration
6523 type sec_rec is record (
6524 security_profile_id varchar2(15),
6525 security_profile_name varchar2(240)
6526 );
6527 --
6528 -- Table type declaration
6529 type report_rec is table of sec_rec index by binary_integer;
6530 --
6531 -- Security profile records not processed due to the
6532 -- unavailability of lock.
6533 sec_not_processed report_rec;
6534 --
6535 -- Get all security profiles excluding the view_all profiles.
6536 cursor csr_security_prof is
6537 select pspv.*
6538 from per_security_profiles_v pspv
6539 where pspv.view_all_flag = 'N'
6540 and pspv.view_all_candidates_flag <> p_profile_option;
6541 --
6542 begin
6543 --
6544 hr_utility.set_location('Entering '||l_proc, 10);
6545 --
6546 -- Checking that whether iRecruitment is installed.
6547 if nvl(fnd_profile.value('IRC_INSTALLED_FLAG'), 'N') = 'N' then
6548 raise e_irec_not_installed;
6549 end if;
6550 --
6551 hr_utility.set_location(l_proc, 20);
6552 -- Assume that, iRecruitment is installed, hence continuing the process.
6553 for r_sec in csr_security_prof loop
6554 --
6555 begin
6556 --
6557 hr_utility.set_location(l_proc, 30);
6558 -- Get the lock of the respective security profile record.
6559 per_security_profiles_pkg.lock_row(
6560 x_rowid => r_sec.row_id,
6561 x_security_profile_id => r_sec.security_profile_id,
6562 x_business_group_id => r_sec.business_group_id,
6563 x_position_id => r_sec.position_id,
6564 x_organization_id => r_sec.organization_id,
6565 x_position_structure_id => r_sec.position_structure_id,
6566 x_organization_structure_id => r_sec.organization_structure_id,
6567 x_include_top_org_flag => rtrim(r_sec.include_top_organization_flag),
6568 x_include_top_position_flag => rtrim(r_sec.include_top_position_flag),
6569 x_security_profile_name => rtrim(r_sec.security_profile_name),
6570 x_view_all_applicants_flag => rtrim(r_sec.view_all_applicants_flag),
6571 x_view_all_employees_flag => rtrim(r_sec.view_all_employees_flag),
6572 x_view_all_flag => rtrim(r_sec.view_all_flag),
6573 x_view_all_organizations_flag => rtrim(r_sec.view_all_organizations_flag),
6574 x_view_all_payrolls_flag => rtrim(r_sec.view_all_payrolls_flag),
6575 x_view_all_positions_flag => rtrim(r_sec.view_all_positions_flag),
6576 x_view_all_cwk_flag => rtrim(r_sec.view_all_cwk_flag),
6577 x_view_all_contacts_flag => rtrim(r_sec.view_all_contacts_flag),
6578 x_view_all_candidates_flag => rtrim(r_sec.view_all_candidates_flag),
6579 x_include_exclude_payroll_flag => rtrim(r_sec.include_exclude_payroll_flag),
6580 x_reporting_oracle_username => rtrim(r_sec.reporting_oracle_username),
6581 x_allow_granted_users_flag => rtrim(r_sec.allow_granted_users_flag),
6582 x_restrict_by_supervisor_flag => rtrim(r_sec.restrict_by_supervisor_flag),
6583 x_supervisor_levels => r_sec.supervisor_levels,
6584 x_exclude_secondary_asgs_flag => rtrim(r_sec.exclude_secondary_asgs_flag),
6585 x_exclude_person_flag => rtrim(r_sec.exclude_person_flag),
6586 x_named_person_id => r_sec.named_person_id,
6587 x_custom_restriction_flag => rtrim(r_sec.custom_restriction_flag),
6588 x_restriction_text => rtrim(r_sec.restriction_text),
6589 x_exclude_business_groups_flag => rtrim(r_sec.exclude_business_groups_flag),
6590 x_org_security_mode => rtrim(r_sec.org_security_mode),
6591 x_restrict_on_individual_asg => rtrim(r_sec.restrict_on_individual_asg),
6592 x_top_organization_method => rtrim(r_sec.top_organization_method),
6593 x_top_position_method => rtrim(r_sec.top_position_method)
6594 );
6595 --
6596 hr_utility.set_location(l_proc, 40);
6597 -- Sucessfully locked the row, now updating the
6598 -- view_all_candidates_flag with the given value through parameter
6599 -- p_profile_option
6600 per_security_profiles_pkg.update_row(
6601 x_rowid => r_sec.row_id,
6602 x_security_profile_id => r_sec.security_profile_id,
6603 x_business_group_id => r_sec.business_group_id,
6604 x_position_id => r_sec.position_id,
6605 x_organization_id => r_sec.organization_id,
6606 x_position_structure_id => r_sec.position_structure_id,
6607 x_organization_structure_id => r_sec.organization_structure_id,
6608 x_include_top_org_flag => r_sec.include_top_organization_flag,
6609 x_include_top_position_flag => r_sec.include_top_position_flag,
6610 x_security_profile_name => r_sec.security_profile_name,
6611 x_view_all_applicants_flag => r_sec.view_all_applicants_flag,
6612 x_view_all_employees_flag => r_sec.view_all_employees_flag,
6613 x_view_all_flag => r_sec.view_all_flag,
6614 x_view_all_organizations_flag => r_sec.view_all_organizations_flag,
6615 x_view_all_payrolls_flag => r_sec.view_all_payrolls_flag,
6616 x_view_all_positions_flag => r_sec.view_all_positions_flag,
6617 x_view_all_cwk_flag => r_sec.view_all_cwk_flag,
6618 x_view_all_contacts_flag => r_sec.view_all_contacts_flag,
6619 x_view_all_candidates_flag => p_profile_option,
6620 x_include_exclude_payroll_flag => r_sec.include_exclude_payroll_flag,
6621 x_reporting_oracle_username => r_sec.reporting_oracle_username,
6622 x_allow_granted_users_flag => r_sec.allow_granted_users_flag,
6623 x_restrict_by_supervisor_flag => r_sec.restrict_by_supervisor_flag,
6624 x_supervisor_levels => r_sec.supervisor_levels,
6625 x_exclude_secondary_asgs_flag => r_sec.exclude_secondary_asgs_flag,
6626 x_exclude_person_flag => r_sec.exclude_person_flag,
6627 x_named_person_id => r_sec.named_person_id,
6628 x_custom_restriction_flag => r_sec.custom_restriction_flag,
6629 x_restriction_text => r_sec.restriction_text,
6630 x_exclude_business_groups_flag => r_sec.exclude_business_groups_flag,
6631 x_org_security_mode => r_sec.org_security_mode,
6632 x_restrict_on_individual_asg => r_sec.restrict_on_individual_asg,
6633 x_top_organization_method => r_sec.top_organization_method,
6634 x_top_position_method => r_sec.top_position_method,
6635 x_request_id => l_req_id,
6636 x_program_application_id => l_appl_id,
6637 x_program_id => l_prog_id,
6638 x_program_update_date => l_upd_date
6639 );
6640 --
6641 hr_utility.set_location('Sec Prof Id:'||r_sec.security_profile_id, 50);
6642 hr_utility.set_location('Sec Name:'||r_sec.security_profile_name, 55);
6643 hr_utility.set_location('BG Id:'||r_sec.business_group_id, 60);
6644 --
6645 exception
6646 --
6647 -- Could not obtain the lock.
6648 when others then
6649 --
6650 -- Keeping the failed record details into a PL/SQL cache. This will
6651 -- be shown to customer in a report format in concurrent log after
6652 -- the warning (translated) message.
6653 sec_not_processed(l_sec_cnt).security_profile_id
6654 := r_sec.security_profile_id;
6655 sec_not_processed(l_sec_cnt).security_profile_name
6656 := r_sec.security_profile_name;
6657 l_sec_cnt := l_sec_cnt + 1;
6658 --
6659 -- Keeping the information traced.
6660 hr_utility.trace('Cannot process security profile :');
6661 hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662 hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663 hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664 hr_utility.trace('BG Name: '||r_sec.business_group_name);
6665 --
6666 --
6667 end;
6668 --
6669 end loop;
6670 --
6671 hr_utility.set_location(l_proc, 70);
6672 -- Needs to format the report of failed records (if any) after the
6673 -- warning message.
6674 if sec_not_processed.count > 0 then
6675 --
6676 hr_utility.set_location(l_proc, 80);
6677 -- Setting the message to get the translated message text
6678 fnd_message.set_name('PER', 'PER_449705_SEC_UPDATE_FAILED');
6679 --
6680 errbuf := null;
6681 errbuf := nvl(fnd_message.get, 'PER_449705_SEC_UPDATE_FAILED');
6682 retcode := 1; -- Concurrent process finished with a warning.
6683 --
6684 -- Looping through the PL/SQL cache and writing to the concurrent
6685 -- log file.
6686 for i in sec_not_processed.first..sec_not_processed.last loop
6687 --
6688 fnd_file.put_line(fnd_file.log,
6689 sec_not_processed(i).security_profile_name||'('||
6690 sec_not_processed(i).security_profile_id||')');
6691 --
6692 end loop;
6693 --
6694 end if;
6695 --
6696 hr_utility.set_location('Leaving '||l_proc, 99);
6697 --
6698 exception
6699 --
6700 when e_irec_not_installed then
6701 --
6702 -- Setting the message to get the translated message text
6703 fnd_message.set_name('PER', 'PER_449706_IRC_NOT_INSTALLED');
6704 --
6705 errbuf := null;
6706 errbuf := nvl(fnd_message.get, 'PER_449706_IRC_NOT_INSTALLED');
6707 retcode := 1; -- Concurrent process finished with a warning.
6708 --
6709 hr_utility.set_location('Leaving '||l_proc, 99);
6710 --
6711 end submit_cand_sec_opt;
6712 --
6713 END pay_pyucslis_pkg;
[Home] [Help]