Single Insert for Oracle Projects

Create Project
PA_PROJECT_PUB.CREATE_PROJECT
$PA_TOP/patch/115/sql/PAPMPRPB.pls
API:
pa_project_pub.create_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);



Example:

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_user_id            NUMBER;
  l_responsibiity_id        NUMBER;

-- Predefined Composit data types  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
-- GET GLOBAL VALUES
  select user_id, responsibility_id
    into l_user_id, l_responsibility_id
    from pa_user_resp_v
   where user_name = 'sraju';        -- need to get from Apps

-- SET GLOBAL VALUES  pa_interface_utils_pub.set_global_info(
    p_api_version_number    => 1.0,
    p_responsibility_id    => l_responsibility_id,
    p_user_id        => l_user_id,
    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 := 'XXCONVERT';         -- can be user defined

-- PRODUCT DATA (PROJECT_IN_REC_TYPE)
  l_project_in.created_from_project_id := '1001';  -- Project id from template
  l_project_in.project_name :='Test Project';
  l_project_in.pm_project_reference := 'Test Project';
  l_project_in.description := 'This is a Test Project';
  l_project_in.project_status_code := '';
--  l_project_in.carrying_out_organization_id := 2;    -- can default from template
  l_project_in.start_date := '03-JAN-2009';-- can override default from template
  l_project_in.completion_date := '';       -- can override default 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 := 1005;

--KEY MEMBERS DATA (PROJECT_ROLE_TBL_TYPE)
-- can be defaulted from the Template, but not from a Project  m := 1;
  l_person_id := 34;            -- need to get from Apps
  l_project_role_type := 'PROJECT MANAGER';
  l_key_members(m).person_id := 53;
  l_key_members(m).project_role_type := 'PROJECT MANAGER';


--CLASS CATEGORIES DATA (CLASS_CATEGORY_TBL_TYPE)
--  l_class_categories(1).class_category := 'Product'; --value from template
--  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;

-- This is just a set of looping structures to create the tasks and sub-tasks
-- If it is difficult to understand, then feel free to create them individually.
  a := 0;
  for level1 in 1..number_of_tasks1 loop
    a := a + 1;
    l_tasks_in_rec.pm_task_reference := level1 || '.' || '0';
    l_tasks_in_rec.task_name := 'Top Level ' || level1 || '.' || '0';
    l_tasks_in_rec.pm_parent_task_reference := '';
    l_tasks_in_rec.task_start_date := '';
    l_tasks_in_rec.task_completion_date := '';
    l_tasks_in_rec.actual_start_date := '';
    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 := level1 || '.' || '0';

    for level2 in 1..number_of_tasks2 loop
      a := a + 1;
      l_tasks_in_rec.pm_task_reference := level1 || '.' || level2;
      l_tasks_in_rec.task_name := '2 Level ' || level1 || '.' || level2;
      l_tasks_in_rec.pm_parent_task_reference := parent_level1;
      l_tasks_in(a) := l_tasks_in_rec;
      parent_level2 := level1 || '.' || level2;

      for level3 in 1..number_of_tasks3 loop
        a := a + 1;
    l_tasks_in_rec.pm_task_reference := level1 || '.' || level2 || '.' || level3;
    l_tasks_in_rec.task_name := '3 Level ' || level1 || '.' || level2 || '.' || level3;
    l_tasks_in_rec.pm_parent_task_reference := parent_level2;
    l_tasks_in(a) := l_tasks_in_rec;
    parent_level3 := level1 || '.' || level2 || '.' || level3;

    for level4 in 1..number_of_tasks4 loop
          a := a + 1;
      l_tasks_in_rec.pm_task_reference := level1 || '.' || level2 || '.' || level3 || '.' || level4;
      l_tasks_in_rec.task_name := 'Fourth Level ' || level1 || '.' || level2 || '.' || level3 || '.' || level4;
      l_tasks_in_rec.pm_parent_task_reference := parent_level3;
      l_tasks_in(a) := l_tasks_in_rec;
    end loop;
      end loop;
    end loop;
  end loop;

--INIT_CREATE_PROJECT
  pa_project_pub.init_project;

--CREATE_PROJECT  pa_project_pub.create_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 for errors  if l_return_status != 'S'
  then
    raise API_ERROR;
  end if;

  dbms_output.put_line('New Project Id: ' || l_project_out.pa_project_id);
  dbms_output.put_line('New Project Number: ' || l_project_out.pa_project_number);

  Commit;