[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into pay_payroll_list (payroll_id, security_profile_id)
select p_payroll_id, psp.security_profile_id
from per_security_profiles psp
where psp.view_all_flag <> 'Y'
and (( psp.view_all_payrolls_flag <> 'Y'
and business_group_id = p_business_group_id)
or ( psp.view_all_payrolls_flag <> 'Y'
and business_group_id is null))
and not exists
(select 1
from pay_payroll_list ppl
where ppl.security_profile_id = psp.security_profile_id
and ppl.payroll_id = p_payroll_id);
insert into per_person_list(person_id, security_profile_id, request_id
,program_application_id, program_id
,program_update_date)
select p_person_id, psp.security_profile_id, l_req_id, l_appl_id,
l_prog_id, l_upd_date
from per_security_profiles psp
where psp.view_all_flag <> 'Y'
and (((psp.view_all_contacts_flag <> 'Y' or
(psp.view_all_contacts_flag = 'Y' and
psp.view_all_candidates_flag = 'X')) and
business_group_id = p_business_group_id) or
((psp.view_all_contacts_flag <> 'Y' or
(psp.view_all_contacts_flag = 'Y' and
psp.view_all_candidates_flag = 'X')) and
business_group_id is null))
and not exists
(select 1
from per_person_list ppl
where ppl.security_profile_id = psp.security_profile_id
and ppl.person_id = p_person_id);
l_update_date date := trunc(sysdate);
INSERT INTO PER_PERSON_LIST
(PERSON_ID
,SECURITY_PROFILE_ID
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE)
select p_person_id, psp.security_profile_id, l_request_id,
l_program_application_id, l_program_id, l_update_date
from per_security_profiles psp
where psp.view_all_flag <> 'Y'
and ((
( (psp.view_all_organizations_flag <> 'Y' and
nvl(psp.top_organization_method, 'S') <> 'U')
or psp.view_all_payrolls_flag <> 'Y'
or (psp.view_all_positions_flag <> 'Y' and
nvl(psp.top_position_method, 'S') <> 'U')
or nvl(psp.custom_restriction_flag, 'N') = 'Y')
and business_group_id = p_business_group_id)
or
( ( psp.view_all_organizations_flag <> 'Y'
or NVL(psp.custom_restriction_flag, 'N') = 'Y')
and business_group_id is null))
and not exists
(select 1
from per_person_list ppl
where ppl.security_profile_id = psp.security_profile_id
and ppl.granted_user_id is null
and ppl.person_id = p_person_id);
delete from per_person_list
where person_id=p_person_id
and security_profile_id is not null;
'select 1
from per_all_assignments_f ASSIGNMENT,
per_all_people_f PERSON,
per_person_type_usages_f PERSON_TYPE
where ASSIGNMENT.assignment_id = :asg_id
and to_date(:asg_eff_date,''dd-mon-yyyy'')
between ASSIGNMENT.effective_start_date
and ASSIGNMENT.effective_end_date
and PERSON.person_id = ASSIGNMENT.person_id
and to_date(:per_eff_date,''dd-mon-yyyy'')
between PERSON.effective_start_date
and PERSON.effective_end_date
and PERSON.person_id = PERSON_TYPE.person_id
and to_date(:ptu_eff_date,''dd-mon-yyyy'')
between PERSON_TYPE.effective_start_date
and PERSON_TYPE.effective_end_date';
select paf.person_id,
paf.organization_id,
paf.position_id,
paf.payroll_id,
paf.business_group_id,
paf.assignment_type,
ppf.current_employee_flag,
ppf.current_npw_flag
from per_all_assignments_f paf,
per_all_people_f ppf
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id = ppf.person_id
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
l_update_date date := trunc(sysdate);
PROCEDURE INSERT_CACHE_TO_LIST IS
errors Number;
l_proc varchar2(100):= 'add_to_person_list.insert_cache_to_list';
Insert into per_person_list(security_profile_id,
person_id,request_id,
program_application_id,
program_id,
program_update_date)
values(l_security_profie_table(per_rec),
l_person_id,
nvl(l_request_id, ''),
nvl(l_prog_appl_id, ''),
nvl(l_program_id, ''),
to_date(to_char(l_update_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
);
l_inst_str := ' select sec.security_profile_id '; --
select 1 from per_person_list ppl
where ppl.person_id = :l_person_id
and ppl.granted_user_id is null
and ppl.security_profile_id = sec.security_profile_id) ';
select 1 from per_person_list ppl
where ppl.person_id =:l_person_id
and ppl.security_profile_id = sec.security_profile_id)';
insert_cache_to_list;
select asg.person_id
, asg.organization_id
, asg.position_id
, asg.payroll_id
, asg.business_group_id
from per_all_assignments_f asg
where asg.person_id=p_person_id
and ( (asg.assignment_type='E'
and asg.effective_end_date = (select max(p.actual_termination_date)
from per_periods_of_service p
where p.person_id = p_person_id)
) or
(asg.assignment_type='A'
and asg.effective_end_date = (select max(ap.date_end)
from per_applications ap
where ap.person_id = p_person_id)
) );
select 1
from per_all_people_f
where person_id=p_person_id;
select 1
from fnd_user
where user_id=p_granted_user_id;
select 1
from per_person_list
where person_id = p_person_id
and granted_user_id = p_granted_user_id
and security_profile_id is null;
INSERT INTO PER_PERSON_LIST
(PERSON_ID
,GRANTED_USER_ID)
values
(p_person_id
,p_granted_user_id);
delete from per_person_list
where person_id=p_person_id
and granted_user_id =p_granted_user_id
and security_profile_id is null;
delete from per_person_list
where person_id = p_person_id
and granted_user_id is not null
and security_profile_id is null;
SELECT *
FROM per_all_assignments_f paaf
WHERE paaf.person_id = p_person_id
AND p_effective_date BETWEEN
paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_type NOT IN ('A','B');
SELECT posv.org_structure_version_id
FROM per_org_structure_versions posv
WHERE posv.organization_structure_id = p_organization_structure_id
AND p_effective_date BETWEEN
posv.date_from AND NVL(posv.date_to, hr_general.end_of_time);
SELECT ppsv.pos_structure_version_id
FROM per_pos_structure_versions ppsv
WHERE ppsv.position_structure_id = p_position_structure_id
AND p_effective_date BETWEEN
ppsv.date_from AND NVL(ppsv.date_to, hr_general.end_of_time);
SELECT o.organization_id_child
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 = p_top_organization_id
AND o.org_structure_version_id = p_org_structure_version_id;
SELECT NULL
FROM per_org_structure_elements o
WHERE o.org_structure_version_id = p_org_structure_version_id
AND o.organization_id_child = p_top_organization_id
AND rownum = 1;
SELECT hapf.position_id
FROM hr_all_positions_f hapf
WHERE hapf.position_id = p_position_id
AND p_effective_date BETWEEN
hapf.effective_start_date AND hapf.effective_end_date
AND hr_security_internal.show_organization
(hapf.organization_id) = 'TRUE';
SELECT p.subordinate_position_id
FROM per_pos_structure_elements p
CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id
START WITH p.parent_position_id = p_top_position_id
AND p.pos_structure_version_id = p_pos_structure_version_id
AND EXISTS
(SELECT null
FROM hr_all_positions_f hapf
WHERE hapf.position_id = p.subordinate_position_id
AND p_effective_date BETWEEN
hapf.effective_start_date AND hapf.effective_end_date
AND hr_security_internal.show_organization
(hapf.organization_id) = 'TRUE');
SELECT p.subordinate_position_id
FROM per_pos_structure_elements p
CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id
START WITH p.parent_position_id = p_top_position_id
AND p.pos_structure_version_id = p_pos_structure_version_id;
SELECT NULL
FROM per_pos_structure_elements p
WHERE p.pos_structure_version_id = p_pos_structure_version_id
AND p.subordinate_position_id = p_top_position_id
AND rownum = 1;
select pcr.contact_person_id
from per_contact_relationships pcr
where pcr.person_id = p_person_id
and p_effective_date between pcr.date_start
and nvl(pcr.date_end, hr_api.g_eot)
and not exists
(select null
from per_all_assignments_f paaf
where paaf.person_id = pcr.contact_person_id
and paaf.ASSIGNMENT_TYPE <> 'B'); -- Bug 4450149
select distinct papf.person_id
from per_all_people_f papf
where (p_business_group_id is null or
papf.business_group_id = p_business_group_id)
and papf.employee_number is null
and papf.npw_number is null
and papf.applicant_number is null
and not exists
(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);
l_temp_per_tbl.delete;
select ptuf.person_id
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 ppt.business_group_id + 0 = nvl(p_business_group_id,ppt.business_group_id)--fix for bug 5222441.
and not exists
(select null
from per_all_assignments_f paaf
where paaf.person_id = ptuf.person_id)
and not exists
(select null
from per_contact_relationships pcr
where pcr.contact_person_id = ptuf.person_id);
select paaf.person_id,paaf.assignment_id
from per_all_people_f papf,
per_all_assignments_f paaf,
irc_rec_team_members irt
where papf.person_id = paaf.person_id
and nvl(paaf.vacancy_id,(select distinct vacancy_id from PER_VAC_LINKED_ASSIGNMENTS
where tgt_apl_asg_id=paaf.assignment_id and rownum<2)) = irt.vacancy_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and paaf.assignment_type in('A','O')
and irt.person_id = p_person_id;
SELECT o.business_group_id
FROM hr_all_organization_units o
WHERE o.organization_id = p_organization_id;
g_org_tbl.DELETE(p_business_group_id);
g_org_tbl.DELETE(l_temp_org_tbl(i));
SELECT organization_id
,entry_type
FROM per_security_organizations
WHERE p_security_profile_id IS NOT NULL
AND security_profile_id = p_security_profile_id
ORDER BY entry_type DESC;
g_org_tbl.DELETE(org_rec.organization_id);
l_select_from_clause VARCHAR2(1000);
l_select_from_clause :=
'SELECT hr_asg.assignment_id
,hr_asg.person_id
FROM '||l_paaf_name||' hr_asg '; --Fix For Bug # 12739699
( select null from '||l_paaf_name||' papf
where papf.person_id = hr_asg.person_id
and papf.assignment_type in (''A'',''C'',''E'')
and papf.effective_end_date >= :effective_date )
)
/* End Ex-Employee */
/* Future Employee */
or ( hr_asg.effective_start_date > :effective_date
and not exists
( select null from '||l_paaf_name||' papf
where papf.person_id = hr_asg.person_id
and papf.assignment_type in (''A'',''C'',''E'')
and papf.effective_start_date < hr_asg.effective_start_date )
)
/* End Future Employee */
)';
(SELECT null
FROM per_person_list ppl
WHERE ppl.security_profile_id = :security_profile_id
AND ppl.person_id = hr_asg.person_id)');
'EXISTS (SELECT NULL
FROM '||l_paaf_name||' ASSIGNMENT,
per_all_people_f PERSON,
per_person_type_usages_f PERSON_TYPE
WHERE ASSIGNMENT.rowid = hr_asg.rowid
AND ASSIGNMENT.ASSIGNMENT_ID = hr_asg.assignment_id
AND ASSIGNMENT.ASSIGNMENT_TYPE IN (''A'',''C'',''E'')
/* For the bug 6196437 */
AND PERSON.person_id = ASSIGNMENT.person_id
AND (
/* Active Employee */
:effective_date
BETWEEN PERSON.effective_start_date AND PERSON.effective_end_date
or /* Future Employee */
PERSON.effective_start_date > :effective_date)
AND PERSON.person_id = PERSON_TYPE.person_id
AND ( /* Active Employee */
:effective_date BETWEEN
PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date
or /* Future Employee */
PERSON_TYPE.effective_start_date > :effective_date)',
p_sec_prof_rec.restriction_text||')'));
l_sql_str := l_select_from_clause || l_where_clause || l_connect_clause;
(SELECT null
FROM per_person_list ppl
WHERE ppl.security_profile_id = :security_profile_id
AND ppl.person_id = hr_asg.person_id)');
'EXISTS (SELECT NULL
FROM '||l_paaf_name||' ASSIGNMENT,
per_all_people_f PERSON,
per_person_type_usages_f PERSON_TYPE
WHERE ASSIGNMENT.rowid = hr_asg.rowid
AND ASSIGNMENT.ASSIGNMENT_ID = hr_asg.assignment_id /* For the bug 6196437 */
AND PERSON.person_id = ASSIGNMENT.person_id
AND :effective_date
BETWEEN PERSON.effective_start_date AND PERSON.effective_end_date
AND PERSON.person_id = PERSON_TYPE.person_id
AND :effective_date BETWEEN
PERSON_TYPE.effective_start_date AND
PERSON_TYPE.effective_end_date',
p_sec_prof_rec.restriction_text||')'));
l_sql_str := l_select_from_clause || l_where_clause || l_connect_clause;
SELECT pol.organization_id
FROM per_organization_list pol
WHERE pol.security_profile_id IS NOT NULL
AND pol.user_id IS NOT NULL
AND pol.security_profile_id = p_security_profile_id
AND pol.user_id = p_user_id;
SELECT pol.organization_id
FROM per_organization_list pol
WHERE pol.security_profile_id IS NOT NULL
AND pol.user_id IS NULL
AND pol.security_profile_id = p_security_profile_id;
SELECT ppl.position_id
FROM per_position_list ppl
WHERE ppl.security_profile_id IS NOT NULL
AND ppl.user_id IS NOT NULL
AND ppl.security_profile_id = p_security_profile_id
AND ppl.user_id = p_user_id;
SELECT ppl.position_id
FROM per_position_list ppl
WHERE ppl.security_profile_id IS NOT NULL
AND ppl.user_id IS NULL
AND ppl.security_profile_id = p_security_profile_id;
SELECT ppl.payroll_id
FROM pay_payroll_list ppl
WHERE ppl.security_profile_id IS NOT NULL
AND ppl.security_profile_id = p_security_profile_id;
SELECT ppl.person_id
FROM per_person_list ppl
WHERE ppl.security_profile_id IS NOT NULL
AND ppl.granted_user_id IS NOT NULL
AND ppl.security_profile_id = p_security_profile_id
AND ppl.granted_user_id = p_user_id;
SELECT ppl.person_id
FROM per_person_list ppl
WHERE ppl.security_profile_id IS NOT NULL
AND ppl.granted_user_id IS NULL
AND ppl.security_profile_id = p_security_profile_id;
SELECT pal.assignment_id
,pal.person_id
FROM per_assignment_list pal
WHERE pal.security_profile_id IS NOT NULL
AND pal.user_id IS NOT NULL
AND pal.security_profile_id = p_security_profile_id
AND pal.user_id = p_user_id;
SELECT pal.assignment_id
,pal.person_id
FROM per_assignment_list pal
WHERE pal.security_profile_id IS NOT NULL
AND pal.user_id IS NULL
AND pal.security_profile_id = p_security_profile_id;
SELECT plc.person_id
FROM per_person_list_changes plc
WHERE plc.security_profile_id IS NOT NULL
AND plc.security_profile_id = p_security_profile_id
AND NOT EXISTS
(SELECT NULL
FROM per_all_assignments_f paaf
WHERE paaf.person_id = plc.person_id
AND paaf.assignment_type <> 'B'
AND p_effective_date BETWEEN
paaf.effective_start_date AND paaf.effective_end_date)
AND EXISTS
(SELECT NULL
FROM per_all_assignments_f paaf2
WHERE paaf2.person_id = plc.person_id
AND paaf2.assignment_type <> 'B'
AND p_effective_date > paaf2.effective_start_date);
SELECT ppl.person_id
FROM per_person_list ppl
WHERE ppl.granted_user_id IS NOT NULL
AND ppl.granted_user_id = p_user_id
AND ppl.security_profile_id IS NULL;
SELECT paaf.assignment_id
FROM per_all_assignments_f paaf
WHERE paaf.person_id = p_person_id
AND p_effective_date BETWEEN
paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_type <> 'B';
SELECT pay.payroll_id
FROM pay_all_payrolls_f pay
,pay_security_payrolls psp
WHERE psp.security_profile_id IS NOT NULL
AND psp.security_profile_id = p_security_profile_id
AND psp.payroll_id = pay.payroll_id
AND p_effective_date BETWEEN
pay.effective_start_date AND pay.effective_end_date;
SELECT pay.payroll_id
FROM pay_all_payrolls_f pay
WHERE p_effective_date BETWEEN
pay.effective_start_date AND pay.effective_end_date
AND pay.business_group_id IS NOT NULL
AND pay.business_group_id = p_business_group_id
AND NOT EXISTS
(SELECT NULL
FROM pay_security_payrolls psp
WHERE psp.security_profile_id IS NOT NULL
AND psp.security_profile_id = p_security_profile_id
AND psp.payroll_id = pay.payroll_id);
SELECT pol.organization_id
INTO l_organization_id
FROM per_organization_list pol
WHERE pol.user_id IS NOT NULL
AND pol.security_profile_id IS NOT NULL
AND pol.user_id = p_user_id
AND pol.security_profile_id = p_security_profile_id
AND rownum = 1;
SELECT ppl.position_id
INTO l_position_id
FROM per_position_list ppl
WHERE ppl.user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id
AND rownum = 1;
SELECT ppl.person_id
INTO l_person_id
FROM per_person_list ppl
WHERE ppl.granted_user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.granted_user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id
AND rownum = 1;
SELECT pal.assignment_id
INTO l_assignment_id
FROM per_assignment_list pal
WHERE pal.user_id IS NOT NULL
AND pal.security_profile_id IS NOT NULL
AND pal.user_id = p_user_id
AND pal.security_profile_id = p_security_profile_id
AND rownum = 1;
PROCEDURE delete_org_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get deleted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'delete_org_list_for_user';
DELETE FROM per_organization_list pol
WHERE pol.user_id IS NOT NULL
AND pol.security_profile_id IS NOT NULL
AND pol.user_id = p_user_id
AND pol.security_profile_id = p_security_profile_id;
END delete_org_list_for_user;
PROCEDURE delete_pos_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get deleted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'delete_pos_list_for_user';
DELETE FROM per_position_list ppl
WHERE ppl.user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id;
END delete_pos_list_for_user;
PROCEDURE delete_per_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get deleted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'delete_per_list_for_user';
DELETE FROM per_person_list ppl
WHERE ppl.granted_user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.granted_user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id;
END delete_per_list_for_user;
PROCEDURE delete_asg_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get deleted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'delete_asg_list_for_user';
DELETE FROM per_assignment_list pal
WHERE pal.user_id IS NOT NULL
AND pal.security_profile_id IS NOT NULL
AND pal.user_id = p_user_id
AND pal.security_profile_id = p_security_profile_id;
END delete_asg_list_for_user;
PROCEDURE insert_org_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get inserted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'insert_org_list_for_user';
INSERT INTO per_organization_list
(security_profile_id
,organization_id
,user_id
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES
(p_security_profile_id
,i
,p_user_id
,l_request_id
,l_program_application_id
,l_program_id
,sysdate
);
END insert_org_list_for_user;
PROCEDURE insert_pos_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get inserted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'insert_pos_list_for_user';
INSERT INTO per_position_list
(security_profile_id
,position_id
,user_id
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES
(p_security_profile_id
,i
,p_user_id
,l_request_id
,l_program_application_id
,l_program_id
,sysdate
);
END insert_pos_list_for_user;
PROCEDURE insert_per_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get inserted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'insert_per_list_for_user';
INSERT INTO per_person_list
(security_profile_id
,person_id
,granted_user_id
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES
(p_security_profile_id
,i
,p_user_id
,l_request_id
,l_program_application_id
,l_program_id
,sysdate
);
END insert_per_list_for_user;
PROCEDURE insert_asg_list_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- Break out into an autonomous transaction so that this does not
-- share the same commit cycle as the calling routine.
-- This ensures rows get inserted where there is no assumed commits
-- elsewhere, for example, at longon. It also commits regardless of
-- unexpected exceptions elsewhere.
--
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc VARCHAR2(72) := g_package||'insert_asg_list_for_user';
INSERT INTO per_assignment_list
(security_profile_id
,assignment_id
,person_id
,user_id
,request_id
,program_application_id
,program_id
,program_update_date)
VALUES
(p_security_profile_id
,i
,g_asg_tbl(i)
,p_user_id
,l_request_id
,l_program_application_id
,l_program_id
,sysdate
);
END insert_asg_list_for_user;
,p_update_static_lists IN BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN IS
--
-- Local variables.
--
l_org_structure_version_id NUMBER;
IF p_update_static_lists
AND NVL(p_sec_prof_rec.top_organization_method, 'S') = 'U'
THEN
--
-- Existing records for this user are deleted and then
-- re-inserted. Records are only inserted if this profile
-- is using user-based org security.
--
IF g_dbg THEN op(l_proc, 150); END IF;
delete_org_list_for_user
(p_user_id, p_sec_prof_rec.security_profile_id);
insert_org_list_for_user
(p_user_id,p_sec_prof_rec.security_profile_id);
,p_update_static_lists IN BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN IS
--
-- Local variables.
--
l_pos_structure_version_id NUMBER;
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
IF p_update_static_lists AND
NVL(p_sec_prof_rec.top_position_method, 'S') = 'U'
THEN
--
-- Existing records for this user are deleted and then
-- re-inserted. Records are only inserted if this profile
-- is using user-based org security.
--
IF g_dbg THEN op(l_proc, 130); END IF;
delete_pos_list_for_user
(p_user_id, p_sec_prof_rec.security_profile_id);
insert_pos_list_for_user
(p_user_id,p_sec_prof_rec.security_profile_id);
,p_update_static_lists in boolean default false
) return boolean is
-- Local variables.
l_proc varchar2(72) := g_package||'evaluate_per_access';
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
if p_update_static_lists and l_user_restriction then
--
-- Existing records for this user are deleted and then
-- re-inserted. Records are only inserted if this profile
-- is using user-based security.
if g_dbg then op(l_proc, 130); end if;
delete_per_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
insert_per_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
delete_asg_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
insert_asg_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
,p_update_static_lists in boolean default false
,p_top_person_id in number
) return boolean is
-- Local variables.
l_proc varchar2(72) := g_package||'evaluate_per_access';
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
,p_update_static_lists => p_update_static_lists
,p_debug => g_dbg_type);
if p_update_static_lists and l_user_restriction then
--
-- Existing records for this user are deleted and then
-- re-inserted. Records are only inserted if this profile
-- is using user-based security.
if g_dbg then op(l_proc, 130); end if;
delete_per_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
insert_per_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
delete_asg_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
insert_asg_list_for_user(p_user_id,p_sec_prof_rec.security_profile_id);
,p_update_static_lists IN BOOLEAN DEFAULT FALSE
,p_debug IN NUMBER DEFAULT g_NO_DEBUG)
IS
--
-- Local variables.
--
l_proc VARCHAR2(72) := g_package||'evaluate_access';
IF p_update_static_lists THEN
op(' p_update_static_lists '||
'TRUE');
op(' p_update_static_lists '||
'FALSE');
g_org_tbl.DELETE;
g_pos_tbl.DELETE;
g_pay_tbl.DELETE;
g_per_tbl.DELETE;
g_asg_tbl.DELETE;
g_vac_per_tbl.DELETE;
g_vac_asg_tbl.DELETE;
,p_update_static_lists => p_update_static_lists);
,p_update_static_lists => p_update_static_lists);
,p_update_static_lists => p_update_static_lists);
,p_update_static_lists IN BOOLEAN DEFAULT FALSE
,p_debug IN NUMBER DEFAULT g_NO_DEBUG
,p_top_person_id IN NUMBER)
IS
--
-- Local variables.
--
l_proc VARCHAR2(72) := g_package||'evaluate_access';
IF p_update_static_lists THEN
op(' p_update_static_lists '||
'TRUE');
op(' p_update_static_lists '||
'FALSE');
g_org_tbl.DELETE;
g_pos_tbl.DELETE;
g_pay_tbl.DELETE;
g_per_tbl.DELETE;
g_asg_tbl.DELETE;
g_vac_per_tbl.DELETE;
g_vac_asg_tbl.DELETE;
,p_update_static_lists => p_update_static_lists);
,p_update_static_lists => p_update_static_lists);
,p_update_static_lists => p_update_static_lists
,p_top_person_id => p_top_person_id);
PROCEDURE delete_static_lists_for_user
(p_user_id IN NUMBER
,p_security_profile_id IN NUMBER)
IS
--
-- This is not an autonomous transaction because it is called during
-- normal transaction processing, eg, by APIs, so an explicit commit
-- cannot be issued.
--
--
-- Local variables.
--
l_proc VARCHAR2(72) := g_package||'delete_static_lists_for_user';
DELETE FROM per_organization_list pol
WHERE pol.user_id IS NOT NULL
AND pol.security_profile_id IS NOT NULL
AND pol.user_id = p_user_id
AND pol.security_profile_id = p_security_profile_id;
DELETE FROM per_position_list ppl
WHERE ppl.user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id;
DELETE FROM per_person_list ppl
WHERE ppl.granted_user_id IS NOT NULL
AND ppl.security_profile_id IS NOT NULL
AND ppl.granted_user_id = p_user_id
AND ppl.security_profile_id = p_security_profile_id;
DELETE FROM per_assignment_list pal
WHERE pal.user_id IS NOT NULL
AND pal.security_profile_id IS NOT NULL
AND pal.user_id = p_user_id
AND pal.security_profile_id = p_security_profile_id;
END delete_static_lists_for_user;
PROCEDURE delete_security_list_for_bg(p_business_group_id NUMBER)
IS
--
l_proc varchar2(80) := g_package||'delete_security_list_for_bg';
SELECT pp.person_id
FROM per_people_f pp,
per_person_list pl
WHERE pp.person_id = pl.person_id
AND pp.business_group_id = p_business_group_id;
DELETE FROM pay_security_payrolls psp
WHERE psp.business_group_id = p_business_group_id;
DELETE FROM pay_payroll_list ppl
WHERE EXISTS ( SELECT ''
FROM pay_payrolls_f pay
WHERE pay.payroll_id = ppl.payroll_id
AND pay.business_group_id = p_business_group_id);
DELETE FROM per_person_list pl
WHERE pl.person_id = pevrec.person_id;
DELETE FROM per_position_list pol
WHERE EXISTS ( SELECT ''
FROM hr_all_positions_f pos
WHERE pos.position_id = pol.position_id
AND pos.business_group_id = p_business_group_id);
hr_utility.set_location('hr_delete.delete_security_list_for_bg',6);
DELETE FROM per_organization_list ol
WHERE EXISTS ( SELECT null
FROM hr_all_organization_units ou
WHERE ou.business_group_id = p_business_group_id
and ou.organization_id = ol.organization_id);
hr_utility.set_location('hr_delete.delete_security_list_for_bg',7);
DELETE FROM per_security_profiles psp
WHERE psp.business_group_id = p_business_group_id
AND psp.view_all_flag = 'N';
hr_utility.set_location('hr_delete.delete_security_list_for_bg',8);
DELETE FROM per_security_organizations pso
WHERE EXISTS( SELECT null
FROM hr_all_organization_units ou
WHERE ou.business_group_id = p_business_group_id
and ou.organization_id = pso.organization_id);
hr_utility.set_location('hr_delete.delete_security_list_for_bg',9);
DELETE FROM per_security_users psu
WHERE psu.security_profile_id IN (SELECT sp.security_profile_id
FROM per_security_profiles sp
WHERE sp.business_group_id = p_business_group_id);
END delete_security_list_for_bg;
PROCEDURE delete_per_from_security_list(P_PERSON_ID IN number)
IS
--
-- bug fix 3760559. l_proc size increased to 80.
l_proc varchar2(80) := 'HR_SECURITY_INTERNAL.DELETE_PER_FROM_SECURITY_LIST';
delete from per_person_list l
where l.person_id = P_PERSON_ID;
end delete_per_from_security_list;
p_update_date date;
p_update_date := trunc(sysdate);
insert into per_organization_list
(organization_id
,security_profile_id
,request_id
,program_application_id
,program_id
,program_update_date)
select
p_organization_id
,p_security_profile_id
,p_request_id
,p_program_application_id
,p_program_id
,p_update_date
from sys.dual
where not exists(select 1
from per_organization_list pol
where pol.organization_id = p_organization_id
and pol.security_profile_id = p_security_profile_Id
);
PROCEDURE delete_org_from_security_list(P_Organization_Id in number)
IS
--
l_proc varchar2(80) := 'HR_SECURITY_INTERNAL.DELETE_ORG_FROM_SECURITY_LIST';
DELETE FROM PER_ORGANIZATION_LIST
WHERE organization_id = P_Organization_Id;
end delete_org_from_security_list;
insert into per_position_list
(security_profile_id, position_id)
values (p_Security_Profile_Id, p_position_id);
PROCEDURE delete_pos_from_security_list(p_position_Id in number)
IS
--
l_proc varchar2(80) := 'HR_SECURITY_INTERNAL.DELETE_POS_FROM_SECURITY_LIST';
DELETE FROM PER_POSITION_LIST
WHERE position_id = p_position_Id;
end delete_pos_from_security_list;
PROCEDURE delete_pay_from_security_list(p_payroll_id number)
IS
--
l_proc varchar2(80) := 'HR_SECURITY_INTERNAL.DELETE_PAY_FROM_SECURITY_LIST';
DELETE FROM PAY_PAYROLL_LIST
WHERE payroll_id = p_payroll_id;
end delete_pay_from_security_list;
DELETE FROM PER_ALL_ASSIGNMENTS_F_PERF;
hr_utility.set_location(SQL%ROWCOUNT ||' Rows Deleted from PER_ALL_ASSIGNMENTS_F_PERF', 20);
fnd_file.put_line(fnd_file.log,'Rows Deleted into PER_ALL_ASSIGNMENTS_F_PERF');
INSERT INTO PER_ALL_ASSIGNMENTS_F_PERF
(ASSIGNMENT_ID, EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,PERSON_ID,ASSIGNMENT_TYPE, PAYROLL_ID,
POSITION_ID,SUPERVISOR_ID,ORGANIZATION_ID,ASSIGNMENT_STATUS_TYPE_ID,PRIMARY_FLAG,
SUPERVISOR_ASSIGNMENT_ID,BUSINESS_GROUP_ID)
SELECT ASSIGNMENT_ID, EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,PERSON_ID,ASSIGNMENT_TYPE, PAYROLL_ID,
POSITION_ID,SUPERVISOR_ID,ORGANIZATION_ID,ASSIGNMENT_STATUS_TYPE_ID,PRIMARY_FLAG,SUPERVISOR_ASSIGNMENT_ID,
BUSINESS_GROUP_ID FROM PER_ALL_ASSIGNMENTS_F;
hr_utility.set_location(SQL%ROWCOUNT ||' Rows Inserted into PER_ALL_ASSIGNMENTS_F_PERF', 30);
fnd_file.put_line(fnd_file.log,'Rows Inserted into PER_ALL_ASSIGNMENTS_F_PERF');