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;

--> =======================================================================================================