The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select nvl(pce.proficiency_level_id, -100)
from per_competence_elements pce, per_rating_levels prl
where pce.competence_id = p_comp_id
and pce.person_id = p_person_id
and prl.rating_level_id(+) = pce.proficiency_level_id
and prl.step_value >= (select step_value from per_rating_levels where rating_level_id = p_level_id)
and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
Select nvl(pce.proficiency_level_id, -100)
from per_competence_elements pce
where pce.competence_id = p_comp_id
and pce.person_id = p_person_id
and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
and pce.proficiency_level_id is null;
Select nvl(pce.proficiency_level_id, -100)
from per_competence_elements pce
where pce.competence_id = p_comp_id
and pce.person_id = p_person_id
and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
SELECT
Decode(PRL.STEP_VALUE, null, -1, PRL.STEP_VALUE),
PCE.COMPETENCE_ELEMENT_ID
FROM
PER_RATING_LEVELS PRL,
PER_COMPETENCE_ELEMENTS PCE
WHERE
PCE.PROFICIENCY_LEVEL_ID = PRL.RATING_LEVEL_ID (+)
and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
AND PCE.type = 'DELIVERY'
AND PCE.ACTIVITY_VERSION_ID = p_act_ver_id
AND PCE.COMPETENCE_ID = p_comp_id
ORDER BY PRL.STEP_VALUE DESC;
SELECT
PRL.STEP_VALUE
FROM
PER_RATING_LEVELS PRL
WHERE
PRL.RATING_LEVEL_ID = p_level_id;
SELECT MAX(STEP_VALUE)
FROM per_competence_levels_v
WHERE COMPETENCE_ID = p_comp_id;
select MAX(step_value)
from
per_rating_levels prl
,per_competences pce
where (prl.rating_scale_id = pce.rating_scale_id
OR pce.competence_id = prl.competence_id)
AND pce.competence_id = p_comp_id;
SELECT MIN(STEP_VALUE)
FROM per_competence_levels_v
WHERE COMPETENCE_ID = p_comp_id;
select MIN(step_value)
from
per_rating_levels prl
,per_competences pce
where (prl.rating_scale_id = pce.rating_scale_id
OR pce.competence_id = prl.competence_id)
AND pce.competence_id = p_comp_id;
SELECT
oev.event_id,
OCU.online_flag,
OFR.learning_object_id
FROM
ota_activity_versions OAV,
ota_offerings OFR,
ota_events OEV,
ota_category_usages OCU
WHERE
OFR.activity_version_id = OAV.activity_version_id
And OEV.parent_offering_id = OFR.offering_id
And OFR.delivery_mode_id = OCU.category_usage_id
And OCU.type ='DM'
And (OEV.event_type = 'SCHEDULED' or OEV.event_type = 'SELFPACED')
And OEV.event_status <> 'A'
And OAV.activity_version_id = p_act_ver_id;
SELECT lesson_status
FROM ota_performances
WHERE
user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = p_lo_id;
SELECT cpr.prerequisite_course_id
FROM ota_course_prerequisites cpr
WHERE cpr.activity_version_id = p_act_ver_id
and cpr.prerequisite_type = 'M'
and cpr.enforcement_mode in ('B', p_enforcement_mode);
SELECT cpe.competence_id,
cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
and cpe.mandatory = 'Y'
and cpe.type = 'OTA_COMP_PREREQ';
SELECT cpr.prerequisite_course_id
FROM ota_course_prerequisites cpr
WHERE cpr.activity_version_id = p_act_ver_id
and cpr.prerequisite_type = 'A'
and cpr.enforcement_mode in ('B', p_enforcement_mode);
SELECT cpe.competence_id,
cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
and ( cpe.mandatory = 'N' or cpe.mandatory is null )
and cpe.type = 'OTA_COMP_PREREQ';
SELECT activity_version_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT ofr.activity_version_id
FROM ota_events evt, ota_offerings ofr
WHERE evt.event_id = p_event_id
and evt.parent_offering_id = ofr.offering_id;
select party.party_id
from HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_PARTIES party,
HZ_RELATIONSHIPS rel,
HZ_ORG_CONTACTS org_cont,
HZ_PARTIES rel_party,
HZ_CUST_ACCOUNTS role_acct
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
SELECT cpr.prerequisite_course_id
FROM ota_course_prerequisites cpr
WHERE cpr.activity_version_id = p_act_ver_id
and cpr.prerequisite_type = 'M'
and cpr.enforcement_mode in ('B', p_enforcement_mode);
SELECT cpe.competence_id,
cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
and cpe.mandatory = 'Y'
and cpe.type = 'OTA_COMP_PREREQ';
select
ota_cpr_utility.is_course_completed
(odb.delegate_person_id
,odb.delegate_contact_id
,nvl(odb.delegate_person_id,odb.delegate_contact_id)
,decode(odb.delegate_person_id,null,'C', 'E')
,p_prerequisite_course_id
) prereq_met
from
ota_delegate_bookings odb,
ota_booking_status_types bst,
ota_events evb
where
odb.booking_status_type_id = bst.booking_status_type_id
and evb.event_id = odb.event_id
and evb.event_id = p_event_id;
select
ota_cpr_utility.is_competency_acheived
(odb.delegate_person_id
,p_comp_id
,p_level_id
) prereq_met
from
ota_delegate_bookings odb,
ota_events evb
where
evb.event_id = odb.event_id
and evb.event_id = p_event_id;
SELECT activity_version_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT activity_version_id
FROM ota_events
WHERE event_id = p_event_id;
select 'Y'
from OTA_EVENTS oev
where oev.ACTIVITY_VERSION_ID = p_prerequisite_course_id
and (oev.EVENT_TYPE = 'SCHEDULED' or oev.EVENT_TYPE = 'SELFPACED')
and oev.EVENT_STATUS <> 'A'
and nvl(trunc(oev.course_end_date), trunc(sysdate)) >= trunc(sysdate);