[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'T', grant_guid
from fnd_grants
where grantee_key = p_grantee_key
and grantee_type = 'USER'
and instance_set_id = p_instance_set_id
and grantee_type = p_grantee_type
and instance_type = p_instance_type
and menu_id = p_menu_id;
select
pp.resource_source_id into l_person_id
from pa_project_parties pp, pa_project_role_types_b rlt
where pp.object_type = 'PJP_PORTFOLIO'
and pp.object_id = p_portfolio_id
and PP.project_role_id = rlt.project_role_id
and rlt.project_role_type = G_OWNER;
select
project_role_type into l_role
from pa_project_role_types_b
where project_role_id = p_project_role_id;
select
project_role_id into l_role_id
from pa_project_role_types_b
where project_role_type = p_project_role;
SELECT FULL_NAME
INTO l_party_name
FROM PER_ALL_PEOPLE_F PPF
WHERE PERSON_ID = p_person_id;
select 'T'
from pa_project_parties
where project_role_id = p_project_role_id
and object_type = 'PJP_PORTFOLIO'
and resource_type_id = 101
and resource_source_id = p_party_id
and rownum=1;
SELECT 'T'
FROM PA_PROJECT_PARTIES
WHERE OBJECT_TYPE = 'PJP_PORTFOLIO'
AND OBJECT_ID = P_PORTFOLIO_ID
AND RESOURCE_SOURCE_ID = P_PERSON_ID;
PROCEDURE Update_Portfolio_User
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_portfolio_party_id IN PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE,
p_project_role_id IN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
-- standard parameters
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'Update_Portfolio_User';
CURSOR update_rec_csr (p_project_party_id IN NUMBER) IS
select
object_id,
object_type,
project_id,
resource_id,
resource_type_id,
resource_source_id,
project_role_id,
start_date_active,
end_date_active,
scheduled_flag,
record_version_number,
grant_id
from pa_project_parties
where project_party_id = p_project_party_id;
p_msg_log => 'Entering fpa_security_pvt.update_portfolio_user',
x_return_status => x_return_status);
open update_rec_csr(p_portfolio_party_id);
fetch update_rec_csr into
l_object_id,
l_object_type,
l_project_id,
l_resource_id,
l_resource_type_id,
l_resource_source_id,
l_project_role_id,
l_start_date_active,
l_end_date_active,
l_scheduled_flag,
l_record_version_number,
l_grant_id;
close update_rec_csr;
NOT ABLE TO UPDATE USING BELOW CALL WITH VALIDATIONS AS ROLE ID IS IGNORED FOR UPDATE
WHEN "PA_INSTALL.IS_PRM_LICENSED()" IS 'Y'. NEED TO FIND OUT DETAILS ON THE
PROFILE OPTION IF BELOW CALL WITH VALIDATIONS CAN BE USED.
*/
/*
PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY(
p_commit => FND_API.G_FALSE,
p_validate_only => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_debug_mode => 'N',
p_object_id => l_object_id,
p_object_type => l_object_type,
p_project_role_id => p_project_role_id,
p_resource_type_id => l_resource_type_id,
p_resource_source_id => l_resource_source_id,
p_resource_id => l_resource_id,
p_start_date_active => l_start_date_active,
p_scheduled_flag => l_scheduled_flag,
p_record_version_number => l_record_version_number,
p_calling_module => 'FORM',
p_project_id => null,
p_project_end_date => l_end_date_active,
p_project_party_id => p_portfolio_party_id,
p_assignment_id => 0,
p_assign_record_version_number => null,
p_end_date_active => l_end_date_active,
x_assignment_id => l_assignment_id,
x_wf_type => l_wf_type,
x_wf_item_type => l_wf_item_type,
x_wf_process => l_wf_process,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE',
l_object_id,
l_end_date_active,
l_end_date_active,
l_project_party_id,
x_call_overlap,
x_assignment_action,
x_return_status);
UPDATE pa_project_parties SET
project_role_id = p_project_role_id,
start_date_active = l_start_date_active,
end_date_active = l_end_date_active,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE project_party_id = p_portfolio_party_id;
END Update_Portfolio_User;
PROCEDURE Update_Portfolio_Owner
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_portfolio_id IN NUMBER,
p_person_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
-- standard parameters
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'Update_Portfolio_Owner';
select
pp.project_party_id,
pp.resource_source_id,
pp.project_role_id
from pa_project_parties pp, pa_project_role_types_b rlt
where pp.object_type = 'PJP_PORTFOLIO'
and pp.object_id = p_portfolio_id
and PP.project_role_id = rlt.project_role_id
and rlt.project_role_type = G_OWNER;
p_msg_log => 'Entering fpa_security_pvt.update_portfolio_owner',
x_return_status => x_return_status);
p_msg_log => 'Exiting fpa_security_pvt.update_portfolio_owner',
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY(
p_commit => FND_API.G_FALSE,
p_validate_only => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_debug_mode => 'N',
p_object_id => l_object_id,
p_object_type => l_object_type,
p_project_role_id => p_project_role_id,
p_resource_type_id => l_resource_type_id,
p_resource_source_id => l_resource_source_id,
p_resource_id => l_resource_id,
p_start_date_active => l_start_date_active,
p_scheduled_flag => l_scheduled_flag,
p_record_version_number => l_record_version_number,
p_calling_module => 'FORM',
p_project_id => null,
p_project_end_date => l_end_date_active,
p_project_party_id => p_portfolio_party_id,
p_assignment_id => 0,
p_assign_record_version_number => null,
p_end_date_active => l_end_date_active,
x_assignment_id => l_assignment_id,
x_wf_type => l_wf_type,
x_wf_item_type => l_wf_item_type,
x_wf_process => l_wf_process,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
NOT ABLE TO UPDATE USING ABOVE CALL WITH VALIDATIONS AS ROLE ID IS IGNORED FOR UPDATE
WHEN "PA_INSTALL.IS_PRM_LICENSED()" IS 'Y'. NEED TO FIND OUT DETAILS ON THE
PROFILE OPTION IF ABOVE CALL WITH VALIDATIONS CAN BE USED.
*/
UPDATE pa_project_parties SET
resource_source_id = p_person_id
WHERE project_party_id = l_project_party_id;
END Update_Portfolio_Owner;
Desc: Call to delete portfolio user and the the grant for the role.
parameters:
p_portfolio_party_id -> pa_project_parties.project_party_id.
***************************************************************** */
PROCEDURE Delete_Portfolio_User
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_portfolio_party_id IN PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE,
p_instance_set_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR project_parties_csr (p_portfolio_party_id in number) IS
SELECT project_role_id, resource_source_id, object_id
FROM pa_project_parties
WHERE project_party_id = p_portfolio_party_id;
CURSOR verify_delete_csr (p_portfolio_party_id in number) IS
SELECT 'T'
FROM pa_project_parties
WHERE project_party_id = p_portfolio_party_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Portfolio_User';
p_msg_log => 'Entering fpa_security_pvt.delete_portfolio_user',
x_return_status => x_return_status);
, p_msg_name => 'FPA_SEC_DELETE_FAILED'
, p_token1 => 'USER'
,p_token1_value => l_user);
l_msg_log := 'FPA_SEC_DELETE_FAILED'||p_portfolio_party_id;
PA_PROJECT_PARTIES_PKG.Delete_Row(x_project_id => null,
x_project_party_id => p_portfolio_party_id,
x_record_version_number => null);
open verify_delete_csr(p_portfolio_party_id);
fetch verify_delete_csr into l_exists;
close verify_delete_csr;
, p_msg_name => 'FPA_SEC_DELETE_FAILED'
, p_token1 => 'USER'
,p_token1_value => l_user);
l_msg_log := 'FPA_SEC_DELETE_FAILED '||p_portfolio_party_id;
p_msg_log => 'end fpa_security_pvt.Delete_Portfolio_User',
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END Delete_Portfolio_User;