[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct hats.transaction_id
FROM hr_api_transaction_steps hats
WHERE hats.item_type = p_item_type
AND hats.item_key = p_item_key
AND hats.activity_id = p_actid
ORDER by hats.transaction_id;
select application_column_name
from FND_DESCR_FLEX_COLUMN_USAGES
where descriptive_flex_context_code = 'SSHR Information'
and end_user_column_name = user_column_name
-- ***** Start new code for bug 2731002 *****
and application_id=800
and descriptive_flexfield_name='Org Developer DF';
v_BlockStr := ' select ' || col_name ||
' from hr_organization_information hoi ' ||
' where hoi.organization_id = :1 '||
' and hoi.org_information_context = ' ||'''SSHR Information''';
job_select_segments VARCHAR2(500) DEFAULT NULL;
job_select_segments := replace(p_job_segments,'|','||''.''||');
v_BlockStr := ' SELECT ' || job_select_segments ||
' FROM per_job_definitions pjd, per_jobs pj ' ||
' WHERE pjd.job_definition_id = pj.job_definition_id ' ||
' AND pj.job_id = :1';
SELECT full_name,
business_group_id
FROM per_all_people_f
WHERE person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date
AND NVL(effective_end_date, TRUNC(SYSDATE));
SELECT ppf.person_id,
ppf.full_name,
paf.assignment_number,
paf.business_group_id,
paf.effective_start_date,
paf.effective_end_date,
paf.payroll_id,
paf.location_id,
paf.primary_flag,
paf.object_version_number
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT ppf.person_id,
ppf.full_name
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.supervisor_id = ppf.person_id
-- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
AND p_effective_date BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT pj.job_id,
pj.name
FROM per_jobs_vl pj,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.job_id = pj.job_id
-- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
AND paf.business_group_id+0 = pj.business_group_id+0
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT hl.location_id,
hl.location_code
FROM hr_locations hl,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.location_id = hl.location_id
-- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT hou.organization_id,
hou.name
FROM hr_organization_units hou,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.organization_id = hou.organization_id
-- AND paf.primary_flag = 'Y' -- To supprot multiple assignments
AND paf.business_group_id+0 = hou.business_group_id+0
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_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.primary_flag = 'Y'
-- Fix for bug 2268056
AND paf.assignment_type in ('E','C')
ORDER BY paf.effective_start_date DESC;
SELECT meaning
FROM hr_lookups
WHERE LOOKUP_CODE = p_code
AND LOOKUP_TYPE = p_lookup_type AND
p_date between nvl(start_date_active , p_date) AND
nvl(end_date_active, p_date)
AND enabled_flag = 'Y' ;
SELECT MIN(pps.date_start)
FROM per_periods_of_service pps,
per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.person_id = pps.person_id;
select ser.actual_termination_date
from per_periods_of_service ser,
per_all_assignments_f ass
where ass.period_of_service_id = ser.period_of_service_id
AND TRUNC(SYSDATE) between ass.effective_start_date
AND ass.effective_end_date
AND ass.assignment_id = p_assignment_id;
SELECT MAX(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date > p_effective_date;
|| the logged in person or from the selected resposibility.
|| Logged in Person - This function will return the business group of
|| the logged in person.
|| Responsibility - The Function call returns the Business Group ID
|| for the current session's login responsibility.
|| The defaulting levels are as defined in the
|| package FND_PROFILE. It returns business group id
|| value for a specific user/resp/appl combo.
|| Default is user/resp/appl/site is current login.
||
|| Access Status:
|| Public.
||
||===========================================================================
*/
FUNCTION get_business_group_id (
p_person_id IN per_people_f.person_id%TYPE
)
RETURN per_business_groups.business_group_id%TYPE
IS
-- Local Variables.
l_person_rec per_people_f%ROWTYPE;
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id IN
(SELECT per.business_group_id
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND TRUNC(p_effective_date) BETWEEN per.effective_start_date
AND NVL(per.effective_end_date, TRUNC(SYSDATE)));
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id IN
(SELECT paf.business_group_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND TRUNC(p_effective_date) BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE)));
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
SELECT org.org_information9 legislation_code
FROM per_all_people_f ppf
,hr_organization_information org
WHERE org.organization_id = ppf.business_group_id
AND org.org_information_context = 'Business Group Information'
AND ppf.person_id = p_person_id
AND TRUNC(p_effective_date)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT org.org_information9 legislation_code
FROM per_all_assignments_f paf
,hr_organization_information org
WHERE org.organization_id = paf.business_group_id
AND org.org_information_context = 'Business Group Information'
AND paf.assignment_id = p_assignment_id
AND TRUNC(p_effective_date)
BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT org_information9 legislation_code
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
and organization_id = p_business_group_id;