API to Update Task Information in Oracle Projects


Here is one API to update Task Information in Oracle Projects. Here I have used the API to update the Task Manager Information.

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;
Advertisements