The following lines contain the word 'select', 'insert', 'update' or 'delete':
select oev.course_end_date
from ota_events oev,
ota_offerings off, ota_category_usages ocu
where oev.event_id=p_id
and (oev.parent_offering_id = off.offering_id or oev.offering_id = off.offering_id)
and off.delivery_mode_id = ocu.category_usage_id
and ocu.synchronous_flag = 'Y';
/*select pce.effective_date_from
from per_competence_elements pce , ota_offerings off,ota_events oev
where oev.parent_offering_id=off.offering_id
and off.activity_version_id=pce.activity_version_id
and oev.event_id= p_id
and pce.competence_id =p_comp_id
and type='DELIVERY'; */
select pce.effective_date_from
from per_competence_elements pce
where pce.object_id= p_id
and pce.competence_id =p_comp_id
and type='OTA_LEARNING_PATH';*/
select trunc(sysdate) into l_eff_date from dual;
select trunc(sysdate) into l_eff_date from dual;
select business_group_id from per_all_people_f
where person_id= p_person_id
and trunc(sysdate) between trunc(effective_start_date) and trunc(nvl(effective_end_date,sysdate+1));
hr_transaction_api.delete_transaction_step(p_validate => false
,p_transaction_step_id => l_old_transaction_step_id
,p_person_id => p_person_id
,p_object_version_number => l_old_object_version_number);
hr_transaction_api.update_transaction
(p_transaction_id => l_transaction_id
-- ,p_status => lv_status
,p_transaction_state => null
-- ,p_transaction_effective_date => ld_trans_effec_date
);
p_message_name => 'HR_UPDATE_NOT_ALLOWED');
PROCEDURE Update_competence ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT nocopy VARCHAR2 )
is
l_transaction_step_id number(15);
hr_utility.set_location('ENTERING Update Competence', 10);
hr_utility.set_location('ERROR Update Competence', 10);
hr_transaction_api.update_transaction(
p_transaction_id => hr_transaction_ss.get_transaction_id
(p_item_type => itemtype
,p_item_key => itemkey),
p_status => 'E');
end Update_competence;
PROCEDURE check_Update_competence ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT nocopy VARCHAR2 )
is
l_transaction_step_id number(15);
hr_utility.set_location('ENTERING check Update Competence', 10);
hr_utility.set_location('ERROR Update Competence', 10);
hr_transaction_api.update_transaction(
p_transaction_id => hr_transaction_ss.get_transaction_id
(p_item_type => itemtype
,p_item_key => itemkey),
p_status => 'E');
end check_Update_competence;
SELECT WAAV.TEXT_VALUE Value
FROM WF_ACTIVITY_ATTR_VALUES WAAV
WHERE WAAV.PROCESS_ACTIVITY_ID = actid
AND WAAV.NAME = 'HR_APPROVAL_REQ_FLAG';
select competency_update_level
from ota_certifications_b
where certification_id = l_id;
select competency_update_level
from ota_learning_paths
where learning_path_id = l_id;
select oav.competency_update_level,off.competency_update_level
from ota_activity_versions oav,ota_offerings off,ota_events oev
where oav.activity_version_id = off.activity_version_id
and off.offering_id = oev.parent_offering_id
and
oev.event_id = l_id;
SELECT paf.ASSIGNMENT_ID
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.primary_flag ='Y'
AND paf.assignment_type in ('E','A', 'C');
l_item_value:='NOTIFYUPDATE';
elsif l_item_value = 'NOTIFYUPDATE' then
resultout:='COMPLETE:NOTIFYUPDATE';
select INSTR(inString,'^',startPos) into l_posFound from dual;
select SUBSTR(inString,startPos,((length(inString)+1)-startPos)) into l_value from dual;
select SUBSTR(inString,startPos,(l_posFound-startPos)) into l_value from dual;
Select Proficiency_level_id
from per_competence_elements
where Competence_id = CompId
and person_id = personId;
procedure validate_competence_update
(p_item_type in varchar2,
p_item_key in varchar2,
p_message out nocopy varchar2) is
l_transaction_step_id number(15);
end validate_competence_update;
Select Proficiency_level_id
from per_competence_elements
where Competence_id = CompId
and person_id = personId;
return 'NOUPDATE';
return 'UPDATE';
l_will_comp_update varchar2(50);
SELECT wrpv.display_name displayName
FROM wf_runnable_processes_v wrpv
WHERE wrpv.item_type = p_itemtype
AND wrpv.process_name = p_process;
Select user_id ,user_name
from
fnd_user
where employee_id=p_person_id;
SELECT
EMPLOYEE_ID
FROM
FND_USER
WHERE
user_id = l_creator_user_id ;
SELECT
paf.ASSIGNMENT_ID
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN
paf.effective_start_date
AND paf.effective_end_date
AND paf.primary_flag ='Y'
AND paf.assignment_type in ('E','A', 'C');
SELECT
paf.ASSIGNMENT_ID
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND paf.primary_flag ='Y'
AND paf.assignment_type in ('E','A', 'C')
order by paf.effective_end_date;
Select ppf.full_name
FROM per_all_people_f ppf
where ppf.person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT asg.supervisor_id, per.full_name
FROM per_all_assignments_f asg,
per_all_people_f per
WHERE asg.person_id = p_person_id
AND per.person_id = asg.supervisor_id
AND asg.primary_flag = 'Y'
AND trunc(sysdate)
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND trunc(sysdate)
BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT user_name
FROM fnd_user
WHERE employee_id= l_supervisor_id;
select oev.title,oav.version_name
from ota_Events_vl oev ,ota_activity_versions_tl oav
--,ota_offerings off
where
--oev.parent_offering_id=off.offering_id and
oev.activity_version_id= oav.activity_version_id
and oev.event_id= p_eventid
and Language= USERENV('LANG');
select name from ota_learning_paths_TL
where learning_path_id =csr_lp_id
and Language= USERENV('LANG');
Select 1 from
per_all_people_f
where person_id= p_person_id
and (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or --Bug#6869342
business_group_id = ota_general.get_business_group_id);
is select name from ota_certifications_tl
where certification_id = p_certification_id
and Language= USERENV('LANG');
SELECT decode(instr(web_html_call, param), 0, '-1',
substr(substr(web_html_call, instr(web_html_call, param),
(decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))),
instr(substr(web_html_call, instr(web_html_call, param),
(decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))), '=')+1)) "paramvalue"
FROM fnd_form_functions
WHERE function_name = 'OTA_LEARNER_HOME_SS';
l_will_comp_update := chk_comp_level(l_comp_ids,l_level_ids,p_person_id);
select hr_workflow_item_key_s.nextval
into l_item_key
from sys.dual;
l_func:='OTA_ADMIN_COMPETENCE_UPDATE';
SELECT pce.competence_id CompetenceId
,nvl(pce.proficiency_level_id,-1) LevelId
FROM per_competence_elements pce
, ota_offerings OFF
, ota_events EVT
, per_competences pc
WHERE off.activity_version_id = pce.activity_version_id
AND evt.parent_offering_id = off.offering_id
AND evt.event_id = p_event_id
and pce.competence_id = pc.competence_id
and trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
SELECT
pc.competence_id,
pc.renewal_period_frequency,
pc.renewal_period_units
FROM
per_competences pc
where
pc.competence_id = p_competence_id
AND pc.renewal_period_frequency IS NOT NULL
--AND pc.renewal_period_units IS NOT NULL modified for bug8410902
AND pc.renewal_period_units IN ('Y','D','H','MIN','M','Q','W');
Select nvl(fnd_profile.value('OTA_ALLOW_COMPETENCY_UPDATE_WITH_RENEWAL_PERIOD'),'N') from dual;
SELECT pce.competence_id CompetenceId
,nvl(pce.proficiency_level_id,-1) LevelId
FROM per_competence_elements pce
, per_competences pc
WHERE pce.type = 'OTA_LEARNING_PATH'
AND pce.object_id = l_learning_path_id
AND pce.competence_id = pc.competence_id
AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
SELECT pce.competence_id CompetenceId
,nvl(pce.proficiency_level_id,-1) LevelId
FROM per_competence_elements pce
, per_competences pc
WHERE pce.type = 'OTA_CERTIFICATION' ---Batra to revisit*********************
AND pce.object_id = p_certification_id
AND pce.competence_id = pc.competence_id
AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
select cenr.expiration_date
from ota_cert_enrollments cenr
where certification_id = p_certification_id
and person_id = p_person_id;