[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select max(ceo.date_to),ceo.outcome_id
FROM per_comp_element_outcomes ceo , per_competence_outcomes pco
where competence_element_id = compEleId
AND ceo.date_to < p_eff_date
and pco.outcome_id = ceo.outcome_id
and pco.date_from <= p_eff_date
and nvl(pco.date_to,p_eff_date) >= p_eff_date
group by ceo.outcome_id;
Select competence_cluster INTO l_competence_cluster FROM per_competences_vl
WHERE competence_id = p_competence_id;
Select pce.status, pce.Achieved_date, pce.person_id
INTO l_status, l_achieved_date, p_person_id
FROM per_competence_elements pce
Where pce.competence_element_id = p_competence_element_id;
Select count(*) INTO l_noof_outcomes
FROM Per_comp_element_outcomes CEO
Where ceo.outcome_id = EndDatedRec.outcome_id
AND ceo.competence_element_id = p_competence_element_id
AND ceo.date_from <= p_eff_date
AND nvl(ceo.date_to,p_eff_date) >= p_eff_date;
Select count(*) INTO l_noof_outcomes
FROM hr_api_transaction_steps S, hr_api_transaction_values tv,
hr_api_transaction_values tv1, hr_api_transaction_values tv2,
hr_api_transaction_values tv3
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.PROCESS_API'
and tv.transaction_step_id = s.transaction_step_id
and tv1.transaction_step_id = s.transaction_step_id
and tv2.transaction_step_id = s.transaction_step_id
and tv3.transaction_step_id = s.transaction_step_id
AND tv3.NAME = 'P_COMPETENCE_ELEMENT_ID'
AND tv3.Number_Value = p_competence_element_id
AND tv.NAME = 'P_OUTCOME_ID'
AND tv1.NAME = 'P_DATE_FROM'
AND tv2.NAME = 'P_DATE_TO'
AND tv.number_value = EndDatedRec.outcome_id
AND tv1.date_value <= p_eff_date
AND nvl(tv2.date_value,p_eff_date) >= p_eff_date;
Select count(*) into l_noof_outcomes 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(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
AND ceo.Competence_element_id = p_competence_element_id;
Select count(*) INTO l_noof_outcomes
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, per_competence_outcomes pco
Where ts.ITEM_TYPE = p_item_type
AND ts.item_key = p_item_key
And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
AND tv3.NAME = 'P_PERSON_ID'
AND tv3.Number_Value = p_person_id
AND tv.NAME = 'P_OUTCOME_ID'
AND tv1.NAME = 'P_DATE_FROM'
AND tv2.NAME = 'P_DATE_TO'
And tv.number_value = pco.outcome_id
AND pco.competence_id = p_competence_id
AND nvl(p_eff_date,trunc(sysdate)) BETWEEN pco.date_from AND nvl(pco.date_to,nvl(p_eff_date,trunc(sysdate)))
AND nvl(tv2.date_value,trunc(p_eff_date)) < trunc(p_eff_date);
Select count(*) INTO l_noof_outcomes from
per_competence_outcomes pco
WHERE competence_id = p_competence_id
AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
AND NOT EXISTS ( (Select 1 FROM per_comp_element_outcomes
Where competence_element_id = p_competence_element_id
AND per_comp_element_outcomes.outcome_id = pco.outcome_id
AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
AND NOT EXISTS
(Select 1 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(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
AND ceo.Competence_element_id = p_competence_element_id
AND ceo.Outcome_id = pco.Outcome_id))
Union All
(Select 1 FROM hr_api_transaction_values tv,
hr_api_transaction_values tv1, hr_api_transaction_values tv2,
hr_api_transaction_values tv3 , hr_api_transaction_steps ts
Where ts.ITEM_TYPE = p_item_type
AND ts.item_key = p_item_key
And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
AND tv3.NAME = 'P_PERSON_ID'
AND tv3.Number_Value = p_person_id
AND tv.NAME = 'P_OUTCOME_ID'
AND tv1.NAME = 'P_DATE_FROM'
AND tv2.NAME(+) = 'P_DATE_TO'
And tv.number_value = pco.outcome_id
And trunc(p_eff_date) BETWEEN tv1.date_value
AND nvl(tv2.date_value,trunc(p_eff_date))));
Select count(*) INTO l_noof_outcomes from
per_competence_outcomes pco
Where pco.Competence_id = p_competence_id
AND pco.date_from <= p_eff_date
AND nvl(pco.date_to,p_eff_date) >= p_eff_date;
Select HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_COMP_STATUS', l_status_id) INTO l_status_meaning
FROM dual;
Select competence_cluster INTO l_competence_cluster FROM per_competences_vl
WHERE competence_id = p_competence_id;
Select count(*) INTO l_noof_outcomes
from
per_competence_outcomes pco
WHERE pco.competence_id = p_competence_id
AND nvl(p_eff_date,sysdate) BETWEEN pco.date_from AND nvl(pco.date_to,nvl(p_eff_date,trunc(sysdate)))
AND NOT exists (Select 1 FROM hr_api_transaction_values tv,
hr_api_transaction_values tv1, hr_api_transaction_values tv2,
hr_api_transaction_values tv3 , hr_api_transaction_steps ts
Where ts.ITEM_TYPE = p_item_type
AND ts.item_key = p_item_key
And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
AND tv3.NAME = 'P_PERSON_ID'
AND tv3.Number_Value = p_person_id
AND tv.NAME = 'P_OUTCOME_ID'
AND tv1.NAME = 'P_DATE_FROM'
AND tv2.NAME(+) = 'P_DATE_TO'
And tv.number_value = pco.outcome_id
And trunc(p_eff_date) BETWEEN tv1.date_value
AND nvl(tv2.date_value,trunc(p_eff_date))
Union all
(Select 1 FROM Per_comp_element_outcomes CEO
Where ceo.Competence_element_id = p_competence_element_id
AND ceo.Outcome_id = pco.Outcome_id
And ceo.date_from <= trunc(p_eff_date)
AND nvl(ceo.date_to,trunc(p_eff_date)) >= trunc(p_eff_date)
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 l_noof_outcomes
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, per_competence_outcomes pco
Where ts.ITEM_TYPE = p_item_type
AND ts.item_key = p_item_key
And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
AND tv3.NAME = 'P_PERSON_ID'
AND tv3.Number_Value = p_person_id
AND tv.NAME = 'P_OUTCOME_ID'
AND tv1.NAME = 'P_DATE_FROM'
AND tv2.NAME(+) = 'P_DATE_TO'
And tv.number_value = pco.outcome_id
AND pco.competence_id = p_competence_id
AND p_eff_date BETWEEN pco.date_from AND nvl(pco.date_to,p_eff_date)
AND nvl(tv2.date_value,p_eff_date) < p_eff_date;
SELECT pce.competence_id from
per_competence_elements pce
Where pce.TYPE = 'QUALIFICATION'
AND pce.Qualification_type_id = p_qualification_type_id
AND pce.effective_date_from <= trunc(sysdate)
AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);
Select competence_element_id from
per_competence_elements pce
Where pce.TYPE = 'PERSONAL'
AND pce.competence_id = p_competence_id
AND pce.person_id = p_person_id
AND pce.effective_date_from <= trunc(sysdate)
AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);
Select count(*) INTO l_TotComps
from
per_competence_elements pce
Where pce.TYPE = 'QUALIFICATION'
AND pce.Qualification_type_id = p_qualification_type_id
AND pce.effective_date_from <= trunc(sysdate)
AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);