In my previous blog entry, I talked about the overall picture of developing a custom Oracle Projects interfacing process. In this entry, I’ll review the steps for creating the initial Project record and associated components. For the Projects Interface Application used as my reference, the main Project record requirements are: Customer; Project Manager; Tasks; Budget (Cost/Revenue)
For those familiar with developing API based interfaces, calling the Create-Project API functions in the same way. For those unfamiliar with API development, prior to the actual API call, inbound data from the third-party application should be assigned to the available API record-fields, for creating the Oracle Project (much like populating the fields of an Interface table). Some fields are required and will cause the Create Project API to fail, returning and error if missing or invalid.
The Oracle Create Project API definition:
PA_PROJECT_PUB.CREATE_PROJECT
(p_api_version_number,
p_commit,
p_init_msg_list,
p_msg_count,
p_msg_data,
p_return_status,
p_workflow_started,
p_pm_product_code,
p_project_in,
p_project_out,
p_key_members,
p_class_categories,
p_tasks_in,
p_tasks_out);
Now that we have an idea on the road map to create a project, we will go into some of the details as we start our drive. After verifying the incoming Project does not already exist in Oracle, processing continues. An existing Oracle Project requires a call to the Update-Project API for any needed changes or additions. The Oracle Create-Project API will reject a record if it already exists. To avoid this scenario – I recommend adding this validation prior to setting up the Create-Project call. If the Project is found to already exist in Oracle, it can be flagged for processing in the Project ‘Update’ routine.
When creating Oracle Projects, predefined templates are used as starting points to base the Project creation on. Templates are typically created as part of the functional setups and will often be based on project requirements pertaining to project types, resource and customer requirements, and budget processing.
- The 1st processing step is determining the template to be used for the project to be created. For my application we have 5 different templates to choose from, all pretty much having the same setup details except for the project type. Selection of the Oracle project template is based on a field value on the incoming project record.
- Next, the custom processing should go right into some data validation. For my application, a project customer is required on all project template types. The customer on the incoming project record must exist as a customer in Oracle. The customer account ID needs to populate the corresponding project API field record.
- In the 3rd step, validation of the incoming Project Manger information occurs. A Project Manager is also a required component for the Project record to be successfully created, with a status of ‘Approved’. This entails checking the resource is currently setup in Oracle HR, as a current Employee with the necessary HR job/assignment. Again, like the need for a valid customer record, the API will reject records containing invalid PM data. To catch this early in the process, it’s a ‘best practice’ to validate some of the data requirements prior to invoking the API. To validate PM data, the retrieved Oracle ‘Party ID’ record value needs to be assigned to the corresponding API field-record, along with the role type of ‘Project Manager’ and a start date. For my application, there are up to 3-different project resource roles assigned but only the project manager is required. The other 2 resource assignments, when applicable, follow the same process as the PM role.
- The final processing step before the API call, involves selection and assignment of the project tasks. As a standard function for project creation, the tasks can be auto-created based on the template setup and selected. A more custom approach is to have tasks assigned to a project as needed. The application I’m describing takes the more custom approach, where projects are averaging 50+ task records within a project cycle. From the data staging table, process the inbound project task records, assigning each record to the API Project-Task record-fields. Project-Task record items such as Task-Name, Code, and Start-Date are some of the main fields populated here.
Now having the main Create-Project API record fields populated:
- Assigned a Project Template (P_PROJECT_IN API record group)
- Validated and assigned Project Customer (P_PROJECT_IN API record group)
- Validated and assigned Project Resource(s) (P_KEY_MEMBERS API record group)
- Assigned Project Tasks (P_TASKS_IN API record group)
The program call to the PA_PROJECT_PUB.CREATE_PROJECT API can be invoked. A standard practice is to capture the returned results of the API, checking for success or rejection. In our case, all incoming project records that fail to be created as Oracle Project records are captured in a process logging table and displayed on a process exception report. Successfully created Project records can now be viewed and manipulated in the applications or updated via the Update API.
For each Project record successfully created, there is an associated budget that needs to be created. The project budget is based on cost and revenue figures passed in with each task record of the project. A separate call to the Project Budget API - CREATE_DRAFT_FINPLAN, is needed and occurs right after the Project Create API.
In my next Oracle Projects blog entry, I’ll complete the initial discussion on Project creation by detailing the steps surrounding creation of the Project Budget.
Blog author: Lamonte Bradley
Lamonte is a contributing blog author on the Applications Technical Services Practice team at BizTech.
