[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_id
FROM fnd_user
WHERE employee_id = p_emp_id
ORDER BY creation_date;
SELECT h.employee_id, h.full_name ||' ('||h.employee_num||') ' nameNum
FROM per_employees_x h,
ak_web_user_sec_attr_values a
WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
AND a.web_user_id = p_web_user_id
AND h.employee_id = a.number_value
ORDER BY UPPER(h.full_name);
SELECT h.employee_id, h.full_name ||' ('||h.employee_num||') ' nameNum
FROM per_employees_current_x h,
ak_web_user_sec_attr_values a
WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
AND a.web_user_id = p_web_user_id
AND h.employee_id = a.number_value
ORDER BY UPPER(h.full_name);
SELECT emp.person_id, full_name
FROM
(
SELECT employee_id person_id, full_name
FROM per_employees_x emp
WHERE upper(full_name) like upper(p_upper_approver_name_fuzzy)
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT person_id, full_name
FROM per_cont_workers_current_x emp
WHERE upper(full_name) like upper(p_upper_approver_name_fuzzy)
) emp,
per_assignments_f pera,
per_assignment_status_types peras
WHERE emp.person_id = pera.person_id
AND pera.assignment_status_type_id = peras.assignment_status_type_id
AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
AND pera.assignment_type in ('C', 'E')
AND pera.primary_flag='Y'
AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
AND rownum < 3;
SELECT count(*)
INTO p_count
FROM per_people_x h,
ak_web_user_sec_attr_values a
WHERE a.attribute_code = p_attribute_code
AND a.web_user_id = p_web_user_id
AND h.person_id = a.number_value;
select ORGANIZATION_ID
into p_org_id
from
( select organization_id
from per_employees_x emp
where employee_id = P_EmployeeID
and not AP_WEB_DB_HR_INT_PKG.isPersonCwk(emp.employee_id)='Y'
union all
select organization_id
from per_cont_workers_current_x emp
where person_id = P_EmployeeID);
/* SELECT emp1.full_name, emp1.employee_num, emp1.default_code_combination_id
INTO p_emp_info_rec.employee_name,
p_emp_info_rec.employee_num,
p_emp_info_rec.emp_ccid
FROM
(
SELECT emp.full_name,
emp.employee_num,
emp.default_code_combination_id,
employee_id person_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT cwk.full_name,
cwk.npw_number employee_num,
cwk.default_code_combination_id,
cwk. person_id
FROM per_cont_workers_current_x cwk
WHERE cwk.person_id = p_employee_id
) emp1;
SELECT emp1.full_name, emp1.employee_num, pera.default_code_comb_id
INTO p_emp_info_rec.employee_name,
p_emp_info_rec.employee_num,
p_emp_info_rec.emp_ccid
FROM
(
SELECT emp.full_name,
emp.employee_num,
emp.default_code_combination_id,
employee_id person_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT cwk.full_name,
cwk.npw_number employee_num,
cwk.default_code_combination_id,
cwk. person_id
FROM per_cont_workers_current_x cwk
WHERE cwk.person_id = p_employee_id
) emp1,
per_assignments_f pera
-- per_assignment_status_types peras
WHERE emp1.person_id = pera.person_id
-- AND pera.assignment_status_type_id = peras.assignment_status_type_id
AND trunc(sysdate) between trunc(pera.effective_start_date) and trunc(pera.effective_end_date)
AND pera.assignment_type in ('C', 'E')
AND pera.primary_flag='Y';
SELECT employee_id
INTO p_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT COUNT(*)
INTO l_security_attr_cnt
FROM hr_employees_current_v h,
ak_web_user_sec_attr_values a
WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
AND a.web_user_id = P_WebUserID
AND h.employee_id = a.number_value;
SELECT mgr.person_id, mgr.full_name, pera.business_group_id
INTO p_manager_id, p_manager_name, p_manager_org_id
FROM per_people_x mgr,
per_assignments_f pera,
per_assignment_status_types peras
WHERE mgr.person_id = (
SELECT emp.supervisor_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.supervisor_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
)
AND mgr.person_id = pera.person_id
AND pera.assignment_status_type_id = peras.assignment_status_type_id
AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
AND pera.assignment_type in ('C', 'E')
AND pera.primary_flag='Y'
AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
SELECT 1
INTO v_numRows
FROM DUAL
WHERE exists
(SELECT 1
FROM
per_people_f p,
per_assignments_f a,
per_assignment_status_types past
WHERE a.person_id = p.person_id
AND p.person_id = p_person_id
AND a.assignment_status_type_id = past.assignment_status_type_id
AND a.primary_flag = 'Y'
AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
AND a.assignment_type in ('E', 'C')
AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'SUSP_ASSIGN', 'SUSP_CWK_ASG')); -- Bug 8357892(sodash) Added SUSP_ASSIGN for active suspended employees
SELECT 1
INTO v_numRows
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM
per_cont_workers_current_x p
WHERE
p.person_id = p_person_id);
SELECT 1
INTO v_numRows
FROM DUAL
WHERE exists
(SELECT 1
FROM
per_people_f p,
per_assignments_f a,
per_assignment_status_types past
WHERE a.person_id = p.person_id
AND p.person_id = p_person_id
AND a.assignment_status_type_id = past.assignment_status_type_id
AND a.primary_flag = 'Y'
AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
AND a.assignment_type in ('E', 'C')
AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK',
'SUSP_ASSIGN', 'SUSP_CWK_ASG'));
SELECT inactive_date
INTO l_date
FROM per_employees_x
WHERE employee_id = p_employee_id;
SELECT mgr.person_id, mgr.full_name, peras.per_system_status
INTO p_manager_id, p_manager_name, p_manager_status
FROM per_people_x mgr,
per_assignments_f pera,
per_assignment_status_types peras
WHERE mgr.person_id = (
SELECT emp.supervisor_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.supervisor_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
)
AND mgr.person_id = pera.person_id
AND pera.assignment_status_type_id = peras.assignment_status_type_id
AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
AND pera.assignment_type in ('C', 'E')
AND pera.primary_flag='Y'
AND rownum =1;
SELECT asg.organization_id
INTO p_organization_id
FROM per_assignments_f asg
WHERE asg.person_id = p_employee_id
AND asg.assignment_type in ('E','C')
AND asg.primary_flag='Y'
AND TRUNC(p_effective_date)
BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT 1 into v_numRows
FROM DUAL
WHERE EXISTS
(SELECT 1 FROM fnd_user fnd, wf_users wf
WHERE fnd.user_name = wf.name and employee_id = p_emp_id
AND ORIG_SYSTEM not in ('HZ_PARTY','CUST_CONT')
AND status = 'ACTIVE')
and rownum=1;
SELECT user_id INTO p_user_id
FROM fnd_user
WHERE user_name = p_user_name;