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;