[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pa_debug.write('HR_UPDATE_API', 'log: ' || p_log_msg, 3);
pa_hr_update_api.check_exp_OU(p_org_id => p_org_id
,x_return_status => v_return_status
,x_error_message_code => v_error_message_code
);
SELECT Job_id
INTO v_job_id
FROM per_all_assignments_f
WHERE Person_id = P_person_id
AND P_date BETWEEN effective_start_date
AND effective_end_date
AND job_id is NOT NULL
AND primary_flag = 'Y'
and assignment_type in ('E', 'C');
SELECT jei_information3
INTO utilization_flag
FROM per_job_extra_info
WHERE job_id = v_job_id
AND information_type = v_job_info_type
AND jei_information3 IS NOT NULL; -- Bug 2898766
SELECT Job_id
INTO v_job_id
FROM per_all_assignments_f
WHERE Person_id = P_person_id
AND P_date BETWEEN effective_start_date
AND effective_end_date
AND job_id is NOT NULL
AND primary_flag = 'Y'
AND assignment_type in ('E', 'C');
SELECT jei_information2
INTO Billable_flag
FROM per_job_extra_info
WHERE job_id = v_job_id
AND information_type = v_job_info_type
AND jei_information2 IS NOT NULL; -- Bug 2898766
SELECT job_id
INTO l_job_id
FROM per_all_assignments_f paaf
WHERE paaf.person_id = p_person_id
AND trunc(p_date) BETWEEN trunc(paaf.effective_start_date) -- Bug 8269512 : introduced trunc() on dates
AND trunc(paaf.effective_end_date) -- Bug 8269512 : introduced trunc() on dates
AND paaf.job_id is NOT NULL
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type in ('E', 'C')
AND ((SELECT per_system_status
FROM per_assignment_status_types past
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id) IN ('ACTIVE_ASSIGN','ACTIVE_CWK')); --Bug#8879958
SELECT jei_information6
INTO l_schedulable_flag
FROM per_job_extra_info
WHERE job_id = l_job_id
AND information_type = l_job_info_type
AND jei_information6 IS NOT NULL;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.check_exp_OU');
SELECT 'Y'
INTO v_dummy
FROM pa_implementations_all
WHERE org_id = p_org_id
AND rownum = 1;
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.check_exp_OU'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_create_resoruce_denorm');
end if; ----- then assign joblevel to zero when grade is deleted
PA_RESOURCE_PVT.update_resource_denorm
( p_resource_denorm_old_rec => v_resource_rec_old
,p_resource_denorm_new_rec => v_resource_rec_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_API.call_create_resource_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
PROCEDURE update_job_level_res_denorm
( P_job_id_old per_jobs.job_id%type
,P_job_id_new per_jobs.job_id%type
,P_job_level_old NUMBER
,P_job_level_new NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
PA_RESOURCE_PVT.update_resource_denorm
( p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_job_level_res_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_job_level_res_denorm;
PROCEDURE update_all_jobs
( P_job_id per_jobs.job_id%type
,P_job_level_old pa_resources_denorm.resource_job_level%type
,P_job_level_new pa_resources_denorm.resource_job_level%type
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_job_id PER_JOBS.JOB_ID%type;
SELECT l_job_id effected_job_id
FROM sys.dual
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjg.job_group_id = get_job_group_id(l_job_id)
UNION
SELECT distinct pjr.from_job_id effected_job_id
FROM pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.to_job_id = l_job_id
AND pjr.to_job_group_id = pjg.job_group_id
UNION
SELECT distinct pjr.to_job_id effected_job_id
FROM pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.from_job_id = l_job_id
AND pjr.from_job_group_id = pjg.job_group_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_all_jobs');
update_job_level_res_denorm
( P_job_id_old => l_job_id
,P_job_id_new => l_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_all_jobs'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_all_jobs;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
If P_calling_mode = 'INSERT' OR P_calling_mode = 'UPDATE' Then
IF P_DEBUG_MODE = 'Y' THEN
log_message('P_calling_mode = ' || P_calling_mode);
update_job_level_res_denorm
( P_job_id_old => NULL
,P_job_id_new => l_PRJG_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_all_jobs
( P_job_id => l_master_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_job_level_res_denorm
( P_job_id_old => NULL
,P_job_id_new => l_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
Elsif P_calling_mode = 'UPDATE' then
IF P_DEBUG_MODE = 'Y' THEN
log_message('P_calling_mode = UPDATE');
Elsif P_calling_mode = 'DELETE' then
IF P_DEBUG_MODE = 'Y' THEN
log_message('P_calling_mode = DELETE');
update_all_jobs
( P_job_id => l_master_job_id
,P_job_level_old => NULL
,P_job_level_new => NULL
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_job_level_res_denorm
( P_job_id_old => NULL
,P_job_id_new => l_job_id
,P_job_level_old => NULL
,P_job_level_new => NULL
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pa_job_relation_job_id'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
PROCEDURE perform_job_updates
( P_job_id per_jobs.job_id%type
,P_job_level_old pa_resources_denorm.resource_job_level%type
,P_job_level_new pa_resources_denorm.resource_job_level%type
,P_job_group_id per_job_groups.job_group_id%type
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_job_id PER_JOBS.JOB_ID%type;
SELECT distinct pjr.from_job_id effected_job_id
FROM pa_job_relationships pjr
WHERE pjr.to_job_id = l_job_id
AND pjr.to_job_group_id = l_job_group_id
UNION
SELECT distinct pjr.to_job_id effected_job_id
FROM pa_job_relationships pjr
WHERE pjr.from_job_id = l_job_id
AND pjr.from_job_group_id = l_job_group_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.perform_job_updates');
log_message('**** Performing Job Level Updates ****');
update_job_level_res_denorm
( P_job_id_old => l_job_id
,P_job_id_new => l_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_all_jobs
( P_job_id => l_master_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_all_jobs
( P_job_id => l_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
p_procedure_name => 'perform_job_updates',
p_error_text => x_msg_data );
END perform_job_updates;
PROCEDURE update_job_level_dff
( P_job_id per_jobs.job_id%type
,P_job_level_old pa_resources_denorm.resource_job_level%type
,P_job_level_new pa_resources_denorm.resource_job_level%type
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) IS
l_job_id PER_JOBS.JOB_ID%type;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_dff');
log_message('Update Denorm for the Job Id and Job Level');
update_job_level_res_denorm
( P_job_id_old => l_job_id
,P_job_id_new => l_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
perform_job_updates
( P_job_id => l_job_id
,P_job_level_old => l_job_level_old
,P_job_level_new => l_job_level_new
,P_job_group_id => l_job_group_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
p_procedure_name => 'update_job_level_dff',
p_error_text => x_msg_data );
END update_job_level_dff;
SELECT l_job_id effected_job_id
FROM sys.dual
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjg.job_group_id = get_job_group_id(l_job_id)
UNION
SELECT distinct pjr.from_job_id effected_job_id
FROM pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.to_job_id = l_job_id
AND pjr.to_job_group_id = pjg.job_group_id
UNION
SELECT distinct pjr.to_job_id effected_job_id
FROM pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.from_job_id = l_job_id
AND pjr.from_job_group_id = pjg.job_group_id
UNION
SELECT l_job_id effected_job_id
FROM sys.dual
,per_job_groups pjg
WHERE pjg.master_flag = 'N'
AND pjg.job_group_id = get_job_group_id(l_job_id)
AND NOT EXISTS (
SELECT 'Y'
FROM per_job_groups
WHERE master_flag = 'Y'
AND job_group_id = get_job_group_id(l_job_id)
);
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_valid_grades_job_id');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_valid_grades_job_id'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT distinct pvg.job_id
FROM per_valid_grades pvg
,per_job_groups pjg
WHERE
pvg.grade_id = l_grade_id
AND pjg.master_flag = 'Y'
AND pjg.job_group_id = get_job_group_id(pvg.job_id)
UNION
SELECT distinct pjr.from_job_id
FROM per_valid_grades pvg
,pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.to_job_id = pvg.job_id
AND pjr.to_job_group_id = pjg.job_group_id
AND pvg.grade_id = l_grade_id
UNION
SELECT distinct pjr.to_job_id
FROM per_valid_grades pvg
,pa_job_relationships pjr
,per_job_groups pjg
WHERE pjg.master_flag = 'Y'
AND pjr.from_job_id = pvg.job_id
AND pjr.from_job_group_id = pjg.job_group_id
AND pvg.grade_id = l_grade_id
UNION
SELECT distinct pvg.job_id
FROM per_valid_grades pvg
,per_job_groups pjg
WHERE pjg.master_flag = 'N'
AND pjg.job_group_id = get_job_group_id(pvg.job_id)
AND pvg.grade_id = l_grade_id
AND NOT EXISTS (
SELECT 'Y'
FROM per_job_groups
WHERE master_flag = 'Y'
AND job_group_id = get_job_group_id(pvg.job_id)
);
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_grades_job_id');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_grades_job_id'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
PROCEDURE update_job_levels
( P_calling_mode IN VARCHAR2
,P_per_grades_grade_id IN per_grades.grade_id%type DEFAULT NULL
,P_per_grades_sequence_old IN NUMBER DEFAULT NULL
,P_per_grades_sequence_new IN NUMBER DEFAULT NULL
,P_per_valid_grade_job_id IN per_valid_grades.valid_grade_id%type DEFAULT NULL
,P_per_valid_grade_id_old IN per_grades.grade_id%type DEFAULT NULL
,P_per_valid_grade_id_new IN per_grades.grade_id%type DEFAULT NULL
,P_from_job_id_old IN pa_job_relationships.from_job_id%type DEFAULT NULL
,P_from_job_id_new IN pa_job_relationships.from_job_id%type DEFAULT NULL
,P_to_job_id_old IN pa_job_relationships.to_job_id%type DEFAULT NULL
,P_to_job_id_new IN pa_job_relationships.to_job_id%type DEFAULT NULL
,P_from_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
,P_to_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
)IS
v_return_status VARCHAR2(2000);
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_job_levels');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_job_levels'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_job_levels;
PROCEDURE Update_OU_resource(P_default_OU_old IN Pa_all_organizations.org_id%type
,P_default_OU_new IN Pa_all_organizations.org_id%type
,P_resource_id IN Pa_Resources_denorm.resource_id%type
default NULL
,P_person_id IN Pa_Resources_denorm.person_id%type
default NULL
,P_start_date IN Date default NULL
,P_end_date_old IN Date default NULL
,P_end_date_new IN Date default NULL
,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
)IS
v_return_status VARCHAR2(2000);
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND nvl(p_end_date_new, sysdate) >= resource_effective_start_date
AND resource_effective_start_date >= p_start_date
AND resource_effective_end_date <= p_end_date_old
AND resource_effective_end_date =
(Select max(resource_effective_end_date)
from pa_resources_denorm rd2
where rd2.person_id = p_person_id)
-- bug#10235228
;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_OU_resource');
PA_RESOURCE_PVT.update_resource_denorm (
p_resource_denorm_old_rec => v_resource_rec_old
,p_resource_denorm_new_rec => v_resource_rec_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
Update_EndDate(
p_person_id => p_person_id,
p_old_start_date => p_start_date,
p_new_start_date => p_start_date,
p_old_end_date => p_end_date_old,
p_new_end_date => p_end_date_new,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_OU_resource'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_OU_resource;
SELECT distinct
ind.person_id
,ind.assignment_start_date
,ind.assignment_end_date
,to_number(hoi.org_information1) default_OU
FROM pa_r_project_resources_ind_v ind
,hr_organization_information hoi
WHERE ind.organization_id =
/* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
AND ind.assignment_end_date >= sysdate
AND hoi.organization_id = l_organization_id
AND hoi.org_information_context = 'Exp Organization Defaults'
AND ind.organization_id = l_organization_id -- 4898509
ORDER BY ind.person_id,ind.assignment_start_date ;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Default_OU_Change');
(P_calling_mode => 'UPDATE'
,P_Organization_id => P_Organization_id
,P_Default_OU => P_Default_OU_old
,P_Default_OU_NEW => v_default_OU
,P_inactive_date => trunc(sysdate)
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
) ;
pa_hr_update_api.check_exp_OU
(p_org_id => v_default_OU
,x_return_status => v_return_status
,x_error_message_code => v_error_message_code
);
If P_calling_mode = 'UPDATE' then
-- check for whether the default OU is changed if so call
-- check OU change api to update the resource OU entity
If (NVL(P_default_OU_old,-99) <> nvl(P_default_OU_new,-99)) then
-- if OU is updated then call resource denorm api to
-- reflect the changes in pa_resources_denorm entity
If v_person_id is NOT NULL then
Update_OU_resource
(P_default_OU_old => p_default_OU_old
,p_default_OU_new => p_default_OU_new
,P_person_id => v_person_id
,P_start_date => v_assn_start_date
,P_end_date_old => v_assn_end_date
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
Elsif P_calling_mode = 'INSERT' then
-- the P_calling_mode is 'INSERT'
-- this api is called to populate resources whenever a new record is added in
-- in Hr_organizatioin_information entity
-- or due to insert in pa_all_organizations entity
PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
P_API_VERSION => L_API_VERSION
,P_COMMIT => v_commit
,P_VALIDATE_ONLY => v_validate_only
,P_INTERNAL => v_internal
,P_PERSON_ID => v_person_id
,P_INDIVIDUAL => v_individual
,P_RESOURCE_TYPE => v_resource_type
,X_RETURN_STATUS => x_return_status
,X_RESOURCE_ID => v_dummy
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Default_OU_Change'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT job_id
FROM per_jobs
WHERE job_group_id = l_proj_job_group_new
AND business_group_id = p_organization_id;
SELECT job_id
FROM per_jobs
WHERE job_group_id = l_proj_job_group_old
AND business_group_id = p_organization_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Proj_Res_Job_Group_Change');
IF p_calling_mode = 'INSERT' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
-- When Insert, we update all resource denorm records with job_id that
-- belongs to the new Project Resource Job Group Id
OPEN get_new_job_ids;
update_job_level_dff
(P_job_id => l_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
ELSIF p_calling_mode = 'UPDATE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
-- When update we have to set the resource denorm records job level to NULL for job id
-- that belongs to the old Project Resource Job Group Id
OPEN get_old_job_ids;
perform_job_updates
(P_job_id => l_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level
,P_job_group_id => l_job_group_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
update_job_level_dff
(P_job_id => l_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
ELSIF p_calling_mode = 'DELETE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
-- When Delete, we have to set the resource denorm records job level to NULL for job id
-- that belongs to the old Project Resource Job Group Id
OPEN get_old_job_ids;
update_job_level_dff
(P_job_id => l_job_id
,P_job_level_old => NULL
,P_job_level_new => l_job_level
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Proj_Res_Job_Group_Change'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT distinct ind.person_id
FROM pa_r_project_resources_ind_v ind
,hr_organization_information hoi
WHERE ind.organization_id =
/* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
AND ind.assignment_end_date >= sysdate
AND hoi.organization_id = l_organization_id
AND hoi.org_information_context = 'Exp Organization Defaults'
ORDER BY ind.person_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pull_resources');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pull_resources'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT
distinct
assn.person_id
,assn.effective_start_date
,assn.effective_end_date
, res.resource_id
, hrinf.org_information1
FROM per_all_assignments_f assn
, hr_organization_information hrinf
/* , per_person_types pertypes Commented for bug#2781713 */
, per_assignment_status_types pastype
, pa_resource_txn_attributes res
, pa_all_organizations allorgs
, per_all_people_f pep
WHERE
assn.assignment_status_type_id = pastype.assignment_status_type_id
AND assn.person_id = res.person_id
AND assn.primary_flag = 'Y'
AND assn.assignment_type in ('E', 'C') -- CWK Changes
AND assn.organization_id = allorgs.organization_id
AND assn.organization_id = hrinf.organization_id
AND assn.effective_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND assn.effective_end_date >= trunc(sysdate)
AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
AND hrinf.org_information_context = 'Exp Organization Defaults'
/* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
/* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
pep.current_npw_flag = 'Y') -- CWK Changes
AND pep.person_id = assn.person_id
AND allorgs.organization_id = P_organization_id
AND allorgs.org_id = P_default_OU
AND allorgs.pa_org_use_type = 'EXPENDITURES'
AND allorgs.inactive_date is Not null
AND (allorgs.organization_id,allorgs.org_id) = (
SELECT exporg.organization_id, exporg.org_id
FROM pa_all_organizations exporg
WHERE exporg.pa_org_use_type = 'EXPENDITURES'
AND exporg.inactive_date is Not null
AND exporg.organization_id = allorgs.organization_id
AND exporg.org_id = allorgs.org_id
AND rownum = 1 );
SELECT
distinct
assn.person_id
,assn.effective_start_date
,assn.effective_end_date
, res.resource_id
-- , hrinf.org_information1
FROM per_all_assignments_f assn
, hr_organization_information hrinf
/* , per_person_types pertypes Commented for bug#2781713 */
, per_assignment_status_types pastype
, pa_resource_txn_attributes res
, pa_all_organizations allorgs
, per_all_people_f pep
WHERE
assn.assignment_status_type_id = pastype.assignment_status_type_id
AND assn.person_id = res.person_id
AND assn.primary_flag = 'Y'
AND assn.assignment_type in ('E', 'C') -- CWK Changes
AND assn.organization_id = allorgs.organization_id
AND assn.organization_id = hrinf.organization_id
AND assn.effective_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND assn.effective_end_date >= trunc(sysdate)
AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
AND hrinf.org_information_context = 'Exp Organization Defaults'
/* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
/* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
pep.current_npw_flag = 'Y') -- CWK Changes
AND pep.person_id = assn.person_id
AND allorgs.organization_id = P_organization_id
AND allorgs.org_id = P_default_OU -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
AND allorgs.pa_org_use_type = 'EXPENDITURES'
-- AND allorgs.inactive_date is Not null
AND (allorgs.organization_id,allorgs.org_id) = ( -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
SELECT exporg.organization_id, exporg.org_id
FROM pa_all_organizations exporg
WHERE exporg.pa_org_use_type = 'EXPENDITURES'
-- AND exporg.inactive_date is Not null
AND exporg.organization_id = allorgs.organization_id
AND exporg.org_id = allorgs.org_id -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
AND rownum = 1 );
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.make_resource_inactive');
P_Default_OU_NEW will be null when called for orghierarchy update
P_Default_OU_NEW will not be null when called for nulling out default OU
*/
IF p_inactive_date is NOT NULL and p_calling_mode = 'UPDATE' then
if P_Default_OU_NEW is null then
open get_all_inactive_resource;
-- update the resource denorm with end date the resources
Update_OU_resource (
P_default_OU_old => v_default_OU
,p_default_OU_new => v_default_OU
,P_person_id => v_person_id
,P_start_date => v_assn_start_date
,P_end_date_old => v_assn_end_date
,P_end_date_new => p_inactive_date
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.make_resource_inactive'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT distinct
assn.person_id
,assn.effective_start_date
,assn.effective_end_date
FROM per_all_assignments_f assn
, hr_organization_information hrinf
/* , per_person_types pertypes Commented for Bug#2781713 */
, per_assignment_status_types pastype
, per_all_people_f pep
WHERE
assn.assignment_status_type_id = pastype.assignment_status_type_id
AND assn.primary_flag = 'Y'
AND assn.assignment_type in ('E', 'C') -- CWK Changes
AND assn.job_id = l_job_id
AND assn.organization_id = hrinf.organization_id
AND assn.effective_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND assn.effective_end_date >= trunc(sysdate)
AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
AND hrinf.org_information_context = 'Exp Organization Defaults'
/* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
/* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
pep.current_npw_flag = 'Y') -- CWK Changes
AND pep.person_id = assn.person_id
AND assn.organization_id =
(SELECT exporg.organization_id
FROM pa_all_organizations exporg
WHERE exporg.pa_org_use_type = 'EXPENDITURES'
AND exporg.inactive_date is null
AND exporg.organization_id = assn.organization_id
AND rownum = 1 )
ORDER BY 1,2;
SELECT distinct
assn.person_id
,assn.effective_start_date
,assn.effective_end_date
FROM per_all_assignments_f assn
, hr_organization_information hrinf
/* , per_person_types pertypes Commented for Bug#2781713 */
, per_assignment_status_types pastype
, per_all_people_f pep
WHERE
assn.assignment_status_type_id = pastype.assignment_status_type_id
AND assn.primary_flag = 'Y'
AND assn.assignment_type in ('E', 'C') -- CWK Changes
AND assn.organization_id = l_org_id
AND assn.organization_id = hrinf.organization_id
AND assn.effective_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND assn.effective_end_date >= trunc(sysdate)
AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
AND hrinf.org_information_context = 'Exp Organization Defaults'
/* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
/* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
pep.current_npw_flag = 'Y') -- CWK Changes
AND pep.person_id = assn.person_id
AND assn.organization_id =
(SELECT exporg.organization_id
FROM pa_all_organizations exporg
WHERE exporg.pa_org_use_type = 'EXPENDITURES'
AND exporg.inactive_date is null
AND exporg.organization_id = assn.organization_id
AND rownum = 1 )
ORDER BY 1,2;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_forcast_api');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_forcast_api'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_billable_resoruce_denorm');
PA_RESOURCE_PVT.update_resource_denorm
( p_resource_denorm_old_rec => v_resource_rec_old
,p_resource_denorm_new_rec => v_resource_rec_new
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_billable_resoruce_denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
max(resource_effective_end_date) resource_effective_end_date
FROM pa_resources_denorm
WHERE job_id = p_job_id
GROUP BY person_id; */
SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
max(resource_effective_end_date) resource_effective_end_date
FROM pa_resources_denorm
WHERE job_id = p_job_id
AND resource_effective_end_date = (Select max(resource_effective_end_date)
from pa_resources_denorm rd2
where rd2.job_id = p_job_id
AND (rd2.resource_effective_end_date >= sysdate OR rd2.resource_effective_end_date is null))
GROUP BY person_id;
SELECT DISTINCT res.person_id person_id
FROM pa_r_project_resources_v res
WHERE res.job_id = p_job_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_job_extra_billability');
If P_calling_mode = 'INSERT' THEN
l_pull_res_flag := 'Y';
ELSIF P_calling_mode = 'UPDATE' THEN
-- If p_utilize_flag_old='N' AND p_utilize_flag_new='Y', we need to
-- pull the people who have the job
IF p_utilize_flag_old='N' AND p_utilize_flag_new='Y' THEN
l_pull_res_flag := 'Y';
update_job_level_dff
(P_job_id => P_job_id
,P_job_level_old => TO_NUMBER(P_job_level_old)
,P_job_level_new => TO_NUMBER(P_job_level_new)
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
log_message('After update_job_level_dff');
ELSIF P_calling_mode = 'DELETE' THEN
IF p_utilize_flag_old ='Y' THEN
l_end_date_res_flag := 'Y';
log_message('P_calling_mode=DELETE, p_utilize_flag_old:'||p_utilize_flag_old
||', l_end_date_res_flag:'||l_end_date_res_flag);
update_job_level_dff
(P_job_id => P_job_id
,P_job_level_old => TO_NUMBER(P_job_level_old)
,P_job_level_new => NULL
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count );
log_message('After calling update_job_level_dff');
Update_EndDate
(p_person_id => rec.person_id,
p_old_start_date => rec.resource_effective_start_date,
p_new_start_date => rec.resource_effective_start_date,
p_old_end_date => rec.resource_effective_end_date,
p_new_end_date => sysdate,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
log_message('Return status from Update_EndDate = ' || x_return_status);
log_message('error msg from Update_EndDate: ' || substr(x_msg_data,1,200));
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_job_extra_billability'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
select cand.candidate_id, cand.record_version_number
from pa_candidates cand,
pa_project_assignments asgmt,
pa_project_statuses ps
where cand.resource_id = l_resource_id
and cand.assignment_id = asgmt.assignment_id
and asgmt.assignment_type = 'OPEN_ASSIGNMENT'
and asgmt.status_code = ps.project_status_code (+)
and (ps.project_system_status_code = 'OPEN_ASGMT'
OR ps.project_system_status_code is null)
and asgmt.start_date > trunc(p_effective_date)
and cand.status_code not in
(select ps2.project_status_code
from pa_project_statuses ps2
where ps2.status_type='CANDIDATE'
and ps2.project_system_status_code IN
('CANDIDATE_DECLINED','CANDIDATE_WITHDRAWN'));
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.withdraw_cand_nominations');
PA_CANDIDATE_PUB.Update_Candidate
(p_candidate_id => rec.candidate_id,
p_status_code => l_status_code,
p_ranking => null,
p_change_reason_code => null,
p_record_version_number => rec.record_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_validate_status => FND_API.G_FALSE,
-- Added for bug 9187892
p_attribute_category => l_candidate_in_rec.attribute_category,
p_attribute1 => l_candidate_in_rec.attribute1,
p_attribute2 => l_candidate_in_rec.attribute2,
p_attribute3 => l_candidate_in_rec.attribute3,
p_attribute4 => l_candidate_in_rec.attribute4,
p_attribute5 => l_candidate_in_rec.attribute5,
p_attribute6 => l_candidate_in_rec.attribute6,
p_attribute7 => l_candidate_in_rec.attribute7,
p_attribute8 => l_candidate_in_rec.attribute8,
p_attribute9 => l_candidate_in_rec.attribute9,
p_attribute10 => l_candidate_in_rec.attribute10,
p_attribute11 => l_candidate_in_rec.attribute11,
p_attribute12 => l_candidate_in_rec.attribute12,
p_attribute13 => l_candidate_in_rec.attribute13,
p_attribute14 => l_candidate_in_rec.attribute14,
p_attribute15 => l_candidate_in_rec.attribute15,
x_record_version_number => l_rec_ver_num,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
log_message('Return status from Update Candidate = ' || x_return_status);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.withdraw_cand_nominations'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
/* Procedure Update_EndDate calls Update_Resource_Denorm to update the
end date in pa_resources_denorm and update FI data for the resource.
This procedure now also handles automatic candidates withdrawal.
It is called whenever a resource is terminated in HR, whenever
the change in assignment organization which does not belong to Exp Hier,
or whenever an organization is taken out from the Exp Hier. In these
cases, the resource is considered no longer active in PJR.
*/
PROCEDURE Update_EndDate(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
-- AND p_new_end_date >= resource_effective_start_date
AND resource_effective_start_date >= p_old_start_date
AND resource_effective_end_date <= p_old_end_date
;
SELECT MAX (asgn_end_date) FROM
(select max(res.assignment_end_date) asgn_end_date
from pa_r_project_resources_v res
where res.person_id = p_person_id
and res.assignment_end_date IS NOT NULL
UNION
select max(res.assignment_end_date) ass_end_date
from pa_r_project_resources_term_v res
where res.person_id = p_person_id
and res.assignment_end_date IS NOT NULL );
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_EndDate');
PA_RESOURCE_PVT.update_resource_denorm(
p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
--Delete this record
pa_resource_pvt.delete_resource_denorm(
p_person_id => p_person_id
,p_res_effective_start_date => rec.resource_effective_start_date
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
SELECT MAX(paaf.effective_end_date)
INTO l_resource_effective_end_date
FROM per_all_assignments_f paaf,
per_assignment_status_types past
WHERE paaf.person_id=p_person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type in ('E','C')
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK');
SELECT max(assignment_end_date)
INTO l_resource_effective_end_date
FROM pa_r_project_resources_v
WHERE person_id = p_person_id;
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_EndDate'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_EndDate;
/* Procedure Update_Org calls Update_Resource_Denorm and Create_Forecast_Item to update the organization and org_id for the resources in pa_resources_denorm table and regenerate forecast items for the resource respectively.
*/
PROCEDURE Update_Org(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_org_id IN per_all_assignments_f.organization_id%TYPE,
p_new_org_id IN per_all_assignments_f.organization_id%TYPE,
p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_default_ou_old pa_resources_denorm.resource_org_id%TYPE;
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND resource_effective_start_date >= p_new_start_date
AND resource_effective_end_date <= p_new_end_date
;
select decode(substr(USERENV('CLIENT_INFO'),1,1),
' ', NULL,
substr(USERENV('CLIENT_INFO'),1,10)) org from dual; */
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Org');
and update pa_resource_ou.organization.*/
IF (Belongs_ExpOrg(p_new_org_id) = 'Y') THEN
-- If the old org Id does not belong to exp hier,
-- we have to fix data in resource denorm (we end dated that
-- record previously when the org changes from Exp Hier
-- to Non Exp Hier). So calling Create_Resource
IF(Belongs_ExpOrg(p_old_org_id) = 'N') THEN
pa_r_project_resources_pub.create_resource (
p_api_version => 1.0
,p_init_msg_list => NULL
,p_commit => FND_API.G_FALSE
,p_validate_only => NULL
,p_max_msg_count => NULL
,p_internal => 'Y'
,p_person_id => p_person_id
,p_individual => 'Y'
,p_resource_type => NULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_resource_id => l_resource_id);
PA_RESOURCE_PVT.update_resource_denorm(
p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
Update_EndDate(
p_person_id => p_person_id,
p_old_start_date => p_old_start_date,
p_new_start_date => p_new_start_date,
p_old_end_date => p_old_end_date,
p_new_end_date => sysdate,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Org'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Org;
SELECT 'Y' FROM dual WHERE exists
(SELECT 'Y' FROM hr_organization_information
WHERE organization_id = P_Organization_id
and org_information_context = 'Exp Organization Defaults'
and org_information1 = P_Default_OU_new);
SELECT 'x'
FROM dual
WHERE exists
(select organization_id
FROM pa_all_organizations
WHERE organization_id = p_org_id
AND inactive_date is null
AND pa_org_use_type = 'EXPENDITURES');
select to_number(org_information1)
into l_default_ou
from hr_organization_information
where organization_id = p_org_id
and org_information_context = 'Exp Organization Defaults';
Procedure Update_Job retrieves the job level for the job and calls
Update_Resource_Denorm and Create_Forecast_Item to update the denorm
table and regenerate forecast items for the resource respectively.
*/
PROCEDURE Update_Job(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_job IN per_all_assignments_f.job_id%TYPE,
p_new_job IN per_all_assignments_f.job_id%TYPE,
p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND resource_effective_start_date >= p_new_start_date
AND resource_effective_end_date <= p_new_end_date;
SELECT min(resource_effective_start_date) resource_effective_start_date,
max(resource_effective_end_date) resource_effective_end_date
FROM pa_resources_denorm
WHERE job_id = p_old_job
AND person_id = p_person_id
GROUP BY person_id;*/
SELECT min(resource_effective_start_date) resource_effective_start_date,
max(resource_effective_end_date) resource_effective_end_date
FROM pa_resources_denorm
WHERE job_id = p_old_job
AND person_id = p_person_id
AND resource_effective_end_date = (Select max(resource_effective_end_date)
from pa_resources_denorm rd2
where rd2.job_id = p_old_job
AND rd2.person_id = p_person_id
AND (rd2.resource_effective_end_date >= sysdate OR rd2.resource_effective_end_date is null))
GROUP BY person_id;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Job');
log_message('beginning of Update_Job');
Update_EndDate
(p_person_id => p_person_id,
p_old_start_date => l_resource_start_date,
p_new_start_date => l_resource_start_date,
p_old_end_date => l_resource_end_date,
p_new_end_date => sysdate,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
log_message('Return status from Update_EndDate = ' || x_return_status);
PA_RESOURCE_PVT.update_resource_denorm(
p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Job'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Job;
/* Procedure Update_Supervisor calls Update_Resource_Denorm to update
the resource's supervisor in the denorm table.
*/
PROCEDURE Update_Supervisor(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
p_new_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND p_new_end_date >= resource_effective_start_date
AND resource_effective_start_date >= p_new_start_date
AND resource_effective_end_date <= p_new_end_date
;
SELECT DISTINCT resource_name
FROM pa_resources_denorm
WHERE person_id = p_new_supervisor
;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Supervisor');
PA_RESOURCE_PVT.update_resource_denorm(
p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Supervisor'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Supervisor;
/* Procedure Update_PrimaryFlag calls Update_EndDate to end date the
record for which the assignment record's primary flag has changed form
yes to no and then calls Create_Forecast_Item to regenerate
the forecast items for this resource.
*/
PROCEDURE Update_PrimaryFlag(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(1);
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_PrimaryFlag');
--Call Update_EndDate to end date the resource_ou record.
-- Commented the end date change in PA as this is not required
-- Solves bug 1608837
/* Update_EndDate(p_person_id => p_person_id,
p_old_start_date => p_old_start_date,
p_new_start_date => p_new_start_date,
p_old_end_date => p_old_end_date,
p_new_end_date => p_new_end_date,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_PrimaryFlag'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_PrimaryFlag;
/* Procedure Update_Name calls updates the resource's name in pa_resources.
*/
PROCEDURE Update_Name(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_name IN per_all_people_f.full_name%TYPE,
p_new_name IN per_all_people_f.full_name%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
l_resource_id pa_resources.resource_id%TYPE;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Name');
UPDATE pa_resources
SET name = p_new_name
WHERE resource_id = l_resource_id;
PA_RESOURCE_PVT.update_resource_denorm(
p_resource_denorm_old_rec => l_resource_rec_old
,p_resource_denorm_new_rec => l_resource_rec_new
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Name'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Name;
SELECT territory_short_name
INTO l_country_code
FROM fnd_territories_vl
WHERE territory_code = p_country_code ;
SELECT name
INTO l_job_name
FROM per_jobs
WHERE job_id = P_job_id;
SELECT name
INTO l_org_name
FROM hr_all_organization_units_tl
WHERE organization_id = P_org_id
AND language = USERENV('LANG');
SELECT name
INTO l_grade_name
FROM per_grades
WHERE grade_id = P_grade_id;
PROCEDURE Delete_PA_Resource_Denorm(
p_person_id IN per_all_people_f.person_id%TYPE,
p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
p_old_end_date IN per_all_assignments_f.effective_end_date%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
CURSOR res_denorm_recs IS
SELECT resource_effective_start_date,
resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND resource_effective_start_date >= p_old_start_date
AND resource_effective_end_date <= p_old_end_date
;
PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Delete_PA_Resource_Denorm');
--Delete the record
DELETE FROM PA_RESOURCES_DENORM
WHERE person_id = p_person_id
AND resource_effective_start_date = rec.resource_effective_start_date;
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Delete_PA_Resource_Denorm'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Delete_PA_Resource_Denorm;