Projects Foundation APIs

Any project-based organization implementing Oracle ERP application will require migrating all the projects information from legacy system. If the organization uses third party applications apart from the Oracle applications ERP like Primavera, Microsoft Projects, CASH etc…then there may be business requirements to integrate Oracle Projects with external systems.In such a scenario, projects are required to be maintained parallel in both the systems and further they are periodically updated in both the systems. We can use Project Foundation API’s to integrate both the systems. This article explains behaviour of API’s and how to use API’s in migration of projects defined in the external systems, how one can use the API’s to make necessary updations to projects created.

Projects can be created in different ways. Setup a template project and copy the projects from the template. This approach is best suited if number of projects to be migrated is very less.

Prerequisites

Activity Management Setup

Step 1: Setting up of source product is mandatory.

Navigation to setup Source Product: Projects Responsibility ? Setup ?

Activity Management Gateway à Source Product

PM Product Code Setup

Note: One can define any number of product sources depending on the

requirement. The ‘product source code’ defined here will be passed as a

value to the parameter p_pm_product_code.

Template Setup

Step 2: Setup template project with all the required setups. Setup quick entry fields as mandatory to enable API to validate these fields as mandatory parameters for the project being created.

Navigation to setup template: Projects Responsibility ? Setup ? Projects

Project Templates -> New.

After creating the project, Click on the ‘Setup Quick Entry’ to set up the quick entry fields.

Project Template Setup

Note: Enabling the ‘Required’ field will enforce the API to validate the parameter as mandatory when using this template. For more information on setup of Project Templates and setup Quick Entry please refer Oracle Projects user guide.

Other Setups

Step 4: Prior to migrating projects from legacy applications, following pre-requisites should be ensured:

oOrganization – Organization setup must be completed in Oracle HRMS.

oEmployee – Employee setup must be completed in Oracle HRMS.

oCustomer – Customer setup must be completed in Oracle Receivables.

Employees being defined as key members on the projects should be defined in HRMS with valid date range and have a valid assignment. Customers should be setup in Oracle receivables with valid ship-to-address, bill-to-addresses and the bill-to-addresses should have bill to contact setup.

Create Project and Update Project API’s

PA_PROJECT_PUB.Create Project

This API is used to create project from an existing project template/project. The section below discusses how the project can be created using the AMG API create_project

Parameters for Create_Projects:

The parameters to create_project and their structures are discussed below.

Name

Usage

TYPE

Required?

P_API_VERSION_NUMBER

IN

NUMBER

Yes

P_COMMIT

IN

VARCHAR2(1)

No

P_INIT_MSG_LIST

IN

VARCHAR2(1)

No

P_MSG_COUNT

OUT

NUMBER

P_MSG_DATA

OUT

VARCHAR2(2000)

P_RETURN_STATUS

OUT

VARCHAR2(1)

P_WORKFLOW_STARTED

OUT

VARCHAR2

P_PM_PRODUCT_CODE

IN

VARCHAR2(30)

Yes

P_PROJECT_IN

IN

PROJECT_IN_REC_TYPE

Yes

P_PROJECT_OUT

OUT

PROJECT_OUT_REC_TYPE

P_KEY_MEMBERS

IN

PROJECT_ROLE_TBL_TYPE

No

P_CLASS_CATEGORIES

IN

CLASS_CATEGORY_TBL_TYPE No

P_TASKS_IN

IN

TASK_IN_TBL_TYPE

No

P_TASKS_OUT

OUT

TASK_OUT_TBL_TYPE

P_ORG_ROLES

IN

TABLE TYPE

No

P_STRUCTUR_IN

IN

TABLE TYPE

No

P_EXT_ATTR_TBL_IN

IN

TABLE TYPE

No

Project Information

Basic project Information like project number, Project name, long name required to create a project are passed as parameters to the API. Depending on the project template setup and quick entry setup, parameters are decided to be mandatory for API. Information like project type, project status, project

Organization, distribution rules, retention tax codes, Project classifications, currencies, billing setup etc., can be setup at the template level so that these project templates can be used for creation of projects. If certain parameter information is not defined in the template or if you intend to over write the template values the same should be passed as parameters to API. If there is retention setup in project template, to copy this there should either be project template date or project start date. If project template date or project start date is not available then project will not be created despite the customer details being passed to the API.

Task Structure

Task structure will not be copied from project template when project is created from API unlike copying the project template to a new project from the application. The task structure should be explicitly passed to projects API.

Task structure should be passed to API in the order they should be created. Top tasks and parent tasks should be given prior to the task being created. In case ordering is not done, the task structure will not be copied and project will not be created. Further if the project is created and the task structure created without ordering, errors would occur on entering expenditures against the task. More information on this can be obtained from Metalink note 278492.1.

If task structure is created when the order is not correct, there is a possibility that the parent task_id’s and the top task_id’s will not be populated correctly.

For some of the tasks the top task_id’s that are populated will not be that of top tasks. In such a scenario, where the API is allowing creation of tasks although task structure is passed in incorrect order, application of patch 3261065 will resolve the problem and enable the API to show a proper error message on stopping the project creation.

Classifications

Classifications setup defined as mandatory in quick entry field should be attached to the project. If the project template has the mandatory classifications attached, then these classifications are not required to be passed while uploading projects using API. In case these classifications are not setup in the template then the classifications mandatory need to be passed to the API as parameter using the class_category_tbl_type parameter.

These classification values will be appended to the values already defined in the template. If the classification values being passed via API is already setup at project template level, API execution errors. When populating the categories if the code_percentage is not given API may populate junk numbers or characters. To avoid this problem you need to pass the value of code_percentage value FND_API.G_MISS_NUM. This is advisory although not mandatory.

Key Members

Project_role_tbl_type is a table type parameter, which is passed to create_project API for copying key members. All the key members required on the project should be passed to the create_project API. Alternatively if there are set of employees who will have to be defined as key members on all the projects, the same set of employees can be setup at the template level instead of passing the employees as parameters via API. Project manager is a mandatory role to be defined on any project. If this role is defined at the template level, then this should not be included in the project_role_tbl_type parameter, as it is already passed to the API through the project record. If key member with same role already exist on project template, the same should not be passed as parameter to API, else the API will error out.

In the project template if transaction duration dates are given, then the key member start date will be determined retrospective from the project start date. If the employee is having a valid assignment during these dates, the key member will be attached else the employee will not copied as key members.

The API will give no message or warning.

When no key member dates are being passed via API, project start date defaults as key member start date. When all the dates (Template trans start date, project start date, key member start date) are null, then the start date of the key member defaults as the first day of the year in which the project is copied, otherwise start date of assignment of the employee defaults as key member start date if the employee is defined in HR later during the same year.

If the employees being copied using API do not have valid HR assignments as per the retrospective key member start date based on the project template start date and project start date project is created with out giving any error messages but these key members will not be copied to the new project created.

PA_PROJECT_PUB.Update Project

This API is used to update project and task information for an existing project. This API can also be used to update a template project. The parameters for Update_project are as follows:

Name

Usage

TYPE

Required?

p_api_version_number

IN

NUMBER

Y

p_commit

IN

VARCHAR2

Y

p_init_msg_list

IN

VARCHAR2

Y

p_msg_count

OUT

NUMBER

p_msg_data

OUT

VARCHAR2

p_return_status

OUT

VARCHAR2

p_workflow_started

OUT

VARCHAR2

p_pm_product_code

IN

VARCHAR2

Y

p_project_in

IN

project_in_rec_type

Y

p_project_out

OUT

project_out_rec_type

p_key_members

IN

project_role_tbl_type

N

p_class_categories

IN

class_category_tbl_type N

p_tasks_in

IN

task_in_tbl_type

N

p_tasks_out

OUT

task_out_tbl_type

p_org_roles

IN

project_role_tbl_type

N

p_structure_in

IN

structure_in_rec_type

N

p_structure_out

OUT

structure_out_rec_type

p_pass_entire_structure IN

VARCHAR2

N

Project Information

Basic projects information like project name, long name, project description, task structure, and key member information can be updated using update_project. The unique project that is to be updated will be identified by the pm_project_reference. In case the project is created manually (ie, using copy to functionality) the project will be created with out populating the pm_task_reference. To update such projects created using ‘copy to’ functionality, pass the project_id along with the pm_project_reference, which will be used to identify the unique project to be updated by the update_project API.

Task Structure

Tasks can be added to the project. When the project is updated, tasks will be appended to existing task structure. When trying to update existing task, unique task will be identified by pm_task_reference. In case the task is not created with the project API (for the projects created using copy to function), then pm_task_reference is not populated and hence it will try to add this task, which in turn will give an error “unique constraint” as the same already exists. In this case the unique task will be identified by the task_number and task_id. So these values need to pass to update_project API.

In general, the task structure should be updated to add additional tasks to the existing WBS structure. In such case we have to follow the same process as we do in the creation of project.

Classifications

If additional classifications need to be appended to the project, you can pass these values to the update API and append new classifications to the project.

Key Members

If more key members need to be added to the project we can use update API to add key members in bulk. Key members may play a major role in terms of project security.

Sample Codes :

CreateProject:

http://www.projectsaccounting.com/code-snippets/category/1-projects?download=1:create-project

Update Project:

http://www.projectsaccounting.com/code-snippets/category/1-projects?download=2:update-project

Other AMG APIs

Other important AMG APIs are add_task and update_task , I will continue with those in the next part.

Reference:

Oracle Projects User Guide 11i

Oracle Projects APIs, Client Extensions and Open Interfaces Reference.

Leave a Reply