API to update and assign Project Roles in an Oracle Project
November 28, 2011 3 Comments
Oracle has provided a seeded package called PA_PROJECT_PARTIES_PUB to create, update or delete a project party (or Key member) in an oracle project. From front end, the navigation is Project Billing Super User (or related responsibility) > Projects > Find Projects > Open > Options > Key Members. The records in the form are displayed through a view (PA_PROJECT_PLAYERS) and the base table is PA_PROJECT_PARTIES.
PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY:
DECLARE l_project_id 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 := '7033'; l_project_role := 'Project Manager'; l_resource_name := 'Koch, Dibyajyoti'; l_start_date_active := '24-NOV-2011'; l_end_date_active := '24-NOV-2012'; 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 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); 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, 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;
PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY:
DECLARE l_project_id 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 := '7033'; l_project_role := 'Project Accountant'; l_resource_name := 'Koch, Dibyajyoti'; l_start_date_active := '24-NOV-2011'; l_end_date_active := '24-NOV-2012'; 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); l_project_end_date:= pa_project_dates_utils.get_project_finish_date(l_project_id); PA_PROJECT_PARTIES_PUB.CREATE_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_project_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_START_DATE_ACTIVE => l_start_date_active, P_SCHEDULED_FLAG => 'N', P_CALLING_MODULE => NULL, P_PROJECT_ID => l_project_id, P_PROJECT_END_DATE => l_project_end_date, P_MGR_VALIDATION_TYPE => 'FORM', P_END_DATE_ACTIVE => l_end_date_active, X_PROJECT_PARTY_ID => l_project_party_id, X_RESOURCE_ID => l_resource_id, 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;