1 Reply Latest reply on May 3, 2011 9:27 PM by shahcsanjay

    Error while creating new projects using api

    shahcsanjay
      Hello,
      I am having error while creating projects using standard api, PA_PROJECT_PUB.CREATE_PROJECTS. The error I am having is as follow.

      Source template ID is invalid.

      ===
      My code is as follow:

      SET SERVEROUTPUT ON SIZE 1000000
      SET VERIFY OFF
      define no=&amg_number
      DECLARE
      -- Variables used to initialize the session
      l_user_id NUMBER;
      l_responsibility_id NUMBER;

      cursor get_key_members is
      select person_id, project_role_type, rownum
      from pa_project_players
      where project_id = 1;
      -- Counter variables
      a NUMBER := 0;
      m NUMBER := 0;

      -- Variables needed for API standard parameters

      l_commit VARCHAR2(1) := 'F';
      l_init_msg_list VARCHAR2(1) := 'T';
      l_api_version_number NUMBER :=1.0;
      l_return_status VARCHAR2(1);
      l_msg_count NUMBER;
      l_msg_data VARCHAR2(2000);


      -- Variables used specifically in error message retrieval

      l_encoded VARCHAR2(1) := 'F';
      l_data VARCHAR2(2000);
      l_msg_index NUMBER;
      l_msg_index_out NUMBER;


      -- Variables needed for Oracle Project specific parameters

      -- Input variables

      l_pm_product_code VARCHAR2(30);
      l_project_in pa_project_pub.project_in_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 pa_project_pub.task_in_tbl_type;

      -- Record variables for loading table variables above

      l_key_member_rec pa_project_pub.project_role_rec_type;
      l_class_category_rec pa_project_pub.class_category_rec_type;
      l_task_rec pa_project_pub.task_in_rec_type;

      -- Output variables

      l_workflow_started VARCHAR2(100);
      l_project_out pa_project_pub.project_out_rec_type;
      l_tasks_out pa_project_pub.task_out_tbl_type;

      -- Exception to call messag handlers if API returns an error.

      API_ERROR EXCEPTION;

      BEGIN

      -- Initialize the session with my user id and Projects, Vision Serves (USA0
      -- responsibility:

      select user_id into l_user_id
      from fnd_user
      where user_name = 'SSHAH';

      select responsibility_id into l_responsibility_id
      from fnd_responsibility_tl
      where responsibility_name = 'Projects Implementation Superuser';

      pa_interface_utils_pub.set_global_info(
      p_api_version_number => l_api_version_number,
      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);

      if l_return_status != 'S' then
      raise API_ERROR;
      end if;
      -- Provide values for input variables

      -- L_PM_PRODUCT_CODE: These are stored in pa_lookups and can be defined
      -- by the user. In this case we select a pre-defined one.

      select lookup_code into l_pm_product_code
      from pa_lookups
      where lookup_type = 'PM_PRODUCT_CODE'
      and meaning = 'Conversion';


      -- L_PROJECT_IN: We have to provide values for all required elements
      -- of this record (see p 5-13, 5-14 for the definition of the record).
      -- Customers will normally select this information from some external
      -- source
      l_project_in.pm_project_reference := 'AGL-AMG Project &no';
      l_project_in.project_name := 'AGL-AMG Project &no';
      l_project_in.created_from_project_id := 1;
      l_project_in.carrying_out_organization_id := 2864; /*Cons. West*/
      l_project_in.project_status_code := 'UNAPPROVED';
      l_project_in.start_date := '01-JAN-11';
      l_project_in.completion_date := '31-DEC-11';
      l_project_in.description := 'Trying Hard';
      l_project_in.project_relationship_code := 'Primary';

      -- L_KEY_MEMBERS: To load the key member table we load individual
      -- key member records and assign them to the key member table. In
      -- the example below I am selecting all of the key member setup
      -- from an existing project with 4 key members ('EE-Proj-01'):

      for km in get_key_members loop
      -- Get the next record and load into key members record:
      l_key_member_rec.person_id := km.person_id;
      l_key_member_rec.project_role_type := km.project_role_type;
      -- Assign this record to the table (array)
      l_key_members(km.rownum) := l_key_member_rec;
      end loop;

      -- L_CLASS_CATEGORIES: commented out below should fix the error we get
      -- because the template does not have an assigment for the mandatory class
      -- 'BAS Test'

      l_class_category_rec.class_category := 'Product';
      l_class_category_rec.class_code := 'Non-classified';
      -- Assign the record to the table (array)
      l_class_categories(1) := l_class_category_rec;

      -- L_TASKS_IN: We will load in a single task and a subtask providing only
      -- the basic fields (see pp. 5-16,5-17,5-18 for the definition of
      -- the task record)

      l_task_rec.pm_task_reference := '1';
      l_task_rec.pa_task_number := '1';
      l_task_rec.task_name := 'Construction';
      l_task_rec.pm_parent_task_reference := '' ;
      l_task_rec.task_description := 'Plant function';
      -- Assign the top task to the table.
      l_taskS_in(1) := l_task_rec;

      -- Assign values for the sub task
      l_task_rec.pm_task_reference := '1.1';
      l_task_rec.pa_task_number := '1.1';
      l_task_rec.task_name := 'Brick laying';
      l_task_rec.pm_parent_task_reference := '1' ;
      l_task_rec.task_description := 'Plant building';
      -- Assign the subtask to the task table.
      l_tasks_in(2) := l_task_rec;

      -- All inputs are assigned, so call the API:
      pa_project_pub.create_project
      (p_api_version_number => 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, if it is not success, then raise message handling
      -- exception.
      IF l_return_status != 'S' THEN
      dbms_output.put_line('Msg_count: '||to_char(l_msg_count));
      dbms_output.put_line('Error: ret status: '||l_return_status);
      RAISE API_ERROR;
      END IF;

      -- perform manual commit since p_commit was set to False.
      COMMIT;


      --HANDLE EXCEPTIONS

      EXCEPTION
      WHEN API_ERROR THEN
      FOR i IN 1..l_msg_count LOOP
      pa_interface_utils_pub.get_messages(
      p_msg_count => l_msg_count,
      p_encoded => l_encoded,
      p_msg_index => i,
      p_msg_data => l_msg_data,
      p_data => l_data,
      p_msg_index_out => l_msg_index_out);
      dbms_output.put_line('ERROR: '||to_char(l_msg_index_out)||': '||l_data);
      END LOOP;
      rollback;

      WHEN OTHERS THEN
      dbms_output.put_line('Error: '||sqlerrm);
      FOR i IN 1..l_msg_count LOOP
      pa_interface_utils_pub.get_messages(
      p_msg_count => l_msg_count,
      p_encoded => l_encoded,
      p_msg_index => i,
      p_msg_data => l_msg_data,
      p_data => l_data,
      p_msg_index_out => l_msg_index_out);
      dbms_output.put_line('ERROR: '||to_char(l_msg_index_out)||': '||l_data);
      END LOOP;
      rollback;
      END;
      /
      ===
      Msg_count: 1
      Error: ret status: E
      ERROR: 1: Project: 'AGL-AMG Project 1123'
      Source template ID is invalid.
      PL/SQL procedure successfully completed.