[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(ORG.organization_id_child, l_orgid, LEVEL+1, LEVEL) ORG_LEVEL
FROM per_org_structure_elements ORG
WHERE (ORG.organization_id_child = l_orgid OR
ORG.organization_id_parent = l_orgid)
AND ORG.org_structure_version_id = l_org_strver_id
CONNECT
BY
PRIOR organization_id_child = organization_id_parent
AND PRIOR org_structure_version_id = org_structure_version_id
START
WITH organization_id_parent
NOT IN (SELECT organization_id_child
FROM per_org_structure_elements);
SELECT organization_id
INTO l_orgid
FROM hr_all_positions_f
WHERE position_id = p_position_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT LEVEL org_level,
organization_id_parent,
organization_id_child,
org_structure_version_id
FROM per_org_structure_elements
WHERE org_structure_version_id = l_org_strver_id
CONNECT
BY
PRIOR organization_id_parent = organization_id_child
START
WITH organization_id_child = (SELECT POS.organization_id
FROM hr_all_positions_f POS,
per_assignments_f ASG
WHERE ASG.rowid = p_ASG_rowid
AND ASG.position_id = POS.position_id
AND p_effective_date BETWEEN POS.effective_start_date
AND POS.effective_end_date)
ORDER BY LEVEL DESC;
SELECT POS.organization_id
INTO l_result
FROM hr_all_positions_f POS,
per_assignments_f ASG
WHERE ASG.rowid = p_ASG_rowid
AND ASG.position_id = POS.position_id
AND p_effective_date BETWEEN POS.effective_start_date
AND POS.effective_end_date;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y'
AND TRUNC(l_effective_date)
BETWEEN NVL(start_date_active, TRUNC(l_effective_date))
AND NVL(end_date_active, TRUNC(l_effective_date));
select flx.id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' --
and flx.application_id = 800 --
and flx.id_flex_structure_name = p_structure_name
and flx.language = 'US';
select pea.analysis_criteria_id,
pan.date_from, -- added for bug fix : 609285
pea.start_date_active,
pea.segment1,
pea.segment2,
pea.segment3,
pea.segment4,
pea.segment5,
pea.segment6,
pea.segment7,
pea.segment8,
pea.segment9,
pea.segment10,
pea.segment11,
pea.segment12,
pea.segment13,
pea.segment14,
pea.segment15,
pea.segment16,
pea.segment17,
pea.segment18,
pea.segment19,
pea.segment20
from per_analysis_Criteria pea,
per_person_analyses pan
where pan.person_id = p_person_id
and pan.id_flex_num = l_id_flex_num
and pea.analysis_Criteria_id = pan.analysis_criteria_id
and p_effective_date
between nvl(pan.date_from,p_effective_date)
and nvl(pan.date_to,p_effective_date)
and p_effective_date
between nvl(pea.start_date_active,p_effective_date)
and nvl(pea.end_date_active,p_effective_date)
order by 2 desc ;
SELECT GDF.segment2
INTO l_result
FROM per_grades GRD
,per_grade_definitions GDF
WHERE GRD.grade_id = p_ASG.grade_id
AND GDF.grade_definition_id = GRD.grade_definition_id;
SELECT job.name || ' - ' || decode_lookup('GHR_US_OCC_SERIES', job.name)
INTO l_result
FROM hr_all_positions_f POS,
per_jobs job
WHERE POS.position_id = p_ASG.position_id
AND TRUNC(effective_date) BETWEEN POS.effective_start_date
AND POS.effective_end_date
AND JOB.job_id = POS.job_id;
SELECT GRD.name
INTO l_result
FROM per_grades GRD
WHERE GRD.grade_id = p_ASG.grade_id;
SELECT NVL(LKP.description, l_result)
INTO l_result
FROM hr_lookups LKP
WHERE LKP.lookup_type = 'GHR_US_HANDICAP_CODE'
AND LKP.lookup_code = l_rescpy
AND LKP.enabled_flag = 'Y'
AND TRUNC(l_effective_date)
BETWEEN NVL(LKP.start_date_active, TRUNC(l_effective_date))
AND NVL(LKP.end_date_active, TRUNC(l_effective_date));
SELECT NVL(LKP.description, LKP.meaning)
INTO l_result
FROM hr_lookups LKP
WHERE LKP.lookup_type = 'GHR_US_OCC_CATEGORY_CODE'
AND LKP.lookup_code = l_posei_data.poei_information6
AND LKP.enabled_flag = 'Y'
AND TRUNC(l_effective_date)
BETWEEN NVL(LKP.start_date_active, TRUNC(l_effective_date))
AND NVL(LKP.end_date_active, TRUNC(l_effective_date));
SELECT person_id
INTO l_result
FROM per_people_f
WHERE rowid = p_PER_rowid;
SELECT GDF.segment1
INTO l_result
FROM per_grades GRD
,per_grade_definitions GDF
WHERE GRD.grade_id = p_ASG.grade_id
AND GDF.grade_definition_id = GRD.grade_definition_id;
SELECT full_name
INTO l_result
FROM per_people_f
WHERE rowid = p_PER_rowid;
SELECT person_id
INTO l_result
FROM per_people_f
WHERE rowid = p_PER_rowid;
SELECT employee_number
INTO l_result
FROM per_people_f
WHERE rowid = p_PER_rowid;
SELECT POS.organization_id
INTO l_result
FROM hr_all_positions_f POS
WHERE POS.position_id = p_ASG.position_id
AND TRUNC(l_effective_date) BETWEEN POS.effective_start_date
AND POS.effective_end_date;
PROCEDURE Delete_Temp_Data IS
BEGIN
DELETE FROM GHR_BREAKDOWN_RESULTS WHERE session_id = USERENV('SESSIONID');
SELECT PER.rowid PER_rowid, ASG.rowid ASG_rowid,
PER.person_id, PER.sex
FROM per_people_f PER, per_assignments_f ASG
WHERE l_effective_date BETWEEN PER.effective_start_date
AND PER.effective_end_date
AND PER.person_id = ASG.person_id
AND l_effective_date BETWEEN ASG.effective_start_date
AND ASG.effective_end_date
AND get_hierarchy_level(ASG.position_id, l_effective_date) >= l_for_clause
AND ghr_api.get_position_agency_code_pos(ASG.position_id, ASG.business_group_id)
LIKE l_agcy_and_selm
AND ASG.primary_flag = 'Y'
AND ASG.assignment_type <> 'B';
DELETE FROM GHR_BREAKDOWN_RESULTS
WHERE session_id = l_rslt_session_id
AND breakdown_criteria_id = p_breakdown_criteria_id;
DELETE FROM GHR_BREAKDOWN_RESULTS
WHERE session_id = l_rslt_session_id;
INSERT INTO ghr_breakdown_results
(session_id, breakdown_result_id, breakdown_criteria_id, for_clause, within_clause,
by_clause, sex, ethnic_origin)
VALUES
(l_rslt_session_id, l_rslt_id, p_breakdown_criteria_id, l_rslt_for_clause,
l_rslt_within_clause, l_rslt_by_clause, r_emp.sex, r_GRP1.pei_information5);
SELECT s.name
FROM per_organization_structures s
,per_org_structure_versions v
WHERE v.org_structure_version_id = p_org_structure_version_id
AND v.organization_structure_id = s.organization_structure_id;