1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

API to Create Project and Task in Oracle Apps

Discussion in 'Oracle Apps Technical' started by nilesh.dudhane, Nov 25, 2014.

  1. nilesh.dudhane

    nilesh.dudhane Active Member

    Messages:
    42
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    India
    The following is code for an API to Create Project and Task in Oracle Apps

    Code (SQL):

    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);
    -- 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_cust_rec             pa_project_pub.customer_in_rec_type;
       l_cust_tbl             pa_project_pub.customer_tbl_type;
       l_person_id            NUMBER;
       l_project_role_type    VARCHAR2 (20);
       l_resp_id              NUMBER                   := fnd_global.resp_id;
       l_user_id              NUMBER                   := fnd_global.user_id;
       l_appl_id              NUMBER                   := fnd_global.resp_appl_id;
       v_ind                  NUMBER;
       v_custid               NUMBER;
       v_personid             NUMBER;
       sstask_cnt             NUMBER;
       stask_cnt              NUMBER;
       ptask_cnt              NUMBER;
       v_orgid                NUMBER;
       api_error              EXCEPTION;
       v_projectid            NUMBER;

    ---- Cursoe To get Project Header Details ----------------------
       CURSOR c1
       IS
          SELECT DISTINCT operating_unit, project_name, project_description, start_date,
                 completion_date, actual_start_date, actual_finish_date,
                 project_template, customer_name, project_manager
            FROM xx_project_import
           WHERE error_flag = 'N';

    ---- Curosr to get Project Task Details ------------------------
       CURSOR c2 (v_project VARCHAR2)
       IS
          SELECT task_name, sub_task_name, sub_sub_task_name, task_start_date,
                 task_completion_date, task_actual_start_date,
                 task_actual_finish_date
            FROM xx_project_import
           WHERE error_flag = 'N' AND project_name = v_project
           ORDER BY task_name;
    BEGIN
       fnd_file.put_line (fnd_file.output, '====== LOG FILE STARTED ======');
       -- intiialize applications information
       fnd_global.apps_initialize (1110, 50657, 712);
    -- PRODUCT RELATED DATA
       l_pm_product_code := 'PJM';                         -- can be user defined

       FOR c1_rec IN c1
       LOOP
          -------------- Query To Get Organization Id ---------------------
          BEGIN
             SELECT organization_id
               INTO v_orgid
               FROM hr_operating_units
              WHERE UPPER (NAME) = UPPER (TRIM (c1_rec.operating_unit));
          EXCEPTION
             WHEN OTHERS
             THEN
                fnd_file.put_line (fnd_file.output, 'Invalid Operating Unit');
                dbms_output.put_line ('Invalid Operating Unit');
          END;

          mo_global.set_policy_context ('S', v_orgid);

          -------------- Query To Get Project Template ID -----------------
          BEGIN
             SELECT project_id
               INTO v_projectid
               FROM pa_projects_all
              WHERE org_id = v_orgid
                AND UPPER (name) = UPPER (TRIM (c1_rec.project_template))
                AND template_flag='Y';
          EXCEPTION
             WHEN OTHERS
             THEN
                fnd_file.put_line (fnd_file.output, ' Invalid Project Template');
                dbms_output.put_line (' Invalid Project Template');
          END;

          -------------- Query To Get Customer Id -------------------------
          BEGIN
             SELECT customer_id
               INTO v_custid
               FROM ar_customers
              WHERE UPPER (customer_name) = UPPER (TRIM (c1_rec.customer_name));
          EXCEPTION
             WHEN OTHERS
             THEN
                fnd_file.put_line (fnd_file.output, 'Invalid customer name');
          END;

          -------------- Query to Get Person Id --------------------------
          BEGIN
             SELECT person_id
               INTO v_personid
               FROM per_all_people_f
              WHERE UPPER(title||' '||first_name||' '||last_name)=UPPER(c1_rec.project_manager);
          EXCEPTION
             WHEN OTHERS
             THEN
                fnd_file.put_line (fnd_file.output, 'Employee Not Defined');
                dbms_output.put_line ('Employee Not Defined');
          END;

          -- PRODUCT DATA (PROJECT_IN_REC_TYPE)
          l_project_in.created_from_project_id := v_projectid;-- Project id from template
          l_project_in.project_name := c1_rec.project_name;--Project Name Come From Excel;
          l_project_in.pm_project_reference := c1_rec.project_name;--'Project Reference';
          l_project_in.description := c1_rec.project_description;--Project Come From Excel';
          l_project_in.project_status_code := 'SUBMITTED';
          l_project_in.carrying_out_organization_id := v_orgid;-- can default from template
          l_project_in.start_date := c1_rec.start_date;-- can override default from template
          l_project_in.completion_date := c1_rec.completion_date;-- can override default from template
          l_project_in.actual_start_date := c1_rec.actual_start_date;
          l_project_in.actual_finish_date := c1_rec.actual_finish_date;
          l_project_in.process_mode := 'ONLINE';
          l_project_in.customer_id := v_custid;
          --KEY MEMBERS DATA (PROJECT_ROLE_TBL_TYPE)
          -- can be defaulted from the Template, but not from a Project
          m := 1;
          l_person_id := v_personid;                      -- need to get from Apps
          l_project_role_type := 'PROJECT MANAGER';
          l_key_members (m).person_id := v_personid;
          l_key_members (m).project_role_type := 'PROJECT MANAGER';
          -- CUSTOMERS
          v_ind := 0;
          l_cust_rec.customer_id := v_custid;
          l_cust_rec.project_relationship_code := 'PRIMARY';
          l_cust_rec.bill_to_customer_id := NULL;        --cus.x_bill_customer_id;
          l_cust_rec.ship_to_customer_id := NULL;
          l_cust_rec.contact_id := NULL; --  pa_interface_utils_pub.g_pa_miss_num;
          l_cust_rec.project_contact_type_code := NULL;--  pa_interface_utils_pub.g_pa_miss_char;
          l_cust_rec.customer_bill_split := NULL;--nvl(cus.customer_bill_split, pa_interface_utils_pub.g_pa_miss_num);
          l_cust_rec.allow_inv_user_rate_type_flag := NULL;
          l_cust_rec.inv_rate_date := NULL;
          l_cust_rec.inv_rate_type := NULL;
          l_cust_rec.inv_currency_code := NULL;
          l_cust_rec.inv_exchange_rate := NULL;
          l_cust_rec.bill_another_project_flag := 'N';
          l_cust_rec.receiver_task_id := NULL;
          v_ind := v_ind + 1;
          l_cust_tbl (v_ind) := l_cust_rec;
          a:=0;
          --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
          FOR c1_rec1 IN c2 (c1_rec.project_name)
          LOOP
          a := a+1;
             ----------- Query to Count Number of Parent Task ----------------
             BEGIN
                SELECT COUNT (*)
                  INTO ptask_cnt
                  FROM xx_project_import
                 WHERE task_name = c1_rec1.task_name
                 AND PROJECT_NAME=c1_rec.PROJECT_NAME;
             EXCEPTION
                WHEN OTHERS
                THEN
                   fnd_file.put_line (fnd_file.output, SQLERRM);
             END;

             --Set the number of tasks for every level (there are 4 levels)
             number_of_tasks1 := ptask_cnt;        

             FOR level1 IN 1 .. number_of_tasks1
             LOOP
                l_tasks_in_rec.pm_task_reference := c1_rec1.task_name;
                l_tasks_in_rec.task_name := c1_rec1.task_name ;
                l_tasks_in_rec.pm_parent_task_reference := '';
                l_tasks_in_rec.task_start_date := c1_rec1.task_start_date;
                l_tasks_in_rec.task_completion_date := c1_rec1.task_completion_date;
                l_tasks_in_rec.actual_start_date := c1_rec1.task_actual_start_date;
                l_tasks_in_rec.actual_finish_date := c1_rec1.task_actual_finish_date;
                l_tasks_in_rec.receive_project_invoice_flag := 'Y';
                l_tasks_in(a) := l_tasks_in_rec;
                parent_level1 := c1_rec1.task_name;
             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,
                                         p_customers_in          => l_cust_tbl
                                        );

          -- Check for errors
          IF l_return_status != 'S'
          THEN
             RAISE api_error;
          END IF;

          IF l_return_status ='S' THEN
          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
                               );
                UPDATE xx_project_import
                SET ERROR_FLAG='S',ERROR_DESCRIPTION=l_project_out.pa_project_number||'Created Successfully'
                WHERE PROJECT_NAME=c1_rec.PROJECT_NAME;
          END IF;
          COMMIT;
       END LOOP;
    --HANDLE EXCEPTIONS
    -- Get the error message that were returned if it did not complete sucessfully
    EXCEPTION
       WHEN api_error
       THEN
          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          => l_msg_count,
                                               p_msg_index_out      => l_msg_index_out
                                              );
                DBMS_OUTPUT.put_line ('error message: ' || l_data);
             END LOOP;

             ROLLBACK;
          END IF;
       WHEN OTHERS
       THEN
          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          => l_msg_count,
                                               p_msg_index_out      => l_msg_index_out
                                              );
                DBMS_OUTPUT.put_line ('error message: ' || l_data);
             END LOOP;

             ROLLBACK;
          END IF;
    END;
    /
     
     
  2. jamesbondo2

    jamesbondo2 Guest

    Thanks a lot for you support, please can you have a look into my code, the project is created when i remove the tasks loop but no tasks are copied, but when i keep it the project is not created.


    So please advice.


    Best Regards,
    Ahmed EMbaby