Wednesday, October 7, 2015

PA_PROJECT_PUB.CREATE_PROJECT - Creating Project, Task and ChildTask with API

DECLARE
   j                      NUMBER := 0;
   k                      NUMBER := 0;

   --> API standard parameters
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2 (2000);
   l_return_status        VARCHAR2 (1);
   l_api_version_number   NUMBER := 1.0;
   l_commit               VARCHAR2 (1) := 'T';
   l_init_msg_list        VARCHAR2 (1) := 'F';
   l_workflow_status      VARCHAR2 (1) := 'Y';
   l_msg_index_out        NUMBER;
   l_data                 VARCHAR2 (2000);
   l_output               VARCHAR2 (2000); 
   l_msg_dummy            VARCHAR2 (2000);

   --> COMPOSITE 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_task_in              PA_PROJECT_PUB.task_in_tbl_type;
   l_tasks_out            PA_PROJECT_PUB.task_out_tbl_type;
   l_task_in_rec          PA_PROJECT_PUB.task_in_rec_type;
  
   l_person_id            NUMBER := 257;
   l_pm_product_code      VARCHAR2 (20) := 'GMS';
   l_project_id           NUMBER    := 19767;
   l_organization_id      NUMBER    := 121;
   l_project_role_type    VARCHAR2 (20) := 'PROJECT MANAGER';
   p_project_number       VARCHAR2 (20) := TO_CHAR(SYSDATE,'DDMONRRRR:HH24MISS');   

   --> variables needed for the user_id and responsibility_id
   l_user_id              NUMBER;
   l_responsibility_id    NUMBER;
   
   CURSOR C1
   IS
      SELECT Task_id,
             TASK_NUMBER,
             TASK_NAME,
             TOP_TASK_ID,
             PROJECT_ID,
             WBS_LEVEL
        FROM pa_tasks
       WHERE 1=1
         AND project_id = 19767
         and WBS_LEVEL in = 1

         ;
         
   CURSOR C2
   IS
      SELECT Task_id,
             TASK_NUMBER,
             TASK_NAME,
             TOP_TASK_ID,
             PROJECT_ID,
             WBS_LEVEL
        FROM pa_tasks
       WHERE 1=1
         AND project_id = 19767
         and WBS_LEVEL in = 2
         ;

   CURSOR C3
   IS
        select papp.RESOURCE_SOURCE_ID,pprtt.PROJECT_ROLE_TYPE
        from pa_project_parties papp ,pa_project_role_types pprtt
        where 1=1
        AND pprtt.project_role_id = papp.project_role_id
        and papp.project_id = 19767
        And papp.resource_type_id = 101
        and sysdate between papp.start_date_active and nvl(papp.end_date_active,SYSDATE )
        ;
BEGIN
   ---->>=============================================================================================
   -- GET GLOBAL INFO
   SELECT user_id, responsibility_id
     INTO l_user_id, l_responsibility_id
     FROM pa_user_resp_v
    WHERE user_name = 'RCHINTHAPATLA'
    and RESPONSIBILITY_name  = 'Project Billing Super User' ;

   -- SET GLOBAL INFO
   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_project_in.pm_project_reference        := p_project_number;
   l_project_in.project_name                := p_project_number;
   l_project_in.pa_project_number           := p_project_number;
   l_project_in.created_from_project_id     := l_project_id;
   l_project_in.carrying_out_organization_id := l_organization_id;
   l_project_in.public_sector_flag          := 'N';
   l_project_in.project_status_code         := 'UNAPPROVED';
   l_project_in.description             := p_project_number;
   l_project_in.start_date              := SYSDATE;
   l_project_in.completion_date         := SYSDATE + 60;
  
/*
   l_key_members(1).person_id               := l_person_id;
   l_key_members(1).project_role_type       := l_project_role_type;
   l_key_members(1).start_date          := SYSDATE;
*/

--/*
   BEGIN
      FOR i IN C3
      LOOP
         BEGIN
            k := k + 1;
            l_key_members (k).person_id     := i.resource_source_id;
            l_key_members (k).project_role_type := i.project_role_type;
            l_key_members (k).start_date        := SYSDATE;
         END;
      END LOOP;
   END;
--*/

   --> This is just a set of looping structures to create the tasks and sub-tasks
   j := 0;
   FOR p_task IN C1
   LOOP
      j := j + 1;
      l_task_in_rec.pm_task_reference       := p_task.TASK_NUMBER;
      l_task_in_rec.task_name               := p_task.TASK_NUMBER;
      l_task_in_rec.task_start_date         := SYSDATE + 1;
      l_task_in_rec.task_completion_date        := SYSDATE + 60;
      -- l_task_in_rec.actual_start_date        := '';
      -- l_task_in_rec.actual_finish_date       := '';
      -- l_task_in_rec.early_start_date     := '';
      -- l_task_in_rec.early_finish_date        := '';
      -- l_task_in_rec.late_start_date      := '';
      -- l_task_in_rec.late_finish_date     := '';
      L_TASK_IN (J)                 := L_TASK_IN_REC;

      FOR c_task IN C2
      LOOP
         j := j + 1;
         l_task_in_rec.pm_task_reference        := c_task.TASK_NUMBER;
         l_task_in_rec.task_name            := c_task.TASK_NUMBER;
         l_task_in_rec.pm_parent_task_reference := p_task.TASK_NUMBER;
         L_TASK_IN (J) := L_TASK_IN_REC;
      END LOOP; -->Child Task Loop
   END LOOP; -->Parent Task Loop

   -->INITIALIZE PROJECT;
   PA_PROJECT_PUB.INIT_PROJECT;

   --> =======================================================================================================
   --> CREATING PROJECT
   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_status,
      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_task_in,
      p_tasks_out            => l_tasks_out
   );

   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line ( 'Project ID = ' || l_project_out.pa_project_id || ' - Project Number = ' || l_project_out.pa_project_number);
      FOR I IN 1 .. l_task_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('Return Status = ' || l_tasks_out (i).return_status||' - Task Id = ' || l_tasks_out (i).pa_task_id||' - Task Ref = ' || l_tasks_out (i).pm_task_reference);
      END LOOP;
   ELSE
        FOR n IN 1 .. l_msg_count
        LOOP
            fnd_msg_pub.get (n, fnd_api.g_false, l_msg_data, l_msg_dummy);
            l_output := (TO_CHAR (n) || ': ' || l_msg_data);
            DBMS_OUTPUT.PUT_LINE ( 'API Error while CREATE_PROJECT : ' || l_output || ' - ' || SQLERRM);
        END LOOP;
   END IF;

--> =======================================================================================================
-- HANDLE EXCEPTIONS
EXCEPTION
   WHEN OTHERS
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         PA_INTERFACE_UTILS_PUB.GET_MESSAGES (p_msg_data           => l_msg_data,
                                              p_data               => l_data,
                                              p_msg_count          => l_msg_count,
                                              p_msg_index_out      => l_msg_index_out
                                             );
         DBMS_OUTPUT.put_line ('Error Msg 2: ' || l_data);
         DBMS_OUTPUT.put_line ('Error Msg 2: ' || l_msg_data);
      END LOOP;     
      DBMS_OUTPUT.put_line ( 'An error occured while creating project, SQLCODE ->' || SQLERRM);
END;
--> =======================================================================================================

2 comments:

Post a Comment

Best Blogger TipsGet Flower Effect