The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pac.person_id INTO l_person_id
FROM per_allocated_tasks pat, per_allocated_checklists pac
WHERE allocated_task_id = l_allocated_task_id
AND pac.allocated_checklist_id = pat.allocated_checklist_id;
SELECT 'Y'
FROM wf_local_roles
WHERE name = p_role_name;
select
TEXT_VALUE,RESPONDER
from
wf_notifications wfn,
wf_notification_attributes wfna
where
wfn.notification_id=wfna.notification_id and context like cur_p_context and responder is not null and name ='RESULT';
SELECT 'X'
FROM per_ben_identified_events
WHERE person_id = p_person_id
AND nvl(assignment_id,-1) = nvl(p_assignment_id,-1)
AND event_reason_id = p_ler_id
AND effective_date = p_effective_date;
insert into per_ben_identified_events(
ben_identified_event_id,
event_reason_id,
person_id,
assignment_id,
status,
effective_date,
object_version_number)
values(
per_ben_identified_events_s.nextval,
p_ler_id,
p_person_id,
p_assignment_id,
'Pending',
p_effective_date,
1);
select
ctsk.eligibility_object_id
,ckl.checklist_id
,ckl.name checklist_name
,ckl.description checklist_description
,ckl.checklist_category
,ctsk.checklist_task_name
,ctsk.description checklist_task_description
,ctsk.task_in_checklist_id
,ctsk.target_duration
,ctsk.target_duration_uom
,ctsk.mandatory_flag
,ctsk.ame_attribute_identifier
,ctsk.action_url
,ckl.event_reason_id
,ckl.business_group_id
from
per_tasks_in_checklist ctsk,
per_checklists ckl
where ckl.event_reason_id = pl_ler_event
and ctsk.checklist_id = ckl.checklist_id
order by ckl.checklist_id,ctsk.eligibility_object_id;
select
'Exists'
from
per_tasks_in_checklist tic,
per_allocated_checklists pac,
per_allocated_tasks pat
where
tic.task_in_checklist_id = p_task_in_checklist_id
and pac.person_id = p_person_id
and tic.checklist_id = pac.checklist_id
and tic.checklist_task_name = pat.task_name
and pac.allocated_checklist_id = pat.allocated_checklist_id;
select allocated_checklist_id
FROM per_allocated_checklists
WHERE person_id = p_person_id
AND nvl(assignment_id,-1) = nvl(p_assignment_id,-1)
AND checklist_id = p_checklist_id;
hr_utility.set_location('after insert',70);
SELECT person_id ,
assignment_id ,
effective_date,
event_reason_id
FROM per_ben_identified_events
WHERE status = 'Pending'
FOR UPDATE OF status;
UPDATE per_ben_identified_events
SET status = 'Processed'
WHERE CURRENT OF pending_events;
delete from per_ben_identified_events
where status = 'Processed';
SELECT person_id ,
assignment_id ,
effective_date,
event_reason_id
FROM per_ben_identified_events
WHERE status = 'Pending'
AND person_id = p_person_id
FOR UPDATE OF status;
UPDATE per_ben_identified_events
SET status = 'Processed'
WHERE CURRENT OF pending_events;
SELECT papf.full_name
FROM per_all_people_f papf
WHERE papf.person_id = p_person_id
AND
(
SELECT max (ppos.date_start)
FROM per_periods_of_service ppos
WHERE ppos.person_id = p_person_id
) BETWEEN papf.effective_start_date
AND papf.effective_end_date;*/ --Commented for Bug#12385504
SELECT papf.full_name
FROM per_all_people_f papf,
(
SELECT to_date(max (ppos.date_start)) effective_date
FROM per_periods_of_service ppos
WHERE ppos.person_id = p_person_id
) ppfe
WHERE papf.person_id = p_person_id
AND papf.effective_start_date =
NVL(ppfe.effective_date,(SELECT to_date(max (effective_start_date))
FROM per_all_people_f
WHERE person_id = p_person_id));
SELECT ORIG_SYSTEM_ID
FROM wf_local_roles
where ORIG_SYSTEM = 'PER'
and name = p_task_done_by;
update PER_ALLOCATED_TASKS
set status = 'COM',
performer_orig_sys_id = nvl(l_performer_orig_sys_id,performer_orig_sys_id) -- bug 7560762
where ALLOCATED_TASK_ID = l_alloc_id;
SELECT ORIG_SYSTEM_ID
FROM wf_local_roles
where ORIG_SYSTEM = 'PER'
and name = p_task_done_by;
update PER_ALLOCATED_TASKS
set status = 'REJ',
performer_orig_sys_id = nvl(l_performer_orig_sys_id,performer_orig_sys_id) -- bug 7560762
where ALLOCATED_TASK_ID = l_alloc_id;
SELECT checklist_task_name
,description
,task_sequence
,mandatory_flag
,target_duration
,target_duration_uom
,action_url
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,information_category
,information1
,information2
,information3
,information4
,information5
,information6
,information7
,information8
,information9
,information10
,information11
,information12
,information13
,information14
,information15
,information16
,information17
,information18
,information19
,information20
FROM per_tasks_in_checklist
WHERE checklist_id = cp_checklist_id;
SELECT ADD_MONTHS(SYSDATE,lr_tasks.target_duration)
INTO l_target_end_date FROM DUAL;
select substr(p_transaction_id,instr(p_transaction_id,'-')+1,length(p_transaction_id))
INTO l_person_id FROM dual;
select substr(p_transaction_id,instr(p_transaction_id,'-')+1,length(p_transaction_id))
INTO l_person_id FROM dual;
SELECT effective_date
INTO l_effective_date
FROM per_ben_identified_events
WHERE status = 'Pending'
AND person_id = l_person_id;
SELECT supervisor_id INTO l_supervisor_id
FROM per_assignments_f
WHERE person_id = l_person_id
AND l_effective_date BETWEEN effective_start_date AND effective_end_date
AND assignment_type = 'E'
AND primary_flag = 'Y';
SELECT AME_ATTRIBUTE_IDENTIFIER INTO l_ame_attribute_identifier
FROM PER_TASKS_IN_CHECKLIST
WHERE task_in_checklist_id= substr(p_transaction_id,1,instr(p_transaction_id,'-')-1);