Monday, June 25, 2018

PA - Updating the Project Status with API - PA_PROJECT_PUB.UPDATE_PROJECT


DECLARE
  -- Variables needed to create task hierachy
  level1            NUMBER;
  level2            NUMBER;
  level3            NUMBER;
  a                NUMBER;
  m                NUMBER;
  parent_level1            VARCHAR2(30);
  parent_level2            VARCHAR2(30);
  parent_level3            VARCHAR2(30);
  number_of_tasks1        NUMBER;        -- number of tasks/levels
  number_of_tasks2        NUMBER;
  number_of_tasks3        NUMBER;
  number_of_tasks4        NUMBER;

  -- Variables needed for API standard parameters
  l_api_version_number        NUMBER := 1.0;
  l_commit            VARCHAR2(1) := 'F';
  l_return_status        VARCHAR2(1);
  l_init_msg_list        VARCHAR2(1) := 'F';
  l_msg_count            NUMBER;
  l_msg_index_out        NUMBER;
  l_msg_data            VARCHAR2(2000);
  l_data            VARCHAR2(2000);
  l_workflow_started        VARCHAR2(1) := 'Y';
  l_pm_product_code        VARCHAR2(10);
  l_project_in                  PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
  l_project_out                 PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
  l_key_members            PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
  l_class_categories        PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
  l_tasks_in_rec        PA_PROJECT_PUB.TASK_IN_REC_TYPE;
  l_tasks_in            PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
  l_tasks_out_rec        PA_PROJECT_PUB.TASK_OUT_REC_TYPE;
  l_tasks_out            PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;

  l_person_id            NUMBER;
  l_project_role_type        VARCHAR2(20);

  API_ERROR            EXCEPTION;

BEGIN
-- SET GLOBAL VALUES
  pa_interface_utils_pub.set_global_info(
    p_api_version_number    => 1.0,
    p_responsibility_id    => 50604,
    p_user_id                => 1009110,
    p_msg_count        => l_msg_count,
    p_msg_data        => l_msg_data,
    p_return_status        => l_return_status);

-- PRODUCT RELATED DATA
  l_pm_product_code := 'CNV';

-- PRODUCT DATA (PROJECT_IN_REC_TYPE)
  l_project_in.pa_project_id := 562653;     -- New Project Id
  l_project_in.project_name :='XXCD_WDDHD2323 US';
  l_project_in.pm_project_reference := 'XXCD_WDDHD2323 US';
  l_project_in.description := 'Updating project with new Information';
  l_project_in.project_status_code := 'APPROVED';
  l_project_in.carrying_out_organization_id := 204;
  l_project_in.start_date := '03-JAN-2018';    -- Default Value from template
  l_project_in.completion_date := '';        -- Default Value from template
  l_project_in.actual_start_date := '';
  l_project_in.actual_finish_date := '';
  l_project_in.early_start_date := '';
  l_project_in.early_finish_date := '';
  l_project_in.late_start_date := '';
  l_project_in.late_finish_date := '';
  l_project_in.customer_id := 50056;        -- Value from Apps
  l_project_in.project_relationship_code := 'Primary';    -- Value from Apps

-- KEY MEMBERS DATA (PROJECT_ROLE_TBL_TYPE) 
  m := 1;
  l_person_id := 34;            -- Value from template
  l_project_role_type := 'PROJECT MANAGER';
  l_key_members(m).person_id := 53;
  l_key_members(m).project_role_type := 'PROJECT MANAGER';

dbms_output.put_line('m := 2');
  m := 2;
  l_key_members(m).person_id := 36;    -- Value from template
  l_key_members(m).project_role_type := 'COLLECTER';

dbms_output.put_line('m := 3');
  m := 3;
  l_key_members(m).person_id := 357;    -- value from template
  l_key_members(m).project_role_type := 'CONSULTANT';

 
--CLASS CATEGORIES DATA (CLASS_CATEGORY_TBL_TYPE)
 l_class_categories(1).class_category := 'Product';
 l_class_categories(1).class_code := 'Product 1';

--TASKS DATA
  --Set the number of tasks for every level (there are 4 levels)
  number_of_tasks1 := 1;
  number_of_tasks2 := 2;
  number_of_tasks3 := 0;
  number_of_tasks4 := 0;

--Update the tasks with starting and one completion date
  a := 1;
    l_tasks_in_rec.pm_task_reference := '1.0';
    l_tasks_in_rec.task_name := 'Top Level 1';
    l_tasks_in_rec.pm_parent_task_reference := '';
    l_tasks_in_rec.task_start_date := '03-JAN-2020';
    l_tasks_in_rec.task_completion_date := '';
    l_tasks_in_rec.actual_start_date := '15-JAN-2020';
    l_tasks_in_rec.actual_finish_date := '';
    l_tasks_in_rec.early_start_date := '';
    l_tasks_in_rec.early_finish_date := '';
    l_tasks_in_rec.late_start_date := '';
    l_tasks_in_rec.late_finish_date := '';
    l_tasks_in(a) := l_tasks_in_rec;
    parent_level1 := '1.0';

      a := a + 1;
      l_tasks_in_rec.pm_task_reference := '1.1';
      l_tasks_in_rec.task_name := '2 Level 1.1';
      l_tasks_in_rec.pm_parent_task_reference := parent_level1;
      l_tasks_in(a) := l_tasks_in_rec;

      a := a + 1;
      l_tasks_in_rec.pm_task_reference := '1.2';
      l_tasks_in_rec.task_name := '2 Level 1.2';
      l_tasks_in_rec.pm_parent_task_reference := parent_level1;
      l_tasks_in_rec.task_completion_date := '15-SEP-2020';
      l_tasks_in(a) := l_tasks_in_rec;

--INIT_UPDATE_PROJECT
  PA_PROJECT_PUB.INIT_PROJECT;

--UPDATE_PROJECT
  PA_PROJECT_PUB.UPDATE_PROJECT
  (
    l_api_version_number,
    p_commit         => l_commit,
    p_init_msg_list     => l_init_msg_list,
    p_msg_count         => l_msg_count,
    p_msg_data         => l_msg_data,
    p_return_status     => l_return_status,
    p_workflow_started     => l_workflow_started,
    p_pm_product_code     => l_pm_product_code,
    p_project_in         => l_project_in,
    p_project_out        => l_project_out,
    p_key_members         => l_key_members,
    p_class_categories     => l_class_categories,
    p_tasks_in         => l_tasks_in,
    p_tasks_out         => l_tasks_out
    );

-- Check the return status of the update_project call
  if l_return_status != 'S'
  then
    raise API_ERROR;
  end if;

  dbms_output.put_line('Project Updated Successfully');

  Commit;

-- Check the returned records in the task out structure
  for i in 1..l_tasks_out.count loop
    dbms_output.put_line('Task Id  = ' || l_tasks_out(i).pa_task_id);
    dbms_output.put_line('Task Ref = ' || l_tasks_out(i).pm_task_reference);
    dbms_output.put_line('Return Status = ' || l_tasks_out(i).return_status);
  end loop;


--HANDLE EXCEPTIONS
  EXCEPTION
  When API_ERROR then
    dbms_output.put_line('An API_ERROR occurred');
    if l_msg_count >= 1
    then
      for i in 1..l_msg_count loop
        pa_interface_utils_pub.get_messages(
        p_msg_data     => l_msg_data,
        p_encoded    => 'F',
        p_data         => l_data,
        p_msg_count     => l_msg_count,
                p_msg_index     => i,
        p_msg_index_out => l_msg_index_out);
        dbms_output.put_line('error message: ' || l_data);
      end loop;
      rollback;
    end if;
  When OTHERS then
    dbms_output.put_line('An error occurred, sqlcode = ' || sqlcode);
    if l_msg_count >= 1
      then
      for i in 1..l_msg_count loop
        pa_interface_utils_pub.get_messages(
        p_msg_count     => l_msg_count,
        p_encoded    => 'F',
        p_msg_data     => l_msg_data,
        p_data         => l_data,
                p_msg_index     => i,
        p_msg_index_out => l_msg_index_out);
        dbms_output.put_line('error message: ' || l_data);
      end loop;
      rollback;
    end if;
END;
/



Note: When updating the project status, Project Manager should be active for the total project period

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect