[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_exists
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
AND rownum = 1;
SELECT 'Y'
INTO l_exists
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date));
SELECT profile_id,
effective_end_date
INTO l_prev_profile_id,
l_end_date
FROM pa_role_profiles
WHERE effective_start_date = (SELECT max(effective_start_date)
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(effective_start_date) < TRUNC(p_effective_start_date)
AND effective_end_date IS NULL)
AND resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1);
UPDATE pa_role_profiles
SET effective_end_date = p_effective_start_date - 1
WHERE profile_id = l_prev_profile_id;
PA_ROLE_PROFILES_PKG.Insert_Row1
( p_profile_name => p_profile_name,
p_description => p_description,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_profile_type_code => 'ACTUAL',
p_business_group_id => l_business_group_id,
p_organization_id => l_organization_id,
p_job_id => l_job_id,
p_position_id => l_position_id,
x_profile_id => l_profile_id,
x_return_status => l_return_status);
PA_ROLE_PROFILES_PKG.Insert_Row2
( p_profile_id => l_profile_id,
p_project_role_id => l_role_id_tbl(i),
p_role_weighting => p_weighting_tbl(i),
x_return_status => l_return_status);
PROCEDURE Update_Default_Profile
( p_profile_id IN NUMBER,
p_business_group_id IN NUMBER DEFAULT NULL,
p_business_group_name IN VARCHAR2 DEFAULT NULL,
p_organization_id IN NUMBER DEFAULT NULL,
p_organization_name IN VARCHAR2 DEFAULT NULL,
p_job_id IN NUMBER DEFAULT NULL,
p_job_name IN VARCHAR2 DEFAULT NULL,
p_position_id IN NUMBER DEFAULT NULL,
p_position_name IN VARCHAR2 DEFAULT NULL,
p_profile_name IN VARCHAR2,
p_description IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE DEFAULT NULL,
p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_business_group_id NUMBER;
PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Update_Default_Profile');
SELECT 'Y'
INTO l_exists
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
AND profile_id <> p_profile_id
AND rownum = 1;
SELECT 'Y'
INTO l_exists
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date))
AND profile_id <> p_profile_id;
SELECT profile_id,
effective_end_date
INTO l_prev_profile_id,
l_end_date
FROM pa_role_profiles
WHERE effective_start_date = (SELECT max(effective_start_date)
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND TRUNC(effective_start_date) < TRUNC(p_effective_start_date)
AND effective_end_date IS NULL
AND profile_id <> p_profile_id)
AND resource_id IS NULL
AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
AND NVL(job_id, -1) = NVL(l_job_id, -1)
AND NVL(position_id, -1) = NVL(l_position_id, -1)
AND profile_id <> p_profile_id;
UPDATE pa_role_profiles
SET effective_end_date = p_effective_start_date - 1
WHERE profile_id = l_prev_profile_id;
PA_ROLE_PROFILES_PKG.Update_Row
( p_profile_id => p_profile_id,
p_profile_name => p_profile_name,
p_description => p_description,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_business_group_id => l_business_group_id,
p_organization_id => l_organization_id,
p_job_id => l_job_id,
p_position_id => l_position_id,
x_return_status => l_return_status);
DELETE FROM pa_role_profile_lines
WHERE profile_id = p_profile_id;
PA_ROLE_PROFILES_PKG.Insert_Row2
( p_profile_id => p_profile_id,
p_project_role_id => l_role_id_tbl(i),
p_role_weighting => p_weighting_tbl(i),
x_return_status => l_return_status);
END Update_Default_Profile;
PROCEDURE Delete_Profile
( p_profile_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
DELETE FROM pa_role_profiles
WHERE profile_id = p_profile_id;
DELETE FROM pa_role_profile_lines
WHERE profile_id = p_profile_id;
End Delete_Profile;
SELECT (decode(position_id, null, 0, 4)
+ decode(job_id, null, 0, 3)
+ decode(organization_id, null, 0, 2)
+ decode(business_group_id, null, 0, 1)) AS weight,
profile_id
FROM pa_role_profiles
WHERE resource_id IS NULL
AND NVL(position_id,l_position_id) = l_position_id
AND NVL(job_id, l_job_id) = l_job_id
AND NVL(organization_id,l_organization_id) = l_organization_id
AND NVL(business_group_id,l_business_group_id) = l_business_group_id
AND TRUNC(p_resource_start_date) BETWEEN TRUNC(effective_start_date)
AND NVL(TRUNC(effective_end_date), TRUNC(p_resource_start_date));
SELECT profile_name,
description,
effective_start_date,
effective_end_date,
profile_type_code
INTO l_profile_name,
l_description,
l_profile_start_date,
l_profile_end_date,
l_profile_type_code
FROM pa_role_profiles
WHERE profile_id = l_profile_id;
PA_ROLE_PROFILES_PKG.Insert_Row1
( p_profile_name => l_profile_name,
p_description => l_description,
p_effective_start_date => p_resource_start_date,
p_effective_end_date => l_end_date,
p_profile_type_code => 'ACTUAL',
p_approval_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_approved,
p_resource_id => p_resource_id,
x_profile_id => l_res_profile_id,
x_return_status => l_return_status);
SELECT project_role_id,
role_weighting
BULK COLLECT INTO l_role_id_tbl,
l_weighting_tbl
FROM pa_role_profile_lines
WHERE profile_id = l_profile_id;
PA_ROLE_PROFILES_PKG.Insert_Row2
( p_profile_id => l_res_profile_id,
p_project_role_id => l_role_id_tbl(i),
p_role_weighting => l_weighting_tbl(i),
x_return_status => l_return_status);