[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cvl.name
,cmpe.competence_element_id
,cmpe.object_version_number
,cmpe.competence_id
,cmpe.proficiency_level_id
,ratl.step_value
,cmpe.effective_date_from
,cmpe.effective_date_to
,cmpe.certification_date
,cmpe.certification_method
,hr_general.decode_lookup ('CERTIFICATION_METHOD' ,cmpe.certification_method) certification_method_desc
,cmpe.next_certification_date
,cmpe.source_of_proficiency_level
,hr_general.decode_lookup ('PROFICIENCY_SOURCE' ,cmpe.source_of_proficiency_level) src_proficiency_lvl_desc
,cmpe.comments
,cvl.competence_alias
,cmpe.status -- added for Competnce Qualification link enahncement
from per_rating_levels ratl
,per_competence_elements cmpe
,per_competences_vl cvl
where cmpe.competence_element_id = p_competence_element_id
and cmpe.competence_id = cvl.competence_id
and cmpe.proficiency_level_id = ratl.rating_level_id (+)
and trunc(sysdate) between nvl(cmpe.effective_date_from, trunc(sysdate))
and nvl(cmpe.effective_date_to, trunc(sysdate));
select step_value from per_rating_levels
where rating_level_id = p_proficiency_level_id;
select a.step_value
from per_rating_levels a,per_competence_elements b
where b.proficiency_level_id = a.rating_level_id(+)
and b.competence_element_id = p_competence_element_id;
select proficiency_level_id
from per_competence_elements
where competence_element_id = p_competence_element_id;
select name
from per_competences_vl
where competence_id = p_competence_id;
select effective_date_from
from per_competence_elements
where competence_element_id = p_competence_element_id;
select competence_alias
from per_competences_vl
where competence_id = p_competence_id;
select varchar2_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
SELECT status
FROM per_competence_elements
WHERE competence_element_id = p_competence_element_id;
select 'F' status
from hr_api_transaction_steps s, hr_api_transaction_values a,
hr_api_transaction_values b
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.transaction_step_id = a.transaction_step_id
and s.transaction_step_id = b.transaction_step_id
and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
union
select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
and ts.transaction_id = t.transaction_id
and t.selected_person_id = p_person_id and t.status = 'Y'
and exists (Select 'e' From hr_api_transaction_values c
Where c.transaction_step_id = ts.transaction_step_id
and c.name = 'P_COMPETENCE_ID'
and c.number_value = p_competence_id);
select varchar2_value into l_mode_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
select max(effective_date_from)
into l_eff_date_from
from per_competence_elements
where person_id = to_number(p_person_id)
and competence_id = p_competence_id group by competence_id;
select competence_element_id, object_version_number
into l_competence_element_id, l_obj_ver_num
from per_competence_elements
where person_id = to_number(p_person_id)
and effective_date_from = l_eff_date_from
and competence_id = p_competence_id;
select number_value
into l_competence_element_id
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and NAME = 'P_UPGRADED_FROM_REC_ID';
select a.transaction_step_id
into l_transaction_step_id
from hr_api_transaction_values a,
hr_api_transaction_values b,
hr_api_transaction_steps steps
where steps.transaction_id = l_transaction_id
and steps.transaction_step_id = a.transaction_step_id
and a.name = 'P_CHANGE_MODE'
and a.varchar2_value = 'UPGRADE'
and steps.transaction_step_id = b.transaction_step_id
and b.name = 'P_COMPETENCE_ELEMENT_ID'
and b.number_value = l_competence_element_id
and a.transaction_step_id <> l_competence_element_id;
update hr_api_transaction_values
set date_value = (x_eff_date_from - 1)
where transaction_step_id = l_transaction_step_id
and name = 'P_EFF_DATE_TO';
ELSIF p_change_mode = 'UPDATE_UPDATE' THEN
hr_utility.set_location(l_proc,85);
,p_change_mode => 'UPDATE_APPLY'
,p_sys_generated => 'N'
,p_upg_from_rec_id => -1
,p_status => p_competence_status);
Procedure process_save_currentupdate
(p_item_type in wf_items.item_type%type
,p_item_key in wf_items.item_key%type
,p_actid in varchar2
,p_person_id in number
,p_change_mode in varchar2 default null
,p_preupd_obj_vers_num in number default null
,p_competence_id in number default null
,p_competence_element_id in number default null
,p_competence_name in varchar2 default null
,p_competence_alias in varchar2 default null
,p_proficiency_level_id in number default null
,p_step_value in number default null
,p_preupd_proficy_lvl_id in number default null
,p_certification_mthd in varchar2 default null
,p_proficy_lvl_source in varchar2 default null
,p_eff_date_from in varchar2 default null
,p_eff_date_to in varchar2 default null
,p_certification_date in varchar2 default null
,p_next_certifctn_date in varchar2 default null
,p_comments in varchar2 default null
,p_prev_step_value in number default null
,p_prev_start_date in varchar2 default null
,p_competence_status IN VARCHAR2 DEFAULT null
,transaction_step_id in number default null) is
--
l_proc varchar2(200) := g_package || 'process_save_currentupdate';
l_wf_update_mode varchar2(100) default null;
IF p_change_mode = 'UPDATE_APPLY' THEN
hr_utility.set_location(l_proc,75);
,p_change_mode => 'UPDATE_HIST'
,p_sys_generated => 'Y'
,p_upg_from_rec_id => -1
,p_prev_status => l_preupd_cmptnce_row.status
,p_status => p_competence_status);
,p_change_mode => 'UPDATE_APPLY'
,p_sys_generated => 'N'
,p_upg_from_rec_id => p_competence_element_id
,p_status => p_competence_status);
ELSIF p_change_mode = 'UPDATE_UPDATE' THEN
hr_utility.set_location(l_proc,95);
,p_change_mode => 'UPDATE_APPLY'
,p_sys_generated => 'N'
,p_upg_from_rec_id => -1
,p_status => p_competence_status);
,p_change_mode => 'UPDATE_APPLY'
,p_sys_generated => 'N'
,p_upg_from_rec_id => -1
,p_status => p_competence_status);
End process_save_currentupdate;
Select ITEM_TYPE,ITEM_KEY,ACTIVITY_ID
INTO l_item_type, l_item_key, l_activity_id
FROM hr_api_transaction_steps
WHERE transaction_step_id = p_transaction_step_id;
Select pce.status, Achieved_date INTO l_status, l_achieved_date
FROM
per_competence_elements pce
Where pce.competence_element_id = l_cmp_element_rec.competence_element_id;
Select s.transaction_step_id
FROM hr_api_transaction_steps s,
hr_api_transaction_values c, hr_api_transaction_values d,
hr_api_transaction_values e, hr_api_transaction_values f
WHERE
s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = p_activity_id
AND s.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
and c.transaction_step_id = s.transaction_step_id
and d.transaction_step_id = s.transaction_step_id
and e.transaction_step_id = s.transaction_step_id
and f.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_PERSON_ID'
AND d.NAME = 'P_COMPETENCE_ELEMENT_ID'
AND e.NAME = 'P_COMPETENCE_ID'
AND f.NAME = 'P_CHANGE_MODE'
AND c.number_value = p_person_id
AND d.number_value = p_competence_element_id
AND e.number_value = p_competence_id
AND f.varchar2_value = p_change_mode ;
select date_to end_date,
date_from start_date
from per_competences_vl
where competence_id = p_competence_id;
delete_transaction_step_id(p_transaction_step_id);
select varchar2_value into l_mode_fetch
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_CHANGE_MODE';
if l_mode_fetch = 'UPGRADE' or l_mode_fetch = 'UPDATE_APPLY' then
hr_utility.set_location(l_proc,155);
select varchar2_value into m_mode from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_CHANGE_MODE';
select number_value into l_prev_step_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_PREV_STEP_VALUE';
select number_value into l_prev_prof_lvl_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_PREUPD_PROFICY_LVL_ID';
select date_value into l_prev_start_date
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_PREV_START_DATE';
delete_transaction_step_id(rec.transaction_step_id);
hr_utility.set_location('after calling delete_transaction_step_id ',190);
select step.transaction_step_id
from hr_api_transaction_steps step, hr_api_transaction_values val
where step.transaction_id = p_transaction_id
and val.transaction_step_id = step.transaction_step_id
and val.varchar2_value = 'ADD';
select steps.transaction_step_id
from hr_api_transaction_values val, hr_api_transaction_steps steps
where steps.transaction_id = p_transaction_id
and steps.transaction_step_id = val.transaction_step_id
and val.name = 'P_CHANGE_MODE'
and val.varchar2_value IN ('UPDATE_APPLY','UPDATE_HIST');
delete from hr_api_transaction_values
where transaction_step_id = I.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_step_id = I.transaction_step_id;
select steps.transaction_step_id
from hr_api_transaction_values val, hr_api_transaction_steps steps
where steps.transaction_id = p_transaction_id
and steps.transaction_step_id = val.transaction_step_id
and val.varchar2_value = 'ADD';
delete from hr_api_transaction_values
where transaction_step_id = I.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_id = l_transaction_id
and transaction_step_id = I.transaction_step_id;
PROCEDURE delete_all_ids
(p_item_type in varchar2
,p_item_key in varchar2) is
l_proc varchar2(200) := g_package || 'delete_all_ids';
select transaction_step_id
from hr_api_transaction_steps
where transaction_id = p_transaction_id;
delete from hr_api_transaction_values
where transaction_step_id = l_result.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_id = l_transaction_id
and transaction_step_id = l_result.transaction_step_id;
end delete_all_ids;
PROCEDURE delete_transaction_step_id
(p_transaction_step_id IN number) is
l_proc varchar2(200) := g_package || 'delete_transaction_step_id';
select transaction_id into l_transaction_id
from hr_api_transaction_steps
where transaction_step_id = l_transaction_step_id
and rownum = 1;
select varchar2_value
into l_mode
from hr_api_transaction_values
where transaction_step_id = l_transaction_step_id
and name = 'P_CHANGE_MODE';
select number_value
into l_competence_element_id
from hr_api_transaction_values
where transaction_step_id = l_transaction_step_id
and NAME = 'P_UPGRADED_FROM_REC_ID';
select a.transaction_step_id into l_txid
from hr_api_transaction_values a,hr_api_transaction_values b,
hr_api_transaction_steps steps
where steps.transaction_id = l_transaction_id
and steps.transaction_step_id = a.transaction_step_id
and a.name = 'P_CHANGE_MODE'
and a.varchar2_value = 'UPGRADE'
and steps.transaction_step_id = b.transaction_step_id
and b.name = 'P_COMPETENCE_ELEMENT_ID'
and b.number_value = l_competence_element_id
and a.transaction_step_id <> l_transaction_step_id;
delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
delete from hr_api_transaction_values where transaction_step_id = l_txid ;
delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
PROCEDURE process_save_update_details
(p_item_type in wf_items.item_type%type
,p_item_key in wf_items.item_key%type
,p_actid in varchar2
,p_person_id in number
,p_proficiency_level_id in number default null
,p_step_value in number default null
,p_eff_date_from in varchar2 default null
,p_prev_step_value in number default null
,p_competence_status IN VARCHAR2 DEFAULT null
,transaction_step_id in number ) is
l_proc varchar2(200) := g_package || 'process_save_update_details';
l_trans_tbl(l_count).param_value := 'UPDATE_APPLY';
END process_save_update_details;
procedure save_update_details
(p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in varchar2
,p_pid in number
,p_competence_id in number
,p_competence_element_id in number default null
,p_proficiency_level_id in number default null
,p_eff_date_from in varchar2 default null
,p_comments in varchar2 default null
,p_eff_date_to in varchar2 default null
,p_proficy_lvl_source in varchar2 default null
,p_certification_mthd in varchar2 default null
,p_certification_date in varchar2 default null
,p_next_certifctn_date in varchar2 default null
,p_preupd_obj_vers_num in number default null
,p_transaction_step_id in number
,p_prev_eff_date_from in varchar2 default null
,p_pre_eff_date_to in varchar2 default null
,p_competence_status in varchar2 default null
,p_error_message out nocopy long) is
l_proc varchar2(200) := g_package || 'save_update_details';
end save_update_details;
PROCEDURE final_update_save
(p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in varchar2
,p_competence_element_id in number default null
,p_pid in number
,p_proficiency_level_id in number default null
,p_eff_date_from in varchar2 default null
,p_step_value in number
,p_transaction_step_id in number
,p_competence_status IN VARCHAR2 ) is
l_proc varchar2(200) := g_package || 'final_update_save';
update hr_api_transaction_values val
set val.varchar2_value = 'UPGRADE'
where val.transaction_step_id in (select val1.transaction_step_id
from hr_api_transaction_values val1,
hr_api_transaction_steps steps
where steps.transaction_id = l_transaction_id
and steps.transaction_step_id = val1.transaction_step_id
and val1.name = 'P_COMPETENCE_ELEMENT_ID'
and val1.number_value = p_competence_element_id)
and val.name = 'P_CHANGE_MODE'
and val.varchar2_value = 'UPDATE_HIST';
select number_value into l_competence_element_id
from hr_api_transaction_values
where transaction_step_id = l_transaction_step_id
and NAME = 'P_UPGRADED_FROM_REC_ID';
select a.transaction_step_id into l_tx_step_id
from hr_api_transaction_values a,hr_api_transaction_values b,
hr_api_transaction_steps steps
where steps.transaction_id = l_transaction_id
and steps.transaction_step_id = a.transaction_step_id
and a.name = 'P_CHANGE_MODE'
and a.varchar2_value = 'UPGRADE'
and steps.transaction_step_id = b.transaction_step_id
and b.name = 'P_COMPETENCE_ELEMENT_ID'
and b.number_value = l_competence_element_id
and a.transaction_step_id <> l_transaction_step_id;
update hr_api_transaction_values
set date_value = l_preupd_date_to
where transaction_step_id = l_tx_step_id
and name = 'P_EFF_DATE_TO';
end final_update_save;
select upper(rtrim(ltrim(p_competence_name))), upper(rtrim(ltrim(p_competence_alias)))
into p_competence_name, p_competence_alias
from dual;
select name,competence_alias,competence_id into p_competence_name,p_competence_alias,p_competence_id
from per_competences_vl
where upper(name) = p_competence_name or upper(competence_alias) = p_competence_alias
and (business_group_id+0 = p_business_group_id
or business_group_id is null);
select competence_id into l_competence_id from per_competences_vl
where trim(upper(competence_alias)) = trim(upper(p_competence_alias));
select competence_id into l_competence_id from per_competences_vl
where trim(upper(name)) = trim(upper(p_competence_name));
select competence_id into l_competence_id from per_competences_vl
where ( (trim(upper(name))) = (trim(upper(p_competence_name)))) or ((trim(upper(competence_alias))) = (trim(upper(p_competence_alias))) );
procedure validate_updated_row
(p_competence_id in varchar2
,p_step_value in varchar2
,p_person_id in varchar2
,p_eff_date_from in varchar2 default null
,r_step_value out nocopy varchar2
,r_new_prof_level out nocopy varchar2
,p_item_type in varchar2 default null
,p_item_key in varchar2 default null
,p_activity_id in varchar2 default null
,p_error_message out nocopy varchar2) is
l_proc varchar2(200) := g_package || 'validate_updated_row';
update_date_validate (p_person_id => p_person_id
,p_competence_id => p_competence_id
,p_eff_date_from => p_eff_date_from
,p_error_message => p_error_message);
select ratl.step_value,ratl.rating_level_id
into l_step_value,l_new_prof_level
from per_competences_vl cvl, per_rating_levels ratl
where ((ratl.competence_id = cvl.competence_id and cvl.competence_id = p_competence_id)
or (cvl.rating_scale_id = ratl.rating_scale_id and cvl.competence_id = p_competence_id))
and ratl.step_value = p_step_value;
Select count(*) INTO l_count
FROM hr_api_transaction_steps S,
hr_api_transaction_values A,
hr_api_transaction_values C,
hr_api_transaction_values D
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl((p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND c.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_COMPETENCE_ID'
AND c.number_value = p_competence_id
AND a.transaction_step_id = s.transaction_step_id
AND a.NAME = 'P_DATE_FROM'
AND d.transaction_step_id = s.transaction_step_id
AND d.NAME = 'P_PERSON_ID'
AND d.number_value = p_person_id
AND a.date_value < to_date(p_eff_date_from, g_date_format);
Select count(*) INTO l_count
FROM hr_api_transaction_steps S,
hr_api_transaction_values A,
hr_api_transaction_values C,
hr_api_transaction_values D
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl((p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND c.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_COMPETENCE_ID'
AND c.number_value = p_competence_id
AND a.transaction_step_id = s.transaction_step_id
AND a.NAME = 'P_DATE_FROM'
AND d.transaction_step_id = s.transaction_step_id
AND d.NAME = 'P_PERSON_ID'
AND d.number_value = p_person_id
AND a.date_value < to_date(p_eff_date_from, g_date_format);
end validate_updated_row;
select name,competence_alias
into l_competence_name,l_competence_alias from per_competences_vl
where competence_id = p_competence_id;
select to_date(date_from, g_date_format) start_date
from per_competences_vl
where competence_id = p_competence_id;
select 'F' status
from hr_api_transaction_steps s, hr_api_transaction_values a,
hr_api_transaction_values b, hr_api_transaction_values d
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.transaction_step_id = a.transaction_step_id
and s.transaction_step_id = b.transaction_step_id
and s.transaction_step_id = d.transaction_step_id
and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
and d.name = 'P_CHANGE_MODE' and d.varchar2_value <> 'ADD'
union
select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
and ts.transaction_id = t.transaction_id
and t.selected_person_id = p_person_id and t.status = 'Y'
and exists (Select 'e' From hr_api_transaction_values c
Where c.transaction_step_id = ts.transaction_step_id
and c.name = 'P_COMPETENCE_ID'
and c.number_value = p_competence_id)
union
Select 'F' status from per_competence_elements pce
where pce.person_id = p_person_id
and pce.type = 'PERSONAL'
and trunc(sysdate) between nvl(pce.effective_date_from,sysdate)
and nvl(pce.effective_date_to,sysdate)
and pce.competence_id = p_competence_id;
select rtrim(ltrim(p_competence_name)), rtrim(ltrim(p_competence_alias)), rtrim(ltrim(p_competence_id)), rtrim(ltrim(p_step_value))
into p_competence_name, p_competence_alias, p_competence_id, p_step_value
from dual;
select name, competence_alias
into l_competence_name, l_competence_alias
from per_competences_vl
where competence_id = p_competence_id;
select name, competence_alias, competence_id
into p_competence_name,p_competence_alias,p_competence_id
from per_competences_vl
where upper(name) = nvl(upper(p_competence_name), upper(name))
and nvl(upper(competence_alias),'#') = nvl(upper(p_competence_alias), nvl(upper(competence_alias),'#'))
and (business_group_id+0 = p_business_group_id or business_group_id is null);
select per_rating_levels.step_value ,per_rating_levels.rating_level_id
into p_step_value,p_prof_level_id
from per_competences_vl, per_rating_levels
where ((per_rating_levels.competence_id = per_competences_vl.competence_id
or per_competences_vl.rating_scale_id = per_rating_levels.rating_scale_id)
and (per_competences_vl.competence_id = p_competence_id) and (per_rating_levels.step_value = p_step_value))
and (per_competences_vl.business_group_id+0 = p_business_group_id
or per_competences_vl.business_group_id is null);
Select count(*) INTO l_count
FROM hr_api_transaction_steps S,
hr_api_transaction_values A,
hr_api_transaction_values C,
hr_api_transaction_values D
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl((p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND c.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_COMPETENCE_ID'
AND c.number_value = p_competence_id
AND a.transaction_step_id = s.transaction_step_id
AND a.NAME = 'P_DATE_FROM'
AND d.transaction_step_id = s.transaction_step_id
AND d.NAME = 'P_PERSON_ID'
AND d.number_value = p_person_id
AND a.date_value < to_date(p_eff_date_from, g_date_format);
Select count(*) INTO l_count
FROM hr_api_transaction_steps S,
hr_api_transaction_values A,
hr_api_transaction_values C,
hr_api_transaction_values D
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl((p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND c.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_COMPETENCE_ID'
AND c.number_value = p_competence_id
AND a.transaction_step_id = s.transaction_step_id
AND a.NAME = 'P_DATE_TO'
AND d.transaction_step_id = s.transaction_step_id
AND d.NAME = 'P_PERSON_ID'
AND d.number_value = p_person_id
AND nvl(a.date_value,to_date(p_eff_date_to, g_date_format)) > to_date(p_eff_date_to, g_date_format);
select per_rating_levels.step_value ,per_rating_levels.rating_level_id
into l_step_value,l_prof_level_id
from per_competences_vl, per_rating_levels
where ((per_rating_levels.competence_id = per_competences_vl.competence_id or per_competences_vl.rating_scale_id = per_rating_levels.rating_scale_id)
and (per_competences_vl.competence_id = p_competence_id) and (per_rating_levels.step_value = p_step_value) );
Procedure delete_add_page
(transaction_step_ids in varchar2) is
l_proc varchar2(200) := g_package || 'delete_add_page';
/* HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
p_transaction_step_id => to_number(j)); */
delete from hr_api_transaction_values where transaction_step_id = to_number(j);
delete from hr_api_transaction_steps where transaction_step_id = to_number(j);
end delete_add_page;
update hr_api_transaction_values val
set val.varchar2_value = 'ADDITION'
where transaction_step_id in (select transaction_step_id
from hr_api_transaction_steps steps
where steps.transaction_id = l_transaction_id)
and val.name = 'P_CHANGE_MODE' and val.varchar2_value = 'ADD';
procedure update_date_validate
(p_person_id in varchar2 default null
,p_competence_id in varchar2 default null
,p_eff_date_from in varchar2 default null
,p_error_message out nocopy varchar2) is
l_proc varchar2(200) := g_package || 'update_date_validate';
select max(effective_date_from)
into l_eff_date_from
from per_competence_elements
where person_id = to_number(p_person_id)
and competence_id = to_number(p_competence_id) group by competence_id;
end update_date_validate;
select effective_date_to
into m_eff_date_to
from per_competence_elements
where person_id = p_person_id
and competence_id = p_competence_id
and effective_date_to < trunc(sysdate);
select transaction_step_id
from hr_api_transaction_steps
where item_type = p_item_type
and item_key = p_item_key
and activity_id = p_activity_id;
select competence_element_id
,competence_id
,proficiency_level_id
,effective_date_from
,effective_date_to
,certification_date
,certification_method
,next_certification_date
,source_of_proficiency_level
,comments
,status
from per_competence_elements
where competence_element_id = p_competence_element_id;
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 50);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 75);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 100);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 125);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 150);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 180);
ELSE -- pre-update is null
hr_utility.set_location( l_proc , 210);
Select rl1.step_value || decode(rl1.name, '', '', ' ' || rl1.name) minprof,
rl2.step_value || decode(rl2.name, '', '', ' ' || rl2.name) maxprof
From per_competence_elements pce, per_all_assignments_f paaf,
per_rating_levels rl1, per_rating_levels rl2
Where paaf.person_id = p_person_id
And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
And pce.enterprise_id = paaf.business_group_id
And pce.competence_id = p_competence_id
And pce.type = 'REQUIREMENT'
And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
And proficiency_level_id = rl1.rating_level_id (+)
And high_proficiency_level_id = rl2.rating_level_id (+);
Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
From per_competence_elements pce, per_all_assignments_f paaf,
per_rating_levels rl1, per_rating_levels rl2
Where paaf.person_id = p_person_id
And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
And pce.job_id = paaf.job_id
And pce.competence_id = p_competence_id
And pce.type = 'REQUIREMENT'
And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
And proficiency_level_id = rl1.rating_level_id (+)
And high_proficiency_level_id = rl2.rating_level_id (+);
Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
From per_competence_elements pce, per_all_assignments_f paaf,
per_rating_levels rl1, per_rating_levels rl2
Where paaf.person_id = p_person_id
And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
And pce.position_id = paaf.position_id
And pce.competence_id = p_competence_id
And pce.type = 'REQUIREMENT'
And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
And proficiency_level_id = rl1.rating_level_id (+)
And high_proficiency_level_id = rl2.rating_level_id (+);
Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
From per_competence_elements pce, per_all_assignments_f paaf,
per_rating_levels rl1, per_rating_levels rl2
Where paaf.person_id = p_person_id
And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
And pce.organization_id = paaf.organization_id
And pce.competence_id = p_competence_id
And pce.type = 'REQUIREMENT'
And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
And proficiency_level_id = rl1.rating_level_id (+)
And high_proficiency_level_id = rl2.rating_level_id (+);
Select 'Y'
From per_competence_elements pce, per_all_assignments_f paaf, per_competences pc
Where paaf.person_id = p_person_id
And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
And pc.competence_id = p_competence_id
And pce.competence_id = pc.competence_id
And pce.type = 'REQUIREMENT'
And pce.mandatory = 'Y'
And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
And (pce.job_id = paaf.job_id
Or pce.organization_id = paaf.organization_id
Or pce.position_id = paaf.position_id
Or pce.enterprise_id = paaf.business_group_id);