[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| FUNCTION: update_object_version
|| DESCRIPTION: Update the object version number in the transaction step
|| to pass the invalid object api error for Save for Later.
||=======================================================================
*/
PROCEDURE update_object_version
(p_transaction_step_id in number
,p_login_person_id in number) IS
l_proc constant varchar2(100) := g_package || ' update_object_version';
END update_object_version;
CURSOR csr_update_access_check IS
SELECT hao.organization_id
,fnd_date.canonical_to_date(cost_center.ORG_INFORMATION3) start_date
,fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4) end_date
,decode(
(decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE'
,HR_SECURITY.SHOW_RECORD
('HR_ALL_ORGANIZATION_UNITS'
,HAO.ORGANIZATION_ID
)
),'TRUE',0,1
) +
decode(decode(hr_general.get_xbg_profile
,'Y', hao.business_group_id
,hr_general.get_business_group_id
)
,hao.business_group_id,0,1
) +
decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE'
,HR_SECURITY.SHOW_RECORD
('PER_ALL_PEOPLE_F'
,PAP.PERSON_ID
,PAP.PERSON_TYPE_ID
,PAP.EMPLOYEE_NUMBER
,PAP.APPLICANT_NUMBER
)
)
,'TRUE',0,1
) +
decode(decode(hr_general.get_xbg_profile
,'Y',pap.business_group_id
,hr_general.get_business_group_id
)
,pap.business_group_id,0,1
)
),0,'Y','N'
) has_update_access
FROM hr_organization_information cost_center
,per_all_people_f pap, hr_all_organization_units hao
WHERE cost_center.ORG_INFORMATION2 = to_char(pap.person_id)
AND cost_center.org_information_context = 'Organization Name Alias'
AND pap.person_id = ln_person_id
AND (pap.current_employee_flag = 'Y' or pap.current_npw_flag = 'Y')
AND hao.organization_id = cost_center.organization_id
AND trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
/* Excluding pending approvals */
AND not exists (select 'e' from hr_api_transaction_steps s, hr_api_transactions t
where s.api_name = 'HR_CCMGR_SS.PROCESS_API'
--Bug 3034218: Exclude current process, include v5 pending status RO, ROS and YS
and s.transaction_id = t.transaction_id and status IN ('YS', 'Y','RO','ROS')
and t.item_key <> itemKey
--BUG 3648732
and exists
(
SELECT NULL FROM hr_api_transaction_values v
WHERE s.transaction_step_id+0 = v.transaction_step_id
AND v.name = 'P_ORGANIZATION_ID'
AND v.number_value = hao.organization_id
)
and rownum < 2)
AND exists (select 'e' from hr_organization_information class, hr_org_info_types_by_class ctype
where ctype.org_information_type = 'Organization Name Alias'
and ctype.org_classification = class.org_information1
and class.org_information_context = 'CLASS'
and class.org_information2 = 'Y'
and class.organization_id = cost_center.organization_id)
AND (nvl(fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4),ld_effective_date) >= ld_effective_date
Or (fnd_date.canonical_to_date(cost_center.ORG_INFORMATION4) <= ld_effective_date
and fnd_date.canonical_to_date(cost_center.ORG_INFORMATION3)
= (select max(fnd_date.canonical_to_date(oi.ORG_INFORMATION3))
from hr_organization_information oi
where oi.org_information_context = 'Organization Name Alias'
and oi.organization_id = cost_center.organization_id))); -- 2476134
SELECT paf.supervisor_id
FROM per_all_assignments_f paf
,per_all_people_f ppf
WHERE ppf.person_id = ln_creator_person_id
and paf.person_id = ppf.person_id
and ld_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ld_effective_date between paf.effective_start_date
and paf.effective_end_date
and (paf.assignment_type = 'E' and ppf.current_employee_flag = 'Y'
or paf.assignment_type = 'C' and ppf.current_npw_flag = 'Y')
and paf.primary_flag = 'Y';
FOR get_update_access in csr_update_access_check LOOP
IF get_update_access.has_update_access = 'Y' THEN
resultout := 'COMPLETE:'|| 'Y';
p_selected_emp_name IN VARCHAR2 DEFAULT NULL,
p_single_supervisor_name IN VARCHAR2 DEFAULT NULL ,
p_single_effective_date IN DATE DEFAULT NULL,
p_term_flag IN VARCHAR2,
p_selected_emp_id IN NUMBER,
p_rptg_grp_id IN VARCHAR2 DEFAULT NULL,
p_plan_id IN VARCHAR2 DEFAULT NULL,
p_effective_date_option IN VARCHAR2 DEFAULT NULL ) IS
--p_selected_emp_id is the new attribute added on jan 8th raj
ln_transaction_id NUMBER ;
p_name => 'p_selected_emp_name' ,
p_value =>p_selected_emp_name ) ;
p_name => 'p_selected_emp_id' ,
p_value => p_selected_emp_id) ;
|| PROCEDURE: update_supervisor
|| DESCRIPTION: This changes the per_all_assignments_f.supervisor_id attribute
|| to the value of the passed in parm p_supervisor_id.
||===========================================================================
*/
PROCEDURE update_supervisor (
p_effective_date DATE ,
p_attribute_update_mode VARCHAR2,
p_assignment_id NUMBER,
p_supervisor_id NUMBER,
--Assignment Security
p_supervisor_assignment_id NUMBER,
p_validate BOOLEAN )
IS
-- Bug 2130066 Fix Begins: 01/11/2002
-- Changed from per_assignments_f to per_all_assignments_f. This is
-- necessary because when a Supervisor Security profile restrict to 1 level,
-- we cannot get data for 2 levels down or beyond. This will happen when
-- the 1st level employee is terminated, we need to change all his direct
-- reports to another employee. In this case, the direct reports of the
-- terminating employee will not be returned because the Supervisor Security
-- profile is restricted to 1 level.
CURSOR lc_object_version_no IS
SELECT object_version_number,
assignment_type
FROM per_all_assignments_f -- Bug 2130066 fix
WHERE assignment_id = p_assignment_id
AND (p_effective_date BETWEEN
NVL ( effective_start_date , p_effective_date)
AND NVL ( effective_end_date , p_effective_date )) ;
l_proc constant varchar2(100) := g_package || ' update_supervisor';
hr_assignment_att.update_asg(
p_validate=>p_validate,
p_effective_date=>p_effective_date ,
p_attribute_update_mode=>p_attribute_update_mode ,
p_assignment_id=>p_assignment_id ,
p_assignment_type => ln_assignment_type,
p_object_version_number =>ln_object_version_no ,
p_supervisor_id => p_supervisor_id ,
-- Assignment Security
p_supervisor_assignment_id => l_supervisor_assignment_id ,
p_comment_id => ln_comment_id ,
p_effective_start_date=>ld_effective_start_date,
p_effective_end_date=>ld_effective_end_date,
p_no_managers_warning=>lb_no_managers_warning,
p_other_manager_warning=>lb_other_manager_warning ) ;
END update_supervisor ;
p_selected_emp_id NUMBER ,
p_selected_person_sup_id NUMBER ,
p_selected_person_old_sup_id NUMBER ,
-- Assignment Security
p_selected_person_sup_asg_id NUMBER ,
p_sel_person_old_sup_asg_id NUMBER ,
p_passed_effective_date DATE ,
p_passed_assignment_id NUMBER ,
p_direct_reports ltt_direct_reports ,
p_validate BOOLEAN ,
p_from_term BOOLEAN DEFAULT FALSE ,
p_emp_asg_id hr_util_misc_web.g_varchar2_tab_type,
p_emp_effective_date hr_util_misc_web.g_varchar2_tab_type,
-- Assignment Security
p_emp_sup_asg_id hr_util_misc_web.g_varchar2_tab_type,
p_term_flag VARCHAR2)
IS
lv_message_number VARCHAR2(80);
SAVEPOINT update_supervisor ;
( p_selected_person_sup_id IS NOT NULL AND
p_selected_person_old_sup_id IS NULL ) OR
( p_selected_person_sup_id IS NULL AND
p_selected_person_old_sup_id IS NOT NULL) OR
(p_selected_person_sup_id <> p_selected_person_old_sup_id) OR
( hr_general2.supervisor_assignments_in_use = 'TRUE'
AND (p_selected_person_sup_asg_id is not NULL and
p_sel_person_old_sup_asg_id is NULL)
) OR
( hr_general2.supervisor_assignments_in_use = 'TRUE'
AND (p_selected_person_sup_asg_id is NULL and
p_sel_person_old_sup_asg_id is not NULL)
) OR
( hr_general2.supervisor_assignments_in_use = 'TRUE'
AND p_selected_person_sup_asg_id <> p_sel_person_old_sup_asg_id)
)
THEN
update_supervisor(
p_passed_effective_date ,
'ATTRIBUTE_UPDATE' ,
p_passed_assignment_id ,
p_selected_person_sup_id,
-- Assignment Security
p_selected_person_sup_asg_id,
p_validate) ;
IF ((p_direct_reports(i).supervisor_id <> p_selected_emp_id) or
(p_direct_reports(i).supervisor_id is Null) or
(hr_general2.supervisor_assignments_in_use = 'TRUE' AND
p_direct_reports(i).supervisor_assignment_id <>
p_passed_assignment_id)
or (hr_general2.supervisor_assignments_in_use = 'TRUE' AND
p_direct_reports(i).supervisor_assignment_id is NULL)
)
THEN
update_supervisor(
p_direct_reports(i).effective_date ,
'ATTRIBUTE_UPDATE' ,
p_direct_reports(i).assignment_id ,
p_direct_reports(i).supervisor_id ,
-- Assignment Security
p_direct_reports(i).supervisor_assignment_id ,
p_validate);
update_supervisor(
p_emp_effective_date(i) ,
'ATTRIBUTE_UPDATE' ,
p_emp_asg_id(i) ,
p_selected_emp_id,
p_emp_sup_asg_id(i) ,
p_validate);
ROLLBACK to update_supervisor ;
SELECT distinct paf.assignment_id
FROM per_all_assignments_f paf, -- Bug 2130066 fix
per_all_people_f ppf
WHERE ppf.person_id = p_person_id
AND p_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND paf.person_id = ppf.person_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND ((paf.assignment_type = 'E' and ppf.current_employee_flag = 'Y')
OR (paf.assignment_type = 'C' and ppf.current_npw_flag = 'Y'));
p_selected_emp_id IN OUT NOCOPY NUMBER ,
p_passed_assignment_id IN OUT NOCOPY NUMBER ,
p_sup_id OUT NOCOPY NUMBER,
p_old_sup_id OUT NOCOPY NUMBER ,
-- Assignment Security
p_sup_asg_id OUT NOCOPY NUMBER,
p_old_sup_asg_id OUT NOCOPY NUMBER ,
p_sup_name OUT NOCOPY VARCHAR2 ,
p_old_sup_name OUT NOCOPY VARCHAR2,
p_passed_effective_date OUT NOCOPY DATE ,
p_direct_reports IN OUT NOCOPY ltt_direct_reports,
p_emp_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_emp_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_emp_asg_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_emp_date IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type ,
p_emp_sup_asg_id IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_single_supervisor_name IN OUT NOCOPY VARCHAR2,
p_single_effective_date IN OUT NOCOPY DATE,
p_term_flag IN OUT NOCOPY VARCHAR2)
IS
ln_transaction_step_id NUMBER;
ln_selected_person_id NUMBER ;
ln_selected_emp_id NUMBER ;
lv_selected_emp_name VARCHAR2(250);
lv_selected_emp BOOLEAN;
lv_selected_emp :=
hr_transaction_api.get_boolean_value
(p_transaction_step_id => ln_transaction_step_id,
p_name =>'p_selected_emp');
lv_selected_emp := false;
p_selected_emp_id :=
hr_transaction_api.get_number_value
(p_transaction_step_id => ln_transaction_step_id,
p_name =>'p_selected_emp_id');
if lv_selected_emp then
hr_utility.trace('In (if lv_selected_emp) '|| l_proc);
p_name =>'p_selected_person_sup_id');
p_name =>'p_selected_person_old_sup_id');
p_name =>'p_selected_person_sup_asg_id');
lv_selected_emp_name :=
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => ln_transaction_step_id,
p_name =>'p_selected_emp_name');
p_name =>'p_selected_person_sup_name');
p_name =>'p_selected_person_old_sup_name');
end if; -- end selected emp
ln_selected_emp_id NUMBER ;
p_selected_emp_id=>ln_selected_emp_id ,
p_passed_assignment_id=>ln_passed_assignment_id,
p_sup_id=>ln_sup_id ,
p_old_sup_id=>ln_old_sup_id ,
-- Assignment Security
p_sup_asg_id=>ln_sup_asg_id ,
p_old_sup_asg_id=>ln_old_sup_asg_id ,
p_sup_name=>lv_sup_name ,
p_old_sup_name=>lv_old_sup_name ,
p_passed_effective_date=>ld_passed_effective_date,
p_direct_reports=>ltt_reports,
p_emp_name=>ltt_emp_name ,
p_emp_id=>ltt_emp_id ,
p_emp_asg_id=>ltt_emp_asg_id ,
p_emp_date=>ltt_emp_date ,
-- Assignment Security
p_emp_sup_asg_id =>ltt_emp_sup_asg_id ,
p_single_supervisor_name=>lv_single_supervisor_name,
p_single_effective_date=>ld_single_effective_date,
p_term_flag=>ld_term_flag) ;
ln_selected_emp_id := hr_process_person_ss.g_person_id;
p_selected_emp_id=>ln_selected_emp_id ,
p_selected_person_sup_id=>ln_sup_id,
p_selected_person_old_sup_id=>ln_old_sup_id ,
-- Assignment Security
p_selected_person_sup_asg_id=>ln_sup_asg_id,
p_sel_person_old_sup_asg_id=>ln_old_sup_asg_id ,
p_passed_effective_date=>ld_passed_effective_date ,
p_passed_assignment_id=>ln_passed_assignment_id,
p_direct_reports=>ltt_reports,
p_validate=>p_validate ,
p_emp_asg_id=>ltt_emp_asg_id ,
p_emp_effective_date=>ltt_emp_date,
-- Assignment Security
p_emp_sup_asg_id=>ltt_emp_sup_asg_id ,
p_term_flag=>ld_term_flag ) ;
p_name => 'p_selected_emp' ,
p_value => TRUE ) ;
p_name => 'p_selected_emp_id' ,
p_value =>p_emp_id ) ;
p_name => 'p_selected_emp_name' ,
p_value =>p_emp_name ) ;
p_name => 'p_selected_person_sup_name' ,
p_value =>p_new_sup_name ) ;
p_name => 'p_selected_person_old_sup_name' ,
p_value =>p_old_sup_name ) ;
p_name => 'p_selected_person_old_sup_id' ,
p_value =>p_old_sup_id ) ;
p_name => 'p_selected_person_sup_id' ,
p_value =>p_new_sup_id ) ;
p_name => 'p_selected_person_sup_asg_id' ,
p_value =>p_new_sup_asg_id ) ;
|| PROCEDURE: update_asg
|| DESCRIPTION:
||
||===========================================================================
*/
PROCEDURE update_asg
(p_validate in number default 0
,p_attribute_update_mode in varchar2
,p_manager_details_tab in out nocopy SSHR_MANAGER_DETAILS_TAB_TYP
,p_item_type in varchar2 default null
,p_item_key in varchar2 default null
,p_actid in varchar2 default null
,p_rptg_grp_id in varchar2 default null
,p_plan_id in varchar2 default null
,p_effective_date_option in varchar2 default null
,p_num_of_direct_reports in number default 0
,p_num_of_new_direct_reports in number default 0
,p_selected_person_id in number
,p_selected_person_name in varchar2
,p_term_sup_flag in varchar2
,p_login_person_id in number
,p_save_for_later in varchar2 default 'SAVE'
,p_transaction_step_id in out nocopy number
)
IS
l_transaction_id number ;
l_proc constant varchar2(100) := g_package || 'update_asg';
update_asg(p_validate => p_validate,
p_attribute_update_mode => p_attribute_update_mode,
p_item_type => p_item_type,
p_item_key => p_item_key,
p_actid => p_actid,
p_assignment_id => p_manager_details_tab(I).assignment_id,
p_object_version_number => p_manager_details_tab(I).object_ver_number,
p_supervisor_id => p_manager_details_tab(I).supervisor_id,
p_supervisor_assignment_id => l_supervisor_assignment_id,
p_effective_date => p_manager_details_tab(I).effective_date,
p_comment_id => p_manager_details_tab(I).comment_id,
p_effective_start_date => p_manager_details_tab(I).effective_start_date,
p_effective_end_date => p_manager_details_tab(I).effective_end_date,
p_no_managers_warning => p_manager_details_tab(I).no_managers_warning,
p_other_manager_warning => p_manager_details_tab(I).other_manager_warning,
p_error_message_appl => p_manager_details_tab(I).error_message_appl,
p_error_message_name => p_manager_details_tab(I).error_message_name,
p_error_message => p_manager_details_tab(I).error_message);
hr_utility.trace('end of checking in update_asg');
p_selected_emp_id => p_selected_person_id,
p_selected_emp_name => p_selected_person_name,
p_no_of_direct_reports => p_num_of_direct_reports,
p_no_of_emps => p_num_of_new_direct_reports,
p_term_flag => p_term_sup_flag,
p_rptg_grp_id => p_rptg_grp_id,
p_plan_id => p_plan_id,
p_effective_date_option => p_effective_date_option);
hr_utility.trace('bdefore writing Txn in update_asg');
END update_asg;
|| PROCEDURE: update_asg
|| DESCRIPTION:
||
||===========================================================================
*/
procedure update_asg
(p_validate in NUMBER default 0
,p_effective_date in date
,p_attribute_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_supervisor_id in number default null
,p_supervisor_assignment_id in number default null
,p_assignment_number in varchar2 default null
,p_change_reason in varchar2 default null
,p_comments in varchar2 default null
,p_date_probation_end in date default null
,p_default_code_comb_id in number default null
,p_frequency in varchar2 default null
,p_internal_address_line in varchar2 default null
,p_manager_flag in varchar2 default null
,p_normal_hours in number default null
,p_perf_review_period in number default null
,p_perf_review_period_frequency in varchar2 default null
,p_probation_period in number default null
,p_probation_unit in varchar2 default null
,p_sal_review_period in number default null
,p_sal_review_period_frequency in varchar2 default null
,p_set_of_books_id in number default null
,p_source_type in varchar2 default null
,p_time_normal_finish in varchar2 default null
,p_time_normal_start in varchar2 default null
,p_ass_attribute_category in varchar2 default null
,p_title in varchar2 default null
,p_comment_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_no_managers_warning out nocopy Number
,p_other_manager_warning out nocopy NUMBER
,p_item_type in varchar2 default null
,p_item_key in varchar2 default null
,p_actid in varchar2 default null
,p_error_message_appl out nocopy varchar2
,p_error_message_name out nocopy varchar2
,p_error_message out nocopy long
)
IS
ln_supervisor_id number ;
SELECT object_version_number, assignment_type
FROM per_all_assignments_f -- Bug 2130066 fix
WHERE assignment_id = l_assignment_id
AND (p_effective_date BETWEEN
NVL ( effective_start_date , p_effective_date)
AND NVL ( effective_end_date , p_effective_date )) ;
l_attribute_update_mode varchar2(50);
select object_version_number
from per_all_people_f
where person_id = p_appl_person_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
select object_version_number,
assignment_type,
person_id
from per_all_assignments_f
where assignment_id = p_appl_assign_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
select object_version_number,
assignment_type
from per_all_assignments_f
where person_id = p_appl_person_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
select assignment_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select person_id
from per_all_people_f
where person_id = p_supervisor_id;
SELECT per.current_applicant_flag,
per.current_employee_flag,
per.current_npw_flag
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
l_proc constant varchar2(100) := g_package || ' update_asg';
SAVEPOINT sup_update_asg;
l_attribute_update_mode := p_attribute_update_mode;
Check the mode we are entering to update
case 1: update the current employee assignment record with applicant person id
Create a employee record with applicant person id
case 2: update the applicant assignment record with existing employee person id.
Create a employee record with applicant assignment record details.
case 3: update the current employee assignment record with existing employee person id
No need to create a dummy employee record.
*/
-- Checking case 1:
open lc_get_current_applicant_flag(p_supervisor_id, p_effective_date);
hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
p_item_key => p_item_key
,p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
hr_assignment_att.update_asg(
p_validate =>lb_validate,
p_effective_date =>p_effective_date ,
p_attribute_update_mode =>p_attribute_update_mode ,
p_assignment_id =>l_assignment_id ,
p_assignment_type =>l_assignment_type,
p_object_version_number =>ln_object_version_no ,
p_supervisor_id => l_supervisor_id ,
-- Assignment Security
p_supervisor_assignment_id =>l_supervisor_assignment_id,
p_comment_id => p_comment_id ,
p_effective_start_date =>p_effective_start_date,
p_effective_end_date =>p_effective_end_date,
p_no_managers_warning =>lb_no_managers_warn,
p_other_manager_warning =>lb_other_manager_warn ) ;
ROLLBACK TO sup_update_asg;
ROLLBACK TO sup_update_asg;
ROLLBACK TO sup_update_asg;
END update_asg;