API to update and assign Project Roles in an Oracle Project


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;

3 Responses to API to update and assign Project Roles in an Oracle Project

  1. Will says:

    Fantastic!
    That is awesome! We maintain between 140 projects, and often get bulk updates required for roles. This has been a great starting point to put together a script to update.

  2. Roh says:

    According to oracle support, this is not listed as a public API and is not oracle supported. How have you dealt with any issues if encountered while using this package. REF: Metalink Note #1082143.1 – Add Key Member with Long Name to Project Errors : ORA-06502 PA_PROJECT_PARTIES_PUB

  3. Avijit Karmakar says:

    Very helpful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: