Introduction:
This blog has the SQL Script to update the Project Status using API along with each task.
Cause of the issue: To update status of the project and its task using application was not happening as per the need so created the script with custom validations.
How do we solve:
Created a custom SQL script and added custom logic and validation to update the project status and task status.
Update the Project Status using API – PA_PROJECT_PUB.UPDATE_PROJECT
DECLARE
–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);
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;
–Variables needed to create task hierachy
ln_cnt NUMBER;
ln_member_cnt 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;
l_person_id NUMBER;
l_project_role_type VARCHAR2(20);
API_ERROR EXCEPTION;
BEGIN
— SET GLOBAL VALUES
pa_interface_utils_pub.set_global_info(
p_api_version_number => 1.0,
p_responsibility_id => 93457,
p_user_id => 581,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status
);
— PRODUCT RELATED DATA
l_pm_product_code := ‘CNV’;
— PRODUCT DATA (PROJECT_IN_REC_TYPE)
l_project_in.pa_project_id := 4576889;
— New Project Id
l_project_in.project_name := ‘XXCD_WDDHD2323 US’;
l_project_in.pm_project_reference := ‘XXCD_WDDHD2323 US’;
l_project_in.description := ‘Updating project with new Information’;
l_project_in.project_status_code := ‘APPROVED’;
l_project_in.carrying_out_organization_id := 749;
l_project_in.start_date := ’01-APR-2004′;
l_project_in.completion_date := ”;
l_project_in.actual_start_date := ”;
l_project_in.actual_finish_date := ”;
l_project_in.early_start_date := ”;
l_project_in.early_finish_date := ”;
l_project_in.late_start_date := ”;
l_project_in.late_finish_date := ”;
l_project_in.customer_id := 965493;
l_project_in.project_relationship_code := ‘Primary’;
—
ln_member_cnt := 1;
l_person_id := 34;
— Value from template
l_project_role_type := ‘PROJECT MANAGER’;
l_key_members(ln_member_cnt).person_id := 3987;
l_key_members(ln_member_cnt).project_role_type := ‘PROJECT MANAGER’;
dbms_output.put_line(‘ln_member_cnt := 2’);
—
ln_member_cnt:= 2;
l_key_members(ln_member_cnt).person_id := 6706;
l_key_members(ln_member_cnt).project_role_type := ‘COLLECTER’;
dbms_output.put_line(‘ln_member_cnt := 3’);
—
ln_member_cnt:= 3;
l_key_members(ln_member_cnt).person_id := 5678;
l_key_members(ln_member_cnt).project_role_type := ‘CONSULTANT’;
—
l_class_categories(1).class_category := ‘Product’;
l_class_categories(1).class_code := ‘Product 1’;
—
number_of_tasks1 := 1;
number_of_tasks2 := 2;
number_of_tasks3 := 0;
number_of_tasks4 := 0;
–Update the tasks with starting and one completion date
————————————————–
ln_cnt := 1;
l_tasks_in_rec.pm_task_reference := ‘1.0’;
l_tasks_in_rec.task_name := ‘Top Level 1′;
l_tasks_in_rec.pm_parent_task_reference := ”;
l_tasks_in_rec.task_start_date := ’01-APR-2004′;
l_tasks_in_rec.task_completion_date := ”;
l_tasks_in_rec.actual_start_date := ’01-APR-2004’;
l_tasks_in_rec.actual_finish_date := ”;
l_tasks_in_rec.early_start_date := ”;
l_tasks_in_rec.early_finish_date := ”;
l_tasks_in_rec.late_start_date := ”;
l_tasks_in_rec.late_finish_date := ”;
l_tasks_in(ln_cnt) := l_tasks_in_rec;
parent_level1 := ‘1.0’;
ln_cnt := ln_cnt + 1;
l_tasks_in_rec.pm_task_reference := ‘1.1’;
l_tasks_in_rec.task_name := ‘2 Level 1.1’;
l_tasks_in_rec.pm_parent_task_reference := parent_level1;
l_tasks_in(ln_cnt) := l_tasks_in_rec;
ln_cnt:= ln_cnt + 1;
l_tasks_in_rec.pm_task_reference := ‘1.2’;
l_tasks_in_rec.task_name := ‘2 Level 1.2′;
l_tasks_in_rec.pm_parent_task_reference := parent_level1;
l_tasks_in_rec.task_completion_date := ’31-DEC-2004’;
l_tasks_in(ln_cnt) := l_tasks_in_rec;
———————————–
PA_PROJECT_PUB.INIT_PROJECT;
PA_PROJECT_PUB.UPDATE_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_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
);
— Check the return status of the update_project call
IF l_return_status != ‘S’ THEN
RAISE API_ERROR;
END IF;
dbms_output.put_line(‘Project Updated Successfully’);
COMMIT;
— Check the returned records in the task out structure
FOR i IN 1..l_tasks_out.count LOOP
dbms_output.put_line(‘Task Id = ‘||l_tasks_out(i).pa_task_id);
dbms_output.put_line(‘Task Ref = ‘||l_tasks_out(i).pm_task_reference);
dbms_output.put_line(‘Return Status = ‘||l_tasks_out(i).return_status);
END LOOP;
–HANDLE EXCEPTIONS
EXCEPTION WHEN API_ERROR THEN
dbms_output.put_line(‘An API_ERROR occurred’);
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 => i,
p_msg_index_out => l_msg_index_out
);
dbms_output.put_line(‘error message: ‘||l_data);
END LOOP;
ROLLBACK;
END IF;
WHEN OTHERS THEN
dbms_output.put_line(‘An error occurred, sqlcode = ‘||sqlcode);
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 => i,
p_msg_index_out => l_msg_index_out
);
dbms_output.put_line(‘error message: ‘||l_data);
end loop;
rollback;
end if;
END;
