Friday, April 27, 2018

API Script to Create Oracle Project with Task and Key Members - PA_PROJECT_PUB.CREATE_PROJECT


DECLARE

   x_project_template_id         NUMBER := 4321;
  
   --Declare all required variables, some are defaulted
   v_project_template_name      pa_projects_all.NAME%TYPE;
   v_proj_template_number       pa_projects_all.segment1%TYPE;
   v_project_template_type      VARCHAR2 (100);
   v_carrying_organization_id   NUMBER;
   v_project_status_code        VARCHAR2 (100);
   v_project_start_date         DATE;
   v_project_completion_date    DATE;
   v_project_closed_date        DATE;
   v_enabled_flag               VARCHAR2 (20);
   v_template_flag              pa_projects_all.template_flag%TYPE;
   v_temp_start_date            DATE;
   v_temp_end_date              DATE;
   v_tax_item_id                VARCHAR2 (100);
   v_project_temp_org_id        NUMBER;
   v_proj_temp_currency         pa_projects_all.project_currency_code%TYPE;

   -- Variables needed to create task hierachy
   a                            NUMBER;
   m                            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) := 'T';
   l_msg_count                  NUMBER;
   l_msg_index_out              NUMBER;
   l_msg_data                   VARCHAR2 (32000);
   l_data                       VARCHAR2 (32000);
   l_workflow_started           VARCHAR2 (1) := 'Y';
   l_pm_product_code            VARCHAR2 (10) := 'OKE';
  
   -- 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_tasks_in_rec               pa_project_pub.task_in_rec_type;
   l_tasks_in                   pa_project_pub.task_in_tbl_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;

   v_ind                        NUMBER := 0;
   api_error                    EXCEPTION;

  
   ---BELOW CURSOR DERIVES THE TASK DETAILS
   CURSOR get_task_data (p_project_id NUMBER)
   IS
        SELECT project_id,
               task_id,
               task_number,
               (SELECT task_number FROM pa_tasks WHERE task_id = a.parent_task_id) parent_task_number,
               task_name,
               start_date,
               completion_date,
               actual_start_date,
               actual_finish_date,
               wbs_level,
               parent_task_id,
               carrying_out_organization_id,
               receive_project_invoice_flag,
               billable_flag
          FROM pa_tasks a
         WHERE project_id = p_project_id
      ORDER BY task_number;

     
   --BELOW CURSOR DERIVES DEFAULT VALUES FOR KEY MEMBERS. YOU CAN WRITE CURSOR TO DERIVE KEY MEMBERS DETAILS AS PER BUSINESS NEED.
   --BELOW IS ONLY FOR SAMPLE
   CURSOR get_default_key_members
   IS
      SELECT flv.tag,
             flv.description meaning,
             prt.project_role_type,
             papf.person_id
        FROM fnd_lookup_values_vl flv,
             pa_project_role_types prt,
             per_all_people_f papf
       WHERE     lookup_type = 'XXPK_LOOKUP'
             AND flv.meaning LIKE 'XXPK_MEANING'
             AND flv.description = prt.meaning
             AND papf.employee_number = flv.tag
             AND SYSDATE BETWEEN NVL (effective_start_Date, SYSDATE - 1) AND NVL (effective_end_Date, SYSDATE + 1);

BEGIN
   -- DERIVE DETAILS FROM PROJECT TEMPLATE
   BEGIN
      SELECT ppa.NAME,
             ppa.segment1 proj_template_number,
             ppa.project_type,
             ppa.carrying_out_organization_id,
             ppa.project_status_code,
             TO_DATE (ppa.start_date, 'DD-MON-RRRR'),
             ppa.completion_date,
             ppa.closed_date,
             ppa.enabled_flag,
             ppa.template_flag,
             ppa.template_start_date_active,
             ppa.template_end_date_active,
             ppa.org_id,
             ppa.project_currency_code,
             ppa.attribute3
        INTO v_project_template_name,
             v_proj_template_number,
             v_project_template_type,
             v_carrying_organization_id,
             v_project_status_code,
             v_project_start_date,
             v_project_completion_date,
             v_project_closed_date,
             v_enabled_flag,
             v_template_flag,
             v_temp_start_date,
             v_temp_end_date,
             v_project_temp_org_id,
             v_proj_temp_currency,
             v_tax_item_id
        FROM pa_projects_all ppa
       WHERE     1 = 1
             AND ppa.project_id = x_project_template_id
             AND ppa.template_flag = 'Y';
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR:- CANNOT GET PROJECT TEMPLATE DETAILS FOR PROJECT ID-'|| x_project_template_id);
         RETURN;
   END;

   -- PRODUCT DATA (PROJECT_IN_REC_TYPE)
   --Below values can be derived as required by custom logic
   l_project_in.created_from_project_id         := x_project_template_id;
   l_project_in.pa_project_number                 := 'XXAA_APPS88_001';
   l_project_in.project_name                     := 'XXAA_APPS88_001';
   l_project_in.pm_project_reference             := 'XXAA_APPS88_001';
   l_project_in.description                     := 'XXAA_APPS88_001';
   l_project_in.long_name                         := 'XXAA_APPS88_001';
   l_project_in.project_status_code             := 'UNAPPROVED';
   l_project_in.carrying_out_organization_id     := v_carrying_organization_id;
   l_project_in.start_date                         := '01-Jan-2018';        /*project_start_date*/
   l_project_in.completion_date                 := '31-Dec-2018';       /*project_end_date*/
   l_project_in.process_mode                     := 'ONLINE';
   l_project_in.enable_top_task_customer_flag     := 'N';
   l_project_in.customer_id                     := 56893;            /*project_customer*/
   l_project_in.attribute_category                 := v_project_template_type;
   l_project_in.attribute3                         := v_tax_item_id; -- Custom values if DFF is setup at project header
   l_project_in.sys_program_flag                 := 'Y';   -- needed for suproject association
   l_project_in.date_eff_funds_flag             := 'Y';

   m := 0;
   FOR role_data IN get_default_key_members
   LOOP
      m := m + 1;
      l_key_members (m).person_id              := role_data.person_id;
      l_key_members (m).project_role_type     := role_data.project_role_type;
      l_key_members (m).project_role_meaning := role_data.meaning;
      l_key_members (m).start_date              := x_proj_start_date;
   END LOOP;

   -- CUSTOMERS
   l_cust_rec.customer_id                     := x_bill_to_cust;
   l_cust_rec.project_relationship_code     := 'PRIMARY';
   l_cust_rec.bill_to_customer_id             := x_bill_to_cust;
   l_cust_rec.ship_to_customer_id             := x_bill_to_cust;
   l_cust_rec.bill_to_address_id             := x_bill_to_cust_address_id; -- BILL_TO Cust_ACCT_SITE_ID
   l_cust_rec.ship_to_address_id             := x_ship_to_cust_address_id; -- SHIP_TO Cust_ACCT_SITE_ID
   l_cust_rec.contact_id                     := NULL;
   l_cust_rec.project_contact_type_code     := NULL;
   l_cust_rec.customer_bill_split             := NULL;
   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.enable_top_task_cust_flag     := 'N';
   l_cust_rec.receiver_task_id                 := NULL;
   v_ind                                     := v_ind + 1;
   l_cust_tbl (v_ind)                         := l_cust_rec;

   --TASKS DATA
   a := 0;

   FOR task_rec IN get_task_data (x_project_template_id)
   LOOP
      a := a + 1;
      l_tasks_in_rec.pm_task_reference                 := task_rec.task_number;
      l_tasks_in_rec.task_name                         := task_rec.task_name;
      l_tasks_in_rec.pm_parent_task_reference         := task_rec.parent_task_number;
      l_tasks_in_rec.receive_project_invoice_flag     := task_rec.receive_project_invoice_flag;
      l_tasks_in_rec.billable_flag                     := TASK_REC.billable_flag;
      l_tasks_in (a)                                 := l_tasks_in_rec;
   END LOOP;

   BEGIN
      --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   => x_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
         FND_FILE.PUT_LINE (FND_FILE.LOG, 'PROJECT CREATION COMPLETED IN ERROR.');
         RAISE api_error;
      END IF;

      IF l_return_status = 'S'
      THEN
         FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'PROJECT CREATED SUCCESSFULLY.');
         FND_FILE.PUT_LINE (FND_FILE.LOG, 'NEW PROJECT ID: ' || l_project_out.pa_project_id);
      END IF;

      COMMIT;
      x_new_out_project_id := l_project_out.pa_project_id;
      RETURN;
   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);
               FND_FILE.PUT_LINE (FND_FILE.LOG, 'API ERROR: ' || l_data);
            END LOOP;

            ROLLBACK;
            RETURN;
         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);
               FND_FILE.PUT_LINE (FND_FILE.LOG, 'API ERROR MESSAGE: ' || l_data);
            END LOOP;

            ROLLBACK;
            RETURN;
         END IF;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'In When Others Error of PROJECT CREATION API.Error - ' || SQLERRM);
      RETURN;
END;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect