API to Update Task Information in Oracle Projects

/* Formatted on 3/27/2017 2:32:15 PM (QP5 v5.114.809.3010) */
DECLARE
   l_return_status            VARCHAR (10);
   l_msg_count                VARCHAR (240);
   l_MSG_DATA                 VARCHAR (240);
   l_rowid                    VARCHAR2 (240);
   task_record                PA_TASKS%ROWTYPE;
   task_struc_record          PA_PROJ_ELEMENTS%ROWTYPE;
   l_last_updated_by          NUMBER := FND_GLOBAL.USER_ID;
   l_last_update_date         DATE;
   l_last_update_login        NUMBER := FND_GLOBAL.LOGIN_ID;
   pt_task_name               PA_TASKS.TASK_NAME%TYPE;
   l_task_name                PA_PROJ_ELEMENTS.NAME%TYPE;
   l_task_name1               PA_PROJ_ELEMENTS.NAME%TYPE;
   l_project_id               PA_PROJECTS_ALL.PROJECT_ID%TYPE;
   l_task_id                  PA_TASKS.TASK_ID%TYPE;
   l_task_manager_person_id   PA_TASKS.TASK_MANAGER_PERSON_ID%TYPE;
   l_output                   VARCHAR2 (2000);
   l_msg_dummy                VARCHAR2 (2000);
   n                          NUMBER := 0;
BEGIN
   l_project_id := 590;
   l_task_id := 3355;
   l_task_manager_person_id := 136263;                      --Koch, Dibyajyoti

   BEGIN
      SELECT   *
        INTO   task_record
        FROM   pa_tasks
       WHERE   project_id = l_project_id AND task_id = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   *
        INTO   task_struc_record
        FROM   pa_proj_elements
       WHERE   PROJECT_ID = l_project_id AND PROJ_ELEMENT_ID = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   ROWID
        INTO   l_rowid
        FROM   pa_tasks
       WHERE   project_id = l_project_id AND task_id = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   SYSDATE INTO l_last_update_date FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   task_name
        INTO   pt_task_name
        FROM   pa_tasks
       WHERE   task_id = l_task_id;

      SELECT   name
        INTO   l_task_name
        FROM   pa_proj_elements
       WHERE   proj_element_id = l_task_id;

      IF pt_task_name = l_task_name
      THEN
         l_task_name1 := l_task_name;
      ELSE
         l_task_name1 := pt_task_name;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   PA_TASKS_PKG.UPDATE_ROW (
      X_ROWID                          => l_rowid,
      X_TASK_ID                        => task_record.task_id,
      X_PROJECT_ID                     => task_record.project_id,
      X_TASK_NUMBER                    => task_record.task_number,
      X_LAST_UPDATE_DATE               => l_last_update_date,  --Updated Value
      X_LAST_UPDATED_BY                => l_last_updated_by,   --Updated Value
      X_LAST_UPDATE_LOGIN              => l_last_update_login, --Updated Value
      X_Task_Name                      => task_record.task_name,
      X_Long_Task_Name                 => task_record.long_task_name,
      X_TOP_TASK_ID                    => task_record.top_task_id,
      X_WBS_LEVEL                      => task_record.wbs_level,
      X_READY_TO_BILL_FLAG             => task_record.ready_to_bill_flag,
      X_READY_TO_DISTRIBUTE_FLAG       => task_record.ready_to_distribute_flag,
      X_PARENT_TASK_ID                 => task_record.parent_task_id,
      X_DESCRIPTION                    => task_record.description,
      X_CARRYING_OUT_ORGANIZATION_ID   => task_record.carrying_out_organization_id,
      X_SERVICE_TYPE_CODE              => task_record.service_type_code,
      X_TASK_MANAGER_PERSON_ID         => l_task_manager_person_id, --Updated Value
      X_CHARGEABLE_FLAG                => task_record.chargeable_flag,
      X_BILLABLE_FLAG                  => task_record.billable_flag,
      X_LIMIT_TO_TXN_CONTROLS_FLAG     => task_record.limit_to_txn_controls_flag,
      X_START_DATE                     => task_record.start_date,
      X_COMPLETION_DATE                => task_record.completion_date,
      X_ADDRESS_ID                     => task_record.address_id,
      X_LABOR_BILL_RATE_ORG_ID         => task_record.labor_bill_rate_org_id,
      X_LABOR_STD_BILL_RATE_SCHDL      => task_record.labor_std_bill_rate_schdl,
      X_LABOR_SCHEDULE_FIXED_DATE      => task_record.labor_schedule_fixed_date,
      X_LABOR_SCHEDULE_DISCOUNT        => task_record.labor_schedule_discount,
      X_NON_LABOR_BILL_RATE_ORG_ID     => task_record.non_labor_bill_rate_org_id,
      X_NL_STD_BILL_RATE_SCHDL         => task_record.non_labor_std_bill_rate_schdl,
      X_NL_SCHEDULE_FIXED_DATE         => task_record.non_labor_schedule_fixed_date,
      X_NON_LABOR_SCHEDULE_DISCOUNT    => task_record.non_labor_schedule_discount,
      X_LABOR_COST_MULTIPLIER_NAME     => task_record.labor_cost_multiplier_name,
      X_ATTRIBUTE_CATEGORY             => task_record.attribute_category,
      X_ATTRIBUTE1                     => task_record.attribute1,
      X_ATTRIBUTE2                     => task_record.attribute2,
      X_ATTRIBUTE3                     => task_record.attribute3,
      X_ATTRIBUTE4                     => task_record.attribute4,
      X_ATTRIBUTE5                     => task_record.attribute5,
      X_ATTRIBUTE6                     => task_record.attribute6,
      X_ATTRIBUTE7                     => task_record.attribute7,
      X_ATTRIBUTE8                     => task_record.attribute8,
      X_ATTRIBUTE9                     => task_record.attribute9,
      X_ATTRIBUTE10                    => task_record.attribute10,
      X_COST_IND_RATE_SCH_ID           => task_record.cost_ind_rate_sch_id,
      X_REV_IND_RATE_SCH_ID            => task_record.rev_ind_rate_sch_id,
      X_INV_IND_RATE_SCH_ID            => task_record.inv_ind_rate_sch_id,
      X_COST_IND_SCH_FIXED_DATE        => task_record.cost_ind_sch_fixed_date,
      X_REV_IND_SCH_FIXED_DATE         => task_record.rev_ind_sch_fixed_date,
      X_INV_IND_SCH_FIXED_DATE         => task_record.inv_ind_sch_fixed_date,
      X_LABOR_SCH_TYPE                 => task_record.labor_sch_type,
      X_NON_LABOR_SCH_TYPE             => task_record.non_labor_sch_type,
      X_ALLOW_CROSS_CHARGE_FLAG        => task_record.allow_cross_charge_flag,
      X_PROJECT_RATE_DATE              => task_record.project_rate_date,
      X_PROJECT_RATE_TYPE              => task_record.project_rate_type,
      X_CC_PROCESS_LABOR_FLAG          => task_record.cc_process_labor_flag,
      X_LABOR_TP_SCHEDULE_ID           => task_record.labor_tp_schedule_id,
      X_LABOR_TP_FIXED_DATE            => task_record.labor_tp_fixed_date,
      X_CC_PROCESS_NL_FLAG             => task_record.cc_process_nl_flag,
      X_NL_TP_SCHEDULE_ID              => task_record.nl_tp_schedule_id,
      X_NL_TP_FIXED_DATE               => task_record.nl_tp_fixed_date,
      X_RECEIVE_PROJECT_INVOICE_FLAG   => task_record.receive_project_invoice_flag,
      X_WORK_TYPE_ID                   => task_record.work_type_id,
      X_JOB_BILL_RATE_SCHEDULE_ID      => task_record.job_bill_rate_schedule_id,
      X_emp_bill_rate_schedule_id      => task_record.emp_bill_rate_schedule_id,
      X_taskfunc_cost_rate_type        => task_record.taskfunc_cost_rate_type,
      X_taskfunc_cost_rate_date        => task_record.taskfunc_cost_rate_date,
      X_non_lab_std_bill_rt_sch_id     => task_record.non_lab_std_bill_rt_sch_id,
      X_labor_disc_reason_code         => task_record.labor_disc_reason_code,
      X_non_labor_disc_reason_code     => task_record.non_labor_disc_reason_code,
      x_retirement_cost_flag           => task_record.retirement_cost_flag,
      x_cint_eligible_flag             => task_record.cint_eligible_flag,
      X_CINT_STOP_DATE                 => task_record.cint_stop_date,
      X_GEN_ETC_SRC_CODE               => task_record.gen_etc_source_code
   );

   PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2 (
      p_calling_module                 => 'FORMS',
      p_task_id                        => task_record.task_id,
      p_task_number                    => task_record.task_number,
      p_task_name                      => l_task_name1,
      P_TASK_DESCRIPTION               => TASK_RECORD.DESCRIPTION,
      p_task_manager_id                => l_task_manager_person_id, --Updated Value
      p_carrying_out_organization_id   => task_record.carrying_out_organization_id,
      p_pm_product_code                => task_record.pm_product_code,
      p_pm_task_reference              => task_record.pm_task_reference,
      p_location_id                    => task_struc_record.location_id,
      p_ref_task_id                    => NULL,
      p_project_id                     => task_struc_record.project_id,
      x_msg_count                      => l_msg_count,
      x_msg_data                       => l_msg_data,
      x_return_status                  => l_return_status
   );
   COMMIT;

   IF l_return_status <> 'S'
   THEN
      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 (
            'Error: API Error while updating the Task: ' || l_output
         );
         COMMIT;
      END LOOP;
   ELSE
      DBMS_OUTPUT.put_line ('Sucessfully Update the task');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Other Error in Project: ' || SQLERRM);
END;