[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pa_debug.write('PA_HR_UPDATE_PA_ENTITIES', 'log: ' || p_log_msg, 3);
PROCEDURE update_project_entities ( p_calling_mode in varchar2,
p_table_name in varchar2,
p_person_id in number DEFAULT NULL,
p_start_date_old in date DEFAULT NULL,
p_start_date_new in date DEFAULT NULL,
p_end_date_old in date DEFAULT NULL,
p_end_date_new in date DEFAULT NULL,
p_org_id_old in number DEFAULT NULL,
p_org_id_new in number DEFAULT NULL,
p_job_id_old in number DEFAULT NULL,
p_job_id_new in number DEFAULT NULL,
p_from_job_group_id in number DEFAULT NULL,
p_to_job_group_id in number DEFAULT NULL,
p_job_level_old in number DEFAULT NULL,
p_job_level_new in number DEFAULT NULL,
p_supervisor_old in number DEFAULT NULL,
p_supervisor_new in number DEFAULT NULL,
p_primary_flag_old in varchar2 DEFAULT NULL,
p_primary_flag_new in varchar2 DEFAULT NULL,
p_org_info1_old in varchar2 DEFAULT NULL,
p_org_info1_new in varchar2 DEFAULT NULL,
p_jei_information2_old in varchar2 DEFAULT NULL,
p_jei_information2_new in varchar2 DEFAULT NULL,
p_jei_information3_old in varchar2 DEFAULT NULL,
p_jei_information3_new in varchar2 DEFAULT NULL,
p_jei_information4_old in varchar2 DEFAULT NULL,
p_jei_information4_new in varchar2 DEFAULT NULL,
p_jei_information6_old in varchar2 DEFAULT NULL,
p_jei_information6_new in varchar2 DEFAULT NULL,
p_grade_id_old in number DEFAULT NULL,
p_grade_id_new in number DEFAULT NULL,
p_full_name_old in varchar2 DEFAULT NULL,
p_full_name_new in varchar2 DEFAULT NULL,
p_country_old in varchar2 DEFAULT NULL,
p_country_new in varchar2 DEFAULT NULL,
p_city_old in varchar2 DEFAULT NULL,
p_city_new in varchar2 DEFAULT NULL,
p_region2_old in varchar2 DEFAULT NULL,
p_region2_new in varchar2 DEFAULT NULL,
p_org_struct_element_id in number DEFAULT NULL,
p_organization_id_parent in number DEFAULT NULL,
p_organization_id_child in number DEFAULT NULL,
p_org_structure_version_id in number DEFAULT NULL,
p_inactive_date_old in date DEFAULT NULL,
p_inactive_date_new in date DEFAULT NULL,
p_from_job_id_old in number DEFAULT NULL,
p_from_job_id_new in number DEFAULT NULL,
p_to_job_id_old in number DEFAULT NULL,
p_to_job_id_new in number DEFAULT NULL,
p_org_info_context in varchar2 DEFAULT NULL,
x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_error_message_code out NOCOPY varchar2) --File.Sql.39 bug 4440895
is
--
--
--
ItemType varchar2(30) := 'PAXWFHRU'; -- Identifies the workflow that will be executed.
IF ( p_calling_mode='INSERT' AND (p_jei_information3_new='N' OR p_jei_information3_new IS NULL)) THEN
return;
SELECT pa_workflow_itemkey_s.nextval
INTO itemkey
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'HR_CHANGE_MGMT'
,p_item_type => itemtype
,p_item_key => itemkey
,p_entity_key1 => to_char(p_person_id)
,p_entity_key2 => to_char(p_person_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
END update_project_entities;
avalue => pa_hr_update_api.get_org_name(l_organization_id_child));
avalue => pa_hr_update_api.get_org_name(l_organization_id_parent));
wf_core.context('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
avalue => pa_hr_update_api.get_job_name(l_job_id));
pa_hr_update_api.per_job_extra_billability
(p_calling_mode =>l_calling_mode
,P_job_id =>l_job_id
,P_billable_flag_new =>l_jei_information2_new
,P_billable_flag_old =>l_jei_information2_old
,P_utilize_flag_new =>l_jei_information3_new
,P_utilize_flag_old =>l_jei_information3_old
,P_job_level_new =>l_jei_information4_new
,P_job_level_old =>l_jei_information4_old
,p_schedulable_flag_new =>l_jei_information6_new
,p_schedulable_flag_old =>l_jei_information6_old
,x_return_status =>l_return_status
,x_msg_data =>l_msg_data
,x_msg_count =>l_msg_count );
wf_core.context('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
PA_HR_UPDATE_API.update_name ( p_person_id => l_person_id
,p_old_name => l_full_name_old
,p_new_name => l_full_name_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
wf_core.context('pa_hr_update_pa_entities',
'Full_Name_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Full_Name_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Full_Name_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
avalue => pa_hr_update_api.get_org_name(l_org_id));
pa_hr_update_api.default_ou_change
( p_calling_mode => l_calling_mode,
p_organization_id => l_org_id,
p_default_ou_new => l_org_info1_new,
p_default_ou_old => l_org_info1_old,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
pa_hr_update_api.proj_res_job_group_change
( p_calling_mode => l_calling_mode,
p_organization_id => l_org_id,
p_proj_job_group_new => l_org_info1_new,
p_proj_job_group_old => l_org_info1_old,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
avalue => pa_hr_update_api.get_job_name(l_job_id_new));
avalue => pa_hr_update_api.get_grade_name(l_grade_id_new));
PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
,P_per_valid_grade_job_id => l_job_id_New
,P_per_valid_grade_id_old => l_grade_id_old
,P_per_valid_grade_id_new => l_grade_id_New
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
wf_core.context('pa_hr_update_pa_entities',
'Valid_Grade_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Valid_Grade_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Valid_Grade_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
avalue => pa_hr_update_api.get_grade_name(l_grade_id));
PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
,P_per_grades_grade_id => l_grade_id
,P_per_grades_sequence_old => l_job_level_old
,P_per_grades_sequence_new => l_job_level_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
wf_core.context('pa_hr_update_pa_entities',
'Job_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Job_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Job_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
pa_hr_update_api.address_change( p_calling_mode => l_calling_mode
, p_person_id => l_person_id
, p_country_old => l_country_old
, p_country_new => l_country_new
, p_city_old => l_city_old
, p_city_new => l_city_new
, p_region2_old => l_region2_old
, p_region2_new => l_region2_new
, p_date_from_old => l_date_from_old
, p_date_from_new => l_date_from_new
, p_date_to_old => l_date_to_old
, p_date_to_new => l_date_to_new
, p_addr_prim_flag_old => l_addr_prim_flag_old
, p_addr_prim_flag_new => l_addr_prim_flag_new
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
select 'Y'
into l_org_res_exists
from dual
where exists (SELECT 'Y'
FROM pa_resources_denorm
WHERE resource_organization_id = l_org_id
and sysdate between resource_effective_start_date and
resource_effective_end_date
AND rownum = 1
UNION ALL
SELECT 'Y'
FROM pa_resources_denorm
WHERE resource_organization_id = l_org_id
and resource_effective_start_date > sysdate
AND rownum = 1);
avalue => pa_hr_update_api.get_org_name(l_org_id));
IF l_calling_mode = 'UPDATE' THEN
--dbms_output.put_line('Calling Mode is Update');
if(pa_hr_update_api.belongs_ExpOrg(l_org_id) = 'N') then
-- dbms_output.put_line('Making resources inactive');
PA_HR_UPDATE_API.make_resource_inactive( p_calling_mode => l_calling_mode
,P_Organization_id => l_org_id
,P_Default_OU => l_org_info1_new
,P_inactive_date => l_inactive_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
IF (PA_HR_UPDATE_API.check_pjr_default_ou(l_org_id,l_org_info1_new) = 'Y') THEN /*Added for bug 8568641 */
--dbms_output.put_line('Pulling resources');
PA_HR_UPDATE_API.pull_resources( P_Organization_id => l_org_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
ELSIF l_calling_mode = 'INSERT' THEN
--dbms_output.put_line('Calling mode is Insert');
pa_hr_update_api.default_ou_change( p_calling_mode => l_calling_mode,
p_organization_id => l_org_id,
p_default_ou_new => l_org_info1_new,
p_default_ou_old => l_org_info1_old,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Project_Organization_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Project_Organization_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
avalue => pa_hr_update_api.get_job_name(l_from_job_id_new));
avalue => pa_hr_update_api.get_job_name(l_to_job_id_new));
PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
,P_from_job_id_old => l_from_job_id_old
,P_from_job_id_new => l_from_job_id_new
,P_to_job_id_old => l_to_job_id_old
,P_to_job_id_new => l_to_job_id_new
,P_from_job_group_id => l_from_job_group_id
,P_to_job_group_id => l_to_job_group_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_entities',
'Address_Change',
itemtype,
itemkey,
to_char(actid),
funcmode);
select 'Y'
from per_all_assignments_f asgn,
per_assignment_status_types status
where asgn.person_id = l_person_id
and trunc(l_start_date_new) between trunc(asgn.effective_start_date) and trunc(asgn.effective_end_date)
and asgn.assignment_type in ('E', 'C')
and asgn.primary_flag ='Y'
and asgn.assignment_status_type_id = status.assignment_status_type_id
and status.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
if l_calling_mode = 'DELETE' then
pa_hr_update_api.Delete_PA_Resource_Denorm (
p_person_id => l_person_id
,p_old_start_date => l_start_date_old
,p_old_end_date => l_end_date_old
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
elsif l_calling_mode = 'INSERT' then
if ( l_primary_flag_new = 'Y' and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y') then
-- Added new if condition for Bug 7423251
IF (pa_hr_update_api.check_job_utilization (p_job_id => l_job_id_new
,p_person_id => null
,p_date => null)) = 'Y' then
--Bug 9762784 Start
OPEN chk_asgn(l_person_id, l_start_date_new);
IF (pa_hr_update_api.get_defaultou(l_org_id_new) <> -999 and l_chk_asgn = 'Y') THEN --Bug 9762784
-- call the work flow to update pa objects
SAVEPOINT l_assignment_change;
END IF ; -- IF (pa_hr_update_api.get_defaultou(l_org_id_new) <> -999) THEN
elsif l_calling_mode = 'UPDATE' then
SAVEPOINT l_assignment_change;
and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y'
and pa_hr_update_api.check_job_utilization (
P_job_id => l_job_id_new,
P_person_id => NULL,
p_date => NULL )= 'Y'
and pa_hr_update_api.get_defaultou(l_org_id_new) <> -999) then -- bug 6886592
--dbms_output.put_line('Calling ind pull 1');
and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'N') then
/*If the assignments organization does not belong to expenditure hierarchy
then the corresponding assignment in pa_resources_denorm must be
end dated
*/
pa_hr_update_api.Update_EndDate(
p_person_id => l_person_id,
p_old_start_date => l_start_date_old,
p_new_start_date => l_start_date_new,
p_old_end_date => l_end_date_old,
p_new_end_date => sysdate,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
pa_hr_update_api.Update_PrimaryFlag (
p_person_id => l_person_id
,p_old_start_date => l_start_date_old
,p_new_start_date => l_start_date_new
,p_old_end_date => l_end_date_old
,p_new_end_date => l_end_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT 'Y'
INTO l_res_asgn_exists
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_resources_denorm
WHERE person_id = l_person_id
AND resource_effective_start_date =
l_start_date_new);
pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y') THEN commented and changed as below for bug 5665503 */
if ( (l_res_asgn_exists = 'N' OR (trunc( l_start_date_old) <> trunc(
l_start_date_new)) )
AND ( pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y')
and (pa_hr_update_api.check_job_utilization(
P_job_id => l_job_id_new,
P_person_id => NULL,
p_date => NULL )= 'Y')
and pa_hr_update_api.get_defaultou(l_org_id_new) <> -999) /*bug6886592*/
THEN
--dbms_output.put_line('Calling ind pull 2 as record does not exist or start date is changed');
pa_hr_update_api.Update_EndDate (
p_person_id => l_person_id
,p_old_start_date => l_start_date_old
,p_new_start_date => l_start_date_new
,p_old_end_date => l_end_date_old
,p_new_end_date => l_end_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
if ((pa_hr_update_api.belongs_ExpOrg(l_org_id_old) ='Y' and
pa_hr_update_api.get_defaultou(l_org_id_old) <> -999)) then
if ((pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y' and
pa_hr_update_api.get_defaultou(l_org_id_new) <> -999))
then
pa_hr_update_api.Update_Org (
p_person_id => l_person_id
,p_old_org_id => l_org_id_old
,p_new_org_id => l_org_id_new
,p_old_start_date => l_start_date_old
,p_new_start_date => l_start_date_new
,p_old_end_date => l_end_date_old
,p_new_end_date => l_end_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
if ((pa_hr_update_api.check_job_utilization(
P_job_id => l_job_id_old,
P_person_id => NULL,
p_date => NULL ))= 'Y'
or
(pa_hr_update_api.check_job_utilization(
P_job_id => l_job_id_new,
P_person_id => NULL,
p_date => NULL ))= 'Y' ) THEN
-- bug 9687133
if (l_job_id_old is not null or (l_job_id_old is null and
pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y' and
pa_hr_update_api.get_defaultou(l_org_id_new) <> -999)) THEN
--dbms_output.put_line('Job changed');
pa_hr_update_api.Update_Job (
p_person_id => l_person_id
,p_old_job => l_job_id_old
,p_new_job => l_job_id_new
,p_new_start_date => l_start_date_new
,p_new_end_date => l_end_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_hr_update_api.Update_Supervisor (
p_person_id => l_person_id
,p_old_supervisor => l_supervisor_old
,p_new_supervisor => l_supervisor_new
,p_new_start_date => l_start_date_new
,p_new_end_date => l_end_date_new
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
end if; --end update
wf_core.context ('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
wf_core.context('pa_hr_update_pa_objects',
'start_date_change',
itemtype,
itemkey,
to_char(actid),
funcmode);
SELECT pa_workflow_itemkey_s.nextval
INTO l_item_key
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'HR_CHANGE_MGMT'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(p_person_id)
,p_entity_key2 => to_char(p_person_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_PA_ENTITIES',
p_procedure_name => 'create_fte_sync_wf');
SELECT max(resource_effective_end_date)
INTO l_resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = l_person_id;
log_message('before calling PA_HR_UPDATE_API.withdraw_cand_nominations, l_resource_effective_end_date: '||l_resource_effective_end_date);
PA_HR_UPDATE_API.withdraw_cand_nominations
( p_person_id => l_person_id,
p_effective_date => l_resource_effective_end_date,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
log_message('after calling PA_HR_UPDATE_API.withdraw_cand_nominations, l_return_status: '||l_return_status);
wf_core.context('pa_hr_update_pa_entities',
'Start_fte_sync_wf',
itemtype,
itemkey,
to_char(actid),
funcmode);