The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cvl.name
,cmpe.COMP_ELEMENT_OUTCOME_ID
,cmpe.object_version_number
,cmpe.COMPETENCE_ELEMENT_ID
,cmpe.OUTCOME_ID
,cmpe.date_from
,cmpe.date_to
from per_comp_element_outcomes cmpe
,per_competence_outcomes_VL cvl
where cmpe.comp_element_outcome_id = p_comp_element_outcome_id
and cmpe.outcome_id = cvl.outcome_id
and trunc(sysdate) between nvl(cmpe.date_from, trunc(sysdate))
and nvl(cmpe.date_to, trunc(sysdate));
select name
from per_competence_outcomes_vl
where outcome_id = p_outcome_id;
select date_from
from per_comp_element_outcomes
where comp_element_outcome_id = p_comp_element_outcome_id;
select varchar2_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
Procedure check_delete_rec(p_item_type IN varchar2
,p_item_key IN varchar2
,p_actid IN varchar2
,p_person_id IN number
,p_outcome_id IN number);
SELECT object_version_number FROM per_comp_element_outcomes
WHERE comp_element_outcome_id = p_comp_element_outcome_id;
Select ts.transaction_step_id
FROM hr_api_transaction_steps ts,
hr_api_transaction_values tv,
hr_api_transaction_values tv1,
hr_api_transaction_values tv2,
hr_api_transaction_values tv3,
hr_api_transaction_values tv4
Where ts.transaction_step_id = tv.transaction_step_id
AND ts.item_type = p_item_type
AND ts.item_key = p_item_key
AND ts.activity_id = p_activity_id
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
And tv4.transaction_step_id = ts.transaction_step_id
And tv4.NAME = 'P_PERSON_ID'
AND tv4.Number_Value = p_person_id
AND tv1.transaction_step_id(+) = ts.transaction_step_id
AND tv.NAME = 'P_COMPETENCE_ID'
AND tv.number_value = p_comp_id
AND tv1.NAME(+) = 'P_COMPETENCE_ELEMENT_ID'
AND tv1.number_value(+) = p_comp_ele_id
AND tv2.transaction_step_id = ts.transaction_step_id
AND tv2.NAME = 'P_DATE_FROM'
AND tv3.transaction_step_id = ts.transaction_step_id
AND tv3.NAME = 'P_DATE_TO';
Select ceo.comp_element_outcome_id ,ts.transaction_step_id,ceo.object_version_number
FROM
per_comp_element_outcomes ceo,
hr_api_transaction_steps ts,
hr_api_transaction_values tv
where
ceo.competence_element_id = p_comp_ele_id
AND ts.item_type = p_item_type
AND ts.item_key = p_item_key
AND ts.activity_id = p_activity_id
AND ts.transaction_step_id = tv.transaction_step_id
AND tv.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
AND tv.number_value = ceo.COMP_ELEMENT_OUTCOME_ID;
Select co.outcome_id
,ceo.DATE_FROM
,ceo.DATE_TO
,ceo.object_version_number
From per_Competence_Outcomes co
,Per_comp_element_outcomes ceo
Where ceo.COMPETENCE_ELEMENT_ID = p_comp_ele_id
AND co.outcome_id = ceo.outcome_id
AND NOT EXISTS (Select 1 FROM hr_api_transaction_values tv1,
hr_api_transaction_values tv2,
hr_api_transaction_values tv3,
hr_api_transaction_values tv4,
hr_api_transaction_steps s
WHERE tv1.transaction_step_id = s.transaction_step_id
and s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
And tv2.transaction_step_id = s.transaction_step_id
And tv3.transaction_step_id = s.transaction_step_id
AND tv4.transaction_step_id = s.transaction_step_id
AND tv4.NAME = 'P_PERSON_ID'
AND tv4.number_value = p_person_id
AND tv1.name = 'P_OUTCOME_ID'
AND tv1.number_value = co.OUTCOME_ID
AND tv2.name = 'P_DATE_FROM'
AND tv3.name = 'P_DATE_TO'
AND tv2.date_Value >= co.date_from
AND nvl(tv3.date_Value,trunc(sysdate)) <= nvl(co.date_to,nvl(tv3.date_Value,trunc(sysdate)))
and not exists (
Select 1 from per_comp_element_outcomes pco
Where pco.competence_element_id = ceo.COMPETENCE_ELEMENT_ID
and pco.outcome_id = co.OUTCOME_ID
and pco.date_from = tv2.date_value
and nvl(pco.date_to,to_date('01-01-1001','DD-MM-YYYY')) = nvl(tv3.
date_value,to_date('01-01-1001','DD-MM-YYYY')))
)
And NOT EXISTS (SELECT 1
FROM hr_api_transaction_steps S1,
hr_api_transaction_values C
Where s1.item_type = p_item_type
and s1.item_key = p_item_key
and s1.activity_id = nvl((p_activity_id),s1.activity_id)
and s1.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
and c.transaction_step_id = s1.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND ceo.comp_element_outcome_id = C.number_value);
Select ce.Effective_date_From,ce.Effective_date_to
From per_competence_elements ce
Where ce.COMPETENCE_ELEMENT_ID = p_new_comp_ele_id;
/* Select ceo.outcome_id,ceo.date_from,ceo.date_to,
ce.Effective_date_From,ce.Effective_date_to, ceo.object_version_number
From per_comp_element_outcomes ceo,
per_competence_elements ce
Where ceo.competence_element_id = p_comp_ele_id
AND ce.competence_element_id = p_new_comp_ele_id;*/
delete_transaction_step_id(outids.transaction_step_id);
Select COMPETENCE_ELEMENT_ID ,
OUTCOME_ID,
DATE_FROM,
DATE_TO
FROM per_comp_element_outcomes
Where comp_element_outcome_id = p_comp_element_outcome_id;
hr_utility.set_location(' Entering: Inserting new record' || l_proc,30);
select date_to end_date,
date_from start_date
from per_competence_outcomes_vl
where outcome_id = p_outcome_id;
delete_transaction_step_id(p_transaction_step_id);
Select count(*) INTO x_count
from per_comp_element_outcomes ceo
where competence_element_id = x_comp_ele_id
and outcome_id = p_outcome_id and
ceo.date_from <= to_date(p_date_from,g_date_format) and
nvl(ceo.date_to,trunc(sysdate)) >= to_date(p_date_from,g_date_format)
AND NOT exists(
Select 1 FROM
hr_api_transaction_steps S, hr_api_transaction_values C
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
Select count(*) INTO x_count
from per_comp_element_outcomes ceo
where competence_element_id = x_comp_ele_id
and outcome_id = p_outcome_id and
ceo.date_from >= to_date(p_date_from,g_date_format)
AND NOT exists(
Select 1 FROM
hr_api_transaction_steps S, hr_api_transaction_values C
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
Select count(*) INTO x_count
from per_comp_element_outcomes ceo
where competence_element_id = x_comp_ele_id
and outcome_id = p_outcome_id and
ceo.date_from >= to_date(p_date_from,g_date_format)
AND ceo.date_from <= to_date(p_date_to,g_date_format)
AND NOT exists(
Select 1 FROM
hr_api_transaction_steps S, hr_api_transaction_values C
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
delete_transaction_step_id(p_transaction_step_id);
check_delete_rec(p_item_type => p_item_type
,p_item_key => p_item_key
,p_actid => p_activity_id
,p_person_id => p_person_id
,p_outcome_id => p_outcome_id);
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
cursor get_all_ids (p_transaction_id number) is
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_transaction_step_id number;
CURSOR get_txn_step_id IS select transaction_id
from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id
and rownum = 1;
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 ;
END delete_transaction_step_id;
procedure save_update_details
(p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in varchar2
,p_outcome_id in number
,p_competence_element_id in number default null
,p_comp_element_outcome_id in number default null
,p_date_from in varchar2 default null
,p_date_to in varchar2 default null
,p_preupd_obj_vers_num in number default null
,p_transaction_step_id in number
,p_prev_date_from in varchar2 default null
,p_pre_date_to in varchar2 default null
,p_person_id IN VARCHAR2 DEFAULT null
,p_error_message out nocopy long) is
--
--
l_user_date_format varchar2(20) ;
l_proc := g_package || 'save_update_details';
end save_update_details;
Procedure delete_add_page
(p_transaction_step_id in number) is
--
CURSOR get_step_ids (txn_step_id IN number) is
Select outcome.transaction_step_id
FROM hr_api_transaction_steps outcome,
hr_api_transaction_values ocomp,
hr_api_transaction_values operson,
hr_api_transaction_values ofrom_dt,
hr_api_transaction_values oto_dt,
hr_api_transaction_steps competence,
hr_api_transaction_values ccomp,
hr_api_transaction_values cperson,
hr_api_transaction_values cfrom_dt,
hr_api_transaction_values cto_dt
Where competence.transaction_step_id = txn_step_id
AND ccomp.transaction_step_id = competence.transaction_step_id
AND cperson.transaction_step_id = competence.transaction_step_id
AND cfrom_dt.transaction_step_id = competence.transaction_step_id
AND cto_dt.transaction_step_id = competence.transaction_step_id
AND ccomp.NAME = 'P_COMPETENCE_ID'
AND cperson.NAME = 'P_PERSON_ID'
AND cfrom_dt.NAME = 'P_EFF_DATE_FROM'
AND cto_dt.NAME = 'P_EFF_DATE_TO'
And outcome.item_key = competence.item_key
And outcome.item_type = competence.item_type
And outcome.activity_id = competence.activity_id
And outcome.api_name = HR_COMP_OUTCOME_PROFILE_SS.g_api_name
AND ocomp.transaction_step_id = competence.transaction_step_id
AND operson.transaction_step_id = competence.transaction_step_id
AND ofrom_dt.transaction_step_id = competence.transaction_step_id
AND oto_dt.transaction_step_id = competence.transaction_step_id
AND ocomp.NAME = 'P_COMPETENCE_ID'
AND operson.NAME = 'P_PERSON_ID'
AND ofrom_dt.NAME = 'P_EFF_DATE_FROM'
AND oto_dt.NAME = 'P_EFF_DATE_TO'
And ocomp.number_value = ccomp.number_value
And operson.number_value = cperson.number_value
AND ofrom_dt.date_value >= cfrom_dt.date_value
AND nvl(oto_dt.date_value,trunc(sysdate)) <= nvl(cto_dt.date_value,trunc(sysdate));
l_proc := g_package || 'delete_add_page';
delete from hr_api_transaction_values where transaction_step_id = txnStepIds.transaction_step_id;
delete from hr_api_transaction_steps where transaction_step_id = txnStepIds.transaction_step_id;
end delete_add_page;
SELECT comp_element_outcome_id
,competence_element_id
,outcome_id
,date_from
,date_to
from per_comp_element_outcomes
where comp_element_outcome_id = p_comp_element_outcome_id;
ELSE -- pre-update is null
IF p_date_from is not null THEN
l_changed := true;
ELSE -- pre-update is null
IF p_date_to is not null THEN
l_changed := true;
PROCEDURE mark_for_delete
(p_item_type in varchar2
,p_item_key in varchar2
,p_activity_id in varchar2
,p_comp_element_outcome_id in number
,p_transaction_step_id in varchar2 default null
,p_error_message OUT nocopy long ) IS
l_transaction_id NUMBER DEFAULT null;
l_proc := g_package || 'mark_for_delete';
,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
,p_item_type => p_item_type
,p_item_key => p_item_key
,p_activity_id => p_activity_id
,p_transaction_step_id => l_transaction_step_id
,p_object_version_number => x_trans_ovn);
,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
,p_transaction_data => l_trans_tbl);
END mark_for_delete;
PROCEDURE check_delete_rec
( p_item_type IN varchar2
,p_item_key IN varchar2
,p_actid IN varchar2
,p_person_id IN number
,p_outcome_id IN number) IS
l_txn_step_id hr_api_transaction_steps.transaction_step_id%type;
l_proc := g_package || 'check_delete_rec';
SELECT s.transaction_step_id INTO l_txn_step_id
FROM hr_api_transaction_steps S,
hr_api_transaction_values C,
per_comp_element_outcomes ceo,
per_competence_elements pce
Where s.item_type = p_item_type
and s.item_key = p_item_key
and s.activity_id = nvl((p_actid),s.activity_id)
and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND ceo.outcome_id = p_outcome_id
AND ceo.Competence_Element_id = pce.competence_element_id
AND pce.PERSON_ID = p_person_id
AND ceo.comp_element_outcome_id = C.number_value;
delete_transaction_step_id(l_txn_step_id);
End check_delete_rec;
Select Count(*) INTO l_count
FROM hr_api_transaction_steps s, per_competence_outcomes_vl co,
hr_api_transaction_values a, hr_api_transaction_values b,
hr_api_transaction_values c, hr_api_transaction_values p
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 CO.COMPETENCE_ID =p_competence_id
AND co.date_from <= trunc(sysdate)
AND nvl(co.date_to,trunc(sysdate)) >= trunc(sysdate)
AND b.date_value >= co.date_from
AND nvl(c.date_Value,trunc(sysdate)) <= nvl(co.date_to, nvl(c.date_Value,trunc(sysdate)))
and a.name = 'P_OUTCOME_ID'
and a.transaction_step_id = s.transaction_step_id
and a.number_value= co.outcome_id
and b.name = 'P_DATE_FROM'
and b.transaction_step_id = s.transaction_step_id
and c.name = 'P_DATE_TO'
and p.transaction_step_id = s.transaction_step_id
and p.name = 'P_PERSON_ID'
and p.Number_Value = p_pid;
SELECT COUNT(*) INTO l_count
FROM hr_api_transaction_steps S,
hr_api_transaction_values C,
per_comp_element_outcomes ceo
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.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND ceo.Competence_Element_id = p_competence_element_id
AND ceo.comp_element_outcome_id = C.number_value;
Procedure delete(p_validate in boolean default false
,p_transaction_step_id in number
,p_effective_date in varchar2 default null) is
begin
null;
Select DISTINCT s.transaction_step_id
FROM hr_api_transaction_steps S,
hr_api_transaction_values C,
per_comp_element_outcomes ceo
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.DELETE'
and c.transaction_step_id = s.transaction_step_id
AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
AND c.number_value = ceo.comp_element_outcome_id
AND ceo.competence_element_id = p_competence_element_id;
Select DISTINCT s.transaction_step_id
FROM hr_api_transaction_steps S,
hr_api_transaction_values C
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_PROFILE_SS.PROCESS_API'
AND c.transaction_step_id = s.transaction_step_id
AND c.NAME = 'P_COMPETENCE_ELEMENT_ID'
AND c.number_value = p_competence_element_id;
Select DISTINCT s.transaction_step_id
FROM hr_api_transaction_steps S,
hr_api_transaction_values C
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_ELEMENT_ID'
AND c.number_value = p_competence_element_id;
delete_transaction_step_id(sid_cur.transaction_step_id);
delete_transaction_step_id(sid_comp_cur.transaction_step_id);
delete_transaction_step_id(sid_out_cur.transaction_step_id);