[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ver.org_structure_version_id
from
per_org_structure_versions ver,
per_organization_structures str
where
ver.organization_structure_id = str.organization_structure_id
and str.business_group_id = p_business_group_id
and str.position_control_structure_flg='Y'
and nvl(p_effective_date, sysdate) between ver.date_from
and nvl(ver.date_to, hr_general.end_of_time);
select organization_id, business_group_id
from
per_all_assignments_f
where
assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select business_group_id
from
hr_all_organization_units
where
organization_id = p_organization_id;
PROCEDURE per_abv_insert_validate(
p_assignment_id number,
p_value number,
p_unit varchar2,
p_effective_date date) is
--
l_proc varchar2(72) := g_package||'per_abv_insert_validate';
pqh_psf_bus.per_abv_insert_validate(
p_assignment_id =>p_assignment_id,
p_value =>p_value,
p_unit =>p_unit,
p_effective_date =>p_effective_date);
PROCEDURE per_abv_update_validate(
p_abv_id number,
p_assignment_id number,
p_value number,
p_unit varchar2,
p_effective_date date,
p_validation_start_date date,
p_validation_end_date date,
p_datetrack_mode varchar2) is
--
l_proc varchar2(72) := g_package||'per_abv_update_validate';
pqh_psf_bus.per_abv_update_validate(
p_abv_id =>p_abv_id,
p_assignment_id =>p_assignment_id,
p_value =>p_value,
p_unit =>p_unit,
p_effective_date =>p_effective_date,
p_validation_start_date =>p_validation_start_date,
p_validation_end_date =>p_validation_end_date,
p_datetrack_mode =>p_datetrack_mode);
if p_event = 'INSERT_VALIDATE' then
pqh_psf_bus.hr_psf_bus_insert_validate(p_rec, p_effective_date);
elsif p_event = 'UPDATE_VALIDATE' then
pqh_psf_bus.hr_psf_bus_update_validate(p_rec, p_effective_date, p_validation_start_date,p_validation_end_date, p_datetrack_mode );
if p_event = 'INSERT_VALIDATE' then
pqh_psf_bus.per_asg_bus_insert_validate(p_rec, p_effective_date);
elsif p_event = 'UPDATE_VALIDATE' then
pqh_psf_bus.per_asg_bus_update_validate(p_rec,
p_effective_date,
p_validation_start_date,
p_validation_end_date,
p_datetrack_mode );
elsif p_event = 'DELETE_VALIDATE' then
pqh_psf_bus.per_asg_bus_delete_validate(
p_rec => p_rec
,p_effective_date => p_effective_date
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_datetrack_mode => p_datetrack_mode);
SELECT level, POSITION_CONTROL_ENABLED_FLAG
FROM
PER_ORG_STRUCTURE_ELEMENTS A
where
a.business_group_id = p_business_group_id
start with organization_id_child = P_ORGANIZATION_ID
and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
connect by organization_id_child = prior organization_id_parent
and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
select organization_id_parent organization_id
from PER_ORG_STRUCTURE_ELEMENTS
where ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
minus
select organization_id_child organization_id
from PER_ORG_STRUCTURE_ELEMENTS
where ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
select nvl(osv.topnode_pos_ctrl_enabled_flag,'N')
from per_org_structure_versions osv
where osv.ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
Select assignment_id
from per_all_assignments_f
where position_id = p_position_id
and effective_start_date = p_start_date
and effective_end_date = p_end_date;
/* Below call to Wf_Directory.DeleteUserRole is to deleted already end-dated old POS - PER relation.
After deleting the old one, we can create the new relation with new POS */
hr_utility.set_location(l_proc,180);
/*Wf_Directory.DeleteUserRole ( p_username => l_user_name,
p_rolename => 'POS'|| ':' || l_old_position_id,
p_userOrigSystem => 'PER',
p_userOrigSystemID => l_user_orig_system_id,
p_roleOrigSystem => 'POS',
p_roleOrigSystemID => l_old_position_id);*/
select min(effective_start_date) into l_effective_start_date
from per_all_assignments_f
where assignment_id = l_assignment_id
and assignment_type = 'E'
and position_id = c_pos_id;*/
select min(effective_start_date) into l_effective_start_date
from per_all_assignments_f
where assignment_id = l_assignment_id
and assignment_type = 'E'
and position_id = c_pos_id
and effective_start_date <= c_curr_start_date
and effective_start_date > nvl((select max(effective_start_date)
from per_all_assignments_f
where assignment_id = l_assignment_id
and position_id not in (c_pos_id)
and effective_start_date < c_curr_start_date),(effective_start_date-1));
select min(effective_start_date) into l_effective_start_date
from per_all_assignments_f
where assignment_id = l_assignment_id
and assignment_type = 'E'
and position_id = c_pos_id
and effective_start_date > nvl((select max(effective_start_date)
from per_all_assignments_f
where assignment_id = l_assignment_id
and position_id not in (c_pos_id)
and effective_start_date < c_assg_start_date),(effective_start_date-1));
SELECT usr.user_name,usr.start_date,end_date
FROM per_all_assignments_f ass
, fnd_user usr
WHERE ass.person_id = usr.employee_id
and ass.person_id = p_person_id
--AND p_effective_start_date BETWEEN usr.start_date AND nvl (usr.end_date, p_effective_start_date)
AND ass.position_id IS NOT NULL
AND p_effective_start_date BETWEEN ass.effective_start_date AND ass.effective_end_date
AND ass.assignment_type IN ('E', 'C')
AND ass.position_id = p_old_position_id;
if (p_event in ('POST_INSERT', 'POST_UPDATE')) then
if (p_rec.assignment_type = 'E') then
if (l_position_id <> nvl(l_old_position_id,-1)) then
pqh_psf_bus.chk_position_budget(
p_assignment_id => l_assignment_id,
p_effective_date => p_effective_date,
p_called_from => 'ASG',
p_old_position_id => l_old_position_id,
p_new_position_id => l_position_id);
hr_utility.set_location(l_proc||'To Delete previous user role',86);
/* Wf_Directory.DeleteUserRole ( p_username => l_user_name,
p_rolename => 'POS'|| ':' || l_old_pos_id,
p_userOrigSystem => 'PER',
p_userOrigSystemID => p_rec.person_id,
p_roleOrigSystem => 'POS',
p_roleOrigSystemID => l_old_pos_id);*/
hr_utility.set_location(l_proc||'Deleted pervious user role',87);
SELECT USR.USER_NAME USER_NAME,
'PER' USER_ORIG_SYSTEM,
PER.PERSON_ID USER_ORIG_SYSTEM_ID,
'POS'||':'||POS.POSITION_ID ROLE_NAME,
'POS' ROLE_ORIG_SYSTEM,
POS.POSITION_ID ROLE_ORIG_SYSTEM_ID,
PER.FULL_NAME USER_DISPLAY_NAME,
'QUERY' NOTIFICATION_PREFERENCE,
FNDL.NLS_LANGUAGE LANGUAGE,
FNDT.NLS_TERRITORY TERRITORY,
PER.EMAIL_ADDRESS EMAIL_ADDRESS,
NULL FAX,
'ACTIVE' STATUS
from PER_ALL_ASSIGNMENTS_F ASS,
PER_ALL_POSITIONS POS,
FND_USER USR,
PER_ALL_PEOPLE_F PER,
FND_TERRITORIES FNDT,
FND_LANGUAGES FNDL,
HR_LOCATIONS HRL
where ASS.ASSIGNMENT_ID = P_ASSIGNMENT_ID
and ASS.POSITION_ID = POS.POSITION_ID
and ASS.PERSON_ID = USR.EMPLOYEE_ID
and ASS.PERSON_ID = PER.PERSON_ID
and trunc(sysdate) between PER.EFFECTIVE_START_DATE
and PER.EFFECTIVE_END_DATE
and trunc(sysdate) between ASS.EFFECTIVE_START_DATE
and ASS.EFFECTIVE_END_DATE
and trunc(sysdate) between USR.START_DATE
and nvl(USR.END_DATE, sysdate+1)
and PER.EMPLOYEE_NUMBER is not null
and ASS.ASSIGNMENT_TYPE = 'E'
and POS.LOCATION_ID = HRL.LOCATION_ID(+)
and HRL.COUNTRY = FNDT.TERRITORY_CODE(+)
and FNDT.NLS_TERRITORY = FNDL.NLS_TERRITORY(+);
select 'x'
from wf_roles -- wf_local_roles --(bug 2897533)
where orig_system_id = p_position_id
and orig_system = 'POS';
select 'x'
from wf_users --wf_local_users --(bug 2897533)
where orig_system_id = p_user_id
and orig_system = 'PER';
select 'x'
from wf_user_roles --wf_local_user_roles --(bug 2897533)
where user_orig_system_id = p_person_id
and user_orig_system = 'PER'
and role_orig_system = 'POS';
hr_utility.set_location('WF_SYNC set DELETE parameter true: '||l_proc, 10);
wf_event.AddParameterToList('DELETE', 'TRUE',l_plist);
hr_utility.set_location('In Insert WF_SYNC role: '
|| r_person.ROLE_NAME, 25);
hr_utility.set_location('In Insert WF_SYNC user: '
|| r_person.USER_NAME, 25);