[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
avalue => 'Update HR Error : '
|| substr(p_error_msg,1,1000)
);
PROCEDURE UpdateRHistoryProcess( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result in out nocopy varchar2) is
--
--
--
l_result varchar2(4000);
wf_core.context('GHR_SF52', 'ghr_wf_pkg.UpdateRHistoryProcess',itemtype, itemkey, to_char(actid), funcmode);
end UpdateRHistoryProcess;
PROCEDURE UpdateFinalFYIWFUsers ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result in out nocopy varchar2) is
--
--
--
cursor csr_get_approver_name is
SELECT user_name from ghr_pa_routing_history
where pa_request_id = itemkey
and approval_status = 'APPROVE'
order by pa_routing_history_id desc;
SELECT user_name from ghr_pa_routing_history
where pa_request_id = itemkey
and action_taken in ('UPDATE_HR_COMPLETE','ENDED')
order by pa_routing_history_id desc;
if ( CheckItemAttribute ( p_name => 'PERSON_UPDATE_HR',
p_itemtype => itemtype,
p_itemkey => itemkey ) and (l_upd_hr_user_name Is Not Null) ) then
wf_engine.SetItemAttrText( itemtype => itemtype,
itemkey => itemkey,
aname => 'PERSON_UPDATE_HR',
avalue => l_upd_hr_user_name
);
wf_core.context('GHR_SF52', 'ghr_wf_pkg.UpdateFinalFYIWFUsers',itemtype, itemkey, to_char(actid), funcmode);
end UpdateFinalFYIWFUsers ;
if ( CheckItemAttribute ( p_name => 'PERSON_UPDATE_HR',
p_itemtype => itemtype,
p_itemkey => itemkey ) ) then
--
l_upd_hr_user_name := wf_engine.GetItemAttrText
(itemtype => itemtype,
itemkey => itemkey,
aname => 'PERSON_UPDATE_HR'
);
elsif l_action_taken in ('UPDATE_HR_COMPLETE','ENDED') then
result := 'COMPLETE:UPDATE_HR_COMPLETE';
SELECT max(pa_routing_history_id)
FROM ghr_pa_routing_history
WHERE pa_request_id = p_pa_request_id;
SELECT action_taken, user_name, groupbox_id
FROM ghr_pa_routing_history
WHERE pa_routing_history_id = l_pa_routing_history_id;
SELECT name
FROM GHR_GROUPBOXES
WHERE GROUPBOX_ID = l_groupbox_id;
if l_action_taken not in ('CANCELED','UPDATE_HR_COMPLETE','FUTURE_ACTION','ENDED') or l_action_taken is Null then
if l_user_name is not null then
p_user_name := l_user_name;
elsif l_action_taken in ('CANCELED','FUTURE_ACTION','UPDATE_HR_COMPLETE','ENDED') then
p_action_taken := l_action_taken;
SELECT noa_family_code, request_number,
employee_first_name, employee_last_name, employee_middle_names,
proposed_effective_date, effective_date, requested_by_person_id,
routing_group_id, to_organization_id, first_noa_desc, second_noa_desc,
first_noa_code, second_noa_code, from_position_id, personnel_office_id,
status, to_position_id
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
SELECT name
FROM ghr_families
WHERE noa_family_code = l_noa_family_code;
SELECT name, description
FROM ghr_routing_groups
WHERE routing_group_id = l_routing_group_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = l_to_organization_id ;
SELECT action_taken from ghr_pa_routing_history
where pa_request_id = p_pa_request_id
order by pa_routing_history_id desc;
SELECT date_notification_sent, creation_date FROM ghr_pa_routing_history
WHERE pa_request_id = p_pa_request_id
order by 1 asc;
SELECT hru.name
FROM hr_organization_units hru,
hr_all_positions_f hpf
WHERE hpf.position_id = nvl(l_from_position_id,-9999)
and nvl(l_effective_date,sysdate)
between hpf.effective_start_date
and hpf.effective_end_date
and hpf.organization_id = hru.organization_id;
elsif l_action_taken1 in ('UPDATE_HR_COMPLETE') then
l_subject := 'Personnel Action : Update HR Complete : ' || l_first_noa_desc || ' : Req# ' || l_request_number;
avalue => 'Update HR Error : '
|| substr(p_error_msg,1,1000)
);
avalue => 'Update HR Error : '
|| substr(p_error_msg,1,1000)
);
if p_action_taken = 'UPDATE_HR_COMPLETE' then
wf_engine.CompleteActivity('GHR_SF52', p_pa_request_id, 'BLOCK_FUTURE_ACTION','UPDATE_HR_COMPLETE');
select name
from wf_item_attribute_values
where item_type = upper(p_itemtype)
and item_key = nvl(p_itemkey,'-9999')
and name = nvl(p_name,'-9999');
SELECT action_taken
FROM ghr_pa_routing_history
WHERE pa_request_id = itemkey
order by pa_routing_history_id desc;
if l_action_taken in ('ENDED','UPDATE_HR_COMPLETE') then
if ( CheckItemAttribute ( p_name => 'PA_REQUEST_RO',
p_itemtype => itemtype,
p_itemkey => itemkey ) ) then
-- Bug # 8597583 modified parameters to pass with out double quotes
l_load_form := 'GHRWS52L:p_pa_request_id=' || Itemkey
|| ' p_inbox_query_only=YES' || ' WORKFLOW_NAME=GHR_US_PA_REQUEST'
|| ' p_wf_notification_id=&#NID';--Bug# 6923642
select name,groupbox_id,display_name from ghr_groupboxes
where routing_group_id = l_routing_group_id
and name = l_forward_to_name;
select groupbox_user_id,groupbox_id,user_name,
INITIATOR_FLAG,
REQUESTER_FLAG,
AUTHORIZER_FLAG,
PERSONNELIST_FLAG,
APPROVER_FLAG,
REVIEWER_FLAG
from ghr_groupbox_users
where groupbox_id = ( select groupbox_id from ghr_groupboxes
where routing_group_id = l_routing_group_id )
and user_name = l_forward_to_name;
SELECT pei.pei_information3 routing_group_id
,pei.pei_information4 initiator_flag
,pei.pei_information5 requester_flag
,pei.pei_information6 authorizer_flag
,pei.pei_information7 personnelist_flag
,pei.pei_information8 approver_flag
,pei.pei_information9 reviewer_flag
,pei.person_id person_id
FROM per_people_extra_info pei
,fnd_user use
WHERE use.user_name = p_user_name
AND pei.person_id = use.employee_id
AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
AND pei.pei_information3 = ( SELECT routing_group_id from
GHR_PA_REQUESTS
where pa_request_id = itemkey);
select usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
from per_people_f per,
fnd_user usr
where upper(usr.user_name) = upper(p_user_name)
and per.person_id = usr.employee_id
and l_effective_date
between effective_start_date
and effective_end_date; */
SELECT usr.employee_id,
per.first_name,
per.last_name,
per.middle_names
FROM per_people_f per,
fnd_user usr
WHERE usr.user_name = upper(p_user_name)
AND per.person_id = usr.employee_id
AND l_effective_date
BETWEEN effective_start_date
AND effective_end_date;
select prh.pa_routing_history_id,
prh.object_version_number
from ghr_pa_routing_history prh
where prh.pa_request_id = itemkey
order by prh.pa_routing_history_id desc;
SELECT routing_group_id,nvl(effective_date,sysdate) effective_date,
first_noa_id,second_noa_id,noa_family_code
from ghr_pa_requests
where pa_request_id = itemkey;