Throughout my many years of Application Interface development, I have grown to love designing and implementing custom Oracle interface processes. No matter how many times you develop an Order Management Interface or Suppliers conversion, no two are alike. One particular module that has landed on my favorite’s list is Oracle Projects. What a cross-combination of items: customers, employees, assignments, locations, accounting and of course the Projects module objects themselves. With Projects you have Templates, tasks, budgets, expenditures, costing and billing, just to name some of the key aspects. Developing conversion and interface processes for this module has become a specialty of mine.
This will be the first in a series of blogs on Oracle Projects, as I pass on the major features for developing custom interface processes into Projects. The first interface I will detail, utilizes standard Oracle Project API’s, to create and update Project records. In subsequent blog entries, I will describe how to interface expenditure data to projects using Oracle’s standard Projects Interface table.
So, let’s go under the hood!
Requirement: Import new and updated Project related information from a 3rd party application to Oracle Projects (R12), every 15 minutes. The information includes tasks, resources, and budget data.
For many interface processes, data is often exchanged using a text or comma-delimited file. But nothing beats a direct database link to pass the data between systems. Eliminating the need for file movement and data-loading processes such as FTP and SQL*Loader. The direct link is the method I chose for this interface. The overall process design, setup, and flow of a Projects interface are very much the same as many of the other Oracle interfaces, where you have – a data staging table, custom programs to process the data, standard Oracle API’s or open interface tables, and the setup of the target application. The design of an interface varies with the availability of module API’s and their usage within the processing program. Oracle Projects has several API’s available for creating and updating Project records with associated resources, tasks, and budgets. Below is the list of Project API’s I will discuss in this blog series:
- PA_PROJECT_PUB.CREATE_PROJECT
- PA_PROJECT_PUB.UPDATE_PROJECT
- PA_BUDGET_PUB.CREATE_DRAFT_FINPLAN
- PA_BUDGET_PUB.BASELINE_BUDGET
- PA_PROJECT_PUB.UPDATE_TASK
- PA_PROJECT_PUB.ADD_TASK
- PA_BUDGET_PUB.UPDATE_BUDGET
- PA_BUDGET_PUB.ADD_ BUDGET_LINE
- PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO
In designing an interface that utilizes a direct data-link, I find it is ‘best practice’ to make use of two data staging tables instead of the typical one. The first table is used for the initial loading of data inbound directly from the 3rd party application. The second table is used for actual processing. It is loaded with data from the initial staging table, transacted against, and validated via the interface process.
Once the layout of the data tables is established, main interface program development can proceed. Prior to any calls to the listed Projects API’s, there should be a series of process validations completed against the incoming data, to ensure the required record data is present and to retrieve any Oracle supporting data for the record. After initial data validation, object variables for the API to be called should be loaded with the inbound Project record information.
In my next Oracle Projects blog entry, I’ll go into the development of the PL/SQL processing program focusing on what key data validations should occur leading up to the first Projects API call – PA_PROJECT_PUB.CREATE_PROJECT.
Blog author: Lamonte Bradley
Lamonte is a contributing blog author on the Applications Technical Services Practice team at BizTech.
