[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_row(
p_row_id in out nocopy varchar2,
p_grade_id in out nocopy number,
p_business_group_id number,
p_grade_definition_id number,
p_date_from date,
p_sequence number,
p_comments varchar2,
p_date_to date,
p_name varchar2,
p_request_id number,
p_program_application_id number,
p_program_id number,
p_program_update_date date,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
p_language_code varchar2 default hr_api.userenv_lang) IS
cursor c1 is
select per_grades_s.nextval
from sys.dual;
select rowid
from per_grades
where grade_id = p_grade_id;
insert into per_grades (
grade_id,
business_group_id,
grade_definition_id,
date_from,
sequence,
comments,
date_to,
name,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20)
values (
p_grade_id,
p_business_group_id,
p_grade_definition_id,
p_date_from,
p_sequence,
p_comments,
p_date_to,
p_name,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20);
end insert_row;
procedure delete_row(p_row_id varchar2,
p_grd_id in number) is
begin
stbdelvl(p_grd_id);
delete from per_grades g
where g.rowid = chartorowid(P_ROW_ID);
end delete_row;
p_program_update_date date,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
p_language_code varchar2 default hr_api.userenv_lang) IS
cursor OPM_CUR is
select *
from per_grades_vl g
where g.row_id = chartorowid(P_ROW_ID)
for update of grade_id nowait;
and ((opm_rec.program_update_date = p_program_update_date)
or (opm_rec.program_update_date is null
and (p_program_update_date is null)))
and ((opm_rec.attribute_category = p_attribute_category)
or (opm_rec.attribute_category is null
and (p_attribute_category is null)))
and ((opm_rec.attribute1 = p_attribute1)
or (opm_rec.attribute1 is null
and (p_attribute1 is null)))
and ((opm_rec.attribute2 = p_attribute2)
or (opm_rec.attribute2 is null
and (p_attribute2 is null)))
and ((opm_rec.attribute3 = p_attribute3)
or (opm_rec.attribute3 is null
and (p_attribute3 is null)))
and ((opm_rec.attribute4 = p_attribute4)
or (opm_rec.attribute4 is null
and (p_attribute4 is null)))
and ((opm_rec.attribute5 = p_attribute5)
or (opm_rec.attribute5 is null
and (p_attribute5 is null)))
and ((opm_rec.attribute6 = p_attribute6)
or (opm_rec.attribute6 is null
and (p_attribute6 is null)))
and ((opm_rec.attribute7 = p_attribute7)
or (opm_rec.attribute7 is null
and (p_attribute7 is null)))
and ((opm_rec.attribute8 = p_attribute8)
or (opm_rec.attribute8 is null
and (p_attribute8 is null)))
and ((opm_rec.attribute9 = p_attribute9)
or (opm_rec.attribute9 is null
and (p_attribute9 is null)))
and ((opm_rec.attribute10 = p_attribute10)
or (opm_rec.attribute10 is null
and (p_attribute10 is null)))
and ((opm_rec.attribute11 = p_attribute11)
or (opm_rec.attribute11 is null
and (p_attribute11 is null)))
and ((opm_rec.attribute12 = p_attribute12)
or (opm_rec.attribute12 is null
and (p_attribute12 is null)))
and ((opm_rec.attribute13 = p_attribute13)
or (opm_rec.attribute13 is null
and (p_attribute13 is null)))
and ((opm_rec.attribute14 = p_attribute14)
or (opm_rec.attribute14 is null
and (p_attribute14 is null)))
and ((opm_rec.attribute15 = p_attribute15)
or (opm_rec.attribute15 is null
and (p_attribute15 is null)))
and ((opm_rec.attribute16 = p_attribute16)
or (opm_rec.attribute16 is null
and (p_attribute16 is null)))
and ((opm_rec.attribute17 = p_attribute17)
or (opm_rec.attribute17 is null
and (p_attribute17 is null)))
and ((opm_rec.attribute18 = p_attribute18)
or (opm_rec.attribute18 is null
and (p_attribute18 is null)))
and ((opm_rec.attribute19 = p_attribute19)
or (opm_rec.attribute19 is null
and (p_attribute19 is null)))
and ((opm_rec.attribute20 = p_attribute20)
or (opm_rec.attribute20 is null
and (p_attribute20 is null))) )
THEN
--
-- MLS Processing
--
per_gdt_shd.lck (p_grade_id => p_grade_id
,p_language => p_language_code);
procedure update_row(
p_row_id varchar2,
p_grade_id number,
p_business_group_id number,
p_grade_definition_id number,
p_date_from date,
p_sequence number,
p_comments varchar2,
p_date_to date,
p_name varchar2,
p_request_id number,
p_program_application_id number,
p_program_id number,
p_program_update_date date,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
p_language_code varchar2 default hr_api.userenv_lang) is
begin
update per_grades g
set
g.grade_id = p_grade_id,
g.business_group_id = p_business_group_id,
g.grade_definition_id = p_grade_definition_id,
g.date_from = p_date_from,
g.sequence = p_sequence,
g.comments = p_comments,
g.date_to = p_date_to,
g.name = p_name,
g.request_id = p_request_id,
g.program_application_id = p_program_application_id,
g.program_id = p_program_id,
g.program_update_date = p_program_update_date,
g.attribute_category = p_attribute_category,
g.attribute1 = p_attribute1,
g.attribute2 = p_attribute2,
g.attribute3 = p_attribute3,
g.attribute4 = p_attribute4,
g.attribute5 = p_attribute5,
g.attribute6 = p_attribute6,
g.attribute7 = p_attribute7,
g.attribute8 = p_attribute8,
g.attribute9 = p_attribute9,
g.attribute10 = p_attribute10,
g.attribute11 = p_attribute11,
g.attribute12 = p_attribute12,
g.attribute13 = p_attribute13,
g.attribute14 = p_attribute14,
g.attribute15 = p_attribute15,
g.attribute16 = p_attribute16,
g.attribute17 = p_attribute17,
g.attribute18 = p_attribute18,
g.attribute19 = p_attribute19,
g.attribute20 = p_attribute20
where g.rowid = chartorowid(P_ROW_ID);
end update_row;
select 'x'
FROM per_all_assignments_f
WHERE grade_id = p_grd_id;
select 'x'
FROM per_valid_grades
WHERE grade_id = p_grd_id;
select 'x'
FROM per_vacancies
WHERE grade_id = p_grd_id;
select 'x'
FROM pay_element_links
WHERE grade_id = p_grd_id;
select 'x'
FROM per_budget_elements
WHERE grade_id = p_grd_id;
select 'x'
FROM per_grade_spines
WHERE grade_id = p_grd_id;
select 'x'
FROM pay_grade_rules
WHERE grade_or_spinal_point_id = p_grd_id
AND rate_type = 'G';
UPDATE pay_grade_rules_f
SET sequence = p_seq,
last_update_date = SYSDATE,
last_updated_by = p_lastup,
last_update_login = p_login
WHERE grade_or_spinal_point_id = p_grd_id
AND rate_type = 'G';
SELECT 'Y'
INTO l_exists
FROM sys.dual
WHERE EXISTS
(select 'x'
FROM per_valid_grades vg
WHERE vg.business_group_id = p_bgroup
AND vg.grade_id = p_grd_id);
DELETE FROM per_valid_grades vg
WHERE vg.business_group_id + 0 = p_bgroup
AND vg.grade_id = p_grd_id
AND vg.date_from > p_date_to;
UPDATE per_valid_grades vg
SET vg.date_to = (select least(
nvl(p.date_end,p_eot),
nvl(j.date_to,p_eot),
nvl(p_date_to,p_eot))
from hr_positions p,
per_jobs_v j,
per_valid_grades v
where v.valid_grade_id = vg.valid_grade_id
and v.position_id = p.position_id(+)
and v.job_id = j.job_id(+)
)
WHERE vg.business_group_id + 0 = p_bgroup
AND vg.grade_id = p_grd_id
AND (NVL(vg.date_to,p_eot) > p_date_to
OR vg.date_to = p_date_to_old);
UPDATE per_valid_grades vg
SET vg.date_to = null
WHERE vg.date_to = p_eot
AND vg.grade_id = p_grd_id;
SELECT a.grade_structure
FROM per_business_groups a,
fnd_compiled_id_flexs fcf,
fnd_id_flex_structures fif
WHERE a.business_group_id + 0 = p_b_group
AND fcf.id_flex_code = 'GRD'
AND fcf.application_id = fif.application_id
/* FIX for WWBUG 1523904 */
AND to_char(fif.id_flex_num) = a.grade_structure
/* End of fix for 1523904 */
AND UPPER(fif.dynamic_inserts_allowed_flag) = 'Y'
AND rownum = 1;
SELECT 'x'
FROM per_valid_grades
WHERE grade_id = p_grd_id
AND p_date_from > date_from;
select 'x'
FROM per_grades p
WHERE (p.ROWID <> p_rwid OR p_rwid IS NULL)
AND (p.grade_id <> p_grd_id OR p_grd_id IS NULL)
AND p.business_group_id + 0 = p_bgroup_id
AND p.grade_definition_id = p_grdef_id;
SELECT 'Y'
FROM per_grades p
WHERE (p_rwid IS NULL OR p_rwid <> p.ROWID)
AND p_bgroup_id = p.business_group_id + 0
AND p_seg = p.name;
select date_to
from per_grades
where grade_id = p_grd_id;
select 'x'
from per_grades g
where g.sequence = p_seq
and g.business_group_id + 0 = p_bgroup
and (p_rwid is null or chartorowid(p_rwid) <> g.rowid);
select 'x' from pay_grade_rules_f p
where p.grade_or_spinal_point_id = p_grade_id
and p.rate_type='G' -- Bug fix 4024588
and p.effective_start_date < nvl(p_date_from, hr_api.g_sot);
select 'x' from pay_grade_rules_f p
where p.grade_or_spinal_point_id = p_grade_id
and p.rate_type='G' -- Bug fix 3360504. Type check added.
and p.effective_end_date > nvl(p_date_to, hr_api.g_eot);
select 'x' from per_grade_spines_f pgs
where pgs.grade_id = p_grade_id
and pgs.effective_end_date > nvl(p_date_to, hr_api.g_eot);