Introduction
This Post illustrates the steps required to Update and assign project Roles using PA_PROJECT_PARTIES_PUB in Oracle EBS.
Script to Update and assign project Roles using PA_PROJECT_PARTIES_PUB
DECLARE
l_project_id_PREM PA_PROJECT_PARTIES.PROJECT_ID%TYPE :=NULL;
l_project_role VARCHAR2(240) :=NULL;
l_resource_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE :=NULL;
l_start_date_active DATE :=NULL;
l_end_date_active DATE :=NULL;
l_project_role_id pa_project_role_types.PROJECT_ROLE_ID%TYPE :=NULL;
l_project_role_type pa_project_role_types.PROJECT_ROLE_TYPE%TYPE :=NULL;
l_resource_source_id PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE :=NULL;
l_project_party_id PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE :=NULL;
l_object_id PA_PROJECT_PARTIES.OBJECT_ID%TYPE :=NULL;
l_resource_id PA_PROJECT_PARTIES.RESOURCE_ID%TYPE :=NULL;
l_record_version_number pa_project_parties.record_version_number%type :=null;
l_project_end_date DATE;
l_return_status VARCHAR2(20) :=NULL;
l_assignment_id NUMBER :=NULL;
l_wf_type VARCHAR2(240) :=NULL;
l_wf_item_type VARCHAR2(240) :=NULL;
l_wf_process VARCHAR2(240) :=NULL;
l_msg_count NUMBER :=NULL;
l_msg_data VARCHAR2(240) :=NULL;
BEGIN
—Input Parameters—-
l_project_id_PREM := ‘3631’;
l_project_role := ‘Project Manager’;
l_resource_name := ‘Holt, Mr. Henry’;
l_start_date_active := ’01-NOV-2007′;
l_end_date_active := ’31-DEC-2007′;
SELECT PROJECT_ROLE_ID,
PROJECT_ROLE_TYPE
INTO l_project_role_id,
l_project_role_type
FROM PA_PROJECT_ROLE_TYPES
WHERE UPPER(MEANING) =UPPER(l_project_role);
SELECT DISTINCT PERSON_ID
INTO l_resource_source_id
FROM PER_ALL_PEOPLE_F
WHERE UPPER(FULL_NAME) =UPPER(l_resource_name);
SELECT PROJECT_PARTY_ID,
OBJECT_ID,
RESOURCE_ID,
RECORD_VERSION_NUMBER
INTO l_project_party_id,
l_object_id,
l_resource_id,
l_record_version_number
FROM PA_PROJECT_PARTIES
WHERE PROJECT_ID= l_project_id_PREM
AND PROJECT_ROLE_ID= l_project_role_id
AND RESOURCE_SOURCE_ID= l_resource_source_id;
l_project_end_date:= pa_project_dates_utils.get_project_finish_date(l_project_id_PREM);
PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
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 => ‘PA_PROJECTS’,
P_PROJECT_ROLE_ID => l_project_role_id,
P_PROJECT_ROLE_TYPE => l_project_role_type,
P_RESOURCE_TYPE_ID => 101, –EMPLOYEE
P_RESOURCE_SOURCE_ID => l_resource_source_id,
P_RESOURCE_NAME => l_resource_name,
P_RESOURCE_ID => l_resource_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_SCHEDULED_FLAG => ‘N’,
P_RECORD_VERSION_NUMBER => l_record_version_number,
P_CALLING_MODULE => FND_API.G_MISS_CHAR,
P_PROJECT_ID => l_project_id_PREM,
P_PROJECT_END_DATE => l_project_end_date,
P_PROJECT_PARTY_ID => l_project_party_id,
P_ASSIGNMENT_ID => null,
P_ASSIGN_RECORD_VERSION_NUMBER =>l_record_version_number+1,
P_MGR_VALIDATION_TYPE => ‘FORM’,
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 => l_msg_count,
x_msg_data => l_msg_data);
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Status:’||l_return_status);
DBMS_OUTPUT.PUT_LINE(‘Message:’||l_msg_data);
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE(‘Try Again!!’);
END;
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
