[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into per_person_list(security_profile_id, person_id, request_id,
program_application_id, program_id,
program_update_date)
select /*+ USE_NL(PSP) */
distinct ppl.security_profile_id, pcr.contact_person_id,
l_req_id, l_appl_id, l_prog_id, l_upd_date
from per_contact_relationships pcr,
per_person_list ppl,
per_security_profiles psp
where ppl.person_id = p_person_id
and ppl.security_profile_id = psp.security_profile_id
and (psp.view_all_contacts_flag = 'N' or
(psp.view_all_contacts_flag = 'Y' and
psp.view_all_candidates_flag = 'X'))
and (nvl(psp.top_organization_method, 'S') <> 'U' and
nvl(psp.top_position_method, 'S') <> 'U' and
nvl(psp.custom_restriction_flag, 'N') <> 'U')
and ((psp.business_group_id = p_business_group_id and
p_generation_scope = 'ALL_BUS_GRP') or
(psp.business_group_id is null and
p_generation_scope = 'ALL_GLOBAL') or
p_generation_scope = 'ALL_PROFILES')
and pcr.person_id = ppl.person_id
and not exists
(select /*+ NO_MERGE */ null
from per_all_assignments_f asg
where asg.person_id = pcr.contact_person_id
and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
and not exists
(select /*+ NO_MERGE */ null
from per_person_list ppl1
where ppl1.person_id = pcr.contact_person_id
and ppl1.granted_user_id is null
and ppl1.security_profile_id = ppl.security_profile_id);
select distinct
papf.person_id,
papf.business_group_id
from per_all_people_f papf
where not exists
(select null
from per_all_assignments_f asg
where asg.person_id = papf.person_id)
and not exists
(select null
from per_contact_relationships pcr
where pcr.contact_person_id = papf.person_id)
and ((p_generation_scope = 'ALL_BUS_GRP' and
papf.business_group_id = p_business_group_id) or
p_generation_scope <> 'ALL_BUS_GRP')
and not exists
(select null
from per_person_type_usages_f ptuf,
per_person_types ppt
where ppt.system_person_type = 'IRC_REG_USER'
and ptuf.person_type_id = ppt.person_type_id
and ptuf.person_id = papf.person_id);
select psp.security_profile_id,
psp.business_group_id
from per_security_profiles psp
where (psp.view_all_contacts_flag = 'N' or
(psp.view_all_contacts_flag = 'Y' and
psp.view_all_candidates_flag = 'X'))
and (nvl(psp.top_organization_method, 'S') <> 'U' and
nvl(psp.top_position_method, 'S') <> 'U' and
nvl(psp.custom_restriction_flag, 'N') <> 'U')
and psp.business_group_id = p_business_group_id;
select psp.security_profile_id,
psp.business_group_id
from per_security_profiles psp
where (psp.view_all_contacts_flag = 'N' or
(psp.view_all_contacts_flag = 'Y' and
psp.view_all_candidates_flag = 'X'))
and (nvl(psp.top_organization_method, 'S') <> 'U' and
nvl(psp.top_position_method, 'S') <> 'U' and
nvl(psp.custom_restriction_flag, 'N') <> 'U')
and ((p_generation_scope = 'ALL_GLOBAL' and
psp.business_group_id is null) or
p_generation_scope = 'ALL_PROFILES');
select 'X' into t_varchar
from per_person_list p2
where p2.person_id = l_per_tbl(j)
and p2.security_profile_id = l_sp_tbl(i);
insert into per_person_list(security_profile_id, person_id,
request_id, program_application_id,
program_id, program_update_date)
values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
l_appl_id, l_prog_id, l_upd_date);
delete_old_person_list_changes
DESCRIPTION
Delete entries in the person list changes table which are no longer
required because they are currently employed.
PARAMETERS
l_effective_date - date at which we are running.
========================================================================== */
--
PROCEDURE delete_old_person_list_changes (l_effective_date DATE)
IS
BEGIN
--
hr_utility.set_location('hr_listgen.delete_old_person_list_changes',10);
hr_utility.set_location('hr_listgen.delete_old_person_list_changes',20);
END delete_old_person_list_changes;
Insert payroll list entries for the current security profile based on
the secured payroll table per_security_payrolls generated by the define
security profile form. If the include_exclude option in the security
profile is set to 'I' then the specified payrolls are copied to the payroll
list. If the include_exclude flag is 'E' then all other payrolls for
the business group are inserted into the list.
PARAMETERS
l_security_profile_id - identifier of the current security profile
l_business_group_id - business group of the security profile.
l_include_exclude_payroll_flag - include/exclude option of security profile
l_effective_date - date at which the lists are generated
l_update_date - today's date.
========================================================================== */
PROCEDURE build_payroll_list (l_security_profile_id NUMBER,
l_business_group_id NUMBER,
l_include_exclude_payroll_flag VARCHAR2,
l_effective_date DATE,
l_update_date DATE)
IS
BEGIN
--
IF (l_include_exclude_payroll_flag = 'I') THEN
--
hr_utility.set_location('hr_listgen.build_payroll_list', 10);
INSERT INTO pay_payroll_list
(payroll_id,
security_profile_id,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT pay.payroll_id,
l_security_profile_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM pay_all_payrolls_f pay,
pay_security_payrolls sec
WHERE sec.security_profile_id = l_security_profile_id
AND sec.payroll_id = pay.payroll_id
AND l_effective_date
BETWEEN pay.effective_start_date
AND pay.effective_end_date;
INSERT INTO pay_payroll_list
(payroll_id,
security_profile_id,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT pay.payroll_id,
l_security_profile_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM pay_all_payrolls_f pay
WHERE l_effective_date
BETWEEN pay.effective_start_date
AND pay.effective_end_date
AND pay.business_group_id + 0 = l_business_group_id
AND NOT EXISTS
(SELECT NULL
FROM pay_security_payrolls sec
WHERE sec.security_profile_id = l_security_profile_id
AND sec.payroll_id = pay.payroll_id) ;
Insert values into the organization list for the security profile.
Starting with the organization specified a tree walk of the organization
structure element table per_org_structure_elements takes place and
all organization below that specified are inserted into the organization
list. If the include_top_org option is specified then that organisation
is explicitly inserted into the list. The business group is
inserted into the organisation list if not previously inserted.
PARAMETERS
l_security_profile_id - identifier of the current security profile
l_include_top_org_flag - include/exclude top organization option
l_organization_structure_id - identifier of the organization structure
to be used.
l_organization_id - top organization to consider within the
organization structure
l_exclude_business_groups_flag - include/exclude all business groups when
running in global mode
l_effective_date - effective date of the run to pick the
structure version.
l_update_date - todays date.
p_business_group_mode - LOCAL/GLOBAL depends on type of security
profile.
========================================================================== */
--
PROCEDURE build_organization_list (
l_security_profile_id NUMBER,
l_include_top_org_flag VARCHAR2,
l_organization_structure_id NUMBER,
l_organization_id NUMBER,
l_exclude_business_groups_flag VARCHAR2,
l_effective_date DATE,
l_update_date DATE,
p_business_group_mode VARCHAR2) IS
--
l_proc varchar2(100) := 'pay_pyucslis_pkg.build_organization_list';
INSERT INTO per_organization_list
(security_profile_id,
organization_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
o.organization_id_child,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM per_org_structure_elements o
CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
AND o.org_structure_version_id = PRIOR o.org_structure_version_id
START WITH o.organization_id_parent = l_organization_id
AND o.org_structure_version_id =
(SELECT v.org_structure_version_id
FROM per_org_structure_versions v
WHERE v.organization_structure_id = l_organization_structure_id
AND l_effective_date BETWEEN v.date_from
AND NVL(v.date_to, TO_DATE('31-12-4712','dd-mm-yyyy')));
INSERT INTO per_organization_list
(security_profile_id,
request_id,
program_id,
program_application_id,
program_update_date,
organization_id)
SELECT l_security_profile_id,
p_request_id,
p_program_id,
p_program_application_id,
l_update_date,
pso.organization_id
FROM per_security_organizations pso
WHERE pso.entry_type = 'I'
AND pso.security_profile_id = l_security_profile_id
AND NOT EXISTS
(SELECT NULL
FROM per_organization_list pol
WHERE pol.security_profile_id = l_security_profile_id
AND pol.organization_id = pso.organization_id);
INSERT INTO per_organization_list
(security_profile_id,
organization_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
l_organization_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM DUAL
/* Duplicate check. Required because of organization list Includes */
WHERE NOT EXISTS
(SELECT NULL
FROM per_organization_list pol
WHERE pol.security_profile_id = l_security_profile_id
AND pol.user_id IS NULL
AND pol.organization_id = l_organization_id);
INSERT INTO per_organization_list
(security_profile_id,
organization_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT DISTINCT
l_security_profile_id,
org.business_group_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM hr_all_organization_units org
, per_organization_list lst
WHERE lst.security_profile_id = l_security_profile_id
AND lst.organization_id=org.organization_id
AND NOT EXISTS
(SELECT 1
FROM per_organization_list lst2
WHERE lst2.organization_id = org.business_group_id
AND lst2.user_id IS NULL
AND lst2.security_profile_id = l_security_profile_id);
INSERT INTO per_organization_list
(security_profile_id,
organization_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
s.business_group_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM per_security_profiles s
WHERE s.security_profile_id = l_security_profile_id
AND NOT EXISTS
(SELECT NULL
FROM per_organization_list b
WHERE b.organization_id = s.business_group_id
AND b.user_id IS NULL
AND b.security_profile_id = l_security_profile_id);
DELETE
FROM per_organization_list
WHERE security_profile_id = l_security_profile_id
AND user_id IS NULL
AND organization_id IN
(SELECT organization_id
FROM per_security_organizations
WHERE security_profile_id = l_security_profile_id
AND entry_type = 'E');
DELETE
FROM per_organization_list pol
WHERE pol.security_profile_id = l_security_profile_id
AND pol.user_id IS NULL
AND pol.organization_id IN
(SELECT org.business_group_id
FROM hr_all_organization_units org
WHERE org.organization_id = pol.organization_id
AND org.organization_id = org.business_group_id);
Insert values into the position list for the security profile.
A tree walk of the position structure table takes place starting with
the top position specified. If the 'all_organisations' option is
specified then a row is inserted for each position in the structure
below the top position. If 'all_organizations' is not specified then
rows are only inserted if the position encountered exists in an
organization in the organization list for the security profile. If
the 'include top position' option is specified then the position is
explictly inserted into the position list.
PARAMETERS
l_security_profile_id - identifier of the current security profile.
l_view_all_organizations_flag - all organizations option
l_include_top_position_flag - include/exclude top position option
l_position_structure_id - position structure to be used.
l_position_id - top position in the position structure
to be used.
l_effective_date - effective_date of the run at which to
pick the version.
l_update_date - today's date.
========================================================================== */
--
PROCEDURE build_position_list (l_security_profile_id NUMBER,
l_view_all_organizations_flag VARCHAR2,
l_include_top_position_flag VARCHAR2,
l_position_structure_id NUMBER,
l_position_id NUMBER,
l_effective_date DATE,
l_update_date DATE)
IS
BEGIN
--
IF (l_view_all_organizations_flag = 'N') THEN
--
hr_utility.set_location('hr_listgen.build_position_list', 10);
INSERT INTO per_position_list
(security_profile_id,
position_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
p.subordinate_position_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM per_pos_structure_elements p
WHERE EXISTS
(SELECT NULL
FROM hr_all_positions_f pp,
per_organization_list ol
WHERE ol.organization_id = pp.organization_id
AND pp.position_id = p.subordinate_position_id
AND ol.security_profile_id= l_security_profile_id)
START WITH p.parent_position_id = l_position_id
AND p.pos_structure_version_id =
(SELECT v.pos_structure_version_id
FROM per_pos_structure_versions v
WHERE v.position_structure_id = l_position_structure_id
AND l_effective_date
BETWEEN v.date_from
AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
INSERT INTO per_position_list
(security_profile_id,
position_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
l_position_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM sys.dual
WHERE EXISTS
(SELECT NULL
FROM hr_all_positions_f pp,
per_organization_list ol
WHERE ol.organization_id = pp.organization_id
AND pp.position_id = l_position_id
AND ol.security_profile_id= l_security_profile_id);
INSERT INTO per_position_list
(security_profile_id,
position_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT l_security_profile_id,
p.subordinate_position_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM per_pos_structure_elements p
START WITH p.parent_position_id = l_position_id
AND p.pos_structure_version_id =
(SELECT v.pos_structure_version_id
FROM per_pos_structure_versions v
WHERE v.position_structure_id = l_position_structure_id
AND l_effective_date
BETWEEN v.date_from
AND NVL(v.date_to, to_date('31-12-4712','dd-mm-yyyy')))
CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id;
INSERT INTO per_position_list
(security_profile_id,
position_id,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES (l_security_profile_id,
l_position_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date);
Insert contacts into the person list for the security profile.
PARAMETERS
p_security_profile_id - security profile identifier
p_effective_date - date at which the lists are generated
p_business_group_id - business group ID from the security profile.
If it's null(global profile) include contacts
from all BGs. Otherwise just for the profiles
business group.
========================================================================== */
--
procedure build_contact_list(
p_security_profile_id number,
p_view_all_contacts_flag varchar2, -- Added for bug (6376000/4774264)
p_effective_date date,
p_business_group_id number
) is
--
l_proc varchar2(72):= g_package||'build_contact_list';
-- In this case we need to Insert the contact records for,
-- 1) Related contacts - If the person/Employee is visible then Only Insert
-- contacts related to the Person/Employee.
-- --> Query #2 will populate these records.
-- 2) Unrelated Contacts - Insert all Unrelated i.e which is not belong to any
-- Person/Record in system.
-- --> Query #3 will populate these records.
-- 3) View All contacts = Yes --> Insert all reacords which are related to the
-- Person/Employee in the system but not populated because of the Security Profile setup like
-- Employee = Restricted.
-- --> Query #1 will populate these records.
-- Query #1
insert into per_person_list(security_profile_id, request_id, program_id
,program_application_id, program_update_date
,person_id)
select distinct p_security_profile_id, l_req_id, l_prog_id,
l_appl_id, l_upd_date, pcr.contact_person_id
from per_contact_relationships pcr,
per_all_people_f ppl -- per_person_list ppl for bug (6376000/4774264)
where ppl.person_id = pcr.person_id
and (pcr.business_group_id = p_business_group_id or
p_business_group_id is null)
-- and ppl.security_profile_id = p_security_profile_id for bug (6376000/4774264)
and not exists
(select null
from per_all_assignments_f asg
where asg.person_id = pcr.contact_person_id
and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
and not exists
(select null
from per_person_list ppl1
where ppl1.person_id = pcr.contact_person_id
and ppl1.granted_user_id is null
and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
insert into per_person_list(security_profile_id, request_id, program_id
,program_application_id, program_update_date
,person_id)
select distinct ppl.security_profile_id, l_req_id, l_prog_id,
l_appl_id, l_upd_date, pcr.contact_person_id
from per_contact_relationships pcr,
per_person_list ppl
where ppl.person_id = pcr.person_id
and (pcr.business_group_id = p_business_group_id or
p_business_group_id is null)
and ppl.security_profile_id = p_security_profile_id
and not exists
(select null
from per_all_assignments_f asg
where asg.person_id = pcr.contact_person_id
and asg.ASSIGNMENT_TYPE <> 'B') -- Bug 4450149
and not exists
(select null
from per_person_list ppl1
where ppl1.person_id = pcr.contact_person_id
and ppl1.granted_user_id is null
and ppl1.security_profile_id = ppl.security_profile_id);
insert into per_person_list(security_profile_id, request_id,
program_application_id, program_id,
program_update_date, person_id)
select distinct psp.security_profile_id, l_req_id, l_appl_id,
l_prog_id, l_upd_date, papf.person_id
from per_all_people_f papf,
per_security_profiles psp
where psp.security_profile_id = p_security_profile_id
and (psp.business_group_id = papf.business_group_id or
psp.business_group_id is null)
and not exists
(select null
from per_all_assignments_f asg
where asg.person_id = papf.person_id)
and not exists ---- Rever Commneted for for bug 4774264
(select null
from per_contact_relationships pcr
where pcr.contact_person_id = papf.person_id)
and not exists
(select null
from per_person_type_usages_f ptuf,
per_person_types ppt
where ppt.system_person_type = 'IRC_REG_USER'
and ptuf.person_type_id = ppt.person_type_id
and ptuf.person_id = papf.person_id)
and not exists
(select null
from per_person_list ppl
where ppl.person_id = papf.person_id
and ppl.granted_user_id is null
and ppl.security_profile_id = psp.security_profile_id);
Insert additional person list entries for persons in the person list
changes table. If an entry exists for the security profile in the
person list changes table and there is not an entry already for that
person in the person list then a row is inserted. Only persons who
have a termination date before the effective date and who do
not have a current period of service (at effective date) are added. As
'B' assignments are created on termination we need to exclude these
assignments from consideration.
PARAMETERS
l_security_profile_id - identifier of the current security profile.
l_effective_date - date for which the secure lists are generated.
l_update_date - today's date.
========================================================================= */
--
PROCEDURE add_person_list_changes (l_security_profile_id NUMBER,
l_effective_date DATE,
l_update_date DATE)
IS
BEGIN
--
hr_utility.set_location('hr_listgen.add_person_list_changes',10);
INSERT INTO per_person_list
(security_profile_id,
person_id,
request_id,
program_application_id,
program_id,
program_update_date )
SELECT DISTINCT l_security_profile_id,
plc.person_id,
p_request_id,
p_program_application_id,
p_program_id,
l_update_date
FROM per_person_list_changes plc
WHERE plc.security_profile_id = l_security_profile_id
AND NOT EXISTS
(SELECT NULL
FROM per_all_assignments_f pos
WHERE pos.person_id = plc.person_id
AND pos.assignment_type <> 'B'
AND l_effective_date
BETWEEN pos.effective_start_date
AND pos.effective_end_date)
AND EXISTS
(SELECT NULL
FROM per_all_assignments_f pos
WHERE pos.person_id = plc.person_id
AND l_effective_date > pos.effective_start_date)
AND NOT EXISTS
(SELECT NULL
FROM per_person_list ppl
WHERE ppl.person_id = plc.person_id
AND ppl.granted_user_Id IS NULL
AND ppl.security_profile_id = plc.security_profile_id);
p_update_date date,
p_who_to_process varchar2)
IS
l_select_text varchar2(500);
,p_update_date IN DATE
-- ,p_from_clause IN VARCHAR2
--- ,p_generation_scope IN VARCHAR2
-- ,p_business_group_id IN NUMBER
-- ,p_assignment_type IN VARCHAR2
,p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE
)
AS
BEGIN
hr_utility.set_location('Entering init_statement',10);
'INSERT into per_person_list
(security_profile_id,
person_id,
request_id,
program_application_id,
program_id,
program_update_date ) ');
fnd_dsql.add_text( ' SELECT DISTINCT ');
fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
/*-------- additional select clause ---------------*/
if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
(p_sec_rec.view_all_applicants_flag = 'N'
and (p_sec_rec.view_all_employees_flag <>'Y'
or p_sec_rec.view_all_cwk_flag <>'Y')) then
fnd_dsql.add_text(', per_all_people_f PERSON ');
/*------------------ end additional select clause -----------------*/
/*-------------- start where clause -------------------*/
fnd_dsql.add_text(' Where ');
(SELECT NULL
FROM per_all_people_f papf1
WHERE papf1.person_id = PERSON.person_id
AND papf1.effective_start_date < PERSON.effective_start_date)) ');
,p_update_date => p_update_date
-- ,p_from_clause => p_from_clause
-- ,p_generation_scope => p_generation_scope
-- ,p_business_group_id => p_business_group_id
-- ,p_assignment_type => p_assignment_type
,p_sec_rec => sec_rec
);
fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
FROM per_all_assignments_f pos1
WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
(SELECT NULL
FROM per_all_assignments_f pos1
WHERE pos1.person_id = ASSIGNMENT.person_id
AND ((pos1.assignment_type=''E'' and
pos1.period_of_service_id=ASSIGNMENT.period_of_service_id) or
(pos1.assignment_type=''A'' and
pos1.application_id=ASSIGNMENT.application_id) or
(pos1.assignment_type=''C'' and
pos1.period_of_placement_date_start =
ASSIGNMENT.period_of_placement_date_start))
AND pos1.effective_start_date< ASSIGNMENT.effective_start_date)
or (ASSIGNMENT.effective_end_date < ');
fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f asg
where asg.person_id = assignment.person_id
and asg.assignment_type in(''A'',''C'',''E'')
) ');
(SELECT NULL
FROM per_all_assignments_f papf
WHERE papf.person_Id = ASSIGNMENT.person_id
AND papf.assignment_type in(''A'',''C'',''E'')
AND papf.effective_end_date >= ');
fnd_dsql.add_text(' and not exists(select 1
from per_person_list ppl
where ppl.security_profile_id = ');
hr_utility.trace('select '||to_char(length(l_select_text)));
l_del_str := ' delete from per_person_list ppl where
ppl.person_id = '||p_person_id||'
and ppl.granted_user_id is null and exists
(select ''X'' from per_security_profiles pspf
where pspf.security_profile_id = ppl.security_profile_id ';
delete from per_person_list ppl where
ppl.person_id = p_person_id
and ppl.granted_user_id is null and exists
(select 'X' from per_security_profiles pspf
where pspf.security_profile_id = ppl.security_profile_id
and pspf.business_group_id = p_business_group_id);
delete from per_person_list ppl where
ppl.person_id = p_person_id
and ppl.granted_user_id is null and exists
(select 'X' from per_security_profiles pspf
where pspf.security_profile_id = ppl.security_profile_id
and pspf.business_group_id is null);
delete from per_person_list ppl where
ppl.person_id = p_person_id
and ppl.granted_user_id is null and exists
(select 'X' from per_security_profiles pspf
where pspf.security_profile_id = ppl.security_profile_id);
delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = 'N' or
(pspf.view_all_contacts_flag = 'Y' and
pspf.view_all_candidates_flag = 'X'))
and pspf.business_group_id = p_business_group_id
)
and ppl.person_id in (
select pcr.contact_person_id
from per_contact_relationships pcr,
per_person_type_usages_f ptu,
per_person_types ppt
where pcr.person_id = p_person_id
and pcr.contact_person_id = ptu.person_id
and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'OTHER')
and ppl.granted_user_id is null;
delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = 'N' or
(pspf.view_all_contacts_flag = 'Y' and
pspf.view_all_candidates_flag = 'X'))
and pspf.business_group_id is null
)
and ppl.person_id in (
select pcr.contact_person_id
from per_contact_relationships pcr,
per_person_type_usages_f ptu,
per_person_types ppt
where pcr.person_id = p_person_id
and pcr.contact_person_id = ptu.person_id
and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'OTHER')
and ppl.granted_user_id is null;
delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = 'N' or
(pspf.view_all_contacts_flag = 'Y' and
pspf.view_all_candidates_flag = 'X'))
)
and ppl.person_id in (
select pcr.contact_person_id
from per_contact_relationships pcr,
per_person_type_usages_f ptu,
per_person_types ppt
where pcr.person_id = p_person_id
and pcr.contact_person_id = ptu.person_id
and to_date(to_char(p_effective_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'OTHER')
and ppl.granted_user_id is null;
l_del_str := ' delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = ''N'' or
(pspf.view_all_contacts_flag = ''Y'' and
pspf.view_all_candidates_flag = ''X'')) '
||l_sub_str||')
and ppl.person_id in (
select pcr.contact_person_id
from per_contact_relationships pcr,
per_person_type_usages_f ptu,
per_person_types ppt
where pcr.person_id = '||p_person_id||
' and pcr.contact_person_id = ptu.person_id
and to_date('''||to_char(p_effective_date,
'dd/mm/yyyy')||''', ''dd/mm/yyyy'')
between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = ''OTHER'')
and ppl.granted_user_id is null';
delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = 'N' or
(pspf.view_all_contacts_flag = 'Y' and
pspf.view_all_candidates_flag = 'X')))
/* and ppl.person_id in
(select papf.person_id
from per_all_people_f papf
where papf.person_id = ppl.person_id
and ((p_generation_scope = 'ALL_BUS_GRP' and
papf.business_group_id = p_business_group_id) or
p_generation_scope <> 'ALL_BUS_GRP')) */
and not exists
(select null
from per_all_assignments_f asg
where asg.person_id = ppl.person_id)
and not exists
(select null
from per_contact_relationships pcr
where pcr.contact_person_id = ppl.person_id);
delete from per_person_list ppl
where ppl.security_profile_id in
(select pspf.security_profile_id
from per_security_profiles pspf
where (pspf.view_all_contacts_flag = 'N' or
(pspf.view_all_contacts_flag = 'Y' and
pspf.view_all_candidates_flag = 'X')))
and ppl.person_id in
(select papf.person_id
from per_all_people_f papf
where papf.person_id = ppl.person_id
and papf.business_group_id = p_business_group_id)
and not exists
(select null
from per_all_assignments_f asg
where asg.person_id = ppl.person_id)
and not exists
(select null
from per_contact_relationships pcr
where pcr.contact_person_id = ppl.person_id);
delete
from per_organization_list
where security_profile_id = p_security_profile_id
and user_id is null;
delete
from per_position_list
where security_profile_id = p_security_profile_id
and user_id is null;
delete
from per_person_list
where security_profile_id = p_security_profile_id
and granted_user_id is null;
delete
from pay_payroll_list
where security_profile_id = p_security_profile_id;
select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
from per_person_type_usages_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
and ppf.person_type_id = ppt.person_type_id --taken out for Performance bug.
-- Current person today
and (( -- ppf.person_type_id = ppt.person_type_id and
ppt.business_group_id =
nvl(p_business_group_id,ppt.business_group_id)
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppt.system_person_type in ('EMP','APL','CWK'))
OR
-- Future person
( -- ppf.person_type_id = ppt.person_type_id and
ppt.business_group_id =
nvl(p_business_group_id,ppt.business_group_id)
and p_effective_date < ppf.effective_start_date
and ppt.system_person_type in ('EMP','APL','CWK')))
group by ppf.person_id;
select ppf.person_id, greatest(min(ppf.effective_start_date), p_effective_date)
from per_person_type_usages_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
/*
** Current person today
*/
and (( ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id =
nvl(p_business_group_id,ppt.business_group_id)
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppt.system_person_type in ('EMP','APL','CWK'))
OR
/*
** Future person
*/
( ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id =
nvl(p_business_group_id,ppt.business_group_id)
and p_effective_date < ppf.effective_start_date
and ppt.system_person_type in ('EMP','APL','CWK')))
group by ppf.person_id;
select ppf.person_id
from per_person_type_usages_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
and ( ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id =
nvl(p_business_group_id,ppt.business_group_id)
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppt.system_person_type in ('EMP','APL','CWK'));
select ppf.person_id, paf.assignment_type,
least(max(paf.effective_end_date), p_effective_date) effective_date
from per_person_type_usages_f ppf,
per_person_types ppt,
per_all_assignments_f paf
where ppf.person_id = p_person_id
and ppf.person_id = paf.person_id
and paf.assignment_type in ('A','C','E')
and paf.effective_start_date < p_effective_date
/*
** Existed as a current person at somepoint in history
*/
and ( ppf.person_type_id = ppt.person_type_id
and p_effective_date > ppf.effective_start_date
and ppt.system_person_type in ('EMP','APL','CWK'))
/*
** ...as an ex person on the effective date
*/
and exists (select null
from per_person_type_usages_f ppf1,
per_person_types ppt1
where ppf1.person_id = ppf.person_id
and p_effective_date between ppf1.effective_start_date
and ppf1.effective_end_date
and ppf1.person_type_id = ppt1.person_type_id
and ppt1.business_group_id = nvl(p_business_group_id,
ppt1.business_group_id)
and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
/*
** ...and not a current person on effective date or in the future.
**
** (Due to the implementation of PTU I can be both EMP and EX-APL
** today. i.e. I'm an employee who was successfully hired after
** some application process. In this case the person should be
** processed as a current and not an ex person. Note the
** exception for APLs who are former EMPs/CWKs - in this
** case an APL who is also term'd should be visible as both an
** APL and as EX-EMP/EX-CWK therefore this cursor can see people
** who are EX-EMP/EX-CWK but who are also APL
*/
and not exists (select null
from per_person_type_usages_f ppf2,
per_person_types ppt2
where ppf2.person_id = ppf.person_id
and p_effective_date < ppf2.effective_end_date
and ppf2.person_type_id = ppt2.person_type_id
and ppt2.business_group_id = nvl(p_business_group_id,
ppt2.business_group_id)
and ppt2.system_person_type in ('EMP','CWK'))
group by ppf.person_id, paf.assignment_type
order by effective_date desc;
select paf.assignment_id, paf.effective_start_date
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type not in ('B','O') -- added from bug 4352765, Bug 7412855
and ( (p_effective_date between paf.effective_start_date
and paf.effective_end_date)
or
(paf.effective_start_date > p_effective_date and
not exists (select null
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <=
paf.effective_start_date)));
select 'Y'
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = p_person_id
and p_effective_date < ptu.effective_end_date
and ptu.person_type_Id = ppt.person_type_id
and ppt.system_person_type = 'APL';
select 'Y'
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = p_person_id
and p_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EX_EMP','EX_CWK');
l_update_date date;
select *
from per_security_profiles
where ((business_group_id = p_business_group_id and
p_generation_scope = 'ALL_BUS_GRP')
or (business_group_id is null and
p_generation_scope = 'ALL_GLOBAL')
or (p_generation_scope = 'ALL_PROFILES')
and org_security_mode in ('NONE','HIER'));
l_update_date := trunc(sysdate);
l_update_date);
l_update_date,
l_business_group_mode);
l_update_date);
SELECT seu.user_id
,usr.employee_id person_id
,seu.security_user_id
,seu.object_version_number
FROM per_security_users seu
,fnd_user usr
WHERE seu.security_profile_id = p_sec_prof_rec.security_profile_id
AND (seu.process_in_next_run_flag = 'Y' OR l_all_static_users = 'Y')
AND seu.user_id = usr.user_id
AND usr.employee_id IS NOT NULL
AND nvl(p_user_id,usr.user_id) = seu.user_id;
SELECT fusg.responsibility_id,
fusg.responsibility_application_id,
fusg.security_group_id
FROM FND_USER_RESP_GROUPS fusg
WHERE fusg.user_id = p_user_id
AND p_effective_date BETWEEN fusg.start_date
AND NVL(fusg.end_date,p_effective_date)
AND rownum = 1;
,p_update_static_lists => TRUE
,p_debug => l_debug_type);
hr_security_user_api.update_security_user
(p_effective_date => p_effective_date
,p_security_user_id => user_rec.security_user_id
,p_user_id => user_rec.user_id
,p_security_profile_id => p_sec_prof_rec.security_profile_id
,p_process_in_next_run_flag => 'N'
,p_object_version_number => l_api_ovn
,p_del_static_lists_warning => l_del_static_lists_warning);
SELECT seu.user_id,
seu.security_profile_id
FROM per_security_users seu
WHERE seu.user_id = p_user_id
AND seu.security_profile_id = p_security_profile_id;
SELECT *
from per_security_profiles
where security_profile_id = l_security_profile_id;
l_update_date date;
SELECT pap.parameter_value
FROM pay_action_parameters pap
WHERE pap.parameter_name = 'LOGGING';
SELECT *
FROM per_security_profiles
WHERE ( (business_group_id = p_business_group_id and
p_generation_scope = 'ALL_BUS_GRP')
OR
(business_group_id is null and
p_generation_scope = 'ALL_GLOBAL')
OR
(p_generation_scope = 'ALL_PROFILES')
AND org_security_mode IN ('NONE','HIER')
);
l_update_date := trunc(sysdate);
l_update_date);
p_update_date := trunc(sysdate);
l_update_date date;
select pap.parameter_value
from pay_action_parameters pap
where pap.parameter_name = 'LOGGING';
select *
from per_security_profiles
where (((security_profile_id = p_security_profile_id or
security_profile_name = p_security_profile_name)
and p_generation_scope in ('SINGLE_PROF','SINGLE_USER'))
or (business_group_id = p_business_group_id and
p_generation_scope = 'ALL_BUS_GRP')
or (business_group_id is null and
p_generation_scope='ALL_GLOBAL')
or (p_generation_scope = 'ALL_PROFILES'))
and org_security_mode in ('NONE', 'HIER');
l_update_date := trunc(sysdate);
l_update_date);
l_update_date,
l_business_group_mode);
l_update_date);
l_update_date,
p_who_to_process);
l_update_date);
select pay_core_utils.get_parameter('GENERATION_SCOPE',
pa1.legislative_parameters)
into l_generation_scope
from pay_payroll_actions pa1
where payroll_action_id = pactid;
sqlstr := 'select distinct per.person_id
from per_all_people_f per
,pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
order by per.person_id';
sqlstr := 'select distinct per.person_id
from per_all_people_f per
,pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and pay_core_utils.get_parameter(''BUSINESS_GROUP_ID'',
ppa.legislative_parameters) =
per.business_group_id
order by per.person_id';
** Cursor to select the individual person ID's for each person in the range
** between stperson and endperson.
**
** Use the emp/apl/cwk number columns to filter out contact only people
** unless they become a Emp/Apl/Cwl in the future.
*/
cursor c_actions(pactid number,
stperson number,
endperson number) is
select distinct ppf.person_id
from per_person_type_usages_f ppf
,pay_payroll_actions ppa
,per_person_types ppt
where ppf.person_id between stperson and endperson
and ppa.payroll_action_id = pactid
-- and ppf.person_type_id = ppt.person_type_id --commented Bug6809753
and (( l_business_group_id = ppt.business_group_id
and l_generation_scope = 'ALL_BUS_GRP')
OR
( l_generation_scope = 'ALL_GLOBAL')
OR
( l_generation_scope = 'ALL_PROFILES'))
and (( l_who_to_proc in ('CURRENT','ALL')
/*
** Current person today
*/
and (( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppt.system_person_type in ('EMP','APL','CWK'))
OR
/*
** Future person
*/
( ppf.person_type_id = ppt.person_type_id and -- un commented Bug6809753
ppa.effective_date < ppf.effective_start_date
and ppt.system_person_type in ('EMP','APL','CWK'))))
OR
( l_who_to_proc in ('TERM','ALL')
/*
** Existed as a current person at somepoint in history
*/
and ( ppf.person_type_id = ppt.person_type_id
and ppa.effective_date > ppf.effective_start_date
and ppt.system_person_type in ('EMP','APL','CWK'))
/*
** ...as an ex person on the effective date
*/
and exists (select null
from per_person_type_usages_f ppf1,
per_person_types ppt1
where ppf1.person_id = ppf.person_id
and ppa.effective_date between ppf1.effective_start_date
and ppf1.effective_end_date
and ppf1.person_type_id = ppt1.person_type_id
and ppt1.business_group_id = ppt.business_group_id
and ppt1.system_person_type in ('EX_EMP','EX_APL','EX_CWK'))
/*
** ...and not a current person on effective date or in
** the future.
**
** Due to the implementation of PTU I can be both EMP and EX-APL
** today. i.e. I'm an employee who was successfully hired after
** some application process. In this case the person should be
** processed as a current and not an ex person. Note the
** exception for APLs who are either former EMPs/CWKs - in this
** case an APL who is also term'd should be visible as both an
** APL and as EX-EMP/EX-CWK therefore this cursor can see people
** who are EX-EMP/EX-CWK but who are also APL
*/
and not exists (select null
from per_person_type_usages_f ppf2,
per_person_types ppt2
where ppf2.person_id = ppf.person_id
and ppa.effective_date < ppf2.effective_end_date
and ppf2.person_type_id = ppt2.person_type_id
and ppt2.business_group_id = ppt.business_group_id
and ppt2.system_person_type in ('EMP','CWK'))));
** be used if terminated people are not selected.
*********************************************************************************************/
/*
** Cursor to select the assignment ID's for each person in the range
** between stperson and endperson.
*/
cursor c_actions_prev(pactid number,
stperson number,
endperson number) is
select distinct asg.assignment_id,
asg.person_id
from per_all_assignments_f asg
,pay_payroll_actions ppa
where asg.assignment_type in ('E','A','C')
and asg.person_id between stperson and endperson
and ppa.payroll_action_id = pactid
and ( pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
ppa.legislative_parameters) =
asg.business_group_id
and pay_core_utils.get_parameter('GENERATION_SCOPE',
ppa.legislative_parameters) =
'ALL_BUS_GRP'
OR
pay_core_utils.get_parameter('GENERATION_SCOPE',
ppa.legislative_parameters) =
'ALL_GLOBAL'
OR
pay_core_utils.get_parameter('GENERATION_SCOPE',
ppa.legislative_parameters) =
'ALL_PROFILES')
and ((ppa.effective_date between asg.effective_start_date
and asg.effective_end_date)
or
(asg.effective_start_date > ppa.effective_date and
not exists (select null
from per_all_assignments_f paf1
where paf1.assignment_id = asg.assignment_id
and paf1.effective_start_date <
ppa.effective_date)));
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
ppa.legislative_parameters),
pay_core_utils.get_parameter('GENERATION_SCOPE',
ppa.legislative_parameters),
pay_core_utils.get_parameter('WHO_TO_PROCESS',
ppa.legislative_parameters)
into l_business_group_id, l_generation_scope, l_who_to_proc
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
** If terminated people selected
**********************************/
IF (l_who_to_proc in ('TERM', 'ALL'))
THEN
for perrec in c_actions(pactid, stperson, endperson) loop
select pay_assignment_actions_s.nextval
into l_lockingactid
from dual;
** and insert the first action record.
*/
l_temp_person_id := perrec.person_id;
** insert an action record for this assignment
*/
l_temp_person_id := perrec.person_id;
** If terminated people are not selected
******************************************/
for asgrec in c_actions_prev(pactid, stperson, endperson) loop
select pay_assignment_actions_s.nextval
into l_lockingactid
from dual;
** and insert the first action record.
*/
l_temp_person_id := asgrec.person_id;
** insert an action record for this assignment
*/
l_temp_person_id := asgrec.person_id;
p_update_date := trunc(sysdate);
select ass.object_id
from pay_assignment_actions ass
where ass.assignment_action_id = p_assactid;
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
ppa.legislative_parameters),
pay_core_utils.get_parameter('GENERATION_SCOPE',
ppa.legislative_parameters),
pay_core_utils.get_parameter('WHO_TO_PROCESS',
ppa.legislative_parameters)
into l_business_group_id, l_generation_scope, l_who_to_process
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_assactid;
select 1
from per_person_list
where person_id = p_person_id
and granted_user_id is null
and security_profile_id = p_security_profile_id;
select pspv.*
from per_security_profiles_v pspv
where pspv.view_all_flag = 'N'
and pspv.view_all_candidates_flag <> p_profile_option;
per_security_profiles_pkg.update_row(
x_rowid => r_sec.row_id,
x_security_profile_id => r_sec.security_profile_id,
x_business_group_id => r_sec.business_group_id,
x_position_id => r_sec.position_id,
x_organization_id => r_sec.organization_id,
x_position_structure_id => r_sec.position_structure_id,
x_organization_structure_id => r_sec.organization_structure_id,
x_include_top_org_flag => r_sec.include_top_organization_flag,
x_include_top_position_flag => r_sec.include_top_position_flag,
x_security_profile_name => r_sec.security_profile_name,
x_view_all_applicants_flag => r_sec.view_all_applicants_flag,
x_view_all_employees_flag => r_sec.view_all_employees_flag,
x_view_all_flag => r_sec.view_all_flag,
x_view_all_organizations_flag => r_sec.view_all_organizations_flag,
x_view_all_payrolls_flag => r_sec.view_all_payrolls_flag,
x_view_all_positions_flag => r_sec.view_all_positions_flag,
x_view_all_cwk_flag => r_sec.view_all_cwk_flag,
x_view_all_contacts_flag => r_sec.view_all_contacts_flag,
x_view_all_candidates_flag => p_profile_option,
x_include_exclude_payroll_flag => r_sec.include_exclude_payroll_flag,
x_reporting_oracle_username => r_sec.reporting_oracle_username,
x_allow_granted_users_flag => r_sec.allow_granted_users_flag,
x_restrict_by_supervisor_flag => r_sec.restrict_by_supervisor_flag,
x_supervisor_levels => r_sec.supervisor_levels,
x_exclude_secondary_asgs_flag => r_sec.exclude_secondary_asgs_flag,
x_exclude_person_flag => r_sec.exclude_person_flag,
x_named_person_id => r_sec.named_person_id,
x_custom_restriction_flag => r_sec.custom_restriction_flag,
x_restriction_text => r_sec.restriction_text,
x_exclude_business_groups_flag => r_sec.exclude_business_groups_flag,
x_org_security_mode => r_sec.org_security_mode,
x_restrict_on_individual_asg => r_sec.restrict_on_individual_asg,
x_top_organization_method => r_sec.top_organization_method,
x_top_position_method => r_sec.top_position_method,
x_request_id => l_req_id,
x_program_application_id => l_appl_id,
x_program_id => l_prog_id,
x_program_update_date => l_upd_date
);
fnd_message.set_name('PER', 'PER_449705_SEC_UPDATE_FAILED');
errbuf := nvl(fnd_message.get, 'PER_449705_SEC_UPDATE_FAILED');