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:

/* Formatted on 3/27/2017 2:43:17 PM (QP5 v5.114.809.3010) */
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:

/* Formatted on 3/27/2017 2:44:48 PM (QP5 v5.114.809.3010) */
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;