[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT business_group_id, nvl(org_information10,'USD') currency_code
FROM per_people_f ppf, hr_organization_information oi
WHERE ppf.person_id = fnd_global.employee_id
AND ppf.business_group_id = oi.organization_id
AND oi.org_information_context = 'Business Group Information'
AND g_eff_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT 4 rank, hr_general.decode_lookup('STRUCTURE_TYPE','POS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
AND ce.position_id = paf.position_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 3 rank, hr_general.decode_lookup('STRUCTURE_TYPE','JOB')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
AND ce.job_id = paf.job_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 2 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ORG')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
AND ce.organization_id = paf.organization_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 1 rank, hr_general.decode_lookup('STRUCTURE_TYPE','BUS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
FROM per_competence_elements ce, per_all_assignments_f paf
WHERE ce.type = 'REQUIREMENT'
AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
AND ce.enterprise_id = paf.business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E', 'C')
AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
AND ce.competence_id = p_cid
AND paf.person_id = p_pid
UNION ALL
SELECT 0 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ADD')||'##' src
FROM dual
ORDER BY RANK DESC;
FUNCTION validate_selected_function (
p_api_version IN NUMBER
,p_function IN VARCHAR2
,p_object_name IN VARCHAR2
,p_person_id IN VARCHAR2 -- p_instance_pk1_value
,p_instance_pk2_value IN VARCHAR2
,p_user_name IN VARCHAR2
,p_eff_date IN DATE
)
RETURN VARCHAR2
IS
l_proc varchar2(72) := g_package||' validate_selected_function';
END validate_selected_function;
select named_person_id
from per_security_profiles
where security_profile_id=p_security_profile_id;
select employee_id
from fnd_user
where user_id=p_user_id;
SELECT function_id, parameters, web_html_call
FROM fnd_form_functions fff
WHERE fff.function_name = p_func_name;
SELECT 'N' status
FROM WF_PROCESS_ACTIVITIES pa1, WF_PROCESS_ACTIVITIES pa2,
WF_ACTIVITIES a1, WF_ACTIVITIES a2, WF_ACTIVITY_ATTR_VALUES aav
WHERE pa1.process_item_type = p_item_type
and pa1.process_name = p_wfpname
and pa1.activity_name = pa2.process_name
and a1.name = pa1.process_name
and pa1.process_version = a1.version
and a1.item_type = p_item_type
and sysdate between a1.begin_date and nvl(a1.end_date,sysdate)
and pa2.process_item_type = p_item_type
and pa2.process_name = a2.name
and pa2.process_version = a2.version
and a2.item_type = p_item_type
and sysdate between a2.begin_date and nvl(a2.end_date,sysdate)
and pa2.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS')
UNION
SELECT 'N' status
FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
WF_ACTIVITY_ATTR_VALUES aav
where a.item_type = p_item_type
and a.name = p_wfpname
and sysdate between a.begin_date and nvl(a.end_date,sysdate)
and pa.process_item_type = a.item_type
and pa.process_name = a.name
and pa.process_version = a.version
and pa.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS');
p_selected_person_id NUMBER,
p_effective_date DATE)
RETURN VARCHAR2 IS
cursor fetch_asg (l_person_id NUMBER, l_effective_date DATE) is
SELECT 'T'
FROM per_assignments_f2 paf, per_assignment_status_types past
WHERE paf.person_id = l_person_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type IN ('E', 'C')
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status NOT IN ('TERM_ASSIGN','END');
OPEN fetch_asg (p_selected_person_id, p_effective_date);
hr_utility.trace('Going into Fetch after (OPEN fetch_asg (p_selected_person_id, p_effective_date) ): '|| l_proc);
SELECT 'C' FROM dual
WHERE UPPER(p_ak_region) IN (
'HR_CCMGR_OVERVIEW_TOP_SS',
'PQH_ACADEMIC_RANK_TOP',
'PQH_ACADEMIC_RANK_OVRVW_TOP',
'PQH_TENURE_STATUS_OVRVW_TOP',
'PQH_TENURE_STATUS_TOP',
'PQH_REVIEW_FIND_TOP',
--'OTA_TRAINING_TOP_SS',
--'OTA_ADDTRNG_OVERVIEW_TOP_SS',
--'PQH_REVIEWS_TOP',
--'PQH_EVENTS_MGR_SEARCH_TOP',
'HR_LOA_SUMMARY_TOP_SS'
);
SELECT nvl(current_npw_flag,'N')
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND p_eff_date BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT function_id, parameters, web_html_call
FROM fnd_form_functions fff
WHERE fff.function_name = p_func_name;
select 'T' from fnd_menus m, fnd_menu_entries me
where menu_name = p_menu_name
and m.menu_id = me.menu_id
and me.function_id = p_func_id;
SELECT 'C' status
FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
WF_ACTIVITY_ATTR_VALUES aav
where a.item_type = p_item_type
and a.name = p_wfpname
and sysdate between a.begin_date and nvl(a.end_date,sysdate)
and pa.process_item_type = a.item_type
and pa.process_name = a.name
and pa.process_version = a.version
and pa.instance_id = aav.process_activity_id
and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
and 'C' = hr_util_misc_ss.check_akregion_code(text_value);
g_entity_list.delete;
aname => 'HR_SELECTED_PERSON_TYPE_ATTR');
DELETE PER_INTERIM_PERSON_LIST;
INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
values (person_data(i).person_id, person_data(i).assignment_id, person_data(i).in_my_list);
DELETE PER_INTERIM_PERSON_LIST;
INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
SELECT selected_person_id, selected_assignment_id, 'Y'
FROM hr_working_person_lists
WHERE owning_person_id = person_id;
INSERT INTO HR_WORKING_PERSON_LISTS(working_person_list_id, owning_person_id,
selected_person_id, current_selection, multiple_selection, selected_assignment_id)
SELECT HR_WORKING_PERSON_LISTS_s.NEXTVAL,
prsn_id,
list.person_id,
NULL,
NULL,
list.assignment_id
FROM PER_INTERIM_PERSON_LIST list;
select security_group_id into l_secGrpId
from fnd_security_groups
where security_group_key = p_bgId;
DELETE PER_INTERIM_ENTITY_LIST;
INSERT INTO PER_INTERIM_ENTITY_LIST (entity_name,state,pk1,pk2,pk3,pk4,pk5)
values (entity_data(i).entity_name, entity_data(i).state,entity_data(i).pk1,entity_data(i).pk2,entity_data(i).pk3,entity_data(i).pk4,entity_data(i).pk5);
DELETE PER_INTERIM_ENTITY_LIST;
select assignment_id
into assi_id
from per_people_f ppf, per_assignments_f paf
where paf.person_id = ppf.person_id
and trunc(sysdate) between paf.effective_start_date(+) and paf.effective_end_date(+)
and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+)
and ppf.person_id = p_person_id
and paf.assignment_id = p_assignment_id
and nvl(ppf.CURRENT_EMP_OR_APL_FLAG,'N') = 'N' and nvl(ppf.CURRENT_EMPLOYEE_FLAG,'N') = 'N'
and nvl(ppf.CURRENT_NPW_FLAG,'N') = 'N';
select from_person_id
from hr_person_deployments dep
where dep.to_person_id = person_id and permanent='Y';